<?php
// allocation.php
// VIMS RDT Order / Pick Allocation  Process
// ----------------------------------------------------------------------------------------
// Modified 2021-05-06 - Initial version
// ----------------------------------------------------------------------------------------
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Expires: 0');
// Header to allow Cross-Server AJAX Connection
header('Access-Control-Allow-Origin: *');
set_time_limit(0);

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

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

$mErrMsg = "OK  -";

// Get Pick
if (($mFunction != null) && (strtolower($mFunction) == "getpick"))
{
    $mErrMsg = "";
    $mToPLt = "";
    $mTrain = "";
    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if ($k2 == "customerreference")
            $mCustomerRef = trim($v2);
        elseif ($k2 == "documentreference")
            $mDocumentRef = trim($v2);
        elseif ($k2 == "locationcode")
            $mLocationCode = strtoupper(trim($v2));
        elseif ($k2 == "tasktype")
            $mTaskType = strtoupper(trim($v2));
        elseif ($k2 == "aisle")
            $mAisle = trim($v2);
        elseif ($k2 == "train")
            $mTrain = trim($v2);
        elseif ($k2 == "toplt")
            $mToPlt = trim($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper(trim($v2));
    }

    $mStatus = getPick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mTransactionTypeId, $mCustomerRef, $mAisle, $mConnect, $mToPlt, $mDocumentRef, $mTaskType, $mTrain);
    echo $mStatus;
    return;
}

// post pick
elseif (($mFunction != null) && (strtolower($mFunction) == "postpick"))
{
    $mErrMsg = "";
    $mToPlt = "";
    $mToLocHash = "";
    $mPickQty = "";
    $mDocumentRef = "";
    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);

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

    // Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if (strtolower($k2) == "pickdetailid")
            $mPickDtlId = trim($v2);
        elseif (strtolower($k2) == "inventorymasterid")
            $mPickInvId = trim($v2);
        elseif (strtolower($k2) == "plt")
            $mToPlt = trim($v2);
        elseif (strtolower($k2) == "serialreference")
            $mSerialReference = trim($v2);
        elseif ((strtolower($k2) == "quantity") || (strtolower($k2) == "countqty") || (strtolower($k2) == "pickqty"))
            $mPickQty = floatval($v2);
        elseif (strtolower($k2) == "tolocationhashcode")
            $mToLocHash = trim($v2);
        elseif (strtolower($k2) == "customerreference")
            $mCustomerRef = trim($v2);
        elseif (strtolower($k2) == "documentreference")
            $mDocumentRef = trim($v2);
        elseif (strtolower($k2) == "locationcode")
            $mLocationCode = strtoupper(trim($v2));
        elseif (strtolower($k2) == "aisle")
            $mAisle = trim($v2);
        elseif ($k2 == "tasktype")
            $mTaskType = strtoupper(trim($v2));
        elseif ($k2 == "train")
            $mTrain = strtoupper(trim($v2));
        elseif ($k2 == "fromlocationhashcode")
            $mFromLocation = strtoupper(trim($v2));
        elseif ($k2 == "vldpartnumber")
            $mVldPartNumber = strtoupper(trim($v2));
        elseif ($k2 == "vldlocation")
            $mVldLocation = strtoupper(trim($v2));
        elseif ($k2 == "vldserialreference")
            $mVldSerialReference = strtoupper(trim($v2));
        elseif ((strtolower($k2) == "vldqty"))
            $mVldQty = floatval($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionTypeId")
            $mTransactionTypeId = strtoupper(trim($v2));
        elseif ($k2 == "rdtstart")
            $mRdtStart = trim($v2);
        elseif ($k2 == "rdtend")
            $mRdtEnd = trim($v2);
    }

    $mRdtStart = str_replace("colon", ":", $mRdtStart);
    $mRdtEnd = str_replace("colon", ":", $mRdtEnd);

    $mStatus = updatePickDetail($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mSerialReference, $mPickQty, $mCurrentUser, $mTransactionTypeId, $mToPlt, $mToLocHash, $mConnect, $mRdtStart, $mRdtEnd);
    if (substr($mStatus, 0, 2) == "OK")
        $mStatus = getPick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mTransactionTypeId, $mCustomerRef, $mAisle, $mConnect, $mToPlt, $mDocumentRef, $mTaskType, $mTrain);

    echo $mStatus;
    return;
}

// Validate Pick
elseif (($mFunction != null) && ($mFunction == "validatepick"))
{

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

    $mErrMsg = "";
    // Explode Input Parm
// Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    $i = 0;
    $mPartNumber = null;
    $mSerialReference = null;
    //	$mFromLocation = null;
    $mVldLocationHash = "";
    $mVldPartNumber = "";
    $mVldSerialReference = "";
    $mQty = "";
    $mVldQty = "";
    $mOrderQty = "";
    $mCustomerReference = "";
    $mDocumentReference = "";
    $mCurrentUser = "UNKNOWN";
    $mTaskType = "";
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if (strtolower($k2) == "pickdetailid")
            $mPickDtlId = trim($v2);
        elseif (strtolower($k2) == "inventorymasterid")
            $mPickInvId = trim($v2);
        elseif ($k2 == "customerreference")
            $mCustomerReference = strtoupper(trim($v2));
        elseif ($k2 == "documentreference")
            $mDocumentReference = strtoupper(trim($v2));
        elseif ($k2 == "tasktype")
            $mTaskType = strtoupper(trim($v2));
        elseif ($k2 == "partnumber")
            $mPartNumber = strtoupper(trim($v2));
        elseif ($k2 == "serialreference")
            $mSerialReference = strtoupper(trim($v2));
        elseif ($k2 == "fromlocationhashcode")
            $mFromLocation = trim($v2);
        elseif ($k2 == "vldpartnumber")
            $mVldPartNumber = strtoupper(trim($v2));
        elseif ($k2 == "vldlocation")
            $mVldLocation = strtoupper(trim($v2));
        elseif ($k2 == "vldserialreference")
            $mVldSerialReference = strtoupper(trim($v2));
        elseif ((strtolower($k2) == "quantity") || (strtolower($k2) == "countqty"))
            $mQty = floatval($v2);
        elseif ((strtolower($k2) == "vldquantity") || (strtolower($k2) == "vldqty"))
            $mVldQty = floatval($v2);
        elseif ((strtolower($k2) == "orderqty"))
            $mOrderQty = floatval($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper(trim($v2));
        elseif ((strtolower($k2) == "transactiontypeid"))
            $mTransactionTypeId = strtoupper(trim($v2));
    }
    // Check Task Type Settings
    if ($mTransactionTypeId == "PBTTP" && $mTaskType != "")
    {
        // mysql connection
        $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
            exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
        $mDataQuery = "SELECT id FROM location_type WHERE location_type_code LIKE('$mTaskType%') AND is_train=1 LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-1" . mysqli_error($mConnection) . "-" . $mDataQuery);
        if (mysqli_num_rows($mData) == 0)
        {
            mysqli_close($mConnection);
            $mErrMsg = "FAIL-Invalid Task Type for this pick type";
            echo $mErrMsg;
            return;
        }
    }
    $mTransactionTypes = ['PBTP','PBTTP'];
//    if ($mTransactionTypeId == "PBTP" && $mTaskType != "")
    if (in_array($mTransactionTypeId,$mTransactionTypes) && $mTaskType != "")
    {
        // mysql connection
        $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
            exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
        $mDataQuery = "SELECT id FROM location_type WHERE location_type_code LIKE('$mTaskType%') LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-1" . mysqli_error($mConnection) . "-" . $mDataQuery);
        if (mysqli_num_rows($mData) == 0)
        {
            mysqli_close($mConnection);
            $mErrMsg = "FAIL-Invalid Task Type for this pick type";
            echo $mErrMsg;
            return;
        }
    }

    if ($mDocumentReference == "")
    {
        echo "OK  -";
        return;
    }
    //    if (!$mFromLocation)
//        $mFromLocation = 'none';
    if ($mDocumentReference != "")
        $mErrMsg = validatePick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mDocumentReference, $mFromLocation, $mVldLocation, $mPartNumber, $mVldPartNumber, $mSerialReference, $mVldSerialReference, $mQty, $mVldQty, $mTransactionTypeId, $mOrderQty);
    else
        $mErrMsg = validatePick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mCustomerReference, $mFromLocation, $mVldLocation, $mPartNumber, $mVldPartNumber, $mSerialReference, $mVldSerialReference, $mQty, $mVldQty, $mTransactionTypeId, $mOrderQty);
    echo $mErrMsg;
    return;
}

// Test for full-box-pick
elseif (($mFunction != null) && ($mFunction == "checkfullboxpick"))
{
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
    // Set default error message
    $mErrMsg = "";

    $mSerialReference = "";
    $mPartNumber = "";
    $mFullBoxPick = 0;

    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    $i = 0;
    $mSerialReference = "";
    $mPartNumber = "";
    $mFullBoxPick = 0;
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if (strtolower($k2) == "documentreference")
            $mDocumentReference = strtoupper($v2);
        elseif (strtolower($k2) == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif (strtolower($k2) == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper(trim($v2));
    }

    // Check Order Type Settings
    $mDataQuery = "SELECT order_header.id AS id, order_header.document_reference AS document_reference, order_header.time_slot AS time_slot, order_type.order_type_code AS order_type_code, 0 AS full_box_pick FROM order_header LEFT JOIN document_status ON order_header.document_status_id=document_status.id LEFT JOIN order_type ON order_header.order_type=order_type.order_type_code JOIN pick_header ON order_header.pick_header_id=pick_header.id WHERE pick_header.document_reference='" . $mDocumentReference . "' AND (order_header.document_status_id IS null OR order_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated')) LIMIT 50";
    if (!mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-1" . mysqli_error($mConnection) . "-" . $mDataQuery);
    $mData = mysqli_query($mConnection, $mDataQuery);
    if (mysqli_num_rows($mData) == 0)
    {
        $dateTime = DateTime::createFromFormat('Y-m-d H:i:s', $mDocumentReference);
        if ($dateTime !== false && !array_sum($dateTime::getLastErrors()))
        {
            $mDataQuery = "SELECT order_header.id AS id, order_header.document_reference AS document_reference, order_header.time_slot AS time_slot, order_type.order_type_code AS order_type_code, 0 AS full_box_pick FROM order_header LEFT JOIN document_status ON order_header.document_status_id=document_status.id LEFT JOIN order_type ON order_header.order_type=order_type.order_type_code WHERE (order_header.time_slot='" . $mDocumentReference . "' OR CONCAT(substr(time_slot,1,4),substr(time_slot,6,2),substr(time_slot,9,2),' ',substr(time_slot,12,2),substr(time_slot,15,2))='" . $mDocumentReference . "')  AND (order_header.document_status_id IS null OR order_header.document_status_id=0 OR lower(document_status.document_status_code) in('open','allocated'))";
            if (!mysqli_query($mConnection, $mDataQuery))
                exit("FAIL-2" . mysqli_error($mConnection) . "-" . $mDataQuery);
            $mData = mysqli_query($mConnection, $mDataQuery);
        }
    }
    $mPickByLine = 0;
    $mFullBoxPick = 0;
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mOrderType = ($mRow['order_type_code']);
        if ($mRow['full_box_pick'] == 1)
            $mFullBoxPick = ($mRow['full_box_pick']);
    }
    mysqli_close($mConnection);
    // Set Qty screen settings based on full-box flag
    if ($mFullBoxPick == 0)
        if ($mSerialReference == "")
            if ($mTransactionTypeId == "PBTP")
                $mDataStr = "DATA:boxCountQty::HHHHHHHHHH";
            else
                $mDataStr = "DATA:boxCountQty::HHHHHHHHHH";
        elseif ($mPartNumber == "")
            $mDataStr = "DATA:serialReference:" . $mSerialReference . "::skipqtyAllocated,qtyAllocated:";
        else
            $mDataStr = "DATA:partNumber:" . $mPartNumber . "::skipqtyAllocated,qtyAllocated:";
    else
        if ($mSerialReference == "")
            if ($mTransactionTypeId == "PBTP")
                $mDataStr = "DATA:boxCountQty::HHHHHHHHHI";
            else
                $mDataStr = "DATA:boxCountQty::HHHHHHHHHH";
        elseif ($mPartNumber == "")
            $mDataStr = "DATA:serialReference:" . $mSerialReference . "::skipqtyAllocated,qtyAllocated:";
        else
            $mDataStr = "DATA:partNumber:" . $mPartNumber . "::skiptoqtyAllocated,qtyAllocated:";
    echo $mDataStr;
    return;
}

// Pick Pallet Box Count
elseif (($mFunction != null) && ($mFunction == "validatepickboxcount"))
{
    $mQty = "";
    $mReference = "";
    $mPlt = "";
    $mTrys = "3";
    $mErrMsg = "OK  -";
    // Explode Input Parm
// Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    $i = 0;
    $mMarQry = "MARQRY";
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if ($k2 == "reference")
            $mReference = $v2;
        elseif ($k2 == "toplt")
            $mPlt = strtoupper($v2);
        elseif ($k2 == "trys")
            $mTrys = $v2;
        elseif ($k2 == "qty")
            $mQty = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = $v2;
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper($v2);
    }
    // Check Reference
    if (($mReference == "") || (!$mReference))
    {
        echo "FAIL-Invalid Reference";
        return;
    }
    if (($mPlt == "") || (!$mPlt))
    {
        echo "FAIL-Invalid Pallet";
        return;
    }
    // get Pallet / box count
    if (($mErrMsg == "OK  -") && ($mReference != ""))
    {
        // mysql connection
        $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
            exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
        // get Pallet Inventory Count
        $i = 0;
        $mInvCount = 0;
        $mDataQuery = "SELECT count(*) as invcount FROM inventory_master JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE tag.tag_reference = '" . $mPlt . "' AND inventory_master.inventory_qty <> 0 LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-3" . mysqli_error($mConnection) . "-" . $mDataQuery);
        $mData = mysqli_query($mConnection, $mDataQuery);
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mInvCount = (int) ($mRow['invcount']);
            $i++;
        }

        $mPltSts = "";
        // Check Counted Qty against Inventory Sum
        if (($mTrys != "") && ($mQty != $mInvCount))
        {
            $mErrMsg = "FAIL-Wrong Box Count Quantity.";
            if (($mTrys == "0") || ($mTrys == "1"))
            {
                // Set Status to PI
                $mErrMsg = "FAIL-Wrong Box Count";
                $mPltSts = "PI";
                $mErrMsg = "OKMS-Locate Box to Query Area,DATA:toLocationHashCode:" . $mMarQry . ":HHHHHHH*HH,DATA:boxCountQty::HHHHHHHHHH";
            }
            else
            {
                $mPltSts = $mTrys;
            }
        }
        else
        {
            $mPltSts = "OK";
            $mErrMsg = "DATA:toLocationHashCode::HHHHHHH*HH,boxCountQty::HHHHHHHHHH,rdtmessage:";
        }
        $mDataQuery = "UPDATE tag SET created_by='" . $mPltSts . "', last_updated=now() WHERE tag_reference='" . $mPlt . "' LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-4" . mysqli_error($mConnection) . "-" . $mDataQuery);

        mysqli_close($mConnection);
        if ($mErrMsg == "OK  -")
            $mErrMsg = "OK  -boxcount";
    }
    echo $mErrMsg;
    return;
}

// Skip / Resequence Current Pick Task
elseif (($mFunction != null) && ($mFunction == "skippick"))
{
    $mReference = "";
    $mToPlt = "";
    $mTrain = "";
    $mTrys = "0";
    $mErrMsg = "";
    $mDocumentRef = "";
    // 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 :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if (strtolower($k2) == "reference")
            $mReference = $v2;
        elseif (strtolower($k2) == "documentreference")
            $mDocumentRef = $v2;
        elseif (strtolower($k2) == "pickdetailid")
            $mPickDtlId = strtoupper($v2);
        elseif (strtolower($k2) == "locationcode")
            $mLocationCode = strtoupper($v2);
        elseif (strtolower($k2) == "aislecode")
            $mAisle = $v2;
        elseif ($k2 == "tasktype")
            $mTaskType = strtoupper(trim($v2));
        elseif ($k2 == "train")
            $mTrain = strtoupper(trim($v2));
        elseif (strtolower($k2) == "toplt")
            $mToPlt = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = $v2;
        elseif ($k2 == "transactionTypeId")
            $mTransactionTypeId = strtoupper($v2);
        elseif ($k2 == "rdtstart")
            $mRdtStart = trim($v2);
        elseif ($k2 == "rdtend")
            $mRdtEnd = trim($v2);
    }
    $mRdtStart = str_replace("colon", ":", $mRdtStart);
    $mRdtEnd = str_replace("colon", ":", $mRdtEnd);
    if ($mReference == "" && $mDocumentRef == "")
    {
        exit("FAIL-Invalid Reference");
    }

    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());

    // Get id for document status
    $mDocStsId = 0;
    $mDocPkdId = 0;
    $mDataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-5" . mysqli_error($mConnection) . $mDataQuery;
    $mData = (mysqli_query($mConnection, $mDataQuery));
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        if (strtolower($mRow['document_status_code']) == "open")
            $mDocStsId = ($mRow['id']);
        if (strtolower($mRow['document_status_code']) == "picked")
            $mDocPkdId = ($mRow['id']);
    }

    // Update Pick Status
    $mDataQuery = "SELECT pick_detail.document_reference AS document_reference, pick_detail.inventory_master_id AS inventory_master_id, pick_detail.part_id AS part_id, pick_detail.part_number AS part_number,pick_detail.qty_allocated AS qty_allocated, pick_detail.line_no AS line_no, pick_detail.pick_body_id AS pick_body_id, pick_detail.pick_priority AS pick_priority";
    $mDataQuery .= ",pick_body.pick_header_id AS pick_header_id";
    $mDataQuery .= ",CASE WHEN pick_detail.blocked IS null THEN 0 ELSE pick_detail.blocked END AS blocked";
    $mDataQuery .= ",pick_header.order_header_id AS order_header_id, pick_header.customer_reference AS customer_reference";
    $mDataQuery .= " FROM pick_detail";
    $mDataQuery .= " JOIN pick_body ON pick_detail.pick_body_id=pick_body.id";
    $mDataQuery .= " JOIN pick_header ON pick_body.pick_header_id=pick_header.id";
    $mDataQuery .= " WHERE pick_detail.id=" . $mPickDtlId . " LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-6" . mysqli_error($mConnection) . "-" . $mDataQuery);

    $mDocumentRef = "";
    $mInvId = "";
    $mAlcQty = 0;
    $mBlocked = 0;
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mDocumentRef = $mRow['document_reference'];
        $mInvId = $mRow['inventory_master_id'];
        $mAlcQty = $mRow['qty_allocated'];
        $mPickBdyId = $mRow['pick_body_id'];
        $mOrdHdrId = $mRow['order_header_id'];
        $mPickHdrId = $mRow['pick_header_id'];
        $mPickPartId = $mRow['part_id'];
        $mPickPartNumber = $mRow['part_number'];
        $mDtlLineNo = $mRow['line_no'];
        $mBlocked = $mRow['blocked'];
        $mCustomerReferenceCode = $mRow['customer_reference'];
        $mPickDtlPriority = $mRow['pick_priority'];
    }
    if ($mDocumentRef != "")
    {
        $mOrdBdyId = "";
        $mDataQuery = "SELECT order_body_id FROM pick_order_link WHERE pick_detail_id=" . $mPickDtlId;
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-7" . mysqli_error($mConnection) . "-" . $mDataQuery);
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            if ($mOrdBdyId != "")
                $mOrdBdyId .= "," . $mRow['order_body_id'];
            else
                $mOrdBdyId = $mRow['order_body_id'];
        }
        // Set high Priority on Document Detail (sequence)
        if ($mOrdBdyId != "" && $mOrdBdyId != null)
        {
            if ($mBlocked == 0)
                $mDataQuery = "UPDATE order_body SET sequence=CASE WHEN sequence = '' THEN 900 WHEN sequence IS null THEN 900 WHEN sequence < 900 THEN 900 ELSE sequence+1 END WHERE id IN(" . $mOrdBdyId . ")";
            else
                $mDataQuery = "UPDATE order_body SET sequence=999999 WHERE id IN(" . $mOrdBdyId . ")";
            if (!mysqli_query($mConnection, $mDataQuery))
                exit("FAIL-8" . mysqli_error($mConnection) . "-" . $mDataQuery);
        }
        else
        {
            $mDataQuery = "UPDATE order_body SET sequence=CASE WHEN sequence = '' THEN 900 WHEN sequence IS null THEN 900 WHEN sequence < 900 THEN 900 ELSE sequence+1 END WHERE EXISTS(SELECT id FROM pick_order_link WHERE order_body.id=pick_order_link.order_body_id AND pick_order_link.pick_detail_id=" . $mPickDtlId . ")";
        }
        if ($mBlocked == 0)
            $mDataQuery = "UPDATE pick_detail SET pick_priority=CASE WHEN pick_priority < 900 then 900 WHEN pick_priority IS null then 900 ELSE pick_priority+1 END";
        else
            $mDataQuery = "UPDATE pick_detail SET pick_priority=999999, processed=1";
        $mDataQuery .= " ,in_use_by='',last_updated_date=now(),created_by='" . $mCurrentUser . "-SKIP'";
        if ($mRdtStart != "")
            $mDataQuery .= ",rdt_start='" . $mRdtStart . "'";
        if ($mRdtEnd != "")
            $mDataQuery .= ",rdt_end='" . $mRdtEnd . "'";
        $mDataQuery .= " WHERE pick_detail.id='" . $mPickDtlId . "' AND processed=0 LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-9" . mysqli_error($mConnection) . "-" . $mDataQuery);

        // Update Related Blocked Data
        if ($mBlocked == 1)
        {
            /*
                        $mDataQuery = "UPDATE pick_body SET qty_transacted=CASE WHEN qty_transacted<qty_expected THEN qty_transacted+".$mAlcQty." ELSE qty_transacted+0 END";
                        $mDataQuery .= ",qty_allocated=0";
                        $mDataQuery .= ",qty_shortage=qty_expected-qty_transacted";
                        $mDataQuery .= ",processed=CASE WHEN qty_transacted<qty_expected THEN 0 ELSE 1 END";
                        $mDataQuery .= " WHERE id=".$mPickBdyId." LIMIT 1";
                        if (!mysqli_query($mConnection,$mDataQuery))
                            exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                        $mDataQuery = "UPDATE order_body SET qty_transacted=qty_transacted+".$mAlcQty.",difference=qty_transacted-qty_expected,last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mOrdBdyId." LIMIT 1";
                        if (!mysqli_query($mConnection,$mDataQuery))
                            return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
            // Check for Pick Complete
                        $mDataQuery = "SELECT id FROM order_body WHERE order_header_id=".$mOrdHdrId." AND qty_expected>qty_transacted LIMIT 1";
                        if (!$mData = mysqli_query($mConnection,$mDataQuery))
                            exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                        if (mysqli_num_rows($mData) == 0)
                        {
                            $mDataQuery = "UPDATE order_header SET document_status_id=".$mDocPkdId.",last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id = ".$mOrdHdrId." LIMIT 1";
                            if (!mysqli_query($mConnection,$mDataQuery))
                            return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
                            $mDataQuery = "UPDATE pick_header SET document_status_id=".$mDocPkdId.",last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id = ".$mPickHdrId." LIMIT 1";
                            if (!mysqli_query($mConnection,$mDataQuery))
                            return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
                        }
            */
        }

        $mReturn = updatePickHistory($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mInvId, $mAlcQty, $mCurrentUser, "F2SKIP", $mDtlLineNo, "", $mDtlLineNo . "-" . $mPickDtlPriority, $mOrdBdyId);

        // Check for Other outstanding Pick Lines																																		  
        $mDataQuery = "SELECT id FROM pick_detail WHERE document_reference='" . $mDocumentRef . "' AND processed=0 AND pick_priority<900 LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-10" . mysqli_error($mConnection) . "-" . $mDataQuery);
        $mData = mysqli_query($mConnection, $mDataQuery);
        if (mysqli_num_rows($mData) == 0)
        {
            if ($mBlocked == 0)
            {
                // UnAllocate Current Pick tasks
                $mDataQuery = "UPDATE inventory_master SET allocated_qty=CASE WHEN allocated_qty>" . $mAlcQty . " THEN allocated_qty-" . $mAlcQty . " ELSE 0 END, available_qty=inventory_qty-(allocated_qty+hold_qty) WHERE id=" . $mInvId . " LIMIT 1";
                if (!mysqli_query($mConnection, $mDataQuery))
                    exit("FAIL-11" . mysqli_error($mConnection) . "-" . $mDataQuery);

                $mDataQuery = "UPDATE pick_detail SET in_use_by='',qty_allocated=0,processed=1,last_updated_date=now(),created_by='" . $mCurrentUser . "-SKIP'";
                if ($mRdtStart != "")
                    $mDataQuery .= ",rdt_start='" . $mRdtStart . "'";
                if ($mRdtEnd != "")
                    $mDataQuery .= ",rdt_end='" . $mRdtEnd . "'";
                $mDataQuery .= " WHERE pick_detail.id='" . $mPickDtlId . "' AND processed=0 LIMIT 1";
                if (!mysqli_query($mConnection, $mDataQuery))
                    exit("FAIL-12" . mysqli_error($mConnection) . "-" . $mDataQuery);

                $mDataQuery = "UPDATE pick_body SET pick_body.qty_allocated=CASE WHEN qty_allocated<" . $mAlcQty . " THEN 0 ELSE qty_allocated-" . $mAlcQty . " END, pick_body.qty_shortage= CASE WHEN pick_body.qty_allocated>pick_body.qty_expected THEN 0 ELSE pick_body.qty_expected-pick_body.qty_allocated END,last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE pick_body.id=" . $mPickBdyId . " LIMIT 1";
                if (!mysqli_query($mConnection, $mDataQuery))
                    exit("FAIL-13" . mysqli_error($mConnection) . "-" . $mDataQuery);
            }
        }
        // Allocate Next Pick
        $mErrMsg = getPick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mTransactionTypeId, $mReference, $mAisle, $mConnect, $mToPlt, $mDocumentRef, $mTaskType, $mTrain);
    }
    if ($mErrMsg == "")
        exit("OK  -");
    else
        exit($mErrMsg);
}

