
 <link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div">
<h1>Over Time Summary Report</h1>
<?php //error_reporting(0);
include('./includes/fun-php.php');
include('./includes/fun-HRClass.php');
   if($_REQUEST['Search'] )
  {  
	   $paystatus=true;  
	   $years=$_REQUEST['payyear'];
	   $month=$_REQUEST['paymonth'];
	   $empcode=$_REQUEST['empcode'];
	   $emptype=$_REQUEST['emptype'];
	   $employment_type=$_REQUEST['employment_type'];
	   $site=$_REQUEST['site'];	   
	   
	   	$_SESSION['payyear']=$_REQUEST['payyear'];
		$_SESSION['paymonth']=$_REQUEST['paymonth'];
		$_SESSION['empcode']=$_REQUEST['empcode'];
		$_SESSION['emptype']=$_REQUEST['emptype'];
		$_SESSION['employment_type']=$_REQUEST['employment_type'];
 		$_SESSION['emp_salprocess_site']=$_REQUEST['site'];	   
	  } ?>	 
<form action="" method="post">
<table border="0" cellpadding="3" cellspacing="0" width="100%">
<tr class="row1">
	<td width="13%" align="right">Branch</td>
	<td width="20%"><select name="site" id="site"  onchange="ShowDiv(this.value,'hid-empshift','GetEmployeeShift.php')">
	<option value="0">All</option>
	<?php 
	if($_REQUEST['site']!='000'){
	$site_sql=DB_query("Select loccode, locationname from locations  where CompanyId='".$_SESSION['ACGCompanyId']."' and loccode in('".$_SESSION['AssignedBranch']."')",$db);}
	else
	{$site_sql=DB_query("Select loccode, locationname from locations",$db);}
	while($site_row=DB_fetch_array($site_sql)){

	?>
	<option value="<?php echo $site_row['loccode'];?>"<?php echo ($_REQUEST['site']==$site_row['loccode']?'selected':'');?>><?php echo $site_row['locationname'];?></option>
	<?php } ?>
	</select></td>
	<td width="13%" align="right">Month</td>
	<td width="20%"><select name="paymonth" >
	 <?php
	 $arr=array(1=>"Jan","Feb","Mar","Apr","May","June","July","Aug","Sept","Oct","Nov","Dec");
	 foreach ($arr as $key => $value) {
	 	$p='';
	 	if( $key==$_REQUEST['paymonth'])
		$p='selected';
	   ?><option value="<?php echo $key;?>" <?php echo $p;?> ><?php echo $value;?></option>
	 <?php } ?></select>
		  </td> 
	<td width="13%" align="right">Year</td>
	<td><select name="payyear" >
	 <?php
	 
	 for($i=date('Y');$i>2000;$i--)
	 {
	 	$p='';
	 	if( $i==$_REQUEST['payyear'])
		$p='selected';
	 ?><option value="<?php echo $i;?>" <?php echo $p;?>><?php echo $i;?></option>
	 <?php 
	 }
	 ?></select></td> 
</tr>
<tr class="row1">
	<td align="right">Employee Type</td>
	<td><div id='hid-empshift'>
	  <select name="emptype" id="emptype" >	 
	 	<?php 
	  	$emp_shift_sql=DB_query("Select id,shift_for,shift_category  from shift_master where site='".$_SESSION['emp_salprocess_site']."'",$db);
	 	while($emp_shift_row=DB_fetch_array($emp_shift_sql))
		{	 
		?>
		<option value="<?php echo $emp_shift_row['id'];?>"<?php echo ($_SESSION['emptype']==$emp_shift_row['id']?'selected':'')?>><?php echo $emp_shift_row['shift_for'].'('.$emp_shift_row['shift_category'].')'?></option>
		<?php } ?>
		</select>	
		</div></td>
	<td align="right">Employment Type</td> 
	<td><select name="employment_type"  onchange="ShowDiv(this.value,'hid-empdiv','GetEmployee.php?site='+document.getElementById('site').value+'&emptype='+document.getElementById('emptype').value)" > 
	<option value="0">All</option>
	<option value="Permanent" <?php if($_REQUEST['employment_type']=='Permanent') echo "selected";?>>Permanent</option>
	<option value="Temporary" <?php if($_REQUEST['employment_type']=='Temporary') echo "selected";?>>Temporary</option>
	</select></td> 
	<td align="right">Employee Code</td>
	<td><div id='hid-empdiv'>
	 <?php	$emp_sql=DB_query("Select id, emp_code,emp_name,employee_type from empmaster where  employee_type='".$_SESSION['emptype']."' and  employment_type='".$_SESSION['employment_type']."'    and emp_site='".$_SESSION['emp_salprocess_site']."'order by emp_name",$db);
	?>
	 <select name="empcode" id="empcode" >
			<option value="All">All</option>
			<?php 	

			while($emp_row=DB_fetch_array($emp_sql))
			{?>
				<option value="<?php echo $emp_row['id'];?>"<?php if($_SESSION['empcode']==$emp_row['id'])echo "selected";?>><?php echo $emp_row['emp_name'].' ( '.$emp_row['emp_code'].' ) ' ;?></option>
			<?php 	
			}
			?>
		</select>	
	 </div></td>
