root
02ba2fe6b6
API CI/CD / Validate (composer + pint) (push) Successful in 3m8s
API CI/CD / Build frontend assets (push) Successful in 1m2s
API CI/CD / Test (PHPUnit) (push) Failing after 6m5s
API CI/CD / Security audit (push) Failing after 52s
API CI/CD / Deploy to shared hosting (PHP) (push) Has been skipped
1511 lines
57 KiB
PHP
1511 lines
57 KiB
PHP
<?php
|
|
|
|
namespace App\Services\SchoolYears;
|
|
|
|
use App\Models\AuditLog;
|
|
use App\Models\Configuration;
|
|
use App\Models\ParentAccount;
|
|
use App\Models\ParentBalanceTransfer;
|
|
use App\Models\SchoolYear;
|
|
use App\Services\Promotions\LevelProgressionService;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
use Illuminate\Validation\ValidationException;
|
|
use RuntimeException;
|
|
|
|
class SchoolYearClosureService
|
|
{
|
|
public function __construct(
|
|
private SchoolYearResolver $resolver,
|
|
private LevelProgressionService $progression
|
|
) {}
|
|
|
|
public function list(): array
|
|
{
|
|
$this->resolver->ensureCurrentTracked();
|
|
|
|
return SchoolYear::query()
|
|
->orderByDesc('start_date')
|
|
->orderByDesc('id')
|
|
->get()
|
|
->map(fn (SchoolYear $year) => $this->presentSchoolYear($year))
|
|
->all();
|
|
}
|
|
|
|
public function createYear(array $payload, ?int $actorId = null): array
|
|
{
|
|
$name = trim((string) ($payload['name'] ?? ''));
|
|
$startDate = (string) ($payload['start_date'] ?? '');
|
|
$endDate = (string) ($payload['end_date'] ?? '');
|
|
|
|
$errors = [];
|
|
if ($name === '') {
|
|
$errors[] = 'School year name is required.';
|
|
}
|
|
if ($startDate === '' || $endDate === '') {
|
|
$errors[] = 'Start date and end date are required.';
|
|
}
|
|
if ($startDate !== '' && $endDate !== '' && $startDate >= $endDate) {
|
|
$errors[] = 'Start date must be before end date.';
|
|
}
|
|
if (SchoolYear::query()->where('name', $name)->exists()) {
|
|
$errors[] = sprintf('School year %s already exists.', $name);
|
|
}
|
|
if ($this->hasDateOverlap($startDate, $endDate)) {
|
|
$errors[] = 'School year dates overlap an existing year.';
|
|
}
|
|
|
|
if ($errors !== []) {
|
|
throw ValidationException::withMessages(['school_year' => $errors]);
|
|
}
|
|
|
|
$year = SchoolYear::query()->create([
|
|
'name' => $name,
|
|
'start_date' => $startDate,
|
|
'end_date' => $endDate,
|
|
'status' => SchoolYear::STATUS_DRAFT,
|
|
'is_current' => false,
|
|
]);
|
|
|
|
$this->logAudit(
|
|
$actorId,
|
|
'school_year_created',
|
|
'school_years',
|
|
(int) $year->id,
|
|
null,
|
|
$year->toArray(),
|
|
['mode' => 'draft']
|
|
);
|
|
|
|
return $this->presentSchoolYear($year);
|
|
}
|
|
|
|
public function updateYear(int $id, array $payload, ?int $actorId = null): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
$oldState = $year->toArray();
|
|
$name = trim((string) ($payload['name'] ?? $year->name));
|
|
$startDate = (string) ($payload['start_date'] ?? optional($year->start_date)->toDateString());
|
|
$endDate = (string) ($payload['end_date'] ?? optional($year->end_date)->toDateString());
|
|
|
|
$errors = [];
|
|
if ($startDate === '' || $endDate === '') {
|
|
$errors[] = 'Start date and end date are required.';
|
|
}
|
|
if ($startDate !== '' && $endDate !== '' && $startDate >= $endDate) {
|
|
$errors[] = 'Start date must be before end date.';
|
|
}
|
|
if ($name === '') {
|
|
$errors[] = 'School year name is required.';
|
|
}
|
|
if (SchoolYear::query()->where('name', $name)->where('id', '!=', $year->id)->exists()) {
|
|
$errors[] = sprintf('School year %s already exists.', $name);
|
|
}
|
|
if ($this->hasDateOverlap($startDate, $endDate, (int) $year->id)) {
|
|
$errors[] = 'School year dates overlap an existing year.';
|
|
}
|
|
if ($name !== $year->name && $this->schoolYearHasOperationalRecords($year->name)) {
|
|
$errors[] = 'This school year name is already referenced by operational records and cannot be renamed safely.';
|
|
}
|
|
|
|
if ($errors !== []) {
|
|
throw ValidationException::withMessages(['school_year' => $errors]);
|
|
}
|
|
|
|
$year->name = $name;
|
|
$year->start_date = $startDate;
|
|
$year->end_date = $endDate;
|
|
$year->save();
|
|
|
|
if ($year->is_current) {
|
|
Configuration::setConfigValueByKey('school_year', $year->name);
|
|
}
|
|
|
|
$this->logAudit(
|
|
$actorId,
|
|
'school_year_updated',
|
|
'school_years',
|
|
(int) $year->id,
|
|
$oldState,
|
|
$year->fresh()?->toArray(),
|
|
null
|
|
);
|
|
|
|
return $this->presentSchoolYear($year->fresh());
|
|
}
|
|
|
|
public function summary(int $id): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
$balances = $this->parentBalances($id);
|
|
$promotions = $this->promotionPreview($id);
|
|
|
|
return [
|
|
'school_year' => $this->presentSchoolYear($year),
|
|
'totals' => [
|
|
'students_considered' => $promotions['summary']['total_students'],
|
|
'students_blocked' => $promotions['summary']['hold'],
|
|
'parents_with_balances' => $balances['summary']['parents_with_positive_balance'],
|
|
'parents_with_positive_balance' => $balances['summary']['parents_with_positive_balance'],
|
|
'parents_with_credit_balance' => $balances['summary']['parents_with_credit_balance'],
|
|
'total_positive_unpaid_balance' => $balances['summary']['total_positive_unpaid_balance'],
|
|
'total_credit_balance' => $balances['summary']['total_credit_balance'],
|
|
'net_balance_to_transfer' => $balances['summary']['net_balance_impact'],
|
|
'net_balance_impact' => $balances['summary']['net_balance_impact'],
|
|
],
|
|
];
|
|
}
|
|
|
|
public function summaryByYearName(string $schoolYear): array
|
|
{
|
|
return $this->summary((int) $this->findByNameOrFail($schoolYear)->id);
|
|
}
|
|
|
|
public function closingReport(int $id): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
$audit = $this->latestClosureAudit((int) $year->id);
|
|
$metadata = is_array($audit?->metadata) ? $audit->metadata : [];
|
|
|
|
$newYearName = trim((string) ($metadata['new_school_year'] ?? ''));
|
|
if ($newYearName === '') {
|
|
$newYearName = $this->resolveClosedTargetYearName($year->name);
|
|
}
|
|
|
|
$newYear = $newYearName !== ''
|
|
? SchoolYear::query()->where('name', $newYearName)->first()
|
|
: null;
|
|
|
|
$promotionCounts = is_array($metadata['promotion_counts'] ?? null)
|
|
? $metadata['promotion_counts']
|
|
: [];
|
|
$transferTotals = $this->transferTotalsForClosedYear($year->name, $newYearName !== '' ? $newYearName : null);
|
|
$invoiceBalanceTotals = $this->positiveInvoiceBalanceTotals($year->name);
|
|
|
|
$warnings = [];
|
|
if (! $audit) {
|
|
$warnings[] = 'No closure audit log was found for this school year. Totals were reconstructed from current records where possible.';
|
|
}
|
|
if ($year->status !== SchoolYear::STATUS_CLOSED) {
|
|
$warnings[] = sprintf('School year %s is currently %s, so this is a live preview rather than a final closure report.', $year->name, $year->status);
|
|
}
|
|
if ($newYearName !== '' && ! $newYear) {
|
|
$warnings[] = sprintf('The closure target school year %s was referenced but no matching school_years row was found.', $newYearName);
|
|
}
|
|
|
|
return [
|
|
'old_school_year' => $this->presentSchoolYear($year),
|
|
'new_school_year' => $this->presentSchoolYear($newYear),
|
|
'closed_by' => $this->resolveUserName((int) ($year->closed_by ?? $audit?->user_id ?? 0)),
|
|
'closed_at' => optional($year->closed_at ?? $audit?->created_at)->toDateTimeString(),
|
|
'audit_reference' => $audit ? sprintf('audit_logs:%d', (int) $audit->id) : null,
|
|
'totals' => [
|
|
'students_promoted' => (int) ($promotionCounts['promote'] ?? 0),
|
|
'students_repeated' => (int) ($promotionCounts['repeat'] ?? 0),
|
|
'students_graduated' => (int) ($promotionCounts['graduate'] ?? 0),
|
|
'students_transferred' => (int) ($promotionCounts['transfer'] ?? 0),
|
|
'students_withdrawn' => (int) ($promotionCounts['withdraw'] ?? 0),
|
|
'parents_with_unpaid_balances' => $invoiceBalanceTotals['parents_with_unpaid_balances'],
|
|
'total_unpaid_balance_transferred' => $invoiceBalanceTotals['total_unpaid_balance'],
|
|
'parents_with_credit_balances' => $transferTotals['parents_with_credit_balances'],
|
|
'total_credit_carried_or_reported' => $transferTotals['total_credit_carried_or_reported'],
|
|
'total_credit_transferred' => $transferTotals['total_credit_carried_or_reported'],
|
|
'net_balance_impact' => round(
|
|
$invoiceBalanceTotals['total_unpaid_balance'] - $transferTotals['total_credit_carried_or_reported'],
|
|
2
|
|
),
|
|
],
|
|
'warnings' => $warnings,
|
|
];
|
|
}
|
|
|
|
public function closingReportByYearName(string $schoolYear): array
|
|
{
|
|
return $this->closingReport((int) $this->findByNameOrFail($schoolYear)->id);
|
|
}
|
|
|
|
public function validateClose(int $id, array $payload): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
$newYear = $payload['new_school_year'] ?? [];
|
|
$targetName = trim((string) ($newYear['name'] ?? $this->resolver->suggestNextName($year->name) ?? ''));
|
|
$promotionPreview = $this->buildPromotionPreview($year->name, $targetName);
|
|
$balances = $this->buildParentBalanceRows($year->name, $targetName);
|
|
|
|
$errors = [];
|
|
$warnings = [];
|
|
|
|
if ($year->status !== SchoolYear::STATUS_ACTIVE) {
|
|
$errors[] = sprintf('Only an active school year can be closed. %s is currently %s.', $year->name, $year->status);
|
|
}
|
|
|
|
$errors = array_merge($errors, $this->validateNewYearPayload($year, $newYear));
|
|
|
|
if ($promotionPreview['summary']['hold'] > 0) {
|
|
$errors[] = sprintf('%d students are missing promotion decisions or target placement.', $promotionPreview['summary']['hold']);
|
|
}
|
|
|
|
$pendingPayments = $this->countPendingPayments($year->name);
|
|
if ($pendingPayments > 0) {
|
|
$errors[] = sprintf('%d payments are not posted or still pending.', $pendingPayments);
|
|
}
|
|
|
|
$draftInvoices = $this->countDraftInvoices($year->name);
|
|
if ($draftInvoices > 0) {
|
|
$errors[] = sprintf('%d invoices are still in draft status.', $draftInvoices);
|
|
}
|
|
|
|
$invalidInvoices = $this->countFinancialInconsistencies($year->name);
|
|
if ($invalidInvoices > 0) {
|
|
$errors[] = sprintf('%d invoices have inconsistent amount, paid amount, and balance values.', $invalidInvoices);
|
|
}
|
|
|
|
$duplicateAccounts = $this->countDuplicateParentAccounts();
|
|
if ($duplicateAccounts > 0) {
|
|
$errors[] = sprintf('%d duplicate parent account rows exist.', $duplicateAccounts);
|
|
}
|
|
|
|
if ($balances['summary']['existing_transfer_conflicts'] > 0) {
|
|
$errors[] = sprintf(
|
|
'%d parent balance transfers already exist for the requested year transition.',
|
|
$balances['summary']['existing_transfer_conflicts']
|
|
);
|
|
}
|
|
|
|
if ($balances['summary']['parents_with_credit_balance'] > 0) {
|
|
$warnings[] = sprintf(
|
|
'%d parents have a credit balance that will be carried into the new year.',
|
|
$balances['summary']['parents_with_credit_balance']
|
|
);
|
|
}
|
|
|
|
return [
|
|
'can_close' => $errors === [],
|
|
'errors' => $errors,
|
|
'warnings' => $warnings,
|
|
'promotion_summary' => $promotionPreview['summary'],
|
|
'financial_summary' => $balances['summary'],
|
|
];
|
|
}
|
|
|
|
public function validateCloseByYearName(string $schoolYear, array $payload): array
|
|
{
|
|
return $this->validateClose((int) $this->findByNameOrFail($schoolYear)->id, $payload);
|
|
}
|
|
|
|
public function previewClose(int $id, array $payload): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
$newYear = $payload['new_school_year'] ?? [];
|
|
$targetName = trim((string) ($newYear['name'] ?? $this->resolver->suggestNextName($year->name) ?? ''));
|
|
|
|
return [
|
|
'school_year' => $this->presentSchoolYear($year),
|
|
'validation' => $this->validateClose($id, $payload),
|
|
'promotion_preview' => $this->buildPromotionPreview($year->name, $targetName),
|
|
'parent_balances' => $this->buildParentBalanceRows($year->name, $targetName),
|
|
];
|
|
}
|
|
|
|
public function previewCloseByYearName(string $schoolYear, array $payload): array
|
|
{
|
|
return $this->previewClose((int) $this->findByNameOrFail($schoolYear)->id, $payload);
|
|
}
|
|
|
|
public function close(int $id, array $payload, ?int $actorId): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
$validation = $this->validateClose($id, $payload);
|
|
if (! $validation['can_close']) {
|
|
throw ValidationException::withMessages(['school_year' => $validation['errors']]);
|
|
}
|
|
|
|
$confirmation = trim((string) ($payload['confirmation'] ?? ''));
|
|
$expected = sprintf('CLOSE %s', $year->name);
|
|
if ($confirmation !== $expected) {
|
|
throw ValidationException::withMessages([
|
|
'confirmation' => [sprintf('Confirmation must exactly match "%s".', $expected)],
|
|
]);
|
|
}
|
|
|
|
$newYearPayload = $payload['new_school_year'];
|
|
$promotionPreview = $this->buildPromotionPreview($year->name, $newYearPayload['name']);
|
|
$balances = ! empty($payload['transfer_unpaid_balances'])
|
|
? $this->buildParentBalanceRows($year->name, $newYearPayload['name'])
|
|
: ['rows' => [], 'summary' => ['net_balance_to_transfer' => 0]];
|
|
|
|
return DB::transaction(function () use ($year, $payload, $actorId, $newYearPayload, $promotionPreview, $balances) {
|
|
$newYear = SchoolYear::query()->firstOrCreate(
|
|
['name' => $newYearPayload['name']],
|
|
[
|
|
'start_date' => $newYearPayload['start_date'],
|
|
'end_date' => $newYearPayload['end_date'],
|
|
'status' => SchoolYear::STATUS_DRAFT,
|
|
'is_current' => false,
|
|
]
|
|
);
|
|
|
|
if ($newYear->status === SchoolYear::STATUS_CLOSED) {
|
|
throw new RuntimeException('The requested new school year already exists as closed and cannot be activated.');
|
|
}
|
|
|
|
SchoolYear::query()->where('is_current', true)->update([
|
|
'is_current' => false,
|
|
'updated_at' => now(),
|
|
]);
|
|
|
|
$oldState = $year->toArray();
|
|
$year->status = SchoolYear::STATUS_CLOSED;
|
|
$year->is_current = false;
|
|
$year->closed_at = now();
|
|
$year->closed_by = $actorId;
|
|
$year->save();
|
|
|
|
$newYear->start_date = $newYearPayload['start_date'];
|
|
$newYear->end_date = $newYearPayload['end_date'];
|
|
$newYear->status = SchoolYear::STATUS_ACTIVE;
|
|
$newYear->is_current = true;
|
|
$newYear->closed_at = null;
|
|
$newYear->closed_by = null;
|
|
$newYear->save();
|
|
|
|
Configuration::setConfigValueByKey('school_year', $newYear->name);
|
|
|
|
$promotionCounts = $this->applyPromotions($promotionPreview['rows'], $newYear->name, $newYear->start_date, $actorId);
|
|
$balanceCounts = ! empty($payload['transfer_unpaid_balances'])
|
|
? $this->applyBalanceTransfers($balances['rows'], $year->name, $newYear->name, $actorId)
|
|
: [
|
|
'transferred_parents' => 0,
|
|
'transferred_amount' => 0.0,
|
|
'credit_parents' => 0,
|
|
'credit_amount' => 0.0,
|
|
];
|
|
|
|
$this->logAudit(
|
|
$actorId,
|
|
'school_year_closed',
|
|
'school_years',
|
|
(int) $year->id,
|
|
$oldState,
|
|
$year->fresh()?->toArray(),
|
|
[
|
|
'new_school_year' => $newYear->name,
|
|
'promotion_counts' => $promotionCounts,
|
|
'balance_counts' => $balanceCounts,
|
|
]
|
|
);
|
|
|
|
return [
|
|
'closed_school_year' => $this->presentSchoolYear($year->fresh()),
|
|
'new_school_year' => $this->presentSchoolYear($newYear->fresh()),
|
|
'promotion_counts' => $promotionCounts,
|
|
'balance_counts' => $balanceCounts,
|
|
];
|
|
});
|
|
}
|
|
|
|
public function closeByYearName(string $schoolYear, array $payload, ?int $actorId): array
|
|
{
|
|
return $this->close((int) $this->findByNameOrFail($schoolYear)->id, $payload, $actorId);
|
|
}
|
|
|
|
public function reopen(int $id, ?int $actorId): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
|
|
return DB::transaction(function () use ($year, $actorId) {
|
|
$currentActive = SchoolYear::query()->where('is_current', true)->where('id', '!=', $year->id)->first();
|
|
if ($currentActive) {
|
|
$currentActive->status = SchoolYear::STATUS_DRAFT;
|
|
$currentActive->is_current = false;
|
|
$currentActive->save();
|
|
}
|
|
|
|
$oldState = $year->toArray();
|
|
$year->status = SchoolYear::STATUS_ACTIVE;
|
|
$year->is_current = true;
|
|
$year->closed_at = null;
|
|
$year->closed_by = null;
|
|
$year->save();
|
|
|
|
Configuration::setConfigValueByKey('school_year', $year->name);
|
|
|
|
$this->logAudit(
|
|
$actorId,
|
|
'school_year_reopened',
|
|
'school_years',
|
|
(int) $year->id,
|
|
$oldState,
|
|
$year->fresh()?->toArray(),
|
|
['previous_active_year' => $currentActive?->name]
|
|
);
|
|
|
|
return $this->presentSchoolYear($year->fresh());
|
|
});
|
|
}
|
|
|
|
public function reopenByYearName(string $schoolYear, ?int $actorId): array
|
|
{
|
|
return $this->reopen((int) $this->findByNameOrFail($schoolYear)->id, $actorId);
|
|
}
|
|
|
|
public function parentBalances(int $id, ?string $toSchoolYear = null): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
|
|
return $this->buildParentBalanceRows(
|
|
$year->name,
|
|
$toSchoolYear ?: ($this->resolver->suggestNextName($year->name) ?? $year->name),
|
|
false
|
|
);
|
|
}
|
|
|
|
public function parentBalancesByYearName(string $schoolYear, ?string $toSchoolYear = null): array
|
|
{
|
|
return $this->parentBalances((int) $this->findByNameOrFail($schoolYear)->id, $toSchoolYear);
|
|
}
|
|
|
|
public function promotionPreview(int $id, ?string $toSchoolYear = null): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
|
|
return $this->buildPromotionPreview(
|
|
$year->name,
|
|
$toSchoolYear ?: ($this->resolver->suggestNextName($year->name) ?? $year->name)
|
|
);
|
|
}
|
|
|
|
public function promotionPreviewByYearName(string $schoolYear, ?string $toSchoolYear = null): array
|
|
{
|
|
return $this->promotionPreview((int) $this->findByNameOrFail($schoolYear)->id, $toSchoolYear);
|
|
}
|
|
|
|
public function findOrFail(int $id): SchoolYear
|
|
{
|
|
$this->resolver->ensureCurrentTracked();
|
|
|
|
return SchoolYear::query()->findOrFail($id);
|
|
}
|
|
|
|
public function findByNameOrFail(string $schoolYear): SchoolYear
|
|
{
|
|
$this->resolver->ensureCurrentTracked();
|
|
|
|
return SchoolYear::query()->where('name', trim($schoolYear))->firstOrFail();
|
|
}
|
|
|
|
private function validateNewYearPayload(SchoolYear $currentYear, array $newYear): array
|
|
{
|
|
$errors = [];
|
|
$name = trim((string) ($newYear['name'] ?? ''));
|
|
$startDate = $newYear['start_date'] ?? null;
|
|
$endDate = $newYear['end_date'] ?? null;
|
|
|
|
if ($name === '') {
|
|
$errors[] = 'New school year name is required.';
|
|
}
|
|
|
|
if (! $startDate || ! $endDate) {
|
|
$errors[] = 'New school year start_date and end_date are required.';
|
|
|
|
return $errors;
|
|
}
|
|
|
|
if ($startDate >= $endDate) {
|
|
$errors[] = 'New school year start date must be before end date.';
|
|
}
|
|
|
|
$duplicateName = SchoolYear::query()
|
|
->where('name', $name)
|
|
->where('id', '!=', $currentYear->id)
|
|
->exists();
|
|
if ($duplicateName) {
|
|
$errors[] = sprintf('School year %s already exists.', $name);
|
|
}
|
|
|
|
$overlap = $this->hasDateOverlap($startDate, $endDate, (int) $currentYear->id);
|
|
|
|
if ($overlap) {
|
|
$errors[] = 'New school year dates overlap an existing school year.';
|
|
}
|
|
|
|
$otherActive = SchoolYear::query()
|
|
->where('status', SchoolYear::STATUS_ACTIVE)
|
|
->where('id', '!=', $currentYear->id)
|
|
->exists();
|
|
|
|
if ($otherActive) {
|
|
$errors[] = 'Another active school year already exists.';
|
|
}
|
|
|
|
return $errors;
|
|
}
|
|
|
|
private function hasDateOverlap(string $startDate, string $endDate, ?int $ignoreId = null): bool
|
|
{
|
|
if ($startDate === '' || $endDate === '') {
|
|
return false;
|
|
}
|
|
|
|
return SchoolYear::query()
|
|
->when($ignoreId !== null, fn ($query) => $query->where('id', '!=', $ignoreId))
|
|
->whereDate('start_date', '<=', $endDate)
|
|
->whereDate('end_date', '>=', $startDate)
|
|
->exists();
|
|
}
|
|
|
|
private function schoolYearHasOperationalRecords(string $schoolYear): bool
|
|
{
|
|
$tables = [
|
|
['table' => 'invoices', 'column' => 'school_year'],
|
|
['table' => 'enrollments', 'column' => 'school_year'],
|
|
['table' => 'student_class', 'column' => 'school_year'],
|
|
['table' => 'payments', 'column' => 'school_year'],
|
|
['table' => 'student_decisions', 'column' => 'school_year'],
|
|
['table' => 'classSection', 'column' => 'school_year'],
|
|
['table' => 'classes', 'column' => 'school_year'],
|
|
];
|
|
|
|
foreach ($tables as $mapping) {
|
|
if (! Schema::hasTable($mapping['table'])) {
|
|
continue;
|
|
}
|
|
if (DB::table($mapping['table'])->where($mapping['column'], $schoolYear)->exists()) {
|
|
return true;
|
|
}
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
private function buildPromotionPreview(string $currentYear, string $targetYear): array
|
|
{
|
|
$studentIds = collect(DB::table('student_class')->where('school_year', $currentYear)->pluck('student_id'))
|
|
->merge(DB::table('enrollments')->where('school_year', $currentYear)->pluck('student_id'))
|
|
->map(fn ($id) => (int) $id)
|
|
->unique()
|
|
->values();
|
|
|
|
$students = DB::table('students')
|
|
->whereIn('id', $studentIds->all())
|
|
->get()
|
|
->keyBy('id');
|
|
|
|
$assignments = DB::table('student_class')
|
|
->where('school_year', $currentYear)
|
|
->orderByDesc('updated_at')
|
|
->orderByDesc('id')
|
|
->get();
|
|
|
|
$assignmentByStudent = [];
|
|
foreach ($assignments as $assignment) {
|
|
$studentId = (int) $assignment->student_id;
|
|
if (! isset($assignmentByStudent[$studentId])) {
|
|
$assignmentByStudent[$studentId] = $assignment;
|
|
}
|
|
}
|
|
|
|
$classSections = DB::table('classSection')->get()->keyBy('class_section_id');
|
|
$classes = DB::table('classes')->get()->keyBy('id');
|
|
|
|
$enrollments = DB::table('enrollments')
|
|
->where('school_year', $currentYear)
|
|
->orderByDesc('updated_at')
|
|
->orderByDesc('id')
|
|
->get();
|
|
$enrollmentByStudent = [];
|
|
foreach ($enrollments as $enrollment) {
|
|
$studentId = (int) $enrollment->student_id;
|
|
if (! isset($enrollmentByStudent[$studentId])) {
|
|
$enrollmentByStudent[$studentId] = $enrollment;
|
|
}
|
|
}
|
|
|
|
$decisions = Schema::hasTable('student_decisions')
|
|
? DB::table('student_decisions')
|
|
->where('school_year', $currentYear)
|
|
->orderByDesc('updated_at')
|
|
->orderByDesc('id')
|
|
->get()
|
|
: collect();
|
|
$decisionByStudent = [];
|
|
foreach ($decisions as $decision) {
|
|
$studentId = (int) $decision->student_id;
|
|
if (! isset($decisionByStudent[$studentId])) {
|
|
$decisionByStudent[$studentId] = $decision;
|
|
}
|
|
}
|
|
|
|
$rows = [];
|
|
$summary = [
|
|
'total_students' => 0,
|
|
'promote' => 0,
|
|
'repeat' => 0,
|
|
'graduate' => 0,
|
|
'transfer' => 0,
|
|
'withdraw' => 0,
|
|
'hold' => 0,
|
|
];
|
|
|
|
foreach ($studentIds as $studentId) {
|
|
$student = $students->get($studentId);
|
|
if (! $student) {
|
|
continue;
|
|
}
|
|
|
|
$assignment = $assignmentByStudent[$studentId] ?? null;
|
|
$enrollment = $enrollmentByStudent[$studentId] ?? null;
|
|
$decision = $decisionByStudent[$studentId] ?? null;
|
|
$section = $assignment ? $classSections->get($assignment->class_section_id) : null;
|
|
$currentClass = $section ? $classes->get($section->class_id) : null;
|
|
$progression = $assignment
|
|
? $this->progression->resolveByCurrentClassSectionId((int) $assignment->class_section_id)
|
|
: null;
|
|
|
|
$decisionText = strtolower(trim((string) ($decision->decision ?? '')));
|
|
$score = $decision && isset($decision->year_score) ? (float) $decision->year_score : null;
|
|
$enrollmentStatus = strtolower(trim((string) ($enrollment->enrollment_status ?? '')));
|
|
$warnings = [];
|
|
|
|
$action = 'hold';
|
|
if (in_array($enrollmentStatus, ['withdrawn', 'refund pending', 'withdraw under review', 'denied'], true)) {
|
|
$action = 'withdraw';
|
|
} elseif (str_contains($decisionText, 'transfer')) {
|
|
$action = 'transfer';
|
|
} elseif (str_contains($decisionText, 'withdraw')) {
|
|
$action = 'withdraw';
|
|
} elseif (str_contains($decisionText, 'graduat')) {
|
|
$action = 'graduate';
|
|
} elseif (in_array($decisionText, ['retained', 'repeat', 'repeated', 'not promoted'], true) || ($score !== null && $score < 60)) {
|
|
$action = 'repeat';
|
|
} elseif ($this->isPassingSeventeenYearOld($student, $decisionText, $score)) {
|
|
$action = 'graduate';
|
|
} elseif (($progression['is_terminal'] ?? false) === true) {
|
|
$action = 'graduate';
|
|
} elseif ($decisionText !== '' || $score !== null) {
|
|
$action = 'promote';
|
|
}
|
|
|
|
$targetLevelId = null;
|
|
$targetLevelName = null;
|
|
if ($action === 'repeat') {
|
|
$targetLevelId = $progression['current_level_id'] ?? ($section->class_id ?? null);
|
|
$targetLevelName = $progression['current_level_name'] ?? ($currentClass->class_name ?? null);
|
|
} elseif ($action === 'promote') {
|
|
$targetLevelId = $progression['next_level_id'] ?? null;
|
|
$targetLevelName = $progression['next_level_name'] ?? null;
|
|
if ($this->isKindergartenAgeReadyForGradeOne($student, $currentClass, $section)) {
|
|
[$targetLevelId, $targetLevelName] = $this->resolveGradeOneTarget();
|
|
}
|
|
if ($targetLevelId === null) {
|
|
$action = 'hold';
|
|
$warnings[] = 'No next grade mapping exists for this student.';
|
|
}
|
|
}
|
|
|
|
if ($action === 'hold' && $warnings === []) {
|
|
$warnings[] = 'Missing promotion decision or score.';
|
|
}
|
|
|
|
[$targetClassSectionId, $targetClassSectionName] = $this->resolveTargetSection(
|
|
$targetLevelId,
|
|
$targetYear
|
|
);
|
|
|
|
$row = [
|
|
'student_id' => $studentId,
|
|
'student_name' => trim((string) ($student->firstname ?? '').' '.(string) ($student->lastname ?? '')),
|
|
'parent_id' => (int) ($student->parent_id ?? 0) ?: null,
|
|
'current_grade' => $progression['current_level_name'] ?? ($currentClass->class_name ?? null),
|
|
'current_class' => $section->class_section_name ?? null,
|
|
'promotion_action' => $action,
|
|
'target_grade' => $targetLevelName,
|
|
'target_class' => $targetClassSectionName,
|
|
'target_class_section_id' => $targetClassSectionId,
|
|
'warnings' => $warnings,
|
|
'decision' => $decision->decision ?? null,
|
|
'score' => $score,
|
|
];
|
|
|
|
$rows[] = $row;
|
|
$summary['total_students']++;
|
|
$summary[$action]++;
|
|
}
|
|
|
|
usort($rows, static fn (array $a, array $b) => strcmp($a['student_name'], $b['student_name']));
|
|
|
|
return [
|
|
'current_school_year' => $currentYear,
|
|
'target_school_year' => $targetYear,
|
|
'rows' => $rows,
|
|
'summary' => $summary,
|
|
];
|
|
}
|
|
|
|
private function isPassingSeventeenYearOld(object $student, string $decisionText, ?float $score): bool
|
|
{
|
|
$age = (int) ($student->age ?? 0);
|
|
if ($age !== 17) {
|
|
return false;
|
|
}
|
|
|
|
if ($score !== null) {
|
|
return $score >= 60;
|
|
}
|
|
|
|
return str_contains($decisionText, 'promot')
|
|
|| str_contains($decisionText, 'pass');
|
|
}
|
|
|
|
private function isKindergartenAgeReadyForGradeOne(object $student, ?object $currentClass, ?object $section): bool
|
|
{
|
|
$age = (int) ($student->age ?? 0);
|
|
if ($age + 1 < 6) {
|
|
return false;
|
|
}
|
|
|
|
$className = strtolower(trim((string) ($currentClass->class_name ?? '')));
|
|
$sectionName = strtolower(trim((string) ($section->class_section_name ?? '')));
|
|
|
|
return in_array($className, ['kg', 'kindergarten'], true)
|
|
|| in_array($sectionName, ['kg', 'kindergarten'], true);
|
|
}
|
|
|
|
private function resolveGradeOneTarget(): array
|
|
{
|
|
$target = DB::table('classes')
|
|
->whereIn(DB::raw('LOWER(class_name)'), ['1', 'grade 1', 'class 1'])
|
|
->orderByRaw("CASE WHEN class_name = '1' THEN 0 ELSE 1 END")
|
|
->orderBy('id')
|
|
->first();
|
|
|
|
return $target
|
|
? [(int) $target->id, (string) $target->class_name]
|
|
: [null, null];
|
|
}
|
|
|
|
private function buildParentBalanceRows(string $fromSchoolYear, string $toSchoolYear, bool $includeCreditOnlyRows = true): array
|
|
{
|
|
$invoiceQuery = $includeCreditOnlyRows
|
|
? $this->eligibleCarryoverInvoiceQuery($fromSchoolYear)
|
|
: DB::table('invoices')->where('school_year', $fromSchoolYear);
|
|
|
|
if ($includeCreditOnlyRows) {
|
|
$invoiceQuery->whereRaw('ABS(COALESCE(balance, 0)) > 0.01');
|
|
} else {
|
|
$invoiceQuery->whereRaw('COALESCE(balance, 0) > 0.01');
|
|
}
|
|
|
|
$invoices = $invoiceQuery->get(['id', 'parent_id', 'balance']);
|
|
|
|
$balancesByParent = [];
|
|
foreach ($invoices as $invoice) {
|
|
$parentId = (int) ($invoice->parent_id ?? 0);
|
|
if ($parentId <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$balance = round((float) ($invoice->balance ?? 0), 2);
|
|
$balancesByParent[$parentId] ??= [
|
|
'positive_unpaid_balance' => 0.0,
|
|
'credit_balance' => 0.0,
|
|
'positive_invoice_ids' => [],
|
|
'credit_invoice_ids' => [],
|
|
];
|
|
|
|
if ($balance > 0.01) {
|
|
$balancesByParent[$parentId]['positive_unpaid_balance'] += $balance;
|
|
$balancesByParent[$parentId]['positive_invoice_ids'][] = (int) $invoice->id;
|
|
} elseif ($balance < -0.01) {
|
|
$balancesByParent[$parentId]['credit_balance'] += abs($balance);
|
|
$balancesByParent[$parentId]['credit_invoice_ids'][] = (int) $invoice->id;
|
|
}
|
|
}
|
|
|
|
$rows = [];
|
|
$summary = [
|
|
'parents_with_positive_balance' => 0,
|
|
'parents_with_credit_balance' => 0,
|
|
'parents_with_net_non_zero_balance' => 0,
|
|
'existing_transfer_conflicts' => 0,
|
|
'total_positive_unpaid_balance' => 0.0,
|
|
'total_credit_balance' => 0.0,
|
|
'net_balance_impact' => 0.0,
|
|
// Deprecated aliases retained for older UI/tests during the migration.
|
|
'parents_with_non_zero_balance' => 0,
|
|
'total_old_unpaid_balance' => 0.0,
|
|
'net_balance_to_transfer' => 0.0,
|
|
];
|
|
|
|
foreach ($balancesByParent as $parentId => $balanceRow) {
|
|
$positive = round((float) $balanceRow['positive_unpaid_balance'], 2);
|
|
$credit = round((float) $balanceRow['credit_balance'], 2);
|
|
$net = round($positive - $credit, 2);
|
|
|
|
if ($positive < 0.01 && $credit < 0.01) {
|
|
continue;
|
|
}
|
|
|
|
$existing = $positive > 0.01
|
|
? ParentBalanceTransfer::query()
|
|
->where('parent_id', $parentId)
|
|
->where('from_school_year', $fromSchoolYear)
|
|
->where('to_school_year', $toSchoolYear)
|
|
->first()
|
|
: null;
|
|
|
|
if (! $includeCreditOnlyRows && $positive < 0.01) {
|
|
if ($credit > 0.01) {
|
|
$summary['parents_with_credit_balance']++;
|
|
$summary['total_credit_balance'] += $credit;
|
|
}
|
|
if (abs($net) > 0.01) {
|
|
$summary['parents_with_net_non_zero_balance']++;
|
|
}
|
|
|
|
continue;
|
|
}
|
|
|
|
$row = [
|
|
'parent_id' => $parentId,
|
|
'parent_name' => $this->resolveParentName($parentId),
|
|
'student_names' => $this->resolveStudentNames($parentId, $fromSchoolYear),
|
|
'unpaid_invoice_count' => count($balanceRow['positive_invoice_ids']),
|
|
'positive_unpaid_balance' => $positive,
|
|
'old_unpaid_balance' => $positive,
|
|
'old_unpaid_amount' => $positive,
|
|
'credit_balance' => $credit,
|
|
'net_balance' => $net,
|
|
'amount_to_transfer' => $positive,
|
|
'old_school_year' => $fromSchoolYear,
|
|
'new_school_year' => $toSchoolYear,
|
|
'new_old_balance_invoice_id' => $existing?->new_invoice_id ?? $existing?->old_balance_invoice_id ?? null,
|
|
'transfer_date' => optional($existing?->created_at)->toDateTimeString(),
|
|
'created_by' => $existing?->created_by,
|
|
'transfer_status' => $existing?->status ?? ($positive > 0.01 ? 'pending' : 'credit_reported'),
|
|
'existing_transfer_id' => $existing?->id,
|
|
'existing_transfer_conflict' => $existing !== null,
|
|
'positive_invoice_ids' => $balanceRow['positive_invoice_ids'],
|
|
'credit_invoice_ids' => $balanceRow['credit_invoice_ids'],
|
|
'source_invoice_ids' => $balanceRow['positive_invoice_ids'],
|
|
];
|
|
|
|
$rows[] = $row;
|
|
|
|
if ($positive > 0.01) {
|
|
$summary['parents_with_positive_balance']++;
|
|
$summary['total_positive_unpaid_balance'] += $positive;
|
|
}
|
|
if ($credit > 0.01) {
|
|
$summary['parents_with_credit_balance']++;
|
|
$summary['total_credit_balance'] += $credit;
|
|
}
|
|
if (abs($net) > 0.01) {
|
|
$summary['parents_with_net_non_zero_balance']++;
|
|
}
|
|
if ($row['existing_transfer_conflict']) {
|
|
$summary['existing_transfer_conflicts']++;
|
|
}
|
|
}
|
|
|
|
usort($rows, static fn (array $a, array $b) => $b['amount_to_transfer'] <=> $a['amount_to_transfer']);
|
|
|
|
$summary['total_positive_unpaid_balance'] = round($summary['total_positive_unpaid_balance'], 2);
|
|
$summary['total_credit_balance'] = round($summary['total_credit_balance'], 2);
|
|
$summary['net_balance_impact'] = round($summary['total_positive_unpaid_balance'] - $summary['total_credit_balance'], 2);
|
|
$summary['parents_with_non_zero_balance'] = $summary['parents_with_net_non_zero_balance'];
|
|
$summary['total_old_unpaid_balance'] = $summary['total_positive_unpaid_balance'];
|
|
$summary['net_balance_to_transfer'] = $summary['net_balance_impact'];
|
|
$summary['parent_count'] = $summary['parents_with_positive_balance'];
|
|
$summary['total_transferred'] = $summary['total_positive_unpaid_balance'];
|
|
$summary['total_credit'] = $summary['total_credit_balance'];
|
|
|
|
return [
|
|
'from_school_year' => $fromSchoolYear,
|
|
'to_school_year' => $toSchoolYear,
|
|
'rows' => $rows,
|
|
'summary' => $summary,
|
|
];
|
|
}
|
|
|
|
private function eligibleCarryoverInvoiceQuery(string $schoolYear)
|
|
{
|
|
return DB::table('invoices')
|
|
->where('school_year', $schoolYear)
|
|
->whereNotIn(DB::raw("LOWER(COALESCE(status, ''))"), [
|
|
'void',
|
|
'voided',
|
|
'cancelled',
|
|
'canceled',
|
|
'draft',
|
|
'pending',
|
|
'paid',
|
|
'full',
|
|
'full payment',
|
|
'complete',
|
|
'completed',
|
|
]);
|
|
}
|
|
|
|
private function resolveTargetSection(?int $targetLevelId, string $schoolYear): array
|
|
{
|
|
if ($targetLevelId === null || $targetLevelId <= 0) {
|
|
return [null, null];
|
|
}
|
|
|
|
$section = DB::table('classSection')
|
|
->where('class_id', $targetLevelId)
|
|
->where(function ($query) use ($schoolYear) {
|
|
$query->whereNull('school_year')
|
|
->orWhere('school_year', $schoolYear);
|
|
})
|
|
->orderBy('class_section_id')
|
|
->first();
|
|
|
|
if (! $section) {
|
|
return [null, null];
|
|
}
|
|
|
|
return [(int) $section->class_section_id, $section->class_section_name];
|
|
}
|
|
|
|
private function applyPromotions(array $rows, string $newSchoolYear, string $enrollmentDate, ?int $actorId): array
|
|
{
|
|
$semester = trim((string) (Configuration::getConfig('semester') ?? ''));
|
|
$counts = [
|
|
'promote' => 0,
|
|
'repeat' => 0,
|
|
'graduate' => 0,
|
|
'transfer' => 0,
|
|
'withdraw' => 0,
|
|
];
|
|
|
|
foreach ($rows as $row) {
|
|
$action = $row['promotion_action'];
|
|
if (! isset($counts[$action])) {
|
|
continue;
|
|
}
|
|
|
|
$counts[$action]++;
|
|
|
|
if (! in_array($action, ['promote', 'repeat'], true)) {
|
|
$this->logAudit(
|
|
$actorId,
|
|
'school_year_promotion_decision_applied',
|
|
'students',
|
|
(int) $row['student_id'],
|
|
null,
|
|
null,
|
|
['action' => $action, 'new_school_year' => $newSchoolYear]
|
|
);
|
|
|
|
continue;
|
|
}
|
|
|
|
$existingEnrollment = DB::table('enrollments')
|
|
->where('student_id', $row['student_id'])
|
|
->where('school_year', $newSchoolYear)
|
|
->first();
|
|
|
|
if (! $existingEnrollment) {
|
|
DB::table('enrollments')->insert([
|
|
'student_id' => $row['student_id'],
|
|
'class_section_id' => $row['target_class_section_id'],
|
|
'parent_id' => $row['parent_id'],
|
|
'enrollment_date' => $enrollmentDate,
|
|
'enrollment_status' => 'enrolled',
|
|
'withdrawal_date' => null,
|
|
'is_withdrawn' => 0,
|
|
'admission_status' => 'accepted',
|
|
'semester' => $semester !== '' ? $semester : null,
|
|
'school_year' => $newSchoolYear,
|
|
'created_at' => now(),
|
|
'updated_at' => now(),
|
|
]);
|
|
}
|
|
|
|
if ($row['target_class_section_id']) {
|
|
$existingAssignment = DB::table('student_class')
|
|
->where('student_id', $row['student_id'])
|
|
->where('school_year', $newSchoolYear)
|
|
->where('class_section_id', $row['target_class_section_id'])
|
|
->exists();
|
|
|
|
if (! $existingAssignment) {
|
|
DB::table('student_class')->insert([
|
|
'student_id' => $row['student_id'],
|
|
'class_section_id' => $row['target_class_section_id'],
|
|
'is_event_only' => 0,
|
|
'semester' => $semester !== '' ? $semester : 'Fall',
|
|
'school_year' => $newSchoolYear,
|
|
'created_at' => now(),
|
|
'updated_at' => now(),
|
|
'updated_by' => $actorId,
|
|
]);
|
|
}
|
|
}
|
|
|
|
if ($row['parent_id']) {
|
|
ParentAccount::query()->firstOrCreate(
|
|
['parent_id' => $row['parent_id'], 'school_year' => $newSchoolYear],
|
|
['opening_balance' => 0, 'current_balance' => 0]
|
|
);
|
|
}
|
|
|
|
$this->logAudit(
|
|
$actorId,
|
|
'school_year_promotion_decision_applied',
|
|
'students',
|
|
(int) $row['student_id'],
|
|
null,
|
|
null,
|
|
[
|
|
'action' => $action,
|
|
'new_school_year' => $newSchoolYear,
|
|
'target_class_section_id' => $row['target_class_section_id'],
|
|
]
|
|
);
|
|
}
|
|
|
|
return $counts;
|
|
}
|
|
|
|
private function applyBalanceTransfers(array $rows, string $fromSchoolYear, string $toSchoolYear, ?int $actorId): array
|
|
{
|
|
$counts = [
|
|
'transferred_parents' => 0,
|
|
'transferred_amount' => 0.0,
|
|
'credit_parents' => 0,
|
|
'credit_amount' => 0.0,
|
|
];
|
|
|
|
foreach ($rows as $row) {
|
|
$amount = round((float) $row['amount_to_transfer'], 2);
|
|
$credit = round((float) ($row['credit_balance'] ?? 0), 2);
|
|
if ($amount < 0.01) {
|
|
if ($credit > 0.01) {
|
|
$counts['credit_parents']++;
|
|
$counts['credit_amount'] += $credit;
|
|
}
|
|
continue;
|
|
}
|
|
|
|
$transfer = ParentBalanceTransfer::query()->where([
|
|
'parent_id' => $row['parent_id'],
|
|
'from_school_year' => $fromSchoolYear,
|
|
'to_school_year' => $toSchoolYear,
|
|
])->lockForUpdate()->first();
|
|
|
|
if ($transfer) {
|
|
throw new RuntimeException(sprintf(
|
|
'Parent %d already has a balance transfer for %s -> %s.',
|
|
$row['parent_id'],
|
|
$fromSchoolYear,
|
|
$toSchoolYear
|
|
));
|
|
}
|
|
|
|
$newAccount = ParentAccount::query()->firstOrCreate(
|
|
['parent_id' => $row['parent_id'], 'school_year' => $toSchoolYear],
|
|
['opening_balance' => 0, 'current_balance' => 0]
|
|
);
|
|
if (Schema::hasColumn('parent_accounts', 'school_year_id') && $newAccount->school_year_id === null) {
|
|
$newAccount->school_year_id = $this->schoolYearIdForName($toSchoolYear);
|
|
}
|
|
$newAccount->opening_balance = round((float) $newAccount->opening_balance + $amount, 2);
|
|
$newAccount->current_balance = round((float) $newAccount->current_balance + $amount, 2);
|
|
$newAccount->save();
|
|
|
|
$transferPayload = [
|
|
'parent_id' => $row['parent_id'],
|
|
'from_school_year' => $fromSchoolYear,
|
|
'to_school_year' => $toSchoolYear,
|
|
'amount' => $amount,
|
|
'status' => 'transferred',
|
|
'source_summary_json' => [
|
|
'source_invoice_ids' => $row['positive_invoice_ids'] ?? $row['source_invoice_ids'] ?? [],
|
|
'positive_invoice_ids' => $row['positive_invoice_ids'] ?? $row['source_invoice_ids'] ?? [],
|
|
'credit_invoice_ids' => $row['credit_invoice_ids'] ?? [],
|
|
'old_unpaid_balance' => $row['old_unpaid_balance'] ?? $amount,
|
|
'positive_unpaid_balance' => $row['positive_unpaid_balance'] ?? $amount,
|
|
'credit_balance' => $credit,
|
|
'net_balance' => $row['net_balance'] ?? ($amount - $credit),
|
|
],
|
|
'created_by' => $actorId,
|
|
];
|
|
if (Schema::hasColumn('parent_balance_transfers', 'source_summary')) {
|
|
$transferPayload['source_summary'] = $transferPayload['source_summary_json'];
|
|
}
|
|
$fromYearId = $this->schoolYearIdForName($fromSchoolYear);
|
|
$toYearId = $this->schoolYearIdForName($toSchoolYear);
|
|
if (Schema::hasColumn('parent_balance_transfers', 'from_school_year_id')) {
|
|
$transferPayload['from_school_year_id'] = $fromYearId;
|
|
}
|
|
if (Schema::hasColumn('parent_balance_transfers', 'to_school_year_id')) {
|
|
$transferPayload['to_school_year_id'] = $toYearId;
|
|
}
|
|
|
|
$transfer = ParentBalanceTransfer::query()->create($transferPayload);
|
|
|
|
$newInvoiceId = null;
|
|
if ($amount > 0) {
|
|
$invoicePayload = [
|
|
'parent_id' => $row['parent_id'],
|
|
'invoice_number' => sprintf('OB-%s-%d', str_replace('-', '', $toSchoolYear), $transfer->id),
|
|
'total_amount' => $amount,
|
|
'balance' => $amount,
|
|
'paid_amount' => 0,
|
|
'has_discount' => 0,
|
|
'issue_date' => now()->toDateString(),
|
|
'due_date' => null,
|
|
'status' => 'unpaid',
|
|
'description' => sprintf('Opening balance transferred from %s', $fromSchoolYear),
|
|
'school_year' => $toSchoolYear,
|
|
'created_at' => now(),
|
|
'updated_at' => now(),
|
|
'updated_by' => $actorId,
|
|
'semester' => Configuration::getConfig('semester'),
|
|
'balance_transfer_id' => $transfer->id,
|
|
];
|
|
if (Schema::hasColumn('invoices', 'invoice_type')) {
|
|
$invoicePayload['invoice_type'] = 'opening_balance';
|
|
}
|
|
if (Schema::hasColumn('invoices', 'school_year_id')) {
|
|
$invoicePayload['school_year_id'] = $toYearId;
|
|
}
|
|
$newInvoiceId = DB::table('invoices')->insertGetId($invoicePayload);
|
|
$transfer->new_invoice_id = $newInvoiceId;
|
|
$transfer->save();
|
|
|
|
$counts['transferred_parents']++;
|
|
$counts['transferred_amount'] += $amount;
|
|
if ($credit > 0.01) {
|
|
$counts['credit_parents']++;
|
|
$counts['credit_amount'] += $credit;
|
|
}
|
|
}
|
|
|
|
$this->logAudit(
|
|
$actorId,
|
|
'school_year_balance_transferred',
|
|
'parent_balance_transfers',
|
|
(int) $transfer->id,
|
|
null,
|
|
$transfer->fresh()?->toArray(),
|
|
['new_invoice_id' => $newInvoiceId]
|
|
);
|
|
}
|
|
|
|
$counts['transferred_amount'] = round($counts['transferred_amount'], 2);
|
|
$counts['credit_amount'] = round($counts['credit_amount'], 2);
|
|
|
|
return $counts;
|
|
}
|
|
|
|
private function countPendingPayments(string $schoolYear): int
|
|
{
|
|
if (! Schema::hasTable('payments')) {
|
|
return 0;
|
|
}
|
|
|
|
return DB::table('payments')
|
|
->where('school_year', $schoolYear)
|
|
->whereIn(DB::raw('LOWER(COALESCE(status, ""))'), ['pending', 'processing', 'draft', 'unallocated'])
|
|
->count();
|
|
}
|
|
|
|
private function countDraftInvoices(string $schoolYear): int
|
|
{
|
|
return DB::table('invoices')
|
|
->where('school_year', $schoolYear)
|
|
->whereIn(DB::raw('LOWER(COALESCE(status, ""))'), ['draft', 'pending'])
|
|
->count();
|
|
}
|
|
|
|
private function schoolYearIdForName(string $schoolYear): ?int
|
|
{
|
|
if (! Schema::hasTable('school_years')) {
|
|
return null;
|
|
}
|
|
|
|
$id = SchoolYear::query()->where('name', $schoolYear)->value('id');
|
|
|
|
return $id ? (int) $id : null;
|
|
}
|
|
|
|
private function countFinancialInconsistencies(string $schoolYear): int
|
|
{
|
|
return DB::table('invoices')
|
|
->where('school_year', $schoolYear)
|
|
->whereRaw('ABS(COALESCE(total_amount, 0) - COALESCE(paid_amount, 0) - COALESCE(balance, 0)) > 0.01')
|
|
->count();
|
|
}
|
|
|
|
private function countDuplicateParentAccounts(): int
|
|
{
|
|
if (! Schema::hasTable('parent_accounts')) {
|
|
return 0;
|
|
}
|
|
|
|
return DB::table('parent_accounts')
|
|
->select('parent_id', 'school_year')
|
|
->groupBy('parent_id', 'school_year')
|
|
->havingRaw('COUNT(*) > 1')
|
|
->count();
|
|
}
|
|
|
|
private function resolveParentName(int $parentId): ?string
|
|
{
|
|
$row = DB::table('users')->where('id', $parentId)->first();
|
|
if (! $row) {
|
|
return null;
|
|
}
|
|
|
|
return trim((string) ($row->firstname ?? '').' '.(string) ($row->lastname ?? ''));
|
|
}
|
|
|
|
private function resolveStudentNames(int $parentId, string $schoolYear): array
|
|
{
|
|
$studentIds = DB::table('enrollments')
|
|
->where('parent_id', $parentId)
|
|
->where('school_year', $schoolYear)
|
|
->pluck('student_id')
|
|
->map(fn ($id) => (int) $id)
|
|
->all();
|
|
|
|
if ($studentIds === []) {
|
|
$studentIds = DB::table('students')->where('parent_id', $parentId)->pluck('id')->map(fn ($id) => (int) $id)->all();
|
|
}
|
|
|
|
return DB::table('students')
|
|
->whereIn('id', $studentIds)
|
|
->get()
|
|
->map(fn ($student) => trim((string) ($student->firstname ?? '').' '.(string) ($student->lastname ?? '')))
|
|
->filter()
|
|
->values()
|
|
->all();
|
|
}
|
|
|
|
private function latestClosureAudit(int $schoolYearId): ?AuditLog
|
|
{
|
|
if (! Schema::hasTable('audit_logs')) {
|
|
return null;
|
|
}
|
|
|
|
return AuditLog::query()
|
|
->where('action', 'school_year_closed')
|
|
->where(function ($query) use ($schoolYearId) {
|
|
$query->where('record_id', $schoolYearId)
|
|
->orWhere(function ($nested) use ($schoolYearId) {
|
|
$nested->where('table_name', 'school_years')
|
|
->where('record_id', $schoolYearId);
|
|
});
|
|
})
|
|
->orderByDesc('created_at')
|
|
->orderByDesc('id')
|
|
->first();
|
|
}
|
|
|
|
private function resolveClosedTargetYearName(string $fromSchoolYear): string
|
|
{
|
|
if (Schema::hasTable('parent_balance_transfers')) {
|
|
$target = DB::table('parent_balance_transfers')
|
|
->where('from_school_year', $fromSchoolYear)
|
|
->whereNotNull('to_school_year')
|
|
->orderByDesc('created_at')
|
|
->value('to_school_year');
|
|
|
|
if (is_string($target) && trim($target) !== '') {
|
|
return trim($target);
|
|
}
|
|
}
|
|
|
|
return $this->resolver->suggestNextName($fromSchoolYear) ?? '';
|
|
}
|
|
|
|
private function transferTotalsForClosedYear(string $fromSchoolYear, ?string $toSchoolYear = null): array
|
|
{
|
|
$totals = [
|
|
'parents_with_unpaid_balances' => 0,
|
|
'total_unpaid_balance_transferred' => 0.0,
|
|
'parents_with_credit_balances' => 0,
|
|
'total_credit_carried_or_reported' => 0.0,
|
|
'total_credit_transferred' => 0.0,
|
|
'net_balance_impact' => 0.0,
|
|
];
|
|
|
|
if (! Schema::hasTable('parent_balance_transfers')) {
|
|
return $totals;
|
|
}
|
|
|
|
$columns = ['amount'];
|
|
if (Schema::hasColumn('parent_balance_transfers', 'source_summary_json')) {
|
|
$columns[] = 'source_summary_json';
|
|
}
|
|
if (Schema::hasColumn('parent_balance_transfers', 'source_summary')) {
|
|
$columns[] = 'source_summary';
|
|
}
|
|
|
|
$rows = DB::table('parent_balance_transfers')
|
|
->where('from_school_year', $fromSchoolYear)
|
|
->when($toSchoolYear !== null && trim($toSchoolYear) !== '', fn ($query) => $query->where('to_school_year', $toSchoolYear))
|
|
->get($columns);
|
|
|
|
foreach ($rows as $row) {
|
|
$amount = round((float) ($row->amount ?? 0), 2);
|
|
if ($amount > 0.01) {
|
|
$totals['parents_with_unpaid_balances']++;
|
|
$totals['total_unpaid_balance_transferred'] += $amount;
|
|
}
|
|
|
|
$sourceSummary = $this->decodeTransferSourceSummary($row);
|
|
$credit = round((float) ($sourceSummary['credit_balance'] ?? 0), 2);
|
|
if ($credit > 0.01) {
|
|
$totals['parents_with_credit_balances']++;
|
|
$totals['total_credit_carried_or_reported'] += $credit;
|
|
}
|
|
}
|
|
|
|
$totals['total_unpaid_balance_transferred'] = round($totals['total_unpaid_balance_transferred'], 2);
|
|
$totals['total_credit_carried_or_reported'] = round($totals['total_credit_carried_or_reported'], 2);
|
|
$totals['total_credit_transferred'] = $totals['total_credit_carried_or_reported'];
|
|
$totals['net_balance_impact'] = round(
|
|
$totals['total_unpaid_balance_transferred'] - $totals['total_credit_carried_or_reported'],
|
|
2
|
|
);
|
|
|
|
return $totals;
|
|
}
|
|
|
|
private function positiveInvoiceBalanceTotals(string $schoolYear): array
|
|
{
|
|
$rows = DB::table('invoices')
|
|
->where('school_year', $schoolYear)
|
|
->whereRaw('COALESCE(balance, 0) > 0.01')
|
|
->select('parent_id', DB::raw('SUM(COALESCE(balance, 0)) as total_balance'))
|
|
->groupBy('parent_id')
|
|
->get();
|
|
|
|
return [
|
|
'parents_with_unpaid_balances' => $rows->count(),
|
|
'total_unpaid_balance' => round((float) $rows->sum('total_balance'), 2),
|
|
];
|
|
}
|
|
|
|
private function decodeTransferSourceSummary(object $row): array
|
|
{
|
|
$raw = $row->source_summary_json ?? $row->source_summary ?? null;
|
|
if (is_array($raw)) {
|
|
return $raw;
|
|
}
|
|
if (! is_string($raw) || trim($raw) === '') {
|
|
return [];
|
|
}
|
|
|
|
$decoded = json_decode($raw, true);
|
|
|
|
return is_array($decoded) ? $decoded : [];
|
|
}
|
|
|
|
private function resolveUserName(int $userId): string|int|null
|
|
{
|
|
if ($userId <= 0) {
|
|
return null;
|
|
}
|
|
|
|
if (! Schema::hasTable('users')) {
|
|
return $userId;
|
|
}
|
|
|
|
$user = DB::table('users')->where('id', $userId)->first();
|
|
if (! $user) {
|
|
return $userId;
|
|
}
|
|
|
|
$name = trim((string) ($user->firstname ?? '').' '.(string) ($user->lastname ?? ''));
|
|
if ($name !== '') {
|
|
return $name;
|
|
}
|
|
|
|
$email = trim((string) ($user->email ?? ''));
|
|
|
|
return $email !== '' ? $email : $userId;
|
|
}
|
|
|
|
public function archive(int $id, ?int $actorId): array
|
|
{
|
|
$year = $this->findOrFail($id);
|
|
|
|
if ($year->status === SchoolYear::STATUS_ARCHIVED) {
|
|
throw ValidationException::withMessages([
|
|
'school_year' => ['This school year is already archived.'],
|
|
]);
|
|
}
|
|
|
|
$oldState = $year->toArray();
|
|
$year->status = SchoolYear::STATUS_ARCHIVED;
|
|
$year->is_current = false;
|
|
$year->save();
|
|
|
|
$this->logAudit(
|
|
$actorId,
|
|
'school_year_archived',
|
|
'school_years',
|
|
(int) $year->id,
|
|
$oldState,
|
|
$year->fresh()?->toArray(),
|
|
null
|
|
);
|
|
|
|
return $this->presentSchoolYear($year->fresh());
|
|
}
|
|
|
|
public function archiveByYearName(string $schoolYear, ?int $actorId): array
|
|
{
|
|
return $this->archive((int) $this->findByNameOrFail($schoolYear)->id, $actorId);
|
|
}
|
|
|
|
private function presentSchoolYear(?SchoolYear $year): ?array
|
|
{
|
|
if (! $year) {
|
|
return null;
|
|
}
|
|
|
|
return [
|
|
'id' => (int) $year->id,
|
|
'name' => $year->name,
|
|
'start_date' => optional($year->start_date)->toDateString(),
|
|
'end_date' => optional($year->end_date)->toDateString(),
|
|
'status' => $year->status,
|
|
'is_current' => (bool) $year->is_current,
|
|
'closed_at' => optional($year->closed_at)->toDateTimeString(),
|
|
'closed_by' => $year->closed_by,
|
|
];
|
|
}
|
|
|
|
private function logAudit(
|
|
?int $userId,
|
|
string $action,
|
|
?string $tableName,
|
|
?int $recordId,
|
|
?array $oldValue,
|
|
?array $newValue,
|
|
?array $metadata = null
|
|
): void {
|
|
if (! Schema::hasTable('audit_logs')) {
|
|
return;
|
|
}
|
|
|
|
AuditLog::query()->create([
|
|
'user_id' => $userId,
|
|
'action' => $action,
|
|
'table_name' => $tableName,
|
|
'record_id' => $recordId,
|
|
'old_value' => $oldValue,
|
|
'new_value' => $newValue,
|
|
'metadata' => $metadata,
|
|
'created_at' => now(),
|
|
]);
|
|
}
|
|
}
|