<?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,
    ],
];
$selBranch="SELECT branch_name FROM site_setting WHERE 1 and cm_id='".$_REQUEST['cmIds']."' and type='A'";
$ptrBranch=mysql_query($selBranch);
$dataBranch=mysql_fetch_array($ptrBranch);
$sel_mon="select month_name,month_id from months where month_id='".$_REQUEST['months']."'";
$ptr_mon=mysql_query($sel_mon);
$data_mon=mysql_fetch_array($ptr_mon);
// 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 headers
// Define styles for bold text and border
$header1Style = [
    'font' => [
        'bold' => true, // Bold text
    ],
    'borders' => [
        'allborders' => [
            'style' => PHPExcel_Style_Border::BORDER_THIN, // Thin border
        ],
    ],
    'alignment' => [
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, // Align left
    ]
];

$objPHPExcel->setActiveSheetIndex(0)
    ->SetCellValue('A1', 'Branch Name:')
    ->SetCellValue('B1', $dataBranch['branch_name'])
    ->SetCellValue('A2', 'Month:')
    ->SetCellValue('B2', $data_mon['month_name'])
    ->SetCellValue('A3', 'Year:')
    ->SetCellValue('B3',$_REQUEST['years']);

// Apply the bold and border styles to the header cells
$objPHPExcel->getActiveSheet()->getStyle('A1:B3')->applyFromArray($header1Style);


$objPHPExcel->setActiveSheetIndex(0)
    ->SetCellValue('A4', 'Sr. No.')
    ->SetCellValue('B4', 'Staff Name')
    ->SetCellValue('C4', 'Month')
    ->SetCellValue('D4', 'Year')
    ->SetCellValue('E4', 'Final Performance(%)');
// 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:E4')->applyFromArray($headerStyle);
function convertToHoursMins($time, $format = '%02d:%02d') {
    if ($time < 1) {
        return;
    }
    $hours = floor($time / 60);
    $minutes = ($time % 60);
    return sprintf($format, $hours, $minutes);
}
// Add data
$sql_querys = "SELECT * FROM pm_monthly_performance WHERE cm_id=" . mysql_real_escape_string($_REQUEST['cmIds']) . " and month='" . $_REQUEST['months'] . "' and year='" . $_REQUEST['years'] . "'";
$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'];
    $final_performance = round($val_query['performance_percentage']);

    $objPHPExcel->setActiveSheetIndex(0)
        ->SetCellValue('A' . $rowCount, $i)
        ->SetCellValue('B' . $rowCount, $staff_name)
        ->SetCellValue('C' . $rowCount, ucwords($data_mon['month_name']))
        ->SetCellValue('D' . $rowCount, ucwords($val_query['year']))
        ->SetCellValue('E' . $rowCount, ucwords($final_performance));

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

    // Apply conditional background color based on final performance
    if ($final_performance < 0) {
        $objPHPExcel->getActiveSheet()->getStyle("A$rowCount:E$rowCount")->applyFromArray($redBackgroundStyle);
    } elseif ($final_performance < 50) {
        $objPHPExcel->getActiveSheet()->getStyle("A$rowCount:E$rowCount")->applyFromArray($yellowBackgroundStyle);
    } else {
        $objPHPExcel->getActiveSheet()->getStyle("A$rowCount:E$rowCount")->applyFromArray($lightBackgroundStyle);
    }

    $rowCount++;
    $i++;
}

// Adjust column widths
foreach (range('A', 'E') 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="' . $dataBranch['branch_name'] .' '. $data_mon['month_name']. ' '. $_REQUEST['years']. ' Performance.xls"');
header('Cache-Control: max-age=0');

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