public function action_disposition()
{
$this->template->title = "PPI Disposition Report";
if (\Auth::has_access('reports.all_centers')) {
$center = \Input::post('center');
$view_all = TRUE;
} else {
$view_all = FALSE;
list($driver, $user_id) = \Auth::get_user_id();
$this_user = \Model_User::find($user_id);
$center = $this_user->call_center_id;
}
$all_call_centers = \Model_Call_Center::find('all');
$hourlyStatsReport = array('Referrals' => \Crm\Reports\Model_Ppi::perPeriod('referral', $center, \Input::post('startdate'), \Input::post('enddate')), 'Pack Outs' => \Crm\Reports\Model_Ppi::perPeriod('packOut', $center, \Input::post('startdate'), \Input::post('enddate')), 'Pack In' => \Crm\Reports\Model_Ppi::perPeriod('packIn', $center, \Input::post('startdate'), \Input::post('enddate')));
$quickView = array(array('No Contacts' => count(\Crm\Reports\Model_Ppi::simpleStats('noContact', $center, \Input::post('startdate'), \Input::post('enddate'))), 'Referrals' => count(\Crm\Reports\Model_Ppi::simpleStats('referral', $center, \Input::post('startdate'), \Input::post('enddate'))), 'Pack Outs' => count(\Crm\Reports\Model_Ppi::simpleStats('packOut', $center, \Input::post('startdate'), \Input::post('enddate'))), 'Pack Ins' => count(\Crm\Reports\Model_Ppi::packIns($center, \Input::post('startdate'), \Input::post('enddate')))));
$report = Report\Create::forge(array('hourlyStats' => array('reportResults' => $hourlyStatsReport, 'displayType' => 'chart'), 'noContacts' => array('reportResults' => \Crm\Reports\Model_Ppi::simpleStats('noContact', $center, \Input::post('startdate'), \Input::post('enddate')), 'options' => array('class' => 'zebra-striped datatable')), 'referrals' => array('reportResults' => \Crm\Reports\Model_Ppi::simpleStats('referral', $center, \Input::post('startdate'), \Input::post('enddate')), 'options' => array('class' => 'zebra-striped datatable')), 'packOuts' => array('reportResults' => \Crm\Reports\Model_Ppi::simpleStats('packOut', $center, \Input::post('startdate'), \Input::post('enddate')), 'options' => array('class' => 'zebra-striped datatable')), 'packIns' => array('reportResults' => \Crm\Reports\Model_Ppi::packIns($center, \Input::post('startdate'), \Input::post('enddate')), 'options' => array('class' => 'zebra-striped datatable')), 'quickView' => array('reportResults' => $quickView, 'options' => array('direction' => 'vertical'))), 3600);
$this->template->content = View::forge(static::$_viewPath . 'ppi/disposition.php', array('reports' => $report->generate(), 'all_call_centers' => $all_call_centers, 'center' => $center, 'start_date' => \Input::post('startdate'), 'end_date' => \Input::post('enddate'), 'view_all' => $view_all));
}
public static function generate_monthly_payment_report($center = null, $_startDate = null, $_endDate = null)
{
$startDate = is_null($_startDate) ? date('Y-m-d', mktime(0, 0, 0, (int) date('m'), 1, (int) date('Y'))) : $_startDate;
$endDate = is_null($_endDate) ? date('Y-m-d', mktime(0, 0, 0, (int) date('m'), (int) date('d'), (int) date('Y'))) : $_endDate;
$db_choice = array('GAB' => array('DS' => 'Debtsolv', 'LP' => 'Leadpool_GABFS', 'QU' => 'Office <> \'RESOLVE\''), 'RESOLVE' => array('DS' => 'BS_Debtsolv_DM', 'LP' => 'BS_Leadpool_DM', 'QU' => 'Office = \'RESOLVE\''));
$thisDB = $db_choice[is_null($center) ? 'GAB' : $center];
$quickViewStartDate = strtotime("-18 months");
$quickViewCountQuery = "SELECT\n\t REPLACE(CONVERT(VARCHAR(7), P_R.Date, 111), '/', '-') AS Month\n\t, COUNT(DISTINCT P_R.ClientID) as totalCount\nFROM \n\t" . $thisDB['DS'] . ".dbo.Payment_Receipt AS P_R\nWHERE\n\tP_R.Date >= '" . date('Y-m-01', $quickViewStartDate) . "' AND P_R.Date <= '" . date('Y-m-d') . "'\nGROUP BY\n\tREPLACE(CONVERT(VARCHAR(7), P_R.Date, 111), '/', '-')\nORDER BY\n\tREPLACE(CONVERT(VARCHAR(7), P_R.Date, 111), '/', '-')";
$getGraphDetails = DB::query($quickViewCountQuery)->cached(300)->execute('debtsolv');
$expectedPaymentsQuery = "SELECT\n\t CC.ID as ClientID\n\t, (CC.Forename + ' ' + CC.Surname) AS ClientName\n\t, ps.DateExpected\n\t, (ps.Amount+ps.OvertimeAmount+ps.AdditionalAmount)/100 AS AmountExpected\n\t, ISNULL(PR.Amount,0)/100 AS AmountReceived\n\t, ISNULL(PR.Date,'31 dec 1899') AS 'Date Received'\n\t, CASE WHEN PR.ID IS null THEN 'Migrated Payment' ELSE 'Client Payment' END As 'Receipt Type'\nFROM \n\t" . $thisDB['DS'] . ".dbo.Payment_Schedule AS ps\nINNER JOIN\n\t" . $thisDB['DS'] . ".dbo.Client_Contact AS CC ON ps.ClientID = CC.ID\nINNER JOIN \n\t" . $thisDB['DS'] . ".dbo.Client_LeadData AS CLD ON CC.ID = CLD.Client_ID\nLEFT OUTER JOIN \n\t" . $thisDB['DS'] . ".dbo.PaymentSchedule_AllocationHistory AS psah ON ps.ID = psah.ScheduleID\nLEFT OUTER JOIN\n\t" . $thisDB['DS'] . ".dbo.Payment_Receipt AS PR ON psah.ReceiptID = PR.ID\nINNER JOIN\n\t" . $thisDB['DS'] . ".dbo.Users AS Admin ON CLD.Administrator = Admin.ID\nINNER JOIN\n\t" . $thisDB['DS'] . ".dbo.Users AS Credit ON CLD.CreditController = Credit.ID \nWHERE\n\t(ps.DateExpected >= '" . $startDate . "' AND ps.DateExpected < '" . $endDate . "')\n\tAND CC.status = 9\nORDER BY \n\tps.DateExpected";
$expectedPaymentDetails = DB::query($expectedPaymentsQuery)->cached(300)->execute('debtsolv');
$monthPaymentsQuery = "SELECT\n\t D_PA.ClientID\n\t, (D_CC.Forename + ' ' + D_CC.Surname) AS Name\n\t, D_PA.Amount/100 AS AmountIn\n\t, D_CPD.NormalExpectedPayment/100 AS NormalExpectedPayment\n\t, D_LI.Name AS Introducer\n\t, ISNULL(L_CLD.LeadRef2, 'NONE') AS Shortcode\n\t, (SELECT SUM(CASE WHEN EstimatedBalance > 0 THEN EstimatedBalance ELSE AmountOwed END)/100 FROM " . $thisDB['DS'] . ".[dbo].[Finstat_Debt] WHERE ClientID = D_PA.ClientID) AS TotalOwed\n\t, (SELECT Top (1) ResponseVal FROM Debtsolv_GABFS.dbo.Client_CustomQuestionResponses WHERE QuestionID = 10007 AND ClientID = D_CLD.Client_ID) AS 'ProductType'\nFROM\n\t" . $thisDB['DS'] . ".dbo.Payment_Receipt AS D_PA\nLEFT JOIN\n\t" . $thisDB['DS'] . ".dbo.Client_PaymentData AS D_CPD ON D_PA.ClientID = D_CPD.ClientID\nLEFT JOIN\n\t" . $thisDB['DS'] . ".dbo.Client_Contact AS D_CC ON D_PA.ClientID = D_CC.ID\nLEFT JOIN\n " . $thisDB['DS'] . ".dbo.Client_LeadData AS D_CLD ON D_PA.ClientID = D_CLD.Client_ID\nLEFT JOIN\n " . $thisDB['DS'] . ".dbo.Type_Lead_Source AS D_TLS ON D_CLD.SourceID=D_TLS.ID\nLEFT JOIN\n " . $thisDB['DS'] . ".dbo.Lead_Introducers AS D_LI ON D_TLS.IntroducerID=D_LI.ID\nLEFT JOIN \n " . $thisDB['LP'] . ".dbo.Client_LeadDetails AS L_CLD ON D_CLD.LeadPoolReference = L_CLD.ClientID\nWHERE\n\t(D_PA.Date >= '" . $startDate . "' AND D_PA.Date < '" . $endDate . "')";
$getPayments = DB::query($monthPaymentsQuery)->cached(300)->execute('debtsolv');
$monthFirstPaymentsQuery = "SELECT\n REPLACE(CONVERT(VARCHAR(7), FirstPaymentDate, 111), '/', '-') AS Month\n , COUNT(DISTINCT ClientID) AS Total\nFROM \n\t[Dialler].[dbo].[client_dates]\nWHERE \n\t" . $thisDB['QU'] . "\n\tAND (FirstPaymentDate >= '" . date('Y-m-01', $quickViewStartDate) . "' AND FirstPaymentDate <= '" . date('Y-m-d') . "')\nGROUP BY\n\tREPLACE(CONVERT(VARCHAR(7), FirstPaymentDate, 111), '/', '-')";
$getFirstPayments = DB::query($monthFirstPaymentsQuery)->cached(300)->execute('debtsolv');
$clientPayments = array();
$introducerPayments = array();
$completedClients = array();
foreach ($getPayments as $payment) {
if (!in_array($payment['ClientID'], $completedClients)) {
$paymentTotal = isset($clientPayments[$payment['ClientID']]['AmountIn']) ? $clientPayments[$payment['ClientID']]['AmountIn'] + $payment['AmountIn'] : $payment['AmountIn'];
$paymentCount = isset($clientPayments[$payment['ClientID']]['count']) ? $clientPayments[$payment['ClientID']]['count'] + 1 : 1;
$shortCodeChange = array("Games Blaster" => "GAB", "Gregson and Brooke" => "GAB", "Phillipines" => "GBS", "PPI - 03-11-2011" => "GAB", "PPI - 17-11-2011" => "GAB", "PPI - 19-11-2011" => "GAB", "PPI - 21-11-2011" => "GAB", "Teleprospects" => "GAB", "UCS" => "GAB", "Unique Prospects" => "GAB", "60k Home Owner" => "GAB", "Data Compiled 2011" => "GAB", "Data Compiled 2012" => "GAB", "Dialler Manual Dial" => "GAB", "Digos Call Centre" => "GBS", "Digos Call Centre (Post PPI)" => "GBS", "DK101" => "GAB", "GAB Debt Hotkeys" => "GAB", "JPO" => "GAB", "DLG" => "GAB");
if ($payment['Shortcode'] != 'NONE' && $payment['Shortcode'] != '' && $payment['Shortcode'] != ' ') {
if (isset($shortCodeChange[(string) $payment['Shortcode']])) {
$introducerTitle = $shortCodeChange[(string) $payment['Shortcode']];
} else {
$introducerTitle = (string) $payment['Shortcode'];
}
} else {
if (isset($shortCodeChange[(string) $payment['Introducer']])) {
$introducerTitle = $shortCodeChange[(string) $payment['Introducer']];
} else {
$introducerTitle = (string) $payment['Introducer'];
}
}
$introducerPayments[$introducerTitle] = array('amount' => isset($introducerPayments[$introducerTitle]) ? $introducerPayments[$introducerTitle]['amount'] + $payment['AmountIn'] : $payment['AmountIn'], 'total' => isset($clientPayments[$payment['ClientID']]) ? $introducerPayments[$introducerTitle]['total'] : $introducerPayments[$introducerTitle]['total'] + 1);
$pdtype = "";
switch ((string) $payment['ProductType']) {
case '0':
$pdtype = "DR";
break;
case '1':
$pdtype = "DMPLUS";
break;
case '2':
$pdtype = "PPI";
break;
case '3':
$pdtype = "DRPLUS";
break;
case '':
$pdtype = "";
break;
}
$clientPayments[] = array($payment['ClientID'], $payment['Name'], $introducerTitle, $paymentTotal, $payment['NormalExpectedPayment'], $payment['TotalOwed'], $pdtype, $paymentTotal >= $payment['NormalExpectedPayment'] ? 'Full payment made in ' . $paymentCount . ' payments.' : 'DI of £' . $payment['NormalExpectedPayment'] . ' not reached, ' . $paymentCount . ' payments made.', $paymentTotal >= $payment['NormalExpectedPayment'] ? TRUE : FALSE);
$completedClients[] = $payment['ClientID'];
}
}
// Parse Graph
$graphDetails = array();
foreach ($getGraphDetails as $graph) {
$date = explode('-', $graph['Month']);
$graphDetails[date("M Y", mktime(0, 0, 0, (int) $date[1], 1, $date[0]))] = $graph['totalCount'];
}
$graphDetails2 = array();
$graphDetails3 = array();
foreach ($getFirstPayments as $graph) {
$date = explode('-', $graph['Month']);
$graphDetails2[date("M Y", mktime(0, 0, 0, (int) $date[1], 1, $date[0]))] = (int) $graphDetails[date("M Y", mktime(0, 0, 0, (int) $date[1], 1, $date[0]))] - (int) $graph['Total'];
$graphDetails3[date("M Y", mktime(0, 0, 0, (int) $date[1], 1, $date[0]))] = (int) $graph['Total'];
}
$report = Report\Create::forge(array('monthlyStats' => array('reportResults' => array('Unique Payments' => $graphDetails, 'Regular Payments' => $graphDetails2, 'First Payments' => $graphDetails3), 'displayType' => 'chart')), 3600);
$introducerPaymentsReturn = array();
foreach ($introducerPayments as $inpayName => $inpayValues) {
$introducerPaymentsReturn[] = array($inpayName, number_format($inpayValues['total'], 0), "£" . number_format($inpayValues['amount'], 2), $inpayValues['total'] < 0.1 ? "£0.00" : "£" . number_format($inpayValues['amount'] / $inpayValues['total'], 2));
}
$expectedPayments = array();
foreach ($expectedPaymentDetails as $expected) {
$expectedPayments[] = array($expected['ClientID'], $expected['ClientName'], $expected['DateExpected'], $expected['AmountExpected'], $expected['AmountReceived'], (int) $expected['AmountReceived'] >= (int) $expected['AmountExpected'] ? TRUE : FALSE);
}
return array('reports' => $report->generate(), 'clients' => $clientPayments, 'expected' => $expectedPayments, 'introducer' => $introducerPaymentsReturn);
}
public function action_report()
{
$report = Report\Create::forge(array('Report1' => array('reportResults' => array(), 'displayType' => 'table', 'options' => array())), 3600);
$this->template->title = "Reporting Test";
$this->template->content = View::forge('test/report', array('reports' => $report->generate()));
}