SAMPLE (SOURCE-CODE) of
DATA INSERTION PROGRAM for
"TO-DO_LIST RELATED APP: JOB DONE" POST
that utilizes FIREBASE DATABASE

<!-- ============================================== -->
<!-- FIREBASE CDN (BLOGGER COMPAT MODE) -->
<!-- ============================================== -->
<script src="https://www.gstatic.com/firebasejs/9.22.2/firebase-app-compat.js"></script>
<script src="https://www.gstatic.com/firebasejs/9.22.2/firebase-database-compat.js"></script>

<script>
firebase.initializeApp({
  apiKey: "AIzaSyAF5r6Rvu-DmoV-vf47wYTZfarpVGmNYR0",
  authDomain: "ronin-11938.firebaseapp.com",
  databaseURL: "https://ronin-11938-default-rtdb.firebaseio.com",
  projectId: "ronin-11938",
  storageBucket: "ronin-11938.firebasestorage.app",
  messagingSenderId: "823368889742",
  appId: "1:823368889742:web:609e16ace214b94a0df"
});

var rdb = firebase.database();
var refCRUD = rdb.ref("JobDoneData");
</script>

<!-- ============================================== -->
<!-- XLSX (SheetJS) FOR EXCEL PARSING -->
<!-- ============================================== -->
<script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>

<!-- ============================================== -->
<!-- STYLE (BLOGGER SAFE) -->
<!-- ============================================== -->
<style>
:root {
  --bg: #ffffff;
  --card: #ffffff;
  --glass: rgba(255,255,255,0.82);
  --border: #dcdcdc;
  --shadow: rgba(0,0,0,0.14);
  --text: #222;
}
@media (prefers-color-scheme: dark) {
  :root {
    --bg: #111;
    --card: #1b1b1b;
    --glass: rgba(40,40,40,0.72);
    --border: #333;
    --shadow: rgba(0,0,0,0.55);
    --text: #f5f5f5;
  }
}
body { background: var(--bg); color: var(--text); }

.crud-box, .table-box, .import-box {
  background: var(--card);
  border:1px solid var(--border);
  padding:18px;
  border-radius:12px;
  margin-bottom:20px;
  box-shadow:0 4px 12px var(--shadow);
}

input, select, textarea {
  padding:10px;
  width:100%;
  border-radius:8px;
  border:1px solid var(--border);
  box-shadow:0 1px 3px var(--shadow) inset;
}

.icon-btn {
  cursor:pointer;
  font-size:22px;
  margin-right:6px;
  transition:0.15s;
}
.icon-btn:hover { transform:scale(1.28); }

/* DATE COLUMN — TWO ROWS (Month + Year) */
.date-cell {
  width: 150px;
  min-width: 150px;
  text-align: center;
  white-space: normal;
  line-height: 1.25;
  font-weight: 600;
}
.date-year {
  display:block;
  font-size:0.9em;
  margin-top:2px;
  color:#666;
}

.glass-modal {
  position:fixed; top:0; left:0; width:100%; height:100%;
  background:rgba(0,0,0,0.55);
  display:none;
  align-items:center;
  justify-content:center;
  z-index:9999;
}
.glass-modal-content {
  background:var(--glass);
  backdrop-filter:blur(12px);
  padding:20px;
  border-radius:15px;
  width:90%;
  max-width:420px;
  box-shadow:0 4px 18px var(--shadow);
  color:var(--text);
}

.table-clean { border-collapse:collapse; width:100%; }
.table-clean th {
  background:#eaeaea;
  padding:10px;
  border-bottom:2px solid var(--border);
}
.table-clean td {
  padding:10px;
  border-bottom:1px solid var(--border);
}
.table-clean tr:nth-child(even) {
  background:rgba(0,0,0,0.04);
}

/* Import area */
#importLog, #importPreview {
  width:100%;
  padding:10px;
  border-radius:8px;
  border:1px solid var(--border);
  font-family:Consolas,"Courier New",monospace;
  font-size:12px;
  white-space:pre-wrap;
  background:#fafafa;
  max-height:160px;
  overflow-y:auto;
}
</style>

