<?php
header('Cache-Control: no-cache, no-store, must-revalidate');   
header('Expires: 0');
//postScreen - Transaction Processing
// echo "FAIL-test";

$mFunction  		= $_GET['function'];
$mConnect   		= $_GET['connection'];
$mUser				= $_GET['user'];
$mPwd				= $_GET['pword'];
$mHost              = $_GET['host'];
$mDbName			= $_GET['dbase'];
$mMqServer			 = $_GET['mqserver'];
$mTransactionTypeId = $_GET['transactionTypeid'];
$mSequencingRequestId = $_GET['sequencingRequestId'];
$mAllocationDetailId = $_GET['allocationId'];
$mSerialReference    = $_GET['serialReference'];
$mPartNumber         = $_GET['partNumber'];
$mAltPartNumber      = $_GET['altPartNumber'];
$mQty                = $_GET['qty'];
$mDocumentReferenceCode = $_GET['documentReferenceCode'];
$mFromLocationCode   = $_GET['fromLocationCode'];
$mToLocationCode     = $_GET['toLocationCode'];
$mCell               = $_GET['Cell'];
$mStillage           = $_GET['Stillage'];
$mComment            = $_GET['comment'];
$mFromTagCode		 = $_GET['fromTagCode'];
$mToTagCode          = $_GET['toTagCode'];
$mSequence           = $_GET['sequence'];
$mCurrentUser		 = $_GET['currentUser'];
$mCustomerReferenceCode = $_GET['customerReferenceCode'];
$mRanOrOrder		 = $_GET['ranOrOrder'];
$mInvId   			 = $_GET['invId'];
// $mRanOrOrder		 = " ";
if (($mTransactionTypeId == null) || ($mTransactionTypeId == ""))
	$mTransactionTypeId = "RBTD";
if (($mMqServer == "") || ($mMqServer == null))
	$mMqServer = "";
$mFunction = strToLower($mFunction);

$mReturn = "";

// Allocate available Inventory
if (($mFunction != null) && ($mFunction == "getpartforcell"))
{
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
		
	$mStartDate = date("Y-m-d H:i:s");
// Cell / Stillage Picking
	if (($mCell != null) && ($mCell != ""))
	{
// Find next available sequence part	
		$mDataQuery = "SELECT id, part_reference, sequence FROM ".$mCell." WHERE workstation='".$mCell."' AND document_reference_code = '".$mDocumentReferenceCode."' AND processed=0 ORDER by workstation, document_reference_code, processed, pick_sequence, sequence LIMIT 1";
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mSeqId = "";
		$mSeqPartRef = "";
		$mSeqSeq = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mSeqId = ($mRow['id']);
			$mSeqPartRef = ($mRow['part_reference']);
			$mSeqSeq = ($mRow['sequence']);
		}
		if ($mSeqPartRef == "")
			$mReturn = "DATA:EOD";

		if ($mReturn == "")
		{				
// Get related Part Id & Location
			$mDataQuery = "SELECT id, fixed_location_id, secondary_location_id, jis_supply_group AS vendor_part_code FROM part WHERE part_number = '".$mSeqPartRef."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mPartId = "";
			$mPartFixedLoc = "";
			$mPartSecondLoc = "";
			$mPartVendorPart = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mPartId = ($mRow['id']);
				$mPartVendorPart = ($mRow['vendor_part_code']);
				$mInvLoc = ($mRow['fixed_location_id']);
				$mPartFixedLoc = ($mRow['fixed_location_id']);
				$mPartSecondLoc = ($mRow['secondary_location_id']);
				if (($mCell == "CELL7") && ($mPartSecondLoc != ""))
				{
					$mPartFixedLoc = ($mRow['secondary_location_id']);
					$mPartSecondLoc = ($mRow['fixed_location_id']);
				}
			}
			if ($mPartId == "")
				$mReturn = "FAIL-Unknown Part";
		}
		$mInvId = "";
		if ($mReturn == "")
		{
// Allocate Inventory
			$mDataQuery = "SELECT id, current_location_id, inventory_qty FROM inventory_master WHERE part_id=".$mPartId." AND current_location_id=".$mPartFixedLoc." AND inventory_qty > allocated_qty AND serial_reference<>'' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mInvId = "";
			$mInvLoc = "";
			$mInvQty = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mInvId = ($mRow['id']);
				$mInvLoc = ($mRow['current_location_id']);
				$mInvQty = ($mRow['inventory_qty']);
			}
			if (($mInvId == "") && ($mPartSecondLoc != "") && ($mPartSecondLoc != null))
			{
				$mDataQuery = "SELECT id, current_location_id, inventory_qty FROM inventory_master WHERE part_id=".$mPartId." AND current_location_id=".$mPartSecondLoc." AND inventory_qty > allocated_qty AND serial_reference<>'' LIMIT 1";
				$mData = mysqli_query($mConnection,$mDataQuery);
				$mInvId = "";
				$mInvLoc = "";
				$mInvQty = "";
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mInvId = ($mRow['id']);
					$mInvLoc = ($mRow['current_location_id']);
					$mInvQty = ($mRow['inventory_qty']);
				}
			}
			if ($mInvId == "")
			{
				$mReturn = "DATA:NO STOCK - ".$mSeqPartRef;
			}		
		}
		if ($mReturn == "")
		{
			$mLocLoc = "XX";
//		$mPartVendorPart = "40156685";

			$mDataQuery = "SELECT location_code, location_hash FROM location WHERE id=".$mInvLoc." LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mLocLoc = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mLocLoc = ($mRow['location_code']);
			}
		
		// Get Secondary Location Code
			$mLocSecondLoc = "";
			if (($mInvId == "") && ($mPartSecondLoc != ""))
			{
				$mDataQuery = "SELECT location_code, location_hash FROM location WHERE id=".$mPartSecondLoc." LIMIT 1";
				$mData = mysqli_query($mConnection,$mDataQuery);
				$mLocSecondLoc = "";
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mLocSecondLoc = ($mRow['location_code']);
				}
				if ($mLocSecondLoc != "")
					$mLocLoc .= " ".$mLocSecondLoc;
			}

			$mReturn = "DATA:sequencingRequestId:".$mSeqId.",allocationId:null,fromLocationCode:".$mLocLoc.",partNumber:".$mSeqPartRef.",comment:".$mPartVendorPart.",sequence:".$mSeqSeq.",altPartNumber:".$mPartVendorPart.",qty:1";
		
			$mCellMessage = '{"function":"colourcell","workstation":"'.$mCell.'","documentreferencecode":"'.$mDocumentReferenceCode.'","sequence":[';
			$mDataQuery = "SELECT sequence FROM ".$mCell." WHERE document_reference_code='".$mDocumentReferenceCode."' AND part_reference='".$mSeqPartRef."' AND processed <> 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$i = 0;
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				if ($i > 0)
					$mCellMessage .= ',';
				$mCellMessage .= '{"sequence":'.$mRow['sequence'].',"colour":"CYAN"}';
				$i = $i+1;
			}
			$mCellMessage .= ']}';
			if ($mMqServer != "")
			{
				try
				{
					$stomp = new Stomp("tcp://".$mMqServer.":61613");
				}
				catch (Exception $e)
				{
				}
				if ($e == "")
				{
					$stomp->send("/topic/".$mCell, $mCellMessage, array("id"=>1,"persistent"=>"true","content-type"=>"text/xml;charset=utf-8"));
					unset($stomp);
				}
			}
		}
		mysqli_close($mConnection);
		echo($mReturn);
		return;
	}
// Pick by Customer Reference
	elseIf (($mCustomerReferenceCode != null) && ($mCustomerReferenceCode != ""))
	{
//		exit("DATA:fromLocationCode:UNKNOWN,partNumber:UNKNOWN");
// Document Header
		$mReturn = "";
//		$mDataQuery = "SELECT id AS id FROM document_header WHERE customer_reference_code='".$mCustomerReferenceCode."' AND complete=0  AND document_type='Pick' LIMIT 1";
		$mDataQuery = "SELECT id AS id, document_reference_code AS document_reference_code FROM document_header WHERE customer_reference_code='".$mCustomerReferenceCode."' AND complete=0  AND document_type='Pick' LIMIT 1";
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mId = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mId = ($mRow['id']);
			$mDocumentReferenceCode = ($mRow['document_reference_code']);
		}
		if ($mId == "")
		{
			$mReturn = "FAIL-Invalid Pick Ref.".$mCustomerReferenceCode;
			exit($mReturn);
		}
		if ($mReturn == "")
		{
			$mReturn = allocateScheduledWork($mConnection,$mHost,$mUser,$mPwd,$mDbName,$mDocumentReferenceCode=null, $mId, $mInvId, $mCurrentUser);
			if ($mReturn == "")
				$mReturn = "FAIL-Allocation Error";
			if ($mReturn == "DATA:EOD")
			{
				$mReturn = allocateOrderLines($mConnection,$mDocumentReferenceCode, $mId, $mCustomerReferenceCode, $mCurrentUser);
				if (substr($mReturn,0,5)!="FAIL-")
				{	
					if ($mReturn != "DATA:EOD")
						$mReturn = allocateScheduledWork($mConnection,$mHost,$mUser,$mPwd,$mDbName,$mDocumentReferenceCode=null, $mId, $mInvId, $mCurrentUser);
					if ($mReturn == "")
					$mReturn = "FAIL-Allocation Error";
				}
			}
			exit($mReturn);
		}
	}
}

