<?php
session_start();

include_once("class/class.MySQL.php");

require_once ('class/PHPExcel/IOFactory.php');
require_once('class/PHPExcel.php');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
$db	=new MySQL();
$objPHPExcel = new PHPExcel();


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

// Set document properties
$objPHPExcel->getProperties()->setCreator("Aadhocc Technologies")
							 ->setLastModifiedBy("Aadhocc Technologies")
							 ->setTitle("Professional Tax - Form 5A")
							 ->setSubject("Professional Tax - Form 5A")
							 ->setDescription("Professional Tax - Form 5A")
							 ->setKeywords("Professional Tax - Form 5A")
							 ->setCategory("Professional Tax - Form 5A");
///// Create 
$objPHPExcel->setActiveSheetIndex(0);

$companyDetails= $db->fetchQuery("SELECT * FROM addhrm_company_master ");

$objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "".$companyDetails[0]['company_name']."\n"."".$companyDetails[0]['company_address']."\n"."".$companyDetails[0]['company_phone']."\n"."".$companyDetails[0]['company_mobile']."\n"."".$companyDetails[0]['company_email']."");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(100);

// SET HEADER

$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
$objPHPExcel->getActiveSheet()->getStyle("A3:E3")->getFont()->setSize(16);		
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,));
$objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getStyle('A4:E4')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
	
		
$objPHPExcel->getActiveSheet()->mergeCells('A3:E3');
$objPHPExcel->getActiveSheet()->setCellValue('A3', "Professional Tax - Form 5A");	
$objPHPExcel->getActiveSheet()->setCellValue('A4', "SI NO");
$objPHPExcel->getActiveSheet()->setCellValue('B4', "Employees whose monthly salaries or
wages or both ");
$objPHPExcel->getActiveSheet()->setCellValue('C4', "No. of Employees");
$objPHPExcel->getActiveSheet()->setCellValue('D4', "Rate of Tax");
$objPHPExcel->getActiveSheet()->setCellValue('E4', "Amount of Tax Deducted");

	$k=5;
	
	//////////////////////////////user rights checking//////////////////////////////////////



//////////////////////////////user rights checking//////////////////////////////////////

		
		$sqlstring="SELECT * from professional_tax";
		
	$sum_cnt=0;
	$sum_tax=0;
		$EmpPayStructure_array = $db->fetchQuery($sqlstring);
		$i=1;
	foreach($EmpPayStructure_array as $EmpPayStructure_row){
			
			$payroll_sql="select count(distinct `userid`) as cnt from payroll_salary_pay WHERE total_earnings >= ($EmpPayStructure_row[min_amount]/6) ";
			if($EmpPayStructure_row['max_amount']>0){
				$payroll_sql="and total_earnings <= ($EmpPayStructure_row[max_amount]/6)";
			}			
			
			$payroll = $db->fetchQuery($payroll_sql);
			
			foreach ($payroll as $payroll_dtl) {
			$cnt=$payroll_dtl['cnt'];
			$total_tax=$payroll_dtl['cnt']*$EmpPayStructure_row['tax'];
			}
			if($EmpPayStructure_row['max_amount']>0){
				$max= "To $EmpPayStructure_row[max_amount]";
			}else{
				$max= "and Above ";
			}
			
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, $i);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, $EmpPayStructure_row['min_amount'].$max);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, $cnt);
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$k, $EmpPayStructure_row['tax']);
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, $total_tax);	
				$sum_cnt+=$cnt;  
				$sum_tax+=$total_tax;  
				$k++ ;
				$i++ ;
		}

	//$objPHPExcel->getActiveSheet()->mergeCells('A'.$k.':B'.$k);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, "");
$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, "Total");
$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, $sum_cnt);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$k, "");
$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, $sum_tax);
	

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Professional Tax - Form 5A.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

	
}



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

// Set document properties
$objPHPExcel->getProperties()->setCreator("Aadhocc Technologies")
							 ->setLastModifiedBy("Aadhocc Technologies")
							 ->setTitle("PF - Form 5 - Consolidated statement of members joining PF")
							 ->setSubject("PF - Form 5 - Consolidated statement of members joining PF")
							 ->setDescription("PF - Form 5 - Consolidated statement of members joining PF")
							 ->setKeywords("PF - Form 5 - Consolidated statement of members joining PF")
							 ->setCategory("PF - Form 5 - Consolidated statement of members joining PF");
///// Create 
$objPHPExcel->setActiveSheetIndex(0);

