Adding Price Groups to Product List And Purchase

This guide covers how to implement dynamic selling price groups in the Ultimate POS product list and integrate them with the purchase system.

Overview

The price groups feature allows you to:

Display multiple selling price groups as columns in the product list
Manage group-specific pricing for products
Update price group values from purchase operations

Product list

Edit Purchase

Add Purchase

📦 Download Starter Files

price-groups-in-products-purchase.zip

Prerequisites

Ultimate POS Laravel application
Basic understanding of Laravel, Blade templates, and DataTables
Database with selling_price_groups and variation_group_prices tables

Step 1: Display Price Groups in Product List

1.1 Modify the Product List View Template

File: resources/views/product/partials/product_list.blade.php

Add the dynamic price group headers after the existing table headers:

@foreach ($price_groups as $price_group_key => $price_group)
    @php 
        $colspan++;
    @endphp
    <th>{{ $price_group->name }}</th>
@endforeach

Location: Insert this code block in the table header section, typically after the standard product columns (SKU, Category, Brand, etc.).

Here is the complete code for product_list.blade.php:

@php 
    $colspan = 15;
    $custom_labels = json_decode(session('business.custom_labels'), true);
@endphp
<table class="table table-bordered table-striped ajax_view hide-footer" id="product_table">
    <thead>
        <tr>
            <th><input type="checkbox" id="select-all-row" data-table-id="product_table"></th>
            <th>{{__('lang_v1.product_image')}}</th>
            <th>@lang('messages.action')</th>
            <th>@lang('sale.product')</th>
           
            @can('view_purchase_price')
                @php 
                    $colspan++;
                @endphp
                <th>@lang('lang_v1.unit_perchase_price')</th>
            @endcan
            @can('access_default_selling_price')
                @php 
                    $colspan++;
                @endphp
                <th>@lang('lang_v1.selling_price')</th>
            @endcan
            <th>@lang('report.current_stock')</th>
            <th>@lang('product.product_type')</th>
            
            @foreach ($price_groups as $price_group_key => $price_group)
                @php 
                    $colspan++;
                @endphp
                <th>{{ $price_group->name }}</th>
            @endforeach

            <th>@lang('product.category')</th>
            <th>@lang('product.brand')</th>
            <th>@lang('product.tax')</th>
            <th>@lang('product.sku')</th>
            <th>
                @lang('purchase.business_location') 
                @show_tooltip(__('lang_v1.product_business_location_tooltip'))
            </th>
            <th id="cf_1">{{ $custom_labels['product']['custom_field_1'] ?? '' }}</th>
            <th id="cf_2">{{ $custom_labels['product']['custom_field_2'] ?? '' }}</th>
            <th id="cf_3">{{ $custom_labels['product']['custom_field_3'] ?? '' }}</th>
            <th id="cf_4">{{ $custom_labels['product']['custom_field_4'] ?? '' }}</th>
            <th id="cf_5">{{ $custom_labels['product']['custom_field_5'] ?? '' }}</th>
            <th id="cf_6">{{ $custom_labels['product']['custom_field_6'] ?? '' }}</th>
            <th id="cf_7">{{ $custom_labels['product']['custom_field_7'] ?? '' }}</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <td colspan="{{$colspan}}">
                <div style="display: flex; width: 100%;">
                    @can('product.delete')
                        {!! Form::open(['url' => action([\App\Http\Controllers\ProductController::class, 'massDestroy']), 'method' => 'post', 'id' => 'mass_delete_form' ]) !!}
                        {!! Form::hidden('selected_rows', null, ['id' => 'selected_rows']); !!}
                        {!! Form::submit(__('lang_v1.delete_selected'), array('class' => 'tw-dw-btn tw-dw-btn-outline tw-dw-btn-xs tw-dw-btn-error', 'id' => 'delete-selected')) !!}
                        {!! Form::close() !!}
                    @endcan

                    @can('product.update')
                        @if(config('constants.enable_product_bulk_edit'))
                            &nbsp;
                            {!! Form::open(['url' => action([\App\Http\Controllers\ProductController::class, 'bulkEdit']), 'method' => 'post', 'id' => 'bulk_edit_form' ]) !!}
                            {!! Form::hidden('selected_products', null, ['id' => 'selected_products_for_edit']); !!}
                            <button type="submit" class="tw-dw-btn tw-dw-btn-xs tw-dw-btn-outline tw-dw-btn-primary" id="edit-selected"> <i class="fa fa-edit"></i>{{__('lang_v1.bulk_edit')}}</button>
                            {!! Form::close() !!}
                        @endif
                        &nbsp;
                        <button type="button" class="tw-dw-btn tw-dw-btn-xs tw-dw-btn-outline tw-dw-btn-accent update_product_location" data-type="add">@lang('lang_v1.add_to_location')</button>
                        &nbsp;
                        <button type="button" class="tw-dw-btn tw-dw-btn-xs tw-dw-btn-outline tw-dw-btn-neutral update_product_location" data-type="remove">@lang('lang_v1.remove_from_location')</button>
                    @endcan
                
                    &nbsp;
                    {!! Form::open(['url' => action([\App\Http\Controllers\ProductController::class, 'massDeactivate']), 'method' => 'post', 'id' => 'mass_deactivate_form' ]) !!}
                    {!! Form::hidden('selected_products', null, ['id' => 'selected_products']); !!}
                    {!! Form::submit(__('lang_v1.deactivate_selected'), array('class' => 'tw-dw-btn tw-dw-btn-xs tw-dw-btn-outline tw-dw-btn-warning', 'id' => 'deactivate-selected')) !!}
                    {!! Form::close() !!} @show_tooltip(__('lang_v1.deactive_product_tooltip'))
                    &nbsp;
                    @if($is_woocommerce)
                        <button type="button" class="tw-dw-btn tw-dw-btn-xs tw-dw-btn-outline tw-dw-btn-warning toggle_woocomerce_sync">
                            @lang('lang_v1.woocommerce_sync')
                        </button>
                    @endif
                </div>
            </td>
        </tr>
    </tfoot>
</table>

1.2 Update DataTable Column Configuration

File: resources/views/product/index.blade.php

Add the dynamic DataTable columns configuration:

@foreach ($price_groups as $price_group)
    {
        data: 'group_price{{$price_group->id}}',
        name: 'group_price{{$price_group->id}}',
        searchable: false,
        orderable: false,
        width: '80px'
    },
@endforeach

Location: Insert this within the DataTable columns array configuration, after the existing column definitions. Here is the complete code for the new DataTable columns:

