<?php
// 
// VIMS RDT transaction Processing
// ----------------------------------------------------------------------------------------
// Modified 2020-03-23 - Initial KUK Version
// 2021-01-19 - postrcts - modular part testing
// 2021-01-27 - postrcts - don't allow overbook to ss type location.
// 2020-09-28 - postRcts - set inventory_master pallet to null
// 2020-09-28 - postPts - set inventory_master pallet to null
// 2020-12-01 - various - post Vendor_code to transaction_history
// 2021-01-04 - various - set RanOrder to NO-RAN if null
// 2021-02-23 - various - post product_type_code to transaction_history
// 2021-03-03 - postaudit - Logging of RDT
// 2021-03-16 - set inventory_status for NO-RAN serials
// 2021-03-19 - various - noran status
// 2021-04-26 - postptm / postpts - populate 'created_by' on transaction_history.
// 2021-05-06 - postTransaction - changes for RTS process.
// 2021-05-18 - postTransaction - force 'unadvised' parts to 'qtine' status.
// 2021-05-26 - potsRcts - change validation for no inventory exists.
// 2021-06-01 - postTransaction write tocase to receipt_body
// 2021-06-03 - postRcts - update fqa_task table
// 2021-06-09 - postRcts - retain 'QTINE' inventory_status.
// 2021-06-14 - postPts - retain 'QTINE' inventory_status.
// 2021-07-27 - sendStckmvMsg - Send Stock Move Message.
// 2022-04-21 - userdevice - record user log-on to RDT device
// ----------------------------------------------------------------------------------------
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);
// Enable exceptions for MySQLi errors
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

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

$mReturnFields = "";

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

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


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

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

if (($mFunction != null) && (strtolower($mFunction) == "postaudit"))
{
    $mStatus = "";
    $mStatus = postAudit($mTableName,$mFilter,$mHost,$mUser,$mPwd,$mDbName,$mTransactionTypeId="AUDIT");
    echo $mStatus;
    return;
}
// Log User / Device
if (($mFunction != null) && (strtolower($mFunction) == "userdevice"))
{
//  exit("FAIL-userdevice".$mFunction);
    $mStatus = "";
    $mStatus = userDevice($mFilter,$mHost,$mUser,$mPwd,$mDbName);
    echo $mStatus;
    return;
}
// Set Inspect Flag
if (($mFunction != null) && (strtolower($mFunction) == "setinspect"))
{
//  exit("FAIL-userdevice".$mFunction);
    $mStatus = "";
    $mStatus = setInspect($mFilter,$mHost,$mUser,$mPwd,$mDbName);
    echo $mStatus;
    return;
}
// Inventory Location Move
if (($mFunction != null) && (strtolower($mFunction) == "postloctoloc"))
{
//  exit("FAIL-userdevice".$mFunction);
    $mStatus = "";
    $mStatus = postLoctoLoc($mFilter,$mHost,$mUser,$mPwd,$mDbName);
    echo $mStatus;
    return;
}

// Inventory tag / serial Move
if (($mFunction != null) && (strtolower($mFunction) == "postplttoloc"))
{
//  exit("FAIL-userdevice".$mFunction);
    $mStatus = "";
    $mStatus = postPlttoLoc($mFilter,$mHost,$mUser,$mPwd,$mDbName);
    echo $mStatus;
    return;
}

function postPtm($mPltNumber,$mToLocHash,$mCurrentUser,$mHost,$mUser,$mPwd,$mDbName)
{
// 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.id AS id, location_code AS location_code";
    $mDataQuery .=",location_type.location_type_code AS location_type_code";
    $mDataQuery .=" FROM location"; 
    $mDataQuery .=" LEFT JOIN location_type ON location.location_type_id=location_type.id";
    $mDataQuery .=" WHERE location.location_hash='".$mToLocHash."'";
    $mDataQuery .=" LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        return "FAIL-Invalid Location Code.";
    $mRow = mysqli_fetch_assoc($mData);
    $mToLocId = ($mRow['id']);
    $mToLocCode = ($mRow['location_code']);
    $mToLocType = $mRow['location_type_code'];
// Get id for inventory status 'ok'
    $mInvStsId = 0;
    $mDataQuery = "SELECT id AS id FROM inventory_status WHERE lower(inventory_status_code) in('ok','noran') LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    $mRow = mysqli_fetch_assoc($mData);
    $mInvStsId = ($mRow['id']);
// Get id for document status
    $mDocStsId = 0;
    $mDocCmpId = 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']) == "complete")
            $mDocCmpId=($mRow['id']);
    }
    if ($mDocCmpId == null)
        $mDocCmpId = 1;
// Get Existing Inventory
    $mDataQuery = "SELECT inventory_master.id AS id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, vendor.vendor_reference_code AS vendor_code, CASE WHEN inventory_master.serial_reference IS NULL THEN pick_detail.serial_reference ELSE inventory_master.serial_reference END AS serial_reference, CASE WHEN order_body.ran_order IS null THEN inventory_master.ran_or_order ELSE order_body.ran_order END AS ran_order, inventory_master.inventory_qty AS inventory_qty";
    $mDataQuery .=", tag.tag_reference AS plt_number, location.location_code AS location_code,product_type.product_type_code AS product_type_code,pick_body.document_reference AS document_reference";
    $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 inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
    $mDataQuery .=" LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id";
    $mDataQuery .=" LEFT JOIN pick_detail ON pick_detail.inventory_master_id=inventory_master.id";
    $mDataQuery .=" LEFT JOIN pick_body ON pick_detail.pick_body_id=pick_body.id";
    $mDataQuery .=" LEFT JOIN order_body ON pick_body.order_body_id=order_body.id";
    $mDataQuery .=" LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id";
    $mDataQuery .=" WHERE (tag.tag_reference='".$mPltNumber."' OR inventory_master.serial_reference = '".$mPltNumber."') AND inventory_qty>0";
    if (!$mData = mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
//  return "FAIL-".mysqli_error($mConnection).$mDataQuery."-".mysqli_num_rows($mData);  
//  $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        return "FAIL-No inventory exists for this pallet or serial reference.";
    $mPlt = "";
    $mDocumentReference = "";
    while($mRow = mysqli_fetch_assoc($mData)) 
    {
        
        $mRanOrder = $mRow['ran_order'];
        $mDocumentReference = $mRow['document_reference'];
        $mInvId = ($mRow['id']);
        $mPlt = ($mRow['plt_number']);
        if (($mDocumentReference != null) && ($mDocumentReference != ""))
            $mTranQuery = "INSERT INTO transaction_history (id,associated_document_reference,short_code,part_number,serial_reference,ran_or_order,txn_qty,source_tag,from_location_code,to_location_code,date_created,created_by,last_updated,last_updated_by,transaction_reference_code,from_plt,conversion_factor,vendor_code,product_type_code) VALUES(DEFAULT,'".$mDocumentReference."','PTM','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".$mRanOrder."',".($mRow['inventory_qty']).",'".($mRow['plt_number'])."','".($mRow['location_code'])."','".$mToLocCode."',now(),'".$mCurrentUser."',now(),'".$mCurrentUser."','PTM','".$mRow['plt_number']."',".$mRow['conversion_factor'].",'".$mRow['vendor_code']."','".$mRow['product_type_code']."')";   
        else
            $mTranQuery = "INSERT INTO transaction_history (id,associated_document_reference,short_code,part_number,serial_reference,ran_or_order,txn_qty,source_tag,from_location_code,to_location_code,date_created,created_by,last_updated,last_updated_by,transaction_reference_code,from_plt,conversion_factor,vendor_code,product_type_code) VALUES(DEFAULT,null,'PTM','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".$mRanOrder."',".($mRow['inventory_qty']).",'".($mRow['plt_number'])."','".($mRow['location_code'])."','".$mToLocCode."',now(),'".$mCurrentUser."',now(),'".$mCurrentUser."','PTM','".$mRow['plt_number']."',".$mRow['conversion_factor'].",'".$mRow['vendor_code']."','".$mRow['product_type_code']."')";    
        if (!mysqli_query($mConnection,$mTranQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;      
        
        if ($mInvId != "")
        {
            $mInvQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',last_updated=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mInvId." LIMIT 1";
//          $mInvQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',parent_tag_id=null, tag_reference='',last_updated=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mInvId." LIMIT 1";
            if (!mysqli_query($mConnection,$mInvQuery))
                return "FAIL-".mysqli_error($mConnection).$mInvQuery;
// Marshal location_type
            if (strtolower($mToLocType) == 'maxdpt')
            {
                try
                {
                    $mDataQuery = "DELETE FROM inventory_master WHERE id=".$mInvId." LIMIT 1";
                    mysqli_query($mConnection,$mDataQuery);
                }
                catch(Exception $e)
                {
                    return "FAIL-".mysqli_error($mConnection).$mDataQuery;
                }
            }
        }       
    }
    return("OK  -locate Complete");
}

// Set Location Filling Status
function locationFillingSts($mFromLocationId,$mFromLocationCode,$mToLocationId,$mToLocationCode,$mHost,$mUser,$mPwd,$mDbName)
{
    $mInvCount = 0;
// mysql connection
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        die("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error()."-".$mHost."-".$mUser."-".$mPwd."-".$mDbName);
    $mDataQuery = "";
// Check for Inventory in Location  
    if ($mFromLocationId != "")
    {
        $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.current_location_id=".$mFromLocationId." 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";
    }
    elseif ($mFromLocationCode != "")
    {
        $mDataQuery = "SELECT inventory_master.id AS inv_count FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id WHERE (location.location_code='".$mFromLocationCode."' OR location.location_hash='".$mFromLocationCode."') AND inventory_master.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))
            return("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 (($mFromLocationId == "") && ($mFromLocationCode != ""))
            {
                $mDataQuery = "SELECT location.id AS id FROM location WHERE (location_hash ='".$mFromLocationCode."' OR location_code = '".$mFromLocationCode."') LIMIT 1";
                if (!$mData = mysqli_query($mConnection,$mDataQuery))
                    return("FAIL-2".mysqli_error($mConnection)."-".$mDataQuery);
                $i=0;
                while($mRow = mysqli_fetch_assoc($mData)) 
                {
                    $mFromLocationId = ($mRow['id']);
                    $i++;
                }
            }

            if ($mFromLocationId != "")
            {
                $mDataQuery = "UPDATE location SET location.filling_status_id=(SELECT id FROM location_filling_status WHERE lower(filling_code) = 'empty') WHERE location.id=".$mFromLocationId." LIMIT 1";
                if (!mysqli_query($mConnection,$mDataQuery))
                    return ("FAIL-3".mysqli_error($mConnection)."-".$mDataQuery);
            }
        }
    }
// To Location
    if (($mToLocationId != "") || ($mToLocationCode != ""))
    {
// Check for Inventory in Location
        $mDataQuery = "";
        if ($mToLocationId != "")
        {
            $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.current_location_id=".$mToLocationId." 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";
        }
        elseif ($mToLocationCode != "")
        {
            $mDataQuery = "SELECT inventory_master.id AS inv_count FROM inventory_master JOIN location ON inventory_master.current_location_id=location.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id WHERE (location.location_code='".$mToLocationCode."' OR location.location_hash='".$mToLocationCode."') AND inventory_master.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 (!$mData = mysqli_query($mConnection,$mDataQuery))
                return("FAIL-4".mysqli_error($mConnection)."-".$mDataQuery);
        
            $i = 0;
            while($mRow = mysqli_fetch_assoc($mData)) 
            {
                $mInvCount = ($mRow['inv_count']);
                $i++;
            }
            if ($i > 0)
            {
                if (($mToLocationId == "") && ($mToLocationCode != ""))
                {
                    $mDataQuery = "SELECT location.id AS id FROM location WHERE (location_hash ='".$mToLocationCode."' OR location_code = '".$mToLocationCode."') LIMIT 1";
                    if (!$mData = mysqli_query($mConnection,$mDataQuery))
                        return("FAIL-5".mysqli_error($mConnection)."-".$mDataQuery);
                    $i=0;
                    while($mRow = mysqli_fetch_assoc($mData)) 
                    {
                        $mToLocationId = ($mRow['id']);
                        $i++;
                    }
                }

                if ($mToLocationId != "")
                {
                    $mDataQuery = "SELECT id, filling_code FROM location_filling_status";
                    if (!$mData = mysqli_query($mConnection,$mDataQuery))
                        return("FAIL-6".mysqli_error($mConnection)."-".$mDataQuery);
                    $i=0;
                    $mFillId = 0;
                    $mFullId = 0;
                    $mClosedId = 0;
                    $mQtineId = 0;
                    $mNotIn = "";
                    while($mRow = mysqli_fetch_assoc($mData)) 
                    {
                        if (strtolower($mRow['filling_code']) == "filling")
                            $mFillId = ($mRow['id']);
                        if (strtolower($mRow['filling_code']) == "full")
                            $mFullId = ($mRow['id']);
                        if (strtolower($mRow['filling_code']) == "closed")
                            $mClosedId = ($mRow['id']);
                        if (strtolower($mRow['filling_code']) == "qtine")
                            $mQtineId = ($mRow['id']);
                        $i++;
                    }
                    $mDataQuery = "UPDATE location SET location.filling_status_id=".$mFillId." WHERE location.id=".$mToLocationId;                  
                    if ($mFullId != 0)
                        $mDataQuery .= " AND location.filling_status_id <> ".$mFullId;
                    if ($mClosedId != 0)
                        $mDataQuery .= " AND location.filling_status_id <> ".$mClosedId;
                    if ($mQtineId != 0)
                        $mDataQuery .= " AND location.filling_status_id <> ".$mQtineId;
                    $mDataQuery .= " LIMIT 1";
                    if (!mysqli_query($mConnection,$mDataQuery))
                        return ("FAIL-7".mysqli_error($mConnection)."-".$mDataQuery);
                }       
            }
        }
    }
//  return ("FAIL-TEST1-".$mDataQuery."-".$mToLocationId."-".$mToLocationCode);
    return("OK  -Location Filling Status");
}

// Create / Return Pallet Tag
function processPlt($mPlt,$mVirtualPalletLbl,$mTransactionTypeId,$mPltSts,$mCurrentUser,$mHost,$mUser,$mPwd,$mDbName)
{
//  $mParms = json_decode($mJsonObject, true);
// Blank Pallet
    if (($mVirtualPalletLbl == "0") && ($mPlt == ""))
        return("FAIL-Invalid Pallet");
// Check Pallet Type
    if ((substr($mPlt,0,3) == "PCK" && substr($mTransactionTypeId,0,2) != "PB"))
        return("FAIL-Invalid Pallet Type");
    elseif ((substr($mPlt,0,3) == "RCV" && substr($mTransactionTypeId,0,2) != "RB"))
        return("FAIL-Invalid Pallet Type");
    elseif ((substr($mPlt,0,3) == "RXD" && substr($mTransactionTypeId,0,3) != "JRB"))
        return("FAIL-Invalid Pallet Type");
    elseif ((substr($mPlt,0,3) == "REP" && substr($mTransactionTypeId,0,3) != "REPLEN"))
        return("FAIL-Invalid Pallet Type");
    
// mysql connection - Use Passed values...
        $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
            exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
    
// Check Pallet Status
    if ($mPlt != "" && $mPltSts != "")
    {
        $mDataQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_status_id AS inventory_status_id, inventory_status.inventory_status_code AS inventory_status_code FROM inventory_master LEFT JOIN tag on inventory_master.parent_tag_id=tag.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id WHERE inventory_status.inventory_status_code='".$mPltSts."' AND (tag.tag_reference='".$mPlt."' OR inventory_master.serial_reference='".$mPlt."') LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-1".mysqli_error($mConnection)."-".$mDataQuery;             
        $mData = mysqli_query($mConnection,$mDataQuery);
        if (mysqli_num_rows($mData) == 0)
        {
            $mDataQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_status_id AS inventory_status_id, inventory_status.inventory_status_code AS inventory_status_code FROM inventory_master LEFT JOIN tag on inventory_master.parent_tag_id=tag.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id WHERE tag.tag_reference='".$mPlt."' OR inventory_master.serial_reference='".$mPlt."' LIMIT 1";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-1".mysqli_error($mConnection)."-".$mDataQuery;             
            $mData = mysqli_query($mConnection,$mDataQuery);
        }       
        $mInvId = "";
        $mInvStsId = "";
        $mInvStsCode = "";
        $mRow = mysqli_fetch_assoc($mData);
        if (mysqli_num_rows($mData) > 0)
        {
            $mInvStsId = ($mRow['inventory_status_id']);
            $mInvStsCode = ($mRow['inventory_status_code']);
// Check Inventory Status of the Pallet
            if (strtolower($mInvStsCode) != strtolower($mPltSts))
            {
                return "FAIL-This Pallet is in Use.";
            }
        }       
    }
// Test for Existing Pallet / Tag
    if ($mPlt != "")
    {
// Select by Serial
//      $mDataQuery = "SELECT inventory_master.parent_tag_id AS parent_tag_id, tag.tag_reference AS tag_reference FROM inventory_master LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE (inventory_master.serial_reference='".$mPlt."' OR inventory_master.serial_reference='".substr($mPlt,1)."') LIMIT 1";
        $mDataQuery = "SELECT inventory_master.parent_tag_id AS parent_tag_id, tag.tag_reference AS tag_reference FROM inventory_master LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE (inventory_master.serial_reference='".$mPlt."') LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-2".mysqli_error($mConnection)."-".$mDataQuery;             
        $mData = mysqli_query($mConnection,$mDataQuery);
        if (mysqli_num_rows($mData) == 0)
        {
//          $mDataQuery = "SELECT scan_data.plt_reference AS tag_reference FROM scan_data WHERE (scan_data.serial_reference='".$mPlt."' OR scan_data.serial_reference='".substr($mPlt,1)."') LIMIT 1";
            $mDataQuery = "SELECT scan_data.plt_reference AS tag_reference FROM scan_data WHERE (scan_data.serial_reference='".$mPlt."') LIMIT 1";
            if (!$mData = mysqli_query($mConnection,$mDataQuery))
                return "FAIL-3".mysqli_error($mConnection)."-".$mDataQuery;             
//          $mData = mysqli_query($mConnection,$mDataQuery);
        }
        $mTagRef = "";
        $mRow = mysqli_fetch_assoc($mData); 
        $mTagRef = ($mRow['tag_reference']);
        if ($mTagRef != "")
        {
            return($mTagRef);
        }
        
        $mDataQuery = "SELECT id FROM tag WHERE tag_reference='".$mPlt."' AND (active IS null OR active = '1') LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-3".mysqli_error($mConnection)."-".$mDataQuery;
                
        $mData = mysqli_query($mConnection,$mDataQuery);
        $mTagId = "";
        $mRow = mysqli_fetch_assoc($mData); 
        $mTagId = ($mRow['id']);
        if ($mTagId == "")
        {
            $mDataQuery = "INSERT INTO tag (id,version,auto_generated,current_location_id,date_created,document_header_id,last_updated,last_updated_by,tag_reference,validated,active,picked) VALUES(DEFAULT,0,0,null,now(),null,now(),'".$mCurrentUser."','".$mPlt."',1,1,0)";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-4".mysqli_error($mConnection).$mDataQuery;
        }
        return($mPlt);
    }
// Create Virtual Pallet / Tag
    else
    {
        $mDataQuery = "INSERT INTO tag (id,version,auto_generated,current_location_id,date_created,document_header_id,last_updated,last_updated_by,tag_reference,validated,active,picked) VALUES(DEFAULT,0,0,null,now(),null,now(),'".$mCurrentUser."','PLT000000',1,1,0)";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
        $mTagId=mysqli_insert_id($mConnection);
        $mTagReference = "000000".round($mTagId);
        if (substr($mTransactionTypeId,0,2) == "PB")
            $mTagReference = "PCK".substr($mTagReference,-6);
        elseIf (substr($mTransactionTypeId,0,2) == "RB")
            $mTagReference = "RCV".substr($mTagReference,-6);
        elseIf (substr($mTransactionTypeId,0,3) == "JRB")
            $mTagReference = "RXD".substr($mTagReference,-6);
        elseIf (substr($mTransactionTypeId,0,3) == "REP")
            $mTagReference = "REP".substr($mTagReference,-6);
        else
            $mTagReference = "PLT".substr($mTagReference,-6);
        $mDataQuery = "UPDATE tag SET tag_reference='".$mTagReference."' WHERE id=".$mTagId." LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-5".mysqli_error($mConnection).$mDataQuery;
        return($mTagReference);
    }
}

// Create Receipt Header for unadvised Items
function addReceiptHeader($mcustomerRef,$mDelNote,$mTransactionTypeId,$mCurrentUser,$mHost,$mUser,$mPwd,$mDbName)
{
    if (($mcustomerRef == "") || ($mDelNote == ""))
        Return "FAIL-Invalid customer or Delivery Note";
// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());

// Get id for inventory status 'ok'
    $mInvStsId = 0;
    $mInvRcvId = 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']) == "noran")
            $mInvNrId = ($mRow['id']);
    }

// Get id for document status
    $mDocStsId = 0;
    $mDocCmpId = 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']) == "complete")
            $mDocCmpId=($mRow['id']);
    }
    if ($mDocCmpId == null)
        $mDocCmpId = 1;
    
// check for invalid customer / Del.note
    $mDocHdrId = "";
    $mDataQuery = "SELECT id AS id, vendor_id AS vendor_id, customer_reference AS customer_reference, haulier_reference AS haulier_reference, expected_delivery_date AS expected_delivery_date FROM receipt_header WHERE customer_reference = '".$mcustomerRef."' AND document_status_id IN(0,".$mDocStsId.",".$mDocCmpId.") LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        return("FAIL-Invalid customer Reference for Delivery Note.");

    $mRow = mysqli_fetch_assoc($mData);
    $mVendorId=($mRow['vendor_id']);
    $mExpectedDelivery = ($mRow['expected_delivery_date']);
    $mCustomerRef = ($mRow['customer_reference']);
    $mHaulierRef = ($mRow['haulier_reference']);
    
// Create Receipt Header
    if ((!$mVendorId) || ($mVendorId == null) || ($mVendorId == ""))
    {
        $mDataQuery = "INSERT INTO receipt_header (id,document_reference,customer_reference,delivery_note,vendor_id,haulier_reference,expected_delivery_date,document_status_id,date_created,created_by,last_updated_date,last_updated_by)";
        $mDataQuery .= " VALUES(DEFAULT,'RCP000000','".$mcustomerRef."','".$mDelNote."',null,'".$mHaulierRef."','".$mExpectedDelivery."',".$mDocStsId.",now(),'".$mCurrentUser."',now(),'".$mCurrentUser."')";
    }
    else
    {
        $mDataQuery = "INSERT INTO receipt_header (id,document_reference,customer_reference,delivery_note,vendor_id,haulier_reference,expected_delivery_date,document_status_id,date_created,created_by,last_updated_date,last_updated_by)";
        $mDataQuery .= " VALUES(DEFAULT,'RCP000000','".$mcustomerRef."','".$mDelNote."',".$mVendorId.",'".$mHaulierRef."','".$mExpectedDelivery."',".$mDocStsId.",now(),'".$mCurrentUser."',now(),'".$mCurrentUser."')";
    }
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mDocHdrId=mysqli_insert_id($mConnection);
    $mDocReference = "000000".round($mDocHdrId);
    $mDocReference = "RCP".substr($mDocReference,-6);
    $mDataQuery = "UPDATE receipt_header SET document_reference='".$mDocReference."' WHERE id=".$mDocHdrId." LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;

    return("OK  -");
}

function postRcts($mJsonObject,$mConnect,$mHost,$mUser,$mPwd,$mDbName)
{
//  exit("FAIL-TEST1-".$mJsonObject);
    $mParms = json_decode($mJsonObject, true);
    $mTransactionTypeId = $mParms['transactiontypeid'];
    
// mysql connection - Use Passed values...
        $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
            exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());

// Get Company Settings
    $mCloseReceipt = 0;
    $mDataQuery = "SELECT company.id AS id, company.auto_close_receipt AS auto_close_receipt FROM company LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    while($mRow = mysqli_fetch_assoc($mData))
    {
        $mCloseReceipt = $mRow['auto_close_receipt'];
    }
    if ($mCloseReceipt == null)
        $mCloseReceipt = 0;
    
// Get id for document status
    $mDocStsId = 0;
    $mDocClsId = 0;
    $mDocCmpId = 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']) == "closed")
            $mDocClsId=($mRow['id']);
        if (strtolower($mRow['document_status_code']) == "complete")
            $mDocCmpId=($mRow['id']);
    }
    if ($mDocClsId == null)
        $mDocClsId = 1;
    if ($mDocCmpId == null)
        $mDocCmpId = 1;
    
