root
540 lines
19 KiB
PHP
540 lines
19 KiB
PHP
<?php
|
|
|
|
namespace App\Services\Inventory;
|
|
|
|
use App\Models\InventoryItem;
|
|
use App\Models\InventoryMovement;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Log;
|
|
|
|
class InventoryMovementService
|
|
{
|
|
public function __construct(private InventoryContextService $context) {}
|
|
|
|
public function list(array $filters = []): array
|
|
{
|
|
$selectedYear = trim((string) ($filters['school_year'] ?? $this->context->schoolYear()));
|
|
$selectedSem = trim((string) ($filters['semester'] ?? $this->context->semester()));
|
|
$includeVoided = ! empty($filters['include_voided']);
|
|
|
|
$builder = DB::table('inventory_movements as m')
|
|
->select([
|
|
'm.*',
|
|
'ii.name as item_name',
|
|
DB::raw("CONCAT(pb.firstname, ' ', pb.lastname) AS performed_by_name"),
|
|
DB::raw("CONCAT(t.firstname, ' ', t.lastname) AS teacher_name"),
|
|
DB::raw("CONCAT(s.firstname, ' ', s.lastname) AS student_name"),
|
|
'cs.class_section_name',
|
|
])
|
|
->leftJoin('inventory_items as ii', 'ii.id', '=', 'm.item_id')
|
|
->leftJoin('users as pb', 'pb.id', '=', 'm.performed_by')
|
|
->leftJoin('users as t', 't.id', '=', 'm.teacher_id')
|
|
->leftJoin('students as s', 's.id', '=', 'm.student_id')
|
|
->leftJoin('classSection as cs', 'cs.class_section_id', '=', 'm.class_section_id')
|
|
->orderBy('m.id', 'DESC');
|
|
|
|
// Exclude voided movements by default
|
|
if (! $includeVoided) {
|
|
$builder->whereNull('m.voided_at');
|
|
}
|
|
|
|
if ($selectedYear !== '') {
|
|
$builder->where('m.school_year', $selectedYear);
|
|
}
|
|
if ($selectedSem !== '') {
|
|
$builder->where('m.semester', $selectedSem);
|
|
}
|
|
|
|
return $builder->get()->map(fn ($r) => (array) $r)->all();
|
|
}
|
|
|
|
/**
|
|
* @deprecated Use recordMovement() instead. This method allows editing
|
|
* movement history which weakens auditability.
|
|
*/
|
|
public function create(array $data, int $performedBy = 0): array
|
|
{
|
|
$itemId = (int) ($data['item_id'] ?? 0);
|
|
$movementType = (string) ($data['movement_type'] ?? '');
|
|
$qtyChange = (int) ($data['qty_change'] ?? 0);
|
|
|
|
$item = InventoryItem::query()->find($itemId);
|
|
if (! $item) {
|
|
return ['ok' => false, 'message' => 'Selected item not found.'];
|
|
}
|
|
|
|
$qtyChange = $this->normalizeMovementQty($movementType, $qtyChange);
|
|
if ($this->wouldGoNegative($itemId, $qtyChange)) {
|
|
return ['ok' => false, 'message' => 'Not enough stock to apply this movement.'];
|
|
}
|
|
|
|
$payload = $this->buildMovementPayload($data, $itemId, $qtyChange, $movementType, $performedBy);
|
|
|
|
try {
|
|
InventoryMovement::query()->create($payload);
|
|
$this->recalcQuantity($itemId);
|
|
} catch (\Throwable $e) {
|
|
Log::error('Inventory movement create failed: '.$e->getMessage());
|
|
|
|
return ['ok' => false, 'message' => 'Could not save movement.'];
|
|
}
|
|
|
|
return ['ok' => true];
|
|
}
|
|
|
|
/**
|
|
* @deprecated Do NOT edit movements directly. Use voidMovement() + recordMovement() instead.
|
|
* Editing historical movements destroys audit integrity.
|
|
*/
|
|
public function update(int $id, array $data): array
|
|
{
|
|
$movement = InventoryMovement::query()->find($id);
|
|
if (! $movement) {
|
|
return ['ok' => false, 'message' => 'Movement not found.'];
|
|
}
|
|
|
|
// Prevent editing voided movements
|
|
if ($movement->voided_at) {
|
|
return ['ok' => false, 'message' => 'Cannot edit a voided movement.'];
|
|
}
|
|
|
|
$movementType = (string) ($data['movement_type'] ?? $movement->movement_type);
|
|
$qtyChange = (int) ($data['qty_change'] ?? $movement->qty_change);
|
|
$qtyChange = $this->normalizeMovementQty($movementType, $qtyChange);
|
|
|
|
$itemId = (int) $movement->item_id;
|
|
$delta = $qtyChange - (int) $movement->qty_change;
|
|
if ($delta !== 0 && $this->wouldGoNegative($itemId, $delta)) {
|
|
return ['ok' => false, 'message' => 'Not enough stock to apply this movement.'];
|
|
}
|
|
|
|
$payload = [
|
|
'qty_change' => $qtyChange,
|
|
'movement_type' => $movementType,
|
|
'reason' => trim((string) ($data['reason'] ?? '')),
|
|
'note' => trim((string) ($data['note'] ?? '')),
|
|
'semester' => $data['semester'] ?? $this->context->semester(),
|
|
'school_year' => $data['school_year'] ?? $this->context->schoolYear(),
|
|
'teacher_id' => $this->nullableInt($data['teacher_id'] ?? null),
|
|
'student_id' => $this->nullableInt($data['student_id'] ?? null),
|
|
'class_section_id' => $this->nullableInt($data['class_section_id'] ?? null),
|
|
];
|
|
|
|
try {
|
|
$movement->update($payload);
|
|
$this->recalcQuantity($itemId);
|
|
} catch (\Throwable $e) {
|
|
Log::error('Inventory movement update failed: '.$e->getMessage());
|
|
|
|
return ['ok' => false, 'message' => 'Could not update movement.'];
|
|
}
|
|
|
|
return ['ok' => true];
|
|
}
|
|
|
|
/**
|
|
* @deprecated Do NOT hard-delete movements. Use voidMovement() instead.
|
|
* Deleting historical movements destroys audit integrity.
|
|
*/
|
|
public function delete(int $id): array
|
|
{
|
|
$movement = InventoryMovement::query()->find($id);
|
|
if (! $movement) {
|
|
return ['ok' => false, 'message' => 'Movement not found.'];
|
|
}
|
|
|
|
// Prevent hard-deleting movements that have corrections
|
|
$hasCorrections = InventoryMovement::query()
|
|
->where('corrects_movement_id', $id)
|
|
->exists();
|
|
if ($hasCorrections) {
|
|
return ['ok' => false, 'message' => 'Cannot delete a movement that has corrections. Void it instead.'];
|
|
}
|
|
|
|
$itemId = (int) $movement->item_id;
|
|
try {
|
|
$movement->delete();
|
|
if ($itemId > 0) {
|
|
$this->recalcQuantity($itemId);
|
|
}
|
|
} catch (\Throwable $e) {
|
|
Log::error('Inventory movement delete failed: '.$e->getMessage());
|
|
|
|
return ['ok' => false, 'message' => 'Could not delete movement.'];
|
|
}
|
|
|
|
return ['ok' => true];
|
|
}
|
|
|
|
/**
|
|
* @deprecated Do NOT bulk-delete movements. Use voidMovement() instead.
|
|
* Deleting historical movements destroys audit integrity.
|
|
*/
|
|
public function bulkDelete(array $ids): array
|
|
{
|
|
$ids = array_values(array_filter(array_map('intval', $ids), static fn ($v) => $v > 0));
|
|
if (empty($ids)) {
|
|
return ['ok' => false, 'message' => 'No movements selected.'];
|
|
}
|
|
|
|
// Check for corrections
|
|
$hasCorrections = InventoryMovement::query()
|
|
->whereIn('corrects_movement_id', $ids)
|
|
->exists();
|
|
if ($hasCorrections) {
|
|
return ['ok' => false, 'message' => 'Cannot delete movements that have corrections. Void them instead.'];
|
|
}
|
|
|
|
$itemIds = DB::table('inventory_movements')
|
|
->select('item_id')
|
|
->whereIn('id', $ids)
|
|
->get()
|
|
->map(fn ($r) => (int) ($r->item_id ?? 0))
|
|
->filter(fn ($v) => $v > 0)
|
|
->unique()
|
|
->values()
|
|
->all();
|
|
|
|
try {
|
|
InventoryMovement::query()->whereIn('id', $ids)->delete();
|
|
foreach ($itemIds as $itemId) {
|
|
$this->recalcQuantity($itemId);
|
|
}
|
|
} catch (\Throwable $e) {
|
|
Log::error('Inventory movement bulk delete failed: '.$e->getMessage());
|
|
|
|
return ['ok' => false, 'message' => 'Bulk delete failed.'];
|
|
}
|
|
|
|
return ['ok' => true, 'deleted' => count($ids)];
|
|
}
|
|
|
|
/**
|
|
* Record a new inventory movement (append-only).
|
|
* This is the primary method for all stock changes.
|
|
*/
|
|
public function recordMovement(
|
|
int $itemId,
|
|
int $qtyChange,
|
|
string $type,
|
|
?string $reason = null,
|
|
?string $note = null,
|
|
?int $classSectionId = null,
|
|
?int $studentId = null,
|
|
?int $performedBy = null
|
|
): bool {
|
|
$isInitial = ($type === 'initial');
|
|
|
|
if (! $isInitial) {
|
|
$this->ensureInitialMovement($itemId);
|
|
$this->ensureInitialMovementForYear($itemId, $this->context->schoolYear());
|
|
$this->recalcQuantity($itemId);
|
|
}
|
|
|
|
if (in_array($type, ['out', 'distribution'], true) && $qtyChange > 0) {
|
|
$qtyChange = -abs($qtyChange);
|
|
}
|
|
|
|
if (in_array($type, ['out', 'distribution'], true) && $this->wouldGoNegative($itemId, $qtyChange)) {
|
|
return false;
|
|
}
|
|
|
|
InventoryMovement::query()->create([
|
|
'item_id' => $itemId,
|
|
'qty_change' => $qtyChange,
|
|
'movement_type' => $type,
|
|
'reason' => $reason,
|
|
'note' => $note,
|
|
'semester' => $this->context->semester(),
|
|
'school_year' => $this->context->schoolYear(),
|
|
'performed_by' => $performedBy,
|
|
'teacher_id' => $performedBy,
|
|
'student_id' => $studentId,
|
|
'class_section_id' => $classSectionId,
|
|
]);
|
|
|
|
$this->recalcQuantity($itemId);
|
|
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* Void an existing movement (append-only correction).
|
|
* The original movement is preserved but marked as voided.
|
|
*/
|
|
public function voidMovement(int $movementId, string $reason, int $voidedBy): array
|
|
{
|
|
$movement = InventoryMovement::query()->find($movementId);
|
|
if (! $movement) {
|
|
return ['ok' => false, 'message' => 'Movement not found.'];
|
|
}
|
|
|
|
if ($movement->voided_at) {
|
|
return ['ok' => false, 'message' => 'Movement is already voided.'];
|
|
}
|
|
|
|
$itemId = (int) $movement->item_id;
|
|
|
|
try {
|
|
DB::transaction(function () use ($movement, $reason, $voidedBy, $itemId) {
|
|
// Mark original as voided
|
|
$movement->update([
|
|
'voided_at' => now(),
|
|
'voided_by' => $voidedBy,
|
|
'void_reason' => $reason,
|
|
]);
|
|
|
|
// Create reverse movement to correct the quantity
|
|
$reverseQty = -(int) $movement->qty_change;
|
|
if ($reverseQty !== 0) {
|
|
InventoryMovement::query()->create([
|
|
'item_id' => $itemId,
|
|
'qty_change' => $reverseQty,
|
|
'movement_type' => 'adjust',
|
|
'reason' => 'Correction: voided movement #'.$movement->id,
|
|
'note' => $reason,
|
|
'semester' => $this->context->semester(),
|
|
'school_year' => $this->context->schoolYear(),
|
|
'performed_by' => $voidedBy,
|
|
'corrects_movement_id' => $movement->id,
|
|
]);
|
|
}
|
|
|
|
$this->recalcQuantity($itemId);
|
|
});
|
|
} catch (\Throwable $e) {
|
|
Log::error('Inventory movement void failed: '.$e->getMessage());
|
|
|
|
return ['ok' => false, 'message' => 'Could not void movement.'];
|
|
}
|
|
|
|
return ['ok' => true];
|
|
}
|
|
|
|
/**
|
|
* Create a correction movement for an existing movement.
|
|
* This does NOT void the original; it adds an adjusting entry.
|
|
*/
|
|
public function correctMovement(int $movementId, int $correctedQtyChange, string $reason, int $performedBy): array
|
|
{
|
|
$movement = InventoryMovement::query()->find($movementId);
|
|
if (! $movement) {
|
|
return ['ok' => false, 'message' => 'Movement not found.'];
|
|
}
|
|
|
|
if ($movement->voided_at) {
|
|
return ['ok' => false, 'message' => 'Cannot correct a voided movement.'];
|
|
}
|
|
|
|
$itemId = (int) $movement->item_id;
|
|
|
|
if ($this->wouldGoNegative($itemId, $correctedQtyChange)) {
|
|
return ['ok' => false, 'message' => 'Not enough stock to apply correction.'];
|
|
}
|
|
|
|
try {
|
|
InventoryMovement::query()->create([
|
|
'item_id' => $itemId,
|
|
'qty_change' => $correctedQtyChange,
|
|
'movement_type' => 'adjust',
|
|
'reason' => 'Correction for movement #'.$movement->id,
|
|
'note' => $reason,
|
|
'semester' => $this->context->semester(),
|
|
'school_year' => $this->context->schoolYear(),
|
|
'performed_by' => $performedBy,
|
|
'corrects_movement_id' => $movement->id,
|
|
]);
|
|
|
|
$this->recalcQuantity($itemId);
|
|
} catch (\Throwable $e) {
|
|
Log::error('Inventory movement correction failed: '.$e->getMessage());
|
|
|
|
return ['ok' => false, 'message' => 'Could not correct movement.'];
|
|
}
|
|
|
|
return ['ok' => true];
|
|
}
|
|
|
|
public function recalcQuantity(int $itemId): void
|
|
{
|
|
$sumRow = InventoryMovement::query()
|
|
->selectRaw('SUM(qty_change) as qty_change')
|
|
->where('item_id', $itemId)
|
|
->whereNull('voided_at') // Exclude voided movements from calculations
|
|
->first();
|
|
|
|
$sum = (int) ($sumRow?->qty_change ?? 0);
|
|
if ($sum < 0) {
|
|
$sum = 0;
|
|
}
|
|
|
|
$item = InventoryItem::query()->select('id', 'type', 'quantity', 'needs_repair_qty', 'good_qty')->find($itemId);
|
|
if (! $item) {
|
|
return;
|
|
}
|
|
|
|
$updates = [];
|
|
if ((int) $item->quantity !== $sum) {
|
|
$updates['quantity'] = $sum;
|
|
}
|
|
|
|
if ($item->type === 'classroom') {
|
|
$needsRepair = (int) ($item->needs_repair_qty ?? 0);
|
|
$goodCalc = max(0, $sum - $needsRepair);
|
|
if ((int) ($item->good_qty ?? 0) !== $goodCalc) {
|
|
$updates['good_qty'] = $goodCalc;
|
|
}
|
|
}
|
|
|
|
if (! empty($updates)) {
|
|
$item->update($updates);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Reconcile inventory items - compare stored quantity against movement totals.
|
|
* Returns an array of items that have discrepancies.
|
|
*/
|
|
public function reconcile(?string $schoolYear = null): array
|
|
{
|
|
$schoolYear = $schoolYear ?: $this->context->schoolYear();
|
|
$discrepancies = [];
|
|
|
|
$items = InventoryItem::query()
|
|
->when($schoolYear, fn ($q) => $q->where('school_year', $schoolYear))
|
|
->get();
|
|
|
|
foreach ($items as $item) {
|
|
$movementSum = (int) InventoryMovement::query()
|
|
->where('item_id', $item->id)
|
|
->whereNull('voided_at')
|
|
->sum('qty_change');
|
|
|
|
$storedQty = (int) $item->quantity;
|
|
$variance = $storedQty - $movementSum;
|
|
|
|
if ($variance !== 0) {
|
|
$discrepancies[] = [
|
|
'id' => $item->id,
|
|
'name' => $item->name,
|
|
'stored_quantity' => $storedQty,
|
|
'movement_sum' => $movementSum,
|
|
'variance' => $variance,
|
|
];
|
|
}
|
|
}
|
|
|
|
return [
|
|
'school_year' => $schoolYear,
|
|
'total_items' => $items->count(),
|
|
'discrepancies_count' => count($discrepancies),
|
|
'discrepancies' => $discrepancies,
|
|
];
|
|
}
|
|
|
|
private function ensureInitialMovement(int $itemId): void
|
|
{
|
|
$hasMov = InventoryMovement::query()->where('item_id', $itemId)->exists();
|
|
if ($hasMov) {
|
|
return;
|
|
}
|
|
|
|
$item = InventoryItem::query()->select('quantity')->find($itemId);
|
|
$initialQty = (int) ($item?->quantity ?? 0);
|
|
|
|
InventoryMovement::query()->create([
|
|
'item_id' => $itemId,
|
|
'qty_change' => $initialQty,
|
|
'movement_type' => 'initial',
|
|
'semester' => $this->context->semester(),
|
|
'school_year' => $this->context->schoolYear(),
|
|
]);
|
|
}
|
|
|
|
private function ensureInitialMovementForYear(int $itemId, string $schoolYear): void
|
|
{
|
|
$hasAnyThisYear = InventoryMovement::query()
|
|
->where('item_id', $itemId)
|
|
->where('school_year', $schoolYear)
|
|
->exists();
|
|
|
|
if (! $hasAnyThisYear) {
|
|
$onHand = (int) (InventoryItem::query()->where('id', $itemId)->value('quantity') ?? 0);
|
|
InventoryMovement::query()->create([
|
|
'item_id' => $itemId,
|
|
'qty_change' => $onHand,
|
|
'movement_type' => 'initial',
|
|
'school_year' => $schoolYear,
|
|
'semester' => $this->context->semester() ?: null,
|
|
]);
|
|
|
|
return;
|
|
}
|
|
|
|
$hasInitial = InventoryMovement::query()
|
|
->where('item_id', $itemId)
|
|
->where('school_year', $schoolYear)
|
|
->where('movement_type', 'initial')
|
|
->exists();
|
|
|
|
if (! $hasInitial) {
|
|
$onHand = (int) (InventoryItem::query()->where('id', $itemId)->value('quantity') ?? 0);
|
|
$netYear = (int) (InventoryMovement::query()
|
|
->where('item_id', $itemId)
|
|
->where('school_year', $schoolYear)
|
|
->sum('qty_change') ?? 0);
|
|
$opening = $onHand - $netYear;
|
|
|
|
InventoryMovement::query()->create([
|
|
'item_id' => $itemId,
|
|
'qty_change' => $opening,
|
|
'movement_type' => 'initial',
|
|
'school_year' => $schoolYear,
|
|
'semester' => $this->context->semester() ?: null,
|
|
]);
|
|
}
|
|
}
|
|
|
|
private function normalizeMovementQty(string $movementType, int $qtyChange): int
|
|
{
|
|
if (in_array($movementType, ['out', 'distribution'], true) && $qtyChange > 0) {
|
|
return -abs($qtyChange);
|
|
}
|
|
|
|
return $qtyChange;
|
|
}
|
|
|
|
private function wouldGoNegative(int $itemId, int $delta): bool
|
|
{
|
|
$onHand = (int) (InventoryItem::query()->where('id', $itemId)->value('quantity') ?? 0);
|
|
|
|
return $onHand + $delta < 0;
|
|
}
|
|
|
|
private function nullableInt($val): ?int
|
|
{
|
|
if ($val === null || $val === '' || $val === '0') {
|
|
return null;
|
|
}
|
|
|
|
return (int) $val;
|
|
}
|
|
|
|
private function buildMovementPayload(array $data, int $itemId, int $qtyChange, string $movementType, int $performedBy): array
|
|
{
|
|
return [
|
|
'item_id' => $itemId,
|
|
'qty_change' => $qtyChange,
|
|
'movement_type' => $movementType,
|
|
'reason' => trim((string) ($data['reason'] ?? '')),
|
|
'note' => trim((string) ($data['note'] ?? '')),
|
|
'semester' => $data['semester'] ?? $this->context->semester(),
|
|
'school_year' => $data['school_year'] ?? $this->context->schoolYear(),
|
|
'performed_by' => $performedBy ?: null,
|
|
'teacher_id' => $this->nullableInt($data['teacher_id'] ?? null),
|
|
'student_id' => $this->nullableInt($data['student_id'] ?? null),
|
|
'class_section_id' => $this->nullableInt($data['class_section_id'] ?? null),
|
|
];
|
|
}
|
|
}
|