Stock Report Filter Enhancement Tutorial

This comprehensive tutorial guides you through enhancing the Stock Report functionality in Ultimate POS by adding advanced filtering capabilities including multi-select filters

Overview

This enhancement transforms the standard Stock Report into a powerful filtering tool with multi-select capabilities.

Stock Report Filter Enhancement

What This Enhancement Adds

Multi-select capability for all existing filters (Location, Category, Sub-category, Brand, Unit)
Stock level filter with options: All, Greater than Zero, Less than One
"All" option handling for improved user experience
Improved filtering logic for better performance and accuracy

Benefits

✅ Multi-dimensional Filtering: Select multiple categories, brands, locations simultaneously
✅ Improved User Experience: Intuitive "All" options and clear filter states
✅ Enhanced Performance: Optimized database queries for complex filters
✅ Proper Currency Display: Consistent formatting with currency symbols

Implementation Steps

Step 1: Update ReportController.php

1.1 Add the Enhanced getStockValue Method

Replace your existing getStockValue method with this enhanced version:

app/Http/Controllers/ReportController.php
/**
 * Enhanced getStockValue method for multi-select compatibility
 *
 * @param Request $request
 * @return \Illuminate\Http\JsonResponse
 */
public function getStockValue(Request $request)
{
    try {
        $business_id = $request->session()->get('user.business_id');
        $end_date = \Carbon::now()->format('Y-m-d');
        $permitted_locations = auth()->user()->permitted_locations();

        // Process filters using enhanced logic
        $filters = $this->processStockValueFilters($request);

        // Handle location_id separately for getOpeningClosingStock method
        $location_id = null;
        if (isset($filters['location_id'])) {
            $location_id = $filters['location_id'];
            unset($filters['location_id']); // Remove from filters array
        }

        // Get Closing stock by purchase price
        $closing_stock_by_pp = $this->transactionUtil->getOpeningClosingStock(
            $business_id,
            $end_date,
            $location_id,
            false, // is_opening = false
            false, // by_sale_price = false (by purchase price)
            $filters,
            $permitted_locations
        );

        // Get Closing stock by selling price
        $closing_stock_by_sp = $this->transactionUtil->getOpeningClosingStock(
            $business_id,
            $end_date,
            $location_id,
            false, // is_opening = false
            true,  // by_sale_price = true
            $filters,
            $permitted_locations
        );

        // Calculate potential profit and margin
        $potential_profit = $closing_stock_by_sp - $closing_stock_by_pp;
        $profit_margin = empty($closing_stock_by_sp) ? 0 : ($potential_profit / $closing_stock_by_sp) * 100;

        // Format the values using transactionUtil->num_f() with currency symbol
        $response = [
            'closing_stock_by_pp' => $this->transactionUtil->num_f($closing_stock_by_pp, true),
            'closing_stock_by_sp' => $this->transactionUtil->num_f($closing_stock_by_sp, true),
            'potential_profit' => $this->transactionUtil->num_f($potential_profit, true),
            'profit_margin' => number_format($profit_margin, 1),

            // Keep raw values for calculations if needed
            'raw_closing_stock_by_pp' => round($closing_stock_by_pp, 2),
            'raw_closing_stock_by_sp' => round($closing_stock_by_sp, 2),
            'raw_potential_profit' => round($potential_profit, 2),
            'raw_profit_margin' => round($profit_margin, 2),
        ];

        return response()->json($response);
    } catch (\Exception $e) {
        \Log::error('Error in getStockValue: ' . $e->getMessage());
        \Log::error('Stack trace: ' . $e->getTraceAsString());

        return response()->json([
            'closing_stock_by_pp' => $this->transactionUtil->num_f(0, true),
            'closing_stock_by_sp' => $this->transactionUtil->num_f(0, true),
            'potential_profit' => $this->transactionUtil->num_f(0, true),
            'profit_margin' => '0.0',
        ], 500);
    }
}

1.2 Add the processStockValueFilters Method

Add this new private method to handle filter processing:

app/Http/Controllers/ReportController.php
/**
 * Process stock value specific filters
 *
 * @param Request $request
 * @return array
 */
private function processStockValueFilters(Request $request)
{
    $filters = [];

    // Define filter mappings
    $filterMappings = [
        'location_id' => $request->input('location_id'),
        'category_id' => $request->input('category_id'),
        'sub_category_id' => $request->input('sub_category_id'),
        'brand_id' => $request->input('brand_id'),
        'unit_id' => $request->input('unit_id'),
        'stock_level' => $request->input('stock_level')
    ];

    foreach ($filterMappings as $key => $value) {
        if (!empty($value)) {
            if (is_array($value)) {
                // Remove empty values, null values, and "All" selections
                $value = array_filter($value, function ($v) {
                    return $v !== '' && $v !== null && trim($v) !== '' && $v !== 'null';
                });

                if (!empty($value)) {
                    if ($key === 'stock_level') {
                        // Stock level is not an array
                        $filters[$key] = reset($value);
                    } else {
                        // Convert to integers for ID fields, removing any zero values
                        $intValues = array_values(array_map('intval', $value));
                        $intValues = array_filter($intValues, function ($v) {
                            return $v > 0; // Only include positive integers
                        });

                        if (!empty($intValues)) {
                            $filters[$key] = $intValues;
                        }
                    }
                }
            } else {
                if (trim($value) !== '' && $value !== null) {
                    if (in_array($key, ['category_id', 'sub_category_id', 'brand_id', 'unit_id', 'location_id'])) {
                        $intValue = intval($value);
                        if ($intValue > 0) {
                            $filters[$key] = $intValue;
                        }
                    } else {
                        $filters[$key] = $value;
                    }
                }
            }
        }
    }

    return $filters;
}