$companyDetails= $db->fetchQuery("SELECT * FROM addhrm_company_master ");

$objPHPExcel->getActiveSheet()->mergeCells('A1:L1');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "".$companyDetails[0]['company_name']."\n"."".$companyDetails[0]['company_address']."\n"."".$companyDetails[0]['company_phone']."\n"."".$companyDetails[0]['company_mobile']."\n"."".$companyDetails[0]['company_email']."");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(100);

// SET HEADER

$objPHPExcel->getActiveSheet()->getStyle('A3:L3')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
$objPHPExcel->getActiveSheet()->getStyle("A3:L3")->getFont()->setSize(16);		
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,));
$objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getStyle('A4:L4')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
	
		
$objPHPExcel->getActiveSheet()->mergeCells('A3:L3');
$objPHPExcel->getActiveSheet()->setCellValue('A3', "PF - Form 5 - Consolidated statement of members joining PF");	
$objPHPExcel->getActiveSheet()->setCellValue('A4', "Account.No.");
$objPHPExcel->getActiveSheet()->setCellValue('B4', "Name of Employee");
$objPHPExcel->getActiveSheet()->setCellValue('C4', "Father\'s Name (or Husband\'s Name in case of married woman)");
$objPHPExcel->getActiveSheet()->setCellValue('D4', "Date of Birth");
$objPHPExcel->getActiveSheet()->setCellValue('E4', "Sex");
$objPHPExcel->getActiveSheet()->setCellValue('F4', "Date of joining the Fund");
$objPHPExcel->getActiveSheet()->setCellValue('G4', "Total period of previous service as on the date of joining the Fund (Enclose scheme certificate if applicable)");
$objPHPExcel->getActiveSheet()->setCellValue('H4', "Remarks");

	$k=5;
	
	//////////////////////////////user rights checking//////////////////////////////////////



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

if(isset($_REQUEST['to_date'])){
$todate=$_REQUEST['to_date'];
}
//echo $sqlstring="SELECT distinct a.*,CONCAT(e_fname,e_lname)as emp_name FROM employee a inner join payroll_salary_pay s on s.userid=a.e_id  where 1 ";
$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($fromdate!="" && $todate!=""){
		$sqlstring.=" and pf_date_join_fund between '$fromdate' and '$todate'";
		}
		
		/*if(isset($_GET["from_date"]) && !empty($_GET['from_date'])){
			$sqlstring.=" and pf_date_join_fund >= '$_GET[from_date]' ";
		}
		if(isset($_GET["to_date"]) && !empty($_GET['to_date'])){
			$sqlstring.=" and '$_GET[to_date]' >= date_join ";
		}*/
		/* if(isset($_GET["emp_id"]) && !empty($_GET['emp_id'])){
			$sqlstring.=" and e_id = '$_GET[emp_id]' ";
		}
		 if(isset($_GET["emp_name"]) && !empty($_GET['emp_name'])){
			$sqlstring.=" and e_fname = '$_GET[emp_name]' ";
		}
		 if(isset($_GET["pf_no"]) && !empty($_GET['pf_no'])){
			$sqlstring.=" and pf_no = '$_GET[pf_no]' ";
		}*/
		if(isset($_GET["type_sort"]) && !empty($_GET["type_sort"])){
			$sqlstring.=" order by $_GET[type_sort] ASC";	
		}
		
		//echo $sqlstring;
		
		$EmpPayStructure_array = $db->fetchQuery($sqlstring);
		$i=0;
	foreach($EmpPayStructure_array as $EmpPayStructure_row)
	{
			//$EmpPayStructure_row['pf_service_year']
			//$EmpPayStructure_row['pf_service_month']
			//$EmpPayStructure_row['pf_service_day']
			$total_period_previous_service = $EmpPayStructure_row['pf_service_year'].' Years '.$EmpPayStructure_row['pf_service_month'].' Months '.$EmpPayStructure_row['pf_service_day'].' Days';
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, $EmpPayStructure_row['pf_accnum']);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, $EmpPayStructure_row['emp_name']);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, $EmpPayStructure_row['e_mname']);
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$k, date('d M Y', strtotime($EmpPayStructure_row['e_dob'])));
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, $EmpPayStructure_row['e_gender']);		
		$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, $EmpPayStructure_row['pf_date_join_fund']);		
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, $total_period_previous_service);		
		/*$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");	*/	
		$k++;
	}
	
/*$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);*/
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="PF-Form 5-Consolidated statement of members joining PF.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

	
}


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

