From 92101d528a49ac3dda0a0f06960313b9a2794fd6 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Thu, 19 Mar 2015 19:05:45 +0100 Subject: [PATCH] Additional fixes for journal handling. Added reg_type in trans_tax_details. --- gl/includes/db/gl_db_trans.inc | 69 +++++++++++++------------ gl/inquiry/journal_inquiry.php | 13 ++++- includes/ui/items_cart.inc | 5 +- purchasing/includes/db/invoice_db.inc | 2 +- reporting/rep709.php | 8 +-- sales/includes/db/sales_credit_db.inc | 2 +- sales/includes/db/sales_delivery_db.inc | 2 +- sales/includes/db/sales_invoice_db.inc | 4 +- sql/alter2.4.sql | 10 ++++ sql/en_US-demo.sql | 23 +++++---- sql/en_US-new.sql | 1 + 11 files changed, 81 insertions(+), 58 deletions(-) diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 18c6ba66..d5adab4f 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -407,7 +407,7 @@ function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, } } add_trans_tax_details($trans_type, $trans_no, $tax['id'], $tax['rate'], $included, - $amount, $net_amount, $ex_rate, $date, $memo); + $amount, $net_amount, $ex_rate, $date, $memo, null); } @@ -417,17 +417,21 @@ function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, // actual tax type rate. // function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included, - $amount, $net_amount, $ex_rate, $tran_date, $memo) + $amount, $net_amount, $ex_rate, $tran_date, $memo, $reg_type=null) { + // guess tax register if not set + if (!isset($reg_type)) + $reg_type = in_array($trans_type, array(ST_SUPPINVOICE, ST_SUPPCREDIT)) ? TR_OUTPUT + : in_array($trans_type, array(ST_SALESINVOICE, ST_CUSTCREDIT)) ? TR_INPUT : null; $sql = "INSERT INTO ".TB_PREF."trans_tax_details (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate, - included_in_price, net_amount, amount, memo) + included_in_price, net_amount, amount, memo, reg_type) VALUES (".db_escape($trans_type)."," . db_escape($trans_no).",'" .date2sql($tran_date)."',".db_escape($tax_id)."," .db_escape($rate).",".db_escape($ex_rate).",".($included ? 1:0)."," .db_escape($net_amount)."," - .db_escape($amount).",".db_escape($memo).")"; + .db_escape($amount).",".db_escape($memo).",".db_escape($reg_type, true).")"; db_query($sql, "Cannot save trans tax details"); @@ -471,47 +475,46 @@ function clear_trans_tax_details($type, $type_no) db_query($sql, "The transaction tax details could not be cleared"); } -function get_tax_summary($from, $to) +function get_tax_summary($from, $to, $also_zero_purchases=false) { $fromdate = date2sql($from); $todate = date2sql($to); $sql = "SELECT - SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE - ." || trans_type=".ST_JOURNAL.",-1,1)* - IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE - ." || (trans_type=".ST_JOURNAL ." AND amount<0)" - ." || trans_type=".ST_CUSTCREDIT.", net_amount*ex_rate,0)) net_output, - - SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE - ." || trans_type=".ST_JOURNAL.",-1,1)* - IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE - ." || (trans_type=".ST_JOURNAL ." AND amount<0)" - ." || trans_type=".ST_CUSTCREDIT.", amount*ex_rate,0)) payable, - - SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE." || trans_type=".ST_JOURNAL.",-1,1)* - IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE - ." || (trans_type=".ST_JOURNAL ." AND amount<0)" - ." || trans_type=".ST_CUSTCREDIT.", 0, net_amount*ex_rate)) net_input, - - SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE." || trans_type=".ST_JOURNAL.",-1,1)* - IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE - ." || (trans_type=".ST_JOURNAL ." AND amount<0)" - ." || trans_type=".ST_CUSTCREDIT.", 0, amount*ex_rate)) collectible, + SUM(IF(trans_type=".ST_SUPPCREDIT.",-1,1)*IF(taxrec.amount, taxrec.effective_amount/taxrec.amount, 1)* + IF((reg_type=".TR_OUTPUT.")" + ." || ((trans_type IN(".ST_SUPPINVOICE.",".ST_SUPPCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) + ), net_amount*ex_rate,0) + ) net_output, + + SUM(IF(trans_type=".ST_SUPPCREDIT.",-1,1)* + IF((reg_type=".TR_OUTPUT.")" + ." || ((trans_type IN(".ST_SUPPINVOICE.",".ST_SUPPCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) + ), amount*ex_rate,0)) payable, + + SUM(IF(trans_type IN(".ST_SUPPCREDIT."),-1,1)* + IF(reg_type=".TR_INPUT + . ($also_zero_purchases ? '': " AND tax_type_id AND taxrec.rate") + .", net_amount*ex_rate, 0)) net_input, + + SUM(IF(trans_type IN(".ST_SUPPCREDIT."),-1,1)* + IF(reg_type=".TR_INPUT + . ($also_zero_purchases ? '': " AND tax_type_id AND taxrec.rate ") + .", amount*ex_rate, 0)) collectible, taxrec.rate, ttype.id, ttype.name - FROM ".TB_PREF."tax_types ttype, - ".TB_PREF."trans_tax_details taxrec - WHERE taxrec.tax_type_id=ttype.id - AND taxrec.trans_type != ".ST_CUSTDELIVERY." + FROM ".TB_PREF."trans_tax_details taxrec LEFT JOIN ".TB_PREF."tax_types ttype ON taxrec.tax_type_id=ttype.id + LEFT JOIN ".TB_PREF."tax_groups tgroup ON taxrec.tax_group_id=tgroup.id + WHERE taxrec.trans_type IN (".implode(',', + array(ST_SALESINVOICE, ST_CUSTCREDIT, ST_SUPPINVOICE, ST_SUPPCREDIT, ST_JOURNAL)).") AND taxrec.tran_date >= '$fromdate' AND taxrec.tran_date <= '$todate' GROUP BY ttype.id"; -//display_error($sql); + + // display_error($sql); return db_query($sql,"Cannot retrieve tax summary"); } - //-------------------------------------------------------------------------------------------------- function exists_gl_trans($type, $trans_id) @@ -563,7 +566,7 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al gl.tran_date, gl.type as trans_type, gl.type_no as trans_no, - IFNULL(max(supp.supp_name), max(cust.name)) as name, + IFNULL(MAX(supp.supp_name), MAX(cust.name)) as name, IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference, refs.reference," .($dimension ? " -SUM(IF(dim.dimension in(gl.dimension_id,gl.dimension2_id), gl.amount, 0)) as amount,":" SUM(IF(gl.amount>0, gl.amount,0)) as amount,") diff --git a/gl/inquiry/journal_inquiry.php b/gl/inquiry/journal_inquiry.php index ad77e8bf..999ae232 100644 --- a/gl/inquiry/journal_inquiry.php +++ b/gl/inquiry/journal_inquiry.php @@ -92,7 +92,12 @@ function edit_link($row) if ($myrow['alloc'] != 0 || get_voided_entry(ST_SALESINVOICE, $row["trans_no"]) !== false) $ok = false; } - return $ok ? trans_editor_link($row["trans_type"], $row["trans_type"]) : ''; + return $ok ? trans_editor_link( $row["trans_type"], $row["trans_no"]) : ''; +} + +function invoice_supp_reference($row) +{ + return $row['supp_reference']; } $sql = get_sql_for_journal_inquiry(get_post('filterType', -1), get_post('FromDate'), @@ -104,6 +109,7 @@ $cols = array( _("Type") => array('fun'=>'systype_name'), _("Trans #") => array('fun'=>'view_link'), _("Counterparty") => array('ord' => ''), + _("Supplier's Reference") => 'skip', _("Reference"), _("Amount") => array('type'=>'amount'), _("Memo"), @@ -116,6 +122,11 @@ if (!check_value('AlsoClosed')) { $cols[_("#")] = 'skip'; } +if($_POST['filterType'] == ST_SUPPINVOICE) //add the payment column if shown supplier invoices only +{ + $cols[_("Supplier's Reference")] = array('fun'=>'invoice_supp_reference', 'align'=>'center'); +} + $table =& new_db_pager('journal_tbl', $sql, $cols); $table->width = "80%"; diff --git a/includes/ui/items_cart.inc b/includes/ui/items_cart.inc index e149865f..e2a2ca91 100644 --- a/includes/ui/items_cart.inc +++ b/includes/ui/items_cart.inc @@ -428,10 +428,7 @@ class items_cart add_trans_tax_details($this->trans_type, $this->order_id, $tax_id, $this->tax_info['rate'][$tax_id], 0, $tax_nominal, $net, $this->rate, $this->tran_date, - $this->source_ref, - $this->tax_info['tax_group'], - $this->tax_info['tax_date'], - $tax, $this->tax_info['tax_category'], 0, $reg); + $this->source_ref, $reg); } } } diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index b14c7199..1cfd4518 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -200,7 +200,7 @@ function add_supp_invoice(&$supp_trans) // do not receive as ref because we chan } add_trans_tax_details($trans_type, $invoice_id, $taxitem['tax_type_id'], $taxitem['rate'], $supp_trans->tax_included, $taxitem['Value'], - $taxitem['Net'], $ex_rate, $date_, $supp_trans->supp_reference); + $taxitem['Net'], $ex_rate, $date_, $supp_trans->supp_reference, TR_INPUT); if (isset($taxitem['purchasing_gl_code'])) { diff --git a/reporting/rep709.php b/reporting/rep709.php index b9cd1171..9ee5ef2c 100644 --- a/reporting/rep709.php +++ b/reporting/rep709.php @@ -53,7 +53,7 @@ function getTaxTransactions($from, $to) LEFT JOIN ".TB_PREF."debtors_master as debt ON dtrans.debtor_no=debt.debtor_no LEFT JOIN ".TB_PREF."cust_branch as branch ON dtrans.branch_code=branch.branch_code WHERE (taxrec.amount <> 0 OR taxrec.net_amount <> 0) - AND taxrec.trans_type <> ".ST_CUSTDELIVERY." + AND !ISNULL(taxrec.reg_type) AND taxrec.tran_date >= '$fromdate' AND taxrec.tran_date <= '$todate' ORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate"; @@ -161,18 +161,18 @@ function print_tax_report() $rep->NewPage(); } } - if ($trans['trans_type']==ST_JOURNAL && $trans['amount']<0) { + if ($trans['trans_type']==ST_JOURNAL && $trans['reg_type']==TR_INPUT) { $taxes[$trans['tax_type_id']]['taxin'] += $trans['amount']; $taxes[$trans['tax_type_id']]['in'] += $trans['net_amount']; } - elseif ($trans['trans_type']==ST_JOURNAL && $trans['amount']>=0) { + elseif ($trans['trans_type']==ST_JOURNAL && $trans['reg_type']==TR_OUTPUT) { $taxes[$trans['tax_type_id']]['taxout'] += $trans['amount']; $taxes[$trans['tax_type_id']]['out'] += $trans['net_amount']; } elseif (in_array($trans['trans_type'], array(ST_BANKDEPOSIT,ST_SALESINVOICE,ST_CUSTCREDIT))) { $taxes[$trans['tax_type_id']]['taxout'] += $trans['amount']; $taxes[$trans['tax_type_id']]['out'] += $trans['net_amount']; - } else { + } elseif ($trans['reg_type'] !== NULL) { $taxes[$trans['tax_type_id']]['taxin'] += $trans['amount']; $taxes[$trans['tax_type_id']]['in'] += $trans['net_amount']; } diff --git a/sales/includes/db/sales_credit_db.inc b/sales/includes/db/sales_credit_db.inc index cdf86abf..9e8e9ae7 100644 --- a/sales/includes/db/sales_credit_db.inc +++ b/sales/includes/db/sales_credit_db.inc @@ -153,7 +153,7 @@ function write_credit_note(&$credit_note, $write_off_acc) add_trans_tax_details(ST_CUSTCREDIT, $credit_no, $taxitem['tax_type_id'], $taxitem['rate'], $credit_note->tax_included, $taxitem['Value'], $taxitem['Net'], $ex_rate, - $credit_note->document_date, $credit_note->reference); + $credit_note->document_date, $credit_note->reference, TR_OUTPUT); $total += add_gl_trans_customer(ST_CUSTCREDIT, $credit_no, $credit_date, $taxitem['sales_gl_code'], 0, 0, $taxitem['Value'], $credit_note->customer_id, diff --git a/sales/includes/db/sales_delivery_db.inc b/sales/includes/db/sales_delivery_db.inc index 290a3f2d..eec60c8d 100644 --- a/sales/includes/db/sales_delivery_db.inc +++ b/sales/includes/db/sales_delivery_db.inc @@ -157,7 +157,7 @@ function write_sales_delivery(&$delivery,$bo_policy) $ex_rate = get_exchange_rate_from_home_currency(get_customer_currency($delivery->customer_id), $delivery->document_date); add_trans_tax_details(ST_CUSTDELIVERY, $delivery_no, $taxitem['tax_type_id'], $taxitem['rate'], $delivery->tax_included, $taxitem['Value'], - $taxitem['Net'], $ex_rate, $delivery->document_date, $delivery->reference ); + $taxitem['Net'], $ex_rate, $delivery->document_date, $delivery->reference, null); } } diff --git a/sales/includes/db/sales_invoice_db.inc b/sales/includes/db/sales_invoice_db.inc index d2b97cf2..c944c6bf 100644 --- a/sales/includes/db/sales_invoice_db.inc +++ b/sales/includes/db/sales_invoice_db.inc @@ -169,8 +169,8 @@ function write_sales_invoice(&$invoice) if ($taxitem['Net'] != 0) { $ex_rate = get_exchange_rate_from_home_currency(get_customer_currency($invoice->customer_id), $date_); add_trans_tax_details(ST_SALESINVOICE, $invoice_no, $taxitem['tax_type_id'], - $taxitem['rate'], $invoice->tax_included, $taxitem['Value'], - $taxitem['Net'], $ex_rate, $date_, $invoice->reference, $date_, $prepaid_factor*$taxitem['Value']); + $taxitem['rate'], $invoice->tax_included, $prepaid_factor*$taxitem['Value'], + $taxitem['Net'], $ex_rate, $date_, $invoice->reference, TR_OUTPUT); if (isset($taxitem['sales_gl_code'])) $total += add_gl_trans_customer(ST_SALESINVOICE, $invoice_no, $date_, $taxitem['sales_gl_code'], 0, 0, (-$taxitem['Value'])*$prepaid_factor, $invoice->customer_id, diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index eedb0566..3ae9e0ec 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -200,3 +200,13 @@ ALTER TABLE `0_debtor_trans` ADD PRIMARY KEY (`type`,`trans_no`, `debtor_no`); ALTER TABLE `0_supp_trans` DROP PRIMARY KEY; ALTER TABLE `0_supp_trans` ADD PRIMARY KEY (`type`,`trans_no`, `supplier_id`); +ALTER TABLE `0_trans_tax_details` ADD COLUMN `reg_type` tinyint(1) DEFAULT NULL AFTER `memo`; + +UPDATE `0_trans_tax_details` reg + SET reg.reg_type=1 + WHERE reg.trans_type IN(20, 21); + +UPDATE `0_trans_tax_details` reg + SET reg.reg_type=0 + WHERE reg.trans_type IN(10, 11); + diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index f286b143..781d5037 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -2284,6 +2284,7 @@ CREATE TABLE IF NOT EXISTS `0_trans_tax_details` ( `net_amount` double NOT NULL default '0', `amount` double NOT NULL default '0', `memo` tinytext, + `reg_type` tinyint(1) default NULL, PRIMARY KEY (`id`), KEY `Type_and_Number` (`trans_type`,`trans_no`), KEY `tran_date` (`tran_date`) @@ -2293,17 +2294,17 @@ CREATE TABLE IF NOT EXISTS `0_trans_tax_details` ( -- Dumping data for table `0_trans_tax_details` -- -INSERT INTO `0_trans_tax_details` VALUES(1, 20, 7, '2014-06-21', 1, 5, 1, 0, 3300, 165, '5t'); -INSERT INTO `0_trans_tax_details` VALUES(2, 13, 3, '2014-06-21', 1, 5, 1, 0, 50, 2.5, 'auto'); -INSERT INTO `0_trans_tax_details` VALUES(3, 10, 17, '2014-06-21', 1, 5, 1, 0, 50, 2.5, '1'); -INSERT INTO `0_trans_tax_details` VALUES(4, 13, 4, '2014-06-21', 1, 5, 1.3932, 0, 35.89, 1.7945, 'auto'); -INSERT INTO `0_trans_tax_details` VALUES(5, 10, 18, '2014-06-21', 1, 5, 1.3932, 0, 35.89, 1.7945, '2'); -INSERT INTO `0_trans_tax_details` VALUES(6, 2, 5, '2014-06-21', 1, 5, 1, 0, 95.2, 4.76, ''); -INSERT INTO `0_trans_tax_details` VALUES(7, 1, 8, '2014-06-21', 1, 5, 1, 0, -47.6, -2.38, ''); -INSERT INTO `0_trans_tax_details` VALUES(8, 20, 8, '2014-06-21', 1, 5, 1, 0, -19, -0.95, 'cc'); -INSERT INTO `0_trans_tax_details` VALUES(9, 13, 5, '2014-06-21', 1, 5, 1, 1, 47.619047619048, 2.3809523809524, 'auto'); -INSERT INTO `0_trans_tax_details` VALUES(10, 10, 19, '2014-06-21', 1, 5, 1, 1, 47.619047619048, 2.3809523809524, '3'); -INSERT INTO `0_trans_tax_details` VALUES(11, 11, 3, '2014-06-21', 1, 5, 1.3932, 0, 35.89, 1.7945, '1'); +INSERT INTO `0_trans_tax_details` VALUES(1, 20, 7, '2014-06-21', 1, 5, 1, 0, 3300, 165, '5t', 1); +INSERT INTO `0_trans_tax_details` VALUES(2, 13, 3, '2014-06-21', 1, 5, 1, 0, 50, 2.5, 'auto', NULL); +INSERT INTO `0_trans_tax_details` VALUES(3, 10, 17, '2014-06-21', 1, 5, 1, 0, 50, 2.5, '1', 0); +INSERT INTO `0_trans_tax_details` VALUES(4, 13, 4, '2014-06-21', 1, 5, 1.3932, 0, 35.89, 1.7945, 'auto', NULL); +INSERT INTO `0_trans_tax_details` VALUES(5, 10, 18, '2014-06-21', 1, 5, 1.3932, 0, 35.89, 1.7945, '2', 0); +INSERT INTO `0_trans_tax_details` VALUES(6, 2, 5, '2014-06-21', 1, 5, 1, 0, 95.2, 4.76, '', NULL); +INSERT INTO `0_trans_tax_details` VALUES(7, 1, 8, '2014-06-21', 1, 5, 1, 0, -47.6, -2.38, '', NULL); +INSERT INTO `0_trans_tax_details` VALUES(8, 20, 8, '2014-06-21', 1, 5, 1, 0, -19, -0.95, 'cc', 1); +INSERT INTO `0_trans_tax_details` VALUES(9, 13, 5, '2014-06-21', 1, 5, 1, 1, 47.619047619048, 2.3809523809524, 'auto', NULL); +INSERT INTO `0_trans_tax_details` VALUES(10, 10, 19, '2014-06-21', 1, 5, 1, 1, 47.619047619048, 2.3809523809524, '3', 0); +INSERT INTO `0_trans_tax_details` VALUES(11, 11, 3, '2014-06-21', 1, 5, 1.3932, 0, 35.89, 1.7945, '1', 0); -- -------------------------------------------------------- diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index b4191c00..69cb4c3d 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -2044,6 +2044,7 @@ CREATE TABLE IF NOT EXISTS `0_trans_tax_details` ( `net_amount` double NOT NULL default '0', `amount` double NOT NULL default '0', `memo` tinytext, + `reg_type` tinyint(1) default NULL, PRIMARY KEY (`id`), KEY `Type_and_Number` (`trans_type`,`trans_no`), KEY `tran_date` (`tran_date`) -- 2.30.2