// Check for Allocation > Expected
elseif (($mFunction != null) && ($mFunction == "checkoverallocate"))
{
    $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 :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if (strtolower($k2) == "allocationid")
            $mPickDtlId = $v2;
        elseif ($k2 == "currentUser")
            $mCurrentUser = $v2;
        elseif ($k2 == "transactionTypeId")
            $mTransactionTypeId = strtoupper($v2);
        elseif ($k2 == "rdtstart")
            $mRdtStart = trim($v2);
        elseif ($k2 == "rdtend")
            $mRdtEnd = trim($v2);
    }
    $mRdtStart = str_replace("colon", ":", $mRdtStart);
    $mRdtEnd = str_replace("colon", ":", $mRdtEnd);

    $mReturn = checkOverAllocate($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mPickDtlId);
    if ($mReturn == "")
    {
        exit("Ok  -checkoverallocate");
    }
    //	exit("FAIL-TEST1 ".$mFilter);
}

// Missing Pick Serial
elseif (($mFunction != null) && ($mFunction == "postwicheck"))
{
    $mErrMsg = "";
    $mFromLocationHash = "";
    $mCustomerReference = "";
    $mAisleCode = "";
    $mToPlt = "";
    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    foreach ($mArray1 as $k1 => $v1)
    {
        list($k2, $v2) = explode(":", $mArray1[$k1]);
        if ($k2 == "plt")
            $mPltNumber = trim($v2);
        elseif (strtolower($k2) == "fromlocationhashcode")
            $mFromLocationHash = trim($v2);
        elseif (strtolower($k2) == "tolocationhashcode")
            $mToLocationHash = trim($v2);
        elseif (strtolower($k2) == "customerreference")
            $mCustomerReference = trim($v2);
        elseif (strtolower($k2) == "aislecode")
            $mAisleCode = trim($v2);
        elseif (strtolower($k2) == "serialreference")
            $mSerialReference = trim($v2);
        elseif (strtolower($k2) == "partnumber")
            $mPartNumber = trim($v2);
        elseif (strtolower($k2) == "allocationid")
            $mPickDtlId = trim($v2);
        elseif (strtolower($k2) == "sequencingrequestid")
            $mPickInvId = trim($v2);
        elseif (strtolower($k2) == "toplt")
            $mToPlt = trim($v2);
        elseif ((strtolower($k2) == "quantity") || (strtolower($k2) == "countqty") || (strtolower($k2) == "qtyallocated"))
            $mPickQty = floatval($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionTypeId")
            $mTransactionTypeId = strtoupper(trim($v2));
    }
    $mStatus = postWiCheck($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mSerialReference, $mPickQty, $mAisleCode, $mCurrentUser, $mTransactionTypeId, $mFromLocationHash, $mToPlt);
    exit($mStatus);
}

// Get task_type list for select
if (($mFunction != null) && (strtolower($mFunction) == "getpicklist"))
{
    //	echo("FAIL-TEST2");
    $mErrMsg = "";
    $mReference = "";
    $mOffset = 0;
    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    foreach ($mArray1 as $k1 => $v1)
    {
        list($k2, $v2) = explode(":", $mArray1[$k1]);
        if ($k2 == "reference")
            $mReference = trim($v2);
        elseif ($k2 == "key")
            $mKey = trim($v2);
        elseif ($k2 == "offset")
            $mOffset = trim($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper(trim($v2));
    }
    $mStatus = getPickList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mReference, $mKey, $mOffset);
    echo $mStatus;
    return;
}

// Get task_type list for select
if (($mFunction != null) && (strtolower($mFunction) == "gettasktypelist"))
{
    //	echo("FAIL-TEST2");
    $mErrMsg = "";
    $mReference = "";
    $mOffset = 0;
    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    foreach ($mArray1 as $k1 => $v1)
    {
        list($k2, $v2) = explode(":", $mArray1[$k1]);
        if ($k2 == "reference")
            $mReference = trim($v2);
        elseif ($k2 == "key")
            $mKey = trim($v2);
        elseif ($k2 == "offset")
            $mOffset = trim($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper(trim($v2));
    }
    $mStatus = getTaskTypeList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mReference, $mKey, $mOffset);
    echo $mStatus;
    return;
}

// Get train list for select
if (($mFunction != null) && (strtolower($mFunction) == "gettrainlist"))
{
    //	echo("FAIL-TEST2");
    $mErrMsg = "";
    $mReference = "";
    $mOffset = 0;
    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    foreach ($mArray1 as $k1 => $v1)
    {
        list($k2, $v2) = explode(":", $mArray1[$k1]);
        if ($k2 == "reference")
            $mReference = trim($v2);
        elseif ($k2 == "key")
            $mKey = trim($v2);
        elseif ($k2 == "offset")
            $mOffset = trim($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper(trim($v2));
    }
    $mStatus = getTrainList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mReference, $mKey, $mOffset);
    echo $mStatus;
    return;
}

// Get train list for select
if (($mFunction != null) && (strtolower($mFunction) == "gettrainloclist"))
{
    //	echo("FAIL-TEST2");
    $mErrMsg = "";
    $mReference = "";
    $mOffset = 0;
    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    foreach ($mArray1 as $k1 => $v1)
    {
        list($k2, $v2) = explode(":", $mArray1[$k1]);
        if ($k2 == "reference")
            $mReference = trim($v2);
		if ($k2 == "tasktype")
            $mTaskType = trim($v2);
        elseif ($k2 == "key")
            $mKey = trim($v2);
        elseif ($k2 == "offset")
            $mOffset = trim($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper(trim($v2));
    }
    $mStatus = getTrainLocList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mReference, $mTaskType, $mKey, $mOffset);
    echo $mStatus;
    return;
}

// Test for full-box-pick
elseif (($mFunction != null) && ($mFunction == "checkfullboxpart"))
{
    //    exit("FAIL-TEST1");
// 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 = "";

    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    $i = 0;
    $mPartNumber = "";
    $mFullBoxPick = 0;
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if (strtolower($k2) == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper(trim($v2));
    }
    $mReturn = checkFullBoxPart($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mPartNumber);
    exit("DATA:rdtMessage:" . $mReturn);
}
elseif (($mFunction != null) && ($mFunction == "changeserial"))
{
    // Set default error message
    $mErrMsg = "";
    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    $i = 0;
    $mSerialReference = "";
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);
    // Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if (strtolower($k2) == "pickdetailid")
            $mPickDtlId = strtoupper($v2);
        elseif (strtolower($k2) == "inventorymasterid")
            $mPickInvId = strtoupper($v2);
        elseif (strtolower($k2) == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif ((strtolower($k2) == "quantity") || (strtolower($k2) == "countqty") || (strtolower($k2) == "qtyallocated"))
            $mPickQty = floatval($v2);
        elseif ($k2 == "currentUser")
            $mCurrentUser = trim($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper(trim($v2));
    }
    // mysql connection - Use Passed values...
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
    // Replace Inventory Serial
    $mDataQuery = "SELECT * FROM inventory_master WHERE id = " . $mPickInvId . " LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
    $mOldSerialReference = $mSerialReference;
    $mInvQty = 0;
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mOldSerialReference = $mRow['serial_reference'];
        $mInvQty = $mRow['inventory_qty'];
        $mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,hold_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) VALUES(DEFAULT,0,0,null," . $mRow['current_location_id'] . ",now(),null," . $mPickQty . ",now(),'" . $mCurrentUser . "',null," . $mRow['part_id'] . ",'" . $mRow['ran_or_order'] . "',0,0,0,'" . $mSerialReference . "',null," . $mRow['conversion_factor'] . ",now(),1,'" . $mRow['part_number'] . "',0,0," . $mPickQty . ",'" . $mCurrentUser . "','" . $mRow['location_code'] . "',null,null," . $mRow['current_location_id'] . "," . $mRow['inventory_status_id'] . ")";
        if (!mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
        $mNewInvId = mysqli_insert_id($mConnection);
    }

    $mDataQuery = "";
    $mDataQuery = "UPDATE inventory_master SET inventory_qty=inventory_qty-" . $mPickQty . ",allocated_qty=CASE WHEN allocated_qty>=" . $mPickQty . " THEN allocated_qty-" . $mPickQty . " ELSE 0 END,available_qty=inventory_qty-allocated_qty,last_updated=now(),last_updated_by='" . $mCurrentUser . "' WHERE inventory_master.id=" . $mPickInvId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
    $mReturn = updatePickHistory($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mPickQty, $mCurrentUser, "SA-OUT", "", "", "", "", $mOldSerialReference, $mSerialReference);
    $mDataQuery = "UPDATE pick_detail SET inventory_master_id=" . $mNewInvId . ",serial_reference='" . $mSerialReference . "',last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE pick_detail.id=" . $mPickDtlId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
    $mReturn = updatePickHistory($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mNewInvId, $mPickQty, $mCurrentUser, "SA-IN", "", "", "", "", $mSerialReference, $mOldSerialReference);
    $mDataQuery = "DELETE FROM inventory_master WHERE inventory_qty<=0 AND id=" . $mPickInvId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
    //    exit("OK  -changeserial");
    $mReturn = "DATA:sequencingRequestId:" . $mNewInvId;
    exit($mReturn);
}

function checkFullBoxPart($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mPartNumber)
{
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());

    $mDataQuery = "SELECT part.full_box_pick AS full_box_pick FROM part WHERE part.part_number='" . $mPartNumber . "' LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-14" . mysqli_error($mConnection) . "-" . $mDataQuery);
    //	exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery." ".mysqli_num_rows($mData));
    list($mFullBoxPick) = mysqli_fetch_row($mData);
    mysqli_close($mConnection);
    if ($mFullBoxPick == 1)
        $mRdtMessage = "Pick the Full Box";
    else
        $mRdtMessage = "";
    return $mRdtMessage;
}
// Check for Over Allocation
function checkOverAllocate($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mPickDtlId)
{
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());

    $mDataQuery = "SELECT pick_body.qty_allocated AS qty_allocated FROM pick_body JOIN pick_detail ON pick_body.id=pick_detail.pick_body_id WHERE pick_detail.id=" . $mPickDtlId . " LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-15" . mysqli_error($mConnection) . "-" . $mDataQuery);
    //	exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery." ".mysqli_num_rows($mData));
    $i = 0;
    $mAlcQty = 0;
    $mDocBdyId = "";
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mAlcQty = $mRow['qty_allocated'];
        $i++;
    }
    $mOrdQty = 0;
    if ($mDocBdyId == "")
    {
        $mDataQuery = "SELECT order_body_id FROM pick_order_link WHERE pick_detail_id=" . $mPickDtlId;
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-16" . mysqli_error($mConnection) . "-" . $mDataQuery);
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            if ($mDocBdyId != "")
                $mDocBdyId .= "," . $mRow['order_body_id'];
            else
                $mDocBdyId = $mRow['order_body_id'];
        }
    }
    if ($i > 0 && $mDocBdyId != "")
    {
        $mDataQuery = "SELECT id, part_number, line_no, qty_expected FROM order_body WHERE order_body.id IN(" . $mDocBdyId . ") LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-17" . mysqli_error($mConnection) . "-" . $mDataQuery);
        $mData = mysqli_query($mConnection, $mDataQuery);
        //		exit("FAIL-".mysqli_error($mConnection)."-".$mDataQuery." ".mysqli_num_rows($mData));
        $i = 0;
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mOrdQty = $mRow['qty_expected'];
            $mPartNumber = $mRow['part_number'];
        }
    }
    //	return "FAIL-Ordered ".$mOrdQty." is less than Allocated ".$mAlcQty;
    if ($mAlcQty > $mOrdQty)
        return "OKMS-Part " . $mPartNumber . " Ordered " . $mOrdQty . " is less than Allocated " . $mAlcQty;
    else
        return "OK  -checkalcqty";
}

// Validate Pick Entries
function validatePick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mDocumentRef, $mFromLocation, $mVldLocation, $mPartNumber, $mVldPartNumber, $mSerialReference, $mVldSerialReference, $mQty, $mVldQty, $mTransactionTypeId, $mOrderQty = "")
{
    //	exit("FAIL-TEST1-".$mSerialReference." ".$mFromLocation);
    $mErrMsg = "";
    $mUnknownSerial = "";
    $mLocData = "";

    //    exit("FAIL-TEST1-".$mFromLocation);

    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());

    $mDataQuery = "SELECT id FROM pick_header WHERE document_reference='" . $mDocumentRef . "' LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-18" . mysqli_error($mConnection) . "-" . $mDataQuery);
    if (mysqli_num_rows($mData) == 0)
    {
        $mErrMsg = "FAIL-Pick Reference is Incorrect";
        return $mErrMsg;
    }
    if ($mFromLocation == "none")
    {
        return "OK  -fromlocation";
    }
    if (($mFromLocation == "") || ($mFromLocation == null))
    {
        //		$mErrMsg = "FAIL-Location is Incorrect-".$mFromLocation;
        $mErrMsg = "OKMS-Location is Incorrect,DATA:fromLocationHashCode:::skipfromLocationHashCode";
        return $mErrMsg;
    }
    if (strpos($mSerialReference, "'") === true)
    {
        $mErrMsg = "FAIL-Serial Contains an invalid value";
        return $mErrMsg;
    }
    $mNoSerialScan = 0;
    if (($mErrMsg == "") && (($mFromLocation != "") || ($mVldLocation != "")))
    {
        $mDataQuery = "SELECT location.location_code AS location_code, location.location_hash AS location_hash";
        $mDataQuery .= ",location_type.no_serial_scan AS no_serial_scan";
        $mDataQuery .= " FROM location";
        $mDataQuery .= " LEFT JOIN location_type ON location.location_type_id=location_type.id";
        $mDataQuery .= " WHERE location.location_hash = '" . $mFromLocation . "' LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-18" . mysqli_error($mConnection) . "-" . $mDataQuery);
        $i = 0;
        list($mLocCode, $mLocHash, $mNoSerialScan) = mysqli_fetch_row($mData);

        if (mysqli_num_rows($mData) > 0)
        {
            $mDataQuery = "SELECT inventory_master.id AS id FROM inventory_master";
            $mDataQuery .= " LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
            $mDataQuery .= " WHERE inventory_master.location_code='" . $mLocCode . "' AND inventory_master.part_number='" . $mVldPartNumber . "' AND inventory_master.inventory_qty-inventory_master.hold_qty-inventory_master.picked_qty>0";
            $mDataQuery .= " AND (inventory_master.inventory_status_id IS null OR (inventory_status.non_pickable<>1 OR lower(inventory_status.inventory_status_code) IN('ok','lost')))";
            $mDataQuery .= " LIMIT 1";
            if (!$mData = mysqli_query($mConnection, $mDataQuery))
                exit("FAIL-18" . mysqli_error($mConnection) . "-" . $mDataQuery);
            if (mysqli_num_rows($mData) == 0)
            {
                $mErrMsg = "FAIL-No available stock of " . $mVldPartNumber . " in this location";
                return $mErrMsg;
            }
            else
            {
                if ($mLocCode != $mVldLocation)
                {
                    //                    $mErrMsg = "DATA:fromLocationHashCodecontent:".$mDataStr.",fromLocationHashCodeinput:".$mFromLocation;
                    $mLocData = "fromLocationHashCodecontent:" . $mLocCode . ",fromLocationHashCodeinput:" . $mFromLocation;
                }
            }
        }
        if (mysqli_num_rows($mData) == 0)
        {
            $mErrMsg = "FAIL-Location is Incorrect-" . $mFromLocation;
            return $mErrMsg;
        }
    }

    // Validate Entered Serial	
    if (($mErrMsg == "") && (!empty($mSerialReference)))
    {
        // Test for Strict Serial
        $mStrictSerial = 0;
        // Check at Company Level		
        if ($mCurrentUser != "")
            $mDataQuery = "SELECT strict_serial_pick FROM company LIMIT 1";
        else
            $mDataQuery = "SELECT strict_serial_pick FROM company WHERE id > 0 LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-20" . mysqli_error($mConnection) . "-" . $mDataQuery);
        $mData = mysqli_query($mConnection, $mDataQuery);
        $i = 0;
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            if ($mRow['strict_serial_pick'] != null)
                $mStrictSerial = ($mRow['strict_serial_pick']);
            $i++;
        }
        if ($mStrictSerial == 1)
        {
            if ($mSerialReference != $mVldSerialReference)
                $mErrMsg = "FAIL-Scanned Serial does not match Prompted Serial ";
        }

        if ($mErrMsg == "")
        {
            $mDataQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, inventory_master.requires_inspection AS inspect, inventory_master.requires_decant AS decant";
            $mDataQuery .= ",inventory_master.hold_qty AS hold_qty, inventory_master.picked_qty AS picked_qty";
            $mDataQuery .= ",part.part_number AS part_number,part.conversion_factor AS conversion_factor,part.full_box_pick AS full_box_pick";
            $mDataQuery .= ",location.location_code AS location_code, location.location_hash AS location_hash";
            $mDataQuery .= ",location_type.no_serial_scan";
            $mDataQuery .= ",inventory_status.non_pickable AS non_pickable,inventory_status.inventory_status_code AS inventory_status_code";
            $mDataQuery .= " FROM inventory_master";
            $mDataQuery .= " JOIN part ON inventory_master.part_id=part.id";
            $mDataQuery .= " LEFT JOIN vendor ON part.vendor_id=vendor.id";
            $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 .= " LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
            $mDataQuery .= " WHERE inventory_master.serial_reference='" . $mSerialReference . "'";
            if ($mPartNumber != "")
                $mDataQuery .= " AND (part.part_number='" . $mPartNumber . "')";
            //            elseif ($mVldPartNumber != "")
//                $mDataQuery .= " AND (part.part_number='".$mVldPartNumber."')";			
            $mDataQuery .= " AND inventory_master.inventory_qty>0";
            //           $mDataQuery .=" AND (inventory_master.inventory_status_id IS null OR (inventory_status.non_pickable<>1 OR lower(inventory_status.inventory_status_code) IN('ok','lost')))";
            $mDataQuery .= " LIMIT 2";
            if (!$mData = mysqli_query($mConnection, $mDataQuery))
                exit("FAIL-21" . mysqli_error($mConnection) . "-" . $mDataQuery);
            //            exit("FAIL-21".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData));
// UNKNOWN Serial processing
            if (mysqli_num_rows($mData) == 0)
            {
                $i = 1;
                $mInvId = 0;
                $mInvQty = $mVldQty;
                $mPartConvFactor = 0;
                $mPartNumber = $mVldPartNumber;
                $mInsp = 0;
                $mDecant = 0;
                $mLocCode = $mVldLocation;
                $mLocHash = $mVldLocation;
                $mUnknownSerial = "Y";
                $mFullBoxPick = 0;
                $mNonPickable = 0;
                $mInvStatus = null;
            }
            else
            {
                $i = 0;
                $mInvId = "";
                $mInvQty = 0;
                $mPartConvFactor = 1;
                $mHoldQty = 9;
                while ($mRow = mysqli_fetch_assoc($mData))
                {
                    if ($i == 0)
                    {
                        $mLocCode = ($mRow['location_code']);
                        $mLocHash = ($mRow['location_hash']);
                        $mInspect = ($mRow['inspect']);
                        $mDecant = ($mRow['decant']);
                        $mInvId = ($mRow['id']);
                        $mInvQty = ($mRow['inventory_qty'] - $mRow['picked_qty']);
                        $mPartConvFactor = ($mRow['conversion_factor']);
                        $mPartNumber = ($mRow['part_number']);
                        $mFullBoxPick = ($mRow['full_box_pick']);
                        $mNonPickable = ($mRow['non_pickable']);
                        $mInvStatus = ($mRow['inventory_status_code']);
                        $mHoldQty = ($mRow['hold_qty']);
                        // Apply Conversion Factor
                        if ($mPartConvFactor > 0)
                        {
                            $mInvQty = $mInvQty / $mPartConvFactor;
                            $mHoldQty = $mHoldQty / $mPartConvFactor;
                        }
                    }
                    $i++;
                }
            }
            // Test Part Number
            if (mysqli_num_rows($mData) == 1)
            {
                if ($mPartNumber != $mVldPartNumber)
                {
                    $mErrMsg = "FAIL-Incorrect Serial / Part Number";
                    return $mErrMsg;
                }
            }
            if ($i == 0)
            {
                $mErrMsg = "FAIL-Serial is Incorrect-" . $mSerialReference;
                return $mErrMsg;
            }
            if ($i == 1)
            {
                if ($mInvQty <= 0)
                {
                    $mErrMsg = "FAIL-Scanned Serial is unavailable";
                }
                elseif ($mInvQty <= $mHoldQty)
                {
                    $mErrMsg = "FAIL-Scanned Serial is on-hold";
                }
                elseif ($mInspect == "1")
                {
                    $mErrMsg = "FAIL-Serial is flagged for inspection";
                }
                elseif ($mDecant == "1")
                {
                    $mErrMsg = "FAIL-Serial is flagged for decant";
                }
                elseif ($mLocCode != $mVldLocation && $mLocHash != $mVldLocation)
                {
                    $mErrMsg = "FAIL-Scanned Serial is in a different Location " . $mLocCode . " " . $mVldLocation;
                }
                elseif ($mNonPickable == 1)
                {
                    $mErrMsg = "FAIL-Scanned Serial is Not-Pickable";
                }
                elseif (strtolower($mInvStatus) == "picked")
                {
                    $mErrMsg = "FAIL-Scanned Serial is unavailable";
                }
                if ($mInvQty < $mVldQty)
                {
                    $mVldQty = $mInvQty;
                }
                if ($mErrMsg == "")
                {
                    if ($mSerialReference != $mVldSerialReference)
                    {
                        if ($mFullBoxPick == 1)
                            $mErrMsg = "DATA:serialReferencecontent:" . $mSerialReference . "::,serialReferenceinput:" . $mSerialReference . "::,qtyAllocated:" . $mVldQty;
                        else
                            $mErrMsg = "DATA:serialReferencecontent:" . $mSerialReference . "::,serialReferenceinput:" . $mSerialReference . "::,qtyAllocated:" . $mVldQty . "::skipqtyAllocated";
                    }
                }
            }
            // Multiple Serials - Prompt for Part Number
            if ($i > 1)
            {
                if ($mTransactionTypeId == "PBTM")
                    $mErrMsg = "DATA:partNumber::HHHPPIPPPP:skippartNumber";
                else
                    $mErrMsg = "DATA:partNumber::HHHPPIPPPP:skippartNumber";
            }
        }
    }

    //	if (($mSerialReference=="" || $mSerialReference==null) && $mNoSerialScan=1)
