GMT Offset Now Madness

[iratik]

The implementation of recall logic in AST_VDHopper leaves something to be desired. (Running 2.04? ). Looking closely at the sql, it is only looking at the last status of a dispositioned lead, not the total count by each status. One would think from the lead recycle frequency list that the system actually counts the number of times a call has been dispositioned in each status. The SQL just checks the last status and filters by "status='AA' and called_since_last_reset in ('N','Y1','Y2') " where Yn goes up to the MAX, this is followed by a large set of conditions to filter by GMT offset which is quite difficult to debug or maintain.

We use 24 hour calling and the recall hopper fill query is easily 100 lines long due to generated sql conditions for including the GMT offsets.

My understanding is that this was probably a difficulty to determine if a lead was callable in the lead's gmt offset from the server's gmt offset, and that the local calling time profiles (24hr etc..) were being combined with the aforementioned pieces of information to generate a large set of conditions for a lead recycle based hopper fill.

Implement the following function to avoid generated sql, this function will also work for figuring out recall timing in local time zones as it is compatible with minute/second resolution.

Code: Select all

delimiter //
drop function if exists check_gmt_callable//
create function check_gmt_callable(callee_gmt decimal(3,2), start_time int, end_time int) returns boolean deterministic
begin
   #this function takes a GMT offset value like -7 (mountain), or -8 (pactific) and an integer
   #representing the start time of the call , this integer can just be 9*3600 for 9'oclock
   #or we can use time_to_sec("09:00:00")
   #the function will factor in the current server GMT offset and whether the
   #current time is past the start time relative to the gmt offset given in callee_gmt

   declare current_gmt_offset_sec integer;
   declare current_time_sec int;
   declare callee_gmt_difference int;
   declare callee_gmt_start int;
   declare callee_gmt_end int;
   set current_time_sec = time_to_sec(time(now()));
   #even if the server were set to utc, then this would come out 0 ..
   #if we divided the value in current_gmt_offset_sec , from a server running in CST
   #by 3600 , we would get -6
   set current_gmt_offset_sec = (unix_timestamp(now())-unix_timestamp(utc_timestamp()));
   #determine the difference between the target gmt offset and the server/local offset
   set callee_gmt_difference = current_gmt_offset_sec - (callee_gmt*3600);
   #apply those differences to the start/end times
   set callee_gmt_start = start_time+callee_gmt_difference;
   set callee_gmt_end = end_time+callee_gmt_difference;
   #check to see if the current time is within that range
   return (current_time_sec > callee_gmt_start) and (current_time_sec<callee_gmt_end);
end
//
delimiter ;
select check_gmt_callable(-8.00,9*3600,10*3600);


We use the above function as a filter in vicidial and manually enter the calling time appropriate for each filter. The gmt offset of the lead is stored in vicidial_list as gmt_offset_now. The filter sql for calling the leads between 9am and 3pm in their local time would be.
(check_gmt_callable(gmt_offset_now,time_to_sec("09:00"),time_to_sec("15:00")))[/code]

[mflorell]

Did you say you were running ViciDial 2.0.4?

Can you post your admin.php version and build?

[iratik]

$admin_version = '2.4-251';
$build = '100423-1030';

[mflorell]

OK, that's much better.

Another reason the timezone elements were set up the way they were, is because of state-specific call time restrictions. For consumer-based calling in the USA there are over a dozen states that have rules outside of the standard 9am-9pm practice for telemarketing.

As for lead recycling, we built it that way at a client's request many years ago, mostly because they wanted to call Busy leads much more frequently, and not call Answering Machines a second time for several hours. Most of our clients do not use lead recycling, instead opting for manual or automated resetting of leads, or using other means of lead call control.