// Set document properties
$objPHPExcel->getProperties()->setCreator("Aadhocc Technologies")
							 ->setLastModifiedBy("Aadhocc Technologies")
							 ->setTitle("PF - Form 10 - Consolidated statement of members Leaving PF")
							 ->setSubject("PF - Form 10 - Consolidated statement of members Leaving PF")
							 ->setDescription("PF - Form 10 - Consolidated statement of members Leaving PF")
							 ->setKeywords("PF - Form 10 - Consolidated statement of members Leaving PF")
							 ->setCategory("PF - Form 10 - Consolidated statement of members Leaving PF");
///// Create 
$objPHPExcel->setActiveSheetIndex(0);

$companyDetails= $db->fetchQuery("SELECT * FROM addhrm_company_master ");

$objPHPExcel->getActiveSheet()->mergeCells('A1:L1');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "".$companyDetails[0]['company_name']."\n"."".$companyDetails[0]['company_address']."\n"."".$companyDetails[0]['company_phone']."\n"."".$companyDetails[0]['company_mobile']."\n"."".$companyDetails[0]['company_email']."");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(100);

// SET HEADER

$objPHPExcel->getActiveSheet()->getStyle('A3:L3')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
$objPHPExcel->getActiveSheet()->getStyle("A3:L3")->getFont()->setSize(16);		
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,));
$objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getStyle('A4:L4')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
	
		
$objPHPExcel->getActiveSheet()->mergeCells('A3:L3');
$objPHPExcel->getActiveSheet()->setCellValue('A3', "PF - Form 10 - Consolidated statement of members Leaving PF");	
$objPHPExcel->getActiveSheet()->setCellValue('A4', "Account.No.");
$objPHPExcel->getActiveSheet()->setCellValue('B4', "Name of the Member");
$objPHPExcel->getActiveSheet()->setCellValue('C4', "Father\'s Name (or Husband\'s Name in case of married woman)");
$objPHPExcel->getActiveSheet()->setCellValue('D4', "Date of Leaving Service");
$objPHPExcel->getActiveSheet()->setCellValue('E4', "Reason for Leaving Service");
$objPHPExcel->getActiveSheet()->setCellValue('F4', "Remarks");

	$k=5;
	
	//////////////////////////////user rights checking//////////////////////////////////////



//////////////////////////////user rights checking//////////////////////////////////////

$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 ";
 	//$sqlstring="SELECT e.*,g.resigned_dat,CONCAT(e_fname,e_lname)as emp_name FROM employee e inner join gratuity_table g on e.e_id=g.e_id where emp_basic_pay!=0 ";
		/* 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($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";	
		}
		
		$EmpPayStructure_array = $db->fetchQuery($sqlstring);
		$i=0;
	foreach($EmpPayStructure_array as $EmpPayStructure_row)
	{
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, $EmpPayStructure_row['pf_accnum']);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, $EmpPayStructure_row['emp_name']);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, $EmpPayStructure_row['e_mname']);
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$k, date('d M Y', strtotime($EmpPayStructure_row['pf_leaving_date'])));
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, $EmpPayStructure_row['reason_leaving_pf']);		
		$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, $EmpPayStructure_row['pfremarks']);		
		$k++;
	}
	
/*$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);*/
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="PF-Form 10-Consolidated statement of members Leaving PF.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

	
}


if($_GET['status']=='mst')
{
// Set document properties
$objPHPExcel->getProperties()->setCreator("Aadhocc Technologies")
							 ->setLastModifiedBy("Aadhocc Technologies")
							 ->setTitle("PF - Monthly Statement")
							 ->setSubject("PF - Monthly Statement")
							 ->setDescription("PF - Monthly Statement")
							 ->setKeywords("PF - Monthly Statement")
							 ->setCategory("PF - Monthly Statement");
///// Create 
$objPHPExcel->setActiveSheetIndex(0);

$companyDetails= $db->fetchQuery("SELECT * FROM addhrm_company_master ");

$objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "".$companyDetails[0]['company_name']."\n"."".$companyDetails[0]['company_address']."\n"."".$companyDetails[0]['company_phone']."\n"."".$companyDetails[0]['company_mobile']."\n"."".$companyDetails[0]['company_email']."");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(100);

// SET HEADER