// re-Allocate Serial
if (($mFunction != null) && ($mFunction == "getallocation"))
{
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
		
	$mStartDate = date("Y-m-d H:i:s");
/*
// Check for change of Serial
	$mDataQuery = "SELECT inventory_master.id AS id, inventory_master.serial_reference AS serial_reference, part.part_number AS part_number FROM inventory_master JOIN part ON inventory_master.part_id=part.id WHERE inventory_master.id=".$mInvId." LIMIT 1";
	$mData = mysqli_query($mConnection,$mDataQuery);
	$i = 0;
	while($mRow = mysqli_fetch_assoc($mData))  
	{
		if(($mPartNumber == ($mRow['part_number'])) && ($mSerialReference == ($mRow['serial_reference'])));
		{
			$mReturn = "DATA:sequencingRequestId:".$mSequencingRequestId.",allocationId:".$mAllocationDetailId.",fromLocationCode:".$mFromLocationCode.",partNumber:".$mPartNumber.",invId:".$mInvId;
			exit($mReturn);
		}
		$i = $i + 1;
	}
	if ($i == 0)
	{
		$mReturn = "FAIL-Invalid Serial for Part";
		exit($mReturn);
	}
*/
// Get Full Box Pick Flag based on Order Type
	$mDataQuery = "SELECT order_type_cross_reference.id AS id, order_type_cross_reference.full_box_pick AS full_box_pick FROM document_header JOIN order_type_cross_reference ON document_header.order_type=order_type_cross_reference.rr_order_type WHERE document_header.customer_reference_code='".$mCustomerReferenceCode."' AND document_header.complete=0  AND document_header.document_type like('%Order%') LIMIT 1";
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mFullBoxPick = "";
	$i = 0;
	while($mRow = mysqli_fetch_assoc($mData))  
	{
		$mFullBoxPick = ($mRow['full_box_pick']);
		$i = $i + 1;
	}
//	$mReturn = "FAIL-TEST0 Invalid  ".$mFullBoxPick.":".$mDataQuery.":".$i;
//	exit($mReturn);
// get Inventory Master
	$mDataQuery = "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, location.location_code AS location_code FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN location_type ON location.location_type_id=location_type.id JOIN inventory_status ON location_type.default_inventory_status_id=inventory_status.id LEFT JOIN part ON inventory_master.part_id=part.id WHERE inventory_master.serial_reference='".$mSerialReference."' AND part.part_number='".$mPartNumber."' AND inventory_master.inventory_qty >0 AND inventory_status.non_pickable=0 ORDER BY location.location_pick_seq, location.location_code, inventory_master.stock_date LIMIT 1";
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mInvInvId = "";
	$mInvInventoryQty = 0;
	$mInvAllocatedQty = 0;
	$mLocationCode = "";
	$i = 0;
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mInvInvId = ($mRow['id']);
		$mInvInventoryQty= ($mRow['inventory_qty']);
		$mInvAllocatedQty= ($mRow['allocated_qty']);
		$mLocationCode= ($mRow['location_code']);
		$i = $i+1;
	}
	if ($i == 0)
	{
		$mReturn = "FAIL-Invalid Serial for Part ".$mPartNumber;
		exit($mReturn);
	}
// Check for existing Allocation for this Part_Number / Serial_Reference
	if ($mInvId != $mInvInvId)
	{
		$mDataQuery = "UPDATE allocation_detail SET last_updated=now(),last_updated_by='".$mCurrentUser."',inventory_master_id=".$mInvId." WHERE inventory_master_id=".$mInvInvId." AND processed=0";
		mysqli_query($mConnection,$mDataQuery);
	}
// get Allocation Detail
	$mDataQuery = "SELECT allocation_detail.id AS id, allocation_detail.allocated_qty AS allocated_qty, allocation_detail.inventory_master_id AS inventory_master_id FROM allocation_detail WHERE allocation_detail.id=".$mAllocationDetailId." LIMIT 1";
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mAlcInvId = "";
	$mAlcAllocatedQty = 0;
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mAlcInvId = ($mRow['inventory_master_id']);
		$mAlcAllocatedQty= ($mRow['allocated_qty']);
	}
// unallocate Inventory Master
	$mDataQuery = "UPDATE inventory_master SET last_updated=now(),last_updated_by='".$mCurrentUser."', allocated_qty=allocated_qty-".$mAlcAllocatedQty." WHERE id=".$mAlcInvId." LIMIT 1";
	mysqli_query($mConnection,$mDataQuery);
// if Full Box Pick - Allocate Entire Serial Qty.
	if (($mFullBoxPick == "1") || ($mAlcAllocatedQty > $mInvInventoryQty))
		$mAlcAllocatedQty = $mInvInventoryQty;
// Update Allocation & Inventory
	$mDataQuery = "UPDATE allocation_detail SET last_updated=now(),last_updated_by='".$mCurrentUser."', allocated_qty=".$mAlcAllocatedQty.", inventory_master_id=".$mInvInvId." WHERE id=".$mAllocationDetailId." LIMIT 1";
	mysqli_query($mConnection,$mDataQuery);
	$mDataQuery = "UPDATE inventory_master SET last_updated=now(),last_updated_by='".$mCurrentUser."', allocated_qty=".$mAlcAllocatedQty." WHERE id=".$mInvInvId." LIMIT 1";
	mysqli_query($mConnection,$mDataQuery);
// Return Screen Details
	if ($mInvInvId != "")
		$mReturn = "DATA:sequencingRequestId:".$mSequencingRequestId.",allocationId:".$mAllocationDetailId.",fromLocationCode:".$mLocationCode.",partNumber:".$mPartNumber.",qty:".$mAlcAllocatedQty.",invId:".$mInvInvId;
	else
		$mReturn = "FAIL-Invalid Serial for Part ".$mPartNumber;
	exit($mReturn);
}

// Run SHELL Command Script
if (($mFunction != null) && ($mFunction=="groovy"))
{
	$mReturn = shell_exec("groovyscript.bat");
	return;
}

// Send JMS CELL Message using STOMP protocol.
if (($mFunction != null) && ($mFunction == "sendjms"))
{
	if ($mMqServer != "")
	{
		try 
		{
			$stomp = new Stomp("tcp://".$mMqServer.":61613");	
		} 
		catch(Exception $e) 
		{
			die("FAIL-Connection failed:".$e->getMessage());
		}
// send a message to the queue
	$mReturn = "<document status='OK' part='".$mPartNumber."' stillagesequence='".$mSequence."' locationSeq='".$mSequence."' />";
		if ($e == "")
		{
			$stomp->send("/topic/".$mCell, "<document status='OK' part='".$mPartNumber."' stillagesequence='".$mSequence."' locationSeq='".$mSequence."' />", array("id"=>1,"persistent"=>"true","content-type"=>"text/xml;charset=utf-8"));
// close connection
			unset($stomp);
		}
		// send a message to the queue
		$mReturn = "<document status='OK' part='".$mPartNumber."' stillagesequence='".$mSequence."' locationSeq='".$mSequence."' />";
		echo("jms:".$mReturn);
	}
	return;
}

if (($mFunction != null) && ($mFunction == "postscreen"))
{
// Get Transaction Definition
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
	exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
	if (is_numeric($mTransactionTypeId))
		$mDataQuery = "SELECT id, associated_document_type, process_type, short_code, transaction_reference_code, auto_create_document FROM ".$mDbName.".transaction_type WHERE id = ".$mTransactionTypeId." LIMIT 1";
	else
		$mDataQuery = "SELECT id, associated_document_type, process_type, short_code, transaction_reference_code, auto_create_document FROM ".$mDbName.".transaction_type WHERE short_code = '".$mTransactionTypeId."' LIMIT 1";
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mTranId = "";
	$mFunction = "";
	$mTranDocumentType = "";
	$mTranShortCode = "";
	$mTranRefCode = "";
	$mAutoCreateDoc = "";
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mTranId = ($mRow['id']);
		$mFunction = ($mRow['process_type']);
		$mTranDocumentType = ($mRow['associated_document_type']);
		$mTranShortCode = ($mRow['short_code']);
		$mTranRefCode = ($mRow['transaction_reference_code']);
		$mAutoCreateDoc = ($mRow['auto_create_document']);
	}
//	exit("FAIL-TEST2 ".$mTransactionTypeId.":".$mFunction.":".$mAutoCreateDoc);
	if (($mFunction != null) && ($mFunction == "Receipt"))
	{
//		$mRanOrOrder = "";
// Validate Document Ref.
//		if (($mAutoCreateDoc != "") && ($mAutoCreateDoc != null))
		if ($mAutoCreateDoc == "0")
		{
			$mdataQuery = "SELECT id AS id FROM ".$mDbName.".document_header WHERE document_reference_code='".$mDocumentReferenceCode."' AND active=1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mId = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mId = ($mRow['id']);
			}
			if ($mId == "")
			{
				$mReturn = "FAIL-Invalid Receipt Ref.";
				exit($mReturn);
			}
		}
