<?php
require('classes/fpdf.php');
require('classes/db.class.php');

$db = new db();

$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');
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['description'];
        } else {
            return "NONE";
        }
    }
}

function get_username($user_id)
{
    $db = new db();
    $user_info_res = $db->exec_query('users', ['*'], '', '', '', '', "record_id = $user_id");
    $user_info = $user_info_res->fetch_assoc();
    return $user_info['username'];
}

$header = [100, 160];
// Title
$pdf->SetFont('Arial', 'B', 20);
$pdf->Cell(1);
$pdf->Cell($header[0], 15, 'JOB CARD AGE ANALYSIS', "B", 1, 'L');
$pdf->SetFont('Arial', '', 20);
$pdf->Cell($header[0], 15, '', "", 1, 'L');

$current_date = date("Y-m-d H:i");
$open_jc_res = $db->exec_query("job_cards", ["*"], '', '', '', '', "date_time_closed = '' AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 15 DAY)) <= UNIX_TIMESTAMP(date_time_created) AND UNIX_TIMESTAMP(date_time_created) <= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY))");
$pdf->SetFont('Arial', 'B', 15);
$pdf->Cell(1);
$pdf->Cell($header[0], 15, '1-15 days old ', "B", 1, 'L');
$pdf->Cell($header[0], 15, 'Total: ' . $open_jc_res->num_rows, "B", 1, 'L');

$pdf->SetFont('Arial', '', 15);
$pdf->Cell($header[0], 15, '', "", 1, 'L');

// $pdf->Line(11, 22, 290, 22);
$table_top = [15, 32, 32, 28, 32, 32, 100];
$pdf->SetFont('Arial', '', 7);
$pdf->Cell(1);
$pdf->Cell($table_top[0], 5, "J.C.", "LBR", 0, 'L');
$pdf->Cell($table_top[1], 5, "STATUS", "BR", 0, 'l');
$pdf->Cell($table_top[2], 5, "CREATED", "BR", 0, 'l');
$pdf->Cell($table_top[3], 5, "USER CREATED", "BR", 0, 'l');
$pdf->Cell($table_top[4], 5, "OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[5], 5, "USER OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[6], 5, "DESCRIPTION", "BR", 1, 'l');
while ($open_jc = $open_jc_res->fetch_assoc()) {



    $pdf->Cell(1);
    $pdf->Cell($table_top[0], 5, $open_jc['record_id'], "B", 0, 'L');
    $pdf->Cell($table_top[1], 5, $open_jc['job_card_status'], "B", 0, 'l');
    $pdf->Cell($table_top[2], 5, $open_jc['date_time_created'], "B", 0, 'l');
    $pdf->Cell($table_top[3], 5, get_username($open_jc['user']), "B", 0, 'l');
    $pdf->Cell($table_top[4], 5, $open_jc['date_time_opened'], "B", 0, 'l');
    $pdf->Cell($table_top[5], 5, get_username($open_jc['user_id_opened']), "B", 0, 'l');
    $pdf->Cell($table_top[6], 5, get_asset_id($open_jc['asset_id']), "B", 1, 'l');
}
$pdf->AddPage('L');


$current_date = date("Y-m-d H:i");
$open_jc_res = $db->exec_query("job_cards", ["*"], '', '', '', '', "date_time_closed = '' AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) <= UNIX_TIMESTAMP(date_time_created) AND UNIX_TIMESTAMP(date_time_created) <= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 16 DAY))");
$pdf->SetFont('Arial', 'B', 15);
$pdf->Cell(1);
$pdf->Cell($header[0], 15, '16-30 days old ', "B", 1, 'L');
$pdf->Cell($header[0], 15, 'Total: ' . $open_jc_res->num_rows, "B", 1, 'L');

$pdf->SetFont('Arial', '', 15);
$pdf->Cell($header[0], 15, '', "", 1, 'L');

// $pdf->Line(11, 22, 290, 22);
$table_top = [15, 32, 32, 28, 32, 32, 100];
$pdf->SetFont('Arial', '', 7);
$pdf->Cell(1);
$pdf->Cell($table_top[0], 5, "J.C.", "LBR", 0, 'L');
$pdf->Cell($table_top[1], 5, "STATUS", "BR", 0, 'l');
$pdf->Cell($table_top[2], 5, "CREATED", "BR", 0, 'l');
$pdf->Cell($table_top[3], 5, "USER CREATED", "BR", 0, 'l');
$pdf->Cell($table_top[4], 5, "OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[5], 5, "USER OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[6], 5, "DESCRIPTION", "BR", 1, 'l');
while ($open_jc = $open_jc_res->fetch_assoc()) {



    $pdf->Cell(1);
    $pdf->Cell($table_top[0], 5, $open_jc['record_id'], "B", 0, 'L');
    $pdf->Cell($table_top[1], 5, $open_jc['job_card_status'], "B", 0, 'l');
    $pdf->Cell($table_top[2], 5, $open_jc['date_time_created'], "B", 0, 'l');
    $pdf->Cell($table_top[3], 5, get_username($open_jc['user']), "B", 0, 'l');
    $pdf->Cell($table_top[4], 5, $open_jc['date_time_opened'], "B", 0, 'l');
    $pdf->Cell($table_top[5], 5, get_username($open_jc['user_id_opened']), "B", 0, 'l');
    $pdf->Cell($table_top[6], 5, get_asset_id($open_jc['asset_id']), "B", 1, 'l');

}

