<?php
class Wanted
{
    public $db;
    public $contract_id;
    public function __construct($db, $contract_id)
    {
        $this->db          = $db;
        $this->contract_id = $contract_id;
    }
    public static function hoursCost($date_in, $day_cost, $hour_qty)
    { $data =  array() ;
        $start      = new DateTime("2019-11-16");
        if ($date_in > $start) {
            if ($hour_qty < 6) {
                $data['hours_cost'] = round((($hour_qty - 1) * ($day_cost / 12)),2);
            } else
                {
                $data['hours_cost'] = $day_cost;
                }
                $data['hour_cost'] = round(($day_cost / 12),2);
                return  $data ;
        } else {
            if ($hour_qty < 9) {
                $data['hours_cost'] = ($hour_qty  * (intval($day_cost / 10)));
            } else {
                $data['hours_cost'] = $day_cost;
            }
            $data['hour_cost'] = intval($day_cost / 10) ;
            return  $data ;
        }
    }

    public static function vat2020($date_in, $day_cost, $date_out)
    { $data =  array() ;
        $days_qty5 = 0 ; $days_qty15 = 0; 
        $start      = new DateTime("2020-06-30");
        if ($date_in > $start) {
            if ($date_out > $start) {
                $days_qty15 = $date_in->diff($date_out)->format("%a");
                $data['days_cost15'] = round(($days_qty15 * $day_cost),2);
                $data['days_cost5'] = 0 ;
                $data['days_qty15'] = $days_qty15;
                $data['days_qty5'] = 0;
                $data['days_qty'] = $days_qty15;
            } else
                {
                $days_qty5 = $start->diff($date_out)->format("%a");
                $data['days_cost5'] = round(($days_qty5 * $day_cost),2);
                $days_qty15 = $date_in->diff($start)->format("%a");

                $data['days_cost15'] = round(($days_qty15 * $day_cost),2);
                $data['days_qty15'] = $days_qty15;
                $data['days_qty5'] = $days_qty5;
                $data['days_qty'] = $days_qty5 + $days_qty15;
                }
                return  $data ;
        } else {
            $days_qty5 = $date_in->diff($date_out)->format("%a");
            $data['days_cost5'] = round(($days_qty5 * $day_cost),2);
            $data['days_cost15'] = 0 ;
            $data['days_qty15'] = 0;
            $data['days_qty5'] = $days_qty5;
            $data['days_qty'] = $days_qty5;
            return  $data ;
        }
    }
    public function flash(){
        $db          = $this->db;
        $contract_id = $this->contract_id;

        $flash = $db->prepare("SELECT * from flashback WHERE contract_id = ? ");
        $flash->bindParam(1, $contract_id);
        $flash->execute();
        if ($flash->rowCount() > 0) {
            $back_in        = $flash->fetch(PDO::FETCH_ASSOC);
            $money_in       = $back_in["money_in"];
            $money_wanted   = $back_in["money_wanted"];
        }else {
            $money_in = 0; $money_wanted = 0;
        }
        return $money_wanted - $money_in ;
    }
    public static function flashBack($db,$contract_id)  {
        return (new self($db,$contract_id))->flash();
    }
    public function wanted()
    {
        $db          = $this->db;
        $contract_id = $this->contract_id;
        $data = array();
        $total_in = 0;
        $vat15 = 0 ;
        $vat5 = 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"];
            if (empty($total_in))  $total_in = 0;
        }else {
            $total_in = 0;
        }

        $courts_in = 0;
        $court = $db->prepare("SELECT sum(money_in) as courtIN from courts_in 
                                        WHERE contract_id = ? ");
        $court->bindParam(1, $contract_id);
        $court->execute();
        if ($court->rowCount() > 0) {
            $courts = $court->fetch(PDO::FETCH_ASSOC);
            $courts_in = $courts["courtIN"];
            if (empty($courts_in)) { $courts_in = 0;}
        } else {
            $courts_in = 0;
        }
        $total_out = 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"];
            if (empty($total_out))  $total_out = 0;
        } else {
            $total_out = 0;
        }

