X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=reporting%2Frep305.php;h=a6f8a9bc6a57ca4635cc1c09a7149309ef59f5b7;hb=48589f9ce6c51bb25e2fa8ed83c57d9dfa485e44;hp=1fa6e8cdf934fa3be5634f4e51e3873b2dad9813;hpb=0c760ea65c8c6f0a45ea8328abab53be649f2105;p=fa-stable.git diff --git a/reporting/rep305.php b/reporting/rep305.php index 1fa6e8cd..a6f8a9bc 100644 --- a/reporting/rep305.php +++ b/reporting/rep305.php @@ -34,25 +34,27 @@ function getTransactions($from, $to) $from = date2sql($from); $to = date2sql($to); - $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."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_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."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"; + $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"); @@ -61,18 +63,22 @@ 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, - 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"; + 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"); } @@ -86,13 +92,15 @@ 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, 260, 295, 330, 370, 410, 455, 515); $headers = array(_('Stock ID'), _('Description'), _('PO No'), _('GRN')."#", _('Inv')."#", _('Qty'), _('Inv Price'), _('PO Price'), _('Total')); @@ -102,7 +110,9 @@ function print_grn_valuation() $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); @@ -127,9 +137,6 @@ function print_grn_valuation() } $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['std_cost_unit'] *= $rate; $rep->NewLine(); $rep->TextCol(0, 1, $trans['item_code']); @@ -143,7 +150,7 @@ function print_grn_valuation() $suppinv = getSuppInvDetails($trans['grn_item_id']); while ($inv=db_fetch($suppinv)) { - $inv['inv_price'] *= $rate; + $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); @@ -156,9 +163,12 @@ function print_grn_valuation() $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); @@ -189,4 +199,3 @@ function print_grn_valuation() $rep->End(); } -?> \ No newline at end of file