<?php
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);
date_default_timezone_set('Europe/London');
// error_reporting(E_ALL);
// ini_set('display_errors', 1);
// ----------------------------------------------------------------------------------------
// Use Statements
// ----------------------------------------------------------------------------------------
use classes\MysqliVeu;

$protocol = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? "https://" : "http://";
$ip = $_SERVER['HTTP_HOST'];
$baseUrl = $protocol.$ip;

$root = $_SERVER['DOCUMENT_ROOT'];
if (empty($root))
{
    $root = (__DIR__);
}

$root = str_replace("\\","/",$root);
$dir = (dirname(__FILE__));
$dir = str_replace("\\","/",$dir);

// get Base Directory
$baseDir = $dir;
for ($i=0;$i<10;$i++)
{
    $testDir = dirname($baseDir);
    $testDir = str_replace("\\","/",$testDir);
    
    $baseDir = str_replace("\\","/",$baseDir);
    if ($baseDir == $root)
    {
        break;
    }
    if ($testDir == $root)
    {
        break;
    }
    $baseDir = dirname($baseDir);
}
// ----------------------------------------------------------------------------
// Global Variables Statements and function loader call
// ----------------------------------------------------------------------------
$function = $_GET['function'] ?? '';
$env = $_GET['env'] ?? '';
// ----------------------------------------------------------------------------
// Environment
// ----------------------------------------------------------------------------
if (!empty($env))
{
    $environmentFile = $baseDir.'/'.$env.'/.env';
}
else
{
    $environmentFile = $baseDir.'/.env';
}
$_ENV = parse_ini_file($environmentFile, true, INI_SCANNER_TYPED);
// Includes and requires
require_once $baseDir.'/rdtvims/rdtphp/classes/MysqliVeu.php';
$db = new MysqliVeu($_ENV['database']['DB_HOST'],$_ENV['database']['DB_USERNAME'],$_ENV['database']['DB_PASSWORD'],$_ENV['database']['DB_DATABASE']);

if (!empty($_GET['filter']))
{
    $post = $_GET['filter'];
}
// Get Input Parameters from POST or GET
if (!empty($_POST))
{
    $post = "";
    foreach($_POST as $key=>$val)
    {
        if ($post != "")
            $post .= "|AND|";
        $post .= $key."=".$val;
    }
}
if (empty($post))
{
    $post = file_get_contents("php://input");
    $params = json_decode($post, true);
    $post = "";
    foreach($params as $key=>$val)
    {
        if ($post != "")
            $post .= "|AND|";
        $post .= $key."=".$val;
    }
}

$post = str_replace("&","|AMPERSAND|",$post);
$post = str_replace("|AND|","&",$post);
$post = str_replace("|COLON|",":",$post);
$post = str_replace("/dbase/",$_ENV['database']['DB_DATABASE'],$post);
// Replace Chars in Input String to emulate a JSON format instead of GET format
$post = str_replace("=",":",$post);
$post = str_replace("&",",",$post);

$mErrMsg = "OK  -";

if ($function == 'validatetraintrolley')
{
    $return = validateTrainTrolley($post,$db);
}
elseif ($function == 'gettoloc')
{
    $return = getToLoc($post,$db);
}
elseif ($function == 'getserltoloc')
{
    $return = getSerlToLoc($post,$db);
}
elseif ($function == 'validatetoloc')
{
    $return = validateToLoc($post,$db);
}
elseif ($function == 'posttoline')
{
    $return = postToLine($post,$db);
}
elseif ($function == 'postpltoline')
{
    $return = postPlToLine($post,$db);
}
elseif ($function == 'deleteinv')
{
    $return = deleteInv($post,$db);
}
elseif ($function == 'setfillingstatus')
{
    $return = setFillingStatus($post,$db);
}
elseif ($function == 'getserialcount')
{
    $return = getSerialCount($post,$db);
}
else
{
    $return = "FAIL-Unknown Function";
}
echo $return;
return;

