Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

Convert Visual Foxpro to SQL query

Hi,

I'm trying to convert the query below to SQL and could do with a little help. Anyone good with Visual Foxpro to SQL coding? :)

*** Calculate (NEW) CPH
*** The allarea file might be very big. We need to do some tests to work out if we should split it up for calculations

INDEX ON TTOD(time) TAG cphmaster
SET ORDER TO cphmaster
SELECT TTOD(time) as date FROM allarea GROUP BY 1 INTO CURSOR cphdates READWRITE
SELECT cphdates
COUNT FOR date<=DATE() TO ln_numberofdates
ln_datecount = 0
SCAN
      *** Update the progres bar and info
      ln_datecount = ln_datecount + 1
      thisform.txtInfo.Caption = "Calculating Shift ID's for "+ALLTRIM(DTOC(cphdates.date))
      thisform.shape3.Width = (ln_datecount/ln_numberofdates)*264
      thisform.shape4.Width = 120+((ln_datecount/ln_numberofdates)*264)/2.2
     
      SELECT allarea
      SELECT * from allarea WHERE TTOD(time) = cphdates.date INTO CURSOR datearea READWRITE
     
      *** Calculate which shift ID applies to each record
      SCAN
            lc_callgroup = IIF('_aa'$caller,'CRD',IIF('_'$caller,'BTN','')) && Work out which calling group this record is in
            lc_dow = DOW(TTOD(datearea.time)) && The day of week for this record
            lt_thistime = CTOT('01/01/2000 '+SUBSTR(TTOC(datearea.time),AT(' ',TTOC(datearea.time))+1)) && The time of this record
            *** Assign each record it's correct shiftid from new_shifttime
            SELECT new_shifttime
            LOCATE FOR lc_callgroup = new_shifttime.callgroup AND lt_thistime >= new_shifttime.shiftstart AND lc_dow = new_shifttime.dow
            IF FOUND()
                  REPLACE datearea.shiftid WITH new_shifttime.shiftid
            ELSE
                  && This call was made outside of normal shift times, don't calculate
            ENDIF
      ENDSCAN
     
      *** Index the file for calculating CPH
      SELECT datearea
      INDEX ON ALLTRIM(UPPER(caller))+ALLTRIM(STR(shiftid))+ALLTRIM(TTOC(time)) TAG cphindexer
      SET DECIMALS TO 5

      *** Create a list of individual shifts
      SELECT caller, shiftid, TTOD(time) as cdate FROM datearea GROUP BY 1,2,3 INTO CURSOR cphshifts
      lc_cphscanconds = IIF(ll_cumulativecph,'shiftid>0',IIF(ll_7daycph,'shiftid>0 AND cdate >= DATE()-7','cdate>DATE()'))
      SELECT cphshifts
      *** Scan through the caller/shifts file (only scan for shifts in the last 7 days if ll_7daycps is set)
      SCAN FOR &lc_cphscanconds
            thisform.txtInfo.Caption = "Calculating CPH for "+ALLTRIM(DTOC(cphdates.date))+" ("+ALLTRIM(STR(ROUND((RECNO()/RECCOUNT())*100,0)))+"%)"
            SELECT datearea
            SELECT caller, time, 00000.00000 as cphtime, shiftid FROM datearea WHERE datearea.caller = cphshifts.caller AND datearea.shiftid = cphshifts.shiftid AND TTOD(datearea.time) = cphshifts.cdate ORDER BY 2 INTO CURSOR cphcaller READWRITE
            SELECT new_shifttime
            LOCATE FOR cphcaller.shiftid = new_shifttime.shiftid && Find this so we can refrence for the first and last records
           
            *** Decide when we should be calculating times from / to
            IF CTOT("01/01/2000 "+lc_reportstarttime) > new_shifttime.shiftstart AND (ll_runwithtimerange OR ll_runwithdaterange)
                  lt_calcmindate = CTOT("01/01/2000 "+lc_reportstarttime)
            ELSE
                  lt_calcmindate = new_shifttime.shiftstart
            ENDIF
            IF CTOT("01/01/2000 "+lc_reportendtime) < new_shifttime.shiftend AND (ll_runwithtimerange OR ll_runwithdaterange)
                  lt_calcmaxdate = CTOT("01/01/2000 "+lc_reportendtime)
            ELSE
                  lt_calcmaxdate = new_shifttime.shiftend
            ENDIF
           
           
            *** Decide if break times will apply for this shift
            IF CTOT("01/01/2000 "+lc_reportstarttime) < new_shifttime.breakstart AND CTOT("01/01/2000 "+lc_reportendtime) > new_shifttime.breakend AND (ll_runwithtimerange OR ll_runwithdaterange)
                  ll_includebreak=.t.
                  lt_breakmindate = new_shifttime.breakstart
                  lt_breakmaxdate = new_shifttime.breakend
            ELSE
                  ll_includebreak=.f.
            ENDIF
           
           
            SELECT cphcaller
            INDEX ON ALLTRIM(UPPER(caller))+ALLTRIM(STR(shiftid))+ALLTRIM(TTOC(time)) TAG cphindexer
            SCAN
                  SELECT datearea
                  SEEK ALLTRIM(UPPER(cphcaller.caller))+ALLTRIM(STR(cphcaller.shiftid))+ALLTRIM(TTOC(cphcaller.time))
                  IF FOUND()
                        SELECT cphcaller
                        lt_thistime = CTOT('01/01/2000 '+SUBSTR(TTOC(cphcaller.time),AT(' ',TTOC(cphcaller.time))+1))
                        DO CASE && This is where the CPH for the individual record will be calculated
                              CASE RECNO()=1 && Exception for the first record
                                    IF lt_thistime <= lt_calcmindate+(ln_cphthreshold*60)
                                          ln_thiscph = lt_thistime - lt_calcmindate && Calculate from the start of the shift
                                    ELSE
                                          ln_thiscph = 0 && We cannot determine an appropiate CPH time for this record
                                    ENDIF
                                   
                              CASE RECNO()=RECCOUNT() && Exception for the last resord
                                    IF lt_thistime >= lt_calcmaxdate-(ln_cphthreshold*60)
                                          ln_thiscph = (lt_calcmaxdate - lt_thistime) + (lt_thistime - lt_lasttime) && Calculate to the end of the shift
                                    ELSE
                                          ln_thiscph = 0 && Do the normal thing
                                    ENDIF
                                   
                              OTHERWISE && For all other records
                                    ln_thiscph = lt_thistime - lt_lasttime
                                    IF ln_thiscph>(ln_cphthreshold*60) && The CPH has exceled the allowed amount of time, check if there has been a break!
                                          IF ll_includebreak AND lt_lasttime<(lt_breakmindate+500) AND lt_thistime>(lt_breakmindate-500) AND ((lt_thistime-lt_lasttime)-(lt_breakmaxdate-lt_breakmindate))<(ln_cphthreshold*60)
                                                && This is the first call after a break, OK to have high CPH
                                          ELSE
                                                ln_thiscph=0 && The caller has been off the phone for longer than the threshold. Discard this amount of time
                                          ENDIF
                                    ENDIF
                        ENDCASE
                        lt_lasttime = lt_thistime && Keep track of the last calculated time so we can use it for the next record
                        *** ALSO DO SUMMINK WITH THE BREAKTIME FIELDS TO ALLOW THE CPH TO BE CARRIED OVER
                        &&CTOT("01/01/2010 10:13:09") - CTOT("01/01/2010 10:00:00")
                 
                        SELECT datearea
                        REPLACE datearea.cphtime WITH IIF(ln_thiscph>0,ln_thiscph / 3600,datearea.calltime)
                  ENDIF
                  SELECT cphcaller
            ENDSCAN
      ENDSCAN
      *** Remove the data we have just calculated for and replace with the updates data
      *** This will be faster than updating the existing data
      SELECT allarea
      DELETE FOR TTOD(TIME) = cphdates.date
      APPEND FROM DBF('datearea')
      SELECT datearea
      USE