product_table = $('#product_table').DataTable({
    processing: true,
    serverSide: true,
    fixedHeader: false,
    aaSorting: [
        [3, 'asc']
    ],
    scrollY: "75vh",
    scrollX: true,
    scrollCollapse: true,
    "ajax": {
        "url": "/products",
        "data": function(d) {
            d.type = $('#product_list_filter_type').val();
            d.category_id = $('#product_list_filter_category_id').val();
            d.brand_id = $('#product_list_filter_brand_id').val();
            d.unit_id = $('#product_list_filter_unit_id').val();
            d.tax_id = $('#product_list_filter_tax_id').val();
            d.active_state = $('#active_state').val();
            d.not_for_selling = $('#not_for_selling').is(':checked');
            d.location_id = $('#location_id').val();
            if ($('#repair_model_id').length == 1) {
                d.repair_model_id = $('#repair_model_id').val();
            }

            if ($('#woocommerce_enabled').length == 1 && $('#woocommerce_enabled').is(':checked')) {
                d.woocommerce_enabled = 1;
            }

            d = __datatable_ajax_callback(d);
        }
    },
    columnDefs: [{
        "targets": [0, 1, 2],
        "orderable": false,
        "searchable": false
    }],
    columns: [{
            data: 'mass_delete'
        },
        {
            data: 'image',
            name: 'products.image',
            width: '40px'
        },
        {
            data: 'action',
            name: 'action',
            width: '60px'
        },
        {
            data: 'product',
            name: 'products.name',
            width: '160px'
        },
       
        @can('view_purchase_price')
            {
                data: 'purchase_price',
                name: 'max_purchase_price',
                searchable: false,
                width: '60px'
            },
        @endcan
        @can('access_default_selling_price')
            {
                data: 'selling_price',
                name: 'max_price',
                searchable: false,
                width: '60px'
            },
        @endcan 
        {
            data: 'current_stock',
            searchable: false,
            width: '80px'
        },
        {
            data: 'type',
            name: 'products.type'
        },

        @foreach ($price_groups as $price_group)
            {
                data: 'group_price{{$price_group->id}}',
                name: 'group_price{{$price_group->id}}',
                searchable: false,
                orderable: false,
                width: '80px'
            },
        @endforeach

        {
            data: 'category',
            name: 'c1.name'
        },
        {
            data: 'brand',
            name: 'brands.name'
        },
        {
            data: 'tax',
            name: 'tax_rates.name',
            searchable: false
        },
        {
            data: 'sku',
            name: 'products.sku'
        },
       {
            data: 'product_locations',
            name: 'product_locations'
        },
        {
            data: 'product_custom_field1',
            name: 'products.product_custom_field1',
            visible: $('#cf_1').text().length > 0
        },
        {
            data: 'product_custom_field2',
            name: 'products.product_custom_field2',
            visible: $('#cf_2').text().length > 0
        },
        {
            data: 'product_custom_field3',
            name: 'products.product_custom_field3',
            visible: $('#cf_3').text().length > 0
        },
        {
            data: 'product_custom_field4',
            name: 'products.product_custom_field4',
            visible: $('#cf_4').text().length > 0
        },
        {
            data: 'product_custom_field5',
            name: 'products.product_custom_field5',
            visible: $('#cf_5').text().length > 0
        },
        {
            data: 'product_custom_field6',
            name: 'products.product_custom_field6',
            visible: $('#cf_6').text().length > 0
        },
        {
            data: 'product_custom_field7',
            name: 'products.product_custom_field7',
            visible: $('#cf_7').text().length > 0
        },
    ],
    createdRow: function(row, data, dataIndex) {
        if ($('input#is_rack_enabled').val() == 1) {
            var target_col = 0;
            @can('product.delete')
                target_col = 1;
            @endcan
            $(row).find('td:eq(' + target_col + ') div').prepend(
                '<i style="margin:auto;" class="fa fa-plus-circle text-success cursor-pointer no-print rack-details" title="' +
                LANG.details + '"></i>&nbsp;&nbsp;');
        }
        $(row).find('td:eq(0)').attr('class', 'selectable_td');
    },
    fnDrawCallback: function(oSettings) {
        __currency_convert_recursively($('#product_table'));
    },
});

1.3 Enhanced Controller Implementation

File: app/Http/Controllers/ProductController.php

Key Changes in the `index()` method:

1. Add Price Groups Query