// Get id for inventory status
    $mInvStsId = 0;
    $mInvRcvId = 0;
    $mInvPkdId = 0;
    $mInvPltId = 0;
    $mInvMisId = 0;
    $mInvQaId = 0;
    $mInvNrId = 0;
    $mInvOvRId = 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']) == "qa")
            $mInvQaId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "qtine")
            $mInvQaId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "missing")
            $mInvMisId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "noran")
            $mInvNrId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "overbooking")
            $mInvOvrId = ($mRow['id']);
    }
    if ($mInvPkdId == null && $mInvPltId != null)
        $mInvPkdId = $mInvPltId;
    if (($mInvNrId == 0) || ($mInvNrId == null))
        $mInvNrId = $mInvStsId;
    
    $mDataQuery = "SELECT location.id AS id, location.location_code AS location_code, location.inventory_status_id AS inventory_status_id, location.part_number AS part_number, location.pick_group_id AS pick_group, location.max_weight AS max_weight, location.location_type_id AS location_type_id, location.location_sub_type_id location_sub_type_id";
    $mDataQuery .=", location_type.location_area AS location_area, location_type.location_type_code AS location_type_code";
    $mDataQuery .=" FROM location";
    $mDataQuery .=" LEFT JOIN location_type ON location.location_type_id=location_type.id";
    $mDataQuery .=" WHERE location.location_hash='".$mParms['tolocationhashcode']."' OR location.location_code='".$mParms['tolocationhashcode']."'";
    $mDataQuery .=" LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-1".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
    {
        return "FAIL-Invalid Location Code".$mDataQuery;
    }
    $mLocWeight = 0;
    $mRow = mysqli_fetch_assoc($mData);
    $mToLocId = ($mRow['id']);
    $mToLocCode = ($mRow['location_code']);
    $mToLocInvId = ($mRow['inventory_status_id']);
    $mLocType = ($mRow['location_area']);
    $mLocPart = ($mRow['part_number']);
    $mLocTypeCode = ($mRow['location_type_code']);
    $mLocTypeId = ($mRow['location_type_id']);
    $mLocSubTypeId = ($mRow['location_sub_type_id']);
    $mLocWeight = ($mRow['max_weight']);
    
// Test for Fixed-Part Location
    if ($mToLocCode != $mParms['locationcode'])
    {
        $mDataQuery = "SELECT location.id AS id, location.pick_group_id AS pick_group, location.part_number AS part_number FROM location WHERE location.location_code='".$mParms['locationcode']."' LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-1".mysqli_error($mConnection).$mDataQuery;
        $mData = (mysqli_query($mConnection,$mDataQuery));
        $mRow = mysqli_fetch_assoc($mData);
        if (($mRow['pick_group'] != null) && ($mRow['part_number'] != null && $mRow['part_number'] != "" && $mRow['part_number'] != $mLocPart))
        {
            return "FAIL-Incorrect Location for Fixed Part";
        }
    }
    
    $mRctDtlId = "";
    $mRctCtsQty = 0;
    $mRctHdrId = "";
    $mTxnQty = 0;
    $mPartId = "";
    $mConvFactor = 1;
    $mModular = 0;
    $mPartWeight = 0;
// Get Part
    if (!empty($mParms['partnumber']))
    {    
        
        $mDataQuery = "SELECT part.id AS id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, part.modular AS modular, part.weight AS weight, vendor.vendor_reference_code AS vendor_code FROM part LEFT JOIN vendor ON part.vendor_id=vendor.id WHERE (part.part_number = '".$mParms['partnumber']."') LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-2".mysqli_error($mConnection).$mDataQuery; 
        $mData = (mysqli_query($mConnection,$mDataQuery)); 
        if (mysqli_num_rows($mData) == 0)
        {
            return("FAIL-Invalid Part Number");
        }
        while($mRow = mysqli_fetch_assoc($mData))
        {
            $mPartId = ($mRow['id']);
            $mConvFactor = ($mRow['conversion_factor']);
            $mVendorCode = ($mRow['vendor_code']);
            $mPartNumber = ($mRow['part_number']);
            $mModular = ($mRow['modular']);
            $mPartWeight = ($mRow['weight']);
        }
    }
    if (empty($mPartWeight))
    {
        $mPartWeight = 999999;
    }
    
// Check Location Current Loading
    if (!empty($mToLocCode))
    {
        $mCurrWeight = 0;
        
        $mDataQuery = "SELECT sum(part.weight) AS current_weight";
        $mDataQuery .=" FROM inventory_master";
        $mDataQuery .=" LEFT JOIN part ON inventory_master.part_id=part.id";
        $mDataQuery .=" WHERE";
        $mDataQuery .=" (inventory_master.location_code=?)";
        $mDataQuery .=" AND";
        $mDataQuery .=" inventory_master.inventory_qty>0";
        $mDataQuery .=" GROUP BY location_code";
        $mDataQuery .=" LIMIT 1";
        
        try
        {
            $mStmt = $mConnection->prepare($mDataQuery);
            $mStmt->bind_param("s", $mToLocCode);
            $mStmt->execute();
            $mData = $mStmt->get_result();
            $mRows = $mData->fetch_all(MYSQLI_ASSOC);
        }
        catch (Exception $e)
        {
            exit("FAIL-".$e." ".mysqli_error($mConnection)." ".$mDataQuery);
        }
        $mStmt->close();
        
        foreach ($mRows as $row)
        {
            $mCurrWeight = $row['current_weight'];
        }
        if (empty($mCurrWeight))
        {
            $mCurrWeight = 0;
        }
        $mCurrWeight += $mPartWeight;
        if ($mLocWeight-$mCurrWeight < 0)
        {
            exit("FAIL-This part exceeds the location weight");
        }
//      exit("FAIL-TEST1 ".$mLocWeight-$mCurrWeight);
    }

// Validate Scanned Qty against Rec.Qty.    
    if (($mParms['quantity']) && ($mParms['quantity'] != null) && ($mParms['quantity'] != ""))
    {
// Test for modular part
        if (($mTransactionTypeId == "CTS") || ($mTransactionTypeId == "CTS"))
        {
            if ($mModular == 1)
            {
                return ("FAIL-Modular Part - use PTS ");
            }
        }
// Validate Scanned Qty against Rec.Qty.
        $mRctDtlQty = "";
        $mcustomerRef = "";
        $mDataQuery = "SELECT receipt_header.id AS hdr_id, receipt_header.document_reference AS document_reference, receipt_header.customer_reference AS customer_reference, receipt_header.jis_ind AS jis_ind,receipt_body.quarantined_qty AS quarantined_qty, receipt_detail.id AS id, receipt_detail.received_qty AS received_qty FROM receipt_detail JOIN receipt_body ON receipt_detail.receipt_body_id=receipt_body.id JOIN receipt_header ON receipt_body.receipt_header_id=receipt_header.id WHERE receipt_detail.part_number='".$mParms['partnumber']."' AND receipt_detail.serial_reference='".$mParms['serialreference']."' AND receipt_header.document_status_id=".$mDocStsId." LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-3".mysqli_error($mConnection).$mDataQuery;
        $mData = (mysqli_query($mConnection,$mDataQuery));
        if (mysqli_num_rows($mData) == 0)
        {
            $mDataQuery = "SELECT receipt_header.id AS hdr_id, receipt_header.document_reference AS document_reference, receipt_header.customer_reference AS customer_reference, receipt_header.jis_ind AS jis_ind,receipt_body.quarantined_qty AS quarantined_qty, receipt_detail.id AS id, receipt_detail.received_qty AS received_qty FROM receipt_detail JOIN receipt_body ON receipt_detail.receipt_body_id=receipt_body.id JOIN receipt_header ON receipt_body.receipt_header_id=receipt_header.id WHERE receipt_detail.part_number='".$mParms['partnumber']."' AND receipt_detail.serial_reference='".$mParms['serialreference']."' ORDER BY receipt_detail.id DESC LIMIT 1";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-3".mysqli_error($mConnection).$mDataQuery;
            $mData = (mysqli_query($mConnection,$mDataQuery));
        }
        $mRow = mysqli_fetch_assoc($mData);
        $mRctDtlId = ($mRow['id']);
        $mRctDtlQty = ($mRow['received_qty']);
        $mRctQaQty = ($mRow['quarantined_qty']);
        $mcustomerRef = ($mRow['customer_reference']);
        $mRctHdrId = ($mRow['hdr_id']);
        $mRctDocRef = ($mRow['document_reference']);
        $mRctJisInd = ($mRow['jis_ind']);
        
//      if (($mRctDtlQty != "") && (intval($mParms['quantity']*$mConvFactor) != intval($mRctDtlQty)))
//      $mConvQty = $mRctDtlQty/$mConvFactor;
//      if ($mConvQty != $mParms['quantity'])
//      {
//          return "FAIL-Scanned Qty does not match Receipt Qty.";
//      }
// Store Converted Qty. 
        if ($mParms['quantity'])
        {
            $mRctCtsQty = ($mParms['quantity']*$mConvFactor);
            $mTxnQty = ($mParms['quantity']*$mConvFactor);
        }
        else
        {
            $mRctCtsQty = 0;
            $mTxnQty = 0;
        }
    }
    