//        $mSerialReference=$mVldSerialReference;
    if ($mErrMsg == "")
    {
        //        return "FAIL-TEST-".$mFromLocation."-".$mNoSerialScan."-".$mSerialReference."-".$mQty;
        if ($mNoSerialScan == 0)
        {
            if (empty($mSerialReference))
            {
                $mErrMsg = "DATA:serialReference:::skipserialReference";
                //               $mErrMsg = "DATA:serialReferenceinput:".$mSerialReference."::skip7";
            }
        }
        else
        {
            if (!$mQty || $mQty == null || $mQty == "")
            {
                //                return "FAIL-TEST-".$mFromLocation."-".$mNoSerialScan."-".$mSerialReference."-".$mQty."-".$mVldSerialReference;
                if (empty($mSerialReference))
                {
                    if (($mVldSerialReference != null) && ($mVldSerialReference != ""))
                    {
                        $mErrMsg = "DATA:serialReferenceinput:" . $mVldSerialReference . "::,qtyAllocated:" . $mVldQty . "::skipqtyAllocated";
                        return $mErrMsg;
                    }
                    else
                    {
                        $mErrMsg = "DATA:serialReference:::skipserialReference";
                        return $mErrMsg;
                    }
                }
                else
                {
                    $mErrMsg = "DATA:serialReferenceinput:" . $mSerialReference . "::,qtyAllocated:" . $mVldQty . "::skipqtyAllocated";
                    return $mErrMsg;
                }
            }
        }
    }
    // Check Scanned Part against pick
    if (($mErrMsg == "") && ($mPartNumber != ""))
    {
        if (trim($mPartNumber) != trim($mVldPartNumber))
        {
            $mDataQuery = "SELECT id AS id, part_number AS part_number FROM part WHERE upper(part_number) = '" . $mPartNumber . "' OR (upper(part_number)='" . $mVldPartNumber . "' AND upper(jis_supply_group)='" . $mPartNumber . "') LIMIT 1";
            if (!mysqli_query($mConnection, $mDataQuery))
                exit("FAIL-19" . mysqli_error($mConnection) . "-" . $mDataQuery);
            $mData = mysqli_query($mConnection, $mDataQuery);
            $i = 0;
            while ($mRow = mysqli_fetch_assoc($mData))
            {
                $mPartNumber = $mRow['part_number'];
                $i++;
            }

            if (($i == 0) || (strtoupper($mPartNumber) != $mVldPartNumber))
                $mErrMsg = "FAIL-Incorrect Part Number";
        }
    }

    if ($mErrMsg == "")
    {
        // Check Scanned Qty.
        if ($mQty != "")
        {
            if (is_numeric($mQty))
            {
                //				if (($mFullBoxPick == 0) && ($mQty > $mVldQty))
                if ($mQty <= 0)
                    $mErrMsg = "FAIL-Scanned quantity is incorrect";
            }
            else
            {
                $mErrMsg = "FAIL-This must be a number only";
            }
            if ($mErrMsg == "")
            {
                // Check Scanned Qty against Serial
                if ($mQty > $mInvQty)
                    $mErrMsg = "FAIL-Scanned quantity is greater than serial quantity";
            }
            if (($mErrMsg == "") && ($mOrderQty != ""))
            {
                if (($mQty > $mOrderQty))
                    $mErrMsg = "FAIL-Scanned quantity is greater than order quantity";
            }
        }
    }
    // Test for Piece (non-full-box) pick
    if ($mErrMsg == "")
    {
        if ($mNoSerialScan == 1)
            $mFullBoxPick = 0;
        if (($mSerialReference != "") && ($mQty == ""))
        {
            if ($mFullBoxPick == 1)
            {
                if ($mVldQty > $mInvQty)
                {
                    $mErrMsg = "DATA:sequencingRequestId:" . $mInvId . ",serialReference:" . $mSerialReference . ",qtyAllocated:::skipqtyAllocated";
                }
                else
                {
                    $mErrMsg = "DATA:sequencingRequestId:" . $mInvId . ",qtyAllocatedvalue:" . $mVldQty;
                }
            }
            else
            {
                if ($mPartNumber == "")
                {
                    if ($mNoSerialScan == 1)
                        $mErrMsg = "DATA:sequencingRequestId:" . $mInvId . ",serialReferenceinput:" . $mSerialReference . "::skipqtyAllocated";
                    else
                        $mErrMsg = "DATA:sequencingRequestId:" . $mInvId . ",serialReference:" . $mSerialReference . ",qtyAllocated:::skipqtyAllocated";
                }
                else
                {
                    if ($mNoSerialScan == 1)
                        $mErrMsg = "DATA:sequencingRequestId:" . $mInvId . ",partNumber:" . $mPartNumber . ",qtyAllocated:::skipqtyAllocated";
                    else
                        $mErrMsg = "DATA:sequencingRequestId:" . $mInvId . ",partNumber:" . $mPartNumber . ",qtyAllocated:::skipqtyAllocated";
                }
            }
        }
    }

    mysqli_close($mConnection);
    if (substr($mErrMsg, 0, 2) != "FA")
    {
        if (!empty($mLocData))
        {
            if (substr($mErrMsg, 0, 4) == "DATA")
            {
                $mErrMsg .= "," . $mLocData;
            }
            else
            {
                $mErrMsg = "DATA:" . $mLocData;
            }
        }
    }
    if ($mErrMsg == "")
    {
        $mErrMsg = "OK  -Valid Pick";
    }
    return $mErrMsg;
}

// Get Next Pick
function getPick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mTransactionType, $mCustomerReferenceCode, $mAisle, $mConnect, $mToPlt = "", $mDocumentRef, $mTaskType, $mTrain)
{
    $mReturn = "";
    if ($mAisle == "NONE")
    {
        return "OK  -";
    }
    if (($mCustomerReferenceCode == null || $mCustomerReferenceCode == "") && ($mDocumentRef == null || $mDocumentRef == ""))
    {
        return "FAIL-noPickRef";
    }

    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();

    // Order Header
    $mReturn = "";
    if ($mDocumentRef != null && $mDocumentRef != "")
    {
        $mDataQuery = "SELECT pick_header.id AS id, pick_header.document_reference AS document_reference, pick_header.time_slot AS time_slot, pick_header.pick_type AS pick_type FROM pick_header LEFT JOIN document_status ON pick_header.document_status_id=document_status.id WHERE document_reference='" . $mDocumentRef . "' AND (pick_header.document_status_id IS null OR lower(document_status.document_status_code) in('open','allocated')) LIMIT 1";
    }
    else
    {
        $mDataQuery = "SELECT order_header.id AS id, order_header.document_reference AS document_reference, order_header.time_slot AS time_slot, order_header.order_type AS order_type, order_header.order_type AS pick_type FROM order_header LEFT JOIN document_status ON order_header.document_status_id=document_status.id LEFT JOIN order_type ON order_header.order_type=order_type.order_type_code WHERE customer_reference='" . $mCustomerReferenceCode . "' AND (order_header.document_status_id IS null OR lower(document_status.document_status_code) in('open','allocated')) LIMIT 1";
    }
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-26" . mysqli_error($mConnection) . "-" . $mDataQuery;

    if (mysqli_num_rows($mData) == 0)
    {
        if ($mTransactionType != "")
        {
            if ($mToPlt != "")
            {
                return "OKMS-No Pick Tasks,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
            }
            else
            {
                return "OKMS-No Pick Tasks,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
            }
        }
        else
        {
            $mTransactionType = "PBTP";
            return "OKMS-No Pick Tasks " . $mTransactionType . ",CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
        }
    }
    $mOrderId = "";
    $mTimeSlot = "";
    $mPickByLine = 0;
    $mFullBoxPick = 0;
    $mOrderType = "";
    $mPickType = "";
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mOrderId = ($mRow['id']);
        $mDocumentReferenceCode = ($mRow['document_reference']);
        $mOrderType = ($mRow['order_type']);
        $mPickType = ($mRow['pick_type']);
        $mTimeSlot = ($mRow['time_slot']);
        $mFullBoxPick = ($mRow['full_box_pick']);
    }
    // validate order_type
    if ($mOrderId != "")
    {
        if ($mOrderType != "")
        {
            $mDataQuery = "SELECT id FROM order_type WHERE order_type_code = '" . $mOrderType . "' LIMIT 1";
            if (!mysqli_query($mConnection, $mDataQuery))
                return "FAIL-27" . mysqli_error($mConnection) . "-" . $mDataQuery;
            $mData = mysqli_query($mConnection, $mDataQuery);
            if (mysqli_num_rows($mData) == 0)
                return "FAIL-Invalid Order Type";
        }
    }

    if ($mOrderId == "")
    {
        $mReturn = "";
        if ($mDocumentRef != null && $mDocumentRef != "")
        {
            $mDataQuery = "SELECT pick_header.id AS id, pick_header.document_reference AS document_reference, pick_header.time_slot AS time_slot, pick_header.pick_type AS pick_type,document_status.document_status_code AS document_status_code FROM pick_header LEFT JOIN document_status ON pick_header.document_status_id=document_status.id WHERE document_reference='" . $mDocumentRef . "' LIMIT 1";
        }
        else
        {
            $mDataQuery = "SELECT order_header.id AS id, order_header.order_type AS order_type, order_header.order_type AS pick_type,document_status.document_status_code AS document_status_code FROM order_header LEFT JOIN order_type ON order_header.order_type=order_type.order_type_code LEFT JOIN document_status ON pick_header.document_status_id=document_status.id WHERE customer_reference='" . $mCustomerReferenceCode . "' LIMIT 1";
        }
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-28" . mysqli_error($mConnection) . "-" . $mDataQuery;
        $mData = mysqli_query($mConnection, $mDataQuery);
        if (mysqli_num_rows($mData) == 0)
            return "FAIL-Invalid Pick Reference";

        while ($mRow = mysqli_fetch_assoc($mData))
        {
            if (strtolower($mRow['document_status_code']) != "open")
            {
                if ($mTransactionType == "")
                    $mTransactionType = "PBTP";
                return "OKMS-This Order is Complete,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
            }
            $mOrderType = ($mRow['order_type']);
            $mPickType = ($mRow['pick_type']);
            if ($mTransactionType != "")
            {
                if ($mToPlt != "")
                {
                    return "OKMS-Pick Complete,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
                }
                else
                {
                    return "OKMS-No Pick Tasks,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
                }
            }
            else
            {
                if ($mTransactionType == "")
                    $mTransactionType = "PBTP";
                return "OKMS-No Pick Tasks,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
            }
        }
        $mReturn = "DATA:EOD-This Pick is Complete";
        return $mReturn;
    }

    if ($mPickByLine < 0)
        $mPickByLine = 0;
    if ($mFullBoxPick < 0)
        $mFullBoxPick = 0;

    if ($mReturn == "")
    {
        // Server Allocation
        $mAlcReturn = "";
        if ($mDocumentRef != null && $mDocumentRef != "")
        {
            $mDataQuery = "SELECT id FROM pick_header WHERE document_reference = '" . $mDocumentRef . "' LIMIT 1";
        }
        else
        {
            $mDataQuery = "SELECT id FROM pick_header WHERE customer_reference = '" . $mCustomerReferenceCode . "' LIMIT 1";
        }
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-29" . mysqli_error($mConnection) . "-" . $mDataQuery;

        $mAlcReturn = "FAIL-Allocation Error";
        if (mysqli_num_rows($mData) == 0)
        {
            $mPickHdrId = 0;
            //            $mAlcReturn = allocateServerOrderLines($mConnect,$mCustomerReferenceCode,$mCurrentUser,"");
//            if (substr($mAlcReturn,0,2)=="FA")
//            {
//            }
        }
        else
        {
            list($mPickHdrId) = mysqli_fetch_row($mData);
        }

        // Pick Header
        if ($mDocumentRef != null && $mDocumentRef != "")
            $mReturn = addUpdatePick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mAutoCreateDoc = null, $mDocumentRef, $mPickHdrId, $mTimeSlot);
        else
            $mReturn = addUpdatePick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mAutoCreateDoc = null, $mCustomerReferenceCode, $mPickHdrId, $mTimeSlot);

        if (substr($mReturn, 0, 4) == "FAIL")
            return $mReturn;
        else
        {
            $mPickHdrId = $mReturn;
            $mDataQuery = "SELECT order_body.id AS id,order_body.part_id AS part_id,order_body.zone_destination AS zone_destination";
            $mDataQuery .= " FROM order_body";
            $mDataQuery .= " LEFT JOIN part ON order_body.part_id=part.id";
            $mDataQuery .= " LEFT JOIN location_type ON part.fixed_location_type_id=location_type.id";
            $mDataQuery .= " WHERE order_body.qty_expected>order_body.qty_transacted";
            $mDataQuery .= " AND EXISTS(SELECT id FROM order_header WHERE order_body.order_header_id=order_header.id AND order_header.pick_header_id=" . $mPickHdrId . ")";
            if ($mTaskType != "ALL" && $mTaskType != "" && $mTaskType != null)
                $mDataQuery .= " AND location_type.task_group='" . $mTaskType . "'";
            $mDataQuery .= " LIMIT 1";
            if (!$mData = mysqli_query($mConnection, $mDataQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
            //            return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData);
            list($mOrdBdyId, $mOrdPartId, $mToLoc) = mysqli_fetch_row($mData);
            if (mysqli_num_rows($mData) > 0)
            {
                $mDataQuery = "SELECT order_body.id AS id FROM order_body";
                $mDataQuery .= " LEFT JOIN part ON order_body.part_id=part.id";
                $mDataQuery .= " LEFT JOIN location_type ON part.fixed_location_type_id=location_type.id";
                $mDataQuery .= " WHERE EXISTS(SELECT id FROM order_header WHERE order_body.order_header_id=order_header.id AND order_header.pick_header_id=" . $mPickHdrId . ")";
                $mDataQuery .= " AND order_body.part_id=" . $mOrdPartId . " AND order_body.zone_destination='" . $mToLoc . "'";
                if ($mTaskType != "ALL" && $mTaskType != "" && $mTaskType != null)
                    $mDataQuery .= " AND location_type.task_group='" . $mTaskType . "'";
                if (!$mData = mysqli_query($mConnection, $mDataQuery))
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                $mOrdBdyIds = "";
                while ($mRow = mysqli_fetch_assoc($mData))
                {
                    if ($mOrdBdyIds != "")
                        $mOrdBdyIds .= "," . $mRow['id'];
                    else
                        $mOrdBdyIds = $mRow['id'];
                }
                $mAlcReturn = allocateOrderLines($mHost, $mUser, $mPwd, $mDbName, $mOrderId, $mPickHdrId, $mCurrentUser, $mAisle, $mOrdBdyIds, $mTaskType);
                //                $mAlcReturn = allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderId,$mPickHdrId,$mCurrentUser,"ALL");
            }
            else
            {
                $mAlcReturn = "FAIL-No Outstanding Order Lines " . $mDataQuery;
            }
            if (substr($mAlcReturn, 0, 2) == "FA")
            {
                return $mAlcReturn;
            }
            // If Server allcation failed - try RDT allocate.
/*            
            if (substr($mAlcReturn,0,2)=="FA")
            {
                try
                {
                    $mAlcReturn = allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderId,$mPickHdrId,$mCurrentUser,"ALL");
                }
                catch(Exception $e)
                {
                    return "FAIL-System Error";
                }
            }
*/
            // Loop Through Returned Pick Header Ids
            $mReturn = getPickDetail($mHost, $mUser, $mPwd, $mDbName, $mOrderId, $mPickHdrId, $mAisle, $mCurrentUser, $mDocumentRef, $mPickByLine, $mFullBoxPick, $mToPlt, $mTaskType, $mTrain);
            if ($mReturn == "")
                $mReturn = "FAIL-Allocation Error";
            if ($mReturn == "DATA:EOD-No Outstanding Order Lines")
            {
                if ($mTransactionType != "")
                {
                    if ($mToPlt != "")
                        return "CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
                    else
                        return "OKMS-No Orders to Allocate,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
                }
                return $mReturn;
            }
            if ($mReturn == "DATA:EOD-This Pick is Complete")
            {
                // Hard-coding for Kanban Pick
                if ($mTransactionType != "")
                {
                    if ($mToPlt != "")
                        return "CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
                    else
                        return "OKMS-No Pick Tasks to Allocate,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
                }
                return $mReturn;
            }
            elseif (substr($mReturn, 0, 8) == "DATA:EOD")
            {
                $mReturn = allocateOrderLines($mHost, $mUser, $mPwd, $mDbName, $mOrderId, $mPickHdrId, $mCurrentUser, "", "", $mTaskType);
                if (substr($mReturn, 0, 5) != "FAIL-")
                {
                    if (substr($mReturn, 0, 8) != "DATA:EOD")
                        $mReturn = getPickDetail($mHost, $mUser, $mPwd, $mDbName, $mOrderId, $mPickHdrId, $mAisle, $mCurrentUser, $mDocumentRef, $mPickByLine, $mFullBoxPick, $mToPlt, $mTaskType, $mTrain);
                    if (substr($mReturn, 0, 8) == "DATA:EOD")
                    {
                        /*
                        // Check for unprocessed Picks	
                                                $mDataQuery = "SELECT id AS id,order_body_id AS order_body_id FROM pick_body WHERE pick_body.pick_header_id=".$mPickHdrId." AND processed=0 LIMIT 1";
                                                if (!$mData = mysqli_query($mConnection,$mDataQuery))
                                                    return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
                                                if (mysqli_num_rows($mData) > 0)
                                                {
                                                    $mRow = mysqli_fetch_assoc($mData);
                                                    $mReturn = allocateOrderLines($mHost,$mUser,$mPwd,$mDbName,$mOrderId, $mPickHdrId, $mCurrentUser,"",$mRow['order_body_id']);
                                                    $mReturn = getPickDetail($mHost,$mUser,$mPwd,$mDbName,$mOrderId,$mPickHdrId,$mAisle,$mCurrentUser,$mDocumentRef,$mPickByLine,$mFullBoxPick,$mToPlt);
                                                    if (substr($mReturn,0,8) != "DATA:EOD")
                                                    {
                                                        mysqli_close($mConnection);
                                                        return $mReturn;
                                                    }
                                                }
                        */
                        // Do in-use pick tasks exist?
                        $mDataQuery = "SELECT pick_detail.id AS id, pick_detail.in_use_by AS in_use_by FROM pick_detail JOIN pick_body ON pick_detail.pick_body_id=pick_body.id";
                        $mDataQuery .= " WHERE pick_body.pick_header_id=" . $mPickHdrId . " AND pick_detail.in_use_by <> '' AND pick_detail.in_use_by<>'" . $mCurrentUser . "' AND (pick_detail.last_updated_date >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)) LIMIT 1";
                        if (!$mData = mysqli_query($mConnection, $mDataQuery))
                            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                        if (mysqli_num_rows($mData) > 0)
                        {
                            $mRow = mysqli_fetch_assoc($mData);
                            if ($mTransactionType == "")
                                $mTransactionType = "PBTP";
                            mysqli_close($mConnection);
                            return "OKMS-The next Pick Task is locked to " . $mRow['in_use_by'] . ",CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
                        }
                        // Hard-coding for Kanban Pick
                        if ($mTransactionType != "")
                        {
                            if ($mToPlt != "")
                                return "OKMS-No Stock for this pick,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
                            else
                                return "OKMS-No Stock for this pick,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL,toPlt:" . $mToPlt;
                        }
                        else
                        {
                            if ($mTransactionType == "")
                                $mTransactionType = "PBTP";
                            return "OKMS-No Stock for this pick,CHGTRAN:" . $mTransactionType . "?DATA:customerReference:,aisleCode:ALL";
                        }
                    }
                    elseif ($mReturn == "")
                        $mReturn = "FAIL-Allocation Error";
                }
            }
        }
        mysqli_close($mConnection);
        return $mReturn;
    }

    $mReturn = "FAIL-EOD-No Matching Customer Reference";
    mysqli_close($mConnection);
    return $mReturn;
}

