<?php
// allocation.php
// VIMS RDT Order / Pick Allocation  Process
// ----------------------------------------------------------------------------------------
// Modified 2019-04-26 - Over Allocation Correction.
// ----------------------------------------------------------------------------------------
header('Cache-Control: no-cache, no-store, must-revalidate');   
header('Expires: 0');
set_time_limit(0);

$mFunction  		= $_GET['function'];
$mConnect   		= $_GET['connection'];
$mUser				= $_GET['user'];
$mPwd				= $_GET['pword'];
$mHost              = $_GET['host'];
$mDbName			= $_GET['dbase'];
$mTableName 		= $_GET['table'];
$mFilter			= $_GET['filter'];
$mReturnFields		= $_GET['returnfields'];

$mFilter = str_replace("|AND|","&",$mFilter);
$mFilter = str_replace("/dbase/",$mDbName,$mFilter);

$mErrMsg = "OK  -";

// Get Pick
if (($mFunction != null) && (strtolower($mFunction) == "getpick"))
{
	$mErrMsg = "";
	$mToPLt = "";
// Replace Chars in Input String to emulate a JSON format instead of GET format
	$mFilter = str_replace("=",":",$mFilter);
	$mFilter = str_replace("&",",",$mFilter);
// Explode Input String Into Array - Separated by ,
	$mArray1 = explode(",",$mFilter);
// Create Associative Array by Exploding Array Elements on :
	foreach($mArray1 as $k1=>$v1)
	{
		list($k2, $v2) = explode(":",$mArray1[$k1]);
		if ($k2 == "customerreference")
			$mCustomerRef = trim($v2);
		elseif ($k2 == "locationcode")
			$mLocationCode = strtoupper(trim($v2));
		elseif ($k2 == "aisle")
			$mAisle = trim($v2);
		elseif ($k2 == "toplt")
			$mToPlt = trim($v2);
		elseif ($k2 == "currentUser")
			$mCurrentUser = trim($v2);
		elseif ($k2 == "transactionType")
			$mTransactionTypeId = strtoupper(trim($v2));
	}
	$mConnectp = str_replace(":8081/vantec",":8085/pick",$mConnect);
	$mStatus = getPick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mCustomerRef,$mAisle,$mConnectp,$mToPlt);
	echo $mStatus;
	return;
}

// post pick
elseif (($mFunction != null) && (strtolower($mFunction) == "postpick"))
{
	$mErrMsg = "";
	$mToPlt = "";
	$mToLocHash = "";
// Replace Chars in Input String to emulate a JSON format instead of GET format
	$mFilter = str_replace("=",":",$mFilter);
	$mFilter = str_replace("&",",",$mFilter);
// Explode Input String Into Array - Separated by ,
	$mArray1 = explode(",",$mFilter);
// Create Associative Array by Exploding Array Elements on :
	foreach($mArray1 as $k1=>$v1)
	{
		list($k2, $v2) = explode(":",$mArray1[$k1]);
		if (strtolower($k2) == "pickdetailid")
			$mPickDtlId = trim($v2);
		elseif (strtolower($k2) == "inventorymasterid")
			$mPickInvId = trim($v2);
		elseif (strtolower($k2) == "plt")
			$mToPlt = trim($v2);
		elseif (strtolower($k2) == "serialreference")
			$mSerialReference = trim($v2);
		elseif ((strtolower($k2) == "quantity") || (strtolower($k2) == "countqty") || (strtolower($k2) == "pickqty"))	
			$mPickQty = floatval($v2);
		elseif (strtolower($k2) == "tolocationhashcode")
			$mToLocHash = trim($v2);		
		elseif (strtolower($k2) == "customerreference")
			$mCustomerRef = trim($v2);
		elseif (strtolower($k2) == "locationcode")
			$mLocationCode = strtoupper(trim($v2));
		elseif (strtolower($k2) == "aisle")
			$mAisle = trim($v2);
		elseif ($k2 == "currentUser")
			$mCurrentUser = trim($v2);
		elseif ($k2 == "transactionTypeId")
			$mTransactionTypeId = strtoupper(trim($v2));
	}
	$mConnectp = str_replace(":8081/vantec",":8085/pick",$mConnect);
	$mStatus = updatePickDetail($mHost,$mUser,$mPwd,$mDbName,$mPickDtlId,$mPickInvId,$mSerialReference,$mPickQty,$mCurrentUser,$mTransactionTypeId,$mToPlt,$mToLocHash,$mConnect);
	if (substr($mStatus,0,2) == "OK")
		$mStatus = getPick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mCustomerRef,$mAisle,$mConnectp,$mToPlt);
//	exit ($mStatus);
	echo $mStatus;
	return;
}

// Validate Pick
elseif (($mFunction != null) && ($mFunction=="validatepick"))
{
	$mErrMsg = "";
// Explode Input Parm
// Replace Chars in Input String to emulate a JSON format instead of GET format
	$mFilter = str_replace("=",":",$mFilter);
	$mFilter = str_replace("&",",",$mFilter);
	$i=0;
	$mPartNumber = "";
	$mSerialReference = "";
	$mFromLocation = "";
	$mVldLocationHash = "";
	$mVldPartNumber = "";
	$mVldSerialReference = "";
	$mQty = "";
	$mCustomerReference = "";
// Explode Input String Into Array - Separated by ,
	$mArray1 = explode(",",$mFilter);
// Create Associative Array by Exploding Array Elements on :
	foreach($mArray1 as $k1=>$v1)
	{
		list($k2, $v2) = explode(":",$mArray1[$k1]);
		if ($k2 == "customerreference")
			$mCustomerReference = strtoupper(trim($v2));
		elseif ($k2 == "partnumber")
			$mPartNumber = strtoupper(trim($v2));
		elseif ($k2 == "serialreference")
			$mSerialReference = strtoupper(trim($v2));
		elseif ($k2 == "fromlocationhashcode")
			$mFromLocation = strtoupper(trim($v2));
		elseif ($k2 == "vldpartnumber")
			$mVldPartNumber = strtoupper(trim($v2));
		elseif ($k2 == "vldlocation")
			$mVldLocation = strtoupper(trim($v2));
		elseif ($k2 == "vldserialreference")
			$mVldSerialReference = strtoupper(trim($v2));
		elseif ((strtolower($k2) == "quantity") || (strtolower($k2) == "countqty"))
			$mQty = floatval($v2);
		elseif ((strtolower($k2) == "vldquantity"))
			$mVldQty = floatval($v2);
		elseif ($k2 == "currentUser")
			$mCurrentUser = trim($v2);
		elseif ($k2 == "transactionType")
			$mTransactionTypeId = strtoupper(trim($v2));
	}
	
	$mErrMsg = validatePick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mCustomerReference,$mFromLocation,$mVldLocation,$mPartNumber,$mVldPartNumber,$mSerialReference,$mVldSerialReference,$mQty,$mVldQty);
	echo $mErrMsg;
	return;

}

// Test for full-box-pick
elseif (($mFunction != null) && ($mFunction=="checkfullboxpick"))
{
// mysql connection
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
// Set default error message
	$mErrMsg = "";
	
	$mSerialReference = "";
	$mPartNumber = "";
	$mFullBoxPick = 1;
	
// Explode Input Parm
// Replace Chars in Input String to emulate a JSON format instead of GET format
	$mFilter = str_replace("=",":",$mFilter);
	$mFilter = str_replace("&",",",$mFilter);
	$i=0;
	$mPartNumber = "";
// Explode Input String Into Array - Separated by ,
	$mArray1 = explode(",",$mFilter);
// Create Associative Array by Exploding Array Elements on :
	foreach($mArray1 as $k1=>$v1)
	{
		list($k2, $v2) = explode(":",$mArray1[$k1]);
		if (strtolower($k2) == "customerreference")
			$mCustomerReference = strtoupper($v2);
		elseif (strtolower($k2) == "partnumber")
			$mPartNumber = strtoupper($v2);
		elseif (strtolower($k2) == "serialreference")
			$mSerialReference = strtoupper($v2);
		elseif ($k2 == "currentUser")
			$mCurrentUser = trim($v2);
		elseif ($k2 == "transactionType")
			$mTransactionTypeId = strtoupper(trim($v2));
	}
// Check Order Type Settings
	$mDataQuery = "SELECT document_header.id AS id, document_header.document_reference_code AS document_reference, document_header.time_slot AS time_slot, order_type.pick_by_line, order_type.order_type_code AS order_type_code, order_type.full_box_pick AS full_box_pick FROM document_header LEFT JOIN document_status ON document_header.document_status_id=document_status.id LEFT JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE document_header.document_type='RRM Order' AND document_header.customer_reference_code='".$mCustomerReference."' AND (document_header.document_status_id IS null OR document_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated')) LIMIT 1";		
	if (!mysqli_query($mConnection,$mDataQuery))
		exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
	$mData = mysqli_query($mConnection,$mDataQuery);
	if (mysqli_num_rows($mData) == 0)
	{
		$mDataQuery = "SELECT document_header.id AS id, document_header.document_reference_code AS document_reference, document_header.time_slot AS time_slot, order_type.order_type_code AS order_type_code, order_type.pick_by_line, order_type.full_box_pick AS full_box_pick FROM document_header LEFT JOIN document_status ON document_header.document_status_id=document_status.id LEFT JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE document_header.time_slot='".$mCustomerReference."' AND (document_header.document_status_id IS null OR document_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated')) ORDER BY document_header.expected_delivery";
		if (!mysqli_query($mConnection,$mDataQuery))
			exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
		$mData = mysqli_query($mConnection,$mDataQuery);
	}
	$mPickByLine = 0;
	$mFullBoxPick = 1;
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mOrderType = ($mRow['order_type_code']);
		$mPickByLine = ($mRow['pick_by_line']);
		$mFullBoxPick = ($mRow['full_box_pick']);
	}
//	exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".$mOrderType."-".$mFullBoxPick);
	mysqli_close($mConnection);
// Set Qty screen settings based on full-box flag
	if ($mFullBoxPick == 0)
		if ($mSerialReference == "")
//			$mDataStr = "DATA:toLocationHashCode::HHHHHHI,boxCountQty::HHHHHHH";
			$mDataStr = "DATA:toLocationHashCode::HHHHHHH,boxCountQty::HHHHHHH";
		elseif ($mPartNumber == "")
			$mDataStr = "DATA:serialReference:".$mSerialReference."::skip,qtyAllocated::I";
		else
			$mDataStr = "DATA:partNumber:".$mPartNumber."::skip,qtyAllocated::I";
	else
		if ($mSerialReference == "")
			$mDataStr = "DATA:toLocationHashCode::HHHHHHH,boxCountQty::HHHHHHI";
		elseif ($mPartNumber == "")
			$mDataStr = "DATA:serialReference:".$mSerialReference.",qtyAllocated::P";
		else
			$mDataStr = "DATA:partNumber:".$mPartNumber.",qtyAllocated::P";
//	exit($mDataStr);
	echo $mDataStr;
	return;
}

// Pick Pallet Box Count
elseif (($mFunction != null) && ($mFunction == "validatepickboxcount"))
{
	$mQty = "";
	$mReference = "";
	$mPlt = "";
	$mTrys = "3";
	$mErrMsg = "OK  -";
// Explode Input Parm
// Replace Chars in Input String to emulate a JSON format instead of GET format
	$mFilter = str_replace("=",":",$mFilter);
	$mFilter = str_replace("&",",",$mFilter);
	$i=0;
	$mMarQry = "MARQRY";

// Explode Input String Into Array - Separated by ,
	$mArray1 = explode(",",$mFilter);
// Create Associative Array by Exploding Array Elements on :
	foreach($mArray1 as $k1=>$v1)
	{
		list($k2, $v2) = explode(":",$mArray1[$k1]);
		if ($k2 == "reference")
			$mReference = $v2;
		elseif ($k2 == "toplt")
			$mPlt = strtoupper($v2);
		elseif ($k2 == "trys")
			$mTrys = $v2;
		elseif ($k2 == "qty")
			$mQty = $v2;
		elseif ($k2 == "currentUser")
			$mCurrentUser = $v2;
		elseif ($k2 == "transactionType")
			$mTransactionTypeId = strtoupper($v2);
	}
// Check Reference
	if (($mReference == "") || (!$mReference))
	{
//		exit("FAIL-Invalid Reference");
		echo "FAIL-Invalid Reference";
		return;
	}
	if (($mPlt == "") || (!$mPlt))
	{
//		exit("FAIL-Invalid Pallet");
		echo "FAIL-Invalid Pallet";
		return;
	}
// get Pallet / box count
	if (($mErrMsg == "OK  -") && ($mReference != ""))
	{
// mysql connection
		$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
			exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
// get Pallet Inventory Count
		$i = 0;
		$mInvCount = 0;
// Count Inventory
//		$mDataQuery = "SELECT count(*) as invcount FROM inventory_master JOIN pick_detail ON inventory_master.id=pick_detail.inventory_master_id JOIN pick_body ON pick_body.id=pick_detail.pick_body_id JOIN pick_header ON pick_header.id=pick_body.pick_header_id WHERE inventory_master.plt_number = '".$mPlt."' AND pick_header.customer_reference='".$mReference."' AND inventory_master.inventory_qty <> 0 LIMIT 1";
		$mDataQuery = "SELECT count(*) as invcount FROM inventory_master JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE tag.tag_reference = '".$mPlt."' AND inventory_master.inventory_qty <> 0 LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
		$mData = mysqli_query($mConnection,$mDataQuery);		
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mInvCount = (int)($mRow['invcount']);
			$i++;
		}
//		exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".$mQty."-".$mInvCount."-".$i);
		$mPltSts = "";
// Check Counted Qty against Inventory Sum
		if (($mTrys != "") && ($mQty != $mInvCount))
		{
			$mErrMsg = "FAIL-Wrong Box Count Quantity.";
			if (($mTrys == "0") || ($mTrys == "1"))
			{
// Set Status to PI
				$mErrMsg = "FAIL-Wrong Box Count";
				$mPltSts = "PI";
				$mErrMsg = "OKMS-Locate Box to Query Area,DATA:toLocationHashCode:".$mMarQry.":HHHHHHI,DATA:boxCountQty::HHHHHHH";
			}
			else
			{
				$mPltSts = $mTrys;
			}
		}
		else
		{
			$mPltSts = "OK";
			$mErrMsg = "DATA:toLocationHashCode::HHHHHHI,boxCountQty::HHHHHHH";
		}
		$mDataQuery = "UPDATE tag SET created_by='".$mPltSts."', last_updated=now() WHERE tag_reference='".$mPlt."' LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
		
		mysqli_close($mConnection);
		if ($mErrMsg == "OK  -")
			$mErrMsg = "OKCR-";
	}
//	exit($mErrMsg);
	echo $mErrMsg;
	return;
}

// echo "FAIL-test";

// Validate Pick Entries
function validatePick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mCustomerReference,$mFromLocation,$mVldLocation,$mPartNumber,$mVldPartNumber,$mSerialReference,$mVldSerialReference,$mQty,$mVldQty)
{
//	echo "FAIL-TEST1";
//	return;
	
	$mErrMsg = "";
	if ($mFromLocation == "")
	{
		$mErrMsg = "FAIL-Location is Incorrect";
	}
	
// mysql connection
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
	
	if (($mErrMsg == "") && (($mFromLocation != "") || ($mVldLocation != "")))
	{
		$mDataQuery = "SELECT location.location_code AS location_code FROM location WHERE location.location_hash = '".$mFromLocation."' LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
		$mData = mysqli_query($mConnection,$mDataQuery);
		$i = 0;
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDataStr = ($mRow['location_code']);
			$i++;
		}
		if (($i == 0) || ($mDataStr != $mVldLocation))
		{
			$mErrMsg = "FAIL-Location is Incorrect";
		}
	}

// Check Scanned Part against pick
	if (($mErrMsg == "") && ($mPartNumber != ""))
	{
		if (trim($mPartNumber) != trim($mVldPartNumber))
		{
			$mDataQuery = "SELECT id AS id, part_number AS part_number FROM part WHERE upper(part_number) = '".$mPartNumber."' OR (upper(part_number)='".$mVldPartNumber."' AND upper(jis_supply_group)='".$mPartNumber."') LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
			$mData = mysqli_query($mConnection,$mDataQuery);
			$i = 0;
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mPartNumber = $mRow['part_number'];
				$i++;
			}
			
			if (($i == 0) || (strtoupper($mPartNumber) != $mVldPartNumber))
				$mErrMsg = "FAIL-Incorrect Part Number";
		}
	}
// Validate Entered Serial	
	if (($mErrMsg == "") && ($mSerialReference != ""))
	{
		if ($mPartNumber == "")
			if ($mVldPartNumber == "")
				$mDataQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.requires_inspection AS inspect, location.location_code, part.conversion_factor AS conversion_factor FROM inventory_master JOIN part ON inventory_master.part_id=part.id JOIN location on inventory_master.current_location_id=location.id WHERE UPPER(inventory_master.serial_reference)='".$mSerialReference."' AND inventory_master.inventory_qty > 0 LIMIT 2";
			else
				$mDataQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.requires_inspection AS inspect, location.location_code,part.part_number AS part_number, part.conversion_factor AS conversion_factor FROM inventory_master JOIN part ON inventory_master.part_id=part.id JOIN location on inventory_master.current_location_id=location.id WHERE UPPER(inventory_master.serial_reference)='".$mSerialReference."' AND (part.part_number='".$mVldPartNumber."' OR part.jis_supply_group ='".$mVldPartNumber."') AND inventory_master.inventory_qty > 0 LIMIT 1";	
		else
			$mDataQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.requires_inspection AS inspect, location.location_code,part.part_number AS part_number, part.conversion_factor AS conversion_factor FROM inventory_master JOIN part ON inventory_master.part_id=part.id JOIN location on inventory_master.current_location_id=location.id WHERE UPPER(inventory_master.serial_reference)='".$mSerialReference."' AND (part.part_number='".$mPartNumber."' OR part.jis_supply_group ='".$mPartNumber."') AND inventory_master.inventory_qty > 0 LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
		
//		exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".$mPartNumber."-".$mVldPartNumber."-".$mFilter);
		
		$mData = mysqli_query($mConnection,$mDataQuery);
		$i = 0;
		$mInvId = "";
		$mInvQty = 0;
		$mPartConvFactor = 1;
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDataStr = ($mRow['location_code']);
			$mInspect = ($mRow['inspect']);
			$mInvId = ($mRow['id']);
			$mInvQty = ($mRow['inventory_qty']);
			$mPartConvFactor = ($mRow['conversion_factor']);
			$mPartNumber = ($mRow['part_number']);
			$i++;
		}
		if ($i == 0)
		{
			$mErrMsg = "FAIL-Serial is Incorrect";
//			exit($mErrMsg);
//			echo $mErrMsg;
			return $mErrMsg;
		}
		if ($i == 1)
		{
			if ($mInspect == "1")
			{
				$mErrMsg = "FAIL-Serial is flagged for inspection";
			}
			if ($mDataStr != $mVldLocation)
			{
				$mErrMsg = "FAIL-Scanned Serial is in a different Location ";
			}
		}
		if ($mErrMsg == "")
		{
// Apply Conversion Factor
			if ($mPartConvFactor > 0)
				$mInvQty = $mInvQty / $mPartConvFactor;
// Check Scanned Qty against Serial
			if ($mQty > $mInvQty)
				$mErrMsg = "FAIL-Scanned quantity is greater than serial quantity";
		
// Multiple Serials - Prompt for Part Number
			if ($i > 1)
			{
				if ($mErrMsg == "")			
				{
					if ($mTransactionTypeId == "PBTM")
						$mErrMsg = "DATA:serialReferencecontent:".$mSerialReference."::skip5,partNumber::I,toLocationHashCode::HPPPIPH";
					else
						$mErrMsg = "DATA:serialReferencecontent:".$mSerialReference."::skip5,partNumber::I";
				}
			}
		}
	}
		
	if ($mErrMsg == "")
	{
// Get Order Type Settings
		$mDataQuery = "SELECT order_type.full_box_pick AS full_box_pick, order_type.strict_serial AS strict_serial_pick FROM pick_header JOIN document_header ON pick_header.order_header_id=document_header.id JOIN order_type ON document_header.order_type=order_type.order_type_code AND document_header.pick_type=order_type.order_type_extension WHERE pick_header.customer_reference='".$mCustomerReference."' LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
		$mData = mysqli_query($mConnection,$mDataQuery);
		if (mysqli_num_rows($mData) == 0)
		{
			$mDataQuery = "SELECT order_type.full_box_pick AS full_box_pick, order_type.strict_serial AS strict_serial_pick FROM pick_header JOIN document_header ON pick_header.order_header_id=document_header.id JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE pick_header.customer_reference='".$mCustomerReference."' LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
			$mData = mysqli_query($mConnection,$mDataQuery);
		}
//			exit("FAIL-TEST1".$mDataQuery."-".mysqli_num_rows($mData));
		$mStrictSerial = "0";
		$mFullBoxPick = "0";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mStrictSerial = ($mRow['strict_serial_pick']);
			$mFullBoxPick = ($mRow['full_box_pick']);
		}
		if (mysqli_num_rows($mData) == 0)
		{
			if ($mCurrentUser != "")
				$mDataQuery = "SELECT strict_serial_pick FROM company LIMIT 1";
			else
				$mDataQuery = "SELECT strict_serial_pick FROM company WHERE id > 0 LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
			$mData = mysqli_query($mConnection,$mDataQuery);	
			$i = 0;			
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mStrictSerial = ($mRow['strict_serial_pick']);
				$i++;
			}
		}
		if ($mStrictSerial == "1")
		{
			if ($mSerialReference != $mVldSerialReference)
				$mErrMsg = "FAIL-Scanned Serial does not match Prompted Serial ";
		}
