Beispiel #1
0
 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $date1 = $this->form->date1;
     $date2 = $this->form->date2;
     $deptStart = FormLib::get('deptStart');
     $deptEnd = FormLib::get('deptEnd');
     $deptMulti = FormLib::get('departments', array());
     $include_sales = FormLib::get('includeSales', 0);
     $buyer = FormLib::get('buyer', '');
     // args/parameters differ with super
     // vs regular department
     $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
     $where = ' 1=1 ';
     if ($buyer !== '') {
         if ($buyer == -2) {
             $where .= ' AND s.superID != 0 ';
         } elseif ($buyer != -1) {
             $where .= ' AND s.superID=? ';
             $args[] = $buyer;
         }
     }
     if ($buyer != -1) {
         list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
         $where .= $conditional;
     }
     $dlog = DTransactionsModel::selectDlog($date1, $date2);
     $query = "SELECT d.upc,\n                    p.brand,\n                    p.description,\n                    d.department,\n                    t.dept_name,\n                    SUM(total) AS total,\n                    SUM(d.cost) AS cost," . DTrans::sumQuantity('d') . " AS qty\n                  FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't');
     // join only needed with specific buyer
     if ($buyer !== '' && $buyer > -1) {
         $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
     } elseif ($buyer !== '' && $buyer == -2) {
         $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
     }
     $query .= "WHERE tdate BETWEEN ? AND ?\n            AND {$where}\n            AND d.cost <> 0 ";
     if ($include_sales != 1) {
         $query .= "AND d.discounttype=0 ";
     }
     $query .= "GROUP BY d.upc,p.description,d.department,t.dept_name\n            ORDER BY sum(total) DESC";
     $prep = $dbc->prepare_statement($query);
     $result = $dbc->exec_statement($query, $args);
     $data = array();
     $sum_total = 0.0;
     $sum_cost = 0.0;
     while ($row = $dbc->fetch_row($result)) {
         $margin = $row['total'] == 0 ? 0 : ($row['total'] - $row['cost']) / $row['total'] * 100;
         $record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['cost']), sprintf('%.2f', $row['total']), sprintf('%.2f', $margin), sprintf('%.2f', $row['qty'] == 0 ? 0 : ($row['total'] - $row['cost']) / $row['qty']));
         $sum_total += $row['total'];
         $sum_cost += $row['cost'];
         $data[] = $record;
     }
     // go through and add a contribution to margin value
     for ($i = 0; $i < count($data); $i++) {
         // (item_total - item_cost) / total sales
         $contrib = ($data[$i][5] - $data[$i][4]) / $sum_total * 100;
         $data[$i][] = sprintf('%.2f', $contrib);
     }
     return $data;
 }
 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $date1 = $this->form->date1;
     $date2 = $this->form->date2;
     $deptStart = FormLib::get('deptStart');
     $deptEnd = FormLib::get('deptEnd');
     $deptMulti = FormLib::get('departments', array());
     $buyer = FormLib::get('buyer', '');
     $dlog = DTransactionsModel::selectDlog($date1, $date2);
     $queryAll = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n            COUNT(DISTINCT trans_num) as trans_count\n            FROM {$dlog} AS d \n            WHERE tdate BETWEEN ? AND ?\n            GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)\n            ORDER BY YEAR(tdate), MONTH(tdate), DAY(tdate)";
     $argsAll = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
     $querySelected = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n            COUNT(DISTINCT trans_num) as trans_count\n            FROM {$dlog} AS d ";
     if ($buyer !== '' && $buyer > -1) {
         $querySelected .= " LEFT JOIN superdepts AS s ON d.department=s.dept_ID ";
     } elseif ($buyer !== '' && $buyer == -2) {
         $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
     }
     $querySelected .= " WHERE tdate BETWEEN ? AND ? ";
     $argsSel = $argsAll;
     if ($buyer !== '') {
         if ($buyer == -2) {
             $querySelected .= ' AND s.superID != 0 ';
         } elseif ($buyer != -1) {
             $querySelected .= ' AND s.superID=? ';
             $argsSel[] = $buyer;
         }
     }
     if ($buyer != -1) {
         list($conditional, $argsSel) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $argsSel);
         $querySelected .= $conditional;
     }
     $querySelected .= " GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)";
     $dataset = array();
     $prep = $dbc->prepare_statement($queryAll);
     $result = $dbc->exec_statement($prep, $argsAll);
     while ($row = $dbc->fetch_row($result)) {
         $datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']);
         $dataset[$datestr] = array('ttl' => $row['trans_count'], 'sub' => 0);
     }
     $prep = $dbc->prepare_statement($querySelected);
     $result = $dbc->exec_statement($prep, $argsSel);
     while ($row = $dbc->fetch_row($result)) {
         $datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']);
         if (isset($dataset[$datestr])) {
             $dataset[$datestr]['sub'] = $row['trans_count'];
         }
     }
     $data = array();
     foreach ($dataset as $date => $count) {
         $record = array($date, $count['sub'], $count['ttl']);
         $record[] = sprintf('%.2f%%', $count['sub'] / $count['ttl'] * 100);
         $data[] = $record;
     }
     return $data;
 }