        $data['total_in']  = $total_in;
        $data['courts_in'] = $courts_in;
        $data['total_out'] = $total_out;
        $payed             = round((($total_in + $courts_in)- $total_out), 2);
        $data['payed']     = $payed;

        $km_qty     = 0;
        $days_qty   = 0;
        $hour_qty   = 0;
        $sql = $db->prepare("SELECT * 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"];
            $day_cost      = $row["day_cost"];
            $cont_status   = $row["cont_status"];
            if (empty($date_in)) {
                $date1 = new DateTime($date_out);
                $date2 = new DateTime(date("Y-m-d"));

                $vat2020 = self::vat2020($date2, $day_cost, $date1);       
            } else {
                $date1 = new DateTime($date_out);
                $date2 = new DateTime($date_in);
                $vat2020 = self::vat2020($date2, $day_cost, $date1);
            }
            $days_qty       = $vat2020['days_qty'];
            $days_qty15     = $vat2020['days_qty15'];
            $days_qty5      = $vat2020['days_qty5'];
            $days_cost15    = $vat2020['days_cost15'];
            $days_cost5     = $vat2020['days_cost5'];
            $days_cost      = $days_cost15 + $days_cost5 ; 
            
            $kilo_cost        = $row["kilo_cost"];
            $free_kilo        = $row["free_kilo"];
            $km_in            = $row["km_in"];
            $km_out           = $row["km_out"];
            $time_in          = $row["time_in"];
            $wesam_ins        = $row["wesam_ins"];
            $open_kilo        = $row["open_kilo"];
            if($days_qty > 1){
                $wesam_ins        = round(($wesam_ins * $days_qty),2) ;
                $open_kilo        = round(($open_kilo * $days_qty),2) ;
            }
            $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;
            }
            if (empty($date_in)){ $date_in = new DateTime(date("Y-m-d"));}
            else { $date_in = new DateTime($date_in);  }
            $data['hour_cost'] = round(($day_cost / 12),2);
            if ($hour_qty > 0) {
                $hcost = self::hoursCost($date_in, $day_cost, $hour_qty);
                $hours_cost = $hcost['hours_cost'] ;
                $data['hour_cost'] = $hcost['hour_cost'] ;
            } else {
                $hours_cost = 0;
                $hour_qty   = 0;
            }
            if ($hours_cost == $day_cost) {
                $day_km  = $day_km + 1;
            }
            
            if (!empty($km_in)) {
                $free_kilo = $day_km * $free_kilo;
                $km_qty = $km_in - ($km_out + $free_kilo);
                if ($km_qty < 0) {
                    $km_qty = 0;
                }
                if ($km_qty > 0) {
                    $kilos_cost = ($km_qty * $kilo_cost) / 100;
                } else {
                    $kilos_cost = 0;
                }
                $kilos_cost = intval($kilos_cost);
                $used_km = $km_in - $km_out;
            } else {
                $kilos_cost  = 0;
                $km_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;       }
            $rent_cost            = round(($days_cost + $kilos_cost + $hours_cost), 2);
            $data['rent_cost']    = $rent_cost;
            // $tot_addtion          = round(($addtion_cost + $violations + $ins_claim), 2);
             $tot_addtion          = round(($addtion_cost + $ins_claim), 2);
            $data['tot_addtion']  =  $tot_addtion;
            $data['deduction']    = $deduction;
            $data['date_in']      = $date_in;
            $data['days_qty']     = $days_qty;

            $data['day_cost']     = $day_cost;
            $data['days_cost']    = $days_cost;
            $data['km_qty']       = $km_qty;
            $data['kilo_cost']    = $kilo_cost;
            $data['kilos_cost']   = $kilos_cost;
            $data['used_km']      = $used_km;
            $data['hour_qty']     = $hour_qty;
            $data['hours_cost']   = $hours_cost;
            $data['cont_status']  = $cont_status;
            $data['addtion_desc']  = $row["addtion_desc"];


            $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);
                $cutt = $toty_cut["cutt"];
            } else {
                $cutt = 0;
            }
            $debit = $db->prepare("SELECT sum(debit_money) as debit from debit_vat WHERE contract_id = ? ");
            $debit->bindParam(1, $contract_id);
            $debit->execute();
            if ($debit->rowCount() > 0) {
                $debit_cut = $debit->fetch(PDO::FETCH_ASSOC);
                $debit_vat = $debit_cut["debit"];
            } else {
                $debit_vat = 0;
            }
            $cutt =  $cutt +  $debit_vat;
            $data['cutt']  = $cutt;
            $data['tot_cut']  = round(($cutt + $deduction), 2);
            $net_cost         = round((($rent_cost +  $tot_addtion + $wesam_ins + $open_kilo) - ($deduction + $cutt)),2);
            $vat_req = 0 ;

            $vat_start        = new DateTime('2018-01-10');
            $vat_15           = new DateTime("2020-06-30");
            $flashback = self::flashBack($db,$contract_id);
        if ($date_in > $vat_start){
            if($date_in > $vat_15){
                    $vat15 = round((($net_cost - $days_cost5) * 0.15), 2);
                    $vat5 = round(($days_cost5 * 0.05),2);
                }else{
                    $vat5     = round(($net_cost * 0.05), 2);
                    $vat15    = 0 ;
                }
                $vat_req = $vat5 + $vat15 ;
                $after_vat        = round(($flashback +$vat_req + $net_cost), 2) + $violations;
                
             } else {
                $after_vat        = round(($net_cost), 2)+ $violations;
            }

                $wanted               = round(($after_vat -  $payed), 2);
                // $wanted = $wanted + $flashback;
                $data['wanted']       = $wanted ;
                if($wanted !=0){
                    $data['late'] = round(($wanted /($day_cost *1.15)),2);
                }else{
                    $data['late'] = 0 ;
                }
                $data['after_vat']    = $after_vat;
                $data['tot_addtion']  = $tot_addtion;
                $data['addtion_cost'] = $addtion_cost;
                $data['violations']   = $violations;
                $data['ins_claim']    = $ins_claim;

                $data['net_cost']     = $net_cost;
                $data['daysCost5']    = $days_cost5;
                $data['vat_req']      = $vat_req;
                $data['vat15']        = $vat15;
                $data['vat5']         = $vat5 ;
                $data['days_qty15']   = $days_qty15;
                $data['wesam_ins']    = $wesam_ins ;
                $data['open_kilo']    = $open_kilo;

            return $data;
        } 
    }//wanted


   public  function vatInvoice($end){
            $db          = $this->db;
            $contract_id = $this->contract_id;

                $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 ;
                $rent_cost 		= 0 ;
                $day_km 		= 0 ;
                $cont_status  	= 1 ;
                $vat_req 		= 0 ;
                $after_vat 		= 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($last)){$date_out = $last ; }
            $date1 = new DateTime($date_out);
            $date2 = new DateTime($end);
            $days_qty = $date2->diff($date1)->format("%a");
       
                    $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($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 = round(($day_cost / 12),2) ;
                    }
            if($hour_qty > 1){ 
                    if($hour_qty < 6){
                        $hours_cost = round((($hour_qty - 1) * ($day_cost / 12)),2);
                    }else if($hour_qty > 5){
                        $hours_cost = $day_cost ;
                        $day_km  	= $day_km + 1 ;
                    }
            } else {$hours_cost = 0 ; $hour_qty = 0 ; }
                          
        if(!empty($km_in)){
            $date_in2 = date('Y-m-d', strtotime($date_in));
            $end2 	  = date('Y-m-d', strtotime($end));
        if($end2 < $date_in2){
                $kilos_cost 	= 0 ;
            }else{
                $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 ;}

             if(empty($date_in)){
                $hours_cost 	= 0 ;
                $kilos_cost 	= 0 ;
                $addtion_cost   = 0 ;
                $violations     = 0 ;
                $deduction		= 0 ;
                $addtion_desc   = "";
            } else{        
                $date_in2 = date('Y-m-d', strtotime($date_in));
                $end2 	  = date('Y-m-d', strtotime($end));
            if($end2 < $date_in2){
                $hours_cost 	= 0 ;
                $kilos_cost 	= 0 ;
                $addtion_cost 	= 0 ;
                $violations   	= 0 ;
                $deduction		= 0 ;
                $addtion_desc 	= "";
                 }
         }
              $net_cost 	= round((( $days_cost + $kilos_cost + $hours_cost + $addtion_cost)- $deduction),2) ;
              $vat_req 		= round(($net_cost  * 0.15),2) ;
              $after_vat 	= round(($vat_req + $net_cost + $violations),2) ;
            }
        $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 ;
}// end of vat_invoice

