<?php
// PI Count
// VIMS RDT PI Processing
// ----------------------------------------------------------------------------------------
// Modified 2019-05-08 - fixed pi_header update errors.
// 		
// ----------------------------------------------------------------------------------------

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);

// ****************************************************************************
// Includes and Requires
// ****************************************************************************
require_once __DIR__ . '/./helpers.php';


// ****************************************************************************
// Error Handler 
// ****************************************************************************
// Sets error handler function
// set_error_handler('rdtErrorHandler', E_ERROR | E_COMPILE_ERROR | E_CORE_ERROR | E_USER_ERROR);


// ****************************************************************************
// Local Utility Functions
// ****************************************************************************

/**
 * Retrieves status document status id from from document_status table
 *
 * @param string $documentStatusCode
 * @param object $connection
 * @return int
 */
function getDocumentStatusId(string $documentStatusCode, object $connection): int
{
    $query = 'SELECT
            id
        FROM document_status
        WHERE LOWER(document_status_code) = ?
        LIMIT 1';

    if (!$stmt = $connection->prepare($query))
    {
        exit('FAIL-' . $connection->error . ' - ' . $query);
    }

    $stmt->bind_param('s', $documentStatusCode);

    if (!$stmt->execute())
    {
        exit('FAIL-' . $stmt->error . ' - ' . __FUNCTION__);
    }

    $result = $stmt->get_result();
    ['id' => $documentStatusId] = $result->fetch_assoc();

    $result->free();
    $stmt->close();

    return $documentStatusId;
}


/**
 * Retrieves the pi_body_id associated with a given pi_header_id.
 *
 * @param int $piHeaderId The pi_header_id for which to retrieve the pi_body_id.
 * @param object $connection The database connection object.
 * @return int The pi_body_id associated with the given pi_header_id.
 */
function getPiBodyId(int $piHeaderId, object $connection): int
{
    $query = 'SELECT
            id
        FROM pi_body
        WHERE pi_header_id = ?';

    $stmt = $connection->prepare($query);
    $stmt->bind_param('i', $piHeaderId);
    $stmt->execute();

    if (!$result = $stmt->get_result())
    {
        exit('FAIL-' . $connection->error . '-' . $query . '-' . $result->num_rows);
    }

    ['id' => $piBodyId] = $result->fetch_assoc();

    $result->free();
    $stmt->close();

    return $piBodyId;
}


/**
 * Checks if an invalid serial exists in the pi_detail table.
 *
 * @param string $serialReference The serial reference to check.
 * @param int $piBodyId The pi_body_id to check.
 * @param object $connection The database connection object.
 * @return bool Returns true if an invalid serial is found, false otherwise.
 */
function checkInvalidSerialOnPiDetail(string $serialReference, int $piBodyId, object $connection): bool
{
    $query = 'SELECT id 
		FROM pi_detail
		WHERE 
			serial_reference = ? AND 
			pi_body_id = ? AND
			is_serial_invalid = 1
	';

    $stmt = $connection->prepare($query);
    $stmt->bind_param('si', $serialReference, $piBodyId);

    if (!$stmt->execute())
    {
        exit("FAIL- {$connection->error} - $query");
    }

    $result = $stmt->get_result();
    $isRowPresent = $result->num_rows > 0;

    $result->free();
    $stmt->close();

    return $isRowPresent;
}


/**
 * Retrieves the variance value from the pi_header table.
 *
 * @param int $piHeaderId The pi_header_id to retrieve the variance value for.
 * @param object $connection The database connection object.
 * @return int The variance value as an integer.
 */
function getVarienceValue(int $piHeaderId, object $connection): int
{
    $query = 'SELECT
            varience_value
        FROM pi_header
        WHERE id = ?
        LIMIT 1';

    $stmt = $connection->prepare($query);
    $stmt->bind_param('i', $piHeaderId);

    if (!$stmt->execute())
    {
        exit("FAIL- {$connection->error} - $query");
    }

    $result = $stmt->get_result();
    ['varience_value' => $varienceValue] = $result->fetch_assoc();

    $result->free();
    $stmt->close();

    return (int) $varienceValue;
}


/**
 * Checks if a given part number exists in the e2part table.
 *
 * @param string $partNumber The part number to check.
 * @param mysqli $connection The database connection instance.
 * @return bool Returns true if the part number exists in the e2part table, false otherwise.
 */
function isPartNumberOnE2partTable(string $partNumber, mysqli $connection): bool
{
    $query = 'SELECT
            id
        FROM e2part
        WHERE part_number = ?';

    $stmt = $connection->prepare($query);
    $stmt->bind_param('s', $partNumber);

    if (!$stmt->execute())
    {
        exit('FAIL-' . $connection->error . ' - ' . $query);
    }

    $result = $stmt->get_result();
    $numOfRows = $result->num_rows;

    $result->free();
    $stmt->close();

    return $numOfRows > 0;
}


/**
 * Gets item_cost value from e2part table for provided part number.
 *
 * @param string $partNumber The part number of the item.
 * @param mysqli $connection The database connection.
 * @return float The cost of the item. If the item is not found, returns 0.0.
 */
function getItemCost(string $partNumber, mysqli $connection): float
{
    $query = 'SELECT item_cost FROM e2part WHERE part_number = ?';
    $stmt = $connection->prepare($query);
    $stmt->bind_param('s', $partNumber);
    if (!$stmt->execute())
    {
        exit('FAIL-' . $connection->error . ' - ' . $query);
    }
    $result = $stmt->get_result();
    ['item_cost' => $itemCost] = $result->fetch_assoc();

    $result->free();
    $stmt->close();

    return ($itemCost === null) ? 0.0 : (float) $itemCost;
}


/**
 * Retrieves the sum of the "difference" column from the "pi_detail" table,
 * based on the given "pi_header_id".
 *
 * @param int $piHeaderId The ID of the PI header.
 * @param mysqli $connection The mysqli connection object.
 * @return int The sum of the "difference" column, or 0 if the sum is null or not found.
 */
function getDifferenceValue(int $piHeaderId, mysqli $connection): int
{
    $query = 'SELECT 
            SUM(pi_detail.difference) AS difference_sum
        FROM pi_detail 
            JOIN pi_body ON pi_body.id = pi_detail.pi_body_id
        WHERE pi_body.pi_header_id = ?';

    $stmt = $connection->prepare($query);
    $stmt->bind_param('i', $piHeaderId);

    if (!$stmt->execute())
    {
        exit('FAIL-' . $connection->error . ' - ' . $query);
    }

    $result = $stmt->get_result();
    ['difference_sum' => $differenceSum] = $result->fetch_assoc();

    $result->free();
    $stmt->close();

    return $differenceSum ? (int) $differenceSum : 0;
}


/**
 * Updates the varience_value column in the pi_header table for the given piHeaderId.
 *
 * @param int $piHeaderId The id of the pi_header row to update.
 * @param float $varienceValue The new value for the varience_value column.
 * @param string $user The user who is performing the update.
 * @param object $connection The database connection object.
 * @return bool True if the update was successful, false otherwise.
 */
function setVarienceValue(int $piHeaderId, float $varienceValue, string $user, object $connection): bool
{

    $query = 'UPDATE pi_header
        SET
            varience_value = ?,
            last_updated_date = NOW(),
            last_updated_by = ?
        WHERE id = ?
    ';

    $stmt = $connection->prepare($query);
    $stmt->bind_param('dsi', $varienceValue, $user, $piHeaderId);

    if (!$stmt->execute())
    {
        exit('FAIL-' . $connection->error . ' - ' . $query);
    }

    $affectedRows = $stmt->affected_rows;
    $stmt->close();

    return $affectedRows > 0;
}


/**
 * Closes the PI header and updates its status and related data.
 *
 * @param int $mPiHdrId The ID of the PI header.
 * @param int $mDocId The ID of the document status.
 * @param string $mPartNumber The part number.
 * @param string $mCurrentUser The current user.
 * @param mysqli $mConnection The mysqli database connection object.
 * @return string Returns an OKMS response message indicating the status of the operation.
 * If an error occurs, the response message will start with 'FAIL-' followed by the error message and the query.
 * If the document is closed with no differences, the response message will start with 'OKMS- Post Completed - Document Closed'.
 * If the document is closed with some differences, the response message will start with 'OKMS- Post Completed with difference'.
 * If the item cost is not found, the response message will start with 'OKMS- Post Completed. Item cost not found'.
 */
function closePiHeader(int $mPiHdrId, int $mDocId, string $mPartNumber, string $mCurrentUser, mysqli $mConnection): string
{
    $mDataQuery = "SELECT
            sum(actual_qty) AS actual_qty,
            sum(expected_qty) AS expected_qty
        FROM pi_body
        WHERE pi_header_id = {$mPiHdrId}";

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

    $mRow = mysqli_fetch_assoc($mData);

    $mDataQuery = "UPDATE pi_header
        SET
            document_status_id = {$mDocId},
            actual_case_qty = {$mRow['actual_qty']},
            expected_case_qty = {$mRow['expected_qty']},
            difference = (actual_case_qty - expected_case_qty),
            last_updated_date = now(),
            last_updated_by = '{$mCurrentUser}'
        WHERE id = {$mPiHdrId}";

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

    $mErrMsg = 'FAIL-P.I Count Closed for ' . $mPartNumber . ',PIPARTCOUNT';

    // Updates PI Count Variance and closes document
    try
    {
        // Checks if part number exists in e2part table
        if (isPartNumberOnE2partTable($mPartNumber, $mConnection))
        {
            // Gets item_cost value from e2part table for provided part number
            $itemCost = getItemCost($mPartNumber, $mConnection);

            // Gets difference value from pi_body table for provided pi_header_id
            $differenceSum = getDifferenceValue($mPiHdrId, $mConnection);

            // Sets variance value for part number found on e2part table
            $varienceValue = $itemCost * $differenceSum;
            $varienceValue = round($varienceValue, 2);

            // Updates varience_value on pi_header table
            setVarienceValue($mPiHdrId, $varienceValue, $mCurrentUser, $mConnection);

            // Updates document_status_id on pi_header and pi_body if document difference is 0
            if (getVarienceValue($mPiHdrId, $mConnection) === 0)
            {
                // Gets document status id
                $mDocId = getDocumentStatusId('closed', $mConnection);
                if ($mDocId === '')
                {
                    $mDocId = 3;
                }

                // Updates document_status_id on pi_body
                $piBodyQuery = 'UPDATE pi_body
							SET
								document_status_id = ?,
								last_updated_date = NOW(),
								last_updated_by = ?
							WHERE
								pi_header_id = ?
						';

                $stmt = $mConnection->prepare($piBodyQuery);
                $stmt->bind_param('isi', $mDocId, $mCurrentUser, $mPiHdrId);
                if (!$stmt->execute())
                {
                    throw new Exception ($stmt->error . ' - ' . $piBodyQuery);
                }

                // Updates document_statut_id on pi_header
                $piHeaderQuery = 'UPDATE pi_header
							SET
								document_status_id = ?,
								last_updated_date = NOW(),
								last_updated_by = ?
							WHERE
								id = ?
						';

                $stmt = $mConnection->prepare($piHeaderQuery);
                $stmt->bind_param('isi', $mDocId, $mCurrentUser, $mPiHdrId);

                if (!$stmt->execute())
                {
                    return ('FAIL-' . $stmt->error . '-' . $piHeaderQuery);
                }

                // Response message without difference
                exit('OKMS- Post Completed - Document Closed,PIPARTLOC');
            }
            else
            {
                // exit('FAIL-' . __FUNCTION__ . ' ' . __LINE__);
                exit('OKMS- Post Completed with difference,PIPARTLOC');

            }
        }
        else
        {
            // Response message if item cost was not found
            exit('OKMS- Post Completed. Item cost not found,PIPARTLOC');
        }
    }
    catch (Exception $e)
    {
        // exit("FAIL-Connection Error at - ".$mConnect.$mFilter.":".$e);
        exit('FAIL- ' . $e);
    }
}


/**
 * Formats the document reference code for a PI header ID.
 *
 * @param int $piHeaderId The PI header ID.
 * @param int $numLength The length of the numeric portion of the document reference code. Default is 6.
 *
 * @return string The formatted document reference code.
 */
function formatDocumentReferenceCode(int $piHeaderId, int $numLength = 6): string
{
    $docNum = str_pad($piHeaderId, 6, '0', STR_PAD_LEFT);
    return 'PI' . $docNum;
}


// ****************************************************************************
// Global variables definition
// ****************************************************************************
$mFunction = $_GET['function'];
$mConnect = $_GET['connection'];
$mUser = $_GET['user'];
$mPwd = $_GET['pword'];
$mHost = $_GET['host'];
$mDbName = $_GET['dbase'];
$mTableName = $_GET['table'];
if (isset($_GET['filter']))
{
    $mFilter = $_GET['filter'];
}
$mReturnFields = $_GET['returnfields'];

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

$mErrMsg = "OK  -";


// ****************************************************************************
// Function Routers
// ****************************************************************************

// Validate Pi
if (($mFunction != null) && ($mFunction == "validatepi"))
{
    $mErrMsg = "";

    // Explode Input Parm
    // Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    $i = 0;
    $mPartNumber = null;
    $mSerialReference = null;
    $mFromLocation = null;
    $mVldLocationHash = "";
    $mVldPartNumber = "";
    $mVldSerialReference = "";
    $mQty = "";
    $mVldQty = "";
    $mCurrentUser = "UNKNOWN";

    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);

    // Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($mArray1); $i++)
    {
        $s = 0;
        $e = 0;
        $k1 = $mArray1[$i];
        $s = strpos($k1, ":");
        if ($s >= 0)
        {
            $k2 = substr($k1, 0, $s);
            $v2 = substr($k1, $s + 1);
        }
        if ($k2 == "partnumber")
        {
            $mPartNumber = strtoupper(trim($v2));
        }
        elseif ($k2 == "serialreference")
        {
            $mSerialReference = strtoupper(trim($v2));
        }
        elseif ($k2 == "fromlocationhashcode")
        {
            $mFromLocation = strtoupper(trim($v2));
        }
        elseif ($k2 == "vldpartnumber")
        {
            $mVldPartNumber = strtoupper(trim($v2));
        }
        elseif ($k2 == "vldlocation")
        {
            $mVldLocation = strtoupper(trim($v2));
        }
        elseif ($k2 == "vldserialreference")
        {
            $mVldSerialReference = strtoupper(trim($v2));
        }
        elseif ((strtolower($k2) == "quantity") || (strtolower($k2) == "countqty"))
        {
            $mQty = floatval($v2);
        }
        elseif ((strtolower($k2) == "vldquantity") || (strtolower($k2) == "vldqty"))
        {
            $mVldQty = floatval($v2);
        }
        elseif ((strtolower($k2) == "orderqty"))
        {
            $mOrderQty = floatval($v2);
        }
        elseif ($k2 == "currentUser")
        {
            $mCurrentUser = trim($v2);
        }
        elseif ($k2 == "transactionType")
        {
            $mTransactionTypeId = strtoupper(trim($v2));
        }
        elseif ((strtolower($k2) == "transactiontypeid"))
        {
            $mTransactionTypeId = strtoupper(trim($v2));
        }
    }
    if (!$mFromLocation)
    {
        $mFromLocation = 'none';
    }
    $mErrMsg = validatePi($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mFromLocation, $mVldLocation, $mPartNumber, $mVldPartNumber, $mSerialReference, $mVldSerialReference, $mQty, $mVldQty, $mTransactionTypeId);
    echo $mErrMsg;
    return;
}

// Gets Part list for PIPARTLOC screen
if (($mFunction != null) && (strtolower($mFunction) == "getpipartlist"))
{
    $mErrMsg = "";
    $mReference = "";

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

    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);

    // Create Associative Array by Exploding Array Elements on :
    foreach ($mArray1 as $k1 => $v1)
    {
        list($k2, $v2) = explode(":", $mArray1[$k1]);
        if ($k2 == "currentUser")
        {
            $mCurrentUser = trim($v2);
        }
        elseif ($k2 == "responsetype")
        {
            $mResponseType = strtoupper(trim($v2));
        }
        elseif ($k2 == "pipartnumber")
        {
            $mPartNumber = trim($v2);
        }
    }

    $mStatus = getPiPartList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mResponseType, $mPartNumber);

    exit($mStatus);
}


// ****************************************************************************
// Main Functions
// ****************************************************************************

/**
 * Gets PI Part Number list for RDT Dropdowns.
 *
 * @param string $mHost The hostname of the database.
 * @param string $mUser The username to connect to the database.
 * @param string $mPwd The password to connect to the database.
 * @param string $mDbName The name of the database.
 * @param string $mCurrentUser The current user.
 * @param string $mResponseType The response type.
 * @param string $mPartNumber (Optional) The part number to filter the result by.
 *
 * @return string Returns the response data based on the response type.
 */