</tr>
<tr class="row1">
	<td colspan="6" align="center"><input type="submit" name="Search" class="search" value="Search" /></td>
</tr>
</table>	
</form>	

<?php 
  if($_REQUEST['Search'] )
  {
  		$days_in_month=cal_days_in_month(CAL_GREGORIAN ,$month , $years );
		$timestamp = mktime(0, 0, 0, $month, 1);
		$monthName = date('M', $timestamp );
  
  ?>

<p align="right"><a href="Print-overtimesummary.php" class="search" target="_blank">Print</a></p>
<h3><font size="3px">OT Summary For The Month Of <?php echo $monthName?></font></h3>

 <?php 
	   if($emptype=='4' || $emptype=='7')
	   { $cond="and gross_sal<10000";}
	   else{$cond='';}
	   
	   $filter2='';
	   if(isset($site) && $site!='0')
	   $filter2[]="emp_site='".$site."'";
	   if(isset($empcode) && $empcode!='All')
	   $filter2[]="id = '".$empcode."'";
	   if(isset($employment_type) && $employment_type!='0')
	   $filter2[]="employment_type = '".$employment_type."'";
	   if(isset($emptype) && $emptype!='')
	   $filter2[]="employee_type = '".$emptype."'";
	   
	   if($filter2!='')
	   {
	   	$filter2=implode(' and ',$filter2);
	   }
	   
	   $dept_sql="SELECT DISTINCT(department) as dept FROM empmaster where $filter2 $cond";
	  //echo $dept_sql;
	  
	  
	   $_SESSION['query2']=$dept_sql;
	   $dept_res=DB_query($dept_sql,$db);
	   
	   while($dept_row=DB_fetch_array($dept_res))
	  {

		$total_OT=array();
		$department_code=$dept_row['dept'];
		$department_name=$s->GetFieldValue('deptmaster',"deptcode='".$department_code."'",'deptname');
	  ?>
	  <table width="100%" border="0" cellpadding="3" cellspacing="0" class="thborder">
  	  <tr>
      <th colspan="6" style="text-align:left;">Department : <?php echo $department_name;?></th>
     </tr>
	   <?php 
	   $filter='';
	   if(isset($site) && $site!='0')
	   $filter[]="A.emp_site='".$site."'";
	   if(isset($empcode) && $empcode!='All')
	   $filter[]="A.id = '".$empcode."'";
	   if(isset($employment_type) && $employment_type!='0')
	   $filter[]="A.employment_type = '".$employment_type."'";
	   
	   if(isset($filter) && $filter!='')
	   {
			$filter=implode(' and ',$filter);
			if($emptype!='0'){ 
		$sql="Select A.department, A.id as employee_id, A.emp_weekly_off, A.emp_name, A.emp_code, A.employee_type, A.gross_sal, B.*, C.shift_for, C.id as shift_id from empmaster A , daily_attendance B, shift_master C where $filter and A.id=B.employee and A.employee_type=C.id and MONTH(B.attendance_date)='0".$month."' and YEAR(B.attendance_date)='".$years."' and A.employee_type ='".$emptype."' and A.department='".$department_code."' GROUP BY A.emp_code"; }
		else 
		{
			$sql="Select A.department, A.id as employee_id, A.emp_weekly_off, A.emp_name, A.emp_code, A.employee_type, A.gross_sal, B.*, C.shift_for, C.id as shift_id from empmaster A , daily_attendance B, shift_master C where $filter and A.id=B.employee and A.employee_type=C.id and MONTH(B.attendance_date)='0".$month."' and YEAR(B.attendance_date)='".$years."' and A.department='".$department_code."'  GROUP BY A.emp_code";
		}
	   }
	   else if(empty($filter) && $filter=='')
	   {
			if($emptype!='0'){ 
		$sql="Select A.department, A.id as employee_id, A.emp_weekly_off, A.emp_name, A.emp_code, A.employee_type, A.gross_sal, B.*, C.shift_for, C.id as shift_id from empmaster A , daily_attendance B, shift_master C where A.id=B.employee and A.employee_type=C.id and MONTH(B.attendance_date)='0".$month."' and YEAR(B.attendance_date)='".$years."' and A.employee_type ='".$emptype."' and A.department='".$department_code."'   GROUP BY A.emp_code "; }
		else
		{
			$sql="Select A.department, A.id as employee_id, A.emp_weekly_off, A.emp_name, A.emp_code, A.employee_type, A.gross_sal, B.*, C.shift_for, C.id as shift_id from empmaster A , daily_attendance B, shift_master C where A.id=B.employee and A.employee_type=C.id and MONTH(B.attendance_date)='0".$month."' and YEAR(B.attendance_date)='".$years."' and A.department='".$department_code."'   GROUP BY A.emp_code ";
		}	   
	   }
	  
	   $_SESSION['query']=$sql;
	   $_SESSION['month']=$month; 
	   $_SESSION['year']=$years; 
		
		//echo $sql;
	   $result=DB_query($sql,$db);
?>
	<tr>
    <th>Employee Name</th>
	<th width="10%">Employee Code</th>
    <th width="10%">Total OT Hours/Days</th>
	<th width="10%">Wages Rate</th>
    <th width="10%">OT Amount</th>
	<th width="10%">Signature</th>
   </tr>
<?php 

	   while($myrow=DB_fetch_array($result))
	   {
	   //echo $myrow['gross_sal']."===".$myrow['employee_type']."<br>";
	   if(($myrow['gross_sal']>=10000 && $myrow['employee_type']=='4') || $myrow['gross_sal']>=10000 && $myrow['employee_type']=='7'){echo "";}
	   else {
	   
	 		$emp_id=$myrow['employee_id'];
			$emp_name=$myrow['emp_name'];
			$emp_code=$myrow['emp_code'];
			$emp_status=$myrow['shift_for'];
			$emp_week_off=$myrow['emp_weekly_off'];
			$week_off_day=GetDay($emp_week_off);
			$gross_salary=$myrow['gross_sal'];
			$emp_status_id=$myrow['employee_type'];
			$emp_dept_id=$myrow['department'];
			$emp_dept_name=$s->GetFieldValue('deptmaster',"deptcode='".$emp_dept_id."'",'deptname');

			$startdate=firstOfMonth($month,$years);
			$end_date=lastOfMonth($month,$years);
			
			if($emp_status=='Worker')
			{
				$TotalOt=getOTHours($emp_id,$month,$years);
				
				if($gross_salary>5000)
				$wages_rate=5000;
				else
				$wages_rate=$gross_salary;
				
				
				$finalWorkHours='';
				$total_work_hrs_in_month='00:00';
				for($i=$startdate;$i<=$end_date;$i++)
				{
					
					//echo $i;
					//echo DayOfDate($i)."<br>=============";
					//echo $week_off_day."<br>";
					
					if(DayOfDate($i)==$week_off_day)
					{
						$array_of_week_off[]=$i;
						$check_week_off_present="Select work_hrs,pres_abs from daily_attendance where attendance_date='".$i."' and employee='".$emp_id."'";
						//echo $check_week_off_present."<br>";
						$check_result=DB_query($check_week_off_present,$db);
						//echo DB_num_rows($check_result);
						if(DB_num_rows($check_result)>0)
						{
							
							$check_row=DB_fetch_array($check_result);
							//echo $check_row['pres_abs']."<br>";
							if($check_row['pres_abs']=='P')
							{
								$work_hours=$check_row['work_hrs'];
								//echo $work_hours."<br>";
								$total_work_hrs_in_month_BRK=explode(':',$total_work_hrs_in_month);
								//echo $total_work_hrs_in_month_BRK[0];
								$work_hours_BRK=explode(':',$work_hours);
								//echo $work_hours_BRK[0];
								$total_hours=$total_work_hrs_in_month_BRK[0]+$work_hours_BRK[0];
								$total_min=$total_work_hrs_in_month_BRK[1]+$work_hours_BRK[1];
								//echo "<br>".$total_min;
								$total_work_hrs_in_month=$total_hours.":".$total_min;
								$quotient=$total_min/60;
								//echo "<br>".$quotient;
								$q=explode(".",$quotient);
								//echo "<br>".$q[0];
								if($q[0]!=0)
								{$r=$total_min%60;}
								else
								{$r=$total_min;}
								
								 $finalWorkHours=($total_hours+$q[0]).":".$r;
							}	
							
						}
						
					}
					
				}
				//echo $TotalOt."<br>";
				//echo "final===".$finalWorkHours."<br>";
				$TotalOt_BRK=explode(':',$TotalOt);
				$finalWorkHours_BRK=explode(':',$finalWorkHours);
				
				 $hrs=$TotalOt_BRK[0]+$finalWorkHours_BRK[0]; 
				 $min=$TotalOt_BRK[1]+$finalWorkHours_BRK[1];
				
				$quotient=$min/60;
				$q=explode(".",$quotient);
				
				//if($q[0]!=0)
				{$r=$min%60;}
				//else
				//{ $r=$min;}
				//echo "r====".$r;
				$FinalGrandTotalOT=($hrs+$q[0]).":".$r;
				
				$ot_amt_per_day=round($wages_rate/$days_in_month,2);
				$ot_amt_per_hour=round($ot_amt_per_day/8,2);

				$ot_hours_brk=explode(':',$FinalGrandTotalOT);
				$hours=$ot_hours_brk[0];
				$total_ot_amt_hrs=$hours*$ot_amt_per_hour;
				$minutes=$ot_hours_brk[1];
				$ot_amt_per_min=$ot_amt_per_hour/60;
				$total_ot_amt_min=$minutes*$ot_amt_per_min;
				$ot_grand_total=$total_ot_amt_hrs+$total_ot_amt_min;
			}
			
			else if($emp_status=='Staff')
			{
				$wages_rate=$gross_salary;
				$ot_condition=DB_query("Select OThours from ot_salary_master where emp_shift='".$emp_status_id."'",$db);
				$ot_condition_res=DB_fetch_array($ot_condition);
				
				$check_ot_time="SELECT COUNT(*) as count FROM daily_attendance WHERE TIME_TO_SEC(ot_hrs)>23400 AND employee='".$emp_id."' AND MONTH(attendance_date)='0".$month."' and YEAR(attendance_date)='".$years."'";
				//echo $check_ot_time;
				$check_res=DB_query($check_ot_time,$db);
				$check_row=DB_fetch_array($check_res);
				//echo $check_row['count'];
				$cr=0;
				for($i=$startdate;$i<=$end_date;$i++)
				{
					
					//echo DayOfDate($i);//==$week_off_day
					//echo $week_off_day;
					if(DayOfDate($i)==$week_off_day)
					{
						$check_week_off_present="Select work_hrs,pres_abs from daily_attendance where attendance_date='".$i."' and employee='".$emp_id."' and pres_abs='P' and TIME_TO_SEC(work_hrs)>23400";
						//echo $check_week_off_present."<br>";
						$check_result=DB_query($check_week_off_present,$db);
						$weekly_off_ot_days=DB_num_rows($check_result);
						if($weekly_off_ot_days==1)
						{$cr++;}
					}
				}
				
				
				if($check_row['count']>0 || $cr>0)
				{
					$FinalGrandTotalOT=$check_row['count']+$cr." Day(s)";
					if($wages_rate>='10000')
					{
						$ot_grand_total=$check_row['count']+$cr." CS";
					}
					else if($wages_rate<'10000')
					{
						$one_day_salary=$wages_rate/$days_in_month;
						$ot_grand_total=(($one_day_salary)*($check_row['count']+$cr));
					
					}
				}
				else {$FinalGrandTotalOT=''; $ot_grand_total='';}
			}
			
?> 
  <tr> 
    <td><?php 
	echo $emp_name; //echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(".$emp_dept_name.")"; ?></td>
	<td><?php echo $emp_code;?></td>
    <td><?php echo $FinalGrandTotalOT;?></td>
	<td><?php echo $wages_rate;?></td>
    <td><?php if($emp_status=='Worker'){echo round($ot_grand_total); $total_OT[]=round($ot_grand_total);}else {echo round($ot_grand_total); $total_OT[]=round($ot_grand_total);}
	?></td>
	<td>&nbsp;</td>
    </tr>

 <?php } }?> 

    <tr> 
    <td>&nbsp;</td>
	<td>&nbsp;</td>
    <td>&nbsp;</td>
	<td><strong>Grand Total</strong></td>
    <td><strong><?php //print_r($total_OT); 
	echo array_sum($total_OT);?></strong></td>
	<td>&nbsp;</td>
   </tr> 
 </table><br />
<?php } } ?>
</div>
</div>