<?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: *');

//postDocument - Transaction Processing
// echo "FAIL-test";

$mReturn = "";

// Update Document Header, Body, Detail
function postdocument($mJsonObject,$mTranDocumentType,$mDocumentReferencePrefix,$mHost,$mUser,$mPwd,$mDbName)
{
	$mReturn = "";
	$mParms = json_decode($mJsonObject, true);
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return "FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error();
	
	$mAutoCreateDoc = "";
	$mDocumentReferenceCode = "";
	
// Get id for document status
	$mDocStsId = 0;
	$mDocCmpId = 0;
	$mDataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while($mRow = mysqli_fetch_assoc($mData))
	{
		if (strtolower($mRow['document_status_code']) == "open")
			$mDocStsId=($mRow['id']);
		if (strtolower($mRow['document_status_code']) == "complete")
			$mDocCmpId=($mRow['id']);
	}
	if ($mDocCmpId == null)
		$mDocCmpId = 1;
	
	$mDeliveryNote = $mParms['deliverynote'];
// Receipt_header
//	if (($mParms['deliverynote'] == null) || ($mParms['deliverynote'] == ""))
//	{
		$mDataQuery = "SELECT receipt_header.id AS id, receipt_header.document_reference AS document_reference, receipt_header.delivery_note AS delivery_note, CASE WHEN receipt_header.expected_delivery_date IS null THEN receipt_header.date_created ELSE receipt_header.expected_delivery_date END AS expected_delivery_date, receipt_header.customer_reference AS customer_reference FROM receipt_header JOIN receipt_body ON receipt_body.receipt_header_id=receipt_header.id WHERE receipt_header.haulier_reference='".($mParms['haulierreference'])."' AND receipt_body.part_number = '".($mParms['partnumber'])."' AND receipt_header.document_status_id IN(".$mDocStsId.",".$mDocCmpId.") LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		if (mysqli_num_rows($mData) == 0)
		{
			$mDataQuery = "SELECT receipt_header.id AS id, receipt_header.document_reference AS document_reference, receipt_header.customer_reference AS customer_reference, receipt_header.delivery_note AS delivery_note, CASE WHEN receipt_header.expected_delivery_date IS null THEN receipt_header.date_created ELSE receipt_header.expected_delivery_date END AS expected_delivery_date, receipt_header.customer_reference AS customer_reference FROM receipt_header WHERE receipt_header.haulier_reference='".($mParms['haulierreference'])."' AND receipt_header.document_status_id IN(".$mDocStsId.",".$mDocCmpId.") LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			$mData = mysqli_query($mConnection,$mDataQuery);
		}
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDocumentHdrId = ($mRow['id']);
			$mCustomerReference = ($mRow['customer_reference']);
			$mDeliveryNote = ($mRow['delivery_note']);
			$mExpectedDelivery = ($mRow['expected_delivery_date']);
		}
		if ($mDocumentHdrId == "")
		{
//			$mReturn = "FAIL-Invalid Haulier Reference.";
			$mReturn = "OK  -Invalid Haulier Reference.";
			return $mReturn;
		}
