<?PHP 
$servername = "192.169.232.59";
$username = "atvinter_atv19";
$password = "KoDq(=tt6d}(";
$dbname = "atvinter_dbproject";


// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());

}else{
  //  echo'success';
}

error_reporting(E_ALL & ~E_NOTICE);

date_default_timezone_set("America/Los_Angeles");



function mnltopst($date, $time){

    $convert = new DateTime($date.' '.$time, new DateTimeZone('Asia/Manila'));
    
    
    $convert->setTimezone(new DateTimeZone('America/Los_Angeles'));
    
    $datetime = $convert->format('m/d/Y h:i A');
    
    
    return ($datetime);
    
    
}
function psttomnl($date, $time){
    
  $convert = new DateTime($date.' '.$time, new DateTimeZone('America/Los_Angeles'));
  
  
  $convert->setTimezone(new DateTimeZone('Asia/Manila'));
  
  $datetime = $convert->format('m/d/Y h:i A');
  
  
  return ($datetime);
  
  
  
  
}


function computeDuration($date1,$date2){

  
        $curdate = strtotime($date1);
        $datadate = strtotime($date2);
        $diff = $curdate - $datadate;
     
        $sec  = $diff;
       return $min = floor($diff/(60));

        $hrs = floor($diff/(60*60));
        $days = floor($diff/(60*60*24));
    

}

 if(isset($_POST['teamfilter'])){
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename="LILO.csv";');
    $csv =  fopen('php://output', 'w');

    $header =['Employee ID','Name','Team','Date','Login(Pacific)','Logout(Pacific)','Login(Manila)','Logout(Manila)','Late','First Break','Second Break','Toilet Break','Lunch Break','Prod','Non-Prod','Admin'];

    fputcsv($csv, $header);

 

    $sql=$conn->query('SELECT *, schedule.date AS "sched_date" FROM schedule JOIN empinfo ON schedule.empid = empinfo.empid  JOIN empdetails ON schedule.empid = empdetails.detailid WHERE empdetails.team="'.$_POST['team'].'" AND status= "Active" AND schedule.date BETWEEN "'.$_POST['from'].'" AND "'.$_POST['to'].'" '  );

    while ( $row=$sql->fetch_assoc() ) {
      

        $lilo = $conn->query('SELECT * FROM tbl_login WHERE date="'.date('m/d/Y', strtotime($row['sched_date'])).'" AND empid="'.$row['empid'].'" ');
        $lilo_row = $lilo->fetch_assoc();

       if($lilo_row['timein'] == ''){
           if($row['idl'] != ''){
               $leave = $conn->query("SELECT * FROM `leave` WHERE id='".$row['idl']."' AND status='3' ");
               if($leave->num_rows > 0){
                   $leave_row = $leave->fetch_assoc();
                   $timein = $leave_row['name'];
                   $timeout= '--';
                   $mnl_timein = '--';
                   $mnl_timeout= '--';
                   $late ='--';   
                   $break = ['1st Break' => '--','2nd Break' =>'--','Toilet Break' => '--','Lunch Break' => '--'];
                   $prod = ["Prod"=>'--',"Non-Prod"=>'--',"Admin"=>'--'];                            }
           }else if($row['pststarttime'] == 'OFF' ||$row['pststarttime'] == 'HOLIDAY OFF' ){
               $timein = $row['pststarttime'];
               $timeout= $row['pststarttime'];
               $mnl_timein = '--';
               $mnl_timeout= '--';
               $late ='--';
               $break = ['1st Break' => '--','2nd Break' =>'--','Toilet Break' => '--','Lunch Break' => '--'];
               $prod = ["Prod"=>'--',"Non-Prod"=>'--',"Admin"=>'--'];
               
           }else{
               if(date('Y-m-d') > $row['sched_date']){
                   $timein= 'ABSENT';
                   $timeout= '--';
                   $mnl_timein = '--';
                   $mnl_timeout= '--';
                   $late ='--';
                   $break = ['1st Break' => '--','2nd Break' =>'--','Toilet Break' => '--','Lunch Break' => '--'];
                   $prod = ["Prod"=>'--',"Non-Prod"=>'--',"Admin"=>'--'];
               }else{
                   $timein= '--';
                   $timeout= '--';
                   $mnl_timein = '--';
                   $mnl_timeout= '--';
                   $late ='--';
                   $break = ['1st Break' => '--','2nd Break' =>'--','Toilet Break' => '--','Lunch Break' => '--'];
                   $prod = ["Prod"=>'--',"Non-Prod"=>'--',"Admin"=>'--'];
               }
           }
       }else{
           $timein = date('h:i A', strtotime($lilo_row['timein']));
           $timeout =date('h:i A', strtotime($lilo_row['timeout']));
           $mnl_timein = date( 'h:i A', strtotime( psttomnl(date('Y-m-d',strtotime($lilo_row['timein'])), date('H:i',strtotime($lilo_row['timein'])))  ));
           $mnl_timeout = date( 'h:i A', strtotime( psttomnl(date('Y-m-d',strtotime($lilo_row['timeout'])), date('H:i',strtotime($lilo_row['timeout'])))  ));
           $late = 0;

           if(strtotime($lilo_row['timein']) > strtotime($row['pststarttime'])){
               $late = computeDuration(date('Y-m-d H:i', strtotime($lilo_row['timein'])),$row['sched_date'].' '.$row['pststarttime'] ) ;
           }

           $activity = array("1st Break","2nd Break", "Toilet Break","Lunch Break");
           $type= array("Prod","Non-Prod","Admin");

           foreach ($activity as $key => $activitys) {
               $util = $conn->query("SELECT SUM(elapsedtime) AS 'break' FROM tbl_utilization WHERE empid='".$row['empid']."' AND date= '".date('m/d/Y',strtotime($row['sched_date']))."' AND activity='$activitys' ");

            
               $util_row = $util->fetch_assoc();
               $break[$activitys] = $util_row['break'];
               
             

           }
           foreach ($type as $key => $types) {
            $util = $conn->query("SELECT SUM(elapsedtime) AS 'time' FROM tbl_utilization WHERE empid='".$row['empid']."' AND date= '".date('m/d/Y',strtotime($row['sched_date']))."' AND type='$types' ");

         
            $util_row = $util->fetch_assoc();
            $prod[$types] = $util_row['time'];

        }

               
           
       }

     
        //ATV19-173,ATV19-174,ATV19-175,ATV19-176,ATV19-177,ATV19-178,ATV19-179
        
        /* $leavesql = $conn->query("SELECT SUM(duration) as 'total' FROM `leave` WHERE empid='".$row['empid']."' AND type='Paid Time Off' AND status IN ('3','0','1','2') AND datefiled >= '2020-02-13' ");
        $leaverow = $leavesql->fetch_assoc();
        $total = $leaverow['total'];
        $leavecredit = $row['ptoearned'] - $row['usedpto'];
        $monthupdate = '2019-12-'.date('d',strtotime($row['hireddate'])); */
    

    // $conn->query("UPDATE pto SET monthupdate='$monthupdate' WHERE name='Paid Time Off'AND empid='".$row['empid']."' ");
    
    /* echo'<tr>
    <td>'.$row['empid'].'</td>
    <td>'.$row['firstname'].' '.$row['surname'].'</td>
    <td>'.$row['team'].'</td>
    <td>'.date('m/d/Y',strtotime($row['sched_date'])).'</td>
    <td>'.$timein.'</td>
    <td>'.$timeout.'</td>
    <td>'.$mnl_timein.'</td>
    <td>'.$mnl_timeout.'</td>
    <td>'.$late.'</td>
    <td>'.($break['1st Break'] == '' ? '0' : $break['1st Break']).'</td>
    <td>'.($break['2nd Break']== '' ? '0' : $break['2nd Break']).'</td>
    <td>'.($break['Toilet Break']== '' ? '0' : $break['Toilet Break']).'</td>
    <td>'.($break['Lunch Break']== '' ? '0' : $break['Lunch Break']).'</td>
    <td>'.($prod['Prod']== '' ? '0' : $prod['Prod']).'</td>
    <td>'.($prod['Non-Prod']== '' ? '0' : $prod['Non-Prod']).'</td>
    <td>'.($prod['Admin']== '' ? '0' : $prod['Admin']).'</td>

    

    


    </tr>'; */
    $data = [$row['empid'],$row['firstname'].' '.$row['surname'],$row['team'],date('m/d/Y',strtotime($row['sched_date'])),$timein,  $timeout, $mnl_timein,$mnl_timeout , $late,($break['1st Break'] == '' ? '0' : $break['1st Break']), ($break['2nd Break']== '' ? '0' : $break['2nd Break']), ($break['Toilet Break']== '' ? '0' : $break['Toilet Break']), ($break['Lunch Break']== '' ? '0' : $break['Lunch Break']),($prod['Prod']== '' ? '0' : $prod['Prod']),  ($prod['Non-Prod']== '' ? '0' : $prod['Non-Prod']),($prod['Admin']== '' ? '0' : $prod['Admin'])  ];

        fputcsv($csv, $data);
    }

    fclose($csv);
    $conn->close;

}?>