We have sometimes spikes in call volume. So to handle the drops from customers hanging up. We are setting up some list that represent time buckets. So let's say, morning shift, mid-day shift, evening shift. All the calls that drop will schedule in to a list.
I am making it an extension of the API.
DB Query
dump all calls from a range of ingroups that are status drops, only one instance of each phone number to a list 1001
- Code: Select all
INSERT INTO vicidial_list (entry_date,modify_date, status, list_id, gmt_offset_now, called_since_last_reset, phone_code, phone_number, date_of_birth, called_count, last_local_call_time, rank, entry_list_id, comments)
SELECT call_date as entry_date, '0000-00-00 00:00:00' as modify_date,'NEW' as status,
'1002' as list_id, vl.gmt_offset_now as gmt_offset_now, 'N' as called_since_last_reset,
'1' as phone_code, vcl.phone_number as phone_number, '0000-00-00' as date_of_birth,
'0' as called_count, CONCAT(CURDATE(),' ','00:00:00') as last_local_call_time, '0' as rank, '1002' as entry_list_id, CONCAT('CALLED: ',COUNT(vcl.phone_number),' Times') as comments
FROM vicidial_closer_log vcl
LEFT JOIN vicidial_list vl ON vcl.lead_id = vl.lead_id
LEFT JOIN vicidial_list vlcheck ON vcl.phone_number = vlcheck.phone_number AND vlcheck.list_id = '1002'
WHERE vcl.call_date
BETWEEN CONCAT(CURDATE(),' ','09:00:00') AND CONCAT(CURDATE(),' ','11:59:59')
AND vcl.status = 'DROP'
AND campaign_id LIKE '90%'
AND campaign_id != '9008'
AND vlcheck.phone_number is NULL
GROUP BY vcl.phone_number
ORDER BY entry_date ASC
