root
2118 lines
85 KiB
PHP
Executable File
2118 lines
85 KiB
PHP
Executable File
<?php
|
|
|
|
namespace App\Controllers\View;
|
|
use App\Controllers\BaseController;
|
|
use App\Models\ReimbursementModel;
|
|
use App\Models\ExpenseModel;
|
|
use App\Models\UserModel;
|
|
use App\Models\ConfigurationModel;
|
|
use App\Models\ReimbursementBatchModel;
|
|
use App\Models\ReimbursementBatchItemModel;
|
|
use App\Models\ReimbursementBatchAdminFileModel;
|
|
use App\Services\EmailService;
|
|
use CodeIgniter\Exceptions\PageNotFoundException;
|
|
use CodeIgniter\HTTP\Files\UploadedFile;
|
|
use ZipArchive;
|
|
|
|
class ReimbursementController extends BaseController
|
|
{
|
|
protected $db;
|
|
protected $configModel;
|
|
protected $semester;
|
|
protected $schoolYear;
|
|
protected $expenseModel;
|
|
protected $reimbModel;
|
|
protected $userModel;
|
|
protected $batchModel;
|
|
protected $batchItemModel;
|
|
protected $batchAdminFileModel;
|
|
|
|
public const SPECIAL_RECIPIENTS = [
|
|
// Reserved virtual recipients that should always be selectable.
|
|
990001 => 'Masjid',
|
|
990002 => 'Donation',
|
|
];
|
|
|
|
public function __construct()
|
|
{
|
|
$this->db = \Config\Database::connect();
|
|
if (!$this->db->connect()) {
|
|
log_message('error', 'Database connection failed.');
|
|
throw new \Exception('Database connection failed.');
|
|
}
|
|
|
|
$this->configModel = new ConfigurationModel();
|
|
$this->expenseModel = new ExpenseModel();
|
|
$this->reimbModel = new ReimbursementModel();
|
|
$this->userModel = new UserModel();
|
|
$this->batchModel = new ReimbursementBatchModel();
|
|
$this->batchItemModel = new ReimbursementBatchItemModel();
|
|
$this->batchAdminFileModel = new ReimbursementBatchAdminFileModel();
|
|
|
|
$this->semester = $this->configModel->getConfig('semester') ?? 'Fall';
|
|
$this->schoolYear = $this->configModel->getConfig('school_year') ?? date('Y');
|
|
}
|
|
|
|
/**
|
|
* Return staff users (admins/teachers/etc.) excluding parents/guests.
|
|
*/
|
|
private function staffUsers(): array
|
|
{
|
|
$rows = $this->userModel
|
|
->select('users.id, users.firstname, users.lastname, roles.name AS role_name')
|
|
->join('user_roles', 'user_roles.user_id = users.id', 'left')
|
|
->join('roles', 'roles.id = user_roles.role_id', 'left')
|
|
->where('roles.name IS NOT NULL', null, false)
|
|
->findAll();
|
|
|
|
$excludedRoles = array_map('strtolower', ['parent', 'student', 'guest']);
|
|
$staff = [];
|
|
|
|
foreach ($rows as $row) {
|
|
$roleName = strtolower((string) ($row['role_name'] ?? ''));
|
|
$id = (int) ($row['id'] ?? 0);
|
|
if ($id <= 0 || in_array($roleName, $excludedRoles, true)) {
|
|
continue;
|
|
}
|
|
if (!isset($staff[$id])) {
|
|
$staff[$id] = [
|
|
'id' => $id,
|
|
'firstname' => $row['firstname'] ?? '',
|
|
'lastname' => $row['lastname'] ?? '',
|
|
];
|
|
}
|
|
}
|
|
|
|
uasort($staff, static function ($a, $b) {
|
|
$nameA = trim(($a['firstname'] ?? '') . ' ' . ($a['lastname'] ?? ''));
|
|
$nameB = trim(($b['firstname'] ?? '') . ' ' . ($b['lastname'] ?? ''));
|
|
return strcasecmp($nameA, $nameB);
|
|
});
|
|
|
|
return array_values($staff);
|
|
}
|
|
|
|
private function fetchRecipientUsers(): array
|
|
{
|
|
return $this->staffUsers();
|
|
}
|
|
|
|
private function appendSpecialRecipients(array $users): array
|
|
{
|
|
$existingIds = array_map(static function ($row) {
|
|
return isset($row['id']) && is_numeric($row['id']) ? (int) $row['id'] : null;
|
|
}, $users);
|
|
|
|
foreach (self::SPECIAL_RECIPIENTS as $id => $label) {
|
|
if (!in_array($id, $existingIds, true)) {
|
|
$users[] = [
|
|
'id' => $id,
|
|
'firstname' => $label,
|
|
'lastname' => '',
|
|
];
|
|
}
|
|
}
|
|
|
|
return $users;
|
|
}
|
|
|
|
private function recipientOptions(): array
|
|
{
|
|
return $this->appendSpecialRecipients($this->fetchRecipientUsers());
|
|
}
|
|
|
|
private function nextYearlyBatchNumberForSchoolYear(?string $schoolYear = null): int
|
|
{
|
|
$year = trim((string) ($schoolYear ?? $this->schoolYear));
|
|
if ($year === '') {
|
|
return 1;
|
|
}
|
|
$record = $this->batchModel
|
|
->select('MAX(yearly_batch_number) AS max_number')
|
|
->where('school_year', $year)
|
|
->first();
|
|
|
|
$max = (int) ($record['max_number'] ?? 0);
|
|
return $max + 1;
|
|
}
|
|
|
|
private function adminRoster(): array
|
|
{
|
|
$admins = $this->staffUsers();
|
|
|
|
if (empty($admins)) {
|
|
$admins = $this->fetchRecipientUsers();
|
|
}
|
|
|
|
return $admins;
|
|
}
|
|
|
|
private function specialRecipientLabel($value): ?string
|
|
{
|
|
if ($value === null || !is_numeric($value)) {
|
|
return null;
|
|
}
|
|
|
|
$id = (int) $value;
|
|
return self::SPECIAL_RECIPIENTS[$id] ?? null;
|
|
}
|
|
|
|
/**
|
|
* Ensure a subdirectory exists under WRITEPATH . 'uploads'.
|
|
* Returns the absolute path to the subdirectory.
|
|
*/
|
|
private function ensureUploadSubdir(string $subdir): string
|
|
{
|
|
$base = rtrim(WRITEPATH, DIRECTORY_SEPARATOR) . DIRECTORY_SEPARATOR . 'uploads';
|
|
$dir = $base . DIRECTORY_SEPARATOR . trim($subdir, '/\\');
|
|
|
|
if (!is_dir($dir)) {
|
|
// Try to create the directory tree if missing
|
|
if (!@mkdir($dir, 0755, true) && !is_dir($dir)) {
|
|
log_message('error', 'Failed to create upload directory: ' . $dir);
|
|
throw new \RuntimeException('Upload directory is not available.');
|
|
}
|
|
}
|
|
if (!is_writable($dir)) {
|
|
log_message('error', 'Upload directory not writable: ' . $dir);
|
|
throw new \RuntimeException('Upload directory is not writable.');
|
|
}
|
|
return $dir;
|
|
}
|
|
|
|
/**
|
|
* Ensure a subdirectory exists under FCPATH . 'uploads'.
|
|
* Returns the absolute path to the subdirectory.
|
|
*/
|
|
/**
|
|
* Save a reimbursement receipt to writable/uploads/reimbursements and
|
|
* return the stored filename (basename). Returns null if no valid file provided.
|
|
*/
|
|
private function saveReimbReceipt($file): ?string
|
|
{
|
|
if (!$file instanceof UploadedFile) {
|
|
return null;
|
|
}
|
|
if (!$file->isValid() || $file->hasMoved()) {
|
|
return null;
|
|
}
|
|
// Skip zero-length inputs (when no file chosen for optional uploads)
|
|
$size = (int) ($file->getSize() ?? 0);
|
|
if ($size <= 0) {
|
|
return null;
|
|
}
|
|
|
|
// Ensure target directory exists and is writable
|
|
$this->ensureUploadSubdir('reimbursements');
|
|
|
|
try {
|
|
// Prefer CI's store() API which places the file under WRITEPATH/uploads/<subdir>
|
|
$stored = $file->store('reimbursements');
|
|
if (!$stored) {
|
|
throw new \RuntimeException('store() returned empty result');
|
|
}
|
|
return basename($stored);
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Reimbursement receipt store() failed: {message}', ['message' => $e->getMessage()]);
|
|
// Fallback to move() with a generated filename
|
|
$ext = $file->getClientExtension() ?: pathinfo($file->getName(), PATHINFO_EXTENSION);
|
|
$ext = $ext ? ('.' . strtolower($ext)) : '';
|
|
$newName = 'reimb_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . $ext;
|
|
$ok = $file->move(WRITEPATH . 'uploads' . DIRECTORY_SEPARATOR . 'reimbursements', $newName, true);
|
|
if (!$ok) {
|
|
log_message('error', 'Failed to move reimbursement receipt to reimbursements directory.');
|
|
throw new \RuntimeException('Unable to save uploaded file.');
|
|
}
|
|
return $newName;
|
|
}
|
|
}
|
|
|
|
private function buildReimbursementQuery($filters = [])
|
|
{
|
|
return $this->expenseModel->getReimbursedExpensesWithDetails($filters);
|
|
}
|
|
|
|
public function underProcessing()
|
|
{
|
|
$pendingQuery = $this->db->table('expenses e');
|
|
$pendingQuery->select('
|
|
e.id AS expense_id,
|
|
e.amount AS expense_amount,
|
|
e.description,
|
|
e.receipt_path AS expense_receipt,
|
|
e.retailor,
|
|
e.purchased_by,
|
|
u.firstname AS purchaser_firstname,
|
|
u.lastname AS purchaser_lastname
|
|
');
|
|
$pendingQuery->join('users u', 'u.id = e.purchased_by', 'left');
|
|
$pendingQuery->join('reimbursement_batch_items bi', 'bi.expense_id = e.id AND bi.unassigned_at IS NULL', 'left');
|
|
$pendingQuery->where('e.reimbursement_id IS NULL', null, false);
|
|
$pendingQuery->where('e.category !=', 'Donation');
|
|
$pendingQuery->groupStart()
|
|
->where('e.status', 'approved')
|
|
->orWhere('e.status', 'Approved')
|
|
->orWhere('e.status', 'APPROVED')
|
|
->groupEnd();
|
|
$pendingQuery->where('bi.id IS NULL', null, false);
|
|
$pendingQuery->orderBy('e.created_at', 'DESC');
|
|
|
|
$pendingRows = $pendingQuery->get()->getResultArray();
|
|
|
|
$pendingItems = [];
|
|
$itemsMap = [];
|
|
|
|
foreach ($pendingRows as $row) {
|
|
$expenseId = (int) ($row['expense_id'] ?? 0);
|
|
if ($expenseId <= 0) {
|
|
continue;
|
|
}
|
|
$receiptUrl = $this->expenseReceiptUrl($row['expense_receipt'] ?? null);
|
|
$fullName = trim(($row['purchaser_firstname'] ?? '') . ' ' . ($row['purchaser_lastname'] ?? '')) ?: 'Unknown';
|
|
|
|
$item = [
|
|
'id' => $expenseId,
|
|
'expense_id' => $expenseId,
|
|
'reimbursement_id' => null,
|
|
'expense_amount' => (float) ($row['expense_amount'] ?? 0),
|
|
'vendor' => (string) ($row['retailor'] ?? ''),
|
|
'description' => trim((string) ($row['description'] ?? '')),
|
|
'purchased_by' => $fullName,
|
|
'receipt_url' => $receiptUrl,
|
|
'batch_id' => null,
|
|
'batch_label' => null,
|
|
'admin_id' => null,
|
|
'admin_name' => null,
|
|
'batch_number' => 0,
|
|
];
|
|
|
|
$pendingItems[] = $item;
|
|
$itemsMap[$expenseId] = $item;
|
|
}
|
|
|
|
$assignedQuery = $this->db->table('reimbursement_batch_items bi');
|
|
$assignedQuery->select('
|
|
bi.id AS batch_item_id,
|
|
bi.batch_id,
|
|
bi.admin_id,
|
|
bi.reimbursement_id,
|
|
bi.expense_id,
|
|
b.title AS batch_title,
|
|
b.status AS batch_status,
|
|
b.yearly_batch_number AS batch_year_number,
|
|
e.amount AS expense_amount,
|
|
e.description,
|
|
e.receipt_path AS expense_receipt,
|
|
e.retailor,
|
|
purchaser.firstname AS purchaser_firstname,
|
|
purchaser.lastname AS purchaser_lastname,
|
|
admin.firstname AS admin_firstname,
|
|
admin.lastname AS admin_lastname
|
|
');
|
|
$assignedQuery->join('reimbursement_batches b', 'b.id = bi.batch_id', 'inner');
|
|
$assignedQuery->join('expenses e', 'e.id = bi.expense_id', 'inner');
|
|
$assignedQuery->join('users purchaser', 'purchaser.id = e.purchased_by', 'left');
|
|
$assignedQuery->join('users admin', 'admin.id = bi.admin_id', 'left');
|
|
$assignedQuery->where('b.status', 'open');
|
|
$assignedQuery->where('bi.unassigned_at IS NULL', null, false);
|
|
$assignedQuery->where('e.reimbursement_id IS NULL', null, false);
|
|
$assignedQuery->where('e.category !=', 'Donation');
|
|
$assignedQuery->groupStart()
|
|
->where('e.status', 'approved')
|
|
->orWhere('e.status', 'Approved')
|
|
->orWhere('e.status', 'APPROVED')
|
|
->groupEnd();
|
|
$assignedQuery->orderBy('b.opened_at', 'ASC');
|
|
$assignedQuery->orderBy('bi.assigned_at', 'ASC');
|
|
|
|
$assignedRows = $assignedQuery->get()->getResultArray();
|
|
|
|
$batchStructures = [];
|
|
|
|
foreach ($assignedRows as $row) {
|
|
$expenseId = (int) ($row['expense_id'] ?? 0);
|
|
$batchId = (int) ($row['batch_id'] ?? 0);
|
|
if ($expenseId <= 0 || $batchId <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$receiptUrl = $this->expenseReceiptUrl($row['expense_receipt'] ?? null);
|
|
$purchaser = trim(($row['purchaser_firstname'] ?? '') . ' ' . ($row['purchaser_lastname'] ?? '')) ?: 'Unknown';
|
|
$yearlyNumber = (int) ($row['batch_year_number'] ?? 0);
|
|
$batchLabel = trim((string) ($row['batch_title'] ?? ''));
|
|
if ($batchLabel === '') {
|
|
$fallbackNumber = $yearlyNumber > 0 ? $yearlyNumber : $batchId;
|
|
$batchLabel = 'Batch #' . $fallbackNumber;
|
|
}
|
|
$adminId = $row['admin_id'] ? (int) $row['admin_id'] : 0;
|
|
$adminName = $adminId > 0
|
|
? (trim(($row['admin_firstname'] ?? '') . ' ' . ($row['admin_lastname'] ?? '')) ?: 'Admin')
|
|
: 'Unassigned';
|
|
$batchStatus = strtolower(trim((string) ($row['batch_status'] ?? ''))) ?: 'open';
|
|
|
|
$item = [
|
|
'id' => $expenseId,
|
|
'expense_id' => $expenseId,
|
|
'reimbursement_id' => $row['reimbursement_id'] ? (int) $row['reimbursement_id'] : null,
|
|
'expense_amount' => (float) ($row['expense_amount'] ?? 0),
|
|
'vendor' => (string) ($row['retailor'] ?? ''),
|
|
'description' => trim((string) ($row['description'] ?? '')),
|
|
'purchased_by' => $purchaser,
|
|
'receipt_url' => $receiptUrl,
|
|
'batch_id' => $batchId,
|
|
'batch_label' => $batchLabel,
|
|
'admin_id' => $adminId ?: null,
|
|
'admin_name' => $adminId > 0 ? $adminName : null,
|
|
'batch_number' => $batchId,
|
|
];
|
|
|
|
$itemsMap[$expenseId] = $item;
|
|
|
|
if (!isset($batchStructures[$batchId])) {
|
|
$batchStructures[$batchId] = [
|
|
'batchId' => $batchId,
|
|
'label' => $batchLabel,
|
|
'slots' => [],
|
|
'status' => $batchStatus,
|
|
'yearly_batch_number'=> $yearlyNumber,
|
|
];
|
|
} else {
|
|
$batchStructures[$batchId]['status'] = $batchStatus;
|
|
if ($yearlyNumber > 0) {
|
|
$batchStructures[$batchId]['yearly_batch_number'] = $yearlyNumber;
|
|
}
|
|
}
|
|
|
|
$slotKey = $adminId ?: 0;
|
|
if (!isset($batchStructures[$batchId]['slots'][$slotKey])) {
|
|
$batchStructures[$batchId]['slots'][$slotKey] = [
|
|
'admin_id' => $adminId ?: 0,
|
|
'admin_name' => $adminId > 0 ? $adminName : 'Unassigned',
|
|
'items' => [],
|
|
'check_file' => null,
|
|
];
|
|
}
|
|
$batchStructures[$batchId]['slots'][$slotKey]['items'][] = $expenseId;
|
|
}
|
|
|
|
$adminFileMap = [];
|
|
if (!empty($batchStructures)) {
|
|
$batchIds = array_keys($batchStructures);
|
|
$files = $this->batchAdminFileModel
|
|
->select('batch_id, admin_id, filename, original_filename')
|
|
->whereIn('batch_id', $batchIds)
|
|
->findAll();
|
|
foreach ($files as $file) {
|
|
$batchId = (int) ($file['batch_id'] ?? 0);
|
|
$adminId = (int) ($file['admin_id'] ?? 0);
|
|
if ($batchId <= 0) {
|
|
continue;
|
|
}
|
|
$adminFileMap[$batchId][$adminId] = [
|
|
'filename' => $file['filename'],
|
|
'original_filename' => $file['original_filename'] ?? null,
|
|
'url' => base_url('reimbursements/batch/admin-file/' . rawurlencode($file['filename']) . '/inline'),
|
|
];
|
|
}
|
|
}
|
|
|
|
foreach ($batchStructures as $batchId => &$batch) {
|
|
foreach ($batch['slots'] as $slotKey => &$slot) {
|
|
$adminId = $slot['admin_id'] ?? 0;
|
|
$slot['check_file'] = $adminFileMap[$batchId][$adminId] ?? null;
|
|
}
|
|
unset($slot);
|
|
}
|
|
unset($batch);
|
|
|
|
$existingBatches = array_map(static function (array $batch) {
|
|
$normalizedSlots = array_map(static function (array $slot) {
|
|
$slot['items'] = array_values(array_map('intval', $slot['items']));
|
|
return $slot;
|
|
}, array_values($batch['slots']));
|
|
|
|
$itemIds = [];
|
|
foreach ($normalizedSlots as $slot) {
|
|
$itemIds = array_merge($itemIds, $slot['items']);
|
|
}
|
|
|
|
return [
|
|
'batchId' => $batch['batchId'],
|
|
'batchNumber' => $batch['batchId'],
|
|
'label' => $batch['label'],
|
|
'sequence' => $batch['yearly_batch_number'] ?? 0,
|
|
'yearly_batch_number'=> $batch['yearly_batch_number'] ?? 0,
|
|
'slots' => $normalizedSlots,
|
|
'itemIds' => array_values(array_unique($itemIds)),
|
|
'status' => $batch['status'] ?? 'open',
|
|
];
|
|
}, array_values($batchStructures));
|
|
|
|
$admins = $this->adminRoster();
|
|
|
|
return view('reimbursements/under_processing', [
|
|
'pendingItems' => $pendingItems,
|
|
'existingBatches' => $existingBatches,
|
|
'adminUsers' => $admins,
|
|
'itemsPayload' => array_values($itemsMap),
|
|
'createBatchUrl' => base_url('reimbursements/batch/create'),
|
|
'checkUploadUrl' => base_url('reimbursements/batch/admin-file/upload'),
|
|
]);
|
|
}
|
|
|
|
public function markDonation()
|
|
{
|
|
if (strtolower($this->request->getMethod()) !== 'post') {
|
|
return $this->response->setStatusCode(405)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Method not allowed',
|
|
]);
|
|
}
|
|
|
|
$expenseId = (int) $this->request->getPost('expense_id');
|
|
if ($expenseId <= 0) {
|
|
return $this->response->setStatusCode(422)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Invalid expense id.',
|
|
]);
|
|
}
|
|
|
|
$expense = $this->expenseModel->find($expenseId);
|
|
if (!$expense) {
|
|
return $this->response->setStatusCode(404)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Expense not found.',
|
|
]);
|
|
}
|
|
|
|
if (!empty($expense['reimbursement_id'])) {
|
|
return $this->response->setStatusCode(409)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Expense is already tied to a reimbursement.',
|
|
]);
|
|
}
|
|
|
|
$userId = (int) (session()->get('user_id') ?? 0);
|
|
$now = date('Y-m-d H:i:s');
|
|
|
|
$this->db->transBegin();
|
|
try {
|
|
$this->batchItemModel
|
|
->where('expense_id', $expenseId)
|
|
->where('unassigned_at IS NULL', null, false)
|
|
->set('unassigned_at', $now)
|
|
->update();
|
|
|
|
$this->expenseModel->update($expenseId, [
|
|
'category' => 'Donation',
|
|
'status' => 'approved',
|
|
'status_reason' => 'Marked as Donation (non-reimbursable).',
|
|
'approved_by' => $userId ?: null,
|
|
'updated_by' => $userId ?: null,
|
|
'reimbursement_id'=> null,
|
|
]);
|
|
} catch (\Throwable $e) {
|
|
$this->db->transRollback();
|
|
log_message('error', 'Failed to mark expense #{expense} as donation: {msg}', [
|
|
'expense' => $expenseId,
|
|
'msg' => $e->getMessage(),
|
|
]);
|
|
return $this->response->setStatusCode(500)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Unable to mark donation right now.',
|
|
]);
|
|
}
|
|
|
|
if (!$this->db->transStatus()) {
|
|
$this->db->transRollback();
|
|
return $this->response->setStatusCode(500)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Unable to mark donation right now.',
|
|
]);
|
|
}
|
|
$this->db->transCommit();
|
|
|
|
$newHash = function_exists('csrf_hash') ? csrf_hash() : null;
|
|
return $this->response
|
|
->setHeader('X-CSRF-HASH', (string) $newHash)
|
|
->setJSON([
|
|
'success' => true,
|
|
'csrf_hash' => $newHash,
|
|
]);
|
|
}
|
|
|
|
public function createBatch()
|
|
{
|
|
if (strtolower($this->request->getMethod()) !== 'post') {
|
|
return $this->response->setStatusCode(405)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Method not allowed',
|
|
]);
|
|
}
|
|
|
|
$title = trim((string) ($this->request->getPost('title') ?? ''));
|
|
$userId = (int) (session()->get('user_id') ?? 0);
|
|
$now = date('Y-m-d H:i:s');
|
|
$sequence = $this->nextYearlyBatchNumberForSchoolYear();
|
|
|
|
$data = [
|
|
'title' => $title !== '' ? $title : null,
|
|
'status' => 'open',
|
|
'created_by' => $userId ?: null,
|
|
'opened_at' => $now,
|
|
'school_year' => $this->schoolYear,
|
|
'semester' => $this->semester,
|
|
'yearly_batch_number' => $sequence,
|
|
];
|
|
|
|
try {
|
|
$this->batchModel->insert($data);
|
|
$batchId = (int) $this->batchModel->getInsertID();
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Failed to create reimbursement batch: {msg}', ['msg' => $e->getMessage()]);
|
|
return $this->response->setStatusCode(500)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Unable to create batch right now.',
|
|
]);
|
|
}
|
|
|
|
if ($batchId <= 0) {
|
|
return $this->response->setStatusCode(500)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Failed to create batch.',
|
|
]);
|
|
}
|
|
|
|
$label = $title !== '' ? $title : 'Batch #' . $sequence;
|
|
if ($title === '') {
|
|
$this->batchModel->update($batchId, ['title' => $label]);
|
|
}
|
|
|
|
$newHash = function_exists('csrf_hash') ? csrf_hash() : null;
|
|
|
|
return $this->response
|
|
->setHeader('X-CSRF-HASH', (string) $newHash)
|
|
->setJSON([
|
|
'success' => true,
|
|
'batch_id' => $batchId,
|
|
'label' => $label,
|
|
'sequence' => $sequence,
|
|
'csrf_hash'=> $newHash,
|
|
]);
|
|
}
|
|
|
|
public function updateBatchAssignment()
|
|
{
|
|
if (strtolower($this->request->getMethod()) !== 'post') {
|
|
return $this->response->setStatusCode(405)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Method not allowed',
|
|
]);
|
|
}
|
|
|
|
$expenseId = (int) $this->request->getPost('expense_id');
|
|
$batchIdRaw = $this->request->getPost('batch_id');
|
|
$batchId = (int) $batchIdRaw;
|
|
$fallbackBatchNumber = $this->request->getPost('batch_number');
|
|
if ($batchId <= 0 && $fallbackBatchNumber !== null && trim((string) $fallbackBatchNumber) !== '') {
|
|
$batchId = (int) $fallbackBatchNumber;
|
|
}
|
|
$adminIdRaw = $this->request->getPost('admin_id');
|
|
$adminId = ($adminIdRaw === '' || $adminIdRaw === null) ? null : (int) $adminIdRaw;
|
|
$reimbursementId = (int) $this->request->getPost('reimbursement_id') ?: null;
|
|
|
|
if ($expenseId <= 0) {
|
|
return $this->response->setStatusCode(422)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Invalid expense id.',
|
|
]);
|
|
}
|
|
|
|
$now = date('Y-m-d H:i:s');
|
|
|
|
$activeItem = $this->batchItemModel
|
|
->where('expense_id', $expenseId)
|
|
->where('unassigned_at IS NULL', null, false)
|
|
->first();
|
|
|
|
if ($batchId <= 0) {
|
|
if ($activeItem) {
|
|
$this->batchItemModel->update((int) $activeItem['id'], ['unassigned_at' => $now]);
|
|
if (!$reimbursementId && !empty($activeItem['reimbursement_id'])) {
|
|
$reimbursementId = (int) $activeItem['reimbursement_id'];
|
|
}
|
|
}
|
|
|
|
$newHash = function_exists('csrf_hash') ? csrf_hash() : null;
|
|
return $this->response
|
|
->setHeader('X-CSRF-HASH', (string) $newHash)
|
|
->setJSON([
|
|
'success' => true,
|
|
'batch_id' => 0,
|
|
'admin_id' => null,
|
|
'reimbursement_id' => $reimbursementId,
|
|
'csrf_hash' => $newHash,
|
|
]);
|
|
}
|
|
|
|
$batch = $this->batchModel->find($batchId);
|
|
if (!$batch || strtolower((string) ($batch['status'] ?? 'open')) !== 'open') {
|
|
return $this->response->setStatusCode(404)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Batch not found or already closed.',
|
|
]);
|
|
}
|
|
|
|
$currentAdmin = $activeItem ? (int) ($activeItem['admin_id'] ?? 0) : null;
|
|
$activeSameBatch = $activeItem && (int) ($activeItem['batch_id'] ?? 0) === $batchId;
|
|
if ($activeSameBatch && ($currentAdmin === ($adminId ?? 0))) {
|
|
$newHash = function_exists('csrf_hash') ? csrf_hash() : null;
|
|
return $this->response
|
|
->setHeader('X-CSRF-HASH', (string) $newHash)
|
|
->setJSON([
|
|
'success' => true,
|
|
'batch_id' => $batchId,
|
|
'admin_id' => $adminId,
|
|
'reimbursement_id' => $activeItem['reimbursement_id'] ?? null,
|
|
'csrf_hash' => $newHash,
|
|
]);
|
|
}
|
|
|
|
if ($activeSameBatch) {
|
|
if (!$reimbursementId) {
|
|
$reimbursementId = $activeItem['reimbursement_id'] ? (int) $activeItem['reimbursement_id'] : $this->lookupReimbursementId($expenseId);
|
|
}
|
|
$updatePayload = [
|
|
'admin_id' => $adminId,
|
|
'assigned_at' => $now,
|
|
'reimbursement_id' => $reimbursementId,
|
|
'unassigned_at' => $adminId === null ? $now : null,
|
|
];
|
|
$this->batchItemModel->update((int) $activeItem['id'], $updatePayload);
|
|
$newHash = function_exists('csrf_hash') ? csrf_hash() : null;
|
|
return $this->response
|
|
->setHeader('X-CSRF-HASH', (string) $newHash)
|
|
->setJSON([
|
|
'success' => true,
|
|
'batch_id' => $batchId,
|
|
'admin_id' => $adminId,
|
|
'reimbursement_id' => $reimbursementId,
|
|
'csrf_hash' => $newHash,
|
|
]);
|
|
}
|
|
|
|
if ($activeItem) {
|
|
$this->batchItemModel->update((int) $activeItem['id'], ['unassigned_at' => $now]);
|
|
if (!$reimbursementId && !empty($activeItem['reimbursement_id'])) {
|
|
$reimbursementId = (int) $activeItem['reimbursement_id'];
|
|
}
|
|
}
|
|
|
|
if (!$reimbursementId) {
|
|
$reimbursementId = $this->lookupReimbursementId($expenseId);
|
|
}
|
|
|
|
$insertData = [
|
|
'batch_id' => $batchId,
|
|
'expense_id' => $expenseId,
|
|
'reimbursement_id' => $reimbursementId,
|
|
'admin_id' => $adminId,
|
|
'assigned_at' => $now,
|
|
'school_year' => $this->schoolYear,
|
|
'semester' => $this->semester,
|
|
];
|
|
|
|
try {
|
|
$this->batchItemModel->insert($insertData);
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Failed to assign expense #{expense} to batch #{batch}: {msg}', [
|
|
'expense' => $expenseId,
|
|
'batch' => $batchId,
|
|
'msg' => $e->getMessage(),
|
|
]);
|
|
return $this->response->setStatusCode(500)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Unable to update batch assignment right now.',
|
|
]);
|
|
}
|
|
|
|
$newHash = function_exists('csrf_hash') ? csrf_hash() : null;
|
|
|
|
return $this->response
|
|
->setHeader('X-CSRF-HASH', (string) $newHash)
|
|
->setJSON([
|
|
'success' => true,
|
|
'batch_id' => $batchId,
|
|
'admin_id' => $adminId,
|
|
'reimbursement_id' => $reimbursementId,
|
|
'csrf_hash' => $newHash,
|
|
]);
|
|
}
|
|
|
|
public function lockBatch()
|
|
{
|
|
if (strtolower($this->request->getMethod()) !== 'post') {
|
|
return $this->response->setStatusCode(405)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Method not allowed',
|
|
]);
|
|
}
|
|
|
|
$batchId = (int) $this->request->getPost('batch_id');
|
|
if ($batchId <= 0) {
|
|
return $this->response->setStatusCode(422)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Invalid batch id.',
|
|
]);
|
|
}
|
|
|
|
$batch = $this->batchModel->find($batchId);
|
|
if (!$batch || strtolower((string) ($batch['status'] ?? '')) !== 'open') {
|
|
return $this->response->setStatusCode(404)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Batch not found or already closed.',
|
|
]);
|
|
}
|
|
|
|
$adminRows = $this->batchItemModel
|
|
->select('DISTINCT COALESCE(admin_id, 0) AS admin_id')
|
|
->where('batch_id', $batchId)
|
|
->where('unassigned_at IS NULL', null, false)
|
|
->get()
|
|
->getResultArray();
|
|
$adminIds = array_values(array_unique(array_map(static fn ($row) => isset($row['admin_id']) ? (int) $row['admin_id'] : 0, $adminRows)));
|
|
$requiredAdmins = array_filter($adminIds, static fn ($adminId) => $adminId > 0);
|
|
if (!empty($requiredAdmins)) {
|
|
$uploadedFiles = $this->batchAdminFileModel
|
|
->select('admin_id')
|
|
->where('batch_id', $batchId)
|
|
->whereIn('admin_id', $requiredAdmins)
|
|
->findAll();
|
|
$uploadedAdminIds = array_values(array_unique(array_map(static fn ($file) => isset($file['admin_id']) ? (int) $file['admin_id'] : 0, $uploadedFiles)));
|
|
foreach ($requiredAdmins as $adminId) {
|
|
if (!in_array($adminId, $uploadedAdminIds, true)) {
|
|
return $this->response->setStatusCode(409)->setJSON([
|
|
'success' => false,
|
|
'error' => 'All admin sections must have a check file before submitting the batch.',
|
|
]);
|
|
}
|
|
}
|
|
}
|
|
|
|
$this->db->transBegin();
|
|
|
|
// Create reimbursement records for any batch items that don't yet have one
|
|
$items = $this->db->table('reimbursement_batch_items bi')
|
|
->select('bi.id AS batch_item_id, bi.reimbursement_id AS batch_reimb_id, bi.expense_id, e.amount, e.purchased_by, e.description, e.reimbursement_id AS expense_reimb_id, e.school_year AS expense_school_year, e.semester AS expense_semester')
|
|
->join('expenses e', 'e.id = bi.expense_id', 'inner')
|
|
->where('bi.batch_id', $batchId)
|
|
->where('bi.unassigned_at IS NULL', null, false)
|
|
->get()
|
|
->getResultArray();
|
|
|
|
$now = date('Y-m-d H:i:s');
|
|
$userId = (int) (session()->get('user_id') ?? 0);
|
|
|
|
try {
|
|
foreach ($items as $item) {
|
|
$expenseId = (int) ($item['expense_id'] ?? 0);
|
|
$recipientId = (int) ($item['purchased_by'] ?? 0);
|
|
if ($expenseId <= 0 || $recipientId <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$reimbId = $item['batch_reimb_id'] ?: ($item['expense_reimb_id'] ?: $this->lookupReimbursementId($expenseId));
|
|
if (!$reimbId) {
|
|
$payload = [
|
|
'expense_id' => $expenseId,
|
|
'amount' => (float) ($item['amount'] ?? 0),
|
|
'reimbursed_to' => $recipientId,
|
|
'approved_by' => $userId ?: null,
|
|
'description' => trim((string) ($item['description'] ?? '')),
|
|
'status' => 'Paid',
|
|
'added_by' => $userId ?: null,
|
|
'school_year' => $item['expense_school_year'] ?: $this->schoolYear,
|
|
'semester' => $item['expense_semester'] ?: $this->semester,
|
|
'reimbursement_method' => 'Check',
|
|
'batch_number' => $batchId,
|
|
'created_at' => $now,
|
|
'updated_at' => $now,
|
|
];
|
|
$reimbId = $this->reimbModel->insert($payload);
|
|
}
|
|
|
|
if ($reimbId) {
|
|
$this->reimbModel->update($reimbId, [
|
|
'batch_number' => $batchId,
|
|
'approved_by' => $userId ?: null,
|
|
'status' => 'Paid',
|
|
]);
|
|
$this->expenseModel->update($expenseId, ['reimbursement_id' => $reimbId]);
|
|
if (!empty($item['batch_item_id'])) {
|
|
$this->batchItemModel->update((int) $item['batch_item_id'], ['reimbursement_id' => $reimbId]);
|
|
}
|
|
}
|
|
}
|
|
|
|
$update = [
|
|
'status' => 'closed',
|
|
'closed_at' => $now,
|
|
];
|
|
|
|
if ($userId > 0) {
|
|
$update['closed_by'] = $userId;
|
|
}
|
|
|
|
$this->batchModel->update($batchId, $update);
|
|
} catch (\Throwable $e) {
|
|
$this->db->transRollback();
|
|
log_message('error', 'Failed to lock reimbursement batch #{batch}: {msg}', [
|
|
'batch' => $batchId,
|
|
'msg' => $e->getMessage(),
|
|
]);
|
|
return $this->response->setStatusCode(500)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Unable to lock batch right now.',
|
|
]);
|
|
}
|
|
|
|
if (!$this->db->transStatus()) {
|
|
$this->db->transRollback();
|
|
return $this->response->setStatusCode(500)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Unable to lock batch right now.',
|
|
]);
|
|
}
|
|
$this->db->transCommit();
|
|
|
|
$newHash = function_exists('csrf_hash') ? csrf_hash() : null;
|
|
return $this->response
|
|
->setHeader('X-CSRF-HASH', (string) $newHash)
|
|
->setJSON([
|
|
'success' => true,
|
|
'batch_id' => $batchId,
|
|
'status' => 'closed',
|
|
'csrf_hash'=> $newHash,
|
|
]);
|
|
}
|
|
|
|
public function uploadBatchAdminFile()
|
|
{
|
|
if (strtolower($this->request->getMethod()) !== 'post') {
|
|
return $this->response->setStatusCode(405)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Method not allowed',
|
|
]);
|
|
}
|
|
|
|
$batchId = (int) $this->request->getPost('batch_id');
|
|
$adminId = (int) $this->request->getPost('admin_id');
|
|
$file = $this->request->getFile('check_file');
|
|
|
|
if ($batchId <= 0 || $adminId < 0) {
|
|
return $this->response->setStatusCode(422)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Invalid batch or admin specified.',
|
|
]);
|
|
}
|
|
|
|
if (!$file instanceof UploadedFile || !$file->isValid()) {
|
|
return $this->response->setStatusCode(422)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Please provide a valid file.',
|
|
]);
|
|
}
|
|
|
|
$batch = $this->batchModel->find($batchId);
|
|
if (!$batch) {
|
|
return $this->response->setStatusCode(404)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Batch not found.',
|
|
]);
|
|
}
|
|
if (strtolower((string) ($batch['status'] ?? '')) !== 'open') {
|
|
return $this->response->setStatusCode(409)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Cannot upload files for a locked batch.',
|
|
]);
|
|
}
|
|
|
|
$uploadDir = $this->ensureUploadSubdir('reimbursements');
|
|
$newName = $file->getRandomName();
|
|
try {
|
|
$file->move($uploadDir, $newName);
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Failed to save batch check file: {msg}', ['msg' => $e->getMessage()]);
|
|
return $this->response->setStatusCode(500)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Unable to store the uploaded file.',
|
|
]);
|
|
}
|
|
|
|
$existing = $this->batchAdminFileModel
|
|
->where('batch_id', $batchId)
|
|
->where('admin_id', $adminId)
|
|
->first();
|
|
if ($existing && !empty($existing['filename'])) {
|
|
$oldFile = $uploadDir . DIRECTORY_SEPARATOR . $existing['filename'];
|
|
if (is_file($oldFile)) {
|
|
@unlink($oldFile);
|
|
}
|
|
}
|
|
|
|
$now = date('Y-m-d H:i:s');
|
|
$userId = (int) (session()->get('user_id') ?? 0);
|
|
$payload = [
|
|
'batch_id' => $batchId,
|
|
'admin_id' => $adminId,
|
|
'filename' => $newName,
|
|
'original_filename'=> $file->getClientName(),
|
|
'uploaded_at' => $now,
|
|
'uploaded_by' => $userId ?: null,
|
|
];
|
|
|
|
if ($existing) {
|
|
$this->batchAdminFileModel->update((int) $existing['id'], $payload);
|
|
} else {
|
|
$this->batchAdminFileModel->insert($payload);
|
|
}
|
|
|
|
$newHash = function_exists('csrf_hash') ? csrf_hash() : null;
|
|
return $this->response
|
|
->setHeader('X-CSRF-HASH', (string) $newHash)
|
|
->setJSON([
|
|
'success' => true,
|
|
'batch_id' => $batchId,
|
|
'admin_id' => $adminId,
|
|
'filename' => $newName,
|
|
'original_filename' => $file->getClientName(),
|
|
'url' => base_url('reimbursements/batch/admin-file/' . rawurlencode($newName) . '/inline'),
|
|
'csrf_hash' => $newHash,
|
|
]);
|
|
}
|
|
|
|
public function serveAdminCheckFile(string $filename, string $mode = 'inline')
|
|
{
|
|
$safeName = basename(trim($filename));
|
|
if ($safeName === '') {
|
|
throw new PageNotFoundException('File not specified.');
|
|
}
|
|
|
|
$path = $this->ensureUploadSubdir('reimbursements') . DIRECTORY_SEPARATOR . $safeName;
|
|
if (!is_file($path)) {
|
|
throw new PageNotFoundException('File not found.');
|
|
}
|
|
|
|
$mime = mime_content_type($path) ?: 'application/octet-stream';
|
|
$disposition = strtolower(trim((string) $mode)) === 'download' ? 'attachment' : 'inline';
|
|
|
|
// Use CI's download helper to ensure correct headers/output; override to allow inline display
|
|
return $this->response
|
|
->download($path, null)
|
|
->setFileName($safeName)
|
|
->setHeader('Content-Type', $mime)
|
|
->setHeader('Content-Disposition', sprintf('%s; filename="%s"', $disposition, $safeName));
|
|
}
|
|
|
|
private function expenseReceiptUrl(?string $filename): ?string
|
|
{
|
|
if (!$filename) return null;
|
|
$safe = basename(trim($filename));
|
|
return $safe !== '' ? site_url('receipts/' . $safe) : null;
|
|
}
|
|
|
|
private function reimbReceiptUrl(?string $filename): ?string
|
|
{
|
|
if (!$filename) return null;
|
|
$safe = basename(trim($filename));
|
|
return $safe !== '' ? site_url('reimbreceipts/' . $safe) : null;
|
|
}
|
|
|
|
private function sanitizeAttachmentComponent(string $value): string
|
|
{
|
|
$clean = preg_replace('/[^A-Za-z0-9]+/', '_', trim((string) $value));
|
|
$clean = trim($clean, '_');
|
|
return $clean === '' ? 'unknown' : $clean;
|
|
}
|
|
|
|
private function formatAttachmentDate(?string $value): string
|
|
{
|
|
if (!$value) {
|
|
return 'unknown';
|
|
}
|
|
$timestamp = strtotime($value);
|
|
if ($timestamp === false) {
|
|
return 'unknown';
|
|
}
|
|
return date('Ymd', $timestamp);
|
|
}
|
|
|
|
private function uniqueZipEntryName(string $base, string $extension, array &$used): string
|
|
{
|
|
$candidate = $base . $extension;
|
|
$counter = 1;
|
|
while (isset($used[$candidate])) {
|
|
$candidate = $base . '_' . $counter . $extension;
|
|
$counter++;
|
|
}
|
|
$used[$candidate] = true;
|
|
return $candidate;
|
|
}
|
|
|
|
private function formatCsvDate(?string $value): string
|
|
{
|
|
if (!$value) {
|
|
return '';
|
|
}
|
|
$timestamp = strtotime($value);
|
|
if ($timestamp === false) {
|
|
return '';
|
|
}
|
|
return date('m-d-Y', $timestamp);
|
|
}
|
|
|
|
private function fetchBatchReceiptRows(int $batchId, array $receiptIds = []): array
|
|
{
|
|
$builder = $this->db->table('reimbursement_batch_items bi');
|
|
$builder->select('e.id AS expense_id, e.retailor, e.amount, e.description, e.receipt_path AS receipt_filename, e.date_of_purchase AS purchase_date, u.firstname AS purchaser_firstname, u.lastname AS purchaser_lastname');
|
|
$builder->join('expenses e', 'e.id = bi.expense_id', 'inner');
|
|
$builder->join('users u', 'u.id = e.purchased_by', 'left');
|
|
$builder->where('bi.batch_id', $batchId);
|
|
if (!empty($receiptIds)) {
|
|
$builder->whereIn('e.id', $receiptIds);
|
|
}
|
|
$rows = $builder->get()->getResultArray();
|
|
return array_values(array_filter($rows, static function ($row) {
|
|
return !empty($row['receipt_filename']);
|
|
}));
|
|
}
|
|
|
|
private function writeBatchCsvMetadata($handle, string $batchLabel, array $batch): void
|
|
{
|
|
$closedAtRaw = $batch['closed_at'] ?? null;
|
|
fputcsv($handle, ['Batch Title', $batchLabel]);
|
|
fputcsv($handle, ['School Year', $batch['school_year'] ?? '', 'Semester', $batch['semester'] ?? '', 'Closed At', $this->formatCsvDate($closedAtRaw)]);
|
|
fputcsv($handle, []);
|
|
fputcsv($handle, ['Amount', 'Credit', 'Paid', 'Balance', 'Date', 'By Who', 'Vendor', 'Description']);
|
|
}
|
|
|
|
private function writeBatchCsvSummary($handle, array $receiptRows, string $totalsDate = ''): void
|
|
{
|
|
$receiptTotalsByRecipient = [];
|
|
foreach ($receiptRows as $row) {
|
|
$recipientName = trim(($row['purchaser_firstname'] ?? '') . ' ' . ($row['purchaser_lastname'] ?? ''));
|
|
if ($recipientName === '') {
|
|
$recipientName = 'Unknown';
|
|
}
|
|
$vendor = trim((string) ($row['retailor'] ?? ''));
|
|
if ($vendor === '') {
|
|
$vendor = 'Vendor N/A';
|
|
}
|
|
$dateText = $this->formatCsvDate($row['purchase_date'] ?? null);
|
|
$descriptionValue = trim((string) ($row['description'] ?? ''));
|
|
$descriptionParts = $descriptionValue !== '' ? [$descriptionValue] : ['Receipt'];
|
|
$amountValue = (float) ($row['amount'] ?? 0);
|
|
$receiptTotalsByRecipient[$recipientName] = ($receiptTotalsByRecipient[$recipientName] ?? 0.0) + $amountValue;
|
|
fputcsv($handle, [
|
|
number_format($amountValue, 2, '.', ''),
|
|
'',
|
|
number_format($amountValue, 2, '.', ''),
|
|
'0.00',
|
|
$dateText,
|
|
$recipientName,
|
|
$vendor,
|
|
implode(' | ', $descriptionParts),
|
|
]);
|
|
}
|
|
if (!empty($receiptTotalsByRecipient)) {
|
|
fputcsv($handle, []);
|
|
fputcsv($handle, ['Totals by Recipient', '', '', '', '', '', '', '']);
|
|
foreach ($receiptTotalsByRecipient as $recipient => $total) {
|
|
$formatted = number_format($total, 2, '.', '');
|
|
fputcsv($handle, [
|
|
$formatted,
|
|
'',
|
|
$formatted,
|
|
'0.00',
|
|
$totalsDate,
|
|
$recipient,
|
|
'',
|
|
'',
|
|
]);
|
|
}
|
|
}
|
|
}
|
|
|
|
private function parseAttachmentIds($raw): array
|
|
{
|
|
if (is_array($raw)) {
|
|
$decoded = $raw;
|
|
} else {
|
|
$decoded = json_decode((string) $raw, true);
|
|
}
|
|
if (!is_array($decoded)) {
|
|
return [];
|
|
}
|
|
$ids = [];
|
|
foreach ($decoded as $entry) {
|
|
$id = (int) ($entry ?? 0);
|
|
if ($id > 0) {
|
|
$ids[$id] = $id;
|
|
}
|
|
}
|
|
return array_values($ids);
|
|
}
|
|
|
|
public function index()
|
|
{
|
|
$semester = $this->request->getGet('semester') ?: null;
|
|
$status = $this->request->getGet('status') ?: null;
|
|
$filterYear = $this->request->getGet('school_year') ?: $this->schoolYear;
|
|
$userId = $this->request->getGet('user_id') ?: null;
|
|
|
|
$filters = [
|
|
'school_year' => $filterYear,
|
|
'semester' => $semester,
|
|
'status' => $status, // e.g., 'Paid'
|
|
'user_id' => $userId,
|
|
];
|
|
|
|
$expenses = $this->expenseModel
|
|
->getReimbursedExpensesWithDetails($filters)
|
|
->get()
|
|
->getResultArray();
|
|
|
|
// Build closed batch summaries/details (all closed batches)
|
|
$batchSummaries = [];
|
|
$batchDetails = [];
|
|
$batchQuery = $this->db->table('reimbursement_batches b')
|
|
->select("
|
|
b.id AS batch_id,
|
|
b.title AS batch_title,
|
|
b.yearly_batch_number,
|
|
b.closed_at,
|
|
b.school_year AS batch_school_year,
|
|
e.id AS expense_id,
|
|
e.amount AS expense_amount,
|
|
e.description,
|
|
e.retailor,
|
|
e.receipt_path AS expense_receipt,
|
|
e.date_of_purchase AS expense_date_of_purchase,
|
|
e.purchased_by,
|
|
u.firstname AS purchaser_firstname,
|
|
u.lastname AS purchaser_lastname,
|
|
r.amount AS reimb_amount
|
|
")
|
|
->join('reimbursement_batch_items bi', 'bi.batch_id = b.id', 'inner')
|
|
->join('expenses e', 'e.id = bi.expense_id', 'inner')
|
|
->join('users u', 'u.id = e.purchased_by', 'left')
|
|
->join('reimbursements r', 'r.expense_id = e.id', 'left')
|
|
->where('b.status', 'closed')
|
|
->where('bi.unassigned_at IS NULL', null, false);
|
|
|
|
if (!empty($filterYear)) {
|
|
$batchQuery->groupStart()
|
|
->where('b.school_year', $filterYear)
|
|
->orWhere('e.school_year', $filterYear)
|
|
->groupEnd();
|
|
}
|
|
if (!empty($userId)) {
|
|
$batchQuery->where('e.purchased_by', $userId);
|
|
}
|
|
|
|
$batchRows = $batchQuery->get()->getResultArray();
|
|
$batchAttachments = [];
|
|
foreach ($batchRows as $row) {
|
|
$bid = (int) ($row['batch_id'] ?? 0);
|
|
if ($bid <= 0) {
|
|
continue;
|
|
}
|
|
$labelNumber = (int) ($row['yearly_batch_number'] ?? $bid);
|
|
if (!isset($batchSummaries[$bid])) {
|
|
$batchSummaries[$bid] = [
|
|
'batch_id' => $bid,
|
|
'title' => trim((string) ($row['batch_title'] ?? '')) ?: ('Batch #' . $labelNumber),
|
|
'sequence' => $labelNumber,
|
|
'closed_at' => $row['closed_at'] ?? null,
|
|
'amount' => 0.0,
|
|
'items' => 0,
|
|
];
|
|
}
|
|
$amount = $row['reimb_amount'] ?? $row['expense_amount'] ?? 0;
|
|
$batchSummaries[$bid]['amount'] += (float) $amount;
|
|
$batchSummaries[$bid]['items'] += 1;
|
|
|
|
if (!isset($batchDetails[$bid])) {
|
|
$batchDetails[$bid] = [
|
|
'title' => $batchSummaries[$bid]['title'],
|
|
'sequence' => $labelNumber,
|
|
'closed_at' => $batchSummaries[$bid]['closed_at'],
|
|
'items' => [],
|
|
'checks' => [],
|
|
];
|
|
}
|
|
if (!isset($batchAttachments[$bid])) {
|
|
$batchAttachments[$bid] = [
|
|
'receipts' => [],
|
|
'checks' => [],
|
|
];
|
|
}
|
|
$batchDetails[$bid]['items'][] = [
|
|
'amount' => (float) $amount,
|
|
'description' => $row['description'] ?? '',
|
|
'vendor' => $row['retailor'] ?? '',
|
|
'purchased_by' => trim(($row['purchaser_firstname'] ?? '') . ' ' . ($row['purchaser_lastname'] ?? '')),
|
|
'receipt_url' => $this->expenseReceiptUrl($row['expense_receipt'] ?? null),
|
|
];
|
|
$receiptFilename = trim((string) ($row['expense_receipt'] ?? ''));
|
|
if ($receiptFilename !== '') {
|
|
$batchAttachments[$bid]['receipts'][] = [
|
|
'expense_id' => (int) ($row['expense_id'] ?? 0),
|
|
'receipt_filename' => $receiptFilename,
|
|
'receipt_url' => $this->expenseReceiptUrl($receiptFilename),
|
|
'purchaser_firstname' => $row['purchaser_firstname'] ?? '',
|
|
'purchaser_lastname' => $row['purchaser_lastname'] ?? '',
|
|
'vendor' => $row['retailor'] ?? '',
|
|
'description' => $row['description'] ?? '',
|
|
'amount' => (float) ($row['expense_amount'] ?? 0),
|
|
'date_of_purchase' => $row['expense_date_of_purchase'] ?? '',
|
|
];
|
|
}
|
|
}
|
|
|
|
// Attach uploaded admin check files for all closed batches
|
|
if (!empty($batchSummaries)) {
|
|
$batchIds = array_keys($batchSummaries);
|
|
$files = $this->batchAdminFileModel
|
|
->select('id AS file_id, batch_id, admin_id, filename, original_filename, uploaded_at')
|
|
->whereIn('batch_id', $batchIds)
|
|
->findAll();
|
|
$adminNames = $this->adminRoster();
|
|
$adminNameMap = [];
|
|
foreach ($adminNames as $admin) {
|
|
$adminNameMap[(int) $admin['id']] = trim(($admin['firstname'] ?? '') . ' ' . ($admin['lastname'] ?? ''));
|
|
}
|
|
foreach ($files as $file) {
|
|
$bid = (int) ($file['batch_id'] ?? 0);
|
|
if ($bid <= 0 || !isset($batchDetails[$bid])) {
|
|
continue;
|
|
}
|
|
$aid = (int) ($file['admin_id'] ?? 0);
|
|
$batchDetails[$bid]['checks'][] = [
|
|
'admin' => $adminNameMap[$aid] ?? ($aid > 0 ? 'Admin #' . $aid : 'Unassigned'),
|
|
'filename' => $file['filename'],
|
|
'original' => $file['original_filename'] ?? $file['filename'],
|
|
'url' => base_url('reimbursements/batch/admin-file/' . rawurlencode($file['filename']) . '/inline'),
|
|
];
|
|
if (!isset($batchAttachments[$bid])) {
|
|
$batchAttachments[$bid] = [
|
|
'receipts' => [],
|
|
'checks' => [],
|
|
];
|
|
}
|
|
$batchAttachments[$bid]['checks'][] = [
|
|
'file_id' => (int) ($file['file_id'] ?? 0),
|
|
'filename' => $file['filename'],
|
|
'original_filename' => $file['original_filename'] ?? $file['filename'],
|
|
'admin' => $adminNameMap[$aid] ?? ($aid > 0 ? 'Admin #' . $aid : 'Unassigned'),
|
|
'url' => base_url('reimbursements/batch/admin-file/' . rawurlencode($file['filename']) . '/inline'),
|
|
'uploaded_at' => $file['uploaded_at'] ?? null,
|
|
];
|
|
}
|
|
}
|
|
|
|
if (!empty($batchSummaries)) {
|
|
$batchSummaries = array_values($batchSummaries);
|
|
usort($batchSummaries, static function ($a, $b) {
|
|
$aSeq = (int) ($a['sequence'] ?? 0);
|
|
$bSeq = (int) ($b['sequence'] ?? 0);
|
|
return $bSeq <=> $aSeq;
|
|
});
|
|
}
|
|
|
|
$donationBatch = null;
|
|
$donationDetails = [
|
|
'items' => [],
|
|
'checks' => [],
|
|
];
|
|
$donationQuery = $this->db->table('expenses e')
|
|
->select('
|
|
e.id AS expense_id,
|
|
e.amount AS expense_amount,
|
|
e.description,
|
|
e.retailor,
|
|
e.receipt_path AS expense_receipt,
|
|
e.purchased_by,
|
|
e.school_year,
|
|
e.semester,
|
|
e.status,
|
|
u.firstname AS purchaser_firstname,
|
|
u.lastname AS purchaser_lastname
|
|
')
|
|
->join('users u', 'u.id = e.purchased_by', 'left')
|
|
->where('e.category', 'Donation');
|
|
|
|
if (!empty($filterYear)) {
|
|
$donationQuery->where('e.school_year', $filterYear);
|
|
}
|
|
if (!empty($semester)) {
|
|
$donationQuery->where('e.semester', $semester);
|
|
}
|
|
if (!empty($userId)) {
|
|
$donationQuery->where('e.purchased_by', $userId);
|
|
}
|
|
if (!empty($status)) {
|
|
$donationQuery->groupStart()
|
|
->where('e.status', $status)
|
|
->orWhere('e.status', strtolower($status))
|
|
->orWhere('e.status', strtoupper($status))
|
|
->groupEnd();
|
|
}
|
|
|
|
$donationRows = $donationQuery->orderBy('e.created_at', 'DESC')->get()->getResultArray();
|
|
if (!empty($donationRows)) {
|
|
$donationTotal = 0.0;
|
|
foreach ($donationRows as $row) {
|
|
$amount = (float) ($row['expense_amount'] ?? 0);
|
|
$donationTotal += $amount;
|
|
$purchaser = trim(($row['purchaser_firstname'] ?? '') . ' ' . ($row['purchaser_lastname'] ?? ''));
|
|
if ($purchaser === '') {
|
|
$purchaser = 'Unknown';
|
|
}
|
|
$donationDetails['items'][] = [
|
|
'amount' => $amount,
|
|
'description' => $row['description'] ?? '',
|
|
'vendor' => $row['retailor'] ?? '',
|
|
'purchased_by' => $purchaser,
|
|
'receipt_url' => $this->expenseReceiptUrl($row['expense_receipt'] ?? null),
|
|
];
|
|
}
|
|
|
|
$donationBatch = [
|
|
'title' => 'Donations',
|
|
'items' => count($donationDetails['items']),
|
|
'amount' => $donationTotal,
|
|
];
|
|
}
|
|
|
|
foreach ($expenses as &$e) {
|
|
if (array_key_exists('expense_receipt', $e)) {
|
|
$e['expense_receipt_url'] = $this->expenseReceiptUrl($e['expense_receipt']);
|
|
} elseif (array_key_exists('receipt_path', $e)) {
|
|
$e['expense_receipt_url'] = $this->expenseReceiptUrl($e['receipt_path']);
|
|
}
|
|
|
|
if (array_key_exists('reimb_receipt', $e)) {
|
|
$e['reimb_receipt_url'] = $this->reimbReceiptUrl($e['reimb_receipt']);
|
|
} elseif (array_key_exists('receipt_path_reimb', $e)) {
|
|
$e['reimb_receipt_url'] = $this->reimbReceiptUrl($e['receipt_path_reimb']);
|
|
}
|
|
|
|
$recipientId = $e['reimb_recipient_id'] ?? ($e['reimbursed_to'] ?? null);
|
|
$hasName = trim(($e['reimb_firstname'] ?? '') . ' ' . ($e['reimb_lastname'] ?? '')) !== '';
|
|
if (!$hasName) {
|
|
$label = $this->specialRecipientLabel($recipientId);
|
|
if ($label !== null) {
|
|
$e['reimb_firstname'] = $label;
|
|
$e['reimb_lastname'] = '';
|
|
}
|
|
}
|
|
}
|
|
unset($e);
|
|
|
|
$users = $this->recipientOptions();
|
|
|
|
$years = $this->db->table('reimbursements')
|
|
->select('school_year')
|
|
->distinct()
|
|
->orderBy('school_year', 'DESC')
|
|
->get()
|
|
->getResultArray();
|
|
$schoolYears = array_column($years, 'school_year');
|
|
|
|
// Add school years from fallback expenses (closed batches without reimbursements)
|
|
if (!empty($fallbackRows)) {
|
|
$fallbackYears = array_values(array_unique(array_filter(array_column($fallbackRows, 'school_year'))));
|
|
$schoolYears = array_values(array_unique(array_merge($schoolYears, $fallbackYears)));
|
|
rsort($schoolYears);
|
|
}
|
|
|
|
return view('reimbursements/index', [
|
|
'expenses' => $expenses,
|
|
'users' => $users,
|
|
'schoolYears' => $schoolYears,
|
|
'batchSummaries' => $batchSummaries,
|
|
'batchDetails' => $batchDetails,
|
|
'batchAttachments' => $batchAttachments,
|
|
'donationBatch' => $donationBatch,
|
|
'donationDetails' => $donationDetails,
|
|
]);
|
|
}
|
|
|
|
public function sendBatchEmail()
|
|
{
|
|
$batchId = (int) ($this->request->getPost('batch_id') ?? 0);
|
|
if ($batchId <= 0) {
|
|
return $this->response->setStatusCode(400)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Batch not specified.',
|
|
]);
|
|
}
|
|
|
|
$recipientEmail = trim((string) ($this->request->getPost('recipient_email') ?? ''));
|
|
if ($recipientEmail === '' || !filter_var($recipientEmail, FILTER_VALIDATE_EMAIL)) {
|
|
return $this->response->setStatusCode(422)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Please provide a valid recipient email address.',
|
|
]);
|
|
}
|
|
|
|
$message = trim((string) ($this->request->getPost('message') ?? ''));
|
|
$receiptIds = $this->parseAttachmentIds($this->request->getPost('receipts'));
|
|
$checkIds = $this->parseAttachmentIds($this->request->getPost('checks'));
|
|
|
|
$batch = $this->batchModel->find($batchId);
|
|
if (!$batch) {
|
|
return $this->response->setStatusCode(404)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Requested batch was not found.',
|
|
]);
|
|
}
|
|
|
|
$receiptRows = !empty($receiptIds) ? $this->fetchBatchReceiptRows($batchId, $receiptIds) : [];
|
|
|
|
$checkRows = [];
|
|
if (!empty($checkIds)) {
|
|
$checkRows = $this->batchAdminFileModel
|
|
->select('id AS file_id, admin_id, filename, original_filename, uploaded_at')
|
|
->where('batch_id', $batchId)
|
|
->whereIn('id', $checkIds)
|
|
->findAll();
|
|
$adminLookup = [];
|
|
foreach ($this->adminRoster() as $admin) {
|
|
$adminLookup[(int) ($admin['id'] ?? 0)] = trim((string) (($admin['firstname'] ?? '') . ' ' . ($admin['lastname'] ?? '')));
|
|
}
|
|
foreach ($checkRows as &$row) {
|
|
$aid = (int) ($row['admin_id'] ?? 0);
|
|
$row['admin'] = $adminLookup[$aid] ?? ($aid > 0 ? 'Admin #' . $aid : 'Admin');
|
|
}
|
|
unset($row);
|
|
}
|
|
|
|
$tempDir = WRITEPATH . 'tmp';
|
|
if (!is_dir($tempDir)) {
|
|
mkdir($tempDir, 0755, true);
|
|
}
|
|
$cleanupPaths = [];
|
|
|
|
$batchLabel = trim((string) ($batch['title'] ?? '')) ?: ('Batch #' . ((int) ($batch['yearly_batch_number'] ?? $batchId)));
|
|
$batchFileLabel = $this->sanitizeAttachmentComponent($batchLabel);
|
|
|
|
$csvPath = tempnam($tempDir, 'batch_csv_');
|
|
$cleanupPaths[] = $csvPath;
|
|
$csvHandle = fopen($csvPath, 'w');
|
|
if (!$csvHandle) {
|
|
if (is_file($csvPath)) {
|
|
@unlink($csvPath);
|
|
}
|
|
return $this->response->setStatusCode(500)->setJSON([
|
|
'success' => false,
|
|
'error' => 'Unable to create the summary file.',
|
|
]);
|
|
}
|
|
$batchClosedDate = $this->formatCsvDate($batch['closed_at'] ?? null);
|
|
$this->writeBatchCsvMetadata($csvHandle, $batchLabel, $batch);
|
|
$this->writeBatchCsvSummary($csvHandle, $receiptRows, $batchClosedDate);
|
|
fclose($csvHandle);
|
|
|
|
$csvAttachmentName = $batchFileLabel . '_summary.csv';
|
|
|
|
$receiptsArchivePath = null;
|
|
$receiptZipCreated = false;
|
|
if (!empty($receiptRows)) {
|
|
$archivePath = $tempDir . '/batch_receipts_' . $batchId . '_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . '.zip';
|
|
$zip = new ZipArchive();
|
|
$added = 0;
|
|
$usedNames = [];
|
|
if ($zip->open($archivePath, ZipArchive::CREATE | ZipArchive::OVERWRITE) === true) {
|
|
foreach ($receiptRows as $row) {
|
|
$sourcePath = WRITEPATH . 'uploads/receipts/' . basename((string) ($row['receipt_filename'] ?? ''));
|
|
if (!is_file($sourcePath)) {
|
|
continue;
|
|
}
|
|
$firstName = $row['purchaser_firstname'] ?? '';
|
|
if (trim($firstName) === '' && !empty($row['purchaser_lastname'] ?? '')) {
|
|
$firstName = $row['purchaser_lastname'];
|
|
}
|
|
$firstName = $this->sanitizeAttachmentComponent($firstName);
|
|
$vendorPart = $this->sanitizeAttachmentComponent($row['retailor'] ?? '');
|
|
$datePart = $this->formatAttachmentDate($row['purchase_date'] ?? '');
|
|
$extension = pathinfo($sourcePath, PATHINFO_EXTENSION);
|
|
$extPart = $extension !== '' ? ('.' . strtolower($extension)) : '';
|
|
$baseName = $firstName . '_' . $vendorPart . '_' . $datePart;
|
|
$entryName = $this->uniqueZipEntryName($baseName, $extPart, $usedNames);
|
|
if ($zip->addFile($sourcePath, $entryName)) {
|
|
$added++;
|
|
}
|
|
}
|
|
$zip->close();
|
|
}
|
|
if ($added > 0 && is_file($archivePath)) {
|
|
$receiptsArchivePath = $archivePath;
|
|
$cleanupPaths[] = $archivePath;
|
|
$receiptZipCreated = true;
|
|
} elseif (is_file($archivePath)) {
|
|
@unlink($archivePath);
|
|
}
|
|
}
|
|
|
|
$checksArchivePath = null;
|
|
$checksAdded = 0;
|
|
if (!empty($checkRows)) {
|
|
$archivePath = $tempDir . '/batch_checks_' . $batchId . '_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . '.zip';
|
|
$zip = new ZipArchive();
|
|
$usedNames = [];
|
|
if ($zip->open($archivePath, ZipArchive::CREATE | ZipArchive::OVERWRITE) === true) {
|
|
foreach ($checkRows as $row) {
|
|
$sourcePath = WRITEPATH . 'uploads/reimbursements/' . basename((string) ($row['filename'] ?? ''));
|
|
if (!is_file($sourcePath)) {
|
|
continue;
|
|
}
|
|
$adminLabel = $row['admin'] ?? 'Admin';
|
|
$firstToken = strtok($adminLabel, ' ');
|
|
$namePart = $this->sanitizeAttachmentComponent($firstToken ?: $adminLabel);
|
|
$extension = pathinfo($sourcePath, PATHINFO_EXTENSION);
|
|
$extPart = $extension !== '' ? ('.' . strtolower($extension)) : '';
|
|
$entryName = $this->uniqueZipEntryName($namePart, $extPart, $usedNames);
|
|
if ($zip->addFile($sourcePath, $entryName)) {
|
|
$checksAdded++;
|
|
}
|
|
}
|
|
$zip->close();
|
|
}
|
|
if ($checksAdded > 0 && is_file($archivePath)) {
|
|
$checksArchivePath = $archivePath;
|
|
$cleanupPaths[] = $archivePath;
|
|
} elseif (is_file($archivePath)) {
|
|
@unlink($archivePath);
|
|
}
|
|
}
|
|
|
|
$attachmentSummary = ['CSV Summary'];
|
|
if ($receiptZipCreated) {
|
|
$attachmentSummary[] = 'Receipt Files';
|
|
}
|
|
if ($checksArchivePath) {
|
|
$attachmentSummary[] = 'Check Scans';
|
|
}
|
|
|
|
$subject = 'Reimbursement Batch Export: ' . $batchLabel;
|
|
$bodyParts = [
|
|
'<p>Please find attached the export for <strong>' . htmlspecialchars($batchLabel, ENT_QUOTES, 'UTF-8') . '</strong>.</p>',
|
|
];
|
|
if ($message !== '') {
|
|
$bodyParts[] = '<p><strong>Message:</strong><br>' . nl2br(htmlspecialchars($message, ENT_QUOTES, 'UTF-8')) . '</p>';
|
|
}
|
|
$bodyParts[] = '<p>Attachments: ' . htmlspecialchars(implode(', ', $attachmentSummary), ENT_QUOTES, 'UTF-8') . '.</p>';
|
|
$bodyParts[] = '<p>Regards,<br>Al Rahma Sunday School</p>';
|
|
$htmlBody = implode("\n", $bodyParts);
|
|
|
|
$attachments = [
|
|
['path' => $csvPath, 'name' => $csvAttachmentName],
|
|
];
|
|
if ($receiptsArchivePath) {
|
|
$attachments[] = ['path' => $receiptsArchivePath, 'name' => $batchFileLabel . '_receipts.zip'];
|
|
}
|
|
if ($checksArchivePath) {
|
|
$attachments[] = ['path' => $checksArchivePath, 'name' => $batchFileLabel . '_checks.zip'];
|
|
}
|
|
|
|
$responseBody = [
|
|
'success' => true,
|
|
'message' => 'Batch export emailed.',
|
|
];
|
|
$statusCode = 200;
|
|
try {
|
|
$mailer = new EmailService();
|
|
if (!$mailer->send($recipientEmail, $subject, $htmlBody, 'general', $attachments)) {
|
|
throw new \RuntimeException('EmailService failed to send the batch export.');
|
|
}
|
|
$responseBody['message'] = 'Email sent successfully.';
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Failed to send batch export email: {msg}', ['msg' => $e->getMessage()]);
|
|
$statusCode = 500;
|
|
$responseBody = [
|
|
'success' => false,
|
|
'error' => 'Failed to send email. Please try again.',
|
|
];
|
|
} finally {
|
|
foreach ($cleanupPaths as $path) {
|
|
if ($path && is_file($path)) {
|
|
@unlink($path);
|
|
}
|
|
}
|
|
}
|
|
|
|
return $this->response->setStatusCode($statusCode)->setJSON($responseBody);
|
|
}
|
|
|
|
public function export()
|
|
{
|
|
$type = $this->request->getGet('type'); // 'processed' or 'under_processing'
|
|
|
|
if ($type === 'under_processing') {
|
|
// Approved expenses not yet reimbursed
|
|
$builder = $this->db->table('expenses');
|
|
$builder->select('expenses.*, u.firstname AS purchaser_firstname, u.lastname AS purchaser_lastname');
|
|
$builder->join('users u', 'u.id = expenses.purchased_by', 'left');
|
|
$builder->where('expenses.status', 'approved'); // <-- fixed
|
|
$builder->where('expenses.reimbursement_id IS NULL', null, false);
|
|
$builder->where('expenses.category !=', 'Donation');
|
|
|
|
$records = $builder->orderBy('expenses.created_at', 'DESC')->get()->getResultArray();
|
|
|
|
// Group rows by purchaser so each person appears once in the CSV
|
|
$grouped = [];
|
|
foreach ($records as $row) {
|
|
$personId = (int)($row['purchased_by'] ?? 0);
|
|
$name = trim(($row['purchaser_firstname'] ?? '') . ' ' . ($row['purchaser_lastname'] ?? ''));
|
|
if ($name === '') {
|
|
$name = $row['purchaser_firstname'] ?? $row['purchaser_lastname'] ?? 'Unknown';
|
|
}
|
|
$key = $personId > 0 ? 'id_' . $personId : 'name_' . strtolower($name);
|
|
if (!isset($grouped[$key])) {
|
|
$grouped[$key] = [
|
|
'name' => $name ?: 'Unknown',
|
|
'total' => 0.0,
|
|
'vendors' => [],
|
|
'descriptions'=> [],
|
|
];
|
|
}
|
|
|
|
$amount = (float)($row['amount'] ?? 0);
|
|
$grouped[$key]['total'] += $amount;
|
|
|
|
$vendor = trim((string)($row['retailor'] ?? ''));
|
|
if ($vendor !== '' && !in_array($vendor, $grouped[$key]['vendors'], true)) {
|
|
$grouped[$key]['vendors'][] = $vendor;
|
|
}
|
|
|
|
$desc = trim((string)($row['description'] ?? ''));
|
|
if ($desc !== '' && !in_array($desc, $grouped[$key]['descriptions'], true)) {
|
|
$grouped[$key]['descriptions'][] = $desc;
|
|
}
|
|
}
|
|
|
|
header('Content-Type: text/csv; charset=UTF-8');
|
|
header('Content-Disposition: attachment; filename=under_processing_expenses.csv');
|
|
$output = fopen('php://output', 'w');
|
|
// UTF-8 BOM for Excel
|
|
fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));
|
|
|
|
fputcsv($output, ['Person', 'Total Amount', 'Vendors', 'Descriptions']);
|
|
foreach ($grouped as $entry) {
|
|
fputcsv($output, [
|
|
$entry['name'],
|
|
number_format($entry['total'], 2, '.', ''),
|
|
implode('; ', $entry['vendors']),
|
|
implode('; ', $entry['descriptions']),
|
|
]);
|
|
}
|
|
fclose($output);
|
|
exit;
|
|
}
|
|
|
|
// Default: processed reimbursements (filterable)
|
|
$builder = $this->reimbModel
|
|
->select('reimbursements.*, u.firstname AS reimb_firstname, u.lastname AS reimb_lastname')
|
|
->join('users u', 'u.id = reimbursements.reimbursed_to', 'left');
|
|
|
|
if ($semester = $this->request->getGet('semester')) {
|
|
$builder->where('reimbursements.semester', $semester);
|
|
}
|
|
if ($status = $this->request->getGet('status')) {
|
|
$builder->where('reimbursements.status', $status);
|
|
}
|
|
if ($userId = $this->request->getGet('user_id')) {
|
|
$builder->where('reimbursements.reimbursed_to', $userId);
|
|
}
|
|
if ($schoolYear = $this->request->getGet('school_year')) {
|
|
$builder->where('reimbursements.school_year', $schoolYear);
|
|
}
|
|
|
|
$records = $builder->orderBy('reimbursements.created_at', 'DESC')->get()->getResultArray();
|
|
|
|
foreach ($records as &$row) {
|
|
$hasName = trim(($row['reimb_firstname'] ?? '') . ' ' . ($row['reimb_lastname'] ?? '')) !== '';
|
|
if (!$hasName) {
|
|
$label = $this->specialRecipientLabel($row['reimbursed_to'] ?? null);
|
|
if ($label !== null) {
|
|
$row['reimb_firstname'] = $label;
|
|
$row['reimb_lastname'] = '';
|
|
}
|
|
}
|
|
}
|
|
unset($row);
|
|
|
|
header('Content-Type: text/csv; charset=UTF-8');
|
|
header('Content-Disposition: attachment; filename=reimbursements_export.csv');
|
|
$output = fopen('php://output', 'w');
|
|
// UTF-8 BOM for Excel
|
|
fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));
|
|
|
|
fputcsv($output, ['Amount', 'Reimbursed To', 'Status', 'Method', 'Check #', 'School Year', 'Semester', 'Date']);
|
|
foreach ($records as $r) {
|
|
fputcsv($output, [
|
|
$r['amount'],
|
|
trim(($r['reimb_firstname'] ?? '') . ' ' . ($r['reimb_lastname'] ?? '')),
|
|
$r['status'],
|
|
$r['reimbursement_method'] ?? '-',
|
|
$r['check_number'] ?? '-',
|
|
$r['school_year'] ?? '-',
|
|
$r['semester'] ?? '-',
|
|
$r['created_at'],
|
|
]);
|
|
}
|
|
fclose($output);
|
|
exit;
|
|
}
|
|
|
|
public function exportBatch()
|
|
{
|
|
$batchId = (int) ($this->request->getGet('batch_id') ?? 0);
|
|
if ($batchId <= 0) {
|
|
return $this->response->setStatusCode(400)->setBody('Invalid batch specified.');
|
|
}
|
|
|
|
$batch = $this->batchModel->find($batchId);
|
|
if (!$batch) {
|
|
throw new PageNotFoundException('Batch not found.');
|
|
}
|
|
|
|
$batchLabel = trim((string) ($batch['title'] ?? '')) ?: ('Batch #' . $batchId);
|
|
$receiptRows = $this->fetchBatchReceiptRows($batchId);
|
|
|
|
header('Content-Type: text/csv; charset=UTF-8');
|
|
$filename = sprintf('batch_%d_items.csv', $batchId);
|
|
header('Content-Disposition: attachment; filename=' . $filename);
|
|
$output = fopen('php://output', 'w');
|
|
// UTF-8 BOM for Excel
|
|
fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));
|
|
|
|
$batchClosedDate = $this->formatCsvDate($batch['closed_at'] ?? null);
|
|
$this->writeBatchCsvMetadata($output, $batchLabel, $batch);
|
|
$this->writeBatchCsvSummary($output, $receiptRows, $batchClosedDate);
|
|
|
|
fclose($output);
|
|
exit;
|
|
}
|
|
|
|
public function create()
|
|
{
|
|
$expenseId = $this->request->getGet('expense_id');
|
|
|
|
// users list (unchanged)
|
|
$users = $this->recipientOptions();
|
|
|
|
// fetch expense to prefill amount
|
|
$prefillAmount = null;
|
|
if ($expenseId) {
|
|
$exp = $this->expenseModel
|
|
->select('id, amount')
|
|
->find((int)$expenseId);
|
|
if ($exp) {
|
|
$prefillAmount = $exp['amount'];
|
|
}
|
|
}
|
|
|
|
return view('reimbursements/create', [
|
|
'users' => $users,
|
|
'expense_id' => $expenseId,
|
|
'prefill_amount' => $prefillAmount,
|
|
]);
|
|
}
|
|
|
|
|
|
public function store()
|
|
{
|
|
helper(['form']);
|
|
|
|
// Normalize method just in case (e.g., "cash" → "Cash")
|
|
$methodRaw = (string) $this->request->getPost('reimbursement_method');
|
|
$method = ucfirst(strtolower($methodRaw));
|
|
$expenseId = (int) ($this->request->getPost('expense_id') ?? 0);
|
|
|
|
// Base rules
|
|
$rules = [
|
|
'amount' => 'required|decimal|greater_than[0]',
|
|
'reimbursed_to' => 'required|is_natural_no_zero',
|
|
'reimbursement_method' => 'required|in_list[Cash,Check]',
|
|
];
|
|
|
|
// Messages (used only when the corresponding rule is present)
|
|
$messages = [
|
|
'receipt' => [
|
|
'uploaded' => 'Receipt file is required.',
|
|
'max_size' => 'Maximum file size is 2MB.',
|
|
'ext_in' => 'Allowed formats: JPG, JPEG, PNG, WEBP, GIF, or PDF.',
|
|
'mime_in' => 'Allowed formats: JPG, JPEG, PNG, WEBP, GIF, or PDF.',
|
|
],
|
|
];
|
|
|
|
// Conditional validation
|
|
if ($method === 'Check') {
|
|
// Require check number + receipt for checks
|
|
$rules['check_number'] = 'required|string|max_length[50]';
|
|
$rules['receipt'] = 'uploaded[receipt]'
|
|
. '|max_size[receipt,2048]'
|
|
. '|ext_in[receipt,jpg,jpeg,png,webp,gif,pdf]'
|
|
. '|mime_in[receipt,image/jpg,image/jpeg,image/png,image/webp,image/gif,application/pdf]';
|
|
} elseif ($method === 'Cash') {
|
|
// Receipt optional for cash; if provided, validate it
|
|
$file = $this->request->getFile('receipt');
|
|
if ($file && $file->isValid() && ($file->getSize() ?? 0) > 0) {
|
|
$rules['receipt'] = 'max_size[receipt,2048]'
|
|
. '|ext_in[receipt,jpg,jpeg,png,webp,gif,pdf]'
|
|
. '|mime_in[receipt,image/jpg,image/jpeg,image/png,image/webp,image/gif,application/pdf]';
|
|
}
|
|
}
|
|
|
|
if (!$this->validate($rules, $messages)) {
|
|
return redirect()->back()->withInput()->with('errors', $this->validator->getErrors());
|
|
}
|
|
|
|
if ($expenseId > 0) {
|
|
$expense = $this->expenseModel->find($expenseId);
|
|
if ($expense && strcasecmp($expense['category'] ?? '', 'Donation') === 0) {
|
|
return redirect()->back()->withInput()->with('errors', [
|
|
'expense_id' => 'Donation expenses are tracked but should not be reimbursed.'
|
|
]);
|
|
}
|
|
}
|
|
|
|
// Store file only if one was actually uploaded
|
|
try {
|
|
$receiptName = $this->saveReimbReceipt($this->request->getFile('receipt'));
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Failed to save reimbursement receipt in store(): {msg}', ['msg' => $e->getMessage()]);
|
|
return redirect()->back()->withInput()->with('errors', [
|
|
'receipt' => 'Failed to save uploaded file. Please try again or contact admin.'
|
|
]);
|
|
}
|
|
|
|
$userId = (int) (session()->get('user_id') ?? 0);
|
|
$recipientId = (int) $this->request->getPost('reimbursed_to');
|
|
|
|
// Mark reimbursement as Paid when recorded
|
|
$data = [
|
|
'expense_id' => $expenseId ?: null,
|
|
'amount' => $this->request->getPost('amount'),
|
|
'reimbursed_to' => $recipientId,
|
|
'description' => $this->request->getPost('description'),
|
|
'reimbursement_method' => $method,
|
|
'check_number' => $method === 'Check' ? $this->request->getPost('check_number') : null,
|
|
'receipt_path' => $receiptName, // may be null for Cash
|
|
'school_year' => $this->schoolYear,
|
|
'semester' => $this->semester,
|
|
'added_by' => $userId,
|
|
'approved_by' => $userId,
|
|
'status' => 'Paid',
|
|
];
|
|
|
|
$this->reimbModel->insert($data);
|
|
$reimbursementId = $this->reimbModel->getInsertID();
|
|
|
|
if ($expenseId = $this->request->getPost('expense_id')) {
|
|
$this->expenseModel->update($expenseId, ['reimbursement_id' => $reimbursementId]);
|
|
}
|
|
|
|
return redirect()->to('/reimbursements')->with('success', 'Reimbursement recorded as Paid.');
|
|
}
|
|
|
|
|
|
// Optional old flow kept for compatibility (also sets Paid)
|
|
public function process()
|
|
{
|
|
$expenseId = (int) ($this->request->getPost('expense_id') ?? 0);
|
|
if ($expenseId > 0) {
|
|
$expense = $this->expenseModel->find($expenseId);
|
|
if ($expense && strcasecmp($expense['category'] ?? '', 'Donation') === 0) {
|
|
return redirect()->back()->withInput()->with('errors', [
|
|
'expense_id' => 'Donation expenses are tracked but should not be reimbursed.'
|
|
]);
|
|
}
|
|
}
|
|
|
|
try {
|
|
$receiptName = $this->saveReimbReceipt($this->request->getFile('receipt'));
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Failed to save reimbursement receipt in process(): {msg}', ['msg' => $e->getMessage()]);
|
|
return redirect()->back()->withInput()->with('errors', [
|
|
'receipt' => 'Failed to save uploaded file. Please try again or contact admin.'
|
|
]);
|
|
}
|
|
|
|
$userId = (int) (session()->get('user_id') ?? 0);
|
|
$recipientId = (int) $this->request->getPost('reimbursed_to');
|
|
|
|
$reimbursementId = $this->reimbModel->insert([
|
|
'amount' => $this->request->getPost('amount'),
|
|
'reimbursed_to' => $recipientId,
|
|
'approved_by' => $userId,
|
|
'receipt_path' => $receiptName,
|
|
'description' => 'Expense reimbursement',
|
|
'status' => 'Paid',
|
|
'added_by' => $userId,
|
|
'school_year' => $this->schoolYear,
|
|
'semester' => $this->semester,
|
|
'check_number' => $this->request->getPost('check_number'),
|
|
'reimbursement_method' => $this->request->getPost('reimbursement_method')
|
|
]);
|
|
|
|
$this->expenseModel->update($expenseId, [
|
|
'reimbursement_id' => $reimbursementId
|
|
]);
|
|
|
|
return redirect()->to('/reimbursements/under-processing')->with('success', 'Reimbursement processed!');
|
|
}
|
|
|
|
public function reimbursedExpenses()
|
|
{
|
|
$expenses = $this->db->table('reimbursements r')
|
|
->select('
|
|
e.amount AS expense_amount,
|
|
e.category,
|
|
e.description,
|
|
e.receipt_path AS expense_receipt,
|
|
e.purchased_by,
|
|
r.amount AS reimb_amount,
|
|
r.reimbursement_method,
|
|
r.check_number,
|
|
r.receipt_path AS reimb_receipt,
|
|
r.status AS reimb_status,
|
|
r.created_at,
|
|
u.firstname AS purchaser_firstname,
|
|
u.lastname AS purchaser_lastname,
|
|
a.firstname AS approver_firstname,
|
|
a.lastname AS approver_lastname
|
|
')
|
|
->join('expenses e', 'e.id = r.expense_id')
|
|
->join('users u', 'u.id = e.purchased_by', 'left')
|
|
->join('users a', 'a.id = r.approved_by', 'left')
|
|
->orderBy('r.created_at', 'DESC')
|
|
->get()
|
|
->getResultArray();
|
|
|
|
foreach ($expenses as &$e) {
|
|
$e['expense_receipt_url'] = $this->expenseReceiptUrl($e['expense_receipt'] ?? null);
|
|
$e['reimb_receipt_url'] = $this->reimbReceiptUrl($e['reimb_receipt'] ?? null);
|
|
}
|
|
|
|
return view('reimbursements/reimbursed_expenses', ['expenses' => $expenses]);
|
|
}
|
|
|
|
// app/Controllers/ReimbursementController.php
|
|
|
|
|
|
public function edit(int $id)
|
|
{
|
|
$reimb = $this->reimbModel->find($id);
|
|
if (!$reimb) {
|
|
throw PageNotFoundException::forPageNotFound("Reimbursement #$id not found");
|
|
}
|
|
|
|
$users = $this->recipientOptions();
|
|
|
|
return view('reimbursements/edit', [
|
|
'reimb' => $reimb,
|
|
'users' => $users,
|
|
'receipt_url' => $reimb['receipt_path'] ? site_url('reimbreceipts/' . basename($reimb['receipt_path'])) : null,
|
|
]);
|
|
}
|
|
|
|
public function update(int $id)
|
|
{
|
|
helper(['form']);
|
|
|
|
$reimb = $this->reimbModel->find($id);
|
|
if (!$reimb) {
|
|
throw PageNotFoundException::forPageNotFound("Reimbursement #$id not found");
|
|
}
|
|
|
|
$methodRaw = (string) $this->request->getPost('reimbursement_method');
|
|
$method = ucfirst(strtolower($methodRaw));
|
|
|
|
$rules = [
|
|
'amount' => 'required|decimal|greater_than[0]',
|
|
'reimbursed_to' => 'required|is_natural_no_zero',
|
|
'reimbursement_method' => 'required|in_list[Cash,Check]',
|
|
];
|
|
|
|
// Optional file validation (only if provided)
|
|
$file = $this->request->getFile('receipt');
|
|
if ($method === 'Check') {
|
|
$rules['check_number'] = 'required|string|max_length[50]';
|
|
if ($file && $file->isValid() && ($file->getSize() ?? 0) > 0) {
|
|
$rules['receipt'] = 'max_size[receipt,2048]'
|
|
. '|ext_in[receipt,jpg,jpeg,png,webp,gif,pdf]'
|
|
. '|mime_in[receipt,image/jpg,image/jpeg,image/png,image/webp,image/gif,application/pdf]';
|
|
}
|
|
} else { // Cash
|
|
if ($file && $file->isValid() && ($file->getSize() ?? 0) > 0) {
|
|
$rules['receipt'] = 'max_size[receipt,2048]'
|
|
. '|ext_in[receipt,jpg,jpeg,png,webp,gif,pdf]'
|
|
. '|mime_in[receipt,image/jpg,image/jpeg,image/png,image/webp,image/gif,application/pdf]';
|
|
}
|
|
}
|
|
|
|
if (!$this->validate($rules)) {
|
|
return redirect()->back()->withInput()->with('errors', $this->validator->getErrors());
|
|
}
|
|
|
|
// Handle new/removed receipt
|
|
$receiptName = $reimb['receipt_path'];
|
|
try {
|
|
$maybeNew = $this->saveReimbReceipt($file);
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Failed to save reimbursement receipt in update(): {msg}', ['msg' => $e->getMessage()]);
|
|
return redirect()->back()->withInput()->with('errors', [
|
|
'receipt' => 'Failed to save uploaded file. Please try again or contact admin.'
|
|
]);
|
|
}
|
|
if ($maybeNew !== null) {
|
|
$receiptName = $maybeNew;
|
|
}
|
|
if ($this->request->getPost('remove_receipt') === '1') {
|
|
$receiptName = null;
|
|
}
|
|
|
|
$this->reimbModel->update($id, [
|
|
'amount' => $this->request->getPost('amount'),
|
|
'reimbursed_to' => (int) $this->request->getPost('reimbursed_to'),
|
|
'description' => $this->request->getPost('description'),
|
|
'reimbursement_method' => $method,
|
|
'check_number' => $method === 'Check' ? $this->request->getPost('check_number') : null,
|
|
'receipt_path' => $receiptName,
|
|
'updated_by' => (int) (session()->get('user_id') ?? 0),
|
|
// keep status as-is; or set from form if you add a status field
|
|
]);
|
|
|
|
return redirect()->to('/reimbursements')->with('success', 'Reimbursement updated.');
|
|
}
|
|
|
|
private function lookupReimbursementId(int $expenseId): ?int
|
|
{
|
|
if ($expenseId <= 0) {
|
|
return null;
|
|
}
|
|
|
|
$row = $this->reimbModel
|
|
->select('id')
|
|
->where('expense_id', $expenseId)
|
|
->orderBy('id', 'DESC')
|
|
->first();
|
|
|
|
return $row ? (int) $row['id'] : null;
|
|
}
|
|
}
|