<?php

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


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

//User Access
$querylog=" select ifNull(login_belongstogrp,0) As FI_BelongsToGrp from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')  ";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}
							
//Read From Module Table
$querymodule=" Select ifNull(Module_NegativeQuantity,0) As Module_NegativeQuantity, ifNull(Module_DoubleUnit,0) As Module_DoubleUnit ".
             " From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}


//Read From Setup Table							
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2 ".
             " from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}

$PurId    		= $_POST['PurId']; if ($PurId == ""){$PurId = $_GET["PurId"];}
$Mode  			= $_POST['Mode']; if ($Mode == ""){$Mode = $_GET["Mode"];}
$SerialId  		= $_POST['SerialId']; if ($SerialId == ""){$SerialId = $_GET["SerialId"];}
$ItemSerialNo	= $_POST['ItemSerialNo']; if ($ItemSerialNo == ""){$ItemSerialNo = $_GET["ItemSerialNo"];}
$lstWarehouse	= $_POST['lstWarehouse']; if ($lstWarehouse == ""){$lstWarehouse = $_GET["lstWarehouse"];}
$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='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='Mode' id='Mode' value='$Mode' size='10' maxlength='40'/>		    
HERE;

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

	$query= " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0)  ".
			" From Purchasesserialno ".
			" where Purchasesserialno.SerialNo_Code =  '".$ItemSerialNo."' ".
			" And Purchasesserialno.Journal_Type =  'PI' ";
			//" And Not Exists ( Select 1 from purchasesserialno as purchasesserialno1 ".
     		//"                  Where purchasesserialno1.SerialNo_Code = purchasesserialno.SerialNo_Code ".
			//"                  And purchasesserialno1.Journal_Type != 'PI'  ".
			//"                 ) ";
			  
	$result = selectData($connection, $query);
	$SerialNoExists = $result[0];
	
	$IsAvailable = 1;
	$ErrorExists = 0;
	$SelectedSerialID = 0;
	$SelectedSerialQty     = 0;
	$SelectedSerialQtySold = 0;
	$SelectedItemCode      = '';
	$SelectedGrnDtID       = '';
	
	if ($SerialNoExists != '') {
		if ($lstWarehouse != '' && isset($lstWarehouse)) {
		
			$sub_query= " Select *  ".
						" From temp_purchasesserialno ".
						" where SerialNo_Code =  '".$ItemSerialNo."' ".
						" And Journal_Type in ('PR')";
			 //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) As SerialNo_QuantitySold, Item_Code, ifNull(PurchaseDt_ID, GrnDt_ID)  ".
							" From purchasesserialno ".
							" where SerialNo_Code =  '".$ItemSerialNo."' ".
							" And Warehouse_Code =  '".$lstWarehouse."' ".
							" And 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 (".$lstWarehouse.") . "; 
				}	
				else {
					
					if ($sub_row[1] == 1)
					{
						//In Edit Case And Delete A Serial For The Return GRN Then
					    // Allow To Add That Serial Again By Getting The Serials Related For That Return GRN (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, GrnPurchasesDt.Item_Code, GrnPurchasesDt.RelatedDt_ID As GrnDtID, 1 As OrderColumn ".
									" From purchasesserialno ".
									" Inner Join GrnPurchasesDt on GrnPurchasesDt.PurchaseDt_ID = Purchasesserialno.PurchaseDt_ID ".
									" Where GrnPurchasesDt.Purchase_ID = '".$PurId."' ".
									" And Purchasesserialno.SerialNo_Code =  '".$ItemSerialNo."' ".
									" And Purchasesserialno.Warehouse_Code =  '".$lstWarehouse."' ".
									" And Purchasesserialno.Journal_Type = 'PR' ".
									" And GrnPurchasesDt.Journal_Type = 'PR' ".
									" Union ".
									" Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0), ".
									" Item_Code, ifNull(PurchaseDt_ID, GrnDt_ID) As GrnDtID, 2 As OrderColumn  ".
									" From purchasesserialno ".
									" where SerialNo_Code =  '".$ItemSerialNo."' ".
									" And Warehouse_Code =  '".$lstWarehouse."' ".
									" And ifNull(SerialNo_Used,0) = 0 ".
									" And 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 (".$lstWarehouse."). "; 
						}  else {
							$SelectedSerialID      = $sub_row[0];
							$SelectedSerialQty     = $sub_row[3];
							$SelectedSerialQtySold = $sub_row[4];
							$SelectedItemCode      = $sub_row[5];
							$SelectedGrnDtID  	   = $sub_row[6];
							$IsAvailable = 1;
						}	
					
					} else {
						$SelectedSerialID      = $sub_row[0];
						$SelectedSerialQty     = $sub_row[3];
						$SelectedSerialQtySold = $sub_row[4];
						$SelectedItemCode      = $sub_row[5];
						$SelectedGrnDtID  	   = $sub_row[6];
						$IsAvailable = 1;
					}	

				}	
			}
			else {
				$IsAvailable = 0;
				$message  = "Insert not allowed, duplicate serial no (exists 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. "; 
	}
	
	
	if ($IsAvailable == 1)
	{
		
		$ErrorExists   = 0;
		$SelectedGrnID = '';
		
		$sub_query = " Select Purchase_ID From GrnPurchasesDt ".
		             " Where GrnPurchasesDt.PurchaseDt_ID = '".$SelectedGrnDtID."' ".
					 " And GrnPurchasesDt.Journal_Type = 'PI' ";
		$sub_result = selectData($connection, $sub_query);
		$SelectedGrnID = $sub_result[0];				   			 
				
		//Insert Master Table
		if ($PurId == "" && $ErrorExists == 0){
		
			fbegin();	
			$vPurId = get_nextsequence('Grn_Id','',$connection);	
			fcommit();
			
			$query = " INSERT INTO temp_grnpurchases (Purchase_id, Purchase_Reference, Purchase_Date, Purchase_Remark, Supplier_code, Currency_Code, Project_Code, Costcent_code, Purchase_InvoiceNo, Journaldt_valuedate, Purchase_Posted, ".
					 " Purchase_Taxable, Purchase_TaxableBase1, Purchase_TaxableBase2, Purchase_NoneTaxable, Purchase_NoneTaxableBase1, Purchase_NoneTaxableBase2, Purchase_DiscountAmount, Purchase_DiscountBase1, Purchase_DiscountBase2, ".
					 " Purchase_DiscountPer, Purchase_Total, Purchase_TotalBase1, Purchase_TotalBase2, Purchase_VAT, Purchase_VATBase1, Purchase_VATBase2, Purchase_VatChkbx, Purchase_Mode, Journal_Type, Journal_Id, Purchase_BelongsToGroup, ".
					 " Purchase_Acctax1, Purchase_Acctax2, Purchase_Acctax3, Purchase_Acctax4, Purchase_Acctax5, Purchase_DebitAccount, Purchase_DebitCur, Purchase_Import, Purchase_Type, Purchase_Grn, Purchase_PackingListRef, Purchase_FormMNo, ".
					 " Purchase_ReturnRef, User_Account, Logged_Code) ".
					 " SELECT $vPurId, Null, now(), Purchase_Remark, GrnPurchases.Supplier_code, GrnPurchases.Currency_Code, GrnPurchases.Project_Code, GrnPurchases.Costcent_code, Purchase_InvoiceNo, ADDDATE(now(), ifNull(Suppliers.Supplier_PaymentTerms,0)), 0,  ".
					 " 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, Purchase_VatChkbx, Purchase_Mode, 'PR', Null, Purchase_BelongsToGroup, Purchase_Acctax1, Purchase_Acctax2, Purchase_Acctax3, Purchase_Acctax4, ".
					 " Purchase_Acctax5, Purchase_DebitAccount, Purchase_DebitCur, Purchase_Import, Purchase_Type, Purchase_Grn, Purchase_PackingListRef, Purchase_FormMNo, Purchase_Reference, '".$_SESSION['useraccount']."', ".
					 " '".$_SESSION['useraccount']."' ".
					 " From GrnPurchases Inner Join Suppliers on GrnPurchases.Supplier_Code = Suppliers.Ledger_Number ".
					 " Where GrnPurchases.Purchase_ID = '".$SelectedGrnID."' ".
					 " And GrnPurchases.Journal_Type = 'PI' ";
			
			$result=mysql_query($query,$connection);
			//echo $query."<br>";exit;	
			
			if ($result){
				$PurId = $vPurId;
				fcommit();
			}else{
				$vPurId = "";
				$PurId  = "";
				$ErrorExists = 1;
				$message  = mysql_error(); 
				frollback();
			}

		
		}
		
		if ($PurId != ""  && $ErrorExists == 0)
		{
			
			$vPurId = $PurId;
			
			$sub_query = " SELECT Currency_Code, Purchase_Date, Supplier_code, Project_Code, Costcent_code, ".
						 " Date_Format(Purchase_Date,'%d/%m/%Y') As PurchaseDate  ".
						 " FROM Temp_GrnPurchases ".
						 " WHERE Purchase_ID = '".$vPurId."' ";
			$return_result = selectData($connection, $sub_query);
			
			$sub_query = " SELECT PurchaseDt_ID, ifNull(PurchaseDt_Quantity,0) ".
						 " FROM Temp_GrnPurchasesDt ".
						 " WHERE Temp_GrnPurchasesDt.RelatedDt_ID = '".$SelectedGrnDtID."' ".
						 " And Temp_GrnPurchasesDt.Journal_Type = 'PR' ";
			$sub_result = selectData($connection, $sub_query);
			//echo $sub_query; exit;
			
			//If Item Not Exists Then Insert Into Temp_GrnPurchasesDt Else Update
			if ($sub_result[0] == '') {
				
				$sub_query3 = " SELECT GrnPurchasesDt.Purchase_ID, ifNull(GrnPurchasesDt.Item_CodePacked, '') ".
							  " FROM GrnPurchasesDt ".
							  " WHERE GrnPurchasesDt.PurchaseDt_Id = '".$SelectedGrnDtID."' ".
							  " And GrnPurchasesDt.Journal_Type = 'PI' ";
				$sub_result3 = selectData($connection, $sub_query3);
			
					 
				$sub_query = " SELECT GrnPurchasesDt.PurchaseDt_Id, GrnPurchasesDt.Currency_Code, now(), GrnPurchasesDt.Supplier_Code, GrnPurchasesDt.Project_Code, GrnPurchasesDt.CostCent_Code, GrnPurchasesDt.Warehouse_Code, ".
							 " GrnPurchasesDt.Item_code, GrnPurchasesDt.Item_CodePacked, GrnPurchasesDt.Item_QtyPacked, ifNull(GrnPurchasesDt.PurchaseDt_Quantity,0), GrnPurchasesDt.PurchaseDt_Volume, ".
							 " GrnPurchasesDt.Item_UnitCoef, GrnPurchasesDt.Item_Unit2Qty, Purchasedt_Remark, GrnPurchasesDt.Purchase_invoiceNo, Purchasedt_DirectStkDbt, Purchasedt_DirectStkCrd, Purchasedt_DirectStkCur, ".
							 " Purchasedt_ItemAccount, PurchaseDt_VATAccount, GrnPurchasesDt.Item_CodeFinished, GrnPurchasesDt.Item_CodeSemiFinished, GrnPurchases.Purchase_Reference, Items.Item_WithSerialNo ".
							 " FROM GrnPurchasesDt Inner Join GrnPurchases on GrnPurchasesDt.Purchase_ID = GrnPurchases.Purchase_ID ".
							 " Inner Join Items on GrnPurchasesDt.Item_Code = Items.Item_Code ";
				
				//If Not A Pack	Item
				if ($sub_result3[1] == '')	$sub_query = $sub_query." WHERE GrnPurchasesDt.PurchaseDt_Id = '".$SelectedGrnDtID."' And GrnPurchasesDt.Journal_Type = 'PI' ";
				else $sub_query = $sub_query." WHERE GrnPurchasesDt.Purchase_ID = '".$sub_result3[0]."' And GrnPurchasesDt.Item_CodePacked = '".$sub_result3[1]."'  And GrnPurchasesDt.Journal_Type = 'PI' ";
				
				//echo $sub_query; exit;
				//$sub_row = selectData($connection, $sub_query);
				$sub_result=mysql_query($sub_query,$connection) or die(mysql_error());
				while($sub_row=mysql_fetch_array($sub_result)){
				
					$vPurDtQty  = 1;	
					
					//If Case Pack Item And The Item Is Not The One Being Scanned And Item WithSerial True Then Set The Qty To Zero
					if ($sub_row[8] != '' && !(empty($sub_row[8])) && $sub_row[0] != $SelectedGrnDtID && $sub_row[24] == 1)
						$vPurDtQty = 0;
					
					$vItemUnitCoef = 0;
					$vItemUnit2Qty = 0;
					if ($Module_DoubleUnit == 1) {
						$vItemUnitCoef = $sub_row[12];
						$vItemUnit2Qty = $vItemUnitCoef * $vPurDtQty;
					}
										
					fbegin();
					$vPurDtId = get_nextsequence('Grndt_Id','',$connection);	
					fcommit();
						
					fbegin();
					
					$sub_query1 = " INSERT INTO temp_grnpurchasesdt (PurchaseDt_id, Purchase_id, RelatedDt_Id, Currency_Code, Purchase_Date, Supplier_Code, Project_Code, CostCent_Code, Warehouse_Code, Item_code, Item_CodePacked, ". 
								  " Item_QtyPacked, PurchaseDt_Quantity, PurchaseDt_Volume, Item_UnitCoef, Item_Unit2Qty, Purchasedt_Remark, Purchase_invoiceNo, Purchasedt_DirectStkDbt, Purchasedt_DirectStkCrd, Purchasedt_DirectStkCur, ".
								  " Purchasedt_ItemAccount, PurchaseDt_VATAccount, Item_CodeFinished, Item_CodeSemiFinished, PurchaseDt_OldData, Journal_Type, Related_Ref, Logged_Code ) ".
								  " VALUES ( $vPurDtId, $vPurId, '".$sub_row[0]."', '".$return_result[0]."', '".$return_result[1]."', '".$return_result[2]."', '".$return_result[3]."', '".$return_result[4]."', '".$sub_row[6]."', '".$sub_row[7]."', ". 
								  " if('".$sub_row[8]."'='', Null, '".$sub_row[8]."'), '".$sub_row[9]."', $vPurDtQty, '".$sub_row[11]."', '".$vItemUnitCoef."', '".$vItemUnit2Qty."', '".$sub_row[14]."', '".$sub_row[15]."', '".$sub_row[16]."', '".$sub_row[17]."', '".$sub_row[18]."', ".
								  " '".$sub_row[19]."', '".$sub_row[20]."', '".$sub_row[21]."', '".$sub_row[22]."', 0, 'PR', '".$sub_row[23]."', '".$_SESSION['useraccount']."' ) ";
					
					//echo $sub_query1; exit;	
					$sub_result1=mysql_query($sub_query1,$connection);
				}
				
			} else {
			
				$vPurDtId      = $sub_result[0];
				$vPurDtQty     = $sub_result[1] + 1;

				$sub_query1 = " UPDATE temp_grnpurchasesdt SET ".
							  " PurchaseDt_Quantity = (ifNull(PurchaseDt_Quantity,0) + 1) ";
							  
				if ($Module_DoubleUnit == 1) {
					$sub_query1 = $sub_query1.", Item_Unit2Qty = ifNull(Item_UnitCoef,0)*(ifNull(PurchaseDt_Quantity,0)) ";
				}			  
				$sub_query1 = $sub_query1." WHERE PurchaseDt_ID = '".$vPurDtId."'";
				
				//echo $sub_query1; exit;				  
				$sub_result1=mysql_query($sub_query1,$connection);
			}
			
			
			if ($sub_result1){

				fbegin();
				$vSerId = get_nextsequence('Serial_Id','',$connection);	
				fcommit();
						
				$sub_query2 = " INSERT INTO temp_purchasesserialno (Serial_ID, Journal_Type, GrnDt_ID, PurchaseDt_ID, Item_Code, SerialNo_Code, SerialNo_Used, RelatedSerial_Id, SerialNo_Quantity, SerialNo_QuantitySold, Warehouse_code, Logged_Code ) ".
							  " VALUES ( ".$vSerId.", 'PR', $vPurDtId, $vPurDtId, '".$SelectedItemCode."', '".$ItemSerialNo."', 0, '".$SelectedSerialID."', '".$SelectedSerialQty."', 0, '".$lstWarehouse."', '".$_SESSION['useraccount']."' ) ";
								 
				//echo $sub_query2; exit;										 
				$sub_result2 = mysql_query($sub_query2,$connection);
					
				fcommit();
			}else{
				$ErrorExists = 1;
				$message  = mysql_error(); 
				frollback();
			}
			
		} //End if ($PurId != "" )
		
		
	} //End Case IsAvailable = True
} //End Case Insert