function getPiPartList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mResponseType, $mPartNumber = "")
{
    // mysql connection - Use Passed values...
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
    exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());

    $mDataQuery = "";
    if ($mPartNumber)
    {
        $mDataQuery = "SELECT
				MAX(pi_header.id) AS pi_header_id,
				pi_header.part_number AS part_number
			FROM pi_header
			LEFT JOIN document_status ON document_status_id = document_status.id
			WHERE
				part_number = ? AND 
				(document_status_id IS NULL OR LOWER(document_status_code) NOT IN ('closed'))
			GROUP BY pi_header.part_number, pi_header.date_created
			ORDER BY pi_header.date_created
		";
    }
    else
    {
        $mDataQuery = "SELECT
				MAX(pi_header.id) AS pi_header_id,
				pi_header.part_number AS part_number
			FROM pi_header
			LEFT JOIN document_status ON document_status_id = document_status.id
			WHERE 
				part_number <> '' AND
				(document_status_id IS NULL OR LOWER(document_status_code) NOT IN ('closed'))
			GROUP BY pi_header.part_number, pi_header.date_created
			ORDER BY pi_header.date_created, pi_header.part_number
		";
    }

    if (!$stmt = $mConnection->prepare($mDataQuery))
    {
        exit("FAIL- " . $mConnection->error);
    }

    if ($mPartNumber)
    {
        $stmt->bind_param("s", $mPartNumber);
    }

    if (!$stmt->execute())
    {
        exit("FAIL- " . $stmt->error);
    }

    $mData = $stmt->get_result();

    // Prepares response data
    $mDropdown = [];
    while ($mRow = $mData->fetch_assoc())
    {
        $mDropdown[] = [
            'value' => "allocationId:{$mRow['pi_header_id']},partNumber:{$mRow['part_number']}",
            'text' => $mRow['part_number']
        ];
    }
    $mResponseData = "";
    if ($mResponseType === "DROP")
    {
        $mResponseData = "{$mResponseType}-" . json_encode($mDropdown);
    }
    else
    {
        $mResponseData = "{$mResponseType}:{$mDropdown[0]['value']}"; // Used when responseTupe is 'DATA'
    }

    $mData->free();
    $mConnection->close();

    return $mResponseData;
}


/**
 * Validates the PI Location
 *
 * @param string $mHost The host of the database connection.
 * @param string $mUser The username for the database connection.
 * @param string $mPwd The password for the database connection.
 * @param string $mDbName The name of the database.
 * @param string $mCurrentUser The current user.
 * @param string $mFromLocation The location from where the validation is performed.
 * @param string $mVldLocation The validated location.
 * @param string $mPartNumber The part number.
 * @param string $mVldPartNumber The validated part number.
 * @param string $mSerialReference The serial reference.
 * @param string $mVldSerialReference The validated serial reference.
 * @param int $mQty The quantity.
 * @param int $mVldQty The validated quantity.
 * @param int $mTransactionTypeId The transaction type ID.
 * @return string Returns "OK -validatePi" if the PI is valid, otherwise returns an error message.
 */
function validatePi($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mFromLocation, $mVldLocation, $mPartNumber, $mVldPartNumber, $mSerialReference, $mVldSerialReference, $mQty, $mVldQty, $mTransactionTypeId)
{
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
    exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());

    $mErrMsg = "";

    if (($mErrMsg == "") && ($mFromLocation != ""))
    {
        $mNoSerialScan = 0;
        $mDataQuery = "SELECT
                location.location_code AS location_code,
                location_type.no_serial_scan AS no_serial_scan
            FROM location
                LEFT JOIN location_type ON location.location_type_id = location_type.id
            WHERE location.location_hash = '{$mFromLocation}'
            LIMIT 1";

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

        $i = 0;
        list($mLocCode, $mNoSerialScan) = mysqli_fetch_row($mData);
    }

    if ($mErrMsg == "")
    {
        if ($mNoSerialScan == 0)
        {
            if (($mSerialReference == null) || ($mSerialReference == ""))
            {
                $mErrMsg = "DATA:serialReference:::skipserialReference";
            }
        }
        else
        {
            if (!$mQty || $mQty == null || $mQty == "")
            {
                //                return "FAIL-TEST-".$mFromLocation."-".$mNoSerialScan."-".$mSerialReference."-".$mQty."-".$mVldSerialReference;
                if (($mSerialReference == null || $mSerialReference == ""))
                {
                    if (($mVldSerialReference != null) && ($mVldSerialReference != ""))
                    {
                        $mErrMsg = "DATA:serialReferenceinput:" . $mVldSerialReference . "::,scanQty:::skipscanQty";
                        return $mErrMsg;
                    }
                    else
                    {
                        $mErrMsg = "DATA:serialReference:::skipserialReference";
                        return $mErrMsg;
                    }
                }
                else
                {
                    $mErrMsg = "DATA:serialReferenceinput:" . $mSerialReference . "::,scanQty:::skipscanQty";
                    return $mErrMsg;
                }
            }
        }
    }
    if ($mErrMsg == "")
    {
        return "OK  -validatePi";
    }
    else
    {
        return $mErrMsg;
    }
}


/**
 * Get Oldest PI Part.
 *
 * @param string $mHost The database host.
 * @param string $mUser The database username.
 * @param string $mPwd The database password.
 * @param string $mDbName The database name.
 * @param string $mPiPartNumber (optional) The PI part number to filter the results.
 * @return string Returns the PI summary if found, otherwise returns an error message.
 */
function piPartPrompt($mHost, $mUser, $mPwd, $mDbName, $mPiPartNumber = "", $mCurrentUser = "")
{
    $mDataStr = "";

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

    if ($mPiPartNumber != "")
    {
        $mDataQuery = "SELECT 
                pi_header.id AS id,
                pi_header.part_number AS part_number
            FROM pi_header
                LEFT JOIN document_status ON document_status_id = document_status.id
            WHERE 
                part_number <> ''
                AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
                AND part_number='{$mPiPartNumber}'
            ORDER by pi_header.date_created, pi_header.part_number
            LIMIT 1";
    }
    else
    {
        $mDataQuery = "SELECT
		        pi_header.id AS id,
		        pi_header.part_number AS part_number
            FROM pi_header
                LEFT JOIN document_status ON document_status_id = document_status.id
            WHERE part_number <> '' AND (document_status_id IS NULL OR LOWER(document_status_code) NOT IN ('closed'))
            ORDER BY pi_header.date_created, pi_header.part_number
            LIMIT 1";
    }

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

    $i = 0;
    if (mysqli_num_rows($mData) > 0)
    {
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mPiPartNumber = ($mRow['part_number']);
        }
        $mReturn = piPartSummary($mHost, $mUser, $mPwd, $mDbName, $mPiPartNumber, $mCurrentUser, $mTransactionType = "PIPARTLOC", $mConnect = "");
    }
    else
    {
        if ($mPiPartNumber != "")
        {
            $mReturn = "OKMS-No Active P.I Counts for " . $mPiPartNumber . ",MENUPI";
        }
        else
        {
            $mReturn = "OKMS-No Active P.I Counts,MENUPI";
        }
    }
    return $mReturn;
}


/**
 * Retrieves the next PI location for a given part number in a physical inventory.
 *
 * @param string $mHost The database host address.
 * @param string $mUser The database username.
 * @param string $mPwd The database password.
 * @param string $mDbName The database name.
 * @param string $mPartNumber The part number to get the next location for.
 * @param string $mCurrentUser The current user.
 * @param string $mFromLocation (optional) The location to exclude from the search.
 * @param string $mConnect (optional) Additional connection parameters.
 *
 * @return string Returns the next available location for the given part number, or a failure message.
 */
function piNextLocation($mHost, $mUser, $mPwd, $mDbName, $mPartNumber, $mCurrentUser = "", $mFromLocation = "", $mConnect = "")
{
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
    
    // Gets pi_header.id for provided part number
    $mPiHdrId = "";
    $mDataQuery = "SELECT
            pi_header.id AS id 
        FROM pi_header
            LEFT JOIN document_status ON document_status_id = document_status.id
        WHERE
            part_number = '{$mPartNumber}'
            AND (document_status.id IS NULL OR lower(document_status_code) <> 'closed')
        LIMIT 1";

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

    if (mysqli_num_rows($mData) > 0)
    {
        ['id' => $mPiHdrId] = $mData->fetch_assoc();
    }
    else
    {
        if (!$mFromLocation)
        {
            return "FAIL-No Location";
        }
        elseif (!$mPartNumber)
        {
            return "FAIL-Please provide a valid part number";
        }
        else
        {
            return ("FAIL-No P.I Count for - $mPartNumber");
        }
    }

    // Gets location_code and hash_code
    $mDataQuery = "SELECT
            location.location_code AS location_code,
            location.location_hash AS hash_code
        FROM location 
            JOIN location_type ON location.location_type_id = location_type.id
            JOIN pi_body ON pi_body.location_code = location.location_code 
            LEFT JOIN document_status ON pi_body.document_status_id = document_status.id
        WHERE 
            pi_body.part_number = '{$mPartNumber}'
            AND pi_body.pi_header_id = {$mPiHdrId}
            AND ((pi_body.document_status_id IS NULL) OR (lower(document_status.document_status_code) NOT IN ('closed')))
            AND lower(location_type.location_area) = 'storage'
        ORDER BY location_code
        LIMIT 1";

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

    if ($mFromLocation || mysqli_num_rows($mData) == 0)
    {
        $mDataQuery = "SELECT
				location.location_code AS location_code,
				location.location_hash AS hash_code,
                location_sub_type.location_sub_type_code AS location_sub_type_code
			FROM location 
                JOIN location_type ON location.location_type_id = location_type.id
                JOIN inventory_master ON inventory_master.current_location_id = location.id
			    LEFT JOIN location_sub_type ON location.location_sub_type_id = location_sub_type.id
                JOIN part ON inventory_master.part_id = part.id
                LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id
			WHERE
				part.part_number = '{$mPartNumber}'
				-- AND lower(location_type.location_area) = 'storage'
			    AND lower(location_sub_type.location_sub_type_code) <> 'dock'
				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'))
				AND NOT EXISTS (SELECT id FROM pi_body WHERE pi_body.pi_header_id = {$mPiHdrId} AND pi_body.location_code = location.location_code)
			ORDER BY location_code
			LIMIT 1";

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

    if ((mysqli_num_rows($mData) == 0) && ($mPiHdrId != ''))
    {
        // Checks for locations with scanned differences
        $query = "SELECT
                pi_body.location_code AS location_code
            FROM pi_body
                JOIN pi_detail ON pi_detail.pi_body_id = pi_body.id
            WHERE 
                pi_detail.difference <> 0
                AND pi_body.pi_header_id = ?
            LIMIT 1";

        $stmt = $mConnection->prepare($query);
        $stmt->bind_param('i', $mPiHdrId);
        if (!$stmt->execute())
        {
            return ('FAIL-' . mysqli_error($mConnection) . '-' . $query);
        }
        $mData = $stmt->get_result();

        if ($mData->num_rows === 0)
        {
            return ('FAIL-No more locations for this Part Number');
        }

        //		$mDataQuery = "UPDATE pi_header SET document_status_id=3 WHERE id=".$mPiId." LIMIT 1";
        // $mDataQuery = "SELECT
        //         sum(actual_qty) AS actual_qty,
        //         sum(expected_qty) AS expected_qty
        //     FROM pi_body
        //     WHERE pi_header_id = {$mPiHdrId}";
        // $mActQty = 0;
        // $mExpQty = 0;
        // while ($mRow = mysqli_fetch_assoc($mData))
        // {
        //     $mActQty = ($mRow['actual_qty']);
        //     $mExpQty = ($mRow['expected_qty']);
        // }
        // $mDataQuery = "UPDATE pi_header
        //     SET document_status_id = (SELECT id FROM document_status WHERE lower(document_status_code) = 'closed'),
        //         actual_case_qty = {$mActQty},
        //         expected_case_qty = {$mExpQty},
        //         difference = (actual_case_qty - expected_case_qty),
        //         last_updated_date = now(),
        //         last_updated_by = '{$mCurrentUser}'
        //     WHERE id={$mPiHdrId}
        //     LIMIT 1";
        //
        // if (!mysqli_query($mConnection, $mDataQuery))
        // {
        //     return ('FAIL-' . mysqli_error($mConnection) . '-' . $mDataQuery);
        // }
    }


    $mPiLocation = "UNKNOWN";
    $i = 0;
    if (mysqli_num_rows($mData) > 0)
    {
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mPiLocation = ($mRow['location_code']);
        }
    }

    // if ($mPiLocation === $mFromLocation)
    // {
    //     exit('FAIL- Please check items at ' . $mPiLocation);
    // }

    return $mPiLocation;
}


/**
 * Generates the summary of PI parts.
 *
 * @param string $mHost The hostname of the MySQL server.
 * @param string $mUser The username for the MySQL server.
 * @param string $mPwd The password for the MySQL server.
 * @param string $mDbName The name of the MySQL database.
 * @param string $mPartNumber The part number to filter the results.
 * @param string $mCurrentUser The current user.
 * @param string $mTransactionType The transaction type.
 * @param string $mConnect (optional) The additional connection information.
 *
 * @return string Returns the summary of PI parts.
 */
function piPartSummary($mHost, $mUser, $mPwd, $mDbName, $mPartNumber, $mCurrentUser = "", $mTransactionType = "", $mConnect = "")
{
    $mDataStr = "";
    $mTransactionType = "PIPARTLOC";
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
    exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
    // Check if PI By Part is allowed
    $mPiByPart = "0";

    if ($mPiByPart == "1")
    {
        return "CHGTRAN:" . $mTransactionType;
    }

    // Read Selected PI Headers
    if ($mPartNumber == "")
    {
        $mDataQuery = "SELECT
		        pi_header.id AS id,
		        pi_header.part_number AS part_number
            FROM pi_header
                LEFT JOIN document_status ON document_status_id = document_status.id
            WHERE 
                part_number <> ''
                AND (document_status_id IS NULL OR LOWER(document_status_code) NOT IN ('closed'))
            ORDER BY pi_header.date_created, pi_header.part_number";
    }

    if ($mPartNumber != "")
    {
        $mDataQuery = "SELECT
		        pi_header.id AS id,
		        pi_header.part_number AS part_number
            FROM pi_header
                LEFT JOIN document_status ON document_status_id = document_status.id
            WHERE 
                part_number = '{$mPartNumber}'
                AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
            ORDER by pi_header.date_created";
    }

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

    $mPiHdrId = "";
    $mPiPartNumber = "";
    $i = 0;
    $l = 0;
    if (mysqli_num_rows($mData) > 0)
    {
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mPiHdrId = ($mRow['id']);
            $mPiPartNumber = ($mRow['part_number']);
            if ($mDataStr != "")
            {
                $mDataStr .= ",";
            }
            $i++;
            $l++;
            // Format PI Ref.
            $mDocumentReferenceCode = "000000" . round($mPiHdrId);
            $mDocumentReferenceCode = "PI" . substr($mDocumentReferenceCode, -6);

            // "SELECT" Screen Definition
// Create PI Part Summary Screen - {"label":"1","name":"PIPARTLOC?DATA:partNumber:PARTA?5","input":"PARTA","content":"PARTA","screenoption":"PHHHH"}
// label = option (1-8), name = Settings for CHGTRAN function (Transaction to load?Values to pre-load to screen?Transaction Line to start processing from),input = Menu Value, content = Menu Display, screenoption = Protected/Hidden

            if ($i < 8)
            {
                $mDataStr .= '{"label":"' . $l . '","name":"' . $mTransactionType . '?DATA:partNumber:' . $mPiPartNumber . ',allocationId:' . $mPiHdrId . '?5","input":"' . $mPiPartNumber . '","content":"' . $mDocumentReferenceCode . '","screenoption":"PHHH"}';
            }
            elseif ($i < 16)
            {
                $mDataStr .= '{"label":"' . $l . '","name":"' . $mTransactionType . '?DATA:partNumber:' . $mPiPartNumber . ',allocationId:' . $mPiHdrId . '?5","input":"' . $mPiPartNumber . '","content":"' . $mDocumentReferenceCode . '","screenoption":"HPHH"}';
            }
            elseif ($i < 24)
            {
                $mDataStr .= '{"label":"' . $l . '","name":"' . $mTransactionType . '?DATA:partNumber:' . $mPiPartNumber . ',allocationId:' . $mPiHdrId . '?5","input":"' . $mPiPartNumber . '","content":"' . $mDocumentReferenceCode . '","screenoption":"HHPH"}';
            }
            else
            {
                $mDataStr .= '{"label":"' . $l . '","name":"' . $mTransactionType . '?DATA:partNumber:' . $mPiPartNumber . ',allocationId:' . $mPiHdrId . '?5","input":"' . $mPiPartNumber . '","content":"' . $mDocumentReferenceCode . '","screenoption":"HHHP"}';
            }
            if (($i == 8) || ($i == 16) || ($i == 24))
            {
                $l = 0;
            }
        }
        // SELECT Header
        $mDataStr = ('[{"label":"Opt","name":"partNumber","input":"Part","content":"PI Ref.","screenoption":"PPPP"},' . $mDataStr . ']');
        return "SELECT:" . $mDataStr;
    }
    else
    {
        return "FAIL-No P.I Counts for " . $mPartNumber;
    }
}