//all money for one contracts in a month invoice - outvoice
function contract_month($first , $last){
    $db          = $this->db;
    $contract_id = $this->contract_id;
        $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
 function totalVat(){
    $db = $this->db;
    $contract_id = $this->contract_id;
    $sql= $db->prepare("SELECT sum(after_vat) as total FROM vat where contract_id = $contract_id");
    $sql->execute();
  if($sql->rowCount()>0){$row = $sql->fetch(PDO::FETCH_ASSOC);
     $totalVat = $row['total'];} else {$totalVat = 0 ; }
     return $totalVat;
}
function totalDeduction(){
    $db = $this->db;
    $contract_id = $this->contract_id;
    $totalVat = 0 ;
    $sql= $db->prepare("SELECT sum(money_de) as total FROM deduction where contract_id = $contract_id");
    $sql->execute();
  if($sql->rowCount()>0){$row = $sql->fetch(PDO::FETCH_ASSOC);
     $totalVat = $row['total'];}
     return $totalVat;
}
}

class AutoClose{
    public $db;
    public $contract_id;

    function __construct($db,$contract_id){
        $this->db = $db;
        $this->contract_id = $contract_id ;
    }
   public function autoClose(){
       $db          = $this->db;
       $contract_id = $this->contract_id ;

       $wanted   = new Wanted($db,$contract_id) ;
       $pay      = $wanted->wanted();
       $date_in  = $pay['date_in'] ;
       $time_in  = $pay['time_in'] ;
    //    $km_in    = $pay['km_in'] ;
          
    if(!empty($date_in)&&!empty($time_in)){
                   $wanted 			= $pay["wanted"] ;
                   $total_in 		= $pay["total_in"] ;
                   $total_out 		= $pay["total_out"] ;
            
                   $days_cost 		= $pay["days_cost"] ;
                   $kilos_cost 	    = $pay["kilos_cost"] ;
                   $hours_cost 	    = $pay["hours_cost"] ;
                   $net_cost 		= $pay["net_cost"] ;
       
                   $vat_req 	    = $pay["vat_req"];
                   $after_vat 		= $pay["after_vat"];
                   $payed           = $pay["payed"];
 if($wanted == 0){$cont_status = 2 ;}else {$cont_status = 3 ;}
        $contt = $db->prepare(" UPDATE contracts SET   total_in = ?, total_out = ?,  wanted = ? , cont_status = ? , days_cost = ?,
                kilos_cost = ? , hours_cost = ? , net_cost = ? , vat_req = ? ,  after_vat = ?
                WHERE contract_id = ? ");
           $contt->bindParam(1,$total_in);
           $contt->bindParam(2,$total_out);
           $contt->bindParam(3,$wanted);
           $contt->bindParam(4,$cont_status);
           $contt->bindParam(5,$days_cost);
           $contt->bindParam(6,$kilos_cost);
           $contt->bindParam(7,$hours_cost);
           $contt->bindParam(8,$net_cost);
           $contt->bindParam(9,$vat_req);
           $contt->bindParam(10,$after_vat);
           $contt->bindParam(11,$contract_id);
    if( $contt->execute()){     return $payed ;     }
       }
    }
       
}