Before handling AJAX requests (if (request()->ajax()) {), add:

// Get price groups for the business
$price_groups = SellingPriceGroup::where('business_id', $business_id)->get();

2. Dynamic Price Group Columns

Add this code after the existing column definitions:

Important Note: The original Ultimate POS code uses ->rawColumns([...]) directly in the DataTables chain. The enhanced version uses a $raw_columns variable for better management of dynamic columns.

Original Pattern:

return Datatables::of($products)
    // ... column definitions ...
    ->rawColumns(['action', 'image', 'mass_delete', 'product', 'selling_price', 'purchase_price', 'category', 'current_stock'])
    ->make(true);

Enhanced Pattern with Price Groups:

// Define base raw columns
$raw_columns = [
    'action', 'image', 'sku', 'mass_delete', 'product', 
    'selling_price', 'purchase_price', 'category', 'current_stock',
];

// Create DataTables instance
$datatables = Datatables::of($products)
    ->addColumn('product_locations', function ($row) {
        return $row->product_locations->implode('name', ', ');
    })
    // ... other existing column definitions ...
    ;

// Add dynamic price group columns
foreach ($price_groups as $price_group) {
    $column_name = 'group_price' . $price_group->id;
    $raw_columns[] = $column_name;
    
    $datatables->editColumn($column_name, function ($row) use ($price_group) {
        $due_html = '';
        $group_price = (float) 0;
        
        if (!empty($price_group->id)) {
            $variation_group_price = VariationGroupPrice::where('variation_id', $row->variation_id)
                ->where('price_group_id', $price_group->id)
                ->first();
            
            if (!empty($variation_group_price)) {
                $group_price = (float) $variation_group_price->price_inc_tax;
            }
        }
        
        $due_html .= '<span class="group_price" data-orig-value="' . $group_price . '">' 
                   . $this->productUtil->num_f($group_price, true) . '</span>';
        
        return $due_html;
    });
}

// Apply raw columns and return
return $datatables->setRowAttr([
    'data-href' => function ($row) {
        if (auth()->user()->can('product.view')) {
            return action([\App\Http\Controllers\ProductController::class, 'view'], [$row->id]);
        } else {
            return '';
        }
    },
])
->rawColumns($raw_columns)
->make(true);

Complete index method:

Add this import at the top of ProductController.php with other imports:

use App\VariationGroupPrice;

public function index()
{
    if (!auth()->user()->can('product.view') && !auth()->user()->can('product.create')) {
        abort(403, 'Unauthorized action.');
    }
    
    $business_id = request()->session()->get('user.business_id');
    $selling_price_group_count = SellingPriceGroup::countSellingPriceGroups($business_id);
    $is_woocommerce = $this->moduleUtil->isModuleInstalled('Woocommerce');

    // Get price groups for the business
    $price_groups = SellingPriceGroup::where('business_id', $business_id)->get();

    if (request()->ajax()) {
        // Filter by location
        $location_id = request()->get('location_id', null);
        $permitted_locations = auth()->user()->permitted_locations();

        $query = Product::with(['media'])
            ->leftJoin('brands', 'products.brand_id', '=', 'brands.id')
            ->join('units', 'products.unit_id', '=', 'units.id')
            ->leftJoin('categories as c1', 'products.category_id', '=', 'c1.id')
            ->leftJoin('categories as c2', 'products.sub_category_id', '=', 'c2.id')
            ->leftJoin('tax_rates', 'products.tax', '=', 'tax_rates.id')
            ->join('variations as v', 'v.product_id', '=', 'products.id')
            ->leftJoin('variation_location_details as vld', function ($join) use ($permitted_locations) {
                $join->on('vld.variation_id', '=', 'v.id');
                if ($permitted_locations != 'all') {
                    $join->whereIn('vld.location_id', $permitted_locations);
                }
            })
            ->whereNull('v.deleted_at')
            ->where('products.business_id', $business_id)
            ->where('products.type', '!=', 'modifier');

        if (!empty($location_id) && $location_id != 'none') {
            if ($permitted_locations == 'all' || in_array($location_id, $permitted_locations)) {
                $query->whereHas('product_locations', function ($query) use ($location_id) {
                    $query->where('product_locations.location_id', '=', $location_id);
                });
            }
        } elseif ($location_id == 'none') {
            $query->doesntHave('product_locations');
        } else {
            if ($permitted_locations != 'all') {
                $query->whereHas('product_locations', function ($query) use ($permitted_locations) {
                    $query->whereIn('product_locations.location_id', $permitted_locations);
                });
            } else {
                $query->with('product_locations');
            }
        }

        $products = $query->select(
            'products.id',
            'v.id as variation_id', // Critical for price groups
            'products.name as product',
            'products.type',
            'c1.name as category',
            'c2.name as sub_category',
            'units.actual_name as unit',
            'brands.name as brand',
            'tax_rates.name as tax',
            'products.sku',
            'products.image',
            'products.enable_stock',
            'products.is_inactive',
            'products.not_for_selling',
            'products.product_custom_field1', 'products.product_custom_field2', 'products.product_custom_field3', 'products.product_custom_field4', 'products.product_custom_field5', 'products.product_custom_field6',
            'products.product_custom_field7', 'products.product_custom_field8', 'products.product_custom_field9',
            'products.product_custom_field10', 'products.product_custom_field11', 'products.product_custom_field12',
            'products.product_custom_field13', 'products.product_custom_field14', 'products.product_custom_field15',
            'products.product_custom_field16', 'products.product_custom_field17', 'products.product_custom_field18', 
            'products.product_custom_field19', 'products.product_custom_field20',
            'products.alert_quantity',
            DB::raw('SUM(vld.qty_available) as current_stock'),
            DB::raw('MAX(v.sell_price_inc_tax) as max_price'),
            DB::raw('MIN(v.sell_price_inc_tax) as min_price'),
            DB::raw('MAX(v.dpp_inc_tax) as max_purchase_price'),
            DB::raw('MIN(v.dpp_inc_tax) as min_purchase_price')
        );

        // If WooCommerce is enabled, add field to query
        if ($is_woocommerce) {
            $products->addSelect('woocommerce_disable_sync');
        }

        $products->groupBy('products.id');

        // Apply filters
        $type = request()->get('type', null);
        if (!empty($type)) {
            $products->where('products.type', $type);
        }

        $category_id = request()->get('category_id', null);
        if (!empty($category_id)) {
            $products->where('products.category_id', $category_id);
        }

        $brand_id = request()->get('brand_id', null);
        if (!empty($brand_id)) {
            $products->where('products.brand_id', $brand_id);
        }

        $unit_id = request()->get('unit_id', null);
        if (!empty($unit_id)) {
            $products->where('products.unit_id', $unit_id);
        }

        $tax_id = request()->get('tax_id', null);
        if (!empty($tax_id)) {
            $products->where('products.tax', $tax_id);
        }

        $active_state = request()->get('active_state', null);
        if ($active_state == 'active') {
            $products->Active();
        }
        if ($active_state == 'inactive') {
            $products->Inactive();
        }
        
        $not_for_selling = request()->get('not_for_selling', null);
        if ($not_for_selling == 'true') {
            $products->ProductNotForSales();
        }

        $woocommerce_enabled = request()->get('woocommerce_enabled', 0);
        if ($woocommerce_enabled == 1) {
            $products->where('products.woocommerce_disable_sync', 0);
        }

        if (!empty(request()->get('repair_model_id'))) {
            $products->where('products.repair_model_id', request()->get('repair_model_id'));
        }

        // Create DataTables instance
        $datatables = Datatables::of($products)
            ->addColumn('product_locations', function ($row) {
                return $row->product_locations->implode('name', ', ');
            })
            ->editColumn('category', '{{$category}} @if(!empty($sub_category))<br/> -- {{$sub_category}}@endif')
            ->addColumn('action', function ($row) use ($selling_price_group_count) {
                $html = '<div class="btn-group"><button type="button" class="tw-dw-btn tw-dw-btn-xs tw-dw-btn-outline tw-dw-btn-info tw-w-max dropdown-toggle" data-toggle="dropdown" aria-expanded="false">'.__('messages.actions').'<span class="caret"></span><span class="sr-only">Toggle Dropdown</span></button><ul class="dropdown-menu dropdown-menu-left" role="menu"><li><a href="'.action([\App\Http\Controllers\LabelsController::class, 'show']).'?product_id='.$row->id.'" data-toggle="tooltip" title="'.__('lang_v1.label_help').'"><i class="fa fa-barcode"></i> '.__('barcode.labels').'</a></li>';

                if (auth()->user()->can('product.view')) {
                    $html .= '<li><a href="'.action([\App\Http\Controllers\ProductController::class, 'view'], [$row->id]).'" class="view-product"><i class="fa fa-eye"></i> '.__('messages.view').'</a></li>';
                }

                if (auth()->user()->can('product.update')) {
                    $html .= '<li><a href="'.action([\App\Http\Controllers\ProductController::class, 'edit'], [$row->id]).'"><i class="glyphicon glyphicon-edit"></i> '.__('messages.edit').'</a></li>';
                }

                if (auth()->user()->can('product.delete')) {
                    $html .= '<li><a href="'.action([\App\Http\Controllers\ProductController::class, 'destroy'], [$row->id]).'" class="delete-product"><i class="fa fa-trash"></i> '.__('messages.delete').'</a></li>';
                }

                if ($row->is_inactive == 1) {
                    $html .= '<li><a href="'.action([\App\Http\Controllers\ProductController::class, 'activate'], [$row->id]).'" class="activate-product"><i class="fas fa-check-circle"></i> '.__('lang_v1.reactivate').'</a></li>';
                }

                $html .= '<li class="divider"></li>';

                if ($row->enable_stock == 1 && auth()->user()->can('product.opening_stock')) {
                    $html .= '<li><a href="#" data-href="'.action([\App\Http\Controllers\OpeningStockController::class, 'add'], ['product_id' => $row->id]).'" class="add-opening-stock"><i class="fa fa-database"></i> '.__('lang_v1.add_edit_opening_stock').'</a></li>';
                }

                if (auth()->user()->can('product.view')) {
                    $html .= '<li><a href="'.action([\App\Http\Controllers\ProductController::class, 'productStockHistory'], [$row->id]).'"><i class="fas fa-history"></i> '.__('lang_v1.product_stock_history').'</a></li>';
                }

                if (auth()->user()->can('product.create')) {
                    if ($selling_price_group_count > 0) {
                        $html .= '<li><a href="'.action([\App\Http\Controllers\ProductController::class, 'addSellingPrices'], [$row->id]).'"><i class="fas fa-money-bill-alt"></i> '.__('lang_v1.add_selling_price_group_prices').'</a></li>';
                    }

                    $html .= '<li><a href="'.action([\App\Http\Controllers\ProductController::class, 'create'], ['d' => $row->id]).'"><i class="fa fa-copy"></i> '.__('lang_v1.duplicate_product').'</a></li>';
                }

                if (!empty($row->media->first())) {
                    $html .= '<li><a href="'.$row->media->first()->display_url.'" download="'.$row->media->first()->display_name.'"><i class="fas fa-download"></i> '.__('lang_v1.product_brochure').'</a></li>';
                }

                $html .= '</ul></div>';

                return $html;
            })
            ->editColumn('product', function ($row) use ($is_woocommerce) {
                $product = $row->is_inactive == 1 ? e($row->product).' <span class="label bg-gray">'.__('lang_v1.inactive').'</span>' : e($row->product);

                $product = $row->not_for_selling == 1 ? $product.' <span class="label bg-gray">'.__('lang_v1.not_for_selling').'</span>' : $product;

                if ($is_woocommerce && !$row->woocommerce_disable_sync) {
                    $product = $product.'<br><i class="fab fa-wordpress"></i>';
                }

                return $product;
            })
            ->editColumn('image', function ($row) {
                return '<div style="display: flex;"><img src="'.$row->image_url.'" alt="Product image" class="product-thumbnail-small"></div>';
            })
            ->editColumn('type', '@lang("lang_v1." . $type)')
            ->addColumn('mass_delete', function ($row) {
                return '<input type="checkbox" class="row-select" value="'.$row->id.'">';
            })
            ->editColumn('current_stock', function ($row) {
                if ($row->enable_stock) {
                    $stock = $this->productUtil->num_f($row->current_stock, false, null, true);
                    return $stock.' '.$row->unit;
                } else {
                    return '--';
                }
            })
            ->addColumn('purchase_price', '<div style="white-space: nowrap;">@format_currency($min_purchase_price) @if($max_purchase_price != $min_purchase_price && $type == "variable") - @format_currency($max_purchase_price)@endif </div>')
            ->addColumn('selling_price', '<div style="white-space: nowrap;">@format_currency($min_price) @if($max_price != $min_price && $type == "variable") - @format_currency($max_price)@endif </div>')
            ->filterColumn('products.sku', function ($query, $keyword) {
                $query->whereHas('variations', function ($q) use ($keyword) {
                    $q->where('sub_sku', 'like', "%{$keyword}%");
                })
                ->orWhere('products.sku', 'like', "%{$keyword}%");
            });

        // Define base raw columns
        $raw_columns = [
            'action', 'image', 'mass_delete', 'product', 
            'selling_price', 'purchase_price', 'category', 'current_stock', 'product_locations'
        ];

        // Add dynamic price group columns
        foreach ($price_groups as $price_group) {
            $column_name = 'group_price' . $price_group->id;
            $raw_columns[] = $column_name;
            
            $datatables->editColumn($column_name, function ($row) use ($price_group) {
                try {
                    $group_price = 0;
                    
                    if (isset($row->variation_id) && !empty($price_group->id)) {
                        $variation_group_price = VariationGroupPrice::where('variation_id', $row->variation_id)
                            ->where('price_group_id', $price_group->id)
                            ->first();
                        
                        if ($variation_group_price) {
                            $group_price = (float) $variation_group_price->price_inc_tax;
                        }
                    }
                    
                    return '<span class="group_price" data-orig-value="' . $group_price . '">' 
                           . $this->productUtil->num_f($group_price, true) . '</span>';
                           
                } catch (\Exception $e) {
                    \Log::error('Price group error: ' . $e->getMessage());
                    return '<span class="group_price" data-orig-value="0">0.00</span>';
                }
            });
        }

        // Apply final configuration and return
        return $datatables
            ->setRowAttr([
                'data-href' => function ($row) {
                    if (auth()->user()->can('product.view')) {
                        return action([\App\Http\Controllers\ProductController::class, 'view'], [$row->id]);
                    } else {
                        return '';
                    }
                },
            ])
            ->rawColumns($raw_columns)
            ->make(true);
    }

    // Non-AJAX request - return view with data
    $rack_enabled = (request()->session()->get('business.enable_racks') || request()->session()->get('business.enable_row') || request()->session()->get('business.enable_position'));

    $categories = Category::forDropdown($business_id, 'product');
    $brands = Brands::forDropdown($business_id);
    $units = Unit::forDropdown($business_id);

    $tax_dropdown = TaxRate::forBusinessDropdown($business_id, false);
    $taxes = $tax_dropdown['tax_rates'];

    $business_locations = BusinessLocation::forDropdown($business_id);
    $business_locations->prepend(__('lang_v1.none'), 'none');

    $show_manufacturing_data = $this->moduleUtil->isModuleInstalled('Manufacturing') && (auth()->user()->can('superadmin') || $this->moduleUtil->hasThePermissionInSubscription($business_id, 'manufacturing_module'));

    // List product screen filter from module
    $pos_module_data = $this->moduleUtil->getModuleData('get_filters_for_list_product_screen');

    $is_admin = $this->productUtil->is_admin(auth()->user());

    return view('product.index')
        ->with(compact(
            'price_groups',
            'rack_enabled',
            'categories',
            'brands',
            'units',
            'taxes',
            'business_locations',
            'show_manufacturing_data',
            'pos_module_data',
            'is_woocommerce',
            'is_admin'
        ));
}

Key Changes Explained:

Variable Declaration: $raw_columns array is created to manage column names dynamically
DataTables Instance: Store the DataTables object in $datatables variable for manipulation
Dynamic Addition: Price group columns are added to both the DataTables instance and the raw columns array
Final Assembly: Apply rawColumns() and make() at the end

Critical Addition - Missing variation_id:

The original query is missing the essential variation_id field needed for price group lookup. Add this to your select statement:

$products = $query->select(
    'products.id',
    'v.id as variation_id', // ADD THIS LINE - Essential for price groups!
    'products.name as product',
    'products.type',
    // ... rest of existing fields ...
);

Complete Integration Steps:

Add variation_id to select
Get price groups before AJAX handling
Replace direct rawColumns with variable approach
Add price group processing loop
Update rawColumns call to use the variable

3. Pass Price Groups to View

return view('product.index')
    ->with(compact(
        'price_groups', // Add this line
        'rack_enabled',
        'categories',
        'brands',
        'units',
        'taxes',
        'business_locations',
        'show_manufacturing_data',
        'pos_module_data',
        'is_woocommerce',
        'is_admin'
    ));

Key Implementation Notes

Price Group Loop Position: The price group columns are positioned after the product type column and before category/brand columns
Colspan Management: Each price group dynamically increments the $colspan variable for proper table footer spanning
DataTable Integration: Price groups are added as non-searchable, non-orderable columns in the DataTable configuration
Variation ID Requirement: The variation_id field is essential for linking price groups to specific product variations
Dynamic Column Generation: Price group columns are generated based on actual database records, not hardcoded

Testing

Verify Price Groups Display: Check that price group columns appear in the product list
Test Data Population: Ensure price group values display correctly for products with assigned group prices
Verify Filtering: Test location and other filters work with the enhanced query
Performance Check: Monitor query performance with the additional joins and data

Step 2: Integration with Purchase System

2.1 Add Price Group Columns to Purchase Create Form

File: resources/views/purchase/create.blade.php

Add price group column headers to the purchase product table. Locate the existing selling price header and add the price group headers after it:

<th>
    @lang('purchase.unit_selling_price')
    <small>(@lang('product.inc_of_tax'))</small>
</th>

@foreach ($price_groups_all as $price_group_key => $price_group)
    <th>{{ $price_group->name }}</th>
@endforeach

Location: Add this code after the unit selling price header in the purchase table.

2.2 Add Price Group Input Fields to Purchase Entry Row

File: resources/views/purchase/partials/purchase_entry_row.blade.php

Add price group input fields for each product row. Locate the lot number section and add the price group fields before it:

@if(!empty($price_groups) && is_iterable($price_groups))
    @foreach($price_groups as $price_group)
        <td>
            @php
                $group_price_group = $price_group->id;
            @endphp
            {!! Form::text('purchases['.$row_count.'][group_prices]['.$group_price_group.'][group_price]',!empty($variation_prices[$variation->id][$group_price_group]['price']) ? @num_format($variation_prices[$variation->id][$group_price_group]['price']) : 0, ['class' => 'form-control input_number input-sm']); !!}
            {!! Form::hidden('purchases['.$row_count.'][group_prices]['.$group_price_group.'][group_price_id]',$price_group->id); !!}
            @php
                $price_type = !empty($variation_prices[$variation->id][$group_price_group]['price_type']) ? $variation_prices[$variation->id][$group_price_group]['price_type'] : 'fixed';
            @endphp
            <input type="hidden" name="purchases[{{$row_count}}][group_prices][{{$group_price_group}}][group_price_type]" value="{{ $price_type }}"> 
        </td>
    @endforeach
@endif

Location: Add this code before the lot number section:

@if(session('business.enable_lot_number'))
    @php
        $lot_number = !empty($imported_data['lot_number']) ? $imported_data['lot_number'] : null;
    @endphp
    <td>
        {!! Form::text('purchases[' . $row_count . '][lot_number]', $lot_number, ['class' => 'form-control input-sm']); !!}
    </td>
@endif

2.3 Update Purchase Controller Methods

File: app/Http/Controllers/PurchaseController.php

2.3.1 Import Required Model

At the top of the file with other imports, ensure you have:

use App\SellingPriceGroup;

2.3.2 Update `create()` Method

In the create() method, add price groups data before returning the view. Locate the existing code:

$common_settings = !empty(session('business.common_settings')) ? session('business.common_settings') : [];
$price_groups = SellingPriceGroup::forDropdown($business_id);
$price_groups_all = SellingPriceGroup::where('business_id', $business_id)->get();

return view('purchase.create')->with(
    compact(
        'price_groups',
        'price_groups_all',
        'taxes',
        'orderStatuses',
        'business_locations',
        'currency_details',
        'default_purchase_status',
        'customer_groups',
        'types',
        'shortcuts',
        'payment_line',
        'payment_types',
        'accounts',
        'bl_attributes',
        'common_settings'
    )
);

2.3.3 Update `edit()` Method

In the edit() method, ensure price groups are loaded and variation prices are prepared. Add this code before return view('purchase.edit'):

$variation_prices = [];

$price_groups = SellingPriceGroup::where('business_id', $business_id)->active()->get();
foreach ($purchase->purchase_lines as $key => $value) {
    if (!empty($value->sub_unit_id)) {
        $formated_purchase_line = $this->productUtil->changePurchaseLineUnit($value, $business_id);
        $purchase->purchase_lines[$key] = $formated_purchase_line;
    }

    foreach ($value->variations->group_prices as $group_price) {
        $variation_prices[$group_price->variation_id][$group_price->price_group_id] = [
            'price' => $group_price->price_inc_tax,
            'price_type' => $group_price->price_type
        ];
    }
}

Then include the price groups in the view compact:

return view('purchase.edit')
    ->with(compact(
        'price_groups',
        'variation_prices',
        'taxes',
        'purchase',
        'orderStatuses',
        'business_locations',
        'business',
        'currency_details',
        'default_purchase_status',
        'customer_groups',
        'types',
        'shortcuts',
        'purchase_orders',
        'common_settings'
    ));

2.3.4 Update `getPurchaseEntryRow()` Method

In the getPurchaseEntryRow() method, add price group data loading:

public function getPurchaseEntryRow(Request $request)
{
    if (request()->ajax()) {
        $product_id = $request->input('product_id');
        $variation_id = $request->input('variation_id');
        $business_id = request()->session()->get('user.business_id');
        $location_id = $request->input('location_id');
        $is_purchase_order = $request->has('is_purchase_order');
        $supplier_id = $request->input('supplier_id');

        $hide_tax = 'hide';
        if ($request->session()->get('business.enable_inline_tax') == 1) {
            $hide_tax = '';
        }

        $currency_details = $this->transactionUtil->purchaseCurrencyDetails($business_id);

        if (!empty($product_id)) {
            $row_count = $request->input('row_count');
            $product = Product::where('id', $product_id)
                ->with(['unit', 'second_unit', 'variations.group_prices']) // ADD variations.group_prices
                ->first();

            $sub_units = $this->productUtil->getSubUnits($business_id, $product->unit->id, false, $product_id);

            $query = Variation::where('product_id', $product_id)
                ->with([
                    'product_variation',
                    'group_prices', // ADD this line
                    'variation_location_details' => function ($q) use ($location_id) {
                        $q->where('location_id', $location_id);
                    },
                ]);
            if ($variation_id !== '0') {
                $query->where('id', $variation_id);
            }

            $variations = $query->get();
            $taxes = TaxRate::where('business_id', $business_id)
                ->ExcludeForTaxGroup()
                ->get();

            $last_purchase_line = $this->getLastPurchaseLine($variation_id, $location_id, $supplier_id);

            // ADD these lines for price groups
            $price_groups = SellingPriceGroup::where('business_id', $business_id)->get();

            $variation_prices = [];
            foreach ($product->variations as $variation) {
                foreach ($variation->group_prices as $group_price) {
                    $variation_prices[$variation->id][$group_price->price_group_id] = [
                        'price' => $group_price->price_inc_tax,
                        'price_type' => $group_price->price_type
                    ];
                }
            }

            return view('purchase.partials.purchase_entry_row')
                ->with(compact(
                    'product',
                    'price_groups', // ADD this
                    'variation_prices', // ADD this
                    'variations',
                    'row_count',
                    'variation_id',
                    'taxes',
                    'currency_details',
                    'hide_tax',
                    'sub_units',
                    'is_purchase_order',
                    'last_purchase_line'
                ));
        }
    }
}

2.3.5 Update `importPurchaseProducts()` Method

In the importPurchaseProducts() method, add price group support for imported products before $html = view('purchase.partials.imported_purchase_product_rows'):

$price_groups = SellingPriceGroup::where('business_id', $business_id)->get();

$variation_prices = [];
foreach ($formatted_data as $data) {
    if (!empty($data['product'])) {
        foreach ($data['product']->variations as $variation) {
            foreach ($variation->group_prices as $group_price) {
                $variation_prices[$variation->id][$group_price->price_group_id] = [
                    'price' => $group_price->price_inc_tax,
                    'price_type' => $group_price->price_type
                ];
            }
        }
    }
}

$html = view('purchase.partials.imported_purchase_product_rows')
    ->with(compact('formatted_data', 'taxes', 'currency_details', 'hide_tax', 'row_count', 'price_groups', 'variation_prices'))->render();

2.4 Add Price Group Columns to Purchase Edit Form

File: resources/views/purchase/partials/edit_purchase_entry_row.blade.php

Add price group column headers to the purchase edit table. Locate the existing selling price header and add the price group headers after it:

@if(empty($is_purchase_order))
    <th>@lang('purchase.unit_selling_price') <small>(@lang('product.inc_of_tax'))</small></th>
    
    @if(!empty($price_groups) && is_iterable($price_groups))
        @foreach ($price_groups as $price_group_key => $price_group)
            <th>{{ $price_group->name }}</th>
        @endforeach
    @endif
    
    @if(session('business.enable_lot_number'))
        <th>@lang('lang_v1.lot_number')</th>
    @endif
    @if(session('business.enable_product_expiry'))
        <th>@lang('product.mfg_date') / @lang('product.exp_date')</th>
    @endif
@endif

Add price group input fields for each existing purchase line. Locate the selling price cell and add the price group fields after it:

@if(empty($is_purchase_order))
<td>
    @if(session('business.enable_editing_product_from_purchase'))
        {!! Form::text('purchases[' . $loop->index . '][default_sell_price]', number_format($sp, $currency_precision, $currency_details->decimal_separator, $currency_details->thousand_separator), ['class' => 'form-control input-sm input_number default_sell_price', 'required']); !!}
    @else
        {{number_format($sp, $currency_precision, $currency_details->decimal_separator, $currency_details->thousand_separator)}}
    @endif
</td>

@if(!empty($price_groups) && is_iterable($price_groups))
    @foreach($price_groups as $price_group)
        <td>
            @php
                $group_price_group = $price_group->id;
                $current_group_price = 0;
                if(isset($variation_prices[$purchase_line->variation_id][$group_price_group]['price'])) {
                    $current_group_price = $variation_prices[$purchase_line->variation_id][$group_price_group]['price'];
                }
                $price_type = isset($variation_prices[$purchase_line->variation_id][$group_price_group]['price_type']) ? 
                    $variation_prices[$purchase_line->variation_id][$group_price_group]['price_type'] : 'fixed';
            @endphp
            {!! Form::text('purchases['.$loop->index.'][group_prices]['.$group_price_group.'][group_price]', 
                number_format($current_group_price, $currency_precision, $currency_details->decimal_separator, $currency_details->thousand_separator), 
                ['class' => 'form-control input-sm input_number']); !!}
            {!! Form::hidden('purchases['.$loop->index.'][group_prices]['.$group_price_group.'][group_price_id]', $price_group->id); !!}
            <input type="hidden" name="purchases[{{$loop->index}}][group_prices][{{$group_price_group}}][group_price_type]" value="{{ $price_type }}">
        </td>
    @endforeach
@endif

You can add a custom CSS rule to set minimum width for all form inputs on the edit page. Place this <style> block at the very top of your edit_purchase_entry_row.blade.php file, right after the PHP variables section:

@php
    $hide_tax = '';
    if(session()->get('business.enable_inline_tax') == 0){
        $hide_tax = 'hide';
    }
    $currency_precision = session('business.currency_precision', 2);
    $quantity_precision = session('business.quantity_precision', 2);
@endphp

{{-- Add a custom CSS rule to set minimum width for all form inputs --}}
<style>
#purchase_entry_table .form-control {
    min-width: 5rem;
}
</style>

