<?php
include 'inc_classes.php';
if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle2 = new PHPExcel_Style();
//styles
    $sharedStyle1->applyFromArray(
	array('fill' 	=> array(
								'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
							),
		  'borders' => array(
		                         'top'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
								'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
								'right'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
								'left'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
								
							),
		  'alignment' => array(
                               'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER),
							   'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
		 ));
  	$sharedStyle2->applyFromArray(
	array('fill' 	=> array(
                                'type'  => PHPExcel_Style_Fill::FILL_SOLID,
                                'color' => array('argb' => '#FFDAB9') 
							),
		  'borders' => array(
								'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
								'top'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
								'right'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
								'left'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
							),
		  'alignment' => array(
                               'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
		 ));

// 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");


// Add some data

    if($_REQUEST['start_date'])
    {
        $or_date=date('d-m-Y',strtotime($_REQUEST['start_date']));
        $c_date=date('Y-m-d',strtotime($_REQUEST['start_date']));
        $dates=" and DATE(added_date )='".date('Y-m-d',strtotime($_REQUEST['start_date']))."'";
        $date=" and DATE(followup_date )='".date('Y-m-d',strtotime($_REQUEST['start_date']))."'";
    }
    else{
        $dates="";
        $date="";
    }
    if($_REQUEST['staff_id'])
    {
        $admin_id=$_REQUEST['staff_id'];
        $search_admin_id="and admin_id='".$_REQUEST['staff_id']."'";
        $search_staff_id="and staff_id='".$_REQUEST['staff_id']."'";
    }
    else{
        $search_admin_id="";
        $search_staff_id="";
        $admin_id='';
    }
$sel_staff = "SELECT admin_id,attendence_id, name, type FROM site_setting WHERE 1 AND admin_id='".$admin_id."'";
$ptr_staff = mysql_query($sel_staff);
$data_staff = mysql_fetch_array($ptr_staff);
$staff_name = $data_staff['name'];
$staff_att_id=$data_staff['attendence_id'];
if($data_staff['type']=='F'){
    $working_time=450;
}
else{
    $working_time=480;
}
$select_user="select * from pr_pune_biometric_attendance where UserID='".$staff_att_id."' and DATE(PunchTime)='".$c_date."' order by PunchTime asc";
$ptr_chk_ext = mysql_query($select_user);
$check_in = '';
$check_out = '';
if ($ptr_chk_ext && mysql_num_rows($ptr_chk_ext) > 0) {
    $data_user_att = mysql_fetch_array($ptr_chk_ext);
        if ($data_user_att['AttendanceType'] == 'CheckIn') {
        $check_in = date('H:i', strtotime($data_user_att['PunchTime']));
        $data_user_att = mysql_fetch_array($ptr_chk_ext);
        if ($data_user_att && $data_user_att['AttendanceType'] == 'CheckOut') {
            $check_out = date('H:i', strtotime($data_user_att['PunchTime']));
        }
    } 
    else if ($data_user_att['AttendanceType'] == 'CheckOut') {
        $check_out = date('H:i', strtotime($data_user_att['PunchTime']));
        $data_user_att = mysql_fetch_array($ptr_chk_ext);
        if ($data_user_att && $data_user_att['AttendanceType'] == 'CheckIn') {
            $check_in = date('H:i', strtotime($data_user_att['PunchTime']));
        }
    }
}


$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A1', 'Staff Name:');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('B1', $staff_name);

$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A2', 'Date:');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('B2', $or_date);

$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A3', 'Reporting time:');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('B3', $check_in);
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C3', 'Leaving time:');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('D3', $check_out);
$styleArrayLightGreen = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => array('rgb' => '90EE90')
    ),
);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3:B3')->applyFromArray($styleArrayLightGreen);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C3:D3')->applyFromArray($styleArrayLightGreen);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1:D4')->getFont()->setBold(true);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1:D4')->getFont()->setSize(12);
$styleArray = [
    'borders' => [
        'allborders' => [
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => ['argb' => 'FF000000'],
        ],
    ],
];
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1:D4')->applyFromArray($styleArray);

$sel_for_Num_rows="select * from tk_individual_task where 1 ".$search_staff_id." ".$dates."";
$my_querxy=mysql_query($sel_for_Num_rows);
$no_of_rows=mysql_num_rows($my_querxy);
$no_of_rows = ($no_of_rows+8);
$no_of_rows_new = ($no_of_rows+3);