Beispiel #3
0
 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $date1 = $this->form->date1;
     $date2 = $this->form->date2;
     $deptStart = FormLib::get('deptStart');
     $deptEnd = FormLib::get('deptEnd');
     $deptMulti = FormLib::get('departments', array());
     $buyer = FormLib::get('buyer', '');
     // args/parameters differ with super
     // vs regular department
     $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
     $where = ' 1=1 ';
     if ($buyer !== '') {
         if ($buyer == -2) {
             $where .= ' AND s.superID != 0 ';
         } elseif ($buyer != -1) {
             $where .= ' AND s.superID=? ';
             $args[] = $buyer;
         }
     }
     if ($buyer != -1) {
         list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
         $where .= $conditional;
     }
     $dlog = DTransactionsModel::selectDlog($date1, $date2);
     $query = "\n            SELECT d.upc,\n                p.brand,\n                p.description," . DTrans::sumQuantity('d') . " AS qty,\n                CASE WHEN memDiscount <> 0 AND memType <> 0 THEN unitPrice - memDiscount ELSE unitPrice END as price,\n                d.department, \n                t.dept_name, \n                SUM(total) AS total\n            FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't');
     // join only needed with specific buyer
     if ($buyer !== '' && $buyer > -1) {
         $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
     } elseif ($buyer !== '' && $buyer == -2) {
         $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
     }
     $query .= "\n            WHERE tdate BETWEEN ? AND ?\n                AND {$where}\n            GROUP BY d.upc,p.description,price,d.department,t.dept_name\n            ORDER BY d.upc";
     $prep = $dbc->prepare_statement($query);
     $result = $dbc->exec_statement($query, $args);
     $data = array();
     while ($row = $dbc->fetch_row($result)) {
         $record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['price']), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
         $data[] = $record;
     }
     // bold items that sold at multiple prices
     for ($i = 0; $i < count($data); $i++) {
         if (!isset($data[$i + 1])) {
             continue;
         }
         if ($data[$i][0] == $data[$i + 1][0]) {
             $data[$i]['meta'] = FannieReportPage::META_BOLD;
             $data[$i + 1]['meta'] = FannieReportPage::META_BOLD;
         }
     }
     return $data;
 }
Beispiel #4
0
 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $date1 = $this->form->date1;
     $date2 = $this->form->date2;
     $deptStart = FormLib::get('deptStart');
     $deptEnd = FormLib::get('deptEnd');
     $deptMulti = FormLib::get('departments', array());
     $buyer = FormLib::get('buyer', '');
     // args/parameters differ with super
     // vs regular department
     $args = array();
     $where = ' 1=1 ';
     if ($buyer !== '') {
         if ($buyer == -2) {
             $where .= ' AND s.superID != 0 ';
         } elseif ($buyer != -1) {
             $where .= ' AND s.superID=? ';
             $args[] = $buyer;
         }
     }
     if ($buyer != -1) {
         list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 'p');
         $where .= $conditional;
     }
     $args[] = $date1 . ' 00:00:00';
     $args[] = $date2 . ' 23:59:59';
     $query = "SELECT MIN(CASE WHEN a.modified IS NULL THEN p.modified ELSE a.modified END) AS entryDate, \n            a.upc, p.description, p.department, d.dept_name\n            FROM products AS p INNER JOIN prodUpdate AS a ON a.upc=p.upc\n            LEFT JOIN departments AS d ON d.dept_no=p.department ";
     // join only needed with specific buyer
     if ($buyer !== '' && $buyer > -1) {
         $query .= 'LEFT JOIN superdepts AS s ON p.department=s.dept_ID ';
     } elseif ($buyer !== '' && $buyer == -2) {
         $query .= 'LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID ';
     }
     $query .= "WHERE {$where}\n            GROUP BY p.upc,p.description,p.department, d.dept_name\n            HAVING entryDate BETWEEN ? AND ?\n            ORDER BY entryDate";
     $prep = $dbc->prepare_statement($query);
     $result = $dbc->exec_statement($query, $args);
     $data = array();
     while ($row = $dbc->fetch_row($result)) {
         $record = array($row['entryDate'], $row['upc'], $row['description'], $row['department'], $row['dept_name']);
         $data[] = $record;
     }
     return $data;
 }