1.3 Update the getStockReport Method

Update your existing getStockReport method to include the enhanced filter processing:

app/Http/Controllers/ReportController.php
/**
 * Shows product stock report with enhanced multi-select filtering
 *
 * @return \Illuminate\Http\Response
 */
public function getStockReport(Request $request)
{
    if (!auth()->user()->can('stock_report.view')) {
        abort(403, 'Unauthorized action.');
    }

    $business_id = $request->session()->get('user.business_id');

    // Check if manufacturing module is enabled
    $show_manufacturing_data = 0;
    if (
        $this->moduleUtil->isModuleInstalled('Manufacturing') &&
        (auth()->user()->can('superadmin') ||
            $this->moduleUtil->hasThePermissionInSubscription($business_id, 'manufacturing_module'))
    ) {
        $show_manufacturing_data = 1;
    }

    // Check selling price group permissions
    $selling_price_groups = SellingPriceGroup::where('business_id', $business_id)->get();
    $allowed_selling_price_group = false;
    foreach ($selling_price_groups as $selling_price_group) {
        if (auth()->user()->can('selling_price_group.' . $selling_price_group->id)) {
            $allowed_selling_price_group = true;
            break;
        }
    }

    if ($request->ajax()) {
        // Process enhanced multi-select filters
        $filters = $this->processEnhancedStockReportFilters($request);

        // Add manufacturing data flag
        $filters['show_manufacturing_data'] = $show_manufacturing_data;

        // Return the details in ajax call
        $for = $request->input('for') == 'view_product' ? 'view_product' : 'datatables';

        $products = $this->productUtil->getProductStockDetails($business_id, $filters, $for);

        // To show stock details on view product modal
        if ($for == 'view_product' && !empty($request->input('product_id'))) {
            $product_stock_details = $products;
            return view('product.partials.product_stock_details')->with(compact('product_stock_details'));
        }

        // Build DataTable with enhanced columns
        $datatable = Datatables::of($products)
            ->editColumn('stock', function ($row) {
                if ($row->enable_stock) {
                    $stock = $row->stock ? $row->stock : 0;
                    return '<span class="current_stock" data-orig-value="' . (float) $stock . '" data-unit="' . $row->unit . '"> ' .
                        $this->transactionUtil->num_f($stock, false, null, true) . '</span> ' . $row->unit;
                } else {
                    return '--';
                }
            })
            ->editColumn('product', function ($row) {
                $name = $row->product;
                return $name;
            })
            ->addColumn('action', function ($row) {
                return '<a class="tw-dw-btn tw-dw-btn-xs tw-dw-btn-outline tw-dw-btn-info tw-w-max" href="' .
                    action([\App\Http\Controllers\ProductController::class, 'productStockHistory'], [$row->product_id]) .
                    '?location_id=' . $row->location_id . '&variation_id=' . $row->variation_id .
                    '"><i class="fas fa-history"></i> ' . __('lang_v1.product_stock_history') . '</a>';
            })
            ->addColumn('variation', function ($row) {
                $variation = '';
                if ($row->type == 'variable') {
                    $variation .= $row->product_variation . '-' . $row->variation_name;
                }
                return $variation;
            })
            ->editColumn('total_sold', function ($row) {
                $total_sold = 0;
                if ($row->total_sold) {
                    $total_sold = (float) $row->total_sold;
                }
                return '<span data-is_quantity="true" class="total_sold" data-orig-value="' . $total_sold . '" data-unit="' . $row->unit . '" >' .
                    $this->transactionUtil->num_f($total_sold, false, null, true) . '</span> ' . $row->unit;
            })
            ->editColumn('total_transfered', function ($row) {
                $total_transfered = 0;
                if ($row->total_transfered) {
                    $total_transfered = (float) $row->total_transfered;
                }
                return '<span class="total_transfered" data-orig-value="' . $total_transfered . '" data-unit="' . $row->unit . '" >' .
                    $this->transactionUtil->num_f($total_transfered, false, null, true) . '</span> ' . $row->unit;
            })
            ->editColumn('total_adjusted', function ($row) {
                $total_adjusted = 0;
                if ($row->total_adjusted) {
                    $total_adjusted = (float) $row->total_adjusted;
                }
                return '<span class="total_adjusted" data-orig-value="' . $total_adjusted . '" data-unit="' . $row->unit . '" >' .
                    $this->transactionUtil->num_f($total_adjusted, false, null, true) . '</span> ' . $row->unit;
            })
            ->editColumn('unit_price', function ($row) use ($allowed_selling_price_group) {
                $html = '';
                if (auth()->user()->can('access_default_selling_price')) {
                    $html .= $this->transactionUtil->num_f($row->unit_price, true);
                }

                if ($allowed_selling_price_group) {
                    $html .= ' <button type="button" class="tw-dw-btn tw-dw-btn-xs tw-dw-btn-outline tw-dw-btn-primary tw-w-max btn-modal no-print" data-container=".view_modal" data-href="' .
                        action([\App\Http\Controllers\ProductController::class, 'viewGroupPrice'], [$row->product_id]) . '">' .
                        __('lang_v1.view_group_prices') . '</button>';
                }

                return $html;
            })
            ->editColumn('stock_price', function ($row) {
                $html = '<span class="total_stock_price" data-orig-value="' . $row->stock_price . '">' .
                    $this->transactionUtil->num_f($row->stock_price, true) . '</span>';
                return $html;
            })
            ->editColumn('stock_value_by_sale_price', function ($row) {
                $stock = $row->stock ? $row->stock : 0;
                $unit_selling_price = (float) $row->group_price > 0 ? $row->group_price : $row->unit_price;
                $stock_price = $stock * $unit_selling_price;

                return '<span class="stock_value_by_sale_price" data-orig-value="' . (float) $stock_price . '" > ' .
                    $this->transactionUtil->num_f($stock_price, true) . '</span>';
            })
            ->addColumn('potential_profit', function ($row) {
                $stock = $row->stock ? $row->stock : 0;
                $unit_selling_price = (float) $row->group_price > 0 ? $row->group_price : $row->unit_price;
                $stock_price_by_sp = $stock * $unit_selling_price;
                $potential_profit = (float) $stock_price_by_sp - (float) $row->stock_price;

                return '<span class="potential_profit" data-orig-value="' . (float) $potential_profit . '" > ' .
                    $this->transactionUtil->num_f($potential_profit, true) . '</span>';
            })
            ->setRowClass(function ($row) {
                return $row->enable_stock && $row->stock <= $row->alert_quantity ? 'bg-danger' : '';
            })
            ->filterColumn('variation', function ($query, $keyword) {
                $query->whereRaw("CONCAT(COALESCE(pv.name, ''), '-', COALESCE(variations.name, '')) like ?", ["%{$keyword}%"]);
            })
            ->filterColumn('stock', function ($query, $keyword) {
                if (request('stock_level') == 'gt_zero') {
                    $query->having('stock', '>', 0);
                } elseif (request('stock_level') == 'lt_one') {
                    $query->having('stock', '<=', 0);
                }
            })
            ->removeColumn('enable_stock')
            ->removeColumn('unit')
            ->removeColumn('id');

        $raw_columns = [
            'unit_price',
            'total_transfered',
            'total_sold',
            'total_adjusted',
            'stock',
            'stock_price',
            'stock_value_by_sale_price',
            'potential_profit',
            'action',
        ];

        // Add manufacturing columns if enabled
        if ($show_manufacturing_data) {
            $datatable->editColumn('total_mfg_stock', function ($row) {
                $total_mfg_stock = 0;
                if ($row->total_mfg_stock) {
                    $total_mfg_stock = (float) $row->total_mfg_stock;
                }

                return '<span data-is_quantity="true" class="total_mfg_stock" data-orig-value="' . $total_mfg_stock . '" data-unit="' . $row->unit . '" >' .
                    $this->transactionUtil->num_f($total_mfg_stock, false, null, true) . '</span> ' . $row->unit;
            });
            $raw_columns[] = 'total_mfg_stock';
        }

        return $datatable->rawColumns($raw_columns)->make(true);
    }

    // Prepare data for the view
    $categories = Category::forDropdown($business_id, 'product');
    $brands = Brands::forDropdown($business_id);
    $units = Unit::where('business_id', $business_id)->pluck('short_name', 'id');
    $business_locations = BusinessLocation::forDropdown($business_id, true);

    return view('report.stock_report')->with(compact(
        'categories',
        'brands',
        'units',
        'business_locations',
        'show_manufacturing_data'
    ));
}