// Update Pick Header
function addUpdatePick($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mAutoCreateDoc, $mDocumentRef, $mPickHdrId, $mTimeSlot)
{
    $mAutoCreateDoc = 1;
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();

    // Create Pick if non exists	
    if ($mAutoCreateDoc != "0")
    {
        // Pick_Header
        $mDataQuery = "SELECT id,document_reference FROM pick_header WHERE pick_header.id=" . $mPickHdrId . " LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;

        $mDocumentRef = "";
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mDocumentRef = ($mRow['document_reference']);
        }

        // No Pick_header
        if (mysqli_num_rows($mData) == 0)
        {
            if ($mDocumentRef == "")
                $mDocumentRef = "PCK999999";
            $mDataQuery = "Insert INTO pick_header (id, document_reference, customer_reference, order_header_id, document_status_id, date_created, created_by, last_updated_date, last_updated_by) VALUES(DEFAULT,'" . $mDocumentRef . "','" . $mCustomerReferenceCode . "'," . $mOrderHdrId . ",1,now(),'" . $mCurrentUser . "',now(),'" . $mCurrentUser . "')";
            if (!mysqli_query($mConnection, $mDataQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
            $mPickHdrId = mysqli_insert_id($mConnection);
            // Create new Pick Document_Ref.
            $mDocumentRef = "000000" . round($mPickHdrId);
            $mDocumentRef = "PCK" . substr($mDocumentRef, -6);
            $mDataQuery = "UPDATE pick_header SET document_reference='" . $mDocumentReferenceCode . "', document_status_id=(SELECT document_status.id FROM document_status WHERE lower(document_status.document_status_code)='open') WHERE id=" . $mPickHdrId . " LIMIT 1";
            if (!mysqli_query($mConnection, $mDataQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        }
        else
        {
            $mDataQuery = "UPDATE pick_header SET last_updated_date=now(), last_updated_by='" . $mCurrentUser . "' WHERE id=" . $mPickHdrId . " LIMIT 1";
            if (!mysqli_query($mConnection, $mDataQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        }

        $mDataQuery = "UPDATE order_header SET order_header.document_status_id = (SELECT id FROM document_status WHERE lower(document_status.document_status_code)='allocated'), order_header.last_updated_date=now(),order_header.last_updated_by='" . $mCurrentUser . "' WHERE order_header.pick_header_id=" . $mPickHdrId;
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    }
    else
    {
        $mDataQuery = "SELECT id AS id FROM pick_header WHERE pick_header_id=" . $mPickHdrId . " LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        $mPickHdrId = "";
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            if ($mPickHdrId != "")
                $mPickHdrId .= ",";
            $mPickHdrId .= ($mRow['id']);
        }
    }
    mysqli_close($mConnection);
    return $mPickHdrId;
}

function readPickDetail($mHost, $mUser, $mPwd, $mDbName, $mOrderHdrId, $mPickHdrId, $mAisle, $mTaskType, $mTrain, $mCurrentUser)
{
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();
    // loop until available detail is found
    $mRetry = "Y";
    while ($mRetry == "Y")
    {
        $mJsonArray = array();
        // Get available Pick Body / Detail
        $mDataQuery = "SELECT pick_detail.id AS id, pick_detail.inventory_master_id AS invId, pick_detail.qty_allocated AS qty_allocated, pick_detail.pick_body_id AS pick_body_id, pick_detail.line_no AS line_no, pick_detail.pick_priority AS pick_priority";
        $mDataQuery .= ", pick_detail.line_no AS our_line_no";
        $mDataQuery .= ", pick_detail.zone_destination AS zone";
        $mDataQuery .= ", part.part_number AS part_number, part.rdt_prompt AS rdt_prompt";
        $mDataQuery .= ", inventory_master.serial_reference AS serial_reference, inventory_master.date_created AS date_created";
        $mDataQuery .= ", location.location_code AS location_code, location.location_pick_seq";
        $mDataQuery .= ", location_type.no_serial_scan AS no_serial_scan";
        $mDataQuery .= " FROM pick_detail";
        $mDataQuery .= " JOIN pick_body ON pick_detail.pick_body_id=pick_body.id";
        $mDataQuery .= " LEFT JOIN part ON pick_detail.part_id=part.id";
        $mDataQuery .= " LEFT JOIN inventory_master ON pick_detail.inventory_master_id=inventory_master.id";
        $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";
        // Test for muliple Pick Header Ids (timeslot selection)
        $i = strpos($mPickHdrId, ",");
        if ($i > 0)
            $mDataQuery .= " WHERE pick_body.pick_header_id IN(" . $mPickHdrId . ")";
        else
            $mDataQuery .= " WHERE pick_body.pick_header_id=" . $mPickHdrId;
        $mDataQuery .= " AND (pick_detail.processed=0 OR pick_detail.processed IS null) AND (pick_detail.in_use_by IS null OR pick_detail.in_use_by='' OR pick_detail.in_use_by='" . $mCurrentUser . "' OR (pick_detail.last_updated_date < DATE_SUB(NOW(), INTERVAL 5 MINUTE)))";
        if (($mAisle == "") || ($mAisle == null))
            $mDataQuery .= " AND (location.aisle_code='' OR location.aisle_code IS null)";
        elseif ($mAisle != "ALL")
            $mDataQuery .= " AND location.aisle_code='" . $mAisle . "'";
        if ($mTaskType != "ALL" && $mTaskType != "" && $mTaskType != null)
            $mDataQuery .= " AND location_type.task_group='" . $mTaskType . "'";
        if ($mTrain != null && $mTrain != "")
            $mDataQuery .= " AND EXISTS(SELECT id FROM zone_destination WHERE pick_detail.zone_destination=zone_destination.zone_destination AND EXISTS(SELECT id FROM trolley WHERE zone_destination.trolley_id=trolley.id AND EXISTS(SELECT id FROM train WHERE trolley.train_id=train.id AND train='" . $mTrain . "')))";
        if ($mPickByLine == 1)
            $mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.pick_priority, pick_detail.pick_sequence, location.location_pick_seq,pick_detail.location_code,pick_detail.serial_reference LIMIT 1";
        else
            $mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.pick_priority, pick_detail.pick_sequence, location.location_pick_seq, pick_detail.location_code,pick_detail.serial_reference LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-1" . mysqli_error($mConnection) . "-" . $mDataQuery;
        if (mysqli_num_rows($mData) == 0)
        {
            $mDataQuery = "SELECT pick_detail.id AS id, pick_detail.inventory_master_id AS invId, pick_detail.pick_body_id AS pick_body_id, pick_detail.qty_allocated AS qty_allocated, pick_detail.qty_scanned AS qty_scanned, pick_detail.part_number AS part_number, pick_detail.serial_reference AS serial_reference, pick_detail.location_code AS location_code, pick_detail.line_no AS our_line_no, part.rdt_prompt AS rdt_prompt";
            $mDataQuery .= ", location.location_pick_seq AS location_pick_seq";
            $mDataQuery .= ", location_type.no_serial_scan AS no_serial_scan";
            $mDataQuery .= ", pick_detail.zone_destination AS zone";
            $mDataQuery .= " FROM pick_detail";
            $mDataQuery .= " JOIN pick_body ON pick_detail.pick_body_id=pick_body.id";
            $mDataQuery .= " JOIN part ON pick_detail.part_id=part.id";
            //            $mDataQuery .= " LEFT JOIN location ON pick_detail.location_code=location.location_code";
            $mDataQuery .= " LEFT JOIN location ON part.fixed_location_id=location.id";
            $mDataQuery .= " LEFT JOIN location_type ON part.fixed_location_type_id=location_type.id";
            // Test for muliple Pick Header Ids (timeslot selection)
            $i = strpos($mPickHdrId, ",");
            if ($i > 0)
                $mDataQuery .= " WHERE pick_body.pick_header_id IN(" . $mPickHdrId . ")";
            else
                $mDataQuery .= " WHERE pick_body.pick_header_id=" . $mPickHdrId;
            $mDataQuery .= " AND (pick_detail.processed=0 OR pick_detail.processed IS null) AND (pick_detail.in_use_by IS null OR pick_detail.in_use_by='' OR pick_detail.in_use_by='" . $mCurrentUser . "' OR (pick_detail.last_updated_date < DATE_SUB(NOW(), INTERVAL 5 MINUTE)))";
            if (($mAisle != "ALL") && ($mAisle != ""))
                $mDataQuery .= " AND location.aisle_code='" . $mAisle . "'";
            if ($mTaskType != "ALL" && $mTaskType != "" && $mTaskType != null)
                $mDataQuery .= " AND location_type.task_group='" . $mTaskType . "'";
            if ($mTrain != null && $mTrain != "")
                $mDataQuery .= " AND EXISTS(SELECT id FROM zone_destination WHERE pick_detail.zone_destination=zone_destination.zone_destination AND EXISTS(SELECT id FROM trolley WHERE zone_destination.trolley_id=trolley.id AND EXISTS(SELECT id FROM train WHERE trolley.train_id=train.id AND train='" . $mTrain . "')))";
            if ($mPickByLine == 1)
                $mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.pick_priority, pick_detail.pick_sequence,location.location_pick_seq,pick_detail.location_code,pick_detail.serial_reference LIMIT 1";
            else
                $mDataQuery .= " ORDER BY pick_body.pick_header_id, pick_detail.pick_priority, pick_detail.pick_sequence,location.location_pick_seq,pick_detail.location_code,pick_detail.serial_reference LIMIT 1";
            if (!$mData = mysqli_query($mConnection, $mDataQuery))
                return "FAIL-2" . mysqli_error($mConnection) . "-" . $mDataQuery;
        }
        //        print_r ("FAIL-TEST4-".$mDataQuery."-".mysqli_error($mConnection)."-".mysqli_num_rows($mData));
        if (mysqli_num_rows($mData) == 0)
        {
            $mRetry = "";
        }
        else
        {
            while ($mRow = mysqli_fetch_assoc($mData))
            {
                $mInvId = 0;
                $mDataQuery = "SELECT Inventory_master.id AS id, inventory_master.location_code AS location_code, inventory_master.inventory_qty AS inventory_qty";
                $mDataQuery .= " FROM inventory_master";
                $mDataQuery .= " WHERE inventory_master.id=" . $mRow['invId'];
                $mDataQuery .= " LIMIT 1";
                if (!$mInvData = mysqli_query($mConnection, $mDataQuery))
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                if (mysqli_num_rows($mInvData) > 0)
                {
                    list($mInvId, $mInvLoc, $mInvQty) = mysqli_fetch_row($mInvData);
                }

                if ($mInvId = 0 || $mInvQty < $mRow['qty_allocated'] || $mRow['qty_allocated'] <= 0)
                {
                    $mInvQuery = "UPDATE pick_detail SET qty_allocated=0,processed=1,last_updated_date=now(),last_updated_by='" . $mCurrentUser . "_UNAVAIL' WHERE id=" . $mRow['id'] . " LIMIT 1";
                    if (!mysqli_query($mConnection, $mInvQuery))
                        return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
                    $mInvQuery = "UPDATE inventory_master SET allocated_qty=0,available_qty=inventory_qty-hold_qty,last_updated=now(),last_updated_by='" . $mCurrentUser . "_UNAVAIL' WHERE id=" . $mRow['invId'] . " LIMIT 1";
                    if (!mysqli_query($mConnection, $mInvQuery))
                        return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;

                    $mPickBdyId = $mRow['pick_body_id'];
                    if ($mPickBdyId != "")
                    {
                        $mInvQuery = "UPDATE pick_body SET qty_allocated=qty_allocated-" . $mRow['qty_allocated'] . " WHERE pick_body.id=" . $mPickBdyId . " LIMIT 1";
                        if (!mysqli_query($mConnection, $mInvQuery))
                            return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
                    }
                    //                    $mOrdBdyIds = $mRow['order_body_id'];
                    $mOrdBdyIds = "";
                    if ($mOrdBdyIds == "")
                    {
                        $mInvQuery = "SELECT order_body_id FROM pick_order_link WHERE pick_detail_id=" . $mRow['id'];
                        if (!$mInvData = mysqli_query($mConnection, $mInvQuery))
                            return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
                        while ($mInvRow = mysqli_fetch_assoc($mInvData))
                        {
                            if ($mOrdBdyIds != "")
                                $mOrdBdyIds .= "," . $mInvRow['order_body_id'];
                            else
                                $mOrdBdyIds = $mInvRow['order_body_id'];
                        }
                    }
                    $mReturn = allocateOrderLines($mHost, $mUser, $mPwd, $mDbName, $mOrderHdrId, $mPickHdrId, $mCurrentUser, "", $mOrdBdyIds, $mTaskType);
                    break;
                }
                $mOrdBdyQty = 0;
                if ($mOrdBdyIds != "")
                {
                    $mInvQuery = "SELECT sum(order_body.qty_expected) AS order_qty";
                    $mInvQuery .= " FROM order_body ";
                    $mInvQuery .= " JOIN order_header ON order_body.order_header_id=order_header.id";
                    $mInvQuery .= " WHERE order_header.pick_header_id=" . $mPickHdrId . " AND order_body.id IN(" . $mOrdBdyId . ")";
                    if (!$mInvData = mysqli_query($mConnection, $mInvQuery))
                        return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
                    list($mOrdBdyQty) = mysqli_fetch_row($mInvData);
                }
                $mToLocCode = null;
                $mInvQuery = "SELECT location.location_code FROM location JOIN order_type ON location.id=order_type.fixed_to_location_id JOIN order_header ON order_type.order_type_code=order_header.order_type WHERE order_header.pick_header_id=" . $mPickHdrId . " LIMIT 1";
                if (!$mInvData = mysqli_query($mConnection, $mInvQuery))
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
                list($mToLocCode) = mysqli_fetch_row($mInvData);
                $mRow['location_code'] = $mInvLoc;
                $mRow['to_location_code'] = $mToLocCode;
                $mRow['order_qty'] = $mOrdBdyQty;
                $mJsonArray[] = $mRow;
                $mRetry = "";
                // Exit Loop it Blocked Task		
                if ($mRow['blocked'] == 1)
                {
                    $mRetry = "";
                    break;
                }
            }
        }
    }
    return $mJsonArray;
}

function getPickDetail($mHost, $mUser, $mPwd, $mDbName, $mOrderHdrId, $mPickHdrId, $mAisle, $mCurrentUser, $mCustomerReferenceCode, $mPickByLine = 0, $mFullBoxPick = 0, $mToPlt = "", $mTaskType = "", $mTrain = "")
{
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();

    $mJsonArray = array();
    $mRow = array();
    $mReturn = "";
    $mPartId = "";
    $mRdtPrompt = "";
    $mZone = "";

    $mPickDtlId = "";
    $mPickBdyId = "";
    $mPickInvId = "";
    $mPickQty = 0;
    $mFullBoxPick = 0;
    $mPartNumber = "";
    $mRdtPrompt = "";
    $mSerialReference = "";
    $mLocationCode = "";
    $mPickDtlLine = "";
    $mPickOrdLine = "";
    $mBlocked = 0;
    $mOrdBdyId = null;
    $mZone = "";
    $mNoSerialScan = 0;
    $mToLocCode = "";

    // Get next Pick Detail
    $mJsonArray = readPickDetail($mHost, $mUser, $mPwd, $mDbName, $mOrderHdrId, $mPickHdrId, $mAisle, $mTaskType, $mTrain, $mCurrentUser);
    //    print_r ("FAIL-TEST4-".$mJsonArray);
    if (is_string($mJsonArray) && substr($mJsonArray, 0, 2) == 'FA')
    {
        echo ("FAIL-Allocation Error-" . $mJsonArray);
        return;
    }
    foreach ($mJsonArray as $mRow)
    {
        $mPickDtlId = ($mRow['id']);
        $mPickDtlLine = ($mRow['pick_priority']);
        $mPickBdyId = ($mRow['pick_body_id']);
        $mPickInvId = ($mRow['invId']);
        $mPickQty = ($mRow['qty_allocated']);
        $mScanQty = ($mRow['qty_scanned']);
        $mOrdBdyQty = ($mRow['order_qty']);
        $mPartNumber = ($mRow['part_number']);
        $mRdtPrompt = ($mRow['rdt_prompt']);
        $mSerialReference = ($mRow['serial_reference']);
        $mLocationCode = ($mRow['location_code']);
        $mConversionFactor = 1;
        $mZone = ($mRow['zone']);
        $mNoSerialScan = ($mRow['no_serial_scan']);
        $mToLocCode = ($mRow['to_location_code']);
    }
    // No Valid Allocation found
    if ($mPickDtlId == "")
    {
        $mDataQuery = "SELECT id FROM pick_body WHERE pick_header_id =" . $mPickHdrId . " LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        if (mysqli_num_rows($mData) == 0)
            return ("DATA:EOD-No Pick Tasks");

        if (($mTransactionType == "") || ($mTransactionType == null) || (strlen($mTransactionType) == 0))
            $mTransactionType = "PBTP";

        $mDataQuery = "SELECT id FROM pick_body WHERE pick_header_id =" . $mPickHdrId . " AND processed=0 LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;

        //		return "FAIL-TEST1".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData);

        if (mysqli_num_rows($mData) == 0)
            $mReturn = "DATA:EOD-This Pick is Complete";
        else
        {
            $mReturn = "DATA:EOD-No Stock for this Pick";
        }
    }
    else
    {
        $mDataQuery = "UPDATE pick_detail SET pick_detail.in_use_by='" . $mCurrentUser . "',date_created=now(),last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE pick_detail.id=" . $mPickDtlId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        if ($mPickQty != 0 && $mConversionFactor != 0)
            $mPickQty = $mPickQty / $mConversionFactor;
        // Check Full Box Part
        $mRdtPrompt = "";
        $mRdtPrompt = checkFullBoxPart($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mPartNumber);
        // return Pick Details to the screen
        if ($mBlocked == 0)
        {
            if ($mNoSerialScan == 1)
            {
                if ($mToPlt == "")
                    $mReturn = "DATA:aisleCode:" . $mAisle . ",fromLocationHashCodecontent:" . $mLocationCode . ",serialReferencevalue:" . $mSerialReference . ",partNumbervalue:" . $mPartNumber . ",qtyAllocatedvalue:" . $mPickQty . ",orderQtyvalue:" . $mOrdBdyQty . ",allocationId:" . $mPickDtlId . ",sequencingRequestId:" . $mPickInvId . ",zone:" . $mZone . ",boxCountQty:,toLocationHashCode:" . $mToLocCode . ",wiCode:,rdttime:start,rdtMessage:" . $mRdtPrompt;
                else
                    $mReturn = "DATA:aisleCode:" . $mAisle . ",fromLocationHashCodecontent:" . $mLocationCode . ",serialReferencevalue:" . $mSerialReference . ",partNumbervalue:" . $mPartNumber . ",qtyAllocatedvalue:" . $mPickQty . ",orderQtyvalue:" . $mOrdBdyQty . ",allocationId:" . $mPickDtlId . ",sequencingRequestId:" . $mPickInvId . ",zone:" . $mZone . ",boxCountQty:,toLocationHashCode:" . $mToLocCode . ",wiCode:,rdttime:start,rdtMessage:" . $mRdtPrompt;
            }
            else
            {
                if ($mToPlt == "")
                    $mReturn = "DATA:aisleCode:" . $mAisle . ",fromLocationHashCodecontent:" . $mLocationCode . ",serialReferencecontent:" . $mSerialReference . ",partNumbervalue:" . $mPartNumber . ",qtyAllocatedvalue:" . $mPickQty . ",orderQtyvalue:" . $mOrdBdyQty . ",allocationId:" . $mPickDtlId . ",sequencingRequestId:" . $mPickInvId . ",zone:" . $mZone . ",boxCountQty:HHHHHHHHHI,toLocationHashCode:" . $mToLocCode . ",wiCode:,rdttime:start,rdtMessage:" . $mRdtPrompt;
                else
                    $mReturn = "DATA:aisleCode:" . $mAisle . ",fromLocationHashCodecontent:" . $mLocationCode . ",serialReferencecontent:" . $mSerialReference . ",partNumbervalue:" . $mPartNumber . ",qtyAllocatedvalue:" . $mPickQty . ",orderQtyvalue:" . $mOrdBdyQty . ",allocationId:" . $mPickDtlId . ",sequencingRequestId:" . $mPickInvId . ",zone:" . $mZone . ",boxCountQty::HHHHHHHHHI,toLocationHashCode:" . $mToLocCode . ",wiCode::HHHHHHH,rdttime:start,rdtMessage:" . $mRdtPrompt;
            }
        }
        else
        {
            $mRdtPrompt = "This task is BLOCKED - F2 To Skip";
            if ($mToPlt == "")
                $mReturn = "DATA:aisleCode:" . $mAisle . ",fromLocationHashCodecontent:BLOCKED:HHPPPPHHHH,serialReferencecontent:BLOCKED:HHPPPPHHHH,partNumbervalue:" . $mPartNumber . ",qtyAllocatedvalue:" . $mPickQty . ",orderQtyvalue:" . $mOrdBdyQty . ",allocationId:" . $mPickDtlId . ",sequencingRequestId:" . $mPickInvId . ",zone:" . $mZone . ",boxCountQty::HHHHHHH,toLocationHashCode:" . $mToLocCode . ":HHHHHHHHHH,wiCode::HHHHHHHHHH,rdttime:start,rdtMessage:" . $mRdtPrompt;
            else
                $mReturn = "DATA:aisleCode:" . $mAisle . ",fromLocationHashCodecontent:BLOCKED:HHPPPPHHHH,serialReferencecontent:BLOCKED:HHPPPPHHHH,partNumbervalue:" . $mPartNumber . ",qtyAllocatedvalue:" . $mPickQty . ",orderQtyvalue:" . $mOrdBdyQty . ",allocationId:" . $mPickDtlId . ",sequencingRequestId:" . $mPickInvId . ",zone:" . $mZone . ",boxCountQty::HHHHHHH,toLocationHashCode:" . $mToLocCode . ":HHHHHHHHHH,wiCode::HHHHHHHHHH,rdttime:start,rdtMessage:" . $mRdtPrompt;
        }
        // Check if pick line is overallocated
        if (substr($mReturn, 0, 2) == "DA")
        {
            //			$mErrMsg = checkOverAllocate($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mPickDtlId);
//			if (substr($mErrMsg,0,4) == "OKMS")
//			{
//				$mReturn = $mErrMsg.",".$mReturn;
//			}
//			elseif(substr($mErrMsg,0,2)=="FA")
//				$mReturn = $mErrMsg;
        }
    }
    mysqli_close($mConnection);
    return $mReturn;
}

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

    // POST Operation
    try
    {
        // Replace default Server Tomcat Service

        //		$mConnect = "http://172.16.1.70:8081/vantec/";

        // Get Server Data from URL
        $mJsonArray = array();
        $mJsonArray['customerReference'] = $mCustomerReferenceCode;
        $mJsonArray['userId'] = $mCurrentUser;
        // Convert Array to Json Object
        try
        {
            $mJsonObject = json_encode($mJsonArray, JSON_FORCE_OBJECT);
        }
        catch (Exception $e)
        {
            exit("FAIL-Json Error");
        }
        // Replace default Server Tomcat Service
        $mConnectp = str_replace(":8081/vantec", ":8095/pick", $mConnect);
        if ($mConnectp == $mConnect)
        {
            $e = strpos($mConnect, ":8081");
            $mConnectp = substr($mConnect, 0, $e) . ":8095/pick/";
        }
        $mCustomerReferenceCode = str_replace(' ', '+', $mCustomerReferenceCode);
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $mConnectp . "allocate?customerReference=" . $mCustomerReferenceCode . "&userId=" . $mCurrentUser);
        curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json'));
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $mJsonObject);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        $mResult = curl_exec($ch);
        $mErrors = curl_error($ch);
        $mResponse = curl_getinfo($ch, CURLINFO_HTTP_CODE);

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

    $mErrMsg = str_replace("&", "|AND|", $mErrMsg);
    return $mErrMsg;
}

// Allocate Outstanding Order Lines
function allocateOrderLines($mHost, $mUser, $mPwd, $mDbName, $mOrdHdrId, $mPickHdrId, $mCurrentUser, $mAll = "", $mOrdBdyId = null, $mTaskType = "", $mTrain = "")
{
    $mReturn = "";
    $mAlcInvId = "";
    $mReqQty = 0;
    $mJsonArray = array();
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();

    // Pick Header
    $mDataQuery = "SELECT pick_header.document_reference AS document_reference, pick_header.customer_reference AS customer_reference FROM pick_header WHERE id=" . $mPickHdrId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    $mData = mysqli_query($mConnection, $mDataQuery);
    $mPickDocumentReference = "";
    $mPickCustomerReference = "";
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mPickDocumentReference = ($mRow['document_reference']);
        $mPickCustomerReference = ($mRow['customer_reference']);
    }
    // Get Unallocated Order Body
    if ($mOrdBdyId != null)
    {
        $mDataQuery = "SELECT order_body.id AS id, order_body.qty_expected AS order_qty, order_body.line_no AS priority, order_body.line_no AS line_no, order_body.zone_destination AS to_loc";
        $mDataQuery .= ",CASE WHEN order_body.qty_transacted IS null THEN 0 ELSE order_body.qty_transacted END AS qty_transacted, order_body.qty_expected AS qty_expected";
        $mDataQuery .= ",CASE WHEN order_body.blocked IS null THEN 0 ELSE order_body.blocked END AS blocked";
        $mDataQuery .= ",part.id AS part_id,part.part_number AS part_number, part.vendor_part_code AS vendor_part_code";
        $mDataQuery .= " FROM order_body ";
        $mDataQuery .= " JOIN order_header ON order_body.order_header_id=order_header.id";
        $mDataQuery .= " JOIN part ON order_body.part_number=part.part_number";
        $mDataQuery .= " WHERE order_header.pick_header_id=" . $mPickHdrId . " AND order_body.id IN(" . $mOrdBdyId . ")";
        if ($mTrain != null && $mTrain != "")
            $mDataQuery .= " AND EXISTS(SELECT id FROM zone_destination WHERE order_body.zone_destination=zone_destination.zone_destination AND EXISTS(SELECT id FROM trolley WHERE zone_destination.trolley_id=trolley.id AND EXISTS(SELECT id FROM train WHERE trolley.train_id=train.id AND train='" . $mTrain . "')))";
        $mDataQuery .= " ORDER BY order_body.part_number,order_body.zone_destination,order_body.id LIMIT 50";
    }
    elseif ($mPickByLine == "1")
    {
        $mDataQuery = "SELECT order_body.id AS id, order_body.qty_expected AS order_qty, order_body.line_no AS priority, order_body.line_no AS line_no, order_body.zone_destination AS to_loc";
        $mDataQuery .= ",CASE WHEN order_body.qty_transacted IS null THEN 0 ELSE order_body.qty_transacted END AS qty_transacted, order_body.qty_expected AS qty_expected";
        $mDataQuery .= ",CASE WHEN order_body.blocked IS null THEN 0 ELSE order_body.blocked END AS blocked";
        $mDataQuery .= ",part.id AS part_id,part.part_number AS part_number, part.vendor_part_code AS vendor_part_code";
        $mDataQuery .= " FROM order_body ";
        $mDataQuery .= " JOIN order_header ON order_body.order_header_id=order_header.id";
        $mDataQuery .= " JOIN part ON order_body.part_number=part.part_number";
        $mDataQuery .= " LEFT JOIN location_type ON part.fixed_location_type_id=location_type.id";
        $mDataQuery .= " WHERE order_header.pick_header_id=" . $mPickHdrId;
        $mDataQuery .= " AND (order_body.qty_expected>order_body.qty_transacted OR order_body.qty_transacted IS null)";
        if ($mTaskType != "ALL" && $mTaskType != "" && $mTaskType != null)
            $mDataQuery .= " AND location_type.task_group='" . $mTaskType . "'";
        if ($mTrain != null && $mTrain != "")
            $mDataQuery .= " AND EXISTS(SELECT id FROM zone_destination WHERE order_body.zone_destination=zone_destination.zone_destination AND EXISTS(SELECT id FROM trolley WHERE zone_destination.trolley_id=trolley.id AND EXISTS(SELECT id FROM train WHERE trolley.train_id=train.id AND train='" . $mTrain . "')))";
        $mDataQuery .= " ORDER BY order_body.part_number,order_body.zone_destination,order_body.id LIMIT 50";
    }
    else
    {
        $mDataQuery = "SELECT order_body.id AS id, order_body.qty_expected AS order_qty, order_body.line_no AS priority, order_body.line_no AS line_no, order_body.zone_destination AS to_loc";
        $mDataQuery .= ",CASE WHEN order_body.qty_transacted IS null THEN 0 ELSE order_body.qty_transacted END AS qty_transacted, order_body.qty_expected AS qty_expected";
        $mDataQuery .= ",CASE WHEN order_body.blocked IS null THEN 0 ELSE order_body.blocked END AS blocked";
        $mDataQuery .= ",part.id AS part_id,part.part_number AS part_number, part.vendor_part_code AS vendor_part_code";
        $mDataQuery .= " FROM order_body ";
        $mDataQuery .= " JOIN order_header ON order_body.order_header_id=order_header.id";
        $mDataQuery .= " JOIN part ON order_body.part_number=part.part_number";
        $mDataQuery .= " LEFT JOIN location_type ON part.fixed_location_type_id=location_type.id";
        $mDataQuery .= " WHERE order_header.pick_header_id=" . $mPickHdrId;
        $mDataQuery .= " AND (order_body.qty_expected>order_body.qty_transacted OR order_body.qty_transacted IS null)";
        $mDataQuery .= " AND EXISTS(SELECT id FROM inventory_master WHERE part.id=inventory_master.part_id AND (inventory_master.inventory_qty-(inventory_master.allocated_qty+inventory_master.hold_qty))>0)";
        if ($mTaskType != "ALL" && $mTaskType != "" && $mTaskType != null)
            $mDataQuery .= " AND location_type.task_group='" . $mTaskType . "'";
        if ($mTrain != null && $mTrain != "")
            $mDataQuery .= " AND EXISTS(SELECT id FROM zone_destination WHERE order_body.zone_destination=zone_destination.zone_destination AND EXISTS(SELECT id FROM trolley WHERE zone_destination.trolley_id=trolley.id AND EXISTS(SELECT id FROM train WHERE trolley.train_id=train.id AND train='" . $mTrain . "')))";
        $mDataQuery .= " ORDER BY order_body.part_number,order_body.zone_destination,order_body.id LIMIT 50";
    }
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    if (($mPickByLine == "1") && (mysqli_num_rows($mData) == 0))
    {
        $mDataQuery = "SELECT order_body.id AS id, order_body.qty_expected AS order_qty, order_body.line_no AS priority, order_body.line_no AS line_no, order_body.zone_destination AS to_loc";
        $mDataQuery .= ",CASE WHEN order_body.qty_transacted IS null THEN 0 ELSE order_body.qty_transacted END AS qty_transacted, order_body.qty_expected AS qty_expected";
        $mDataQuery .= ",CASE WHEN order_body.blocked IS null THEN 0 ELSE order_body.blocked END AS blocked";
        $mDataQuery .= ",part.id AS part_id,part.part_number AS part_number, part.vendor_part_code AS vendor_part_code";
        $mDataQuery .= " FROM order_body ";
        $mDataQuery .= " JOIN order_header ON order_body.order_header_id=order_header.id";
        $mDataQuery .= " JOIN part ON order_body.part_number=part.part_number";
        $mDataQuery .= " LEFT JOIN location_type ON part.fixed_location_type_id=location_type.id";
        $mDataQuery .= " WHERE order_header.pick_header_id=" . $mPickHdrId;
        $mDataQuery .= " AND (order_body.qty_expected>order_body.qty_transacted OR order_body.qty_transacted IS null)";
        $mDataQuery .= " AND EXISTS(SELECT id FROM inventory_master WHERE part.id=inventory_master.part_id AND (inventory_master.inventory_qty-(inventory_master.allocated_qty+inventory_master_hold_qty))>0)";
        if ($mTaskType != "ALL" && $mTaskType != "" && $mTaskType != null)
            $mDataQuery .= " AND location_type.task_group='" . $mTaskType . "'";
        if ($mTrain != null && $mTrain != "")
            $mDataQuery .= " AND EXISTS(SELECT id FROM zone_destination WHERE order_body.zone_destination=zone_destination.zone_destination AND EXISTS(SELECT id FROM trolley WHERE zone_destination.trolley_id=trolley.id AND EXISTS(SELECT id FROM train WHERE trolley.train_id=train.id AND train='" . $mTrain . "')))";
        $mDataQuery .= " ORDER BY order_body.part_number,order_body.zone_destination,order_body.id LIMIT 50";
    }
    $mStart = date('Y-m-d H:i:s');
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    //	return "FAIL-TEST3 ".$mDataQuery."-".mysqli_num_rows($mData);

    // Process Order Body
    $mBdyId = "";
    $mBdyQty = 0;
    $mBdyOrderQty = 0;
    $mBdyQtyExpected = 0;
    $mBdyQtyTransacted = 0;
    $mBdyPartId = "";
    $mBdyPartNumber = "";
    $mBdyLineNo = 1;
    $mPriority = 1;
    $mVendorPart = "";
    $mDtlSerialRef = "";
    $mBlocked = 0;
    $mToLoc = "";
    if (mysqli_num_rows($mData) == 0)
    {
        $mReturn = "DATA:EOD-No Outstanding Order Lines";
    }

    if ($mReturn == "")
    {
        $mInvTime = "";
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $i++;
            $mBdyId = ($mRow['id']);
            $mBdyPartId = ($mRow['part_id']);
            $mBdyOrderQty = ($mRow['order_qty']);
            $mBdyQtyExpected = ($mRow['order_qty']);
            $mBdyQtyTransacted = ($mRow['qty_transacted']);
            $mBdyPartNumber = ($mRow['part_number']);
            $mBdyLineNo = ($mRow['line_no']);
            $mVendorPart = ($mRow['vendor_part_code']);
            $mDtlSerialRef = ($mRow['serial_reference']);
            $mPickBdyId = ($mRow['pick_body_id']);
            $mPriority = ($mRow['priority']);
            $mBlocked = ($mRow['blocked']);
            $mToLoc = ($mRow['to_loc']);

            // If order_body has no part_id find matching part record
            if (($mRow['part_id']) == null)
            {
                $mPartQuery = "SELECT id AS id, part_number AS part_number, vendor_part_code AS vendor_part_code FROM part WHERE part_number LIKE('%" . $mRow['part_number'] . "%' LIMIT 1";
                if (!mysqli_query($mConnection, $mPartQuery))
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mPartQuery;
                $mPartData = mysqli_query($mConnection, $mPartQuery);
                while ($mPartRow = mysqli_fetch_assoc($mPartData))
                {
                    $mBdyPartId = ($mRow['id']);
                    $mBdyPartNumber = ($mRow['part_number']);
                    $mVendorPart = ($mRow['vendor_part_code']);
                }
            }

            if ($mInvPickPriority < 1)
                $mInvPickPriority = 1;
            if ($mPriority < 1)
                $mPriority = 1;
            if ($mBdyLineNo < 1)
                $mBdyLineNo = 1;

            if (($mBdyPartId != "") && ($mBdyId != ""))
            {
                // Outs. Qty
                $mBdyQty = $mBdyQtyExpected - $mBdyQtyTransacted;
                // Ignore blocked Documents
                if ($mPriority == 999999)
                    $mBdyQty = 0;
                // find available inventory
                if ($mBdyQty > 0)
                {
                    // Check for existing pick_body
                    $mPickQuery = "SELECT pick_body.id AS id, pick_body.qty_expected AS qty_expected,pick_body.qty_allocated AS qty_allocated FROM pick_body WHERE pick_body.pick_header_id=" . $mPickHdrId . " AND pick_body.part_id=" . $mBdyPartId . " AND pick_body.zone_destination='" . $mToLoc . "' AND pick_body.processed<>1 LIMIT 1";
                    if (!$mPickData = mysqli_query($mConnection, $mPickQuery))
                        return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
                    if (mysqli_num_rows($mPickData) == 0)
                    {
                        $mOrdExpectedQty = 0;
                        $mPickQuery = "SELECT sum(qty_expected) FROM order_body WHERE order_body.part_id=" . $mBdyPartId . " AND order_body.zone_destination='" . $mToLoc . "' AND EXISTS(SELECT id FROM order_header WHERE order_body.order_header_id=order_header.id AND order_header.pick_header_id=" . $mPickHdrId . ") LIMIT 1";
                        if (!$mPickData = mysqli_query($mConnection, $mPickQuery))
                            return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
                        list($mOrdExpectedQty) = mysqli_fetch_row($mPickData);
                        $mPickQuery = "INSERT INTO pick_body (id,part_id,part_number,pick_header_id,customer_reference,document_reference,qty_expected,qty_transacted,qty_allocated,qty_shortage,processed,to_location_code,zone_destination,date_created,created_by,last_updated_date,last_updated_by)";
                        $mPickQuery .= " VALUES(DEFAULT," . $mBdyPartId . ",'" . $mBdyPartNumber . "'," . $mPickHdrId . ",'" . $mPickCustomerReference . "','" . $mPickDocumentReference . "'," . $mOrdExpectedQty . ",0,0,0,0,'" . $mToLoc . "','" . $mToLoc . "',now(),'" . $mCurrentUser . "_RDT',now(),'" . $mCurrentUser . "')";
                        if (!mysqli_query($mConnection, $mPickQuery))
                            return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
                        $mPickBdyId = mysqli_insert_id($mConnection);
                        $mPickBdyQtyExpected = $mOrdExpectedQty;
                        $mPickBdyQtyAllocated = 0;
                    }
                    else
                    {
                        $mPickRow = mysqli_fetch_assoc($mPickData);
                        $mPickBdyId = $mPickRow['id'];
                        $mPickBdyQtyExpected = $mPickRow['qty_expected'];
                        $mPickBdyQtyAllocated = $mPickRow['qty_allocated'];
                    }

                    if ($mPickBdyQtyExpected > $mPickBdyQtyAllocated)
                    {
                        $mAlcInvId = "";
                        $mInvQty = 0;
                        $mInvAllocatedQty = 0;
                        $mInvSerialReference = "";
                        $mInvRanOrder = "";
                        $mInvLocationCode = "";
                        $mInvPickPriority = 999;

                        $mJsonArray = readInventory($mHost, $mUser, $mPwd, $mDbName, $mBdyPartId, $mBdyQty);
                        if (is_string($mJsonArray) && substr($mJsonArray, 0, 2) == 'FA')
                        {
                            echo ("FAIL-Allocation Error-" . $mJsonArray);
                            return;
                        }

                        for ($i = 0; $i < count($mJsonArray); $i++)
                        {
                            $mAlcInvId = $mJsonArray[$i]['id'];
                            $mBdyPartNumber = $mJsonArray[$i]['part_number'];
                            $mInvSerialReference = $mJsonArray[$i]['serial_reference'];
                            $mInvAllocatedQty = $mJsonArray[$i]['qty'];
                            $mInvLocationCode = $mJsonArray[$i]['location_code'];
                            $mInvPickPriority = $mJsonArray[$i]['pick_priority'];
                            $mPriority = 1;

                            $mPickQuery = "SELECT id FROM pick_detail WHERE pick_detail.pick_body_id=" . $mPickBdyId . " AND pick_detail.zone_destination ='" . $mToLoc . "' AND processed<>1 LIMIT 1";
                            if (!$mPickData = mysqli_query($mConnection, $mPickQuery))
                                return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;

                            if (mysqli_num_rows($mPickData) == 0)
                            {
                                $mPickQuery = "INSERT INTO pick_detail (id,inventory_master_id,pick_body_id,part_id,part_number,serial_reference,document_reference,location_code,qty_allocated,line_no,pick_priority,processed,qty_scanned,date_created,created_by,last_updated_date,last_updated_by,blocked,zone_destination,pick_sequence)";
                                if ($mBlocked == 0)
                                    $mPickQuery .= " VALUES(DEFAULT," . $mAlcInvId . "," . $mPickBdyId . "," . $mBdyPartId . ",'" . $mBdyPartNumber . "','" . $mInvSerialReference . "','" . $mPickDocumentReference . "','" . $mInvLocationCode . "'," . $mInvAllocatedQty . "," . $mBdyLineNo . "," . $mPriority . ",0,0,now(),'" . $mCurrentUser . "_RDT',now(),'" . $mCurrentUser . "_" . $mAlcType . "'," . $mBlocked . ",'" . $mToLoc . "','" . $mInvPickPriority . "')";
                                else
                                    $mPickQuery .= " VALUES(DEFAULT,null," . $mPickBdyId . "," . $mBdyPartId . ",'" . $mBdyPartNumber . "','BLOCKED','" . $mPickDocumentReference . "',''," . $mInvAllocatedQty . "," . $mBdyLineNo . "," . $mPriority . ",0,0,now(),'" . $mCurrentUser . "_RDT',now(),'" . $mCurrentUser . "_" . $mAlcType . "'," . $mBlocked . ",'" . $mToLoc . "','" . $mInvPickPriority . "')";
                                if (!mysqli_query($mConnection, $mPickQuery))
                                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
                                $mPickDtlId = mysqli_insert_id($mConnection);
                            }
                            else
                            {
                                list($mPickDtlId) = mysqli_fetch_row($mPickData);
                                $mPickQuery = "UPDATE pick_detail SET qty_allocated=qty_allocated+" . $mInvAllocatedQty . ",last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE pick_detail.id=" . $mPickDtlId . " LIMIT 1";
                                if (!mysqli_query($mConnection, $mPickQuery))
                                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
                            }

                            $mPickQuery = "SELECT id FROM pick_order_link WHERE pick_detail_id=" . $mPickDtlId . " AND order_body_id=" . $mBdyId . " LIMIT 1";
                            if (!$mPickData = mysqli_query($mConnection, $mPickQuery))
                                return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;

                            if (mysqli_num_rows($mPickData) == 0)
                            {
                                $mPickQuery = "INSERT INTO pick_order_link (id,pick_detail_id,order_body_id,date_created,created_by,last_updated_date,last_updated_by,line_no)";
                                $mPickQuery .= " VALUES(DEFAULT," . $mPickDtlId . "," . $mBdyId . ",now(),'" . $mCurrentUser . "',now(),'" . $mCurrentUser . "'," . $mBdyLineNo . ")";
                                if (!mysqli_query($mConnection, $mPickQuery))
                                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
                            }

                            // Update Pick Body
                            $mPickQuery = "UPDATE pick_body SET qty_allocated=qty_allocated+" . $mInvAllocatedQty . ",last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE pick_body.id=" . $mPickBdyId . " LIMIT 1";
                            if (!mysqli_query($mConnection, $mPickQuery))
                                return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
                            // Update Inventory_master
                            $mPickQuery = "UPDATE inventory_master SET allocated_qty=allocated_qty+" . $mInvAllocatedQty . ",available_qty=inventory_qty-(allocated_qty+hold_qty),last_updated=now(),last_updated_by='" . $mCurrentUser . "' WHERE inventory_master.id=" . $mAlcInvId . " AND inventory_master.allocated_qty<inventory_master.inventory_qty LIMIT 1";
                            if (!mysqli_query($mConnection, $mPickQuery))
                                return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
                        }
                    }
                }
            }
        }
    }

    $mEnd = date('Y-m-d H:i:s');

    $mPickQuery = "SELECT pick_detail.id AS id FROM pick_detail JOIN pick_body ON pick_detail.pick_body_id=pick_body.id WHERE pick_body.pick_header_id=" . $mPickHdrId . " AND pick_detail.processed<>1 LIMIT 1";
    if (!$mPickData = mysqli_query($mConnection, $mPickQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
    if (mysqli_num_rows($mPickData) == 0)
    {
        $mReturn = "DATA:EOD";
    }
    else
    {
        $mReturn = "OK  -allocateOrderLines";
    }
    mysqli_close($mConnection);
    return $mReturn;
}

// Get available Inventory
function readInventory($mHost, $mUser, $mPwd, $mDbName, $mBdyPartId, $mBdyQty)
{
    $mStart = date('Y-m-d H:i:s');
    $mIds = "";
    $mReqQty = 0;
    $mJsonArray = array();
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();
    $mAllocateNoRan = 1;
    /*
    // Get Company settings
        $mDataQuery = "SELECT allocate_no_ran AS allocate_no_ran FROM company LIMIT 1";
        if (!$mData = mysqli_query($mConnection,$mDataQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
        $mAllocateNoRan = mysqli_fetch_row($mData);
    */
    // Pruefcude / Trial-Part Allocation
    if (strtolower($mOrderTypeExt) == "pc")
    {
        $mInvQuery = "SELECT inventory_master.id AS id, (inventory_master.inventory_qty-inventory_master.hold_qty) AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id JOIN pick_group ON location.pick_group_id=pick_group.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=" . $mBdyPartId . " AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND lower(location_type.location_type_code)<>'nopick' AND (inventory_master.inventory_status_id IS null OR (lower(inventory_status.inventory_status_code) IN('pc','pc') AND inventory_status.non_pickable<>1)) ORDER BY inventory_master.date_created, inventory_master.location_code,inventory_master.inventory_qty LIMIT 1";
        if (!mysqli_query($mConnection, $mInvQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
        $mInvData = mysqli_query($mConnection, $mInvQuery);
        if (mysqli_num_rows($mInvData) == 0)
        {
            $mInvQuery = "SELECT inventory_master.id AS id, (inventory_master.inventory_qty-inventory_master.hold_qty) AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=" . $mBdyPartId . " AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND lower(location_type.location_area)<>'work' AND lower(location_type.location_type_code)<>'nopick' AND (inventory_master.inventory_status_id IS null OR (lower(inventory_status.inventory_status_code) IN('pc','pc') AND inventory_status.non_pickable<>1)) ORDER BY inventory_master.date_created, inventory_master.location_code,inventory_master.inventory_qty LIMIT 50";
            if (!mysqli_query($mConnection, $mInvQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
            $mInvData = mysqli_query($mConnection, $mInvQuery);
        }
        if (mysqli_num_rows($mInvData) > 0)
            $mAlcType .= "_PRUEFCUBE";

    }
    // Standard Allocation
    else
    {
        // Marshalling Area
        $mInvQuery = "SELECT inventory_master.id AS id, (inventory_master.inventory_qty-inventory_master.hold_qty) AS inventory_qty, inventory_master.allocated_qty AS allocated_qty,inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order";
        $mInvQuery .= ",part.part_number AS part_number, part.full_box_pick AS full_box_pick";
        $mInvQuery .= ",location.location_code AS location_code,location_column.filling_priority AS pick_priority";
        $mInvQuery .= ",product_type.product_type_code AS product_type_code";
        $mInvQuery .= " FROM inventory_master";
        $mInvQuery .= " JOIN location ON inventory_master.current_location_id=location.id";
        $mInvQuery .= " JOIN part ON inventory_master.part_id=part.id";
        $mInvQuery .= " JOIN pick_group ON location.pick_group_id=pick_group.id";
        $mInvQuery .= " LEFT JOIN vendor ON part.vendor_id=vendor.id";
        $mInvQuery .= " LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
        $mInvQuery .= " LEFT JOIN location_column ON location.column_code=location_column.column_code";
        $mInvQuery .= " LEFT JOIN location_type ON location.location_type_id=location_type.id";
        $mInvQuery .= " LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id";
        $mInvQuery .= " WHERE inventory_master.part_id=" . $mBdyPartId;
        $mInvQuery .= " AND inventory_master.serial_reference IS NOT null AND inventory_master.inventory_qty >0";
        $mInvQuery .= " AND (inventory_master.inventory_qty>inventory_master.allocated_qty OR NOT EXISTS(SELECT id FROM pick_detail WHERE inventory_master.id=pick_detail.inventory_master_id AND pick_detail.processed=0))";
        $mInvQuery .= " AND inventory_master.requires_inspection=0";
        $mInvQuery .= " AND inventory_master.requires_decant<>1";
        $mInvQuery .= " AND location_type.is_pickable=1";
        $mInvQuery .= " AND lower(location_type.location_type_code) IN('ma','maxdpt')";
        $mInvQuery .= " AND inventory_status.non_pickable<>1";
        $mInvQuery .= " AND (inventory_master.inventory_status_id IS null OR (lower(inventory_status.inventory_status_code) IN('ok','ok')))";
        $mInvQuery .= " ORDER BY inventory_master.date_created, inventory_master.location_code, inventory_master.part_id,inventory_master.inventory_qty LIMIT 10";
        if (!$mInvData = mysqli_query($mConnection, $mInvQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
        if (mysqli_num_rows($mInvData) > 0)
            $mAlcType .= "_MARSHALL";
        // PickFace
        if (mysqli_num_rows($mInvData) == 0)
        {
            $mInvQuery = "SELECT inventory_master.id AS id, (inventory_master.inventory_qty-inventory_master.hold_qty) AS inventory_qty, inventory_master.allocated_qty AS allocated_qty,inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order";
            $mInvQuery .= ",part.part_number AS part_number, part.full_box_pick AS full_box_pick";
            $mInvQuery .= ",location.location_code AS location_code,location_column.filling_priority AS pick_priority";
            $mInvQuery .= ",location.aisle_code AS aisle_code,location.rack_code AS rack_code";
            $mInvQuery .= ",CASE WHEN location_rack.priority_row_column='C' OR location_rack.priority_row_column IS null THEN location_column.filling_priority ELSE location_row.filling_priority END AS seq1";
            $mInvQuery .= ",CASE WHEN location_rack.priority_row_column<>'C' AND location_rack.priority_row_column IS NOT null THEN location_column.filling_priority ELSE location_row.filling_priority END AS seq2";
            $mInvQuery .= ",product_type.product_type_code AS product_type_code";
            $mInvQuery .= " FROM inventory_master";
            $mInvQuery .= " JOIN location ON inventory_master.current_location_id=location.id";
            $mInvQuery .= " JOIN part ON inventory_master.part_id=part.id";
            $mInvQuery .= " JOIN pick_group ON location.pick_group_id=pick_group.id";
            $mInvQuery .= " LEFT JOIN vendor ON part.vendor_id=vendor.id";
            $mInvQuery .= " LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
            $mInvQuery .= " LEFT JOIN location_column ON location.column_code=location_column.column_code";
            $mInvQuery .= " LEFT JOIN location_aisle ON location.aisle_code=location_aisle.aisle_code";
            $mInvQuery .= " LEFT JOIN location_rack ON location.rack_code=location_rack.rack_code";
            $mInvQuery .= " LEFT JOIN location_row ON location.row_code=location_row.row_code";
            $mInvQuery .= " LEFT JOIN location_type ON location.location_type_id=location_type.id";
            $mInvQuery .= " LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id";
            $mInvQuery .= " WHERE inventory_master.part_id=" . $mBdyPartId;
            $mInvQuery .= " AND inventory_master.serial_reference IS NOT null AND inventory_master.inventory_qty >0";
            $mInvQuery .= " AND (inventory_master.inventory_qty>inventory_master.allocated_qty OR NOT EXISTS(SELECT id FROM pick_detail WHERE inventory_master.id=pick_detail.inventory_master_id AND pick_detail.processed=0))";
            $mInvQuery .= " AND inventory_master.requires_inspection=0";
            $mInvQuery .= " AND inventory_master.requires_decant<>1";
            $mInvQuery .= " AND location_type.is_pickable=1";
            $mInvQuery .= " AND lower(location_type.location_type_code)<>'nopick'";
            $mInvQuery .= " AND inventory_status.non_pickable<>1";
            $mInvQuery .= " AND (inventory_master.inventory_status_id IS null OR (lower(inventory_status.inventory_status_code) IN('ok','ok')))";
            $mInvQuery .= " ORDER BY inventory_master.date_created";
            $mDataQuery .= ",location_aisle.filling_priority,location_aisle.aisle_code, location_rack.filling_priority,location_rack.rack_code, seq1, seq2,location.location_code";
            $mInvQuery .= " LIMIT 10";
            if (!mysqli_query($mConnection, $mInvQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
            $mInvData = mysqli_query($mConnection, $mInvQuery);
            if (mysqli_num_rows($mInvData) > 0)
                $mAlcType .= "_PICKFACE";
        }
        // Drop Zone
        if (mysqli_num_rows($mInvData) == 0)
        {

            $mInvQuery = "SELECT inventory_master.id AS id, (inventory_master.inventory_qty-inventory_master.hold_qty) AS inventory_qty, inventory_master.allocated_qty AS allocated_qty,inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order";
            $mInvQuery .= ",part.part_number AS part_number, part.full_box_pick AS full_box_pick";
            $mInvQuery .= ",location.location_code AS location_code,location_column.filling_priority AS pick_priority";
            $mInvQuery .= ",product_type.product_type_code AS product_type_code";
            $mInvQuery .= " FROM inventory_master";
            $mInvQuery .= " JOIN location ON inventory_master.current_location_id=location.id";
            $mInvQuery .= " JOIN part ON inventory_master.part_id=part.id";
            $mInvQuery .= " JOIN pick_group ON location.pick_group_id=pick_group.id";
            $mInvQuery .= " LEFT JOIN vendor ON part.vendor_id=vendor.id";
            $mInvQuery .= " LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
            $mInvQuery .= " LEFT JOIN location_column ON location.column_code=location_column.column_code";
            $mInvQuery .= " LEFT JOIN location_type ON location.location_type_id=location_type.id";
            $mInvQuery .= " LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id";
            $mInvQuery .= " WHERE inventory_master.part_id=" . $mBdyPartId;
            $mInvQuery .= " AND inventory_master.serial_reference IS NOT null AND inventory_master.inventory_qty >0";
            $mInvQuery .= " AND (inventory_master.inventory_qty>inventory_master.allocated_qty OR NOT EXISTS(SELECT id FROM pick_detail WHERE inventory_master.id=pick_detail.inventory_master_id AND pick_detail.processed=0))";
            $mInvQuery .= " AND inventory_master.requires_inspection=0";
            $mInvQuery .= " AND inventory_master.requires_decant<>1";
            $mInvQuery .= " AND location_type.is_pickable=1";
            $mInvQuery .= " AND lower(location_type.location_type_code)='dz'";
            $mInvQuery .= " AND inventory_status.non_pickable<>1";
            $mInvQuery .= " AND (inventory_master.inventory_status_id IS null OR (lower(inventory_status.inventory_status_code) IN('ok','ok')))";
            $mInvQuery .= " ORDER BY inventory_master.date_created, inventory_master.location_code, inventory_master.part_id,inventory_master.inventory_qty LIMIT 10";
            if (!$mInvData = mysqli_query($mConnection, $mInvQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
            if (mysqli_num_rows($mInvData) > 0)
                $mAlcType .= "_DROPZONE";
        }
        // Stores
        if (mysqli_num_rows($mInvData) == 0)
        {
            $mInvQuery = "SELECT inventory_master.id AS id, (inventory_master.inventory_qty-inventory_master.hold_qty) AS inventory_qty, inventory_master.allocated_qty AS allocated_qty,inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order";
            $mInvQuery .= ",part.part_number AS part_number, part.full_box_pick AS full_box_pick";
            $mInvQuery .= ",location.location_code AS location_code,location_column.filling_priority AS pick_priority";
            $mInvQuery .= ",location.aisle_code AS aisle_code,location.rack_code AS rack_code";
            $mInvQuery .= ",product_type.product_type_code AS product_type_code";
            $mInvQuery .= ",CASE WHEN location_rack.priority_row_column='C' OR location_rack.priority_row_column IS null THEN location_column.filling_priority ELSE location_row.filling_priority END AS seq1";
            $mInvQuery .= ",CASE WHEN location_rack.priority_row_column<>'C' AND location_rack.priority_row_column IS NOT null THEN location_column.filling_priority ELSE location_row.filling_priority END AS seq2";
            $mInvQuery .= " FROM inventory_master";
            $mInvQuery .= " JOIN location ON inventory_master.current_location_id=location.id";
            $mInvQuery .= " JOIN part ON inventory_master.part_id=part.id";
            $mInvQuery .= " LEFT JOIN pick_group ON location.pick_group_id=pick_group.id";
            $mInvQuery .= " LEFT JOIN vendor ON part.vendor_id=vendor.id";
            $mInvQuery .= " LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
            $mInvQuery .= " LEFT JOIN location_column ON location.column_code=location_column.column_code";
            $mInvQuery .= " LEFT JOIN location_aisle ON location.aisle_code=location_aisle.aisle_code";
            $mInvQuery .= " LEFT JOIN location_rack ON location.rack_code=location_rack.rack_code";
            $mInvQuery .= " LEFT JOIN location_row ON location.row_code=location_row.row_code";
            $mInvQuery .= " LEFT JOIN location_type ON location.location_type_id=location_type.id";
            $mInvQuery .= " LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id";
            $mInvQuery .= " WHERE inventory_master.part_id=" . $mBdyPartId;
            $mInvQuery .= " AND inventory_master.serial_reference IS NOT null AND inventory_master.inventory_qty >0";
            $mInvQuery .= " AND (inventory_master.inventory_qty>inventory_master.allocated_qty OR NOT EXISTS(SELECT id FROM pick_detail WHERE inventory_master.id=pick_detail.inventory_master_id AND pick_detail.processed=0))";
            $mInvQuery .= " AND inventory_master.requires_inspection=0";
            $mInvQuery .= " AND inventory_master.requires_decant<>1";
            $mInvQuery .= " AND (location_type.is_pickable=1 OR location_type.is_pickable IS null)";
            $mInvQuery .= " AND lower(location_type.location_type_code)<>'nopick'";
            $mInvQuery .= " AND inventory_status.non_pickable<>1";
            $mInvQuery .= " AND (inventory_master.inventory_status_id IS null OR (lower(inventory_status.inventory_status_code) IN('ok','ok')))";
            $mInvQuery .= " ORDER BY inventory_master.date_created";
            $mDataQuery .= ",location_aisle.filling_priority,location_aisle.aisle_code, location_rack.filling_priority,location_rack.rack_code, seq1, seq2,location.location_code";
            $mInvQuery .= " LIMIT 10";
            if (!mysqli_query($mConnection, $mInvQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
            $mInvData = mysqli_query($mConnection, $mInvQuery);
            if (mysqli_num_rows($mInvData) > 0)
                $mAlcType .= "_RACK";
        }
    }

    //    return "FAIL-TEST5-".$mInvQuery."-".mysqli_num_rows($mInvData)."-".mysqli_error($mConnection);

    if ($mIds != "")
        $mIds .= "," . $mBdyPartId;
    else
        $mIds = $mBdyPartId;

    while ($mInvRow = mysqli_fetch_assoc($mInvData))
    {
        $mAlcInvId = ($mInvRow['id']);
        $mInvQty = ($mInvRow['inventory_qty']);
        $mInvAllocatedQty = ($mInvRow['allocated_qty']);
        $mInvPartNumber = ($mInvRow['part_number']);
        $mInvSerialReference = ($mInvRow['serial_reference']);
        $mInvRanOrder = ($mInvRow['ran_order']);
        $mInvLocationCode = ($mInvRow['location_code']);
        //        $mInvPickPriority = ($mInvRow['pick_priority']);
        $mInvPickPriority = ($mInvRow['aisle_code'] . $mInvRow['rack_code'] . $mInvRow['seq1'] . $mInvRow['seq2'] . $mInvRow['location_code']);
        $mPickAllocatedQty = $mInvAllocatedQty;
        if ($mPickAllocatedQty > $mInvQty)
            $mPickAllocatedQty = $mInvQty;
        // Available Inventory Qty.
        $mInvQty = $mInvQty - $mPickAllocatedQty;
        // Existing Order Allocated Qty.
        $mOrdAllocatedQty = $mBdyQty;
        // Calculate Pick / Required Qty.
// Full Box Pick - take all Serial Inventory
        if ($mFullBoxPick == 1)
        {
            $mReqQty = $mInvQty;
            $mBdyQty = $mBdyQty - $mInvQty;
        }
        // Order Qty > Serial Inventory - Take all Serial Inventory
        elseif ($mBdyQty > $mInvQty)
        {
            $mReqQty = $mInvQty;
            $mBdyQty = $mBdyQty - $mInvQty;
        }
        // Take Order Requirement Qty.
        else
        {
            $mReqQty = $mBdyQty;
            $mBdyQty = 0;
        }
        $mJsonArray[] = array('id' => $mAlcInvId, 'part_number' => $mInvPartNumber, 'serial_reference' => $mInvSerialReference, 'qty' => $mReqQty, 'location_code' => $mInvLocationCode, 'pick_priority' => $mInvPickPriority);
        if ($mBdyQty <= 0)
            break;
    }

    //    echo("FAIL-".$mIds);
//    return;
    $mEnd = date('Y-m-d H:i:s');
    //   return "FAIL-".$mStart."-".$mEnd;
    return $mJsonArray;
}

function updatePickDetail($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mSerialReference, $mPickQty, $mCurrentUser, $mTransactionTypeId, $mToPlt, $mToLocHash, $mConnect = "", $mRdtStart = "", $mRdtEnd = "")
{
    if (($mPickDtlId == "") || ($mPickDtlId == null))
        return "FAIL-incorrect Pick Reference";
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();

    // Get id for inventory status
    $mInvStsId = 0;
    $mInvRcvId = 0;
    $mInvPckId = 0;
    $mInvPltId = 0;
    $mInvNrId = 0;
    $mDataQuery = "SELECT id AS id, inventory_status_code AS inventory_status_code FROM inventory_status";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . $mDataQuery;
    $mData = (mysqli_query($mConnection, $mDataQuery));
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        if (strtolower($mRow['inventory_status_code']) == "ok")
            $mInvStsId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "rcv")
            $mInvRcvId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "pkd")
            $mInvPkdId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "plt")
            $mInvPltId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "noran")
            $mInvNrId = ($mRow['id']);
    }
    if ($mInvPkdId == null && $mInvPltId != null)
        $mInvPkdId = $mInvPltId;
    // Get id for document status
    $mDocStsId = 0;
    $mDocPkdId = 0;
    $mDataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . $mDataQuery;
    $mData = (mysqli_query($mConnection, $mDataQuery));
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        if (strtolower($mRow['document_status_code']) == "open")
            $mDocStsId = ($mRow['id']);
        if (strtolower($mRow['document_status_code']) == "picked")
            $mDocPkdId = ($mRow['id']);
    }

    // Get Tag Id if Required
    if ($mToPlt != "")
    {
        $mDataQuery = "SELECT id FROM tag WHERE tag_reference='" . $mToPlt . "' LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mTagId = ($mRow['id']);
        }
    }
    // Get default Pick Location Id
    $mPickLocId = null;
    $mPickLocCode = "INTRAN";
    $mDataQuery = "SELECT id,location_code FROM location WHERE location_code IN('INTRAN','PICK01') LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mPickLocId = ($mRow['id']);
        $mPickLocCode = ($mRow['location_code']);
    }
    $mToLocCode = "";
    // Get Location Id if Required
    if ($mToLocHash != "")
    {
        $mToLocId = null;
        $mDataQuery = "SELECT id,location_code FROM location WHERE location_hash='" . $mToLocHash . "' LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mToLocId = ($mRow['id']);
            $mToLocCode = ($mRow['location_code']);
        }
    }
    // Store Inventory_master id from Pick_detail
    $mPrvInvId = "";
    $mPickBdyId = "";
    $mPickHdrId = "";
    $mPickDtlQty = 0;
    $mConversionFactor = 1;
    $mDocDtlId = "";
    $mPickSerialRef = "";
    $mCustomerReference = "";
    $mProcessed = 0;
    $mDocBdyId = "";
    $mDataQuery = "SELECT pick_detail.inventory_master_id AS inventory_master_id, pick_detail.pick_body_id AS pick_body_id, pick_detail.qty_allocated AS qty_allocated, pick_detail.line_no AS line_no, pick_detail.pick_priority AS pick_priority, pick_detail.processed AS processed, pick_body.pick_header_id AS pick_header_id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, CASE WHEN pick_detail.serial_reference_scanned <> '' THEN pick_detail.serial_reference_scanned ELSE pick_detail.serial_reference END AS serial_reference, pick_header.customer_reference AS customer_reference FROM pick_detail JOIN pick_body ON pick_detail.pick_body_id=pick_body.id JOIN pick_header ON pick_body.pick_header_id=pick_header.id LEFT JOIN part ON pick_detail.part_id=part.id WHERE pick_detail.id=" . $mPickDtlId . " LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mPrvInvId = ($mRow['inventory_master_id']);
        $mPickBdyId = ($mRow['pick_body_id']);
        $mPickHdrId = ($mRow['pick_header_id']);
        $mPickDtlQty = ($mRow['qty_allocated']);
        $mPickDtlAlcQty = ($mRow['qty_allocated']);
        $mConversionFactor = ($mRow['conversion_factor']);
        $mPartNumber = ($mRow['part_number']);
        $mDocDtlId = ($mRow['line_no']);
        $mPickSerialRef = ($mRow['serial_reference']);
        $mPickPriority = ($mRow['pick_priority']);
        $mCustomerReference = ($mRow['customer_reference']);
        $mProcessed = ($mRow['processed']);
    }
    if (($mSerialReference == "") || ($mSerialReference == null))
        $mSerialReference = $mPickSerialRef;

    if ($mProcessed == 1)
    {
        return "FAIL-This line has already been Picked - F2 to skip";
    }
    // Zero Qty Not allowed
    if ((!$mPickQty) || ($mPickQty <= 0) || ($mPickDtlQty <= 0))
    {
        return ("FAIL-Incorrect quantity-") . $mPickQty;
    }
    // Use Scanned Qty if Entered
    if (($mPickQty != "") && ($mPickQty != null))
        $mPickDtlQty = $mPickQty * $mConversionFactor;
    else
        $mPickQty = $mPickDtlQty;


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

    // verify that the update has been performed
    $mDataQuery = "SELECT id FROM pick_detail WHERE id=" . $mPickDtlId . " AND processed=0 LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    if (mysqli_num_rows($mData) > 0)
    {
        return "FAIL-Update Error - please scan again " . $mDataQuery;
    }

    // Check if Full Serial has been picked
    $mDataQuery = "SELECT inventory_master.id AS id,inventory_master.part_id AS part_id,inventory_master.ran_or_order AS ran_order,inventory_master.current_location_id AS location_id,inventory_master.product_type_id AS product_type_id ,inventory_master.tag_reference AS plt_number,inventory_master.inventory_qty AS inventory_qty";
    $mDataQuery .= ",part.part_number AS part_number,part.full_box_pick AS full_box_pick";
    $mDataQuery .= ",location.location_code AS location_code";
    $mDataQuery .= " FROM inventory_master";
    $mDataQuery .= " JOIN part ON inventory_master.part_id=part.id";
    $mDataQuery .= " JOIN location ON inventory_master.current_location_id=location.id";
    $mDataQuery .= " WHERE inventory_master.id=" . $mPickInvId . " LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    $mInvQty = 0;
    $mFromLocCode = "";
    $mRanOrder = "";
    $mFullBoxPart = 0;
    $mInvRow = array();
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mInvQty = ($mRow['inventory_qty']);
        $mFromLocCode = ($mRow['location_code']);
        $mRanOrder = ($mRow['ran_order']);
        $mFullBoxPart = ($mRow['full_box_pick']);
        $mInvId = ($mRow['id']);
        $mInvRow = ($mRow);
    }
    /*	
    // Get Ran from order body
        $mDataQuery = "SELECT ran_order FROM order_body WHERE order_body.id=".$mDocBdyId." LIMIT 1";
        if (!$mData = mysqli_query($mConnection,$mDataQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
        while($mRow = mysqli_fetch_assoc($mData))
        {
            $mRanOrder = ($mRow['ran_order']);
        }
    */
    if ($mInvQty <= $mPickDtlQty)
    {
        // Change Inventory Master Status - Full Serial Pick
        $mDataQuery = "UPDATE inventory_master SET inventory_status_id=" . $mInvPkdId . ",allocated_qty=0,available_qty=(inventory_qty-hold_qty)";
        if ($mToPlt != "")
            $mDataQuery .= ",inventory_master.tag_reference='" . $mToPlt . "'";
        if (($mTagId != null) && ($mTagId != ""))
            $mDataQuery .= ",inventory_master.parent_tag_id=" . $mTagId;
        if ($mToLocCode != "")
            $mDataQuery .= ",inventory_master.current_location_id=" . $mToLocId . ",inventory_master.location_id=" . $mToLocId . ",inventory_master.location_code='" . $mToLocCode . "'";
        elseif ($mPickLocId != null)
            //			$mDataQuery .= ",inventory_master.current_location_id=".$mPickLocId;
            $mDataQuery .= ",inventory_master.current_location_id=" . $mPickLocId . ",inventory_master.location_id=" . $mPickLocId . ",inventory_master.location_code='" . $mPickLocCode . "'";
        if (($mRanOrder == "") || ($mRanOrder == "NO-RAN"))
            $mDataQuery .= ", ran_or_order = '" . $mCustomerReference . "'";

        $mDataQuery .= ",last_updated=now(),last_updated_by='" . $mCurrentUser . "_FULL' WHERE inventory_master.id=" . $mPickInvId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        // Check for Replen
        $mDataQuery = "SELECT replenish_when_empty AS replen FROM transaction_type WHERE upper(short_code)='" . $mTransactionTypeId . "' LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            if ($mRow['replen'] == 1)
            {
                try
                {
                    include_once('replen.php');
                    $mReturn = createReplen($mInvRow['part_number'], $mFromLocCode, $mCurrentUser, $mConnect);
                }
                catch (Exception $e)
                {
                }
            }
        }
    }
    else
    // Partial Pick Reduce Existing Inventory - Create New Pick Inventory
    {
        $mDataQuery = "UPDATE inventory_master SET inventory_qty = inventory_qty - " . $mPickDtlQty . ",allocated_qty=CASE WHEN allocated_qty>=" . $mPickDtlQty . " THEN allocated_qty-" . $mPickDtlQty . " ELSE 0 END, available_qty = inventory_qty-(allocated_qty+hold_qty), inventory_status_id=" . $mInvStsId . ",last_updated=now(),last_updated_by='" . $mCurrentUser . "_PCK' WHERE inventory_master.id=" . $mPickInvId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;

        if (($mRanOrder == "") || ($mRanOrder == "NO-RAN"))
            $mRanOrder = $mCustomerReference;

        if ($mPickLocId == null)
            $mPickLocId = ($mInvRow['location_id']);
        if ($mToLocCode == "")
            if (($mTagId == "") || ($mTagId == null))
                $mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,hold_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) VALUES(DEFAULT,0,0,null," . $mPickLocId . ",now(),null," . $mPickDtlQty . ",now(),'" . $mCurrentUser . "',null," . $mInvRow['part_id'] . ",'" . $mRanOrder . "',0,0,0,null,null," . $mConversionFactor . ",now(),1,'" . $mInvRow['part_number'] . "',0,0," . $mPickDtlQty . ",'" . $mCurrentUser . "','" . $mPickLocCode . "','" . $mToPlt . "',null," . $mPickLocId . "," . $mInvPkdId . ")";
            else
                $mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,hold_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) VALUES(DEFAULT,0,0,null," . $mPickLocId . ",now(),null," . $mPickDtlQty . ",now(),'" . $mCurrentUser . "'," . $mTagId . "," . $mInvRow['part_id'] . ",'" . $mRanOrder . "',0,0,0,null,null," . $mConversionFactor . ",now(),1,'" . $mInvRow['part_number'] . "',0,0," . $mPickDtlQty . ",'" . $mCurrentUser . "','" . $mPickLocCode . "','" . $mToPlt . "',null," . $mPickLocId . "," . $mInvPkdId . ")";
        else
            if (($mTagId == "") || ($mTagId == null))
                $mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,hold_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) VALUES(DEFAULT,0,0,null," . $mToLocId . ",now(),null," . $mPickDtlQty . ",now(),'" . $mCurrentUser . "',null," . $mInvRow['part_id'] . ",'" . $mRanOrder . "',0,0,0,null,null," . $mConversionFactor . ",now(),1,'" . $mInvRow['part_number'] . "',0,0," . $mPickDtlQty . ",'" . $mCurrentUser . "','" . $mToLocCode . "','" . $mToPlt . "',null," . $mToLocId . "," . $mInvPkdId . ")";
            else
                $mDataQuery = "INSERT INTO inventory_master (id,allocated_qty,hold_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) VALUES(DEFAULT,0,0,null," . $mToLocId . ",now(),null," . $mPickDtlQty . ",now(),'" . $mCurrentUser . "'," . $mTagId . "," . $mInvRow['part_id'] . ",'" . $mRanOrder . "',0,0,0,null,null," . $mConversionFactor . ",now(),1,'" . $mInvRow['part_number'] . "',0,0," . $mPickDtlQty . ",'" . $mCurrentUser . "','" . $mToLocCode . "','" . $mToPlt . "',null," . $mToLocId . "," . $mInvPkdId . ")";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        $mPickInvId = mysqli_insert_id($mConnection);
        // Change Inventory Id On Pick Detail
        $mDataQuery = "UPDATE pick_detail SET inventory_master_id=" . $mPickInvId . " WHERE id=" . $mPickDtlId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    }

    // Check / Change Pick Body Processed Status
    $mDataQuery = "UPDATE pick_body SET";
    $mDataQuery .= " qty_transacted= (CASE WHEN (qty_transacted<qty_expected) THEN qty_transacted+" . $mPickDtlQty . " ELSE qty_transacted END), qty_shortage=qty_expected-qty_transacted";
    $mDataQuery .= ",qty_allocated= (CASE WHEN qty_allocated>" . $mPickDtlQty . " THEN qty_allocated-" . $mPickDtlQty . " ELSE 0 END)";
    $mDataQuery .= ",last_updated_date=now(),last_updated_by='" . $mCurrentUser . "'";
    $mDataQuery .= ", processed= (CASE WHEN (qty_expected <= qty_transacted) THEN 1 ELSE 0 END)";
    $mDataQuery .= " WHERE pick_body.id=" . $mPickBdyId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;

    // Update Order Body Pick Status
    if ($mDocBdyId == "")
    {
        $mDataQuery = "SELECT order_body_id FROM pick_order_link WHERE pick_detail_id=" . $mPickDtlId;
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            if ($mDocBdyId != "")
                $mDocBdyId .= "," . $mRow['order_body_id'];
            else
                $mDocBdyId = $mRow['order_body_id'];
        }
    }
    if ($mDocBdyId != "")
    {
        if (substr($mDocBdyId, 0, 1) == ",")
            $mDocBdyId = substr($mDocBdyId, 1);
        $mOrdBdyQty = $mPickDtlQty;
        $mDataQuery = "SELECT order_body.id AS id,order_body.qty_expected AS qty_expected,order_body.qty_transacted AS qty_transacted FROM order_body WHERE order_body.id IN(" . $mDocBdyId . ")";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        $mPickOrdId = "";
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mPickOrdId = ($mRow['id']);
            if ($mOrdBdyQty < ($mRow['qty_expected'] - $mRow['qty_transacted']))
            {
                $mReqQty = $mOrdBdyQty;
                $mOrdBdyQty = 0;
            }
            else
            {
                $mReqQty = $mRow['qty_expected'] - $mRow['qty_transacted'];
                $mOrdBdyQty = $mOrdBdyQty - $mReqQty;
            }
            if (($mPickOrdId != "") && ($mPickOrdId != null))
            {
                $mOrderQuery = "UPDATE order_body SET qty_transacted=qty_transacted+" . $mReqQty . ",difference=qty_transacted-qty_expected,last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE id=" . $mPickOrdId . " LIMIT 1";
                if (!mysqli_query($mConnection, $mOrderQuery))
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mOrderQuery;
            }
        }
        if ($mOrdBdyQty > 0)
        {
            $mOrderQuery = "UPDATE order_body SET qty_transacted=qty_transacted+" . $mOrdBdyQty . ",difference=qty_transacted-qty_expected,last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE id=" . $mPickOrdId . " LIMIT 1";
            if (!mysqli_query($mConnection, $mOrderQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mOrderQuery;
        }
    }

    //	return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".$mOrderQuery."-".$i;

    // Update Inventory_master Allocated Qty
    if (($mPrvInvId != $mPickInvId) && ($mPickSerialRef != $mSerialReference))
    {
        $mDataQuery = "UPDATE inventory_master SET allocated_qty=CASE WHEN allocated_qty>=" . $mPickDtlAlcQty . " THEN allocated_qty-" . $mPickDtlAlcQty . " ELSE 0 END, available_qty=inventory_qty-(allocated_qty+hold_qty),last_updated=now(),last_updated_by='" . $mCurrentUser . "_ALC' WHERE inventory_master.id=" . $mPrvInvId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    }
    // If Pick Body is Complete Check for outstanding Pick Details
    $mDataQuery = "SELECT pick_detail.id AS id, pick_detail.inventory_master_id AS inventory_master_id, pick_detail.qty_allocated AS qty_allocated FROM pick_body JOIN pick_detail ON pick_detail.pick_body_id=pick_body.id WHERE pick_body.id=" . $mPickBdyId . " AND pick_body.processed=1 AND pick_detail.processed=0";
    $mData = mysqli_query($mConnection, $mDataQuery);
    $mAlcQty = 0;
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mAlcQty = $mRow['qty_allocated'];
        $mPickQuery = "UPDATE inventory_master set allocated_qty=CASE WHEN allocated_qty>" . $mAlcQty . " THEN allocated_qty-" . $mAlcQty . " ELSE 0 END, available_qty=inventory_qty-(allocated_qty+hold_qty),last_updated=now(),last_updated_by='" . $mCurrentUser . "_CMP' WHERE id=" . $mRow['inventory_master_id'] . " LIMIT 1";
        if (!mysqli_query($mConnection, $mPickQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
        $mPickQuery = "UPDATE pick_detail SET qty_allocated=0,processed=1 WHERE id=" . $mRow['id'] . " LIMIT 1";
        if (!mysqli_query($mConnection, $mPickQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
    }
    // Check for Order Complete
    $mDataQuery = "SELECT order_body.id AS id FROM order_body JOIN order_header ON order_body.order_header_id=order_header.id WHERE order_header.pick_header_id=" . $mPickHdrId . " AND order_body.qty_expected>order_body.qty_transacted LIMIT 1";
    $mPickData = mysqli_query($mConnection, $mDataQuery);
    if (mysqli_num_rows($mPickData) == 0)
    {
        if ($mDocPkdId != "")
        {
            $mDataQuery = "UPDATE pick_header SET document_status_id=" . $mDocPkdId . ",last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE id=" . $mPickHdrId . " LIMIT 1";
            if (!mysqli_query($mConnection, $mDataQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        }

        $mDataQuery = "UPDATE order_header SET document_status_id=" . $mDocPkdId . ",last_updated_date=now(),last_updated_by='" . $mCurrentUser . "_COMP' WHERE pick_header_id=" . $mPickHdrId;
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;

        // Send Server Message
//		http://172.16.1.70:8092/screen/writeOrderCompleteMessage?documentReference=COR408605
//		http://172.16.1.70:8092/screen/writeCrossDockCompleteMessage?documentReference=COR408605
//		http://172.16.1.70:8092/screen/writeReSeqCompleteMessage?documentReference=COR408605

        $mPickQuery = "SELECT document_reference_code AS document_reference, pick_type AS pick_type, order_type AS order_type FROM order_header WHERE pick_header_id=" . $mPickHdrId . " LIMIT 1";
        $mPickData = mysqli_query($mConnection, $mPickQuery);
        $mPickType = "";
        while ($mRow = mysqli_fetch_assoc($mPickData))
        {
            $mPickType = $mRow['pick_type'];
            $mOrderType = $mRow['order_type'];
            $mOrdDocRef = $mRow['document_reference'];
        }
        try
        {
            // Get Server Data from URL
            $mJsonArray = array();
            $mJsonArray['documentReference'] = $mOrdDocRef;
            // Convert Array to Json Object
            try
            {
                $mJsonObject = json_encode($mJsonArray, JSON_FORCE_OBJECT);
            }
            catch (Exception $e)
            {
                exit("FAIL-Json Error");
            }
            // Replace default Server Tomcat Service
            $mConnectp = str_replace(":8081/vantec", ":8092/screen", $mConnect);
            if ($mConnectp == $mConnect)
            {
                $e = strpos($mConnect, ":8081");
                $mConnectp = substr($mConnect, 0, $e) . ":8092/screen/";
            }
            //            $ch = curl_init();
        }
        catch (Exception $e)
        {
            $mErrMsg = "FAIL-Connection Error at - " . $mConnect . $mFilter . ":" . $e;
        }
    }

    // Refresh inventory_master Allocated_qty
    if ($mPrvInvId != $mPickInvId)
    {
        $mPickQuery = "SELECT id FROM pick_detail WHERE inventory_master_id=" . $mPrvInvId . " LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mPickQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
        if (mysqli_num_rows($mData) == 0)
        {
            $mPickQuery = "UPDATE inventory_master SET allocated_qty=0,available_qty=(inventory_qty-hold_qty) WHERE id=" . $mPrvInvId . " LIMIT 1";
            if (!mysqli_query($mConnection, $mPickQuery))
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
        }
    }
    $mPickQuery = "SELECT id FROM pick_detail WHERE inventory_master_id=" . $mPickInvId . " LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mPickQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
    if (mysqli_num_rows($mData) == 0)
    {
        $mPickQuery = "UPDATE inventory_master SET allocated_qty=0,available_qty=(inventory_qty-hold_qty) WHERE id=" . $mPickInvId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mPickQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
    }

    $mOrderType = "";
    $mDocHdrId = "";
    $mPickQuery = "SELECT pick_header.order_header_id AS order_header_id, order_header.order_type AS order_type FROM pick_header JOIN order_header ON pick_header.order_header_id=order_header.id WHERE pick_header.id=" . $mPickHdrId . " LIMIT 1";
    if (!$mPickData = mysqli_query($mConnection, $mPickQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mPickQuery;
    $mPickData = mysqli_query($mConnection, $mPickQuery);
    while ($mRow = mysqli_fetch_assoc($mPickData))
    {
        $mDocHdrId = ($mRow['order_header_id']);
        $mOrderType = $mRow['order_type'];
        $mPickType = ($mRow['pick_type']);
    }
    $mReturn = updatePickHistory($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mPickDtlQty, $mCurrentUser, $mTransactionTypeId, $mFromLocCode, $mOrderType, $mDocDtlId . "-" . $mPickPriority, $mDocBdyId);

    // If Full_box_pick part - move remainder of Serial
    if ($mFullBoxPart == 1)
    {
        $mDataQuery = "UPDATE inventory_master SET inventory_status_id=" . $mInvStsId;
        if ($mToLocCode != "")
            $mDataQuery .= ",inventory_master.current_location_id=" . $mToLocId . ",inventory_master.location_id=" . $mToLocId . ",inventory_master.location_code='" . $mToLocCode . "'";
        elseif ($mPickLocId != null)
            $mDataQuery .= ",inventory_master.current_location_id=" . $mPickLocId . ",inventory_master.location_id=" . $mPickLocId . ",inventory_master.location_code='" . $mPickLocCode . "'";
        $mDataQuery .= ",last_updated=now(),last_updated_by='" . $mCurrentUser . "_FULL' WHERE inventory_master.id=" . $mInvId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        $mDataQuery = "SELECT inventory_master.inventory_qty AS inventory_qty FROM inventory_master WHERE inventory_master.id=" . $mInvId . " LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        list($mPickDtlQty) = mysqli_fetch_row($mData);
        $mReturn = updatePickHistory($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mPickDtlQty, $mCurrentUser, 'STKMV', $mFromLocCode, $mOrderType, $mDocDtlId . "-" . $mPickPriority, $mDocBdyId);
        // Un-allocate related parts
        $mDataQuery = "SELECT id AS id,inventory_master_id AS inventory_master_id,pick_body_id AS pick_body_id,qty_allocated AS qty_allocated FROM pick_detail WHERE part_number='" . $mPartNumber . "' LIMIT 10";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        $mData = mysqli_query($mConnection, $mDataQuery);
        while ($mRow = mysqli_fetch_assoc($mPickData))
        {
            $mDataQuery = "UPDATE inventory_master SET allocated_qty=CASE WHEN allocated_qty>" . $mRow['allocated_qty'] . " THEN allocated_qty-" . $mRow['allocated_qty'] . " ELSE 0 END, available_qty=inventory_qty-(allocated_qty+hold_qty) WHERE id=" . $mRow['inventory_master_id'] . " LIMIT 1";
            if (!mysqli_query($mConnection, $mDataQuery))
                exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);

            $mDataQuery = "UPDATE pick_detail SET in_use_by='',qty_allocated=0,processed=1,last_updated_date=now(),created_by='" . $mCurrentUser . "-FULLPART'";
            $mDataQuery .= " WHERE pick_detail.id='" . $mRow['id'] . "' AND processed=0 LIMIT 1";
            if (!mysqli_query($mConnection, $mDataQuery))
                exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);

            $mDataQuery = "UPDATE pick_body SET pick_body.qty_allocated=CASE WHEN qty_allocated<" . $mRow['allocated_qty'] . " THEN 0 ELSE qty_allocated-" . $mRow['allocated_qty'] . " END, pick_body.qty_shortage= CASE WHEN pick_body.qty_allocated>pick_body.qty_expected THEN 0 ELSE pick_body.qty_expected-pick_body.qty_allocated END,last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE pick_body.id=" . $mRow['pick_body_id'] . " LIMIT 1";
            if (!mysqli_query($mConnection, $mDataQuery))
                exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
            $mPickDtlQty . -$mRow['allocated_qty'];
            if ($mPickDtlQty <= 0)
                break;
        }
    }

    mysqli_close($mConnection);

    if (is_string($mReturn) && substr($mReturn, 0, 2) == 'FA')
        return $mReturn;

    // Set Location Filling Status
    $mReturn = flagEmptyLocation($mFromLocCode, $mCurrentUser, $mHost, $mUser, $mPwd, $mDbName);

    if ($mReturn != "")
        return $mReturn;

    return "OK  -";
}

// Update Transaction History
function updatePickHistory($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mPickDtlQty, $mCurrentUser, $mTransactionTypeId, $mFromLocCode = "", $mOrderType = "", $mPickPriority = "", $mOrdBdyId = 0, $mSerialReference = null, $mComment = null)
{
    if (($mHost == null) || ($mHost == ""))
        $mConnection = defaultDbConnection();
    else
        $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();
    // Order Type
    $mOrderDesc = $mOrderType;
    if ($mOrderType != "")
    {
        $mDataQuery = "SELECT description AS order_desc FROM order_type WHERE order_type_code='" . $mOrderType . "' LIMIT 1";
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        $mData = mysqli_query($mConnection, $mDataQuery);
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mOrderDesc = $mRow['order_desc'];
        }
    }
    // Get Ran from order body
    $mRanOrder = "";
    /*
        $mDataQuery = "SELECT ran_order FROM order_body WHERE order_body.id=".$mOrdBdyId." LIMIT 1";
        if (!$mData = mysqli_query($mConnection,$mDataQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
        while($mRow = mysqli_fetch_assoc($mData))
        {
            $mRanOrder = ($mRow['ran_order']);
        }
    */
    // Inventory Master
    $mDataQuery = "SELECT inventory_master.ran_or_order AS ran_order, inventory_master.current_location_id AS location_id, part.part_number,part.conversion_factor, location.location_code,inventory_master.tag_reference AS plt_number, inventory_master.serial_reference AS serial_reference, inventory_master.inventory_qty AS inventory_qty, vendor.vendor_reference_code AS vendor_code,product_type.product_type_code AS product_type_code";
    $mDataQuery .= " FROM inventory_master";
    $mDataQuery .= " JOIN part ON inventory_master.part_id=part.id";
    $mDataQuery .= " JOIN location ON inventory_master.current_location_id=location.id";
    $mDataQuery .= " LEFT JOIN vendor ON part.vendor_id=vendor.id";
    $mDataQuery .= " LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id";
    $mDataQuery .= " WHERE inventory_master.id=" . $mPickInvId . " LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    $mInvRow = array();
    if (mysqli_num_rows($mData) > 0)
    {
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mInvRow = $mRow;
        }
    }
    if ($mRanOrder == "")
    {
        $mRanOrder = $mInvRow['ran_order'];
    }
    // Pick Detail
    $mDataQuery = "SELECT pick_detail.qty_allocated,CASE WHEN pick_detail.serial_reference_scanned IS null THEN pick_detail.serial_reference ELSE pick_detail.serial_reference_scanned END AS serial_reference,pick_body.customer_reference,pick_body.document_reference,pick_body.pick_header_id AS pick_header_id FROM pick_detail JOIN pick_body ON pick_detail.pick_body_id=pick_body.id WHERE pick_detail.id=" . $mPickDtlId . " LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    $mDtlRow = array();
    if (mysqli_num_rows($mData) > 0)
    {
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mDtlRow = $mRow;
        }
    }
    $mSerialRef = $mDtlRow['serial_reference'];
    if (($mSerialRef == "" || $mSerialRef == null) && $mInvRow['serial_reference'] != null)
        $mSerialRef = $mInvRow['serial_reference'];
    if ($mSerialReference != null)
    {
        $mSerialRef = $mSerialReference;
    }
    if ($mTransactionTypeId == "MISSING")
        $mPickDtlQty = $mInvRow['inventory_qty'];
    //	$mRanOrder = $mInvRow['ran_order'];
    if (($mRanOrder == "") || ($mRanOrder == null) || ($mRanOrder == "NORAN"))
        $mRanOrder = $mDtlRow['customer_reference'];
    // Transaction History	
    if ($mOrderType != "")
    {
        if ($mTransactionTypeId == "F2SKIP")
            $mDataQuery = "INSERT INTO transaction_history_skip";
        else
            $mDataQuery = "INSERT INTO transaction_history";
        $mDataQuery .= " (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,vendor_code,product_type_code) VALUE(DEFAULT,'" . $mRanOrder . "','" . $mInvRow['part_number'] . "','" . $mSerialRef . "','" . $mTransactionTypeId . "-" . $mOrderDesc . "'," . $mPickDtlQty . ",'" . $mFromLocCode . "','" . $mInvRow['location_code'] . "','" . $mInvRow['plt_number'] . "','" . $mDtlRow['customer_reference'] . "','" . $mDtlRow['document_reference'] . "',now(),'" . $mCurrentUser . "',now(),'" . $mCurrentUser . "','" . $mInvRow['plt_number'] . "','" . $mTransactionTypeId . "','" . $mComment . "','" . $mPickPriority . "'," . $mInvRow['conversion_factor'] . ",'" . $mInvRow['vendor_code'] . "','" . $mInvRow['product_type_code'] . "')";
    }
    else
    {
        if ($mTransactionTypeId == "F2SKIP")
            $mDataQuery = "INSERT INTO transaction_history_skip";
        else
            $mDataQuery = "INSERT INTO transaction_history";
        $mDataQuery .= " (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,vendor_code,product_type_code) VALUE(DEFAULT,'" . $mRanOrder . "','" . $mInvRow['part_number'] . "','" . $mSerialRef . "','" . $mTransactionTypeId . "'," . $mPickDtlQty . ",'" . $mFromLocCode . "','" . $mInvRow['location_code'] . "','" . $mInvRow['plt_number'] . "','" . $mDtlRow['customer_reference'] . "','" . $mDtlRow['document_reference'] . "',now(),'" . $mCurrentUser . "',now(),'" . $mCurrentUser . "','" . $mInvRow['plt_number'] . "','" . $mTransactionTypeId . "','" . $mComment . "','" . $mPickPriority . "'," . $mInvRow['conversion_factor'] . ",'" . $mInvRow['vendor_code'] . "','" . $mInvRow['product_type_code'] . "')";
    }

    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    mysqli_close($mConnection);
    return "OK  -";
    //    return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
}

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

}

// Pick Run Aisle Summary
function pickAisleSummary($mHost, $mUser, $mPwd, $mDbName, $mCustomerReference, $mCurrentUser, $mTransactionType = "PBTP", $mConnect = "")
{
    $mDataStr = "";

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

// Flag Inventory / Pick Task as Missing
function missingPickSerial($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mSerialReference, $mPickQty, $mAisle, $mTaskType, $mCurrentUser, $mTransactionTypeId, $mFromLoc, $mToPlt = "", $mTrain = "")
{
    $mReturn = "";

    if ($mPickQty == null)
        $mPickQty = 0;
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();
    // Get id for inventory status
    $mInvStsId = 0;
    $mInvRcvId = 0;
    $mInvPckId = 0;
    $mInvPltId = 0;
    $mInvMisId = 0;
    $mDataQuery = "SELECT id AS id, inventory_status_code AS inventory_status_code FROM inventory_status";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . $mDataQuery;
    $mData = (mysqli_query($mConnection, $mDataQuery));
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        if (strtolower($mRow['inventory_status_code']) == "ok")
            $mInvStsId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "rcv")
            $mInvRcvId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "pkd")
            $mInvPkdId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "plt")
            $mInvPltId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "missing")
            $mInvMisId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "lost")
            $mInvMisId = ($mRow['id']);
    }

    if ($mInvPkdId == null && $mInvPltId != null)
        $mInvPkdId = $mInvPltId;

    $mDataQuery = "SELECT pick_detail.id AS id, pick_detail.pick_body_id AS pick_body_id, pick_detail.document_reference AS document_reference, pick_detail.part_id AS part_id, pick_detail.qty_allocated AS qty_allocated, pick_detail.location_code AS location_code,pick_detail.line_no AS line_no, pick_detail.pick_priority AS pick_priority FROM pick_detail WHERE id=" . $mPickDtlId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $DataQuery;
    $mData = mysqli_query($mConnection, $mDataQuery);
    $mPickBdyId = "";
    $mPickDtlLoc = "";
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mPickBdyId = $mRow['pick_body_id'];
        $mDocumentReference = $mRow['document_reference'];
        $mPartId = $mRow['part_id'];
        $mPickDtlQty = $mRow['qty_allocated'];
        $mPickDtlLoc = $mRow['location_code'];
        $mDocDtlId = $mRow['line_no'];
        $mPickPriority = $mRow['pick_priority'];
    }
    // Validate Entered Location
    $mDataQuery = "SELECT id AS id, location_hash AS location_hash FROM location WHERE location_code='" . $mPickDtlLoc . "' LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $DataQuery;
    $mData = mysqli_query($mConnection, $mDataQuery);
    $i = 0;
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        if ($mFromLoc == $mRow['location_hash'])
            $i++;
    }
    //	if ($i == 0)
