<?php



include 'connect.php';



require_once 'PHPExcel.php';



require_once 'PHPExcel/IOFactory.php';







// Create new PHPExcel object



$objPHPExcel = new PHPExcel();







// Create a first sheet, representing sales data



//$objPHPExcel->setActiveSheetIndex(0);



$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Something');







// Rename sheet



//$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');







// Create a new worksheet, after the default sheet



$objPHPExcel->createSheet();







// Add some data to the second sheet, resembling some different data types



$objPHPExcel->setActiveSheetIndex(1);







$styleArray = array(



'font'  => array(



    'bold'  => true,



    'color' => array('rgb' => 'FF0000'),



));







$stylearr = array(



'font'  => array(



    'bold'  => true,



    'color' => array('rgb' => 'BD3A00'),



));







$stylear = array(



'font'  => array(



    'bold'  => true,



	'color' => array('rgb' => '00A404'),



));







$style = array(



'font'  => array(



    'bold'  => true,	



));





$month=$_GET['month'];

$year=$_GET['year'];





$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);



$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(30);



$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(30);





$objPHPExcel->getActiveSheet()->setCellValue('G1', 'Salary Sheet');



$objPHPExcel->getActiveSheet()->getStyle("G1")->applyFromArray($styleArray);



$objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,));







