TropangCNTC's picture
Update index.html
4685c41 verified
Raw History Blame Contribute Delete
40 kB
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <title>Report Generators (Daily & Weekly)</title> <!-- SheetJS Library for fast processing --> <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script> <!-- ExcelJS Library for 100% exact template preservation & logos --> <script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.3.0/exceljs.min.js"></script> <!-- JSZip Library for direct XML streaming (1M+ rows in 1 single file/sheet) --> <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script> <style> * { box-sizing: border-box; font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; } body { background-color: #f4f7f6; margin: 0; padding: 20px; } .main-wrapper { display: flex; gap: 20px; max-width: 1600px; margin: 0 auto; } .container { flex: 1; background: #ffffff; padding: 25px; border-radius: 8px; box-shadow: 0 4px 12px rgba(0,0,0,0.1); } h2 { margin-top: 0; color: #1f4e78; border-bottom: 2px solid #adb9ca; padding-bottom: 10px; font-size: 20px; } .grid { display: grid; grid-template-columns: 1fr 1fr; gap: 15px; margin-bottom: 20px; } .field { display: flex; flex-direction: column; } .full-width { grid-column: span 2; } label { font-weight: 600; margin-bottom: 5px; color: #333; font-size: 13px; } input[type="text"], input[type="date"], input[type="file"], select { padding: 9px; border: 1px solid #ccc; border-radius: 4px; font-size: 13px; background-color: #ffffff; } input[type="text"], input[type="date"], input[type="file"] { padding: 9px; border: 1px solid #ccc; border-radius: 4px; font-size: 13px; } input[type="file"] { background: #fafafa; cursor: pointer; } button.submit-btn { background-color: #1f4e78; color: white; border: none; padding: 12px 20px; font-size: 15px; font-weight: bold; border-radius: 4px; cursor: pointer; width: 100%; margin-top: 10px; transition: background-color 0.2s; } button.submit-btn:hover { background-color: #153755; } button.submit-btn:disabled { background-color: #a0aec0; cursor: not-allowed; } .hint { font-size: 11px; color: #666; margin-top: 4px; } .badge { display: inline-block; padding: 3px 8px; border-radius: 12px; font-size: 11px; font-weight: bold; margin-left: 6px; } .badge.online { background: #d4edda; color: #155724; } .badge.offline { background: #f8d7da; color: #721c24; } .status { margin-top: 15px; padding: 10px; border-radius: 4px; display: none; font-weight: 500; font-size: 13px; white-space: pre-wrap; } .status.success { background-color: #d4edda; color: #155724; border: 1px solid #c3e6cb; } .status.error { background-color: #f8d7da; color: #721c24; border: 1px solid #f5c6cb; } .status.info { background-color: #d1ecf1; color: #0c5460; border: 1px solid #bee5eb; } .progress-bar { width: 100%; background-color: #e2e8f0; border-radius: 4px; overflow: hidden; margin-top: 10px; display: none; } .progress-fill { height: 8px; width: 0%; background-color: #1f4e78; transition: width 0.2s; } @media (max-width: 1100px) { .main-wrapper { flex-direction: column; } } </style> </head> <body> <div class="main-wrapper"> <!-- LEFT COLUMN: DAILY CLIENT MAC ADDRESS REPORT GENERATOR --> <div class="container"> <h2>Daily Client MAC Address Report Generator</h2> <div class="grid"> <div class="field full-width"> <label> 1. TEMPLATE FILE STATUS <span id="dailyTemplateBadge" class="badge offline">Loading...</span> </label> <span class="hint">Auto-loaded: `[TEMPLATE] DAILY CLIENT MAC ADDRESS REPORT TEMPLATE.xlsx`</span> </div> <div class="field full-width"> <label for="dailyRawDataFile">2. UPLOAD RAW DATA FILE (.xlsx / .csv)</label> <input type="file" id="dailyRawDataFile" accept=".xlsx, .xls, .csv" /> <span class="hint">Processing starts Row 3. Blank Site Codes, non-ELRN, and blank Client MACs skipped.</span> </div> <div class="field full-width"> <label> 3. SITE DATABASE STATUS <span id="dailyJsonBadge" class="badge offline">Loading...</span> </label> <span class="hint">Auto-loaded: `e-learning site database.json`</span> </div> <div class="field"> <label for="dailyProject">PROJECT NAME</label> <input type="text" id="dailyProject" value="CONNECTIVITY ENHANCEMENT PROGRAM FOR E-LEARNING IN PUBLIC SCHOOL" readonly style="background-color: #e9ecef; cursor: not-allowed; font-weight: bold;" /> </div> <div class="field"> <label for="dailySupplier">SERVICE PROVIDER</label> <input type="text" id="dailySupplier" value="COMCLARK NETWORK AND TECHNOLOGY CORP." readonly style="background-color: #e9ecef; cursor: not-allowed; font-weight: bold;" /> </div> <div class="field"> <label for="dailyLotNumber">LOT NUMBER</label> <select id="dailyLotNumber"> <option value="">-- Select Lot Number --</option> <option value="LOT A">LOT A</option> <option value="LOT B">LOT B</option> <option value="LOT C">LOT C</option> </select> </div> <div class="field"> <label for="dailyReportDate" style="color: #dc3545;">REPORT DATE</label> <input type="date" id="dailyReportDate" /> </div> </div> <button id="dailyGenBtn" class="submit-btn" onclick="generateDailyReport()">Generate Daily Report</button> <div id="dailyProgressBar" class="progress-bar"> <div id="dailyProgressFill" class="progress-fill"></div> </div> <div id="dailyStatusMessage" class="status"></div> </div> <!-- RIGHT COLUMN: WEEKLY PERFORMANCE REPORT GENERATOR --> <div class="container"> <h2>Weekly Performance Report Generator</h2> <div class="grid"> <div class="field full-width"> <label>1. TEMPLATE FILE STATUS <span id="weeklyTemplateBadge" class="badge offline">Loading...</span></label> <span class="hint">Auto-loaded: `[TEMPLATE] WEEKLY PERFORMANCE REPORT.xlsx`</span> </div> <div class="field full-width"> <label for="weeklyRawDataFile">2. UPLOAD RAW DATA FILE (.xlsx / .csv)</label> <input type="file" id="weeklyRawDataFile" accept=".xlsx, .xls, .csv" /> <span class="hint">Processing starts Row 9. Populates Col A to E accurately.</span> </div> <div class="field full-width"> <label>3. SITE DATABASE STATUS <span id="weeklyJsonBadge" class="badge offline">Loading...</span></label> <span class="hint">Auto-loaded: `e-learning site database.json`</span> </div> <div class="field"> <label for="weeklyProject">PROJECT NAME</label> <input type="text" id="weeklyProject" value="CONNECTIVITY ENHANCEMENT PROGRAM FOR E-LEARNING IN PUBLIC SCHOOL" readonly style="background-color: #e9ecef; cursor: not-allowed; font-weight: bold;" /> </div> <div class="field"> <label for="weeklySupplier">SERVICE PROVIDER</label> <input type="text" id="weeklySupplier" value="COMCLARK NETWORK AND TECHNOLOGY CORP." readonly style="background-color: #e9ecef; cursor: not-allowed; font-weight: bold;" /> </div> <div class="field"> <label for="weeklyLotNumber">LOT NUMBER</label> <select id="weeklyLotNumber"> <option value="">-- Select Lot Number --</option> <option value="LOT A">LOT A</option> <option value="LOT B">LOT B</option> <option value="LOT C">LOT C</option> </select> </div> <div class="field"> <label for="weeklyReportDate" style="color: #dc3545;">REPORT PERIOD / DURATION</label> <input type="text" id="weeklyReportDate" placeholder="e.g. November 1 - 7, 2026" /> </div> </div> <button id="weeklyGenBtn" class="submit-btn" onclick="generateWeeklyReport()">Generate Weekly Report</button> <div id="weeklyProgressBar" class="progress-bar"> <div id="weeklyProgressFill" class="progress-fill"></div> </div> <div id="weeklyStatusMessage" class="status"></div> </div> </div> <script> let loadedSiteDatabase = {}; let cachedDailyTemplateArrayBuffer = null; let cachedWeeklyTemplateArrayBuffer = null; document.getElementById('dailyReportDate').valueAsDate = new Date(); // Extract YYYY-MM-DD date from raw filename (supports YYYY-MM-DD, YYYY_MM_DD, YYYYMMDD, MM-DD-YYYY) function extractDateFromFilename(filename) { if (!filename) return null; // Match YYYY-MM-DD or YYYY_MM_DD or YYYY.MM.DD let m = filename.match(/(20\d{2})[-_.](\d{2})[-_.](\d{2})/); if (m) return `${m[1]}-${m[2]}-${m[3]}`; // Match compact YYYYMMDD m = filename.match(/(20\d{2})(\d{2})(\d{2})/); if (m) return `${m[1]}-${m[2]}-${m[3]}`; // Match MM-DD-YYYY or MM_DD_YYYY m = filename.match(/(\d{2})[-_.](\d{2})[-_.](20\d{2})/); if (m) return `${m[3]}-${m[1]}-${m[2]}`; return null; } // Auto-detect date when user selects a Daily Raw Data file document.getElementById('dailyRawDataFile').addEventListener('change', function () { if (this.files && this.files.length > 0) { const detectedDate = extractDateFromFilename(this.files[0].name); if (detectedDate) { document.getElementById('dailyReportDate').value = detectedDate; showStatus('dailyStatusMessage', `Auto-matched Report Date (${detectedDate}) from raw filename.`, 'info'); } } }); // Extract formatted Weekly Period (handles same-month, cross-month, and cross-year) function extractWeeklyPeriodFromFilename(filename) { if (!filename) return null; const dates = filename.match(/(20\d{2})[-_.](\d{2})[-_.](\d{2})/g); if (!dates || dates.length < 2) return null; const startParts = dates[0].split(/[-_.]/); const endParts = dates[1].split(/[-_.]/); const months = [ "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" ]; const startYear = parseInt(startParts[0], 10); const startMonthIdx = parseInt(startParts[1], 10) - 1; const startDay = parseInt(startParts[2], 10); const endYear = parseInt(endParts[0], 10); const endMonthIdx = parseInt(endParts[1], 10) - 1; const endDay = parseInt(endParts[2], 10); if (startMonthIdx < 0 || startMonthIdx > 11 || endMonthIdx < 0 || endMonthIdx > 11) return null; const startMonth = months[startMonthIdx]; const endMonth = months[endMonthIdx]; if (startYear === endYear && startMonth === endMonth) { return `${startMonth} ${startDay} - ${endDay}, ${endYear}`.toUpperCase(); } else if (startYear === endYear) { return `${startMonth} ${startDay} - ${endMonth} ${endDay}, ${endYear}`.toUpperCase(); } else { return `${startMonth} ${startDay}, ${startYear} - ${endMonth} ${endDay}, ${endYear}`.toUpperCase(); } } // Global listener: Auto-paste Report Period when Weekly Raw Data file is chosen document.addEventListener('change', function (e) { const target = e.target; if (!target || target.type !== 'file' || !target.files || target.files.length === 0) return; const filename = target.files[0].name; const isWeeklyInput = (target.id && target.id.toLowerCase().includes('weekly')) || (filename.match(/(20\d{2})[-_.](\d{2})[-_.](\d{2})/g) || []).length >= 2; if (isWeeklyInput) { const detectedPeriod = extractWeeklyPeriodFromFilename(filename); const periodInput = document.getElementById('weeklyPeriod') || document.getElementById('weeklyReportPeriod') || document.querySelector('input[placeholder*="November 1 - 7"]'); if (detectedPeriod && periodInput) { periodInput.value = detectedPeriod; if (typeof showStatus === 'function') { showStatus('weeklyStatusMessage', `Auto-matched Report Period (${detectedPeriod}) from raw filename.`, 'info'); } } } }); function formatMacAddress(macStr) { if (!macStr) return ""; const str = macStr.toString().trim(); if (str.length === 0) return ""; const cleanHex = str.replace(/[^a-fA-F0-9]/g, "").toUpperCase(); if (cleanHex.length === 12) { return cleanHex.substring(0,2) + ":" + cleanHex.substring(2,4) + ":" + cleanHex.substring(4,6) + ":" + cleanHex.substring(6,8) + ":" + cleanHex.substring(8,10) + ":" + cleanHex.substring(10,12); } return str.toUpperCase(); } function getProp(obj, keys) { if (!obj) return ""; for (let k of keys) { if (obj[k] !== undefined && obj[k] !== null) return obj[k]; } const objKeys = Object.keys(obj); for (let targetKey of keys) { const foundKey = objKeys.find(k => k.toLowerCase() === targetKey.toLowerCase()); if (foundKey && obj[foundKey] !== undefined && obj[foundKey] !== null) { return obj[foundKey]; } } return ""; } async function autoLoadTemplates() { // 1. Daily Template Load const dailyBadge = document.getElementById('dailyTemplateBadge'); const dailyFilename = encodeURIComponent('[TEMPLATE] DAILY CLIENT MAC ADDRESS REPORT TEMPLATE.xlsx'); try { const resp = await fetch(`./${dailyFilename}`, { cache: "no-store" }); if (!resp.ok) throw new Error(`HTTP ${resp.status}`); cachedDailyTemplateArrayBuffer = await resp.arrayBuffer(); dailyBadge.className = "badge online"; dailyBadge.innerText = "Ready"; } catch (err) { dailyBadge.className = "badge offline"; dailyBadge.innerText = "Error Loading"; } // 2. Weekly Template Load const weeklyBadge = document.getElementById('weeklyTemplateBadge'); const weeklyFilename = encodeURIComponent('[TEMPLATE] WEEKLY PERFORMANCE REPORT.xlsx'); try { const resp = await fetch(`./${weeklyFilename}`, { cache: "no-store" }); if (!resp.ok) throw new Error(`HTTP ${resp.status}`); cachedWeeklyTemplateArrayBuffer = await resp.arrayBuffer(); weeklyBadge.className = "badge online"; weeklyBadge.innerText = "Ready"; } catch (err) { weeklyBadge.className = "badge offline"; weeklyBadge.innerText = "Error Loading"; } } async function autoLoadJsonDatabase() { const dailyBadge = document.getElementById('dailyJsonBadge'); const weeklyBadge = document.getElementById('weeklyJsonBadge'); const filename = encodeURIComponent('e-learning site database.json'); try { const response = await fetch(`./${filename}`, { cache: "no-store" }); if (!response.ok) throw new Error(`HTTP ${response.status}`); const jsonData = await response.json(); parseAndSetJsonData(jsonData); const count = Object.keys(loadedSiteDatabase).length; dailyBadge.className = "badge online"; dailyBadge.innerText = `Ready (${count} Sites)`; weeklyBadge.className = "badge online"; weeklyBadge.innerText = `Ready (${count} Sites)`; } catch (err) { dailyBadge.className = "badge offline"; dailyBadge.innerText = "Error"; weeklyBadge.className = "badge offline"; weeklyBadge.innerText = "Error"; } } function parseAndSetJsonData(jsonData) { const items = Array.isArray(jsonData) ? jsonData : [jsonData]; loadedSiteDatabase = {}; items.forEach(item => { const scKey = getProp(item, ["Site Code", "SITE CODE", "site_code", "SiteCode"]).toString().trim().toUpperCase(); if (scKey) { loadedSiteDatabase[scKey] = { siteName: getProp(item, ["Site Name", "SITE NAME", "site_name", "SiteName"]), region: getProp(item, ["Region", "REGION", "region"]), province: getProp(item, ["Province", "PROVINCE", "province"]) }; } }); } window.addEventListener('DOMContentLoaded', () => { autoLoadTemplates(); autoLoadJsonDatabase(); }); function formatDateForFilename(dateStr) { let dt = dateStr ? new Date(dateStr) : new Date(); if (isNaN(dt.getTime())) dt = new Date(); const months = ["JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST", "SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER"]; const monthName = months[dt.getUTCMonth()]; const day = dt.getUTCDate(); const year = dt.getUTCFullYear(); return `${monthName} ${day}, ${year}`; } function formatDateString(dateStr) { let dt = dateStr ? new Date(dateStr) : new Date(); if (isNaN(dt.getTime())) dt = new Date(); const months = ["JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST", "SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER"]; const monthName = months[dt.getUTCMonth()]; const day = dt.getUTCDate(); const year = dt.getUTCFullYear(); return `: ${monthName} ${day}, ${year} (00:00 - 23:59)`; } const sleep = (ms) => new Promise(resolve => setTimeout(resolve, ms)); function downloadExcelBuffer(buffer, filename) { const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }); const url = window.URL.createObjectURL(blob); const a = document.createElement('a'); a.href = url; a.download = filename; document.body.appendChild(a); a.click(); document.body.removeChild(a); window.URL.revokeObjectURL(url); } // --- 1. DAILY REPORT GENERATOR FUNCTION (1 SINGLE .XLSX FILE, 1 SINGLE SHEET, 1M+ ROWS) --- async function generateDailyReport() { const genBtn = document.getElementById('dailyGenBtn'); const rawFileInput = document.getElementById('dailyRawDataFile'); const project = document.getElementById('dailyProject').value.trim() || "CONNECTIVITY ENHANCEMENT PROGRAM FOR E-LEARNING IN PUBLIC SCHOOL"; const supplier = document.getElementById('dailySupplier').value.trim() || "COMCLARK NETWORK AND TECHNOLOGY CORP."; const lotNumber = document.getElementById('dailyLotNumber').value.trim(); const dateVal = document.getElementById('dailyReportDate').value; const progressFill = document.getElementById('dailyProgressFill'); const progressBar = document.getElementById('dailyProgressBar'); if (!rawFileInput.files || rawFileInput.files.length === 0) { showStatus('dailyStatusMessage', 'Please select and upload a Raw Data File.', 'error'); return; } genBtn.disabled = true; genBtn.innerText = "Processing Data... Please Wait"; progressBar.style.display = 'block'; progressFill.style.width = '5%'; showStatus('dailyStatusMessage', 'Converting raw file to lightweight CSV stream...', 'info'); await sleep(50); try { if (!cachedDailyTemplateArrayBuffer) { throw new Error("Daily Template file auto-load failed."); } const file = rawFileInput.files[0]; const isCsv = file.name.toLowerCase().endsWith('.csv'); let cleanDataRows = []; let skippedCount = 0; const processRowValues = (rawSiteCode, rawSiteName, apMacRaw, rawRegion, rawProvince, clientMacRaw) => { let effectiveSiteCode = rawSiteCode ? rawSiteCode.toString().trim() : ''; let effectiveSiteName = rawSiteName ? rawSiteName.toString().trim() : ''; let cleanSiteCode = effectiveSiteCode.toUpperCase(); // FALLBACK LOGIC: If Site Code (Col A) is blank/invalid, check if Site Name (Col B) starts with an ELRN code (e.g. "ELRN-L3-0404 Sto. Rosario NHS") if (!effectiveSiteCode || cleanSiteCode === '' || cleanSiteCode === 'NAN' || cleanSiteCode === 'NULL' || cleanSiteCode === 'UNDEFINED' || !cleanSiteCode.startsWith("ELRN")) { const siteNameElrnMatch = effectiveSiteName.match(/^(ELRN-[A-Z0-9]+-[A-Z0-9]+)\s*(.*)$/i); if (siteNameElrnMatch) { effectiveSiteCode = siteNameElrnMatch[1].toUpperCase(); cleanSiteCode = effectiveSiteCode; // Remove the extracted ELRN code from Site Name fallback effectiveSiteName = siteNameElrnMatch[2] ? siteNameElrnMatch[2].trim() : effectiveSiteName; } } // Skip if still blank or non-ELRN after checking both Col A and Col B if (!effectiveSiteCode || cleanSiteCode === '' || cleanSiteCode === 'NAN' || cleanSiteCode === 'NULL' || cleanSiteCode === 'UNDEFINED') { skippedCount++; return; } if (!cleanSiteCode.startsWith("ELRN")) { skippedCount++; return; } const cleanClientMac = clientMacRaw.toUpperCase(); if (!clientMacRaw || cleanClientMac === '' || cleanClientMac === 'NAN' || cleanClientMac === 'NULL' || cleanClientMac === 'UNDEFINED') { skippedCount++; return; } const formattedApMac = formatMacAddress(apMacRaw); const formattedClientMac = formatMacAddress(clientMacRaw); let finalSiteName = effectiveSiteName; let finalRegion = rawRegion; let finalProvince = rawProvince; if (loadedSiteDatabase[cleanSiteCode]) { finalSiteName = loadedSiteDatabase[cleanSiteCode].siteName || effectiveSiteName; finalRegion = loadedSiteDatabase[cleanSiteCode].region || rawRegion; finalProvince = loadedSiteDatabase[cleanSiteCode].province || rawProvince; } cleanDataRows.push([ effectiveSiteCode, finalSiteName, formattedApMac, finalRegion, finalProvince, formattedClientMac, "" ]); }; const parseCsvLine = (line) => { const cols = []; let cur = ''; let inQuotes = false; for (let j = 0; j < line.length; j++) { const ch = line[j]; if (ch === '"') { inQuotes = !inQuotes; } else if (ch === ',' && !inQuotes) { cols.push(cur.trim()); cur = ''; } else { cur += ch; } } cols.push(cur.trim()); return cols; }; // STEP 1: Convert input file to CSV Stream (auto-handles .xlsx/.xls or .csv) let csvStream = null; let totalBytes = 0; if (isCsv) { csvStream = file.stream(); totalBytes = file.size; } else { let rawFileBuffer = await file.arrayBuffer(); let rawWorkbook = XLSX.read(rawFileBuffer, { type: 'array', dense: true }); rawFileBuffer = null; const rawSheet = rawWorkbook.Sheets[rawWorkbook.SheetNames[0]]; const csvString = XLSX.utils.sheet_to_csv(rawSheet); rawWorkbook = null; const csvBlob = new Blob([csvString], { type: 'text/csv' }); csvStream = csvBlob.stream(); totalBytes = csvBlob.size; } // STEP 2: Stream-parse CSV lines const reader = csvStream.getReader(); const decoder = new TextDecoder('utf-8'); let leftover = ''; let rowIndex = 0; let bytesRead = 0; while (true) { const { done, value } = await reader.read(); if (done) break; bytesRead += value.byteLength; const chunkText = leftover + decoder.decode(value, { stream: true }); const lines = chunkText.split(/\r?\n/); leftover = lines.pop(); for (let l = 0; l < lines.length; l++) { if (rowIndex >= 2 && lines[l].trim() !== '') { const c = parseCsvLine(lines[l]); processRowValues(c[0] || '', c[1] || '', c[2] || '', c[3] || '', c[4] || '', c[5] || ''); } rowIndex++; } if (rowIndex % 50000 === 0) { progressFill.style.width = `${10 + Math.floor((bytesRead / Math.max(1, totalBytes)) * 40)}%`; showStatus('dailyStatusMessage', `Filtering CSV stream: ${rowIndex.toLocaleString()} rows scanned...`, 'info'); await sleep(10); } } if (leftover && rowIndex >= 2 && leftover.trim() !== '') { const c = parseCsvLine(leftover); processRowValues(c[0] || '', c[1] || '', c[2] || '', c[3] || '', c[4] || '', c[5] || ''); } const totalValid = cleanDataRows.length; if (totalValid > 1048564) { throw new Error(`Valid rows (${totalValid.toLocaleString()}) exceed Excel's maximum single-sheet limit of 1,048,576 rows.`); } // STEP 3: Prepare exact template shell (1 row sample) via ExcelJS so all logos, styles, metadata & red B7 are 100% preserved progressFill.style.width = '55%'; showStatus('dailyStatusMessage', `Preparing exact template shell for ${totalValid.toLocaleString()} rows in 1 single sheet...`, 'info'); await sleep(50); let shellWorkbook = new ExcelJS.Workbook(); await shellWorkbook.xlsx.load(cachedDailyTemplateArrayBuffer); const shellSheet = shellWorkbook.worksheets[0]; if (project) shellSheet.getCell('B4').value = ": " + project; if (supplier) shellSheet.getCell('B5').value = ": " + supplier; if (lotNumber) shellSheet.getCell('B6').value = ": " + lotNumber; shellSheet.getCell('B7').value = formatDateString(dateVal); shellSheet.getCell('B7').font = { name: 'Calibri', size: 11, bold: true, color: { argb: 'FFFF0000' } }; // Write 1 sample row at Row 12 so ExcelJS generates the exact cell style IDs (s="...") for A12:G12 const templateRow = shellSheet.getRow(12); const defaultBorder = { top: { style: 'thin', color: { argb: 'FF000000' } }, left: { style: 'thin', color: { argb: 'FF000000' } }, bottom: { style: 'thin', color: { argb: 'FF000000' } }, right: { style: 'thin', color: { argb: 'FF000000' } } }; for (let c = 1; c <= 7; c++) { const cell = templateRow.getCell(c); cell.value = "SAMPLE"; if (!cell.border) cell.border = defaultBorder; } templateRow.commit(); const shellBuffer = await shellWorkbook.xlsx.writeBuffer({ useSharedStrings: false, useStyles: true }); shellWorkbook = null; // STEP 4: Stream-inject all 700k–1M+ rows directly into xl/worksheets/sheet1.xml as byte chunks const zip = await JSZip.loadAsync(shellBuffer); // STEP 4: Stream-inject all 700k–1M+ rows directly into xl/worksheets/sheet1.xml as byte chunks (Bypasses V8 512MB String Limit!) const originalSheetXml = await zip.file("xl/worksheets/sheet1.xml").async("string"); // Extract exact style index (s="...") from Row 12 cells A12..G12 const row12Match = originalSheetXml.match(/<row r="12"[^>]*>([\s\S]*?)<\/row>/); const colStyleAttrs = ['', '', '', '', '', '', '']; const colLetters = ['A', 'B', 'C', 'D', 'E', 'F', 'G']; if (row12Match) { for (let c = 0; c < 7; c++) { const cellRegex = new RegExp(`<c r="${colLetters[c]}12"([^>]*)>`); const m = row12Match[1].match(cellRegex); if (m && m[1]) { const sMatch = m[1].match(/s="\d+"/); if (sMatch) colStyleAttrs[c] = ` ${sMatch[0]}`; } } } // Split original sheet1.xml right before <row r="12" and right after </sheetData> const splitBeforeRow12 = originalSheetXml.indexOf('<row r="12"'); const splitAfterSheetData = originalSheetXml.indexOf('</sheetData>'); const xmlHeader = originalSheetXml.substring(0, splitBeforeRow12); const xmlFooter = originalSheetXml.substring(splitAfterSheetData); const escapeXml = (str) => { if (!str) return ''; return str.replace(/&/g, '&amp;') .replace(/</g, '&lt;') .replace(/>/g, '&gt;') .replace(/"/g, '&quot;') .replace(/'/g, '&apos;'); }; const encoder = new TextEncoder(); const xmlByteChunks = [encoder.encode(xmlHeader)]; const BATCH_SIZE = 10000; for (let i = 0; i < totalValid; i += BATCH_SIZE) { const end = Math.min(i + BATCH_SIZE, totalValid); let batchXml = ''; for (let r = i; r < end; r++) { const excelRowNum = 12 + r; const rowVals = cleanDataRows[r]; batchXml += `<row r="${excelRowNum}">`; for (let c = 0; c < 7; c++) { const val = rowVals[c]; if (val !== '') { batchXml += `<c r="${colLetters[c]}${excelRowNum}"${colStyleAttrs[c]} t="inlineStr"><is><t>${escapeXml(val)}</t></is></c>`; } else { batchXml += `<c r="${colLetters[c]}${excelRowNum}"${colStyleAttrs[c]}/>`; } } batchXml += `</row>`; } xmlByteChunks.push(encoder.encode(batchXml)); if (i % 50000 === 0) { progressFill.style.width = `${55 + Math.floor((i / totalValid) * 35)}%`; showStatus('dailyStatusMessage', `Streaming XML rows: ${end.toLocaleString()} / ${totalValid.toLocaleString()}...`, 'info'); await sleep(10); } } cleanDataRows = null; xmlByteChunks.push(encoder.encode(xmlFooter)); progressFill.style.width = '92%'; showStatus('dailyStatusMessage', 'Compressing single Excel (.xlsx) file...', 'info'); await sleep(50); const sheetXmlBlob = new Blob(xmlByteChunks, { type: 'application/xml' }); zip.file("xl/worksheets/sheet1.xml", sheetXmlBlob); const formattedDateForFile = formatDateForFilename(dateVal); const lotSuffix = lotNumber ? `_${lotNumber}` : ''; const outputFilename = `DAILY CLIENT MAC ADDRESS REPORT${lotSuffix}_${formattedDateForFile}.xlsx`; const finalBuffer = await zip.generateAsync({ type: 'blob', compression: 'DEFLATE', compressionOptions: { level: 3 } }); const url = window.URL.createObjectURL(finalBuffer); const a = document.createElement('a'); a.href = url; a.download = outputFilename; document.body.appendChild(a); a.click(); document.body.removeChild(a); window.URL.revokeObjectURL(url); progressFill.style.width = '100%'; showStatus('dailyStatusMessage', `Success! Generated 1 Single Daily Report Excel file (${totalValid.toLocaleString()} valid rows in 1 sheet, ${skippedCount.toLocaleString()} skipped).`, 'success'); } catch (err) { showStatus('dailyStatusMessage', 'Error during generation: ' + err.message, 'error'); } finally { genBtn.disabled = false; genBtn.innerText = "Generate Daily Report"; setTimeout(() => { progressBar.style.display = 'none'; }, 3000); } } // --- 2. WEEKLY PERFORMANCE REPORT GENERATOR FUNCTION --- async function generateWeeklyReport() { const genBtn = document.getElementById('weeklyGenBtn'); const rawFileInput = document.getElementById('weeklyRawDataFile'); const progressFill = document.getElementById('weeklyProgressFill'); const progressBar = document.getElementById('weeklyProgressBar'); if (!rawFileInput.files || rawFileInput.files.length === 0) { showStatus('weeklyStatusMessage', 'Please select and upload a Weekly Raw Data File.', 'error'); return; } genBtn.disabled = true; genBtn.innerText = "Processing Data... Please Wait"; progressBar.style.display = 'block'; progressFill.style.width = '10%'; showStatus('weeklyStatusMessage', 'Reading weekly raw data and exact template with logos...', 'info'); await sleep(50); try { if (!cachedWeeklyTemplateArrayBuffer) { throw new Error("Weekly Template file auto-load failed."); } const workbook = new ExcelJS.Workbook(); await workbook.xlsx.load(cachedWeeklyTemplateArrayBuffer); const worksheet = workbook.worksheets[0]; const rawFileBuffer = await rawFileInput.files[0].arrayBuffer(); const rawWorkbook = XLSX.read(rawFileBuffer, { type: 'array', dense: true }); const rawSheet = rawWorkbook.Sheets[rawWorkbook.SheetNames[0]]; const rawMatrix = XLSX.utils.sheet_to_json(rawSheet, { header: 1, raw: false, defval: '' }); progressFill.style.width = '35%'; showStatus('weeklyStatusMessage', `Filtering records starting from Row 9 (${rawMatrix.length.toLocaleString()} raw rows)...`, 'info'); await sleep(50); let cleanDataRows = []; let skippedCount = 0; const totalRaw = rawMatrix.length; const parseToNumber = (val) => { if (val === undefined || val === null || val === '') return ''; const cleanStr = val.toString().replace(/,/g, '').trim(); const num = Number(cleanStr); return !isNaN(num) && cleanStr !== '' ? num : val; }; for (let i = 8; i < totalRaw; i++) { const row = rawMatrix[i]; if (!row || row.length === 0) continue; const rawSiteCodeInput = row[0] !== undefined && row[0] !== null ? row[0].toString().trim() : ''; const elrnMatch = rawSiteCodeInput.match(/ELRN-[A-Z0-9]+-[A-Z0-9]+/i); if (!elrnMatch) { skippedCount++; continue; } const rawSiteCode = elrnMatch[0].toUpperCase(); const cleanSiteCode = rawSiteCode; let dbSiteName = ""; if (loadedSiteDatabase[cleanSiteCode]) { dbSiteName = loadedSiteDatabase[cleanSiteCode].siteName || ""; } const rawColC = parseToNumber(row[2]); const rawColD = parseToNumber(row[3]); const rawColE = parseToNumber(row[4]); cleanDataRows.push([ rawSiteCode, dbSiteName, rawColC, rawColD, rawColE ]); if (i % 200000 === 0) { progressFill.style.width = `${35 + Math.floor((i / totalRaw) * 25)}%`; await sleep(10); } } progressFill.style.width = '70%'; showStatus('weeklyStatusMessage', `Pasting ${cleanDataRows.length.toLocaleString()} clean rows into template preserving exact logo...`, 'info'); await sleep(50); const project = document.getElementById('weeklyProject') ? document.getElementById('weeklyProject').value.trim() : ''; const supplier = document.getElementById('weeklySupplier') ? document.getElementById('weeklySupplier').value.trim() : ''; const lotNumber = document.getElementById('weeklyLotNumber') ? document.getElementById('weeklyLotNumber').value.trim() : ''; const dateVal = document.getElementById('weeklyReportDate') ? document.getElementById('weeklyReportDate').value.trim() : ''; // --- AUTO-PASTE METADATA SA CELLS B4, B5, B6, B7 (PURE BLACK FONT / NO RED) --- if (project) { const cellB4 = worksheet.getCell('B4'); cellB4.value = ": " + project; cellB4.font = { name: 'Calibri', size: 11, bold: true, color: { argb: 'FF000000' } }; } if (supplier) { const cellB5 = worksheet.getCell('B5'); cellB5.value = ": " + supplier; cellB5.font = { name: 'Calibri', size: 11, bold: true, color: { argb: 'FF000000' } }; } if (lotNumber) { const cellB6 = worksheet.getCell('B6'); cellB6.value = ": " + lotNumber; cellB6.font = { name: 'Calibri', size: 11, bold: true, color: { argb: 'FF000000' } }; } if (dateVal) { const cellB7 = worksheet.getCell('B7'); cellB7.value = ": " + dateVal; cellB7.font = { name: 'Calibri', size: 11, bold: true, color: { argb: 'FF000000' } }; } // I-FORCE PA-BLACK DIN ANG LABELS SA COLUMN A (A4 to A7) PARA SIGURADONG WALA NANG RED for (let r = 4; r <= 7; r++) { worksheet.getCell(`A${r}`).font = { name: 'Calibri', size: 11, bold: true, color: { argb: 'FF000000' } }; } // ----------------------------------------------------------------- const templateRow = worksheet.getRow(11); const templateStyles = []; for (let c = 1; c <= 8; c++) { const sampleCell = templateRow.getCell(c); templateStyles[c] = { font: sampleCell.font ? JSON.parse(JSON.stringify(sampleCell.font)) : undefined, alignment: sampleCell.alignment ? JSON.parse(JSON.stringify(sampleCell.alignment)) : undefined, numFmt: sampleCell.numFmt || undefined }; } let startRow = 11; let totalDataRows = cleanDataRows.length; for (let r = 0; r < totalDataRows; r++) { const rowData = cleanDataRows[r]; const currentRowIndex = startRow + r; const row = worksheet.getRow(currentRowIndex); for (let c = 1; c <= 8; c++) { const cell = row.getCell(c); if (c <= 5) { cell.value = rowData[c - 1]; } const style = templateStyles[c]; cell.style = { font: style && style.font ? JSON.parse(JSON.stringify(style.font)) : undefined, alignment: style && style.alignment ? JSON.parse(JSON.stringify(style.alignment)) : undefined, numFmt: style ? style.numFmt : undefined, border: { top: { style: 'thin', color: { argb: 'FF000000' } }, left: { style: 'thin', color: { argb: 'FF000000' } }, bottom: { style: 'thin', color: { argb: 'FF000000' } }, right: { style: 'thin', color: { argb: 'FF000000' } } } }; } if (r % 50000 === 0) { progressFill.style.width = `${70 + Math.floor((r / totalDataRows) * 20)}%`; await sleep(10); } } for (let r = 1; r <= 9; r++) { const headerRow = worksheet.getRow(r); for (let c = 1; c <= 8; c++) { headerRow.getCell(c).border = undefined; } } progressFill.style.width = '95%'; showStatus('weeklyStatusMessage', 'Exporting final report with exact template layout...', 'info'); await sleep(50); const periodSuffix = dateVal ? `_${dateVal}` : ''; const lotSuffix = lotNumber ? `_${lotNumber}` : ''; const outputFilename = `WEEKLY PERFORMANCE REPORT${lotSuffix}${periodSuffix}.xlsx`; const buffer = await workbook.xlsx.writeBuffer(); const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }); const url = window.URL.createObjectURL(blob); const a = document.createElement('a'); a.href = url; a.download = outputFilename; // Dynamic output filename fixed! document.body.appendChild(a); a.click(); document.body.removeChild(a); window.URL.revokeObjectURL(url); progressFill.style.width = '100%'; showStatus('weeklyStatusMessage', `Success! Generated exact Weekly Report (${skippedCount.toLocaleString()} non-ELRN/blank rows skipped).`, 'success'); } catch (err) { showStatus('weeklyStatusMessage', 'Error during generation: ' + err.message, 'error'); } finally { genBtn.disabled = false; genBtn.innerText = "Generate Weekly Report"; setTimeout(() => { progressBar.style.display = 'none'; }, 3000); } } function showStatus(elemId, msg, type) { const el = document.getElementById(elemId); el.className = 'status ' + type; el.style.display = 'block'; el.innerText = msg; } </script> </body> </html>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Report Generators (Daily & Weekly)</title>
<!-- SheetJS Library for fast processing -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<!-- ExcelJS Library for 100% exact template preservation & logos -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.3.0/exceljs.min.js"></script>
<!-- JSZip Library for direct XML streaming (1M+ rows in 1 single file/sheet) -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<style>
* {
box-sizing: border-box;
font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
}
body {
background-color: #f4f7f6;
margin: 0;
padding: 20px;
}
.main-wrapper {
display: flex;
gap: 20px;
max-width: 1600px;
margin: 0 auto;
}
.container {
flex: 1;
background: #ffffff;
padding: 25px;
border-radius: 8px;
box-shadow: 0 4px 12px rgba(0,0,0,0.1);
}
h2 {
margin-top: 0;
color: #1f4e78;
border-bottom: 2px solid #adb9ca;
padding-bottom: 10px;
font-size: 20px;
}
.grid {
display: grid;
grid-template-columns: 1fr 1fr;
gap: 15px;
margin-bottom: 20px;
}
.field {
display: flex;
flex-direction: column;
}
.full-width {
grid-column: span 2;
}
label {
font-weight: 600;
margin-bottom: 5px;
color: #333;
font-size: 13px;
}
input[type="text"], input[type="date"], input[type="file"], select {
padding: 9px;
border: 1px solid #ccc;
border-radius: 4px;
font-size: 13px;
background-color: #ffffff;
}
input[type="text"], input[type="date"], input[type="file"] {
padding: 9px;
border: 1px solid #ccc;
border-radius: 4px;
font-size: 13px;
}
input[type="file"] {
background: #fafafa;
cursor: pointer;
}
button.submit-btn {
background-color: #1f4e78;
color: white;
border: none;
padding: 12px 20px;
font-size: 15px;
font-weight: bold;
border-radius: 4px;
cursor: pointer;
width: 100%;
margin-top: 10px;
transition: background-color 0.2s;
}
button.submit-btn:hover {
background-color: #153755;
}
button.submit-btn:disabled {
background-color: #a0aec0;
cursor: not-allowed;