//================Apply styles============================
$objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle1, "A5:A$no_of_rows");
$objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle1, "B5:B$no_of_rows");
$objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle1, "C5:C$no_of_rows");
$objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle1, "D5:D$no_of_rows");
$objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle1, "E5:E$no_of_rows");
$objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle1, "F5:F$no_of_rows");
$objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle1, "G5:G$no_of_rows");
$objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle1, "H5:H$no_of_rows");
// $objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle1, "I5:I$no_of_rows");
$objPHPExcel->setActiveSheetIndex(0)->setSharedStyle($sharedStyle2, "A4:H4");

//================== Set column widths==============================
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(11);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(70);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setAutoSize(true);
// $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setAutoSize(true);

$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1:A'.$objPHPExcel->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true); 
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B1:B'.$objPHPExcel->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true); 
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C1:C'.$objPHPExcel->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true); 
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D1:D'.$objPHPExcel->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true); 
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E1:E'.$objPHPExcel->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true); 
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F1:F'.$objPHPExcel->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true); 
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G1:G'.$objPHPExcel->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true); 
$objPHPExcel->setActiveSheetIndex(0)->getStyle('H1:H'.$objPHPExcel->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true); 
// $objPHPExcel->setActiveSheetIndex(0)->getStyle('I1:I'.$objPHPExcel->setActiveSheetIndex(0)->getHighestRow())->getAlignment()->setWrapText(true); 

$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A4', 'Sr. No.' ,'5');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('B4', 'Task Name','5');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C4', 'Task Time','5');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('D4', 'Task Point','6');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('E4', 'Task Details','5');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('F4', 'Staff Status','5');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('G4', 'Status','5');
$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('H4', 'Validation','5');
// $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('H4', 'Enquiry Assigned By','5');
// $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('H4', 'Enquiry Source','5');

function convertToHoursMins($time, $format = '%02d:%02d') {
    if ($time < 1) {
        return;
    }
    $hours = floor($time / 60);
    $minutes = ($time % 60);
    return sprintf($format, $hours, $minutes);
}
$sql_querys="select * from tk_individual_task where 1 ".$search_staff_id." ".$dates."";
$query_order=mysql_query($sql_querys);
$i=1;
$rowCount=5;
$k=1;
$task_hours=0;
while($val_query=mysql_fetch_array($query_order))
{
    $task_hours+=$val_query['hours'];
	$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A'.$rowCount, $i);
	$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('B'.$rowCount, strip_tags(ucwords($val_query['task_name'])));						
	$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C'.$rowCount, convertToHoursMins($val_query['hours'], '%02d hour %02d min'));
	$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('D'.$rowCount, $val_query['points']);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('E'.$rowCount, strip_tags(ucwords($val_query['description'])));	
	$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('F'.$rowCount, $val_query['staff_status']);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('G'.$rowCount, $val_query['status']);				
	$objPHPExcel->setActiveSheetIndex(0)->SetCellValue('H'.$rowCount, $val_query['Validation']);	
	$k++;
	$rowCount++;
	$i++;			
}
$tot_id=0;
$sel_student_batch="select distinct(followup_id) from followup_details where 1 and response!='1' ".$search_admin_id." ".$dates."";
$ptr_student_batch=mysql_query($sel_student_batch);
if($tot_call_id=mysql_num_rows($ptr_student_batch))
{
    $tot_id= $tot_call_id*3;
    $calling_point=number_format($tot_id*0.208, 2);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A'.$rowCount, $i);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('B'.$rowCount, 'Calling ['.$tot_call_id.']');						
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C'.$rowCount, convertToHoursMins($tot_id, '%02d hour %02d min'));
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('D'.$rowCount, $calling_point);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('E'.$rowCount, 'Doing Followup Calls');	
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('F'.$rowCount, 'Complete');		
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('G'.$rowCount, 'Complete');		
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('H'.$rowCount, 'Valid');
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount.':H'.$rowCount)->applyFromArray(
        array(
            'font'  => array(
                'bold'  => true,
            ),
        )
    );
    $rowCount++;
    $i++;
}
$tot_enq=0;
$sel_student_enq="select distinct(followup_id) from followup_details where 1 and response='1' ".$search_admin_id." ".$dates."";
$ptr_student_enq=mysql_query($sel_student_enq);
if($tot_student_enq=mysql_num_rows($ptr_student_enq))
{
    $tot_enq= $tot_student_enq*45;
    $inquiry_point=number_format($tot_enq*0.208, 2);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A'.$rowCount, $i);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('B'.$rowCount, 'Inquiry ['.$tot_student_enq.']');						
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C'.$rowCount, convertToHoursMins($tot_enq, '%02d hour %02d min'));
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('D'.$rowCount, $inquiry_point);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('E'.$rowCount, 'Taking Walk-In Inquiry');	
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('F'.$rowCount, 'Complete');		
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('G'.$rowCount, 'Complete');		
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('H'.$rowCount, 'Valid');
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount.':H'.$rowCount)->applyFromArray(
        array(
            'font'  => array(
                'bold'  => true,
            ),
        )
    );
    $rowCount++;
    $i++;
}
$hour=0;
$sel_student_batch="select distinct(c_b_id) from batch_timetable where 1 ".$search_staff_id." ".$dates."";
$ptr_student_batch=mysql_query($sel_student_batch);
if($tot_batch_id=mysql_num_rows($ptr_student_batch))
{
    $batch_time= $tot_batch_id*2.5;
    $hour=  $batch_time*60 ;
    $batch_point=number_format($hour*0.221, 2);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A'.$rowCount, $i);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('B'.$rowCount, 'Batch ['.$tot_batch_id.']');						
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C'.$rowCount, convertToHoursMins($hour, '%02d hour %02d min'));
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('D'.$rowCount, $batch_point);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('E'.$rowCount, 'Taking Batch');	
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('F'.$rowCount, 'Complete');		
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('G'.$rowCount, 'Complete');		
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('H'.$rowCount, 'Valid');
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount.':H'.$rowCount)->applyFromArray(
        array(
            'font'  => array(
                'bold'  => true,
            ),
        )
    );
    $rowCount++;
    $i++;
}
$hour=0;
$lead_time=0;
$sel_minute="select minutes from pm_sales_kpi where action_name='add_lead'";
$ptr_mintute=mysql_query($sel_minute);
$data_minute=mysql_fetch_array($ptr_mintute);
$lead_min=$data_minute['minutes'];
$sel_lead="select distinct(inquiry_id) from inquiry where 1 ".$dates." ".$search_admin_id."";
$ptr_lead=mysql_query($sel_lead);
$lead_record=mysql_num_rows($ptr_lead);
if($lead_record){
    $lead_time=$lead_record*$lead_min;
    $lead_point=number_format($lead_time*0.208, 2);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A'.$rowCount, $i);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('B'.$rowCount, 'Leads ['.$lead_record.']');						
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C'.$rowCount, convertToHoursMins($lead_time, '%02d hour %02d min'));
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('D'.$rowCount, $lead_point);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('E'.$rowCount, 'Adding Leads In system');	
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('F'.$rowCount, 'Complete');		
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('G'.$rowCount, 'Complete');		
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('H'.$rowCount, 'Valid');
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount.':H'.$rowCount)->applyFromArray(
        array(
            'font'  => array(
                'bold'  => true,
            ),
        )
    );
    $rowCount++;
    $i++;
}

