<?php

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


include("config.inc.php");
include("Functions_Sales.php");
include("function.inc.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");

//Read From CashRegister Table
$queryCashRegister=" Select ifNull(CashRegister_RestSerialNo,0) As CashRegister_RestSerialNo ".
				   " From CashRegister ".
				   " Where CashRegister_Name = '".$_SESSION['cashregister']."' ";
$resultCashRegister=mysql_query($queryCashRegister,$connection) or die(mysql_error());
while($rowCashRegister=mysql_fetch_array($resultCashRegister)){
	extract($rowCashRegister);
}



$Mode		  = $_GET['Mode']; if ($Mode == ""){$Mode = $_POST["Mode"];}
$PerPage 	  = $_GET['PerPage'];if ($PerPage == ""){$PerPage = $_POST["PerPage"];}
$SerialId 	  = $_GET['SerialId'];if ($SerialId == ""){$SerialId = $_POST["SerialId"];}
$ItemSerialNo = $_GET['ItemSerialNo'];if ($ItemSerialNo == ""){$ItemSerialNo = $_POST["ItemSerialNo"];}
$HideSerialNo = $_GET['HideSerialNo'];if ($HideSerialNo == ""){$HideSerialNo = $_POST["HideSerialNo"];}

$RetId  	= $_GET["RetId"]; if ($RetId == ""){$RetId = $_POST["RetId"];}   
$RetDtId 	= $_GET["RetDtId"]; if ($RetDtId == ""){$RetDtId = $_POST["RetDtId"];}   



$sub_query = " Select Item_code, Warehouse_Code, abs(SaleDt_Quantity), if(Journal_Type='RI',0,1) As ItemReturn ".
			 " From Temp_RetailDt As RetailDt ".
		     " Where SaleDt_ID = '".$RetDtId."' ".
			 " And Sale_ID = '".$RetId."' ";
$sub_result = selectData($connection, $sub_query);
$ItemCode   = $sub_result[0];
$ItemWhCode = $sub_result[1]; 
$ItemQty    = $sub_result[2];
$ItemReturn = $sub_result[3];

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='ItemReturn' id='ItemReturn' value='$ItemReturn' size='10' maxlength='40'/>
<input type='hidden' name='RetDtId' id='RetDtId' value='$RetDtId' size='10' maxlength='40'/>
<input type='hidden' name='RetId' id='RetId' value='$RetId' 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'/>		    
HERE;

