public static function checkLeads()
{
$startDate = strtotime('now -2 weeks');
$endDate = strtotime('now -48 hours');
// Get a list of all responses for this date range
$externalReferrals = \DB::query('SELECT * FROM survey_responses WHERE created_at >= ' . $startDate . ' AND created_at <= ' . $endDate . ';')->execute();
$uniqueDetails = array();
foreach ($externalReferrals as $response) {
// Add the reference to the list of uniques for the time range
$uniqueDetails[$response['reference']][$response['question_id']] = array('answer' => $response['answer_id'], 'extra' => $response['extra']);
}
$ppiLeads = $drLeads = $drLegLeads = array();
foreach ($uniqueDetails as $referral => $referralDetails) {
if ((Controller_Survey_Lead::checkAnswer(12, 47, $referralDetails) and Controller_Survey_Lead::checkAnswer(11, array(41, 42, 43, 44, 45, 46), $referralDetails) and Controller_Survey_Lead::checkAnswer(8, 31, $referralDetails) || Controller_Survey_Lead::checkAnswer(5, array(11, 12, 13), $referralDetails) and Controller_Survey_Lead::checkAnswer(4, 8, $referralDetails) || Controller_Survey_Lead::checkAnswer(3, 6, $referralDetails)) || (Controller_Survey_Lead::checkAnswer(27, 166, $referralDetails) || Controller_Survey_Lead::checkAnswer(28, 168, $referralDetails) and Controller_Survey_Lead::checkAnswer(34, 191, $referralDetails) || (Controller_Survey_Lead::checkAnswer(33, array(185, 184, 183, 182, 181), $referralDetails) and Controller_Survey_Lead::checkAnswer(32, array(179, 178, 177), $referralDetails)) and Controller_Survey_Lead::checkAnswer(40, 217, $referralDetails))) {
$drLeads[] = $referral;
}
if ((Controller_Survey_Lead::checkAnswer(15, 66, $referralDetails) and Controller_Survey_Lead::checkAnswer(16, 69, $referralDetails) and !in_array($referral, $drLeads)) || (Controller_Survey_Lead::checkAnswer(30, 172, $referralDetails) and Controller_Survey_Lead::checkAnswer(31, 175, $referralDetails) and Controller_Survey_Lead::checkAnswer(40, 217, $referralDetails) and !in_array($referral, $drLeads))) {
$ppiLeads[] = $referral;
}
if (Controller_Survey_Lead::checkAnswer(12, 47, $referralDetails) and !in_array($referral, $drLeads) and !in_array($referral, $ppiLeads)) {
$drLegLeads[] = $referral;
}
}
// Create a CSV file for the lead type
$ppiAllLeads = array();
foreach ($ppiLeads as $lead) {
$thisCheck = Model_Survey_Lead_Dialler::query()->where('referral_id', $lead);
if ($thisCheck->count() < 1) {
$singleLead = \Model_Crmreferral::find($lead);
$ppiLeadInsert = array('lead_id' => "", 'entry_date' => date("Y-m-d H:i:s", strtotime($singleLead->referral_date)), 'modify_date' => date("Y-m-d H:i:s", strtotime($singleLead->referral_date)), 'status' => "NEW", 'user' => "", 'vendor_lead_code' => "", 'source_id' => "", 'list_id' => 199999, 'gmt_offset_now' => 0.0, 'called_since_last_reset' => "N", 'phone_code' => "9", 'phone_number' => (int) is_null($singleLead->tel_home) ? $singleLead->tel_mobile : $singleLead->tel_home, 'title' => "", 'first_name' => $singleLead->forename, 'middle_initial' => "", 'last_name' => $singleLead->surname, 'address1' => $singleLead->street_and_number, 'address2' => $singleLead->area, 'address3' => $singleLead->district, 'city' => $singleLead->town, 'state' => "", 'province' => $singleLead->county, 'postal_code' => $singleLead->post_code, 'country_code' => "UK", 'gender' => "U", 'date_of_birth' => date('Y-m-d', strtotime($singleLead->date_of_birth)), 'alt_phone' => (int) $singleLead->tel_mobile == 0 ? "" : (int) $singleLead->tel_mobile, 'email' => "", 'security_phrase' => "Y", 'comments' => "!!! SURVEY LEAD !!! - PPI QUALIFIED - Referral ID: " . $lead . " - Survey Taken on : " . date("jS F Y", strtotime($singleLead->referral_date)), 'called_count' => 0, 'last_local_call_time' => "2009-01-01 00:00:00", 'rank' => 0, 'owner' => "", 'entry_list_id' => 0);
// Add leads directly to the dialler
list($insertID, $rowsChanged) = \DB::insert('vicidial_list')->set($ppiLeadInsert)->execute('gabdialler');
$ppiAllLeads[] = array('diallerid' => $insertID, 'forename' => $singleLead->forename, 'surname' => $singleLead->surname, 'address1' => $singleLead->street_and_number, 'address2' => $singleLead->area, 'address3' => $singleLead->district, 'town' => $singleLead->town, 'county' => $singleLead->county, 'postcode' => $singleLead->post_code, 'home' => $singleLead->tel_home, 'mobile' => $singleLead->tel_mobile, 'email' => $singleLead->email, 'dob' => date('d-m-Y', strtotime($singleLead->date_of_birth)));
$leadInsert = new \Model_Survey_Lead_Dialler();
$leadInsert->referral_id = (int) $lead;
$leadInsert->dialler_id = (int) $insertID;
$leadInsert->type = "PPI";
$leadInsert->save();
}
}
$drAllLeads = array();
foreach ($drLeads as $lead) {
$thisCheck = Model_Survey_Lead_Dialler::query()->where('referral_id', $lead);
if ($thisCheck->count() < 1) {
$singleLead = \Model_Crmreferral::find($lead);
$drLeadInsert = array('lead_id' => "", 'entry_date' => date("Y-m-d H:i:s", strtotime($singleLead->referral_date)), 'modify_date' => date("Y-m-d H:i:s", strtotime($singleLead->referral_date)), 'status' => "NEW", 'user' => "", 'vendor_lead_code' => "", 'source_id' => "", 'list_id' => 149999, 'gmt_offset_now' => 0.0, 'called_since_last_reset' => "N", 'phone_code' => "9", 'phone_number' => (int) is_null($singleLead->tel_home) ? $singleLead->tel_mobile : $singleLead->tel_home, 'title' => "", 'first_name' => $singleLead->forename, 'middle_initial' => "", 'last_name' => $singleLead->surname, 'address1' => $singleLead->street_and_number, 'address2' => $singleLead->area, 'address3' => $singleLead->district, 'city' => $singleLead->town, 'state' => "", 'province' => $singleLead->county, 'postal_code' => $singleLead->post_code, 'country_code' => "UK", 'gender' => "U", 'date_of_birth' => date('Y-m-d', strtotime($singleLead->date_of_birth)), 'alt_phone' => (int) $singleLead->tel_mobile == 0 ? "" : (int) $singleLead->tel_mobile, 'email' => "", 'security_phrase' => "Y", 'comments' => "!!! SURVEY LEAD !!! - DR QUALIFIED - Referral ID: " . $lead . " - Survey Taken on : " . date("jS F Y", strtotime($singleLead->referral_date)), 'called_count' => 0, 'last_local_call_time' => "2009-01-01 00:00:00", 'rank' => 0, 'owner' => "", 'entry_list_id' => 0);
// Add leads directly to the dialler
list($insertID, $rowsChanged) = \DB::insert('vicidial_list')->set($drLeadInsert)->execute('gabdialler');
$drAllLeads[] = array('diallerid' => $insertID, 'forename' => $singleLead->forename, 'surname' => $singleLead->surname, 'address1' => $singleLead->street_and_number, 'address2' => $singleLead->area, 'address3' => $singleLead->district, 'town' => $singleLead->town, 'county' => $singleLead->county, 'postcode' => $singleLead->post_code, 'home' => $singleLead->tel_home, 'mobile' => $singleLead->tel_mobile, 'email' => $singleLead->email, 'dob' => date('d-m-Y', strtotime($singleLead->date_of_birth)));
$leadInsert = new \Model_Survey_Lead_Dialler();
$leadInsert->referral_id = (int) $lead;
$leadInsert->dialler_id = (int) $insertID;
$leadInsert->type = "DR";
$leadInsert->save();
}
}
$drLegAllLeads = array();
foreach ($drLegLeads as $lead) {
$thisCheck = Model_Survey_Lead_Dialler::query()->where('referral_id', $lead);
if ($thisCheck->count() < 1) {
$singleLead = \Model_Crmreferral::find($lead);
$drLeadInsert = array('lead_id' => "", 'entry_date' => date("Y-m-d H:i:s", strtotime($singleLead->referral_date)), 'modify_date' => date("Y-m-d H:i:s", strtotime($singleLead->referral_date)), 'status' => "NEW", 'user' => "", 'vendor_lead_code' => "", 'source_id' => "", 'list_id' => 149998, 'gmt_offset_now' => 0.0, 'called_since_last_reset' => "N", 'phone_code' => "9", 'phone_number' => (int) is_null($singleLead->tel_home) ? $singleLead->tel_mobile : $singleLead->tel_home, 'title' => "", 'first_name' => $singleLead->forename, 'middle_initial' => "", 'last_name' => $singleLead->surname, 'address1' => $singleLead->street_and_number, 'address2' => $singleLead->area, 'address3' => $singleLead->district, 'city' => $singleLead->town, 'state' => "", 'province' => $singleLead->county, 'postal_code' => $singleLead->post_code, 'country_code' => "UK", 'gender' => "U", 'date_of_birth' => date('Y-m-d', strtotime($singleLead->date_of_birth)), 'alt_phone' => (int) $singleLead->tel_mobile == 0 ? "" : (int) $singleLead->tel_mobile, 'email' => "", 'security_phrase' => "Y", 'comments' => "!!! SURVEY LEAD !!! - Interested In New Legislation - Referral ID: " . $lead . " - Survey Taken on : " . date("jS F Y", strtotime($singleLead->referral_date)), 'called_count' => 0, 'last_local_call_time' => "2009-01-01 00:00:00", 'rank' => 0, 'owner' => "", 'entry_list_id' => 0);
// Add leads directly to the dialler
list($insertID, $rowsChanged) = \DB::insert('vicidial_list')->set($drLeadInsert)->execute('gabdialler');
$drAllLeads[] = array('diallerid' => $insertID, 'forename' => $singleLead->forename, 'surname' => $singleLead->surname, 'address1' => $singleLead->street_and_number, 'address2' => $singleLead->area, 'address3' => $singleLead->district, 'town' => $singleLead->town, 'county' => $singleLead->county, 'postcode' => $singleLead->post_code, 'home' => $singleLead->tel_home, 'mobile' => $singleLead->tel_mobile, 'email' => $singleLead->email, 'dob' => date('d-m-Y', strtotime($singleLead->date_of_birth)));
$leadInsert = new \Model_Survey_Lead_Dialler();
$leadInsert->referral_id = (int) $lead;
$leadInsert->dialler_id = (int) $insertID;
$leadInsert->type = "DRLeg";
$leadInsert->save();
}
}
}
public static function generate_externals_report($introducer = null, $_startDate = null, $_endDate = null)
{
ini_set('memory_limit', '-1');
$startDate = is_null($_startDate) ? date('Y-m-d') : $_startDate;
$endDate = is_null($_endDate) ? date('Y-m-d') : $_endDate;
$externalReferrals = \Model_Crmreferral::query();
if (!is_null($introducer)) {
if (is_array($introducer)) {
$externalReferrals->where('introducer_id', "IN", $introducer);
} else {
$externalReferrals->where('introducer_id', (int) $introducer);
}
} else {
return null;
}
if (!is_null($_endDate)) {
$externalReferrals->where(DB::expr('DATE(referral_date)'), '>=', $startDate)->where(DB::expr('DATE(referral_date)'), '<=', $endDate);
} else {
$externalReferrals->where(DB::expr('DATE(referral_date)'), '=', $startDate);
}
$externalReferralResult = $externalReferrals->get();
$allReferrals = array();
foreach ($externalReferralResult as $referral) {
$questionCount = 0;
$responses = \Model_Survey_Response::query()->where('reference', $referral->id)->get();
$responseList = array();
foreach ($responses as $singleResponse) {
if ($singleResponse->answer_id != 0) {
$questionCount++;
$responseList[$singleResponse->question_id] = array(Model_Survey_Question::find($singleResponse->question_id)->question, Model_Survey_Question_Answer::find($singleResponse->answer_id)->answer, $singleResponse->extra);
}
}
// Check if the lead has been referred
$thisCheck = Model_Survey_Lead_Dialler::query()->where('referral_id', $referral->id);
if ($thisCheck->count() > 0) {
$details = $thisCheck->get_one();
$status = $details->type;
$goDetails = \Goautodial\Model_Vicidial_List::find($details->dialler_id);
$completed = array('DMPLUS', 'DR', 'PPICOM');
$failed = array('PPICLM', 'DNC', 'DNCL', 'HUNGUP', 'TPS', 'NI', 'DNQ', 'DEC', 'ALLRDY', 'NP');
if (in_array($goDetails->status, $completed)) {
$statusMessage = "Success. This referral continued with the " . $status . " package.";
} else {
if (in_array($goDetails->status, $failed)) {
$statusMessage = "Unfortunately this referral did not continue with the " . $status . " package.";
$status .= "no";
} else {
$statusMessage = "Referral has qualified for " . $status . ". This status will change when we have more information.";
}
}
} else {
if (strtotime("now -48 hours") < strtotime($referral->referral_date)) {
$status = null;
$statusMessage = "Referral has not yet been passed to the Consumer. Please wait, this will change.";
} else {
$status = null;
$statusMessage = "Referral has not yet qualified for any services. This status may change in the future.";
}
}
$allReferrals[] = array($referral->id, trim(ucwords(trim($referral->title) . " " . trim($referral->forename) . " " . trim($referral->surname))), $referral->introducer_agent_name, \Model_Call_Center::find($referral->introducer_id)->title, $referral->dialler_list_id, date("d/m/Y", strtotime($referral->referral_date)), date("H:i", strtotime($referral->referral_date)), $questionCount, $responseList, $status, $statusMessage);
}
//print_r($allReferrals);
return $allReferrals;
}