king
d9ea7550b0
- 출고방식 선택지에 "파렛트" 추가 - 신규/수정 폼에서 상태·문서번호 입력 제거 (status는 작성중 기본 + 취소처리로만 변경) - document_no DB 컬럼 제거: init.sql 반영 + 기존 DB용 002 드롭 마이그레이션(승인 후 실행) - 폼: 공통 헤더(좌) / 품목 라인(우) 2열 배치 - 상세: 엑셀·상태변경 버튼 삭제, ◀◀ 달력(검정) 좌측, 수정 우측 정렬, 문서번호 표시 제거 Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
200 lines
11 KiB
PL/PgSQL
200 lines
11 KiB
PL/PgSQL
-- =====================================================================
|
|
-- cupang_db 초기화 스크립트 (PostgreSQL) — 쿠팡 밀크런 모듈
|
|
-- =====================================================================
|
|
-- 멱등(idempotent): 여러 번 실행해도 안전. 기존 데이터를 삭제하지 않는다.
|
|
--
|
|
-- ⚠️ 실행 전 사용자 승인 + 백업 확인 필수. DROP/TRUNCATE 없음.
|
|
--
|
|
-- 실행 방법 (운영 PostgreSQL 컨테이너명: postgres-db):
|
|
--
|
|
-- 1) DB / 역할 / 스키마 생성 (superuser 로 1회)
|
|
-- read -s -p "cupang_app password: " APP_PWD; echo
|
|
-- docker exec -i postgres-db psql -U postgres \
|
|
-- -v app_password="$APP_PWD" \
|
|
-- < scripts/sql/cupang_db_init.sql
|
|
--
|
|
-- 2) main-app .env 에 연결 정보 등록
|
|
-- CUPANG_DB_URL=postgresql://cupang_app:<APP_PWD>@postgres-db:5432/cupang_db
|
|
--
|
|
-- 3) main-app 재기동
|
|
-- cd /opt/www/main && docker compose up -d --build
|
|
--
|
|
-- 주의:
|
|
-- - 기존 DB 가 있으면 DROP 하지 않는다(CREATE DATABASE 는 미존재 시에만).
|
|
-- - 비밀번호는 절대 Git 에 커밋하지 않는다. psql -v 로만 전달.
|
|
-- - itemcode_db 는 이 스크립트가 건드리지 않는다(상품은 읽기 전용 참조).
|
|
-- =====================================================================
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
-- DB 가 없을 때만 생성
|
|
SELECT 'CREATE DATABASE cupang_db ENCODING ''UTF8'' TEMPLATE template0'
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'cupang_db')
|
|
\gexec
|
|
|
|
-- 앱 전용 로그인 역할 (expense_app 패턴과 동일)
|
|
SELECT 'CREATE ROLE cupang_app LOGIN PASSWORD ' || quote_literal(:'app_password')
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cupang_app')
|
|
\gexec
|
|
|
|
-- 항상 최신 비밀번호로 동기화
|
|
SELECT 'ALTER ROLE cupang_app WITH LOGIN PASSWORD ' || quote_literal(:'app_password')
|
|
\gexec
|
|
|
|
GRANT CONNECT ON DATABASE cupang_db TO cupang_app;
|
|
|
|
-- cupang_db 컨텍스트로 전환
|
|
\connect cupang_db
|
|
|
|
-- ── updated_at 자동 갱신 트리거 함수 (멱등: CREATE OR REPLACE) ──
|
|
CREATE OR REPLACE FUNCTION cupang_set_updated_at() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.updated_at := now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 1) 입고센터
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS cupang_centers (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
DROP TRIGGER IF EXISTS trg_cupang_centers_updated ON cupang_centers;
|
|
CREATE TRIGGER trg_cupang_centers_updated
|
|
BEFORE UPDATE ON cupang_centers
|
|
FOR EACH ROW EXECUTE FUNCTION cupang_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 2) 박스 입수량 규칙
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS cupang_box_rules (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
product_code TEXT NOT NULL UNIQUE,
|
|
product_name_snapshot TEXT,
|
|
box_name TEXT NOT NULL DEFAULT '쿠팡박스',
|
|
units_per_box INTEGER NOT NULL CHECK (units_per_box > 0),
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
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_cupang_box_rules_code ON cupang_box_rules (product_code);
|
|
|
|
DROP TRIGGER IF EXISTS trg_cupang_box_rules_updated ON cupang_box_rules;
|
|
CREATE TRIGGER trg_cupang_box_rules_updated
|
|
BEFORE UPDATE ON cupang_box_rules
|
|
FOR EACH ROW EXECUTE FUNCTION cupang_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 2-b) 제품명 카탈로그 (itemcode_db 에서 가져와 등록 → 폼 드롭다운 소스)
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS cupang_products (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
product_code TEXT NOT NULL UNIQUE,
|
|
product_name TEXT NOT NULL,
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_cupang_products_name ON cupang_products (product_name);
|
|
|
|
DROP TRIGGER IF EXISTS trg_cupang_products_updated ON cupang_products;
|
|
CREATE TRIGGER trg_cupang_products_updated
|
|
BEFORE UPDATE ON cupang_products
|
|
FOR EACH ROW EXECUTE FUNCTION cupang_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 3) 출고 묶음 (헤더)
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS cupang_shipments (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
created_by TEXT NOT NULL,
|
|
document_date DATE NOT NULL,
|
|
ship_date DATE NOT NULL,
|
|
center_arrival_date DATE NOT NULL,
|
|
center_id BIGINT REFERENCES cupang_centers(id),
|
|
center_name_snapshot TEXT NOT NULL DEFAULT '',
|
|
ship_method TEXT NOT NULL DEFAULT '택배',
|
|
outbound_summary TEXT NOT NULL DEFAULT '',
|
|
worker TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT '작성중',
|
|
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_cupang_ship_doc_date ON cupang_shipments (document_date);
|
|
CREATE INDEX IF NOT EXISTS idx_cupang_ship_ship_date ON cupang_shipments (ship_date);
|
|
CREATE INDEX IF NOT EXISTS idx_cupang_ship_arr_date ON cupang_shipments (center_arrival_date);
|
|
CREATE INDEX IF NOT EXISTS idx_cupang_ship_status ON cupang_shipments (status);
|
|
CREATE INDEX IF NOT EXISTS idx_cupang_ship_center ON cupang_shipments (center_id);
|
|
|
|
DROP TRIGGER IF EXISTS trg_cupang_shipments_updated ON cupang_shipments;
|
|
CREATE TRIGGER trg_cupang_shipments_updated
|
|
BEFORE UPDATE ON cupang_shipments
|
|
FOR EACH ROW EXECUTE FUNCTION cupang_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 4) 출고 라인
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS cupang_shipment_lines (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
shipment_id BIGINT NOT NULL REFERENCES cupang_shipments(id) ON DELETE CASCADE,
|
|
line_no INTEGER NOT NULL,
|
|
product_code TEXT NOT NULL,
|
|
product_name_snapshot TEXT NOT NULL,
|
|
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
|
box_rule_id BIGINT REFERENCES cupang_box_rules(id),
|
|
units_per_box INTEGER,
|
|
calculated_boxes INTEGER,
|
|
remainder_units INTEGER,
|
|
manual_box_text TEXT NOT NULL DEFAULT '',
|
|
memo TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (shipment_id, line_no)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_cupang_lines_shipment ON cupang_shipment_lines (shipment_id);
|
|
CREATE INDEX IF NOT EXISTS idx_cupang_lines_code ON cupang_shipment_lines (product_code);
|
|
|
|
DROP TRIGGER IF EXISTS trg_cupang_lines_updated ON cupang_shipment_lines;
|
|
CREATE TRIGGER trg_cupang_lines_updated
|
|
BEFORE UPDATE ON cupang_shipment_lines
|
|
FOR EACH ROW EXECUTE FUNCTION cupang_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 5) 초기 입고센터 seed (멱등: ON CONFLICT DO NOTHING)
|
|
-- sort_order 는 목록 순서대로 부여.
|
|
-- ════════════════════════════════════════════════════════════
|
|
INSERT INTO cupang_centers (name, sort_order) VALUES
|
|
('대구3', 1), ('인천32', 2), ('이천1', 3), ('인천42', 4), ('인천26', 5),
|
|
('인천16', 6), ('인천28', 7), ('안성8', 8), ('천안8(RC)', 9), ('시흥2', 10),
|
|
('인천36', 11), ('MGMH5', 12), ('XRC10(RC)', 13), ('인천14', 14), ('경기광주5', 15),
|
|
('경기광주3', 16), ('XRC06(RC)', 17), ('용인1', 18), ('인천30', 19), ('마장1', 20),
|
|
('안성4', 21), ('대구6', 22), ('전라광주2', 23), ('창원1', 24), ('고양1', 25),
|
|
('동탄1', 26), ('이천4', 27), ('XRC09(RC)', 28)
|
|
ON CONFLICT (name) DO NOTHING;
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 6) 권한 (cupang_app: CRUD only)
|
|
-- ════════════════════════════════════════════════════════════
|
|
GRANT USAGE ON SCHEMA public TO cupang_app;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON
|
|
cupang_centers, cupang_box_rules, cupang_products,
|
|
cupang_shipments, cupang_shipment_lines
|
|
TO cupang_app;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO cupang_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO cupang_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT USAGE, SELECT ON SEQUENCES TO cupang_app;
|
|
|
|
SELECT 'cupang_db ready' AS status;
|