//	}
//	else
//	{
//		$mDeliveryNote = $mParms['deliverynote'];
//	}
// Document_Header
	if (($mDocumentReferenceCode != null) && ($mDocumentReferenceCode != ""))
	{
		$mDataQuery = "SELECT id,document_reference_code,requirement_header_id FROM document_header WHERE document_reference_code='".$mDocumentReferenceCode."' LIMIT 1";
	}
	else
	{
//		$mDataQuery = "SELECT id,document_reference_code,requirement_header_id FROM document_header WHERE document_type='".$mTranDocumentType."' AND customer_reference_code='".$mDeliveryNote."' AND complete <> 1 LIMIT 1";
		$mDataQuery = "SELECT id,document_reference_code,requirement_header_id FROM document_header WHERE document_type='".$mTranDocumentType."' AND lifex='".$mDeliveryNote."' AND complete <> 1 LIMIT 1";
	}
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	$mData = mysqli_query($mConnection,$mDataQuery);
	$mDocumentReferenceCode = "";
	$mDocumentHdrId = "";

	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$mDocumentHdrId = ($mRow['id']);
		$mDocumentReferenceCode = ($mRow['document_reference_code']);
	}
	if (($mDocumentHdrId == "") || ($mDeliveryNote == ""))
	{
		$mDataQuery = "UPDATE document_register SET version=version+1,last_updated=now(),last_updated_by='".$mParms['currentuser']."',register_value=register_value+1 WHERE document_type='Customer Receipt' LIMIT 1";
		mysqli_query($mConnection,$mDataQuery);
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mDataQuery = "SELECT register_value FROM document_register WHERE document_type='Customer Receipt' LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mDocumentReferenceCode = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{					
			$mDocumentReferenceCode = $mDocumentReferencePrefix.($mRow['register_value']);
		}
		if ($mDocumentReferenceCode == "")
			$mDocumentReferenceCode = $mDocumentReferencePrefix."999999";
		if ($mExpectedDelivery == null)
			$mDataQuery = "INSERT INTO document_header (id, version, active, auto_confirm, auto_created, complete, date_created, document_reference_code, document_type, last_updated, last_updated_by, vendor_id, class, expected_delivery, document_disposition_id, inspection_required, qa_type, time_slot, stillage, work_station, despatched, marshalled, picked, parent_id, customer_order_id, bwlvs, lgnum, pldat, plzei, tanum, zpind, customer_reference_code, lifex, requirement_header_id, scheduled_work_id, stock_shortage, allocated, transmitted) VALUES(DEFAULT,0,1,0,1,0,now(),'".$mDocumentReferenceCode."','".$mTranDocumentType."',now(),'".$mParms['currentuser']."',1,'com.vantec.documents.receipts.CustomerReceiptHeader',now(),null,0,null,null,null,null,0,0,0,null,null,null,null,null,null,null,null,'".$mCustomerReference."','".$mDeliveryNote."',null,null,0,null,null)";
		else
			$mDataQuery = "INSERT INTO document_header (id, version, active, auto_confirm, auto_created, complete, date_created, document_reference_code, document_type, last_updated, last_updated_by, vendor_id, class, expected_delivery, document_disposition_id, inspection_required, qa_type, time_slot, stillage, work_station, despatched, marshalled, picked, parent_id, customer_order_id, bwlvs, lgnum, pldat, plzei, tanum, zpind, customer_reference_code, lifex, requirement_header_id, scheduled_work_id, stock_shortage, allocated, transmitted) VALUES(DEFAULT,0,1,0,1,0,now(),'".$mDocumentReferenceCode."','".$mTranDocumentType."',now(),'".$mParms['currentuser']."',1,'com.vantec.documents.receipts.CustomerReceiptHeader','".$mExpectedDelivery."',null,0,null,null,null,null,0,0,0,null,null,null,null,null,null,null,null,'".$mCustomerReference."','".$mDeliveryNote."',null,null,0,null,null)";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
