<?php

session_start();


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

include("config.inc.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($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");

$query1="select base1,base2 from setup";
$result1=mysql_query($query1,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($result1)){
extract($rows);

}


$cltcar_name=ereg_replace("'", "\'", trim($cltcar_name));
$shipment_remark=ereg_replace("'", "\'", trim($shipment_remark));


/// Load from sales
if($mainmode=='loaddeliveries'){

	$query_count="select count(*) from sales
				left join clients on clients.ledger_number=sales.client_code
				where sale_posted=0 and sales.cltcar_code='".$cltcar_code."'";

	$resultcount = mysql_query($query_count,$connection);			
	$row_count=mysql_fetch_row($resultcount);	
	
	$count=$row_count[0];
	
	
	if($count==0){
	
		$message='Load Not Allowed . No Invoices for this Driver ';
	
	}
	
		else{

		
		fbegin();	
		$resultvars = mysql_query("set @result :=0");
		$query_nextsequence="call NextSequence1('shipmentrouting_reference','',@result)";
		$result = mysql_query($query_nextsequence,$connection);
		
		$queryresult = " select @result from dual";
		$resultqueryresult = mysql_query($queryresult,$connection);
		$row=mysql_fetch_row($resultqueryresult);
		$PReference=$row[0];
		fcommit();
		

		
		if($PReference!=''){
			fbegin();	
			//Insert into Master Table
			$query_load="insert into shipments(shipment_reference,cltcar_code,shipment_DriverName,currency_code,shipment_date)
						values('".$PReference."','".$cltcar_code."','".$cltcar_name."','".$base2."',now())";
						//echo$query_load;


			$resultload = mysql_query($query_load,$connection);

			$shipment_id=mysql_insert_id();

		//echo$shipment_id;

			//Insert into Detail from sales 
			if($resultload  && $shipment_id!=''){



				$query_loaddt="insert into shipmentsdt(shipment_id,client_code,Sale_Reference,Delivery_reference,area_code)
							select '".$shipment_id."',client_code,sale_reference,Sale_RelatedID,clients.area_code from sales
								left join clients on clients.ledger_number=sales.client_code
								where sale_posted=0 and sales.cltcar_code='".$cltcar_code."'";
//echo$query_loaddt;				
$resultloaddt = mysql_query($query_loaddt,$connection) or die(mysql_error());
				$message= mysql_error();
				
				if($resultloaddt && $message==''){
				
					$update_sales="update sales set sale_posted=1 where sale_reference in (select sale_reference from shipmentsdt where shipment_id='".$shipment_id."')";
					$resultupdate = mysql_query($update_sales,$connection);
					$message= mysql_error();
					$i=1;
					if($resultupdate && $message==''){
					
						//Getting number of clients in detail
						$query_countClient="select distinct client_code from shipmentsdt where shipment_id='".$shipment_id."'";
						$resultcountClient = mysql_query($query_countClient,$connection);
						$numberClient=mysql_num_rows($resultcountClient);
						
						//echo$numberClient;exit;
						
						///Compaing each amount per client based on the visit number
						for($i=1;$i<=$numberClient;$i=$i+1){
							$query_area="select distinct client_code,shipmentsdt.area_code as area,ifnull(Area_Tariff,0)
										from shipmentsdt
										left join areatransp on areatransp.area_code=shipmentsdt.area_code
										where shipment_id='".$shipment_id."' and (visitnumber is null or visitnumber='')
										and upper(CarType_code)=upper('".$carType_code."')  and area_tarrifvisit=$i 
										order by Area_Tariff desc limit 1"; 
										
							//echo$query_area."<BR>";
							$resultarea = mysql_query($query_area,$connection);
							$rowarea=mysql_fetch_row($resultarea);
							
							//Updating price + number of visit
							$update_Shipment="update shipmentsdt set visitnumber=$i,
												 shipment_amount='".$rowarea[2]."' 
												 where client_code='".$rowarea[0]."'
											and shipment_id='".$shipment_id."'";
							$resultUpdate = mysql_query($update_Shipment,$connection);				
							
							$update_Shipment="update shipments set shipment_totalamount=ifnull(shipment_totalamount,0)+'".$rowarea[2]."'
											where shipment_id='".$shipment_id."'";
							$resultUpdate = mysql_query($update_Shipment,$connection);	
							
						}
						
						//Getting Max number of visit filled 
						$query_maxVisit="select max(visitnumber) from shipmentsdt where shipment_id='".$shipment_id."'";
						$resultMax = mysql_query($query_maxVisit,$connection);		
						$rowMax=mysql_fetch_row($resultMax);
						$MaxVisit=$rowMax[0];
						
						/// Getting client with no visit number
						$query_visits="select  distinct client_code
										from shipmentsdt
										where shipment_id='".$shipment_id."' and (visitnumber is null or visitnumber='')"; 
						
						$resultvisits = mysql_query($query_visits,$connection);	
						while($rowvisit=mysql_fetch_row($resultvisits)){
							
							$MaxVisit=$MaxVisit+1;
							$update_Visit="update shipmentsdt set visitnumber=$MaxVisit
										where shipment_id='".$shipment_id."'
										and client_code='".$rowvisit[0]."'
										";
						
							$resultUpdateVisit = mysql_query($update_Visit,$connection);	
						}
						
					}
				}
				else frollback();
			}
			else frollback();
		}
		if($message=='')
			fcommit();
		else frollback();
	
	}

}



