app / utils /forms_csv_parser.py
m1m02530125's picture
ใƒใ‚ฐไฟฎๆญฃ: #31 #32 #33 #35 #36
21f88cc
Raw History Blame Contribute Delete
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
@dataclass
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,
)