// Check Scanned Qty.
		if (($mErrMsg == "") && ($mQty != ""))
		{
			if (($mFullBoxPick == "0") && ($mQty != $mVldQty))
				$mErrMsg = "FAIL-Scanned quantity is incorrect";
		}		
	}
// Test for Piece (non-full-box) pick
	if ($mErrMsg == "")
	{
		if (($mSerialReference != "") && ($mQty == ""))
		{
			if ($mFullBoxPick == "1")
				$mErrMsg = "DATA:sequencingRequestId:".$mInvId.",qtyAllocatedvalue:".$mInvQty.":P";
			else
				if ($mPartNumber == "")
				{
					$mErrMsg = "DATA:sequencingRequestId:".$mInvId.",serialReferencecontent:".$mSerialReference."::skip6,qtyAllocated::I";
				}
				else
				{
					$mErrMsg = "DATA:sequencingRequestId:".$mInvId.",partNumber:".$mPartNumber."::skip6,qtyAllocated::I";
				}
		}
	}
//		exit("FAIL-TEST1".$mDataQuery."-".mysqli_num_rows($mData)."-".$mStrictSerial);

	mysqli_close($mConnection);

	if ($mErrMsg == "")
	{
		$mErrMsg = "OK  -Valid Pick";
	}
//	echo $mErrMsg;
	return $mErrMsg;
}

// Get Next Pick
function getPick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mCustomerReferenceCode,$mAisle,$mConnect,$mToPlt="")
{
	if ($mAisle == "NONE")
	{
		return "OK  -";
	}
	if (($mCustomerReferenceCode != null) && ($mCustomerReferenceCode != ""))
	{
// mysql connection
		$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
		if (!$mConnection)
			return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();
// Order Header
		$mReturn = "";
//		$mDataQuery = "SELECT order_header.id AS id, order_header.document_reference AS document_reference, order_header.time_slot AS time_slot, order_type.pick_by_line FROM order_header LEFT JOIN document_status ON order_header.document_status_id=document_status.id LEFT JOIN order_type ON order_header.order_type=order_type.order_type_code WHERE customer_reference='".$mCustomerReferenceCode."' AND (order_header.document_status_id IS null OR lower(document_status.document_status_code) in('open','allocated')) LIMIT 1";
		$mDataQuery = "SELECT document_header.id AS id, document_header.document_reference_code AS document_reference, document_header.time_slot AS time_slot, document_header.order_type AS order_type, document_header.pick_type AS pick_type , order_type.pick_by_line, order_type.full_box_pick, order_type.transaction_type_code AS transaction_type FROM document_header LEFT JOIN document_status ON document_header.document_status_id=document_status.id LEFT JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE document_header.document_type='RRM Order' AND document_header.customer_reference_code='".$mCustomerReferenceCode."' AND (document_header.document_status_id IS null OR document_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated')) LIMIT 1";		

		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		if (mysqli_num_rows($mData) == 0)
		{
//			$mDataQuery = "SELECT order_header.id AS id, order_header.document_reference AS document_reference, order_header.time_slot AS time_slot, order_type.pick_by_line FROM order_header LEFT JOIN document_status ON order_header.document_status_id=document_status.id LEFT JOIN order_type ON order_header.order_type=order_type.order_type_code WHERE time_slot='".$mCustomerReferenceCode."' AND (order_header.document_status_id IS null OR lower(document_status.document_status_code) in('open','allocated')) ORDER BY order_header.expected_delivery_time";	
			$mDataQuery = "SELECT document_header.id AS id, document_header.document_reference_code AS document_reference, document_header.time_slot AS time_slot, document_header.order_type AS order_type, document_header.pick_type AS pick_type, order_type.pick_by_line AS pick_by_line, order_type.full_box_pick AS full_box_pick, order_type.transaction_type_code AS transaction_type FROM document_header LEFT JOIN document_status ON document_header.document_status_id=document_status.id LEFT JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE document_header.time_slot='".$mCustomerReferenceCode."' AND (document_header.document_status_id IS null OR document_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated')) ORDER BY document_header.expected_delivery";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			$mData = mysqli_query($mConnection,$mDataQuery);
		}
		$mOrderId = "";
		$mTimeSlot = "";
		$mPickByLine = 0;
		$mFullBoxPick = 1;
		$mOrderType = "";
		$mPickType = "";
		$mTransactionType = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			if ($mOrderId != "")
				$mOrderId .= ",";
			$mOrderId .= ($mRow['id']);
			$mDocumentReferenceCode = ($mRow['document_reference']);
			$mOrderType = ($mRow['order_type']);
			$mPickType = ($mRow['pick_type']);
			$mTimeSlot = ($mRow['time_slot']);
			$mPickByLine = ($mRow['pick_by_line']);
			$mFullBoxPick = ($mRow['full_box_pick']);
			$mTransactionType = ($mRow['transaction_type']);
		}
		if ($mOrderId == "")
		{
//			$mDataQuery = "SELECT order_header.id AS id, order_header.order_type AS order_type, order_type.transaction_type_code AS transaction_type FROM order_header LEFT JOIN order_type ON order_header.order_type=order_type.order_type_code WHERE customer_reference='".$mCustomerReferenceCode."' LIMIT 1";
			$mDataQuery = "SELECT document_header.id AS id, document_header.order_type AS order_type, order_type.transaction_type_code AS transaction_type FROM document_header LEFT JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE customer_reference_code='".$mCustomerReferenceCode."' LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mOrderType = ($mRow['order_type']);
				$mTransactionType = ($mRow['transaction_type']);
//				if ($mOrderType == "350")
				if ($mTransactionType != "")
				{
					if ($mToPlt != "")
					{
//						$mTransactionType="PBTP";
						return "OKMS-Pick Complete,CHGTRAN:".$mTransactionType."?DATA:customerReference:,aisleCode:ALL,toPlt:".$mToPlt;
					}
					else
					{
						return("FAIL-No Pick Tasks");
					}
				}
			}
			$mReturn = "DATA:EOD-This Pick is Complete";
//			return "FAIL-Invalid Customer Reference";
			return $mReturn;
		}
		
		mysqli_close($mConnection);
		
		if ($mPickByLine < 0)
			$mPickByLine = 0;
		if ($mFullBoxPick < 0)
			$mFullBoxPick = 1;
		if ($mReturn == "")
		{
// Pick Header
			$mReturn = addUpdatePick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mAutoCreateDoc=null,$mCustomerReferenceCode,$mOrderId,$mTimeSlot);
			if (substr($mReturn,0,4) == "FAIL")
				return $mReturn;
			else
			{
				$mPickHdrId = $mReturn;
// Test for XD Order
				if ($mPickType == "XD")
				{
					$mReturn = getXdPick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mCustomerReferenceCode,$mPickHdrId,$mOrderId,"ALL");
					if (substr($mReturn,0,4) == "FAIL")
						return $mReturn;
//					if ($mReturn == "DATA:EOD")	
//						$mReturn = "DATA:EOD;customerReference;".$mCustomerReferenceCode;
					return $mReturn;
				}
//				return "FAIL-TEST1-".$mReturn;
// Loop Through Returned Pick Header Ids
				$mReturn = getPickDetail($mHost,$mUser,$mPwd,$mDbName,$mOrderId,$mPickHdrId,$mAisle,$mCurrentUser,$mCustomerReferenceCode,$mPickByLine,$mFullBoxPick,$mToPlt);
				if ($mReturn == "")
					$mReturn = "FAIL-Allocation Error";
				if ($mReturn == "DATA:EOD-This Pick is Complete")
				{
// Hard-coding for Kanban Pick
//					if ($mOrderType == "350")
					if ($mTransactionType != "")
					{
//						$mTransactionType="PBTP";
						if ($mToPlt != "")
							return "CHGTRAN:".$mTransactionType."?DATA:customerReference:,aisleCode:ALL,toPlt:".$mToPlt;
						else
							return "FAIL-No Pick Tasks";
					}
					return $mReturn;
				}
				if (substr($mReturn,0,8) == "DATA:EOD")
				{
					$mReturn = allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderId, $mPickHdrId, $mCurrentUser,"");
//					$mReturn = allocateServerOrderLines($mConnect,$mCustomerReferenceCode,$mCurrentUser,"");
					if (substr($mReturn,0,5)!="FAIL-")
					{
						if (substr($mReturn,0,8) != "DATA:EOD")
							$mReturn = getPickDetail($mHost,$mUser,$mPwd,$mDbName,$mOrderId,$mPickHdrId,$mAisle,$mCurrentUser,$mCustomerReferenceCode,$mPickByLine,$mFullBoxPick,$mToPlt);
						if (substr($mReturn,0,8) == "DATA:EOD")
						{
							$mReturn = allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderId, $mPickHdrId, $mCurrentUser,"");
							$mReturn = getPickDetail($mHost,$mUser,$mPwd,$mDbName,$mOrderId,$mPickHdrId,$mAisle,$mCurrentUser,$mCustomerReferenceCode,$mPickByLine,$mFullBoxPick,$mToPlt);
//							if (substr($mReturn,0,8) == "DATA:EOD")
//							{
//								$mReturn = allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderId, $mPickHdrId, $mCurrentUser,"Y");
//								$mReturn = getPickDetail($mHost,$mUser,$mPwd,$mDbName,$mOrderId,$mPickHdrId,$mAisle,$mCurrentUser,$mCustomerReferenceCode,$mPickByLine,$mFullBoxPick,$mToPlt);
//							}
						}
						if (substr($mReturn,0,8) == "DATA:EOD")	
						{
// Hard-coding for Kanban Pick
//							if ($mOrderType == "350")
							if ($mTransactionType != "")
							{
//								$mTransactionType="PBTP";
								if ($mToPlt != "")
									$mReturn = "CHGTRAN:".$mTransactionType."?DATA:customerReference:,aisleCode:ALL,toPlt:".$mToPlt;
								else
									$mReturn = "FAIL-No Pick Tasks";
							}
							else
								$mReturn .= ";customerReference;".$mCustomerReferenceCode;
						}
						elseif ($mReturn == "")
							$mReturn = "FAIL-Allocation Error";
					}
				}
			}
			return $mReturn;
		}
	}
	else
	{
		$mReturn = "FAIL-EOD-No Matching Customer Reference";
	}
	return $mReturn;
}

