Hi Mark,
I'm trying to think aloud. It usually helps me to try to explain the problem, when I can get to the solution.
We defined these fields just recently, BTW.
Here is how I understand them, though I'm not 100% sure my understanding is right.
The regular account limit is the number of accounts user can have in his queue for a day. It is set in his profile as a fixed number, which manager would be able to change.
The Overflow number is the number of accounts user can have in addition to regular limit, if there are delinquent accounts. E.g. he can have 50 as a regular count and 10 for overflow, so he may leave 10 accounts not resolved on the prev. day and he would have 50+10 on the next day.
The delinquent number I believe should be updated on a daily basis (however, there is no process in place which does it currently). I think, it's accumulative number of accounts which were not resolved and were not re-scheduled. E.g. they still would have a past date as a scheduled_time.
Now, my program has a number of days as a parameter. E.g. it could be run every night or it could be run once a week or once a month or on demand. I was thinking about how should I implement it and finally came up to the idea of having it as a Stored Procedure. This way it would be always run on the server and should be the most efficient way.
Bellow is a code for the program, which I wrote yesterday:
FUNCTION Assign_Queues
LPARAMETERS tcQueue_Name_From, tcQueue_Name_To, tcDepartment_Code, tcUserID, tnNumberOfDays
LOCAL ltStartProcessTime, lcSetTalk
lcSetTalk = SET('talk')
SET TALK OFF
ltStartProcessTime = DATETIME()
STRTOFILE("Started to run assigning queues process at " + TTOC(m.ltStartProcessTime) + CHR(13) + CHR(10),"AssignQueues.txt",0)
#DEFINE START_TIME 28800
LOCAL lcError, lcSetExact, ltScheduledTime, ldScheduled_Date
LOCAL ARRAY laOriginallyUsed[1]
lnOriginallyUsed = AUSED(laOriginallyUsed)
lcError = ""
lcSetExact = SET('exact')
SET EXACT ON
LOCAL ldTomorrow
ldTomorrow = DATE() + 1
SELECT TOP m.tnNumberOfDays Calendar_masters.tDate ;
FROM ;
mmVisCollect!Calendar_masters ;
INNER JOIN mmVisCollect!CALENDAR_CHILDS ;
ON Calendar_masters.ccalendar_masters_pk = CALENDAR_CHILDS.ccalendar_masters_fk ;
INNER JOIN mmVisCollect!VISCODES ;
ON VISCODES.cviscodes_pk = CALENDAR_CHILDS.ccalendar_code_fk ;
WHERE Calendar_masters.tDate >= m.ldTomorrow ;
AND VISCODES.ccode_value = "QD" ;
INTO ARRAY arrQDays ORDER BY Calendar_masters.tDate
LOCAL ARRAY arrQueue[1]
SELECT cDepartment_Code, cQueue_Names_Pk FROM mmVisCollect!Queue_Names;
WHERE cQueue_Name = m.tcQueue_Name_From ;
AND cDepartment_Code LIKE m.tcDepartment_Code ;
INTO ARRAY arrQueue
SELECT Departments.cDepartment_Code, ;
USERS.cUserID, USERS.iID AS iUserID ;
FROM ;
mmVisCollect!USERS ;
INNER JOIN mmVisCollect!UsGrLink;
ON USERS.iID = UsGrLink.iUserID ;
INNER JOIN mmVisCollect!EMPLOYEE_TEAMS ;
ON EMPLOYEE_TEAMS.cEmployee_teams_pk = UsGrLink.cemployee_teams_fk ;
INNER JOIN mmVisCollect!Departments ;
ON Departments.cdepartments_pk = EMPLOYEE_TEAMS.cemployee_departments_fk ;
WHERE USERS.cUserID LIKE m.tcUserID ;
AND EMPLOYEE_TEAMS.iActive_flag = 1 ;
AND Departments.cDepartment_Code LIKE m.tcDepartment_Code ;
AND Departments.iActive_flag = 1 ;
AND UsGrLink.iActive_flag = 1 ;
GROUP BY 1,2,3 ;
ORDER BY 1,2,3 INTO CURSOR c_Users
LOCAL lnI
FOR lnI = 1 TO m.tnNumberOfDays
IF NOT EMPTY(m.lcError)
EXIT
ENDIF
ldScheduled_Date = TTOD(arrQDays[m.lnI])
ltScheduled_Time = DTOT(m.ldScheduled_Date) + START_TIME
SELECT c_Users
SCAN WHILE EMPTY(m.lcError)
lcError = Assign_Accounts_To_User(@arrQueue, ;
m.tcQueue_Name_To, c_Users.cDepartment_Code, ;
c_Users.cUserID, c_Users.iUserID, ;
m.ltScheduled_Time)
ENDSCAN
NEXT
LOCAL lnNowUsed
LOCAL ARRAY laCurUsed[1]
lnNowUsed = AUSED(laCurUsed)
FOR lnI = 1 TO m.lnNowUsed
IF ASCAN(laOriginallyUsed,laCurUsed[m.lnI,1],1,m.lnOriginallyUsed,1) = 0
USE IN (laCurUsed[m.lnI,1])
ENDIF
NEXT
IF m.lcSetExact = "OFF"
SET EXACT OFF
ENDIF
IF m.lcSetTalk = "ON"
SET TALK ON
ENDIF
LOCAL ltEndProcessTime
ltEndProcessTime = DATETIME()
STRTOFILE("Finished to run assigning queues process at " + TTOC(m.ltEndProcessTime) + ;
CHR(13) + CHR(10) + IIF(!EMPTY(m.lcError), "Error encountered: " + m.lcError, ""), ;
"AssignQueues.txt",1)
RETURN m.lcError
FUNCTION Assign_Accounts_To_User
LPARAMETERS taQueues, tcQueue_Name_To, tcDepartment_Code, ;
tcUserID, tiUserID, ttScheduled_Time
EXTERNAL ARRAY taQueues
LOCAL lcCommission_Owner_UsgrLink_fk, lcQueue_Names_Fk, lcError
lcError = ""
SELECT UsGrLink.cUsGrLink_pk, ;
Queue_Names.cQueue_Names_Pk, Queue_Names.cQueue_Name, ;
Queue_Names.cDepartment_Code, ;
Employee_queue_profiles.cemployee_queue_profiles_pk, ;
Employee_queue_profiles.ylow_dollar_balance_amount, ;
Employee_queue_profiles.yhigh_dollar_balance_amount, ;
Employee_queue_profiles.iregular_account_limit, ;
Employee_queue_profiles.iqueue_overflow_count, ;
Employee_queue_profiles.idelinquent_account_limit ;
FROM mmVisCollect!UsGrLink ;
INNER JOIN mmVisCollect!Employee_queue_profiles ;
ON UsGrLink.cUsGrLink_pk = Employee_queue_profiles.cusgrlink_fk ;
INNER JOIN mmVisCollect!Queue_Names ;
ON Queue_Names.cQueue_Names_Pk = Employee_queue_profiles.cqueue_names_fk;
WHERE UsGrLink.iUserID = m.tiUserID ;
AND UsGrLink.iActive_flag = 1 ;
AND Employee_queue_profiles.iActive_flag = 1 ;
AND Queue_Names.cDepartment_Code LIKE m.tcDepartment_Code ;
AND Queue_Names.cQueue_Name = m.tcQueue_Name_To ;
ORDER BY UsGrLink.cUsGrLink_pk ;
INTO CURSOR c_Profiles
IF RECCOUNT('c_Profiles') > 0
LOCAL lnNumAccounts, lnQueue, lnAccountsToAssign
LOCAL ARRAY laMaxCount[1]
SELECT SUM(NVL(iregular_account_limit,0) + NVL(iqueue_overflow_count,0)) ;
AS lnNumAccounts FROM c_Profiles INTO ARRAY laMaxCount
lnNumAccounts = laMaxCount[1]
lnQueue = ASCAN(taQueues, m.tcDepartment_Code, 1, ALEN(taQueues,1), 1)
lcQueue_Names_Fk = taQueues [m.lnQueue, 2]
SELECT TOP m.lnNumAccounts TRANS.cTrans_Pk, ;
Trans_Employees_Queues.cTrans_Employees_Queues_Pk, 0 AS iAssigned, ;
SPACE(16) AS cCommission_Owner_UsGrLink_fk, ;
SPACE(16) AS cqueue_names_fk ;
FROM mmVisCollect!TRANS INNER JOIN mmVisCollect!Trans_Employees_Queues ;
ON TRANS.cTrans_Pk = Trans_Employees_Queues.cTrans_fk ;
WHERE ISNULL(Trans_Employees_Queues.cCommission_Owner_UsGrLink_fk) ;
AND Trans_Employees_Queues.cqueue_names_fk = m.lcQueue_Names_Fk ;
ORDER BY TRANS.tDate_Received ;
INTO CURSOR curAccounts READWRITE
GO TOP IN curAccounts
IF RECCOUNT('curAccounts') = 0
lcError = "Nothing to assign to the user " + m.tcUserID + " - queues exhausted!"
RETURN m.lcError
ENDIF
LOCAL ltStart, ltEnd
ltStart = DTOT(TTOD(m.ttScheduled_Time))
ltEnd = DTOT(TTOD(m.ttScheduled_Time)+1)-1
SELECT c_Profiles
GO TOP
SCAN WHILE EMPTY(m.lcError)
lcCommission_Owner_UsgrLink_fk = c_Profiles.cUsGrLink_pk
lcQueue_Names_Fk = c_Profiles.cQueue_Names_Pk
LOCAL ARRAY arrScheduled[1]
STORE 0 TO arrScheduled
SELECT COUNT(*) AS cntScheduled ;
FROM mmVisCollect!Trans_Employees_Queues ;
INNER JOIN mmVisCollect!Employee_Queue_Schedules ;
ON Trans_Employees_Queues.cTrans_Employees_Queues_Pk = ;
Employee_Queue_Schedules.cTrans_Employees_Queues_fk ;
WHERE Trans_Employees_Queues.cCommission_Owner_UsGrLink_fk = m.lcCommission_Owner_UsgrLink_fk ;
AND Trans_Employees_Queues.cqueue_names_fk = m.lcQueue_Names_Fk ;
AND BETWEEN(Employee_Queue_Schedules.tScheduled_Time, m.ltStart, m.ltEnd) ;
INTO ARRAY arrScheduled
lnAccountsToAssign = c_Profiles.iregular_account_limit - arrScheduled[1]
IF m.lnAccountsToAssign > 0
REPLACE ;
iAssigned WITH 1 ;
cCommission_Owner_UsGrLink_fk WITH m.lcCommission_Owner_UsgrLink_fk, ;
cqueue_names_fk WITH m.lcQueue_Names_Fk ;
NEXT m.lnAccountsToAssign IN curAccounts
IF _TALLY < m.lnAccountsToAssign
lcError = "Not enough unassigned accounts to assign to the user " + m.tcUserID
ENDIF
ENDIF
ENDSCAN
IF EMPTY(m.lcError)
lcError = Update_Queue_Tables('curAccounts', m.ttScheduled_Time, m.tcUserID)
ENDIF
ENDIF
RETURN m.lcError
FUNCTION Update_Queue_Tables
LPARAMETERS tcAlias, ttTime, tcUserID
IF NOT USED('updTrans')
USE TRANS IN 0 ALIAS updTrans AGAIN SHARED ORDER TAG cTrans_Pk
ENDIF
IF NOT USED('updEmp_Queues')
USE Trans_Employees_Queues IN 0 ALIAS updEmp_Queues ;
AGAIN SHARED ORDER TAG cEmpQue_Pk
ENDIF
IF NOT USED('updSchedules')
USE Employee_Queue_Schedules IN 0 ALIAS updSchedules ;
AGAIN SHARED
ENDIF
LOCAL ;
lcTrans_pk, ;
lcTrans_Employees_Queues_pk, ;
lcCommission_Owner_UsgrLink_fk, ;
lcQueue_Names_Fk, lcError, llFailure
BEGIN TRANSACTION
SELECT(m.tcAlias)
GO TOP
lcError = ""
llFailure = .F.
SCAN FOR iAssigned = 1 AND EMPTY(m.lcError) AND NOT m.llFailure
lcTrans_pk = cTrans_Pk
lcTrans_Employees_Queues_pk = cTrans_Employees_Queues_Pk
lcCommission_Owner_UsgrLink_fk = cCommission_Owner_UsGrLink_fk
lcQueue_Names_Fk = cqueue_names_fk
IF SEEK(m.lcTrans_pk,"UpdTrans")
TRY
REPLACE cCommissioned_Owner WITH m.tcUserID IN updTrans
CATCH
llFailure = .T.
ENDTRY
ENDIF
IF NOT m.llFailure AND SEEK(m.lcTrans_Employees_Queues_pk,"UpdEmp_Queues")
TRY
REPLACE cCommission_Owner_UsGrLink_fk ;
WITH m.lcCommission_Owner_UsgrLink_fk, ;
cqueue_names_fk WITH m.lcQueue_Names_Fk ;
IN updEmp_Queues
CATCH
llFailure = .T.
ENDTRY
ENDIF
IF NOT m.llFailure
IF NOT SEEK(m.lcTrans_Employees_Queues_pk,"updSchedules", "CEMPQUE_FK")
TRY
INSERT INTO updSchedules (cTrans_Employees_Queues_fk, cDiary_Code_fk, tScheduled_Time, iActive_At_Day_Start_Flag) ;
VALUES ;
(m.lcTrans_Employees_Queues_pk, .NULL., m.ttTime, 1)
CATCH
llFailure = .T.
ENDTRY
ELSE
lcError = "The account " + m.lcTrans_Employees_Queues_pk + " is already scheduled!"
ENDIF
ENDIF
ENDSCAN
IF m.llFailure
IF TYPE('gaTriggerErrors[1]')<>"U"
LOCAL lnI
FOR lnI = 1 TO ALEN(gaTriggerErrors)
lcError = m.lcError + gaTriggerErrors [m.lnI] + CHR(13)
NEXT
ENDIF
ENDIF
IF EMPTY(m.lcError)
END TRANSACTION
ELSE
ROLLBACK
ENDIF
RETURN m.lcError
ENDFUNC>Can you show us how these number flow into the fields you provided? Also what those fields are supposed to mean could help.
>
>I read them as iRegular_Account_Limit is the maximum accounts the user can be assigned. iDelinquent_Account_Limit is the maximum # of delinquent accounts a user can have at any one time. I am not sure how I read the overflow count.
>
>When I have a problem with how a concept is supposed to work, I get out a flip chart (tablet of paper about 30inches by 48inches in size) and start diagramming the logic. A large dry-erase board works wonders also.
>
>>Hi everybody,
>>
>>I'm writing a procedure which distributes work between employees. I have a pool of unassigned accounts which I'm assigning to the users. Each user has the following fields in his/her profile:
>>
>>iregular_account_limit
>>iqueue_overflow_count
>>idelinquent_account_limit
>>
>>If the user didn't work his/her accounts, they become delinquent.
>>
>>I would try to explain the problem in example:
>>The new user Joe has 50 accounts for his Daily queue as a regular count limit and 10 for overflow. My program assigns him 50 accounts for Monday, 50 for Tuesday, 50 for Wednesday, 50 for Thursday and 50 for Friday.
>>
>>Now, on Monday he worked up 30 accounts, and re-scheduled 10 for the next week’s Monday. So, he now has 10 delinquent accounts. On Tuesday he had the same situation, e.g. he resolved 30 accounts, re-scheduled 10 and 10 scheduled on the following Tuesday. And so he did the rest of the week. So, at the end of Friday he has 50 delinquent accounts.
>>
>>Now, how is my program supposed to work? How I’m supposed to assign him accounts for the next week?
If it's not broken, fix it until it is.
My Blog 
