<?php
// decant.php
// VIMS Decant Processing
// ----------------------------------------------------------------------------------------

// ----------------------------------------------------------------------------------------
header('Cache-Control: no-cache, no-store, must-revalidate');   
header('Expires: 0');
// Header to allow Cross-Server AJAX Connection
header('Access-Control-Allow-Origin: *');
date_default_timezone_set('Europe/London');
set_time_limit(0);
// Enable exceptions for MySQLi errors
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// error_reporting(E_ALL);
// ini_set('display_errors', 1);

// report - VIMS RDT Report Controller

include_once 'connect.php';
$conn = dbConnection();
// validaterdt - VIMS Server Validation
$mFilter = "";
$mFunction          = $_GET['function'];
$mConnect           = $_GET['connection'];
$mUser              = $_GET['user'];
$mPwd               = $_GET['pword'];
$mHost              = $_GET['host'];
$mDbName            = $_GET['dbase'];
$mTableName         = $_GET['table'];
$mReturnFields      = $_GET['returnfields'];
$mFilter            = $_GET['filter'];

if (!empty($conn['host']))
    $mHost = $conn['host'];
if (!empty($conn['user']))
    $mUser = $conn['user'];
if (!empty($conn['pwd']))
    $mPwd = $conn['pwd'];
if (!empty($conn['dbase']))
    $mDbName = $conn['dbase'];
if (!empty($conn['path']))
    $mConnect = $conn['path'];


// Get Input Parameters from POST or GET
if (!empty($_POST))
{
    $mFilter = "";
    foreach($_POST as $k1=>$v1)
    {
        if ($mFilter != "")
            $mFilter .= "|AND|";
        $mFilter .= $k1."=".$v1;
    }
}
if ($mFilter == "")
{
    $mFilter = file_get_contents("php://input");
    $mArray1 = json_decode($mFilter, true);
    $mFilter = "";
    foreach($mArray1 as $k1=>$v1)
    {
        if ($mFilter != "")
            $mFilter .= "|AND|";
        $mFilter .= $k1."=".$v1;
    }
}

$mFilter = str_replace("&","|AMPERSAND|",$mFilter);
$mFilter = str_replace("|AND|","&",$mFilter);
$mFilter = str_replace("|COLON|",":",$mFilter);
$mFilter = str_replace("/dbase/",$mDbName,$mFilter);
// Replace Chars in Input String to emulate a JSON format instead of GET format
$mFilter = str_replace("=",":",$mFilter);
$mFilter = str_replace("&",",",$mFilter);

$mSQLData = array();

$mErrMsg = "OK  -";

// Check for Session Time-Out
if (($mFunction != null) && ($mFunction == "sessiontimeout"))
{
    $mTimeOut = 900;
    $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 == "timeout")
            $mTimeOut = $v2*3600;
    }
    
    session_start();
    if(time() - $_SESSION['userData']['timestamp'] > $mTimeOut)  
    { 
// subtract new timestamp from the old one
        echo("FALO-Session Timed-out");
        unset($_SESSION['userData']);
        exit;
    } 
    else 
    {
        $_SESSION['userData']['timestamp'] = time(); //set new timestamp
    }
}

// Get Decant Id. from Input Serial
elseif (($mFunction != null) && ($mFunction=="getdecant"))
{
    $mDecantId = "";
// Set default error message
    $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;
    $mPage = "";
// 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 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "decantid")
            $mDecantId = $v2;
        elseif ($k2 == "printer")
            $mPrinter = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
    }
// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());

// Check that Setial is in a decant location
    $mDataQuery = "SELECT inventory_master.id AS id, inventory_master.ran_or_order AS ran_or_order, inventory_master.available_qty AS inventory_qty,inventory_master.product_type_id,inventory_master.requires_decant AS requires_decant";
    $mDataQuery .=", location_type.location_type_code AS location_type_code";
    $mDataQuery .=" FROM inventory_master";
    $mDataQuery .=" JOIN location ON inventory_master.current_location_id=location.id";
    $mDataQuery .=" LEFT JOIN location_type ON location.location_type_id=location_type.id";
    $mDataQuery .=" WHERE inventory_master.serial_reference='".$mSerialReference."' AND inventory_master.part_number='".$mPartNumber."' AND allocated_qty = 0 LIMIT 1";
    if (!$mData = mysqli_query($mConnection,$mDataQuery))
        exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
    if (mysqli_num_rows($mData) > 0)
    {
        $mRow = mysqli_fetch_assoc($mData);
        if (strtolower($mRow['requires_decant']) != 1)
        {
//            echo("FAIL-This Serial is not flagged for Decant");
//            return;
        }
    }
    else
    {
        $mDataQuery = "SELECT decant_body.id AS id FROM decant_body JOIN decant_header ON decant_body.decant_header_id=decant_header.id WHERE decant_body.serial_reference='".$mSerialReference."' AND decant_header.part_number='".$mPartNumber."' AND decant_status IN(0,1) LIMIT 1";
        if (!$mData = mysqli_query($mConnection,$mDataQuery))
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
    }
    if (mysqli_num_rows($mData) == 0)
    {
        exit("FAIL-This Serial is not available for Decant");
    }
// Test for existing Decant Reference
    if ($mDecantId != "")
    {
        $mDataQuery = "SELECT decant_header.id AS id ";
        $mDataQuery .=" FROM decant_header";
		$mDataQuery .=" JOIN decant_body on decant_header.id=decant_body.decant_header_id AND decant_body.decant_status <> 2";
        $mDataQuery .=" LEFT JOIN document_status ON decant_header.document_status_id=document_status.id";
        $mDataQuery .=" WHERE decant_header.id=".$mDecantId." LIMIT 1";
        if (!$mData = mysqli_query($mConnection,$mDataQuery))
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
        if (mysqli_num_rows($mData) == 0)
        {
            exit ("FAIL-This decant is not available");
        }
/*
// check if the decant is complete
        $mDataQuery = "SELECT decant_body.id AS id FROM decant_body WHERE decant_header_id =".$mDecantId." AND decant_status IN(0,1) AND EXISTS(SELECT id FROM inventory_master WHERE decant_body.inventory_master_id=inventory_master.id) LIMIT 1";
        if (!$mData = mysqli_query($mConnection,$mDataQuery))
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
        if (mysqli_num_rows($mData) == 0)
        {
            exit ("OKMS-This decant is complete,DECANT");
        }
*/
// validate Part Number
        $mDataQuery = "SELECT id, part_number FROM decant_header WHERE id=".$mDecantId." LIMIT 1";
        try
        {
            $mData = mysqli_query($mConnection,$mDataQuery);
        }
        catch (Exception $e)
        {
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
        }
        if (mysqli_num_rows($mData) > 0)
        {
            $mRow = mysqli_fetch_assoc($mData);
            if ($mPartNumber != $mRow['part_number'])
            {
                $mErrMsg = "FAIL-Wrong Part for this Decant.";
                exit($mErrMsg);
            }
        }
// Check if decant is allocated - don't allow new serials to be added.
        if ($mErrMsg == "")
        {
            
            $mDataQuery = "SELECT id FROM decant_body WHERE decant_header_id=".$mDecantId." AND serial_reference='".$mSerialReference."' LIMIT 1";
            if (!$mData = mysqli_query($mConnection,$mDataQuery))
                exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
            
            if (mysqli_num_rows($mData) == 0)
            {
                try
                {
                    $mDataQuery = "SELECT decant_header.id AS id FROM decant_header WHERE decant_header.id=".$mDecantId." AND EXISTS(SELECT id FROM document_status WHERE decant_header.document_status_id=document_status.id AND lower(document_status.document_status_code) IN('open','allocated')) LIMIT 1";
                    $mData = mysqli_query($mConnection,$mDataQuery);
                }
                catch (Exception $e)
                {
                    exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
                }
                if (mysqli_num_rows($mData) == 0)
                    exit("FAIL-Cannot Add Serials to this Decant Task.");
            }           
        }
        if ($mErrMsg == "")
        {
// Get RAN for this Serial.     
            $mDataQuery = "SELECT ran_or_order AS ran_order,product_type_id AS product_type_id,inventory_status_id,requires_inspection";
			$mDataQuery .=" FROM inventory_master";
			$mDataQuery .=" WHERE part_number='".$mPartNumber."' AND serial_reference='".$mSerialReference."'";
			$mDataQuery .=" LIMIT 1";
            try
            {
                $mData = mysqli_query($mConnection,$mDataQuery);
            }
            catch(Exception $e)
            {
                exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
            }
            if (mysqli_num_rows($mData) == 0)
            {
                $mDataQuery = "SELECT tt_ran_order AS ran_order FROM decant_body WHERE serial_reference='".$mSerialReference."' AND decant_status=1 LIMIT 1";
                try
                {
                    $mData = mysqli_query($mConnection,$mDataQuery);
                }
                catch (Exception $e)
                {
                    exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
                }
                if (mysqli_num_rows($mData) == 0)
                {
                    $mErrMsg = "FAIL-Invalid Serial";
                    exit($mErrMsg);
                }
            }
            if ($mErrMsg == "")
            {
                $mProductTypeId=0;
                $mRow = mysqli_fetch_assoc($mData);
                $mRanOrder = strtolower(($mRow['ran_order']));
                $mProductTypeId = $mRow['product_type_id'];
				$mInvStatusId = $mRow['inventory_status_id'];
				$mInvInsp = $mRow['requires_inspection'];
                if ($mRanOrder != null && $mRanOrder != "")
                {
                    if (($mRanOrder == "no-ran") || ($mRanOrder == "noran"))
                    {
                        $mDataQuery = "SELECT id FROM decant_body WHERE decant_header_id=".$mDecantId." AND lower(tt_ran_order)<>'no-ran' AND lower(tt_ran_order)<>'noran' LIMIT 1";
                    }
                    else
                    {
                        $mDataQuery = "SELECT id FROM decant_body WHERE decant_header_id=".$mDecantId." AND (lower(tt_ran_order)='no-ran' OR lower(tt_ran_order)='noran') LIMIT 1";
                    }
                    if (!$mData = mysqli_query($mConnection,$mDataQuery))
                        exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
                    if (mysqli_num_rows($mData) > 0)
                    {
                        $mErrMsg = "FAIL-Mixed RAN serials are not allowed";
                        exit($mErrMsg);
                    }
                }
                if ($mErrMsg == "")
                {
//					exit("FAIL-TEST1 ".$mProductTypeId."-".$mInvStatusId);
                    if (!empty($mProductTypeId) || !empty($mInvStatusId) || !empty($mInvInsp))
                    {
						$mOr = false;
                        $mDataQuery = "SELECT decant_body.id FROM decant_body";
						$mDataQuery .=" JOIN inventory_master ON decant_body.inventory_master_id=inventory_master.id";
						$mDataQuery .=" WHERE decant_body.decant_header_id=".$mDecantId." AND ";
						$mDataQuery .="(";
						if (!empty($mProductTypeId))
						{
							$mDataQuery .=" (inventory_master.product_type_id<>".$mProductTypeId.")";
							$mOr = true;
						}
						if (!empty($mInvStatusId))
						{
							if ($mOr == true)
								$mDataQuery .= " OR";
							$mDataQuery .=" (inventory_master.inventory_status_id<>".$mInvStatusId.")";
							$mOr = true;
						}
						if (!empty($mInvInsp))
						{
							if ($mOr == true)
								$mDataQuery .= " OR";
							$mDataQuery .=" (inventory_master.requires_inspection<>".$mInvInsp.")";
						}
						$mDataQuery .=")";
						$mDataQuery .=" LIMIT 1";
						
						try
						{
							$mData = mysqli_query($mConnection,$mDataQuery);
						}
						catch (Exception $e)
						{
                            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
                        }
                        if (mysqli_num_rows($mData) > 0)
                        {
                            $mErrMsg = "FAIL-Mixed Serial Types are not allowed";
                            exit($mErrMsg);
                        }
                    }
                }
            }
        }
    }
    else
