<?php
  session_name('CAKEPHP'); session_start();
  $PageSecurity = 15;
 $title = _('Monthly Attendance') ;
  include('includes/fun-php.php');
  function leave_allowed($empid,$type,$year,$eff_date,$leaveday)
{
	   global $db;
        $tot=0;
       //  echo $leaveday;
         //echo $eff_date;
          $eff_date=date('Y',strtotime($eff_date));
          $pre_year=$year-1;
        //echo $calLeaRecord = "Select between_date as total from emp_leave_apply where empid='".$empid."' and leave_type='".$type."' and approve_status='Y' and year(approved_date_from)='".$pre_year."' group by leave_type";	   
         $calLeaveRecord = "Select sum(total_approval_days) as total from emp_leave_apply where empid='".$empid."' 
          and leave_type='".$type."'  and approve_status='Y' and year(approved_date_from)='".$pre_year."' group by leave_type";	   
	      $ResultData = DB_query($calLeaveRecord,$db);
	      $RowData = DB_fetch_array($ResultData);

       
         $privious_opening = "Select opening as total_opening from hr_leave_opening where empid='".$empid."' 
          and leave_type='".$type."'   and opening_year='".$year."' group by leave_type";	   
	      $ResultData1 = DB_query($privious_opening,$db);
	      $RowData1 = DB_fetch_array($ResultData1);
		
          if($RowData1['total_opening']==''){ $opening_leave=0;}else { $opening_leave=$RowData1['total_opening'];}

        for($p=$eff_date;$p<$year;$p++)
        {
        $leavedays=($leavedays+$leaveday); 
        }
       //echo $leavedays;
       // echo $eff_date."--".$year;
        if($eff_date<$year){
         $tot= $leavedays-$RowData['total'];
        }
        if($eff_date==$year){
         $tot= $leaveday;  
        }

        return($tot+$opening_leave);
}
function leave_calculation($empid,$type,$year,$month)
{
	global $db;
         $calLeaveRecord = "Select sum(total_approval_days) as total from emp_leave_apply where empid='".$empid."' and leave_type='".$type."' 
        and approve_status='Y' and year(approved_date_from)='".$year."' and month(approved_date_from)='".$month."' group by leave_type";	   
	$ResultData = DB_query($calLeaveRecord,$db);
	$RowData = DB_fetch_array($ResultData);
	return($RowData['total']);
}

function leave_calculation_privious($empid,$type,$year,$month)
{

global $db;
         $calLeaveRecord = "Select sum(total_approval_days) as total from emp_leave_apply where empid='".$empid."' and leave_type='".$type."' 
        and approve_status='Y' and year(approved_date_from)='".$year."' and month(approved_date_from) <'".$month."' group by leave_type";	   
	$ResultData = DB_query($calLeaveRecord,$db);
	$RowData = DB_fetch_array($ResultData);
	return($RowData['total']);


}


function leave_calculationForApplied($empid,$type,$year)
{
	global $db;
	$calLeaveRecord = "Select sum(total_days) as total from emp_leave_apply where empid='".$empid."' and leave_type='".$type."' and year(period_from)='".$year."' group by leave_type  ";	   
	$ResultData = DB_query($calLeaveRecord,$db);
	$RowData = DB_fetch_array($ResultData);
	return($RowData['total']);
}

function Cancel_leave_calculation($empid,$type,$year)
{
	global $db;
	$calLeaveRecord = "Select sum(total_approval_days) as total from emp_leave_apply where empid='".$empid."' and leave_type='".$type."' and approve_status='C' and year(approved_date_from)='".$year."' group by leave_type  ";	   
	$ResultData = DB_query($calLeaveRecord,$db);
	$RowData = DB_fetch_array($ResultData);
	return($RowData['total']);
}


function dateDiff1($d1, $d2)
	{
		return round(abs(strtotime($d1)-strtotime($d2))/86400)+1;
	}  

$currentyear=date('Y');
?>

 <script type="text/javascript" language="javascript">
 