// Validate Part Number
		$mDataQuery = "SELECT id AS part_id, conversion_factor FROM ".$mDbName.".part WHERE part_number = '".$mPartNumber."' LIMIT 1";
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mPartId = "";
		$mPartConvFactor = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mPartId = ($mRow['part_id']);
			$mPartConvFactor = ($mRow['conversion_factor']);
		}
		if ($mPartId == "")
			$mReturn = "FAIL-Wrong Part Number";
		if ($mReturn == "")
		{
// Check if Part / Serial already exists
			$mDataQuery = "SELECT id AS inv_id, inventory_qty AS inventory_qty FROM ".$mDbName.".inventory_master WHERE part_id = ".$mPartId." AND serial_reference='".$mSerialReference."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mInvId = "";
			$mInvQty = "0";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mInvId = ($mRow['inv_id']);
				$mInvQty = ($mRow['inventory_qty']);
			}
			if (($mInvId != "") && (intval($mInvQty) != 0))
				$mReturn = "FAIL-Duplicate Serial - Qty.".$mInvQty;
		}
		if ($mReturn == "")
		{
// Validate Location Code
			$mDataQuery = "SELECT id AS loc_id, location_code AS location_code FROM ".$mDbName.".location WHERE location_code = '".$mToLocationCode."' OR location_hash = '".$mToLocationCode."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mLocId = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mLocId = ($mRow['loc_id']);
				$mToLocationCode = ($mRow['location_code']);
			}
			if ($mLocId == "")
				$mReturn = "FAIL-Wrong Location";
		}
		if ($mReturn == "")
		{
			$mToTagId = "";
// Create / Retrieve Pallet Tag
			if (($mToTagCode != "") && ($mToTagCode != null))
			{
				$mDataQuery = "SELECT id AS id FROM ".$mDbName.".tag WHERE tag_reference='".$mToTagCode."' LIMIT 1";
				$mData = mysqli_query($mConnection,$mDataQuery);
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mToTagId = ($mRow['id']);
				}
				if ($mToTagId == "")
				{
					$mDataQuery = "INSERT INTO ".$mDbName.".tag (id,version,auto_generated,current_location_id,date_created,document_header_id,last_updated,last_updated_by,tag_reference,validated,active) VALUES(DEFAULT,0,0,null,now(),null,now(),'".$mCurrentUser."','".$mToTagCode."',1,1)";
					mysqli_query($mConnection,$mDataQuery);
					$mDataQuery = "SELECT id AS id FROM ".$mDbName.".tag WHERE tag_reference='".$mToTagCode."' LIMIT 1";
					$mData = mysqli_query($mConnection,$mDataQuery);
					while($mRow = mysqli_fetch_assoc($mData)) 
					{
						$mToTagId = ($mRow['id']);
					}
				}
			}
			If ($mAutoCreateDoc != "0")
			{
// Document_Header
				$mDataQuery = "SELECT id, document_reference_code FROM ".$mDbName.".document_header WHERE document_type='".$mTranDocumentType."' AND customer_reference_code='".$mCustomerReferenceCode."' AND active=1 LIMIT 1";
				$mData = mysqli_query($mConnection,$mDataQuery);
				$mDocumentReferenceCode = "";
				$mDocumentHdrId = "";
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mDocumentHdrId = ($mRow['id']);
					$mDocumentReferenceCode = ($mRow['document_reference_code']);
				}
				if ($mDocumentHdrId == "")
				{
					$mDataQuery = "UPDATE ".$mDbName.".document_register SET version=version+1,last_updated=now(),last_updated_by='".$mCurrentUser."',register_value=register_value+1 WHERE document_type='".$mTranDocumentType."'";
					mysqli_query($mConnection,$mDataQuery);
					$mDataQuery = "SELECT register_value FROM ".$mDbName.".document_register WHERE document_type='".$mTranDocumentType."' LIMIT 1";
					$mData = mysqli_query($mConnection,$mDataQuery);
					$mDocumentReferenceCode = "";
					while($mRow = mysqli_fetch_assoc($mData)) 
					{			
//						$mDocumentReferenceCode = "SQR".($mRow['register_value']);
						$mDocumentReferenceCode = "RCP".($mRow['register_value']);
					}
					if ($mDocumentReferenceCode == "")
						$mDocumentReferenceCode = "SQR999999";
					$mDataQuery = "INSERT INTO ".$mDbName.".document_header (id, version, active, auto_confirm, auto_created, complete, date_created, document_reference_code, document_type, last_updated, last_updated_by, vendor_id, class, expected_delivery, document_disposition_id, inspection_required, qa_type, time_slot, stillage, work_station, despatched, marshalled, picked, parent_id, customer_order_id, bwlvs, lgnum, pldat, plzei, tanum, zpind, customer_reference_code, requirement_header_id, scheduled_work_id, stock_shortage, allocated, transmitted) VALUES(DEFAULT,0,0,0,0,0,now(),'".$mDocumentReferenceCode."','".$mTranDocumentType."',now(),'".$mCurrentUser."',3,'com.vantec.documents.receipts.CustomerReceiptHeader',null,null,0,null,null,null,null,0,0,0,null,null,null,null,null,null,null,null,'".$mCustomerReferenceCode."',null,null,0,null,null)";
					mysqli_query($mConnection,$mDataQuery);
					$mDocumentHdrId=mysqli_insert_id($mConnection);
//					$mDataQuery = "SELECT id FROM ".$mDbName.".document_header WHERE document_reference_code='".$mDocumentReferenceCode."' AND complete <> 1 LIMIT 1";
//					$mData = mysqli_query($mConnection,$mDataQuery);
//					$mDocumentHdrId = "";
//					while($mRow = mysqli_fetch_assoc($mData)) 
//					{
//						$mDocumentHdrId = ($mRow['id']);
//					}
				}
				else
				{
					$mDataQuery = "UPDATE ".$mDbName.".document_header SET last_updated=now(), last_updated_by='".$mCurrentUser."' WHERE id=".$mDocumentHdrId;
					mysqli_query($mConnection,$mDataQuery);
				}
			}
			else
			{
				$mDataQuery = "SELECT id FROM ".$mDbName.".document_header WHERE document_reference_code='".$mDocumentReferenceCode."' AND active = 1 LIMIT 1";
				$mData = mysqli_query($mConnection,$mDataQuery);
				$mDocumentHdrId = "";
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mDocumentHdrId = ($mRow['id']);
				}
			}		
			if ($mDocumentHdrId != "")
			{
// Document_body
				$mDataQuery = "SELECT id AS body_id FROM ".$mDbName.".document_body WHERE document_header_id=".$mDocumentHdrId." AND part_id=".$mPartId." LIMIT 1";
				$mData = mysqli_query($mConnection,$mDataQuery);
				$mDocumentBodyId = "";
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mDocumentBodyId = ($mRow['body_id']);
				}
				if ($mDocumentBodyId == "")
				{
					$mDataQuery = "INSERT INTO ".$mDbName.".document_body (id,version,date_created,document_header_id,external_reference,last_updated,last_updated_by,part_id,qty_expected,qty_transacted,class,alpha_qty_expected,our_line_no,kbnkz, kqvnq, matnr, nsolm, tapos, vsolm, wdatu, wepos, active, qty_allocated, requirement_body_id) VALUES(DEFAULT,0,now(),".$mDocumentHdrId.",'',now(),'".$mCurrentUser."',".$mPartId.",0,".$mQty.",'com.vantec.documents.receipts.CustomerReceiptBody','0',0,'','','','','','','','',1,0,null)";
					try{
						mysqli_query($mConnection,$mDataQuery);
						$mDocumentBodyId=mysqli_insert_id($mConnection);
					} catch(Exception $e) {
						exit("FAIL-Document_Body:".$e->getMessage());
					}
//					$mDataQuery = "SELECT id AS body_id FROM ".$mDbName.".document_body WHERE document_header_id=".$mDocumentHdrId." AND part_id =".$mPartId." LIMIT 1";
//					$mData = mysqli_query($mConnection,$mDataQuery);
//					$mDocumentBodyId = "";
//					while($mRow = mysqli_fetch_assoc($mData)) 
//					{
//						$mDocumentBodyId = ($mRow['body_id']);
//					}
//					echo("FAIL-Document_body1:".$mDocumentHdrId.":".$mDocumentBodyId);
				}
				else
				{
					$mDataQuery = "UPDATE ".$mDbName.".document_body SET last_updated=now(),last_updated_by='".$mCurrentUser."',qty_transacted=qty_transacted+".$mQty." WHERE id=".$mDocumentBodyId;
					mysqli_query($mConnection,$mDataQuery);
				}
// Document_detail
				if ($mDocumentBodyId != "")
				{
					$mDocumentDtlId = "";
					$mDataQuery = "SELECT id AS id FROM document_detail WHERE document_body_id=".$mDocumentBodyId." AND serial_reference='".$mSerialReference."' LIMIT 1";
					$mData = mysqli_query($mConnection,$mDataQuery);
					while($mRow = mysqli_fetch_assoc($mData)) 
					{
						$mDocumentDtlId = ($mRow['id']);
					}
					if ($mDocumentDtlId == "")
					{	
						$mDataQuery = "INSERT INTO document_detail (id, version, date_created, document_body_id, last_updated, last_updated_by, qty_expected, qty_transacted, serial_reference, class, chassis, sequence, trim_sequence, validated, active, kbnkz, kqvnq, matnr, nsolm, our_detail_no, tapos, vsolm, wdatu, wepos, qty_allocated, requirement_detail_id) VALUES(DEFAULT,0,now(),".$mDocumentBodyId.",now(),'".$mDocumentReferenceCode."',0,".$mQty.",'".$mSerialReference."','com.vantec.documents.receipts.CustomerReceiptDetail','',null,'',0,1,'','','".$mPartNumber."','".$mRanOrOrder."',0,'','','','',0,null)";
						try
						{
							mysqli_query($mConnection,$mDataQuery);
							$mDocumentDtlId=mysqli_insert_id($mConnection);
						}
						catch(Exception $e)
						{
						}
					}
					else
					{
						$mDataQuery = "UPDATE document_detail SET qty_transacted=qty_transacted+".$mQty.", last_updated=now(), last_updated_by='".$mDocumentReferenceCode."' WHERE id=".$mDocumentDtlId." LIMIT 1";
						mysqli_query($mConnection,$mDataQuery);
					}
//					exit("FAIL-Last Insert:".$mDocumentHdrId);
				}
			}
// Create Inventory
//			if (($mRanOrOrder == "") || ($mRanOrOrder == null))
//				$mRanOrOrder = $mDocumentReferenceCode;
			if (($mInvId != "") && (intval($mInvQty)==0))
			{
				$mDataQuery = "DELETE FROM ".$mDbName.".inventory_master WHERE id=".$mInvId;
				mysqli_query($mConnection,$mDataQuery);
			}
			if ($mToTagId == "")
				$mDataQuery = "INSERT INTO ".$mDbName.".inventory_master (id,version,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) VALUES(DEFAULT,0,0,null,null,".$mLocId.",now(),null,".$mQty.",now(),'".$mCurrentUser."',null,".$mPartId.",'".$mRanOrOrder."',0,0,0,'".$mSerialReference."',".$mDocumentDtlId.",".$mPartConvFactor.",now(),1)";
			else	
				$mDataQuery = "INSERT INTO ".$mDbName.".inventory_master (id,version,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) VALUES(DEFAULT,0,0,null,null,".$mLocId.",now(),null,".$mQty.",now(),'".$mCurrentUser."',".$mToTagId.",".$mPartId.",'".$mRanOrOrder."',0,0,0,'".$mSerialReference."',".$mDocumentDtlId.",".$mPartConvFactor.",now(),1)";
			mysqli_query($mConnection,$mDataQuery);
			$mInvId=mysqli_insert_id($mConnection);
			
