public function action_delete($id = null)
{
if ($user_center = Model_User_Center::find($id)) {
$user_center->delete();
Session::set_flash('success', 'Deleted user_center #' . $id);
} else {
Session::set_flash('error', 'Could not delete user_center #' . $id);
}
Response::redirect('user/centers');
}
public static function generate_disposition_report($center = null, $has_access = TRUE, $all_centers = TRUE, $start_date = null, $end_date = null, $include_no_contacts = FALSE)
{
// Check the logged in user is authorised to view the disposition report
if ($has_access) {
// Check if the user has access to all disposition reports or just their own center
if ($all_centers) {
$view_all = TRUE;
} else {
$view_all = FALSE;
list($driver, $user_id) = Auth::get_user_id();
$this_user = Model_User::find($user_id);
// ## START - Pull an array of call centers this user can access
$call_center_array = array();
$sall_call_centers = Model_User_Center::query()->where('user', $user_id)->get();
foreach ($sall_call_centers as $acc) {
$call_center_check = Model_Call_Center::find($acc->center);
$call_center_array[] = $call_center_check->shortcode;
}
// ## END - Pull an array of call centers this user can access
if (count($call_center_array) < 1) {
return array('status' => 'FAIL', 'message' => 'You do not have access to the disposition report.');
} else {
$center = $call_center_array;
}
}
if (!is_null($center) || $view_all) {
if (is_null($start_date)) {
$start_date = date("d-m-Y");
}
if (!is_null($start_date) && is_null($end_date)) {
$end_date = $start_date;
}
$cache_name = $center . $start_date . $end_date;
$disposition_duration = "\n\t\t\t\t(\n\t\t\t\t(CLD.DateCreated >= CONVERT(datetime, '" . $start_date . "', 105) AND CLD.DateCreated <= CONVERT(datetime, '" . $end_date . "', 105)+1)\n\t\t\t\tOR\n\t\t\t\t(CC.LastContactAttempt >= CONVERT(datetime, '" . $start_date . "', 105) AND CC.LastContactAttempt <= CONVERT(datetime, '" . $end_date . "', 105)+1)\n\t\t\t\t) ";
$pack_in_duration = "(D_CLD.DatePackReceived >= CONVERT(datetime, '" . $start_date . "', 105) AND D_CLD.DatePackReceived <= CONVERT(datetime, '" . $end_date . "', 105)) ";
if (is_array($center)) {
// Multiple call centers allowed
$flatList = "";
$total = count($center);
$i = 1;
foreach ($center as $sCenter) {
$flatList .= $sCenter;
$flatList .= $i < $total ? "', '" : "";
$i++;
}
$call_center_choice = "AND DI_REF.short_code IN ('" . $flatList . "')";
} else {
$call_center_choice = !is_null($center) ? "AND DI_REF.short_code = '" . $center . "'" : "";
}
$results1 = DB::query("SELECT CLD.ClientID\n\t\t\t\t ,CLD.LeadRef AS 'Dialler Lead ID'\n\t\t\t ,(CD.Forename + ' ' + CD.Surname) AS Name\n\t\t\t , CASE WHEN D_CLD.SourceID > 0 THEN (SELECT TOP (1) Description FROM Debtsolv_GABFS.dbo.Type_Lead_Source WHERE ID=D_CLD.SourceID)\n WHEN LBA.LeadSourceID > 0 THEN (SELECT TOP (1) Description FROM Debtsolv_GABFS.dbo.Type_Lead_Source WHERE ID=LBA.LeadSourceID)\n ELSE (SELECT TOP (1) Description FROM Leadpool_GABFS.dbo.Type_Lead_Source WHERE Reference COLLATE DATABASE_DEFAULT=DI_REF.list_id COLLATE DATABASE_DEFAULT)\n END AS 'Lead Source'\n\t\t\t ,CASE WHEN\n\t\t\t \tISNULL(DI_REF.short_code,'<None>') = '<None>'\n\t\t\t THEN\n\t\t\t \t('<span id='''+CONVERT(varchar,CLD.ClientID)+''' class=''no-office''></span>')\n\t\t\t ELSE\n\t\t\t \tDI_REF.short_code\n\t\t\t END AS Office\n\t\t\t ,CASE WHEN\n\t\t\t \t DI_REF.short_code = 'REACTIV'\n\t\t\t THEN\n\t\t\t\t ISNULL((\n\t\t\t\t SELECT Top (1)\n\t\t\t\t Undersigned COLLATE DATABASE_DEFAULT \n\t\t\t\t FROM\n\t\t\t\t Debtsolv_GABFS.dbo.Users AS D_URS\n\t\t\t\t LEFT JOIN\n\t\t\t\t Debtsolv_GABFS.dbo.Client_LeadData AS D_CLD ON D_URS.ID = D_CLD.TelesalesAgent\n\t\t\t\t WHERE\n\t\t\t\t D_CLD.LeadPoolReference = CLD.ClientID\n\t\t\t\t ), '')\n\t\t\t ELSE\n\t\t\t ISNULL(DI_REF.full_name, '') END AS 'Telesales Agent',\n\t\t\t \n\t\t\t ISNULL((\n\t\t\t SELECT Top (1)\n\t\t\t Undersigned\n\t\t\t FROM\n\t\t\t Debtsolv_GABFS.dbo.Users AS D_URS\n\t\t\t LEFT JOIN\n\t\t\t Debtsolv_GABFS.dbo.Client_LeadData AS D_CLD ON D_URS.ID = D_CLD.Counsellor\n\t\t\t WHERE\n\t\t\t D_CLD.LeadPoolReference = CLD.ClientID\n\t\t\t ),(SELECT Top (1)\n\t\t\t \t Undersigned\n\t\t\t \tFROM\n\t\t\t \t Debtsolv_GABFS.dbo.Users AS DURS\n\t\t\t \tLEFT JOIN\n\t\t\t \t Leadpool_GABFS.dbo.CampaignContactAccess AS CCA ON DURS.ID = CCA.UserID\n\t\t\t \tWHERE\n\t\t\t \t CCA.CampaignContactID = CC.ID\n\t\t\t \tORDER BY\n\t\t\t \tCCA.AccessDate DESC)) AS 'Consolidator'\n\t\t\t \n\t\t\t ,TCR.[Description]\n\t\t\t ,ISNULL(DI_REF.product,'DR') AS Product\n\t\t\t ,D_CPD.NormalExpectedPayment / 100 AS DI,\n\t\t\t (\n\t\t\t \tSELECT Top (1)\n\t\t\t \t\tResponseText\n\t\t\t \tFROM\n\t\t\t \t\tDebtsolv_GABFS.dbo.Client_CustomQuestionResponses\n\t\t\t \tWHERE\n\t\t\t \t\tQuestionID = 10001\n\t\t\t \t\tAND ClientID = D_CLD.Client_ID\n\t\t\t ) AS 'Delivery',\n\t\t\t (\n\t\t\t \tSELECT Top (1)\n\t\t\t \t\tResponseVal\n\t\t\t \tFROM\n\t\t\t \t\tDebtsolv_GABFS.dbo.Client_CustomQuestionResponses\n\t\t\t \tWHERE\n\t\t\t \t\tQuestionID = 10007\n\t\t\t \t\tAND ClientID = D_CLD.Client_ID\n\t\t\t ) AS 'ProductType'\n\t\t\t ,CONVERT(varchar, CLD.DateCreated, 120) AS 'Referred Date'\n\t\t\t ,CONVERT(varchar, CC.LastContactAttempt, 120) AS 'Last Contact Date'\n\t\t\t ,CASE\n\t\t\t WHEN CC.ContactResult = 700\n\t\t\t THEN CONVERT(varchar, CC.Appointment, 120)\n\t\t\t ELSE\n\t\t\t ''\n\t\t\t END AS 'Call Back Date'\n\t\t\t , CC.ContactResult AS ContactResult\n\t\t\t FROM\n\t\t\t Leadpool_GABFS.dbo.Client_LeadDetails AS CLD\n\t\t\t LEFT JOIN\n\t\t\t Leadpool_GABFS.dbo.Client_Details AS CD ON CLD.ClientID = CD.ClientID\n\t\t\t LEFT JOIN\n\t\t\t Leadpool_GABFS.dbo.Campaign_Contacts AS CC ON CLD.ClientID = CC.ClientID\n\t\t\t LEFT JOIN\n\t\t\t Leadpool_GABFS.dbo.Type_ContactResult AS TCR ON CC.ContactResult = TCR.ID\n\t\t\t LEFT JOIN\n\t\t\t\tLeadpool_GABFS.dbo.LeadBatch AS LBA ON CLD.LeadBatchID = LBA.ID\n\n\t\t\t LEFT JOIN\n\t\t\t Debtsolv_GABFS.dbo.Client_LeadData AS D_CLD ON CLD.ClientID = D_CLD.LeadPoolReference\n\t\t\t LEFT JOIN\n\t\t\t Debtsolv_GABFS.dbo.Users AS D_U ON D_CLD.TelesalesAgent = D_U.ID\n\t\t\t LEFT JOIN\n\t\t\t Debtsolv_GABFS.dbo.Client_PaymentData AS D_CPD ON D_CLD.Client_ID = D_CPD.ClientID\n\t\t\t LEFT JOIN\n\t\t\t \tDialler.dbo.referrals AS DI_REF ON CLD.ClientID = DI_REF.leadpool_id\n\n\n\t\t\t WHERE\n\t\t\t " . $disposition_duration . "\n\t\t\t\tAND NOT ((D_CPD.InitialAgreedAmount is null OR D_CPD.NormalExpectedPayment <= 0) AND CC.ContactResult = 1500)\n\t\t\t " . $call_center_choice . "\n\t\t\t AND ISNULL(DI_REF.product,'DR') = 'DR'\n\t\t\t ORDER BY\n\t\t\t\tCLD.LeadRef2\n\t\t\t ,TCR.[Description]\n\t\t\t ,Product\n\t\t\t ,CLD.DateCreated DESC")->cached(300, "disposition.report." . $cache_name . "gab", false)->execute('debtsolv');
$results = array();
foreach ($results1 as $rsult) {
$results[] = $rsult;
}
foreach ($results2 as $rsult) {
$results[] = $rsult;
}
$totals = array('referrals' => array('count' => 0, 'value' => 0), 'pack_outs' => array('count' => 0, 'value' => 0), 'dr_pack_outs' => array('count' => 0, 'value' => 0), 'dmplus_pack_outs' => array('count' => 0, 'value' => 0), 'pack_ins' => array('count' => 0, 'value' => 0), 'paid' => array('count' => 0, 'value' => 0));
$result_parse = array();
$lost_parse = array();
$po_result_parse = array();
foreach ($results as $result) {
if ($result['Dialler Lead ID'] == 'fab' || $result['Telesales Agent'] == 'FAB Admin' || $result['Consolidator'] == 'FAB Admin') {
// Do Nothing
} else {
if (($result['ContactResult'] == 0 || $result['ContactResult'] == 900 || $result['ContactResult'] == 721) && !$include_no_contacts) {
$lost_parse[] = array('<a href="/reports/change_offices/' . $result['ClientID'] . '/">' . $result['ClientID'] . '</a>', $result['Dialler Lead ID'], $result['Name'], $result['Lead Source'], $result['Office'], $result['Telesales Agent'], $result['Consolidator'], '<div class="dispositionName">' . $result['Description'] . '</div>', date("d-m-y H:i:s", strtotime($result['Referred Date'])));
} else {
if (strtotime($result['Referred Date']) >= strtotime($start_date . " 00:00:00") && strtotime($result['Referred Date']) <= strtotime($end_date . " 23:59:59")) {
$pdtype = "";
switch ((string) $result['ProductType']) {
case '0':
$pdtype = "DR";
break;
case '1':
$pdtype = "DMPLUS";
break;
case '2':
$pdtype = "PPI";
break;
case '3':
$pdtype = "DRPLUS";
break;
case '':
$pdtype = "";
break;
}
$result_parse[] = array('<a href="/reports/change_offices/' . $result['ClientID'] . '/">' . $result['ClientID'] . '</a>', $result['Dialler Lead ID'], $result['Name'], $result['Lead Source'], $result['Office'], $result['Telesales Agent'], $result['Consolidator'], '<div class="dispositionName">' . $result['Description'] . '</div>', $result['DI'], $result['Delivery'], $pdtype, date("d-m-y", strtotime($result['Referred Date'])), date("d-m-y H:i", strtotime($result['Last Contact Date'])), $result['Call Back Date'] != " " ? date("d-m-y", strtotime($result['Call Back Date'])) : "");
$totals['referrals']['count']++;
$totals['referrals']['value'] = $totals['referrals']['value'] + $result['DI'];
}
if ($result['Description'] == "Lead Completed") {
$pdtype = "";
switch ((string) $result['ProductType']) {
case '0':
$pdtype = "DR";
$totals['dr_pack_outs']['count']++;
$totals['dr_pack_outs']['value'] = $totals['dr_pack_outs']['value'] + $result['DI'];
break;
case '1':
$pdtype = "DMPLUS";
$totals['dmplus_pack_outs']['count']++;
$totals['dmplus_pack_outs']['value'] = $totals['dmplus_pack_outs']['value'] + $result['DI'];
break;
case '2':
$pdtype = "PPI";
break;
case '3':
$pdtype = "DRPLUS";
$totals['dr_pack_outs']['count']++;
$totals['dr_pack_outs']['value'] = $totals['dr_pack_outs']['value'] + $result['DI'];
break;
default:
$pdtype = "";
break;
}
$po_result_parse[] = array('<a href="/reports/change_offices/' . $result['ClientID'] . '/">' . $result['ClientID'] . '</a>', $result['Dialler Lead ID'], $result['Name'], $result['Lead Source'], $result['Office'], $result['Telesales Agent'], $result['Consolidator'], $result['DI'], $result['Delivery'], $pdtype, date("d-m-y", strtotime($result['Referred Date'])));
$totals['pack_outs']['count']++;
$totals['pack_outs']['value'] = $totals['pack_outs']['value'] + $result['DI'];
}
}
}
}
$pack_ins1 = DB::query("SELECT CLD.ClientID AS 'Leadpool ID'\n\t\t\t\t\t ,D_CPD.ClientID AS ClientID\n\t\t\t\t\t ,CLD.LeadRef AS 'Dialler Lead ID'\n\t\t\t\t ,(CD.Forename + ' ' + CD.Surname) AS Name\n\t\t\t\t , CASE WHEN D_CLD.SourceID > 0 THEN (SELECT TOP (1) Description FROM Debtsolv_GABFS.dbo.Type_Lead_Source WHERE ID=D_CLD.SourceID)\n WHEN LBA.LeadSourceID > 0 THEN (SELECT TOP (1) Description FROM Debtsolv_GABFS.dbo.Type_Lead_Source WHERE ID=LBA.LeadSourceID)\n ELSE (SELECT TOP (1) Description FROM Leadpool_GABFS.dbo.Type_Lead_Source WHERE Reference COLLATE DATABASE_DEFAULT=DI_REF.list_id COLLATE DATABASE_DEFAULT)\n END AS 'Lead Source'\n\t\t\t\t ,CLD.LeadRef2 AS Office\n\t\t\t\t ,TCR.[Description]\n\t\t\t\t \n\t\t\t\t , CASE\n\t\t\t\t \t\tWHEN\n\t\t\t\t \t\t\tDI_REF.full_name = ' '\n\t\t\t\t \t\tTHEN\n\t\t\t\t \t\t\t'NONE'\n\t\t\t\t \t\tELSE\n\t\t\t\t \t\t\tISNULL(DI_REF.full_name,D_U.Undersigned)\n\t\t\t\t \t\tEND AS 'Telesales Agent'\n\t\t\t \n\t\t\t\t ,ISNULL((\n\t\t\t\t SELECT Top (1)\n\t\t\t\t Undersigned\n\t\t\t\t FROM\n\t\t\t\t Debtsolv_GABFS.dbo.Users AS D_URS\n\t\t\t\t LEFT JOIN\n\t\t\t\t Debtsolv_GABFS.dbo.Client_LeadData AS D_CLD ON D_URS.ID = D_CLD.Counsellor\n\t\t\t\t WHERE\n\t\t\t\t D_CLD.LeadPoolReference = CLD.ClientID\n\t\t\t\t ),(SELECT Top (1)\n\t\t\t\t \t Undersigned\n\t\t\t\t \tFROM\n\t\t\t\t \t Debtsolv_GABFS.dbo.Users AS DURS\n\t\t\t\t \tLEFT JOIN\n\t\t\t\t \t Leadpool_GABFS.dbo.CampaignContactAccess AS CCA ON DURS.ID = CCA.UserID\n\t\t\t\t \tWHERE\n\t\t\t\t \t CCA.CampaignContactID = CC.ID\n\t\t\t\t \tORDER BY\n\t\t\t\t \tCCA.AccessDate DESC)) AS 'Consolidator'\n\t\t\t\t \n\t\t\t\t ,CASE\n\t\t\t\t WHEN D_CPD.InitialAgreedAmount > 0 AND CC.ContactResult = 1500\n\t\t\t\t THEN 'DR'\n\t\t\t\t WHEN (D_CPD.InitialAgreedAmount is null OR D_CPD.InitialAgreedAmount <= 0) AND CC.ContactResult = 1500\n\t\t\t\t THEN 'PPI'\n\t\t\t\t ELSE\n\t\t\t\t ''\n\t\t\t\t END AS Product\n\t\t\t\t ,D_CPD.NormalExpectedPayment / 100 AS DI\n\t\t\t\t ,(\n\t\t\t\t \tSELECT Top (1)\n\t\t\t\t \t\tResponseText\n\t\t\t\t \tFROM\n\t\t\t\t \t\tDebtsolv_GABFS.dbo.Client_CustomQuestionResponses\n\t\t\t\t \tWHERE\n\t\t\t\t \t\tQuestionID = 10001\n\t\t\t\t \t\tAND ClientID = D_CLD.Client_ID\n\t\t\t\t ) AS 'Delivery'\n\t\t\t\t ,CONVERT(varchar, D_CLD.DatePackReceived, 105) AS 'Pack In Date'\n\t\t\t\t ,CONVERT(varchar, CLD.DateCreated, 120) AS 'Referred Date'\n\t\t\t\t ,CONVERT(varchar, CC.LastContactAttempt, 120) AS 'Last Contact Date'\n\t\t\t\t ,CASE\n\t\t\t\t WHEN CC.ContactResult = 700\n\t\t\t\t THEN CONVERT(varchar, CC.Appointment, 120)\n\t\t\t\t ELSE\n\t\t\t\t ''\n\t\t\t\t END AS 'Call Back Date'\n\t\t\t\t FROM\n\t\t\t\t Leadpool_GABFS.dbo.Client_LeadDetails AS CLD\n\t\t\t\t LEFT JOIN\n\t\t\t\t Leadpool_GABFS.dbo.Client_Details AS CD ON CLD.ClientID = CD.ClientID\n\t\t\t\t LEFT JOIN\n\t\t\t\t Leadpool_GABFS.dbo.Campaign_Contacts AS CC ON CLD.ClientID = CC.ClientID\n\t\t\t\t LEFT JOIN\n\t\t\t\t Leadpool_GABFS.dbo.Type_ContactResult AS TCR ON CC.ContactResult = TCR.ID\n\t\t\t\t LEFT JOIN\n\t\t\t\t\tLeadpool_GABFS.dbo.LeadBatch AS LBA ON CLD.LeadBatchID = LBA.ID\n\t\t\t\t LEFT JOIN\n\t\t\t\t\tLeadpool_GABFS.dbo.Type_Lead_Source AS LSO ON LBA.LeadSourceID = LSO.ID\n\t\t\t\t LEFT JOIN\n\t\t\t\t Debtsolv_GABFS.dbo.Client_LeadData AS D_CLD ON CLD.ClientID = D_CLD.LeadPoolReference\n\t\t\t\t LEFT JOIN\n\t\t\t\t Debtsolv_GABFS.dbo.Users AS D_U ON D_CLD.TelesalesAgent = D_U.ID\n\t\t\t\t LEFT JOIN\n\t\t\t\t Debtsolv_GABFS.dbo.Client_PaymentData AS D_CPD ON D_CLD.Client_ID = D_CPD.ClientID\n\t\t\t\t LEFT JOIN\n\t\t\t\t \tDialler.dbo.referrals AS DI_REF ON CLD.ClientID = DI_REF.leadpool_id\n\t\t\t\t WHERE \n\t\t\t\t \t" . $pack_in_duration . "\n\t\t\t\t \t" . $call_center_choice . "\n\t\t\t\t \tAND D_U.Undersigned <> 'FAB Admin'\n\t\t\t\t \tAND D_CPD.NormalExpectedPayment > 0\n\t\t\t\t ORDER BY\n\t\t\t\t\tCLD.LeadRef2\n\t\t\t\t ,TCR.[Description]\n\t\t\t\t ,Product\n\t\t\t\t ,CLD.DateCreated DESC")->cached(300)->execute('debtsolv');
$pack_ins = array();
foreach ($pack_ins1 as $pi) {
$pack_ins[] = $pi;
}
foreach ($pack_ins2 as $pi) {
$pack_ins[] = $pi;
}
$all_pack_in = array();
foreach ($pack_ins as $pack_in) {
$all_pack_in[] = array('<a href="/reports/change_offices/' . $pack_in['ClientID'] . '/">' . $pack_in['ClientID'] . '</a>', $pack_in['Dialler Lead ID'], $pack_in['Name'], $pack_in['Lead Source'], $pack_in['Office'], $pack_in['Telesales Agent'], $pack_in['Consolidator'], $pack_in['DI'], $pack_in['Delivery'], date("d-m-y", strtotime($pack_in['Referred Date'])), date("d-m-y", strtotime($pack_in['Pack In Date'])));
$totals['pack_ins']['count']++;
$totals['pack_ins']['value'] = $totals['pack_ins']['value'] + $pack_in['DI'];
}
$paid_duration = "(D_CD.FirstPaymentDate >= '" . date('Y-m-d', strtotime($start_date)) . "' AND D_CD.FirstPaymentDate <= '" . date('Y-m-d', strtotime($end_date)) . "') ";
$paid_reports1 = \DB::query("SELECT\n D_CD.ClientID\n , D_CLD.LeadPoolReference\n , (CD.Forename + ' ' + CD.Surname) AS Name\n , CASE WHEN D_CLD.SourceID > 0 THEN (SELECT TOP (1) Description FROM Debtsolv_GABFS.dbo.Type_Lead_Source WHERE ID=D_CLD.SourceID)\n WHEN LBA.LeadSourceID > 0 THEN (SELECT TOP (1) Description FROM Debtsolv_GABFS.dbo.Type_Lead_Source WHERE ID=LBA.LeadSourceID)\n ELSE (SELECT TOP (1) Description FROM Leadpool_GABFS.dbo.Type_Lead_Source WHERE Reference COLLATE DATABASE_DEFAULT=DI_REF.list_id COLLATE DATABASE_DEFAULT)\n END AS 'Lead Source'\n\t\t\t\t\t\t\t\t\t\t\t , CLD.LeadRef2 AS Office\n\t\t\t\t\t\t\t\t\t\t\t , CASE\n \t\t\t\t \t\tWHEN\n \t\t\t\t \t\t\tDI_REF.full_name = ' '\n \t\t\t\t \t\tTHEN\n \t\t\t\t \t\t\t'NONE'\n \t\t\t\t \t\tELSE\n \t\t\t\t \t\t\tISNULL(DI_REF.full_name,D_U.Undersigned)\n \t\t\t\t \t\tEND AS 'Telesales Agent'\n \t\t\t\t \t\t\t\t\t\t ,ISNULL((\n \t\t\t\t SELECT Top (1)\n \t\t\t\t Undersigned\n \t\t\t\t FROM\n \t\t\t\t Debtsolv_GABFS.dbo.Users AS D_URS\n \t\t\t\t LEFT JOIN\n \t\t\t\t Debtsolv_GABFS.dbo.Client_LeadData AS D_CLD ON D_URS.ID = D_CLD.Counsellor\n \t\t\t\t WHERE\n \t\t\t\t D_CLD.LeadPoolReference = CLD.ClientID\n \t\t\t\t ),(SELECT Top (1)\n \t\t\t\t \t Undersigned\n \t\t\t\t \tFROM\n \t\t\t\t \t Debtsolv_GABFS.dbo.Users AS DURS\n \t\t\t\t \tLEFT JOIN\n \t\t\t\t \t Leadpool_GABFS.dbo.CampaignContactAccess AS CCA ON DURS.ID = CCA.UserID\n \t\t\t\t \tWHERE\n \t\t\t\t \t CCA.CampaignContactID = CC.ID\n \t\t\t\t \tORDER BY\n \t\t\t\t \t CCA.AccessDate DESC)) AS 'Consolidator'\n , D_CPD.NormalExpectedPayment/100 AS DI\n ,(\n \t\t\t \tSELECT Top (1)\n \t\t\t \t\tResponseVal\n \t\t\t \tFROM\n \t\t\t \t\tDebtsolv_GABFS.dbo.Client_CustomQuestionResponses\n \t\t\t \tWHERE\n \t\t\t \t\tQuestionID = 10007\n \t\t\t \t\tAND ClientID = D_CLD.Client_ID\n \t\t\t ) AS 'ProductType'\n ,CONVERT(varchar, CLD.DateCreated, 120) AS 'Referred Date'\n ,CONVERT(varchar, D_CLD.DatePackReceived, 105) AS 'Pack In Date'\n , D_CD.FirstPaymentDate\n FROM\n Dialler.dbo.client_dates AS D_CD\n LEFT JOIN\n Debtsolv_GABFS.dbo.Client_PaymentData AS D_CPD ON D_CPD.ClientID=D_CD.ClientID\n LEFT JOIN\n Debtsolv_GABFS.dbo.Client_LeadData AS D_CLD ON D_CD.ClientID = D_CLD.Client_ID\n LEFT JOIN\n Dialler.dbo.referrals AS DI_REF ON D_CLD.LeadPoolReference = DI_REF.leadpool_id\n LEFT JOIN\n Leadpool_GABFS.dbo.Client_LeadDetails AS CLD ON CLD.ClientID = D_CLD.LeadPoolReference\n LEFT JOIN\n Leadpool_GABFS.dbo.Client_Details AS CD ON D_CLD.LeadPoolReference = CD.ClientID\n LEFT JOIN\n Leadpool_GABFS.dbo.LeadBatch AS LBA ON CLD.LeadBatchID = LBA.ID\n LEFT JOIN\n Leadpool_GABFS.dbo.Type_Lead_Source AS LSO ON LBA.LeadSourceID = LSO.ID\n\t\t\t\t LEFT JOIN\n\t\t\t\t Leadpool_GABFS.dbo.Campaign_Contacts AS CC ON CLD.ClientID = CC.ClientID\n\t\t\t\t LEFT JOIN\n\t\t\t\t Debtsolv_GABFS.dbo.Users AS D_U ON D_CLD.TelesalesAgent = D_U.ID\n WHERE\n " . $paid_duration . "\n " . $call_center_choice . "\n AND D_CD.Office = 'GAB'\n\t\t\t\t")->cached(300)->execute('debtsolv');
$all_paid = array();
foreach ($paid_reports1 as $paid) {
$pdtype = "";
switch ((string) $paid['ProductType']) {
case '0':
$pdtype = "DR";
break;
case '1':
$pdtype = "DMPLUS";
break;
case '2':
$pdtype = "PPI";
break;
case '3':
$pdtype = "DRPLUS";
break;
case '':
$pdtype = "";
break;
}
$all_paid['G' . $paid['ClientID']] = array('<a href="/reports/change_offices/' . $paid['ClientID'] . '/">' . $paid['ClientID'] . '</a>', $paid['LeadPoolReference'], $paid['Name'], $paid['Lead Source'], $paid['Office'], $paid['Telesales Agent'], $paid['Consolidator'], $paid['DI'], $pdtype, date("d-m-y", strtotime($paid['Referred Date'])), date("d-m-y", strtotime($paid['Pack In Date'])), date("d-m-y", strtotime($paid['FirstPaymentDate'])));
$totals['paid']['count']++;
$totals['paid']['value'] = $totals['paid']['value'] + $paid['DI'];
}
foreach ($paid_reports2 as $paid) {
$pdtype = "";
switch ((string) $paid['ProductType']) {
case '0':
$pdtype = "DR";
break;
case '1':
$pdtype = "DMPLUS";
break;
case '2':
$pdtype = "PPI";
break;
case '3':
$pdtype = "DRPLUS";
break;
case '':
$pdtype = "";
break;
}
$all_paid['R' . $paid['ClientID']] = array('<a href="/reports/change_offices/' . $paid['ClientID'] . '/">' . $paid['ClientID'] . '</a>', $paid['LeadPoolReference'], $paid['Name'], $paid['Lead Source'], '<a href="/reports/change_offices/' . $paid['ClientID'] . '/">' . $paid['Office'] . '</a>', '<a href="/reports/change_offices/' . $paid['ClientID'] . '/">' . $paid['Telesales Agent'] . '</a>', $paid['Consolidator'], $paid['DI'], $pdtype, date("d-m-y", strtotime($paid['Referred Date'])), date("d-m-y", strtotime($paid['Pack In Date'])), date("d-m-y", strtotime($paid['FirstPaymentDate'])));
$totals['paid']['count']++;
$totals['paid']['value'] = $totals['paid']['value'] + $paid['DI'];
}
$totals['paid']['count'] = 0;
$totals['paid']['value'] = 0;
$all_paid_return = array();
foreach ($all_paid as $allPaid) {
$all_paid_return[] = $allPaid;
$totals['paid']['count']++;
$totals['paid']['value'] = $totals['paid']['value'] + $allPaid[7];
}
// arrange the totals
$totals['referrals']['value'] = number_format($totals['referrals']['value'], 2);
$totals['pack_outs']['value'] = number_format($totals['pack_outs']['value'], 2);
$totals['dr_pack_outs']['value'] = number_format($totals['dr_pack_outs']['value'], 2);
$totals['dmplus_pack_outs']['value'] = number_format($totals['dmplus_pack_outs']['value'], 2);
$totals['pack_ins']['value'] = number_format($totals['pack_ins']['value'], 2);
$totals['paid']['value'] = number_format($totals['paid']['value'], 2);
return array('status' => 'SUCCESS', 'totals' => $totals, 'paid' => array("aaData" => $all_paid_return, "bPaginate" => false, "bDestroy" => true, "bProcessing" => true, "aoColumnDefs" => array(array("iDataSort" => 2, "asSorting" => array("asc"), "aTargets" => array(0))), "aoColumns" => array(array("sTitle" => "Client ID", "bSortable" => false), array("sTitle" => "Leadpool ID", "bSortable" => false), array("sTitle" => "Name", "sType" => "string"), array("sTitle" => "Lead Source", "sType" => "string"), array("sTitle" => "Office", "sType" => "string"), array("sTitle" => "Telesales", "sType" => "string"), array("sTitle" => "Consolidator", "sType" => "string"), array("sTitle" => "DI", "sType" => "numeric"), array("sTitle" => "Product", "sType" => "string"), array("sTitle" => "Referred", "sType" => "date-uk"), array("sTitle" => "Pack In", "sType" => "date-uk"), array("sTitle" => "First Payment", "sType" => "date-uk"))), 'no_contacts' => array("aaData" => $lost_parse, "bPaginate" => false, "bDestroy" => true, "bProcessing" => true, "aoColumnDefs" => array(array("iDataSort" => 2, "asSorting" => array("asc"), "aTargets" => array(0))), "aoColumns" => array(array("sTitle" => "Leadpool ID", "bSortable" => false), array("sTitle" => "Dialler ID", "bSortable" => false), array("sTitle" => "Name", "sType" => "string"), array("sTitle" => "Lead Source", "sType" => "string"), array("sTitle" => "Office", "sType" => "string"), array("sTitle" => "Telesales", "sType" => "string"), array("sTitle" => "Consolidator", "sType" => "string"), array("sTitle" => "Result", "sType" => "string"), array("sTitle" => "Referred", "sType" => "date-uk"))), 'referrals' => array("aaData" => $result_parse, "bDestroy" => true, "bPaginate" => false, "bProcessing" => true, "aoColumnDefs" => array(array("iDataSort" => 2, "asSorting" => array("asc"), "aTargets" => array(0))), "aoColumns" => array(array("sTitle" => "Leadpool ID", "bSortable" => false), array("sTitle" => "Dialler ID", "bSortable" => false), array("sTitle" => "Name", "sType" => "string"), array("sTitle" => "Lead Source", "sType" => "string"), array("sTitle" => "Office", "sType" => "string"), array("sTitle" => "Telesales", "sType" => "string"), array("sTitle" => "Consolidator", "sType" => "string"), array("sTitle" => "Result", "sType" => "string"), array("sTitle" => "DI", "sType" => "numeric"), array("sTitle" => "Delivery", "sType" => "string"), array("sTitle" => "Product", "sType" => "string"), array("sTitle" => "Referred", "sType" => "date-uk"), array("sTitle" => "Last Contact", "sType" => "date-euro"), array("sTitle" => "Call Back", "sType" => "date-uk"))), 'pack_ins' => array("aaData" => $all_pack_in, "bDestroy" => true, "bPaginate" => false, "bProcessing" => true, "aoColumnDefs" => array(array("iDataSort" => 2, "asSorting" => array("asc"), "aTargets" => array(0))), "aoColumns" => array(array("sTitle" => "Debtsolv ID", "bSortable" => false), array("sTitle" => "Dialler ID", "bSortable" => false), array("sTitle" => "Name", "sType" => "string"), array("sTitle" => "Lead Source", "sType" => "string"), array("sTitle" => "Office", "sType" => "string"), array("sTitle" => "Telesales", "sType" => "string"), array("sTitle" => "Consolidator", "sType" => "string"), array("sTitle" => "DI", "sType" => "numeric"), array("sTitle" => "Delivery", "sType" => "string"), array("sTitle" => "Referred", "sType" => "date-uk"), array("sTitle" => "Pack In", "sType" => "date-uk"))), 'pack_outs' => array("aaData" => $po_result_parse, "bDestroy" => true, "bPaginate" => false, "bProcessing" => true, "aoColumnDefs" => array(array("iDataSort" => 2, "asSorting" => array("asc"), "aTargets" => array(0))), "aoColumns" => array(array("sTitle" => "Leadpool ID", "bSortable" => false), array("sTitle" => "Dialler ID", "bSortable" => false), array("sTitle" => "Name", "sType" => "string"), array("sTitle" => "Lead Source", "sType" => "string"), array("sTitle" => "Office", "sType" => "string"), array("sTitle" => "Telesales", "sType" => "string"), array("sTitle" => "Consolidator", "sType" => "string"), array("sTitle" => "DI", "sType" => "numeric"), array("sTitle" => "Delivery", "sType" => "string"), array("sTitle" => "Product", "sType" => "string"), array("sTitle" => "Referred", "sType" => "date-uk"))));
}
} else {
$this->response(array('status' => 'FAIL', 'message' => 'You do not have access to the disposition report.'));
}
}
