1 (edited by kvvaradha 10/16/2019 07:58:21 am)

Topic: The get_balance function needs to adjusted to eliminate voided entries

The get_balance function also includes the voided payment or edited payment. Its better, we have to include the voided table to exclude the entries of voided amount.

here is my adjusted code.

//----------------------------------------------------------------------------------------------------
function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true) 
{
    $from_date = date2sql($from);
    $to_date = date2sql($to);

    $sql = "SELECT    SUM(IF(amount >= 0, amount, 0)) as debit, 
                    SUM(IF(amount < 0, -amount, 0)) as credit,
                    SUM(amount) as balance 
        FROM ".TB_PREF."gl_trans trans  LEFT JOIN ".TB_PREF."voided AS vd ON trans.type = vd.type AND trans.type_no = vd.id,"
            .TB_PREF."chart_master coa,"
            .TB_PREF."chart_types act_type, "
            .TB_PREF."chart_class act_class
        WHERE trans.account=coa.account_code  AND ISNULL(vd.id)
            AND coa.account_type=act_type.id 
        AND act_type.class_id=act_class.cid"
        ." AND ".($from_incl ? "tran_date >= '$from_date'" : "tran_date > IF(ctype>0 AND ctype<".CL_INCOME.", '0000-00-00', '$from_date')")
        ." AND ".($to_incl ? "tran_date <= '$to_date'" : "tran_date < '$to_date'")
        .($account == null ? '' : " AND account=".db_escape($account))
        .($dimension == 0 ? ''  : " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)))
        .($dimension2 == 0 ? '' : " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2)));

    $result = db_query($sql,"No general ledger accounts were returned");

    return db_fetch($result);
}

Re: The get_balance function needs to adjusted to eliminate voided entries

@joe and @itronics, check this with existing core.

Re: The get_balance function needs to adjusted to eliminate voided entries

I just wonder if this will calculate faster with this sophisticated statement.
The voided amounts are 0 so the results are the same. Just a thought.

Joe