<div class="table-responsive">
    <!-- rest of your table -->

Or you can add a section CSS in resources/views/purchase/create.blade.php and resources/views/purchase/edit.blade.php pages:

@section('css')
<style>
    #purchase_entry_table .form-control {
        min-width: 5rem;
    }
</style>
@endsection

Set minimum width for all form inputs on the edit page

2.5 Update ProductUtil for Price Group Processing

File: app/Utils/ProductUtil.php

Add the price group processing method to ProductUtil:

public function createOrUpdateGroupPrice($group_prices, $variation_id)
{
    if (!empty($variation_id)) {
        foreach ($group_prices as $key => $value) {
            // \Log::info("Processing group price key: $key", $value);

            // Check if the record already exists
            $variation_group_price = VariationGroupPrice::where('variation_id', $variation_id)
                ->where('price_group_id', $value['group_price_id'])
                ->first();

            if (empty($variation_group_price)) {
                // \Log::info('Creating NEW variation group price record');
                // Create new record
                $variation_group_price = new VariationGroupPrice();
                $variation_group_price->variation_id = $variation_id;
                $variation_group_price->price_group_id = $value['group_price_id'];
            } else {
                // \Log::info('UPDATING existing variation group price record', [
                //     'id' => $variation_group_price->id,
                //     'current_price' => $variation_group_price->price_inc_tax
                // ]);
            }

            // Convert price using num_uf
            $new_price = $this->num_uf($value['group_price']);

            // Update the values
            $variation_group_price->price_inc_tax = $new_price;
            $variation_group_price->price_type = $value['group_price_type'];

            // Save each record individually
            $result = $variation_group_price->save();
        }
    } else {
        \Log::warning('Variation ID is empty - skipping price group update');
    }
}

