Untitled

elseif ($action == 'getStockPosition') {
            $query->leftJoin('warehouses as warehouse_parent', 'warehouse_parent.id', '=', 'report_stocks.warehouse_parent_id');
            $query->leftJoin('warehouses', 'warehouses.id', '=', 'report_stocks.warehouse_id');

            $query->leftJoin('product_brands', 'product_brands.id', '=', 'report_stocks.product_id');

            $query->leftJoin('products', 'products.brand_id', '=', 'product_brands.id');

            $query->leftJoin('product_details', function ($join) {
                $join->on('products.id', '=', 'product_details.product_id');
                $join->where('product_details.is_default', true);
                $join->where('product_details.deleted_at', null);
            });

            $query->leftJoin('uom', 'uom.id', '=', 'product_details.uom_id');

            $query->leftJoin('users as created_user', 'created_user.id', '=', 'report_stocks.created_by');
            $query->leftJoin('users as updated_user', 'updated_user.id', '=', 'report_stocks.updated_by');
            $query->leftJoin('users as deleted_user', 'deleted_user.id', '=', 'report_stocks.deleted_by');

            $query->select([
                DB::raw('(array_agg(report_stocks.id order by report_stocks.id))[1] as id'),
                'report_stocks.warehouse_parent_id',
                'warehouse_parent.name AS warehouse_parent_name',
                'warehouses.name AS warehouse_name',
                'product_brands.code AS brand_code',
                'product_brands.name AS brand_name',
                'uom.name AS uom_name',
                DB::raw('sum(report_stocks.quantity) AS on_hand'),
                DB::raw('(sum(report_stocks.quantity) AS booked'),
                DB::raw('sum(report_stocks.quantity) - 4 AS available')
            ]);

            $query->where('report_stocks.product_id', '!=', null);
            $query->where('report_stocks.is_active', true);
            $query->where('report_stocks.deleted_at', null);

            
            $query->groupBy(
                'report_stocks.warehouse_parent_id',

                'warehouse_parent.name',
                'warehouses.name',

                'product_brands.code',
                'product_brands.name',

                'uom.name',
            );