for "M1 Status Summary", i want to count "completed", "not enrolled", "enrolled" from column "M1 Status", and also keep "Records Removed"
124763e verified | <html lang="en"> | |
| <head> | |
| <meta charset="UTF-8"> | |
| <meta name="viewport" content="width=device-width, initial-scale=1.0"> | |
| <title>AITP Enrolment CrossChecker | Centre for Learning and Teaching</title> | |
| <link rel="icon" type="image/x-icon" href="/static/favicon.ico"> | |
| <script src="https://cdn.tailwindcss.com"></script> | |
| <script src="https://cdn.jsdelivr.net/npm/feather-icons/dist/feather.min.js"></script> | |
| <script src="https://unpkg.com/feather-icons"></script> | |
| <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script> | |
| <!-- Fav Icon --> | |
| <link rel="icon" href="img/favicon.ico" type="image/x-icon"> | |
| <script> | |
| tailwind.config = { | |
| theme: { | |
| extend: { | |
| colors: { | |
| primary: { | |
| 50: '#f0f9ff', | |
| 100: '#e0f2fe', | |
| 200: '#bae6fd', | |
| 300: '#7dd3fc', | |
| 400: '#38bdf8', | |
| 500: '#0ea5e9', | |
| 600: '#0284c7', | |
| 700: '#0369a1', | |
| 800: '#075985', | |
| 900: '#0c4a6e', | |
| }, | |
| secondary: { | |
| 50: '#fdf4ff', | |
| 100: '#fae8ff', | |
| 200: '#f5d0fe', | |
| 300: '#f0abfc', | |
| 400: '#e879f9', | |
| 500: '#d946ef', | |
| 600: '#c026d3', | |
| 700: '#a21caf', | |
| 800: '#86198f', | |
| 900: '#701a75', | |
| } | |
| } | |
| } | |
| } | |
| } | |
| </script> | |
| </head> | |
| <body class="bg-gray-50 min-h-screen"> | |
| <!-- Main Content --> | |
| <div class="max-w-4xl mx-auto py-12 px-4 sm:px-6 lg:px-8"> | |
| <!-- Header --> | |
| <div class="text-center mb-12"> | |
| <h1 class="text-4xl font-bold text-gray-900 mb-4">AITP Enrolment CrossChecker </h1> | |
| <p class="text-lg text-gray-600 max-w-2xl mx-auto"> | |
| Upload your Admin & Teaching Staff Enrolment List and Teaching Staff List to analyse data matches and unique records. | |
| </p> | |
| </div> | |
| <!-- Upload Sections --> | |
| <div class="grid md:grid-cols-3 gap-8 mb-12"> | |
| <!-- File 1 Upload --> | |
| <div class="bg-white rounded-2xl shadow-lg p-6 border-2 border-dashed border-primary-200 hover:border-primary-400 transition-colors"> | |
| <div class="text-center"> | |
| <i data-feather="file-text" class="w-12 h-12 text-primary-500 mx-auto mb-4"></i> | |
| <h3 class="text-xl font-semibold text-gray-900 mb-2">File 1: Enrolment List</h3> | |
| <p class="text-gray-600 mb-4">Admin and Teaching Staff Enrolment List<br>(.xlsx, .xls)</p> | |
| <input type="file" id="file1" accept=".xlsx,.xls" class="hidden"> | |
| <label for="file1" class="cursor-pointer bg-primary-500 hover:bg-primary-600 text-white px-6 py-3 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="upload" class="w-4 h-4 mr-2"></i> | |
| Upload File 1 | |
| </label> | |
| <div id="file1-status" class="mt-3 text-sm text-gray-500"></div> | |
| </div> | |
| </div> | |
| <!-- File 2 Upload --> | |
| <div class="bg-white rounded-2xl shadow-lg p-6 border-2 border-dashed border-secondary-200 hover:border-secondary-400 transition-colors"> | |
| <div class="text-center"> | |
| <i data-feather="users" class="w-12 h-12 text-secondary-500 mx-auto mb-4"></i> | |
| <h3 class="text-xl font-semibold text-gray-900 mb-2">File 2: Teaching Staff List</h3> | |
| <p class="text-gray-600 mb-4">Teaching and Instructing Staff List<br>(.xlsx, .xls)</p> | |
| <input type="file" id="file2" accept=".xlsx,.xls" class="hidden"> | |
| <label for="file2" class="cursor-pointer bg-secondary-500 hover:bg-secondary-600 text-white px-6 py-3 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="upload" class="w-4 h-4 mr-2"></i> | |
| Upload File 2 | |
| </label> | |
| <div id="file2-status" class="mt-3 text-sm text-gray-500"></div> | |
| </div> | |
| </div> | |
| <!-- File 3 Upload --> | |
| <div class="bg-white rounded-2xl shadow-lg p-6 border-2 border-dashed border-green-200 hover:border-green-400 transition-colors"> | |
| <div class="text-center"> | |
| <i data-feather="database" class="w-12 h-12 text-green-500 mx-auto mb-4"></i> | |
| <h3 class="text-xl font-semibold text-gray-900 mb-2">File 3: Department Data</h3> | |
| <p class="text-gray-600 mb-4">Department Records List<br>(.xlsx, .xls)</p> | |
| <input type="file" id="file3" accept=".xlsx,.xls" class="hidden"> | |
| <label for="file3" class="cursor-pointer bg-green-500 hover:bg-green-600 text-white px-6 py-3 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="upload" class="w-4 h-4 mr-2"></i> | |
| Upload File 3 | |
| </label> | |
| <div id="file3-status" class="mt-3 text-sm text-gray-500"></div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Process File 3 Button --> | |
| <div class="text-center mb-6"> | |
| <button id="process-file3-btn" disabled class="bg-green-500 hover:bg-green-600 text-white px-8 py-4 rounded-xl font-semibold text-lg transition-all transform hover:scale-105 disabled:opacity-50 disabled:cursor-not-allowed disabled:transform-none"> | |
| <i data-feather="filter" class="w-5 h-5 mr-2 inline"></i> | |
| Process File 3 | |
| </button> | |
| </div> | |
| <!-- Compare and Clear Buttons --> | |
| <div class="text-center mb-12 space-x-4"> | |
| <button id="compare-btn" disabled class="bg-gradient-to-r from-primary-500 to-secondary-500 hover:from-primary-600 hover:to-secondary-600 text-white px-8 py-4 rounded-xl font-semibold text-lg transition-all transform hover:scale-105 disabled:opacity-50 disabled:cursor-not-allowed disabled:transform-none"> | |
| <i data-feather="git-compare" class="w-5 h-5 mr-2 inline"></i> | |
| Compare Data | |
| </button> | |
| <button id="clear-btn" class="bg-gray-500 hover:bg-gray-600 text-white px-8 py-4 rounded-xl font-semibold text-lg transition-all transform hover:scale-105"> | |
| <i data-feather="refresh-cw" class="w-5 h-5 mr-2 inline"></i> | |
| Clear All | |
| </button> | |
| </div> | |
| <!-- Results Section --> | |
| <div id="results" class="hidden bg-white rounded-2xl shadow-lg p-8"> | |
| <h2 class="text-2xl font-bold text-gray-900 mb-6 text-center">Comparison Results</h2> | |
| <!-- Data Analysis Section --> | |
| <div class="bg-red-50 border border-red-200 rounded-xl p-6 mb-8"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="alert-triangle" class="w-6 h-6 text-red-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-red-700">Data Analysis</h3> | |
| </div> | |
| <div class="grid md:grid-cols-3 gap-4"> | |
| <div class="bg-white rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-red-600 mb-1" id="file1-null-count">0</div> | |
| <div class="text-sm text-red-700 font-medium">File 1 Null Values</div> | |
| <div class="text-xs text-red-500 mt-1">In "m1_select_session" column</div> | |
| </div> | |
| <div class="bg-white rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-red-600 mb-1" id="file2-null-count">0</div> | |
| <div class="text-sm text-red-700 font-medium">File 2 Null Values</div> | |
| <div class="text-xs text-red-500 mt-1">In "email" column</div> | |
| </div> | |
| <div class="bg-white rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-red-600 mb-1" id="file2-duplicates-count">0</div> | |
| <div class="text-sm text-red-700 font-medium">File 2 Duplicates</div> | |
| <div class="text-xs text-red-500 mt-1">Duplicate emails</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Download Null Values Button --> | |
| <div class="text-center mb-6"> | |
| <button id="download-null-btn" class="bg-red-500 hover:bg-red-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download Null Values Report | |
| </button> | |
| </div> | |
| <!-- Filtered Comparison Section --> | |
| <div class="bg-blue-50 border border-blue-200 rounded-xl p-6 mb-8"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="filter" class="w-6 h-6 text-blue-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-blue-700">Filtered Comparison Results</h3> | |
| </div> | |
| <p class="text-center text-blue-600 text-sm mb-4">Comparing File 1 (CNA) with File 2 (Email) after removing null values</p> | |
| <div class="grid md:grid-cols-3 gap-6"> | |
| <div class="bg-yellow-50 border border-yellow-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-plus" class="w-6 h-6 text-yellow-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-yellow-600 mb-1" id="filtered-unique-file1-count">0</div> | |
| <div class="text-xs text-yellow-700 font-medium">Unique to File 1 (CNA)</div> | |
| </div> | |
| <div class="bg-primary-50 border border-primary-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-check" class="w-6 h-6 text-primary-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-primary-600 mb-1" id="filtered-common-count">0</div> | |
| <div class="text-xs text-primary-700 font-medium">Common Records</div> | |
| </div> | |
| <div class="bg-green-50 border border-green-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-x" class="w-6 h-6 text-green-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-green-600 mb-1" id="filtered-unique-file2-count">0</div> | |
| <div class="text-xs text-green-700 font-medium">Unique to File 2 (Email)</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Download Buttons --> | |
| <div class="text-center mb-6 flex flex-wrap justify-center gap-4"> | |
| <button id="download-filtered-btn" class="bg-blue-500 hover:bg-blue-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download Filtered Comparison Results | |
| </button> | |
| <button id="download-updated-file2-btn" class="bg-green-500 hover:bg-green-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download Updated File 2 | |
| </button> | |
| </div> | |
| <!-- Non-Filtered Comparison Section --> | |
| <div class="bg-purple-50 border border-purple-200 rounded-xl p-6 mb-8"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="eye" class="w-6 h-6 text-purple-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-purple-700">Non-Filtered Comparison Results</h3> | |
| </div> | |
| <p class="text-center text-purple-600 text-sm mb-4">Comparing File 1 (CNA) with File 2 (Email) including all records</p> | |
| <div class="grid md:grid-cols-3 gap-6"> | |
| <div class="bg-yellow-50 border border-yellow-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-plus" class="w-6 h-6 text-yellow-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-yellow-600 mb-1" id="non-filtered-unique-file1-count">0</div> | |
| <div class="text-xs text-yellow-700 font-medium">Unique to File 1 (CNA)</div> | |
| </div> | |
| <div class="bg-primary-50 border border-primary-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-check" class="w-6 h-6 text-primary-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-primary-600 mb-1" id="non-filtered-common-count">0</div> | |
| <div class="text-xs text-primary-700 font-medium">Common Records</div> | |
| </div> | |
| <div class="bg-green-50 border border-green-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-x" class="w-6 h-6 text-green-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-green-600 mb-1" id="non-filtered-unique-file2-count">0</div> | |
| <div class="text-xs text-green-700 font-medium">Unique to File 2 (Email)</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Download Buttons --> | |
| <div class="text-center mb-6 flex flex-wrap justify-center gap-4"> | |
| <button id="download-non-filtered-btn" class="bg-purple-500 hover:bg-purple-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download Non-Filtered Comparison Results | |
| </button> | |
| <button id="download-updated-file2-non-filtered-btn" class="bg-orange-500 hover:bg-orange-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download Updated File 2 | |
| </button> | |
| </div> | |
| <!-- File 3 Results Section --> | |
| <div id="file3-results" class="hidden bg-white rounded-2xl shadow-lg p-8 mb-8"> | |
| <h2 class="text-2xl font-bold text-gray-900 mb-6 text-center">File 3 Analysis Results</h2> | |
| <!-- Summary Stats --> | |
| <div class="bg-green-50 border border-green-200 rounded-xl p-6 mb-6"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="bar-chart-2" class="w-6 h-6 text-green-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-green-700">M1 Status Summary</h3> | |
| </div> | |
| <div class="grid md:grid-cols-2 lg:grid-cols-4 gap-4"> | |
| <div class="bg-white rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-green-600 mb-1" id="m1-completed-count">0</div> | |
| <div class="text-sm text-green-700 font-medium">Completed</div> | |
| <div class="text-xs text-green-500 mt-1">Records with "completed" status</div> | |
| </div> | |
| <div class="bg-blue-50 border border-blue-200 rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-blue-600 mb-1" id="m1-enrolled-count">0</div> | |
| <div class="text-sm text-blue-700 font-medium">Enrolled</div> | |
| <div class="text-xs text-blue-500 mt-1">Records with "enrolled" status</div> | |
| </div> | |
| <div class="bg-orange-50 border border-orange-200 rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-orange-600 mb-1" id="m1-not-enrolled-count">0</div> | |
| <div class="text-sm text-orange-700 font-medium">Not Enrolled</div> | |
| <div class="text-xs text-orange-500 mt-1">Records with "not enrolled" status</div> | |
| </div> | |
| <div class="bg-gray-50 border border-gray-200 rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-gray-600 mb-1" id="file3-removed-count">0</div> | |
| <div class="text-sm text-gray-700 font-medium">Records Removed</div> | |
| <div class="text-xs text-gray-500 mt-1">THEI department records</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Download Button --> | |
| <div class="text-center mb-6"> | |
| <button id="download-file3-btn" class="bg-green-500 hover:bg-green-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download Filtered File 3 | |
| </button> | |
| </div> | |
| <!-- File 3 vs File 2 Comparison Section --> | |
| <div class="bg-indigo-50 border border-indigo-200 rounded-xl p-6 mb-6"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="git-compare" class="w-6 h-6 text-indigo-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-indigo-700">Completed Records Comparison (File 3 vs File 2)</h3> | |
| </div> | |
| <p class="text-center text-indigo-600 text-sm mb-4">Comparing "Completed" records from File 3 (CNA) with File 2 (EMAIL)</p> | |
| <div class="grid md:grid-cols-3 gap-6"> | |
| <div class="bg-yellow-50 border border-yellow-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-plus" class="w-6 h-6 text-yellow-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-yellow-600 mb-1" id="f3-vs-f2-unique-file3-count">0</div> | |
| <div class="text-xs text-yellow-700 font-medium">Unique to File 3 (CNA)</div> | |
| </div> | |
| <div class="bg-primary-50 border border-primary-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-check" class="w-6 h-6 text-primary-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-primary-600 mb-1" id="f3-vs-f2-common-count">0</div> | |
| <div class="text-xs text-primary-700 font-medium">Common Records</div> | |
| </div> | |
| <div class="bg-green-50 border border-green-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-x" class="w-6 h-6 text-green-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-green-600 mb-1" id="f3-vs-f2-unique-file2-count">0</div> | |
| <div class="text-xs text-green-700 font-medium">Unique to File 2 (Email)</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Download Button for F3 vs F2 Comparison --> | |
| <div class="text-center mb-6"> | |
| <button id="download-f3-vs-f2-btn" class="bg-indigo-500 hover:bg-indigo-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download File 3 vs File 2 Comparison | |
| </button> | |
| </div> | |
| <!-- First Module Date Monthly Breakdown Section --> | |
| <div class="bg-cyan-50 border border-cyan-200 rounded-xl p-6 mb-6"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="calendar" class="w-6 h-6 text-cyan-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-cyan-700">First Module Date Monthly Breakdown (Common Records)</h3> | |
| </div> | |
| <p class="text-center text-cyan-600 text-sm mb-4">Counting "First Module Date" by month for common records (Completed)</p> | |
| <div id="first-module-date-breakdown" class="grid md:grid-cols-2 lg:grid-cols-3 gap-4"> | |
| <div class="bg-white rounded-lg p-4 text-center text-gray-500"> | |
| <div class="text-sm">No data available yet</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Download Button for First Module Date Breakdown --> | |
| <div class="text-center mb-6"> | |
| <button id="download-first-module-date-btn" class="bg-cyan-500 hover:bg-cyan-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center" disabled> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download First Module Date Breakdown | |
| </button> | |
| </div> | |
| <!-- M5 Status Summary Section --> | |
| <div class="bg-teal-50 border border-teal-200 rounded-xl p-6 mb-6"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="check-circle" class="w-6 h-6 text-teal-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-teal-700">M5 Status Summary</h3> | |
| </div> | |
| <div class="grid md:grid-cols-2 gap-4"> | |
| <div class="bg-white rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-teal-600 mb-1" id="m5-y-count">0</div> | |
| <div class="text-sm text-teal-700 font-medium">M5 = "Y" Records</div> | |
| <div class="text-xs text-teal-500 mt-1">Records with M5 value "Y"</div> | |
| </div> | |
| <div class="bg-gray-50 border border-gray-200 rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-gray-600 mb-1" id="m5-not-y-count">0</div> | |
| <div class="text-sm text-gray-700 font-medium">M5 ≠ "Y" Records</div> | |
| <div class="text-xs text-gray-500 mt-1">Records without M5 value "Y"</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- M5 (Y) vs File 2 Comparison Section --> | |
| <div class="bg-rose-50 border border-rose-200 rounded-xl p-6 mb-6"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="git-compare" class="w-6 h-6 text-rose-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-rose-700">M5 (Y) Records Comparison (File 3 vs File 2)</h3> | |
| </div> | |
| <p class="text-center text-rose-600 text-sm mb-4">Comparing "M5 = Y" records from File 3 (CNA) with File 2 (EMAIL)</p> | |
| <div class="grid md:grid-cols-3 gap-6"> | |
| <div class="bg-yellow-50 border border-yellow-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-plus" class="w-6 h-6 text-yellow-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-yellow-600 mb-1" id="m5-vs-f2-unique-file3-count">0</div> | |
| <div class="text-xs text-yellow-700 font-medium">Unique to File 3 (CNA)</div> | |
| </div> | |
| <div class="bg-primary-50 border border-primary-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-check" class="w-6 h-6 text-primary-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-primary-600 mb-1" id="m5-vs-f2-common-count">0</div> | |
| <div class="text-xs text-primary-700 font-medium">Common Records</div> | |
| </div> | |
| <div class="bg-green-50 border border-green-200 rounded-xl p-4 text-center"> | |
| <i data-feather="user-x" class="w-6 h-6 text-green-600 mx-auto mb-2"></i> | |
| <div class="text-2xl font-bold text-green-600 mb-1" id="m5-vs-f2-unique-file2-count">0</div> | |
| <div class="text-xs text-green-700 font-medium">Unique to File 2 (Email)</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Download Button for M5 vs F2 Comparison --> | |
| <div class="text-center mb-6"> | |
| <button id="download-m5-vs-f2-btn" class="bg-rose-500 hover:bg-rose-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center"> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download M5 (Y) vs File 2 Comparison | |
| </button> | |
| </div> | |
| <!-- M5 Not Equal to Y for Common Records Section --> | |
| <div class="bg-slate-50 border border-slate-200 rounded-xl p-6 mb-6"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="alert-circle" class="w-6 h-6 text-slate-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-slate-700">M5 ≠ "Y" in Common Records (Completed)</h3> | |
| </div> | |
| <p class="text-center text-slate-600 text-sm mb-4">Checking common records from Completed comparison where M5 is not equal to "Y"</p> | |
| <div class="grid md:grid-cols-2 gap-4"> | |
| <div class="bg-white rounded-lg p-4 text-center border border-slate-200"> | |
| <div class="text-2xl font-bold text-slate-600 mb-1" id="common-m5-not-y-count">0</div> | |
| <div class="text-sm text-slate-700 font-medium">M5 ≠ "Y" Records</div> | |
| <div class="text-xs text-slate-500 mt-1">Common records with M5 not equal to Y</div> | |
| </div> | |
| <div class="bg-emerald-50 border border-emerald-200 rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-emerald-600 mb-1" id="common-m5-y-count">0</div> | |
| <div class="text-sm text-emerald-700 font-medium">M5 = "Y" Records</div> | |
| <div class="text-xs text-emerald-500 mt-1">Common records with M5 equal to Y</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Download Button for M5 Not Equal to Y in Common Records --> | |
| <div class="text-center mb-6"> | |
| <button id="download-common-m5-not-y-btn" class="bg-slate-500 hover:bg-slate-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center" disabled> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download M5 ≠ "Y" in Common Records | |
| </button> | |
| </div> | |
| <!-- Completion Date Monthly Breakdown Section --> | |
| <div class="bg-amber-50 border border-amber-200 rounded-xl p-6 mb-6"> | |
| <div class="flex items-center justify-center mb-4"> | |
| <i data-feather="calendar" class="w-6 h-6 text-amber-600 mr-2"></i> | |
| <h3 class="text-lg font-semibold text-amber-700">Completion Date Monthly Breakdown (Common Records)</h3> | |
| </div> | |
| <p class="text-center text-amber-600 text-sm mb-4">Counting "Completion Date" by month for common records (M5 = Y)</p> | |
| <div id="completion-date-breakdown" class="grid md:grid-cols-2 lg:grid-cols-3 gap-4"> | |
| <div class="bg-white rounded-lg p-4 text-center text-gray-500"> | |
| <div class="text-sm">No data available yet</div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- Download Button for Completion Date Breakdown --> | |
| <div class="text-center mb-6"> | |
| <button id="download-completion-date-btn" class="bg-amber-500 hover:bg-amber-600 text-white px-6 py-2 rounded-lg font-medium transition-colors inline-flex items-center" disabled> | |
| <i data-feather="download" class="w-4 h-4 mr-2"></i> | |
| Download Completion Date Breakdown | |
| </button> | |
| </div> | |
| </div> | |
| <!-- Loading Spinner --> | |
| <div id="loading" class="hidden fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center z-50"> | |
| <div class="bg-white rounded-xl p-6 flex items-center space-x-3"> | |
| <div class="animate-spin rounded-full h-8 w-8 border-b-2 border-primary-500"></div> | |
| <span class="text-gray-700">Processing files...</span> | |
| </div> | |
| </div> | |
| </div> | |
| <script> | |
| let file1Data = null; | |
| let file2Data = null; | |
| let file3Data = null; | |
| let uniqueEnrolmentData = null; | |
| let nullRecords = []; | |
| let filteredFile1Data = null; | |
| let filteredFile3Data = null; | |
| // File upload handlers | |
| document.getElementById('file1').addEventListener('change', function(e) { | |
| handleFileUpload(e, 1); | |
| }); | |
| document.getElementById('file2').addEventListener('change', function(e) { | |
| handleFileUpload(e, 2); | |
| }); | |
| document.getElementById('file3').addEventListener('change', function(e) { | |
| handleFileUpload(e, 3); | |
| }); | |
| function handleFileUpload(event, fileNumber) { | |
| const file = event.target.files[0]; | |
| if (!file) return; | |
| const statusElement = document.getElementById(`file${fileNumber}-status`); | |
| statusElement.textContent = `Loading ${file.name}...`; | |
| statusElement.className = 'mt-3 text-sm text-blue-500'; | |
| const reader = new FileReader(); | |
| reader.onload = function(e) { | |
| try { | |
| const data = new Uint8Array(e.target.result); | |
| const workbook = XLSX.read(data, { type: 'array' }); | |
| // Get first sheet | |
| const firstSheet = workbook.Sheets[workbook.SheetNames[0]]; | |
| const jsonData = XLSX.utils.sheet_to_json(firstSheet); | |
| if (fileNumber === 1) { | |
| file1Data = jsonData; | |
| statusElement.textContent = `${file.name} loaded (${jsonData.length} records)`; | |
| statusElement.className = 'mt-3 text-sm text-green-500'; | |
| } else if (fileNumber === 2) { | |
| file2Data = jsonData; | |
| statusElement.textContent = `${file.name} loaded (${jsonData.length} records)`; | |
| statusElement.className = 'mt-3 text-sm text-green-500'; | |
| } else if (fileNumber === 3) { | |
| file3Data = jsonData; | |
| statusElement.textContent = `${file.name} loaded (${jsonData.length} records)`; | |
| statusElement.className = 'mt-3 text-sm text-green-500'; | |
| } | |
| updateCompareButton(); | |
| } catch (error) { | |
| statusElement.textContent = `Error loading file: ${error.message}`; | |
| statusElement.className = 'mt-3 text-sm text-red-500'; | |
| } | |
| }; | |
| reader.readAsArrayBuffer(file); | |
| } | |
| function updateCompareButton() { | |
| const compareBtn = document.getElementById('compare-btn'); | |
| compareBtn.disabled = !(file1Data && file2Data); | |
| const processFile3Btn = document.getElementById('process-file3-btn'); | |
| processFile3Btn.disabled = !file3Data; | |
| } | |
| // Clear button handler | |
| document.getElementById('clear-btn').addEventListener('click', function() { | |
| // Clear file inputs | |
| document.getElementById('file1').value = ''; | |
| document.getElementById('file2').value = ''; | |
| document.getElementById('file3').value = ''; | |
| // Clear status displays | |
| document.getElementById('file1-status').textContent = ''; | |
| document.getElementById('file2-status').textContent = ''; | |
| document.getElementById('file3-status').textContent = ''; | |
| // Clear data variables | |
| file1Data = null; | |
| file2Data = null; | |
| file3Data = null; | |
| filteredFile3Data = null; | |
| uniqueEnrolmentData = null; | |
| nullRecords = []; | |
| // Disable download button | |
| document.getElementById('download-null-btn').disabled = true; | |
| document.getElementById('download-filtered-btn').disabled = true; | |
| // Disable buttons | |
| document.getElementById('compare-btn').disabled = true; | |
| // Hide results sections | |
| document.getElementById('results').classList.add('hidden'); | |
| document.getElementById('file3-results').classList.add('hidden'); | |
| // Reset status classes | |
| document.getElementById('file1-status').className = 'mt-3 text-sm text-gray-500'; | |
| document.getElementById('file2-status').className = 'mt-3 text-sm text-gray-500'; | |
| document.getElementById('file3-status').className = 'mt-3 text-sm text-gray-500'; | |
| // Disable file3 button | |
| document.getElementById('process-file3-btn').disabled = true; | |
| document.getElementById('download-file3-btn').disabled = true; | |
| document.getElementById('download-first-module-date-btn').disabled = true; | |
| document.getElementById('download-m5-vs-f2-btn').disabled = true; | |
| } | |
| ); | |
| // Initialize download buttons as disabled | |
| document.getElementById('download-null-btn').disabled = true; | |
| document.getElementById('download-filtered-btn').disabled = true; | |
| document.getElementById('download-non-filtered-btn').disabled = true; | |
| document.getElementById('download-updated-file2-btn').disabled = true; | |
| document.getElementById('download-updated-file2-non-filtered-btn').disabled = true; | |
| document.getElementById('download-file3-btn').disabled = true; | |
| // Compare function | |
| document.getElementById('compare-btn').addEventListener('click', function() { | |
| if (!file1Data || !file2Data) return; | |
| // Show loading | |
| document.getElementById('loading').classList.remove('hidden'); | |
| // Process after a small delay to show loading state | |
| setTimeout(() => { | |
| compareData(); | |
| document.getElementById('loading').classList.add('hidden'); | |
| }, 500); | |
| }); | |
| function compareData() { | |
| // Count null values in m1_select_session column of File 1 | |
| let file1NullCount = 0; | |
| nullRecords = []; | |
| // Count null/blank values in email column of File 2 | |
| let file2NullCount = 0; | |
| let file2NullRecords = []; | |
| // Count duplicate emails in File 2 | |
| let file2DuplicateCount = 0; | |
| const emailCount = new Map(); | |
| const duplicateEmails = new Set(); | |
| // Enable download buttons | |
| document.getElementById('download-null-btn').disabled = false; | |
| document.getElementById('download-filtered-btn').disabled = false; | |
| document.getElementById('download-non-filtered-btn').disabled = false; | |
| document.getElementById('download-updated-file2-btn').disabled = false; | |
| document.getElementById('download-updated-file2-non-filtered-btn').disabled = false; | |
| // Count null values in File 2 and track duplicates | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| // Check for null/blank emails | |
| if (email === null || email === undefined || | |
| email === '' || email.toString().toLowerCase() === 'null') { | |
| file2NullCount++; | |
| file2NullRecords.push({ | |
| ...row, | |
| Status: "Null/Blank Email" | |
| }); | |
| } else { | |
| // Track email occurrences for duplicate detection | |
| const emailKey = email.toString().trim().toLowerCase(); | |
| emailCount.set(emailKey, (emailCount.get(emailKey) || 0) + 1); | |
| if (emailCount.get(emailKey) === 2) { | |
| duplicateEmails.add(emailKey); | |
| } | |
| } | |
| }); | |
| // Count total duplicates | |
| file2DuplicateCount = Array.from(duplicateEmails).reduce((count, email) => { | |
| return count + (emailCount.get(email) - 1); | |
| }, 0); | |
| // Update File 2 null count and duplicates display | |
| document.getElementById('file2-null-count').textContent = file2NullCount; | |
| document.getElementById('file2-duplicates-count').textContent = file2DuplicateCount; | |
| // Filter out null values from file1 to create filteredFile1Data | |
| filteredFile1Data = file1Data.filter(row => { | |
| const m1SelectSession = row.m1_select_session || row.M1_SELECT_SESSION || row['m1_select_session']; | |
| if (m1SelectSession === null || m1SelectSession === undefined || | |
| m1SelectSession === '' || m1SelectSession.toString().toLowerCase() === 'null') { | |
| file1NullCount++; | |
| nullRecords.push({ | |
| ...row, | |
| Status: "Null values found" | |
| }); | |
| return false; // Exclude this row from filtered data | |
| } | |
| return true; // Include this row in filtered data | |
| }); | |
| // Update File 1 null count display | |
| document.getElementById('file1-null-count').textContent = file1NullCount; | |
| // Extract CNA from filtered file1 and Email from file2 | |
| const file1CNA = new Set(); | |
| const file1EmailMap = new Map(); | |
| const file1DataMap = new Map(); | |
| // Process filtered file1 data (CNA column) | |
| filteredFile1Data.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (cna && typeof cna === 'string') { | |
| const cnaKey = cna.trim().toLowerCase(); | |
| file1CNA.add(cnaKey); | |
| if (email && typeof email === 'string') { | |
| file1EmailMap.set(cnaKey, email.trim()); | |
| } | |
| file1DataMap.set(cnaKey, row); | |
| } | |
| }); | |
| const file2Email = new Set(); | |
| const file2DataMap = new Map(); | |
| // Process file2 data (Email column) | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (email && typeof email === 'string') { | |
| const emailKey = email.trim().toLowerCase(); | |
| file2Email.add(emailKey); | |
| file2DataMap.set(emailKey, row); | |
| } | |
| }); | |
| // For original comparison, we compare CNA from original file1 with Email from file2 | |
| // Need to get original CNA values (including nulls) for accurate count | |
| const originalFile1CNA = new Set(); | |
| file1Data.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| if (cna && typeof cna === 'string') { | |
| originalFile1CNA.add(cna.trim().toLowerCase()); | |
| } | |
| }); | |
| // Calculate intersections and differences for original comparison | |
| const common = new Set([...originalFile1CNA].filter(cna => file2Email.has(cna))); | |
| const uniqueToFile1 = new Set([...originalFile1CNA].filter(cna => !file2Email.has(cna))); | |
| const uniqueToFile2 = new Set([...file2Email].filter(email => !originalFile1CNA.has(email))); | |
| // Calculate intersections and differences for non-filtered comparison (original CNA vs Email) | |
| const nonFilteredCommon = new Set([...originalFile1CNA].filter(cna => file2Email.has(cna))); | |
| const nonFilteredUniqueToFile1 = new Set([...originalFile1CNA].filter(cna => !file2Email.has(cna))); | |
| const nonFilteredUniqueToFile2 = new Set([...file2Email].filter(email => !originalFile1CNA.has(email))); | |
| // Calculate intersections and differences for filtered comparison (CNA vs Email) | |
| const filteredCommon = new Set([...file1CNA].filter(cna => file2Email.has(cna))); | |
| const filteredUniqueToFile1 = new Set([...file1CNA].filter(cna => !file2Email.has(cna))); | |
| const filteredUniqueToFile2 = new Set([...file2Email].filter(email => !file1CNA.has(email))); | |
| // Create separate data for unique enrolment records (from original file1 data) | |
| uniqueEnrolmentData = Array.from(uniqueToFile1).map(cna => { | |
| const originalRow = file1DataMap.get(cna); | |
| return { | |
| ...originalRow, | |
| Status: 'Unique to Enrolment List', | |
| Note: 'Record from Enrolment List not found in Staff List' | |
| }; | |
| }); | |
| // Update UI with results for filtered comparison | |
| document.getElementById('filtered-common-count').textContent = filteredCommon.size; | |
| document.getElementById('filtered-unique-file1-count').textContent = filteredUniqueToFile1.size; | |
| document.getElementById('filtered-unique-file2-count').textContent = filteredUniqueToFile2.size; | |
| // Update UI with results for non-filtered comparison | |
| document.getElementById('non-filtered-common-count').textContent = nonFilteredCommon.size; | |
| document.getElementById('non-filtered-unique-file1-count').textContent = nonFilteredUniqueToFile1.size; | |
| document.getElementById('non-filtered-unique-file2-count').textContent = nonFilteredUniqueToFile2.size; | |
| // Display detailed records for original comparison | |
| // Show results section | |
| document.getElementById('results').classList.remove('hidden'); | |
| // Scroll to results | |
| document.getElementById('results').scrollIntoView({ behavior: 'smooth' }); | |
| } | |
| // Process File 3 button handler | |
| document.getElementById('process-file3-btn').addEventListener('click', function() { | |
| if (!file3Data) return; | |
| // Show loading | |
| document.getElementById('loading').classList.remove('hidden'); | |
| // Process after a small delay to show loading state | |
| setTimeout(() => { | |
| processFile3Data(); | |
| document.getElementById('loading').classList.add('hidden'); | |
| }, 500); | |
| }); | |
| function processFile3Data() { | |
| // Filter out rows where department starts with "THEI" | |
| filteredFile3Data = file3Data.filter(row => { | |
| const department = row.department || row.DEPARTMENT || row.Department; | |
| if (department && typeof department === 'string') { | |
| return !department.trim().toUpperCase().startsWith('THEI'); | |
| } | |
| return true; // Keep if no department column | |
| }); | |
| const removedCount = file3Data.length - filteredFile3Data.length; | |
| // Count completed, enrolled, and not enrolled in M1 Status column | |
| let completedCount = 0; | |
| let enrolledCount = 0; | |
| let notEnrolledCount = 0; | |
| let otherCount = 0; | |
| filteredFile3Data.forEach(row => { | |
| const m1Status = row['M1 Status'] || row['m1_status'] || row['M1_STATUS'] || row.m1Status; | |
| if (m1Status && typeof m1Status === 'string') { | |
| const statusLower = m1Status.toString().trim().toLowerCase(); | |
| if (statusLower === 'completed') { | |
| completedCount++; | |
| } else if (statusLower === 'enrolled') { | |
| enrolledCount++; | |
| } else if (statusLower === 'not enrolled') { | |
| notEnrolledCount++; | |
| } else { | |
| otherCount++; // Other statuses | |
| } | |
| } else { | |
| otherCount++; // Missing status | |
| } | |
| }); | |
| // Add other statuses to not enrolled (for display purposes) | |
| notEnrolledCount += otherCount; | |
| // Update UI | |
| document.getElementById('m1-completed-count').textContent = completedCount; | |
| document.getElementById('m1-enrolled-count').textContent = enrolledCount; | |
| document.getElementById('m1-not-enrolled-count').textContent = notEnrolledCount; | |
| document.getElementById('file3-removed-count').textContent = removedCount; | |
| // Compare File 3 (completed records) with File 2 (email) | |
| compareFile3WithFile2(); | |
| // Enable download buttons | |
| document.getElementById('download-file3-btn').disabled = false; | |
| document.getElementById('download-f3-vs-f2-btn').disabled = false; | |
| document.getElementById('download-first-module-date-btn').disabled = window.firstModuleDateBreakdownData && window.firstModuleDateBreakdownData.length > 0 ? false : true; | |
| // Show results section | |
| document.getElementById('file3-results').classList.remove('hidden'); | |
| // Scroll to results | |
| document.getElementById('file3-results').scrollIntoView({ behavior: 'smooth' }); | |
| } | |
| // Function to compare File 3 (completed) with File 2 | |
| function compareFile3WithFile2() { | |
| if (!filteredFile3Data || !file2Data) return; | |
| // Get only completed records from File 3 | |
| const completedFile3Records = filteredFile3Data.filter(row => { | |
| const m1Status = row['M1 Status'] || row['m1_status'] || row['M1_STATUS'] || row.m1Status; | |
| if (m1Status && typeof m1Status === 'string') { | |
| return m1Status.toString().trim().toLowerCase() === 'completed'; | |
| } | |
| return false; | |
| }); | |
| // Extract CNA from completed File 3 records | |
| const file3CNA = new Set(); | |
| const file3DataMap = new Map(); | |
| completedFile3Records.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| if (cna && typeof cna === 'string') { | |
| const cnaKey = cna.trim().toLowerCase(); | |
| file3CNA.add(cnaKey); | |
| file3DataMap.set(cnaKey, row); | |
| } | |
| }); | |
| // Extract Email from File 2 | |
| const file2Email = new Set(); | |
| const file2DataMap = new Map(); | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (email && typeof email === 'string') { | |
| const emailKey = email.trim().toLowerCase(); | |
| file2Email.add(emailKey); | |
| file2DataMap.set(emailKey, row); | |
| } | |
| }); | |
| // Calculate intersections and differences | |
| const common = new Set([...file3CNA].filter(cna => file2Email.has(cna))); | |
| const uniqueToFile3 = new Set([...file3CNA].filter(cna => !file2Email.has(cna))); | |
| const uniqueToFile2 = new Set([...file2Email].filter(email => !file3CNA.has(email))); | |
| // Update UI | |
| document.getElementById('f3-vs-f2-common-count').textContent = common.size; | |
| document.getElementById('f3-vs-f2-unique-file3-count').textContent = uniqueToFile3.size; | |
| document.getElementById('f3-vs-f2-unique-file2-count').textContent = uniqueToFile2.size; | |
| // Process first module date breakdown for common records | |
| processFirstModuleDateBreakdown(common, file3DataMap); | |
| // Call M5 comparison function | |
| compareM5WithFile2(); | |
| // Process M5 not equal to Y for common records | |
| processM5NotYInCommonRecords(common, file3DataMap); | |
| } | |
| // Function to compare M5 (Y) records from File 3 with File 2 | |
| function compareM5WithFile2() { | |
| if (!filteredFile3Data || !file2Data) return; | |
| // Count M5 = "Y" and M5 ≠ "Y" | |
| let m5YCount = 0; | |
| let m5NotYCount = 0; | |
| filteredFile3Data.forEach(row => { | |
| const m5Value = row['M5'] || row['m5'] || row['M5_STATUS'] || row.m5; | |
| if (m5Value && typeof m5Value === 'string') { | |
| const m5Trimmed = m5Value.toString().trim().toUpperCase(); | |
| if (m5Trimmed === 'Y') { | |
| m5YCount++; | |
| } else { | |
| m5NotYCount++; | |
| } | |
| } else { | |
| m5NotYCount++; // Count as not Y if M5 is missing | |
| } | |
| }); | |
| // Update UI for M5 summary | |
| document.getElementById('m5-y-count').textContent = m5YCount; | |
| document.getElementById('m5-not-y-count').textContent = m5NotYCount; | |
| // Get only records where M5 = "Y" from File 3 | |
| const m5YRecords = filteredFile3Data.filter(row => { | |
| const m5Value = row['M5'] || row['m5'] || row['M5_STATUS'] || row.m5; | |
| if (m5Value && typeof m5Value === 'string') { | |
| return m5Value.toString().trim().toUpperCase() === 'Y'; | |
| } | |
| return false; | |
| }); | |
| // Extract CNA from M5 = "Y" File 3 records | |
| const m5File3CNA = new Set(); | |
| const m5File3DataMap = new Map(); | |
| m5YRecords.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| if (cna && typeof cna === 'string') { | |
| const cnaKey = cna.trim().toLowerCase(); | |
| m5File3CNA.add(cnaKey); | |
| m5File3DataMap.set(cnaKey, row); | |
| } | |
| }); | |
| // Extract Email from File 2 (reuse existing) | |
| const file2Email = new Set(); | |
| const file2DataMap = new Map(); | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (email && typeof email === 'string') { | |
| const emailKey = email.trim().toLowerCase(); | |
| file2Email.add(emailKey); | |
| file2DataMap.set(emailKey, row); | |
| } | |
| }); | |
| // Calculate intersections and differences for M5 comparison | |
| const m5Common = new Set([...m5File3CNA].filter(cna => file2Email.has(cna))); | |
| const m5UniqueToFile3 = new Set([...m5File3CNA].filter(cna => !file2Email.has(cna))); | |
| const m5UniqueToFile2 = new Set([...file2Email].filter(email => !m5File3CNA.has(email))); | |
| // Update UI for M5 comparison | |
| document.getElementById('m5-vs-f2-common-count').textContent = m5Common.size; | |
| document.getElementById('m5-vs-f2-unique-file3-count').textContent = m5UniqueToFile3.size; | |
| document.getElementById('m5-vs-f2-unique-file2-count').textContent = m5UniqueToFile2.size; | |
| // Enable download button | |
| document.getElementById('download-m5-vs-f2-btn').disabled = false; | |
| document.getElementById('download-common-m5-not-y-btn').disabled = window.commonM5NotYRecords && window.commonM5NotYRecords.length > 0 ? false : true; | |
| // Process completion date breakdown for common records | |
| processCompletionDateBreakdown(m5Common, m5File3DataMap); | |
| } | |
| // Function to process first module date breakdown for common records | |
| function processFirstModuleDateBreakdown(commonSet, dataMap) { | |
| const monthCounts = new Map(); | |
| commonSet.forEach(cna => { | |
| const row = dataMap.get(cna); | |
| if (row) { | |
| const firstModuleDate = row['First Module Date'] || row['first_module_date'] || row['FIRST_MODULE_DATE'] || row.firstModuleDate; | |
| if (firstModuleDate && typeof firstModuleDate === 'string' && firstModuleDate.trim() !== '') { | |
| try { | |
| // Parse the date and extract month | |
| const dateObj = new Date(firstModuleDate); | |
| // Check if date is valid | |
| if (!isNaN(dateObj.getTime())) { | |
| const monthKey = dateObj.toLocaleString('en-US', { | |
| year: 'numeric', | |
| month: 'long' | |
| }); | |
| monthCounts.set(monthKey, (monthCounts.get(monthKey) || 0) + 1); | |
| } | |
| } catch (e) { | |
| // Invalid date format, skip | |
| } | |
| } | |
| } | |
| }); | |
| // Sort months chronologically | |
| const sortedMonths = Array.from(monthCounts.entries()).sort((a, b) => { | |
| const dateA = new Date(a[0] + ' 1, 2000'); | |
| const dateB = new Date(b[0] + ' 1, 2000'); | |
| return dateA - dateB; | |
| }); | |
| // Update UI | |
| const breakdownContainer = document.getElementById('first-module-date-breakdown'); | |
| if (sortedMonths.length === 0) { | |
| breakdownContainer.innerHTML = ` | |
| <div class="bg-white rounded-lg p-4 text-center text-gray-500 col-span-full"> | |
| <div class="text-sm">No valid first module dates found in common records</div> | |
| </div> | |
| `; | |
| } else { | |
| breakdownContainer.innerHTML = sortedMonths.map(([month, count]) => ` | |
| <div class="bg-white rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-cyan-600 mb-1">${count}</div> | |
| <div class="text-xs text-cyan-700 font-medium">${month}</div> | |
| </div> | |
| `).join(''); | |
| } | |
| // Store breakdown data for download | |
| window.firstModuleDateBreakdownData = sortedMonths; | |
| // Enable download button if there's data | |
| document.getElementById('download-first-module-date-btn').disabled = sortedMonths.length === 0; | |
| } | |
| // Download first module date breakdown | |
| document.getElementById('download-first-module-date-btn').addEventListener('click', function() { | |
| if (!window.firstModuleDateBreakdownData || window.firstModuleDateBreakdownData.length === 0) return; | |
| // Prepare data for export | |
| const exportData = []; | |
| // Add header row | |
| exportData.push({ | |
| "Month": "", | |
| "Count": "", | |
| "Details": "First Module Date Monthly Breakdown for Common Records (Completed)" | |
| }); | |
| // Add separator | |
| exportData.push({ | |
| "Month": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add monthly data | |
| window.firstModuleDateBreakdownData.forEach(([month, count]) => { | |
| exportData.push({ | |
| "Month": month, | |
| "Count": count, | |
| "Details": "" | |
| }); | |
| }); | |
| // Add total | |
| const total = window.firstModuleDateBreakdownData.reduce((sum, [_, count]) => sum + count, 0); | |
| exportData.push({ | |
| "Month": "Total", | |
| "Count": total, | |
| "Details": "" | |
| }); | |
| // Create worksheet from export data | |
| const ws = XLSX.utils.json_to_sheet(exportData); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "First Module Date Breakdown"); | |
| // Export to file | |
| XLSX.writeFile(wb, "first_module_date_breakdown.xlsx"); | |
| }); | |
| // Function to process completion date breakdown for common records | |
| function processCompletionDateBreakdown(commonSet, dataMap) { | |
| const monthCounts = new Map(); | |
| commonSet.forEach(cna => { | |
| const row = dataMap.get(cna); | |
| if (row) { | |
| const completionDate = row['Completion Date'] || row['completion_date'] || row['COMPLETION_DATE'] || row.completionDate; | |
| if (completionDate && typeof completionDate === 'string' && completionDate.trim() !== '') { | |
| try { | |
| // Parse the date and extract month | |
| const dateObj = new Date(completionDate); | |
| // Check if date is valid | |
| if (!isNaN(dateObj.getTime())) { | |
| const monthKey = dateObj.toLocaleString('en-US', { | |
| year: 'numeric', | |
| month: 'long' | |
| }); | |
| monthCounts.set(monthKey, (monthCounts.get(monthKey) || 0) + 1); | |
| } | |
| } catch (e) { | |
| // Invalid date format, skip | |
| } | |
| } | |
| } | |
| }); | |
| // Sort months chronologically | |
| const sortedMonths = Array.from(monthCounts.entries()).sort((a, b) => { | |
| const dateA = new Date(a[0] + ' 1, 2000'); | |
| const dateB = new Date(b[0] + ' 1, 2000'); | |
| return dateA - dateB; | |
| }); | |
| // Update UI | |
| const breakdownContainer = document.getElementById('completion-date-breakdown'); | |
| if (sortedMonths.length === 0) { | |
| breakdownContainer.innerHTML = ` | |
| <div class="bg-white rounded-lg p-4 text-center text-gray-500 col-span-full"> | |
| <div class="text-sm">No valid completion dates found in common records</div> | |
| </div> | |
| `; | |
| } else { | |
| breakdownContainer.innerHTML = sortedMonths.map(([month, count]) => ` | |
| <div class="bg-white rounded-lg p-4 text-center"> | |
| <div class="text-2xl font-bold text-amber-600 mb-1">${count}</div> | |
| <div class="text-xs text-amber-700 font-medium">${month}</div> | |
| </div> | |
| `).join(''); | |
| } | |
| // Store breakdown data for download | |
| window.completionDateBreakdownData = sortedMonths; | |
| // Enable download button if there's data | |
| document.getElementById('download-completion-date-btn').disabled = sortedMonths.length === 0; | |
| } | |
| // Download completion date breakdown | |
| document.getElementById('download-completion-date-btn').addEventListener('click', function() { | |
| if (!window.completionDateBreakdownData || window.completionDateBreakdownData.length === 0) return; | |
| // Prepare data for export | |
| const exportData = []; | |
| // Add header row | |
| exportData.push({ | |
| "Month": "", | |
| "Count": "", | |
| "Details": "Completion Date Monthly Breakdown for Common Records (M5 = Y)" | |
| }); | |
| // Add separator | |
| exportData.push({ | |
| "Month": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add monthly data | |
| window.completionDateBreakdownData.forEach(([month, count]) => { | |
| exportData.push({ | |
| "Month": month, | |
| "Count": count, | |
| "Details": "" | |
| }); | |
| }); | |
| // Add total | |
| const total = window.completionDateBreakdownData.reduce((sum, [_, count]) => sum + count, 0); | |
| exportData.push({ | |
| "Month": "Total", | |
| "Count": total, | |
| "Details": "" | |
| }); | |
| // Create worksheet from export data | |
| const ws = XLSX.utils.json_to_sheet(exportData); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "Completion Date Breakdown"); | |
| // Export to file | |
| XLSX.writeFile(wb, "completion_date_breakdown.xlsx"); | |
| }); | |
| // Function to process M5 not equal to Y for common records | |
| function processM5NotYInCommonRecords(commonSet, dataMap) { | |
| let m5NotYCount = 0; | |
| let m5YCount = 0; | |
| const m5NotYRecords = []; | |
| commonSet.forEach(cna => { | |
| const row = dataMap.get(cna); | |
| if (row) { | |
| const m5Value = row['M5'] || row['m5'] || row['M5_STATUS'] || row.m5; | |
| let isM5Y = false; | |
| if (m5Value && typeof m5Value === 'string') { | |
| const m5Trimmed = m5Value.toString().trim().toUpperCase(); | |
| if (m5Trimmed === 'Y') { | |
| m5YCount++; | |
| isM5Y = true; | |
| } | |
| } | |
| if (!isM5Y) { | |
| m5NotYCount++; | |
| m5NotYRecords.push({ | |
| ...row, | |
| Status: "M5 not equal to Y" | |
| }); | |
| } | |
| } | |
| }); | |
| // Update UI | |
| document.getElementById('common-m5-not-y-count').textContent = m5NotYCount; | |
| document.getElementById('common-m5-y-count').textContent = m5YCount; | |
| // Store data for download | |
| window.commonM5NotYRecords = m5NotYRecords; | |
| // Enable download button if there's data | |
| document.getElementById('download-common-m5-not-y-btn').disabled = m5NotYRecords.length === 0; | |
| } | |
| // Download M5 not equal to Y in common records | |
| document.getElementById('download-common-m5-not-y-btn').addEventListener('click', function() { | |
| if (!window.commonM5NotYRecords || window.commonM5NotYRecords.length === 0) return; | |
| // Create worksheet from data | |
| const ws = XLSX.utils.json_to_sheet(window.commonM5NotYRecords); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "M5 Not Y in Common Records"); | |
| // Export to file | |
| XLSX.writeFile(wb, "common_records_m5_not_y.xlsx"); | |
| }); | |
| // Download filtered File 3 | |
| document.getElementById('download-file3-btn').addEventListener('click', function() { | |
| if (!filteredFile3Data || filteredFile3Data.length === 0) return; | |
| // Create worksheet from filtered data | |
| const ws = XLSX.utils.json_to_sheet(filteredFile3Data); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "Filtered File 3"); | |
| // Export to file | |
| XLSX.writeFile(wb, "filtered_file3.xlsx"); | |
| }); | |
| // Download null values report | |
| document.getElementById('download-null-btn').addEventListener('click', function() { | |
| if (!file1Data || file1Data.length === 0) return; | |
| // Create report data based on original file1 data with status column | |
| const reportData = file1Data.map(row => { | |
| const m1SelectSession = row.m1_select_session || row.M1_SELECT_SESSION || row['m1_select_session']; | |
| const isNull = (m1SelectSession === null || m1SelectSession === undefined || | |
| m1SelectSession === '' || m1SelectSession.toString().toLowerCase() === 'null'); | |
| return { | |
| ...row, | |
| Status: isNull ? "Null values found" : "Valid" | |
| }; | |
| }); | |
| // Create worksheet from report data | |
| const ws = XLSX.utils.json_to_sheet(reportData); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "Null Values Report"); | |
| // Export to file | |
| XLSX.writeFile(wb, "null_values_report.xlsx"); | |
| }); | |
| // Download filtered comparison results | |
| document.getElementById('download-filtered-btn').addEventListener('click', function() { | |
| if (!file1Data || !file2Data || !filteredFile1Data) return; | |
| // Prepare data for export | |
| const exportData = []; | |
| // Add header row | |
| exportData.push({ | |
| "Type": "Summary", | |
| "Count": "", | |
| "Details": "Filtered Comparison Results" | |
| }); | |
| // Add summary rows | |
| exportData.push({ | |
| "Type": "Common Records (CNA in both files)", | |
| "Count": document.getElementById('filtered-common-count').textContent, | |
| "Details": "" | |
| }); | |
| exportData.push({ | |
| "Type": "Unique to Enrolment List (CNA only)", | |
| "Count": document.getElementById('filtered-unique-file1-count').textContent, | |
| "Details": "" | |
| }); | |
| exportData.push({ | |
| "Type": "Unique to Staff List (Email only)", | |
| "Count": document.getElementById('filtered-unique-file2-count').textContent, | |
| "Details": "" | |
| }); | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add detailed records | |
| exportData.push({ | |
| "Type": "Common Records (CNA)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Get common records | |
| const file1CNA = new Set(); | |
| const file2Email = new Set(); | |
| filteredFile1Data.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| if (cna && typeof cna === 'string') { | |
| file1CNA.add(cna.trim().toLowerCase()); | |
| } | |
| }); | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (email && typeof email === 'string') { | |
| file2Email.add(email.trim().toLowerCase()); | |
| } | |
| }); | |
| const filteredCommon = new Set([...file1CNA].filter(cna => file2Email.has(cna))); | |
| if (filteredCommon.size === 0) { | |
| exportData.push({ | |
| "Type": "No common records found", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| filteredCommon.forEach(record => { | |
| exportData.push({ | |
| "Type": "Common", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add unique to File 1 records | |
| exportData.push({ | |
| "Type": "Unique to Enrolment List (CNA)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| const filteredUniqueToFile1 = new Set([...file1CNA].filter(cna => !file2Email.has(cna))); | |
| if (filteredUniqueToFile1.size === 0) { | |
| exportData.push({ | |
| "Type": "No unique records in Enrolment List", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| filteredUniqueToFile1.forEach(record => { | |
| exportData.push({ | |
| "Type": "Unique File 1", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add unique to File 2 records | |
| exportData.push({ | |
| "Type": "Unique to Staff List (Email)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| const filteredUniqueToFile2 = new Set([...file2Email].filter(email => !file1CNA.has(email))); | |
| if (filteredUniqueToFile2.size === 0) { | |
| exportData.push({ | |
| "Type": "No unique records in Staff List", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| filteredUniqueToFile2.forEach(record => { | |
| exportData.push({ | |
| "Type": "Unique File 2", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Create worksheet from export data | |
| const ws = XLSX.utils.json_to_sheet(exportData); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "Filtered Comparison Results"); | |
| // Export to file | |
| XLSX.writeFile(wb, "filtered_comparison_results.xlsx"); | |
| }); | |
| // Download non-filtered comparison results | |
| document.getElementById('download-non-filtered-btn').addEventListener('click', function() { | |
| if (!file1Data || !file2Data) return; | |
| // Prepare data for export | |
| const exportData = []; | |
| // Add header row | |
| exportData.push({ | |
| "Type": "Summary", | |
| "Count": "", | |
| "Details": "Non-Filtered Comparison Results" | |
| }); | |
| // Add summary rows | |
| exportData.push({ | |
| "Type": "Common Records (CNA in both files)", | |
| "Count": document.getElementById('non-filtered-common-count').textContent, | |
| "Details": "" | |
| }); | |
| exportData.push({ | |
| "Type": "Unique to Enrolment List (CNA only)", | |
| "Count": document.getElementById('non-filtered-unique-file1-count').textContent, | |
| "Details": "" | |
| }); | |
| exportData.push({ | |
| "Type": "Unique to Staff List (Email only)", | |
| "Count": document.getElementById('non-filtered-unique-file2-count').textContent, | |
| "Details": "" | |
| }); | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add detailed records | |
| exportData.push({ | |
| "Type": "Common Records (CNA)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Get common records | |
| const file1CNA = new Set(); | |
| const file2Email = new Set(); | |
| file1Data.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| if (cna && typeof cna === 'string') { | |
| file1CNA.add(cna.trim().toLowerCase()); | |
| } | |
| }); | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (email && typeof email === 'string') { | |
| file2Email.add(email.trim().toLowerCase()); | |
| } | |
| }); | |
| const nonFilteredCommon = new Set([...file1CNA].filter(cna => file2Email.has(cna))); | |
| if (nonFilteredCommon.size === 0) { | |
| exportData.push({ | |
| "Type": "No common records found", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| nonFilteredCommon.forEach(record => { | |
| exportData.push({ | |
| "Type": "Common", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add unique to File 1 records | |
| exportData.push({ | |
| "Type": "Unique to Enrolment List (CNA)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| const nonFilteredUniqueToFile1 = new Set([...file1CNA].filter(cna => !file2Email.has(cna))); | |
| if (nonFilteredUniqueToFile1.size === 0) { | |
| exportData.push({ | |
| "Type": "No unique records in Enrolment List", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| nonFilteredUniqueToFile1.forEach(record => { | |
| exportData.push({ | |
| "Type": "Unique File 1", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add unique to File 2 records | |
| exportData.push({ | |
| "Type": "Unique to Staff List (Email)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| const nonFilteredUniqueToFile2 = new Set([...file2Email].filter(email => !file1CNA.has(email))); | |
| if (nonFilteredUniqueToFile2.size === 0) { | |
| exportData.push({ | |
| "Type": "No unique records in Staff List", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| nonFilteredUniqueToFile2.forEach(record => { | |
| exportData.push({ | |
| "Type": "Unique File 2", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Create worksheet from export data | |
| const ws = XLSX.utils.json_to_sheet(exportData); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "Non-Filtered Comparison Results"); | |
| // Export to file | |
| XLSX.writeFile(wb, "non_filtered_comparison_results.xlsx"); | |
| }); | |
| // Download updated file 2 with status column | |
| document.getElementById('download-updated-file2-btn').addEventListener('click', function() { | |
| if (!file1Data || !file2Data) return; | |
| // Create sets for comparison | |
| const file1CNA = new Set(); | |
| const file2Email = new Set(); | |
| // Process filtered file1 data (CNA column) | |
| filteredFile1Data.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| if (cna && typeof cna === 'string') { | |
| file1CNA.add(cna.trim().toLowerCase()); | |
| } | |
| }); | |
| // Process file2 data (Email column) | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (email && typeof email === 'string') { | |
| file2Email.add(email.trim().toLowerCase()); | |
| } | |
| }); | |
| // Create updated file2 data with status column | |
| const updatedFile2Data = file2Data.map(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| let status = "Unique to File 2"; | |
| if (email && typeof email === 'string') { | |
| const emailKey = email.trim().toLowerCase(); | |
| if (file1CNA.has(emailKey)) { | |
| status = "Common records"; | |
| } | |
| } | |
| return { | |
| ...row, | |
| Status: status | |
| }; | |
| }); | |
| // Create worksheet from updated data | |
| const ws = XLSX.utils.json_to_sheet(updatedFile2Data); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "Updated File 2"); | |
| // Export to file | |
| XLSX.writeFile(wb, "updated_file2_with_status.xlsx"); | |
| }); | |
| // Download updated file 2 with status column (Non-Filtered) | |
| document.getElementById('download-updated-file2-non-filtered-btn').addEventListener('click', function() { | |
| if (!file1Data || !file2Data) return; | |
| // Create sets for comparison using original file1 data (non-filtered) | |
| const file1CNANonFiltered = new Set(); | |
| const file2Email = new Set(); | |
| // Process original file1 data (CNA column) - non-filtered | |
| file1Data.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| if (cna && typeof cna === 'string') { | |
| file1CNANonFiltered.add(cna.trim().toLowerCase()); | |
| } | |
| }); | |
| // Process file2 data (Email column) | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (email && typeof email === 'string') { | |
| file2Email.add(email.trim().toLowerCase()); | |
| } | |
| }); | |
| // Create updated file2 data with status column based on non-filtered comparison | |
| const updatedFile2DataNonFiltered = file2Data.map(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| let status = "Unique to File 2"; | |
| if (email && typeof email === 'string') { | |
| const emailKey = email.trim().toLowerCase(); | |
| if (file1CNANonFiltered.has(emailKey)) { | |
| status = "Common records"; | |
| } | |
| } | |
| return { | |
| ...row, | |
| Status: status | |
| }; | |
| }); | |
| // Create worksheet from updated data | |
| const ws = XLSX.utils.json_to_sheet(updatedFile2DataNonFiltered); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "Updated File 2 Non-Filtered"); | |
| // Export to file | |
| XLSX.writeFile(wb, "updated_file2_with_status_non_filtered.xlsx"); | |
| }); | |
| // Download File 3 vs File 2 comparison | |
| document.getElementById('download-f3-vs-f2-btn').addEventListener('click', function() { | |
| if (!filteredFile3Data || !file2Data) return; | |
| // Get only completed records from File 3 | |
| const completedFile3Records = filteredFile3Data.filter(row => { | |
| const m1Status = row['M1 Status'] || row['m1_status'] || row['M1_STATUS'] || row.m1Status; | |
| if (m1Status && typeof m1Status === 'string') { | |
| return m1Status.toString().trim().toLowerCase() === 'completed'; | |
| } | |
| return false; | |
| }); | |
| // Prepare data for export | |
| const exportData = []; | |
| // Add header row | |
| exportData.push({ | |
| "Type": "Summary", | |
| "Count": "", | |
| "Details": "File 3 (Completed) vs File 2 Comparison Results" | |
| }); | |
| // Add summary rows | |
| exportData.push({ | |
| "Type": "Common Records (CNA in both files)", | |
| "Count": document.getElementById('f3-vs-f2-common-count').textContent, | |
| "Details": "" | |
| }); | |
| exportData.push({ | |
| "Type": "Unique to File 3 (Completed CNA only)", | |
| "Count": document.getElementById('f3-vs-f2-unique-file3-count').textContent, | |
| "Details": "" | |
| }); | |
| exportData.push({ | |
| "Type": "Unique to File 2 (Email only)", | |
| "Count": document.getElementById('f3-vs-f2-unique-file2-count').textContent, | |
| "Details": "" | |
| }); | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Extract CNA from completed File 3 records | |
| const file3CNA = new Set(); | |
| const file3DataMap = new Map(); | |
| completedFile3Records.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| if (cna && typeof cna === 'string') { | |
| const cnaKey = cna.trim().toLowerCase(); | |
| file3CNA.add(cnaKey); | |
| file3DataMap.set(cnaKey, row); | |
| } | |
| }); | |
| // Extract Email from File 2 | |
| const file2Email = new Set(); | |
| const file2DataMap = new Map(); | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (email && typeof email === 'string') { | |
| const emailKey = email.trim().toLowerCase(); | |
| file2Email.add(emailKey); | |
| file2DataMap.set(emailKey, row); | |
| } | |
| }); | |
| // Calculate intersections and differences | |
| const common = new Set([...file3CNA].filter(cna => file2Email.has(cna))); | |
| const uniqueToFile3 = new Set([...file3CNA].filter(cna => !file2Email.has(cna))); | |
| const uniqueToFile2 = new Set([...file2Email].filter(email => !file3CNA.has(email))); | |
| // Add common records | |
| exportData.push({ | |
| "Type": "Common Records (CNA)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| if (common.size === 0) { | |
| exportData.push({ | |
| "Type": "No common records found", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| common.forEach(record => { | |
| exportData.push({ | |
| "Type": "Common", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add unique to File 3 records | |
| exportData.push({ | |
| "Type": "Unique to File 3 (Completed CNA)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| if (uniqueToFile3.size === 0) { | |
| exportData.push({ | |
| "Type": "No unique records in File 3", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| uniqueToFile3.forEach(record => { | |
| exportData.push({ | |
| "Type": "Unique File 3", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add unique to File 2 records | |
| exportData.push({ | |
| "Type": "Unique to File 2 (Email)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| if (uniqueToFile2.size === 0) { | |
| exportData.push({ | |
| "Type": "No unique records in File 2", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| uniqueToFile2.forEach(record => { | |
| exportData.push({ | |
| "Type": "Unique File 2", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Create worksheet from export data | |
| const ws = XLSX.utils.json_to_sheet(exportData); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "File 3 vs File 2 Comparison"); | |
| // Export to file | |
| XLSX.writeFile(wb, "file3_vs_file2_comparison.xlsx"); | |
| }); | |
| // Initialize download buttons as disabled | |
| document.getElementById('download-f3-vs-f2-btn').disabled = true; | |
| document.getElementById('download-first-module-date-btn').disabled = true; | |
| document.getElementById('download-m5-vs-f2-btn').disabled = true; | |
| document.getElementById('download-common-m5-not-y-btn').disabled = true; | |
| // Download M5 (Y) vs File 2 comparison | |
| document.getElementById('download-m5-vs-f2-btn').addEventListener('click', function() { | |
| if (!filteredFile3Data || !file2Data) return; | |
| // Get only records where M5 = "Y" from File 3 | |
| const m5YRecords = filteredFile3Data.filter(row => { | |
| const m5Value = row['M5'] || row['m5'] || row['M5_STATUS'] || row.m5; | |
| if (m5Value && typeof m5Value === 'string') { | |
| return m5Value.toString().trim().toUpperCase() === 'Y'; | |
| } | |
| return false; | |
| }); | |
| // Prepare data for export | |
| const exportData = []; | |
| // Add header row | |
| exportData.push({ | |
| "Type": "Summary", | |
| "Count": "", | |
| "Details": "M5 (Y) vs File 2 Comparison Results" | |
| }); | |
| // Add summary rows | |
| exportData.push({ | |
| "Type": "M5 = 'Y' Total Records", | |
| "Count": document.getElementById('m5-y-count').textContent, | |
| "Details": "" | |
| }); | |
| exportData.push({ | |
| "Type": "Common Records (CNA in both files)", | |
| "Count": document.getElementById('m5-vs-f2-common-count').textContent, | |
| "Details": "" | |
| }); | |
| exportData.push({ | |
| "Type": "Unique to File 3 (M5=Y CNA only)", | |
| "Count": document.getElementById('m5-vs-f2-unique-file3-count').textContent, | |
| "Details": "" | |
| }); | |
| exportData.push({ | |
| "Type": "Unique to File 2 (Email only)", | |
| "Count": document.getElementById('m5-vs-f2-unique-file2-count').textContent, | |
| "Details": "" | |
| }); | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Extract CNA from M5 = "Y" File 3 records | |
| const m5File3CNA = new Set(); | |
| const m5File3DataMap = new Map(); | |
| m5YRecords.forEach(row => { | |
| const cna = row.cna || row.CNA || row.Cna; | |
| if (cna && typeof cna === 'string') { | |
| const cnaKey = cna.trim().toLowerCase(); | |
| m5File3CNA.add(cnaKey); | |
| m5File3DataMap.set(cnaKey, row); | |
| } | |
| }); | |
| // Extract Email from File 2 | |
| const file2Email = new Set(); | |
| const file2DataMap = new Map(); | |
| file2Data.forEach(row => { | |
| const email = row.email || row.EMAIL || row.Email; | |
| if (email && typeof email === 'string') { | |
| const emailKey = email.trim().toLowerCase(); | |
| file2Email.add(emailKey); | |
| file2DataMap.set(emailKey, row); | |
| } | |
| }); | |
| // Calculate intersections and differences | |
| const m5Common = new Set([...m5File3CNA].filter(cna => file2Email.has(cna))); | |
| const m5UniqueToFile3 = new Set([...m5File3CNA].filter(cna => !file2Email.has(cna))); | |
| const m5UniqueToFile2 = new Set([...file2Email].filter(email => !m5File3CNA.has(email))); | |
| // Add common records | |
| exportData.push({ | |
| "Type": "Common Records (CNA)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| if (m5Common.size === 0) { | |
| exportData.push({ | |
| "Type": "No common records found", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| m5Common.forEach(record => { | |
| exportData.push({ | |
| "Type": "Common", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add unique to File 3 records | |
| exportData.push({ | |
| "Type": "Unique to File 3 (M5=Y CNA)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| if (m5UniqueToFile3.size === 0) { | |
| exportData.push({ | |
| "Type": "No unique records in File 3", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| m5UniqueToFile3.forEach(record => { | |
| exportData.push({ | |
| "Type": "Unique File 3", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Add separator | |
| exportData.push({ | |
| "Type": "", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| // Add unique to File 2 records | |
| exportData.push({ | |
| "Type": "Unique to File 2 (Email)", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| if (m5UniqueToFile2.size === 0) { | |
| exportData.push({ | |
| "Type": "No unique records in File 2", | |
| "Count": "", | |
| "Details": "" | |
| }); | |
| } else { | |
| m5UniqueToFile2.forEach(record => { | |
| exportData.push({ | |
| "Type": "Unique File 2", | |
| "Count": "", | |
| "Details": record | |
| }); | |
| }); | |
| } | |
| // Create worksheet from export data | |
| const ws = XLSX.utils.json_to_sheet(exportData); | |
| // Create workbook and add worksheet | |
| const wb = XLSX.utils.book_new(); | |
| XLSX.utils.book_append_sheet(wb, ws, "M5 (Y) vs File 2 Comparison"); | |
| // Export to file | |
| XLSX.writeFile(wb, "m5_y_vs_file2_comparison.xlsx"); | |
| }); | |
| // Initialize feather icons | |
| feather.replace(); | |
| </script> | |
| <script src="https://huggingface.co/deepsite/deepsite-badge.js"></script> | |
| </body> | |
| </html> | |