//		return "FAIL-Invalid Location-".$mDataQuery."-".$mPickDtlLoc."-".$mFromLoc;

    $mDataQuery = "UPDATE pick_detail SET qty_allocated=0,processed=1,last_updated_date=now(),last_updated_by='" . $mCurrentUser . "' WHERE id=" . $mPickDtlId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    mysqli_query($mConnection, $mDataQuery);
    if ($mPickBdyId != "")
    {
        $mDataQuery = "UPDATE pick_body SET qty_allocated=(SELECT (CASE WHEN sum(qty_allocated) IS null THEN 0 ELSE sum(qty_allocated+qty_scanned) END) FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id) WHERE pick_body.id=" . $mPickBdyId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    }
    if ($mInvMisId != null)
        $mDataQuery = "UPDATE inventory_master SET inventory_status_id=" . $mInvMisId . ", allocated_qty=0,available_qty=(inventory_qty-hold_qty),last_updated=now(),last_updated_by='" . $mCurrentUser . "' WHERE id=" . $mPickInvId . " AND inventory_status_id<>" . $mInvPkdId . " LIMIT 1";
    else
        $mDataQuery = "UPDATE inventory_master SET inventory_qty = 0, allocated_qty=0,available_qty=(inventory_qty-hold_qty),last_updated=now(),last_updated_by='" . $mCurrentUser . "' WHERE id=" . $mPickInvId . " AND inventory_status_id<>" . $mInvPkdId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;

    $mReturn = updatePickHistory($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mPickDtlQty, $mCurrentUser, "MISSING", $mFromLocCode, "", $mDocDtlId . "-" . $mPickPriority);

    // Look for alternative Serial
    $mInvQuery = "SELECT inventory_master.id AS id, (inventory_master.inventory_qty-inventory_master.hold_qty) AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, part.conversion_factor AS conversion_factor, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id JOIN pick_group ON location.pick_group_id=pick_group.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=" . $mPartId . " AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND inventory_master.requires_decant<>1 AND lower(location_type.location_area)<>'work' AND lower(location_type.location_type_code)<>'nopick' AND inventory_status.non_pickable<>1 AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) IN('ok','ok')) ORDER BY inventory_master.date_created, inventory_master.location_code,inventory_master.inventory_qty LIMIT 1";
    if (!mysqli_query($mConnection, $mInvQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
    $mInvData = mysqli_query($mConnection, $mInvQuery);
    $mAlcType = "_PICKFACE";
    if (mysqli_num_rows($mInvData) == 0)
    {
        $mInvQuery = "SELECT inventory_master.id AS id, (inventory_master.inventory_qty-inventory_master.hold_qty) AS inventory_qty, inventory_master.allocated_qty AS allocated_qty, part.part_number AS part_number, part.conversion_factor AS conversion_factor, part.rdt_prompt AS rdt_prompt, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, location.location_code AS location_code,location_column.filling_priority AS pick_priority FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN location_column ON location.column_code=location_column.column_code LEFT JOIN location_type ON location.location_type_id=location_type.id WHERE inventory_master.part_id=" . $mPartId . " AND inventory_master.inventory_qty >0 AND inventory_master.inventory_qty>inventory_master.allocated_qty AND inventory_master.requires_inspection=0 AND inventory_master.requires_decant<>1 AND lower(location_type.location_area)<>'work' AND lower(location_type.location_type_code)<>'nopick' AND inventory_status.non_pickable<>1 AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) IN('ok','ok')) ORDER BY inventory_master.date_created, inventory_master.location_code,inventory_master.inventory_qty LIMIT 1";
        if (!mysqli_query($mConnection, $mInvQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mInvQuery;
        $mInvData = mysqli_query($mConnection, $mInvQuery);
        $mAlcType = "_NOTPICKFACE";
    }
    if (!mysqli_query($mConnection, $mInvQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $InvQuery;
    $mData = mysqli_query($mConnection, $mInvQuery);
    $i = 0;
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        if (($mRow['inventory_qty'] > 0) && ($mRow['inventory_qty'] < $mPickDtlQty))
            $mPickDtlQty = $mRow['inventory_qty'];
        if ($mRow['conversion_factor'] > 1)
            $mPickQty = $mPickDtlQty / $mRow['conversion_factor'];
        else
            $mPickQty = $mPickDtlQty;

        $mDataQuery = "UPDATE pick_detail SET inventory_master_id=" . $mRow['id'] . ",part_id=" . $mPartId . ",part_number='" . $mRow['part_number'] . "',serial_reference='" . $mRow['serial_reference'] . "',location_code='" . $mRow['location_code'] . "',qty_allocated=" . $mPickDtlQty . ", processed=0 WHERE id=" . $mPickDtlId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
        // return Pick Details to the screen
        if ($mToPlt == "")
            $mReturn = "DATA:fromLocationHashCodecontent:" . $mRow['location_code'] . ",serialReferencecontent:" . $mRow['serial_reference'] . ",qtyAllocated:" . $mPickQty . ",sequencingRequestId:" . $mRow['id'] . ",boxCountQty::HHHHHHHHHH,toLocationHashCode:,wiCode:,rdtMessage:" . $mRow['rdt_prompt'];
        else
            $mReturn = "DATA:fromLocationHashCodecontent:" . $mRow['location_code'] . ",serialReferencecontent:" . $mRow['serial_reference'] . ",qtyAllocated:" . $mPickQty . ",sequencingRequestId:" . $mRow['id'] . ",boxCountQty::HHHHHHHHHH,toLocationHashCode::HHHHHHHHHH,wiCode::HHHHHHHHHH,rdtMessage:" . $mRow['rdt_prompt'] . "::skipfromLocationHashCode";
        $i++;
    }
    if ($i == 0)
    {
        $mOrdBdyId = "";
        $mDataQuery = "SELECT order_body_id FROM pick_order_link WHERE pick_detail_id=" . $mPickDtlId;
        if (!$mData = mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            if ($mOrdBdyId != "")
                $mOrdBdyId .= "," . $mRow['order_body_id'];
            else
                $mOrdBdyId = $mRow['order_body_id'];
        }
        $mReturn = allocateOrderLines($mHost, $mUser, $mPwd, $mDbName, $mOrderHdrId, $mPickHdrId, $mCurrentUser, $mAisle, $mOrdBdyIds, $mTaskType);
        $mReturn = getPickDetail($mHost, $mUser, $mPwd, $mDbName, $mOrderHdrId, $mPickHdrId, $mAisle, $mDocumentRef, $mPickByLine, $mFullBoxPick, $mToPlt, $mTaskType, $mTrain);
        if (substr($mReturn, 0, 8) == "DATA:EOD")
        {
            // Hard-coding for Kanban Pick
//			if ($mOrderType == "350")
            if ($mTransactionType != "")
            {
                $mReturn = "CHGTRAN:" . $mTransactionType . "?DATA:documentReference:,aisleCode:ALL,toPlt:" . $mToPlt;
            }
            else
            {
                if ($mTransactionType == "")
                    $mTransactionType = "PBTP";
                $mReturn = "CHGTRAN:" . $mTransactionType . "?DATA:documentReference:,aisleCode:ALL,toPlt:" . $mToPlt;
            }
        }
    }
    //	return ("FAIL-TEST1-".$mReturn."-".$mCustomerReference."-".$mPickHdrId);
    if ($mReturn == "")
        $mReturn = "OK  -Missing Serial";
    return ($mReturn);
}

// Flag Inventory / Pick Task as Missing
function postWiCheck($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mSerialReference, $mPickQty, $mAisle, $mCurrentUser, $mTransactionTypeId, $mFromLoc, $mToPlt = "", $mTaskType = "", $mTrain = "")
{
    $mReturn = "";

    if ($mPickQty == null)
        $mPickQty = 0;
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName);
    if (!$mConnection)
        return "FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error();
    // Get id for inventory status
    $mInvStsId = 0;
    $mInvRcvId = 0;
    $mInvPckId = 0;
    $mInvPltId = 0;
    $mInvMisId = 0;
    $mInvWicId = 0;
    $mDataQuery = "SELECT id AS id, inventory_status_code AS inventory_status_code FROM inventory_status";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . $mDataQuery;
    $mData = (mysqli_query($mConnection, $mDataQuery));
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        if (strtolower($mRow['inventory_status_code']) == "ok")
            $mInvStsId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "rcv")
            $mInvRcvId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "pkd")
            $mInvPkdId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "plt")
            $mInvPltId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "missing")
            $mInvMisId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "lost")
            $mInvMisId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "wicheck")
            $mInvWicId = ($mRow['id']);
    }

    $mDataQuery = "SELECT pick_detail.id AS id, pick_detail.pick_body_id AS pick_body_id, pick_detail.document_reference AS document_reference, pick_detail.part_id AS part_id, pick_detail.qty_allocated AS qty_allocated, pick_detail.location_code AS location_code,pick_detail.line_no AS line_no, pick_detail.pick_priority AS pick_priority";
    $mDataQuery .= ",pick_body.order_body_id AS order_body_id";
    $mDataQuery .= " FROM pick_detail";
    $mDataQuery .= " JOIN pick_body ON pick_detail.pick_body_id=pick_body.id";
    $mDataQuery .= " WHERE pick_detail.id=" . $mPickDtlId . " LIMIT 1";
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    $mPickBdyId = "";
    $mPickDtlLoc = "";
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mPickBdyId = $mRow['pick_body_id'];
        $mOrdBdyId = $mRow['order_body_id'];
        $mDocumentRef = $mRow['document_reference'];
        $mPartId = $mRow['part_id'];
        $mPickDtlQty = $mRow['qty_allocated'];
        $mPickDtlLoc = $mRow['location_code'];
        $mDocDtlId = $mRow['line_no'];
        $mPickPriority = $mRow['pick_priority'];
    }

    $mDataQuery = "UPDATE pick_detail SET qty_allocated=0,processed=1,last_updated_date=now(),last_updated_by='" . $mCurrentUser . "_WICHECK' WHERE id=" . $mPickDtlId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    mysqli_query($mConnection, $mDataQuery);
    if ($mPickBdyId != "")
    {
        $mDataQuery = "UPDATE pick_body SET qty_allocated=(SELECT (CASE WHEN sum(qty_allocated) IS null THEN 0 ELSE sum(qty_allocated+qty_scanned) END) FROM pick_detail WHERE pick_detail.pick_body_id=pick_body.id) WHERE pick_body.id=" . $mPickBdyId . " LIMIT 1";
        if (!mysqli_query($mConnection, $mDataQuery))
            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
    }
    if ($mInvWicId != null)
        $mDataQuery = "UPDATE inventory_master SET inventory_status_id=" . $mInvWicId . ", allocated_qty=0,available_qty=(inventory_qty-hold_qty),last_updated=now(),last_updated_by='" . $mCurrentUser . "' WHERE id=" . $mPickInvId . " AND inventory_status_id<>" . $mInvPkdId . " LIMIT 1";
    else
        $mDataQuery = "UPDATE inventory_master SET inventory_qty = 0, allocated_qty=0,available_qty=(inventory_qty-hold_qty),last_updated=now(),last_updated_by='" . $mCurrentUser . "' WHERE id=" . $mPickInvId . " AND inventory_status_id<>" . $mInvPkdId . " LIMIT 1";
    if (!mysqli_query($mConnection, $mDataQuery))
        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;

    $mReturn = updatePickHistory($mHost, $mUser, $mPwd, $mDbName, $mPickDtlId, $mPickInvId, $mPickDtlQty, $mCurrentUser, "WICHECK", $mFromLocCode, "", $mDocDtlId . "-" . $mPickPriority);

    // Look for alternative Serial
    $mOrdBdyId = "";
    $mDataQuery = "SELECT order_body_id FROM pick_order_link WHERE pick_detail_id=" . $mPickDtlId;
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        if ($mOrdBdyId != "")
            $mOrdBdyId .= "," . $mRow['order_body_id'];
        else
            $mOrdBdyId = $mRow['order_body_id'];
    }
    $mReturn = allocateOrderLines($mHost, $mUser, $mPwd, $mDbName, $mOrderHdrId, $mPickHdrId, $mCurrentUser, $mAisle, $mOrdBdyIds, $mTaskType);
    $mReturn = getPickDetail($mHost, $mUser, $mPwd, $mDbName, $mOrderHdrId, $mPickHdrId, $mAisle, $mCurrentUser, $mDocumentRef, $mPickByLine, $mFullBoxPick, $mToPlt, $mTaskType, $mTrain);
    if (substr($mReturn, 0, 8) == "DATA:EOD")
    {
        // Hard-coding for Kanban Pick
        if ($mTransactionType != "")
        {
            //			$mReturn = "CHGTRAN:".$mTransactionType."?DATA:customerReference:,aisleCode:ALL,toPlt:".$mToPlt;
            $mReturn = "OKMS-No Pick Tasks,CHGTRAN:" . $mTransactionType . "?DATA:documentReference:,aisleCode:ALL,toPlt:" . $mToPlt;
        }
        else
        {
            $mTransactionType = "PBTP";
            //			$mReturn = "CHGTRAN:".$mTransactionType."?DATA:customerReference:,aisleCode:ALL,toPlt:".$mToPlt;
            $mReturn = "OKMS-No Pick Tasks,CHGTRAN:" . $mTransactionType . "?DATA:documentReference:,aisleCode:ALL,toPlt:" . $mToPlt;
        }
    }
    if ($mReturn == "")
        $mReturn = "OK  -Wi Check";
    return ($mReturn);
}

