<?php
include '../../fpdf.php';
include "../../root.class.php";
$call = new call_functions();


$db = new db_safeguard();
$jobcard_res = $db->query("job_cards", "SELECT * FROM job_cards WHERE record_id = {$_GET['record_id']}");
$jobcard = $jobcard_res->fetch_assoc();
function remove_html_tags($text)
{
    $description = strip_tags($text);
    $description = str_replace("\n", "\r\n", $description);
    $description = preg_replace('/\s\s+/', ' ', $description);
    $description = preg_replace('/\n\n+/', "\n", $description);
    return $description;
}

$pdf = new FPDF();
$pdf->AliasNbPages();
$pdf->AddPage('P');


$pdf->Image('../../logo.png', 10, 10, 30);
$pdf->SetFont('Arial', 'B', 6);
$pdf->cell(35);
$pdf->Cell(115, 5, "MIDRAND AIR SERVICES (PTY) LTD", 0, 0, 'L');
$pdf->Cell(35, 5, "REG NO: 2018/573291/07", 0, 1, 'R');
$pdf->cell(35);

$pdf->Cell(115, 5, "PO BOX 915", 0, 0, 'L');
$pdf->Cell(35, 5, "VAT NO: 4860287269", 0, 1, 'R');
$pdf->cell(35);

$pdf->Cell(115, 5, "IRENE, 0062", 0, 0, 'L');
$pdf->Cell(35, 5, "PHONE: 012 661 2211", 0, 1, 'R');
$pdf->cell(35);

$pdf->Cell(115, 5, "", 0, 0, 'L');
$pdf->Cell(35, 5, "FAX: 012 661 2212", 0, 1, 'R');
$pdf->Cell(115, 15, "", 0, 1, 'L');
/// header end

$pdf->cell(35);
$pdf->SetFont('Arial', 'B', 15);
$pdf->Cell(120, 10, $jobcard['jc_number'] . " : " . $jobcard['project_name'] . " REPORT", 0, 1, 'C');
$pdf->Cell(120, 5, "", 0, 1, 'C');
$pdf->SetFont('Arial', '', 6);
$pdf->cell(28);
$pdf->Cell(30, 10, "MANAGER", 1, 0, 'L');
$pdf->SetFont('Arial', 'B', 6);
$pdf->Cell(45, 10, $call->get_username($jobcard['account_manager_id']), 1, 0, 'L');
$pdf->SetFont('Arial', '', 6);
$pdf->Cell(30, 10, "STATUS", 1, 0, 'L');
$pdf->SetFont('Arial', 'B', 6);
$status = ($jobcard['status'] == 1) ? 'ACTIVE' : 'INACTIVE';
$pdf->SetFont('Arial', 'B', 6);
$pdf->Cell(35, 10, $status, 1, 1, 'L');

$pdf->SetFont('Arial', 'B', 15);
$pdf->Cell(120, 5, "", 0, 1, 'C');
$pdf->Cell(120, 10, "DISPATCHED", "B", 1, 'L');
$pdf->Cell(120, 5, "", 0, 1, 'C');
$pdf->SetFont('Arial', '', 8);
// get all dispatched items under this jobcard number

$dispatched_res = $db->query("order_trans", "SELECT * FROM order_trans WHERE job_card_no = '{$jobcard['jc_number']}' AND amount < 0 ORDER BY record_id DESC");
// echo"SELECT * FROM order_trans WHERE job_card_no = '{$jobcard['name']}' AND amount < 0 ORDER BY record_id DESC";
// headings
$pdf->Cell(25, 10, "AMOUNT", "B", 0, 'L');
$pdf->Cell(25, 10, "PRICE EA (ex)", "B", 0, 'L');
$pdf->Cell(25, 10, "TOTAL (ex)", "B", 0, 'L');
$pdf->Cell(25, 10, "DATE", "B", 0, 'L');
$pdf->Cell(25, 10, "USER", "B", 0, 'L');
$pdf->Cell(65, 10, "ITEM", "B", 1, 'L');
$total_dispatched_value = 0;
$total_items = 0;
$pdf->SetFont('Arial', '', 6);

while ($dispatched = $dispatched_res->fetch_assoc()) {

    $pdf->Cell(25, 7, $dispatched['amount'], "B", 0, 'L');
    $total_items += $dispatched['amount'];
    $pdf->Cell(25, 7, "R " . $call->get_stock_price($dispatched['stock_id']), "B", 0, 'L');
    $pdf->Cell(25, 7, "R " . abs($call->get_stock_price($dispatched['stock_id']) * $dispatched['amount']), "B", 0, 'L');
    $total_dispatched_value += abs($call->get_stock_price($dispatched['stock_id']) * $dispatched['amount']);
    $pdf->Cell(25, 7, $dispatched['date_time'], "B", 0, 'L');
    $pdf->Cell(25, 7, $call->get_username($dispatched['user_id']), "B", 0, 'L');
    $pdf->Cell(65, 7, $call->get_stock_name($dispatched['stock_id']), "B", 1, 'L');
    $index_dispatched++;
}
$pdf->SetFont('Arial', '', 8);

