root
1671 lines
67 KiB
PHP
1671 lines
67 KiB
PHP
<?php
|
||
|
||
namespace App\Controllers\View;
|
||
|
||
use App\Controllers\BaseController;
|
||
use App\Models\ClassSectionModel;
|
||
use App\Models\InventoryItemModel;
|
||
use App\Models\InventoryCategoryModel;
|
||
use App\Models\ConfigurationModel;
|
||
use App\Models\TeacherClassModel;
|
||
use App\Models\StudentClassModel;
|
||
use App\Models\UserModel;
|
||
use App\Models\InventoryMovementModel;
|
||
use App\Models\TeacherModel;
|
||
|
||
class InventoryController extends BaseController
|
||
{
|
||
protected $itemModel;
|
||
protected $teacherModel;
|
||
protected $catModel;
|
||
protected $configModel;
|
||
protected $schoolYear;
|
||
protected $semester;
|
||
protected $movModel;
|
||
protected $teacherClassModel;
|
||
protected $studentClassModel;
|
||
protected $userModel;
|
||
protected $db;
|
||
protected $classSectionModel;
|
||
private array $types = ['classroom', 'book', 'office', 'kitchen'];
|
||
protected array $movementTypes = ['initial', 'in', 'out', 'adjust', 'distribution'];
|
||
protected array $semesters = ['Spring', 'Fall'];
|
||
|
||
|
||
public function __construct()
|
||
{
|
||
// Init models
|
||
$this->movModel = new InventoryMovementModel();
|
||
$this->userModel = new UserModel();
|
||
$this->configModel = new ConfigurationModel();
|
||
$this->itemModel = new InventoryItemModel();
|
||
$this->catModel = new InventoryCategoryModel();
|
||
$this->teacherClassModel = new TeacherClassModel();
|
||
$this->studentClassModel = new StudentClassModel();
|
||
$this->schoolYear = $this->configModel->getConfig('school_year');
|
||
$this->semester = $this->configModel->getConfig('semester');
|
||
$this->classSectionModel = new ClassSectionModel();
|
||
$this->db = \Config\Database::connect();
|
||
$this->teacherModel = new TeacherModel();
|
||
helper(['form', 'text']);
|
||
}
|
||
|
||
// app/Controllers/InventoryController.php (only the changed bits)
|
||
|
||
private function viewForIndex(string $type): string
|
||
{
|
||
// inventory/classroom/index, inventory/book/index, inventory/office/index, inventory/kitchen/index
|
||
return "inventory/{$type}/index";
|
||
}
|
||
|
||
private function viewForForm(string $type): string
|
||
{
|
||
// inventory/classroom/form, inventory/book/form, inventory/office/form, inventory/kitchen/form
|
||
return "inventory/{$type}/form";
|
||
}
|
||
|
||
public function index(string $type = 'classroom')
|
||
{
|
||
$type = $this->normalizeType($type);
|
||
|
||
$schoolYears = $this->getSchoolYearsForType($type);
|
||
$requested = trim((string) $this->request->getGet('school_year'));
|
||
$selectedYear = $requested !== '' ? $requested : $this->schoolYear;
|
||
$selectedSemRaw = $this->request->getGet('semester');
|
||
$selectedSem = $selectedSemRaw === null ? (string) $this->semester : trim((string) $selectedSemRaw);
|
||
|
||
$builder = $this->itemModel->where('type', $type);
|
||
if (strtolower($selectedYear) !== 'all') {
|
||
$builder->where('school_year', $selectedYear);
|
||
}
|
||
if ($selectedSem !== '') {
|
||
$builder->where('semester', $selectedSem);
|
||
}
|
||
|
||
$items = $builder->orderBy('name', 'ASC')->findAll();
|
||
$categories = $this->catModel->optionsForType($type);
|
||
|
||
// NEW: build UpdatedBy name map
|
||
$userIds = array_values(array_unique(array_map(fn($i) => (int)($i['updated_by'] ?? 0), $items)));
|
||
$userIds = array_values(array_filter($userIds));
|
||
$userNames = $this->userNamesByIds($userIds);
|
||
|
||
return view($this->viewForIndex($type), [
|
||
'type' => $type,
|
||
'items' => $items,
|
||
'categories' => $categories,
|
||
'conditionOptions' => ['good' => 'Good condition', 'needs_repair' => 'Needs repair', 'need_replace' => 'Need replace', 'cannot_find' => 'Cannot find'],
|
||
'schoolYears' => $schoolYears,
|
||
'selectedYear' => $selectedYear,
|
||
'currentYear' => $this->schoolYear,
|
||
'semester' => $selectedSem,
|
||
'userNames' => $userNames, // <-- pass to views
|
||
]);
|
||
}
|
||
|
||
public function edit(int $id)
|
||
{
|
||
$item = $this->itemModel->find($id);
|
||
if (!$item) return redirect()->back()->with('error', 'Item not found.');
|
||
|
||
// Load categories for THIS item type
|
||
$categories = $this->catModel->optionsForType($item['type']);
|
||
|
||
return view($this->viewForForm($item['type']), [
|
||
'type' => $item['type'],
|
||
'item' => $item,
|
||
'categories' => $categories,
|
||
'conditionOptions' => [
|
||
'good' => 'Good condition',
|
||
'needs_repair' => 'Needs repair',
|
||
'need_replace' => 'Need replace',
|
||
'cannot_find' => 'Cannot find'
|
||
],
|
||
'isEdit' => true, // <- tell the view we're editing
|
||
]);
|
||
}
|
||
|
||
public function update(int $id)
|
||
{
|
||
$item = $this->itemModel->find($id);
|
||
if (!$item) return redirect()->back()->with('error', 'Item not found.');
|
||
|
||
// lock to original type so users can’t switch books -> office, etc.
|
||
$data = $this->filterItemData($this->request->getPost(), $item['type']);
|
||
|
||
// Do not allow changing the PK via payload
|
||
unset($data['id']);
|
||
|
||
if (!$this->itemModel->update($id, $data)) {
|
||
return redirect()->back()->withInput()->with('error', implode(', ', $this->itemModel->errors()));
|
||
}
|
||
|
||
return redirect()->to(site_url('inventory/' . $item['type']))->with('success', 'Item updated.');
|
||
}
|
||
|
||
public function store()
|
||
{
|
||
$data = $this->request->getPost();
|
||
|
||
$itemData = $this->filterItemData($data);
|
||
// Use insert() to get ID cleanly
|
||
$itemId = $this->itemModel->insert($itemData, true);
|
||
if ($itemId) {
|
||
$initialQty = (int) ($itemData['quantity'] ?? 0);
|
||
if ($initialQty !== 0) {
|
||
$this->recordMovement($itemId, $initialQty, 'initial', 'Initial stock');
|
||
} else {
|
||
// ensure quantity is synced even if zero
|
||
$this->recalcQuantity($itemId);
|
||
}
|
||
return redirect()->to(site_url("inventory/{$itemData['type']}"))->with('success', 'Item added.');
|
||
}
|
||
return redirect()->back()->withInput()->with('error', implode(', ', $this->itemModel->errors()));
|
||
}
|
||
|
||
// Lazily ensure the very first movement = current on-hand, for legacy items
|
||
private function ensureInitialMovement(int $itemId): void
|
||
{
|
||
// Any movement already?
|
||
$hasMov = $this->movModel->where('item_id', $itemId)->limit(1)->countAllResults();
|
||
if ($hasMov > 0) return; // legacy seed only
|
||
|
||
$item = $this->itemModel->select('quantity')->find($itemId);
|
||
$initialQty = (int)($item['quantity'] ?? 0);
|
||
|
||
$this->movModel->insert([
|
||
'item_id' => $itemId,
|
||
'qty_change' => $initialQty,
|
||
'movement_type' => 'initial',
|
||
'semester' => $this->semester,
|
||
'school_year' => $this->schoolYear,
|
||
'performed_by' => $this->currentUserId(),
|
||
], false);
|
||
}
|
||
|
||
public function delete(int $id)
|
||
{
|
||
$item = $this->itemModel->find($id);
|
||
if ($item) {
|
||
$type = $item['type'];
|
||
$this->itemModel->delete($id);
|
||
return redirect()->to(site_url("inventory/{$type}"))->with('success', 'Item deleted.');
|
||
}
|
||
return redirect()->back()->with('error', 'Item not found.');
|
||
}
|
||
|
||
// Categories
|
||
public function saveCategory()
|
||
{
|
||
// Normalize inputs
|
||
$rawType = (string)($this->request->getPost('type') ?? '');
|
||
$type = strtolower(trim($rawType));
|
||
$id = $this->request->getPost('id');
|
||
|
||
// Collapse multiple spaces in the name and trim
|
||
$name = preg_replace('/\s+/', ' ', trim((string)$this->request->getPost('name')));
|
||
|
||
$data = [
|
||
'type' => in_array($type, ['classroom', 'book', 'office', 'kitchen'], true) ? $type : 'office',
|
||
'name' => $name,
|
||
'description' => (string)$this->request->getPost('description'),
|
||
];
|
||
|
||
// Only books have grade range
|
||
if ($data['type'] === 'book') {
|
||
$gmin = $this->request->getPost('grade_min');
|
||
$gmax = $this->request->getPost('grade_max');
|
||
|
||
$gmin = ($gmin === '' || $gmin === null) ? null : max(0, (int)$gmin);
|
||
$gmax = ($gmax === '' || $gmax === null) ? null : max(0, (int)$gmax);
|
||
|
||
if ($gmin !== null && $gmax !== null && $gmin > $gmax) {
|
||
return redirect()->back()->withInput()->with('error', 'Grade Min cannot be greater than Grade Max.');
|
||
}
|
||
if ($gmin !== null && $gmin > 13) $gmin = 13;
|
||
if ($gmax !== null && $gmax > 13) $gmax = 13;
|
||
|
||
$data['grade_min'] = $gmin;
|
||
$data['grade_max'] = $gmax;
|
||
} else {
|
||
$data['grade_min'] = null;
|
||
$data['grade_max'] = null;
|
||
}
|
||
|
||
// --- Duplicate guard for (type, name) pair ---
|
||
$existing = $this->catModel
|
||
->where('type', $data['type'])
|
||
->where('name', $data['name'])
|
||
->first();
|
||
|
||
try {
|
||
if ($id) {
|
||
// Updating: allow if the found record is THIS record, block otherwise
|
||
$pk = $this->catModel->primaryKey ?? 'id';
|
||
if ($existing && (int)$existing[$pk] !== (int)$id) {
|
||
return redirect()->back()->withInput()->with(
|
||
'error',
|
||
'A category with this Type & Name already exists. Please choose a different name or edit the existing one.'
|
||
);
|
||
}
|
||
$ok = $this->catModel->update((int)$id, $data);
|
||
} else {
|
||
// Creating: block if already exists
|
||
if ($existing) {
|
||
// If you PREFER auto-converting this into an update, uncomment the two lines below:
|
||
// $pk = $this->catModel->primaryKey ?? 'id';
|
||
// return $this->catModel->update((int)$existing[$pk], $data)
|
||
// ? redirect()->back()->with('success', 'Category already existed; details updated.')
|
||
// : redirect()->back()->withInput()->with('error', 'Failed to update existing category.');
|
||
|
||
return redirect()->back()->withInput()->with(
|
||
'error',
|
||
'This category already exists: ' . $data['type'] . ' — ' . $data['name'] . '.'
|
||
);
|
||
}
|
||
$ok = (bool) $this->catModel->insert($data);
|
||
}
|
||
} catch (\Throwable $e) {
|
||
// Handle duplicate at DB level (safety net)
|
||
$msg = $e->getMessage();
|
||
if (strpos($msg, 'Duplicate entry') !== false && strpos($msg, 'type_name') !== false) {
|
||
return redirect()->back()->withInput()->with(
|
||
'error',
|
||
'Duplicate Type/Name. A category with this combination already exists.'
|
||
);
|
||
}
|
||
log_message('critical', 'Failed to save category: {msg}', ['msg' => $msg]);
|
||
return redirect()->back()->withInput()->with('error', 'Failed to save category.');
|
||
}
|
||
|
||
if (!$ok) {
|
||
return redirect()->back()->withInput()->with('error', 'Failed to save category.');
|
||
}
|
||
|
||
return redirect()->back()->with('success', 'Category saved.');
|
||
}
|
||
|
||
public function optionsForType(string $type): array
|
||
{
|
||
return $this->asArray()
|
||
->select('id, name, description, grade_min, grade_max')
|
||
->where('type', $type)
|
||
->orderBy('name', 'ASC')
|
||
->findAll();
|
||
}
|
||
|
||
private function normalizeType(?string $type): string
|
||
{
|
||
$type = strtolower((string)$type);
|
||
return in_array($type, $this->types, true) ? $type : 'classroom';
|
||
}
|
||
|
||
private function currentUserId(): ?int
|
||
{
|
||
foreach (['user_id', 'id', 'uid', 'userId'] as $k) {
|
||
$v = session()->get($k);
|
||
if ($v !== null && $v !== '') return (int)$v;
|
||
}
|
||
return null;
|
||
}
|
||
|
||
|
||
// add a tiny helper (optional, but keeps things tidy)
|
||
private function post(string $key, $default = null)
|
||
{
|
||
$v = $this->request->getPost($key);
|
||
if ($v === '' || $v === null) return $default;
|
||
return is_string($v) ? trim($v) : $v;
|
||
}
|
||
|
||
private function filterItemData(array $data, ?string $lockedType = null): array
|
||
{
|
||
$type = $lockedType ?? $this->normalizeType($this->post('type', 'classroom'));
|
||
|
||
$qtyRaw = $this->request->getPost('quantity');
|
||
$quantity = is_numeric($qtyRaw) ? (int)$qtyRaw : 0;
|
||
|
||
$base = [
|
||
// 'id' should NOT be required; we pass PK separately on update()
|
||
'type' => $type,
|
||
'category_id' => ($this->post('category_id') !== null && $this->post('category_id') !== '') ? (int)$this->post('category_id') : null,
|
||
'name' => (string) $this->post('name', ''),
|
||
'description' => $this->post('description'),
|
||
'quantity' => $quantity,
|
||
'unit' => $this->post('unit'),
|
||
'sku' => $this->post('sku'),
|
||
'notes' => $this->post('notes'),
|
||
'semester' => $this->semester,
|
||
'school_year' => $this->schoolYear,
|
||
'updated_by' => $this->currentUserId(),
|
||
];
|
||
|
||
$base['condition'] = ($type === 'classroom') ? $this->post('condition') : null;
|
||
|
||
if ($type === 'book') {
|
||
$base['isbn'] = $this->post('isbn');
|
||
$base['edition'] = $this->post('edition');
|
||
} else {
|
||
$base['isbn'] = $base['edition'] = null;
|
||
}
|
||
|
||
return $base;
|
||
}
|
||
|
||
public function auditClassroomForm(int $itemId)
|
||
{
|
||
$item = $this->itemModel->find($itemId);
|
||
if (!$item || $item['type'] !== 'classroom') {
|
||
return redirect()->back()->with('error', 'Invalid classroom item.');
|
||
}
|
||
|
||
// Defaults if columns were just added
|
||
$item['good_qty'] = (int)($item['good_qty'] ?? 0);
|
||
$item['needs_repair_qty'] = (int)($item['needs_repair_qty'] ?? 0);
|
||
$item['need_replace_qty'] = (int)($item['need_replace_qty'] ?? 0);
|
||
$item['cannot_find_qty'] = (int)($item['cannot_find_qty'] ?? 0);
|
||
|
||
return view('inventory/classroom/audit_form', ['item' => $item]);
|
||
}
|
||
|
||
public function auditClassroomStore(int $itemId)
|
||
{
|
||
$item = $this->itemModel->find($itemId);
|
||
if (!$item || $item['type'] !== 'classroom') {
|
||
return redirect()->back()->with('error', 'Invalid classroom item.');
|
||
}
|
||
|
||
// Previous buckets
|
||
$prevGood = (int)($item['good_qty'] ?? 0);
|
||
$prevRepair = (int)($item['needs_repair_qty'] ?? 0);
|
||
$prevLoss = (int)($item['need_replace_qty'] ?? 0);
|
||
$prevMiss = (int)($item['cannot_find_qty'] ?? 0);
|
||
|
||
// New buckets (non-negative ints)
|
||
$newRepair = max(0, (int)$this->request->getPost('needs_repair_qty'));
|
||
$newLoss = max(0, (int)$this->request->getPost('need_replace_qty'));
|
||
$newMiss = max(0, (int)$this->request->getPost('cannot_find_qty'));
|
||
|
||
// Good is derived: keep total physical = good + repair (loss/missing are not on hand)
|
||
// We base it on current on-hand (item.quantity) and newRepair.
|
||
// If you want to enter "good" directly, you can switch to reading it from POST.
|
||
$onHandNow = (int)$item['quantity'];
|
||
$newGood = max(0, $onHandNow - $newRepair);
|
||
|
||
// Movements for loss/missing deltas (affects physical on-hand)
|
||
$deltaLoss = $newLoss - $prevLoss;
|
||
$deltaMiss = $newMiss - $prevMiss;
|
||
|
||
// Positive deltas mean more items are now total-loss/missing → stock OUT
|
||
$totalOut = 0;
|
||
if ($deltaLoss > 0) $totalOut += $deltaLoss;
|
||
if ($deltaMiss > 0) $totalOut += $deltaMiss;
|
||
|
||
// Negative deltas mean we recovered some previously lost/missing → stock IN
|
||
$totalIn = 0;
|
||
if ($deltaLoss < 0) $totalIn += -$deltaLoss;
|
||
if ($deltaMiss < 0) $totalIn += -$deltaMiss;
|
||
|
||
// Apply stock movements
|
||
if ($totalOut > 0) {
|
||
if (!$this->recordMovement($itemId, -$totalOut, 'out', 'Audit: loss/missing increased')) {
|
||
return redirect()->back()->withInput(); // error flashed inside recordMovement
|
||
}
|
||
}
|
||
if ($totalIn > 0) {
|
||
$this->recordMovement($itemId, +$totalIn, 'in', 'Audit: loss/missing decreased');
|
||
}
|
||
|
||
// Recalc on-hand now that movements applied
|
||
$this->recalcQuantity($itemId);
|
||
|
||
// Persist buckets
|
||
$this->itemModel->update($itemId, [
|
||
'good_qty' => $newGood,
|
||
'needs_repair_qty' => $newRepair,
|
||
'need_replace_qty' => $newLoss,
|
||
'cannot_find_qty' => $newMiss,
|
||
// also stamp audit user/time
|
||
'updated_by' => $this->currentUserId(),
|
||
'updated_at' => utc_now(),
|
||
]);
|
||
|
||
return redirect()->to(site_url('inventory/classroom'))->with('success', 'Audit saved.');
|
||
}
|
||
|
||
private function userNamesByIds(array $ids): array
|
||
{
|
||
$ids = array_values(array_unique(array_filter($ids, fn($v) => !empty($v))));
|
||
if (!$ids) return [];
|
||
|
||
if (!$this->db->tableExists('users')) return [];
|
||
|
||
// Detect available columns to build a safe SELECT
|
||
$cols = $this->db->getFieldNames('users'); // returns [] if empty
|
||
$hasFirst = in_array('first_name', $cols, true) || in_array('firstname', $cols, true);
|
||
$hasLast = in_array('last_name', $cols, true) || in_array('lastname', $cols, true);
|
||
$firstCol = in_array('first_name', $cols, true) ? 'first_name' : (in_array('firstname', $cols, true) ? 'firstname' : null);
|
||
$lastCol = in_array('last_name', $cols, true) ? 'last_name' : (in_array('lastname', $cols, true) ? 'lastname' : null);
|
||
$nameCol = in_array('name', $cols, true) ? 'name' : null;
|
||
$emailCol = in_array('email', $cols, true) ? 'email' : null;
|
||
|
||
if ($firstCol || $lastCol) {
|
||
$select = "id, TRIM(CONCAT(IFNULL($firstCol,''),' ',IFNULL($lastCol,''))) AS display_name";
|
||
} elseif ($nameCol) {
|
||
$select = "id, $nameCol AS display_name";
|
||
} elseif ($emailCol) {
|
||
$select = "id, $emailCol AS display_name";
|
||
} else {
|
||
// Last resort: just return IDs as labels
|
||
$out = [];
|
||
foreach ($ids as $id) $out[(int)$id] = 'User #' . $id;
|
||
return $out;
|
||
}
|
||
|
||
$rows = $this->db->table('users')->select($select)->whereIn('id', $ids)->get()->getResultArray();
|
||
$map = [];
|
||
foreach ($rows as $r) {
|
||
$disp = trim((string)($r['display_name'] ?? ''));
|
||
$map[(int)$r['id']] = $disp !== '' ? $disp : ('User #' . $r['id']);
|
||
}
|
||
return $map;
|
||
}
|
||
|
||
// Distinct school years for a given inventory type (newest first).
|
||
private function getSchoolYearsForType(string $type): array
|
||
{
|
||
// Pull distinct non-null school_year values for this type
|
||
$years = $this->itemModel
|
||
->select('school_year')
|
||
->where('type', $type)
|
||
->where('school_year IS NOT NULL', null, false)
|
||
->groupBy('school_year')
|
||
->orderBy('school_year', 'DESC')
|
||
->findColumn('school_year') ?? [];
|
||
|
||
// Normalize & de-dup
|
||
$years = array_values(array_unique(array_filter(array_map('trim', $years))));
|
||
|
||
// Ensure the current year (from config) appears as an option, even if no rows yet
|
||
$currentYear = $this->schoolYear;
|
||
if ($currentYear && !in_array($currentYear, $years, true)) {
|
||
array_unshift($years, $currentYear);
|
||
$years = array_values(array_unique($years));
|
||
}
|
||
|
||
return $years;
|
||
}
|
||
|
||
private function recordMovement(
|
||
int $itemId,
|
||
int $qtyChange,
|
||
string $type, // initial|in|out|adjust|distribution
|
||
?string $reason = null,
|
||
?string $note = null,
|
||
?int $classSectionId = null,
|
||
?int $studentId = null
|
||
): bool {
|
||
$me = $this->currentUserId();
|
||
$isInitial = ($type === 'initial');
|
||
|
||
// ✅ Only seed legacy/year opening for NON-initial inserts
|
||
if (!$isInitial) {
|
||
// Seed a one-time legacy opening movement if *no* movement exists for this item
|
||
$this->ensureInitialMovement($itemId);
|
||
|
||
// Ensure there is an opening for *this* school year
|
||
$this->ensureInitialMovementForYear($itemId, $this->schoolYear);
|
||
|
||
// Sync on-hand before validating outflow
|
||
$this->recalcQuantity($itemId);
|
||
}
|
||
|
||
// Normalize sign for out/distribution
|
||
if (in_array($type, ['out', 'distribution'], true) && $qtyChange > 0) {
|
||
$qtyChange = -abs($qtyChange);
|
||
}
|
||
|
||
// Prevent going negative on out/distribution
|
||
if (in_array($type, ['out', 'distribution'], true)) {
|
||
$onHand = (int) ($this->itemModel->select('quantity')->where('id', $itemId)->first()['quantity'] ?? 0);
|
||
if ($onHand + $qtyChange < 0) {
|
||
session()->setFlashdata('error', 'Not enough stock to complete this deduction.');
|
||
return false;
|
||
}
|
||
}
|
||
|
||
$ok = $this->movModel->insert([
|
||
'item_id' => $itemId,
|
||
'qty_change' => $qtyChange,
|
||
'movement_type' => $type,
|
||
'reason' => $reason,
|
||
'note' => $note,
|
||
'semester' => $this->semester,
|
||
'school_year' => $this->schoolYear,
|
||
'performed_by' => $me,
|
||
'teacher_id' => $me,
|
||
'student_id' => $studentId,
|
||
'class_section_id' => $classSectionId,
|
||
], false);
|
||
|
||
if ($ok) {
|
||
$this->recalcQuantity($itemId);
|
||
return true;
|
||
}
|
||
return false;
|
||
}
|
||
|
||
private function recalcQuantity(int $itemId): void
|
||
{
|
||
// Sum movements
|
||
$sumRow = $this->movModel
|
||
->selectSum('qty_change')
|
||
->where('item_id', $itemId)
|
||
->first();
|
||
|
||
$sum = (int)($sumRow['qty_change'] ?? 0);
|
||
if ($sum < 0) $sum = 0;
|
||
|
||
// Fetch current values we compare against
|
||
$item = $this->itemModel
|
||
->select('id, type, quantity, needs_repair_qty, good_qty')
|
||
->find($itemId);
|
||
|
||
if (!$item) return;
|
||
|
||
$updates = [];
|
||
|
||
// (optional) if you want quantity to mirror movement sum, keep this block
|
||
// If you prefer to leave quantity as entered, just delete this IF.
|
||
if ((int)$item['quantity'] !== $sum) {
|
||
$updates['quantity'] = $sum;
|
||
}
|
||
|
||
// Keep classroom Good = OnHand − NeedsRepair (derived)
|
||
if (($item['type'] ?? null) === 'classroom') {
|
||
$needsRepair = (int)($item['needs_repair_qty'] ?? 0);
|
||
$goodCalc = max(0, $sum - $needsRepair);
|
||
if (!array_key_exists('good_qty', $item) || (int)$item['good_qty'] !== $goodCalc) {
|
||
$updates['good_qty'] = $goodCalc;
|
||
}
|
||
}
|
||
|
||
// ✅ Only update if there is something to update
|
||
if (!empty($updates)) {
|
||
try {
|
||
$this->itemModel->update($itemId, $updates);
|
||
} catch (\CodeIgniter\Database\Exceptions\DataException $e) {
|
||
// This would happen if allowedFields stripped everything
|
||
log_message('error', 'recalcQuantity() skipped update: ' . $e->getMessage());
|
||
}
|
||
}
|
||
}
|
||
|
||
public function summary(string $type = 'classroom')
|
||
{
|
||
$type = $this->normalizeType($type);
|
||
$currentSem = $this->semester;
|
||
$currentYear = $this->schoolYear;
|
||
$selectedYear = trim((string) $this->request->getGet('school_year')) ?: $currentYear;
|
||
|
||
// Items of this type
|
||
$items = $this->itemModel->where('type', $type)->orderBy('name', 'ASC')->findAll();
|
||
$itemIds = array_map(fn($i) => (int)$i['id'], $items);
|
||
|
||
// Aggregate movements for the selected year (or all)
|
||
$agg = [];
|
||
if ($itemIds) {
|
||
$qb = $this->movModel->select('item_id,
|
||
SUM(CASE WHEN qty_change>0 THEN qty_change ELSE 0 END) AS received,
|
||
SUM(CASE WHEN qty_change<0 THEN -qty_change ELSE 0 END) AS issued')
|
||
->whereIn('item_id', $itemIds)
|
||
->groupBy('item_id');
|
||
|
||
if (strtolower($selectedYear) !== 'all') {
|
||
$qb->where('school_year', $selectedYear);
|
||
}
|
||
|
||
foreach ($qb->findAll() as $row) {
|
||
$agg[(int)$row['item_id']] = [
|
||
'received' => (int)$row['received'],
|
||
'issued' => (int)$row['issued'],
|
||
];
|
||
}
|
||
}
|
||
|
||
// school year options
|
||
$schoolYears = $this->getSchoolYearsForType($type);
|
||
|
||
return view('inventory/summary', [
|
||
'type' => $type,
|
||
'items' => $items,
|
||
'agg' => $agg,
|
||
'selectedYear' => $selectedYear,
|
||
'currentYear' => $currentYear,
|
||
'schoolYears' => $schoolYears,
|
||
]);
|
||
}
|
||
|
||
public function adjustForm(int $itemId)
|
||
{
|
||
$item = $this->itemModel->find($itemId);
|
||
if (!$item) return redirect()->back()->with('error', 'Item not found.');
|
||
return view('inventory/adjust_form', ['item' => $item, 'type' => $item['type']]);
|
||
}
|
||
|
||
public function adjustStore(int $itemId)
|
||
{
|
||
$item = $this->itemModel->find($itemId);
|
||
if (!$item) return redirect()->back()->with('error', 'Item not found.');
|
||
|
||
$mode = $this->request->getPost('mode'); // 'in' or 'out' or 'adjust'
|
||
$qty = (int) $this->request->getPost('quantity');
|
||
$reason = (string) $this->request->getPost('reason');
|
||
$note = (string) $this->request->getPost('note');
|
||
|
||
if ($qty <= 0) return redirect()->back()->with('error', 'Quantity must be > 0.');
|
||
|
||
$delta = ($mode === 'out') ? -abs($qty) : (($mode === 'in') ? abs($qty) : $qty);
|
||
$type = ($mode === 'out') ? 'out' : (($mode === 'in') ? 'in' : 'adjust');
|
||
|
||
if (!$this->recordMovement((int)$itemId, $delta, $type, $reason, $note)) {
|
||
return redirect()->back()->withInput(); // error is flashed inside recordMovement
|
||
}
|
||
|
||
return redirect()->to(site_url('inventory/summary/' . $item['type']))->with('success', 'Stock updated.');
|
||
}
|
||
|
||
/**
|
||
* Teacher book distribution page:
|
||
* - Locks to teacher’s class section if only one, else uses ?class_section_id
|
||
* - Filters BOOKS by category grade range (grade_min/grade_max) against class grade (class_id)
|
||
* - Groups books by CATEGORY (with grade range in the group label) for an <optgroup>-style select
|
||
* - Shows per-student prior distribution history for the selected book in the current school year
|
||
*/
|
||
public function teacherDistributeForm()
|
||
{
|
||
// 1) Build teacher/class context (auth/role/no-class handled inside)
|
||
$ctx = $this->buildTeacherClassContext();
|
||
if ($ctx instanceof \CodeIgniter\HTTP\RedirectResponse) {
|
||
return $ctx; // early exit on redirect (not logged in / no class / invalid role)
|
||
}
|
||
|
||
// 2) Decide selected class section (lock if teacher has only one)
|
||
$lockClassSection = \is_array($ctx['classSectionIds'] ?? null) && \count($ctx['classSectionIds']) === 1;
|
||
$classSectionId = $lockClassSection
|
||
? (int) ($ctx['defaultClassSectionId'] ?? 0)
|
||
: (int) ($this->request->getGet('class_section_id') ?? ($ctx['defaultClassSectionId'] ?? 0));
|
||
|
||
// 3) Convert class section -> class grade (class_id as integer grade level)
|
||
$classID = null;
|
||
if (!empty($classSectionId)) {
|
||
$classID = $this->classSectionModel->getClassId($classSectionId); // e.g., 3 for Grade 3
|
||
if ($classID !== null) $classID = (int) $classID;
|
||
}
|
||
|
||
// 4) Selected book (optional, to render student history and on-hand)
|
||
$itemId = (int) ($this->request->getGet('item_id') ?? 0);
|
||
|
||
// 5) Fetch BOOKS filtered by category grade range and GROUP BY CATEGORY
|
||
$db = \Config\Database::connect();
|
||
$builder = $db->table('inventory_items i')
|
||
->select('i.id,i.name,i.isbn,i.edition,i.quantity,i.category_id,i.type,
|
||
c.name AS category_name, c.grade_min, c.grade_max')
|
||
->join('inventory_categories c', 'c.id = i.category_id', 'left')
|
||
->where('i.type', 'book');
|
||
|
||
if (!empty($classID)) {
|
||
$builder->groupStart()
|
||
// Case A: both bounds set and classID between [min..max]
|
||
->groupStart()
|
||
->where('c.grade_min IS NOT NULL', null, false)
|
||
->where('c.grade_max IS NOT NULL', null, false)
|
||
->where('c.grade_min <=', (int)$classID)
|
||
->where('c.grade_max >=', (int)$classID)
|
||
->groupEnd()
|
||
|
||
// Case B: only min set AND equals min
|
||
->orGroupStart()
|
||
->where('c.grade_min IS NOT NULL', null, false)
|
||
->where('c.grade_max', null) // IS NULL
|
||
->where('c.grade_min', (int)$classID) // =
|
||
->groupEnd()
|
||
|
||
// Case C: only max set AND equals max
|
||
->orGroupStart()
|
||
->where('c.grade_max IS NOT NULL', null, false)
|
||
->where('c.grade_min', null) // IS NULL
|
||
->where('c.grade_max', (int)$classID) // =
|
||
->groupEnd()
|
||
->groupEnd();
|
||
}
|
||
|
||
$rows = $builder->orderBy('i.name', 'ASC')->get()->getResultArray();
|
||
|
||
// Build CATEGORY groups keyed by category_id
|
||
$labelFor = static function (?array $r): string {
|
||
$name = $r['category_name'] ?? 'Uncategorized';
|
||
$gmin = $r['grade_min'] ?? null;
|
||
$gmax = $r['grade_max'] ?? null;
|
||
if ($gmin === null && $gmax === null) return $name;
|
||
if ($gmin !== null && $gmax !== null) return $name . ' (G' . (int)$gmin . '–G' . (int)$gmax . ')';
|
||
if ($gmin !== null) return $name . ' (G' . (int)$gmin . '+)';
|
||
return $name . ' (≤G' . (int)$gmax . ')';
|
||
};
|
||
|
||
// booksGrouped: [catId => ['label'=>string, 'items'=>[['id'=>..,'display'=>..,'quantity'=>..], ...]]]
|
||
$booksGrouped = [];
|
||
foreach ($rows as $r) {
|
||
$catId = (int)($r['category_id'] ?? 0);
|
||
if (!isset($booksGrouped[$catId])) {
|
||
$booksGrouped[$catId] = [
|
||
'label' => $labelFor($r),
|
||
'items' => [],
|
||
];
|
||
}
|
||
$display = $r['name'];
|
||
if (!empty($r['isbn'])) $display .= ' — ISBN ' . $r['isbn'];
|
||
if (!empty($r['edition'])) $display .= ' (' . $r['edition'] . ')';
|
||
|
||
$booksGrouped[$catId]['items'][] = [
|
||
'id' => (int)$r['id'],
|
||
'display' => $display,
|
||
'quantity' => (int)($r['quantity'] ?? 0),
|
||
];
|
||
}
|
||
|
||
// Sort groups by label, and each group’s items by display
|
||
uasort($booksGrouped, fn($a, $b) => strnatcasecmp($a['label'], $b['label']));
|
||
foreach ($booksGrouped as &$grp) {
|
||
usort($grp['items'], fn($x, $y) => strnatcasecmp($x['display'], $y['display']));
|
||
}
|
||
unset($grp);
|
||
|
||
// 6) Students of the selected class section (provided by context)
|
||
$students = $ctx['studentsData'][$classSectionId] ?? [];
|
||
|
||
// 7) History map: how many of the selected book each student already received this school year
|
||
$already = [];
|
||
if ($itemId && $classSectionId) {
|
||
$rowsHist = $this->movModel
|
||
->select('student_id, SUM(CASE WHEN qty_change < 0 THEN -qty_change ELSE 0 END) AS qty')
|
||
->where([
|
||
'item_id' => $itemId,
|
||
'movement_type' => 'distribution',
|
||
'class_section_id' => $classSectionId,
|
||
'school_year' => $ctx['schoolYear'],
|
||
])
|
||
->groupBy('student_id')
|
||
->findAll();
|
||
|
||
foreach ($rowsHist as $r) {
|
||
$sid = (int) ($r['student_id'] ?? 0);
|
||
if ($sid) $already[$sid] = (int) ($r['qty'] ?? 0);
|
||
}
|
||
}
|
||
|
||
// 8) On-hand for the selected book (to show live available stock)
|
||
$onHand = 0;
|
||
if ($itemId) {
|
||
$book = $this->itemModel->find($itemId);
|
||
if ($book && ($book['type'] ?? '') === 'book') {
|
||
$onHand = (int) ($book['quantity'] ?? 0);
|
||
}
|
||
}
|
||
|
||
// 9) Render view
|
||
return view('inventory/teacher_distribute', [
|
||
'booksGrouped' => $booksGrouped, // grouped by CATEGORY for <optgroup>
|
||
'items' => $rows, // raw list if needed elsewhere (optional)
|
||
|
||
'classes' => $ctx['classes'],
|
||
'class_section_id' => $classSectionId,
|
||
'lockClassSection' => $lockClassSection,
|
||
|
||
'item_id' => $itemId,
|
||
'students' => $students,
|
||
'already' => $already,
|
||
'onHand' => $onHand,
|
||
|
||
'schoolYear' => $ctx['schoolYear'],
|
||
'semester' => $ctx['semester'],
|
||
]);
|
||
}
|
||
|
||
public function teacherDistributeStore()
|
||
{
|
||
$ctx = $this->buildTeacherClassContext();
|
||
if ($ctx instanceof \CodeIgniter\HTTP\RedirectResponse) {
|
||
return $ctx;
|
||
}
|
||
|
||
$itemId = (int)$this->request->getPost('item_id');
|
||
$postedClassId = (int)$this->request->getPost('class_section_id');
|
||
$selectedIds = (array)$this->request->getPost('student_ids');
|
||
$note = (string)$this->request->getPost('note');
|
||
|
||
// Enforce teacher’s classes
|
||
$classIds = $ctx['classSectionIds'];
|
||
$classSectionId = (count($classIds) === 1)
|
||
? (int)$classIds[0]
|
||
: (in_array($postedClassId, $classIds, true) ? $postedClassId : 0);
|
||
|
||
if (!$classSectionId) {
|
||
return redirect()->back()->with('error', 'Invalid class selection.');
|
||
}
|
||
|
||
// Validate book
|
||
$book = $this->itemModel->find($itemId);
|
||
if (!$book || $book['type'] !== 'book') {
|
||
return redirect()->back()->with('error', 'Invalid book.');
|
||
}
|
||
|
||
// Validate students belong to the class
|
||
$students = $ctx['studentsData'][$classSectionId] ?? [];
|
||
$validIds = [];
|
||
foreach ($students as $st) {
|
||
$sid = $st['student_id'] ?? ($st['id'] ?? ($st['user_id'] ?? null));
|
||
if ($sid) $validIds[(int)$sid] = true;
|
||
}
|
||
$selectedIds = array_values(array_unique(array_map('intval', $selectedIds)));
|
||
$selectedIds = array_values(array_filter($selectedIds, fn($sid) => isset($validIds[$sid])));
|
||
|
||
// Already distributed this year for this class/book
|
||
$already = [];
|
||
$rows = $this->movModel
|
||
->select('student_id, SUM(CASE WHEN qty_change < 0 THEN -qty_change ELSE 0 END) AS qty')
|
||
->where([
|
||
'item_id' => $itemId,
|
||
'movement_type' => 'distribution',
|
||
'class_section_id' => $classSectionId,
|
||
'school_year' => $ctx['schoolYear'],
|
||
])->groupBy('student_id')->findAll();
|
||
foreach ($rows as $r) {
|
||
$sid = (int)($r['student_id'] ?? 0);
|
||
if ($sid) $already[$sid] = (int)$r['qty'];
|
||
}
|
||
|
||
// Only assign to students who don't already have it
|
||
$toGive = [];
|
||
foreach ($selectedIds as $sid) {
|
||
if (($already[$sid] ?? 0) < 1) $toGive[] = $sid;
|
||
}
|
||
|
||
// Stock check
|
||
$needed = count($toGive);
|
||
$onHand = (int)$book['quantity'];
|
||
if ($needed > $onHand) {
|
||
return redirect()->back()->withInput()->with('error', 'Not enough stock: need ' . $needed . ', on hand ' . $onHand . '.');
|
||
}
|
||
|
||
// Record one movement per student
|
||
$okCount = 0;
|
||
foreach ($toGive as $sid) {
|
||
$ok = $this->recordMovement($itemId, -1, 'distribution', 'Teacher distribution', $note, $classSectionId, $sid);
|
||
if ($ok) $okCount++;
|
||
}
|
||
|
||
if ($okCount === 0) {
|
||
return redirect()
|
||
->to(site_url('inventory/books/distribute?class_section_id=' . $classSectionId . '&item_id=' . $itemId))
|
||
->with('warning', 'No changes (everyone selected already has this book).');
|
||
}
|
||
|
||
return redirect()
|
||
->to(site_url('inventory/books/distribute?class_section_id=' . $classSectionId . '&item_id=' . $itemId))
|
||
->with('success', 'Distributed to ' . $okCount . ' student(s).');
|
||
}
|
||
|
||
|
||
/**
|
||
* Builds the same context your TeacherController::classView() creates.
|
||
* Returns an array with: user, classes, roleNames, classSectionIds, studentsData, taNames, defaultClassSectionId
|
||
* Throws a redirect Response (return it) when access is invalid (not logged in / no classes / invalid role).
|
||
*/
|
||
private function buildTeacherClassContext()
|
||
{
|
||
// 1) Logged-in user?
|
||
$user_id = session()->get('user_id');
|
||
if (!$user_id) {
|
||
return redirect()->to('/login')->with('error', 'Please log in first');
|
||
}
|
||
|
||
// 2) All assignments (main or TA) for current school year
|
||
$classes = $this->teacherClassModel->getClassAssignmentsByUserId($user_id, $this->schoolYear);
|
||
|
||
if (empty($classes)) {
|
||
return redirect()->to('no-classes')->with('message', 'You do not have an assigned class yet. Please contact the administration.');
|
||
}
|
||
|
||
// 3) Identify user & role
|
||
$user = $this->userModel->find($user_id);
|
||
if (!$user) {
|
||
return redirect()->to('/error')->with('error', 'User not found');
|
||
}
|
||
|
||
$roles = $this->db->table('user_roles ur')
|
||
->select('r.name')
|
||
->join('roles r', 'r.id = ur.role_id')
|
||
->where('ur.user_id', $user_id)
|
||
->get()->getResultArray();
|
||
|
||
$roleNames = array_map(fn($r) => $r['name'], $roles);
|
||
|
||
if (in_array('teacher', $roleNames, true)) {
|
||
$user['role_name'] = 'teacher';
|
||
} elseif (in_array('teacher_assistant', $roleNames, true)) {
|
||
$user['role_name'] = 'teacher_assistant';
|
||
} else {
|
||
return redirect()->to('/error')->with('error', 'Access denied');
|
||
}
|
||
|
||
// 4) Collect class_section IDs
|
||
$classSectionIds = array_column($classes, 'class_section_id');
|
||
$studentsData = [];
|
||
|
||
// 5) Group students by class_section_id
|
||
if (!empty($classSectionIds)) {
|
||
$students = $this->studentClassModel->getStudentsByClassSectionIds($classSectionIds);
|
||
foreach ($students as $student) {
|
||
$studentsData[$student['class_section_id']][] = $student;
|
||
}
|
||
}
|
||
|
||
// 6) TA names per class_section (same as your code)
|
||
$taAssignments = $this->teacherClassModel
|
||
->whereIn('class_section_id', $classSectionIds)
|
||
->where('school_year', $this->schoolYear)
|
||
->where('position', 'ta')
|
||
->findAll();
|
||
|
||
$taNames = [];
|
||
foreach ($taAssignments as $ta) {
|
||
$taUser = $this->userModel->find($ta['teacher_id']);
|
||
if ($taUser) {
|
||
$csid = $ta['class_section_id'];
|
||
$taNames[$csid][] = ($taUser['firstname'] ?? '') . ' ' . ($taUser['lastname'] ?? '');
|
||
}
|
||
}
|
||
|
||
// 7) Default selection = active session choice (if valid) or first class
|
||
$sessionId = (int)(session()->get('class_section_id') ?? 0);
|
||
if ($sessionId && in_array($sessionId, $classSectionIds, true)) {
|
||
$defaultClassSectionId = $sessionId;
|
||
} else {
|
||
$defaultClassSectionId = $classSectionIds[0] ?? null;
|
||
if ($defaultClassSectionId) {
|
||
session()->set('class_section_id', $defaultClassSectionId);
|
||
}
|
||
}
|
||
|
||
return [
|
||
'user' => $user,
|
||
'roleNames' => $roleNames,
|
||
'classes' => $classes,
|
||
'classSectionIds' => $classSectionIds,
|
||
'studentsData' => $studentsData,
|
||
'taNames' => $taNames,
|
||
'defaultClassSectionId' => $defaultClassSectionId,
|
||
'schoolYear' => $this->schoolYear,
|
||
'semester' => $this->semester,
|
||
'user_id' => $user_id,
|
||
];
|
||
}
|
||
|
||
// Ensure an initial movement exists for THIS school year, even if we started mid-year
|
||
private function ensureInitialMovementForYear(int $itemId, string $schoolYear): void
|
||
{
|
||
// If we already have any movement for this item in this year, make sure an 'initial' exists; else create one.
|
||
$hasAnyThisYear = $this->movModel
|
||
->where('item_id', $itemId)
|
||
->where('school_year', $schoolYear)
|
||
->limit(1)->countAllResults();
|
||
|
||
// Already has any movement this year?
|
||
if ($hasAnyThisYear === 0) {
|
||
// No movement at all this year → opening = current on-hand
|
||
$onHand = (int) ($this->itemModel->select('quantity')->where('id', $itemId)->first()['quantity'] ?? 0);
|
||
$this->movModel->insert([
|
||
'item_id' => $itemId,
|
||
'qty_change' => $onHand,
|
||
'movement_type' => 'initial',
|
||
'school_year' => $schoolYear,
|
||
'semester' => $this->semester ?? null,
|
||
'performed_by' => $this->currentUserId(),
|
||
], false);
|
||
return;
|
||
}
|
||
|
||
// We have some movement this year. If there's no 'initial' yet, backfill a correct opening.
|
||
$hasInitialThisYear = $this->movModel
|
||
->where('item_id', $itemId)
|
||
->where('school_year', $schoolYear)
|
||
->where('movement_type', 'initial')
|
||
->limit(1)->countAllResults();
|
||
|
||
if ($hasInitialThisYear === 0) {
|
||
// Opening = current_on_hand - net_year_movements
|
||
$onHand = (int) ($this->itemModel->select('quantity')->where('id', $itemId)->first()['quantity'] ?? 0);
|
||
$netYear = (int) ($this->movModel->selectSum('qty_change')
|
||
->where('item_id', $itemId)
|
||
->where('school_year', $schoolYear)
|
||
->first()['qty_change'] ?? 0);
|
||
|
||
$opening = $onHand - $netYear; // reconstruct beginning-of-year truth
|
||
$this->movModel->insert([
|
||
'item_id' => $itemId,
|
||
'qty_change' => $opening,
|
||
'movement_type' => 'initial',
|
||
'school_year' => $schoolYear,
|
||
'semester' => $this->semester ?? null,
|
||
'performed_by' => $this->currentUserId(),
|
||
], false);
|
||
}
|
||
}
|
||
|
||
public function create(string $type = 'classroom')
|
||
{
|
||
$categories = $this->catModel->optionsForType($type);
|
||
return view("inventory/{$type}/form", [
|
||
'type' => $type,
|
||
'item' => [], // important for create
|
||
'categories' => $categories,
|
||
'conditionOptions' => [
|
||
'good' => 'Good condition',
|
||
'needs_repair' => 'Needs repair',
|
||
'need_replace' => 'Need replace',
|
||
'cannot_find' => 'Cannot find',
|
||
],
|
||
]);
|
||
}
|
||
|
||
public function summaryAll()
|
||
{
|
||
// -------- Inputs --------
|
||
$selectedYear = trim((string) $this->request->getGet('school_year')) ?: (string) $this->schoolYear;
|
||
$selectedType = strtolower((string) ($this->request->getGet('type') ?? 'all')); // optional: all|book|classroom|office|kitchen
|
||
$isAllYears = (strtolower($selectedYear) === 'all');
|
||
|
||
// -------- Items query (single source of truth) --------
|
||
$qbItems = $this->db->table('inventory_items i')
|
||
->select('i.*, c.name AS category_name, CONCAT(u.firstname, " ", u.lastname) AS updated_by_name')
|
||
->join('inventory_categories c', 'c.id = i.category_id', 'left')
|
||
->join('users u', 'u.id = i.updated_by', 'left')
|
||
->orderBy('i.name', 'ASC');
|
||
|
||
// Filter by school year unless "all"
|
||
if (!$isAllYears) {
|
||
$qbItems->where('i.school_year', $selectedYear);
|
||
}
|
||
|
||
// Optional: filter by type if provided
|
||
if (in_array($selectedType, ['book', 'classroom', 'office', 'kitchen'], true)) {
|
||
$qbItems->where('i.type', $selectedType);
|
||
}
|
||
|
||
$items = $qbItems->get()->getResultArray();
|
||
|
||
// -------- Prepare output structures --------
|
||
$rows = [];
|
||
$totals = [
|
||
'opening' => 0,
|
||
'received' => 0,
|
||
'distributed' => 0,
|
||
'other_out' => 0,
|
||
'adjust_net' => 0,
|
||
'ending' => 0,
|
||
'onhand' => 0,
|
||
'variance' => 0,
|
||
];
|
||
|
||
// If no items, render an empty table gracefully
|
||
if (empty($items)) {
|
||
return view('inventory/summary', [
|
||
'selectedYear' => $selectedYear,
|
||
'currentYear' => $this->schoolYear,
|
||
'schoolYears' => $this->getSchoolYearsForType('book') ?: [$this->schoolYear, 'All'],
|
||
'rows' => $rows,
|
||
'totals' => $totals,
|
||
// optionally pass the selected type if your view supports it
|
||
'selectedType' => $selectedType,
|
||
]);
|
||
}
|
||
|
||
// -------- Aggregate movements for the fetched items --------
|
||
$itemIds = array_map('intval', array_column($items, 'id'));
|
||
|
||
$aggById = [];
|
||
$qb = $this->db->table('inventory_movements m')
|
||
->select("
|
||
m.item_id,
|
||
SUM(CASE WHEN m.movement_type = 'initial' THEN m.qty_change ELSE 0 END) AS opening,
|
||
SUM(CASE WHEN m.qty_change > 0 AND m.movement_type IN ('in','adjust') THEN m.qty_change ELSE 0 END) AS received,
|
||
SUM(CASE WHEN m.qty_change < 0 AND m.movement_type = 'distribution' THEN -m.qty_change ELSE 0 END) AS distributed,
|
||
SUM(CASE WHEN m.qty_change < 0 AND m.movement_type = 'out' THEN -m.qty_change ELSE 0 END) AS other_out,
|
||
SUM(CASE WHEN m.movement_type = 'adjust' THEN m.qty_change ELSE 0 END) AS adjust_net
|
||
")
|
||
->whereIn('m.item_id', $itemIds)
|
||
->groupBy('m.item_id');
|
||
|
||
if (!$isAllYears) {
|
||
$qb->where('m.school_year', $selectedYear);
|
||
}
|
||
|
||
foreach ($qb->get()->getResultArray() as $r) {
|
||
$aggById[(int)$r['item_id']] = [
|
||
'opening' => (int)($r['opening'] ?? 0),
|
||
'received' => (int)($r['received'] ?? 0),
|
||
'distributed' => (int)($r['distributed'] ?? 0),
|
||
'other_out' => (int)($r['other_out'] ?? 0),
|
||
'adjust_net' => (int)($r['adjust_net'] ?? 0),
|
||
];
|
||
}
|
||
|
||
// -------- Build rows + totals --------
|
||
foreach ($items as $it) {
|
||
$id = (int)$it['id'];
|
||
$a = $aggById[$id] ?? ['opening' => 0, 'received' => 0, 'distributed' => 0, 'other_out' => 0, 'adjust_net' => 0];
|
||
|
||
$opening = (int)$a['opening'];
|
||
$received = (int)$a['received'];
|
||
$distributed = (int)$a['distributed'];
|
||
$otherOut = (int)$a['other_out'];
|
||
$adjustNet = (int)$a['adjust_net'];
|
||
|
||
$ending = $opening + $received - $distributed - $otherOut + $adjustNet;
|
||
$onhand = (int)$it['quantity'];
|
||
$variance = $onhand - $ending;
|
||
|
||
$rows[] = [
|
||
'id' => $id,
|
||
'name' => (string)$it['name'],
|
||
'type' => (string)$it['type'],
|
||
'category' => (string)($it['category_name'] ?? ''),
|
||
'opening' => $opening,
|
||
'received' => $received,
|
||
'distributed' => $distributed,
|
||
'other_out' => $otherOut,
|
||
'adjust_net' => $adjustNet,
|
||
'ending' => $ending,
|
||
'onhand' => $onhand,
|
||
'variance' => $variance,
|
||
];
|
||
|
||
$totals['opening'] += $opening;
|
||
$totals['received'] += $received;
|
||
$totals['distributed'] += $distributed;
|
||
$totals['other_out'] += $otherOut;
|
||
$totals['adjust_net'] += $adjustNet;
|
||
$totals['ending'] += $ending;
|
||
$totals['onhand'] += $onhand;
|
||
$totals['variance'] += $variance;
|
||
}
|
||
|
||
// -------- Year dropdown options --------
|
||
$schoolYears = $this->getSchoolYearsForType('book') ?: [$this->schoolYear, 'All'];
|
||
|
||
return view('inventory/summary', [
|
||
'selectedYear' => $selectedYear,
|
||
'currentYear' => $this->schoolYear,
|
||
'schoolYears' => $schoolYears,
|
||
'rows' => $rows,
|
||
'totals' => $totals,
|
||
'selectedType' => $selectedType,
|
||
]);
|
||
}
|
||
|
||
/** =========================
|
||
* LIST
|
||
* ========================= */
|
||
public function movementsIndex()
|
||
{
|
||
// Academic filters
|
||
$selectedYearRaw = $this->request->getGet('school_year');
|
||
$selectedSemRaw = $this->request->getGet('semester');
|
||
$selectedYear = $selectedYearRaw === null ? (string) $this->schoolYear : trim((string) $selectedYearRaw);
|
||
$selectedSem = $selectedSemRaw === null ? (string) $this->semester : trim((string) $selectedSemRaw);
|
||
|
||
// Pull movements with friendly names when possible
|
||
$builder = $this->db->table('inventory_movements m')
|
||
->select([
|
||
'm.*',
|
||
'ii.name AS item_name',
|
||
|
||
// Performed by (users)
|
||
"CONCAT(pb.firstname, ' ', pb.lastname) AS performed_by_name",
|
||
|
||
// Teacher (users)
|
||
"CONCAT(t.firstname, ' ', t.lastname) AS teacher_name",
|
||
|
||
// Student (students)
|
||
"CONCAT(s.firstname, ' ', s.lastname) AS student_name",
|
||
|
||
// Class section
|
||
'cs.class_section_name',
|
||
])
|
||
->join('inventory_items ii', 'ii.id = m.item_id', 'left')
|
||
->join('users pb', 'pb.id = m.performed_by', 'left')
|
||
->join('users t', 't.id = m.teacher_id', 'left')
|
||
->join('students s', 's.id = m.student_id', 'left')
|
||
->join('classSection cs', 'cs.class_section_id = m.class_section_id', 'left')
|
||
->orderBy('m.id', 'DESC');
|
||
|
||
if ($selectedYear !== '') $builder->where('m.school_year', $selectedYear);
|
||
if ($selectedSem !== '') $builder->where('m.semester', $selectedSem);
|
||
|
||
$movements = $builder->get()->getResultArray();
|
||
|
||
return view('inventory/movements/index', [
|
||
'movements' => $movements,
|
||
'schoolYear'=> $selectedYear,
|
||
'semester' => $selectedSem,
|
||
]);
|
||
}
|
||
|
||
/** =========================
|
||
* CREATE (form)
|
||
* ========================= */
|
||
public function movementsCreate()
|
||
{
|
||
return view('inventory/movements/form', [
|
||
'movement' => $this->blankMovement(),
|
||
'movementTypes' => $this->movementTypes,
|
||
'semesters' => $this->semesters,
|
||
'items' => $this->getItems(),
|
||
'classSections' => $this->getClassSections(),
|
||
'teachers' => $this->getTeachers(),
|
||
'students' => $this->getStudents(),
|
||
'action' => site_url('inventory/movements/store'),
|
||
'title' => 'Create Inventory Movement',
|
||
'isCreate' => true,
|
||
]);
|
||
}
|
||
|
||
/** =========================
|
||
* STORE (POST)
|
||
* ========================= */
|
||
public function movementsStore()
|
||
{
|
||
$rules = [
|
||
'item_id' => 'required|is_natural_no_zero',
|
||
'qty_change' => 'required|integer',
|
||
'movement_type' => 'required|in_list[' . implode(',', $this->movementTypes) . ']',
|
||
'reason' => 'permit_empty|max_length[120]',
|
||
'note' => 'permit_empty|string',
|
||
'semester' => 'permit_empty|in_list[' . implode(',', $this->semesters) . ']',
|
||
'school_year' => 'permit_empty|max_length[16]',
|
||
'teacher_id' => 'permit_empty|is_natural_no_zero',
|
||
'student_id' => 'permit_empty|is_natural_no_zero',
|
||
'class_section_id' => 'permit_empty|is_natural_no_zero',
|
||
];
|
||
|
||
if (! $this->validate($rules)) {
|
||
return redirect()->back()->withInput()->with('status', 'error')
|
||
->with('message', implode(' ', $this->validator->getErrors()));
|
||
}
|
||
|
||
$userId = (int) (session('user_id') ?? 0);
|
||
$itemId = (int) $this->request->getPost('item_id');
|
||
$movementType = (string) $this->request->getPost('movement_type');
|
||
|
||
$item = $this->itemModel->find($itemId);
|
||
if (!$item) {
|
||
return redirect()->back()->withInput()->with('status', 'error')
|
||
->with('message', 'Selected item not found.');
|
||
}
|
||
|
||
$qtyChange = $this->normalizeMovementQty($movementType, (int) $this->request->getPost('qty_change'));
|
||
if ($this->wouldGoNegative($itemId, $qtyChange)) {
|
||
return redirect()->back()->withInput();
|
||
}
|
||
|
||
$semester = trim((string) $this->request->getPost('semester'));
|
||
$schoolYear = trim((string) $this->request->getPost('school_year'));
|
||
if ($semester === '') $semester = (string) $this->semester;
|
||
if ($schoolYear === '') $schoolYear = (string) $this->schoolYear;
|
||
|
||
$data = [
|
||
'item_id' => $itemId,
|
||
'qty_change' => $qtyChange,
|
||
'movement_type' => $movementType,
|
||
'reason' => trim((string)$this->request->getPost('reason')),
|
||
'note' => trim((string)$this->request->getPost('note')),
|
||
'semester' => $semester ?: null,
|
||
'school_year' => $schoolYear ?: null,
|
||
'performed_by' => $userId ?: null,
|
||
'teacher_id' => $this->nullableInt($this->request->getPost('teacher_id')),
|
||
'student_id' => $this->nullableInt($this->request->getPost('student_id')),
|
||
'class_section_id' => $this->nullableInt($this->request->getPost('class_section_id')),
|
||
'created_at' => utc_now(),
|
||
'updated_at' => utc_now(),
|
||
];
|
||
|
||
$ok = $this->db->table('inventory_movements')->insert($data);
|
||
|
||
if (! $ok) {
|
||
return redirect()->back()->withInput()->with('status', 'error')
|
||
->with('message', 'Could not save movement.');
|
||
}
|
||
|
||
$this->recalcQuantity($itemId);
|
||
|
||
return redirect()->to(site_url('inventory/movements'))
|
||
->with('status', 'success')
|
||
->with('message', 'Movement created.');
|
||
}
|
||
|
||
/** =========================
|
||
* EDIT (form)
|
||
* ========================= */
|
||
public function movementsEdit(int $id)
|
||
{
|
||
$movement = $this->db->table('inventory_movements')->where('id', $id)->get()->getRowArray();
|
||
if (!$movement) {
|
||
return redirect()->to(site_url('inventory/movements'))
|
||
->with('status', 'error')->with('message', 'Movement not found.');
|
||
}
|
||
|
||
// enrich for display (names)
|
||
$movement = $this->hydrateNames($movement);
|
||
|
||
return view('inventory/movements/form', [
|
||
'movement' => $movement, // already hydrated by your helper
|
||
'movementTypes' => $this->movementTypes,
|
||
'semesters' => $this->semesters,
|
||
'items' => $this->getItems(),
|
||
'classSections' => $this->getClassSections(),
|
||
'teachers' => $this->getTeachers(),
|
||
'students' => $this->getStudents(),
|
||
'action' => site_url('inventory/movements/update/' . $id),
|
||
'title' => 'Edit Inventory Movement',
|
||
'isCreate' => false,
|
||
]);
|
||
}
|
||
|
||
/** =========================
|
||
* UPDATE (POST)
|
||
* ========================= */
|
||
public function movementsUpdate(int $id)
|
||
{
|
||
$rules = [
|
||
'qty_change' => 'required|integer',
|
||
'movement_type' => 'required|in_list[' . implode(',', $this->movementTypes) . ']',
|
||
'reason' => 'permit_empty|max_length[120]',
|
||
'note' => 'permit_empty|string',
|
||
'semester' => 'permit_empty|in_list[' . implode(',', $this->semesters) . ']',
|
||
'school_year' => 'permit_empty|max_length[16]',
|
||
'teacher_id' => 'permit_empty|is_natural_no_zero',
|
||
'student_id' => 'permit_empty|is_natural_no_zero',
|
||
'class_section_id' => 'permit_empty|is_natural_no_zero',
|
||
];
|
||
|
||
if (! $this->validate($rules)) {
|
||
return redirect()->back()->withInput()->with('status', 'error')
|
||
->with('message', implode(' ', $this->validator->getErrors()));
|
||
}
|
||
|
||
$existing = $this->db->table('inventory_movements')->where('id', $id)->get()->getRowArray();
|
||
if (!$existing) {
|
||
return redirect()->to(site_url('inventory/movements'))
|
||
->with('status', 'error')->with('message', 'Movement not found.');
|
||
}
|
||
|
||
$movementType = (string) $this->request->getPost('movement_type');
|
||
$qtyChange = $this->normalizeMovementQty($movementType, (int) $this->request->getPost('qty_change'));
|
||
|
||
$itemId = (int) ($existing['item_id'] ?? 0);
|
||
if ($itemId > 0) {
|
||
$delta = $qtyChange - (int) ($existing['qty_change'] ?? 0);
|
||
if ($delta !== 0 && $this->wouldGoNegative($itemId, $delta)) {
|
||
return redirect()->back()->withInput();
|
||
}
|
||
}
|
||
|
||
$semester = trim((string) $this->request->getPost('semester'));
|
||
$schoolYear = trim((string) $this->request->getPost('school_year'));
|
||
if ($semester === '') $semester = (string) $this->semester;
|
||
if ($schoolYear === '') $schoolYear = (string) $this->schoolYear;
|
||
|
||
$data = [
|
||
// item_id is often immutable after posting history; include if you want to allow editing:
|
||
// 'item_id' => (int) $this->request->getPost('item_id'),
|
||
'qty_change' => $qtyChange,
|
||
'movement_type' => $movementType,
|
||
'reason' => trim((string)$this->request->getPost('reason')),
|
||
'note' => trim((string)$this->request->getPost('note')),
|
||
'semester' => $semester ?: null,
|
||
'school_year' => $schoolYear ?: null,
|
||
'teacher_id' => $this->nullableInt($this->request->getPost('teacher_id')),
|
||
'student_id' => $this->nullableInt($this->request->getPost('student_id')),
|
||
'class_section_id' => $this->nullableInt($this->request->getPost('class_section_id')),
|
||
'updated_at' => utc_now(),
|
||
];
|
||
|
||
$ok = $this->db->table('inventory_movements')->where('id', $id)->update($data);
|
||
|
||
if (! $ok) {
|
||
return redirect()->back()->withInput()->with('status', 'error')
|
||
->with('message', 'Could not update movement.');
|
||
}
|
||
|
||
if ($itemId > 0) {
|
||
$this->recalcQuantity($itemId);
|
||
}
|
||
|
||
return redirect()->to(site_url('inventory/movements'))
|
||
->with('status', 'success')
|
||
->with('message', 'Movement updated.');
|
||
}
|
||
|
||
/** =========================
|
||
* DELETE (POST)
|
||
* ========================= */
|
||
public function movementsDelete(int $id)
|
||
{
|
||
// Optional: authorization checks here
|
||
log_message('info', 'Inventory: delete one attempt', ['id' => $id, 'user' => (int)(session('user_id') ?? 0)]);
|
||
$movement = $this->db->table('inventory_movements')->select('id, item_id')->where('id', $id)->get()->getRowArray();
|
||
if (!$movement) {
|
||
return redirect()->back()->with('status', 'error')->with('message', 'Movement not found.');
|
||
}
|
||
|
||
$ok = $this->db->table('inventory_movements')->where('id', $id)->delete();
|
||
log_message('info', 'Inventory: delete one result', ['id' => $id, 'ok' => (bool)$ok, 'affected' => $this->db->affectedRows()]);
|
||
|
||
if (! $ok) {
|
||
return redirect()->back()->with('status', 'error')
|
||
->with('message', 'Could not delete movement.');
|
||
}
|
||
|
||
if (!empty($movement['item_id'])) {
|
||
$this->recalcQuantity((int) $movement['item_id']);
|
||
}
|
||
return redirect()->to(site_url('inventory/movements'))
|
||
->with('status', 'success')->with('message', 'Movement deleted.');
|
||
}
|
||
|
||
/* ===========================
|
||
* Helpers
|
||
* =========================== */
|
||
|
||
/** Default structure for create form */
|
||
private function blankMovement(): array
|
||
{
|
||
return [
|
||
'id' => null,
|
||
'item_id' => null,
|
||
'qty_change' => 0,
|
||
'movement_type' => 'adjust',
|
||
'reason' => '',
|
||
'note' => '',
|
||
'semester' => null,
|
||
'school_year' => null,
|
||
'performed_by' => session('user_id') ?? null,
|
||
'teacher_id' => null,
|
||
'student_id' => null,
|
||
'class_section_id' => null,
|
||
'created_at' => null,
|
||
'updated_at' => null,
|
||
];
|
||
}
|
||
|
||
/** Coerce to nullable int */
|
||
private function nullableInt($val): ?int
|
||
{
|
||
if ($val === null || $val === '' || $val === '0') {
|
||
return null;
|
||
}
|
||
return (int) $val;
|
||
}
|
||
|
||
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) ($this->itemModel->select('quantity')->where('id', $itemId)->first()['quantity'] ?? 0);
|
||
if ($onHand + $delta < 0) {
|
||
session()->setFlashdata('status', 'error');
|
||
session()->setFlashdata('message', 'Not enough stock to apply this movement.');
|
||
return true;
|
||
}
|
||
return false;
|
||
}
|
||
|
||
/** Hydrate display names for a row (used in edit) */
|
||
private function hydrateNames(array $m): array
|
||
{
|
||
// Item
|
||
if (!empty($m['item_id'])) {
|
||
$row = $this->db->table('inventory_items')->select('name')->where('id', $m['item_id'])->get()->getRowArray();
|
||
$m['item_name'] = $row['name'] ?? null;
|
||
}
|
||
|
||
// Performed By (users)
|
||
if (!empty($m['performed_by'])) {
|
||
$row = $this->db->table('users')
|
||
->select("CONCAT(firstname, ' ', lastname) AS fullname")
|
||
->where('id', $m['performed_by'])
|
||
->get()->getRowArray();
|
||
$m['performed_by_name'] = $row['fullname'] ?? null;
|
||
}
|
||
|
||
// Teacher (users)
|
||
if (!empty($m['teacher_id'])) {
|
||
$row = $this->db->table('users')
|
||
->select("CONCAT(firstname, ' ', lastname) AS fullname")
|
||
->where('id', $m['teacher_id'])
|
||
->get()->getRowArray();
|
||
$m['teacher_name'] = $row['fullname'] ?? null;
|
||
}
|
||
|
||
// Student (students)
|
||
if (!empty($m['student_id'])) {
|
||
$row = $this->db->table('students')
|
||
->select("CONCAT(firstname, ' ', lastname) AS fullname")
|
||
->where('id', $m['student_id'])
|
||
->get()->getRowArray();
|
||
$m['student_name'] = $row['fullname'] ?? null;
|
||
}
|
||
|
||
// Class section (by CODE)
|
||
if (!empty($m['class_section_id'])) {
|
||
$row = $this->db->table('classSection')
|
||
->select('class_section_name')
|
||
->where('class_section_id', $m['class_section_id'])
|
||
->get()->getRowArray();
|
||
$m['class_section_name'] = $row['class_section_name'] ?? null;
|
||
}
|
||
|
||
return $m;
|
||
}
|
||
|
||
private function getTeachers(): array
|
||
{
|
||
$rows = $this->teacherModel->getTeachersAndTAs();
|
||
|
||
// Normalize to include a ready-to-use fullname
|
||
return array_map(static function ($r) {
|
||
$fullname = trim(($r['firstname'] ?? '') . ' ' . ($r['lastname'] ?? ''));
|
||
return [
|
||
'id' => (int) $r['id'],
|
||
'firstname' => $r['firstname'] ?? '',
|
||
'lastname' => $r['lastname'] ?? '',
|
||
'fullname' => $fullname,
|
||
'email' => $r['email'] ?? '',
|
||
'cellphone' => $r['cellphone'] ?? '',
|
||
'role' => $r['role'] ?? '',
|
||
];
|
||
}, $rows);
|
||
}
|
||
|
||
|
||
/** Dropdowns */
|
||
private function getItems(): array
|
||
{
|
||
return $this->db->table('inventory_items')->select('id, name')->orderBy('name', 'ASC')->get()->getResultArray();
|
||
}
|
||
private function getClassSections(): array
|
||
{
|
||
return $this->db->table('classSection')
|
||
->select('class_section_id, class_section_name')
|
||
->orderBy('class_section_name', 'ASC')->get()->getResultArray();
|
||
}
|
||
|
||
|
||
private function getStudents(): array
|
||
{
|
||
// Returns: id, fullname
|
||
return $this->db->table('students')
|
||
->select("id, CONCAT(firstname, ' ', lastname) AS fullname", false)
|
||
->orderBy('lastname', 'ASC')
|
||
->orderBy('firstname', 'ASC')
|
||
->get()
|
||
->getResultArray();
|
||
}
|
||
|
||
public function movementsBulkDelete()
|
||
{
|
||
$ids = (array) $this->request->getPost('ids');
|
||
|
||
// sanitize to ints and drop invalids
|
||
$ids = array_values(array_filter(array_map('intval', $ids), fn($v) => $v > 0));
|
||
|
||
if (empty($ids)) {
|
||
log_message('warning', 'Inventory: bulk delete called with empty ids', ['user' => (int)(session('user_id') ?? 0)]);
|
||
return redirect()->back()->with('status','error')->with('message','No movements selected.');
|
||
}
|
||
|
||
log_message('info', 'Inventory: bulk delete attempt', ['count' => count($ids), 'ids' => $ids, 'user' => (int)(session('user_id') ?? 0)]);
|
||
$itemIds = $this->db->table('inventory_movements')
|
||
->select('item_id')
|
||
->whereIn('id', $ids)
|
||
->get()->getResultArray();
|
||
$itemIds = array_values(array_unique(array_filter(array_map(
|
||
static fn($r) => (int) ($r['item_id'] ?? 0),
|
||
$itemIds
|
||
))));
|
||
|
||
$ok = $this->db->table('inventory_movements')->whereIn('id', $ids)->delete();
|
||
log_message('info', 'Inventory: bulk delete result', ['ok' => (bool)$ok, 'affected' => $this->db->affectedRows()]);
|
||
|
||
if (! $ok) {
|
||
return redirect()->back()->with('status','error')->with('message','Bulk delete failed.');
|
||
}
|
||
|
||
foreach ($itemIds as $itemId) {
|
||
$this->recalcQuantity($itemId);
|
||
}
|
||
|
||
return redirect()->to(site_url('inventory/movements'))
|
||
->with('status','success')
|
||
->with('message', count($ids) . ' movement(s) deleted.');
|
||
}
|
||
|
||
}
|