nhance/app/Helpers/TPADataCompareHelper.php

<?php

namespace App\Helpers;

use DateTime;

class TPADataCompareHelper
{
    /** Common fields to compare */
    public const COMPARE_FIELDS = ['name', 'dob', 'gender', 'relation'];

    /* =========================
       Normalization utilities
       ========================= */

    public static function parseDateToYmd(?string $date): ?string
    {
        if (empty($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',
        ];

        foreach ($formats as $format) {
            $dt = DateTime::createFromFormat($format, $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;
        }

        $name = trim($name);
        // $name = preg_replace('/\.+/', ' ', $name);
        // $name = preg_replace('/\s+/', ' ', $name);
        return mb_strtolower($name);
    }

    public static function normalizeEmpId(?string $empId): ?string
    {
        if ($empId === null) {
            return null;
        }

        $empId = trim($empId);
        $empId = preg_replace('/[\s\-\/]/', '', $empId);
        return $empId === '' ? null : $empId;
    }

    public static function normalizeGender(?string $gender): ?string
    {
        if ($gender === null) {
            return null;
        }

        $g = strtoupper(trim($gender));
        return in_array($g, ['M', 'MALE']) ? 'M'
             : (in_array($g, ['F', 'FEMALE']) ? 'F' : $g);
    }

    public static function normalizeRelation(?string $relation): ?string
    {
        return $relation === null ? null : strtoupper(trim($relation));
    }

    /* =========================
       Core normalization
       ========================= */

    public static function normalizeEmployees(
        array $rows,
        array $keyMap,
        ?string $pkKey = null
    ): array {
        $normalized = [];
        // print_r($rows);die();
        foreach ($rows as $index => $row) {
            // echo "Normalizing record #" . ($index + 1) . "\n";
            $item = [
                'pk' => $pkKey && isset($row[$pkKey]) ? $row[$pkKey] : null,
                '__raw' => $row,
            ];

            foreach ($keyMap as $canonical => $possibleKeys) {
                $value = null;

                foreach ($possibleKeys as $key) {
                    if (isset($row[$key]) && $row[$key] !== '') {
                        $value = $row[$key];
                        break;
                    }
                }

                switch ($canonical) {
                    case 'emp_id':
                        $item['emp_id'] = self::normalizeEmpId($value);
                        break;
                    case 'name':
                        $item['name'] = self::normalizeName($value);
                        break;
                    case 'dob':
                        $item['dob'] = self::parseDateToYmd($value);
                        break;
                    case 'gender':
                        $item['gender'] = self::normalizeGender($value);
                        break;
                    case 'relation':
                        $item['relation'] = self::normalizeRelation($value);
                        break;
                    default:
                        $item[$canonical] = $value;
                }
            }

            if (!empty($item['pk'])) {
                $normalized[$item['pk']] = $item;
            } else {
                $normalized['__no_emp_' . $index] = $item;
            }

             // echo "Normalizing record #" . ($index + 1) . "\n";
        }

        return $normalized;
    }

    /* =========================
       Main comparison
       ========================= */

    public static function compareDbVsMediassist(
        array $dbData,
        array $mediassistAPIdata
    ): array {
        // echo count($dbData) . " records in DB data\n";die();
        $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'],
        ];

        $A = self::normalizeEmployees($dbData, $dbKeyMap, 'id');
         
        $B = self::normalizeEmployees($mediassistAPIdata, $mediassistKeyMap);
       dd($B);
        $report = [
            'summary' => [
                'total_db' => count($A),
                'total_mediassist' => count($B),
                'matched' => 0,
                'mismatched' => 0,
                'only_in_db' => 0,
                'only_in_mediassist' => 0,
            ],
            'exact_matches' => [],
            'field_mismatches' => [],
            'only_in_db' => [],
            'only_in_mediassist' => [],
        ];

        foreach ($A as $empId => $rowA) {

            if (!isset($B[$empId])) {
                $report['only_in_db'][] = [
                    'pk' => $rowA['pk'],
                    'emp_id' => $empId,
                    'data' => $rowA,
                ];
                continue;
            }

            $rowB = $B[$empId];
            $diff = [];

            foreach (self::COMPARE_FIELDS as $field) {
                if (($rowA[$field] ?? null) !== ($rowB[$field] ?? null)) {
                    $diff[$field] = [
                        'db' => $rowA[$field] ?? null,
                        'mediassist' => $rowB[$field] ?? null,
                    ];
                }
            }

            if (empty($diff)) {
                $report['exact_matches'][] = [
                    'pk' => $rowA['pk'],
                    'emp_id' => $empId,
                ];
                $report['summary']['matched']++;
            } else {
                $report['field_mismatches'][] = [
                    'pk' => $rowA['pk'],
                    'emp_id' => $empId,
                    'differences' => $diff,
                ];
                $report['summary']['mismatched']++;
            }

            unset($B[$empId]);
        }

        foreach ($B as $empId => $rowB) {
            $report['only_in_mediassist'][] = [
                'pk' => null,
                'emp_id' => $empId,
                'data' => $rowB,
            ];
        }

        $report['summary']['only_in_db'] = count($report['only_in_db']);
        $report['summary']['only_in_mediassist'] = count($report['only_in_mediassist']);

        return $report;
    }
}