if($mainmode=='confirmshipment' || $mainmode=='saveshipment'){

	$query_dt="select shipmentdt_id,client_code,client_address,sale_reference,delivery_reference,ledger_name,shipment_amount,area_name,shipmentsdt.area_code from shipmentsdt 
				left join clients on clients.ledger_number=shipmentsdt.client_code
				left join area on area.area_code=shipmentsdt.area_code
				where shipment_id='".$shipment_id."'
				order by  visitnumber asc,client_code";
	//echo$query_dt;		
	$resultdt=mysql_query($query_dt,$connection) or die(mysql_error());
	$i=1;
			while($rowsdt=mysql_fetch_array($resultdt)){
			
			    $area_code='area_code'.$i;
				$area_name='area_name'.$i;
				$amount='amount'.$i;
				$visitorder='visitorder'.$i;
				
				if($clientcode!=$rowsdt[1]){
					$query_update="update shipmentsdt set area_code='".$$area_code."',
														shipment_amount='".$$amount."',
														visitnumber='".$$visitorder."'
														where shipment_id='".$shipment_id."'
														
														and client_code='".$rowsdt[1]."'";
									//		echo$query_update;exit;
					$resultUpdate = mysql_query($query_update,$connection);
					$message= mysql_error();
					$total_amount=$total_amount+$$amount;
				}
				
				
				$clientcode=$rowsdt[1];
				$i=$i+1;
			}
			
	
			$update_price="update shipments set shipment_totalamount='".$total_amount."',
									shipment_DriverName='".$cltcar_name."'
									
									where shipment_id='".$shipment_id."'";
								
			$resultprice = mysql_query($update_price,$connection);		
}

