<?php

session_start();
if($_SESSION['dbName']==""){
header("location:../index.php");
}


include("config.inc.php");
include("Functions_Purchase.php");

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");

$querysetup="select base1, base2, grid_color1, grid_color2, serialNb_quantity_setup from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
extract($rowsetup);
} 

set_time_limit(0);		

$PurId    			= $_POST['PurId']; 			if ($PurId == ""){$PurId = $_GET["PurId"];}
$PurDtId    		= $_POST['PurDtId']; 		if ($PurDtId == ""){$PurDtId = $_GET["PurDtId"];}
$Mode  				= $_POST['Mode']; 			if ($Mode == ""){$Mode = $_GET["Mode"];}
$ItemQty    		= $_POST['ItemQty']; 		if ($ItemQty == ""){$ItemQty = $_GET["ItemQty"];}
$ItemCode    		= $_POST['ItemCode']; 		if ($ItemCode == ""){$ItemCode = $_GET["ItemCode"];}
$ItemWhCode 		= $_POST['ItemWhCode']; 	if ($ItemWhCode == ""){$ItemWhCode = $_GET["ItemWhCode"];}
$SerialId  			= $_POST['SerialId']; 		if ($SerialId == ""){$SerialId = $_GET["SerialId"];}
$ItemSerialNo		= $_POST['ItemSerialNo']; 	if ($ItemSerialNo == ""){$ItemSerialNo = $_GET["ItemSerialNo"];}
$HideSerialNo		= $_POST['HideSerialNo']; 	if ($HideSerialNo == ""){$HideSerialNo = $_GET["HideSerialNo"];}
$SRString			= $_POST['SRString']; 		if ($SRString == ""){$SRString = $_GET["SRString"];}
$SRSeqFrom 			= $_POST['SRSeqFrom']; 		if ($SRSeqFrom == ""){$SRSeqFrom = $_GET["SRSeqFrom"];}
$SRSeqTo			= $_POST['SRSeqTo']; 		if ($SRSeqTo == ""){$SRSeqTo = $_GET["SRSeqTo"];}
$PerPage			= $_POST['PerPage']; 		if ($PerPage == ""){$PerPage = $_GET["PerPage"];}
$SrFromNbr			= $_POST['SrFromNbr'];	 	if ($SrFromNbr == ""){$SrFromNbr = $_GET["SrFromNbr"];}
$SrToNbr			= $_POST['SrToNbr']; 		if ($SrToNbr == ""){$SrToNbr = $_GET["SrToNbr"];}

$txtfromserialnb	= $_POST['txtfromserialnb']; 		if ($txtfromserialnb == ""){$txtfromserialnb = $_GET["txtfromserialnb"];}
$txttoserialnb		= $_POST['txttoserialnb']; 		if ($txttoserialnb == ""){$txttoserialnb = $_GET["txttoserialnb"];}
$serialtodelete 	= $_POST['txttodelete'];					

