Search options (Page 2 of 5)

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 no

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`);  

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;
    }
}
//---------------------------------------------------------------------------------