歩合給（不動産営業職）の勤怠管理システムの設計ガイド

// ① 日：所定が8hを超える日はその所定、それ以外は8hを超えた時間
// ② 週：所定が40hを超える週はその所定、それ以外は40hを超えた時間（①を除く）
// ③ 月：月の実働合計 − 法定総枠（①②を除く）
残業(h) = ① + ② + ③　　※法定休日の労働は含めず別枠（3割5分）

// 法定総枠は暦日数で決まる（変形労働カレンダーから取得）
// 31日=177.0 / 30日=171.0 / 29日(うるう年)=165.5 / 28日=160.0

// ===== ① 設定・共通（勤怠データ_2025 のApps Scriptに貼り付け） =====
// スクリプト プロパティ（プロジェクトの設定 → スクリプト プロパティ）
//   PUNCH_SHEET_ID  : 打刻フォームの連携シート（ファイルID）
//   REPORT_SHEET_ID : 日報フォームの連携シート（ファイルID）
//   LEAVE_SHEET_ID  : 有給休暇届フォームの連携シート（ファイルID）
//   NOTIFY_EMAIL    : 管理者のメールアドレス
const P = PropertiesService.getScriptProperties();
const TZ = 'Asia/Tokyo';
const SHEETS = { daily: '日報データ', monthly: '月次集計', cal: '変形労働カレンダー',
                 master: '社員マスタ', fix: '修正履歴' };

// 日報データの列番号（PHASE 2 §2 と一致させる）
const D = { date: 1, id: 2, name: 3, inT: 4, outT: 5, brk: 6, work: 7, night: 8,
            mtg: 9, deal: 10, note: 11, nIn: 12, nOut: 13, nPlan: 14, amount: 15, state: 16 };

// 連携シートの見出し（フォームの設問文に合わせて変更）
const H = {
  time: '回答日時', name: 'お名前', kind: '区分',
  // 日報フォーム
  rType: '記録の種類', mtgWho: '相手', mtgMin: '時間（分）', mtgWhat: '内容',
  prop: '物件名', dealType: '種別', amount: '金額（円）', note: '連絡事項',
  nIn: '翌日の出勤予定', nOut: '翌日の退出予定', nPlan: '翌日の予定',
  // 有給休暇届フォーム
  leaveDate: '取得日'
};

// 月の法定総枠（本設計の設定値。厳密な上限は 177.1 / 171.4 / 165.7 / 160.0）
const LEGAL_CAP = { 31: 177.0, 30: 171.0, 29: 165.5, 28: 160.0 };
const WEEK_START = 0;      // 週の起算日：0=日曜（就業規則に合わせる）
const MIN_WAGE = 1200;     // 事業場の最低賃金（円／時）。改定時に更新

function ss_() { return SpreadsheetApp.getActiveSpreadsheet(); }
function sh_(name) { return ss_().getSheetByName(name); }
function fmt_(d, f) { return Utilities.formatDate(new Date(d), TZ, f); }
function norm_(s) { return String(s || '').replace(/[\s　]/g, ''); }
function mail_(subject, body) {
  const to = P.getProperty('NOTIFY_EMAIL');
  if (to) MailApp.sendEmail(to, subject, body);
}

// 連携シートを見出し名で読み、1行＝1オブジェクトにする
function readSheet_(id) {
  if (!id) return [];
  const v = SpreadsheetApp.openById(id).getSheets()[0].getDataRange().getValues();
  if (v.length < 2) return [];
  const head = v[0].map(String);
  return v.slice(1).map(function (row) {
    const o = {};
    head.forEach(function (k, i) { o[k] = row[i]; });
    return o;
  });
}

// 社員マスタ：氏名 → { id, pay, fixedPay, stdHours, commissionRate }
function master_() {
  const m = {};
  sh_(SHEETS.master).getDataRange().getValues().slice(1).forEach(function (r) {
    if (!r[1]) return;
    m[norm_(r[1])] = { id: r[0], name: r[1], payType: r[7], fixedPay: Number(r[8]) || 0,
                       rate: Number(r[9]) || 0, guarantee: Number(r[10]) || 0,
                       stdHours: Number(r[11]) || 0 };
  });
  return m;
}