if($Mode == 'insertgenerate' ){ 
$t=1;  
if($SrFromNbr!= '') for ($field = $SrFromNbr; $field <= $SrToNbr; $field++) 
	for($i=$txtfromserialnb;$i<=$txttoserialnb;$i++){
			 
	$query1 = " SELECT Count(*) As SerialNoCount ".
          	 " 	from temp_purchasesserialno ".
	         "   WHERE SerialNo_Code = '".$field."".$txtprefixserialnb."".$i."' ".
			 "   AND Journal_Type = 'PI'   ";		 
	$query2 = "   SELECT Count(*) As SerialNoCount from purchasesserialno ".
	         "   WHERE SerialNo_Code = '".$field."".$txtprefixserialnb."".$i."' ".
			 "   AND Journal_Type = 'PI'  ".
			 "   AND PurchaseDt_ID != ".$PurDtId;	
	$result1 = selectData($connection, $query1);
	$result2 = selectData($connection, $query2);
	$SerialNoExists1 = $result1[0];
	$SerialNoExists2 = $result2[0];
	
	if ($SerialNoExists1 == 0 && $SerialNoExists2 == 0) {
		if ($ItemWhCode != '' && isset($ItemWhCode)) {
			mysql_query("START TRANSACTION");
			$SerId = get_nextsequence('Serial_Id','',$connection);	
			fcommit();
			
			mysql_query("START TRANSACTION");
			$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, Warehouse_code, Logged_Code ) ".
					 " VALUES ( ".$SerId.", 'PI', $PurDtId, '".$ItemCode."', '".$field."".$txtprefixserialnb."".$i."', 0, Null, 1, '".$ItemWhCode."', '".$_SESSION['useraccount']."' ) ";
							 
			$result=mysql_query($query,$connection);
				
			if ($result){
				//$addserialnbr = $i.",".$addserialnbr;
				$message = "Add serial no done successfully.<br>";
				fcommit();
			}else{
				$message  = mysql_error(); 
				frollback();
			}	
		}
		else {
			$message  = "Insert not allowed, warehouse is empty. "; 
		}	
	}
	else {
		
		$notaddserialnbr = $field."".$txtprefixserialnb."".$i.",".$notaddserialnbr;
		if(!($t%15)) $notaddserialnbr = $notaddserialnbr."<br>";
		$messagerr  = "Insert was not allowed for these Serial No: ".$notaddserialnbr; 
		$t++;
	}
	
	}
	
	if($SrFromNbr == ''){ 
	for($i=$txtfromserialnb;$i<=$txttoserialnb;$i++){
			 
	$query1 = " SELECT Count(*) As SerialNoCount ".
          	 " 	from temp_purchasesserialno ".
	         "   WHERE SerialNo_Code = '".$txtprefixserialnb."".$i."' ".
			 "   AND Journal_Type = 'PI'  ";		 
	$query2 = "   SELECT Count(*) As SerialNoCount from purchasesserialno ".
	         "   WHERE SerialNo_Code = '".$txtprefixserialnb."".$i."' ".
			 "   AND Journal_Type = 'PI'   ".
			 "   AND PurchaseDt_ID != ".$PurDtId;	
	$result1 = selectData($connection, $query1);
	$result2 = selectData($connection, $query2);
	$SerialNoExists1 = $result1[0];
	$SerialNoExists2 = $result2[0];
	
	if ($SerialNoExists1 == 0 && $SerialNoExists2 == 0) {
		if ($ItemWhCode != '' && isset($ItemWhCode)) {
			mysql_query("START TRANSACTION");
			$SerId = get_nextsequence('Serial_Id','',$connection);	
			fcommit();
			
			mysql_query("START TRANSACTION");
			$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, Warehouse_code, Logged_Code ) ".
					 " VALUES ( ".$SerId.", 'PI', $PurDtId, '".$ItemCode."', '".$txtprefixserialnb."".$i."', 0, Null, 1, '".$ItemWhCode."', '".$_SESSION['useraccount']."' ) ";
							 
			$result=mysql_query($query,$connection);
				
			if ($result){
				//$addserialnbr = $i.",".$addserialnbr;
				$message = "Add serial no done successfully.<br>";
				fcommit();
			}else{
				$message  = mysql_error(); 
				frollback();
			}	
		}
		else {
			$message  = "Insert not allowed, warehouse is empty. "; 
		}	
	}
	else {
		
		$notaddserialnbr = $txtprefixserialnb."".$i.",".$notaddserialnbr;
		if(!($t%15)) $notaddserialnbr = $notaddserialnbr."<br>";
		$messagerr  = "Insert was not allowed for these Serial No: ".$notaddserialnbr; //echo (!($t%15))."hh<br>";
		$t++;
			}
		}
	}
	if($serialNb_quantity_setup == 1){
		$queryserial = "select * from temp_purchasesserialno where PurchaseDt_ID = $PurDtId ";
		$resserial = mysql_query($queryserial, $connection);
		$rwserial = mysql_num_rows($resserial);
		$query = " UPDATE temp_purchasesdt SET PurchaseDt_Quantity = $rwserial Where PurchaseDt_ID = ".$PurDtId;
		$res = mysql_query($query, $connection)	; 
	}
//exit;
} //echo $Mode; exit;

