<?php
function wanted($contract_id){
	include "../actions/dbPDO.php";
	$days_qty 		= 0;
	$day_cost 		= 0;
	$days_cost 		= 0;
	$kilo_qty 		= 0;
	$kilo_cost 		= 0;
	$kilos_cost   	= 0;
	$hour_qty 		= 0;
	$hour_cost 		= 0;
	$hours_cost 	= 0;
	$addtion_cost 	= 0;
	$violations 	= 0;
	$ins_claim 		= 0;
	$deduction 		= 0;
	$total_in 		= 0;
	$total_out 		= 0;
	$net_cost 		= 0;
	$wanted 		= 0;
	$payed 		    = 0;
	$tot_add 		= 0;
	$rent_cost 		= 0;
	$day_km 		= 0 ;

	$vat_req 		= 0 ;
	$after_vat 		= 0 ;
	$vat_in 		= 0 ;
	$total_cut		= 0 ;

	$sql = $db->prepare("SELECT date_out , date_in , time_out , time_in , kilo_cost ,kilos_cost , free_kilo , km_in , km_out ,day_cost , hour_cost,addtion_cost , violations ,
		 										ins_claim ,deduction		FROM contracts WHERE contract_id = ? LIMIT 1");
	$sql->bindParam(1,$contract_id);
	$sql->execute();
  if($sql->rowCount()>0){$row = $sql->fetch(PDO::FETCH_ASSOC);
				$date_in   						= $row["date_in"];
				$date_out   					= $row["date_out"];
			if(empty($date_in)){
				$date1 = new DateTime($date_out);
				$date2 = new DateTime(date("Y-m-d"));
				$days_qty = $date2->diff($date1)->format("%a");
			} else{
				$date1 = new DateTime($date_out);
				$date2 = new DateTime($date_in);
				$days_qty = $date2->diff($date1)->format("%a");
			}
		$day_cost 		= $row["day_cost"] ;
		$kilo_cost 		= $row["kilo_cost"] ;
		$hour_cost		= $row["hour_cost"] ;
		$free_kilo		= $row["free_kilo"] ;
		$km_in			= $row["km_in"] ;
		$km_out			= $row["km_out"] ;
		$time_in 		= $row["time_in"];
		$days_cost 		= $days_qty * $day_cost ;
		$day_km 			= $days_qty ;

		if(empty($time_in)){
			$time1 = new DateTime($row['time_out']);
			$time2 = new DateTime(date("H:i"));
		} else{
			$time1 = new DateTime($row['time_out']);
			$time2 = new DateTime($row['time_in']);
		}
			if($time2 > $time1){ $hour_qty = intval($time2->diff($time1)->format("%h")); }else{$hour_qty = 0;}
		  $hour_cost = intval($day_cost / 10) ;

		  if($hour_qty > 0){ $hours_cost = $hour_qty * $hour_cost ; } else {$hours_cost = 0 ; $hour_qty = 0 ; }
			if($hour_qty > 9){
				$hours_cost = $day_cost ;
				$day_km  = $day_km + 1 ; }
					if(($days_qty== 0)&&($hour_qty > 6)){
						$hours_cost = $day_cost ;
						$day_km  = 1 ;
					}
		if(!empty($km_in)){
		  			$kilo_qty = $km_in - ($km_out + ($day_km * $free_kilo)) ;
		  			if($kilo_qty < 0){$kilo_qty = 0 ;}
		  			if($kilo_qty > 0){$kilos_cost = ( $kilo_qty * $kilo_cost)/100 ;} else {$kilos_cost = 0 ;}
		  			$kilos_cost = intval($kilos_cost);
						$used_km = $km_in - $km_out ;
					}
		else {
					$kilos_cost = 0 ; 	$kilo_qty	  = 0 ; 			$used_km = 0 ;
			}
					$addtion_cost   = $row["addtion_cost"];
    				$violations     = $row["violations"];
					$ins_claim	   	= $row["ins_claim"];
    			$deduction			= $row["deduction"];
		if(empty($addtion_cost)){$addtion_cost = 0 ;}
		if(empty($violations)){$violations = 0 ;}
		if(empty($ins_claim)){$ins_claim = 0 ;}
		if(empty($deduction)){$deduction = 0 ;}


	$tot_in = $db->prepare("SELECT sum(money_in) as inn from invoice WHERE contract_id =? ");
	$tot_in->bindParam(1,$contract_id);
	$tot_in->execute();
	if($tot_in->rowCount()>0){$toty_in = $tot_in->fetch(PDO::FETCH_ASSOC);
	$total_in = $toty_in["inn"];
} else { $total_in = 0 ;}

		$tot_out = $db->prepare("SELECT sum(money_out) as outt from outvoice WHERE contract_id = ? ");
		$tot_out->bindParam(1,$contract_id);
		$tot_out->execute();
	if($tot_out->rowCount()>0){$toty_out = $tot_out->fetch(PDO::FETCH_ASSOC);
		$total_out = $toty_out["outt"]; } else { $total_out = 0 ;}

	$tot_cut = $db->prepare("SELECT sum(money_de) as cutt from deduction WHERE contract_id = ? ");
	$tot_cut->bindParam(1,$contract_id);
	$tot_cut->execute();
if($tot_cut->rowCount()>0){$toty_cut = $tot_cut->fetch(PDO::FETCH_ASSOC);
							$total_cut = $toty_cut["cutt"];} else{ $total_cut = 0 ;}

			$rent_cost = $days_cost + $kilos_cost + $hours_cost ;
			
			$net_cost  = ( $rent_cost + $addtion_cost)-($deduction + $total_cut) ;

$payed = round(($total_in - $total_out), 2) ;
if(empty($date_in)){
	$vat_req 		= round(($net_cost * 0.05),2) ;
	$after_vat 		= $vat_req + $net_cost ;
	$wanted 		= round(($after_vat - $payed),2) ;
} else{
  // $vat_start = date('Y-m-d', strtotime("2017-12-31"));
	$vat_start = date('Y-m-d', strtotime("2018-01-10"));
	$date_in = date('Y-m-d', strtotime($date_in));
if($date_in > $vat_start){
	$vat_req 		= round(($net_cost  * 0.05),2) ;
	$after_vat 		= round(($vat_req + $net_cost),2)+ $violations + $ins_claim ;
	$wanted 		= $after_vat -  $payed  ;
	$vat_in = round((($payed * 5)/105),2) ;
			if($after_vat == $payed){
					$vat_in = $vat_req ;
				}
} else {	$after_vat 	= $net_cost + $violations + $ins_claim ;
		$wanted 		=	$after_vat -  $payed  ;}}
		$tot_add   = $addtion_cost + $violations + $ins_claim ;

$data  = array();
				$data["days_qty"] 		= $days_qty ;
				$data["day_cost"] 		= $day_cost ;
				$data["days_cost"] 		= $days_cost ;
				$data["kilo_qty"] 		= $kilo_qty ;
				$data["kilo_cost"] 		= $kilo_cost ;
				$data["kilos_cost"] 	= $kilos_cost ;
				$data["hour_qty"] 		= $hour_qty ;
				$data["hour_cost"] 		= $hour_cost ;
				$data["hours_cost"] 	= $hours_cost ;
				$data["addtion_cost"] 	= $addtion_cost ;
				$data["violations"] 	= $violations ;
				$data["ins_claim"] 		= $ins_claim ;
				$data["deduction"] 		= $deduction ;
				$data["total_in"] 		= $total_in ;
				$data["total_out"] 		= $total_out ;
				$data["total_cut"] 		= $total_cut ;
				$data["net_cost"] 		= $net_cost ;
				$data["wanted"] 		= round(($wanted),2) ;
				$data["payed"] 			= $payed ;
				$data["tot_add"] 		= $tot_add ;
				$data["rent_cost"] 		= $rent_cost ;
				$data["used_km"] 		= $used_km ;
				$data["vat_req"]		= $vat_req ;
				$data["vat_in"]			= $vat_in ;
				$data["after_vat"]		= $after_vat ;
}
return $data ;
}
function wanted_at($contract_id,$at){
	include "../actions/dbPDO.php";
	$days_qty 		= 0;
	$day_cost 		= 0;
	$days_cost 		= 0;
	$kilo_qty 		= 0;
	$kilo_cost 		= 0;
	$kilos_cost   	= 0;
	$hour_qty 		= 0;
	$hour_cost 		= 0;
	$hours_cost 	= 0;
	$addtion_cost 	= 0;
	$violations 	= 0;
	$ins_claim 		= 0;
	$deduction 		= 0;
	$total_in 		= 0;
	$total_out 		= 0;
	$net_cost 		= 0;
	$wanted 		= 0;
	$payed 		    = 0;
	$tot_add 		= 0;
	$rent_cost 		= 0;
	$day_km 		= 0 ;

	$vat_req 		= 0 ;
	$after_vat 		= 0 ;
	$vat_in 		= 0 ;
	$total_cut		= 0 ;

	$sql = $db->prepare("SELECT date_out , date_in , time_out , time_in , kilo_cost ,kilos_cost , free_kilo , km_in , km_out ,day_cost , hour_cost,addtion_cost , violations ,
		 				ins_claim ,deduction		FROM contracts WHERE contract_id = ? LIMIT 1");
	$sql->bindParam(1,$contract_id);
	$sql->execute();
  if($sql->rowCount()>0){$row = $sql->fetch(PDO::FETCH_ASSOC);

				$date_in   						= $row["date_in"];
				$date_out   					= $row["date_out"];
			if(empty($date_in)){
				$date1 = new DateTime($date_out);
				$date2 = new DateTime(date("Y-m-d"));
				$days_qty = $date2->diff($date1)->format("%a");
			} else{
				$date1 = new DateTime($date_out);
				$date2 = new DateTime($date_in);
				$days_qty = $date2->diff($date1)->format("%a");
			}
		$day_cost 		= $row["day_cost"] ;
		$kilo_cost 		= $row["kilo_cost"] ;
		$hour_cost		= $row["hour_cost"] ;
		$free_kilo		= $row["free_kilo"] ;
		$km_in				= $row["km_in"] ;
		$km_out				= $row["km_out"] ;
		$time_in 			= $row["time_in"];
		$days_cost 		= $days_qty * $day_cost ;
		$day_km 			= $days_qty ;

		if(empty($time_in)){
			$time1 = new DateTime($row['time_out']);
			$time2 = new DateTime(date("H:i"));
		} else{
			$time1 = new DateTime($row['time_out']);
			$time2 = new DateTime($row['time_in']);
		}
			if($time2 > $time1){ $hour_qty = intval($time2->diff($time1)->format("%h")); }else{$hour_qty = 0;}
		  $hour_cost = intval($day_cost / 10) ;

		  if($hour_qty > 0){ $hours_cost = $hour_qty * $hour_cost ; } else {$hours_cost = 0 ; $hour_qty = 0 ; }
		      if($hours_cost >= $day_cost){
		                        $hours_cost = $day_cost ;
		                        $day_km  = $day_km + 1 ;
		                      }
					if(($days_qty== 0)&&($hour_qty > 6)){
						$hours_cost = $day_cost ;
						$day_km  = 1 ;
					}
			if(!empty($km_in)){
		  			$kilo_qty = $km_in - ($km_out + ($day_km * $free_kilo)) ;
		  			if($kilo_qty < 0){$kilo_qty = 0 ;}
		  			if($kilo_qty > 0){$kilos_cost = ( $kilo_qty * $kilo_cost)/100 ;} else {$kilos_cost = 0 ;}
		  			$kilos_cost = intval($kilos_cost);
						$used_km = $km_in - $km_out ;
					}
			else {
					$kilos_cost = 0 ;
					$kilo_qty	  = 0 ; $used_km = 0 ;
			}

		$addtion_cost   = $row["addtion_cost"];
    $violations     = $row["violations"];
		$ins_claim	   	= $row["ins_claim"];
    $deduction			= $row["deduction"];
		if(empty($addtion_cost)){$addtion_cost = 0 ;}
		if(empty($violations)){$violations = 0 ;}
		if(empty($ins_claim)){$ins_claim = 0 ;}
		if(empty($deduction)){$deduction = 0 ;}


	$tot_in = $db->prepare("SELECT sum(money_in) as inn from invoice WHERE contract_id =? AND sanad_date <= ? ");
	$tot_in->bindParam(1,$contract_id);
	$tot_in->bindParam(2,$at);
	$tot_in->execute();
	if($tot_in->rowCount()>0){$toty_in = $tot_in->fetch(PDO::FETCH_ASSOC);
	$total_in = $toty_in["inn"];
} else { $total_in = 0 ;}

	$tot_out = $db->prepare("SELECT sum(money_out) as outt from outvoice WHERE contract_id = ? AND sanad_out <= ?  ");
	$tot_out->bindParam(1,$contract_id);
	$tot_out->bindParam(2,$at);
	$tot_out->execute();
	if($tot_out->rowCount()>0){$toty_out = $tot_out->fetch(PDO::FETCH_ASSOC);
	$total_out = $toty_out["outt"]; } else { $total_out = 0 ;}

$tot_cut = $db->prepare("SELECT sum(money_de) as cutt from deduction WHERE contract_id = ? AND sanad_date <= ?  ");
$tot_cut->bindParam(1,$contract_id);
$tot_cut->bindParam(2,$at);
$tot_cut->execute();
if($tot_cut->rowCount()>0){$toty_cut = $tot_cut->fetch(PDO::FETCH_ASSOC);
				$total_cut = $toty_cut["cutt"];} else{ $total_cut = 0 ;}

	$net_cost = ( $days_cost + $kilos_cost + $hours_cost + $addtion_cost )- ($deduction + $total_cut) ;


$payed = round(($total_in - $total_out), 2) ;


$rent_cost = $days_cost + $kilos_cost + $hours_cost ;

if(empty($date_in)){
	$vat_req 		= round(($net_cost * 0.05),2) ;
	$after_vat 	= $vat_req + $net_cost ;
	$wanted 		= round(($after_vat - $payed),2) ;
} else{
  // $vat_start = date('Y-m-d', strtotime("2017-12-31"));
$vat_start = date('Y-m-d', strtotime("2018-01-10"));
$date_in = date('Y-m-d', strtotime($date_in));
if($date_in > $vat_start){
	$vat_req 		= round(($net_cost  * 0.05),2) ;
	$after_vat 		= $vat_req + $net_cost + $violations + $ins_claim ; ;
	$wanted 		= $after_vat - $payed  ;
	$vat_in = round((($payed * 5)/105),2) ;
			if($after_vat == $payed){
					$vat_in = $vat_req ;
				}
} else {
	$wanted 		=	$net_cost -  $payed ;
	$after_vat 	= $net_cost + $violations + $ins_claim ;;
}}
$tot_add   = $addtion_cost + $violations + $ins_claim ;

$data  = array();
				$data["days_qty"] 		= $days_qty ;
				$data["day_cost"] 		= $day_cost ;
				$data["days_cost"] 		= $days_cost ;
				$data["kilo_qty"] 		= $kilo_qty ;
				$data["kilo_cost"] 		= $kilo_cost ;
				$data["kilos_cost"] 	= $kilos_cost ;
				$data["hour_qty"] 		= $hour_qty ;
				$data["hour_cost"] 		= $hour_cost ;
				$data["hours_cost"] 	= $hours_cost ;
				$data["addtion_cost"] = $addtion_cost ;
				$data["violations"] 	= $violations ;
				$data["ins_claim"] 		= $ins_claim ;
				$data["deduction"] 		= $deduction ;
				$data["total_in"] 		= $total_in ;
				$data["total_out"] 		= $total_out ;
				$data["total_cut"] 		= $total_cut ;
				$data["net_cost"] 		= $net_cost ;
				$data["wanted"] 			= round(($wanted),2) ;
				$data["payed"] 				= $payed ;
				$data["tot_add"] 			= $tot_add ;
				$data["rent_cost"] 		= $rent_cost ;
				$data["used_km"] 			= $used_km ;
				$data["vat_req"]			= $vat_req ;
				$data["vat_in"]				= $vat_in ;
				$data["after_vat"]		= $after_vat ;
}
return $data ;
}
//all money for one contracts in a month invoice - outvoice
function contract_month($contract_id , $first , $last){
	include "../actions/dbPDO.php";
$inn = 0;
$outt = 0;
	$sql = $db->prepare("SELECT sum(money_in) as inn from invoice where contract_id = ? AND sanad_date between ? AND ? ");
	$sql->bindParam(1,$contract_id);
	$sql->bindParam(2,$first);
	$sql->bindParam(3,$last);
	$sql->execute();
  if($sql->rowCount()>0){$row = $sql->fetch(PDO::FETCH_ASSOC); $inn  = $row["inn"]; }

	$sql2 = $db->prepare("SELECT sum(money_out) as outt from outvoice where contract_id = ? AND sanad_out between ? AND ? ");
	$sql2->bindParam(1,$contract_id);
	$sql2->bindParam(2,$first);
	$sql2->bindParam(3,$last);
	$sql2->execute();
  if($sql2->rowCount()>0){$row = $sql2->fetch(PDO::FETCH_ASSOC); $outt  = $row["outt"]; }

	return round(($inn - $outt),2) ;
}
// vat invoice
function vat_invoice($contract_id,$end){
	include "../actions/dbPDO.php";

	$days_qty 		= 0;
	$day_cost 		= 0;
	$days_cost 		= 0;
	$kilo_qty 		= 0;
	$kilo_cost 		= 0;
	$kilos_cost   	= 0;
	$hour_qty 		= 0;
	$hour_cost 		= 0;
	$hours_cost 	= 0;
	$addtion_cost 	= 0; 		$addtion_desc = "";
	$violations 	= 0;
	$deduction 		= 0;
	$net_cost 		= 0;
	$wanted 		= 0;
	$rent_cost 		= 0;
	$day_km 		= 0 ;
	$cont_status  	= 1 ;

	$vat_req 		= 0 ;
	$after_vat 		= 0 ;
	$vat_in 		= 0 ;
//last vat end date to have new start for the next vat_in
	$last_sql = $db->prepare("SELECT max(end) as last from vat where contract_id = ?");
	$last_sql->bindParam(1,$contract_id);
	$last_sql->execute();
if($last_sql->rowCount()>0){
	$segl = $last_sql->fetch(PDO::FETCH_ASSOC);
	$last = $segl['last'];
}
	$sql = $db->prepare("SELECT date_out , date_in , time_out , time_in , kilo_cost ,kilos_cost ,
		 	free_kilo , km_in , km_out ,day_cost , hour_cost,addtion_cost , violations ,addtion_desc ,
		 	days_qty,	cont_status ,deduction		FROM contracts WHERE contract_id = ? LIMIT 1");
	$sql->bindParam(1,$contract_id);
	$sql->execute();
  if($sql->rowCount()>0){$row = $sql->fetch(PDO::FETCH_ASSOC);
				$date_in   			= $row["date_in"];
				$date_out   		= $row["date_out"];
				$time_in   			= $row["time_in"];
				$time_out   		= $row["time_out"];
				$day_km  			= $row["days_qty"];
				$cont_status  		= $row["cont_status"];
				$day_cost 			= $row["day_cost"] ;
				$kilo_cost 			= $row["kilo_cost"] ;
				$hour_cost			= $row["hour_cost"] ;
				$free_kilo			= $row["free_kilo"] ;
				$km_in				= $row["km_in"] ;
				$km_out				= $row["km_out"] ;
				$time_in 			= $row["time_in"];
				$time_in 			= $row["time_in"];
				$addtion_desc   	= $row["addtion_desc"];
				$addtion_cost   	= $row["addtion_cost"];
		    	$violations     	= $row["violations"];
		    	$deduction			= $row["deduction"];
				if(empty($addtion_cost)){$addtion_cost = 0 ;}
				if(empty($violations)){$violations = 0 ;}
				if(empty($deduction)){$deduction = 0 ;}
				if(empty($day_km)){$day_km = 0 ;}
		 if(empty($date_in)){
			 if(!empty($last)){$date_out = $last ; }
					$date1 = new DateTime($date_out);
					$date2 = new DateTime($end);
				$days_qty = $date2->diff($date1)->format("%a");
					$hour_cost 			= 0 ;
					$kilos_cost 		= 0 ;
					$addtion_cost   = 0;
			    $violations     = 0;
			    $deduction			= 0 ;
					$addtion_desc   = "";
				} else{
						if(!empty($last)){$date_out = $last ; }
					$date1 = new DateTime($date_out);
					$date2 = new DateTime($end);
					$days_qty = $date2->diff($date1)->format("%a");

					$date_in2 = date('Y-m-d', strtotime($date_in));
					$end2 		= date('Y-m-d', strtotime($end));
			if($end2 < $date_in2){
					$hour_cost 		= 0 ;
					$kilos_cost 	= 0 ;
					$addtion_cost 	= 0 ;
				    $violations   	= 0 ;
				    $deduction		= 0 ;
					$addtion_desc 	= "";
					}
				}
		$days_cost 		=  $days_qty * $day_cost ;
		$used_km 			= 0 ;
		if(!empty($km_in)){$used_km = $km_in - $km_out ;}
		if(!empty($time_in)){
					$time1 = new DateTime($row['time_out']);
					$time2 = new DateTime($row['time_in']);
			if($time2 > $time1){ $hour_qty = intval($time2->diff($time1)->format("%h")); }
					else{$hour_qty = 0;}
		  $hour_cost = intval($day_cost / 10) ;
		}
	if($hour_qty > 0){ $hours_cost = $hour_qty * $hour_cost ; } else {$hours_cost = 0 ; $hour_qty = 0 ; }
		      if($hour_qty > 9){
		                        $hours_cost = $day_cost ;
		                        $day_km  		= $day_km + 1 ;
		                      }
					if(($days_qty== 0)&&($hour_qty > 6)){
														$hours_cost = $day_cost ;
														$day_km  = 1 ;
								}
	if(!empty($km_in)){
		$kilo_qty = $km_in - ($km_out + ($day_km * $free_kilo)) ;
		if($kilo_qty < 0){$kilo_qty = 0 ;}
		if($kilo_qty > 0){$kilos_cost = ( $kilo_qty * $kilo_cost)/100 ;} else {$kilos_cost = 0 ;}
		$kilos_cost = intval($kilos_cost);
		$used_km = $km_in - $km_out ;
	}
	else {	$kilos_cost = 0 ; 	$kilo_qty	  = 0 ; $used_km = 0 ;}

		$net_cost 		= ( $days_cost + $kilos_cost + $hours_cost + $addtion_cost)- $deduction ;
		$vat_req 		= round(($net_cost  * 0.05),2) ;
		$after_vat 		= $vat_req + $net_cost + $violations ;
}
$data  = array();
				$data["days_qty"] 		= $days_qty ;
				$data["day_cost"] 		= $day_cost ;
				$data["days_cost"] 		= $days_cost ;
				$data["date_out"]		= $date_out ;
				$data["date_in"]		= $date_in ;
				$data["kilo_qty"] 		= $kilo_qty ;
				$data["kilo_cost"] 		= $kilo_cost ;
				$data["kilos_cost"] 	= $kilos_cost ;
				$data["hour_qty"] 		= $hour_qty ;
				$data["hour_cost"] 		= $hour_cost ;
				$data["hours_cost"] 	= $hours_cost ;
				$data["addtion_cost"] 	= $addtion_cost ;
				$data["violations"] 	= $violations ;
				$data["deduction"] 		= $deduction ;
				$data["net_cost"] 		= $net_cost ;
				$data["rent_cost"] 		= $rent_cost ;
				$data["used_km"] 		= $used_km ;
				$data["vat_req"]		= $vat_req ;
				$data["after_vat"]		= $after_vat ;
				$data["cont_status"]	= $cont_status ;
				$data["addtion_desc"]	= $addtion_desc ;

return $data ;
}

?>
