213 lines
8.6 KiB
JavaScript
213 lines
8.6 KiB
JavaScript
/* excel.js — Export & Import data pelamar ke/dari Excel (SheetJS) */
|
|
(function (global) {
|
|
'use strict';
|
|
|
|
var HEADERS = [
|
|
'No', 'Nama', 'No WhatsApp', 'Email', 'Posisi', 'Status', 'Sumber', 'Domisili',
|
|
'Gaji Ekspektasi', 'Tanggal Melamar', 'Catatan',
|
|
'Tanggal Interview', 'Jam Interview', 'Metode', 'Lokasi/Link',
|
|
'Pewawancara', 'No WA Pewawancara'
|
|
];
|
|
|
|
var COL_WIDTH = [5, 24, 16, 24, 20, 18, 12, 16, 16, 16, 30, 16, 12, 10, 24, 18, 18];
|
|
|
|
var ALIASES = {
|
|
nama: ['nama', 'nama lengkap', 'name', 'full name'],
|
|
phoneRaw: ['no whatsapp', 'no wa', 'nomor whatsapp', 'nomor wa', 'no hp', 'no. hp', 'whatsapp', 'phone', 'telepon', 'no telp', 'no. telp'],
|
|
email: ['email', 'e-mail'],
|
|
posisi: ['posisi', 'posisi dilamar', 'posisi yang dilamar', 'jabatan', 'position'],
|
|
status: ['status', 'status rekrutmen'],
|
|
sumber: ['sumber', 'sumber pelamar', 'source'],
|
|
domisili: ['domisili', 'alamat', 'kota'],
|
|
gajiEkspektasi: ['gaji ekspektasi', 'gaji', 'ekspektasi gaji', 'expected salary'],
|
|
tanggalMelamar: ['tanggal melamar', 'tgl melamar', 'tanggal', 'date'],
|
|
catatan: ['catatan', 'catatan hr', 'keterangan', 'note', 'notes'],
|
|
ivTanggal: ['tanggal interview', 'tgl interview', 'jadwal interview'],
|
|
ivJam: ['jam interview', 'jam', 'waktu'],
|
|
ivMetode: ['metode', 'metode interview'],
|
|
ivLokasi: ['lokasi/link', 'lokasi', 'link', 'lokasi interview', 'lokasi / link', 'tautan'],
|
|
pewawancara: ['pewawancara', 'nama pewawancara', 'interviewer'],
|
|
telpPewawancara: ['no wa pewawancara', 'no. wa pewawancara', 'no hp pewawancara', 'telp pewawancara', 'whatsapp pewawancara']
|
|
};
|
|
|
|
function hasLib() { return typeof global.XLSX !== 'undefined' && global.XLSX; }
|
|
|
|
function statusName(id, statuses) {
|
|
var s = (statuses || []).filter(function (x) { return x.id === id; })[0];
|
|
return s ? s.nama : '';
|
|
}
|
|
|
|
function resolveStatus(nama, statuses) {
|
|
var target = String(nama || '').trim().toLowerCase();
|
|
var list = statuses || [];
|
|
if (target) {
|
|
var found = list.filter(function (s) { return String(s.nama).trim().toLowerCase() === target; })[0];
|
|
if (found) return found.id;
|
|
var partial = list.filter(function (s) {
|
|
var n = String(s.nama).trim().toLowerCase();
|
|
return n.indexOf(target) >= 0 || target.indexOf(n) >= 0;
|
|
})[0];
|
|
if (partial) return partial.id;
|
|
}
|
|
return list.length ? list.slice().sort(function (a, b) { return (a.urutan || 0) - (b.urutan || 0); })[0].id : 'baru';
|
|
}
|
|
|
|
function normalizeKey(k) {
|
|
return String(k == null ? '' : k).trim().toLowerCase().replace(/\s+/g, ' ').replace(/:$/, '');
|
|
}
|
|
|
|
function pad(n) { return String(n).padStart(2, '0'); }
|
|
|
|
function toDateStr(v) {
|
|
if (v == null || v === '') return '';
|
|
if (v instanceof Date) return v.getFullYear() + '-' + pad(v.getMonth() + 1) + '-' + pad(v.getDate());
|
|
if (typeof v === 'number' && isFinite(v)) {
|
|
var epoch = Date.UTC(1899, 11, 30);
|
|
var d = new Date(epoch + v * 86400000);
|
|
return d.getUTCFullYear() + '-' + pad(d.getUTCMonth() + 1) + '-' + pad(d.getUTCDate());
|
|
}
|
|
var s = String(v).trim();
|
|
var iso = s.match(/^(\d{4})-(\d{1,2})-(\d{1,2})/);
|
|
if (iso) return iso[1] + '-' + pad(iso[2]) + '-' + pad(iso[3]);
|
|
var dmy = s.match(/^(\d{1,2})[\/\-.](\d{1,2})[\/\-.](\d{2,4})$/);
|
|
if (dmy) {
|
|
var yr = dmy[3].length === 2 ? '20' + dmy[3] : dmy[3];
|
|
return yr + '-' + pad(dmy[2]) + '-' + pad(dmy[1]);
|
|
}
|
|
var d2 = new Date(s);
|
|
if (!isNaN(d2.getTime())) return d2.getFullYear() + '-' + pad(d2.getMonth() + 1) + '-' + pad(d2.getDate());
|
|
return '';
|
|
}
|
|
|
|
function toTimeStr(v) {
|
|
if (v == null || v === '') return '';
|
|
if (v instanceof Date) return pad(v.getHours()) + ':' + pad(v.getMinutes());
|
|
if (typeof v === 'number' && isFinite(v)) {
|
|
var total = Math.round(v * 24 * 60);
|
|
return pad(Math.floor(total / 60) % 24) + ':' + pad(total % 60);
|
|
}
|
|
var s = String(v).trim();
|
|
var m = s.match(/^(\d{1,2})[:.](\d{2})/);
|
|
if (m) return pad(m[1]) + ':' + m[2];
|
|
return s;
|
|
}
|
|
|
|
function normalizeMetode(v) {
|
|
var s = String(v == null ? '' : v).trim().toLowerCase();
|
|
if (s.indexOf('online') >= 0 || s.indexOf('daring') >= 0) return 'Online';
|
|
if (s.indexOf('offline') >= 0 || s.indexOf('luring') >= 0 || s.indexOf('kantor') >= 0) return 'Offline';
|
|
return '';
|
|
}
|
|
|
|
function exportCandidates(candidates, statuses) {
|
|
if (!hasLib()) throw new Error('Pustaka Excel tidak termuat.');
|
|
var rows = (candidates || []).map(function (c, i) {
|
|
var iv = c.interview || {};
|
|
var o = {};
|
|
o['No'] = i + 1;
|
|
o['Nama'] = c.nama || '';
|
|
o['No WhatsApp'] = c.phoneNorm || global.Phone.normalize(c.phoneRaw || '');
|
|
o['Email'] = c.email || '';
|
|
o['Posisi'] = c.posisi || '';
|
|
o['Status'] = statusName(c.statusId, statuses);
|
|
o['Sumber'] = c.sumber || '';
|
|
o['Domisili'] = c.domisili || '';
|
|
o['Gaji Ekspektasi'] = c.gajiEkspektasi || '';
|
|
o['Tanggal Melamar'] = c.tanggalMelamar || '';
|
|
o['Catatan'] = c.catatan || '';
|
|
o['Tanggal Interview'] = iv.tanggal || '';
|
|
o['Jam Interview'] = iv.jam || '';
|
|
o['Metode'] = iv.metode || '';
|
|
o['Lokasi/Link'] = iv.lokasi || '';
|
|
o['Pewawancara'] = iv.pewawancara || '';
|
|
o['No WA Pewawancara'] = iv.telpPewawancara || '';
|
|
return o;
|
|
});
|
|
|
|
var ws = global.XLSX.utils.json_to_sheet(rows, { header: HEADERS });
|
|
ws['!cols'] = COL_WIDTH.map(function (w) { return { wch: w }; });
|
|
var wb = global.XLSX.utils.book_new();
|
|
global.XLSX.utils.book_append_sheet(wb, ws, 'Pelamar');
|
|
var stamp = new Date().toISOString().slice(0, 10).replace(/-/g, '');
|
|
global.XLSX.writeFile(wb, 'data-pelamar-' + stamp + '.xlsx');
|
|
}
|
|
|
|
function exportTemplate() {
|
|
if (!hasLib()) throw new Error('Pustaka Excel tidak termuat.');
|
|
var ws = global.XLSX.utils.aoa_to_sheet([HEADERS]);
|
|
ws['!cols'] = COL_WIDTH.map(function (w) { return { wch: w }; });
|
|
var wb = global.XLSX.utils.book_new();
|
|
global.XLSX.utils.book_append_sheet(wb, ws, 'Pelamar');
|
|
global.XLSX.writeFile(wb, 'template-import-pelamar.xlsx');
|
|
}
|
|
|
|
function readRows(file) {
|
|
if (!hasLib()) return Promise.reject(new Error('Pustaka Excel tidak termuat.'));
|
|
var isCsv = /\.csv$/i.test(file.name) || file.type === 'text/csv';
|
|
return new Promise(function (resolve, reject) {
|
|
var fr = new FileReader();
|
|
fr.onerror = function () { reject(fr.error || new Error('Gagal membaca file')); };
|
|
fr.onload = function (e) {
|
|
try {
|
|
var wb = isCsv
|
|
? global.XLSX.read(e.target.result, { type: 'string' })
|
|
: global.XLSX.read(new Uint8Array(e.target.result), { type: 'array', cellDates: true });
|
|
var sheetName = wb.SheetNames[0];
|
|
var ws = wb.Sheets[sheetName];
|
|
var json = global.XLSX.utils.sheet_to_json(ws, { defval: '', raw: true });
|
|
resolve(json);
|
|
} catch (err) {
|
|
reject(new Error('Format file tidak dikenali. Gunakan .xlsx, .xls, atau .csv.'));
|
|
}
|
|
};
|
|
if (isCsv) fr.readAsText(file); else fr.readAsArrayBuffer(file);
|
|
});
|
|
}
|
|
|
|
function mapRows(json, statuses) {
|
|
return (json || []).map(function (row) {
|
|
var lookup = {};
|
|
Object.keys(row).forEach(function (k) { lookup[normalizeKey(k)] = row[k]; });
|
|
function pick(field) {
|
|
var al = ALIASES[field] || [];
|
|
for (var i = 0; i < al.length; i++) {
|
|
var v = lookup[al[i]];
|
|
if (v != null && String(v).trim() !== '') return v;
|
|
}
|
|
return '';
|
|
}
|
|
return {
|
|
nama: String(pick('nama')).trim(),
|
|
phoneRaw: String(pick('phoneRaw')).trim(),
|
|
email: String(pick('email')).trim(),
|
|
posisi: String(pick('posisi')).trim(),
|
|
statusId: resolveStatus(pick('status'), statuses),
|
|
sumber: String(pick('sumber')).trim(),
|
|
domisili: String(pick('domisili')).trim(),
|
|
gajiEkspektasi: String(pick('gajiEkspektasi')).trim(),
|
|
tanggalMelamar: toDateStr(pick('tanggalMelamar')),
|
|
catatan: String(pick('catatan')).trim(),
|
|
interview: {
|
|
tanggal: toDateStr(pick('ivTanggal')),
|
|
jam: toTimeStr(pick('ivJam')),
|
|
metode: normalizeMetode(pick('ivMetode')),
|
|
lokasi: String(pick('ivLokasi')).trim(),
|
|
pewawancara: String(pick('pewawancara')).trim(),
|
|
telpPewawancara: String(pick('telpPewawancara')).trim()
|
|
}
|
|
};
|
|
});
|
|
}
|
|
|
|
global.Excel = {
|
|
HEADERS: HEADERS,
|
|
available: hasLib,
|
|
exportCandidates: exportCandidates,
|
|
exportTemplate: exportTemplate,
|
|
readRows: readRows,
|
|
mapRows: mapRows,
|
|
toDateStr: toDateStr,
|
|
toTimeStr: toTimeStr
|
|
};
|
|
})(window);
|