<?xml version="1.0" encoding="utf-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
	<channel>
		<title><![CDATA[FrontAccounting forum — GL PAYMENT MODIFICATIONS  (WHERE IS THE MISTAKE IN THIS CODE?)]]></title>
		<link>https://frontaccounting.com/punbb/viewtopic.php?id=9159</link>
		<atom:link href="https://frontaccounting.com/punbb/extern.php?action=feed&amp;tid=9159&amp;type=rss" rel="self" type="application/rss+xml" />
		<description><![CDATA[The most recent posts in GL PAYMENT MODIFICATIONS  (WHERE IS THE MISTAKE IN THIS CODE?).]]></description>
		<lastBuildDate>Sun, 17 Jan 2021 08:52:52 +0000</lastBuildDate>
		<generator>PunBB</generator>
		<item>
			<title><![CDATA[GL PAYMENT MODIFICATIONS  (WHERE IS THE MISTAKE IN THIS CODE?)]]></title>
			<link>https://frontaccounting.com/punbb/viewtopic.php?pid=38950#p38950</link>
			<description><![CDATA[<div class="quotebox"><blockquote><p>this is what I tried to do to modify my GL Payment transactions<br />1. I added two extra columns to <strong>bank_trans</strong> table to save cheques and voucher no</p><div class="codebox"><pre><code>ALTER 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`);  </code></pre></div><p>2. <strong>gl_bank.php</strong> file I added the following codes :<br /></p><div class="codebox"><pre><code>function create_cart($type, $trans_no)
{
    global $Refs;

    if (isset($_SESSION[&#039;pay_items&#039;]))
    {
        unset ($_SESSION[&#039;pay_items&#039;]);
    }

    $cart = new items_cart($type);
        $cart-&gt;order_id = $trans_no;

    if ($trans_no) {

        $bank_trans = db_fetch(get_bank_trans($type, $trans_no));
        $_POST[&#039;bank_account&#039;] = $bank_trans[&quot;bank_act&quot;];
        $_POST[&#039;PayType&#039;]      = $bank_trans[&quot;person_type_id&quot;];
        $cart-&gt;reference       = $bank_trans[&quot;ref&quot;];
        $_POST[&#039;check_no&#039;]     = $bank_trans[&quot;check_no&quot;];  // New field for table check no

        if ($bank_trans[&quot;person_type_id&quot;] == PT_CUSTOMER)
        {
            $trans = get_customer_trans($trans_no, $type);    
            $_POST[&#039;person_id&#039;] = $trans[&quot;debtor_no&quot;];
            $_POST[&#039;PersonDetailID&#039;] = $trans[&quot;branch_code&quot;];
        }
        elseif ($bank_trans[&quot;person_type_id&quot;] == PT_SUPPLIER)
        {
            $trans = get_supp_trans($trans_no, $type);
            $_POST[&#039;person_id&#039;] = $trans[&quot;supplier_id&quot;];
        }
        elseif ($bank_trans[&quot;person_type_id&quot;] == PT_MISC)
            $_POST[&#039;person_id&#039;] = $bank_trans[&quot;person_id&quot;];
        elseif ($bank_trans[&quot;person_type_id&quot;] == PT_QUICKENTRY)
            $_POST[&#039;person_id&#039;] = $bank_trans[&quot;person_id&quot;];
        else 
            $_POST[&#039;person_id&#039;] = $bank_trans[&quot;person_id&quot;];

        $cart-&gt;memo_     = get_comments_string($type, $trans_no);
        $cart-&gt;tran_date = sql2date($bank_trans[&#039;trans_date&#039;]);

        $cart-&gt;original_amount = $bank_trans[&#039;amount&#039;];
        $result = get_gl_trans($type, $trans_no);
        if ($result) {
            while ($row = db_fetch($result)) {
                if (is_bank_account($row[&#039;account&#039;])) {
                    // date exchange rate is currenly not stored in bank transaction,
                    // so we have to restore it from original gl amounts
                    $ex_rate = $bank_trans[&#039;amount&#039;]/$row[&#039;amount&#039;];
                } else {
                    $cart-&gt;add_gl_item( $row[&#039;account&#039;], $row[&#039;dimension_id&#039;],
                        $row[&#039;dimension2_id&#039;], $row[&#039;amount&#039;], $row[&#039;memo_&#039;]);
                }
            }
        }

        // apply exchange rate
        foreach($cart-&gt;gl_items as $line_no =&gt; $line)
            $cart-&gt;gl_items[$line_no]-&gt;amount *= $ex_rate;

    } else {
        $cart-&gt;reference = $Refs-&gt;get_next($cart-&gt;trans_type, null, $cart-&gt;tran_date);
        $cart-&gt;tran_date = new_doc_date();
        if (!is_date_in_fiscalyear($cart-&gt;tran_date))
            $cart-&gt;tran_date = end_fiscalyear();
    }

    $_POST[&#039;memo_&#039;]      = $cart-&gt;memo_;
    $_POST[&#039;ref&#039;]        = $cart-&gt;reference;
    $_POST[&#039;check_no&#039;]   = $cart-&gt;check_no; // Check no
    $_POST[&#039;source_ref&#039;] = $cart-&gt;source_ref;  // voucher no
    $_POST[&#039;date_&#039;]      = $cart-&gt;tran_date;

    $_SESSION[&#039;pay_items&#039;] = &amp;$cart;
}</code></pre></div><p>step 3 : <strong>gl_db_bank_trans.inc</strong>:</p><div class="codebox"><pre><code>//-----------------------------------------------------------------------------------------------
// 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=&quot;&quot;, $err_msg=&quot;&quot;, $rate=0)
{
    $sqlDate = date2sql($date_);

    // convert $amount to the bank&#039;s currency
    if ($currency != &quot;&quot;)
    {
        $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 = &quot;INSERT INTO &quot;.TB_PREF.&quot;bank_trans (type, trans_no, bank_act, ref,check_no, source_ref,
        trans_date, amount, person_type_id, person_id) &quot;;

    //$sql .= &quot;VALUES ($type, $trans_no, &#039;$bank_act&#039;, &quot;.db_escape($ref).&quot;, $check_no, $source_ref, &#039;$sqlDate&#039;,
    //    &quot;.db_escape($amount_bank).&quot;, &quot;.db_escape($person_type_id)
//        .&quot;, &quot;. db_escape($person_id).&quot;)&quot;;

    $sql .= &quot;VALUES ($type, $trans_no, &#039;$bank_act&#039;, &quot;.db_escape($ref).&quot;, &quot;.db_escape($check_no).&quot;,&quot;.db_escape($source_ref).&quot;,$sqlDate&#039;,
        &quot;.db_escape($amount_bank).&quot;, &quot;.db_escape($person_type_id)
        .&quot;, &quot;. db_escape($person_id).&quot;)&quot;;


    if ($err_msg == &quot;&quot;)
        $err_msg = &quot;The bank transaction could not be inserted&quot;;

    db_query($sql, $err_msg);
}</code></pre></div><p>STEP 4:&nbsp; <strong>gl_db_banking.inc</strong>:<br /></p><div class="codebox"><pre><code>//----------------------------------------------------------------------------------------

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 &amp;&amp; $trans_type != ST_BANKDEPOSIT)
        display_db_error(&quot;Invalid type ($trans_type) sent to add_bank_transaction&quot;);

    $do_exchange_variance = false;
    $exchanged = false;
    if ($use_transaction)
        begin_transaction();

    $args = func_get_args(); if (count($args) &lt; 13) $args[] = true;
    $args = (object)array_combine(array(&#039;trans_type&#039;, &#039;trans_no&#039;, &#039;from_account&#039;, &#039;items&#039;, &#039;date_&#039;,
        &#039;person_type_id&#039;, &#039;person_id&#039;, &#039;person_detail_id&#039;, &#039;ref&#039;,&#039;check_no&#039;,&#039;source_ref&#039;, &#039;memo_&#039;, &#039;use_transaction&#039;, &#039;settled_amount&#039;),
        $args);
    hook_db_prewrite($args, $trans_type);

    $aid = 0;
    if ($trans_no) {
        $old_trans = $trans_no;
        $Refs-&gt;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-&gt;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_, &#039;&#039;,
            $ref, &quot;&quot;, $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-&gt;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[&#039;type_no&#039;], $trans_no, $row[&#039;description&#039;],
            $row[&#039;filename&#039;], $row[&#039;unique_name&#039;], $row[&#039;filesize&#039;], $row[&#039;filetype&#039;]);
    }
    // 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,
        &quot;Cannot insert a source bank transaction&quot;);
    $total = 0;
    foreach ($items-&gt;gl_items as $gl_item)
    {
        $is_bank_to = is_bank_account($gl_item-&gt;code_id);

        if ($trans_type == ST_BANKPAYMENT AND $is_bank_to)
        {
            // we don&#039;t allow payments to go to a bank account. use transfer for this !
            display_db_error(&quot;invalid payment entered. Cannot pay to another bank account&quot;, &quot;&quot;);
        }

        // do the destination account postings
        $total += add_gl_trans($trans_type, $trans_no, $date_, $gl_item-&gt;code_id,
            $gl_item-&gt;dimension_id, $gl_item-&gt;dimension2_id, $gl_item-&gt;reference,
            $gl_item-&gt;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-&gt;amount,
                $person_type_id, $person_id, $currency,
                &quot;Cannot insert a destination bank transaction&quot;);
            if ($do_exchange_variance)
            {
                add_exchange_variation($trans_no1, $date_, $is_bank_to, $gl_item-&gt;code_id, 
                    $currency, $person_type_id, $person_id);
            }
        }
        // store tax details if the gl account is a tax account

        $amount = $gl_item-&gt;amount;
        $ex_rate = get_exchange_rate_from_home_currency($currency, $date_);

        add_gl_tax_details($gl_item-&gt;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-&gt;get_next(ST_JOURNAL, null, $date_);
            $Refs-&gt;save(ST_JOURNAL, $trans_no1, $ref1);
            add_audit_trail(ST_JOURNAL, $trans_no1, $date_);
        }
    }

    add_comments($trans_type, $trans_no, $date_, $memo_);

    $Refs-&gt;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(), _(&quot;Document reentered.&quot;));
        if ($msg)
        {
            display_error($msg);
            return false;
        }
    }


    $args-&gt;trans_no = $trans_no;
    hook_db_postwrite($args, $trans_type);
    if ($use_transaction)
        commit_transaction();

    return array($trans_type, $trans_no);
}</code></pre></div><p>STEP 5 :<strong> gl_bank_ui.inc</strong>:<br /></p><div class="codebox"><pre><code>function display_bank_header(&amp;$order)
{
    global $Ajax;
    $payment = $order-&gt;trans_type == ST_BANKPAYMENT;

    $customer_error = false;
    div_start(&#039;pmt_header&#039;);

    start_outer_table(TABLESTYLE2, &quot;width=&#039;90%&#039;&quot;); // outer table

    table_section(1);
    
         date_row(_(&quot;Date:&quot;), &#039;date_&#039;, &#039;&#039;, true, 0, 0, 0, null, true);

    ref_row(_(&quot;Reference:&quot;), &#039;ref&#039;, &#039;&#039;, $order-&gt;reference, false, $order-&gt;trans_type, get_post(&#039;date_&#039;)); 
   // text_row(_(&quot;check No:&quot;), &#039;check_no&#039;, $_POST[&#039;check_no&#039;], 16, 40);
    text_row(_(&quot;Check No:&quot;),  &#039;check_no&#039;, $_POST[&#039;check_no&#039;], 16, 40); // Newly Added
   
    table_section(2, &quot;33%&quot;);

    if (!isset($_POST[&#039;PayType&#039;]))
    {
        if (isset($_GET[&#039;PayType&#039;]))
            $_POST[&#039;PayType&#039;] = $_GET[&#039;PayType&#039;];
        else
            $_POST[&#039;PayType&#039;] = &quot;&quot;;
    }
    if (!isset($_POST[&#039;person_id&#039;]))
    {
        if (isset($_GET[&#039;PayPerson&#039;]))
            $_POST[&#039;person_id&#039;] = $_GET[&#039;PayPerson&#039;];
        else
            $_POST[&#039;person_id&#039;] = &quot;&quot;;
    }
    if (isset($_POST[&#039;_PayType_update&#039;])) {
        $_POST[&#039;person_id&#039;] = &#039;&#039;;
        $Ajax-&gt;activate(&#039;pmt_header&#039;);
        $Ajax-&gt;activate(&#039;code_id&#039;);
        $Ajax-&gt;activate(&#039;pagehelp&#039;);
        $Ajax-&gt;activate(&#039;editors&#039;);
        $Ajax-&gt;activate(&#039;footer&#039;);
    }
    payment_person_types_list_row( $payment ? _(&quot;Pay To:&quot;):_(&quot;From:&quot;),
         &#039;PayType&#039;, $_POST[&#039;PayType&#039;], true);
    switch ($_POST[&#039;PayType&#039;])
    {
        case PT_MISC :
            text_row_ex($payment ?_(&quot;To the Order of:&quot;):_(&quot;Name:&quot;),
                 &#039;person_id&#039;, 20, 50);
           text_row(_(&quot;Voucher No:&quot;), &#039;source_ref&#039;, null, 20, 50);    // Newly Added
            break;
        case PT_SUPPLIER :
            supplier_list_row(_(&quot;Supplier:&quot;), &#039;person_id&#039;, null, false, true, false, true);
            text_row(_(&quot;Voucher No:&quot;), &#039;source_ref&#039;, null, 20, 50);     // Newly added
            break;
        case PT_CUSTOMER :
            customer_list_row(_(&quot;Customer:&quot;), &#039;person_id&#039;, null, false, true, false, true);
            text_row(_(&quot;Voucher No:&quot;), &#039;source_ref&#039;, null, 20, 50);    //New Line

            if (db_customer_has_branches($_POST[&#039;person_id&#039;]))
            {
                customer_branches_list_row(_(&quot;Branch:&quot;), $_POST[&#039;person_id&#039;], 
                    &#039;PersonDetailID&#039;, null, false, true, true, true);
            }
            else
            {
                $_POST[&#039;PersonDetailID&#039;] = ANY_NUMERIC;
                hidden(&#039;PersonDetailID&#039;);
            }
            $trans = get_customer_habit($_POST[&#039;person_id&#039;]); // take care of customers on hold
            if ($trans[&#039;dissallow_invoices&#039;] != 0)
            {
                if ($payment)
                {
                    $customer_error = true;
                    display_error(_(&quot;This customer account is on hold.&quot;));
                }
                else
                    display_warning(_(&quot;This customer account is on hold.&quot;));
            }
            break;

        case PT_QUICKENTRY :
            quick_entries_list_row(_(&quot;Type&quot;).&quot;:&quot;, &#039;person_id&#039;, null, ($payment ? QE_PAYMENT : QE_DEPOSIT), true);
            $qid = get_quick_entry(get_post(&#039;person_id&#039;));

            if (list_updated(&#039;person_id&#039;)) {
                unset($_POST[&#039;totamount&#039;]); // enable default
                $Ajax-&gt;activate(&#039;footer&#039;);
                $Ajax-&gt;activate(&#039;totamount&#039;);
            }
            amount_row($qid[&#039;base_desc&#039;].&quot;:&quot;, &#039;totamount&#039;, price_format($qid[&#039;base_amount&#039;]),
                 null, &quot;&amp;nbsp;&amp;nbsp;&quot;.submit(&#039;go&#039;, _(&quot;Go&quot;), false, false, true));
            text_row(_(&quot;Voucher No:&quot;), &#039;source_ref&#039;, null, 20, 50);    // New Line
            break;

    }

    table_section(3, &quot;33%&quot;);

    if (!$order-&gt;order_id &amp;&amp; !get_post(&#039;bank_account&#039;))
    {
        if ($_POST[&#039;PayType&#039;] == PT_CUSTOMER)
            $_POST[&#039;bank_account&#039;] = get_default_customer_bank_account($_POST[&#039;person_id&#039;]);
        elseif ($_POST[&#039;PayType&#039;] == PT_SUPPLIER)    
            $_POST[&#039;bank_account&#039;] = get_default_supplier_bank_account($_POST[&#039;person_id&#039;]);
        else
            unset($_POST[&#039;bank_account&#039;]);
    }        
    
    bank_accounts_list_row( $payment ? _(&quot;From:&quot;) : _(&quot;Into:&quot;), &#039;bank_account&#039;, null, true);
    if ($payment)
        bank_balance_row($_POST[&#039;bank_account&#039;]);

    $bank_currency = get_bank_account_currency($_POST[&#039;bank_account&#039;]);

    exchange_rate_display(get_company_currency(), $bank_currency, $_POST[&#039;date_&#039;]);

    end_outer_table(1); // outer table

    div_end();
    if ($customer_error)
    {
        end_form();
        end_page();
        exit;
    }
}
//---------------------------------------------------------------------------------</code></pre></div></blockquote></div>]]></description>
			<author><![CDATA[null@example.com (cedricktshiyoyo)]]></author>
			<pubDate>Sun, 17 Jan 2021 08:52:52 +0000</pubDate>
			<guid>https://frontaccounting.com/punbb/viewtopic.php?pid=38950#p38950</guid>
		</item>
	</channel>
</rss>