// Check for valid Inventory
    $mDataQuery = "SELECT inventory_master.id AS id,inventory_master.serial_reference AS serial_reference,inventory_master.ran_or_order AS ran_order,inventory_master.inventory_qty AS inventory_qty,inventory_master.inventory_status_id AS inventory_status_id";
    $mDataQuery .=",part.part_number AS part_number,part.conversion_factor AS conversion_factor,part.is_big_part AS big_part, part.fixed_location_type_id AS fixed_location_type_id, part.fixed_sub_type_id AS fixed_sub_type_id";
    $mDataQuery .=",vendor.vendor_reference_code AS vendor_code";
    $mDataQuery .=",tag.tag_reference AS plt_number";
    $mDataQuery .=",location.location_code AS location_code";
    $mDataQuery .=",location_type.locate_overbooking AS locate_overbooking";
    $mDataQuery .=",product_type.product_type_code AS product_type_code";
    $mDataQuery .=" FROM inventory_master";
    $mDataQuery .=" JOIN part ON inventory_master.part_id=part.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";
    $mDataQuery .=" LEFT JOIN vendor ON part.vendor_id=vendor.id";
    $mDataQuery .=" LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
    $mDataQuery .=" LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id";
    $mDataQuery .=" LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id";
    if (($mParms['partnumber'] == "") || ($mParms['partnumber'] == null))
    {
        $mDataQuery .=" WHERE inventory_master.serial_reference='".$mParms['serialreference']."' AND inventory_qty>0";
    }
    else
    {
        $mDataQuery .=" WHERE (part.part_number='".$mParms['partnumber']."') AND inventory_master.serial_reference='".$mParms['serialreference']."' AND inventory_qty>0";
    }
//  if ($mTransactionTypeId == "CTS")
//      $mDataQuery .= " AND inventory_master.inventory_status_id <> ".$mInvRcvId;
    $mDataQuery .= " LIMIT 1";  
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        return "FAIL-No Available inventory exists for this serial reference";
    $mInvId = "";
    $mBigPart = 0;
    while($mRow = mysqli_fetch_assoc($mData)) 
    {
        $mPartNumber = $mRow['part_number'];
        $mVendorCode = $mRow['vendor_code'];
        $mConvFactor = $mRow['conversion_factor'];
        if ($mRctCtsQty == 0)
            $mRctCtsQty = ($mRow['inventory_qty']);
        $mTxnQty = $mRctCtsQty;
        $mInvId = ($mRow['id']);
        $mInvInvId = ($mRow['inventory_status_id']);
        $mFromLocCode = ($mRow['location_code']);
        $mRanOrder = ($mRow['ran_order']);
        $mProductType = ($mRow['product_type_code']);
        $mBigPart = ($mRow['big_part']);
        $mLocOverBook = ($mRow['locate_overbooking']);
        $mFromLocCode = $mRow['location_code'];
        $mPltNumber = $mRow['plt_number'];
        $mFixedTypeId = $mRow['fixed_location_type_id'];
        $mFixedSubTypeId = $mRow['fixed_sub_type_id'];
    }
// mixed location types are not allowed
    if (strtolower($mLocType) == "storage")
    {
        if ($mLocTypeId != $mFixedTypeId)
        {
            exit ("FAIL-This part does not match the location settings");
        }
        if ($mLocSubTypeId != $mFixedSubTypeId)
        {
            exit ("FAIL-This part does not match the location settings");
        }
    }
// CTS not allowed for inventory_status RCV
    if ($mTransactionTypeId == "CTS" && $mInvInvId==$mInvRcvId)
    {
        return "FAIL-This Serial cannot be moved until receipt is closed";
    }
// over-booked stock is not allowed into 'SS' store
    if (strtolower($mRow['inventory_status_code']) == "overbooking")
    {
//      if (strtolower($mLocTypeCode) == "ss")
        if ($mLocOverBook != 1)
        {
            return "FAIL-Overbooked stock cannot go to this location";
        }
    }
    if (strtolower($mLocTypeCode) == "qa")
        $mTransactionTypeId = "TQA";
    if ($mConvFactor > 0)
        $mTranQuery = "INSERT INTO transaction_history (id,short_code,part_number,serial_reference,ran_or_order,txn_qty,source_tag,from_location_code,to_location_code,date_created,created_by,last_updated,last_updated_by,transaction_reference_code,conversion_factor) 
                        VALUES(DEFAULT,'".$mTransactionTypeId."','".$mPartNumber."','".$mParms['serialreference']."','".$mRanOrder."',".$mTxnQty.",'".$mPltNumber."','".$mFromLocCode."','".$mToLocCode."',now(),'".$mParms['currentuser']."',now(),'".$mParms['currentuser']."','".$mParms['transactiontypeid']."',".$mConvFactor.")";   
    else
        $mTranQuery = "INSERT INTO transaction_history (id,short_code,part_number,serial_reference,ran_or_order,txn_qty,source_tag,from_location_code,to_location_code,date_created,created_by,last_updated,last_updated_by,transaction_reference_code,conversion_factor) 
                        VALUES(DEFAULT,'".$mTransactionTypeId."','".$mPartNumber."','".$mParms['serialreference']."','".$mRanOrder."',".$mTxnQty.",'".$mPltNumber."','".$mFromLocCode."','".$mToLocCode."',now(),'".$mParms['currentuser']."',now(),'".$mParms['currentuser']."','".$mParms['transactiontypeid']."',1)";  
    if (!mysqli_query($mConnection,$mTranQuery))
        return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;

// Inventory_Status
//    If ($mInvInvId != $mInvPkdId && $mInvInvId != $mInvQaId && $mInvInvId != $mInvOvrId && $mToLocInvId != 0 && !empty($mToLocInvId))
//        $mInvInvId = $mToLocInvId;
// NORAN
//  if (($mRanOrder == "NORAN" || $mRanOrder == "NO-RAN") && ($m1Tier !=1) && (strtolower($mProductType)=="w1-prd") && ($mInvNrId != 0))
//      $mInvInvId = $mInvNrId;
// Full Box Pick
    if ($mInvId != "")
    {
        if ($mParms['transactiontypeid'] == "FQA")
            $mDataQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',last_updated=now(),last_updated_by='".$mParms['currentuser']."', inventory_status_id=".$mInvInvId.", requires_inspection=0";
        elseif ($mParms['transactiontypeid'] == "TQA")
            $mDataQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',last_updated=now(),last_updated_by='".$mParms['currentuser']."', inventory_status_id=".$mInvInvId.", requires_inspection=1";
        else
            $mDataQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',last_updated=now(),last_updated_by='".$mParms['currentuser']."', inventory_status_id=".$mInvInvId.",parent_tag_id=null,tag_reference=null"; 
        if ((strtolower($mLocType) == "storage") && ($mFromLocCode == "PICK01"))
            $mDataQuery .= ",ran_or_order=null, parent_tag_id=null,tag_reference=null";
//      if ($mParms('transactiontypeid') == "CTL")
//          $mDataQuery .= ",ran_or_order=null, parent_tag_id=null,tag_reference=null";
        else
            $mDataQuery .= ",parent_tag_id=null,tag_reference=null";
        $mDataQuery .= " WHERE id=".$mInvId." LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    }
// SCRAP location_type
    if (strtolower($mLocTypeCode) == 'maxdpt')
    {
        try
        {
            $mDataQuery = "DELETE FROM inventory_master WHERE id=".$mInvId." LIMIT 1";
            mysqli_query($mConnection,$mDataQuery);
        }
        catch(Exception $e)
        {
            return "FAIL-".mysqli_error($mConnection).$mDataQuery;
        }
    }
    if ($mRctDtlId != "")
    {
        try
        {
            $mDataQuery = "UPDATE receipt_detail SET cts_qty=".$mRctCtsQty.",last_updated_date=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mRctDtlId." LIMIT 1";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
        }
        catch(Exception $e)
        {
            return("FAIL-CTS Qty.");
        }
    }
// Flag fqa_task as processed
    if ($mParms['transactiontypeid'] == "FQA")
    {
        if ($mParms['documentreference'])
        {
            $mDataQuery = "UPDATE fqa_tasks SET processed=1,last_updated_date=now(),last_updated_by='".$mParms['currentuser']."' WHERE document_reference='".$mParms['documentreference']."' AND part_number='".$mParms['partnumber']."' AND serial_reference='".$mParms['serialreference']."' AND processed=0 LIMIT 1";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-".mysqli_error($mConnection).$mDataQuery;
        }
    }
// Flag Receipt if All CTS is complete
//  $mDataQuery = "SELECT receipt_header.id AS id FROM receipt_header JOIN receipt_body ON receipt_body.receipt_header_id=receipt_header.id JOIN receipt_detail ON receipt_detail.receipt_body_id=receipt_body.id WHERE receipt_header.customer_reference='".$mcustomerRef."' AND (receipt_body.received_qty<receipt_body.advised_qty OR (receipt_detail.received_qty=0 OR receipt_detail.received_qty<>receipt_detail.cts_qty)) LIMIT 1";
    if ($mRctHdrId != "")
    {
        $mDataQuery = "SELECT receipt_header.id AS id FROM receipt_header JOIN receipt_body ON receipt_body.receipt_header_id=receipt_header.id LEFT JOIN receipt_detail ON receipt_detail.receipt_body_id=receipt_body.id WHERE receipt_header.id=".$mRctHdrId." AND (receipt_body.received_qty<receipt_body.advised_qty OR (receipt_detail.received_qty IS null OR receipt_detail.received_qty=0 OR receipt_detail.received_qty<>receipt_detail.cts_qty)) LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-".mysqli_error($mConnection).$mDataQuery;
        $mData = (mysqli_query($mConnection,$mDataQuery));
        if (mysqli_num_rows($mData) == 0)
        {
// Get id for document status 'complete'        
            $mDataQuery = "SELECT id AS id FROM document_status WHERE lower(document_status_code) = 'complete' LIMIT 1";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-".mysqli_error($mConnection).$mDataQuery;
            $mData = (mysqli_query($mConnection,$mDataQuery));
            $mRow = mysqli_fetch_assoc($mData);
            $mDocStsId = ($mRow['id']);
            $mDataQuery = "UPDATE receipt_header SET document_status_id=".$mDocStsId.",last_updated_date=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mRctHdrId." LIMIT 1";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;

// Close receipt_body 
            $mDataQuery = "SELECT receipt_body.id AS id FROM receipt_body WHERE receipt_body.receipt_header_id=".$mRctHdrId;
            if (!$mData = mysqli_query($mConnection,$mDataQuery))
                exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
            while($mRow = mysqli_fetch_assoc($mData)) 
            {
                $mBdyId = $mRow['id'];
                
                $mUpdQuery = "UPDATE receipt_body SET document_status_id=".$mDocStsId." WHERE id=".$mBdyId." LIMIT 1";
                if (!mysqli_query($mConnection,$mUpdQuery))
                    exit ("FAIL-".mysqli_error($mConnection)."-".$mUpdQuery);
// Flag inventory_master & receipt_detail
                $mDtlQuery = "SELECT receipt_detail.id AS id FROM receipt_detail WHERE receipt_detail.receipt_body_id=".$mBdyId;
                if (!$mDtlData = mysqli_query($mConnection,$mDtlQuery))
                    exit ("FAIL-".mysqli_error($mConnection)."-".$mDtlQuery);
                while($mDtlRow = mysqli_fetch_assoc($mDtlData)) 
                {
                    $mDtlId = $mDtlRow['id'];
                    $mUpdQuery = "UPDATE receipt_detail SET document_status_id=".$mDocStsId." WHERE id=".$mDtlId." LIMIT 1";
                    if (!mysqli_query($mConnection,$mUpdQuery))
                        exit ("FAIL-".mysqli_error($mConnection)."-".$mUpdQuery);
                }
            }
            
// Send IDOC Trigger for Confirm Receipt
//          http://van-kuk-test:8093/vantec/writeCompleteReceiptMessage Input - {"documentReference":"RCP000001","userId":"admin"}
// Get Server Data from URL
            $mJsonArray = array();
            $mJsonArray['documentReference'] = $mRctDocRef;
            $mJsonArray['userId'] = $mParms['currentuser'];
// 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",":8093/vantec",$mConnect);
            if ($mConnectp == $mConnect)
            {
                $e = strpos($mConnect,":8081");
                $mConnectp = substr($mConnect,0,$e).":8093/vantec/";
            }
            $ch = curl_init();
            try
            {
                if ($mJisInd != "")
                    curl_setopt($ch, CURLOPT_URL, $mConnectp."writeCompleteJisReceiptMessage?documentReference=".$mRctDocRef);
                else
                    curl_setopt($ch, CURLOPT_URL, $mConnectp."writeCompleteReceiptMessage?documentReference=".$mRctDocRef."&userId=".$mParms['currentuser']);
                curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json')); 
                curl_setopt($ch, CURLOPT_POST, 1);
                curl_setopt($ch, CURLOPT_POSTFIELDS, $mJsonObject);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                $mResult = curl_exec($ch);
                $mErrors = curl_error($ch);
                $mResponse = curl_getinfo($ch, CURLINFO_HTTP_CODE);

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

    $mReturn = flagEmptyLocation($mFromLocCode,$mParms['currentuser'],$mHost,$mUser,$mPwd,$mDbName);
    
    return("OK  -locate Complete");
}

function postPiecePick($mJsonObject,$mConnect,$mHost,$mUser,$mPwd,$mDbName)
{
    $mParms = json_decode($mJsonObject, true);
    $mPartNumber = $mParms['partnumber'];
    
    if ($mParms['ranorder'])
        $mRanOrder = $mParms['ranorder'];
// mysql connection - Use Passed values...
        $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
            exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());

// Get Company Settings
    $mCloseReceipt = 0;
    $mDataQuery = "SELECT company.id AS id, company.auto_close_receipt AS auto_close_receipt FROM company LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    while($mRow = mysqli_fetch_assoc($mData))
    {
        $mCloseReceipt = $mRow['auto_close_receipt'];
    }
    if ($mCloseReceipt == null)
        $mCloseReceipt = 0;

// Get id for document status
    $mDocStsId = 0;
    $mDocClsId = 0;
    $mDocCmpId = 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']) == "closed")
            $mDocClsId=($mRow['id']);
        if (strtolower($mRow['document_status_code']) == "complete")
            $mDocCmpId=($mRow['id']);
    }
    if ($mDocClsId == null)
        $mDocClsId = 1;
    if ($mDocCmpId == null)
        $mDocCmpId = 1;
    
// Get id for inventory status
    $mInvStsId = 0;
    $mInvRcvId = 0;
    $mInvPkdId = 0;
    $mInvPltId = 0;
    $mInvMisId = 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']) == "gi")
            $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']) == "qa")
            $mInvQaId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "qtine")
            $mInvQaId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "noran")
            $mInvNrId = ($mRow['id']);
    }
    if ($mInvPkdId == null && $mInvPltId != null)
        $mInvPkdId = $mInvPltId;

    if ($mInvStsId == null)
        $mInvStsId = 0;
    if ($mInvRcvId == null)
        $mInvRcvId = 0;
    if ($mInvPkdId == null)
        $mInvPkdId = 0;
    if ($mInvPltId == null)
        $mInvPltId = 0;
    if ($mInvMisId == null)
        $mInvMisId = 0;
    if ($mInvQaId == null)
        $mInvQaId = 0;
    if ($mInvNrId == null)
        $mInvNrId = 0;
    
    $mDataQuery = "SELECT id AS id, location_code AS location_code FROM location WHERE location.location_hash='".$mParms['tolocationhashcode']."' OR location.location_code='".$mParms['tolocationhashcode']."' LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-1".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
    {
        return "FAIL-Invalid Location Code".$mDataQuery;
    }
    $mRow = mysqli_fetch_assoc($mData);
    $mToLocId = ($mRow['id']);
    $mToLocCode = ($mRow['location_code']);
    $mRctDtlId = "";
    $mRctCtsQty = 0;
    $mRctHdrId = "";
    $mTxnQty = 0;
    $mPartId = "";
    if (($mParms['quantity']) && ($mParms['quantity'] != null) && ($mParms['quantity'] != ""))
    {
// Get Part Conversion Factor
        $mConvFactor = 1;
        $mVendorCode = "";
        $mDataQuery = "SELECT part.id AS id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, vendor.vendor_reference_code AS vendor_code FROM part LEFT JOIN vendor ON part.vendor_id=vendor.id WHERE part_number = '".$mParms['partnumber']."' LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-2".mysqli_error($mConnection).$mDataQuery; 
        $mData = (mysqli_query($mConnection,$mDataQuery));
        if (mysqli_num_rows($mData) == 0)
        {
            return("FAIL-Invalid Part Number");
        }
        while($mRow = mysqli_fetch_assoc($mData))
        {
            $mPartId = ($mRow['id']);
            $mConvFactor = ($mRow['conversion_factor']);
            $mVendorCode = ($mRow['vendor_code']);
            $mPartNumber = ($mRow['part_number']);
        }
// Store Converted Qty.     
        $mQty = ($mParms['quantity']*$mConvFactor);
        $mTxnQty = ($mParms['quantity']*$mConvFactor);  
        
// Validate Scanned Qty against Rec.Qty.
        $mRctDtlQty = "";
        $mcustomerRef = "";
        $mDataQuery = "SELECT receipt_header.id AS hdr_id, receipt_header.document_reference AS document_reference, receipt_header.customer_reference AS customer_reference, receipt_header.jis_ind AS jis_ind, receipt_detail.id AS id, receipt_detail.received_qty AS received_qty FROM receipt_detail JOIN receipt_body ON receipt_detail.receipt_body_id=receipt_body.id JOIN receipt_header ON receipt_body.receipt_header_id=receipt_header.id WHERE receipt_detail.part_number='".$mParms['partnumber']."' AND receipt_detail.serial_reference='".$mParms['serialreference']."' LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-3".mysqli_error($mConnection).$mDataQuery;
        $mData = (mysqli_query($mConnection,$mDataQuery));
        $mRow = mysqli_fetch_assoc($mData);
        $mRctDtlId = ($mRow['id']);
        $mRctDtlQty = ($mRow['received_qty']);
        $mcustomerRef = ($mRow['customer_reference']);
        $mRctHdrId = ($mRow['hdr_id']);
        $mRctDocRef = ($mRow['document_reference']);
        $mRctJisInd = ($mRow['jis_ind']);
        $mRctSts = ($mRow['receipt_status']);
        
// If not auto_receipt Putaway not allowed on open receipt
        if ($mCloseReceipt == 0 && ($mRctSts == $mDocStsId || $mRctSts == $mDocCmpId))
        {
            return "FAIL-Receipt-".$mHaulierRef." is not closed.";
        }
        
        if (($mRctDtlQty != "") && ($mQty > $mRctDtlQty))
        {
            return "FAIL-Scanned Qty does not match Receipt Qty.";
        }       
    }
    
