<?php
session_start();
include("../../../../config/database.php");
$conct  = new connectionCreat();
$conct->connectToDB();
$conct->selectDB();
$setExcelName	   = "excel_dwn".time(); 
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
///Basic 
/// days calculate in a month
function getSundays($y,$m)
{ 
	$date = "$y-$m-01";
	$first_day = date('N',strtotime($date));
	$first_day = 7 - $first_day + 1;
	$last_day =  date('t',strtotime($date));
	$days = array();
	for($i=$first_day; $i<=$last_day; $i=$i+7 )
	{
		$days[] = $i;
	}
	return  $days; 
}
$month		= $_SESSION['att_month'];
$year		= $_SESSION['att_year'];
$d		 	= $_GET['d'];
$sun_arr   = getSundays($year,$month);
$sunday_dts_arr= array();
foreach($sun_arr as $key)
{
array_push($sunday_dts_arr,$year."-".$month."-".$key);	 
}
$sunday_dts = implode("','",$sunday_dts_arr);
$sundays	= $sundays_1    = count($sun_arr);
$from      	= date("Y-m-d",strtotime("01-$month-$year"));
$to	    	= date("Y-m-d",strtotime("$d-$month-$year"));
$emp_name	= $_SESSION['att_emp_name'] ;
$state		= $_SESSION['att_state'];
$monthName  = date('F', mktime(0, 0, 0,$month, 10));

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
for($col = 'A'; $col !== 'B'; $col++) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
	}
		
$styleArray1 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => 'ff1a1a'),
        'size'  => 16,
		'name'  => 'Cambria'
    ));
$styleArray2 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '009900'),
        'size'  => 11,
		'name'  => 'Cambria'
    ));	
$styleArray3 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '0000'),
        'size'  => 11,
		'name'  => 'Cambria',
    ),
	'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
	);	
$styleArray4 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '000'),
        'size'  => 11,
		'name'  => 'Cambria'
    ));	
$styleArray5 = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '3333cc'),
        'size'  => 12,
		'name'  => 'Cambria'
    ));
// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
			 ->setLastModifiedBy("Maarten Balliauw")
			 ->setTitle("Office 2007 XLSX Test Document")
			 ->setSubject("Office 2007 XLSX Test Document")
			 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
			 ->setKeywords("office 2007 openxml php")
			 ->setCategory("Test result file");
//HEADIGN
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'S NO.')
            ->setCellValue('B1', 'Emp Code')
			->setCellValue('C1', 'Employee Name')
			->setCellValue('D1', 'Designation')
			->setCellValue('E1', 'Father Name')
			->setCellValue('F1', 'State')
			->setCellValue('G1', 'Bank Name')
			->setCellValue('H1', 'Branch IFSC Code')
			->setCellValue('I1', 'Account No')
			->setCellValue('J1', 'Days in Month')
			->setCellValue('K1', 'Sundays')
            ->setCellValue('L1', 'Holidays')
			->setCellValue('M1', 'Working Days Available')
			->setCellValue('N1', 'Leave')
			->setCellValue('O1', 'Balance Working Days')
			->setCellValue('P1', 'Meeting / Training / Conf')
			->setCellValue('Q1', 'Sundays Worked')
			->setCellValue('R1', 'Holidays Worked')			
			->setCellValue('S1', 'Days Retailed')
			->setCellValue('T1', 'Basic Salary')
			->setCellValue('U1', 'DA')
			->setCellValue('V1', 'TA')
			->setCellValue('W1', 'Phone')
			->setCellValue('X1', 'Travel Exp.')
			->setCellValue('Y1', 'Salary')
			->setCellValue('Z1', 'DA')
			->setCellValue('AA1', 'TA')
			->setCellValue('AB1', 'Phone')			
			->setCellValue('AC1', 'Travel Exp.')
			->setCellValue('AD1', $monthName." ".$year." Salary ")
			->setCellValue('AE1', 'Payment Status')
			->setCellValue('AF1', 'UAN Status');									
