Deleting a fiscal year problem. Fixed.

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

diff --git a/admin/db/fiscalyears_db.inc b/admin/db/fiscalyears_db.inc
index 9051e47547faa3e49f84f1e0754b59dbb39605f7..de87036608c1c2b7a14ba060f2ccc12fb25bbb43 100644 (file)
--- 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 <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
+require_once($path_to_root. "/includes/ui/items_cart.inc");
 
 // fiscal year routines
 function add_fiscalyear($from_date, $to_date, $closed)
@@ -262,7 +263,9 @@ 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))
        {
@@ -287,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))
        {
@@ -300,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']}";
@@ -321,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']}'";
@@ -341,7 +345,7 @@ 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");
+       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");
@@ -349,6 +353,7 @@ function delete_this_fiscalyear($selected_id)
 
        $last_account='';
        $new = false;
+       $total = 0.0;
        while ($row = db_fetch($result))
        {
                if ($last_account != $row['account']) // deletes all subledgers postings, so do it once for account
@@ -363,6 +368,8 @@ function delete_this_fiscalyear($selected_id)
                                (".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;
                }
        }
@@ -372,6 +379,7 @@ function delete_this_fiscalyear($selected_id)
                $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_);