Download index.html from servicedelivery/DAILYCLIENTMACADDRESSREPORT: direct link, hf CLI and curl.
- Browser
- Download file 40 kB
-
https://huggingface.co/spaces/servicedelivery/DAILYCLIENTMACADDRESSREPORT/resolve/main/index.html
- Command line
-
hf download hf://spaces/servicedelivery/DAILYCLIENTMACADDRESSREPORT/index.html
-
curl -L -o index.html https://huggingface.co/spaces/servicedelivery/DAILYCLIENTMACADDRESSREPORT/resolve/main/index.html
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, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/"/g, '"')
.replace(/'/g, ''');
};
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>
<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;