if ($Mode == 'insert' && $ItemSerialNo != '') {

	$query = " SELECT Count(*) As SerialNoCount From ".
          	 " ( SELECT SerialNo_Code from temp_purchasesserialno ".
	         "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
			 "   AND Journal_Type = 'PI'  ".
			 "   UNION ".
			 "   SELECT SerialNo_Code from purchasesserialno ".
	         "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
			 "   AND Journal_Type = 'PI'  ".
			 "   AND PurchaseDt_ID != ".$PurDtId.
			 " ) Q1 ";
	$result = selectData($connection, $query);
	$SerialNoExists = $result[0];
	
	if ($SerialNoExists == 0) {
		if ($ItemWhCode != '' && isset($ItemWhCode)) {
			fbegin();
			$SerId = get_nextsequence('Serial_Id','',$connection);	
			fcommit();
			
			fbegin();
			$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, Warehouse_code, Logged_Code ) ".
					 " VALUES ( ".$SerId.", 'PI', $PurDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, Null, 1, '".$ItemWhCode."', '".$_SESSION['useraccount']."' ) ";
							 
			$result=mysql_query($query,$connection);
				
			if ($result){
				$message = "Add serial no ".$ItemSerialNo." done successfully.";
				fcommit();
			}else{
				$message  = mysql_error(); 
				frollback();
			}	
		}
		else {
			$message  = "Insert not allowed, warehouse is empty. "; 
		}	
	}
	else {
		$message  = "Insert not allowed, Serial No ".$ItemSerialNo." exists. "; 
	}
	if($serialNb_quantity_setup == 1){
		$queryserial = "select * from temp_purchasesserialno where PurchaseDt_ID = $PurDtId ";
		$resserial = mysql_query($queryserial, $connection);
		$rwserial = mysql_num_rows($resserial);
		$query = " UPDATE temp_purchasesdt SET PurchaseDt_Quantity = $rwserial Where PurchaseDt_ID = ".$PurDtId;
		$res = mysql_query($query, $connection)	; 
	}
}

else if ($Mode == 'delete' && $SerialId != '') {
	
	
	$query = " SELECT ifNull(SerialNo_Used, 0) As SerialNo_Used from temp_purchasesserialno ".
	         " WHERE Serial_ID = ".$SerialId;
			 //" AND Logged_Code = '".$_SESSION['useraccount']."' ";
	$result = selectData($connection, $query);
	$SerialNoUsed = $result[0];
	
	if ($SerialNoUsed == 0) {
		fbegin();
		
		$query = " DELETE from temp_purchasesserialno ".
				 " WHERE Serial_ID = ".$SerialId.
				 //" AND Logged_Code = '".$_SESSION['useraccount']."' ".
				 " AND ifNull(SerialNo_Used, 0) = 0  ";
		
		$result=mysql_query($query,$connection);
			
		if ($result){
			$message = "Delete serial no ".$ItemSerialNo." done successfully.";
			fcommit();
		}else{
			$message  = mysql_error(); 
			frollback();
		}			
	} 
	else {	
		$message  = "Delete not allowed, Serial No ".$ItemSerialNo." is used. "; 
	}	
	if($serialNb_quantity_setup == 1){
		$queryserial = "select * from temp_purchasesserialno where PurchaseDt_ID = $PurDtId ";
		$resserial = mysql_query($queryserial, $connection);
		$rwserial = mysql_num_rows($resserial);
		$query = " UPDATE temp_purchasesdt SET PurchaseDt_Quantity = $rwserial Where PurchaseDt_ID = ".$PurDtId;
		$res = mysql_query($query, $connection)	; 
	}
}