// Try to get decant ref. based on serial_reference
    {
//      exit("FAIL-TEST1-".$mFilter."-".$mDataQuery);
        $mDocumentReference = "";
        $mDataQuery = "SELECT decant_header.id AS id, decant_header.document_reference AS document_reference FROM decant_body JOIN decant_header ON decant_body.decant_header_id=decant_header.id WHERE decant_body.serial_reference='".$mSerialReference."' AND decant_header.part_number='".$mPartNumber."' AND decant_body.decant_status IN(0,1) AND decant_body.qty >= 0 AND EXISTS(SELECT id FROM document_status WHERE decant_header.document_status_id=document_status.id AND lower(document_status.document_status_code) IN('open','allocated')) LIMIT 1";
        try
        {
            $mData = mysqli_query($mConnection,$mDataQuery);
        }
        catch(Exception $e)
        {
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
        }
        if (mysqli_num_rows($mData) == 0)
        {
            $mDataQuery = "SELECT id, part_number, vendor_id FROM part WHERE part_number='".$mPartNumber."' LIMIT 1";
            try
            {
                $mData = mysqli_query($mConnection,$mDataQuery);
            }
            catch(Exception $e)
            {
                exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
            }
            $mRow = mysqli_fetch_assoc($mData);
            $mPartId = $mRow['id'];
            $mVendorId = $mRow['vendor_id'];
            $mDocumentReference = "DEC000000";
            $mDataQuery = "Insert INTO decant_header (id, document_reference, part_number, vendor_id, document_status_id, part_id, date_created, created_by, last_updated_date, last_updated_by) VALUES(DEFAULT,'".$mDocumentReference."','".$mPartNumber."',".$mVendorId.",1,".$mPartId.",now(),'".$mCurrentUser."',now(),'".$mCurrentUser."')";
            try
            {
                mysqli_query($mConnection,$mDataQuery);
            }
            catch(Exception $e)
            {
                exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
            }
            $mDecantId=mysqli_insert_id($mConnection);
// Create new Pick Document_Ref.
            $mDocumentReference = "000000".round($mDecantId);
            $mDocumentReference = "DEC".substr($mDocumentReference,-6);
            $mDataQuery = "UPDATE decant_header SET document_reference='".$mDocumentReference."', document_status_id=(SELECT document_status.id FROM document_status WHERE lower(document_status.document_status_code)='open') WHERE id=".$mDecantId." LIMIT 1";
            try
            {
                mysqli_query($mConnection,$mDataQuery);
            }
            catch(Exception $e)
            {
                exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
            }
        }
        else
        {
            $mRow = mysqli_fetch_assoc($mData);
            $mDecantId = ($mRow['id']);
            $mDocumentReference = ($mRow['document_reference']);
        }
        if (substr($mErrMsg,0,4) != "FAIL")
            $mErrMsg = "DATA:documentReferenceinput:".$mDocumentReference.",allocationId:".$mDecantId;  
    }
    mysqli_close($mConnection);
    if ($mErrMsg == "")
        $mErrMsg = "OK  -getdecant";
    echo($mErrMsg);
    return;
}

// Validate / Process Split Serial
elseif (($mFunction != null) && ($mFunction=="decantserial"))
{
	$mReturn = decantSerial($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mFilter);
	exit ($mReturn);
}