// Flag Empty Location
function flagEmptyLocation($mLocationCode, $mCurrentUser, $mHost, $mUser, $mPwd, $mDbName, $mConnect = '')
{
    $mErrMsg = "";
    $mInvCount = 0;

    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        die("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
    $mDataQuery = "";
    // Check for Inventory in Location	
    if ($mLocationCode != "")
    {
        $mDataQuery = "SELECT inventory_master.id AS inv_count FROM inventory_master LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id WHERE inventory_master.location_code='" . $mLocationCode . "' AND inventory_qty > 0 AND (inventory_master.inventory_status_id IS null OR lower(inventory_status.inventory_status_code) NOT IN('pkd','plt')) LIMIT 1";
    }
    if ($mDataQuery != "")
    {
        if (!mysqli_query($mConnection, $mDataQuery))
            exit("FAIL-1" . mysqli_error($mConnection) . "-" . $mDataQuery);
        $mData = mysqli_query($mConnection, $mDataQuery);
        $i = 0;
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mInvCount = ($mRow['inv_count']);
            $i++;
        }
        if ($i == 0)
        {
            if ($mLocationCode != "")
            {
                $mDataQuery = "UPDATE location SET location.filling_status_id=(SELECT id FROM location_filling_status WHERE lower(filling_code) = 'empty') WHERE location.location_code='" . $mLocationCode . "' LIMIT 1";
                if (!mysqli_query($mConnection, $mDataQuery))
                    exit("FAIL-3" . mysqli_error($mConnection) . "-" . $mDataQuery);
            }
        }
    }
    return "OK  -Location Filling Status";
}

