Topic: Grab Data of individual Accounts From Database

hey,

i am trying to grab / fetch data of individual account, can you please tell me how can i do it because everytime i am failed!

i want the net total of :

e.g Income , Expenses ,

Re: Grab Data of individual Accounts From Database

There is a trial balance report that provides all the ledger balances.

Re: Grab Data of individual Accounts From Database

hey,

i have upload a image for you to what i want

https://imgur.com/a/crjzGJe

in this image the red box that i want to grab the amounts in separate page or top of the each page

if you guide me about 1 section then we will grab for anything that we want

because our client dont want to see the long TB / BS they want short report

i mean

TOTAL CURRENT ASSETS
TOTAL LTA

TOTAL ASSETS


SAME GOES IN TB OR B/S

help me its urgent

regards

Post's attachments

GL_BS.png 81.4 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

Re: Grab Data of individual Accounts From Database

Edit by commenting out the inner loop echo statements in gl/inquiry/gl_trial_balance.php.

Re: Grab Data of individual Accounts From Database

i have already seen it but i think i cant explain clearly;

i make a page of short_tb.php & short_pls.php & short_bs.php

and here i want to show TB in short form like all head total like:

Current assets & LTA = Assets
Current liabilities & LTL = Liabilities
Sales = Sales
Expenses = Expenses

its mean i want to show only main head in different pages.

how can i do that? if i am not clear to you let me know

Re: Grab Data of individual Accounts From Database

Attached is a sample screenshot and the tb summary file for your reference.

Post's attachments

ShortTB.zip 23.2 kb, 2 downloads since 2018-11-21 

You don't have the permssions to download the attachments of this post.

Re: Grab Data of individual Accounts From Database

hey, thanks for reply me well i will look as soon as possible
but kindly take a look this balance sheet

https://www50.zippyshare.com/v/O06EKLTt/file.html

and screenshot is here: https://imgur.com/a/FqJ6Mhg

in this bs.php file its grab account_class all to all

can you please tell me or do it can we get only 1 account via giving account_class id e.g 1 or account code 1200 for current assets

please take a look and tell me...

regards

Post's attachments

short_bs.zip 4.8 kb, 1 downloads since 2018-11-21 

You don't have the permssions to download the attachments of this post.

Re: Grab Data of individual Accounts From Database

The Balance Sheet Drilldown page does what you want.
Also the General Ledger Enquiry too provides the single ledger folio.

Post's attachments

BS_DrillDown.png 9.8 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

Re: Grab Data of individual Accounts From Database

@emiangel839 - Regarding your comment on my site. here I answered you with the query. 

 $today = Today();
    $begin = begin_fiscalyear();
    $begin1 = date2sql($begin);
    $today1 = date2sql($today);
    $sql = "SELECT SUM(amount) AS total, c.class_name, c.ctype FROM
        ".TB_PREF."gl_trans,".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t, 
        ".TB_PREF."chart_class AS c WHERE
        account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid AND c.cid=1 
        AND IF(c.ctype > 3, tran_date >= '$begin1', tran_date >= '0000-00-00') 
        AND tran_date <= '$today1'  ORDER BY c.cid"; 
    $result = db_query($sql, "Transactions could not be calculated");

hope it helps you

Re: Grab Data of individual Accounts From Database

well bro leave it i think i cant explain clearly,

well, can you please tell how can i make a function to get my total receivables see image like this

see image: https://imgur.com/a/hGf36kH

please help me and make one function as example others i will do it by myself

hope you understand now.

its urgent

Post's attachments

Get_Total_Receivables.png 11.7 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

Re: Grab Data of individual Accounts From Database

kvvaradha wrote:

@emiangel839 - Regarding your comment on my site. here I answered you with the query. 

 $today = Today();
    $begin = begin_fiscalyear();
    $begin1 = date2sql($begin);
    $today1 = date2sql($today);
    $sql = "SELECT SUM(amount) AS total, c.class_name, c.ctype FROM
        ".TB_PREF."gl_trans,".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t, 
        ".TB_PREF."chart_class AS c WHERE
        account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid AND c.cid=1 
        AND IF(c.ctype > 3, tran_date >= '$begin1', tran_date >= '0000-00-00') 
        AND tran_date <= '$today1'  ORDER BY c.cid"; 
    $result = db_query($sql, "Transactions could not be calculated");

hope it helps you


thanks but see my image what exactly i want

Re: Grab Data of individual Accounts From Database

Still we are not clear what you are asking, receivables and payables are just balances of sales and purchase.

You know what, apmuthu already said, the trail balance has what you want. So why dont you check the trail balance queries to program it.

From the image, itself you are giving confusions.  I am not sure what you looking.

Check my LTE theme demo dashboard bottom " Class Balances" Table. I have shown such a things i guess.

Re: Grab Data of individual Accounts From Database

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

Post's attachments

ACBals.png 37.8 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

Re: Grab Data of individual Accounts From Database

 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

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

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

Re: Grab Data of individual Accounts From Database

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

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

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

Re: Grab Data of individual Accounts From Database

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

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

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

@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

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

Re: Grab Data of individual Accounts From Database

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