// Check for valid Inventory
    $mDataQuery = "SELECT inventory_master.id AS id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, vendor.vendor_reference_code AS vendor_code,inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, inventory_master.inventory_qty AS inventory_qty,inventory_master.product_type_id AS product_type_id, inventory_master.pruf_cube AS pruf_cube, tag.tag_reference AS plt_number, location.location_code AS location_code,product_type.product_type_code";
    $mDataQuery .=" FROM inventory_master";
    $mDataQuery .=" JOIN part ON inventory_master.part_id=part.id";
    $mDataQuery .=" LEFT JOIN location ON inventory_master.current_location_id=location.id";
    $mDataQuery .=" LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
    $mDataQuery .=" LEFT JOIN tag ON inventory_master.parent_tag_id=tag.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 (part.part_number='".$mParms['partnumber']."') AND inventory_master.serial_reference='".$mParms['serialreference']."' AND inventory_master.inventory_status_id IN(0,".$mInvStsId.",".$mInvRcvId.",".$mInvMisId.",".$mInvNrId.") AND inventory_qty>0 LIMIT 1";  
    if (!$mData = mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
//  $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        return "FAIL-No inventory exists for this serial reference.";
    $mInvId = "";
    while($mRow = mysqli_fetch_assoc($mData)) 
    {
        $mInvQty = ($mRow['inventory_qty']);
        if ($mQty == 0)
            $mQty = ($mRow['inventory_qty']);
        
        $mInvId = ($mRow['id']);
        $mInvPrdId = $mRow['product_type_id'];
        $mInvPruf = $mRow['pruf_cube'];
        $mRanOrOrder = $mRow['ran_order'];
        $mConvFactor = ($mRow['conversion_factor']);
        $mVendorCode = ($mRow['vendor_code']);
        $mPartNumber = ($mRow['part_number']);
        
// Invalid Scan Qty.
        if (($mQty == 0) || ($mQty > $mInvQty))
        {
            return("FAIL-Invalid Quantity");
        }
        
        $mTxnQty = $mQty;
        if ($mConvFactor > 0)
            $mTranQuery = "INSERT INTO transaction_history (id,short_code,part_number,serial_reference,ran_or_order,txn_qty,source_tag,from_location_code,to_location_code,date_created,created_by,last_updated,last_updated_by,transaction_reference_code,conversion_factor,vendor_code,product_type_code) VALUES(DEFAULT,'".$mParms['transactiontypeid']."','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".$mParms['customerreference']."',".$mTxnQty.",'".($mRow['plt_number'])."','".($mRow['location_code'])."','".$mToLocCode."',now(),'".$mParms['currentuser']."',now(),'".$mParms['currentuser']."','".$mParms['transactiontypeid']."',".$mConvFactor.",'".$mVendorCode."','".$mRow['product_type_code']."')";  
        else
            $mTranQuery = "INSERT INTO transaction_history (id,short_code,part_number,serial_reference,ran_or_order,txn_qty,source_tag,from_location_code,to_location_code,date_created,created_by,last_updated,last_updated_by,transaction_reference_code,conversion_factor,vendor_code,product_type_code) VALUES(DEFAULT,'".$mParms['transactiontypeid']."','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".$mParms['customerreference']."',".$mTxnQty.",'".($mRow['plt_number'])."','".($mRow['location_code'])."','".$mToLocCode."',now(),'".$mParms['currentuser']."',now(),'".$mParms['currentuser']."','".$mParms['transactiontypeid']."',1,'".$mVendorCode."','".$mRow['product_type_code']."')"; 
        if (!mysqli_query($mConnection,$mTranQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;
    }
    if ($mInvId != "")
    {
// full box transaction
        if ($mInvQty <= $mQty)
        {
            if ($mInvPkdId <> 0)
                $mDataQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',last_updated=now(),last_updated_by='".$mParms['currentuser']."', inventory_status_id=".$mInvPkdId.", ran_or_order='".$mParms['customerreference']."' WHERE id=".$mInvId." LIMIT 1";
            else
                $mDataQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',last_updated=now(),last_updated_by='".$mParms['currentuser']."', inventory_status_id=".$mInvStsId.", ran_or_order='".$mParms['customerreference']."' WHERE id=".$mInvId." LIMIT 1";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-".mysqli_error($mConnection).$mDataQuery;
        }
        else
        {
            if (($mRanOrder == null) || ($mRanOrder == ""))
                    $mRanOrder = "NO-RAN";
            if ($mInvPkdId == 0)
                $mInvPkdId = $mInvStsId;
            if (($mInvPruf == null) || ($mInvPruf != 0 && $mInvPruf != 1))
                $mInvPruf = 0;
            if ($mInvPrdId != null)
                $mDataQuery = "INSERT INTO inventory_master (id,version,allocated_qty,hold_qty,current_inventory_status_id,current_location_id,location_id,location_code,date_created,created_by,expiry_date,inventory_qty,available_qty,last_updated,last_updated_by,parent_tag_id,part_id,part_number,ran_or_order,recorded_missing,requires_decant,requires_inspection,serial_reference,originating_detail_id,conversion_factor,stock_date,active,inventory_status_id,product_type_id,pruf_cube) VALUES(DEFAULT,0,0,0,null,".$mToLocId.",".$mToLocId.",'".$mToLocCode."',now(),'".$mParms['currentuser']."',null,".$mQty.",".$mQty.",now(),'".$mParms['currentuser']."',null,".$mPartId.",'".$mParms['partnumber']."','".$mParms['customerreference']."',0,0,0,null,null,".$mConvFactor.",now(),1,".$mInvPkdId.",".$mInvPrdId.",".$mInvPruf.")";
            else
                $mDataQuery = "INSERT INTO inventory_master (id,version,allocated_qty,hold_qty,current_inventory_status_id,current_location_id,location_id,location_code,date_created,created_by,expiry_date,inventory_qty,available_qty,last_updated,last_updated_by,parent_tag_id,part_id,part_number,ran_or_order,recorded_missing,requires_decant,requires_inspection,serial_reference,originating_detail_id,conversion_factor,stock_date,active,inventory_status_id,product_type_id,pruf_cube) VALUES(DEFAULT,0,0,0,null,".$mToLocId.",".$mToLocId.",'".$mToLocCode."',now(),'".$mParms['currentuser']."',null,".$mQty.",".$mQty.",now(),'".$mParms['currentuser']."',null,".$mPartId.",'".$mParms['partnumber']."','".$mParms['customerreference']."',0,0,0,null,null,".$mConvFactor.",now(),1,".$mInvPkdId.",null,".$mInvPruf.")";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-".mysqli_error($mConnection).$mDataQuery;
            $mDataQuery = "UPDATE inventory_master SET inventory_qty=inventory_qty-".$mTxnQty.", allocated_qty = CASE WHEN allocated_qty>inventory_qty THEN inventory_qty ELSE allocated_qty+0 END, available_qty=inventory_qty-(allocated_qty+hold_qty),last_updated=now(),last_updated_by='".$mParms['currentuser']."', inventory_status_id=".$mInvStsId." WHERE id=".$mInvId." LIMIT 1";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-".mysqli_error($mConnection).$mDataQuery;
        }
    }
    if ($mRctDtlId != "")
    {
        try
        {
            $mDataQuery = "UPDATE receipt_detail SET cts_qty=".$mQty.",last_updated_date=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mRctDtlId." LIMIT 1";
            if (!mysqli_query($mConnection,$mDataQuery))
                return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
        }
        catch(Exception $e)
        {
            return("FAIL-Receipt Qty.");
        }
    }
    return("OK  -locate Complete");
}

function postPts($mPltNumber,$mPartNumber,$mToLocHash,$mCurrentUser,$mHost,$mUser,$mPwd,$mDbName,$mClosePlt='Y')
{
// 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.id AS id, location.location_code AS location_code, location.multi_part_location AS multi_part_location, location.location_type_id AS location_type_id, location.location_sub_type_id AS location_sub_type_id";
    $mDataQuery .=", location_type.location_type_code AS location_type_code";
    $mDataQuery .=" FROM location";
    $mDataQuery .=" JOIN location_type ON location.location_type_id=location_type.id";
    $mDataQuery .=" WHERE location.location_hash='".$mToLocHash."' LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        return "FAIL-Invalid Location Code.";
    $mRow = mysqli_fetch_assoc($mData);
    $mToLocId = ($mRow['id']);
    $mToLocCode = ($mRow['location_code']);
    $mMultiPart = ($mRow['multi_part_location']);
    $mLocTypeId = ($mRow['location_type_id']);
    $mLocSubTypeId = ($mRow['location_sub_type_id']);
    $mLocTypeCode = ($mRow['location_type_code']);
    
// If Drop Zone retain Pallet.    
    if ($mLocTypeCode == "DZ")
        $mClosePlt = "N";

// Get id for inventory status
    $mInvStsId = 0;
    $mInvRcvId = 0;
    $mInvPkdId = 0;
    $mInvPltId = 0;
    $mInvMisId = 0;
    $mInvQaId = 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']) == "qa")
            $mInvQaId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "qtine")
            $mInvQaId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "missing")
            $mInvMisId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "noran")
            $mInvNrId = ($mRow['id']);
        elseif (strtolower($mRow['inventory_status_code']) == "overbooking")
            $mInvOvrId = ($mRow['id']);
    }
    if ($mInvPkdId == null && $mInvPltId != null)
        $mInvPkdId = $mInvPltId;
    if (($mInvNrId == 0) || ($mInvNrId == null))
        $mInvNrId = $mInvStsId;
        
// Get id for document status
    $mDocStsId = null;
    $mDocCmpId = null;
    $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']) == "complete")
            $mDocCmpId=($mRow['id']);
    }
    if ($mDocCmpId == null)
        $mDocCmpId = 1;

// Get Pallet or Serial No.
    $mDataQuery = "SELECT tag_reference AS tag_reference, part_id AS part_id FROM inventory_master WHERE tag_reference='".$mPltNumber."' LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData)==0)
    {
        $mDataQuery = "SELECT tag_reference AS tag_reference, part_id AS part_id FROM inventory_master WHERE serial_reference='".$mPltNumber."' LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-".mysqli_error($mConnection).$mDataQuery;
        $mData = (mysqli_query($mConnection,$mDataQuery));
    }
    while($mRow = mysqli_fetch_assoc($mData))
    {
        $mPartId = $mRow['part_id'];
// Replace Serial Reference with tag_reference
        if (($mRow['tag_reference'] != null) && ($mRow['tag_reference'] != ""))
            $mPltNumber = $mRow['tag_reference'];
    }

// Test for mixed pallet / location
    if ($mMultiPart == 0)
    {
        $mDataQuery = "SELECT id FROM inventory_master WHERE (tag_reference='".$mPltNumber."' OR current_location_id=".$mToLocId.") AND part_id<>".$mPartId." LIMIT 1";
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-".mysqli_error($mConnection).$mDataQuery;
        $mData = (mysqli_query($mConnection,$mDataQuery));
        if (mysqli_num_rows($mData) > 0)
            return "FAIL-Mixed Parts are not allowed.";
    }
