X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Fdb%2Finventory_db.inc;h=e8f8e3c28888c4cc07b0c3d8d91e9eb4dd6742ca;hb=b52ca51128e33a2d051d8e36a7dcc2c22e816020;hp=afd706901be0ec966a15ad6ca51d68288f72c7cc;hpb=54d94b089fef26367a4adae21891d0a465b3d22d;p=fa-stable.git

diff --git a/includes/db/inventory_db.inc b/includes/db/inventory_db.inc
index afd70690..e8f8e3c2 100644
--- a/includes/db/inventory_db.inc
+++ b/includes/db/inventory_db.inc
@@ -45,7 +45,8 @@ function get_qoh_on_date($stock_id, $location=null, $date_=null, $exclude=0)
             $myrow[0] -= $myrow2[0];
     }
 
-    return $myrow[0];
+    $qoh =  $myrow[0];
+		return $qoh ? $qoh : 0;
 }
 
 //--------------------------------------------------------------------------------------
@@ -65,7 +66,6 @@ function get_item_edit_info($stock_id)
 
 function get_standard_cost($stock_id)
 {
-	//Chaitanya : Compatibility with service items
 	$sql = "SELECT (material_cost + labour_cost + overhead_cost) AS std_cost
 		FROM ".TB_PREF."stock_master s WHERE stock_id=".db_escape($stock_id);
 	$result = db_query($sql, "The standard cost cannot be retrieved");
@@ -130,7 +130,7 @@ function last_negative_stock_trans_id($stock_id, $to)
 	
 	$qty = 0;
 	$flag = 0;
-	$negative_trans_id = 1;
+	$negative_trans_id = -1;
 	
 	while ($myrow = db_fetch($result))
 	{
@@ -144,7 +144,10 @@ function last_negative_stock_trans_id($stock_id, $to)
 			$flag = 0;	
 	}
 		
-	return $negative_trans_id;
+	if ($flag == 1)
+		return $negative_trans_id;
+	else 
+		return false;
 }
 
 //-------------------------------------------------------------------
@@ -164,30 +167,20 @@ function get_deliveries_between($stock_id, $from, $to)
 function get_deliveries_from_trans($stock_id, $from)
 {
 	// -ve qty is delivery either by ST_CUSTDELIVERY or inventory adjustment
-	$sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves
-		WHERE stock_id=".db_escape($stock_id)." AND qty < 0 AND
-			trans_id>='$from' GROUP BY stock_id";
+    //Price for GRN and SUPPCREDIT and std_cost for other trans_types
+    $sql = "SELECT SUM(-qty), SUM(-qty*IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost))
+        FROM ".TB_PREF."stock_moves
+        WHERE stock_id=".db_escape($stock_id)." AND qty < 0 AND
+            trans_id>='$from' GROUP BY stock_id";
 	$result = db_query($sql, "The deliveries could not be updated");
 	$row = db_fetch_row($result);
 	
-	//return $row;
-	
-	// Get Std cost of previsous transaction before the cut-over delivery
-	// This is useful to get inventory valuation
-	//Chaitanya : Corrected
-	/*$sql = "SELECT max( `trans_id` )
-			FROM ".TB_PREF."stock_moves
-			WHERE stock_id = ".db_escape($stock_id)."
-			AND trans_id<'$from'";
-	$result = db_query($sql, "The deliveries could not be updated");
-	$trans = db_fetch_row($result);
-	$prev_trans = $trans[0];*/
-	
-	$sql = "SELECT standard_cost FROM ".TB_PREF."stock_moves
-		WHERE stock_id=".db_escape($stock_id)
-			." AND trans_id ='$from'";
-	$result = db_query($sql, "The deliveries could not be updated");
-	$cost = db_fetch_row($result);	
+    $sql = "SELECT IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost)
+        FROM ".TB_PREF."stock_moves
+        WHERE stock_id=".db_escape($stock_id)
+            ." AND trans_id ='$from'";
+    $result = db_query($sql, "The deliveries could not be updated");
+    $cost = db_fetch_row($result);
 	
 	// Adjusting QOH valuation 
 	$sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
@@ -196,7 +189,7 @@ function get_deliveries_from_trans($stock_id, $from)
 	$result = db_query($sql, "The deliveries could not be updated");
 	$qoh = db_fetch_row($result);
 	
-	$qty = $row[0] - $qoh[0]; //Qoh is minus from delivered in -ve
+	$qty = $row[0] - $qoh[0]; //QOH prior to -ve stock is subtracted
 	$final_cost = $row[1] - $qoh[0]*$cost[0];
 	
 	return array($qty,$final_cost); 
@@ -215,48 +208,14 @@ function get_purchases_from_trans($stock_id, $from)
 }
 
 //-------------------------------------------------------------------
