<?php
/*session_start();
header ("Expires: Mon, 28 Oct 2008 05:00:00 GMT");
			header ("Last-Modified: " . gmdate("D,d M Y H:i:s") . " GMT");
			header ("Cache-Control: no-cache, must-revalidate");
			header ("Pragma: no-cache");
			
			header ("Content-type: application/vnd.ms-excel");
			header ("Content-Disposition: attachment; filename=Salarylist.xls" );
			header ("Content-Description: Generated Report" );
			
			echo $_SESSION['tablehtml'];
			exit;*/
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();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Aadhocc Technologies")
							 ->setLastModifiedBy("Aadhocc Technologies")
							 ->setTitle("Pay Salary Report")
							 ->setSubject("Pay Salary Report")
							 ->setDescription("Pay Salary Report")
							 ->setKeywords("Pay Salary Report")
							 ->setCategory("Pay Salary Report");
///// Create 
$objPHPExcel->setActiveSheetIndex(0);

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

$objPHPExcel->getActiveSheet()->mergeCells('A1:K1');
$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()->getRowDimension(4)->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getStyle('A4:k4')->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()->setCellValue('A4', "Employee Code");
$objPHPExcel->getActiveSheet()->setCellValue('B4', "Employee Name");
$objPHPExcel->getActiveSheet()->setCellValue('C4', "Designation");
$objPHPExcel->getActiveSheet()->setCellValue('D4', "Earning");
$objPHPExcel->getActiveSheet()->setCellValue('E4', "Deduction");
$objPHPExcel->getActiveSheet()->setCellValue('F4', "LOP");
$objPHPExcel->getActiveSheet()->setCellValue('G4', "Net Pay");
$objPHPExcel->getActiveSheet()->setCellValue('H4', "Bank Name");
$objPHPExcel->getActiveSheet()->setCellValue('I4', "Branch Name");
$objPHPExcel->getActiveSheet()->setCellValue('J4', "Account No");
$objPHPExcel->getActiveSheet()->setCellValue('K4', "IFSC Code");

	$k=5;
	$cond='where 1';
	if($_GET['branch'])
	{
		$cond.=' and A.branchid='.$_GET['branch'].'';
	}
	if($_GET['memberid'])
	{
		$cond.=' and A.e_id='.$_GET['memberid'].'';
	}
	
	$EmpPayStructure_array= $db->fetchQuery("SELECT A.e_id,A.employee_id,B.desig_id,A.e_fname,desig_name,branch_name,emp_basic_pay,date_join,e_bank,e_accountno,e_code,e_bankadd FROM employee A join designation B on A.e_post=B.desig_id join branches C on A.branchid=C.branch_id ".$cond." ");
	foreach($EmpPayStructure_array as $EmpPayStructure_row)
	{
		if ($k % 2 != 0) { 
			$objPHPExcel->getActiveSheet()->getStyle('A'.$k.':k'.$k.'')->applyFromArray(
				array(
					'fill' => array(
						'type' => PHPExcel_Style_Fill::FILL_SOLID,
						'color' => array('rgb' => 'dbeef4')
					),
					'borders' => array(
						'allborders' => array(
							'style' => PHPExcel_Style_Border::BORDER_THIN
						)
					)
				)
			);
		}
		else
		{
			$objPHPExcel->getActiveSheet()->getStyle('A'.$k.':k'.$k.'')->applyFromArray(
				array(
					'borders' => array(
						'allborders' => array(
							'style' => PHPExcel_Style_Border::BORDER_THIN
						)
					)
				)
			);
		}
		///////////////////////// Calculate LOP //////////////////////////////////////////////////
		$explode_date_of_join=explode('-',$EmpPayStructure_row['date_join']);
		if($_GET['salyear'])
		{
			$year=$_GET['salyear'];
		}
		else
		{
			$year=date("Y");
		}
		
		if($_GET['salmonth'])
		{
			$month=$_GET['salmonth'];
		}
		else
		{
			$month=date("m");
		}
		
		if($year==date('Y') && $month==date('m'))
		{
			// To find no of days between date ranges
			//$now = time();
			$yesterday_to_time=strtotime(date('Y-m-d',strtotime("-1 days")));
     		$first_date = strtotime(''.$year.'-'.$month.'-01');

     		$datediff = $yesterday_to_time - $first_date;
     		$last_day =floor($datediff/(60*60*24));
			
			$endOfLastmth=date('Y-m-d',strtotime("-1 days"));
		}
		else
		{
			$last_day = cal_days_in_month(CAL_GREGORIAN, $month, $year);
			$endOfLastmth=$year.'-'.$month.'-'.$last_day;
		}
		
		if($explode_date_of_join[2]==$year && $explode_date_of_join[1]==$month)
		{
			$firstOfLastmth=$year.'-'.$month.'-'.$explode_date_of_join[0].'';
		}
		else
		{
			$firstOfLastmth=$year.'-'.$month.'-01';
		}
		
		$availedleaves=0;
			
			
		//$endOfLastmth=date('Y-m-d');
		$lastMtnHolidays = $db->fetchQuery("SELECT * FROM payroll_holiday_calendar WHERE date between '".$firstOfLastmth."' and '".$endOfLastmth."' and is_blocked='0' ");
		$holidays=array();
		foreach($lastMtnHolidays as $lastMtnHolidays){
			  $holidays[]=$lastMtnHolidays['date'];
		}
			
		$noofholidays=count($holidays);

		$workingDays=  $db->getWorkingDays($firstOfLastmth,$endOfLastmth,$holidays);
		
		$lastyrattendance = $db->fetchQuery("SELECT count(distinct(e_date)) FROM time WHERE e_date>='".$firstOfLastmth."' and e_date<='".$endOfLastmth."' and e_id='".$EmpPayStructure_row['e_id']."'");
		$yrattendance=$lastyrattendance[0]['count(distinct(e_date))'];
		
		$approved_leaves_array =array();
		$approved_leaves_array = $db->fetchQuery("Select day_from,half_full_day from payroll_user_leave_requests where employee_id='".$EmpPayStructure_row['e_id']."' and is_cancelled=0 and imd_status=1 and is_blocked=0 and day_from>='".$firstOfLastmth."' and day_from<='".$endOfLastmth."' ");
			
		$approved_leaves_count=count($approved_leaves_array);
		$approved_leaves_present=0;
			
		if(!empty($approved_leaves_array))
		{
			foreach($approved_leaves_array as $approved_leaves_array){
			  $approved_leaves_date_array[]=$approved_leaves_array['day_from'];
			}	
			$leave_date_clause = join(',',$approved_leaves_date_array); 
			$approved_leaves_query=$db->fetchQuery("SELECT count(distinct(e_date)) as leave_count FROM time WHERE e_date in (".$leave_date_clause.") and e_id='".$EmpPayStructure_row['e_id']."' ");
				
			$approved_leaves_present=$approved_leaves_query[0]['leave_count']; 
			$approved_leaves_count=$approved_leaves_count-$approved_leaves_present; 
		}
		
		$availedleaves=$workingDays-($yrattendance+$approved_leaves_count);
		
		///////////////////////////// End of LOP Calculations ////////////////////////////
		
		$earning_total_amount=0;
		$deduction_total_amount=0;
		$netpay_total_amount=0;
		$lop_total_amount=0;
		
		$basic_salary=$EmpPayStructure_row['emp_basic_pay'];
		if($year==$explode_date_of_join[2])
		{
			if($month<$explode_date_of_join[1])
			{
				$basic_salary=0;
			}
		}
		//$basic_salary=$EmpPayStructure_row['emp_basic_pay'];
		
		$condition_structure['employee_id'] = $EmpPayStructure_row['e_id'];
		$emp_str_count = $db->CountRows('salary_structure_employee',$condition_structure);
		
		if($emp_str_count == 0){
			$str_type=1;
			$collectid = $EmpPayStructure_row['desig_id'];
		}else{
			$str_type=2;
			$collectid = $EmpPayStructure_row['e_id'];
		}
		
		$salary_calculated_array=salaryCalculation($collectid,$basic_salary,$str_type);
		
		
		
	//	$salary_calculated_array=salaryCalculation($EmpPayStructure_row['desig_id'],$basic_salary);
		
		////////////////////////////////////////////////// EARNINGS//////////////////////////////////
		$earnings = $salary_calculated_array['earnings'];
		$expandable_earning_rows;
		
		$total_monthly_earning_amount=0;
		$total_yearly_earning_amount = 0;
		foreach($earnings as $earnings):
		
			$total_monthly_earning_amount +=$earnings['calculateAmt'];
			$total_yearly_earning_amount +=($earnings['calculateAmt']*12);
									
		endforeach;
		
		
		
		////////////////////////////////////////////////// DEDUCTION//////////////////////////////////
		$deduction = $salary_calculated_array['deduction'];
		$expandable_deduction_rows;
		$total_monthly_deduction_amount=0;
		$total_yearly_deduction_amount = 0;
		foreach($deduction as $deduction):
		
			$total_monthly_deduction_amount +=$deduction['calculateAmt'];
			$total_yearly_deduction_amount +=($deduction['calculateAmt']*12);						
									
		endforeach;
		
		
		$earning_total_amount= $total_monthly_earning_amount;
		$deduction_total_amount= $total_monthly_deduction_amount;
		//$lop_total_amount= (($salary_calculated_array['earnings'][0]['calculateAmt']/12)/$last_day)*$availedleaves;
		//$netpay_total_amount = ($total_monthly_earning_amount/12) - ($total_monthly_deduction_amount/12) - $lop_total_amount;
		$lop_total_amount= ($salary_calculated_array['earnings'][0]['calculateAmt']/(12*(cal_days_in_month(CAL_GREGORIAN, $month, $year))))*$availedleaves;
		//$lop_total_amount=$availedleaves;
		$netpay_total_amount = ($total_monthly_earning_amount/12) - ($total_monthly_deduction_amount/12) - $lop_total_amount;
		
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$k, $EmpPayStructure_row['e_id']);
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$k, $EmpPayStructure_row['e_fname']);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$k, $EmpPayStructure_row['desig_name']);
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$k, number_format(round($earning_total_amount/12),2, '.', ''));
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$k, number_format(round($deduction_total_amount/12),2, '.', ''));
		$objPHPExcel->getActiveSheet()->setCellValue('F'.$k, number_format(round($lop_total_amount),2, '.', ''));
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$k, number_format(round($netpay_total_amount),2, '.', ''));
		$objPHPExcel->getActiveSheet()->setCellValue('H'.$k, $EmpPayStructure_row['e_bank']);
		$objPHPExcel->getActiveSheet()->setCellValue('I'.$k, $EmpPayStructure_row['e_bankadd']);
		$objPHPExcel->getActiveSheet()->setCellValue('J'.$k, $EmpPayStructure_row['e_accountno']);
		$objPHPExcel->getActiveSheet()->setCellValue('K'.$k, $EmpPayStructure_row['e_code']);
		
		$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);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Pay_Salary_Report.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;