/**
 * Add / Update PI Tables
 *
 * @param string $mHost The host of the database.
 * @param string $mUser The username of the database.
 * @param string $mPwd The password of the database.
 * @param string $mDbName The name of the database.
 * @param string $mPartNumber The part number to update.
 * @param string $mLocation The location code to update.
 * @param string $mCurrentUser The current user.
 * @param string $mConnect The database connection (optional).
 *
 * @return string Returns "FAIL-" followed by the error message if the update fails, and the number of updated rows if successful.
 */
function piPartUpdate($mHost, $mUser, $mPwd, $mDbName, $mPartNumber, $mLocation, $mCurrentUser = "", $mConnect = "")
{
    $mDataStr = "";
    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
    exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());

    $mDataQuery = "SELECT id
        FROM document_status
        WHERE LOWER(document_status_code) = 'open'
        LIMIT 1";

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

    $mRow = mysqli_fetch_assoc($mData);
    $mDocId = ($mRow['id']);

    $mDataQuery = "SELECT
	        inventory_master.id AS inv_id,
	        part.part_number AS part_number,
	        inventory_master.serial_reference AS serial_reference,
	        inventory_master.inventory_qty AS inventory_qty,
	        inventory_master.ran_or_order AS ran_order,
	        pi_header.id AS pi_hdr_id,
	        location.location_code AS location_code,
	        location.location_hash AS hash_code
		FROM location 
            JOIN location_type ON location.location_type_id=location_type.id
            JOIN inventory_master ON inventory_master.current_location_id=location.id
            JOIN part ON inventory_master.part_id=part.id
            JOIN pi_header ON part.part_number=pi_header.part_number AND (pi_header.document_status_id IS null OR pi_header.document_status_id={$mDocId})
            LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id
		WHERE 
		    part.part_number = '{$mPartNumber}'
		    AND location.location_code = '{$mLocation}'
		    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'))
		ORDER BY location_code";

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

    $i = 0;
    if (mysqli_num_rows($mData) > 0)
    {
        while ($mRow = mysqli_fetch_assoc($mData))
        {
            $mPiHdrId = ($mRow['pi_hdr_id']);
            if ($mPiHdrId != "")
            {
                $mPiBdyQuery = "SELECT
				        pi_body.id AS id
                    FROM pi_body
                    WHERE 
                        pi_header_id = {$mRow['pi_hdr_id']}
                        AND part_number='{$mPartNumber}'
                        AND location_code='{$mLocation}'
                    LIMIT 1";

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

                if (mysqli_num_rows($mPiBdyData) == 0)
                {
                    $mPiBdyQuery = "INSERT INTO pi_body
                            (id, part_number, location_code, expected_qty, actual_qty, difference, pi_header_id, date_created, created_by, last_updated_date, last_updated_by, document_status_id, serial_not_exist, incorrect_location, picking_left)
                        VALUES
                            (DEFAULT, '{$mRow['part_number']}', '{$mRow['location_code']}', 0, 0, 0, {$mRow['pi_hdr_id']}, now(), '{$mCurrentUser}', now(),'{$mCurrentUser}', {$mDocId}, 0, 0, 0)";

                    if (!mysqli_query($mConnection, $mPiBdyQuery))
                    {
                        return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiBdyQuery);
                    }
                    $mPiBdyId = mysqli_insert_id($mConnection);
                }
                else
                {
                    $mPiBdyRow = mysqli_fetch_assoc($mPiBdyData);
                    $mPiBdyId = ($mPiBdyRow['id']);
                }
                $mPiDtlQuery = "SELECT
                        id 
                    FROM pi_detail
                    WHERE 
                        pi_body_id = {$mPiBdyId}
                        AND serial_reference = '{$mRow['serial_reference']}'
                    LIMIT 1";

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

                if (mysqli_num_rows($mPiDtlData) == 0)
                {
                    $mPiDtlQuery = "INSERT INTO pi_detail 
                            (id, serial_reference, expected_qty, actual_qty, difference, pi_body_id, date_created, created_by, last_updated_date, last_updated_by)
                        VALUES
                            (DEFAULT, '{$mRow['serial_reference']}', {$mRow['inventory_qty']}, 0, 0 - {$mRow['inventory_qty']}, {$mPiBdyId},now(), '{$mCurrentUser}', now(), '{$mCurrentUser}')";

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

                    // Flag Inventory with 'PI' Location
                    $mPiDtlQuery = "SELECT
                            id,
                            location_code
                        FROM location
                        WHERE LOWER(location_code='stkchk')
                        LIMIT 1";

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

                    $mPiDtlRow = mysqli_fetch_assoc($mPiDtlData);
                    $mLocId = ($mPiDtlRow['id']);
                    $mLocStk = ($mPiDtlRow['location_code']);

                    $mPiDtlQuery = "SELECT
                            id
                        FROM inventory_status
                        WHERE LOWER(inventory_status_code) IN('pkd','plt')
                        LIMIT 1";

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

                    $mPiDtlRow = mysqli_fetch_assoc($mPiDtlData);
                    $mInvPkd = ($mPiDtlRow['id']);

                    $mDocumentReferenceCode = formatDocumentReferenceCode($mRow['pi_hdr_id']);

                    $mTranQuery = "INSERT INTO transaction_history
                            (id, short_code, part_number, serial_reference, ran_or_order, txn_qty, from_location_code, to_location_code, associated_document_reference, date_created, last_updated, last_updated_by, transaction_reference_code)
                        VALUES
                            (DEFAULT, 'PI', '{$mRow['part_number']}', '{$mRow['serial_reference']}', '{$mRow['ran_order']}', {$mRow['inventory_qty']}, '{$mRow['location_code']}', '{$mLocStk}', '{$mDocumentReferenceCode}', now(), now(), '{$mCurrentUser}', 'PI')";

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

                    $mDataQuery = "UPDATE inventory_master
                        SET 
                            current_location_id = {$mLocId},
                            location_id = {$mLocId},
                            location_code = '{$mLocStk}',
                            last_updated = now(),
                            last_updated_by = '{$mCurrentUser}'
                        WHERE inventory_master.id = {$mRow['inv_id']}
                        LIMIT 1";

                    if (!mysqli_query($mConnection, $mDataQuery))
                    {
                        return ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
                    }
                    $i++;
                }
            }
            else
            {
                return ("FAIL-System Error on PI Header");
            }
        }
        //		if ($mPiBdyId != "")
