<?php
require('../../fpdf.php');
include "../../root.class.php";

$db = new db_safeguard();

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

$batch_no_res = $db->query("batch_data", "SELECT * FROM batch_data WHERE record_id = '{$_GET['record_id']}'");
$batch_no = $batch_no_res->fetch_assoc();

$batch_no = $batch_no['batch_no'];

$jobcard_res = $db->query("lop_jobcard", "SELECT * FROM lop_jobcard WHERE batch_no = '$batch_no'");
$lop_jobcard = $jobcard_res->fetch_assoc();

$pdf->SetFont('Arial', 'B', 20);
$pdf->Cell(180, 15, "JOBCARD REPORT FOR BATCH {$batch_no}", 0, 1, 'C');
$pdf->Cell(10, 5, '', 0, 1, 'L');
$pdf->SetFont('Arial', '', 15);

$pdf->Cell(180, 15, "LOP JOBCARD", 0, 1, 'C');
$pdf->Cell(10, 5, '', 0, 1, 'L');
$pdf->SetFont('Arial', '', 10);

$pdf->Cell(100, 10, 'Date Time Opened:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['jobcard_date_opened'], "", 1, 'L');

$pdf->Cell(100, 10, 'Batch no:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['batch_no'], "", 1, 'L');

$pdf->Cell(100, 10, 'Number of Trolleys:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['batch_no'], "", 1, 'L');

$pdf->Cell(100, 10, 'Mass placed on trolley (Kg):', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['batch_no'], "", 1, 'L');

$pdf->Cell(100, 10, 'Room Number:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['batch_no'], "", 1, 'L');

$pdf->Cell(100, 10, 'Quality Control Checked By:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['quality_control_checked_by'], "", 1, 'L');

$pdf->Cell(100, 10, 'Production Checked By:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['production_checked_by'], "", 1, 'L');

$pdf->Cell(100, 10, 'Production Supervisor:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['production_supervisor'], "", 1, 'L');

$pdf->Cell(100, 10, 'Quality Control:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['quality_control'], "", 1, 'L');

$pdf->Cell(100, 10, 'Date Time Closed:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['jobcard_closed'], "", 1, 'L');

$pdf->Cell(100, 10, 'Quality Control Checked By:', 0, 0, 'L');
$pdf->Cell(100, 10, $lop_jobcard['quality_control_checked_by'], "", 1, 'L');

// echo $lop_jobcard['quality_controller_signature'];

// $pdf->Image("../lop/" . $lop_jobcard['quality_controller_signature'], 50, 110, 30);

// $pdf->Image("../lop/" . $lop_jobcard['supervisor_signature'], 50, 110, 30);

$pdf->AddPage('p');
$pdf->SetFont('Arial', '', 13);

$pdf->Cell(180, 15, "LOP EQUIPMENT CHECKED HISTORY", 0, 1, 'C');
$pdf->Cell(10, 5, '', 0, 1, 'L');
$pdf->SetFont('Arial', '', 15);

$check_equipment_res = $db->query("lop_equipment_check_list", "SELECT * FROM `lop_equipment_check_list` WHERE batch_no = '$batch_no'");

if ($check_equipment_res->num_rows == 0) {
    $pdf->Cell(180, 10, 'NO EQUIPMENT CHECKED YET', 0, 0, 'C');
    $pdf->Cell(10, 5, '', 0, 1, 'L');
    $pdf->SetFont('Arial', '', 10);
} else {

    $pdf->Cell(40, 8, "Equipment Name", 1, 0, 'L');
    $pdf->Cell(100, 8, "Question", 1, 0, 'L');
    $pdf->Cell(20, 8, "Answer", 1, 0, 'L');
    $pdf->Cell(20, 8, "Comment", 1, 1, 'L');

    while ($check_equipment = $check_equipment_res->fetch_assoc()) {

        $equipment_res = $db->query("equipment_list", "SELECT * FROM equipment_list WHERE record_id = '{$check_equipment['equipment_id']}'");
        $equipment = $equipment_res->fetch_assoc();

        $pdf->Cell(40, 8, $equipment['name'], "LB", 0, 'L');
        $pdf->Cell(100, 8, $check_equipment['question'], "LBR", 0, 'L');
        $pdf->Cell(20, 8, $check_equipment['text'], "LBR", 0, 'L');
        $pdf->Cell(20, 8, $check_equipment['comment'], "BR", 1, 'L');

    }
}

$pdf->AddPage('L');
$pdf->SetFont('Arial', '', 13);

$pdf->Cell(280, 15, "LOP FOOD SAFETY INSPECTIONS", 0, 1, 'C');
$pdf->Cell(10, 5, '', 0, 1, 'L');
$pdf->SetFont('Arial', '', 15);

$lop_record_id = $lop_jobcard['record_id'];

$lop_food_safety_res = $db->query("lop_food_safety_inspections", "SELECT * FROM `lop_food_safety_inspections` WHERE lop_jobcard_no = '{$lop_record_id}'");

if ($lop_food_safety_res->num_rows == 0) {
    $pdf->Cell(280, 10, 'FOOD SAFETY INSPECTIONS NOT CHECKED', 0, 0, 'C');
    $pdf->Cell(10, 5, '', 0, 1, 'L');
    $pdf->SetFont('Arial', '', 10);
} else {

    $lop_food_safety = $lop_food_safety_res->fetch_assoc();

    $lop_food_safety_comments_res = $db->query("lop_food_safety_comments", "SELECT * FROM `lop_food_safety_comments` WHERE food_safety_id = '{$lop_record_id}'");

    $food_comments = $lop_food_safety_comments_res->fetch_assoc();

    $pdf->Cell(130, 8, "Food safety Inspection criteria/ points:", 1, 0, 'L');
    $pdf->Cell(35, 8, "Select/ complete:", 1, 1, 'L');

    $pdf->Cell(130, 8, "Equipment Clean & sanitized", "LB", 0, 'L');
    $pdf->Cell(35, 8, $lop_food_safety['equipment_cleaned'], "LBR", 1, 'L');

    $pdf->Cell(10, 5, '', 0, 1, 'L');

    $pdf->Cell(200, 8, "Comment: ", 1, 1, 'L');
    $pdf->multicell(200, 8, $food_comments['equipment_cleaned_comment'], 1, 'L');

    $pdf->Cell(10, 10, '', 0, 1, 'L');

    $pdf->Cell(130, 8, "Food safety Inspection criteria/ points:", 1, 0, 'L');
    $pdf->Cell(35, 8, "Select/ complete:", 1, 1, 'L');

    $pdf->Cell(130, 8, "LOP room clean?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $lop_food_safety['magnet_1_cleaned'], "LBR", 1, 'L');

    $pdf->Cell(10, 5, '', 0, 1, 'L');

    $pdf->Cell(200, 8, "Comment: ", 1, 1, 'L');
    $pdf->multicell(200, 8, $food_comments['magnet_1_comment'], 1, 'L');

    $pdf->Cell(10, 10, '', 0, 1, 'L');

    $pdf->Cell(130, 8, "Food safety Inspection criteria/ points:", 1, 0, 'L');
    $pdf->Cell(35, 8, "Select/ complete:", 1, 1, 'L');
    $pdf->Cell(130, 8, "No pest acitivity noted?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $lop_food_safety['magnet_2_cleaned'], "LBR", 1, 'L');

    $pdf->Cell(10, 5, '', 0, 1, 'L');

    $pdf->Cell(200, 8, "Comment: ", 1, 1, 'L');
    $pdf->multicell(200, 8, $food_comments['magnet_2_comment'], 1, 'L');

    $pdf->Cell(10, 10, '', 0, 1, 'L');

    $pdf->Cell(130, 8, "Food safety Inspection criteria/ points:", 1, 0, 'L');
    $pdf->Cell(35, 8, "Select/ complete:", 1, 1, 'L');
    $pdf->Cell(130, 8, "No off smells or taints?:", "LB", 0, 'L');
    $pdf->Cell(35, 8, $lop_food_safety['oxyacid_concentration_verification	'], "LBR", 1, 'L');

    $pdf->Cell(10, 5, '', 0, 1, 'L');

    $pdf->Cell(200, 8, "Comment: ", 1, 1, 'L');
    $pdf->multicell(200, 8, $food_comments['oxyacid_concentration_comment'], 1, 'L');

    $pdf->Cell(10, 10, '', 0, 1, 'L');

    $pdf->Cell(130, 8, "Food safety Inspection criteria/ points:", 1, 0, 'L');
    $pdf->Cell(35, 8, "Select/ complete:", 1, 1, 'L');
    $pdf->Cell(130, 8, "Air/ dust extraction system operational?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $lop_food_safety['lop_equipment_checked'], "LBR", 1, 'L');


    $pdf->Cell(10, 5, '', 0, 1, 'L');

    $pdf->Cell(200, 8, "Comment: ", 1, 1, 'L');
    $pdf->multicell(200, 8, $food_comments['lop_equipment_checked_comment'], 1, 'L');

    $pdf->Cell(10, 10, '', 0, 1, 'L');

    $pdf->Cell(130, 8, "Food safety Inspection criteria/ points:", 1, 0, 'L');
    $pdf->Cell(35, 8, "Select/ complete:", 1, 1, 'L');
    $pdf->Cell(130, 8, "No visual contamination risks in room such as cobwebs, loose silicone, dust, water vapour, visble mould etc.?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $lop_food_safety['oil_rust_signs'], "LBR", 1, 'L');


    $pdf->Cell(10, 5, '', 0, 1, 'L');

    $pdf->Cell(200, 8, "Comment: ", 1, 1, 'L');
    $pdf->multicell(200, 8, $food_comments['oil_rust_signs_comment'], 1, 'L');

    $pdf->Cell(10, 10, '', 0, 1, 'L');

    $pdf->Cell(130, 8, "Food safety Inspection criteria/ points:", 1, 0, 'L');
    $pdf->Cell(35, 8, "Select/ complete:", 1, 1, 'L');
    $pdf->Cell(130, 8, "Cheese Crates clean & free from foreign objects?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $lop_food_safety['shell_sieves_clean'], "LBR", 1, 'L');


    $pdf->Cell(10, 5, '', 0, 1, 'L');

    $pdf->Cell(200, 8, "Comment: ", 1, 1, 'L');
    $pdf->multicell(200, 8, $food_comments['shell_sieves_clean_comment'], 1, 'L');

    $pdf->Cell(10, 10, '', 0, 1, 'L');

    $pdf->Cell(130, 8, "Food safety Inspection criteria/ points:", 1, 0, 'L');
    $pdf->Cell(35, 8, "Select/ complete:", 1, 1, 'L');
    $pdf->Cell(130, 8, "Is there traceability available for each trolley?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $lop_food_safety['filter_dust_control_working'], "LBR", 1, 'L');


    $pdf->Cell(10, 5, '', 0, 1, 'L');

    $pdf->Cell(200, 8, "Comment: ", 1, 1, 'L');
    $pdf->multicell(200, 8, $food_comments['filter_dust_control_working_comment'], 1, 'L');

    $pdf->Cell(10, 10, '', 0, 1, 'L');

}

$pdf->AddPage('L');
$pdf->SetFont('Arial', '', 13);

$pdf->Cell(280, 15, "LOP QUALITY CONTROL REPORT", 0, 1, 'C');
$pdf->Cell(10, 5, '', 0, 1, 'L');
$pdf->SetFont('Arial', '', 15);

$lop_food_safety_res = $db->query("lop_quality_process_control", "SELECT * FROM `lop_quality_process_control` WHERE lop_id = '$lop_record_id'");

if ($lop_food_safety_res->num_rows == 0) {
    $pdf->Cell(280, 20, 'QUALITY CONTROL NOT DONE', 0, 0, 'C');
    $pdf->Cell(10, 5, '', 0, 1, 'L');
    $pdf->SetFont('Arial', '', 10);
} else {

    $pdf->Cell(100, 10, 'Date Time Opened:', 0, 0, 'L');
    $pdf->Cell(100, 10, $lop_jobcard['date_time'], "", 1, 'L');

    $pdf->Cell(100, 10, 'Shift (Day / Night):', 0, 0, 'L');
    $pdf->Cell(100, 10, $lop_jobcard['shift'], "", 1, 'L');

    $pdf->Cell(100, 10, 'Sample Size (Kg):', 0, 0, 'L');
    $pdf->Cell(100, 10, $lop_jobcard['sample_size'], "", 1, 'L');

    $pdf->Cell(100, 10, 'Trolley Number:', 0, 0, 'L');
    $pdf->Cell(100, 10, $lop_jobcard['trolley_number'], "", 1, 'L');

    $pdf->Cell(100, 10, 'Trolley Position:', 0, 0, 'L');
    $pdf->Cell(100, 10, $lop_jobcard['trolley_position'], "", 1, 'L');

    $pdf->Cell(10, 20, '', 0, 1, 'L');

    $pdf->Cell(35, 8, "Mouldy Kernel", 1, 0, 'L');
    $pdf->Cell(35, 8, "Rancid/off-smell kernel", 1, 0, 'L');
    $pdf->Cell(35, 8, "Kernel Moisture %", 1, 0, 'L');
    $pdf->Cell(35, 8, "Env. Temperature", 1, 0, 'L');
    $pdf->Cell(35, 8, "Env. Humidity", 1, 0, 'L');
    $pdf->Cell(35, 8, "Discolouration", 1, 0, 'L');

    $res_dat = $db->query("lop_quality_process_control", "SELECT * FROM lop_quality_process_control WHERE record_id = {$_GET['record_id']}");
    while ($checks = $res_dat->fetch_assoc()) {

        $user_res = $db->query("users", "SELECT * FROM users WHERE record_id = {$checks['person_inspecting']}");
        $user = $user_res->fetch_assoc();

        $pdf->Cell(35, 8, $checks['mouldy_kernal'] . " kg", 1, 0, 'L');
        $pdf->Cell(35, 8, $checks['rancid_kernal_smell	'] . " kg", 1, 0, 'L');
        $pdf->Cell(35, 8, $checks['kernal_moisture'] . " kg", 1, 0, 'L');
        $pdf->Cell(35, 8, $checks['env_temperature'] . " %", 1, 0, 'L');
        $pdf->Cell(35, 8, $checks['env_humidity'] . " kg", 1, 0, 'L');
        $pdf->Cell(35, 8, $checks['discolouration	'] . " kg", 1, 0, 'L');

        $pdf->Cell(10, 20, '', 0, 1, 'L');

        $pdf->Cell(50, 10, "Person Inspecting : ", 0, 0, 'L');
        $pdf->Cell(40, 10, $user['username'], 0, 1, 'L');

        $pdf->Image("../lop/" . $checks['signature'], 50, 100, 30);
        $pdf->Cell(30, 15, "Signature : ", 0, 1, 'L');
    }
}

$pdf->Cell(10, 5, '', 0, 1, 'L');

$pdf->Output("I");
