0001503: Request - fee account included in bank account table

[fa-stable.git] / admin / db / fiscalyears_db.inc

diff --git a/admin/db/fiscalyears_db.inc b/admin/db/fiscalyears_db.inc
index 4dc32b6952de79e35ceb4e50a7f8d72336a16e44..9ace5d195ee0b3c684a1f1a811dae7cb56803ed1 100644 (file)
--- a/admin/db/fiscalyears_db.inc
+++ b/admin/db/fiscalyears_db.inc
@@ -71,26 +71,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)
@@ -114,6 +136,12 @@ function close_year($year)
                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);
@@ -152,7 +180,7 @@ function open_year($year)
        $from = sql2date($myrow['begin']);
 
        begin_transaction();
-       open_transactions($from);
+       close_transactions(add_days($from, -1));
        commit_transaction();
 }
 
@@ -235,7 +263,7 @@ function delete_this_fiscalyear($selected_id)
        {
                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;
@@ -309,22 +337,40 @@ function delete_this_fiscalyear($selected_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(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 = "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))