<?php
session_start();
include("../../../../config/database.php");
$conct  = new connectionCreat();
$conct->connectToDB();
$conct->selectDB();
$setExcelName	   = "isr_details".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 makecomma($input)
{
    // This function is written by some anonymous person - I got it from Google
    if(strlen($input)<=2)
    { return $input; }
    $length=substr($input,0,strlen($input)-2);
    $formatted_input = makecomma($length).",".substr($input,-2);
    return $formatted_input;
}
function formatInIndianStyle($num){
    // This is my function
    $pos = strpos((string)$num, ".");
    if ($pos === false) { $decimalpart="00";}
    else { $decimalpart= substr($num, $pos+1, 2); $num = substr($num,0,$pos); }

    if(strlen($num)>3 & strlen($num) <= 12){
                $last3digits = substr($num, -3 );
                $numexceptlastdigits = substr($num, 0, -3 );
                $formatted = makecomma($numexceptlastdigits);
                $stringtoreturn = $formatted.",".$last3digits.".".$decimalpart ;
    }elseif(strlen($num)<=3){
                $stringtoreturn = $num.".".$decimalpart ;
    }elseif(strlen($num)>12){
                $stringtoreturn = number_format($num, 2);
    }

    if(substr($stringtoreturn,0,2)=="-,"){$stringtoreturn = "-".substr($stringtoreturn,2 );}

    return $stringtoreturn;
}
$invoice_no = $_GET['invoice_no'];
$sql_1		= mysql_query("SELECT * FROM `tbl_invoice` where `invoice_no`='$invoice_no' ")or die(mysql_error());
$data_1		= mysql_fetch_array($sql_1);
$monthName  = date('F', mktime(0, 0, 0, $data_1['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', 'Employee Name')
			->setCellValue('C1', 'Designation')
			->setCellValue('D1', 'Headquarter')
			->setCellValue('E1', 'State')
			->setCellValue('F1', $monthName." ".$data_1['year'].' Payment');									
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setWrapText(true); 								
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->applyFromArray($styleArray3);
$i	= 2;	
$sql		 = mysql_query(" SELECT * FROM `tbl_isr_details` where `invoice_no`='$invoice_no' ")or die(mysql_error());

            $j					= 0;
			$total				= 0;
			$cout_rows			= mysql_num_rows($sql);
            while($row			= mysql_fetch_array($sql))
            {
				$sql_4			= mysql_query("SELECT * FROM `tbl_emp_records` WHERE `id`='".$row['emp_id']."' ")or die(mysql_error());
				$data_4			= mysql_fetch_array($sql_4);
				
				$total		= $total+$row['amt'];	
				$objPHPExcel->setActiveSheetIndex(0)			
				  ->setCellValue('A'.$i, ($i-1))
				  ->setCellValue('B'.$i, ucwords(strtolower($data_4['emp_name'])))
				  ->setCellValue('C'.$i, $data_4['desiganation'])
				  ->setCellValue('D'.$i, ucwords(strtolower($data_4['head_qtrer'])))
				  ->setCellValue('E'.$i, ucwords(strtolower($data_4['state'])))
				  ->setCellValue('F'.$i, formatInIndianStyle($row['amt']));
				  $i++;
			}
// $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->mergeCells('A'.$i.':E'.$i);

$objPHPExcel->setActiveSheetIndex(0)			
->setCellValue('A'.$i, "Total")
->setCellValue('F'.$i, formatInIndianStyle(round($total)));
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':F'.$i)->applyFromArray($styleArray3);
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;