// Transaction History
//			if (($mParentTag != "") && ($mParentTag != null))
			if ($mToTagId == "")
				$mDataQuery = "INSERT INTO ".$mDbName.".transaction_history (id,associated_document_reference,date_created,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,to_location_code,transaction_reference_code,txn_qty,comment,active,conversion_factor) VALUES(DEFAULT,'".$mDocumentReferenceCode."',now(),'".$mFromLocationCode."',now(),'".$mCurrentUser."',null,'".$mPartNumber."','".$mRanOrOrder."','".$mSerialReference."','".$mTranShortCode."','".$mToLocationCode."','".$mTranRefCode."',".$mQty.",'',1,0)";
			else
				$mDataQuery = "INSERT INTO ".$mDbName.".transaction_history (id,associated_document_reference,date_created,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,to_location_code,transaction_reference_code,txn_qty,comment,active,conversion_factor) VALUES(DEFAULT,'".$mDocumentReferenceCode."',now(),'".$mFromLocationCode."',now(),'".$mCurrentUser."',".$mToTagId.",'".$mPartNumber."','".$mRanOrOrder."','".$mSerialReference."','".$mTranShortCode."','".$mToLocationCode."','".$mTranRefCode."',".$mQty.",'',1,0)";
			mysqli_query($mConnection,$mDataQuery);
		}
// Auto-Complete Document_Header
		if (($mDocumentReferenceCode != "") && ($mDocumentReferenceCode != null))
		{
			if ($mTranDocumentType == "Sequence Receipt")
			{
//				$mDataQuery = "UPDATE ".$mDbName.".document_header SET version=version+1,active=1,auto_confirm=1,auto_created=1, last_updated=now(),last_updated_by='".$mCurrentUser."',expected_delivery=now(),document_disposition_id=1 WHERE document_reference_code='".$mDocumentReferenceCode."'";
//				mysqli_query($mConnection,$mDataQuery);
				$mReturn = "DATA:documentReferenceCode:".$mDocumentReferenceCode;
			}
		}
		if ($mReturn == "")
		{
			$mReturn = "OK  -";
		}
	}
	
	if (($mFunction != null) && ($mFunction == "Transfer"))
	{
//		addUpdateDocument($mConnection,$mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,"PCK",$mDocumentReferenceCode,$mCustomerReferenceCode,$mTranDocumentType,$mPartId,$mPartNumber,$mSerialReference,$mQty,$mRanOrOrder);
//		exit("FAIL-Transfer:".$mAllocationDetailId);
		$mAllowSerialReuse = "1";
// Get Parent_Tag
		$mParentTagId = "";
		if (($mDocumentReferenceCode != "") && ($mDocumentReferenceCode != null))
		{
			$mDataQuery = "SELECT id AS parent_tag_id FROM ".$mDbName.".tag WHERE tag_reference='".$mDocumentReferenceCode."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mParentTagId = ($mRow['parent_tag_id']);
			}
		}
// Get From_Tag
		$mFromTagId = "";
		if (($mFromTagCode != "") && ($mFromTagCode != null))
		{
			$mDataQuery = "SELECT id AS from_tag_id FROM ".$mDbName.".tag WHERE tag_reference='".$mFromTagCode."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mFromTagId = ($mRow['from_tag_id']);
			}
			if ($mFromTagId == "")
				exit("FAIL-Invalid Pallet/Tag - ".$mFromTagCode);
		}
// Get Location Ids.
		$mFromLocationId = "";
		if (($mFromLocationCode != "") && ($mFromLocationCode != null))
		{
			$mDataQuery = "SELECT id AS id, location_code AS location_code FROM ".$mDbName.".location WHERE location_code='".$mFromLocationCode."' OR location_hash = '".$mFromLocationCode."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mFromLocationId = ($mRow['id']);
				$mFromLocationCode = ($mRow['location_code']);
			}
			if ($mFromLocationId == "")
				exit("FAIL-Invalid From-Location - ".$mFromLocationCode);
		}
		$mToLocationId = "";
		if (($mToLocationCode != "") && ($mToLocationCode != null))
		{
			$mDataQuery = "SELECT id AS id, location_code AS location_code FROM ".$mDbName.".location WHERE location_code='".$mToLocationCode."' OR location_hash = '".$mToLocationCode."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mToLocationId = ($mRow['id']);
				$mToLocationCode = ($mRow['location_code']);
			}
		
			if ($mToLocationId == "")
				exit("FAIL-Invalid To-Location - ".$mToLocationCode);
		}
		
		if (($mSerialReference != "") && ($mSerialReference != null))
		{
			if (($mPartNumber == "") || ($mPartNumber == null))
			{
				$mDataQuery = "SELECT part_id, conversion_factor, current_location_id, location_code FROM ".$mDbName.".inventory_master LEFT JOIN ".$mDbName.".location ON inventory_master.current_location_id=location.id WHERE inventory_qty > 0 AND serial_reference='".$mSerialReference."' LIMIT 1";
			}
			else
			{
				$mDataQuery = "SELECT part.id AS part_id, conversion_factor, fixed_location_id AS current_location_id, location_code FROM ".$mDbName.".part LEFT JOIN ".$mDbName.".location ON part.fixed_location_id=location.id WHERE part_number ='".$mPartNumber."' LIMIT 1";
			}
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mPartId = "";
			$mPartConvFactor = "1";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mPartId = ($mRow['part_id']);
				$mPartConvFactor = ($mRow['conversion_factor']);
				if ($mFromLocationId == "")
				{
					$mFromLocationId = ($mRow['current_location_id']);
					$mFromLocationCode = ($mRow['location_code']);
				}
			}
			
//			$mReturn = "FAIL-Test:".$mDataQuery.":".$mPartId;
			
			$mInvId = "";
			$mInvQty = "";
// Serial Processing
			$mDataQuery = "SELECT inventory_master.id AS inv_id, current_location_id, inventory_qty, allocated_qty, ran_or_order, location_code FROM ".$mDbName.".inventory_master LEFT JOIN ".$mDbName.".location ON inventory_master.current_location_id=location.id WHERE part_id=".$mPartId." AND serial_reference='".$mSerialReference."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mInvId = ($mRow['inv_id']);
				$mInvQty = ($mRow['inventory_qty']);
				$mInvAllcQty = ($mRow['allocated_qty']);
				$mFromLocationId = ($mRow['current_location_id']);
				$mFromLocationCode = ($mRow['location_code']);
				if (($mRanOrOrder == null) || ($mRanOrOrder == ""))
					$mRanOrOrder = ($mRow['ran_or_order']);
			}
// Inventory Found			
			if($mInvId != "")
			{
				$mDataQuery = "UPDATE ".$mDbName.".inventory_master SET version=version+1, last_updated=now(), last_updated_by='".$mCurrentUser."'";
//				if ($mQty != "")
//					$mDataQuery = $mDataQuery.", inventory_qty=inventory_qty+".$mQty;
				if ($mParentTagId != "")
					$mDataQuery = $mDataQuery.", parent_tag_id=".$mParentTagId;
				if ($mToLocationId != "")
					$mDataQuery = $mDataQuery.", current_location_id=".$mToLocationId;
				if (($mQty == null) || ($mQty == ""))
					$mQty = "0";
				if ($mInvAllcQty >= $mQty)
					$mDataQuery = $mDataQuery.", allocated_qty=allocated_qty-".$mQty;
				else
					$mDataQuery = $mDataQuery.", allocated_qty=0";
				$mDataQuery = $mDataQuery." WHERE id=".$mInvId;
				mysqli_query($mConnection,$mDataQuery);
//				exit("FAIL-Test:".$mDataQuery.":".$mInvId);
			}
			else
			{
				if ($mToLocationId != "")
					$mLocId = $mToLocationId;
				else
					$mLocId = $mFromLocationId;
				
				$mDataQuery = "INSERT INTO ".$mDbName.".inventory_master (id,version,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) VALUES(DEFAULT,0,0,null,null,".$mLocId.",now(),null,".$mQty.",now(),'".$mCurrentUser."',null,".$mPartId.",'".$mRanOrOrder."',0,0,0,'".$mSerialReference."',null,".$mPartConvFactor.",now(),1)";
				mysqli_query($mConnection,$mDataQuery);
				$mInvId=mysqli_insert_id($mConnection);
			}						
			if (($mInvId != null) && ($mInvId != ""))
			{
// Reduce Inventory Allocated Qty
				if (($mAllocationDetailId != null) && ($mAllocationDetailId != ""))
				{
//					$mDataQuery = "UPDATE ".$mDbName.".inventory_master SET allocated_qty=allocated_qty-".$mQty.",last_updated_by='".$mCurrentUser."', last_updated=now() WHERE allocated_qty > 0 AND EXISTS(SELECT allocation_detail.id FROM ".$mDbName.".allocation_detail WHERE inventory_master.id=allocation_detail.inventory_master_id AND allocation_detail.id=".$mAllocationDetailId.")";
//					mysqli_query($mConnection,$mDataQuery);
					$mDataQuery = "SELECT inventory_master_id AS inventory_master_id FROM ".$mDbName.".allocation_detail WHERE id=".$mAllocationDetailId." LIMIT 1";
					$mData = mysqli_query($mConnection,$mDataQuery);
					$mAllcInvId = "";
					while($mRow = mysqli_fetch_assoc($mData)) 
					{
						$mAllcInvId = ($mRow['inventory_master_id']);
					}	
					$mDataQuery = "UPDATE ".$mDbName.".allocation_detail SET allocated_qty=allocated_qty-".$mQty.",processed=1,last_updated_by='".$mCurrentUser."',last_updated=now(),inventory_master_id=".$mInvId." WHERE id=".$mAllocationDetailId;
					mysqli_query($mConnection,$mDataQuery);	
// if Picked Serial <> Original Allocation and Picked Serial is allocated to another task - switch the Inventory Id on the outstanding Allocation
					if ($mInvId != $mAllcInvId)
					{
						$mDataQuery = "UPDATE ".$mDbName.".allocation_detail SET inventory_master_id=".$mAllcInvId.", last_updated_by='".$mCurrentUser."',last_updated=now() WHERE processed=0 AND inventory_master_id=".$mInvId;
						mysqli_query($mConnection,$mDataQuery);	
					}
					$mDataQuery = "SELECT id AS id FROM ".$mDbName.".allocation_detail WHERE inventory_master_id=".$mInvId." AND processed=0 LIMIT 1";
					$mData = mysqli_query($mConnection,$mDataQuery);
					$mAllcId = "";
					while($mRow = mysqli_fetch_assoc($mData)) 
					{
						$mAllcId = ($mRow['id']);
					}
					if ($mAllcId == "")
					{
						$mDataQuery = "UPDATE ".$mDbName.".inventory_master SET allocated_qty=allocated_qty-".$mQty.",last_updated_by='".$mCurrentUser."', last_updated=now() WHERE id=".$mAllcInvId." AND allocated_qty >=".$mQty." LIMIT 1";
						mysqli_query($mConnection,$mDataQuery);
					}
				}
			}
			else
			{
				mysqli_close($mConnection);
				exit("FAIL-Invalid Inventory Id - ".$mSerialReference);
			}
			