function validateTrainTrolley($post,$db)
{
    $i = 0;
    $mTrolley = "";
    $mReference = "";
    $mSite = "";
    $mLocType = "";

// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "trolley")
            $mTrolley = $val;
        elseif (strtolower($key) == "skip")
            $mSkip = $val;
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = $val;
        elseif (strtolower($key) == "device")
            $mDevice = strtoupper($val);
        elseif ($key == "transactionTypeId")
            $mTransactionTypeId = strtoupper($val);
    }

    if (($mTrolley == "") || (strlen($mTrolley) == 0))
    {
        return "FAIL-Blank Trolley is not allowed";
    }
    
    $sql = "SELECT
            train_locations.id as id, train_locations.train_location_code, train_locations.train_location_hash AS train_location_hash, train_locations.train_locations_status_id AS train_locations_status_id,
            train_locations_status.train_locations_status AS train_locations_status
            FROM train_locations
            JOIN train_locations_status ON train_locations.train_locations_status_id=train_locations_status.id
        WHERE
            train_locations.train_location_code = :ref
        OR
            train_locations.train_location_hash = :ref
            ";
    $sql.= ' LIMIT 1';
    $params = "ref:".$mTrolley;
        
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    
    foreach ($rows as $row)
    {
        if (strtoupper($row['train_locations_status']) != "CLOSED")
        {
            return "FAIL-Train Trolley is not closed.";
        }
        $sql = "SELECT inventory_master.id AS id, location_type.location_type_code AS location_type_code
                FROM inventory_master
                LEFT JOIN location ON inventory_master.current_location_id=location.id
                LEFT JOIN location_type ON location.location_type_id=location_type.id
                WHERE inventory_master.location_code=:ref
                LIMIT 1
                ";
        $params = "ref:".$rows[0]['train_location_code'];
        
        $result = $db->queryParamsJson($sql, $params);
        try
        {
            $result = json_decode($result,true);
        }
        catch (Exception $e)
        {
        }
        $message = $result['message'];
        $numRows = $result['numrows'];
        $invRows = $result['data'];
        
        If ($numRows == 0)
        {
            return "FAIL-No Serials in this Trolley";
        }
        if (strtoupper($invRows[0]['location_type_code']) == "MAXDPT")
        {
            return "FAIL-The Trolley has already been delivered";
        }
    }
    
    if ($numRows == 0)
    {
            $sql = "SELECT 
                    inventory_master.id AS id
                    FROM 
                    inventory_master
                    JOIN
                    inventory_status ON inventory_master.inventory_status_id=inventory_status.id
                    WHERE
                    inventory_master.tag_reference=:ref OR inventory_master.serial_reference=:ref
                    AND
                    inventory_status_code = 'PKD'
                    LIMIT 1";
            $params = "ref:".$mTrolley;
            $result = $db->queryParamsJson($sql, $params);
            try
            {
                $result = json_decode($result,true);
            }
            catch (Exception $e)
            {
            }
            $message = $result['message'];
            $numRows = $result['numrows'];
            $rows = $result['data'];
            if ($numRows == 0)
            {
                return "FAIL-No valid inventory for this reference ".$mTrolley;
            }
    }
    return "OK  -validateserial";
}

function getToLoc($post,$db)
{
    $i = 0;
    $mTrolley = "";
    $mSite = "";
    $mLocType = "";

// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "trolley")
            $mTrolley = $val;
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = $val;
        elseif (strtolower($key) == "device")
            $mDevice = strtoupper($val);
        elseif ($key == "transactionTypeId")
            $mTransactionTypeId = strtoupper($val);
    }

    if (empty($mTrolley))
    {
        return "DATA:trolley:::skiptrolley";
    }
    
    $sql = "SELECT
                trolley.id AS id,
                location.location_hash AS location_hash, location.location_code AS location_code,
                im.count AS count
            FROM train_locations
            JOIN trolley ON train_locations.train_location_code LIKE CONCAT('%', trolley.trolley)
            JOIN zone_destination ON zone_destination.trolley_id=trolley.id
            LEFT JOIN location ON location.id = zone_destination.location_id
            LEFT JOIN (
                SELECT count(id) AS count, current_location_id, location_code FROM inventory_master
                ) im ON train_locations.train_location_code=im.location_code
            WHERE
                train_locations.train_location_code = :ref
            OR
                train_locations.train_location_hash = :ref
            ";
    $sql.= ' LIMIT 1';
    $params = "ref:".$mTrolley;
        
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    
    if ($numRows == 0)
    {
        return "FAIL-Active Trolley not found";
    }
    
    foreach ($rows as $row)
    {
        if (empty($row['count']))
        {
//            return "FAIL-this trolley is empty";
        }
        return "DATA:toLocationHashCodecontent:".$row['location_code']."::skiptoLocationHashCode";
    }
    return "OK  -getToLoc";
}

