<?php //include("../secure.php");
session_start(); 
  include("dbConfig.php");
   $uquery=mysqli_query($conn,"select * from `user_tbl` where user_id='".$_SESSION['user_id']."'");
	$urow=mysqli_fetch_assoc($uquery);
	$user_id=$urow['user_id'];
	   $sql_c="SELECT * FROM user_tbl where  user_id='".$_SESSION['user_id']."' ";
       $result_c = $conn->query($sql_c);
       
       
   
	header("Content-Type: application/xls");    
	header("Content-Disposition: attachment; filename=file.xls");  
	header("Pragma: no-cache"); 
	header("Expires: 0");


    

	
$setData = chr(255) . chr(254) . mb_convert_encoding($setData, "UTF-16LE", "UTF-8");
// add encoding in headers
header("Content-Encoding: UTF-16LE");
header("Content-type: text/csv; charset=UTF-16LE");

header("Content-Disposition: attachment; filename=Attendance_departure_detection.xls");
header("Pragma: no-cache");
header("Expires: 0");
	
	
	 
	 
	 
   $lang=$_GET['lang'];  
	$link_lang='lang='.$lang;

include("../languege_filee_hr.php");
 


$date_from=$_GET['date_from'];
$date_to=$_GET['date_to']; 


$main_system_id='2222';
$wind=$_GET['wind']; //echo $wind;

$company_id=$_GET['company_id'];
    $step_id=$_GET['step_id'];
    $date_from1=$_GET['date_from'];
    $date_to1=$_GET['date_to']; 
    $check_isset=$_GET['check_isset']; 
     $employ_id_sel = $_GET["employ_id_sel"];
     $todaydate = date("Y-m-d");
     
       $employ_stat = $_GET["employ_stat"];
       
       
       
     //================================================  
       
       
         function getBetweenDates($startDate, $endDate) {
                        $rangArray = [];
                     
                        $startDate = strtotime($startDate);
                        $endDate = strtotime($endDate);
                     
                        for ($currentDate = $startDate; $currentDate <= $endDate; $currentDate += (86400)) {
                            $date = date('Y-m-d', $currentDate);
                            $rangArray[] = $date;
                        }
                     
                        return $rangArray;
                    }
                    $dates = getBetweenDates($date_from, $date_to);
                   //print_r($dates);
                   $ids_dates = join("','",$dates); 
       
       
       
     
   //===============================
   
   	$a=array();
    $sql_comv="SELECT * FROM waseet_company_user_tbl where system_id='$main_system_id' AND deleted='0'AND user_id='".$_SESSION['user_id']."' GROUP BY company_id ";
     $result_comv = $conn->query($sql_comv);
     if ($result_comv->num_rows > 0) {
      while($row_comv = $result_comv->fetch_assoc()) {
      $company_id_all=$row_comv['company_id']; //echo $company_id_all.'<br>';
     array_push($a,$company_id_all);
      // echo $_SESSION['user_id'].'<br>';
        }} 
      // print_r($a);
      ?>
	<?php //echo  $company_id.'<br>';
	//echo $main_system_id;
	 //echo $urow['user_type'];
	  $ids = join("','",$a); 
	  
	//  print_r($a);
   //=================================
   
   include("../hr_permission.php");
	
	$output = "";
	
	$output .="
		<table>
			<thead>
				<tr>  
				   
					<th>".$id."</th>     
						<th>".$employee_name." </th>    
							<th>".$hawia_iqama_number."</th>

					<th>".$the_companyr."</th> 
						<th>".$dept."</th>
										<th>".$job."</th>  
	<th>".$Shift_Details."</th>  
		<th>عدد ساعات الحضور</th>  
				<th>جمالي ساعات الشفت</th>  
					<th>إجمالي ساعات الخصم</th>  
					
						<th>".$Total_salary."</th>  
							<th>".$Cut_from_salary."</th>  
								<th>".$remainder_of_the_salary."</th>  
								
										<th>".$date_fromm."</th>
											<th>".$date_too."</th>
									
								
								

				</tr>
			<tbody>
	";
	