function open_print(url)
{
window.open(url,'popup','height=500,width=750,left=200,scrollbars=yes');
}
 
 
function check()
{

	 var month=document.getElementById("month").value;	  
	 if(month=='000')
	 {
	 	alert('Please select month'); 
		document.getElementById("month").focus();
		return false;
	 }
	 
	
}

</script>
<link rel="stylesheet" type="text/css" href="css/table_frize_1.css">
<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain" style="width:100%;">
<div class="tab_div" style="width:100%;">
<h1 align="center"><strong>Monthly Attendance</strong></h1>
<h3 align="center"><font color="#CC0033" ><strong>*Please select a month to view attendance</strong></font></h3>


<form action="" method="post">
 <table width="100%" align="center" class="" border="1" cellspacing="0" cellpadding="2" style="border-collapse:collapse;">
  <tr>
    <td width="13%" class="tabledata"><strong>For Month</strong></td>
  
    <td width="10%" class="tabledata">
		<select name="month" id="month" style="width:120px">
  			<option value="000">Select</option>
			<option value='01'<?php echo ($_REQUEST['month']=='01'?'selected':'')?>>January</option>
			<option value='02'<?php echo ($_REQUEST['month']=='02'?'selected':'')?>>February</option>
			<option value='03'<?php echo ($_REQUEST['month']=='03'?'selected':'')?>>March</option>
			<option value='04'<?php echo ($_REQUEST['month']=='04'?'selected':'')?>>April</option>
			<option value='05'<?php echo ($_REQUEST['month']=='05'?'selected':'')?>>May</option>
			<option value='06'<?php echo ($_REQUEST['month']=='06'?'selected':'')?>>June</option>
			<option value='07'<?php echo ($_REQUEST['month']=='07'?'selected':'')?>>July</option>
			<option value='08'<?php echo ($_REQUEST['month']=='08'?'selected':'')?>>August</option>
			<option value='09'<?php echo ($_REQUEST['month']=='09'?'selected':'')?>>September</option>
			<option value='10'<?php echo ($_REQUEST['month']=='10'?'selected':'')?>>October</option>
			<option value='11'<?php echo ($_REQUEST['month']=='11'?'selected':'')?>>November</option>
			<option value='12'<?php echo ($_REQUEST['month']=='12'?'selected':'')?>>December</option>
  		</select>	
  	</td>
    <td width="10%" class="tabledata"><strong>Year</strong></td>
    <td width="10%" class="tabledata">
		<select name="year" id="year" style="width:120px">
        <?php 
			for($year=date('Y');$year>1999;$year--){
		?>
        	<option value="<?php echo $year;?>"<?php echo ($_REQUEST['year']==$year?'selected':'')?>><?php echo $year;?></option>
        <?php }	?>
    	</select>
	</td>
  <td width="15%" class="tabledata"><strong>Branch</strong></td>
  <td width="10%" class="tabledata">
  	<select name="site" id="site" style="width:120px">
		<option value="0">Select</option>
		<?php 		
		
		$site_sql=DB_query("Select loccode, locationname from locations  where CompanyId='".$_SESSION['ACGCompanyId']."' 
			and loccode in('".$_SESSION['AssignedBranch']."')",$db);
			
			
			while($site_row=DB_fetch_array($site_sql))
			{
				$site1='';
				if($_REQUEST['site']==$site_row['loccode'])
					$site1="selected";
		?>
		<option value="<?php echo $site_row['loccode'];?>"<?php echo $site1; ?> ><?php echo $site_row['locationname'];?>
		</option>
		<?php }?>
	</select>
  </td>
    <td width="13%" class="tabledata"><strong>Employee</strong></td>
	<td width="10%" class="tabledata">
		<select name="employee_code" id="employee_code">
			<option value="">select</option>
			<?php 
				$emp_query = "select * from empmaster where CompanyId='".$_SESSION['ACGCompanyId']."'";
				$emp_res = DB_query($emp_query,$db) or die(DB_error());
				while($emp_result = DB_fetch_array($emp_res))
				{
			?>
			 <option value="<?php echo $emp_result['id'];?>" <?php if($_REQUEST['employee_code']==$emp_result['id']) { ?> selected="selected" <?php } ?>><?php echo $emp_result['emp_name'];?></option>
			<?php } ?>
		</select>
	&nbsp;</td>
  </tr>
  <tr>
  	<td colspan="8">
	<p align="center">
	<input type="submit" name="View" value="View"  onclick="return check();" class="search"/>

	<a href="#" onclick="open_print('Print-monthly_attendance.php')" id="print"><input type="button" value="Print" id="print" class="search"/></a>
	




	</p>
	</td>
  </tr>
</table>

</form>
<form action="" method="post">
<p align="left"><font color="#0000FF"><strong>P : Present<br />
  A : Absent<br />
  L : On Leave<br />
  HO : Holiday<br />
  WO : weekly Off<br /></strong></font></p>
<fieldset>
<div style="overflow:auto; width:auto ; height:auto">
<?php
if($_REQUEST['View']!='')
{
  $month=trim($_REQUEST['month']);
  $year=trim($_REQUEST['year']);
  $employee_code=trim($_REQUEST['employee_code']);
  $site=trim($_REQUEST['site']);
  
  $_SESSION['M']= $month;
  $_SESSION['Y']= $year;
  $_SESSION['EC']= $employee_code;
  $_SESSION['S']= $site;      

  if(date('m')==$month)
  	$workingdays=date('d');
  else
  	$workingdays= cal_days_in_month(CAL_GREGORIAN, $month, $year);
 ?>
 

<table width="100%"  border="1" cellpadding="3" cellspacing="0" style="border:#808080 1px solid;">
  <tr bgcolor="#CCCCCC">
    <td width="2%"><strong>Sr.</strong>

	</td>
    <td width="15%">&nbsp;</td>
    <td colspan="35"><strong>Dates</strong></td>
	
  </tr>
  <tr>
    <td><strong>No</strong></td>
    <td><strong>Employee Name</strong></td>

	<?php 
	  for($k=1;$k<=$workingdays;$k++)
	  {
	   $startdate=$k.'-'.$month.'-'.$year;
	   $day=DayOfDate($startdate);
	
	   $holiday_status=GET_Holidays($k,$month,$year);
	   $condition="day(start_date)='".$k."' and  month(start_date)='".$month."' and published='Y' and  year(start_date)='".$year."'";
	   $holidaytitle=$s->GetFieldValue('holidays',$condition ,'holiday_title');
	   
	  
		
	?>
    <td width="2%"><strong><?php  echo $k;?>
	 <?php  //echo $k.'<br>('.$day.')'.($holiday_status==1?$holidaytitle:"");?>
	</strong></td>
	<?php } ?>
	<td width="63">Leave</td>
   <td width="60">Allowed </td>  
    <td width="60">Taken</td>
    <td>Remaining</td>
    
  </tr>
<?php 
	$filter=array();
	if($employee_code!='')
		$filter[]=" id='".$employee_code."'";
	if ($site!='')
		$filter[]=" emp_site='".$site."'";
	if(count($filter)>0)
		$filter =implode(' and ', $filter);
		if($month==12){
			$month1=1;
			$year1=$year+1;
		}else{
			$month1=$month+1;
			$year1=$year;
		}	
	$searchval=$month1."/01/".$year1;
   	  $att_sql="select * from empmaster where $filter and  doj<'".date("Y-m-d",strtotime($searchval))."' and dol='0000-00-00'";
	$att_result=DB_query($att_sql,$db);
	$i=0;$j=1;
	$numrow1=DB_num_rows($att_result);
	if($numrow1>0)
	{
	while($att_row=DB_fetch_array($att_result))
	{	
?>
  <tr>
    <td><?php echo $j++;?></td>
    <td><?php echo $att_row['emp_name'];?></td>
	<?php 
	$pres_abs='';
/////................................progress work...................../////////	
		  

			
		    $date_sql="Select id,pres_abs,date(attendance_date) as date1,employee,leave_code
		    from daily_attendance where site='".$_REQUEST['site']."' and employee='".$att_row['id']."'
	           and month(attendance_date)='".$_REQUEST['month']."' and year(attendance_date)='".$_REQUEST['year']."'
			    order by  attendance_date asc";

		$date_res=DB_query($date_sql,$db);
		$date3='';
		while($date_row=DB_fetch_array($date_res))
		{	
		$date1=explode("-",$date_row['date1']);
		$date2=ltrim($date1[2], '0');		
		
	
		if($date_row['leave_code']!='000' && !is_null($date_row['leave_code']))
		{
		   $condition="id='".$date_row['leave_code']."'";		
		   $holidaytitle = $s->GetFieldValue('leavemaster',$condition ,'leavecode');
		}
		else if($date_row['leave_code']=='000' && is_null($date_row['leave_code']))
		{
			
					 					
			//$data['pres_abs']=$check;				
				
			$holidaytitle='';
		}

		
			if($date_row['pres_abs']!='' )
	 		{
			
				if($date_row['pres_abs']=='WL' && ($date_row['leave_code']=='000' || $date_row['leave_code']==''))
				{				
				$pres_abs[$date2]="WO";
				}
				else if($date_row['leave_code']!='' && $date_row['leave_code']!='000')
				{				
				$pres_abs[$date2]=$holidaytitle ;
				}
				else if ($date_row['leave_code']=='000' ||  is_null($date_row['leave_code']))	
				{				
	 			
				 			if($date_row['pres_abs']=='L')
				 			{
				 			$data_sql2 = "select * from emp_leave_apply where empid='".$att_row['id']."'
				 			  and  '".$date_row['date1']."' between period_from and period_to
					          and approve_status='Y'";
							  $data_sql2_res = DB_query($data_sql2,$db) or die(DB_error());
							  $data_sql2_result = DB_fetch_array($data_sql2_res);			
						      $pres_abs[$date2]=$s->getField('leavemaster','id',$data_sql2_result['leave_type'],'leavecode');	
						   }
						   else
						   {
			                  $pres_abs[$date2]=$date_row['pres_abs'];	
						   }		
				}

	 		}	
			else
	 		{
	 			$pres_abs[$date2]='L';
			}
		
           
		//	if($day=='Sun')
		  	//$pres_abs[]='WO';
		 	if($holiday_status==1) 
		 	$pres_abs[]='HO';
		}		
	
	for($k=1;$k<=$workingdays;$k++)
	{
	 $startdate=$k.'-'.$month.'-'.$year;
	   $day= DayOfDate($startdate);
		
       $holiday_status=GET_Holidays($k,$month,$year);
	 	if(0<$k && $k<10)
		{$d="0".$k;}
		else
		{$d=$k;}
		
		
	if($date3[$k]!="")
	{
		
		//$pres_abs[$k] = $date3[$k];
	}
	$data_array[$att_row['id']][]=array($k=>$pres_abs[$k]);
		
	?>
	<td style="<?php echo $bg;?>"><font color="#0000FF"><?php echo $pres_abs[$k];?></font></td>
    <?php }?>

    <td colspan="4" style="padding:0px;"><table width="100%"  border="1" cellpadding="3" cellspacing="0">
    <?php
      $GetEmpLeave = "Select * from emp_leave where  emp_id='".$att_row['id']."' ";	   
	  $ResultEmpLeave = DB_query($GetEmpLeave,$db);
	  while($RowEmpLeave=DB_fetch_array($ResultEmpLeave))
	  {

       $leaveType=$s->getField('leavemaster','id',$RowEmpLeave['leave_type'],'leavetype');
        $leavecode=$s->getField('leavemaster','id',$RowEmpLeave['leave_type'],'leavecode');

        if($leaveType=='c')
        {
        	 $eff_date=date('Y',strtotime($att_row['leave_eff_date']));
        	 if(($leavecode=='PL' || $leavecode='EL') && date("Y")==$eff_date)
               {
        	$privious_opening = "Select opening as total_opening from hr_leave_opening where empid='".$att_row['id']."' 
          and leave_type='".$RowEmpLeave['leave_type']."'   and opening_year='".$_REQUEST['year']."' group by leave_type";	   
	      $ResultData1 = DB_query($privious_opening,$db);
	      $RowData1 = DB_fetch_array($ResultData1);
		
          if($RowData1['total_opening']==''){ $opening_leave=0;}else { $opening_leave=$RowData1['total_opening'];}
          //echo $opening_leave;         
        	  $allowed_leave=$opening_leave+$RowEmpLeave['leave_days'];
        }
        else	
        $allowed_leave=leave_allowed($att_row['id'],$RowEmpLeave['leave_type'],$_REQUEST['year'],$att_row['leave_eff_date'],$RowEmpLeave['leave_days']);
        }
        if($leaveType=='e')
        {
		         $eff_date=date('Y',strtotime($att_row['leave_eff_date']));
		         $privious_opening = "Select opening as total_opening from hr_leave_opening where empid='".$att_row['id']."' 
		          and leave_type='".$RowEmpLeave['leave_type']."'   and opening_year='".$_REQUEST['year']."' group by leave_type";	   
			      $ResultData1 = DB_query($privious_opening,$db);
			      $RowData1 = DB_fetch_array($ResultData1);
				
		          if($RowData1['total_opening']==''){ $opening_leave=0;}else { $opening_leave=$RowData1['total_opening'];}
		          //echo $opening_leave;     
		    	
		          if($RowData1['total_opening']==''){ $opening_leave=0;}else { $opening_leave=$RowData1['total_opening'];}
		          if(($leavecode=='PL' || $leavecode='EL') && date("Y")==$eff_date)
		           {       	
		          //echo $opening_leave;         
		        	 $allowed_leave=$opening_leave;        }
		        else    
		        $allowed_leave=$RowEmpLeave['leave_days']+$opening_leave;    
        }
	$cancelleave=Cancel_leave_calculation($att_row['id'],$RowEmpLeave['leave_type'],$_REQUEST['year']);
	$AppliedLeaveDays=leave_calculationForApplied($att_row['id'],$RowEmpLeave['leave_type'],$_REQUEST['year']);
	$TakenLeaveDays=leave_calculation($att_row['id'],$RowEmpLeave['leave_type'],$_REQUEST['year'],$_REQUEST['month']);

	$TakenLeaveDays_privious=leave_calculation_privious($att_row['id'],$RowEmpLeave['leave_type'],$_REQUEST['year'],$_REQUEST['month']);
	$totalapplyleave=$AppliedLeaveDays-$cancelleave-$TakenLeaveDays;
	 $RemainingLeaveDays=$allowed_leave-($TakenLeaveDays+$TakenLeaveDays_privious);
     ?>	
    <tr>
    <td width="62"><?php echo $leavecode1= $s->getField('leavemaster','id',$RowEmpLeave['leave_type'],'leavecode'); ?></td>
    <td width="60"><?php  echo $allowed_leave= $allowed_leave-$TakenLeaveDays_privious;?> </td>  
    <td width="60"><?php if($TakenLeaveDays!=''){echo $taken_leave= $TakenLeaveDays;}else{echo $taken_leave= '0';}?></td>	
	<td><?php if($RemainingLeaveDays!=''){echo $Remaining= $RemainingLeaveDays;}else{echo $Remaining='0';}?></td>
    </tr>
    <?php
   $data_leave_dtls[$att_row['id']][]=array("leavecode"=>$leavecode1,"allowed_leave"=>$allowed_leave,"taken_leave"=>$taken_leave,"Remaining"=>$Remaining);

     } ?>
    </table></td>
   
  </tr>
   
  <?php $i++;} 
 $_SESSION['attendance_leave']=$data_leave_dtls;
$_SESSION['attandance_query']= $att_sql;
 $_SESSION['attandance']=$data_array;

} else { ?><tr><td colspan="33">No Records</td></tr><?php 
unset($_SESSION['attendance_leave']);
unset($_SESSION['attandance_query']);
unset($_SESSION['attandance']);
} } ?>
  

</table>
 <?php

 ?>
</div>
</fieldset>
</form>

</div>
</div>