Customer/Supplier Balances Report | Allocated column not showing correctly. Fixed.

[fa-stable.git] / reporting / rep201.php

diff --git a/reporting/rep201.php b/reporting/rep201.php
index d0b886bf20a3758a6db9cc0af394ea1a4d53dcef..0e7f15b49ef793d80f716a98751585f52da3c4d7 100644 (file)
--- a/reporting/rep201.php
+++ b/reporting/rep201.php
@@ -31,17 +31,17 @@ function get_open_balance($supplier_id, $to)
 {
        $to = date2sql($to);
 
-    $sql = "SELECT SUM(IF(".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type = ".ST_BANKDEPOSIT.", 
-       (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS charges,
-       SUM(IF(".TB_PREF."supp_trans.type <> ".ST_SUPPINVOICE." AND ".TB_PREF."supp_trans.type <> ".ST_BANKDEPOSIT.", 
-       (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS credits,
-               SUM(".TB_PREF."supp_trans.alloc) AS Allocated,
-               SUM(IF(".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type = ".ST_BANKDEPOSIT.",
-               (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ".TB_PREF."supp_trans.alloc),
-               (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount + ".TB_PREF."supp_trans.alloc))) AS OutStanding
-               FROM ".TB_PREF."supp_trans
-       WHERE ".TB_PREF."supp_trans.tran_date < '$to'
-               AND ".TB_PREF."supp_trans.supplier_id = '$supplier_id' GROUP BY supplier_id";
+    $sql = "SELECT SUM(IF(t.type = ".ST_SUPPINVOICE." OR t.type = ".ST_BANKDEPOSIT.", 
+       (t.ov_amount + t.ov_gst + t.ov_discount), 0)) AS charges,
+       SUM(IF(t.type <> ".ST_SUPPINVOICE." AND t.type <> ".ST_BANKDEPOSIT.", 
+       (t.ov_amount + t.ov_gst + t.ov_discount), 0)) AS credits,
+               SUM(IF(t.type <> ".ST_SUPPINVOICE." AND t.type <> ".ST_BANKDEPOSIT.", t.alloc * -1, t.alloc)) AS Allocated,
+               SUM(IF(t.type = ".ST_SUPPINVOICE." OR t.type = ".ST_BANKDEPOSIT.",
+               (t.ov_amount + t.ov_gst + t.ov_discount - t.alloc),
+               (t.ov_amount + t.ov_gst + t.ov_discount + t.alloc))) AS OutStanding
+               FROM ".TB_PREF."supp_trans t
+       WHERE t.tran_date < '$to'
+               AND t.supplier_id = '$supplier_id' GROUP BY supplier_id";
 
     $result = db_query($sql,"No transactions were returned");
     return db_fetch($result);
@@ -52,15 +52,14 @@ function getTransactions($supplier_id, $from, $to)
        $from = date2sql($from);
        $to = date2sql($to);
 
-    $sql = "SELECT ".TB_PREF."supp_trans.*,
-                               (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)
-                               AS TotalAmount, ".TB_PREF."supp_trans.alloc AS Allocated,
-                               ((".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE.")
-                                       AND ".TB_PREF."supp_trans.due_date < '$to') AS OverDue
-                       FROM ".TB_PREF."supp_trans
-                       WHERE ".TB_PREF."supp_trans.tran_date >= '$from' AND ".TB_PREF."supp_trans.tran_date <= '$to' 
-                       AND ".TB_PREF."supp_trans.supplier_id = '$supplier_id' AND ".TB_PREF."supp_trans.ov_amount!=0
-                               ORDER BY ".TB_PREF."supp_trans.tran_date";
+    $sql = "SELECT *,
+                               (t.ov_amount + t.ov_gst + t.ov_discount)
+                               AS TotalAmount, IF(t.type <> ".ST_SUPPINVOICE." AND t.type <> ".ST_BANKDEPOSIT.", t.alloc * -1, t.alloc) AS Allocated,
+                               ((t.type = ".ST_SUPPINVOICE.") AND t.due_date < '$to') AS OverDue
+                       FROM ".TB_PREF."supp_trans t
+                       WHERE t.tran_date >= '$from' AND t.tran_date <= '$to' 
+                       AND t.supplier_id = '$supplier_id' AND t.ov_amount <> 0
+                               ORDER BY t.tran_date";
 
     $TransResult = db_query($sql,"No transactions were returned");
 
@@ -142,24 +141,19 @@ function print_supplier_balances()
                if (!$convert && $currency != $myrow['curr_code'])
                        continue;
                $accumulate = 0;
+               $rate = $convert ? get_exchange_rate_from_home_currency($myrow['curr_code'], Today()) : 1;
                $bal = get_open_balance($myrow['supplier_id'], $from);
                $init[0] = $init[1] = 0.0;
-               $init[0] = round2(abs($bal['charges']), $dec);
-               $init[1] = round2(Abs($bal['credits']), $dec);
-               $init[2] = round2($bal['Allocated'], $dec);
+               $init[0] = round2(abs($bal['charges']*$rate), $dec);
+               $init[1] = round2(Abs($bal['credits']*$rate), $dec);
+               $init[2] = round2($bal['Allocated']*$rate, $dec);
                if ($show_balance)
                {
                        $init[3] = $init[0] - $init[1];
                        $accumulate += $init[3];
                }       
                else    
-                       $init[3] = round2($bal['OutStanding'], $dec);
-               $total = array(0,0,0,0);
-               for ($i = 0; $i < 4; $i++)
-               {
-                       $total[$i] += $init[$i];
-                       $grandtotal[$i] += $init[$i];
-               }
+                       $init[3] = round2($bal['OutStanding']*$rate, $dec);
                $res = getTransactions($myrow['supplier_id'], $from, $to);
                if ($no_zeros && db_num_rows($res) == 0) continue;
 
@@ -172,15 +166,21 @@ function print_supplier_balances()
                $rep->AmountCol(5, 6, $init[1], $dec);
                $rep->AmountCol(6, 7, $init[2], $dec);
                $rep->AmountCol(7, 8, $init[3], $dec);
+               $total = array(0,0,0,0);
+               for ($i = 0; $i < 4; $i++)
+               {
+                       $total[$i] += $init[$i];
+                       $grandtotal[$i] += $init[$i];
+               }
                $rep->NewLine(1, 2);
-               if (db_num_rows($res)==0) continue;
-
                $rep->Line($rep->row + 4);
+               if (db_num_rows($res)==0) {
+                       $rep->NewLine(1, 2);
+                       continue;
+               }       
                while ($trans=db_fetch($res))
                {
                        if ($no_zeros && floatcmp(abs($trans['TotalAmount']), $trans['Allocated']) == 0) continue;
-                       $rate = $convert ? get_exchange_rate_to_home_currency($myrow['curr_code'], Today()) : 1;
-
                        $rep->NewLine(1, 2);
                        $rep->TextCol(0, 1, $systypes_array[$trans['type']]);
                        $rep->TextCol(1, 2,     $trans['reference']);
@@ -205,7 +205,8 @@ function print_supplier_balances()
                        if ($trans['TotalAmount'] > 0.0)
                                $item[3] = $item[0] - $item[2];
                        else    
-                               $item[3] = ($item[1] - $item[2]) * -1;
+                               //$item[3] = ($item[1] - $item[2]) * -1;
+                               $item[3] = -$item[1] - $item[2];
                        if ($show_balance)      
                                $rep->AmountCol(7, 8, $accumulate, $dec);
                        else