$objPHPExcel->getActiveSheet()->getStyle('A3:I3')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
$objPHPExcel->getActiveSheet()->getStyle("A3:I3")->getFont()->setSize(16);		
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,));
$objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
	
		
$objPHPExcel->getActiveSheet()->mergeCells('A3:I3');
$objPHPExcel->getActiveSheet()->setCellValue('A3', "PF - Monthly Statement");	
$objPHPExcel->getActiveSheet()->setCellValue('A4', "Employee.No.");
$objPHPExcel->getActiveSheet()->setCellValue('B4', "Name");
$objPHPExcel->getActiveSheet()->setCellValue('C4', "PF No");
$objPHPExcel->getActiveSheet()->mergeCells("D4:F4");
$objPHPExcel->getActiveSheet()->setCellValue('D4', "Employees Contribution");
$objPHPExcel->getActiveSheet()->mergeCells("G4:I4");
$objPHPExcel->getActiveSheet()->setCellValue('G4', "Employers Contribution");

$objPHPExcel->getActiveSheet()->setCellValue('A5', "");
$objPHPExcel->getActiveSheet()->setCellValue('B5', "");
$objPHPExcel->getActiveSheet()->setCellValue('C5', "");
$objPHPExcel->getActiveSheet()->setCellValue('D5', "Basic+DA");
$objPHPExcel->getActiveSheet()->setCellValue('E5', "PF");
$objPHPExcel->getActiveSheet()->setCellValue('F5', "VPF");
$objPHPExcel->getActiveSheet()->setCellValue('G5', "PF");
$objPHPExcel->getActiveSheet()->setCellValue('H5', "EPS");
$objPHPExcel->getActiveSheet()->setCellValue('I5', "Total");

	$k=6;
	
	//////////////////////////////user rights checking//////////////////////////////////////



//////////////////////////////user rights checking//////////////////////////////////////

	
$sqlstring="SELECT *,CONCAT(e_fname,e_lname)as emp_name,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["type_sort"]) && !empty($_REQUEST["type_sort"])){
			$sqlstring.=" order by $_REQUEST[type_sort] ASC";	
		}*/	
		
		$EmpPayStructure_array = $db->fetchQuery($sqlstring);
		$i=0;
		$sum_basic_da=0;
		$sum_pf=0;
	foreach($EmpPayStructure_array as $EmpPayStructure_row)
	{
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, $EmpPayStructure_row['e_id']);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, $EmpPayStructure_row['emp_name']);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, $EmpPayStructure_row['pf_no']);
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$k, "");
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, $EmpPayStructure_row['dtl_pf']);
		$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");		
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");		
		$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");		
		$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");		
		$k++;
		
		$sum_basic_da+= $details['dtl_basic']+$details['dtl_allowance'];
		$sum_pf+= $details['dtl_pf'];			
				
	}
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, "Total");
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$k,	$sum_basic_da);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, $sum_pf);
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");
	$k++;
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, "Gross for EPS Calculation");
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$k,	"");
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, $sum_pf);
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");
	$k++;
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, "Account No 1 EPF");
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$k,	"");
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");
	$k++;
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, "Account No 10 EPS");
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$k,	"");
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");
	$k++;
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, "Account No 2PF Admin 1.1%");
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$k,	"");
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");
	$k++;
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, "Account No. 21 E D L I 0.5%");
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$k,	"");
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, "Account No. 22 EDLI Admin 0.005%");
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$k,	"");
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");
	$k++;
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, "Total");
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$k,	"");
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, $sum_pf);
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="PF - Monthly Statement"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

	
}

if($_GET['status']=='3a')
{
// Set document properties
$objPHPExcel->getProperties()->setCreator("Aadhocc Technologies")
							 ->setLastModifiedBy("Aadhocc Technologies")
							 ->setTitle("PF - Form 3A")
							 ->setSubject("PF - Form 3A")
							 ->setDescription("PF - Form 3A")
							 ->setKeywords("PF - Form 3A")
							 ->setCategory("PF - Form 3A");
///// Create 
$objPHPExcel->setActiveSheetIndex(0);

$companyDetails= $db->fetchQuery("SELECT * FROM addhrm_company_master ");

$objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "".$companyDetails[0]['company_name']."\n"."".$companyDetails[0]['company_address']."\n"."".$companyDetails[0]['company_phone']."\n"."".$companyDetails[0]['company_mobile']."\n"."".$companyDetails[0]['company_email']."");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(100);

// SET HEADER

$objPHPExcel->getActiveSheet()->getStyle('A3:I3')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
$objPHPExcel->getActiveSheet()->getStyle("A3:I3")->getFont()->setSize(16);		
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,));
$objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getStyle('A4:I4')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
	$objPHPExcel->getActiveSheet()->getStyle('A5:I5')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
	
		