function getPickList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mReference, $mKey = null, $mOffset = 0)
{
    $mKey = str_replace("|COMMA|", ",", $mKey);
    $mKey = str_replace("|OPEN|", "(", $mKey);
    $mKey = str_replace("|CLOSE|", ")", $mKey);

    // mysql connection - Use Passed values...
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());

    //	$mDataQuery = "SELECT pick_header.id AS id, pick_header.document_reference AS document_reference FROM order_header JOIN pick_header ON order_header.pick_header_id=pick_header.id LEFT JOIN document_status ON order_header.document_status_id=document_status.id WHERE order_header.pick_header_id IS NOT NULL AND (lower(document_status.document_status_code)='allocated') GROUP BY order_header.pick_header_id ORDER by order_header.pick_header_id DESC LIMIT 50 OFFSET ".$mOffset;

    $mDataQuery = "SELECT pick_header.id AS id, pick_header.document_reference AS document_reference";
    $mDataQuery .= " FROM pick_header";
    $mDataQuery .= " LEFT JOIN document_status ON pick_header.document_status_id=document_status.id";
    $mDataQuery .= " WHERE pick_header.id > 0";
    $mDataQuery .= " AND lower(document_status.document_status_code) IN('open','allocated')";
    if (!empty($mKey))
    {
        if ($mKey === "AAA")
        {
            $mDataQuery .= " AND pick_header.pick_type IN ('AAA') AND (original_pick_header_reference IN (SELECT document_reference FROM pick_header WHERE pick_type <> 'FAB') OR original_pick_header_reference IS NULL)";
            // $mDataQuery .=" AND pick_header.pick_type IN ('AAA') AND pick_header.id IN (SELECT pick_header_id FROM order_header WHERE customer_reference NOT LIKE 'M%')";
        }
        else if ($mKey === 'AAAFAB')
        {
            $mDataQuery .= " AND pick_header.pick_type = 'AAA' AND (original_pick_header_reference IN (SELECT document_reference FROM pick_header WHERE pick_type = 'FAB') OR pick_header.id IN (SELECT pick_header_id FROM order_header WHERE customer_reference LIKE 'M%'))";

            // $mDataQuery .= " AND pick_header.pick_type = 'AAA' AND pick_header.id IN (SELECT pick_header_id FROM order_header WHERE customer_reference LIKE 'M%')";
        }
        else if ($mKey === "REJECT")
        {
            $mDataQuery .= " AND pick_header.pick_type = 'REJECT'";
        }
    }
    $mDataQuery .= " ORDER by pick_header.id DESC LIMIT 50 OFFSET " . $mOffset;

    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
    $mDropdown = array();
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mDropdown[] = array('value' => "documentReference:" . $mRow['document_reference'], 'text' => $mRow['document_reference'], );
    }
    mysqli_close($mConnection);
    return "DROP-" . json_encode($mDropdown);
}

