<?php
// Include required files
include 'inc_classes.php';
require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel.php';

if (PHP_SAPI == 'cli') {
    die('This example should only be run from a Web Browser');
}

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Define styles
$headerStyle = [
    'font' => [
        'bold' => true,
        'color' => ['rgb' => 'FFFFFF'],
        'size' => 12,
    ],
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['rgb' => '4F81BD'],
    ],
    'borders' => [
        'allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN],
    ],
    'alignment' => [
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    ]
];

$dataStyle = [
    'borders' => [
        'allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN],
    ],
    'alignment' => [
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    ]
];

$boldStyle = [
    'font' => [
        'bold' => true,
    ],
];
$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 headers
// Define styles for bold text and border
$header1Style = [
    'font' => [
        'bold' => true,           // Bold text
        'size' => 16,            // Increase font size
    ],
    // 'borders' => [
    //     'allborders' => [
    //         'style' => PHPExcel_Style_Border::BORDER_THIN, // Thin border
    //     ],
    // ],
    'alignment' => [
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, // Align center horizontally
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,    // Align center vertically
    ],
];
$objPHPExcel->getActiveSheet()->mergeCells('A1:B1');

// Set the header text for the merged cells
$objPHPExcel->setActiveSheetIndex(0)
    ->SetCellValue('A1', 'Task Report');
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($header1Style);


$objPHPExcel->setActiveSheetIndex(0)
    ->SetCellValue('A4', 'Sr. No.')
    ->SetCellValue('B4', 'Task Name')
    ->SetCellValue('C4', 'Task Assignee')
    ->SetCellValue('D4', 'Task Type')
    ->SetCellValue('E4', 'Priority')
    ->SetCellValue('F4', 'Task Time')
    ->SetCellValue('G4', 'Task Point')
    ->SetCellValue('H4', 'Status')
    ->SetCellValue('I4', 'Added Date')
    ->SetCellValue('J4', 'Dead Line')
    ->SetCellValue('K4', 'Complete Date')
    ->SetCellValue('L4', 'Assinged By');

// Define styles for conditional formatting
$redBackgroundStyle = [
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['argb' => 'FFFFC7CE'], // Light red
    ],
    'borders' => [
        'allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN],
    ],
    'alignment' => [
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    ]
];

$yellowBackgroundStyle = [
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['argb' => 'FFFFEB9C'], // Light yellow
    ],
    'borders' => [
        'allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN],
    ],
    'alignment' => [
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    ],
];

$lightBackgroundStyle = [
    'fill' => [
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => ['argb' => 'FFCCFFCC'], // Light green
    ],
    'borders' => [
        'allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN],
    ],
    'alignment' => [
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    ]
];
$objPHPExcel->getActiveSheet()->getStyle('A4:L4')->applyFromArray($headerStyle);
function convertToHoursMins($time, $format = '%02d:%02d') {
    if ($time < 1) {
        return;
    }
    $hours = floor($time / 60);
    $minutes = ($time % 60);
    return sprintf($format, $hours, $minutes);
}
    if($_REQUEST['start_date'] && $_REQUEST['start_date']!=="0000-00-00" && $_REQUEST['start_date']!="From Date")
    {
        $frm_date=explode("/",$_REQUEST['start_date']);
        $frm_dates=$frm_date[2]."-".$frm_date[1]."-".$frm_date[0];
        $start_date=$frm_dates;
        $dates=" and DATE(added_date )='".date('Y-m-d',strtotime($frm_dates))."'";
    }
    if($_REQUEST['branch_name']!='')
    {
        $branch_name=$_REQUEST['branch_name'];
        $select_cm_id="select cm_id from site_setting where branch_name='".$branch_name."' and type='A'";
        $ptr_cm_id=mysql_query($select_cm_id);
        $data_cm_id=mysql_fetch_array($ptr_cm_id);
        $search_cm_id="and cm_id='".$data_cm_id['cm_id']."'";
    }
    else
    {
        $search_cm_id='';
    }
    if($_REQUEST['staff_name1']!='')
    {
        $staff_id=$_REQUEST['staff_name1'];
        $search_staff_id="and staff_id='".$staff_id."'";
    }
    else
    {
        $search_staff_id='';
    }
    if($_REQUEST['staff_names']!='')
    {
        $admin_ids=$_REQUEST['staff_names'];
        $search_admin_id="and assign_by='".$admin_ids."'";
    }
    else
    {
        $search_admin_id='';
    }
    if($_REQUEST['task_type']!='')
    {
        $task_type=$_REQUEST['task_type'];
        $search_task_type="and task_type='".$task_type."'";
    }
    else
    {
        $search_task_type='';
    }
    if($_REQUEST['status']!='')
    {
        $status=$_REQUEST['status'];
        $search_status="and status='".$status."'";
    }
    else
    {
        $search_status='';
    }