$objPHPExcel->getActiveSheet()->mergeCells('A3:H3');
$objPHPExcel->getActiveSheet()->setCellValue('A3', "PF - Form 3A");	
$objPHPExcel->getActiveSheet()->mergeCells("A4:A5");
$objPHPExcel->getActiveSheet()->setCellValue('A4', "Month");
$objPHPExcel->getActiveSheet()->mergeCells("B4:B5");
$objPHPExcel->getActiveSheet()->setCellValue('B4', "Name");
$objPHPExcel->getActiveSheet()->mergeCells("C4:D4");
$objPHPExcel->getActiveSheet()->setCellValue('C4', "MEMBER\'S SHARE");
$objPHPExcel->getActiveSheet()->mergeCells("E4:F4");
$objPHPExcel->getActiveSheet()->setCellValue('E4', "EMPLOYER\'S SHARE");
$objPHPExcel->getActiveSheet()->mergeCells("G4:G5");
$objPHPExcel->getActiveSheet()->setCellValue('G4', "Ref. of Advance");
$objPHPExcel->getActiveSheet()->mergeCells("H4:H5");
$objPHPExcel->getActiveSheet()->setCellValue('H4', "Period of non contributory service (if any) No of days");
$objPHPExcel->getActiveSheet()->mergeCells("I4:I5");
$objPHPExcel->getActiveSheet()->setCellValue('I4', "Remarks");

$objPHPExcel->getActiveSheet()->setCellValue('A5', "");
$objPHPExcel->getActiveSheet()->setCellValue('B5', "");
$objPHPExcel->getActiveSheet()->setCellValue('C5', "Amount of Wages");
$objPHPExcel->getActiveSheet()->setCellValue('D5', "E.P.F");
$objPHPExcel->getActiveSheet()->setCellValue('E5', "E.P.F Contribution");
$objPHPExcel->getActiveSheet()->setCellValue('F5', "Pension Fund Contribution 8 1/3 %");
$objPHPExcel->getActiveSheet()->setCellValue('G5', "");
$objPHPExcel->getActiveSheet()->setCellValue('H5', "");
$objPHPExcel->getActiveSheet()->setCellValue('I5', "");

	$k=6;
	
	//////////////////////////////user rights checking//////////////////////////////////////



//////////////////////////////user rights checking//////////////////////////////////////

	  $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 1 ";
		  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]' ";
		}
		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 MONTH(`payment_date`) ASC";	
		 if(isset($_REQUEST["type_sort"]) && !empty($_REQUEST["type_sort"])){
			$sqlstring.=" ,$_REQUEST[type_sort] ASC";	
		}
	
		$EmpPayStructure_array = $db->fetchQuery($sqlstring);
		$i=0;
		$sum_basic_da=0;
		$sum_pf=0;
	foreach($EmpPayStructure_array as $EmpPayStructure_row)
	{
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, $EmpPayStructure_row['month_name']);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, $EmpPayStructure_row['emp_name']);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, round($EmpPayStructure_row['total_paid'],2));
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$k, round($EmpPayStructure_row['pf'],2));
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, "");
		$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");		
		$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");		
		$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");		
		$k++;					
	}
	
	

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="PF - Form 3A"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

	
}


if($_GET['status']=='6a')
{
// Set document properties
$objPHPExcel->getProperties()->setCreator("Aadhocc Technologies")
							 ->setLastModifiedBy("Aadhocc Technologies")
							 ->setTitle("PF - Form  6A - Consolidated Statement For Contribution Card")
							 ->setSubject("PF - Form  6A - Consolidated Statement For Contribution Card")
							 ->setDescription("PF - Form  6A - Consolidated Statement For Contribution Card")
							 ->setKeywords("PF - Form  6A - Consolidated Statement For Contribution Card")
							 ->setCategory("PF - Form  6A - Consolidated Statement For Contribution Card");
///// Create 
$objPHPExcel->setActiveSheetIndex(0);

$companyDetails= $db->fetchQuery("SELECT * FROM addhrm_company_master ");

$objPHPExcel->getActiveSheet()->mergeCells('A1:L1');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "".$companyDetails[0]['company_name']."\n"."".$companyDetails[0]['company_address']."\n"."".$companyDetails[0]['company_phone']."\n"."".$companyDetails[0]['company_mobile']."\n"."".$companyDetails[0]['company_email']."");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(100);

// SET HEADER