Update the createOrUpdatePurchaseLines method in ProductUtil to process price groups. Your createOrUpdatePurchaseLines method only processes the first element which only contains group_price 1. Elements 2-7 don't have product_id or variation_id, so they're skipped by the validation.

Fix: Modify your createOrUpdatePurchaseLines method to handle this structure:

/**
 * Add/Edit transaction purchase lines
 *
 * @param  object  $transaction
 * @param  array  $input_data
 * @param  array  $currency_details
 * @param  bool  $enable_product_editing
 * @param  string  $before_status = null
 * @return array
 */
public function createOrUpdatePurchaseLines($transaction, $input_data, $currency_details, $enable_product_editing, $before_status = null)
{
    $updated_purchase_lines = [];
    $updated_purchase_line_ids = [0];
    $exchange_rate = !empty($transaction->exchange_rate) ? $transaction->exchange_rate : 1;

    foreach ($input_data as $data) {

        // Skip if this is just a group_prices fragment without product data
        if (isset($data['group_prices']) && !isset($data['product_id'])) {
            // This is a price group fragment, merge it with the main purchase line
            continue;
        }

        // Validate required fields before processing
        if (!is_array($data) || empty($data['quantity']) || empty($data['product_id']) || empty($data['variation_id'])) {
            continue;
        }

        $multiplier = 1;

        if (isset($data['sub_unit_id']) && $data['sub_unit_id'] == $data['product_unit_id']) {
            unset($data['sub_unit_id']);
        }

        if (!empty($data['sub_unit_id'])) {
            $unit = Unit::find($data['sub_unit_id']);
            $multiplier = !empty($unit->base_unit_multiplier) ? $unit->base_unit_multiplier : 1;
        }
        $new_quantity = $this->num_uf($data['quantity']) * $multiplier;

        $new_quantity_f = $this->num_f($new_quantity);
        $old_qty = 0;
        //update existing purchase line
        if (isset($data['purchase_line_id'])) {
            $purchase_line = PurchaseLine::findOrFail($data['purchase_line_id']);
            $updated_purchase_line_ids[] = $purchase_line->id;
            $old_qty = $purchase_line->quantity;

            $this->updateProductStock($before_status, $transaction, $data['product_id'], $data['variation_id'], $new_quantity, $purchase_line->quantity, $currency_details);
        } else {
            //create newly added purchase lines
            $purchase_line = new PurchaseLine();
            $purchase_line->product_id = $data['product_id'];
            $purchase_line->variation_id = $data['variation_id'];

            //Increase quantity only if status is received
            if ($transaction->status == 'received') {
                $this->updateProductQuantity($transaction->location_id, $data['product_id'], $data['variation_id'], $new_quantity_f, 0, $currency_details);
            }
        }
        // Collect all group prices from all array elements for this variation
        $all_group_prices = [];

        // Get group prices from current element
        if (isset($data['group_prices'])) {
            $all_group_prices = array_merge($all_group_prices, $data['group_prices']);
        }

        // Get group prices from other elements in the array
        foreach ($input_data as $other_data) {
            if (isset($other_data['group_prices']) && !isset($other_data['product_id'])) {
                $all_group_prices = array_merge($all_group_prices, $other_data['group_prices']);
            }
        }

        // Process all collected group prices
        if (!empty($all_group_prices)) {
            $this->createOrUpdateGroupPrice($all_group_prices, $data['variation_id']);
        }

        $purchase_line->quantity = $new_quantity;
        $purchase_line->pp_without_discount = ($this->num_uf($data['pp_without_discount'], $currency_details) * $exchange_rate) / $multiplier;
        $purchase_line->discount_percent = $this->num_uf($data['discount_percent'], $currency_details);
        $purchase_line->purchase_price = ($this->num_uf($data['purchase_price'], $currency_details) * $exchange_rate) / $multiplier;
        $purchase_line->purchase_price_inc_tax = ($this->num_uf($data['purchase_price_inc_tax'], $currency_details) * $exchange_rate) / $multiplier;
        $purchase_line->item_tax = ($this->num_uf($data['item_tax'], $currency_details) * $exchange_rate) / $multiplier;
        $purchase_line->tax_id = $data['purchase_line_tax_id'];
        $purchase_line->lot_number = !empty($data['lot_number']) ? $data['lot_number'] : null;
        $purchase_line->mfg_date = !empty($data['mfg_date']) ? $this->uf_date($data['mfg_date']) : null;
        $purchase_line->exp_date = !empty($data['exp_date']) ? $this->uf_date($data['exp_date']) : null;
        $purchase_line->sub_unit_id = !empty($data['sub_unit_id']) ? $data['sub_unit_id'] : null;
        $purchase_line->purchase_order_line_id = !empty($data['purchase_order_line_id']) ? $data['purchase_order_line_id'] : null;
        $purchase_line->purchase_requisition_line_id = !empty($data['purchase_requisition_line_id']) && $transaction->type == 'purchase_order' ? $data['purchase_requisition_line_id'] : null;

        if (!empty($data['secondary_unit_quantity'])) {
            $purchase_line->secondary_unit_quantity = $this->num_uf($data['secondary_unit_quantity']);
        }

        $updated_purchase_lines[] = $purchase_line;

        //Edit product price
        if ($enable_product_editing == 1 && $transaction->type == 'purchase') {
            if (isset($data['default_sell_price'])) {
                $variation_data['sell_price_inc_tax'] = ($this->num_uf($data['default_sell_price'], $currency_details)) / $multiplier;
            }
            $variation_data['pp_without_discount'] = ($this->num_uf($data['pp_without_discount'], $currency_details) * $exchange_rate) / $multiplier;
            $variation_data['variation_id'] = $purchase_line->variation_id;
            $variation_data['purchase_price'] = $purchase_line->purchase_price;

            $this->updateProductFromPurchase($variation_data);
        }

        if ($transaction->type == 'purchase_order') {
            //Update purchase requisition line quantity received
            $this->updatePurchaseOrderLine($purchase_line->purchase_requisition_line_id, $purchase_line->quantity, $old_qty);
        }

        //Update purchase order line quantity received
        $this->updatePurchaseOrderLine($purchase_line->purchase_order_line_id, $purchase_line->quantity, $old_qty);
    }

    //unset deleted purchase lines
    $delete_purchase_line_ids = [];
    $delete_purchase_lines = null;
    if (!empty($updated_purchase_line_ids)) {
        $delete_purchase_lines = PurchaseLine::where('transaction_id', $transaction->id)
            ->whereNotIn('id', $updated_purchase_line_ids)
            ->get();

        if ($delete_purchase_lines->count()) {
            foreach ($delete_purchase_lines as $delete_purchase_line) {
                $delete_purchase_line_ids[] = $delete_purchase_line->id;

                //decrease deleted only if previous status was received
                if ($before_status == 'received') {
                    $this->decreaseProductQuantity(
                        $delete_purchase_line->product_id,
                        $delete_purchase_line->variation_id,
                        $transaction->location_id,
                        $delete_purchase_line->quantity
                    );
                }

                //If purchase order line set decrease quantity
                if (!empty($delete_purchase_line->purchase_order_line_id)) {
                    $this->updatePurchaseOrderLine($delete_purchase_line->purchase_order_line_id, 0, $delete_purchase_line->quantity);
                }

                //If purchase order line set decrease quantity
                if (!empty($delete_purchase_line->purchase_requisition_line_id)) {
                    $this->updatePurchaseOrderLine($delete_purchase_line->purchase_requisition_line_id, 0, $delete_purchase_line->quantity);
                }
            }

            //unset if purchase order line from purchase lines if exists
            if ($transaction->type == 'purchase_order') {
                PurchaseLine::whereIn('purchase_order_line_id', $delete_purchase_line_ids)
                    ->update(['purchase_order_line_id' => null]);
            }

            //Delete deleted purchase lines
            PurchaseLine::where('transaction_id', $transaction->id)
                ->whereIn('id', $delete_purchase_line_ids)
                ->delete();
        }
    }

    //update purchase lines
    if (!empty($updated_purchase_lines)) {
        $transaction->purchase_lines()->saveMany($updated_purchase_lines);
    }

    return $delete_purchase_lines;
}