1.4 Add the processEnhancedStockReportFilters Method

Add this method to handle enhanced stock report filtering:

app/Http/Controllers/ReportController.php
/**
 * Process enhanced multi-select filters for stock report
 *
 * @param Request $request
 * @return array
 */
private function processEnhancedStockReportFilters(Request $request)
{
    // Get all possible filters
    $filters = $request->only([
        'location_id',
        'category_id',
        'sub_category_id',
        'brand_id',
        'unit_id',
        'tax_id',
        'type',
        'only_mfg_products',
        'active_state',
        'not_for_selling',
        'repair_model_id',
        'product_id',
        'stock_level'
    ]);

    // Multi-select filters that need special processing
    $multiSelectFilters = ['location_id', 'category_id', 'sub_category_id', 'brand_id', 'unit_id'];

    foreach ($multiSelectFilters as $filter) {
        if (isset($filters[$filter])) {
            // Handle different input formats
            if (is_string($filters[$filter])) {
                // Handle comma-separated strings
                $filters[$filter] = explode(',', $filters[$filter]);
            }

            // Ensure it's an array
            if (!is_array($filters[$filter])) {
                $filters[$filter] = [$filters[$filter]];
            }

            // Remove empty values, null values, and "All" selections ('')
            $filters[$filter] = array_filter($filters[$filter], function ($value) {
                return $value !== '' && $value !== null && $value !== '0' && trim($value) !== '';
            });

            // If no valid values remain, remove the filter (means "All")
            if (empty($filters[$filter])) {
                unset($filters[$filter]);
            } else {
                // Re-index array and convert to proper types
                $filters[$filter] = array_values(array_map('intval', $filters[$filter]));
            }
        }
    }

    // Handle boolean and special filters
    $filters['not_for_selling'] = isset($filters['not_for_selling']) &&
        ($filters['not_for_selling'] == 'true' || $filters['not_for_selling'] == '1') ? 1 : 0;

    $filters['only_mfg_products'] = isset($filters['only_mfg_products']) &&
        ($filters['only_mfg_products'] == '1' || $filters['only_mfg_products'] === true) ? 1 : 0;

    // Handle stock level filter
    if (isset($filters['stock_level']) && empty(trim($filters['stock_level']))) {
        unset($filters['stock_level']);
    }

    // Handle active state
    if (isset($filters['active_state']) && empty(trim($filters['active_state']))) {
        unset($filters['active_state']);
    }

    // Handle type filter
    if (isset($filters['type']) && empty(trim($filters['type']))) {
        unset($filters['type']);
    }

    // Remove any remaining empty or null values
    $filters = array_filter($filters, function ($value) {
        if (is_array($value)) {
            return !empty($value);
        }
        return $value !== '' && $value !== null;
    });

    return $filters;
}

