<?php

session_start();
if($HTTP_SESSION_VARS['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);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_SerialNoOut,0) As Module_SerialNoOut ".
			 " From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}
$serialtodelete = $_POST['txttodelete'];
set_time_limit(0);
							
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='ItemCode' id='ItemCode' value='$ItemCode' size='20' maxlength='40'/>
<input type='hidden' name='AdjDtId' id='AdjDtId' value='$AdjDtId' size='10' maxlength='40'/>
<input type='hidden' name='AdjId' id='AdjId' value='$AdjId' size='10' maxlength='40'/>
<input type='hidden' name='ItemQty' id='ItemQty' value='$ItemQty' size='10' maxlength='40'/>
<input type='hidden' name='ItemWhCode' id='ItemWhCode' value='$ItemWhCode' 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'/>	
<input type='hidden' name='AdjustType' value='$AdjustType'>
HERE;

if ($AdjustType == 'O')      { $JourType = 'AR'; }
else if ($AdjustType == 'I') { $JourType = 'AI'; }
else if ($AdjustType == 'A') { $JourType = 'AI'; } 

$sub_query = " Select AdjustmentDt.Journal_Type ".
			 " From Temp_AdjustmentDt As AdjustmentDt ".
			 " Where Adjustment_ID = '".$AdjId."' ".
			 " And AdjustmentDt_id = '".$AdjDtId."' ";
$sub_result = selectData($connection, $sub_query);	    
$JourType   = $sub_result[0];// echo $JourType;exit;
if($Mode == 'insertgenerate' ){ 
$t=0;
$t1=0;
$t2=0;$t3=0;
	for($i=$txtfromserialnb;$i<=$txttoserialnb;$i++){
			$IsAvailable = 1;
	
	if ($ItemWhCode != '' && isset($ItemWhCode)) {
		if (($AdjustType == 'I') || ($AdjustType == 'A' && $JourType == 'AI')) {
			$query = " SELECT Count(*) As SerialNoCount From ".
					 " ( SELECT SerialNo_Code from temp_purchasesserialno ".
					 "   WHERE SerialNo_Code = '".$txtprefixserialnb."".$i."' ".
					 "   AND Journal_Type = '".$JourType."' ".
					 //"   AND ifNull(SerialNo_Used, 0) = 0  ".
					 "   UNION ".
					 "   SELECT SerialNo_Code from purchasesserialno ".
					 "   WHERE SerialNo_Code = '".$txtprefixserialnb."".$i."' ".
					 "   AND Journal_Type = '".$JourType."' ".
					 "   AND AdjustmentDt_ID != '".$AdjDtId."' ".
					 //"   AND ifNull(SerialNo_Used, 0) = 0  ".
					 " ) Q1 ";
			$result = selectData($connection, $query);
			$SerialNoExists = $result[0];
	
			if ($SerialNoExists == 0) {
				$IsAvailable = 1;	
			}
			else {
				$IsAvailable = 0;
				$notaddserialnbr = $txtprefixserialnb."".$i.",".$notaddserialnbr;
				if($t == 10) $notaddserialnbr = $notaddserialnbr."<br>";
				$messagerr  = "Insert not allowed, Serial No ".$notaddserialnbr." duplicates. "; 
				$t++;
			}	
		}	
		else if (($AdjustType == 'O') || ($AdjustType == 'A' && $JourType == 'AR')) {
			
			$query= " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0)  ".
					" From purchasesserialno ".
					" where Item_Code = '".$ItemCode."' ".
					" And SerialNo_Code =  '".$txtprefixserialnb."".$i."' ".
					" And Journal_Type in ('PI','DR','TR','AI','PD')";
					  
			$result = selectData($connection, $query);
			$SerialNoExists = $result[0];
			//echo $query; exit;

			$SelectedSerialID      = 0;
			$SelectedSerialQty     = 0;
			$SelectedSerialQtySold = 0;
			
			if ($SerialNoExists != '' || $Module_SerialNoOut == 1) {
				
				$sub_query = " SELECT Count(*) As SerialNoCount From ".
							 " ( SELECT SerialNo_Code from temp_purchasesserialno ".
							 "   WHERE SerialNo_Code = '".$txtprefixserialnb."".$i."' ".
							 "   AND Journal_Type = 'AR' ".
							 "   UNION ".
							 "   SELECT SerialNo_Code from purchasesserialno ".
							 "   WHERE SerialNo_Code = '".$txtprefixserialnb."".$i."' ".
							 "   AND Journal_Type = 'AR' ".
							 "   AND AdjustmentDt_ID != '".$AdjDtId."' ".
							 "   AND ifNull(SerialNo_Used, 0) = 0  ".
							 " ) Q1 ";
					 
				//echo $sub_query; exit;	
				$sub_result = selectData($connection, $sub_query);
				$RecCount = $sub_result[0];
				
				if ($RecCount == 0 || $RecCount == '') {
					$sub_query= " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0) As SerialNo_QuantitySold  ".
								" From purchasesserialno ".
								" where Item_Code = '".$ItemCode."' ".
								" And SerialNo_Code =  '".$txtprefixserialnb."".$i."' ".
								" And Warehouse_Code =  '".$ItemWhCode."' ".
								" And Journal_Type in ('PI','DR','TR','AI','PD') ";
									
					//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 && $Module_SerialNoOut != 1) {
						$IsAvailable = 0;
						$notaddserialnbr1 = $txtprefixserialnb."".$i.",".$notaddserialnbr1;
						if($t1 == 10) $notaddserialnbr1 = $notaddserialnbr1."<br>";
						$messagerr1  = "<br>Insert not allowed, Serial No $notaddserialnbr1 is not available in the warehouse (".$ItemWhCode.") . "; 
						$t1++;
					}	
					else if ($RecCount == 0 && $Module_SerialNoOut == 1) {
						$SelectedSerialID      = 'null';
						$SelectedSerialQty     = 1;
						$SelectedSerialQtySold = 0;
						$IsAvailable = 1;
					}	
					else {
							
						if ($sub_row[1] == 1)
						{
							$sub_query= " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0)  ".
										" From purchasesserialno ".
										" where Item_Code = '".$ItemCode."' ".
										" And SerialNo_Code =  '".$txtprefixserialnb."".$i."' ".
										" And Warehouse_Code =  '".$ItemWhCode."' ".
										" And ifNull(SerialNo_Used,0) = 0 ".
										" And Journal_Type in ('PI','DR','TR','AI','PD')";
							$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;
								$notaddserialnbr2 = $txtprefixserialnb."".$i.",".$notaddserialnbr2;
								if($t2 == 10) $notaddserialnbr2 = $notaddserialnbr2."<br>";
								$messagerr2  = "<br>Insert not allowed, Serial No $notaddserialnbr2 is used in warehouse (".$ItemWhCode."). "; 
								$t2++;
							}  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, duplicate serial no. <br>"; 
				}	
			}
			else {
				$IsAvailable = 0;
				$notaddserialnbr3 = $txtprefixserialnb."".$i.",".$notaddserialnbr3;
					if($t3 == 10) $notaddserialnbr3 = $notaddserialnbr3."<br>";
				$messagerr3  = "Insert not allowed, Serial No $notaddserialnbr3 is not available.<br> "; 
				$t3++;
			}
		}
	}
	else {
		$IsAvailable = 0;
		$message  = "Insert not allowed, warehouse is empty. "; 
	}
	
	
	
	if ($IsAvailable == 1)
	{
		fbegin();
		$SerId = get_nextsequence('Serial_Id','',$connection);	
		fcommit();
					
		fbegin();
		
		if (($AdjustType == 'I') || ($AdjustType == 'A' && $JourType == 'AI')) { 
			$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, AdjustmentDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, Warehouse_code, Logged_Code ) ".
					 " VALUES ( ".$SerId.", '".$JourType."', $AdjDtId, $AdjDtId, '".$ItemCode."', '".$txtprefixserialnb."".$i."', 0, Null, 1, '".$ItemWhCode."', '".$_SESSION['useraccount']."' ) ";
		} else if (($AdjustType == 'O') || ($AdjustType == 'A' && $JourType == 'AR')) {		 
			$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, AdjustmentDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, SerialNo_QuantitySold, Warehouse_code, Logged_Code ) ".
					 " VALUES ( ".$SerId.", '".$JourType."', $AdjDtId, $AdjDtId, '".$ItemCode."', '".$txtprefixserialnb."".$i."', 0, ".$SelectedSerialID.", '".$SelectedSerialQty."', $SelectedSerialQtySold + 1, '".$ItemWhCode."', '".$_SESSION['useraccount']."' ) ";		 
		}
		
		$result=mysql_query($query,$connection);
			
		if ($result){
			$message = "Add serial no done successfully.";
			fcommit();
		}else{
			$message  = mysql_error(); 
			frollback();
		}
	
	}
	
		
	}
	if($serialNb_quantity_setup == 1){
		$queryserial = "select * from temp_purchasesserialno where PurchaseDt_ID = $AdjDtId ";
		$resserial = mysql_query($queryserial, $connection);
		$rwserial = mysql_num_rows($resserial);
		$query = " UPDATE Temp_AdjustmentDt SET AdjustmentDt_Quantity = $rwserial Where AdjustmentDt_ID = ".$AdjDtId;
		$res = mysql_query($query, $connection)	; 
	}
	
}	
if ($Mode == 'insert' && $ItemSerialNo != '') {

	$IsAvailable = 1;
	
	if ($ItemWhCode != '' && isset($ItemWhCode)) {
		if (($AdjustType == 'I') || ($AdjustType == 'A' && $JourType == 'AI')) {
			$query = " SELECT Count(*) As SerialNoCount From ".
					 " ( SELECT SerialNo_Code from temp_purchasesserialno ".
					 "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
					 "   AND Journal_Type = '".$JourType."' ".
					 //"   AND ifNull(SerialNo_Used, 0) = 0  ".
					 "   UNION ".
					 "   SELECT SerialNo_Code from purchasesserialno ".
					 "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
					 "   AND Journal_Type = '".$JourType."' ".
					 "   AND AdjustmentDt_ID != '".$AdjDtId."' ".
					 //"   AND ifNull(SerialNo_Used, 0) = 0  ".
					 " ) Q1 ";
			$result = selectData($connection, $query);
			$SerialNoExists = $result[0];
	
			if ($SerialNoExists == 0) {
				$IsAvailable = 1;	
			}
			else {
				$IsAvailable = 0;
				$message  = "Insert not allowed, Serial No ".$ItemSerialNo." duplicates. "; 
			}	
		}	
		else if (($AdjustType == 'O') || ($AdjustType == 'A' && $JourType == 'AR')) {
			
			$query= " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0)  ".
					" From purchasesserialno ".
					" where Item_Code = '".$ItemCode."' ".
					" And SerialNo_Code =  '".$ItemSerialNo."' ".
					" And Journal_Type in ('PI','DR','TR','AI','PD')";
					  
			$result = selectData($connection, $query);
			$SerialNoExists = $result[0];
			//echo $query; exit;

			$SelectedSerialID      = 0;
			$SelectedSerialQty     = 0;
			$SelectedSerialQtySold = 0;
			
			if ($SerialNoExists != '' || $Module_SerialNoOut == 1) {
				
				$sub_query = " SELECT Count(*) As SerialNoCount From ".
							 " ( SELECT SerialNo_Code from temp_purchasesserialno ".
							 "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
							 "   AND Journal_Type = 'AR' ".
							 "   UNION ".
							 "   SELECT SerialNo_Code from purchasesserialno ".
							 "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
							 "   AND Journal_Type = 'AR' ".
							 "   AND AdjustmentDt_ID != '".$AdjDtId."' ".
							 "   AND ifNull(SerialNo_Used, 0) = 0  ".
							 " ) Q1 ";
					 
				//echo $sub_query; exit;	
				$sub_result = selectData($connection, $sub_query);
				$RecCount = $sub_result[0];
				
				if ($RecCount == 0 || $RecCount == '') {
					$sub_query= " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0) As SerialNo_QuantitySold  ".
								" From purchasesserialno ".
								" where Item_Code = '".$ItemCode."' ".
								" And SerialNo_Code =  '".$ItemSerialNo."' ".
								" And Warehouse_Code =  '".$ItemWhCode."' ".
								" And Journal_Type in ('PI','DR','TR','AI','PD') ";
									
					//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 && $Module_SerialNoOut != 1) {
						$IsAvailable = 0;
						$message  = "Insert not allowed, Serial No (".$ItemSerialNo.") is not available in the warehouse (".$ItemWhCode.") . "; 
					}	
					else if ($RecCount == 0 && $Module_SerialNoOut == 1) {
						$SelectedSerialID      = 'null';
						$SelectedSerialQty     = 1;
						$SelectedSerialQtySold = 0;
						$IsAvailable = 1;
					}	
					else {
							
						if ($sub_row[1] == 1)
						{
							$sub_query= " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0)  ".
										" From purchasesserialno ".
										" where Item_Code = '".$ItemCode."' ".
										" And SerialNo_Code =  '".$ItemSerialNo."' ".
										" And Warehouse_Code =  '".$ItemWhCode."' ".
										" And ifNull(SerialNo_Used,0) = 0 ".
										" And Journal_Type in ('PI','DR','TR','AI','PD')";
							$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, duplicate serial no. "; 
				}	
			}
			else {
				$IsAvailable = 0;
				$message  = "Insert not allowed, Serial No (".$ItemSerialNo.") is not available. "; 
			}
		}
	}
	else {
		$IsAvailable = 0;
		$message  = "Insert not allowed, warehouse is empty. "; 
	}
	
	
	
	if ($IsAvailable == 1)
	{
		fbegin();
		$SerId = get_nextsequence('Serial_Id','',$connection);	
		fcommit();
					
		fbegin();
		
		if (($AdjustType == 'I') || ($AdjustType == 'A' && $JourType == 'AI')) { 
			$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, AdjustmentDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, Warehouse_code, Logged_Code ) ".
					 " VALUES ( ".$SerId.", '".$JourType."', $AdjDtId, $AdjDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, Null, 1, '".$ItemWhCode."', '".$HTTP_SESSION_VARS['useraccount']."' ) ";
		} else if (($AdjustType == 'O') || ($AdjustType == 'A' && $JourType == 'AR')) {		 
			$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, AdjustmentDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, SerialNo_QuantitySold, Warehouse_code, Logged_Code ) ".
					 " VALUES ( ".$SerId.", '".$JourType."', $AdjDtId, $AdjDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, ".$SelectedSerialID.", '".$SelectedSerialQty."', $SelectedSerialQtySold + 1, '".$ItemWhCode."', '".$HTTP_SESSION_VARS['useraccount']."' ) ";		 
		}
		
		$result=mysql_query($query,$connection);
			
		if ($result){
			$message = "Add serial no ".$ItemSerialNo." done successfully.";
			fcommit();
		}else{
			$message  = mysql_error(); 
			frollback();
		}
	
	}
	if($serialNb_quantity_setup == 1){
		$queryserial = "select * from temp_purchasesserialno where PurchaseDt_ID = $AdjDtId ";
		$resserial = mysql_query($queryserial, $connection);
		$rwserial = mysql_num_rows($resserial);
		$query = " UPDATE Temp_AdjustmentDt SET AdjustmentDt_Quantity = $rwserial Where AdjustmentDt_ID = ".$AdjDtId;
		$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 = '".$HTTP_SESSION_VARS['useraccount']."' ";
	$result = selectData($connection, $query);
	$SerialNoUsed = $result[0];
	
	if ($SerialNoUsed == 0) {
		fbegin();
		
		$query = " DELETE from temp_purchasesserialno ".
				 " WHERE Serial_ID = ".$SerialId.
				 //" AND Logged_Code = '".$HTTP_SESSION_VARS['useraccount']."' ".
				 " AND ifNull(SerialNo_Used, 0) = 0  ";
		
		$result=mysql_query($query,$connection);
			
		if ($result){
			if($serialNb_quantity_setup == 1){
			$query = " UPDATE Temp_AdjustmentDt SET AdjustmentDt_Quantity = ifnull(AdjustmentDt_Quantity-1,0) Where AdjustmentDt_ID = ".$AdjDtId;
			$res = mysql_query($query, $connection)	; 
	}
			$message = "Delete serial no ".$ItemSerialNo." done successfully.";
			fcommit();
		}else{
			$message  = mysql_error(); 
			frollback();
		}			
	} 
	else {	
		$message  = "Delete not allowed, Serial No ".$ItemSerialNo." is used. "; 
	}	
}