// 【トリガー】10分おき：打刻・日報・修正をまとめて反映
function syncAll() {
  syncPunches();
  syncReports();
  applyFixes();
}

// ===== ② 打刻の転記（連携シート「打刻」→ 日報データ） =====
// 直近3日分を毎回作り直す（何度動かしても同じ結果になる）。修正済の日は触らない
function syncPunches() {
  const daily = sh_(SHEETS.daily);
  const master = master_();
  const since = new Date(); since.setDate(since.getDate() - 3);
  const byKey = {};
  readSheet_(P.getProperty('PUNCH_SHEET_ID')).forEach(function (r) {
    const t = new Date(r[H.time]);
    if (isNaN(t.getTime()) || t < since) return;
    // 深夜0〜5時の打刻は前日の勤務として扱う
    const workDate = new Date(t); if (t.getHours() < 5) workDate.setDate(workDate.getDate() - 1);
    const key = norm_(r[H.name]) + '|' + fmt_(workDate, 'yyyy/MM/dd');
    (byKey[key] = byKey[key] || []).push({ t: t, kind: String(r[H.kind]) });
  });
  Object.keys(byKey).forEach(function (key) {
    const parts = key.split('|');
    const m = master[parts[0]];
    if (!m) return;                                   // マスタにない名前は無視（表記ゆれは管理者が確認）
    const ps = byKey[key].sort(function (a, b) { return a.t - b.t; });
    let inT = null, outT = null, brkStart = null, brkMin = 0;
    ps.forEach(function (p) {
      if (p.kind === '出勤' && !inT) inT = p.t;          // 最初の出勤
      if (p.kind === '退勤') outT = p.t;                 // 最後の退勤
      if (p.kind === '休憩開始') brkStart = p.t;
      if (p.kind === '休憩終了' && brkStart) { brkMin += (p.t - brkStart) / 60000; brkStart = null; }
    });
    const row = findDailyRow_(daily, parts[1], m.name);
    if (daily.getRange(row, D.state).getValue() === '修正済') return;
    daily.getRange(row, D.date).setValue(parts[1]);
    daily.getRange(row, D.id).setValue(m.id);
    daily.getRange(row, D.name).setValue(m.name);
    if (inT) daily.getRange(row, D.inT).setValue(fmt_(inT, 'HH:mm'));
    if (outT) daily.getRange(row, D.outT).setValue(fmt_(outT, 'HH:mm'));
    daily.getRange(row, D.brk).setValue(Math.round(brkMin));
    if (inT && outT) {
      const w = calcWorkMinutes_(inT, outT, Math.round(brkMin));
      daily.getRange(row, D.work).setValue(w.work);
      daily.getRange(row, D.night).setValue(w.night);
    }
    daily.getRange(row, D.state).setValue('自動');
  });
}

// 日報データで（日付, 氏名）の行を探す。なければ末尾に追加して行番号を返す
function findDailyRow_(daily, dateStr, name) {
  const v = daily.getDataRange().getValues();
  for (let i = 1; i < v.length; i++) {
    if (fmt_(v[i][0], 'yyyy/MM/dd') === dateStr && norm_(v[i][2]) === norm_(name)) return i + 1;
  }
  return daily.getLastRow() + 1;
}

// 実働（分）と深夜（22:00〜翌5:00）の分数
function calcWorkMinutes_(inT, outT, breakMin) {
  const gross = (outT - inT) / 60000;
  let night = 0;
  for (let t = new Date(inT); t < outT; t.setMinutes(t.getMinutes() + 1)) {
    const h = t.getHours();
    if (h >= 22 || h < 5) night++;
  }
  return { work: Math.max(0, Math.round(gross - breakMin)), night: night };
}

