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

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"];}
$PerPage		= $_POST['PerPage']; if ($PerPage == ""){$PerPage = $_GET["PerPage"];}
							

print <<<HERE
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" href="default.css" type="text/css" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

</head>
<body>
<form name='additemserials' method='post' action=''>
<input type='hidden' name='ItemSerialNo' id='ItemSerialNo' value='$ItemSerialNo' size='20' maxlength='40'/>
<input type='hidden' name='SerialId' id='SerialId' value='$SerialId' size='10' maxlength='40'/>
<input type='hidden' name='PurDtId' id='PurDtId' value='$PurDtId' size='10' maxlength='40'/>
<input type='hidden' name='PurId' id='PurId' value='$PurId' size='10' maxlength='40'/>
<input type='hidden' name='PerPage' id='PerPage' value='$PerPage' size='10' maxlength='40'/>
<input type='hidden' name='HideSerialNo' id='HideSerialNo' value='$HideSerialNo' size='10' maxlength='40'/>
<input type='hidden' name='Mode' id='Mode' value='$Mode' size='10' maxlength='40'/>		    
HERE;

$sub_query = " Select PurchasesDt.Item_code, PurchasesDt.Warehouse_Code, ifNull(PurchasesDt.PurchaseDt_Quantity,0) ".
		     " From Temp_PurchasesDt As PurchasesDt ".
		     " Where PurchasesDt.Purchase_ID = '".$PurId."' ".
		     " And  PurchasesDt.PurchaseDt_ID = '".$PurDtId."' ";
$sub_result = selectData($connection, $sub_query);			 
					   
$ItemCode   = $sub_result[0];
$ItemWhCode = $sub_result[1];
$ItemQty    = $sub_result[2];