else if ($Mode == 'generate' && $PurDtId != '' && $PurId != '') {

	$vString  = $SRString;
	$vSeqFrom = $SRSeqFrom;
	$vSeqTo   = $SRSeqTo;
	
	$vLenSeqFrom = StrLen($vSeqFrom);
	$vLenSeqTo	 = StrLen($vSeqTo);
	$vRange      = $vSeqTo - $vSeqFrom;
	$ct = 0;
	$sub_query = " Select Item_code, Warehouse_Code ".
				 " From Temp_PurchasesDt As PurchasesDt ".
				 " Where Purchase_ID = '".$PurId."' ".
				 " And PurchaseDt_ID = '".$PurDtId."' ";
	$sub_result = selectData($connection, $sub_query);	    

	$ItemCode	 = $sub_result[0];
	$ItemWhCode  = $sub_result[1];
	
	if ($ItemCode != '' && $ItemWhCode != '') {
		
		$vExistsSerials = ''; 
		
		for ($i = $vSeqFrom; $i <= $vSeqTo; $i++) {
			
			if($field != '')
			$ItemSerialNo = $field."".$vString. str_pad($i, $vLenSeqTo, '0', STR_PAD_LEFT);
			else
			$ItemSerialNo = $vString. str_pad($i, $vLenSeqTo, '0', STR_PAD_LEFT);
			//echo $ItemSerialNo."<br>";
			
			$sub_query1 = " SELECT Count(*) As SerialNoCount From ".
						  " ( SELECT SerialNo_Code from temp_purchasesserialno ".
						  "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
						  "   AND Journal_Type = 'PI'   ".
						  "   UNION ".
						  "   SELECT SerialNo_Code from purchasesserialno ".
						  "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
						  "   AND Journal_Type = 'PI'   ".
						  "   AND PurchaseDt_ID != ".$PurDtId.
						  " ) Q1 ";
			$sub_result1 = selectData($connection, $sub_query1);
			$SerialNoExists = $sub_result1[0];
			
			if ($SerialNoExists == 0) {
				fbegin();
				$SerId = get_nextsequence('Serial_Id','',$connection);	
				fcommit();
					
				fbegin();
				$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, Warehouse_code, Logged_Code ) ".
						 " VALUES ( ".$SerId.", 'PI', $PurDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, Null, 1, '".$ItemWhCode."', '".$_SESSION['useraccount']."' ) ";
				//echo $query."<br>";					 
				$result=mysql_query($query,$connection);
						
				if ($result){
					//$message = "Generating serials done successfully.";
					fcommit();
				}else{
					$message  = mysql_error(); 
					frollback();
				}	
			} else {
				if(!($ct%15)) $vExistsSerials = $vExistsSerials.'<br>';
				$vExistsSerials = $vExistsSerials.', '.$ItemSerialNo;
				$ct++;
			}	
		}	
		
		if ($vExistsSerials	!= '')
			$message = " Cannot generate following serial(s), already exist(s): <br>". substr($vExistsSerials, 2);
	} else {
		$message = " Cannot generate serials, Item or Warehouse is empty.";
	}
	if($serialNb_quantity_setup == 1){
		$queryserial = "select * from temp_purchasesserialno where PurchaseDt_ID = $PurDtId ";
		$resserial = mysql_query($queryserial, $connection);
		$rwserial = mysql_num_rows($resserial);
		$query = " UPDATE temp_purchasesdt SET PurchaseDt_Quantity = $rwserial Where PurchaseDt_ID = ".$PurDtId;
		$res = mysql_query($query, $connection)	; 
	}
}