//		{
        $mDataQuery = "SELECT
                    sum(expected_qty) AS expected_qty,
                    sum(actual_qty) AS actual_qty 
                FROM pi_detail
                WHERE pi_body_id = {$mPiBdyId}
                LIMIT 1";

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

        if (mysqli_num_rows($mData) > 0)
        {
            $mRow = mysqli_fetch_assoc($mData);
            $mPiBdyQuery = "UPDATE pi_body
                SET 
                    expected_qty = {$mRow['expected_qty']},
                    actual_qty = {$mRow['actual_qty']},
                    difference = (actual_qty-expected_qty),
                    last_updated_date = now(),
                    last_updated_by = '{$mCurrentUser}'
                WHERE id={$mPiBdyId}
                LIMIT 1";

            if (!mysqli_query($mConnection, $mPiBdyQuery))
            {
                return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiBdyQuery);
            }
        }
        //		}
    }
    if ($mPiHdrId != "")
    {
        $mDataQuery = "SELECT
                sum(expected_qty) AS expected_qty,
                sum(actual_qty) AS actual_qty
            FROM pi_body
            WHERE pi_header_id = {$mPiHdrId}";

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

        if (mysqli_num_rows($mData) > 0)
        {
            $mRow = mysqli_fetch_assoc($mData);
            $mPiBdyQuery = "UPDATE pi_header
                SET
                    expected_case_qty = {$mRow['expected_qty']},
                    actual_case_qty = {$mRow['actual_qty']},
                    difference = (actual_case_qty-expected_case_qty),
                    last_updated_date = now(),
                    last_updated_by = '{$mCurrentUser}'
                WHERE id = {$mPiHdrId}
                LIMIT 1";

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


/**
 * Processes and validates a serial number for a specified part in inventory.
 *
 * @param string $mHost The hostname for the database connection.
 * @param string $mUser The username for the database connection.
 * @param string $mPwd The password for the database connection.
 * @param string $mDbName The database name to connect to.
 * @param string $mFilter The filter criteria for the operation, formatted as key-value pairs.
 * @param string $mCurrentUser The current user's identifier, optional.
 * @param string $mConnect The connection parameters, optional.
 * @return mixed Returns a failure message if validation fails, or a success message otherwise.
 */
function piPartSerial($mHost, $mUser, $mPwd, $mDbName, $mFilter, $mCurrentUser = "", $mConnect = "")
{
    $mErrMsg = "";
    // Explode Input Parm
// Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    $i = 0;
    $mPiHdrId = "";
    $mSerialRef = "";
    $mPartNumber = "";
    $mScanQty = "";
    $mQty = "";
    $mCloseLocationHash = "";
    $mPiLocationHash = "";

    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);

    // Create Associative Array by Exploding Array Elements on :
    foreach ($mArray1 as $k1 => $v1)
    {
        list($k2, $v2) = explode(":", $mArray1[$k1]);
        if ($k2 == "piid")
        {
            $mPiHdrId = $v2;
        }
        elseif ($k2 == "locationhashcode")
        {
            $mLocationCode = strtoupper($v2);
        }
        elseif ($k2 == "qty")
        {
            $mQty = $v2;
        }
        elseif ($k2 == "serialreference")
        {
            $mSerialRef = trim(strtoupper($v2));
        }
        elseif ($k2 == "vldlocationhashcode")
        {
            $mVldLocationHash = strtoupper($v2);
        }
        elseif ($k2 == "partnumber")
        {
            $mPartNumber = strtoupper($v2);
        }
        elseif ($k2 == "scanqty")
        {
            if (is_numeric($v2))
            {
                $mScanQty = $v2;
            }
            else
            {
                return "FAIL-This must be a number only";
            }
        }
        elseif ($k2 == "pilocationhashcode")
        {
            $mPiLocationHash = strtoupper($v2);
        }
        elseif ($k2 == "pilocationcode")
        {
            $mPiLocationCode = strtoupper($v2);
        }
        elseif ($k2 == "currentUser")
        {
            $mCurrentUser = $v2;
        }
        elseif ($k2 == "transactionType")
        {
            $mTransactionTypeId = strtoupper($v2);
        }
        elseif ($k2 == "closehashcode")
        {
            $mCloseLocationHash = strtoupper($v2);
        }
    }

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

    // Validates Scanned Serial
    if ($mSerialRef != "")
    {
        if ($mPartNumber == "")
        {
            $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,
					location.location_hash AS hash_code,
					location.location_code AS location_code,
					inventory_status.inventory_status_code AS status_code,
					part.part_number AS part_number
				FROM inventory_master 
				    LEFT JOIN inventory_status ON inventory_master.inventory_status_id = inventory_status.id
				    JOIN location ON inventory_master.current_location_id = location.id
				    JOIN part ON inventory_master.part_id = part.id
				WHERE 
					inventory_master.serial_reference = ?
					AND inventory_qty > 0 
				LIMIT 2";
        }
        else
        {
            $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,
					location.location_hash AS hash_code,
					location.location_code AS location_code,
					inventory_status.inventory_status_code AS status_code,
					part.part_number AS part_number 
				FROM inventory_master
                    LEFT JOIN inventory_status ON inventory_master.inventory_status_id = inventory_status.id
                    JOIN location ON inventory_master.current_location_id = location.id
                    JOIN part ON inventory_master.part_id = part.id
				WHERE 
					inventory_master.serial_reference = ?
					AND part.part_number = ?
					AND inventory_qty > 0 
				LIMIT 2";
        }

        $stmt = $mConnection->prepare($mDataQuery);

        // Binds Parameters conditionally
        if ($mPartNumber == '')
        {
            $stmt->bind_param('s', $mSerialRef);
        }
        else
        {
            $stmt->bind_param('ss', $mSerialRef, $mPartNumber);
        }

        if (!$stmt->execute())
        {
            return ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
        }

        $mData = $stmt->get_result();
        $stmt->close();


        if ($mData->num_rows === 0) // Adds records for invalid serials
        {
            // Checks if the invalid serial was already added to pi_detail table
            // Gets pi body id for pi header id
            $piBodyId = getPiBodyId($mPiHdrId, $mConnection);

            if (checkInvalidSerialOnPiDetail($mSerialRef, $piBodyId, $mConnection))
            {
                return "FAIL-Invalid Serial Already Scanned";
            }

            // Sets error message for new invalid serials are scanned
            if ($mPartNumber === "")
            {
                $mErrMsg = "FAIL-Unknown Serial";
            }
            else
            {
                $mErrMsg = "FAIL-Unknown Serial or Part Number";
            }

            // Updates incorrect_serial count on pi_header
            $mPiQuery = "SELECT
					pi_header.id AS id,
					pi_header.actual_case_qty AS actual_case_qty,
					CASE
						WHEN pi_header.serial_not_exist IS NULL THEN 0
						ELSE pi_header.serial_not_exist
					END AS serial_not_exist
				FROM pi_header
				LEFT JOIN document_status ON document_status_id = document_status.id
				WHERE
					part_number = ?
					AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
				LIMIT 1
			";
            $stmt = $mConnection->prepare($mPiQuery);
            $stmt->bind_param('s', $mPartNumber);
            $stmt->execute();

            if (!$mPiData = $stmt->get_result())
            {
                return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery . "-" . mysqli_num_rows($mPiData));
            }

            $mSerialNotExist = 0;
            $mActualQty = 0;

            if ($mPiData->num_rows > 0)
            {
                $mPiRow = $mPiData->fetch_assoc();
                $mPiHdrId = ($mPiRow['id']);
                $mSerialNotExist = ($mPiRow['serial_not_exist']);
                $mActualQty = ($mPiRow['actual_case_qty']);
            }

            $mSerialNotExist++;
            // $mActualQty++;
            $mPiData->free();
            $stmt->close();

            // Updates incorrect_serial count on pi_header
            $mPiQuery = "UPDATE pi_header
                SET 
                    actual_case_qty = {$mActualQty},
                    serial_not_exist = {$mSerialNotExist},
                    difference = (actual_case_qty - expected_case_qty),
                    last_updated_date = now(),
                    last_updated_by = '{$mCurrentUser}'
                WHERE id = {$mPiHdrId}";

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

            // Updates incorrect_serial count on pi_body table
            $mPiQuery = "SELECT 
					pi_body.id AS id,
					pi_body.actual_qty AS actual_qty,
					CASE 
						WHEN pi_body.serial_not_exist IS NULL THEN 0
						ELSE pi_body.serial_not_exist 
					END AS serial_not_exist 
				FROM pi_body
				LEFT JOIN document_status ON document_status_id = document_status.id
				WHERE 
					pi_header_id = {$mPiHdrId}
					AND part_number = '{$mPartNumber}'
					AND (document_status_id IS null OR lower(document_status_code) NOT IN ('closed'))
				LIMIT 1
			";

            if (!$mPiData = mysqli_query($mConnection, $mPiQuery))
            {
                return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery . "-" . mysqli_num_rows($mPiData));
            }

            $mSerialNotExist = 0;
            $mActualQty = 0;
            $mPiBdyId = "";

            if (mysqli_num_rows($mPiData) > 0)
            {
                ($mPiRow = mysqli_fetch_assoc($mPiData));
                $mPiBdyId = ($mPiRow['id']);
                $mSerialNotExist = ($mPiRow['serial_not_exist']);
                $mActualQty = ($mPiRow['actual_qty']) ?? 0;
            }

            $mSerialNotExist++;
            // $mActualQty++;

            if (!empty($mPiBdyId))
            {

                $mPiQuery = "UPDATE pi_body";
                $mPiQuery .= " SET ";
                if (!empty($mActualQty))
                {
                    $mPiQuery .= "actual_qty = {$mActualQty}, ";
                }

                if (!empty($mSerialNotExist))
                {
                    $mPiQuery .= "serial_not_exist = {$mSerialNotExist}, ";
                }
                $mPiQuery .= "difference = (actual_qty - expected_qty), last_updated_date = now(), ";
                if (!empty($mCurrentUser))
                {
                    $mPiQuery .= "last_updated_by = '{$mCurrentUser}' ";
                }
                $mPiQuery .= "WHERE id = {$mPiBdyId}";

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

                // Adds record of invalid serial to pi_detail table
                $piDetailInsertQuery = "INSERT INTO pi_detail
						(serial_reference, actual_qty, expected_qty, difference, is_serial_invalid, pi_body_id, date_created, created_by, last_updated_date, last_updated_by)
					VALUES
						(?, 0, 0, 0, 1, ?, NOW(), ?, NOW(), ?);
				";
                $stmt = $mConnection->prepare($piDetailInsertQuery);
                $stmt->bind_param("siss", $mSerialRef, $mPiBdyId, $mCurrentUser, $mCurrentUser);

                if (!$stmt->execute())
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery);
                }
            }

            if (!empty($errMsg))
            {
                $mErrMsg = "OK  -piPartSerial";
            }

            return $mErrMsg;
        }
        else
        {
            // Check That Scanned serial is valid
            $mRow = $mData->fetch_assoc();
            $mInventoryId = $mRow['id'];

            // Check / Update Inventory on Missing Status
            if (strtolower(($mRow['status_code'])) == "missing")
            {
                $mPiQuery = "UPDATE inventory_master
					SET 
						inventory_status_id = (SELECT id FROM inventory_status WHERE lower(inventory_status_code) = 'ok')
					WHERE id = {$mRow['id']}
					LIMIT 1";

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

            // Check Inventory Picked / Location Status
            if ((((strtolower(($mRow['status_code'])) == "pkd") || (strtolower(($mRow['status_code'])) == "plt")) && (strtolower($mLocationCode) != "stkchk")) || ((strtolower(($mRow['location_code'])) != "stkchk") && ($mPiLocationCode != ($mRow['location_code']))))
            {
                // Update picked / left count
                $mPiQuery = "SELECT
						pi_header.id AS id,
						pi_header.actual_case_qty AS actual_case_qty,
						CASE 
							WHEN pi_header.incorrect_location IS NULL THEN 0
							ELSE pi_header.incorrect_location
						END AS incorrect_location,
						CASE
							WHEN pi_header.picking_left IS NULL THEN 0
							ELSE pi_header.picking_left
						END AS picking_left 
					FROM pi_header
					    LEFT JOIN document_status ON document_status_id = document_status.id
					WHERE 
						part_number = '{$mPartNumber}'
						AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
					LIMIT 1";

                if (!$mPiData = mysqli_query($mConnection, $mPiQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery . "-" . mysqli_num_rows($mPiData));
                }


                $mPickingLeft = 0;
                $mIncorrectLocation = 0;
                $mActualQty = 0;

                if (mysqli_num_rows($mPiData) > 0)
                {
                    ($mPiRow = mysqli_fetch_assoc($mPiData));
                    $mPiHdrId = ($mPiRow['id']);
                    $mPickingLeft = ($mPiRow['picking_left']);
                    $mIncorrectLocation = ($mPiRow['incorrect_location']);
                    $mActualQty = ($mPiRow['actual_case_qty']);
                }

                $mDocumentReferenceCode = formatDocumentReferenceCode($mPiHdrId);

                if ((strtolower(($mRow['status_code'])) == "pkd") || (strtolower(($mRow['status_code'])) == "plt"))
                {
                    $mPickingLeft++;
                }
                else
                {
                    $mIncorrectLocation++;
                }
                $mActualQty++;

                $mPiQuery = "UPDATE pi_header
					SET 
						actual_case_qty = {$mActualQty},
						incorrect_location = {$mIncorrectLocation},
						picking_left = {$mPickingLeft},
						difference = (actual_case_qty - expected_case_qty),
						last_updated_date = now(),
						last_updated_by = '{$mCurrentUser}'
					WHERE id = {$mPiHdrId}";

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

                // Update picked / left count
                $mPiQuery = "SELECT
						pi_body.id AS id,
						pi_body.actual_qty AS actual_qty,
						CASE
							WHEN pi_body.incorrect_location IS NULL THEN 0
							ELSE pi_body.incorrect_location
						END AS incorrect_location,
						CASE
							WHEN pi_body.picking_left IS NULL THEN 0
							ELSE pi_body.picking_left
						END AS picking_left
					FROM pi_body
					LEFT JOIN document_status ON pi_body.document_status_id = document_status.id
					WHERE 
						pi_header_id = {$mPiHdrId}
						AND part_number = '{$mPartNumber}'
					AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
					LIMIT 1";

                if (!$mPiData = mysqli_query($mConnection, $mPiQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery . "-" . mysqli_num_rows($mPiData));
                }

                $mPickingLeft = 0;
                $mIncorrectLocation = 0;
                $mActualQty = 0;
                $mPiBdyId = "";

                if (mysqli_num_rows($mPiData) > 0)
                {
                    ($mPiRow = mysqli_fetch_assoc($mPiData));
                    $mPiBdyId = ($mPiRow['id']);
                    $mPickingLeft = ($mPiRow['picking_left']);
                    $mIncorrectLocation = ($mPiRow['incorrect_location']);
                    $mActualQty = ($mPiRow['actual_qty']);
                }

                if ((strtolower(($mRow['status_code'])) == "pkd") || (strtolower(($mRow['status_code'])) == "plt"))
                {
                    $mPickingLeft++;
                }
                else
                {
                    $mIncorrectLocation++;
                }
                $mActualQty++;

                if ($mPiBdyId != "")
                {
                    $mPiQuery = "UPDATE pi_body
                        SET 
                            actual_qty = {$mActualQty},
                            incorrect_location = {$mIncorrectLocation},
                            picking_left = {$mPickingLeft},
                            last_updated_date = now(),
                            last_updated_by = '{$mCurrentUser}'
                        WHERE id = {$mPiBdyId}";

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

// Update Inventory master with 'PI' Location
                $mPiQuery = "SELECT
                        id,
                        location_code
                    FROM location 
                    WHERE LOWER(location_code='stkchk') 
                    LIMIT 1";

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

                $mPiRow = mysqli_fetch_assoc($mPiData);
                $mLocId = ($mPiRow['id']);
                $mLocStk = ($mPiRow['location_code']);

                $mPiQuery = "UPDATE inventory_master
                    SET
                        current_location_id = {$mLocId},
                        location_id = {$mLocId},
                        location_code = '{$mLocStk}',
                        last_updated = now(),
                        last_updated_by = '{$mCurrentUser}'
                    WHERE id = {$mInventoryId}
                    LIMIT 1";

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

                $mPiDtlQuery = "SELECT id
                    FROM pi_detail
                    WHERE
                        pi_body_id = {$mPiBdyId}
                        AND serial_reference='{$mSerialRef}'
                    LIMIT 1";

                if (!$mPiDtlData = mysqli_query($mConnection, $mPiDtlQuery))
                {
                    return ("FAIL-" . $mConnection->error . "-" . $mPiDtlQuery);
                }

                if (mysqli_num_rows($mPiDtlData) == 0)
                {
                    $mPiDtlQuery = "INSERT INTO pi_detail
                            (id, serial_reference, expected_qty, actual_qty, difference, pi_body_id, date_created, created_by, last_updated_date, last_updated_by)
                        VALUES
                            (DEFAULT, '{$mSerialRef}', {$mRow['inventory_qty']}, 0, (0 - {$mRow['inventory_qty']}), {$mPiBdyId}, now(), '{$mCurrentUser}', now(), '{$mCurrentUser}')";
                    if (!mysqli_query($mConnection, $mPiDtlQuery))
                    {
                        return ("FAIL-" . $mConnection->error . "-" . $mPiDtlQuery);
                    }
                }

                $mTranQuery = "INSERT INTO transaction_history
                        (id, short_code, part_number, serial_reference, ran_or_order, txn_qty, from_location_code, to_location_code, associated_document_reference, date_created, last_updated, last_updated_by, transaction_reference_code)
                    VALUES
                        (DEFAULT, 'PI', '{$mPartNumber}', '{$mSerialRef}', '{$mRow['ran_order']}', {$mRow['inventory_qty']}, '{$mRow['location_code']}', '{$mLocStk}', '{$mDocumentReferenceCode}', now(), now(), '{$mCurrentUser}', 'PI')";

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

                // Serial
                if ((strtolower(($mRow['status_code'])) != "pkd") || (strtolower(($mRow['status_code'])) != "plt"))
                {
                    $mErrMsg = "FAIL-Serial should be in location {$mRow['location_code']} - Rescan Serial";
                }
                else
                {
                    //					if(($mRow['plt_number']) != "")
//						$mErrMsg = "FAIL-Serial has been picked to Pallet ".($mRow['plt_number']." - Rescan Serial");
//					else
                    $mErrMsg = "FAIL-Serial has been picked - Rescan Serial";
                }
                return $mErrMsg;
            }


            if ($mErrMsg == "")
            {
                //  Prompt for Piece Qty
                if ($mScanQty == "")
                {
                    // $mRow = mysqli_fetch_assoc($mData);
                    $mErrMsg = "DATA:serialReference:" . $mSerialRef . ",partNumber:" . $mPartNumber . ":P:skip4";
                    return ($mErrMsg);
                }
            }

            // Check Scanned Qty.
            $mDataQuery = "SELECT
                    part.part_number AS part_number,
                    part.conversion_factor AS conversion_factor,
                    inventory_master.serial_reference AS serial_reference,
                    inventory_master.ran_or_order AS ran_order,
                    inventory_master.inventory_qty AS inventory_qty,
                    location.location_code AS location_code
                FROM inventory_master
                    JOIN part ON inventory_master.part_id = part.id
                    LEFT JOIN inventory_status ON inventory_master.inventory_status_id = inventory_status.id
                    JOIN location ON inventory_master.current_location_id = location.id
                WHERE inventory_master.id={$mInventoryId} LIMIT 1";

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

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

            while ($mRow = mysqli_fetch_assoc($mData))
            {
                $mConversionFactor = $mRow['conversion_factor'];
                if ($mConversionFactor > 1)
                {
                    $mTxnQty = $mScanQty * $mConversionFactor;
                }
                else
                {
                    $mTxnQty = $mScanQty;
                }

                // Check Scanned Qty.
                $mTrnQuery = "SELECT
                        pi_detail.id AS id,
                        pi_detail.actual_qty AS actual_qty,
                        pi_detail.expected_qty AS expected_qty,
                        inventory_master.inventory_qty AS inventory_qty
                    FROM inventory_master
                        JOIN pi_detail ON inventory_master.serial_reference = pi_detail.serial_reference
                    WHERE 
                        inventory_master.id = {$mInventoryId}
                        AND EXISTS(SELECT id FROM pi_body WHERE pi_detail.pi_body_id = pi_body.id AND pi_body.part_number='{$mPartNumber}' AND EXISTS(SELECT id FROM pi_header WHERE pi_body.pi_header_id=pi_header.id AND pi_header.id={$mPiHdrId}))
                    LIMIT 1";

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

                // return ("FAIL-".mysqli_error($mConnection)."-".$mTrnQuery."-".mysqli_num_rows($mTrndata));

                $mTrnRow = mysqli_fetch_assoc($mTrnData);

                if (mysqli_num_rows($mTrnData) > 0)
                {
                    $mPiQuery = "UPDATE pi_detail 
                        SET 
                            actual_qty = {$mTxnQty},
                            difference = actual_qty - expected_qty,
                            last_updated_date = now(),
                            last_updated_by = '{$mCurrentUser}'
                        WHERE 
                            serial_reference = '{$mSerialRef}'
                            AND EXISTS
                                    (
                                        SELECT id
                                        FROM pi_body
                                        WHERE
                                            pi_detail.pi_body_id = pi_body.id
                                            AND pi_body.part_number='{$mPartNumber}'
                                            AND EXISTS 
                                                (
                                                    SELECT id
                                                    FROM pi_header
                                                    WHERE
                                                        pi_body.pi_header_id = pi_header.id
                                                        AND pi_header.id={$mPiHdrId}
                                                )
                                    )
                        LIMIT 1";

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

                    if ($mTxnQty != $mTrnRow['expected_qty'] && $mTxnQty != $mTrnRow['actual_qty'])
                    {
                        return ("FAIL-quantity difference - re-enter to confirm");
                    }
                }
                else
                {
                    return ("FAIL-This serial should not be in this location");
                }
            }
            // Valid Serial Scanned
            $mPiQuery = "SELECT
					pi_header.id AS id,
					pi_header.actual_case_qty AS actual_case_qty,
					pi_header.actual_case_qty AS actual_case_qty,
					CASE
						WHEN pi_header.incorrect_location IS NULL THEN 0
						ELSE pi_header.incorrect_location
					END AS incorrect_location,
					CASE
						WHEN pi_header.picking_left IS NULL THEN 0
						ELSE pi_header.picking_left
					END AS picking_left
				FROM pi_header
				LEFT JOIN document_status ON pi_header.document_status_id = document_status.id
				WHERE
					part_number = '{$mPartNumber}'
					AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
				LIMIT 1";

            if (!$mPiData = mysqli_query($mConnection, $mPiQuery))
            {
                return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery . "-" . mysqli_num_rows($mPiData));
            }
            $mPiHdrId = "";

            if (mysqli_num_rows($mPiData) > 0)
            {
                ($mPiRow = mysqli_fetch_assoc($mPiData));
                $mPiHdrId = ($mPiRow['id']);
            }

            $mPiQuery = "SELECT
					pi_body.id AS id
				FROM pi_body
				LEFT JOIN document_status ON pi_body.document_status_id = document_status.id
				WHERE 
					pi_header_id = {$mPiHdrId}
					AND pi_body.part_number = '{$mPartNumber}'
					AND pi_body.location_code = '{$mPiLocationCode}'
					AND (document_status_id IS null OR lower(document_status_code) NOT IN ('closed'))
				LIMIT 1";

            if (!$mPiData = mysqli_query($mConnection, $mPiQuery))
            {
                return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery . "-" . mysqli_num_rows($mPiData));
            }

            $mPiBdyId = "";

            if (mysqli_num_rows($mPiData) > 0)
            {
                ($mPiRow = mysqli_fetch_assoc($mPiData));
                $mPiBdyId = ($mPiRow['id']);
            }

            $mPiQuery = "SELECT id FROM pi_detail WHERE pi_body_id = {$mPiBdyId} AND serial_reference = '{$mSerialRef}' LIMIT 1";

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

            if (mysqli_num_rows($mPiData) == 0)
            {
                $mPiQuery = "INSERT INTO pi_detail 
                        (id, serial_reference, expected_qty, actual_qty, difference, pi_body_id, date_created, created_by, last_updated_date, last_updated_by) 
                    VALUES
                        (DEFAULT, '{$mSerialRef}', 0, 0, 0, {$mPiBdyId}, now(), '{$mCurrentUser}', now(), '{$mCurrentUser}')";

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

                $mPiDtlId = mysqli_insert_id($mConnection);
            }
            else
            {
                ($mPiRow = mysqli_fetch_assoc($mPiData));
                $mPiDtlId = ($mPiRow['id']);
                try
                {
                    $mPiQuery = "UPDATE pi_detail
                        SET 
                            actual_qty = {$mTxnQty},
                            difference = actual_qty - expected_qty,
                            last_updated_date = now(),
                            last_updated_by = '{$mCurrentUser}'
                        WHERE 
                            pi_body_id = {$mPiBdyId}
                            AND serial_reference = '{$mSerialRef}'
                        LIMIT 1";

                    if (!mysqli_query($mConnection, $mPiQuery))
                    {
                        return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery);
                    }
                }
                catch (Exception $e)
                {
                }
            }

            //			if ($mPiBdyId != "")
//			{
            $mPiQuery = "SELECT
                    sum(CASE WHEN expected_qty>0 then 1 ELSE 0 END) AS expected_qty,
                    sum(CASE WHEN actual_qty>0 THEN 1 ELSE 0 END) AS actual_qty
                FROM pi_detail 
                WHERE pi_body_id = {$mPiBdyId} LIMIT 1";

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

            if (mysqli_num_rows($mPiData) > 0)
            {
                $mPiRow = mysqli_fetch_assoc($mPiData);

                $mPiBdyQuery = "UPDATE pi_body
                    SET 
                        expected_qty = {$mPiRow['expected_qty']},
                        actual_qty = {$mPiRow['actual_qty']},
                        difference = (actual_qty - expected_qty),
                        last_updated_date = now(),
                        last_updated_by = '{$mCurrentUser}'
                    WHERE id = {$mPiBdyId}
                LIMIT 1";

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

            // Query updates quantity on inventory_mastes table
            // $mDataQuery = "UPDATE inventory_master
            //     SET
            //         current_location_id = (SELECT id FROM location WHERE location.location_code = '{$mPiLocationCode}'),
            //         location_id = current_location_id,
            //         location_code = '{$mPiLocationCode}',
            //         inventory_qty = {$mTxnQty},
            //         allocated_qty = 0,
            //         available_qty = inventory_qty,
            //         last_updated = now(),
            //         last_updated_by = '{$mCurrentUser}'
            //     WHERE
            //         inventory_master.part_number = '{$mPartNumber}'
            //         AND inventory_master.serial_reference='{$mSerialRef}'
            //         AND inventory_qty > 0
            //     LIMIT 1";

            $mDataQuery = "UPDATE inventory_master
                SET 
                    current_location_id = (SELECT id FROM location WHERE location.location_code = '{$mPiLocationCode}'),
                    location_id = current_location_id,
                    location_code = '{$mPiLocationCode}',
                    allocated_qty = 0,
                    available_qty = inventory_qty,
                    last_updated = now(),
                    last_updated_by = '{$mCurrentUser}'
                WHERE 
                    inventory_master.part_number = '{$mPartNumber}'
                    AND inventory_master.serial_reference='{$mSerialRef}'
                    AND inventory_qty > 0
                LIMIT 1";

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

            $mDocumentReferenceCode = formatDocumentReferenceCode($mPiHdrId);

            $mTranQuery = "INSERT INTO transaction_history
                    (id,short_code, part_number, serial_reference, ran_or_order, txn_qty, from_location_code, to_location_code, associated_document_reference, date_created, last_updated, last_updated_by, transaction_reference_code)
                VALUES
                    (DEFAULT, 'PI', '{$mPartNumber}', '{$mSerialRef}', '{$mRow['ran_order']}', {$mTxnQty}, 'STKCHK', '{$mPiLocationCode}', '{$mDocumentReferenceCode}', now(), now(), '{$mCurrentUser}', 'PI')";

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

        if ($mPiHdrId != "")
        {
            $mPiQuery = "SELECT
                    sum(expected_qty) AS expected_qty,
                    sum(actual_qty) AS actual_qty
                FROM pi_body
                WHERE pi_header_id = {$mPiHdrId}";

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

            if (mysqli_num_rows($mPiData) > 0)
            {
                $mPiRow = mysqli_fetch_assoc($mPiData);
                $mPiQuery = "UPDATE pi_header 
                    SET 
                        expected_case_qty = {$mPiRow['expected_qty']},
                        actual_case_qty = {$mPiRow['actual_qty']},
                        difference = (actual_case_qty - expected_case_qty),
                        last_updated_date = now(),
                        last_updated_by = '{$mCurrentUser}'
                    WHERE id = {$mPiHdrId}
                    LIMIT 1";

                if (!mysqli_query($mConnection, $mPiQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery);
                }
            }
        }
    }
    // mysqli_close($mConection);
    return ("OK  -");
}


