From 702a71dbe463765b5fd1892a982ede3e5f264dee Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Sun, 30 May 2010 23:37:47 +0000 Subject: [PATCH] Allowing modifying of Bank Payments/Deposits --- CHANGELOG.txt | 10 ++ gl/gl_bank.php | 133 ++++++++++++++++++++- gl/includes/db/gl_db_bank_trans.inc | 34 ++++++ gl/includes/db/gl_db_banking.inc | 141 ++++++++++++++++++++++- gl/includes/db/gl_db_trans.inc | 27 +++++ gl/inquiry/journal_inquiry.php | 6 +- purchasing/includes/db/supp_trans_db.inc | 44 +++++++ sales/includes/db/cust_trans_db.inc | 56 +++++++++ 8 files changed, 441 insertions(+), 10 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index 9c25fbcc..27b4e7b5 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,6 +19,16 @@ Legend: ! -> Note $ -> Affected files +31-May-2010 Joe Hunt +! Allowing modifying of Bank Payments/Deposits +$ /gl/gl_bank.php + /gl/includes/db/gl_db_banking.inc + /gl/includes/db/gl_db_bank_trans.inc + /gl/includes/db/gl_db_trans.inc + /gl/inquiry/journal_inquiry.php + /purchasing/includes/db/supp_trans_db.inc + /sales/includes/db/cust_trans_db.inc + 27-May-2010 Merged changes from main trunk (2.2.8, 2.2.9) as follows ------------------------------- Release 2.2.9 ---------------------------------- diff --git a/gl/gl_bank.php b/gl/gl_bank.php index 1c85bcca..6ef02631 100644 --- a/gl/gl_bank.php +++ b/gl/gl_bank.php @@ -36,6 +36,12 @@ if (isset($_GET['NewPayment'])) { } else if(isset($_GET['NewDeposit'])) { $_SESSION['page_title'] = _($help_context = "Bank Account Deposit Entry"); handle_new_order(ST_BANKDEPOSIT); +} else if(isset($_GET['ModifyPayment'])) { + $_SESSION['page_title'] = _($help_context = "Modify Bank Account Entry")." #".$_GET['trans_no']; + create_cart(ST_BANKPAYMENT, $_GET['trans_no']); +} else if(isset($_GET['ModifyDeposit'])) { + $_SESSION['page_title'] = _($help_context = "Modify Bank Account Entry")." #".$_GET['trans_no']; + create_cart(ST_BANKDEPOSIT, $_GET['trans_no']); } page($_SESSION['page_title'], false, false, '', $js); @@ -75,6 +81,22 @@ if (isset($_GET['AddedID'])) display_footer_exit(); } +if (isset($_GET['UpdatedID'])) +{ + $trans_no = $_GET['UpdatedID']; + $trans_type = ST_BANKPAYMENT; + + display_notification_centered(_("Payment $trans_no has been modified")); + + display_note(get_gl_view_str($trans_type, $trans_no, _("&View the GL Postings for this Payment"))); + + hyperlink_params($_SERVER['PHP_SELF'], _("Enter Another &Payment"), "NewPayment=yes"); + + hyperlink_params($_SERVER['PHP_SELF'], _("Enter A &Deposit"), "NewDeposit=yes"); + + display_footer_exit(); +} + if (isset($_GET['AddedDep'])) { $trans_no = $_GET['AddedDep']; @@ -90,6 +112,22 @@ if (isset($_GET['AddedDep'])) display_footer_exit(); } +if (isset($_GET['UpdatedDep'])) +{ + $trans_no = $_GET['UpdatedDep']; + $trans_type = ST_BANKDEPOSIT; + + display_notification_centered(_("Deposit $trans_no has been modified")); + + display_note(get_gl_view_str($trans_type, $trans_no, _("&View the GL Postings for this Deposit"))); + + hyperlink_params($_SERVER['PHP_SELF'], _("Enter Another &Deposit"), "NewDeposit=yes"); + + hyperlink_params($_SERVER['PHP_SELF'], _("Enter A &Payment"), "NewPayment=yes"); + + display_footer_exit(); +} + if (isset($_POST['_date__changed'])) { $Ajax->activate('_ex_rate'); } @@ -112,6 +150,73 @@ function handle_new_order($type) $_SESSION['pay_items']->tran_date = $_POST['date_']; } +function create_cart($type, $trans_no) +{ + global $Refs; + + if (isset($_SESSION['pay_items'])) + { + unset ($_SESSION['pay_items']); + } + + $_SESSION['pay_items'] = new items_cart($type); + $_SESSION['pay_items']->order_id = $trans_no; + + if ($trans_no) { + $result = get_gl_trans($type, $trans_no); + + if ($result) { + while ($row = db_fetch($result)) { + if ($row['amount'] == 0) continue; + if (is_bank_account($row['account'])) continue; + $date = $row['tran_date']; + $_SESSION['pay_items']->add_gl_item($row['account'], $row['dimension_id'], + $row['dimension2_id'], $row['amount'], $row['memo_']); + } + } + $_SESSION['pay_items']->memo_ = get_comments_string($type, $trans_no); + $_SESSION['pay_items']->tran_date = sql2date($date); + $_SESSION['pay_items']->reference = $Refs->get($type, $trans_no); + //////////////////////////////////////////// + // Check Ref Original ????? + $_POST['ref_original'] = $_SESSION['pay_items']->reference; // Store for comparison when updating + + $bank_trans = db_fetch(get_bank_trans($type, $trans_no)); + $_POST['bank_account'] = $bank_trans["bank_act"]; + $_POST['PayType'] = $bank_trans["person_type_id"]; + + if ($bank_trans["person_type_id"] == PT_CUSTOMER) //2 + { + $trans = get_customer_trans($trans_no, $type); + $_POST['person_id'] = $trans["debtor_no"]; + $_POST['PersonDetailID'] = $trans["branch_code"]; + } + elseif ($bank_trans["person_type_id"] == PT_SUPPLIER) //3 + { + $trans = get_supp_trans($trans_no, $type); + $_POST['person_id'] = $trans["supplier_id"]; + } + elseif ($bank_trans["person_type_id"] == PT_MISC) //0 + $_POST['person_id'] = $bank_trans["person_id"]; + elseif ($bank_trans["person_type_id"] == PT_QUICKENTRY) //4 + $_POST['person_id'] = $bank_trans["person_id"]; + else + $_POST['person_id'] = $bank_trans["person_id"]; + + } else { + $_SESSION['pay_items']->reference = $Refs->get_next(0); + $_SESSION['pay_items']->tran_date = new_doc_date(); + if (!is_date_in_fiscalyear($_SESSION['pay_items']->tran_date)) + $_SESSION['pay_items']->tran_date = end_fiscalyear(); + $_POST['ref_original'] = -1; + } + + $_POST['memo_'] = $_SESSION['pay_items']->memo_; + $_POST['ref'] = $_SESSION['pay_items']->reference; + $_POST['date_'] = $_SESSION['pay_items']->tran_date; + + //$_SESSION['pay_items'] = &$_SESSION['pay_items']; +} //----------------------------------------------------------------------------------------------- if (isset($_POST['Process'])) @@ -137,7 +242,7 @@ if (isset($_POST['Process'])) set_focus('ref'); $input_error = 1; } - elseif (!is_new_reference($_POST['ref'], $_SESSION['pay_items']->trans_type)) + elseif ($_POST['ref'] != $_SESSION['pay_items']->reference && !is_new_reference($_POST['ref'], $_SESSION['pay_items']->trans_type)) { display_error( _("The entered reference is already in use.")); set_focus('ref'); @@ -162,12 +267,26 @@ if (isset($_POST['Process'])) if (isset($_POST['Process'])) { - + begin_transaction(); + + $_SESSION['pay_items'] = &$_SESSION['pay_items']; + $new = $_SESSION['pay_items']->order_id == 0; + + if (!$new) + { + clear_bank_trans($_SESSION['pay_items']->trans_type, $_SESSION['pay_items']->order_id, true); + $trans = reinsert_bank_transaction( + $_SESSION['pay_items']->trans_type, $_SESSION['pay_items']->order_id, $_POST['bank_account'], + $_SESSION['pay_items'], $_POST['date_'], + $_POST['PayType'], $_POST['person_id'], get_post('PersonDetailID'), + $_POST['ref'], $_POST['memo_'], false); + } + else $trans = add_bank_transaction( $_SESSION['pay_items']->trans_type, $_POST['bank_account'], $_SESSION['pay_items'], $_POST['date_'], $_POST['PayType'], $_POST['person_id'], get_post('PersonDetailID'), - $_POST['ref'], $_POST['memo_']); + $_POST['ref'], $_POST['memo_'], false); $trans_type = $trans[0]; $trans_no = $trans[1]; @@ -175,9 +294,15 @@ if (isset($_POST['Process'])) $_SESSION['pay_items']->clear_items(); unset($_SESSION['pay_items']); - + + commit_transaction(); + + if ($new) meta_forward($_SERVER['PHP_SELF'], $trans_type==ST_BANKPAYMENT ? "AddedID=$trans_no" : "AddedDep=$trans_no"); + else + meta_forward($_SERVER['PHP_SELF'], $trans_type==ST_BANKPAYMENT ? + "UpdatedID=$trans_no" : "UpdatedDep=$trans_no"); } /*end of process credit note */ diff --git a/gl/includes/db/gl_db_bank_trans.inc b/gl/includes/db/gl_db_bank_trans.inc index 37c173c0..3face339 100644 --- a/gl/includes/db/gl_db_bank_trans.inc +++ b/gl/includes/db/gl_db_bank_trans.inc @@ -152,4 +152,38 @@ function void_bank_trans($type, $type_no, $nested=false) //---------------------------------------------------------------------------------- +//---------------------------------------------------------------------------------------- + +function clear_bank_trans($type, $type_no, $nested=false) +{ + global $Refs; + + if (!$nested) + begin_transaction(); + + $sql = "DELETE FROM ".TB_PREF."bank_trans + WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); + + $result = db_query($sql, "could not clear bank transactions for type=$type and trans_no=$type_no"); + + clear_gl_trans($type, $type_no, true); + + // in case it's a customer trans - probably better to check first + void_cust_allocations($type, $type_no); + clear_customer_trans($type, $type_no); + + // in case it's a supplier trans - probably better to check first + void_supp_allocations($type, $type_no); + clear_supp_trans($type, $type_no); + + clear_trans_tax_details($type, $type_no); + + //Delete the reference + $Refs->delete($type, $type_no); + + if (!$nested) + commit_transaction(); +} + + ?> \ No newline at end of file diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index 58825018..3506a5c8 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -248,9 +248,8 @@ function add_bank_transaction($trans_type, $from_account, $items, $date_, "Cannot insert a destination bank transaction"); if ($do_exchange_variance) { - if (add_exchange_variation($trans_no1, $date_, $is_bank_to, $gl_item->code_id, - $currency, $person_type_id, $person_id)) - $exchanged = true; + add_exchange_variation($trans_no1, $date_, $is_bank_to, $gl_item->code_id, + $currency, $person_type_id, $person_id); } } // store tax details if the gl account is a tax account @@ -276,6 +275,142 @@ function add_bank_transaction($trans_type, $from_account, $items, $date_, add_audit_trail(ST_JOURNAL, $trans_no1, $date_); } } + + add_comments($trans_type, $trans_no, $date_, $memo_); + + $Refs->save($trans_type, $trans_no, $ref); + add_audit_trail($trans_type, $trans_no, $date_); + + if ($use_transaction) + commit_transaction(); + + return array($trans_type, $trans_no); +} + +//---------------------------------------------------------------------------------------- + +//---------------------------------------------------------------------------------- +// Re-Insert bank payment or deposit to database using the transaction number passed. +// +// $from_account - bank account id +// $item - transaction cart (line item's amounts in bank account's currency) +// $person_type_id - defines type of $person_id identifiers +// $person_id - supplier/customer/other id +// $person_detail_id - customer branch id or not used +// +// returns an array of (inserted trans type, trans no) + +function reinsert_bank_transaction($trans_type, $trans_no, $from_account, $items, $date_, + $person_type_id, $person_id, $person_detail_id, $ref, $memo_, $use_transaction=true) +{ + global $Refs, $SysPrefs; + + // we can only handle type 1 (payment)and type 2 (deposit) + if ($trans_type != ST_BANKPAYMENT && $trans_type != ST_BANKDEPOSIT) + display_db_error("Invalid type ($trans_type) sent to add_bank_transaction"); + + $do_exchange_variance = false; + $exchanged = false; + if ($use_transaction) + begin_transaction(); + + $currency = get_bank_account_currency($from_account); + $bank_gl_account = get_bank_gl_account($from_account); + + // the gl items are already inversed/negated for type 2 (deposit) + $total_amount = $items->gl_items_total(); + + if ($person_type_id == PT_CUSTOMER) + { + // we need to add a customer transaction record + + // convert to customer currency + $cust_amount = exchange_from_to($total_amount, $currency, get_customer_currency($person_id), $date_); + // we need to negate it too + $cust_amount = -$cust_amount; + + $trans_no = reinsert_customer_trans($trans_type, $trans_no, $person_id, $person_detail_id, $date_, + $ref, $cust_amount); + + } + elseif ($person_type_id == PT_SUPPLIER) + { + // we need to add a supplier transaction record + // convert to supp currency + $supp_amount = exchange_from_to($total_amount, $currency, get_supplier_currency($person_id), $date_); + + // we need to negate it too + $supp_amount = -$supp_amount; + + $trans_no = reinsert_supp_trans($trans_type, $trans_no, $person_id, $date_, '', + $ref, "", $supp_amount, 0, 0); + + } + else + { + $do_exchange_variance = $SysPrefs->auto_currency_revaluation(); + if ($do_exchange_variance) + $trans_no1 = get_next_trans_no(ST_JOURNAL); + } + + // do the source account postings + + add_bank_trans($trans_type, $trans_no, $from_account, $ref, + $date_, -$total_amount, + $person_type_id, $person_id, + $currency, + "Cannot insert a source bank transaction"); + $total = 0; + foreach ($items->gl_items as $gl_item) + { + $is_bank_to = is_bank_account($gl_item->code_id); + + if ($trans_type == ST_BANKPAYMENT AND $is_bank_to) + { + // we don't allow payments to go to a bank account. use transfer for this ! + display_db_error("invalid payment entered. Cannot pay to another bank account", ""); + } + + // do the destination account postings + $total += add_gl_trans($trans_type, $trans_no, $date_, $gl_item->code_id, + $gl_item->dimension_id, $gl_item->dimension2_id, $gl_item->reference, + $gl_item->amount, $currency, $person_type_id, $person_id); + + if ($is_bank_to) + { + add_bank_trans($trans_type, $trans_no, $is_bank_to, $ref, + $date_, $gl_item->amount, + $person_type_id, $person_id, $currency, + "Cannot insert a destination bank transaction"); + if ($do_exchange_variance) + { + add_exchange_variation($trans_no1, $date_, $is_bank_to, $gl_item->code_id, + $currency, $person_type_id, $person_id); + } + } + // 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, + $ex_rate, $date_, $memo_); + } + // do the source account postings + add_gl_trans($trans_type, $trans_no, $date_, $bank_gl_account, 0, 0, $memo_, + -$total, null, $person_type_id, $person_id); + + if ($do_exchange_variance) + { + if ($exchanged || add_exchange_variation($trans_no1, $date_, $from_account, $bank_gl_account, + $currency, $person_type_id, $person_id)) + { + $ref1 = $Refs->get_next(ST_JOURNAL); + $Refs->save(ST_JOURNAL, $trans_no1, $ref1); + add_audit_trail(ST_JOURNAL, $trans_no1, $date_); + } + } + add_comments($trans_type, $trans_no, $date_, $memo_); $Refs->save($trans_type, $trans_no, $ref); diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 51a83e95..e6416967 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -401,6 +401,17 @@ function void_trans_tax_details($type, $type_no) db_query($sql, "The transaction tax details could not be voided"); } +//---------------------------------------------------------------------------------------- + +function clear_trans_tax_details($type, $type_no) +{ + $sql = "DELETE FROM ".TB_PREF."trans_tax_details + WHERE trans_no=".db_escape($type_no) + ." AND trans_type=".db_escape($type); + + db_query($sql, "The transaction tax details could not be cleared"); +} + function get_tax_summary($from, $to) { $fromdate = date2sql($from); @@ -561,6 +572,22 @@ function void_gl_trans($type, $trans_id, $nested=false) commit_transaction(); } +//-------------------------------------------------------------------------------------------------- + +function clear_gl_trans($type, $trans_id, $nested=false) +{ + if (!$nested) + begin_transaction(); + + $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE type=".db_escape($type) + ." AND type_no=".db_escape($trans_id); + + db_query($sql, "could not clear gl transactions for type=$type and trans_no=$trans_id"); + + if (!$nested) + commit_transaction(); +} + //---------------------------------------------------------------------------------------- function void_journal_trans($type, $type_no, $use_transaction=true) diff --git a/gl/inquiry/journal_inquiry.php b/gl/inquiry/journal_inquiry.php index 54dfa1c0..ed6f9be7 100644 --- a/gl/inquiry/journal_inquiry.php +++ b/gl/inquiry/journal_inquiry.php @@ -80,9 +80,9 @@ function gl_link($row) } $editors = array( - 0 => "/gl/gl_journal.php?ModifyGL=Yes&trans_no=%d&trans_type=%d", -// 1=> Bank Payment, -// 2=> Bank Deposit, + ST_JOURNAL => "/gl/gl_journal.php?ModifyGL=Yes&trans_no=%d&trans_type=%d", + ST_BANKPAYMENT => "/gl/gl_bank.php?ModifyPayment=Yes&trans_no=%d&trans_type=%d", + ST_BANKDEPOSIT => "/gl/gl_bank.php?ModifyDeposit=Yes&trans_no=%d&trans_type=%d", // 4=> Funds Transfer, ST_SALESINVOICE => "/sales/customer_invoice.php?ModifyInvoice=%d", // 11=> diff --git a/purchasing/includes/db/supp_trans_db.inc b/purchasing/includes/db/supp_trans_db.inc index 30294e69..a2265bb9 100644 --- a/purchasing/includes/db/supp_trans_db.inc +++ b/purchasing/includes/db/supp_trans_db.inc @@ -46,6 +46,41 @@ function add_supp_trans($type, $supplier_id, $date_, $due_date, $reference, $sup //------------------------------------------------------------------------------------------------------------- +function reinsert_supp_trans($type, $trans_no, $supplier_id, $date_, $due_date, $reference, $supp_reference, + $amount, $amount_tax, $discount, $err_msg="", $rate=0) +{ + if ($trans_no == '') + display_db_error('Invalid call to function reinsert_supp_trans'); + + $date = date2sql($date_); + if ($due_date == "") + $due_date = "0000-00-00"; + else + $due_date = date2sql($due_date); + + $curr = get_supplier_currency($supplier_id); + + if ($rate == 0) + $rate = get_exchange_rate_from_home_currency($curr, $date_); + + + $sql = "INSERT INTO ".TB_PREF."supp_trans (trans_no, type, supplier_id, tran_date, due_date, + reference, supp_reference, ov_amount, ov_gst, rate, ov_discount) "; + $sql .= "VALUES (".db_escape($trans_no).", ".db_escape($type) + .", ".db_escape($supplier_id).", '$date', '$due_date', + ".db_escape($reference).", ".db_escape($supp_reference).", ".db_escape($amount) + .", ".db_escape($amount_tax).", ".db_escape($rate).", ".db_escape($discount).")"; + + if ($err_msg == "") + $err_msg = "Cannot insert a supplier transaction record"; + + db_query($sql, $err_msg); + add_audit_trail($type, $trans_no, $date_); + + return $trans_no; +} +//------------------------------------------------------------------------------------------------------------- + function get_supp_trans($trans_no, $trans_type=-1) { $sql = "SELECT ".TB_PREF."supp_trans.*, (".TB_PREF."supp_trans.ov_amount+".TB_PREF."supp_trans.ov_gst+".TB_PREF."supp_trans.ov_discount) AS Total, @@ -126,6 +161,15 @@ function void_supp_trans($type, $type_no) //---------------------------------------------------------------------------------------- +function clear_supp_trans($type, $type_no) +{ + $sql = "DELETE FROM ".TB_PREF."supp_trans + WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); + + db_query($sql, "could not clear supp transactions for type=$type and trans_no=$type_no"); +} +//---------------------------------------------------------------------------------------- + function post_void_supp_trans($type, $type_no) { if ($type == ST_SUPPAYMENT) diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index 770c73ed..ab560ace 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -145,6 +145,53 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, //---------------------------------------------------------------------------------------- +function reinsert_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, + $date_, $reference, $Total, $discount=0, $Tax=0, $Freight=0, $FreightTax=0, + $sales_type=0, $order_no=0, $trans_link=0, $ship_via=0, $due_date="", + $AllocAmt=0, $rate=0, $dimension_id=0, $dimension2_id=0) +{ + if ($trans_no == '') + display_db_error('Invalid call to function reinsert_customer_trans'); + + $curr = get_customer_currency($debtor_no); + if ($rate == 0) + $rate = get_exchange_rate_from_home_currency($curr, $date_); + + $SQLDate = date2sql($date_); + if ($due_date == "") + $SQLDueDate = "0000-00-00"; + else + $SQLDueDate = date2sql($due_date); + + if ($trans_type == ST_BANKPAYMENT) + $Total = -$Total; + + $sql = "INSERT INTO ".TB_PREF."debtor_trans ( + trans_no, type, + debtor_no, branch_code, + tran_date, due_date, + reference, tpe, + order_, ov_amount, ov_discount, + ov_gst, ov_freight, ov_freight_tax, + rate, ship_via, alloc, trans_link, + dimension_id, dimension2_id + ) VALUES ($trans_no, ".db_escape($trans_type).", + ".db_escape($debtor_no).", ".db_escape($BranchNo).", + '$SQLDate', '$SQLDueDate', ".db_escape($reference).", + ".db_escape($sales_type).", ".db_escape($order_no).", $Total, ".db_escape($discount).", $Tax, + ".db_escape($Freight).", + $FreightTax, $rate, ".db_escape($ship_via).", $AllocAmt, ".db_escape($trans_link).", + ".db_escape($dimension_id).", ".db_escape($dimension2_id).")"; + + db_query($sql, "The debtor transaction record could not be inserted"); + + add_audit_trail($trans_type, $trans_no, $date_, _("Updated.")); + + return $trans_no; +} + +//---------------------------------------------------------------------------------------- + function get_customer_trans($trans_id, $trans_type) { $sql = "SELECT trans.*," @@ -282,6 +329,15 @@ function void_customer_trans($type, $type_no) //---------------------------------------------------------------------------------------- +function clear_customer_trans($type, $type_no) +{ + // Delete + $sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); + + db_query($sql, "could not clear debtor transactions for type=$type and trans_no=$type_no"); +} +//---------------------------------------------------------------------------------------- + function post_void_customer_trans($type, $type_no) { switch ($type) { -- 2.30.2