$pdf->AddPage('L');
$current_date = date("Y-m-d H:i");
$open_jc_res = $db->exec_query("job_cards", ["*"], '', '', '', '', "date_time_closed = '' AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 60 DAY)) <= UNIX_TIMESTAMP(date_time_created) AND UNIX_TIMESTAMP(date_time_created) <= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 31 DAY))");
$pdf->SetFont('Arial', 'B', 15);
$pdf->Cell(1);
$pdf->Cell($header[0], 15, '31-60 days old ', "B", 1, 'L');
$pdf->Cell($header[0], 15, 'Total: ' . $open_jc_res->num_rows, "B", 1, 'L');

$pdf->SetFont('Arial', '', 15);
$pdf->Cell($header[0], 15, '', "", 1, 'L');

// $pdf->Line(11, 22, 290, 22);
$table_top = [15, 32, 32, 28, 32, 32, 100];
$pdf->SetFont('Arial', '', 7);
$pdf->Cell(1);
$pdf->Cell($table_top[0], 5, "J.C.", "LBR", 0, 'L');
$pdf->Cell($table_top[1], 5, "STATUS", "BR", 0, 'l');
$pdf->Cell($table_top[2], 5, "CREATED", "BR", 0, 'l');
$pdf->Cell($table_top[3], 5, "USER CREATED", "BR", 0, 'l');
$pdf->Cell($table_top[4], 5, "OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[5], 5, "USER OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[6], 5, "DESCRIPTION", "BR", 1, 'l');
while ($open_jc = $open_jc_res->fetch_assoc()) {



    $pdf->Cell(1);
    $pdf->Cell($table_top[0], 5, $open_jc['record_id'], "B", 0, 'L');
    $pdf->Cell($table_top[1], 5, $open_jc['job_card_status'], "B", 0, 'l');
    $pdf->Cell($table_top[2], 5, $open_jc['date_time_created'], "B", 0, 'l');
    $pdf->Cell($table_top[3], 5, get_username($open_jc['user']), "B", 0, 'l');
    $pdf->Cell($table_top[4], 5, $open_jc['date_time_opened'], "B", 0, 'l');
    $pdf->Cell($table_top[5], 5, get_username($open_jc['user_id_opened']), "B", 0, 'l');
    $pdf->Cell($table_top[6], 5, get_asset_id($open_jc['asset_id']), "B", 1, 'l');
}


$pdf->AddPage('L');
$current_date = date("Y-m-d H:i");
$open_jc_res = $db->exec_query("job_cards", ["*"], '', '', '', '', "date_time_closed = '' AND UNIX_TIMESTAMP(CURDATE()) - UNIX_TIMESTAMP(date_time_created) > 60*24*60*60");
$pdf->SetFont('Arial', 'B', 15);
$pdf->Cell(1);
$pdf->Cell($header[0], 15, '60+ days old ', "B", 1, 'L');
$pdf->Cell($header[0], 15, 'Total: ' . $open_jc_res->num_rows, "B", 1, 'L');

$pdf->SetFont('Arial', '', 15);
$pdf->Cell($header[0], 15, '', "", 1, 'L');

// $pdf->Line(11, 22, 290, 22);
$table_top = [15, 32, 32, 28, 32, 32, 100];
$pdf->SetFont('Arial', '', 7);
$pdf->Cell(1);
$pdf->Cell($table_top[0], 5, "J.C.", "LBR", 0, 'L');
$pdf->Cell($table_top[1], 5, "STATUS", "BR", 0, 'l');
$pdf->Cell($table_top[2], 5, "CREATED", "BR", 0, 'l');
$pdf->Cell($table_top[3], 5, "USER CREATED", "BR", 0, 'l');
$pdf->Cell($table_top[4], 5, "OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[5], 5, "USER OPENED", "BR", 0, 'l');
$pdf->Cell($table_top[6], 5, "DESCRIPTION", "BR", 1, 'l');
while ($open_jc = $open_jc_res->fetch_assoc()) {



    $pdf->Cell(1);
    $pdf->Cell($table_top[0], 5, $open_jc['record_id'], "B", 0, 'L');
    $pdf->Cell($table_top[1], 5, $open_jc['job_card_status'], "B", 0, 'l');
    $pdf->Cell($table_top[2], 5, $open_jc['date_time_created'], "B", 0, 'l');
    $pdf->Cell($table_top[3], 5, get_username($open_jc['user']), "B", 0, 'l');
    $pdf->Cell($table_top[4], 5, $open_jc['date_time_opened'], "B", 0, 'l');
    $pdf->Cell($table_top[5], 5, get_username($open_jc['user_id_opened']), "B", 0, 'l');
    $pdf->Cell($table_top[6], 5, get_asset_id($open_jc['asset_id']), "B", 1, 'l');
}


$pdf->Output("I");

// echo "Structure?";