Step 2: Update TransactionUtil.php

Update your getOpeningClosingStock method to support multi-select filters:

app/Utils/TransactionUtil.php
public function getOpeningClosingStock($business_id, $date, $location_id, $is_opening = false, $by_sale_price = false, $filters = [], $permitted_locations = null)
{
    $query = PurchaseLine::join(
        'transactions as purchase',
        'purchase_lines.transaction_id',
        '=',
        'purchase.id'
    )
        ->where('purchase.type', '!=', 'purchase_order')
        ->where('purchase.business_id', $business_id);

    $price_query_part = '(purchase_lines.purchase_price +
                    COALESCE(purchase_lines.item_tax, 0))';

    if ($by_sale_price) {
        $price_query_part = 'v.sell_price_inc_tax';
    }

    $query->leftjoin('variations as v', 'v.id', '=', 'purchase_lines.variation_id')
        ->leftjoin('products as p', 'p.id', '=', 'purchase_lines.product_id');

    // Handle multi-select filters
    if (!empty($filters['category_id'])) {
        if (is_array($filters['category_id'])) {
            $query->whereIn('p.category_id', $filters['category_id']);
        } else {
            $query->where('p.category_id', $filters['category_id']);
        }
    }

    if (!empty($filters['sub_category_id'])) {
        if (is_array($filters['sub_category_id'])) {
            $query->whereIn('p.sub_category_id', $filters['sub_category_id']);
        } else {
            $query->where('p.sub_category_id', $filters['sub_category_id']);
        }
    }

    if (!empty($filters['brand_id'])) {
        if (is_array($filters['brand_id'])) {
            $query->whereIn('p.brand_id', $filters['brand_id']);
        } else {
            $query->where('p.brand_id', $filters['brand_id']);
        }
    }

    if (!empty($filters['unit_id'])) {
        if (is_array($filters['unit_id'])) {
            $query->whereIn('p.unit_id', $filters['unit_id']);
        } else {
            $query->where('p.unit_id', $filters['unit_id']);
        }
    }

    if (!empty($filters['user_id'])) {
        $query->where('purchase.created_by', $filters['user_id']);
    }

    //If opening
    if ($is_opening) {
        $next_day = \Carbon::createFromFormat('Y-m-d', $date)->addDay()->format('Y-m-d');

        $query->where(function ($query) use ($date, $next_day) {
            $query->whereRaw("date(transaction_date) <= '$date'")
                ->orWhereRaw("date(transaction_date) = '$next_day' AND purchase.type='opening_stock' ");
        });
    } else {
        $query->whereRaw("date(transaction_date) <= '$date'");
    }

    $query->select(
        DB::raw("SUM((purchase_lines.quantity - purchase_lines.quantity_returned - purchase_lines.quantity_adjusted -
                    (SELECT COALESCE(SUM(tspl.quantity - tspl.qty_returned), 0) FROM
                    transaction_sell_lines_purchase_lines AS tspl
                    JOIN transaction_sell_lines as tsl ON
                    tspl.sell_line_id=tsl.id
                    JOIN transactions as sale ON
                    tsl.transaction_id=sale.id
                    WHERE tspl.purchase_line_id = purchase_lines.id AND
                    date(sale.transaction_date) <= '$date') ) * $price_query_part
                ) as stock")
    );

    //Check for permitted locations of a user
    if (!empty($permitted_locations)) {
        if ($permitted_locations != 'all') {
            $query->whereIn('purchase.location_id', $permitted_locations);
        }
    }

    // Updated location_id handling to support arrays
    if (!empty($location_id)) {
        if (is_array($location_id)) {
            // Handle multiple locations
            $query->whereIn('purchase.location_id', $location_id);
        } else {
            // Original code for a single location
            $query->where('purchase.location_id', $location_id);
        }
    }

    $details = $query->first();

    return $details->stock;
}

Step 3: Update the Blade View

Replace your resources/views/report/stock_report.blade.php file with this enhanced version:

resources/views/report/stock_report.blade.php
@extends('layouts.app')
@section('title', __('report.stock_report'))

@section('content')

<!-- Content Header (Page header) -->
<section class="content-header">
    <h1 class="tw-text-xl md:tw-text-3xl tw-font-bold tw-text-black">{{ __('report.stock_report')}}</h1>
</section>