$objPHPExcel->getActiveSheet()->getStyle('A3:L3')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
$objPHPExcel->getActiveSheet()->getStyle("A3:L3")->getFont()->setSize(16);		
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,));
$objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getStyle('A4:L4')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
	$objPHPExcel->getActiveSheet()->getStyle('A5:L5')->applyFromArray(
    		array(
        		'fill' => array(
            		'type' => PHPExcel_Style_Fill::FILL_SOLID,
            		'color' => array('rgb' => '808080')
        		),
				'borders' => array(
    				'allborders' => array(
      					'style' => PHPExcel_Style_Border::BORDER_THIN
    				)
  				),
				'font'  => array(
        			'bold'  => true,
        			'color' => array('rgb' => 'FFFFFF')/*,
        			'size'  => 15,
        			'name'  => 'Verdana'*/
    			)
    		)
		);
	
		
$objPHPExcel->getActiveSheet()->mergeCells('A3:H3');
$objPHPExcel->getActiveSheet()->setCellValue('A3', "PF - Form 6A - Consolidated Statement For Contribution Card");	
$objPHPExcel->getActiveSheet()->mergeCells("A4:A5");
$objPHPExcel->getActiveSheet()->setCellValue('A4', "Account.No.");
$objPHPExcel->getActiveSheet()->mergeCells("B4:B5");
$objPHPExcel->getActiveSheet()->setCellValue('B4', "Name of the Member");
$objPHPExcel->getActiveSheet()->mergeCells("C4:C5");
$objPHPExcel->getActiveSheet()->setCellValue('C4', "Wages retaining");
$objPHPExcel->getActiveSheet()->mergeCells("D4:F4");
$objPHPExcel->getActiveSheet()->setCellValue('D4', "Amount of Worker\'s Contributions deducted from the Wages");
$objPHPExcel->getActiveSheet()->mergeCells("G4:I4");
$objPHPExcel->getActiveSheet()->setCellValue('G4', "Employer\'s Contributions");
$objPHPExcel->getActiveSheet()->mergeCells("J4:J5");
$objPHPExcel->getActiveSheet()->setCellValue('J4', "Refund of Advance");
$objPHPExcel->getActiveSheet()->mergeCells("K4:K5");
$objPHPExcel->getActiveSheet()->setCellValue('K4', "Rate of Higher Voluntary Contribution if any");
$objPHPExcel->getActiveSheet()->mergeCells("L4:L5");
$objPHPExcel->getActiveSheet()->setCellValue('L4', "Remarks");

$objPHPExcel->getActiveSheet()->setCellValue('A5', "");
$objPHPExcel->getActiveSheet()->setCellValue('B5', "");
$objPHPExcel->getActiveSheet()->setCellValue('C5', "");
$objPHPExcel->getActiveSheet()->setCellValue('D5', "EPF");
$objPHPExcel->getActiveSheet()->setCellValue('E5', "EPS");
$objPHPExcel->getActiveSheet()->setCellValue('F5', "Total");
$objPHPExcel->getActiveSheet()->setCellValue('G5', "EPF");
$objPHPExcel->getActiveSheet()->setCellValue('H5', "EPS");
$objPHPExcel->getActiveSheet()->setCellValue('I5', "Total");
$objPHPExcel->getActiveSheet()->setCellValue('J5', "");
$objPHPExcel->getActiveSheet()->setCellValue('K5', "");
$objPHPExcel->getActiveSheet()->setCellValue('L5', "");

	$k=6;
	
	//////////////////////////////user rights checking//////////////////////////////////////



//////////////////////////////user rights checking//////////////////////////////////////

	 $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 1 ";
		 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 MONTH(`payment_date`) ASC";
		 if(isset($_REQUEST["type_sort"]) && !empty($_REQUEST["type_sort"])){
			$sqlstring.=" ,'$_REQUEST[type_sort]' ASC";	
		}
	
		$EmpPayStructure_array = $db->fetchQuery($sqlstring);
		$i=0;
		$sum_basic_da=0;
		$sum_pf=0;
	foreach($EmpPayStructure_array as $EmpPayStructure_row)
	{
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, $EmpPayStructure_row['e_accountno']);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, $EmpPayStructure_row['emp_name']);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, round($EmpPayStructure_row['total_paid'],2));
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$k, round($EmpPayStructure_row['pf'],2));
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, "");
		$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, "");
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, "");		
		$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, "");		
		$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, "");		
		$k++;					
	}
	
	

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="PF - Form  6A - Consolidated Statement For Contribution Card"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

	
}

?>