<!-- ============================================== -->
<!-- INPUT FORM -->
<!-- ============================================== -->
<div class="crud-box">
  <h3>➕ Add Job Done</h3>

  <label><b>Month</b></label>
  <select id="monthSelect" style="margin-bottom:10px;"></select>

  <label><b>Year</b></label>
  <select id="yearSelect" style="margin-bottom:10px;"></select>

  <label><b>Description</b></label>
  <textarea id="jobDesc" style="height:80px;"></textarea>

  <br><br>
  <button onclick="addRecord()"
    style="padding:10px 20px; border-radius:8px; border:none; background:#007bff; color:white;">
    💾 Save
  </button>
</div>

<!-- ============================================== -->
<!-- DB STATUS + IMPORT FROM SPREADSHEET -->
<!-- ============================================== -->
<div class="import-box">
  <h3>📂 Database Status & Spreadsheet Sync (Full Replace)</h3>
  <p>
    Current records in <b>JobDoneData</b>:
    <span id="dbCount" style="font-weight:bold;">0</span>
  </p>

  <hr>

  <p><b>Step 1.</b> Choose Excel file (.xls / .xlsx) with columns:<br>
     <i>Month | Year | Description</i> (Month will be stored EXACTLY as in the file)</p>
  <input type="file" id="fileInput" accept=".xls,.xlsx" />

  <br><br>
  <button onclick="parseSpreadsheet()" style="padding:8px 16px;">📄 Read Spreadsheet</button>

  <br><br>
  <p><b>Parsed preview:</b></p>
  <div id="importPreview">No data parsed yet.</div>

  <br>
  <button id="syncBtn" onclick="syncToFirebase()" style="padding:8px 16px;" disabled>
    ⚠️ FULL REPLACE: Sync Spreadsheet → Firebase
  </button>

  <p style="color:red; margin-top:8px;">
    WARNING: This will <b>delete all existing records</b> in JobDoneData and replace them with rows from the spreadsheet.
  </p>

  <h4>Log</h4>
  <div id="importLog">Waiting...</div>
</div>

<!-- ============================================== -->
<!-- SEARCH + SORT -->
<!-- ============================================== -->
<div class="crud-box">
  <label><b>Search</b></label>
  <input id="searchInput" onkeyup="renderTable()" placeholder="Search description or date…" />

  <br><br>

  <label><b>Sort by</b></label>
  <select id="sortSelect" onchange="renderTable()">
    <option value="desc">Newest First</option>
    <option value="asc">Oldest First</option>
  </select>
</div>

<!-- ============================================== -->
<!-- TABLE -->
<!-- ============================================== -->
<div class="table-box">
  <table class="table-clean">
    <thead>
      <tr>
        <th>Date</th>
        <th>Description</th>
        <th>Action</th>
      </tr>
    </thead>
    <tbody id="dataList"></tbody>
  </table>
</div>

<!-- ============================================== -->
<!-- CUSTOM FLOATING GLASS MODAL -->
<!-- ============================================== -->
<div id="modalGlass" class="glass-modal">
  <div class="glass-modal-content">
    <h4 id="modalTitle"></h4>
    <div id="modalBody" style="white-space:pre-wrap; margin-top:10px;"></div>
    <br>
    <button onclick="closeModal()" style="padding:8px 15px;">Close</button>
  </div>
</div>

<!-- ============================================== -->
<!-- LOGIC SCRIPT -->
<!-- ============================================== -->
<script>
var allData = [];
var importedRows = []; // from spreadsheet
var monthNames = ["January","February","March","April","May","June","July","August","September","October","November","December"];

/* -------------------------- UTILITIES -------------------------- */
function logImport(msg) {
  var area = document.getElementById("importLog");
  area.textContent += "\n" + msg;
  area.scrollTop = area.scrollHeight;
}