// ===== ③ 日報の転記（連携シート「日報」→ 日報データ I〜O 列） =====
function syncReports() {
  const daily = sh_(SHEETS.daily);
  const master = master_();
  const since = new Date(); since.setDate(since.getDate() - 3);
  const byKey = {};
  readSheet_(P.getProperty('REPORT_SHEET_ID')).forEach(function (r) {
    const t = new Date(r[H.time]);
    if (isNaN(t.getTime()) || t < since) return;
    const key = norm_(r[H.name]) + '|' + fmt_(t, 'yyyy/MM/dd');
    (byKey[key] = byKey[key] || []).push(r);
  });
  Object.keys(byKey).forEach(function (key) {
    const parts = key.split('|');
    const m = master[parts[0]];
    if (!m) return;
    const mtg = [], deal = [], note = [];
    let amount = 0, nIn = '', nOut = '', nPlan = '';
    byKey[key].forEach(function (r) {
      const type = String(r[H.rType] || '');
      if (type === '打ち合わせ') mtg.push(r[H.mtgWho] + ' ' + r[H.mtgMin] + '分 ' + r[H.mtgWhat]);
      if (type === '成約報告') { deal.push(r[H.prop] + ' ' + r[H.dealType]); amount += Number(r[H.amount]) || 0; }
      if (type === '報告事項') { note.push(String(r[H.note] || '')); nIn = r[H.nIn] || nIn; nOut = r[H.nOut] || nOut; nPlan = r[H.nPlan] || nPlan; }
    });
    const row = findDailyRow_(daily, parts[1], m.name);
    daily.getRange(row, D.date).setValue(parts[1]);
    daily.getRange(row, D.id).setValue(m.id);
    daily.getRange(row, D.name).setValue(m.name);
    daily.getRange(row, D.mtg).setValue(mtg.join('／'));
    daily.getRange(row, D.deal).setValue(deal.join('／'));
    daily.getRange(row, D.note).setValue(note.join('／'));
    daily.getRange(row, D.nIn).setValue(nIn);
    daily.getRange(row, D.nOut).setValue(nOut);
    daily.getRange(row, D.nPlan).setValue(nPlan);
    daily.getRange(row, D.amount).setValue(amount);
  });
}

// ===== ⑤ 1か月単位変形労働時間制の残業判定（日→週→月）と割増賃金 =====
// cal：{ 'yyyy/MM/dd': { std: 所定(h), legalHoliday: true/false } }
function calcOvertime_(rows, cal, daysInMonth) {
  let dayOt = 0, weekOt = 0, holiday = 0, total = 0;
  const weeks = {};
  rows.forEach(function (r) {
    const dateStr = fmt_(r.date, 'yyyy/MM/dd');
    const c = cal[dateStr] || { std: 0, legalHoliday: false };
    const h = r.work / 60;
    if (c.legalHoliday) { holiday += h; return; }     // 法定休日労働は別枠（3割5分）
    total += h;
    const dayLimit = Math.max(8, c.std);               // 所定が8h超の日はその所定、それ以外は8h
    const ot = Math.max(0, h - dayLimit);
    dayOt += ot;
    const wk = weekKey_(new Date(r.date));
    weeks[wk] = weeks[wk] || { work: 0, std: 0, dayOt: 0 };
    weeks[wk].work += h; weeks[wk].std += c.std; weeks[wk].dayOt += ot;
  });
  Object.keys(weeks).forEach(function (k) {
    const w = weeks[k];
    const weekLimit = Math.max(40, w.std);             // 所定が40h超の週はその所定、それ以外は40h
    weekOt += Math.max(0, w.work - weekLimit - w.dayOt);
  });
  const cap = LEGAL_CAP[daysInMonth];
  const monthOt = Math.max(0, total - cap - dayOt - weekOt);
  return { total: total, holiday: holiday, dayOt: dayOt, weekOt: weekOt, monthOt: monthOt,
           overtime: dayOt + weekOt + monthOt };
}

