<?php
 //export.php
 include "../actions/dbPDO.php";
 if(!empty($_FILES["excel_cont"]))
 {
      $file_array = explode(".", $_FILES["excel_cont"]["name"]);
      if($file_array[1] == "xls")
      {
           include("../op/PHPExcel/IOFactory.php");
          //  $reader = PHPExcel_IOFactory::createReader("Excel5");
           $object = PHPExcel_IOFactory::load($_FILES["excel_cont"]["tmp_name"]);
           foreach($object->getWorksheetIterator() as $worksheet)
           {
                $highestRow = $worksheet->getHighestRow();
                $row=2; $segl = 1 ;
                while($row<=$highestRow)
                {
                     $idsa       =  $worksheet->getCellByColumnAndRow(0, $row)->getValue();
                     $contract_id       =  $worksheet->getCellByColumnAndRow(1, $row)->getValue();
                     $total_cost        =  $worksheet->getCellByColumnAndRow(2, $row)->getValue();
                     $rent_days       =  $worksheet->getCellByColumnAndRow(24, $row)->getValue();
                     $cont_if       =  $worksheet->getCellByColumnAndRow(28, $row)->getValue();

                     $used_area     =  $worksheet->getCellByColumnAndRow(29, $row)->getValue();
                     $time_out     =  $worksheet->getCellByColumnAndRow(4, $row)->getValue();
                     $time_in     =  $worksheet->getCellByColumnAndRow(5, $row)->getValue();
                     $date_out  =  $worksheet->getCellByColumnAndRow(6, $row)->getValue();
                     $date_in     =  $worksheet->getCellByColumnAndRow(7, $row)->getValue();
                     $days_qty     =  $worksheet->getCellByColumnAndRow(8, $row)->getValue();
                     $day_cost        =  $worksheet->getCellByColumnAndRow(9, $row)->getValue();
                     $hour_cost         =  $worksheet->getCellByColumnAndRow(10, $row)->getValue();
                     $km_out         =  $worksheet->getCellByColumnAndRow(11, $row)->getValue();
                     $km_in         =  $worksheet->getCellByColumnAndRow(12, $row)->getValue();
                     $free_kilo      =  $worksheet->getCellByColumnAndRow(13, $row)->getValue();
                     $kilo_cost  =  $worksheet->getCellByColumnAndRow(14, $row)->getValue();

                     $open_comm     =  $worksheet->getCellByColumnAndRow(15, $row)->getValue();
                     $days_cost     =  $worksheet->getCellByColumnAndRow(16, $row)->getValue();
                     $hours_cost     =  $worksheet->getCellByColumnAndRow(18, $row)->getValue();
                     $kilos_cost  =  $worksheet->getCellByColumnAndRow(17, $row)->getValue();
                     $addtion_cost     =  $worksheet->getCellByColumnAndRow(19, $row)->getValue();
                     $addtion_desc     =  $worksheet->getCellByColumnAndRow(20, $row)->getValue();
                     $deduction        =  $worksheet->getCellByColumnAndRow(21, $row)->getValue();
                     $total_in        =  $worksheet->getCellByColumnAndRow(22, $row)->getValue();
                     $total_out         =  $worksheet->getCellByColumnAndRow(23, $row)->getValue();
                     $net_cost         =  $worksheet->getCellByColumnAndRow(25, $row)->getValue();
                     $wanted      =  $worksheet->getCellByColumnAndRow(26, $row)->getValue();
                     $tamm_expire  =  $worksheet->getCellByColumnAndRow(31, $row)->getValue();
                     $comm      =  $worksheet->getCellByColumnAndRow(27, $row)->getValue();
                     $branch  =  $worksheet->getCellByColumnAndRow(32, $row)->getValue();

                     $date_out    = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($date_out));
                     if(!empty($date_in)){$date_in   = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($date_in));}
                     else{$date_in = "";}
                     $time_out    = date('H:i', PHPExcel_Shared_Date::ExcelToPHP($time_out));
                     if(!empty($time_in)){$time_in   = date('H:i', PHPExcel_Shared_Date::ExcelToPHP($time_in));}
                     else{$time_in = "";}

                     $car= $db->prepare("SELECT carid , (SELECT cust_id from customers where idsa = ?) as cust FROM cars where total_cost = ?");
                     $car->bindParam(1,$idsa);
                     $car->bindParam(2,$total_cost);
                      $car->execute();
                      if($car->rowCount()>0){$res = $car->fetch(PDO::FETCH_ASSOC);
                          $carid    = $res['carid'];
                          $cust_id  = $res['cust'];
                        }
                        switch ( $cont_if) {
                                case "ساري":
                                $cont_status =  "1";
                          break;
                                case "مقفل":
                                $cont_status =  "2";
                          break;
                              case "مدين" :
                              $cont_status =  "3";
                              break;
                            }
if(!empty($date_in)){
$sql = $db->prepare(" INSERT INTO contracts(contract_id , carid ,cust_id , rent_days, cont_status , used_area ,
    time_out , time_in,  date_out , date_in , days_qty ,day_cost , hour_cost,km_out , km_in , free_kilo ,  kilo_cost ,
    open_comm,  days_cost ,  hours_cost , kilos_cost , addtion_cost , addtion_desc , deduction , total_in ,
    total_out , net_cost , wanted ,tamm_expire , comm , branch )
VALUES (?, ?, ?, ?, ? , ?, ?,?, ?, ?, ?, ?, ?, ?, ? , ?, ?,?, ?, ?,?, ?, ?, ?, ? , ?, ?,?, ?, ?,?)" );
                       $sql->bindParam(1,$contract_id);
                       $sql->bindParam(2,$carid);
                       $sql->bindParam(3,$cust_id);
                       $sql->bindParam(4,$rent_days);
                       $sql->bindParam(5,$cont_status);
                       $sql->bindParam(6,$used_area);
                       $sql->bindParam(7,$time_out);
                       $sql->bindParam(8,$time_in);
                       $sql->bindParam(9,$date_out);
                       $sql->bindParam(10,$date_in);
                       $sql->bindParam(11,$days_qty);
                       $sql->bindParam(12,$day_cost);
                       $sql->bindParam(13,$hour_cost);
                       $sql->bindParam(14,$km_out);
                       $sql->bindParam(15,$km_in);
                       $sql->bindParam(16,$free_kilo);
                       $sql->bindParam(17,$kilo_cost);

                       $sql->bindParam(18,$open_comm);
                       $sql->bindParam(19,$days_cost);
                       $sql->bindParam(20,$hours_cost);
                       $sql->bindParam(21,$kilos_cost);
                       $sql->bindParam(22,$addtion_cost);
                       $sql->bindParam(23,$addtion_desc);
                       $sql->bindParam(24,$deduction);
                       $sql->bindParam(25,$total_in);
                       $sql->bindParam(26,$total_out);
                       $sql->bindParam(27,$net_cost);
                       $sql->bindParam(28,$wanted);
                       $sql->bindParam(29,$tamm_expire);
                       $sql->bindParam(30,$comm);
                       $sql->bindParam(31,$branch);
                       $sql->execute() ;
                    $segl = $segl + 1 ;
                    $row =  $row + 1 ;
      } else{
        $sql = $db->prepare(" INSERT INTO contracts(contract_id , carid ,cust_id , rent_days, cont_status , used_area ,
            time_out ,  date_out ,  days_qty ,day_cost , hour_cost , km_out , free_kilo ,  kilo_cost ,
            open_comm,  days_cost ,  hours_cost , kilos_cost , addtion_cost , addtion_desc , deduction , total_in ,
            total_out , net_cost , wanted ,tamm_expire , comm , branch )
        VALUES (?, ?, ?, ?, ? , ?, ?,?, ?, ?, ?, ?, ?, ?, ? , ?, ?,?, ?, ?,?, ?, ?, ?, ? , ?, ?,?)" );
                               $sql->bindParam(1,$contract_id);
                               $sql->bindParam(2,$carid);
                               $sql->bindParam(3,$cust_id);
                               $sql->bindParam(4,$rent_days);
                               $sql->bindParam(5,$cont_status);
                               $sql->bindParam(6,$used_area);
                               $sql->bindParam(7,$time_out);
                               $sql->bindParam(8,$date_out);
                               $sql->bindParam(9,$days_qty);
                               $sql->bindParam(10,$day_cost);
                               $sql->bindParam(11,$hour_cost);
                               $sql->bindParam(12,$km_out);
                               $sql->bindParam(13,$free_kilo);
                               $sql->bindParam(14,$kilo_cost);

                               $sql->bindParam(15,$open_comm);
                               $sql->bindParam(16,$days_cost);
                               $sql->bindParam(17,$hours_cost);
                               $sql->bindParam(18,$kilos_cost);
                               $sql->bindParam(19,$addtion_cost);
                               $sql->bindParam(20,$addtion_desc);
                               $sql->bindParam(21,$deduction);
                               $sql->bindParam(22,$total_in);
                               $sql->bindParam(23,$total_out);
                               $sql->bindParam(24,$net_cost);
                               $sql->bindParam(25,$wanted);
                               $sql->bindParam(26,$tamm_expire);
                               $sql->bindParam(27,$comm);
                               $sql->bindParam(28,$branch);
                               $sql->execute() ;
                            $segl = $segl + 1 ;
                            $row =  $row + 1 ;

      }

                }
 }
 echo $segl ;
 }
 }

 ?>
