From: Janusz Dobrowolski Date: Thu, 29 Jan 2009 08:37:22 +0000 (+0000) Subject: Changes related to rewrite and optimalzation of taz register. X-Git-Tag: v2.4.2~19^2~1577 X-Git-Url: https://delta.frontaccounting.com/gitweb/?p=fa-stable.git;a=commitdiff_plain;h=4e436722cb991b7273c08bdc1dc53b5390772972 Changes related to rewrite and optimalzation of taz register. --- diff --git a/gl/includes/db/gl_db_bank_trans.inc b/gl/includes/db/gl_db_bank_trans.inc index 6e440080..32e2d86e 100644 --- a/gl/includes/db/gl_db_bank_trans.inc +++ b/gl/includes/db/gl_db_bank_trans.inc @@ -105,14 +105,14 @@ function void_bank_trans($type, $type_no, $nested=false) // in case it's a customer trans - probably better to check first void_cust_allocations($type, $type_no); - void_customer_trans_tax_details($type, $type_no); void_customer_trans($type, $type_no); // in case it's a supplier trans - probably better to check first void_supp_allocations($type, $type_no); - void_supp_invoice_tax_items($type, $type_no); void_supp_trans($type, $type_no); + void_trans_tax_details($type, $type_no); + if (!$nested) commit_transaction(); } diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index b9d8734e..7e465091 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -147,7 +147,8 @@ function add_bank_transaction($trans_type, $from_account, $items, $date_, // store tax details if the gl account is a tax account $amount = $gl_item->amount; - add_gl_tax_details($gl_item->code_id, $trans_type, $trans_no, $amount); + add_gl_tax_details($gl_item->code_id, $trans_type, $trans_no, $amount, + $date_, $memo); } // do the source account postings diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 21fa114a..ae7ac397 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -209,23 +209,74 @@ function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, } //-------------------------------------------------------------------------------- -// Stores GL journal/bank transaction on tax account for tax report +// Stores journal/bank transaction tax details if applicable // -function add_gl_tax_details($gl_code, $trans_type, $trans_id, $amount) +function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $date, $memo) { $tax_type = is_tax_account($gl_code); if(!$tax_type) return; // $gl_code is not tax account - + $tax = get_tax_type($tax_type); - if ($gl_code == $tax['sales_gl_code']) - add_customer_trans_tax_detail_item($trans_type, $trans_id, - $tax['id'], $tax['rate'], 0, -$amount); - else - add_supp_invoice_tax_item($trans_type, $trans_id, - $tax['id'], $tax['rate'], 0, $amount); + if ($gl_code == $tax['sales_gl_code']) + $amount = -$amount; + // we have to restore net amount as we cannot know the base amount + if ($tax['rate'] == 0) { +// display_warning(_("You should not post gl transactions +// to tax account with zero tax rate.")); + $net_amount = 0; + } else { + // calculate net amount + $net_amount = $amount/$tax['rate']*100; + } + add_trans_tax_details($trans_type, $trans_no, $tax['id'], $tax['rate'], 0, + $amount, $net_amount, $date, $memo); } +//-------------------------------------------------------------------------------- +// +// Store transaction tax details for fiscal purposes with 'freezed' +// actual tax type rate. +// +function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included, + $amount, $net_amount, $tran_date, $memo) +{ + $sql = "INSERT INTO ".TB_PREF."trans_tax_details + (trans_type, trans_no, tran_date, tax_type_id, rate, + included_in_price, net_amount, amount, memo) + VALUES (".db_escape($trans_type)."," . db_escape($trans_no).",'" + .date2sql($tran_date)."',".db_escape($tax_id)."," + .$rate.",".($included ? 1:0).",".db_escape($net_amount)."," + .db_escape($amount).",".db_escape($memo).")"; + + db_query($sql, "Cannot save trans tax details"); + +} +//---------------------------------------------------------------------------------------- + +function get_trans_tax_details($trans_type, $trans_no) +{ + $sql = "SELECT ".TB_PREF."trans_tax_details.*, ".TB_PREF."tax_types.name AS tax_type_name + FROM ".TB_PREF."trans_tax_details,".TB_PREF."tax_types + WHERE trans_type = $trans_type + AND trans_no = $trans_no + AND amount != 0 + AND ".TB_PREF."tax_types.id = ".TB_PREF."trans_tax_details.tax_type_id"; + + return db_query($sql, "The transaction tax details could not be retrieved"); +} + +//---------------------------------------------------------------------------------------- + +function void_trans_tax_details($type, $type_no) +{ + $sql = "UPDATE ".TB_PREF."trans_tax_details SET amount=0, net_amount=0 + WHERE trans_no=$type_no + AND trans_type=$type"; + + db_query($sql, "The transaction tax details could not be voided"); +} + //-------------------------------------------------------------------------------- function add_journal_entries($items, $date_, $ref, $reverse, $memo_=null) { @@ -251,7 +302,7 @@ function add_journal_entries($items, $date_, $ref, $reverse, $memo_=null) } // store tax details if the gl account is a tax account add_gl_tax_details($journal_item->code_id, - $trans_type, $trans_id, $journal_item->amount); + $trans_type, $trans_id, -$journal_item->amount, $date_, $memo_); } add_comments($trans_type, $trans_id, $date_, $memo_); @@ -283,7 +334,7 @@ function add_journal_entries($items, $date_, $ref, $reverse, $memo_=null) } // store tax details if the gl account is a tax account add_gl_tax_details($journal_item->code_id, - $trans_type, $trans_id, -$journal_item->amount); + $trans_type, $trans_id, $journal_item->amount, $date, $memo_); } add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_); @@ -329,8 +380,7 @@ function void_journal_trans($type, $type_no) void_gl_trans($type, $type_no, true); void_bank_trans($type, $type_no,true); - void_customer_trans_tax_details($type, $type_no); - void_supp_invoice_tax_items($type, $type_no); + void_trans_tax_details($type, $type_no); commit_transaction(); } diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index 9c66d648..61f6a524 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -187,7 +187,8 @@ function add_supp_invoice($supp_trans, $invoice_no=0) // do not receive as ref b // store tax details if the gl account is a tax account add_gl_tax_details($entered_gl_code->gl_code, - $trans_type, $invoice_id, $entered_gl_code->amount); + $trans_type, $invoice_id, $entered_gl_code->amount, + $date_, $supp_trans->supp_reference); } foreach ($supp_trans->grn_items as $entered_grn) { @@ -267,8 +268,9 @@ function add_supp_invoice($supp_trans, $invoice_no=0) // do not receive as ref b if (!$supp_trans->is_invoice) $taxitem['Value'] = -$taxitem['Value']; // here we suppose that tax is never included in price (we are company customer). - add_supp_invoice_tax_item($trans_type, $invoice_id, $taxitem['tax_type_id'], - $taxitem['rate'], 0, $taxitem['Value']); + add_trans_tax_details($trans_type, $invoice_id, + $taxitem['tax_type_id'], $taxitem['rate'], 0, $taxitem['Value'], + $taxitem['Net'], $date_, $supp_trans->supp_reference); $total += add_gl_trans_supplier($trans_type, $invoice_id, $date_, $taxitem['purchasing_gl_code'], 0, 0, $taxitem['Value'], @@ -459,7 +461,7 @@ function void_supp_invoice($type, $type_no) } void_supp_invoice_items($type, $type_no); - void_supp_invoice_tax_items($type, $type_no); + void_trans_tax_details($type, $type_no); commit_transaction(); } diff --git a/purchasing/includes/db/invoice_items_db.inc b/purchasing/includes/db/invoice_items_db.inc index d6fb61cd..36957354 100644 --- a/purchasing/includes/db/invoice_items_db.inc +++ b/purchasing/includes/db/invoice_items_db.inc @@ -57,39 +57,4 @@ function void_supp_invoice_items($type, $type_no) db_query($sql, "could not void supptrans details"); } -//---------------------------------------------------------------------------------------- - -function add_supp_invoice_tax_item($supp_trans_type, $supp_trans_no, $tax_type_id, - $rate, $included_in_price, $amount) -{ - $sql = "INSERT INTO ".TB_PREF."supp_invoice_tax_items (supp_trans_type, supp_trans_no, tax_type_id, rate, included_in_price, amount) - VALUES ($supp_trans_type, $supp_trans_no, $tax_type_id, $rate, $included_in_price, $amount)"; - - db_query($sql, "The supplier transaction tax detail could not be added"); -} - -//---------------------------------------------------------------------------------------- - -function get_supp_invoice_tax_items($supp_trans_type, $supp_trans_no) -{ - $sql = "SELECT ".TB_PREF."supp_invoice_tax_items.*, ".TB_PREF."tax_types.name AS tax_type_name - FROM ".TB_PREF."supp_invoice_tax_items,".TB_PREF."tax_types - WHERE supp_trans_type = $supp_trans_type - AND supp_trans_no = $supp_trans_no - AND ".TB_PREF."tax_types.id = ".TB_PREF."supp_invoice_tax_items.tax_type_id"; - - return db_query($sql, "The supplier transaction tax details could not be queried"); -} - -//---------------------------------------------------------------------------------------- - -function void_supp_invoice_tax_items($type, $type_no) -{ - $sql = "UPDATE ".TB_PREF."supp_invoice_tax_items SET amount=0 - WHERE supp_trans_type = $type - AND supp_trans_no=$type_no"; - - db_query($sql, "The supplier transaction tax details could not be voided"); -} - ?> \ No newline at end of file diff --git a/purchasing/view/view_supp_credit.php b/purchasing/view/view_supp_credit.php index 22f7de2e..c5cb4a76 100644 --- a/purchasing/view/view_supp_credit.php +++ b/purchasing/view/view_supp_credit.php @@ -60,7 +60,7 @@ $display_sub_tot = number_format2($total_gl+$total_grn,user_price_dec()); start_table("$table_style width=95%"); label_row(_("Sub Total"), $display_sub_tot, "align=right", "nowrap align=right width=17%"); -$tax_items = get_supp_invoice_tax_items(21, $trans_no); +$tax_items = get_trans_tax_details(21, $trans_no); display_supp_trans_tax_details($tax_items, 1); $display_total = number_format2(-($supp_trans->ov_amount + $supp_trans->ov_gst),user_price_dec()); diff --git a/purchasing/view/view_supp_invoice.php b/purchasing/view/view_supp_invoice.php index c0c89e13..ac8432dc 100644 --- a/purchasing/view/view_supp_invoice.php +++ b/purchasing/view/view_supp_invoice.php @@ -65,7 +65,7 @@ $display_sub_tot = number_format2($total_gl+$total_grn,user_price_dec()); start_table("width=95% $table_style"); label_row(_("Sub Total"), $display_sub_tot, "align=right", "nowrap align=right width=15%"); -$tax_items = get_supp_invoice_tax_items(20, $trans_no); +$tax_items = get_trans_tax_details(20, $trans_no); display_supp_trans_tax_details($tax_items, 1); $display_total = number_format2($supp_trans->ov_amount + $supp_trans->ov_gst,user_price_dec()); diff --git a/reporting/rep107.php b/reporting/rep107.php index 98f419f0..279a3316 100644 --- a/reporting/rep107.php +++ b/reporting/rep107.php @@ -168,7 +168,7 @@ function print_invoices() $rep->TextCol(3, 6, $doc_Shipping, -2); $rep->TextCol(6, 7, $DisplayFreight, -2); $rep->NewLine(); - $tax_items = get_customer_trans_tax_details($j, $i); + $tax_items = get_trans_tax_details($j, $i); while ($tax_item = db_fetch($tax_items)) { $DisplayTax = number_format2($sign*$tax_item['amount'], $dec); diff --git a/reporting/rep110.php b/reporting/rep110.php index 1add3c45..49abe399 100644 --- a/reporting/rep110.php +++ b/reporting/rep110.php @@ -147,7 +147,7 @@ function print_deliveries() $rep->TextCol(3, 6, $doc_Shipping, -2); $rep->TextCol(6, 7, $DisplayFreight, -2); $rep->NewLine(); - $tax_items = get_customer_trans_tax_details(13, $i); + $tax_items = get_trans_tax_details(13, $i); while ($tax_item = db_fetch($tax_items)) { $DisplayTax = number_format2($tax_item['amount'], $dec); diff --git a/reporting/rep709.php b/reporting/rep709.php index 4ea86e29..238bd309 100644 --- a/reporting/rep709.php +++ b/reporting/rep709.php @@ -23,76 +23,35 @@ include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/includes/data_checks.inc"); include_once($path_to_root . "/gl/includes/gl_db.inc"); -//---------------------------------------------------------------------------------------------------- - -print_tax_report(); - -function getTax($tno, $tpe) -{ - // GROUP BY debtor_trans_type, debtor_trans_no"; - $sql = "SELECT SUM(amount) AS Amount, MAX(included_in_price) AS Included FROM ".TB_PREF."debtor_trans_tax_details - WHERE debtor_trans_no=$tno - AND debtor_trans_type=$tpe - AND amount <> 0"; +//------------------------------------------------------------------ - $result = db_query($sql,"No transactions were returned"); - return db_fetch($result); -} -function getCustTransactions($from, $to) -{ - $fromdate = date2sql($from); - $todate = date2sql($to); - - $netamount = "IF(".TB_PREF."debtor_trans.type=11,-(ov_amount+ov_freight+ov_discount),ov_amount+ov_freight+ov_discount)*".TB_PREF."debtor_trans.rate"; - - $sql = "SELECT ".TB_PREF."debtor_trans.reference, - ".TB_PREF."debtor_trans.trans_no, - ".TB_PREF."debtor_trans.type, - ".TB_PREF."debtor_trans.rate, - ".TB_PREF."sys_types.type_name, - ".TB_PREF."debtor_trans.tran_date, - ".TB_PREF."debtor_trans.debtor_no, - ".TB_PREF."debtors_master.name, - ".TB_PREF."debtors_master.curr_code, - ".TB_PREF."debtor_trans.branch_code, - ".TB_PREF."debtor_trans.order_, - $netamount AS NetAmount - FROM ".TB_PREF."debtor_trans - INNER JOIN ".TB_PREF."debtors_master ON ".TB_PREF."debtor_trans.debtor_no=".TB_PREF."debtors_master.debtor_no - INNER JOIN ".TB_PREF."sys_types ON ".TB_PREF."debtor_trans.type=".TB_PREF."sys_types.type_id - WHERE ".TB_PREF."debtor_trans.tran_date >= '$fromdate' - AND ".TB_PREF."debtor_trans.tran_date <= '$todate' - AND (".TB_PREF."debtor_trans.type=10 OR ".TB_PREF."debtor_trans.type=11) - ORDER BY ".TB_PREF."debtor_trans.tran_date"; - - return db_query($sql,"No transactions were returned"); -} +print_tax_report(); -function getSuppTransactions($from, $to) +function getTaxTransactions($from, $to) { $fromdate = date2sql($from); $todate = date2sql($to); - $sql = "SELECT ".TB_PREF."supp_trans.supp_reference, - ".TB_PREF."supp_trans.type, - ".TB_PREF."sys_types.type_name, - ".TB_PREF."supp_trans.tran_date, - ".TB_PREF."supp_trans.supplier_id, - ".TB_PREF."supp_trans.rate, - ".TB_PREF."suppliers.supp_name, - ".TB_PREF."suppliers.curr_code, - ".TB_PREF."supp_trans.rate, - (ov_amount+ov_discount)*rate AS NetAmount, - ov_gst*rate AS Tax - FROM ".TB_PREF."supp_trans - INNER JOIN ".TB_PREF."suppliers ON ".TB_PREF."supp_trans.supplier_id=".TB_PREF."suppliers.supplier_id - INNER JOIN ".TB_PREF."sys_types ON ".TB_PREF."supp_trans.type=".TB_PREF."sys_types.type_id - WHERE ".TB_PREF."supp_trans.tran_date >= '$fromdate' - AND ".TB_PREF."supp_trans.tran_date <= '$todate' - AND (".TB_PREF."supp_trans.type=20 OR ".TB_PREF."supp_trans.type=21) - ORDER BY ".TB_PREF."supp_trans.tran_date"; - + $sql = "SELECT taxrec.*, + stype.type_name, + if(supp.supp_name is null, debt.name, supp.supp_name) as name, + branch.br_name + FROM ".TB_PREF."trans_tax_details taxrec + LEFT JOIN ".TB_PREF."supp_trans strans + ON taxrec.trans_no=strans.trans_no AND taxrec.trans_type=strans.type + LEFT JOIN ".TB_PREF."suppliers as supp ON strans.supplier_id=supp.supplier_id + LEFT JOIN ".TB_PREF."debtor_trans dtrans + ON taxrec.trans_no=dtrans.trans_no AND taxrec.trans_type=dtrans.type + 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, + ".TB_PREF."sys_types stype + WHERE taxrec.trans_type=stype.type_id + AND taxrec.trans_type != 13 + AND taxrec.tran_date >= '$fromdate' + AND taxrec.tran_date <= '$todate' + ORDER BY taxrec.tran_date"; +//display_error($sql); return db_query($sql,"No transactions were returned"); } @@ -109,59 +68,13 @@ function getTaxInfo($id) return db_fetch($result); } -function getCustInvTax($taxtype, $from, $to) -{ - $fromdate = date2sql($from); - $todate = date2sql($to); - $amount = "IF(".TB_PREF."debtor_trans_tax_details.debtor_trans_type=11,-amount,amount)*".TB_PREF."debtor_trans.rate"; - $mamount = "IF(".TB_PREF."debtor_trans_tax_details.included_in_price=0, 0, $amount)"; - $netamount = "IF(".TB_PREF."debtor_trans.type=11,-quantity,quantity)*unit_price*".TB_PREF."debtor_trans.rate-$mamount"; - - $sql = "SELECT SUM($netamount), - SUM($amount) - FROM ".TB_PREF."debtor_trans_details, ".TB_PREF."debtor_trans_tax_details, ".TB_PREF."debtor_trans - WHERE ".TB_PREF."debtor_trans.type>=10 - AND ".TB_PREF."debtor_trans.type<=11 - AND ".TB_PREF."debtor_trans_details.debtor_trans_no=".TB_PREF."debtor_trans.trans_no - AND ".TB_PREF."debtor_trans_details.debtor_trans_type=".TB_PREF."debtor_trans.type - AND ".TB_PREF."debtor_trans.trans_no=".TB_PREF."debtor_trans_tax_details.debtor_trans_no - AND ".TB_PREF."debtor_trans.type=".TB_PREF."debtor_trans_tax_details.debtor_trans_type - AND ".TB_PREF."debtor_trans_tax_details.amount <> 0 - AND ".TB_PREF."debtor_trans_tax_details.tax_type_id=$taxtype - AND ".TB_PREF."debtor_trans.tran_date >= '$fromdate' - AND ".TB_PREF."debtor_trans.tran_date <= '$todate'"; - - $result = db_query($sql,"No transactions were returned"); - return db_fetch_row($result); -} - -function getSuppInvTax($taxtype, $from, $to) -{ - $fromdate = date2sql($from); - $todate = date2sql($to); - $sql = "SELECT SUM(unit_price * quantity * ".TB_PREF."supp_trans.rate), SUM(amount*".TB_PREF."supp_trans.rate) - FROM ".TB_PREF."supp_invoice_items, ".TB_PREF."supp_invoice_tax_items, ".TB_PREF."supp_trans - WHERE ".TB_PREF."supp_trans.type>=20 - AND ".TB_PREF."supp_trans.type<=21 - AND ".TB_PREF."supp_trans.trans_no=".TB_PREF."supp_invoice_tax_items.supp_trans_no - AND ".TB_PREF."supp_trans.type=".TB_PREF."supp_invoice_tax_items.supp_trans_type - AND ".TB_PREF."supp_invoice_items.supp_trans_no=".TB_PREF."supp_trans.trans_no - AND ".TB_PREF."supp_invoice_items.supp_trans_type=".TB_PREF."supp_trans.type - AND ".TB_PREF."supp_invoice_tax_items.tax_type_id=$taxtype - AND ".TB_PREF."supp_invoice_tax_items.amount <> 0 - AND ".TB_PREF."supp_trans.tran_date >= '$fromdate' - AND ".TB_PREF."supp_trans.tran_date <= '$todate'"; - - $result = db_query($sql,"No transactions were returned"); - return db_fetch_row($result); -} - //---------------------------------------------------------------------------------------------------- function print_tax_report() { - global $path_to_root; - + global $path_to_root, $trans_dir; + + include_once($path_to_root . "/reporting/includes/pdf_report.inc"); $rep = new FrontReport(_('Tax Report'), "TaxReport.pdf", user_pagesize()); @@ -177,27 +90,18 @@ function print_tax_report() else $summary = _('Detailed Report'); - $res = getTaxTypes(); $taxes = array(); - $i = 0; while ($tax=db_fetch($res)) - $taxes[$i++] = $tax['id']; - $idcounter = count($taxes); - - $totalinvout = array(0,0,0,0,0,0,0,0,0,0); - $totaltaxout = array(0,0,0,0,0,0,0,0,0,0); - $totalinvin = array(0,0,0,0,0,0,0,0,0,0); - $totaltaxin = array(0,0,0,0,0,0,0,0,0,0); + $taxes[$tax['id']] = array('in'=>0, 'out'=>0, 'taxin'=>0, 'taxout'=>0); if (!$summaryOnly) { - $cols = array(0, 80, 130, 190, 290, 370, 435, 500, 565); - - $headers = array(_('Trans Type'), _('#'), _('Date'), _('Name'), _('Branch Name'), - _('Net'), _('Tax'), ''); + $cols = array(0, 80, 130, 180, 290, 370, 455, 505, 555); + $headers = array(_('Trans Type'), _('Ref'), _('Date'), _('Name'), _('Branch Name'), + _('Net'), _('Rate'), _('Tax')); $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right'); $params = array( 0 => $comments, @@ -208,36 +112,29 @@ function print_tax_report() $rep->Info($params, $cols, $headers, $aligns); $rep->Header(); } + $totalnet = 0.0; $totaltax = 0.0; - - $transactions = getCustTransactions($from, $to); + $transactions = getTaxTransactions($from, $to); while ($trans=db_fetch($transactions)) { - $tx = getTax($trans['trans_no'], $trans['type']); - if ($tx === false) - $tax_amt = 0; - else - { - $tx['Amount'] *= $trans['rate']; - if ($trans['type'] == 11) - $tx['Amount'] *= -1; - if ($tx['Included'] > 0) - $trans['NetAmount'] -= $tx['Amount']; - $tax_amt = $tx['Amount']; + if (in_array($trans['trans_type'], array(11,20,1))) { + $trans['net_amount'] *= -1; + $trans['amount'] *= -1; } + if (!$summaryOnly) { $rep->TextCol(0, 1, $trans['type_name']); - $rep->TextCol(1, 2, $trans['reference']); + $rep->TextCol(1, 2, $trans['memo']); $rep->TextCol(2, 3, sql2date($trans['tran_date'])); $rep->TextCol(3, 4, $trans['name']); - if ($trans["branch_code"] > 0) - $rep->TextCol(4, 5, get_branch_name($trans["branch_code"])); + $rep->TextCol(4, 5, $trans['br_name']); - $rep->TextCol(5, 6, number_format2($trans['NetAmount'], $dec)); - $rep->TextCol(6, 7, number_format2($tax_amt, $dec)); + $rep->TextCol(5, 6, number_format2($trans['net_amount'], $dec)); + $rep->TextCol(6, 7, number_format2($trans['rate'], $dec)); + $rep->TextCol(7, 8, number_format2($trans['amount'], $dec)); $rep->NewLine(); @@ -247,231 +144,67 @@ function print_tax_report() $rep->Header(); } } - $totalnet += $trans['NetAmount']; - $totaltax += $tax_amt; - - } - if (!$summaryOnly) - { - $rep->NewLine(); - - if ($rep->row < $rep->bottomMargin + $rep->lineHeight) - { - $rep->Line($rep->row - 2); - $rep->Header(); - } - $rep->Line($rep->row + $rep->lineHeight); - $rep->TextCol(3, 5, _('Total Outputs')); - $rep->TextCol(5, 6, number_format2($totalnet, $dec)); - $rep->TextCol(6, 7, number_format2($totaltax, $dec)); - $rep->Line($rep->row - 5); - $rep->Header(); - } - $totalinnet = 0.0; - $totalintax = 0.0; - - $transactions = getSuppTransactions($from, $to); - - while ($trans=db_fetch($transactions)) - { - if (!$summaryOnly) - { - $rep->TextCol(0, 1, $trans['type_name']); - $rep->TextCol(1, 2, $trans['supp_reference']); - $rep->TextCol(2, 3, sql2date($trans['tran_date'])); - $rep->TextCol(3, 5, $trans['supp_name']); - $rep->TextCol(5, 6, number_format2($trans['NetAmount'], $dec)); - $rep->TextCol(6, 7, number_format2($trans['Tax'], $dec)); - - $rep->NewLine(); - if ($rep->row < $rep->bottomMargin + $rep->lineHeight) - { - $rep->Line($rep->row - 2); - $rep->Header(); - } - } - $totalinnet += $trans['NetAmount']; - $totalintax += $trans['Tax']; - - } - if (!$summaryOnly) - { - $rep->NewLine(); - - if ($rep->row < $rep->bottomMargin + $rep->lineHeight) - { - $rep->Line($rep->row - 2); - $rep->Header(); + if ($trans['amount'] > 0) { + $taxes[$trans['tax_type_id']]['taxin'] += $trans['amount']; + $taxes[$trans['tax_type_id']]['in'] += $trans['net_amount']; + } else { + $taxes[$trans['tax_type_id']]['taxout'] -= $trans['amount']; + $taxes[$trans['tax_type_id']]['out'] -= $trans['net_amount']; } - $rep->Line($rep->row + $rep->lineHeight); - $rep->TextCol(3, 5, _('Total Inputs')); - $rep->TextCol(5, 6, number_format2($totalinnet, $dec)); - $rep->TextCol(6, 7, number_format2($totalintax, $dec)); - $rep->Line($rep->row - 5); + + $totalnet += $trans['net_amount']; + $totaltax += $trans['amount']; } - $cols2 = array(0, 100, 200, 300, 400, 500, 600); + + // Summary + $cols2 = array(0, 100, 180, 260, 340, 420, 500); - $headers2 = array(_('Tax Rate'), _('Outputs'), _('Output Tax'), _('Inputs'), _('Input Tax'), '', ''); + $headers2 = array(_('Tax Rate'), _('Outputs'), _('Output Tax'), _('Inputs'), _('Input Tax'), _('Net Tax')); $aligns2 = array('left', 'right', 'right', 'right', 'right', 'right', 'right'); - $invamount = 0.0; - for ($i = 0; $i < $idcounter; $i++) - { - $amt = getCustInvTax($taxes[$i], $from, $to); - $totalinvout[$i] += $amt[0]; - $totaltaxout[$i] += $amt[1]; - $invamount += $amt[0]; - } - if ($totalnet != $invamount) - { - $totalinvout[$idcounter] = ($totalnet - $invamount); - $totaltaxout[$idcounter] = 0.0; - } - $invamount2 = 0.0; - for ($i = 0; $i < $idcounter; $i++) - { - $amt = getSuppInvTax($taxes[$i], $from, $to); - $totalinvin[$i] += $amt[0]; - $totaltaxin[$i] += $amt[1]; - $invamount2 += $amt[0]; - } - if ($totalinnet != $invamount2) - { - $totalinvin[$idcounter] = ($totalinnet - $invamount2); - $totaltaxin[$idcounter] = 0.0; - } - if ($totalnet != $invamount || $totalinnet != $invamount2) - $idcounter++; for ($i = 0; $i < count($cols2); $i++) - { $rep->cols[$i] = $rep->leftMargin + $cols2[$i]; - $rep->headers[$i] = $headers2[$i]; - $rep->aligns[$i] = $aligns2[$i]; - } + + $rep->headers = $headers2; + $rep->aligns = $aligns2; $rep->Header(); - //$counter = count($totalinvout); - //$counter = max($counter, $idcounter); - $trow = $rep->row; - $i = 0; - for ($j = 0; $j < $idcounter; $j++) - { - if (isset($taxes[$j]) && $taxes[$j] > 0) - { - $tx = getTaxInfo($taxes[$j]); - $str = $tx['name'] . " " . number_format2($tx['rate'], $dec) . "%"; - } - else - $str = _('No tax specified'); - $rep->TextCol($i, $i + 1, $str); - $rep->NewLine(); - } - $i++; - $rep->row = $trow; - for ($j = 0; $j < $idcounter; $j++) - { - $rep->TextCol($i, $i + 1, number_format2($totalinvout[$j], $dec)); + $taxtotal = 0; + foreach( $taxes as $id=>$sum) + { + $tx = getTaxInfo($id); + + $rep->TextCol(0, 1, $tx['name'] . " " . number_format2($tx['rate'], $dec) . "%"); + $rep->TextCol(1, 2, number_format2($sum['out'], $dec)); + $rep->TextCol(2, 3,number_format2($sum['taxout'], $dec)); + $rep->TextCol(3, 4, number_format2($sum['in'], $dec)); + $rep->TextCol(4, 5,number_format2($sum['taxin'], $dec)); + $rep->TextCol(5, 6, number_format2($sum['taxin']-$sum['taxout'], $dec)); + $taxtotal += $sum['taxin']-$sum['taxout']; $rep->NewLine(); } - $i++; - $rep->row = $trow; - for ($j = 0; $j < $idcounter; $j++) - { - $rep->TextCol($i, $i + 1,number_format2($totaltaxout[$j], $dec)); - $rep->NewLine(); - } - $i++; - $rep->row = $trow; - for ($j = 0; $j < $idcounter; $j++) - { - $rep->TextCol($i, $i + 1, number_format2($totalinvin[$j], $dec)); - $rep->NewLine(); - } - $i++; - $rep->row = $trow; - for ($j = 0; $j < $idcounter; $j++) - { - $rep->TextCol($i, $i + 1, number_format2($totaltaxin[$j], $dec)); - $rep->NewLine(); - } - $rep->Line($rep->row - 4); - - $rep->row -= 16; - $rep->Font('italic'); - $rep->TextCol(0, 1, _("General Ledger")); - $rep->aligns[1] = 'left'; - $rep->TextCol(1, 3, _("Description")); - $rep->TextCol(3, 4, _("Amount")); - $rep->Font(); - $rep->Line($rep->row - 6); - $rep->row -= 22; - - $taxes = getTaxTypes(); - $total = 0; - $bdate = date2sql($from); - $edate = date2sql($to); - - while ($tx = db_fetch($taxes)) - { - if ($tx['sales_gl_code'] == $tx['purchasing_gl_code']) - { - $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) AS payable, SUM(IF(amount < 0, -amount, 0)) AS collectible - FROM ".TB_PREF."gl_trans WHERE account = '".$tx['sales_gl_code']."' AND tran_date >= '$bdate' AND tran_date <= '$edate'"; - $result = db_query($sql, "Error retrieving tax inquiry"); - $row = db_fetch($result); - $payable = -$row['payable']; - $collectible.= -$row['collectible']; - } - else - { - $sql = "SELECT SUM(amount) AS collectible - FROM ".TB_PREF."gl_trans WHERE account = '".$tx['sales_gl_code']."' AND tran_date >= '$bdate' AND tran_date <= '$edate'"; - $result = db_query($sql, "Error retrieving tax inquiry"); - $row = db_fetch($result); - $collectible = -$row['collectible']; - $sql = "SELECT SUM(amount) AS payable - FROM ".TB_PREF."gl_trans WHERE account = '".$tx['purchasing_gl_code']."' AND tran_date >= '$bdate' AND tran_date <= '$edate'"; - $result = db_query($sql, "Error retrieving tax inquiry"); - $row = db_fetch($result); - $payable = -$row['payable']; - } - $net = $collectible + $payable; - $total += $net; - $rep->TextCol(0, 1, $tx['name'] . " " . $tx['rate'] . "%"); - $rep->TextCol(1, 3, _("Charged on sales") . " (" . _("Output Tax")."):"); - $rep->TextCol(3, 4, number_format2($collectible, $dec)); - $rep->NewLine(); - $rep->TextCol(0, 1, $tx['name'] . " " . $tx['rate'] . "%"); - $rep->TextCol(1, 3, _("Paid on purchases") . " (" . _("Input Tax")."):"); - $rep->TextCol(3, 4, number_format2($payable, $dec)); - $rep->NewLine(); - $rep->Font('bold'); - $rep->TextCol(0, 1, $tx['name'] . " " . $tx['rate'] . "%"); - $rep->TextCol(1, 3, _("Net payable or collectible")); - $rep->TextCol(3, 4, number_format2($net, $dec)); - $rep->Font(); - $rep->NewLine(); - } $rep->Font('bold'); - $rep->TextCol(1, 3, _("Total payable or refund")); - $rep->TextCol(3, 4, number_format2($total, $dec)); + $rep->NewLine(); + $rep->Line($rep->row + $rep->lineHeight); + $rep->TextCol(3, 5, _("Total payable or refund")); + $rep->TextCol(5, 6, number_format2($taxtotal, $dec)); + $rep->Line($rep->row - 5); $rep->Font(); $rep->NewLine(); - - $rep->Line($rep->row - 4); $locale = $path_to_root . "/lang/" . $_SESSION['language']->code . "/locale.inc"; if (file_exists($locale)) { $taxinclude = true; include($locale); - /* - if (function_exists("TaxFunction")) - TaxFunction(); - */ + +// if (function_exists("TaxFunction")) +// TaxFunction(); + } + $rep->End(); } diff --git a/sales/includes/db/cust_trans_details_db.inc b/sales/includes/db/cust_trans_details_db.inc index 903c8a92..8d7ce386 100644 --- a/sales/includes/db/cust_trans_details_db.inc +++ b/sales/includes/db/cust_trans_details_db.inc @@ -52,42 +52,6 @@ function void_customer_trans_details($type, $type_no) } //---------------------------------------------------------------------------------------- -function add_customer_trans_tax_detail_item($debtor_trans_type, $debtor_trans_no, - $tax_type_id, $rate, $included_in_price, $amount) -{ - $sql = "INSERT INTO ".TB_PREF."debtor_trans_tax_details (debtor_trans_no, debtor_trans_type, tax_type_id, rate, included_in_price, amount) - VALUES ($debtor_trans_no, $debtor_trans_type, $tax_type_id, $rate, $included_in_price, $amount)"; - - db_query($sql, "The debtor transaction tax detail could not be added"); -} - -//---------------------------------------------------------------------------------------- - -function get_customer_trans_tax_details($debtor_trans_type, $debtor_trans_no) -{ - $sql = "SELECT ".TB_PREF."debtor_trans_tax_details.*, ".TB_PREF."tax_types.name AS tax_type_name - FROM ".TB_PREF."debtor_trans_tax_details,".TB_PREF."tax_types - WHERE debtor_trans_no=$debtor_trans_no - AND debtor_trans_type=$debtor_trans_type - AND amount != 0 - AND ".TB_PREF."tax_types.id = ".TB_PREF."debtor_trans_tax_details.tax_type_id"; - - return db_query($sql, "The debtor transaction tax details could not be queried"); -} - -//---------------------------------------------------------------------------------------- - -function void_customer_trans_tax_details($type, $type_no) -{ - $sql = "UPDATE ".TB_PREF."debtor_trans_tax_details SET amount=0 - WHERE debtor_trans_no=$type_no - AND debtor_trans_type=$type"; - - db_query($sql, "The debtor transaction tax details could not be voided"); -} - -//---------------------------------------------------------------------------------------- - function write_customer_trans_detail_item($debtor_trans_type, $debtor_trans_no, $stock_id, $description, $quantity, $unit_price, $unit_tax, $discount_percent, $std_cost, $line_id=0) { diff --git a/sales/includes/db/sales_credit_db.inc b/sales/includes/db/sales_credit_db.inc index 7db5f664..292edee4 100644 --- a/sales/includes/db/sales_credit_db.inc +++ b/sales/includes/db/sales_credit_db.inc @@ -81,7 +81,7 @@ function write_credit_note($credit_note, $write_off_acc) void_cust_allocations(11, $credit_no, $credit_date); void_gl_trans(11, $credit_no, true); void_stock_move(11, $credit_no); - void_customer_trans_tax_details(11, $credit_no); + void_trans_tax_details(11, $credit_no); } if ($credit_invoice) { $invoice_alloc_balance = get_DebtorTrans_allocation_balance(10, $credit_invoice); @@ -153,8 +153,9 @@ function write_credit_note($credit_note, $write_off_acc) foreach ($taxes as $taxitem) { if ($taxitem['Value'] != 0) { - add_customer_trans_tax_detail_item(11, $credit_no, $taxitem['tax_type_id'], - $taxitem['rate'], $credit_note->tax_included, $taxitem['Value']); + add_trans_tax_details(11, $credit_no, $taxitem['tax_type_id'], + $taxitem['rate'], $credit_note->tax_included, $taxitem['Value'], + $taxitem['Net'], $credit_note->document_date, $credit_note->reference); $total += add_gl_trans_customer(11, $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 8fb7b1eb..1e48f9fd 100644 --- a/sales/includes/db/sales_delivery_db.inc +++ b/sales/includes/db/sales_delivery_db.inc @@ -48,7 +48,7 @@ function write_sales_delivery(&$delivery,$bo_policy) } else { void_gl_trans(13, $delivery_no, true); void_stock_move(13, $delivery_no); - void_customer_trans_tax_details(13, $delivery_no); + void_trans_tax_details(13, $delivery_no); delete_comments(13, $delivery_no); } @@ -124,8 +124,9 @@ function write_sales_delivery(&$delivery,$bo_policy) // taxes - this is for printing purposes foreach ($taxes as $taxitem) { if ($taxitem['Value'] != 0) { - add_customer_trans_tax_detail_item(13, $delivery_no, $taxitem['tax_type_id'], - $taxitem['rate'], $delivery->tax_included, $taxitem['Value']); + add_trans_tax_details(13, $delivery_no, $taxitem['tax_type_id'], + $taxitem['rate'], $delivery->tax_included, $taxitem['Value'], + $taxitem['Net'], $delivery->document_date, $delivery->reference ); } } @@ -165,7 +166,7 @@ function void_sales_delivery($type, $type_no) // clear details after they've been reversed in the sales order void_customer_trans_details($type, $type_no); - void_customer_trans_tax_details($type, $type_no); + void_trans_tax_details($type, $type_no); void_cust_allocations($type, $type_no); diff --git a/sales/includes/db/sales_invoice_db.inc b/sales/includes/db/sales_invoice_db.inc index 8a128cb3..3f84c3e9 100644 --- a/sales/includes/db/sales_invoice_db.inc +++ b/sales/includes/db/sales_invoice_db.inc @@ -80,7 +80,7 @@ function write_sales_invoice(&$invoice) delete_comments(10, $invoice_no); void_gl_trans(10, $invoice_no, true); void_cust_allocations(10, $invoice_no); // ? - void_customer_trans_tax_details(10, $invoice_no); + void_trans_tax_details(10, $invoice_no); } $total = 0; foreach ($invoice->line_items as $invoice_line) { @@ -146,9 +146,11 @@ function write_sales_invoice(&$invoice) } // post all taxes foreach ($taxes as $taxitem) { + if ($taxitem['Value'] != 0) { - add_customer_trans_tax_detail_item(10, $invoice_no, $taxitem['tax_type_id'], - $taxitem['rate'], $invoice->tax_included, $taxitem['Value']); + add_trans_tax_details(10, $invoice_no, $taxitem['tax_type_id'], + $taxitem['rate'], $invoice->tax_included, $taxitem['Value'], + $taxitem['Net'], $date_, $invoice->reference); $total += add_gl_trans_customer(10, $invoice_no, $date_, $taxitem['sales_gl_code'], 0, 0, (-$taxitem['Value']), $invoice->customer_id, @@ -210,7 +212,7 @@ function void_sales_invoice($type, $type_no) // clear details after they've been reversed in the sales order void_customer_trans_details($type, $type_no); - void_customer_trans_tax_details($type, $type_no); + void_trans_tax_details($type, $type_no); void_cust_allocations($type, $type_no); diff --git a/sales/view/view_credit.php b/sales/view/view_credit.php index 0ddf27f4..bdc50620 100644 --- a/sales/view/view_credit.php +++ b/sales/view/view_credit.php @@ -137,7 +137,7 @@ if ($sub_total != 0) "nowrap align=right width=15%"); label_row(_("Shipping"), $display_freight, "colspan=6 align=right", "nowrap align=right"); -$tax_items = get_customer_trans_tax_details(11, $trans_id); +$tax_items = get_trans_tax_details(11, $trans_id); display_customer_trans_tax_details($tax_items, 6); label_row("" . _("TOTAL CREDIT") . " + "SELECT tr.tran_date, tr.type, tr.trans_no, dt.tax_type_id, + dt.rate, dt.included_in_price, dt.amount, tr.reference as ref + FROM ".$pref."debtor_trans_tax_details dt + LEFT JOIN ".$pref."trans_tax_details tt + ON dt.debtor_trans_no=tt.trans_no + AND dt.debtor_trans_type=tt.trans_type, + ".$pref."debtor_trans tr + WHERE tt.trans_type is NULL + AND dt.debtor_trans_no = tr.trans_no + AND dt.debtor_trans_type = tr.type", + + "supp_invoice_tax_items" => + "SELECT tr.tran_date, tr.type, tr.trans_no, st.tax_type_id, + st.rate, st.included_in_price, st.amount, tr.supp_reference as ref + FROM ".$pref."supp_invoice_tax_items st + LEFT JOIN ".$pref."trans_tax_details tt + ON st.supp_trans_no=tt.trans_no + AND st.supp_trans_type=tt.trans_type, + ".$pref."supp_trans tr + WHERE tt.trans_type is NULL + AND st.supp_trans_no = tr.trans_no + AND st.supp_trans_type = tr.type"); + + foreach ($move_sql as $tbl => $sql) { + if (!check_table($pref, $tbl)){ + $res = db_query($sql, "Cannot retrieve trans tax details from $tbl"); + while ($row = db_fetch($res)) { + $net_amount = $row['rate'] == 0 ? + 0 : ($row['included_in_price'] ? + ($row['amount']/$row['rate']*(100-$row['rate'])) + :($row['amount']/$row['rate']*100)); + $sql2 = "INSERT INTO ".$pref."trans_tax_details + (trans_type,trans_no,tran_date,tax_type_id,rate, + included_in_price, net_amount, amount, memo) + VALUES ('".$row['type']."','".$row['trans_no']."','" + .$row['tran_date']."','".$row['tax_type_id']."','" + .$row['rate']."','".$row['included_in_price']."','" + .$net_amount + ."','".$row['amount']."','".$row['ref']."')"; + db_query($sql2, "Cannot move trans tax details from $tbl"); + } + db_query("DROP TABLE ".$pref.$tbl, "cannot remove $tbl"); + } + } return true; } // // Checking before install // - function pre_check($pref) + function pre_check($pref) { + // We cannot perform successfull upgrade on system where the + // trans tax details tables was deleted during previous try. + if (check_table($pref, 'debtor_trans_tax_details') + || check_table($pref, 'supp_invoice_tax_items')) { + display_error(_("Seems that system upgrade to version 2.1 has + been performed for this company already.
If something has gone + wrong and you want to retry upgrade process you MUST perform + database restore from last backup file first.")); + + return false; + } + return true; // true when ok, fail otherwise } // @@ -85,6 +149,7 @@ class fa2_1 { if (check_table($pref, 'suppliers', 'credit_limit')) return false; if (check_table($pref, 'bank_trans', 'reconciled', array('Type'=>'date'))) return false; + if (check_table($pref, 'trans_tax_details')) return false; return true; } }; diff --git a/sql/alter2.1.sql b/sql/alter2.1.sql index 26ab1d2f..05d16b35 100644 --- a/sql/alter2.1.sql +++ b/sql/alter2.1.sql @@ -291,3 +291,20 @@ ALTER TABLE `0_stock_category` ADD `inactive` tinyint(1) NOT NULL DEFAULT '0'; ALTER TABLE `0_item_units` DROP COLUMN `inactive`; ALTER TABLE `0_item_units` ADD `inactive` tinyint(1) NOT NULL DEFAULT '0'; + +DROP TABLE IF EXISTS `0_trans_tax_details`; + +CREATE TABLE `0_trans_tax_details` ( + `id` int(11) NOT NULL auto_increment, + `trans_type` smallint(6) default NULL, + `trans_no` int(11) default NULL, + `tran_date` date NOT NULL, + `tax_type_id` int(11) NOT NULL default '0', + `rate` double NOT NULL default '0', + `included_in_price` tinyint(1) NOT NULL default '0', + `net_amount` double NOT NULL default '0', + `amount` double NOT NULL default '0', + `memo` tinytext default NULL, + PRIMARY KEY (`id`) +) TYPE=InnoDB AUTO_INCREMENT=1 ; + diff --git a/taxes/tax_calc.inc b/taxes/tax_calc.inc index 2cbb2199..aa8f9de8 100644 --- a/taxes/tax_calc.inc +++ b/taxes/tax_calc.inc @@ -173,7 +173,10 @@ function get_tax_for_items($items, $prices, $shipping_cost, $tax_group, $tax_inc $ret_tax_array = $tax_items_array; else $ret_tax_array = get_tax_group_items_as_array($tax_group); - + + foreach($ret_tax_array as $k=>$t) + $ret_tax_array[$k]['Net'] = 0; + // loop for all items for ($i = 0; $i < count($items); $i++) { @@ -184,14 +187,17 @@ function get_tax_for_items($items, $prices, $shipping_cost, $tax_group, $tax_inc foreach ($item_taxes as $item_tax) { $index = $item_tax['tax_type_id']; - if($tax_included==1) // 2008-11-26 Joe Hunt Taxes are stored without roundings + if($tax_included==1) {// 2008-11-26 Joe Hunt Taxes are stored without roundings //$ret_tax_array[$index]['Value'] += round($prices[$i] * $item_tax['rate'] // / ($item_tax['rate'] + 100), user_price_dec()); $ret_tax_array[$index]['Value'] += ($prices[$i] * $item_tax['rate'] / ($item_tax['rate'] + 100)); - else + $ret_tax_array[$index]['Net'] += ($prices[$i] * 100 / ($item_tax['rate'] + 100)); + } else { //$ret_tax_array[$index]['Value'] += // round($prices[$i] * $item_tax['rate'] / 100, user_price_dec()); $ret_tax_array[$index]['Value'] += ($prices[$i] * $item_tax['rate'] / 100); + $ret_tax_array[$index]['Net'] += $prices[$i]; + } } } } @@ -206,14 +212,17 @@ function get_tax_for_items($items, $prices, $shipping_cost, $tax_group, $tax_inc { $index = $item_tax['tax_type_id']; if(isset($ret_tax_array[$index])) { - if($tax_included==1) // 2008-11-26 Joe Hunt Taxes are stored without roundings + if($tax_included==1) {// 2008-11-26 Joe Hunt Taxes are stored without roundings //$ret_tax_array[$index]['Value'] += round($shipping_cost * $item_tax['rate'] // / ($item_tax['rate'] + 100), user_price_dec()); $ret_tax_array[$index]['Value'] += ($shipping_cost * $item_tax['rate'] / ($item_tax['rate'] + 100)); - else + $ret_tax_array[$index]['Net'] += ($shipping_cost * 100 / ($item_tax['rate'] + 100)); + } else { //$ret_tax_array[$index]['Value'] += // round($shipping_cost * $item_tax['rate'] / 100, user_price_dec()); $ret_tax_array[$index]['Value'] += ($shipping_cost * $item_tax['rate'] / 100); + $ret_tax_array[$index]['Net'] += $shipping_cost; + } } } }