X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=reporting%2Frep305.php;h=a6f8a9bc6a57ca4635cc1c09a7149309ef59f5b7;hb=48589f9ce6c51bb25e2fa8ed83c57d9dfa485e44;hp=bfbaf6b01acc1e41f0494d72b798288d182b4d1a;hpb=d96d85e01d70b9b2516bc95379c6f7771f97f6e8;p=fa-stable.git diff --git a/reporting/rep305.php b/reporting/rep305.php index bfbaf6b0..a6f8a9bc 100644 --- a/reporting/rep305.php +++ b/reporting/rep305.php @@ -33,24 +33,56 @@ function getTransactions($from, $to) { $from = date2sql($from); $to = date2sql($to); - - $sql = "SELECT DISTINCT ".TB_PREF."grn_batch.supplier_id, - ".TB_PREF."purch_order_details.*, - ".TB_PREF."stock_master.description - FROM ".TB_PREF."stock_master, - ".TB_PREF."purch_order_details, - ".TB_PREF."grn_batch - 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.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"; - + + $sql = "SELECT grn.id batch_no, + grn.supplier_id, + poline.*, + item.description, + grn_line.qty_recd, + grn_line.quantity_inv, + grn_line.id grn_item_id + FROM " + .TB_PREF."stock_master item," + .TB_PREF."purch_order_details poline," + .TB_PREF."grn_batch grn," + .TB_PREF."grn_items grn_line + WHERE item.stock_id=poline.item_code + AND grn.purch_order_no=poline.order_no + AND grn.id = grn_line.grn_batch_id + AND grn_line.po_detail_item = poline.po_detail_item + AND grn_line.qty_recd>0 + AND grn.delivery_date>='$from' + AND grn.delivery_date<='$to' + AND item.mb_flag <>'F' + ORDER BY item.stock_id, grn.delivery_date"; + return db_query($sql,"No transactions were returned"); } +function getSuppInvDetails($grn_item_id) +{ + $sql = "SELECT + inv_line.supp_trans_no inv_no, + inv_line.quantity inv_qty, + inv.rate, + IF (inv.tax_included = 1, inv_line.unit_price - inv_line.unit_tax, inv_line.unit_price) inv_price + FROM " + .TB_PREF."grn_items grn_line," + .TB_PREF."supp_trans inv," + .TB_PREF."supp_invoice_items inv_line + WHERE grn_line.id = inv_line.grn_item_id + AND grn_line.po_detail_item = inv_line.po_detail_item_id + AND grn_line.item_code = inv_line.stock_id + AND inv.type = inv_line.supp_trans_type + AND inv.trans_no = inv_line.supp_trans_no + AND inv_line.supp_trans_type = 20 + AND inv_line.grn_item_id = ".$grn_item_id." + ORDER BY inv_line.id asc"; + + return db_query($sql,"No transactions were returned"); +} + //---------------------------------------------------------------------------------------------------- function print_grn_valuation() @@ -60,27 +92,31 @@ 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); - $rep->Header(); + $rep->NewPage(); $res = getTransactions($from, $to); $total = $qtotal = $grandtotal = 0.0; @@ -94,43 +130,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']); $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); @@ -138,4 +199,3 @@ function print_grn_valuation() $rep->End(); } -?> \ No newline at end of file