7 Commits

Author SHA1 Message Date
king 7046a504a1 fix: 코드 점검 — 시간대·외부 API·XSS·서버 블로킹 수정
- 네이버/카페24/발주 파일명/반품 입고일을 KST로 고정 (컨테이너 UTC 대응)
- 네이버 API timeout 추가, last-changed-statuses 'more' 페이지 이어서 조회, 주문번호 중복 제거
- 스마트스토어 발주확인을 엑셀 생성 이후로 이동
- 반품 신청/입고 목록 innerHTML 이스케이프 (XSS)
- 카페24 OAuth 콜백: 반사형 XSS 차단, /corm 경로로 복귀
- 블로킹 async 엔드포인트를 sync로 전환 (코드표·반품·송장 업로드·자사몰 행사·밀크런·CS 가져오기)
- DB 연결 closing 처리, 세트 목록 N+1 쿼리 제거
- 반품 일괄 입고 중복 송장 건너뛰기 및 안내
- 작업 결과 메모리 1시간 TTL, 401 로그인 next 경로 중복 수정
- 송장 '.0' 처리 수정 및 네이버/카페24 송장 파싱 공용화
- SortableJS 1.15.6 고정, 중복 코드·미사용 import 정리

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-22 20:30:26 +09:00
king ae50500655 Fit manual order grid columns and rows to cell content 2026-10-22 20:00:19 +09:00
king 827cb3b0a8 Add database-backed manual order grid with automatic cell saving 2026-10-22 18:33:59 +09:00
king 399e91518c Move CORM settings and histories to dedicated PostgreSQL database 2026-10-22 17:48:50 +09:00
king 92986eb1cc feat(auth): delegate CORM sessions and permissions to ERP 2026-10-22 16:42:12 +09:00
king dfe91196cb Merge branch 'feat/v9.1-merge' 2026-10-22 16:07:55 +09:00
king d2fdec5c43 feat: v9.1 업그레이드 — CS 기록 메뉴 + 반품 목록 검색 + 구글 연동 안정화
원본(CS 통합 프로그램 v9.1, 2026.09.03)을 서버판에 병합. 서버용 개조는 유지:
SSO(AuthGuard·SessionMiddleware), /corm 서브경로(root_path·APP_BASE_PATH),
X-Service-Token, /health/db, 환경변수 DB 설정, cafe24/naver 키 환경변수.

- CS 기록 메뉴(routers/cs_records.py, cs_records.js/css): 달력별 기록·완료·복사·
  다음 업무일 이월, 상시 메모(폴더·색), 일정, 엑셀 내려받기/업로드.
  표는 itemcode_db 에 자동 생성(CREATE TABLE IF NOT EXISTS). DB 설정은 원본의
  하드코딩 대신 CS_RECORD_DB_* → POSTGRES_*/ITEMCODE_DB 환경변수.
- New CS 누락·파손·불량·오배송 발주 → CS 기록 자동 저장.
- 반품 신청/입고 목록: 최근 3개월 기본, 전체 보기, 원송장번호 전체 검색.
- google_runtime.py: 구글 호출 시간 제한·전용 작업 슬롯, 설정 시트 스냅샷 캐시
  (예전 10분 인메모리 캐시와 /api/config/refresh 대체), 설정 저장 한 번에 원자적으로.
