<?php
require('classes/fpdf.php');
require('classes/db.class.php');

// if (isset($_GET['structure_id'])) {

function get_asset_id($asset_id)
{
    if ($asset_id == "NONE") {
    } else {

        $db = new db();

        $res = $db->exec_query('assets', ['*'], '', '', '', '', "record_id = $asset_id");
        if ($res) {
            $values = $res->fetch_assoc();

            return $values['fleet_no'];
        } else {
            return "NONE";
        }
    }
}

$db = new db();
$where_data = str_replace('|', "'", $_POST['where_data']);

$jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', $where_data, 'ORDER BY record_id DESC');
$jobcard_totals["TOTAL"] = $jobcards_res->num_rows;

$jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', "job_card_status = 'CLOSED' AND " . $where_data, 'ORDER BY record_id DESC');
$jobcard_totals["CLOSED"] = $jobcards_res->num_rows;

$jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', "job_card_status = 'OPEN' AND " . $where_data, 'ORDER BY record_id DESC');
$jobcard_totals["OPEN"] = $jobcards_res->num_rows;

$jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', "job_card_status = 'AWAITING PARTS' AND " . $where_data, 'ORDER BY record_id DESC');
$jobcard_totals["AWAITING PARTS"] = $jobcards_res->num_rows;

$job_card_types_res = $db->exec_query('jobcard_types', ['*']);
// $jobcard_totals = [];
while ($jobcard_types = $job_card_types_res->fetch_assoc()) {
    // echo '<br>'.$jobcard_types['name'];

    $jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', "job_card_type = '{$jobcard_types['name']}' AND " . $where_data, 'ORDER BY record_id DESC');
    $jobcard_totals["{$jobcard_types['name']}"] = $jobcards_res->num_rows;
}

$most_num = [];
$most_asset = [];
$least_num = [];
$least_asset = [];