$sql_querys = "select * from tk_individual_task where 1 ".$search_cm_id." ".$search_staff_id." ".$search_task_type." ".$search_status." ".$dates." and Validation='valid' order by added_date DESC ";
$query_order = mysql_query($sql_querys);

$rowCount = 5;
$i = 1;
while ($val_query = mysql_fetch_array($query_order)) {
    $sel_staff = "SELECT admin_id, name FROM site_setting WHERE admin_id='" . $val_query['staff_id'] . "'";
    $ptr_staff = mysql_query($sel_staff);
    $data_staff = mysql_fetch_array($ptr_staff);
    $staff_name = $data_staff['name'];
    $add1="select name from site_setting where 1 and admin_id='".$val_query['assign_by']."' and system_status='Enabled'";
    $ptr1=mysql_query($add1);
    $data1=mysql_fetch_array($ptr1);
    $status = $val_query['status'];
    if($status =='complete')
    {
    $complete_Date = date("d-m-Y", strtotime( $val_query['sta_complete_date']));
    }
    else{
    $complete_Date='-';
    }
    $objPHPExcel->setActiveSheetIndex(0)
        ->SetCellValue('A' . $rowCount, $i)
        ->SetCellValue('B' . $rowCount, ucwords($val_query['task_name']))
        ->SetCellValue('C' . $rowCount, ucwords($staff_name))
        ->SetCellValue('D' . $rowCount, ucwords($val_query['assign_type']))
        ->SetCellValue('E' . $rowCount, ucwords($val_query['priority']))
        ->SetCellValue('F' . $rowCount, convertToHoursMins($val_query['hours'], '%02d hour %02d min'))
        ->SetCellValue('G' . $rowCount, ucwords($val_query['points']))
        ->SetCellValue('H' . $rowCount, ucwords($status))
        ->SetCellValue('I' . $rowCount, date("d-m-Y", strtotime( $val_query['added_date'])))
        ->SetCellValue('J' . $rowCount, date("d-m-Y", strtotime( $val_query['due_date'])))
        ->SetCellValue('K' . $rowCount, $complete_Date)
        ->SetCellValue('L' . $rowCount, $data1['name']);

    // Apply bold style for Staff Name and Final Performance
    $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount)->applyFromArray($boldStyle);
    $objPHPExcel->getActiveSheet()->getStyle('L' . $rowCount)->applyFromArray($boldStyle);

    // Apply conditional background color based on final performance
    if ($status =='in_complete') {
        $objPHPExcel->getActiveSheet()->getStyle("A$rowCount:L$rowCount")->applyFromArray($redBackgroundStyle);
    } elseif ($status =='in_progress') {
        $objPHPExcel->getActiveSheet()->getStyle("A$rowCount:L$rowCount")->applyFromArray($yellowBackgroundStyle);
    } elseif ($status =='complete') {
        $objPHPExcel->getActiveSheet()->getStyle("A$rowCount:L$rowCount")->applyFromArray($lightBackgroundStyle);
    }else{
        $objPHPExcel->getActiveSheet()->getStyle("A$rowCount:L$rowCount")->applyFromArray($dataStyle);
    }

    $rowCount++;
    $i++;
}

// Adjust column widths
foreach (range('A', 'L') as $columnID) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Performance');

// Redirect output to the browser
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Task Report '. date('d M Y') . '.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
