<?php
/*-- Business Logic Class.	App:scm@solarexplosives.com Company:softwareCREATIONS Author:dashgandhi@yahoo.com Contact:+919819603482	*/

class BusiLogic {

const BaseApp			= 'scm';					// 'b2b' | 'scm'
const DBNameTrade		= 'b2b_solar_sc';		// Should keep in sink in b2b.index.php, b2b.class.BusiLogic & b2b.ajax.php 
const DBNameNonTrade	= 'cib2b_solar_sc';
const DEFCOMPANYCD		= 1800;
// PartyTypes
const	PTCompany		= 1;
const	PTTransporter	= 2;
const	PTCustomer		= 4;
const 	PTSecurityAgency = 201;			

const	SectorCdOverall	= 'A';			// Overall SectorCd
const	LUFLoading		= 1;			// LUF: LoadUnloadCd Values
const 	LUFUnloading	= 2;
const	LUFUnloadNLoading	= 4;
const	LUFCompanyVanEmpty	= 8;
const	UTCustomer	= 11;		// UT:UserType. Customer. scm_user_type_m.UserType for ssm_user_type_m.IsCustomer

const	PlantCdChakdoh	= 1;
const	PlantCdSawanga	= 2;

// vm_van_status_m.VanStatusCd
const	VSWaitParking		= 1;
const	VSWaitLoading		= 2;
const	VSLoading			= 3;
const	VSLoaded			= 4;
const	VSDespatched		= 9;
const	VSInCompanyVanArea	= 21;		// CompanyVan in CompanyVanArea
const	VSCancelled			= 99;

private		$objDBQuery;
private		$dbName;
protected	$lastInsertTranNo;
	
public function __construct($objDBQuery, $dbName) {
	$this->objDBQuery			= $objDBQuery;
	$this->dbName				= $dbName;
	$this->lastInsertTranNo		= 0;
	date_default_timezone_set('Asia/Kolkata');
}
	
public function execUpdateQuery($updateQuery) {
	return $this->objDBQuery->execUpdateQuery($updateQuery);
}

public function execSelectQuery($execQuery, $resultType=0, &$preResults=NULL) {
	return $this->objDBQuery->execSelectQuery($execQuery, $resultType, $preResults);
}

public function manageTransaction($SetRollbackCommit) {
	$this->objDBQuery->manageTransaction($SetRollbackCommit);
}

public function getInsertId() {
	return $this->objDBQuery->objDBConnect->insert_id;
}

public function getInsertTranNo() {
	return $this->lastInsertTranNo;
}

public function getRelNamedLock($GetLock, $TableRLockName, $ColValArr=NULL) {
	if ($GetLock) {
		$ColValStr = '';
		foreach ($ColValArr as $key => $value) {			// while (list($key, $value) = each($ColValArr))
			$ColValStr .= $key . ':' . $value . ',';
		}
		$NamedLock	= $this->dbName . '@' . $TableRLockName . '-' . $ColValStr;
		$Success = 0;
		while (!$Success) {
			$sql		= "SELECT GET_LOCK('$NamedLock', 10) AS Success";
			$SuccessArr = $this->execSelectQuery($sql);
			$Success	= $SuccessArr[0]['Success'];
		}
		return $NamedLock;
	} else {
		$sql		= "SELECT RELEASE_LOCK('$TableRLockName') AS Success";
		$SuccessArr = $this->execSelectQuery($sql);
		$Success	= $SuccessArr[0]['Success'];
		return (isset($Success) && $Success) ? '' : 'Release Lock: '. $TableRLockName .' Failure!';
	}
}

// General Functions -------------------------------------------------------------------------------------------------------------------------------------

public function strPositSubstitute($strSource, $para1DArray = NULL, $para2DArray = NULL) {
/*
Substitute source string containing </./> tag delimited positional paras with positional values specified in $para1DArray & $para2DArray
$para1DArray is 1 dimensional array for </0/>, </1/>, ... 
$para2DArray is 2 dimensional array for </00/>, </01/>, .... Source is expected to include \r\n at end of the this positional para row
Routine should treat these positionval values as 1st Row of a table & replace with as many times as no. of rows in $para2DArray

$strSource = 'Dear sir, Please note that your P.O. No. </0/> dated </1/> Is approved by us with Token No. </2/>' . "\r\n";
$strSource .= 'Rates of following Items have been changed:' . "\r\n";
$strSource .= 'Item			OldRate			NewRate' . "\r\n";
$strSource .= '</00/>			</01/>			</02/>' . "\r\n";
$strSource .= 'Please do check in b2b too.' . "\r\n";
$para1DArray = array('123456','10/12/2012','T12234');
$para2DArray = array(	array('Item1','OldRate','NewRate'),	array('Item2','OldRate','NewRate'),	array('Item3','OldRate','NewRate')	);
$fstr = $objBusiLogic->strPositSubstitute($strSource, $para1DArray ,$para2DArray );
*/
	if (isset($para1DArray) && is_array($para1DArray)) {
		$arrSearch = array();
		for ($paraCur=0; $paraCur <= count($para1DArray); ++$paraCur) {
			$arrSearch[$paraCur] = '</' . $paraCur . '/>';
		}
		$strSource = str_replace($arrSearch, $para1DArray, $strSource);
	}

	if (isset($para2DArray) && is_array($para2DArray)) {
		$countRow = count($para2DArray);
		$countCol = count($para2DArray[0]);
		$valRowBeg = strpos($strSource,'</00/>');
		$lastPosPara = '</0' . (string)($countCol-1) . '/>';
		$valRowEnd = strpos($strSource, $lastPosPara) + strlen($lastPosPara);
		$strPositional = substr($strSource, $valRowBeg, $valRowEnd-$valRowBeg);
		$strSourceEnd = substr($strSource, $valRowEnd);
		$strSource = substr($strSource, 0, $valRowBeg);
		
		$arrSearch = array();
		for ($paraCur=0; $paraCur < $countCol; ++$paraCur) {
			$arrSearch[$paraCur] = '</0' . $paraCur . '/>';
		}
		for ($curRow=0; $curRow<$countRow; ++$curRow) {
			$strSource .= str_replace($arrSearch, $para2DArray[$curRow], $strPositional) . ($curRow < $countRow-1 ? "\r\n" : '');
		}
		$strSource .= $strSourceEnd;
	}
	return $strSource;
}

public function convertDate($dateString, $brit2Ansi, $bmDateTimePart=3) {		// $bmDateTimePart: 1:Date, 2:Time, 3:Both
	if ($brit2Ansi) {		// British (dd-mm-yyyy) to ANSI (yyyy-mm-dd)
		$returnVal = preg_replace('/(\d{2})[-\/](\d{2})[-\/](\d{4})/', "$3-$2-$1", $dateString);
	} else {				// ANSI to British
		$returnVal = (substr($dateString,0,4)=="0000" ? "" : preg_replace('/(\d{4})[-\/](\d{2})[-\/](\d{2})/', "$3-$2-$1", $dateString));
	}

	if ($bmDateTimePart == 3) {
		return $returnVal;
	} elseif (($bmDateTimePart & 1) == 1) {
		return substr($returnVal,0,10);
	} else {
		return substr($returnVal,11,8);
	}
}

public function addDate($date2Add = NULL, $days2Add, $returnBritish = TRUE) {
// $date2Add: Null:Today. Must be ANSIDate.
// $days2Add: A.Complete expression eg '+1 day', '-1 day', '+1 week 2 days 4 hours 2 seconds'. ('next Thursday':will NOT work) B.Just No. of Days to ADD
	$date2Add = isset($date2Add) ? $date2Add : 'now';
	$days2Add = in_array(substr($days2Add, 0, 1), array('+','-')) ? $days2Add : '+ ' . $days2Add .' day';
	$date2Add = strtotime($days2Add, strtotime($date2Add));
	return date(($returnBritish ? 'd-m-Y' : 'Y-m-d'), $date2Add);
}

public function getUniqueFileName($fileFolder, $prefix, $extension) {
	while (true) {
		$fileName = $fileFolder . uniqid($prefix, true) . $extension;
		if (!file_exists($fileName))
			break;
	}
	return ($fileName);
}

public function array_sum_key($arr, $index = NULL) {
	if (!is_array($arr) || count($arr) < 1) {
		return 0;
	}
	$ret = 0;
	foreach ($arr as $id => $data) {
		if (isset($index)) {
			$ret += (isset($data[$index])) ? $data[$index] : 0;
		} else {
			$ret += $data;
		}
	}
	return $ret;
}

public function searchSubArray(Array $array, $key, $value) {
    foreach ($array as $subArray) {
        if (isset($subArray[$key]) && $subArray[$key] == $value)
          return $subArray;
    }
	return FALSE;
}

public function fillMasterDropDown($keyCd, $arrCodeMDisp, $showPlSelect, $curValue, $colDescript=NULL, $dataColName=NULL) {
	$colDescript	= isset($colDescript) ? $colDescript : 'Descript';
	$isSelected	= FALSE;
	$selected	= $htmlStr = $dataStr = '';
	if ($showPlSelect) {
		$selected = "selected='selected'";
		$htmlStr .= "<option value='' " . $selected . "> " . 'Please Select' . "</option>";
	}
	foreach ($arrCodeMDisp as $mData) {
		$selected = "";
		if ($curValue==$mData[$keyCd]) {
			$selected = "selected='selected'";
			$isSelected = TRUE;
		}
		$dataStr = $this->fillMasterDropDownDataCol($dataColName, $mData);		// isset($dataColName) ? "data-$dataColName='{$mData[$dataColName]}' " : ""
		$htmlStr .= "<option $dataStr value='{$mData[$keyCd]}' $selected > {$mData[$colDescript]} </option>";
	}
	if (!$isSelected && $curValue!='') {
		$selected = "selected='selected'";
		$dataStr = $this->fillMasterDropDownDataCol($dataColName, NULL);	// isset($dataColName) ? "data-$dataColName='' " : ""
		$htmlStr .= "<option $dataStr value='{$curValue}' $selected > {$curValue} Missing </option>";
	}

	return $htmlStr;
}

public function fillMasterDropDownDataCol($dataColNameArr, $mData=NULL) {
	$dataStr	= '';
	if (isset($dataColNameArr)) {
		if ((array)($dataColNameArr) !== $dataColNameArr) {		// !is_array($dataColNameArr)
			$dataColNameArr = array($dataColNameArr);
		}
		$max	= count($dataColNameArr);
		for ($j=0; $j<$max; $j++) {
			$dataColName	= $dataColNameArr[$j];
			$dataColVal		= '';
			if (isset($mData)) {
				$dataColVal	=  $mData[$dataColName];
			}
			$dataStr .= "data-$dataColName='$dataColVal' " ;
		}
	}
	return $dataStr;
}

public function makeTabReport($reportData, $repPartBitmap=3,  $fileName='', $handle=-1, $repTitle='', $hasSerial=FALSE, $breakFlds=0, $sumFlds='') {
// $reportData: Must be Numeric+Associative Array ie execSelectQuery($execQuery, $resultType=2)
// $repPartBitmap: 1:FirstReport, 2:LastReport. $fileName is must for FirstReport &| LastReport. $handle is set for FirstReport & must for Not-FirstReport
// $repTitle: If '', consider the report as GrandTotal of Detail Report & do not print ColumnHeader, skip a line & also skip SrNo column, if $hasSerial

	$isFirstReport = ($repPartBitmap & 1) == 1;
	$isLastReport = ($repPartBitmap & 2) == 2;
	if ($isFirstReport) $handle = fopen($fileName,'wb');
	$isDetailReport = $repTitle!='';

	$arrayKeys = array_keys($reportData[0]);
	$tildeCount =0;
	foreach ($arrayKeys as $keyValue) {
		$tildeCount = max($tildeCount, substr_count($keyValue, '~'));
	}
		
	if ($isDetailReport) {
		if (!$isFirstReport) 	fwrite($handle, "\r\n\r\n");	// leave 2 lines for any subsequent Detail report
		fwrite($handle, $repTitle . "\r\n");
	}
	fwrite($handle, "\r\n");

	if ($isDetailReport) {
		for ($tildeCur=0; $tildeCur <= $tildeCount; $tildeCur++) {
			if ($hasSerial)
				fwrite($handle, (($tildeCur==0) ? 'Sr.' : "") . "\t");
			for ($i=1; $i<count($arrayKeys); $i+=2) {
				$partArr = explode('~',$arrayKeys[$i]);
//				$separatorAt = strpos($arrayKeys[$i], '~');
				$colVal = (count($partArr) > $tildeCur) ? $partArr[$tildeCur] : "";
				fwrite($handle, $colVal . "\t");
			}
			fwrite($handle, "\r\n");
		}
	}

// array_slice ( array $array , int $offset [, int $length = NULL [, bool $preserve_keys = false ]] )
	$columnCount = count($reportData[0])/2;
	$SrNo = 1;

	for ($i=0; $i<count($reportData); $i++) {
		if ($hasSerial)
			fwrite($handle, ($isDetailReport ? $SrNo : '')."\t");
		if ($i>0 && $breakFlds>0) {
			$curBreakValArr = array_slice($reportData[$i], 0, $breakFlds*2, TRUE);
			$diffArray = array_diff_assoc($preBreakValArr, $curBreakValArr);
		}
		
		for ($j=0; $j<$columnCount; $j++) {
			fwrite($handle, (($i==0 || $j>=$breakFlds || $diffArray) ? $reportData[$i][$j]. "\t" : "\t"));
		}
		fwrite($handle, "\r\n" );
		if ($breakFlds>0)
			$preBreakValArr = array_slice($reportData[$i], 0, $breakFlds*2, TRUE);
		$SrNo += 1;
	}

	if ($isLastReport) {
		fclose($handle);
		echo "<script language='javascript'>window.location.href='includes/download.php?fileName=$fileName&baseDir=./../wrk/';</script>";
		return 0;
	} else return $handle;
}

function reportDownload($reportData, $fileName) {
	$handle = fopen($fileName,'wb');
	for ($curRow=0; $curRow < count($reportData); $curRow++) {
		for ($curColumn=0; $curColumn < count($reportData[$curRow]); $curColumn++ ) {
			fwrite($handle, $reportData[$curRow][$curColumn]. "\t");
		}
		fwrite($handle, "\r\n");
	}
	fclose($handle);
	return 0;
}

// Common Functions used for Display/Select Master+Description in many forms --------------------------------------------------------------------------------

public function pr($data){
	echo "<pre>";
	print_r($data);
	echo "<pre>";
}

public function getUserTypeMDisp() {
	$sql		= "SELECT UserType, Descript FROM scm_user_type_m";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getUserMDisp($strActiveFlag='') {		// $strActiveFlag: Can be , delimited ActiveFlag list to filter
	$sql		= "SELECT UserName, Descript, UserType, CustomerCd, ActiveFlag FROM scm_user_m". ((empty($strActiveFlag)) ? "" : " WHERE ActiveFlag IN (".$strActiveFlag.")");
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getPartyMDisp($PartyTypeCd, $PartyCdAs='PartyCd', $PartyCd=NULL, $filterCond='') {		// $filterCond or $PartyCd. Not both!
	$PartyTypeCd = (int)$PartyTypeCd;
	$listColumns = "PartyCd AS $PartyCdAs,Descript,SectorCd";
	$sql		= "SELECT $listColumns FROM scm_party_m WHERE PartyTypeCd=$PartyTypeCd" . ($filterCond=='' ? "" : " AND " .$filterCond) . (is_null($PartyCd) ? "" : " AND PartyCd='$PartyCd'").  " ORDER BY Descript";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getCustomerMDisp($CustomerCd=NULL, $filterCond='') {
	return $this->getPartyMDisp(self::PTCustomer, 'CustomerCd', $CustomerCd, $filterCond);
}

public function getSecurityAgencyMDisp($SecurityAgencyCd=NULL, $filterCond='') {
	return $this->getPartyMDisp(self::PTSecurityAgency, 'SecurityAgencyCd', $SecurityAgencyCd, $filterCond);
}

public function getPartyTypeMDisp($PartyTypeCd=NULL, $filterCond='') {		// $filterCond or $PartyTypeCd. Not both!
	$sql		= "SELECT * FROM scm_party_type_m " . ($filterCond=='' ? "" : " WHERE " .$filterCond) . (isset($PartyTypeCd) ? " WHERE PartyTypeCd=$PartyTypeCd" : ""). " ORDER BY PartyTypeCd;";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getStateMDisp() {
	$sql		= "SELECT StateCd,Descript FROM scm_state_m ORDER BY Descript";
	$returnData = $this->execSelectQuery($sql);
	return $returnData;	
}
	
public function getCityMDisp($CityCd='') {		// DEFUNCT
	$sql		= "SELECT * FROM scm_city_m " . (isset($CityCd) && $CityCd!='' ? " WHERE CityCd=$CityCd" : " WHERE 1<>1"). " ORDER BY Descript;";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getCityMForState($StateCd) {
	$sql		= "SELECT * FROM scm_city_m WHERE StateCd='$StateCd' ORDER BY Descript;";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getLoadUnloadMDisp($LoadUnloadCd=0) {
	$listColumns = "LoadUnloadCd,Descript";
	$sql		= "SELECT ".$listColumns." FROM vm_load_unload_m " . (($LoadUnloadCd==0) ? "" : " WHERE LoadUnloadCd=$LoadUnloadCd").  " ORDER BY Descript";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getVanMDisp($IdVan, $GateNo, $IsGateIn, $PlantCd) {
	$inputArr = array((int)$IdVan, (int)$GateNo, (int)$IsGateIn, (int)$PlantCd);
	$paraString		= vsprintf("%s, %s, %s, %s", $inputArr);
	$sql			= "call sp_getGateTranVanM(" . $paraString. ")";
/*	if ($IdVan != '' || $GateNo == 1 || $GateNo == 4) {
		$sql		= "SELECT A.IdVan, A.VanNo, A.Descript, A.TransporterTypeCd, A.TransporterName, '' AS DriverLicNo FROM vm_van_m A" 
				. ($IdVan == '' ? '' : " WHERE A.IdVan=$IdVan") . " ORDER BY A.VanNo";
	} else {
		$inputArr 		= array($IsGateIn, $PlantCd);
		$paraString		= vsprintf("%s, %s", $inputArr);
		$sql			= "call sp_getGateTranVanMGate2(" . $paraString. ")";
	}*/
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getVanDriverMDisp($DriverLicNo=NULL) {
	$sql		= "SELECT DriverLicNo, Descript, TransporterTypeCd, DriverLicValidUptoDate, HazardLicValidUptoDate, TelNo
					FROM vm_van_driver_m " . (isset($DriverLicNo) ? " WHERE DriverLicNo='$DriverLicNo'" : ""). " ORDER BY DriverLicNo;";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getRE11StatusMDisp() {
	$sql		= "SELECT RE11StatusCd, Descript, IsReceived FROM vm_re11_status_m ORDER BY Descript;";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getPlantMDisp($PlantCd=0) {
	$sql		= "SELECT * FROM scm_plant_m " . (empty($PlantCd) ? "WHERE PlantCd<>0" : "WHERE PlantCd=$PlantCd") . " ORDER BY Descript;";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getSectorMDisp($SectorCd='', $filterCond='') {		// SectorCd or filterCond. Not both
	$sql		= "SELECT * FROM scm_sector_m WHERE 1=1" . (empty($SectorCd) ? "" : " AND SectorCd='$SectorCd'") 
					. ($filterCond=='' ? "" : " AND " .$filterCond) . " ORDER BY Descript;";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getCompanyMDisp($CompanyCd=NULL) {
	$sql		= "SELECT * FROM scm_company_m" . ((isset($CompanyCd)) ? " WHERE CompanyCd=$CompanyCd" : "") ;
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getVanStatusMDisp($VanStatusCd = NULL) {
	$sql	= "SELECT A.* FROM vm_van_status_m A WHERE " 
		. (isset($VanStatusCd) ? ($VanStatusCd=='' ? "1<>1" : "A.VanStatusCd = $VanStatusCd") : "1=1") . " ORDER BY A.Descript";
	$returnData = $this->execSelectQuery($sql);
	return $returnData;	
}

public function getVehicleMDispComp($SectorCd) {
	$sql		= "SELECT VehicleNo,VehicleNo AS Descript FROM " . ($SectorCd=='T' ? self::DBNameTrade : self::DBNameNonTrade) . ".so_vehicle_m"
				. " WHERE CustomerCd=". self::DEFCOMPANYCD . " OR IsDummyCompanyVan ORDER BY VehicleNo";
	$returnData = $this->execSelectQuery($sql);
	return $returnData;	
}

public function getCompVanStatusMDisp($CompVanStatusCd=NULL) {
	$sql		= "SELECT * FROM vm_compvan_status_m" . ((isset($CompVanStatusCd)) ? " WHERE CompVanStatusCd=$CompVanStatusCd" : "") ;
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getSecurityGuardMDisp($IdSecurityGuard=0) {
	$sql		= "SELECT A.IdSecurityGuard,RPAD(CONCAT(A.Descript,' @ ',B.Descript),65,' ') AS Descript FROM vm_security_guard_m A LEFT OUTER JOIN scm_party_m B" 
					. " ON A.SecurityAgencyCd=B.PartyCd WHERE A.IsActive ". ($IdSecurityGuard==0 ? "" : " AND A.IdSecurityGuard=$IdSecurityGuard") . " ORDER BY A.Descript;";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function getSoItemMDisp() {
	$sql		= "SELECT ItemCd,Descript FROM b2b_solar_sc.so_item_m ORDER BY 2";
	$returnData = $this->execSelectQuery($sql);
	return $returnData;	
}
	
public function getControlData() {		// Gate1CallInValidHr, Gate1CallInValidHr
	$sql = "SELECT * FROM scm_control A";
	$returnData = $this->execSelectQuery($sql);
	return $returnData;
}

function monthName($monthNoLogical, $isFull=FALSE) {
// Return Full/Short Month Name of given Logical Month No.
	return date(($isFull) ? 'F' : 'M', mktime(0, 0, 0, ($monthNoLogical+4)%12-1, 1, 2000));
}

// GateTran -----------------------------------------------------------------------------------------------------------------------------
public function setGateTranConstants($GateDetail, &$GateCd, &$GateNo, &$IsGateIn, &$LoadUnloadCd, &$PlantCd, &$FormName=NULL, &$SubAction=NULL, &$VanIdInputBM=NULL) {
	$GateDetailArr	= explode(',',$GateDetail);
	$GateCd			= $GateDetailArr[0];
	$IsGateIn		= $GateDetailArr[1];
	$LoadUnloadCd	= $GateDetailArr[2];		// 0: Not known. Only available beforehand from Gate-1-In & CompanyArea
	$FormName		= $GateDetailArr[3];
	$SubAction		= "subAction=" . $GateDetail;
	$this->getGateTranControl($GateCd, $GateNo, $PlantCd, $VanIdInputBM);
}

public function getGateTranControl($GateCd, &$GateNo, &$PlantCd, &$VanIdInputBM) {
	$sql = "SELECT GateNo, PlantCd, " . ($_SESSION['IsAdmin'] ? "VanIdInputAdminBM" : "VanIdInputNonAdminBM") . " AS VanIdInputBM FROM vm_gate_m A WHERE A.GateCd=$GateCd";
	$returnData = $this->execSelectQuery($sql);
	$GateNo			= $returnData[0]['GateNo'];
	$PlantCd		= $returnData[0]['PlantCd'];
	$VanIdInputBM	= (int)$returnData[0]['VanIdInputBM'];
	return;
}

public function getGateTranList($search, $ListForMY, $GateDetail) {
	$DTSAnsi		= preg_replace('/(\d{2})[-\/](\d{4})/', "$2-$1", $ListForMY) . '-01';
	$DTEAnsi		= $this->addDate($this->addDate($DTSAnsi, '+1 month', FALSE), '-1 day', FALSE);
	$GateCd = $GateNo = $IsGateIn = $LoadUnloadCd = $FormName = $PlantCd = '';
	$this->setGateTranConstants($GateDetail, $GateCd, $GateNo, $IsGateIn, $LoadUnloadCd, $PlantCd);
	$searchCnd	= ((isset($search)) ? " AND (C.VanNo LIKE '$search%' OR B.Descript LIKE '$search%')" : "") ;
	if( $IsGateIn==0 && ($GateCd=='1' or $GateCd=='11')){ 
		$sql	= "SELECT E.PONo, A.ForGuardPhotoVerify, A.GateTranNo, DATE_FORMAT(A.DateTime,'%d-%m-%Y %H:%i:%S') AS DateTime, C.VanNo, A.DriverLicNo
			,A.DriverLicNo, IFNULL(xf_Proper(B.Descript),A.DriverLicNo) AS DriverName, A.VanReportingNo, B.TelNo
			,LEFT(xf_Proper(C.TransporterName),35) AS TransporterName, D.ShortDesc AS Sector
			FROM vm_gate_tran A LEFT OUTER JOIN vm_van_driver_m B ON A.DriverLicNo=B.DriverLicNo
			LEFT OUTER JOIN vm_van_m C ON A.IdVan=C.IdVan
			LEFT OUTER JOIN scm_sector_m D ON A.SectorCd=D.SectorCd
			INNER JOIN  vm_gate_tran_customer E ON A.GateCd=E.GateCd and A.IsGateIn = E.IsGateIn and A.GateTranNo= E.GateTranNo 
			WHERE A.GateCd=$GateCd AND A.IsGateIn=$IsGateIn" . $searchCnd . " AND DATE_FORMAT(A.DateTime,'%Y-%m-%d') BETWEEN '$DTSAnsi' AND '$DTEAnsi'";
	} else{
	$sql	= "SELECT A.GateTranNo, A.ForGuardPhotoVerify, DATE_FORMAT(A.DateTime,'%d-%m-%Y %H:%i:%S') AS DateTime, C.VanNo, A.DriverLicNo
			,A.DriverLicNo, IFNULL(xf_Proper(B.Descript),A.DriverLicNo) AS DriverName, A.VanReportingNo, B.TelNo
			,LEFT(xf_Proper(C.TransporterName),35) AS TransporterName, D.ShortDesc AS Sector
			FROM vm_gate_tran A LEFT OUTER JOIN vm_van_driver_m B ON A.DriverLicNo=B.DriverLicNo
			LEFT OUTER JOIN vm_van_m C ON A.IdVan=C.IdVan
			LEFT OUTER JOIN scm_sector_m D ON A.SectorCd=D.SectorCd
			WHERE GateCd=$GateCd AND IsGateIn=$IsGateIn" . $searchCnd . " AND DATE_FORMAT(A.DateTime,'%Y-%m-%d') BETWEEN '$DTSAnsi' AND '$DTEAnsi'";
		}
	$sql .=  ($LoadUnloadCd==0 ? "" : " AND LoadUnloadCd=$LoadUnloadCd") ." ORDER BY A.GateTranNo DESC";
	$returnData = $this->execSelectQuery($sql);
	return $returnData;
}

public function insertGateTran($data, &$VanReportingNo) {
	$sql	= "SELECT IFNULL(MAX(GateTranNo),0) AS GateTranNoLast FROM vm_gate_tran WHERE GateCd={$data['GateCd']} AND IsGateIn={$data['IsGateIn']}";
	$returnData	= $this->execSelectQuery($sql);
	$this->lastInsertTranNo	= $returnData[0]['GateTranNoLast']+1;
	$VanReportingNo	= (int)$data['VanReportingNo'];
	$HasVanReportingNo = (int)$data['HasVanReportingNo'];
	if ($VanReportingNo==0 && $HasVanReportingNo==1) {
		$sql	= "SELECT IFNULL(MAX(VanReportingNo),0) AS VanReportingNo FROM vm_gate_tran WHERE PlantCd={$data['PlantCd']}";
		$returnData	= $this->execSelectQuery($sql);
		$VanReportingNo	= $returnData[0]['VanReportingNo']+1;
	}
	
	$data['SectorCd'] = trim($data['SectorCd'])=='' ? self::SectorCdOverall : $data['SectorCd'];
	
	$stmt	= "INSERT INTO vm_gate_tran(GateCd, IsGateIn, GateTranNo, DateTime, LoadUnloadCd, GateNo, PlantCd, IdVan, DriverLicNo
		, VanReportingNo, TransporterTypeCd, SectorCd, Gate1DateTimeLimit, Gate1CallCount, VanStatusCd, Remark, WarningChecks, TareWt, GrossWt
		, TransUserName, TransDateTime, IdSecurityGuard) 
		VALUES (%s, %s, %s, '%s', %s, %s, %s, %s, '%s', %s, %s, '%s', '%s', %s, %s, '%s', '%s', %s, %s, '%s', '%s', %s)";
	$inputArr	= array((int)$data['GateCd'], (int)$data['IsGateIn'], $this->lastInsertTranNo, $this->convertDate($data['DateTime'],TRUE)
		, (int)$data['LoadUnloadCd'], (int)$data['GateNo'], (int)$data['PlantCd'], $data['IdVan'], $data['DriverLicNo'], $VanReportingNo
		, (int)$data['TransporterTypeCd'], $data['SectorCd'], $this->convertDate($data['Gate1DateTimeLimit'],TRUE)
		, (int)$data['Gate1CallCount'], (int)$data['VanStatusCd'], $data['Remark'], $data['WarningChecks'], (float)$data['TareWt'], (float)$data['GrossWt']
		, $_SESSION['UserName'], date('Y-m-d H:i:s'), (int)$data['IdSecurityGuard']);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->execUpdateQuery($sql);
	if (!$returnData && isset($data['DriverTelNo']) && $data['DriverTelNo'] != '') {
		$sql = "UPDATE vm_van_driver_m SET TelNo='{$data['DriverTelNo']}' WHERE DriverLicNo='{$data['DriverLicNo']}'";
		$this->execUpdateQuery($sql);
	}
	return $returnData;
}

public function updateGateTran($data) {
//	$oriDataArr = $this->getGateTranHeader((int)$data['GateCd'], (int)$data['IsGateIn'], (int)$data['GateTranNo']);
	$stmt	= "UPDATE vm_gate_tran SET DateTime='%s', LoadUnloadCd=%s, GateNo=%s, PlantCd=%s, IdVan=%s, DriverLicNo='%s', VanReportingNo=%s
		, TransporterTypeCd=%s, SectorCd='%s', Gate1DateTimeLimit='%s', Gate1CallCount=%s, VanStatusCd=%s, Remark='%s', WarningChecks='%s'
		, TareWt=%s, GrossWt=%s, TransUserName='%s', TransDateTime='%s', IdSecurityGuard=%s WHERE GateCd=%s AND IsGateIn=%s AND GateTranNo=%s";
	$inputArr	= array( $this->convertDate($data['DateTime'],TRUE)
		, (int)$data['LoadUnloadCd'], (int)$data['GateNo'], (int)$data['PlantCd'], $data['IdVan'], $data['DriverLicNo'], (int)$data['VanReportingNo']
		, (int)$data['TransporterTypeCd'], $data['SectorCd'], $this->convertDate($data['Gate1DateTimeLimit'],TRUE)
		, (int)$data['Gate1CallCount'], (int)$data['VanStatusCd'], $data['Remark'], $data['WarningChecks'], (float)$data['TareWt'], (float)$data['GrossWt']
		, $_SESSION['UserName'], date('Y-m-d H:i:s'), (int)$data['IdSecurityGuard'], (int)$data['GateCd'], (int)$data['IsGateIn'], (int)$data['GateTranNo']);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->execUpdateQuery($sql);
	return $returnData;
}

public function getGateTranCustomerInsertQuery() {
	return ("INSERT INTO vm_gate_tran_customer (GateCd, IsGateIn, GateTranNo, CustomerCd, SectorCd, ContractNo, PONo
		, TokenNoPrefix, TokenNo, RE11No, RE11Date, RE11StatusCd, IsRE11Received, SAPSONo) 
		VALUES (%s, %s, %s, '%s', '%s', %s, %s, '%s', %s, '%s', '%s', %s, %s, '%s')");
}

public function getGateTranCustomerUpdateQuery() {
	return ("UPDATE vm_gate_tran_customer SET CustomerCd='%s', SectorCd='%s', ContractNo=%s, PONo=%s, TokenNoPrefix='%s', TokenNo=%s
		, RE11No='%s', RE11Date='%s', RE11StatusCd=%s, IsRE11Received=%s, SAPSONo='%s' WHERE UniqueId=%s");
}

public function getGateTranCustomerDeleteQuery() {
	return ("DELETE FROM vm_gate_tran_customer WHERE UniqueId=%s");
}

public function insertGateTranCustomer($data) {
	$returnData = array();
	$return = array();
	$queryInsert	= $this->getGateTranCustomerInsertQuery();

	for($curRowNo=1; $curRowNo<=$data['LastRowNo1']; $curRowNo++) {
		if (!isset($data['CustomerCd1_'.$curRowNo]) || trim($data['CustomerCd1_'.$curRowNo])=='') {
			continue;
		}	
		$inputArr	= array((int)$data['GateCd'], (int)$data['IsGateIn'], (int)$data['GateTranNo'], $data['CustomerCd1_'.$curRowNo]
			, $data['SectorCd1_'.$curRowNo], (int)$data['ContractNo1_'.$curRowNo], (int)$data['PONo1_'.$curRowNo], $data['TokenNoPrefix1_'.$curRowNo], (int)$data['TokenNo1_'.$curRowNo]
			, $data['RE11No1_'.$curRowNo], $this->convertDate($data['RE11Date1_'.$curRowNo],TRUE), (int)$data['RE11StatusCd1_'.$curRowNo]
			, (int)$data['IsRE11Received1_'.$curRowNo], $data['SAPSONo1_'.$curRowNo]);
		$sql		= vsprintf($queryInsert, $inputArr);
		$returnData	= $this->execUpdateQuery($sql);
		if(((int)$data['GateCd']=='1'  || (int)$data['GateCd']=='11') &&  (int)$data['IsGateIn'] =='0' && $data['VanStatusCd'] =='9'){
			$return	= $this->sendEMailVanDespatched($data['CustomerCd1_'.$curRowNo], (int)$data['PONo1_'.$curRowNo], $data['TokenNoPrefix1_'.$curRowNo], (int)$data['TokenNo1_'.$curRowNo], $data['IdVan']);
		}
	if($returnData){
			return $returnData;
		}
	}
	return $returnData;
}

public function sendEMailVanDespatched($CustomerCd, $PONo, $TokenNoPrefix, $TokenNo, $IdVan) {
	$sql = "SELECT A.EMailId FROM scm_party_m A WHERE A.PartyCd='".$CustomerCd ."'";
	$eMailDetailArr = $this->execSelectQuery($sql);
	$EMailId = $eMailDetailArr[0]['EMailId'];
	//$EMailId = 'websupport@solargroup.com';
	
	$sql = "SELECT VanNo FROM vm_van_m  WHERE IdVan='" .$IdVan."'";
	$VanDetailArr = $this->execSelectQuery($sql);
	$VanNo = $VanDetailArr[0]['VanNo'];
	$Token = $TokenNoPrefix."/".$TokenNo;
	$templateArr = $this->getEMailTemplate(2);
	$alertMsg = $this->strPositSubstitute($templateArr['Body'], array($PONo, '0000-00-00', $Token, $VanNo ));
	$eMailBody = str_replace(array('\r','\n'),array("\r","\n"), $alertMsg);
	$objSolarMailer = new solarMailer();
	$objSolarMailer->sendSolarMail($EMailId, $templateArr['Subject'], $eMailBody, $templateArr['EMailIdCC']);
	return $objSolarMailer;
}


public function updateGateTranCustomer($data) {
	$queryInsert	= $this->getGateTranCustomerInsertQuery();
	$queryUpdate	= $this->getGateTranCustomerUpdateQuery();
	$queryDelete	= $this->getGateTranCustomerDeleteQuery();
	$returnData		= array();
	for($curRowNo=1; $curRowNo<=$data['LastRowNo1']; $curRowNo++) {
		if (!isset($data['CustomerCd1_'.$curRowNo])) {
			continue;
		}	

		$UniqueId = (int)$data['UniqueId1_'.$curRowNo];
		if (trim($data['CustomerCd1_'.$curRowNo])=='') {
			if ($UniqueId>0) {
				$inputArr	= array();
				$inputArr	= array($UniqueId);
				$sql		= vsprintf($queryDelete, $inputArr);
				$returnData	= $this->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}
		} else {
			if ($UniqueId>0) {
				$inputArr	= array($data['CustomerCd1_'.$curRowNo]
					, $data['SectorCd1_'.$curRowNo], (int)$data['ContractNo1_'.$curRowNo], (int)$data['PONo1_'.$curRowNo]
					, $data['TokenNoPrefix1_'.$curRowNo], (int)$data['TokenNo1_'.$curRowNo]
					, $data['RE11No1_'.$curRowNo], $this->convertDate($data['RE11Date1_'.$curRowNo],TRUE), (int)$data['RE11StatusCd1_'.$curRowNo]
					, (int)$data['IsRE11Received1_'.$curRowNo], $data['SAPSONo1_'.$curRowNo], $UniqueId);
				$sql		= vsprintf($queryUpdate,$inputArr);
				$returnData	= $this->execUpdateQuery($sql);
			} else {
				$inputArr	= array((int)$data['GateCd'], (int)$data['IsGateIn'], (int)$data['GateTranNo'], $data['CustomerCd1_'.$curRowNo]
					, $data['SectorCd1_'.$curRowNo], (int)$data['ContractNo1_'.$curRowNo], (int)$data['PONo1_'.$curRowNo]
					, $data['TokenNoPrefix1_'.$curRowNo], (int)$data['TokenNo1_'.$curRowNo]
					, $data['RE11No1_'.$curRowNo], $this->convertDate($data['RE11Date1_'.$curRowNo],TRUE), (int)$data['RE11StatusCd1_'.$curRowNo]
					, (int)$data['IsRE11Received1_'.$curRowNo], $data['SAPSONo1_'.$curRowNo]);
				$sql		= vsprintf($queryInsert,$inputArr);
				$returnData	= $this->execUpdateQuery($sql);
			}
			if ($returnData) return $returnData;
		}
	}
	return $returnData;
}

public function getGateTranCheckInsertQuery() {
	return ("INSERT INTO vm_gate_tran_check (GateCd, IsGateIn, GateTranNo, IsPhysical, VanCheckCd, CheckStatus, IsWarning, CheckResult, Remark) 
		VALUES (%s, %s, %s, %s, %s, %s, %s, '%s', '%s')");
}

public function getGateTranCheckUpdateQuery() {
	return ("UPDATE vm_gate_tran_check SET IsPhysical=%s, VanCheckCd=%s, CheckStatus=%s, IsWarning=%s, CheckResult='%s'
		, Remark='%s' WHERE UniqueId=%s");
}

public function getGateTranCheckDeleteQuery() {
	return ("DELETE FROM vm_gate_tran_check WHERE UniqueId=%s");
}

public function insertGateTranCheck($data) {
	$returnData = array();
	$queryInsert	= $this->getGateTranCheckInsertQuery();

	for($curRowNo=1; $curRowNo<=$data['LastRowNo2']; $curRowNo++) {
/*	if (!isset($data['CustomerCd1_'.$curRowNo]) || trim($data['CustomerCd1_'.$curRowNo])=='') {
		continue;
	}	*/
	$inputArr	= array((int)$data['GateCd'], (int)$data['IsGateIn'], (int)$data['GateTranNo'], (int)$data['IsPhysical2_'.$curRowNo]
		, (int)$data['VanCheckCd2_'.$curRowNo], (int)$data['CheckStatus2_'.$curRowNo], (int)$data['IsWarning2_'.$curRowNo]
		, $data['CheckResult2_'.$curRowNo], $data['Remark2_'.$curRowNo]);
	$sql		= vsprintf($queryInsert, $inputArr);
	$returnData	= $this->execUpdateQuery($sql);
	if ($returnData) return $returnData;
	}
	return $returnData;
}

public function updateGateTranCheck($data) {
	$queryInsert	= $this->getGateTranCheckInsertQuery();
	$queryUpdate	= $this->getGateTranCheckUpdateQuery();
	$queryDelete	= $this->getGateTranCheckDeleteQuery();
	$returnData		= array();
	for($curRowNo=1; $curRowNo<=$data['LastRowNo2']; $curRowNo++) {
/*		if (!isset($data['CustomerCd1_'.$curRowNo])) {
			continue;
		}	*/

		$UniqueId = (int)$data['UniqueId2_'.$curRowNo];
		if (1!=1) {
			if ($UniqueId>0) {
				$inputArr	= array();
				$inputArr	= array($UniqueId);
				$sql		= vsprintf($queryDelete, $inputArr);
				$returnData	= $this->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}
		} else {
			if ($UniqueId>0) {
				$inputArr	= array((int)$data['IsPhysical2_'.$curRowNo], (int)$data['VanCheckCd2_'.$curRowNo]
					, (int)$data['CheckStatus2_'.$curRowNo], (int)$data['IsWarning2_'.$curRowNo]
					, $data['CheckResult2_'.$curRowNo], $data['Remark2_'.$curRowNo], $UniqueId);
				$sql		= vsprintf($queryUpdate,$inputArr);
				$returnData	= $this->execUpdateQuery($sql);
			} else {
				$inputArr	= array((int)$data['GateCd'], (int)$data['IsGateIn'], (int)$data['GateTranNo'], (int)$data['IsPhysical2_'.$curRowNo]
					, (int)$data['VanCheckCd2_'.$curRowNo], (int)$data['CheckStatus2_'.$curRowNo], (int)$data['IsWarning2_'.$curRowNo]
					, $data['CheckResult2_'.$curRowNo], $data['Remark2_'.$curRowNo]);
				$sql		= vsprintf($queryInsert,$inputArr);
				$returnData	= $this->execUpdateQuery($sql);
			}
			if ($returnData) return $returnData;
		}
	}
	return $returnData;
}

public function getGateTranHeader($GateCd, $IsGateIn, $GateTranNo) {
	if( $IsGateIn==0 && ($GateCd=='1' or $GateCd=='11')){
	$stmt	= "SELECT A.*, B.PONo FROM vm_gate_tran A INNER JOIN  vm_gate_tran_customer B ON A.GateCd=B.GateCd and A.GateTranNo=B.GateTranNo and A.IsGateIn=B.IsGateIn   WHERE A.GateCd=%s AND A.IsGateIn=%s AND A.GateTranNo=%s";
	} else {
	$stmt	= "SELECT A.* FROM vm_gate_tran A   WHERE A.GateCd=%s AND A.IsGateIn=%s AND A.GateTranNo=%s";
	
	}
	
	$inputArr 	= array($GateCd, $IsGateIn, $GateTranNo);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData = $this->execSelectQuery($sql);
	return $returnData;
}

public function UpdateGaurdPhotoUpload($GateTranNo, $VerificationStatus) {
	$stmt		= "UPDATE vm_gate_tran SET ForGuardPhotoVerify=%s WHERE GateTranNo='%s' and (GateCd='1' OR GateCd='11')";
	$inputArr	= array($VerificationStatus, $GateTranNo);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->execUpdateQuery($sql);
	return $returnData;
}

public function getB2BVerificationStatus($IdExpdet) {
	$stmt		= "SELECT ForGuardPhotoVerify from b2b_solar_sc.so_expdet where IdExpDet=%s ";
	$inputArr	= array( $IdExpdet);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}


public function getGateTranSubCustomerEdit($GateCd, $IsGateIn, $GateTranNo) {
	$sql = "SELECT A.*,CONCAT(TRIM(A.TokenNoPrefix),'/',A.TokenNo%100) AS TokenNoFull FROM vm_gate_tran_customer A WHERE A.GateCd=$GateCd AND A.IsGateIn=$IsGateIn AND A.GateTranNo=$GateTranNo";
	$returnData = $this->execSelectQuery($sql);
	return $returnData;
}

public function getGateTranSubCustomerNewB2B($IdVan) {
	$inputArr 		= array($IdVan);
	$paraString		= vsprintf("%s", $inputArr);
	$sql			= "call sp_getGateTranSubCustomerNewB2B(" . $paraString. ")";
	$returnData 	= $this->execSelectQuery($sql);
	return $returnData;
}

public function getGateTranSubCustomerNewPre($GateCd, $IsGateIn, $GateTranNo) {
	$inputArr 		= array($GateCd, $IsGateIn, $GateTranNo);
	$paraString		= vsprintf("%s, %s, %s", $inputArr);
	$sql			= "call sp_getGateTranSubCustomerNewPre(" . $paraString. ")";
	$returnData 	= $this->execSelectQuery($sql);
	return $returnData;
}


public function getGateTranSubCheckNew($LoadUnloadCd, $GateNo, $IsGateIn, $SectorCd, $IdVan, $DriverLicNo, $GateCdLast, $IsGateInLast, $GateTranNoLast) {
	$inputArr 		= array($LoadUnloadCd, $GateNo, $IsGateIn, $SectorCd, $IdVan, $DriverLicNo, $GateCdLast, $IsGateInLast, $GateTranNoLast);
	$paraString		= vsprintf("%s, %s, %s , '%s', %s, '%s', %s, %s, %s", $inputArr);
	$sql			= "call sp_getGateTranSubCheckNew(" . $paraString. ")";
	$returnData 	= $this->execSelectQuery($sql);
	return $returnData;
}

public function getGateTranSubCheckEdit($GateCd, $IsGateIn, $GateTranNo) {
	$inputArr 		= array($GateCd, $IsGateIn, $GateTranNo);
	$paraString		= vsprintf("%s, %s, %s", $inputArr);
	$sql			= "call sp_getGateTranSubCheckEdit(" . $paraString. ")";
	$returnData 	= $this->execSelectQuery($sql);
	return $returnData;
}

public function getGateTranVanReportingNo($GateCd, $IdVan, $DriverLicNo, $IsGateIn) {
	$inputArr 		= array($GateCd, $IdVan, $DriverLicNo, $IsGateIn);
	$paraString		= vsprintf("%s, %s, '%s', %s", $inputArr);
	$sql			= "call sp_getGateTranVanReportingNo(" . $paraString. ")";
	$preResults		= '';
	$returnData 	= $this->execSelectQuery($sql, 0, $preResults);
	return $preResults;
}

public function checkGetTranVanActive($VanNo) {		// defunct
	$inputArr 		= array($VanNo);
	$paraString		= vsprintf("'%s'",$inputArr);
	$sql			= "call sp_checkGetTranVanActive(" . $paraString. ")";
	$returnData 	= $this->execSelectQuery($sql);
	return $returnData;
	
}

public function getGateTranVanStatusApp($VanStatusCd, $GateNo, $IsGateIn, $LoadUnloadCd, $TransporterTypeCd) {
	$inputArr 		= array($VanStatusCd, $GateNo, $IsGateIn, $LoadUnloadCd, $TransporterTypeCd);
	$paraString		= vsprintf("%s, %s, %s, %s, %s",$inputArr);
	$sql			= "call sp_getGateTranVanStatusApp(" . $paraString. ")";
	$returnData 	= $this->execSelectQuery($sql);
	return $returnData;
}

public function fillGateTranVanDriverMDisp($IdVan, $DriverLicNo) {
	$inputArr 		= array($IdVan, $DriverLicNo);
	$paraString		= vsprintf("%s, '%s'",$inputArr);
	$sql			= "call sp_fillGateTranVanDriverMDisp(" . $paraString. ")";
	$returnData 	= $this->execSelectQuery($sql);
	return $returnData;
}

public function getVansNVanCapacity($PlantCd, $SectorCd) {
	$inputArr 		= array($PlantCd, $SectorCd);
	$paraString		= vsprintf("%s, '%s'",$inputArr);
	$sql			= "call sp_getVansNVanCapacity(" . $paraString. ")";
	$returnData 	= $this->execSelectQuery($sql);
	return $returnData;
}

public function processPostGateTran($data) {
	if ($data['GateNo']==1 && $data['IsGateIn']==1 && $data['VanStatusCd'] == self::VSWaitLoading) {
// For WaitingForLoading Vans, mail the Customer, if RE11 is Not received
		$inputArr 		= array($data['GateCd'], $data['IsGateIn'], $data['GateTranNo']);
		$paraString		= vsprintf("%s, %s, %s", $inputArr);
		$sql			= "call sp_mailGateTranRE11(" . $paraString. ")";
		$eMailDetailArr	= $this->execSelectQuery($sql);

		$templateArr = $this->getEMailTemplate(1);
		
		foreach ($eMailDetailArr as $eMailDetailRow) {
			$eMailBody = $this->strPositSubstitute($templateArr[0]['Body'], array($eMailDetailRow['Plant'], $eMailDetailRow['DateTime'], $eMailDetailRow['VanNo'], $eMailDetailRow['TokenNoFull']));
			$eMailBody = str_replace(array('\r','\n'),array("\r","\n"), $eMailBody);
			$objSolarMailer = new solarMailer();
			$objSolarMailer->sendSolarMail($eMailDetailRow['EMailId'], $templateArr[0]['Subject'], $eMailBody, $templateArr[0]['EMailIdCC']);
		}
	}	
}

// Company Van Status Transaction ---------------------------------------------------------------------------------------------------------------------------------------
public function getCompVanStatusTranList($search = '') {
	$inputArr 		= array(self::PlantCdChakdoh, 0, $search);
	$paraString		= vsprintf("%s, %s, '%s'", $inputArr);
	$sql			= "call sp_getCompVanStatusTran(" . $paraString. ")";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function updateCompVanStatusTran($data) {
	$DateTime = ($data['OldCompVanStatusCd']==$data['CompVanStatusCd'] ? $data['StatusDateTime'] : date('Y-m-d H:i:s'));
	$DateTime = $this->convertDate($DateTime, TRUE, 3);
	if ((int)$data['RecInTran']==0) {
		$stmt	= "INSERT INTO vm_compvan_status_tran (GateCd, IsGateIn, GateTranNo, IdVan, CompVanStatusCd, DateTime, TransUserName, TransDateTime)
					VALUES (%s, %s, %s, %s, %s, '%s', '%s', '%s')";
		$inputArr   = array((int)$data['GateCd'], (int)$data['IsGateIn'], (int)$data['GateTranNo'], (int)$data['IdVan']
						,(int)$data['CompVanStatusCd'], $DateTime, $_SESSION['UserName'], date('Y-m-d H:i:s'));
	} else {
		$stmt	= "UPDATE vm_compvan_status_tran SET CompVanStatusCd=%s, DateTime='%s', TransUserName='%s', TransDateTime='%s'
					WHERE GateCd=%s AND IsGateIn=%s AND GateTranNo=%s";
		$inputArr   = array((int)$data['CompVanStatusCd'], $DateTime, $_SESSION['UserName'], date('Y-m-d H:i:s')
							, (int)$data['GateCd'], (int)$data['IsGateIn'], (int)$data['GateTranNo']);
	}
	$sql		= vsprintf($stmt, $inputArr);
	$failMsg	= $this->execUpdateQuery($sql);
	return $failMsg;
}

public function getCompVanStatusTranHeader($IdVan) {
	$inputArr 		= array(self::PlantCdChakdoh, $IdVan, '');
	$paraString		= vsprintf("%s, %s, '%s'", $inputArr);
	$sql			= "call sp_getCompVanStatusTran(" . $paraString. ")";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}

// Utility Functions --------------------------------------------------------------------------------------------------------------------------------------
public function importData($srcFile, $tableName, $row1Status=1) {
// Import data from Tab limited Text file in specified Table. 
// row1Status 0: No Column Names in 1st Row. Data starts from 1st row. Assume ALL column values given in proper order. Can not skip any column
// 			  1: 1st Row with proper Column Names. (May contain Spaces, which are removed by this proc). Can skip any column (provided they have Null allowed or Default Values)
//			 -1: 1st Row with Column Names which have to be ignored. Data starts from 2nd row, but act as row1Status=0
$sql = "TRUNCATE TABLE ". $tableName;
$queryResult	= $this->execUpdateQuery($sql);

$handle = fopen($srcFile, "r");
if ($handle) { 
	$arrSrcData = explode("\n", fread($handle, filesize($srcFile))); 
}
$sizearrSrcData = count($arrSrcData);
$dataRow = ($row1Status==0) ? 0 : 1;

$columnCount	= 1000;
if ($row1Status == 1) {
	$curRec	= explode("\t", $arrSrcData[0]);
	$j		= count($curRec)-1;
	$isEmptyHead	= TRUE;
	while ($isEmptyHead && $j >= 0 ) {
		if (trim($curRec[$j])=='') {
			$j--;
		} else {
			$isEmptyHead = FALSE;
		}
	}
	$curRec	= array_slice($curRec, 0, $j+1);
	$columnCount= count($curRec);
	$columnList	= str_replace(' ', '', "`" . trim(implode("`,`", $curRec)) . "`");
}

for (; $dataRow < $sizearrSrcData; $dataRow++) {
	$curRec	= explode("\t", $arrSrcData[$dataRow]);
	$curRec	= array_slice($curRec, 0, $columnCount);
	$curRec	= preg_replace('/"$/','',preg_replace('/^"/', '', $curRec));	// remove " at Start & End of any string (put by Excel when converting Strings including ,)
	$curRec	= str_replace("'","\'",$curRec);		// Escape embeded 's with \'s
	
	$rowData		= str_replace("\r", '', implode("", $curRec));
	if (!empty($rowData)) {
		$rowData	= trim(implode("','", $curRec));
		$rowData	= "'" . $rowData . "'";
		$sql		= "INSERT INTO " . $tableName . ($row1Status==1 ? "(" . $columnList . ")" : "") . " VALUES (" . $rowData . ");";
		$queryResult= $this->execUpdateQuery($sql);
	}
}
}

public function getEMailTemplate($EMailTemplateNo) {
	$sql = 'SELECT Subject, Body, EMailIdCC FROM scm_email_template_m WHERE EMailTemplateNo='.$EMailTemplateNo ;
	$templateArr = $this->execSelectQuery($sql);
	$templateArr = array_map('trim', $templateArr[0]); 
	return 	$templateArr;
}

// Login & UserMgmt Function ------------------------------------------------------------------------------------------------------------------------------
public function getLoginDetail($data) {
	$stmt	= "SELECT A.*, C.Descript AS UserTypeName, C.IsAdmin, C.IsSubAdmin, C.IsReadAdmin, C.IsCustomer, C.IsASM, C.IsSalesCoordinator, C.IsGateOperator, C.HasCustomer, C.IsDispatcher, IFNULL(B.Descript,A.CustomerCd) AS Customer, IFNULL(B.SectorCd,' ') AS SectorCd FROM 
				scm_user_m A LEFT OUTER JOIN scm_party_m B ON A.CustomerCd=B.PartyCd 
				LEFT OUTER JOIN scm_user_type_m C ON A.UserType=C.UserType WHERE A.UserName='%s' and A.ActiveFlag IN (0,2)";
   $inputArr       = array(strtolower($data['UserName']));
   $sql            = vsprintf($stmt,$inputArr);
   $returnData = $this->execSelectQuery($sql);
   return $returnData;
}

public function getUserList($searchVal) {
	$searchCnd	= (isset($searchVal)) ? " AND A.Descript LIKE '". $searchVal . "%'" : "";
	$sql		= "SELECT A.UserName,A.Descript,IFNULL(C.Descript,A.UserType) AS UserType,A.ActiveFlag,IFNULL(B.Descript,A.CustomerCd) AS Customer
					FROM scm_user_m A LEFT OUTER JOIN scm_party_m B ON A.CustomerCd=B.PartyCd 
					LEFT OUTER JOIN scm_user_type_m C ON A.UserType=C.UserType
					WHERE A.ActiveFlag!=3 $searchCnd ORDER BY A.UserType,B.Descript,A.UserName";
	$returnData = $this->execSelectQuery($sql);
	return $returnData;
}
	
public function getUser($UserName) {
	$stmt		= "SELECT  * FROM scm_user_m WHERE UserName='%s' ";
	$inputArr	=  array($UserName);
	$sql		= vsprintf($stmt, $inputArr);
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}

public function isExistUser($mcode) {
	$sql			= "SELECT * FROM scm_user_m WHERE UserName='". $mcode. "'";
	$queryResult	= $this->execSelectQuery($sql);
	$rowCount		= count($queryResult);
	return $rowCount;
}

public function insertUser($data) {
	$stmt	= "INSERT INTO scm_user_m (UserName, Descript, Password, UserType, CustomerCd, ActiveFlag)
				VALUES('%s', '%s', '%s', %s, '%s', %s);";
	$CustomerCd = ($data['UserType']==11 ? $data['CustomerCd'] : '');
	$inputArr	= array(addslashes(trim($data['UserName'])),addslashes($data['Descript']),trim(md5($data['Password'])),(int)$data['UserType'],$CustomerCd,(int)$data['ActiveFlag']);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->execUpdateQuery($sql);
	return $returnData;
}

public function updateUser($data) {
	$CustomerCd = ($data['UserType']==11 ? $data['CustomerCd'] : '');
	if (isset($data['pswChange'])) {
		$stmt		= " UPDATE scm_user_m SET Descript = '%s', Password ='%s' ,UserType=%s,CustomerCd='%s',ActiveFlag=%s WHERE UserName='%s'";
		$inputArr	= array(addslashes($data['Descript']), trim(md5($data['Password'])), (int)$data['UserType'], $CustomerCd, (int)$data['ActiveFlag'], $data['oldUserName']);
	} else {
		$stmt		= " UPDATE scm_user_m SET Descript = '%s', UserType=%s,CustomerCd='%s',ActiveFlag=%s WHERE UserName='%s'";
		$inputArr	= array(addslashes($data['Descript']), (int)$data['UserType'], $CustomerCd, (int)$data['ActiveFlag'], $data['oldUserName']);
	}
	$sql				= vsprintf($stmt,$inputArr);
	$returnData		= $this->execUpdateQuery($sql);
	return $returnData;
}

public function removeUser($userRemoveList) {
	$sql = "UPDATE scm_user_m SET ActiveFlag=3 WHERE UserName IN (" . $userRemoveList . ")";
	$returnData = $this->execUpdateQuery($sql);
	return $returnData;
}

public function changePsw($data) {
	$sql		= "SELECT A.Password FROM scm_user_m A WHERE A.UserName='" . $_SESSION['UserName'] . "'";
	$returnData = $this->execSelectQuery($sql);
	if ($returnData[0]['Password']==md5(trim($_POST['OldPsw']))) {
		$stmt		= "UPDATE scm_user_m SET Password ='%s'WHERE UserName='%s'";
		$inputArr	= array(trim(md5($data['NewPsw'])), $_SESSION['UserName']);
		$sql		= vsprintf($stmt,$inputArr);
		$returnMsg	= $this->execUpdateQuery($sql);
	   return $returnMsg;
	} else
		return "Sorry. Unmatched Old-Password. Password not changed.";
}

//	get___List, get___, insert___ (or insert___Header, insert___Sub), update___ (or update___Header, update___Sub), isExist___



// Party Master ---------------------------------------------------------------------------------------------------------------------------------------
public function getPartyList($search = NULL, $PartyTypeCd) {
	$searchCnd	= ((isset($search)) ? " WHERE A.Descript LIKE '$search%' AND " : " WHERE ") . "A.PartyTypeCd=$PartyTypeCd" ;
//				. " AND A.PartyCd<>'31201113'";
	$sql		= "SELECT A.*,REPLACE(A.Descript,UPPER(A.City),'') AS Party,IFNULL(B.ShortDesc,A.SectorCd) AS Sector,A.City FROM scm_party_m A LEFT OUTER JOIN scm_sector_m B ON A.SectorCd=B.SectorCd 
					$searchCnd ORDER BY A.SectorCd, A.Descript";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function insertPartyData($data) {
	$stmt = "INSERT INTO scm_party_m (PartyCd, Descript, PartyTypeCd, SectorCd, Address, City, StateCd, TelNo, EMailId, IsActive, Division, DistChannel, ContactPerson) 
				VALUES ('%s', '%s', %s, '%s', '%s', '%s', '%s', '%s', '%s', %s, %s, %s, '%s')";
	$inputArr   = array($data['PartyCd'], $data['Descript'], (int)$data['PartyTypeCd'], $data['SectorCd'], $data['Address'], $data['City']
				, $data['StateCd'], $data['TelNo'], $data['EMailId'], (int)$data['IsActive'], (int)$data['Division'], (int)$data['DistChannel'], $data['ContactPerson']);
	$sql        = vsprintf($stmt, $inputArr);
	$failMsg	= $this->execUpdateQuery($sql);
	return $failMsg;
}

public function updatePartyData($data) {
	$stmt	= "UPDATE scm_party_m SET Descript='%s', PartyTypeCd=%s, SectorCd='%s', Address='%s', City='%s'
				, StateCd='%s', TelNo='%s', EMailId='%s', IsActive=%s, Division=%s, DistChannel=%s, ContactPerson='%s' WHERE PartyCd='%s'";
	$inputArr   = array($data['Descript'], (int)$data['PartyTypeCd'], $data['SectorCd'], $data['Address'], $data['City']
				, $data['StateCd'], $data['TelNo'], $data['EMailId'], (int)$data['IsActive'], (int)$data['Division'], (int)$data['DistChannel'], $data['ContactPerson'], $data['oldPartyCd']);
	$sql		= vsprintf($stmt,$inputArr);
	$failMsg	= $this->execUpdateQuery($sql);
	return $failMsg;
}

public function PartyEditData($ccode) {
	$stmt		= "SELECT * FROM scm_party_m WHERE PartyCd='%s' ";
	$inputArr	= array($ccode);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}

public function isExistPartyCode($ccode) {
	$sql			= "SELECT * FROM scm_party_m WHERE PartyCd='".$ccode."'";
	$queryResult	= $this->execSelectQuery($sql);
	$rowCount		= count($queryResult);
	return $rowCount;
}

// Van Master ---------------------------------------------------------------------------------------------------------------------------------------
public function getVanMList($search = NULL) {
	$searchCnd	= ((isset($search)) ? " WHERE A.VanNo LIKE '$search%' OR A.TransporterName LIKE '$search%'" : "") ;
	$sql		= "SELECT A.*,IFNULL(B.Descript,A.TransporterTypeCd) AS TransporterType, IFNULL(C.Descript,'') AS State FROM vm_van_m A LEFT OUTER JOIN scm_party_type_m B 
					ON A.TransporterTypeCd=B.PartyTypeCd LEFT OUTER JOIN scm_state_m C ON A.StateCd = C.StateCd $searchCnd ORDER BY A.TransporterTypeCd, A.VanNo";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function insertVanM($data) {
	$sql	= "SELECT IFNULL(MAX(IdVan),0) AS IdVan FROM vm_van_m WHERE TransporterTypeCd={$data['TransporterTypeCd']}";
	$returnData	= $this->execSelectQuery($sql);
	$this->lastInsertTranNo	= $returnData[0]['IdVan']+1;
	$stmt = "INSERT INTO vm_van_m (IdVan, VanNo, Descript, TransporterTypeCd, TransporterName, SectorCd, StateCd, Capacity, ExplLicNo, Dockey, ExplLicValidUptoDate, InsuranceValidUptoDate, FitnessCertValidUptoDate) 
				VALUES (%s, '%s', '%s', %s, '%s', '%s', '%s', %s, '%s', '%s', '%s',  '%s', '%s')";
	$inputArr   = array($this->lastInsertTranNo, $data['VanNo'], $data['Descript'], (int)$data['TransporterTypeCd'], $data['TransporterName'], $data['SectorCd'], $data['StateCd'], (float)$data['Capacity']
					, $data['ExplLicNo'], $data['Dockey'], $this->convertDate($data['ExplLicValidUptoDate'],TRUE), $this->convertDate($data['InsuranceValidUptoDate'],TRUE)
					, $this->convertDate($data['FitnessCertValidUptoDate'],TRUE));
	$sql        = vsprintf($stmt, $inputArr);
	$failMsg	= $this->execUpdateQuery($sql);
	return $failMsg;
}

public function updateVanMData($data) {
	$stmt	= "UPDATE vm_van_m SET VanNo='%s', Descript='%s', TransporterTypeCd=%s, TransporterName='%s', SectorCd='%s', StateCd='%s', Capacity=%s, ExplLicNo='%s', Dockey='%s', ExplLicValidUptoDate='%s'
				, InsuranceValidUptoDate='%s', FitnessCertValidUptoDate='%s' WHERE IdVan=%s";
	$inputArr   = array($data['VanNo'], $data['Descript'], (int)$data['TransporterTypeCd'], $data['TransporterName'], $data['SectorCd'], $data['StateCd'], (float)$data['Capacity']
					, $data['ExplLicNo'], $data['Dockey'], $this->convertDate($data['ExplLicValidUptoDate'],TRUE), $this->convertDate($data['InsuranceValidUptoDate'],TRUE)
					, $this->convertDate($data['FitnessCertValidUptoDate'],TRUE), $data['IdVan']);
	$sql		= vsprintf($stmt,$inputArr);
	$failMsg	= $this->execUpdateQuery($sql);
	return $failMsg;
}

public function VanMEditData($ccode) {
	$stmt		= "SELECT * FROM vm_van_m WHERE VanNo='%s' ";
	$inputArr	= array($ccode);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}

public function isExistVanMCode($ccode, $Dockey='') {
	$sql			= "SELECT * FROM vm_van_m WHERE VanNo='".$ccode."' AND Dockey='".$Dockey."'";
	$queryResult	= $this->execSelectQuery($sql);
	$rowCount		= count($queryResult);
	return $rowCount;
}

// Security Guard Master ---------------------------------------------------------------------------------------------------------------------------------------
public function getSecurityGuardMList($search = NULL) {
	$searchCnd	= ((isset($search)) ? " WHERE A.Descript LIKE '$search%' OR B.Descript LIKE '$search%'" : "") ;
	$sql		= "SELECT A.IdSecurityGuard,A.Descript,A.TelNo,IF(A.IsActive,'Active','InActive') AS IsActive
					,IFNULL(B.Descript,A.SecurityAgencyCd) AS SecurityAgency FROM vm_security_guard_m A LEFT OUTER JOIN scm_party_m B 
					ON A.SecurityAgencyCd=B.PartyCd $searchCnd ORDER BY A.IdSecurityGuard";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function insertSecurityGuardM($data) {
	$stmt = "INSERT INTO vm_security_guard_m (IdSecurityGuard, Descript, SecurityAgencyCd, Address, Age, TelNo, IdCardType, IdCardNo IsActive) 
				VALUES (%s, '%s', '%s', '%s', %s, '%s', '%s', '%s' %s)";
	$inputArr   = array((int)$data['IdSecurityGuard'], $data['Descript'], $data['SecurityAgencyCd'], $data['Address'], (int)$data['Age']
					, $data['TelNo'], $data['IdCardType'],$data['IdCardNo'], (int)$data['IsActive']);
	$sql        = vsprintf($stmt, $inputArr);
	$failMsg	= $this->execUpdateQuery($sql);
	return $failMsg;
}

public function updateSecurityGuardMData($data) {
	$stmt	= "UPDATE vm_security_guard_m SET Descript='%s', SecurityAgencyCd='%s', Address='%s', Age=%s, TelNo='%s', IdCardType='%s', IdCardNo='%s', IsActive=%s WHERE IdSecurityGuard=%s";
	$inputArr   = array($data['Descript'], $data['SecurityAgencyCd'], $data['Address'], (int)$data['Age']
					, $data['TelNo'], $data['IdCardType'],$data['IdCardNo'], (int)$data['IsActive'], (int)$data['oldIdSecurityGuard']);
	$sql		= vsprintf($stmt,$inputArr);
	$failMsg	= $this->execUpdateQuery($sql);
	return $failMsg;
}

public function SecurityGuardMEditData($ccode) {
	$stmt		= "SELECT * FROM vm_security_guard_m WHERE IdSecurityGuard=%s ";
	$inputArr	= array($ccode);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}

public function isExistSecurityGuardMCode($ccode) {
	$sql			= "SELECT * FROM vm_security_guard_m WHERE IdSecurityGuard='".$ccode."'";
	$queryResult	= $this->execSelectQuery($sql);
	$rowCount		= count($queryResult);
	return $rowCount;
}

public function getVanMSubTransporterEdit($IdVan) {
	$sql	= "SELECT A.UniqueId, A.IdVan, A.TransporterCd, IFNULL(B.Descript,A.TransporterCd) AS Descript FROM vm_van_m_transporter A 
				LEFT OUTER JOIN scm_party_m B ON A.TransporterCd=B.PartyCd WHERE A.IdVan=$IdVan ORDER BY A.UniqueId";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}

public function getVanMTransporterInsertQuery() {
	return ("INSERT INTO vm_van_m_transporter (IdVan, TransporterCd) 
		VALUES (%s, '%s')");
}

public function getVanMTransporterUpdateQuery() {
	return ("UPDATE vm_van_m_transporter SET TransporterCd='%s' WHERE UniqueId=%s");
}

public function getVanMTransporterDeleteQuery() {
	return ("DELETE FROM vm_van_m_transporter WHERE UniqueId=%s");
}

public function insertVanMTransporter($data) {
	$returnData = array();
	$queryInsert	= $this->getVanMTransporterInsertQuery();

	for($curRowNo=1; $curRowNo<=$data['LastRowNo1']; $curRowNo++) {
	if (!isset($data['TransporterCd1_'.$curRowNo]) || trim($data['TransporterCd1_'.$curRowNo])=='') {
		continue;
	}	
	$inputArr	= array($data['IdVan'], $data['TransporterCd1_'.$curRowNo]);
	$sql		= vsprintf($queryInsert, $inputArr);
	$returnData	= $this->execUpdateQuery($sql);
	if ($returnData) return $returnData;
	}
	return $returnData;
}

public function updateVanMTransporter($data) {
	$queryInsert	= $this->getVanMTransporterInsertQuery();
	$queryUpdate	= $this->getVanMTransporterUpdateQuery();
	$queryDelete	= $this->getVanMTransporterDeleteQuery();
	$returnData		= array();
	for($curRowNo=1; $curRowNo<=$data['LastRowNo1']; $curRowNo++) {
		if (!isset($data['TransporterCd1_'.$curRowNo])) {
			continue;
		}	

		$UniqueId = (int)$data['UniqueId1_'.$curRowNo];
		if (trim($data['TransporterCd1_'.$curRowNo])=='') {
			if ($UniqueId>0) {
				$inputArr	= array();
				$inputArr	= array($UniqueId);
				$sql		= vsprintf($queryDelete, $inputArr);
				$returnData	= $this->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}
		} else {
			if ($UniqueId>0) {
				$inputArr	= array($data['TransporterCd1_'.$curRowNo], $UniqueId);
				$sql		= vsprintf($queryUpdate, $inputArr);
				$returnData	= $this->execUpdateQuery($sql);
			} else {
				$inputArr	= array($data['IdVan'], $data['TransporterCd1_'.$curRowNo]);
				$sql		= vsprintf($queryInsert, $inputArr);
				$returnData	= $this->execUpdateQuery($sql);
			}
			if ($returnData) return $returnData;
		}
	}
	return $returnData;
}

// VanDriver Master ---------------------------------------------------------------------------------------------------------------------------------------
public function getVanDriverMList($search = NULL) {
	$searchCnd	= ((isset($search)) ? " WHERE A.DriverLicNo LIKE '$search%' OR A.Descript LIKE '$search%'" : "") ;
	$sql		= "SELECT A.DriverLicNo, xf_Proper(A.Descript) AS Descript, A.TransporterTypeCd, DATE_FORMAT(A.DriverLicValidUptoDate,'%d-%m-%Y') AS DriverLicValidUptoDate, DATE_FORMAT(A.HazardLicValidUptoDate,'%d-%m-%Y') AS HazardLicValidUptoDate, A.TelNo
					,IFNULL(B.Descript,A.TransporterTypeCd) AS TransporterType FROM vm_van_driver_m A LEFT OUTER JOIN scm_party_type_m B ON A.TransporterTypeCd=B.PartyTypeCd $searchCnd ORDER BY A.TransporterTypeCd, A.DriverLicNo";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;	
}

public function insertVanDriverM($data) {
	$stmt = "INSERT INTO vm_van_driver_m (DriverLicNo, Descript, TransporterTypeCd, DriverLicPDF, AdharCardPDF,  AdharCardNo, DriverLicValidUptoDate, HazardLicValidUptoDate, TelNo) 
			VALUES ('%s', '%s', %s, '%s', '%s', '%s', '%s', '%s', '%s')";
		$filenameLic = str_replace("/", "_", $data['DriverLicNo']). '.pdf';
        $target_pathLic = 'Upload/Driver/Licence/';
        $target_pathLic = $target_pathLic . $filenameLic;

		$filenameAdhar = $data['AdharCardNo'] . '.pdf';
        $target_pathAdhar = 'Upload/Driver/AdharCard/';
        $target_pathAdhar = $target_pathAdhar . $filenameAdhar;
        
		if(!move_uploaded_file($_FILES['DriverLicPDF']['tmp_name'], $target_pathLic)){
			$filenameLic=''; 
		 }
		if(!move_uploaded_file($_FILES['AdharCardPDF']['tmp_name'], $target_pathAdhar)){
			$filenameAdhar='';
		}
		
	$inputArr   = array($data['DriverLicNo'], $data['Descript'], (int)$data['TransporterTypeCd'], $filenameLic, $filenameAdhar, $data['AdharCardNo'],  $this->convertDate($data['DriverLicValidUptoDate'],TRUE)
					, $this->convertDate($data['HazardLicValidUptoDate'],TRUE), $data['TelNo']);
	$sql        = vsprintf($stmt, $inputArr);
	$failMsg	= $this->execUpdateQuery($sql);
	return $failMsg;
}

public function updateVanDriverMData($data) {
	//print_r($data);
	$stmt	= "UPDATE vm_van_driver_m SET Descript='%s', TransporterTypeCd=%s, DriverLicValidUptoDate='%s', HazardLicValidUptoDate='%s', TelNo='%s', DriverLicPDF='%s', AdharCardPDF ='%s', AdharCardNo='%s'  WHERE DriverLicNo='%s'";
		$filenameLic = str_replace("/", "_", $data['DriverLicNo']) . '.pdf';
        $target_pathLic = 'Upload/Driver/Licence/';
        $target_pathLic = $target_pathLic . $filenameLic;

		$filenameAdhar = $data['AdharCardNo'] . '.pdf';
        $target_pathAdhar = 'Upload/Driver/AdharCard/';
        $target_pathAdhar = $target_pathAdhar . $filenameAdhar;
        
		if(!move_uploaded_file($_FILES['DriverLicPDF']['tmp_name'], $target_pathLic)){
			$filenameLic=$data['DriverLicPDF']; 
		 }
		if(!move_uploaded_file($_FILES['AdharCardPDF']['tmp_name'], $target_pathAdhar)){
			$filenameAdhar=$data['AdharCardPDF'];;
		}
		
	$inputArr   = array($data['Descript'], (int)$data['TransporterTypeCd'], $this->convertDate($data['DriverLicValidUptoDate'],TRUE)
					, $this->convertDate($data['HazardLicValidUptoDate'],TRUE), $data['TelNo'],  $filenameLic, $filenameAdhar, $data['AdharCardNo'], $data['oldDriverLicNo']);
	$sql		= vsprintf($stmt,$inputArr);
	$failMsg	= $this->execUpdateQuery($sql);
	return $failMsg;
}

public function VanDriverMEditData($ccode) {
	$stmt		= "SELECT * FROM vm_van_driver_m WHERE DriverLicNo='%s' ";
	$inputArr	= array($ccode);
	$sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}

public function isExistVanDriverMCode($ccode) {
	$sql			= "SELECT * FROM vm_van_driver_m WHERE DriverLicNo='".$ccode."'";
	$queryResult	= $this->execSelectQuery($sql);
	$rowCount		= count($queryResult);
	return $rowCount;
}
		
public function getVanDriverMSubTransporterEdit($DriverLicNo) {
	$sql	= "SELECT A.UniqueId, A.DriverLicNo, A.TransporterCd, IFNULL(B.Descript,A.TransporterCd) AS Descript FROM vm_van_driver_m_transporter A 
				LEFT OUTER JOIN scm_party_m B ON A.TransporterCd=B.PartyCd WHERE DriverLicNo='$DriverLicNo' ORDER BY A.UniqueId";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}

public function getVanIdBarcode($vanId) {
	$sql	= "SELECT IdVan FROM vm_van_m  WHERE VanBarcodeId='$vanId'";
	$returnData	= $this->execSelectQuery($sql);
	return $returnData;
}

function getStringBetween($str,$from,$to)
{
    $sub = substr($str, strpos($str,$from)+strlen($from),strlen($str));
    return substr($sub,0,strpos($sub,$to));
}

public function getVanDriverMTransporterInsertQuery() {
	return ("INSERT INTO vm_van_driver_m_transporter (DriverLicNo, TransporterCd) 
		VALUES ('%s', '%s')");
}

public function getVanDriverMTransporterUpdateQuery() {
	return ("UPDATE vm_van_driver_m_transporter SET TransporterCd='%s' WHERE UniqueId=%s");
}

public function getVanDriverMTransporterDeleteQuery() {
	return ("DELETE FROM vm_van_driver_m_transporter WHERE UniqueId=%s");
}

public function insertVanDriverMTransporter($data) {
	$returnData = array();
	$queryInsert	= $this->getVanDriverMTransporterInsertQuery();

	for($curRowNo=1; $curRowNo<=$data['LastRowNo1']; $curRowNo++) {
	if (!isset($data['TransporterCd1_'.$curRowNo]) || trim($data['TransporterCd1_'.$curRowNo])=='') {
		continue;
	}	
	$inputArr	= array($data['DriverLicNo'], $data['TransporterCd1_'.$curRowNo]);
	$sql		= vsprintf($queryInsert, $inputArr);
	$returnData	= $this->execUpdateQuery($sql);
	if ($returnData) return $returnData;
	}
	return $returnData;
}

public function updateVanDriverMTransporter($data) {
	$queryInsert	= $this->getVanDriverMTransporterInsertQuery();
	$queryUpdate	= $this->getVanDriverMTransporterUpdateQuery();
	$queryDelete	= $this->getVanDriverMTransporterDeleteQuery();
	$returnData		= array();
	for($curRowNo=1; $curRowNo<=$data['LastRowNo1']; $curRowNo++) {
		if (!isset($data['TransporterCd1_'.$curRowNo])) {
			continue;
		}	

		$UniqueId = (int)$data['UniqueId1_'.$curRowNo];
		if (trim($data['TransporterCd1_'.$curRowNo])=='') {
			if ($UniqueId>0) {
				$inputArr	= array();
				$inputArr	= array($UniqueId);
				$sql		= vsprintf($queryDelete, $inputArr);
				$returnData	= $this->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}
		} else {
			if ($UniqueId>0) {
				$inputArr	= array($data['TransporterCd1_'.$curRowNo], $UniqueId);
				$sql		= vsprintf($queryUpdate, $inputArr);
				$returnData	= $this->execUpdateQuery($sql);
			} else {
				$inputArr	= array($data['DriverLicNo'], $data['TransporterCd1_'.$curRowNo]);
				$sql		= vsprintf($queryInsert, $inputArr);
				$returnData	= $this->execUpdateQuery($sql);
			}
			if ($returnData) return $returnData;
		}
	}
	return $returnData;
}
// ---------------------------------------------------------------------------------------------------------------------------------------
	public function downloadFile($fullPath) {
		// Must be fresh start
		if (headers_sent())		die('Headers Sent');

		// Required for some browsers
		if (ini_get('zlib.output_compression'))		ini_set('zlib.output_compression', 'Off');
		
		if (file_exists($fullPath)) {
			$fsize = filesize($fullPath);
			$path_parts = pathinfo($fullPath);
			$ext = strtolower($path_parts["extension"]);
			
			// Determine Content Type
			switch ($ext) {
			case "pdf": $ctype="application/pdf"; break;
			case "exe": $ctype="application/octet-stream"; break;
			case "zip": $ctype="application/zip"; break;
			case "doc": $ctype="application/msword"; break;
			case "xls": $ctype="application/vnd.ms-excel"; break;
			case "ppt": $ctype="application/vnd.ms-powerpoint"; break;
			case "gif": $ctype="image/gif"; break;
			case "png": $ctype="image/png"; break;
			case "jpeg":
			case "jpg": $ctype="image/jpg"; break;
			default: $ctype="application/force-download";
			}
			
			header("Pragma: public"); // required
			header("Expires: 0");
			header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
			header("Cache-Control: private", false); // required for certain browsers
			header("Content-Type: ".$ctype);
			header('Content-Disposition: attachment; filename="'.basename($fullPath).'";' );
			header("Content-Transfer-Encoding: binary");
			header("Content-Length: ".$fsize);
			ob_clean();
			flush();
			readfile($fullPath);
		} else die('File Not Found');
	} 

public function reportOnlineVanStatus($PlantCd, $SectorCdStr, $StateCd, $ItemCdStr) {
	$inputArr 		= array($PlantCd, $SectorCdStr, $StateCd, $ItemCdStr);
	$paraString		= vsprintf("%s, '%s', '%s', '%s'", $inputArr);
	$sql			= "call sp_reportOnlineVanStatus(" . $paraString. ")";
	$returnData 	= $this->execSelectQuery($sql, 2);
	return $returnData;
}

public function reportCustomerVanStatus($CustomerCd) {
	$inputArr 		= array($CustomerCd);
	$paraString		= vsprintf("'%s'", $inputArr);
	$sql			= "call sp_reportCustomerVanStatus(" . $paraString. ")";
	$preResults		= '';
	$returnData 	= $this->execSelectQuery($sql, 2, $preResults);
	return $preResults;
}

function utilSetFORCompVan($SectorCd, $CompanyCd, $IsAssignVan) {
	$inputArr 		= array($SectorCd, $CompanyCd, $IsAssignVan);
	$paraString		= vsprintf("'%s', %s, %s", $inputArr);
	$sql			= "call sp_utilSetFORCompanyVan(" . $paraString. ")";
	$preResults		= '';
	$returnData 	= $this->execSelectQuery($sql, 2, $preResults);
	return $preResults;
}

public function utilSetFORCompVanSave($data) {
	$IsAssignVan	= $data['IsAssignVan']=='1';
	for ($CurSubNo=1; $CurSubNo<=$data['LastSubNo']; $CurSubNo++) {
		$Priority	= $data['Priority'.$CurSubNo.'_0'];
		$VanNo		= $data['Van_No_'.$CurSubNo.'_0'];
		for ($CurRowNo=1; $CurRowNo<=$data['LastRowNo'.$CurSubNo]; $CurRowNo++) {
			$PONo	= $data['PO_No_'.$CurSubNo.'_'.$CurRowNo];
			if ($IsAssignVan) {
				$inputArr = array($VanNo, (int)$PONo);
				$stmt	= "UPDATE " . ($data['SectorCd']=='T' ? self::DBNameTrade : self::DBNameNonTrade) . ".so_expdet SET VehicleNo='%s' WHERE IdExpDet=%s";
			} else {
				$inputArr = array((int)$Priority, (int)$PONo);
				$stmt	= "UPDATE " . ($data['SectorCd']=='T' ? self::DBNameTrade : self::DBNameNonTrade) . ".so_expdet SET FORVanPriority=%s WHERE IdExpDet=%s";
			}
			$sql	= vsprintf($stmt, $inputArr);
			$failMsg= $this->execUpdateQuery($sql);
		}
	}
	return $failMsg;
}

function utilSetAllVan($SectorCd, $CompanyCd, $IsAssignVan) {
	$inputArr 		= array($SectorCd, $CompanyCd, $IsAssignVan);
	$paraString		= vsprintf("'%s', %s, %s", $inputArr);
	$sql			= "call sp_utilSetAllVan(" . $paraString. ")";
	$preResults		= '';
	$returnData 	= $this->execSelectQuery($sql, 2, $preResults);
	return $preResults;
}

public function utilSetAllVanSave($data) {
	$IsAssignVan	= $data['IsAssignVan']=='1';
	for ($CurSubNo=1; $CurSubNo<=$data['LastSubNo']; $CurSubNo++) {
		$Priority	= $data['Priority'.$CurSubNo.'_0'];
		$VanNo		= $data['Van_No_'.$CurSubNo.'_0'];
		
		for ($CurRowNo=1; $CurRowNo<=$data['LastRowNo'.$CurSubNo]; $CurRowNo++) {
			$PONo	= $data['PO_No_'.$CurSubNo.'_'.$CurRowNo];
			$SONo		= $data['SONo'.$CurSubNo.'_'.$CurRowNo];
			$RE11No		= $data['RE11No'.$CurSubNo.'_'.$CurRowNo];
			if ($IsAssignVan) {
				$inputArr = array($VanNo, (int)$PONo);
				$stmt	= "UPDATE " . ($data['SectorCd']=='T' ? self::DBNameTrade : self::DBNameNonTrade) . ".so_expdet SET VehicleNo='%s' WHERE IdExpDet=%s";
			} else {
				$inputArr = array((int)$Priority, (int)$PONo);
				$stmt	= "UPDATE " . ($data['SectorCd']=='T' ? self::DBNameTrade : self::DBNameNonTrade) . ".so_expdet SET FORVanPriority=%s WHERE IdExpDet=%s";
			}
			$sql	= vsprintf($stmt, $inputArr);
			$failMsg= $this->execUpdateQuery($sql);
			if(!$IsAssignVan){
				$inputArr = array((int)$Priority, $_SESSION['UserName'], $RE11No, $SONo);
				$stmt	= "UPDATE vm_gate_tran_customer SET  VanPriority='%s', VanPriorityUserName='%s'  WHERE RE11No='%s' and SAPSONo='%s'";
				$sql	= vsprintf($stmt, $inputArr);
				$failMsg= $this->execUpdateQuery($sql);
			}
		}
	}
	return $failMsg;
}

public function utilSetFORExFactoryVanSave($data) {
	$LastRowNo	= $data['LastRowNo'];
	$sql='';
	for ($CurSubNo=0; $CurSubNo<=$LastRowNo; $CurSubNo++) {
		$RE11NO			= isset($data['RE11NO_'.$CurSubNo]) ? $data['RE11NO_'.$CurSubNo] :'';
		$SONO			= isset($data['SONO_'.$CurSubNo]) ? $data['SONO_'.$CurSubNo] : '';
		$VanHold		= isset($data['VanHold_'.$CurSubNo]) ? $data['VanHold_'.$CurSubNo] : 0;
		$PriorityNo		= isset($data['PriorityNo_'.$CurSubNo]) ?  $data['PriorityNo_'.$CurSubNo] : 0;
		$PriorityReason	= isset($data['PriorityReason_'.$CurSubNo]) ? $data['PriorityReason_'.$CurSubNo] : '';
		if($VanHold!=0 || $PriorityNo!=0 ){
		$inputArr = array($VanHold, $PriorityNo, $PriorityReason, $_SESSION['UserName'], $RE11NO, $SONO);
		$stmt	= "UPDATE vm_gate_tran_customer SET VanHold='%s', VanPriority='%s', VanPriorityReason='%s', VanPriorityUserName='%s'  WHERE RE11No='%s' and SAPSONo='%s'";
		
		$sql	= vsprintf($stmt, $inputArr);
		$failMsg= $this->execUpdateQuery($sql);
		}
		}
	
	return $failMsg;
}

public function utilSetFORExFactoryVanApprove($data) {
	$LastRowNo	= $data['LastRowNo'];
	for ($CurSubNo=0; $CurSubNo<=$LastRowNo; $CurSubNo++) {
		$RE11NO			= isset($data['RE11NO_'.$CurSubNo]) ? $data['RE11NO_'.$CurSubNo] :'';
		$SONO			= isset($data['SONO_'.$CurSubNo]) ? $data['SONO_'.$CurSubNo] : '';
		$VanHold		= isset($data['VanHold_'.$CurSubNo]) ? $data['VanHold_'.$CurSubNo] : 0;
		$PriorityNo		= isset($data['PriorityNo_'.$CurSubNo]) ?  $data['PriorityNo_'.$CurSubNo] : 0;
		$PriorityReason	= isset($data['PriorityReason_'.$CurSubNo]) ? $data['PriorityReason_'.$CurSubNo] : '';
		if($VanHold!=0 || $PriorityNo!=0 ){
		$inputArr = array($VanHold, $PriorityNo, $PriorityReason, $_SESSION['UserName'], 1, $RE11NO, $SONO);
		$stmt	= "UPDATE vm_gate_tran_customer SET VanHold='%s', VanPriority='%s', VanPriorityReason='%s', VanPriorityUserName='%s' , VanPriorityStatus='%s'  WHERE RE11No='%s' and SAPSONo='%s' and VanPriority!=0";
		
		$sql	= vsprintf($stmt, $inputArr);
		$failMsg= $this->execUpdateQuery($sql);
		}
		}
	
	return $failMsg;
}


public function removeDriverPDF($DriverLicenceNo, $removefield, $filename) {
	if($removefield =='Licence'){
        $target_path = '/upload/Driver/Licence/';
        $target_path = $target_path . $filename;
       // unlink($target_path);
        $sql = "update vm_van_driver_m SET DriverLicPDF = ''  where DriverLicNo ='".$DriverLicenceNo . "'";
        $result = $this->execUpdateQuery($sql);
        return $result;
    }
	else if($removefield =='AdharCard'){
        $target_path = '/upload/Driver/AdharCard/';
        $target_path = $target_path . $filename;
        //unlink($target_path);
        $sql = "update vm_van_driver_m SET AdharCardPDF = ''  where DriverLicNo ='".$DriverLicenceNo . "'";
        $result = $this->execUpdateQuery($sql);
        return $result;
    }
}
 
}		// class BusiLogic
?>