/**
 * Closes PI Part Body
 *
 * @param string $mHost The database host.
 * @param string $mUser The database username.
 * @param string $mPwd The database password.
 * @param string $mDbName The database name.
 * @param string $mFilter The filter to apply. Should be in the format "key1:value1,key2:value2".
 * @param string $mConnect Optional. The database connection string. Defaults to empty string.
 *
 * @return string Returns the result of the operation.
 *      If successful, returns "OKMS-Check Next Location,PIPARTLOC?DATA:allocationId:{mPiHdrId},partNumber:{mPartNumber},locationHashCode:{mLocationCode}::skip2".
 *      If no more locations for the part are found, returns the result of the closePiHeader function. Otherwise, returns an error message in the format "FAIL-{error message}-{query}".
 */
function piPartLocClose($mHost, $mUser, $mPwd, $mDbName, $mFilter, $mCurrentUser = "",  $mConnect = "")
{
    $mLocationCode = "";
    $mErrMsg = "";
    // Explode Input Parm
// Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    $i = 0;
    $mPiHdrId = "";
    $mSerialRef = "";
    $mPartNumber = "";
    $mQty = "";
    $mCloseLocationHash = "";
    $mPiLocationHash = "";

    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);

    // Create Associative Array by Exploding Array Elements on :
    foreach ($mArray1 as $k1 => $v1)
    {
        list($k2, $v2) = explode(":", $mArray1[$k1]);
        if (strtolower($k2) == "allocationid")
        {
            $mPiHdrId = $v2;
        }
        elseif ($k2 == "qty")
        {
            $mQty = $v2;
        }
        elseif (strtolower($k2) == "serialreference")
        {
            $mSerialReference = strtoupper($v2);
        }
        elseif (strtolower($k2) == "locationhashcode")
        {
            $mLocationHash = strtoupper($v2);
        }
        elseif (strtolower($k2) == "vldlocationhashcode")
        {
            $mVldLocationHash = strtoupper($v2);
        }
        elseif (strtolower($k2) == "partnumber")
        {
            $mPartNumber = strtoupper($v2);
        }
        elseif (strtolower($k2) == "pilocationhashcode")
        {
            $mPiLocationHash = strtoupper($v2);
        }
        elseif (strtolower($k2) == "pilocationcode")
        {
            $mPiLocationCode = strtoupper($v2);
        }
        elseif ($k2 == "currentUser")
        {
            $mCurrentUser = $v2;
        }
        elseif ($k2 == "transactionType")
        {
            $mTransactionTypeId = strtoupper($v2);
        }
        elseif (strtolower($k2) == "closehashcode")
        {
            $mCloseLocationHash = strtoupper($v2);
        }
    }

    // mysql connection
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or exit("FAIL-Connect failed: " . mysqli_connect_errno() . " : " . mysqli_connect_error());
	
// Gets document status id
	$mDocId = getDocumentStatusId('closed', $mConnection);
	
// Check If Inventory exists
	$mDataQuery = "SELECT id FROM inventory_master WHERE part_number = '$mPartNumber' LIMIT 1";
	try
	{
		$mData = mysqli_query($mConnection, $mDataQuery);
	}
	catch (Exception $e)
	{
		return ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
	}
	if (mysqli_num_rows($mData) == 0)
	{
		$mDataQuery = "UPDATE pi_header
				SET
					document_status_id = {$mDocId},
					actual_case_qty = 0,
					expected_case_qty = 0,
					difference = 0,
					last_updated_date = now(),
					last_updated_by = '{$mCurrentUser}'
				WHERE id = {$mPiHdrId}";
		try
		{
			$mData = mysqli_query($mConnection, $mDataQuery);
		}
		catch (Exception $e)
		{
			return ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
		}
			$mErrMsg = "FAIL-Empty PI Count Closed";
			return $mErrMsg;
	}
    // Close PI Header
    if ($mLocationHash != "")
    {
        $mLocationCode = "";
        $mDataQuery = "SELECT location.location_code FROM location
			WHERE location.location_hash='{$mLocationHash}' 
			OR location.location_code='{$mLocationHash}'
			LIMIT 1
		";

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

        if (mysqli_num_rows($mData) > 0)
        {
            list($mLocationCode) = mysqli_fetch_row($mData);
        }
        else
        {
            return "FAIL- Invalid Location";
        }


        // Gets document status id
        $mDocId = getDocumentStatusId('complete', $mConnection);

        if ($mDocId === "")
        {
            $mDocId = 2;
        }

        // Test for Valid Open Body
        $mDataQuery = "SELECT pi_body.id
			FROM pi_body 
			WHERE 
				pi_header_id = {$mPiHdrId}
				AND (
						location_code = '{$mLocationCode}'
                        OR EXISTS
                            (
                                SELECT id 
                                FROM location 
                                WHERE 
                                    pi_body.location_code = location.location_code
                                    AND location.location_code = '{$mLocationCode}' 
                            )
					)
			LIMIT 1
		";

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

        if (mysqli_num_rows($mData) == 0)
        {
            return ("FAIL-there is no active P.I Count for this location");
        }

        $mDataQuery = "UPDATE pi_body
			SET
				document_status_id = {$mDocId},
				last_updated_date = now(),
				last_updated_by = '{$mCurrentUser}'
			WHERE
				pi_header_id = {$mPiHdrId}
				AND part_number = '{$mPartNumber}'
				AND ( 
				        location_code = '{$mLocationCode}' 
                        OR EXISTS
                            (
								SELECT id
								FROM location
								WHERE
									pi_body.location_code = location.location_code
									AND location.location_code = '{$mLocationCode}'
							)
					)
			LIMIT 1
		";

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

        // Test for existing Active Pi Body Records
        $mDataQuery = "SELECT
				pi_body.id
			FROM pi_body
			WHERE 
				pi_header_id = {$mPiHdrId}
				AND document_status_id <> $mDocId
			LIMIT 1";

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

        // If no pi body test for Inventory
        if (mysqli_num_rows($mData) == 0)
        {
            $mDataQuery = "SELECT location.location_code AS location_code
				FROM location 
				JOIN location_type ON location.location_type_id=location_type.id
				JOIN inventory_master ON inventory_master.current_location_id=location.id
				JOIN part ON inventory_master.part_id=part.id
				LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id
				WHERE 
					part.part_number = '{$mPartNumber}'
					AND lower(location_type.location_area) = 'storage'
					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'))
					AND NOT EXISTS (SELECT id FROM pi_body WHERE pi_body.pi_header_id = {$mPiHdrId} AND pi_body.location_code = location.location_code)
				LIMIT 1";

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

        // exit('FAIL-' . mysqli_num_rows($mData));


        if (mysqli_num_rows($mData) == 0) // No more inventory to be checked and no differences found
        {
            $mErrMsg = closePiHeader($mPiHdrId, $mDocId, $mPartNumber, $mCurrentUser, $mConnection);
        }
        else // Serials available on Inventory to be checked
        {
            $currentFromLocation = $mLocationCode;

            // Tries to Find Next PI Location
            $mLocationCode = piNextLocation($mHost, $mUser, $mPwd, $mDbName, $mPartNumber, $mCurrentUser, $mLocationHash, $mConnect);

            if ($mLocationCode == "FAIL-No more locations for this Part Number")
            {
                // $mErrMsg = $mLocationCode;

                // Closes the document if no more locations for the part are found.
                $mErrMsg = closePiHeader($mPiHdrId, $mDocId, $mPartNumber, $mCurrentUser, $mConnection);

            }
            if ($mLocationCode === $currentFromLocation) // Posts PI document with differences
            {
                $mErrMsg = closePiHeader($mPiHdrId, $mDocId, $mPartNumber, $mCurrentUser, $mConnection);
            }
            else
            {
                // exit('FAIL-' . __FUNCTION__ . ' ' . __LINE__);
                // Return Screen Prompt for Next PI Location
                // $mErrMsg = "DATA:allocationId:" . $mPiHdrId . ",partNumber:" . $mPartNumber . ",locationHashCode:" . $mLocationCode;
                $mErrMsg = 'OKMS-Check Next Location,PIPARTLOC?DATA:allocationId:' . $mPiHdrId . ',partNumber:' . $mPartNumber . ',locationHashCode:' . $mLocationCode . '::skip2';

            }
        }
        // mysqli_close($mConnection);
    }
    else
    {
        $mErrMsg = "FAIL- No Location";
    }
    return ($mErrMsg);
}


/**
 * PI Location Box Count.
 *
 * @param string $mHost The hostname of the database server.
 * @param string $mUser The username used to connect to the database.
 * @param string $mPwd The password used to connect to the database.
 * @param string $mDbName The name of the database.
 * @param string $mCurrentUser The current user.
 * @param string $mFilter The filter string in a specific format.
 * @param string $mConnect The connection string (optional, default value is empty).
 *
 * @return string The result of the method call.
 */
