<?php
session_start();  
include_once("class/class.MySQL.php");

$db	=new MySQL();
  ///////////////////////////////////////
  
  if($_POST['status']=='ar'){
  		$html 	= '<html><head>
	
		<style type="text/css">
		body {font-family: "Trebuchet MS";color: #202020;font-size: 12px;line-height: 16px; background: #fff;}
		.content {
		  padding: 20px 15px;
		  overflow: auto;
		}
		h1,
		h2,
		h3,
		h4,
		h5,
		h6,
		.h1,
		.h2,
		.h3,
		.h4,
		.h5,
		.h6 {
		  font-family: "Source Sans Pro", sans-serif;
		}
		.table{
			border-collapse:collapse;
			margin-bottom:15px;
		}
		.table td{border:2px #e0dcdc solid; padding-left:5px;}
		.table tr{height:30px;color:#202020;font-size:12px; line-height:20px;}
		.title{font-weight:bold;}
		</style>
	  </head>
	  <body>';
	

	 $html .='
	  <div class="content" style="page-break-after:always;">
	  
	  <table style="width:100%;">
		<tr>
		<td style="text-align:center;width:90%;"><h4 style="margin:0px 0px 5px 0px;"> ACCIDENT REGISTER </h4> </td></tr>
				
		<tr>
		<td style="text-align:center;width:90%;"><h3 style="margin:0px 0px 5px 0px;">EMPLOYEES STATE INSURANCE CORPORATION
</h3> 
		</td>
		</tr>
	   </table>	
	  
		
		 
		<table width="100%" border="1" cellspacing="2" cellpadding="2" class="table">
		  <tr>
			<td class="title" style="vertical-align:middle;">SI NO</td>
			<td class="title" style="vertical-align:middle;">Date of Notice</td>
			<td class="title" style="vertical-align:middle;">Time of Notice</td>
			<td class="title" style="vertical-align:middle;">Name and Address Of Injured Person</td>
			<td class="title" style="vertical-align:middle;">Gender </td>
			<td class="title" style="vertical-align:middle;">Age </td>
			<td class="title" style="vertical-align:middle;">Insurance No</td>
			<td class="title" style="vertical-align:middle;">Shift, Department and Occupation of the employee</td>				<td class="title" colspan="5">Details of Injury</td>
		  </tr>
		  
		  <tr>
			<td class="title"></td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" >Cause</td>
			<td class="title" >Nature</td>
			<td class="title" >Date</td>
			<td class="title" >Time</td>
			<td class="title" >Place</td>
		  </tr>
		  
		  <tr>
			<td class="title">1</td>
			<td class="title" >2</td>
			<td class="title" >3</td>
			<td class="title" >4</td>
			<td class="title" >5</td>
			<td class="title" >6</td>
			<td class="title" >7</td>
			<td class="title" >8</td>
			<td class="title" >9</td>
			<td class="title" >10</td>
			<td class="title" >11</td>
			<td class="title" >12</td>
			<td class="title" >13</td>
		  </tr>
 
		  ';
	
		$i=1;	
		
				$cond=" where 1 ";
				 if(isset($_REQUEST["emp"])  && !empty($_REQUEST['emp'])){
		   	$cond.=" and e_id in (".$_REQUEST['emp'].") ";
		   }
	 $userdetails =  $db->fetchQuery("SELECT e_add,e_gender,date_join,CONCAT(e_fname,e_lname)as emp_name,desig_name,depart_name from employee em left join designation d on em.e_post=d.desig_id left join department dpt on d.departs_id=dpt.depart_id $cond");	
			
		
	$i=1;
	$sum_gross=0;
	$sum_esi=0;
		foreach($userdetails as $details) {
			if($details['date_join']!=""){
				$c= date('Y');
$y= date('Y',strtotime($details['date_join']));
$age= $c-$y;
			}else $age="";
			
//$age=date_diff(date_create($details['date_join']), date_create('today'))->y;	
			
			 $html .=  '
			  <tr>
			<td class="title">'.$i++.'</td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" >'.$details['emp_name'].','.$details['e_add'].'</td>
			<td class="title" >'.$details['e_gender'].'</td>
			<td class="title" >'.$age.'</td>
			<td class="title" ></td>
			<td class="title" >'.$details['depart_name'].', '.$details['desig_name'].'</td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" ></td>
		  </tr>';				
				  
		}
		$html .= ' </table>
		<br/><br/><br/><br/>
		<table width="100%" border="1" cellspacing="2" cellpadding="2" class="table">
		  <tr>
			<td class="title" style="vertical-align:middle;">What exactly was the injured person doing at the time
of accident
</td>
			<td class="title" style="vertical-align:middle;">Name, occupation, address and signature or the thumb impression of the person(s)giving notice</td>
			<td class="title" style="vertical-align:middle;">Signature and designation of the person who makes the entry in the Accident Book</td>
			<td class="title" style="vertical-align:middle;">Name, address and occupation of two witnesses
</td>
			<td class="title" style="vertical-align:middle;">Remarks, if any</td>
		  </tr>
		  
		 	  
		  <tr>
			<td class="title">14</td>
			<td class="title" >15</td>
			<td class="title" >16</td>
			<td class="title" >17</td>
			<td class="title" >18</td>
		  </tr>
 
		  ';
	
		$i=1;	
				  
				
	$i=1;
	$sum_gross=0;
	$sum_esi=0;
		foreach($userdetails as $details) {
			
			 $html .=  '
			  <tr>			
			<td class="title" ></td>
			<td class="title" >'.$details['emp_name'].', '.$details['e_add'].', '.$details['desig_name'].'</td>	
			<td class="title" ></td>
			<td class="title" ></td>
			<td class="title" ></td>
		  </tr>
		  
				';
				$sum_gross+= $details['total_payable'];
				$sum_esi+= $details['dtl_esi'];
				
				  
		}
		$html .= '
		 		 		  
				  
				  </table>
				   </div>
				   </body></html>';
				   
				   echo $html;
  	}
  	
  	
  if($_POST['status']=='tax'){
	$html 	= '<html><head>
	
		<style type="text/css">
		body {font-family: "Trebuchet MS";color: #202020;font-size: 12px;line-height: 16px; background: #fff;}
		.content {
		  padding: 20px 15px;
		  overflow: auto;
		}
		h1,
		h2,
		h3,
		h4,
		h5,
		h6,
		.h1,
		.h2,
		.h3,
		.h4,
		.h5,
		.h6 {
		  font-family: "Source Sans Pro", sans-serif;
		}
		.table{
			border-collapse:collapse;
			margin-bottom:15px;
		}
		.table td{border:2px #e0dcdc solid; padding-left:5px;}
		.table tr{height:30px;color:#202020;font-size:12px; line-height:20px;}
		.title{font-weight:bold;}
		</style>
	  </head>
	  <body>';
	if($_POST['location_id']=="0"){
		$head="RETURN OF TAX PAYABLE BY EMPLOYER UNDER SUB-SECTION(1) OF SECTION 6-A OF THE
KERALA NON MONTHLY ON PROFESSION, TRADE, CALLINGS AND EMPLOYMENT ACT 1976.";
	}else{
		$head="RETURN OF TAX PAYABLE BY EMPLOYER UNDER SUB-SECTION(1) OF SECTION 6-A OF THE
KERALA ON PROFESSION, TRADE, CALLINGS AND EMPLOYMENT ACT 1976.";
	}

	 $html .='
	  <div class="content" style="page-break-after:always;">
	  
	  
		 
		<table width="100%" border="1" cellspacing="2" cellpadding="2" class="table">
		  <tr>
			<td class="title">SI NO</td>
			<td class="title">Employees whose monthly salaries or
wages or both </td>
			<td class="title">No. of Employees </td>
			<td class="title">Rate of Tax</td>
			<td class="title">Amount of Tax Deducted</td>
			
		  </tr>';
	
		$i=1;	
		$sqlstring="SELECT * from professional_tax";
		
		$userdetails = $db->fetchQuery($sqlstring);
	$sum_cnt=0;
	$sum_tax=0;
		foreach ($userdetails as $details) {
			
			$payroll_sql="select count(distinct `userid`) as cnt from payroll_salary_pay WHERE total_earnings >= ($details[min_amount]/6) ";
			if($details['max_amount']>0){
				$payroll_sql="and total_earnings <= ($details[max_amount]/6)";
			}
			
			
			$payroll = $db->fetchQuery($payroll_sql);
			
			foreach ($payroll as $payroll_dtl) {
			$cnt=$payroll_dtl['cnt'];
			$total_tax=$payroll_dtl['cnt']*$details['tax'];
			}
			if($details['max_amount']>0){
				$max= "To $details[max_amount]";
			}else{
				$max= "and Above ";
			}
			 $html .=  '<tr>
					<td>'.$i++.'</td>
					<td>'.$details['min_amount'].' '.$max.'</td>
					<td>'.$cnt.'</td>
					<td>'.$details['tax'].'</td>
					<td>'.$total_tax.'</td>				
				  </tr>';
				
				$sum_cnt+=$cnt;  
				$sum_tax+=$total_tax;  
		}
		$html .= '
		 <tr>
			<td class="title"  colspan="2" align="right">Total</td>
			<td class="title">'.$sum_cnt.'</td>
			<td class="title"></td>
			<td class="title">'.$sum_tax.'</td>
			
		  </tr>
		   <tr>
			<td class="title"  colspan="3" align="right">Add : Simple interest payable (if any) on the above
amount at two percent per month or part thereof ( vide
section II (2) of the Act ). </td>			
			<td class="title"></td>
			<td class="title"></td>
			
		  </tr>
		  
		  
		  <tr>
			<td class="title"  colspan="2" align="right">Grand Total</td>
			<td class="title">'.$sum_cnt.'</td>
			<td class="title"></td>
			<td class="title">'.$sum_tax.'</td>
			
		  </tr>
		  </table>
		
			
					
					
				   </div>
				   </body></html>';
				   
				   echo $html;
	}
	
	
if($_GET['status']=='5'){

$fromdate='';
$todate='';
if(isset($_REQUEST['from_date'])){
$fromdate=$_REQUEST['from_date'];
}

if(isset($_REQUEST['to_date'])){
$todate=$_REQUEST['to_date'];
}	
$sqlstring="SELECT CONCAT(e_fname,e_lname)as emp_name, e_accountno,pf_accnum,e_mname,e_dob,e_gender,pf_date_join_fund,pf_service_year,pf_service_month,pf_service_day FROM employee where 1 ";
		/* if(isset($_GET["from_date"]) && !empty($_GET['from_date'])){
			$sqlstring.=" and DATE_FORMAT(date_join,'%d-%m-%Y') >= '$_GET[from_date]' ";
		}
		if(isset($_GET["to_date"]) && !empty($_GET['to_date'])){
			$sqlstring.=" and '$_GET[to_date]' >= DATE_FORMAT(date_join,'%d-%m-%Y') ";
		}*/
		
		/*if(isset($_GET["from_date"]) && !empty($_GET['from_date'])){
			$sqlstring.=" and date_join >= '$_GET[from_date]' ";
		}
		if(isset($_GET["to_date"]) && !empty($_GET['to_date'])){
			$sqlstring.=" and '$_GET[to_date]' >= date_join ";
		}*/
		
		if($fromdate!="" && $todate!=""){
		$sqlstring.=" and pf_date_join_fund between '$fromdate' and '$todate'";
		}
		 if(isset($_GET["type_sort"]) && !empty($_GET["type_sort"])){
			$sqlstring.=" order by $_GET[type_sort] ASC";	
		}
		
		$userdetails = $db->fetchQuery($sqlstring);
$arr=array();
$i=1;
   foreach($userdetails as $name)
   { 
    $total_period_previous_service = $name['pf_service_year'].' Years '.$name['pf_service_month'].' Months '.$name['pf_service_day'].' Days';  
    $arr[$i]['slno']=$i;
    $arr[$i]['e_accountno']=$name['pf_accnum'];
    $arr[$i]['emp_name']=$name['emp_name'];
    $arr[$i]['e_mname']=$name['e_mname'];   
    $arr[$i]['e_dob']=date('d M Y', strtotime($name['e_dob']));  
    $arr[$i]['e_gender']=$name['e_gender'];  
	$arr[$i]['pf_date_join_fund']=$name['pf_date_join_fund'];  
	$arr[$i]['total_period_previous_service']=$total_period_previous_service; 
	$i++;
   }
  
	
	echo '{"name":'.json_encode($arr).'}';
	}
	
  
if($_GET['status']=='10'){
	
$fromdate='';
$todate='';
if(isset($_REQUEST['from_date'])){
$fromdate=$_REQUEST['from_date'];
}

if(isset($_REQUEST['to_date'])){
$todate=$_REQUEST['to_date'];
}
 $sqlstring="SELECT CONCAT(e_fname,e_lname)as emp_name, pf_accnum,e_mname,e_dob,e_gender,pf_leaving_date,reason_leaving_pf,pfremarks FROM employee where 1 ";
	
		/* if(isset($_GET["from_date"]) && !empty($_GET['from_date'])){
			$sqlstring.=" and DATE_FORMAT(date_join,'%d-%m-%Y') >= '$_GET[from_date]' ";
		}
		if(isset($_GET["to_date"]) && !empty($_GET['to_date'])){
			$sqlstring.=" and '$_GET[to_date]' >= DATE_FORMAT(date_join,'%d-%m-%Y') ";
		}*/
		
		/*if(isset($_GET["from_date"]) && !empty($_GET['from_date'])){
			$sqlstring.=" and resigned_dat >= '$_GET[from_date]' ";
		}
		if(isset($_GET["to_date"]) && !empty($_GET['to_date'])){
			$sqlstring.=" and '$_GET[to_date]' >= resigned_dat ";
		}*/
		
		if($fromdate!="" && $todate!=""){
		$sqlstring.=" and pf_leaving_date between '$fromdate' and '$todate'";
		}		
		 if(isset($_GET["type_sort"]) && !empty($_GET["type_sort"])){
			$sqlstring.=" order by $_GET[type_sort] ASC";	
		}
		//echo $sqlstring;
		$userdetails = $db->fetchQuery($sqlstring);
$arr=array();
$i=1;
   foreach($userdetails as $name)
   {   
    $arr[$i]['slno']=$i;
    $arr[$i]['e_accountno']=$name['pf_accnum'];
    $arr[$i]['emp_name']=$name['emp_name'];
    $arr[$i]['e_mname']=$name['e_mname'];   
    $arr[$i]['resigned_dat']=date('d M Y', strtotime($name['pf_leaving_date']));     
	$arr[$i]['reason_leaving_pf']=$name['reason_leaving_pf'];
	$arr[$i]['pfremarks']=$name['pfremarks'];
	$i++;
   }
  
	
	echo '{"name":'.json_encode($arr).'}';
	}

  
if($_GET['status']=='mst'){
	

 $sqlstring="SELECT *,CONCAT(e_fname,e_lname)as emp_name,pf_accnum,dtl_basic,dtl_da,dtl_hra,dtl_pf,dtl_esi,dtl_allowance,dtl_incentive FROM employee as em join payroll_salary_pay as paysal ON em.e_id = paysal.userid left join designation as des on des.desig_id = em.e_post left join pf_esi_details as pfesi on pfesi.emp_id = em.e_id 
left join (select payment_id,amount as dtl_basic from payroll_salary_pay_details where name like '%basic%')as saldtl on paysal.id=saldtl.payment_id 
 left join (select payment_id,amount as dtl_da from payroll_salary_pay_details where name like '%da%')as saldtl1 on paysal.id=saldtl1.payment_id 
left join (select payment_id,amount as dtl_hra from payroll_salary_pay_details where name like '%hra%')as saldtl2 on paysal.id=saldtl2.payment_id 
left join (select payment_id,amount as dtl_pf from payroll_salary_pay_details where name like '%pf%')as saldtl3 on paysal.id=saldtl3.payment_id 
left join (select payment_id,amount as dtl_esi from payroll_salary_pay_details where name like '%esi%')as saldtl4 on paysal.id=saldtl4.payment_id 
left join (select payment_id,amount as dtl_allowance from payroll_salary_pay_details where name like '%allowance%')as saldtl5 on paysal.id=saldtl5.payment_id 
left join (select payment_id,amount as dtl_incentive from payroll_salary_pay_details where name like '%incentive%')as saldtl6 on paysal.id=saldtl6.payment_id 
where month(paysal.pay_period) = '".date('m',strtotime($_REQUEST['from_date']))."' and year(paysal.pay_period) ='".date('Y',strtotime($_REQUEST['from_date']))."' ";
		if(isset($_REQUEST["emp_id"]) && !empty($_REQUEST['emp_id'])){
			$sqlstring.=" and e_id = '$_REQUEST[emp_id]' ";
		}
		 if(isset($_REQUEST["filter_emp_name"]) && !empty($_REQUEST['filter_emp_name'])){
			$sqlstring.=" and e_fname = '$_REQUEST[filter_emp_name]' ";
		}
		if(isset($_REQUEST["pf_no"]) && !empty($_REQUEST['pf_no'])){
			$sqlstring.=" and pf_no = '$_REQUEST[pf_no]' ";
		}	
		
		 if(isset($_REQUEST["type_sort"]) && !empty($_REQUEST["type_sort"])){
			$sqlstring.=" order by $_REQUEST[type_sort] ASC";	
		}	
		
		//echo $sqlstring;
		$userdetails = $db->fetchQuery($sqlstring);
$arr=array();
$i=1;
   foreach($userdetails as $name)
   {   
    $arr[$i]['slno']=$i;
    $arr[$i]['e_id']=$name['e_id'];
    $arr[$i]['emp_name']=$name['emp_name'];
    $arr[$i]['dtl_basic']=$name['dtl_basic'];   
    $arr[$i]['dtl_allowance']=$name['dtl_allowance'];   
    $arr[$i]['dtl_pf']=$name['dtl_pf'];      
	$arr[$i]['pf_accnum']=$name['pf_accnum']; 
	$i++;
   }
  
	
	echo '{"name":'.json_encode($arr).'}';
	}


if($_GET['status']=='3a'){
	
 $cond="";
	
	/*$emp_id=23;
	 $cond = 'AND e_id IN('.$emp_id.')';*/
/*	if(!empty($emp_id))$cond = 'AND e_id IN('.implode(',',$emp_id).')';
	$userdetails = $db->fetchQuery("SELECT *,CONCAT(e_fname,e_lname)as emp_name FROM employee where emp_basic_pay!=0 $cond "); 
	foreach ($userdetails as $details)
	{*/
		// $employee_id=$details['e_id'];
		    $sqlstring="SELECT CONCAT(e_fname,e_lname)as emp_name,sum(total_paid) as total_paid,MONTHNAME(`payment_date`) AS month_name,c.percentage,d.amount,(d.amount*(c.percentage/100)) as pf FROM payroll_salary_pay a left join salary_structure_employee b on a.userid=b.employee_id left join salary_structure c on b.salary_structure_id=c.id left join( select id,amount from salary_structure) as d on d.id=c.parent_id inner join employee e on e.e_id=a.userid where emp_basic_pay!=0 ";
		 if(isset($_REQUEST["emp"])  && !empty($_REQUEST['emp'])){
		   	$sqlstring.=" and userid in (".$_REQUEST['emp'].") ";
		   }
		   
	    if(isset($_REQUEST["from_date"]) && !empty($_REQUEST['from_date'])){
		$sqlstring.=" and payment_date >= '$_GET[from_date]' ";
		}
		if(isset($_REQUEST["to_date"]) && !empty($_REQUEST['to_date'])){
			$sqlstring.=" and '$_REQUEST[to_date]' >= payment_date ";
		}
		/* if(isset($_REQUEST["emp_id"]) && !empty($_REQUEST['emp_id'])){
			$sqlstring.=" and e_id = '$_REQUEST[emp_id]' ";
		}
		 if(isset($_REQUEST["filter_emp_name"]) && !empty($_REQUEST['filter_emp_name'])){
			$sqlstring.=" and e_fname = '$_REQUEST[filter_emp_name]' ";
		}
		if(isset($_REQUEST["pf_no"]) && !empty($_REQUEST['pf_no'])){
			$sqlstring.=" and pf_no = '$_REQUEST[pf_no]' ";
		}*/
		/* if(isset($_REQUEST["from_date"])){
			$sqlstring.=" and DATE_FORMAT(payment_date,'%d-%m-%Y') >= '$_REQUEST[from_date]' ";
		}
		if(isset($_REQUEST["to_date"])){
			$sqlstring.=" and '$_REQUEST[to_date]' >= DATE_FORMAT(payment_date,'%d-%m-%Y') ";
		}*/
		$sqlstring.=" group by MONTHNAME(`payment_date`), userid order by ";
		 if(isset($_REQUEST["type_sort"]) && !empty($_REQUEST["type_sort"])){
			$sqlstring.=" $_REQUEST[type_sort] ASC, ";	
		}
		$sqlstring.="MONTH(`payment_date`) ASC";
		$userdetailss = $db->fetchQuery($sqlstring);
		$arr=array();
	$i=1;
   foreach($userdetailss as $name)
   {   
   // $arr[$i]['slno']=$i;
    $arr[$i]['emp_name']=$name['emp_name'];
    $arr[$i]['month_name']=$name['month_name'];
    $arr[$i]['total_paid']=$name['total_paid'];
    $arr[$i]['pf']=$name['pf'];   
	$i++;
   }
   
	//}

  
	
	echo '{"name":'.json_encode($arr).'}';
	}



if($_GET['status']=='6a'){
	
 $cond="";
	
	/*$emp_id=23;
	 $cond = 'AND e_id IN('.$emp_id.')';*/
/*	if(!empty($emp_id))$cond = 'AND e_id IN('.implode(',',$emp_id).')';
	$userdetails = $db->fetchQuery("SELECT *,CONCAT(e_fname,e_lname)as emp_name FROM employee where emp_basic_pay!=0 $cond "); 
	foreach ($userdetails as $details)
	{*/
		// $employee_id=$details['e_id'];
		    $sqlstring="SELECT e_accountno,CONCAT(e_fname,e_lname)as emp_name,sum(total_paid) as total_paid,MONTHNAME(`payment_date`) AS month_name,c.percentage,d.amount,(d.amount*(c.percentage/100)) as pf FROM payroll_salary_pay a left join salary_structure_employee b on a.userid=b.employee_id left join salary_structure c on b.salary_structure_id=c.id left join( select id,amount from salary_structure) as d on d.id=c.parent_id inner join employee e on e.e_id=a.userid where emp_basic_pay!=0 ";
		 if(isset($_REQUEST["emp"])  && !empty($_REQUEST['emp'])){
		   	$sqlstring.=" and userid in (".$_REQUEST['emp'].") ";
		   }
		   
	    if(isset($_REQUEST["from_date"]) && !empty($_REQUEST['from_date'])){
		$sqlstring.=" and payment_date >= '$_REQUEST[from_date]' ";
		}		
		
		$sqlstring.=" group by MONTHNAME(`payment_date`), userid order by ";
		 if(isset($_REQUEST["type_sort"]) && !empty($_REQUEST["type_sort"])){
			$sqlstring.=" $_REQUEST[type_sort] ASC, ";	
		}
		$sqlstring.="MONTH(`payment_date`) ASC";
		
		$userdetailss = $db->fetchQuery($sqlstring);
		$arr=array();
	$i=1;
   foreach($userdetailss as $name)
   {   
    $arr[$i]['slno']=$i;
    $arr[$i]['e_accountno']=$name['e_accountno'];
    $arr[$i]['emp_name']=$name['emp_name'];
    $arr[$i]['total_paid']=$name['total_paid'];
    $arr[$i]['pf']=$name['pf'];   
	$i++;
   }
   
	//}

  
	
	echo '{"name":'.json_encode($arr).'}';
	}


?>