-/* Original Code V0 Leave as is a while
-function adjust_deliveries_v0($stock_id, $material_cost, $to)
-{
-	if (!is_inventory_item($stock_id))
-		return;
-	$from = last_negative_stock_begin_date($stock_id, $to);
-	if ($from == false || $from == "")
-		return;
-	$from = sql2date($from);
-	$row = get_deliveries_between($stock_id, $from, $to);
-	if ($row == false)
-		return;	
-	$old_cost = $row[1];
-	$new_cost = $row[0] * $material_cost;
-	$diff = $new_cost - $old_cost;
-	if ($diff != 0)
-	{
-	   	$update_no = get_next_trans_no(ST_COSTUPDATE);
-		if (!is_date_in_fiscalyear($to))
-			$to = end_fiscalyear();
-	   
-		$stock_gl_code = get_stock_gl_code($stock_id);
-
-		$memo_ = sprintf(_("Cost was %s changed to %s for item '%s'"),
-			$old_cost, $new_cost, $stock_id);
-		add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["cogs_account"], 
-			$stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $memo_, $diff);
 
-		add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["inventory_account"], 
-			0, 0, $memo_, -$diff);
-		add_audit_trail(ST_COSTUPDATE, $update_no, $to);
-   	}
-}
-*/
-//New written function
 function adjust_deliveries($stock_id, $material_cost, $to)
 {
 	if (!is_inventory_item($stock_id))
 		return;
 	
 	$from = last_negative_stock_trans_id($stock_id, $to);
-	if ($from == false || $from == "")
+	if ($from == false || $from == -1)
 		return;
 
 	$row = get_deliveries_from_trans($stock_id, $from);
@@ -290,8 +249,9 @@ function adjust_deliveries($stock_id, $material_cost, $to)
 		$dec = user_price_dec();
 		$old_cost = -round2($old_sales_cost-$old_purchase_cost,$dec);
 		$new_cost = -round2($new_sales_cost-$new_purchase_cost,$dec);
-		
-		$memo_ = _("Cost was ") . $old_cost. _(" changed to ") . $new_cost . _(" for item ")."'$stock_id'";
+
+		$memo_ = sprintf(_("Cost was %s changed to %s x quantity on hand for item '%s'"),
+			number_format2($old_cost, 2), number_format2($new_cost, 2), $stock_id);
 		add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["cogs_account"], 
 			$stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $memo_, $diff);	   
 
@@ -304,7 +264,6 @@ function adjust_deliveries($stock_id, $material_cost, $to)
 function get_stock_gl_code($stock_id)
 {
 	/*Gets the GL Codes relevant to the item account  */
-	//Chaitanya : Updated to also provide mb_flag
 	$sql = "SELECT mb_flag, inventory_account, cogs_account,
 		adjustment_account, sales_account, assembly_account, dimension_id, dimension2_id FROM
 		".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id);
@@ -314,11 +273,10 @@ function get_stock_gl_code($stock_id)
 }
 
 //-----------------------------------------------------------------------------------------
-//Chaitanya : New Function handle negative stock effect
-//Called in add_stock_move
+
 function handle_negative_inventory($stock_id, $quantity, $standard_cost, $date_)
 {
-	//Chaitanya : If negative adjustment result in negative or zero inventory 
+	//If negative adjustment result in negative or zero inventory
 	//then difference should be adjusted
 	$qoh = get_qoh_on_date($stock_id);
 
@@ -328,9 +286,9 @@ function handle_negative_inventory($stock_id, $quantity, $standard_cost, $date_)
 
 		$id = get_next_trans_no(ST_JOURNAL);
 		$ref = $Refs->get_next(ST_JOURNAL);
-		$diff = get_standard_cost($stock_id) - $standard_cost;
-		
-		if ($diff !=0)
+		$diff = round($qoh*get_standard_cost($stock_id) + $quantity*$standard_cost, user_price_dec());
+
+		if ($diff != 0)
 		{
 			$stock_gl_code = get_stock_gl_code($stock_id);
 			$memo = _("Zero/negative inventory handling");
@@ -338,13 +296,13 @@ function handle_negative_inventory($stock_id, $quantity, $standard_cost, $date_)
 			add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, 
 				$stock_gl_code["inventory_account"],
 				$stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo, 
-				-$qoh * $diff);
+				-$diff);
 			//GL Posting to inventory adjustment account
 			add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, 
 				$stock_gl_code["adjustment_account"],
 				$stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo,
