<?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 DISTINCT order_trans.stock_id as stock_id FROM order_trans LEFT JOIN stock ON order_trans.stock_id = stock.record_id WHERE included_in_value_report = 1 ORDER BY `description` ASC");

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

$sheet->fromArray(['Valueble Item Report'], NULL, 'A1');
$sheet->fromArray(["Job Card Number: {$_POST['jc_number']}", "Date:" . date("Y-m-d H:i")], NULL, 'A2');
$sheet->fromArray(["Item", "Amount", "Disp/Rec. By", "Project Manager", "Job Card", "Job Card Name", "Disp/Rec To."], NULL, 'A3');

while ($dispatch_items = $dispatch_res->fetch_assoc()) {
    $stock_id = $dispatch_items['stock_id'];

    $dispatch_amount = $db->query("order_trans", "SELECT SUM(amount) as amount, job_card_no, user_id, received FROM order_trans WHERE stock_id = $stock_id AND job_card_no = {$_POST['jc_number']}");
    $dispatch = $dispatch_amount->fetch_assoc();

    $item_res = $db->query("stock", "SELECT * FROM stock WHERE record_id = $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([
        $item['description'],
        $dispatch['amount'],
        $call->get_username($dispatch['user_id']),
        $account_manager,
        $dispatch['job_card_no'],
        $job_card_name,
        $dispatch['received']

    ], 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="stock_take.xlsx"');

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