// Transaction History
			if ($mQty != "")
				$mInvQty = $mQty;
//			if (($mParentTag != "") && ($mParentTag != null))
			if ($mParentTagId != "")
				$mDataQuery = "INSERT INTO ".$mDbName.".transaction_history (id,associated_document_reference,date_created,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,to_location_code,transaction_reference_code,txn_qty,comment,active,conversion_factor) VALUES(DEFAULT,'".$mDocumentReferenceCode."',now(),'".$mFromLocationCode."',now(),'".$mCurrentUser."',".$mParentTagId.",'".$mPartNumber."','".$mRanOrOrder."','".$mSerialReference."','".$mTranShortCode."','".$mToLocationCode."','".$mTranRefCode."',".$mInvQty.",'',1,0)";
			else
				$mDataQuery = "INSERT INTO ".$mDbName.".transaction_history (id,associated_document_reference,date_created,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,to_location_code,transaction_reference_code,txn_qty,comment,active,conversion_factor) VALUES(DEFAULT,'".$mDocumentReferenceCode."',now(),'".$mFromLocationCode."',now(),'".$mCurrentUser."','".$mDocumentReferenceCode."','".$mPartNumber."','".$mRanOrOrder."','".$mSerialReference."','".$mTranShortCode."','".$mToLocationCode."','".$mTranRefCode."',".$mInvQty.",'',1,0)";
			mysqli_query($mConnection,$mDataQuery);
		}
// From-Tag Processing
		elseif ($mFromTagId != "")
		{
			$mDataQuery = "SELECT a.id AS inv_id, a.current_location_id AS current_location_id, a.inventory_qty AS inventory_qty, a.serial_reference AS serial_reference, b.part_number AS part_number, c.location_code AS location_code FROM ".$mDbName.".inventory_master a JOIN ".$mDbName.".part b ON a.part_id=b.id LEFT JOIN ".$mDbName.".location c ON a.current_location_id=c.id WHERE a.parent_tag_id=".$mFromTagId;
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mInvId = ($mRow['inv_id']);
				$mFromLocationId = ($mRow['current_location_id']);
				$mFromLocationCode = ($mRow['location_code']);
				$mInvQty = ($mRow['inventory_qty']);
				$mSerialReference = ($mRow['serial_reference']);
				$mPartNumber = ($mRow['part_number']);
					
				$mDataQueryUpd = "UPDATE ".$mDbName.".inventory_master SET version=version+1, last_updated=now(), last_updated_by='".$mCurrentUser."'";
				if ($mToLocationId != "")
					$mDataQueryUpd = $mDataQueryUpd.", current_location_id=".$mToLocationId;
				$mDataQueryUpd .= " WHERE id=".$mInvId;
				mysqli_query($mConnection,$mDataQueryUpd);
			
// Transaction History
				if ($mQty != "")
					$mInvQty = $mQty;
			
				$mDataQuery = "INSERT INTO ".$mDbName.".transaction_history (id,associated_document_reference,date_created,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,to_location_code,transaction_reference_code,txn_qty,comment,active,conversion_factor) VALUES(DEFAULT,'".$mDocumentReferenceCode."',now(),'".$mFromLocationCode."',now(),'".$mCurrentUser."','".$mFromTagCode."','".$mPartNumber."','".$mRanOrOrder."','".$mSerialReference."','".$mTranShortCode."','".$mToLocationCode."','".$mTranRefCode."',".$mInvQty.",'',1,0)";
				mysqli_query($mConnection,$mDataQuery);
			}
		}
// Transfer All Inventory Based on Document_Ref / Parent_Tag
		elseif (($mDocumentReferenceCode != null) && ($mDocumentReferenceCode != "") && ($mToLocationCode != null) && ($mToLocationCode != "") && ($mParentTagId != ""))
		{
// Check Location
//			$mToLocationId = "";
//			$mDataQuery = "SELECT id AS id, location_code AS location_code FROM ".$mDbName.".location WHERE WHERE location_code='".$mToLocationCode."' OR location_hash = '".$mToLocationCode."' LIMIT 1";
//			$mData = mysqli_query($mConnection,$mDataQuery);
//			while($mRow = mysqli_fetch_assoc($mData)) 
//			{
//				$mToLocationId = ($mRow['id']);
//				$mToLocationCode = ($mRow['location_code']);
//			}
//			if ($mToLocationId == "")
//				exit("FAIL-Invalid To-Location - ".$mToLocationCode);
// Find all Inventory with matching Parent Tag
			$mInvId = "";
			$mDataQuery = "SELECT a.id AS inv_id, a.current_location_id AS current_location_id, a.inventory_qty AS inventory_qty, a.serial_reference AS serial_reference, b.part_number AS part_number, c.location_code AS location_code FROM ".$mDbName.".inventory_master a JOIN ".$mDbName.".part b ON a.part_id=b.id JOIN ".$mDbName.".location c ON a.current_location_id=c.id WHERE a.parent_tag_id=".$mParentTagId;
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mInvId = ($mRow['inv_id']);
				$mFromLocationId = ($mRow['current_location_id']);
				$mFromLocationCode = ($mRow['location_code']);
				$mInvQty = ($mRow['inventory_qty']);
				$mSerialReference = ($mRow['serial_reference']);
				$mPartNumber = ($mRow['part_number']);
// Update Inventory To-Location					
				$mDataQueryUpd = "UPDATE ".$mDbName.".inventory_master SET version=version+1, last_updated=now(), last_updated_by='".$mCurrentUser."'";
				if ($mToLocationId != "")
					$mDataQueryUpd = $mDataQueryUpd.", current_location_id=".$mToLocationId;
				$mDataQueryUpd .= " WHERE id=".$mInvId;
				mysqli_query($mConnection,$mDataQueryUpd);			
// Transaction History
				if ($mQty != "")
					$mInvQty = $mQty;			
				$mDataQuery = "INSERT INTO ".$mDbName.".transaction_history (id,associated_document_reference,date_created,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,to_location_code,transaction_reference_code,txn_qty,comment,active,conversion_factor) VALUES(DEFAULT,'".$mDocumentReferenceCode."',now(),'".$mFromLocationCode."',now(),'".$mCurrentUser."',".$mParentTagId.",'".$mPartNumber."','".$mRanOrOrder."','".$mSerialReference."','".$mTranShortCode."','".$mToLocationCode."','".$mTranRefCode."',".$mInvQty.",'',1,0)";
				mysqli_query($mConnection,$mDataQuery);
			}
		}
// Add / Update Document
		addUpdateDocument($mConnection,$mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,"PCK",$mDocumentReferenceCode,$mCustomerReferenceCode,$mTranDocumentType,$mPartId,$mPartNumber,$mSerialReference,$mQty,$mRanOrOrder,$mAutoCreateDoc);
// Auto-Complete Document_Header
		if (($mDocumentReferenceCode != "") && ($mDocumentReferenceCode != null))
		{
			$mDataQuery = "UPDATE ".$mDbName.".document_header SET version=version+1,active=1,auto_confirm=1,auto_created=1, last_updated=now(),last_updated_by='".$mCurrentUser."'";
			if ($mTranShortCode="RSTC")
				$mDataQuery = $mDataQuery.",expected_delivery=now(),document_disposition_id=1";
			if ($mTranShortCode="PTM")
				$mDataQuery = $mDataQuery.",complete=1,marshalled=1";
			if ($mTranShortCode="MTT")
				$mDataQuery = $mDataQuery.",complete=1,marshalled=1,despatched=1";
			$mDataQuery = $mDataQuery." WHERE document_reference_code='".$mDocumentReferenceCode."'";
			mysqli_query($mConnection,$mDataQuery);
		}
		if ($mReturn == "")
		{
			$mReturn = "OK  -";
		}	
	}
	if (($mFunction != null) && ($mFunction == "Issue"))
	{
		$mReturn = "";
		$mAllowSerialReuse = "1";
		if (($mSerialReference != null) && ($mSerialReference != "") && ($mPartNumber != null) && ($mPartNumber != ""))
		{
		}
		elseif (($mPartNumber != null) && ($mPartNumber != ""))
		{
		}
// Get Location Ids.
//		$mFromLocationId = "";
		elseif (($mFromLocationCode != "") && ($mFromLocationCode != null))
// Issue All Stock From-Location 
		{
			$mFromLocationId = "";
			$mDataQuery = "SELECT id AS id, location_code AS location_code FROM ".$mDbName.".location WHERE location_code='".$mFromLocationCode."' OR location_hash = '".$mFromLocationCode."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mFromLocationId = ($mRow['id']);
				$mFromLocationCode = ($mRow['location_code']);
			}
			if ($mFromLocationId == "")
			{
				mysqli_close($mConnection);
				exit("FAIL-Invalid From-Location - ".$mFromLocationCode);
			}
		}
