hjjeong
12ea68616d
- getList SQL: 라인 집계에 product_summary(=PRODUCT_NAME, contract_item.product distinct join) / return_reason_summary 추가. wace는 헤더 product 폐지·라인으로 이동(운영 90건 contract_mgmt.product NULL) → 라인 집계로 그리드 표시 - GRID_COLUMNS 3개 추가: 제품구분 / 국내해외 / 반납사유 - searchForm.search_partName 필드 추가(초기화 포함). 검색 폼 UI는 PartSelect mode=partName 이미 존재 - docs/migration/sales/01-estimate-verify.md: wace ↔ RPS 항목 매핑 / 운영 데이터 코드 체계 / 갭 우선순위 - scripts/verify-estimate.sql: BEGIN/ROLLBACK 5개 시나리오 (등록·수정·G1·수주취소·그리드) 자동 검증 Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
245 lines
11 KiB
PL/PgSQL
245 lines
11 KiB
PL/PgSQL
-- ============================================================
|
|
-- 견적관리 자동 검증 SQL (BEGIN/ROLLBACK 시나리오)
|
|
-- 사용법:
|
|
-- PGPASSWORD='vexplor0909!!' psql -h 211.115.91.141 -p 11134 -U postgres \
|
|
-- -d vexplor_rps -f scripts/verify-estimate.sql
|
|
--
|
|
-- 모든 시나리오는 트랜잭션 안에서 실행 후 ROLLBACK — DB 영향 0.
|
|
-- ============================================================
|
|
|
|
\echo ''
|
|
\echo '================== Estimate 검증 시작 =================='
|
|
|
|
-- ─────────────────────────────────────────────────────────
|
|
-- 시나리오 0: 사전 카운트
|
|
-- ─────────────────────────────────────────────────────────
|
|
|
|
\echo ''
|
|
\echo '[0] 사전 카운트 ============================'
|
|
SELECT 'contract_mgmt' AS tbl, COUNT(*) AS cnt FROM contract_mgmt
|
|
UNION ALL SELECT 'contract_item', COUNT(*) FROM contract_item WHERE status='ACTIVE'
|
|
UNION ALL SELECT 'contract_item_serial', COUNT(*) FROM contract_item_serial WHERE status='ACTIVE'
|
|
UNION ALL SELECT 'project_mgmt', COUNT(*) FROM project_mgmt;
|
|
|
|
|
|
-- ─────────────────────────────────────────────────────────
|
|
-- 시나리오 1: 신규 견적요청 등록 (헤더 + 라인 + 시리얼)
|
|
-- ─────────────────────────────────────────────────────────
|
|
|
|
\echo ''
|
|
\echo '[1] 신규 견적요청 등록 시나리오 =========================='
|
|
BEGIN;
|
|
|
|
-- 1-a) 채번 룰 검증: {YY}C-{NNNN} 다음 번호
|
|
SELECT '예상 contract_no' AS info, '26C-' || LPAD(
|
|
(COALESCE((SELECT MAX(SUBSTRING(contract_no FROM '\d{4}$')::int)
|
|
FROM contract_mgmt WHERE contract_no LIKE '26C-%'), 0) + 1)::text, 4, '0') AS expected_no;
|
|
|
|
-- 1-b) 헤더 INSERT
|
|
INSERT INTO contract_mgmt (
|
|
objid, contract_no, category_cd, area_cd, customer_objid, paid_type,
|
|
receipt_date, contract_currency, exchange_rate, approval_required,
|
|
is_direct_order, writer, regdate
|
|
) VALUES (
|
|
'CM-VERIFY-001', '26C-9991', '0001791', '0001220', 'C_0000005546', 'paid',
|
|
'2026-05-09', '0001566', NULL, 'N',
|
|
'N', 'admin', NOW()
|
|
);
|
|
|
|
-- 1-c) 라인 INSERT
|
|
INSERT INTO contract_item (
|
|
objid, contract_objid, seq, product, part_objid, part_no, part_name,
|
|
quantity, due_date, return_reason, customer_request,
|
|
regdate, writer, status
|
|
) VALUES (
|
|
'CI-VERIFY-001', 'CM-VERIFY-001', 1, '0001793', '1868255719', '10INSQURE', '10인치 사각척',
|
|
2, '2026-05-15', NULL, '정합성 검증',
|
|
NOW(), 'admin', 'ACTIVE'
|
|
);
|
|
|
|
-- 1-d) 시리얼 INSERT
|
|
INSERT INTO contract_item_serial (objid, item_objid, seq, serial_no, regdate, writer, status)
|
|
VALUES ('CIS-VERIFY-001', 'CI-VERIFY-001', 1, 'SN-001', NOW(), 'admin', 'ACTIVE'),
|
|
('CIS-VERIFY-002', 'CI-VERIFY-001', 2, 'SN-002', NOW(), 'admin', 'ACTIVE');
|
|
|
|
-- 검증
|
|
\echo ' → 헤더/라인/시리얼 확인:'
|
|
SELECT cm.contract_no, ci.product, ci.part_no, ci.quantity,
|
|
(SELECT COUNT(*) FROM contract_item_serial WHERE item_objid=ci.objid AND status='ACTIVE') AS serial_cnt
|
|
FROM contract_mgmt cm
|
|
JOIN contract_item ci ON ci.contract_objid=cm.objid AND ci.status='ACTIVE'
|
|
WHERE cm.objid='CM-VERIFY-001';
|
|
|
|
ROLLBACK;
|
|
\echo ' → ROLLBACK 완료'
|
|
|
|
|
|
-- ─────────────────────────────────────────────────────────
|
|
-- 시나리오 2: 견적요청 수정 (라인 1→2 확장 + UPSERT)
|
|
-- ─────────────────────────────────────────────────────────
|
|
|
|
\echo ''
|
|
\echo '[2] 견적요청 수정(라인 확장) 시나리오 =========================='
|
|
BEGIN;
|
|
|
|
-- 26C-0801: 기존 ACTIVE 라인 1건. upsertItems 흐름 시뮬레이션.
|
|
|
|
-- 2-a) 기존 라인 비활성
|
|
UPDATE contract_item SET status='INACTIVE', chgdate=NOW(), chg_user_id='admin'
|
|
WHERE contract_objid='-1778190592';
|
|
|
|
-- 2-b) 기존 라인 1건 ON CONFLICT로 ACTIVE 복구 (objid 동일)
|
|
INSERT INTO contract_item (
|
|
objid, contract_objid, seq, product, part_objid, part_no, part_name,
|
|
quantity, due_date, regdate, writer, status
|
|
) VALUES (
|
|
'-701833325', '-1778190592', 1, '0001793', '1868255719', '10INSQURE', '10인치 사각척',
|
|
2, '2026-05-15', NOW(), 'admin', 'ACTIVE'
|
|
)
|
|
ON CONFLICT (objid) DO UPDATE SET status='ACTIVE', quantity=EXCLUDED.quantity, chgdate=NOW();
|
|
|
|
-- 2-c) 새 라인 추가
|
|
INSERT INTO contract_item (
|
|
objid, contract_objid, seq, product, part_objid, part_no, part_name,
|
|
quantity, regdate, writer, status
|
|
) VALUES (
|
|
'CI-VERIFY-NEW', '-1778190592', 2, '0001807', '1868255719', '10INSQURE', '10인치 사각척',
|
|
3, NOW(), 'admin', 'ACTIVE'
|
|
);
|
|
|
|
-- 검증
|
|
\echo ' → ACTIVE 라인 (수정 후 2건이어야):'
|
|
SELECT seq, product, quantity, status FROM contract_item
|
|
WHERE contract_objid='-1778190592' AND status='ACTIVE' ORDER BY seq;
|
|
|
|
ROLLBACK;
|
|
\echo ' → ROLLBACK 완료'
|
|
|
|
|
|
-- ─────────────────────────────────────────────────────────
|
|
-- 시나리오 3: 수주확정 → project_mgmt 자동생성 (G1)
|
|
-- ─────────────────────────────────────────────────────────
|
|
|
|
\echo ''
|
|
\echo '[3] 수주확정 G1 시나리오 =========================='
|
|
BEGIN;
|
|
|
|
\echo ' → BEFORE project_mgmt:'
|
|
SELECT COUNT(*) AS before_cnt FROM project_mgmt;
|
|
|
|
-- 3-a) UPDATE contract_result='0000964' (수주)
|
|
UPDATE contract_mgmt SET contract_result='0000964', chg_user_id='admin'
|
|
WHERE objid='-1778190592';
|
|
|
|
-- 3-b) project_no 채번 (직접 인라인 SQL)
|
|
WITH meta AS (
|
|
SELECT
|
|
CASE CC_CAT.code_name
|
|
WHEN '오버홀' THEN 'O' WHEN '개조' THEN 'M' WHEN '개발' THEN 'D'
|
|
WHEN '견적' THEN 'Q' WHEN '수리' THEN 'R' WHEN '판매' THEN 'S' ELSE 'T'
|
|
END AS cat_abbr,
|
|
CASE CC_PRD.code_name
|
|
WHEN 'Machine' THEN 'MC' WHEN 'A/S' THEN 'AS' WHEN 'D/S' THEN 'DS'
|
|
WHEN 'B/S' THEN 'BS' WHEN 'C/T' THEN 'CT' WHEN 'A/C' THEN 'AC'
|
|
WHEN 'W/M' THEN 'WM' WHEN '기타' THEN '기타'
|
|
ELSE REPLACE(COALESCE(CC_PRD.code_name, ''), '/', '')
|
|
END AS prd_abbr,
|
|
TO_CHAR(CURRENT_DATE, 'YYMMDD') AS ymd
|
|
FROM (SELECT 1) X
|
|
LEFT JOIN comm_code CC_CAT ON CC_CAT.code_id = '0001791' AND CC_CAT.status='active'
|
|
LEFT JOIN comm_code CC_PRD ON CC_PRD.code_id = '0001793' AND CC_PRD.status='active'
|
|
)
|
|
SELECT '예상 project_no' AS info,
|
|
m.cat_abbr || '-' || m.prd_abbr || '-' || m.ymd || '-' ||
|
|
LPAD(COALESCE((SELECT MAX(SUBSTRING(project_no FROM '\d{3}$')::int) + 1
|
|
FROM project_mgmt
|
|
WHERE project_no LIKE m.cat_abbr || '-' || m.prd_abbr || '-' || m.ymd || '-%'), 1)::text, 3, '0')
|
|
AS expected_project_no
|
|
FROM meta m;
|
|
|
|
-- 3-c) project_mgmt INSERT (라인 단위)
|
|
INSERT INTO project_mgmt (
|
|
objid, contract_objid, category_cd, customer_objid, product,
|
|
customer_project_name, status_cd, due_date, contract_currency, regdate, writer,
|
|
contract_no, contract_result, project_no, is_temp,
|
|
part_objid, part_no, part_name, quantity, contract_item_objid
|
|
)
|
|
SELECT
|
|
'PJ-VERIFY-001'::varchar, T.objid, T.category_cd, T.customer_objid, '0001793'::varchar,
|
|
T.customer_project_name, T.status_cd, NULL, T.contract_currency, NOW(), T.writer,
|
|
T.contract_no, T.contract_result,
|
|
'R-CT-' || TO_CHAR(CURRENT_DATE, 'YYMMDD') || '-' ||
|
|
LPAD(COALESCE((SELECT MAX(SUBSTRING(project_no FROM '\d{3}$')::int) + 1
|
|
FROM project_mgmt
|
|
WHERE project_no LIKE 'R-CT-' || TO_CHAR(CURRENT_DATE, 'YYMMDD') || '-%'), 1)::text, 3, '0'),
|
|
'1', '1868255719', '10INSQURE', '10인치 사각척', '2', '-701833325'
|
|
FROM contract_mgmt T WHERE T.objid='-1778190592';
|
|
|
|
\echo ' → AFTER project_mgmt:'
|
|
SELECT COUNT(*) AS after_cnt FROM project_mgmt;
|
|
\echo ' → 새 project_mgmt 행:'
|
|
SELECT objid, project_no, contract_objid, product, quantity FROM project_mgmt WHERE objid='PJ-VERIFY-001';
|
|
|
|
ROLLBACK;
|
|
\echo ' → ROLLBACK 완료'
|
|
|
|
|
|
-- ─────────────────────────────────────────────────────────
|
|
-- 시나리오 4: 수주취소 (cancel_qty 입력만, contract_result 미변경)
|
|
-- ─────────────────────────────────────────────────────────
|
|
|
|
\echo ''
|
|
\echo '[4] 수주취소 시나리오 =========================='
|
|
BEGIN;
|
|
|
|
\echo ' → BEFORE 26C-0801 contract_result:'
|
|
SELECT contract_no, contract_result FROM contract_mgmt WHERE contract_no='26C-0801';
|
|
|
|
-- 라인 cancel_qty UPDATE만
|
|
UPDATE contract_item SET cancel_qty='1', chgdate=NOW(), chg_user_id='admin'
|
|
WHERE contract_objid='-1778190592' AND status='ACTIVE';
|
|
|
|
\echo ' → AFTER 라인 cancel_qty:'
|
|
SELECT objid, quantity, cancel_qty FROM contract_item
|
|
WHERE contract_objid='-1778190592' AND status='ACTIVE';
|
|
|
|
\echo ' → contract_result 미변경 검증:'
|
|
SELECT contract_no, contract_result FROM contract_mgmt WHERE contract_no='26C-0801';
|
|
|
|
ROLLBACK;
|
|
\echo ' → ROLLBACK 완료'
|
|
|
|
|
|
-- ─────────────────────────────────────────────────────────
|
|
-- 시나리오 5: 그리드 SQL (V1 컬럼) 정합성
|
|
-- ─────────────────────────────────────────────────────────
|
|
|
|
\echo ''
|
|
\echo '[5] 그리드 V1 컬럼 (제품구분/국내해외/반납사유) 검증 =========================='
|
|
SELECT
|
|
T.contract_no,
|
|
COALESCE(CI_AGG.product_summary, CC_PRD.code_name) AS product_name,
|
|
CC_AREA.code_name AS area_name,
|
|
CI_AGG.return_reason_summary AS return_reason_summary,
|
|
CI_AGG.item_count AS line_cnt
|
|
FROM contract_mgmt T
|
|
LEFT JOIN comm_code CC_PRD ON CC_PRD.code_id = T.product AND CC_PRD.status='active'
|
|
LEFT JOIN comm_code CC_AREA ON CC_AREA.code_id = T.area_cd AND CC_AREA.status='active'
|
|
LEFT JOIN (
|
|
SELECT CI.contract_objid,
|
|
COUNT(*) AS item_count,
|
|
STRING_AGG(DISTINCT CC_PRDI.code_name, ', ') FILTER (WHERE CC_PRDI.code_name IS NOT NULL) AS product_summary,
|
|
STRING_AGG(DISTINCT CC_RR.code_name, ', ') FILTER (WHERE CC_RR.code_name IS NOT NULL) AS return_reason_summary
|
|
FROM contract_item CI
|
|
LEFT JOIN comm_code CC_PRDI ON CC_PRDI.code_id = CI.product AND CC_PRDI.status='active'
|
|
LEFT JOIN comm_code CC_RR ON CC_RR.code_id = CI.return_reason AND CC_RR.status='active'
|
|
WHERE CI.status='ACTIVE'
|
|
GROUP BY CI.contract_objid
|
|
) CI_AGG ON CI_AGG.contract_objid = T.objid
|
|
WHERE T.contract_no IN ('26C-0801','26C-0800','26C-0797','26C-0796','26C-0795','26C-0791','26C-0788')
|
|
ORDER BY T.contract_no DESC;
|
|
|
|
|
|
\echo ''
|
|
\echo '================== Estimate 검증 끝 (모두 ROLLBACK) =================='
|