Re: How to run the final SQL query

null@example.com (apmuthu) — Wed, 14 Mar 2018 15:00:45 +0000

The TB_PREF is a constant defined as the value of the tbpref array element in the appropriate company# in the config_db.php file. It is the table prefix for your database instance used for the company under reference, generally: 0_, 1_, etc.

All PT_* and ST_* constants among others are defined in includes/types.inc file.

The hard coded date and other parts of the sql are generally taken from the SysPrefs object or from various form fields the scripts interact with.

How to run the final SQL query

null@example.com (nashmil.b) — Wed, 14 Mar 2018 11:27:34 +0000

I'm new to FA, and have some information about PHP and SQL. Now I want to run the SQL query in my FA database but have some problems. In the following query what are the TB_PREF, PT_SUPPLIER, PT_CUSTOMER and other constants? And in case if I want to have them replaced, what are the values?

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, debt.name), bt.person_id) as person_name, 
            IFNULL(gl.person_id, IFNULL(sup.supplier_id, debt.debtor_no)) as person_id,
            IF(gl.person_id, gl.person_type_id, IF(sup.supplier_id,".  PT_SUPPLIER . "," .  "IF(debt.debtor_no," . PT_CUSTOMER . ", -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
             FROM "
            .TB_PREF."gl_trans gl
            LEFT JOIN ".TB_PREF."voided v ON gl.type_no=v.id 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 grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." AND gl.person_id=grn.supplier_id
            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 OR grn.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_type_id=gl.person_type_id AND bt.person_id=gl.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 a.user=u.id
            LEFT JOIN ".TB_PREF."refs ref ON ref.type=gl.type AND ref.id=gl.type_no,"
        .TB_PREF."chart_master coa
        WHERE coa.account_code=gl.account
        AND ISNULL(v.date_)
        AND gl.tran_date >= '01.14.2018'
        AND gl.tran_date <= '02.14.2018'
        AND gl.amount <> 0"

FrontAccounting forum — How to run the final SQL query

Re: How to run the final SQL query

How to run the final SQL query