Exemplo n.º 1
0
 function fetch_report_data()
 {
     global $FANNIE_OP_DB, $FANNIE_COOP_ID;
     $d1 = $this->form->date1;
     $d2 = $this->form->date2;
     $dept = FormLib::get_form_value('dept', 0);
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     // Can dlog views if they include cost.
     $dtrans = DTransactionsModel::select_dtrans($d1, $d2);
     $datestamp = $dbc->identifier_escape('datetime');
     if (isset($FANNIE_COOP_ID) && $FANNIE_COOP_ID == 'WEFC_Toronto') {
         $shrinkageUsers = " AND (t.card_no not between 99900 and 99998)";
     } else {
         $shrinkageUsers = "";
     }
     // The eventual return value.
     $data = array();
     $taxNames = array(0 => '');
     $tQ = $dbc->prepare_statement("SELECT id, rate, description FROM taxrates WHERE id > 0 ORDER BY id");
     $tR = $dbc->exec_statement($tQ);
     // Try generating code in this loop for use in SELECT and reporting.
     //  See SalesAndTaxTodayReport.php
     while ($trow = $dbc->fetch_array($tR)) {
         $taxNames[$trow['id']] = $trow['description'];
     }
     /**
       Margin column was added to departments but if
       deptMargin is present data may not have been migrated
     */
     $margin = 'd.margin';
     $departments_table = $dbc->tableDefinition('departments');
     if ($dbc->tableExists('deptMargin')) {
         $margin = 'm.margin';
     } elseif (!isset($departments_table['margin'])) {
         $margin = '0.00';
     }
     /* Using department settings at the time of sale.
      * I.e. The department# from the transaction.
      *  If that department# no longer exists or is different then the report will be wrong.
      *  This does not use a departments table contemporary with the transactions.
      * [0]Dept_name [1]Cost, [2]HST, [3]GST, [4]Sales, [x]Qty, [x]superID, [x]super_name
      */
     if ($dept == 0) {
         // Change varname to sales or totals
         $costs = "SELECT\n                    d.dept_name dname,\n                    sum(CASE WHEN t.trans_type = 'I' THEN t.cost \n                         WHEN t.trans_type = 'D' AND {$margin} > 0.00 \n                         THEN t.total - (t.total * {$margin}) END) AS costs,\n                    sum(CASE WHEN t.tax = 1 THEN t.total * x.rate ELSE 0 END) AS taxes1,\n                    sum(CASE WHEN t.tax = 2 THEN t.total * x.rate ELSE 0 END) AS taxes2,\n                    sum(t.total) AS sales,\n                    sum(t.quantity) AS qty,\n                    s.superID AS sid,\n                    s.super_name AS sname\n                FROM\n                    {$dtrans} AS t LEFT JOIN\n                    departments AS d ON d.dept_no=t.department LEFT JOIN\n                    MasterSuperDepts AS s ON t.department=s.dept_ID LEFT JOIN\n                    taxrates AS x ON t.tax=x.id ";
         if ($margin == 'm.margin') {
             $costs .= " LEFT JOIN deptMargin AS m ON t.department=m.dept_id ";
         }
         $costs .= "\n                WHERE \n                    ({$datestamp} BETWEEN ? AND ?)\n                    AND (s.superID > 0 OR s.superID IS NULL) \n                    AND t.trans_type in ('I','D')\n                    AND t.trans_status not in ('D','X','Z')\n                    AND t.emp_no not in (9999){$shrinkageUsers}\n                    AND t.register_no != 99\n                    AND t.upc != 'DISCOUNT'\n                    AND t.trans_subtype not in ('CP','IC')\n                GROUP BY\n                    s.superID, s.super_name, d.dept_name, t.department\n                ORDER BY\n                    s.superID, t.department";
     } elseif ($dept == 1) {
         $costs = "SELECT\n                CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name END AS dname,\n                sum(CASE WHEN t.trans_type = 'I' THEN t.cost \n                     WHEN t.trans_type = 'D' AND {$margin} > 0.00 \n                     THEN t.total - (t.total * {$margin}) END) AS costs,\n                sum(CASE WHEN t.tax = 1 THEN t.total * x.rate ELSE 0 END) AS taxes1,\n                sum(CASE WHEN t.tax = 2 THEN t.total * x.rate ELSE 0 END) AS taxes2,\n                sum(t.total) AS sales,\n                sum(t.quantity) AS qty,\n                CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID END AS sid,\n                CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END AS sname\n            FROM\n                {$dtrans} AS t LEFT JOIN\n                products AS p ON t.upc=p.upc LEFT JOIN\n                departments AS d ON d.dept_no=t.department LEFT JOIN\n                departments AS e ON p.department=e.dept_no LEFT JOIN\n                MasterSuperDepts AS s ON s.dept_ID=p.department LEFT JOIN\n                MasterSuperDepts AS r ON r.dept_ID=t.department LEFT JOIN\n                taxrates AS x ON t.tax=x.id ";
         if ($margin == 'm.margin') {
             $costs .= " LEFT JOIN deptMargin AS m ON t.department=m.dept_id ";
         }
         $costs .= "\n            WHERE\n                ({$datestamp} BETWEEN ? AND ?)\n                AND (s.superID > 0 OR (s.superID IS NULL AND r.superID > 0)\n                    OR (s.superID IS NULL AND r.superID IS NULL))\n                AND t.trans_type in ('I','D')\n                AND t.trans_status not in ('D','X','Z')\n                AND t.emp_no not in (9999){$shrinkageUsers}\n                AND t.register_no != 99\n                AND t.upc != 'DISCOUNT'\n                AND t.trans_subtype not in ('CP','IC')\n            GROUP BY\n                CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,\n                CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END,\n                CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name end,\n                CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end\n            ORDER BY\n                CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,\n                CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end";
     }
     $costsP = $dbc->prepare_statement($costs);
     $costArgs = array($d1 . ' 00:00:00', $d2 . ' 23:59:59');
     $costsR = $dbc->exec_statement($costsP, $costArgs);
     // Array in which totals used in the report are accumulated.
     $supers = array();
     $curSuper = 0;
     $grandTotal = 0;
     $this->grandCostsTotal = 0;
     $this->grandSalesTotal = 0;
     $this->grandTax1Total = 0;
     $this->grandTax2Total = 0;
     while ($row = $dbc->fetch_array($costsR)) {
         if ($curSuper != $row['sid']) {
             $curSuper = $row['sid'];
         }
         if (!isset($supers[$curSuper])) {
             $supers[$curSuper] = array('name' => $row['sname'], 'qty' => 0.0, 'costs' => 0.0, 'sales' => 0.0, 'taxes1' => 0.0, 'taxes2' => 0.0, 'depts' => array());
         }
         $supers[$curSuper]['qty'] += $row['qty'];
         $supers[$curSuper]['costs'] += $row['costs'];
         $supers[$curSuper]['sales'] += $row['sales'];
         $supers[$curSuper]['taxes1'] += $row['taxes1'];
         $supers[$curSuper]['taxes2'] += $row['taxes2'];
         $this->grandCostsTotal += $row['costs'];
         $this->grandSalesTotal += $row['sales'];
         $this->grandTax1Total += $row['taxes1'];
         $this->grandTax2Total += $row['taxes2'];
         // GROUP BY produces 1 row per dept. Values are sums.
         $supers[$curSuper]['depts'][] = array('name' => $row['dname'], 'qty' => $row['qty'], 'costs' => $row['costs'], 'sales' => $row['sales'], 'taxes1' => $row['taxes1'], 'taxes2' => $row['taxes2']);
     }
     $superCount = 1;
     foreach ($supers as $s) {
         if ($s['sales'] == 0 && !$this->show_zero) {
             $superCount++;
             continue;
         }
         $this->report_headers[] = array("{$s['name']}", 'Qty', 'Costs', '% Costs', 'DeptC%', 'Sales', '% Sales', 'DeptS %', 'Margin %', 'GST', 'HST');
         // add department records
         $superCostsSum = $s['costs'];
         $superSalesSum = $s['sales'];
         foreach ($s['depts'] as $d) {
             $record = array($d['name'], sprintf('%.2f', $d['qty']), sprintf('$%.2f', $d['costs']));
             $costPercent = 'n/a';
             if ($this->grandCostsTotal > 0) {
                 $costPercent = sprintf('%.2f%%', $d['costs'] / $this->grandCostsTotal * 100);
             }
             $record[] = $costPercent;
             $costPercent = 'n/a';
             if ($superCostsSum > 0) {
                 $costPercent = sprintf('%.2f%%', $d['costs'] / $superCostsSum * 100);
             }
             $record[] = $costPercent;
             $record[] = sprintf('$%.2f', $d['sales']);
             $salePercent = 'n/a';
             if ($this->grandSalesTotal > 0) {
                 $salePercent = sprintf('%.2f%%', $d['sales'] / $this->grandSalesTotal * 100);
             }
             $record[] = $salePercent;
             $salePercent = 'n/a';
             if ($superSalesSum > 0) {
                 $salePercent = sprintf('%.2f%%', $d['sales'] / $superSalesSum * 100);
             }
             $record[] = $salePercent;
             $margin = 'n/a';
             if ($d['sales'] > 0 && $d['costs'] > 0) {
                 $margin = sprintf('%.2f%%', 100 * ($d['sales'] - $d['costs']) / $d['sales']);
             }
             $record[] = $margin;
             $record[] = sprintf('%.2f', $d['taxes2']);
             $record[] = sprintf('%.2f', $d['taxes1']);
             $data[] = $record;
         }
         /* "super record" is a row of totals for the superdept,
          * instead of using calculate_footers().
          */
         $record = array($s['name'], sprintf('%.2f', $s['qty']), sprintf('$%s', number_format($s['costs'], 2)));
         $costPercent = 'n/a';
         if ($this->grandCostsTotal > 0) {
             $costPercent = sprintf('%.2f%%', $s['costs'] / $this->grandCostsTotal * 100);
         }
         $record[] = $costPercent;
         $record[] = '';
         $record[] = sprintf('$%s', number_format($s['sales'], 2));
         //sprintf('%.2f',$s['sales']);
         $salePercent = 'n/a';
         if ($this->grandSalesTotal > 0) {
             $salePercent = sprintf('%.2f%%', $s['sales'] / $this->grandSalesTotal * 100);
         }
         $record[] = $salePercent;
         $record[] = '';
         $margin = 'n/a';
         if ($s['sales'] > 0 && $s['costs'] > 0) {
             $margin = sprintf('%.2f%%', 100 * ($s['sales'] - $s['costs']) / $s['sales']);
         }
         $record[] = $margin;
         $record[] = sprintf('%.2f', $s['taxes2']);
         $record[] = sprintf('%.2f', $s['taxes1']);
         $record['meta'] = FannieReportPage::META_BOLD;
         $data[] = $record;
         // Rather than start a new report, insert a blank line between superdepts.
         $data[] = array('meta' => FannieReportPage::META_BLANK);
         if ($superCount < count($supers)) {
             $data[] = array('meta' => FannieReportPage::META_REPEAT_HEADERS);
         }
         $superCount++;
     }
     /** Discounts applied at the member type level.
      */
     $report = array();
     /* Headings
      */
     $this->report_headers[] = array('MEMBER TYPE', 'Qty', '', '', '', 'Amount', '', '', '', '', '');
     $data[] = array('meta' => FannieReportPage::META_REPEAT_HEADERS);
     // A row for each type of member.
     $dDiscountTotal = 0;
     $dQtyTotal = 0;
     $discQ = $dbc->prepare("\n                SELECT m.memDesc, \n                    SUM(t.total) AS Discount,\n                    count(*) AS ct\n                FROM {$dtrans} t\n                    INNER JOIN {$FANNIE_OP_DB}.memtype m ON t.memType = m.memtype\n                WHERE ({$datestamp} BETWEEN ? AND ?)\n                    AND t.upc = 'DISCOUNT'\n                    AND t.total <> 0\n                    AND t.trans_status not in ('D','X','Z')\n                    AND t.emp_no not in (9999){$shrinkageUsers}\n                    AND t.register_no != 99\n                    AND t.trans_subtype not in ('CP','IC')\n                GROUP BY m.memDesc\n                ORDER BY m.memDesc");
     $discR = $dbc->exec_statement($discQ, $costArgs);
     if ($discR === False) {
         $data[] = array("SQL exec on {$dtrans} failed");
     } else {
         $record = array('', '', '', '', '', '', '', '', '', '', '');
         while ($discW = $dbc->fetch_row($discR)) {
             $record[0] = $discW['memDesc'];
             $record[1] = $discW['ct'];
             $dQtyTotal += $discW['ct'];
             $record[5] = sprintf('$%.2f', 1 * $discW['Discount']);
             $dDiscountTotal += 1 * $discW['Discount'];
             $data[] = $record;
         }
         // Total Footer
         $record = array("DISCOUNTS", number_format($dQtyTotal, 0), '', '', '', number_format($dDiscountTotal, 2), '', '', '', '', '');
         $record['meta'] = FannieReportPage::META_BOLD;
         $data[] = $record;
         $data[] = array('meta' => FannieReportPage::META_BLANK);
     }
     // The discount total is negative.
     $this->grandSalesTotal += $dDiscountTotal;
     $this->summary_data[] = $report;
     // End of Discounts
     /** The summary of grand totals proportions for the whole store.
      */
     // Headings
     $record = array('', '', 'Costs', '', '', 'Sales', 'Profit', '', 'Margin %', isset($taxNames['2']) ? $taxNames['2'] : 'n/a', isset($taxNames['1']) ? $taxNames['1'] : 'n/a');
     $record['meta'] = FannieReportPage::META_BOLD;
     $data[] = $record;
     // Grand totals
     $record = array('WHOLE STORE', '', '$ ' . number_format($this->grandCostsTotal, 2), '', '', '$ ' . number_format($this->grandSalesTotal, 2), '$ ' . number_format($this->grandSalesTotal - $this->grandCostsTotal, 2), '');
     $margin = 'n/a';
     if ($this->grandSalesTotal > 0) {
         $margin = number_format(($this->grandSalesTotal - $this->grandCostsTotal) / $this->grandSalesTotal * 100, 2) . ' %';
     }
     $record[] = $margin;
     $record[] = '$ ' . number_format($this->grandTax2Total, 2);
     $record[] = '$ ' . number_format($this->grandTax1Total, 2);
     $record['meta'] = FannieReportPage::META_BOLD;
     $data[] = $record;
     $this->grandTTL = $grandTotal;
     return $data;
     // fetch_report_data()
 }