else if ($Mode == 'loadexcel' && $PurDtId != '' && $PurId != '') {
	
	
	$sub_query = " Select Item_code, Warehouse_Code ".
				 " From Temp_PurchasesDt As PurchasesDt ".
				 " Where Purchase_ID = '".$PurId."' ".
				 " And PurchaseDt_ID = '".$PurDtId."' ";
	$sub_result = selectData($connection, $sub_query);	    

	$ItemCode	 = $sub_result[0];
	$ItemWhCode  = $sub_result[1];
	
	if ($ItemCode != '' && $ItemWhCode != '') {
	
		$file = $_FILES['excelfile']['tmp_name'];
		
		if (isset($_FILES['excelfile']) && $_FILES['excelfile']['size'] > 0) { 

			$file_name = addslashes($_FILES['excelfile']['name']);
			$file_type =trim($_FILES['excelfile']['type']);
			
			//echo $file_type; exit;
			if (($file_type != 'application/vnd.ms-excel') && ($file_type != 'application/excel') && 
			    ($file_type != 'application/vnd.msexcel') )
			{
				$message = "Invalid file type. Please use only .xls files.";
			}
			else {
				
				$uploaddir = './uploadsexcel/'; //assigns upload directory 
				if (!is_dir($uploaddir)) mkdir($uploaddir, 0700);
				
				$uploadfile = $uploaddir . basename($_FILES['excelfile']['name']); //assigns upload directory and file name 
				if (move_uploaded_file($_FILES['excelfile']['tmp_name'], $uploadfile)) { 
				
					require_once('Spreadsheet/Excel/Reader.php');
					
					// ExcelFile($filename, $encoding);
					$data = new Spreadsheet_Excel_Reader();

					// Set output Encoding.
					$data->setUTFEncoder('iconv');
					$data->setOutputEncoding('CP1251');
					$data->read($uploadfile);
					
					if ($_FILES["excelfile"]["error"] == 0){
					
						$vExistsSerials = '';
						for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
							
							$ItemSerialNo = $data->sheets[0]['cells'][$i][1];
							//echo $ItemSerialNo."<br>";
							
							if ($ItemSerialNo != '') {
								$sub_query1 = " SELECT Count(*) As SerialNoCount From ".
											  " ( SELECT SerialNo_Code from temp_purchasesserialno ".
											  "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
											  "   AND Journal_Type = 'PI'  ".
											  "   UNION ".
											  "   SELECT SerialNo_Code from purchasesserialno ".
											  "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
											  "   AND Journal_Type = 'PI'  ".
											  "   AND PurchaseDt_ID != ".$PurDtId.
											  " ) Q1 ";
								$sub_result1 = selectData($connection, $sub_query1);
								$SerialNoExists = $sub_result1[0];
								
								if ($SerialNoExists == 0) {
									fbegin();
									$SerId = get_nextsequence('Serial_Id','',$connection);	
									fcommit();
										
									fbegin();
									$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, Warehouse_code, Logged_Code ) ".
											 " VALUES ( ".$SerId.", 'PI', $PurDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, Null, 1, '".$ItemWhCode."', '".$_SESSION['useraccount']."' ) ";
									//echo $query."<br>";					 
									$result=mysql_query($query,$connection);
											
									if ($result){
										//$message = "Generating serials done successfully.";
										fcommit();
									}else{
										$message  = mysql_error(); 
										frollback();
									}	
								} else {
									$vExistsSerials = $vExistsSerials.', '.$ItemSerialNo;
								}
							}
						}
						
					}
					else {
						$message = $_FILES["excelfile"]["error"];
					}
				
				} else 
				{ 
					$message = "File upload failed."; 
				}
				
			}
			
		} else {
			$message = "File not found.";
		}
				
		if ($vExistsSerials	!= '')
			$message = " Cannot load following serial(s), already exist(s): <br>". substr($vExistsSerials, 2);
	} else {
		$message = " Cannot load serials, Item or Warehouse is empty.";
	}
	
}
else if ($Mode == 'deleteall') {
	
		fbegin();

		foreach($serialtodelete as $serialid){
			
		$query = " DELETE from temp_purchasesserialno ".
				 " WHERE Serial_ID = ".$serialid." and ifNull(SerialNo_Used, 0) = 0  ";
		//echo $query; exit;
		$result=mysql_query($query,$connection);
			
		if ($result){
			$message = "Delete serial no done successfully.";
			fcommit();
		}else{
			$message  = mysql_error(); 
			frollback();
		}			
		}
	if($serialNb_quantity_setup == 1){
		$queryserial = "select * from temp_purchasesserialno where PurchaseDt_ID = $PurDtId ";
		$resserial = mysql_query($queryserial, $connection);
		$rwserial = mysql_num_rows($resserial);
		$query = " UPDATE temp_purchasesdt SET PurchaseDt_Quantity = $rwserial Where PurchaseDt_ID = ".$PurDtId;
		$res = mysql_query($query, $connection)	; 
	}
}
$message = ereg_replace(' ','%20',$message);
$ItemWhCode = ereg_replace('&','%26',$ItemWhCode);
if($messagerr !=''){
	$message=$message."".$messagerr;
	echo "<form name='purchasenb' method='POST' action=''>".
		"<input type='hidden' name='message' value='$message'>";
	echo "<script> 	document.purchasenb.action='Form_PurchaseSerialNo.php?SRItemCode=$ItemCode&SRItemQty=$ItemQty&SRWhCode=$ItemWhCode&SRPurDtId=$PurDtId&SRPurId=$PurId&SRRemainQty=$SRRemainQty&perpage=$PerPage&SRHideSerialNo=$HideSerialNo'; ";
	echo " document.purchasenb.submit();</script>";
	echo "</form>";	exit;
}
else header ("Location: Form_PurchaseSerialNo.php?SRItemCode=".$ItemCode."&SRItemQty=".$ItemQty."&SRWhCode=".$ItemWhCode."&SRPurDtId=".$PurDtId."&SRPurId=".$PurId."&SRRemainQty=".$SRRemainQty."&perpage=".$PerPage."&SRHideSerialNo=".$HideSerialNo."&message=".$message);


?>