reporting/rep305.php

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 $page_security = 'SA_SUPPLIERANALYTIC';
  13 // ----------------------------------------------------------------
  14 // $ Revision:  2.0 $
  15 // Creator:     Joe Hunt
  16 // date_:       2005-05-19
  17 // Title:       GRN Valuation Report
  18 // ----------------------------------------------------------------
  19 $path_to_root="..";
  20
  21 include_once($path_to_root . "/includes/session.inc");
  22 include_once($path_to_root . "/includes/date_functions.inc");
  23 include_once($path_to_root . "/includes/data_checks.inc");
  24 include_once($path_to_root . "/includes/banking.inc");
  25 include_once($path_to_root . "/gl/includes/gl_db.inc");
  26 include_once($path_to_root . "/inventory/includes/db/items_category_db.inc");
  27
  28 //----------------------------------------------------------------------------------------------------
  29
  30 print_grn_valuation();
  31
  32 function getTransactions($from, $to)
  33 {
  34         $from = date2sql($from);
  35         $to = date2sql($to);
  36
  37         $sql = "SELECT grn.id batch_no,
  38                         grn.supplier_id,
  39             poline.*,
  40             item.description,
  41                         grn_line.qty_recd,
  42                         grn_line.quantity_inv,
  43                         grn_line.id grn_item_id
  44         FROM "
  45                 .TB_PREF."stock_master item,"
  46                 .TB_PREF."purch_order_details poline,"
  47                 .TB_PREF."grn_batch grn,"
  48                 .TB_PREF."grn_items grn_line
  49         WHERE item.stock_id=poline.item_code
  50         AND grn.purch_order_no=poline.order_no
  51                 AND grn.id = grn_line.grn_batch_id
  52                 AND grn_line.po_detail_item = poline.po_detail_item
  53         AND grn_line.qty_recd>0
  54         AND grn.delivery_date>='$from'
  55         AND grn.delivery_date<='$to'
  56         AND item.mb_flag <>'F'
  57         ORDER BY item.stock_id, grn.delivery_date";
  58
  59     return db_query($sql,"No transactions were returned");
  60
  61 }
  62
  63 function getSuppInvDetails($grn_item_id)
  64 {
  65         $sql = "SELECT
  66                         inv_line.supp_trans_no inv_no,
  67                         inv_line.quantity inv_qty,
  68                         inv.rate,
  69                         IF (inv.tax_included = 1, inv_line.unit_price - inv_line.unit_tax, inv_line.unit_price) inv_price
  70                         FROM "
  71                                 .TB_PREF."grn_items grn_line,"
  72                                 .TB_PREF."supp_trans inv,"
  73                                 .TB_PREF."supp_invoice_items inv_line
  74                         WHERE grn_line.id = inv_line.grn_item_id
  75                         AND grn_line.po_detail_item = inv_line.po_detail_item_id
  76                         AND grn_line.item_code = inv_line.stock_id
  77                         AND inv.type = inv_line.supp_trans_type
  78                         AND inv.trans_no = inv_line.supp_trans_no
  79                         AND inv_line.supp_trans_type = 20
  80                         AND inv_line.grn_item_id = ".$grn_item_id."
  81                         ORDER BY inv_line.id asc";
  82
  83         return db_query($sql,"No transactions were returned");
  84 }
  85
  86 //----------------------------------------------------------------------------------------------------
  87
  88 function print_grn_valuation()
  89 {
  90     global $path_to_root;
  91
  92         $from = $_POST['PARAM_0'];
  93         $to = $_POST['PARAM_1'];
  94         $comments = $_POST['PARAM_2'];
  95         $orientation = $_POST['PARAM_3'];
  96         $destination = $_POST['PARAM_4'];
  97         if ($destination)
  98                 include_once($path_to_root . "/reporting/includes/excel_report.inc");
  99         else
 100                 include_once($path_to_root . "/reporting/includes/pdf_report.inc");
 101
 102         $orientation = ($orientation ? 'L' : 'P');
 103         $dec = user_price_dec();
 104
 105         $cols = array(0, 75, 225, 260, 295, 330, 370, 410, 455, 515);
 106         $headers = array(_('Stock ID'), _('Description'), _('PO No'), _('GRN')."#", _('Inv')."#", _('Qty'), _('Inv Price'), _('PO Price'), _('Total'));
 107
 108         $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right', 'right');
 109
 110     $params =   array(  0 => $comments,
 111                                     1 => array('text' => _('Period'),'from' => $from, 'to' => $to));
 112
 113     $rep = new FrontReport(_('GRN Valuation Report'), "GRNValuationReport", user_pagesize(), 9, $orientation);
 114     if ($orientation == 'L')
 115         recalculate_cols($cols);
 116
 117     $rep->Font();
 118     $rep->Info($params, $cols, $headers, $aligns);
 119     $rep->NewPage();
 120
 121         $res = getTransactions($from, $to);
 122         $total = $qtotal = $grandtotal = 0.0;
 123         $stock_id = '';
 124         while ($trans=db_fetch($res))
 125         {
 126                 if ($stock_id != $trans['item_code'])
 127                 {
 128                         if ($stock_id != '')
 129                         {
 130                                 $rep->Line($rep->row  - 4);
 131                                 $rep->NewLine(2);
 132                                 $rep->TextCol(0, 3, _('Total'));
 133                                 $rep->AmountCol(5, 6, $qtotal, $qdec);
 134                                 $rep->AmountCol(8, 9, $total, $dec);
 135                                 $rep->NewLine();
 136                                 $total = $qtotal = 0;
 137                         }
 138                         $stock_id = $trans['item_code'];
 139                 }
 140
 141                 $rep->NewLine();
 142                 $rep->TextCol(0, 1, $trans['item_code']);
 143                 $rep->TextCol(1, 2, $trans['description']);
 144                 $rep->TextCol(2, 3, $trans['order_no']);
 145                 $qdec = get_qty_dec($trans['item_code']);
 146                 $rep->TextCol(3, 4, $trans['batch_no']);
 147
 148                 if ($trans['quantity_inv'])
 149                 {
 150                         $suppinv = getSuppInvDetails($trans['grn_item_id']);
 151                         while ($inv=db_fetch($suppinv))
 152                         {
 153                                 $inv['inv_price'] *= $inv['rate'];
 154                                 $rep->TextCol(4, 5, $inv['inv_no']);
 155                                 $rep->AmountCol(5, 6, $inv['inv_qty'], $qdec);
 156                                 $rep->AmountCol(6, 7, $inv['inv_price'], $dec);
 157                                 $rep->AmountCol(7, 8, $trans['std_cost_unit'], $dec);
 158                                 $amt = round2($inv['inv_qty'] * $inv['inv_price'], $dec);
 159                                 $rep->AmountCol(8, 9, $amt, $dec);
 160                                 $rep->NewLine();
 161                                 $total += $amt;
 162                                 $qtotal += $inv['inv_qty'];
 163                                 $grandtotal += $amt;
 164                         }
 165                 }
 166
 167                 if ($trans['qty_recd'] - $trans['quantity_inv'] !=0 )
 168                 {
 169                         $curr = get_supplier_currency($trans['supplier_id']);
 170                         $rate = get_exchange_rate_from_home_currency($curr, sql2date($trans['delivery_date']));
 171                         $trans['unit_price'] *= $rate;
 172                         $rep->TextCol(4, 5, "--");
 173                         $rep->AmountCol(5, 6, $trans['qty_recd'] - $trans['quantity_inv'], $qdec);
 174                         $rep->AmountCol(7, 8, $trans['unit_price'], $dec);
 175                         $amt = round2(($trans['qty_recd'] - $trans['quantity_inv']) * $trans['unit_price'], $dec);
 176                         $rep->AmountCol(8, 9, $amt, $dec);
 177                         $total += $amt;
 178                         $qtotal += $trans['qty_recd'] - $trans['quantity_inv'];
 179                         $grandtotal += $amt;
 180                 }
 181                 else
 182                         $rep->NewLine(-1);
 183         }
 184         if ($stock_id != '')
 185         {
 186                 $rep->Line($rep->row  - 4);
 187                 $rep->NewLine(2);
 188                 $rep->TextCol(0, 3, _('Total'));
 189                 $rep->AmountCol(5, 6, $qtotal, $qdec);
 190                 $rep->AmountCol(8, 9, $total, $dec);
 191                 $rep->Line($rep->row  - 4);
 192                 $rep->NewLine(2);
 193                 $rep->TextCol(0, 7, _('Grand Total'));
 194                 $rep->AmountCol(8, 9, $grandtotal, $dec);
 195         }
 196
 197         $rep->Line($rep->row  - 4);
 198         $rep->NewLine();
 199     $rep->End();
 200 }
 201