// Send Decant qty to Server Process
elseif (($mFunction != null) && ($mFunction=="decantqty"))
{
    $mResult = "";
    $mResponse = "";
// Set default error message
    $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;
    $mDecantId = "";
// 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 == "decantid")
            $mDecantId = strtoupper($v2);
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "printer")
            $mPrinter = strtoupper($v2);
        elseif ($k2 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif (($k2 == "quantity") || ($k2 == "countQty"))
// Test for non-numeric Quantity
            if(is_numeric($v2))
            {
                $mQty = floatval($v2);
                if ($mQty <= 0)
                    exit("FAIL-Invalid quantity");
            }
            else
            {
                exit("FAIL-This must be a number");
            }           
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
        elseif ($k2 == "transactionTypeId")
            $mTransactionTypeId = ($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = ($v2);
    }
// test for valid Decant Id
    if ((!$mDecantId) || ($mDecantId == ""))
    {
        exit("FAIL-Decant Header is incorrect");
    }
// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
// validate decant qty.
    $mConversionFactor = 1;
    $mDataQuery = "SELECT conversion_factor FROM part WHERE part_number='$mPartNumber' LIMIT 1";
    if (!$mData = mysqli_query($mConnection,$mDataQuery))
        exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
    list($mConversionFactor) = mysqli_fetch_row($mData);
    $mDataQuery = "SELECT SUM(decant_body.qty) FROM decant_body WHERE decant_header_id=".$mDecantId." AND decant_status=1 GROUP BY decant_header_id";
    if (!$mData = mysqli_query($mConnection,$mDataQuery))
        exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
    list($mDecQty) = mysqli_fetch_row($mData);
    $mDataQuery = "SELECT max(decant_body.document_reference) AS document_reference,SUM(decant_body.qty) AS qty FROM decant_body WHERE decant_header_id=".$mDecantId." AND decant_status=0";
    if (!$mData = mysqli_query($mConnection,$mDataQuery))
        exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
    $mRow = mysqli_fetch_assoc($mData);
    $mDocumentReference = $mRow['document_reference'];
    $mPartNumber = $mRow['part_number'];
    $mInvQty = $mRow['qty'];
//          exit("FAIL-".mysqli_error($mConnection).$mDataQuery." ".$mRow['qty']." ".$mQty." ".$mQty*$mConversionFactor." ".$mPartNumber);
    if ($mDecQty >= $mInvQty)
    {
//        echo ("OKMS-Decant Split Complete - Print Labels,DATA:rdtMessage:Serial ".$mSerialReference." created,printer:".$mPrinter.":HHHHIHH:skip5");
        echo ("OKMS-Decant Split Complete - Print Labels (F5),DATA:rdtMessage:Serial ".$mSerialReference." created,printer:".$mPrinter."::skip2");
//        echo ("OKMS-Decant Split Complete - Print Labels (F5),DATA:rdtMessage:Serial ".$mSerialReference." created,CHGTRAN:decantlabel?DATA:".$mSerialReference);
        return;
    }
    if (($mQty*$mConversionFactor)+$mDecQty >= $mInvQty)
    {
        echo("FAIL-Split Qty is greater than total serial qty");
        return;
    }   
    $mReturn = "";

    $i = 0;
//    $mParentQty = $mRow['qty'];
    $mParentQty = $mInvQty;
    $mDecantQty = $mQty*$mConversionFactor;
    while ($mParentQty > 0)
    {
        If ($mParentQty < $mDecantQty)
        {
            $mDecantQty = $mParentQty;
            $mParentQty = 0;
        }
        else
        {
            $mParentQty = $mParentQty-$mDecantQty;
        }
        $i++;
//                    $mDecantQuery = "SELECT serial_reference FROM decant_body WHERE decant_header_id=".$mDecantId." AND serial_reference LIKE('".$mSerialReference."-%') ORDER by decant_header_id,id DESC,serial_reference DESC LIMIT 1";
        $mDecantQuery = "SELECT serial_reference,date_created FROM decant_body WHERE decant_header_id=".$mDecantId." ORDER by decant_header_id,id DESC,serial_reference DESC LIMIT 1";
        if (!$mDecantData = mysqli_query($mConnection,$mDecantQuery))
            exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
        if (mysqli_num_rows($mDecantData) == 0)
        {
            exit ("FAIL-Decant does not exist.");
        }
        else
        { 
            list($mDecantSerial,$mDateCreated) = mysqli_fetch_row($mDecantData);
            if (strpos($mDecantSerial,"-") === false)
            {
                $mDecantSerial = $mDecantSerial."-0000";
            }
            $mSuffix = substr($mDecantSerial,-4);
            $i = intval($mSuffix);
            $mSerialFound = false;
            $count = 0;
//                while($mSerialFound == true);
            for ($count = 0; $count <= 50; $count++)
            {
                $i = $i+1;
                $mSuffix = "0000".round($i);
                $mSerialReference = substr($mDecantSerial,0,strlen($mDecantSerial)-5)."-".substr($mSuffix,-4);
                $mDecantQuery = "SELECT id,date_created FROM inventory_master WHERE serial_reference = '".$mSerialReference."' LIMIT 1";
                if (!$mDecantData = mysqli_query($mConnection,$mDecantQuery))
                    exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
                if (mysqli_num_rows($mDecantData) == 0)
                    $mSerialFound = false;
                else
                {
                    $mSerialFound = true;
                    list($mId,$mDateCreated) = mysqli_fetch_row($mDecantData);
                }
                if ($mSerialFound == false)
                    break;                        
            }
        }
        $mDecantQuery = "SELECT id FROM decant_body WHERE decant_status=1 AND serial_reference='".$mSerialReference."' LIMIT 1";
        if (!$mDecantData = mysqli_query($mConnection,$mDecantQuery))
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
        if (mysqli_num_rows($mDecantData) == 0)
        {
            if ($mRow['ran_or_order'] == null)
                $mDataQuery = "Insert INTO decant_body (id, decant_header_id, document_reference, tt_ran_order, serial_reference, qty, inventory_master_id, decant_status, date_created, created_by, last_updated_date, last_updated_by) VALUES(DEFAULT,".$mDecantId.",'".$mDocumentReference."',null,'".$mSerialReference."',".$mDecantQty.",null,1,'".$mDateCreated."','".$mCurrentUser."',now(),'".$mCurrentUser."')";
            else
                $mDataQuery = "Insert INTO decant_body (id, decant_header_id, document_reference, tt_ran_order, serial_reference, qty, inventory_master_id, decant_status, date_created, created_by, last_updated_date, last_updated_by) VALUES(DEFAULT,".$mDecantId.",'".$mDocumentReference."','".$mRow['ran_or_order']."','".$mSerialReference."',".$mDecantQty.",null,1,'".$mDateCreated."','".$mCurrentUser."',now(),'".$mCurrentUser."')";
            if (!mysqli_query($mConnection,$mDataQuery))
                exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
        }
        if ($mTransactionTypeId == "DECANTCASE")
        {
			$mReturn = $mFilter . ",serialreference=".$mSerialReference;
			$mReturn = decantSerial ($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mReturn);
            continue;
        }
        else
        {
            break;
        }
    }
    $mDataQuery = "UPDATE decant_header SET document_status_id=(SELECT id FROM document_status WHERE lower(document_status_code)='allocated') WHERE id=".$mDecantId." LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
	if ($mTransactionTypeId == "DECANTCASE")
	{
		$mErrMsg = "OKMS-Decant Split Complete - Print Labels (F5)";
	}
    if ($mErrMsg == "")
    {
//      $mErrMsg = "OK  -decantqty";
        $mErrMsg = "DATA:rdtMessage:Serial ".$mSerialReference." created";
    }
    if (($mQty*$mConversionFactor)+$mDecQty >= $mInvQty)
    {
        $mErrMsg = "OKMS-Decant Split Complete - Print Labels (F5),DATA:rdtMessage:Serial ".$mSerialReference." created::skip2";
    }
    echo ($mErrMsg);
    return;
}

// Check for Mixed RANs
elseif (($mFunction != null) && ($mFunction=="checkran"))
{
    $mResult = "";
// Set default error message
    $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;
// 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 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "decantid")
            $mDecantId = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
    }
// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error()); 
// Get RAN for this Serial.     
    $mDataQuery = "SELECT ran_or_order AS ran_order FROM inventory_master WHERE part_number='".$mPartNumber."' AND serial_reference='".$mSerialReference."' LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        $mErrMsg = "FAIL-Invalid Serial";
    if ($mErrMsg == "")
    {
        $mRow = mysqli_fetch_assoc($mData);
        $mRanOrder = strtolower(($mRow['ran_order']));
    
        if (($mRanOrder == "no-ran") || ($mRanOrder == "noran"))
        {
            $mDataQuery = "SELECT id FROM decant_body WHERE id=".$mDecantId." AND lower(tt_ran_order)<>'no-ran' AND lower(tt_ran_order)<>'noran' LIMIT 1";
        }
        else
        {
            $mDataQuery = "SELECT id FROM decant_body WHERE id=".$mDecantId." AND (lower(tt_ran_order)='no-ran' OR lower(tt_ran_order)='noran') LIMIT 1";
        }
        if (!mysqli_query($mConnection,$mDataQuery))
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
        $mData = (mysqli_query($mConnection,$mDataQuery));
        if (mysqli_num_rows($mData) > 0)
        {
            $mErrMsg = "FAIL-Mixed RAN serials are not allowed";
        }
    }
    
    mysqli_close($mConnection);
    
    if ($mErrMsg == "")
    {
        $mErrMsg = "OK  -checkran";
    }
    echo ($mErrMsg);
    return;
}

// Flag Decant Header as Allocated
elseif (($mFunction != null) && ($mFunction=="allocatedecant"))
{
    $mResult = "";
// Set default error message
    $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;
// 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 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "decantid")
            $mDecantId = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
    }
// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
    
    $mDataQuery = "UPDATE decant_header SET document_status_id=(SELECT id FROM document_status WHERE lower(document_status_code)='allocated') WHERE decant_header.id=".$mDecantId." LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
    
    mysqli_close($mConnection);
    $mErrMsg = "OK  -allocatedecant";
}

// Flag Decant Header as Allocated
elseif (($mFunction != null) && ($mFunction=="checkdecantcomplete"))
{
    $mDecantId = "";
// Set default error message
    $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;
    $mPage = "";
// 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 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "decantid")
            $mDecantId = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
    }
// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error()); 
// Test for existing Decant Reference
    if ($mDecantId != "")
    {
// check if the decant is complete
        $mDataQuery = "SELECT decant_body.id AS id FROM decant_body WHERE decant_header_id =".$mDecantId." AND decant_status=0 AND EXISTS(SELECT id FROM inventory_master WHERE decant_body.inventory_master_id=inventory_master.id) LIMIT 1";
        if (!$mData = mysqli_query($mConnection,$mDataQuery))
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
        $mDataQuery = "UPDATE decant_header SET document_status_id=(SELECT id FROM document_status WHERE document_status_code='closed'),last_updated=now(),last_updated_by='".$mCurrentUser."' LIMIT 1";
        if (!$mData = mysqli_query($mConnection,$mDataQuery))
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
        if (mysqli_num_rows($mData) == 0)
        {
            echo ("OKMS-This decant is complete,DECANT");
            return;
        }
    }
    $mStatus = "OK  -decantComplete";
}

// Get Decant Id. from Input Serial
elseif (($mFunction != null) && ($mFunction=="getdecantref"))
{
    $mDecantId = "";
// Set default error message
    $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;
    $mPage = "";
// Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",",$mFilter);
// Create Associative Array by Exploding Array Elements on :
//    exit ("FAIL-".$mFilter);
    foreach($mArray1 as $k1=>$v1)
    {
        list($k2, $v2) = explode(":",$mArray1[$k1]);
        if ($k2 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "printer")
            $mPrinter = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
    }
// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());

// Try to get decant ref. based on serial_reference
    $mDocumentReference = "";
    $mDataQuery = "SELECT decant_header.id AS id, decant_header.document_reference AS document_reference FROM decant_body JOIN decant_header ON decant_body.decant_header_id=decant_header.id WHERE decant_body.serial_reference='".$mSerialReference."' AND decant_header.part_number='".$mPartNumber."' AND decant_body.qty>0 ORDER BY decant_header.document_reference DESC LIMIT 1";
    if (!$mData = mysqli_query($mConnection,$mDataQuery))
        exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
    if (mysqli_num_rows($mData) > 0)
    {
        $mRow = mysqli_fetch_assoc($mData);
        $mDecantId = ($mRow['id']);
        $mDocumentReference = ($mRow['document_reference']);
    }
    else
    {
        $mErrMsg = "FAIL-No Decant for this Serial";
    }

    if (substr($mErrMsg,0,4) != "FAIL")
        $mErrMsg = "DATA:documentReferenceinput:".$mDocumentReference.",allocationId:".$mDecantId;  
    
    mysqli_close($mConnection);
    if ($mErrMsg == "")
        $mErrMsg = "OK  -getdecantref";
    echo($mErrMsg);
    return;
}

// Flag Decant Header as Allocated
elseif (($mFunction != null) && ($mFunction=="closedecantheader"))
{
    $mResult = "";
// Set default error message
    $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;

// 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 == "id")
            $mDecantId = $v2;
        elseif ($k2 == "userId")
            $mCurrentUser = strtoupper($v2);
    }

// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
    
    $mDataQuery = "UPDATE decant_header SET document_status_id=(SELECT id FROM document_status WHERE lower(document_status_code)='closed') WHERE decant_header.id=".$mDecantId." LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
    
    mysqli_close($mConnection);
    $mErrMsg = "OK  -closedecantheader";

}

// Return Inventory / Part Details
elseif (($mFunction != null) && (strtolower($mFunction) == "getdecantpart"))
{
// Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=",":",$mFilter);
    $mFilter = str_replace("&",",",$mFilter);
    $i=0;
// Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",",$mFilter);
// Create Associative Array by Exploding Array Elements on :
    $mPartNumber = "";
    $mSerialReference = "";
    $mVendorPartCode = "";
    $mLocationCode = "";
    $mPlt = "";
    $mShowMsg = "";
    $mSkip = "";
    foreach($mArray1 as $k1=>$v1)
    {
        list($k2, $v2) = explode(":",$mArray1[$k1]);
        if ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "vendorpartcode")
            $mVendorPartCode = strtoupper($v2);
        elseif ($k2 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "tolocationhashcode")
            $mLocationCode = strtoupper($v2);
        elseif ($k2 == "plt")
            $mPlt = strtoupper($v2);
        elseif ($k2 == "showmessage")
            $mShowMsg = strtoupper($v2);
        elseif (strtolower($k2) == "skip")
            $mSkip = strtolower($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = $v2;
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper($v2);
        elseif ($k2 == "transactionTypeId")
            $mTransactionTypeId = strtoupper($v2);
    }
// blank input parameters - ignore
    if (($mPartNumber == "") && ($mVendorPartCode == "") && ($mSerialReference == "") && ($mPlt == ""))
    {
        echo "OK  -";
        return;
    }
    // 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 = "FAIL-No Matching Records";

// Find Part Number from Serial Reference
    if (($mSerialReference != "") && ($mPartNumber == "") && ($mVendorPartCode == ""))
    {
        $mDataQuery = "SELECT inventory_master.id AS id, part.part_number AS part_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 WHERE inventory_master.serial_reference='".$mSerialReference."' AND inventory_qty > 0 LIMIT 2";
        $mData = mysqli_query($mConnection,$mDataQuery);
        if (mysqli_num_rows($mData) > 1)
        {
            mysqli_close($mConnection);
            if ($mSkip != "")
            {
                echo "DATA:serialReference:".$mSerialReference."::,skip".$mSkip.",partNumber::I";
                return;
            }   
            else
            {
                echo "DATA:partNumber::I";
                return;
            }
        }
        elseif (mysqli_num_rows($mData) == 1)
        {
            while($mRow = mysqli_fetch_assoc($mData)) 
            {
                $mPartNumber = ($mRow['part_number']);
                $mLocationCode = ($mRow['location_code']);
            }
            if ($mTransactionTypeId == "CTD")
            {
                $mDataQuery = "SELECT location.location_code AS location_code FROM location JOIN location_type ON location.location_type_id=location_type.id WHERE location_type.location_type_code='dec' LIMIT 1";
                if (!$mData = mysqli_query($mConnection,$mDataQuery))
                    exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                if (mysqli_num_rows($mData) > 0)
                {
                    list($mLocationCode) = mysqli_fetch_row($mData);
                }
                
            }
        }
        elseif (mysqli_num_rows($mData) == 0)
        {
            $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 JOIN location ON inventory_master.current_location_id=location.id JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE tag.tag_reference='".$mPlt."' AND inventory_qty > 0 LIMIT 1";
            $mData = mysqli_query($mConnection,$mDataQuery);
            if (mysqli_num_rows($mData) == 0)
            {
                $mDataQuery = "SELECT decant_body.id AS id, part.part_number AS part_number, '' AS location_code FROM decant_body JOIN decant_header ON decant_body.decant_header_id=decant_header.id JOIN part ON decant_header.part_id=part.id WHERE decant_body.serial_reference='".$mSerialReference."' LIMIT 1";
                if (!mysqli_query($mConnection,$mDataQuery))
                    exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                $mData = mysqli_query($mConnection,$mDataQuery);
            }
            if (mysqli_num_rows($mData) == 0)
            {
                $mErrMsg = "FAIL-Serial not found".$mSerialReference;
                mysqli_close($mConnection);
                exit($mErrMsg);
            }
            else
            {
                while($mRow = mysqli_fetch_assoc($mData)) 
                {
                    $mPartNumber = ($mRow['part_number']);
                    $mLocationCode = ($mRow['location_code']);
                }
            }
        }
    }

    if ($mPartNumber != "")
    {
        $mDataQuery = "SELECT part_number AS partNumber, rdt_prompt AS rdtMessage, requires_count AS countonrcpt, effective_from AS effective_from, effective_to AS effective_to FROM part WHERE part_number='".$mPartNumber."' LIMIT 1";
    }
    if ($mVendorPartCode != "")
    {
        $mDataQuery = "SELECT part_number AS partNumber, rdt_prompt AS rdtMessage, requires_count AS countonrcpt, effective_from AS effective_from, effective_to AS effective_to FROM part WHERE jis_supply_group='".$mVendorPartCode."' LIMIT 1";
    }
    if (!mysqli_query($mConnection,$mDataQuery))
    {
        exit("FAIL-".mysqli_error($mConnection));
    }
    $mData = mysqli_query($mConnection,$mDataQuery);
    $i = 0;
    $mRdtMessage = "";
    $mCountOnRcpt = "";
    $mEffFrom = "";
    $mEffTo = "";
    while($mRow = mysqli_fetch_assoc($mData)) 
    {
        $mPartNumber = ($mRow['partNumber']);
        $mRdtMessage=($mRow['rdtMessage']);
        $mCountOnRcpt=($mRow['countonrcpt']);
        $mEffFrom=($mRow['effective_from']);
        $mEffTo=($mRow['effective_to']);
        $i++;
    }
    mysqli_close($mConnection);
   
    $mSymbols=array('!','"','#','$','%','&','\'','(',')','*','+',',','-','.','/',':',';','<','>','=','?','@','[',']','\\','^','_','{','}','|','~','`','£');
    $mRdtMessage=str_replace($mSymbols,'',$mRdtMessage);
    
    $mDataStr = "";
    if ($i > 0)
    {
        $mStartDate = date("Y-m-d H:i:s");
        if (($mStartDate < $mEffFrom) || ($mStartDate > $mEffTo))
        {
            $mTransactionTypes['PBTP','PBTTP','PBTM'];
            if (in_array($mTransactionTypeId,$mTransactionTypes))
//            if (($mTransactionTypeId == "PBTP") || ($mTransactionTypeId == "PBTM"))
            {
                $mRdtMessage .= " Warning - ".$mPartNumber." is not active today";
            }
            else
            {
                echo "FAIL- ".$mPartNumber." is not active today";
                return;
            }
        }       
    }
    if ($i > 0)
    {
        if ($mShowMsg != "Y")
            $mRdtMessage = "";
        if (empty($mRdtMessage))
        {
//            $mRdtMessage = "Enter next serial or F2";
        }
        if ($mTransactionTypeId == "JRBTP")
            $mDataStr = "DATA-PartNumber:".$mPartNumber.",quantity:1,rdtMessage:".$mRdtMessage.",countonrcpt:".$mCountOnRcpt;
        elseif ($mTransactionTypeId == "CTD")
            $mDataStr = "DATA-PartNumber:".$mPartNumber.",rdtMessage:".$mRdtMessage;
        elseif ($mTransactionTypeId == "CTM")
            $mDataStr = "DATA-PartNumber:".$mPartNumber.",rdtMessage:".$mRdtMessage.",countonrcpt:".$mCountOnRcpt;
        elseif ($mLocationCode != "")
            $mDataStr = "DATA-PartNumber:".$mPartNumber.",rdtMessage:".$mRdtMessage.",countonrcpt:".$mCountOnRcpt.",toLocationHashCode:".$mLocationCode;
        else
            $mDataStr = "DATA-PartNumber:".$mPartNumber.",rdtMessage:".$mRdtMessage.",countonrcpt:".$mCountOnRcpt;
        $mErrMsg = "";
//      exit($mDataStr);
        echo $mDataStr;
        return;
    }
    else
    {
        echo "FAIL-Invalid Part Number";
        return;
    }
}