<!-- Main content -->
<section class="content">
    <div class="row">
        <div class="col-md-12">
            @component('components.filters', ['title' => __('report.filters')])
            {!! Form::open(['url' => action([\App\Http\Controllers\ReportController::class, 'getStockReport']), 'method'
            => 'get', 'id' => 'stock_report_filter_form' ]) !!}
            <div class="col-md-3">
                <div class="form-group">
                    {!! Form::label('location_id', __('purchase.business_location') . ':') !!}
                    {!! Form::select('location_id[]', $business_locations, null, [
                    'class' => 'form-control select2',
                    'style' => 'width:100%',
                    'multiple',
                    'id' => 'location_id'
                    ]); !!}
                </div>
            </div>

            <div class="col-md-3">
                <div class="form-group">
                    {!! Form::label('category_id', __('category.category') . ':') !!}
                    {!! Form::select('category_id[]', $categories, null, [
                    'placeholder' => __('messages.all'),
                    'class' => 'form-control select2',
                    'style' => 'width:100%',
                    'id' => 'category_id',
                    'multiple'
                    ]); !!}
                </div>
            </div>

            <div class="col-md-3">
                <div class="form-group">
                    {!! Form::label('sub_category_id', __('product.sub_category') . ':') !!}
                    {!! Form::select('sub_category_id[]', array(), null, [
                    'placeholder' => __('messages.all'),
                    'class' => 'form-control select2',
                    'style' => 'width:100%',
                    'id' => 'sub_category_id',
                    'multiple'
                    ]); !!}
                </div>
            </div>

            <div class="col-md-3">
                <div class="form-group">
                    {!! Form::label('brand', __('product.brand') . ':') !!}
                    {!! Form::select('brand[]', $brands, null, [
                    'placeholder' => __('messages.all'),
                    'class' => 'form-control select2',
                    'style' => 'width:100%',
                    'id' => 'brand',
                    'multiple'
                    ]); !!}
                </div>
            </div>

            <div class="col-md-3">
                <div class="form-group">
                    {!! Form::label('unit',__('product.unit') . ':') !!}
                    {!! Form::select('unit[]', $units, null, [
                    'placeholder' => __('messages.all'),
                    'class' => 'form-control select2',
                    'style' => 'width:100%',
                    'id' => 'unit',
                    'multiple'
                    ]); !!}
                </div>
            </div>
            <div class="col-md-3">
                <div class="form-group">
                    {!! Form::label('stock_level', __('report.stock_level') . ':') !!}
                    {!! Form::select('stock_level', [
                    '' => __('messages.all'),
                    'gt_zero' => __('report.greater_than_zero'),
                    'lt_one' => __('report.less_than_one')
                    ], null, ['class' => 'form-control select2', 'style' => 'width:100%', 'id' => 'stock_level']); !!}
                </div>
            </div>
            @if($show_manufacturing_data)
            <div class="col-md-3">
                <div class="form-group">
                    <br>
                    <div class="checkbox">
                        <label>
                            {!! Form::checkbox('only_mfg', 1, false,
                            [ 'class' => 'input-icheck', 'id' => 'only_mfg_products']); !!} {{
                            __('manufacturing::lang.only_mfg_products') }}
                        </label>
                    </div>
                </div>
            </div>
            @endif
            {!! Form::close() !!}
            @endcomponent
        </div>
    </div>
    @can('view_product_stock_value')
    <div class="row">
        <div class="col-md-12">
            @component('components.widget', ['class' => 'box-solid'])
            <table class="table no-border">
                <tr>
                    <td>@lang('report.closing_stock') (@lang('lang_v1.by_purchase_price'))</td>
                    <td>@lang('report.closing_stock') (@lang('lang_v1.by_sale_price'))</td>
                    <td>@lang('lang_v1.potential_profit')</td>
                    <td>@lang('lang_v1.profit_margin')</td>
                </tr>
                <tr>
                    <td>
                        <h3 id="closing_stock_by_pp" class="mb-0 mt-0"></h3>
                    </td>
                    <td>
                        <h3 id="closing_stock_by_sp" class="mb-0 mt-0"></h3>
                    </td>
                    <td>
                        <h3 id="potential_profit" class="mb-0 mt-0"></h3>
                    </td>
                    <td>
                        <h3 id="profit_margin" class="mb-0 mt-0"></h3>
                    </td>
                </tr>
            </table>
            @endcomponent
        </div>
    </div>
    @endcan
    <div class="row">
        <div class="col-md-12">
            @component('components.widget', ['class' => 'box-solid'])
            @include('report.partials.stock_report_table')
            @endcomponent
        </div>
    </div>
</section>
<!-- /.content -->

@endsection

@section('javascript')
<script src="{{ asset('js/report.js?v=' . $asset_v) }}"></script>