function getSerlToLoc($post,$db)
{
    $i = 0;
    $mTrolley = "";
    $mSerial = "";
    $mPartNumber = "";
    $mSite = "";
    $mLocType = "";

// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "trolley")
            $mTrolley = $val;
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = $val;
        elseif (strtolower($key) == "device")
            $mDevice = strtoupper($val);
        elseif ($key == "transactionTypeId")
            $mTransactionTypeId = strtoupper($val);
    }

    if (empty($mTrolley))
    {
        return "DATA:trolley:::skiptrolley";
    }
    
    $sql = "SELECT
                inventory_master.id AS id,inventory_master.serial_reference,inventory_master.part_number,
                pick_detail.zone_destination AS zone_destination,
                location.location_hash AS location_hash, location.location_code AS location_code
            FROM inventory_master
            LEFT JOIN pick_detail ON inventory_master.part_number=pick_detail.part_number AND inventory_master.serial_reference=pick_detail.serial_reference_scanned
            LEFT JOIN zone_destination ON pick_detail.zone_destination=zone_destination.zone_destination
            LEFT JOIN location ON location.id = zone_destination.location_id
            WHERE
                inventory_master.serial_reference = :ref
            OR
                inventory_master.tag_reference = :ref
            ";
    $sql.= ' LIMIT 1';
    $params = "ref:".$mTrolley;
        
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    if ($numRows == 0)
    {
        return "FAIL-Active Pallet / Serial not found";
    }
    
    foreach ($rows as $row)
    {
        if (empty($row['location_code']))
        {
            return "DATA:tolocationHashCodecontent:".$row['zone_destination']."::skiptoLocationHashCode";
        }
        else
        {
            return "DATA:toLocationHashCodecontent:".$row['location_code']."::skiptoLocationHashCode";
        }
    }
    return "OK  -getSerlToLoc";
}

function validateToLoc($post,$db)
{
    $i = 0;
    $mTrolley = "";
    $mLocCode = "";
    $mSite = "";
    $mLocType = "";

// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "trolley")
            $mTrolley = $val;
        elseif (strtolower($key) == "location")
            $mLocCode = $val;
        elseif (strtolower($key) == "locationhash")
            $mLocHash = $val;
        elseif (strtolower($key) == "site")
            $mSite = $val;
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = $val;
        elseif (strtolower($key) == "device")
            $mDevice = strtoupper($val);
        elseif ($key == "transactionTypeId")
            $mTransactionTypeId = strtoupper($val);
    }

    if (empty($mLocCode))
    {
        return "DATA:toLocationHashCode:::skiptoLocationHashCode";
    }
    
    $sql = "SELECT
            location.id AS id,
            location.location_code AS location_code
        FROM location
        LEFT JOIN location_type ON location.location_type_id=location_type.id
        WHERE
            (location.location_code = :loc OR location.location_hash = :loc)
            ";
        $params ="loc:".$mLocHash;
        $sql.= ' LIMIT 1';
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    
    if ($numRows == 0)
    {
        return "FAIL-Location not found";
    }
    
    if (strtoupper($rows[0]['location_code']) != strtoupper($mLocCode))
    {
        return "FAIL-Incorrect Location";
    }
// store 'delete' flag
    return "DATA:allocationId:".$rows[0]['delete_after_pick'];
    
    return "OK  -validateLocation";
}