function isYear(str) {
  return /^[0-9]{4}$/.test(str);
}

/* Parse month RAW → { num, label }
   - label: EXACTLY as in file (trimmed), except empty → "UNDATED MONTH"
   - num: used ONLY for sortKey (0 if cannot detect) */
function parseMonthRaw(monthRaw) {
  if (monthRaw == null || String(monthRaw).trim() === "") {
    return { num: 0, label: "UNDATED MONTH" };
  }

  var label = String(monthRaw).trim(); // what we SHOW
  var lower = label.toLowerCase().replace(/\./g, "");

  // try numeric: 1..12
  var digitsOnly = label.replace(/[^\d]/g, "");
  if (digitsOnly !== "") {
    var n = parseInt(digitsOnly, 10);
    if (!isNaN(n) && n >= 1 && n <= 12) {
      return { num: n, label: label };
    }
  }

  // try to map known Indonesian/English names to numeric, but KEEP label as-is
  var dict = {
    "januari": 1, "january": 1,
    "februari": 2, "pebruari": 2, "february": 2,
    "maret": 3, "march": 3,
    "april": 4,
    "mei": 5, "may": 5,
    "juni": 6, "june": 6,
    "juli": 7, "july": 7,
    "agustus": 8, "august": 8,
    "september": 9,
    "oktober": 10, "october": 10,
    "november": 11,
    "desember": 12, "december": 12
  };

  if (dict[lower]) {
    return { num: dict[lower], label: label };
  }

  // fuzzy first 3 chars for numeric only
  var p3 = lower.substring(0,3);
  var fuzzy = {
    "jan": 1,
    "feb": 2,
    "peb": 2,
    "mar": 3,
    "apr": 4,
    "mei": 5,
    "may": 5,
    "jun": 6,
    "jul": 7,
    "agu": 8,
    "ags": 8,
    "aug": 8,
    "sep": 9,
    "okt": 10,
    "oct": 10,
    "nov": 11,
    "des": 12,
    "dse": 12,
    "dec": 12
  };
  if (fuzzy[p3]) {
    return { num: fuzzy[p3], label: label };
  }

  // if completely unknown → keep label, num=0
  return { num: 0, label: label };
}

/* -------------------------- LOAD MONTH & YEAR SELECTS (MANUAL INPUT) -------------------------- */
function loadMonthYear() {
  var mSel = document.getElementById("monthSelect");
  var ySel = document.getElementById("yearSelect");

  var today = new Date();
  var curM = today.getMonth() + 1;
  var curY = today.getFullYear();

  var i, y;

  for (i=1; i<=12; i++) {
    var o = document.createElement("option");
    o.value = i;
    o.text = monthNames[i-1];
    if (i === curM) o.selected = true;
    mSel.appendChild(o);
  }

  for (y=2023; y<=curY+5; y++) {
    var o2 = document.createElement("option");
    o2.value = y;
    o2.text = y;
    if (y === curY) o2.selected = true;
    ySel.appendChild(o2);
  }
}
loadMonthYear();

/* -------------------------- ADD RECORD (MANUAL FORM) -------------------------- */
function addRecord() {
  var m = document.getElementById("monthSelect").value;
  var y = document.getElementById("yearSelect").value;
  var desc = document.getElementById("jobDesc").value;

  if (m==="" || y==="" || desc.trim()==="") {
    alert("Please complete all fields.");
    return;
  }

  var id = refCRUD.push().key;
  var monthNum = parseInt(m,10);
  var monthLabel = monthNames[monthNum-1] || "UNDATED MONTH";
  var yearNum = parseInt(y,10);
  var sortKey = yearNum * 100 + monthNum;

  refCRUD.child(id).set({
    id:id,
    month:monthNum,
    monthName:monthLabel,  // manual entries use English labels
    year:yearNum,
    description:desc,
    sortKey:sortKey
  });

  document.getElementById("jobDesc").value="";
}