function getTaskTypeList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mReference, $mKey = null, $mOffset = 0)
{
    //	echo("FAIL-TEST3");
// mysql connection - Use Passed values...
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
    $mDataQuery = "SELECT location_type.id AS id,location_type.task_group AS task_group FROM pick_header";
    $mDataQuery .= " JOIN pick_body ON pick_header.id=pick_body.pick_header_id";
    $mDataQuery .= " JOIN part ON pick_body.part_id=part.id";
    $mDataQuery .= " JOIN location_type ON part.fixed_location_type_id=location_type.id";
    $mDataQuery .= " WHERE pick_header.document_reference='$mReference'";
    $mDataQuery .= " AND pick_body.processed<>1";
    if ($mKey == "train")
        $mDataQuery .= " AND location_type.is_train=1";
    $mDataQuery .= " GROUP by location_type.location_type_code";
    $mDataQuery .= " ORDER by location_type.location_type_code LIMIT 100 OFFSET " . $mOffset;
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
    //	exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData)."-".$mKey);
    $mDropdown = array();
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mDropdown[] = array('value' => "taskType:" . $mRow['task_group'], 'text' => $mRow['task_group'], );
    }
    mysqli_close($mConnection);
    return "DROP-" . json_encode($mDropdown);
}

function getTrainList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mReference, $mKey = null, $mOffset = 0)
{
    //	echo("FAIL-TEST3");
// mysql connection - Use Passed values...
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
        exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
    $mDataQuery = "SELECT train.id AS id,train AS train, description AS description FROM train LIMIT 50 OFFSET " . $mOffset;
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
        exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
    //	exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData));
    $mDropdown = array();
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        $mDropdown[] = array('value' => "train:" . $mRow['train'], 'text' => $mRow['description'], );
    }
    mysqli_close($mConnection);
    return "DROP-" . json_encode($mDropdown);
}

function getTrainLocList($mHost,$mUser,$mPwd,$mDbName,$mCurrentUser,$mReference,$mTaskType,$mKey=null,$mOffset=0)
{
    //	echo("FAIL-TEST3");
// mysql connection - Use Passed values...
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
//		$mDataQuery = "SELECT train_locations.id AS id,train_locations.train_location_code AS train_location_code FROM train_locations WHERE train_locations.train_location_code LIKE('".$mReference."%') LIMIT 50 OFFSET ".$mOffset;
	$mDataQuery = "
		SELECT
		train_locations.id AS id,train_locations.train_location_code AS train_location_code
		FROM 
		pick_body
		JOIN part on part.id = pick_body.part_id 
		JOIN location_type on location_type.id = part.fixed_location_type_id
		JOIN train_locations ON train_location_code LIKE(CONCAT(pick_body.document_reference,'%'))
		AND train_location_code LIKE(CONCAT('%',pick_body.zone_destination))
		LEFT JOIN train_locations_status ON train_locations.train_locations_status_id=train_locations_status.id
		JOIN pick_detail ON pick_detail.pick_body_id=pick_body.id
		WHERE pick_body.document_reference='$mReference' AND location_type_code='$mTaskType'
		AND upper(train_locations_status)<>'CLOSED'
		AND location_type.is_train=1
		AND pick_detail.qty_allocated>0
		GROUP BY id,train_location_code
		ORDER BY train_location_code
	";
	
	if (!$mData = mysqli_query($mConnection,$mDataQuery))
		exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
    //	exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData));
    $mDropdown = array();
	while($mRow = mysqli_fetch_assoc($mData)) 
    {
		$mDropdown[] = array('value' => "train:".$mRow['train_location_code'],'text' => $mRow['train_location_code'],);
    }
    mysqli_close($mConnection);
	return "DROP-".json_encode($mDropdown);
}
