X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=reporting%2Frep305.php;h=8ce820d8faf9d3d9ea0ecd1dc1d9d9931f98cda8;hb=44abddfd180ec27cbd889f4c8c8f8271f6d6d19d;hp=6711d9b7f4257bb7f1a8e42b9432a671760a478f;hpb=46c5f7a65a7659a44ae8254c63152074363d3987;p=fa-stable.git diff --git a/reporting/rep305.php b/reporting/rep305.php index 6711d9b7..8ce820d8 100644 --- a/reporting/rep305.php +++ b/reporting/rep305.php @@ -34,15 +34,22 @@ function getTransactions($from, $to) $from = date2sql($from); $to = date2sql($to); - $sql = "SELECT DISTINCT ".TB_PREF."grn_batch.supplier_id, + $sql = "SELECT ".TB_PREF."grn_batch.id batch_no, + ".TB_PREF."grn_batch.supplier_id, ".TB_PREF."purch_order_details.*, - ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive + ".TB_PREF."stock_master.description, + ".TB_PREF."grn_items.qty_recd, + ".TB_PREF."grn_items.quantity_inv, + ".TB_PREF."grn_items.id grn_item_id FROM ".TB_PREF."stock_master, ".TB_PREF."purch_order_details, - ".TB_PREF."grn_batch + ".TB_PREF."grn_batch, + ".TB_PREF."grn_items WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."purch_order_details.item_code AND ".TB_PREF."grn_batch.purch_order_no=".TB_PREF."purch_order_details.order_no - AND ".TB_PREF."purch_order_details.quantity_received>0 + AND ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id + AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."purch_order_details.po_detail_item + AND ".TB_PREF."grn_items.qty_recd>0 AND ".TB_PREF."grn_batch.delivery_date>='$from' AND ".TB_PREF."grn_batch.delivery_date<='$to' ORDER BY ".TB_PREF."stock_master.stock_id, ".TB_PREF."grn_batch.delivery_date"; @@ -51,6 +58,26 @@ function getTransactions($from, $to) } +function getSuppInvDetails($grn_item_id) +{ + $sql = "SELECT + ".TB_PREF."supp_invoice_items.supp_trans_no inv_no, + ".TB_PREF."supp_invoice_items.quantity inv_qty, + ".TB_PREF."supp_trans.rate, + IF (".TB_PREF."supp_trans.tax_included = 1, ".TB_PREF."supp_invoice_items.unit_price - ".TB_PREF."supp_invoice_items.unit_tax, ".TB_PREF."supp_invoice_items.unit_price) inv_price + FROM ".TB_PREF."grn_items, ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items + WHERE ".TB_PREF."grn_items.id = ".TB_PREF."supp_invoice_items.grn_item_id + AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."supp_invoice_items.po_detail_item_id + AND ".TB_PREF."grn_items.item_code = ".TB_PREF."supp_invoice_items.stock_id + AND ".TB_PREF."supp_trans.type = ".TB_PREF."supp_invoice_items.supp_trans_type + AND ".TB_PREF."supp_trans.trans_no = ".TB_PREF."supp_invoice_items.supp_trans_no + AND ".TB_PREF."supp_invoice_items.supp_trans_type = 20 + AND ".TB_PREF."supp_invoice_items.grn_item_id = ".$grn_item_id." + ORDER BY ".TB_PREF."supp_invoice_items.id asc"; + + return db_query($sql,"No transactions were returned"); +} + //---------------------------------------------------------------------------------------------------- function print_grn_valuation() @@ -60,23 +87,27 @@ function print_grn_valuation() $from = $_POST['PARAM_0']; $to = $_POST['PARAM_1']; $comments = $_POST['PARAM_2']; - $destination = $_POST['PARAM_3']; + $orientation = $_POST['PARAM_3']; + $destination = $_POST['PARAM_4']; if ($destination) include_once($path_to_root . "/reporting/includes/excel_report.inc"); else include_once($path_to_root . "/reporting/includes/pdf_report.inc"); - $dec = user_price_dec(); + $orientation = ($orientation ? 'L' : 'P'); + $dec = user_price_dec(); - $cols = array(0, 75, 225, 275, 345, 390, 445, 515); - $headers = array(_('Stock ID'), _('Description'), _('PO No'), _('Qty Received'), _('Unit Price'), _('Actual Price'), _('Total')); + $cols = array(0, 75, 225, 260, 295, 330, 370, 410, 455, 515); + $headers = array(_('Stock ID'), _('Description'), _('PO No'), _('GRN')."#", _('Inv')."#", _('Qty'), _('Inv Price'), _('PO Price'), _('Total')); - $aligns = array('left', 'left', 'left', 'right', 'right', 'right', 'right'); + $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right', 'right', 'right'); $params = array( 0 => $comments, 1 => array('text' => _('Period'),'from' => $from, 'to' => $to)); - $rep = new FrontReport(_('GRN Valuation Report'), "GRNValuationReport", user_pagesize()); + $rep = new FrontReport(_('GRN Valuation Report'), "GRNValuationReport", user_pagesize(), 9, $orientation); + if ($orientation == 'L') + recalculate_cols($cols); $rep->Font(); $rep->Info($params, $cols, $headers, $aligns); @@ -94,43 +125,68 @@ function print_grn_valuation() $rep->Line($rep->row - 4); $rep->NewLine(2); $rep->TextCol(0, 3, _('Total')); - $rep->AmountCol(3, 4, $qtotal, $qdec); - $rep->AmountCol(6, 7, $total, $dec); + $rep->AmountCol(5, 6, $qtotal, $qdec); + $rep->AmountCol(8, 9, $total, $dec); $rep->NewLine(); $total = $qtotal = 0; } $stock_id = $trans['item_code']; } - $curr = get_supplier_currency($trans['supplier_id']); - $rate = get_exchange_rate_from_home_currency($curr, sql2date($trans['delivery_date'])); - $trans['unit_price'] *= $rate; - $trans['act_price'] *= $rate; $rep->NewLine(); $rep->TextCol(0, 1, $trans['item_code']); - $rep->TextCol(1, 2, $trans['description'].($trans['inactive']==1 ? " ("._("Inactive").")" : ""), -1); + $rep->TextCol(1, 2, $trans['description']); $rep->TextCol(2, 3, $trans['order_no']); $qdec = get_qty_dec($trans['item_code']); - $rep->AmountCol(3, 4, $trans['quantity_received'], $qdec); - $rep->AmountCol(4, 5, $trans['unit_price'], $dec); - $rep->AmountCol(5, 6, $trans['act_price'], $dec); - $amt = round2($trans['quantity_received'] * $trans['act_price'], $dec); - $rep->AmountCol(6, 7, $amt, $dec); - $total += $amt; - $qtotal += $trans['quantity_received']; - $grandtotal += $amt; + $rep->TextCol(3, 4, $trans['batch_no']); + + if ($trans['quantity_inv']) + { + $suppinv = getSuppInvDetails($trans['grn_item_id']); + while ($inv=db_fetch($suppinv)) + { + $inv['inv_price'] *= $inv['rate']; + $rep->TextCol(4, 5, $inv['inv_no']); + $rep->AmountCol(5, 6, $inv['inv_qty'], $qdec); + $rep->AmountCol(6, 7, $inv['inv_price'], $dec); + $rep->AmountCol(7, 8, $trans['std_cost_unit'], $dec); + $amt = round2($inv['inv_qty'] * $inv['inv_price'], $dec); + $rep->AmountCol(8, 9, $amt, $dec); + $rep->NewLine(); + $total += $amt; + $qtotal += $inv['inv_qty']; + $grandtotal += $amt; + } + } + + if ($trans['qty_recd'] - $trans['quantity_inv'] !=0 ) + { + $curr = get_supplier_currency($trans['supplier_id']); + $rate = get_exchange_rate_from_home_currency($curr, sql2date($trans['delivery_date'])); + $trans['unit_price'] *= $rate; + $rep->TextCol(4, 5, "--"); + $rep->AmountCol(5, 6, $trans['qty_recd'] - $trans['quantity_inv'], $qdec); + $rep->AmountCol(7, 8, $trans['unit_price'], $dec); + $amt = round2(($trans['qty_recd'] - $trans['quantity_inv']) * $trans['unit_price'], $dec); + $rep->AmountCol(8, 9, $amt, $dec); + $total += $amt; + $qtotal += $trans['qty_recd'] - $trans['quantity_inv']; + $grandtotal += $amt; + } + else + $rep->NewLine(-1); } if ($stock_id != '') { $rep->Line($rep->row - 4); $rep->NewLine(2); $rep->TextCol(0, 3, _('Total')); - $rep->AmountCol(3, 4, $qtotal, $qdec); - $rep->AmountCol(6, 7, $total, $dec); + $rep->AmountCol(5, 6, $qtotal, $qdec); + $rep->AmountCol(8, 9, $total, $dec); $rep->Line($rep->row - 4); $rep->NewLine(2); - $rep->TextCol(0, 6, _('Grand Total')); - $rep->AmountCol(6, 7, $grandtotal, $dec); + $rep->TextCol(0, 7, _('Grand Total')); + $rep->AmountCol(8, 9, $grandtotal, $dec); } $rep->Line($rep->row - 4);