$objPHPExcel->getActiveSheet()->getStyle('A1:AF1')->getAlignment()->setWrapText(true); 								
$objPHPExcel->getActiveSheet()->getStyle('A1:AF1')->applyFromArray($styleArray3);
$i	= 2;	
if($emp_name=='ALL') 
            {
              $sql			= mysql_query(" SELECT distinct `id`,`emp_name`,`fname`,`salary_rate`,`da_rate`,`phone_rate`,`ta_rate`,`travel_exp`,`state`,`account_no`,`branch_ifsc`,`bank_name`,`emp_code`,`payment_status`,`uan_status`,`desiganation` FROM `tbl_emp_records` WHERE `status`='0' and `state`='$state' order by `emp_name` ASC")or die(mysql_error());
            }
            else
            {
              $sql			= mysql_query(" SELECT distinct `id`,`emp_name`,`fname`,`salary_rate`,`da_rate`,`phone_rate`,`ta_rate` ,`travel_exp`,`state`,`account_no`,`branch_ifsc`,`bank_name`,`emp_code`,`payment_status`,`uan_status`,`desiganation` FROM `tbl_emp_records` WHERE `status`='0' and `state`='$state' and `id`='$emp_name' ")or die(mysql_error());
            }
            while($row			= mysql_fetch_array($sql))
            {
				$off_in_month   	= 0;$lea_in_month = 0;$wor_in_month = 0;$per_in_month	= 0;$b_wr_in_month = 0;	$su_w_in_month = 0;$ho_w_in_month	= 0;$days_retailed	= 0;$basic_sal		= 0;$da_sal			= 0;$phone_sal		= 0;$ta_sal			= 0.00;$misc_sal		= 0.0;$salary	= 0;$da_final_sal = 0;$ta_final_sal = 0.00;$ph_final_sal = 0;$misc_final_sal = 0;$final_salary = 0;
				$emp_id				= $row['id'];
				///Seprate Calculate
				/*$sep_qry			= mysql_query(" select max(`date`) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='7' and `date` between '$from' and '$to' ")or die(mysql_error());
				$sep_data   		= mysql_fetch_array($sep_qry);
				if(isset($sep_data['max(`date`)']))
				{
					$start 		= new DateTime($from);
					$end 		= new DateTime($sep_data['max(`date`)']);
					$days 		= $start->diff($end, true)->days;					
					$sundays_1 	= intval($days / 7) + ($start->format('N') + $days % 7 >= 7);
				}*/
				///Weekly Off Calculate
				$w_off_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='5' and `date` between '$from' and '$to' ")or die(mysql_error());
				$w_off_data   	= mysql_fetch_array($w_off_qry);
				$w_off_in_month	= $w_off_data['count(*)'];
				///Holidays Calculate
				$off_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='2' and `date` between '$from' and '$to' ")or die(mysql_error());
				$off_data   	= mysql_fetch_array($off_qry);
				$off_in_month	= $off_data['count(*)'];
				///working days
				$wor_in_month	= ($d-$sundays-$off_in_month);
				///Leave Calculate
				$lea_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark` IN('3','4') and `date` between '$from' and '$to' ")or die(mysql_error());
				$lea_data   	= mysql_fetch_array($lea_qry);
				$lea_in_month	= $lea_data['count(*)'];
				///Persent Calculate
				//and `date` NOT IN('$sunday_dts')
				$per_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='1' and `date` between '$from' and '$to' ")or die(mysql_error());
				$per_data   	= mysql_fetch_array($per_qry);
				$per_in_month	= $per_data['count(*)'];
				//// Balance Workin Days in month
				$b_wr_in_month	= $per_in_month;// - $lea_in_month);
				///Sunday Worked Calculate
				$sun_w_qry		= mysql_query(" select count(*) from `tbl_attendence` where `emp_id`='$emp_id' and `state`='$state' and `mark`='1' and `date` IN ('$sunday_dts') ")or die(mysql_error());
				$sun_w_data= mysql_fetch_array($sun_w_qry);
				$su_w_in_month  = $sun_w_data['count(*)'];
				/////Holidays Working Days
				///Days Retailed
				$days_retailed	= ($b_wr_in_month+$ho_w_in_month+$su_w_in_month);
				///basic salary
				$basic_sal		= $row['salary_rate'];
				$da_sal			= $row['da_rate'];
				$phone_sal		= $row['phone_rate'];
				$ta_sal			= $row['ta_rate'];
				$misc_sal		= $row['travel_exp'];
				/////Main Calculations
				$a              = ($b_wr_in_month+$w_off_in_month+$off_in_month+$su_w_in_month);
				$salary			= ($basic_sal/$d) * $a;
				if($salary>$basic_sal)
				{
					$salary     = $basic_sal;
				}
				$da_final_sal	= $days_retailed*$da_sal;
				if($da_final_sal>2500)
				{
					$da_final_sal = 2500;	
				}
				if(($month==2)&&($da_sal!=0))
				{
					if( ($per_in_month+$w_off_in_month)==$d)
					{
						$da_final_sal = 2500;	
					}
				}
				$ph_final_sal	= ($phone_sal/$d)*$a;
				if($ph_final_sal>500)
				{
					$ph_final_sal = 500;	
				}
				$ta_final_sal	= ($ta_sal)*$days_retailed;
				if($ta_final_sal>2000)
				{
					$ta_final_sal = 2000;	
				}				
				$misc_final_sal	= ($misc_sal);
				$final_salary   = ($salary+$da_final_sal+$ta_final_sal+$ph_final_sal+$misc_final_sal);
				$objPHPExcel->setActiveSheetIndex(0)			
				  ->setCellValue('A'.$i, ($i-1))
				  ->setCellValue('B'.$i, $row['emp_code'])
				  ->setCellValue('C'.$i, $row['emp_name'])
				  ->setCellValue('D'.$i, $row['desiganation'])
				  ->setCellValue('E'.$i, $row['fname'])
				  ->setCellValue('F'.$i, $row['state'])
				  ->setCellValue('G'.$i, $row['bank_name'])
				  ->setCellValue('H'.$i, $row['branch_ifsc'])
				  ->setCellValue('I'.$i, $row['account_no'])			  
				  ->setCellValue('J'.$i, $d)
				  ->setCellValue('K'.$i, $sundays)
				  ->setCellValue('L'.$i, $off_in_month)
				  ->setCellValue('M'.$i, $wor_in_month)
				  ->setCellValue('N'.$i, $d-($w_off_in_month+$days_retailed))				  
				  ->setCellValue('O'.$i, $b_wr_in_month)
				  ->setCellValue('P'.$i, 0)
				  ->setCellValue('Q'.$i, $su_w_in_month)
				  ->setCellValue('R'.$i, $ho_w_in_month)
				  ->setCellValue('S'.$i, $days_retailed)
				  ->setCellValue('T'.$i, round($basic_sal))
				  ->setCellValue('U'.$i, round($da_sal))
				  ->setCellValue('V'.$i, round($ta_sal))
				  ->setCellValue('W'.$i, round($phone_sal))
				  ->setCellValue('X'.$i, round($misc_sal))
				  ->setCellValue('Y'.$i, round($salary))
				  ->setCellValue('Z'.$i, round($da_final_sal))
				  ->setCellValue('AA'.$i, round($ta_final_sal))
				  ->setCellValue('AB'.$i, round($ph_final_sal))
				  ->setCellValue('AC'.$i, round($misc_final_sal))
				  ->setCellValue('AD'.$i, round($final_salary))
				  ->setCellValue('AE'.$i, $row['payment_status'])
				  ->setCellValue('AF'.$i, $row['uan_status']);
				  $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$i, $row['branch_ifsc'], PHPExcel_Cell_DataType::TYPE_STRING);
				  $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$i, $row['account_no'], PHPExcel_Cell_DataType::TYPE_STRING);
 $objPHPExcel->getActiveSheet()->getStyle('N'.$i.':AD'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
// $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setAutoSize(true);

$i++;
}
mysql_close();
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client's web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.$setExcelName.".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;