// Delete Inventory
		if ($mReturn == "")
		{
			$mDataQuery = "SELECT a.id AS id,b.part_number AS part_number, a.inventory_qty AS inventory_qty, a.serial_reference AS serial_reference, c.tag_reference AS tag_reference FROM ".$mDbName.".inventory_master a JOIN ".$mDbName.".part b ON a.part_id=b.id LEFT JOIN ".$mDbName.".tag c ON a.parent_tag_id=c.id WHERE a.current_location_id=".$mFromLocationId; 
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mInvId = ($mRow['id']);
				$mPartNumber = ($mRow['part_number']);
				$mSerialReference = ($mRow['serial_reference']);
				$mInvQty = ($mRow['inventory_qty']);
				$mTagReference = ($mRow['tag_reference']);
				
// Create Manifest Document Header
				if ((!$mDocumentReferenceCode) || ($mDocumentReferenceCode == null) || ($mDocumentReferenceCode == ""))
				{
					$mDocumentReferenceCode = addUpdateDocument($mConnection,$mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,"MAN",$mDocumentReferenceCode,$mCustomerReferenceCode,$mTranDocumentType,$mPartId,$mPartNumber,$mSerialReference,$mQty,$mRanOrOrder,$mAutoCreateDoc);
				}
				$mTagReference = $mDocumentReferenceCode;
				$mDataQueryUpd = "INSERT INTO ".$mDbName.".transaction_history (id,associated_document_reference,date_created,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,to_location_code,transaction_reference_code,txn_qty,comment,active,conversion_factor) VALUES(DEFAULT,'".$mTagReference."',now(),'".$mFromLocationCode."',now(),'".$mCurrentUser."','','".$mPartNumber."','".$mRanOrOrder."','".$mSerialReference."','".$mTranShortCode."','','".$mTranRefCode."',".$mInvQty.",'',1,0)";
				mysqli_query($mConnection,$mDataQueryUpd);
				$mDataQueryDlt = "DELETE FROM ".$mDbName.".inventory_master WHERE id = ".$mInvId;
				mysqli_query($mConnection,$mDataQueryDlt);
			}
			if ($mInvId == "")
			{
				mysqli_close($mConnection);
				$mReturn = "FAIL-Invalid Inventory From-Location - ".$mFromLocationCode;
				exit("FAIL-Invalid Inventory From-Location - ".$mFromLocationCode);
			}
			if ($mReturn == "")
			{
				try
				{
					$mDataQuery = "SELECT id AS id,document_reference_code AS document_reference_code, work_station AS workstation, stillage AS stillage FROM ".$mDbName.".document_header WHERE despatched=0 AND trailer_uid LIKE('".$mFromLocationCode."%')";
					$mData = mysqli_query($mConnection,$mDataQuery);
					$mId = "";
					while($mRow = mysqli_fetch_assoc($mData)) 
					{
						$mId = ($mRow['id']);
						$mDocumentReferenceCode = ($mRow['document_reference_code']);
						$mStillage = ($mRow['stillage']);
						$mDataQueryUpd = "INSERT INTO ".$mDbName.".transaction_history (id,associated_document_reference,date_created,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,to_location_code,transaction_reference_code,txn_qty,comment,active,conversion_factor) VALUES(DEFAULT,'".$mDocumentReferenceCode."',now(),'".$mFromLocationCode."',now(),'".$mCurrentUser."','','','','".$mStillage."','".$mTranShortCode."','','".$mTranRefCode."',0,'',1,0)";
						mysqli_query($mConnection,$mDataQueryUpd);
						$mDataQueryUpd = "Update ".$mDbName.".document_header SET marshalled=1, despatched=1 WHERE id=".$mId;
						mysqli_query($mConnection,$mDataQueryUpd);
					}
				}
				catch(Exception $e)
				{
				}
				$mReturn = "OKMS-Manifest ".$mDocumentReferenceCode." Created for ".$mFromLocationCode;
			}			
		}
	}	
	mysqli_close($mConnection);
	echo($mReturn);
	return;
}
// Update Document Header, Body, Detail
function addUpdateDocument($mConnection,$mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mDocumentReferencePrefix,$mDocumentReferenceCode=null,$mCustomerReferenceCode=null,$mTranDocumentType=null,$mPartId=null,$mPartNumber=null,$mSerialReference=null,$mQty=null,$mRanOrOrder=null,$mAutoCreateDoc=null)
{	
	If ($mAutoCreateDoc != "0")
	{
// Document_Header
		if (($mDocumentReferenceCode != null) && ($mDocumentReferenceCode != ""))
		{
			$mDataQuery = "SELECT id,document_reference_code,requirement_header_id FROM ".$mDbName.".document_header WHERE document_reference_code='".$mDocumentReferenceCode."' LIMIT 1";
		}
		else
		{
			$mDataQuery = "SELECT id,document_reference_code,requirement_header_id FROM ".$mDbName.".document_header WHERE document_type='".$mTranDocumentType."' AND customer_reference_code='".$mCustomerReferenceCode."' AND complete <> 1 LIMIT 1";
		}
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mDocumentReferenceCode = "";
		$mDocumentHdrId = "";
		$mRequirementHdrId = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDocumentHdrId = ($mRow['id']);
			$mDocumentReferenceCode = ($mRow['document_reference_code']);
			$mRequirementHdrId = ($mRow['requirement_header_id']);
		}
		if ($mDocumentHdrId == "")
		{
			$mDataQuery = "UPDATE ".$mDbName.".document_register SET version=version+1,last_updated=now(),last_updated_by='".$mCurrentUser."',register_value=register_value+1 WHERE document_type='".$mTranDocumentType."' LIMIT 1";
			mysqli_query($mConnection,$mDataQuery);
			$mDataQuery = "SELECT register_value FROM ".$mDbName.".document_register WHERE document_type='".$mTranDocumentType."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mDocumentReferenceCode = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{			
		
				$mDocumentReferenceCode = $mDocumentReferencePrefix.($mRow['register_value']);
			}
			if ($mDocumentReferenceCode == "")
				$mDocumentReferenceCode = $mDocumentReferencePrefix."999999";
//			$mDataQuery = "INSERT INTO ".$mDbName.".document_header (id, version, active, auto_confirm, auto_created, complete, date_created, document_reference_code, document_type, last_updated, last_updated_by, vendor_id, class, expected_delivery, document_disposition_id, inspection_required, qa_type, time_slot, stillage, work_station, despatched, marshalled, picked, parent_id, customer_order_id, bwlvs, lgnum, pldat, plzei, tanum, zpind, customer_reference_code, requirement_header_id, scheduled_work_id, stock_shortage, allocated, transmitted) VALUES(DEFAULT,0,0,0,0,0,now(),'".$mDocumentReferenceCode."','".$mTranDocumentType."',now(),'".$mCurrentUser."',3,'com.vantec.documents.receipts.CustomerReceiptHeader',null,null,0,null,null,null,null,0,0,0,null,null,null,null,null,null,null,null,'".$mCustomerReferenceCode."',null,null,0,null,null)";
			$mDataQuery = "INSERT INTO ".$mDbName.".document_header (id, version, active, auto_confirm, auto_created, complete, date_created, document_reference_code, document_type, last_updated, last_updated_by, vendor_id, class, expected_delivery, document_disposition_id, inspection_required, qa_type, time_slot, stillage, work_station, despatched, marshalled, picked, parent_id, customer_order_id, bwlvs, lgnum, pldat, plzei, tanum, zpind, customer_reference_code, requirement_header_id, scheduled_work_id, stock_shortage, allocated, transmitted) VALUES(DEFAULT,0,1,0,1,0,now(),'".$mDocumentReferenceCode."','".$mTranDocumentType."',now(),'".$mCurrentUser."',null,'com.vantec.documents.shipments.ShipmentHeader',null,null,0,null,null,null,null,1,1,0,null,null,null,null,null,null,null,null,'".$mCustomerReferenceCode."',null,null,0,null,null)";
			mysqli_query($mConnection,$mDataQuery);
			$mDataQuery = "SELECT id FROM ".$mDbName.".document_header WHERE document_reference_code='".$mDocumentReferenceCode."' AND complete <> 1 LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mDocumentHdrId = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mDocumentHdrId = ($mRow['id']);
			}
		}
		else
		{
			$mDataQuery = "UPDATE ".$mDbName.".document_header SET last_updated=now(), last_updated_by='".$mCurrentUser."' WHERE id=".$mDocumentHdrId;
			mysqli_query($mConnection,$mDataQuery);
		}
	}
	else
	{
		$mDataQuery = "SELECT id,requirement_header_id FROM ".$mDbName.".document_header WHERE document_reference_code='".$mDocumentReferenceCode."' AND complete <> 1 LIMIT 1";
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mDocumentHdrId = "";
		$mRequirementHdrId = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDocumentHdrId = ($mRow['id']);
			$mRequirementHdrId = ($mRow['requirement_header_id']);
		}
	}
	if ($mDocumentHdrId != "")
	{
// Document_body
		$mDataQuery = "SELECT id AS body_id FROM document_body WHERE document_header_id=".$mDocumentHdrId." AND part_id=".$mPartId." LIMIT 1";
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mDocumentBodyId = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDocumentBodyId = ($mRow['body_id']);
		}
		if ($mDocumentBodyId == "")
		{
			$mDataQuery = "INSERT INTO document_body (id,version,date_created,document_header_id,external_reference,last_updated,last_updated_by,part_id,qty_expected,qty_transacted,class,alpha_qty_expected,our_line_no,kbnkz, kqvnq, matnr, nsolm, tapos, vsolm, wdatu, wepos, active, qty_allocated, requirement_body_id) VALUES(DEFAULT,0,now(),".$mDocumentHdrId.",'',now(),'".$mCurrentUser."',".$mPartId.",0,".$mQty.",'com.vantec.documents.receipts.CustomerReceiptBody','0',0,'','','','','','','','',1,0,null)";
			try{
				mysqli_query($mConnection,$mDataQuery);
			} catch(Exception $e) {
				exit("FAIL-Document_Body:".$e->getMessage());
			}
			$mDataQuery = "SELECT id AS body_id FROM document_body WHERE document_header_id=".$mDocumentHdrId." AND part_id =".$mPartId." LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mDocumentBodyId = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mDocumentBodyId = ($mRow['body_id']);
			}
		}
		else
		{
			$mDataQuery = "UPDATE document_body SET last_updated=now(),last_updated_by='".$mCurrentUser."',qty_transacted=qty_transacted+".$mQty." WHERE id=".$mDocumentBodyId." LIMIT 1";
			mysqli_query($mConnection,$mDataQuery);
// Clear any outstanding orphan allocations
			clearAllocation($mConnection, $mDocId, $mCustomerReferenceCode, $mCurrentUser);
		}