Location: Insert this code after the line where $purchase_line is created or found, and before the quantity assignment.

2.6 Update Import Template and Processing

File: import_purchase_products_template.xls

The import template needs to be updated to include price group columns. The current template structure should be extended with additional columns for each price group.

Add this import at the top of your PurchaseController.php file if it doesn't exist:

use Maatwebsite\Excel\Facades\Excel;

Original Template Structure:

Column A: SKU (required)
Column B: Quantity (required)  
Column C: Unit Cost Before Discount
Column D: Discount Percent
Column E: Tax Name
Column F: Lot Number
Column G: Manufacturing Date
Column H: Expiry Date

Updated Template Structure:

Column A: SKU (required)
Column B: Quantity (required)  
Column C: Unit Cost Before Discount
Column D: Discount Percent
Column E: Tax Name
Column F: Lot Number
Column G: Manufacturing Date
Column H: Expiry Date
Column I: Price Group 1 (if exists)
Column J: Price Group 2 (if exists)
Column K: Price Group 3 (if exists)
... (continue for each price group)

Template Update Instructions:

Add Dynamic Columns: The number of price group columns depends on how many selling price groups exist in your business
Column Headers: Use the actual price group names as column headers
Sample Data: Include sample price group values in the template
Documentation: Update any accompanying documentation to explain the new columns

