<?php
require('classes/fpdf.php');
require('classes/db.class.php');

$db = new db();

$pdf = new FPDF();

$pdf->AliasNbPages();

$pdf->AddPage('L');

$table_top = [50, 28, 16, 65, 35, 55, 30, 40, 25, 20, 20, 20];

$pdf->SetFont('Arial', 'B', 20);
$pdf->Cell(200, 15, "CLIENTS REPORT ", 0, 1, 'C');
$pdf->Cell(10, 5, '', 0, 1, 'L');
$pdf->SetFont('Arial', '', 10);

$where_data = $_POST['where_data'];

$pdf->Cell(-2);

$batch_res = $db->exec_query('batches', ['*']);

$clients_res = $db->query("SELECT DISTINCT(client_id) FROM batches WHERE $where_data");

while ($clients = $clients_res->fetch_assoc()) {
    $client_name_res = $db->exec_query('clients', ['*'], '', '', '', '', "record_id = {$clients['client_id']}", '', false);
    $client = $client_name_res->fetch_assoc();

    $pdf->SetFont('Arial', 'B', 15);
    $pdf->Cell(200, 20, "{$client['client_name']} DELIVERY Summary", 0, 1, 'C');
    $pdf->SetFont('Arial', '', 10);

    $pdf->Cell($table_top[8], 8, "CLIENT", 1, 0, 'L');
    $pdf->Cell($table_top[7], 8, "TOTAL", 1, 0, 'L');
    $pdf->Cell($table_top[7], 8, "TOTAL SOUND", 1, 0, 'L');
    $pdf->Cell($table_top[7], 8, "TOTAL NOT SOUND", 1, 0, 'L');
    $pdf->Cell($table_top[7], 8, "TOTAL AMOUNT", 'LTB', 1, 'L');

    $unq_variety_res = $db->query("SELECT DISTINCT(variety_id) FROM batches WHERE $where_data AND client_id = {$clients['client_id']}");

    while ($unq_variety = $unq_variety_res->fetch_assoc()) {
        $variety_name_res = $db->exec_query('variety', ['*'], '', '', '', '', "record_id = {$unq_variety['variety_id']}");
        $variety_name = $variety_name_res->fetch_assoc();

        $client_count_res = $db->query("SELECT * FROM batches WHERE $where_data AND variety_id = {$unq_variety['variety_id']} AND client_id = {$clients['client_id']}");
        $client_count = $client_count_res->num_rows;

        $sound_count_res = $db->query("SELECT * FROM batches WHERE $where_data AND variety_id = {$unq_variety['variety_id']} AND client_id = {$clients['client_id']} AND sound_not_sound = 'YES'");
        $sound_count = $sound_count_res->num_rows;

        $not_sound_count_res = $db->query("SELECT * FROM batches WHERE $where_data AND variety_id = {$unq_variety['variety_id']} AND client_id = {$clients['client_id']} AND sound_not_sound = 'NO'");
        $not_sound_count = $not_sound_count_res->num_rows;

        $amount_res = $db->exec_query('batches',['*'], '', '', '', '', "$where_data AND variety_id = {$unq_variety['variety_id']} AND client_id = {$clients['client_id']}");

        $amount = $amount_res->fetch_assoc();

        $total_amount =+ $amount['weight'];

        $pdf->Cell($table_top[8], 8, $variety_name['name'], 1, 0, 'L');
        $pdf->Cell($table_top[7], 8, $client_count, 1, 0, 'L');
        $pdf->Cell($table_top[7], 8, $sound_count, 1, 0, 'L');
        $pdf->Cell($table_top[7], 8, $not_sound_count, 1, 0, 'L');
        $pdf->Cell($table_top[7], 8, $total_amount, 'LTB', 1, 'L');

    }
}



$pdf->Output("I");