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

$total_stock_value = 0.00;
$db = new db_safeguard();
$where_data = ' 1 ';
function remove_html_tags($text)
{
    $description = strip_tags($text);
    $description = str_replace("\n", "\r\n", $description);
    $description = preg_replace('/\s\s+/', ' ', $description);
    $description = preg_replace('/\n\n+/', "\n", $description);
    return $description;
}

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

$pdf->SetFont('Arial', 'B', 7);
$pdf->Cell(95, 5, "CATEGORY NAME", 1, 0, 'L');
$pdf->Cell(35, 5, "VALUE", 1, 0, 'L');
$pdf->Cell(35, 5, "AMOUNT", 1, 1, 'L');
$pdf->Cell(35, 1, "", 0, 1, 'L');
$pdf->SetFont('Arial', '', 5);

if (strlen($_GET['description']) > 0) {
    $description = "AND `description` LIKE '%{$_GET['description']}%'";
}else{
    $description = "";
}

if (strlen($_GET['category_id']) > 0) {
    $category_id = "AND `category_id` = {$_GET['category_id']}";
}else{
    $category_id = "";
}

if (strlen($_GET['stock_type']) > 0) {
    $stock_type_id = "AND `stock_type` = {$_GET['stock_type']}";
}else{
    $stock_type_id = "";
}


$stock_category_res = $db->query("stock", "SELECT DISTINCT category_id FROM `stock` WHERE 1 $category_id $description $stock_type_id");

while ($stock_category = $stock_category_res->fetch_assoc()) {
    $category_res = $db->query("stock_categories", "SELECT * from stock_categories WHERE record_id = {$stock_category['category_id']}");
    $category = $category_res->fetch_assoc();

    $pdf->Cell(95, 5, remove_html_tags($category['name']), 1, 0, '');


    $stock_res = $db->query("stock", "SELECT * FROM stock WHERE category_id = {$stock_category['category_id']} $description $stock_type_id");

    $total_category_value = 0;
    $total_category_amount = 0;

    while ($stock = $stock_res->fetch_assoc()) {

        $stock_amount_res = $db->query("order_trans", "SELECT SUM(amount) as total FROM order_trans WHERE stock_id = {$stock['record_id']}");

        $total_category_amount += $stock_amount = $stock_amount_res->fetch_assoc()['total'];

        $stock_value = $stock_amount * (float) $stock['cost_price'];

        $total_category_value += $stock_value;

    }

    $total_stock_value += $total_category_value;

    $pdf->Cell(35, 5, "R " . number_format((float) $total_category_value, 3, ".", ","), 1, 0, 'L');
    $pdf->Cell(35, 5, number_format((float) $total_category_amount, 3, ".", ","), 1, 1, 'L');

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

}

$pdf->SetFont('Arial', 'B', 7);

$pdf->Cell(95, 5, "TOTAL STOCK VALUE", 1, 0, 'L');

$pdf->Cell(35, 5, "R " . number_format((float) $total_stock_value, 3, ".", ","), 1, 1, 'L');


$total_stock_value = 0;

