예제 #1
0
파일: ppi.php 프로젝트: ClixLtd/pccupload
 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));
 }
예제 #2
0
 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 &pound;' . $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), "&pound;" . number_format($inpayValues['amount'], 2), $inpayValues['total'] < 0.1 ? "&pound;0.00" : "&pound;" . 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);
 }
예제 #3
0
파일: test.php 프로젝트: ClixLtd/pccupload
 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()));
 }