//		return "FAIL-TEST1".mysqli_error($mConnection)."-".$mDataQuery;
		$mDataQuery = "SELECT id FROM document_header WHERE document_reference_code='".$mDocumentReferenceCode."' AND complete <> 1 LIMIT 1";
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mDocumentHdrId = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDocumentHdrId = ($mRow['id']);
		}
	}
	else
	{
		$mDataQuery = "UPDATE document_header SET last_updated=now(), last_updated_by='".$mParms['currentuser']."' WHERE id=".$mDocumentHdrId;
		mysqli_query($mConnection,$mDataQuery);
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
	}
	
	if ($mDocumentHdrId != "")
	{
// Part Id
		$mDataQuery = "SELECT id AS id, conversion_factor AS conversion_factor FROM part WHERE part_number LIKE('%".$mParms['partnumber']."%') LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mPartId = "";
		$mPartConvFactor = 1;
		if (mysqli_num_rows($mData) > 0)
		{
			$mRow = mysqli_fetch_assoc($mData);
			$mPartId = ($mRow['id']);
			$mPartConvFactor = ($mRow['conversion_factor']);
		}
// Transaction Qty
		$mQty = 0;
		$mTxnQty = 0;
		if ($mParms['quantity'])
		{
			$mQty = $mParms['quantity']*$mPartConvFactor;
			$mTxnQty = $mParms['quantity'];
		}
		else
		{
			$mQty = 1*$mPartConvFactor;
			$mTxnQty = 1;
		}
// Document_body
		$mDataQuery = "SELECT id AS id FROM document_body WHERE document_header_id=".$mDocumentHdrId." AND part_id=".$mPartId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($mConnection,$mDataQuery);
		$mDocumentBodyId = "";
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$mDocumentBodyId = ($mRow['id']);
		}
		
		if ($mDocumentBodyId == "")
		{		
			$mDataQuery = "INSERT INTO document_body (id,version,date_created,document_header_id,external_reference,last_updated,last_updated_by,part_id,qty_expected,qty_transacted,class,alpha_qty_expected,our_line_no,kbnkz, kqvnq, matnr, nsolm, tapos, vsolm, wdatu, wepos, active, qty_allocated, requirement_body_id,pruf_cube) VALUES(DEFAULT,0,now(),".$mDocumentHdrId.",'',now(),'".$mParms['currentuser']."',".$mPartId.",0,".$mQty.",'com.vantec.documents.receipts.CustomerReceiptBody','0',0,'','','','','','','','',1,0,null,0)";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			$mDocumentBodyId = mysqli_insert_id($mConnection);
		}
		else
		{
			$mDataQuery = "UPDATE document_body SET last_updated=now(),last_updated_by='".$mParms['currentuser']."',qty_transacted=qty_transacted+".$mQty." WHERE id=".$mDocumentBodyId." LIMIT 1";
			mysqli_query($mConnection,$mDataQuery);
		}

// Document_detail
		if ($mDocumentBodyId != "")
		{
			$mDocumentDtlId = "";
			$mDataQuery = "SELECT id AS id FROM document_detail WHERE document_body_id=".$mDocumentBodyId." AND serial_reference='".$mParms['serialreference']."' LIMIT 1";
			if (!mysqli_query($mConnection,$mDataQuery))
				return ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
			$mData = mysqli_query($mConnection,$mDataQuery);
			while($mRow = mysqli_fetch_assoc($mData)) 
			{
				$mDocumentDtlId = ($mRow['id']);
			}
			if ($mDocumentDtlId == "")
			{					
				$mDataQuery = "INSERT INTO document_detail (id, version, date_created, document_body_id, last_updated, last_updated_by, qty_expected, qty_transacted, serial_reference, class, chassis, sequence, trim_sequence, validated, active, kbnkz, kqvnq, matnr, nsolm, our_detail_no, tapos, vsolm, wdatu, wepos, qty_allocated, requirement_detail_id,pruf_cube) VALUES(DEFAULT,0,now(),".$mDocumentBodyId.",now(),'".$mDocumentReferenceCode."',0,".$mQty.",'".$mParms['serialreference']."','com.vantec.documents.receipts.CustomerReceiptDetail','',null,'',0,1,'','','".$mParms['partnumber']."','".$mParms['ranororder']."',0,'','','','',0,null,0)";
				mysqli_query($mConnection,$mDataQuery);
				$mDocumentDtlId=mysqli_insert_id($mConnection);
			}
			else
			{
				$mDataQuery = "UPDATE document_detail SET qty_transacted=qty_transacted+".$mQty.", last_updated=now(), last_updated_by='".$mDocumentReferenceCode."' WHERE id=".$mDocumentDtlId." LIMIT 1";
				if (!mysqli_query($mConnection,$mDataQuery))
					return ("FAIL-".mysqli_error($mConnection)."-".$mDataQuery);
			}
		}
	}
}