Beispiel #5
0
 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $date1 = $this->form->date1;
     $date2 = $this->form->date2;
     $deptStart = FormLib::get('deptStart');
     $deptEnd = FormLib::get('deptEnd');
     $deptMulti = FormLib::get('departments', array());
     $buyer = FormLib::get('buyer', '');
     // args/parameters differ with super
     // vs regular department
     $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
     $where = ' 1=1 ';
     if ($buyer !== '') {
         if ($buyer > -1) {
             $where .= ' AND s.superID=? ';
             $args[] = $buyer;
         } elseif ($buyer == -2) {
             $where .= ' AND s.superID <> 0 ';
         }
     }
     if ($buyer != -1) {
         list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
         $where .= $conditional;
     }
     $tempTables = array('connection' => $dbc, 'clauses' => array(array('sql' => 'trans_type IN (?, ?)', 'params' => array('I', 'D'))));
     $dlog = DTransactionsModel::selectDlog($date1, $date2, $tempTables);
     $query = "SELECT year(tdate),month(tdate),day(tdate),\n          SUM(CASE WHEN trans_type='D' THEN total ELSE 0 END) as total,\n          SUM(CASE WHEN trans_type='D' THEN abs(quantity) ELSE 0 END) as qty,\n          SUM(CASE WHEN trans_type='D' THEN 1.0 ELSE 0.0 END) /\n          SUM(CASE WHEN trans_type IN ('I','D') THEN 1.0 ELSE 0.0 END) as percentage\n          FROM {$dlog} as d ";
     // join only needed with specific buyer
     if ($buyer !== '' && $buyer > -1) {
         $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
     } elseif ($buyer == -2) {
         $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
     }
     $query .= "WHERE trans_type IN ('I','D')\n            AND tdate BETWEEN ? AND ?\n            AND {$where}\n            GROUP BY year(tdate),month(tdate),day(tdate)\n            ORDER BY year(tdate),month(tdate),day(tdate)";
     $prep = $dbc->prepare_statement($query);
     $result = $dbc->exec_statement($query, $args);
     $data = array();
     while ($row = $dbc->fetch_row($result)) {
         $record = array(sprintf('%d/%d/%d', $row[1], $row[2], $row[0]), sprintf('%.2f', $row['total']), sprintf('%.2f', $row['qty']), sprintf('%.2f%%', $row['percentage'] * 100));
         $data[] = $record;
     }
     return $data;
 }