// Return Inventory / Part Details
elseif (($mFunction != null) && (strtolower($mFunction) == "postctd"))
{
// Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=",":",$mFilter);
    $mFilter = str_replace("&",",",$mFilter);
    $i=0;
//    exit("FAIL-".$mFilter);
// Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",",$mFilter);
// Create Associative Array by Exploding Array Elements on :
    $mPartNumber = "";
    $mSerialReference = "";
    $mLocationCode = "";
    foreach($mArray1 as $k1=>$v1)
    {
        list($k2, $v2) = explode(":",$mArray1[$k1]);
        if ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "tolocationhashcode")
            $mLocationCode = strtoupper($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = $v2;
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper($v2);
        elseif ($k2 == "transactionTypeId")
            $mTransactionTypeId = strtoupper($v2);
    }
// blank input parameters - ignore
    if (($mPartNumber == "") && ($mVendorPartCode == "") && ($mSerialReference == ""))
    {
        echo "OK  -";
        return;
    }
    // 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 = "";
    $mDataQuery = "SELECT id,location_code FROM location WHERE location_code = '$mLocationCode' OR location_hash ='$mLocationVCode' LIMIT 1";
    try
    {
        $mData = mysqli_query($mConnection,$mDataQuery);
    }
    catch (Exception $e)
    {
        exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData));
    }
    if (mysqli_num_rows($mData) > 0)
    {
        list ($mLocId,$mLocCode) = mysqli_fetch_row($mData);
    }
    else
    {   exit("OK  -No Location");
    }
    $mDataQuery = "SELECT * FROM inventory_master WHERE serial_reference='$mSerialReference' AND part_number='$mPartNumber' LIMIT 1";
    try
    {
        $mData = mysqli_query($mConnection,$mDataQuery);
    }
    catch (Exception $e)
    {
        exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData));
    }
    $mRow = mysqli_fetch_assoc($mData);
    $mDataQuery = "UPDATE inventory_master SET current_location_id=".$mLocId.",location_id=".$mLocId.",location_code='$mLocCode'";
    $mDataQuery .=", requires_decant=1";
    $mDataQuery .=", last_updated=now(),last_updated_by='$mCurrentUser'";
    $mDataQuery .=" WHERE serial_reference='$mSerialReference' AND part_number='$mPartNumber'";
    $mDataQuery .=" LIMIT 1";
    try
    {
        mysqli_query($mConnection,$mDataQuery);
    }
    catch (Exception $e)
    {
        exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData));
    }
    if (empty($mErrMsg))
    {
        $mErrMsg = "OK  -postctd";
    }
}

// Validate / Process Split Serial
elseif (($mFunction != null) && ($mFunction=="decantmergeserial"))
{
    $mResult = "";
    $mResponse = "";
// Set default error message
    $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;
// 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 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "decantreference")
            $mDecantRef = strtoupper($v2);
        elseif ($k2 == "decantid")
            $mDecantId = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
    }
    
// mysql connection - Use Passed values...
	Try
	{
		$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	}
	catch(Exception $e)
	{
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
	}
        