if($mainmode=='confirmshipment'){

	// Count if car code exists in trans table
	$query_TransId="select Transp_ID from transp where Transp_CarCode='".$cltcar_code."' and transp_posted=0";
	$resultTrans = mysql_query($query_TransId,$connection);
	$row_Id=mysql_fetch_row($resultTrans);
	$TransPId=$row_Id[0];
	
	//echo$TransPId;echo$query_TransId;exit;
	
	if($TransPId==''){
		fbegin();	
		$resultvars = mysql_query("set @result :=0");
		$query_nextsequence="call NextSequence1('Transp_ID','',@result)";
		$result = mysql_query($query_nextsequence,$connection);
		
		$queryresult = " select @result from dual";
		$resultqueryresult = mysql_query($queryresult,$connection);
		$row=mysql_fetch_row($resultqueryresult);
		$TransPId=$row[0];
		fcommit();
		
		fbegin();	
		$resultvars = mysql_query("set @result :=0");
		$query_nextsequence="call NextSequence1('Transp_Reference','',@result)";
		$result = mysql_query($query_nextsequence,$connection);
		
		$queryresult = " select @result from dual";
		$resultqueryresult = mysql_query($queryresult,$connection);
		$row=mysql_fetch_row($resultqueryresult);
		$TransReference=$row[0];
		fcommit();	
		
		
	
		$insert_transpMaster="insert into transp(Transp_ID,Transp_date,Transp_Reference,Currency_Code,Driver_number,Transp_CarCode,transp_posted) ".
						" select '".$TransPId."',now(),'".$TransReference."',currency_code,cltcar.ledger_number,shipments.cltcar_code,'0' from shipments ".
						" inner join cltcar on cltcar.cltcar_code=shipments.cltcar_code ".
						" where shipment_id='".$shipment_id."'";
						
			//echo$insert_transpMaster;exit;
		$resultinsertMaster = mysql_query($insert_transpMaster,$connection);
		$message= mysql_error();	
	}
	
		
	if($TransPId!='' && $message==''){
	//Insert into Details
	
		$query1="select currency_code as currency from shipments where shipment_id='".$shipment_id."'";
		$result1=mysql_query($query1,$connection) or die(mysql_error());
		while($rows=mysql_fetch_array($result1)){
		extract($rows);

		}
		
		/// Currency Rates

			$query = "SELECT Curdt_Rate FROM curdt WHERE Currency_Code = '".$currency."' AND Curdt_date = (SELECT MAX(Curdt_date) FROM curdt WHERE Currency_Code = '".$currency."' )";
			$result = mysql_query($query,$connection);
			$rowrate=mysql_fetch_row($result);
			$rate_dt = $rowrate[0];

			
			$query = "SELECT Curdt_Rate FROM curdt WHERE Currency_Code = '".$base1."' AND Curdt_date = (SELECT MAX(Curdt_date) FROM curdt WHERE Currency_Code = '".$base1."' )";
			$result = mysql_query($query,$connection);
			$rowrate1=mysql_fetch_row($result);
			$rate_dt1 = $rowrate1[0];
			
			
			$query = "SELECT Curdt_Rate FROM curdt WHERE Currency_Code = '".$base2."' AND Curdt_date = (SELECT MAX(Curdt_date) FROM curdt WHERE Currency_Code = '".$base2."' )";
			$result = mysql_query($query,$connection);
			$rowrate2=mysql_fetch_row($result);
			$rate_dt2 = $rowrate2[0];	
		
			
			
		//Getting owner cod
		$query_owner="select cltcar.Cltcar_Owner from cltcar where cltcar_code='".$cltcar_code."'";
		$resultowner = mysql_query($query_owner,$connection);
		$row_owner=mysql_fetch_row($resultowner);
		$owner_code=$row_owner[0];
		
		
		//Selecting detail of shipment to insert into transpdt
		$query_dt="select shipmentsdt.area_code,Project_Code,CostCenter_Code,shipmentsdt.Delivery_reference,shipmentsdt.shipment_amount,shipmentsdt.visitnumber,shipments.shipment_date,shipments.Shipment_reference,client_code as Code,
  (select count(*) from shipmentsdt where  shipment_id='".$shipment_id."' and client_code=Code) ".
						" from shipmentsdt ".
						" left join clients on clients.ledger_number=shipmentsdt.client_code ".
						" inner join shipments on shipments.shipment_id=shipmentsdt.shipment_id ".
						" where shipments.shipment_id='".$shipment_id."'";
		$resultdt = mysql_query($query_dt,$connection);
		while($row_dt=mysql_fetch_row($resultdt)){

			// Next sequence for dt ID
			fbegin();	
			$resultvars = mysql_query("set @result :=0");
			$query_nextsequence="call NextSequence1('TranspDT_ID','',@result)";
			$result = mysql_query($query_nextsequence,$connection);
			
			$queryresult = " select @result from dual";
			$resultqueryresult = mysql_query($queryresult,$connection);
			$row=mysql_fetch_row($resultqueryresult);
			$TranspDT_ID=$row[0];
			fcommit();	
			
			
			if($row_dt[0]=='')
				$areacode='null';
			else $areacode="'$row_dt[0]'";
			
			///Amount base1 , base2 
		
			$TranspDT_Tariff=$row_dt[4]/$row_dt[9];
			$TranspDT_Tariff1=($TranspDT_Tariff*$rate_dt)/$rate_dt1;
			$TranspDT_Tariff2=($TranspDT_Tariff*$rate_dt)/$rate_dt2;
		
			if($TranspDT_Tariff1=='')
				$TranspDT_Tariff1=0;
				
			if($TranspDT_Tariff2=='')
				$TranspDT_Tariff2=0;	
				
			$insert_TransDt="insert into transpdt(TranspDT_ID,Transp_ID,Area_Code,Project_Code,CostCenter_Code,Delivery_Reference,TranspDT_Tariff,transpdt_debit,TranspDT_Tariff1,TranspDT_Tariff2,transpdt_tarrifvisit,transpdt_date,TranspDT_Remark) values( ".
							" $TranspDT_ID,$TransPId,$areacode,'".$row_dt[1]."','".$row_dt[2]."','".$row_dt[3]."','".$TranspDT_Tariff."','".$owner_code."',$TranspDT_Tariff1,$TranspDT_Tariff2,'".$row_dt[5]."','$row_dt[6]',concat('SR: ',$row_dt[7]))";
						//	echo$insert_TransDt."<BR>";
			$resultTransDt = mysql_query($insert_TransDt,$connection);
			$message= mysql_error();	

			
		}
		
		$query_update="update transp set Transp_AmountSum=(select sum(TranspDT_Tariff) from transpdt where transp_id='".$TransPId."')
										where transp_id='".$TransPId."'";
									///	echo$query_update;exit;
		$resultUpdate = mysql_query($query_update,$connection);
		$message= mysql_error();
		
	}
	
			
	
	$query_update="update shipments set shipment_confirmed=1
										where shipment_id='".$shipment_id."'";
								//		echo$query_update;exit;
	$resultUpdate = mysql_query($query_update,$connection);
	$message= mysql_error();
	
	if($message=='')
		fcommit();	
	else frollback();
}



