X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;ds=sidebyside;f=gl%2Fincludes%2Fdb%2Fgl_db_trans.inc;h=459db9f4ceafe826a67a5b20bdca3d2d79729a8c;hb=de2da4b73a8505f8e6032bb04b26d859339c89f4;hp=4e907726bdf56ef65c8cd4c05964395f7d8d77d6;hpb=6bcd87642330092910f5d6977845a76ac59350ac;p=fa-stable.git diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 4e907726..459db9f4 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -148,7 +148,7 @@ function get_gl_transactions($from_date, $to_date, $trans_no=0, AND gl.amount <> 0"; if ($trans_no > 0) - $sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no);; + $sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no); if ($account != null) $sql .= " AND gl.account = ".db_escape($account); @@ -462,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).")"; @@ -597,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, + IFNULL(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(gl.person_id, 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; }