Beispiel #6
0
 function fetch_report_data()
 {
     global $FANNIE_OP_DB, $FANNIE_ARCHIVE_DB;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $date1 = $this->form->date1;
     $date2 = $this->form->date2;
     $deptStart = FormLib::get_form_value('deptStart', 0);
     $deptEnd = FormLib::get_form_value('deptEnd', 0);
     $deptMulti = FormLib::get('departments', array());
     $tempName = "TempNoMove";
     $dlog = DTransactionsModel::selectDlog($date1, $date2);
     $tempQ = $dbc->prepare_statement("CREATE TABLE {$tempName} (upc varchar(13))");
     $dbc->exec_statement($tempQ);
     $insQ = $dbc->prepare("\n            INSERT INTO {$tempName}\n            SELECT d.upc FROM {$dlog} AS d\n            WHERE \n                d.tdate BETWEEN ? AND ?\n                AND d.trans_type='I'\n            GROUP BY d.upc");
     $dbc->exec_statement($insQ, array($date1 . ' 00:00:00', $date2 . ' 23:59:59'));
     $where = ' 1=1 ';
     $buyer = FormLib::get('super');
     $args = array();
     if ($buyer !== '') {
         if ($buyer == -2) {
             $where .= ' AND s.superID != 0 ';
         } elseif ($buyer != -1) {
             $where .= ' AND s.superID=? ';
             $args[] = $buyer;
         }
     }
     if ($buyer != -1) {
         list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 'p');
         $where .= $conditional;
     }
     $query = "\n            SELECT p.upc,\n                p.brand,\n                p.description,\n                d.dept_no,\n                d.dept_name \n            FROM products AS p \n                LEFT JOIN departments AS d ON p.department=d.dept_no ";
     if ($buyer !== '' && $buyer > -1) {
         $query .= 'LEFT JOIN superdepts AS s ON p.department=s.dept_ID ';
     } elseif ($buyer !== '' && $buyer == -2) {
         $query .= 'LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID ';
     }
     $query .= " WHERE p.upc NOT IN (\n                SELECT upc FROM {$tempName}\n                )\n                AND {$where}\n                AND p.inUse=1\n            ORDER BY p.upc";
     $prep = $dbc->prepare($query);
     $result = $dbc->exec_statement($prep, $args);
     /**
       Simple report
     
       Issue a query, build array of results
     */
     $ret = array();
     while ($row = $dbc->fetch_array($result)) {
         $record = array();
         $record[] = $row[0];
         $record[] = $row[1];
         $record[] = $row[2];
         $record[] = $row[3];
         $record[] = $row[4];
         if ($this->report_format == 'html') {
             $record[] = sprintf('<a href="" id="del%s"
                     onclick="backgroundDeactivate(\'%s\');return false;">
                     Deactivate this item</a>', $row[0], $row[0]);
         } else {
             $record[] = '';
         }
         if ($this->report_format == 'html') {
             $record[] = sprintf('<a href="" id="del%s"
                     onclick="backgroundDelete(\'%s\',\'%s\');return false;">
                     Delete this item</a>', $row[0], $row[0], $row[1]);
         } else {
             $record[] = '';
         }
         $ret[] = $record;
     }
     $drop = $dbc->prepare_statement("DROP TABLE {$tempName}");
     $dbc->exec_statement($drop);
     return $ret;
 }
Beispiel #7
0
 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $date1 = $this->form->date1;
     $date2 = $this->form->date2;
     $deptStart = FormLib::get('deptStart', 1);
     $deptEnd = FormLib::get('deptEnd', 1);
     $deptMulti = FormLib::get('departments', array());
     $buyer = FormLib::get('buyer', '');
     $dlog = DTransactionsModel::selectDlog($date1, $date2);
     /**
       Report Query notes:
       * Combining vendorName and prodExtra.distributor is a nod to
         legacy data. Eventually data should be fully normalized on
         products.default_vendor_id
       * Excluding prodExtra.distributor empty string combines those
         records with SQL NULL. Having two different "blank" rows
         is confusing for users.
       * Joins are only needed is a super department condition is 
         involved. WHERE clause changes similarly.
     */
     $query = '
         SELECT COALESCE(v.vendorName, x.distributor) AS vendor,
             ' . DTrans::sumQuantity('t') . ' AS qty,
             SUM(t.total) AS ttl
         FROM ' . $dlog . ' AS t
             ' . DTrans::joinProducts('t', 'p', 'LEFT') . '
             LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID
             LEFT JOIN prodExtra AS x ON p.upc=x.upc 
             ';
     if ($buyer !== '' && $buyer > -1) {
         $query .= ' LEFT JOIN superdepts AS s ON t.department=s.dept_ID ';
     } elseif ($buyer !== '' && $buyer == -2) {
         $query .= ' LEFT JOIN MasterSuperDepts AS s ON t.department=s.dept_ID ';
     }
     $query .= '
         WHERE t.tdate BETWEEN ? AND ?
             AND t.trans_type IN (\'I\',\'D\') ';
     $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
     if ($buyer !== '') {
         if ($buyer == -2) {
             $query .= ' AND s.superID != 0 ';
         } elseif ($buyer != -1) {
             $query .= ' AND s.superID=? ';
             $args[] = $buyer;
         }
     }
     if ($buyer != -1) {
         list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 't');
         $query .= $conditional;
     }
     $query .= '
         GROUP BY COALESCE(v.vendorName, x.distributor)
         ORDER BY SUM(total) DESC';
     $prep = $dbc->prepare($query);
     $result = $dbc->execute($prep, $args);
     $data = array();
     $total_sales = 0.0;
     while ($w = $dbc->fetch_row($result)) {
         $data[] = array($w['vendor'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['ttl']), 0.0);
         $total_sales += $w['ttl'];
     }
     for ($i = 0; $i < count($data); $i++) {
         $data[$i][3] = sprintf('%.2f%%', $data[$i][2] / $total_sales * 100);
         $data[$i][2] = '$' . $data[$i][2];
     }
     return $data;
 }
