X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fincludes%2Fdb%2Fgl_db_trans.inc;h=3776a0f07ee12ca687adddcabfea5da5a18b4bd0;hb=ddadb47f2620ce6902ad4694ce6512568862ba05;hp=d2e766ad8dc4c59ca0826e36d9897466f87a0e33;hpb=97023f8706bb7149c62ca49eaec123cca3c51fa0;p=fa-stable.git diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index d2e766ad..3776a0f0 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -1,5 +1,14 @@ . +***********************************************************************/ //-------------------------------------------------------------------------------- // Base function for adding a GL transaction @@ -8,15 +17,20 @@ // if $currency is not set, then defaults to no conversion function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension2, $memo_, - $amount, $currency=null, $person_type_id=null, $person_id=null, $err_msg="") + $amount, $currency=null, $person_type_id=null, $person_id=null, $err_msg="", $rate=0) { global $use_audit_trail; - + $date = date2sql($date_); if ($currency != null) - $amount_in_home_currency = to_home_currency($amount, $currency, $date_); + { + if ($rate == 0) + $amount_in_home_currency = to_home_currency($amount, $currency, $date_); + else + $amount_in_home_currency = round2($amount * $rate, user_price_dec()); + } else - $amount_in_home_currency = $amount; + $amount_in_home_currency = round2($amount, user_price_dec()); if ($dimension == null || $dimension < 0) $dimension = 0; if ($dimension2 == null || $dimension2 < 0) @@ -27,7 +41,7 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension $memo_ = $_SESSION["wa_current_user"]->username; else $memo_ = $_SESSION["wa_current_user"]->username . " - " . $memo_; - } + } $sql = "INSERT INTO ".TB_PREF."gl_trans ( type, type_no, tran_date, account, dimension_id, dimension2_id, memo_, amount"; @@ -37,17 +51,18 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension $sql .= ") "; $sql .= "VALUES ($type, $trans_id, '$date', - '$account', $dimension, $dimension2, '$memo_', $amount_in_home_currency"; + '$account', $dimension, $dimension2, ".db_escape($memo_).", $amount_in_home_currency"; if ($person_type_id != null) - $sql .= ", $person_type_id, '$person_id'"; + $sql .= ", $person_type_id, ". db_escape($person_id); $sql .= ") "; if ($err_msg == "") $err_msg = "The GL transaction could not be inserted"; - return db_query($sql, $err_msg); + db_query($sql, $err_msg); + return $amount_in_home_currency; } //-------------------------------------------------------------------------------- @@ -56,16 +71,27 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension // $date_ is display date (non-sql) // $amount is in HOME currency -function add_gl_trans_std_cost($type, $trans_id, $date_, $account, $dimension, $dimension2, +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); else - return null; + return 0; } +// Function for even out rounding problems +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) + 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 + return 0; +} + //-------------------------------------------------------------------------------- function get_gl_transactions($from_date, $to_date, $trans_no=0, @@ -112,6 +138,19 @@ function get_gl_trans($type, $trans_id) //-------------------------------------------------------------------------------- +function get_gl_wo_cost_trans($trans_id, $person_id=-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=$trans_id + AND ".TB_PREF."gl_trans.person_type_id=".PT_WORKORDER; + if ($person_id != -1) + $sql .= " AND ".TB_PREF."gl_trans.person_id=$person_id"; + $sql .= " AND amount < 0"; + + return db_query($sql, "The gl transactions could not be retrieved"); +} + function get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0) { $from = date2sql($from_date); @@ -158,19 +197,49 @@ function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dim return $row[0]; } +//---------------------------------------------------------------------------------------------------- +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='$account' AND"; + if ($dimension > 0) + $sql .= " dimension_id=$dimension AND"; + if ($dimension2 > 0) + $sql .= " dimension2_id=$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' "; + + $result = db_query($sql,"No general ledger accounts were returned"); + + return db_fetch($result); +} + //-------------------------------------------------------------------------------- -function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0) +function get_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=$account "; - if ($from_date != "") + WHERE account='$account' "; + if ($from_date != "") $sql .= " AND tran_date >= '$from' "; - if ($to_date != "") + if ($to_date != "") $sql .= " AND tran_date <= '$to' "; if ($dimension > 0) $sql .= " AND dimension_id = $dimension"; @@ -183,65 +252,190 @@ function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=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) +{ + $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; + // 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; + } + + add_trans_tax_details($trans_type, $trans_no, $tax['id'], $tax['rate'], 0, + $amount, $net_amount, $ex_rate, $date, $memo); + +} -function add_journal_entries($items, $date_, $ref, $reverse, $memo_=null) +//-------------------------------------------------------------------------------- +// +// Store transaction tax details for fiscal purposes with 'freezed' +// actual tax type rate. +// +function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included, + $amount, $net_amount, $ex_rate, $tran_date, $memo) { - begin_transaction(); - $trans_type = systypes::journal_entry(); - $trans_id = get_next_trans_no($trans_type); + $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) + VALUES (".db_escape($trans_type)."," . db_escape($trans_no).",'" + .date2sql($tran_date)."',".db_escape($tax_id)."," + .$rate.",".$ex_rate.",".($included ? 1:0)."," + .db_escape($net_amount)."," + .db_escape($amount).",".db_escape($memo).")"; + + db_query($sql, "Cannot save trans tax details"); + +} +//---------------------------------------------------------------------------------------- + +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 + FROM ".TB_PREF."trans_tax_details,".TB_PREF."tax_types + WHERE trans_type = $trans_type + AND trans_no = $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"); +} + +//---------------------------------------------------------------------------------------- + +function void_trans_tax_details($type, $type_no) +{ + $sql = "UPDATE ".TB_PREF."trans_tax_details SET amount=0, net_amount=0 + WHERE trans_no=$type_no + AND trans_type=$type"; + + db_query($sql, "The transaction tax details could not be voided"); +} + +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, + taxrec.rate, + ttype.id, + ttype.name + FROM ".TB_PREF."tax_types ttype, + ".TB_PREF."trans_tax_details taxrec + WHERE taxrec.tax_type_id=ttype.id + AND taxrec.trans_type != ".ST_CUSTDELIVERY." + AND taxrec.tran_date >= '$fromdate' + AND taxrec.tran_date <= '$todate' + GROUP BY ttype.id"; +//display_error($sql); + return db_query($sql,"Cannot retrieve tax summary"); +} + +//-------------------------------------------------------------------------------- +// Write/update journal entries. +// +function write_journal_entries(&$cart, $reverse) +{ + global $Refs; + + $date_ = $cart->tran_date; + $ref = $cart->reference; + $memo_ = $cart->memo_; + $new = $cart->order_id == 0; + $trans_type = $cart->trans_type; + $trans_id = $new ? get_next_trans_no($trans_type) : $cart->order_id; - foreach ($items as $journal_item) + begin_transaction(); + + if($new) { + $cart->order_id = $trans_id; + } else { + void_journal_trans($trans_type, $trans_id, true); + delete_comments($trans_type, $trans_id); + } + + foreach ($cart->gl_items as $journal_item) { + // 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, + 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); - if ($is_bank_to) + if ($is_bank_to) { - add_bank_trans($trans_type, $trans_id, $journal_item->code_id, $ref, - $date_, 3, $journal_item->amount, - 0, "", get_company_currency(), - "Cannot insert a destination bank transaction"); - } + add_bank_trans($trans_type, $trans_id, $is_bank_to, $ref, + $date_, $journal_item->amount, 0, "", get_company_currency(), + "Cannot insert a destination bank transaction"); + } + // 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_); } + + 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_); - add_comments($trans_type, $trans_id, $date_, $memo_); + add_audit_trail($trans_type, $trans_id, $date_); - references::save($trans_type, $trans_id, $ref); + commit_transaction(); - if ($reverse) + if ($reverse) { - - $reversingDate = date(user_date_display(), - Mktime(0,0,0,get_month($date_)+1,1,get_year($date_))); + //$reversingDate = date(user_date_display(), + // Mktime(0,0,0,get_month($date_)+1,1,get_year($date_))); + $reversingDate = begin_month(add_months($date_, 1)); $trans_id_reverse = get_next_trans_no($trans_type); - foreach ($items as $journal_item) + foreach ($cart->gl_items as $journal_item) { $is_bank_to = is_bank_account($journal_item->code_id); add_gl_trans($trans_type, $trans_id_reverse, $reversingDate, - $journal_item->code_id, $journal_item->dimension_id, $journal_item->dimension2_id, + $journal_item->code_id, $journal_item->dimension_id, $journal_item->dimension2_id, $journal_item->reference, -$journal_item->amount); - if ($is_bank_to) + if ($is_bank_to) { - add_bank_trans($trans_type, $trans_id_reverse, $journal_item->code_id, $ref, - $reversingDate, 3, $journal_item->amount, - 0, "", get_company_currency(), - "Cannot insert a destination bank transaction"); - } + add_bank_trans($trans_type, $trans_id_reverse, $is_bank_to, $ref, + $reversingDate, -$journal_item->amount, + 0, "", get_company_currency(), + "Cannot insert a destination bank transaction"); + } + // 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_); } add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_); - references::save($trans_type, $trans_id_reverse, $ref); + $Refs->save($trans_type, $trans_id_reverse, $ref); + add_audit_trail($trans_type, $trans_id_reverse, $reversingDate); } - commit_transaction(); - return $trans_id; } @@ -270,6 +464,17 @@ function void_gl_trans($type, $trans_id, $nested=false) commit_transaction(); } -//-------------------------------------------------------------------------------------------------- +//---------------------------------------------------------------------------------------- + +function void_journal_trans($type, $type_no) +{ + begin_transaction(); + + void_bank_trans($type, $type_no, true); +// void_gl_trans($type, $type_no, true); // this is done above +// void_trans_tax_details($type, $type_no); // ditto + + commit_transaction(); +} ?> \ No newline at end of file