// Get Existing Inventory
    $mDataQuery = "SELECT inventory_master.id AS id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, part.modular AS modular, vendor.vendor_reference_code AS vendor_code, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, inventory_master.inventory_qty AS inventory_qty, inventory_master.inventory_status_id AS inventory_status_id, tag.tag_reference AS plt_number, location.location_code AS location_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 inventory_status ON inventory_master.inventory_status_id=inventory_status.id";
    $mDataQuery .=" LEFT JOIN tag ON inventory_master.parent_tag_id=tag.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 (tag.tag_reference='".$mPltNumber."' OR inventory_master.serial_reference = '".$mPltNumber."') AND inventory_qty>0";
    if ($mPartNumber != "" && $mPartNumber != null)
        $mDataQuery .= " AND (part.part_number='".$mPartNumber."')"; 
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        return "FAIL-No inventory exists for this pallet or serial reference.";
    $mPlt = "";
    while($mRow = mysqli_fetch_assoc($mData)) 
    {
        if ($mRow['modular'] == true)
            $mClosePlt = "N";
        $mPartNumber = $mRow['part_number'];
// Check That Receipt is closed
        $mJisInd = "";
        $mRctQuery = "SELECT receipt_header.id AS id, receipt_header.document_status_id AS document_status_id, receipt_header.jis_ind AS jis_ind, part.part_number AS part_number,product_type.product_type_code AS product_type_code";
        $mRctQuery .=" FROM receipt_header";
        $mRctQuery .=" JOIN receipt_body ON receipt_body.receipt_header_id=receipt_header.id";
        $mRctQuery .=" JOIN receipt_detail ON receipt_detail.receipt_body_id=receipt_body.id";
        $mRctQuery .=" JOIN part ON receipt_body.part_id=part.id";
        $mRctQuery .=" JOIN product_type ON receipt_detail.product_type_id=product_type.id";
        $mRctQuery .=" WHERE (part.part_number='".$mPartNumber."') AND receipt_detail.serial_reference='".$mRow['serial_reference']."' LIMIT 1";
        if (!mysqli_query($mConnection,$mRctQuery))
            return "FAIL-".mysqli_error($mConnection).$mRctQuery;
        $mRctData = (mysqli_query($mConnection,$mRctQuery));
        if (mysqli_num_rows($mRctData) > 0)
        {
            $mRctRow = mysqli_fetch_assoc($mRctData);
            if (($mRctRow['document_status_id'] == $mDocStsId) || ($mRctRow['document_status_id'] == $mDocCmpId))
                return "FAIL-Receipt is not closed";
//              return "FAIL-Receipt is not closed".$mRctRow['document_status_id']."-".$mDocStsId."-".$mRctQuery."-".$mRctRow['id'];
            $mJisInd = $mRctRow['jis_ind'];
            $mPartNumber = $mRctRow['part_number'];
        }
// Update Tables
        $mInvId = ($mRow['id']);
        $mPlt = ($mRow['plt_number']);
        $mInvInvId = ($mRow['inventory_status_id']);
        $mTranQuery = "INSERT INTO transaction_history (id,short_code,part_number,serial_reference,ran_or_order,txn_qty,source_tag,from_location_code,to_location_code,date_created,last_updated,last_updated_by,transaction_reference_code,from_plt) VALUES(DEFAULT,'PTS','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".($mRow['ran_order'])."',".($mRow['inventory_qty']).",'".($mRow['plt_number'])."','".($mRow['location_code'])."','".$mToLocCode."',now(),now(),'".$mCurrentUser."','PTS','".$mRow['plt_number']."')";   
        if (!mysqli_query($mConnection,$mTranQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;  
        
        if ($mInvId != "")
        {
// Inventory_Status
            If (($mInvInvId != $mInvPkdId && $mInvInvId != $mInvQaId) && ($mToLocInvId != 0) && ($mToLocInvId != null))
                $mInvInvId = $mToLocInvId;
            if ((substr($mPlt,0,3) == "RXD") || ($mJis_Ind == "1"))
                $mInvQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',last_updated=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mInvId." LIMIT 1";
            else
//              $mInvQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',last_updated=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mInvId." LIMIT 1";
                $mInvQuery = "UPDATE inventory_master SET current_location_id=".$mToLocId.",location_id=".$mToLocId.",location_code='".$mToLocCode."',inventory_status_id=".$mInvInvId.",last_updated=now(),last_updated_by='".$mParms['currentuser']."'";
                if ($mClosePlt == "Y")
                    $mInvQuery .= ",parent_tag_id=null, tag_reference=null";
                $mInvQuery .= " WHERE id=".$mInvId." LIMIT 1";
            if (!mysqli_query($mConnection,$mInvQuery))
                return "FAIL-".mysqli_error($mConnection).$mInvQuery;
        }       
    }
    return("OK  -locate Complete");
}

// General Transaction Processing
function postTransaction($mJsonObject,$mConnect,$mHost,$mUser,$mPwd,$mDbName)
{
    $mTime = date("H-i-s");
    $mPartNumber = "";
    $mReturn = "";
    $mParms = json_decode($mJsonObject, true);

  // print_r('FAIL-TEST1-' . $mJsonObject);

    $mRanOrder = null;
    if ($mParms['ranorder'])
    {
        $mRanOrder = $mParms['ranorder'];
    }

    if (($mRanOrder == null) || ($mRanOrder == ""))
    {
        $mRanOrder = "NO-RAN";
    }
    
    $mConnection = mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
//  return "FAIL-TEST1-".$mJsonObject;
    if ($mParms['transactiontypeid'])
    {
        
// Part Number      
        if (($mParms['partnumber'] != "") || ($mParms['partnumber'] == ""))
        {
            $mPartNumber = $mParms['partnumber'];
//          return "FAIL-TEST1";

// Test for Valid Part Number
            $mDataQuery = "SELECT id FROM part WHERE part_number='{$mPartNumber}' LIMIT 1";

            if (!mysqli_query($mConnection,$mDataQuery))
            {
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
            }

            $mData = mysqli_query($mConnection,$mDataQuery);

            if (mysqli_num_rows($mData) == 0)
            {
                return "FAIL-Incorrect Part Number";
            }
        }
        $mPartNumber = $mParms['partnumber'];

// Test for valid serial
        if (($mParms['serialreference']) && ($mParms['serialreference']==""))
        {
            return "FAIL-Incorrect Serial Reference".$mJsonObject;
        }

// Get id for inventory status 'ok'
        $mInvStsId = 0;
        $mInvQaId = 0;
        $mInvRcvId = 0;
        $mInvOvrId = 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']) == "qa")
                $mInvQaId = ($mRow['id']);
            elseif (strtolower($mRow['inventory_status_code']) == "qtine")
                $mInvQaId = ($mRow['id']);
            elseif (strtolower($mRow['inventory_status_code']) == "gi")
                $mInvRcvId = ($mRow['id']);
            elseif (strtolower($mRow['inventory_status_code']) == "overbooking")
                $mInvOvrId = ($mRow['id']);
            elseif (strtolower($mRow['inventory_status_code']) == "noran")
                $mInvNrId = ($mRow['id']);
        }

        if ($mInvStsId == null)
            $mInvStsId = 0;
        if ($mInvRcvId == null)
            $mInvRcvId = 0;
        if ($mInvQaId == null)
            $mInvQaId = 0;
        if ($mInvOvrId == null)
            $mInvOvrId = 0;
        if ($mInvNrId == null)
            $mInvNrId = 0;

// Get id for document status
        $mDocStsId = 0;
        $mDocCmpId = 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']) == "complete")
                $mDocCmpId=($mRow['id']);
        }

        if ($mDocStsId == null)
            $mDocStsId = 0;
        if ($mDocCmpId == null)
            $mDocCmpId = 0;
        
// Get id for product type
        $mPrufId = 0;
        $mProdId = 0;
        $mDataQuery = "SELECT id AS id, product_type_code AS product_type_code FROM product_type";

        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['product_type_code']) == "prod")
                $mProdId = ($mRow['id']);
            elseif (strtolower($mRow['product_type_code']) == "prufcube")
                $mPrufId = ($mRow['id']);
        }

        if ($mProdId == null)
        {
            $mProdId = 0;
        }

        if ($mPrufId == null)
        {
            $mPrufId = 0;
        }