// Check Serial
    if ($mDecantRef == $mSerialReference)
    {
        $mDataQuery = "SELECT id FROM decant_body WHERE document_reference='".$mDecantRef."' AND serial_reference='".$mDecantRef."' AND qty=0";
        try
        {
            $mData = mysqli_query($mConnection,$mDataQuery);
        }
        catch (Exception $e)
        {
            exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
        }
        if (mysqli_num_rows($mData)>0)
        {
            exit("DATA:allocationId:".$mDecantId.",documentReference:".$mDecantRef.",partNumber:".$mPartNumber.",serialReference:::skipserialReference,rdtMessage:Enter next serial or F2");
        }
// Post Decant to Inventory
        $mDataQuery = "SELECT decant_body.id As id, decant_body.inventory_master_id AS inventory_master_id,decant_body.qty AS qty,decant_body.serial_reference AS serial_reference";
        $mDataQuery .=", decant_header.part_number AS part_number";
        $mDataQuery .=", part.id AS part_id,part.conversion_factor AS conversion_factor";
		$mDataQuery .=", inventory_master.current_location_id AS location_id,inventory_master.location_code AS location_code";
        $mDataQuery .=" FROM decant_body";
        $mDataQuery .=" JOIN decant_header ON decant_body.decant_header_id=decant_header.id";
        $mDataQuery .=" LEFT JOIN part ON decant_header.part_id=part.id";
		$mDataQuery .=" LEFT JOIN inventory_master ON decant_body.inventory_master_id=inventory_master.id";
        $mDataQuery .=" WHERE decant_body.document_reference='".$mDecantRef."'";
		$mDataQuery .=" ORDER BY decant_body.document_reference, decant_body.id DESC";
        try
        {
            $mData = mysqli_query($mConnection,$mDataQuery);
        }
        catch(Exception $e)
        {
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery." ".mysqli_num_rows($mData));
        }
		$mLocId = "";
        while($mRow = mysqli_fetch_assoc($mData)) 
        {
			if (empty($mLocId))
			{
				$mLocId = $mRow['location_id'];
				$mLocCode = $mRow['location_code'];
			}
//			print_r("FAIL-TEST1-".mysqli_num_rows($mData)." ".$mDecantRef." ".$mRow['serial_reference']);
            if ($mRow['serial_reference']==$mDecantRef)
            {
				$mSerialReference = $mDecantRef;
				if (empty($mLocId))
				{
					$mInvQuery = "SELECT id,location_code FROM location WHERE location_code='decant' LIMIT 1";
					try
					{
						$mInvData = mysqli_query($mConnection,$mInvQuery);
					}
					catch(Exception $e)
					{
						exit("FAIL-".mysqli_error($mConnection).$mInvQuery." ".mysqli_num_rows($mInvData));
					}
					list($mLocId,$mLocCode) = mysqli_fetch_row($mInvData);
				}
				
				$mSuffix = 0;
				$mSuffixChar = "ABCDEFGHIJK";
				for ($i = 0; $i <= 10; $i++)
				{
					$mInvQuery = "SELECT id FROM inventory_master WHERE part_number='".$mPartNumber."' AND serial_reference='".$mSerialReference."' LIMIT 1";
					try
					{
						$mInvData = mysqli_query($mConnection,$mInvQuery);
					}
					catch(Exception $e)
					{
						exit("FAIL-".mysqli_error($mConnection).$mInvQuery." ".mysqli_num_rows($mInvData));
					}
					if (mysqli_num_rows($mInvData) == 0)
					{
						break;
					}
					else
					{
						$mSerialReference = $mDecantRef.substr($mSuffixChar,$mSuffix,1);
						$mSuffix++;
						continue;
					}
				}
				
				if (mysqli_num_rows($mInvData) == 0)
                {
                    $mInvQuery = "INSERT INTO inventory_master (id,allocated_qty,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)";
                    $mInvQuery .=" VALUES(DEFAULT,0,null,".$mLocId.",now(),null,".$mRow['qty'].",now(),'".$mCurrentUser."',null,".$mRow['part_id'].",null,0,0,0,'".$mSerialReference."',null,".$mRow['conversion_factor'].",now(),1,'".$mPartNumber."',0,0,".$mRow['qty'].",'".$mCurrentUser."','".$mLocCode."',null,null,".$mLocId.",1)";
                    try
                    {
                        mysqli_query($mConnection,$mInvQuery);
                    }
                    catch(Exception $e)
                    {
                        exit ("FAIL-".mysqli_error($mConnection)."-".$mInvQuery);
                    }
                    $mTransactionTypeId = "DEC_IN";
                    $mInvQuery = "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,comment,source_tag,conversion_factor)";
                    $mInvQuery .=" VALUE(DEFAULT,'".$mDecantRef."','".$mPartNumber."','".$mSerialReference."','DEC-IN',".$mRow['qty'].",null,'".$mLocCode."',null,null,'".$mDecantRef."',now(),'".$mCurrentUser."',now(),'".$mCurrentUser."',null,'DEC-IN','',null,".$mRow['conversion_factor'].")";
                    try
					{
						$mInvData = mysqli_query($mConnection,$mInvQuery);
					}
					catch(Exception $e)
					{
						exit("FAIL-".mysqli_error($mConnection).$mInvQuery." ".mysqli_num_rows($mInvData));
					}
                }
                $mInvQuery = "UPDATE decant_body SET decant_status=2,last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mRow['id']." LIMIT 1";
                try
				{
					mysqli_query($mConnection,$mInvQuery);
				}
				catch(Exception $e)
				{
					exit("FAIL-".mysqli_error($mConnection).$mInvQuery);
				}
            }
            else
            {
                $mInvQuery = "SELECT * FROM inventory_master WHERE part_number='".$mPartNumber."' AND serial_reference='".$mRow['serial_reference']."' LIMIT 1";
                try
                {
                    $mInvData = mysqli_query($mConnection,$mInvQuery);
                }
                catch(Exception $e)
                {
                    exit ("FAIL-".mysqli_error($mConnection)."-".$mInvQuery);
                }
                if (mysqli_num_rows($mInvData) > 0)
                {
                    $mInvRow = mysqli_fetch_assoc($mInvData);
                    $mInvQuery = "DELETE FROM inventory_master WHERE id > 0 AND id=".$mRow['inventory_master_id']." LIMIT 1";
                    try
                    {
                        mysqli_query($mConnection,$mInvQuery);
                    }
                    catch(Exception $e)
                    {
                        $mInvQuery = "UPDATE inventory_master SET inventory_qty=0, allocated_qty=0,available_qty=0,last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mInvRow['id']." LIMIT 1";
                        try
                        {
                            mysqli_query($mConnection,$mInvQuery);
                        }
                        catch(Exception $e)
                        {
                            exit ("FAIL-".mysqli_error($mConnection)."-".$mInvQuery);
                        }
                    }
                    $mTransactionTypeId = "DEC_OUT";
                    $mInvQuery = "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,comment,source_tag,conversion_factor)";
                    $mInvQuery .=" VALUE(DEFAULT,'".$mDecantRef."','".$mPartNumber."','".$mInvRow['serial_reference']."','".$mTransactionTypeId."',".$mInvRow['inventory_qty'].",'".$mInvRow['location_code']."',null,null,null,'".$mDecantRef."',now(),'".$mCurrentUser."',now(),'".$mCurrentUser."',null,'".$mTransactionTypeId."','',null,".$mInvRow['conversion_factor'].")";
                    try
                    {
                        mysqli_query($mConnection,$mInvQuery);
                    }
                    catch(Exception $e)
                    {
                        exit ("FAIL-".mysqli_error($mConnection)."-".$mInvQuery);
                    }
                }
            }
        }
        $mInvQuery = "UPDATE decant_header SET document_status_id=(SELECT id FROM document_status WHERE document_status_code='closed') WHERE id=".$mDecantId." LIMIT 1";
        try
        {
            mysqli_query($mConnection,$mInvQuery);
        }
        catch(Exception $e)
        {
            exit ("FAIL-".mysqli_error($mConnection)."-".$mInvQuery);
        }
        
        exit("OKMS-Decant ".$mDecantRef." is complete,CHGTRAN:DECANTMERGE");
    }
    else
    {
        $mDataQuery = "SELECT id,available_qty AS inventory_qty FROM inventory_master WHERE part_number='".$mPartNumber."' AND serial_reference='".$mSerialReference."' LIMIT 1";
        try
		{
			$mData = mysqli_query($mConnection,$mDataQuery);
		}
		catch(Exception $e)
		{
			exit("FAIL-".mysqli_error($mConnection).$mDataQuery." ".mysqli_num_rows($mData));
		}
        if (mysqli_num_rows($mData) == 0)
        {
            exit ("FAIL-Invalid Serial");
        }
        else
        {
            list($mInvId,$mInvQty) = mysqli_fetch_row($mData);
        }
    }
//  exit("FAIL-TEST1".$mDataQuery);
// Check if Decant Header exists
    $mResult = $mDecantId;
    if (is_numeric($mResult))
    {
// Test for existing Decant Reference
        $mDataQuery = "SELECT id, document_reference, part_number FROM decant_header WHERE id=".$mResult." LIMIT 1";
        try
		{
			$mData = mysqli_query($mConnection,$mDataQuery);
		}
		catch(Exception $e)
		{
			exit("FAIL-".mysqli_error($mConnection).$mDataQuery." ".mysqli_num_rows($mData));
		}
        $mRow = mysqli_fetch_assoc($mData);
        $mDocumentReference = $mRow['document_reference'];
        if (substr($mSerialReference,0,9) != $mDocumentReference)
            $mRdtMessage = "Enter Next Serial to Decant or F2 if Done.";
        else
            $mRdtMessage = "";
// Does serial exist on decant_body
        $mDataQuery = "SELECT id, tt_ran_order, decant_status, qty FROM decant_body WHERE decant_body.decant_header_id=".$mResult." AND decant_body.serial_reference='".$mDocumentReference."' AND decant_status IN(0) LIMIT 1";
        try
		{
			$mData = mysqli_query($mConnection,$mDataQuery);
		}
		catch(Exception $e)
		{
			exit("FAIL-".mysqli_error($mConnection).$mDataQuery." ".mysqli_num_rows($mData));
		}
        if (mysqli_num_rows($mData) == 0)
        {
            $mDataQuery = "INSERT INTO decant_body (id, decant_header_id, document_reference, tt_ran_order, serial_reference, qty, inventory_master_id, decant_status, date_created, created_by, last_updated_date, last_updated_by) VALUES(DEFAULT,".$mResult.",'".$mDocumentReference."',null,'".$mDocumentReference."',0,null,0,now(),'".$mCurrentUser."',now(),'".$mCurrentUser."')";
            try
			{
				mysqli_query($mConnection,$mDataQuery);
			}
			catch(Exception $e)
			{
				exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
			}
        }
// Add Scanned Serial to decant_body
        $mDataQuery = "SELECT id, tt_ran_order, decant_status, qty FROM decant_body WHERE decant_body.decant_header_id=".$mResult." AND decant_body.serial_reference='".$mSerialReference."' AND decant_status IN(1) LIMIT 1";
        try
		{
			$mData = mysqli_query($mConnection,$mDataQuery);
		}
		catch(Exception $e)
		{
			exit("FAIL-".mysqli_error($mConnection).$mDataQuery." ".mysqli_num_rows($mData));
		}
        if (mysqli_num_rows($mData) == 0)
        {
            $mDataQuery = "INSERT INTO decant_body (id, decant_header_id, document_reference, tt_ran_order, serial_reference, qty, inventory_master_id, decant_status, date_created, created_by, last_updated_date, last_updated_by) VALUES(DEFAULT,".$mResult.",'".$mDocumentReference."',null,'".$mSerialReference."',".$mInvQty.",".$mInvId.",1,now(),'".$mCurrentUser."',now(),'".$mCurrentUser."')";
            try
			{
				mysqli_query($mConnection,$mDataQuery);
			}
			catch(Exception $e)
			{
				exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
			}
        }
        else
        {
            if (!empty($mInvId))
            {
                list($mDecId) = mysqli_fetch_row($mData);
                $mDataQuery = "UPDATE decant_body SET qty = ".$mInvQty.",inventory_master_id=".$mInvId." WHERE id=".$mDecId." LIMIT 1";
                try
				{
					$mData = mysqli_query($mConnection,$mDataQuery);
				}
				catch(Exception $e)
				{
					exit("FAIL-".mysqli_error($mConnection).$mDataQuery." ".mysqli_num_rows($mData));
				}
            }
        }
    }
    else
	{
		if (empty($mErrMsg))
		{
			exit("DATA:rdtMessage:Scan next serial or F2 to complete");
		}
		else
		{	
			exit($mErrMsg);
		}
	}
