<?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();

// 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";
}


$dispatch_res = $db->query('order_trans', "SELECT ot.amount as amount, s.description as discription,o.po_number as po_number,o.jc_number as jc_number,ot.job_card_no as job_card,ot.date_time as date_time,ot.user_id as user_id,ot.received as received ,ot.contractor as contractor FROM order_trans as ot LEFT JOIN orders as o ON o.record_id = ot.order_id LEFT JOIN stock as s ON s.record_id = ot.stock_id WHERE s.item_code = 'TECSPA'");

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$row = 3;

$sheet->fromArray(['TECSPA Item Movement Report'], NULL, 'A1');

$sheet->fromArray(["Amount", "Item", "PO Number", "PO JC", "Disp JC", "Date Time", "User", "Received", "Contractor"], NULL, 'A2');

while ($dispatch_items = $dispatch_res->fetch_assoc()) {


    $sheet->fromArray([
        $dispatch_items['amount'],
        $dispatch_items['discription'],
        $dispatch_items['po_number'],
        $dispatch_items['jc_number'],
        $dispatch_items['job_card'],
        $dispatch_items['date_time'],
        $call->get_username($dispatch_items['user_id']),
        $dispatch_items['received'],
        $dispatch_items['contractor']

    ], NULL, 'A' . $row++);

}

// Add headers

// Fetch data and add to spreadsheet


// Output as Excel file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="tecspa_tran_report.xlsx"');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');