else if ($Mode == 'delete' && $SerialId != '') {
	
	$ErrorExists = 0;
	$IsAvailable = 1;
	
	$query = " SELECT ifNull(SerialNo_Used, 0) As SerialNo_Used, ifNull(PurchaseDt_ID, GrnDt_ID)  from temp_purchasesserialno ".
	         " WHERE Serial_ID = ".$SerialId;
			 //" AND Logged_Code = '".$_SESSION['useraccount']."' ";
	$result = selectData($connection, $query);
	$SerialNoUsed   = $result[0];
	$SerialGrnDt_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){
			$message = "Delete serial no (".$ItemSerialNo.") done successfully.";
			
			$query = " UPDATE temp_grnpurchasesdt 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 = '".$SerialGrnDt_Id."' ";
			//echo $query."<br>";exit;				
			if (!updateData($connection, $query, true))		 
				$message  = mysql_error(); 	
			
			
			fcommit();
		}else{
			$ErrorExists = 1;
			$message  = mysql_error(); 
			frollback();
		}			
	} 
	else {	
		$ErrorExists = 1;
		$message  = "Delete not allowed, Serial No ".$ItemSerialNo." is used. "; 
	}	
}


$message          = ereg_replace(' ','%20',$message);

$lstWarehouse     = ereg_replace('#','%23',$lstWarehouse);
$lstWarehouse     = ereg_replace('&','%26',$lstWarehouse);

header ( "Location: ./Form_RetGoodRecNoteAddSerial.php?lstWarehouse=".$lstWarehouse."&PPurId=".$PurId."&perpage=".$PerPage."&message=".$message );


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

HERE;

?>