function piLocCount($mHost, $mUser, $mPwd, $mDbName, $mFilter, $mCurrentUser = "", $mConnect = "")
{
    $mLocationCode = "";
    $mErrMsg = "";

// Explode Input Parm
// Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);
    $i = 0;
    $mPiHdrId = "";
    $mSerialRef = "";
    $mPartNumber = "";
    $mScanQty = "";
    $mQty = "";
    $mCloseLocationHash = "";
    $mPiLocationHash = "";
    // Explode Input String Into Array - Separated by ,
    $mArray1 = explode(",", $mFilter);

    // Create Associative Array by Exploding Array Elements on :
    foreach ($mArray1 as $k1 => $v1)
    {
        list($k2, $v2) = explode(":", $mArray1[$k1]);
        if ($k2 == "locationhashcode")
        {
            $mLocationCode = strtoupper($v2);
        }
        elseif ($k2 == "qty")
        {
            $mQty = $v2;
        }
        elseif ($k2 == "serialreference")
        {
            $mSerialReference = strtoupper($v2);
        }
        elseif ($k2 == "vldlocationhashcode")
        {
            $mVldLocationHash = strtoupper($v2);
        }
        elseif ($k2 == "partnumber")
        {
            $mPartNumber = strtoupper($v2);
        }
        elseif ($k2 == "scanqty")
        {
            $mScanQty = $v2;
        }
        elseif ($k2 == "pilocationhashcode")
        {
            $mPiLocationHash = strtoupper($v2);
        }
        elseif ($k2 == "pilocationcode")
        {
            $mPiLocationCode = strtoupper($v2);
        }
        elseif ($k2 == "currentUser")
        {
            $mCurrentUser = $v2;
        }
        elseif ($k2 == "transactionType")
        {
            $mTransactionTypeId = strtoupper($v2);
        }
        elseif ($k2 == "closehashcode")
        {
            $mCloseLocationHash = strtoupper($v2);
        }
    }

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

    // Close PI Header
    if ($mCloseLocationHash != "")
    {
        // Reads Values from DB for document status
        $mDataQuery = "SELECT
				MAX(CASE WHEN document_status_code = 'COMPLETE' THEN id END) AS doc_id_without_diff,
				MAX(CASE WHEN document_status_code = 'CLOSED' THEN id END) AS doc_id_with_diff
			FROM
			document_status;
		";

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

        $mRow = mysqli_fetch_assoc($mData);

        $docIdWithoutDiff = $mRow["doc_id_without_diff"];
        $docIdWithDiff = $mRow["doc_id_with_diff"];

        $mDataQuery = "SELECT
				pi_header.id AS id,
				pi_header.actual_case_qty AS actual_case_qty
			FROM pi_header
                LEFT JOIN location ON pi_header.location_code = location.location_code
                LEFT JOIN document_status ON pi_header.document_status_id = document_status.id
			WHERE
				location.location_hash = '{$mCloseLocationHash}'
				AND (document_status_id IS NULL OR lower(document_status.document_status_code) NOT IN ('closed'))
			LIMIT 1
		";

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

        $mPiHdrId = 0;

        if ($mData->num_rows > 0)
        {
            ['id' => $mPiHdrId] = $mData->fetch_assoc();
        }

        if ($mPiHdrId != "")
        {
            $mDataQuery = "UPDATE pi_header 
					SET 
						document_status_id =
							CASE
								WHEN actual_case_qty = expected_case_qty THEN {$docIdWithoutDiff}
								WHEN actual_case_qty < expected_case_qty THEN {$docIdWithDiff}
							END,
						difference = (actual_case_qty-expected_case_qty),
						last_updated_date = now(),
						last_updated_by = '{$mCurrentUser}'
					WHERE id = {$mPiHdrId}";

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

            $mDataQuery = "UPDATE pi_body
					SET
						document_status_id =
						    CASE
                                WHEN actual_qty = expected_qty THEN {$docIdWithoutDiff}
                                WHEN actual_qty < expected_qty THEN {$docIdWithDiff}
						    END,
						difference = (actual_qty - expected_qty),
						last_updated_date = now(),
						last_updated_by = '{$mCurrentUser}'
					WHERE pi_header_id = {$mPiHdrId}";

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

            // Calculate Count Totals
            $mPiBdyId = "";
            $mDataQuery = "SELECT pi_body.id AS id FROM pi_body WHERE pi_header_id = {$mPiHdrId}";

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

            while ($mRow = mysqli_fetch_assoc($mData))
            {
                $mPiBdyId = ($mRow['id']);

                $mPiQuery = "SELECT
				        sum(CASE WHEN expected_qty > 0 then 1 ELSE 0 END) AS expected_qty,
				        sum(CASE WHEN actual_qty > 0 THEN 1 ELSE 0 END) AS actual_qty
                    FROM pi_detail
                    WHERE pi_body_id = {$mPiBdyId}
                    LIMIT 1";

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

                if (mysqli_num_rows($mPiData) > 0)
                {
                    $mPiRow = mysqli_fetch_assoc($mPiData);
                    $mPiBdyQuery = "UPDATE pi_body 
                            SET 
                                expected_qty = {$mPiRow['expected_qty']},
                                actual_qty = {$mPiRow['actual_qty']},
                                difference = (actual_qty-expected_qty),
                                last_updated_date = now(),
                                last_updated_by = '{$mCurrentUser}'
                            WHERE id = {$mPiBdyId}
                            LIMIT 1";
                    if (!mysqli_query($mConnection, $mPiBdyQuery))
                    {
                        return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiBdyQuery);
                    }
                }
            }

            $mPiQuery = "SELECT
			        sum(expected_qty) AS expected_qty,
			        sum(actual_qty) AS actual_qty
                FROM pi_body
                WHERE pi_header_id = {$mPiHdrId}";

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

            //			return "FAIL-".mysqli_error($mConnection)."-".$mPiQuery."-".mysqli_num_rows($mPiData);
            if (mysqli_num_rows($mPiData) > 0)
            {
                $mPiRow = mysqli_fetch_assoc($mPiData);
                $mPiHdrQuery = "UPDATE pi_header
				    SET 
				        expected_case_qty = {$mPiRow['expected_qty']},
				        actual_case_qty = {$mPiRow['actual_qty']},
				        difference = (actual_case_qty - expected_case_qty),
				        last_updated_date = now(),
				        last_updated_by = '{$mCurrentUser}'
				    WHERE id={$mPiHdrId}
				    LIMIT 1";

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

            // Retrieves part number value from pi_body table is $mPartNumber is empty
            if (!$mPartNumber)
            {
                $query = "SELECT part_number
                    FROM pi_body
                    WHERE pi_header_id = ?
                    LIMIT 1";
                $stmt = $mConnection->prepare($query);
                $stmt->bind_param('i', $mPiHdrId);
                $stmt->execute();
                $result = $stmt->get_result();
                ['part_number' => $mPartNumber] = $result->fetch_assoc();
                $result->free();
                $stmt->close();
            }

            // Updates PI Count Variance
            try
            {
                if (isPartNumberOnE2partTable($mPartNumber, $mConnection))
                {
                    // Gets item_cost value from e2part table for provided part number
                    $itemCost = getItemCost($mPartNumber, $mConnection);

                    // Gets difference value from pi_body table for provided pi_header_id
                    $differenceSum = getDifferenceValue($mPiHdrId, $mConnection);

                    // Sets variance value for part number found on e2part table
                    $varienceValue = $itemCost * $differenceSum;
                    $varienceValue = round($varienceValue, 2);

                    // Updates varience_value on pi_header table
                    setVarienceValue($mPiHdrId, $varienceValue, $mCurrentUser, $mConnection);

                    // Updates document_status_id on pi_header and pi_body if document difference is 0
                    if (getVarienceValue($mPiHdrId, $mConnection) === 0)
                    {
                        // Gets document status id
                        $mDocId = getDocumentStatusId('closed', $mConnection);
                        if ($mDocId === '')
                        {
                            $mDocId = 3;
                        }

                        // Updates document_status_id on pi_body
                        $piBodyQuery = 'UPDATE pi_body
							SET
								document_status_id = ?,
								last_updated_date = NOW(),
								last_updated_by = ?
							WHERE
								pi_header_id = ?
						';

                        $stmt = $mConnection->prepare($piBodyQuery);
                        $stmt->bind_param('isi', $mDocId, $mCurrentUser, $mPiHdrId);
                        if (!$stmt->execute())
                        {
                            throw new Exception ($stmt->error . ' - ' . $piBodyQuery);
                        }

                        // Updates document_statut_id on pi_header
                        $piHeaderQuery = 'UPDATE pi_header
							SET
								document_status_id = ?,
								last_updated_date = NOW(),
								last_updated_by = ?
							WHERE
								id = ?
						';
                        $stmt = $mConnection->prepare($piHeaderQuery);
                        $stmt->bind_param('isi', $mDocId, $mCurrentUser, $mPiHdrId);
                        if (!$stmt->execute())
                        {
                            throw new Exception ($stmt->error . ' - ' . $piBodyQuery);
                        }

                        // Response message without difference
                        return 'OKMS- Post Completed - Document Closed,PILOC';
                    }
                    else
                    {
                        return 'OKMS- Post Completed with difference,PILOC';

                    }

                    $mErrMsg = 'OK  - Post Complete';
                }
                else
                {
                    // Response message if item cost was not found
                    return 'OKMS- Post Completed. Item cost not found,PILOC';
                }
            }
            catch (Exception $error)
            {
                exit('FAIL- ' . $error);
            }


// // Get Server Data from URL
// 			$mJsonArray = [
//                 'piHeaderId' => $mPiHdrId,
//                 'currentUser' => $mCurrentUser
//             ];
//
// 			// Convert Array to Json Object
// 			try
// 			{
// 				$mJsonObject = json_encode($mJsonArray, JSON_FORCE_OBJECT);
// 			}
// 			catch (Exception $e)
// 			{
// 				exit("FAIL-Json Error");
// 			}
// 			// Replace default Server Tomcat Service
// 			$mConnectp = str_replace(":8081/vantec", ":8091/pi", $mConnect);
// 			if ($mConnectp == $mConnect)
// 			{
// 				$e = strpos($mConnect, ":8081");
// 				$mConnectp = substr($mConnect, 0, $e) . ":8091/pi/";
// 			}
// 			$ch = curl_init();
// 			try
// 			{
// 				curl_setopt($ch, CURLOPT_URL, $mConnectp . "countVarienceValuePI?piHeaderId=" . $mPiHdrId . "currentUser=" . $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);
// 			}

            $mErrMsg = "FAIL-P.I Check Closed";
        }
        else
        {
            $mErrMsg = "FAIL-There is no active P-I count for this location";
        }

        mysqli_close($mConnection);
        return ($mErrMsg);
    }

    /*
                   // PI Serial Relocate
                       if($mPiLocationHash != "")
                       {
                           $mDataQuery = "SELECT id AS id, location_code AS location_code, location_hash AS hash_code FROM location WHERE location_hash='".$mPiLocationHash."' LIMIT 1";
                           if (!$mData = mysqli_query($mConnection,$mDataQuery))
                               return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                           if (mysqli_num_rows($mData) > 0)
                           {
                               ($mRow = mysqli_fetch_assoc($mData));
                               $mLocationCode = ($mRow['location_code']);
                           }
                           else
                           {
                               $mErrMsg = "FAIL-Invalid Location";
                           }
                           if ($mErrMsg == "")
                           {
                               if ($mLocationCode != $mPiLocationCode)
                               {
                                   $mErrMsg = "FAIL-Wrong Location Scanned";
                               }
                           }
                   // Update Inventory & Transaction History
                           if ($mErrMsg == "")
                           {
                               $mDataQuery = "SELECT id AS id, location_code AS location_code, location_hash AS hash_code FROM location WHERE location_hash='".$mVldLocationHash."' LIMIT 1";
                               if (!$mData = mysqli_query($mConnection,$mDataQuery))
                                   return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                               if (mysqli_num_rows($mData) > 0)
                               {
                                   ($mRow = mysqli_fetch_assoc($mData));
                                   $mLocationCode = ($mRow['location_code']);
                               }
                               $mDataQuery = "SELECT pi_header.id AS id, pi_header.actual_case_qty AS actual_case_qty FROM pi_header JOIN location ON pi_header.location_code=location.location_code LEFT JOIN document_status ON pi_header.document_status_id=document_status.id WHERE location.location_code='".$mLocationCode."' AND (document_status_id IS null OR lower(document_status_code) NOT IN ('closed')) LIMIT 1";
                               if (!$mData = mysqli_query($mConnection,$mDataQuery))
                                   return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                               if (mysqli_num_rows($mData) > 0)
                               {
                                   ($mRow = mysqli_fetch_assoc($mData));
                                   $mPiHdrId = ($mRow['id']);
                               }
                               $mDocumentReferenceCode = "000000".round($mPiHdrId);
                               $mDocumentReferenceCode = "PI".substr($mDocumentReferenceCode,-6);
                               if ($mPartNumber == "")
                                   $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,location.location_hash AS hash_code, location.location_code AS location_code, inventory_status.inventory_status_code AS status_code FROM inventory_master LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id WHERE inventory_master.serial_reference='".$mSerialReference."' AND inventory_qty > 0 LIMIT 1";
                               else
                                   $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,location.location_hash AS hash_code, location.location_code AS location_code, inventory_status.inventory_status_code AS status_code FROM inventory_master LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id JOIN location ON inventory_master.current_location_id=location.id JOIN part ON inventory_master.part_id=part.id WHERE inventory_master.serial_reference='".$mSerialReference."' AND part.part_number='".$mPartNumber."' AND inventory_qty > 0 LIMIT 1";
                               if (!$mData = mysqli_query($mConnection,$mDataQuery))
                                   return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery."-".$mFilter);
                               while($mRow = mysqli_fetch_assoc($mData))
                               {
                                   $mId = ($mRow['id']);
                                   $mTranQuery = "INSERT INTO transaction_history (id,short_code,part_number,serial_reference,ran_or_order,txn_qty,from_location_code,to_location_code,associated_document_reference,date_created,last_updated,last_updated_by,transaction_reference_code) VALUES(DEFAULT,'PI','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".($mRow['ran_order'])."',".($mRow['inventory_qty']).",'".$mLocationCode."','".($mRow['location_code'])."','".$mDocumentReferenceCode."',now(),now(),'".$mCurrentUser."','PI')";
                                   if (!mysqli_query($mConnection,$mTranQuery))
                                       return("FAIL-".mysqli_error($mConnection)."-".$mTranQuery);
                               }
                               $mDataQuery = "UPDATE inventory_master SET last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id =".$mId." LIMIT 1";
                               if (!mysqli_query($mConnection,$mDataQuery))
                                   return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                           }
                           mysqli_close($mConnection);
                   //		$mErrMsg = "DATA:qty::H:skip3,serialReference::I,partNumber::H,piLocationHashCode::H";
                           if ($mErrMsg == "")
                               $mErrMsg = "OK  -";
                           exit($mErrMsg);
                       }
                   */

    // PI Location / Box Count
//	elseif($mLocationCode != "")
    if ($mLocationCode != "")
    {
        if ($mQty == "")
        {
            // Check for existing Open PI Header
            $mDataQuery = "SELECT 
                    pi_header.id AS id,
                    pi_header.actual_case_qty AS actual_case_qty
                FROM pi_header 
                    JOIN location ON pi_header.location_code = location.location_code
                    LEFT JOIN document_status ON pi_header.document_status_id = document_status.id
                WHERE 
                    location.location_hash = '{$mLocationCode}'
                    AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
                LIMIT 1";

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

            if (mysqli_num_rows($mData) > 0)
            {
                $mRow = mysqli_fetch_assoc($mData);
                $mQty = ($mRow['actual_case_qty']);
                $mErrMsg = "DATA:locationHashCode:" . $mLocationCode . ":P,qty:" . $mQty . ":P:skip3,serialReference:";
                return ($mErrMsg);
            }
            else
            {
                return ("OK  -");
            }
        }
        elseif (($mQty < "0") || ($mQty > "999999"))
        {
            return ("FAIL-Invalid Count Quantity");
        }

        // Valid Box Count - Get Inventory Serial Count
        $mDataQuery = "SELECT
				count(inventory_master.id) AS count
			FROM inventory_master 
                LEFT JOIN inventory_status ON inventory_master.inventory_status_id = inventory_status.id
                JOIN location ON inventory_master.current_location_id=location.id
			WHERE 
				inventory_master.inventory_qty > 0
				AND (inventory_master.inventory_status_id IS NULL OR lower(inventory_status.inventory_status_code) NOT IN ('pkd','plt'))
				AND location.location_hash='{$mLocationCode}'
			LIMIT 1";

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

        if ((mysqli_num_rows($mData) == 0))
        {
            $mErrMsg = "FAIL-Error Found in Location";
        }
        else
        {
            $i = 0;
            $mDataStr = "0";
            while ($mRow = mysqli_fetch_assoc($mData))
            {
                $mDataStr = ($mRow['count']);
                $i++;
            }

            // Create / Update pi_header Record
            $mDataQuery = "SELECT
					pi_header.id AS id
				FROM pi_header
                    JOIN location ON pi_header.location_code = location.location_code
                    LEFT JOIN document_status ON pi_header.document_status_id = document_status.id WHERE 
                        location.location_hash='{$mLocationCode}'
                        AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
				LIMIT 1
			";

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

            if (mysqli_num_rows($mData) > 0)
            {
                ($mRow = mysqli_fetch_assoc($mData));
                $mPiHdrId = ($mRow['id']);
            }
            //			if ((mysqli_num_rows($mData) == 0))
            else
            {
                $mDataQuery = "SELECT id FROM document_status WHERE LOWER(document_status_code) = 'open' LIMIT 1";
                if (!$mData = mysqli_query($mConnection, $mDataQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
                }
                $mRow = mysqli_fetch_assoc($mData);
                $mDocId = ($mRow['id']);

                $mDataQuery = "SELECT location_code FROM location WHERE location_hash='{$mLocationCode}' LIMIT 1";
                if (!$mData = mysqli_query($mConnection, $mDataQuery))
                {
                    return "FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery;
                }
                $mRow = mysqli_fetch_assoc($mData);

                if ($mDocId != "")
                {
                    $mDataQuery = "INSERT INTO pi_header 
                            (id, location_code, actual_case_qty, expected_case_qty, difference, document_status_id, date_created, created_by, last_updated_date, last_updated_by, serial_not_exist, incorrect_location, picking_left)
                        VALUES
                            (DEFAULT, '{$mRow['location_code']}', {$mQty}, {$mDataStr}, 0, {$mDocId}, now(), '{$mCurrentUser}', now(), '{$mCurrentUser}', 0, 0, 0)";
                }
                else
                {
                    $mDataQuery = "INSERT INTO pi_header 
                            (id, location_code, actual_case_qty, expected_case_qty, difference, document_status_id, date_created, created_by, last_updated_date, last_updated_by, serial_not_exist, incorrect_location, picking_left)
                        VALUES
                            (DEFAULT, '{$mRow['location_code']}', {$mQty}, {$mDataStr}, 0, null, now(), '{$mCurrentUser}', now(), '{$mCurrentUser}', 0, 0, 0)";
                }

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

                $mPiHdrId = mysqli_insert_id($mConnection);
                $mDataQuery = "UPDATE pi_header
				        SET 
				            difference = (actual_case_qty - expected_case_qty),
				            last_updated_date = now(),
				            last_updated_by = '{$mCurrentUser}'
				        WHERE id = {$mPiHdrId}
				        LIMIT 1";

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

// Move to stockcheck location regardless of box count accuracy
            if (($mQty != $mDataStr) || ($mQty == $mDataStr))
            {
                //				$mErrMsg = "FAIL-P.I Count Error";

// Update / Create PI transactions & update Inventory with 'stkchk' Location
                $mDataQuery = "SELECT id, location_code FROM location WHERE LOWER(location_code = 'stkchk') LIMIT 1";

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

                if (mysqli_num_rows($mData) == 0)
                {
                    return ("FAIL-System Error - Invalid Stock-Check Location");
                }
                $mRow = mysqli_fetch_assoc($mData);
                $mLocId = ($mRow['id']);
                $mLocStk = ($mRow['location_code']);

                // Get Inventory Statii
                $mDataQuery = "SELECT id FROM inventory_status WHERE LOWER(inventory_status_code) IN('ok','ok') LIMIT 1";
                if (!$mData = mysqli_query($mConnection, $mDataQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
                }
                $mRow = mysqli_fetch_assoc($mData);
                $mInvOk = ($mRow['id']);

                $mDataQuery = "SELECT id FROM inventory_status WHERE LOWER(inventory_status_code) IN('pkd','plt') LIMIT 1";
                if (!$mData = mysqli_query($mConnection, $mDataQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
                }
                ($mRow = mysqli_fetch_assoc($mData));
                $mInvPkd = ($mRow['id']);

                $mDataQuery = "SELECT id FROM inventory_status WHERE LOWER(inventory_status_code) IN('pi','pi') LIMIT 1";
                if (!$mData = mysqli_query($mConnection, $mDataQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
                }
                ($mRow = mysqli_fetch_assoc($mData));
                $mInvPi = ($mRow['id']);

                // Get Part Details
                $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,
                        location.location_code AS location_code
                    FROM inventory_master
                        JOIN part ON inventory_master.part_id = part.id
                        LEFT JOIN inventory_status ON inventory_master.inventory_status_id = inventory_status.id
                        JOIN location ON inventory_master.current_location_id = location.id
                    WHERE 
                        inventory_master.inventory_qty > 0
                        AND (inventory_master.inventory_status_id IS NULL OR inventory_master.inventory_status_id <> {$mInvPkd})
                        AND location.location_hash='{$mLocationCode}'";

                if (!mysqli_query($mConnection, $mDataQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . $mDataQuery);
                }
                $mData = (mysqli_query($mConnection, $mDataQuery));
                $mDocumentReferenceCode = "000000" . round($mPiHdrId);
                $mDocumentReferenceCode = "PI" . substr($mDocumentReferenceCode, -6);

                if ($mPiHdrId == "")
                {
                    return ("FAIL-PI Header System Failure");
                }

                while ($mRow = mysqli_fetch_assoc($mData))
                {
                    $mPiBdyQuery = "SELECT
					        pi_body.id
                        FROM pi_body
                        WHERE 
                            pi_header_id = {$mPiHdrId}
                            AND part_number = '{$mRow['part_number']}'
                            AND location_code='{$mRow['location_code']}'
                        LIMIT 1";

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

                    if (mysqli_num_rows($mPiBdyData) == 0)
                    {
                        $mPiBdyQuery = "INSERT INTO pi_body
                                (id, part_number, location_code, expected_qty, actual_qty, difference, pi_header_id, date_created,created_by, last_updated_date, last_updated_by, document_status_id, serial_not_exist, incorrect_location, picking_left)
                            VALUES
                                (DEFAULT, '{$mRow['part_number']}', '{$mRow['location_code']}', 0, 0, 0, {$mPiHdrId}, now(), '{$mCurrentUser}', now(), '{$mCurrentUser}', 1, 0, 0, 0)";

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

                        $mPiBdyId = mysqli_insert_id($mConnection);
                    }
                    else
                    {
                        $mPiBdyRow = mysqli_fetch_assoc($mPiBdyData);
                        $mPiBdyId = ($mPiBdyRow['id']);
                    }

                    $mPiDtlQuery = "SELECT id 
                        FROM pi_detail
                        WHERE 
                            pi_body_id = {$mPiBdyId} 
                            AND serial_reference = '{$mRow['serial_reference']}' 
                        LIMIT 1";

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

                    if (mysqli_num_rows($mPiDtlData) == 0)
                    {
                        $mPiDtlQuery = "INSERT INTO pi_detail
                                (id, serial_reference, expected_qty, actual_qty, difference, pi_body_id, date_created, created_by,  last_updated_date, last_updated_by)
                            VALUES 
                                (DEFAULT, '{$mRow['serial_reference']}', {$mRow['inventory_qty']}, 0, 0 - {$mRow['inventory_qty']}, {$mPiBdyId}, now(), '{$mCurrentUser}', now(), '{$mCurrentUser}')";

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

                        $mPiBdyQuery = "UPDATE pi_body
                            SET 
                                expected_qty = expected_qty + 1,
                                difference = (actual_qty - expected_qty),
                                last_updated_date = now(),
                                last_updated_by = '{$mCurrentUser}'
                            WHERE id={$mPiBdyId}
                            LIMIT 1";

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

                    $mTranQuery = "INSERT INTO transaction_history
                            (id, short_code, part_number, serial_reference, ran_or_order, txn_qty, from_location_code, to_location_code, associated_document_reference, date_created, last_updated, last_updated_by, transaction_reference_code)
                        VALUES
                            (DEFAULT, 'PI', '{$mRow['part_number']}', '{$mRow['serial_reference']}', '{$mRow['ran_order']}', {$mRow['inventory_qty']}, '{$mRow['location_code']}', '{$mLocStk}', '{$mDocumentReferenceCode}', now(), now(), '{$mCurrentUser}', 'PI')";

                    if (!mysqli_query($mConnection, $mTranQuery))
                    {
                        return ("FAIL-" . mysqli_error($mConnection) . "-" . $mTranQuery);
                    }
                }
                $mDataQuery = "UPDATE inventory_master
				    SET 
				        current_location_id = {$mLocId},
				        location_id = {$mLocId},
				        location_code = '{$mLocStk}',
				        inventory_status_id = {$mInvPi},
				        last_updated = now(),
				        last_updated_by = '{$mCurrentUser}'
				    WHERE 
				        inventory_master.inventory_qty > 0
				        AND (inventory_status_id IS NULL OR inventory_status_id <> {$mInvPkd})
				        AND EXISTS (SELECT id FROM location WHERE inventory_master.current_location_id=location.id AND location.location_hash='{$mLocationCode}')";
                if (!mysqli_query($mConnection, $mDataQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
                }
                // Prompt for Serial Scan
                $mErrMsg = "DATA:qty:" . $mQty . ":P:skip3,serialReference:";
                return ($mErrMsg);
            }
        }
        /*
                                      // Box Count Correct Post PILOC Update
                                              if ($mErrMsg == "")
                                              {
                                                  $mDataQuery = "SELECT id FROM inventory_status WHERE lower(inventory_status_code) IN('pkd','plt') LIMIT 1";
                                                  if (!$mData = mysqli_query($mConnection,$mDataQuery))
                                                      return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                                                  ($mRow = mysqli_fetch_assoc($mData));
                                                  $mInvStatus = ($mRow['id']);
                                                  $mDataQuery = "UPDATE inventory_master SET last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE inventory_master.inventory_qty>0 AND (inventory_status_id IS null OR inventory_status_id <> ".$mInvStatus.") AND EXISTS(SELECT id FROM location WHERE inventory_master.current_location_id=location.id AND location.location_hash='".$mLocationCode."')";
                                                  if (!mysqli_query($mConnection,$mDataQuery))
                                                      return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                                                  $mDataQuery = "SELECT id FROM document_status WHERE lower(document_status_code)='closed' LIMIT 1";
                                                  if (!$mData = mysqli_query($mConnection,$mDataQuery))
                                                      return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                                                  ($mRow = mysqli_fetch_assoc($mData));
                                                  $mDocId = ($mRow['id']);
                                                  $mDataQuery = "SELECT pi_header.id AS id, pi_header.actual_case_qty AS actual_case_qty FROM pi_header JOIN location ON pi_header.location_code=location.location_code LEFT JOIN document_status ON pi_header.document_status_id=document_status.id WHERE location.location_hash='".$mLocationCode."' AND (document_status_id IS null OR lower(document_status_code) NOT IN ('closed')) LIMIT 1";
                                                  if (!$mData = mysqli_query($mConnection,$mDataQuery))
                                                      return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
                                                  if (mysqli_num_rows($mData) > 0)
                                                  {
                                                      ($mRow = mysqli_fetch_assoc($mData));
                                                      $mPiId = ($mRow['id']);
                                                  }
                                                  $mDataQuery = "UPDATE pi_header SET document_status_id=".$mDocId.",difference=(actual_case_qty-expected_case_qty),last_updated_date=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mPiId;
                                                  if (!mysqli_query($mConnection,$mDataQuery))
                                                      return("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);

                                                  $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, location.location_code AS location_code FROM inventory_master JOIN part ON inventory_master.part_id=part.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id JOIN location ON inventory_master.current_location_id=location.id WHERE inventory_master.inventory_qty>0 AND (inventory_master.inventory_status_id IS null OR inventory_master.inventory_status_id <> ".$mInvStatus.") AND location.location_hash='".$mLocationCode."'";
                                                  if (!mysqli_query($mConnection,$mDataQuery))
                                                      return("FAIL-".mysqli_error($mConnection).$mDataQuery);
                                                  $mData = (mysqli_query($mConnection,$mDataQuery));
                                                  $mDocumentReferenceCode = "000000".round($mPiId);
                                                  $mDocumentReferenceCode = "PI".substr($mDocumentReferenceCode,-6);
                                                  while($mRow = mysqli_fetch_assoc($mData))
                                                  {
                                                      $mPiBdyQuery = "SELECT id FROM pi_body WHERE pi_header_id=".$mPiId." AND part_number='".($mRow['part_number'])."' AND location_code='".($mRow['location_code'])."' LIMIT 1";
                                                      if (!$mPiBdyData = mysqli_query($mConnection,$mPiBdyQuery))
                                                          return "FAIL-".mysqli_error($mConnection)."-".$mPiBdyQuery;
                                                      if (mysqli_num_rows($mPiBdyData) == 0)
                                                      {
                                                          $mPiBdyQuery = "INSERT INTO pi_body (id,part_number,location_code,expected_qty,actual_qty,difference,pi_header_id,date_created,created_by,last_updated_date,last_updated_by,document_status_id,serial_not_exist,incorrect_location,picking_left) VALUES(DEFAULT,'".($mRow['part_number'])."','".($mRow['location_code'])."',0,0,0,".$mPiId.",now(),'".$mCurrentUser."',now(),'".$mCurrentUser."',3,0,0,0)";
                                                          if (!mysqli_query($mConnection,$mPiBdyQuery))
                                                              return ("FAIL-".mysqli_error($mConnection)."-".$mPiBdyQuery);
                                                          $mPiBdyId=mysqli_insert_id($mConnection);
                                                      }
                                                      else
                                                      {
                                                          $mPiBdyRow = mysqli_fetch_assoc($mPiBdyData);
                                                          $mPiBdyId = ($mPiBdyRow['id']);
                                                      }
                                                      $mPiDtlQuery = "SELECT id FROM pi_detail WHERE pi_body_id=".$mPiBdyId." AND serial_reference ='".($mRow['serial_reference'])."' LIMIT 1";
                                                      if (!$mPiDtlData = mysqli_query($mConnection,$mPiDtlQuery))
                                                          return "FAIL-".mysqli_error($mConnection)."-".$mPiDtlQuery;
                                                      if (mysqli_num_rows($mPiDtlData) == 0)
                                                      {
                                                          $mPiDtlQuery = "INSERT INTO pi_detail (id,serial_reference,expected_qty,actual_qty,difference,pi_body_id,date_created,created_by,last_updated_date,last_updated_by) VALUES(DEFAULT,'".($mRow['serial_reference'])."',".($mRow['inventory_qty']).",".($mRow['inventory_qty']).",0,".$mPiBdyId.",now(),'".$mCurrentUser."',now(),'".$mCurrentUser."')";
                                                          if (!mysqli_query($mConnection,$mPiDtlQuery))
                                                              return ("FAIL-".mysqli_error($mConnection)."-".$mPiDtlQuery);
                                                          $mPiBdyQuery = "UPDATE pi_body SET expected_qty=expected_qty+".($mRow['inventory_qty']).", actual_qty=actual_qty+".($mRow['inventory_qty']).", difference=(actual_qty-expected_qty), last_updated_date=now(), last_updated_by='".$mCurrentUser."' WHERE id=".$mPiBdyId." LIMIT 1";
                                                          if (!mysqli_query($mConnection,$mPiBdyQuery))
                                                              return ("FAIL-".mysqli_error($mConnection)."-".$mPiBdyQuery);
                                                      }
                                                      $mTranQuery = "INSERT INTO transaction_history (id,short_code,part_number,serial_reference,ran_or_order,txn_qty,from_location_code,to_location_code,associated_document_reference,date_created,last_updated,last_updated_by,transaction_reference_code) VALUES(DEFAULT,'PI','".($mRow['part_number'])."','".($mRow['serial_reference'])."','".($mRow['ran_order'])."',".($mRow['inventory_qty']).",'".($mRow['location_code'])."','".($mRow['location_code'])."','".$mDocumentReferenceCode."',now(),now(),'".$mCurrentUser."','PI')";
                                                      if (!mysqli_query($mConnection,$mTranQuery))
                                                          return("FAIL-".mysqli_error($mConnection)."-".$mTranQuery);
                                                  }
                                                  $mErrMsg = "OKMS-Count Correct";
                                                  return($mErrMsg);
                                              }
                                      */
    }
    // PI Serial Scan
    elseif (($mSerialReference != ""))
    {
        $mErrMsg = "";

        if ($mPartNumber == "")
        {
            $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.tag_reference AS plt_number,
                location.location_hash AS hash_code,
                location.location_code AS location_code,
                inventory_status.inventory_status_code AS status_code,
                part.part_number AS part_number
            FROM inventory_master
                LEFT JOIN inventory_status ON inventory_master.inventory_status_id = inventory_status.id
                JOIN location ON inventory_master.current_location_id = location.id
                JOIN part ON inventory_master.part_id = part.id
            WHERE
                inventory_master.serial_reference = '{$mSerialReference}'
                AND inventory_qty > 0
            LIMIT 2";
        }
        else
        {
            $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.tag_reference AS plt_number,
                    location.location_hash AS hash_code,
                    location.location_code AS location_code,
                    inventory_status.inventory_status_code AS status_code,
                    part.part_number AS part_number
                FROM inventory_master
                    LEFT JOIN inventory_status ON inventory_master.inventory_status_id = inventory_status.id
                    JOIN location ON inventory_master.current_location_id=location.id
                    JOIN part ON inventory_master.part_id = part.id
                WHERE
                    inventory_master.serial_reference = '{$mSerialReference}'
                    AND part.part_number='{$mPartNumber}'
                    AND inventory_qty > 0
                LIMIT 2";
        }

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

        if ((mysqli_num_rows($mData) == 0))
        {
            if ($mPartNumber == "")
            {
                $mErrMsg = "FAIL-Unknown Serial";
            }
            else
            {
                $mErrMsg = "FAIL-Unknown Serial / Part";
            }

            // Update incorrect_serial count
            $mPiQuery = "SELECT
			        pi_header.id AS id,
			        pi_header.actual_case_qty AS actual_case_qty,
			        pi_header.serial_not_exist AS serial_not_exist
                FROM pi_header 
                    JOIN location ON pi_header.location_code = location.location_code
                    LEFT JOIN document_status ON pi_header.document_status_id = document_status.id
                WHERE
                    location.location_hash = '{$mVldLocationHash}'
                    AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
                LIMIT 1";

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

            $mSerialNotExist = 0;
            $mActualQty = 0;

            if (mysqli_num_rows($mPiData) > 0)
            {
                $mPiRow = mysqli_fetch_assoc($mPiData);
                $mPiHdrId = ($mPiRow['id']);
                $mSerialNotExist = ($mPiRow['serial_not_exist']);
                $mActualQty = ($mPiRow['actual_case_qty']);
            }

            $mSerialNotExist++;
            $mActualQty++;

            $mPiQuery = "UPDATE pi_header
			    SET 
			        actual_case_qty = {$mActualQty},
			        serial_not_exist = {$mSerialNotExist},
			        difference = (actual_case_qty - expected_case_qty),
			        last_updated_date = now(),
			        last_updated_by = '{$mCurrentUser}'
			    WHERE id={$mPiHdrId}";

            if (!mysqli_query($mConnection, $mPiQuery))
            {
                return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery);
            }
        }
        if ($mErrMsg == "")
        {
            // Duplicate Part / Serial - Prompt for Part Number
            if (mysqli_num_rows($mData) > 1)
            {
                $mErrMsg = "DATA:serialReference:" . $mSerialReference . "::skip4,partNumber::I";
                return $mErrMsg;
            }
        }
        // Store Inventory Details
        if ($mErrMsg == "")
        {
            $i = 0;
            while ($mRow = mysqli_fetch_assoc($mData))
            {
                $mId = ($mRow['id']);
                $mLocationHash = ($mRow['hash_code']);
                $mLocationCode = ($mRow['location_code']);
                $mRanOrder = ($mRow['ran_order']);
                $mInvQty = ($mRow['inventory_qty']);
                $mPltNumber = ($mRow['plt_number']);
                $mInvStatus = ($mRow['status_code']);
                $mPartNumber = ($mRow['part_number']);
                //				if ($mVldLocationHash == ($mRow['hash_code']))
// Check Inventory / Serial Current Location
                if ((strtolower(($mRow['location_code'])) == "stkchk") || ($mVldLocationHash == ($mRow['hash_code'])))
                {
                    $i++;
                }
            }

            // If Serial is in stkchk - Check PI Original Location
            if (strtolower($mLocationCode) == "stkchk")
            {
                $mPiQuery = "SELECT
                        pi_detail.id AS id,
                        pi_body.location_code AS location_code,
                        location.location_hash AS location_hash 
                    FROM pi_detail
                        JOIN pi_body ON pi_detail.pi_body_id = pi_body.id
                        JOIN pi_header ON pi_body.pi_header_id = pi_header.id
                        JOIN location ON pi_body.location_code = location.location_code
                    WHERE 
                        pi_detail.serial_reference = '{$mSerialReference}'
                        AND pi_body.part_number = '{$mPartNumber}'
                        AND location.location_hash <> '{$mVldLocationHash}'
                        AND pi_detail.expected_qty > 0
                    LIMIT 1";

                if (!$mPiData = mysqli_query($mConnection, $mPiQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery . "-" . mysqli_num_rows($mPiData));
                }

                if (mysqli_num_rows($mPiData) > 0)
                {
                    $mPiRow = mysqli_fetch_assoc($mPiData);
                    $mLocationHash = ($mPiRow['hash_code']);
                    $mLocationCode = ($mPiRow['location_code']);
                    $i = 0;
                    $mPiQuery = "UPDATE pi_detail SET expected_qty = 0 WHERE id = {$mPiRow['id']} LIMIT 1";

                    if (!mysqli_query($mConnection, $mPiQuery))
                    {
                        return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery);
                    }
                }
                //				return("FAIL-TEST1".mysqli_error($mConnection)."-".$mPiQuery."-".mysqli_num_rows($mPiData));
            }
            // Start

// Check Inventory Picked / Location Status
            if ((((strtolower($mInvStatus) == "pkd") || (strtolower($mInvStatus) == "plt")) && (strtolower($mLocationCode) != "stkchk")) || ($i == 0))
            {
                // Update picked / left count
                $mPiQuery = "SELECT
				        pi_header.id AS id,
				        pi_header.expected_case_qty AS expected_case_qty,
				        pi_header.actual_case_qty AS actual_case_qty,
				        CASE
				            WHEN pi_header.incorrect_location IS NULL THEN 0
				            ELSE pi_header.incorrect_location
                        END AS incorrect_location,
                        CASE
                            WHEN pi_header.picking_left IS NULL THEN 0
                            ELSE pi_header.picking_left
                        END AS picking_left
                    FROM pi_header
                        JOIN location ON pi_header.location_code = location.location_code
                        LEFT JOIN document_status ON pi_header.document_status_id = document_status.id
                    WHERE 
                        location.location_hash = '{$mVldLocationHash}'
                        AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
                    LIMIT 1";

                if (!$mPiData = mysqli_query($mConnection, $mPiQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery . "-" . mysqli_num_rows($mPiData));
                }

                $mPickingLeft = 0;
                $mIncorrectLocation = 0;
                $mActualQty = 0;
                $mPiHdrId = "";

                if (mysqli_num_rows($mPiData) > 0)
                {
                    $mPiRow = mysqli_fetch_assoc($mPiData);
                    $mPiHdrId = ($mPiRow['id']);
                    $mPickingLeft = ($mPiRow['picking_left']);
                    $mIncorrectLocation = ($mPiRow['incorrect_location']);
                    $mActualQty = ($mPiRow['actual_case_qty']);
                }

                $mDocumentReferenceCode = "000000" . round($mPiHdrId);
                $mDocumentReferenceCode = "PI" . substr($mDocumentReferenceCode, -6);

                if ((strtolower($mInvStatus) == "pkd") || (strtolower($mInvStatus) == "plt"))
                {
                    $mPickingLeft++;
                }
                else
                {
                    $mIncorrectLocation++;
                }

                $mActualQty++;

                $mPiQuery = "UPDATE pi_header
				    SET 
				        actual_case_qty = {$mActualQty},
				        incorrect_location = {$mIncorrectLocation},
				        picking_left = {$mPickingLeft},
				        difference = (actual_case_qty - expected_case_qty),
				        last_updated_date = now(),
				        last_updated_by = '{$mCurrentUser}'
				    WHERE id={$mPiHdrId}";

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

// Update picked / left count
                $mPiQuery = "SELECT
				        pi_body.id AS id,
				        pi_body.actual_qty AS actual_qty,
				        CASE
				            WHEN pi_body.incorrect_location IS NULL THEN 0
				            ELSE pi_body.incorrect_location
                        END AS incorrect_location,
                        CASE
                            WHEN pi_body.picking_left IS NULL THEN 0
                            ELSE pi_body.picking_left
                        END AS picking_left
                    FROM pi_body
                        LEFT JOIN document_status ON document_status_id = document_status.id
                    WHERE 
                        pi_header_id = {$mPiHdrId}
                        AND part_number = '{$mPartNumber}'
                        AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
                    LIMIT 1";

                if (!$mPiData = mysqli_query($mConnection, $mPiQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery . "-" . mysqli_num_rows($mPiData));
                }

                $mPickingLeft = 0;
                $mIncorrectLocation = 0;
                $mActualQty = 0;
                $mPiBdyId = "";

                if (mysqli_num_rows($mPiData) > 0)
                {
                    $mPiRow = mysqli_fetch_assoc($mPiData);
                    $mPiBdyId = ($mPiRow['id']);
                    $mPickingLeft = ($mPiRow['picking_left']);
                    $mIncorrectLocation = ($mPiRow['incorrect_location']);
                    $mActualQty = ($mPiRow['actual_qty']);
                }

                if ((strtolower($mInvStatus) == "pkd") || (strtolower($mInvStatus) == "plt"))
                {
                    $mPickingLeft++;
                }
                else
                {
                    $mIncorrectLocation++;
                }

                $mActualQty++;
                if ($mPiBdyId != "")
                {
                    $mPiQuery = "UPDATE pi_body
					    SET
					        actual_qty = {$mActualQty},
					        incorrect_location = {$mIncorrectLocation},
					        picking_left = {$mPickingLeft},
					        last_updated_date = now(),
					        last_updated_by = '{$mCurrentUser}'
					    WHERE id={$mPiBdyId}";

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

                // Update Inventory master with 'PI' Location
                $mPiQuery = "SELECT id, location_code FROM location WHERE LOWER(location_code='stkchk') LIMIT 1";

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

                $mPiRow = mysqli_fetch_assoc($mPiData);
                $mLocId = ($mPiRow['id']);
                $mLocStk = ($mPiRow['location_code']);

                //				$mPiQuery = "UPDATE inventory_master SET current_location_id=".$mLocId.", location_code='".$mLocStk."',last_updated=now(),last_updated_by='".$mCurrentUser."' WHERE id=".$mId." LIMIT 1";

                $mPiQuery = "UPDATE inventory_master
				    SET
				        current_location_id = {$mLocId},
				        location_id = {$mLocId},
				        location_code = '{$mLocStk}',
				        last_updated = now(),
				        last_updated_by = '{$mCurrentUser}'
				    WHERE id = {$mId}
                    LIMIT 1";

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

                $mPiQuery = "SELECT
                        pi_body.id AS id
                    FROM pi_body
                    WHERE 
                        pi_header_id={$mPiHdrId}
                        AND part_number ='{$mPartNumber}'
                    LIMIT 1";

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

                if (mysqli_num_rows($mPiData) > 0)
                {
                    $mPiRow = mysqli_fetch_assoc($mPiData);
                    $mPiBdyId = ($mPiRow['id']);
                }
                else
                {
                    $mPiQuery = "INSERT INTO pi_body
                            (id, part_number, location_code, expected_qty, actual_qty, difference, pi_header_id, date_created, created_by, last_updated_date, last_updated_by, document_status_id, serial_not_exist, incorrect_location, picking_left)
                        VALUES
                            (DEFAULT, '{$mPartNumber}', '{$mLocationCode}', 0, 0, 0, {$mPiHdrId}, NOW(), '{$mCurrentUser}', NOW(), '{$mCurrentUser}', 3, 0, 0, 0)";

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

                    $mPiBdyId = mysqli_insert_id($mConnection);
                }

                $mPiQuery = "INSERT INTO pi_detail
                        (id, serial_reference, expected_qty, actual_qty, difference, pi_body_id, date_created, created_by, last_updated_date, last_updated_by)
                    VALUES
                        (DEFAULT, '{$mSerialReference}', 0, {$mInvQty}, {$mInvQty}, {$mPiBdyId}, NOW(), '{$mCurrentUser}', NOW(), '{$mCurrentUser}')";

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

                $mTranQuery = "INSERT INTO transaction_history
                        (id, short_code, part_number, serial_reference, ran_or_order, txn_qty, parent_tag, from_location_code, to_location_code, associated_document_reference, date_created, last_updated, last_updated_by, transaction_reference_code)
                    VALUES
                        (DEFAULT, 'PI', '{$mPartNumber}', '{$mSerialReference}', '{$mRanOrder}', {$mInvQty}, '{$mPltNumber}', '{$mLocationCode}', '{$mLocStk}', '{$mDocumentReferenceCode}', NOW(), NOW(), '{$mCurrentUser}', 'PI')";

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

                if ((strtolower($mInvStatus) != "pkd") || (strtolower($mInvStatus) == "plt"))
                {
                    $mErrMsg = "FAIL-Serial should be in location " . $mLocationCode . " - Rescan Serial";
                }
                else
                {
                    if ($mPltNumber != "")
                    {
                        $mErrMsg = "FAIL-Serial has been picked to Pallet " . $mPltNumber . " - Rescan Serial";
                    }
                    else
                    {
                        $mErrMsg = "FAIL-Serial has been picked - Rescan Serial";
                    }
                }

                if ($mErrMsg == "")
                {
                    $mErrMsg = "FAIL-Rescan Serial";
                }

                return $mErrMsg;
            }
        }

        if ($mErrMsg == "")
        {
            // Check Receipt Qty
            $mReceivedQty = "";
            $mDataQuery = "SELECT
                    id,
                    received_qty
                FROM receipt_detail
                WHERE 
                    serial_reference = '{$mSerialReference}'
                    AND part_number='{$mPartNumber}'
                LIMIT 1";

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

            if (mysqli_num_rows($mData) > 0)
            {
                list($mRcpId, $mReceivedQty) = mysqli_fetch_row($mData);
            }

            //  Prompt for Piece Qty
            if ($mScanQty == "")
            {
                //                return("FAIL-TEST1-".$mInvQty."-".$mReceivedQty);
//				$mRow = mysqli_fetch_assoc($mData);
                if ($mInvQty == $mReceivedQty)
                {
                    $mErrMsg = "DATA:serialReference:" . $mSerialReference . ",partNumber:" . $mPartNumber . ":P,scanQty:" . $mInvQty . ",scanQtyinput:" . $mInvQty . ":skipscanQty";
                }
                else
                {
                    $mErrMsg = "DATA:serialReference:" . $mSerialReference . ",partNumber:" . $mPartNumber . ":P:skipscanQty";
                }
                return ($mErrMsg);
            }
        }
        // Valid Piece Qty
        if ($mErrMsg == "")
        {
            // Correct Serial / Qty Scanned - Move Inventory back to location
            $mDataQuery = "SELECT id, location_code FROM location WHERE location_hash = '{$mVldLocationHash}' LIMIT 1";

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

            ($mRow = mysqli_fetch_assoc($mData));
            $mLocId = ($mRow['id']);
            $mLocStk = ($mRow['location_code']);

            // Get Inventory Statuseseses
            $mDataQuery = "SELECT id FROM inventory_status WHERE LOWER(inventory_status_code) IN ('pkd','plt') LIMIT 1";

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

            $mRow = mysqli_fetch_assoc($mData);
            $mInvPkd = ($mRow['id']);

            $mDataQuery = "SELECT id FROM inventory_status WHERE LOWER(inventory_status_code) IN ('ok','ok') LIMIT 1";

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

            $mRow = mysqli_fetch_assoc($mData);
            $mInvOk = ($mRow['id']);

            $mDataQuery = "SELECT
			        pi_header.id AS id,
			        pi_header.actual_case_qty AS actual_case_qty
                FROM pi_header
                    JOIN location ON pi_header.location_code = location.location_code
                    LEFT JOIN document_status ON pi_header.document_status_id = document_status.id
                WHERE 
                    location.location_hash = '{$mVldLocationHash}'
                    AND (document_status_id IS NULL OR lower(document_status_code) NOT IN ('closed'))
                LIMIT 1";

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

            if (mysqli_num_rows($mData) > 0)
            {
                ($mRow = mysqli_fetch_assoc($mData));
                $mPiHdrId = ($mRow['id']);
            }

            $mDocumentReferenceCode = "000000" . round($mPiHdrId);
            $mDocumentReferenceCode = "PI" . substr($mDocumentReferenceCode, -6);

            $mDataQuery = "SELECT
			        part.part_number AS part_number,
			        part.conversion_factor AS conversion_factor,
			        inventory_master.serial_reference AS serial_reference,
			        inventory_master.ran_or_order AS ran_order,
			        inventory_master.inventory_qty AS inventory_qty,
			        location.location_code AS location_code
                FROM inventory_master 
                    JOIN part ON inventory_master.part_id = part.id
                    LEFT JOIN inventory_status ON inventory_master.inventory_status_id = inventory_status.id
                    JOIN location ON inventory_master.current_location_id = location.id
                WHERE inventory_master.id = {$mId}
                LIMIT 1";

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

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

            while ($mRow = mysqli_fetch_assoc($mData))
            {
                $mConversionFactor = $mRow['conversion_factor'];

                if ($mConversionFactor > 1)
                {
                    $mTxnQty = $mScanQty * $mConversionFactor;
                }
                else
                {
                    $mTxnQty = $mScanQty;
                }

                // Check Scanned Qty.
                $mTrnQuery = "SELECT
				        pi_detail.id AS id,
                        pi_detail.actual_qty AS actual_qty,
                        pi_detail.expected_qty AS expected_qty,
                        inventory_master.inventory_qty AS inventory_qty
                    FROM inventory_master
                        JOIN pi_detail ON inventory_master.serial_reference = pi_detail.serial_reference
                    WHERE 
                        inventory_master.id = {$mId}
                        AND EXISTS(SELECT id FROM pi_body WHERE pi_detail.pi_body_id=pi_body.id AND pi_body.part_number = '{$mPartNumber}'AND EXISTS(SELECT id FROM pi_header WHERE pi_body.pi_header_id=pi_header.id AND pi_header.id = {$mPiHdrId}))
                    LIMIT 1";

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

                //				return ("FAIL-".mysqli_error($mConnection)."-".$mTrnQuery);

                $mTrnRow = mysqli_fetch_assoc($mTrnData);

                if (mysqli_num_rows($mTrnData) > 0)
                {
                    $mPiQuery = "UPDATE pi_detail
                        SET 
                            actual_qty = {$mTxnQty},
                            difference = actual_qty - expected_qty,
                            last_updated_date = now(),
                            last_updated_by = '{$mCurrentUser}'
                        WHERE 
                            serial_reference = '{$mSerialReference}'
                        AND EXISTS (
                                SELECT id 
                                FROM pi_body 
                                WHERE 
                                    pi_detail.pi_body_id = pi_body.id
                                    AND pi_body.part_number='{$mPartNumber}'
                                    AND EXISTS (SELECT id FROM pi_header WHERE pi_body.pi_header_id = pi_header.id AND pi_header.id = {$mPiHdrId})
                            )
                        LIMIT 1";

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

                    if ($mTxnQty != $mTrnRow['actual_qty'] && $mTxnQty != $mTrnRow['inventory_qty'])
                    {
                        return ("FAIL-quantity difference - re-enter to confirm");
                    }
                }
                else
                {
                    return ("FAIL-This serial should not be in this location");
                }

                $mTrnQuery = "INSERT INTO transaction_history
                        (id, short_code, part_number, serial_reference, ran_or_order, txn_qty, from_location_code, to_location_code, associated_document_reference, date_created, created_by, last_updated, last_updated_by, transaction_reference_code)
                    VALUES
                        (DEFAULT, 'PI', '{$mRow['part_number']}', '{$mRow['serial_reference']}', '{$mRow['ran_order']}', {$mTxnQty}, '{$mRow['location_code']}', '{$mLocStk}', '{$mDocumentReferenceCode}', now(), '{$mCurrentUser}', now(), '{$mCurrentUser}', 'PI')";

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

            // Update Inventory / Serial with Scanned Location & Piece Qty.

            // TODO: Check if the inventory_qty must be updated on PI Loc. transaction

            // $mDataQuery = "UPDATE inventory_master
            //     SET
            //         current_location_id = {$mLocId},
            //         location_id = {$mLocId},
            //         location_code = '{$mLocStk}',
            //         inventory_qty = {$mTxnQty},
            //         allocated_qty = 0,
            //         available_qty = inventory_qty,
            //         last_updated = now(),
            //         last_updated_by = '{$mCurrentUser}',
            //         inventory_status_id = {$mInvOk}
            //     WHERE id={$mId}
            //     LIMIT 1";

            $mDataQuery = "UPDATE inventory_master
                SET
                    current_location_id = {$mLocId},
                    location_id = {$mLocId},
                    location_code = '{$mLocStk}',
                    allocated_qty = 0,
                    available_qty = inventory_qty,
                    last_updated = now(),
                    last_updated_by = '{$mCurrentUser}',
                    inventory_status_id = {$mInvOk}
                WHERE id = {$mId}
                LIMIT 1";

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

            if ($mPiHdrId != "")
            {
                $mPiQuery = "UPDATE pi_detail
                    SET
                        actual_qty = {$mTxnQty},
                        difference = actual_qty - expected_qty,
                        last_updated_date = now(),
                        last_updated_by = '{$mCurrentUser}'
                    WHERE 
                        serial_reference = '{$mSerialReference}'
                        AND EXISTS(
                                SELECT id
                                FROM pi_body
                                WHERE 
                                    pi_detail.pi_body_id = pi_body.id
                                    AND pi_body.part_number = '{$mPartNumber}'
                                    AND EXISTS(SELECT id FROM pi_header WHERE pi_body.pi_header_id=pi_header.id AND pi_header.id={$mPiHdrId})
                           )
                    LIMIT 1";

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

            // Update PI Body with scan Count
            $mPiQuery = "SELECT id AS id FROM pi_body WHERE pi_header_id = {$mPiHdrId} AND part_number = '{$mPartNumber}' LIMIT 1";
            if (!$mPiData = mysqli_query($mConnection, $mPiQuery))
            {
                return "FAIL-" . mysqli_error($mConnection) . "-" . $mPiQuery;
            }

            $mPiRow = mysqli_fetch_assoc($mPiData);
            $mPiBdyId = ($mPiRow['id']);

            //			if ($mPiBdyId != "")
//			{
            $mPiQuery = "SELECT
                    sum(CASE WHEN expected_qty > 0 THEN 1 ELSE 0 END) AS expected_qty,
                    sum(CASE WHEN actual_qty > 0 THEN 1 ELSE 0 END) AS actual_qty
                FROM pi_detail WHERE pi_body_id={$mPiBdyId} LIMIT 1";

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

            if (mysqli_num_rows($mPiData) > 0)
            {
                $mPiRow = mysqli_fetch_assoc($mPiData);
                $mPiBdyQuery = "UPDATE pi_body
				    SET
				        expected_qty = {$mPiRow['expected_qty']},
				        actual_qty = {$mPiRow['actual_qty']},
				        difference = (actual_qty-expected_qty),
				        last_updated_date = now(),
				        last_updated_by = '{$mCurrentUser}'
				    WHERE id = {$mPiBdyId}
				    LIMIT 1";

                if (!mysqli_query($mConnection, $mPiBdyQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiBdyQuery);
                }
            }
            // $mPiQuery = "SELECT sum(CASE WHEN expected_qty>0 THEN 1 ELSE 0 END) AS expected_qty, sum(CASE WHEN actual_qty>0 THEN 1 ELSE 0 END) AS actual_qty FROM pi_body WHERE pi_header_id=".$mPiHdrId;
            $mPiQuery = "SELECT
			        sum(expected_qty) AS expected_qty,
			        sum(actual_qty) AS actual_qty
                FROM pi_body
                WHERE pi_header_id = {$mPiHdrId}";

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

            if (mysqli_num_rows($mPiData) > 0)
            {
                $mPiRow = mysqli_fetch_assoc($mPiData);
                $mPiHdrQuery = "UPDATE pi_header
				    SET
				        expected_case_qty = {$mPiRow['expected_qty']},
				        actual_case_qty = {$mPiRow['actual_qty']},
				        difference = (actual_case_qty-expected_case_qty),
				        last_updated_date = now(),
				        last_updated_by = '{$mCurrentUser}'
				    WHERE id = {$mPiHdrId}
				    LIMIT 1";

                if (!mysqli_query($mConnection, $mPiHdrQuery))
                {
                    return ("FAIL-" . mysqli_error($mConnection) . "-" . $mPiHdrQuery);
                }
            }
            //			}
//			$mErrMsg = "OK  -";

// Reset Screen Values for next scan
            $mErrMsg = $mErrMsg = "DATA:serialReference::,partNumber::P,piLocationHashCode::H";
        }
    }
    mysqli_close($mConnection);
    if ($mErrMsg == "")
    {
        $mErrMsg = "FAIL-Unexpected Error";
    }
    return $mErrMsg;
}
