<?php
header("Content-type: text/html; charset=windows-1256");
session_start();

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

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

set_time_limit(0);

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


$querymodule=" Select ifNull(Module_Project,0) As Module_Project, ifNull(Module_CostCenter,0) As Module_CostCenter, 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);
}

$querylog="select ifNull(Login_negativeQty,0) As Login_negativeQty, 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);
}

$querydflwh="select warehouse_code as defaultwarehouse from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."' and ifNull(default_warehouse,0) = 1 ";
$resultdflwh=mysql_query($querydflwh,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultdflwh)){
  extract($rows);
}
if($defaultwarehouse == '') {
	$querydflwh="select Warehouse_code from adjustmentsetup ";
	$resultdflwh=mysql_query($querydflwh,$connection) or die(mysql_error());
	$rows=mysql_fetch_row($resultdflwh);
	$defaultwarehouse = $rows[0];

}
//echo $defaultwarehouse; exit;
//Projects List
$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " order by project.project_code"; 
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectrows   = mysql_fetch_row($projectresult);

if($projectrows[0] != '') $projectcode  = $projectrows[0];
else $projectcode  = 'NULL';


//Costcenter List
$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$_SESSION['dbLabel'].".loginstk on costcent.costcent_code like concat(loginstk.costcent_code,'%') ".
 			   " where user_account='".$_SESSION['useraccount']."' ".
 			   " order by costcent.costcent_code";
			  
$costcentresult = mysql_query($querycostcent,$connection) or die(mysql_error());
$costcentrows   = mysql_fetch_row($costcentresult);
if($costcentrows[0] != '') $costcentcode  = $costcentrows[0];
else $costcentcode  = 'NULL';

$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);
}

$fromitem 	 		 = $_POST['fromitem'];
$txtRequestedQty 	 = $_POST['txtRequestedQty'];
$txtprefixserialnb 	 = $_POST['txtprefixserialnb'];
$txtfromserialnb 	 = $_POST['txtfromserialnb']; 
$txttoserialnb		 = $_POST['txttoserialnb'];

$txtfromserialnb = trim ($txtfromserialnb);
$txttoserialnb = trim ($txttoserialnb);

