X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=admin%2Fdb%2Ffiscalyears_db.inc;h=de87036608c1c2b7a14ba060f2ccc12fb25bbb43;hb=d6ba8c34ca46258a5ed424dfca43c4c8841ea8c4;hp=4dc32b6952de79e35ceb4e50a7f8d72336a16e44;hpb=d23b97ed26bc0608c9ba1037b66058c709e83765;p=fa-stable.git diff --git a/admin/db/fiscalyears_db.inc b/admin/db/fiscalyears_db.inc index 4dc32b69..de870366 100644 --- a/admin/db/fiscalyears_db.inc +++ b/admin/db/fiscalyears_db.inc @@ -9,6 +9,7 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ +require_once($path_to_root. "/includes/ui/items_cart.inc"); // fiscal year routines function add_fiscalyear($from_date, $to_date, $closed) @@ -48,8 +49,6 @@ function get_fiscalyear($id) function get_current_fiscalyear() { - global $path_to_root; - include_once($path_to_root . "/admin/db/company_db.inc"); $year = get_company_pref('f_year'); $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($year); @@ -71,26 +70,48 @@ function delete_fiscalyear($id) commit_transaction(); } -function is_date_in_fiscalyears($date) +function is_date_in_fiscalyears($date, $closed=true) { $date = date2sql($date); $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE '$date' >= begin AND '$date' <= end"; - + if (!$closed) + $sql .= " AND closed=0"; $result = db_query($sql, "could not get all fiscal years"); return db_fetch($result) !== false; } -function is_bad_begin_date($date) +function get_fiscalyear_begin_for_date($date) { - $bdate = date2sql($date); - $sql = "SELECT MAX(end) FROM ".TB_PREF."fiscal_year WHERE begin < '$bdate'"; + $date = date2sql($date); + $sql = "SELECT begin FROM ".TB_PREF."fiscal_year WHERE '$date' >= begin AND '$date' <= end"; + $result = db_query($sql, "could not get begin date of the fiscal year"); + $row = db_fetch_row($result); + if ($row != false) + return sql2date($row[0]); + else + return begin_fiscalyear(); +} +function check_begin_end_date($date1, $date2) +{ + $sql = "SELECT MAX(end), MIN(begin) FROM ".TB_PREF."fiscal_year"; $result = db_query($sql, "could not retrieve last fiscal years"); $row = db_fetch_row($result); if ($row[0] === null) - return false; + return true; $max = add_days(sql2date($row[0]), 1); - return ($max !== $date); + $min = add_days(sql2date($row[1]), -1); + return ($max === $date1 || $min === $date2); +} + +function next_begin_date() +{ + $sql = "SELECT MAX(end) FROM ".TB_PREF."fiscal_year"; + $result = db_query($sql, "could not retrieve last fiscal years"); + $row = db_fetch_row($result); + if ($row[0] === null) + return false; + return add_days(sql2date($row[0]), 1); } function check_years_before($date, $closed=false) @@ -108,12 +129,20 @@ function check_years_before($date, $closed=false) //--------------------------------------------------------------------------------------------- function close_year($year) { + global $Refs; + $co = get_company_prefs(); if (get_gl_account($co['retained_earnings_act']) == false || get_gl_account($co['profit_loss_year_act']) == false) { display_error(_("The Retained Earnings Account or the Profit and Loss Year Account has not been set in System and General GL Setup")); return false; } + if (!is_account_balancesheet($co['retained_earnings_act']) || is_account_balancesheet($co['profit_loss_year_act'])) + { + display_error(_("The Retained Earnings Account should be a Balance Account or the Profit and Loss Year Account should be an Expense Account (preferred the last one in the Expense Class)")); + return false; + } + begin_transaction(); $myrow = get_fiscalyear($year); @@ -131,15 +160,15 @@ function close_year($year) if ($balance != 0.0) { - $trans_type = ST_JOURNAL; - $trans_id = get_next_trans_no($trans_type); - - add_gl_trans($trans_type, $trans_id, $to, $co['retained_earnings_act'], - 0, 0, _("Closing Year"), -$balance); - add_gl_trans($trans_type, $trans_id, $to, $co['profit_loss_year_act'], - 0, 0, _("Closing Year"), $balance); - - } + $cart = new items_cart(ST_JOURNAL); + $cart->reference = $Refs->get_next(ST_JOURNAL, null, $to); + $cart->tran_date = $cart->doc_date = $cart->event_date = $to; + + $cart->add_gl_item($co['retained_earnings_act'], 0, 0, -$balance, _("Closing Year"), '', 0); + $cart->add_gl_item($co['profit_loss_year_act'], 0, 0, $balance, _("Closing Year"), '', 0); + $cart->memo_ = _("Closing Year"); + write_journal_entries($cart); + } close_transactions($to); commit_transaction(); @@ -152,7 +181,7 @@ function open_year($year) $from = sql2date($myrow['begin']); begin_transaction(); - open_transactions($from); + close_transactions(add_days($from, -1)); commit_transaction(); } @@ -162,11 +191,16 @@ function delete_attachments_and_comments($type_no, $trans_no) $sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no = $type_no AND trans_no = $trans_no"; $result = db_query($sql, "Could not retrieve attachments"); + $delflag = false; while ($row = db_fetch($result)) { + $delflag = true; $dir = company_path(). "/attachments"; if (file_exists($dir."/".$row['unique_name'])) unlink($dir."/".$row['unique_name']); + } + if ($delflag) + { $sql = "DELETE FROM ".TB_PREF."attachments WHERE type_no = $type_no AND trans_no = $trans_no"; db_query($sql, "Could not delete attachment"); } @@ -181,7 +215,7 @@ function delete_this_fiscalyear($selected_id) { global $db_connections; - db_backup($db_connections[$_SESSION["wa_current_user"]->company], 'Security backup before Fiscal Year Removal'); + db_backup($db_connections[user_company()], 'Security backup before Fiscal Year Removal'); begin_transaction(); $ref = _("Open Balance"); $myrow = get_fiscalyear($selected_id); @@ -229,13 +263,15 @@ function delete_this_fiscalyear($selected_id) delete_attachments_and_comments(25, $row['id']); } $sql = "SELECT trans_no, type FROM ".TB_PREF."debtor_trans WHERE tran_date <= '$to' AND - (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) = alloc"; + (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) = + (SELECT SUM(amt) FROM ".TB_PREF."cust_allocations WHERE date_alloc <= '$to' AND trans_no = trans_no_to AND + type = trans_type_to)"; $result = db_query($sql, "Could not retrieve debtor trans"); while ($row = db_fetch($result)) { if ($row['type'] == ST_SALESINVOICE) { - $deliveries = get_parent_trans(ST_SALESINVOICE,$row['trans_no']); + $deliveries = get_sales_parent_numbers($row['type'], $row['trans_no']); foreach ($deliveries as $delivery) { $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = $delivery AND debtor_trans_type = ".ST_CUSTDELIVERY; @@ -254,7 +290,7 @@ function delete_this_fiscalyear($selected_id) delete_attachments_and_comments($row['type'], $row['trans_no']); } $sql = "SELECT trans_no, type FROM ".TB_PREF."supp_trans WHERE tran_date <= '$to' AND - ABS(ov_amount + ov_gst + ov_discount) = alloc"; + ABS(ov_amount + ov_gst + ov_discount) = (SELECT ABS(SUM(amt)) FROM ".TB_PREF."supp_allocations WHERE date_alloc <= '$to' AND trans_no = trans_no_to AND type = trans_type_to)"; $result = db_query($sql, "Could not retrieve supp trans"); while ($row = db_fetch($result)) { @@ -267,19 +303,19 @@ function delete_this_fiscalyear($selected_id) delete_attachments_and_comments($row['type'], $row['trans_no']); } $sql = "SELECT id FROM ".TB_PREF."workorders WHERE released_date <= '$to' AND closed=1"; - $result = db_query($sql, "Could not retrieve supp trans"); + $result = db_query($sql, "Could not retrieve workorders"); while ($row = db_fetch($result)) { $sql = "SELECT issue_no FROM ".TB_PREF."wo_issues WHERE workorder_id = {$row['id']}"; $res = db_query($sql, "Could not retrieve wo issues"); - while ($row2 = db_fetch_row($res)) - { - $sql = "DELETE FROM ".TB_PREF."wo_issue_items WHERE issue_id = {$row2[0]}"; - db_query($sql, "Could not delete wo issue items"); - } - delete_attachments_and_comments(ST_MANUISSUE, $row['id']); - $sql = "DELETE FROM ".TB_PREF."wo_issues WHERE workorder_id = {$row['id']}"; - db_query($sql, "Could not delete wo issues"); + while ($row2 = db_fetch_row($res)) + { + $sql = "DELETE FROM ".TB_PREF."wo_issue_items WHERE issue_id = {$row2[0]}"; + db_query($sql, "Could not delete wo issue items"); + $sql = "DELETE FROM ".TB_PREF."wo_issues WHERE workorder_id = {$row2[0]}"; + db_query($sql, "Could not delete wo issues"); + delete_attachments_and_comments(ST_MANUISSUE, $row2[0]); + } $sql = "DELETE FROM ".TB_PREF."wo_manufacture WHERE workorder_id = {$row['id']}"; db_query($sql, "Could not delete wo manufacture"); $sql = "DELETE FROM ".TB_PREF."wo_requirements WHERE workorder_id = {$row['id']}"; @@ -288,9 +324,10 @@ function delete_this_fiscalyear($selected_id) db_query($sql, "Could not delete workorders"); delete_attachments_and_comments(ST_WORKORDER, $row['id']); } - $sql = "SELECT loc_code, stock_id, SUM(qty) AS qty, SUM(qty*standard_cost) AS std_cost FROM ".TB_PREF."stock_moves WHERE tran_date <= '$to' GROUP by - loc_code, stock_id"; - $result = db_query($sql, "Could not retrieve supp trans"); + $sql = "SELECT loc_code, stock_id, SUM(qty) AS qty," + ." SUM(-qty*IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost)) AS std_cost" + ." FROM ".TB_PREF."stock_moves WHERE tran_date <= '$to' GROUP by loc_code, stock_id"; + $result = db_query($sql, "Could not retrieve stock moves"); while ($row = db_fetch($result)) { $sql = "DELETE FROM ".TB_PREF."stock_moves WHERE tran_date <= '$to' AND loc_code = '{$row['loc_code']}' AND stock_id = '{$row['stock_id']}'"; @@ -308,23 +345,45 @@ function delete_this_fiscalyear($selected_id) $sql = "DELETE FROM ".TB_PREF."exchange_rates WHERE date_ <= '$to'"; db_query($sql, "Could not delete exchange rates"); $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE tran_date <= '$to'"; - db_query($sql, "Could not delete exchange rates"); - - $sql = "SELECT account, SUM(amount) AS amount FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' GROUP by account"; + db_query($sql, "Could not delete budget trans"); + $sql = "SELECT account, SUM(amount) AS amount, person_type_id, person_id FROM " + .TB_PREF."gl_trans WHERE tran_date <= '$to' GROUP by account, person_type_id, person_id"; $result = db_query($sql, "Could not retrieve gl trans"); + $trans_no = get_next_trans_no(ST_JOURNAL); + + $last_account=''; + $new = false; + $total = 0.0; while ($row = db_fetch($result)) { - $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' AND account = '{$row['account']}'"; - db_query($sql, "Could not delete gl trans"); - if (is_account_balancesheet($row['account'])) + if ($last_account != $row['account']) // deletes all subledgers postings, so do it once for account + { + $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' AND account = '{$row['account']}'"; + db_query($sql, "Could not delete gl trans"); + $last_account = $row['account']; + } + if (is_account_balancesheet($row['account']) && $row['amount']) { - $trans_no = get_next_trans_no(ST_JOURNAL); - $sql = "INSERT INTO ".TB_PREF."gl_trans (type, type_no, tran_date, account, memo_, amount) VALUES - (".ST_JOURNAL.", $trans_no, '$to', '{$row['account']}', '$ref', {$row['amount']})"; + $sql = "INSERT INTO ".TB_PREF."gl_trans (type, type_no, tran_date, account, memo_, amount, person_type_id, person_id) VALUES + (".ST_JOURNAL.", $trans_no, '$to', '{$row['account']}', '$ref', {$row['amount']}, " + .db_escape($row['person_type_id'], true).", ".db_escape($row['person_id'], true).")"; db_query($sql, "Could not insert gl trans"); + if ($row['amount'] > 0.0) + $total += $row['amount']; + $new = true; } } - + if ($new) + { + global $Refs; + $trans_type = ST_JOURNAL; + $date_ = sql2date($to); + $reference = $Refs->get_next($trans_type, null, $date_); + $Refs->save($trans_type, $trans_no, $reference); + add_journal($trans_type, $trans_no, $total, $date_, get_company_currency(), $reference); + $Refs->save($trans_type, $trans_no, $reference); + add_audit_trail($trans_type, $trans_no, $date_); + } $sql = "SELECT bank_act, SUM(amount) AS amount FROM ".TB_PREF."bank_trans WHERE trans_date <= '$to' GROUP BY bank_act"; $result = db_query($sql, "Could not retrieve bank trans"); while ($row = db_fetch($result)) @@ -371,4 +430,3 @@ function delete_this_fiscalyear($selected_id) } -?> \ No newline at end of file