X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fincludes%2Fdb%2Fgl_db_trans.inc;h=ebea4c1d879900d22923e64afe5548dc12d0acf7;hb=e72f0b009f8e0bbfe4b42aba11aded7f0d2b8c41;hp=26ad6b2ac5047d741bd24dbe752d3ac81b01e9cc;hpb=9a1b3abd5371ad7f6315db1d56f8356587444314;p=fa-stable.git diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 26ad6b2a..ebea4c1d 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -44,7 +44,7 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension } if (!is_subledger_account($account) || $account==get_company_pref('grn_clearing_act')) $person_id = $person_type_id = null; - + $sql = "INSERT INTO ".TB_PREF."gl_trans ( type, type_no, tran_date, account, dimension_id, dimension2_id, memo_, amount"; @@ -79,6 +79,7 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension function add_gl_trans_std_cost($type, $trans_id, $date_, $account, $dimension, $dimension2, $memo_, $amount, $person_type_id=null, $person_id=null, $err_msg="") { + if ($amount != 0) return add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension2, $memo_, $amount, null, $person_type_id, $person_id, $err_msg); @@ -103,76 +104,85 @@ function add_gl_balance($type, $trans_id, $date_, $amount, $person_type_id=null, //-------------------------------------------------------------------------------- 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_id=null) + $account=null, $dimension=0, $dimension2=0, $filter_type=null, + $amount_min=null, $amount_max=null, $person_type=null, $person_id=null, $memo='') { - global $SysPrefs; + global $SysPrefs; - $from = date2sql($from_date); - $to = date2sql($to_date); + $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, debt.name), bt.person_id) as person_name, - IFNULL(gl.person_id, IFNULL(sup.supplier_id, debt.debtor_no)) as person_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 + $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, debt.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 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."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." + 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."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_type_id=gl.person_type_id AND bt.person_id=gl.person_id + 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 a.user=u.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."comments c ON c.id=gl.type_no AND c.type=gl.type - 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 >= '$from' - AND gl.tran_date <= '$to'"; - if (isset($SysPrefs->show_voided_gl_trans) && $SysPrefs->show_voided_gl_trans == 0) - $sql .= " AND gl.amount <> 0"; + 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 >= '$from' + AND gl.tran_date <= '$to' + AND gl.amount <> 0"; - if ($person_id) - $sql .= " AND gl.person_id=".db_escape($person_id); + if ($trans_no > 0) + $sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no); - 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 ($account != null) - $sql .= " AND gl.account = ".db_escape($account); + if ($dimension != 0) + $sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)); - 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 ($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 ($filter_type != null AND is_numeric($filter_type)) - $sql .= " AND gl.type= ".db_escape($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 ($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 .= " ORDER BY tran_date, counter"; + $sql .= " GROUP BY counter"; - return db_query($sql, "The transactions for could not be retrieved"); -} + $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"); +} //-------------------------------------------------------------------------------- @@ -204,9 +214,10 @@ function get_gl_trans($type, $trans_id) //-------------------------------------------------------------------------------- -function get_gl_wo_cost_trans($trans_id, $cost_type=-1) +function get_gl_wo_cost_trans($trans_id, $cost_type=-1, $all_gl=false) { - $sql = "SELECT costing.*, gl.*, chart.account_name, com.memo_ FROM " + $sql = "SELECT costing.*, gl.*, chart.account_name, com.memo_ + FROM " .TB_PREF."wo_costing costing, " .TB_PREF."gl_trans gl LEFT JOIN ".TB_PREF."comments com ON gl.type=com.type AND gl.type_no=com.id," .TB_PREF."chart_master chart @@ -217,7 +228,44 @@ function get_gl_wo_cost_trans($trans_id, $cost_type=-1) AND gl.type_no=costing.trans_no"; if ($cost_type != -1) $sql .= " AND costing.cost_type=".db_escape($cost_type); - $sql .= " AND amount < 0"; + $sql .= $all_gl ? " AND amount != 0" : " AND amount < 0"; + + return db_query($sql, "The gl transactions could not be retrieved"); +} + +function get_gl_wo_issue_trans($trans_id, $person_id=-1, $all_gl=false) +{ + $sql = "SELECT issue.*, gl.*, chart.account_name, com.memo_ + FROM " + .TB_PREF."wo_issues issue," + .TB_PREF."gl_trans gl LEFT JOIN ".TB_PREF."comments com ON gl.type=com.type AND gl.type_no=com.id," + .TB_PREF."chart_master chart + WHERE issue.workorder_id=".db_escape($trans_id) + ." AND chart.account_code=gl.account + AND gl.type=".ST_MANUISSUE." AND gl.type_no=issue.issue_no"; +// ." AND gl.person_type_id=".PT_WORKORDER; + if ($person_id != -1) + $sql .= " AND gl.person_id=".db_escape($person_id); + $sql .= $all_gl ? " AND amount != 0" : " AND amount < 0"; + $sql .= " ORDER BY type, type_no"; + return db_query($sql, "The gl transactions could not be retrieved"); +} + +function get_gl_wo_productions($trans_id, $all_gl=false) +{ + $sql = "SELECT rcv.*, gl.*, chart.account_name, com.memo_ + FROM " + .TB_PREF."wo_manufacture rcv, " + .TB_PREF."gl_trans gl LEFT JOIN ".TB_PREF."comments com ON gl.type=com.type AND gl.type_no=com.id," + .TB_PREF."chart_master chart + WHERE + rcv.workorder_id=".db_escape($trans_id) + ." AND chart.account_code=gl.account + AND gl.type=".ST_MANURECEIVE." + AND gl.type_no=rcv.id + AND amount != 0 " + .($all_gl ? " AND amount != 0" : " AND amount < 0") + ." ORDER BY type, type_no"; return db_query($sql, "The gl transactions could not be retrieved"); } @@ -234,14 +282,14 @@ function get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $d if ($to_date != "") $sql .= " AND tran_date < '$to'"; if ($dimension != 0) - $sql .= " AND dimension_id = ".($dimension<0?0:db_escape($dimension)); + $sql .= " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)); if ($dimension2 != 0) - $sql .= " AND dimension2_id = ".($dimension2<0?0:db_escape($dimension2)); + $sql .= " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2)); $result = db_query($sql, "The starting balance for account $account could not be calculated"); $row = db_fetch_row($result); - return $row[0]; + return round2($row[0], user_price_dec()); } //-------------------------------------------------------------------------------- @@ -258,43 +306,37 @@ function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dim if ($to_date != "") $sql .= " AND tran_date <= '$to'"; if ($dimension != 0) - $sql .= " AND dimension_id = ".($dimension<0?0:db_escape($dimension)); + $sql .= " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)); if ($dimension2 != 0) - $sql .= " AND dimension2_id = ".($dimension2<0?0:db_escape($dimension2)); + $sql .= " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2)); $result = db_query($sql, "Transactions for account $account could not be calculated"); $row = db_fetch_row($result); - return (float)$row[0]; + return round2($row[0], user_price_dec()); } //---------------------------------------------------------------------------------------------------- function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true) { - $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,".TB_PREF."chart_master," - .TB_PREF."chart_types, ".TB_PREF."chart_class - WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND " - .TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id - AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND"; - - if ($account != null) - $sql .= " account=".db_escape($account)." AND"; - if ($dimension != 0) - $sql .= " dimension_id = ".($dimension<0?0:db_escape($dimension))." AND"; - if ($dimension2 != 0) - $sql .= " dimension2_id = ".($dimension2<0?0:db_escape($dimension2))." AND"; $from_date = date2sql($from); - if ($from_incl) - $sql .= " tran_date >= '$from_date' AND"; - else - $sql .= " tran_date > IF(ctype>0 AND ctype<".CL_INCOME.", '0000-00-00', '$from_date') AND"; $to_date = date2sql($to); - if ($to_incl) - $sql .= " tran_date <= '$to_date' "; - else - $sql .= " tran_date < '$to_date' "; + + $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"); @@ -309,7 +351,8 @@ function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $from = date2sql($from_date); $to = date2sql($to_date); - $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans + $sql = "SELECT SUM(amount) + FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account); if ($from_date != "") $sql .= " AND tran_date >= '$from' "; @@ -392,14 +435,11 @@ function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, if(!$tax_type) return; // $gl_code is not tax account $tax = get_tax_type($tax_type); - //if ($gl_code == $tax['sales_gl_code']) if ($trans_type == ST_SALESINVOICE || $trans_type == ST_CUSTDELIVERY || $trans_type == ST_CUSTCREDIT) $amount = -$amount; // we have to restore net amount as we cannot know the base amount if ($net_amount===null) { if ($tax['rate'] == 0) { -// display_warning(_("You should not post gl transactions -// to tax account with zero tax rate.")); $net_amount = 0; } else { // calculate net amount @@ -422,14 +462,16 @@ function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included // guess tax register if not set if (!isset($reg_type)) $reg_type = in_array($trans_type, array(ST_SUPPINVOICE, ST_SUPPCREDIT)) ? TR_OUTPUT - : in_array($trans_type, array(ST_SALESINVOICE, ST_CUSTCREDIT)) ? TR_INPUT : null; + : (in_array($trans_type, array(ST_SALESINVOICE, ST_CUSTCREDIT)) ? TR_INPUT : null); $sql = "INSERT INTO ".TB_PREF."trans_tax_details (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate, included_in_price, net_amount, amount, memo, reg_type) VALUES (".db_escape($trans_type)."," . db_escape($trans_no).",'" - .date2sql($tran_date)."',".db_escape($tax_id)."," - .db_escape($rate).",".db_escape($ex_rate).",".($included ? 1:0)."," + .date2sql($tran_date)."'," + .(int)($tax_id)."," + .(float)($rate)."," + .(float)($ex_rate).",".($included ? 1:0)."," .db_escape($net_amount)."," .db_escape($amount).",".db_escape($memo).",".db_escape($reg_type, true).")"; @@ -440,17 +482,15 @@ function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included function get_trans_tax_details($trans_type, $trans_no) { - $sql = "SELECT ".TB_PREF."trans_tax_details.*, " - .TB_PREF."tax_types.name AS tax_type_name, " - .TB_PREF."trans_tax_details.rate AS effective_rate, " - .TB_PREF."tax_types.rate AS rate - FROM ".TB_PREF."trans_tax_details,".TB_PREF."tax_types - WHERE trans_type = ".db_escape($trans_type)." - AND trans_no = ".db_escape($trans_no)." - AND (net_amount != 0 OR amount != 0) - AND ".TB_PREF."tax_types.id = ".TB_PREF."trans_tax_details.tax_type_id"; - - return db_query($sql, "The transaction tax details could not be retrieved"); + $sql = "SELECT tax_details.*, tax_type.name 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_type.id = tax_details.tax_type_id + WHERE + 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"); } //---------------------------------------------------------------------------------------- @@ -481,15 +521,15 @@ function get_tax_summary($from, $to, $also_zero_purchases=false) $todate = date2sql($to); $sql = "SELECT - SUM(IF(trans_type=".ST_SUPPCREDIT.",-1,1)* + SUM(IF(trans_type=".ST_CUSTCREDIT.",-1,1)* IF((reg_type=".TR_OUTPUT.")" - ." || ((trans_type IN(".ST_SUPPINVOICE.",".ST_SUPPCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) + ." || ((trans_type IN(".ST_SALESINVOICE.",".ST_CUSTCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) ), net_amount*ex_rate,0) ) net_output, - SUM(IF(trans_type=".ST_SUPPCREDIT.",-1,1)* + SUM(IF(trans_type=".ST_CUSTCREDIT.",-1,1)* IF((reg_type=".TR_OUTPUT.")" - ." || ((trans_type IN(".ST_SUPPINVOICE.",".ST_SUPPCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) + ." || ((trans_type IN(".ST_SALESINVOICE.",".ST_CUSTCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) ), amount*ex_rate,0)) payable, SUM(IF(trans_type IN(".ST_SUPPCREDIT."),-1,1)* @@ -559,70 +599,55 @@ function clear_gl_trans($type, $trans_id, $nested=false) } function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false, - $user_id=null, $contractor_id=null, $dimension=null) + $user_id=null) { - - $sql = "SELECT IFNULL(a.gl_seq,0) as gl_seq, - gl.tran_date, - gl.type as trans_type, - gl.type_no as trans_no, - IFNULL(MAX(supp.supp_name), MAX(cust.name)) as name, - IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference, - refs.reference," - .($dimension ? " -SUM(IF(dim.dimension in(gl.dimension_id,gl.dimension2_id), gl.amount, 0)) as amount,":" SUM(IF(gl.amount>0, gl.amount,0)) as amount,") - ."com.memo_, - IF(ISNULL(u.user_id),'',u.user_id) as user_id"; - - if ($contractor_id > 0) { - $sql.= ", st.supplier_id, dt.debtor_no "; - } - - $sql.= " FROM ".TB_PREF."gl_trans as gl - LEFT JOIN ".TB_PREF."audit_trail as a ON - (gl.type=a.type AND gl.type_no=a.trans_no) - LEFT JOIN ".TB_PREF."comments as com ON - (gl.type=com.type AND gl.type_no=com.id) - LEFT JOIN ".TB_PREF."refs as refs ON - (gl.type=refs.type AND gl.type_no=refs.id) - LEFT JOIN ".TB_PREF."users as u ON - a.user=u.id - LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no - LEFT JOIN ".TB_PREF."debtors_master cust ON gl.person_type_id=2 AND gl.person_id=cust.debtor_no - LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no - LEFT JOIN ".TB_PREF."suppliers supp ON gl.person_type_id=3 AND gl.person_id=supp.supplier_id" - .($dimension ? - " LEFT JOIN (SELECT type, type_no, MAX(IFNULL(dimension_id, dimension2_id)) dimension FROM ".TB_PREF."gl_trans GROUP BY type, type_no) dim - ON gl.type=dim.type AND gl.type_no=dim.type_no" : '') - ." WHERE gl.tran_date >= '" . date2sql($from) . "' - AND gl.tran_date <= '" . date2sql($to) . "' - AND gl.amount!=0"; - if ($ref) { - $sql .= " AND refs.reference LIKE ". db_escape("%$ref%"); - } - if ($memo) { - $sql .= " AND com.memo_ LIKE ". db_escape("%$memo%"); - } - if ($filter != -1) { - $sql .= " AND gl.type=".db_escape($filter); - } - if (!$alsoclosed) { - $sql .= " AND gl_seq=0"; - } - else - $sql .= " AND NOT ISNULL(a.gl_seq)"; - - if ($user_id != null) - $sql .= " AND user_id = ".db_escape($user_id); - - if ($contractor_id > 0) { - $sql.= " AND (dt.debtor_no =".$contractor_id; - $sql.= " OR st.supplier_id =".$contractor_id.") "; - } - - if ($dimension != null) - $sql .= " AND dim.dimension = ".db_escape($dimension); - - $sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no"; - - return $sql; + $sql = "SELECT IFNULL(a.gl_seq,0) as gl_seq, + gl.tran_date, + gl.type as trans_type, + gl.type_no as trans_no, + IF(MAX(gl.person_id), MAX(gl.person_id), IFNULL(st.supplier_id, IFNULL(grn.supplier_id, IFNULL(dt.debtor_no, bt.person_id)))) as person_id, + IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference, + refs.reference, + IF(gl.type=".ST_BANKTRANSFER.",MAX(gl.amount),SUM(IF(gl.amount>0, gl.amount,0))) as amount, + com.memo_, + IF(ISNULL(u.user_id),'',u.user_id) as user_id, + IF(MAX(gl.person_id), MAX(gl.person_type_id), IF(!ISNULL(st.supplier_id) OR !ISNULL(grn.supplier_id),". PT_SUPPLIER . "," . "IF(dt.debtor_no," . PT_CUSTOMER . "," . + "IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_id + FROM ".TB_PREF."gl_trans as gl + LEFT JOIN ".TB_PREF."audit_trail as a ON + (gl.type=a.type AND gl.type_no=a.trans_no) + LEFT JOIN ".TB_PREF."comments as com ON + (gl.type=com.type AND gl.type_no=com.id) + LEFT JOIN ".TB_PREF."refs as refs ON + (gl.type=refs.type AND gl.type_no=refs.id) + LEFT JOIN ".TB_PREF."users as u ON + a.user=u.id + LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." + 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."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no AND (gl.type_no != 0 or gl.person_id=dt.debtor_no) AND gl.person_type_id=2 + LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no AND (gl.type !=0 or gl.person_id=st.supplier_id) AND gl.person_type_id=3 + WHERE gl.tran_date >= '" . date2sql($from) . "' + AND gl.tran_date <= '" . date2sql($to) . "' + AND gl.amount!=0"; + if ($ref) { + $sql .= " AND refs.reference LIKE ". db_escape("%$ref%"); + } + if ($memo) { + $sql .= " AND com.memo_ LIKE ". db_escape("%$memo%"); + } + if ($filter != -1) { + $sql .= " AND gl.type=".db_escape($filter); + } + if (!$alsoclosed) { + $sql .= " AND gl_seq=0"; + } + else + $sql .= " AND NOT ISNULL(a.gl_seq)"; + + if ($user_id != null) + $sql .= " AND user_id = ".db_escape($user_id); + + $sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no"; + return $sql; }