<?php 
$PageSecurity = 15;
set_time_limit (900) ;
ini_set('session.bug_compat_42', 0);
$title = _('Monthly Attendance') ;
include('includes/fun-php.php'); 
?>
<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div">
<h1><?php echo $page_title; ?></h1>
<h3 align="center"><font color="#CC0033"><strong>*Upload monthly Attendance</strong></font></h3>

<form method="post" action="" enctype="multipart/form-data">
<table width="100%" border="0"  cellpadding="3" cellspacing="0">
<tr class="row1">
	<td width="13%"><strong>Import xlsx / xls File</strong></td>
	<td width="30%"><input type="file" name="imprt" id="imprt" />
	</td>
	<td align="40%" colspan="2"><input type="submit" name="impsave" value="Save" class="search" /></td>
</tr>
</table>
</form>
<?php
 require('XlsxReader/php-excel-reader/excel_reader2.php');
 require('XlsxReader/SpreadsheetReader.php');
class ExcelReader extends Spreadsheet_Excel_Reader {	
 function GetInArray($sheet=0) {
  $result = array();
   for($row=1; $row<=$this->rowcount($sheet); $row++) {
    for($col=1;$col<=$this->colcount($sheet);$col++) {
     if(!$this->sheets[$sheet]['cellsInfo'][$row][$col]['dontprint']) {
      $val = $this->val($row,$col,$sheet);
      $result[$row][$col] = $val;
     }
    }
   }
  return $result;
 }
}
if(isset($_REQUEST['impsave'])){
	if(isset($_FILES['imprt']['name']) && $_FILES['imprt']['name']!=''){
		$ext = substr($_FILES['imprt']['name'], strrpos($_FILES['imprt']['name'], '.') + 1); 
		if($_FILES['imprt']['name']!='' && $_FILES['imprt']['size']>0 && ($ext=='xlsx' || $ext=='xls')){ 
	 		$data = new ExcelReader($_FILES['imprt']['tmp_name']);
	 		$data_arr=$data->GetInArray();
	 		$emp_arr=array();
	 		for($i=1;$i<=count($data_arr);$i++){
	 			$j=1;
	 			for($k=1;$k<=count($data_arr[$i]);$k++)
	 			{
	 				$month3=$data_arr[2];
	 				
	 				if($data_arr[$i][$k]=='Days')
	 				{
	 					$days['days']=$data_arr[$i];
	 				}
	 				if($data_arr[$i][$k]=='Employee:')
	 				{
	 					$j=$j+3;
	 					$emp_id=explode(':',$data_arr[$i][$j]);
	 					continue 2;
	 				}
	 				if(isset($emp_id[0]) && $emp_id[0]!='')
					{
						if($data_arr[$i][$k]=='Status')$emp_arr[$emp_id[0]]['Status']=array_combine($days['days'],$data_arr[$i]);
						if($data_arr[$i][$k]=='InTime')$emp_arr[$emp_id[0]]['InTime']=array_combine($days['days'],$data_arr[$i]);
						if($data_arr[$i][$k]=='OutTime')$emp_arr[$emp_id[0]]['OutTime']=array_combine($days['days'],$data_arr[$i]);
						if($data_arr[$i][$k]=='Duration')$emp_arr[$emp_id[0]]['Duration']=array_combine($days['days'],$data_arr[$i]);
						if($data_arr[$i][$k]=='Late By')$emp_arr[$emp_id[0]]['Late By']=array_combine($days['days'],$data_arr[$i]);
						if($data_arr[$i][$k]=='Early By')$emp_arr[$emp_id[0]]['Early By']=array_combine($days['days'],$data_arr[$i]);
						if($data_arr[$i][$k]=='OT')$emp_arr[$emp_id[0]]['OT']=array_combine($days['days'],$data_arr[$i]);
						if($data_arr[$i][$k]=='Shift')$emp_arr[$emp_id[0]]['Shift']=array_combine($days['days'],$data_arr[$i]);
					}
	 			}
	 		}
	 		for($m=0;$m<count($month3);$m++)
	 		{
	 			if($month3[$m]!='')
	 			{
	 				$month2=$month3[$m];
	 			}
	 		}
	 		$month1=explode(' ',$month2); 
	 		$month=date('m',strtotime($month1[0]));
	 		$year=$month1[2];
	 		$sql="INSERT INTO `daily_attendance` (site,e_code,employee,attendance_date,attendance_day,attendance_month,attendance_year,time_in,time_out,late_arrival,ot_hrs,work_hrs,pres_abs,shift_category,shift_start,shiftstart_AMPM,shift_end,shiftend_AMPM,created_date,created_by) values ";
	 		foreach($emp_arr as $emp_key=>$emp_value)
	 		{
	 			$site=$s->GetFieldValue('empmaster'," emp_code='".$emp_key."'" ,'emp_site');
	 			$emp_id=$s->GetFieldValue('empmaster'," emp_code='".$emp_key."'" ,'id');
	 			$shiftcat = $s->GetFieldValue('empmaster'," emp_code='".$emp_key."'" ,'employee_type');
                $shiftStart = $s->GetFieldValue('shift_master'," id='".$shiftcat."'" ,'shift_start');
                $start_AMPM= $s->GetFieldValue('shift_master'," id='".$shiftcat."'" ,'start_AMPM');  
                $end_AMPM=$s->GetFieldValue('shift_master'," id='".$shiftcat."'" ,'end_AMPM'); 
                $shiftEnd = $s->GetFieldValue('shift_master'," id='".$shiftcat."'" ,'shift_end');

	 			if(isset($emp_id) && $emp_id!='')
	 			{
	 				$day_wise_atte = array_values($emp_value);
		            $i=1;$j=-1;
		            $status_array = $day_wise_atte[0];
	                foreach($status_array as $key=>$value)
	                {
	                	if($i>2)
	                	{
	                		$dt1=$year."/".$month."/".$j;
		            		$atten_dt=date('Y-m-d',strtotime($dt1));
		            		$j=str_pad($j,2,0,STR_PAD_LEFT);
	                		$values .="('".$site."','".$emp_key."','".$emp_id."','".$atten_dt."','".$j."','".$month."','".$year."','".$emp_value['InTime'][$key]."','".$emp_value['OutTime'][$key]."','".$emp_value['Late By'][$key]."','".$emp_value['OT'][$key]."','".$emp_value['Duration'][$key]."','".$emp_value['Status'][$key]."','".$shiftcat."','".$shiftStart."','".$start_AMPM."','".$shiftEnd."','".$end_AMPM."','".date('Y-m-d')."','".$_SESSION['UserID']."'),";
	                	}
	                	$i++; 
	                	$j++;
	                }
	 			}
	 			else{
	 				$not_updated_emp_arr[]=$emp_key;
	 			}
	 		}
	 		//echo $values; exit;
	 		//echo "<pre>"; print_r($not_updated_emp_arr);
	 		$values=rtrim($values,',');
	 		$final_sql=$sql.$values;
	 		$ins=DB_query($final_sql,$db);
	 		/*if(count($not_updated_emp_arr)>0)
	 		{			
				header("Content-type: application/x-msdownload"); 
				header("Content-Disposition: attachment; filename=not_updated_employee.xls"); 
				header("Pragma: no-cache"); 
				header("Expires: 0"); 
	 		}*/
	 		if($ins)
	 		{
	 			echo $msg="<br><font color='#009933' size='+1'>File has been imported successfully.</font>";
				$s->pageLocation('index.php?route=HRModule/Master/biometric_attendance_new&modid='.$_REQUEST['modid'].'&frmid='.$_REQUEST['frmid'].'');
			}
			else
			{
				echo $msg="<br><font color='#009933' size='+1'>File has not been imported successfully.</font>";
			}

		}
		else
		{
			?>
	 			<script> alert("<?php echo 'Please Select valid file';?>");</script>
	 		<?php 
		} 
	}else{
		?>
	 		<script> alert("<?php echo 'Please Select file First.';?>");</script>
	 	<?php
	}
}
?>
</div>
</div>
