Beispiel #1
0
 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;
 }
Beispiel #2
0
 /**
   Create a tax rate, update it, delete it.
 */
 public function unitTest($phpunit)
 {
     $get = $this->get_view();
     $phpunit->assertNotEquals(0, strlen($get));
     $form = new \COREPOS\common\mvc\ValueContainer();
     $form->desc = array('test rate');
     $form->rate = array('0.05');
     $form->account = array('101');
     $this->setForm($form);
     $post = $this->post_handler();
     $phpunit->assertInternalType('bool', $post);
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $rate = new TaxRatesModel($dbc);
     $rate->id(1);
     $phpunit->assertEquals(true, $rate->load());
     $phpunit->assertEquals('test rate', $rate->description());
     $phpunit->assertEquals(0.05, $rate->rate());
     $phpunit->assertEquals('101', $rate->salesCode());
     $form->rate = array('0.15');
     $this->setForm($form);
     $post = $this->post_handler();
     $rate->reset();
     $rate->id(1);
     $phpunit->assertEquals(true, $rate->load());
     $phpunit->assertEquals(0.15, $rate->rate());
     $form->del = array(1);
     $this->setForm($form);
     $post = $this->post_handler();
     $rate->reset();
     $rate->id(1);
     $phpunit->assertEquals(false, $rate->load());
 }