$asset_arrray_res = $db->query("SELECT DISTINCT asset_id FROM job_cards WHERE $where_data");
while ($assets = $asset_arrray_res->fetch_assoc()) {
    // echo $assets['asset_id'];
    $jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', "asset_id = {$assets['asset_id']} AND " . $where_data, 'ORDER BY record_id DESC');
    if (strlen($most_num["TOTAL"]) > 0) {
        if ($most_num["TOTAL"] < $jobcards_res->num_rows) {
            $most_num["TOTAL"] = $jobcards_res->num_rows;
            $most_asset["TOTAL"] = $assets['asset_id'];
        }
        if ($least_num["TOTAL"] > $jobcards_res->num_rows) {
            $least_num["TOTAL"] = $jobcards_res->num_rows;
            $least_asset["TOTAL"] = $assets['asset_id'];
        }
    } else {
        $most_num["TOTAL"] = 0;
        $most_asset["TOTAL"] = "NONE";
        $least_num["TOTAL"] = 0;
        $least_asset["TOTAL"] = "NONE";
    }
    $jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', "asset_id = {$assets['asset_id']} AND job_card_status = 'CLOSED' AND " . $where_data, 'ORDER BY record_id DESC');

    if (strlen($most_num["CLOSED"]) > 0) {
        if ($most_num["CLOSED"] < $jobcards_res->num_rows) {
            $most_num["CLOSED"] = $jobcards_res->num_rows;
            $most_asset["CLOSED"] = $assets['asset_id'];
        }
        if ($least_num["CLOSED"] > $jobcards_res->num_rows) {
            $least_num["CLOSED"] = $jobcards_res->num_rows;
            $least_asset["CLOSED"] = $assets['asset_id'];
        }
    } else {
        $most_num["CLOSED"] = 0;
        $most_asset["CLOSED"] = "NONE";
        $least_num["CLOSED"] = 0;
        $least_asset["CLOSED"] = "NONE";
    }

    $jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', "asset_id = {$assets['asset_id']} AND job_card_status = 'OPEN' AND " . $where_data, 'ORDER BY record_id DESC');
    if (strlen($most_num["OPEN"]) > 0) {
        if ($most_num["OPEN"] < $jobcards_res->num_rows) {
            $most_num["OPEN"] = $jobcards_res->num_rows;
            $most_asset["OPEN"] = $assets['asset_id'];
        }
        if ($least_num["OPEN"] > $jobcards_res->num_rows) {
            $least_num["OPEN"] = $jobcards_res->num_rows;
            $least_asset["OPEN"] = $assets['asset_id'];
        }
    } else {
        $most_num["OPEN"] = 0;
        $most_asset["OPEN"] = "NONE";
        $least_num["OPEN"] = 0;
        $least_asset["OPEN"] = "NONE";
    }

    $jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', "asset_id = {$assets['asset_id']} AND job_card_status = 'AWAITING PARTS' AND " . $where_data, 'ORDER BY record_id DESC');
    if (strlen($most_num["AWAITING PARTS"]) > 0) {
        if ($most_num["AWAITING PARTS"] < $jobcards_res->num_rows) {
            $most_num["AWAITING PARTS"] = $jobcards_res->num_rows;
            $most_asset["AWAITING PARTS"] = $assets['asset_id'];
        }
        if ($least_num["AWAITING PARTS"] > $jobcards_res->num_rows) {
            $least_num["AWAITING PARTS"] = $jobcards_res->num_rows;
            $least_asset["AWAITING PARTS"] = $assets['asset_id'];
        }
    } else {
        $most_num["AWAITING PARTS"] = 0;
        $most_asset["AWAITING PARTS"] = "NONE";
        $least_num["AWAITING PARTS"] = 0;
        $least_asset["AWAITING PARTS"] = "NONE";
    }


    $job_card_types_res = $db->exec_query('jobcard_types', ['*']);
    // $jobcard_totals = [];
    while ($jobcard_types = $job_card_types_res->fetch_assoc()) {
        // echo '<br>'.$jobcard_types['name'];

        $jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', "asset_id = {$assets['asset_id']} AND job_card_type = '{$jobcard_types['name']}' AND " . $where_data, 'ORDER BY record_id DESC');
        if (strlen($most_num["{$jobcard_types['name']}"]) > 0) {
            if ($most_num["{$jobcard_types['name']}"] < $jobcards_res->num_rows) {
                $most_num["{$jobcard_types['name']}"] = $jobcards_res->num_rows;
                $most_asset["{$jobcard_types['name']}"] = $assets['asset_id'];
            }
            if ($least_num["{$jobcard_types['name']}"] > $jobcards_res->num_rows) {
                $least_num["{$jobcard_types['name']}"] = $jobcards_res->num_rows;
                $least_asset["{$jobcard_types['name']}"] = $assets['asset_id'];
            }
        } else {
            $most_num["{$jobcard_types['name']}"] = 0;
            $most_asset["{$jobcard_types['name']}"] = "NONE";
            $least_num["{$jobcard_types['name']}"] = 0;
            $least_asset["{$jobcard_types['name']}"] = "NONE";
        }
    }
}

// var_dump($least_asset);
// var_dump($least_num);


$pdf = new FPDF();
$pdf->AliasNbPages();

$pdf->AddPage('L');
$pdf->Image('images/logo_1.png', 255, 2, 40);

// Arial bold 15
$pdf->SetFont('Arial', 'B', 8);
// Move to the right

$pdf->Cell(20);
$pdf->Cell(60, -4, '', 0, 1, 'C');

$header = [75, 160];
// Title
$pdf->SetFont('Arial', 'B', 20);
$pdf->Cell(1);
$pdf->Cell($header[0], 15, 'JOB CARD REPORT', "BR", 0, 'L');
$pdf->SetFont('Arial', '', 20);

if (strlen($_POST['date_from']) > 2) {
    $pdf->Cell($header[1], 15,  " {$_POST['date_type']} : {$_POST['date_from']}   TO   {$_POST['date_to']}", "T", 1, 'L');
} else {
    $pdf->Cell($header[1], 15, 'ALL', "T", 1, 'C');
}
$pdf->SetFont('Arial', 'B', 8);

$pdf->Cell($header[0], 10, "", "", 1, 'L');

// $pdf->Line(11, 22, 290, 22);

$totals_table = [30];
$index = 0;
$numbers = 0;
$y = 28;

