X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=purchasing%2Fincludes%2Fdb%2Fgrn_db.inc;h=09090c302fc6a2bf08b3afbab50f94bfd2b0653b;hb=17b390efcf904072b02ec866b2a427490471a260;hp=24e421a6c3078c37b7e5bac16b8d2b7da2dc065a;hpb=f5a290483ff27984cb2b66ac1ec50c5e2d36d087;p=fa-stable.git

diff --git a/purchasing/includes/db/grn_db.inc b/purchasing/includes/db/grn_db.inc
index 24e421a6..09090c30 100644
--- a/purchasing/includes/db/grn_db.inc
+++ b/purchasing/includes/db/grn_db.inc
@@ -12,11 +12,6 @@
 //------------------- update average material cost ------------------------------------------ Joe Hunt Mar-03-2008
 function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $adj_only=false)
 {
-	//Handle if inventory will become negative
-    //8-OCT-2011 : Skip negative inventory adjustment for case of adjust_only
-    if (is_inventory_item($stock_id) && !$adj_only) 
-        handle_negative_inventory($stock_id, $qty, $price, $date);	
-
 	// probably this function should be optimized
 	// passing transaction cart as argument. This would
 	// save a couple of db calls like get_supplier()
@@ -38,27 +33,33 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date,
 	}	
 	else
 		$price_in_home_currency = $price;
-	
+
 	$price_in_home_currency_ = $price_in_home_currency;
+
+	//Handle if inventory will become negative
+    //8-OCT-2011 : Skip negative inventory adjustment for case of adjust_only
+    if (is_inventory_item($stock_id) && !$adj_only) 
+        handle_negative_inventory($stock_id, $qty, $price_in_home_currency, $date);
 	
 	$sql = "SELECT mb_flag, material_cost, labour_cost, overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id=".db_escape($stock_id);
 	$result = db_query($sql);
 	$myrow = db_fetch($result);
 	$material_cost = $myrow['material_cost'];
 	
-	//Price adjustment for manufactured item
-	if (!$adj_only && $myrow['mb_flag'] == 'M') 
+	//Price adjustment for manufactured item
+	if (!$adj_only && $myrow['mb_flag'] == 'M') 
 	{
 		$standard_cost = get_standard_cost($stock_id);
 		//reduce overhead_cost and labour_cost from price as those will remain as is
 		$price_in_home_currency = $price_in_home_currency - $myrow['labour_cost'] - $myrow['overhead_cost'];
 	}
-	
+
 	if ($adj_only)
 		$exclude = ST_CUSTDELIVERY;
 	else
 		$exclude = 0;
 	$cost_adjust = false;
+
 	$qoh = get_qoh_on_date($stock_id);
 
 	if ($adj_only)
@@ -75,14 +76,14 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date,
 			$qoh = 0;
 		}
 		if ($qoh + $qty > 0)
-			$material_cost 
-= ($qoh * $material_cost + $qty * $price_in_home_currency) /	($qoh + $qty);
-	}	
-	
+			$material_cost = ($qoh * $material_cost + $qty * $price_in_home_currency) /	($qoh + $qty);
+	}
+
 	if ($cost_adjust) // new 2010-02-10 //Chaitanya : Material_cost replaced with price
 		adjust_deliveries($stock_id, $price_in_home_currency_, $date);
 	$sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost)."
 		WHERE stock_id=".db_escape($stock_id);
+
 	db_query($sql,"The cost details for the inventory item could not be updated");
 	return $material_cost;
 }
@@ -98,7 +99,16 @@ function add_grn(&$po)
 	begin_transaction();
 	hook_db_prewrite($po, ST_SUPPRECEIVE);
 