-				$qoh * $diff);
-				
+				$diff);
+
 			add_audit_trail(ST_JOURNAL, $id, $date_);
 			add_comments(ST_JOURNAL, $id, $date_, $memo);
 			$Refs->save(ST_JOURNAL, $id, $ref);	
@@ -363,13 +321,6 @@ function add_stock_move($type, $stock_id, $trans_no, $location,
     $date_, $reference, $quantity, $std_cost, $person_id=0, $show_or_hide=1,
     $price=0, $discount_percent=0, $error_msg="")
 {
-	// Chaitanya : Removed following restriction considering WO issues
-	// Voiding issues and productions with Service items can not get the 
-	// Service items compatibility
-	// do not add a stock move if it's a non-inventory item
-	//if (!is_inventory_item($stock_id))
-		//return null;
-
 	$date = date2sql($date_);
 
 	$sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, trans_no, type, loc_code,
@@ -390,7 +341,6 @@ function add_stock_move($type, $stock_id, $trans_no, $location,
 	return db_insert_id();
 }
 
-//Chaitanya : Added function
 function update_stock_move($type, $trans_no, $stock_id, $cost)
 {
 	$sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=".db_escape($cost)
@@ -433,38 +383,29 @@ function get_stock_moves($type, $type_no, $visible=false)
 
 function void_stock_move($type, $type_no)
 {
-    //Chaitanya : Reversing stock move rather than voiding as it is hazardous to lose stock movement trail with respect to costing
-    /*$sql = "UPDATE ".TB_PREF."stock_moves SET qty=0, price=0, discount_percent=0,
-        standard_cost=0 WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
-        
-    db_query($sql, "Could not void stock moves"); */
-    
     $sql = "SELECT * from ".TB_PREF."stock_moves WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
     $result = db_query($sql, "Could not void stock moves");
     while ($row = db_fetch($result))
     {
 		//Skip cost averaging of service items
 		if (is_inventory_item($row["stock_id"]))
-		{        
+		{
 			// The cost has to be adjusted.
-			//Chaitanya : Transaction rates are stored either as price or standard_cost depending
-			//on types
-			$types = array(ST_SUPPCREDIT);
-			if (in_array($type,$types))
-				$trans_rate = $row["price"];
+			// Transaction rates are stored either as price or standard_cost depending on types
+			$types = array(ST_SUPPCREDIT, ST_SUPPRECEIVE);
+			if (in_array($type, $types))
+				$unit_cost = $row["price"];
 			else
-				$trans_rate = $row["standard_cost"];
+				$unit_cost = $row["standard_cost"];
 
-			update_average_material_cost(0, $row["stock_id"],
-				$trans_rate, -$row["qty"], sql2date($row["tran_date"]));
+			update_average_material_cost($row["person_id"], $row["stock_id"],
+				$unit_cost, -$row["qty"], sql2date($row["tran_date"]));
 		}
-		
-		//Post stock move for service items also
-        add_stock_move($type, $row["stock_id"], $type_no, $row["loc_code"],
-    		sql2date($row["tran_date"]), $row["reference"], -$row["qty"]
-			, $row["standard_cost"], $row["person_id"], $row["visible"],
-    		$row["price"], $row["discount_percent"]);
+
     }
+	$sql = "DELETE FROM ".TB_PREF."stock_moves WHERE type=".db_escape($type)
+		."	AND trans_no=".db_escape($type_no);
+	db_query($sql, "The stock movement cannot be delated");
 }
 
 //--------------------------------------------------------------------------------------------------
@@ -488,4 +429,4 @@ function get_location_name($loc_code)
 //--------------------------------------------------------------------------------------------------
 
 
-?>
\ No newline at end of file
+?>