apps/backend/src/modules/inventory/read-models/inventory-read-model.service.tsTypeScript
import { Injectable } from '@nestjs/common';
import { DatabaseService } from '../../../infrastructure/database/database.service';
@Injectable()
export class InventoryReadModelService {
constructor(private readonly database: DatabaseService) {}
async overview(input:{organizationId:string;locationId:string;businessDate:string}) {
const summary = await this.database.query<any>(
`select
count(*) filter(
where rr.id is not null and sb.total_balance <= rr.reorder_point
)::int as low_stock_count,
count(*) filter(where sb.total_balance < 0)::int as negative_stock_count
from (
select
si.id,
coalesce(sum(sbp.quantity_normalized),0) as total_balance
from stock_items si
left join stock_balance_projection sbp
on sbp.stock_item_id=si.id and sbp.location_id=$2
where si.organization_id=$1 and si.is_active=true
group by si.id
) sb
left join reorder_rules rr
on rr.stock_item_id=sb.id and rr.location_id=$2 and rr.is_active=true`,
[input.organizationId, input.locationId],
);
const openCounts = await this.database.query<{ count: number }>(
`select count(*)::int as count
from inventory_counts
where organization_id=$1 and location_id=$2
and status in ('draft','in_progress','review')`,
[input.organizationId, input.locationId],
);
const waste = await this.database.query<{ total: string }>(
`select coalesce(sum(normalized_quantity),0)::text as total
from waste_records
where organization_id=$1 and location_id=$2
and business_date=$3::date`,
[input.organizationId,input.locationId,input.businessDate],
);
return {
businessDate:input.businessDate,
lowStockCount: summary[0]?.low_stock_count ?? 0,
negativeStockCount: summary[0]?.negative_stock_count ?? 0,
openCountCount: openCounts[0]?.count ?? 0,
wasteTodayNormalized: waste[0]?.total ?? '0',
};
}
async stock(input: { organizationId: string; locationId: string }) {
return this.database.query<any>(
`select
si.id,
si.ingredient_id as "ingredientId",
si.name,
si.base_unit_code as "unitCode",
u.code as unit,
coalesce(sum(sbp.quantity_normalized),0)::text as balance,
rr.reorder_point::text,
rr.target_stock::text,
case
when coalesce(sum(sbp.quantity_normalized),0) < 0 then 'negative'
when rr.id is not null and coalesce(sum(sbp.quantity_normalized),0) <= rr.reorder_point then 'low'
else 'ok'
end as stock_status,
max(sm.created_at) as last_movement_at
from stock_items si
join units u on u.code=si.base_unit_code
left join stock_balance_projection sbp
on sbp.stock_item_id=si.id and sbp.location_id=$2
left join reorder_rules rr
on rr.stock_item_id=si.id and rr.location_id=$2 and rr.is_active=true
left join stock_movements sm
on sm.stock_item_id=si.id and sm.location_id=$2
where si.organization_id=$1 and si.is_active=true
group by si.id,u.code,rr.id
order by lower(si.name)`,
[input.organizationId, input.locationId],
);
}
async storageLocations(input: {
organizationId: string;
locationId: string;
}) {
return this.database.query<any>(
`select
sl.id,sl.name,sl.storage_type,sl.sort_order
from storage_locations sl
where sl.organization_id=$1 and sl.location_id=$2 and sl.is_active=true
order by sl.sort_order,lower(sl.name),sl.id`,
[input.organizationId, input.locationId],
);
}
async movements(input: {
organizationId: string;
locationId: string;
stockItemId?: string;
}) {
return this.database.query<any>(
`select
sm.id,sm.movement_type,sm.quantity_delta::text,
sm.normalized_quantity_delta::text,
u.code as unit,sm.source_type,sm.source_id,
sm.reason,sm.business_date,sm.created_at,
si.name as stock_item_name,
sl.name as storage_location_name
from stock_movements sm
join stock_items si on si.id=sm.stock_item_id
join storage_locations sl on sl.id=sm.storage_location_id
join units u on u.code=sm.normalized_unit_code
where sm.organization_id=$1
and sm.location_id=$2
and ($3::uuid is null or sm.stock_item_id=$3)
order by sm.created_at desc
limit 500`,
[input.organizationId, input.locationId, input.stockItemId ?? null],
);
}
}