Nut.ไปเรื่อย
2fbb0b3d51
- Replace xlsx with exceljs in construction calculator; switch date input to Thai Buddhist Era selectors - Add disbursement plan preview HTML and schedule table prototype - Add sync_budget_come_from SQL script - Add budget type select menu design plan Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
640 lines
25 KiB
HTML
640 lines
25 KiB
HTML
<!DOCTYPE html>
|
|
<html lang="th">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
|
<title>คำนวณงวดงานสิ่งก่อสร้าง</title>
|
|
<script src="https://cdn.jsdelivr.net/npm/exceljs@4.4.0/dist/exceljs.min.js"></script>
|
|
<style>
|
|
* { box-sizing: border-box; margin: 0; padding: 0; }
|
|
body {
|
|
font-family: 'Sarabun', 'Segoe UI', sans-serif;
|
|
background: #f0f2f5;
|
|
padding: 20px;
|
|
font-size: 14px;
|
|
color: #222;
|
|
}
|
|
.page {
|
|
max-width: 1200px;
|
|
margin: auto;
|
|
background: #fff;
|
|
border-radius: 10px;
|
|
box-shadow: 0 2px 12px rgba(0,0,0,0.1);
|
|
overflow: hidden;
|
|
}
|
|
.page-header {
|
|
background: #1a56a0;
|
|
color: white;
|
|
padding: 18px 28px;
|
|
}
|
|
.page-header h1 { font-size: 18px; font-weight: 700; margin-bottom: 4px; }
|
|
.page-header p { font-size: 13px; opacity: 0.85; }
|
|
|
|
/* Form */
|
|
.form-section {
|
|
background: #f7f9fc;
|
|
border-bottom: 1px solid #e0e6ef;
|
|
padding: 18px 28px;
|
|
}
|
|
.form-row { display: flex; gap: 20px; align-items: flex-end; flex-wrap: wrap; }
|
|
.form-group { display: flex; flex-direction: column; gap: 5px; }
|
|
.form-group label {
|
|
font-size: 11px; color: #555; font-weight: 700;
|
|
text-transform: uppercase; letter-spacing: 0.5px;
|
|
}
|
|
.form-group input[type="text"],
|
|
.form-group input[type="number"],
|
|
.form-group input[type="date"] {
|
|
border: 1.5px solid #c5d3e8;
|
|
border-radius: 6px;
|
|
padding: 8px 12px;
|
|
font-size: 14px;
|
|
background: white;
|
|
color: #333;
|
|
font-family: inherit;
|
|
}
|
|
.form-group input[type="number"] { width: 90px; }
|
|
.form-group input#contractAmount { width: 180px; }
|
|
.date-sel {
|
|
border: 1.5px solid #c5d3e8; border-radius: 6px;
|
|
padding: 0 6px; font-size: 14px; background: white;
|
|
color: #333; font-family: inherit;
|
|
height: 37px; box-sizing: border-box; cursor: pointer;
|
|
}
|
|
.ratio-group { display: flex; align-items: center; gap: 6px; }
|
|
.ratio-group span { font-size: 12px; color: #666; }
|
|
|
|
/* Buttons */
|
|
.btn {
|
|
border: none; border-radius: 6px; padding: 9px 20px;
|
|
font-size: 14px; font-weight: 600; cursor: pointer; font-family: inherit;
|
|
}
|
|
.btn-calc { background: #1a56a0; color: white; }
|
|
.btn-export { background: #217346; color: white; }
|
|
.btn-export:disabled { background: #aaa; cursor: not-allowed; }
|
|
.btn-add {
|
|
background: #e8f0fb; color: #1a56a0;
|
|
border: 1.5px solid #c5d3e8; border-radius: 5px;
|
|
padding: 5px 14px; font-size: 13px; cursor: pointer;
|
|
font-family: inherit; font-weight: 600;
|
|
}
|
|
.btn-del {
|
|
background: none; border: none; color: #cc3333;
|
|
font-size: 16px; cursor: pointer; padding: 0 6px; line-height: 1;
|
|
}
|
|
|
|
/* Summary cards */
|
|
.table-section { padding: 20px 28px 28px; }
|
|
.summary-row { display: flex; gap: 12px; margin-bottom: 16px; flex-wrap: wrap; }
|
|
.summary-card {
|
|
background: #f0f6ff; border: 1px solid #c5d3e8;
|
|
border-radius: 8px; padding: 10px 16px; min-width: 150px;
|
|
}
|
|
.summary-card .lbl { font-size: 11px; color: #666; font-weight: 600; }
|
|
.summary-card .val { font-size: 15px; font-weight: 700; color: #1a56a0; margin-top: 3px; }
|
|
.summary-card.warn .val { color: #c0392b; }
|
|
.summary-card.ok .val { color: #217346; }
|
|
|
|
/* Table toolbar */
|
|
.table-toolbar {
|
|
display: flex; justify-content: space-between;
|
|
align-items: center; margin-bottom: 12px;
|
|
}
|
|
.legend { display: flex; gap: 14px; font-size: 12px; color: #666; align-items: center; }
|
|
.legend-box {
|
|
width: 13px; height: 13px; border-radius: 3px;
|
|
display: inline-block; vertical-align: middle; margin-right: 4px;
|
|
}
|
|
|
|
/* Table */
|
|
.table-wrapper { overflow-x: auto; }
|
|
table { width: 100%; border-collapse: collapse; font-size: 13px; }
|
|
th {
|
|
background: #1a56a0; color: white;
|
|
padding: 8px 8px; text-align: center;
|
|
white-space: nowrap; font-size: 12px;
|
|
}
|
|
th.sub { background: #2a6abf; }
|
|
th.editable-col { background: #c0832a; }
|
|
td {
|
|
padding: 6px 8px;
|
|
border-bottom: 1px solid #e8edf5;
|
|
text-align: right; white-space: nowrap;
|
|
}
|
|
td.center { text-align: center; }
|
|
td.editable-cell { background: #fffbf0; text-align: center; padding: 3px 5px; }
|
|
td.editable-cell input {
|
|
border: 1.5px solid #f0c040; border-radius: 4px;
|
|
padding: 4px 5px; width: 62px; text-align: center;
|
|
font-size: 13px; font-family: inherit;
|
|
}
|
|
td.editable-cell input:focus { outline: none; border-color: #e67e22; }
|
|
td.acc { background: #f0f6ff; font-weight: 600; color: #1a56a0; }
|
|
td.calc { color: #333; }
|
|
tr:nth-child(even) td { background: #fafcff; }
|
|
tr:nth-child(even) td.editable-cell { background: #fffdf0; }
|
|
tr:nth-child(even) td.acc { background: #e8f0fb; }
|
|
tr:hover td { background: #f0f6ff !important; }
|
|
tr.total-row td {
|
|
background: #dce8f8 !important;
|
|
font-weight: 700; color: #1a56a0;
|
|
border-top: 2px solid #1a56a0;
|
|
}
|
|
.pct-status { font-size: 11px; font-weight: 700; margin-left: 6px; }
|
|
.pct-warn { color: #c0392b; }
|
|
.pct-ok { color: #217346; }
|
|
</style>
|
|
</head>
|
|
<body>
|
|
<div class="page">
|
|
<div class="page-header">
|
|
<h1>การคำนวณงวดงานสิ่งก่อสร้าง</h1>
|
|
<p>กรอกข้อมูล กำหนดจำนวนงวด แล้วแก้ไขวันและร้อยละในตาราง ระบบจะคำนวณให้อัตโนมัติ</p>
|
|
</div>
|
|
|
|
<div class="form-section">
|
|
<div class="form-row">
|
|
<div class="form-group">
|
|
<label>วงเงินสัญญา (บาท)</label>
|
|
<input type="text" id="contractAmount" placeholder="เช่น 120000000" value="">
|
|
</div>
|
|
<div class="form-group">
|
|
<label>วันเริ่มก่อสร้าง (พ.ศ.)</label>
|
|
<div style="display:flex;gap:4px;">
|
|
<select id="startDay" class="date-sel" style="width:58px" onchange="onStartDateChange()"><option value="">วัน</option></select>
|
|
<select id="startMonth" class="date-sel" style="width:80px" onchange="onStartDateChange()">
|
|
<option value="">เดือน</option>
|
|
<option value="1">ม.ค.</option><option value="2">ก.พ.</option><option value="3">มี.ค.</option>
|
|
<option value="4">เม.ย.</option><option value="5">พ.ค.</option><option value="6">มิ.ย.</option>
|
|
<option value="7">ก.ค.</option><option value="8">ส.ค.</option><option value="9">ก.ย.</option>
|
|
<option value="10">ต.ค.</option><option value="11">พ.ย.</option><option value="12">ธ.ค.</option>
|
|
</select>
|
|
<select id="startYear" class="date-sel" style="width:80px" onchange="onStartDateChange()"><option value="">ปี</option></select>
|
|
</div>
|
|
<input type="hidden" id="startDate" value="">
|
|
</div>
|
|
<div class="form-group">
|
|
<label>เงินงบประมาณ (%)</label>
|
|
<input type="number" id="ngpmPct" value="95" min="0" max="100" style="width:80px">
|
|
</div>
|
|
<div class="form-group">
|
|
<label>เงินนอกงบ (%)</label>
|
|
<input type="number" id="nookPct" value="5" min="0" max="100" style="width:80px" readonly>
|
|
</div>
|
|
<div class="form-group">
|
|
<label>จำนวนงวด</label>
|
|
<input type="number" id="installmentCount" value="27" min="1" max="100" style="width:75px">
|
|
</div>
|
|
<div class="form-group" style="flex-direction:row;gap:10px;align-items:flex-end">
|
|
<button class="btn btn-export" id="btnExport" onclick="exportExcel().catch(console.error)" disabled>⬇ Export Excel</button>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="table-section">
|
|
<div class="summary-row" id="summaryRow">
|
|
<div class="summary-card">
|
|
<div class="lbl">วงเงินสัญญา</div>
|
|
<div class="val" id="s_contract">—</div>
|
|
</div>
|
|
<div class="summary-card">
|
|
<div class="lbl">เงิน งปม.</div>
|
|
<div class="val" id="s_ngpm">—</div>
|
|
</div>
|
|
<div class="summary-card">
|
|
<div class="lbl">เงินนอก</div>
|
|
<div class="val" id="s_nook">—</div>
|
|
</div>
|
|
<div class="summary-card" id="s_pct_card">
|
|
<div class="lbl">รวมร้อยละทุกงวด</div>
|
|
<div class="val" id="s_pct">0.00%</div>
|
|
</div>
|
|
<div class="summary-card">
|
|
<div class="lbl">วันสิ้นสุดสัญญา</div>
|
|
<div class="val" id="s_endDate">—</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="table-toolbar">
|
|
<div class="legend">
|
|
<span>
|
|
<span class="legend-box" style="background:#fffbf0;border:1.5px solid #f0c040"></span>
|
|
แก้ไขได้
|
|
</span>
|
|
<span>
|
|
<span class="legend-box" style="background:#f0f6ff;border:1px solid #c5d3e8"></span>
|
|
คำนวณอัตโนมัติ
|
|
</span>
|
|
</div>
|
|
<button class="btn-add" onclick="addRow()">+ เพิ่มงวด</button>
|
|
</div>
|
|
|
|
<div class="table-wrapper">
|
|
<table id="mainTable">
|
|
<thead>
|
|
<tr>
|
|
<th rowspan="2">งวดที่</th>
|
|
<th class="editable-col" rowspan="2">จำนวนวัน<br><small>(แก้ไขได้)</small></th>
|
|
<th class="editable-col" rowspan="2">ร้อยละ (%)<br><small>(แก้ไขได้)</small></th>
|
|
<th rowspan="2">วันส่งมอบงาน</th>
|
|
<th colspan="3">จำนวนเงินงวดนั้น (บาท)</th>
|
|
<th colspan="3">จำนวนเงินสะสม (บาท)</th>
|
|
<th rowspan="2"></th>
|
|
</tr>
|
|
<tr>
|
|
<th class="sub">รวม</th>
|
|
<th class="sub">เงิน งปม.</th>
|
|
<th class="sub">เงินนอก</th>
|
|
<th class="sub">รวม</th>
|
|
<th class="sub">เงิน งปม.</th>
|
|
<th class="sub">เงินนอก</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody id="tableBody"></tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<script>
|
|
// State
|
|
let installments = [];
|
|
|
|
// Template data (27 งวด from Excel)
|
|
const TEMPLATE_27 = [
|
|
{days:20, percent:3.80},
|
|
{days:20, percent:3.70},{days:20, percent:3.70},{days:20, percent:3.70},{days:20, percent:3.70},
|
|
{days:30, percent:3.70},{days:30, percent:3.70},{days:30, percent:3.70},{days:30, percent:3.70},
|
|
{days:30, percent:3.70},{days:30, percent:3.70},{days:30, percent:3.70},{days:30, percent:3.70},
|
|
{days:30, percent:3.70},{days:30, percent:3.70},{days:30, percent:3.70},{days:30, percent:3.70},
|
|
{days:30, percent:3.70},{days:30, percent:3.70},{days:30, percent:3.70},{days:30, percent:3.70},
|
|
{days:30, percent:3.70},{days:30, percent:3.70},{days:30, percent:3.70},
|
|
{days:40, percent:3.70},{days:40, percent:3.70},{days:40, percent:3.70}
|
|
];
|
|
|
|
function parseAmount(str) {
|
|
return parseFloat(String(str).replace(/,/g, '')) || 0;
|
|
}
|
|
|
|
function formatNumber(n) {
|
|
if (!n && n !== 0) return '—';
|
|
return Math.round(n).toLocaleString('th-TH');
|
|
}
|
|
|
|
function addDays(dateStr, days) {
|
|
const [y, m, d] = String(dateStr).split('-').map(Number);
|
|
const date = new Date(y, m - 1, d);
|
|
date.setDate(date.getDate() + days);
|
|
return date;
|
|
}
|
|
|
|
function toThaiDate(date) {
|
|
if (!(date instanceof Date) || isNaN(date)) return '—';
|
|
const y = date.getFullYear() + 543;
|
|
const m = String(date.getMonth() + 1).padStart(2, '0');
|
|
const d = String(date.getDate()).padStart(2, '0');
|
|
return `${d}/${m}/${y}`;
|
|
}
|
|
|
|
function getInputs() {
|
|
const ngpmPct = parseFloat(document.getElementById('ngpmPct').value) || 0;
|
|
return {
|
|
contract: parseAmount(document.getElementById('contractAmount').value),
|
|
startDate: document.getElementById('startDate').value,
|
|
ngpmPct,
|
|
nookPct: 100 - ngpmPct,
|
|
};
|
|
}
|
|
|
|
function recalcAll() {
|
|
const {contract, startDate, ngpmPct, nookPct} = getInputs();
|
|
const hasStart = startDate && contract > 0;
|
|
|
|
let cumDays = 0, cumTotal = 0, cumNgpm = 0, cumNook = 0;
|
|
let sumPct = 0;
|
|
|
|
installments.forEach((inst, i) => {
|
|
const days = parseFloat(inst.days) || 0;
|
|
const pct = parseFloat(inst.percent) || 0;
|
|
const isLast = i === installments.length - 1;
|
|
cumDays += days;
|
|
sumPct += pct;
|
|
|
|
let total, ngpm, nook;
|
|
if (contract > 0) {
|
|
total = isLast ? (contract - cumTotal) : Math.round(contract * pct / 100);
|
|
ngpm = isLast ? (Math.round(contract * ngpmPct / 100) - cumNgpm) : Math.round(total * ngpmPct / 100);
|
|
nook = total - ngpm;
|
|
} else {
|
|
total = 0; ngpm = 0; nook = 0;
|
|
}
|
|
cumTotal += total; cumNgpm += ngpm; cumNook += nook;
|
|
|
|
const deliveryDate = hasStart ? toThaiDate(addDays(startDate, cumDays)) : '—';
|
|
|
|
const row = document.getElementById(`row_${i}`);
|
|
if (!row) return;
|
|
row.querySelector('.cell-delivery').textContent = deliveryDate;
|
|
row.querySelector('.cell-total').textContent = formatNumber(total);
|
|
row.querySelector('.cell-ngpm').textContent = formatNumber(ngpm);
|
|
row.querySelector('.cell-nook').textContent = formatNumber(nook);
|
|
row.querySelector('.cell-acc-total').textContent = formatNumber(cumTotal);
|
|
row.querySelector('.cell-acc-ngpm').textContent = formatNumber(cumNgpm);
|
|
row.querySelector('.cell-acc-nook').textContent = formatNumber(cumNook);
|
|
});
|
|
|
|
const totalRow = document.getElementById('totalRow');
|
|
if (totalRow) {
|
|
totalRow.querySelector('.cell-total').textContent = formatNumber(contract);
|
|
totalRow.querySelector('.cell-ngpm').textContent = formatNumber(Math.round(contract * ngpmPct / 100));
|
|
totalRow.querySelector('.cell-nook').textContent = formatNumber(Math.round(contract * nookPct / 100));
|
|
totalRow.querySelector('.cell-pct').textContent = sumPct.toFixed(2) + '%';
|
|
}
|
|
|
|
document.getElementById('s_contract').textContent = contract > 0 ? formatNumber(contract) + ' ฿' : '—';
|
|
document.getElementById('s_ngpm').textContent = contract > 0 ? formatNumber(Math.round(contract * ngpmPct / 100)) + ' ฿' : '—';
|
|
document.getElementById('s_nook').textContent = contract > 0 ? formatNumber(Math.round(contract * nookPct / 100)) + ' ฿' : '—';
|
|
|
|
const pctOk = Math.abs(sumPct - 100) < 0.001;
|
|
const pctCard = document.getElementById('s_pct_card');
|
|
pctCard.className = 'summary-card ' + (pctOk ? 'ok' : 'warn');
|
|
document.getElementById('s_pct').innerHTML =
|
|
sumPct.toFixed(2) + '%' +
|
|
(pctOk
|
|
? '<span class="pct-status pct-ok">✓ ครบ 100%</span>'
|
|
: '<span class="pct-status pct-warn">⚠ ยังไม่ครบ 100%</span>');
|
|
|
|
if (hasStart) {
|
|
document.getElementById('s_endDate').textContent = toThaiDate(addDays(startDate, cumDays));
|
|
} else {
|
|
document.getElementById('s_endDate').textContent = '—';
|
|
}
|
|
|
|
document.getElementById('btnExport').disabled = !(pctOk && contract > 0 && startDate);
|
|
}
|
|
|
|
function renderTable() {
|
|
const tbody = document.getElementById('tableBody');
|
|
tbody.innerHTML = '';
|
|
|
|
installments.forEach((inst, i) => {
|
|
const tr = document.createElement('tr');
|
|
tr.id = `row_${i}`;
|
|
tr.innerHTML = `
|
|
<td class="center">${i + 1}</td>
|
|
<td class="editable-cell">
|
|
<input type="number" class="inp-days" min="0"
|
|
onchange="installments[${i}].days=this.value; recalcAll()"
|
|
oninput="installments[${i}].days=this.value; recalcAll()">
|
|
</td>
|
|
<td class="editable-cell">
|
|
<input type="number" class="inp-pct" min="0" max="100" step="0.01"
|
|
onchange="installments[${i}].percent=this.value; recalcAll()"
|
|
oninput="installments[${i}].percent=this.value; recalcAll()">
|
|
</td>
|
|
<td class="center calc cell-delivery">—</td>
|
|
<td class="calc cell-total">—</td>
|
|
<td class="calc cell-ngpm">—</td>
|
|
<td class="calc cell-nook">—</td>
|
|
<td class="acc cell-acc-total">—</td>
|
|
<td class="acc cell-acc-ngpm">—</td>
|
|
<td class="acc cell-acc-nook">—</td>
|
|
<td class="center">
|
|
<button class="btn-del" onclick="deleteRow(${i})" title="ลบงวดนี้">✕</button>
|
|
</td>
|
|
`;
|
|
// Set values safely via property assignment (not attribute interpolation)
|
|
tr.querySelector('.inp-days').value = inst.days;
|
|
tr.querySelector('.inp-pct').value = inst.percent;
|
|
tbody.appendChild(tr);
|
|
});
|
|
|
|
const totalTr = document.createElement('tr');
|
|
totalTr.id = 'totalRow';
|
|
totalTr.className = 'total-row';
|
|
totalTr.innerHTML = `
|
|
<td colspan="2" class="center">รวมทั้งสิ้น</td>
|
|
<td class="center cell-pct">0.00%</td>
|
|
<td class="center">—</td>
|
|
<td class="cell-total">—</td>
|
|
<td class="cell-ngpm">—</td>
|
|
<td class="cell-nook">—</td>
|
|
<td colspan="3" class="center">—</td>
|
|
<td></td>
|
|
`;
|
|
tbody.appendChild(totalTr);
|
|
|
|
recalcAll();
|
|
}
|
|
|
|
function addRow() {
|
|
installments.push({days: 0, percent: 0});
|
|
document.getElementById('installmentCount').value = installments.length;
|
|
renderTable();
|
|
}
|
|
|
|
function deleteRow(i) {
|
|
installments.splice(i, 1);
|
|
document.getElementById('installmentCount').value = installments.length;
|
|
renderTable();
|
|
}
|
|
|
|
function onCountChange(n) {
|
|
n = parseInt(n) || 1;
|
|
if (n < 1) n = 1;
|
|
if (n !== installments.length && installments.length > 0) {
|
|
if (!confirm(`การเปลี่ยนจำนวนงวดเป็น ${n} งวด จะล้างข้อมูลเดิม ต้องการดำเนินการต่อไหม?`)) {
|
|
document.getElementById('installmentCount').value = installments.length;
|
|
return;
|
|
}
|
|
}
|
|
if (n === 27) {
|
|
installments = TEMPLATE_27.map(t => ({...t}));
|
|
} else {
|
|
installments = Array.from({length: n}, () => ({days: 0, percent: 0}));
|
|
}
|
|
renderTable();
|
|
}
|
|
|
|
async function exportExcel() {
|
|
const {contract, startDate, ngpmPct, nookPct} = getInputs();
|
|
const wb = new ExcelJS.Workbook();
|
|
const ws = wb.addWorksheet('งวดงาน', {
|
|
views: [{ state: 'frozen', xSplit: 0, ySplit: 4 }]
|
|
});
|
|
|
|
const NCOLS = 10;
|
|
ws.columns = [
|
|
{width:8},{width:10},{width:10},{width:16},
|
|
{width:18},{width:18},{width:16},
|
|
{width:18},{width:18},{width:18}
|
|
];
|
|
|
|
const thin = { style: 'thin', color: { argb: 'FFB0C4DE' } };
|
|
const medT = { style: 'medium', color: { argb: 'FF1A56A0' } };
|
|
const bord = { top: thin, left: thin, bottom: thin, right: thin };
|
|
|
|
function fill(cell, argb) {
|
|
cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb } };
|
|
}
|
|
function font(cell, opts) {
|
|
cell.font = { name: 'Sarabun', ...opts };
|
|
}
|
|
function align(cell, h) {
|
|
cell.alignment = { horizontal: h, vertical: 'middle', wrapText: true };
|
|
}
|
|
|
|
// ── Row 1: Title ──────────────────────────────────────
|
|
ws.addRow(['การคำนวณงวดงานสิ่งก่อสร้าง']);
|
|
ws.mergeCells(1, 1, 1, NCOLS);
|
|
ws.getRow(1).height = 30;
|
|
const tc = ws.getCell(1, 1);
|
|
fill(tc, 'FF1A56A0');
|
|
font(tc, { bold: true, size: 14, color: { argb: 'FFFFFFFF' } });
|
|
align(tc, 'center');
|
|
|
|
// ── Row 2: Info ───────────────────────────────────────
|
|
const startDisp = startDate ? (() => {
|
|
const [y, m, d] = startDate.split('-').map(Number);
|
|
return `${String(d).padStart(2,'0')}/${String(m).padStart(2,'0')}/${y + 543}`;
|
|
})() : '—';
|
|
ws.addRow([`วงเงินสัญญา: ${Math.round(contract).toLocaleString('th-TH')} บาท วันเริ่มก่อสร้าง: ${startDisp} สัดส่วน งปม./เงินนอก: ${ngpmPct}% / ${nookPct}%`]);
|
|
ws.mergeCells(2, 1, 2, NCOLS);
|
|
ws.getRow(2).height = 20;
|
|
const ic = ws.getCell(2, 1);
|
|
fill(ic, 'FFE8F0FB');
|
|
font(ic, { size: 10, color: { argb: 'FF1A56A0' } });
|
|
align(ic, 'center');
|
|
|
|
// ── Row 3: Header row 1 (merged spans) ───────────────
|
|
ws.addRow(['งวดที่','จำนวนวัน','ร้อยละ (%)','วันส่งมอบงาน',
|
|
'จำนวนเงินงวดนั้น (บาท)','','','จำนวนเงินสะสม (บาท)','','']);
|
|
ws.mergeCells(3,1,4,1); ws.mergeCells(3,2,4,2);
|
|
ws.mergeCells(3,3,4,3); ws.mergeCells(3,4,4,4);
|
|
ws.mergeCells(3,5,3,7); ws.mergeCells(3,8,3,10);
|
|
ws.getRow(3).height = 22;
|
|
for (let c = 1; c <= NCOLS; c++) {
|
|
const cell = ws.getCell(3, c);
|
|
fill(cell, 'FF1A56A0');
|
|
font(cell, { bold: true, size: 10, color: { argb: 'FFFFFFFF' } });
|
|
align(cell, 'center');
|
|
cell.border = bord;
|
|
}
|
|
|
|
// ── Row 4: Header row 2 (sub-header) ─────────────────
|
|
ws.addRow(['','','','','รวม','เงิน งปม.','เงินนอก','รวม','เงิน งปม.','เงินนอก']);
|
|
ws.getRow(4).height = 18;
|
|
for (let c = 5; c <= NCOLS; c++) {
|
|
const cell = ws.getCell(4, c);
|
|
fill(cell, 'FF2A6ABF');
|
|
font(cell, { bold: true, size: 10, color: { argb: 'FFFFFFFF' } });
|
|
align(cell, 'center');
|
|
cell.border = bord;
|
|
}
|
|
|
|
// ── Data rows ─────────────────────────────────────────
|
|
let cumDays = 0, cumTotal = 0, cumNgpm = 0, cumNook = 0;
|
|
installments.forEach((inst, i) => {
|
|
const days = parseFloat(inst.days) || 0;
|
|
const pct = parseFloat(inst.percent) || 0;
|
|
const isLast = i === installments.length - 1;
|
|
cumDays += days;
|
|
const total = isLast ? (contract - cumTotal) : Math.round(contract * pct / 100);
|
|
const ngpm = isLast ? (Math.round(contract * ngpmPct / 100) - cumNgpm) : Math.round(total * ngpmPct / 100);
|
|
const nook = total - ngpm;
|
|
cumTotal += total; cumNgpm += ngpm; cumNook += nook;
|
|
|
|
const delivery = toThaiDate(addDays(startDate, cumDays));
|
|
const even = i % 2 === 1;
|
|
ws.addRow([i+1, days, pct/100, delivery, total, ngpm, nook, cumTotal, cumNgpm, cumNook]);
|
|
const rn = 5 + i;
|
|
ws.getRow(rn).height = 18;
|
|
for (let c = 1; c <= NCOLS; c++) {
|
|
const cell = ws.getCell(rn, c);
|
|
const isAcc = c >= 8;
|
|
fill(cell, isAcc ? (even ? 'FFD8E8F5' : 'FFE8F0FB') : (even ? 'FFF5F8FF' : 'FFFFFFFF'));
|
|
font(cell, { size: 10, color: { argb: isAcc ? 'FF1A56A0' : 'FF222222' }, bold: isAcc });
|
|
cell.border = bord;
|
|
if (c === 1 || c === 2 || c === 4) { align(cell, 'center'); }
|
|
else if (c === 3) { cell.numFmt = '0.00%'; align(cell, 'center'); }
|
|
else { cell.numFmt = '#,##0'; align(cell, 'right'); }
|
|
}
|
|
});
|
|
|
|
// ── Total row ─────────────────────────────────────────
|
|
const sumPct = installments.reduce((s, r) => s + (parseFloat(r.percent) || 0), 0);
|
|
const tRn = 5 + installments.length;
|
|
ws.addRow(['รวมทั้งสิ้น','', sumPct/100,'',
|
|
Math.round(contract), Math.round(contract*ngpmPct/100), Math.round(contract*nookPct/100),
|
|
'','','']);
|
|
ws.mergeCells(tRn, 1, tRn, 2);
|
|
ws.getRow(tRn).height = 22;
|
|
for (let c = 1; c <= NCOLS; c++) {
|
|
const cell = ws.getCell(tRn, c);
|
|
fill(cell, 'FFDCE8F8');
|
|
font(cell, { bold: true, size: 10, color: { argb: 'FF1A56A0' } });
|
|
cell.border = { ...bord, top: medT };
|
|
if (c === 1) { align(cell, 'center'); }
|
|
else if (c === 3) { cell.numFmt = '0.00%'; align(cell, 'center'); }
|
|
else if (c >= 5 && c <= 7) { cell.numFmt = '#,##0'; align(cell, 'right'); }
|
|
}
|
|
|
|
// ── Download ──────────────────────────────────────────
|
|
const buf = await wb.xlsx.writeBuffer();
|
|
const blob = new Blob([buf], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
|
|
const url = URL.createObjectURL(blob);
|
|
const a = document.createElement('a');
|
|
a.href = url;
|
|
const now = new Date();
|
|
a.download = `งวดงาน_${now.getFullYear()+543}${String(now.getMonth()+1).padStart(2,'0')}${String(now.getDate()).padStart(2,'0')}.xlsx`;
|
|
a.click();
|
|
URL.revokeObjectURL(url);
|
|
}
|
|
|
|
document.getElementById('contractAmount').addEventListener('input', recalcAll);
|
|
// Init date dropdowns
|
|
(function() {
|
|
const dayEl = document.getElementById('startDay');
|
|
for (let d = 1; d <= 31; d++) {
|
|
const o = document.createElement('option');
|
|
o.value = d; o.textContent = d;
|
|
dayEl.appendChild(o);
|
|
}
|
|
const yearEl = document.getElementById('startYear');
|
|
const curBE = new Date().getFullYear() + 543;
|
|
for (let y = 2540; y <= 2590; y++) {
|
|
const o = document.createElement('option');
|
|
o.value = y; o.textContent = y;
|
|
yearEl.appendChild(o);
|
|
}
|
|
})();
|
|
|
|
function onStartDateChange() {
|
|
const d = parseInt(document.getElementById('startDay').value) || 0;
|
|
const m = parseInt(document.getElementById('startMonth').value) || 0;
|
|
const y = parseInt(document.getElementById('startYear').value) || 0;
|
|
const hidden = document.getElementById('startDate');
|
|
if (d && m && y) {
|
|
const ce = y - 543;
|
|
hidden.value = `${ce}-${String(m).padStart(2,'0')}-${String(d).padStart(2,'0')}`;
|
|
} else {
|
|
hidden.value = '';
|
|
}
|
|
recalcAll();
|
|
}
|
|
document.getElementById('ngpmPct').addEventListener('input', function() {
|
|
const v = parseFloat(this.value) || 0;
|
|
document.getElementById('nookPct').value = (100 - v).toFixed(2);
|
|
recalcAll();
|
|
});
|
|
document.getElementById('installmentCount').addEventListener('change', function() {
|
|
onCountChange(this.value);
|
|
});
|
|
|
|
// Init
|
|
installments = TEMPLATE_27.map(t => ({...t}));
|
|
renderTable();
|
|
</script>
|
|
</body>
|
|
</html>
|