<?php
include "../../root.class.php";
require '../../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$call = new call_functions();
$db = new db_safeguard();
if ($_GET['jc_number'] != '') {
    if ($_GET['from_date'] == '' && $_GET['to_date'] == '') {
        $dispatch_res = $db->query('order_trans', "SELECT * FROM order_trans WHERE (job_card_no = '{$_GET['jc_number']}' AND user_id != 0 AND order_id = 0) ORDER BY `record_id` DESC;");
    } else {
        $dispatch_res = $db->query('order_trans', "SELECT * FROM order_trans WHERE ((date_time BETWEEN '{$_GET['from_date']} 00:00' AND '{$_GET['to_date']} 23:59' AND amount < 0) AND (job_card_no = '{$_GET['jc_number']}' AND user_id != 0 AND order_id = 0))  OR ((date_time BETWEEN '{$_GET['from_date']} 00:00' AND '{$_GET['to_date']} 23:59' AND amount > 0) AND (job_card_no = '{$_GET['jc_number']}' AND user_id != 0 AND order_id = 0)) ORDER BY `record_id` DESC;");
    }
} else {

    $dispatch_res = $db->query('order_trans', "SELECT * FROM order_trans WHERE ((date_time BETWEEN '{$_GET['from_date']} 00:00' AND '{$_GET['to_date']} 23:59' AND amount < 0) AND (job_card_no != '' AND user_id != 0 AND order_id = 0))  OR ((date_time BETWEEN '{$_GET['from_date']} 00:00' AND '{$_GET['to_date']} 23:59' AND amount > 0) AND (job_card_no != '' AND user_id != 0 AND order_id = 0)) ORDER BY `record_id` DESC;");
    
}

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Add headers
$sheet->fromArray(['Date', 'Item EA', 'Amount', 'Disp/Rec. By', 'Project Manager', 'Job Card', 'Job Card Name', "Disp/Rec To."], NULL, 'A1');

// Fetch data and add to spreadsheet
$row = 2;
while ($dispatch = $dispatch_res->fetch_assoc()) {
    $item_res = $db->query("stock", "SELECT * FROM stock WHERE record_id = {$dispatch['stock_id']}");
    $item = $item_res->fetch_assoc();

    // check if job card is on project job card:
    $job_card_project_res = $db->query("job_cards", "SELECT * FROM job_cards WHERE jc_number = '{$dispatch['job_card_no']}'");
    $job_card_tech_res = $db->query("job_cards_tech", "SELECT * FROM job_cards_tech WHERE jc_number ='{$dispatch['job_card_no']}'");
    $job_card_acc_res = $db->query("job_card_accounts", "SELECT * FROM job_card_accounts WHERE `name` = '{$dispatch['job_card_no']}'");
    if ($job_card_project_res->num_rows > 0) {
        $job_card = $job_card_project_res->fetch_assoc();
        $job_card_name = $job_card["project_name"];
        $account_manager = $call->get_username($job_card['account_manager_id']);
    } else if ($job_card_tech_res->num_rows > 0) {
        $job_card = $job_card_tech_res->fetch_assoc();
        $job_card_name = $job_card["company_name"];
        $account_manager = $call->get_username($job_card['user_to_do']);
    } else if ($job_card_acc_res->num_rows > 0) {
        $job_card = $job_card_acc_res->fetch_assoc();
        $job_card_name = $job_card["description"];
        $account_manager = $call->get_username($job_card['account_manager_id']);
    } else {
        $job_card_name = "N/A";
        $account_manager = "N/A";
    }

    $sheet->fromArray([
        $dispatch['date_time'],
        $item['description'],
        $dispatch['amount'],
        $call->get_username($dispatch['user_id']),
        $account_manager,
        $dispatch['job_card_no'],
        $job_card_name,
        $dispatch['received']

    ], NULL, 'A' . $row++);
}

// Output as Excel file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="dispatch_report.xlsx"');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');