<script type="text/javascript">
    $(document).ready(function() {
    // ROBUST SUBCATEGORY SOLUTION - Prevents clearing issues

    var subcategoryUpdateInProgress = false;
    var subcategoryUpdateTimeout = null;
    var lastCategoryState = '';

    function updateSubcategoryOptionsRobust() {
        var selectedCategories = $('#category_id').val() || [];
        var currentCategoryState = JSON.stringify(selectedCategories.sort());

        // Prevent duplicate updates for same category state
        if (currentCategoryState === lastCategoryState && !subcategoryUpdateInProgress) {
            return;
        }

        // Prevent multiple simultaneous updates
        if (subcategoryUpdateInProgress) {
            // Retry after current update completes
            setTimeout(function() {
                if (!subcategoryUpdateInProgress) {
                    updateSubcategoryOptionsRobust();
                }
            }, 500);
            return;
        }

        lastCategoryState = currentCategoryState;

        // Handle empty/All selection
        if (selectedCategories.length === 0 || selectedCategories.includes('')) {
            setSubcategoryToAll();
            return;
        }

        subcategoryUpdateInProgress = true;

        var $subcategorySelect = $('#sub_category_id');

        // Store current selection and options BEFORE making any changes
        var currentSelection = $subcategorySelect.val() || [];
        var currentOptions = [];
        $subcategorySelect.find('option').each(function() {
            currentOptions.push({
                value: $(this).val(),
                text: $(this).text()
            });
        });

        // Add loading indicator WITHOUT clearing existing options
        if (!$subcategorySelect.find('option[value="loading"]').length) {
            $subcategorySelect.append($('<option>', {
                value: 'loading',
                text: 'Loading...',
                disabled: true
            }));
            $subcategorySelect.prop('disabled', true);
        }

        // Make AJAX request
        $.ajax({
            url: '/products/get_sub_categories_multi',
            method: 'POST',
            data: {
                cat_ids: selectedCategories,
                _token: $('meta[name="csrf-token"]').attr('content') || ''
            },
            dataType: 'json',
            timeout: 10000,
            success: function(data) {
                if (data && typeof data === 'object') {
                    // Build new options list
                    var newOptions = [{ value: '', text: '-- All --' }];

                    // Add subcategories from response, sorted by name
                    var sortedKeys = Object.keys(data).sort(function(a, b) {
                        return data[a].localeCompare(data[b]);
                    });

                    sortedKeys.forEach(function(key) {
                        newOptions.push({
                            value: key,
                            text: data[key]
                        });
                    });

                    // Now safely replace all options at once
                    $subcategorySelect.empty();
                    newOptions.forEach(function(option) {
                        $subcategorySelect.append($('<option>', {
                            value: option.value,
                            text: option.text
                        }));
                    });

                    // Try to restore previous selection
                    var validSelections = currentSelection.filter(function(val) {
                        return newOptions.some(function(opt) { return opt.value === val; });
                    });

                    if (validSelections.length > 0) {
                        $subcategorySelect.val(validSelections);
                    } else {
                        $subcategorySelect.val('');
                    }
                } else {
                    $subcategorySelect.empty().append($('<option>', {
                        value: '',
                        text: '-- All --'
                    })).val('');
                }

                // Re-enable and refresh
                $subcategorySelect.prop('disabled', false);
                $subcategorySelect.trigger('change.select2');

            },
            error: function(xhr, status, error) {
                // Restore previous options on error
                $subcategorySelect.empty();
                currentOptions.forEach(function(option) {
                    if (option.value !== 'loading') {
                        $subcategorySelect.append($('<option>', {
                            value: option.value,
                            text: option.text
                        }));
                    }
                });
                $subcategorySelect.val(currentSelection);
                $subcategorySelect.prop('disabled', false);
                $subcategorySelect.trigger('change.select2');

                // Show user-friendly error
                if (typeof toastr !== 'undefined') {
                    toastr.error('Error loading subcategories');
                }
            },
            complete: function() {
                subcategoryUpdateInProgress = false;
            }
        });
    }

    function setSubcategoryToAll() {
        var $subcategorySelect = $('#sub_category_id');

        $subcategorySelect.empty().append($('<option>', {
            value: '',
            text: '-- All --'
        })).val('').trigger('change.select2');
    }

    // Debounced category change handler
    $('#category_id').on('change', function(e, extraData) {
        if (extraData && extraData.skipSubcategoryUpdate) {
            return;
        }

        // Clear any pending timeout
        if (subcategoryUpdateTimeout) {
            clearTimeout(subcategoryUpdateTimeout);
        }

        // Debounce the update (wait for user to finish selecting)
        subcategoryUpdateTimeout = setTimeout(function() {
            updateSubcategoryOptionsRobust();
        }, 300); // 300ms delay
    });

    // Enhanced multi-select filter handling
    function setupRobustMultiSelectFilter(elementId) {
        var $element = $('#' + elementId);
        var changeTimeout = null;

        $element.on('select2:select select2:unselect', function(e) {
            // Clear any pending timeout
            if (changeTimeout) {
                clearTimeout(changeTimeout);
            }

            // Debounce the logic handling
            changeTimeout = setTimeout(function() {
                handleMultiSelectLogic(elementId);
            }, 100);
        });
    }

    function handleMultiSelectLogic(elementId) {
        var $element = $('#' + elementId);
        var currentValues = $element.val() || [];

        if (currentValues.length === 0) {
            $element.data('prev-values', []);
            return;
        }

        var hasAll = currentValues.includes('');
        var hasSpecific = currentValues.some(val => val !== '');

        if (hasAll && hasSpecific) {
            var previousValues = $element.data('prev-values') || [];
            var wasAllPreviously = previousValues.includes('');

            if (wasAllPreviously) {
                // User added specific to "All" - remove "All"
                var specificValues = currentValues.filter(val => val !== '');
                $element.val(specificValues);
                $element.trigger('change.select2');

            } else {
                // User added "All" to specific - remove specific
                $element.val(['']);
                $element.trigger('change.select2');
            }
        }

        $element.data('prev-values', $element.val() || []);
    }

    // Initialize robust filters
    function initializeRobustFilters() {
        // Initialize previous values
        ['location_id', 'category_id', 'sub_category_id', 'brand', 'unit'].forEach(function(id) {
            $('#' + id).data('prev-values', $('#' + id).val() || []);
        });

        // Setup robust handlers
        setupRobustMultiSelectFilter('location_id');
        setupRobustMultiSelectFilter('category_id');
        setupRobustMultiSelectFilter('sub_category_id');
        setupRobustMultiSelectFilter('brand');
        setupRobustMultiSelectFilter('unit');
    }

    // Function to get stock value with multi-select filters
    function get_stock_value() {
        var loader = '<i class="fas fa-spinner fa-spin"></i>';
        $('.card-value').html(loader);

        // Get filter values and CLEAN them immediately
        var locationIds = ($('#location_id').val() || []).filter(v => v !== '' && v !== null);
        var categoryIds = ($('#category_id').val() || []).filter(v => v !== '' && v !== null);
        var subCategoryIds = ($('#sub_category_id').val() || []).filter(v => v !== '' && v !== null);
        var brandIds = ($('#brand').val() || []).filter(v => v !== '' && v !== null);
        var unitIds = ($('#unit').val() || []).filter(v => v !== '' && v !== null);

        // Build data object only with non-empty filters
        var data = {};

        if (locationIds.length > 0) data.location_id = locationIds;
        if (categoryIds.length > 0) data.category_id = categoryIds;
        if (subCategoryIds.length > 0) data.sub_category_id = subCategoryIds;
        if (brandIds.length > 0) data.brand_id = brandIds;
        if (unitIds.length > 0) data.unit_id = unitIds;

        $.ajax({
            url: '/reports/get-stock-value',
            data: data,
            success: function(response) {
                // Use the pre-formatted values from backend
                $('#closing_stock_by_pp').text(response.closing_stock_by_pp || '0.00');
                $('#closing_stock_by_sp').text(response.closing_stock_by_sp || '0.00');
                $('#potential_profit').text(response.potential_profit || '0.00');
                $('#profit_margin').text(response.profit_margin + '%' || '0.0%');

                // Update price group comparison if functions exist
                if (typeof allPriceGroupData !== 'undefined') {
                    allPriceGroupData = response;
                    if (typeof updatePriceGroupDisplay === 'function') {
                        updatePriceGroupDisplay('default');
                    }
                    if (typeof renderPriceGroupComparison === 'function') {
                        renderPriceGroupComparison();
                    }
                }
            },
            error: function(xhr, status, error) {
                $('.card-value').text('Error');
            }
        });
    }

    function getProcessedFilterValues() {
        var locationIds = $('#location_id').val() || [];
        var categoryIds = $('#category_id').val() || [];
        var subCategoryIds = $('#sub_category_id').val() || [];
        var brandIds = $('#brand').val() || [];
        var unitIds = $('#unit').val() || [];
        var stockLevel = $('#stock_level').val();

        return {
            location_id: (locationIds.includes('') || locationIds.length === 0) ? null : locationIds,
            category_id: (categoryIds.includes('') || categoryIds.length === 0) ? null : categoryIds,
            sub_category_id: (subCategoryIds.includes('') || subCategoryIds.length === 0) ? null : subCategoryIds,
            brand_id: (brandIds.includes('') || brandIds.length === 0) ? null : brandIds,
            unit_id: (unitIds.includes('') || unitIds.length === 0) ? null : unitIds,
            stock_level: stockLevel || null
        };
    }

    // Filter change handlers
    $('#stock_report_filter_form #location_id, #stock_report_filter_form #category_id, #stock_report_filter_form #sub_category_id, #stock_report_filter_form #brand, #stock_report_filter_form #unit, #stock_report_filter_form #stock_level').change(function(e, extraData) {
        if (extraData && extraData.skipSubcategoryUpdate) {
            return;
        }

        // Reload tables
        if (typeof stock_report_table !== 'undefined') {
            stock_report_table.ajax.reload();
        }
        if (typeof stock_expiry_report_table !== 'undefined') {
            stock_expiry_report_table.ajax.reload();
        }

        // Update stock values
        get_stock_value();
    });

    // Initialize everything
    initializeRobustFilters();

    // Initialize stock value if element exists
    if ($('#closing_stock_by_pp').length > 0) {
        setTimeout(get_stock_value, 1000);
    }

    // Expose functions globally
    window.updateSubcategoryOptionsRobust = updateSubcategoryOptionsRobust;
    window.get_stock_value = get_stock_value;

});
</script>
@endsection