-	$grn = add_grn_batch($po->order_no, $po->supplier_id, $po->reference, $po->Location, $date_);
+	if (!is_company_currency($po->curr_code))
+	{
+		if (!$po->ex_rate)
+			$po->ex_rate = get_date_exchange_rate($po->curr_code, $date_);
+		else
+			add_new_exchange_rate($po->curr_code, $date_, $po->ex_rate);
+	} else
+		$po->ex_rate = 1;
+
+	$grn = add_grn_batch($po->order_no, $po->supplier_id, $po->reference, $po->Location, $date_, $po->ex_rate);
 
     $clearing_act = get_company_pref('grn_clearing_act');
 	if ($clearing_act) {	// otherwise GRN clearing account is not used
@@ -117,10 +127,10 @@ function add_grn(&$po)
 				if ($clearing_act)
 					$total += add_gl_trans_supplier(ST_SUPPRECEIVE, $grn, $date_, $stock_gl_code["inventory_account"],
 						$stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'],
-						$order_line->receive_qty * $order_line->taxfree_charge_price($po), $po->supplier_id, '', 0, $order_line->stock_id);
-				update_average_material_cost($po->supplier_id, $order_line->stock_id, $order_line->price,
+						$order_line->receive_qty * $order_line->taxfree_charge_price($po), $po->supplier_id, '', 0, $order_line->stock_id);
+				update_average_material_cost($po->supplier_id, $order_line->stock_id, $order_line->price,
 					$order_line->receive_qty, $date_);
-			}		
+			}
 			//----------------------------------------------------------------------------------------------------------------
 			if ($order_line->qty_received == 0)
 			{
@@ -143,7 +153,7 @@ function add_grn(&$po)
 			/* Update location stock records - NB  a po cannot be entered for a service/kit parts done automatically */
 			add_stock_move(ST_SUPPRECEIVE, $order_line->stock_id, $grn, $po->Location, $date_, "",
 				$order_line->receive_qty, $order_line->standard_cost,
-            		$po->supplier_id, 1, $order_line->taxfree_charge_price($po));
+            		$po->supplier_id, 1, $order_line->taxfree_charge_price($po));
 
 		} /*quantity received is != 0 */
 	} /*end of order_line loop */
@@ -165,13 +175,13 @@ function add_grn(&$po)
 
 //----------------------------------------------------------------------------------------
 