else if ($Mode == 'load' && $AdjDtId != '' && $AdjId != '') {

	$vString  = $SRString;
	$vSeqFrom = $SRSeqFrom;
	$vSeqTo   = $SRSeqTo;
	
	$vLenSeqFrom = StrLen($vSeqFrom);
	$vLenSeqTo	 = StrLen($vSeqTo);
	$vRange      = $vSeqTo - $vSeqFrom;
	
	$sub_query = " Select Item_code, Warehouse_Code, AdjustmentDt_Quantity ".
				 " From Temp_AdjustmentDt As AdjustmentDt ".
				 " Where Adjustment_ID = '".$AdjId."' ".
				 " And AdjustmentDt_id = '".$AdjDtId."' ";
	$sub_result = selectData($connection, $sub_query);	    

	$ItemCode	 = $sub_result[0];
	$ItemWhCode  = $sub_result[1];
	//$ItemQty	 = $sub_result[2];
	
	if ($ItemCode != '' && $ItemWhCode != '') {
		
		$vExistsSerials		 = '';
		$vUnavailableSerials = '';
		$vUsedSerials		 = '';
		
		$IsAvailable = 1;
		
		for ($i = $vSeqFrom; $i <= $vSeqTo; $i++) {
		
			$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 Item_Code = '".$ItemCode."' ".
						  "   AND Journal_Type = '".$JourType."' ".
						  "   UNION ".
						  "   SELECT SerialNo_Code from purchasesserialno ".
						  "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
						  "   AND Item_Code = '".$ItemCode."' ".
						  "   AND ifNull(SerialNo_Used,0) = 0 ".
						  "   AND Journal_Type = '".$JourType."' ".
						  "   AND AdjustmentDt_ID != '".$AdjDtId."' ".
						  " ) Q1 ";
			$sub_result1 = selectData($connection, $sub_query1);
			$SerialNoExists = $sub_result1[0];
			
			if ($SerialNoExists == 0) {
			
				if (($AdjustType == 'O') || ($AdjustType == 'A' && $JourType == 'AR')) {
					$sub_query2 = " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0)  ".
								  " From purchasesserialno ".
								  " where Item_Code = '".$ItemCode."' ".
								  " And SerialNo_Code =  '".$ItemSerialNo."' ".
								  " And Warehouse_Code =  '".$ItemWhCode."' ".
								  " And Journal_Type in ('PI','DR','TR','AI','PD')";
							  
					$sub_result2 = selectData($connection, $sub_query2);
					$SerialNoAvailable = $sub_result2[0];
					//echo $query; exit;
					$IsAvailable = 1;
					$SelectedSerialID 	   = 0;
					$SelectedSerialQty     = 0;
					$SelectedSerialQtySold = 0;
					
					if ($SerialNoAvailable != '') {

						$sub_query3 = " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0) As SerialNo_QuantitySold  ".
									  " From purchasesserialno ".
									  " where Item_Code = '".$ItemCode."' ".
									  " And SerialNo_Code =  '".$ItemSerialNo."' ".
									  " And Warehouse_Code =  '".$ItemWhCode."' ".
									  " And Journal_Type in ('PI','DR','TR','AI','PD') ";
									  	
											
						//echo $sub_query3; exit;			
						$sub_result3=mysql_query($sub_query3,$connection)  or die(mysql_error());
						$sub_row3=mysql_fetch_array($sub_result3);
						
						if ($sub_row3[1] == 1)
						{
							$sub_query4 = " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0)  ".
										  " From purchasesserialno ".
										  " where Item_Code = '".$ItemCode."' ".
										  " And SerialNo_Code =  '".$ItemSerialNo."' ".
										  " And Warehouse_Code =  '".$ItemWhCode."' ".
										  " And ifNull(SerialNo_Used,0) = 0 ".
										  " And Journal_Type in ('PI','DR','TR','AI','PD')";
							$sub_result4=mysql_query($sub_query4,$connection)  or die(mysql_error());
							$sub_row4=mysql_fetch_array($sub_result4);
							$RecCount=mysql_num_rows($sub_result4);
										
							if ($RecCount == 0) {
								$IsAvailable = 0;
								$vUsedSerials = $vUsedSerials.', '.$ItemSerialNo;
							}  else {
								$SelectedSerialID      = $sub_row4[0];
								$SelectedSerialQty     = $sub_row4[3];
								$SelectedSerialQtySold = $sub_row4[4];
								$IsAvailable = 1;
							}	
									
						} else {
							$SelectedSerialID      = $sub_row3[0];
							$SelectedSerialQty     = $sub_row3[3];
							$SelectedSerialQtySold = $sub_row3[4];
							$IsAvailable = 1;
						}	
						
					}
					else {
						$IsAvailable = 0;
						$vUnavailableSerials = $vUnavailableSerials.', '.$ItemSerialNo;
					}
				}	
				
				if ($IsAvailable == 1)
				{
					fbegin();
					$SerId = get_nextsequence('Serial_Id','',$connection);	
					fcommit();
								
					fbegin();
					if (($AdjustType == 'I') || ($AdjustType == 'A' && $JourType == 'AI')) {
						$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, AdjustmentDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, Warehouse_code, Logged_Code ) ".
								 " VALUES ( ".$SerId.", '".$JourType."', $AdjDtId, $AdjDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, Null, 1, '".$ItemWhCode."', '".$HTTP_SESSION_VARS['useraccount']."' ) ";
					} else if (($AdjustType == 'O') || ($AdjustType == 'A' && $JourType == 'AR')) {		 
						$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, PurchaseDt_ID, AdjustmentDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, SerialNo_QuantitySold, Warehouse_code, Logged_Code ) ".
								 " VALUES ( ".$SerId.", '".$JourType."', $AdjDtId, $AdjDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, '".$SelectedSerialID."', '".$SelectedSerialQty."', $SelectedSerialQtySold + 1, '".$ItemWhCode."', '".$HTTP_SESSION_VARS['useraccount']."' ) ";		 
					}
												 
					$result=mysql_query($query,$connection);
									
					if ($result){
						//$message = "Generating serials done successfully.";
						fcommit();
					} else {
						$message  = mysql_error(); 
						frollback();
					}
				
				}
	
			} else {
				$vExistsSerials = $vExistsSerials.', '.$ItemSerialNo;
			}	
		}	// End of Loop
		
		if ($vExistsSerials	!= '')
			$message = " Cannot load following serial(s), already exist(s): <br>". substr($vExistsSerials, 2);
		if ($vUnavailableSerials != '')
			$message = $message."<br>"." Cannot load following serial(s), not available in the warehouse (".$ItemWhCode."): <br>". substr($vUnavailableSerials, 2);	
		if ($vUsedSerials != '')
			$message = $message."<br>"." Cannot load following serial(s), are used in the warehouse (".$ItemWhCode."): <br>". substr($vUsedSerials, 2);	

	} else {
		$message = " Cannot load serials, Item or Warehouse is empty.";
	}
	
}

