<?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();

$dispatch_res = $db->query('order_trans', "SELECT * FROM order_trans LEFT JOIN stock ON order_trans.stock_id = stock.record_id WHERE (((order_trans.date_time BETWEEN '{$_GET['from_date']} 00:00' AND '{$_GET['to_date']}  23:59' AND order_trans.amount < 0) AND (order_trans.job_card_no != '' AND order_trans.user_id != 0 AND order_trans.order_id = 0))  OR ((order_trans.date_time BETWEEN '{$_GET['from_date']} 00:00' AND '{$_GET['to_date']}  23:59' AND order_trans.amount > 0) AND (order_trans.job_card_no != '' AND order_trans.user_id != 0 AND order_trans.order_id = 0))) AND stock.item_code = 'TECSPA' ORDER BY `order_trans`.`record_id` DESC;");



$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Add headers
$sheet->fromArray(['Date', 'Item EA','Item Code', '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'],
        $item['item_code'],
        $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');