$objPHPExcel->getActiveSheet()->setCellValue('A3', 'S.No.')->getStyle("A3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Emp.ID')->getStyle("B3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Emp.Name')->getStyle("C3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Father / Husband Name')->getStyle("D3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Design / Dept')->getStyle("E3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('F3', 'DOJ')->getStyle("F3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('G3', 'No.Of Payable Days')->getStyle("G3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('H3', 'OT Days')->getStyle("H3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('I3', 'Salary')->getStyle("I3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('J3', 'Ex - Gratia')->getStyle("J3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('K3', 'Special Allowance')->getStyle("K3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('L3', 'KPI Ince')->getStyle("L3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('M3', 'Advance / Loan')->getStyle("M3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('N3', 'Fine')->getStyle("N3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('O3', 'Room Rent')->getStyle("O3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('P3', 'Total Deduction')->getStyle("P3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('Q3', 'Grand Total')->getStyle("Q3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('R3', 'Net Payable')->getStyle("R3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('S3', 'Mode of payment')->getStyle("S3")->applyFromArray($style);



$objPHPExcel->getActiveSheet()->setCellValue('T3', 'Signature')->getStyle("T3")->applyFromArray($style);





function returnDates($fromdate, $todate) {

    $fromdate = DateTime::createFromFormat('d-m-Y', $fromdate);

    $todate = DateTime::createFromFormat('d-m-Y', $todate);

    return new DatePeriod(

        $fromdate,

        new DateInterval('P1D'),

        $todate->modify('+1 day')

    );

}



function returnDays($fromdate, $todate) {

				$fromdate = DateTime::createFromFormat('d-m-Y', $fromdate);

				$todate = DateTime::createFromFormat('d-m-Y', $todate);

				return new DatePeriod(

					$fromdate,

					new DateInterval('P1D'),

					$todate->modify('+1 day')

				);

			}





$u=5;



$sno=0;



$em=mysqli_query($conn,"select * from employee order by ID asc");

while($em1=mysqli_fetch_assoc($em))

{



$date1 = $year.'-'.$month.'-01';

$dte=date("t",strtotime($date1));

$date2 = $year.'-'.$month.'-'.$dte;







$date1 = date('d-m-Y',strtotime($date1));

$date2 = date('d-m-Y',strtotime($date2));



$datePeriod = returnDates($date1, $date2);



$tote=0;

foreach($datePeriod as $date) {



    $tdate = $date->format('d-m-Y');

	

	$cdate = $date->format('Y-m-d');



	$kp=mysqli_query($conn,"select * from kpi where date='$cdate' and employee='$em1[ID]'");

	$kp1=mysqli_fetch_assoc($kp);

	

	$sum=$kp1['attendance']+$kp1['socialmedia']+$kp1['crmupdate']+$kp1['businessconversation']+$kp1['deliveryfeedback']+$kp1['productfeedback']+$kp1['nocalls']+$kp1['paymentcollection']+$kp1['tds']+$td1['customerretention'];

	

	if($sum>0)

	{

	$kpii=1400/30/$sum*$sum;

	}

	

	$kpii=number_format($kpii,0,'.','');

	

	$tote=$tote+$kpii;



}





		//



			$nm=0;

			

			

			$date1 = date('d-m-Y',strtotime($date1));

			$date2 = date('d-m-Y',strtotime($date2));

			

			$datePeriod = returnDays($date1, $date2);

			foreach($datePeriod as $dte) {

				$tdate = $dte->format('d-m-Y');

				

				$nm++;		

				

			}

			

			$lastday=$nm;

			

			$date1 = date('Y-m-d',strtotime($date1));

			$date2 = date('Y-m-d',strtotime($date2));



			$no = 0;

			$start = new DateTime($date1);

			$end   = new DateTime($date2);

			$end   = $end->modify('+1 day');

			

			$interval = DateInterval::createFromDateString('1 day');

			$period = new DatePeriod($start, $interval, $end);

			foreach ($period as $dt)

			{

				if ($dt->format('N') == 7)

				{

					$no++;

				}

			}

			$sundays = $no;



	//



	$date1=date('Y-m-d',strtotime($date1));

	$date2=date('Y-m-d',strtotime($date2));



$ph=mysqli_query($conn,"select * from holiday where date between '$date1' and '$date2'");

$ph1=mysqli_num_rows($ph);

	

	$balancedays=$lastday-$sundays-$ph1;



$sm=date('Y-m-d',strtotime($date1));



$sal=mysqli_query($conn,"select * from salary where empid='$em1[empid]' and incrementfrom<='$sm' order by ID desc limit 1");

$sal1=mysqli_fetch_assoc($sal);



$desig=mysqli_query($conn,"select * from employee_designation where ID='$em1[designation]'");

$desig1=mysqli_fetch_assoc($desig);



$spd=$sal1['salary']/$balancedays;



$otat=mysqli_query($conn,"select * from ot_attendance where empid='$em1[empid]' and month(date)='$month' and year(date)='$year'");

$otat1=mysqli_num_rows($otat);



$otvl=$otat1*$spd;



$grand=$sal1['salary']+$tote+$otvl;



$grand=number_format($grand,0,'.','').'.00';



$a='A'.$u;



$b='B'.$u;



$c='C'.$u;



$d='D'.$u;



$e='E'.$u;



$f='F'.$u;



$g='G'.$u;



$h='H'.$u;



$i='I'.$u;



$j='J'.$u;



$k='K'.$u;



$l='L'.$u;



$m='M'.$u;



$n='N'.$u;



$o='O'.$u;



$p='P'.$u;



$q='Q'.$u;



$r='R'.$u;



$s='S'.$u;



$t='T'.$u;





$sno=$sno+1;





$doj=date('d-m-Y',strtotime($em1['dateofjoining']));





$objPHPExcel->getActiveSheet()->setCellValue($a, $sno);



$objPHPExcel->getActiveSheet()->setCellValue($b, $em1['empid']);



$objPHPExcel->getActiveSheet()->setCellValue($c, $em1['name']);



$objPHPExcel->getActiveSheet()->setCellValue($d, $em1['fathername']);



$objPHPExcel->getActiveSheet()->setCellValue($e, $desig1['designation']);



$objPHPExcel->getActiveSheet()->setCellValue($f, $doj);



$objPHPExcel->getActiveSheet()->setCellValue($g, $balancedays);



$objPHPExcel->getActiveSheet()->setCellValue($h, $otat1);



$objPHPExcel->getActiveSheet()->setCellValue($i, $sal1['salary']);



$objPHPExcel->getActiveSheet()->setCellValue($j, '');



$objPHPExcel->getActiveSheet()->setCellValue($k, '');



$objPHPExcel->getActiveSheet()->setCellValue($l, $tote);



$objPHPExcel->getActiveSheet()->setCellValue($m, '');



$objPHPExcel->getActiveSheet()->setCellValue($n, '');



$objPHPExcel->getActiveSheet()->setCellValue($o, '');



$objPHPExcel->getActiveSheet()->setCellValue($p, '');



$objPHPExcel->getActiveSheet()->setCellValue($q, $grand);



$objPHPExcel->getActiveSheet()->setCellValue($r, $grand);



$objPHPExcel->getActiveSheet()->setCellValue($s, 'Bank, A/C No. '.$em1['accountno']);



$objPHPExcel->getActiveSheet()->setCellValue($t, '');







$u=$u+1;



}





// Rename 2nd sheet



$objPHPExcel->getActiveSheet()->setTitle('Salary_Sheet');







// Redirect output to a client’s web browser (Excel5)



header('Content-Type: application/vnd.ms-excel');



header('Content-Disposition: attachment;filename="salarysheet.xls"');



header('Cache-Control: max-age=0');



$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');



$objWriter->save('php://output');

















?>