public function action_delete($id = null)
{
if ($data_supplier_campaign_lists_duplicate = Model_Data_Supplier_Campaign_Lists_Duplicate::find($id)) {
$data_supplier_campaign_lists_duplicate->delete();
Session::set_flash('success', 'Deleted data_supplier_campaign_lists_duplicate #' . $id);
} else {
Session::set_flash('error', 'Could not delete data_supplier_campaign_lists_duplicate #' . $id);
}
Response::redirect('data/supplier/campaign/lists/duplicates');
}
public function test($total = 1)
{
ob_end_flush();
$mtime = microtime();
$mtime = explode(" ", $mtime);
$mtime = $mtime[1] + $mtime[0];
$starttime = $mtime;
$duplicate_details = array();
$count = 0;
$details = \Model_Data_Supplier_Campaign_Lists_Duplicate::query()->where('dialler', 0)->limit($total)->get();
$leads = array();
$id_reference = array();
foreach ($details as $lead) {
$leads[] = $lead->duplicate_number;
$id_reference[$lead->duplicate_number] = $lead->id;
}
print_r($id_reference);
$duplicate_details = \Goautodial\Insert::duplicate_check($leads);
foreach ($duplicate_details as $number => $dupe) {
$lead = \Model_Data_Supplier_Campaign_Lists_Duplicate::find($id_reference[$number]);
$lead->dialler = $dupe['dialler'];
$lead->lead_id = $dupe['data']['lead_id'];
$lead->save();
$count++;
}
print $count . " duplicates found.\n";
@ob_flush();
$mtime = microtime();
$mtime = explode(" ", $mtime);
$mtime = $mtime[1] + $mtime[0];
$endtime = $mtime;
$totaltime = $endtime - $starttime;
echo "It took " . $totaltime . " seconds to track " . $count . " duplicates.\n\n";
}
public function action_create()
{
if (Input::method() == 'POST') {
$config = array('path' => DOCROOT . DS . 'files', 'randomize' => true, 'ext_whitelist' => array('csv'));
Upload::process($config);
// Get the uploaded file and open it
if (Upload::is_valid()) {
$file = Upload::get_files(0);
$contents = File::read($file['file'], true);
// create an array of the CSV
$found_duplicates = array();
$duplicate_check = array();
$headings_csv = array();
$file_csv = array();
$i = 0;
$duplicate_check_count = 0;
foreach (explode("\n", $contents) as $line) {
$item = array();
if ($i == 0) {
$headings_csv = str_getcsv($line);
} else {
foreach (str_getcsv($line) as $key => $content) {
$item[$headings_csv[$key]] = $content;
if (strlen($content) > 6 and $headings_csv[$key] == "phone_number" || $headings_csv[$key] == "alt_phone") {
$duplicate_check[] = $content;
}
}
$file_csv[] = $item;
}
$duplicate_check_count++;
if ($duplicate_check_count == 50000) {
// Pull the current duplicates from the diallers to avoid max limit
$duplicate_temp = \Goautodial\Insert::duplicate_check($duplicate_check);
$found_duplicates = $found_duplicates + $duplicate_temp;
$duplicate_check = array();
$duplicate_temp = null;
$duplicate_check_count = 0;
}
$i++;
}
// Check for duplicates
$duplicate_temp = \Goautodial\Insert::duplicate_check($duplicate_check);
$found_duplicates = $found_duplicates + $duplicate_temp;
foreach ($file_csv as $lead) {
$phonenumber = isset($lead['phone_number']) ? $lead['phone_number'] : '';
$altphone = isset($lead['alt_phone']) ? $lead['alt_phone'] : '';
if (!isset($found_duplicates[$phonenumber]) and !isset($found_duplicates[$altphone])) {
$lead_table = Model_Lead_Table::forge(array('phone_number' => isset($lead['phone_number']) ? $lead['phone_number'] : '', 'title' => isset($lead['title']) ? $lead['title'] : '', 'first_name' => isset($lead['first_name']) ? $lead['first_name'] : '', 'middle_initial' => isset($lead['middle_initial']) ? $lead['middle_initial'] : '', 'last_name' => isset($lead['last_name']) ? $lead['last_name'] : '', 'address1' => isset($lead['address1']) ? $lead['address1'] : '', 'address2' => isset($lead['address2']) ? $lead['address2'] : '', 'address3' => isset($lead['address3']) ? $lead['address3'] : '', 'city' => isset($lead['city']) ? $lead['city'] : '', 'state' => isset($lead['state']) ? $lead['state'] : '', 'province' => isset($lead['province']) ? $lead['province'] : '', 'postal_code' => isset($lead['postal_code']) ? $lead['postal_code'] : '', 'country_code' => isset($lead['country_code']) ? $lead['country_code'] : '', 'gender' => isset($lead['gender']) ? $lead['gender'] : '', 'date_of_birth' => isset($lead['date_of_birth']) ? $lead['date_of_birth'] : '', 'alt_phone' => isset($lead['alt_phone']) ? $lead['alt_phone'] : '', 'email' => isset($lead['email']) ? $lead['email'] : '', 'security_phrase' => isset($lead['security_phrase']) ? $lead['security_phrase'] : '', 'comments' => isset($lead['comments']) ? $lead['comments'] : ''));
$lead_table->save();
} else {
$dupe = isset($found_duplicates[$phonenumber]) ? $found_duplicates[$phonenumber] : $found_duplicates[$altphone];
$lead_dupe = Model_Data_Supplier_Campaign_Lists_Duplicate::forge(array('list_id' => Input::post('list_id'), 'database_server_id' => 999, 'duplicate_number' => $phonenumber != '' ? $phonenumber : $altphone, 'dialler' => $dupe['dialler'], 'lead_id' => $dupe['dialler'] == 999 ? 999 : $dupe['data']['lead_id']));
$lead_dupe->save();
}
}
/*
$val = Model_Lead_Table::validate('create');
if ($val->run())
{
$lead_table = Model_Lead_Table::forge(array(
'phone_number' => Input::post('phone_number'),
'title' => Input::post('title'),
'first_name' => Input::post('first_name'),
'middle_initial' => Input::post('middle_initial'),
'last_name' => Input::post('last_name'),
'address1' => Input::post('address1'),
'address2' => Input::post('address2'),
'address3' => Input::post('address3'),
'city' => Input::post('city'),
'state' => Input::post('state'),
'province' => Input::post('province'),
'postal_code' => Input::post('postal_code'),
'country_code' => Input::post('country_code'),
'gender' => Input::post('gender'),
'date_of_birth' => Input::post('date_of_birth'),
'alt_phone' => Input::post('alt_phone'),
'email' => Input::post('email'),
'security_phrase' => Input::post('security_phrase'),
'comments' => Input::post('comments'),
));
if ($lead_table and $lead_table->save())
{
Session::set_flash('success', 'Added lead_table #'.$lead_table->id.'.');
Response::redirect('lead/table');
}
else
{
Session::set_flash('error', 'Could not save lead_table.');
}
}
else
{
Session::set_flash('error', $val->error());
}
*/
}
}
$this->template->title = "Lead_Tables";
$this->template->content = View::forge('lead/table/create');
}
/**
* Query to get common report statistics from a given array of dialler lists IDs.
*
* @access public
* @static
* @param mixed $list_array
* @param mixed $sort_by (default: null)
* @return void
*/
public static function list_stats($list_array, $sort_by = null)
{
$return_array = null;
$sorted_by_list = null;
foreach ($list_array as $dialler_id => $lists) {
$lists_implode = implode("', '", $lists);
$this_result = \DB::query("\n\t\t\tSELECT \n\t\t\t\tVLo.list_id AS 'List ID',\n\t\t\t\tVLi.list_description AS 'List Reference', \n\t\t\t\tIFNULL(COUNT(CASE WHEN (VLo.status IN ('3RDPAR','BNKRPT','BUSINE','CCJ','CHDB','CREDRA','DECEAS','DIEXCO','DMPLUS','EXISCL','HUNGUP','IVA','NODEBT','NOINDM','NOTAFF','NSTRUG','PPI','SECDBT','WRNAME','CALLBK','CBHOLD','DNC','NI','NP','PPIREF','SALE') ) then VLo.lead_id END),0) AS 'Spoken To',\n\t\t\t\tIFNULL(COUNT(CASE WHEN (VLo.status='POSSDC') then VLo.lead_id END),0) AS 'Disconnected',\n\t\t\t\tIFNULL(COUNT(CASE WHEN (VLo.status='TPS') then VLo.lead_id END),0) AS 'TPS',\n\t\t\t\tIFNULL((SELECT count(uniqueid) AS Referrals FROM vicidial_log WHERE list_id=VLo.list_id AND status='SALE'),0) AS 'Referrals',\n\t\t\t\tIFNULL((SELECT count(uniqueid) AS Referrals FROM vicidial_log WHERE list_id=VLo.list_id AND status IN ('PPI','PPIREF')),0) AS 'PPI Referrals',\n\t\t\t\tcount(VLo.lead_id) - IFNULL(COUNT(CASE WHEN (VLo.status='POSSDC') then VLo.lead_id END),0) - IFNULL(COUNT(CASE WHEN (VLo.status='TPS') then VLo.lead_id END),0) AS 'Dialable Leads'\n\t\t\tFROM \n\t\t\t\tvicidial_list AS VLo\n\t\t\tLEFT JOIN\n\t\t\t\tvicidial_lists AS VLi ON VLo.list_id=VLi.list_id\n\t\t\tWHERE \n\t\t\t\tVLo.list_id IN ('" . $lists_implode . "')\n\t\t\tGROUP BY \n\t\t\t\tVLo.list_id\n\t\t\tORDER BY\n\t\t\t\tVLo.list_id ASC;\n\t\t\t")->cached(900)->execute(static::connection($dialler_id));
$return_array[$dialler_id] = $this_result->as_array();
$connection = null;
}
if (is_null($sort_by)) {
// Sort by list name and combine
foreach ($return_array as $server => $results) {
foreach ($results as $single_list) {
$dupes = \Model_Data_Supplier_Campaign_Lists_Duplicate::select()->where('database_server_id', $server)->where('list_id', $single_list['List ID']);
if (isset($sorted_by_list[$single_list['List Reference']])) {
$sorted_by_list[$single_list['List Reference']]['Spoken To'] = $sorted_by_list[$single_list['List Reference']]['Spoken To'] + $single_list['Spoken To'];
//$sorted_by_list[$single_list['List Reference']]['Dialled'] = $sorted_by_list[$single_list['List Reference']]['Dialled'] + $single_list['Dialled'];
$sorted_by_list[$single_list['List Reference']]['Disconnected'] = $sorted_by_list[$single_list['List Reference']]['Disconnected'] + $single_list['Disconnected'];
$sorted_by_list[$single_list['List Reference']]['TPS'] = $sorted_by_list[$single_list['List Reference']]['TPS'] + $single_list['TPS'];
$sorted_by_list[$single_list['List Reference']]['Referrals'] = $sorted_by_list[$single_list['List Reference']]['Referrals'] + $single_list['Referrals'];
$sorted_by_list[$single_list['List Reference']]['PPI Referrals'] = $sorted_by_list[$single_list['List Reference']]['PPI Referrals'] + $single_list['PPI Referrals'];
$sorted_by_list[$single_list['List Reference']]['Duplicates'] = $sorted_by_list[$single_list['List Reference']]['Duplicates'] + $dupes->count();
$sorted_by_list[$single_list['List Reference']]['Dialable Leads'] = $sorted_by_list[$single_list['List Reference']]['Dialable Leads'] + $single_list['Dialable Leads'];
} else {
$sorted_by_list[$single_list['List Reference']]['List ID'] = $single_list['List ID'];
$sorted_by_list[$single_list['List Reference']]['List Reference'] = $single_list['List Reference'];
$sorted_by_list[$single_list['List Reference']]['Dialable Leads'] = $single_list['Dialable Leads'];
$sorted_by_list[$single_list['List Reference']]['Spoken To'] = $single_list['Spoken To'];
$sorted_by_list[$single_list['List Reference']]['Referrals'] = $single_list['Referrals'];
$sorted_by_list[$single_list['List Reference']]['PPI Referrals'] = $single_list['PPI Referrals'];
//$sorted_by_list[$single_list['List Reference']]['Dialled'] = $single_list['Dialled'];
$sorted_by_list[$single_list['List Reference']]['Disconnected'] = $single_list['Disconnected'];
$sorted_by_list[$single_list['List Reference']]['TPS'] = $single_list['TPS'];
$sorted_by_list[$single_list['List Reference']]['Duplicates'] = $dupes->count();
}
}
}
foreach ($sorted_by_list as $list_id => $details) {
$sorted_by_list[$list_id]['Conversion Ratio'] = (int) $sorted_by_list[$list_id]['Spoken To'] > 0 ? number_format($sorted_by_list[$list_id]['Referrals'] / $sorted_by_list[$list_id]['Spoken To'] * 100, 2) . "%" : "0%";
}
}
return is_null($sorted_by_list) ? $return_array : $sorted_by_list;
}
