364 lines
14 KiB
PHP
364 lines
14 KiB
PHP
<?php
|
|
|
|
namespace App\Helpers;
|
|
|
|
use DateTime;
|
|
|
|
class TPADataCompareHelper2
|
|
{
|
|
// fields compared by default
|
|
public const COMPARE_FIELDS = ['name', 'dob', 'gender', 'relation'];
|
|
|
|
// candidate unique id fields from MediAssist / Excel — prefer these when present
|
|
public const MEDIASSIST_UNIQUE_IDS = [
|
|
'benefMediAssistID',
|
|
'benefSlNoasperInsurer',
|
|
'benefNIAPersonID'
|
|
];
|
|
|
|
/* -------------------------
|
|
Normalization utilities
|
|
------------------------- */
|
|
|
|
public static function parseDateToYmd(?string $date): ?string
|
|
{
|
|
if ($date === null || $date === '') return null;
|
|
$formats = ['d/m/Y H:i:s','d/m/Y','Y-m-d H:i:s','Y-m-d','d-m-Y','d-m-Y H:i:s'];
|
|
foreach ($formats as $f) {
|
|
$dt = DateTime::createFromFormat($f, $date);
|
|
if ($dt !== false) return $dt->format('Y-m-d');
|
|
}
|
|
$ts = strtotime($date);
|
|
return $ts ? date('Y-m-d', $ts) : null;
|
|
}
|
|
|
|
public static function normalizeName(?string $name): ?string
|
|
{
|
|
if ($name === null) return null;
|
|
$s = trim($name);
|
|
// $s = preg_replace('/\.+/', ' ', $s);
|
|
// $s = preg_replace('/\s+/', ' ', $s);
|
|
return mb_strtolower($s);
|
|
}
|
|
|
|
public static function normalizeEmpId(?string $id): ?string
|
|
{
|
|
if ($id === null) return null;
|
|
$s = trim($id);
|
|
// $s = preg_replace('/[\s\-\/]/', '', $s);
|
|
// $s = preg_replace('/[^\p{L}\p{N}]/u', '', $s);
|
|
return $s === '' ? null : $s;
|
|
}
|
|
|
|
public static function normalizeGender(?string $g): ?string
|
|
{
|
|
if ($g === null) return null;
|
|
$u = strtoupper(trim($g));
|
|
if (in_array($u, ['M','MALE'])) return 'M';
|
|
if (in_array($u, ['F','FEMALE'])) return 'F';
|
|
return $u;
|
|
}
|
|
|
|
public static function normalizeRelation(?string $r): ?string
|
|
{
|
|
if ($r === null) return null;
|
|
return strtoupper(trim($r));
|
|
}
|
|
|
|
/* -------------------------------------
|
|
Normalize rows -> canonical structure
|
|
Returns array of items (not keyed), each item contains:
|
|
- 'pk' (DB primary key or null)
|
|
- 'emp_id' (employee code)
|
|
- 'name','dob','gender','relation' (normalized)
|
|
- '__raw' original row
|
|
- '__rowid' generated unique row id for API rows (if needed)
|
|
------------------------------------- */
|
|
public static function normalizeRows(array $rows, array $keyMap, ?string $pkKey = null): array
|
|
{
|
|
$out = [];
|
|
$idx = 0;
|
|
foreach ($rows as $r) {
|
|
$item = [
|
|
'pk' => ($pkKey && isset($r[$pkKey])) ? $r[$pkKey] : null,
|
|
'__raw' => $r,
|
|
'__rowid' => null,
|
|
];
|
|
|
|
// fill canonical fields
|
|
foreach ($keyMap as $canonical => $candidates) {
|
|
$val = null;
|
|
foreach ($candidates as $k) {
|
|
if (is_array($r) && array_key_exists($k, $r) && $r[$k] !== '') {
|
|
$val = $r[$k];
|
|
break;
|
|
}
|
|
}
|
|
switch ($canonical) {
|
|
case 'emp_id': $item['emp_id'] = self::normalizeEmpId((string)$val); break;
|
|
case 'name': $item['name'] = self::normalizeName((string)$val); break;
|
|
case 'dob': $item['dob'] = self::parseDateToYmd((string)$val); break;
|
|
case 'gender': $item['gender'] = self::normalizeGender((string)$val); break;
|
|
case 'relation': $item['relation'] = self::normalizeRelation((string)$val); break;
|
|
default: $item[$canonical] = $val;
|
|
}
|
|
}
|
|
|
|
// create a stable row id (for API rows without PK) so they remain unique
|
|
$item['__rowid'] = $item['pk'] ?? ('api_row_' . $idx++);
|
|
|
|
$out[] = $item;
|
|
}
|
|
|
|
return $out;
|
|
}
|
|
|
|
/* -------------------------
|
|
Build grouped index by emp_code:
|
|
returns [ emp_code => [ item1, item2, ... ] ]
|
|
Use '__no_emp' key for rows without emp_id
|
|
------------------------- */
|
|
public static function groupByEmpCode(array $items): array
|
|
{
|
|
$groups = [];
|
|
foreach ($items as $it) {
|
|
$key = $it['emp_id'] ?? '__no_emp';
|
|
if ($key === null || $key === '') $key = '__no_emp';
|
|
$groups[$key][] = $it;
|
|
}
|
|
return $groups;
|
|
}
|
|
|
|
/* -------------------------
|
|
Find a match for a DB row inside a mediassist group (array of items).
|
|
Matching priority:
|
|
1) unique ids present in mediassist (if db raw has any of those too)
|
|
2) exact name + dob
|
|
3) exact name + relation
|
|
4) fuzzy name + dob (threshold %)
|
|
If found, returns index of matched item in $group array; else null.
|
|
Note: $group is passed by reference so matched item can be removed by caller.
|
|
------------------------- */
|
|
public static function findMatchInGroup(array $dbRow, array $group, int $fuzzyThreshold = 85): ?int
|
|
{
|
|
// 1) match on unique ids if DB row contains any of those (rare)
|
|
// foreach (self::MEDIASSIST_UNIQUE_IDS as $uidField) {
|
|
// $dbVal = $dbRow['__raw'][$uidField] ?? null;
|
|
// if ($dbVal) {
|
|
// foreach ($group as $i => $gItem) {
|
|
// $gVal = $gItem['__raw'][$uidField] ?? null;
|
|
// if ($gVal && (string)$gVal === (string)$dbVal) return $i;
|
|
// }
|
|
// }
|
|
// }
|
|
|
|
// 2) match exact name + dob (both normalized)
|
|
if (!empty($dbRow['name']) && !empty($dbRow['dob'])) {
|
|
foreach ($group as $i => $gItem) {
|
|
if (!empty($gItem['name']) && !empty($gItem['dob'])
|
|
&& $dbRow['name'] === $gItem['name']
|
|
&& $dbRow['dob'] === $gItem['dob']) {
|
|
return $i;
|
|
}
|
|
}
|
|
}
|
|
|
|
// 3) match exact name + relation (if dob missing or unreliable)
|
|
if (!empty($dbRow['name']) && !empty($dbRow['relation'])) {
|
|
foreach ($group as $i => $gItem) {
|
|
if (!empty($gItem['name']) && !empty($gItem['relation'])
|
|
&& $dbRow['name'] === $gItem['name']
|
|
&& $dbRow['relation'] === $gItem['relation']) {
|
|
return $i;
|
|
}
|
|
}
|
|
}
|
|
|
|
// 4) fuzzy name + dob (if dob present)
|
|
if ($fuzzyThreshold > 0 && !empty($dbRow['name'])) {
|
|
foreach ($group as $i => $gItem) {
|
|
if (empty($gItem['name'])) continue;
|
|
$score = 0.0;
|
|
similar_text($dbRow['name'], $gItem['name'], $score); // percent
|
|
$dobMatches = (!empty($dbRow['dob']) && !empty($gItem['dob']) && $dbRow['dob'] === $gItem['dob']);
|
|
// require DOB match OR higher threshold if dob missing
|
|
if ($dobMatches && $score >= max(60, $fuzzyThreshold - 10)) { // slightly relaxed
|
|
return $i;
|
|
}
|
|
if (!$dobMatches && $score >= $fuzzyThreshold) {
|
|
// if names are very similar even w/o dob, accept (cautious)
|
|
return $i;
|
|
}
|
|
}
|
|
}
|
|
|
|
// no match
|
|
return null;
|
|
}
|
|
|
|
/* -------------------------
|
|
Main comparison routine (grouped matching)
|
|
Options:
|
|
- fuzzy_threshold (default 85)
|
|
- try_global_search_if_emp_missing (bool, default false)
|
|
------------------------- */
|
|
public static function compareDbVsMediassistGrouped(
|
|
array $dbRows,
|
|
array $mediassistAPIdata,
|
|
string $dbPkKey = 'id',
|
|
array $options = [],
|
|
array $dbKeyMap = null,
|
|
array $mediassistKeyMap = null,
|
|
): array {
|
|
|
|
|
|
$dbKeyMap = [
|
|
'emp_id' => ['emp_code'],
|
|
'name' => ['name'],
|
|
'dob' => ['dob'],
|
|
'gender' => ['gender'],
|
|
'relation' => ['relationship'],
|
|
];
|
|
|
|
$mediassistKeyMap = [
|
|
'emp_id' => ['priBenefEmpCode'],
|
|
'name' => ['benefName', 'priBeneficiaryName'],
|
|
'dob' => ['benefDOB'],
|
|
'gender' => ['benefSex'],
|
|
'relation' => ['relName'],
|
|
];
|
|
|
|
|
|
$fuzzyThreshold = $options['fuzzy_threshold'] ?? 85;
|
|
$tryGlobalSearch = $options['try_global_search_if_emp_missing'] ?? false;
|
|
|
|
// normalize to lists
|
|
$normDb = self::normalizeRows($dbRows, $dbKeyMap, $dbPkKey);
|
|
$normApi = self::normalizeRows($mediassistAPIdata, $mediassistKeyMap, null);
|
|
// dd($normApi);
|
|
// group by emp_code
|
|
$dbGroups = self::groupByEmpCode($normDb);
|
|
$apiGroups = self::groupByEmpCode($normApi);
|
|
|
|
// prepare report
|
|
$report = [
|
|
'summary' => [
|
|
'total_db' => count($normDb),
|
|
'total_mediassist' => count($normApi),
|
|
'matched' => 0,
|
|
'mismatched' => 0,
|
|
'only_in_db' => 0,
|
|
'only_in_mediassist' => 0,
|
|
],
|
|
'exact_matches' => [],
|
|
'field_mismatches' => [],
|
|
'only_in_db' => [],
|
|
'only_in_mediassist' => [],
|
|
];
|
|
|
|
// For each db group (emp_code), try match each dbRow with api group entries
|
|
foreach ($dbGroups as $empCode => $dbItems) {
|
|
$apiItems = $apiGroups[$empCode] ?? [];
|
|
|
|
// We'll mutate a local copy of apiItems to mark matched items (so we can remove)
|
|
$remainingApi = $apiItems;
|
|
|
|
foreach ($dbItems as $dbRow) {
|
|
$matchedIndex = null;
|
|
if (!empty($empCode) && !empty($remainingApi)) {
|
|
$matchedIndex = self::findMatchInGroup($dbRow, $remainingApi, $fuzzyThreshold);
|
|
}
|
|
|
|
// If empCode not present in API and global search allowed, try searching all API rows (costly)
|
|
if ($matchedIndex === null && $tryGlobalSearch && empty($apiItems)) {
|
|
// attempt global search across all api groups (can be heavy)
|
|
foreach ($apiGroups as $gKey => $gList) {
|
|
$mi = self::findMatchInGroup($dbRow, $gList, $fuzzyThreshold);
|
|
if ($mi !== null) {
|
|
// found in another group
|
|
$matchedIndex = $mi;
|
|
// use that group as remainingApi reference so we can remove matched later
|
|
$remainingApi = $apiGroups[$gKey];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($matchedIndex !== null) {
|
|
// found match — use that api row
|
|
$apiMatch = $remainingApi[$matchedIndex];
|
|
|
|
// compare fields
|
|
$diff = [];
|
|
foreach (self::COMPARE_FIELDS as $f) {
|
|
$vDb = $dbRow[$f] ?? null;
|
|
$vApi = $apiMatch[$f] ?? null;
|
|
if ($vDb !== $vApi) {
|
|
$diff[$f] = ['db' => $vDb, 'mediassist' => $vApi];
|
|
}
|
|
}
|
|
|
|
if (empty($diff)) {
|
|
$report['exact_matches'][] = [
|
|
'pk' => $dbRow['pk'],
|
|
'emp_id' => $dbRow['emp_id'],
|
|
'db_rowid' => $dbRow['__rowid'],
|
|
'api_rowid' => $apiMatch['__rowid'],
|
|
];
|
|
$report['summary']['matched']++;
|
|
} else {
|
|
$report['field_mismatches'][] = [
|
|
'pk' => $dbRow['pk'],
|
|
'emp_id' => $dbRow['emp_id'],
|
|
'db_rowid' => $dbRow['__rowid'],
|
|
'api_rowid' => $apiMatch['__rowid'],
|
|
'differences' => $diff
|
|
];
|
|
$report['summary']['mismatched']++;
|
|
}
|
|
|
|
// remove matched api item from remainingApi (so it's not matched again)
|
|
array_splice($remainingApi, $matchedIndex, 1);
|
|
} else {
|
|
// no match found for this dbRow inside api group => only_in_db
|
|
$report['only_in_db'][] = [
|
|
'pk' => $dbRow['pk'],
|
|
'emp_id' => $dbRow['emp_id'],
|
|
'db_rowid' => $dbRow['__rowid'],
|
|
'data' => $dbRow
|
|
];
|
|
}
|
|
} // end each dbRow
|
|
|
|
// after processing all dbRows in this empCode group, any remainingApi are only_in_mediassist
|
|
foreach ($remainingApi as $leftApi) {
|
|
$report['only_in_mediassist'][] = [
|
|
'pk' => null,
|
|
'emp_id' => $leftApi['emp_id'],
|
|
'api_rowid' => $leftApi['__rowid'],
|
|
'data' => $leftApi
|
|
];
|
|
}
|
|
} // end each emp group
|
|
|
|
// Also handle any api groups whose emp_code does not exist in DB at all
|
|
foreach ($apiGroups as $empCode => $apiItems) {
|
|
if (isset($dbGroups[$empCode])) continue; // already handled
|
|
foreach ($apiItems as $ai) {
|
|
$report['only_in_mediassist'][] = [
|
|
'pk' => null,
|
|
'emp_id' => $ai['emp_id'],
|
|
'api_rowid' => $ai['__rowid'],
|
|
'data' => $ai
|
|
];
|
|
}
|
|
}
|
|
|
|
// summary counts
|
|
$report['summary']['only_in_db'] = count($report['only_in_db']);
|
|
$report['summary']['only_in_mediassist'] = count($report['only_in_mediassist']);
|
|
|
|
return $report;
|
|
}
|
|
}
|