/*
    if(($urow['user_type']=='Top_Manager' or $urow['user_type']=='System_Manager')or($view_employees_ul==1)){
                
            
        if($company_id=='all_company'){
               $sql_cw="SELECT * FROM waseet_company_user_tbl where system_id='2222' and company_id IN ('$ids') AND company_id!='' AND deleted='0' ORDER BY `waseet_company_user_tbl`.`crateddate_time` DESC";
                }else{
               $sql_cw="SELECT * FROM waseet_company_user_tbl where system_id='2222' and company_id='$company_id'AND company_id!='' AND deleted='0' ORDER BY `waseet_company_user_tbl`.`crateddate_time` DESC";
                }
                
               }else{
                  $sql_cw="SELECT * FROM waseet_company_user_tbl where system_id='2222' and user_id='".$urow['user_id']."'AND company_id!='' AND deleted='0' ORDER BY `waseet_company_user_tbl`.`crateddate_time` DESC";  
               }
                
               $result_cw = $conn->query($sql_cw);
               if ($result_cw->num_rows > 0) {
                while($row_cw = $result_cw->fetch_assoc()) {
                    
                      
                        $sql_emp="SELECT * FROM hr_employ_tbl where employ_stat='$employ_stat' and employ_id='".$row_cw['user_id']."' AND stop='0' AND deleted='0' ORDER BY `hr_employ_tbl`.`createdDate_timee`,`hr_employ_tbl`.`createdDate_timee` DESC ";
                      $result_emp = $conn->query($sql_emp);
                       if ($result_emp->num_rows > 0) {
                        while($row_emp = $result_emp->fetch_assoc()) { 
  
                    if($lang=='ar'){$employ_name=$row_emp['employ_name_ar'];}else{$employ_name=$row_emp['employ_name_en'];} 
                    */
                    
                    
                  //=========================================
                   
								   if(count($dates)==1 & $dates[0]=="1970-01-01"){

                                   }else{ ?>
									  <?php 
				 if(($urow['user_type']=='Top_Manager' or $urow['user_type']=='System_Manager')or($view_hr_attendance_report==1)){
					if($company_id=='all_company'){
                   $sql_cw="SELECT * FROM waseet_company_user_tbl where system_id='2222' and company_id IN ('$ids') AND company_id!='' AND deleted='0'   group by user_id ORDER BY `waseet_company_user_tbl`.`crateddate_time` DESC";
                    }else{
                   $sql_cw="SELECT * FROM waseet_company_user_tbl where system_id='2222' and company_id='$company_id'AND company_id!='' AND deleted='0'   group by user_id ORDER BY `waseet_company_user_tbl`.`crateddate_time` DESC";
                    }
                    
                   }else{
                      $sql_cw="SELECT * FROM waseet_company_user_tbl where system_id='2222' and user_id='".$urow['user_id']."'AND company_id!='' AND deleted='0'  group by user_id ORDER BY `waseet_company_user_tbl`.`crateddate_time` DESC";  
                   }
                   
                    
                   $result_cw = $conn->query($sql_cw);
                   if ($result_cw->num_rows > 0) {
                    while($row_cw = $result_cw->fetch_assoc()) {
                       

                        $sql_taked="SELECT * FROM hr_takeed_hodor_ensraf_tbl where (date_from in ('$ids_dates') or date_to in ('$ids_dates') ) and employ_id='".$row_cw['user_id']."' group by employ_id ";
                        $result_taked = $conn->query($sql_taked);
                         if ($result_taked->num_rows > 0) {
                          while($row_taked = $result_taked->fetch_assoc()) { 
                            $employ_id_taked = $row_taked["employ_id"];
                           //echo $employ_id_taked;
                           $sum_salary_ex=round($row_taked["sum_salary"],2);
                         
                  
                    $sql_emp="SELECT * FROM hr_employ_tbl where  employ_id='$employ_id_taked' and  employ_stat='active_employ' AND stop='0' AND deleted='0' group by employ_id ORDER BY `hr_employ_tbl`.`employ_name_ar` DESC ";
                          $result_emp = $conn->query($sql_emp);
                           if ($result_emp->num_rows > 0) {
                            while($row_emp = $result_emp->fetch_assoc()) {       
                          if($lang=='ar'){$employ_name=$row_emp['employ_name_ar'];}else{$employ_name=$row_emp['employ_name_en'];}
                          $employee_number_fingerprint = $row_emp["employee_number_fingerprint"]; 
                          $em_id = $row_emp['employ_id'];
                          $em_company = $row_emp['company_id'];
                          $em_dept = $row_emp['dept_id'];
                          $em_job = $row_emp['job_id'];
                          $em_id_num = $row_emp['id_num'];
                          $salary = $row_emp["basic_salary"]+$row_emp["housing_allowance"]+$row_emp["transportation_allowance"]+$row_emp["other_allowances"]+$row_emp["other_allowances_tow"];
                        }} 
                  //============================================
                    
                 
                    $employ_id=$row_emp['employ_id'];
                     $date_birthh=$row_emp['date_birth'];
                      $mobile_number=$row_emp['mobile_number'];
                       $employ_email=$row_emp['employ_email'];
                     if($row_emp["employ_sex"]=='male'){ $gender_type=$male;}elseif($row_emp["employ_sex"]=='female'){  $gender_type=$female;  }else{} 
                
                   if($row_emp["qualification"]==0){ $Qualificationn=$without_degree;}elseif($row_emp["qualification"]==1){  $Qualificationn=$High_school_diploma;
                   }elseif($row_emp["qualification"]==2){ $Qualificationn=$Diploma;  
                   }elseif($row_emp["qualification"]==3){ $Qualificationn=$Bachelor_Degree;
                   }elseif($row_emp["qualification"]==4){ $Qualificationn=$Master_degree; 
                   }elseif($row_emp["qualification"]==5){ $Qualificationn=$a_ph_d;
                   }else{}
                   
                   
                   if($row_emp["social_status"]=='0'){ $marital_statuss=$Celibate;
                   }elseif($row_emp["social_status"]=='1'){ $marital_statuss=$married;
                   }elseif($row_emp["social_status"]=='2'){ $marital_statuss=$separate; 
                   }else{}
                     
                   $id_num=$row_emp['id_num'];
                    $expiration_date_iqama=$row_emp['expiration_date_iqama'];
                     $id_release=$row_emp['id_release'];
                      $release_date=$row_emp['release_date'];
                       $occupationn=$row_emp['occupation'];
                        $Passport_numberr=$row_emp['Passport_number'];
                         $expir_date_passport=$row_emp['expir_date_passport'];
                         
                        $basic_salaryy=$row_emp['basic_salary'];
                        $housing_allowancee=$row_emp['housing_allowance'];
                        $transportation_allowancee=$row_emp['transportation_allowance'];
                        $other_allowancess=$row_emp['other_allowances'];
                        $other_allowances_tow=$row_emp['other_allowances_tow'];
                        $employ_vac=$row_emp['employ_vac'];
                        
                        if($row_emp["contract_status"]=='active'){$contract_status=$active;}elseif($row_emp["contract_status"]=='inactive'){$contract_status=$Inactive;}else{}
                          $date_employmentt=$row_emp['date_employment'];
                           $date_commencement=$row_emp['date_commencement'];
                            $contract_starting_datee=$row_emp['contract_starting_date'];
                             $contract_end_datee=$row_emp['contract_end_date'];
                              $trial_periodd=$row_emp['trial_period'];
                   
                   
                   if( $row_emp["office_employ"]=='office_employee'){ $office_employee_type=$Office_employee;}elseif($row_emp["office_employ"]=='not_office_employee'){$office_employee_type=$not_Office_employee;}else{}
                     if( $row_emp["employ_warranty"]=='On_warranty'){$employ_warrantyy=$On_warranty;}elseif($row_emp["employ_warranty"]=='not_On_warranty'){$employ_warrantyy=$not_On_warranty;}else{}
                      if($row_emp["s_s_discount"]==''){$s_s_discountt=$nothing;}else{$s_s_discountt=$row_emp["s_s_discount"];}
                       $employ_target=$row_emp['employ_target'];
                        $expiry_date_health=$row_emp['expiry_date_health'];
                         $license_expiry_date=$row_emp['license_expiry_date'];
                         
                          $iban_num=$row_emp['iban_num'];
                          
                           $medical_insurance_categoryy=$row_emp['medical_insurance_category'];
                            
                   
     //=================================================                
          $sum_add_hours_hodoor = 0;
                                    $sum_add_hours_shift = 0;
                                    $sum_add_hours_khasm = 0;
                                    $sum_khasem_from_salary = 0;
                                    $sql_clac="SELECT * FROM hr_takeed_hodor_ensraf_tbl where (date_from in ('$ids_dates') or date_to in ('$ids_dates') ) and employ_id='".$row_cw['user_id']."'";
                                    $result_calc = $conn->query($sql_clac);
                                     if ($result_calc->num_rows > 0) {
                                      while($row_clac = $result_calc->fetch_assoc()) {
                                        
                                        $sum_add_hours_hodoor+= round($row_clac["add_hours_hodoor"],2);
                                        $sum_add_hours_shift+= round($row_clac["add_hours_shift"],2);
                                        $sum_add_hours_khasm+= round($row_clac["add_hours_khasm"],2);
                                        $sum_khasem_from_salary+= round($row_clac["khasem_from_salary"],2);
                                      }}        
   
    //====================main_company===================
	$sql_company="SELECT * FROM main_company_tbl where company_id='".$row_cw['company_id']."'";
	 $result_company = $conn->query($sql_company);
    if ($result_company->num_rows > 0) {
     while($row_company = $result_company->fetch_assoc()) {
	if($lang=='ar'){$company_name=$row_company["company_name_ar"];}else{$company_name=$row_company["company_name_en"];} 
		 }
		}
		

	//==============dep=========================
	    $sql_c="SELECT * FROM dept_tb where dept_id='$em_dept' AND dept_id!='0' ";
        $result_c = $conn->query($sql_c);
        if ($result_c->num_rows > 0) {
       while($row_c = $result_c->fetch_assoc()) {
       if($lang=='ar'){$dept_name=$row_c['dept_name'];}else{$city_name2=$row_c['dept_name_en'];}
       }}
        
		
		//==============job=========================
		 $sql_jo="SELECT * FROM job_tb where job_id='$em_job' AND job_id!='0' ";
         $result_jo = $conn->query($sql_jo);
         if ($result_jo->num_rows > 0) {
          while($row_jo = $result_jo->fetch_assoc()) {
        if($lang=='ar'){$job_name=$row_jo['job_name'];}else{$job_name=$row_jo['jop_name_en'];} 
          }}
  //=====================country=================================================
    $sql_c1="SELECT * FROM country_tbl where country_id='".$row_emp['country_id']."' AND stop='0' AND deleted='0' ";
     $result_c1 = $conn->query($sql_c1);
     if ($result_c1->num_rows > 0) {
    while($row_c1 = $result_c1->fetch_assoc()) { 
    if($lang=='ar'){$country_name_ar=$row_c1['country_name_ar'];}else{$country_name_ar=$row_c1['country_name_en'];}   
    }}
    
    
  //====================================================================
  
                                      $sql_takedd="SELECT * FROM hr_takeed_hodor_ensraf_tbl where (date_from in ('$ids_dates') or date_to in ('$ids_dates') ) and employ_id='".$row_cw['user_id']."' group by shift_id ";
                                    $result_takedd = $conn->query($sql_takedd);
                                     if ($result_takedd->num_rows > 0) {
                                      while($row_takedd = $result_takedd->fetch_assoc()) {

                                        $sql_shift="SELECT * FROM hr_shift_tbl where shift_id='".$row_takedd["shift_id"]."' ";
                                        $result_shift = $conn->query($sql_shift);
                                         if ($result_shift->num_rows > 0) {
                                          while($row_shift = $result_shift->fetch_assoc()) {
                                             
                                        $min_start_time=$row_shift['min_start_time'];
                                        $start_time=$row_shift['start_time'];
                                         $max_start_time=$row_shift['max_start_time'];
                                         
                                          $min_end_time=$row_shift['min_end_time'];
                                          $end_time=$row_shift['end_time'];
                                          $max_end_time=$row_shift['max_end_time'];

                                          $number_hours = $row_shift['number_hours']; 
                                            if($lang=='ar'){$shift_name=$row_shift["shift_name_ar"];}else{$shift_name=$row_shift["shift_name_en"];}
  }}
    
   }}
    
    //=====================bank=================================================
  $sql_ba="SELECT * FROM bank_tb where bank_id='".$row_emp["bank_id"]."' AND stop='0' AND deleted='0' ";
  $result_ba = $conn->query($sql_ba);
  if ($result_ba->num_rows > 0) {
  while($row_ba = $result_ba->fetch_assoc()) { 
   if($lang=='ar'){$bank_name=$row_ba['bank_name_ar'];}else{$bank_name=$row_ba['bank_name_en'];}
  }}
  
 //=====================medical_insurance_tbl=================================================
   $sql_med="SELECT * FROM hr_medical_insurance_tbl where insurance_id='".$row_emp["insurance_id"]."' AND stop='0' AND deleted='0' ";
   $result_med = $conn->query($sql_med);
   if ($result_med->num_rows > 0) {
  while($row_med = $result_med->fetch_assoc()) { 
  if($lang=='ar'){$med_ins_com=$row_med['med_ins_com_ar'];}else{$med_ins_com=$row_med['med_ins_com_en'];}
  }}
  
 //========================
 
   $rest_sallary = round($salary - $sum_khasem_from_salary,2);
   
 //================================
  
    $sql_date="SELECT * FROM hr_takeed_hodor_ensraf_tbl where (date_from in ('$ids_dates') or date_to in ('$ids_dates') ) and employ_id='".$row_cw['user_id']."'";
                                    $result_date = $conn->query($sql_date);
                                     if ($result_date->num_rows > 0) {
                                      while($row_date = $result_date->fetch_assoc()) {
                    $fatra1=$row_date["date_from"];   
                    
                  $fatra2=$row_date["date_to"];
                  
                  $ff12=$fatra1.'-'.$fatra2;
                  
                                      }}
  
  //=================================================
  
  

        $output .= "
				<tr>
	
			
				  <td>".$em_id."</td>
				  <td>".$employ_name."</td>
				  <td>".$em_id_num."</td>
					<td>".$company_name."</td>
				  <td>".$dept_name."</td>
					<td>".$job_name."</td>
					<td>".$shift_name."</td>
					<td>".$sum_add_hours_hodoor."</td>
					
					<td>".$sum_add_hours_shift."</td>
					<td>".$sum_add_hours_khasm."</td>
					
					<td>".$sum_salary_ex."</td>
					<td>".$sum_khasem_from_salary."</td>
				
					<td>".$rest_sallary."</td>
					
					<td>".$date_from."</td>
						<td>".$date_to."</td>
					
					
				

				</tr>
	";
}} 

}
}} 

	
	$output .="
			</tbody>
			
		</table>
	";
	
	echo $output;
?>

