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 total'),
        DB::raw('(SELECT sum(rs1.quantity) FROM report_stocks rs1 WHERE rs1.product_id = report_stocks.product_id AND rs1.is_active = true AND rs1.deleted_at IS NULL AND rs1.quantity = 1) AS booked'),
        DB::raw('(SELECT sum(rs2.quantity) FROM report_stocks rs2 WHERE rs2.product_id = report_stocks.product_id AND rs2.is_active = true AND rs2.deleted_at IS NULL) - 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'
    );
}