function postToLine($post,$db)
{
    $now = date('Y-m-d H:i:s');
    $type = "";
// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "allocationid")
            $mDeleteFlag = strtoupper($val);
        elseif (strtolower($key) == "trolley")
            $mTrolley = strtoupper($val);
        elseif (strtolower($key) == "tolocationhash")
            $mToLocHash = strtoupper($val);
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = strtoupper($val);
        elseif ($key == "transactionType")
            $mTransactionTypeId = strtoupper($val);
    }
    if (empty($mTransactionTypeId))
    {
        $mTransactionTypeId = "TRTOLN";
    }
    $sql = "SELECT
            train_locations.id as id,
            train_locations.train_location_code AS train_location_code,
            train_locations.train_location_hash AS train_location_hash, 
            train_locations.train_locations_status_id AS train_locations_status_id,
            train_locations_status.train_locations_status AS train_locations_status
            FROM train_locations
            JOIN train_locations_status ON train_locations.train_locations_status_id=train_locations_status.id
        WHERE
            train_locations.train_location_code = :ref
        OR
            train_locations.train_location_hash = :ref
            ";
    $sql.= ' LIMIT 1';
    $params = "ref:".$mTrolley;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    $loc = $rows[0]['train_location_code'];
    
    $sql = "SELECT
                location.id as id,
                location.location_code AS location_code
            FROM
                location 
            WHERE
                location.location_code = :ref
            OR
                location.location_hash = :ref
            ";
    $sql.= ' LIMIT 1';
    $params = "ref:".$mToLocHash;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $locRow = $result['data'];
    if ($numRows == 0)
    {
       $locRow[0]['location_code'] = $mToLocHash; 
    }
    
    $sql = "SELECT * FROM inventory_master WHERE
    location_code = :loc
    LIMIT 1000";
    $params = "loc:".$loc;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $invRows = $result['data'];
    $pickQty = 0;
    $pickRef = substr($loc,0,9);
    if ($numRows == 0)
    {
        return "OKMS-No Parts to Deliver";
    }
    $sql = "SELECT order_header.document_reference AS document_reference
                FROM pick_header
                JOIN order_header ON order_header.pick_header_id=pick_header.id
                WHERE pick_header.document_reference=:ref";
    $params = "ref:".$pickRef;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    $docRefs = "";
    foreach ($rows as $row)
    {
        if(strpos($docRefs,$rows[0]['document_reference'] === false))
        {
            if (!empty($docRefs))
            {
                $docRefs .=",";
            }
            $docRefs .= $rows[0]['document_reference'];
        }
    }
    
    foreach ($invRows as $invRow)
    {
        $sql = "UPDATE inventory_master SET current_location_id=".$locRow[0]['id'].",location_id=".$locRow[0]['id'].",location_code='".$locRow[0]['location_code']."',last_updated_by='$mCurrentUser',last_updated='$now' 
                WHERE id=:invid 
                LIMIT 1";
        $params = "invid:".$invRow['id'];
        $result = $db->queryParamsJson($sql, $params);
        try
        {
            $result = json_decode($result,true);
        }
        catch (Exception $e)
        {
        }
        $message = $result['message'];
        $numRows = $result['numrows'];

        $invRow['short_code'] = "TRTOLN";
        $invRow['user'] = $mCurrentUser;
        $invRow['ran_or_order'] = $pickRef;
        $invRow['associated_document_reference'] = $docRefs;
        $invRow['plt_number'] = $invRow['tag_reference'];
        $invRow['to_location_code'] = $locRow[0]['location_code'];
        $return = postTransactionHistory($db,$invRow);
    }
    return "OK  -postToLine";
}

function postPlToLine($post,$db)
{
    $now = date('Y-m-d H:i:s');
    $type = "";
// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "allocationid")
            $mDeleteFlag = strtoupper($val);
        elseif (strtolower($key) == "trolley")
            $mTrolley = strtoupper($val);
        elseif (strtolower($key) == "tolocationhash")
            $mToLocHash = strtoupper($val);
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = strtoupper($val);
        elseif ($key == "transactionType")
            $mTransactionTypeId = strtoupper($val);
    }
    if (empty($mTransactionTypeId))
    {
        $mTransactionTypeId = "PLTOLN";
    }
    
    $sql = "SELECT
                location.id as id,
                location.location_code AS location_code,
                location.inventory_status_id AS inventory_status_id,
                location.location_type_id,
                location_type.location_type_code AS location_type_code,
                location_type.delete_after_pick
            FROM
                location
            LEFT JOIN 
                location_type ON location.location_type_id=location_type.id
            WHERE
                location.location_code = :ref
            OR
                location.location_hash = :ref
            ";
    $sql.= ' LIMIT 1';
    $params = "ref:".$mToLocHash;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $locRow = $result['data'];
    if ($numRows == 0)
    {
       $locRow[0]['location_code'] = $mToLocHash; 
    }
    $locId = $locRow[0]['id'];
    $locCode = $locRow[0]['location_code'];
    
// try to get pallet_tag using serial_reference
    $sql = "SELECT tag_reference FROM inventory_master WHERE serial_reference=:ref LIMIT 1";
    $params = "ref:".$mTrolley;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $invRows = $result['data'];
    if ($numRows > 0)
    {
        if (!empty($invRows[0]['tag_reference']))
        {
            $mTrolley = $invRows[0]['tag_reference'];
        }
    }
    
    $sql = "SELECT * FROM inventory_master WHERE
            tag_reference=:ref OR serial_reference=:ref
    LIMIT 1000";
    $params = "ref:".$mTrolley;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $invRows = $result['data'];
    $pickQty = 0;
    $partNumber=$invRows[0]['part_number'];
    $serialReference=$invRows[0]['serial_reference'];

    if ($numRows == 0)
    {
        return "OKMS-No Parts to Deliver";
    }
