<?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();
$jobcard_account_res = $db->query("job_cards_tech", "SELECT * FROM job_cards_tech WHERE `record_id` = '{$_GET['record_id']}'");
$jobcard_account = $jobcard_account_res->fetch_assoc();
$job_card_name = $jobcard_account["jc_number"];
$account_manager = $call->get_username($jobcard_account['user_id']);


$dispatch_res = $db->query('order_trans', "SELECT 
    stock_id,
    SUM(amount) AS total_amount,date_time,user_id,job_card_no,received,signature
FROM 
    order_trans
WHERE 
    job_card_no = '$jobcard_account[jc_number]'
GROUP BY 
    stock_id
HAVING 
    total_amount <> 0
ORDER BY 
    stock_id ASC;");


$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Add headers
$sheet->fromArray(['JC NUMBER', 'JC NAME', 'CATEGORY', 'SOURCE FORM', 'TOTAL COST (ex)', 'QUANTITY', 'DATE OF ENTRY', "PROJECT MANAGER", 'DESCRIPTION', 'RECEIVED'], NULL, "A1");

// Fetch data and add to spreadsheet
$row = 2;
$total = 0;
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();

    if ($item['category_id'] != -1) {
        $category_res = $db->query("stock_categories", "SELECT * FROM stock_categories WHERE record_id = {$item['category_id']}");
        $category = $category_res->fetch_assoc();
    } else {
        $category['name'] = "N/A";
    }


    $form = "DISPATCH";
    $amount = $dispatch['total_amount'] * -1;
    $price = $item['cost_price'] * $amount;

    $total += $price;
    $sheet->fromArray([
        $job_card_name,
        $jobcard_account['company_name'],
        $category['name'],
        $form,
        $price,
        $amount,
        $dispatch['date_time'],
        $account_manager,
        $item['description'],
        $dispatch['received']

    ], NULL, 'A' . $row++);
}

$sheet->fromArray([
    "",
    "",
    "",
    "",
    "=SUM(E2:E" . ($row - 1) . ")",
    "",
    "",
    "",
    ""

], NULL, 'A' . $row++);

$sheet->fromArray([
    "",
    "",
    "",
    "LABOUR",
    "0",
    "",
    "",
    "",
    ""

], NULL, 'A' . $row++);


$sheet->fromArray([
    "",
    "",
    "",
    "INVOICED",
    "0",
    "",
    "",
    "",
    ""

], NULL, 'A' . $row++);


$sheet->fromArray([
    "",
    "",
    "",
    "PROFIT",
    "= E" . ($row - 1) . " - (E" . ($row - 2) . "+E" . ($row - 3) . ")",
    "",
    "",
    "",
    ""

], NULL, 'A' . $row++);



// $sheet->fromArray([
//     "",
//     "",
//     "",
//     "",
//     "",
//     "",
//     "",
//     "",
//     ""

// ], NULL, 'A' . $row++);

// $sheet->fromArray([
//     "PURCHASE ORDERS",
//     "",
//     "",
//     "",
//     "",
//     "",
//     "",
//     "",
//     ""

// ], NULL, 'A' . $row++);



// $sheet->fromArray([
//     "PO",
//     "JOB CARD",
//     "STATUS",
//     "REFERENCE",
//     "COST EX",
//     "COST INC",
//     "",
//     "",
//     ""

// ], NULL, 'A' . $row++);


// $po_res = $db->query('orders', "SELECT * FROM orders WHERE jc_number = '$jobcard_account[jc_number]'");
// $po_lines_index = 1;
// while ($po = $po_res->fetch_assoc()) {

//     $order_items_res = $db->query("order_items", "SELECT * FROM order_items WHERE order_id = {$po['record_id']}");
//     $total = 0;
//     while ($order_item = $order_items_res->fetch_assoc()) {
//         $total += $order_item['quantity'] * $order_item['purchase_price_ex'];
//     }
//     if ($po['status'] == 0) {
//         $status = "OPEN";
//     } else if ($po['status'] == 1) {
//         $status = "CLOSED";
//     }
//     $total_inc = $total * 1.15;
//     $sheet->fromArray([
//         $po['po_number'],
//         $po['jc_number'],
//         $status,
//         $po['reference'],
//         $total,
//         $total_inc,
//         "",
//         "",
//         ""

//     ], NULL, 'A' . $row++);
//     $po_lines_index++;
// }
// Output as Excel file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$date = date('Y_m_d');
header('Content-Disposition: attachment; filename="' . $job_card_name .'_'. $date.'.xlsx"');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');