ENDSCAN
0
Betamax
Asked:
Betamax
  • 3
1 Solution
 
Auric1983Commented:
Betamax,

Is the new table in a SQL Database?  If you can provide a sample excel spreadsheet with 1. Your column layout in the table, 2. description of calculations done. and 3. what you want the end result to be I can probably do something.

My Foxpro is pretty rusty, but it looks like you are looping through a dataset to check if breaks apply, what shift ID it is etc.  

0
 
BetamaxAuthor Commented:
Thanks Auric1983,

Yep I should've included much more info than I did! This query calculates how much time has been spent calling a campaign (that raises money for charities) by each caller working on that campaign. It uses an allarea.dbf that some other foxpro code puts together. I'll try and get an example for you of that.
Any help you can give me is greatly appreciated!
0
 
BetamaxAuthor Commented:
Rather than convert the code above I think I've got a better way of doing the calculation I require.

What I need is to populate a new table with data to do calculations off. The new table is called CPHcalc and has the columns CampaignID, UserName, Date_time, CPHtime.
The data to populate CPHcalc needs to come from a set of tables named telcom001 - telcom999. The CampaignID is the last 3 digits of each telcom table. Each individual table has the Username of each campaign caller, ana a dat_ column and a time_column. I'd like the dat_and time_ columns merged into a single Date_time column in the CPHcalc table.
CPHtime is the difficult one as I need to work out how much time each user has been on the phone for each campaign. For example if on 20/04/2009 Ben has been working on telcom333 and between 12:30 and12:45 he was on the phone, then I need the entry in CPHcalc to be something like:
CampaignID - 333
UserName - Ben
Date_time - 20/04/2009 12:30
CPHtime - 00:15

And then Ben is on the phone to someone else between 12:45 and 14:05 then it will be something like this.
CampaignID - 333
UserName - Ben
Date_time - 20/04/2009 12:45
CPHtime - 00:20

The code copied here returns the following information.

A list of all calls made by 'dianew_pt258'  when working on telcom333 between 17:00 and 21:00

24 - the total of contacts. (CPH = contacts per hour)

So in 4 hours she made 24 contacts meaning her CPH for campaign 333 is 6!

Hope that makes sense and someone can help me. Please. :)



--One user for the 17:00 to 21:00 shift
select * from sysdba.telcom333 where username = 'dianew_pt258' --and customerstatus = 'contact'
and dat_ = '2009-04-20 00:00:00.000'
and cast(left(time_, 2) as int) >= 17 and cast(left(time_, 2) as int) <= 21
order by dat_, time_

select count(customerstatus) from sysdba.telcom333 where username = 'dianew_pt258' and customerstatus = 'contact'
and dat_ = '2009-04-20 00:00:00.000'
and cast(left(time_, 2) as int) >= 17 and cast(left(time_, 2) as int) <= 21

Open in new window

0
 
BetamaxAuthor Commented:
Got it working!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now