/** * 幸大小新 v2 — 施工日誌(日報表) * 每天 21:30 自動產生當天分頁:雲端硬碟「小新備份/施工日誌/施工日誌 115年11月」(一個月一個檔,一天一個分頁) * 產生後可以直接在分頁上修改;需要 Excel 時:檔案 → 下載 → Microsoft Excel * 已存在的分頁不會被覆蓋。要重新產生,請用下面的「重產日報表」。 */ const REPORT = { TITLE: '幸大建設 基隆新東區 施工日誌', FOLDER: '施工日誌', MIN_VENDOR_ROWS: 8, MIN_LABOR_ROWS: 4, COL_WIDTHS: [64, 70, 110, 48, 100, 100, 100, 100, 100], // A~I }; // 排程呼叫(產生今天) function generateDailyReport() { makeReport_(today_(), false); } // 手動:重新產生某一天(會覆蓋該天分頁)。改 DATE 後按執行。 function 重產日報表() { const DATE = ''; // 例如 '2026-11-18';空白=今天 makeReport_(DATE || today_(), true); } function makeReport_(date, force) { const book = monthBook_(date); const name = String(Number(date.slice(8))); let sh = book.getSheetByName(name); if (sh) { if (!force) return; if (book.getSheets().length === 1) book.insertSheet('暫存'); book.deleteSheet(sh); } sh = book.insertSheet(name, sheetIndex_(book, Number(name))); buildReport_(sh, collect_(date), date); ['工作表1', 'Sheet1', '暫存'].forEach(n => { const s = book.getSheetByName(n); if (s && book.getSheets().length > 1) book.deleteSheet(s); }); } function monthBook_(date) { const roc = Number(date.slice(0, 4)) - 1911; const name = `施工日誌 ${roc}年${Number(date.slice(5, 7))}月`; const folder = getFolderPath_([REPORT.FOLDER]); const it = folder.getFilesByName(name); if (it.hasNext()) return SpreadsheetApp.open(it.next()); const book = SpreadsheetApp.create(name); DriveApp.getFileById(book.getId()).moveTo(folder); return book; } function sheetIndex_(book, day) { return book.getSheets().filter(s => /^\d+$/.test(s.getName()) && Number(s.getName()) < day).length; } // ---------- 整理當天資料 ---------- function collect_(date) { const live = (sheet, st) => readAll_(sheet).filter(r => d_(r['日期']) === date && r[st || '狀態'] !== '撤回'); // 1. 協力廠商(本工程) const vmap = {}; live(S.VENDOR).forEach(r => { const k = String(r['廠商']); const v = vmap[k] = vmap[k] || { vendor: k, item: '', am: 0, pm: 0, amText: '', pmText: '' }; if (r['工項']) v.item = String(r['工項']); if (r['時段'] === '上午') { v.am = num_(r['人數']); v.amText = joinText_(v.amText, r['施作內容']); } else { v.pm = num_(r['人數']); v.pmText = joinText_(v.pmText, r['施作內容']); } }); const vendors = Object.keys(vmap).map(k => { const v = vmap[k]; return { kind: v.item ? `${v.item}(${v.vendor})` : v.vendor, days: round_((v.am + v.pm) / 2), text: halfText_(v.amText, v.pmText), }; }); // 統包修繕 const rmap = {}; live(S.REPAIR).forEach(r => { const k = String(r['統包廠商']); rmap[k] = joinText_(rmap[k], r['作業情況']); }); const repairs = Object.keys(rmap).map(k => ({ vendor: k, text: rmap[k] })); // 2. 公司工/點工(依點工公司歸組,個人點工以姓名為一組) const gmap = {}; live(S.LABOR).forEach(r => { const name = String(r['點工姓名']); const g = String(r['點工公司'] || '') || name; const grp = gmap[g] = gmap[g] || { group: g, people: {} }; const p = grp.people[name] = grp.people[name] || { name, am: 0, pm: 0, amText: '', pmText: '' }; if (r['時段'] === '上午') { p.am = num_(r['人數']); p.amText = joinText_(p.amText, r['施作內容']); } else { p.pm = num_(r['人數']); p.pmText = joinText_(p.pmText, r['施作內容']); } }); const labor = Object.keys(gmap).map(g => { const ppl = Object.keys(gmap[g].people).map(n => gmap[g].people[n]); const days = round_(ppl.reduce((s, p) => s + (p.am + p.pm) / 2, 0)); const solo = ppl.length === 1 && ppl[0].name === g; const text = ppl.map(p => (solo ? '' : p.name + ':') + halfText_(p.amText, p.pmText)).join('\n'); return { group: g, days, text }; }); // 二、材料/機具 const lines = []; const mmap = {}; live(S.MATERIAL).forEach(r => { const k = String(r['供應廠商'] || '未填廠商'); const q = r['數量'] === '' ? '' : r['數量']; const item = `${r['材料名稱']}${q}${r['單位'] || ''}` + (r['存放位置'] ? `(放${r['存放位置']})` : '') + (r['材料說明'] ? `(${r['材料說明']})` : ''); (mmap[k] = mmap[k] || []).push(item); }); Object.keys(mmap).forEach(k => lines.push(`${k}:${mmap[k].join('、')}`)); const kmap = {}; live(S.MACHINE).forEach(r => { const k = `${r['廠商']}/${r['機具名稱']}`; const m = kmap[k] = kmap[k] || { key: k, unit: String(r['單位'] || ''), am: 0, pm: 0, text: '' }; if (r['時段'] === '上午') m.am = num_(r['數量']); else m.pm = num_(r['數量']); m.text = joinText_(m.text, r['施作內容']); }); Object.keys(kmap).forEach(k => { const m = kmap[k]; const qty = m.unit === '台' ? `${round_((m.am + m.pm) / 2)}台天` : `${m.am + m.pm}${m.unit}`; lines.push(`機具 ${m.key} ${qty}${m.text ? ':' + m.text : ''}`); }); // 停工 const stops = live(S.STOP).map(r => { const scope = r['停工範圍'] === '部分工項' && r['受影響廠商或工項'] ? r['受影響廠商或工項'] : r['停工範圍']; return `${r['停工時段']}${scope}${r['說明'] ? '(' + r['說明'] + ')' : ''}`; }); // 三、四、工程師 const staff = readAll_(S.STAFF).filter(s => s['狀態'] === '啟用' && s['列入日報'] !== '否').map(s => String(s['本名'])); const jrows = readAll_(S.JOURNAL).filter(r => d_(r['日期']) === date); const jOf = n => jrows.find(r => r['回報人'] === n) || {}; const engineers = staff.map(n => { const j = jOf(n); return { name: n, today: String(j['今日工作'] || ''), tomorrow: String(j['明日預定'] || '').replace(/\n/g, ' ') }; }); const leaveToday = staff.filter(n => jOf(n)['今日休假'] === '是'); const leaveTomorrow = staff.filter(n => jOf(n)['明日休假'] === '是'); let wx = ''; try { wx = weatherText_(date); } catch (err) { logError_(err, '日報表天氣'); } return { vendors, repairs, labor, lines, stops, engineers, leaveToday, leaveTomorrow, wx }; } function halfText_(am, pm) { am = String(am || ''); pm = String(pm || ''); if (am && pm && am !== pm) return `上午:${am};下午:${pm}`; return am || pm; } function round_(n) { return Math.round(n * 10) / 10; } // ---------- 版面(比照現有施工日誌) ---------- function buildReport_(sh, d, date) { const WD = '日一二三四五六'; const dt = new Date(date + 'T00:00:00+08:00'); const wd = WD.charAt(Number(fmt_(dt, 'u')) % 7); const wdNext = WD.charAt((Number(fmt_(dt, 'u')) + 1) % 7); const roc = Number(date.slice(0, 4)) - 1911; let r = 1; const put = (c1, c2, v, o) => { o = o || {}; const rg = sh.getRange(r, c1, 1, c2 - c1 + 1); if (c2 > c1) rg.merge(); rg.setValue(v === undefined || v === null ? '' : v); if (o.b) rg.setFontWeight('bold'); if (o.al) rg.setHorizontalAlignment(o.al); if (o.bg) rg.setBackground(o.bg); if (o.size) rg.setFontSize(o.size); return rg; }; const section = text => { put(1, 9, text, { b: true, bg: '#f0efec' }); r++; }; put(1, 9, REPORT.TITLE, { b: true, al: 'center', size: 16 }); r++; const stopTxt = d.stops.length ? ` 停工:${d.stops.join(';')}` : ''; put(1, 9, ` 日期:${roc} 年 ${Number(date.slice(5, 7))} 月 ${date.slice(8)} 日 星期${wd} 天氣:${d.wx}${stopTxt} 值班:`); r++; put(1, 9, ''); r++; section(' 一、本日出工概況:1. 協力廠商'); put(1, 1, '項次', { b: true, al: 'center' }); put(2, 3, '工 種', { b: true, al: 'center' }); put(4, 4, '工 數', { b: true, al: 'center' }); put(5, 9, '施 工 狀 況 概 述', { b: true, al: 'center' }); r++; const vn = Math.max(d.vendors.length, REPORT.MIN_VENDOR_ROWS); for (let i = 0; i < vn; i++) { const v = d.vendors[i] || {}; put(1, 1, i + 1, { al: 'center' }); put(2, 3, v.kind || ''); put(4, 4, v.days === undefined ? '' : v.days, { al: 'center' }); put(5, 9, v.text || ''); r++; } section(' 統包修繕'); const rn = Math.max(d.repairs.length, 1); for (let i = 0; i < rn; i++) { const x = d.repairs[i] || {}; put(1, 3, x.vendor || ''); put(4, 4, ''); put(5, 9, x.text || ''); r++; } section(' 2. 今日公司工 /點工 '); const ln = Math.max(d.labor.length, REPORT.MIN_LABOR_ROWS); for (let i = 0; i < ln; i++) { const x = d.labor[i] || {}; put(1, 1, x.group || ''); put(2, 2, x.days === undefined ? '' : x.days, { al: 'center' }); put(3, 9, x.text || ''); r++; } section('二、本日材料/機具'); put(1, 9, d.lines.join('\n')); r++; section(`三、本日工作重點:週${wd} 休假:${d.leaveToday.join('、')}`); d.engineers.forEach((e, i) => { put(1, 1, i + 1, { al: 'center' }); put(2, 2, e.name + ':'); put(3, 9, e.today); r++; }); put(1, 1, '交 辦', { al: 'center' }); put(2, 9, ''); r++; section(`四、明日工作重點:週${wdNext} 休假:${d.leaveTomorrow.join('、')}`); d.engineers.forEach((e, i) => { put(1, 1, i + 1, { al: 'center' }); put(2, 2, e.name + ':'); put(3, 9, e.tomorrow); r++; }); put(1, 1, '簽 認', { al: 'center' }); put(2, 9, ' 經 理:'); r++; const all = sh.getRange(1, 1, r - 1, 9); all.setWrap(true).setVerticalAlignment('middle').setFontSize(11) .setBorder(true, true, true, true, true, true, '#666666', SpreadsheetApp.BorderStyle.SOLID); sh.getRange(1, 1, 1, 9).setFontSize(16); REPORT.COL_WIDTHS.forEach((w, i) => sh.setColumnWidth(i + 1, w)); if (sh.getMaxColumns() > 9) sh.deleteColumns(10, sh.getMaxColumns() - 9); if (sh.getMaxRows() > r + 5) sh.deleteRows(r + 5, sh.getMaxRows() - r - 4); }