function salaryCalculation($designation,$basicPay,$type=1) {

		if($type == 1){
		
	$earningsDeduction = $GLOBALS[db]->fetchQuery("SELECT * FROM salary_designation A 
											   LEFT JOIN salary_structure B ON A.salary_structure_id = B.structure_id 
											   LEFT JOIN earning_deduction C ON B.earning_deduction_id	= C.id
											   WHERE A.designation_id='$designation' ORDER BY B.id ASC");
		}else{
		
		
	$earningsDeduction = $GLOBALS[db]->fetchQuery("SELECT * FROM salary_structure_employee A 
											   LEFT JOIN salary_structure B ON A.salary_structure_id = B.structure_id 
											   LEFT JOIN earning_deduction C ON B.earning_deduction_id	= C.id
											   WHERE A.employee_id='$designation' ORDER BY B.id ASC");	
		
		
		
		}								   
											   
											   
											   
	if($basicPay == 0){
			$basicPay = $earningsDeduction[0]['amount'];
	}										   
								   
	$eacalculation = array();
	$c=0;
	foreach($earningsDeduction as $eacalculation){
		// Get All earnings
		if($eacalculation['type'] == 1){
			$calculation['earnings'][$c]['name'] 		= $eacalculation['name'];
			$calculation['earnings'][$c]['parentid'] 		= $eacalculation['parent_id'];
			$calculation['earnings'][$c]['percentage'] 	= $eacalculation['percentage'];
			$calculation['earnings'][$c]['maxamount']	= $eacalculation['amount'];
			$calculation['earnings'][$c]['id']			= $eacalculation['id'];
			$maxamount = $eacalculation['amount'];
			if($eacalculation['parent_id'] == 0 ){
				$parentAmount = $basicPay;
			}else{
				$parentAmount =searchForId($eacalculation['parent_id'], $calculation['earnings']);  
			}
			
			$calculation['earnings'][$c]['calculateAmt']= calculatePercent($eacalculation,$parentAmount,$maxamount);
			
			
		}
		
		// Get all deduction
		if($eacalculation['type'] ==2){
			$calculation['deduction'][$c]['name'] 		= $eacalculation['name'];
			$calculation['deduction'][$c]['parentid'] 		= $eacalculation['parent_id'];
			$calculation['deduction'][$c]['percentage'] = $eacalculation['percentage'];
			$calculation['deduction'][$c]['maxamount']	= $eacalculation['amount'];
			$calculation['deduction'][$c]['id']			= $eacalculation['id'];
			$maxamount = $eacalculation['amount'];
			
			if($eacalculation['parent_id'] == 0 ){
			
				$parentAmount = $basicPay;
				
			}elseif($eacalculation['parent_id'] == 2){
			
				$parentAmount = getGrossPay($calculation['earnings'],$basicPay);
			
			}else{
				$parentAmount =searchForId($eacalculation['parent_id'], $calculation['earnings']);  
			}
			
			$calculation['deduction'][$c]['calculateAmt']= calculatePercent($eacalculation,$parentAmount,$maxamount);
			
		}
		
		$c++;	
	}
	
	//print_r($calculation);
	return $calculation;
}

function calculatePercent($eacalculation,$parentAmount,$maxamount){
	$basicpay = $parentAmount;
	$percent  = $eacalculation['percentage'];
	$amount = $basicpay*($percent/100);
	if(round($basicpay))
	{
		if($amount && round($maxamount))
		{
			$amount = ($amount < $maxamount)?$amount:$maxamount;
		}
		elseif($amount)
		{
			$amount =$amount;
		}
		else
		{
			$amount = $maxamount;
		}
	}
	else
	{
		$amount=0;
	}
	
	return $amount;
}

function searchForId($id, $array) {
   foreach ($array as $key => $val) {
       if ($val['id'] === $id) {
           return $val['calculateAmt'];
       }
   }
   return null;
}

function getGrossPay($earnings,$basicPay){
//print_r($earnings);
	$grossPay  = 0;
	foreach($earnings as $earnings){
		 $grossPay +=$earnings['calculateAmt'];
	}
	return $grossPay;
}

function convert_number_to_words($number) {
    
    $hyphen      = '-';
    $conjunction = ' and ';
    $separator   = ', ';
    $negative    = 'negative ';
    $decimal     = ' point ';
    $dictionary  = array(
        0                   => 'zero',
        1                   => 'one',
        2                   => 'two',
        3                   => 'three',
        4                   => 'four',
        5                   => 'five',
        6                   => 'six',
        7                   => 'seven',
        8                   => 'eight',
        9                   => 'nine',
        10                  => 'ten',
        11                  => 'eleven',
        12                  => 'twelve',
        13                  => 'thirteen',
        14                  => 'fourteen',
        15                  => 'fifteen',
        16                  => 'sixteen',
        17                  => 'seventeen',
        18                  => 'eighteen',
        19                  => 'nineteen',
        20                  => 'twenty',
        30                  => 'thirty',
        40                  => 'fourty',
        50                  => 'fifty',
        60                  => 'sixty',
        70                  => 'seventy',
        80                  => 'eighty',
        90                  => 'ninety',
        100                 => 'hundred',
        1000                => 'thousand',
        1000000             => 'million',
        1000000000          => 'billion',
        1000000000000       => 'trillion',
        1000000000000000    => 'quadrillion',
        1000000000000000000 => 'quintillion'
    );
    
    if (!is_numeric($number)) {
        return false;
    }
    
    if (($number >= 0 && (int) $number < 0) || (int) $number < 0 - PHP_INT_MAX) {
        // overflow
        trigger_error(
            'convert_number_to_words only accepts numbers between -' . PHP_INT_MAX . ' and ' . PHP_INT_MAX,
            E_USER_WARNING
        );
        return false;
    }

    if ($number < 0) {
        return $negative . convert_number_to_words(abs($number));
    }
    
    $string = $fraction = null;
    
    if (strpos($number, '.') !== false) {
        list($number, $fraction) = explode('.', $number);
    }
    
    switch (true) {
        case $number < 21:
            $string = $dictionary[$number];
            break;
        case $number < 100:
            $tens   = ((int) ($number / 10)) * 10;
            $units  = $number % 10;
            $string = $dictionary[$tens];
            if ($units) {
                $string .= $hyphen . $dictionary[$units];
            }
            break;
        case $number < 1000:
            $hundreds  = $number / 100;
            $remainder = $number % 100;
            $string = $dictionary[$hundreds] . ' ' . $dictionary[100];
            if ($remainder) {
                $string .= $conjunction . convert_number_to_words($remainder);
            }
            break;
        default:
            $baseUnit = pow(1000, floor(log($number, 1000)));
            $numBaseUnits = (int) ($number / $baseUnit);
            $remainder = $number % $baseUnit;
            $string = convert_number_to_words($numBaseUnits) . ' ' . $dictionary[$baseUnit];
            if ($remainder) {
                $string .= $remainder < 100 ? $conjunction : $separator;
                $string .= convert_number_to_words($remainder);
            }
            break;
    }
    
    if (null !== $fraction && is_numeric($fraction)) {
        $string .= $decimal;
        $words = array();
        foreach (str_split((string) $fraction) as $number) {
            $words[] = $dictionary[$number];
        }
        $string .= implode(' ', $words);
    }
    
    return $string;
}
?>