/* -------------------------- FIREBASE LISTENER (DB STATUS + TABLE) -------------------------- */
refCRUD.on("value", function(snap) {
  allData=[];
  snap.forEach(function(c){ allData.push(c.val()); });

  document.getElementById("dbCount").textContent = allData.length;

  renderTable();
});

/* -------------------------- RENDER TABLE -------------------------- */
function renderTable() {
  var tbody=document.getElementById("dataList");
  tbody.innerHTML="";

  var search=document.getElementById("searchInput").value.toLowerCase();
  var sort=document.getElementById("sortSelect").value;

  var filtered=[];
  var i;

  for (i=0;i<allData.length;i++){
    var d = allData[i];
    var monthLabel = d.monthName ? d.monthName : "UNDATED MONTH";

    var text = monthLabel + " " + d.year + " " + (d.description || "");
    if (text.toLowerCase().indexOf(search)!==-1) filtered.push(d);
  }

  filtered.sort(function(a,b){
    return sort==="desc" ? b.sortKey-a.sortKey : a.sortKey-b.sortKey;
  });

  for (i=0;i<filtered.length;i++){
    var d2=filtered[i];
    var tr=document.createElement("tr");

    var mLabel = d2.monthName ? d2.monthName : "UNDATED MONTH";

    tr.innerHTML =
      "<td class='date-cell'>" +
         mLabel +
         "<span class='date-year'>" + d2.year + "</span>" +
      "</td>" +
      "<td>"+ (d2.description || "").replace(/\n/g,'<br>') +"</td>" +
      "<td>" +
        "<span class='icon-btn' onclick=\"viewRecord('" + d2.id + "')\">👁️</span>" +
        "<span class='icon-btn' onclick=\"editRecord('" + d2.id + "')\">✏️</span>" +
        "<span class='icon-btn' onclick=\"deleteRecord('" + d2.id + "')\">🗑️</span>" +
      "</td>";

    tbody.appendChild(tr);
  }
}

/* -------------------------- VIEW / EDIT / DELETE -------------------------- */
function viewRecord(id){
  var i;
  for (i=0;i<allData.length;i++){
    if (allData[i].id===id){
      var d = allData[i];
      var monthLabel = d.monthName ? d.monthName : "UNDATED MONTH";
      openModal(
        "View Record",
        monthLabel + " " + d.year + "\n\n" + (d.description || "")
      );
      return;
    }
  }
}

function editRecord(id){
  var i;
  for (i=0;i<allData.length;i++){
    if (allData[i].id===id){
      var newDesc=prompt("Edit description:", allData[i].description || "");
      if (newDesc!==null){
        refCRUD.child(id).update({description:newDesc});
      }
      return;
    }
  }
}

function deleteRecord(id){
  if (confirm("Delete this record?")){
    refCRUD.child(id).remove();
  }
}

function openModal(title,body){
  document.getElementById("modalTitle").textContent=title;
  document.getElementById("modalBody").textContent=body;
  document.getElementById("modalGlass").style.display="flex";
}

function closeModal(){
  document.getElementById("modalGlass").style.display="none";
}