// Try to get related Pick Data
    $sql = "SELECT pick_header.document_reference AS pick_reference, order_header.document_reference AS document_reference, pick_detail.id AS dtlid
            FROM pick_header
            JOIN pick_body ON pick_header.id=pick_body.pick_header_id
            JOIN pick_detail ON pick_body.id=pick_detail.pick_body_id
            JOIN order_header ON order_header.pick_header_id=pick_header.id
            WHERE pick_detail.part_number=:part AND pick_detail.serial_reference_scanned=:ref LIMIT 1";
    $params = "part:".$partNumber.",ref:".$serialReference;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    $docRefs = "";
    $pickRef = "";
    foreach ($rows as $row)
    {
        if(strpos($docRefs,$row['document_reference'] === false))
        {
            if (!empty($docRefs))
            {
                $docRefs .=",";
            }
            $docRefs .= $row['document_reference'];
        }
        $pickRef = $row['pick_reference'];
        $pickId = $row['dtlid'];
    }
    if (empty($locId))
    {
        $locId = null;
    }
// Read through selected Inventory 
    foreach ($invRows as $invRow)
    {
// test for remaining inventory
        if ($invRow['inventory_qty'] <= $invRow['picked_qty'])
        {
            $sql = "UPDATE inventory_master
                    SET";
            if (empty($locId))
            {
                $sql .=" current_location_id=null,location_id=null,";
            }
            else
            {
                $sql .=" current_location_id=$locId,location_id=$locId,";
            }
            $sql .="location_code='$locCode',
                    last_updated_by='$mCurrentUser',last_updated='$now' 
                    WHERE id=:invid
                    LIMIT 1";
            $params = "invid:".$invRow['id'];
            $result = $db->queryParamsJson($sql, $params);
            if(is_array($result))
            {
                $message = $result['message'];
                $numRows = $result['numrows'];
            }
            $pickQty = $invRow['picked_qty'];
        }
        else
        {
// Downdate existing inventory
            $sql = "UPDATE inventory_master SET
                    inventory_qty=inventory_qty-picked_qty,allocated_qty=0,picked_qty=0,
                    parent_tag_id=null,tag_reference=null,
                    last_updated_by='$mCurrentUser',last_updated='$now' 
                    WHERE id=:invid 
                    LIMIT 1";
            $params = "invid:".$invRow['id'];
            $result = $db->queryParamsJson($sql, $params);
            try
            {
                $result = json_decode($result,true);
            }
            catch (Exception $e)
            {
            }
            $message = $result['message'];
            $numRows = $result['numrows'];
// create Pick serial
            $sql = "SELECT * 
                    FROM 
                        inventory_master 
                    WHERE 
                        part_number = '".$partNumber."' AND serial_reference LIKE('".$serialReference."%') 
                    ORDER BY id DESC 
                    LIMIT 1";
            $params ="";
            $result = $db->queryParamsJson($sql, $params);
            try
            {
                $result = json_decode($result,true);
            }
            catch (Exception $e)
            {
            }
            $message = $result['message'];
            $numRows = $result['numrows'];
            $pckRows = $result['data'];
            $mInvSerial = $pckRows[0]['serial_reference'];
            $i = strrpos($mInvSerial,'PCK');
            if ($i > 0)
            {
                $i = $i+3;
                $mSuffix = substr($mInvSerial,$i);
                if ($mRow['inventory_qty'] > 0)
                {
                    if (is_numeric($mSuffix))
                    {
                        $i = intval($mSuffix);
                        $i++;
                        $mSuffix = substr('000'.$i,-3);
                    }
                }
                $mSuffix = trim(str_replace('PCK', '', $mSuffix));
                $mSuffix = 'PCK'.$mSuffix;
            }
            else
            {
                $mSuffix = 'PCK001';
            }
// Insert Pick_serial Record
            $serialReference = trim(str_replace('PCK', '', $serialReference));
            $serialReference=$serialReference.$mSuffix;

            if (empty($mRow['location_id']))
                $mRow['location_id']=$mRow['current_location_id'];
            if (empty($mRow['stock_date']))
            {
                $mCurrentTimestamp = time();
                $mFormattedTimestamp = date('Y-m-d H:i:s', $mCurrentTimestamp);
                $invRow['stock_date'] = $mFormattedTimestamp;
            }
            $sql = "SELECT id FROM inventory_master WHERE part_number=:part AND serial_reference=:ref LIMIT 1";
            $params ="part:".$mPartNumber.",ref:".$serialReference;
            $result = $db->queryParamsJson($sql, $params);
            try
            {
                $result = json_decode($result,true);
            }
            catch (Exception $e)
            {
            }
            $message = $result['message'];
            $numRows = $result['numrows'];
            $pckRows = $result['data'];
            if ($numRows == 0)
            {
                $sql = "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)";
                if (empty($invRow['pruf_cube']))
                    $sql .=" VALUES(DEFAULT,0,0,0,null,".$locId.",".$locId.",'".$locCode."','".$invRow['date_created']."','".$mCurrentUser."',null,0,0,now(),'".$mCurrentUser."',null,".$invRow['part_id'].",'".$invRow['part_number']."','".$invRow['ran_or_order']."',0,0,0,'".$mSerialReference."',null,".$invRow['conversion_factor'].",now(),1,".$invRow['inventory_status_id'].",null,null)";
                else
                    $sql .=" VALUES(DEFAULT,0,0,0,null,".$locId.",".$locId.",'".$locCode."','".$invRow['date_created']."','".$mCurrentUser."',null,0,0,now(),'".$mCurrentUser."',null,".$invRow['part_id'].",'".$invRow['part_number']."','".$invRow['ran_or_order']."',0,0,0,'".$mSerialReference."',null,".$invRow['conversion_factor'].",now(),1,".$invRow['inventory_status_id'].",".$invRow['product_type_id'].",".$invRow['pruf_cube'].")";
                $params ="";
                $result = $db->queryParamsJson($sql, $params);
                try
                {
                    $result = json_decode($result,true);
                }
                catch (Exception $e)
                {
                }
                $message = $result['message'];
                $numRows = $result['numrows'];
                $mInvId=$numRows;
            }
            else
            {
                $mInvId = $pckRows[0]['id'];
            }
            if (!empty($pickId))
            {
                $sql = "UPDATE pick_detail SET serial_reference='".$mSerialReference."' WHERE pick_detail.id=".$pickId." LIMIT 1";
                $params ="";
                $result = $db->queryParamsJson($sql, $params);
                try
                {
                    $result = json_decode($result,true);
                }
                catch (Exception $e)
                {
                }
                $message = $result['message'];
                $numRows = $result['numrows'];
                
                $pickQty = $invRow['picked_qty'];
            }
            else
            {
                $pickQty = 0;
            }
        }
        $invRow['part_number'] = $partNumber;
        $invRow['serial_reference'] = $serialReference;
        $invRow['short_code'] = "PLTOLN";
        $invRow['user'] = $mCurrentUser;
        $invRow['ran_or_order'] = $pickRef;
        $invRow['associated_document_reference'] = $docRefs;
        $invRow['plt_number'] = $invRow['tag_reference'];
        $invRow['to_location_code'] = $locCode;
        $invRow['inventory_qty'] = $pickQty;
        $return = postTransactionHistory($db,$invRow);
    }
    return "OK  -postToLine";
}

