X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;ds=sidebyside;f=gl%2Fincludes%2Fdb%2Fgl_db_trans.inc;h=f9e270d9304653f069769cdbcea5c26d87e6686d;hb=1571869d54be48452fdbe08f25130972ff0a7b5a;hp=af06cedf7a4122aaad4554584c79bfb65f635c74;hpb=074f5b55ae369597c70953bc7f9324cc491a6a7e;p=fa-stable.git diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index af06cedf..f9e270d9 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -42,6 +42,9 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension else $memo_ = $_SESSION["wa_current_user"]->username . " - " . $memo_; } + if (!is_subledger_account($account) && $account==get_company_pref('deffered_income_act') || $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"; @@ -87,12 +90,15 @@ function add_gl_trans_std_cost($type, $trans_id, $date_, $account, $dimension, $ function add_gl_balance($type, $trans_id, $date_, $amount, $person_type_id=null, $person_id=null) { $amount = round2($amount, user_price_dec()); - if ($amount != 0) + + if (floatcmp($amount, 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, "", $amount, null, $person_type_id, $person_id, "The balanced GL transaction could not be inserted"); - else + } else return 0; -} +} //-------------------------------------------------------------------------------- @@ -100,15 +106,23 @@ 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) { + global $show_voided_gl_trans; + $from = date2sql($from_date); $to = date2sql($to_date); $sql = "SELECT ".TB_PREF."gl_trans.*, " - .TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, " - .TB_PREF."chart_master - WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account + .TB_PREF."chart_master.account_name FROM " + .TB_PREF."gl_trans + LEFT JOIN ".TB_PREF."voided v ON " + .TB_PREF."gl_trans.type_no=v.id AND v.type=".TB_PREF."gl_trans.type," + .TB_PREF."chart_master" + ." WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account + AND ISNULL(v.date_) AND tran_date >= '$from' AND tran_date <= '$to'"; + if (isset($show_voided_gl_trans) && $show_voided_gl_trans == 0) + $sql .= " AND ".TB_PREF."gl_trans.amount <> 0"; if ($trans_no > 0) $sql .= " AND ".TB_PREF."gl_trans.type_no LIKE ".db_escape('%'.$trans_no); @@ -140,29 +154,33 @@ function get_gl_transactions($from_date, $to_date, $trans_no=0, function get_gl_trans($type, $trans_id) { - $sql = "SELECT ".TB_PREF."gl_trans.*, " - .TB_PREF."chart_master.account_name FROM " - .TB_PREF."gl_trans, ".TB_PREF."chart_master - WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account - AND ".TB_PREF."gl_trans.type=".db_escape($type) - ." AND ".TB_PREF."gl_trans.type_no=".db_escape($trans_id); - + $sql = "SELECT gl.*, cm.account_name, IF(ISNULL(refs.reference), '', refs.reference) AS reference FROM " + .TB_PREF."gl_trans as gl + LEFT JOIN ".TB_PREF."chart_master as cm ON gl.account = cm.account_code + LEFT JOIN ".TB_PREF."refs as refs ON (gl.type=refs.type AND gl.type_no=refs.id)" + ." WHERE gl.type= ".db_escape($type) + ." AND gl.type_no = ".db_escape($trans_id) + ." AND gl.amount <> 0" + ." ORDER BY counter"; return db_query($sql, "The gl transactions could not be retrieved"); } //-------------------------------------------------------------------------------- -function get_gl_wo_cost_trans($trans_id, $person_id=-1) +function get_gl_wo_cost_trans($trans_id, $cost_type=-1) { - $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM " - .TB_PREF."gl_trans, ".TB_PREF."chart_master - WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account - AND ".TB_PREF."gl_trans.type=".ST_WORKORDER - ." AND ".TB_PREF."gl_trans.type_no=".db_escape($trans_id)." - AND ".TB_PREF."gl_trans.person_type_id=".PT_WORKORDER; - if ($person_id != -1) - $sql .= " AND ".TB_PREF."gl_trans.person_id=".db_escape($person_id); - $sql .= " AND amount < 0"; + $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 + WHERE + costing.workorder_id=".db_escape($trans_id) + ." AND chart.account_code=gl.account + AND gl.type=costing.trans_type + AND gl.type_no=costing.trans_no"; + if ($cost_type != -1) + $sql .= " AND costing.cost_type=".db_escape($cost_type); + $sql .= " AND amount < 0"; return db_query($sql, "The gl transactions could not be retrieved"); } @@ -210,7 +228,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 $row[0]; + return (float)$row[0]; } //---------------------------------------------------------------------------------------------------- @@ -269,31 +287,91 @@ function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $row = db_fetch_row($result); return $row[0]; } +//------------------------------------------------------------------------------------- + +function exists_gl_budget($date_, $account, $dimension, $dimension2) +{ + $sql = "SELECT account FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) + ." AND tran_date='$date_' AND + dimension_id=".db_escape($dimension)." AND dimension2_id=".db_escape($dimension2); + $result = db_query($sql, "Cannot retreive a gl transaction"); + + return (db_num_rows($result) > 0); +} + +function add_update_gl_budget_trans($date_, $account, $dimension, $dimension2, $amount) +{ + $date = date2sql($date_); + + if (exists_gl_budget($date, $account, $dimension, $dimension2)) + $sql = "UPDATE ".TB_PREF."budget_trans SET amount=".db_escape($amount) + ." WHERE account=".db_escape($account) + ." AND dimension_id=".db_escape($dimension) + ." AND dimension2_id=".db_escape($dimension2) + ." AND tran_date='$date'"; + else + $sql = "INSERT INTO ".TB_PREF."budget_trans (tran_date, + account, dimension_id, dimension2_id, amount, memo_) VALUES ('$date', + ".db_escape($account).", ".db_escape($dimension).", " + .db_escape($dimension2).", ".db_escape($amount).", '')"; + + db_query($sql, "The GL budget transaction could not be saved"); +} + +function delete_gl_budget_trans($date_, $account, $dimension, $dimension2) +{ + $date = date2sql($date_); + + $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) + ." AND dimension_id=".db_escape($dimension) + ." AND dimension2_id=".db_escape($dimension2) + ." AND tran_date='$date'"; + db_query($sql, "The GL budget transaction could not be deleted"); +} + +function get_only_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0) +{ + + $from = date2sql($from_date); + $to = date2sql($to_date); + + $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans + WHERE account=".db_escape($account) + ." AND tran_date >= '$from' AND tran_date <= '$to' + AND dimension_id = ".db_escape($dimension) + ." AND dimension2_id = ".db_escape($dimension2); + $result = db_query($sql,"No budget accounts were returned"); + + $row = db_fetch_row($result); + return $row[0]; +} //-------------------------------------------------------------------------------- // Stores journal/bank transaction tax details if applicable // -function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, $date, $memo) +function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, $date, $memo, $included=0, $net_amount = null) { $tax_type = is_tax_account($gl_code); if(!$tax_type) return; // $gl_code is not tax account $tax = get_tax_type($tax_type); - //if ($gl_code == $tax['sales_gl_code']) - // $amount = -$amount; + //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 ($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 - $net_amount = $amount/$tax['rate']*100; + 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 + $net_amount = $amount/$tax['rate']*100; + } } - - add_trans_tax_details($trans_type, $trans_no, $tax['id'], $tax['rate'], 0, + add_trans_tax_details($trans_type, $trans_no, $tax['id'], $tax['rate'], $included, $amount, $net_amount, $ex_rate, $date, $memo); - + } //-------------------------------------------------------------------------------- @@ -322,7 +400,9 @@ 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."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)." @@ -343,20 +423,44 @@ function void_trans_tax_details($type, $type_no) db_query($sql, "The transaction tax details could not be voided"); } +//---------------------------------------------------------------------------------------- + +function clear_trans_tax_details($type, $type_no) +{ + $sql = "DELETE FROM ".TB_PREF."trans_tax_details + WHERE trans_no=".db_escape($type_no) + ." AND trans_type=".db_escape($type); + + db_query($sql, "The transaction tax details could not be cleared"); +} + function get_tax_summary($from, $to) { $fromdate = date2sql($from); $todate = date2sql($to); $sql = "SELECT - SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE.",-1,1)* - IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE." || trans_type=".ST_CUSTCREDIT.", net_amount*ex_rate,0)) net_output, - SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE.",-1,1)* - IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE." || trans_type=".ST_CUSTCREDIT.", amount*ex_rate,0)) payable, - SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE.",-1,1)* - IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE." || trans_type=".ST_CUSTCREDIT.", 0, net_amount*ex_rate)) net_input, - SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE.",-1,1)* - IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE." || trans_type=".ST_CUSTCREDIT.", 0, amount*ex_rate)) collectible, + SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE + ." || trans_type=".ST_JOURNAL.",-1,1)* + IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE + ." || (trans_type=".ST_JOURNAL ." AND amount<0)" + ." || trans_type=".ST_CUSTCREDIT.", net_amount*ex_rate,0)) net_output, + + SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE + ." || trans_type=".ST_JOURNAL.",-1,1)* + IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE + ." || (trans_type=".ST_JOURNAL ." AND amount<0)" + ." || trans_type=".ST_CUSTCREDIT.", amount*ex_rate,0)) payable, + + SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE." || trans_type=".ST_JOURNAL.",-1,1)* + IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE + ." || (trans_type=".ST_JOURNAL ." AND amount<0)" + ." || trans_type=".ST_CUSTCREDIT.", 0, net_amount*ex_rate)) net_input, + + SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE." || trans_type=".ST_JOURNAL.",-1,1)* + IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE + ." || (trans_type=".ST_JOURNAL ." AND amount<0)" + ." || trans_type=".ST_CUSTCREDIT.", 0, amount*ex_rate)) collectible, taxrec.rate, ttype.id, ttype.name @@ -400,9 +504,12 @@ function write_journal_entries(&$cart, $reverse, $use_transaction=true) // post to first found bank account using given gl acount code. $is_bank_to = is_bank_account($journal_item->code_id); - add_gl_trans($trans_type, $trans_id, $date_, $journal_item->code_id, - $journal_item->dimension_id, $journal_item->dimension2_id, - $journal_item->reference, $journal_item->amount); + add_gl_trans($trans_type, $trans_id, $date_, $journal_item->code_id, + $journal_item->dimension_id, $journal_item->dimension2_id, + $journal_item->reference, $journal_item->amount, null, + $journal_item->person_type_id, + $journal_item->person_id); + if ($is_bank_to) { add_bank_trans($trans_type, $trans_id, $is_bank_to, $ref, @@ -411,15 +518,14 @@ function write_journal_entries(&$cart, $reverse, $use_transaction=true) } // store tax details if the gl account is a tax account add_gl_tax_details($journal_item->code_id, - ($journal_item->amount < 0.0 ? ST_BANKDEPOSIT : ST_BANKPAYMENT), $trans_id, -$journal_item->amount, 1, $date_, $memo_); + ST_JOURNAL, $trans_id, $journal_item->amount, 1, $date_, $memo_); } + $Refs->save($trans_type, $trans_id, $ref); if ($new) { add_comments($trans_type, $trans_id, $date_, $memo_); - $Refs->save($trans_type, $trans_id, $ref); } else { update_comments($trans_type, $trans_id, null, $memo_); - $Refs->update($trans_type, $trans_id, $ref); } add_audit_trail($trans_type, $trans_id, $date_); @@ -438,7 +544,9 @@ function write_journal_entries(&$cart, $reverse, $use_transaction=true) add_gl_trans($trans_type, $trans_id_reverse, $reversingDate, $journal_item->code_id, $journal_item->dimension_id, $journal_item->dimension2_id, - $journal_item->reference, -$journal_item->amount); + $journal_item->reference, -$journal_item->amount, + null, $journal_item->person_type_id, $journal_item->person_id); + if ($is_bank_to) { add_bank_trans($trans_type, $trans_id_reverse, $is_bank_to, $ref, @@ -448,7 +556,7 @@ function write_journal_entries(&$cart, $reverse, $use_transaction=true) } // store tax details if the gl account is a tax account add_gl_tax_details($journal_item->code_id, - ($journal_item->amount < 0.0 ? ST_BANKDEPOSIT : ST_BANKPAYMENT), $trans_id, $journal_item->amount, 1, $reversingDate, $memo_); + ST_JOURNAL, $trans_id, $journal_item->amount, 1, $reversingDate, $memo_); } add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_); @@ -492,6 +600,22 @@ function void_gl_trans($type, $trans_id, $nested=false) //---------------------------------------------------------------------------------------- +function clear_gl_trans($type, $trans_id, $nested=false) +{ + if (!$nested) + begin_transaction(); + + $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE type=".db_escape($type) + ." AND type_no=".db_escape($trans_id); + + db_query($sql, "could not clear gl transactions for type=$type and trans_no=$trans_id"); + + if (!$nested) + commit_transaction(); +} + +//---------------------------------------------------------------------------------------- + function void_journal_trans($type, $type_no, $use_transaction=true) { if ($use_transaction) @@ -505,4 +629,45 @@ function void_journal_trans($type, $type_no, $use_transaction=true) commit_transaction(); } +function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false) +{ + + $sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq, + gl.tran_date, + gl.type, + gl.type_no, + refs.reference, + SUM(IF(gl.amount>0, gl.amount,0)) as amount, + com.memo_, + IF(ISNULL(u.user_id),'',u.user_id) as user_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 + WHERE gl.tran_date >= '" . date2sql($from) . "' + AND gl.tran_date <= '" . date2sql($to) . "' + AND gl.amount!=0"; + if ($ref) { + $sql .= " AND 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)"; + + $sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no"; + return $sql; +} ?> \ No newline at end of file