Topic: GL PAYMENT MODIFICATIONS (WHERE IS THE MISTAKE IN THIS CODE?)
this is what I tried to do to modify my GL Payment transactions
1. I added two extra columns to bank_trans table to save cheques and voucher noALTER TABLE `bank_trans` ADD `check_no` VARCHAR(60) NOT NULL AFTER `ref`, ADD `source_ref` VARCHAR(60) NOT NULL AFTER `check_no`, ADD UNIQUE (`check_no`), ADD UNIQUE (`source_ref`);2. gl_bank.php file I added the following codes :
function create_cart($type, $trans_no) { global $Refs; if (isset($_SESSION['pay_items'])) { unset ($_SESSION['pay_items']); } $cart = new items_cart($type); $cart->order_id = $trans_no; if ($trans_no) { $bank_trans = db_fetch(get_bank_trans($type, $trans_no)); $_POST['bank_account'] = $bank_trans["bank_act"]; $_POST['PayType'] = $bank_trans["person_type_id"]; $cart->reference = $bank_trans["ref"]; $_POST['check_no'] = $bank_trans["check_no"]; // New field for table check no if ($bank_trans["person_type_id"] == PT_CUSTOMER) { $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) { $trans = get_supp_trans($trans_no, $type); $_POST['person_id'] = $trans["supplier_id"]; } elseif ($bank_trans["person_type_id"] == PT_MISC) $_POST['person_id'] = $bank_trans["person_id"]; elseif ($bank_trans["person_type_id"] == PT_QUICKENTRY) $_POST['person_id'] = $bank_trans["person_id"]; else $_POST['person_id'] = $bank_trans["person_id"]; $cart->memo_ = get_comments_string($type, $trans_no); $cart->tran_date = sql2date($bank_trans['trans_date']); $cart->original_amount = $bank_trans['amount']; $result = get_gl_trans($type, $trans_no); if ($result) { while ($row = db_fetch($result)) { if (is_bank_account($row['account'])) { // date exchange rate is currenly not stored in bank transaction, // so we have to restore it from original gl amounts $ex_rate = $bank_trans['amount']/$row['amount']; } else { $cart->add_gl_item( $row['account'], $row['dimension_id'], $row['dimension2_id'], $row['amount'], $row['memo_']); } } } // apply exchange rate foreach($cart->gl_items as $line_no => $line) $cart->gl_items[$line_no]->amount *= $ex_rate; } else { $cart->reference = $Refs->get_next($cart->trans_type, null, $cart->tran_date); $cart->tran_date = new_doc_date(); if (!is_date_in_fiscalyear($cart->tran_date)) $cart->tran_date = end_fiscalyear(); } $_POST['memo_'] = $cart->memo_; $_POST['ref'] = $cart->reference; $_POST['check_no'] = $cart->check_no; // Check no $_POST['source_ref'] = $cart->source_ref; // voucher no $_POST['date_'] = $cart->tran_date; $_SESSION['pay_items'] = &$cart; }step 3 : gl_db_bank_trans.inc:
//----------------------------------------------------------------------------------------------- // add a bank transaction // $amount is in $currency // $date_ is display date (non-sql) function add_bank_trans($type, $trans_no, $bank_act, $ref, $check_no, $source_ref,$date_, $amount, $person_type_id, $person_id, $currency="", $err_msg="", $rate=0) { $sqlDate = date2sql($date_); // convert $amount to the bank's currency if ($currency != "") { $bank_account_currency = get_bank_account_currency($bank_act); if ($rate == 0) $to_bank_currency = get_exchange_rate_from_to($currency, $bank_account_currency, $date_); else $to_bank_currency = 1 / $rate; $amount_bank = ($amount / $to_bank_currency); } else $amount_bank = $amount; $amount_bank = round2($amount_bank, user_price_dec()); $sql = "INSERT INTO ".TB_PREF."bank_trans (type, trans_no, bank_act, ref,check_no, source_ref, trans_date, amount, person_type_id, person_id) "; //$sql .= "VALUES ($type, $trans_no, '$bank_act', ".db_escape($ref).", $check_no, $source_ref, '$sqlDate', // ".db_escape($amount_bank).", ".db_escape($person_type_id) // .", ". db_escape($person_id).")"; $sql .= "VALUES ($type, $trans_no, '$bank_act', ".db_escape($ref).", ".db_escape($check_no).",".db_escape($source_ref).",$sqlDate', ".db_escape($amount_bank).", ".db_escape($person_type_id) .", ". db_escape($person_id).")"; if ($err_msg == "") $err_msg = "The bank transaction could not be inserted"; db_query($sql, $err_msg); }STEP 4: gl_db_banking.inc:
//---------------------------------------------------------------------------------------- function write_bank_transaction($trans_type, $trans_no, $from_account, $items, $date_, $person_type_id, $person_id, $person_detail_id, $ref, $check_no, $source_ref , $memo_ , $use_transaction=true, $settled_amount=null) { 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(); $args = func_get_args(); if (count($args) < 13) $args[] = true; $args = (object)array_combine(array('trans_type', 'trans_no', 'from_account', 'items', 'date_', 'person_type_id', 'person_id', 'person_detail_id', 'ref','check_no','source_ref', 'memo_', 'use_transaction', 'settled_amount'), $args); hook_db_prewrite($args, $trans_type); $aid = 0; if ($trans_no) { $old_trans = $trans_no; $Refs->restore_last($trans_type, $trans_no); $aid = has_attachment($trans_type, $trans_no); } else $old_trans = false; $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 if (!isset($settled_amount)) // leaved for backward/ext compatibility $cust_amount = exchange_from_to(abs($total_amount), $currency, get_customer_currency($person_id), $date_); else $cust_amount = $settled_amount; if ($trans_type == ST_BANKPAYMENT) $cust_amount = -$cust_amount; $trans_no = write_customer_trans($trans_type, 0, $person_id, $person_detail_id, $date_, $ref,$check_no,$source_ref, $cust_amount); if ($old_trans) move_trans_attachments($trans_type, $old_trans, $trans_no); } elseif ($person_type_id == PT_SUPPLIER) { // we need to add a supplier transaction record // convert to supp currency if (!isset($settled_amount)) // leaved for for backward/ext compatibility $supp_amount = exchange_from_to(abs($total_amount), $currency, get_supplier_currency($person_id), $date_); else $supp_amount = $settled_amount; if ($trans_type == ST_BANKPAYMENT) $supp_amount = -$supp_amount; $trans_no = write_supp_trans($trans_type, 0, $person_id, $date_, '', $ref, "", $supp_amount, 0, 0); if ($old_trans) move_trans_attachments($trans_type, $old_trans, $trans_no); } else { $trans_no = get_next_trans_no($trans_type); $do_exchange_variance = $SysPrefs->auto_currency_revaluation(); if ($do_exchange_variance) $trans_no1 = get_next_trans_no(ST_JOURNAL); } if ($aid != 0) { $row = get_attachment($aid); update_attachment($aid, $row['type_no'], $trans_no, $row['description'], $row['filename'], $row['unique_name'], $row['filesize'], $row['filetype']); } // do the source account postings add_bank_trans($trans_type, $trans_no, $from_account, $ref, $check_no, $source_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, $check_no,$source_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, null, $date_); $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, $check_no); add_audit_trail($trans_type, $trans_no, $date_); // old transaction can be voided only after new transaction is entered, // otherwise the operation could fail for cash accounts due to temporary negative balance if ($old_trans) { $msg = void_transaction($trans_type, $old_trans, Today(), _("Document reentered.")); if ($msg) { display_error($msg); return false; } } $args->trans_no = $trans_no; hook_db_postwrite($args, $trans_type); if ($use_transaction) commit_transaction(); return array($trans_type, $trans_no); }STEP 5 : gl_bank_ui.inc:
function display_bank_header(&$order) { global $Ajax; $payment = $order->trans_type == ST_BANKPAYMENT; $customer_error = false; div_start('pmt_header'); start_outer_table(TABLESTYLE2, "width='90%'"); // outer table table_section(1); date_row(_("Date:"), 'date_', '', true, 0, 0, 0, null, true); ref_row(_("Reference:"), 'ref', '', $order->reference, false, $order->trans_type, get_post('date_')); // text_row(_("check No:"), 'check_no', $_POST['check_no'], 16, 40); text_row(_("Check No:"), 'check_no', $_POST['check_no'], 16, 40); // Newly Added table_section(2, "33%"); if (!isset($_POST['PayType'])) { if (isset($_GET['PayType'])) $_POST['PayType'] = $_GET['PayType']; else $_POST['PayType'] = ""; } if (!isset($_POST['person_id'])) { if (isset($_GET['PayPerson'])) $_POST['person_id'] = $_GET['PayPerson']; else $_POST['person_id'] = ""; } if (isset($_POST['_PayType_update'])) { $_POST['person_id'] = ''; $Ajax->activate('pmt_header'); $Ajax->activate('code_id'); $Ajax->activate('pagehelp'); $Ajax->activate('editors'); $Ajax->activate('footer'); } payment_person_types_list_row( $payment ? _("Pay To:"):_("From:"), 'PayType', $_POST['PayType'], true); switch ($_POST['PayType']) { case PT_MISC : text_row_ex($payment ?_("To the Order of:"):_("Name:"), 'person_id', 20, 50); text_row(_("Voucher No:"), 'source_ref', null, 20, 50); // Newly Added break; case PT_SUPPLIER : supplier_list_row(_("Supplier:"), 'person_id', null, false, true, false, true); text_row(_("Voucher No:"), 'source_ref', null, 20, 50); // Newly added break; case PT_CUSTOMER : customer_list_row(_("Customer:"), 'person_id', null, false, true, false, true); text_row(_("Voucher No:"), 'source_ref', null, 20, 50); //New Line if (db_customer_has_branches($_POST['person_id'])) { customer_branches_list_row(_("Branch:"), $_POST['person_id'], 'PersonDetailID', null, false, true, true, true); } else { $_POST['PersonDetailID'] = ANY_NUMERIC; hidden('PersonDetailID'); } $trans = get_customer_habit($_POST['person_id']); // take care of customers on hold if ($trans['dissallow_invoices'] != 0) { if ($payment) { $customer_error = true; display_error(_("This customer account is on hold.")); } else display_warning(_("This customer account is on hold.")); } break; case PT_QUICKENTRY : quick_entries_list_row(_("Type").":", 'person_id', null, ($payment ? QE_PAYMENT : QE_DEPOSIT), true); $qid = get_quick_entry(get_post('person_id')); if (list_updated('person_id')) { unset($_POST['totamount']); // enable default $Ajax->activate('footer'); $Ajax->activate('totamount'); } amount_row($qid['base_desc'].":", 'totamount', price_format($qid['base_amount']), null, " ".submit('go', _("Go"), false, false, true)); text_row(_("Voucher No:"), 'source_ref', null, 20, 50); // New Line break; } table_section(3, "33%"); if (!$order->order_id && !get_post('bank_account')) { if ($_POST['PayType'] == PT_CUSTOMER) $_POST['bank_account'] = get_default_customer_bank_account($_POST['person_id']); elseif ($_POST['PayType'] == PT_SUPPLIER) $_POST['bank_account'] = get_default_supplier_bank_account($_POST['person_id']); else unset($_POST['bank_account']); } bank_accounts_list_row( $payment ? _("From:") : _("Into:"), 'bank_account', null, true); if ($payment) bank_balance_row($_POST['bank_account']); $bank_currency = get_bank_account_currency($_POST['bank_account']); exchange_rate_display(get_company_currency(), $bank_currency, $_POST['date_']); end_outer_table(1); // outer table div_end(); if ($customer_error) { end_form(); end_page(); exit; } } //---------------------------------------------------------------------------------