<?php
header('Cache-Control: no-cache, no-store, must-revalidate');   
header('Expires: 0');
// Header to allow Cross-Server AJAX Connection
header('Access-Control-Allow-Origin: *');
set_time_limit(0);

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

// validaterdt - VIMS Server Validation

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

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

$mErrMsg = "OK  -";
$mDataStr = "";	
$mSort = "";		   

// Basic call / response
if (($mFunction != null) && ($mFunction == "replen"))
{
    $mErrMsg = "";
// 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 == "transactionType")
			$mTransactionTypeId = strtoupper(trim($v2));
        elseif ($k2 == "sort")
			$mSort = strtoupper(trim($v2));
    }
// mysql connection
	$connection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());            
    $sql = "SELECT pick_group_code, count(replen_task.id) as open_task FROM replen_task left join pick_group on pick_group.id = pick_group_id where processed = 0 group by pick_group_code";
    if ($mSort != "")
        $sql .=" ORDER BY $mSort";
    $result = mysqli_query($connection, $sql) or die("FAIL-Error in Selecting " . mysqli_error($connection)."-".$sql);
    
//create an array
    $array = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $array[] = $row;
    }
    if (mysqli_num_rows($result) == 0)
    {
        $array[0] = array("id"=>"No Records ");
    }
    echo ("TABLE:replen:".json_encode($array));

    //close the db connection
    mysqli_close($connection);
}

if (($mFunction != null) && ($mFunction == "picklist"))
{
    $mErrMsg = "";
// 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 == "transactionType")
			$mTransactionTypeId = strtoupper(trim($v2));
        elseif ($k2 == "reference")
			$mReference = strtoupper(trim($v2));
        elseif ($k2 == "sort")
			$mSort = strtoupper(trim($v2));
    }
// mysql connection
	$connection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());            
//    $sql = "SELECT part_number,description,expected_qty FROM document_segment WHERE customer_reference='$mReference'";
    $sql = "SELECT part.part_number,part.part_description AS description,document_body.qty_expected,document_body.qty_transacted FROM document_header JOIN document_body ON document_header.id=document_body.document_header_id JOIN part ON document_body.part_id=part.id WHERE document_header.tanum='$mReference'";
    if ($mSort != "")
        $sql .=" ORDER BY $mSort";
    $result = mysqli_query($connection, $sql) or die("FAIL-Error in Selecting " . mysqli_error($connection)."-".$sql);

    //create an array
    $array = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $array[] = $row;
    }
    if (mysqli_num_rows($result) == 0)
    {
        $array[0] = array("id"=>"No Records ");
    }
    echo ("TABLE:$mFunction&filter=$mFilter:".json_encode($array));

    //close the db connection
    mysqli_close($connection);
}

if (($mFunction != null) && ($mFunction == "picklabel"))
{
    $mErrMsg = "";
// 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 == "transactionType")
			$mTransactionTypeId = strtoupper(trim($v2));
        elseif ($k2 == "reference")
			$mReference = strtoupper(trim($v2));
        elseif ($k2 == "sort")
			$mSort = strtoupper(trim($v2));
    }
// mysql connection
	$connection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());            
    $sql = "SELECT 
                pick_detail.id AS id,part.part_number AS part,part.part_description AS description,
                pick_detail.location_code AS loc_code,
                CASE WHEN pick_detail.serial_reference_scanned IS null THEN pick_detail.serial_reference ELSE pick_detail.serial_reference_scanned END as serial, 
                CASE WHEN pick_detail.qty_scanned IS null THEN pick_detail.qty_allocated WHEN pick_detail.qty_scanned <= 0 THEN pick_detail.qty_allocated ELSE pick_detail.qty_scanned END AS qty 
                FROM pick_detail 
                JOIN part ON pick_detail.part_id=part.id 
                WHERE pick_detail.document_reference='$mReference' 
                AND pick_detail.last_updated_by='$mCurrentUser'
                AND (pick_detail.qty_scanned IS NOT null AND pick_detail.qty_scanned > 0)
                AND pick_detail.processed=1";
    if (!empty($mSort))
        $sql .=" ORDER BY $mSort";
    else
        $slq .= " ORDER BY pick_detail.document_reference,pick_detail.last_updated_date DESC";
    $sql .=" LIMIT 50";
    $result = mysqli_query($connection, $sql) or die("FAIL-Error in Selecting " . mysqli_error($connection)."-".$sql);