$pdf->Cell(25, 7, $total_items, "BT", 0, 'L');
$pdf->Cell(25, 7, "", "BT", 0, 'L');
$pdf->Cell(25, 7, "R " . number_format($total_dispatched_value, 3), "BT", 0, 'L');
$pdf->Cell(25, 7, "", "BT", 0, 'L');
$pdf->Cell(25, 7, "", "BT", 0, 'L');
$pdf->Cell(65, 7, "", "BT", 1, 'L');



$pdf->Cell(120, 10, "", 0, 1, 'C');


$pdf->SetFont('Arial', 'B', 15);
$pdf->Cell(120, 10, "RECEIVED", "B", 1, 'L');
$pdf->Cell(120, 5, "", 0, 1, 'C');
$pdf->SetFont('Arial', '', 6);
// get Purchase Orders Linked to this account
$po_res = $db->query("orders", "SELECT * FROM orders WHERE jc_number = '{$jobcard['jc_number']}'");
if ($po_res->num_rows > 0) {
    $po_ids = [];
    while ($po = $po_res->fetch_assoc()) {
        $po_ids[] = " order_id = " . $po['record_id'];
    }
    // implode into sql search
    $po_ids = implode(" OR ", $po_ids);
    $received_res = $db->query("order_trans", "SELECT * FROM order_trans WHERE ($po_ids) AND amount > 0 AND order_id != 0 ORDER BY record_id DESC");
    // echo"SELECT * FROM order_trans WHERE job_card_no = '{$jobcard['name']}' AND amount < 0 ORDER BY record_id DESC";
// headings
    $pdf->Cell(25, 10, "AMOUNT", "B", 0, 'L');
    $pdf->Cell(25, 10, "PRICE EA", "B", 0, 'L');
    $pdf->Cell(25, 10, "TOTAL", "B", 0, 'L');
    $pdf->Cell(25, 10, "DATE", "B", 0, 'L');
    $pdf->Cell(25, 10, "USER", "B", 0, 'L');
    $pdf->Cell(25, 10, "PO", "B", 0, 'L');
    $pdf->Cell(25, 10, "ITEM", "B", 1, 'L');
    $total_received_value = 0;
    $total_items = 0;
    $pdf->SetFont('Arial', '', 6);

    while ($received = $received_res->fetch_assoc()) {
        $pdf->Cell(25, 7, $received['amount'], "B", 0, 'L');
        $total_items += $received['amount'];
        $pdf->Cell(25, 7, "R " . $call->get_stock_price($received['stock_id']), "B", 0, 'L');
        $pdf->Cell(25, 7, "R " . abs($call->get_stock_price($received['stock_id']) * $received['amount']), "B", 0, 'L');
        $total_received_value += abs($call->get_stock_price($received['stock_id']) * $received['amount']);
        $pdf->Cell(25, 7, $received['date_time'], "B", 0, 'L');
        $pdf->Cell(25, 7, $call->get_username($received['user_id']), "B", 0, 'L');
        $pdf->Cell(25, 7, $call->get_po_number($received['order_id']), "B", 0, 'L');
        $pdf->Cell(25, 7, $call->get_stock_name($received['stock_id']), "B", 1, 'L');
        $index_received++;
    }
    $pdf->SetFont('Arial', '', 8);

    $pdf->Cell(25, 7, $total_items, "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 0, 'L');
    $pdf->Cell(25, 7, "R " . number_format($total_received_value, 3), "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 1, 'L');
    $pdf->Cell(120, 7, "", 0, 1, 'C');
}


$pdf->SetFont('Arial', 'B', 15);
$pdf->Cell(120, 10, "ADDITIOAL COSTS", "B", 1, 'L');
$pdf->Cell(120, 5, "", 0, 1, 'C');
$pdf->SetFont('Arial', '', 8);

$orders_res = $db->query("costs", "SELECT * FROM costs WHERE jc_number = '{$jobcard['jc_number']}' ORDER BY record_id DESC");
if ($orders_res->num_rows > 0) {
    // echo"SELECT * FROM order_trans WHERE job_card_no = '{$jobcard['name']}' AND amount < 0 ORDER BY record_id DESC";
// headings

    $pdf->Cell(25, 10, "NAME", "B", 0, 'L');
    $pdf->Cell(35, 10, "CREATED", "B", 0, 'L');
    $pdf->Cell(25, 10, "DATE", "B", 1, 'L');
    $index_orders = 0;
    while ($orders = $orders_res->fetch_assoc()) {
        $pdf->SetFont('Arial', 'B', 6);

        $pdf->Cell(25, 10, $orders['name'], "B", 0, 'L');
        $pdf->Cell(35, 10, $call->get_username($orders['user_id']), "B", 0, 'L');
        $pdf->Cell(25, 10, $orders['date_time_created'], "B", 1, 'L');
        $pdf->Cell(25, 1, "", "", 1, 'L');
        $pdf->SetFont('Arial', 'B', 6);
        $pdf->Cell(120, 7, "ITEM", "B", 0, 'L');
        $pdf->Cell(18, 7, "PRICE Ea (ex)", "B", 0, 'L');
        $pdf->Cell(18, 7, "QUANTITY", "B", 0, 'L');
        $pdf->Cell(18, 7, "TOTAL (ex)", "B", 1, 'L');

        $pdf->SetFont('Arial', '', 6);
        $order_total = 0;
        $order_items_res = $db->query('cost_items', "SELECT * FROM cost_items WHERE cost_id = '{$orders['record_id']}' ORDER BY record_id ASC");
        while ($order_item = $order_items_res->fetch_assoc()) {
            // get item Name

            $pdf->Cell(120, 7, $order_item['name'], "B", 0, 'L');
            $pdf->Cell(18, 7, "R " . $order_item['purchase_price_ex'], "B", 0, 'L');
            $pdf->Cell(18, 7, $order_item['quantity'], "B", 0, 'L');
            $pdf->Cell(18, 7, "R " . number_format($order_item['quantity'] * $order_item['purchase_price_ex'], 2), "B", 1, 'L');
            $order_total += $order_item['quantity'] * $order_item['purchase_price_ex'];
        }

        $index_orders++;
        $pdf->Cell(25, 5, "", "", 1, 'L');

    }
    $pdf->SetFont('Arial', '', 8);

    $pdf->Cell(25, 7, $index_orders, "BT", 0, 'L');
    $pdf->Cell(25, 7, "TOTAL (ex)", "BT", 0, 'L');
    $pdf->Cell(25, 7, "R " . number_format($order_total, 2), "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 1, 'L');
}




$pdf->SetFont('Arial', 'B', 15);
$pdf->Cell(120, 10, "RETURNS", "B", 1, 'L');
$pdf->Cell(120, 5, "", 0, 1, 'C');
$pdf->SetFont('Arial', '', 6);

$returned_res = $db->query("order_trans", "SELECT * FROM order_trans WHERE job_card_no = '{$jobcard['jc_number']}' AND amount > 0 ORDER BY record_id DESC");
if ($returned_res->num_rows > 0) {
    // echo"SELECT * FROM order_trans WHERE job_card_no = '{$jobcard['name']}' AND amount < 0 ORDER BY record_id DESC";
// headings
    $pdf->Cell(25, 10, "AMOUNT", "B", 0, 'L');
    $pdf->Cell(25, 10, "PRICE EA", "B", 0, 'L');
    $pdf->Cell(25, 10, "TOTAL", "B", 0, 'L');
    $pdf->Cell(25, 10, "DATE", "B", 0, 'L');
    $pdf->Cell(25, 10, "USER", "B", 0, 'L');
    $pdf->Cell(25, 10, "ITEM", "B", 1, 'L');
    $total_returned_value = 0;
    $total_items = 0;
    $pdf->SetFont('Arial', '', 6);

    while ($returned = $returned_res->fetch_assoc()) {
        $pdf->Cell(25, 7, $returned['amount'], "B", 0, 'L');
        $total_items += $returned['amount'];
        $pdf->Cell(25, 7, "R " . $call->get_stock_price($returned['stock_id']), "B", 0, 'L');
        $pdf->Cell(25, 7, "R " . abs($call->get_stock_price($returned['stock_id']) * $returned['amount']), "B", 0, 'L');
        $total_returned_value += abs($call->get_stock_price($returned['stock_id']) * $returned['amount']);
        $pdf->Cell(25, 7, $returned['date_time'], "B", 0, 'L');
        $pdf->Cell(25, 7, $call->get_username($returned['user_id']), "B", 0, 'L');
        $pdf->Cell(25, 7, $call->get_stock_name($returned['stock_id']), "B", 1, 'L');
        $index_returned++;
    }
    $pdf->SetFont('Arial', '', 8);

    $pdf->Cell(25, 7, $total_items, "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 0, 'L');
    $pdf->Cell(25, 7, "R " . number_format($total_returned_value, 3), "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 0, 'L');
    $pdf->Cell(25, 7, "", "BT", 1, 'L');
}





$pdf->Output("I");