if ($Mode == 'insert' && $ItemSerialNo != '') {
	
	$query= " Select Count(SerialNo_Code) ".
			" From Temp_Purchasesserialno ".
			" where Temp_Purchasesserialno.RetailDt_ID = '".$RetDtId."' ";
			
	if ($ItemReturn == 0)		
		$query = $query." And Journal_Type = 'DI' ";
	else
		$query = $query." And Journal_Type = 'DR' ";
	
	$result = selectData($connection, $query);
	$SerialCount = $result[0];		
	if ($SerialCount == '') $SerialCount = 0;
	
			
	if ($SerialCount < $ItemQty ) {
		
		if ($CashRegister_RestSerialNo == 1) {
	
			if ($ItemReturn == 0) {
				$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') ";
			} else {			
				$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') ".
						" And Warehouse_Code =  '".$ItemWhCode."' ".
						" And ifNull(SerialNo_Used,0) = 0 ";
			}
			
					  
			$result = selectData($connection, $query);
			$SerialNoExists = $result[0];
			//echo $query; exit;
			$IsAvailable = 0;
			$SelectedSerialID = 0;
			$SelectedSerialQty     = 0;
			$SelectedSerialQtySold = 0;
			
			if ($ItemReturn == 0 && $SerialNoExists != '') {
				if ($ItemWhCode != '' && isset($ItemWhCode)) {
				
					$sub_query= " Select SerialNo_Code  ".
								" From temp_purchasesserialno ".
								" where Item_Code = '".$ItemCode."' ".
								" And SerialNo_Code =  '".$ItemSerialNo."' ".
								" And Warehouse_Code =  '".$ItemWhCode."' ".
								" And Journal_Type in ('DI','DR') ".
								" And ifNull(flagunique, 0) = 0  ".
								" Union ".
								" Select SerialNo_Code  ".
								" From Purchasesserialno ".
								" where Item_Code = '".$ItemCode."' ".
								" And SerialNo_Code =  '".$ItemSerialNo."' ".
								" And Warehouse_Code =  '".$ItemWhCode."' ".
								" And Journal_Type in ('DI') ".
								" And ifNull(flagunique, 0) = 0  ";
					 //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 RelatedSerial_Id As Serial_ID, ifNull(SerialNo_Used,0), Purchasesserialno.warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0) - 1 As SerialNo_QuantitySold, 1 As OrderColumn ".
									" From purchasesserialno ".
									" Inner Join RetailDt on Purchasesserialno.RetailDt_ID = RetailDt.SaleDt_ID ".
									" Where RetailDt.Sale_id = ".$RetId.
									" And Purchasesserialno.Item_Code = '".$ItemCode."' ".
									" And Purchasesserialno.SerialNo_Code =  '".$ItemSerialNo."' ".
									" And Purchasesserialno.Warehouse_Code =  '".$ItemWhCode."' ".
									" And Purchasesserialno.Journal_Type = 'DI' ".
									" 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 ".
									" where Item_Code = '".$ItemCode."' ".
									" And SerialNo_Code =  '".$ItemSerialNo."' ".
									" And Warehouse_Code =  '".$ItemWhCode."' ".
									" And Journal_Type in ('PI','DR','TR','AI','PD') ".
									" Order by OrderColumn ";
									
									
						//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)
							{
								$sub_query= " Select Serial_ID, ifNull(SerialNo_Used,0), Purchasesserialno.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') ";
									
								//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 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. is invoiced. "; 
					}	
				}
				else {
					$IsAvailable = 0;
					$message  = "Insert not allowed, warehouse is empty. "; 
				}	
			}
			else if ($ItemReturn == 1 && $SerialNoExists == ''){
				$sub_query= " Select *  ".
							" From temp_purchasesserialno ".
							" where Item_Code = '".$ItemCode."' ".
							" And SerialNo_Code =  '".$ItemSerialNo."' ".
							" And Warehouse_Code =  '".$ItemWhCode."' ".
							" And Journal_Type in ('DI','DR')";
				//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), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0), Item_Code  ".
								" From purchasesserialno ".
								" where Item_Code = '".$ItemCode."' ".
								" And SerialNo_Code =  '".$ItemSerialNo."' ".
								" And Warehouse_Code =  '".$ItemWhCode."' ".
								" And ifNull(SerialNo_Used,0) = 0 ".
								" And Journal_Type in ('PR','DI','TR','AR','PDT')";
					$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];
						$SelectedItemCode      = $sub_row[5];
						$IsAvailable = 1;
					}	
							
						

					
				}
				else {
					$IsAvailable = 0;
					$message  = "Insert not allowed, serial no. is invoiced. "; 
				}
				
			}
			else if ($ItemReturn == 1 && $SerialNoExists != ''){
				$IsAvailable = 0;
				$message  = "Insert not allowed, Serial No (".$ItemSerialNo.") already exists in the warehouse (".$ItemWhCode."). "; 
			}
			else if ($ItemReturn == 0 && $SerialNoExists == ''){
				$IsAvailable = 0;
				$message  = "Insert not allowed, Serial No (".$ItemSerialNo.") is not available. "; 
			}
		} else {
			$IsAvailable = 1;
			$SelectedSerialID 	   = 0;
			$SelectedSerialQty     = 0;
			$SelectedSerialQtySold = 0;
		
			$sub_query= " Select Item_Code  ".
						" From purchasesserialno ".
						" where SerialNo_Code =  '".$ItemSerialNo."' ";
			$sub_result = selectData($connection, $sub_query);
			$SelectedItemCode = $sub_result[0];								
		}
	}	
	else {
			$IsAvailable = 0;
			$message  = "Insert not allowed, Item Qty[$ItemQty] Exceeded. "; 
	}
	
	if ($IsAvailable == 1)
	{	
		if ($ItemReturn == 0) $vSerType = 'DI';
		else if ($ItemReturn == 1) $vSerType = 'DR';
								
		fbegin();
		$vSerId = get_nextsequence('Serial_Id','',$connection);	
		fcommit();
					
		fbegin();
		$query = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, RetailDt_ID, PurchaseDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, SerialNo_QuantitySold, Warehouse_code, Logged_Code ) ".
				 " VALUES ( ".$vSerId.", '".$vSerType."', $RetDtId, $RetDtId, '".$ItemCode."', '".$ItemSerialNo."', 0, '".$SelectedSerialID."', '".$SelectedSerialQty."', $SelectedSerialQtySold + 1, '".$ItemWhCode."', '".$_SESSION['useraccount']."' ) ";
									 
		$result=mysql_query($query,$connection);
						
		if ($result){
			
			$query = " UPDATE temp_retaildt SET ". 
					 " SerialNo_Code = '".$ItemSerialNo."' ".
					 " WHERE SaleDt_Id = '".$RetDtId."' ";
						 
			//echo $query."<br>";exit;				
			if (!updateData($connection, $query, true))		 
				$message  = mysql_error(); 	
			
			$message = "Serial no (".$ItemSerialNo.") inserted successfully.";
			fcommit();
		} else {
			$message  = mysql_error(); 
			frollback();
		}
	
	}
}

if ($Mode == 'delete' && $SerialId != '') {
	
	
	$query = " SELECT ifNull(SerialNo_Used, 0) As SerialNo_Used, ifNull(PurchaseDt_ID, RetailDt_ID) from temp_purchasesserialno ".
	         " WHERE Serial_ID = ".$SerialId;
			 //" AND Logged_Code = '".$_SESSION['useraccount']."' ";
	$result = selectData($connection, $query);
	$SerialNoUsed      = $result[0];
	$SerialRetailDt_Id = $result[1];
	
	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){
			$query = " UPDATE temp_retaildt SET ". 
					 " SerialNo_Code = Null ".
					 " WHERE SaleDt_Id = '".$SerialRetailDt_Id."' ";
			//echo $query."<br>";exit;				
			if (!updateData($connection, $query, true))		 
				$message  = mysql_error(); 	
				
			
			$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_RetailSerialNo.php?SRRetDtId=".$RetDtId."&SRRetId=".$RetId."&perpage=".$PerPage."&SRHideSerialNo=".$HideSerialNo."&message=".$message);



function fbegin()
{
	mysql_query("START TRANSACTION");
}
function fcommit(){
	mysql_query("COMMIT");
}
function frollback()
{
	mysql_query("ROLLBACK");
}

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

HERE;

?>