Controller Update for Import Processing

You'll also need to update the importPurchaseProducts() method to process the additional price group columns:

public function importPurchaseProducts(Request $request)
{
    try {
        $file = $request->file('file');

        $parsed_array = Excel::toArray([], $file);
        //Remove header row
        $imported_data = array_splice($parsed_array[0], 1);

        $business_id = $request->session()->get('user.business_id');
        $location_id = $request->input('location_id');
        $row_count = $request->input('row_count');
        // Get price groups for processing
        $price_groups = SellingPriceGroup::where('business_id', $business_id)->active()->get();

        $formatted_data = [];
        $row_index = 0;
        $error_msg = '';
        foreach ($imported_data as $key => $value) {
            $row_index = $key + 1;
            $temp_array = [];

            if (!empty($value[0])) {
                $variation = Variation::where('sub_sku', trim($value[0]))
                    ->join('products', 'products.id', '=', 'variations.product_id')
                    ->where('products.business_id', $business_id)
                    ->with([
                        'product_variation',
                        'variation_location_details' => function ($q) use ($location_id) {
                            $q->where('location_id', $location_id);
                        },
                    ])
                    ->select('variations.*')
                    ->first();
                $temp_array['variation'] = $variation;

                if (empty($variation)) {
                    $error_msg = __('lang_v1.product_not_found_exception', ['row' => $row_index, 'sku' => $value[0]]);
                    break;
                }

                $product = Product::where('id', $variation->product_id)
                    ->where('business_id', $business_id)
                    ->with(['unit'])
                    ->first();

                if (empty($product)) {
                    $error_msg = __('lang_v1.product_not_found_exception', ['row' => $row_index, 'sku' => $value[0]]);
                    break;
                }

                $temp_array['product'] = $product;

                $sub_units = $this->productUtil->getSubUnits($business_id, $product->unit->id, false, $product->id);

                $temp_array['sub_units'] = $sub_units;
            } else {
                $error_msg = __('lang_v1.product_not_found_exception', ['row' => $row_index, 'sku' => $value[0]]);
                break;
            }

            if (!empty($value[1])) {
                $temp_array['quantity'] = $value[1];
            } else {
                $error_msg = __('lang_v1.quantity_required', ['row' => $row_index]);
                break;
            }

            $temp_array['unit_cost_before_discount'] = !empty($value[2]) ? $value[2] : $variation->default_purchase_price;
            $temp_array['discount_percent'] = !empty($value[3]) ? $value[3] : 0;

            $tax_id = null;

            if (!empty($value[4])) {
                $tax_name = trim($value[4]);
                $tax = TaxRate::where('business_id', $business_id)
                    ->where('name', 'like', "%{$tax_name}%")
                    ->first();

                $tax_id = $tax->id ?? $tax_id;
            }

            $temp_array['tax_id'] = $tax_id;
            $temp_array['lot_number'] = !empty($value[5]) ? $value[5] : null;
            $temp_array['mfg_date'] = !empty($value[6]) ? $this->productUtil->format_date($value[6]) : null;
            $temp_array['exp_date'] = !empty($value[7]) ? $this->productUtil->format_date($value[7]) : null;

            // Process price group columns (starting from column I = index 8)
            $price_group_data = [];
            foreach ($price_groups as $index => $price_group) {
                $column_index = 8 + $index; // Starting after expiry date column
                if (isset($value[$column_index]) && !empty($value[$column_index])) {
                    $price_group_data[$price_group->id] = [
                        'group_price' => $value[$column_index],
                        'group_price_id' => $price_group->id,
                        'group_price_type' => 'fixed'
                    ];
                }
            }
            $temp_array['group_prices'] = $price_group_data;

            $formatted_data[] = $temp_array;
        }

        if (!empty($error_msg)) {
            return [
                'success' => false,
                'msg' => $error_msg,
            ];
        }

        $hide_tax = 'hide';
        if ($request->session()->get('business.enable_inline_tax') == 1) {
            $hide_tax = '';
        }

        $taxes = TaxRate::where('business_id', $business_id)
            ->ExcludeForTaxGroup()
            ->get();

        $currency_details = $this->transactionUtil->purchaseCurrencyDetails($business_id);

        $price_groups = SellingPriceGroup::where('business_id', $business_id)->get();

        $variation_prices = [];
        foreach ($formatted_data as $data) {
            if (!empty($data['product'])) {
                foreach ($data['product']->variations as $variation) {
                    foreach ($variation->group_prices as $group_price) {
                        $variation_prices[$variation->id][$group_price->price_group_id] = [
                            'price' => $group_price->price_inc_tax,
                            'price_type' => $group_price->price_type
                        ];
                    }
                }
            }
        }

        $html = view('purchase.partials.imported_purchase_product_rows')
            ->with(compact('formatted_data', 'taxes', 'currency_details', 'hide_tax', 'row_count', 'price_groups', 'variation_prices'))->render();

        return [
            'success' => true,
            'msg' => __('lang_v1.imported'),
            'html' => $html,
        ];
    } catch (\Exception $e) {
        return [
            'success' => false,
            'msg' => $e->getMessage(),
        ];
    }
}