$vAdjCurr = $base1;
$actionmode = $_GET['actionmode']; 
	
 if($actionmode == 'submitdetails' && $txtadjid == ''){
	$ErrorExists = 0;
	
	fbegin();
		$vAdjId = get_nextsequence('Adjustment_id','',$connection);
	fcommit();
	
	fbegin();
		$query = " INSERT INTO temp_adjustment (Adjustment_ID, Adjustment_Reference, Adjustment_Date, Currency_Code, Project_Code, 
					Costcent_code,Adjustment_RequestBy, Adjustment_Type, Adjustment_EntryType,  ".
				 "  Adjustment_Amount,Adjustment_AmountBase1, Adjustment_AmountBase2, Journal_Id, Adjustment_BelongsToGroup,  ".
				 "  Adjustment_Posted,Entry_Type, User_Account, Logged_Code, item_code ) ".
				 " VALUES ( ".$vAdjId.", Null, now(),  '".$vAdjCurr."', '$projectcode', '$costcentcode',".
				 "           '".$_SESSION['useraccount']."', 'AD', 'A',  ".
				 "         0, 0, 0, Null, $FI_BelongsToGrp, 0, 'A', '".$_SESSION['useraccount']."', ".
				 "          '".$_SESSION['useraccount']."', '$fromitem' )";


		$result=mysql_query($query,$connection);
		//echo $query."<br>";exit;

		if ($result){
			
			fcommit();

		}else{
			$vAdjId = "";
			$message  = mysql_error();
			$ErrorExists = 1;
			frollback();
		}
		if($vAdjId != '' && $ErrorExists == 0){
			fbegin();
			$vAdjDtId = get_nextsequence('AdjustmentDt_id','',$connection);
			fcommit();
			$querywhse = "select Item_WarehouseCode,Item_SellingPrice1,Item_SellingPrice1Base1,Item_SellingPrice1Base2 from items where Item_Code='$fromitem'";
			$reswhse = mysql_query($querywhse,$connection);
			$rowwhse = mysql_fetch_row($reswhse);
			$lstWarehouse = $defaultwarehouse;
			
			$vAdjDtPrice =  $rowwhse[1];
			$vAdjDtPriceBase1 =  $rowwhse[2];
			$vAdjDtPriceBase2 =  $rowwhse[3];
			
			fbegin();
				$query = " INSERT INTO temp_adjustmentdt (AdjustmentDt_ID, Adjustment_ID, Currency_Code, Adjustment_Date, Project_Code, CostCent_Code, Warehouse_Code, ".
						 " Item_code,  AdjustmentDt_Quantity, AdjustmentDt_Price, AdjustmentDt_PriceBase1, AdjustmentDt_PriceBase2, ".
						 " AdjustmentDt_ExistingQty, AdjustmentDt_PhysicalQty, Journal_Type,    ".
						 " AdjustmentDt_Posted, Item_UnitCoef, Item_Unit2Qty, Logged_Code, adjustmentdt_heatno ) ".
						 " VALUES ( ".$vAdjDtId.", ".$vAdjId.", '".$vAdjCurr."', now(), '$projectcode', '$costcentcode', '".ereg_replace("'", "\'", trim($lstWarehouse))."', ".
						 "          '".$fromitem."', -".($txtRequestedQty).", $vAdjDtPrice, $vAdjDtPriceBase1, $vAdjDtPriceBase2, 0, 0, 'AR',  ".
						 "           0, 0, 0,  ".
						 "          '".$_SESSION['useraccount']."', '".$adjustmentdt_heatno."' ) ";

				$result=mysql_query($query,$connection);
				//echo $query."<br>";exit;

				if ($result){
					fcommit();
					if($txtfromserialnb != '' && $txttoserialnb != ''){
						for($i=$txtfromserialnb;$i<=$txttoserialnb;$i++){
							
							$IsAvailable = 1;
							if ($lstWarehouse != '') {
								$query= " Select Serial_ID, ifNull(SerialNo_Used,0), warehouse_code, ifNull(SerialNo_Quantity,1), ifNull(SerialNo_QuantitySold,0)  ".
										" From purchasesserialno ".
										" where Item_Code = '".$fromitem."' ".
										" And SerialNo_Code =  '".$txtprefixserialnb."".$i."' ".
										" And Journal_Type in ('PI','DR','TR','AI','PD')";
										  
								$result = selectData($connection, $query);
								$SerialNoExists = $result[0];
								$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 != '".$vAdjDtId."' ".
												 "   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 = '".$fromitem."' ".
													" And SerialNo_Code =  '".$txtprefixserialnb."".$i."' ".
													" And Warehouse_Code =  '".$lstWarehouse."' ".
													" 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;
											$ErrorExists = 1;
											$message  = "Insert not allowed, Serial No is not available in the warehouse (".$lstWarehouse.") . "; 
										}	
										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 = '".$fromitem."' ".
															" And SerialNo_Code =  '".$txtprefixserialnb."".$i."' ".
															" And Warehouse_Code =  '".$lstWarehouse."' ".
															" 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;
													$ErrorExists = 1;
													$message  = "Insert not allowed, Serial No is used in warehouse (".$lstWarehouse."). "; 
												}  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;
										$ErrorExists = 1;
										$message  = "Insert not allowed, duplicate serial no. <br>"; 
									}	
								} else {
									$IsAvailable = 0;
									$ErrorExists = 1;
									$message  = "Insert not allowed, Serial No is not available."; 
								}
							} else {
								$IsAvailable = 0;
								$ErrorExists = 1;
								$message  = "Insert not allowed, warehouse is empty. "; 
							}
							
						if ($IsAvailable == 1 && $ErrorExists == 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, SerialNo_QuantitySold, Warehouse_code, Logged_Code ) ".
									" VALUES ( ".$SerId.", 'AR', $vAdjDtId, $vAdjDtId, '".$fromitem."', '".$txtprefixserialnb."".$i."', 0, ".$SelectedSerialID.", '".$SelectedSerialQty."', $SelectedSerialQtySold + 1, '".$lstWarehouse."', '".$_SESSION['useraccount']."' ) ";		 
							//echo $query;
							$result=mysql_query($query,$connection);
				
							if ($result){
								$message = "Add serial no done successfully.";
								fcommit();
							}else{
								$message  = mysql_error(); 
								$ErrorExists = 1;
								frollback();
								}
						} 
						else if ($IsAvailable == 0 && $ErrorExists == 1) {
							break;
							}						
						}
						
					}
					
					
					
				}else{
					$vAdjDtId = '';
					$ErrorExists = 1;
					$message  = mysql_error();
					frollback();
				} 
			if($vAdjDtId != '' && $ErrorExists == 0){
				$querypack = "select Item_CodePackDt,ifnull(Packdt_cost,0),PackDt_Quantity, ifnull(PackDt_CostPercentage,0) from packdt where item_code='$fromitem';";
				$respack = mysql_query($querypack,$connection);
				while($rwpack = mysql_fetch_row($respack)){
					fbegin();
			$vAdjDtIdout = get_nextsequence('AdjustmentDt_id','',$connection);
			fcommit();
			$querywhse = "select Item_WarehouseCode,Item_SellingPrice1,Item_SellingPrice1Base1,Item_SellingPrice1Base2 
							from items where Item_Code='$fromitem'";
			$reswhse = mysql_query($querywhse,$connection);
			$rowwhse = mysql_fetch_row($reswhse);
			$lstWarehouse = $defaultwarehouse;
			if($rwpack[3] == 0) $rwpack[3] = 100;
			if($rwpack[1] == 0){
			$voAdjDtPrice =  $rowwhse[1]*($rwpack[3]/100);
			$voAdjDtPriceBase1 =  $rowwhse[2]*($rwpack[3]/100);
			$voAdjDtPriceBase2 =  $rowwhse[3]*($rwpack[3]/100);
			}
			
			$txtQtydt = $txtRequestedQty * $rwpack[2];
			$voAdjDtPrice =  $voAdjDtPrice/$rwpack[2] ;
			$voAdjDtPriceBase1 =  $voAdjDtPriceBase1/$rwpack[2] ;
			$voAdjDtPriceBase2 =  $voAdjDtPriceBase2/$rwpack[2] ;
			
			fbegin();
					$query = " INSERT INTO temp_adjustmentdt (AdjustmentDt_ID, Adjustment_ID, Currency_Code, Adjustment_Date, Project_Code, CostCent_Code, Warehouse_Code, ".
							 " Item_code,  AdjustmentDt_Quantity, AdjustmentDt_Price, AdjustmentDt_PriceBase1, AdjustmentDt_PriceBase2, ".
							 " AdjustmentDt_ExistingQty, AdjustmentDt_PhysicalQty, Journal_Type,    ".
							 " AdjustmentDt_Posted, Item_UnitCoef, Item_Unit2Qty, Logged_Code, adjustmentdt_heatno ) ".
							 " VALUES ( ".$vAdjDtIdout.", ".$vAdjId.", '".$vAdjCurr."', now(), '$projectcode', '$costcentcode', '".ereg_replace("'", "\'", trim($lstWarehouse))."', ".
							 "          '".$rwpack[0]."', $txtQtydt, $voAdjDtPrice, $voAdjDtPriceBase1, $voAdjDtPriceBase2, 0, 0, 'AI',  ".
							 "           0, 0, 0,  ".
							 "          '".$_SESSION['useraccount']."', '".$adjustmentdt_heatno."' ) ";

					$result=mysql_query($query,$connection);
					//echo $query."<br>";exit;

					if ($result){
						fcommit();
						
					}else{
						$vAdjDtIdout = '';
						$ErrorExists = 1;
						$message  = mysql_error();
						frollback();
					}
				}
			}
			if($ErrorExists == 1){
				$querydel = "delete from temp_adjustmentdt where Adjustment_ID = $vAdjId";
				$resdel = mysql_query($querydel,$connection);
			}
		}
 }

