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 // ----------------------------------------------------------------
17 // Title: GRN Valuation Report
18 // ----------------------------------------------------------------
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");
28 //----------------------------------------------------------------------------------------------------
30 print_grn_valuation();
32 function getTransactions($from, $to)
34 $from = date2sql($from);
37 $sql = "SELECT grn.id batch_no,
42 grn_line.quantity_inv,
43 grn_line.id grn_item_id
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";
59 return db_query($sql,"No transactions were returned");
63 function getSuppInvDetails($grn_item_id)
66 inv_line.supp_trans_no inv_no,
67 inv_line.quantity inv_qty,
69 IF (inv.tax_included = 1, inv_line.unit_price - inv_line.unit_tax, inv_line.unit_price) inv_price
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";
83 return db_query($sql,"No transactions were returned");
86 //----------------------------------------------------------------------------------------------------
88 function print_grn_valuation()
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'];
98 include_once($path_to_root . "/reporting/includes/excel_report.inc");
100 include_once($path_to_root . "/reporting/includes/pdf_report.inc");
102 $orientation = ($orientation ? 'L' : 'P');
103 $dec = user_price_dec();
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'));
108 $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right', 'right');
110 $params = array( 0 => $comments,
111 1 => array('text' => _('Period'),'from' => $from, 'to' => $to));
113 $rep = new FrontReport(_('GRN Valuation Report'), "GRNValuationReport", user_pagesize(), 9, $orientation);
114 if ($orientation == 'L')
115 recalculate_cols($cols);
118 $rep->Info($params, $cols, $headers, $aligns);
121 $res = getTransactions($from, $to);
122 $total = $qtotal = $grandtotal = 0.0;
124 while ($trans=db_fetch($res))
126 if ($stock_id != $trans['item_code'])
130 $rep->Line($rep->row - 4);
132 $rep->TextCol(0, 3, _('Total'));
133 $rep->AmountCol(5, 6, $qtotal, $qdec);
134 $rep->AmountCol(8, 9, $total, $dec);
136 $total = $qtotal = 0;
138 $stock_id = $trans['item_code'];
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']);
148 if ($trans['quantity_inv'])
150 $suppinv = getSuppInvDetails($trans['grn_item_id']);
151 while ($inv=db_fetch($suppinv))
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);
162 $qtotal += $inv['inv_qty'];
167 if ($trans['qty_recd'] - $trans['quantity_inv'] !=0 )
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);
178 $qtotal += $trans['qty_recd'] - $trans['quantity_inv'];
186 $rep->Line($rep->row - 4);
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);
193 $rep->TextCol(0, 7, _('Grand Total'));
194 $rep->AmountCol(8, 9, $grandtotal, $dec);
197 $rep->Line($rep->row - 4);