if ($Mode == 'insert' && $ItemSerialNo != '') {
	
	
	//Check Serial No To Be For Selected Purchase Item That Will Be Returned
	$query= " Select Serial_ID, ifNull(SerialNo_Used,0), Purchasesserialno.warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0)  ".
			" From Purchasesserialno ".
			" Inner Join PurchasesDt on PurchasesDt.PurchaseDt_ID = Purchasesserialno.PurchaseDt_ID ".
			" Inner Join Temp_PurchasesDt As ReturnPurchasesDt on ReturnPurchasesDt.RelatedDt_ID = PurchasesDt.PurchaseDt_ID ".
			" where Purchasesserialno.Item_Code = '".$ItemCode."' ".
			" And Purchasesserialno.SerialNo_Code =  '".$ItemSerialNo."' ".
			" And Purchasesserialno.Journal_Type =  'PI' ".
			" And ReturnPurchasesDt.PurchaseDt_ID = '".$PurDtId."' ".
			" And ReturnPurchasesDt.Journal_Type = 'PR' ".
			" And PurchasesDt.Journal_Type = 'PI' ";
	//echo $query; exit;  
	$result = selectData($connection, $query);
	$SerialNoExists = $result[0];
	
	$IsAvailable = 1;
	$ErrorExists = 0;
	$SelectedSerialID = 0;
	$SelectedSerialQty     = 0;
	$SelectedSerialQtySold = 0;
	
	if ($SerialNoExists != '') {
		if ($ItemWhCode != '' && isset($ItemWhCode)) {
		
			$sub_query= " Select SerialNo_Code  ".
						" From temp_purchasesserialno ".
						" where SerialNo_Code =  '".$ItemSerialNo."' ".
						" And Journal_Type in ('PR') ".
						" Union ".
						" Select SerialNo_Code  ".
						" From purchasesserialno ".
						" where SerialNo_Code =  '".$ItemSerialNo."' ".
						" And Journal_Type in ('PR') ".
						" And purchasesserialno.PurchaseDt_ID != '".$PurDtId."' ";
			 //echo $sub_query; exit;	 
			$sub_result=mysql_query($sub_query,$connection)  or die(mysql_error());
			$RecCount=mysql_num_rows($sub_result);
		
			if ($RecCount == 0 || $RecCount == '') {
				$sub_query= " Select Serial_ID, ifNull(SerialNo_Used,0), Purchasesserialno.warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0) As SerialNo_QuantitySold  ".
							" From Purchasesserialno ".
							" Inner Join PurchasesDt on PurchasesDt.PurchaseDt_ID = Purchasesserialno.PurchaseDt_ID ".
							" Inner Join Temp_PurchasesDt As ReturnPurchasesDt on ReturnPurchasesDt.RelatedDt_ID = PurchasesDt.PurchaseDt_ID ".
							" where Purchasesserialno.Item_Code = '".$ItemCode."' ".
							" And Purchasesserialno.SerialNo_Code =  '".$ItemSerialNo."' ".
							" And Purchasesserialno.Warehouse_Code =  '".$ItemWhCode."' ".
							" And Purchasesserialno.Journal_Type =  'PI' ".
							" And ReturnPurchasesDt.PurchaseDt_ID = '".$PurDtId."' ".
							" And ReturnPurchasesDt.Journal_Type = 'PR' ".
							" And PurchasesDt.Journal_Type = 'PI' ";
							
				//echo $sub_query; exit;			
				$sub_result=mysql_query($sub_query,$connection)  or die(mysql_error());
				$sub_row=mysql_fetch_array($sub_result);
				$RecCount=mysql_num_rows($sub_result);
				
				if ($RecCount == 0) {
					$IsAvailable = 0;
					$message  = "Insert not allowed, Serial No (".$ItemSerialNo.") is not available in the warehouse (".$ItemWhCode.") . "; 
				}	
				else {
					
					if ($sub_row[1] == 1)
					{
						//In Edit Case And Delete A Serial For The Return Purchase Then
					    // Allow To Add That Serial Again By Getting The Serials Related For That Return Purchase (First Part Of Query Below)
						$sub_query= " Select RelatedSerial_Id As Serial_ID, ifNull(SerialNo_Used,0), Purchasesserialno.warehouse_code, ifNull(SerialNo_Quantity,1), ".
									" ifNull(SerialNo_QuantitySold,0) As SerialNo_QuantitySold, 1 As OrderColumn ".
									" From purchasesserialno ".
									" Inner Join PurchasesDt on PurchasesDt.PurchaseDt_ID = Purchasesserialno.PurchaseDt_ID ".
									" Where PurchasesDt.Purchase_ID = '".$PurId."' ".
									" And Purchasesserialno.Item_Code = '".$ItemCode."' ".
									" And Purchasesserialno.SerialNo_Code =  '".$ItemSerialNo."' ".
									" And Purchasesserialno.Warehouse_Code =  '".$ItemWhCode."' ".
									" And Purchasesserialno.Journal_Type = 'PR' ".
									" And PurchasesDt.Journal_Type = 'PR' ".
									" Union ".
									" Select Serial_ID, ifNull(SerialNo_Used,0), Purchasesserialno.warehouse_code, ifNull(SerialNo_Quantity,1), ".
									" ifNull(SerialNo_QuantitySold,0) As SerialNo_QuantitySold, 2 As OrderColumn  ".
									" From Purchasesserialno ".
									" Inner Join PurchasesDt on PurchasesDt.PurchaseDt_ID = Purchasesserialno.PurchaseDt_ID ".
									" Inner Join Temp_PurchasesDt As ReturnPurchasesDt on ReturnPurchasesDt.RelatedDt_ID = PurchasesDt.PurchaseDt_ID ".
									" where Purchasesserialno.Item_Code = '".$ItemCode."' ".
									" And Purchasesserialno.SerialNo_Code =  '".$ItemSerialNo."' ".
									" And Purchasesserialno.Warehouse_Code =  '".$ItemWhCode."' ".
									" And ifNull(Purchasesserialno.SerialNo_Used,0) = 0 ".
									" And Purchasesserialno.Journal_Type =  'PI' ".
									" And ReturnPurchasesDt.PurchaseDt_ID = '".$PurDtId."' ".
									" And ReturnPurchasesDt.Journal_Type = 'PR' ".
									" And PurchasesDt.Journal_Type = 'PI' ".
									" Order by OrderColumn ";
									
																	
						$sub_result=mysql_query($sub_query,$connection)  or die(mysql_error());
						$sub_row=mysql_fetch_array($sub_result);
						$RecCount=mysql_num_rows($sub_result);
						
						if ($RecCount == 0) {
							$IsAvailable = 0;
							$message  = "Insert not allowed, Serial No (".$ItemSerialNo.") is used in warehouse (".$ItemWhCode."). "; 
						}  else {
							$SelectedSerialID      = $sub_row[0];
							$SelectedSerialQty     = $sub_row[3];
							$SelectedSerialQtySold = $sub_row[4];
							$IsAvailable = 1;
						}	
					
					} else {
						$SelectedSerialID      = $sub_row[0];
						$SelectedSerialQty     = $sub_row[3];
						$SelectedSerialQtySold = $sub_row[4];
						$IsAvailable = 1;
					}	

				}	
			}
			else {
				$IsAvailable = 0;
				$message  = "Insert not allowed, serial no already returned (check in temp). "; 
			}	
		}
		else {
			$IsAvailable = 0;
			$message  = "Insert not allowed, warehouse is empty. "; 
		}	
	}
	else {
		$IsAvailable = 0;
		$message  = "Insert not allowed, Serial No (".$ItemSerialNo.") is not available in selected purchase. "; 
	}
	
	
	if ($IsAvailable == 1)
	{
		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.", 'PR', $PurDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, Null, 1, '".$ItemWhCode."', '".$_SESSION['useraccount']."' ) ";
					 
		$result=mysql_query($query,$connection);
						
		if ($result){
			
			$query = " UPDATE temp_purchasesdt SET ".
					 " PurchaseDt_Quantity =  (ifNull(PurchaseDt_Quantity,0) + 1) ";
					 
			if ($Module_DoubleUnit == 1) {
				$query = $query.", Item_Unit2Qty = ifNull(Item_UnitCoef,0)*(ifNull(PurchaseDt_Quantity,0)) ";
			}		 
			$query = $query." WHERE PurchaseDt_ID = '".$PurDtId."' ";
			//echo $query."<br>";exit;				
			if (!updateData($connection, $query, true))		 
				$message  = mysql_error(); 	
			
			$message = "Serial no (".$ItemSerialNo.") inserted successfully.";
			fcommit();
		} else {
			$message  = mysql_error(); 
			frollback();
		}
	
	}
	
	
}

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.";
			
			$query = " UPDATE temp_purchasesdt SET ".
					 " PurchaseDt_Quantity =  (ifNull(PurchaseDt_Quantity,0) - 1) ";
					 
			if ($Module_DoubleUnit == 1) {
				$query = $query.", Item_Unit2Qty = ifNull(Item_UnitCoef,0)*(ifNull(PurchaseDt_Quantity,0)) ";
			}		 
			$query = $query." WHERE PurchaseDt_ID = '".$PurDtId."' ";
			//echo $query."<br>";exit;				
			if (!updateData($connection, $query, true))		 
				$message  = mysql_error();
				
			fcommit();
		}else{
			$message  = mysql_error(); 
			frollback();
		}			
	} 
	else {	
		$message  = "Delete not allowed, Serial No ".$ItemSerialNo." is used. "; 
	}	
}
/*
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.", 'PR', $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.";
	}
	
}*/

$message = ereg_replace(' ','%20',$message);
header ("Location: Form_ReturnPurchaseSerialNo.php?SRPurDtId=".$PurDtId."&SRPurId=".$PurId."&SRRemainQty=".$SRRemainQty."&perpage=".$PerPage."&SRHideSerialNo=".$HideSerialNo."&message=".$message);


print <<<HERE
</form>
</body>

HERE;

?>