else if($actionmode == 'cancel'){
	$query = "delete from temp_purchasesserialno where AdjustmentDt_ID in (select AdjustmentDt_Id from temp_adjustmentdt where Adjustment_Id = $txtadjid )";
	$result = mysql_query($query,$connection);
	
	$query = "delete from temp_adjustmentdt where Adjustment_Id = $txtadjid";
	$result = mysql_query($query,$connection);
	
	$query1 = "delete from temp_adjustment where Adjustment_Id = $txtadjid";
	$result1 = mysql_query($query1,$connection);
}
else if($actionmode == 'saveadjustment'){


	$SMode = 'I';
	$query = "Select Count(*) From Temp_Adjustment where Adjustment_ID = '".$txtadjid."'";
			//echo $query; exit;
			$result = selectData($connection, $query);
			$ExistInTemp = $result[0];

			//Check If Record Exists In Temp Tables
			if ($ExistInTemp == 1) {
			fbegin();
				$query = "SELECT CopyData_TempAdjustment('".$SMode."', ".$txtadjid.", '".$_SESSION['useraccount']."') FROM Dual ";
				//echo $query; exit;
				$result = selectData($connection, $query);
				$result = $result[0];
				if ($result != 0){
					fcommit();
					fbegin();

						$vAdjRef = get_nextsequence('Adjustment_Reference',$vProjCostCent,$connection);

						fcommit();

						$query = " UPDATE Adjustment SET ".
								 " Adjustment_Reference = $vAdjRef ".
								 " WHERE Adjustment_id = $txtadjid ";

						if (!updateData($connection, $query, true))
							$message  = mysql_error();
					} else {
					$message  = 'Error In CopyData_TempAdjustment Function';
					frollback();
					}
			}
}
if($actionmode == 'submitdetails' && $txtadjid != ''){
		
		$query = "select AdjustmentDt_Id,ifnull(PackDt_Quantity,1)
					from temp_adjustmentdt
					left join packdt on packdt.Item_CodePackDt =temp_adjustmentdt.item_code
					where Adjustment_Id=$txtadjid";
		$res = mysql_query($query,$connection);
		while($rwdt = mysql_fetch_row($res)){  
		$queryupdate = "update temp_adjustmentdt set AdjustmentDt_Quantity = $rwdt[1]*$txtRequestedQty where AdjustmentDt_Id=$rwdt[0]";
		$resupdate = mysql_query($queryupdate,$connection);
		}
		$vAdjId = $txtadjid;
}
$message = ereg_replace(" ", "%20", $message);
if($actionmode == 'submitdetails')
header ("Location: ./Form_itemTransfer.php?txtadjid=$vAdjId&mainaction=submitrecord&message=".$message);
else if($actionmode == 'cancel' || $actionmode == 'saveadjustment')
header ("Location: ./Form_itemTransfer.php");
?>