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)
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);
$from = sql2date($myrow['begin']);
begin_transaction();
- open_transactions($from);
+ close_transactions(add_days($from, -1));
commit_transaction();
}
//---------------------------------------------------------------------------------------------
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";
+ $dir = company_path(). "/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";
}
$sql = "DELETE FROM ".TB_PREF."comments WHERE type = $type_no AND id = $trans_no";
db_query($sql, "Could not delete comments");
+ $sql = "DELETE FROM ".TB_PREF."refs WHERE type = $type_no AND id = $trans_no";
+ db_query($sql, "Could not delete refs");
}
+//---------------------------------------------------------------------------------------------
function delete_this_fiscalyear($selected_id)
{
global $db_connections;
{
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;
db_query($sql, "Could not delete debtor trans details");
$sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE trans_no = $delivery AND type = ".ST_CUSTDELIVERY;
db_query($sql, "Could not delete debtor trans");
+ delete_attachments_and_comments(ST_CUSTDELIVERY, $delivery);
}
}
- $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE trans_no_from = {$row['trans_no']} AND type_no_from = {$row['type']}";
+ $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE trans_no_from = {$row['trans_no']} AND trans_type_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");
$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']}";
+ $sql = "DELETE FROM ".TB_PREF."supp_allocations WHERE trans_no_from = {$row['trans_no']} AND trans_type_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."wo_issue_items WHERE issue_id = {$row2[0]}";
db_query($sql, "Could not delete wo issue items");
}
- delete_attachments_and_comments(28, $row['id']);
+ 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");
$sql = "DELETE FROM ".TB_PREF."wo_manufacture 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']);
+ 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";
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";
+ $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;
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
{
- $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']})";
+ $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'])
+ {
+ $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");
+ $new = true;
}
}
+ if ($new)
+ {
+ global $Refs;
+ $trans_type = ST_JOURNAL;
+ $reference = $Refs->get_next($trans_type);
+ $Refs->save($trans_type, $trans_no, $reference);
+ $date_ = sql2date($to);
+
+ 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))
+ {
+ $sql = "DELETE FROM ".TB_PREF."bank_trans WHERE trans_date <= '$to' AND bank_act = '{$row['bank_act']}'";
+ 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, 0, '$to', '{$row['bank_act']}', '$ref', {$row['amount']})";
+ db_query($sql, "Could not insert bank trans");
+ }
+
+ $sql = "DELETE FROM ".TB_PREF."audit_trail WHERE gl_date <= '$to'";
+ db_query($sql, "Could not delete audit trail");
+
+ $sql = "SELECT type, id FROM ".TB_PREF."comments WHERE type != ".ST_SALESQUOTE." AND type != ".ST_SALESORDER." AND type != ".ST_PURCHORDER;
+ $result = db_query($sql, "Could not retrieve comments");
+ while ($row = db_fetch($result))
+ {
+ $sql = "SELECT count(*) FROM ".TB_PREF."gl_trans WHERE type = {$row['type']} AND type_no = {$row['id']}";
+ $res = db_query($sql, "Could not retrieve gl_trans");
+ $row2 = db_fetch_row($res);
+ if ($row2[0] == 0) // if no link, then delete comments
+ {
+ $sql = "DELETE FROM ".TB_PREF."comments WHERE type = {$row['type']} AND id = {$row['id']}";
+ db_query($sql, "Could not delete comments");
+ }
+ }
+ $sql = "SELECT type, id FROM ".TB_PREF."refs WHERE type != ".ST_SALESQUOTE." AND type != ".ST_SALESORDER." AND type != ".ST_PURCHORDER;
+ $result = db_query($sql, "Could not retrieve refs");
+ while ($row = db_fetch($result))
+ {
+ $sql = "SELECT count(*) FROM ".TB_PREF."gl_trans WHERE type = {$row['type']} AND type_no = {$row['id']}";
+ $res = db_query($sql, "Could not retrieve gl_trans");
+ $row2 = db_fetch_row($res);
+ if ($row2[0] == 0) // if no link, then delete refs
+ {
+ $sql = "DELETE FROM ".TB_PREF."refs WHERE type = {$row['type']} AND id = {$row['id']}";
+ db_query($sql, "Could not delete refs");
+ }
+ }
+
delete_fiscalyear($selected_id);
commit_transaction();
}
+
?>
\ No newline at end of file
projects / fa-stable.git / blobdiff