FrontAccounting forum — Grab Data of individual Accounts From Database

Re: Grab Data of individual Accounts From Database

null@example.com (apmuthu) — Wed, 28 Nov 2018 03:22:30 +0000

The issue here is that the alias of the table in addressing the field may be required in some MySQL versions. Therefore the field "account" referred to in the existing sql was sought by you to be replaced by "trans.account" which is okay. The NULL check is still retained.

Re: Grab Data of individual Accounts From Database

null@example.com (kvvaradha) — Tue, 27 Nov 2018 05:54:40 +0000

@apmuthu, its a mandatory parameter to get_balance function. so he has to add it without checking whether the account code null or not.

Re: Grab Data of individual Accounts From Database

null@example.com (apmuthu) — Mon, 26 Nov 2018 16:01:26 +0000

Since the gl_trans is declared with an alias trans it might be necessary for some MySQL versions to use it as in:

        .($account == null ? '' : " AND trans.account=".db_escape($account))

Re: Grab Data of individual Accounts From Database

null@example.com (emiangel839) — Mon, 26 Nov 2018 07:44:49 +0000

@kvvaradha, can you please contact me personally, mailto: emiangel839@gmail.com

Re: Grab Data of individual Accounts From Database

null@example.com (kvvaradha) — Mon, 26 Nov 2018 06:12:04 +0000

@emiangel - You can do it simply by adding this one.

    WHERE trans.account=coa.account_code

Here add it like this

    WHERE trans.account=coa.account_code AND trans.account=".db_escape($account)."

Hope it connects the account code and gets what you needed

Re: Grab Data of individual Accounts From Database

null@example.com (emiangel839) — Mon, 26 Nov 2018 05:36:04 +0000

hey, i have already seen this but the problem is i am unable to connect my $account with account name or account code

see your function

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,"
            .TB_PREF."chart_master coa,"
            .TB_PREF."chart_types act_type, "
            .TB_PREF."chart_class act_class
        WHERE trans.account=coa.account_code
            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);
}

in above $account not linked from my side

Re: Grab Data of individual Accounts From Database

null@example.com (apmuthu) — Sun, 25 Nov 2018 23:39:04 +0000

Note that array element inline addressing is available in later versions of PHP only.

The function get_balance() is defined from Line 316 of gl/includes/db/gl_db_trans.inc and it;s prototype is:

function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true)

The computed data fields available are: credit, debit and balance.

Note that this file has had a commit recently and you may need to update it accordingly.

Re: Grab Data of individual Accounts From Database

null@example.com (emiangel839) — Sun, 25 Nov 2018 09:22:34 +0000

here is for same Professional Expenses

$PE = get_balance(1245, 0, 0, begin_fiscalyear(), Today(), true, true)['balance'];

here PR = Professional Expenses and Account code is 1245

Re: Grab Data of individual Accounts From Database

null@example.com (emiangel839) — Sun, 25 Nov 2018 09:10:27 +0000

Can you please guide me because i am unable to fetch this... hope so you can

Re: Grab Data of individual Accounts From Database

null@example.com (apmuthu) — Sun, 25 Nov 2018 02:58:21 +0000

Choose your Account Receivable using some other function/query and then use the get_balance() function with that value instead of 1065.

Re: Grab Data of individual Accounts From Database

null@example.com (emiangel839) — Fri, 23 Nov 2018 07:08:37 +0000

hey,

i am using this : $AR = get_balance(1065, 0, 0, begin_fiscalyear(), Today(), true, true)['balance'];

here AR is Account Receivable and ID code is 1065

can you please tell me how can i get data without using 1065 because every time 1065 not equal to account receivable

any help

Re: Grab Data of individual Accounts From Database

null@example.com (apmuthu) — Fri, 23 Nov 2018 01:00:45 +0000

Probably does not have the opening balances.....

Re: Grab Data of individual Accounts From Database

null@example.com (emiangel839) — Thu, 22 Nov 2018 16:37:51 +0000

kvvaradha wrote:

 SELECT SUM(amount), account_name FROM 0_gl_trans, 0_chart_master WHERE 0_gl_trans.account=0_chart_master.account_code GROUP BY account

hope this query satisfy your needs.

not working....

Re: Grab Data of individual Accounts From Database

null@example.com (kvvaradha) — Thu, 22 Nov 2018 09:19:04 +0000

 SELECT SUM(amount), account_name FROM 0_gl_trans, 0_chart_master WHERE 0_gl_trans.account=0_chart_master.account_code GROUP BY account

hope this query satisfy your needs.

Re: Grab Data of individual Accounts From Database

null@example.com (emiangel839) — Thu, 22 Nov 2018 06:25:44 +0000

well bro, again i am trying to make a BLOCK that shows only AMOUNT/BALANCES of ACCOUNTS,
see example of image

https://imgur.com/a/xVpxAQr

the balances will be linked with Trial Balance show we can easily grab any amount/balance what we want in future

the main reason is when we grab balances or amounts from TB then afterwards we calculate the RATIO ANALYSIS

so firstly, i want to grab INDIVIDUAL BALANCE that shown in image TB,

see image

Regards