// 週の起算日（WEEK_START）でそろえた週キー
function weekKey_(d) {
  const x = new Date(d);
  x.setDate(x.getDate() - ((x.getDay() - WEEK_START + 7) % 7));
  return fmt_(x, 'yyyy/MM/dd');
}

// 固定給分（月平均所定労働時間で割る）＋歩合給分（その月の総労働時間で割る）
function calcPremium_(m, ot, holidayH, nightH, commission, totalH) {
  const fixedUnit = m.stdHours ? m.fixedPay / m.stdHours : 0;
  const commUnit = totalH ? commission / totalH : 0;
  return {
    fixedUnit: fixedUnit, commUnit: commUnit,
    overtime: fixedUnit * ot * 1.25 + commUnit * ot * 0.25,
    holiday: fixedUnit * holidayH * 1.35 + commUnit * holidayH * 0.35,
    night: (fixedUnit + commUnit) * nightH * 0.25,
    total: fixedUnit * ot * 1.25 + commUnit * ot * 0.25 +
           fixedUnit * holidayH * 1.35 + commUnit * holidayH * 0.35 +
           (fixedUnit + commUnit) * nightH * 0.25
  };
}

// ===== ⑥ 月次集計（毎晩23時のトリガーで当月分を更新） =====
function aggregateMonthly() {
  const now = new Date();
  const ym = fmt_(now, 'yyyy-MM');
  const daysInMonth = new Date(now.getFullYear(), now.getMonth() + 1, 0).getDate();
  const master = master_();
  const cal = {};
  sh_(SHEETS.cal).getDataRange().getValues().slice(1).forEach(function (r) {
    if (r[0]) cal[fmt_(r[0], 'yyyy/MM/dd')] = { std: Number(r[5]) || 0, legalHoliday: String(r[6]).trim() === '○' };
  });
  const byStaff = {};
  sh_(SHEETS.daily).getDataRange().getValues().slice(1).forEach(function (r) {
    if (!r[0] || fmt_(r[0], 'yyyy-MM') !== ym || !r[D.work - 1]) return;
    const k = norm_(r[D.name - 1]);
    (byStaff[k] = byStaff[k] || []).push({ date: r[0], work: Number(r[D.work - 1]) || 0,
      night: Number(r[D.night - 1]) || 0, mtg: r[D.mtg - 1], deal: r[D.deal - 1], amount: Number(r[D.amount - 1]) || 0 });
  });
  const out = [];
  const alerts = [];
  Object.keys(byStaff).forEach(function (k) {
    const m = master[k]; if (!m) return;
    const rows = byStaff[k];
    const ot = calcOvertime_(rows, cal, daysInMonth);
    const nightH = rows.reduce(function (s, r) { return s + r.night; }, 0) / 60;
    const mtgCnt = rows.filter(function (r) { return r.mtg; }).length;
    const dealCnt = rows.filter(function (r) { return r.deal; }).length;
    const amount = rows.reduce(function (s, r) { return s + r.amount; }, 0);
    const commission = Math.round(amount * m.rate);   // 歩合給（給与規程で確定した額に手で差し替え可）
    const totalH = ot.total + ot.holiday;
    const prem = calcPremium_(m, ot.overtime, ot.holiday, nightH, commission, totalH);
    const hourly = totalH ? (m.fixedPay + commission) / totalH : 0;
    const minOk = hourly >= MIN_WAGE ? 'OK' : '要確認';
    if (minOk !== 'OK') alerts.push(m.name + '：時間額 ' + Math.round(hourly) + '円');
    out.push([ym, m.id, m.name, rows.length, round1_(totalH), LEGAL_CAP[daysInMonth],
      round1_(ot.overtime), round1_(nightH), mtgCnt, dealCnt, amount, commission, m.fixedPay,
      Math.round(prem.commUnit), Math.round(prem.total), round1_(ot.holiday), minOk]);
  });
  const sheet = sh_(SHEETS.monthly);
  const v = sheet.getDataRange().getValues();
  for (let i = v.length - 1; i >= 1; i--) { if (v[i][0] === ym) sheet.deleteRow(i + 1); }
  if (out.length) sheet.getRange(sheet.getLastRow() + 1, 1, out.length, out[0].length).setValues(out);
  if (alerts.length) mail_('【勤怠】最低賃金の要確認 ' + ym, alerts.join('\n'));
}
function round1_(x) { return Math.round(x * 10) / 10; }

