Rerun. Changed query_size in users table to unsigned tinyint(1)

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

diff --git a/reporting/rep301.php b/reporting/rep301.php
index 4bd74be3e42bbfbdd1fe9135a35f47628024e88f..5ad1735cb31cb5d2630ca1a0207ce6b4d79a775f 100644 (file)
--- a/reporting/rep301.php
+++ b/reporting/rep301.php
@@ -1,12 +1,12 @@
 <?php
 /**********************************************************************
     Copyright (C) FrontAccounting, LLC.
 <?php
 /**********************************************************************
     Copyright (C) FrontAccounting, LLC.

-       Released under the terms of the GNU General Public License, GPL, 
-       as published by the Free Software Foundation, either version 3 
+       Released under the terms of the GNU General Public License, GPL,
+       as published by the Free Software Foundation, either version 3

        of the License, or (at your option) any later version.
     This program is distributed in the hope that it will be useful,
     but WITHOUT ANY WARRANTY; without even the implied warranty of
        of the License, or (at your option) any later version.
     This program is distributed in the hope that it will be useful,
     but WITHOUT ANY WARRANTY; without even the implied warranty of

-    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
+    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
 $page_security = 'SA_ITEMSVALREP';
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
 $page_security = 'SA_ITEMSVALREP';


@@ -28,12 +28,14 @@ include_once($path_to_root . "/inventory/includes/db/items_category_db.inc");
 
 print_inventory_valuation_report();
 
 
 print_inventory_valuation_report();
 

-function getTransactions($category, $location)
+function getTransactions($category, $location, $date)

 {
 {

+       $date = date2sql($date);

        $sql = "SELECT ".TB_PREF."stock_master.category_id,
                        ".TB_PREF."stock_category.description AS cat_description,
                        ".TB_PREF."stock_master.stock_id,
        $sql = "SELECT ".TB_PREF."stock_master.category_id,
                        ".TB_PREF."stock_category.description AS cat_description,
                        ".TB_PREF."stock_master.stock_id,

-                       ".TB_PREF."stock_master.description,
+                       ".TB_PREF."stock_master.units,
+                       ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive,

                        ".TB_PREF."stock_moves.loc_code,
                        SUM(".TB_PREF."stock_moves.qty) AS QtyOnHand,
                        ".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS UnitCost,
                        ".TB_PREF."stock_moves.loc_code,
                        SUM(".TB_PREF."stock_moves.qty) AS QtyOnHand,
                        ".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS UnitCost,


@@ -43,6 +45,8 @@ function getTransactions($category, $location)
                        ".TB_PREF."stock_moves
                WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id
                AND ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id
                        ".TB_PREF."stock_moves
                WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id
                AND ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id

+               AND ".TB_PREF."stock_master.mb_flag<>'D' 
+               AND ".TB_PREF."stock_moves.tran_date <= '$date'

                GROUP BY ".TB_PREF."stock_master.category_id,
                        ".TB_PREF."stock_category.description, ";
                if ($location != 'all')
                GROUP BY ".TB_PREF."stock_master.category_id,
                        ".TB_PREF."stock_category.description, ";
                if ($location != 'all')


@@ -67,11 +71,13 @@ function print_inventory_valuation_report()
 {
     global $path_to_root;
 
 {
     global $path_to_root;
 

-    $category = $_POST['PARAM_0'];
-    $location = $_POST['PARAM_1'];
-    $detail = $_POST['PARAM_2'];
-    $comments = $_POST['PARAM_3'];
-       $destination = $_POST['PARAM_4'];
+       $date = $_POST['PARAM_0'];
+    $category = $_POST['PARAM_1'];
+    $location = $_POST['PARAM_2'];
+    $detail = $_POST['PARAM_3'];
+    $comments = $_POST['PARAM_4'];
+       $orientation = $_POST['PARAM_5'];
+       $destination = $_POST['PARAM_6'];

        if ($destination)
                include_once($path_to_root . "/reporting/includes/excel_report.inc");
        else
        if ($destination)
                include_once($path_to_root . "/reporting/includes/excel_report.inc");
        else


@@ -79,6 +85,7 @@ function print_inventory_valuation_report()
        $detail = !$detail;
     $dec = user_price_dec();
 
        $detail = !$detail;
     $dec = user_price_dec();
 

+       $orientation = ($orientation ? 'L' : 'P');

        if ($category == ALL_NUMERIC)
                $category = 0;
        if ($category == 0)
        if ($category == ALL_NUMERIC)
                $category = 0;
        if ($category == 0)


@@ -91,25 +98,27 @@ function print_inventory_valuation_report()
        if ($location == 'all')
                $loc = _('All');
        else
        if ($location == 'all')
                $loc = _('All');
        else

-               $loc = $location;
+               $loc = get_location_name($location);

 
 

-       $cols = array(0, 100, 250, 350, 450,    515);
+       $cols = array(0, 75, 225, 250, 350, 450,        515);

 
 

-       $headers = array(_('Category'), '', _('Quantity'), _('Unit Cost'), _('Value'));
+       $headers = array(_('Category'), '', _('UOM'), _('Quantity'), _('Unit Cost'), _('Value'));

 
 

-       $aligns = array('left', 'left', 'right', 'right', 'right');
+       $aligns = array('left', 'left', 'left', 'right', 'right', 'right');

 
     $params =   array(         0 => $comments,
 
     $params =   array(         0 => $comments,

-                                   1 => array('text' => _('Category'), 'from' => $cat, 'to' => ''),
-                                   2 => array('text' => _('Location'), 'from' => $loc, 'to' => ''));
-
-    $rep = new FrontReport(_('Inventory Valuation Report'), "InventoryValReport", user_pagesize());
+                                       1 => array('text' => _('End Date'), 'from' => $date,            'to' => ''),
+                                   2 => array('text' => _('Category'), 'from' => $cat, 'to' => ''),
+                                   3 => array('text' => _('Location'), 'from' => $loc, 'to' => ''));

 
 

+    $rep = new FrontReport(_('Inventory Valuation Report'), "InventoryValReport", user_pagesize(), 9, $orientation);
+    if ($orientation == 'L')
+       recalculate_cols($cols);

     $rep->Font();
     $rep->Info($params, $cols, $headers, $aligns);
     $rep->NewPage();
 
     $rep->Font();
     $rep->Info($params, $cols, $headers, $aligns);
     $rep->NewPage();
 

-       $res = getTransactions($category, $location);
+       $res = getTransactions($category, $location, $date);

        $total = $grandtotal = 0.0;
        $catt = '';
        while ($trans=db_fetch($res))
        $total = $grandtotal = 0.0;
        $catt = '';
        while ($trans=db_fetch($res))


@@ -123,7 +132,7 @@ function print_inventory_valuation_report()
                                        $rep->NewLine(2, 3);
                                        $rep->TextCol(0, 4, _('Total'));
                                }
                                        $rep->NewLine(2, 3);
                                        $rep->TextCol(0, 4, _('Total'));
                                }

-                               $rep->AmountCol(4, 5, $total, $dec);
+                               $rep->AmountCol(5, 6, $total, $dec);

                                if ($detail)
                                {
                                        $rep->Line($rep->row - 2);
                                if ($detail)
                                {
                                        $rep->Line($rep->row - 2);


@@ -143,10 +152,13 @@ function print_inventory_valuation_report()
                        $rep->NewLine();
                        $rep->fontSize -= 2;
                        $rep->TextCol(0, 1, $trans['stock_id']);
                        $rep->NewLine();
                        $rep->fontSize -= 2;
                        $rep->TextCol(0, 1, $trans['stock_id']);

-                       $rep->TextCol(1, 2, $trans['description']);
-                       $rep->AmountCol(2, 3, $trans['QtyOnHand'], get_qty_dec($trans['stock_id']));
-                       $rep->AmountCol(3, 4, $trans['UnitCost'], $dec);
-                       $rep->AmountCol(4, 5, $trans['ItemTotal'], $dec);
+                       $rep->TextCol(1, 2, $trans['description'].($trans['inactive']==1 ? " ("._("Inactive").")" : ""), -1);
+                       $rep->TextCol(2, 3, $trans['units']);
+                       $rep->AmountCol(3, 4, $trans['QtyOnHand'], get_qty_dec($trans['stock_id']));
+                       $dec2 = 0;
+                       price_decimal_format($trans['UnitCost'], $dec2);
+                       $rep->AmountCol(4, 5, $trans['UnitCost'], $dec2);
+                       $rep->AmountCol(5, 6, $trans['ItemTotal'], $dec);

                        $rep->fontSize += 2;
                }
                $total += $trans['ItemTotal'];
                        $rep->fontSize += 2;
                }
                $total += $trans['ItemTotal'];


@@ -157,7 +169,7 @@ function print_inventory_valuation_report()
                $rep->NewLine(2, 3);
                $rep->TextCol(0, 4, _('Total'));
        }
                $rep->NewLine(2, 3);
                $rep->TextCol(0, 4, _('Total'));
        }

-       $rep->Amountcol(4, 5, $total, $dec);
+       $rep->Amountcol(5, 6, $total, $dec);

        if ($detail)
        {
                $rep->Line($rep->row - 2);
        if ($detail)
        {
                $rep->Line($rep->row - 2);


@@ -165,7 +177,7 @@ function print_inventory_valuation_report()
        }
        $rep->NewLine(2, 1);
        $rep->TextCol(0, 4, _('Grand Total'));
        }
        $rep->NewLine(2, 1);
        $rep->TextCol(0, 4, _('Grand Total'));

-       $rep->AmountCol(4, 5, $grandtotal, $dec);
+       $rep->AmountCol(5, 6, $grandtotal, $dec);

        $rep->Line($rep->row  - 4);
        $rep->NewLine();
     $rep->End();
        $rep->Line($rep->row  - 4);
        $rep->NewLine();
     $rep->End();