From 871bc1f187ad6e5622a80c7dd8aadc48d8ea70fd Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Thu, 29 Jan 2009 20:32:04 +0000 Subject: [PATCH 1/1] Additonal fixes to tax on foreign currency transactions. --- gl/includes/db/gl_db_banking.inc | 4 ++- gl/includes/db/gl_db_trans.inc | 46 ++++++++++++++++++++++----- gl/inquiry/tax_inquiry.php | 32 ++++--------------- purchasing/includes/db/invoice_db.inc | 5 +-- reporting/rep709.php | 8 ++--- sql/alter2.1.php | 12 ++++--- sql/alter2.1.sql | 1 + 7 files changed, 62 insertions(+), 46 deletions(-) diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index 7e465091..57412a23 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -147,8 +147,10 @@ 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; + $ex_rate = get_exchange_rate_from_home_currency($currency, $date_); + add_gl_tax_details($gl_item->code_id, $trans_type, $trans_no, $amount, - $date_, $memo); + $ex_rate, $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 ae7ac397..d8e6d58a 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -211,7 +211,7 @@ function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, //-------------------------------------------------------------------------------- // Stores journal/bank transaction tax details if applicable // -function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $date, $memo) +function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, $date, $memo) { $tax_type = is_tax_account($gl_code); if(!$tax_type) return; // $gl_code is not tax account @@ -228,8 +228,9 @@ function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $date, $m // 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); + $amount, $net_amount, $ex_rate, $date, $memo); } @@ -239,14 +240,16 @@ function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $date, $m // actual tax type rate. // function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included, - $amount, $net_amount, $tran_date, $memo) + $amount, $net_amount, $ex_rate, $tran_date, $memo) { + $sql = "INSERT INTO ".TB_PREF."trans_tax_details - (trans_type, trans_no, tran_date, tax_type_id, rate, + (trans_type, trans_no, tran_date, tax_type_id, rate, ex_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)."," + .$rate.",".$ex_rate.",".($included ? 1:0)."," + .db_escape($net_amount)."," .db_escape($amount).",".db_escape($memo).")"; db_query($sql, "Cannot save trans tax details"); @@ -260,7 +263,7 @@ function get_trans_tax_details($trans_type, $trans_no) FROM ".TB_PREF."trans_tax_details,".TB_PREF."tax_types WHERE trans_type = $trans_type AND trans_no = $trans_no - AND amount != 0 + AND (net_amount != 0 OR 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"); @@ -277,6 +280,33 @@ function void_trans_tax_details($type, $type_no) db_query($sql, "The transaction tax details could not be voided"); } +function get_tax_summary($from, $to) +{ + $fromdate = date2sql($from); + $todate = date2sql($to); + + $sql = "SELECT + SUM(IF( trans_type=1 || trans_type=11 || trans_type=20,-1,1)* + IF(trans_type=0 || trans_type=2 || trans_type=10 || trans_type=11, net_amount*ex_rate,0)) net_input, + SUM(IF( trans_type=1 || trans_type=11 || trans_type=20,-1,1)* + IF(trans_type=0 || trans_type=2 || trans_type=10 || trans_type=11, amount*ex_rate,0)) payable, + SUM(IF( trans_type=1 || trans_type=11 || trans_type=20,-1,1)* + IF(trans_type=0 || trans_type=2 || trans_type=10 || trans_type=11, 0, net_amount*ex_rate)) net_output, + SUM(IF( trans_type=1 || trans_type=11 || trans_type=20,-1,1)* + IF(trans_type=0 || trans_type=2 || trans_type=10 || trans_type=11, 0, amount*ex_rate)) 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 != 13 + AND taxrec.tran_date >= '$fromdate' + AND taxrec.tran_date <= '$todate' + GROUP BY ttype.id"; +//display_error($sql); + return db_query($sql,"Cannot retrieve tax summary"); +} //-------------------------------------------------------------------------------- function add_journal_entries($items, $date_, $ref, $reverse, $memo_=null) { @@ -302,7 +332,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, $date_, $memo_); + $trans_type, $trans_id, -$journal_item->amount, 1, $date_, $memo_); } add_comments($trans_type, $trans_id, $date_, $memo_); @@ -334,7 +364,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, $date, $memo_); + $trans_type, $trans_id, $journal_item->amount, 1, $date, $memo_); } add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_); diff --git a/gl/inquiry/tax_inquiry.php b/gl/inquiry/tax_inquiry.php index 0dc7d8f2..b7ca2de3 100644 --- a/gl/inquiry/tax_inquiry.php +++ b/gl/inquiry/tax_inquiry.php @@ -83,8 +83,6 @@ function show_results() { global $path_to_root, $table_style; - $taxes = get_tax_types(); - /*Now get the transactions */ div_start('trans_tbl'); start_table($table_style); @@ -95,31 +93,15 @@ function show_results() $total = 0; $bdate = date2sql($_POST['TransFromDate']); $edate = date2sql($_POST['TransToDate']); + + $taxes = get_tax_summary($_POST['TransFromDate'], $_POST['TransToDate']); + 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; + + $payable = $tx['payable']; + $collectible = $tx['collectible']; + $net = $collectible - $payable; $total += $net; alt_table_row_color($k); label_cell($tx['name'] . " " . $tx['rate'] . "%"); diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index 8c0ca4ca..d2331c17 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -144,6 +144,7 @@ function add_supp_invoice($supp_trans, $invoice_no=0) // do not receive as ref b } $date_ = $supp_trans->tran_date; + $ex_rate = get_exchange_rate_from_home_currency(get_supplier_currency($supp_trans->supplier_id), $date_); /*First insert the invoice into the supp_trans table*/ $invoice_id = add_supp_trans($trans_type, $supp_trans->supplier_id, $date_, $supp_trans->due_date, @@ -188,7 +189,7 @@ 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, - $date_, $supp_trans->supp_reference); + $ex_rate, $date_, $supp_trans->supp_reference); } foreach ($supp_trans->grn_items as $entered_grn) { @@ -270,7 +271,7 @@ function add_supp_invoice($supp_trans, $invoice_no=0) // do not receive as ref b // here we suppose that tax is never included in price (we are company customer). add_trans_tax_details($trans_type, $invoice_id, $taxitem['tax_type_id'], $taxitem['rate'], 0, $taxitem['Value'], - $taxitem['Net'], $date_, $supp_trans->supp_reference); + $taxitem['Net'], $ex_rate, $date_, $supp_trans->supp_reference); $total += add_gl_trans_supplier($trans_type, $invoice_id, $date_, $taxitem['purchasing_gl_code'], 0, 0, $taxitem['Value'], diff --git a/reporting/rep709.php b/reporting/rep709.php index 13a0c4c6..842a7663 100644 --- a/reporting/rep709.php +++ b/reporting/rep709.php @@ -33,12 +33,10 @@ function getTaxTransactions($from, $to) $fromdate = date2sql($from); $todate = date2sql($to); - $sql = "SELECT taxrec.*, IF(ISNULL(dtrans.rate),IF(ISNULL(strans.rate), taxrec.amount, taxrec.amount*strans.rate), - taxrec.amount*dtrans.rate) AS amount, - IF(ISNULL(dtrans.rate),IF(ISNULL(strans.rate), taxrec.net_amount,taxrec.net_amount*strans.rate), - taxrec.net_amount*dtrans.rate) AS net_amount, + $sql = "SELECT taxrec.*, taxrec.amount*ex_rate AS amount, + taxrec.net_amount*ex_rate AS net_amount, stype.type_name, - if(supp.supp_name is null, debt.name, supp.supp_name) as name, + IF(ISNULL(supp.supp_name), debt.name, supp.supp_name) as name, branch.br_name FROM ".TB_PREF."trans_tax_details taxrec LEFT JOIN ".TB_PREF."supp_trans strans diff --git a/sql/alter2.1.php b/sql/alter2.1.php index 155aa4a4..ddfb6fe2 100644 --- a/sql/alter2.1.php +++ b/sql/alter2.1.php @@ -75,7 +75,8 @@ class fa2_1 { $move_sql =array( "debtor_trans_tax_details" => "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 + dt.rate, dt.included_in_price, dt.amount, tr.reference as ref, + tr.rate as ex_rate FROM ".$pref."debtor_trans_tax_details dt LEFT JOIN ".$pref."trans_tax_details tt ON dt.debtor_trans_no=tt.trans_no @@ -87,7 +88,8 @@ class fa2_1 { "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 + st.rate, st.included_in_price, st.amount, tr.supp_reference as ref, + tr.rate as ex_rate FROM ".$pref."supp_invoice_tax_items st LEFT JOIN ".$pref."trans_tax_details tt ON st.supp_trans_no=tt.trans_no @@ -106,12 +108,12 @@ class fa2_1 { ($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, + (trans_type,trans_no,tran_date,tax_type_id,rate,ex_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['rate']."','".$row['ex_rate']."','" + .$row['included_in_price']."','".$net_amount ."','".$row['amount']."','".$row['ref']."')"; db_query($sql2, "Cannot move trans tax details from $tbl"); } diff --git a/sql/alter2.1.sql b/sql/alter2.1.sql index 05d16b35..ae4ba4c6 100644 --- a/sql/alter2.1.sql +++ b/sql/alter2.1.sql @@ -301,6 +301,7 @@ CREATE TABLE `0_trans_tax_details` ( `tran_date` date NOT NULL, `tax_type_id` int(11) NOT NULL default '0', `rate` double NOT NULL default '0', + `ex_rate` double NOT NULL default '1', `included_in_price` tinyint(1) NOT NULL default '0', `net_amount` double NOT NULL default '0', `amount` double NOT NULL default '0', -- 2.30.2