function get_view() { global $FANNIE_OP_DB; $dbc = FannieDB::get($FANNIE_OP_DB); $model = new TaxRatesModel($dbc); $ret = '<div id="alert-area"></div>'; $ret .= '<form action="TaxRateEditor.php" method="post">'; $ret .= '<table class="table">'; $ret .= '<tr><th>Description</th><th>Rate</th><th>Account #</th><th>Delete</th></tr>'; $ret .= '<tr><td>NoTax</th><td>0.00</td><td colspan="2"> </td></tr>'; foreach ($model->find('id') as $tax) { $ret .= sprintf(' <tr> <td><input type="text" name="desc[]" value="%s" class="form-control" /></td> <td><input type="text" name="rate[]" value="%f" class="form-control" /></td> <td><input type="text" name="account[]" value="%s" class="form-control" /></td> <td><input type="checkbox" name="del[]" value="%d" /></td> </tr>', $tax->description(), $tax->rate(), $tax->salesCode(), $tax->id()); } $ret .= '<tr> <td><input type="text" name="desc[]" class="form-control" /></td> <td><input type="text" name="rate[]" class="form-control" /></td> <td><input type="text" name="account[]" class="form-control" /></td> <td>NEW</td></tr>'; $ret .= "</table>"; $ret .= '<p><button type="submit" value="1" name="sub" class="btn btn-default">Save Tax Rates</button></p>'; $ret .= '</form>'; return $ret; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->setDefaultDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $rate_models = new TaxRatesModel($dbc); $rates = array(); foreach ($rate_models->find() as $obj) { $rates[$obj->id()] = $obj; } $perRateSQL = ''; $collectedSQL = ''; foreach ($rates as $id => $obj) { $perRateSQL .= sprintf(' SUM(CASE WHEN discountable=0 AND tax=%d THEN total ELSE 0 END) AS noDisc%d, SUM(CASE WHEN discountable=1 AND tax=%d THEN total ELSE 0 END) AS yesDisc%d,', $id, $id, $id, $id); $collectedSQL .= sprintf(' SUM(CASE WHEN upc=\'TAXLINEITEM\' AND numflag=%d THEN regPrice ELSE 0 END) AS collected%d,', $id, $id); $this->report_headers[] = $obj->description() . ' Expected'; $this->report_headers[] = $obj->description() . ' Net Discount'; $this->report_headers[] = $obj->description() . ' Actual'; $this->report_headers[] = $obj->description() . ' Forgiven'; } $this->report_headers[] = 'Total Expected'; $this->report_headers[] = 'Total Net Discount'; $this->report_headers[] = 'Total Actual'; $this->report_headers[] = 'Total Forgiven'; $dtrans = DTransactionsModel::selectDtrans($date1, $date2); $query = ' SELECT YEAR(datetime) AS year, MONTH(datetime) AS month, DAY(datetime) AS day, register_no, emp_no, trans_no, ' . $perRateSQL . ' MAX(percentDiscount) AS pd, SUM(CASE WHEN trans_subtype IN (\'EF\',\'FS\') THEN -total ELSE 0 END) AS fsTender, ' . $collectedSQL . ' SUM(CASE WHEN upc=\'TAX\' THEN total ELSE 0 END) AS totalTax FROM ' . $dtrans . ' AS d LEFT JOIN taxrates AS t ON d.tax=t.id WHERE datetime BETWEEN ? AND ? AND trans_status NOT IN (\'X\',\'Z\') AND emp_no <> 9999 AND register_no <> 99 GROUP BY YEAR(datetime), MONTH(datetime), DAY(datetime), register_no, emp_no, trans_no HAVING SUM(CASE WHEN d.tax > 0 THEN 1 ELSE 0 END) <> 0 AND SUM(CASE WHEN trans_subtype IN (\'EF\',\'FS\') THEN -total ELSE 0 END) <> 0'; $prep = $dbc->prepare($query); $res = $dbc->execute($prep, array($date1 . ' 00:00:00', $date2 . ' 23:59:59')); $data = array(); while ($w = $dbc->fetchRow($res)) { $record = array(date('Y-m-d', mktime(0, 0, 0, $w['month'], $w['day'], $w['year'])), $w['emp_no'] . '-' . $w['register_no'] . '-' . $w['trans_no'], sprintf('%d%%', $w['pd']), sprintf('%.2f', $w['fsTender'])); $all = new stdClass(); $all->total = 0; $all->net = 0; $all->actual = 0; $all->forgiven = 0; foreach ($rates as $id => $obj) { $total = sprintf('%.2f', ($w['noDisc' . $id] + $w['yesDisc' . $id]) * $obj->rate()); $net = sprintf('%.2f', ($w['noDisc' . $id] + (1 - $w['pd'] / 100.0) * $w['yesDisc' . $id]) * $obj->rate()); $actual = sprintf('%.2f', $w['collected' . $id]); $forgiven = $net - $actual; $record[] = $total; $record[] = $net; $record[] = $actual; $record[] = $forgiven; $all->total += $total; $all->net += $net; $all->actual += $actual; $all->forgiven += $forgiven; } $record[] = $all->total; $record[] = $all->net; $record[] = $all->actual; $record[] = $all->forgiven; $data[] = $record; } return $data; }