else if ($Mode == 'loadexcel' && $AdjDtId != '' && $AdjId != '') {
	
	
	$sub_query = " Select Item_code, Warehouse_Code, AdjustmentDt_Quantity ".
				 " From Temp_AdjustmentDt As AdjustmentDt ".
				 " Where Adjustment_ID = '".$AdjId."' ".
				 " And AdjustmentDt_id = '".$AdjDtId."' ";
	$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 Item_Code = '".$ItemCode."' ".
											  "   AND Journal_Type = '".$JourType."' ".
											  "   UNION ".
											  "   SELECT SerialNo_Code from purchasesserialno ".
											  "   WHERE SerialNo_Code = '".$ItemSerialNo."' ".
											  "   AND Item_Code = '".$ItemCode."' ".
											  "   AND ifNull(SerialNo_Used,0) = 0 ".
											  "   AND Journal_Type = '".$JourType."' ".
											  "   AND AdjustmentDt_ID != '".$AdjDtId."' ".
											  " ) 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, AdjustmentDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, Warehouse_code, Logged_Code ) ".
											 " VALUES ( ".$SerId.", '".$JourType."', $AdjDtId, $AdjDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, Null, 1, '".$ItemWhCode."', '".$HTTP_SESSION_VARS['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  ";
		
		$result=mysql_query($query,$connection);
			
		if ($result){
			if($serialNb_quantity_setup == 1){
				$sub_query = " UPDATE Temp_AdjustmentDt SET  ".
					 " AdjustmentDt_Quantity = ifnull(AdjustmentDt_Quantity-1,0) 
					 Where AdjustmentDt_ID = '".$AdjDtId."' ";
				$sub_result=mysql_query($sub_query,$connection);
			}
			$message = "Delete serial no ".$ItemSerialNo." done successfully.";
			fcommit();
		}else{
			$message  = mysql_error(); 
			frollback();
		}			
		}
	/* } 
	else {	
		$message  = "Delete not allowed, Serial No ".$ItemSerialNo." is used. "; 
	}	 */
}


$message = ereg_replace(' ','%20',$message);
$ItemWhCode = ereg_replace('&','%26',$ItemWhCode);
header ("Location: Form_AdjustmentSerialNo.php?AdjustType=".$AdjustType."&SRItemCode=".$ItemCode."&SRItemQty=".$ItemQty."&SRWhCode=".$ItemWhCode."&SRAdjDtId=".$AdjDtId."&SRAdjId=".$AdjId."&perpage=".$PerPage."&SRHideSerialNo=".$HideSerialNo."&message=".$message."".$messagerr."".$messagerr1."".$messagerr2."".$messagerr3);


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

HERE;

?>