function deleteInv($post,$db)
{
    $now = date('Y-m-d H:i:s');
    $date = date('Ymd');
    $time = date('His');
    
// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "allocationid")
            $mDeleteFlag = strtoupper($val);
        elseif (strtolower($key) == "trolley")
            $mTrolley = strtoupper($val);
        elseif (strtolower($key) == "tolocationhash")
            $mToLocHash = strtoupper($val);
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = strtoupper($val);
        elseif ($key == "transactionType")
            $mTransactionTypeId = strtoupper($val);
    }
    if (empty($mTransactionTypeId))
    {
        $mTransactionTypeId = "TRTOLN";
    }
    
    $sql = "SELECT
            location.id AS id,
            location.location_code AS location_code,
            location.inventory_status_id AS inventory_status_id,
            location.location_type_id,
            location_type.location_type_code AS location_type_code,
            location_type.delete_after_pick AS delete_after_pick
        FROM location
        LEFT JOIN location_type ON location.location_type_id=location_type.id
        WHERE
            (location.location_code = :loc OR location.location_hash = :loc)
            ";
    $sql .= ' LIMIT 1';
    $params ="loc:".$mToLocHash;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    
    if ($numRows == 0)
    {
        return "FAIL-Location not found";
    }
    
    if ($rows[0]['delete_after_pick'] != 1)
    {
        return "OKMS-Stock Moved to ".$rows[0]['location_code'];
    }
    $mToLocHash = $rows[0]['location_code'];
    
    $sql = "SELECT
            train_locations.id as id,
            train_locations.train_location_code AS train_location_code,
            train_locations.train_location_hash AS train_location_hash, 
            train_locations.train_locations_status_id AS train_locations_status_id,
            train_locations_status.train_locations_status AS train_locations_status
            FROM train_locations
            JOIN train_locations_status ON train_locations.train_locations_status_id=train_locations_status.id
        WHERE
            train_locations.train_location_code = :ref
        OR
            train_locations.train_location_hash = :ref
            ";
    $sql.= ' LIMIT 1';
    $params = "ref:".$mTrolley;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    if ($numRows > 0)
    {
        $loc = $rows[0]['train_location_code'];
    }
    
    $sql = "SELECT * FROM inventory_master WHERE
    location_code = :loc
    LIMIT 1000";
    $params = "loc:".$mToLocHash;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $invRows = $result['data'];
    $pickQty = 0;
    $docRefs = "";
    if ($numRows == 0)
    {
        return "OKMS-No Parts to Deliver";
    }
    foreach ($invRows as $invRow)
    {
        $partNumber = $invRow['part_number'];
        $serialReference = $invRow['serial_reference'];
        $pickQty = $pickQty + $invRow['inventory_qty'];
        $sql = "DELETE FROM inventory_master WHERE id=:invid LIMIT 1";
        $params = "invid:".$invRow['id'];
        $result = $db->queryParamsJson($sql, $params);
        try
        {
            $result = json_decode($result,true);
        }
        catch (Exception $e)
        {
        }
        $message = $result['message'];
        $numRows = $result['numrows'];
        if ($numRows == 0)
        {
            $sql = "UPDATE inventory_master SET inventory_qty=0,allocated_qty=0,last_updated_by='$mCurrentUser',last_updated='$now' WHERE id=:invid LIMIT 1";
            $params = "invid:".$invRow['id'];
            $result = $db->queryParamsJson($sql, $params);
            try
            {
                $result = json_decode($result,true);
            }
            catch (Exception $e)
            {
            }
            $message = $result['message'];
            $numRows = $result['numrows'];
        }
    }
    $sql = "SELECT pick_header.id AS id, pick_detail.document_reference AS pick_reference
                FROM pick_detail
                JOIN pick_body ON pick_body.id=pick_detail.pick_body_id
                JOIN pick_header ON pick_header.id=pick_body.pick_header_id
                WHERE pick_detail.part_number-:part AND pick_detail.serial_reference_scanned=:ref
                LIMIT 1";
    $params = "part:".$partNumber.",ref:".$serialReference;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    $pickRef = $rows[0]['pick_reference'];
    $pickId = $rows[0]['id'];
    $sql = "SELECT pick_header.document_reference AS pick_reference, order_header.document_reference AS document_reference
                FROM order_header
                JOIN pick_header ON order_header.pick_header_id=pick_header.id
                WHERE order_header.pick_header_id=:id
                LIMIT 10";
    $params = "id:".$pickId;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    $docRefs = "";
    foreach ($rows AS $row)
    {
        if(strpos($docRefs,$row['document_reference'] === false))
        {
            if (!empty($docRefs))
            {
                $docRefs .=",";
            }
            $docRefs .= $row['document_reference'];
        }
    }
    $invrow = array();
    $invRow['short_code'] = "SHIP";
    $invRow['user'] = $mCurrentUser;
    $invRow['inventory_qty'] = $pickQty;
    $invRow['document_reference'] = $pickRef;
    $invRow['ran_or_order'] = $pickRef;
    $invRow['associated_document_reference'] = $docRefs;
    $invRow['customer_reference']=null;
    $invRow['part_number']=null;
    $invRow['serial_reference']=null;
    $invRow['plt_number'] = $invRows[0]['ran_or_order'];
    $invRow['location_code'] = $mToLocHash;
    $invRow['to_location_code'] = null;
    $return = postTransactionHistory($db,$invRow);