Step 4: Update public/js/report.js

Update your public/js/report.js file to include the stock level filter in the DataTable configuration and change handlers.

4.1 Add Stock Level to DataTable Configuration

Find your stock_report_table DataTable initialization and add the stock level parameter:

public/js/report.js
stock_report_table = $('#stock_report_table').DataTable({
    // ... existing configuration
    ajax: {
        url: '/reports/stock-report',
        data: function (d) {
            // ... existing parameters
            d.stock_level = $('#stock_level').val();
            // ... other parameters
        },
    },
    // ... rest of configuration
});

4.2 Update Filter Change Handlers

Update the change event handler to include the stock level filter:

public/js/report.js
$(
    '#stock_report_filter_form #location_id, #stock_report_filter_form #category_id, #stock_report_filter_form #sub_category_id, #stock_report_filter_form #brand, #stock_report_filter_form #unit, #stock_report_filter_form #stock_level, #stock_report_filter_form #view_stock_filter'
).change(function () {
    stock_report_table.ajax.reload();
    stock_expiry_report_table.ajax.reload();
    get_stock_value();
});

Step 5: Add Subcategory Methods to ProductController

If you don't already have these methods, add them to your ProductController to handle dynamic subcategory loading:

5.1 Add getSubCategories Method

app/Http/Controllers/ProductController.php
/**
 * Get subcategories based on selected categories
 */
