<?php
require_once("dompdf/dompdf_config.inc.php");
include_once("class/class.MySQL.php");
$db		= new MySQL();

if($_POST['form'] =='formMst'){
	$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 #F4F4F4 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;"> Aadhocc </h4> </td></tr>
		<tr>
		<td style="text-align:center;width:90%;"><h3 style="margin:0px 0px 5px 0px;">ESI Report For '.date('M Y',strtotime($_REQUEST['from_date'])).'</h3> 
		</td>
		</tr>
		<tr>
		<td>
		
		</td>
		</tr>
	   </table>	
	  
		
		 
		<table width="100%" border="1" cellspacing="2" cellpadding="2" class="table">
		  <tr>
			<td class="title">SI NO</td>
			<td class="title">Employee.No.</td>
			<td class="title">Insurance No</td>
			<td class="title">Name Of Insured Person</td>
			<td class="title">ESI Gross </td>
			<td class="title">Employee Contribution</td>
			<td class="title">Employer Contribution</td>
			<td class="title">Total</td>				
		  </tr>
		 
 
		  ';
	
		$i=1;	
				  
		/*$userdetails = $db->fetchQuery("SELECT *,CONCAT(e_fname,e_lname)as emp_name FROM employee where emp_basic_pay!=0 AND STR_TO_DATE(date_join,'%d-%m-%Y') between STR_TO_DATE('".$_REQUEST[from_date]."','%d-%m-%Y') AND STR_TO_DATE('".$_REQUEST[to_date]."','%d-%m-%Y')");*/
		
		/*$userdetails = $db->fetchQuery("SELECT *,CONCAT(e_fname,e_lname)as emp_name FROM employee where emp_basic_pay!=0 and STR_TO_DATE(date_join,'%d-%m-%Y') between STR_TO_DATE('".$_REQUEST["from_date"]."','%d-%m-%Y') AND STR_TO_DATE('".$_REQUEST["to_date"]."','%d-%m-%Y')");*/
		//$sqlstring="SELECT *,CONCAT(e_fname,e_lname)as emp_name FROM employee where emp_basic_pay!=0 and date_join between '".$_REQUEST["from_date"]."' AND '".$_REQUEST["to_date"]."'";
			
	 $userdetails =  $db->fetchQuery("SELECT *,CONCAT(e_fname,e_lname)as emp_name,dtl_esi,total_payable FROM employee as em join payroll_salary_pay as paysal ON em.e_id = paysal.userid 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_esi from payroll_salary_pay_details where name like '%esi%')as saldtl4 on paysal.id=saldtl4.payment_id 
where month(paysal.pay_period) = '".date('m',strtotime($_REQUEST['from_date']))."' and year(paysal.pay_period) ='".date('Y',strtotime($_REQUEST['from_date']))."' ");
				
		
	$i=1;
	$sum_gross=0;
	$sum_esi=0;
		foreach($userdetails as $details) {
			
			 $html .=  '<tr>
					<td class="title">'.$i++.'</td>
					<td class="title">'.$details['e_id'].'</td>
					<td class="title"></td>
					<td class="title">'.$details['emp_name'].'</td>
					<td class="title">'.round($details['total_payable'],2).'</td>			
					<td class="title">'.round($details['dtl_esi'],2).'</td>			
					<td class="title"></td>						
					<td align="left" valign="top" class="title">'.round($details['dtl_esi'],2).'</td>					
				  </tr>
				';
				$sum_gross+= $details['total_payable'];
				$sum_esi+= $details['dtl_esi'];
				
				  
		}
		$html .= '
		 		 <tr>
					<td class="title"></td>
					<td class="title"></td>
					<td class="title"></td>
					<td class="title">Grand Total</td>
					<td class="title">'.round($sum_gross,2).'</td>
					  <td class="title">'.round($sum_esi,2).'</td>
					<td class="title"></td>
					<td class="title">'.round($sum_esi,2).'</td>
				  </tr>				  
				  
				  </table>
					<div style="position: fixed; left: 0px; bottom: -140px; right: 0px; height: 150px;">Date : '.date('d-M-Y').'
					<span style="padding-left:30px;">Stamp of the Factory / Estt.</span>
					<span style="padding-left:100px;">Signature of the Employer or</span>
					<span style="padding-left:290px;">other Authorised Officer of the Factory / Establishment</span>
					</div>
				   </div>
				   </body></html>';
	$dompdf = new DOMPDF();
	$dompdf->load_html($html);
	$dompdf->render();
	$dompdf->stream("ESI-Monthly Statement.pdf");
}

if($_POST['form'] =='form5'){
	$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 #F4F4F4 solid; padding-left:5px;}
		.table tr{height:30px;color:#202020;font-size:12px; line-height:20px;}
		.title{font-weight:bold;}
		</style>
	  </head>
	  <body>';
	
$sql="SELECT *,CONCAT(e_fname,e_lname)as emp_name,sum(dtl_esi) as esi,sum(total_payable) as total_wages,sum(worked_days) as days FROM employee as em join payroll_salary_pay as paysal ON em.e_id = paysal.userid 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_esi from payroll_salary_pay_details where name like '%esi%')as saldtl4 on paysal.id=saldtl4.payment_id  ";
		
	if(isset($_REQUEST["half_year"]))
		{
			$y=$_REQUEST["half_year"];
			if($y==1){
				$name="First Half of";
				$m1=4;
				$m2=9;
				$sql.="where month(paysal.pay_period) >= '".$m1."' and month(paysal.pay_period) <= '".$m2."'";
			}
			if($y==2){
				$name="Second Half of";
				$m1=10;
				$m2=12;
				$m3=1;
				$m4=3;
				$sql.="where month(paysal.pay_period) between '".$m1."' and '".$m2."' or month(paysal.pay_period)between '".$m3."' and '".$m4."'";
			}
		}	
		
	 $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;"> FORM 5 </h4> </td></tr>
		<tr>
		<td style="text-align:center;width:90%;"><h3 style="margin:0px 0px 5px 0px;">ESI Report For '.$name.' '.date('Y').'</h3> 
		</td>
		</tr>
		<tr>
		<td style="text-align:center;width:90%;"><h3 style="margin:0px 0px 5px 0px;">RETURN OF CONTRIBUTIONS</h3> 
		</td>
		</tr>
		<tr>
		<td style="text-align:center;width:90%;"><h3 style="margin:0px 0px 5px 0px;">EMPLOYEES STATE INSURANCE CORPORATION
</h3> 
		</td>
		</tr>
		<tr>
		<td>
		
		</td>
		</tr>
	   </table>	
	  
		
		 
		<table width="100%" border="1" cellspacing="2" cellpadding="2" class="table">
		  <tr>
			<td class="title">SI NO</td>			
			<td class="title">Insurance No</td>
			<td class="title">Name Of Insured Person</td>
			<td class="title">No. of days for which wages are paid </td>
			<td class="title">Total amount of wages paid</td>
			<td class="title">Employees Contribution</td>
			<td class="title">Average Daily Wages</td>
			<td class="title">Whether still continues working</td>
			<td class="title">Remarks</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>				
		  </tr>
		  ';
	
		$i=1;	
				  
		/*$userdetails = $db->fetchQuery("SELECT *,CONCAT(e_fname,e_lname)as emp_name FROM employee where emp_basic_pay!=0 AND STR_TO_DATE(date_join,'%d-%m-%Y') between STR_TO_DATE('".$_REQUEST[from_date]."','%d-%m-%Y') AND STR_TO_DATE('".$_REQUEST[to_date]."','%d-%m-%Y')");*/
		
		/*$userdetails = $db->fetchQuery("SELECT *,CONCAT(e_fname,e_lname)as emp_name FROM employee where emp_basic_pay!=0 and STR_TO_DATE(date_join,'%d-%m-%Y') between STR_TO_DATE('".$_REQUEST["from_date"]."','%d-%m-%Y') AND STR_TO_DATE('".$_REQUEST["to_date"]."','%d-%m-%Y')");*/
		//$sqlstring="SELECT *,CONCAT(e_fname,e_lname)as emp_name FROM employee where emp_basic_pay!=0 and date_join between '".$_REQUEST["from_date"]."' AND '".$_REQUEST["to_date"]."'";
		
		
 $userdetails =  $db->fetchQuery($sql);
				
		
	$i=1;
	$sum_gross=0;
	$sum_esi=0;
	foreach($userdetails as $details) {
			$average=$details['total_wages']/$details['days'];
			 $html .=  '<tr>
					<td class="title">'.$i++.'</td>
					<td class="title"></td>
					<td class="title">'.$details['emp_name'].'</td>
					<td class="title">'.$details['days'].'</td>
					<td class="title">'.round($details['total_wages'],2).'</td>			
					<td class="title">'.round($details['esi'],2).'</td>			
					<td class="title">'.round($average,2).'</td>			
					<td class="title"></td>						
					<td align="left" valign="top" class="title">'.round($details['dtl_esi'],2).'</td>					
				  </tr>
				';
				$sum_gross+= $details['total_payable'];
				$sum_esi+= $details['dtl_esi'];				
				  
		}
		$html .= '
		 		 <tr>
					<td class="title" colspan="3" align="right">Total</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"></td>
					
				  </tr>				  
				  
				  </table>
					<div style="position: fixed; left: 0px; bottom: -140px; right: 0px; height: 150px;">Date : '.date('d-M-Y').'
					<span style="padding-left:30px;">Stamp of the Factory / Estt.</span>
					<span style="padding-left:100px;">Signature of the Employer or</span>
					<span style="padding-left:290px;">other Authorised Officer of the Factory / Establishment</span>
					</div>
				   </div>
				   </body></html>';
	$dompdf = new DOMPDF();
	$dompdf->load_html($html);
	$dompdf->render();
	$dompdf->stream("ESI-Monthly Statement.pdf");
}

if($_POST['form'] =='form6'){
	$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 #F4F4F4 solid; padding-left:5px;}
		.table tr{height:30px;color:#202020;font-size:12px; line-height:20px;}
		.title{font-weight:bold;}
		</style>
	  </head>
	  <body>';
	
$sql="SELECT month(paysal.pay_period) as month,CONCAT(e_fname,e_lname)as emp_name,dtl_esi as esi,sum(total_payable) as total_wages,sum(worked_days) as days,d.desig_name,date_join,resigned_dat FROM employee as em left join gratuity_table g on g.e_id=em.e_id left join designation d on em.e_post=d.desig_id left join payroll_salary_pay as paysal ON em.e_id = paysal.userid 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_esi from payroll_salary_pay_details where name like '%esi%')as saldtl4 on paysal.id=saldtl4.payment_id  ";
		
	if(isset($_REQUEST["half_year"]))
		{
			$y=$_REQUEST["half_year"];
			if($y==1){
				$name="First Half of";
				$m1=4;
				$m2=9;
				$m3="";
				$m4="";
				$sql.="where month(paysal.pay_period) between '".$m1."' and '".$m2."'";
			}
			if($y==2){
				$name="Second Half of";
				$m1=10;
				$m2=12;
				$m3=1;
				$m4=3;
				$sql.="where month(paysal.pay_period) between '".$m1."' and '".$m2."' or month(paysal.pay_period)between '".$m3."' and '".$m4."'";
			}
		}	
			
		$sql.=" group by month(paysal.pay_period) ";
		
		
		
	 $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;"> FORM 6 </h4> </td></tr>
		<tr>
		<td style="text-align:center;width:90%;"><h3 style="margin:0px 0px 5px 0px;">ESI Report For '.$name.' '.date('Y').'</h3> 
		</td>
		</tr>
		<tr>
		<td style="text-align:center;width:90%;"><h3 style="margin:0px 0px 5px 0px;">REGISTER OF EMPLOYEES </h3> 
		</td>
		</tr>
		<tr>
		<td style="text-align:center;width:90%;"><h3 style="margin:0px 0px 5px 0px;">EMPLOYEES STATE INSURANCE CORPORATION
</h3> 
		</td>
		</tr>
		<tr>
		<td>
		
		</td>
		</tr>
	   </table>		
		 
		
		  ';
	
		$i=1;					  
		$userdetails =  $db->fetchQuery($sql);
			
		
	$i=1;
	$sum_gross=0;
	$sum_esi=0;
	$m="";
	
	if($y==1){
		$d1=4;
		$d2=5;
		$d3=6;
		$d4=7;
		$d5=8;
		$d6=9;
	}
	if($y==2){
		$d1=10;
		$d2=11;
		$d3=12;
		$d4=1;
		$d5=2;
		$d6=3;
	}
		
		$days=array();
		$total_wages=array();
		$total_esi=array();	
		$datejoin=array();
		
		foreach($userdetails as $details) {	
		for($m=$m1;$m<$m2;$m++){
				
				
			if($m==$details['month']){				
			
			$days[$m]=$details['days'];
			$total_wages[$m]=$details['total_wages'];
			$total_esi[$m]=$details['esi'];
			
			if(date('m',strtotime($details['date_join']))==$m){
				$datejoin[$m]=$details['date_join'];
			}
			elseif(date('m',strtotime($details['resigned_dat']))==$m){
				$datejoin[$m]=$details['resigned_dat'];
			}			
			
			/*if($details['date_join']!=""){
			$date.$m=$details['date_join'];
			}elseif($details['resigned_dat']!="")
			{
				$date.$m=$details['resigned_dat'];
			}*/			
		}
			
		}
	
		
		
	
	if($y==2){	
			for($j=$m3;$j<$m4;$j++){
				
				
			if($j==$details['month']){
			
		
			$days[$j]=$details['days'];
			$total_wages[$j]=$details['total_wages'];
			$total_esi[$j]=$details['esi'];
			
			if(date('m',strtotime($details['date_join']))==$j){
				$datejoin[$j]=$details['date_join'];
			}
			elseif(date('m',strtotime($details['resigned_dat']))==$j){
				$datejoin[$j]=$details['resigned_dat'];
			}
		
		}
		}
		}
		
		}
		
		$html.='<table width="100%" border="1" cellspacing="2" cellpadding="2" class="table">
		<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 colspan="3" class="title">Month  </td>						
		  </tr>
		  <tr>
			<td class="title">SI NO</td>			
			<td class="title">Insurance No</td>
			<td class="title">Name Of Insured Person</td>
			<td class="title">Name Of dispensary to which attached</td>
			<td class="title">Occupation </td>
			<td class="title">Deptt. and shift, if any</td>
			<td class="title">If appointed or left service during the contribution period, date of appointment/leaving service</td>
			<td class="title">No. of days for which Wages Paid/Payable </td>
			<td class="title">Total amount of wages paid/payable</td>
			<td class="title">Employees share of contribution</td>				
		  </tr>
		 
 			<tr>
			<td class="title">1</td>			
			<td class="title">2</td>
			<td class="title">3</td>
			<td class="title">3(A)</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>				
		  </tr>';
		foreach($userdetails as $details) {		
		
		$html .=  '<tr>
					<td class="title">'.$i++.'</td>
					<td class="title"></td>
					<td class="title">'.$details['emp_name'].'</td>
					<td class="title"></td>
					<td class="title">'.$details['desig_name'].'</td>			
					<td class="title"></td>			
					<td class="title">'.$datejoin[$d1].'</td>					
					<td class="title">'.$days[$d1].'</td>						
					<td class="title">'.round($total_wages[$d1],2).'</td>						
					<td align="left" valign="top" class="title">'.round($total_esi[$d1],2).'</td>				
				  </tr>
				';				
			  
		}
		 $html.='				  
				  </table>
				  <br/><br/>
				  ';
				  
				  
			$html.='<table width="100%" border="1" cellspacing="2" cellpadding="2" class="table">
			<tr>
			<td class="title" colspan="3">Month  </td>			
			<td class="title" colspan="3">Month </td>
			<td class="title" colspan="3"> Month  </td>		
		  </tr>
		  
		  <tr>
			<td class="title">No. of days for which Wages Paid/Payable </td>
			<td class="title">Total amount of wages paid/payable</td>
			<td class="title">Employees share of contribution</td>
			<td class="title">No. of days for which Wages Paid/Payable </td>
			<td class="title">Total amount of wages paid/payable</td>
			<td class="title">Employees share of contribution</td>	
			<td class="title">No. of days for which Wages Paid/Payable </td>
			<td class="title">Total amount of wages paid/payable</td>
			<td class="title">Employees share of contribution</td>					
		  </tr>
		 
 <tr>
			<td class="title">10</td>			
			<td class="title">11</td>
			<td class="title">12</td>
			<td class="title">13</td>
			<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>';
		
	foreach($userdetails as $details) {			  
		  $html .=  '<tr>									
					<td class="title">'.$days[$d2].'</td>						
					<td class="title">'.round($total_wages[$d2],2).'</td>						
					<td align="left" valign="top" class="title">'.round($total_esi[$d2],2).'</td>
					<td class="title">'.$days[$d3].'</td>						
					<td class="title">'.round($total_wages[$d3],2).'</td>						
					<td align="left" valign="top" class="title">'.round($total_esi[$d3],2).'</td>	
					<td class="title">'.$days[$d4].'</td>						
					<td class="title">'.round($total_wages[$d4],2).'</td>						
					<td align="left" valign="top" class="title">'.round($total_esi[$d4],2).'</td>					
				  </tr>
				';	 		
				  
		}
		  $html.='				  
				  </table>
				  <br/>
				  ';
				  
				  	$html.='<table width="100%" border="1" cellspacing="2" cellpadding="2" class="table">
			<tr>
			<td class="title" colspan="3">Month  </td>			
			<td class="title" colspan="3">Month  </td>
			<td class="title" colspan="4"  align="center">Summary  </td>				
		  </tr>
		  <tr>
			<td class="title">No. of days for which Wages Paid/Payable </td>
			<td class="title">Total amount of wages paid/payable</td>
			<td class="title">Employees share of contribution</td>
			<td class="title">No. of days for which Wages Paid/Payable </td>
			<td class="title">Total amount of wages paid/payable</td>
			<td class="title">Employees share of contribution</td>	
			<td class="title">Total No. of days for which Wages Paid/Payable in Contribution period</td>
			<td class="title">Total amount of wages paid/payable in Contribution period</td>
			<td class="title">Total Employees share of contribution in Contribution period</td>					
		  </tr>
		 
 <tr>
			<td class="title">19</td>			
			<td class="title">20</td>
			<td class="title">21</td>
			<td class="title">22</td>
			<td class="title">23</td>
			<td class="title">24</td>
			<td class="title">25</td>
			<td class="title">26</td>
			<td class="title">27</td>
			<td class="title">28</td>				
		  </tr>';
		
	foreach($userdetails as $details) {			  
		  $html .=  '<tr>									
					<td class="title">'.$days[$d5].'</td>						
					<td class="title">'.round($total_wages[$d5],2).'</td>						
					<td align="left" valign="top" class="title">'.round($total_esi[$d5],2).'</td>
					<td class="title">'.$days[$d6].'</td>						
					<td class="title">'.round($total_wages[$d6],2).'</td>						
					<td align="left" valign="top" class="title">'.round($total_esi[$d6],2).'</td>	
					<td class="title"></td>						
					<td class="title"></td>						
					<td class="title"></td>					
				  </tr>
				';	 		
				  
		}
		  $html.='				  
				  </table>
				  <br/>
				  ';
				  
		$html .= '<div style="position: fixed; left: 0px; bottom: -140px; right: 0px; height: 150px;">Date : '.date('d-M-Y').'
					<span style="padding-left:30px;">Stamp of the Factory / Estt.</span>
					<span style="padding-left:100px;">Signature of the Employer or</span>
					<span style="padding-left:290px;">other Authorised Officer of the Factory / Establishment</span>
					</div>
				   </div>
				   </body></html>';
				 
$dompdf = new DOMPDF();
	$dompdf->load_html($html);
	$dompdf->render();
	$dompdf->stream("ESI-Monthly Statement.pdf");
}

?>

