wixon/jongryangje
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
jongryangje/writable/database/seed_realistic_data.sql

263 lines
25 KiB
MySQL
Raw Permalink Normal View History

대구 남구청 실제형 시드 데이터 추가 (유저 제외) - 지정판매소 8개, 판매대행소 3개, 업체 4개, 담당자 5명 - 무료대상자 8명, 봉투단가 14건, 포장단위 12건 - 발주 5건(15품목), 입고 15건, 재고 12품목 - 주문접수 7건, 판매/반품 21건, 불출 13건 - seed_realistic_data.sql + Node.js 시딩 스크립트 Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
2026-03-26 14:37:10 +09:00
-- ================================================================
-- 실제와 같은 시범 데이터 (대구 남구청 기준)
-- 유저(member) 테이블 제외
-- 실행 전: 기존 시드 데이터(local_government, code_kind 등) 적용 완료 필요
-- ================================================================
-- lg_idx는 남구청 기준 (local_government_init_daegu.sql 기준 4번째 = lg_idx=4)
-- 실제 lg_idx 값은 환경에 따라 다를 수 있으므로 변수 사용
SET @LG_IDX = (SELECT lg_idx FROM local_government WHERE lg_code = '110204' LIMIT 1);
-- ================================================================
-- 1. 지정판매소 (designated_shop) — 남구 실제 편의점/슈퍼 기반
-- ================================================================
INSERT INTO `designated_shop` (`ds_lg_idx`, `ds_shop_no`, `ds_name`, `ds_biz_no`, `ds_rep_name`, `ds_va_number`, `ds_zip`, `ds_addr`, `ds_addr_jibun`, `ds_tel`, `ds_rep_phone`, `ds_email`, `ds_gugun_code`, `ds_designated_at`, `ds_state`, `ds_regdate`) VALUES
(@LG_IDX, 'DS-2024-001', 'CU 대명점', '502-12-34567', '김민수', 'VA001', '42472', '대구 남구 대명로 112', '대명동 123-4', '053-621-0001', '010-1111-0001', '', '110204', '2023-03-15', 1, NOW()),
(@LG_IDX, 'DS-2024-002', 'GS25 앞산점', '502-23-45678', '박영희', 'VA002', '42473', '대구 남구 앞산순환로 55', '대명동 456-7', '053-621-0002', '010-1111-0002', '', '110204', '2023-05-20', 1, NOW()),
(@LG_IDX, 'DS-2024-003', '세븐일레븐 남구청점', '502-34-56789', '이정훈', 'VA003', '42474', '대구 남구 대명로 215', '대명동 100-1', '053-621-0003', '010-1111-0003', '', '110204', '2023-07-01', 1, NOW()),
(@LG_IDX, 'DS-2024-004', '봉덕슈퍼', '502-45-67890', '최순자', 'VA004', '42475', '대구 남구 봉덕로 88', '봉덕동 234-5', '053-621-0004', '010-1111-0004', '', '110204', '2022-01-10', 1, NOW()),
(@LG_IDX, 'DS-2024-005', 'CU 이천점', '502-56-78901', '정대환', 'VA005', '42476', '대구 남구 이천로 40', '이천동 567-8', '053-621-0005', '010-1111-0005', '', '110204', '2024-01-15', 1, NOW()),
(@LG_IDX, 'DS-2024-006', '미니스톱 대명역점', '502-67-89012', '한미영', 'VA006', '42477', '대구 남구 대명로 300', '대명동 789-0', '053-621-0006', '010-1111-0006', '', '110204', '2024-03-01', 1, NOW()),
(@LG_IDX, 'DS-2024-007', 'GS25 남산점', '502-78-90123', '윤석진', 'VA007', '42478', '대구 남구 남산로 22', '대명동 111-2', '053-621-0007', '010-1111-0007', '', '110204', '2024-06-01', 1, NOW()),
(@LG_IDX, 'DS-2024-008', '대구남구마트', '502-89-01234', '강은주', 'VA008', '42479', '대구 남구 두류로 150', '대명동 333-4', '053-621-0008', '010-1111-0008', '', '110204', '2022-09-01', 1, NOW());
-- ================================================================
-- 2. 판매 대행소 (sales_agency)
-- ================================================================
feat: enhance order sales inventory workflows
2026-04-08 00:20:09 +09:00
INSERT INTO `sales_agency` (`sa_lg_idx`, `sa_kind`, `sa_code`, `sa_name`, `sa_regdate`) VALUES
(@LG_IDX, '청', 'SA-NGU-001', '남구청소과', NOW()),
(@LG_IDX, '공단', 'SA-NGU-002', '대구환경공단 남구', NOW()),
(@LG_IDX, '협회', 'SA-NGU-003', '남구봉투판매협회', NOW());
대구 남구청 실제형 시드 데이터 추가 (유저 제외) - 지정판매소 8개, 판매대행소 3개, 업체 4개, 담당자 5명 - 무료대상자 8명, 봉투단가 14건, 포장단위 12건 - 발주 5건(15품목), 입고 15건, 재고 12품목 - 주문접수 7건, 판매/반품 21건, 불출 13건 - seed_realistic_data.sql + Node.js 시딩 스크립트 Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
2026-03-26 14:37:10 +09:00
-- ================================================================
-- 3. 업체 (company) — 제작/협회/회수
-- ================================================================
INSERT INTO `company` (`cp_lg_idx`, `cp_type`, `cp_name`, `cp_biz_no`, `cp_rep_name`, `cp_tel`, `cp_addr`, `cp_state`, `cp_regdate`) VALUES
(@LG_IDX, 'manufacturer', '(주)대구폴리머', '504-81-11111', '김제작', '053-555-1001', '대구 달서구 성서공단로 100', 1, NOW()),
(@LG_IDX, 'manufacturer', '(주)영남포장', '504-81-22222', '박제조', '053-555-2002', '대구 달성군 화원읍 공단1로 50', 1, NOW()),
(@LG_IDX, 'association', '대구종량제봉투협회', '504-82-33333', '이협회', '053-555-3003', '대구 중구 국채보상로 500', 1, NOW()),
(@LG_IDX, 'collector', '남구자원회수센터', '502-81-44444', '최회수', '053-555-4004', '대구 남구 이천로 200', 1, NOW());
-- ================================================================
-- 4. 담당자 (manager)
-- ================================================================
INSERT INTO `manager` (`mg_lg_idx`, `mg_name`, `mg_dept_code`, `mg_position_code`, `mg_tel`, `mg_phone`, `mg_email`, `mg_state`, `mg_regdate`) VALUES
(@LG_IDX, '김청소', '1', '6', '053-664-2111', '010-2222-1111', '', 1, NOW()),
(@LG_IDX, '이환경', '1', '5', '053-664-2112', '010-2222-2222', '', 1, NOW()),
(@LG_IDX, '박봉투', '3', '1', '053-664-2113', '010-2222-3333', '', 1, NOW()),
(@LG_IDX, '최관리', '2', '2', '053-664-2114', '010-2222-4444', '', 1, NOW()),
(@LG_IDX, '정수불', '1', '3', '053-664-2115', '010-2222-5555', '', 1, NOW());
-- ================================================================
-- 5. 무료용 대상자 (free_recipient)
-- ================================================================
INSERT INTO `free_recipient` (`fr_lg_idx`, `fr_type_code`, `fr_name`, `fr_phone`, `fr_addr`, `fr_dong_code`, `fr_note`, `fr_end_date`, `fr_state`, `fr_regdate`) VALUES
(@LG_IDX, '10', '김복지', '010-3333-1111', '대구 남구 대명로 50-1', '11020401', '생보자 20L 30매/월', NULL, 1, NOW()),
(@LG_IDX, '10', '이수급', '010-3333-2222', '대구 남구 봉덕로 33-2', '11020402', '생보자 20L 30매/월', NULL, 1, NOW()),
(@LG_IDX, '1', '남구보호센터', '053-664-8000', '대구 남구 앞산순환로 200', '11020403', '시설보호 50L 100매/월', NULL, 1, NOW()),
(@LG_IDX, '3', '박유공', '010-3333-3333', '대구 남구 이천로 80', '11020404', '무공수훈자 20L 20매/월', '2026-12-31', 1, NOW()),
(@LG_IDX, '4', '대명노인복지관', '053-664-9000', '대구 남구 대명로 150', '11020405', '복지시설 50L 200매/월', NULL, 1, NOW()),
(@LG_IDX, '10', '최기초', '010-3333-4444', '대구 남구 남산로 15', '11020401', '생보자 10L 30매/월', NULL, 1, NOW()),
(@LG_IDX, '10', '정보호', '010-3333-5555', '대구 남구 두류로 77', '11020402', '생보자 20L 30매/월', NULL, 1, NOW()),
(@LG_IDX, '4', '봉덕종합사회복지관', '053-664-7000', '대구 남구 봉덕로 120', '11020403', '복지시설 30L 150매/월', NULL, 1, NOW());
-- ================================================================
-- 6. 봉투 단가 (bag_price) — 남구 주요 봉투 현행 단가
-- ================================================================
INSERT INTO `bag_price` (`bp_lg_idx`, `bp_bag_code`, `bp_bag_name`, `bp_order_price`, `bp_wholesale`, `bp_consumer`, `bp_start_date`, `bp_end_date`, `bp_state`, `bp_regdate`) VALUES
(@LG_IDX, '10112', '일반용 3L', 25.00, 60.00, 80.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10122', '일반용 5L', 35.00, 100.00, 130.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10132', '일반용 10L', 60.00, 190.00, 250.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10152', '일반용 20L', 100.00, 370.00, 490.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10162', '일반용 30L', 140.00, 540.00, 710.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10172', '일반용 50L', 210.00, 860.00, 1130.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10192', '일반용 75L', 300.00, 1280.00, 1690.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10202', '일반용 100L', 380.00, 1700.00, 2250.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '60102', '음식물 2L', 18.00, 50.00, 60.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '60132', '음식물 10L', 70.00, 210.00, 280.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '40152', '공동주택용 20L', 90.00, 350.00, 460.00, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '20172', '공공용 50L', 180.00, 700.00, 0.00, '2025-01-01', NULL, 1, NOW());
-- 이전 단가 (2024년, 만료)
INSERT INTO `bag_price` (`bp_lg_idx`, `bp_bag_code`, `bp_bag_name`, `bp_order_price`, `bp_wholesale`, `bp_consumer`, `bp_start_date`, `bp_end_date`, `bp_state`, `bp_regdate`) VALUES
(@LG_IDX, '10152', '일반용 20L', 95.00, 350.00, 460.00, '2024-01-01', '2024-12-31', 0, '2024-01-01'),
(@LG_IDX, '10172', '일반용 50L', 200.00, 820.00, 1080.00, '2024-01-01', '2024-12-31', 0, '2024-01-01');
-- ================================================================
-- 7. 포장 단위 (packaging_unit) — 주요 봉투
-- ================================================================
INSERT INTO `packaging_unit` (`pu_lg_idx`, `pu_bag_code`, `pu_bag_name`, `pu_box_per_pack`, `pu_pack_per_sheet`, `pu_total_per_box`, `pu_start_date`, `pu_end_date`, `pu_state`, `pu_regdate`) VALUES
(@LG_IDX, '10112', '일반용 3L', 20, 50, 1000, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10122', '일반용 5L', 20, 50, 1000, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10132', '일반용 10L', 10, 50, 500, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10152', '일반용 20L', 10, 50, 500, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10162', '일반용 30L', 10, 30, 300, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10172', '일반용 50L', 10, 20, 200, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10192', '일반용 75L', 5, 20, 100, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '10202', '일반용 100L', 5, 10, 50, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '60102', '음식물 2L', 20, 50, 1000, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '60132', '음식물 10L', 10, 50, 500, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '40152', '공동주택용 20L', 10, 50, 500, '2025-01-01', NULL, 1, NOW()),
(@LG_IDX, '20172', '공공용 50L', 10, 20, 200, '2025-01-01', NULL, 1, NOW());
-- ================================================================
-- 8. 발주 (bag_order + bag_order_item) — 2025~2026 분기별 발주
-- ================================================================
SET @CO1 = (SELECT cp_idx FROM company WHERE cp_lg_idx = @LG_IDX AND cp_type = 'manufacturer' LIMIT 1);
SET @AG1 = (SELECT sa_idx FROM sales_agency WHERE sa_lg_idx = @LG_IDX LIMIT 1);
-- 2025 Q1 발주
INSERT INTO `bag_order` (`bo_uuid`, `bo_version`, `bo_lg_idx`, `bo_gugun_code`, `bo_dong_code`, `bo_company_idx`, `bo_agency_idx`, `bo_fee_rate`, `bo_order_date`, `bo_lot_no`, `bo_hash`, `bo_status`, `bo_regdate`) VALUES
(UUID(), 1, @LG_IDX, '110204', '', @CO1, @AG1, 3.50, '2025-01-15', 'LOT-2025-Q1-001', SHA2(UUID(), 256), 'normal', '2025-01-15 09:00:00');
SET @BO1 = LAST_INSERT_ID();
INSERT INTO `bag_order_item` (`boi_bo_idx`, `boi_bag_code`, `boi_bag_name`, `boi_unit_price`, `boi_qty_box`, `boi_qty_sheet`, `boi_amount`) VALUES
(@BO1, '10152', '일반용 20L', 100.00, 200, 100000, 10000000.00),
(@BO1, '10172', '일반용 50L', 210.00, 80, 16000, 3360000.00),
(@BO1, '10132', '일반용 10L', 60.00, 100, 50000, 3000000.00),
(@BO1, '60102', '음식물 2L', 18.00, 150, 150000, 2700000.00);
-- 2025 Q2 발주
INSERT INTO `bag_order` (`bo_uuid`, `bo_version`, `bo_lg_idx`, `bo_gugun_code`, `bo_dong_code`, `bo_company_idx`, `bo_agency_idx`, `bo_fee_rate`, `bo_order_date`, `bo_lot_no`, `bo_hash`, `bo_status`, `bo_regdate`) VALUES
(UUID(), 1, @LG_IDX, '110204', '', @CO1, @AG1, 3.50, '2025-04-10', 'LOT-2025-Q2-001', SHA2(UUID(), 256), 'normal', '2025-04-10 09:00:00');
SET @BO2 = LAST_INSERT_ID();
INSERT INTO `bag_order_item` (`boi_bo_idx`, `boi_bag_code`, `boi_bag_name`, `boi_unit_price`, `boi_qty_box`, `boi_qty_sheet`, `boi_amount`) VALUES
(@BO2, '10152', '일반용 20L', 100.00, 250, 125000, 12500000.00),
(@BO2, '10172', '일반용 50L', 210.00, 100, 20000, 4200000.00),
(@BO2, '10162', '일반용 30L', 140.00, 60, 18000, 2520000.00);
-- 2025 Q3 발주
INSERT INTO `bag_order` (`bo_uuid`, `bo_version`, `bo_lg_idx`, `bo_gugun_code`, `bo_dong_code`, `bo_company_idx`, `bo_agency_idx`, `bo_fee_rate`, `bo_order_date`, `bo_lot_no`, `bo_hash`, `bo_status`, `bo_regdate`) VALUES
(UUID(), 1, @LG_IDX, '110204', '', @CO1, @AG1, 3.50, '2025-07-15', 'LOT-2025-Q3-001', SHA2(UUID(), 256), 'normal', '2025-07-15 09:00:00');
SET @BO3 = LAST_INSERT_ID();
INSERT INTO `bag_order_item` (`boi_bo_idx`, `boi_bag_code`, `boi_bag_name`, `boi_unit_price`, `boi_qty_box`, `boi_qty_sheet`, `boi_amount`) VALUES
(@BO3, '10152', '일반용 20L', 100.00, 220, 110000, 11000000.00),
(@BO3, '10132', '일반용 10L', 60.00, 120, 60000, 3600000.00),
(@BO3, '60132', '음식물 10L', 70.00, 80, 40000, 2800000.00);
-- 2026 Q1 발주 (최근)
INSERT INTO `bag_order` (`bo_uuid`, `bo_version`, `bo_lg_idx`, `bo_gugun_code`, `bo_dong_code`, `bo_company_idx`, `bo_agency_idx`, `bo_fee_rate`, `bo_order_date`, `bo_lot_no`, `bo_hash`, `bo_status`, `bo_regdate`) VALUES
(UUID(), 1, @LG_IDX, '110204', '', @CO1, @AG1, 3.50, '2026-01-20', 'LOT-2026-Q1-001', SHA2(UUID(), 256), 'normal', '2026-01-20 09:00:00');
SET @BO4 = LAST_INSERT_ID();
INSERT INTO `bag_order_item` (`boi_bo_idx`, `boi_bag_code`, `boi_bag_name`, `boi_unit_price`, `boi_qty_box`, `boi_qty_sheet`, `boi_amount`) VALUES
(@BO4, '10152', '일반용 20L', 100.00, 300, 150000, 15000000.00),
(@BO4, '10172', '일반용 50L', 210.00, 120, 24000, 5040000.00),
(@BO4, '10132', '일반용 10L', 60.00, 150, 75000, 4500000.00),
(@BO4, '60102', '음식물 2L', 18.00, 200, 200000, 3600000.00),
(@BO4, '10162', '일반용 30L', 140.00, 50, 15000, 2100000.00);
-- 취소된 발주 1건
INSERT INTO `bag_order` (`bo_uuid`, `bo_version`, `bo_lg_idx`, `bo_gugun_code`, `bo_dong_code`, `bo_company_idx`, `bo_agency_idx`, `bo_fee_rate`, `bo_order_date`, `bo_lot_no`, `bo_hash`, `bo_status`, `bo_regdate`) VALUES
(UUID(), 1, @LG_IDX, '110204', '', @CO1, @AG1, 3.50, '2025-10-05', 'LOT-2025-Q4-CANCEL', SHA2(UUID(), 256), 'cancelled', '2025-10-05 09:00:00');
-- ================================================================
-- 9. 입고 (bag_receiving) — 발주에 대응하는 입고
-- ================================================================
INSERT INTO `bag_receiving` (`br_bo_idx`, `br_lg_idx`, `br_bag_code`, `br_bag_name`, `br_qty_box`, `br_qty_sheet`, `br_receive_date`, `br_sender_name`, `br_type`, `br_regdate`) VALUES
(@BO1, @LG_IDX, '10152', '일반용 20L', 200, 100000, '2025-02-01', '(주)대구폴리머', 'scanner', '2025-02-01 10:00:00'),
(@BO1, @LG_IDX, '10172', '일반용 50L', 80, 16000, '2025-02-01', '(주)대구폴리머', 'scanner', '2025-02-01 10:30:00'),
(@BO1, @LG_IDX, '10132', '일반용 10L', 100, 50000, '2025-02-03', '(주)대구폴리머', 'scanner', '2025-02-03 09:00:00'),
(@BO1, @LG_IDX, '60102', '음식물 2L', 150, 150000, '2025-02-03', '(주)대구폴리머', 'scanner', '2025-02-03 09:30:00'),
(@BO2, @LG_IDX, '10152', '일반용 20L', 250, 125000, '2025-04-25', '(주)대구폴리머', 'scanner', '2025-04-25 10:00:00'),
(@BO2, @LG_IDX, '10172', '일반용 50L', 100, 20000, '2025-04-25', '(주)대구폴리머', 'scanner', '2025-04-25 10:30:00'),
(@BO2, @LG_IDX, '10162', '일반용 30L', 60, 18000, '2025-04-28', '(주)영남포장', 'scanner', '2025-04-28 09:00:00'),
(@BO3, @LG_IDX, '10152', '일반용 20L', 220, 110000, '2025-08-01', '(주)대구폴리머', 'scanner', '2025-08-01 10:00:00'),
(@BO3, @LG_IDX, '10132', '일반용 10L', 120, 60000, '2025-08-01', '(주)대구폴리머', 'scanner', '2025-08-01 10:30:00'),
(@BO3, @LG_IDX, '60132', '음식물 10L', 80, 40000, '2025-08-05', '(주)영남포장', 'scanner', '2025-08-05 09:00:00'),
(@BO4, @LG_IDX, '10152', '일반용 20L', 300, 150000, '2026-02-10', '(주)대구폴리머', 'scanner', '2026-02-10 10:00:00'),
(@BO4, @LG_IDX, '10172', '일반용 50L', 120, 24000, '2026-02-10', '(주)대구폴리머', 'scanner', '2026-02-10 10:30:00'),
(@BO4, @LG_IDX, '10132', '일반용 10L', 150, 75000, '2026-02-12', '(주)대구폴리머', 'scanner', '2026-02-12 09:00:00'),
(@BO4, @LG_IDX, '60102', '음식물 2L', 200, 200000, '2026-02-12', '(주)대구폴리머', 'scanner', '2026-02-12 09:30:00'),
(@BO4, @LG_IDX, '10162', '일반용 30L', 50, 15000, '2026-02-15', '(주)영남포장', 'scanner', '2026-02-15 09:00:00');
-- ================================================================
-- 10. 재고 (bag_inventory) — 현재 재고 현황
-- ================================================================
INSERT INTO `bag_inventory` (`bi_lg_idx`, `bi_bag_code`, `bi_bag_name`, `bi_qty`, `bi_updated_at`) VALUES
(@LG_IDX, '10112', '일반용 3L', 45200, NOW()),
(@LG_IDX, '10122', '일반용 5L', 38500, NOW()),
(@LG_IDX, '10132', '일반용 10L', 72300, NOW()),
(@LG_IDX, '10152', '일반용 20L', 185400, NOW()),
(@LG_IDX, '10162', '일반용 30L', 21800, NOW()),
(@LG_IDX, '10172', '일반용 50L', 28600, NOW()),
(@LG_IDX, '10192', '일반용 75L', 5200, NOW()),
(@LG_IDX, '10202', '일반용 100L', 2100, NOW()),
(@LG_IDX, '20172', '공공용 50L', 8400, NOW()),
(@LG_IDX, '40152', '공동주택용 20L', 32100, NOW()),
(@LG_IDX, '60102', '음식물 2L', 128700, NOW()),
(@LG_IDX, '60132', '음식물 10L', 24500, NOW());
-- ================================================================
-- 11. 주문 접수 (shop_order + shop_order_item)
-- ================================================================
SET @DS1 = (SELECT ds_idx FROM designated_shop WHERE ds_lg_idx = @LG_IDX AND ds_shop_no = 'DS-2024-001' LIMIT 1);
SET @DS2 = (SELECT ds_idx FROM designated_shop WHERE ds_lg_idx = @LG_IDX AND ds_shop_no = 'DS-2024-002' LIMIT 1);
SET @DS3 = (SELECT ds_idx FROM designated_shop WHERE ds_lg_idx = @LG_IDX AND ds_shop_no = 'DS-2024-003' LIMIT 1);
SET @DS4 = (SELECT ds_idx FROM designated_shop WHERE ds_lg_idx = @LG_IDX AND ds_shop_no = 'DS-2024-004' LIMIT 1);
INSERT INTO `shop_order` (`so_lg_idx`, `so_ds_idx`, `so_ds_name`, `so_order_date`, `so_delivery_date`, `so_payment_type`, `so_paid`, `so_received`, `so_total_qty`, `so_total_amount`, `so_status`, `so_regdate`) VALUES
(@LG_IDX, @DS1, 'CU 대명점', '2026-03-01', '2026-03-05', '후불', 1, 1, 500, 245000, 'normal', '2026-03-01 09:00:00'),
(@LG_IDX, @DS2, 'GS25 앞산점', '2026-03-03', '2026-03-07', '후불', 1, 1, 300, 147000, 'normal', '2026-03-03 10:00:00'),
(@LG_IDX, @DS3, '세븐일레븐 남구청점', '2026-03-10', '2026-03-14', '선불', 1, 1, 800, 392000, 'normal', '2026-03-10 09:30:00'),
(@LG_IDX, @DS4, '봉덕슈퍼', '2026-03-15', '2026-03-18', '후불', 0, 0, 200, 98000, 'normal', '2026-03-15 11:00:00'),
(@LG_IDX, @DS1, 'CU 대명점', '2026-03-20', '2026-03-24', '후불', 0, 0, 600, 294000, 'normal', '2026-03-20 09:00:00'),
(@LG_IDX, @DS2, 'GS25 앞산점', '2026-02-15', '2026-02-18', '후불', 1, 1, 400, 196000, 'normal', '2026-02-15 10:00:00'),
(@LG_IDX, @DS3, '세븐일레븐 남구청점', '2026-02-20', NULL, '선불', 0, 0, 100, 49000, 'cancelled', '2026-02-20 14:00:00');
-- 주문 품목
SET @SO1 = (SELECT so_idx FROM shop_order WHERE so_lg_idx = @LG_IDX ORDER BY so_idx ASC LIMIT 1);
INSERT INTO `shop_order_item` (`soi_so_idx`, `soi_bag_code`, `soi_bag_name`, `soi_unit_price`, `soi_qty`, `soi_amount`, `soi_box_count`, `soi_pack_count`, `soi_sheet_count`) VALUES
(@SO1, '10152', '일반용 20L', 490.00, 300, 147000, 0, 6, 300),
(@SO1, '10132', '일반용 10L', 250.00, 200, 50000, 0, 4, 200);
SET @SO2 = @SO1 + 1;
INSERT INTO `shop_order_item` (`soi_so_idx`, `soi_bag_code`, `soi_bag_name`, `soi_unit_price`, `soi_qty`, `soi_amount`, `soi_box_count`, `soi_pack_count`, `soi_sheet_count`) VALUES
(@SO2, '10152', '일반용 20L', 490.00, 300, 147000, 0, 6, 300);
-- ================================================================
-- 12. 판매/반품 (bag_sale) — 2026년 1~3월 판매 데이터
-- ================================================================
INSERT INTO `bag_sale` (`bs_lg_idx`, `bs_ds_idx`, `bs_ds_name`, `bs_sale_date`, `bs_bag_code`, `bs_bag_name`, `bs_qty`, `bs_unit_price`, `bs_amount`, `bs_type`, `bs_regdate`) VALUES
-- 3월
(@LG_IDX, @DS1, 'CU 대명점', '2026-03-02', '10152', '일반용 20L', 150, 490, 73500, 'sale', '2026-03-02 10:00:00'),
(@LG_IDX, @DS1, 'CU 대명점', '2026-03-02', '10132', '일반용 10L', 100, 250, 25000, 'sale', '2026-03-02 10:05:00'),
(@LG_IDX, @DS2, 'GS25 앞산점', '2026-03-05', '10152', '일반용 20L', 200, 490, 98000, 'sale', '2026-03-05 11:00:00'),
(@LG_IDX, @DS3, '세븐일레븐 남구청점', '2026-03-08', '10172', '일반용 50L', 80, 1130, 90400, 'sale', '2026-03-08 09:30:00'),
(@LG_IDX, @DS3, '세븐일레븐 남구청점', '2026-03-08', '10152', '일반용 20L', 300, 490, 147000, 'sale', '2026-03-08 09:35:00'),
(@LG_IDX, @DS4, '봉덕슈퍼', '2026-03-12', '10132', '일반용 10L', 200, 250, 50000, 'sale', '2026-03-12 10:00:00'),
(@LG_IDX, @DS4, '봉덕슈퍼', '2026-03-12', '60102', '음식물 2L', 300, 60, 18000, 'sale', '2026-03-12 10:05:00'),
(@LG_IDX, @DS1, 'CU 대명점', '2026-03-18', '10152', '일반용 20L', 250, 490, 122500, 'sale', '2026-03-18 10:00:00'),
(@LG_IDX, @DS2, 'GS25 앞산점', '2026-03-22', '10162', '일반용 30L', 100, 710, 71000, 'sale', '2026-03-22 11:30:00'),
(@LG_IDX, @DS3, '세븐일레븐 남구청점', '2026-03-25', '10152', '일반용 20L', 400, 490, 196000, 'sale', '2026-03-25 09:00:00'),
-- 2월
(@LG_IDX, @DS1, 'CU 대명점', '2026-02-05', '10152', '일반용 20L', 180, 490, 88200, 'sale', '2026-02-05 10:00:00'),
(@LG_IDX, @DS2, 'GS25 앞산점', '2026-02-10', '10152', '일반용 20L', 220, 490, 107800, 'sale', '2026-02-10 11:00:00'),
(@LG_IDX, @DS3, '세븐일레븐 남구청점', '2026-02-15', '10172', '일반용 50L', 60, 1130, 67800, 'sale', '2026-02-15 09:30:00'),
(@LG_IDX, @DS4, '봉덕슈퍼', '2026-02-20', '60102', '음식물 2L', 500, 60, 30000, 'sale', '2026-02-20 10:00:00'),
(@LG_IDX, @DS1, 'CU 대명점', '2026-02-25', '10132', '일반용 10L', 300, 250, 75000, 'sale', '2026-02-25 10:30:00'),
-- 1월
(@LG_IDX, @DS1, 'CU 대명점', '2026-01-08', '10152', '일반용 20L', 200, 490, 98000, 'sale', '2026-01-08 10:00:00'),
(@LG_IDX, @DS2, 'GS25 앞산점', '2026-01-15', '10172', '일반용 50L', 50, 1130, 56500, 'sale', '2026-01-15 11:00:00'),
(@LG_IDX, @DS3, '세븐일레븐 남구청점', '2026-01-22', '10152', '일반용 20L', 350, 490, 171500, 'sale', '2026-01-22 09:30:00'),
(@LG_IDX, @DS4, '봉덕슈퍼', '2026-01-28', '10132', '일반용 10L', 150, 250, 37500, 'sale', '2026-01-28 10:00:00'),
-- 반품 2건
(@LG_IDX, @DS2, 'GS25 앞산점', '2026-03-10', '10152', '일반용 20L', 50, 490, 24500, 'return', '2026-03-10 14:00:00'),
(@LG_IDX, @DS4, '봉덕슈퍼', '2026-02-28', '10132', '일반용 10L', 30, 250, 7500, 'return', '2026-02-28 15:00:00');
-- ================================================================
-- 13. 무료용 불출 (bag_issue)
-- ================================================================
INSERT INTO `bag_issue` (`bi2_lg_idx`, `bi2_year`, `bi2_quarter`, `bi2_issue_type`, `bi2_issue_date`, `bi2_dest_type`, `bi2_dest_name`, `bi2_bag_code`, `bi2_bag_name`, `bi2_qty`, `bi2_status`, `bi2_regdate`) VALUES
(@LG_IDX, 2026, 1, '무료불출', '2026-01-10', '생보자', '김복지', '10152', '일반용 20L', 30, 'normal', '2026-01-10 09:00:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-01-10', '생보자', '이수급', '10152', '일반용 20L', 30, 'normal', '2026-01-10 09:05:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-01-10', '시설', '남구보호센터', '10172', '일반용 50L', 100, 'normal', '2026-01-10 09:10:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-01-10', '수훈자', '박유공', '10152', '일반용 20L', 20, 'normal', '2026-01-10 09:15:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-01-10', '시설', '대명노인복지관', '10172', '일반용 50L', 200, 'normal', '2026-01-10 09:20:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-02-10', '생보자', '김복지', '10152', '일반용 20L', 30, 'normal', '2026-02-10 09:00:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-02-10', '생보자', '이수급', '10152', '일반용 20L', 30, 'normal', '2026-02-10 09:05:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-02-10', '생보자', '최기초', '10132', '일반용 10L', 30, 'normal', '2026-02-10 09:10:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-02-10', '시설', '봉덕종합사회복지관','10162','일반용 30L', 150, 'normal', '2026-02-10 09:15:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-03-10', '생보자', '김복지', '10152', '일반용 20L', 30, 'normal', '2026-03-10 09:00:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-03-10', '생보자', '정보호', '10152', '일반용 20L', 30, 'normal', '2026-03-10 09:05:00'),
(@LG_IDX, 2026, 1, '무료불출', '2026-03-10', '시설', '남구보호센터', '10172', '일반용 50L', 100, 'normal', '2026-03-10 09:10:00'),
-- 취소 1건
(@LG_IDX, 2026, 1, '무료불출', '2026-03-05', '생보자', '이수급', '10152', '일반용 20L', 30, 'cancelled', '2026-03-05 09:00:00');
Reference in New Issue Copy Permalink