// Update Document Header, Body, Detail
function documentToReceipt($mHaulierReference,$mCurrentUser,$mHost,$mUser,$mPwd,$mDbName)
{
	$mReturn = "";
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName);
	if (!$mConnection)
		return("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
	
	$mDocumentReferenceCode = "";
// Get id for document status
	$mDocStsId = 0;
	$mDocCmpId = 0;
	$mDataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
	while($mRow = mysqli_fetch_assoc($mData))
	{
		if (strtolower($mRow['document_status_code']) == "open")
			$mDocStsId=($mRow['id']);
		if (strtolower($mRow['document_status_code']) == "complete")
			$mDocCmpId=($mRow['id']);
	}
	if ($mDocCmpId == null)
		$mDocCmpId = 1;

// Test for Existing Receipt Header
	$mDataQuery = "SELECT id FROM receipt_header WHERE haulier_reference = '".$mHaulierReference."' AND document_status_id in(".$mDocStsId.",".$mDocCmpId.") LIMIT 1";
	if (!mysqli_query($mConnection,$mDataQuery))
		return "FAIL-".mysqli_error($mConnection).$mDataQuery;
	$mData = (mysqli_query($mConnection,$mDataQuery));
//	return "FAIL-".mysqli_error($mConnection).$mDataQuery."-".mysqli_num_rows($mData);
	if (mysqli_num_rows($mData) > 0)
		return ("OK  -receipt_header");
	
	$mDocumentRef = "";
	$mHdrQuery = "SELECT id AS id, customer_reference_code AS delivery_note, expected_delivery AS expected_delivery, jis_receipt AS jis_receipt, vendor_id AS vendor_id FROM document_header WHERE document_reference_code='".$mHaulierReference."' LIMIT 1";
	if (!mysqli_query($mConnection,$mHdrQuery))
		return "FAIL-".mysqli_error($mConnection).$mHdrQuery;
	$mHdrData = (mysqli_query($mConnection,$mHdrQuery));
	if (mysqli_num_rows($mHdrData) == 0)
		return "OK  -doctorec";
	$mRow = mysqli_fetch_assoc($mHdrData);
	
	$mHdrQuery = "SELECT id AS id, customer_reference_code AS customer_reference,lifex AS delivery_note, CASE WHEN expected_delivery IS null THEN date_created ELSE expected_delivery END AS expected_delivery, CASE WHEN jis_receipt =1 THEN substr(vehicle_reference,1,1) ELSE '' END AS jis_ind, vendor_id AS vendor_id FROM document_header WHERE id=".$mRow['id']." OR parent_id=".$mRow['id'];
	if (!mysqli_query($mConnection,$mHdrQuery))
		return "FAIL-".mysqli_error($mConnection).$mHdrQuery;
	$mHdrData = (mysqli_query($mConnection,$mHdrQuery));
	if (mysqli_num_rows($mHdrData) == 0)
		return "OK  -doctorec";
	
	while($mRow = mysqli_fetch_assoc($mHdrData))
	{
		$mDocHdrId = $mRow['id'];
	
// Create Receipt Header
		$mDataQuery = "INSERT INTO receipt_header (id,document_reference,haulier_reference,delivery_note,vendor_id,customer_reference,expected_delivery_date,document_status_id,date_created,created_by,last_updated_date,last_updated_by,jis_ind)";
		$mDataQuery .= " VALUES(DEFAULT,'RCP000000','".$mHaulierReference."','".$mRow['delivery_note']."',".$mRow['vendor_id'].",'".$mRow['customer_reference']."','".$mRow['expected_delivery']."',".$mDocStsId.",now(),'".$mCurrentUser."',now(),'".$mCurrentUser."','".$mRow['jis_ind']."')";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection).$mDataQuery;
		
		$mRctHdrId=mysqli_insert_id($mConnection);
		$mDocReference = "000000".round($mRctHdrId);
		$mDocReference = "RCP".substr($mDocReference,-6);
		$mDataQuery = "UPDATE receipt_header SET document_reference='".$mDocReference."' WHERE id=".$mRctHdrId." LIMIT 1";
		if (!mysqli_query($mConnection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection).$mDataQuery;

// Create Receipt Body
		$mBdyQuery = "SELECT document_body.id AS id, document_body.part_id AS part_id, document_body.qty_expected AS qty_expected, document_body.qty_transacted AS qty_transacted,CASE WHEN document_body.posnr IS null THEN 0 ELSE document_body.posnr END AS our_line_no, part.part_number FROM document_body JOIN part ON document_body.part_id=part.id WHERE document_header_id =".$mDocHdrId;
		if (!mysqli_query($mConnection,$mBdyQuery))
			return "FAIL-".mysqli_error($mConnection).$mBdyQuery;
		$mBdyData = (mysqli_query($mConnection,$mBdyQuery));
//		return "FAIL-".mysqli_error($mConnection).$mBdyQuery."-".mysqli_num_rows($mBdyData);
		while($mBdyRow = mysqli_fetch_assoc($mBdyData)) 
		{
			$mDocBdyId = $mBdyRow['id'];
			$mDataQuery = "INSERT INTO receipt_body (id,document_reference,receipt_header_id,part_id,part_number,advised_qty,received_qty,difference,document_status_id,date_created,created_by,last_updated_date,last_updated_by,line_no)";
			$mDataQuery .= "VALUES(DEFAULT,'".$mDocReference."',".$mRctHdrId.",".$mBdyRow['part_id'].",'".$mBdyRow['part_number']."',".$mBdyRow['qty_expected'].",".$mBdyRow['qty_transacted'].",".($mBdyRow['qty_transacted']-$mBdyRow['qty_expected']).",".$mDocStsId.",now(),'".$mCurrentUser."',now(),'".$mCurrentUser."',".$mBdyRow['our_line_no'].")";
			if (!mysqli_query($mConnection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection).$mDataQuery;
			$mRctBdyId=mysqli_insert_id($mConnection);
//			return "FAIL-".mysqli_error($mConnection).$mDataQuery."-".mysqli_num_rows($mDataQuery);

// Create Receipt Detail
			$mDtlQuery = "SELECT document_detail.id AS id, document_detail.serial_reference AS serial_reference,document_detail.qty_expected AS qty_expected,document_detail.qty_transacted AS qty_transacted FROM document_detail WHERE document_body_id=".$mDocBdyId;
			if (!mysqli_query($mConnection,$mDtlQuery))
				return "FAIL-".mysqli_error($mConnection).$mDtlQuery;
			$mDtlData = (mysqli_query($mConnection,$mDtlQuery));
//			return "FAIL-".mysqli_error($mConnection).$mDtlQuery."-".mysqli_num_rows($mDtlData);
			while($mDtlRow = mysqli_fetch_assoc($mDtlData))	
			{
				$mDataQuery = "INSERT INTO receipt_detail (id,document_reference,receipt_body_id,part_id,part_number,serial_reference,advised_qty,received_qty,difference,document_status_id,date_created,created_by,last_updated_date,last_updated_by)";
				$mDataQuery .= "VALUES(DEFAULT,'".$mDocReference."',".$mRctBdyId.",".$mBdyRow['part_id'].",'".$mBdyRow['part_number']."','".$mDtlRow['serial_reference']."',".$mDtlRow['qty_expected'].",".$mDtlRow['qty_transacted'].",".($mDtlRow['qty_transacted']-$mDtlRow['qty_expected']).",".$mDocStsId.",now(),'".$mCurrentUser."',now(),'".$mCurrentUser."')";
				if (!mysqli_query($mConnection,$mDataQuery))
					return "FAIL-".mysqli_error($mConnection).$mDataQuery;
				$mRctDtlId=mysqli_insert_id($mConnection);
			}
		}
	}	
	return "OK  -doctorec";
}

?>