<?php
session_start();
require('classes/db.class.php');
// Load PhpSpreadsheet classes
require 'vendor/autoload.php'; // Automatically load the PhpSpreadsheet library

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


if (isset($_POST['date_from'])) {
    $dates = " date_time_opened BETWEEN '{$_POST['date_from']}' AND '{$_POST['date_to']}'";
}

function get_letter($num)
{
    $letters = range('A', 'Z');
    $columnName = '';

    while ($num > 0) {
        // Adjust for zero-based index (Excel columns start at 1, not 0)
        $num--;
        $columnName = $letters[$num % 26] . $columnName;
        $num = (int)($num / 26);
    }

    return $columnName;
}


$where_data = $_POST['where_data'];

// try {
$db = new db();
$db_2 = new db('fuel');
// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Set the active sheet
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Date');
$sheet->setCellValue('B1', 'Diesel Purchases');
$sheet->setCellValue('C1', 'Odo Reading/ Hours');
$sheet->setCellValue('A2', 'Tank Capacity');
$sheet->setCellValue('B2', '9000 lt');
$sheet->setCellValue('A6', 'FSP Diesel / AR Bulk');
$sheet->mergeCells('A6:A7');
$sheet->setCellValue('A8', 'Dip reading #start');
$sheet->mergeCells('A8:A9');
$sheet->setCellValue('A10', 'Pump Reading #start');
$sheet->mergeCells('A10:A11');
$sheet->setCellValue('A12', 'O/Ballance Diff');
$sheet->mergeCells('A12:A13');
$sheet->setCellValue('B8', '0');
$sheet->setCellValue('B6', '0');
$sheet->setCellValue('B10', '0');


$leter_index = 4;
$lowers_row_arr = [];


// echo "SELECT DISTINCT asset_id WHERE {$_POST['where_data']}";
$reuslts = $db_2->query("SELECT DISTINCT asset_id FROM `fuel_movement` WHERE {$_POST['where_data']}");
while ($tank = $reuslts->fetch_assoc()) {
    $asset_id = str_replace("1_", "", $tank['asset_id']);
    $asset_res = $db->exec_query('assets', ['*'], '', '', '', '', "record_id = $asset_id");
    $asset = $asset_res->fetch_assoc();
    $letter = get_letter($leter_index);
    $sheet->setCellValue("$letter" . '1', $asset['description']);
    // echo $asset['description'];
    $row_index = 2;
    $fuel_res = $db_2->query("SELECT * FROM `fuel_movement` WHERE {$_POST['where_data']} AND asset_id = '1_{$asset_id}'");
    while ($fuel = $fuel_res->fetch_assoc()) {
        $total_used = $total_used + $fuel['amount'];
        $sheet->setCellValue("$letter" . "$row_index", round($fuel['amount'] / 1000, 2));
        $row_index++;
        $over_all_total = $over_all_total + $fuel['amount'];
    }
    $lowers_row_arr[] = $row_index;
    $totals_used[] = $total_used;
    $total_used = 0;
    $leter_index++;
}
$sheet->setCellValue("A" . max($lowers_row_arr), "Total Literes Issued");
$sheet->setCellValue(get_letter(2) . max($lowers_row_arr), round(($over_all_total / 1000), 2));
$nex_row_index = 4;
foreach ($totals_used as $key => $value) {
    $sheet->setCellValue(get_letter($nex_row_index) . max($lowers_row_arr), round(($value / 1000), 2));
    $nex_row_index++;
}

// $sheet->setCellValue('A' . (max($lowers_row_arr) + 4), 'Pump Reading #end');
// $sheet->mergeCells('A' . (max($lowers_row_arr) + 4) . ':A' . (max($lowers_row_arr) + 5) . '');
// $sheet->setCellValue('B' . (max($lowers_row_arr) + 4), '0');

// $sheet->setCellValue('A' . (max($lowers_row_arr) + 6), 'Dip Reading #end');
// $sheet->mergeCells('A' . (max($lowers_row_arr) + 6) . ':A' . (max($lowers_row_arr) + 7) . '');
// $sheet->setCellValue('B' . (max($lowers_row_arr) + 6), '0');


// $sheet->setCellValue('F' . (max($lowers_row_arr) + 6), 'Pump Reading diffeerence');
// $sheet->setCellValue('F' . (max($lowers_row_arr) + 7), 'Total It Issued Difference');
// $sheet->setCellValue('F' . (max($lowers_row_arr) + 7), round(($over_all_total / 1000), 2));
// $sheet->setCellValue('J' . (max($lowers_row_arr) + 6), "Pump reading total should be the total of the litres issued per month");
// $sheet->setCellValue('J' . (max($lowers_row_arr) + 7), "Total litres issued extra according to the actual pump readings");

// $sheet->setCellValue('F' . (max($lowers_row_arr) + 7), "Dip reading difference");
// $sheet->setCellValue('F' . (max($lowers_row_arr) + 7), "System diesel balance after journals");
// $sheet->setCellValue('I' . (max($lowers_row_arr) + 7), "=+B8+B6-" . (get_letter(2) . max($lowers_row_arr)) . "-B" . 'A' . (max($lowers_row_arr) + 6));
// $sheet->setCellValue('J' . (max($lowers_row_arr) + 7), "Total litres short according to the actual dip readings");
// $sheet->setCellValue('J' . (max($lowers_row_arr) + 7), "Pastel closing balance");


// Create a Writer object to save the Excel file as .xlsx
$writer = new Xlsx($spreadsheet);

// Specify the path and filename
$filename = 'fuel_report.xlsx';

// Save the file to the server
$writer->save($filename);

// Provide feedback to the user
echo "Excel file created successfully! Download here: <a href='$filename' onclick='exxii()'>$filename</a>";
?>
<script>
    function exxii() {
        setTimeout(function() {
            window.location.href = 'reports_home.php';
        }, 1000);
    }
</script>
<?
// } catch (Exception $e) {
//     echo "Error: " . $e->getMessage();
// }