// Document_detail
		if ($mDocumentBodyId != "")
		{
			$mDocumentDtlId = "";
			$mDataQuery = "SELECT id AS id FROM document_detail WHERE document_body_id=".$mDocumentBodyId." AND serial_reference='".$mSerialReference."' LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mDocumentDtlId = ($mRow['id']);
			}
			if ($mDocumentDtlId == "")
			{					
				$mDataQuery = "INSERT INTO document_detail (id, version, date_created, document_body_id, last_updated, last_updated_by, qty_expected, qty_transacted, serial_reference, class, chassis, sequence, trim_sequence, validated, active, kbnkz, kqvnq, matnr, nsolm, our_detail_no, tapos, vsolm, wdatu, wepos, qty_allocated, requirement_detail_id) VALUES(DEFAULT,0,now(),".$mDocumentBodyId.",now(),'".$mDocumentReferenceCode."',0,".$mQty.",'".$mSerialReference."','com.vantec.documents.receipts.CustomerReceiptDetail','',null,'',0,1,'','','".$mPartNumber."','".$mRanOrOrder."',0,'','','','',0,null)";
				try
				{
					mysqli_query($mConnection,$mDataQuery);
					$mDocumentDtlId=mysqli_insert_id($mConnection);
				}
				catch(Exception $e)
				{
				}
			}
			else
			{
				$mDataQuery = "UPDATE document_detail SET qty_transacted=qty_transacted+".$mQty.", last_updated=now(), last_updated_by='".$mDocumentReferenceCode."' WHERE id=".$mDocumentDtlId." LIMIT 1";
				mysqli_query($mConnection,$mDataQuery);
			}
//			exit("FAIL-Last Insert:".$mDocumentHdrId);
		}
	}
// Update Related Customer Order Document
	if ($mRequirementHdrId != "")
	{
		$mDataQuery = "SELECT document_body.id AS body_id FROM document_body WHERE document_body.document_header_id=".$mRequirementHdrId." AND part_id=".$mPartId." LIMIT 1";
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mDocumentBodyId = "";		
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDocumentBodyId = ($mRow['body_id']);
		}
//		exit("FAIL-document_body :".$mdataQuery.":".$mDocumentBodyId);
		if ($mDocumentBodyId == "")
		{
			$mDataQuery = "INSERT INTO document_body (id,version,date_created,document_header_id,external_reference,last_updated,last_updated_by,part_id,qty_expected,qty_transacted,class,alpha_qty_expected,our_line_no,kbnkz, kqvnq, matnr, nsolm, tapos, vsolm, wdatu, wepos, active, qty_allocated, requirement_body_id) VALUES(DEFAULT,0,now(),".$mRequirementHdrId.",'',now(),'".$mCurrentUser."',".$mPartId.",0,".$mQty.",'com.vantec.documents.orders.RROrderBody','0',0,'','','','','','','','',1,0,null)";
			try{
				mysqli_query($mConnection,$mDataQuery);
			} catch(Exception $e) {
				exit("FAIL-Document_Body:".$e->getMessage());
			}
		}
		else
		{
			$mDataQuery = "UPDATE document_body SET last_updated=now(),last_updated_by='".$mCurrentUser."',qty_transacted=qty_transacted+".$mQty." WHERE id=".$mDocumentBodyId;
			mysqli_query($mConnection,$mDataQuery);
		}

// Test for outstanding Order Lines
		$mDataQuery = "SELECT id FROM document_body WHERE document_header_id=".$mRequirementHdrId." AND qty_expected>qty_transacted";
		$mData = mysqli_query($mConnection,$mDataQuery);
		$i = 0;
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$i=$i+1;
		}
		$mDataQuery = "UPDATE document_header SET last_updated=now(),last_updated_by='".$mCurrentUser."'";
		if ($i == 0)
			$mDataQuery = $mDataQuery.",picked=1";
		$mDataQuery = $mDataQuery." WHERE id = ".$mRequirementHdrId;
		mysqli_query($mConnection,$mDataQuery);
		if ($i == 0)
		{
			$mDataQuery = "UPDATE document_header SET last_updated=now(),last_updated_by='".$mCurrentUser."',picked=1 WHERE id=".$mDocumentHdrId;
			mysqli_query($mConnection,$mDataQuery);
		}		
	}
	return $mDocumentReferenceCode;
}

function allocateScheduledWork($mConnection,$mHost,$mUser,$mPwd,$mDbName,$mDocumentReferenceCode=null, $mDocId, $mInvId, $mCurrentUser)
{
	$mReturn = "";
	$mPartId = "";
	
// Get Scheduled_Work
	$mDataQuery = "SELECT scheduled_work.id AS sequencingRequestId, scheduled_work.full_box_pick AS full_box_pick, allocation_detail.id AS allocationId, allocation_detail.inventory_master_id AS invId, allocation_detail.allocated_qty AS allocated_qty, allocation_detail.part_id AS part_id, inventory_master.allocated_qty AS qty, inventory_master.inventory_qty AS inventory_qty FROM scheduled_work JOIN allocation_detail ON scheduled_work.id=allocation_detail.scheduled_work_id JOIN inventory_master ON allocation_detail.inventory_master_id=inventory_master.id JOIN location ON inventory_master.current_location_id=location.id JOIN location_type ON location.location_type_id=location_type.id JOIN inventory_status ON location_type.default_inventory_status_id=inventory_status.id WHERE scheduled_work.associated_header_id=".$mDocId." AND allocation_detail.processed=0 AND allocation_detail.allocated_qty > 0 AND inventory_master.inventory_qty >0 AND inventory_status.non_pickable=0 ORDER BY scheduled_work.associated_header_id, location.location_pick_seq, location.location_code LIMIT 1";	
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mSequencingRequestId = "";
	$mAllocationId = "";
	$mAlcInvId = "";
	$mAlcQty = 0;
	$mAllocatedQty=0;
	$mInvQty=0;
	$mFullBoxPick="";

	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mSequencingRequestId = ($mRow['sequencingRequestId']);
		$mAllocationId = ($mRow['allocationId']);
		$mAlcInvId = ($mRow['invId']);
		$mAlcQty = ($mRow['qty']);
		$mInventoryQty = ($mRow['inventory_qty']);
		$mWrkAllocatedQty = ($mRow['allocated_qty']);
		$mPartId = ($mRow['part_id']);
		$mFullBoxPick = ($mRow['full_box_pick']);
	}
	if ($mFullBoxPick == "1")
	{
		$mAlcQty = $mInventoryQty;
	}
//	exit("FAIL-TEST1".$mPartId.":".$mAlcQty);
// No Valid Allocation found
	if ($mPartId == "")
	{
		$mDataQuery = "SELECT scheduled_work.id AS sequencingRequestId, scheduled_work.full_box_pick AS full_box_pick, allocation_detail.id AS allocationId, allocation_detail.allocated_qty AS allocated_qty, allocation_detail.part_id AS part_id FROM scheduled_work JOIN allocation_detail ON scheduled_work.id=allocation_detail.scheduled_work_id JOIN part ON allocation_detail.part_id=part.id LEFT JOIN inventory_master ON allocation_detail.inventory_master_id=inventory_master.id JOIN location ON inventory_master.current_location_id=location.id WHERE scheduled_work.associated_header_id=".$mDocId." AND allocation_detail.processed=0 ORDER BY location.location_pick_seq, location.location_code, allocation_detail.inventory_master_id LIMIT 1";
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mSequencingRequestId = "";
		$mAllocationId = "";
		$mAlcInvId = "";
		$mWrkAllocatedQty=0;
		$mFullBoxPick="";

		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mSequencingRequestId = ($mRow['sequencingRequestId']);
			$mAllocationId = ($mRow['allocationId']);
			$mWrkAllocatedQty = ($mRow['allocated_qty']);
			$mPartId = ($mRow['part_id']);
			$mFullBoxPick = ($mRow['full_box_pick']);
		}
// Test if Allocation is OK
		if ($mPartId != "")
		{
// Try to find alternative Inventory to Allocate
			$mDataQuery = "SELECT inventory_master.id AS invId, inventory_master.inventory_qty AS qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN location_type ON location.location_type_id=location_type.id JOIN inventory_status ON location_type.default_inventory_status_id=inventory_status.id LEFT JOIN part ON inventory_master.part_id=part.id WHERE inventory_master.part_id=".$mPartId." AND inventory_master.inventory_qty >0 AND inventory_status.non_pickable=0 ORDER BY location.location_pick_seq, location.location_code, inventory_master.stock_date LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mAlcInvId = "";
			$mAlcQty = 0;
			$mInvAllocatedQty=0;
			$mPartNumber = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mAlcInvId = ($mRow['invId']);
				$mAlcQty = ($mRow['qty']);
				$mInvAllocatedQty = ($mRow['allocated_qty']);
				$mPartNumber = ($mRow['part_number']);
			}
			if ($mPartNumber == "")
			{
				$mDataQuery = "SELECT part.part_number AS part_number FROM part WHERE part.id=".$mPartId." LIMIT 1";
				$mData = mysqli_query($mConnection,$mDataQuery);
				$mPartNumber = "";
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mPartNumber = ($mRow['part_number']);

				}
			}
			if ($mPartNumber == "")
				$mPartNumber = "Unknown";