//    return "OK  -deleteMtt ";
    return "OKMS-Shipping Complete for ".$mToLocHash;
}

function postTransactionHistory($db,$row)
{
    $now = date('Y-m-d H:i:s');
    $sql = "INSERT INTO transaction_history
            (short_code,transaction_reference_code,associated_document_reference,customer_reference,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)";
    $params = 
    [
        $row['short_code'],$row['short_code'],$row['associated_document_reference'],$row['customer_reference'],$row['part_number'],$row['serial_reference'],$row['ran_or_order'],$row['inventory_qty'],$row['plt_number'],$row['location_code'],$row['to_location_code'],$row['user'],$now,$now,$row['user']
    ];
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
}

function setFillingStatus($post,$db)
{
    $now = date('Y-m-d H:i:s');
    $date = date('Ymd');
    $time = date('His');
    
// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "locationcode")
            $mLocationCode = strtoupper($val);
        elseif (strtolower($key) == "status")
            $mStatus = strtoupper($val);
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = strtoupper($val);
        elseif ($key == "transactionType")
            $mTransactionTypeId = strtoupper($val);
    }
    if (empty($mTransactionTypeId))
    {
        $mTransactionTypeId = "TRTOLN";
    }
    $now = date('Y-m-d H:i:s');
    $sql = "SELECT inventory_master.id AS id 
            FROM inventory_master
            JOIN location ON inventory_master.current_location_id=location.id
            WHERE 
            location.location_code=:loc
            OR
            location.location_hash=:loc
            LIMIT 1";
    $params = "loc:".$mLocationCode;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $invRows = $result['data'];
    if ($numRows > 0)
    {
        if (empty($mStatus))
        {
            $mStatus='FILLING';
        }
    }
    else
    {
        if (empty($mStatus))
        {
            $mStatus='EMPTY';
        }
    }
    $sql = "SELECT id FROM location_filling_status WHERE filling_code=:status LIMIT 1";
    $params = "status:".$mStatus;
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $invRows = $result['data'];
    if ($numRows > 0)
    {
        $id = $invRows[0]['id'];
        $sql = "UPDATE location SET filling_status_id=:id WHERE location_code=:loc LIMIT 1";
        $params = "id:".$id.",loc:".$mLocationCode;
        $result = $db->queryParamsJson($sql, $params);
        try
        {
            $result = json_decode($result,true);
        }
        catch (Exception $e)
        {
        }
        $message = $result['message'];
        $numRows = $result['numrows'];
    }
    return "OK  -setLocationFillingStatus";
}

