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 ".TB_PREF."grn_batch.id batch_no,\r
  38                         ".TB_PREF."grn_batch.supplier_id, \r
  39             ".TB_PREF."purch_order_details.*,
  40             ".TB_PREF."stock_master.description,\r
  41                         ".TB_PREF."grn_items.qty_recd,\r
  42                         ".TB_PREF."grn_items.quantity_inv,\r
  43                         ".TB_PREF."grn_items.id grn_item_id\r
  44         FROM ".TB_PREF."stock_master,
  45             ".TB_PREF."purch_order_details,
  46             ".TB_PREF."grn_batch,\r
  47                         ".TB_PREF."grn_items \r
  48         WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."purch_order_details.item_code
  49         AND ".TB_PREF."grn_batch.purch_order_no=".TB_PREF."purch_order_details.order_no
  50                 AND ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id \r
  51                 AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."purch_order_details.po_detail_item\r
  52         AND ".TB_PREF."grn_items.qty_recd>0\r
  53         AND ".TB_PREF."grn_batch.delivery_date>='$from'
  54         AND ".TB_PREF."grn_batch.delivery_date<='$to'
  55         ORDER BY ".TB_PREF."stock_master.stock_id, ".TB_PREF."grn_batch.delivery_date";
  56
  57     return db_query($sql,"No transactions were returned");
  58
  59 }
  60
  61 function getSuppInvDetails($grn_item_id)\r
  62 {\r
  63         $sql = "SELECT\r
  64                         ".TB_PREF."supp_invoice_items.supp_trans_no inv_no,\r
  65                         ".TB_PREF."supp_invoice_items.quantity inv_qty,\r
  66                         ".TB_PREF."supp_invoice_items.unit_price inv_price\r
  67                         FROM ".TB_PREF."grn_items, ".TB_PREF."supp_invoice_items\r
  68                         WHERE ".TB_PREF."grn_items.id = ".TB_PREF."supp_invoice_items.grn_item_id\r
  69                         AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."supp_invoice_items.po_detail_item_id\r
  70                         AND ".TB_PREF."grn_items.item_code = ".TB_PREF."supp_invoice_items.stock_id\r
  71                         AND ".TB_PREF."supp_invoice_items.grn_item_id = ".$grn_item_id."\r
  72                         ORDER BY ".TB_PREF."supp_invoice_items.id asc";\r
  73 \r
  74         return db_query($sql,"No transactions were returned");\r
  75 }\r
  76 \r
  77 //----------------------------------------------------------------------------------------------------
  78
  79 function print_grn_valuation()
  80 {
  81     global $path_to_root;
  82
  83         $from = $_POST['PARAM_0'];
  84         $to = $_POST['PARAM_1'];
  85         $comments = $_POST['PARAM_2'];
  86         $destination = $_POST['PARAM_3'];
  87         if ($destination)
  88                 include_once($path_to_root . "/reporting/includes/excel_report.inc");
  89         else
  90                 include_once($path_to_root . "/reporting/includes/pdf_report.inc");
  91
  92     $dec = user_price_dec();
  93
  94         $cols = array(0, 75, 225, 260, 295, 330, 370, 410, 455, 515);\r
  95         $headers = array(_('Stock ID'), _('Description'), _('PO No'), _('GRN')."#", _('Inv')."#", _('Qty'), _('Inv Price'), _('PO Price'), _('Total'));\r
  96
  97         $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right', 'right');\r
  98
  99     $params =   array(  0 => $comments,
 100                                     1 => array('text' => _('Period'),'from' => $from, 'to' => $to));
 101
 102     $rep = new FrontReport(_('GRN Valuation Report'), "GRNValuationReport", user_pagesize());
 103
 104     $rep->Font();
 105     $rep->Info($params, $cols, $headers, $aligns);
 106     $rep->NewPage();
 107
 108         $res = getTransactions($from, $to);
 109         $total = $qtotal = $grandtotal = 0.0;
 110         $stock_id = '';
 111         while ($trans=db_fetch($res))
 112         {
 113                 if ($stock_id != $trans['item_code'])
 114                 {
 115                         if ($stock_id != '')
 116                         {
 117                                 $rep->Line($rep->row  - 4);
 118                                 $rep->NewLine(2);
 119                                 $rep->TextCol(0, 3, _('Total'));
 120                                 $rep->AmountCol(5, 6, $qtotal, $qdec);\r
 121                                 $rep->AmountCol(8, 9, $total, $dec);\r
 122                                 $rep->NewLine();
 123                                 $total = $qtotal = 0;
 124                         }
 125                         $stock_id = $trans['item_code'];
 126                 }
 127                 $curr = get_supplier_currency($trans['supplier_id']);
 128                 $rate = get_exchange_rate_from_home_currency($curr, sql2date($trans['delivery_date']));
 129                 $trans['unit_price'] *= $rate;
 130                 $trans['act_price'] *= $rate;
 131
 132                 $rep->NewLine();
 133                 $rep->TextCol(0, 1, $trans['item_code']);
 134                 $rep->TextCol(1, 2, $trans['description']);\r
 135                 $rep->TextCol(2, 3, $trans['order_no']);
 136                 $qdec = get_qty_dec($trans['item_code']);
 137                 $rep->TextCol(3, 4, $trans['batch_no']);\r
 138                 \r
 139                 if ($trans['quantity_inv'])\r
 140                 {\r
 141                         $suppinv = getSuppInvDetails($trans['grn_item_id']);\r
 142                         while ($inv=db_fetch($suppinv))\r
 143                         {       \r
 144                                 $inv['inv_price'] *= $rate;\r
 145                                 $rep->TextCol(4, 5, $inv['inv_no']);\r
 146                                 $rep->AmountCol(5, 6, $inv['inv_qty'], $qdec);\r
 147                                 $rep->AmountCol(6, 7, $inv['inv_price'], $dec);\r
 148                                 $rep->AmountCol(7, 8, $trans['unit_price'], $dec);\r
 149                                 $amt = round2($inv['inv_qty'] * $inv['inv_price'], $dec);\r
 150                                 $rep->AmountCol(8, 9, $amt, $dec);\r
 151                                 $rep->NewLine();\r
 152                                 $total += $amt;\r
 153                                 $qtotal += $inv['inv_qty'];\r
 154                                 $grandtotal += $amt;\r
 155                         }\r
 156                 }\r
 157         \r
 158                 if ($trans['qty_recd'] - $trans['quantity_inv'] !=0 )\r
 159                 {\r
 160                         $rep->TextCol(4, 5, "--");\r
 161                         $rep->AmountCol(5, 6, $trans['qty_recd'] - $trans['quantity_inv'], $qdec);\r
 162                         $rep->AmountCol(7, 8, $trans['unit_price'], $dec);\r
 163                         $amt = round2(($trans['qty_recd'] - $trans['quantity_inv']) * $trans['unit_price'], $dec);\r
 164                         $rep->AmountCol(8, 9, $amt, $dec);\r
 165                         $total += $amt;\r
 166                         $qtotal += $trans['qty_recd'] - $trans['quantity_inv'];\r
 167                         $grandtotal += $amt;\r
 168                 }\r
 169                 else\r
 170                         $rep->NewLine(-1);\r
 171         }
 172         if ($stock_id != '')
 173         {
 174                 $rep->Line($rep->row  - 4);
 175                 $rep->NewLine(2);
 176                 $rep->TextCol(0, 3, _('Total'));
 177                 $rep->AmountCol(5, 6, $qtotal, $qdec);\r
 178                 $rep->AmountCol(8, 9, $total, $dec);\r
 179                 $rep->Line($rep->row  - 4);
 180                 $rep->NewLine(2);
 181                 $rep->TextCol(0, 7, _('Grand Total'));\r
 182                 $rep->AmountCol(8, 9, $grandtotal, $dec);\r
 183         }
 184
 185         $rep->Line($rep->row  - 4);
 186         $rep->NewLine();
 187     $rep->End();
 188 }
 189
 190 ?>