// Test for no stock
			if ($mAlcInvId == "")
			{
				$mReturn = "FAIL- No Stock for Part ".$mPartNumber;
			}
			if ($mReturn == "")
			{
				if ($mInvAllocatedQty > 0)
				{
					$mAllocatedQty=0;
// Un-Allocate Inventory if Required
					$mId = "";
					$mDataQuery = "SELECT alocation_detail.id AS id, allocation_detail.allocated_qty AS allocated_qty FROM allocation_detail WHERE allocation_detail.inventory_master_id=".$mInvId." AND allocation_detail.processed=0";
					$mData = mysqli_query($mConnection,$mDataQuery);				
					while($mRow = mysqli_fetch_assoc($mData)) 
					{
						$mId = ($mRow['id']);
						$mAllocatedQty = $mAllocatedQty+($mRow['allocated_qty']);
					}
					if ($mInvAllocatedQty > $mAllocatedQty)
						$mInvAllocatedQty = $mInvAllocatedQty-$mAllocatedQty;
					else
						$mInvAllocatedQty = 0;
					if ($mId == "")
						$mInvAllocatedQty = 0;
						$mDataQuery = "UPDATE inventory_master SET allocated_qty = ".$mInvAllocatedQty." ,last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE inventory_master.id=".$mInvId;
						mysqli_query($mConnection,$mDataQuery);
				}
// Allocate Inventory
				if ($mWrkAllocatedQty > $mQty)
					$mWrkAllocatedQty = $mQty;
				$mDataQuery = "UPDATE inventory_master SET allocated_qty = allocated_qty+".$mWrkAllocatedQty." ,last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mAlcInvId." LIMIT 1";
				mysqli_query($mConnection,$mDataQuery);
// Refresh Allocation_detail
				$mDataQuery = "UPDATE allocation_detail SET inventory_master_id=".$mAlcInvId.", allocated_qty=".$mWrkAllocatedQty." ,last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id =".$mAllocationId." LIMIT 1";
				mysqli_query($mConnection,$mDataQuery);
			}
		}
	}
	if ($mReturn == "")
	{
		if ($mSequencingRequestId == "")
		{
			$mReturn = "DATA:EOD";
		}
	}
	if ($mReturn == "")
	{
// Get Inventory Information
		if ($mAlcInvId != "")
		{
			$mDataQuery = "SELECT inventory_master.inventory_qty AS inventory_qty, part.part_number AS part_number, location.location_code AS location_code FROM inventory_master JOIN part ON inventory_master.part_id=part.id LEFT JOIN location ON inventory_master.current_location_id=location.id WHERE inventory_master.id=".$mAlcInvId." LIMIT 1";
			$mData = mysqli_query($mConnection,$mDataQuery);
			$mInvQty = 0;
			$mPartNumber = "";
			$mLocationCode = "";
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mInvQty = ($mRow['inventory_qty']);
				$mPartNumber = ($mRow['part_number']);
				$mLocationCode = ($mRow['location_code']);
			}
			if ($mPartNumber == "")
				$mPartNumber = "Unknown";
			if (($mWrkAllocatedQty > $mInvQty) || ($mWrkAllocatedQty == 0) || ($mFullBoxPick == "1"))
				$mWrkAllocatedQty = $mInvQty;
			$mReturn = "DATA:sequencingRequestId:".$mSequencingRequestId.",allocationId:".$mAllocationId.",fromLocationCode:".$mLocationCode.",partNumber:".$mPartNumber.",qty:".$mWrkAllocatedQty.",invId:".$mAlcInvId;
			exit($mReturn);
		}
		else
		{
			$mReturn = "FAIL-Invalid Inventory";
		}
	}
	return $mReturn;
}

// Allocate Outstanding Order Lines
function allocateOrderLines($mConnection,$mDocumentReferenceCode, $mDocId, $mCustomerReferenceCode, $mCurrentUser)
{
	$mReturn = "DATA:EOD";
	$mScheduledWorkId = "";
	$mAlcInvId = "";
	$mReqQty = 0;
// Get Full Box Pick Flag based on Order Type
	$mDataQuery = "SELECT order_type_cross_reference.id AS id, order_type_cross_reference.full_box_pick AS full_box_pick FROM document_header JOIN order_type_cross_reference ON document_header.order_type=order_type_cross_reference.rr_order_type WHERE document_header.customer_reference_code='".$mCustomerReferenceCode."' AND document_header.complete=0  AND document_header.document_type like('%Order%') LIMIT 1";
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mFullBoxPick = "";
	$i = 0;
	while($mRow = mysqli_fetch_assoc($mData))  
	{
		$mFullBoxPick = ($mRow['full_box_pick']);
		$i = $i + 1;
	}
// Get Document Body
	$mDataQuery = "SELECT document_body.id AS id, document_body.part_id AS part_id, (document_body.qty_expected-document_body.qty_transacted) AS qty, part.part_number AS part_number FROM document_body JOIN part on document_body.part_id=part.id WHERE document_body.document_header_id=".$mDocId." AND document_body.qty_expected>document_body.qty_transacted";
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mBdyId = "";
	$mBdyQty = 0;
	$mBdyPartId = "";
	$mBdyPartNumber = "";
	if (mysqli_num_rows($mData) == 0)
	{
		$mReturn = "DATA:EOD";
		return $mReturn;
	}
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mBdyId = ($mRow['id']);
		$mBdyPartId = ($mRow['part_id']);
		$mBdyQty = ($mRow['qty']);
		$mBdyPartNumber = ($mRow['part_number']);
// Set Default Return Message
		if ($mReturn == "DATA:EOD")
			$mReturn = "FAIL-No Stock ".$mBdyPartNumber;
// Get Pickable Inventory
		if ($mBdyQty > 0)
		{
			$mInvQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS 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_or_order FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN location_type ON location.location_type_id=location_type.id JOIN inventory_status ON location_type.default_inventory_status_id=inventory_status.id LEFT JOIN part ON inventory_master.part_id=part.id WHERE inventory_master.part_id=".$mBdyPartId." AND inventory_master.inventory_qty >0 AND inventory_status.non_pickable=0 ORDER BY location.location_pick_seq, location.location_code, inventory_master.stock_date";
			$mInvData = mysqli_query($mConnection,$mInvQuery);
			$mAlcInvId = "";
			$mInvQty = 0;
			$mInvAllocatedQty = 0;
			$mBdyPartNumber = "";
			$mInvSerialReference = "";
			$mInvRanOrOrder = "";
			while($mInvRow = mysqli_fetch_assoc($mInvData)) 
			{
				$mAlcInvId = ($mInvRow['id']);
				$mInvQty = ($mInvRow['qty']);
				$mInvAllocatedQty = ($mInvRow['allocated_qty']);
				$mBdyPartNumber = ($mInvRow['part_number']);
				$mInvSerialReference = ($mInvRow['serial_reference']);
				$mInvRanOrOrder = ($mInvRow['ran_or_order']);
// Check Allocation
				$mAlcQuery = "SELECT SUM(allocated_qty) AS allocated_qty FROM allocation_detail WHERE inventory_master_id=".$mAlcInvId." AND processed=0 GROUP BY inventory_master_id ORDER BY inventory_master_id";
				$mAlcData = mysqli_query($mConnection,$mAlcQuery);
				$mAlcAllocatedQty = 0;
				if (mysqli_num_rows($mAlcData) > 0)
				{
					while($mAlcRow = mysqli_fetch_assoc($mAlcData))
					{
						if ($mAlcAllocatedQty != ($mAlcRow['allocated_qty']))
							$mAlcAllocatedQty = ($mAlcRow['allocated_qty']);
					}
				}
				else
				{
					$mAlcAllocatedQty = 0;
				}
				if ($mAlcAllocatedQty > $mInvQty)
					$mAlcAllocatedQty = 0;
// Create Document Detail
				$mInvQty = $mInvQty - $mAlcAllocatedQty;
				if ($mFullBoxPick == "1")
				{
					$mReqQty = $mInvQty;
					$mBdyQty = $mBdyQty - $mInvQty;
				}
				elseif ($mBdyQty > $mInvQty)
				{
					$mReqQty = $mInvQty;
					$mBdyQty = $mBdyQty - $mInvQty;
				}
				else
				{
					$mReqQty = $mBdyQty;
					$mBdyQty = 0;
				}
				if ($mInvAllocatedQty <> $mAlcAllocatedQty)
				{
					$mAlcQuery = "UPDATE inventory_master SET allocated_qty=".$mAlcAllocatedQty.", last_updated=now(), last_updated_by='".$mCurrentUser."' WHERE id=".$mAlcInvId." AND allocated_qty <> ".$mAlcAllocatedQty." LIMIT 1";
					mysqli_query($mConnection,$mAlcQuery);
				}
				if ($mReqQty > 0)
				{
					$mAlcQuery = "INSERT INTO document_detail (id, version, date_created, document_body_id, last_updated, last_updated_by, qty_expected, qty_transacted, serial_reference, class, chassis, sequence, trim_sequence, validated, active, kbnkz, kqvnq, matnr, nsolm, our_detail_no, tapos, vsolm, wdatu, wepos, qty_allocated, requirement_detail_id) VALUES(DEFAULT,0,now(),".$mBdyId.",now(),'".$mDocumentReferenceCode."',".$mReqQty.",0,'".$mInvSerialReference."','com.vantec.documents.receipts.CustomerReceiptDetail','',null,'',0,1,'','','".$mBdyPartNumber."','".$mInvRanOrOrder."',0,'','','','',0,null)";
					mysqli_query($mConnection,$mAlcQuery);
					$mDtlId=mysqli_insert_id($mConnection);				
				
// Get / Create Scheduled_Work
					if ($mScheduledWorkId == "")
					{
						$mAlcQuery = "SELECT id AS id FROM scheduled_work WHERE third_party_reference='".$mCustomerReferenceCode."' LIMIT 1";
						$mAlcData = mysqli_query($mConnection,$mAlcQuery);
						if (mysqli_num_rows($mAlcData) > 0)
						{
							while($mAlcRow = mysqli_fetch_assoc($mAlcData))
							{
								$mScheduledWorkId = ($mAlcRow['id']);
							}
						}
						else
						{
							$mAlcQuery = "INSERT INTO scheduled_work (id, version, associated_transaction_id, complete, date_created, from_location_code, in_use, last_updated, last_updated_by, part_number, qty_required, serial_reference, to_location_code, associated_header_id, current_detail_id, third_party_reference, active, line_number, tag, full_box_pick, replenishment_order) VALUES(DEFAULT,0,now(),'57',0,now(),'',0,'".$mCurrentUser."','".$mPartNumber."',".$mQty.",'".$mSerialReference."','',".$mDocId.",".$mDtlId.",'".$mCustomerReferenceCode."',1,0,'',1,0)";
							mysqli_query($mConnection,$mAlcQuery);
							$mScheduledWorkId=mysqli_insert_id($mConnection);
						}
					}
// Create Allocation Detail
					$mAlcQuery = "INSERT INTO allocation_detail (id, allocated_qty, date_created, inventory_master_id, last_updated, last_updated_by, part_id, processed, requirement_id, line_no, scheduled_work_id, originating_request_id, active, to_location_id, replenishment) VALUES(DEFAULT,".$mReqQty.",now(),".$mAlcInvId.",now(),'".$mCurrentUser."',".$mBdyPartId.",0,null,0,".$mScheduledWorkId.",null,1,null,0)";
					mysqli_query($mConnection,$mAlcQuery);
					$mAlcQuery = "UPDATE inventory_master SET allocated_qty=allocated_qty+".$mReqQty.", last_updated=now(), last_updated_by='".$mCurrentUser."' WHERE id=".$mAlcInvId." LIMIT 1";
					mysqli_query($mConnection,$mAlcQuery);
					$mReturn = "";
				}
				if ($mBdyQty <= 0)
				{
					break;
				}
			}
		}
	}
	return $mReturn;
}
// 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']));
				}
			}
		}
	}
}
?>