// Validate customer Reference
        $mDocumentHdrId = "";
        $mDocumentReference = "";
        if (($mParms['customerreference']) && ($mParms['customerreference'] != ""))
        {
            if ($mParms['transactiontypeid'] == "JRBTP")
            {
                $mDataQuery = "SELECT
                        receipt_header.id AS id,
                        receipt_header.document_reference AS document_reference,
                        receipt_header.delivery_note AS delivery_note,
                        receipt_body.quarantined_qty AS quarantined_qty
                    FROM receipt_header
                        JOIN receipt_body ON receipt_header.id = receipt_body.receipt_header_id
                        JOIN receipt_detail ON receipt_body.id = receipt_detail.receipt_body_id
                    WHERE 
                        customer_reference = '{$mParms['customerreference']}'
                        AND receipt_body.part_number = '{$mParms['partnumber']}'
                        AND receipt_detail.serial_reference = '{$mParms['serialreference']}'
                        AND receipt_header.document_status_id IN (0,{$mDocStsId},{$mDocCmpId})
                    LIMIT 1";

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

// If no matching JIS Receipt Look for Part & Serial on unmatched Receipt               
                if (mysqli_num_rows($mData) == 0)
                {
                    $mDataQuery = "SELECT
                            receipt_header.id AS id,
                            receipt_header.document_reference AS document_reference,
                            receipt_header.delivery_note AS delivery_note,
                            receipt_body.quarantined_qty AS quarantined_qty
                        FROM receipt_header 
                            JOIN receipt_body ON receipt_header.id = receipt_body.receipt_header_id
                            JOIN receipt_detail ON receipt_body.id = receipt_detail.receipt_body_id
                        WHERE 
                            receipt_body.part_number = '{$mParms['partnumber']}'
                            AND receipt_detail.serial_reference='{$mParms['serialreference']}'
                            AND (customer_reference IS NULL OR customer_reference <> '{$mParms['customerreference']}')
                            AND receipt_header.document_status_id IN (0,{$mDocStsId},{$mDocCmpId})
                        LIMIT 1";

                    if (!mysqli_query($mConnection,$mDataQuery))
                    {
                        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                    }

                    $mData = mysqli_query($mConnection,$mDataQuery);
                    if (mysqli_num_rows($mData) > 0)
                    {
                        return "FAIL-This Part and Serial is on a different Load";
                    }
                }
            }
            else
            {
                if (($mParms['deliverynote']) && ($mParms['deliverynote'] != ""))
                {
//                  $mDataQuery = "SELECT receipt_header.id AS id, receipt_header.document_reference AS document_reference, receipt_header.delivery_note AS delivery_note FROM receipt_header WHERE customer_reference='".($mParms['customerreference'])."' AND delivery_note='".($mParms['deliverynote'])."' AND receipt_header.document_status_id IN(0,".$mDocStsId.",".$mDocCmpId.") LIMIT 1";
                    $mDataQuery = "SELECT
                            receipt_header.id AS id,
                            receipt_header.document_reference AS document_reference,
                            receipt_header.delivery_note AS delivery_note,
                            receipt_body.quarantined_qty AS quarantined_qty
                        FROM receipt_header
                            JOIN receipt_body ON receipt_body.receipt_header_id = receipt_header.id
                        WHERE 
                            receipt_header.customer_reference = '{$mParms['customerreference']}'
                            AND receipt_header.delivery_note = '{$mParms['deliverynote']}'
                            AND receipt_body.part_number = '{$mPartNumber}'
                            AND receipt_header.document_status_id IN (0,{$mDocStsId},{$mDocCmpId})
                        LIMIT 1";

                    if (!mysqli_query($mConnection,$mDataQuery))
                    {
                        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                    }

                    $mData = mysqli_query($mConnection,$mDataQuery);
                    if (mysqli_num_rows($mData) == 0)
                    {
                        $mDataQuery = "SELECT receipt_header.id AS id, receipt_header.document_reference AS document_reference, receipt_header.delivery_note AS delivery_note FROM receipt_header WHERE customer_reference='" . ($mParms['customerreference']) . "' AND delivery_note='" . ($mParms['deliverynote']) . "' AND receipt_header.document_status_id IN(0," . $mDocStsId . "," . $mDocCmpId . ") LIMIT 1";
                    }
                }
                else
                {
                    $mDataQuery = "SELECT
                            receipt_header.id AS id,
                            receipt_header.document_reference AS document_reference,
                            receipt_header.delivery_note AS delivery_note,
                            receipt_body.quarantined_qty AS quarantined_qty
                        FROM receipt_header
                            JOIN receipt_body ON receipt_body.receipt_header_id = receipt_header.id
                            JOIN part ON receipt_body.part_id = part.id
                        WHERE 
                            receipt_header.customer_reference = '{$mParms['customerreference']}'
                            AND part.part_number = '{$mPartNumber}'
                            AND receipt_header.document_status_id IN (0,{$mDocStsId},{$mDocCmpId})
                        LIMIT 1";
                }

                if (!mysqli_query($mConnection,$mDataQuery))
                {
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                }

                $mData = mysqli_query($mConnection,$mDataQuery);
                if (mysqli_num_rows($mData) == 0)
                {
                    $mDataQuery = "SELECT
                            receipt_header.id AS id,
                            receipt_header.document_reference AS document_reference,
                            receipt_header.delivery_note AS delivery_note
                        FROM receipt_header
                        WHERE
                            receipt_header.customer_reference = '{$mParms['customerreference']}'
                            AND receipt_header.document_status_id IN (0{$mDocStsId},{$mDocCmpId})
                        LIMIT 1";

                    if (!mysqli_query($mConnection,$mDataQuery))
                    {
                        return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                    }
                    $mData = mysqli_query($mConnection,$mDataQuery);
                }
            }
            $mRcvQaQty = 0;
            while($mRow = mysqli_fetch_assoc($mData)) 
            {
                $mDocumentHdrId = ($mRow['id']);
                $mDocumentReference = ($mRow['document_reference']);
                $mDeliveryNote = ($mRow['delivery_note']);
                $mRcvQaQty = ($mRow['quarantined_qty']);
            }

            if ($mDocumentHdrId == "")
            {
                $mReturn = "FAIL-Invalid customer Reference for Update.";
                return $mReturn;
            }
        }

        if ($mReturn == "")
        {
            $mBdyPartId=0;
            if ($mParms['transactiontypeid'] == "RBTC")
            {
                $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
                    exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());

                $mDataQuery = "SELECT
                        receipt_body.id AS id,
                        receipt_body.document_status_id AS document_status_id,
                        receipt_body.part_id AS part_id,
                        document_status.document_status_code AS document_status_code
                    FROM receipt_header
                        JOIN receipt_body ON receipt_header.id = receipt_body.receipt_header_id
                        LEFT JOIN document_status ON receipt_body.document_status_id = document_status.id
                    WHERE 
                        receipt_header.customer_reference = '{$mParms['customerreference']}'
                        AND receipt_body.case_reference='{$mParms['tocase']}'";

                if (!$mData = mysqli_query($mConnection,$mDataQuery))
                {
                    exit ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
                }
//              exit ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".mysqli_num_rows($mData));

                while($mRow = mysqli_fetch_assoc($mData)) 
                {
                    $mBdyId = $mRow['id'];
                    $mBdyPartId = $mRow['part_id'];
                    $mDocSts = strtolower($mRow['document_status_code']);

                    if ($mDocSts == "closed")
                    {
                        $mReturn = "FAIL-This Case is Closed";
                        return $mReturn;
                    }
                }
            }
        }
        if ($mReturn == "")
        {
// Validate Part Number
            $mPartId = 0;
            $mPartConvFactor = 1;
            $mInspection = 0;
            if ($mPartNumber != "")
            {
                $mDataQuery = "SELECT
                        part.id AS id,
                        part.conversion_factor AS conversion_factor,
                        part.requires_inspection AS requires_inspection,
                        part.requires_decant AS requires_decant,
                        part.part_number AS part_number,
                        vendor.vendor_reference_code AS vendor_code 
                    FROM part
                        LEFT JOIN vendor ON part.vendor_id = vendor.id
                    WHERE (part_number='{$mPartNumber}')
                    LIMIT 1";

                if (!mysqli_query($mConnection,$mDataQuery))
                {
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                }
            
                $mData = mysqli_query($mConnection,$mDataQuery);
                while($mRow = mysqli_fetch_assoc($mData)) 
                {
                    $mPartId = ($mRow['id']);
                    $mPartNumber = ($mRow['part_number']);
                    $mPartConvFactor = ($mRow['conversion_factor']);
                    $mInspection = ($mRow['requires_inspection']);
                    $mDecant = ($mRow['requires_decant']);
                    $mVendorId = ($mRow['vendor_id']);
                    $mVendorCode= ($mRow['vendor_code']);
                }

                if ($mPartId == "")
                {
                    $mReturn = "FAIL-Wrong Part Number";
                    return $mReturn;
                }
            }
        }
        
        if ($mReturn == "")
        {
            $mInvId = "";
            $mInvQty = 0;
            if (($mParms['serialreference']) && ($mPartId != ""))
            {
// Check if Part / Serial already exists
                $mDataQuery = "SELECT
                        id AS id,
                        inventory_qty AS inventory_qty
                    FROM inventory_master
                    WHERE 
                        part_id = {$mPartId}
                        AND serial_reference='{$mParms['serialreference']}'
                    LIMIT 1";

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

                if (mysqli_num_rows($mData) == 0)
                {
                    if ($mParms['transactiontypeid'] == "RTND")
                    {
                        $mReturn = "FAIL-Serial Does not Exist";
                    }
                }
                else
                {
                    while($mRow = mysqli_fetch_assoc($mData)) 
                    {
                        $mInvId = ($mRow['id']);
                        $mInvQty = ($mRow['inventory_qty']);
                    }
                    if (($mInvId != "") && (intval($mInvQty) != 0))
                    {
                        if (substr($mParms['transactiontypeid'],0,2) == "RB")
                        {
                            $mReturn = "FAIL-Duplicate Serial.";
                        }
                    }
                    if (($mInvId != "") && (intval($mInvQty) == 0))
                    {
                        if ($mParms['transactiontypeid'] == "RTND")
                        {
                            $mReturn = "FAIL-Serial Does not Exist";
                        }
                    }
                }
            }
        }
        if ($mReturn == "")
        {
// Validate Location Code
            $mToLocId = "";
            $mLocId = "";
            $mLocArea = "";
//            return "FAIL-TEST1-".$mParms['tolocationcode']." ".$mParms['tolocationhashcode'];
            if (($mParms['tolocationcode']) || ($mParms['tolocationhashcode']))
            {
                if ($mParms['tolocationcode'])
                {
                    $mDataQuery = "SELECT location.id AS id, location.location_code AS location_code,location.inventory_status_id AS inventory_status_id";
                    $mDataQuery .=",location_type.location_area AS location_area, location_type.location_type_code AS location_type_code";
                    $mDataQuery .=" FROM location";
                    $mDataQuery .=" LEFT JOIN location_type ON location.location_type_id=location_type.id";
                    $mDataQuery .=" WHERE location.location_code = '".$mParms['tolocationcode']."' OR location.location_hash = '".$mParms['tolocationcode']."' LIMIT 1";
                }
                if ($mParms['tolocationhashcode'])
                {
//                  $mDataQuery = "SELECT id AS id, location_code AS location_code,inventory_status_id AS inventory_status_id FROM location WHERE location_code = '".$mParms['tolocationhashcode']."' OR location_hash = '".$mParms['tolocationhashcode']."' LIMIT 1";
                    $mDataQuery = "SELECT location.id AS id, location.location_code AS location_code,location.inventory_status_id AS inventory_status_id";
                    $mDataQuery .=",location_type.location_area AS location_area, location_type.location_type_code AS location_type_code";
                    $mDataQuery .=" FROM location";
                    $mDataQuery .=" LEFT JOIN location_type ON location.location_type_id=location_type.id";
                    $mDataQuery .=" WHERE location.location_code = '".$mParms['tolocationhashcode']."' OR location.location_hash = '".$mParms['tolocationhashcode']."' LIMIT 1";
                }
                if (!mysqli_query($mConnection,$mDataQuery))
                {
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                }
                
                $mData = mysqli_query($mConnection,$mDataQuery);
                while($mRow = mysqli_fetch_assoc($mData)) 
                {
                    $mLocId = ($mRow['id']);
                    $mToLocId = ($mRow['id']);
                    $mToLocationCode = ($mRow['location_code']);
                    $mToLocInvStsId = ($mRow['inventory_status_id']);
                    $mLocArea = ($mRow['location_area']);
                    $mLocTypeCode = ($mRow['location_type_code']);
                }
                if ($mToLocId == "")
                {
                    $mReturn = "FAIL-Wrong Location ".$mDataQuery;
                    return($mReturn);
                }
            }
        }
        
        if ($mReturn == "")
        {
            $mToTagId = "";

// Create / Retrieve Pallet Tag
            if ($mParms['toplt'])
            {
                $mDataQuery = "SELECT id AS id FROM tag WHERE tag_reference='{$mParms['toplt']}' AND (active IS null OR active = '1') LIMIT 1";
                if (!mysqli_query($mConnection,$mDataQuery))
                {
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                }
                
                $mData = mysqli_query($mConnection,$mDataQuery);
                while($mRow = mysqli_fetch_assoc($mData)) 
                {
                    $mToTagId = ($mRow['id']);
                }
            }

// Create Inventory
//          if (($mRanOrOrder == "") || ($mRanOrOrder == null))
//              $mRanOrOrder = $mDocumentReferenceCode;
            if (($mInvId != "") && (intval($mInvQty)==0))
            {
                $mDataQuery = "DELETE FROM inventory_master WHERE id={$mInvId}";

                if (!mysqli_query($mConnection,$mDataQuery))
                {
                    return "FAIL-" . mysqli_error($mConnection) . $mDataQuery;
                }
                $mInvId = "";
            }
            $mInvUpdate = "";
            $mCountQty = null;
// Apply Conversion Factor
            if (($mParms['quantity']) && ($mParms['quantity'] != ""))
            {
                $mQty = 0;
                $mTxnQty = 0;
                $mQty = $mParms['quantity']*$mPartConvFactor;
                $mTxnQty = $mParms['quantity']*$mPartConvFactor;
                if (($mParms['countqty']) && ($mParms['countqty'] != null))
                {
                    $mQty=$mParms['countqty']*$mPartConvFactor;
                    $mCountQty=$mParms['countqty']*$mPartConvFactor;
                }
            }
// Receiving
            if ((strtolower($mParms['transactiontypeid'])=="rbtd") || (strtolower($mParms['transactiontypeid'])=="rbtp") || (strtolower($mParms['transactiontypeid'])=="testtran") || (strtolower($mParms['transactiontypeid'])=="jrbtp") || ($mParms['transactiontypeid']=="RBTP") || (strtolower($mParms['transactiontypeid'])=="rbtc")) 
            {
                if ($mDocumentHdrId == "")
                {
                    $mReturn = "FAIL-Invalid Receipt Reference for Update.";
                    return($mReturn);
                }
                if ((!$mParms['quantity']) || ($mParms['quantity'] <= 0))
                {
                    $mReturn = "FAIL-Wrong quantity";
                    return($mReturn);
                }

                $mQty = 0;
                $mTxnQty = 0;
                if ($mParms['quantity'])
                {
                    $mQty = $mParms['quantity']*$mPartConvFactor;
                    $mTxnQty = $mParms['quantity']*$mPartConvFactor;
                }
                if (($mParms['countqty']) && ($mParms['countqty'] != null))
                    $mQty=$mParms['countqty']*$mPartConvFactor;

// Receipt Body
                if ((!$mParms['tocase']) || ($mParms['tocase'] == null))
                {
                    $mDataQuery = "SELECT receipt_body.id AS id,receipt_body.received_qty AS received_qty,receipt_body.quarantined_qty AS quarantined_qty, receipt_body.product_type_id AS product_type_id";
                    $mDataQuery .=" FROM receipt_body";
                    $mDataQuery .=" JOIN receipt_detail ON receipt_body.id=receipt_detail.receipt_body_id";
                    $mDataQuery .=" WHERE receipt_body.receipt_header_id=".$mDocumentHdrId." AND (receipt_body.part_id=".$mPartId." OR receipt_body.part_number='".$mPartNumber."')  AND receipt_detail.serial_reference='".$mParms['serialreference']."' AND receipt_body.advised_qty>receipt_body.received_qty LIMIT 1";
                    if (!$mData = mysqli_query($mConnection,$mDataQuery))
                        return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
                    if (mysqli_num_rows($mData) == 0)
                    {
                        $mDataQuery = "SELECT
                                id AS id,received_qty AS received_qty,
                                receipt_body.quarantined_qty AS quarantined_qty,
                                product_type_id AS product_type_id
                            FROM receipt_body
                            WHERE 
                                receipt_header_id = {$mDocumentHdrId}
                                AND (part_id={$mPartId} OR part_number='{$mPartNumber}')
                                AND receipt_body.advised_qty > receipt_body.received_qty
                            LIMIT 1";

                        if (!$mData = mysqli_query($mConnection,$mDataQuery))
                        {
                            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                        }
                    }
                    if (mysqli_num_rows($mData) == 0)
                    {
                        $mDataQuery = "SELECT
                                id AS id,
                                received_qty AS received_qty,
                                receipt_body.quarantined_qty AS quarantined_qty,
                                product_type_id AS product_type_id
                            FROM receipt_body
                            WHERE
                                receipt_header_id = {$mDocumentHdrId}
                                AND (part_id={$mPartId} OR part_number='{$mPartNumber}')
                            LIMIT 1";

                        if (!$mData = mysqli_query($mConnection,$mDataQuery))
                        {
                            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                        }
                    }
                }
                else
                {
                    $mDataQuery = "SELECT receipt_body.id AS id,receipt_body.received_qty AS received_qty,receipt_body.quarantined_qty AS quarantined_qty, receipt_body.product_type_id AS product_type_id";
                    $mDataQuery .=" FROM receipt_body";
                    $mDataQuery .=" JOIN receipt_detail ON receipt_body.id=receipt_detail.receipt_body_id";
                    $mDataQuery .=" WHERE receipt_body.receipt_header_id=".$mDocumentHdrId." AND (receipt_body.part_id=".$mPartId." OR receipt_body.part_number='".$mPartNumber."') AND receipt_detail.serial_reference='".$mParms['serialreference']."' AND case_reference='".$mParms['tocase']."' AND receipt_body.advised_qty>receipt_body.received_qty LIMIT 1";

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

                    if (mysqli_num_rows($mData) == 0)
                    {
                        $mDataQuery = "SELECT
                                id AS id,
                                received_qty AS received_qty,
                                receipt_body.quarantined_qty AS quarantined_qty,
                                product_type_id AS product_type_id
                            FROM receipt_body
                            WHERE 
                                receipt_header_id = {$mDocumentHdrId}
                                AND (part_id={$mPartId} OR part_number='{$mPartNumber}')
                                AND case_reference='{$mParms['tocase']}'
                            LIMIT 1";

                        if (!$mData = mysqli_query($mConnection,$mDataQuery))
                        {
                            return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                        }
                    }
                }
                
                $mRctBdyId = "";
                $mRctBdyQty = 0;
                $mRctQaQty = 0;
                $mRctBdyProdId = null;
                while($mRow = mysqli_fetch_assoc($mData)) 
                {
                    $mRctBdyId = ($mRow['id']);
                    $mRctBdyQty = ($mRow['received_qty']);
                    $mRctQaQty = ($mRow['quarantined_qty']);
                    $mRctBdyProdId = ($mRow['product_type_id']);
                }
//              return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".$mParms['tocase']."-".$mRctBdyId."-".mysqli_num_rows($mData);
// No Receipt Body - Add One
                if ($mRctBdyId == "")
                {
// Get last Idoc Line No.
                    $mLineNo = 0;
                    $mDataQuery = "SELECT
                            line_no AS line_no
                        FROM receipt_body
                        WHERE receipt_header_id = {$mDocumentHdrId}
                        ORDER BY receipt_header_id, line_no DESC
                        LIMIT 1";

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

                    while($mRow = mysqli_fetch_assoc($mData)) 
                    {
                        $mLineNo = $mRow['line_no'];
                    }

                    if (($mLineNo == null) || ($mLineNo < 0))
                    {
                        $mLineNo = 0;
                    }
                    $mLineNo = $mLineNo + 1;

                    if (($mRecProdType != "") && ($mRecProdType != null))
                    {
                        $mDataQuery = "INSERT INTO receipt_body (id,document_reference,part_number,advised_qty,received_qty,difference,part_id,receipt_header_id,document_status_id,date_created,created_by,last_updated_date,last_updated_by,line_no,product_type_id,case_reference)";
                        $mDataQuery .= " VALUES(DEFAULT,'".$mDocumentReference."','".$mPartNumber."',0,".$mQty.",".$mQty.",".$mPartId.",".$mDocumentHdrId.",".$mDocStsId.",now(),'".$mParms['currentuser']."',now(),'".$mParms['currentuser']."',".$mLineNo;
                        if ($mRecProdType != null)
                            $mDataQuery .= ",".$mRecProdType;
                        else
                            $mDataQuery .= ",null";
                        if (($mParms['tocase']) && ($mParms['tocase'] != ""))
                            $mDataQuery .= ",'".$mParms['tocase']."'";
                        else
                            $mDataQuery .= ",null";
                        $mDataQuery .= ")";
                    }
                    else
                    {
                        $mDataQuery = "INSERT INTO receipt_body (id,document_reference,part_number,advised_qty,received_qty,difference,part_id,receipt_header_id,document_status_id,date_created,created_by,last_updated_date,last_updated_by,line_no,product_type_id,case_reference)";
                        $mDataQuery .=" VALUES(DEFAULT,'".$mDocumentReference."','".$mPartNumber."',0,".$mQty.",".$mQty.",".$mPartId.",".$mDocumentHdrId.",".$mDocStsId.",now(),'".$mParms['currentuser']."',now(),'".$mParms['currentuser']."',".$mLineNo;
                        if ($mRecProdType != null)
                            $mDataQuery .= ",".$mRecProdType;
                        else
                            $mDataQuery .= ",null";
                        if (($mParms['tocase']) && ($mParms['tocase'] != ""))
                            $mDataQuery .= ",'".$mParms['tocase']."'";
                        else
                            $mDataQuery .= ",null";
                        $mDataQuery .= ")";
                    }
                    if (!mysqli_query($mConnection,$mDataQuery))
                    {
                        return "FAIL-" . mysqli_error($mConnection) . $mDataQuery . "-" . $mJsonObject;
                    }
                    $mRctBdyId = mysqli_insert_id($mConnection);
                }
                else
                {
                    $mRctBdyQty=$mRctBdyQty+$mQty;
                    $mDataQuery = "UPDATE receipt_body
                        SET
                            part_id = {$mPartId},
                            received_qty = {$mRctBdyQty},
                            difference = received_qty - advised_qty,
                            last_updated_date = now(),
                            last_updated_by = '{$mParms['currentuser']}'
                        WHERE id = {$mRctBdyId}
                        LIMIT 1";

                    if (!mysqli_query($mConnection,$mDataQuery))
                    {
                        return "FAIL-" . mysqli_error($mConnection) . $mDataQuery;
                    }
                }
// Receipt Detail
//              $mDataQuery = "SELECT id AS id,received_qty AS received_qty FROM receipt_detail WHERE receipt_body_id=".$mRctBdyId." AND part_id=".$mPartId." AND serial_reference='".$mParms['serialreference']."' LIMIT 1";
                $mDataQuery = "SELECT
                        receipt_detail.id AS id,
                        receipt_detail.received_qty AS received_qty
                    FROM receipt_detail 
                        JOIN receipt_body ON receipt_detail.receipt_body_id = receipt_body.id
                    WHERE
                        receipt_body.receipt_header_id = {$mDocumentHdrId}
                        AND receipt_body.part_id = {$mPartId}
                        AND receipt_detail.serial_reference = '{$mParms['serialreference']}'
                    LIMIT 1";

                if (!mysqli_query($mConnection,$mDataQuery))
                {
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                }
                
                $mData = mysqli_query($mConnection,$mDataQuery);
                $mRctDtlId = "";
                $mRctDtlQty = 0;
                while($mRow = mysqli_fetch_assoc($mData)) 
                {
                    $mRctDtlId = ($mRow['id']);
                    $mRctDtlQty = ($mRow['received_qty']);
                }
                if ($mRctDtlId == "")
                {
                    if (strtolower($mParms['transactiontypeid'])== "jrbtp")
                    {
                        $mDataQuery = "INSERT INTO receipt_detail (id,document_reference,part_number,part_id,serial_reference,ran_order,advised_qty,received_qty,cts_qty,difference,receipt_body_id,document_status_id,date_created,created_by,last_updated_date,last_updated_by,product_type_id,product_type_id)";
                        $mDataQuery.=" VALUES(DEFAULT,'".$mDocumentReference."','".$mPartNumber."',".$mPartId.",'".$mParms['serialreference']."','".$mRanOrder."',0,".$mQty.",".$mQty.",".$mQty.",".$mRctBdyId.",".$mDocStsId.",now(),'".$mParms['currentuser']."',now(),'".$mParms['currentuser']."'";
                        if ($mRecProdType != null)
                            $mDataQuery .= ",".$mRecProdType;
                        else
                            $mDataQuery .= ",null";
                        $mDataQuery .= ")";
                    }
                    else
                    {
                        $mDataQuery = "INSERT INTO receipt_detail (id,document_reference,part_number,part_id,serial_reference,ran_order,advised_qty,received_qty,cts_qty,difference,receipt_body_id,document_status_id,date_created,created_by,last_updated_date,last_updated_by,product_type_id)";
                        $mDataQuery.= " VALUES(DEFAULT,'".$mDocumentReference."','".$mPartNumber."',".$mPartId.",'".$mParms['serialreference']."','".$mRanOrder."',0,".$mQty.",0,".$mQty.",".$mRctBdyId.",".$mDocStsId.",now(),'".$mParms['currentuser']."',now(),'".$mParms['currentuser']."'";
                        if ($mRecProdType != null)
                            $mDataQuery .= ",".$mRecProdType;
                        else
                            $mDataQuery .= ",null";
                        $mDataQuery .= ")";
                    }
                    if (!mysqli_query($mConnection,$mDataQuery))
                        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
                    $mRctDtlId=mysqli_insert_id($mConnection);
                }
                else
                {
                    $mRctDtlQty=$mRctDtlQty+$mQty;
                    if (strtolower($mParms['transactiontypeid'])== "jrbtp")
                        $mDataQuery = "UPDATE receipt_detail SET received_qty=".$mQty.",cts_qty=".$mQty.",difference=received_qty-advised_qty,last_updated_date=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mRctDtlId." LIMIT 1";
                    else
                        $mDataQuery = "UPDATE receipt_detail SET received_qty=".$mRctDtlQty.",difference=received_qty-advised_qty,last_updated_date=now(),last_updated_by='".$mParms['currentuser']."' WHERE id=".$mRctDtlId." LIMIT 1";
                    if (!mysqli_query($mConnection,$mDataQuery))
                        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
                }
            }
            
// Inventory Master
            if ($mInvId == "")
            {
                $mInvHldQty = 0;
                $mBdyQaQty = 0;
                $mBdyOvrQty = 0;
//              return "FAIL-TEST2".$mRctBdy;
// Test for receipt overbooking
                If ($mRctBdyId != "")
                {

                    $mBdyPartId=0;
                    $mDataQuery = "SELECT receipt_body.id AS id,CASE WHEN receipt_body.part_id IS null THEN 0 ELSE receipt_body.part_id END AS part_id,receipt_body.received_qty AS received_qty,receipt_body.advised_qty AS advised_qty,receipt_body.quarantined_qty AS quarantined_qty,receipt_body.inspect AS inspect";
                    $mDataQuery .=",part.part_number AS part_number,part.imaginary_part AS imaginary_part";
                    $mDataQuery .=" FROM receipt_body";
                    $mDataQuery .=" LEFT JOIN part ON receipt_body.part_id=part.id";
                    $mDataQuery .=" where receipt_body.id=".$mRctBdyId." LIMIT 1";
                    if (!$mData = mysqli_query($mConnection,$mDataQuery))
                        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
                    while($mRow = mysqli_fetch_assoc($mData)) 
                    {
                        if ($mRow['quarantined_qty']>0)
                        {
                            $mInvRcvId = $mInvQaId;
                            $mBdyQaQty = $mRow['quarantined_qty']-($mRow['received_qty']-$mQty);
                        }
                        if ($mRow['received_qty']>$mRow['advised_qty'])
                        {
                            $mInvRcvId = $mInvOvrId;
                            $mBdyOvrQty = $mRow['received_qty']-$mRow['advised_qty'];
                        }
                        if (strtolower($mRow['part_number'])=="notexist")
                        {
                            $mInvRcvId = $mInvQaId;
                        }
                        elseif ($mRow['part_id']==0)
                        {
                            $mInvRcvId = $mInvQaId;
                        }
                        elseif ($mRow['imaginary_part']==1)
                        {
                            $mInvRcvId = $mInvQaId;
                        }
                        if ($mRow['inspect'] == 1)
                        {
                            $mInspection = 1;
                        }
                    }
                }
// test for over-receipt
                if ($mBdyOvrQty > 0)
                {
                    if ($mBdyOvrQty < $mQty)
                        $mInvHldQty = $mBdyOvrQty;
                    else
                        $mInvHldQty = $mQty;
                }
// test for outstanding quarantine Qty
                if ($mBdyQaQty > 0)
                {
                    if ($mQty < $mBdyQaQty)
                        $mInvHldQty += $mQty;
                    else
                        $mInvHldQty += $mBdyQaQty;
                }
                $mInvPruf = 0;
                if (strtolower($mParms['prufconfirm'])=="y")
                    $mInvPruf = 1;
                if ($mToTagId == "")
                {
                    $mDataQuery = "INSERT INTO inventory_master (id,version,allocated_qty,current_inventory_status_id,current_location_id,location_id,location_code,date_created,created_by,expiry_date,inventory_qty,hold_qty,available_qty,last_updated,last_updated_by,parent_tag_id,part_id,part_number,ran_or_order,recorded_missing,requires_decant,requires_inspection,serial_reference,originating_detail_id,conversion_factor,stock_date,active,inventory_status_id,pruf_cube,product_type_id)"; 
                    $mDataQuery .=" VALUES(DEFAULT,0,0,null,".$mLocId.",".$mLocId.",'".$mToLocationCode."',now(),'".$mParms['currentuser']."',null,".$mQty.",".$mInvHldQty.",".($mQty-$mInvHldQty).",now(),'".$mParms['currentuser']."',null,".$mPartId.",'".$mPartNumber."','".$mRanOrder."',0,".$mDecant.",".$mInspection.",'".$mParms['serialreference']."',null,".$mPartConvFactor.",now(),1";
                    if (($mParms['transactiontypeid'] == "RBTC") || ($mParms['transactiontypeid'] == "RBTD") || ($mParms['transactiontypeid'] == "RBTP"))
                        $mDataQuery .=",".$mInvRcvId;
                    elseif($mToLocInvStsId)
                        $mDataQuery .=",".$mToLocInvStsId;
                    else
                        $mDataQuery .=",".$mInvStsId;
                    $mDataQuery .=",".$mInvPruf;
                    if ($mRecProdType != null)
                        $mDataQuery .= ",".$mRecProdType;
                    else
                        $mDataQuery .= ",null";
                    $mDataQuery .= ")";
                }
                else
                {
            if (empty($mQty))
            {
                $mQty = 0;
            }
                    $mDataQuery = "INSERT INTO inventory_master (id,version,allocated_qty,current_inventory_status_id,current_location_id,location_id,location_code,date_created,created_by,expiry_date,inventory_qty,hold_qty,available_qty,last_updated,last_updated_by,parent_tag_id,part_id,part_number,ran_or_order,recorded_missing,requires_decant,requires_inspection,serial_reference,originating_detail_id,conversion_factor,stock_date,active,tag_reference,inventory_status_id,pruf_cube,product_type_id)";
                    $mDataQuery .=" VALUES(DEFAULT,0,0,null,".$mLocId.",".$mLocId.",'".$mToLocationCode."',now(),'".$mParms['currentuser']."',null,".$mQty.",".$mInvHldQty.",".($mQty-$mInvHldQty).",now(),'".$mParms['currentuser']."',".$mToTagId.",".$mPartId.",'".$mPartNumber."','".$mRanOrder."',0,".$mDecant.",".$mInspection.",'".$mParms['serialreference']."',null,".$mPartConvFactor.",now(),1,'".$mParms['toplt']."'";
                    if (($mParms['transactiontypeid'] == "RBTC") || ($mParms['transactiontypeid'] == "RBTD") || ($mParms['transactiontypeid'] == "RBTP"))
                        $mDataQuery .=",".$mInvRcvId;
                    elseif($mToLocInvStsId)
                        $mDataQuery .=",".$mToLocInvStsId;
                    else
                        $mDataQuery .=",".$mInvStsId;
                    $mDataQuery .=",".$mInvPruf;
                    if ($mRecProdType != null)
                        $mDataQuery .= ",".$mRecProdType;
                    else
                        $mDataQuery .= ",null";
                    $mDataQuery .= ")";
                }
                if (!mysqli_query($mConnection,$mDataQuery))
                {
                    return "FAIL-1" . mysqli_error($mConnection) . $mDataQuery;
                }
                $mInvUpdate = "1";
                $mInvId=mysqli_insert_id($mConnection);
            }
            else
            {
                $mDataQuery = "SELECT inventory_master.id AS id, inventory_master.inventory_qty AS inventory_qty, tag.tag_reference AS tag_reference, location.location_code AS location_code,product_type.product_type_code AS product_type_code";
                $mDataQuery .=" FROM inventory_master";
                $mDataQuery .=" LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id";
                $mDataQuery .=" LEFT JOIN location ON inventory_master.current_location_id=location.id";
                $mDataQuery .=" LEFT JOIN product_type ON inventory_master.product_type_id=product_type.id";
                $mDataQuery .=" WHERE inventory_master.id=".$mInvId." LIMIT 1";
                if (!$mData = mysqli_query($mConnection,$mDataQuery))
                    return "FAIL-".mysqli_error($mConnection).$mDataQuery;
                while($mRow = mysqli_fetch_assoc($mData)) 
                {
                    $mQty = $mRow['inventory_qty'];
                    $mFromLocationCode = $mRow['location_code'];
//                  $mToLocationCode = $mRow['location_code'];
                    $mSourceTag = $mRow['tag_reference'];
                    $mInvProdCode = $mRow['product_type_code'];
                }
                if ($mToLocId == "")
                    $mToLocationCode = $mFromLocationCode;
// Get Fixed Location for Case To Plt
                if ($mParms['transactiontypeid'] == "CTP")
                {
                    $mToLocId = "";
                    $mDataQuery = "SELECT id AS id, location_code AS location_code FROM location WHERE location_code='PLTPLT' LIMIT 1";
                    if (!mysqli_query($mConnection,$mDataQuery))
                    {
                        return "FAIL-" . mysqli_error($mConnection) . $mDataQuery;
                    }

                    $mData = (mysqli_query($mConnection,$mDataQuery));
                    while($mRow = mysqli_fetch_assoc($mData)) 
                    {
                        $mToLocId = $mRow['id'];
                        $mToLocationCode = $mRow['location_code'];
                    }
                }

                $mDataQuery = "UPDATE inventory_master SET last_updated=now(), last_updated_by = '{$mParms['currentuser']}'";
                if ($mToTagId != "")
                {
                    $mDataQuery .= ", parent_tag_id = {$mToTagId}, tag_reference = '{$mParms['toplt']}'";
                }
                if ($mToLocId != "")
                {
                    $mDataQuery .= ", current_location_id = {$mToLocId}, location_id = {$mToLocId}, location_code = '{$mToLocationCode}'";
                }
//                return ("FAIL-TEST1-".$mParms['transactiontypeid']);

                if (($mParms['transactiontypeid'] === 'RELOC') || ($mParms['transactiontypeid'] == "FQA"))
                {
                    $allowedLocationAreas = ['storage', 'work'];
                    if (in_array(strtolower($mLocArea), $allowedLocationAreas))
                    {
                        $mDataQuery .= ", hold_qty = 0, available_qty = inventory_qty - allocated_qty, inventory_status_id = {$mInvStsId}";
                        // $mDataQuery .= ", hold_qty = 0, available_qty = inventory_qty - allocated_qty, requires_inspection = 0, inventory_status_id=".$mInvStsId;
                    }
                }
                $mDataQuery .= " WHERE id = {$mInvId} LIMIT 1";

                if (!mysqli_query($mConnection,$mDataQuery))
                {
                    return "FAIL-" . mysqli_error($mConnection) . $mDataQuery;
                }
                $mInvUpdate = "1";

// SCRAP location_type
                if (strtolower($mLocTypeCode) == 'maxdpt')
                {
                    try
                    {
                        $mDataQuery = "DELETE FROM inventory_master WHERE id = {$mInvId} LIMIT 1";
                        mysqli_query($mConnection,$mDataQuery);
                    }
                    catch(Exception $e)
                    {
                        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
                    }
                }
            }      
// Transaction History
            if (($mTxnQty == null) || ($mTxnQty == ""))
            {
                if ($mQty > 0)
                    $mTxnQty = $mQty;
                else
                    $mTxnQty = 0;
            }
            if (($mTxnQty == "") || ($mTxnQty == null) || (!$mTxnQty))
            {
                $mTxnQty = 0;
            }
            if ((!$mDocumentReference) || ($mDocumentReference == null) || ($mDocumentReference == ""))
            {
                if ($mParms['rtsreference'])
                {
                    $mDocumentReference = $mParms['rtsreference'];
                }
            }
            if ((!$mFromLocationCode) || ($mFromLocationCode == null) || ($mFromLocationCode == ""))
            {
                if ($mParms['fromlocationcode'])
                {
                    $mFromLocationCode = $mParms['fromlocationcode'];
                }
            }
            if ($mInvUpdate != "")
            {
                $mTranRefCode = $mParms['transactiontypeid'];
                if (($mParms['tocase']) && ($mParms['tocase'] != ""))
                {
                    $mDataQuery = "INSERT INTO transaction_history
                            (id,associated_document_reference,date_created,created_by,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,source_tag,to_location_code,transaction_reference_code,txn_qty,count_qty,comment,active,conversion_factor,customer_reference,requires_inspection,to_plt)
                        VALUES
                            (DEFAULT,'{$mDocumentReference}',now(),'{$mParms['currentuser']}','{$mFromLocationCode}',now(),'{$mParms['currentuser']}','{$mParms['tocase']}','{$mPartNumber}','{$mRanOrder}','{$mParms['serialreference']}','{$mParms['transactiontypeid']}','{$mSourceTag}','{$mToLocationCode}','{$mTranRefCode}',{$mTxnQty}";

                    if ($mCountQty != null)
                    {
                        $mDataQuery .= "," . $mCountQty;
                    }
                    else
                    {
                        $mDataQuery .= ",null";
                    }
                    $mDataQuery .= ",'',1,{$mPartConvFactor},'{$mDeliveryNote}',{$mInspection},'{$mParms['tocase']}')";
                }
                elseif (($mParms['toplt']) && ($mParms['toplt'] != ""))
                {
                    $mDataQuery = "INSERT INTO transaction_history
                            (id,associated_document_reference,date_created,created_by,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,source_tag,to_location_code,transaction_reference_code,txn_qty,count_qty,comment,active,conversion_factor,customer_reference,requires_inspection,to_plt)
                        VALUES
                            (DEFAULT,'{$mDocumentReference}',now(),'{$mParms['currentuser']}','{$mFromLocationCode}',now(),'{$mParms['currentuser']}','{$mParms['toplt']}','{$mPartNumber}','{$mRanOrder}','{$mParms['serialreference']}','{$mParms['transactiontypeid']}','{$mSourceTag}','{$mToLocationCode}','{$mTranRefCode}',{$mTxnQty}";
                    if ($mCountQty != null)
                    {
                        $mDataQuery .= "," . $mCountQty;
                    }
                    else
                    {
                        $mDataQuery .= ",null";
                    }
                    $mDataQuery .= ",'',1,{$mPartConvFactor},'{$mDeliveryNote}',{$mInspection},'{$mParms['toplt']}')";
                }
                else
                {
                    $mDataQuery = "INSERT INTO transaction_history
                            (id,associated_document_reference,date_created,created_by,from_location_code,last_updated,last_updated_by,parent_tag,part_number,ran_or_order,serial_reference,short_code,source_tag,to_location_code,transaction_reference_code,txn_qty,count_qty,comment,active,conversion_factor,customer_reference,requires_inspection)
                        VALUES
                            (DEFAULT,'{$mDocumentReference}',now(),'{$mParms['currentuser']}','{$mFromLocationCode}',now(),'{$mParms['currentuser']}',null,'{$mPartNumber}','{$mRanOrder}','{$mParms['serialreference']}','{$mParms['transactiontypeid']}','{$mSourceTag}','{$mToLocationCode}','{$mTranRefCode}',{$mTxnQty}";
                    if ($mCountQty != null)
                    {
                        $mDataQuery .= "," . $mCountQty;
                    }
                    else
                    {
                        $mDataQuery .= ",null";
                    }
                    $mDataQuery .= ",'',1,{$mPartConvFactor},'{$mDeliveryNote}',{$mInspection})";
                }
                try 
                {
                    if (!mysqli_query($mConnection,$mDataQuery))
                    {
                        return "FAIL-" . mysqli_error($mConnection) . $mDataQuery;
                    }
                }
                catch (Exception $e) 
                {
                }
            }
        }
// Post Documents - TEMPORARY
/*
        try
        {
            if (($mParms['customerreference']) && ($mParms['customerreference'] != ""))
            {
                include_once ('postdocument.php');
                $mReturn = postdocument($mJsonObject,'Customer Receipt','RCP',$mHost,$mUser,$mPwd,$mDbName);
            }
        }
        catch (Exception $e)
        {
        }
*/ 

// Post Service Message
        if ($mReturn == "")
        {
            if ($mParms['transactiontypeid'] == "RTND")
            {
//                http://van-kuk-test:8093/vantec/writeSTKMVOffsiteReturnMessage
//                {
//                    "partNumber": "DUMMY",
//                    "fromLocation": "POT",
//                    "quantity": 1,
//                    "user": "admin"
//              }
// Get Server Data from URL
                $mJsonArray = array();
                $mJsonArray['partNumber'] = $mPartNumber;
                $mJsonArray['fromLocation'] = $mFromLocationCode;
                $mJsonArray['quantity'] = $mQty;
                $mJsonArray['user'] = $mParms['currentuser'];
// 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",":8093/vantec",$mConnect);
                if ($mConnectp == $mConnect)
                {
                    $e = strpos($mConnect,":8081");
                    $mConnectp = substr($mConnect,0,$e).":8093/vantec/";
                }
                $ch = curl_init();
                try
                {
                    $mUrl = "writeSTKMVOffsiteReturnMessage";
                    curl_setopt($ch, CURLOPT_URL, $mConnectp.$mUrl);
                    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json')); 
                    curl_setopt($ch, CURLOPT_POST, 1);
                    curl_setopt($ch, CURLOPT_POSTFIELDS, $mJsonObject);
                    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                    $mResult = curl_exec($ch);
                    $mErrors = curl_error($ch);
                    $mResponse = curl_getinfo($ch, CURLINFO_HTTP_CODE);
                    curl_close($ch);
                    if ($mResponse != "200")
                    {
                        $mUrl = str_replace("&","|AND|",$mUrl);
//                        print_r("FAIL-Unexpected System Error -".$mResult.":".$mResponse.":".$mErrors.":".$mConnectp.$mUrl.":".$mJsonObject);
                    }
                    else
                    {
                        print_r("FAIL-OK -".$mResult.":".$mResponse.":".$mErrors.":".$mJsonObject.":".$mConnectp);
                        $mJsonArray = json_decode($mResult, true);
                        if ($mJsonArray['message'])
                        {
                            if (substr($mJsonArray['message'],0,2) == "OK")
                            {
                                $mReturn = "OK  -".substr($mJsonArray['message'],2);
                            }
                            else
                            {
                                $mReturn = "FAIL-".$mJsonArray['message'];                
                            }
                        }
                        else
                        {
                            $mReturn = "OK  -sendstkmvmessage";
                        }
                    }
                }
                catch (Exception $e)
                {
                    exit("FAIL-Connection Error at - ".$mConnect.$mFilter.":".$e);
                }
            }
        }

// Return Status
        if ($mReturn == "")
        {
            $mReturn = "OK  -postTransaction";
            $mTimeEnd = date("H-i-s");
//            if ($mTime != $mTimeEnd)
//                $mReturn = "DATA:rdtMessage:Posted ".$mTime."-".$mTimeEnd;
            return $mReturn;
        }
    }
    if ($mReturn == "")
        $mReturn = "OKMS-".strtolower($mParms['transactiontypeid']."-".$mInvUpdate);
    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";
}
// Process Audit Message
function postAudit($mTable,$mData,$mHost,$mUser,$mPwd,$mDbName,$mTransactionTypeId="AUDIT")
{
// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
    
// Update Tables
        $mTranQuery = "INSERT INTO audit_log (id,date_time,type,table_name,after_data) VALUES(DEFAULT,now(),'".$mTransactionTypeId."','".$mTable."','".$mData."')"; 
        if (!mysqli_query($mConnection,$mTranQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;
            
    return("OK  -postAudit");
}
// User Device
function userDevice($mFilter,$mHost,$mUser,$mPwd,$mDbName)
{
// 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;
    
//  exit("FAIL-TEST ".$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 (strtolower($k2) == "currentuser")
            $mCurrentUser = $v2;
        elseif (strtolower($k2) == "device")
            $mDevice = strtoupper($v2);
        elseif ($k2 == "transactionTypeId")
            $mTransactionTypeId = strtoupper($v2);
    }    
// mysql connection - Use Passed values...
    $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
    
// Update Tables
        $mTranQuery = "INSERT INTO user_device (id,user_id,device,transaction_type,date_created,last_updated) VALUES(DEFAULT,'".$mCurrentUser."','".$mDevice."','".$mTransactionTypeId."',now(),now())";    
        if (!mysqli_query($mConnection,$mTranQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;          
    return("OK  -userDevice");
}

// Set inventory_master requires_inspection flag
function setInspect($mFilter,$mHost,$mUser,$mPwd,$mDbName)
{
// 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;
    
//  exit("FAIL-TEST ".$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 (strtolower($k2) == "serialreference")
            $mSerialReference = strtoupper($v2);
        elseif (strtolower($k2) == "partnumber")
            $mPartNumber = strtoupper($v2);
        elseif (strtolower($k2) == "tolocationcode")
            $mToLoc = strtoupper($v2);
        elseif ($k2 == "transactionTypeId")
            $mTransactionTypeId = strtoupper($v2);
    }    
// mysql connection - Use Passed values...
    try
    {
        $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
    }
    catch (Exception $e)
    {
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
    }
    
// Update Tables
    $mDataQuery = "SELECT location.id AS id,location_type.location_type_code AS location_type_code,location.inventory_status_id AS inventory_status_id";
    $mDataQuery .=" FROM location";
    $mDataQuery .=" LEFT JOIN location_type ON location.location_type_id=location_type.id";
    $mDataQuery .=" WHERE";
    $mDataQuery .=" (location_code=? OR location_hash=?)";
    $mDataQuery .=" AND";
    $mDataQuery .=" upper(location_type_code) IN('QA')";
    $mDataQuery .=" LIMIT 1";
    
    try
    {
        $mStmt = $mConnection->prepare($mDataQuery);
        $mStmt->bind_param("ss", $mToLoc, $mToLoc);
        $mStmt->execute();
        $mData = $mStmt->get_result();
    }
    catch (Exception $e)
    {
        exit("FAIL-".$e." ".mysqli_error($mConnection)." ".$mDataQuery);
    }

    $mStmt->close();
    
    if (mysqli_num_rows($mData) > 0)
    {
        $mRows = $mData->fetch_all(MYSQLI_ASSOC);
        foreach ($mRows as $mRow)
        {
            $mInvStsId = $mRow['inventory_status_id'];
            $mDataQuery = "UPDATE inventory_master";
            $mDataQuery .=" SET requires_inspection=1, last_updated=now()";
            $mDataQuery .=" WHERE serial_reference=? AND part_number=?";
            $mDataQuery .=" LIMIT 1";
            try
            {
                $mStmt = $mConnection->prepare($mDataQuery);
                $mStmt->bind_param("ss", $mSerialReference, $mPartNumber);
                $mStmt->execute();
            }
            catch (Exception $e)
            {
                exit("FAIL-".$e." ".mysqli_error($mConnection)." ".$mDataQuery);
            }
            $mStmt->close();
        }
    }
    
    $mConnection->close();
             
    return("OK  setInspect ");
}

function postLocToLoc($mFilter,$mHost,$mUser,$mPwd,$mDbName)
{
// 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;
    
//  exit("FAIL-TEST ".$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 (strtolower($k2) == "fromlocid")
            $mFromLocId = strtoupper($v2);
        elseif (strtolower($k2) == "tolocid")
            $mToLocId = strtoupper($v2);
        elseif (strtolower($k2) == "fromloccode")
            $mFromLoc = strtoupper($v2);
        elseif (strtolower($k2) == "toloccode")
            $mToLoc = strtoupper($v2);
        elseif (strtolower($k2) == "currentuser")
            $mCurrentUser = strtoupper($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper($v2);
    }
    if (empty($mToLoc))
    {
        exit("DATA:toLocation:::skiptoLocation");
    }
// mysql connection - Use Passed values...
    try
    {
        $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
    }
    catch (Exception $e)
    {
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
    }
    if (empty($mFromLocId) && !empty($mFromLoc))
    {
        $mDataQuery = "SELECT id,location_code FROM location WHERE location_code='$mFromLoc' or location_hash = '$mFromLoc' LIMIT 1";
        try
        {
            $mData = (mysqli_query($mConnection,$mDataQuery));
            
            if (mysqli_num_rows($mData) > 0)
            {
                list($mFromLocId,$mFromLoc) = mysqli_fetch_row($mData);
            }
            else
            {
                return "FAIL-No Parts to Relocate from ".$mFromLoc;
            }
        }
        catch(Exception $e)
        {
            return "FAIL-Invalid From Location ".$mFromLoc;
        }
    }
    if (empty($mToLocId) && !empty($mToLoc))
    {
        $mDataQuery = "SELECT id,location_code FROM location WHERE location_code='$mToLoc' or location_hash='$mToLoc' LIMIT 1";
        try
        {
            $mData = (mysqli_query($mConnection,$mDataQuery));
            
            if (mysqli_num_rows($mData) > 0)
            {
                list($mToLocId,$mToLoc) = mysqli_fetch_row($mData);
            }
            else
            {
                return "FAIL-Invalid To Location ".$mToLoc;
            }
        }
        catch(Exception $e)
        {
            return "FAIL-Invalid To Location ".$mFromLoc;
        }
    }
    $mDataQuery = "SELECT inventory_master.id AS id
                    , part.part_number AS part_number
                    , inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order
                    , inventory_master.inventory_qty AS inventory_qty,inventory_master.picked_qty AS picked_qty
                    , tag.tag_reference AS plt_number 
                    FROM inventory_master 
                    JOIN part ON inventory_master.part_id=part.id 
                    LEFT JOIN inventory_status ON inventory_master.current_inventory_status_id=inventory_status.id 
                    LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE location_id=".$mFromLocId." 
                    AND (inventory_master.current_inventory_status_id IS null OR lower(inventory_status.inventory_status_code) <> 'pkd')";  
    if (!$mData = mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    
    if (mysqli_num_rows($mData) == 0)
        return "FAIL-No Parts to Relocate from ".$mFromLoc;
    while($mRow = mysqli_fetch_assoc($mData)) 
    {
        $mTranQuery = "INSERT INTO transaction_history
        (id,short_code,transaction_reference_code,part_number,serial_reference,ran_or_order,txn_qty,from_plt,from_location_code,to_location_code,created_by,date_created,last_updated,last_updated_by) 
        VALUES(DEFAULT,'".$mTransactionTypeId."','".$mTransactionTypeId."','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".($mRow['ran_order'])."',".($mRow['inventory_qty']).",'".($mRow['plt_number'])."','".$mFromLoc."','".$mToLoc."','".$mCurrentUser."',now(),now(),'".$mCurrentUser."')"; 
        if (!mysqli_query($mConnection,$mTranQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;  

        $mDataQuery = "UPDATE inventory_master SET location_id=".$mToLocId.",current_location_id=".$mToLocId.",location_code='".$mToLoc."'
            ,parent_tag_id=null,last_updated=now(),last_updated_by='".$mCurrentUser."' 
            WHERE id=".$mRow['id'];
        if (!mysqli_query($mConnection,$mDataQuery))
            return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    }
    return("OKMS-Locate Complete");
}

function postPltToLoc($mFilter,$mHost,$mUser,$mPwd,$mDbName)
{
// 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;
    
//  exit("FAIL-TEST ".$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 (strtolower($k2) == "fromtag")
            $mFromTag = strtoupper($v2);
        elseif (strtolower($k2) == "toloccode")
            $mToLoc = strtoupper($v2);
        elseif (strtolower($k2) == "currentuser")
            $mCurrentUser = strtoupper($v2);
        elseif ($k2 == "transactionType")
            $mTransactionTypeId = strtoupper($v2);
    }
    if (empty($mFromTag))
    {
        exit("DATA:serialReference:::skipserialReference");
    }
    if (empty($mToLoc))
    {
        exit("DATA:toLocation:::skiptoLocation");
    }
// mysql connection - Use Passed values...
    try
    {
        $mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
    }
    catch (Exception $e)
    {
        exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
    }
    
    if (!empty($mToLoc))
    {
        $mDataQuery = "SELECT id,location_code FROM location 
                        WHERE location_code='$mToLoc' or location_hash='$mToLoc' 
                        LIMIT 1";
        try
        {
            $mData = (mysqli_query($mConnection,$mDataQuery));
            
            if (mysqli_num_rows($mData) > 0)
            {
                list($mToLocId,$mToLoc) = mysqli_fetch_row($mData);
            }
            else
            {
                return "FAIL-Invalid To Location ".$mToLoc;
            }
        }
        catch(Exception $e)
        {
            return "FAIL-Invalid To Location ".$mFromLoc;
        }
    }
    
    if (!empty($mFromTag))
    {
		$mDataQuery = "SELECT inventory_master.id AS id,inventory_master.location_code AS location_code
						, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order
						, inventory_master.inventory_qty AS inventory_qty,inventory_master.picked_qty AS picked_qty
						, part.part_number AS part_number
						, tag.tag_reference AS plt_number
						, location_type.location_type_code AS location_type
						FROM inventory_master 
						JOIN part ON inventory_master.part_id=part.id 
						JOIN location ON inventory_master.current_location_id=location.id
						LEFT JOIN inventory_status ON inventory_master.current_inventory_status_id=inventory_status.id 
						LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id
						LEFT JOIN location_type ON location_type_id=location_type.id
						WHERE (inventory_master.tag_reference='$mFromTag' OR inventory_master.serial_reference='$mFromTag')
						AND (inventory_master.current_inventory_status_id IS null OR lower(inventory_status.inventory_status_code) <> 'pkd')";  
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection).$mDataQuery;
		
		if (mysqli_num_rows($mData) == 0)
			return "FAIL-No Parts to Relocate from ".$mFromTag;
// validate the returned serials
		$mPrvLoc = null;
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			if ($mTransactionTypeId == 'DTT')
			{
				if ($mRow['location_type'] != "RCV")
				{
					return "FAIL-Inventory must be in receiving";
				}
			}
			if ($mTransactionTypeId == 'MTT')
			{
				if ($mRow['location_type'] != "MA")
				{
					return "FAIL-Inventory must be in marshalling";
				}
			}
			if ($mPrvLoc == null)
			{
				$mPrvLoc = $mRow['location_code'];
			}
			if ($mPrvLoc != $mRow['location_code'])
			{
				return "FAIL-The pallet contains mixed location serials";
			}
		}
		if (!$mData = mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection).$mDataQuery;
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mTranQuery = "INSERT INTO transaction_history
			(id,short_code,transaction_reference_code,part_number,serial_reference,ran_or_order,txn_qty,from_plt,from_location_code,to_location_code,created_by,date_created,last_updated,last_updated_by) 
			VALUES(DEFAULT,'".$mTransactionTypeId."','".$mTransactionTypeId."','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".($mRow['ran_order'])."',".($mRow['inventory_qty']).",'".($mRow['plt_number'])."','".$mRow['location_code']."','".$mToLoc."','".$mCurrentUser."',now(),now(),'".$mCurrentUser."')"; 
			if (!mysqli_query($mConnection,$mTranQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;  

			$mDataQuery = "UPDATE inventory_master SET location_id=".$mToLocId.",current_location_id=".$mToLocId.",location_code='".$mToLoc."'
				,parent_tag_id=null,last_updated=now(),last_updated_by='".$mCurrentUser."' 
				WHERE id=".$mRow['id'];
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection).$mDataQuery;
		}
	}
    return("OKMS-Locate Complete");
}

/*
function processLocToPlt($mFromLocId,$mToPlt,$mFromLocCode,$mCurrentUser,$mDbHost=null,$mDbUser=null,$mDbPwd=null,$mDbName=null)
{
// mysql connection - Use Passed values...
    if (($mDbHost != null) && ($mDbHost != ""))
        $mConnection=mysqli_connect($mDbHost,$mDbUser,$mDbPwd,$mDbName) or
            exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
// ... or get defaults from function defaultDbConnection
    else
        $mConnection =defaultDbConnection();
    
//  $mDataQuery = "SELECT part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_order AS ran_order, inventory_master.inventory_qty AS inventory_qty, inventory_master.plt_number AS plt_number FROM inventory_master JOIN part ON inventory_master.part_id=part.id WHERE location_id=".$mFromLocId;  
    $mDataQuery = "SELECT part.part_number AS part_number, inventory_master.serial_reference AS serial_reference, inventory_master.ran_or_order AS ran_order, inventory_master.inventory_qty AS inventory_qty, tag.tag_reference AS plt_number FROM inventory_master JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.current_inventory_status_id=inventory_status.id LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE location_id=".$mFromLocId." AND (inventory_master.current_inventory_status_id IS null OR lower(inventory_status.status_code) <> 'pkd')";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        return "FAIL-No Parts to Relocate for ".$mFromLocId;
    while($mRow = mysqli_fetch_assoc($mData)) 
    {
        $mTranQuery = "INSERT INTO transaction_history (id,short_code,part_number,serial_reference,ran_or_order,txn_qty,from_plt,from_location_code,to_plt,created_by,date_created,last_updated,last_updated_by) VALUES(DEFAULT,'LTL','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".($mRow['ran_order'])."',".($mRow['inventory_qty']).",'".($mRow['plt_number'])."','".$mFromLocCode."','".$mToPlt."','".$mCurrentUser."',now(),now(),'".$mCurrentUser."')";   
        if (!mysqli_query($mConnection,$mTranQuery))
            return "FAIL-".mysqli_error($mConnection)."-".$mTranQuery;  
    }
    $mTagId=null;
    $mDataQuery = "SELECT tag.id AS id FROM tag WHERE tag.tag_reference='".$mToPlt."' LIMIT 1";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;
    $mData = (mysqli_query($mConnection,$mDataQuery));
    if (mysqli_num_rows($mData) == 0)
        return "FAIL-Invalid Tag - ".$mTpPlt;
    $mRow = mysqli_fetch_assoc($mData)) 
    $mTagId = ($mRow['id']);
    $mDataQuery = "UPDATE inventory_master SET parent_tag_id=".$mTagId.",last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE location_id=".$mFromLocId." AND (inventory_master.current_inventory_status_id IS null OR EXISTS(SELECT id FROM inventory_status WHERE inventory_master.inventory_status_id=inventory_status.id AND lower(inventory_status.status_code) NOT IN('pkd','plt'))";
    if (!mysqli_query($mConnection,$mDataQuery))
        return "FAIL-".mysqli_error($mConnection).$mDataQuery;

    return("OKMS-Relocate Complete");
}
*/