function getSerialCount($post,$db)
{
    $now = date('Y-m-d H:i:s');
    $date = date('Ymd');
    $time = date('His');
    
// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "locationcode")
            $mLocationCode = strtoupper($val);
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = strtoupper($val);
        elseif ($key == "transactionType")
            $mTransactionTypeId = strtoupper($val);
    }
    if (empty($mTransactionTypeId))
    {
        $mTransactionTypeId = "TRTOLN";
    }
    $now = date('Y-m-d H:i:s');
    
    $sql = "SELECT location_type.location_type_code AS location_type_code
            FROM location
            JOIN location_type ON location.location_type_id=location_type.id
            WHERE location_code=:loc OR location_hash=:loc";
    $params = "loc:".$mLocationCode; 
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    $locType = $rows[0]['location_type_code'];
    
    $sql = "SELECT COUNT(inventory_master.id) as count 
            FROM inventory_master
            JOIN location ON inventory_master.current_location_id=location.id
            WHERE location.location_code=:loc OR location.location_hash=:loc";
    $params = "loc:".$mLocationCode; 
    $result = $db->queryParamsJson($sql, $params);
    try
    {
        $result = json_decode($result,true);
    }
    catch (Exception $e)
    {
    }
    $message = $result['message'];
    $numRows = $result['numrows'];
    $rows = $result['data'];
    if ($numRows > 0)
    {
        if (strtoupper($locType) == "TRL")
        {
            return "TEXT-<i class='fas fa-truck'></i> <strong>" . htmlspecialchars($rows[0]['count']) . "</strong>";
        }
        else
        {
            return "TEXT-<i class='fas fa-building'</i> <strong>" . htmlspecialchars($rows[0]['count']) . "</strong>";
        }
    }
    return "OK  -getSerialCount";
}