-function add_grn_batch($po_number, $supplier_id, $reference, $location, $date_)
+function add_grn_batch($po_number, $supplier_id, $reference, $location, $date_, $rate)
 {
 	$date = date2sql($date_);
 
-	$sql = "INSERT INTO ".TB_PREF."grn_batch (purch_order_no, delivery_date, supplier_id, reference, loc_code)
+	$sql = "INSERT INTO ".TB_PREF."grn_batch (purch_order_no, delivery_date, supplier_id, reference, loc_code, rate)
 			VALUES (".db_escape($po_number).", ".db_escape($date).", "
-			.db_escape($supplier_id).", ".db_escape($reference).", ".db_escape($location).")";
+			.db_escape($supplier_id).", ".db_escape($reference).", ".db_escape($location).", ".db_escape($rate).")";
 
 	db_query($sql, "A grn batch record could not be inserted.");
 
@@ -255,41 +265,50 @@ function set_grn_item_credited(&$entered_grn, $supplier, $transno, $date)
 function get_grn_items($grn_batch_id=0, $supplier_id="", $outstanding_only=false,
 	$is_invoiced_only=false, $invoice_no=0, $begin="", $end="")
 {
-    $sql = "SELECT ".TB_PREF."grn_batch.*, ".TB_PREF."grn_items.*, "
-    	.TB_PREF."purch_order_details.unit_price,"
-    	.TB_PREF."purch_order_details.act_price,"
-    	.TB_PREF."purch_order_details.quantity_ordered,"
-		.TB_PREF."purch_order_details.std_cost_unit, units
-    	FROM ".TB_PREF."grn_batch, ".TB_PREF."grn_items, "
-    	.TB_PREF."purch_order_details, ".TB_PREF."stock_master";
-    if ($invoice_no != 0)
-    	$sql .= ", ".TB_PREF."supp_invoice_items";
-    $sql .= " WHERE ".TB_PREF."grn_items.grn_batch_id=".TB_PREF."grn_batch.id
-		AND ".TB_PREF."grn_items.po_detail_item=".TB_PREF."purch_order_details.po_detail_item";
-	if ($invoice_no != 0)
-		$sql .= " AND ".TB_PREF."supp_invoice_items.supp_trans_type=".ST_SUPPINVOICE." AND 
-			".TB_PREF."supp_invoice_items.supp_trans_no=$invoice_no AND
-			".TB_PREF."grn_items.id=".TB_PREF."supp_invoice_items.grn_item_id";
-	$sql .= " AND ".TB_PREF."stock_master.stock_id=".TB_PREF."grn_items.item_code ";
+    $sql = "SELECT grn.*, grn_item.*,
+		grn_item.quantity_inv".($invoice_no ? "-invoice_item.quantity" : '')."  quantity_inv,
+    	po_item.unit_price,
+    	po_item.act_price,
+    	po_item.quantity_ordered,
+    	po_item.std_cost_unit, units
+    	FROM	".TB_PREF."grn_batch grn, "
+    			.TB_PREF."purch_order_details po_item, "
+    			.TB_PREF."stock_master stock_item,"
+	   			.TB_PREF."grn_items grn_item ";
+ 
+    if ($invoice_no != 0) {
+    	$sql .= " LEFT JOIN ".TB_PREF."supp_invoice_items invoice_item ON ";
+
+		$inv_cond = "invoice_item.supp_trans_type=".ST_SUPPINVOICE." AND invoice_item.supp_trans_no ";
+		if (is_array($invoice_no))
+			$inv_cond .= " IN (".implode(',' , $invoice_no) . ")";
+		else
+			$inv_cond .= " = $invoice_no";
+		$sql .= "($inv_cond) AND grn_item.id=invoice_item.grn_item_id";
+	}
+    $sql .= " WHERE grn_item.grn_batch_id=grn.id
+		AND grn_item.po_detail_item=po_item.po_detail_item";
+
+	if ($outstanding_only)
+    	$sql .= " AND (".($invoice_no ? "$inv_cond OR ":'')."grn_item.qty_recd - grn_item.quantity_inv > 0)";
+
+	$sql .= " AND stock_item.stock_id=grn_item.item_code ";
 
 	if ($begin != "")
-		$sql .= " AND ".TB_PREF."grn_batch.delivery_date>='".date2sql($begin)."'";
+		$sql .= " AND grn.delivery_date>='".date2sql($begin)."'";
 	if ($end != "")
-		$sql .= " AND ".TB_PREF."grn_batch.delivery_date<='".date2sql($end)."'";
+		$sql .= " AND grn.delivery_date<='".date2sql($end)."'";
 	if ($grn_batch_id != 0)
-		$sql .= " AND ".TB_PREF."grn_batch.id=".db_escape($grn_batch_id)
-			." AND ".TB_PREF."grn_items.grn_batch_id=".db_escape($grn_batch_id);
+		$sql .= " AND grn.id=".db_escape($grn_batch_id)
+			." AND grn_item.grn_batch_id=".db_escape($grn_batch_id);
 
 	if ($is_invoiced_only)
-		$sql .= " AND ".TB_PREF."grn_items.quantity_inv > 0";
-
-	if ($outstanding_only)
-    	$sql .= " AND ".TB_PREF."grn_items.qty_recd - ".TB_PREF."grn_items.quantity_inv > 0";
+		$sql .= " AND grn_item.quantity_inv > 0";
 
 	if ($supplier_id != "")
-		$sql .= " AND ".TB_PREF."grn_batch.supplier_id =".db_escape($supplier_id);
+		$sql .= " AND grn.supplier_id =".db_escape($supplier_id);
 
-	$sql .= " ORDER BY ".TB_PREF."grn_batch.delivery_date, ".TB_PREF."grn_batch.id, ".TB_PREF."grn_items.id";
+	$sql .= " ORDER BY grn.delivery_date, grn.id, grn_item.id";
 
 	return db_query($sql, "Could not retreive GRNS");
 }
@@ -356,6 +375,7 @@ function read_grn($grn_batch, &$order)
 
 	$row = db_fetch($result);
 	$po_number = $row["purch_order_no"];
+	$order->ex_rate = $row["rate"];
 
 	$result = read_po_header($po_number, $order);
 
@@ -453,4 +473,3 @@ function void_grn($grn_batch)
 
 //----------------------------------------------------------------------------------------------------------
 
-?>
\ No newline at end of file