Download utils/forms_csv_parser.py from sdu-shift/app: direct link, hf CLI and curl.
- Browser
- Download file 29.2 kB
-
https://huggingface.co/spaces/sdu-shift/app/resolve/main/utils/forms_csv_parser.py
- Command line
-
hf download hf://spaces/sdu-shift/app/utils/forms_csv_parser.py
-
curl -L -o forms_csv_parser.py https://huggingface.co/spaces/sdu-shift/app/resolve/main/utils/forms_csv_parser.py
29.2 kB
| """Google Forms CSV ใคใณใใผใใใผใตใผ | |
| ๆๅพ ใใ CSV ใใฉใผใใใ๏ผGoogle Forms ๅ็ญใฎในใใฌใใใทใผใใ CSV ๅบๅ๏ผ: | |
| ๅ1: ใฟใคใ ในใฟใณใ๏ผ็ก่ฆ๏ผ | |
| ๅ2: ใๅๅ๏ผในใฟใใ็ป้ฒๅใจๅฎๅ จไธ่ด๏ผ | |
| ๅ3ใ: "4ๆ1ๆฅ(ๆ)ใฎๅบๅคๅธๆ" ๅฝขๅผใฎๆฅไปๅ๏ผใใซใใฆใณๅค๏ผ | |
| ๆซๅฐพ: ใซในใฟใ ๅ ฅๅใฎๆๅป๏ผไปปๆ๏ผใๅ่๏ผไปปๆ๏ผ | |
| """ | |
| from __future__ import annotations | |
| import csv | |
| import re | |
| from dataclasses import dataclass, field | |
| from typing import Optional | |
| from models.schedule import ShiftRequest, SchedulePeriod | |
| from models.employee import Employee | |
| from utils.shift_patterns import ALL_PATTERNS | |
| # ใฉใใซ โ ใใฟใผใณID ใฎ้ๅผใใใใ๏ผGoogle Forms ้ธๆ่ขใฉใใซใจไธ่ดใใใ๏ผ | |
| _LABEL_TO_ID: dict[str, Optional[str]] = { | |
| "ไผใฟ๏ผๅบๅคไธๅฏ๏ผ": None, | |
| "ไผใฟ": None, | |
| "ใซในใฟใ ๏ผๅ่ใซๆๅปใ่จๅ ฅ๏ผ": "custom", | |
| "ใซในใฟใ ": "custom", | |
| "ๆ็ตฆ": "paid_leave", | |
| "ๆ็ตฆไผๆ": "paid_leave", | |
| } | |
| for _p in ALL_PATTERNS: | |
| _LABEL_TO_ID[_p.label] = _p.id | |
| class ImportResult: | |
| requests: list[ShiftRequest] = field(default_factory=list) | |
| # ในใฟใใๅ โ ๅๅพไปถๆฐ๏ผใใใใใในใฟใใใฎใฟ๏ผ | |
| matched: dict[str, int] = field(default_factory=dict) | |
| unmatched_names: list[str] = field(default_factory=list) | |
| warnings: list[str] = field(default_factory=list) | |
| def _parse_date_from_header(header: str, period: SchedulePeriod) -> Optional[str]: | |
| """ | |
| ใ4ๆ1ๆฅ(ๆ)ใฎๅบๅคๅธๆใใชใฉใฎใใใใผใใ YYYY-MM-DD ใ่ฟใใ | |
| ๆ้ใซๅซใพใใชใๆฅไปใฏ None ใ่ฟใใ | |
| """ | |
| m = re.search(r'(\d{1,2})ๆ(\d{1,2})ๆฅ', header) | |
| if not m: | |
| return None | |
| month, day = int(m.group(1)), int(m.group(2)) | |
| for d in period.date_range(): | |
| if d.month == month and d.day == day: | |
| return d.isoformat() | |
| return None | |
| def _parse_numbered_shift_header(header: str, period: SchedulePeriod) -> Optional[str]: | |
| """ | |
| ใๅธๆใทใใ [N]ใๅฝขๅผใฎใใใใผใใ YYYY-MM-DD ใ่ฟใใ | |
| N ใฏ 1 ๅงใพใใง period.start_date ใใ N-1 ๆฅๅพใซๅฏพๅฟใใใ | |
| ไพ: ใๅธๆใทใใ [3]ใโ period.start_date + 2 days | |
| """ | |
| m = re.search(r'\[(\d+)\]', header) | |
| if not m: | |
| return None | |
| n = int(m.group(1)) | |
| dates = list(period.date_range()) | |
| if 1 <= n <= len(dates): | |
| return dates[n - 1].isoformat() | |
| return None | |
| def _time_range_to_pattern(time_str: str) -> tuple[Optional[str], Optional[str], Optional[str]]: | |
| """ | |
| ใ6:00~10:00ใใชใฉใฎๆๅป็ฏๅฒๆๅญๅใ (pattern_id, custom_start, custom_end) ใซๅคๆใใใ | |
| - ๆขๅญใใฟใผใณใฎ start/end ใจๅฎๅ จไธ่ด โ (pattern_id, None, None) | |
| - ไธ่ดใชใ โ ("custom", start, end) | |
| - ่งฃๆไธ่ฝ โ (None, None, None) | |
| ๅฏพๅฟใใฉใผใใใ: HH:MM ~ HH:MM๏ผๅ่งใปๅ จ่งใฉใกใใๅฏ๏ผ | |
| """ | |
| import unicodedata | |
| s = unicodedata.normalize('NFKC', time_str.strip()) | |
| m = re.match(r'^(\d{1,2}:\d{2})\s*[~ใ๏ฝ\-โโ]+\s*(\d{1,2}:\d{2})$', s) | |
| if not m: | |
| return None, None, None | |
| def _norm(t: str) -> str: | |
| h, mn = t.split(':') | |
| return f"{int(h):02d}:{mn}" | |
| start = _norm(m.group(1)) | |
| end = _norm(m.group(2)) | |
| for p in ALL_PATTERNS: | |
| if p.start and p.end: | |
| if _norm(p.start) == start and _norm(p.end) == end: | |
| return p.id, None, None | |
| # ๆขๅญใใฟใผใณใซไธ่ดใใชใ โ ใซในใฟใ ใจใใฆๆๅปใไฟๆ | |
| return "custom", start, end | |
| def _parse_custom_times(raw: str, period: SchedulePeriod) -> dict[str, tuple[str, str]]: | |
| """ | |
| ใ4/1: 10:00ใ16:00, 4/5: 11:00ใ20:00ใใชใฉใ่งฃๆใใฆ | |
| {date_str: (start, end)} ใ่ฟใใ | |
| ๅฏพๅฟใใฉใผใใใ: | |
| - 4/1: 10:00ใ16:00 | |
| - 4ๆ1ๆฅ: 10:00ใ16:00 | |
| - 4.1 10:00ใ16:00 | |
| - 4/1 10:00-16:00 | |
| - 4/1 10:00๏ฝ16:00 | |
| - 4/1 10:00โ16:00 ๏ผen dash๏ผ | |
| - ๅ จ่งๆฐๅญใปๅ จ่งใณใญใณ๏ผ่ชๅใงๅ่งใซๅคๆ๏ผ | |
| """ | |
| import unicodedata | |
| # ๅ จ่ง่ฑๆฐ่จๅทใๅ่งใซ็ตฑไธ๏ผ๏ผโ1ใ๏ผโ: ใชใฉ๏ผ | |
| raw = unicodedata.normalize('NFKC', raw) | |
| result: dict[str, tuple[str, str]] = {} | |
| pattern = re.findall( | |
| r'(\d{1,2})[/ๆ.\-](\d{1,2})[ๆฅ]?' # ๆฅไป: 4/1 4ๆ1ๆฅ 4.1 4-1 | |
| r'[\s:๏ผ]*' # ๅบๅใ (ใณใญใณใปในใใผในใปใชใ) | |
| r'(\d{1,2}:\d{2})' # ้ๅงๆๅป | |
| r'\s*[ใ~๏ฝ\-โโ]+\s*' # ็ฏๅฒ่จๅท | |
| r'(\d{1,2}:\d{2})', # ็ตไบๆๅป | |
| raw, | |
| ) | |
| for month_s, day_s, start, end in pattern: | |
| for d in period.date_range(): | |
| if d.month == int(month_s) and d.day == int(day_s): | |
| result[d.isoformat()] = (start, end) | |
| break | |
| return result | |
| def parse_forms_csv( | |
| csv_path: str, | |
| period: SchedulePeriod, | |
| employees: list[Employee], | |
| ) -> ImportResult: | |
| """ | |
| Google Forms ใฎๅ็ญ CSV ใ่งฃๆใใฆ ImportResult ใ่ฟใใ | |
| 3็จฎ้กใฎใใฉใผใ ๅฝขๅผใซ่ชๅๅฏพๅฟ: | |
| ใๆงๅฝขๅผใใฐใชใใ่ณชๅ / 1ๆฅ1ๅ | |
| ๅใใใใผไพ: ใ4ๆ1ๆฅ(ๆ)ใฎๅบๅคๅธๆใ | |
| ๅค: ใใฟใผใณใฉใใซ็ดๆฅ | |
| ใๆฐๅฝขๅผใGASใใฉใผใ / 1ๆฅ3ๅ | |
| ๅใใใใผไพ: | |
| ใ4ๆ1ๆฅ๏ผๆ๏ผใฏๅบๅคใงใใพใใ๏ผใ โ ๅบๅคๅฏๅฆๅ | |
| ใ4ๆ1ๆฅ๏ผๆ๏ผๆ้ฃใฎๅธๆใทใใใโฆใ โ ๆ้ฃๆ้ๅ | |
| ใ4ๆ1ๆฅ๏ผๆ๏ผใใฃใใผใฎๅธๆใทใใใโฆใโ ใใฃใใผๆ้ๅ | |
| ๅค: | |
| ๅฏๅฆๅ: ใๆ้ฃใฎใฟใใใใฃใใผใฎใฟใใใใใซ๏ผๆ้ฃ๏ผใใฃใใผไธกๆน๏ผใใไผใฟใ | |
| ๆ้ๅ: ใใฟใผใณใฉใใซ orใใใฎไป๏ผๅ่ๆฌใซๆๅปใ่จๅ ฅ๏ผใ | |
| ใใใงใใฏใใใฏในๅฝขๅผใ1ๆฅ1ๅ๏ผ่คๆฐ้ธๆ๏ผ | |
| ๅใใใใผไพ: | |
| ใ4ๆ1ๆฅ๏ผๆ๏ผใฎๅบๅคๅธๆๆ้ใ | |
| ๅค: | |
| ่คๆฐ้ธๆใใใๅคใใซใณใๅบๅใใง็ตๅใใใๆๅญๅ | |
| ๏ผไพ: "ๆ้ฃ: 6:00ใ10:00, ใใฃใใผ: 17:00ใ21:00"๏ผ | |
| """ | |
| result = ImportResult() | |
| name_to_emp: dict[str, Employee] = {e.name: e for e in employees} | |
| # ใจใณใณใผใใฃใณใฐใๅคๅฎ๏ผUTF-8 (BOMไปใๅซใ) ใง่ชญใใชใใใฐ cp932 ใงๅ่ฉฆ่ก๏ผ | |
| encoding = "utf-8-sig" | |
| try: | |
| with open(csv_path, encoding=encoding) as f: | |
| f.read() | |
| except UnicodeDecodeError: | |
| encoding = "cp932" | |
| result.warnings.append( | |
| "UTF-8 ใง่ชญใฟ่พผใใชใใฃใใใ cp932 ใงๅ่ฉฆ่กใใพใใใ" | |
| "ๆฌกๅใใใฏ CSV ใ UTF-8 ใงไฟๅญใใใใจใๆจๅฅจใใพใใ" | |
| ) | |
| try: | |
| with open(csv_path, encoding=encoding, newline="") as f: | |
| reader = csv.DictReader(f) | |
| headers = list(reader.fieldnames or []) | |
| # โโ ๅๅๅใ็นๅฎ โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ | |
| name_col = next( | |
| (h for h in headers if "ๅๅ" in h or "ๆฐๅ" in h), None | |
| ) | |
| if not name_col: | |
| result.warnings.append( | |
| "ๅๅๅใ่ฆใคใใใพใใใงใใใ" | |
| "ใใๅๅใใพใใฏใๆฐๅใใจใใ่ณชๅใใใใ็ขบ่ชใใฆใใ ใใใ" | |
| ) | |
| return result | |
| # ๅ่ๅใปใซในใฟใ ๆๅปๅ | |
| note_col = next((h for h in headers if "ๅ่" in h or "ใณใกใณใ" in h), None) | |
| custom_col = next((h for h in headers if "ใซในใฟใ " in h and "ๆๅป" in h), None) | |
| # โโ ๆฅไปๅใๅ้ก โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ | |
| # date_str โ {"avail": col, "b": col, "d": col, "checkbox": col, "direct": col} | |
| date_cols: dict[str, dict[str, str]] = {} | |
| for h in headers: | |
| ds = _parse_date_from_header(h, period) | |
| if not ds: | |
| ds = _parse_numbered_shift_header(h, period) # ใๅธๆใทใใ [N]ใๅฝขๅผ | |
| if not ds: | |
| continue | |
| if ds not in date_cols: | |
| date_cols[ds] = {} | |
| if "ๅบๅคใงใใพใใ" in h: | |
| date_cols[ds]["avail"] = h # ๆฐๅฝขๅผ: ๅบๅคๅฏๅฆ | |
| elif "ๆ้ฃ" in h and "ใทใใ" in h: | |
| date_cols[ds]["b"] = h # ๆฐๅฝขๅผ: ๆ้ฃๆ้ | |
| elif "ใใฃใใผ" in h and "ใทใใ" in h: | |
| date_cols[ds]["d"] = h # ๆฐๅฝขๅผ: ใใฃใใผๆ้ | |
| elif "ๅบๅคๅธๆๆ้" in h or "ๅธๆๆ้" in h: | |
| date_cols[ds]["checkbox"] = h # ใใงใใฏใใใฏในไธๆฌๅฝขๅผ | |
| else: | |
| date_cols[ds]["direct"] = h # ๆงๅฝขๅผ: ใใฟใผใณ็ดๆฅ้ธๆ | |
| if not date_cols: | |
| result.warnings.append( | |
| "ๆฅไปๅใ่ฆใคใใใพใใใงใใใ" | |
| "ใใใใผใซใ4ๆ1ๆฅใใฎใใใชๆฅไปใๅซใพใใฆใใใ็ขบ่ชใใฆใใ ใใใ" | |
| ) | |
| # โโ ่กใใจใซๅฆ็ โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ | |
| for row_num, row in enumerate(reader, start=2): | |
| name = row.get(name_col, "").strip() | |
| if not name: | |
| result.warnings.append(f"่ก{row_num}: ๅๅใ็ฉบใงใ๏ผในใญใใ๏ผ") | |
| continue | |
| emp = name_to_emp.get(name) | |
| if emp is None: | |
| if name not in result.unmatched_names: | |
| result.unmatched_names.append(name) | |
| result.warnings.append( | |
| f"ใ{name}ใใฏในใฟใใ็ฎก็ใซ็ป้ฒใใใฆใใพใใ๏ผในใญใใ๏ผ" | |
| ) | |
| continue | |
| # ๅ่ใปใซในใฟใ ๆๅปใ่งฃๆ | |
| note = row.get(note_col, "").strip() if note_col else "" | |
| custom_times: dict[str, tuple[str, str]] = {} | |
| if note: | |
| custom_times.update(_parse_custom_times(note, period)) | |
| if custom_col and row.get(custom_col, "").strip(): | |
| custom_times.update(_parse_custom_times(row[custom_col], period)) | |
| row_count = 0 | |
| for date_str, cols in date_cols.items(): | |
| req = None | |
| if "checkbox" in cols: | |
| # โโ ใใงใใฏใใใฏในๅฝขๅผ๏ผ่คๆฐ้ธๆ๏ผ โโโโโโโโโโโ | |
| req = _process_checkbox_fmt( | |
| row, date_str, cols["checkbox"], custom_times, note, emp, result, name | |
| ) | |
| elif "avail" in cols: | |
| # โโ ๆฐๅฝขๅผ: ๅฏๅฆๅ + ๆ้ๅ โโโโโโโโโโโโโโโโโโ | |
| req = _process_new_fmt( | |
| row, date_str, cols, custom_times, note, emp, result, name | |
| ) | |
| elif "direct" in cols: | |
| # โโ ๆงๅฝขๅผ: ็ดๆฅใใฟใผใณ้ธๆ โโโโโโโโโโโโโโโโโ | |
| req = _process_direct_fmt( | |
| row, date_str, cols["direct"], custom_times, note, emp, result, name | |
| ) | |
| if req is not None: | |
| result.requests.append(req) | |
| row_count += 1 | |
| result.matched[name] = result.matched.get(name, 0) + row_count | |
| except UnicodeDecodeError as e: | |
| result.warnings.append(f"ๆๅญใณใผใใจใฉใผ: {e}ใCSV ใ UTF-8 ใงไฟๅญใ็ดใใฆใใ ใใใ") | |
| except Exception as e: | |
| result.warnings.append(f"CSV ่งฃๆใจใฉใผ: {e}") | |
| return result | |
| def _process_new_fmt( | |
| row: dict, | |
| date_str: str, | |
| cols: dict[str, str], | |
| custom_times: dict, | |
| note: str, | |
| emp, | |
| result: "ImportResult", | |
| name: str, | |
| ) -> "Optional[ShiftRequest]": | |
| """ๆฐๅฝขๅผ๏ผGAS/APIๅๅฒใใฉใผใ ๏ผใฎ1ๆฅๅใๅฆ็ใใฆ ShiftRequest ใ่ฟใใ""" | |
| avail_val = row.get(cols["avail"], "").strip() | |
| if not avail_val: | |
| return None # ๆชๅ็ญ = ในใญใใ | |
| if avail_val in ("ใใใ๏ผไผใฟ๏ผ", "ใใใ", "ไผใฟ"): | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id=None, custom_start=None, custom_end=None, note=note, | |
| ) | |
| b_val = row.get(cols.get("b", ""), "").strip() if "b" in cols else "" | |
| d_val = row.get(cols.get("d", ""), "").strip() if "d" in cols else "" | |
| # ไปฅๅใฎใๆ้ฃใฎใฟใใใใฃใใผใฎใฟใใใใใซใใฎ้ธๆ่ขใ ใฃใๅ ดๅใฎไบๆๅฆ็ | |
| if "ใใใซ" in avail_val: | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id="double", custom_start=None, custom_end=None, note=note, | |
| ) | |
| elif "ๆ้ฃ" in avail_val and "ใใฃใใผ" not in avail_val: | |
| return _resolve_time_value(b_val, date_str, custom_times, note, emp, result, name) | |
| elif "ใใฃใใผ" in avail_val and "ๆ้ฃ" not in avail_val: | |
| return _resolve_time_value(d_val, date_str, custom_times, note, emp, result, name) | |
| # ๆฐๅฝขๅผ๏ผใฏใ โ ๆ้ฃใปใใฃใใผไธกๆนใใใงใใฏ๏ผ | |
| has_b = bool(b_val and b_val != "ไผใฟ๏ผๅบๅคไธๅฏ๏ผ" and "ไผใฟ" not in b_val) | |
| has_d = bool(d_val and d_val != "ไผใฟ๏ผๅบๅคไธๅฏ๏ผ" and "ไผใฟ" not in d_val) | |
| if has_b and has_d: | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id="double", custom_start=None, custom_end=None, note=note, | |
| ) | |
| elif has_b: | |
| return _resolve_time_value(b_val, date_str, custom_times, note, emp, result, name) | |
| elif has_d: | |
| return _resolve_time_value(d_val, date_str, custom_times, note, emp, result, name) | |
| else: | |
| # ๆ้ๅธฏใซๆๅนใชๅคใใชใใใพใใฏใไผใฟใใ้ธใฐใใฆใใๅ ดๅ | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id=None, custom_start=None, custom_end=None, note=note, | |
| ) | |
| def _process_direct_fmt( | |
| row: dict, | |
| date_str: str, | |
| col: str, | |
| custom_times: dict, | |
| note: str, | |
| emp, | |
| result: "ImportResult", | |
| name: str, | |
| ) -> "Optional[ShiftRequest]": | |
| """ๆงๅฝขๅผ๏ผ็ดๆฅใใฟใผใณ้ธๆ๏ผใฎ1ๆฅๅใๅฆ็ใใฆ ShiftRequest ใ่ฟใใ""" | |
| value = row.get(col, "").strip() | |
| if not value: | |
| return None | |
| if value in ("ไผใฟ๏ผๅบๅคไธๅฏ๏ผ", "ไผใฟ"): | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id=None, custom_start=None, custom_end=None, note=note, | |
| ) | |
| return _resolve_time_value(value, date_str, custom_times, note, emp, result, name) | |
| def _resolve_time_value( | |
| value: str, | |
| date_str: str, | |
| custom_times: dict, | |
| note: str, | |
| emp, | |
| result: "ImportResult", | |
| name: str, | |
| ) -> "Optional[ShiftRequest]": | |
| """ใใฟใผใณใฉใใซๆๅญๅใ ShiftRequest ใซๅคๆใใๅ ฑ้ๅฆ็ใ""" | |
| if not value: | |
| return None | |
| if "ใใฎไป" in value or value in ("ใซในใฟใ ๏ผๅ่ๆฌใซๆๅปใ่จๅ ฅ๏ผ", "ใซในใฟใ "): | |
| cs = custom_times.get(date_str) | |
| if cs: | |
| custom_start, custom_end = cs | |
| else: | |
| custom_start = custom_end = None | |
| result.warnings.append( | |
| f"ใ{name}ใ{date_str}: ใใใฎไป/ใซในใฟใ ใ้ธๆใงใใๅ่ๆฌใซๆๅปใ่ฆใคใใใพใใ" | |
| ) | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id="custom", custom_start=custom_start, custom_end=custom_end, note=note, | |
| ) | |
| # ใฉใใซใง็ดๆฅไธ่ดใ่ฉฆใฟใ | |
| if value in _LABEL_TO_ID: | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id=_LABEL_TO_ID[value], custom_start=None, custom_end=None, note=note, | |
| ) | |
| # ใHH:MM~HH:MMใๅฝขๅผใฎๆๅป็ฏๅฒใ่ฉฆใฟใ๏ผไพ: 6:00~10:00๏ผ | |
| if re.match(r'^\d{1,2}:\d{2}', value): | |
| pid, cs, ce = _time_range_to_pattern(value) | |
| if pid is not None: | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id=pid, custom_start=cs, custom_end=ce, note=note, | |
| ) | |
| result.warnings.append( | |
| f"ใ{name}ใ{date_str}: ้ธๆๅคใ{value}ใใไธๆใงใ๏ผในใญใใ๏ผ" | |
| ) | |
| return None | |
| # Google Forms ่จญๅใใณใใฌใผใๆๅญๅ | |
| # โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ | |
| def build_form_guide(period: SchedulePeriod, employees=None) -> str: | |
| """ | |
| ๆๅฎๆ้็จใฎ Google Forms ่จญๅใใณใใฌใผใใไบบ้ใ่ชญใใๆๅญๅใง่ฟใใ | |
| Parameters | |
| ---------- | |
| period : ๅฏพ่ฑกใทใใๆ้ | |
| employees : ็ป้ฒๆธใฟในใฟใใไธ่ฆง๏ผNone ใฎๅ ดๅใฏใในใฟใใๅใ่ฟฝๅ ใใจ่กจ่จ๏ผ | |
| """ | |
| from utils.constants import DAY_OF_WEEK_LABELS | |
| all_dates = list(period.date_range()) | |
| n = len(all_dates) | |
| lines = [ | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| f" Google Forms ใใณใใฌใผใ ๏ผๅฏพ่ฑกๆ้: {period.start_date} ๏ฝ {period.end_date}๏ผ", | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| "", | |
| "ใใใฉใผใ ๆงๆใ", | |
| " Q1: ใๅๅ๏ผใใซใใฆใณ๏ผ", | |
| f" Q2: ๅธๆใทใใ๏ผ้ธๆๅผใฐใชใใใป{n}่กรใทใใๅ๏ผ", | |
| " Q3: ๅ่๏ผ่จ่ฟฐๅผใปไปปๆ๏ผ", | |
| "", | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| "ใQ1ใใๅๅ โ ใใซใใฆใณ", | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| " ไฝๆๆ้ : ใใใซใใฆใณใใ้ธๆ โ ้ธๆ่ขใซไปฅไธใ่ฟฝๅ ", | |
| "", | |
| ] | |
| if employees: | |
| for emp in employees: | |
| lines.append(f" {emp.name}") | |
| else: | |
| lines.append(" ๏ผ็ป้ฒๆธใฟในใฟใใๅใ1ๅใใค่ฟฝๅ ใใฆใใ ใใ๏ผ") | |
| lines += [ | |
| "", | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| "ใQ2ใๅธๆใทใใ โ ้ธๆๅผใฐใชใใ", | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| " ไฝๆๆ้ :", | |
| " 1. ใ้ธๆๅผใฐใชใใใใ้ธๆ", | |
| " 2. ่ณชๅๆ: ใๅธๆใทใใใ", | |
| " 3. ใๅ่กใซๅ็ญใๅฟ ้ ใซใใใใฏ ใชใ๏ผๆชๅ็ญ = ไผใฟๆฑใ๏ผ", | |
| "", | |
| " โผ ่กใฉใใซ๏ผๆฅไปใไธใใ้ ใซ่ฟฝๅ ๏ผ:", | |
| ] | |
| for i, d in enumerate(all_dates, start=1): | |
| dow = DAY_OF_WEEK_LABELS[d.weekday()] | |
| lines.append(f" {i} ๏ผ{d.month}/{d.day}ใป{dow}๏ผ") | |
| lines += [ | |
| "", | |
| " โผ ๅใฉใใซ๏ผใทใใๆ้ๅธฏใๅทฆใใ้ ใซ่ฟฝๅ ๏ผ:", | |
| " โป ๅบ่ใฎๅฎ้ใฎๅคๅๆ้ๅธฏใซๅใใใฆ่ช็ฑใซ่จญๅฎใใฆใใ ใใ", | |
| " โป ไปฅไธใฏใขใใชใซ็ป้ฒๆธใฟใฎๆจๆบใใฟใผใณไพ:", | |
| "", | |
| ] | |
| for p in ALL_PATTERNS: | |
| if p.id not in ("custom", "double") and p.start and p.end: | |
| lines.append(f" {p.start}~{p.end}") | |
| lines += [ | |
| " ไผใฟ", | |
| "", | |
| " โ ๏ธ ๅใฉใใซใฏใHH:MM~HH:MMใๅฝขๅผใงๅ ฅๅใใฆใใ ใใ", | |
| " ๏ผไพ: 6:00~10:00๏ผ", | |
| " ใขใใชใซ็ป้ฒใใใฆใใชใๆ้ๅธฏใฏใซในใฟใ ใจใใฆ่ชๅ็ป้ฒใใใพใ", | |
| "", | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| "ใQ3ใๅ่ โ ่จ่ฟฐๅผ๏ผ้ทๆ๏ผใปไปปๆ", | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| " ่ณชๅๆ: ใใใฎไปใใทใใใซ้ขใใ้ฃ็ตกไบ้ ใใใใฐ่จๅ ฅใใฆใใ ใใใ", | |
| "", | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| " ใCSV ๅใ่พผใฟๆ้ ใ", | |
| " 1. ใใฉใผใ ใฎๅ็ญใฟใ โใในใใฌใใใทใผใใซใชใณใฏใ", | |
| " 2. ในใใฌใใใทใผใใ้ใ", | |
| " 3. ใใกใคใซ โ ใใฆใณใญใผใ โ CSV๏ผ.csv๏ผ", | |
| " 4. ใใฎใขใใชใฎใๅธๆใทใใๅ ฅๅใ็ป้ขใง", | |
| " ใGoogle Forms CSV ใใคใณใใผใใใใ่ชญใฟ่พผใ", | |
| "โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ", | |
| ] | |
| return "\n".join(lines) | |
| def parse_google_form_responses( | |
| form_schema: dict, | |
| responses: list[dict], | |
| period: SchedulePeriod, | |
| employees: list[Employee], | |
| ) -> ImportResult: | |
| """ | |
| Google Forms API ใฎใฌในใใณใน (JSON) ใ่งฃๆใใฆ ImportResult ใ่ฟใใ | |
| """ | |
| result = ImportResult() | |
| name_to_emp: dict[str, Employee] = {e.name: e for e in employees} | |
| question_id_to_title = {} | |
| titles = [] | |
| name_col = None | |
| note_col = None | |
| items = form_schema.get("items", []) | |
| for item in items: | |
| title = item.get("title", "") | |
| q_item = item.get("questionItem") | |
| if q_item and "question" in q_item: | |
| q_id = q_item["question"]["questionId"] | |
| question_id_to_title[q_id] = title | |
| titles.append(title) | |
| if "ๅๅ" in title or "ๆฐๅ" in title: | |
| name_col = title | |
| elif "ๅ่" in title or "้ฃ็ตกไบ้ " in title: | |
| note_col = title | |
| if not name_col: | |
| result.warnings.append("ใๅๅใ้ธๆใใ่ณชๅใ่ฆใคใใใพใใใงใใใ") | |
| return result | |
| # ๆฅไปใใจใฎ่จญๅใ็นๅฎ | |
| date_cols: dict[str, dict[str, str]] = {} | |
| for title in titles: | |
| ds = _parse_date_from_header(title, period) | |
| if not ds: | |
| continue | |
| if ds not in date_cols: | |
| date_cols[ds] = {} | |
| if "ๅบๅคใงใใพใใ" in title: | |
| date_cols[ds]["avail"] = title | |
| elif "ๆ้ฃ" in title and "ใทใใ" in title: | |
| date_cols[ds]["b"] = title | |
| elif "ใใฃใใผ" in title and "ใทใใ" in title: | |
| date_cols[ds]["d"] = title | |
| elif "ๅบๅคๅธๆๆ้" in title or "ๅธๆๆ้" in title: | |
| date_cols[ds]["checkbox"] = title | |
| else: | |
| date_cols[ds]["direct"] = title | |
| for idx, resp in enumerate(responses, start=1): | |
| answers = resp.get("answers", {}) | |
| row = {} | |
| for q_id, ans in answers.items(): | |
| title = question_id_to_title.get(q_id) | |
| if not title: | |
| continue | |
| text_answers = ans.get("textAnswers", {}).get("answers", []) | |
| if text_answers: | |
| # ่คๆฐ้ธๆใฎใใงใใฏใใใฏในๅฏพๅฟ: ใซใณใๅบๅใใฎๆๅญๅใซใใฆ CSV ๅฝขๅผใซ็ตฑไธใใ | |
| val = ", ".join(ta.get("value", "").strip() for ta in text_answers if ta.get("value")) | |
| row[title] = val | |
| name = row.get(name_col, "").strip() | |
| if not name: | |
| result.warnings.append(f"ๅ็ญ{idx}: ๅๅใ็ฉบใงใ๏ผในใญใใ๏ผ") | |
| continue | |
| emp = name_to_emp.get(name) | |
| if emp is None: | |
| if name not in result.unmatched_names: | |
| result.unmatched_names.append(name) | |
| result.warnings.append( | |
| f"ใ{name}ใใฏในใฟใใ็ฎก็ใซ็ป้ฒใใใฆใใพใใ๏ผในใญใใ๏ผ" | |
| ) | |
| continue | |
| # ๅ่ใปใซในใฟใ ๆๅปใ่งฃๆ | |
| note = row.get(note_col, "").strip() if note_col else "" | |
| custom_times: dict[str, tuple[str, str]] = {} | |
| if note: | |
| custom_times.update(_parse_custom_times(note, period)) | |
| row_count = 0 | |
| for date_str, cols in date_cols.items(): | |
| req = None | |
| if "checkbox" in cols: | |
| req = _process_checkbox_fmt( | |
| row, date_str, cols["checkbox"], custom_times, note, emp, result, name | |
| ) | |
| elif "avail" in cols: | |
| req = _process_new_fmt( | |
| row, date_str, cols, custom_times, note, emp, result, name | |
| ) | |
| elif "direct" in cols: | |
| req = _process_direct_fmt( | |
| row, date_str, cols["direct"], custom_times, note, emp, result, name | |
| ) | |
| if req is not None: | |
| result.requests.append(req) | |
| row_count += 1 | |
| result.matched[name] = result.matched.get(name, 0) + row_count | |
| return result | |
| def _process_checkbox_fmt( | |
| row: dict, | |
| date_str: str, | |
| col_name: str, | |
| custom_times: dict, | |
| note: str, | |
| emp, | |
| result: "ImportResult", | |
| name: str, | |
| ) -> "Optional[ShiftRequest]": | |
| """ใใงใใฏใใใฏในๅฝขๅผใฎ่คๆฐ้ธๆใฎๅ็ญใใ ShiftRequest ใๆง็ฏใใใ""" | |
| val = row.get(col_name, "").strip() | |
| if not val or "ไผใฟ" in val: | |
| # ็ฉบใใพใใฏใไผใฟใใ้ธๆใใใฆใใๅ ดๅใฏไผใฟๆฑใ | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id=None, custom_start=None, custom_end=None, note=note, | |
| ) | |
| if "ๆ็ตฆ" in val: | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id="paid_leave", custom_start=None, custom_end=None, note=note, | |
| ) | |
| # ้ธๆ่ขใฎใซใณใๅบๅใใ้ ๅใซใใผใน | |
| selections = [s.strip() for s in val.split(",")] | |
| has_breakfast = False | |
| has_dinner = False | |
| selected_b_val = None | |
| selected_d_val = None | |
| has_custom = False | |
| for sel in selections: | |
| if "ใใฎไป" in sel: | |
| has_custom = True | |
| elif "ๆ้ฃ" in sel: | |
| has_breakfast = True | |
| # ไพ "ๆ้ฃ: 6:00ใ10:00" -> "6:00ใ10:00" ใๅๅพ | |
| parts = sel.split(":", 1) | |
| selected_b_val = parts[1].strip() if len(parts) > 1 else sel | |
| elif "ใใฃใใผ" in sel or "ๆฉ" in sel: | |
| has_dinner = True | |
| parts = sel.split(":", 1) | |
| selected_d_val = parts[1].strip() if len(parts) > 1 else sel | |
| if has_custom: | |
| cs = custom_times.get(date_str) | |
| custom_start, custom_end = cs if cs else (None, None) | |
| if not cs: | |
| result.warnings.append( | |
| f"ใ{name}ใ{date_str}: ใใใฎไปใ้ธๆใงใใๅ่ๆฌใซๆๅปใ่ฆใคใใใพใใ" | |
| ) | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id="custom", custom_start=custom_start, custom_end=custom_end, note=note, | |
| ) | |
| if has_breakfast and has_dinner: | |
| # ไธกๆนใใงใใฏใใใๅ ดๅใฏใใใซ | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id="double", custom_start=None, custom_end=None, note=note, | |
| ) | |
| elif has_breakfast and selected_b_val: | |
| return _resolve_time_value(selected_b_val, date_str, custom_times, note, emp, result, name) | |
| elif has_dinner and selected_d_val: | |
| return _resolve_time_value(selected_d_val, date_str, custom_times, note, emp, result, name) | |
| return ShiftRequest( | |
| employee_id=emp.id, date=date_str, | |
| pattern_id=None, custom_start=None, custom_end=None, note=note, | |
| ) | |