Topic: Braathwaate quickreport_inquiry

Missing file include at line 15 of quickreport.php:

include_once($path_to_root . "/includes/");

-- Fixed

Re: Braathwaate quickreport_inquiry

@braathwate: Your report uses your custom functions in and files that have been appended to your quickreport_inquiry.php file for testing:

function scroll_down($div)
    global $Ajax;

    $js = "
        var objDiv = document.getElementById('" . $div . "');
        objDiv.scrollTop = objDiv.scrollHeight;
    if (in_ajax()) {
        $Ajax->addScript(true, $js);
    } else

function get_js_history($vars)
    $js = ' 
    function changeVar() {
        var stateObj = { foo: "bar" };
        var state = "";

    $first = true;
    foreach ( $vars as $var ) {
        $js .= '
        var element = document.getElementsByName("' . $var. '");
        if (element[0])';
        if ($first) {
            $first = false;
            $js .= '
            state += "?';
        } else
            $js .= '
            state += "&';
        $js .= $var .'="' . ' + element[0].value;';

    $js .= '
        history.replaceState(stateObj, "page 2", location.protocol + "//" + + location.pathname + state);
    return $js;

function set_posts($vars)
    foreach ( $vars as $var )
        if (isset($_GET[$var]))
            $_POST[$var] = $_GET[$var];

@joe: see if you want to include them into the core.

The js history and remembering past form field values will result in a cache use and erroneous Ajax output like when a specific customer is chosen first and then a Supplier is chosen without resetting them selectively.

Also, your use of the function get_gl_transactions() in the said report has 12 arguments whereas the standard one in gl/includes/db/ has only 11. The extra argument used is



Re: Braathwaate quickreport_inquiry

@apmuthu: I fixed the reset problem you mentioned.  Thanx.

I have a differing core and the get_gl_transactions() differs in several ways.  One difference is the person_type argument that you mention.   Another difference is that the filter_type parameter accepts a list of gl types instead of just one, which is necessary for the balances to be correct in this report by not displaying deliveries.   A third difference is that the memo field searches by both gl memo and comment memo.

The purpose of this inquiry page is to condense the display of g/l account transactions by placing the two accounts of a double entered transaction into separate columns rather than on separate lines like the standard gl inquiry.   If there are more than two accounts for a transaction, it displays "split".

This is helpful in visually identifying transactions entered into wrong accounts because often transactions use the same accounts each time.   For example, payments to a fuel supplier might usually use the accounts "Auto Expenses" and "Credit Card".   If one scans down the list of transactions for this supplier and then sees "Office Expenses" on one transaction, it probably was incorrectly entered and appropriate action can be taken (by clicking on the pencil to edit the transaction).

Re: Braathwaate quickreport_inquiry

@joe: It might be worthwhile to synch the function get_gl_transactions() with that of @braathwaate. The said function is defined in gl/includes/db/ and is used in the core in:


Furthermore, it is not used in any other extension as well. Besides, the effective_rate is not used anywhere at all (but used in the list of sql fields in function get_trans_tax_details in it!

In fact, the function get_trans_tax_details() from lines 478 to 493:

function get_trans_tax_details($trans_type, $trans_no)
    $sql = "SELECT tax_details.*,
       AS tax_type_name,
                tax_details.rate AS effective_rate,
                tax_type.rate AS rate
        FROM ".TB_PREF."trans_tax_details tax_details,
            ".TB_PREF."tax_types tax_type
            trans_type = ".db_escape($trans_type)."
        AND trans_no = ".db_escape($trans_no)."
        AND (net_amount != 0 OR amount != 0)
        AND = tax_details.tax_type_id";

    return db_query($sql, "The transaction tax details could not be retrieved");

can now be simplified to be:

function get_trans_tax_details($trans_type, $trans_no)
    $sql = "SELECT tax_details.*, AS tax_type_name, tax_type.rate AS rate
        FROM ".TB_PREF."trans_tax_details tax_details INNER JOIN 
        ".TB_PREF."tax_types tax_type ON = tax_details.tax_type_id
                trans_type = ".db_escape($trans_type)."
            AND trans_no = ".db_escape($trans_no)."
            AND (net_amount != 0 OR amount != 0)";

    return db_query($sql, "The transaction tax details could not be retrieved");

Re: Braathwaate quickreport_inquiry

Your optimized function has been committed to core.

Regarding @braathwaate's get_transactions function, I will have to check it carefully before syncing.


Re: Braathwaate quickreport_inquiry

@apmuthu or @Braathwaate,

Where can I find the specific variant of get_transactions?


Re: Braathwaate quickreport_inquiry

Note: this also has my suggested fix for mantis issue 0004817: g/l account inquiry can timeout due to slow sql.

function get_gl_transactions($from_date, $to_date, $trans_no=0,
    $account=null, $dimension=0, $dimension2=0, $filter_type=null,
        $amount_min=null, $amount_max=null, $person_type=null, $person_id=null, $memo='')
    global $SysPrefs;

    $from = date2sql($from_date);
    $to = date2sql($to_date);

    $sql = "SELECT gl.*, j.event_date, j.doc_date, a.gl_seq, u.user_id, st.supp_reference, gl.person_id subcode,
            IFNULL(IFNULL(sup.supp_name,, bt.person_id) as person_name, 
            IFNULL(gl.person_id, IFNULL(sup.supplier_id, IFNULL(debt.debtor_no, bt.person_id))) as person_id,
            IF(gl.person_id, gl.person_type_id, IF(sup.supplier_id,".  PT_SUPPLIER . "," .  "IF(debt.debtor_no," . PT_CUSTOMER . "," . 
            "IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_id,
            IFNULL(st.tran_date, IFNULL(dt.tran_date, IFNULL(bt.trans_date, IFNULL(grn.delivery_date, gl.tran_date)))) as doc_date,
            coa.account_name, ref.reference, IF(ISNULL(c.memo_), gl.memo_, CONCAT(gl.memo_,' ',c.memo_)) AS memo
             FROM "
            .TB_PREF."gl_trans gl
            LEFT JOIN ".TB_PREF."voided v ON AND v.type=gl.type

            LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=st.supplier_id)
            LEFT JOIN ".TB_PREF."grn_batch grn ON AND gl.type=".ST_SUPPRECEIVE."
            LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=dt.debtor_no)

            LEFT JOIN ".TB_PREF."suppliers sup ON st.supplier_id=sup.supplier_id
            LEFT JOIN ".TB_PREF."cust_branch branch ON dt.branch_code=branch.branch_code
            LEFT JOIN ".TB_PREF."debtors_master debt ON dt.debtor_no=debt.debtor_no

            LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
                 AND (bt.person_id != '' AND !ISNULL(bt.person_id))

            LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no
            LEFT JOIN ".TB_PREF."audit_trail a ON a.type=gl.type AND a.trans_no=gl.type_no AND NOT ISNULL(gl_seq)
            LEFT JOIN ".TB_PREF."users u ON
            LEFT JOIN ".TB_PREF."comments c ON AND c.type=gl.type

            LEFT JOIN ".TB_PREF."refs ref ON ref.type=gl.type AND,"
        .TB_PREF."chart_master coa
        WHERE coa.account_code=gl.account
        AND ISNULL(v.date_)
        AND gl.tran_date >= '$from'
        AND gl.tran_date <= '$to'
        AND gl.amount <> 0"; 

    if ($trans_no > 0)
        $sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no);;

    if ($account != null)
        $sql .= " AND gl.account = ".db_escape($account);

    if ($dimension != 0)
        $sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));

    if ($dimension2 != 0)
        $sql .= " AND gl.dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));

    if ($filter_type != null)
        $sql .= " AND gl.type IN (" . $filter_type .")";

    if ($amount_min != null)
        $sql .= " AND ABS(gl.amount) >= ABS(".db_escape($amount_min).")";
    if ($amount_max != null)
        $sql .= " AND ABS(gl.amount) <= ABS(".db_escape($amount_max).")";

    if ($memo)
        $sql .= " AND (gl.memo_ LIKE ". db_escape("%$memo%") . " OR c.memo_ LIKE " . db_escape("%$memo%") . ")";

    $sql .= " GROUP BY counter";

    $sql .= " HAVING TRUE";
    if ($person_type != 0)
            $sql .= " AND person_type_id=".db_escape($person_type); 
    if ($person_id != 0)
            $sql .= " AND person_id=".db_escape($person_id); 

    $sql .= " ORDER BY tran_date, counter";

    return db_query($sql, "The transactions for could not be retrieved");

Re: Braathwaate quickreport_inquiry


Any change to parameters in the file gl_account_inquiry.php that calls get_gl_transactions?

This is the only file that are different in the new parameter list.


Re: Braathwaate quickreport_inquiry


Or do you want 2 extra input filters on the gl inquiry, person-type and person combo. In that case we should probably use som ajax-update when selecting.


Re: Braathwaate quickreport_inquiry

My gl_account_inquiry.php differs from the core because it offers the person-type/person-id filters, just like the quickreport extension.  So the call in my gl_account_inqury.php is:

    $result = get_gl_transactions($_POST['TransFromDate'], $_POST['TransToDate'], -1,
        $_POST["account"], $_POST['Dimension'], $_POST['Dimension2'], null,
        input_num('amount_min'), input_num('amount_max'), get_post('person_type'), get_post('person_id'), $_POST['Memo']);

Without the person-type/person-id filter code, those parameters can be null.

In quickreport.php and my gl inquiry, the person-type/person-id list is ajax enabled.   One regret is that the code does not search on the Miscellaneous person type so perhaps there should be an additional <no search> entry in the list.

The original impetus to add the person-type/person-id filter was because there is no other way of searching for quick entry transactions in FA.

Re: Braathwaate quickreport_inquiry

Ok, I understand. I think I will just use your get_gl_transactions for now. To fix the timeout problem.

And put the extra null parameter in gl_account_inquiry.php.

We could then easily get more fix here later. Thanks for helping.


Re: Braathwaate quickreport_inquiry

The 4th post in this thread lists the files that use the said function.

Re: Braathwaate quickreport_inquiry

This is fixed and committed to repo.


Re: Braathwaate quickreport_inquiry

Hope this addresses the new argument in the calls in:


Re: Braathwaate quickreport_inquiry

Yes, they should. These have only a few parameters. The rest is default.