ViciDial - Number scrubbing / Call list washing

# Get CPD results from Campaigns
# CAMP1 - Campaign 1 name
# CAMP2 - Campaign 2 name
# List ID "537" - Number list common to both CAMP1 and CAMP2

# Run ViciDial with Sangoma Netborder support
# Then prepare to query the ViciDial database for CPD results

# Create table for CAMP1
create table scrubber_result_x_CAMP1 TYPE=MYISAM AS
select campaign_id, lead_id, list_id, term_reason, call_date,
length_in_sec as duration, phone_number, status from vicidial_log
where campaign_id = 'CAMP1' order by lead_id ASC;

# Add CPD column
alter table scrubber_result_x_CAMP1 add column cpd varchar(32);
update scrubber_result_x_CAMP1 set cpd = '' where cpd is null;

# Update CPD column
update scrubber_result_x_CAMP1 AS t1, vicidial_cpd_log AS t2
set t1.cpd = t2.result where t1.lead_id = t2.lead_id;

# OPTIONAL - Set NA for those w/o CPD results
update scrubber_result_x_CAMP1 set cpd = 'NA' where cpd = '';

# --------------------

# Create table for CAMP2
create table scrubber_result_x_CAMP2 TYPE=MYISAM AS
select campaign_id, lead_id, list_id, term_reason, call_date,
length_in_sec as duration, phone_number, status from vicidial_log
where campaign_id = 'CAMP2' order by lead_id ASC;

# Add CPD column
alter table scrubber_result_x_CAMP2 add column cpd varchar(32);
update scrubber_result_x_CAMP2 set cpd = '' where cpd is null;

# Update CPD column
update scrubber_result_x_CAMP2 AS t1, vicidial_cpd_log AS t2
set t1.cpd = t2.result where t1.lead_id = t2.lead_id;

# OPTIONAL - Set NA for those w/o CPD results
update scrubber_result_x_CAMP2 set cpd = 'NA' where cpd = '';

# --------------------

# Create comparison of two Campaigns (CAMP1 and CAMP2)

# Create Comparison table
create table scrubber_result_list_CAMP_COMPARISON TYPE=MYISAM AS
select lead_id, list_id, phone_number from vicidial_list
where list_id = '537' order by lead_id ASC;

# Add CPD column
alter table scrubber_result_list_CAMP_COMPARISON add column cpd varchar(32);
alter table scrubber_result_list_CAMP_COMPARISON add column cpd2 varchar(32);
update scrubber_result_list_CAMP_COMPARISON set cpd = '' where cpd is null;
update scrubber_result_list_CAMP_COMPARISON set cpd2 = '' where cpd2 is null;

# Update CPD column (lookup phone then update cpd)
update scrubber_result_list_CAMP_COMPARISON AS t1, scrubber_result_x_CAMP1 AS t2
set t1.cpd = t2.cpd where t1.phone_number = t2.phone_number;

update scrubber_result_list_CAMP_COMPARISON AS t1, scrubber_result_x_CAMP2 AS t2
set t1.cpd2 = t2.cpd where t1.phone_number = t2.phone_number;

update scrubber_result_list_CAMP_COMPARISON set cpd = 'NA' where cpd = '';
update scrubber_result_list_CAMP_COMPARISON set cpd2 = 'NA' where cpd2 = '';

select * from scrubber_result_list_CAMP_COMPARISON;