//	exit ("FAIL-TEST1-".$mDataQuery."-".$mInvQuery);
	exit ("OK  -decantMergeSerial");
//	exit("OKMS-Enter next serial or F2 to finish");
}

// Validate / Process Split Serial
elseif (($mFunction != null) && ($mFunction=="decantmergecomplete"))
{
    $mResult = "";
    $mResponse = "";
// Set default error message
    $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;
// 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 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "decantreference")
            $mDecantRef = strtoupper($v2);
        elseif ($k2 == "decantid")
            $mDecantId = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
    }
    
// mysql connection - Use Passed values...
    try
    {
        $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
    }
    catch (Exception $e)
    {
        exit("FAIL-Connect failed ".$e."-".$mHost);
    }

//    $mDataQuery = "SELECT sum(qty) AS qty FROM decant_body WHERE document_reference='".$mDecantRef."' AND serial_reference <> '".$mDecantRef."' AND decant_status=1 LIMIT 1";
	$mDataQuery = "SELECT decant_body.qty AS qty";
	$mDataQuery .=",inventory_master.current_location_id AS location_id, inventory_master.location_code AS location_code";
	$mDataQuery .=" FROM decant_body";
	$mDataQuery .=" LEFT JOIN inventory_master ON decant_body.inventory_master_id=inventory_master.id";
	$mDataQuery .=" WHERE decant_body.document_reference='".$mDecantRef."' AND decant_body.serial_reference <> '".$mDecantRef."' AND decant_body.decant_status=1";
    try
    {
        $mData = mysqli_query($mConnection,$mDataQuery);
    }
    catch (Exception $e)
    {
        exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
    }
    $mDecQty = 0;
    if (mysqli_num_rows($mData)>0)
	{
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDecQty = $mDecQty+$mRow['qty'];
			$mInvLocId = $mRow['location_id'];
			$mInvLocCode = $mRow['location_code'];
		}
        if (!empty($mDecQty))
        {
            $mDataQuery = "UPDATE decant_body SET qty=0, decant_status=2 WHERE document_reference='".$mDecantRef."' AND serial_reference <> '".$mSerialReference."' AND decant_status=1";
            try
            {
                mysqli_query($mConnection,$mDataQuery);
            }
            catch (Exception $e)
            {
                exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
            }
            $mDataQuery = "UPDATE decant_body SET qty = ".$mDecQty.", decant_status=1 WHERE document_reference='".$mDecantRef."' AND decant_status=0 LIMIT 1";
            try
            {
                mysqli_query($mConnection,$mDataQuery);
            }
            catch (Exception $e)
            {
                exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
            }
        }
        exit("OKMS-Serial ".$mDecantRef." created. Print the label & scan serial.");
    }
}

// Validate / Process Split Serial
elseif (($mFunction != null) && ($mFunction=="decantcount"))
{
	$mReturn = decantCount($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mFilter);
	exit ($mReturn);
}

if(is_resource($mConnection) && get_resource_type($mConnection)==='mysql link')
{
    try
    {
        mysqli_close($mConnection);
    }
    catch(Exception $e)
    {
    }
}

if (($mStatus == "") && ($mErrMsg != ""))
    $mStatus = $mErrMsg;
// echo json_encode(array_merge(array("status" => $mStatus),array("data" => $mDataStr),$mSQLData));
if ($mDataStr == "")
    echo $mStatus;
else if(substr($mDataStr,0,4) == "DATA")
    echo $mDataStr;
else
    echo $mStatus.$mDataStr;        
return;

// Validate / Process Split Serial
function decantSerial ($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mFilter,$mConnect="")
{
    $mResult = "";
    $mResponse = "";
// Set default error message
    $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;
// 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 == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "decantid")
            $mDecantId = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
		elseif ($k2 == "transactiontype")
            $mTransactionTypeId = strtoupper($v2);
		elseif ($k2 == "transactiontypeId")
            $mTransactionTypeId = strtoupper($v2);
    }

//	exit("FAIL-TEST1-".$mFilter." ".$mSerialReference);

// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error()); 
// Check if Decant Header exists
    $mResult = $mDecantId;
    if (is_numeric($mResult))
    {
// Test for existing Decant Reference
		$mDataQuery = "SELECT id, document_reference, part_number FROM decant_header WHERE id=".$mDecantId." LIMIT 1";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
//              $mData = (mysqli_query($mConnection,$mDataQuery));
		$mRow = mysqli_fetch_assoc($mData);
		$mDecantHdrId = $mRow['id'];
		$mDocumentReference = $mRow['document_reference'];
//      if (substr($mSerialReference,0,9) != $mDocumentReference) 
//          $mRdtMessage = "Enter Next Serial to Decant or F2 if Done.";
//      else
		$mRdtMessage = "";      
// Does serial exist on decant_body
        $mDataQuery = "SELECT id, tt_ran_order, decant_status, qty FROM decant_body";
		$mDataQuery .=" WHERE decant_body.decant_header_id=".$mDecantHdrId." AND decant_body.serial_reference='".$mSerialReference."'";
		if ($mTransactionTypeId == "DECANTMERGE")
		{
			$mDataQuery .="AND decant_status IN(0,1)";
		}
		else
		{
			$mDataQuery .="AND decant_status IN(0,1)";
		}
		$mDataQuery .=" LIMIT 1";
        if (!$mData = mysqli_query($mConnection,$mDataQuery))
            exit("FAIL-".mysqli_error($mConnection).$mDataQuery." ".mysqli_num_rows($mData));
        if (mysqli_num_rows($mData) == 0)
        {
            $mDataQuery = "SELECT inventory_master.id AS id, inventory_master.ran_or_order AS ran_or_order, inventory_master.available_qty AS inventory_qty,inventory_master.date_created AS date_created";
            $mDataQuery .=" FROM inventory_master";
            $mDataQuery .=" LEFT JOIN location ON inventory_master.current_location_id=location.id";
            $mDataQuery .=" LEFT JOIN location_type ON location.location_type_id=location_type.id";
            $mDataQuery .=" WHERE inventory_master.serial_reference='".$mSerialReference."' AND inventory_master.part_number='".$mPartNumber."'";
//            $mDataQuery .=" AND lower(location_type_code)='dec'";
            $mDataQuery .=" LIMIT 1";
            if (!$mData = mysqli_query($mConnection,$mDataQuery))
                exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
            if (mysqli_num_rows($mData) == 0)
            {
                exit("FAIL-This Serial is not available for Decant");
            }
            $mRow = mysqli_fetch_assoc($mData);
            if ($mRow['ran_or_order'] == null)
                $mDataQuery = "Insert INTO decant_body (id, decant_header_id, document_reference, tt_ran_order, serial_reference, qty, inventory_master_id, decant_status, date_created, created_by, last_updated_date, last_updated_by) VALUES(DEFAULT,".$mResult.",'".$mDocumentReference."',null,'".$mSerialReference."',".$mRow['inventory_qty'].",".$mRow['id'].",0,'".$mRow['date_created']."','".$mCurrentUser."',now(),'".$mCurrentUser."')";
            else
                $mDataQuery = "Insert INTO decant_body (id, decant_header_id, document_reference, tt_ran_order, serial_reference, qty, inventory_master_id, decant_status, date_created, created_by, last_updated_date, last_updated_by) VALUES(DEFAULT,".$mResult.",'".$mDocumentReference."','".$mRow['ran_or_order']."','".$mSerialReference."',".$mRow['inventory_qty'].",".$mRow['id'].",0,'".$mRow['date_created']."','".$mCurrentUser."',now(),'".$mCurrentUser."')";
            if (!mysqli_query($mConnection,$mDataQuery))
                exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
        }
        else
        {
// Test for decant serial
            $mRow = mysqli_fetch_assoc($mData);
            if (($mRow['decant_status'] == 1))
            {
                $mDecantId = $mRow['id'];
                $mDecantQty = $mRow['qty'];
                $mRanOrder = $mRow['document_reference'];
                $mDataQuery = "SELECT id FROM inventory_master WHERE part_number='".$mPartNumber."' AND serial_reference='".$mSerialReference."' LIMIT 1";
                if (!$mData = mysqli_query($mConnection,$mDataQuery))
                    exit("FAIL-".mysqli_error($mConnection).$mDataQuery." ".mysqli_num_rows($mData));
                if (mysqli_num_rows($mData) == 0)
                {
                    $mInvQuery = "SELECT inventory_master.id AS id,inventory_master.current_location_id AS current_location_id, location.location_code AS location_code, inventory_master.available_qty AS inventory_qty, inventory_master.ran_or_order AS ran_or_order, inventory_master.serial_reference AS serial_reference, inventory_master.product_type_id AS product_type_id, inventory_master.requires_inspection AS requires_inspection,inventory_master.inventory_status_id AS inventory_status_id, inventory_master.date_created AS date_created, part.id AS part_id,part.part_number AS part_number, part.conversion_factor AS conversion_factor, vendor.vendor_reference_code AS vendor_code,product_type.product_type_code AS product_type_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 vendor ON part.vendor_id=vendor.id LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id WHERE inventory_qty>0 AND EXISTS(SELECT id FROM decant_body WHERE inventory_master.id=decant_body.inventory_master_id AND decant_body.decant_header_id=".$mDecantHdrId." AND decant_body.decant_status=0) LIMIT 100";
                    if (!$mInvData = mysqli_query($mConnection,$mInvQuery))
                        exit("FAIL-".mysqli_error($mConnection).$mInvQuery);
                    While($mInvRow = mysqli_fetch_assoc($mInvData))
                    {
                        $mDataQuery = "SELECT id FROM inventory_master WHERE part_id=".$mInvRow['part_id']." AND serial_reference='".$mSerialReference."' LIMIT 1";
                        if (!$mData = mysqli_query($mConnection,$mDataQuery))
                            exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                        if (mysqli_num_rows($mData) == 0)
                        {
                            $mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,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)";
                            $mDataQuery .=" VALUES(DEFAULT,0,null,".$mInvRow['current_location_id'].",'".$mInvRow['date_created']."',null,".$mDecantQty.",now(),'".$mCurrentUser."',null,".$mInvRow['part_id'].",'".$mInvRow['ran_or_order']."',0,0,".$mInvRow['requires_inspection'].",'".$mSerialReference."',null,".$mInvRow['conversion_factor'].",'".$mInvRow['date_created']."',1,'".$mInvRow['part_number']."',0,0,".$mDecantQty.",'".$mCurrentUser."','".$mInvRow['location_code']."',null,null,".$mInvRow['current_location_id'].",".$mInvRow['inventory_status_id'].")";
                            if (!mysqli_query($mConnection,$mDataQuery))
                                exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                            $mTransactionTypeId = "DEC_IN";
                            $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,comment,source_tag,conversion_factor)";
                            $mDataQuery .=" VALUE(DEFAULT,'".$mInvRow['ran_or_order']."','".$mInvRow['part_number']."','".$mSerialReference."','".$mTransactionTypeId."',".$mDecantQty.",null,'".$mInvRow['location_code']."',null,null,'".$mDocumentReference."','".$mInvRow['date_created']."','".$mCurrentUser."',now(),'".$mCurrentUser."',null,'".$mTransactionTypeId."','',null,".$mInvRow['conversion_factor'].")";
                            if (!mysqli_query($mConnection,$mDataQuery))
                                exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                        }                       
                        $mDataQuery = "UPDATE decant_body SET qty=CASE WHEN qty>".$mDecantQty." THEN qty-".$mDecantQty." ELSE 0 END, last_updated_date=now(), last_updated_by='".$mCurrentUser."' WHERE decant_body.inventory_master_id=".$mInvRow['id']." AND decant_body.decant_header_id=".$mResult." AND decant_body.decant_status=0 LIMIT 1";
                        if (!mysqli_query($mConnection,$mDataQuery))
                            exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                        
                        $mDataQuery = "UPDATE inventory_master SET inventory_qty=CASE WHEN inventory_qty>".$mDecantQty." THEN inventory_qty-".$mDecantQty." ELSE 0 END, available_qty=inventory_qty-allocated_qty-hold_qty, last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mInvRow['id']." LIMIT 1";
                        if (!mysqli_query($mConnection,$mDataQuery))
                            exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                        try
                        {
                            $mDataQuery = "UPDATE decant_body SET inventory_master_id=null WHERE decant_body.id>0 AND inventory_master_id=".$mInvRow['id']." AND decant_status IN(0,2) AND EXISTS(SELECT id FROM inventory_master WHERE id=".$mInvRow['id']." AND inventory_qty=0)";
                            if (!mysqli_query($mConnection,$mDataQuery))
                                exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                        }
                        catch(Exception $e)
                        {
                        }
                        try
                        {
                            $mDataQuery = "DELETE FROM inventory_master WHERE inventory_qty=0 AND id=".$mInvRow['id']." LIMIT 1";
                            if (!mysqli_query($mConnection,$mDataQuery))
                                exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                        }
                        catch(Exception $e)
                        {
                        }
                        $mTransactionTypeId = "DEC_OUT";
                        $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,comment,source_tag,conversion_factor) VALUE(DEFAULT,'".$mInvRow['ran_or_order']."','".$mInvRow['part_number']."','".$mInvRow['serial_reference']."','".$mTransactionTypeId."',".$mDecantQty.",'".$mInvRow['location_code']."',null,null,null,'".$mDocumentReference."',now(),'".$mCurrentUser."',now(),'".$mCurrentUser."',null,'".$mTransactionTypeId."','',null,".$mInvRow['conversion_factor'].")";
                        if (!mysqli_query($mConnection,$mDataQuery))
                            exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                        
                        $mDecantQty = $mDecantQty-$mInvRow['inventory_qty'];
                        if ($mDecantQty <= 0)
                            break;
                    }
                }
                $mDataQuery = "UPDATE decant_body SET decant_status=2 WHERE id=".$mDecantId." LIMIT 1";
                if (!mysqli_query($mConnection,$mDataQuery))
                    exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                $mDataQuery = "SELECT decant_body.id AS id FROM decant_body WHERE decant_header_id =".$mDecantHdrId." AND decant_status=0 AND EXISTS(SELECT id FROM inventory_master WHERE decant_body.inventory_master_id=inventory_master.id) LIMIT 1";
                if (!$mData = mysqli_query($mConnection,$mDataQuery))
                    exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                if (mysqli_num_rows($mData) == 0)
                {
                    $mDataQuery = "UPDATE decant_header SET document_status_id=(SELECT id FROM document_status WHERE lower(document_status_code)='closed') WHERE id=".$mDecantId." LIMIT 1";
                    if (!mysqli_query($mConnection,$mDataQuery))
                        exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
					if ($mTransactionTypeId == "DECANTCASE")
					{
						return "CHGTRAN:DECANTCASE";
					}
					else
					{
						return "OKMS-This decant is complete,DECANT";
					}
                }
                $mRdtMessage = "Serial ".$mSerialReference." Processed";
				if ($mTransactionTypeId == "DECANTCASE")
				{
					return "CHGTRAN:DECANTCASE";
				}
				else
				{
					return "CHGTRAN:DECANT?DATA:documentReferenceinput:".$mDocumentReference.",rdtMessage:".$mRdtMessage;
//                	exit("CHGTRAN:DECANT");
				}
            }
        }       
        return "DATA:allocationId:".$mResult.",documentReferenceinput:".$mDocumentReference.",rdtMessage:".$mRdtMessage;
    }
    else
        return $mErrMsg;
}

// Validate / Process Split Serial
function decantCount($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mFilter,$mConnect="")
{
    $mResult = "";
    $mResponse = "";
	$mBoxCount = 0;
// Set default error message
    $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;
// 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 == "decantid")
            $mDecantId = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = strtoupper($v2);
		elseif ($k2 == "transactiontype")
            $mTransactionTypeId = strtoupper($v2);
		elseif ($k2 == "transactiontypeId")
            $mTransactionTypeId = strtoupper($v2);
    }

//	exit("FAIL-TEST1-".$mFilter." ".$mSerialReference);

// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error()); 
// Check if Decant Header exists
    $mResult = $mDecantId;
    if (is_numeric($mResult))
    {
// Test for existing Decant Reference
		$mDataQuery = "SELECT id, COUNT(id) as box, SUM(qty) as piece FROM decant_body WHERE decant_header_id=".$mDecantId." AND decant_status=1 GROUP BY decant_header_id LIMIT 1";
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			exit("FAIL-".mysqli_error($mConnection).$mDataQuery);
		list($mId,$mBoxCount,$mPieceCount) = mysqli_fetch_row($mData);
	}
	return "DATA:boxCount:".$mBoxCount.",pieceCount:".$mPieceCount;
}