if($mainmode=='delete' || $mainmode=='deletepending'){
	$query_delete="delete from shipments where shipment_id='".$shipment_id."'";
	$resultdelete = mysql_query($query_delete,$connection);
	$message= mysql_error();
}


if($mainmode=='loaddeliveries2'){
$carcode = "";
$routingref = "";
$i=0;	
	$query_count="select sale_id,sale_ftsisrf,sales.cltcar_code,sale_Shippedto,cltcar.cltcar_name,cltcar.carType_code from sales join cltcar on sales.cltcar_code = cltcar.cltcar_code where sales.cltcar_code is not null and sale_posted = 0 and sale_date < now() and sales.cltcar_code <> 0 order by cltcar_code,sale_ftsisrf;";
//echo $query_count ; exit;
	$resultcount = mysql_query($query_count,$connection);			
while ($row_count=mysql_fetch_row($resultcount)){
	
/*
if ($i ==0 ) {
$carcode = $row_count[2];
$routingref = $row_count[1];

		
}*/

if ($row_count[1] != $routingref or $row_count[2] != $carcode  )
		{
		fbegin();	
		$resultvars = mysql_query("set @result :=0");
		$query_nextsequence="call NextSequence1('shipmentrouting_reference','',@result)";
		$result = mysql_query($query_nextsequence,$connection);
		
		$queryresult = " select @result from dual";
		$resultqueryresult = mysql_query($queryresult,$connection);
		$row=mysql_fetch_row($resultqueryresult);
		$PReference=$row[0];
		fcommit();
		

		
		if($PReference!=''){
				
			//Insert into Master Table
			$query_load="insert into shipments(shipment_reference,cltcar_code,currency_code,shipment_date,shipmentrouting_reference,area_code,shipment_drivername)
						values('".$PReference."','".$row_count[2]."','".$base2."',now(),'".$row_count[1]."','".$row_count[3]."','".$row_count[4]."')";
				//	echo$query_load;exit;


			$resultload = mysql_query($query_load,$connection);

			$shipment_id=mysql_insert_id();


			//Insert into Detail from sales 
			if($resultload  && $shipment_id!=''){



				$query_loaddt="insert into shipmentsdt(shipment_id,client_code,Sale_Reference,Delivery_reference,area_code)
							select '".$shipment_id."',sales.client_code,sale_reference,Sale_RelatedID,sales.sale_shippedto from sales
								
								where sale_posted=0 and sales.cltcar_code='".$row_count[2]."' and sale_ftsisrf ='".$row_count[1]."'";
							//echo$query_loaddt;exit;	

$resultloaddt = mysql_query($query_loaddt,$connection) or die(mysql_error());
				$message= mysql_error();
				
				if($resultloaddt && $message==''){
				$update_sales = "update sales,shipmentsdt set sale_posted=1 where shipmentsdt.sale_reference = sales.sale_reference and  shipment_id='".$shipment_id."'" ;
			
					//$update_sales="update sales set sale_posted=1 where sale_reference in (select sale_reference from shipmentsdt where shipment_id='".$shipment_id."')";
					$resultupdate = mysql_query($update_sales,$connection);
					$message= mysql_error();
					$i=1;
					if($resultupdate && $message==''){
					
						//Getting number of clients in detail
						$query_countClient="select distinct client_code from shipmentsdt where shipment_id='".$shipment_id."'";
						$resultcountClient = mysql_query($query_countClient,$connection);
						$numberClient=mysql_num_rows($resultcountClient);
						
						//echo$numberClient;exit;
						
						///Compaing each amount per client based on the visit number
						for($i=1;$i<=$numberClient;$i=$i+1){
							$query_area="select distinct client_code,shipmentsdt.area_code as area,ifnull(Area_Tariff,0)
										from shipmentsdt
										left join areatransp on areatransp.area_code=shipmentsdt.area_code
										where shipment_id='".$shipment_id."' and (visitnumber is null or visitnumber='')
										and upper(CarType_code)=upper('".$row_count[5]."')  and area_tarrifvisit=$i 
										order by Area_Tariff desc limit 1"; 
										
						//	echo$query_area."<BR>";exit;
							$resultarea = mysql_query($query_area,$connection);
							$rowarea=mysql_fetch_row($resultarea);
							
							//Updating price + number of visit
							$update_Shipment="update shipmentsdt set visitnumber=$i,
												 shipment_amount='".$rowarea[2]."' 
												 where client_code='".$rowarea[0]."'
											and shipment_id='".$shipment_id."'";
							$resultUpdate = mysql_query($update_Shipment,$connection);				
							
							$update_Shipment="update shipments set shipment_totalamount=ifnull(shipment_totalamount,0)+'".$rowarea[2]."'
											where shipment_id='".$shipment_id."'";
							$resultUpdate = mysql_query($update_Shipment,$connection);	
							
						}
						
						//Getting Max number of visit filled 
						$query_maxVisit="select max(visitnumber) from shipmentsdt where shipment_id='".$shipment_id."'";
						$resultMax = mysql_query($query_maxVisit,$connection);		
						$rowMax=mysql_fetch_row($resultMax);
						$MaxVisit=$rowMax[0];
						
						/// Getting client with no visit number
						$query_visits="select  distinct client_code
										from shipmentsdt
										where shipment_id='".$shipment_id."' and (visitnumber is null or visitnumber='')"; 
						
						$resultvisits = mysql_query($query_visits,$connection);	
						while($rowvisit=mysql_fetch_row($resultvisits)){
							
							$MaxVisit=$MaxVisit+1;
							$update_Visit="update shipmentsdt set visitnumber=$MaxVisit
										where shipment_id='".$shipment_id."'
										and client_code='".$rowvisit[0]."'
										";
						
							$resultUpdateVisit = mysql_query($update_Visit,$connection);	
						}
						
					}
				}
				else frollback();
			}
			else frollback();
		}
		if($message=='')
			fcommit();
		else frollback();
}



$i = $i + 1;
$carcode = $row_count[2];
$routingref = $row_count[1];
		
}	

}







