133 lines
6.0 KiB
PHP
133 lines
6.0 KiB
PHP
<?php
|
|
require __DIR__.'/../vendor/autoload.php';
|
|
$app = require_once __DIR__.'/../bootstrap/app.php';
|
|
$kernel = $app->make(Illuminate\Contracts\Console\Kernel::class);
|
|
$kernel->bootstrap();
|
|
|
|
use Illuminate\Support\Facades\DB;
|
|
use App\Services\PayrollService;
|
|
use App\Models\User;
|
|
|
|
$payrollService = new PayrollService();
|
|
|
|
// 1. Get current employees
|
|
$currentEmployees = DB::table('employees')
|
|
->leftJoin('users', 'employees.user_id', '=', 'users.id')
|
|
->leftJoin('employee_salaries', 'employees.user_id', '=', 'employee_salaries.employee_id')
|
|
->select('employees.id as emp_id', 'users.name', 'users.email', 'employees.employee_id as custom_employee_id', 'employees.user_id', 'employee_salaries.pay_frequency', 'employee_salaries.basic_salary')
|
|
->get();
|
|
|
|
$startDate = '2026-01-11';
|
|
$endDate = '2026-01-25';
|
|
|
|
echo "=========================================================================\n";
|
|
echo "AUDITING LEGACY PAYROLL VS COMPLIANT PAYROLLSERVICE ($startDate to $endDate)\n";
|
|
echo "=========================================================================\n";
|
|
|
|
$headers = ['Employee', 'Freq', 'Metric', 'Legacy (Stored)', 'Computed', 'Diff'];
|
|
$rows = [];
|
|
|
|
try {
|
|
$legacyUsers = DB::connection('legacy')->table('users')->get();
|
|
|
|
foreach ($legacyUsers as $lUser) {
|
|
// Find match in current
|
|
$match = $currentEmployees->first(function($item) use ($lUser) {
|
|
return strtolower($item->email) === strtolower($lUser->email) || strtolower($item->name) === strtolower($lUser->name);
|
|
});
|
|
|
|
if (!$match) {
|
|
continue;
|
|
}
|
|
|
|
// Get legacy payroll record for this period
|
|
$legacyRecord = DB::connection('legacy')->table('payroll_records')
|
|
->join('payroll_periods', 'payroll_records.payroll_period_id', '=', 'payroll_periods.id')
|
|
->where('payroll_records.user_id', $lUser->id)
|
|
->where('payroll_periods.start_date', $startDate)
|
|
->where('payroll_periods.end_date', $endDate)
|
|
->select('payroll_records.*')
|
|
->first();
|
|
|
|
if (!$legacyRecord) {
|
|
continue;
|
|
}
|
|
|
|
// Run compliant PayrollService calculation
|
|
$computed = $payrollService->calculateForPeriod(
|
|
\App\Models\Employee::where('user_id', $match->user_id)->first(),
|
|
$startDate,
|
|
$endDate
|
|
);
|
|
|
|
// Fetch basic salary from items
|
|
$legacyBasicItem = DB::connection('legacy')->table('payroll_record_items')
|
|
->where('payroll_record_id', $legacyRecord->id)
|
|
->where('salary_component_name', 'Basic Salary')
|
|
->first();
|
|
$legacyBasic = $legacyBasicItem ? (float)$legacyBasicItem->amount : 0.0;
|
|
|
|
// Resolve computed deductions
|
|
$computedSSS = 0.0;
|
|
$computedPH = 0.0;
|
|
$computedPI = 0.0;
|
|
$computedTax = 0.0;
|
|
foreach ($computed['deductions'] as $ded) {
|
|
if (strpos($ded['name'], 'SSS') !== false) $computedSSS = (float)$ded['amount'];
|
|
if (strpos($ded['name'], 'PhilHealth') !== false) $computedPH = (float)$ded['amount'];
|
|
if (strpos($ded['name'], 'Pag-IBIG') !== false) $computedPI = (float)$ded['amount'];
|
|
if (strpos($ded['name'], 'Tax') !== false) $computedTax = (float)$ded['amount'];
|
|
}
|
|
|
|
$metrics = [
|
|
'Basic Salary' => [$legacyBasic, (float)$computed['basic_salary']],
|
|
'Gross Pay' => [(float)$legacyRecord->gross_earning, (float)$computed['total_earnings']],
|
|
'Deductions' => [(float)$legacyRecord->total_deduction, (float)$computed['total_deductions']],
|
|
'Net Pay' => [(float)$legacyRecord->net_pay, (float)$computed['net_pay']],
|
|
'SSS (EE)' => [(float)$legacyRecord->sss_contribution_employee, $computedSSS],
|
|
'PhilHealth (EE)' => [(float)$legacyRecord->philhealth_contribution_employee, $computedPH],
|
|
'Pag-IBIG (EE)' => [(float)$legacyRecord->pagibig_contribution_employee, $computedPI],
|
|
'Tax Withheld' => [(float)$legacyRecord->tax_withheld, $computedTax],
|
|
];
|
|
|
|
echo "\nEmployee: {$match->name} ({$match->pay_frequency})\n";
|
|
echo " Legacy Roster Info: Days Worked: {$legacyRecord->days_worked} | Rendered Hours: {$legacyRecord->total_rendered_hours} | Absents: {$legacyRecord->absent_days}\n";
|
|
echo " Computed Info: Days Worked: {$computed['summary']['days_worked']} | Rendered Hours: {$computed['summary']['rendered_hours']} | Absents: {$computed['summary']['absences']}\n";
|
|
|
|
foreach ($metrics as $metric => $vals) {
|
|
$legacyVal = $vals[0];
|
|
$computedVal = $vals[1];
|
|
$diff = $computedVal - $legacyVal;
|
|
|
|
$status = abs($diff) > 0.05 ? "❌ MISMATCH" : "✅ OK";
|
|
printf(" %-18s | Legacy: %10.2f | Computed: %10.2f | Diff: %10.2f | %s\n",
|
|
$metric, $legacyVal, $computedVal, $diff, $status);
|
|
|
|
if (abs($diff) > 0.05) {
|
|
$rows[] = [
|
|
'employee' => $match->name,
|
|
'frequency' => $match->pay_frequency,
|
|
'metric' => $metric,
|
|
'legacy' => $legacyVal,
|
|
'computed' => $computedVal,
|
|
'diff' => $diff
|
|
];
|
|
}
|
|
}
|
|
}
|
|
} catch (\Exception $e) {
|
|
echo "Error during audit: " . $e->getMessage() . "\n" . $e->getTraceAsString() . "\n";
|
|
}
|
|
|
|
echo "\n=========================================================================\n";
|
|
echo "SUMMARY OF DETECTED INCONSISTENCIES:\n";
|
|
echo "=========================================================================\n";
|
|
if (count($rows) === 0) {
|
|
echo "No significant discrepancies found! All calculations match. ✅\n";
|
|
} else {
|
|
foreach ($rows as $row) {
|
|
printf(" - %-25s (%-12s): %-15s | Legacy: %10.2f | Computed: %10.2f | Diff: %10.2f\n",
|
|
$row['employee'], $row['frequency'], $row['metric'], $row['legacy'], $row['computed'], $row['diff']);
|
|
}
|
|
}
|