const TZ = 'Asia/Tokyo'; const SH_BOOK = '予約'; const SH_HOTEL = 'ホテル'; const SH_PLAN = 'プラン'; const STATUS_OK = '予約'; const STATUS_CANCEL = 'キャンセル'; const DEFAULT_RATE = 15; const BOOK_HEADERS = [ 'ID', '登録日時', 'ホテル名', '参加日', 'プラン', '人数', 'お客様名', '料金合計', '手数料率(%)', '紹介手数料', 'スタジオへのお戻し額', 'ステータス', 'メモ' ]; function setup() { const ss = SpreadsheetApp.getActive(); const make = (name, headers, rows) => { let sh = ss.getSheetByName(name); if (!sh) sh = ss.insertSheet(name); if (sh.getLastRow() === 0) { sh.getRange(1, 1, 1, headers.length).setValues([headers]) .setFontWeight('bold').setBackground('#e3f5f9'); sh.setFrozenRows(1); if (rows.length) sh.getRange(2, 1, rows.length, headers.length).setValues(rows); sh.autoResizeColumns(1, headers.length); } return sh; }; make(SH_HOTEL, ['ホテル名', 'アクセスコード', '手数料率(%)', '管理者'], [ ['まゆ（管理者）', randomCode_(), '', true], ['サンプルホテル', randomCode_(), 15, false], ]); make(SH_PLAN, ['プラン名', '1名参加のときの1人料金', '2名以上のときの1人料金'], [ ['ビーチヨガ（グループ）', 8900, 6900], ['ビーチヨガ（プライベート）', 16500, 11000], ['SUPヨガ（4〜10月）', 16500, 11000], ]); const b = make(SH_BOOK, BOOK_HEADERS, []); b.getRange('D:D').setNumberFormat('@'); b.getRange('H:H').setNumberFormat('#,##0'); b.getRange('J:K').setNumberFormat('#,##0'); const def = ss.getSheetByName('シート1'); if (def && def.getLastRow() === 0 && ss.getSheets().length > 1) ss.deleteSheet(def); } function randomCode_() { const chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789'; let s = ''; for (let i = 0; i < 6; i++) s += chars.charAt(Math.floor(Math.random() * chars.length)); return s; } function doGet(e) { const t = HtmlService.createTemplateFromFile('Index'); t.key = (e && e.parameter && e.parameter.key) ? String(e.parameter.key).trim() : ''; return t.evaluate() .setTitle('ご紹介予約・手数料ページ｜Srishti Studio') .addMetaTag('viewport', 'width=device-width, initial-scale=1') .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); } function getData(key) { const hotel = auth_(key); const plans = getPlans_(); const hotels = hotel.admin ? getHotels_().filter(h => !h.admin).map(h => ({ name: h.name, rate: h.rate })) : []; return { hotel: { name: hotel.name, rate: hotel.rate, admin: hotel.admin }, hotels: hotels, plans: plans, bookings: listBookings_(hotel), today: Utilities.formatDate(new Date(), TZ, 'yyyy-MM-dd'), }; } function addBooking(key, d) { const me = auth_(key); d = d || {}; let target = me; if (me.admin) { target = getHotels_().find(h => !h.admin && h.name === d.hotel); if (!target) throw new Error('ホテルを選んでください'); } const date = String(d.date || ''); if (!/^\d{4}-\d{2}-\d{2}$/.test(date)) throw new Error('参加日を入れてください'); const people = parseInt(d.people, 10); if (!(people >= 1 && people <= 30)) throw new Error('人数は1〜30名で入れてください'); const plan = getPlans_().find(p => p.name === d.plan); if (!plan) throw new Error('プランを選んでください'); const calc = calc_(plan, people, target.rate); const row = [ Utilities.getUuid().slice(0, 8), Utilities.formatDate(new Date(), TZ, 'yyyy-MM-dd HH:mm'), target.name, date, plan.name, people, clip_(d.guest, 50), calc.total, target.rate, calc.fee, calc.back, STATUS_OK, clip_(d.memo, 200), ]; const lock = LockService.getScriptLock(); lock.waitLock(10000); try { const sh = SpreadsheetApp.getActive().getSheetByName(SH_BOOK); const r = sh.getLastRow() + 1; sh.getRange(r, 4).setNumberFormat('@'); sh.getRange(r, 1, 1, row.length).setValues([row]); } finally { lock.releaseLock(); } return getData(key); } function cancelBooking(key, id) { const me = auth_(key); const sh = SpreadsheetApp.getActive().getSheetByName(SH_BOOK); const values = sh.getDataRange().getValues(); for (let i = 1; i < values.length; i++) { if (String(values[i][0]) === String(id)) { if (!me.admin && values[i][2] !== me.name) throw new Error('この予約は変更できません'); sh.getRange(i + 1, 12).setValue(STATUS_CANCEL); return getData(key); } } throw new Error('予約が見つかりませんでした'); } function calc_(plan, people, rate) { const per = people >= 2 ? plan.p2 : plan.p1; const total = per * people; const fee = Math.round(total * rate / 100); return { per: per, total: total, fee: fee, back: total - fee }; } function auth_(key) { key = String(key || '').trim(); if (!key) throw new Error('コードを入れてください'); const h = getHotels_().find(x => x.code === key); if (!h) throw new Error('コードが違うようです。もう一度ご確認ください'); return h; } function getHotels_() { const sh = SpreadsheetApp.getActive().getSheetByName(SH_HOTEL); return sh.getDataRange().getValues().slice(1) .filter(r => r[0] && r[1]) .map(r => ({ name: String(r[0]).trim(), code: String(r[1]).trim(), rate: r[2] === '' || r[2] === null ? DEFAULT_RATE : Number(r[2]), admin: r[3] === true || String(r[3]).toUpperCase() === 'TRUE', })); } function getPlans_() { const sh = SpreadsheetApp.getActive().getSheetByName(SH_PLAN); return sh.getDataRange().getValues().slice(1) .filter(r => r[0] && Number(r[1]) > 0) .map(r => ({ name: String(r[0]).trim(), p1: Number(r[1]), p2: Number(r[2]) > 0 ? Number(r[2]) : Number(r[1]) })); } function listBookings_(hotel) { const sh = SpreadsheetApp.getActive().getSheetByName(SH_BOOK); return sh.getDataRange().getValues().slice(1) .filter(r => r[0] && (hotel.admin || r[2] === hotel.name)) .map(r => ({ id: String(r[0]), created: fmt_(r[1], 'yyyy-MM-dd HH:mm'), hotel: String(r[2]), date: fmt_(r[3], 'yyyy-MM-dd'), plan: String(r[4]), people: Number(r[5]) || 0, guest: String(r[6] || ''), total: Number(r[7]) || 0, rate: Number(r[8]) || 0, fee: Number(r[9]) || 0, back: Number(r[10]) || 0, status: String(r[11] || STATUS_OK), memo: String(r[12] || ''), })); } function fmt_(v, pattern) { if (v instanceof Date) return Utilities.formatDate(v, TZ, pattern); return String(v || ''); } function clip_(s, n) { return String(s || '').trim().slice(0, n); }