// ===== ④ 修正申請の反映（修正履歴シートで管理者が承認した行を日報データへ） =====
// 修正履歴：A申請日時 B対象社員 C対象日 D項目 E修正前 F修正後 G申請者 H承認者 I反映日時
function applyFixes() {
  const fix = sh_(SHEETS.fix);
  const daily = sh_(SHEETS.daily);
  const v = fix.getDataRange().getValues();
  for (let i = 1; i < v.length; i++) {
    if (!v[i][7] || v[i][8]) continue;                 // 未承認、または反映済みは飛ばす
    const dateStr = fmt_(v[i][2], 'yyyy/MM/dd');
    const row = findDailyRow_(daily, dateStr, v[i][1]);
    const col = { '出勤': D.inT, '退勤': D.outT, '休憩': D.brk }[String(v[i][3])];
    if (!col) continue;
    fix.getRange(i + 1, 5).setValue(daily.getRange(row, col).getValue()); // 修正前を保存
    daily.getRange(row, col).setValue(v[i][5]);                            // 修正後を反映
    daily.getRange(row, D.date).setValue(dateStr);
    daily.getRange(row, D.name).setValue(v[i][1]);
    daily.getRange(row, D.state).setValue('修正済');
    recalcDailyRow_(daily, row);
    fix.getRange(i + 1, 9).setValue(new Date());
  }
}

// 1行分の実働・深夜を出勤・退勤・休憩から再計算する
function recalcDailyRow_(daily, row) {
  const r = daily.getRange(row, 1, 1, D.state).getValues()[0];
  if (!r[D.inT - 1] || !r[D.outT - 1]) return;
  const base = fmt_(r[0], 'yyyy/MM/dd');
  const inT = new Date(base + ' ' + fmt_(r[D.inT - 1], 'HH:mm'));
  const outT = new Date(base + ' ' + fmt_(r[D.outT - 1], 'HH:mm'));
  if (outT <= inT) outT.setDate(outT.getDate() + 1);  // 日付をまたぐ勤務
  const w = calcWorkMinutes_(inT, outT, Number(r[D.brk - 1]) || 0);
  daily.getRange(row, D.work).setValue(w.work);
  daily.getRange(row, D.night).setValue(w.night);
}

// ===== ⑦ 未打刻アラート（毎日10:30のトリガー：出勤打刻がない在籍者を管理者へメール） =====
function alertUnpunched() {
  const today = fmt_(new Date(), 'yyyy/MM/dd');
  const punched = new Set(
    readSheet_(P.getProperty('PUNCH_SHEET_ID'))
      .filter(function (r) { return fmt_(r[H.time], 'yyyy/MM/dd') === today && String(r[H.kind]) === '出勤'; })
      .map(function (r) { return norm_(r[H.name]); })
  );
  const onLeave = new Set(
    readSheet_(P.getProperty('LEAVE_SHEET_ID'))
      .filter(function (r) { return fmt_(r[H.leaveDate], 'yyyy/MM/dd') === today; })
      .map(function (r) { return norm_(r[H.name]); })
  );
  const cal = sh_(SHEETS.cal).getDataRange().getValues().slice(1)
    .filter(function (r) { return r[0] && fmt_(r[0], 'yyyy/MM/dd') === today; })[0];
  if (cal && String(cal[2]) === '休日') return;        // 会社の休日は送らない
  const missing = Object.keys(master_()).filter(function (k) { return !punched.has(k) && !onLeave.has(k); });
  if (missing.length) mail_('【勤怠】出勤打刻なし ' + today + '（' + missing.length + '名）', missing.join('\n'));
}