foreach ($jobcard_totals as $key => $value) {
    $move = 5;
    if (($index == 6)) {
        $numbers = 0;
        $y = 75;
    } elseif ($index == 12) {
        $numbers = 0;
        $y = 130;
    }
    $pdf->SetXY(10 + ($numbers * 45), $y);
    $pdf->Cell($totals_table[0], 5, $key, "B", 1, 'C');


    $pdf->SetXY(10 + ($numbers * 45), $y + $move);
    $pdf->Cell($totals_table[0], 5, $value, "", 1, 'C');


    $move = $move + 5;
    $pdf->SetXY(10 + ($numbers * 45), $y + $move);
    $pdf->Cell($totals_table[0], 5, "MOST", "TLR", 1, 'C');

    $move = $move + 5;
    $pdf->SetXY(10 + ($numbers * 45), $y + $move);
    $pdf->Cell($totals_table[0], 5, $most_num[$key], "LR", 1, 'C');

    $move = $move + 5;
    $pdf->SetXY(10 + ($numbers * 45), $y + $move);
    $pdf->Cell($totals_table[0], 5, get_asset_id($most_asset[$key]), "LRB", 1, 'C');
    $index++;
    $numbers++;
}
$pdf->AddPage('L');

$table_top = [10, 32, 5, 28, 50, 265];
$pdf->Cell(1);
$pdf->Cell($table_top[2], 5, "JC", "LBR", 0, 'L');
$pdf->Cell($table_top[3], 5, "STATUS", "BR", 0, 'l');
$pdf->Cell($table_top[3], 5, "DATE OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[1], 5, "USER OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[3], 5, 'DATE CLOSED', "BR", 0, 'l');
$pdf->Cell($table_top[1], 5, "USER CLOSED", "BR", 0, 'l');
$pdf->Cell($table_top[1], 5, "ASSET ID", "BR", 0, 'l');
$pdf->Cell($table_top[1], 5, "JOBCARD TYPE", "BR", 0, 'l');
$pdf->Cell($table_top[4], 5, "DESCRIPTION", "BR", 1, 'l');


$jobcards_res = $db->exec_query('job_cards', ['*'], '', '', '', '', $where_data, 'ORDER BY record_id DESC');
$total_jobcards = 0;
$total_open = 0;
$total_closed = 0;
$total_waiting = 0;
while ($jobcard_info = $jobcards_res->fetch_assoc()) {
    $asset_info_res = $db->exec_query('assets', ['*'], '', '', '', '', "record_id = {$jobcard_info['asset_id']}");
    $asset_info = $asset_info_res->fetch_assoc();
    $user_info_res = $db->exec_query('users', ['*'], '', '', '', '', "record_id = {$jobcard_info['user_id_opened']}");
    $user_info = $user_info_res->fetch_assoc();
    $pdf->SetFont('Arial', 'B', 6);

    $pdf->Cell(1);
    $pdf->Cell($table_top[2], 6, $jobcard_info['record_id'], "", 0, 'L');
    $pdf->Cell($table_top[3], 6, $jobcard_info['job_card_status'], "", 0, 'l');
    $pdf->Cell($table_top[3], 6, $jobcard_info['date_time_opened'], "", 0, 'l');
    $pdf->Cell($table_top[1], 6, $user_info['username'], "", 0, 'l');
    $pdf->Cell($table_top[3], 6, $jobcard_info['date_time_closed'], "", 0, 'l');
    $pdf->Cell($table_top[1], 6, $user_info['username'], "", 0, 'l');
    $pdf->Cell($table_top[1], 6, $asset_info['fleet_no'], "", 0, 'l');
    $pdf->Cell($table_top[1], 6, $jobcard_info['job_card_type'], "", 0, 'l');
    $pdf->Cell($table_top[4], 6, $asset_info['description'], "", 1, 'l');
    if (strlen($jobcard_info['parts']) > 2) {
        $pdf->MultiCell($table_top[5], 13, "PARTS: ".$jobcard_info['parts'], "",'l');
    }
    if (strlen($jobcard_info['notes']) > 2) {
        $pdf->MultiCell($table_top[5], 13, "NOTES: ".$jobcard_info['notes'], "B", 'l');
    } else {
        $pdf->Cell($table_top[5], 5, '', "B", 1, 'l');
    }
    $pdf->SetFont('Arial', 'B', 8);
}


$pdf->Output("I");

    // echo "Structure?";