X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=admin%2Ffiscalyears.php;h=e7a2ff7454ac754ef66276f63ae729f1639da0db;hb=80dd97a37f674cc3691fa04af4c29607067566b2;hp=3701369a1a5304dcc30d810f660624539915e037;hpb=da8311619dd73feae101d246a1957b972e00cbd2;p=fa-stable.git diff --git a/admin/fiscalyears.php b/admin/fiscalyears.php index 3701369a..e7a2ff74 100644 --- a/admin/fiscalyears.php +++ b/admin/fiscalyears.php @@ -1,109 +1,381 @@ . +***********************************************************************/ +$page_security = 'SA_FISCALYEARS'; +$path_to_root = ".."; include_once($path_to_root . "/includes/session.inc"); -page(_("Fiscal Years")); - include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/admin/db/company_db.inc"); include_once($path_to_root . "/includes/ui.inc"); - +include_once($path_to_root . "/sales/includes/db/cust_trans_db.inc"); +include_once($path_to_root . "/admin/db/maintenance_db.inc"); +$js = ""; +if ($use_date_picker) + $js .= get_js_date_picker(); +page(_("Fiscal Years"), false, false, "", $js); + +simple_page_mode(true); //--------------------------------------------------------------------------------------------- -if (isset($_GET['selected_id'])) +function is_date_in_fiscalyears($date) { - $selected_id = $_GET['selected_id']; -} -elseif (isset($_POST['selected_id'])) + $date = date2sql($date); + $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE '$date' >= begin AND '$date' <= end"; + + $result = db_query($sql, "could not get all fiscal years"); + return db_fetch($result) !== false; +} + +function is_bad_begin_date($date) { - $selected_id = $_POST['selected_id']; + $bdate = date2sql($date); + $sql = "SELECT MAX(end) FROM ".TB_PREF."fiscal_year WHERE begin < '$bdate'"; + + $result = db_query($sql, "could not retrieve last fiscal years"); + $row = db_fetch_row($result); + if ($row[0] === null) + return false; + $max = add_days(sql2date($row[0]), 1); + return ($max !== $date); } -else - $selected_id = null; -//--------------------------------------------------------------------------------------------- +function check_years_before($date, $closed=false) +{ + $date = date2sql($date); + $sql = "SELECT COUNT(*) FROM ".TB_PREF."fiscal_year WHERE begin < '$date'"; + if (!$closed) + $sql .= " AND closed=0"; + + $result = db_query($sql, "could not check fiscal years before"); + $row = db_fetch_row($result); + return ($row[0] > 0); +} function check_data() { - if (!isset($selected_id)) - $from = $_POST['from_date']; - else - $from = $selected_id; - if (!is_date($from)) + if (!is_date($_POST['from_date']) || is_date_in_fiscalyears($_POST['from_date']) || is_bad_begin_date($_POST['from_date'])) { display_error( _("Invalid BEGIN date in fiscal year.")); + set_focus('from_date'); return false; } - if (!is_date($_POST['to_date'])) + if (!is_date($_POST['to_date']) || is_date_in_fiscalyears($_POST['to_date'])) { display_error( _("Invalid END date in fiscal year.")); + set_focus('to_date'); return false; } - if (date1_greater_date2($from, $_POST['to_date'])) + if (date1_greater_date2($_POST['from_date'], $_POST['to_date'])) { display_error( _("BEGIN date bigger than END date.")); + set_focus('from_date'); return false; } return true; } - //--------------------------------------------------------------------------------------------- +function close_year($year) +{ + $co = get_company_prefs(); + if ($co['retained_earnings_act'] == '' || $co['profit_loss_year_act'] == '') + { + 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; + } + begin_transaction(); -function handle_submit() + $myrow = get_fiscalyear($year); + $to = $myrow['end']; + // retrieve total balances from balance sheet accounts + $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans INNER JOIN ".TB_PREF."chart_master ON account=account_code + INNER JOIN ".TB_PREF."chart_types ON account_type=id INNER JOIN ".TB_PREF."chart_class ON class_id=cid + WHERE ctype>=".CL_ASSETS." AND ctype <=".CL_EQUITY." AND tran_date <= '$to'"; + $result = db_query($sql, "The total balance could not be calculated"); + + $row = db_fetch_row($result); + $balance = round2($row[0], user_price_dec()); + + $to = sql2date($to); + + 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); + + } + close_transactions($to); + + commit_transaction(); + return true; +} + +function open_year($year) { - global $selected_id; + $myrow = get_fiscalyear($year); + $from = sql2date($myrow['begin']); - if (!check_data()) - return false; + begin_transaction(); + open_transactions($from); + commit_transaction(); +} - if (isset($selected_id)) +function handle_submit() +{ + global $selected_id, $Mode; + + $ok = true; + if ($selected_id != -1) { - update_fiscalyear($_POST['from_date'], $_POST['closed']); - } - else + if ($_POST['closed'] == 1) + { + if (check_years_before($_POST['from_date'], false)) + { + display_error( _("Cannot CLOSE this year because there are open fiscal years before")); + set_focus('closed'); + return false; + } + $ok = close_year($selected_id); + } + else + open_year($selected_id); + if ($ok) + { + update_fiscalyear($selected_id, $_POST['closed']); + display_notification(_('Selected fiscal year has been updated')); + } + } + else { + if (!check_data()) + return false; add_fiscalyear($_POST['from_date'], $_POST['to_date'], $_POST['closed']); + display_notification(_('New fiscal year has been added')); } - - return true; + $Mode = 'RESET'; } //--------------------------------------------------------------------------------------------- -function check_can_delete($todate) +function check_can_delete($selected_id) { - global $selected_id; - + $myrow = get_fiscalyear($selected_id); // PREVENT DELETES IF DEPENDENT RECORDS IN gl_trans - $from = date2sql($selected_id); - $to = date2sql($todate); - $sql= "SELECT COUNT(*) FROM ".TB_PREF."gl_trans WHERE tran_date >= '$from' AND tran_date <= '$to'"; - $result = db_query($sql, "could not query gl_trans master"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) - { - display_error(_("Cannot delete this fiscal year because items have been created referring to it.")); + if (check_years_before(sql2date($myrow['begin']), true)) + { + display_error(_("Cannot delete this fiscal year because thera are fiscal years before.")); + return false; + } + if ($myrow['closed'] == 0) + { + display_error(_("Cannot delete this fiscal year because the fiscal year is not closed.")); return false; } - return true; } //--------------------------------------------------------------------------------------------- +function delete_attachments_and_comments($type_no, $trans_no) +{ + global $comp_path; + + $sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no = $type_no AND trans_no = $trans_no"; + $result = db_query($sql, "Could not retrieve attachments"); + while ($row = db_fetch($result)) + { + $dir = $comp_path."/".user_company(). "/attachments"; + if (file_exists($dir."/".$row['unique_name'])) + unlink($dir."/".$row['unique_name']); + $sql = "DELETE FROM ".TB_PREF."attachments WHERE type_no = $type_no AND trans_no = $trans_no"; + db_query($sql, "Could not delete attachment"); + } + $sql = "DELETE FROM ".TB_PREF."comments WHERE type = $type_no AND id = $trans_no"; + db_query($sql, "Could not delete comments"); +} + +function delete_this_fiscalyear($selected_id) +{ + global $db_connections; + + db_backup($db_connections[$_SESSION["wa_current_user"]->company], 'Security backup before Fiscal Year Removal'); + begin_transaction(); + $ref = _("Open Balance"); + $myrow = get_fiscalyear($selected_id); + $to = $myrow['end']; + $sql = "SELECT order_no, trans_type FROM ".TB_PREF."sales_orders WHERE ord_date <= '$to' AND type <> 1"; // don't take the templates + $result = db_query($sql, "Could not retrieve sales orders"); + while ($row = db_fetch($result)) + { + $sql = "SELECT SUM(qty_sent), SUM(quantity) FROM ".TB_PREF."sales_order_details WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}"; + $res = db_query($sql, "Could not retrieve sales order details"); + $row2 = db_fetch_row($res); + if ($row2[0] == $row2[1]) + { + $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}"; + db_query($sql, "Could not delete sales order details"); + $sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}"; + db_query($sql, "Could not delete sales order"); + delete_attachments_and_comments($row['trans_type'], $row['order_no']); + } + } + $sql = "SELECT order_no FROM ".TB_PREF."purch_orders WHERE ord_date <= '$to'"; + $result = db_query($sql, "Could not retrieve purchase orders"); + while ($row = db_fetch($result)) + { + $sql = "SELECT SUM(quantity_ordered), SUM(quantity_received) FROM ".TB_PREF."purch_order_details WHERE order_no = {$row['order_no']}"; + $res = db_query($sql, "Could not retrieve purchase order details"); + $row2 = db_fetch_row($res); + if ($row2[0] == $row2[1]) + { + $sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE order_no = {$row['order_no']}"; + db_query($sql, "Could not delete purchase order details"); + $sql = "DELETE FROM ".TB_PREF."purch_orders WHERE order_no = {$row['order_no']}"; + db_query($sql, "Could not delete purchase order"); + delete_attachments_and_comments(ST_PURCHORDER, $row['order_no']); + } + } + $sql = "SELECT id FROM ".TB_PREF."grn_batch WHERE delivery_date <= '$to'"; + $result = db_query($sql, "Could not retrieve grn batch"); + while ($row = db_fetch($result)) + { + $sql = "DELETE FROM ".TB_PREF."grn_items WHERE grn_batch_id = {$row['id']}"; + db_query($sql, "Could not delete grn items"); + $sql = "DELETE FROM ".TB_PREF."grn_batch WHERE id = {$row['id']}"; + db_query($sql, "Could not delete grn batch"); + 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"; + $result = db_query($sql, "Could not retrieve debtor trans"); + while ($row = db_fetch($result)) + { + if ($row['type'] == 10) + { + $deliveries = get_parent_trans(10,$row['trans_no']); + foreach ($deliveries as $delivery) + { + $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = $delivery AND debtor_trans_type = 13"; + db_query($sql, "Could not delete debtor trans details"); + $sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE trans_no = $delivery AND type = 13"; + db_query($sql, "Could not delete debtor trans"); + } + } + $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE trans_no_from = {$row['trans_no']} AND type_no_from = {$row['type']}"; + db_query($sql, "Could not delete cust allocations"); + $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = {$row['trans_no']} AND debtor_trans_type = {$row['type']}"; + db_query($sql, "Could not delete debtor trans details"); + $sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE trans_no = {$row['trans_no']} AND type = {$row['type']}"; + db_query($sql, "Could not delete debtor trans"); + 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"; + $result = db_query($sql, "Could not retrieve supp trans"); + while ($row = db_fetch($result)) + { + $sql = "DELETE FROM ".TB_PREF."supp_allocations WHERE trans_no_from = {$row['trans_no']} AND type_no_from = {$row['type']}"; + db_query($sql, "Could not delete supp allocations"); + $sql = "DELETE FROM ".TB_PREF."supp_invoice_items WHERE supp_trans_no = {$row['trans_no']} AND supp_trans_type = {$row['type']}"; + db_query($sql, "Could not delete supp invoice items"); + $sql = "DELETE FROM ".TB_PREF."supp_trans WHERE trans_no = {$row['trans_no']} AND type = {$row['type']}"; + db_query($sql, "Could not delete supp trans"); + 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"); + 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(28, $row['id']); + $sql = "DELETE FROM ".TB_PREF."wo_issues WHERE workorder_id = {$row['id']}"; + db_query($sql, "Could not delete wo issues"); + $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']}"; + db_query($sql, "Could not delete wo requirements"); + $sql = "DELETE FROM ".TB_PREF."workorders WHERE id = {$row['id']}"; + db_query($sql, "Could not delete workorders"); + delete_attachments_and_comments(26, $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"); + 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']}'"; + db_query($sql, "Could not delete stock moves"); + $qty = $row['qty']; + $std_cost = ($qty == 0 ? 0 : round2($row['std_cost'] / $qty, user_price_dec())); + $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, loc_code, tran_date, reference, qty, standard_cost) VALUES + ('{$row['stock_id']}', '{$row['loc_code']}', '$to', '$ref', $qty, $std_cost)"; + db_query($sql, "Could not insert stock move"); + } + $sql = "DELETE FROM ".TB_PREF."voided WHERE date_ <= '$to'"; + db_query($sql, "Could not delete voided items"); + $sql = "DELETE FROM ".TB_PREF."trans_tax_details WHERE tran_date <= '$to'"; + db_query($sql, "Could not delete trans tax details"); + $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"; + $result = db_query($sql, "Could not retrieve gl trans"); + 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'])) + { + $trans_no = get_next_trans_no(0); + if (is_bank_account($row['account'])) + { + $sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE trans_date <= '$to' AND bank_act = '{$row['account']}'"; + $res = db_query($sql, "Could not retrieve bank trans"); + $row2 = db_fetch_row($res); + $sql = "DELETE FROM ".TB_PREF."bank_trans WHERE trans_date <= '$to' AND bank_act = '{$row['account']}'"; + db_query($sql, "Could not delete bank trans"); + $sql = "INSERT INTO ".TB_PREF."bank_trans (type, trans_no, trans_date, bank_act, ref, amount) VALUES + (0, $trans_no, '$to', '{$row['account']}', '$ref', {$row2[0]})"; + db_query($sql, "Could not insert bank trans"); + } + $sql = "INSERT INTO ".TB_PREF."gl_trans (type, type_no, tran_date, account, memo_, amount) VALUES + (0, $trans_no, '$to', '{$row['account']}', '$ref', {$row['amount']})"; + db_query($sql, "Could not insert gl trans"); + } + } + delete_fiscalyear($selected_id); + commit_transaction(); +} -function handle_delete($todate) +function handle_delete() { - global $selected_id; + global $selected_id, $Mode; - if (!check_can_delete($todate)) - return; + if (check_can_delete($selected_id)) { //only delete if used in neither customer or supplier, comp prefs, bank trans accounts - - delete_fiscalyear($selected_id); - - meta_forward($_SERVER['PHP_SELF']); + delete_this_fiscalyear($selected_id); + display_notification(_('Selected fiscal year has been deleted')); + } + $Mode = 'RESET'; } //--------------------------------------------------------------------------------------------- @@ -115,42 +387,52 @@ function display_fiscalyears() $company_year = get_company_pref('f_year'); $result = get_all_fiscalyears(); - + start_form(); + display_note(_("Warning: During fiscal year removal all transactions + are removed and converted into relevant balances. This process is irreversible!"), + 0, 0, "class='currentfg'"); start_table($table_style); $th = array(_("Fiscal Year Begin"), _("Fiscal Year End"), _("Closed"), "", ""); table_header($th); $k=0; - while ($myrow=db_fetch($result)) + while ($myrow=db_fetch($result)) { - if ($myrow['id'] == $company_year) + if ($myrow['id'] == $company_year) { start_row("class='stockmankobg'"); - } + } else alt_table_row_color($k); $from = sql2date($myrow["begin"]); $to = sql2date($myrow["end"]); - if ($myrow["closed"] == 0) + if ($myrow["closed"] == 0) { $closed_text = _("No"); - } - else + } + else { $closed_text = _("Yes"); } label_cell($from); label_cell($to); label_cell($closed_text); - edit_link_cell("selected_id=" . urlencode($from)); - if ($myrow["id"] != $company_year) - delete_link_cell("selected_id=" . urlencode($from) . "&to_date=" . urlencode($to) . "&delete=1"); + edit_button_cell("Edit".$myrow['id'], _("Edit")); + if ($myrow["id"] != $company_year) { + delete_button_cell("Delete".$myrow['id'], _("Delete")); + submit_js_confirm("Delete".$myrow['id'], + sprintf(_("Are you sure you want to remove fiscal year + '%s'-'%s'? All transactions are removed and converted into relevant + balances. Do you want to continue ?"), $from, $to)); + } else + label_cell(''); end_row(); } - end_table();; + end_table(); + end_form(); display_note(_("The marked fiscal year is the current fiscal year which cannot be deleted."), 0, 0, "class='currentfg'"); } @@ -158,61 +440,66 @@ function display_fiscalyears() function display_fiscalyear_edit($selected_id) { - global $table_style2; - + global $table_style2, $Mode; + start_form(); start_table($table_style2); - if ($selected_id) + if ($selected_id != -1) { - $myrow = get_fiscalyear($selected_id); + if($Mode =='Edit') + { + $myrow = get_fiscalyear($selected_id); - $_POST['from_date'] = sql2date($myrow["begin"]); - $_POST['to_date'] = sql2date($myrow["end"]); - $_POST['closed'] = $myrow["closed"]; - hidden('selected_id', $selected_id); - hidden('from_date', $_POST['from_date']); - hidden('to_date', $_POST['to_date']); + $_POST['from_date'] = sql2date($myrow["begin"]); + $_POST['to_date'] = sql2date($myrow["end"]); + $_POST['closed'] = $myrow["closed"]; + } + hidden('from_date'); + hidden('to_date'); label_row(_("Fiscal Year Begin:"), $_POST['from_date']); label_row(_("Fiscal Year End:"), $_POST['to_date']); - } - else + } + else { - text_row(_("Fiscal Year Begin:"), 'from_date', null, 15, 10); - text_row(_("Fiscal Year End:"), 'to_date', null, 15, 10); + date_row(_("Fiscal Year Begin:"), 'from_date', '', null, 0, 0, 1001); + date_row(_("Fiscal Year End:"), 'to_date', '', null, 0, 0, 1001); } + hidden('selected_id', $selected_id); yesno_list_row(_("Is Closed:"), 'closed', null, "", "", false); end_table(1); - submit_add_or_update_center(!isset($selected_id)); + submit_add_or_update_center($selected_id == -1, '', 'both'); end_form(); } //--------------------------------------------------------------------------------------------- -if (isset($_POST['ADD_ITEM']) || isset($_POST['UPDATE_ITEM'])) +if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') { - if (handle_submit()) - { - meta_forward($_SERVER['PHP_SELF']); - } + handle_submit(); } //--------------------------------------------------------------------------------------------- -if (isset($_GET['delete'])) +if ($Mode == 'Delete') { - handle_delete($_GET['to_date']); + global $selected_id; + handle_delete($selected_id); } +if ($Mode == 'RESET') +{ + $selected_id = -1; +} //--------------------------------------------------------------------------------------------- display_fiscalyears(); -hyperlink_no_params($_SERVER['PHP_SELF'], _("Enter a New Fiscal Year")); +echo '
'; display_fiscalyear_edit($selected_id);