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.')); } }