$stock_type_res = $db->query("stock", "SELECT DISTINCT stock_type FROM stock WHERE 1 $category_id $description $stock_type_id");
while ($stock_type = $stock_type_res->fetch_assoc()) {
    $pdf->SetFont('Arial', 'B', 20);
    if ($stock_type['stock_type'] == 0) {
        $pdf->Cell(80, 20, "STOCK", "B", 1, 'L');
        $stock_res = $db->query("stock", "SELECT * FROM stock WHERE stock_type = 0 $category_id $description $stock_type_id ORDER BY  category_id DESC,`description` ASC");
        while ($stock = $stock_res->fetch_assoc()) {
            $stock_amount_res = $db->query("order_trans", "SELECT SUM(amount) as total FROM order_trans WHERE stock_id = {$stock['record_id']}");
            $stock_amount = $stock_amount_res->fetch_assoc()['total'];
            $pdf->SetFont('Arial', '', 6);
            $pdf->MultiCell(140, 4, remove_html_tags($stock['description']), 1, "L", '');
            $pdf->Cell(15, 5, "AMOUNT", "LB", 0, 'L');
            $pdf->Cell(35, 5, "CATEGORY", "LRB", 0, 'L');
            $pdf->Cell(30, 5, "ITEM CODE", "RB", 0, 'L');
            $pdf->Cell(30, 5, "ITEM VALUE", "RB", 0, 'L');
            $pdf->Cell(30, 5, "STOCK VALUE", "RB", 1, 'L');

            if ($stock_amount > 0) {
                $pdf->Cell(15, 5, $stock_amount, "LBR", 0, 'L');
            } else {
                $pdf->Cell(15, 5, 0, "LBR", 0, 'L');
            }
            $category_res = $db->query("stock_categories", "SELECT * FROM `stock_categories` WHERE record_id = {$stock['category_id']}");
            $category = $category_res->fetch_assoc();
            if ($category_res->num_rows > 0) {
                $pdf->Cell(35, 5, $category['name'], "LBR", 0, 'L');
            } else {
                $pdf->Cell(35, 5, "NO STOCK CATEGORY", "LBR", 0, 'L');
            }
            $pdf->Cell(30, 5, $stock['item_code'], "LBR", 0, 'L');
            $pdf->Cell(30, 5, "R " . number_format((float) $stock['cost_price'], 3, ".", ","), "TLBR", 0, 'L');
            $stock_value = $stock_amount * (float) $stock['cost_price'];
            $total_stock_value += $stock_value;
            $pdf->Cell(30, 5, "R " . number_format((float) $stock_value, 3, ".", ","), "TLBR", 1, 'L');
            $pdf->Cell(80, 5, "", "", 1, 'L');

        }
    } elseif ($stock_type['stock_type'] == 1) {
        $pdf->Cell(90, 20, "NON STOCK", "B", 1, 'L');
        $stock_res = $db->query("stock", "SELECT * FROM stock WHERE stock_type = 1  $category_id $description ORDER BY  category_id DESC,`description` ASC");
        while ($stock = $stock_res->fetch_assoc()) {
            $stock_amount_res = $db->query("order_trans", "SELECT SUM(amount) as total FROM order_trans WHERE stock_id = {$stock['record_id']}");
            $stock_amount = $stock_amount_res->fetch_assoc()['total'];
            $pdf->SetFont('Arial', '', 6);
            $pdf->MultiCell(140, 4, remove_html_tags($stock['description']), 1, "L", '');
            $pdf->Cell(15, 5, "AMOUNT", "LB", 0, 'L');
            $pdf->Cell(35, 5, "CATEGORY", "LRB", 0, 'L');
            $pdf->Cell(30, 5, "ITEM CODE", "RB", 0, 'L');
            $pdf->Cell(30, 5, "ITEM VALUE", "RB", 0, 'L');
            $pdf->Cell(30, 5, "STOCK VALUE", "RB", 1, 'L');

            if ($stock_amount > 0) {
                $pdf->Cell(15, 5, $stock_amount, "LBR", 0, 'L');
            } else {
                $pdf->Cell(15, 5, 0, "LBR", 0, 'L');
            }
            $category_res = $db->query("stock_categories", "SELECT * FROM `stock_categories` WHERE record_id = {$stock['category_id']}");
            $category = $category_res->fetch_assoc();
            if ($category_res->num_rows > 0) {
                $pdf->Cell(35, 5, $category['name'], "LBR", 0, 'L');
            } else {
                $pdf->Cell(35, 5, "NO STOCK CATEGORY", "LBR", 0, 'L');
            }
            $pdf->Cell(30, 5, $stock['item_code'], "LBR", 0, 'L');
            $pdf->Cell(30, 5, "R " . number_format((float) $stock['cost_price'], 3, ".", ","), "TLBR", 0, 'L');
            $stock_value = $stock_amount * (float) $stock['cost_price'];
            $total_stock_value += $stock_value;
            $pdf->Cell(30, 5, "R " . number_format((float) $stock_value, 3, ".", ","), "TLBR", 1, 'L');
            $pdf->Cell(80, 5, "", "", 1, 'L');

        }
    } else {
        $pdf->Cell(90, 20, "UNITS", "B", 1, 'L');
        $stock_res = $db->query("stock", "SELECT * FROM stock WHERE stock_type = 2  $category_id $description ORDER BY  category_id DESC,`description` ASC");
        while ($stock = $stock_res->fetch_assoc()) {
            $stock_amount_res = $db->query("order_trans", "SELECT SUM(amount) as total FROM order_trans WHERE stock_id = {$stock['record_id']}");
            $stock_amount = $stock_amount_res->fetch_assoc()['total'];
            $pdf->SetFont('Arial', '', 6);
            $pdf->MultiCell(140, 4, remove_html_tags($stock['description']), 1, "L", '');
            $pdf->Cell(15, 5, "AMOUNT", "LB", 0, 'L');
            $pdf->Cell(35, 5, "CATEGORY", "LRB", 0, 'L');
            $pdf->Cell(30, 5, "ITEM CODE", "RB", 0, 'L');
            $pdf->Cell(30, 5, "ITEM VALUE", "RB", 0, 'L');
            $pdf->Cell(30, 5, "STOCK VALUE", "RB", 1, 'L');

            if ($stock_amount > 0) {
                $pdf->Cell(15, 5, $stock_amount, "LBR", 0, 'L');
            } else {
                $pdf->Cell(15, 5, 0, "LBR", 0, 'L');
            }
            $category_res = $db->query("stock_categories", "SELECT * FROM `stock_categories` WHERE record_id = {$stock['category_id']}");
            $category = $category_res->fetch_assoc();
            if ($category_res->num_rows > 0) {
                $pdf->Cell(35, 5, $category['name'], "LBR", 0, 'L');
            } else {
                $pdf->Cell(35, 5, "NO STOCK CATEGORY", "LBR", 0, 'L');
            }
            $pdf->Cell(30, 5, $stock['item_code'], "LBR", 0, 'L');
            $pdf->Cell(30, 5, "R " . number_format((float) $stock['cost_price'], 3, ".", ","), "TLBR", 0, 'L');
            $stock_value = $stock_amount * (float) $stock['cost_price'];
            $total_stock_value += $stock_value;
            $pdf->Cell(30, 5, "R " . number_format((float) $stock_value, 3, ".", ","), "TLBR", 1, 'L');
            $pdf->Cell(80, 5, "", "", 1, 'L');

        }
    }
}

$pdf->Output("I");