// E Reg replace
$message = ereg_replace(' ','%20',$message);
echo $message;

if($message=='' && $mainmode=='loaddeliveries')
	header ("Location: ./Form_ShipmentRouting.php?mainmode=editmode&shipment_id=$shipment_id&showprint=1&message=".$message);	
if($message=='' && $mainmode=='loaddeliveries2')
	header ("Location: ./Form_ShipmentRouting_Pending.php?mainmode=editmode&shipment_id=$shipment_id&showprint=1&message=".$message);	
	
else if($message!='' && $mainmode=='loaddeliveries')
header ("Location: ./Form_ShipmentRouting.php?shipment_id=$shipment_id&showprint=0&message=".$message);	

else
if($mainmode=='saveshipment')
	header ("Location: ./Form_ShipmentRouting_Pending.php?mainmode=editmode&shipment_id=$shipment_id&message=".$message);

else if($mainmode=='confirmshipment')
	header ("Location: ./Form_ShipmentRouting_Pending.php?message=".$message);
	
else if($mainmode=='delete')
	header ("Location: ./Form_ShipmentRoutingSearch.php?message=".$message);

else if($mainmode=='deletepending')
	header ("Location: ./Form_ShipmentRoutingPendingSearch.php?message=".$message);
	
mysql_close($connection);	
function fbegin()
{
	mysql_query("START TRANSACTION");
}
function fcommit(){
	mysql_query("COMMIT");
}
function frollback()
{
	mysql_query("ROLLBACK");
}	
?>