X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fincludes%2Fdb%2Fgl_db_trans.inc;h=a899f7b4e6f272ff57ef425aa5d776e5431879c5;hb=48589f9ce6c51bb25e2fa8ed83c57d9dfa485e44;hp=4749b41354c7c8c277f8e5a679e4a9b3ae2eb6fe;hpb=ae038fe92d264f26d66fd8e0917d7eb845bd65bb;p=fa-stable.git diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 4749b413..a899f7b4 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -92,7 +92,7 @@ function add_gl_balance($type, $trans_id, $date_, $amount, $person_type_id=null, { $amount = round2($amount, user_price_dec()); - if (floatcmp($amount, 0)) + if ($amount != 0.0) { error_log(sprintf( _("Rounding error %s encountered for trans_type:%s,trans_no:%s"), $amount, $type, $trans_id)); return add_gl_trans($type, $trans_id, $date_, get_company_pref('exchange_diff_act'), 0, 0, "", @@ -104,75 +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' - 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"); +} //-------------------------------------------------------------------------------- @@ -279,7 +289,7 @@ function get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $d $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()); } //-------------------------------------------------------------------------------- @@ -303,7 +313,7 @@ function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dim $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()); } //---------------------------------------------------------------------------------------------------- @@ -448,18 +458,20 @@ function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, // function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included, $amount, $net_amount, $ex_rate, $tran_date, $memo, $reg_type=null) -{ +{ // 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; + $reg_type = in_array($trans_type, array(ST_SUPPINVOICE, ST_SUPPCREDIT)) ? TR_INPUT + : (in_array($trans_type, array(ST_SALESINVOICE, ST_CUSTCREDIT)) ? TR_OUTPUT : 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).")"; @@ -470,19 +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 tax_details.*, - tax_type.name AS tax_type_name, - tax_details.rate AS effective_rate, - tax_type.rate AS rate - FROM ".TB_PREF."trans_tax_details tax_details, - ".TB_PREF."tax_types tax_type - WHERE - trans_type = ".db_escape($trans_type)." - AND trans_no = ".db_escape($trans_no)." - AND (net_amount != 0 OR amount != 0) - AND tax_type.id = 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"); } //---------------------------------------------------------------------------------------- @@ -513,24 +521,24 @@ 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.",".ST_BANKDEPOSIT.")) ), 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.",".ST_BANKDEPOSIT.")) ), amount*ex_rate,0)) payable, - SUM(IF(trans_type IN(".ST_SUPPCREDIT."),-1,1)* - IF(reg_type=".TR_INPUT + SUM(IF(trans_type IN(".ST_SUPPCREDIT.",".ST_BANKPAYMENT."),-1,1)* + IF((reg_type=".TR_INPUT." OR trans_type IN (".ST_SUPPINVOICE.",".ST_BANKPAYMENT."))" . ($also_zero_purchases ? '': " AND tax_type_id AND taxrec.rate") .", net_amount*ex_rate, 0)) net_input, - SUM(IF(trans_type IN(".ST_SUPPCREDIT."),-1,1)* - IF(reg_type=".TR_INPUT + SUM(IF(trans_type IN(".ST_SUPPCREDIT.",".ST_BANKPAYMENT."),-1,1)* + IF((reg_type=".TR_INPUT." OR trans_type IN (".ST_SUPPINVOICE.",".ST_BANKPAYMENT."))" . ($also_zero_purchases ? '': " AND tax_type_id AND taxrec.rate ") .", amount*ex_rate, 0)) collectible, taxrec.rate, @@ -591,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.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.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; }