//    error_log("tables-".$sql." ".mysqli_num_rows($result));

    //create an array
    $array = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $array[] = $row;
    }
    if (mysqli_num_rows($result) == 0)
    {
        $array[0] = array("id"=>"No Records ");
    }
    echo ("TABLEPpicklabel:$mFunction&filter=$mFilter:".json_encode($array));

    //close the db connection
    mysqli_close($connection);
}


if (($mFunction != null) && ($mFunction == "invserial"))
{
    $mErrMsg = "";
// 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 == "transactionType")
			$mTransactionTypeId = strtoupper(trim($v2));
        elseif ($k2 == "reference")
			$mReference = strtoupper(trim($v2));
        elseif ($k2 == "sort")
			$mSort = strtoupper(trim($v2));
    }
// mysql connection
	$connection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());            
    $sql = "SELECT DISTINCT inventory_master.serial_reference AS 'serial reference', part.part_number AS  'part number', inventory_master.ran_or_order AS ran,location.location_code AS 'location code', inventory_master.inventory_qty/part.conversion_factor AS 'inv qty', inventory_master.allocated_qty/part.conversion_factor AS 'allc qty',CASE WHEN inventory_status.inventory_status_code IS null THEN 'OK' ELSE inventory_status.inventory_status_code END AS 'status', CASE WHEN inventory_master.requires_inspection IS null THEN 'NO' WHEN inventory_master.requires_inspection=1 THEN 'YES' ELSE 'NO' END AS insp,pick_header.customer_reference AS 'pick reference', tag.tag_reference AS pallet, inventory_master.last_updated AS 'last updated',inventory_master.last_updated_by AS 'oper' FROM inventory_master LEFT JOIN part ON inventory_master.part_id=part.id LEFT JOIN location ON inventory_master.current_location_id=location.id LEFT JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id LEFT JOIN pick_detail ON inventory_master.id=pick_detail.inventory_master_id LEFT JOIN pick_body ON pick_body.id=pick_detail.pick_body_id LEFT JOIN pick_header ON pick_header.id=pick_body.pick_header_id LEFT JOIN tag ON inventory_master.parent_tag_id=tag.id WHERE inventory_master.serial_reference = '$mReference'";
    if ($mSort != "")
        $sql .=" ORDER BY $mSort";
    $result = mysqli_query($connection, $sql) or die("FAIL-Error in Selecting " . mysqli_error($connection)."-".$sql);

    //create an array
    $array = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $array[] = $row;
    }
    if (mysqli_num_rows($result) == 0)
    {
        $array[0] = array("id"=>"No Records ");
    }
    echo ("TABLECpicklist:$mFunction&filter=$mFilter:".json_encode($array));

    //close the db connection
    mysqli_close($connection);
}
elseif (($mFunction != null) && (strtolower($mFunction) == "getserialoslist"))
{
    $mRtsDtlId = "";
    $mErrMsg = "";
    $mSerialReference = null;
    $mPartNumber = null;
    $mFromLoc = null;
    $mToLoc = null;
// Replace Chars in Input String to emulate a JSON format instead of GET format
    $mFilter = str_replace("=", ":", $mFilter);
    $mFilter = str_replace("&", ",", $mFilter);

    $mStatus = getSerialOsList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $mFilter);
    echo $mStatus;
    return;
}
function getSerialOsList($mHost, $mUser, $mPwd, $mDbName, $mCurrentUser, $post)
{
    $mReturn = "";
    $now = date('Y-m-d H:i:s');
    $date = date('Ymd');
    $time = date('His');
    
// Explode Input String Into Array - Separated by ,
    $params = explode(",", $post);
// Create Associative Array by Exploding Array Elements on :
    for ($i = 0; $i < count($params); $i++)
    {
        $s = 0;
        $e = 0;
        $param = $params[$i];
        $s = strpos($param, ":");
        if ($s >= 0)
        {
            $key = substr($param, 0, $s);
            $val = substr($param, $s + 1);
        }
        if (strtolower($key) == "locationcode")
            $mLocationCode = strtoupper($val);
        if (strtolower($key) == "partnumber")
            $mPartNumber = strtoupper($val);
        elseif (strtolower($key) == "type")
            $mType = strtolower($val);
        elseif (strtolower($key) == "key")
            $mKey = strtoupper($val);
         elseif (strtolower($key) == "reference")
            $mFilter = strtolower($val);
        elseif (strtolower($key) == "currentuser")
            $mCurrentUser = strtoupper($val);
        elseif ($key == "transactionType")
            $mTransactionTypeId = strtoupper($val);
    }
    if (empty($mTransactionTypeId))
    {
        $mTransactionTypeId = "RTSOS";
    }
// mysql connection - Use Passed values...
    $mConnection = mysqli_connect($mHost, $mUser, $mPwd, $mDbName) or
    exit('FAIL-Connect failed: ' . mysqli_connect_errno() . ' : ' . mysqli_connect_error());

    $mDataQuery = "SELECT
            inventory_master.serial_reference AS serial_reference,inventory_master.part_number AS part_number,inventory_master.location_code AS location_code,
            inventory_master.inventory_qty AS inventory_qty,inventory_master.allocated_qty AS allocated_qty
        FROM inventory_master
        JOIN location ON inventory_master.current_location_id=location.id
        WHERE
            inventory_master.available_qty>0";
    if (!empty($mLocationCode))
    {
        $mDataQuery .=" AND (location.location_code='$mLocationCode' OR location.location_hash='$mLocationCode')";
    }
    if (!empty($mPartNumber))
    {
        $mDataQuery .=" AND inventory_master.part_number='$mPartNumber'";
    }
    if (!empty($mFilter))
    {
        $mDataQuery .=" AND (location.location_code LIKE('%$mFilter%') OR location.location_hash LIKE('%$mFilter%') OR
                        inventory_master.part_number LIKE('%$mFilter%') OR
                        inventory_master.serial_reference LIKE('%$mFilter%'))";
    }
    $mDataQuery .=" ORDER BY location_code, part_number, serial_reference
        LIMIT 1000";
//    error_log("tables.php-".$mDataQuery."-".$post);
    if (!$mData = mysqli_query($mConnection, $mDataQuery))
    {
        exit("FAIL-" . mysqli_error($mConnection) . "-" . $mDataQuery);
    }
    $mDropdown = array();
    while ($mRow = mysqli_fetch_assoc($mData))
    {
        if ($mType == "list")
        {
            $mDropdown[] = array('value' => $mRow['serial_reference'], 'text' => $mRow['location_code']." / ".$mRow['part_number']." / ".$mRow['serial_reference']);
        }
        else
        {
            $mDropdown[] = array('Loc.' => $mRow['location_code'],'Part' => $mRow['part_number'], 'Serial' => $mRow['serial_reference'], 'inventory' => $mRow['inventory_qty'], 'allocated' => $mRow['allocated_qty']);
        }
    }
	if (mysqli_num_rows($mData) == 0)
	{
		$mDropdown[] = array('value' => "No Results", 'text' => "No Results");
	}
    
    $mConnection->close();
    if ($mType == "list")
    {
        return 'LIST:serialReference:'.json_encode($mDropdown);
    }
    else
    {
        return 'TABLE:serialReference:'.json_encode($mDropdown);
    }
}
?>