public function getSubCategories(Request $request)
{
    if (request()->ajax()) {
        $term = request()->input('term', '');
        $cat_id = request()->input('cat_id', '');

        $business_id = request()->session()->get('user.business_id');

        $sub_categories = Category::where('business_id', $business_id)
            ->where('category_type', 'product');

        if (!empty($cat_id)) {
            $sub_categories->where('parent_id', $cat_id);
        }

        if (!empty($term)) {
            $sub_categories->where('name', 'like', '%' . $term . '%');
        }

        $sub_categories = $sub_categories->select('name', 'short_code', 'id')
            ->get();

        return json_encode($sub_categories);
    }
}

5.2 Add getSubCategoriesMulti Method

app/Http/Controllers/ProductController.php
/**
 * Get subcategories for multiple selected categories
 */
public function getSubCategoriesMulti(Request $request)
{
    if (request()->ajax()) {
        $cat_ids = request()->input('cat_ids', []);
        $business_id = request()->session()->get('user.business_id');

        if (empty($cat_ids)) {
            return response()->json([]);
        }

        $sub_categories = Category::where('business_id', $business_id)
            ->where('category_type', 'product')
            ->whereIn('parent_id', $cat_ids)
            ->pluck('name', 'id')
            ->toArray();

        return response()->json($sub_categories);
    }
}

5.3 Add Routes for Subcategory Methods

Add these routes to your routes/web.php if they don't exist:

routes/web.php
Route::get('/products/get-sub-categories', [\App\Http\Controllers\ProductController::class, 'getSubCategories']);
Route::post('/products/get_sub_categories_multi', [\App\Http\Controllers\ProductController::class, 'getSubCategoriesMulti']);

How It Works

Multi-Select Filter Processing

Frontend Collection: JavaScript collects values from multi-select dropdowns
Array Filtering: Empty values and "All" selections are filtered out
Backend Processing: PHP processes arrays and single values consistently
Database Querying: Uses whereIn() for arrays and where() for single values

Stock Value Calculation

Filter Processing: Enhanced filters are processed and cleaned
Purchase Price Calculation: Uses getOpeningClosingStock() with by_sale_price = false
Sale Price Calculation: Uses getOpeningClosingStock() with by_sale_price = true
Currency Formatting: Backend formats values using num_f(value, true) for currency display

"All" Option Logic

Empty dropdown selection = "All" (no filter applied)
Selecting "All" with other options removes the other options
Backend treats empty arrays as no filter applied

Key Features

Multi-Select Capabilities

Multiple Locations: Filter by multiple business locations simultaneously
Multiple Categories: Select multiple product categories
Multiple Brands: Filter by multiple brands at once
Multiple Units: Filter by different unit types

Enhanced Stock Value Display

Purchase Price Value: Total stock value at purchase price
Sale Price Value: Total stock value at selling price
Potential Profit: Difference between sale and purchase values
Profit Margin: Percentage profit margin with proper formatting

Robust Error Handling

AJAX Error Management: Graceful error handling for network issues
Filter Validation: Proper validation of filter inputs
Currency Display: Consistent currency formatting across all values

Benefits

Improved User Experience: Intuitive multi-select interface with clear options
Better Data Analysis: Multiple dimension filtering for comprehensive insights
Consistent Formatting: Proper currency display with business-specific symbols
Enhanced Performance: Optimized queries for multi-select filters
Maintainable Code: Clean, well-structured code following Laravel best practices

This enhancement significantly improves the stock reporting capabilities while maintaining backward compatibility and following established patterns in your Ultimate POS application.

Overview​

What This Enhancement Adds​

Benefits​

Implementation Steps​

Step 1: Update ReportController.php​

1.1 Add the Enhanced getStockValue Method​

1.2 Add the processStockValueFilters Method​

1.3 Update the getStockReport Method​

1.4 Add the processEnhancedStockReportFilters Method​

Step 2: Update TransactionUtil.php​

Step 3: Update the Blade View​

Step 4: Update public/js/report.js​

4.1 Add Stock Level to DataTable Configuration​

4.2 Update Filter Change Handlers​

Step 5: Add Subcategory Methods to ProductController​

5.1 Add getSubCategories Method​

5.2 Add getSubCategoriesMulti Method​

5.3 Add Routes for Subcategory Methods​

How It Works​

Multi-Select Filter Processing​

Stock Value Calculation​

"All" Option Logic​

Key Features​

Multi-Select Capabilities​

Enhanced Stock Value Display​

Robust Error Handling​

Benefits​

Overview

What This Enhancement Adds

Benefits

Implementation Steps

Step 1: Update ReportController.php

1.1 Add the Enhanced getStockValue Method

1.2 Add the processStockValueFilters Method

1.3 Update the getStockReport Method

1.4 Add the processEnhancedStockReportFilters Method

Step 2: Update TransactionUtil.php

Step 3: Update the Blade View

Step 4: Update public/js/report.js

4.1 Add Stock Level to DataTable Configuration

4.2 Update Filter Change Handlers

Step 5: Add Subcategory Methods to ProductController

5.1 Add getSubCategories Method

5.2 Add getSubCategoriesMulti Method

5.3 Add Routes for Subcategory Methods

How It Works

Multi-Select Filter Processing

Stock Value Calculation

"All" Option Logic

Key Features

Multi-Select Capabilities

Enhanced Stock Value Display

Robust Error Handling

Benefits