/* -------------------------- PARSE SPREADSHEET (UPLOAD) -------------------------- */
function parseSpreadsheet() {
  var fileInput = document.getElementById("fileInput");
  var preview = document.getElementById("importPreview");
  var syncBtn = document.getElementById("syncBtn");
  document.getElementById("importLog").textContent = "Reading file...";
  importedRows = [];
  syncBtn.disabled = true;

  if (!fileInput.files || !fileInput.files[0]) {
    alert("Please choose an Excel file first.");
    document.getElementById("importLog").textContent = "No file selected.";
    return;
  }

  var file = fileInput.files[0];
  var reader = new FileReader();

  reader.onload = function(e) {
    try {
      var data = e.target.result;
      var workbook = XLSX.read(data, { type:"binary" });

      var firstSheetName = workbook.SheetNames[0];
      var sheet = workbook.Sheets[firstSheetName];

      var rows = XLSX.utils.sheet_to_json(sheet, { header:1 });

      var ok = 0;
      var skip = 0;
      var previewLines = [];

      for (var i=0; i<rows.length; i++) {
        var r = rows[i];
        if (!r || r.length === 0) continue;

        // STRICT: Column-1 = Month, Column-2 = Year, Column-3+ = Description
        var cMonth = r[0] != null ? String(r[0]) : "";
        var cYear  = r[1] != null ? String(r[1]).trim() : "";
        var desc   = "";

        if (r.length >= 3) {
          for (var j=2; j<r.length; j++) {
            if (r[j] != null) {
              if (desc !== "") desc += " ";
              desc += String(r[j]);
            }
          }
        }

        if (!isYear(cYear)) {
          // probably header row or bad row
          skip++;
          continue;
        }

        if (!desc || desc.trim() === "") {
          skip++;
          continue;
        }

        var yearNum = parseInt(cYear,10);
        var mInfo = parseMonthRaw(cMonth);
        var monthNum = mInfo.num;
        var monthLabel = mInfo.label;
        var sortKey = yearNum * 100 + monthNum;

        importedRows.push({
          month: monthNum,
          monthName: monthLabel,   // EXACT text from file (or UNDATED MONTH)
          year: yearNum,
          description: desc,
          sortKey: sortKey
        });

        ok++;
        if (previewLines.length < 10) {
          previewLines.push(monthLabel + " " + yearNum + " — " + desc);
        }
      }

      preview.textContent = "Parsed rows: " + ok + " (skipped: " + skip + ")\n\n" +
                            previewLines.join("\n");
      document.getElementById("importLog").textContent = "Spreadsheet parsed. Ready to sync.";
      if (ok > 0) {
        syncBtn.disabled = false;
      }
    } catch (err) {
      document.getElementById("importLog").textContent = "Error reading file: " + err;
      preview.textContent = "Error.";
    }
  };

  reader.readAsBinaryString(file);
}

/* -------------------------- SYNC (FULL REPLACE) -------------------------- */
function syncToFirebase() {
  if (!importedRows || importedRows.length === 0) {
    alert("No parsed data to sync. Please read spreadsheet first.");
    return;
  }

  if (!confirm("This will DELETE ALL existing records and replace with " +
               importedRows.length + " rows from spreadsheet. Continue?")) {
    return;
  }

  document.getElementById("importLog").textContent = "Clearing JobDoneData...";
  refCRUD.remove(function(err) {
    if (err) {
      document.getElementById("importLog").textContent = "Error clearing DB: " + err;
      return;
    }

    document.getElementById("importLog").textContent = "DB cleared. Inserting rows...";
    for (var i=0; i<importedRows.length; i++) {
      var rec = importedRows[i];
      var newRef = refCRUD.push();
      newRef.set({
        id: newRef.key,
        month: rec.month,
        monthName: rec.monthName,
        year: rec.year,
        description: rec.description,
        sortKey: rec.sortKey
      });
    }

    document.getElementById("importLog").textContent += "\nSync complete. Inserted " +
                                                       importedRows.length + " rows.";
    document.getElementById("syncBtn").disabled = true;

    // FINAL count from DB
    refCRUD.once("value", function(snap) {
      var count = snap.numChildren();
      document.getElementById("importLog").textContent +=
        "\nFINAL COUNT after sync: " + count + " records.";
      document.getElementById("dbCount").textContent = count;
    });
  });
}
</script>

Comments

Post a Comment

Popular posts from this blog

PENDING ITEMs [REVISION] v0

🔗 My Pending and Delayed POSTs SUMMARY Sort by Date Sort by Auto Title Sort by My Title Ascending Descending Insert URL: Your Own Title (Optional): ➕ ADD ENTRY 💾 SAVE EDIT DATE AUTO TITLE MY TITLE URL ACTIONS 📝 TO DO LIST SUMMARY  Sort by Date Sort by Header Sort by Detail Ascending Descending Header (Title): Detail: ➕ ADD ENTRY ...
Read more