<?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("final_sorting_jobcard", "SELECT * FROM final_sorting_jobcard WHERE batch_no = '$batch_no'");
$final_sorting_jobcard = $jobcard_res->fetch_assoc();

// echo "SELECT * FROM final_sorting_jobcard WHERE batch_no = '$batch_no'";

$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, "CRACKING 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, $final_sorting_jobcard['jobcard_date_opened'], "", 1, 'L');

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

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

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

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

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

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

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

// echo $final_sorting_jobcard['quality_controller_signature'];

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

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

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

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

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

if ($check_equipment_res->num_rows == 0) {
    $pdf->Cell(100, 10, 'NO EQUIPMENT CHECKED YET', 0, 0, 'L');
    $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()) {

        // echo "SELECT * FROM equipment_list WHERE record_id = '{$check_equipment['equipment_id']}'";

        $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, "FINAL SORTING FOOD SAFETY INSPECTIONS", 0, 1, 'C');
$pdf->Cell(10, 5, '', 0, 1, 'L');
$pdf->SetFont('Arial', '', 11);

$final_sorting_record_id = $final_sorting_jobcard['record_id'];

$final_food_safety_res = $db->query("final_sorting_food_safety_inspections", "SELECT * FROM `final_sorting_food_safety_inspections` WHERE final_sorting_jobcard_no = '{$final_sorting_record_id}'");

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

    $final_food_safety = $final_food_safety_res->fetch_assoc();

    $final_food_safety_comments_res = $db->query("final_food_safety_comments", "SELECT * FROM `final_food_safety_comments` WHERE food_safety_id = '{$final_sorting_record_id}'");

    $food_comments = $final_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, $final_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, "Magnet 1 (Initial) Clean?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $final_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, "Magnet 2 (Initial) Clean?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $final_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, "Oxyacid Concentration verification (Add in spec after validation):", "LB", 0, 'L');
    $pdf->Cell(35, 8, $final_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, "Cracking equipment inspected (rollers, blades, hopper)? Yes/ No", "LB", 0, 'L');
    $pdf->Cell(35, 8, $final_food_safety['cracking_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['cracking_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 signs of rust, oil leaks, or foreign objects", "LB", 0, 'L');
    $pdf->Cell(35, 8, $final_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, "Shell separator & sieves clean", "LB", 0, 'L');
    $pdf->Cell(35, 8, $final_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, "Air filtration/dust control working properly", "LB", 0, 'L');
    $pdf->Cell(35, 8, $final_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->Cell(130, 8, "Food safety Inspection criteria/ points:", 1, 0, 'L');
    $pdf->Cell(35, 8, "Select/ complete:", 1, 1, 'L');
    $pdf->Cell(130, 8, "Personal hygiene (hands, gloves, PPE) checked?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $final_food_safety['personal_hygiene'], "LBR", 1, 'L');


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

    $pdf->Cell(200, 8, "Comment: ", 1, 1, 'L');
    $pdf->multicell(200, 8, $food_comments['personal_hygiene_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, "Tools sanitized and labelled?", "LB", 0, 'L');
    $pdf->Cell(35, 8, $final_food_safety['tools_sanitized_labelled'], "LBR", 1, 'L');

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

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

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

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

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

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

$final_sorting_record_id = $final_sorting['record_id'];

$quality_control_res = $db->query("final_sorting_quality_control", "SELECT * FROM final_sorting_quality_control` WHERE record_id = '$final_sorting_record_id'");

if ($quality_control_res->num_rows < 0) {
    $pdf->Cell(100, 10, 'QUALITY CONTROL NOT DONE', 0, 0, 'L');
    $pdf->Cell(10, 5, '', 0, 1, 'L');
    $pdf->SetFont('Arial', '', 10);
} else {

    $pdf->Cell(40, 8, "Date & Time", 1, 0, 'L');
    $pdf->Cell(20, 8, "Shift (D/N)", 1, 0, 'L');
    $pdf->Cell(30, 8, "Sample Size(kg)", 1, 0, 'L');
    $pdf->Cell(40, 8, "Product sample point", 1, 0, 'L');
    $pdf->Cell(20, 8, "Wholes", 1, 0, 'L');
    $pdf->Cell(20, 8, "Halves", 1, 0, 'L');
    $pdf->Cell(20, 8, "Dust", 1, 0, 'L');
    $pdf->Cell(20, 8, "Shells", 1, 1, 'L');

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

        $pdf->Cell(40, 8, $checks['date_time'], 1, 0, 'L');
        $pdf->Cell(20, 8, $checks['shift'], 1, 0, 'L');
        $pdf->Cell(30, 8, $checks['sample_size'] . " kg", 1, 0, 'L');
        $pdf->Cell(40, 8, $checks['product_sample_point'], 1, 0, 'L');
        $pdf->Cell(20, 8, $checks['wholes'] . " kg", 1, 0, 'L');
        $pdf->Cell(20, 8, $checks['halves'] . " kg", 1, 0, 'L');
        $pdf->Cell(20, 8, $checks['dust'] . " kg", 1, 0, 'L');
        $pdf->Cell(20, 8, $checks['shells'] . " kg", 1, 0, 'L');

    }

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

    $pdf->Cell(30, 8, "Broken/Chipped", 1, 0, 'L');
    $pdf->Cell(30, 8, "Shell in Kernal", 1, 0, 'L');
    $pdf->Cell(30, 8, "Un-cracked Nuts", 1, 0, 'L');
    $pdf->Cell(45, 8, "Crushed/ Missing Kernal", 1, 0, 'L');
    $pdf->Cell(20, 8, "Moisure %", 1, 0, 'L');
    $pdf->Cell(35, 8, "Env. Temperature", 1, 0, 'L');
    $pdf->Cell(30, 8, "Env. Humidity", 1, 0, 'L');
    $pdf->Cell(35, 8, "Magnet inspection", 1, 1, 'L');


    $res_dat = $db->query("final_sorting_quality_process_control", "SELECT * FROM final_sorting_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_inspection']}");
        $user = $user_res->fetch_assoc();

        $pdf->Cell(30, 8, $checks['broken_chipped'] . " kg", 1, 0, 'L');
        $pdf->Cell(30, 8, $checks['shell_in_kernal'] . " kg", 1, 0, 'L');
        $pdf->Cell(30, 8, $checks['uncracked_missing_kernal'] . " kg", 1, 0, 'L');
        $pdf->Cell(45, 8, $checks['shell_in_kernal'] . " kg", 1, 0, 'L');
        $pdf->Cell(20, 8, $checks['moisture'] . " %", 1, 0, 'L');
        $pdf->Cell(35, 8, $checks['env_temperature'] . " kg", 1, 0, 'L');
        $pdf->Cell(30, 8, $checks['env_humidity'] . " kg", 1, 0, 'L');
        $pdf->Cell(35, 8, $checks['magnet_inspection'], 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("../final_sorting/" . $checks['signature'], 50, 100, 30);
        $pdf->Cell(30, 15, "Signature : ", 0, 1, 'L');
    }
}


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

$pdf->Cell(280, 15, "FINAL SORTING BATCH OUTPUT CALCULATION SUMMARY", 0, 1, 'C');
$pdf->Cell(10, 5, '', 0, 1, 'L');
$pdf->SetFont('Arial', '', 11);

$final_sorting_batch_summary_res = $db->query("final_sorting_batch_summary", "SELECT * FROM `final_sorting_batch_summary` WHERE final_sorting_jobcard_id = '$final_sorting_record_id'");

$final_sorting_batch_summary = $final_sorting_batch_summary_res->fetch_assoc();

$pdf->Cell(200, 8, "Batch Output Calculation Summary", 1, 1, 'L');
$pdf->Cell(70, 8, "Whole Kernel", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['whole_kernal'] . " kg", 1, 0, 'L');
$pdf->Cell(70, 8, "Waste", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['waste'] . " kg", 1, 1, 'L');

$pdf->Cell(70, 8, "Halves", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['halves'] . " kg", 1, 0, 'L');
$pdf->Cell(70, 8, "Kernal Breakage rate (<= 10 - 15%)", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['kernal_breakage_rate'] . " kg", 1, 1, 'L');

$pdf->Cell(70, 8, "Dust", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['dust_summary'] . " kg", 1, 0, 'L');
$pdf->Cell(70, 8, "Moisture (in-shell 1.5 - 3.0%)", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['moisture_in_shell'] . " kg", 1, 1, 'L');

$pdf->Cell(70, 8, "Shells", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['shell_summary'] . " kg", 1, 0, 'L');
$pdf->Cell(70, 8, "Whole kernel recovery (>= 85%)", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['whole_kernal_recovery'] . " kg", 1, 1, 'L');

$pdf->Cell(70, 8, "Chips/Broken", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['chips_broken'] . " kg", 1, 0, 'L');
$pdf->Cell(70, 8, "Shell in kernel contamination (0%)", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['shell_in_kernal_contamination'] . " kg", 1, 1, 'L');

$pdf->Cell(70, 8, "Shell in kernal", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['shell_in_kernal_summary'] . " kg", 1, 0, 'L');
$pdf->Cell(70, 8, "Kernel stuck in shell (< 1 - 2%)", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['kernal_stuck_in_shell'] . " kg", 1, 1, 'L');

$pdf->Cell(70, 8, "Uncracked nuts", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['uncracked_nuts_summary'] . " kg", 1, 0, 'L');

$pdf->Cell(70, 8, "Crushed/ Missing Kernal", 1, 0, 'L');
$pdf->Cell(30, 8, $final_sorting_batch_summary['crushed_missing_kernal_summary'] . " kg", 1, 1, 'L');

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

$pdf->Cell(200, 8, "Actions taken for NC product:", 1, 1, 'L');

$pdf->MultiCell(200, 8, $final_sorting_batch_summary['actions_taken'], 1, 'L');

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

$pdf->Output("I");