Beispiel #8
0
 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $date1 = $this->form->date1;
     $date2 = $this->form->date2;
     $deptStart = FormLib::get('deptStart');
     $deptEnd = FormLib::get('deptEnd');
     $deptMulti = FormLib::get('departments', array());
     $weekday = FormLib::get('weekday', 0);
     $buyer = FormLib::get('buyer', '');
     // args/parameters differ with super
     // vs regular department
     $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
     $where = ' 1=1 ';
     if ($buyer !== '') {
         if ($buyer == -2) {
             $where .= ' AND s.superID != 0 ';
         } elseif ($buyer != -1) {
             $where .= ' AND s.superID=? ';
             $args[] = $buyer;
         }
     }
     if ($buyer != -1) {
         list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
         $where .= $conditional;
     }
     $date_selector = 'year(tdate), month(tdate), day(tdate)';
     $day_names = array();
     if ($weekday == 1) {
         $date_selector = $dbc->dayofweek('tdate');
         $timestamp = strtotime('next Sunday');
         for ($i = 1; $i <= 7; $i++) {
             $day_names[$i] = strftime('%a', $timestamp);
             $timestamp = strtotime('+1 day', $timestamp);
         }
     }
     $hour = $dbc->hour('tdate');
     $dlog = DTransactionsModel::selectDlog($date1, $date2);
     $query = "SELECT {$date_selector}, {$hour} as hour, \n                    sum(d.total) AS ttl, avg(d.total) as avg\n                  FROM {$dlog} AS d ";
     // join only needed with specific buyer
     // or all retail
     if ($buyer !== '' && $buyer > -1) {
         $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
     } elseif ($buyer !== '' && $buyer == -2) {
         $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
     }
     $query .= "WHERE d.trans_type IN ('I','D')\n                    AND d.tdate BETWEEN ? AND ?\n                    AND {$where} ";
     if ($this->config->get('COOP_ID') == 'WFC_Duluth') {
         $query .= ' AND d.department NOT IN (993, 998, 703) ';
     }
     $query .= " GROUP BY {$date_selector}, {$hour}\n                   ORDER BY {$date_selector}, {$hour}";
     $prep = $dbc->prepare_statement($query);
     $result = $dbc->exec_statement($query, $args);
     $dataset = array();
     $minhour = 24;
     $maxhour = 0;
     while ($row = $dbc->fetch_row($result)) {
         $hour = (int) $row['hour'];
         $date = '';
         if ($weekday == 1) {
             $date = $day_names[$row[0]];
         } else {
             $date = sprintf('%d/%d/%d', $row[1], $row[2], $row[0]);
         }
         if (!isset($dataset[$date])) {
             $dataset[$date] = array();
         }
         $dataset[$date][$hour] = $row['ttl'];
         if ($hour < $minhour) {
             $minhour = $hour;
         }
         if ($hour > $maxhour) {
             $maxhour = $hour;
         }
     }
     /**
       # of columns is dynamic depending on the
       date range selected
     */
     $this->report_headers = array('Day');
     foreach ($dataset as $day => $info) {
         $this->report_headers[] = $day;
     }
     $this->report_headers[] = 'Total';
     $data = array();
     /**
       # of rows is dynamic depending when
       the store was open
     */
     for ($i = $minhour; $i <= $maxhour; $i++) {
         $record = array();
         $sum = 0;
         if ($i < 12) {
             $record[] = str_pad($i, 2, '0', STR_PAD_LEFT) . ':00 AM';
         } else {
             if ($i == 12) {
                 $record[] = $i . ':00 PM';
             } else {
                 $record[] = str_pad($i - 12, 2, '0', STR_PAD_LEFT) . ':00 PM';
             }
         }
         // each day's sales for the given hour
         foreach ($dataset as $day => $info) {
             $sales = isset($info[$i]) ? $info[$i] : 0;
             $record[] = sprintf('%.2f', $sales);
             $sum += $sales;
         }
         $record[] = sprintf('%.2f', $sum);
         $data[] = $record;
     }
     return $data;
 }