// Update Pick Header
function addUpdatePick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mAutoCreateDoc=null,$mCustomerReferenceCode,$mOrderHdrId,$mTimeSlot)
{
	$mDocumentReferenceCode = "";
// mysql connection
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();
	
// Create Pick if non exists	
	If ($mAutoCreateDoc != "0")
	{
// Loop Through Order Header Ids.
		$mOrderHdrIds = explode(",",$mOrderHdrId);
		foreach ($mOrderHdrIds as $mOrderHdrId)
		{
// Pick_Header
			$mDataQuery = "SELECT id,document_reference FROM pick_header WHERE order_header_id=".$mOrderHdrId." LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mDocumentReferenceCode = "";
			$mPickHdrId = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mPickHdrId = ($mRow['id']);
				$mDocumentReferenceCode = ($mRow['document_reference']);
			}
// No Pick_header
			if ($mPickHdrId == "")
			{
				if ($mDocumentReferenceCode == "")
					$mDocumentReferenceCode = "PCK999999";			
				$mDataQuery = "Insert INTO pick_header (id, document_reference, customer_reference, order_header_id, document_status_id, date_created, created_by, last_updated_date, last_updated_by) VALUES(DEFAULT,'".$mDocumentReferenceCode."','".$mCustomerReferenceCode."',".$mOrderHdrId.",1,now(),'".$mCurrentUser."',now(),'".$mCurrentUser."')";
				if (!mysqli_query($mConnection,$mDataQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
				$mPickHdrId=mysqli_insert_id($mConnection);
// Create new Pick Document_Ref.
				$mDocumentReferenceCode = "000000".round($mPickHdrId);
				$mDocumentReferenceCode = "PCK".substr($mDocumentReferenceCode,-6);
				$mDataQuery = "UPDATE pick_header SET document_reference='".$mDocumentReferenceCode."', document_status_id=(SELECT document_status.id FROM document_status WHERE lower(document_status.document_status_code)='open') WHERE id=".$mPickHdrId." LIMIT 1";
				if (!mysqli_query($mConnection,$mDataQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
				
//				$mDataQuery = "UPDATE order_header SET order_header.document_status_id = (SELECT id FROM document_status WHERE lower(document_status.document_status_code)='allocated'), order_header.last_updated_date=now(),order_header.last_updated_by='".$mCurrentUser."' WHERE order_header.id=".$mOrderHdrId." LIMIT 1";
				$mDataQuery = "UPDATE document_header SET document_header.document_status_id = (SELECT id FROM document_status WHERE lower(document_status.document_status_code)='allocated'), document_header.last_updated=now(),document_header.last_updated_by='".$mCurrentUser."' WHERE document_header.id=".$mOrderHdrId." LIMIT 1";
				if (!mysqli_query($mConnection,$mDataQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			}
			else
			{
				$mDataQuery = "UPDATE pick_header SET last_updated_date=now(), last_updated_by='".$mCurrentUser."' WHERE id=".$mPickHdrId." LIMIT 1";
				if (!mysqli_query($mConnection,$mDataQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			}
		}
	}
	else
	{
		$i = strpos($mOrderHdrId,",");
		if ($i > 0)
		{
			$mDataQuery = "SELECT id AS id FROM pick_header WHERE order_header_id IN(".$mOrderHdrId.")";
		}
		else
		{
			$mDataQuery = "SELECT id AS id FROM pick_header WHERE order_header_id=".$mOrderHdrId." AND LIMIT 1";
		}
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mPickHdrId = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			if ($mPickHdrId != "")
				$mPickHdrId .= ",";
			$mPickHdrId .= ($mRow['id']);
		}
	}
		
	mysqli_close($mConnection);
	return $mPickHdrId;
}

function getPickDetail($mHost,$mUser,$mPwd,$mDbName,$mOrderHdrId, $mPickHdrId, $mAisle, $mCurrentUser,$mCustomerReferenceCode,$mPickByLine=0,$mFullBoxPick=1,$mToPlt="")
{
	$mReturn = "";
	$mPartId = "";
	$mRdtPrompt = "";
// mysql connection
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();
	
// Get available Pick Body / Detail
	$mDataQuery = "SELECT pick_detail.id AS id, pick_detail.inventory_master_id AS invId, pick_detail.qty_allocated AS qty_allocated, pick_detail.pick_body_id AS pick_body_id, pick_detail.line_no AS line_no, pick_detail.pick_priority AS pick_priority, part.part_number AS part_number, part.rdt_prompt AS rdt_prompt, inventory_master.serial_reference AS serial_reference, inventory_master.date_created AS date_created, location.location_code AS location_code, pick_detail.line_no AS our_line_no";
	$mDataQuery .= " FROM pick_detail";
	$mDataQuery .= " JOIN pick_body ON pick_detail.pick_body_id=pick_body.id";
	$mDataQuery .= " LEFT JOIN part ON pick_detail.part_id=part.id";
	$mDataQuery .= " LEFT JOIN inventory_master ON pick_detail.inventory_master_id=inventory_master.id LEFT JOIN location ON inventory_master.current_location_id=location.id";
// Test for muliple Pick Header Ids (timeslot selection)
	$i = strpos($mPickHdrId,",");
	if ($i > 0)
		$mDataQuery .= " WHERE pick_body.pick_header_id IN(".$mPickHdrId.")";
	else
		$mDataQuery .= " WHERE pick_body.pick_header_id=".$mPickHdrId; 
	$mDataQuery .= " AND (pick_detail.processed=0 OR pick_detail.processed IS null) AND (pick_detail.in_use_by IS null OR pick_detail.in_use_by='' OR pick_detail.in_use_by='".$mCurrentUser."' OR (pick_detail.last_updated_date < DATE_SUB(NOW(), INTERVAL 10 MINUTE)))";
	if (($mAisle == "") || ($mAisle == null))
		$mDataQuery .= " AND (location.aisle_code='' OR location.aisle_code IS null)";
	elseif ($mAisle != "ALL")
		$mDataQuery .= " AND location.aisle_code='".$mAisle."'";
	if ($mPickByLine == 1)
		$mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.pick_priority, pick_detail.line_no, inventory_master.date_created, location.location_code,pick_detail.serial_reference LIMIT 20";
	else
		$mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.pick_priority, pick_detail.line_no, inventory_master.date_created, location.location_code,pick_detail.serial_reference LIMIT 20";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
	
//	return "FAIL-TEST1".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData);
	
	$mPickDtlId = "";
	$mPickBdyId = "";
	$mPickInvId = "";
	$mPickQty = 0;
	$mFullBoxPick="";
	$mPartNumber = "";
	$mRdtPrompt = "";
	$mSerialReference = "";
	$mLocationCode = "";
	$mPickDtlLine = "";
	$mPickOrdLine = "";
	
	if (mysqli_num_rows($mData) == 0)
	{
		$mDataQuery = "SELECT pick_detail.id AS id, pick_detail.inventory_master_id AS invId, pick_detail.pick_body_id AS pick_body_id, pick_detail.qty_allocated AS qty_allocated, pick_detail.qty_scanned AS qty_scanned, pick_detail.part_number AS part_number, pick_detail.serial_reference AS serial_reference, pick_detail.location_code AS location_code, pick_detail.line_no AS our_line_no"; 
		$mDataQuery .= " FROM pick_detail";
		$mDataQuery .= " JOIN pick_body ON pick_detail.pick_body_id=pick_body.id";
		$mDataQuery .= " LEFT JOIN location ON pick_detail.location_code=location.location_code";
// Test for muliple Pick Header Ids (timeslot selection)
		$i = strpos($mPickHdrId,",");
		if ($i > 0)
			$mDataQuery .= " WHERE pick_body.pick_header_id IN(".$mPickHdrId.")";
		else
			$mDataQuery .= " WHERE pick_body.pick_header_id=".$mPickHdrId; 
		$mDataQuery .= " AND (pick_detail.processed=0 OR pick_detail.processed IS null) AND (pick_detail.in_use_by IS null OR pick_detail.in_use_by='' OR pick_detail.in_use_by='".$mCurrentUser."' OR (pick_detail.last_updated_date < DATE_SUB(NOW(), INTERVAL 10 MINUTE)))";
		if (($mAisle != "ALL") && ($mAisle != ""))
			$mDataQuery .= " AND location.aisle_code='".$mAisle."'";
		if ($mPickByLine == 1)
			$mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.line_no, pick_detail.pick_priority,location.location_code,pick_detail.serial_reference LIMIT 20";
		else
			$mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.pick_priority, pick_detail.line_no,location.location_code,pick_detail.serial_reference LIMIT 20";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
	}
	
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mPickDtlId = ($mRow['id']);
		$mPickOrdLine = ($mRow['our_line_no']);
		$mPickDtlLine = ($mRow['pick_priority']);
		$mPickBdyId = ($mRow['pick_body_id']);
		$mPickInvId = ($mRow['invId']);
		$mPickQty = ($mRow['qty_allocated']);
		$mScanQty = ($mRow['qty_scanned']);
		$mPartNumber = ($mRow['part_number']);
		$mRdtPrompt = ($mRow['rdt_prompt']);
		$mSerialReference = ($mRow['serial_reference']);
		$mLocationCode = ($mRow['location_code']);
		$mConversionFactor = 1;
//		$mFullBoxPick = ($mRow['full_box_pick']);
// Check if Inventory is still available
		if ($mPickDtlId != "")
		{
			$mInvId = "";
			if ($mPickInvId != "")
			{
				$mDataQuery = "SELECT inventory_master.id AS id,location.location_code AS location_code, location.location_hash AS hash_code, inventory_qty AS inventory_qty, part.conversion_factor AS conversion_factor FROM inventory_master JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id JOIN location ON inventory_master.current_location_id=location.id LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.id=".$mPickInvId." AND inventory_master.inventory_qty>0 AND inventory_master.requires_inspection=0 AND (inventory_master.inventory_status_id IS null OR inventory_status.non_pickable<>1) LIMIT 1";
				if (!mysqli_query($mConnection,$mDataQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
				$mData = mysqli_query($mConnection,$mDataQuery);
			
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mInvId = ($mRow['id']);
					$mLocationCode = ($mRow['location_code']);
					$mLocationHashCode = ($mRow['hash_code']);
					$mConversionFactor = ($mRow['conversion_factor']);
					$mInvQty = ($mRow['inventory_qty']);
				}
			}
			if (($mInvId != "") && ($mPickQty > $mInvQty))
			{
				$mPickQty = $mInvQty;
				$mDataQuery = "UPDATE pick_detail SET qty_allocated = ".$mPickQty." WHERE id=".$mPickDtlId." LIMIT 1";
				if (!mysqli_query($mConnection,$mDataQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			}
			if (($mInvId == "") || ($mPickQty <= 0))
			{
				$mDataQuery = "UPDATE pick_detail SET qty_allocated=0,processed=1,last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPickDtlId." LIMIT 1";
				if (!mysqli_query($mConnection,$mDataQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
				if ($mPickBdyId != "")
				{
//					$mDataQuery = "UPDATE pick_body SET qty_allocated=(SELECT sum(qty_allocated+qty_scanned) FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id) WHERE pick_body.id=".$mPickBdyId." LIMIT 1";
					$mDataQuery = "UPDATE pick_body SET qty_allocated=qtyu_allocated-".$mPickQty."WHERE pick_body.id=".$mPickBdyId." LIMIT 1";
					if (!mysqli_query($mConnection,$mDataQuery))
						return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
				}
				$mPickDtlId = "";
			}
			else
			{
				break;
			}
		}
	}
//	return "FAIL-TEST1".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData);
	
//	return ("FAIL-TEST1".$mPartId.":".$mAlcQty);
// No Valid Allocation found
	if ($mPickDtlId == "")
	{
		$mDataQuery = "SELECT id FROM pick_body WHERE pick_header_id =".$mPickHdrId." LIMIT 1";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		if (mysqli_num_rows($mData) == 0)
			return("DATA:EOD");
		$mDataQuery = "SELECT id FROM pick_body WHERE pick_header_id =".$mPickHdrId." AND processed=0 LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		if (mysqli_num_rows($mData) == 0)
			$mReturn = "DATA:EOD-This Pick is Complete";
		else
			$mReturn = "DATA:EOD-No Stock for this Pick";
	}
	else
	{
		$mDataQuery = "UPDATE pick_detail SET pick_detail.in_use_by='".$mCurrentUser."',last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE pick_detail.id=".$mPickDtlId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		if ($mPickQty != 0 && $mConversionFactor != 0)
			$mPickQty = $mPickQty / $mConversionFactor;
// return Pick Details to the screen
		if ($mToPlt == "")
			$mReturn = "DATA:customerReference:".$mCustomerReferenceCode.",aisleCode:".$mAisle.",fromLocationHashCodecontent:".$mLocationCode.",serialReferencecontent:".$mSerialReference.",partNumbervalue:".$mPartNumber.":HHPPPPH,qtyAllocatedvalue:".$mPickQty.":HHPPPPH,allocationId:".$mPickDtlId.",sequencingRequestId:".$mPickInvId.",lineNo:".$mPickOrdLine.",rdtMessage:".$mRdtPrompt;
		else
			$mReturn = "DATA:customerReference:".$mCustomerReferenceCode.",aisleCode:".$mAisle.",fromLocationHashCodecontent:".$mLocationCode.",serialReferencecontent:".$mSerialReference.",partNumbervalue:".$mPartNumber.":HHPPPPH,qtyAllocatedvalue:".$mPickQty.":HHPPPPH,allocationId:".$mPickDtlId.",sequencingRequestId:".$mPickInvId.",lineNo:".$mPickOrdLine.",rdtMessage:".$mRdtPrompt."::skip3";
	}
	mysqli_close($mConnection);
	return $mReturn;
}

// allocate using Serer Program
function allocateServerOrderLines($mConnect, $mCustomerReferenceCode, $mCurrentUser,$mAll="")
{
// 	http://172.16.1.39:8085/pick/allocate?customerReference=FirstTest&userId=userId

// POST Operation
	try
	{
// Replace default Server Tomcat Service
		$mConnectp = str_replace(":8081/vantec",":8085/pick",$mConnect);
		$mJsonObject="";
		$mJsonObject="customerReference=".$mCustomerReferenceCode."&userId=".$mCurrentUser;
		$ch = curl_init();
		curl_setopt($ch, CURLOPT_URL, $mConnectp."allocate"."?".$mJsonObject);
		curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json')); 
		curl_setopt($ch, CURLOPT_POST, 1);
		curl_setopt($ch, CURLOPT_POSTFIELDS, $mJsonObject);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
		$mResult = curl_exec($ch);
		$mErrors = curl_error($ch);
		$mResponse = curl_getinfo($ch, CURLINFO_HTTP_CODE);

		curl_close($ch);
		if ($mResponse != "200")
			$mErrMsg = ("FAIL-Unexpected System Error -".$mResult.":".$mResponse.":".$mErrors."-".$mConnect."-".$mJsonObject);
		else
		{
			$mErrMsg = "OK  - Allocated";
			return $mErrMsg;
//			print_r("FAIL-Message Code=".$mJsonObject.":".$mResult.":".$mResponse.":".$mErrors);
		}
	}
	catch (Exception $e)
	{
		$mErrMsg = ("FAIL-Connection Error at - ".$mConnect.$mFilter.":".$e);
	}
}

// Allocate Outstanding Order Lines
function allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderHdrId, $mPickHdrId, $mCurrentUser,$mAll="")
{
	$mReturn = "DATA:EOD";
	$mAlcInvId = "";
	$mReqQty = 0;
// mysql connection
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();
	
// Get Full Box Pick Flag based on Order Type
//	$mDataQuery = "SELECT order_header.id AS id, order_header.order_type AS order_type, order_type.full_box_pick AS full_box_pick, order_type.transaction_type_code AS transaction_type FROM order_header JOIN order_type ON order_header.order_type=order_type.order_type_code WHERE order_header.id='".$mOrderHdrId."' LIMIT 1";
	$mDataQuery = "SELECT document_header.order_type AS order_type, document_header.pick_type AS pick_type, order_type.id AS id, order_type.full_box_pick AS full_box_pick, order_type.pick_by_line AS pick_by_line, order_type.order_type_extension AS order_type_extension FROM document_header JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE document_header.id='".$mOrderHdrId."' LIMIT 1";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mFullBoxPick = "";
	$mPickByLine = "";
	$mOrderType = "";
	$mPickType = "";
	$mOrderTypeExt = "";
	$i = 0;
	while($mRow = mysqli_fetch_assoc($mData))  
	{
		$mFullBoxPick = ($mRow['full_box_pick']);
		$mPickByLine = ($mRow['pick_by_line']);
		$mOrderType = ($mRow['order_type']);
		$mPickType = ($mRow['pick_type']);
		$mOrderTypeExt = ($mRow['order_type_extension']);
		$i = $i + 1;
	}
//	return "FAIL-TEST1 ".$mDataQuery."-".mysqli_num_rows($mData)."-".$mOrderTypeExt;
// Pick Header
	$mDataQuery = "SELECT pick_header.document_reference AS document_reference, pick_header.customer_reference AS customer_reference FROM pick_header WHERE id=".$mPickHdrId." LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mPickDocumentReference = "";
	$mPickCustomerReference = "";
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mPickDocumentReference = ($mRow['document_reference']);
		$mPickCustomerReference = ($mRow['customer_reference']);
	}	
// Get Unallocated Order Body
	if ($mPickByLine == "1")
//		$mDataQuery = "SELECT order_body.id AS id, order_body.part_id, part.part_number, part.vendor_part_code AS vendor_part_code, order_body.qty_expected AS order_qty, sequence AS priority, order_body.line_no AS line_no FROM order_body JOIN part ON order_body.part_id=part.id WHERE order_body.order_header_id=".$mOrderHdrId." AND (order_body.qty_expected>order_body.qty_transacted OR order_body.qty_transacted IS null) AND NOT EXISTS(SELECT id FROM pick_body WHERE order_body.id=pick_body.order_body_id) ORDER BY order_header_id,order_body.sequence,order_body.line_no";
		$mDataQuery = "SELECT document_body.id AS id, document_body.part_id, part.part_number, part.jis_supply_group AS vendor_part_code, document_body.qty_expected AS order_qty, 1 AS priority, 1 AS line_no FROM document_body JOIN part ON document_body.part_id=part.id WHERE document_body.document_header_id=".$mOrderHdrId." AND (document_body.qty_expected>document_body.qty_transacted OR document_body.qty_transacted IS null) AND NOT EXISTS(SELECT id FROM pick_body WHERE document_body.id=pick_body.order_body_id) ORDER BY document_header_id,document_body.id";
	else
//		$mDataQuery = "SELECT order_body.id AS id, order_body.part_id, part.part_number, part.vendor_part_code AS vendor_part_code, order_body.qty_expected AS order_qty, sequence AS priority, order_body.line_no AS line_no FROM order_body JOIN part ON order_body.part_id=part.id WHERE order_body.order_header_id=".$mOrderHdrId." AND (order_body.qty_expected>order_body.qty_transacted OR order_body.qty_transacted IS null) AND NOT EXISTS(SELECT id FROM pick_body WHERE order_body.id=pick_body.order_body_id) ORDER BY order_header_id,order_body.sequence,order_body.line_no";
		$mDataQuery = "SELECT document_body.id AS id, document_body.part_id, part.part_number, part.jis_supply_group AS vendor_part_code, document_body.qty_expected AS order_qty, 1 AS priority, 1 AS line_no FROM document_body JOIN part ON document_body.part_id=part.id WHERE document_body.document_header_id=".$mOrderHdrId." AND (document_body.qty_expected>document_body.qty_transacted OR document_body.qty_transacted IS null) AND NOT EXISTS(SELECT id FROM pick_body WHERE document_body.id=pick_body.order_body_id) ORDER BY document_header_id,document_body.id";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
// If no Document Detail records try Document Body.
	if (($mPickByLine == "1") && (mysqli_num_rows($mData) == 0))
	{
//		$mDataQuery = "SELECT order_body.id AS id, order_body.part_id, part.part_number, part.vendor_part_code AS vendor_part_code, order_body.qty_expected AS order_qty, sequence AS priority, order_body.line_no AS line_no FROM order_body JOIN part ON order_body.part_id=part.id WHERE order_body.order_header_id=".$mOrderHdrId." AND (order_body.qty_expected>order_body.qty_transacted OR order_body.qty_transacted IS null) AND NOT EXISTS(SELECT id FROM pick_body WHERE order_body.id=pick_body.order_body_id) ORDER BY order_header_id,order_body.sequence,order_body.line_no";
		$mDataQuery = "SELECT document_body.id AS id, document_body.part_id, part.part_number, part.jis_supply_group AS vendor_part_code, document_body.qty_expected AS order_qty, 1 AS priority, 1 AS line_no FROM document_body JOIN part ON document_body.part_id=part.id WHERE document_body.document_header_id=".$mOrderHdrId." AND (document_body.qty_expected>document_body.qty_transacted OR document_body.qty_transacted IS null) AND NOT EXISTS(SELECT id FROM pick_body WHERE document_body.id=pick_body.order_body_id) ORDER BY document_header_id,document_body.id";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
	}
//	return "FAIL-TEST2 ".$mDataQuery."-".mysqli_num_rows($mData);

// Create Pick Bodies
	$mBdyId = "";
	$mPartId = "";
	if (mysqli_num_rows($mData)>0)
	{
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mBdyId = ($mRow['id']);
			$mPartId = ($mRow['part_id']);
			$mPartNumber = ($mRow['part_number']);
			$mVendorPart = ($mRow['vendor_part_code']);
			
// If document_body has no part_id find matching part record
			if (($mRow['part_id']) == null)
			{
				$mPartQuery = "SELECT id AS id, part_number AS part_number, jis_supply_group AS vendor_part_code FROM part WHERE part_number LIKE('%".$mRow['part_number']."%' LIMIT 1";
				if (!mysqli_query($mConnection,$mPartQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mPartQuery;
				$mPartData = mysqli_query($mConnection,$mPartQuery);
				while($mPartRow = mysqli_fetch_assoc($mPartData)) 
				{
					$mPartId = ($mRow['id']);
					$mPartNumber = ($mRow['part_number']);
					$mVendorPart = ($mRow['vendor_part_code']);
				}
			}
			
			if (($mPartId != "") && ($mBdyId != ""))
			{
				$mPickQuery = "INSERT INTO pick_body (id,part_id,part_number,order_body_id,pick_header_id,customer_reference,document_reference,qty_expected,qty_transacted,qty_allocated,qty_shortage,processed,date_created,created_by,last_updated_date,last_updated_by) VALUES(DEFAULT,".$mPartId.",'".$mPartNumber."',".$mRow['id'].",".$mPickHdrId.",'".$mPickCustomerReference."','".$mPickDocumentReference."',".$mRow['order_qty'].",0,0,".$mRow['order_qty'].",0,now(),'".$mCurrentUser."_RDT',now(),'".$mCurrentUser."')";
				if (!mysqli_query($mConnection,$mPickQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
				$mPickBdyId=mysqli_insert_id($mConnection);
			}
		}	
	}
// Check for Unallocated Pick Bodies
	if ($mPickByLine == "1")
//		$mDataQuery = "SELECT order_body.id AS id, order_body.part_id AS part_id, order_body.line_no AS line_no, order_body.qty_expected AS order_qty, CASE WHEN order_body.qty_transacted IS null THEN 0 ELSE order_body.qty_transacted END AS qty_transacted, order_body.qty_expected AS qty_expected, order_body.sequence AS priority, 'UNKNOWN' AS serial_reference, part.part_number AS part_number, part.vendor_part_code AS vendor_part_code FROM order_body JOIN part on order_body.part_id=part.id WHERE order_body.order_header_id=".$mOrderHdrId." AND (order_body.qty_expected>order_body.qty_transacted OR order_body.qty_transacted IS null) AND EXISTS(SELECT id FROM pick_body WHERE order_body.id=pick_body.order_body_id AND(pick_body.processed=0 OR pick_body.processed IS null) AND (pick_body.qty_expected>(select sum(CASE WHEN qty_scanned<>0 THEN qty_scanned ELSE qty_allocated END) FROM pick_detail WHERE pick_body.id=pick_detail.pick_body_id) OR NOT EXISTS(SELECT id FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id))) ORDER BY order_header_id, order_body.sequence, order_body.line_no";
//		$mDataQuery = "SELECT document_body.id AS id, document_body.part_id AS part_id, document_detail.our_detail_no AS line_no, document_body.qty_expected AS order_qty, CASE WHEN document_detail.qty_transacted IS null THEN 0 ELSE document_detail.qty_transacted END AS qty_transacted, document_detail.qty_expected AS qty_expected, CASE WHEN document_detail.sequence = '' THEN document_detail.tapos ELSE document_detail.sequence END AS priority, document_detail.serial_reference AS serial_reference, part.part_number AS part_number, part.jis_supply_group AS vendor_part_code FROM document_body JOIN document_detail ON document_detail.document_body_id=document_body.id JOIN part on document_body.part_id=part.id WHERE document_body.document_header_id=".$mOrderHdrId." AND (document_detail.qty_expected>document_detail.qty_transacted OR document_detail.qty_transacted IS null) AND EXISTS(SELECT id FROM pick_body WHERE document_body.id=pick_body.order_body_id AND(pick_body.processed=0 OR pick_body.processed IS null) AND (pick_body.qty_expected>(select sum(CASE WHEN qty_scanned<>0 THEN qty_scanned ELSE qty_allocated END) FROM pick_detail WHERE pick_body.id=pick_detail.pick_body_id) OR NOT EXISTS(SELECT id FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id))) ORDER BY document_header_id, ABS(document_detail.sequence), document_detail.our_detail_no,line_no";
		$mDataQuery = "SELECT document_body.id AS id, document_body.part_id AS part_id, document_detail.our_detail_no AS line_no, document_body.qty_expected AS order_qty, CASE WHEN document_detail.qty_transacted IS null THEN 0 ELSE document_detail.qty_transacted END AS qty_transacted, document_detail.qty_expected AS qty_expected, CASE WHEN document_detail.sequence = '' THEN document_detail.tapos ELSE document_detail.sequence END AS priority, document_detail.serial_reference AS serial_reference, part.part_number AS part_number, part.jis_supply_group AS vendor_part_code FROM document_body JOIN document_detail ON document_detail.document_body_id=document_body.id JOIN part on document_body.part_id=part.id JOIN pick_body ON document_body.id=pick_body.order_body_id WHERE document_body.document_header_id=".$mOrderHdrId." AND (document_detail.qty_expected>document_detail.qty_transacted OR document_detail.qty_transacted IS null) AND (pick_body.processed=0 OR pick_body.processed IS null) AND (pick_body.qty_expected>pick_body.qty_transacted) ORDER BY document_header_id, ABS(document_detail.sequence), document_detail.our_detail_no,line_no";
	else
//		$mDataQuery = "SELECT order_body.id AS id, order_body.part_id AS part_id, order_body.line_no AS line_no, order_body.qty_expected AS order_qty, CASE WHEN order_body.qty_transacted IS null THEN 0 ELSE order_body.qty_transacted END AS qty_transacted, order_body.qty_expected AS qty_expected, order_body.sequence AS priority, 'UNKNOWN' AS serial_reference, part.part_number AS part_number, part.vendor_part_code AS vendor_part_code FROM order_body JOIN part on order_body.part_id=part.id WHERE order_body.order_header_id=".$mOrderHdrId." AND (order_body.qty_expected>order_body.qty_transacted OR order_body.qty_transacted IS null) AND EXISTS(SELECT id FROM pick_body WHERE order_body.id=pick_body.order_body_id AND(pick_body.processed=0 OR pick_body.processed IS null) AND (pick_body.qty_expected>(select sum(CASE WHEN qty_scanned<>0 THEN qty_scanned ELSE qty_allocated END) FROM pick_detail WHERE pick_body.id=pick_detail.pick_body_id) OR NOT EXISTS(SELECT id FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id))) ORDER BY order_header_id, order_body.sequence, order_body.line_no";
//		$mDataQuery = "SELECT document_body.id AS id, document_body.part_id AS part_id, document_detail.our_detail_no AS line_no, document_body.qty_expected AS order_qty, CASE WHEN document_detail.qty_transacted IS null THEN 0 ELSE document_detail.qty_transacted END AS qty_transacted, document_detail.qty_expected AS qty_expected, CASE WHEN document_detail.sequence = '' THEN document_detail.tapos ELSE document_detail.sequence END AS priority, document_detail.serial_reference AS serial_reference, part.part_number AS part_number, part.jis_supply_group AS vendor_part_code FROM document_body JOIN document_detail ON document_detail.document_body_id=document_body.id JOIN part on document_body.part_id=part.id WHERE document_body.document_header_id=".$mOrderHdrId." AND (document_detail.qty_expected>document_detail.qty_transacted OR document_detail.qty_transacted IS null) AND EXISTS(SELECT id FROM pick_body WHERE document_body.id=pick_body.order_body_id AND(pick_body.processed=0 OR pick_body.processed IS null) AND (pick_body.qty_expected>(select sum(CASE WHEN qty_scanned<>0 THEN qty_scanned ELSE qty_allocated END) FROM pick_detail WHERE pick_body.id=pick_detail.pick_body_id) OR NOT EXISTS(SELECT id FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id))) ORDER BY document_header_id, ABS(document_detail.sequence), document_detail.our_detail_no,line_no";
		$mDataQuery = "SELECT document_body.id AS id, document_body.part_id AS part_id, document_detail.our_detail_no AS line_no, document_body.qty_expected AS order_qty, CASE WHEN document_detail.qty_transacted IS null THEN 0 ELSE document_detail.qty_transacted END AS qty_transacted, document_detail.qty_expected AS qty_expected, CASE WHEN document_detail.sequence = '' THEN document_detail.tapos ELSE document_detail.sequence END AS priority, document_detail.serial_reference AS serial_reference, part.part_number AS part_number, part.jis_supply_group AS vendor_part_code FROM document_body JOIN document_detail ON document_detail.document_body_id=document_body.id JOIN part on document_body.part_id=part.id JOIN pick_body ON document_body.id=pick_body.order_body_id WHERE document_body.document_header_id=".$mOrderHdrId." AND (document_detail.qty_expected>document_detail.qty_transacted OR document_detail.qty_transacted IS null) AND (pick_body.processed=0 OR pick_body.processed IS null) AND (pick_body.qty_expected>pick_body.qty_transacted) ORDER BY document_header_id, ABS(document_detail.sequence), document_detail.our_detail_no,line_no";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
	
//	if (($mPickByLine == "1") && (mysqli_num_rows($mData) == 0))
	if (mysqli_num_rows($mData) == 0)
	{
//		$mDataQuery = "SELECT order_body.id AS id, order_body.part_id AS part_id, order_body.line_no AS line_no, order_body.qty_expected AS order_qty, order_body.qty_expected AS qty_expected, CASE WHEN order_body.qty_transacted IS null THEN 0 ELSE order_body.qty_transacted END AS qty_transacted, order_body.sequence AS priority, 'UNKNOWN' AS serial_reference, part.part_number AS part_number, part.vendor_part_code AS vendor_part_code FROM order_body JOIN part on order_body.part_id=part.id WHERE order_body.order_header_id=".$mOrderHdrId." AND (order_body.qty_expected>order_body.qty_transacted OR order_body.qty_transacted IS null) AND EXISTS(SELECT id FROM pick_body WHERE order_body.id=pick_body.order_body_id AND (pick_body.processed=0 OR pick_body.processed IS null) AND (pick_body.qty_expected>(select sum(CASE WHEN qty_scanned<>0 THEN qty_scanned ELSE qty_allocated END) FROM pick_detail WHERE pick_body.id=pick_detail.pick_body_id) OR NOT EXISTS(SELECT id FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id))) ORDER BY order_header_id, order_body.sequence, order_body.line_no";
//		$mDataQuery = "SELECT document_body.id AS id, document_body.part_id AS part_id, document_body.our_line_no AS line_no, document_body.qty_expected AS order_qty, document_body.qty_expected AS qty_expected, CASE WHEN document_body.qty_transacted IS null THEN 0 ELSE document_body.qty_transacted END AS qty_transacted, 1 AS priority, 'UNKNOWN' AS serial_reference, part.part_number AS part_number, part.jis_supply_group AS vendor_part_code FROM document_body JOIN part on document_body.part_id=part.id WHERE document_body.document_header_id=".$mOrderHdrId." AND (document_body.qty_expected>document_body.qty_transacted OR document_body.qty_transacted IS null) AND EXISTS(SELECT id FROM pick_body WHERE document_body.id=pick_body.order_body_id AND (pick_body.processed=0 OR pick_body.processed IS null) AND (pick_body.qty_expected>(select sum(CASE WHEN qty_scanned<>0 THEN qty_scanned ELSE qty_allocated END) FROM pick_detail WHERE pick_body.id=pick_detail.pick_body_id) OR NOT EXISTS(SELECT id FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id))) ORDER BY document_header_id, wepos, line_no";
		$mDataQuery = "SELECT document_body.id AS id, document_body.part_id AS part_id, document_body.our_line_no AS line_no, document_body.qty_expected AS order_qty, document_body.qty_expected AS qty_expected, CASE WHEN document_body.qty_transacted IS null THEN 0 ELSE document_body.qty_transacted END AS qty_transacted, 1 AS priority, 'UNKNOWN' AS serial_reference, part.part_number AS part_number, part.jis_supply_group AS vendor_part_code FROM document_body JOIN part on document_body.part_id=part.id JOIN pick_body ON document_body.id=pick_body.order_body_id WHERE document_body.document_header_id=".$mOrderHdrId." AND (document_body.qty_expected>document_body.qty_transacted OR document_body.qty_transacted IS null) AND (pick_body.processed=0 OR pick_body.processed IS null) AND (pick_body.qty_expected>pick_body.qty_transacted) ORDER BY document_header_id, wepos ,line_no";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	}
	
//	return "FAIL-TEST2 ".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData);
	$mBdyId = "";
	$mBdyQty = 0;
	$mBdyOrderQty = 0;
	$mBdyQtyExpected = 0;
	$mBdyQtyTransacted = 0;
	$mBdyPartId = "";
	$mBdyPartNumber = "";
	$mBdyLineNo = 1;
	$mPriority = 1;
	$mVendorPart = "";
	$mDtlSerialRef = "";
	
//	return "FAIL-TEST1 ".$mDataQuery."-".mysqli_num_rows($mData);
	
	if (mysqli_num_rows($mData) == 0)
	{
//		$mReturn = "DATA:EOD";
		return $mReturn;
	}
//	$mReturn = $mOrderHdrId."-".mysqli_num_rows($mData)."-";
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mBdyId = ($mRow['id']);
		$mBdyPartId = ($mRow['part_id']);
		$mBdyOrderQty = ($mRow['order_qty']);
		$mBdyQtyExpected = ($mRow['qty_expected']);
		$mBdyQtyTransacted = ($mRow['qty_transacted']);
		$mBdyPartNumber = ($mRow['part_number']);
		$mBdyLineNo = ($mRow['line_no']);
		$mPriority = ($mRow['priority']);
		$mVendorPart = ($mRow['vendor_part_code']);
		$mDtlSerialRef = ($mRow['serial_reference']);
// Set Default Return Message
//		$mReturn = "FAIL-No Stock ".$mBdyPartNumber;
// Outs. Qty
		$mBdyQty = $mBdyQtyExpected-$mBdyQtyTransacted;

//		$mReturn .= "-".$mBdyId."-".$mBdyQty."-".$mBdyPartId;

// Get Pickable Inventory
		if ($mBdyQty > 0)
		{
// Test for Jis Order - Allocate Exact Serial if available - otherwise don't allocate
			$mInvQuery = "SELECT document_detail.serial_reference AS serial_reference FROM document_detail WHERE document_detail.document_body_id=".$mBdyId." AND EXISTS(SELECT id FROM part WHERE part.id=".$mBdyPartId." AND part.jis_supply_group <> '') LIMIT 1";
			if (!mysqli_query($mConnection,$mInvQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
			$mInvData = mysqli_query($mConnection,$mInvQuery);
			if (mysqli_num_rows($mInvData) > 0)
			{
				$mInvRow = mysqli_fetch_assoc($mInvData);
				$mDtlSerialRef = ($mInvRow['serial_reference']);
				$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id JOIN part ON inventory_master.part_id=part.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mBdyPartId." AND inventory_master.serial_reference='".$mDtlSerialRef."' AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND (inventory_master.inventory_status_id IS null OR inventory_status.non_pickable<>1) ORDER BY inventory_master.date_created, location.location_code,inventory_master.inventory_qty";
				if (!mysqli_query($mConnection,$mInvQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
				$mInvData = mysqli_query($mConnection,$mInvQuery);
				$mTest="JIS";
			}
// Pruefcude / Trial-Part Allocation
			elseif (strtolower($mOrderTypeExt) == "pc")
			{
				$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id JOIN pick_group ON location.pick_group_id=pick_group.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mBdyPartId." AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND (inventory_master.inventory_status_id IS null OR (lower(inventory_status.inventory_status_code) IN('pc','pc') AND inventory_status.non_pickable<>1)) ORDER BY inventory_master.date_created, location.location_code,inventory_master.inventory_qty";
				if (!mysqli_query($mConnection,$mInvQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
				$mInvData = mysqli_query($mConnection,$mInvQuery);				
				if (mysqli_num_rows($mInvData) == 0)
				{
					$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mBdyPartId." AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND (inventory_master.inventory_status_id IS null OR (lower(inventory_status.inventory_status_code) IN('pc','pc') AND inventory_status.non_pickable<>1)) ORDER BY inventory_master.date_created, location.location_code,inventory_master.inventory_qty";
					if (!mysqli_query($mConnection,$mInvQuery))
						return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
					$mInvData = mysqli_query($mConnection,$mInvQuery);
				}
				$mTest = "_PRUEFCUBE";
//				return ("FAIL-TEST1-".$mInvQuery);
			}
// If Vendor Part Code exists only allocate exact serial
			elseif (($mVendorPart != "") && ($mVendorPart != null))
			{
				$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mBdyPartId." AND inventory_master.inventory_qty >0 AND (inventory_master.serial_reference='".$mDtlSerialRef."' OR locate(inventory_master.serial_reference,'".$mDtlSerialRef."')>0) AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) IN('ok','ok')) LIMIT 1";
				if (!mysqli_query($mConnection,$mInvQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
				$mInvData = mysqli_query($mConnection,$mInvQuery);
//				return ("FAIL-TEST1-".$mInvQuery."-".mysqli_num_rows($mInvData));
			}
// Standard Allocation
			else
			{
//				$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id JOIN pick_group ON location.pick_group_id=pick_group.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mBdyPartId." AND inventory_master.serial_reference IS NOT null AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND inventory_status.non_pickable<>1 AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) IN('ok','ok')) ORDER BY inventory_master.date_created, inventory_master.location_code LIMIT 20";
				$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master"; 
				$mInvQuery .=" JOIN location ON inventory_master.current_location_id=location.id";
				$mInvQuery .=" JOIN part ON inventory_master.part_id=part.id JOIN pick_group ON location.pick_group_id=pick_group.id";
				$mInvQuery .=" LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
				$mInvQuery .=" LEFT JOIN location_column ON location.column_code=location_column.column_code";
				$mInvQuery .=" LEFT JOIN location_type ON location.location_type_id=location_type.id";
				$mInvQuery .=" WHERE inventory_master.part_id=".$mBdyPartId;
				if ($mAllocateNoRan=="0")
				{
					$mInvQuery .= " AND inventory_master.ran_or_order<>'NO-RAN'";
				}
//				$mInvQuery .=" AND inventory.master.requires_decant=0";
				$mInvquery .=" AND inventory_master.serial_reference IS NOT null AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty";
				$mInvQuery .=" AND inventory_master.requires_inspection=0";
				$mInvQuery .=" AND lower(location_type.location_area)<>'work' AND inventory_status.non_pickable<>1";
				$mInvQuery .=" AND (inventory_master.inventory_status_id IS null";
				$mInvQuery .=" OR lower(inventory_status.inventory_status_code) IN('ok','ok'))";
				$mInvQuery .= " ORDER BY inventory_master.date_created, location.location_code,inventory_master.inventory_qty";
//				$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id JOIN pick_group ON location.pick_group_id=pick_group.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mBdyPartId." AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>(SELECT SUM(CASE WHEN pick_detail.qty_allocated IS NOT null THEN pick_detail.qty_allocated ELSE 0 END) FROM pick_detail WHERE inventory_master.id=pick_detail.inventory_master_id AND (pick_detail.processed=0 OR pick_detail.processed IS null)) AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND inventory_status.non_pickable<>1 AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) IN('ok','ok')) ORDER BY inventory_master.date_created, location.location_code,inventory_master.inventory_qty";
				if (!mysqli_query($mConnection,$mInvQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
				$mInvData = mysqli_query($mConnection,$mInvQuery);
				$mTest = "_PICKFACE";
				if (mysqli_num_rows($mInvData) == 0)
				{
					$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mBdyPartId." AND inventory_master.serial_reference IS NOT null AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND inventory_status.non_pickable<>1 AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) IN('ok','ok')) ORDER BY inventory_master.date_created, inventory_master.location_code LIMIT 20";
//					$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mBdyPartId." AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>(SELECT SUM(CASE WHEN pick_detail.qty_allocated IS NOT null THEN pick_detail.qty_allocated ELSE 0 END) FROM pick_detail WHERE inventory_master.id=pick_detail.inventory_master_id AND (pick_detail.processed=0 OR pick_detail.processed IS null)) AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND inventory_status.non_pickable<>1 AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) IN('ok','ok')) ORDER BY inventory_master.date_created, location.location_code,inventory_master.inventory_qty";
					if (!mysqli_query($mConnection,$mInvQuery))
						return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
					$mInvData = mysqli_query($mConnection,$mInvQuery);
					$mTest="_NOTPICKFACE";
				}
			}
			
//			return ("FAIL-TEST1-".$mInvQuery."-".mysqli_num_rows($mInvData));
			
			$mAlcInvId = "";
			$mInvQty = 0;
			$mInvAllocatedQty = 0;
			$mBdyPartNumber = "";
			$mInvSerialReference = "";
			$mInvRanOrder = "";
			$mInvLocationCode = "";
			$mInvPickPriority = 999;
			
			while($mInvRow = mysqli_fetch_assoc($mInvData)) 
			{
				$mAlcInvId = ($mInvRow['id']);
				$mInvQty = ($mInvRow['inventory_qty']);
				$mInvAllocatedQty = ($mInvRow['allocated_qty']);
				$mBdyPartNumber = ($mInvRow['part_number']);
				$mInvSerialReference = ($mInvRow['serial_reference']);
				$mInvRanOrder = ($mInvRow['ran_order']);
				$mInvLocationCode = ($mInvRow['location_code']);
				$mInvPickPriority = ($mInvRow['pick_priority']);
				
				$mPickAllocatedQty = 0;
				$mPickAllocatedQty = $mInvAllocatedQty;
				if ($mPickAllocatedQty > $mInvQty)
					$mPickAllocatedQty = $mInvQty;
// Available Inventory Qty.
				$mInvQty = $mInvQty - $mPickAllocatedQty;
// Existing Order Allocated Qty.
				$mOrdAllocatedQty = $mBdyQty;
// Calculate Pick / Required Qty.
// Full Box Pick - take all Serial Inventory
				if ($mFullBoxPick == "1")
				{
					$mReqQty = $mInvQty;
					$mBdyQty = $mBdyQty - $mInvQty;
				}
// Order Qty > Serial Inventory - Take all Serial Inventory
				elseif ($mBdyQty > $mInvQty)
				{
					$mReqQty = $mInvQty;
					$mBdyQty = $mBdyQty - $mInvQty;
				}
// Take Order Requirement Qty.
				else
				{
					$mReqQty = $mBdyQty;
					$mBdyQty = 0;
				}
// Refresh Inventory Allocated Qty.
//				if ($mInvAllocatedQty <> $mPickAllocatedQty)
//				{
					$mPickQuery = "UPDATE inventory_master SET allocated_qty=".($mPickAllocatedQty+$mReqQty).", available_qty=CASE WHEN inventory_qty>allocated_qty THEN inventory_qty-allocated_qty ELSE 0 END ,last_updated=now(), last_updated_by='".$mCurrentUser."' WHERE id=".$mAlcInvId." LIMIT 1";
					if (!mysqli_query($mConnection,$mPickQuery))
						return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
//				}
// Valid Pick Qty.
				if ($mReqQty > 0)
				{
// Retrieve / Add Pick Body
					$mPickQuery = "SELECT id AS id FROM pick_body WHERE pick_body.pick_header_id=".$mPickHdrId." AND pick_body.order_body_id=".$mBdyId." LIMIT 1";
					$mPickData = mysqli_query($mConnection,$mPickQuery);
					$mPickBdyId = "";
					while($mPickRow = mysqli_fetch_assoc($mPickData))
					{
						$mPickBdyId = ($mPickRow['id']);
					}
					if (($mPickBdyId == "") && ($mBdyId != ""))
					{
						$mPickQuery = "INSERT INTO pick_body (id,part_id,part_number,order_body_id,pick_header_id,customer_reference,document_reference,qty_expected,qty_transacted,qty_allocated,qty_shortage,processed,date_created,created_by,last_updated_date,last_updated_by) VALUES(DEFAULT,".$mBdyPartId.",'".$mBdyPartNumber."',".$mBdyId.",".$mPickHdrId.",'".$mPickCustomerReference."','".$mPickDocumentReference."',".$mOrdAllocatedQty.",0,0,".$mOrdAllocatedQty.",0,now(),'".$mCurrentUser."_RDT',now(),'".$mCurrentUser."')";
						if (!mysqli_query($mConnection,$mPickQuery))
							return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
						$mPickBdyId=mysqli_insert_id($mConnection);	
					}
					
// Create Pick Detail
					if ($mInvPickPriority < 1)
						$mInvPickPriority = 1;
					if ($mPriority <1)
						$mPriority = 1;
					if ($mBdyLineNo < 1)
						$mBdyLineNo = 1;
//					if ($mLineNo < 1)
//						$mLineNo = 1;
//					$mPickQuery = "INSERT INTO pick_detail (id,inventory_master_id,pick_body_id,part_id,part_number,serial_reference,document_reference,location_code,qty_allocated,line_no,pick_priority,processed,qty_scanned,date_created,created_by,last_updated_date,last_updated_by) VALUES(DEFAULT,".$mAlcInvId.",".$mPickBdyId.",".$mBdyPartId.",'".$mBdyPartNumber."','".$mInvSerialReference."','".$mPickDocumentReference."','".$mInvLocationCode."',".$mReqQty.",".$mBdyLineNo.",".$mInvPickPriority.",0,0,now(),'".$mCurrentUser."_RDT',now(),'".$mCurrentUser."')";
					$mPickQuery = "INSERT INTO pick_detail (id,inventory_master_id,pick_body_id,part_id,part_number,serial_reference,document_reference,location_code,qty_allocated,line_no,pick_priority,processed,qty_scanned,date_created,created_by,last_updated_date,last_updated_by) VALUES(DEFAULT,".$mAlcInvId.",".$mPickBdyId.",".$mBdyPartId.",'".$mBdyPartNumber."','".$mInvSerialReference."','".$mPickDocumentReference."','".$mInvLocationCode."',".$mReqQty.",".$mBdyLineNo.",".$mPriority.",0,0,now(),'".$mCurrentUser."_RDT',now(),'".$mCurrentUser.$mTest."')";
					if (!mysqli_query($mConnection,$mPickQuery))
						return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
					$mDtlId=mysqli_insert_id($mConnection);
// Update Pick Body totals					
//					$mPickQuery = "UPDATE pick_body SET pick_body.qty_allocated=(SELECT SUM(qty_allocated+qty_scanned) FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id),pick_body.qty_shortage= CASE WHEN pick_body.qty_allocated>pick_body.qty_expected THEN 0 ELSE pick_body.qty_expected-pick_body.qty_allocated END,last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE pick_body.id=".$mPickBdyId." LIMIT 1";
					$mPickQuery = "UPDATE pick_body SET pick_body.qty_allocated=qty_allocated+".$mReqQty.",pick_body.qty_shortage= CASE WHEN pick_body.qty_allocated>pick_body.qty_expected THEN 0 ELSE pick_body.qty_expected-pick_body.qty_allocated END,last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE pick_body.id=".$mPickBdyId." LIMIT 1";
					if (!mysqli_query($mConnection,$mPickQuery))
						return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
					$mReturn = "OK  -";
				}
// If All Order Qty is allocate leave the loop
				if ($mBdyQty <= 0)
				{
//					return ("FAIL-TEST1-".$mBdyQty."-".$mPickBdyId);
					break;
				}
				$mPickQuery = "SELECT qty_expected AS qty_expected,qty_allocated AS qty_allocated FROM pick_body WHERE pick_body.id =".$mPickBdyId." LIMIT 1";
				$mPickData = mysqli_query($mConnection,$mPickQuery);
				$mPickRow = mysqli_fetch_assoc($mPickData);
				if ($mPickRow['qty_allocated'] >= $mPickRow['qty_expected'])
				{
//					return ("FAIL-TEST2-".$mBdyQty."-".$mPickBdyId);
					break;
				}
			}
// if Details have been created - leave loop
			if (($mDtlId != "") && ($mAll == ""))
			{
//				return ("FAIL-TEST3-".$mBdyQty."-".$mPickBdyId);
				break;
			}
		}
	}
	mysqli_close($mConnection);

//	$mReturn = "FAIL-TEST1-".$mReturn;
	return $mReturn;
}

function updatePickDetail($mHost,$mUser,$mPwd,$mDbName,$mPickDtlId,$mPickInvId,$mSerialReference,$mPickQty,$mCurrentUser,$mTransactionTypeId,$mToPlt,$mToLocHash,$mConnect="")
{
	if (($mPickDtlId == "") || ($mPickDtlId == null))
		return "FAIL-incorrect Pick Reference";
// mysql connection
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();
	
// Get id for inventory status
	$mInvStsId = 0;
	$mInvRcvId = 0;
	$mInvPckId = 0;
	$mInvPltId = 0;
	$mDataQuery = "SELECT id AS id, inventory_status_code AS inventory_status_code FROM inventory_status";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		if (strtolower($mRow['inventory_status_code']) == "ok")
			$mInvStsId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "rcv")
			$mInvRcvId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "pkd")
			$mInvPkdId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "plt")
			$mInvPltId = ($mRow['id']);
	}
	if ($mInvPkdId == null && $mInvPltId != null)
		$mInvPkdId = $mInvPltId;
// Get id for document status
	$mDocStsId = 0;
	$mDocPkdId = 0;
	$mDataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while($mRow = mysqli_fetch_assoc($mData))
	{
		if (strtolower($mRow['document_status_code']) == "open")
			$mDocStsId=($mRow['id']);
		if (strtolower($mRow['document_status_code']) == "picked")
			$mDocPkdId=($mRow['id']);
	}
	
//	$mTagId = null;
// Get Tag Id if Required
	if ($mToPlt != "")
	{
		$mDataQuery = "SELECT id FROM tag WHERE tag_reference='".$mToPlt."' LIMIT 1";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		while($mRow = mysqli_fetch_assoc($mData))
		{
			$mTagId = ($mRow['id']);
		}
	}
// Get default Pick Location Id
	$mPckLocId = null;
	$mDataQuery = "SELECT id,location_code FROM location WHERE location_code='PICK01' LIMIT 1";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	while($mRow = mysqli_fetch_assoc($mData))
	{
		$mPckLocId = ($mRow['id']);
	}
	$mToLocCode = "";
// Get Location Id if Required
	if ($mToLocHash != "")
	{
		$mToLocId = null;
		$mToLocCode = "";		   
		$mDataQuery = "SELECT id,location_code FROM location WHERE location_hash='".$mToLocHash."' LIMIT 1";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		while($mRow = mysqli_fetch_assoc($mData))
		{
			$mToLocId = ($mRow['id']);
			$mToLocCode = ($mRow['location_code']);
		}
	}
// Store Inventory_master id from Pick_detail
	$mPrvInvId = "";
	$mPickBdyId = "";
	$mPickHdrId = "";
	$mPickDtlQty = 0;
	$mConversionFactor = 1;
	$mDocDtlId = "";
	$mPickSerialRef = "";
	$mCustomerReference = "";
	$mDataQuery = "SELECT pick_detail.inventory_master_id AS inventory_master_id, pick_detail.pick_body_id AS pick_body_id, pick_detail.qty_allocated AS qty_allocated, pick_detail.line_no AS line_no, pick_detail.pick_priority AS pick_priority, pick_body.pick_header_id AS pick_header_id, pick_body.order_body_id AS order_body_id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, CASE WHEN pick_detail.serial_reference_scanned <> '' THEN pick_detail.serial_reference_scanned ELSE pick_detail.serial_reference END AS serial_reference, pick_header.customer_reference AS customer_reference FROM pick_detail JOIN pick_body ON pick_detail.pick_body_id=pick_body.id JOIN pick_header ON pick_body.pick_header_id=pick_header.id LEFT JOIN part ON pick_detail.part_id=part.id WHERE pick_detail.id=".$mPickDtlId." LIMIT 1";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	while($mRow = mysqli_fetch_assoc($mData))
	{
		$mPrvInvId = ($mRow['inventory_master_id']);
		$mPickBdyId = ($mRow['pick_body_id']);
		$mPickHdrId = ($mRow['pick_header_id']);
		$mPickDtlQty = ($mRow['qty_allocated']);
		$mConversionFactor = ($mRow['conversion_factor']);
		$mPartNumber = ($mRow['part_number']);
		$mDocDtlId = ($mRow['line_no']);
		$mDocBdyId = ($mRow['order_body_id']);
		$mPickSerialRef = ($mRow['serial_reference']);
		$mPickPriority = ($mRow['pick_priority']);
		$mCustomerReference = ($mRow['customer_reference']);
	}
// Zero Qty Not allowed
	if ((!$mPickQty) || ($mPickQty <= 0))
	{
		return "FAIL-Incorrect quantity-";
	}
// Use Scanned Qty if Entered
	if (($mPickQty != "") && ($mPickQty != null))
		$mPickDtlQty = $mPickQty*$mConversionFactor;
	else
		$mPickQty = $mPickDtlQty;
	
	if (($mSerialReference == "") || ($mSerialReference == null))
		$mSerialReference = $mPickSerialRef;

// Set Pick Detail to Processed	
	$mDataQuery = "UPDATE pick_detail SET processed=1,in_use_by=''";
	if ($mPickInvId != "")
		$mDataQuery .= ",inventory_master_id=".$mPickInvId;
	if ($mSerialReference != "")
		$mDataQuery .= ",serial_reference_scanned='".$mSerialReference."'";
	if ($mPickQty != "")
		$mDataQuery .= ",qty_scanned=".$mPickDtlQty.",qty_allocated=CASE WHEN qty_allocated > qty_scanned THEN qty_allocated-qty_scanned ELSE 0 END";
	$mDataQuery .= ",last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPickDtlId." LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;

// Update Document_detail if possible
	$mDataQuery = "SELECT id AS id FROM document_detail WHERE document_body_id=".$mDocBdyId." AND matnr = '".$mPartNumber."' AND qty_expected > qty_transacted LIMIT 1";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;

	while($mRow = mysqli_fetch_assoc($mData))
	{	
		$mDataQuery = "UPDATE document_detail SET qty_transacted=qty_transacted+".$mPickDtlQty.", last_updated=now(), last_updated_by='".$mCurrentUser."' WHERE id=".$mRow['id']." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	}
	
// Check if Full Serial has been picked
	$mDataQuery = "SELECT inventory_master.id AS id,inventory_master.part_id AS part_id,part.part_number AS part_number,inventory_master.ran_or_order AS ran_order,inventory_master.current_location_id AS location_id,location.location_code AS location_code,inventory_master.product_type_id AS product_type_id ,inventory_master.tag_reference AS plt_number,inventory_master.inventory_qty AS inventory_qty FROM inventory_master JOIN part ON inventory_master.part_id=part.id JOIN location ON inventory_master.current_location_id=location.id WHERE inventory_master.id=".$mPickInvId." LIMIT 1";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mInvQty = 0;
	$mFromLocCode = "";
	$mRanOrder = "";
	$mInvRow = array();
	while($mRow = mysqli_fetch_assoc($mData))
	{
		$mInvQty = ($mRow['inventory_qty']);
		$mFromLocCode = ($mRow['location_code']);
		$mRanOrder = ($mRow['ran_order']);
		$mInvRow = ($mRow);
	}
	if ($mInvQty <= $mPickDtlQty)
	{
// Change Inventory Master Status - Full Serial Pick
		$mDataQuery = "UPDATE inventory_master SET inventory_status_id=".$mInvPkdId.",allocated_qty=0";
		if ($mToPlt != "")
			$mDataQuery .= ",inventory_master.tag_reference='".$mToPlt."'";
		if (($mTagId != null) && ($mTagId != ""))
			$mDataQuery .= ",inventory_master.parent_tag_id=".$mTagId;
		if ($mToLocCode != "")
			$mDataQuery .= ",inventory_master.current_location_id=".$mToLocId.",inventory_master.location_id=".$mToLocId.",inventory_master.location_code='".$mToLocCode."'";
		elseif ($mPckLocId != null)
			$mDataQuery .= ",inventory_master.current_location_id=".$mPckLocId;
		if (($mRanOrder == "") || ($mRanOrder == "NO-RAN"))
			$mDataQuery .= ", ran_or_order = '".$mCustomerReference."'";
		$mDataQuery .= ",last_updated=now(),last_updated_by='".$mCurrentUser."_FULL' WHERE inventory_master.id=".$mPickInvId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
// Check for Replen
		$mDataQuery = "SELECT replenish_when_empty AS replen FROM transaction_type WHERE upper(short_code)='".$mTransactionTypeId."' LIMIT 1";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		while($mRow = mysqli_fetch_assoc($mData))
		{
			if ($mRow['replen'] == 1)
			{
				include_once('replen.php');
				$mReturn = createReplen($mInvRow['part_number'],$mFromLocCode,$mCurrentUser,$mConnect);	
//				$mReturn = updatePickHistory($mHost,$mUser,$mPwd,$mDbName,$mPickDtlId,$mPickInvId,$mPickDtlQty,$mCurrentUser,"REPLEN",$mFromLocCode,$mOrderType);
			}
		}
	}
	else
// Partial Pick Reduce Existing Inventory - Create New Pick Inventory
	{
		$mDataQuery = "UPDATE inventory_master SET inventory_qty = inventory_qty - ".$mPickDtlQty.",allocated_qty=CASE WHEN allocated_qty>=".$mPickDtlQty." THEN allocated_qty-".$mPickDtlQty." ELSE 0 END, available_qty = inventory_qty-allocated_qty, inventory_status_id=".$mInvStsId.",last_updated=now(),last_updated_by='".$mCurrentUser."_PCK' WHERE inventory_master.id=".$mPickInvId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		
		if (($mRanOrder == "") || ($mRanOrder == "NO-RAN"))
			$mRanOrder = $mCustomerReference;
		
		if ($mPckLocId == null)
			$mPckLocId = ($mInvRow['location_id']);
		if ($mToLocCode == "")
			if (($mTagId == "") || ($mTagId == null))
				$mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,current_disposition_id,current_inventory_status_id,current_location_id,date_created,expiry_date,inventory_qty,last_updated,last_updated_by,parent_tag_id,part_id,ran_or_order,recorded_missing,requires_decant,requires_inspection,serial_reference,originating_detail_id,conversion_factor,stock_date,active,part_number,version,pruf_cube,available_qty,created_by,location_code,tag_reference,product_type_id,location_id,inventory_status_id) VALUES(DEFAULT,0,null,null,".$mPckLocId.",now(),null,".$mPickDtlQty.",now(),'".$mCurrentUser."',null,".$mInvRow['part_id'].",'".$mRanOrder."',0,0,0,null,null,".$mConversionFactor.",now(),1,'".$mInvRow['part_number']."',0,0,".$mPickDtlQty.",'".$mCurrentUser."','".$mInvRow['location_code']."','".$mToPlt."',null,".$mInvRow['location_id'].",".$mInvPkdId.")";
			else
				$mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,current_disposition_id,current_inventory_status_id,current_location_id,date_created,expiry_date,inventory_qty,last_updated,last_updated_by,parent_tag_id,part_id,ran_or_order,recorded_missing,requires_decant,requires_inspection,serial_reference,originating_detail_id,conversion_factor,stock_date,active,part_number,version,pruf_cube,available_qty,created_by,location_code,tag_reference,product_type_id,location_id,inventory_status_id) VALUES(DEFAULT,0,null,null,".$mPckLocId.",now(),null,".$mPickDtlQty.",now(),'".$mCurrentUser."',".$mTagId.",".$mInvRow['part_id'].",'".$mRanOrder."',0,0,0,null,null,".$mConversionFactor.",now(),1,'".$mInvRow['part_number']."',0,0,".$mPickDtlQty.",'".$mCurrentUser."','".$mInvRow['location_code']."','".$mToPlt."',null,".$mInvRow['location_id'].",".$mInvPkdId.")";
		else
			if (($mTagId == "") || ($mTagId == null))
				$mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,current_disposition_id,current_inventory_status_id,current_location_id,date_created,expiry_date,inventory_qty,last_updated,last_updated_by,parent_tag_id,part_id,ran_or_order,recorded_missing,requires_decant,requires_inspection,serial_reference,originating_detail_id,conversion_factor,stock_date,active,part_number,version,pruf_cube,available_qty,created_by,location_code,tag_reference,product_type_id,location_id,inventory_status_id) VALUES(DEFAULT,0,null,null,".$mToLocId.",now(),null,".$mPickDtlQty.",now(),'".$mCurrentUser."',null,".$mInvRow['part_id'].",'".$mRanOrder."',0,0,0,null,null,".$mConversionFactor.",now(),1,'".$mInvRow['part_number']."',0,0,".$mPickDtlQty.",'".$mCurrentUser."','".$mToLocCode."','".$mToPlt."',null,".$mToLocId.",".$mInvPkdId.")";
			else
				$mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,current_disposition_id,current_inventory_status_id,current_location_id,date_created,expiry_date,inventory_qty,last_updated,last_updated_by,parent_tag_id,part_id,ran_or_order,recorded_missing,requires_decant,requires_inspection,serial_reference,originating_detail_id,conversion_factor,stock_date,active,part_number,version,pruf_cube,available_qty,created_by,location_code,tag_reference,product_type_id,location_id,inventory_status_id) VALUES(DEFAULT,0,null,null,".$mToLocId.",now(),null,".$mPickDtlQty.",now(),'".$mCurrentUser."',".$mTagId.",".$mInvRow['part_id'].",'".$mRanOrder."',0,0,0,null,null,".$mConversionFactor.",now(),1,'".$mInvRow['part_number']."',0,0,".$mPickDtlQty.",'".$mCurrentUser."','".$mToLocCode."','".$mToPlt."',null,".$mToLocId.",".$mInvPkdId.")";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mPickInvId=mysqli_insert_id($mConnection);
// Change Inventory Id On Pick Detail
		$mDataQuery = "UPDATE pick_detail SET inventory_master_id=".$mPickInvId." WHERE id=".$mPickDtlId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	}

// Check / Change Pick Body Processed Status
//	$mDataQuery = "SELECT SUM(qty_scanned) AS pick_qty FROM pick_detail WHERE pick_detail.pick_body_id =".$mPickBdyId." AND processed=1 GROUP BY pick_detail.pick_body_id ORDER BY pick_detail.pick_body_id";
//	if (!$mData = mysqli_query($mConnection,$mDataQuery))
//		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
//	$mPickQty = 0;
//	while($mRow = mysqli_fetch_assoc($mData))
//	{
//		$mPickQty = ($mRow['pick_qty']);
//	}
//	$mDataQuery = "UPDATE pick_body SET qty_transacted=".$mPickQty;
	$mDataQuery = "UPDATE pick_body SET qty_transacted=qty_transacted+".$mPickDtlQty;
	$mDataQuery .= ",last_updated_date=now(),last_updated_by='".$mCurrentUser."'";
//	$mDataQuery .= ", processed= (CASE WHEN (qty_expected <= ".$mPickQty.") THEN 1 ELSE 0 END)";
	$mDataQuery .= ", processed= (CASE WHEN (qty_expected <= qty_transacted) THEN 1 ELSE 0 END)";
	$mDataQuery	.= " WHERE pick_body.id=".$mPickBdyId." LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;

// Update Order Body Pick Status
	$mDataQuery = "SELECT order_body_id FROM pick_body WHERE id=".$mPickBdyId." LIMIT 1";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mPickOrdId = "";
	while($mRow = mysqli_fetch_assoc($mData))
	{
		$mPickOrdId = ($mRow['order_body_id']);
	}
	if (($mPickOrdId != "") && ($mPickOrdId != null))
	{
//		$mDataQuery = "SELECT sum(pick_body.qty_transacted) AS pick_qty FROM pick_body WHERE pick_body.order_body_id=".$mPickOrdId;
//		if (!$mData = mysqli_query($mConnection,$mDataQuery))
//			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
//		$mPickQty = 0;
//		$i = 0;
//		while($mRow = mysqli_fetch_assoc($mData))
//		{
//			$mPickQty = ($mRow['pick_qty']);
//			$i++;
//		}
//		$mOrderQuery = "UPDATE order_body SET qty_transacted=".$mPickQty.",last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPickOrdId." LIMIT 1";																																						   
		$mOrderQuery = "UPDATE document_body SET qty_transacted=qty_transacted+".$mPickDtlQty.",last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPickOrdId." LIMIT 1";
		if (!mysqli_query($mConnection,$mOrderQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mOrderQuery;
	}
//	return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".$mOrderQuery."-".$i;

// Update Inventory_master Allocated Qty
	if (($mPrvInvId != $mPickInvId) && ($mPickSerialRef != $mSerialReference))
	{
// Pick Detail		
//		$mPickQuery = "SELECT SUM(qty_allocated) AS qty_allocated FROM pick_detail WHERE inventory_master_id=".$mPrvInvId." AND (processed=0 OR processed IS null) GROUP BY inventory_master_id ORDER BY inventory_master_id";
//		if (!mysqli_query($mConnection,$mPickQuery))
//			return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
//		$mPickData = mysqli_query($mConnection,$mPickQuery);
//		$mPickAllocatedQty = 0;
//		if (mysqli_num_rows($mPickData) > 0)
//		{
//			while($mPickRow = mysqli_fetch_assoc($mPickData))
//			{
//				$mPickAllocatedQty = ($mPickRow['qty_allocated']);
//			}
//		}
// Allocation Detail
//		$mPickQuery = "SELECT SUM(allocated_qty) AS qty_allocated FROM allocation_detail WHERE inventory_master_id=".$mPrvInvId." AND (processed=0 OR processed IS null) GROUP BY inventory_master_id ORDER BY inventory_master_id";
//		if (!mysqli_query($mConnection,$mPickQuery))
//			return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
//		$mPickData = mysqli_query($mConnection,$mPickQuery);
//		$mAlcAllocatedQty = 0;
//		if (mysqli_num_rows($mPickData) > 0)
//		{
//			while($mPickRow = mysqli_fetch_assoc($mPickData))
//			{
//				$mAlcAllocatedQty = ($mPickRow['qty_allocated']);
//			}
//		}
		
//		if ($mPickAllocatedQty < 0)
//			$mPickAllocatedQty =0;
//		if ($mAlcAllocatedQty < 0)
//			$mAlcAllocatedQty =0;
		
//		$mPickAllocatedQty=$mPickAllocatedQty+$mAlcAllocatedQty;
		
		$mDataQuery = "UPDATE inventory_master SET allocated_qty=CASE WHEN allocated_qty>=".$mPickDtlQty." THEN allocated_qty-".$mPickDtlQty." ELSE 0 END,last_updated=now(),last_updated_by='".$mCurrentUser."_ALC' WHERE inventory_master.id=".$mPrvInvId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	}
// If Pick Body is Complete Check for outstanding Pick Details
	$mDataQuery = "SELECT pick_detail.id AS id, pick_detail.inventory_master_id AS inventory_master_id, pick_detail.qty_allocated AS qty_allocated FROM pick_body JOIN pick_detail ON pick_detail.pick_body_id=pick_body.id WHERE pick_body.id=".$mPickBdyId." AND pick_body.processed=1 AND pick_detail.processed=0";
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mAlcQty = 0;
	while($mRow = mysqli_fetch_assoc($mData))
	{
		$mAlcQty = $mRow['qty_allocated'];
		$mPickQuery = "UPDATE inventory_master set allocated_qty=CASE WHEN allocated_qty>".$mAlcQty." THEN allocated_qty-".$mAlcQty." ELSE 0 END, available_qty=inventory_qty-allocated_qty,last_updated=now(),last_updated_by='".$mCurrentUser."_CMP' WHERE id=".$mRow['inventory_master_id']." LIMIT 1";
		if (!mysqli_query($mConnection,$mPickQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
		$mPickQuery = "UPDATE pick_detail SET qty_allocated=0,processed=1 WHERE id=".$mRow['id']." LIMIT 1";
		if (!mysqli_query($mConnection,$mPickQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
	}
// Check for Pick Complete
	$mDataQuery = "SELECT id AS id FROM pick_body WHERE pick_header_id=".$mPickHdrId." AND processed=0 LIMIT 1";
	$mPickData = mysqli_query($mConnection,$mDataQuery);
	if (mysqli_num_rows($mPickData) == 0)
	{
		if ($mDocPkdId != "")
		{
			$mDataQuery = "UPDATE pick_header SET document_status_id=".$mDocPkdId.",last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPickHdrId." LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		}
		$mPickQuery = "SELECT id AS id, order_header_id AS order_header_id FROM pick_header WHERE id=".$mPickHdrId." LIMIT 1";
		$mPickData = mysqli_query($mConnection,$mPickQuery);
		while($mRow = mysqli_fetch_assoc($mPickData))
		{
			$mDocHdrId = ($mRow['order_header_id']);
//			$mDataQuery = "UPDATE order_header SET document_status_id=".$mDocPkdId.",last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".($mRow['order_header_id'])." LIMIT 1";
			$mDataQuery = "UPDATE document_header SET picked=1, document_status_id=".$mDocPkdId.",last_updated=now(),last_updated_by='".$mCurrentUser."-COMP' WHERE id=".($mRow['order_header_id'])." LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
//			return "FAIL-TEST1:".$mDataQuery;
		}
// Send Server Message
//		http://172.16.1.70:8092/screen/writeOrderCompleteMessage?documentReference=COR408605
//		http://172.16.1.70:8092/screen/writeCrossDockCompleteMessage?documentReference=COR408605
//		http://172.16.1.70:8092/screen/writeReSeqCompleteMessage?documentReference=COR408605

		$mPickQuery = "SELECT document_reference_code AS document_reference, pick_type AS pick_type, order_type FROM document_header WHERE id=".$mDocHdrId." LIMIT 1";
//		$mPickQuery = "SELECT order_header.document_reference AS document_reference, order_header.order_type FROM order_header WHERE id=".$mDocHdrId." LIMIT 1";																																				  
		$mPickData = mysqli_query($mConnection,$mPickQuery);
		$mPickType = "";
		while($mRow = mysqli_fetch_assoc($mPickData))
		{
			$mPickType = $mRow['pick_type'];
			$mOrderType = $mRow['order_type'];
			$mOrdDocRef = $mRow['document_reference'];
		}
	
// Get Server Data from URL
		$mJsonArray = array();
		$mJsonArray['documentReference'] = $mOrdDocRef;
// Convert Array to Json Object
		try
		{
			$mJsonObject = json_encode($mJsonArray, JSON_FORCE_OBJECT);
		}
		catch(Exception $e)
		{
			exit("FAIL-Json Error");
		}
// Replace default Server Tomcat Service
		$mConnectp = str_replace(":8081/vantec",":8092/screen",$mConnect);		
		$ch = curl_init();
		try
		{
			if ($mPickType == "XD")
				curl_setopt($ch, CURLOPT_URL, $mConnectp."writeCrossDockCompleteMessage?documentReference=".$mOrdDocRef);
			elseif ($mPickType == "RP")
				curl_setopt($ch, CURLOPT_URL, $mConnectp."writeReSeqCompleteMessage?documentReference=".$mOrdDocRef);
			else
				curl_setopt($ch, CURLOPT_URL, $mConnectp."writeOrderCompleteMessage?documentReference=".$mOrdDocRef);
			curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json')); 
			curl_setopt($ch, CURLOPT_POST, 1);
			curl_setopt($ch, CURLOPT_POSTFIELDS, $mJsonObject);
			curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
			$mResult = curl_exec($ch);
			$mErrors = curl_error($ch);
			$mResponse = curl_getinfo($ch, CURLINFO_HTTP_CODE);

			curl_close($ch);
			if ($mResponse != "200")
				$mErrMsg = "FAIL-Unexpected System Error -".$mResult.":".$mResponse.":".$mErrors.":".$mJsonObject;
			else
			{
				$mErrMsg = "OK  - Post Complete";
			}
		}
		catch (Exception $e)
		{
			$mErrMsg = "FAIL-Connection Error at - ".$mConnect.$mFilter.":".$e;
		}
	}
	
	$mOrderType = "";
	$mDocHdrId = "";
//	$mPickQuery = "SELECT pick_header.order_header_id AS order_header_id, order_header.order_type AS order_type FROM pick_header JOIN order_header ON pick_header.order_header_id=order_header.id WHERE pick_header.id=".$mPickHdrId." LIMIT 1";
	$mPickQuery = "SELECT pick_header.order_header_id AS order_header_id, document_header.order_type AS order_type FROM pick_header JOIN document_header ON pick_header.order_header_id=document_header.id WHERE pick_header.id=".$mPickHdrId." LIMIT 1";
	if (!$mPickData = mysqli_query($mConnection,$mPickQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
	$mPickData = mysqli_query($mConnection,$mPickQuery);
	while($mRow = mysqli_fetch_assoc($mPickData))
	{
		$mDocHdrId = ($mRow['order_header_id']);
		$mOrderType = $mRow['order_type'];
	}
/*
	if ($mDocHdrId != "")
	{
		$mPickQuery = "SELECT document_reference_code AS document_reference, pick_type AS pick_type, order_type FROM document_header WHERE id=".$mDocHdrId." LIMIT 1";
		$mPickData = mysqli_query($mConnection,$mPickQuery);
		while($mRow = mysqli_fetch_assoc($mPickData))
		{
			$mOrderType = $mRow['order_type'];
		}
	}
*/
//	return("FAIL-TEST1-".$mPickQuery."-".$mOrderType."-".$mPickHdrId."-".$mDocHdrId."-".mysqli_num_rows($mPickData));
	$mReturn = updatePickHistory($mHost,$mUser,$mPwd,$mDbName,$mPickDtlId,$mPickInvId,$mPickDtlQty,$mCurrentUser,$mTransactionTypeId,$mFromLocCode,$mOrderType);
	
	mysqli_close($mConnection);
	
	if ($mReturn != "")
		return $mReturn;
	
	return "OK  -";
}

// Update Transaction History
function updatePickHistory($mHost,$mUser,$mPwd,$mDbName,$mPickDtlId,$mPickInvId,$mPickDtlQty,$mCurrentUser,$mTransactionTypeId,$mFromLocCode="",$mOrderType="")
{
	if (($mHost == null) || ($mHost == ""))
		$mConnection=defaultDbConnection();
	else
		$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();
// Order Type
	$mOrderDesc = $mOrderType;
	if ($mOrderType != "")
	{
		$mDataQuery = "SELECT description AS order_desc FROM order_type WHERE order_type_code='".$mOrderType."' LIMIT 1";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		while($mRow = mysqli_fetch_assoc($mData))
		{
			$mOrderDesc = $mRow['order_desc'];
		}
	}
// Inventory Master
	$mDataQuery = "SELECT inventory_master.ran_or_order AS ran_order, inventory_master.current_location_id AS location_id, part.part_number,location.location_code,inventory_master.tag_reference AS plt_number, inventory_master.serial_reference AS serial_reference, inventory_master.inventory_qty AS inventory_qty FROM inventory_master JOIN part ON inventory_master.part_id=part.id JOIN location ON inventory_master.current_location_id=location.id WHERE inventory_master.id=".$mPickInvId." LIMIT 1";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mInvRow = array();
	if (mysqli_num_rows($mData) > 0)
	{
		while($mRow = mysqli_fetch_assoc($mData))
		{
			$mInvRow = $mRow;
		}
	}
// Pick Detail
	$mDataQuery = "SELECT pick_detail.qty_allocated,CASE WHEN pick_detail.serial_reference_scanned IS null THEN pick_detail.serial_reference ELSE pick_detail.serial_reference_scanned END AS serial_reference,pick_body.customer_reference,pick_body.document_reference FROM pick_detail JOIN pick_body ON pick_detail.pick_body_id=pick_body.id WHERE pick_detail.id=".$mPickDtlId." LIMIT 1";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mDtlRow = array();
	if (mysqli_num_rows($mData) > 0)
	{
		while($mRow = mysqli_fetch_assoc($mData))
		{
			$mDtlRow = $mRow;
		}
	}
	$mSerialRef = $mDtlRow['serial_reference'];
	if (($mSerialRef == "" || $mSerialRef == null) && $mInvRow['serial_reference'] != null)
		$mSerialRef = $mInvRow['serial_reference'];
	if ($mTransactionTypeId == "MISSING")
		$mPickDtlQty = $mInvRow['inventory_qty'];
// Transaction History	
//	$mDataQuery = "INSERT INTO transaction_history (id,ran_or_order,part_number,serial_reference,short_code,txn_qty,from_location_code,to_location_code,to_plt,customer_reference,associated_document_reference,date_created,created_by,last_updated,last_updated_by,parent_tag,transaction_reference_code) VALUE(DEFAULT,'".$mInvRow['ran_order']."','".$mInvRow['part_number']."','".$mInvRow['serial_reference']."','".$mTransactionTypeId."',".$mPickDtlQty.",'".$mFromLocCode."','".$mInvRow['location_code']."','".$mInvRow['plt_number']."','".$mDtlRow['customer_reference']."','".$mDtlRow['document_reference']."',now(),'".$mCurrentUser."',now(),'".$mCurrentUser."','".$mInvRow['plt_number']."','".$mTransactionTypeId."')";
	if ($mOrderType != "")
		$mDataQuery = "INSERT INTO transaction_history (id,ran_or_order,part_number,serial_reference,short_code,txn_qty,from_location_code,to_location_code,to_plt,customer_reference,associated_document_reference,date_created,created_by,last_updated,last_updated_by,parent_tag,transaction_reference_code) VALUE(DEFAULT,'".$mInvRow['ran_order']."','".$mInvRow['part_number']."','".$mSerialRef."','".$mTransactionTypeId."-".$mOrderDesc."',".$mPickDtlQty.",'".$mFromLocCode."','".$mInvRow['location_code']."','".$mInvRow['plt_number']."','".$mDtlRow['customer_reference']."','".$mDtlRow['document_reference']."',now(),'".$mCurrentUser."',now(),'".$mCurrentUser."','".$mInvRow['plt_number']."','".$mTransactionTypeId."')";
	else
		$mDataQuery = "INSERT INTO transaction_history (id,ran_or_order,part_number,serial_reference,short_code,txn_qty,from_location_code,to_location_code,to_plt,customer_reference,associated_document_reference,date_created,created_by,last_updated,last_updated_by,parent_tag,transaction_reference_code) VALUE(DEFAULT,'".$mInvRow['ran_order']."','".$mInvRow['part_number']."','".$mSerialRef."','".$mTransactionTypeId.$mOrderDesc."',".$mPickDtlQty.",'".$mFromLocCode."','".$mInvRow['location_code']."','".$mInvRow['plt_number']."','".$mDtlRow['customer_reference']."','".$mDtlRow['document_reference']."',now(),'".$mCurrentUser."',now(),'".$mCurrentUser."','".$mInvRow['plt_number']."','".$mTransactionTypeId."')";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
//	return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	mysqli_close($mConnection);
	return "OK  -";
}

// Clear Pick Detail in_use_by
function clearPickDetail($mCurrentUser,$mHost=null,$mUser=null,$mPwd=null,$mDbName=null)
{
// mysql connection
	if (($mHost == null) || ($mHost == ""))
		$mConnection=defaultDbConnection();
	else
		$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();	
// Set Pick Detail to Processed	
	$mDataQuery = "UPDATE pick_detail SET in_use_by=''";
	$mDataQuery .= " WHERE in_use_by='".$mCurrentUser."'";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	mysqli_close($mConnection);
	return "OK  -";
}

// Pick Run Aisle Summary
function pickAisleSummary($mHost,$mUser,$mPwd,$mDbName,$mCustomerReference,$mCurrentUser,$mTransactionType="PBTP",$mConnect="")
{
	$mDataStr = "";
// mysql connection
	if (($mHost == null) || ($mHost == ""))
		$mConnection=defaultDbConnection();
	else
		$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
			exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
// Check if Pick By Aisle is allowed
//	$mDataQuery = "SELECT order_type.pick_by_line AS pick_by_line FROM order_type JOIN order_header ON order_type.order_type_code=order_header.order_type WHERE order_header.customer_reference='".$mCustomerReference."' LIMIT 1";
	$mDataQuery = "SELECT order_type.pick_by_line AS pick_by_line FROM order_type JOIN document_header ON order_type.order_type_code=document_header.order_type WHERE document_header.document_type='RRM Order' AND document_header.customer_reference_code='".$mCustomerReference."' LIMIT 1";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mPickByLine = "";
	while($mRow = mysqli_fetch_assoc($mData))
	{
		$mPickByLine = ($mRow['pick_by_line']);
	}
	if ($mPickByLine == "1")
	{
//		return "CHGTRAN:PBTP?DATA:customerReference:".$mCustomerReference.",aisleCode:ALL";
		return "CHGTRAN:".$mTransactionType."?DATA:customerReference:".$mCustomerReference.",aisleCode:ALL";
	}
	$mDataQuery = "SELECT id AS id, document_reference AS document_reference, order_header_id AS order_header_id FROM pick_header WHERE customer_reference='".$mCustomerReference."'";
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mPickHdrId = "";
	$mPickHdrIds = "";
	$mOrderHdrId = "";
	$mOrderHdrType = "";
	$i = 0;
	if (mysqli_num_rows($mData) == 0)
	{
		$mOrderId = "";
		$mTimeSlot = "";
//		$mDataQuery = "SELECT id AS id, time_slot AS time_slot FROM order_header WHERE customer_reference = '".$mCustomerReference."' LIMIT 1";
		$mDataQuery = "SELECT id AS id, time_slot AS time_slot FROM document_header WHERE document_type='RRM Order' AND customer_reference_code = '".$mCustomerReference."' LIMIT 1";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		while($mRow = mysqli_fetch_assoc($mData))
		{
			$mOrderId = ($mRow['id']);
			$mTimeSlot = ($mRow['time_slot']);
		}
		if ($mOrderId != "")
		{
			$mReturn = addUpdatePick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mAutoCreateDoc,$mCustomerReference,$mOrderId,$mTimeSlot);
			if (substr($mReturn,0,4) == "FAIL")
				return $mReturn;
		}
		$mDataQuery = "SELECT id AS id, document_reference AS document_reference, order_header_id AS order_header_id FROM pick_header WHERE customer_reference='".$mCustomerReference."'";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	}
//	return "FAIL-".$mDataQuery."-".mysqli_num_rows($mData)."-".$mReturn;
	if (mysqli_num_rows($mData) > 0)
	{
		while($mRow = mysqli_fetch_assoc($mData))
		{
			$mPickHdrId = ($mRow['id']);
			if ($mPickHdrIds != "")
				$mPickHdrIds .= ",";
			$mPickHdrIds .= $mPickHdrId;
			$mOrderHdrId = ($mRow['order_header_id']);
			$i++;
		}
// Test if Reallocation is required
//		$mDataQuery = "SELECT order_body.id AS id FROM order_body WHERE order_body.order_header_id = ".$mOrderHdrId." AND (NOT EXISTS(select id FROM pick_body WHERE order_body.id=pick_body.order_body_id) OR EXISTS(SELECT id FROM pick_body WHERE order_body.id=pick_body.order_body_id AND pick_body.qty_expected>pick_body.qty_allocated AND (pick_body.processed=0 OR pick_body.processed IS null)))";
		$mDataQuery = "SELECT document_body.id AS id FROM document_body WHERE document_body.document_header_id = ".$mOrderHdrId." AND (NOT EXISTS(select id FROM pick_body WHERE document_body.id=pick_body.order_body_id) OR EXISTS(SELECT id FROM pick_body WHERE document_body.id=pick_body.order_body_id AND pick_body.qty_expected>pick_body.qty_allocated AND (pick_body.processed=0 OR pick_body.processed IS null)))";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
//		return "FAIL-".$mDataQuery."-".mysqli_num_rows($mData);
		if (mysqli_num_rows($mData) > 0)
		{
// Try to Allocate Inventory to Outs.Orders
			if ($mConnect == "")
				$mReturn = allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderHdrId, $mPickHdrId, $mCurrentUser,"Y");
			else
				$mReturn = allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderHdrId, $mPickHdrId, $mCurrentUser,"Y");
//				$mReturn = allocateServerOrderLines($mConnect,$mCustomerReference,$mCurrentUser,"");
//			return "FAIL-TEST1-".$mReturn."-".$mOrderHdrId."-".$mPickHdrId;
		}
// Read Selected Pick Bodies
		if ($i == 1)
		{
			$mDataQuery = "SELECT id AS id FROM pick_body WHERE pick_header_id=".$mPickHdrId;
		}
		if ($i > 1)
		{
			$mDataQuery = "SELECT id AS id FROM pick_body WHERE pick_header_id IN(".$mPickHdrIds.")";
		}
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mPickBdyId = "";
		$mPickBdyIds = "";
		$i = 0;
		if (mysqli_num_rows($mData) > 0)
		{
			while($mRow = mysqli_fetch_assoc($mData))
			{
				$mPickBdyId = ($mRow['id']);
				if ($mPickBdyIds != "")
					$mPickBdyIds .= ",";
				$mPickBdyIds .= $mPickBdyId;
				$i++;
			}
			if ($i == 1)
			{
				$mDataQuery = "SELECT location.aisle_code, count(pick_detail.id) AS pick_count FROM pick_detail JOIN inventory_master ON pick_detail.inventory_master_id=inventory_master.id JOIN location ON inventory_master.current_location_id=location.id WHERE pick_body_id=".$mPickBdyId." AND (pick_detail.processed=0) GROUP BY location.aisle_code ORDER BY location.aisle_code";
			}
			if ($i > 1)
			{
				$mDataQuery = "SELECT location.aisle_code, count(pick_detail.id) AS pick_count FROM pick_detail JOIN inventory_master ON pick_detail.inventory_master_id=inventory_master.id JOIN location ON inventory_master.current_location_id=location.id WHERE pick_body_id IN(".$mPickBdyIds.") AND (pick_detail.processed=0) GROUP BY location.aisle_code ORDER BY location.aisle_code";
			}
			if (!$mData = mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			$mPickAisleCode= "";
			$mPickCount = 0;
			$i = 0;
			$l = 0;
			if (mysqli_num_rows($mData) > 0)
			{
				while($mRow = mysqli_fetch_assoc($mData))
				{
					$mPickAisleCode = ($mRow['aisle_code']);
					$mPickCount = ($mRow['pick_count']);
					if ($mDataStr != "")
						$mDataStr .= ",";
					$i++;
					$l++;
					if ($i <8)
						$mDataStr .= '{"label":"'.$l.'","name":"'.$mTransactionType.'?DATA:customerReference:'.$mCustomerReference.':I,aisleCode:'.$mPickAisleCode.'?5","input":"'.$mPickAisleCode.'","content":"'.$mPickCount.'","screenoption":"PHHH"}';
					elseif ($i <16)
						$mDataStr .= '{"label":"'.$l.'","name":"'.$mTransactionType.'?DATA:customerReference:'.$mCustomerReference.':I,aisleCode:'.$mPickAisleCode.'?5","input":"'.$mPickAisleCode.'","content":"'.$mPickCount.'","screenoption":"HPHH"}';
					elseif ($i <24)
						$mDataStr .= '{"label":"'.$l.'","name":"'.$mTransactionType.'?DATA:customerReference:'.$mCustomerReference.':I,aisleCode:'.$mPickAisleCode.'?5","input":"'.$mPickAisleCode.'","content":"'.$mPickCount.'","screenoption":"HHPH"}';
					else
						$mDataStr .= '{"label":"'.$l.'","name":"'.$mTransactionType.'?DATA:customerReference:'.$mCustomerReference.':I,aisleCode:'.$mPickAisleCode.'?5","input":"'.$mPickAisleCode.'","content":"'.$mPickCount.'","screenoption":"HHHP"}';
					if(($i==8) || ($i==16) || ($i==24))
						$l=0;
				}
//				return "SELECT:label:customerReference,input:".$mCustomerReference.",screenoption:p,".$mDataStr;
//				$mDataStr = ('[{"label":"customerReference","name":"customerReference","input":"'.$mCustomerReference.'","content":"","screenoption":"PPP"},'.$mDataStr.']');
				$mDataStr = ('[{"label":"'.$mCustomerReference.'","name":"customerReference","input":"Aisle","content":"Picks","screenoption":"PPP"},'.$mDataStr.']');
				return "SELECT:".$mDataStr;
			}
			else
			{
//				return "FAIL- No Pick Tasks For ".$mCustomerReference;
//				return "FAIL-EOD-No Stock;customerReference;".$mCustomerReference;
				return "FAIL-No Stock for this Pick";
			}
		}
		else
		{
			return "FAIL-No Pick Bodies For ".$mCustomerReference;
		}
	}
	else
	{
		return "FAIL-No Picks Raised For -".$mCustomerReference;
	}
}

function getXdPick($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mCustomerReferenceCode,$mPickHdrId,$mOrderHdrId,$mAisle)
{
	$mReturn = "";
	$mPartId = "";	
// mysql connection
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();
	
// Get id for inventory status
	$mInvStsId = 0;
	$mInvRcvId = 0;
	$mInvPckId = 0;
	$mInvPltId = 0;
	$mDataQuery = "SELECT id AS id, inventory_status_code AS inventory_status_code FROM inventory_status";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		if (strtolower($mRow['inventory_status_code']) == "ok")
			$mInvStsId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "rcv")
			$mInvRcvId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "pkd")
			$mInvPkdId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "plt")
			$mInvPltId = ($mRow['id']);
	}
	if ($mInvPkdId == null && $mInvPltId != null)
		$mInvPkdId = $mInvPltId;
// Get id for document status
	$mDocStsId = 0;
	$mDocPkdId = 0;
	$mDataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while($mRow = mysqli_fetch_assoc($mData))
	{
		if (strtolower($mRow['document_status_code']) == "open")
			$mDocStsId = $mDocStsId=($mRow['id']);
		if (strtolower($mRow['document_status_code']) == "picked")
			$mDocPkdId = $mDocStsId=($mRow['id']);
	}
	
// Get available Pick Body / Detail
	$mDataQuery = "SELECT pick_detail.id AS id, pick_detail.inventory_master_id AS invId, pick_detail.qty_allocated AS qty_allocated, pick_detail.pick_body_id AS pick_body_id, pick_detail.line_no AS line_no, pick_detail.pick_priority AS pick_priority, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, location.location_code AS location_code, tag.tag_reference AS tag_reference FROM pick_detail";
	$mDataQuery .= " JOIN pick_body ON pick_detail.pick_body_id=pick_body.id";
	$mDataQuery .= " JOIN inventory_master ON pick_detail.inventory_master_id=inventory_master.id";
	$mDataQuery .= " JOIN tag ON inventory_master.parent_tag_id=tag.id";
	$mDataQuery .= " LEFT JOIN part ON pick_detail.part_id=part.id";
	$mDataQuery .= " LEFT JOIN location ON inventory_master.current_location_id=location.id";
// Test for muliple Pick Header Ids (timeslot selection)
	$i = strpos($mPickHdrId,",");
	if ($i > 0)
		$mDataQuery .= " WHERE pick_body.pick_header_id IN(".$mPickHdrId.")";
	else
		$mDataQuery .= " WHERE pick_body.pick_header_id=".$mPickHdrId; 
	$mDataQuery .= " AND (pick_detail.processed=0 OR pick_detail.processed IS null) AND (pick_detail.in_use_by IS null OR pick_detail.in_use_by='' OR pick_detail.in_use_by='".$mCurrentUser."' OR (pick_detail.last_updated_date < DATE_SUB(NOW(), INTERVAL 10 MINUTE)))";
	if (($mAisle == "") || ($mAisle == null))
		$mDataQuery .= " AND (location.aisle_code='' OR location.aisle_code IS null)";
	elseif ($mAisle != "ALL")
		$mDataQuery .= " AND location.aisle_code='".$mAisle."'";
	if ($mPickByLine == 1)
		$mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.line_no, pick_detail.pick_priority,location.location_code,pick_detail.serial_reference LIMIT 50";
	else
		$mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.pick_priority, pick_detail.line_no, location.location_code,pick_detail.serial_reference LIMIT 50";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
	
//	return "FAIL-TEST1".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData);
	
	$mPickDtlId = "";
	$mPickBdyId = "";
	$mPickInvId = "";
	$mPickQty = 0;
	$mFullBoxPick="";
	$mPartNumber = "";
	$mSerialReference = "";
	$mLocationCode = "";
	$mPickDtlLine = "";
	$mTagReference = "";
	
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mPickDtlId = ($mRow['id']);
//		$mPickDtlLine = ($mRow['line_no']);
		$mPickDtlLine = ($mRow['pick_priority']);
		$mPickBdyId = ($mRow['pick_body_id']);
		$mPickInvId = ($mRow['invId']);
		$mPickQty = ($mRow['qty_allocated']);
		$mPartNumber = ($mRow['part_number']);
		$mSerialReference = ($mRow['serial_reference']);
		$mLocationCode = ($mRow['location_code']);
		$mTagReference = ($mRow['tag_reference']);
		$mConversionFactor = 1;

		if ($mPickDtlId != "")
		{
			break;
		}
	}

//	exit("FAIL-TEST1".$mPartId.":".$mAlcQty);
// No Valid Allocation found - Try to Find Matching Inventory
	if ($mPickDtlId == "")
	{
		$mDataQuery = "SELECT pick_header.document_reference AS document_reference FROM pick_header WHERE id=".$mPickHdrId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mPickDocumentReference = ($mRow['document_reference']);
		}
		$mDataQuery = "SELECT pick_body.id AS id, document_body.id AS order_body_id, document_body.part_id AS part_id, document_body.wempf AS vehicle_ref, document_body.our_line_no AS our_line_no, part.part_number AS part_number , part.conversion_factor AS conversion_factor, document_body.qty_expected AS qty_expected FROM document_body JOIN part ON document_body.part_id=part.id LEFT JOIN pick_body ON document_body.id=pick_body.order_body_id WHERE document_header_id=".$mOrderHdrId;
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mOrdBdyId = $mRow['order_body_id'];
			$mPickBdyId = $mRow['id'];
			$mBdyLineNo = $mRow['our_line_no'];
			$mVehicleRef = $mRow['vehicle_ref'];
			$mPartNumber = $mRow['part_number'];
			$mPartId = $mRow['part_id'];
			$mConversionFactor = $mRow['conversion_factor'];
			if ($mPickBdyId == null)
			{
				$mPickQuery = "INSERT INTO pick_body (id,part_id,part_number,order_body_id,pick_header_id,customer_reference,document_reference,qty_expected,qty_transacted,qty_allocated,qty_shortage,processed,date_created,created_by,last_updated_date,last_updated_by) VALUES(DEFAULT,".$mRow['part_id'].",'".$mRow['part_number']."',".$mOrdBdyId.",".$mPickHdrId.",'".$mCustomerReference."','".$mPickDocumentReference."',".$mRow['qty_expected'].",0,0,".$mRow['qty_expected'].",0,now(),'".$mCurrentUser."_RDT',now(),'".$mCurrentUser."')";
				if (!mysqli_query($mConnection,$mPickQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
				$mPickBdyId=mysqli_insert_id($mConnection);	
			}
			$mPickDtlLine = $mBdyLineNo;
			$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.serial_reference AS serial_reference, inventory_master.inventory_qty AS inventory_qty, inventory_master.parent_tag_id AS tag_id, tag.tag_reference AS plt_number, location.location_code AS location_code FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE inventory_master.part_id=".$mPartId." AND inventory_master.inventory_status_id=".$mInvStsId." AND locate('".$mVehicleRef."',inventory_master.serial_reference)>0 LIMIT 1";
			if (!mysqli_query($mConnection,$mInvQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
			$mInvData = mysqli_query($mConnection,$mInvQuery);
			$i = 0;
			while($mInvRow = mysqli_fetch_assoc($mInvData)) 
			{
				$mLocationCode = $mInvRow['location_code'];
				$mTagReference = $mInvRow['plt_number'];
				$mPickInvId = $mInvRow['id'];
				$mTagId = $mInvRow['tag_id'];
				$i++;
			}
			if (($i > 0) && (($mTagId == null) || ($mTagId == "")))
				return "FAIL-This serial is not on a pallet";
			
			if (($i > 0) && ($mTagId != null) && ($mTagId != ""))
			{
				$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.serial_reference AS serial_reference, inventory_master.inventory_qty AS inventory_qty, tag.tag_reference AS plt_number, location.location_code AS location_code FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE inventory_master.parent_tag_id=".$mTagId;
				if (!mysqli_query($mConnection,$mInvQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
				$mInvData = mysqli_query($mConnection,$mInvQuery);
				while($mInvRow = mysqli_fetch_assoc($mInvData)) 
				{
					$mPickQuery = "SELECT pick_detail.id AS id FROM pick_detail WHERE pick_body_id=".$mPickBdyId." AND inventory_master_id=".$mInvRow['id']." LIMIT 1";
					if (!mysqli_query($mConnection,$mPickQuery))
						return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
					$mPickData = mysqli_query($mConnection,$mPickQuery);
					if (mysqli_num_rows($mPickData) == 0)
					{
						$mPickQuery = "INSERT INTO pick_detail (id,inventory_master_id,pick_body_id,part_id,part_number,serial_reference,document_reference,location_code,qty_allocated,line_no,pick_priority,processed,qty_scanned,date_created,created_by,last_updated_date,last_updated_by) VALUES(DEFAULT,".$mInvRow['id'].",".$mPickBdyId.",".$mPartId.",'".$mPartNumber."','".$mInvRow['serial_reference']."','".$mPickDocumentReference."','".$mInvRow['location_code']."',".$mInvRow['inventory_qty'].",".$mBdyLineNo.",1,0,0,now(),'".$mCurrentUser."_RDT',now(),'".$mCurrentUser."')";
						if (!mysqli_query($mConnection,$mPickQuery))
							return "FAIL-".mysqli_error($mConnection)."-".$mPickQuery;
						$mPickDtlId=mysqli_insert_id($mConnection);
					}
					else
					{
						while($mPickRow = mysqli_fetch_assoc($mPickData)) 
						{				
							$mPickDtlId = $mPickRow['id'];
						}
					}
				}
			}
		}		
	}

	if ($mPickDtlId != "")
	{
		$mDataQuery = "UPDATE pick_detail SET pick_detail.in_use_by='".$mCurrentUser."',last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE pick_detail.id=".$mPickDtlId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
// return Pick Details to the screen
		if ($mFullBoxPick == 0)
			$mReturn = "CHGTRAN:XPTM?DATA:customerReference:".$mCustomerReferenceCode.":P:skip3,aisleCode:".$mAisle.",fromLocationHashCode:".$mLocationCode.",plt:".$mTagReference.",allocationId:".$mPickDtlId.",sequencingRequestId:".$mPickInvId.",partNumber:".$mPartNumber.":HHHHHHH,lineNo:".$mPickDtlLine;
//			$mReturn = "DATA:customerReference:".$mCustomerReferenceCode.",aisleCode:".$mAisle.",fromLocationHashCode:".$mLocationCode.",plt:".$mTagReference.",allocationId:".$mPickDtlId.",sequencingRequestId:".$mPickInvId.",partNumber:".$mPartNumber.",lineNo:".$mPickDtlLine;
		else
			$mReturn = "CHGTRAN:XPTM?DATA:customerReference:".$mCustomerReferenceCode.":P:skip3,aisleCode:".$mAisle.",fromLocationHashCode:".$mLocationCode.",plt:".$mTagReference.",allocationId:".$mPickDtlId.",sequencingRequestId:".$mPickInvId.",partNumber:".$mPartNumber.":HHHHHHH,lineNo:".$mPickDtlLine;
//			$mReturn = "DATA:customerReference:".$mCustomerReferenceCode.",aisleCode:".$mAisle.",fromLocationHashCode:".$mLocationCode.",plt:".$mTagReference.",allocationId:".$mPickDtlId.",sequencingRequestId:".$mPickInvId.",partNumber:".$mPartNumber.",lineNo:".$mPickDtlLine;
	}
	else
	{
		$mReturn = "DATA:EOD-No matching serials for this pick";
	}
	mysqli_close($mConnection);

	return $mReturn;
}

// Flag Inventory / Pick Task as Missing
function missingPickSerial($mHost,$mUser,$mPwd,$mDbName,$mPickDtlId,$mPickInvId,$mSerialReference,$mPickQty,$mAisle,$mCurrentUser,$mTransactionTypeId,$mFromLoc,$mToPlt="")
{
	$mReturn = "";
	
	if ($mPickQty == null)
		$mPickQty = 0;
// mysql connection
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();
// Get id for inventory status
	$mInvStsId = 0;
	$mInvRcvId = 0;
	$mInvPckId = 0;
	$mInvPltId = 0;
	$mInvMisId = 0;
	$mDataQuery = "SELECT id AS id, inventory_status_code AS inventory_status_code FROM inventory_status";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		if (strtolower($mRow['inventory_status_code']) == "ok")
			$mInvStsId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "rcv")
			$mInvRcvId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "pkd")
			$mInvPkdId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "plt")
			$mInvPltId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "missing")
			$mInvMisId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "lost")
			$mInvMisId = ($mRow['id']);
	}
	
	if ($mInvPkdId == null && $mInvPltId != null)
		$mInvPkdId = $mInvPltId;
	
	$mDataQuery = "SELECT pick_detail.id AS id, pick_detail.pick_body_id AS pick_body_id, pick_detail.document_reference AS document_reference, pick_detail.part_id AS part_id, pick_detail.qty_allocated AS qty_allocated, pick_detail.location_code AS location_code FROM pick_detail WHERE id=".$mPickDtlId." LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$DataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mPickBdyId = "";
	$mPickDtlLoc = "";
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mPickBdyId = $mRow['pick_body_id'];
		$mDocumentReference = $mRow['document_reference'];
		$mPartId = $mRow['part_id'];
		$mPickDtlQty = $mRow['qty_allocated'];
		$mPickDtlLoc = $mRow['location_code'];
	}
// Validate Entered Location
	$mDataQuery = "SELECT id AS id, location_hash AS location_hash FROM location WHERE location_code='".$mPickDtlLoc."' LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$DataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
	$i = 0;
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		if ($mFromLoc == $mRow['location_hash'])
			$i++;
	}
//	if ($i == 0)
//		return "FAIL-Invalid Location-".$mDataQuery."-".$mPickDtlLoc."-".$mFromLoc;
	
	$mDataQuery = "UPDATE pick_detail SET qty_allocated=0,processed=1,last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPickDtlId." LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	mysqli_query($mConnection,$mDataQuery);
	if ($mPickBdyId != "")
	{
//		$mDataQuery = "UPDATE pick_body SET qty_allocated=(SELECT (CASE WHEN sum(qty_allocated) IS null THEN 0 ELSE sum(qty_allocated) END) FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id AND pick_detail.processed=0) WHERE pick_body.id=".$mPickBdyId." LIMIT 1";
		$mDataQuery = "UPDATE pick_body SET qty_allocated=(SELECT (CASE WHEN sum(qty_allocated) IS null THEN 0 ELSE sum(qty_allocated+qty_scanned) END) FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id) WHERE pick_body.id=".$mPickBdyId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	}
	if ($mInvMisId != null)
		$mDataQuery = "UPDATE inventory_master SET inventory_status_id=".$mInvMisId.", allocated_qty=0,last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPickInvId."  LIMIT 1";
	else
		$mDataQuery = "UPDATE inventory_master SET inventory_qty = 0, allocated_qty=0,last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPickInvId." LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	
	$mReturn = updatePickHistory($mHost,$mUser,$mPwd,$mDbName,$mPickDtlId,$mPickInvId,$mPickDtlQty,$mCurrentUser,"MISSING",$mFromLocCode);

	$mDataQuery = "SELECT id AS id, order_header_id AS order_header_id, customer_reference AS customer_reference FROM pick_header WHERE document_reference = '".$mDocumentReference."' LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$DataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mPickHdrId = "";
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mPickHdrId = $mRow['id'];
		$mOrderHdrId = $mRow['order_header_id'];
		$mCustomerReference = $mRow['customer_reference'];
	}
// Order Header
//	$mDataQuery = "SELECT order_header.id AS id, order_header.document_reference AS document_reference, order_header.time_slot AS time_slot, order_header.order_type AS order_type, '' AS pick_type, order_type.transaction_type_code AS transaction_type, order_type.pick_by_line, order_type.full_box_pick FROM order_header LEFT JOIN document_status ON order_header.document_status_id=document_status.id LEFT JOIN order_type ON order_header.order_type=order_type.order_type_code WHERE order_header.customer_reference='".$mCustomerReference."' AND (order_header.document_status_id IS null OR order_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated')) LIMIT 1";
	$mDataQuery = "SELECT document_header.id AS id, document_header.document_reference_code AS document_reference, document_header.time_slot AS time_slot, document_header.order_type AS order_type, document_header.pick_type AS pick_type, order_type.transaction_type_code AS transaction_type, order_type.pick_by_line, order_type.full_box_pick FROM document_header LEFT JOIN document_status ON document_header.document_status_id=document_status.id LEFT JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE document_header.document_type='RRM Order' AND document_header.customer_reference_code='".$mCustomerReference."' AND (document_header.document_status_id IS null OR document_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated')) LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
	if (mysqli_num_rows($mData) == 0)
	{
		$mDataQuery = "SELECT order_header.id AS id, order_header.document_reference AS document_reference, order_header.time_slot AS time_slot, order_header.order_type AS order_type, '' AS pick_type, order_type.transaction_type_code AS transaction_type, order_type.pick_by_line, order_type.full_box_pick FROM order_header LEFT JOIN document_status ON order_header.document_status_id=document_status.id LEFT JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE order_header.time_slot='".$mCustomerReference."' AND (order_header.document_status_id IS null OR order_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated')) ORDER BY order_header.expected_delivery";
//		$mDataQuery = "SELECT document_header.id AS id, document_header.document_reference_code AS document_reference, document_header.time_slot AS time_slot, document_header.order_type AS order_type, document_header.pick_type AS pick_type, order_type.transaction_type_code AS transaction_type, order_type.pick_by_line, order_type.full_box_pick FROM document_header LEFT JOIN document_status ON document_header.document_status_id=document_status.id LEFT JOIN order_type ON document_header.order_type=order_type.order_type_code WHERE document_header.time_slot='".$mCustomerReference."' AND (document_header.document_status_id IS null OR document_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated')) ORDER BY document_header.expected_delivery";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
	}
	$mOrderId = "";
	$mTimeSlot = "";
	$mPickByLine = 0;
	$mFullBoxPick = 1;
	$mOrderType = "";
	$mPickType = "";
	$mTransactionType = "";
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		if ($mOrderId != "")
			$mOrderId .= ",";
		$mOrderId .= ($mRow['id']);
		$mDocumentReferenceCode = ($mRow['document_reference']);
		$mOrderType = ($mRow['order_type']);
		$mPickType = ($mRow['pick_type']);
		$mTimeSlot = ($mRow['time_slot']);
		$mPickByLine = ($mRow['pick_by_line']);
		$mFullBoxPick = ($mRow['full_box_pick']);
		$mTransactionType = ($mRow['transaction_type']);
	}	
// Look for alternative Serial
	$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, part.conversion_factor AS conversion_factor, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id JOIN pick_group ON location.pick_group_id=pick_group.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mPartId." AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND inventory_status.non_pickable<>1 AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) IN('ok','ok')) ORDER BY inventory_master.date_created, location.location_code,inventory_master.inventory_qty LIMIT 1";
	if (!mysqli_query($mConnection,$mInvQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
	$mInvData = mysqli_query($mConnection,$mInvQuery);
	$mTest = "_PICKFACE";
	if (mysqli_num_rows($mInvData) == 0)
	{
		$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, part.conversion_factor AS conversion_factor, part.rdt_prompt AS rdt_prompt, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=".$mPartId." AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND inventory_status.non_pickable<>1 AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) IN('ok','ok')) ORDER BY inventory_master.date_created, location.location_code,inventory_master.inventory_qty LIMIT 1";
		if (!mysqli_query($mConnection,$mInvQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mInvQuery;
		$mInvData = mysqli_query($mConnection,$mInvQuery);
		$mTest="_NOTPICKFACE";
	}
	if (!mysqli_query($mConnection,$mInvQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$InvQuery;
	$mData = mysqli_query($mConnection,$mInvQuery);
	$i = 0;
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		if (($mRow['inventory_qty'] > 0) && ($mRow['inventory_qty'] < $mPickDtlQty))
			$mPickDtlQty = $mRow['inventory_qty'];
		if ($mRow['conversion_factor'] > 1)
			$mPickQty = $mPickDtlQty/$mRow['conversion_factor'];
		else
			$mPickQty = $mPickDtlQty;
			
		$mDataQuery = "UPDATE pick_detail SET inventory_master_id=".$mRow['id'].",part_id=".$mPartId.",part_number='".$mRow['part_number']."',serial_reference='".$mRow['serial_reference']."',location_code='".$mRow['location_code']."',qty_allocated=".$mPickDtlQty.", processed=0 WHERE id=".$mPickDtlId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
// return Pick Details to the screen
		if ($mToPlt == "")
			$mReturn = "DATA:fromLocationHashCodecontent:".$mRow['location_code'].",serialReferencecontent:".$mRow['serial_reference'].",qtyAllocated:".$mPickQty.",sequencingRequestId:".$mRow['id'].",rdtMessage:".$mRow['rdt_prompt'];
		else
			$mReturn = "DATA:fromLocationHashCodecontent:".$mRow['location_code'].",serialReferencecontent:".$mRow['serial_reference'].",qtyAllocated:".$mPickQty.",sequencingRequestId:".$mRow['id'].",rdtMessage:".$mRow['rdt_prompt']."::skip3";
		$i++;
	}
	if ($i == 0)
	{
		$mReturn = allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderHdrId, $mPickHdrId, $mCurrentUser,"");
		$mReturn = getPickDetail($mHost,$mUser,$mPwd,$mDbName,$mOrderHdrId,$mPickHdrId,$mAisle,$mCurrentUser,$mCustomerReference,$mPickByLine,$mFullBoxPick,$mToPlt);
		if (substr($mReturn,0,8) == "DATA:EOD")	
		{
// Hard-coding for Kanban Pick
//			if ($mOrderType == "350")
			if ($mTransactionType != "")
			{
//				$mTransactionType="PBTP";
//				if ($mToPlt != "")
					$mReturn = "CHGTRAN:".$mTransactionType."?DATA:customerReference:,aisleCode:ALL,toPlt:".$mToPlt;
//				else
//					$mReturn = "FAIL-No Pick Tasks";
			}
			else
				$mReturn .= ";customerReference;".$mCustomerReferenceCode;
		}
	}
//	return ("FAIL-TEST1-".$mReturn."-".$mCustomerReference."-".$mPickHdrId);
	if ($mReturn == "")
		$mReturn = "OK  -Missing Serial";
	return($mReturn);
}

// Post X-Doc Pick
function postXptm($mCustomerReference,$mPltNumber,$mToLocHash,$mCurrentUser,$mHost,$mUser,$mPwd,$mDbName,$mConnect='')
{
// mysql connection - Use Passed values...
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());	
	$mDataQuery = "SELECT id AS id, location_code AS location_code FROM location WHERE location.location_hash='".$mToLocHash."' LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	if (mysqli_num_rows($mData) == 0)
		return "FAIL-Invalid Location Code.";
	$mRow = mysqli_fetch_assoc($mData);
	$mToLocId = ($mRow['id']);
	$mToLocCode = ($mRow['location_code']);
// Get id for inventory status
	$mInvStsId = 0;
	$mInvRcvId = 0;
	$mInvPckId = 0;
	$mInvPltId = 0;
	$mDataQuery = "SELECT id AS id, inventory_status_code AS inventory_status_code FROM inventory_status";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		if (strtolower($mRow['inventory_status_code']) == "ok")
			$mInvStsId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "rcv")
			$mInvRcvId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "pkd")
			$mInvPkdId = ($mRow['id']);
		elseif (strtolower($mRow['inventory_status_code']) == "plt")
			$mInvPltId = ($mRow['id']);
	}
	
	if ($mInvPkdId == null && $mInvPltId != null)
		$mInvPkdId = $mInvPltId;

// Get id for document status
	$mDocStsId = 0;
	$mDocPkdId = 0;
	$mDataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while($mRow = mysqli_fetch_assoc($mData))
	{
		if (strtolower($mRow['document_status_code']) == "open")
			$mDocStsId=($mRow['id']);
		if (strtolower($mRow['document_status_code']) == "picked")
			$mDocPkdId=($mRow['id']);
	}
//	return ("FAIL-TEST1-".$mDataQuery."-".mysqli_num_rows($mData)."-".$mDocStsId."-".$mDocPkdId);
// Get Pick Header_id
	$mDataQuery = "SELECT id AS id, order_header_id AS order_header_id, document_reference AS document_reference FROM pick_header WHERE pick_header.customer_reference = '".$mCustomerReference."' AND document_status_id=".$mDocStsId." LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while ($mRow = mysqli_fetch_assoc($mData))
	{
		$mPckHdrId = ($mRow['id']);
		$mOrdHdrId = ($mRow['order_header_id']);
		$mDocumentReference = ($mRow['document_reference']);
	}

// Get Existing Inventory
	$mDataQuery = "SELECT inventory_master.id AS id, inventory_master.part_id AS part_id, part.part_number AS part_number, CASE WHEN inventory_master.serial_reference IS NULL THEN pick_detail.serial_reference ELSE inventory_master.serial_reference END AS serial_reference, inventory_master.ran_or_order AS ran_order, inventory_master.inventory_qty AS inventory_qty, tag.tag_reference AS plt_number, location.location_code AS location_code FROM inventory_master JOIN part ON inventory_master.part_id=part.id JOIN location ON inventory_master.current_location_id=location.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id LEFT JOIN pick_detail ON pick_detail.inventory_master_id=inventory_master.id WHERE (tag.tag_reference='".$mPltNumber."' OR inventory_master.serial_reference = '".$mPltNumber."') AND inventory_qty>0";  
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	if (mysqli_num_rows($mData) == 0)
		return "FAIL-No inventory exists for this pallet or serial reference.";
	$mPlt = "";
	$i = 0;
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
// Update Tables
		$mInvId = ($mRow['id']);
		$mInvQty = ($mRow['inventory_qty']);
		$mPlt = ($mRow['plt_number']);
		$mTranQuery = "INSERT INTO transaction_history (id,associated_document_reference,short_code,part_number,serial_reference,ran_or_order,txn_qty,source_tag,from_location_code,to_location_code,date_created,last_updated,last_updated_by,transaction_reference_code,from_plt,customer_reference) VALUES(DEFAULT,'".$mDocumentReference."','XPTM','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".($mRow['ran_order'])."',".($mRow['inventory_qty']).",'".($mRow['plt_number'])."','".($mRow['location_code'])."','".$mToLocCode."',now(),now(),'".$mCurrentUser."','XPTM','".$mRow['plt_number']."','".$mCustomerReference."')";	
		if (!mysqli_query($mConnection,$mTranQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;	
		
		if ($mInvId != "")
		{
			$mInvQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',inventory_status_id=".$mInvPkdId.",ran_or_order='".$mCustomerReference."',allocated_qty=0,last_updated=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mInvId." LIMIT 1";
//			$mInvQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',parent_tag_id=null, tag_reference='',last_updated=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mInvId." LIMIT 1";
			if (!mysqli_query($mConnection,$mInvQuery))
				return "FAIL-".mysqli_error($mConnection).$mInvQuery;
		}
// Update Pick Tables
		if ($i == 0)
		{
			$mOrdQuery = "SELECT document_body.wempf AS wempf,document_detail.tapos AS tapos FROM document_body JOIN document_detail ON document_detail.document_body_id=document_body.id WHERE document_header_id=".$mOrdHdrId." AND part_id=".$mRow['part_id']." LIMIT 1";
			if (!mysqli_query($mConnection,$mOrdQuery))
				return "FAIL-".mysqli_error($mConnection).$mOrdQuery;
			$mOrdData = (mysqli_query($mConnection,$mOrdQuery));
			while($mOrdRow = mysqli_fetch_assoc($mOrdData))
			{
				$mWempf = $mOrdRow['wempf'];
				$mTapos = $mOrdRow['tapos'];
			}
// Get Last TAPOS No.
			$mNextTapos = $mTapos;
			$mOrdQuery = "SELECT document_detail.tapos AS tapos FROM document_detail JOIN document_body ON document_detail.document_body_id=document_body.id WHERE document_body.document_header_id=".$mOrdHdrId." ORDER BY tapos DESC LIMIT 1";
			if (!mysqli_query($mConnection,$mOrdQuery))
				return "FAIL-".mysqli_error($mConnection).$mOrdQuery;
			$mOrdData = (mysqli_query($mConnection,$mOrdQuery));
			while($mOrdRow = mysqli_fetch_assoc($mOrdData))
			{
				$mNextTapos = $mOrdRow['tapos'];
			}
		}
// Get available Pick Bodies
		$mPckBdyId=0;
		$mOrdBdyId=0;
		$mPickQuery = "SELECT id AS id, order_body_id AS order_body_id FROM pick_body WHERE pick_header_id=".$mPckHdrId." AND part_id=".$mRow['part_id']." AND processed=0";
		if (!mysqli_query($mConnection,$mPickQuery))
			return "FAIL-".mysqli_error($mConnection).$mPickQuery;
		$mPickData = (mysqli_query($mConnection,$mPickQuery));
		while($mPickRow = mysqli_fetch_assoc($mPickData)) 
		{
			$mPckBdyId = ($mPickRow['id']);
			$mOrdBdyId = ($mPickRow['order_body_id']);
			$mOrdQuery = "UPDATE pick_detail SET qty_scanned=".$mInvQty;
			if ((strpos($mWempf, $mRow['serial_reference']) !== false) || (substr($mWempf,3)==$mRow['serial_reference']))
			{
				$mOrdQuery .= " ,serial_reference_scanned='".$mWempf."', line_no=".$mTapos;
			}
			else
			{
				$mNextTapos++;
				$mOrdQuery .= " ,serial_reference_scanned='".$mRow['serial_reference']."', line_no=".$mNextTapos;
			}
			$mOrdQuery .= " ,processed=1,in_use_by='',last_updated_date=now(),last_updated_by='".$mCurrentUser."'";
			$mOrdQuery .= " WHERE pick_body_id=".$mPickRow['id']." AND inventory_master_id=".$mInvId." LIMIT 1";
			if (!mysqli_query($mConnection,$mOrdQuery))
				return "FAIL-".mysqli_error($mConnection).$mOrdQuery;
//			print_r("FAIL-".mysqli_error($mConnection).$mOrdQuery);
			$mOrdQuery = "UPDATE pick_body SET qty_transacted=(SELECT SUM(pick_detail.qty_scanned) FROM pick_detail WHERE pick_detail.pick_body_id=".$mPckBdyId.")";
			$mOrdQuery .= ",last_updated_date=now(),last_updated_by='".$mCurrentUser."'";
//			$mOrdQuery .= ", processed= (CASE WHEN (qty_expected <= qty_transacted) THEN 1 ELSE 0 END)";
			$mOrdQuery .= " WHERE pick_body.id=".$mPickRow['id']." LIMIT 1";
			if (!mysqli_query($mConnection,$mOrdQuery))
				return "FAIL-".mysqli_error($mConnection).$mOrdQuery;
			if ($mOrdBdyId != null)
			{
				$mOrdQuery = "UPDATE document_body SET qty_transacted=(SELECT SUM(qty_transacted) FROM pick_body WHERE pick_body.order_body_id=".$mOrdBdyId."),last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mOrdBdyId." LIMIT 1";
				if (!mysqli_query($mConnection,$mOrdQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mOrdQuery;
				$mOrdQuery = "UPDATE document_detail SET qty_transacted=".$mInvQty.",last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE document_body_id=".$mOrdBdyId." AND serial_reference='".$mWempf."' LIMIT 1";
				if (!mysqli_query($mConnection,$mOrdQuery))
					return "FAIL-".mysqli_error($mConnection)."-".$mOrdQuery;
			}
		}
		$i++;		
	}
	
// Check for Pick Complete
	$mDataQuery = "UPDATE pick_body SET processed = (CASE WHEN qty_expected <= qty_transacted THEN 1 ELSE 0 END) WHERE pick_header_id=".$mPckHdrId." AND qty_expected > 0 AND processed=0";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mDataQuery = "SELECT id AS id FROM pick_body WHERE pick_header_id=".$mPckHdrId." AND processed=0 LIMIT 1";
	$mPickData = mysqli_query($mConnection,$mDataQuery);
	if (mysqli_num_rows($mPickData) == 0)
	{
		if ($mDocPkdId != "")
		{
			$mDataQuery = "UPDATE pick_header SET document_status_id=".$mDocPkdId.",last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPckHdrId." LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
//			$mDataQuery = "UPDATE order_header SET document_status_id=".$mDocPkdId.",last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mOrdHdrId." LIMIT 1";
			$mDataQuery = "UPDATE document_header SET picked=1, document_status_id=".$mDocPkdId.",last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mOrdHdrId." LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			
// Send Server Message
//		http://172.16.1.70:8092/screen/writeCrossDockCompleteMessage?documentReference=COR408605

			$mPickQuery = "SELECT document_reference_code AS document_reference, pick_type AS pick_type, order_type FROM document_header WHERE id=".$mOrdHdrId." LIMIT 1";
			$mPickData = mysqli_query($mConnection,$mPickQuery);
			$mPickType = "";
			while($mRow = mysqli_fetch_assoc($mPickData))
			{
				$mPickType = $mRow['pick_type'];
				$mOrderType = $mRow['order_type'];
				$mOrdDocRef = $mRow['document_reference'];
			}
	
// Get Server Data from URL
			$mJsonArray = array();
			$mJsonArray['documentReference'] = $mOrdDocRef;
// Convert Array to Json Object
			try
			{
				$mJsonObject = json_encode($mJsonArray, JSON_FORCE_OBJECT);
			}
			catch(Exception $e)
			{
				exit("FAIL-Json Error");
			}
// Replace default Server Tomcat Service
			$mConnectp = str_replace(":8081/vantec",":8092/screen",$mConnect);		
			$ch = curl_init();
			try
			{
				$mPickType = "XD";
				if ($mPickType == "XD")
					curl_setopt($ch, CURLOPT_URL, $mConnectp."writeCrossDockCompleteMessage?documentReference=".$mOrdDocRef);
				elseif ($mPickType == "RP")
					curl_setopt($ch, CURLOPT_URL, $mConnectp."writeReSeqCompleteMessage?documentReference=".$mOrdDocRef);
				else
					curl_setopt($ch, CURLOPT_URL, $mConnectp."writeOrderCompleteMessage?documentReference=".$mOrdDocRef);
				curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json')); 
				curl_setopt($ch, CURLOPT_POST, 1);
				curl_setopt($ch, CURLOPT_POSTFIELDS, $mJsonObject);
				curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
				$mResult = curl_exec($ch);
				$mErrors = curl_error($ch);
				$mResponse = curl_getinfo($ch, CURLINFO_HTTP_CODE);

				curl_close($ch);
				if ($mResponse != "200")
					$mErrMsg = "FAIL-Unexpected System Error -".$mResult.":".$mResponse.":".$mErrors.":".$mJsonObject;
				else
				{
					$mErrMsg = "OK  - Post Complete";
				}
			}
			catch (Exception $e)
			{
				$mErrMsg = "OKMS-Connection Error at - ".$mConnect.$mFilter.":".$e;
			}			
		}
	}	
	return "OK  -XPick Complete";
}
	
/*
// Clear Orphan Allocations
function clearAllocation($mConnection, $mDocId, $mCustomerReferenceCode, $mCurrentUser)
{
	$mDatQuery = "SELECT id AS id, part_id AS part_id FROM document_body WHERE document_header_id=".$mDocId." AND qty_transacted>=qty_expected AND qty_expected <> 0";
	$mData = mysqli_query($mConnection,$mDataQuery);
	if (mysqli_num_rows($mData) > 0)
	{
		while($mRow = mysqli_fetch_assoc($mData))
		{
			$mPartId = ($mRow['part_id']);
			$mAlcQuery = "SELECT allocation_detail.id AS id, allocation_detail.inventory_master_id AS inv_id FROM scheduled_work JOIN allocation_detail ON scheduled_work.id=allocation_detail.scheduled_work_id WHERE scheduled_work.third_party_reference='".$mCustomerReferenceCode."' AND allocation_detail.part_id=".$mPartId." AND allocation_detail.processed=0";
			$mAlcData = mysqli_query($mConnection,$mAlcQuery);
			if (mysqli_num_rows($mAlcData) > 0)
			{
				while($mAlcRow = mysqli_fetch_assoc($mAlcData))
				{
					mysqli_query($mConnection,"UPDATE inventory_master SET allocated_qty=0, last_updated=now(), last_updated_by='".$mCurrentUser."' WHERE id=".($mAlcRow['inv_id'])." LIMIT 1");
					mysqli_query($mConnection,"UPDATE allocation_detail SET processed=1, last_updated=now(), last_updated_by='".$mCurrentUser."' WHERE processed=0 AND id=".($mAlcRow['id']));
				}
			}
		}
	}
}
*/


// Return Default mySql Connection
function defaultDbConnection()
{

// get default System Settings from xml file	
	$mSystem = "DEFAULT";
// Get System Definition XML from URL
	$mData = file_get_contents("../../vimsconfig/systemsettings.xml");
// Parse XML to XML Object Array
	$xml = simplexml_load_string($mData, 'SimpleXMLElement', LIBXML_NOCDATA);
// Split-out System Definitions from XML 'fields' Nodes
	$i = 0;

	for ($i = 0; $i <= 150; $i++) 
	{			
		$mSystemId = "";
		$mPath = "";
		$mDbHost = "";
		$mDbUser = "";
		$mDbPwd = "";
		$mDbase = "";
		$mMqServer = "";
		$mCompany = "";
		if (("".$xml->systems->system[$i]->systemid."" == "") || ("".$xml->systems->system[$i]->systemid."" == false) || ("".$xml->systems->system[$i]->systemid."" == false))
			break;
		
		$mSystemId = "".$xml->systems->system[$i]->systemid."";
		if (($mSystemId == "") || ($mSystemId == false))
			break;
		
// Store System Settings from "trandef/systemsettings.xml"		
		if (strToUpper($mSystem) == strToUpper($mSystemId))
		{
			$mPath = "".$xml->systems->system[$i]->path."";
			$mDbHost = "".$xml->systems->system[$i]->dbhost."";
			$mDbUser = "".$xml->systems->system[$i]->dbuser."";
			$mDbPwd = "".$xml->systems->system[$i]->dbpwd."";
			$mDbase = "".$xml->systems->system[$i]->dbase."";
			break;
		}
		if (($mSystemId == "") || ($mSystemId == false))
			break;
	}

	$mConnection = mysqli_connect($mDbHost,$mDbUser,$mDbPwd,$mDbase)or die("FAIL-Failed to connect to Database: " . mysqli_connect_error().":".$mSystem);
	return $mConnection;

//	include ../../vimsconfig/phpConfig.php;
// return $connection;
}

// Get Input Parameters from POST or GET
if (!empty($_POST))
{
	$mFilter = "";
	foreach($_POST as $k1=>$v1)
	{
//		print_r("TEST".$k1.":".$v1);
		if ($mFilter != "")
			$mFilter .= "&";
		$mFilter .= $k1."=".$v1;
	}
}
else
{
	$mFilter = $_SERVER['QUERY_STRING'];
}

$mFilter = str_replace("=",":",$mFilter);
$mFilter = str_replace("&",",",$mFilter);

if (strpos($mFilter,"clearpickdetail"))
{
	$mCurrentUser = "";
// Explode Input String Into Array - Separated by ,
	$mArray1 = explode(",",$mFilter);
// Create Associative Array by Exploding Array Elements on :
	$mDbHost = "";
	foreach($mArray1 as $k1=>$v1)
	{
		list($k2, $v2) = explode(":",$mArray1[$k1]);
		if ($k2 == "function")
			$mFunction = $v2;
		elseif ($k2 == "currentUser")
			$mCurrentUser = $v2;
		elseif ($k2 == "host")
			$mDbHost = $v2;
		elseif ($k2 == "user")
			$mDbUser = $v2;
		elseif ($k2 == "pword")
			$mDbPwd = $v2;
		elseif ($k2 == "dbase")
			$mDbase = $v2;
	}
	if ($mDbHost == "")
		echo clearPickDetail($mCurrentUser);
	else
		echo clearPickDetail($mCurrentUser,$mDbHost,$mDbUser,$mDbPwd,$mDbase);
}

?>