$total_hours = $task_hours + $tot_id + $tot_enq + $hour + $lead_time;
$remain_time = $working_time - $total_hours;

if ($total_hours > $working_time) {
    $over_time = $total_hours - $working_time;
    $remain_time = 0;
} else {
    $over_time = 0;
}
if ($total_hours > 0) {
    $objPHPExcel->getActiveSheet()->mergeCells('A'.$rowCount.':B'.$rowCount);
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A'.$rowCount, 'Total Working Time');
    $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C'.$rowCount, convertToHoursMins($total_hours, '%02d hour %02d min'));
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount.':C'.$rowCount)->applyFromArray(
        array(
            'font' => array(
                'bold' => true,
                'size' => 12,
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('rgb' => 'FFFF00') // Yellow background
            ),
        )
    );
    $rowCount++;
    $i++;
}
if ($remain_time > 0 || $over_time > 0) {
    $objPHPExcel->getActiveSheet()->mergeCells('A'.$rowCount.':B'.$rowCount);
    
    if ($over_time > 0) {
        $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A'.$rowCount, 'Over Time');
        $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C'.$rowCount, convertToHoursMins($over_time, '%02d hour %02d min'));
    } else {
        $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('A'.$rowCount, 'Not Working Time');
        $objPHPExcel->setActiveSheetIndex(0)->SetCellValue('C'.$rowCount, convertToHoursMins($remain_time, '%02d hour %02d min'));
    }
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount.':C'.$rowCount)->applyFromArray(
        array(
            'font' => array(
                'bold' => true,
                'size' => 12,
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('rgb' => 'FFCCCC') // Light Red background
            ),
        )
    );
    $rowCount++;
    $i++;
}
// Miscellaneous glyphs, UTF-8
// 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="'.ucwords($staff_name).' Timesheet '.$or_date.'.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;