Template Generation Script (Optional)

You could create a dynamic template generator that creates the Excel file based on current price groups:

public function downloadImportTemplate()
{
    $business_id = request()->session()->get('user.business_id');
    $price_groups = SellingPriceGroup::where('business_id', $business_id)->active()->get();
    
    // Create Excel file with dynamic headers
    $headers = [
        'SKU', 'Quantity', 'Unit Cost Before Discount', 'Discount Percent',
        'Tax Name', 'Lot Number', 'Manufacturing Date', 'Expiry Date'
    ];
    
    // Add price group headers
    foreach ($price_groups as $price_group) {
        $headers[] = $price_group->name;
    }
    
    // Generate Excel file with these headers
    // ... Excel generation logic ...
}

Important Notes:

Backward Compatibility: Ensure the import still works if price group columns are missing
Error Handling: Add validation for price group values in the import process
Documentation: Update user documentation to explain the new template format
Template Versioning: Consider versioning your templates if you have existing users

Key Implementation Features

Dynamic Price Group Headers: Price group columns are generated dynamically based on active selling price groups
Pre-populated Values: When editing purchases, existing price group values are loaded and displayed
Form Validation: Price group inputs use the same validation as other price fields
Import Support: Price groups are included in the product import functionality
Consistent UI: Price group fields follow the same styling and behavior as other input fields

Testing the Integration

Create Purchase: Verify price group columns appear in the create purchase form
Add Products: Ensure price group fields are populated when adding products to purchase
Edit Purchase: Check that existing price group values are loaded correctly
Import Products: Test that price group data is maintained during product import
Data Persistence: Verify that price group values are saved and can be retrieved

Troubleshooting

Common Issues:

Missing Price Group Columns: Ensure $price_groups_all is passed to the create view
Empty Price Group Fields: Verify $variation_prices array is properly structured
Form Submission Errors: Check that price group data structure matches expected format
Import Issues: Ensure price group logic is included in import processing

Performance Considerations:

Database Queries: Monitor the impact of additional joins and queries
Frontend Rendering: Test with multiple price groups to ensure responsive UI
Memory Usage: Check memory consumption with large datasets

Best Practices

Error Handling: Always include try-catch blocks for price group operations
Data Validation: Validate price group data before processing
Logging: Add appropriate logging for debugging price group issues
User Experience: Provide clear feedback when price groups are updated

Next Steps

With both Step 1 (Product List Display) and Step 2 (Purchase Integration) complete, you now have:

✅ Dynamic price group columns in product listings
✅ Price group management in purchase create/edit forms
✅ Automatic price group updates during purchase operations
✅ Support for product imports with price groups

Your Ultimate POS now has full price group functionality integrated across the product and purchase systems!

The ugly truth about nulled scripts/themes/plugins

What lead to permanent ban

DoniaWeB Forums Terms & Rules