- 밀크런: 탭을 열 때만 구글 시트 조회.
- 자동발주 메뉴는 원본처럼 막지 않고 그대로 둠, 엑셀 내려받기 링크도 /corm 경로.
- requirements: holidays 추가. tests/test_google_resilience.py(서비스 토큰으로 SSO 통과).

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-22 16:07:53 +09:00
45 changed files with 9583 additions and 1067 deletions
+4
View File
@@ -37,3 +37,7 @@ deploy.ps1
start.bat
test.ps1
*.xlsx
# 구글 설정 마지막 정상값 스냅샷(google_runtime.SettingsCache)
.cache/
.settings-backups/
+32 -11
View File
@@ -13,6 +13,15 @@ ITEMCODE_DB=itemcode_db
ORDERLIST_DB=orderlist_db
RETURN_DB=return_db
# CORM 설정·내역 전용 DB (서버/계정은 기존 POSTGRES_* 사용, ITEMCODE_DB와 별개)
SETTINGS_DB=corm_db
# 설정 저장소: 이전·검증 완료 후 postgres로 변경
SETTINGS_BACKEND=google
# 문자내역/수동발주내역: history_migrate.py 이전·검증 후 postgres로 변경
HISTORY_BACKEND=google
# 수동발주 표: deploy-manual-orders.sh 이전·검증 후 postgres로 변경
MANUAL_ORDERS_BACKEND=google
# ============================================
# 네이버 커머스 API
# ============================================
@@ -36,24 +45,19 @@ APP_PORT=8002
APP_ROOT_PATH=/corm
# ============================================
# SSO (dbx-main 세션 공유)
# ERP 중앙 인증 (동일 도메인의 /corm/ 경로)
# ============================================
# dbx-main, OMS 와 반드시 같은 값으로 맞춰야 SSO 동작.
# 미설정 시 컨테이너 기동 실패 (fail-fast). openssl rand -hex 32 로 생성.
SESSION_SECRET_KEY=__paste-same-value-as-dbx-main__
# ERP 쿠키 이름과 동일하게 설정. CORM은 서명 키를 보유하지 않고 쿠키를 ERP에 전달만 한다.
SESSION_COOKIE_NAME=session
SESSION_SAME_SITE=lax
SESSION_HTTPS_ONLY=1
SESSION_MAX_AGE=28800
# 세션에서 읽을 사용자 키. dbx-main 이 어느 키로 쓰는지에 맞춰라.
SESSION_USER_KEY=user_email
SESSION_NAME_KEY=user_name
# 미인증 시 보낼 dbx-main 로그인/로그아웃 URL
MAIN_LOGIN_URL=https://dbx.no1king.freeddns.org/login
MAIN_LOGOUT_URL=https://dbx.no1king.freeddns.org/logout
# 사용자 접근은 매 요청 ERP의 현재 사용자/corm 권한으로 확인(실패 시 503 차단).
# dbx-main 과 공용 postgres_default 네트워크에서 통신. 외부 공개 주소보다 내부 주소 권장.
ERP_AUTH_URL=http://dbx-main:8000/api/auth/check
# ===== 서비스 간 호출 인증 =====
# OMS 가 반품 신청을 보낼 때 X-Service-Token 헤더로 이 값을 보낸다.
# CORM 의 AuthGuard 가 해당 헤더가 이 값과 일치하면 세션 검사 우회.
@@ -66,3 +70,20 @@ INTERNAL_SERVICE_TOKEN=__paste-same-value-as-dbx-orderlist__
# ============================================
POSTGRES_NETWORK=postgres_default
WEB_NETWORK=npm_default
# ============================================
# v9.1 선택 항목 (없으면 기본값)
# ============================================
# CS 기록 DB — 비우면 위 POSTGRES_* 와 ITEMCODE_DB 를 쓴다
# CS_RECORD_DB_HOST=
# CS_RECORD_DB_PORT=
# CS_RECORD_DB_NAME=
# CS_RECORD_DB_USER=
# CS_RECORD_DB_PASSWORD=
# CS 기록 다음 업무일 계산에 더할 휴일(YYYY-MM-DD, 쉼표 구분)
# CS_RECORD_EXTRA_HOLIDAYS=
# 구글 연동 시간 제한(초)·동시 작업 수, google 모드의 설정 캐시 유효 시간(초)
# GOOGLE_CONNECT_TIMEOUT=3
# GOOGLE_READ_TIMEOUT=10
# GOOGLE_WORKERS=4
# SETTINGS_CACHE_TTL=60
+1
View File
@@ -0,0 +1 @@
*.sh text eol=lf
+4
View File
@@ -44,3 +44,7 @@ deploy.bat
deploy.ps1
start.bat
test.ps1
# 구글 설정 마지막 정상값 스냅샷(google_runtime.SettingsCache)
.cache/
.settings-backups/
+43 -1
View File
@@ -3,6 +3,21 @@
CS / 발주 / 반품 통합 관리 웹 애플리케이션.
FastAPI + PostgreSQL 기반, Docker로 실행.
로그인·로그아웃·사용자·CORM 접근 권한은 ERP(main-app)가 관리한다.
`erp_auth.py`가 매 사용자 요청마다 ERP의 `GET /api/auth/check?module=corm`에 세션 쿠키를 전달해
현재 권한을 확인한다. ERP에서 권한을 끄거나 사용자를 삭제하면 다음 요청부터 차단된다.
ERP 연결 실패/타임아웃은 503으로 차단하며, CORM은 ERP 세션을 발급·갱신하지 않는다.
사용자 확인은 CORM의 세션 서명 키 없이 동작한다. `SESSION_COOKIE_NAME`은 ERP와 같아야 한다.
`ERP_AUTH_URL` 기본값은 `http://dbx-main:8000/api/auth/check`이다(공용 `postgres_default` 네트워크).
운영은 동일 도메인의 `/corm/` 프록시를 유지하고, **ERP 인증 API를 먼저 배포한 다음 CORM을 배포한다.**
새 CORM만 배포하면 기존 ERP의 404 응답으로 사용자 접근이 503이 된다.
실제 배포 전 양쪽 `git status --short`, `docker compose ps`, 공통 Docker 네트워크와 설정 백업 위치를 확인한다.
기존 `INTERNAL_SERVICE_TOKEN` 시스템 호출과 NPM의 외부 `X-Service-Token` 헤더 제거 설정은 유지한다.
검증: `python -m unittest discover -s tests -v`. 권한이 있는 직원으로 접속 후 ERP에서 `corm` 권한을 끄고,
같은 탭 새로고침 시 403인지 확인한다. 공개 헬스 체크 성공만으로 사용자 인증 성공을 판단하지 않는다.
---
## 기술 스택
@@ -16,18 +31,45 @@ FastAPI + PostgreSQL 기반, Docker로 실행.
## 사용 DB
구글 시트의 업무 설정을 같은 PostgreSQL 서버의 전용 `corm_db`로 이전하려면 [설정 DB 이전 절차](docs/settings-postgres.md)를 따른다.
`SETTINGS_BACKEND=postgres`로 전환한 뒤에는 설정 화면의 조회·저장이 DB를 사용한다. DB 접속 정보와 API 비밀키는 기존 `.env`/환경변수에 유지한다.
문자내역과 수동발주내역도 같은 `corm_db`로 옮기려면 [내역 DB 이전 절차](docs/history-postgres.md)를 따르고 `HISTORY_BACKEND=postgres`로 전환한다.
실제 수동발주 시트를 셀 편집·자동 저장 표로 바꾸려면 [수동발주 표 전환](docs/manual-orders-postgres.md)을 따른다.
운영자 실행: `cd /opt/dbx-corm && git pull --ff-only && bash deploy-manual-orders.sh`.
| DB 이름 | 용도 |
|---|---|
| `itemcode_db` | 품목코드 마스터 |
| `itemcode_db` | 품목코드 마스터 + CS 기록(v9.1: `cs_records`·`cs_memo_folders`·`cs_permanent_memos`·`cs_schedules`, 앱이 처음 쓸 때 `CREATE TABLE IF NOT EXISTS` 로 자동 생성) |
| `corm_db` | CORM 업무 설정·문자내역·수동발주내역·실제 수동발주 표 (`SETTINGS_DB`, 각각 별도 테이블 사용) |
| `orderlist_db` | 주문 정보 조회 |
| `return_db` | 반품 데이터 저장 |
모두 DB 계정은 `king` 통일.
## 버전
- 현재 **v9.1** (2026.09.03) — CS 기록 메뉴(달력·상시 메모·일정, 엑셀 내려받기/업로드), New CS 누락·파손·불량·오배송 발주 → CS 기록 자동 저장,
반품 신청/입고 목록 최근 3개월 + 원송장번호 전체 검색, 구글 연동 시간 제한·설정 스냅샷(`google_runtime.py`).
- 원본은 로컬 `CS 통합 프로그램` 폴더. 서버판은 원본 위에 SSO(AuthGuard)·`/corm` 서브경로·환경변수 DB 설정·`/health/db` 를 유지한다.
원본을 다시 가져올 때 이 부분과 `cafe24_api.py`·`naver_api.py`(원본은 키가 코드에 박혀 있음 — 서버판은 환경변수)는 덮어쓰지 않는다.
---
## 서버 배포 절차 (`/opt/dbx-corm`)
### 설정·문자내역·수동발주내역 PostgreSQL 전환 (운영자 실행)
다른 앱과 시트에서 편집을 잠시 중단한 뒤 서버에서 아래 한 줄을 실행한다. 이미지 빌드 후 CORM 앱을 잠시 정지하며,
원본·로컬 내역·환경과 이전 이미지 백업 → `corm_db` 준비 → 전체 데이터 이전·검증 → 환경 전환 → 읽기 API 확인 순서로 진행한다.
DB 생성 권한이 없는 계정은 관리자가 먼저 `corm_db`를 생성해야 한다.
실패하면 기존 이미지/환경으로 앱 재시작을 시도하고 DB와 백업을 보존한다. 기존 DB 데이터를 덮어쓰거나 삭제하지 않는다.
```bash
cd /opt/dbx-corm && git pull --ff-only && bash deploy-corm-postgres.sh
```
백업은 `.settings-backups/deploy-<시각>-<프로세스번호>/`에 보관된다. 직원 로그인과 실제 화면 동작은 완료 후 별도로 확인한다.
### 1. 코드 받기
```bash
+14 -8
View File
@@ -3,7 +3,7 @@ import json
import os
import time
from pathlib import Path
from datetime import datetime, timedelta
from datetime import datetime, timedelta, timezone
# 환경변수에서 카페24 인증 정보 로드
CLIENT_ID = os.getenv("CAFE24_CLIENT_ID", "")
@@ -12,6 +12,12 @@ MALL_ID = os.getenv("CAFE24_MALL_ID", "miraskitchen")
REDIRECT_URI = os.getenv("CAFE24_REDIRECT_URI", "")
TOKEN_FILE = os.getenv("CAFE24_TOKEN_FILE", str(Path(__file__).resolve().parent / "cafe24_tokens.json"))
REQUEST_TIMEOUT = 30
# 카페24는 시간대 표기 없는 한국 시간을 주고받는다. 컨테이너 TZ(기본 UTC)와 무관하게 KST로 비교한다.
KST = timezone(timedelta(hours=9))
def _now_kst():
return datetime.now(KST).replace(tzinfo=None)
def get_auth_url(redirect_uri=None, state=None):
redirect_uri = redirect_uri or REDIRECT_URI
@@ -77,7 +83,7 @@ def _save_tokens(token_data):
if 'expires_at' not in token_data:
# Fallback if expires_at is not provided (Cafe24 provides expires_at)
expires_in = token_data.get('expires_in', 7200) # usually 2 hours
expires_at = datetime.now() + timedelta(seconds=expires_in - 60) # 1 min buffer
expires_at = _now_kst() + timedelta(seconds=expires_in - 60) # 1 min buffer
token_data['expires_at'] = expires_at.isoformat()
with open(TOKEN_FILE, 'w', encoding='utf-8') as f:
@@ -104,7 +110,7 @@ def _is_expired(value, *, leeway_seconds=0):
expires_at = _parse_cafe24_datetime(value)
if expires_at is None:
return True
now = datetime.now(expires_at.tzinfo) if expires_at.tzinfo else datetime.now()
now = datetime.now(expires_at.tzinfo) if expires_at.tzinfo else _now_kst()
return now >= expires_at - timedelta(seconds=leeway_seconds)
def get_token_status():
@@ -163,8 +169,8 @@ def get_cafe24_orders_count(status="N20"):
"X-Cafe24-Api-Version": "2026-03-01"
}
end_date = datetime.now().strftime("%Y-%m-%d")
start_date = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")
end_date = _now_kst().strftime("%Y-%m-%d")
start_date = (_now_kst() - timedelta(days=7)).strftime("%Y-%m-%d")
url = f"https://{MALL_ID}.cafe24api.com/api/v2/admin/orders/count"
params = {
@@ -190,8 +196,8 @@ def get_cafe24_orders(status="N20", progress_callback=None):
}
# Calculate search date range (e.g. last 7 days)
end_date = datetime.now().strftime("%Y-%m-%d")
start_date = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")
end_date = _now_kst().strftime("%Y-%m-%d")
start_date = (_now_kst() - timedelta(days=7)).strftime("%Y-%m-%d")
url = f"https://{MALL_ID}.cafe24api.com/api/v2/admin/orders"
@@ -268,7 +274,7 @@ def update_cafe24_tracking(dispatch_list):
try:
err_data = response.json()
reason = err_data.get('error', {}).get('message', response.text)
except:
except ValueError:
reason = response.text
results["fail"].append({
+98
View File
@@ -0,0 +1,98 @@
#!/usr/bin/env bash
# Run by the server operator. No source-sheet writes or database overwrites.
set -Eeuo pipefail
umask 077
cd -- "$(dirname -- "${BASH_SOURCE[0]}")"
project_dir="$(pwd -P)"
test -f .env || { printf '%s\n' '.env 파일이 없습니다.' >&2; exit 1; }
git diff --quiet && git diff --cached --quiet || { printf '%s\n' '서버에 커밋되지 않은 코드 변경이 있어 중단합니다.' >&2; exit 1; }
docker compose version >/dev/null
docker compose ps
container="$(docker compose ps -q corm-app)"
test -n "$container" || { printf '%s\n' '기존 corm-app 컨테이너가 실행 중이어야 합니다.' >&2; exit 1; }
# Capture the current runtime, including configurable legacy file locations.
runtime="$(docker exec "$container" python -c 'import os; from pathlib import Path; print(os.getenv("SETTINGS_BACKEND", "google")); print(os.getenv("HISTORY_BACKEND", "google")); print(os.getenv("SETTINGS_DB", "corm_db")); print(int(bool(os.getenv("INTERNAL_SERVICE_TOKEN")))); print(os.getenv("SMS_HISTORY_FILE", "/app/sms_history.dat")); print(os.getenv("MANUAL_HISTORY_FILE", "/app/manual_history.dat"))')"
mapfile -t runtime_values <<< "$runtime"
settings_source="${runtime_values[0]}"
history_source="${runtime_values[1]}"
[[ "$settings_source" =~ ^(google|postgres)$ && "$history_source" =~ ^(google|postgres)$ ]] || exit 1
if [[ "$settings_source" == postgres || "$history_source" == postgres ]]; then
[[ "${runtime_values[2]}" == corm_db ]] || { printf '%s\n' '현재 DB 저장소가 corm_db가 아니므로 자동 이전을 중단합니다.' >&2; exit 1; }
fi
[[ "${runtime_values[3]}" == 1 ]] || { printf '%s\n' '기존 INTERNAL_SERVICE_TOKEN이 없어 전환 후 API를 검증할 수 없습니다.' >&2; exit 1; }
stamp="$(date -u +%Y%m%dT%H%M%SZ)-$$"
backup_dir="$project_dir/.settings-backups/deploy-$stamp"
mkdir -p -- "$backup_dir"
chmod 700 -- "$backup_dir"
cp -p -- .env "$backup_dir/env.before"
cp -p -- docker-compose.yml "$backup_dir/compose.before.yml"
old_image="$(docker inspect --format '{{.Image}}' "$container")"
rollback_image="dbx-corm:before-postgres-$stamp"
docker image tag "$old_image" "$rollback_image"
printf 'services:\n corm-app:\n image: %s\n' "$rollback_image" > "$backup_dir/rollback.yml"
printf '백업 위치: %s\n' "$backup_dir"
printf '%s\n' '이미지를 빌드합니다. 이후 이전이 끝날 때까지 다른 앱과 시트에서 설정·내역 편집을 중단해주세요.'
docker compose build corm-app
stopped=0
env_changed=0
rollback() {
result=$?
trap - ERR INT TERM
printf '전환 실패. 백업 보존 위치: %s\n' "$backup_dir" >&2
if [[ "$stopped" == 1 ]]; then
if [[ "$env_changed" == 1 ]]; then
cp -p -- "$backup_dir/env.before" "$project_dir/.env" || true
fi
# Reuse the exact former image; never restore/drop the database automatically.
if docker compose -f "$project_dir/docker-compose.yml" -f "$backup_dir/rollback.yml" up -d --no-deps --no-build corm-app; then
printf '%s\n' '이전 이미지와 환경으로 앱을 다시 시작했습니다. DB와 원본 백업은 보존했습니다.' >&2
else
printf '%s\n' '앱 자동 재시작을 확인하지 못했습니다. 위 백업의 env.before·rollback.yml을 확인해주세요.' >&2
fi
fi
[[ "$result" != 0 ]] || result=1
exit "$result"
}
trap rollback ERR INT TERM
# Capture existence before stopping; docker cp also works on stopped containers.
sms_exists="$(docker exec "$container" python -c 'import os; from pathlib import Path; print(int(Path(os.getenv("SMS_HISTORY_FILE", "/app/sms_history.dat")).is_file()))')"
manual_exists="$(docker exec "$container" python -c 'import os; from pathlib import Path; print(int(Path(os.getenv("MANUAL_HISTORY_FILE", "/app/manual_history.dat")).is_file()))')"
stopped=1
docker compose stop corm-app
if [[ "$sms_exists" == 1 ]]; then
docker cp "$container:${runtime_values[4]}" "$backup_dir/legacy-sms.dat"
fi
if [[ "$manual_exists" == 1 ]]; then
docker cp "$container:${runtime_values[5]}" "$backup_dir/legacy-manual.dat"
fi
printf '로컬 원본 파일: 문자=%s 수동발주=%s\n' "$sms_exists" "$manual_exists"
run_corm() {
docker compose run --rm --no-deps -T \
-e SETTINGS_DB=corm_db -e SETTINGS_BACKEND=google -e HISTORY_BACKEND=google \
-v "$backup_dir:/backups" -v "$project_dir:/deploy" corm-app python deploy_corm.py "$@"
}
run_corm snapshot --settings-source "$settings_source" --history-source "$history_source"
run_corm database
run_corm migrate
env_changed=1
run_corm environment
docker compose up -d --no-deps --no-build corm-app
ready=0
for attempt in {1..12}; do
if docker compose exec -T corm-app python deploy_corm.py probe; then
ready=1
break
fi
sleep 2
done
[[ "$ready" == 1 ]]
trap - ERR INT TERM
docker compose ps
printf 'CORM 설정·문자내역·수동발주내역 corm_db 전환 완료. 백업: %s\n' "$backup_dir"
printf '%s\n' '직원 계정으로 로그인해 화면에서 내역 불러오기와 설정을 확인해주세요.'
+59
View File
@@ -0,0 +1,59 @@
#!/usr/bin/env bash
set -Eeuo pipefail
umask 077
cd -- "$(dirname -- "${BASH_SOURCE[0]}")"
project_dir="$(pwd -P)"
test -f .env
git diff --quiet && git diff --cached --quiet || { printf '%s\n' '서버 코드 변경을 먼저 확인해주세요.' >&2; exit 1; }
docker compose ps
container="$(docker compose ps -q corm-app)"
test -n "$container"
runtime="$(docker exec "$container" python -c 'import os; assert os.getenv("SETTINGS_DB")=="corm_db" and os.getenv("HISTORY_BACKEND")=="postgres" and os.getenv("SETTINGS_BACKEND")=="postgres", "설정·내역 DB 전환부터 완료해주세요"; assert os.getenv("INTERNAL_SERVICE_TOKEN"), "기존 서비스 토큰이 필요합니다"; print(os.getenv("MANUAL_ORDERS_BACKEND","google"))')"
[[ "$runtime" =~ ^(google|postgres)$ ]]
stamp="$(date -u +%Y%m%dT%H%M%SZ)-$$"
backup_dir="$project_dir/.settings-backups/manual-$stamp"
mkdir -p -- "$backup_dir"
chmod 700 -- "$backup_dir"
cp -p -- .env "$backup_dir/env.before"
old_image="$(docker inspect --format '{{.Image}}' "$container")"
rollback_image="dbx-corm:before-manual-$stamp"
docker image tag "$old_image" "$rollback_image"
printf 'services:\n corm-app:\n image: %s\n' "$rollback_image" > "$backup_dir/rollback.yml"
printf '백업 위치: %s\n' "$backup_dir"
printf '%s\n' '빌드 후 앱을 잠시 정지합니다. 다른 앱과 구글 시트에서도 발주 편집을 중단해주세요.'
docker compose build corm-app
stopped=0
env_changed=0
rollback() {
result=$?
trap - ERR INT TERM
printf '전환 실패. DB·원본·백업은 보존했습니다: %s\n' "$backup_dir" >&2
if [[ "$stopped" == 1 ]]; then
if [[ "$env_changed" == 1 ]]; then cp -p -- "$backup_dir/env.before" "$project_dir/.env" || true; fi
docker compose -f "$project_dir/docker-compose.yml" -f "$backup_dir/rollback.yml" up -d --no-deps --no-build corm-app || true
fi
[[ "$result" != 0 ]] || result=1
exit "$result"
}
trap rollback ERR INT TERM
stopped=1
docker compose stop corm-app
run_corm() {
docker compose run --rm --no-deps -T -e MANUAL_ORDERS_BACKEND="$runtime" \
-v "$backup_dir:/backups" -v "$project_dir:/deploy" corm-app python manual_orders_deploy.py "$@"
}
run_corm snapshot
run_corm migrate
env_changed=1
run_corm environment
docker compose up -d --no-deps --no-build corm-app
ready=0
for attempt in {1..12}; do
if docker compose exec -T corm-app python manual_orders_deploy.py probe; then ready=1; break; fi
sleep 2
done
[[ "$ready" == 1 ]]
trap - ERR INT TERM
docker compose ps
printf '수동발주 DB 표 전환 완료. 백업: %s\n' "$backup_dir"
printf '%s\n' '브라우저를 새로고침하고 수동발주 탭의 셀 편집·자동 저장을 확인해주세요.'
+142
View File
@@ -0,0 +1,142 @@
"""Container-side steps for the operator-run CORM PostgreSQL deployment."""
import argparse
import json
import os
import sys
from contextlib import closing
from pathlib import Path
import psycopg2
from psycopg2 import sql
from dotenv import set_key
from history_migrate import load_backup, read_source
from history_store import PostgresHistoryStore, validate_bundle
from settings_migrate import load_snapshot, read_google_rows, save_snapshot
from settings_store import PostgresSettingsStore, SettingsNotInitialized, settings_db_config
def ensure_database():
config = settings_db_config()
if config["dbname"] != "corm_db":
raise ValueError("배포 대상은 corm_db여야 합니다.")
admin_config = {**config, "dbname": "postgres"}
with closing(psycopg2.connect(**admin_config, connect_timeout=3)) as connection:
connection.autocommit = True
with connection.cursor() as cursor:
cursor.execute("SELECT 1 FROM pg_database WHERE datname = %s", (config["dbname"],))
if cursor.fetchone() is None:
cursor.execute(sql.SQL("CREATE DATABASE {} OWNER {}")
.format(sql.Identifier(config["dbname"]), sql.Identifier(config["user"])))
print("corm_db 생성 완료.")
else:
print("기존 corm_db 사용. 기존 데이터는 덮어쓰지 않습니다.")
def snapshot(directory, settings_source="google", history_source="google"):
directory = Path(directory)
if settings_source == "postgres":
rows, _ = PostgresSettingsStore(settings_db_config()).read()
source = "postgres:corm_db:settings"
else:
rows, source = read_google_rows()
save_snapshot(directory / "settings-source.json", rows, source)
if history_source == "postgres":
bundle = PostgresHistoryStore(settings_db_config()).export_all()
source = "postgres:corm_db:histories"
else:
sms = directory / "legacy-sms.dat"
manual = directory / "legacy-manual.dat"
bundle, source = read_source(str(sms) if sms.exists() else None,
str(manual) if manual.exists() else None)
save_snapshot(directory / "history-source.json", bundle, source)
# Container-created backups should remain readable by the server operator.
owner = directory.stat()
for name in ("settings-source.json", "history-source.json"):
os.chown(directory / name, owner.st_uid, owner.st_gid)
print(f"원본 백업 완료: 설정 {len(rows)}행 / 문자 {len(bundle['sms'])}건 / 수동발주 {len(bundle['manual'])}건.")
def migrate(directory):
directory = Path(directory)
rows, source = load_snapshot(directory / "settings-source.json")
bundle, history_source = load_backup(directory / "history-source.json")
settings = PostgresSettingsStore(settings_db_config())
histories = PostgresHistoryStore(settings_db_config())
try:
settings.read()
except SettingsNotInitialized:
settings.import_rows(rows, source)
if settings.read()[0] != rows:
raise RuntimeError("기존 DB 설정과 원본이 다릅니다. 덮어쓰지 않고 중단합니다.")
try:
histories.check_ready()
except (SettingsNotInitialized, psycopg2.errors.UndefinedTable):
histories.import_all(bundle, history_source)
if validate_bundle(histories.export_all()) != validate_bundle(bundle):
raise RuntimeError("기존 DB 내역과 원본이 다릅니다. 덮어쓰지 않고 중단합니다.")
print("설정·문자내역·수동발주내역 전체 데이터 검증 완료.")
def switch_environment(path):
target = Path(path)
before = target.stat()
for key, value in (("SETTINGS_DB", "corm_db"), ("SETTINGS_BACKEND", "postgres"), ("HISTORY_BACKEND", "postgres")):
set_key(target, key, value, quote_mode="never")
os.chmod(target, 0o600)
os.chown(target, before.st_uid, before.st_gid)
print("설정·내역 저장소를 corm_db로 전환했습니다.")
def probe():
import requests
if os.getenv("SETTINGS_BACKEND") != "postgres" or os.getenv("HISTORY_BACKEND") != "postgres":
raise RuntimeError("앱 저장소 환경이 아직 postgres가 아닙니다.")
settings = PostgresSettingsStore(settings_db_config())
settings.read()
PostgresHistoryStore(settings_db_config()).check_ready()
token = os.getenv("INTERNAL_SERVICE_TOKEN", "")
if not token:
raise RuntimeError("API 검증용 기존 INTERNAL_SERVICE_TOKEN이 없습니다.")
headers = {"X-Service-Token": token}
for path in ("/api/config", "/api/sms/history", "/api/manual/history"):
response = requests.get("http://127.0.0.1:8002" + path, headers=headers, timeout=5)
response.raise_for_status()
data = response.json()
if path == "/api/config":
if response.headers.get("X-Settings-Backend") != "postgres" or not isinstance(data, dict):
raise RuntimeError("설정 API 저장소 검증 실패.")
elif not isinstance(data, list):
raise RuntimeError("내역 API 검증 실패.")
print("DB 및 설정·문자내역·수동발주내역 API 조회 정상.")
def main(argv=None):
parser = argparse.ArgumentParser(description="CORM 운영자 배포 스크립트 내부 단계")
parser.add_argument("step", choices=("snapshot", "database", "migrate", "environment", "probe"))
parser.add_argument("--directory", default="/backups")
parser.add_argument("--env-path", default="/deploy/.env")
parser.add_argument("--settings-source", choices=("google", "postgres"), default="google")
parser.add_argument("--history-source", choices=("google", "postgres"), default="google")
args = parser.parse_args(argv)
try:
if args.step == "snapshot":
snapshot(args.directory, args.settings_source, args.history_source)
elif args.step == "database":
ensure_database()
elif args.step == "migrate":
migrate(args.directory)
elif args.step == "environment":
switch_environment(args.env_path)
else:
probe()
return 0
except Exception as exc:
# Do not print credentials, customer records or request headers.
print(f"배포 단계 실패: {args.step} ({type(exc).__name__}). 원본/접속/DB 권한을 확인해주세요.", file=sys.stderr)
return 1
if __name__ == "__main__":
raise SystemExit(main())
+100
View File
@@ -0,0 +1,100 @@
# 문자내역·수동발주내역을 corm_db로 이전
설정은 `corm_settings`, 문자내역은 `corm_sms_history`, 수동발주내역은 `corm_manual_history`에 저장한다.
서버와 계정은 기존 `POSTGRES_*`, DB 이름은 `SETTINGS_DB=corm_db`를 공통 사용한다.
기존 `itemcode_db`의 코드표와 CS 기록은 그대로 유지한다.
## 동작
- `HISTORY_BACKEND=postgres`이면 두 내역의 조회·저장과 문자내역 삭제는 DB만 사용한다. 기존 `.dat`와 내역 시트에는 추가로 쓰지 않는다.
- 문자 내역의 고객 정보·상품 목록·할인/배송 옵션·문자 전문을 JSONB에 보존한다. 기록마다 고유 ID를 부여해 목록 순서가 바뀌어도 같은 내역을 선택·삭제한다.
- 수동발주내역은 기존처럼 한국 시간의 오늘 내역을 표시한다. DB에는 과거 내역도 모두 보관하며 날짜가 바뀌어도 삭제하지 않는다.
- 실제 발주 입력/엑셀 다운로드에 사용하는 별도 **수동 발주** 시트는 계속 사용한다.
- 기존 수동발주 삭제는 발주 시트의 주문 행도 지우는 기능이다. DB 모드에서도 해당 주문 행 삭제를 확인한 뒤 내역을 삭제한다. 구글 장애 시 DB 내역을 보존하고 오류를 표시한다.
- 같은 일시의 내역이 여러 건이면 주문을 잘못 취소하지 않도록 자동 삭제를 중단한다. 발주 시트에서 대상 주문을 먼저 확인해야 한다.
- 구글 발주 시트 삭제와 DB 삭제는 서로 다른 서비스라 하나의 트랜잭션으로 묶이지 않는다. 시트 삭제 후 DB 오류가 발생하면 시트의 주문 상태를 확인하고 재시도한다. 없는 시트 행은 재삭제하지 않는다.
- DB 장애를 저장 성공으로 표시하거나 예전 시트/파일로 자동 우회하지 않는다.
## 원본 준비
구글 시트에는 상품·문자 텍스트 위주로 저장되고, `.dat` 파일에 상세 상품 목록과 옵션이 더 남아 있을 수 있다.
따라서 **실제 운영 중인 컨테이너의 로컬 내역 파일도 함께 가져와야 한다.** 새 일회용 컨테이너에는 이 파일이 자동 복사되지 않는다.
아래는 Linux 서버 프로젝트 폴더에서 실행하는 명령이다. 먼저 [설정 DB 이전 문서](settings-postgres.md)에 따라
`corm_db`를 준비하고 새 코드를 빌드한다. `.env`에는 `SETTINGS_DB=corm_db`를 지정하고, 내역 모드는 아직 `HISTORY_BACKEND=google`로 유지한다.
1. 직원 및 다른 구버전 앱의 문자 생성·발주·내역 저장·삭제와 시트 직접 편집을 잠시 중단하고, 앱을 정지한다.
```bash
mkdir -p .settings-backups
docker compose stop corm-app
```
2. 기존 컨테이너에서 원본 파일을 호스트로 복사한다. 아래는 기본 경로 예시다.
`SMS_HISTORY_FILE`·`MANUAL_HISTORY_FILE`을 따로 지정했다면 해당 경로를 사용한다.
정지된 컨테이너에서도 `docker cp`가 가능하다. 파일이 없다는 오류가 나면 실제 운영 경로부터 확인한다.
```bash
docker cp dbx-corm:/app/sms_history.dat .settings-backups/legacy-sms.dat
docker cp dbx-corm:/app/manual_history.dat .settings-backups/legacy-manual.dat
```
원래 해당 로컬 파일이 존재하지 않았다면 그 파일의 복사와 다음 명령의 해당 `--*-local-file` 인자를 생략한다.
파일이 없으면 시트에 남은 정보만 이전할 수 있다. 브라우저에만 남아 있는 고객 캐시는 자동 이전하지 않는다.
3. 구글의 **문자내역·수동발주내역 전체**와 로컬 파일을 읽어 원본 백업을 생성한다. DB에는 접속하지 않는다.
```bash
docker compose run --rm --no-deps -v "$PWD/.settings-backups:/backups" corm-app python history_migrate.py snapshot --sms-local-file /backups/legacy-sms.dat --manual-local-file /backups/legacy-manual.dat --output /backups/history-source.json
```
출력된 두 내역 건수와 로컬 파일 유무를 확인한다. 개인 정보나 문자 내용은 콘솔에 출력하지 않는다.
같은 일시의 시트·로컬 기록은 다른 필드도 비교해 한 건씩 병합하고, 매칭되지 않은 기록과 로컬에만 저장된 기록도 보존한다.
같은 일시라는 이유만으로 여러 기록을 한 건으로 합치지 않는다. 형식이 깨진 로컬 JSON은 조용히 누락시키지 않고 이전을 중단한다.
백업에는 원본 전체 행·셀과 로컬 기록도 포함된다. 백업 경로는 덮어쓰지 않으므로 재실행 시 새 파일명을 사용한다.
4. 백업에서 DB로 이전하고 원본과 전체 내역을 검증한다.
```bash
docker compose run --rm --no-deps -v "$PWD/.settings-backups:/backups" corm-app python history_migrate.py import --input-file /backups/history-source.json --apply --backup /backups/before-history-db.json
docker compose run --rm --no-deps -v "$PWD/.settings-backups:/backups" corm-app python history_migrate.py verify --input-file /backups/history-source.json
```
두 테이블의 생성·저장·전체 검증은 하나의 DB 트랜잭션으로 처리한다. 이미 내역이 있으면 덮어쓰거나 중복으로 추가하지 않는다.
빈 원본도 이전 완료 상태로 기록한다. 이전 완료 후 내역을 지웠더라도 재가져오기로 삭제한 내역이 되살아나지 않는다.
5. `.env`를 변경하고 앱을 기동한다. 설정도 함께 전환했다면 다음 세 값을 사용한다.
```dotenv
SETTINGS_DB=corm_db
SETTINGS_BACKEND=postgres
HISTORY_BACKEND=postgres
```
```bash
docker compose up -d --no-deps corm-app
docker compose logs --tail=100 corm-app
```
두 내역 테이블과 이전 완료 표시가 없으면 기동이 실패한다. 기존 브라우저 탭은 새로고침해 새 ID 기반 동작을 사용한다.
직원 계정으로 과거 문자 불러오기, 고객 정보·문자 옵션 복원, 새 문자내역 저장 및 오늘의 수동발주 내역 표시를 확인한다.
실제 주문 취소를 수반하는 수동발주 삭제 검증에는 테스트 주문을 사용한다.
운영 DB에는 자동 테스트를 실행하지 않는다.
설정을 이미 DB로 이전했다면 설정 이전을 반복할 필요는 없다. 이번에는 위 내역 이전과 `HISTORY_BACKEND` 전환만 진행한다.
## 백업과 복귀
정기 백업은 `corm_db` 전체를 포함한다. 내역을 JSON으로 별도 보관하려면 다음을 실행한다.
```bash
docker compose run --rm --no-deps -v "$PWD/.settings-backups:/backups" corm-app python history_migrate.py export --output /backups/current-histories.json
```
항상 새 파일명을 사용하고 고객 정보가 포함된 백업의 접근 권한을 제한한다. `.settings-backups/`는 Git과 이미지 빌드에서 제외된다.
이 JSON도 `import --input-file`로 **아직 내역이 없는 DB**에 복구할 수 있다. 기존 DB 덮어쓰기는 지원하지 않는다.
전환 직후 복귀가 필요하면 `HISTORY_BACKEND=google`로 바꾸고 컨테이너를 재생성한다.
DB 전환 후의 추가·삭제는 기존 시트/파일에 복제되지 않으므로 복귀 전 DB 내역을 내보내 보관한다.
이전 때 복사한 `.dat`는 백업이며, Google 모드에서 로컬 상세 복원을 계속 사용하려면 원래 경로에 마운트하거나 복원해야 한다.
DB 전환 후 변경이 있었다면 양쪽 자료의 차이를 검토해 복구해야 한다.
+62
View File
@@ -0,0 +1,62 @@
# 수동발주 표를 DB로 전환
설정·문자내역·수동발주내역을 `corm_db`로 옮긴 설치에서 사용한다.
같은 DB의 별도 `corm_manual_orders` 테이블에 실제 발주 행을 저장한다.
`itemcode_db`와 CS 기록은 변경하지 않는다.
## 화면과 저장
- 기존 Google 수동 발주 시트의 15개 열 이름·순서와 값을 유지한다. 원본에 추가 열이 있으면 함께 보존한다.
- 프로그램의 수동발주 탭에 편집 가능한 표를 표시한다. 셀을 클릭해 입력하면 약 0.45초 뒤, 또는 셀을 벗어날 때 자동 저장한다. 별도 저장 버튼은 없다.
- Enter/Shift+Enter는 아래/위 셀, Tab/Shift+Tab은 다음/이전 셀로 이동한다. Alt+Enter는 셀 안 줄바꿈이다.
- TSV 형식의 여러 셀 붙여넣기를 지원한다. 필요한 행을 먼저 추가하고 한 번에 1,000셀 이내로 붙여넣는다.
- 입력 중·저장 완료·실패 상태를 표시한다. 실패한 입력은 표에 유지하며 재시도할 수 있다. 미저장 입력이 있는 채로 창을 닫으면 브라우저가 확인한다.
- 같은 셀을 다른 사용자가 먼저 바꿨으면 자동으로 덮어쓰지 않는다. 재시도 시 최신값과 내 입력을 비교해 선택한다.
- 다른 셀에 대한 동시 수정은 DB 행 잠금 안에서 반영한다. 이전 저장 응답을 받지 못한 같은 값의 재시도는 허용한다.
- 다른 사용자의 변경은 편집하지 않는 동안 10초 간격으로 갱신한다. 화면의 새로고침으로 즉시 갱신할 수도 있다.
- 행 추가와 선택 행 삭제를 지원한다. 행 삭제는 DB에 보관 처리하며, Google 시트에는 쓰지 않는다.
- 발주 입력과 수동발주 내역 요약은 같은 DB 트랜잭션으로 저장한다. 브라우저에서 요약을 중복 생성하지 않는다.
- 셀 편집은 값 입력용이다. 수식 계산·Google Sheets의 서식/스크립트는 이 표의 기능에 포함하지 않는다.
## 엑셀 다운로드
기존처럼 발주 자료를 엑셀로 내려받고 현재 표를 비운다. 미저장 셀이 있으면 먼저 저장을 완료한다.
파일 생성·파일 보관·해당 행 보관 처리는 한 트랜잭션에서 수행한다. 파일 생성이 실패하면 표를 비우지 않는다.
내려받는 중 다른 사용자가 추가한 새 행은 함께 지우지 않는다.
표의 **다운로드 기록**에서 최근 10개 파일을 다시 받을 수 있다. 최근 10개만 화면에 표시하며 더 오래된 파일도 DB에서 자동 삭제하지 않는다.
주소·전화·상품코드·우편번호 등은 텍스트로 보존하고, 수량/금액의 일반 정수 값은 숫자로 내보낸다.
`=`로 시작하는 입력은 Excel 수식으로 실행하지 않고 텍스트로 저장한다.
## 서버 실행 명령
다른 앱과 Google 시트에서의 발주 편집을 잠시 중단한 뒤 운영 서버에서 실행한다.
별도 SSH 원격 실행 없이 운영자가 실행하는 명령이다.
```bash
cd /opt/dbx-corm && git pull --ff-only && bash deploy-manual-orders.sh
```
스크립트는 현재 설정·내역 DB 전환 상태 확인 → 이전 환경/이미지 보관 → 새 이미지 빌드 → CORM 정지 →
발주 원본 백업 → 테이블 생성·행/열 전체 이전·읽기 검증 → `.env`의 `MANUAL_ORDERS_BACKEND=postgres` 전환 →
기동·조회 API 점검 순서로 진행한다. 별도 DB 생성은 필요 없다.
백업은 `.settings-backups/manual-<시각>-<프로세스번호>/`에 보관한다.
Google 원본은 수정하거나 지우지 않는다. 이미 DB로 전환된 상태에서 다시 실행하면 현재 DB를 원본으로 백업하며,
기존 DB가 원본과 다르면 덮어쓰지 않고 중단한다.
실패하면 이전 환경/이미지로 앱 재시작을 시도하고, DB와 원본 백업을 보존한다.
전환 후 브라우저를 새로고침한다. 기존 구글 시트 iframe 대신 표가 표시되는지 확인하고,
테스트용 셀 수정 후 저장 완료 표시와 새로고침 후 값 유지를 확인한다.
실제 주문 삭제와 다운로드 후 비우기 검증에는 테스트 발주를 사용한다.
## 백업과 복구
`corm_db` 전체를 정기 백업한다. 이번에 추가되는 테이블은 다음과 같다.
- `corm_manual_sheet`: 열 이름과 최초 이전 정보
- `corm_manual_orders`: 발주 셀·수정 버전·주문 연결 정보·보관 시각
- `corm_manual_exports`: 다시 받을 수 있는 Excel 파일과 해당 행 ID
DB 전환 뒤의 추가·수정·삭제는 기존 Google 시트로 복제되지 않는다.
`MANUAL_ORDERS_BACKEND=google`로 단순 복귀하면 이전 시트 값이 표시되므로,
DB 전환 후 변경이 있다면 현재 발주와 백업을 보관하고 차이를 확인한 뒤 복구한다.
+131
View File
@@ -0,0 +1,131 @@
# 구글 시트 설정을 PostgreSQL로 이전
## 이전 범위와 저장 구조
- 대상: `CS 통합 프로그램 설정` 스프레드시트의 **설정** 탭 전체.
- 상품 그룹·상품 코드/이름/가격·정렬·구분선·소그룹 이름·사은품 규칙·문자 문구·배송비·폰트·송장 접두어를 보존한다.
- 같은 PostgreSQL 서버의 별도 DB `corm_db` 안에 `corm_settings` 테이블로 저장한다. DB 이름은 `SETTINGS_DB`로 지정하며 기본값은 `corm_db`이다. 기존 `ITEMCODE_DB`와 코드표/CS 기록 테이블은 수정하지 않는다.
- 설정 전체를 JSONB 배열로 저장하므로 행과 셀 순서를 보존한다. DB 버전을 함께 비교해 동시 저장의 조용한 덮어쓰기를 차단한다.
- DB 모드는 요청마다 작은 설정 문서 1건을 읽는다. 프로세스별 캐시 차이 없이 최신 커밋 값을 사용하며, 구글 요청/구글 작업 풀/구글 설정 캐시는 사용하지 않는다.
- DB 장애 시에는 503을 반환한다. 구글의 오래된 설정으로 자동 전환하지 않는다.
- DB 접속 정보와 API 비밀키는 계속 `.env`/환경변수에 둔다. `.env`를 DB로 복사하는 처리는 없다.
- 이 도구는 `설정` 탭을 이전한다. `문자내역`, `수동발주내역`은 [내역 DB 이전 절차](history-postgres.md)에 따라 같은 `corm_db`로 별도 이전한다. `아이템코드` 탭은 사용하지 않는다.
- 실제 수동발주 시트와 밀크런 등 다른 구글 연동에는 계속 구글 인증이 필요하다.
구글 시트의 탭과 DB 테이블을 반드시 1:1로 맞출 필요는 없다. 현재 업무 설정은 **설정** 탭 안에서
`APP_SETTINGS`, `GROUP_NAMES`, `ORDER_*`, `SMS_GIFT_RULES` 등의 분류로 구분되어 있다.
이 분류와 상품 순서를 그대로 저장하므로 하나의 `corm_settings` 테이블을 사용해도 설정이 서로 섞이지 않는다.
`문자내역`과 `수동발주내역`은 설정이 아닌 이력 데이터이므로 같은 DB 안의 `corm_sms_history`, `corm_manual_history` 테이블로 구분한다.
## 운영 서버 전환 순서
아래 명령은 Linux 운영 서버의 프로젝트 폴더(`/opt/dbx-corm`)에서 실행한다.
이 문서의 명령을 실행하기 전까지 현재 동작은 바뀌지 않는다. 기본값은 `SETTINGS_BACKEND=google`이다.
기존 `.env`를 예제 파일로 덮어쓰지 말고 필요한 항목만 추가한다.
먼저 PostgreSQL 관리 계정으로 **같은 서버에** `corm_db`를 생성한다. 기존 DB는 이동하거나 변경하지 않는다.
아래는 앱 DB 계정이 `king`일 때의 SQL 예시다. 실제 `POSTGRES_USER`가 다르면 소유자를 맞춘다.
이미 DB가 있다면 생성 명령을 다시 실행하지 말고 소유자와 접근 권한을 확인한다.
```sql
CREATE DATABASE corm_db OWNER king;
```
앱 계정에 DB 생성 권한을 추가할 필요는 없다. 관리자가 DB를 생성하고 앱 계정에는 해당 DB 접근 및 테이블 생성/읽기/쓰기 권한을 부여한다.
앱과 이전 도구는 DB를 자동 생성하지 않는다.
이전 명령을 실행하기 **전에** 서버 `.env`에 아래 값을 추가한다. 이 단계에서는 저장소 모드는 `google`로 유지한다.
```dotenv
ITEMCODE_DB=itemcode_db
SETTINGS_DB=corm_db
SETTINGS_BACKEND=google
```
1. 코드를 서버에 반영하고 새 이미지를 빌드한다. 기존 앱은 아직 구글 모드로 실행된다.
```bash
docker compose build corm-app
```
2. 원본 읽기 및 형식 점검을 실행한다. 이 명령은 DB에 접속하거나 데이터를 쓰지 않는다.
```bash
docker compose run --rm --no-deps corm-app python settings_migrate.py import
```
3. 직원에게 설정 편집을 잠시 멈추도록 알리고, 직접 시트 편집과 다른 구버전 앱의 설정 저장도 중단한다.
이전 완료 후 DB 모드로 기동할 때까지 이 상태를 유지한다.
현재 앱을 정지하고 원본 JSON을 서버 호스트에 백업한 뒤 DB에 저장한다.
```bash
mkdir -p .settings-backups
docker compose stop corm-app
docker compose run --rm --no-deps -v "$PWD/.settings-backups:/backups" corm-app python settings_migrate.py import --apply --backup /backups/before-postgres.json
docker compose run --rm --no-deps corm-app python settings_migrate.py verify
```
`verify`가 **원본과 DB 전체 행·셀·순서 일치**를 출력해야 한다.
`--apply`는 백업을 먼저 생성하고, 테이블 생성·최초 저장·전체 데이터 검증을 하나의 DB 트랜잭션으로 처리한다.
이미 DB 설정이 있거나 백업 경로가 존재하면 덮어쓰지 않고 중단한다.
실패하면 원인을 확인한다. DB가 비어 있는 경우에는 새 백업 경로로 다시 실행할 수 있다.
4. 이전·검증 완료 후 운영 서버 `.env`의 저장소 모드를 변경한다. DB 계정은 `corm_db`에 테이블을 생성하고 읽고 쓸 수 있어야 한다.
```dotenv
SETTINGS_DB=corm_db
SETTINGS_BACKEND=postgres
```
기존 `POSTGRES_HOST`, `POSTGRES_PORT`, `POSTGRES_USER`, `POSTGRES_PASSWORD`, `ITEMCODE_DB`와
`NAVER_*`, `CAFE24_*`, 구글 인증 설정은 그대로 유지한다.
5. 앱을 기동하고 상태 및 실제 사용자 화면을 확인한다.
```bash
docker compose up -d --no-deps corm-app
docker compose ps
docker compose logs --tail=100 corm-app
```
초기화되지 않은 DB에서는 시작이 실패한다. 먼저 이전이 완료되었는지 확인한다.
권한이 있는 직원으로 로그인해 상품 순서·가격·배송비·문자 문구를 확인하고,
설정 저장 후 새로고침으로 반영 여부를 확인한다. 저장 충돌이나 DB 장애 안내가 보이면 저장을 재시도하기 전에 새로고침한다.
브라우저 개발자 도구의 `/api/config` 응답 헤더 `X-Settings-Backend: postgres`로 저장소를 확인할 수 있다.
`/health/db` 확인만으로 사용자 설정 조회·저장 검증을 대신하지 않는다.
이후 설정은 프로그램 화면에서 수정한다. 구글 시트 설정 탭의 변경은 DB에 자동 반영되지 않는다.
## 백업과 복구
정기 PostgreSQL 백업에 **`corm_db`**와 그 안의 `corm_settings` 테이블을 포함한다.
기존 `itemcode_db`만 백업하면 CORM 설정은 보관되지 않는다. 최신 설정을 JSON으로 내보낼 수도 있다.
```bash
docker compose run --rm --no-deps -v "$PWD/.settings-backups:/backups" corm-app python settings_migrate.py export --output /backups/current-settings.json
```
이 명령도 기존 파일은 덮어쓰지 않는다. 매번 새 파일명을 사용한다.
백업에는 업무 설정과 문자 문구가 있으므로 접근을 제한한다. `.settings-backups/`는 Git과 Docker 빌드에서 제외되어 있다.
파일을 호스트에 보관하려면 위와 같이 반드시 백업 디렉터리를 마운트한다.
빈 DB에 JSON 백업을 복구할 때는 다음 명령을 사용한다. 기존 DB 설정은 덮어쓰지 않는다.
```bash
docker compose run --rm --no-deps -v "$PWD/.settings-backups:/backups" corm-app python settings_migrate.py import --input-file /backups/current-settings.json --apply --backup /backups/before-restore.json
docker compose run --rm --no-deps -v "$PWD/.settings-backups:/backups" corm-app python settings_migrate.py verify --input-file /backups/current-settings.json
```
전환 직후 문제가 생기면 `.env`를 `SETTINGS_BACKEND=google`로 되돌리고 컨테이너를 재생성할 수 있다.
구글 시트 원본은 그대로 남아 있다. **DB 전환 후 새로 저장한 내용은 구글에 복제되지 않으므로**,
변경이 있었다면 우선 위 `export`로 최신 DB 설정을 보관한다. 단순 모드 복귀 시에는 구글에 남은 이전 값이 표시된다.
구글 모드에서 다시 수정한 내용을 기존 DB로 재이전하려면 별도로 차이를 검토해야 한다. 자동 덮어쓰기는 지원하지 않는다.
## 로컬 검증
```bash
python -m unittest discover -s tests -v
```
외부 연결 없이 설정 API·순서 보존·구글 작업 풀 분리·오류·충돌·백업/이전 절차를 검증한다.
실제 PostgreSQL 검증은 운영 DB가 아닌 폐기 가능한 테스트 DB의 DSN을 `SETTINGS_TEST_DSN`에 지정한 뒤 같은 명령을 실행한다.
통합 테스트는 매번 별도의 임시 스키마를 만들고 그 스키마만 제거한다. 테스트 계정에는 스키마 생성 권한이 필요하다.
+102
View File
@@ -0,0 +1,102 @@
"""CORM 사용자 접근의 최종 판단을 ERP에 위임한다."""
import os
import secrets
from urllib.parse import quote
import httpx
from fastapi import Request
from fastapi.responses import HTMLResponse, JSONResponse, RedirectResponse
from starlette.middleware.base import BaseHTTPMiddleware
class AuthGuardMiddleware(BaseHTTPMiddleware):
def __init__(self, app):
super().__init__(app)
self.root = os.getenv("APP_ROOT_PATH", "").rstrip("/")
self.login_url = os.getenv(
"MAIN_LOGIN_URL", "https://dbx.no1king.freeddns.org/login"
)
self.auth_url = os.getenv(
"ERP_AUTH_URL", "http://dbx-main:8000/api/auth/check"
).strip()
self.cookie_name = os.getenv("SESSION_COOKIE_NAME", "session")
async def dispatch(self, request: Request, call_next):
raw = request.scope.get("path", "") or "/"
root = self.root or request.scope.get("root_path", "").rstrip("/")
path = raw
if root and (raw == root or raw.startswith(root + "/")):
path = raw[len(root):] or "/"
# 공개 경로는 정확히 일치할 때만 허용한다(정적 자원만 하위 경로 포함).
if path.startswith("/static/") or path in {
"/health", "/health/db", "/api/cafe24/callback",
"/admin/oauth/callback", "/login", "/logout", "/favicon.ico",
}:
return await call_next(request)
# 기존 OMS 등의 시스템 호출 인증. 사용자 로그인/권한과 별개다.
service_token = os.getenv("INTERNAL_SERVICE_TOKEN", "")
incoming = request.headers.get("x-service-token", "")
if service_token and incoming and secrets.compare_digest(incoming, service_token):
return await call_next(request)
wants_html = "text/html" in request.headers.get("accept", "") or not path.startswith("/api/")
def denied(status, message):
headers = {"Cache-Control": "no-store"}
if wants_html:
return HTMLResponse(
f'<!doctype html><html lang="ko"><meta charset="utf-8">'
f"<title>CORM 접근 안내</title><p>{message}</p></html>",
status_code=status, headers=headers,
)
return JSONResponse({"detail": message}, status_code=status, headers=headers)
def unauthenticated():
if not wants_html:
return denied(401, "로그인이 필요합니다.")
next_path = f"{root}{path}"
query = request.scope.get("query_string", b"")
if query:
next_path += "?" + query.decode("latin-1")
sep = "&" if "?" in self.login_url else "?"
return RedirectResponse(
f"{self.login_url}{sep}next={quote(next_path, safe='/')}",
status_code=302, headers={"Cache-Control": "no-store"},
)
cookie = request.cookies.get(self.cookie_name)
if not cookie:
return unauthenticated()
if not self.auth_url:
return denied(503, "ERP 인증 서버 설정을 확인해 주세요.")
try:
# 사용자별 권한 캐시 없음. 쿠키·토큰·ERP 응답 본문은 로그에 남기지 않는다.
# 다른 쿠키/서비스 토큰은 전달하지 않으며 리디렉션도 따라가지 않는다.
async with httpx.AsyncClient(
timeout=3.0, follow_redirects=False, trust_env=False,
cookies={self.cookie_name: cookie},
) as client:
response = await client.get(
self.auth_url, params={"module": "corm"},
)
if response.status_code == 401:
return unauthenticated()
if response.status_code == 403:
return denied(403, "CORM 접근 권한이 없습니다. ERP 관리자에게 문의해 주세요.")
if response.status_code != 200:
return denied(503, "ERP에서 접근 권한을 확인할 수 없습니다. 잠시 후 다시 시도해 주세요.")
data = response.json()
user = data.get("user") if isinstance(data, dict) else None
if (
data.get("module") != "corm" or not isinstance(user, dict)
or not isinstance(user.get("email"), str) or not user["email"].strip()
):
raise ValueError("Invalid ERP authorization response")
except (httpx.HTTPError, ValueError, TypeError, AttributeError):
return denied(503, "ERP에서 접근 권한을 확인할 수 없습니다. 잠시 후 다시 시도해 주세요.")
request.state.erp_user = user
return await call_next(request)
+243
View File
@@ -0,0 +1,243 @@
"""Bounded Google I/O and last-known-good settings, independent of the ASGI loop."""
import asyncio
import copy
import functools
import json
import logging
import os
import tempfile
import threading
import time
from concurrent.futures import ThreadPoolExecutor
from pathlib import Path
import requests
from fastapi import HTTPException
from google.auth.transport.requests import AuthorizedSession, Request
logger = logging.getLogger(__name__)
GOOGLE_CONNECT_TIMEOUT = float(os.getenv("GOOGLE_CONNECT_TIMEOUT", "3"))
GOOGLE_READ_TIMEOUT = float(os.getenv("GOOGLE_READ_TIMEOUT", "10"))
GOOGLE_TIMEOUT = (GOOGLE_CONNECT_TIMEOUT, GOOGLE_READ_TIMEOUT)
GOOGLE_WORKERS = max(1, int(os.getenv("GOOGLE_WORKERS", "4")))
_executor = ThreadPoolExecutor(max_workers=GOOGLE_WORKERS, thread_name_prefix="google-io")
_slots = threading.BoundedSemaphore(GOOGLE_WORKERS)
_settings_write_lock = threading.RLock()
_history_lock = threading.RLock()
def _bounded_timeout(timeout):
if timeout is None:
return GOOGLE_TIMEOUT
if isinstance(timeout, tuple):
connect, read = timeout
else:
connect = read = timeout
return (
min(connect, GOOGLE_CONNECT_TIMEOUT) if connect is not None else GOOGLE_CONNECT_TIMEOUT,
min(read, GOOGLE_READ_TIMEOUT) if read is not None else GOOGLE_READ_TIMEOUT,
)
class GoogleTimeoutSession(requests.Session):
"""Also bound token refresh calls, which otherwise default to 120 seconds."""
def request(self, method, url, **kwargs):
kwargs["timeout"] = _bounded_timeout(kwargs.get("timeout"))
return super().request(method, url, **kwargs)
class GoogleAuthorizedSession(AuthorizedSession):
def request(self, method, url, **kwargs):
kwargs["timeout"] = _bounded_timeout(kwargs.get("timeout"))
return super().request(method, url, **kwargs)
def create_google_session(credentials):
return GoogleAuthorizedSession(
credentials,
auth_request=Request(session=GoogleTimeoutSession()),
refresh_timeout=GOOGLE_CONNECT_TIMEOUT + GOOGLE_READ_TIMEOUT,
)
def create_google_client(credentials):
import gspread
client = gspread.Client(auth=credentials, session=create_google_session(credentials))
client.set_timeout(GOOGLE_TIMEOUT)
return client
async def run_google_io(function, *args, **kwargs):
# No unbounded executor queue, and no consumption of FastAPI's DB worker pool.
if not _slots.acquire(blocking=False):
raise HTTPException(
status_code=503,
detail="구글 연동 작업이 진행 중입니다. 잠시 후 다시 시도해주세요.",
headers={"Retry-After": "3"},
)
try:
future = _executor.submit(functools.partial(function, *args, **kwargs))
except BaseException:
_slots.release()
raise
future.add_done_callback(lambda _: _slots.release())
# Disconnecting the browser must not free a slot while its write still runs.
return await asyncio.shield(asyncio.wrap_future(future))
def google_worker(function):
@functools.wraps(function)
async def wrapped(*args, **kwargs):
return await run_google_io(function, *args, **kwargs)
return wrapped
def _serialized(lock):
def decorate(function):
@functools.wraps(function)
def wrapped(*args, **kwargs):
with lock:
return function(*args, **kwargs)
return wrapped
return decorate
settings_update = _serialized(_settings_write_lock)
history_access = _serialized(_history_lock)
class SettingsCache:
"""Serve snapshots immediately; refresh stale data once, in a separate worker."""
def __init__(self, path, spreadsheet_id, ttl=60, retry_delay=15):
self.path = Path(path)
self.spreadsheet_id = spreadsheet_id
self.ttl = ttl
self.retry_delay = retry_delay
self._lock = threading.RLock()
self._refresh_lock = threading.Lock()
self._executor = ThreadPoolExecutor(max_workers=1, thread_name_prefix="settings-refresh")
self._pending = None
self._rows = None
self._loaded_at = float("-inf")
self._retry_at = 0.0
self._generation = 0
self._restore()
@property
def has_value(self):
with self._lock:
return self._rows is not None
@property
def stale(self):
with self._lock:
return time.monotonic() - self._loaded_at >= self.ttl
def _restore(self):
try:
payload = json.loads(self.path.read_text(encoding="utf-8"))
rows = payload["rows"]
if payload["spreadsheet_id"] != self.spreadsheet_id:
return
if not isinstance(rows, list) or not all(
isinstance(row, list) and all(isinstance(cell, str) for cell in row)
for row in rows
):
return
self._rows = rows
# A restored snapshot is usable immediately, but always revalidated.
except (OSError, ValueError, KeyError, TypeError):
pass
def _persist_locked(self):
temporary = None
try:
self.path.parent.mkdir(parents=True, exist_ok=True)
with tempfile.NamedTemporaryFile(
mode="w", encoding="utf-8", dir=self.path.parent, delete=False
) as target:
temporary = target.name
json.dump(
{"spreadsheet_id": self.spreadsheet_id, "rows": self._rows},
target, ensure_ascii=False,
)
os.replace(temporary, self.path)
except OSError as exc:
logger.warning("Settings snapshot persistence failed (%s)", type(exc).__name__)
finally:
if temporary and os.path.exists(temporary):
os.unlink(temporary)
def publish(self, rows):
with self._lock:
self._rows = copy.deepcopy(rows)
self._loaded_at = time.monotonic()
self._retry_at = 0.0
self._generation += 1
self._persist_locked()
def invalidate(self):
with self._lock:
self._loaded_at = float("-inf")
self._retry_at = 0.0
self._generation += 1
def _load(self, loader, force=False):
with self._refresh_lock:
with self._lock:
if not force:
if self._rows is not None and not self.stale:
return copy.deepcopy(self._rows)
if time.monotonic() < self._retry_at:
if self._rows is not None:
return copy.deepcopy(self._rows)
raise RuntimeError("Settings refresh is cooling down")
generation = self._generation
try:
rows = loader()
except Exception:
with self._lock:
self._retry_at = time.monotonic() + self.retry_delay
raise
with self._lock:
# An earlier read must never replace a subsequently saved setting.
if generation == self._generation:
self.publish(rows)
return copy.deepcopy(self._rows)
def _background_load(self, loader):
try:
self._load(loader)
except Exception as exc:
# Do not log URLs, credential contents or settings data.
logger.warning("Google settings refresh failed (%s); retaining snapshot", type(exc).__name__)
def refresh_in_background(self, loader):
with self._lock:
if time.monotonic() < self._retry_at:
return
if self._pending is None or self._pending.done():
self._pending = self._executor.submit(self._background_load, loader)
def read(self, loader, force=False):
if force:
# Editors must not overwrite Google using a stale cached snapshot.
return self._load(loader, force=True)
with self._lock:
if self._rows is not None:
rows = copy.deepcopy(self._rows)
if self.stale:
self.refresh_in_background(loader)
return rows
return self._load(loader)
def close(self):
self._executor.shutdown(wait=True)
+119
View File
@@ -0,0 +1,119 @@
"""Back up, merge and import both Google history tabs into corm_db once."""
import argparse
import json
import sys
from pathlib import Path
from dotenv import load_dotenv
from history_store import PostgresHistoryStore, merge_sheet_and_local, validate_bundle
from settings_migrate import BASE_DIR, fingerprint, save_snapshot
from settings_store import SettingsAlreadyInitialized, SettingsNotInitialized, settings_db_config
def read_local_records(path):
target = Path(path)
if not target.exists():
return []
return [json.loads(line) for line in target.read_text(encoding="utf-8").splitlines() if line.strip()]
def read_source(sms_local_file=None, manual_local_file=None):
import main
sms_path = Path(sms_local_file or main.SMS_HISTORY_FILE)
manual_path = Path(manual_local_file or main.MANUAL_HISTORY_FILE)
# Explicit source paths must exist; a missing mounted file must not lose detail silently.
for explicit_path in (sms_local_file, manual_local_file):
if explicit_path and not Path(explicit_path).is_file():
raise FileNotFoundError("지정한 로컬 내역 파일이 없습니다.")
local_sms = read_local_records(sms_path)
local_manual = read_local_records(manual_path)
spreadsheet = main.get_gspread_client().open_by_key(main.SETTINGS_SPREADSHEET_ID)
sms_rows = spreadsheet.worksheet("문자내역").get_all_values()
manual_rows = spreadsheet.worksheet("수동발주내역").get_all_values()
bundle = {
"kind": "corm-history",
"sms": merge_sheet_and_local("sms", sms_rows, local_sms),
"manual": merge_sheet_and_local("manual", manual_rows, local_manual),
# Keep complete source material, including unused columns and local-only records.
"source_rows": {"문자내역": sms_rows, "수동발주내역": manual_rows},
"local_records": {"sms": local_sms, "manual": local_manual},
"local_files_found": {"sms": sms_path.is_file(), "manual": manual_path.is_file()},
}
return bundle, "google:" + main.SETTINGS_SPREADSHEET_ID + ":histories+local"
def load_backup(path):
payload = json.loads(Path(path).read_text(encoding="utf-8"))
bundle = payload["rows"]
if payload.get("format") != 1 or payload.get("sha256") != fingerprint(bundle):
raise ValueError("내역 백업 체크섬이 올바르지 않습니다.")
validate_bundle(bundle)
return bundle, "history-backup-file"
def build_store():
return PostgresHistoryStore(settings_db_config())
def main(argv=None):
load_dotenv(BASE_DIR / ".env")
parser = argparse.ArgumentParser(description="문자내역·수동발주내역 → corm_db 이전 / 검증 / 백업")
commands = parser.add_subparsers(dest="command", required=True)
importer = commands.add_parser("import", help="기본은 점검만, --apply일 때만 DB 저장")
importer.add_argument("--apply", action="store_true")
importer.add_argument("--backup")
importer.add_argument("--input-file")
verifier = commands.add_parser("verify", help="전체 내역과 순서를 검증")
verifier.add_argument("--input-file")
snapshot = commands.add_parser("snapshot", help="DB에 접속하지 않고 시트·로컬 원본 백업")
snapshot.add_argument("--output", required=True)
for command in (importer, verifier, snapshot):
command.add_argument("--sms-local-file", help="기존 sms_history.dat 파일 경로")
command.add_argument("--manual-local-file", help="기존 manual_history.dat 파일 경로")
exporter = commands.add_parser("export", help="DB 내역 전체 백업")
exporter.add_argument("--output", required=True)
args = parser.parse_args(argv)
if args.command == "import" and args.apply and not args.backup:
parser.error("--apply에는 --backup 경로가 필요합니다.")
try:
if args.command == "export":
bundle = build_store().export_all()
save_snapshot(args.output, bundle, "postgres:histories")
print(f"내역 백업 완료: 문자 {len(bundle['sms'])}건 / 수동발주 {len(bundle['manual'])}건.")
return 0
bundle, source = (load_backup(args.input_file) if getattr(args, "input_file", None) else
read_source(args.sms_local_file, args.manual_local_file))
expected = validate_bundle(bundle)
print(f"원본 확인: 문자 {len(expected['sms'])}건 / 수동발주 {len(expected['manual'])}건.")
if "local_files_found" in bundle:
for kind, label in (("sms", "문자"), ("manual", "수동발주")):
state = "있음" if bundle["local_files_found"][kind] else "없음"
print(f"로컬 {label} 파일: {state} ({len(bundle['local_records'][kind])}건).")
if args.command == "snapshot":
save_snapshot(args.output, bundle, source)
print("시트·로컬 원본 백업 완료. DB·구글 시트에 쓰지 않았습니다.")
return 0
if args.command == "verify":
if validate_bundle(build_store().export_all()) != expected:
print("검증 실패: 원본과 DB 내역이 다릅니다. 전환 전에 차이를 확인해주세요.", file=sys.stderr)
return 1
print("원본과 DB 전체 내역·순서 일치.")
elif args.apply:
save_snapshot(args.backup, bundle, source)
build_store().import_all(bundle, source)
print("두 내역의 DB 이전 및 트랜잭션 내 전체 검증 완료.")
else:
print("점검만 완료했습니다. DB·구글 시트에 쓰지 않았습니다.")
return 0
except (SettingsAlreadyInitialized, SettingsNotInitialized) as exc:
print(str(exc), file=sys.stderr)
except Exception as exc:
# Never print customer information, source rows or connection credentials.
print(f"작업 실패 ({type(exc).__name__}). 접속 환경·원본·로컬 내역·백업 경로를 확인해주세요.", file=sys.stderr)
return 1
if __name__ == "__main__":
raise SystemExit(main())
+216
View File
@@ -0,0 +1,216 @@
"""CORM histories: stable record IDs and complete payloads in the CORM database."""
import copy
import json
from collections import defaultdict
from contextlib import closing
from psycopg2 import sql
from psycopg2.extras import Json, execute_values
from settings_store import PostgresSettingsStore, SettingsAlreadyInitialized, SettingsNotInitialized
TABLES = {"sms": "corm_sms_history", "manual": "corm_manual_history"}
def first_value(data, *keys):
for key in keys:
value = data.get(key)
if value is not None and str(value).strip():
return str(value)
return ""
def normalize_record(kind, data):
if kind not in TABLES or not isinstance(data, dict):
raise ValueError("올바른 내역 객체가 필요합니다.")
record = copy.deepcopy(data)
record.pop("id", None)
timestamp = record.get("timestamp")
if not isinstance(timestamp, str) or not timestamp.strip():
raise ValueError("내역에 일시가 필요합니다.")
if kind == "sms":
for short, alias, keys in (
("custName", "customer_name", ("custName", "customer_name", "customerName", "name")),
("custPhone", "phone", ("custPhone", "phone", "customer_phone", "customerPhone")),
("custAddress", "address", ("custAddress", "address", "customer_address", "customerAddress")),
):
record[short] = record[alias] = first_value(record, *keys)
if not record.get("items_str"):
items = record.get("items") or []
if not isinstance(items, list) or not all(isinstance(item, dict) for item in items):
raise ValueError("상품 목록 형식이 올바르지 않습니다.")
record["items_str"] = ", ".join(
f"{item.get('label', '')}[{item.get('code', '')}] {item.get('qty', 0)}개"
for item in items
)
# Validate before DB writes; reject non-JSON payloads rather than losing fields.
json.dumps(record, ensure_ascii=False, allow_nan=False)
return record
def sheet_records(kind, rows):
records = []
for row in rows[1:]:
if not row or not row[0].strip():
continue
cells = row + [""] * 8
if kind == "sms":
record = dict(zip(("timestamp", "orderType", "deposit_amount_str", "items_str",
"previewText", "custName", "custPhone", "custAddress"), cells[:8]))
record["previewText"] = record["previewText"].replace(" ", "\n")
else:
amount = cells[2].replace(",", "")
record = {"timestamp": cells[0], "customer_name": cells[1],
"total_amount": int(amount) if amount.lstrip("-").isdigit() else amount}
record["_legacy_sheet_row"] = row
records.append(normalize_record(kind, record))
return records
def merge_sheet_and_local(kind, rows, local_records):
"""Match occurrences one-to-one; preserve unmatched rows and local-only saves."""
local_by_time = defaultdict(list)
normalized = [normalize_record(kind, item) for item in local_records]
for index, record in enumerate(normalized):
local_by_time[record["timestamp"]].append(index)
used = set()
result = []
keys = (("orderType", "deposit_amount_str", "items_str", "previewText", "custName", "custPhone", "custAddress")
if kind == "sms" else ("customer_name", "total_amount"))
def comparable(value, key):
result = " ".join(str(value).split()) if value is not None else ""
return result.replace(",", "") if key in {"deposit_amount_str", "total_amount"} else result
for record in sheet_records(kind, rows):
candidates = local_by_time[record["timestamp"]]
for index in candidates:
local = normalized[index]
if index in used or any(
comparable(record.get(key), key) and comparable(local.get(key), key)
and comparable(record.get(key), key) != comparable(local.get(key), key) for key in keys
):
continue
merged = {**record, **local}
for key, value in record.items():
if not merged.get(key) and value:
merged[key] = value
record = normalize_record(kind, merged)
used.add(index)
break
result.append(record)
result.extend(record for index, record in enumerate(normalized) if index not in used)
return result
class PostgresHistoryStore:
def __init__(self, db_config):
self.database = PostgresSettingsStore(db_config)
def _connect(self):
return self.database._connect()
@staticmethod
def _create_tables(cursor):
for table in TABLES.values():
cursor.execute(sql.SQL("""
CREATE TABLE IF NOT EXISTS {} (
id BIGSERIAL PRIMARY KEY,
occurred_at TEXT NOT NULL,
payload JSONB NOT NULL CHECK (jsonb_typeof(payload) = 'object'),
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
)
""").format(sql.Identifier(table)))
cursor.execute(sql.SQL("CREATE INDEX IF NOT EXISTS {} ON {} (occurred_at text_pattern_ops)")
.format(sql.Identifier(table + "_time_idx"), sql.Identifier(table)))
cursor.execute("""
CREATE TABLE IF NOT EXISTS corm_history_imports (
id SMALLINT PRIMARY KEY CHECK (id = 1), source TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
)
""")
def check_ready(self):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT id FROM corm_history_imports WHERE id = 1")
if cursor.fetchone() is None:
raise SettingsNotInitialized("내역 DB 이전이 필요합니다. history_migrate.py를 실행해주세요.")
for table in TABLES.values():
cursor.execute(sql.SQL("SELECT id FROM {} LIMIT 0").format(sql.Identifier(table)))
def list_records(self, kind, day=None):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
query = sql.SQL("SELECT id, payload FROM {} ").format(sql.Identifier(TABLES[kind]))
parameters = ()
if day is not None:
query += sql.SQL("WHERE occurred_at LIKE %s ")
parameters = (day + "%",)
cursor.execute(query + sql.SQL("ORDER BY id"), parameters)
return [{**payload, "id": record_id} for record_id, payload in cursor.fetchall()]
def add(self, kind, data):
record = normalize_record(kind, data)
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute(sql.SQL("INSERT INTO {} (occurred_at, payload) VALUES (%s, %s) RETURNING id")
.format(sql.Identifier(TABLES[kind])), (record["timestamp"], Json(record)))
return cursor.fetchone()[0]
def get_manual(self, record_id):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT payload FROM corm_manual_history WHERE id = %s", (record_id,))
record = cursor.fetchone()
return record[0] if record else None
def manual_timestamp_count(self, timestamp):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT count(*) FROM corm_manual_history WHERE occurred_at = %s", (timestamp,))
return cursor.fetchone()[0]
def delete(self, kind, record_id):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute(sql.SQL("DELETE FROM {} WHERE id = %s RETURNING id")
.format(sql.Identifier(TABLES[kind])), (record_id,))
return cursor.fetchone() is not None
def clear_sms(self):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("DELETE FROM corm_sms_history")
def export_all(self):
# One repeatable-read transaction gives both tables the same snapshot.
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY")
result = {"kind": "corm-history"}
for kind, table in TABLES.items():
cursor.execute(sql.SQL("SELECT payload FROM {} ORDER BY id").format(sql.Identifier(table)))
result[kind] = [row[0] for row in cursor.fetchall()]
return result
def import_all(self, bundle, source):
records = validate_bundle(bundle)
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
self._create_tables(cursor)
cursor.execute("LOCK TABLE corm_sms_history, corm_manual_history, corm_history_imports IN EXCLUSIVE MODE")
for table in (*TABLES.values(), "corm_history_imports"):
cursor.execute(sql.SQL("SELECT EXISTS (SELECT 1 FROM {})").format(sql.Identifier(table)))
if cursor.fetchone()[0]:
raise SettingsAlreadyInitialized("이미 DB 내역이 있습니다. 기존 내역을 덮어쓰지 않았습니다.")
for kind, table in TABLES.items():
if records[kind]:
execute_values(cursor, sql.SQL("INSERT INTO {} (occurred_at, payload) VALUES %s")
.format(sql.Identifier(table)),
[(record["timestamp"], Json(record)) for record in records[kind]])
cursor.execute(sql.SQL("SELECT payload FROM {} ORDER BY id").format(sql.Identifier(table)))
if [row[0] for row in cursor.fetchall()] != records[kind]:
raise RuntimeError("내역 이전 검증 실패. 트랜잭션을 취소합니다.")
cursor.execute("INSERT INTO corm_history_imports (id, source) VALUES (1, %s)", (source,))
def validate_bundle(bundle):
if not isinstance(bundle, dict) or bundle.get("kind") != "corm-history":
raise ValueError("CORM 내역 백업이 필요합니다.")
if not all(isinstance(bundle.get(kind), list) for kind in TABLES):
raise ValueError("문자내역과 수동발주내역 목록이 필요합니다.")
return {kind: [normalize_record(kind, item) for item in bundle[kind]] for kind in TABLES}
+677 -616
View File
File diff suppressed because it is too large Load Diff
+203
View File
@@ -0,0 +1,203 @@
"""Editable manual-order queue, with atomic cell updates and recoverable exports."""
import io
import re
import uuid
from contextlib import closing
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill
from psycopg2.extras import Json, execute_values
from history_store import normalize_record
from settings_store import PostgresSettingsStore, SettingsAlreadyInitialized, SettingsNotInitialized
HEADERS = ["주문날짜", "번호", "주문번호", "수령인명", "상품코드", "상품명", "주문수량", "주소", "우편번호",
"수령인 전화번호", "수령인 휴대폰", "배송시 요구사항", "송장번호", "주문처", "비고"]
class CellConflict(RuntimeError):
def __init__(self, rows):
self.rows = rows
super().__init__("다른 사용자가 수정한 셀이 있습니다. 최신값을 확인해주세요.")
def validate_source(source):
if not isinstance(source, dict) or source.get("kind") != "corm-manual-orders":
raise ValueError("수동발주 원본이 필요합니다.")
headers, rows = source.get("headers"), source.get("rows")
if not isinstance(headers, list) or len(headers) < 15 or len(headers) > 100 or not all(isinstance(x, str) for x in headers):
raise ValueError("원본 열 구성을 확인해주세요.")
if not isinstance(rows, list) or not all(isinstance(row, list) and len(row) <= len(headers)
and all(isinstance(x, str) and "\x00" not in x for x in row) for row in rows):
raise ValueError("원본 셀 형식을 확인해주세요.")
return headers, [row + [""] * (len(headers) - len(row)) for row in rows]
def build_xlsx(headers, rows):
workbook = Workbook()
sheet = workbook.active
sheet.title = "수동 발주"
for index, values in enumerate([headers] + rows, start=1):
for column, value in enumerate(values, start=1):
numeric = index > 1 and column in (3, 7) and re.fullmatch(r"-?(?:0|[1-9][0-9]{0,14})", value.replace(",", ""))
cell = sheet.cell(index, column, int(value.replace(",", "")) if numeric else value)
# IDs, phone numbers, leading zeros and '=' always remain literal text.
if not numeric:
cell.data_type = "s"
cell.alignment = Alignment(vertical="top", wrap_text=True)
if index == 1:
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill("solid", fgColor="334155")
sheet.freeze_panes = "A2"
sheet.auto_filter.ref = sheet.dimensions
for column in sheet.columns:
sheet.column_dimensions[column[0].column_letter].width = 21
sheet.column_dimensions["H"].width = 45
sheet.column_dimensions["O"].width = 45
output = io.BytesIO()
workbook.save(output)
return output.getvalue()
class ManualOrderStore:
def __init__(self, config):
self.database = PostgresSettingsStore(config)
def _connect(self):
return self.database._connect()
@staticmethod
def _schema(cursor):
cursor.execute("""CREATE TABLE IF NOT EXISTS corm_manual_sheet (
id SMALLINT PRIMARY KEY CHECK(id=1), headers JSONB NOT NULL, source TEXT NOT NULL,
imported_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP)""")
cursor.execute("""CREATE TABLE IF NOT EXISTS corm_manual_orders (
id BIGSERIAL PRIMARY KEY, order_key TEXT NOT NULL, cells JSONB NOT NULL,
version BIGINT NOT NULL DEFAULT 1, archived_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP)""")
cursor.execute("CREATE INDEX IF NOT EXISTS corm_manual_orders_active ON corm_manual_orders(id) WHERE archived_at IS NULL")
cursor.execute("CREATE INDEX IF NOT EXISTS corm_manual_orders_key ON corm_manual_orders(order_key)")
cursor.execute("""CREATE TABLE IF NOT EXISTS corm_manual_exports (
id UUID PRIMARY KEY, filename TEXT NOT NULL, content BYTEA NOT NULL,
row_ids JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP)""")
def check_ready(self):
self.list_rows()
def list_rows(self):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT headers FROM corm_manual_sheet WHERE id=1")
metadata = cursor.fetchone()
if metadata is None:
raise SettingsNotInitialized("수동발주 이전을 먼저 실행해주세요.")
cursor.execute("SELECT id, cells, version FROM corm_manual_orders WHERE archived_at IS NULL ORDER BY id")
return {"headers": metadata[0], "rows": [dict(zip(("id", "cells", "version"), row)) for row in cursor.fetchall()]}
def import_source(self, source, label):
headers, rows = validate_source(source)
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
self._schema(cursor)
cursor.execute("LOCK TABLE corm_manual_sheet, corm_manual_orders IN EXCLUSIVE MODE")
cursor.execute("SELECT EXISTS(SELECT 1 FROM corm_manual_sheet) OR EXISTS(SELECT 1 FROM corm_manual_orders)")
if cursor.fetchone()[0]:
raise SettingsAlreadyInitialized("기존 발주 DB를 덮어쓰지 않았습니다.")
cursor.execute("INSERT INTO corm_manual_sheet(id,headers,source) VALUES(1,%s,%s)", (Json(headers), label))
if rows:
execute_values(cursor, "INSERT INTO corm_manual_orders(order_key,cells) VALUES %s",
[("legacy:" + row[0] if row[0] else "legacy-row:" + str(i), Json(row)) for i, row in enumerate(rows)])
cursor.execute("SELECT cells FROM corm_manual_orders ORDER BY id")
if [row[0] for row in cursor.fetchall()] != rows:
raise RuntimeError("발주 원본 검증 실패.")
def append(self, rows, history=None):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT headers FROM corm_manual_sheet WHERE id=1")
metadata = cursor.fetchone()
if not metadata:
raise SettingsNotInitialized("수동발주 이전이 필요합니다.")
key = str(uuid.uuid4())
_, normalized = validate_source({"kind": "corm-manual-orders", "headers": metadata[0],
"rows": [[str(x) for x in row] for row in rows]})
for row in normalized:
cursor.execute("INSERT INTO corm_manual_orders(order_key,cells) VALUES(%s,%s)", (key, Json(row)))
if history is not None:
record = normalize_record("manual", {**history, "manual_order_key": key})
cursor.execute("INSERT INTO corm_manual_history(occurred_at,payload) VALUES(%s,%s)", (record["timestamp"], Json(record)))
return key
def patch_cells(self, changes):
if not changes or len(changes) > 2000 or len({(c["id"], c["column"]) for c in changes}) != len(changes):
raise ValueError("한 번에 수정할 셀을 확인해주세요.")
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT id,cells,version FROM corm_manual_orders WHERE id=ANY(%s) AND archived_at IS NULL ORDER BY id FOR UPDATE",
(sorted({c["id"] for c in changes}),))
rows = {row[0]: {"id": row[0], "cells": row[1], "version": row[2]} for row in cursor.fetchall()}
if len(rows) != len({c["id"] for c in changes}):
raise LookupError("이미 내려받았거나 삭제된 행입니다. 표를 새로고침해주세요.")
conflicts = []
for change in changes:
row = rows[change["id"]]
column = change["column"]
if column < 0 or column >= len(row["cells"]) or len(change["value"]) > 20000 or "\x00" in change["value"]:
raise ValueError("셀 내용을 확인해주세요.")
actual = row["cells"][column]
if actual != change["previous_value"] and actual != change["value"]:
conflicts.append({"id": row["id"], "column": column, "value": actual})
if conflicts:
raise CellConflict(conflicts)
for change in changes:
rows[change["id"]]["cells"][change["column"]] = change["value"]
for row in rows.values():
cursor.execute("UPDATE corm_manual_orders SET cells=%s,version=version+1 WHERE id=%s RETURNING version", (Json(row["cells"]), row["id"]))
row["version"] = cursor.fetchone()[0]
return list(rows.values())
def archive_rows(self, rows):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT id,version FROM corm_manual_orders WHERE id=ANY(%s) AND archived_at IS NULL ORDER BY id FOR UPDATE", ([r["id"] for r in rows],))
versions = dict(cursor.fetchall())
if any(versions.get(r["id"]) != r["version"] for r in rows):
raise CellConflict([])
cursor.execute("UPDATE corm_manual_orders SET archived_at=CURRENT_TIMESTAMP WHERE id=ANY(%s)", ([r["id"] for r in rows],))
def export_and_archive(self, filename):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT headers FROM corm_manual_sheet WHERE id=1")
headers = cursor.fetchone()[0]
cursor.execute("SELECT id,cells FROM corm_manual_orders WHERE archived_at IS NULL ORDER BY id FOR UPDATE")
rows = cursor.fetchall()
content = build_xlsx(headers, [row[1] for row in rows])
export_id = str(uuid.uuid4())
cursor.execute("INSERT INTO corm_manual_exports(id,filename,content,row_ids) VALUES(%s,%s,%s,%s)",
(export_id, filename, content, Json([row[0] for row in rows])))
cursor.execute("UPDATE corm_manual_orders SET archived_at=CURRENT_TIMESTAMP WHERE id=ANY(%s)", ([row[0] for row in rows],))
return content, export_id
def exports(self, export_id=None):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
if export_id:
cursor.execute("SELECT filename,content FROM corm_manual_exports WHERE id=%s", (export_id,))
row = cursor.fetchone()
if row is None:
raise LookupError("다운로드 기록이 없습니다.")
return row[0], bytes(row[1])
cursor.execute("SELECT id,filename,created_at FROM corm_manual_exports ORDER BY created_at DESC LIMIT 10")
return [{"id": str(r[0]), "filename": r[1], "created_at": r[2].isoformat()} for r in cursor.fetchall()]
def delete_history(self, record_id):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT payload FROM corm_manual_history WHERE id=%s FOR UPDATE", (record_id,))
record = cursor.fetchone()
if record is None:
raise LookupError("이미 삭제된 내역입니다.")
payload = record[0]
key = payload.get("manual_order_key")
if not key:
cursor.execute("SELECT count(*) FROM corm_manual_history WHERE occurred_at=%s", (payload["timestamp"],))
if cursor.fetchone()[0] > 1:
raise ValueError("같은 일시의 내역이 여러 건입니다. 수동발주 표에서 대상 주문을 먼저 확인해주세요.")
key = "legacy:" + payload["timestamp"]
cursor.execute("UPDATE corm_manual_orders SET archived_at=CURRENT_TIMESTAMP WHERE order_key=%s AND archived_at IS NULL", (key,))
cursor.execute("DELETE FROM corm_manual_history WHERE id=%s", (record_id,))
+94
View File
@@ -0,0 +1,94 @@
"""Operator-run migration of the active manual-order sheet; source stays intact."""
import argparse
import json
import os
import sys
from pathlib import Path
import psycopg2
import requests
from dotenv import set_key
from manual_orders import ManualOrderStore, validate_source
from settings_store import settings_db_config, SettingsNotInitialized
from settings_migrate import save_snapshot, fingerprint
def read_source():
if os.getenv("MANUAL_ORDERS_BACKEND", "google") == "postgres":
data = ManualOrderStore(settings_db_config()).list_rows()
return {"kind": "corm-manual-orders", "headers": data["headers"], "rows": [r["cells"] for r in data["rows"]]}
import main
worksheet = main.get_gspread_client().open_by_key(main.MANUAL_SPREADSHEET_ID).get_worksheet(0)
values = worksheet.get_all_values()
if not values:
raise ValueError("수동 발주 시트의 머리글을 확인해주세요.")
width = max(len(row) for row in values)
source = {"kind": "corm-manual-orders", "headers": values[0] + [""] * (width - len(values[0])), "rows": values[1:]}
headers, rows = validate_source(source)
return {"kind": "corm-manual-orders", "headers": headers, "rows": rows}
def migrate(directory):
payload = json.loads((Path(directory) / "manual-orders-source.json").read_text(encoding="utf-8"))
source = payload["rows"]
if payload.get("format") != 1 or payload.get("sha256") != fingerprint(source):
raise ValueError("원본 백업 체크섬이 다릅니다.")
headers, rows = validate_source(source)
store = ManualOrderStore(settings_db_config())
try:
stored = store.list_rows()
except (SettingsNotInitialized, psycopg2.errors.UndefinedTable):
store.import_source(source, "manual-order-sheet")
stored = store.list_rows()
if stored["headers"] != headers or [r["cells"] for r in stored["rows"]] != rows:
raise RuntimeError("기존 발주 DB와 원본이 다릅니다. 덮어쓰지 않고 중단합니다.")
print(f"수동발주 {len(rows)}행 · {len(headers)}열 전체 검증 완료.")
def main(argv=None):
parser = argparse.ArgumentParser()
parser.add_argument("step", choices=("snapshot", "migrate", "environment", "probe"))
parser.add_argument("--directory", default="/backups")
args = parser.parse_args(argv)
try:
if args.step == "snapshot":
source = read_source()
path = Path(args.directory) / "manual-orders-source.json"
save_snapshot(path, source, "manual-orders")
owner = path.parent.stat()
os.chown(path, owner.st_uid, owner.st_gid)
print(f"발주 원본 백업 완료: {len(source['rows'])}행.")
elif args.step == "migrate":
migrate(args.directory)
elif args.step == "environment":
path = Path("/deploy/.env")
owner = path.stat()
set_key(path, "MANUAL_ORDERS_BACKEND", "postgres", quote_mode="never")
os.chmod(path, 0o600)
os.chown(path, owner.st_uid, owner.st_gid)
print("수동발주 저장소를 DB로 전환했습니다.")
else:
if os.getenv("MANUAL_ORDERS_BACKEND") != "postgres":
raise RuntimeError("수동발주 저장소가 아직 postgres가 아닙니다.")
ManualOrderStore(settings_db_config()).check_ready()
token = os.getenv("INTERNAL_SERVICE_TOKEN", "")
if not token:
raise RuntimeError("기존 서비스 토큰이 필요합니다.")
response = requests.get("http://127.0.0.1:8002/api/manual/orders", headers={"X-Service-Token": token}, timeout=5)
response.raise_for_status()
if not isinstance(response.json().get("rows"), list):
raise RuntimeError("발주 API 검증 실패.")
print("수동발주 DB·조회 API 정상.")
return 0
except Exception as exc:
if args.step == "probe" and isinstance(exc, requests.exceptions.ConnectionError):
print("앱 시작 응답을 기다리는 중입니다.", file=sys.stderr)
return 1
print(f"수동발주 전환 단계 실패: {args.step} ({type(exc).__name__}). 원본·DB·백업을 확인해주세요.", file=sys.stderr)
return 1
if __name__ == "__main__":
raise SystemExit(main())
+36 -24
View File
@@ -3,9 +3,17 @@ import requests
import time
import bcrypt
import base64
from datetime import datetime, timedelta, timezone
CLIENT_ID = os.getenv("NAVER_CLIENT_ID", "")
CLIENT_SECRET = os.getenv("NAVER_CLIENT_SECRET", "")
REQUEST_TIMEOUT = 30
# 네이버 API는 한국 시간(+09:00)을 기대한다. 컨테이너 TZ(기본 UTC)와 무관하게 KST로 계산한다.
KST = timezone(timedelta(hours=9))
def _kst_iso(moment):
return moment.astimezone(KST).strftime('%Y-%m-%dT%H:%M:%S.000+09:00')
def get_access_token():
timestamp = str(int(time.time() * 1000))
@@ -28,7 +36,7 @@ def get_access_token():
"Content-Type": "application/x-www-form-urlencoded"
}
response = requests.post(url, data=payload, headers=headers)
response = requests.post(url, data=payload, headers=headers, timeout=REQUEST_TIMEOUT)
if response.status_code == 200:
return response.json().get('access_token')
else:
@@ -44,30 +52,34 @@ def get_payment_done_orders(token):
# 네이버 API는 lastChangedFrom과 lastChangedTo의 간격이 최대 24시간입니다.
# 따라서 최근 7일의 데이터를 가져오기 위해 24시간 단위로 반복 조회합니다.
now = time.time()
now = datetime.now(KST)
for days_ago in range(7, 0, -1):
start_time = now - (days_ago * 24 * 3600)
end_time = now - ((days_ago - 1) * 24 * 3600)
if days_ago == 1:
end_time = now
from_date = time.strftime('%Y-%m-%dT%H:%M:%S.000+09:00', time.localtime(start_time))
to_date = time.strftime('%Y-%m-%dT%H:%M:%S.000+09:00', time.localtime(end_time))
start_time = now - timedelta(days=days_ago)
end_time = now - timedelta(days=days_ago - 1)
params = {
"lastChangedFrom": from_date,
"lastChangedTo": to_date,
"lastChangedFrom": _kst_iso(start_time),
"lastChangedTo": _kst_iso(end_time),
"lastChangedType": "PAYED"
}
response = requests.get(url, headers=headers, params=params)
if response.status_code == 200:
data = response.json()
statuses = data.get('data', {}).get('lastChangeStatuses', [])
all_statuses.extend(statuses)
else:
raise Exception(f"Failed to fetch orders ({from_date}): {response.text}")
# 한 번에 최대 300건만 오므로, 'more'가 있으면 이어서 조회한다.
while True:
response = requests.get(url, headers=headers, params=params, timeout=REQUEST_TIMEOUT)
if response.status_code != 200:
raise Exception(f"Failed to fetch orders ({params['lastChangedFrom']}): {response.text}")
data = response.json().get('data') or {}
all_statuses.extend(data.get('lastChangeStatuses', []))
more = data.get('more') or {}
if not more.get('moreFrom') or (
more['moreFrom'] == params["lastChangedFrom"]
and more.get('moreSequence') == params.get("moreSequence")
):
break
params["lastChangedFrom"] = more['moreFrom']
if more.get('moreSequence'):
params["moreSequence"] = more['moreSequence']
return all_statuses
@@ -83,7 +95,7 @@ def get_product_order_details(token, product_order_ids):
payload = {
"productOrderIds": product_order_ids
}
response = requests.post(url, headers=headers, json=payload)
response = requests.post(url, headers=headers, json=payload, timeout=REQUEST_TIMEOUT)
if response.status_code == 200:
return response.json().get('data', [])
else:
@@ -108,7 +120,7 @@ def dispatch_orders(token, dispatch_list):
}
# 발송일은 현재 시간으로 셋팅
dispatch_date = time.strftime('%Y-%m-%dT%H:%M:%S+09:00')
dispatch_date = datetime.now(KST).strftime('%Y-%m-%dT%H:%M:%S+09:00')
for item in dispatch_list:
item["dispatchDate"] = dispatch_date
@@ -116,7 +128,7 @@ def dispatch_orders(token, dispatch_list):
"dispatchProductOrders": dispatch_list
}
response = requests.post(url, headers=headers, json=payload)
response = requests.post(url, headers=headers, json=payload, timeout=REQUEST_TIMEOUT)
if response.status_code == 200:
return response.json()
else:
@@ -136,7 +148,7 @@ def confirm_orders(token, product_order_ids):
"productOrderIds": product_order_ids
}
response = requests.post(url, headers=headers, json=payload)
response = requests.post(url, headers=headers, json=payload, timeout=REQUEST_TIMEOUT)
if response.status_code == 200:
return response.json()
else:
@@ -148,7 +160,7 @@ def get_product_details(token, product_id):
headers = {
"Authorization": f"Bearer {token}"
}
response = requests.get(url, headers=headers)
response = requests.get(url, headers=headers, timeout=REQUEST_TIMEOUT)
if response.status_code == 200:
return response.json()
+2
View File
@@ -1,4 +1,5 @@
fastapi
httpx>=0.28
uvicorn
pydantic
gspread
@@ -13,3 +14,4 @@ requests
psycopg2-binary
python-dotenv
itsdangerous
holidays
+13 -19
View File
@@ -21,10 +21,10 @@ import urllib.request
import pandas as pd
import psycopg2
import psycopg2.extras
import requests
from fastapi import APIRouter, HTTPException
from fastapi.responses import StreamingResponse
from pydantic import BaseModel
from google_runtime import GOOGLE_READ_TIMEOUT, GOOGLE_TIMEOUT, create_google_client, create_google_session, google_worker
router = APIRouter(prefix="/api/coupang-milkrun", tags=["Coupang Milkrun"])
@@ -226,13 +226,13 @@ def _compute_milkrun_result(text: str):
@router.post("/analyze")
async def analyze_milkrun(payload: MilkrunAnalyzeRequest):
def analyze_milkrun(payload: MilkrunAnalyzeRequest):
result = _compute_milkrun_result(payload.text)
return {"status": "success", **result}
@router.post("/download")
async def download_milkrun(payload: MilkrunAnalyzeRequest):
def download_milkrun(payload: MilkrunAnalyzeRequest):
result = _compute_milkrun_result(payload.text)
df = pd.DataFrame([
@@ -313,9 +313,8 @@ _DRIVE_CACHE_TTL_SECONDS = 300
_drive_cache = {} # file_id -> (저장시각, 파일 bytes)
_sheet_tabs_cache = {} # file_id -> (저장시각, 시트 목록 응답)
# 구글이 응답을 안 주면 무한정 매달리지 않도록 (연결 대기, 응답 대기) 초
_HTTP_TIMEOUT = (10, 120)
_HTTP_READ_TIMEOUT = 120
# 구글이 응답을 안 주면 무한정 매달리지 않도록 응답 대기 시간(초)을 제한한다
_HTTP_READ_TIMEOUT = GOOGLE_READ_TIMEOUT
# 밀크런 출고리스트 구글 시트 (화면에서 주소를 입력하지 않아도 되도록 기본값으로 둔다)
MILKRUN_SHEET_URL = os.getenv(
@@ -419,7 +418,7 @@ def _drive_get(session, file_id, params):
return session.get(
f"https://www.googleapis.com/drive/v3/files/{file_id}",
params={**params, "supportsAllDrives": "true"},
timeout=60,
timeout=GOOGLE_TIMEOUT,
)
@@ -458,12 +457,7 @@ def _fetch_public_workbook(file_id):
def _fetch_drive_workbook_authenticated(file_id):
try:
from google.auth.transport.requests import AuthorizedSession
except ImportError:
raise HTTPException(status_code=500, detail="google-auth 라이브러리가 설치되어 있지 않습니다.")
session = AuthorizedSession(_load_credentials([DRIVE_SCOPE]))
session = create_google_session(_load_credentials([DRIVE_SCOPE]))
response = _drive_get(session, file_id, {"alt": "media"})
if response.status_code != 200:
raise HTTPException(status_code=400, detail=_drive_error_detail(response))
@@ -491,9 +485,7 @@ def fetch_drive_workbook(file_id):
def fetch_drive_file_name(file_id):
"""파일 이름은 있으면 좋은 정보일 뿐이라, 못 가져와도 조용히 빈 값으로 둔다."""
try:
from google.auth.transport.requests import AuthorizedSession
session = AuthorizedSession(_load_credentials([DRIVE_SCOPE]))
session = create_google_session(_load_credentials([DRIVE_SCOPE]))
response = _drive_get(session, file_id, {"fields": "name"})
if response.status_code == 200:
return response.json().get("name", "")
@@ -548,7 +540,7 @@ def _read_sheet_tabs(file_id):
raise HTTPException(status_code=500, detail="gspread 라이브러리가 설치되어 있지 않습니다.")
try:
client = gspread.authorize(_load_credentials([SHEETS_SCOPE]))
client = create_google_client(_load_credentials([SHEETS_SCOPE]))
spreadsheet = client.open_by_key(file_id)
return {
"source": "google_sheet",
@@ -672,7 +664,8 @@ def _read_milkrun_sheet(content, sheet_name):
@router.post("/sheet-rows")
async def get_sheet_rows(payload: SheetRowsRequest):
@google_worker
def get_sheet_rows(payload: SheetRowsRequest):
"""선택한 시트의 제품코드/제품명/수량을 읽어 분석용 텍스트로 돌려준다."""
file_id = extract_file_id(payload.url or MILKRUN_SHEET_URL)
if not payload.sheet:
@@ -687,7 +680,8 @@ async def get_sheet_rows(payload: SheetRowsRequest):
@router.post("/sheet-tabs")
async def get_sheet_tabs(payload: SheetTabsRequest):
@google_worker
def get_sheet_tabs(payload: SheetTabsRequest):
"""구글 드라이브 파일의 시트(탭) 이름 목록을 돌려준다."""
file_id = extract_file_id(payload.url or MILKRUN_SHEET_URL)
File diff suppressed because it is too large Load Diff
+28 -23
View File
@@ -32,6 +32,7 @@ import hashlib
import io
import json
import re
import threading
import time
import urllib.parse
from bisect import bisect_left
@@ -127,6 +128,8 @@ _HEADER_SCAN_ROWS = 15
_SCAN_CACHE_MAX = 4
_SCAN_CACHE_TTL_SECONDS = 600
_scan_cache = OrderedDict() # sha1 -> (저장시각, 스캔결과)
# 엔드포인트가 스레드풀에서 동시에 돌 수 있으므로 캐시 조작은 잠금 안에서 한다.
_scan_cache_lock = threading.Lock()
_DATE_FORMATS = (
"%Y-%m-%d %H:%M:%S", "%Y-%m-%d %H:%M", "%Y-%m-%d",
@@ -262,22 +265,24 @@ def _same_name(original_filename):
# 발주서 스캔 (read_only 1회 통과 + 해시 캐시)
# =====================================================================
def _cache_get(key):
entry = _scan_cache.get(key)
if entry is None:
return None
saved_at, scan = entry
if time.monotonic() - saved_at > _SCAN_CACHE_TTL_SECONDS:
_scan_cache.pop(key, None)
return None
_scan_cache.move_to_end(key)
return scan
with _scan_cache_lock:
entry = _scan_cache.get(key)
if entry is None:
return None
saved_at, scan = entry
if time.monotonic() - saved_at > _SCAN_CACHE_TTL_SECONDS:
_scan_cache.pop(key, None)
return None
_scan_cache.move_to_end(key)
return scan
def _cache_put(key, scan):
_scan_cache[key] = (time.monotonic(), scan)
_scan_cache.move_to_end(key)
while len(_scan_cache) > _SCAN_CACHE_MAX:
_scan_cache.popitem(last=False)
with _scan_cache_lock:
_scan_cache[key] = (time.monotonic(), scan)
_scan_cache.move_to_end(key)
while len(_scan_cache) > _SCAN_CACHE_MAX:
_scan_cache.popitem(last=False)
def _collect_row(row_index, values, orders, sequence):
@@ -593,9 +598,9 @@ def _validate_gift(gift_item_code, gift_name, gift_qty, limit):
@router.post("/first-come/analyze")
async def first_come_analyze(file: UploadFile = File(...)):
def first_come_analyze(file: UploadFile = File(...)):
"""업로드된 발주서에서 주문목록(Q열) 후보를 뽑아 가나다순으로 돌려준다."""
file_bytes = await file.read()
file_bytes = file.file.read()
scan = scan_order_sheet(file_bytes, file.filename)
return {
"status": "success",
@@ -609,7 +614,7 @@ async def first_come_analyze(file: UploadFile = File(...)):
@router.post("/first-come/preview")
async def first_come_preview(
def first_come_preview(
file: UploadFile = File(...),
selected_json: str = Form(...),
limit: int = Form(...),
@@ -621,7 +626,7 @@ async def first_come_preview(
selected_names = _parse_selected(selected_json, "사은품이 지급되는 주문을 최소 1개 선택해주세요.")
_validate_gift(gift_item_code, gift_name, gift_qty, limit)
file_bytes = await file.read()
file_bytes = file.file.read()
scan = scan_order_sheet(file_bytes, file.filename)
result = _first_come_plan(scan, selected_names, limit)
@@ -647,7 +652,7 @@ async def first_come_preview(
@router.post("/first-come/download")
async def first_come_download(
def first_come_download(
file: UploadFile = File(...),
selected_json: str = Form(...),
limit: int = Form(...),
@@ -664,7 +669,7 @@ async def first_come_download(
selected_names = _parse_selected(selected_json, "사은품이 지급되는 주문을 최소 1개 선택해주세요.")
gift = _validate_gift(gift_item_code, gift_name, gift_qty, limit)
file_bytes = await file.read()
file_bytes = file.file.read()
scan = scan_order_sheet(file_bytes, file.filename)
result = _first_come_plan(scan, selected_names, limit)
@@ -1066,9 +1071,9 @@ def _parse_extract_mode(mode):
@router.post("/event-extract/analyze")
async def event_extract_analyze(file: UploadFile = File(...)):
def event_extract_analyze(file: UploadFile = File(...)):
"""업로드된 주문 CSV에서 상품명 후보를 뽑아 가나다순으로 돌려준다."""
file_bytes = await file.read()
file_bytes = file.file.read()
scan = scan_event_csv(file_bytes, file.filename)
warnings = []
@@ -1092,7 +1097,7 @@ async def event_extract_analyze(file: UploadFile = File(...)):
@router.post("/event-extract/download")
async def event_extract_download(
def event_extract_download(
file: UploadFile = File(...),
selected_json: str = Form(...),
mode: str = Form("product"),
@@ -1101,7 +1106,7 @@ async def event_extract_download(
selected_names = _parse_selected(selected_json)
_parse_extract_mode(mode)
file_bytes = await file.read()
file_bytes = file.file.read()
scan = scan_event_csv(file_bytes, file.filename)
result = _event_extract_select(scan, selected_names, mode)
+185 -183
View File
@@ -1,4 +1,5 @@
from fastapi import APIRouter, HTTPException, Query
from contextlib import closing
from fastapi import APIRouter, HTTPException
from pydantic import BaseModel
from typing import Optional, List
import os
@@ -37,6 +38,20 @@ ORDER_DB_CONFIG = {
def get_order_db_conn():
return psycopg2.connect(**ORDER_DB_CONFIG, cursor_factory=psycopg2.extras.DictCursor)
# 컨테이너 TZ(기본 UTC)와 무관하게 입고일은 한국 날짜로 기록한다.
KST = datetime.timezone(datetime.timedelta(hours=9))
ORDER_LOOKUP_SQL = """
SELECT vendor, recipient_name, recipient_phone, recipient_mobile, address
FROM orders
WHERE tracking_number = %s
OR (tracking_number IS NOT NULL
AND tracking_number != ''
AND %s = ANY(regexp_split_to_array(tracking_number, '[,\\s/;]+')))
ORDER BY order_date DESC LIMIT 1
"""
class ReturnRequest(BaseModel):
id: Optional[int] = None
request_date: str
@@ -63,23 +78,13 @@ class BulkReturnReceive(BaseModel):
tracking_numbers: List[str]
@router.get("/lookup")
async def return_lookup(tracking_no: str):
def return_lookup(tracking_no: str):
try:
tracking_no = tracking_no.strip()
conn = get_order_db_conn()
cur = conn.cursor()
# 주문일시(order_date 또는 upload_date) 기준 가장 최신 1건 조회
cur.execute("""
SELECT vendor, recipient_name, recipient_phone, recipient_mobile, address
FROM orders
WHERE tracking_number = %s
OR (tracking_number IS NOT NULL
AND tracking_number != ''
AND %s = ANY(regexp_split_to_array(tracking_number, '[,\\s/;]+')))
ORDER BY order_date DESC LIMIT 1
""", (tracking_no, tracking_no))
row = cur.fetchone()
conn.close()
with closing(get_order_db_conn()) as conn, conn.cursor() as cur:
# 주문일시(order_date) 기준 가장 최신 1건 조회
cur.execute(ORDER_LOOKUP_SQL, (tracking_no, tracking_no))
row = cur.fetchone()
if row:
return {"success": True, "data": dict(row)}
@@ -89,219 +94,216 @@ async def return_lookup(tracking_no: str):
return {"success": False, "message": str(e)}
@router.post("/request")
async def create_return_request(req: ReturnRequest):
def create_return_request(req: ReturnRequest):
try:
req.tracking_no = req.tracking_no.strip()
conn = get_return_db_conn()
cur = conn.cursor()
if req.id:
cur.execute("""
UPDATE return_requests
SET request_date=%s, request_type=%s, receiver_name=%s, address=%s, phone=%s, tracking_no=%s, mall=%s, remarks=%s
WHERE id=%s
""", (req.request_date, req.request_type, req.receiver_name, req.address, req.phone, req.tracking_no, req.mall, req.remarks, req.id))
else:
cur.execute("""
INSERT INTO return_requests
(request_date, request_type, receiver_name, address, phone, tracking_no, mall, remarks)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (req.request_date, req.request_type, req.receiver_name, req.address, req.phone, req.tracking_no, req.mall, req.remarks))
conn.commit()
conn.close()
with closing(get_return_db_conn()) as conn, conn, conn.cursor() as cur:
_save_return_request(cur, req)
return {"success": True}
except Exception as e:
print(f"Request error: {e}")
raise HTTPException(status_code=500, detail=str(e))
def _save_return_request(cur, req):
if req.id:
cur.execute("""
UPDATE return_requests
SET request_date=%s, request_type=%s, receiver_name=%s, address=%s, phone=%s, tracking_no=%s, mall=%s, remarks=%s
WHERE id=%s
""", (req.request_date, req.request_type, req.receiver_name, req.address, req.phone, req.tracking_no, req.mall, req.remarks, req.id))
else:
cur.execute("""
INSERT INTO return_requests
(request_date, request_type, receiver_name, address, phone, tracking_no, mall, remarks)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (req.request_date, req.request_type, req.receiver_name, req.address, req.phone, req.tracking_no, req.mall, req.remarks))
@router.get("/request")
async def get_return_requests(tracking_no: Optional[str] = None):
def get_return_requests(tracking_no: Optional[str] = None, all_records: bool = False):
try:
conn = get_return_db_conn()
cur = conn.cursor()
if tracking_no:
cur.execute("SELECT * FROM return_requests WHERE TRIM(tracking_no) = %s ORDER BY created_at DESC LIMIT 1", (tracking_no.strip(),))
else:
cur.execute("SELECT * FROM return_requests ORDER BY id DESC LIMIT 50")
rows = cur.fetchall()
conn.close()
conditions, params = [], []
if tracking_no and tracking_no.strip():
conditions.append("regexp_replace(r.tracking_no, '[-[:space:]]', '', 'g') = %s")
params.append(''.join(tracking_no.replace('-', '').split()))
elif not all_records:
conditions.append("r.request_date::text >= to_char(CURRENT_DATE - INTERVAL '3 months', 'YYYY-MM-DD')")
where = ' WHERE ' + ' AND '.join(conditions) if conditions else ''
with closing(get_return_db_conn()) as conn, conn.cursor() as cur:
cur.execute("""
SELECT r.*, EXISTS (
SELECT 1 FROM return_receivings v
WHERE TRIM(v.tracking_no) = TRIM(r.tracking_no)
) AS is_received
FROM return_requests r
""" + where + " ORDER BY r.request_date DESC, r.id DESC", params)
rows = cur.fetchall()
return {"success": True, "data": [dict(r) for r in rows]}
except Exception as e:
return {"success": False, "message": str(e)}
@router.post("/receive/bulk")
async def create_return_receive_bulk(req: BulkReturnReceive):
def create_return_receive_bulk(req: BulkReturnReceive):
try:
from datetime import datetime
today = datetime.now().strftime("%Y-%m-%d")
conn_return = get_return_db_conn()
cur_return = conn_return.cursor()
conn_order = get_order_db_conn()
cur_order = conn_order.cursor()
today = datetime.datetime.now(KST).strftime("%Y-%m-%d")
success_count = 0
duplicates = []
seen = set()
for tracking_no in req.tracking_numbers:
tracking_no = tracking_no.strip()
if not tracking_no:
continue
with closing(get_return_db_conn()) as conn_return, closing(get_order_db_conn()) as conn_order:
with conn_return, conn_return.cursor() as cur_return, conn_order.cursor() as cur_order:
for tracking_no in req.tracking_numbers:
tracking_no = tracking_no.strip()
if not tracking_no:
continue
mall = ""
receiver_name = ""
phone = ""
address = ""
# 단건 입고와 같이 이미 입고된 송장(또는 같은 입력 안의 중복)은 다시 넣지 않는다.
if tracking_no in seen:
duplicates.append(tracking_no)
continue
seen.add(tracking_no)
cur_return.execute("SELECT 1 FROM return_receivings WHERE TRIM(tracking_no) = %s LIMIT 1", (tracking_no,))
if cur_return.fetchone():
duplicates.append(tracking_no)
continue
# 1. Check return_requests
cur_return.execute("SELECT mall, receiver_name, phone, address, request_type, remarks FROM return_requests WHERE TRIM(tracking_no) = %s", (tracking_no,))
req_row = cur_return.fetchone()
if req_row:
mall = req_row['mall'] or ""
receiver_name = req_row['receiver_name'] or ""
phone = req_row['phone'] or ""
address = req_row['address'] or ""
req_type = req_row['request_type'] or ""
req_remarks = req_row['remarks'] or ""
if req_remarks:
remarks = f"{req_type}/{req_remarks}"
else:
remarks = req_type
else:
remarks = "고객이 반품"
# 2. Check orders
cur_order.execute("""
SELECT vendor, recipient_name, recipient_phone, recipient_mobile, address
FROM orders
WHERE tracking_number = %s
OR (tracking_number IS NOT NULL
AND tracking_number != ''
AND %s = ANY(regexp_split_to_array(tracking_number, '[,\\s/;]+')))
ORDER BY order_date DESC LIMIT 1
""", (tracking_no, tracking_no))
ord_row = cur_order.fetchone()
if ord_row:
mall = ord_row['vendor'] or ""
receiver_name = ord_row['recipient_name'] or ""
phone = ord_row['recipient_phone'] or ord_row['recipient_mobile'] or ""
address = ord_row['address'] or ""
mall = ""
receiver_name = ""
phone = ""
address = ""
# Insert into return_receivings
cur_return.execute("""
INSERT INTO return_receivings
(receive_date, tracking_no, mall, receiver_name, phone, address, remarks, receive_status)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (today, tracking_no, mall, receiver_name, phone, address, remarks, "환불 대기"))
success_count += 1
# 1. Check return_requests
cur_return.execute("SELECT mall, receiver_name, phone, address, request_type, remarks FROM return_requests WHERE TRIM(tracking_no) = %s", (tracking_no,))
req_row = cur_return.fetchone()
if req_row:
mall = req_row['mall'] or ""
receiver_name = req_row['receiver_name'] or ""
phone = req_row['phone'] or ""
address = req_row['address'] or ""
req_type = req_row['request_type'] or ""
req_remarks = req_row['remarks'] or ""
remarks = f"{req_type}/{req_remarks}" if req_remarks else req_type
else:
remarks = "고객이 반품"
# 2. Check orders
cur_order.execute(ORDER_LOOKUP_SQL, (tracking_no, tracking_no))
ord_row = cur_order.fetchone()
if ord_row:
mall = ord_row['vendor'] or ""
receiver_name = ord_row['recipient_name'] or ""
phone = ord_row['recipient_phone'] or ord_row['recipient_mobile'] or ""
address = ord_row['address'] or ""
conn_return.commit()
conn_return.close()
conn_order.close()
cur_return.execute("""
INSERT INTO return_receivings
(receive_date, tracking_no, mall, receiver_name, phone, address, remarks, receive_status)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (today, tracking_no, mall, receiver_name, phone, address, remarks, "환불 대기"))
success_count += 1
return {"success": True, "count": success_count}
return {"success": True, "count": success_count, "duplicates": duplicates}
except Exception as e:
print(f"Bulk insert error: {e}")
raise HTTPException(status_code=500, detail=str(e))
@router.post("/receive")
async def create_return_receive(req: ReturnReceive):
def create_return_receive(req: ReturnReceive):
try:
req.tracking_no = req.tracking_no.strip()
conn = get_return_db_conn()
cur = conn.cursor()
# 중복 송장 검사
if req.id:
cur.execute("""
SELECT receive_date, receiver_name, phone, mall, receive_status, remarks
FROM return_receivings
WHERE TRIM(tracking_no) = %s AND id != %s
""", (req.tracking_no, req.id))
else:
cur.execute("""
SELECT receive_date, receiver_name, phone, mall, receive_status, remarks
FROM return_receivings
WHERE TRIM(tracking_no) = %s
""", (req.tracking_no,))
dup = cur.fetchone()
if dup:
conn.close()
return {
"success": False,
"error_type": "duplicate",
"data": dict(dup)
}
if not req.id:
cur.execute("SELECT request_type, remarks FROM return_requests WHERE TRIM(tracking_no) = %s", (req.tracking_no,))
req_row = cur.fetchone()
if not req_row:
if req.remarks:
if "고객이 반품" not in req.remarks:
req.remarks = "고객이 반품 / " + req.remarks
else:
req.remarks = "고객이 반품"
else:
req_type = req_row['request_type'] or ""
req_remarks = req_row['remarks'] or ""
auto_remark = f"{req_type}/{req_remarks}" if req_remarks else req_type
if not req.remarks:
req.remarks = auto_remark
elif auto_remark not in req.remarks:
req.remarks = f"{auto_remark} / {req.remarks}"
if req.id:
# Update existing record
cur.execute("""
UPDATE return_receivings
SET receive_date=%s, tracking_no=%s, mall=%s, receiver_name=%s, phone=%s, address=%s, remarks=%s, receive_status=%s
WHERE id=%s
""", (req.receive_date, req.tracking_no, req.mall, req.receiver_name, req.phone, req.address, req.remarks, req.receive_status, req.id))
else:
# Insert new record
cur.execute("""
INSERT INTO return_receivings
(receive_date, tracking_no, mall, receiver_name, phone, address, remarks, receive_status)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (req.receive_date, req.tracking_no, req.mall, req.receiver_name, req.phone, req.address, req.remarks, req.receive_status))
conn.commit()
conn.close()
return {"success": True}
with closing(get_return_db_conn()) as conn, conn, conn.cursor() as cur:
return _save_return_receive(cur, req)
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
def _save_return_receive(cur, req):
# 중복 송장 검사
if req.id:
cur.execute("""
SELECT receive_date, receiver_name, phone, mall, receive_status, remarks
FROM return_receivings
WHERE TRIM(tracking_no) = %s AND id != %s
""", (req.tracking_no, req.id))
else:
cur.execute("""
SELECT receive_date, receiver_name, phone, mall, receive_status, remarks
FROM return_receivings
WHERE TRIM(tracking_no) = %s
""", (req.tracking_no,))
dup = cur.fetchone()
if dup:
return {
"success": False,
"error_type": "duplicate",
"data": dict(dup)
}
if not req.id:
cur.execute("SELECT request_type, remarks FROM return_requests WHERE TRIM(tracking_no) = %s", (req.tracking_no,))
req_row = cur.fetchone()
if not req_row:
if req.remarks:
if "고객이 반품" not in req.remarks:
req.remarks = "고객이 반품 / " + req.remarks
else:
req.remarks = "고객이 반품"
else:
req_type = req_row['request_type'] or ""
req_remarks = req_row['remarks'] or ""
auto_remark = f"{req_type}/{req_remarks}" if req_remarks else req_type
if not req.remarks:
req.remarks = auto_remark
elif auto_remark not in req.remarks:
req.remarks = f"{auto_remark} / {req.remarks}"
if req.id:
# Update existing record
cur.execute("""
UPDATE return_receivings
SET receive_date=%s, tracking_no=%s, mall=%s, receiver_name=%s, phone=%s, address=%s, remarks=%s, receive_status=%s
WHERE id=%s
""", (req.receive_date, req.tracking_no, req.mall, req.receiver_name, req.phone, req.address, req.remarks, req.receive_status, req.id))
else:
# Insert new record
cur.execute("""
INSERT INTO return_receivings
(receive_date, tracking_no, mall, receiver_name, phone, address, remarks, receive_status)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (req.receive_date, req.tracking_no, req.mall, req.receiver_name, req.phone, req.address, req.remarks, req.receive_status))
return {"success": True}
@router.get("/receive")
async def get_return_receives():
def get_return_receives(tracking_no: Optional[str] = None, all_records: bool = False):
try:
conn = get_return_db_conn()
cur = conn.cursor()
cur.execute("SELECT * FROM return_receivings ORDER BY receive_date DESC, mall ASC, id DESC LIMIT 50")
rows = cur.fetchall()
conn.close()
conditions, params = [], []
if tracking_no and tracking_no.strip():
conditions.append("regexp_replace(tracking_no, '[-[:space:]]', '', 'g') = %s")
params.append(''.join(tracking_no.replace('-', '').split()))
elif not all_records:
conditions.append("receive_date::text >= to_char(CURRENT_DATE - INTERVAL '3 months', 'YYYY-MM-DD')")
where = ' WHERE ' + ' AND '.join(conditions) if conditions else ''
with closing(get_return_db_conn()) as conn, conn.cursor() as cur:
cur.execute("SELECT * FROM return_receivings" + where + " ORDER BY receive_date DESC, mall ASC, id DESC", params)
rows = cur.fetchall()
return {"success": True, "data": [dict(r) for r in rows]}
except Exception as e:
return {"success": False, "message": str(e)}
@router.delete("/request/{item_id}")
async def delete_return_request(item_id: int):
def delete_return_request(item_id: int):
try:
conn = get_return_db_conn()
cur = conn.cursor()
cur.execute("DELETE FROM return_requests WHERE id = %s", (item_id,))
conn.commit()
conn.close()
with closing(get_return_db_conn()) as conn, conn, conn.cursor() as cur:
cur.execute("DELETE FROM return_requests WHERE id = %s", (item_id,))
return {"success": True}
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
@router.delete("/receive/{item_id}")
async def delete_return_receive(item_id: int):
def delete_return_receive(item_id: int):
try:
conn = get_return_db_conn()
cur = conn.cursor()
cur.execute("DELETE FROM return_receivings WHERE id = %s", (item_id,))
conn.commit()
conn.close()
with closing(get_return_db_conn()) as conn, conn, conn.cursor() as cur:
cur.execute("DELETE FROM return_receivings WHERE id = %s", (item_id,))
return {"success": True}
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
+107
View File
@@ -0,0 +1,107 @@
"""Explicit, one-time settings import. No Google writes or automatic overwrite."""
import argparse
import hashlib
import json
import os
import sys
from pathlib import Path
from dotenv import load_dotenv
from settings_store import (
PostgresSettingsStore, SettingsAlreadyInitialized, SettingsNotInitialized,
settings_db_config, validate_rows,
)
BASE_DIR = Path(__file__).resolve().parent
def fingerprint(rows):
encoded = json.dumps(rows, ensure_ascii=False, separators=(",", ":")).encode("utf-8")
return hashlib.sha256(encoded).hexdigest()
def save_snapshot(path, rows, source):
target = Path(path)
target.parent.mkdir(parents=True, exist_ok=True)
payload = {"format": 1, "source": source, "sha256": fingerprint(rows), "rows": rows}
# Never replace an existing backup. Restrict access on systems supporting POSIX modes.
descriptor = os.open(target, os.O_WRONLY | os.O_CREAT | os.O_EXCL, 0o600)
with os.fdopen(descriptor, "w", encoding="utf-8") as stream:
json.dump(payload, stream, ensure_ascii=False, indent=2)
stream.write("\n")
stream.flush()
os.fsync(stream.fileno())
def load_snapshot(path):
payload = json.loads(Path(path).read_text(encoding="utf-8"))
if payload.get("format") != 1 or payload.get("sha256") != fingerprint(payload["rows"]):
raise ValueError("백업 파일 형식 또는 체크섬이 올바르지 않습니다.")
validate_rows(payload["rows"])
return payload["rows"], "backup-file"
def read_google_rows():
# Reuse the application's credential loading and bounded HTTP sessions.
# Do not use get_settings_worksheet(): that helper creates a missing tab.
import main
worksheet = main.get_gspread_client().open_by_key(main.SETTINGS_SPREADSHEET_ID).worksheet("설정")
return worksheet.get_all_values(), "google:" + main.SETTINGS_SPREADSHEET_ID + ":설정"
def build_store():
return PostgresSettingsStore(settings_db_config())
def main(argv=None):
load_dotenv(BASE_DIR / ".env")
parser = argparse.ArgumentParser(description="구글 시트 '설정' 탭 → PostgreSQL 이전 / 검증 / 백업")
commands = parser.add_subparsers(dest="command", required=True)
importer = commands.add_parser("import", help="기본은 읽기 전용 점검. --apply일 때만 DB에 저장")
importer.add_argument("--apply", action="store_true")
importer.add_argument("--backup", help="--apply 필수: 이전 전 원본을 보관할 새 JSON 경로")
importer.add_argument("--input-file", help="구글 대신 이 도구가 만든 JSON 백업에서 읽기")
verifier = commands.add_parser("verify", help="원본과 DB가 같은지 읽기 전용 검증")
verifier.add_argument("--input-file")
exporter = commands.add_parser("export", help="현재 DB 설정을 새 JSON 파일로 백업")
exporter.add_argument("--output", required=True)
args = parser.parse_args(argv)
if args.command == "import" and args.apply and not args.backup:
parser.error("--apply에는 --backup 경로가 필요합니다.")
try:
if args.command == "export":
rows, version = build_store().read()
save_snapshot(args.output, rows, f"postgres:version:{version}")
print(f"DB 설정 백업 완료 (version={version}, sha256={fingerprint(rows)}).")
return 0
rows, source = load_snapshot(args.input_file) if args.input_file else read_google_rows()
config = validate_rows(rows)
print(f"원본 확인: {len(rows)}행 / {len(config.sections())}개 분류 / sha256={fingerprint(rows)}")
if args.command == "verify":
stored, version = build_store().read()
if stored != rows:
print("검증 실패: 원본과 DB 설정이 다릅니다. 전환 전에 차이를 확인해주세요.", file=sys.stderr)
return 1
print(f"원본과 DB 전체 행·셀·순서 일치 (version={version}).")
elif args.apply:
save_snapshot(args.backup, rows, source)
version = build_store().import_rows(rows, source)
print(f"DB 이전 및 트랜잭션 내 전체 데이터 검증 완료 (version={version}).")
else:
print("점검만 완료했습니다. DB·구글 시트에 쓰지 않았습니다.")
return 0
except (SettingsAlreadyInitialized, SettingsNotInitialized) as exc:
print(str(exc), file=sys.stderr)
except Exception as exc:
# Connection errors and parser errors may contain credentials or cell values.
print(f"작업 실패 ({type(exc).__name__}). 접속 환경·원본·백업 경로를 확인해주세요.", file=sys.stderr)
return 1
if __name__ == "__main__":
raise SystemExit(main())
+155
View File
@@ -0,0 +1,155 @@
"""Ordered application settings in PostgreSQL; credentials stay in the environment."""
import configparser
import os
from contextlib import closing
import psycopg2
from psycopg2.extras import Json
HEADER = ["대분류 (Section)", "항목 (Key)", "설정값 1", "설정값 2", "설정값 3", "설정값 4", "설정값 5"]
def settings_db_config():
"""Share server credentials, but never fall back to the item-code database."""
return {
"host": os.getenv("POSTGRES_HOST", "postgres-db"),
"port": int(os.getenv("POSTGRES_PORT", "5432")),
"dbname": os.getenv("SETTINGS_DB", "corm_db").strip() or "corm_db",
"user": os.getenv("POSTGRES_USER", "king"),
"password": os.getenv("POSTGRES_PASSWORD", ""),
}
class SettingsNotInitialized(RuntimeError):
pass
class SettingsConflict(RuntimeError):
pass
class SettingsAlreadyInitialized(RuntimeError):
pass
def rows_to_config(rows):
config = configparser.ConfigParser(allow_no_value=True)
config.optionxform = str
for row in rows:
if not row or not row[0].strip():
continue
section = row[0].strip()
if section == HEADER[0]:
continue
key = row[1].strip() if len(row) > 1 else ""
cells = [cell.strip() for cell in row[2:]]
while cells and not cells[-1]:
cells.pop()
if not config.has_section(section):
config.add_section(section)
if key:
config.set(section, key, " | ".join(cells) or None)
return config
def config_to_rows(config):
rows = [HEADER.copy()]
for section in config.sections():
items = config.items(section)
if not items:
rows.append([section, "", ""])
for key, value in items:
rows.append([section, key] if value is None else
[section, key] + [part.strip() for part in value.split("|")])
return rows
def validate_rows(rows):
if not isinstance(rows, list) or not rows or not all(
isinstance(row, list) and all(isinstance(cell, str) and "\x00" not in cell for cell in row)
for row in rows
):
raise ValueError("설정은 문자열 셀로 구성된 비어 있지 않은 행 배열이어야 합니다.")
config = rows_to_config(rows)
if not config.sections():
raise ValueError("이전할 설정이 없습니다. 원본의 '설정' 탭을 확인해주세요.")
# Also validate interpolation before committing, as the existing API uses items().
config_to_rows(config)
return config
class PostgresSettingsStore:
def __init__(self, db_config):
self.db_config = dict(db_config)
def _connect(self):
return psycopg2.connect(
**self.db_config, connect_timeout=3,
options="-c statement_timeout=5000 -c lock_timeout=3000",
)
@staticmethod
def _create_table(cursor):
cursor.execute("""
CREATE TABLE IF NOT EXISTS corm_settings (
id SMALLINT PRIMARY KEY CHECK (id = 1),
rows JSONB NOT NULL CHECK (jsonb_typeof(rows) = 'array'),
version BIGINT NOT NULL DEFAULT 1 CHECK (version > 0),
source TEXT NOT NULL,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
)
""")
def read(self):
with closing(self._connect()) as connection, connection:
with connection.cursor() as cursor:
try:
cursor.execute("SELECT rows, version FROM corm_settings WHERE id = 1")
except psycopg2.errors.UndefinedTable as exc:
raise SettingsNotInitialized("설정 DB가 없습니다. 먼저 settings_migrate.py로 이전해주세요.") from exc
record = cursor.fetchone()
if record is None:
raise SettingsNotInitialized("설정 DB가 비어 있습니다. 먼저 settings_migrate.py로 이전해주세요.")
validate_rows(record[0])
return record[0], record[1]
def write(self, rows, expected_version):
validate_rows(rows)
if not isinstance(expected_version, int) or expected_version < 1:
raise SettingsConflict("설정을 다시 읽은 후 저장해주세요.")
with closing(self._connect()) as connection, connection:
with connection.cursor() as cursor:
cursor.execute("""
UPDATE corm_settings
SET rows = %s, version = version + 1, updated_at = CURRENT_TIMESTAMP
WHERE id = 1 AND version = %s RETURNING version
""", (Json(rows, dumps=_json_dumps), expected_version))
record = cursor.fetchone()
if record is None:
raise SettingsConflict("다른 요청이 설정을 변경했습니다. 새로고침 후 다시 저장해주세요.")
return record[0]
def import_rows(self, rows, source):
"""Initialize once; DDL, insert and readback all share one transaction."""
validate_rows(rows)
with closing(self._connect()) as connection, connection:
with connection.cursor() as cursor:
self._create_table(cursor)
cursor.execute("""
INSERT INTO corm_settings (id, rows, source)
VALUES (1, %s, %s) ON CONFLICT (id) DO NOTHING RETURNING version
""", (Json(rows, dumps=_json_dumps), source))
record = cursor.fetchone()
if record is None:
raise SettingsAlreadyInitialized("이미 DB 설정이 있습니다. 기존 설정을 덮어쓰지 않았습니다.")
cursor.execute("SELECT rows FROM corm_settings WHERE id = 1")
if cursor.fetchone()[0] != rows:
raise RuntimeError("이전 후 설정 검증에 실패했습니다. 트랜잭션을 취소합니다.")
return record[0]
def _json_dumps(value):
import json
return json.dumps(value, ensure_ascii=False)
File diff suppressed because it is too large Load Diff
+31
View File
@@ -0,0 +1,31 @@
.manual-grid-panel { display:flex; flex-direction:column; width:100%; min-width:0; height:100%; padding:16px; gap:10px; box-sizing:border-box; background:#f8fafc; }
.manual-grid-toolbar,.manual-grid-actions,.manual-grid-info { display:flex; align-items:center; gap:10px; flex-wrap:wrap; }
.manual-grid-toolbar { justify-content:space-between; }
.manual-grid-toolbar strong { font-size:18px; color:#1e293b; }
.manual-grid-actions .btn { padding:7px 12px; font-size:13px; }
.manual-muted,.manual-grid-info { color:#64748b; font-size:12px; }
.manual-grid-info > :first-child { margin-right:auto; }
#manual-save-status[data-state="error"] { color:#b91c1c; font-weight:600; }
#manual-save-status[data-state="saving"] { color:#1d4ed8; }
#manual-save-retry { border:1px solid #fecaca; background:#fff1f2; padding:3px 9px; border-radius:4px; cursor:pointer; }
.manual-grid-scroll { flex:1; min-height:200px; overflow:auto; background:white; border:1px solid #cbd5e1; border-radius:8px; }
table.manual-grid { border-collapse:separate; border-spacing:0; table-layout:auto; width:max-content; margin:0; font-size:13px; color:#1e293b; }
.manual-grid th,.manual-grid td { box-sizing:border-box; padding:4px 9px; border:0; border-right:1px solid #e2e8f0; border-bottom:1px solid #e2e8f0; text-align:left; vertical-align:middle; white-space:pre; overflow-wrap:normal; line-height:1.4; background:white; }
.manual-grid th { position:sticky; z-index:2; top:24px; background:#f1f5f9; font-weight:600; text-align:center; padding:6px 9px; }
.manual-grid thead tr:first-child th { top:0; height:24px; padding:1px 9px; text-align:center; color:#64748b; font-size:11px; background:#e9eef5; }
.manual-grid td:first-child,.manual-grid th:first-child { position:sticky; left:0; background:#f1f5f9; color:#64748b; text-align:center; z-index:1; white-space:nowrap; }
.manual-grid th:first-child { z-index:3; }
.manual-row-index { display:flex; align-items:center; justify-content:center; gap:6px; cursor:pointer; }
.manual-grid input[type="checkbox"] { display:block; width:14px; height:14px; margin:0; flex-shrink:0; }
.manual-grid th input[type="checkbox"] { margin:0 auto; }
.manual-grid td[contenteditable]:focus { outline:2px solid #2563eb; outline-offset:-2px; background:#eff6ff; }
.manual-grid td[data-dirty="true"] { background:#fffbeb; }
.manual-grid td[data-error="true"] { background:#fff1f2; box-shadow:inset 0 -2px #e11d48; }
.manual-grid tbody tr:has(input:checked) td { background:#eff6ff; }
#manual-grid-empty { color:#64748b; padding:36px; text-align:center; }
#manual-exports { position:relative; font-size:13px; }
#manual-exports summary { cursor:pointer; padding:7px; }
#manual-export-list { position:absolute; right:0; top:25px; min-width:270px; max-height:300px; overflow:auto; background:white; box-shadow:0 8px 24px #0f172a26; border:1px solid #e2e8f0; border-radius:6px; padding:10px; list-style:none; z-index:5; }
#manual-export-list li { padding:6px; }
#manual-export-list a { color:#1d4ed8; text-decoration:none; }
@media(max-width:800px) { .manual-grid-panel { padding:8px; } .manual-grid-actions { gap:5px; } }
+9
View File
@@ -0,0 +1,9 @@
.return-history-search { flex: 1 1 250px; min-width: 0; margin-left: auto; }
.return-history-search > label { display: block; font-size: .8rem; font-weight: 600; margin-bottom: 3px; }
.return-history-controls { display: flex; align-items: center; gap: 5px; }
.return-history-controls input { flex: 1; min-width: 0; width: 100%; height: 28px; padding: 3px 7px; border: 1px solid #94a3b8; border-radius: 4px; font-size: .8rem; box-sizing: border-box; }
.return-history-controls button { flex: 0 0 auto; height: 28px; padding: 0 9px; border: 0; border-radius: 4px; background: #334155; color: white; font-size: .78rem; cursor: pointer; }
.return-history-controls .return-history-reset { background: #e2e8f0; color: #334155; }
.return-history-search > [role="status"] { display: block; min-height: 16px; margin-top: 3px; font-size: .7rem; color: #475569; }
.return-history-heading { display: flex; align-items: center; gap: 12px; }
.return-history-heading label { display: inline-flex; align-items: center; gap: 4px; margin: 0; font-weight: normal; cursor: pointer; }
+168 -68
View File
@@ -12,13 +12,30 @@
const response = await origFetch(input, init);
if (response.status === 401) {
// 세션 만료 — dbx-main 로그인으로 위임 (next 로 현재 위치 보존)
const next = encodeURIComponent((APP_BASE_PATH || '') + window.location.pathname + window.location.search);
// pathname 에 이미 /corm 이 들어 있으므로 APP_BASE_PATH 를 다시 붙이지 않는다.
const next = encodeURIComponent(window.location.pathname + window.location.search);
window.location.href = (APP_BASE_PATH || '') + '/login?next=' + next;
}
return response;
};
})();
async function settingsErrorMessage(response) {
const fallback = response.status === 409
? '다른 요청이 설정을 변경했습니다. 새로고침 후 다시 저장해주세요.'
: '설정을 저장하지 못했습니다. 새로고침하여 저장 여부를 확인해주세요.';
const data = await response.json().catch(() => ({}));
return typeof data.detail === 'string' ? data.detail : fallback;
}
async function readHistoryResponse(response) {
const data = await response.json().catch(() => ({}));
if (!response.ok) {
throw new Error(typeof data.detail === 'string' ? data.detail : data.detail?.message || '내역 처리를 확인하지 못했습니다. 새로고침해주세요.');
}
return data;
}
function showToast(message) {
const toast = document.createElement('div');
toast.textContent = message;
@@ -82,6 +99,16 @@ function formatShortDate(dateStr) {
return `${m}월 ${day}일(${dow})`;
}
// 서버·쇼핑몰에서 온 값(고객명, 주소, 비고 등)을 innerHTML에 넣기 전에 반드시 거친다.
function escapeHtml(str) {
return String(str == null ? '' : str)
.replace(/&/g, '&amp;')
.replace(/</g, '&lt;')
.replace(/>/g, '&gt;')
.replace(/"/g, '&quot;')
.replace(/'/g, '&#39;');
}
document.addEventListener('DOMContentLoaded', () => {
const ORDER_TYPE_LABELS = { noolak: '누락', pason: '파손', bullyang: '불량', misdelivery: '오배송', normal: '수동발주', ellen: '엘렌' };
const SPECIAL_ORDER_TYPES = new Set(['noolak', 'pason', 'bullyang', 'misdelivery']);
@@ -406,15 +433,6 @@ document.addEventListener('DOMContentLoaded', () => {
}
}
function escapeHtml(str) {
return String(str == null ? '' : str)
.replace(/&/g, '&amp;')
.replace(/</g, '&lt;')
.replace(/>/g, '&gt;')
.replace(/"/g, '&quot;')
.replace(/'/g, '&#39;');
}
function resetMilkrunResult() {
if (linesTbody) linesTbody.innerHTML = '';
if (linesInfo) linesInfo.textContent = '';
@@ -1734,7 +1752,7 @@ document.addEventListener('DOMContentLoaded', () => {
if (data.status === 'success') {
if (!url.includes('submit')) {
alert(data.message);
} else if (data.timestamp) {
} else if (data.timestamp && !data.history_saved) {
const histData = {
timestamp: data.timestamp,
customer_name: submitPayload.customer_name,
@@ -1744,12 +1762,13 @@ document.addEventListener('DOMContentLoaded', () => {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify(histData)
}).then(() => {
}).then(readHistoryResponse).then(() => {
if (typeof loadManualHistory === 'function') {
loadManualHistory();
}
});
}).catch(error => alert('발주는 완료됐지만 수동발주내역 저장을 확인하지 못했습니다. ' + error.message));
}
if (data.history_saved && typeof loadManualHistory === 'function') loadManualHistory();
// Copy the same text that is written to the sheet remarks/comparison field.
if (data.clipboard_text) {
@@ -1759,10 +1778,16 @@ document.addEventListener('DOMContentLoaded', () => {
}
});
}
if (data.cs_record_warning) {
alert(data.cs_record_warning);
} else if (data.cs_record_saved_count > 0) {
showToast('CS 기록에 발주 내용을 한 건으로 자동 저장했습니다.');
}
document.getElementById('btn-reset').click();
if (url.includes('submit')) {
const iframe = document.querySelector('#manual-tab iframe');
if (iframe) iframe.src = iframe.src;
window.manualGrid?.refresh().catch(error => alert(error.message));
}
} else {
alert("Error: " + data.detail);
@@ -1782,6 +1807,7 @@ document.addEventListener('DOMContentLoaded', () => {
document.getElementById('btn-submit-ellen').addEventListener('click', () => submitOrder('/api/order/submit', true));
document.getElementById('btn-download-sheet').addEventListener('click', () => {
if (window.manualGrid) { window.manualGrid.download().catch(error => alert(error.message)); return; }
if (!confirm("수동발주로 입력한 자료는 엑셀 파일로 저장되며, 입력된 수동발주 내용은 사라집니다.\n진행하시겠습니까?")) return;
const btn = document.getElementById('btn-download-sheet');
@@ -2171,14 +2197,14 @@ document.addEventListener('DOMContentLoaded', () => {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify(histData)
}).then(() => {
}).then(readHistoryResponse).then(() => {
writeSmsCustomerCache(stamp, {
custName: custName,
custPhone: custPhone,
custAddress: custAddress
});
loadSmsHistory();
});
}).catch(error => alert('문자내역 저장을 확인하지 못했습니다. ' + error.message));
});
@@ -2189,14 +2215,14 @@ document.addEventListener('DOMContentLoaded', () => {
function loadSmsHistory() {
const u = document.getElementById('sms-history-list');
u.innerHTML = '';
fetch('/api/sms/history').then(r => r.json()).then(data => {
fetch('/api/sms/history').then(readHistoryResponse).then(data => {
data.slice().reverse().forEach((d, idx) => {
const actualIdx = data.length - 1 - idx;
const li = document.createElement('li');
li.style.cursor = 'pointer';
li.style.padding = '4px 0';
li.style.borderBottom = '1px solid #eee';
li.onclick = () => window.loadHistoryItem(actualIdx);
li.onclick = () => window.loadHistoryItem(actualIdx, d.id);
const cachedCustomer = readSmsCustomerCache()[d.timestamp] || {};
const name = firstNonEmpty(d.custName, d.customer_name, cachedCustomer.custName, cachedCustomer.customer_name);
@@ -2221,7 +2247,7 @@ document.addEventListener('DOMContentLoaded', () => {
spanDel.style.marginLeft = '8px';
spanDel.title = '삭제';
spanDel.textContent = '❌';
spanDel.onclick = (e) => window.deleteHistory(actualIdx, e);
spanDel.onclick = (e) => window.deleteHistory(actualIdx, e, d.id);
li.appendChild(spanDel);
u.appendChild(li);
@@ -2230,7 +2256,7 @@ document.addEventListener('DOMContentLoaded', () => {
});
});
}).catch(error => { u.textContent = error.message; });
}
const SMS_CUSTOMER_CACHE_KEY = 'smsCustomerHistoryByTimestamp';
@@ -2263,7 +2289,7 @@ document.addEventListener('DOMContentLoaded', () => {
const u = document.getElementById('manual-history-list');
if (!u) return;
u.innerHTML = '';
fetch('/api/manual/history').then(r => r.json()).then(data => {
fetch('/api/manual/history').then(readHistoryResponse).then(data => {
let totalCount = data.length;
let totalAmount = 0;
data.forEach((d) => {
@@ -2301,18 +2327,22 @@ document.addEventListener('DOMContentLoaded', () => {
spanDel.textContent = '❌';
spanDel.onclick = (e) => {
e.stopPropagation();
if (confirm("🚨 [경고] 이 수동발주 기록을 삭제하시겠습니까?\n\n(구글 시트에서도 함께 삭제됩니다!)")) {
if (confirm("이 수동발주 기록을 삭제하시겠습니까?\n\n연결된 발주 시트의 주문도 함께 삭제됩니다.")) {
const btn = e.target;
btn.textContent = '⏳';
btn.style.pointerEvents = 'none';
fetch(`/api/manual/history/${encodeURIComponent(d.timestamp)}`, { method: 'DELETE' })
.then(() => {
const deleteUrl = d.id !== undefined
? `/api/manual/history/records/${d.id}`
: `/api/manual/history/${encodeURIComponent(d.timestamp)}`;
fetch(deleteUrl, { method: 'DELETE' })
.then(readHistoryResponse).then(() => {
window.loadManualHistory();
const iframe = document.querySelector('#manual-tab iframe');
if (iframe) iframe.src = iframe.src;
window.manualGrid?.refresh().catch(error => alert(error.message));
})
.catch(() => {
alert("삭제 중 오류가 발생했습니다.");
.catch(error => {
alert(error.message || "삭제 중 오류가 발생했습니다.");
btn.textContent = '❌';
btn.style.pointerEvents = 'auto';
});
@@ -2322,13 +2352,13 @@ document.addEventListener('DOMContentLoaded', () => {
u.appendChild(li);
});
}).catch(err => console.error("Error loading manual history", err));
}).catch(error => { u.textContent = error.message; });
}
window.loadHistoryItem = function (idx) {
fetch('/api/sms/history').then(r => r.json()).then(data => {
if (idx < 0 || idx >= data.length) return;
const d = data[idx];
window.loadHistoryItem = function (idx, recordId) {
fetch('/api/sms/history').then(readHistoryResponse).then(data => {
const d = recordId !== undefined ? data.find(record => record.id === recordId) : data[idx];
if (!d) { loadSmsHistory(); return; }
document.getElementById('btn-reset').click();
@@ -2423,19 +2453,22 @@ document.addEventListener('DOMContentLoaded', () => {
updateSelectedOrderItemsSummary();
});
}).catch(error => alert(error.message));
}
window.deleteHistory = function (idx, e) {
window.deleteHistory = function (idx, e, recordId) {
e.stopPropagation();
if (confirm("이 기록을 삭제하시겠습니까?")) {
fetch(`/api/sms/history/${idx}`, { method: 'DELETE' }).then(() => loadSmsHistory());
const url = recordId !== undefined ? `/api/sms/history/records/${recordId}` : `/api/sms/history/${idx}`;
fetch(url, { method: 'DELETE' }).then(readHistoryResponse)
.then(() => loadSmsHistory()).catch(error => alert(error.message));
}
}
document.getElementById('btn-sms-clear-all').addEventListener('click', () => {
if (confirm("정말로 모든 기록을 삭제하시겠습니까?")) {
fetch('/api/sms/history', { method: 'DELETE' }).then(() => loadSmsHistory());
fetch('/api/sms/history', { method: 'DELETE' }).then(readHistoryResponse)
.then(() => loadSmsHistory()).catch(error => alert(error.message));
}
@@ -2615,7 +2648,7 @@ document.addEventListener('DOMContentLoaded', () => {
buildProductTab(configData);
});
} else {
alert('저장 실패');
alert(await settingsErrorMessage(res));
}
} catch (e) {
alert('오류 발생');
@@ -2712,7 +2745,7 @@ document.addEventListener('DOMContentLoaded', () => {
body: JSON.stringify({ [BULK_TRACKING_PREFIX_SETTING]: prefix })
});
if (!res.ok) throw new Error('Failed to save bulk tracking prefix');
if (!res.ok) throw new Error(await settingsErrorMessage(res));
if (!configData.APP_SETTINGS) configData.APP_SETTINGS = {};
configData.APP_SETTINGS[BULK_TRACKING_PREFIX_SETTING] = prefix;
if (btnBulkPrefixSave) btnBulkPrefixSave.style.backgroundColor = '#4a5568';
@@ -2750,7 +2783,7 @@ document.addEventListener('DOMContentLoaded', () => {
await saveBulkTrackingPrefix();
} catch (err) {
console.error('Bulk prefix save failed:', err);
alert('송장 앞 숫자 저장에 실패했습니다.');
alert(err.message || '송장 앞 숫자 저장에 실패했습니다.');
}
});
}
@@ -2783,7 +2816,7 @@ document.addEventListener('DOMContentLoaded', () => {
savedPrefix = await saveBulkTrackingPrefix({ silent: true });
} catch (err) {
console.error('Bulk prefix save failed:', err);
alert('송장 앞 숫자 저장에 실패했습니다.');
alert(err.message || '송장 앞 숫자 저장에 실패했습니다.');
return;
}
@@ -2812,6 +2845,9 @@ document.addEventListener('DOMContentLoaded', () => {
if (res.ok) {
const data = await res.json();
if (data.duplicates && data.duplicates.length) {
alert(`${data.count}건 등록했습니다.\n이미 입고된 송장 ${data.duplicates.length}건은 건너뛰었습니다:\n${data.duplicates.join('\n')}`);
}
bulkReceiveModal.style.display = 'none';
bulkTrackingNumbers.value = '';
updateBulkTrackingLineNumbers();
@@ -2834,30 +2870,48 @@ document.addEventListener('DOMContentLoaded', () => {
}
// Load Lists
const returnHistoryQueries = { req: '', rec: '' };
const returnHistoryVersions = { req: 0, rec: 0 };
function returnHistoryUrl(kind, endpoint) {
const params = new URLSearchParams();
if (returnHistoryQueries[kind]) params.set('tracking_no', returnHistoryQueries[kind]);
if (document.getElementById(`${kind}-history-all`)?.checked) params.set('all_records', 'true');
return `/api/return/${endpoint}?${params}`;
}
function returnHistoryStatus(kind, count, error = '') {
const status = document.getElementById(`${kind}-history-status`);
if (!status) return;
const scope = returnHistoryQueries[kind] ? '전체 기록 검색' :
(document.getElementById(`${kind}-history-all`)?.checked ? '전체 기록' : '최근 3개월');
status.textContent = error || `${scope} · ${count}건`;
}
async function loadReturnRequests() {
const tbody = document.getElementById('tbody-return-requests');
if(!tbody) return;
const version = ++returnHistoryVersions.req;
try {
const res = await fetch('/api/return/request');
const recRes = await fetch('/api/return/receive');
if(res.ok && recRes.ok) {
const res = await fetch(returnHistoryUrl('req', 'request'));
if (!res.ok) throw new Error('신청 목록을 불러오지 못했습니다.');
if(res.ok) {
const data = await res.json();
const recData = await recRes.json();
const receivedTrackingNos = new Set((recData.data || []).map(r => r.tracking_no).filter(t => t));
if (version !== returnHistoryVersions.req) return;
if (!data.success) throw new Error('신청 목록을 불러오지 못했습니다.');
tbody.innerHTML = '';
if(data.data) {
const excludeReceived = document.getElementById('chk-exclude-received')?.checked;
let displayData = data.data;
if (excludeReceived) {
displayData = displayData.filter(i => !(i.tracking_no && receivedTrackingNos.has(i.tracking_no)));
if (excludeReceived && !returnHistoryQueries.req) {
displayData = displayData.filter(i => !i.is_received);
}
returnHistoryStatus('req', displayData.length);
if (!displayData.length) tbody.innerHTML = '<tr><td colspan="8" style="padding:20px;text-align:center">해당하는 신청 내역이 없습니다.</td></tr>';
displayData.forEach(item => {
const isReceived = item.tracking_no && receivedTrackingNos.has(item.tracking_no);
const isReceived = item.is_received;
const tr = document.createElement('tr');
tr.style.cursor = 'pointer';
let displayRemarks = item.remarks || '';
let displayRemarks = escapeHtml(item.remarks || '');
if (isReceived) {
tr.style.textDecoration = 'line-through';
tr.style.color = '#a0aec0';
@@ -2879,16 +2933,16 @@ document.addEventListener('DOMContentLoaded', () => {
if (item.request_date && !item.request_date.startsWith(todayStr)) {
tr.style.backgroundColor = '#cbd5e0';
}
const rowDataJson = JSON.stringify(item).replace(/'/g, "&apos;").replace(/"/g, "&quot;");
const rowDataJson = escapeHtml(JSON.stringify(item));
tr.innerHTML = `
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; width: 30px;" onclick="event.stopPropagation();">
<input type="checkbox" class="chk-req-row" data-item="${rowDataJson}" ${isReceived ? 'disabled' : ''}>
</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 100px;">${formatShortDate(item.request_date)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; cursor: pointer; white-space: nowrap; width: 90px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('이름이 복사되었습니다.');" title="클릭하여 복사">${item.receiver_name}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; cursor: pointer; white-space: nowrap; width: 130px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('연락처가 복사되었습니다.');" title="클릭하여 복사">${item.phone}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; cursor: pointer; white-space: nowrap; width: 110px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('송장번호가 복사되었습니다.');" title="클릭하여 복사">${item.tracking_no}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 65px;">${item.request_type}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 100px;">${escapeHtml(formatShortDate(item.request_date))}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; cursor: pointer; white-space: nowrap; width: 90px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('이름이 복사되었습니다.');" title="클릭하여 복사">${escapeHtml(item.receiver_name)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; cursor: pointer; white-space: nowrap; width: 130px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('연락처가 복사되었습니다.');" title="클릭하여 복사">${escapeHtml(item.phone)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; cursor: pointer; white-space: nowrap; width: 110px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('송장번호가 복사되었습니다.');" title="클릭하여 복사">${escapeHtml(item.tracking_no)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 65px;">${escapeHtml(item.request_type)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; width: 215px;">${displayRemarks}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; width: 40px;" onclick="event.stopPropagation();">
<button class="btn-delete-req" data-id="${item.id}" style="background:none; border:none; color:red; cursor:pointer;">✕</button>
@@ -2922,24 +2976,35 @@ document.addEventListener('DOMContentLoaded', () => {
});
}
}
} catch(e) {}
} catch(e) {
if (version === returnHistoryVersions.req) {
tbody.replaceChildren();
returnHistoryStatus('req', 0, e.message);
}
}
}
async function loadReturnReceivings() {
const tbody = document.getElementById('tbody-return-receivings');
if(!tbody) return;
const version = ++returnHistoryVersions.rec;
try {
const res = await fetch('/api/return/receive');
const res = await fetch(returnHistoryUrl('rec', 'receive'));
if (!res.ok) throw new Error('입고 목록을 불러오지 못했습니다.');
if(res.ok) {
const data = await res.json();
if (version !== returnHistoryVersions.rec) return;
if (!data.success) throw new Error('입고 목록을 불러오지 못했습니다.');
tbody.innerHTML = '';
if(data.data) {
const excludeCompleted = document.getElementById('chk-exclude-completed')?.checked;
let displayData = data.data;
if (excludeCompleted) {
if (excludeCompleted && !returnHistoryQueries.rec) {
displayData = displayData.filter(i => i.receive_status !== '환불완료' && i.receive_status !== '교환완료');
}
returnHistoryStatus('rec', displayData.length);
if (!displayData.length) tbody.innerHTML = '<tr><td colspan="8" style="padding:20px;text-align:center">해당하는 입고 내역이 없습니다.</td></tr>';
displayData.forEach(item => {
const tr = document.createElement('tr');
tr.style.cursor = 'pointer';
@@ -2963,13 +3028,13 @@ document.addEventListener('DOMContentLoaded', () => {
const isCompleted = (item.receive_status === '환불완료' || item.receive_status === '교환완료');
const statusStyle = isCompleted ? 'color: #e53e3e; font-weight: bold;' : '';
tr.innerHTML = `
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 100px;">${formatShortDate(item.receive_date)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; cursor: pointer; white-space: nowrap; width: 90px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('이름이 복사되었습니다.');" title="클릭하여 복사">${item.receiver_name}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; cursor: pointer; white-space: nowrap; width: 130px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('연락처가 복사되었습니다.');" title="클릭하여 복사">${item.phone}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; cursor: pointer; white-space: nowrap; width: 110px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('송장번호가 복사되었습니다.');" title="클릭하여 복사">${item.tracking_no}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 85px;">${item.mall || ''}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 65px; ${statusStyle}">${item.receive_status}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; width: 160px;">${(item.remarks || '').replace(/맞교환/g, `<span style=\"color: red;\">맞교환</span>`)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 100px;">${escapeHtml(formatShortDate(item.receive_date))}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; cursor: pointer; white-space: nowrap; width: 90px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('이름이 복사되었습니다.');" title="클릭하여 복사">${escapeHtml(item.receiver_name)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; cursor: pointer; white-space: nowrap; width: 130px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('연락처가 복사되었습니다.');" title="클릭하여 복사">${escapeHtml(item.phone)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; cursor: pointer; white-space: nowrap; width: 110px;" onclick="navigator.clipboard.writeText(this.innerText); showToast('송장번호가 복사되었습니다.');" title="클릭하여 복사">${escapeHtml(item.tracking_no)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 85px;">${escapeHtml(item.mall)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; white-space: nowrap; width: 65px; ${statusStyle}">${escapeHtml(item.receive_status)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: left; width: 160px;">${escapeHtml(item.remarks).replace(/맞교환/g, `<span style=\"color: red;\">맞교환</span>`)}</td>
<td style="padding: 8px; border-bottom: 1px solid #e2e8f0; text-align: center; width: 40px;" onclick="event.stopPropagation();">
<button class="btn-delete-rec" data-id="${item.id}" style="background:none; border:none; color:red; cursor:pointer;">✕</button>
</td>
@@ -2993,10 +3058,45 @@ document.addEventListener('DOMContentLoaded', () => {
});
}
}
} catch(e) {}
} catch(e) {
if (version === returnHistoryVersions.rec) {
tbody.replaceChildren();
returnHistoryStatus('rec', 0, e.message);
}
}
}
// Initial load
for (const [kind, reload, excludeId] of [
['req', loadReturnRequests, 'chk-exclude-received'],
['rec', loadReturnReceivings, 'chk-exclude-completed']
]) {
const input = document.getElementById(`${kind}-history-tracking`);
const all = document.getElementById(`${kind}-history-all`);
const search = () => {
const query = input.value.replace(/[-\s]/g, '');
if (!query) { returnHistoryStatus(kind, 0, '원송장번호를 입력해주세요.'); input.focus(); return; }
returnHistoryQueries[kind] = query;
document.getElementById(excludeId).checked = false;
reload();
};
document.getElementById(`${kind}-history-search`)?.addEventListener('click', search);
input?.addEventListener('keydown', event => {
if (event.key === 'Enter') { event.preventDefault(); search(); }
});
document.getElementById(`${kind}-history-reset`)?.addEventListener('click', () => {
input.value = '';
returnHistoryQueries[kind] = '';
all.checked = false;
document.getElementById(excludeId).checked = false;
reload();
});
all?.addEventListener('change', () => {
input.value = '';
returnHistoryQueries[kind] = '';
reload();
});
}
const returnTabMenu = document.querySelector('[data-tab="return-tab"]');
const chkExcludeCompleted = document.getElementById('chk-exclude-completed');
@@ -3449,8 +3549,8 @@ document.addEventListener('DOMContentLoaded', () => {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify(payload)
}).then(r => {
if (!r.ok) throw new Error("Network response was not ok (status " + r.status + ")");
}).then(async r => {
if (!r.ok) throw new Error(await settingsErrorMessage(r));
return r.json();
}).then(res => {
alert("상품 설정이 저장되었습니다.");
@@ -3469,7 +3569,7 @@ document.addEventListener('DOMContentLoaded', () => {
});
}).catch(err => {
console.error(err);
alert("저장 실패!");
alert(err.message || "저장 실패!");
document.getElementById('btn-product-save').innerText = '상품 설정 저장';
});
});
@@ -4188,7 +4288,7 @@ if (recFormContainer) {
// Version Update Notice
// ============================================
document.addEventListener('DOMContentLoaded', () => {
let CURRENT_APP_VERSION = "9.0";
let CURRENT_APP_VERSION = "9.1";
const versionEl = document.getElementById('app-version-text');
if (versionEl) {
const text = versionEl.textContent.trim();
File diff suppressed because it is too large Load Diff
+195
View File
@@ -0,0 +1,195 @@
(() => {
const panel = document.getElementById('manual-grid-panel');
if (!panel) return;
const table = document.getElementById('manual-grid');
const status = document.getElementById('manual-save-status');
const retry = document.getElementById('manual-save-retry');
const pending = new Map();
let rows = new Map(), headers = [], saving = null, sentKeys = new Set(), timer, failure = null, locked = false, loadGeneration = 0;
const keyOf = (id, column) => `${id}:${column}`;
const cellAt = (id, column) => table.querySelector(`td[data-id="${id}"][data-column="${column}"]`);
function message(text, state = '') { status.textContent = text; status.dataset.state = state; }
async function request(url, options) {
const response = await fetch(url, options);
if (!response.ok) {
const data = await response.json().catch(() => ({}));
const error = new Error(typeof data.detail === 'string' ? data.detail : data.detail?.message || '처리를 확인하지 못했습니다. 다시 시도해주세요.');
error.conflicts = data.detail?.conflicts;
throw error;
}
return response;
}
const jsonOptions = (method, data) => ({method, headers: {'Content-Type': 'application/json'}, body: JSON.stringify(data)});
function queueCell(td) {
const id = Number(td.dataset.id), column = Number(td.dataset.column), key = keyOf(id, column);
const value = td.textContent.replace(/\r\n/g, '\n');
const previous = pending.get(key)?.previous_value ?? rows.get(id).cells[column];
if (value === rows.get(id).cells[column] && !sentKeys.has(key)) pending.delete(key);
else pending.set(key, {id, column, value, previous_value: previous});
td.dataset.dirty = String(pending.has(key));
delete td.dataset.error;
if (!failure) message('입력 중 · 자동 저장 대기', 'saving');
clearTimeout(timer);
timer = setTimeout(() => { if (!failure) flush().catch(() => {}); }, 450);
}
function applyRows(updated, keys) {
for (const row of updated) {
rows.set(row.id, row);
row.cells.forEach((value, column) => {
const key = keyOf(row.id, column), queued = pending.get(key), td = cellAt(row.id, column);
if (queued && keys.has(key)) queued.previous_value = value;
if (td && !queued) { if (td.textContent !== value) td.textContent = value; delete td.dataset.dirty; delete td.dataset.error; }
});
}
}
function flush() {
clearTimeout(timer);
if (saving) return saving;
if (failure) return Promise.reject(failure);
saving = (async () => {
while (pending.size) {
const batch = [...pending.values()].slice(0, 1000);
sentKeys = new Set(batch.map(c => keyOf(c.id, c.column)));
for (const key of sentKeys) pending.delete(key);
message('저장 중…', 'saving');
try {
const result = await (await request('/api/manual/orders/cells', jsonOptions('PATCH', {changes: batch}))).json();
applyRows(result.rows, sentKeys);
} catch (error) {
for (const change of batch) {
const key = keyOf(change.id, change.column), newer = pending.get(key);
pending.set(key, {...change, value: newer?.value ?? change.value});
const td = cellAt(change.id, change.column);
if (td) td.dataset.error = 'true';
}
failure = error;
retry.hidden = false;
message('저장하지 못한 입력이 있습니다 · ' + error.message, 'error');
throw error;
} finally { sentKeys = new Set(); }
}
message('모든 변경사항 저장됨');
retry.hidden = true;
})().finally(() => { saving = null; });
return saving;
}
retry.addEventListener('click', () => {
if (failure?.conflicts?.length) {
const preview = failure.conflicts.slice(0, 3).map(c => `${headers[c.column]}: 최신값 「${c.value}」 / 내 입력 「${pending.get(keyOf(c.id, c.column))?.value ?? ''}」`).join('\n');
const keepMine = confirm(preview + '\n\n확인: 내 입력으로 변경\n취소: 충돌한 셀은 최신값 사용');
for (const conflict of failure.conflicts) {
const key = keyOf(conflict.id, conflict.column), change = pending.get(key), td = cellAt(conflict.id, conflict.column);
rows.get(conflict.id).cells[conflict.column] = conflict.value;
if (keepMine && change) change.previous_value = conflict.value;
else { pending.delete(key); if (td) { td.textContent = conflict.value; delete td.dataset.dirty; delete td.dataset.error; } }
}
}
failure = null;
flush().catch(() => {});
});
function columnLetter(index) { let out = ''; for (let n = index + 1; n; n = Math.floor((n - 1) / 26)) out = String.fromCharCode(65 + (n - 1) % 26) + out; return out; }
function render(data) {
headers = data.headers;
rows = new Map(data.rows.map(row => [row.id, row]));
const head = table.tHead, body = table.tBodies[0];
head.replaceChildren(); body.replaceChildren();
const letters = head.insertRow(), titles = head.insertRow();
for (const [i, title] of ['', ...headers].entries()) {
const letter = document.createElement('th'), label = document.createElement('th');
letter.textContent = i ? columnLetter(i - 1) : '';
if (i) label.textContent = title;
else { const all = document.createElement('input'); all.type = 'checkbox'; all.setAttribute('aria-label', '모든 행 선택'); all.onchange = () => body.querySelectorAll('input').forEach(box => { box.checked = all.checked; }); label.append(all); }
letters.append(letter); titles.append(label);
}
data.rows.forEach((row, index) => {
const tr = body.insertRow(), number = tr.insertCell(), check = document.createElement('input');
check.type = 'checkbox'; check.dataset.rowId = row.id; check.setAttribute('aria-label', `${index + 1}행 선택`);
const rowIndex = document.createElement('label'), rowNumber = document.createElement('span');
rowIndex.className = 'manual-row-index'; rowNumber.textContent = index + 1;
rowIndex.append(check, rowNumber); number.append(rowIndex);
row.cells.forEach((value, column) => {
const td = tr.insertCell(); td.textContent = value; td.contentEditable = 'plaintext-only'; td.tabIndex = 0;
td.dataset.id = row.id; td.dataset.column = column; td.setAttribute('aria-label', `${index + 1}행 ${headers[column] || columnLetter(column)}`);
td.addEventListener('input', event => { if (!event.isComposing) queueCell(td); });
td.addEventListener('compositionend', () => queueCell(td));
td.addEventListener('blur', () => { if (!failure) flush().catch(() => {}); });
td.addEventListener('keydown', event => {
if (event.isComposing) return;
if (event.key === 'Enter' && event.altKey) return;
if (event.key === 'Tab' || event.key === 'Enter') {
event.preventDefault();
const ids = [...rows.keys()], rowIndex = ids.indexOf(row.id);
let nextRow = rowIndex, nextColumn = column;
if (event.key === 'Enter') nextRow += event.shiftKey ? -1 : 1;
else nextColumn += event.shiftKey ? -1 : 1;
if (nextColumn >= headers.length) { nextColumn = 0; nextRow++; }
if (nextColumn < 0) { nextColumn = headers.length - 1; nextRow--; }
cellAt(ids[nextRow], nextColumn)?.focus();
}
if (event.key === 'Escape') { td.textContent = rows.get(row.id).cells[column]; queueCell(td); td.blur(); }
});
td.addEventListener('paste', event => {
event.preventDefault();
const text = event.clipboardData.getData('text/plain').replace(/\r\n/g, '\n').replace(/\n$/, '');
const matrix = text.split('\n').map(line => line.split('\t')), ids = [...rows.keys()], start = ids.indexOf(row.id);
if (matrix.length + start > ids.length || matrix.some(line => line.length + column > headers.length) || matrix.reduce((n, line) => n + line.length, 0) > 1000) {
alert('붙여넣을 범위를 확인해주세요. 필요한 행을 먼저 추가하고 한 번에 1,000셀 이내로 붙여넣으세요.'); return;
}
matrix.forEach((line, r) => line.forEach((text, c) => { const target = cellAt(ids[start + r], column + c); target.textContent = text; queueCell(target); }));
});
});
});
document.getElementById('manual-row-count').textContent = `${data.rows.length.toLocaleString()}행`;
document.getElementById('manual-grid-empty').hidden = data.rows.length !== 0;
}
async function load() {
if (pending.size || saving || locked) return;
const generation = ++loadGeneration;
const data = await (await request('/api/manual/orders')).json();
if (generation !== loadGeneration || pending.size || saving || locked || table.contains(document.activeElement)) return;
render(data); message('모든 변경사항 저장됨');
}
async function action(work) {
if (locked) return;
locked = true;
loadGeneration++;
table.querySelectorAll('[contenteditable]').forEach(td => { td.contentEditable = 'false'; });
try { await flush(); await work(); }
finally { locked = false; table.querySelectorAll('[contenteditable]').forEach(td => { td.contentEditable = 'plaintext-only'; }); }
await load();
}
async function download() {
if (!confirm('현재 발주를 엑셀로 내려받고 표를 비우시겠습니까? 내려받은 자료는 다운로드 기록에 보관됩니다.')) return;
await action(async () => {
const response = await request('/api/sheet/download_and_clear', {method: 'POST'});
const blob = await response.blob(), a = document.createElement('a');
a.href = URL.createObjectURL(blob);
a.download = decodeURIComponent(response.headers.get('Content-Disposition')?.split("UTF-8''")[1] || '수동발주.xlsx');
document.body.append(a); a.click(); a.remove(); setTimeout(() => URL.revokeObjectURL(a.href), 30000);
});
await loadExports();
}
async function loadExports() {
const list = document.getElementById('manual-export-list');
const exports = await (await request('/api/manual/orders/exports')).json(); list.replaceChildren();
for (const item of exports) { const li = document.createElement('li'), a = document.createElement('a'); a.textContent = item.filename; a.href = (window.APP_BASE_PATH || '') + '/api/manual/orders/exports/' + item.id; li.append(a); list.append(li); }
if (!exports.length) list.textContent = '아직 다운로드 기록이 없습니다.';
}
document.getElementById('manual-add-row').onclick = () => action(async () => { await request('/api/manual/orders', {method: 'POST'}); }).catch(error => message(error.message, 'error'));
document.getElementById('manual-delete-rows').onclick = () => {
const selected = [...table.querySelectorAll('input[data-row-id]:checked')].map(box => rows.get(Number(box.dataset.rowId)));
if (!selected.length || !confirm(`${selected.length}개 행을 표에서 삭제하시겠습니까?`)) return;
action(async () => { await request('/api/manual/orders/archive', jsonOptions('POST', {rows: selected.map(({id}) => ({id, version: rows.get(id).version}))})); }).catch(error => message(error.message, 'error'));
};
document.getElementById('manual-refresh').onclick = async () => {
try { if (failure && confirm('저장하지 못한 입력이 있습니다. 필요한 내용을 복사해 두셨나요? 확인하면 미저장 입력을 버리고 새로 불러옵니다.')) { pending.clear(); failure = null; retry.hidden = true; } else await flush(); document.activeElement?.blur(); await load(); }
catch (error) { message(error.message, 'error'); }
};
document.getElementById('manual-download').onclick = () => download().catch(error => message(error.message + ' · 다운로드 기록도 확인해주세요.', 'error'));
document.getElementById('manual-exports').ontoggle = event => { if (event.target.open) loadExports().catch(error => message(error.message, 'error')); };
window.manualGrid = {flush, refresh: async () => { await flush(); await load(); }, download};
window.addEventListener('beforeunload', event => { if (pending.size || saving) { event.preventDefault(); event.returnValue = ''; } });
document.querySelector('[data-tab="manual-tab"]')?.addEventListener('click', () => load().catch(error => message(error.message, 'error')));
setInterval(() => { if (document.getElementById('manual-tab').classList.contains('active')) load().catch(error => message(error.message, 'error')); }, 10000);
load().catch(error => message(error.message, 'error'));
})();
+6 -5
View File
@@ -1,7 +1,8 @@
// 쿠팡 밀크런 캘린더
//
// 화면이 열리면 구글 드라이브의 밀크런 출고리스트를 자동으로 읽어
// 시트 이름(YYYYMMDD)을 달력에 표시한다.
// 밀크런 탭을 열었을 때만 구글 드라이브의 밀크런 출고리스트를 읽어
// 시트 이름(YYYYMMDD)을 달력에 표시한다. 메인 화면 로딩 중에는
// 구글 시트 조회나 분석 요청을 실행하지 않는다.
// 달력에서 날짜를 고르면 그 시트의 제품코드/제품명/수량을 읽어
// 붙여넣기 입력을 채우고 곧바로 분석까지 실행한다.
// (시트 주소는 서버에 설정되어 있어 화면에서 입력하지 않는다)
@@ -356,8 +357,8 @@
});
}
// 달력 틀을 먼저 보여주고(덮개가 '읽는 중'을 표시), 출고일은 뒤이어 채운다
renderCalendar();
loadSheetTabs(false);
// 초기 화면에서는 달력 틀만 준비한다. 실제 조회와 분석은 위의
// 밀크런 메뉴 클릭 이벤트에서만 시작한다.
renderCalendar('밀크런 탭을 열면 출고일을 불러옵니다');
});
})();
+302 -7
View File
@@ -15,7 +15,10 @@
rel="stylesheet"
href="{{ root_path }}/static/css/styles.css?v=24"
/>
<script src="https://cdn.jsdelivr.net/npm/sortablejs@latest/Sortable.min.js"></script>
<link rel="stylesheet" href="{{ root_path }}/static/css/cs_records.css?v=21" />
<link rel="stylesheet" href="{{ root_path }}/static/css/return_filters.css?v=1" />
<link rel="stylesheet" href="{{ root_path }}/static/css/manual_grid.css?v=2" />
<script src="https://cdn.jsdelivr.net/npm/sortablejs@1.15.6/Sortable.min.js"></script>
<script>
// 서브경로 호스팅 정보 — JS 가 fetch 시 prefix 로 사용한다.
window.APP_BASE_PATH = "{{ root_path }}";
@@ -95,10 +98,10 @@
id="app-version-text"
style="color: #a0aec0; font-size: 0.95rem; margin-top: 4px; font-weight: normal"
>
Ver 9.0
Ver 9.1
</div>
<div style="color: #a0aec0; font-size: 0.75rem; margin-top: 4px; font-weight: normal">
(2026.08.25)
(2026.09.03)
</div>
</div>
<ul class="nav-links">
@@ -109,6 +112,7 @@
<span class="nav-icon">🎧</span>CS 작업
</li>
<li class="active" data-tab="order-tab" data-order-view="new"><span class="nav-icon">✨</span>New CS</li>
<li data-tab="cs-record-tab"><span class="nav-icon">📝</span>CS 기록</li>
<li data-tab="manual-tab"><span class="nav-icon">✍️</span>수동발주</li>
<li data-tab="product-tab"><span class="nav-icon">📦</span>상품설정</li>
<li data-tab="auto-order-tab"><span class="nav-icon">🤖</span>자동발주</li>
@@ -571,6 +575,254 @@
</div>
</div>
</section>
<!-- =========================
CS RECORD TAB
========================== -->
<section id="cs-record-tab" class="tab-content cs-record-tab">
<div class="cs-record-layout">
<aside class="cs-calendar-card">
<div class="cs-calendar-header">
<button id="cs-calendar-prev" type="button" class="cs-icon-button" aria-label="이전 달">‹</button>
<h2 id="cs-calendar-title">-</h2>
<button id="cs-calendar-next" type="button" class="cs-icon-button" aria-label="다음 달">›</button>
</div>
<div class="cs-calendar-weekdays" aria-hidden="true">
<span>일</span><span>월</span><span>화</span><span>수</span><span>목</span><span>금</span><span>토</span>
</div>
<div id="cs-calendar-grid" class="cs-calendar-grid" aria-label="CS 기록 달력"></div>
<div class="cs-calendar-legend">
<span><i class="cs-legend-dot has-record"></i>기록 있음</span>
<span><i class="cs-legend-dot incomplete"></i>미완료 있음</span>
<span><i class="cs-legend-dot schedule"></i>일정 있음</span>
</div>
<section class="cs-schedule-panel" aria-labelledby="cs-schedule-list-title">
<div class="cs-schedule-primary-actions">
<button id="cs-open-schedule-modal" type="button" class="cs-schedule-add-button">+일정 등록</button>
<button id="cs-open-data-modal" type="button" class="cs-schedule-settings-button" aria-label="일정 데이터 설정" title="엑셀 내려받기·데이터 업로드">⚙️</button>
</div>
<div class="cs-schedule-list-header">
<h3 id="cs-schedule-list-title">이번 달 일정</h3>
<span id="cs-schedule-count">0개</span>
</div>
<div id="cs-schedule-list" class="cs-schedule-list"></div>
</section>
</aside>
<div class="cs-record-card">
<header class="cs-record-header">
<div>
<span class="cs-eyebrow">일별 상담·전달 기록</span>
<h1 id="cs-selected-date-title">-</h1>
<p id="cs-record-count">기록을 불러오는 중입니다.</p>
</div>
<div class="cs-record-actions">
<button id="cs-copy-selected" type="button" class="cs-copy-selected-button">체크내용 복사</button>
<button id="cs-copy-next" type="button" class="cs-copy-button">다음 업무일로 미완료 복사</button>
</div>
</header>
<div id="cs-record-status" class="cs-record-status" role="status" hidden></div>
<div class="cs-record-composer">
<textarea id="cs-record-input" rows="3" maxlength="10000" placeholder="고객 상담 내용이나 물류센터 전달 사항을 입력하세요."></textarea>
<div class="cs-composer-footer">
<span>Ctrl + Enter로 빠르게 추가</span>
<button id="cs-record-add" type="button" class="cs-primary-button">기록 추가</button>
</div>
</div>
<div id="cs-record-list" class="cs-record-list"></div>
</div>
<aside class="cs-permanent-card">
<header class="cs-permanent-header">
<div>
<span class="cs-eyebrow">날짜와 관계없이 항상 표시</span>
<h2>📌 항상 확인 메모</h2>
</div>
<button id="cs-open-memo-modal" type="button" class="cs-add-memo-button">+ 메모 추가</button>
</header>
<div class="cs-folder-toolbar">
<div id="cs-folder-tabs" class="cs-folder-tabs" role="tablist" aria-label="메모 폴더"></div>
<div class="cs-folder-actions">
<button id="cs-open-folder-add" type="button">폴더 추가</button>
<button id="cs-open-folder-delete" type="button">폴더 삭제</button>
</div>
</div>
<div id="cs-permanent-list" class="cs-permanent-list"></div>
</aside>
</div>
<div id="cs-schedule-modal" class="cs-modal" hidden>
<div class="cs-modal-dialog cs-modal-dialog-small" role="dialog" aria-modal="true" aria-labelledby="cs-schedule-modal-title">
<div class="cs-schedule-modal-heading">
<span class="cs-schedule-modal-icon" aria-hidden="true">📅</span>
<div>
<span class="cs-eyebrow">CS 캘린더</span>
<h3 id="cs-schedule-modal-title">일정 등록</h3>
</div>
</div>
<div class="cs-schedule-form-row cs-schedule-date-row">
<div>
<label for="cs-schedule-date">시작일</label>
<input id="cs-schedule-date" type="date" required />
</div>
<div>
<label for="cs-schedule-end-date">종료일</label>
<input id="cs-schedule-end-date" type="date" required />
</div>
<label class="cs-all-day-toggle" for="cs-schedule-all-day">
<input id="cs-schedule-all-day" type="checkbox" />
<span aria-hidden="true"></span>
종일
</label>
</div>
<div class="cs-schedule-form-row cs-schedule-time-row">
<div>
<label for="cs-schedule-time">시작 시간 <span class="cs-optional-label">선택</span></label>
<div id="cs-schedule-time" class="cs-time-select-group" aria-label="시작 시간">
<select id="cs-schedule-start-period" aria-label="시작 시간 오전 오후">
<option value="">선택</option><option value="AM">오전</option><option value="PM">오후</option>
</select>
<select id="cs-schedule-start-hour" aria-label="시작 시">
<option value="">시</option><option value="01">01</option><option value="02">02</option><option value="03">03</option><option value="04">04</option><option value="05">05</option><option value="06">06</option><option value="07">07</option><option value="08">08</option><option value="09">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option>
</select>
<select id="cs-schedule-start-minute" aria-label="시작 분">
<option value="">분</option><option value="00">00</option><option value="10">10</option><option value="20">20</option><option value="30">30</option><option value="40">40</option><option value="50">50</option>
</select>
</div>
</div>
<div>
<label for="cs-schedule-end-time">종료 시간 <span class="cs-optional-label">선택</span></label>
<div id="cs-schedule-end-time" class="cs-time-select-group" aria-label="종료 시간">
<select id="cs-schedule-end-period" aria-label="종료 시간 오전 오후">
<option value="">선택</option><option value="AM">오전</option><option value="PM">오후</option>
</select>
<select id="cs-schedule-end-hour" aria-label="종료 시">
<option value="">시</option><option value="01">01</option><option value="02">02</option><option value="03">03</option><option value="04">04</option><option value="05">05</option><option value="06">06</option><option value="07">07</option><option value="08">08</option><option value="09">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option>
</select>
<select id="cs-schedule-end-minute" aria-label="종료 분">
<option value="">분</option><option value="00">00</option><option value="10">10</option><option value="20">20</option><option value="30">30</option><option value="40">40</option><option value="50">50</option>
</select>
</div>
</div>
</div>
<label for="cs-schedule-title">일정 내용</label>
<input id="cs-schedule-title" type="text" maxlength="300" placeholder="예: 물류센터 재고 확인" />
<p class="cs-modal-help">시작일과 종료일을 다르게 선택하면 기간 일정이 됩니다. 종일을 체크하면 시간 없이 저장됩니다.</p>
<div class="cs-modal-actions">
<button type="button" class="cs-modal-cancel" data-cs-close="cs-schedule-modal">취소</button>
<button id="cs-schedule-save" type="button" class="cs-modal-save">일정 저장</button>
</div>
</div>
</div>
<div id="cs-schedule-delete-modal" class="cs-modal" hidden>
<div class="cs-modal-dialog cs-modal-dialog-small" role="dialog" aria-modal="true" aria-labelledby="cs-schedule-delete-title">
<h3 id="cs-schedule-delete-title">일정 삭제</h3>
<p class="cs-delete-message">이 일정을 삭제할까요?</p>
<div id="cs-schedule-delete-preview" class="cs-schedule-delete-preview"></div>
<div class="cs-modal-actions">
<button type="button" class="cs-modal-cancel" data-cs-close="cs-schedule-delete-modal">취소</button>
<button id="cs-schedule-delete-save" type="button" class="cs-modal-delete">일정 삭제</button>
</div>
</div>
</div>
<div id="cs-data-modal" class="cs-modal cs-data-modal" hidden>
<div class="cs-modal-dialog cs-modal-dialog-small" role="dialog" aria-modal="true" aria-labelledby="cs-data-modal-title">
<div class="cs-schedule-modal-heading">
<span class="cs-schedule-modal-icon" aria-hidden="true">⚙️</span>
<div>
<span class="cs-eyebrow">CS 캘린더</span>
<h3 id="cs-data-modal-title">데이터 관리</h3>
</div>
</div>
<p class="cs-data-modal-description">CS 기록, 상시 메모와 일정을 엑셀로 백업하거나 이전에 내려받은 파일을 다시 업로드할 수 있습니다.</p>
<div class="cs-data-actions">
<a id="cs-record-export" class="cs-secondary-button" href="{{ root_path }}/api/cs-records/export">엑셀 내려받기</a>
<label class="cs-secondary-button" for="cs-record-import">데이터 업로드</label>
<input id="cs-record-import" type="file" accept=".xlsx,.xls,.csv" hidden />
</div>
<p class="cs-upload-help">내려받은 엑셀 또는 날짜·내용 열이 있는 파일을 업로드할 수 있습니다.</p>
<div class="cs-modal-actions">
<button type="button" class="cs-modal-save" data-cs-close="cs-data-modal">닫기</button>
</div>
</div>
</div>
<div id="cs-memo-modal" class="cs-modal" hidden>
<div class="cs-modal-dialog" role="dialog" aria-modal="true" aria-labelledby="cs-memo-modal-title">
<h3 id="cs-memo-modal-title">메모 추가</h3>
<label for="cs-memo-folder-select">메모 폴더</label>
<select id="cs-memo-folder-select"></select>
<label for="cs-memo-content">메모 내용</label>
<textarea id="cs-memo-content" rows="7" maxlength="10000" placeholder="항상 확인할 메모 내용을 입력하세요."></textarea>
<div class="cs-modal-actions">
<button type="button" class="cs-modal-cancel" data-cs-close="cs-memo-modal">취소</button>
<button id="cs-memo-save" type="button" class="cs-modal-save">저장</button>
</div>
</div>
</div>
<div id="cs-folder-add-modal" class="cs-modal" hidden>
<div class="cs-modal-dialog cs-modal-dialog-small" role="dialog" aria-modal="true" aria-labelledby="cs-folder-add-title">
<h3 id="cs-folder-add-title">메모 폴더 추가</h3>
<label for="cs-folder-name">추가할 폴더 이름</label>
<input id="cs-folder-name" type="text" maxlength="80" placeholder="예: 물류센터 전달" />
<fieldset class="cs-color-fieldset">
<legend>폴더 색상</legend>
<div id="cs-folder-add-colors" class="cs-color-palette" aria-label="폴더 색상 선택"></div>
</fieldset>
<div class="cs-modal-actions">
<button type="button" class="cs-modal-cancel" data-cs-close="cs-folder-add-modal">취소</button>
<button id="cs-folder-add-save" type="button" class="cs-modal-save">폴더 추가</button>
</div>
</div>
</div>
<div id="cs-folder-edit-modal" class="cs-modal" hidden>
<div class="cs-modal-dialog cs-modal-dialog-small" role="dialog" aria-modal="true" aria-labelledby="cs-folder-edit-title">
<h3 id="cs-folder-edit-title">메모 폴더 설정</h3>
<label for="cs-folder-edit-name">폴더 이름</label>
<input id="cs-folder-edit-name" type="text" maxlength="80" />
<fieldset class="cs-color-fieldset">
<legend>폴더 색상</legend>
<div id="cs-folder-edit-colors" class="cs-color-palette" aria-label="폴더 색상 선택"></div>
</fieldset>
<p class="cs-modal-help">폴더 탭을 더블클릭하면 언제든지 설정을 변경할 수 있습니다.</p>
<div class="cs-modal-actions">
<button type="button" class="cs-modal-cancel" data-cs-close="cs-folder-edit-modal">취소</button>
<button id="cs-folder-edit-save" type="button" class="cs-modal-save">설정 저장</button>
</div>
</div>
</div>
<div id="cs-folder-delete-modal" class="cs-modal" hidden>
<div class="cs-modal-dialog cs-modal-dialog-small" role="dialog" aria-modal="true" aria-labelledby="cs-folder-delete-title">
<h3 id="cs-folder-delete-title">메모 폴더 삭제</h3>
<label for="cs-folder-delete-select">삭제할 폴더</label>
<select id="cs-folder-delete-select"></select>
<p class="cs-modal-help">폴더 안의 메모는 삭제되지 않고 기본 폴더로 이동합니다.</p>
<div class="cs-modal-actions">
<button type="button" class="cs-modal-cancel" data-cs-close="cs-folder-delete-modal">취소</button>
<button id="cs-folder-delete-save" type="button" class="cs-modal-delete">폴더 삭제</button>
</div>
</div>
</div>
<div id="cs-memo-delete-modal" class="cs-modal" hidden>
<div class="cs-modal-dialog cs-modal-dialog-small" role="dialog" aria-modal="true" aria-labelledby="cs-memo-delete-title">
<h3 id="cs-memo-delete-title">상시 메모 삭제</h3>
<p class="cs-delete-message">이 상시 메모를 삭제할까요?</p>
<div id="cs-memo-delete-preview" class="cs-memo-delete-preview"></div>
<div class="cs-modal-actions">
<button type="button" class="cs-modal-cancel" data-cs-close="cs-memo-delete-modal">취소</button>
<button id="cs-memo-delete-save" type="button" class="cs-modal-delete">메모 삭제</button>
</div>
</div>
</div>
</section>
<!-- =========================
AUTO ORDER TAB
========================== -->
@@ -1177,6 +1429,28 @@
class="tab-content"
style="padding: 0"
>
{% if manual_orders_backend == 'postgres' %}
<div id="manual-grid-panel" class="manual-grid-panel">
<div class="manual-grid-toolbar">
<div><strong>수동발주</strong> <span id="manual-row-count" class="manual-muted"></span></div>
<div class="manual-grid-actions">
<button type="button" id="manual-add-row" class="btn btn-secondary">+ 행 추가</button>
<button type="button" id="manual-delete-rows" class="btn btn-secondary">선택 행 삭제</button>
<button type="button" id="manual-refresh" class="btn btn-secondary">새로고침</button>
<button type="button" id="manual-download" class="btn btn-primary">엑셀로 저장</button>
<details id="manual-exports"><summary>다운로드 기록</summary><ul id="manual-export-list"></ul></details>
</div>
</div>
<div class="manual-grid-info"><span>셀을 클릭해 입력하세요 · Enter ↓ · Tab → · 여러 셀 붙여넣기</span>
<span id="manual-save-status" role="status" aria-live="polite">불러오는 중…</span>
<button type="button" id="manual-save-retry" hidden>다시 시도</button>
</div>
<div class="manual-grid-scroll" id="manual-grid-scroll" tabindex="-1">
<table id="manual-grid" class="manual-grid" aria-label="수동발주 편집 표"><thead></thead><tbody></tbody></table>
<p id="manual-grid-empty" hidden>등록된 발주가 없습니다. 발주를 입력하거나 행을 추가하세요.</p>
</div>
</div>
{% else %}
<div style="padding: 10px 10px 0 10px; display: flex; justify-content: flex-end">
<a
href="https://docs.google.com/spreadsheets/d/1QxFtjDurPPZd8NmtXBy4XEUIzkf93aXD5Mlx5kB33xw/edit?gid=0#gid=0"
@@ -1216,6 +1490,7 @@
style="border: none; border-radius: 8px; box-shadow: var(--card-shadow)"
></iframe>
</div>
{% endif %}
</section>
<!-- =========================
GIFT SETTINGS TAB
@@ -3054,6 +3329,15 @@
<option value="기타">기타</option>
</select>
</div>
<div class="return-history-search">
<label for="req-history-tracking">원송장번호 검색 (전체 신청 내역)</label>
<div class="return-history-controls">
<input id="req-history-tracking" type="search" placeholder="원송장번호 입력" autocomplete="off" />
<button id="req-history-search" type="button">검색</button>
<button id="req-history-reset" type="button" class="return-history-reset">초기화</button>
</div>
<span id="req-history-status" role="status">최근 3개월</span>
</div>
</div>
<div
class="mobile-hide"
@@ -3157,7 +3441,7 @@
align-items: center;
"
>
<span>신청 목록</span
<span class="return-history-heading">신청 목록 <label><input id="req-history-all" type="checkbox" /> 전체 보기</label></span
><label
style="
cursor: pointer;
@@ -3430,6 +3714,15 @@
<option value="교환완료">교환완료</option>
</select>
</div>
<div class="return-history-search">
<label for="rec-history-tracking">원송장번호 검색 (전체 입고 내역)</label>
<div class="return-history-controls">
<input id="rec-history-tracking" type="search" placeholder="원송장번호 입력" autocomplete="off" />
<button id="rec-history-search" type="button">검색</button>
<button id="rec-history-reset" type="button" class="return-history-reset">초기화</button>
</div>
<span id="rec-history-status" role="status">최근 3개월</span>
</div>
</div>
<!-- Line 2 -->
<div
@@ -3637,7 +3930,7 @@
align-items: center;
"
>
<span>입고 목록</span
<span class="return-history-heading">입고 목록 <label><input id="rec-history-all" type="checkbox" /> 전체 보기</label></span
><label
style="
cursor: pointer;
@@ -3737,9 +4030,11 @@
{% include 'version_history.html' %}
</main>
</div>
<script src="{{ root_path }}/static/js/app.js?v=69"></script>
<script src="{{ root_path }}/static/js/app.js?v=74"></script>
<script src="{{ root_path }}/static/js/manual_grid.js?v=2"></script>
<script src="{{ root_path }}/static/js/cs_records.js?v=19"></script>
<script src="{{ root_path }}/static/js/mall_event.js?v=7"></script>
<script src="{{ root_path }}/static/js/milkrun_gsheet.js?v=7"></script>
<script src="{{ root_path }}/static/js/milkrun_gsheet.js?v=8"></script>
<!-- Toast Notification -->
<div
+70
View File
@@ -0,0 +1,70 @@
// Optional browser QA: NODE_PATH must contain an installed playwright package.
const {chromium} = require('playwright');
const http = require('node:http');
const fs = require('node:fs');
const path = require('node:path');
const assert = require('node:assert/strict');
const {execFileSync} = require('node:child_process');
const root = path.resolve(__dirname, '..');
const headers = JSON.parse(execFileSync('python', ['-c', 'import json; from manual_orders import HEADERS; print(json.dumps(HEADERS))'], {cwd: root, encoding: 'utf8'}));
const template = fs.readFileSync(path.join(root, 'templates/index.html'), 'utf8');
const markup = template.match(/{% if manual_orders_backend == 'postgres' %}([\s\S]*?){% else %}/)[1];
const html = `<!doctype html><meta charset="utf-8"><link rel="stylesheet" href="/static/css/styles.css"><link rel="stylesheet" href="/static/css/manual_grid.css"><style>body{margin:0;font-family:Arial,sans-serif}section{height:94vh;width:100%}</style><section id="manual-tab" class="active">${markup}</section><script src="/static/js/manual_grid.js"></script>`;
let rows = [1, 2].map(id => ({id, version: 1, cells: headers.map((_, c) => c === 0 ? '2026-10-22 10:00:00' : c === 3 ? `테스트 고객 ${id}` : c === 4 ? 'TEST-0001' : c === 8 ? '00123' : '')}));
let failNext = false, conflictNext = false, exported = false, nextId = 3;
const server = http.createServer(async (req, res) => {
const reply = (code, data) => { res.writeHead(code, {'Content-Type': 'application/json'}); res.end(JSON.stringify(data)); };
if (req.url === '/') {res.writeHead(200, {'Content-Type': 'text/html; charset=utf-8'});res.end(html);return;}
if (req.url.startsWith('/static/')) {res.writeHead(200, {'Content-Type': req.url.endsWith('.css') ? 'text/css' : 'application/javascript'});res.end(fs.readFileSync(path.join(root, req.url)));return;}
if (req.url === '/api/manual/orders' && req.method === 'GET') return reply(200, {headers, rows});
if (req.url === '/api/manual/orders' && req.method === 'POST') {rows.push({id: nextId++, version: 1, cells: headers.map(() => '')});return reply(200, {status: 'success'});}
if (req.url === '/api/manual/orders/cells') {
let body='';for await(const chunk of req)body+=chunk;
const changes=JSON.parse(body).changes;
if(failNext){failNext=false;return reply(503,{detail:'테스트 저장 장애'});}
if(conflictNext){conflictNext=false;const change=changes[0], row=rows.find(r=>r.id===change.id);row.cells[change.column]='다른 사용자의 최신값';row.version++;return reply(409,{detail:{message:'동시 수정 충돌',conflicts:[{id:row.id,column:change.column,value:row.cells[change.column]}]}});}
const updated=new Set();
for(const c of changes){const row=rows.find(r=>r.id===c.id);assert(row);assert(row.cells[c.column]===c.previous_value||row.cells[c.column]===c.value);row.cells[c.column]=c.value;updated.add(row.id);}
for(const id of updated)rows.find(r=>r.id===id).version++;
return reply(200,{rows:rows.filter(r=>updated.has(r.id))});
}
if(req.url==='/api/manual/orders/archive'){
let body='';for await(const chunk of req)body+=chunk;const selected=JSON.parse(body).rows;
for(const row of selected)assert.equal(rows.find(r=>r.id===row.id).version,row.version);
rows=rows.filter(r=>!selected.some(s=>s.id===r.id));return reply(200,{status:'success'});
}
if(req.url==='/api/sheet/download_and_clear'){exported=true;rows=[];res.writeHead(200,{'Content-Type':'application/octet-stream','Content-Disposition':"attachment; filename*=UTF-8''manual.xlsx"});res.end('exported rows');return;}
if(req.url==='/api/manual/orders/exports')return reply(200,exported?[{id:'test-export',filename:'manual.xlsx'}]:[]);
if(req.url==='/api/manual/orders/exports/test-export'){res.writeHead(200,{'Content-Disposition':'attachment; filename="manual.xlsx"'});res.end('exported rows');return;}
reply(404,{});
});
(async()=>{
await new Promise(resolve=>server.listen(0,'127.0.0.1',resolve));
const browser=await chromium.launch({headless:true,executablePath:process.env.CORM_TEST_BROWSER||'C:/Program Files/Google/Chrome/Application/chrome.exe'});
try{
const page=await browser.newPage({viewport:{width:1440,height:900},acceptDownloads:true});
const errors=[];page.on('pageerror',e=>errors.push(e.message));
await page.goto(`http://127.0.0.1:${server.address().port}`);
await page.waitForSelector('td[data-id="1"]');
const cell=(id,column)=>page.locator(`td[data-id="${id}"][data-column="${column}"]`);
const saved=()=>page.waitForFunction(()=>document.getElementById('manual-save-status').textContent==='모든 변경사항 저장됨');
await cell(1,3).fill('자동 저장 고객');await saved();assert.equal(rows[0].cells[3],'자동 저장 고객');
await page.reload();await page.waitForSelector('td[data-id="1"]');assert.equal(await cell(1,3).textContent(),'자동 저장 고객');
await cell(1,3).focus();await page.keyboard.press('Tab');assert.equal(await page.locator(':focus').getAttribute('data-column'),'4');
await cell(1,4).evaluate(td=>{const data=new DataTransfer();data.setData('text/plain','CODE-A\t상품 A\nCODE-B\t상품 B');td.dispatchEvent(new ClipboardEvent('paste',{clipboardData:data,bubbles:true,cancelable:true}));});
await saved();assert.equal(rows[1].cells[5],'상품 B');
failNext=true;await cell(1,3).fill('장애 후 재시도');await page.waitForSelector('#manual-save-retry:not([hidden])');
assert.equal(await cell(1,3).textContent(),'장애 후 재시도');await page.click('#manual-save-retry');await saved();assert.equal(rows[0].cells[3],'장애 후 재시도');
conflictNext=true;await cell(1,3).fill('충돌 입력');await page.waitForSelector('#manual-save-retry:not([hidden])');
page.once('dialog',d=>d.dismiss());await page.click('#manual-save-retry');await saved();assert.equal(await cell(1,3).textContent(),'다른 사용자의 최신값');
await page.locator('body').click({position:{x:1300,y:800}});
fs.mkdirSync(path.join(root,'.cache'),{recursive:true});await page.screenshot({path:path.join(root,'.cache/manual-grid-qa.png')});
await page.click('#manual-add-row');await page.waitForSelector('td[data-id="3"]');
await cell(3,3).fill('삭제 전 수정');await page.check('input[data-row-id="3"]');
page.once('dialog',d=>d.accept());await page.click('#manual-delete-rows');await page.waitForFunction(()=>document.querySelectorAll('#manual-grid tbody tr').length===2);
page.once('dialog',d=>d.accept());const download=page.waitForEvent('download');await page.click('#manual-download');await download;
await page.waitForFunction(()=>document.getElementById('manual-grid-empty').hidden===false);
await page.click('#manual-exports summary');await page.waitForSelector('#manual-export-list a');assert.equal(await page.locator('#manual-export-list a').textContent(),'manual.xlsx');
assert.deepEqual(errors,[]);console.log('Browser QA passed: autosave, reload, Tab, multi-cell paste, failure retry, conflict, row add/delete, export and redownload link.');
}finally{await browser.close();server.close();}
})().catch(error=>{console.error(error);server.close();process.exitCode=1;});
+93
View File
@@ -0,0 +1,93 @@
"""Validate operator deployment decisions without production access."""
import io
import os
import tempfile
import unittest
from contextlib import redirect_stderr, redirect_stdout
from pathlib import Path
from unittest.mock import MagicMock, patch
import deploy_corm as deployment
from history_store import normalize_record
from settings_store import HEADER, SettingsNotInitialized
ROWS = [HEADER, ["APP_SETTINGS", "shipping_fee", "3500"]]
HISTORY = {"kind": "corm-history", "sms": [], "manual": []}
class DeploymentTests(unittest.TestCase):
def setUp(self):
self.output = io.StringIO()
def test_environment_switch_preserves_existing_secret_and_database_keys(self):
with tempfile.TemporaryDirectory() as directory:
path = Path(directory) / ".env"
path.write_text("POSTGRES_PASSWORD='keep-$-secret'\nITEMCODE_DB=itemcode_db\n# preserve comment\n", encoding="utf-8")
with patch.object(os, "chown", create=True), redirect_stdout(self.output):
deployment.switch_environment(path)
text = path.read_text(encoding="utf-8")
self.assertIn("POSTGRES_PASSWORD='keep-$-secret'", text)
self.assertIn("ITEMCODE_DB=itemcode_db", text)
self.assertIn("# preserve comment", text)
self.assertIn("SETTINGS_DB=corm_db", text)
self.assertIn("HISTORY_BACKEND=postgres", text)
def test_existing_settings_difference_is_never_overwritten(self):
store = MagicMock()
store.read.return_value = ([HEADER, ["APP_SETTINGS", "shipping_fee", "9999"]], 2)
with patch.object(deployment, "load_snapshot", return_value=(ROWS, "test")), \
patch.object(deployment, "load_backup", return_value=(HISTORY, "test")), \
patch.object(deployment, "PostgresSettingsStore", return_value=store), \
self.assertRaises(RuntimeError):
deployment.migrate("unused")
store.import_rows.assert_not_called()
def test_incomplete_import_can_resume_without_reimporting_settings(self):
settings = MagicMock()
settings.read.return_value = (ROWS, 1)
histories = MagicMock()
histories.check_ready.side_effect = SettingsNotInitialized()
histories.export_all.return_value = HISTORY
with patch.object(deployment, "load_snapshot", return_value=(ROWS, "test")), \
patch.object(deployment, "load_backup", return_value=(HISTORY, "test")), \
patch.object(deployment, "PostgresSettingsStore", return_value=settings), \
patch.object(deployment, "PostgresHistoryStore", return_value=histories), redirect_stdout(self.output):
deployment.migrate("unused")
settings.import_rows.assert_not_called()
histories.import_all.assert_called_once_with(HISTORY, "test")
def test_snapshot_uses_existing_database_when_already_converted(self):
with tempfile.TemporaryDirectory() as directory:
settings = MagicMock()
settings.read.return_value = (ROWS, 1)
histories = MagicMock()
histories.export_all.return_value = HISTORY
with patch.object(deployment, "PostgresSettingsStore", return_value=settings), \
patch.object(deployment, "PostgresHistoryStore", return_value=histories), \
patch.object(deployment, "read_google_rows") as google, \
patch.object(os, "chown", create=True), redirect_stdout(self.output):
deployment.snapshot(directory, "postgres", "postgres")
google.assert_not_called()
self.assertTrue((Path(directory) / "settings-source.json").exists())
self.assertTrue((Path(directory) / "history-source.json").exists())
def test_database_creation_does_not_modify_an_existing_database(self):
connection = MagicMock()
cursor = connection.cursor.return_value.__enter__.return_value
cursor.fetchone.return_value = (1,)
with patch.object(deployment, "settings_db_config", return_value={"dbname": "corm_db", "user": "app"}), \
patch.object(deployment.psycopg2, "connect", return_value=connection), redirect_stdout(self.output):
deployment.ensure_database()
cursor.execute.assert_called_once()
def test_deployment_error_output_does_not_reveal_credentials(self):
with patch.object(deployment, "ensure_database", side_effect=RuntimeError("password=do-not-print")), \
redirect_stderr(self.output):
self.assertEqual(deployment.main(["database"]), 1)
self.assertNotIn("do-not-print", self.output.getvalue())
if __name__ == "__main__":
unittest.main()
+151
View File
@@ -0,0 +1,151 @@
"""ERP 권한 차단·장애·경로 우회를 DB/외부 네트워크 없이 검증한다."""
import os
import unittest
from unittest.mock import patch
from urllib.parse import parse_qs, urlsplit
import httpx
from fastapi import FastAPI, Request
from fastapi.responses import JSONResponse
from fastapi.testclient import TestClient
from erp_auth import AuthGuardMiddleware
class ErpAuthTests(unittest.TestCase):
def setUp(self):
self.env = patch.dict(os.environ, {
"APP_ROOT_PATH": "/corm", "MAIN_LOGIN_URL": "https://erp.example/login",
"ERP_AUTH_URL": "http://erp.test/api/auth/check", "SESSION_COOKIE_NAME": "erp_session",
"INTERNAL_SERVICE_TOKEN": "system-only-test-token",
})
self.env.start()
self.addCleanup(self.env.stop)
self.calls = []
self.status = 200
self.payload = {"module": "corm", "user": {"email": "worker@example.test", "name": "Worker"}}
self.error = None
self.hits = []
self.real_client = httpx.AsyncClient
def handler(request):
self.calls.append(request)
if self.error:
raise self.error
return httpx.Response(self.status, json=self.payload)
def client_factory(**kwargs):
self.assertEqual(kwargs, {
"timeout": 3.0, "follow_redirects": False, "trust_env": False,
"cookies": {"erp_session": "browser-cookie"},
})
return self.real_client(transport=httpx.MockTransport(handler), **kwargs)
self.mock_client = patch("erp_auth.httpx.AsyncClient", side_effect=client_factory)
self.mock_client.start()
self.addCleanup(self.mock_client.stop)
app = FastAPI()
app.add_middleware(AuthGuardMiddleware)
@app.api_route("/{path:path}", methods=["GET", "POST"])
async def protected(request: Request, path: str):
self.hits.append(path)
return JSONResponse({"user": getattr(request.state, "erp_user", None)})
self.client = TestClient(app, follow_redirects=False)
def get(self, path="/api/items", cookie=True, headers=None):
headers = dict(headers or {})
if cookie:
headers["Cookie"] = "erp_session=browser-cookie; unrelated=do-not-forward"
return self.client.get(path, headers=headers)
def test_authorized_identity_and_only_session_cookie_forwarded(self):
r = self.get()
self.assertEqual(r.status_code, 200)
self.assertEqual(r.json()["user"], self.payload["user"])
call = self.calls[0]
self.assertEqual(str(call.url), "http://erp.test/api/auth/check?module=corm")
self.assertEqual(call.headers["cookie"], "erp_session=browser-cookie")
self.assertNotIn("x-service-token", call.headers)
self.assertNotIn("set-cookie", r.headers)
def test_no_cookie_api_401_and_html_redirect_with_query(self):
self.assertEqual(self.get(cookie=False).status_code, 401)
r = self.get("/corm/returns?status=open&term=a%20b", cookie=False)
self.assertEqual(r.status_code, 302)
parts = urlsplit(r.headers["location"])
self.assertEqual(parts.netloc, "erp.example")
self.assertEqual(parse_qs(parts.query)["next"], ["/corm/returns?status=open&term=a%20b"])
self.assertFalse(self.calls)
self.assertFalse(self.hits)
def test_permission_removal_and_deleted_user_take_effect_next_request(self):
self.assertEqual(self.get().status_code, 200)
self.status = 403
self.assertEqual(self.get().status_code, 403)
self.assertEqual(self.get("/", headers={"Accept": "text/html"}).status_code, 403)
self.status = 401
self.assertEqual(self.get().status_code, 401)
self.assertEqual(self.get("/").status_code, 302)
self.assertEqual(len(self.hits), 1)
self.assertEqual(len(self.calls), 5)
def test_erp_failures_never_allow_access(self):
for status in (302, 404, 422, 500, 503):
with self.subTest(status=status):
self.status = status
self.assertEqual(self.get().status_code, 503)
self.error = httpx.ConnectError("offline")
self.assertEqual(self.get().status_code, 503)
self.error = httpx.ReadTimeout("timeout")
r = self.get("/")
self.assertEqual(r.status_code, 503)
self.assertEqual(r.headers["cache-control"], "no-store")
self.assertFalse(self.hits)
def test_malformed_or_wrong_module_responses_are_denied(self):
for payload in (None, [], {}, {"module": "order", "user": {"email": "a"}},
{"module": "corm", "user": {"email": ""}},
{"module": "corm", "user": {"email": 1}}):
with self.subTest(payload=payload):
self.payload = payload
self.assertEqual(self.get().status_code, 503)
self.assertFalse(self.hits)
def test_exempt_paths_are_exact_and_corm_prefix_is_handled(self):
for path in ("/login", "/logout", "/health/db", "/admin/oauth/callback?code=x",
"/static/app.js", "/corm/health/db", "/corm/static/app.js"):
with self.subTest(path=path):
self.assertEqual(self.get(path, cookie=False).status_code, 200)
for path in ("/login-bypass", "/health/secrets", "/admin/oauth/callback/secret"):
with self.subTest(path=path):
self.assertEqual(self.get(path, cookie=False).status_code, 302)
self.assertFalse(self.calls)
def test_existing_machine_auth_and_invalid_token(self):
self.assertEqual(self.get(cookie=False, headers={"X-Service-Token": "system-only-test-token"}).status_code, 200)
self.assertEqual(self.get(cookie=False, headers={"X-Service-Token": "wrong"}).status_code, 401)
self.assertEqual(len(self.hits), 1)
self.assertFalse(self.calls)
def test_real_application_page_and_login_logout_delegation(self):
import main
client = TestClient(main.app, follow_redirects=False)
page = client.get("/", headers={"Cookie": "erp_session=browser-cookie"})
self.assertEqual(page.status_code, 200)
self.assertNotIn("set-cookie", page.headers)
self.status = 403
self.assertEqual(client.get("/", headers={"Cookie": "erp_session=browser-cookie"}).status_code, 403)
self.assertEqual(client.get("/api/config").status_code, 401)
login = client.get("/login", params={"next": "/corm/?tab=returns"})
self.assertEqual(login.status_code, 302)
self.assertEqual(parse_qs(urlsplit(login.headers["location"]).query)["next"], ["/corm/?tab=returns"])
logout = client.get("/logout")
self.assertEqual(logout.status_code, 302)
self.assertEqual(logout.headers["location"], main.MAIN_LOGOUT_URL)
self.assertNotIn("set-cookie", logout.headers)
if __name__ == "__main__":
unittest.main()
+302
View File
@@ -0,0 +1,302 @@
"""Fault-injection tests. All Google writes and DB connections are replaced."""
import asyncio
import copy
import json
import os
import tempfile
import threading
import time
import unittest
from pathlib import Path
from unittest.mock import MagicMock, patch
from urllib.parse import urlsplit
import requests
_import_cache_dir = tempfile.TemporaryDirectory()
os.environ["SETTINGS_CACHE_FILE"] = str(Path(_import_cache_dir.name) / "settings.json")
# 서버판(dbx-corm)은 ERP 중앙 인증으로 로그인 안 된 요청을 401 로 막는다.
# 테스트는 서비스 간 호출 토큰(X-Service-Token)으로 통과한다.
os.environ["INTERNAL_SERVICE_TOKEN"] = "test-service-token"
import main
import google_runtime as runtime
import routers.cs_records as records
ROWS = [["대분류 (Section)", "항목 (Key)", "설정값 1"], ["APP_SETTINGS", "theme", "old"]]
async def request(method, url, payload=None):
parsed = urlsplit(url)
body = b"" if payload is None else json.dumps(payload).encode()
events = []
received = False
async def receive():
nonlocal received
if not received:
received = True
return {"type": "http.request", "body": body, "more_body": False}
await asyncio.Event().wait()
async def send(event):
events.append(event)
await main.app({
"type": "http", "asgi": {"version": "3.0"}, "http_version": "1.1",
"method": method, "scheme": "http", "path": parsed.path,
"raw_path": parsed.path.encode(), "query_string": parsed.query.encode(),
"root_path": "", "server": ("test", 80), "client": ("127.0.0.1", 1234),
"headers": [(b"content-type", b"application/json"), (b"x-service-token", b"test-service-token")],
}, receive, send)
start = next(e for e in events if e["type"] == "http.response.start")
content = b"".join(e.get("body", b"") for e in events if e["type"] == "http.response.body")
return start["status"], dict(start["headers"]), content
async def wait_until(predicate):
async with asyncio.timeout(2):
while not predicate():
await asyncio.sleep(0.005)
class CacheTests(unittest.TestCase):
def setUp(self):
self.directory = tempfile.TemporaryDirectory()
self.cache = runtime.SettingsCache(Path(self.directory.name) / "settings.json", "test", ttl=60)
def tearDown(self):
self.cache.close()
self.directory.cleanup()
def test_fresh_cache_and_callers_cannot_mutate_snapshot(self):
loader = MagicMock(return_value=copy.deepcopy(ROWS))
first = self.cache.read(loader)
first[1][2] = "changed outside cache"
self.assertEqual(self.cache.read(loader), ROWS)
loader.assert_called_once()
def test_stale_reads_are_immediate_and_refresh_is_single_flight(self):
self.cache.publish(ROWS)
self.cache.invalidate()
started, release = threading.Event(), threading.Event()
def slow_load():
started.set()
release.wait(2)
return [["new"]]
loader = MagicMock(side_effect=slow_load)
try:
began = time.monotonic()
self.assertEqual(self.cache.read(loader), ROWS)
self.assertLess(time.monotonic() - began, 0.2)
self.assertTrue(started.wait(1))
for _ in range(20):
self.assertEqual(self.cache.read(loader), ROWS)
self.assertEqual(loader.call_count, 1)
finally:
release.set()
self.cache._pending.result(2)
self.assertEqual(self.cache.read(loader), [["new"]])
def test_restart_restores_snapshot_when_google_is_down(self):
self.cache.publish(ROWS)
restored = runtime.SettingsCache(self.cache.path, "test")
try:
loader = MagicMock(side_effect=requests.ReadTimeout())
self.assertEqual(restored.read(loader), ROWS)
restored._pending.result(2)
for _ in range(10):
self.assertEqual(restored.read(loader), ROWS)
self.assertEqual(loader.call_count, 1)
finally:
restored.close()
def test_background_read_cannot_replace_a_later_save(self):
self.cache.publish(ROWS)
self.cache.invalidate()
started, release = threading.Event(), threading.Event()
def slow_load():
started.set()
release.wait(2)
return ROWS
try:
self.cache.read(slow_load)
self.assertTrue(started.wait(1))
self.cache.publish([["saved later"]])
finally:
release.set()
self.cache._pending.result(2)
self.assertEqual(self.cache.read(slow_load), [["saved later"]])
self.assertEqual(json.loads(self.cache.path.read_text(encoding="utf-8"))["rows"], [["saved later"]])
def test_force_refresh_failure_is_not_hidden_by_cache(self):
self.cache.publish(ROWS)
with self.assertRaises(requests.ReadTimeout):
self.cache.read(MagicMock(side_effect=requests.ReadTimeout()), force=True)
self.assertEqual(self.cache.read(MagicMock()), ROWS)
def test_cache_cannot_be_reused_for_a_different_spreadsheet(self):
self.cache.publish(ROWS)
other = runtime.SettingsCache(self.cache.path, "different-sheet")
try:
self.assertFalse(other.has_value)
finally:
other.close()
class TimeoutTests(unittest.TestCase):
def test_auth_refresh_default_timeout_is_capped(self):
with patch.object(requests.Session, "request", return_value=object()) as send:
runtime.GoogleTimeoutSession().request("POST", "https://example.invalid", timeout=120)
self.assertEqual(send.call_args.kwargs["timeout"], runtime.GOOGLE_TIMEOUT)
def test_authorized_requests_are_bounded_even_with_no_timeout(self):
session = runtime.create_google_session(MagicMock())
with patch.object(runtime.AuthorizedSession, "request", return_value=object()) as send:
session.request("GET", "https://example.invalid", timeout=None)
self.assertEqual(send.call_args.kwargs["timeout"], runtime.GOOGLE_TIMEOUT)
class ApiTests(unittest.IsolatedAsyncioTestCase):
def setUp(self):
# These tests exercise the legacy Google backend regardless of local .env.
backend = patch.object(main, "SETTINGS_BACKEND", "google")
backend.start()
self.addCleanup(backend.stop)
histories = patch.object(main, "HISTORY_BACKEND", "google")
histories.start()
self.addCleanup(histories.stop)
manual = patch.object(main, "MANUAL_ORDERS_BACKEND", "google")
manual.start()
self.addCleanup(manual.stop)
self.directory = tempfile.TemporaryDirectory()
self.cache = runtime.SettingsCache(Path(self.directory.name) / "settings.json", "test")
self.cache_patch = patch.object(main, "_settings_cache", self.cache)
self.cache_patch.start()
def tearDown(self):
self.cache_patch.stop()
self.cache.close()
self.directory.cleanup()
async def test_google_hang_does_not_block_page_or_db_records(self):
started, release = threading.Event(), threading.Event()
def stalled_google():
started.set()
release.wait(3)
raise requests.ReadTimeout()
connection = MagicMock()
connection.cursor.return_value.__enter__.return_value.fetchall.return_value = []
with patch.object(main, "_load_settings_rows", stalled_google), \
patch.object(records, "_ensure_schema"), patch.object(records, "_connect", return_value=connection):
config_task = asyncio.create_task(request("GET", "/api/config"))
try:
await wait_until(started.is_set)
async with asyncio.timeout(1):
page = await request("GET", "/")
db = await request("GET", "/api/cs-records?record_date=2026-10-19")
self.assertEqual(page[0], 200)
self.assertEqual(db[0], 200)
self.assertEqual(json.loads(db[2])["records"], [])
self.assertFalse(config_task.done())
finally:
release.set()
response = await config_task
self.assertEqual(response[0], 503)
async def test_saturation_keeps_db_pool_and_cached_settings_available(self):
release = threading.Event()
started = []
def stalled():
started.append(True)
release.wait(3)
self.cache.publish(ROWS)
tasks = [asyncio.create_task(runtime.run_google_io(stalled)) for _ in range(runtime.GOOGLE_WORKERS)]
try:
await wait_until(lambda: len(started) == runtime.GOOGLE_WORKERS)
with self.assertRaises(main.HTTPException) as error:
await runtime.run_google_io(lambda: None)
self.assertEqual(error.exception.status_code, 503)
async with asyncio.timeout(1):
config = await request("GET", "/api/config")
page = await request("GET", "/")
self.assertEqual(config[0], 200)
self.assertEqual(page[0], 200)
finally:
release.set()
await asyncio.gather(*tasks)
async def test_settings_save_uses_one_atomic_write_and_updates_cache(self):
self.cache.publish(ROWS)
worksheet = MagicMock(id=123, row_count=999, col_count=26)
worksheet.get_all_values.return_value = copy.deepcopy(ROWS)
with patch.object(main, "get_settings_worksheet", return_value=worksheet):
saved = await request("POST", "/api/app/settings", {"theme": "new"})
self.assertEqual(saved[0], 200)
worksheet.clear.assert_not_called()
worksheet.update.assert_not_called()
worksheet.spreadsheet.batch_update.assert_called_once()
body = worksheet.spreadsheet.batch_update.call_args.args[0]
change = body["requests"][-1]["updateCells"]
self.assertEqual(change["range"], {"sheetId": 123})
self.assertEqual(change["fields"], "userEnteredValue")
self.assertEqual(change["rows"][1]["values"][2]["userEnteredValue"]["stringValue"], "new")
response = await request("GET", "/api/config")
self.assertEqual(json.loads(response[2])["APP_SETTINGS"]["theme"], "new")
self.assertEqual(worksheet.get_all_values.call_count, 1)
async def test_save_does_not_use_stale_settings_during_outage(self):
self.cache.publish(ROWS)
worksheet = MagicMock()
worksheet.get_all_values.side_effect = requests.ReadTimeout()
with patch.object(main, "get_settings_worksheet", return_value=worksheet):
response = await request("POST", "/api/app/settings", {"theme": "new"})
self.assertEqual(response[0], 503)
worksheet.spreadsheet.batch_update.assert_not_called()
self.assertEqual(self.cache.read(MagicMock()), ROWS)
async def test_failed_write_never_publishes_unconfirmed_values(self):
self.cache.publish(ROWS)
worksheet = MagicMock(id=123, row_count=999, col_count=26)
worksheet.get_all_values.return_value = copy.deepcopy(ROWS)
worksheet.spreadsheet.batch_update.side_effect = requests.ReadTimeout()
with patch.object(main, "get_settings_worksheet", return_value=worksheet):
response = await request("POST", "/api/app/settings", {"theme": "new"})
self.assertEqual(response[0], 503)
self.assertEqual(self.cache._rows, ROWS)
self.assertTrue(self.cache.stale)
async def test_concurrent_settings_updates_preserve_both_changes(self):
remote_rows = copy.deepcopy(ROWS)
worksheet = MagicMock(id=123, row_count=999, col_count=26)
worksheet.get_all_values.side_effect = lambda: copy.deepcopy(remote_rows)
def atomic_save(body):
remote_rows[:] = [[c["userEnteredValue"]["stringValue"] for c in row["values"]]
for row in body["requests"][-1]["updateCells"]["rows"]]
worksheet.spreadsheet.batch_update.side_effect = atomic_save
with patch.object(main, "get_settings_worksheet", return_value=worksheet):
results = await asyncio.gather(
request("POST", "/api/app/settings", {"first": "1"}),
request("POST", "/api/app/settings", {"second": "2"}),
)
self.assertTrue(all(result[0] == 200 for result in results))
result = await request("GET", "/api/config")
settings = json.loads(result[2])["APP_SETTINGS"]
self.assertEqual(settings["first"], "1")
self.assertEqual(settings["second"], "2")
if __name__ == "__main__":
unittest.main()
+141
View File
@@ -0,0 +1,141 @@
"""Manual queue fidelity, atomic autosave, export recovery and API isolation."""
import io
import json
import unittest
from unittest.mock import MagicMock, patch
from openpyxl import load_workbook
import main
from manual_orders import HEADERS, CellConflict, ManualOrderStore, build_xlsx, validate_source
from test_google_resilience import request
CELLS = ["2026-10-22 10:00:00", "", "3500", "테스트", "TEST-1", "상품", "2", "주소", "00123", "010-0000-0000", "010-0000-0000", "요청", "", "수동발주", "비고"]
class FormatTests(unittest.TestCase):
def test_excel_keeps_column_order_leading_zeros_and_literal_formulas(self):
cells = CELLS.copy()
cells[14] = '=HYPERLINK("https://example.invalid","text")'
book = load_workbook(io.BytesIO(build_xlsx(HEADERS, [cells])))
sheet = book.active
self.assertEqual(list(next(sheet.values)), HEADERS)
self.assertEqual(sheet["I2"].value, "00123")
self.assertEqual(sheet["G2"].value, 2)
self.assertEqual(sheet["O2"].data_type, "s")
self.assertEqual(sheet["O2"].value, cells[14])
def test_source_padding_keeps_blank_rows_and_extra_columns(self):
headers, rows = validate_source({"kind": "corm-manual-orders", "headers": HEADERS + ["추가 열"], "rows": [[], CELLS + ["추가 값"]]})
self.assertEqual(len(rows[0]), 16)
self.assertEqual(rows[1][-1], "추가 값")
class StoreTests(unittest.TestCase):
def setUp(self):
self.store = ManualOrderStore({})
self.connection = MagicMock()
self.cursor = self.connection.cursor.return_value.__enter__.return_value
connect = patch.object(self.store, "_connect", return_value=self.connection)
connect.start()
self.addCleanup(connect.stop)
def test_same_cell_conflict_aborts_entire_batch(self):
self.cursor.fetchall.return_value = [(1, CELLS.copy(), 2)]
with self.assertRaises(CellConflict):
self.store.patch_cells([{"id": 1, "column": 3, "value": "새 이름", "previous_value": "다른 이름"}])
self.assertIs(self.connection.__exit__.call_args.args[0], CellConflict)
self.assertEqual(self.cursor.execute.call_count, 1)
def test_retry_of_already_saved_value_succeeds_and_other_cells_are_preserved(self):
self.cursor.fetchall.return_value = [(1, CELLS.copy(), 2)]
self.cursor.fetchone.return_value = (3,)
rows = self.store.patch_cells([{"id": 1, "column": 3, "value": CELLS[3], "previous_value": "old"}])
self.assertEqual(rows[0]["cells"][8], "00123")
self.assertEqual(rows[0]["version"], 3)
def test_missing_or_archived_row_is_not_recreated_by_autosave(self):
self.cursor.fetchall.return_value = []
with self.assertRaises(LookupError):
self.store.patch_cells([{"id": 1, "column": 3, "value": "new", "previous_value": "old"}])
def test_order_and_summary_share_one_transaction(self):
self.cursor.fetchone.return_value = (HEADERS,)
self.cursor.execute.side_effect = [None, None, RuntimeError("summary insert failed")]
with self.assertRaises(RuntimeError):
self.store.append([CELLS], {"timestamp": CELLS[0], "customer_name": "테스트", "total_amount": 3500})
self.assertIs(self.connection.__exit__.call_args.args[0], RuntimeError)
def test_export_failure_never_archives_rows(self):
self.cursor.fetchone.return_value = (HEADERS,)
self.cursor.fetchall.return_value = [(1, CELLS)]
with patch("manual_orders.build_xlsx", side_effect=RuntimeError("file failed")), self.assertRaises(RuntimeError):
self.store.export_and_archive("test.xlsx")
self.assertEqual(self.cursor.execute.call_count, 2)
def test_export_is_archived_with_exact_row_ids_for_redownload(self):
self.cursor.fetchone.return_value = (HEADERS,)
self.cursor.fetchall.return_value = [(4, CELLS)]
data, export_id = self.store.export_and_archive("test.xlsx")
self.assertTrue(data.startswith(b"PK"))
calls = self.cursor.execute.call_args_list
self.assertIn("corm_manual_exports", calls[2].args[0])
self.assertEqual(calls[3].args[1], ([4],))
class ApiTests(unittest.IsolatedAsyncioTestCase):
def setUp(self):
self.store = MagicMock()
self.store.list_rows.return_value = {"headers": HEADERS, "rows": [{"id": 1, "cells": CELLS, "version": 1}]}
self.store.patch_cells.return_value = []
self.store.export_and_archive.return_value = (build_xlsx(HEADERS, [CELLS]), "00000000-0000-0000-0000-000000000001")
for key, value in (("MANUAL_ORDERS_BACKEND", "postgres"), ("HISTORY_BACKEND", "postgres"), ("_manual_orders", self.store)):
mock = patch.object(main, key, value)
mock.start()
self.addCleanup(mock.stop)
google = patch.object(main, "get_manual_worksheet", side_effect=AssertionError("Unexpected Google call"))
google.start()
self.addCleanup(google.stop)
async def test_grid_read_patch_and_export_do_not_use_google(self):
with patch.object(main, "run_google_io", side_effect=AssertionError("Google unavailable")):
self.assertEqual((await request("GET", "/api/manual/orders"))[0], 200)
self.assertEqual((await request("PATCH", "/api/manual/orders/cells", {"changes": [{"id": 1, "column": 3, "value": "새 이름", "previous_value": "테스트"}]}))[0], 200)
self.assertEqual((await request("POST", "/api/sheet/download_and_clear"))[0], 200)
async def test_conflicting_autosave_returns_current_values(self):
self.store.patch_cells.side_effect = CellConflict([{"id": 1, "column": 3, "value": "최신 이름"}])
status, _, body = await request("PATCH", "/api/manual/orders/cells", {"changes": [{"id": 1, "column": 3, "value": "내 이름", "previous_value": "old"}]})
self.assertEqual(status, 409)
self.assertEqual(json.loads(body)["detail"]["conflicts"][0]["value"], "최신 이름")
async def test_submission_saves_summary_server_side_and_retains_sheet_layout(self):
payload = {"customer_name": "테스트", "phone": "01000000000", "address": "주소", "comment": "요청",
"order_type": "normal", "no_lid": False, "total_amount": 3500,
"items": [{"code": "TEST-1", "name": "상품", "label": "상품", "quantity": 2, "item_type": "ORDER_SINGLE"}]}
status, _, body = await request("POST", "/api/order/submit", payload)
self.assertEqual(status, 200)
self.assertTrue(json.loads(body)["history_saved"])
rows, history = self.store.append.call_args.args
self.assertEqual(len(rows[0]), 15)
self.assertEqual(rows[0][4:7], ["TEST-1", "상품", 2])
self.assertEqual(history["total_amount"], 3500)
async def test_cancellation_updates_queue_and_history_through_same_store(self):
self.assertEqual((await request("DELETE", "/api/manual/history/records/1"))[0], 200)
self.store.delete_history.assert_called_once_with(1)
async def test_legacy_template_uses_sheet_until_migration_and_db_template_uses_grid(self):
# Reuse the actual template render path with service authentication.
status, _, body = await request("GET", "/")
self.assertEqual(status, 200)
self.assertIn(b'id="manual-grid-panel"', body)
self.assertNotIn(b'id="manual-iframe-container"', body)
with patch.object(main, "MANUAL_ORDERS_BACKEND", "google"):
self.assertEqual((await request("GET", "/api/manual/orders"))[0], 409)
if __name__ == "__main__":
unittest.main()
+77
View File
@@ -0,0 +1,77 @@
"""Read-only source capture and non-overwriting manual queue migration."""
import io
import os
import tempfile
import unittest
from contextlib import redirect_stdout
from pathlib import Path
from unittest.mock import MagicMock, patch
import psycopg2
import main
import manual_orders_deploy as deployment
from manual_orders import HEADERS
from settings_migrate import save_snapshot
SOURCE = {"kind": "corm-manual-orders", "headers": HEADERS, "rows": [["2026-10-22"] + [""] * 14]}
class MigrationTests(unittest.TestCase):
def setUp(self):
self.directory = tempfile.TemporaryDirectory()
self.addCleanup(self.directory.cleanup)
self.path = Path(self.directory.name) / "manual-orders-source.json"
save_snapshot(self.path, SOURCE, "test")
self.store = MagicMock()
self.store.list_rows.return_value = {"headers": HEADERS, "rows": [{"id": 1, "cells": SOURCE["rows"][0], "version": 1}]}
mocked = patch.object(deployment, "ManualOrderStore", return_value=self.store)
mocked.start()
self.addCleanup(mocked.stop)
def test_existing_identical_queue_is_not_reimported(self):
with redirect_stdout(io.StringIO()):
deployment.migrate(self.directory.name)
self.store.import_source.assert_not_called()
def test_existing_different_queue_is_not_overwritten(self):
self.store.list_rows.return_value = {"headers": HEADERS, "rows": []}
with self.assertRaises(RuntimeError):
deployment.migrate(self.directory.name)
self.store.import_source.assert_not_called()
def test_missing_table_imports_then_validates_every_cell(self):
stored = self.store.list_rows.return_value
self.store.list_rows.side_effect = [psycopg2.errors.UndefinedTable(), stored]
with redirect_stdout(io.StringIO()):
deployment.migrate(self.directory.name)
self.store.import_source.assert_called_once_with(SOURCE, "manual-order-sheet")
def test_bad_checksum_never_accesses_database(self):
self.path.write_text(self.path.read_text(encoding="utf-8").replace('2026-10-22', '2026-10-23'), encoding="utf-8")
with self.assertRaises(ValueError):
deployment.migrate(self.directory.name)
self.store.list_rows.assert_not_called()
def test_google_source_keeps_first_sheet_order_and_uses_no_write_helpers(self):
client = MagicMock()
worksheet = client.open_by_key.return_value.get_worksheet.return_value
worksheet.get_all_values.return_value = [HEADERS, ["date"], [], ["other"]]
with patch.dict(os.environ, {"MANUAL_ORDERS_BACKEND": "google"}), \
patch.object(main, "get_gspread_client", return_value=client):
source = deployment.read_source()
self.assertEqual(len(source["rows"]), 3)
self.assertEqual(source["rows"][1], [""] * 15)
client.open_by_key.return_value.get_worksheet.assert_called_once_with(0)
worksheet.update.assert_not_called()
def test_db_source_never_reads_old_google_values(self):
with patch.dict(os.environ, {"MANUAL_ORDERS_BACKEND": "postgres"}), \
patch.object(main, "get_gspread_client") as google:
self.assertEqual(deployment.read_source(), SOURCE)
google.assert_not_called()
if __name__ == "__main__":
unittest.main()
+282
View File
@@ -0,0 +1,282 @@
"""History fidelity, stable deletion, database failures and migration safeguards."""
import asyncio
import copy
import datetime
import io
import json
import os
import tempfile
import threading
import unittest
from contextlib import redirect_stderr, redirect_stdout
from pathlib import Path
from unittest.mock import MagicMock, patch
import main
import history_migrate as migration
import history_store
from history_store import PostgresHistoryStore, merge_sheet_and_local, normalize_record, validate_bundle
from settings_store import SettingsAlreadyInitialized
from test_google_resilience import request
READ_SOURCE = migration.read_source
STAMP = "2026-10-22 11:22:33"
SMS = {"timestamp": STAMP, "orderType": "normal", "deposit_amount_str": "3,500원",
"custName": "테스트 고객", "custPhone": "010-0000-0000", "custAddress": "테스트 주소",
"items": [{"label": "상품", "code": "TEST-1", "qty": 2}],
"smsOpts": {"discount": True, "pointDeduction": 500}, "previewText": "첫줄\n둘째줄"}
SMS_ROWS = [["일시", "주문유형", "입금액", "주문아이템", "메시지", "이름", "연락처", "주소"],
[STAMP, "normal", "3,500원", "상품[TEST-1] 2개", "첫줄 둘째줄", "테스트 고객", "010-0000-0000", "테스트 주소"]]
MANUAL = {"timestamp": STAMP, "customer_name": "테스트 고객", "total_amount": 3500}
BUNDLE = {"kind": "corm-history", "sms": [SMS], "manual": [MANUAL]}
class MergeTests(unittest.TestCase):
def test_sheet_and_local_details_are_merged_without_duplicates(self):
records = merge_sheet_and_local("sms", SMS_ROWS, [SMS])
self.assertEqual(len(records), 1)
self.assertEqual(records[0]["items"], SMS["items"])
self.assertEqual(records[0]["smsOpts"], SMS["smsOpts"])
self.assertEqual(records[0]["custAddress"], SMS["custAddress"])
self.assertEqual(records[0]["previewText"], SMS["previewText"])
self.assertEqual(records[0]["_legacy_sheet_row"], SMS_ROWS[1])
def test_duplicate_timestamps_and_local_only_records_are_preserved(self):
local_only = {**SMS, "timestamp": "2026-10-22 11:22:34"}
rows = SMS_ROWS + [SMS_ROWS[1].copy()]
records = merge_sheet_and_local("sms", rows, [SMS, copy.deepcopy(SMS), local_only])
self.assertEqual(len(records), 3)
self.assertEqual([r["timestamp"] for r in records], [STAMP, STAMP, local_only["timestamp"]])
def test_distinct_orders_at_same_time_are_not_collapsed(self):
other = {**SMS, "custName": "다른 고객"}
records = merge_sheet_and_local("sms", SMS_ROWS, [other, SMS])
self.assertEqual(len(records), 2)
self.assertEqual([r["custName"] for r in records], ["테스트 고객", "다른 고객"])
def test_manual_historical_rows_and_text_amounts_are_kept(self):
rows = [["일시", "이름", "금액"], ["2020-01-01 12:00:00", "과거", "누락"],
[STAMP, "테스트 고객", "3,500", "추가 열"]]
records = merge_sheet_and_local("manual", rows, [MANUAL])
self.assertEqual(len(records), 2)
self.assertEqual(records[0]["total_amount"], "누락")
self.assertEqual(records[1]["total_amount"], 3500)
self.assertEqual(records[1]["_legacy_sheet_row"][-1], "추가 열")
def test_invalid_records_fail_instead_of_disappearing(self):
for value in ({}, {"timestamp": ""}, [], {"timestamp": STAMP, "amount": float("nan")}):
with self.subTest(value=value), self.assertRaises(ValueError):
normalize_record("manual", value)
with self.assertRaises(ValueError):
validate_bundle({"kind": "settings", "sms": [], "manual": []})
class HistoryApiTests(unittest.IsolatedAsyncioTestCase):
def setUp(self):
manual = patch.object(main, "MANUAL_ORDERS_BACKEND", "google")
manual.start()
self.addCleanup(manual.stop)
self.store = MagicMock()
self.store.list_records.return_value = [{**SMS, "id": 42}]
self.store.add.return_value = 42
self.store.delete.return_value = True
self.store.get_manual.return_value = MANUAL.copy()
self.store.manual_timestamp_count.return_value = 1
for name, value in (("HISTORY_BACKEND", "postgres"), ("_postgres_history", self.store)):
mocked = patch.object(main, name, value)
mocked.start()
self.addCleanup(mocked.stop)
self.google_mock = patch.object(main, "get_sms_history_worksheet", side_effect=AssertionError("Unexpected sheet history I/O"))
self.google_mock.start()
self.addCleanup(self.google_mock.stop)
self.local_file = patch("builtins.open", side_effect=AssertionError("Unexpected local history I/O"))
self.local_file.start()
self.addCleanup(self.local_file.stop)
async def test_db_list_and_save_keep_complete_sms_payload_without_google_or_files(self):
response = await request("POST", "/api/sms/history", SMS)
self.assertEqual(response[0], 200)
self.assertEqual(json.loads(response[2])["id"], 42)
self.store.add.assert_called_once_with("sms", SMS)
status, _, body = await request("GET", "/api/sms/history")
self.assertEqual(status, 200)
self.assertEqual(json.loads(body)[0]["smsOpts"], SMS["smsOpts"])
async def test_sms_deletion_uses_id_and_rejects_old_index_route(self):
self.assertEqual((await request("DELETE", "/api/sms/history/records/42"))[0], 200)
self.store.delete.assert_called_once_with("sms", 42)
self.assertEqual((await request("DELETE", "/api/sms/history/0"))[0], 409)
self.store.delete.assert_called_once()
self.assertEqual((await request("DELETE", "/api/sms/history"))[0], 200)
self.store.clear_sms.assert_called_once()
async def test_db_errors_are_failures_and_do_not_report_saved_or_fall_back(self):
self.store.add.side_effect = RuntimeError("password=do-not-print")
response = await request("POST", "/api/sms/history", SMS)
self.assertEqual(response[0], 503)
self.assertNotIn("do-not-print", response[2].decode())
self.store.list_records.side_effect = RuntimeError()
self.assertEqual((await request("GET", "/api/sms/history"))[0], 503)
async def test_manual_today_display_uses_korean_date_but_save_keeps_payload(self):
self.assertEqual((await request("POST", "/api/manual/history", MANUAL))[0], 200)
self.assertEqual((await request("GET", "/api/manual/history"))[0], 200)
day = self.store.list_records.call_args.kwargs["day"]
self.assertEqual(day, datetime.datetime.now(datetime.timezone(datetime.timedelta(hours=9))).strftime("%Y-%m-%d"))
async def test_google_saturation_does_not_block_history_reads_or_adds(self):
with patch.object(main, "run_google_io", side_effect=AssertionError("Google saturated")):
for method, url, payload in (("GET", "/api/sms/history", None),
("POST", "/api/manual/history", MANUAL),
("GET", "/api/manual/history", None)):
self.assertEqual((await request(method, url, payload))[0], 200)
async def test_manual_delete_keeps_history_if_active_order_deletion_fails(self):
with patch.object(main, "delete_manual_order_rows", side_effect=RuntimeError("google unavailable")):
self.assertEqual((await request("DELETE", "/api/manual/history/records/42"))[0], 503)
self.store.delete.assert_not_called()
async def test_manual_delete_removes_active_order_before_database_history(self):
with patch.object(main, "delete_manual_order_rows") as remove:
self.assertEqual((await request("DELETE", "/api/manual/history/records/42"))[0], 200)
remove.assert_called_once_with(STAMP)
self.store.delete.assert_called_once_with("manual", 42)
async def test_slow_order_cancellation_does_not_block_db_history(self):
started, release = threading.Event(), threading.Event()
def stalled(timestamp):
started.set()
release.wait(3)
with patch.object(main, "delete_manual_order_rows", side_effect=stalled):
deletion = asyncio.create_task(request("DELETE", "/api/manual/history/records/42"))
try:
async with asyncio.timeout(2):
while not started.is_set():
await asyncio.sleep(0.005)
async with asyncio.timeout(1):
self.assertEqual((await request("GET", "/api/sms/history"))[0], 200)
self.assertEqual((await request("POST", "/api/sms/history", SMS))[0], 200)
finally:
release.set()
self.assertEqual((await deletion)[0], 200)
async def test_ambiguous_manual_timestamp_does_not_delete_any_order(self):
self.store.manual_timestamp_count.return_value = 2
with patch.object(main, "delete_manual_order_rows") as remove:
self.assertEqual((await request("DELETE", "/api/manual/history/records/42"))[0], 409)
remove.assert_not_called()
self.store.delete.assert_not_called()
async def test_startup_requires_history_migration_and_checks_corm_database(self):
with patch.dict(os.environ, {"SKIP_DB_INIT": "1"}), \
patch.object(main, "SETTINGS_BACKEND", "postgres"), \
patch.object(main._postgres_settings, "read"):
await main.startup_event()
self.store.check_ready.assert_called_once()
self.store.check_ready.side_effect = RuntimeError("missing history tables")
with self.assertRaises(RuntimeError):
await main.startup_event()
class StoreTests(unittest.TestCase):
def setUp(self):
self.store = PostgresHistoryStore({})
self.connection = MagicMock()
self.cursor = self.connection.cursor.return_value.__enter__.return_value
connect = patch.object(self.store, "_connect", return_value=self.connection)
connect.start()
self.addCleanup(connect.stop)
def test_record_id_cannot_be_spoofed_in_saved_payload(self):
self.cursor.fetchone.return_value = (42,)
self.store.add("sms", {**SMS, "id": 999})
parameters = self.cursor.execute.call_args.args[1]
self.assertNotIn("id", parameters[1].adapted)
self.assertEqual(parameters[1].adapted["smsOpts"], SMS["smsOpts"])
def test_import_is_atomic_and_rejects_existing_data(self):
self.cursor.fetchone.return_value = (True,)
with self.assertRaises(SettingsAlreadyInitialized):
self.store.import_all(BUNDLE, "test")
self.assertIs(self.connection.__exit__.call_args.args[0], SettingsAlreadyInitialized)
def test_readback_failure_aborts_both_history_imports(self):
self.cursor.fetchone.side_effect = [(False,), (False,), (False,)]
self.cursor.fetchall.return_value = []
with patch.object(history_store, "execute_values"), self.assertRaises(RuntimeError):
self.store.import_all(BUNDLE, "test")
self.assertIs(self.connection.__exit__.call_args.args[0], RuntimeError)
def test_active_order_row_deletion_never_deletes_intervening_orders(self):
worksheet = MagicMock(id=5)
worksheet.get_all_values.return_value = [["일시"], [STAMP], ["other"], [STAMP]]
with patch.object(main, "get_manual_worksheet", return_value=worksheet):
main.delete_manual_order_rows(STAMP)
requests = worksheet.spreadsheet.batch_update.call_args.args[0]["requests"]
self.assertEqual([r["deleteDimension"]["range"]["startIndex"] for r in requests], [3, 1])
class MigrationTests(unittest.TestCase):
def setUp(self):
self.directory = tempfile.TemporaryDirectory()
self.addCleanup(self.directory.cleanup)
self.backup = Path(self.directory.name) / "histories.json"
self.store = MagicMock()
self.store.export_all.return_value = copy.deepcopy(BUNDLE)
for name, value in (("read_source", (copy.deepcopy(BUNDLE), "test")), ("build_store", self.store)):
mocked = patch.object(migration, name, return_value=value)
mocked.start()
self.addCleanup(mocked.stop)
self.output = io.StringIO()
def run_cli(self, args):
with redirect_stdout(self.output), redirect_stderr(self.output):
return migration.main(args)
def test_dry_run_does_not_connect_to_database(self):
self.assertEqual(self.run_cli(["import"]), 0)
migration.build_store.assert_not_called()
def test_source_snapshot_preserves_original_and_never_accesses_db(self):
self.assertEqual(self.run_cli(["snapshot", "--output", str(self.backup)]), 0)
self.assertEqual(migration.load_backup(self.backup)[0], BUNDLE)
migration.build_store.assert_not_called()
def test_missing_explicit_local_file_fails_before_google_read(self):
with patch.object(main, "get_gspread_client") as google:
with self.assertRaises(FileNotFoundError):
# Call the real source reader, bypassing this class's read_source mock.
READ_SOURCE(str(self.backup), None)
google.assert_not_called()
def test_apply_saves_backup_before_any_database_write(self):
def imported(bundle, source):
self.assertEqual(migration.load_backup(self.backup)[0], bundle)
self.store.import_all.side_effect = imported
self.assertEqual(self.run_cli(["import", "--apply", "--backup", str(self.backup)]), 0)
self.assertEqual(self.run_cli(["verify", "--input-file", str(self.backup)]), 0)
def test_verify_detects_data_loss_and_export_never_overwrites(self):
self.store.export_all.return_value = {**BUNDLE, "sms": []}
self.assertEqual(self.run_cli(["verify"]), 1)
self.assertEqual(self.run_cli(["export", "--output", str(self.backup)]), 0)
self.assertEqual(self.run_cli(["export", "--output", str(self.backup)]), 1)
def test_corrupt_local_record_or_backup_is_not_silently_skipped(self):
self.backup.write_text("not-json\n", encoding="utf-8")
with self.assertRaises(ValueError):
migration.read_local_records(self.backup)
migration.save_snapshot(self.backup.with_suffix(".other"), BUNDLE, "test")
payload = json.loads(self.backup.with_suffix(".other").read_text(encoding="utf-8"))
payload["rows"]["sms"] = []
self.backup.write_text(json.dumps(payload), encoding="utf-8")
with self.assertRaises(ValueError):
migration.load_backup(self.backup)
if __name__ == "__main__":
unittest.main()
+257
View File
@@ -0,0 +1,257 @@
"""Settings compatibility, isolation from Google, migration and write conflicts."""
import copy
import io
import json
import os
import tempfile
import unittest
from contextlib import redirect_stderr, redirect_stdout
from pathlib import Path
from unittest.mock import MagicMock, patch
import psycopg2
import main
import settings_migrate as migration
from settings_store import (
HEADER, PostgresSettingsStore, SettingsAlreadyInitialized, SettingsConflict,
SettingsNotInitialized, config_to_rows, rows_to_config, settings_db_config, validate_rows,
)
from test_google_resilience import request
ROWS = [
HEADER.copy(),
["APP_SETTINGS", "shipping_fee", "3500"],
["GROUP_NAMES", "ORDER_SINGLE", "단품"],
["ORDER_SINGLE", "상품 B", "B-2", "상품 B", "2000", "1500"],
["ORDER_SINGLE", "---separator_1---"],
["ORDER_SINGLE", "상품 A", "A-1", "상품 A", "1000"],
["SMS_GIFT_RULES", "header_text", "안녕하세요\n주문 안내 😊"],
["EMPTY", "", ""],
]
class CodecTests(unittest.TestCase):
def test_order_separators_empty_sections_and_multiline_survive_roundtrip(self):
restored = rows_to_config(config_to_rows(rows_to_config(ROWS)))
self.assertEqual({s: dict(restored.items(s)) for s in restored.sections()},
{s: dict(rows_to_config(ROWS).items(s)) for s in restored.sections()})
self.assertEqual(list(rows_to_config(ROWS)["ORDER_SINGLE"]),
["상품 B", "---separator_1---", "상품 A"])
def test_invalid_or_empty_imports_are_rejected(self):
for rows in ([], [HEADER], [[123]], [["APP_SETTINGS", "key", "a\x00b"]], {"a": 1}):
with self.subTest(rows=rows), self.assertRaises(ValueError):
validate_rows(rows)
class DatabaseRoutingTests(unittest.TestCase):
def test_settings_default_is_independent_of_itemcode_db(self):
with patch.dict(os.environ, {"ITEMCODE_DB": "existing_codes"}, clear=True):
self.assertEqual(settings_db_config()["dbname"], "corm_db")
self.assertEqual(migration.build_store().db_config["dbname"], "corm_db")
def test_explicit_settings_db_uses_existing_server_credentials(self):
env = {"ITEMCODE_DB": "existing_codes", "SETTINGS_DB": "custom_corm",
"POSTGRES_HOST": "shared-pg", "POSTGRES_PORT": "5433",
"POSTGRES_USER": "app-user", "POSTGRES_PASSWORD": "test-only"}
with patch.dict(os.environ, env, clear=True):
config = settings_db_config()
self.assertEqual(config, {"host": "shared-pg", "port": 5433, "dbname": "custom_corm",
"user": "app-user", "password": "test-only"})
self.assertEqual(migration.build_store().db_config, config)
def test_application_settings_store_has_separate_config(self):
self.assertIsNot(main.SETTINGS_DB_CONFIG, main.DB_CONFIG)
self.assertEqual(main._postgres_settings.db_config, main.SETTINGS_DB_CONFIG)
class StoreTests(unittest.TestCase):
def setUp(self):
self.store = PostgresSettingsStore({"host": "test-db", "password": "test-only"})
self.connection = MagicMock()
self.cursor = self.connection.cursor.return_value.__enter__.return_value
self.connect = patch.object(self.store, "_connect", return_value=self.connection)
self.connect.start()
self.addCleanup(self.connect.stop)
def test_read_closes_connection_and_preserves_version(self):
self.cursor.fetchone.return_value = (copy.deepcopy(ROWS), 7)
self.assertEqual(self.store.read(), (ROWS, 7))
self.connection.close.assert_called_once()
def test_missing_data_or_table_does_not_return_empty_settings(self):
self.cursor.fetchone.return_value = None
with self.assertRaises(SettingsNotInitialized):
self.store.read()
self.cursor.execute.side_effect = psycopg2.errors.UndefinedTable()
with self.assertRaises(SettingsNotInitialized):
self.store.read()
def test_write_uses_expected_version_and_parameterized_values(self):
self.cursor.fetchone.return_value = (8,)
self.assertEqual(self.store.write(ROWS, 7), 8)
query, args = self.cursor.execute.call_args.args
self.assertIn("AND version = %s", query)
self.assertEqual(args[1], 7)
self.assertEqual(args[0].adapted, ROWS)
self.connection.__exit__.assert_called_once_with(None, None, None)
def test_conflict_aborts_transaction(self):
self.cursor.fetchone.return_value = None
with self.assertRaises(SettingsConflict):
self.store.write(ROWS, 7)
self.assertIs(self.connection.__exit__.call_args.args[0], SettingsConflict)
def test_import_never_overwrites_an_existing_document(self):
self.cursor.fetchone.return_value = None
with self.assertRaises(SettingsAlreadyInitialized):
self.store.import_rows(ROWS, "test")
self.assertIn("ON CONFLICT (id) DO NOTHING", self.cursor.execute.call_args.args[0])
self.assertIs(self.connection.__exit__.call_args.args[0], SettingsAlreadyInitialized)
def test_import_readback_mismatch_aborts_before_commit(self):
self.cursor.fetchone.side_effect = [(1,), ([HEADER],)]
with self.assertRaises(RuntimeError):
self.store.import_rows(ROWS, "test")
self.assertIs(self.connection.__exit__.call_args.args[0], RuntimeError)
class PostgresApiTests(unittest.IsolatedAsyncioTestCase):
def setUp(self):
manual = patch.object(main, "MANUAL_ORDERS_BACKEND", "google")
manual.start()
self.addCleanup(manual.stop)
histories = patch.object(main, "HISTORY_BACKEND", "google")
histories.start()
self.addCleanup(histories.stop)
self.backend = patch.object(main, "SETTINGS_BACKEND", "postgres")
self.backend.start()
self.addCleanup(self.backend.stop)
self.store = MagicMock()
self.store.read.return_value = (copy.deepcopy(ROWS), 7)
self.store.write.return_value = 8
self.store_patch = patch.object(main, "_postgres_settings", self.store)
self.store_patch.start()
self.addCleanup(self.store_patch.stop)
self.google = patch.object(main, "get_settings_worksheet", side_effect=AssertionError("Unexpected Google I/O"))
self.google_mock = self.google.start()
self.addCleanup(self.google.stop)
async def test_config_reads_db_and_preserves_existing_api_order(self):
status, headers, body = await request("GET", "/api/config")
self.assertEqual(status, 200)
data = json.loads(body)
self.assertEqual(data["ORDER_SINGLE"]["__order"], ["상품 B", "---separator_1---", "상품 A"])
self.assertEqual(data["SMS_GIFT_RULES"]["header_text"], "안녕하세요\n주문 안내 😊")
self.assertEqual(headers[b"x-settings-backend"], b"postgres")
async def test_all_setting_endpoints_write_db_without_google(self):
payloads = [
("/api/app/settings", {"shipping_fee": "4000"}),
("/api/gift/settings", {"rules": {"header_text": "새 문구"}, "app_settings": {"shipping_fee": "4500"}}),
("/api/products/subgroup-name", {"group_key": "ORDER_SINGLE", "subgroup_index": 0, "name": "분류"}),
("/api/products/settings", {
"groups": [{"old_code": "ORDER_SINGLE", "new_code": "ORDER_SINGLE", "name": "단품"}],
"products": {"ORDER_SINGLE": [{"label": "상품", "code": "NEW", "name": "새 상품", "price": 5000}]},
}),
]
for url, payload in payloads:
with self.subTest(url=url):
self.assertEqual((await request("POST", url, payload))[0], 200)
self.assertEqual(self.store.write.call_args.args[1], 7)
self.google_mock.assert_not_called()
async def test_conflict_returns_409_and_db_failure_never_falls_back_to_google(self):
self.store.write.side_effect = SettingsConflict("다른 요청이 변경했습니다.")
self.assertEqual((await request("POST", "/api/app/settings", {"shipping_fee": "4000"}))[0], 409)
self.store.read.side_effect = RuntimeError("database unavailable")
self.assertEqual((await request("GET", "/api/config"))[0], 503)
self.google_mock.assert_not_called()
async def test_google_saturation_does_not_block_db_settings_reads_or_saves(self):
with patch.object(main, "run_google_io", side_effect=AssertionError("Google pool unavailable")):
self.assertEqual((await request("GET", "/api/config"))[0], 200)
self.assertEqual((await request("POST", "/api/app/settings", {"font": "Arial"}))[0], 200)
async def test_postgres_startup_does_not_refresh_google(self):
with patch.dict(os.environ, {"SKIP_DB_INIT": "1"}), \
patch.object(main._settings_cache, "refresh_in_background") as refresh:
await main.startup_event()
refresh.assert_not_called()
self.store.read.assert_called_once()
async def test_startup_rejects_incomplete_migration(self):
self.store.read.side_effect = SettingsNotInitialized("missing")
with patch.dict(os.environ, {"SKIP_DB_INIT": "1"}), self.assertRaises(SettingsNotInitialized):
await main.startup_event()
class MigrationTests(unittest.TestCase):
def setUp(self):
self.directory = tempfile.TemporaryDirectory()
self.addCleanup(self.directory.cleanup)
self.backup = Path(self.directory.name) / "settings.json"
self.store = MagicMock()
self.store.read.return_value = (copy.deepcopy(ROWS), 1)
self.store.import_rows.return_value = 1
for name, value in (("read_google_rows", (copy.deepcopy(ROWS), "google:test:설정")),
("build_store", self.store)):
patched = patch.object(migration, name, return_value=value)
patched.start()
self.addCleanup(patched.stop)
self.output = io.StringIO()
def run_cli(self, args):
with redirect_stdout(self.output), redirect_stderr(self.output):
return migration.main(args)
def test_dry_run_never_connects_to_or_writes_db(self):
self.assertEqual(self.run_cli(["import"]), 0)
migration.build_store.assert_not_called()
def test_apply_backs_up_before_import_and_does_not_overwrite_backups(self):
def check_backup(rows, source):
self.assertEqual(migration.load_snapshot(self.backup)[0], rows)
return 1
self.store.import_rows.side_effect = check_backup
args = ["import", "--apply", "--backup", str(self.backup)]
self.assertEqual(self.run_cli(args), 0)
self.assertEqual(self.run_cli(args), 1)
self.store.import_rows.assert_called_once()
def test_apply_requires_backup_before_source_or_db_access(self):
with self.assertRaises(SystemExit), redirect_stderr(self.output):
migration.main(["import", "--apply"])
migration.read_google_rows.assert_not_called()
migration.build_store.assert_not_called()
def test_backup_checksum_rejects_modified_content(self):
migration.save_snapshot(self.backup, ROWS, "test")
payload = json.loads(self.backup.read_text(encoding="utf-8"))
payload["rows"][1][2] = "9999"
self.backup.write_text(json.dumps(payload), encoding="utf-8")
with self.assertRaises(ValueError):
migration.load_snapshot(self.backup)
def test_verify_detects_order_or_cell_differences(self):
self.assertEqual(self.run_cli(["verify"]), 0)
self.store.read.return_value = (list(reversed(ROWS)), 1)
self.assertEqual(self.run_cli(["verify"]), 1)
self.store.import_rows.assert_not_called()
def test_export_and_offline_import_need_no_google(self):
self.assertEqual(self.run_cli(["export", "--output", str(self.backup)]), 0)
self.assertEqual(self.run_cli(["import", "--input-file", str(self.backup)]), 0)
migration.read_google_rows.assert_not_called()
def test_errors_do_not_print_database_credentials(self):
self.store.import_rows.side_effect = RuntimeError("password=secret-value")
self.assertEqual(self.run_cli(["import", "--apply", "--backup", str(self.backup)]), 1)
self.assertNotIn("secret-value", self.output.getvalue())
if __name__ == "__main__":
unittest.main()
+137
View File
@@ -0,0 +1,137 @@
"""Opt-in real PostgreSQL tests, confined to a newly created temporary schema.
SETTINGS_TEST_DSN must point to a disposable test database, never production.
"""
import os
import threading
import unittest
import uuid
from concurrent.futures import ThreadPoolExecutor
import psycopg2
from psycopg2 import sql
from settings_store import (
HEADER, PostgresSettingsStore, SettingsAlreadyInitialized, SettingsConflict,
SettingsNotInitialized,
)
from history_store import PostgresHistoryStore
from manual_orders import ManualOrderStore, HEADERS, CellConflict
@unittest.skipUnless(os.getenv("SETTINGS_TEST_DSN"), "SETTINGS_TEST_DSN test database is not configured")
class RealPostgresTests(unittest.TestCase):
def setUp(self):
self.dsn = os.environ["SETTINGS_TEST_DSN"]
self.schema = "corm_settings_test_" + uuid.uuid4().hex
connection = psycopg2.connect(self.dsn, connect_timeout=3)
try:
with connection, connection.cursor() as cursor:
cursor.execute(sql.SQL("CREATE SCHEMA {}").format(sql.Identifier(self.schema)))
finally:
connection.close()
self.addCleanup(self.drop_schema)
self.store = PostgresSettingsStore({})
self.store._connect = self.connect
self.histories = PostgresHistoryStore({})
self.histories._connect = self.connect
self.manual = ManualOrderStore({})
self.manual._connect = self.connect
self.rows = [HEADER.copy(), ["APP_SETTINGS", "font", "한글 😊"],
["ORDER_SINGLE", "B", "B-1"], ["ORDER_SINGLE", "A", "A-1"]]
def connect(self):
connection = psycopg2.connect(self.dsn, connect_timeout=3)
with connection.cursor() as cursor:
cursor.execute(sql.SQL("SET search_path TO {}").format(sql.Identifier(self.schema)))
cursor.execute("SET statement_timeout = 5000")
return connection
def drop_schema(self):
assert self.schema.startswith("corm_settings_test_") and len(self.schema) == 51
connection = psycopg2.connect(self.dsn, connect_timeout=3)
try:
with connection, connection.cursor() as cursor:
cursor.execute(sql.SQL("DROP SCHEMA {} CASCADE").format(sql.Identifier(self.schema)))
finally:
connection.close()
def test_jsonb_roundtrip_update_and_duplicate_import(self):
self.assertEqual(self.store.import_rows(self.rows, "test"), 1)
self.assertEqual(self.store.read(), (self.rows, 1))
with self.assertRaises(SettingsAlreadyInitialized):
self.store.import_rows(list(reversed(self.rows)), "test")
self.assertEqual(self.store.read(), (self.rows, 1))
changed = self.rows + [["APP_SETTINGS", "shipping_fee", "4000"]]
self.assertEqual(self.store.write(changed, 1), 2)
self.assertEqual(self.store.read(), (changed, 2))
def test_two_connections_cannot_silently_overwrite_each_other(self):
self.store.import_rows(self.rows, "test")
barrier = threading.Barrier(2)
def save(value):
rows, version = self.store.read()
rows.append(["APP_SETTINGS", "shipping_fee", value])
barrier.wait(timeout=3)
try:
self.store.write(rows, version)
return "saved"
except SettingsConflict:
return "conflict"
with ThreadPoolExecutor(max_workers=2) as executor:
self.assertCountEqual(list(executor.map(save, ["4000", "5000"])), ["saved", "conflict"])
self.assertEqual(self.store.read()[1], 2)
def test_failed_import_rolls_back_table_and_data(self):
with self.assertRaises(psycopg2.IntegrityError):
self.store.import_rows(self.rows, None)
with self.assertRaises(SettingsNotInitialized):
self.store.read()
def test_history_import_full_payload_ids_filters_and_deletion(self):
sms = {"timestamp": "2026-10-22 12:00:00", "items": [{"code": "TEST", "qty": 2}],
"previewText": "문자\n두 줄", "smsOpts": {"discount": True}}
manual = [{"timestamp": "2020-01-01 10:00:00", "total_amount": "누락"},
{"timestamp": "2026-10-22 12:00:00", "total_amount": 3500}]
bundle = {"kind": "corm-history", "sms": [sms], "manual": manual}
self.histories.import_all(bundle, "test")
self.histories.check_ready()
with self.assertRaises(SettingsAlreadyInitialized):
self.histories.import_all(bundle, "test")
saved = self.histories.list_records("sms")
self.assertEqual(saved[0]["smsOpts"], sms["smsOpts"])
self.assertEqual(len(self.histories.list_records("manual", "2026-10-22")), 1)
self.assertEqual(len(self.histories.export_all()["manual"]), 2)
new_id = self.histories.add("sms", {**sms, "timestamp": "2026-10-22 12:00:01"})
self.histories.delete("sms", saved[0]["id"])
self.assertEqual([r["id"] for r in self.histories.list_records("sms")], [new_id])
self.histories.clear_sms()
self.assertEqual(self.histories.list_records("sms"), [])
with self.assertRaises(SettingsAlreadyInitialized):
self.histories.import_all(bundle, "test")
def test_manual_queue_conflicts_export_and_atomic_summary(self):
self.histories.import_all({"kind": "corm-history", "sms": [], "manual": []}, "test")
cells = ["2026-10-22 10:00:00"] + [""] * 14
self.manual.import_source({"kind": "corm-manual-orders", "headers": HEADERS, "rows": [cells]}, "test")
record = self.manual.list_rows()["rows"][0]
change = {"id": record["id"], "column": 3, "value": "이름", "previous_value": ""}
self.manual.patch_cells([change])
with self.assertRaises(CellConflict):
self.manual.patch_cells([{**change, "value": "다른 이름"}])
# A lost successful response can be retried without reporting a conflict.
self.manual.patch_cells([change])
key = self.manual.append([cells], {"timestamp": cells[0], "customer_name": "테스트", "total_amount": 3500})
histories = self.histories.list_records("manual")
self.assertEqual(histories[0]["manual_order_key"], key)
data, export_id = self.manual.export_and_archive("manual.xlsx")
self.assertEqual(self.manual.exports(export_id), ("manual.xlsx", data))
self.assertEqual(self.manual.list_rows()["rows"], [])
self.assertEqual(len(self.manual.exports()), 1)
if __name__ == "__main__":
unittest.main()
+15
View File
@@ -1,4 +1,19 @@
[
{
"version": "v9.1",
"date": "2026.09.03",
"sections": [
{
"items": [
"CS 기록 메뉴 및 CS 메모 기능 추가: 달력 날짜별 고객 상담·물류센터 전달 내용 기록, 완료 처리, 선택 내용 복사, 다음 업무일 이월 기능 제공",
"항상 확인 메모 기능 추가: 폴더별 분류, 7가지 폴더 색상, 2열 포스트잇 화면, 메모·폴더 추가·수정·삭제 팝업 제공",
"CS 기록과 상시 메모를 PostgreSQL 서버에 저장하고 엑셀로 내려받기·업로드할 수 있도록 구성",
"New CS에서 누락·파손·불량·오배송 발주 입력 시 해당 내용을 CS 기록에 자동 저장"
],
"type": "added"
}
]
},
{
"version": "v9.0",
"date": "2026.08.25",