king
b583627bd3
- 달력 다운로드 아래 '말레이시아 재고 차감' 버튼. 선택 날짜 출고를 말레이시아 재고관리에 OUT 으로 반영(movement_date=해당 날짜) · 낱개(MT/MX/MZ)=낱개 OUT, 콤보(MY-)=세트 OUT(재고관리가 BOM 분해) · _N 배수 반영, MD-(뚜껑) 제외 - 날짜당 1회만: dispatch_stock_deductions 마커로 재차감 차단(이중 출고 방지) · 마커 선점 후 반영, 콤보 BOM 누락은 사전검증으로 차단 - export.split_singles_combos, db 차감 가드 메서드, 라우터 POST /dispatch/stock-deduct - 스키마: dispatch_stock_deductions (init + 마이그레이션 dispatch_add_deductions.sql) - 이미 차감된 날짜는 버튼 비활성 + 안내 Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
172 lines
9.6 KiB
PL/PgSQL
172 lines
9.6 KiB
PL/PgSQL
-- =====================================================================
|
|
-- dispatch_db 초기화 스크립트 (PostgreSQL) — 말레이시아 TikTok 출고관리
|
|
-- =====================================================================
|
|
-- 멱등(idempotent): 여러 번 실행해도 안전. 기존 데이터를 삭제하지 않는다.
|
|
--
|
|
-- ⚠️ 실행 전 사용자 승인 + 백업 확인 필수. DROP/TRUNCATE 없음.
|
|
--
|
|
-- 실행 방법 (운영 PostgreSQL 컨테이너명: postgres-db):
|
|
--
|
|
-- 1) DB / 역할 / 스키마 생성 (superuser 로 1회)
|
|
-- read -s -p "dispatch_app password: " APP_PWD; echo
|
|
-- docker exec -i postgres-db psql -U postgres \
|
|
-- -v app_password="$APP_PWD" \
|
|
-- < scripts/sql/dispatch_db_init.sql
|
|
--
|
|
-- 2) main-app .env 에 연결 정보 등록
|
|
-- DISPATCH_DB_URL=postgresql://dispatch_app:<APP_PWD>@postgres-db:5432/dispatch_db
|
|
--
|
|
-- 3) main-app 재기동
|
|
-- cd /opt/www/main && docker compose up -d --build web
|
|
--
|
|
-- 주의:
|
|
-- - 기존 DB 가 있으면 DROP 하지 않는다(CREATE DATABASE 는 미존재 시에만).
|
|
-- - 비밀번호는 절대 Git 에 커밋하지 않는다. psql -v 로만 전달.
|
|
-- - 받는 사람 정보(이름/전화/주소)는 출고 작업 카드 표시 + 출고 엑셀 생성을
|
|
-- 위해 박스 단위로 보관한다(개인정보 — 접근/보관 정책 검토 대상).
|
|
-- - 작업 기준 키: order_id / package_id / tracking_id / seller_sku / quantity.
|
|
-- =====================================================================
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
-- DB 가 없을 때만 생성
|
|
SELECT 'CREATE DATABASE dispatch_db ENCODING ''UTF8'' TEMPLATE template0'
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'dispatch_db')
|
|
\gexec
|
|
|
|
-- 앱 전용 로그인 역할
|
|
SELECT 'CREATE ROLE dispatch_app LOGIN PASSWORD ' || quote_literal(:'app_password')
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'dispatch_app')
|
|
\gexec
|
|
|
|
-- 항상 최신 비밀번호로 동기화
|
|
SELECT 'ALTER ROLE dispatch_app WITH LOGIN PASSWORD ' || quote_literal(:'app_password')
|
|
\gexec
|
|
|
|
GRANT CONNECT ON DATABASE dispatch_db TO dispatch_app;
|
|
|
|
-- dispatch_db 컨텍스트로 전환
|
|
\connect dispatch_db
|
|
|
|
-- ── updated_at 자동 갱신 트리거 함수 (멱등: CREATE OR REPLACE) ──
|
|
CREATE OR REPLACE FUNCTION dispatch_set_updated_at() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.updated_at := now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 1) 출고 배치 (하루 1회~여러 회 업로드 단위)
|
|
-- 같은 날짜/플랫폼이라도 batch_name 으로 구분해 여러 배치 생성 가능.
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS dispatch_batches (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
platform TEXT NOT NULL DEFAULT 'TikTok',
|
|
dispatch_date DATE NOT NULL,
|
|
batch_name TEXT NOT NULL DEFAULT '',
|
|
picking_pdf_path TEXT NOT NULL DEFAULT '',
|
|
label_pdf_path TEXT NOT NULL DEFAULT '',
|
|
order_export_path TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_dispatch_batches_date ON dispatch_batches (dispatch_date DESC);
|
|
|
|
DROP TRIGGER IF EXISTS trg_dispatch_batches_updated ON dispatch_batches;
|
|
CREATE TRIGGER trg_dispatch_batches_updated
|
|
BEFORE UPDATE ON dispatch_batches
|
|
FOR EACH ROW EXECUTE FUNCTION dispatch_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 2) 출고 박스 (1박스 = 1행 = 1작업카드)
|
|
-- 묶음 기준: package_id > tracking_id > order_id (파서가 결정).
|
|
-- 받는 사람(recipient_*) 정보 보관 — 작업 카드 표시 + 출고 엑셀 생성용.
|
|
-- seq 는 배치 내 박스 표시 순번(No).
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS dispatch_parcels (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
batch_id BIGINT NOT NULL REFERENCES dispatch_batches(id) ON DELETE CASCADE,
|
|
seq INTEGER NOT NULL DEFAULT 0,
|
|
order_id TEXT NOT NULL DEFAULT '',
|
|
package_id TEXT NOT NULL DEFAULT '',
|
|
tracking_id TEXT NOT NULL DEFAULT '',
|
|
shipping_provider TEXT NOT NULL DEFAULT '',
|
|
recipient_name TEXT NOT NULL DEFAULT '',
|
|
recipient_phone TEXT NOT NULL DEFAULT '',
|
|
recipient_address TEXT NOT NULL DEFAULT '',
|
|
product_ready BOOLEAN NOT NULL DEFAULT FALSE,
|
|
packed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
label_attached BOOLEAN NOT NULL DEFAULT FALSE,
|
|
handed_to_kagayaku BOOLEAN NOT NULL DEFAULT FALSE,
|
|
courier_scanned BOOLEAN NOT NULL DEFAULT FALSE,
|
|
memo TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_dispatch_parcels_batch ON dispatch_parcels (batch_id, seq);
|
|
CREATE INDEX IF NOT EXISTS idx_dispatch_parcels_order ON dispatch_parcels (order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_dispatch_parcels_tracking ON dispatch_parcels (tracking_id);
|
|
CREATE INDEX IF NOT EXISTS idx_dispatch_parcels_package ON dispatch_parcels (package_id);
|
|
|
|
DROP TRIGGER IF EXISTS trg_dispatch_parcels_updated ON dispatch_parcels;
|
|
CREATE TRIGGER trg_dispatch_parcels_updated
|
|
BEFORE UPDATE ON dispatch_parcels
|
|
FOR EACH ROW EXECUTE FUNCTION dispatch_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 3) 박스 안 상품 (1박스 N상품, 같은 SKU 는 수량 합산되어 1행)
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS dispatch_items (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
parcel_id BIGINT NOT NULL REFERENCES dispatch_parcels(id) ON DELETE CASCADE,
|
|
seller_sku TEXT NOT NULL DEFAULT '',
|
|
product_name TEXT NOT NULL DEFAULT '',
|
|
quantity INTEGER NOT NULL DEFAULT 1
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_dispatch_items_parcel ON dispatch_items (parcel_id);
|
|
CREATE INDEX IF NOT EXISTS idx_dispatch_items_sku ON dispatch_items (seller_sku);
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 4) 작업 상태 변경 로그 (감사/추적용)
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS dispatch_logs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
parcel_id BIGINT NOT NULL REFERENCES dispatch_parcels(id) ON DELETE CASCADE,
|
|
action TEXT NOT NULL DEFAULT '',
|
|
old_value TEXT NOT NULL DEFAULT '',
|
|
new_value TEXT NOT NULL DEFAULT '',
|
|
worker_name TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_dispatch_logs_parcel ON dispatch_logs (parcel_id);
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 5) 말레이시아 재고 차감 1회성 기록 (날짜 단위 멱등 가드)
|
|
-- 그날 출고를 말레이시아 재고관리(OUT)로 1회만 반영. 존재 시 재차감 차단.
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS dispatch_stock_deductions (
|
|
dispatch_date DATE PRIMARY KEY,
|
|
warehouse_code TEXT NOT NULL DEFAULT '',
|
|
single_lines INTEGER NOT NULL DEFAULT 0,
|
|
combo_lines INTEGER NOT NULL DEFAULT 0,
|
|
worker_name TEXT NOT NULL DEFAULT '',
|
|
deducted_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 6) 권한 (dispatch_app: CRUD only)
|
|
-- ════════════════════════════════════════════════════════════
|
|
GRANT USAGE ON SCHEMA public TO dispatch_app;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON
|
|
dispatch_batches, dispatch_parcels, dispatch_items, dispatch_logs,
|
|
dispatch_stock_deductions
|
|
TO dispatch_app;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dispatch_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dispatch_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT USAGE, SELECT ON SEQUENCES TO dispatch_app;
|
|
|
|
SELECT 'dispatch_db ready' AS status;
|