root 권한자가 해줄 일
drop database prokinWebDb;
create database prokinWebDb;
create user prokinWeb@localhost identified by 'prokinWeb1234';
grant all privileges on prokinWebDb. * to prokinWeb@localhost;
commit;
SET GLOBAL log_bin_trust_function_creators = 1;
1) 테이블 생성
use prokinWebDb;
SET FOREIGN_KEY_CHECKS=0;
-- =================================================================
-- 1) DROP TABLES (존재하는 경우 모두 제거)
-- 순서를 의존성 역순으로 나열해야 자동으로 삭제 오류가 발생하지 않음
DROP TABLE IF EXISTS `OrderDetail`;
DROP TABLE IF EXISTS `Order`;
DROP TABLE IF EXISTS `InboundDetail`;
DROP TABLE IF EXISTS `Inbound`;
DROP TABLE IF EXISTS `Inventory`;
DROP TABLE IF EXISTS `Product`;
DROP TABLE IF EXISTS `Category`;
DROP TABLE IF EXISTS `Section`;
DROP TABLE IF EXISTS `Warehouse`;
DROP TABLE IF EXISTS `Franchise`;
DROP TABLE IF EXISTS `MemberRequest`;
DROP TABLE IF EXISTS `MemberAccount`;
DROP TABLE IF EXISTS `Authority`;
-- =================================================================
-- 2) 테이블 생성
CREATE TABLE `Authority` (
`authorityCode` VARCHAR(255) NOT NULL,
`authorityName` VARCHAR(255) NOT NULL,
PRIMARY KEY (`authorityCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `MemberAccount` (
`memberCode` VARCHAR(255) NOT NULL,
`authorityCode` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`phoneNumber` VARCHAR(255) NULL,
`email` VARCHAR(255) NOT NULL,
`address` VARCHAR(255) NULL,
`id` VARCHAR(255) NOT NULL,
`password` VARCHAR(255) NOT NULL,
PRIMARY KEY (`memberCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `MemberRequest` (
`requestCode` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`phoneNumber` VARCHAR(255) NULL,
`email` VARCHAR(255) NOT NULL,
`id` VARCHAR(255) NOT NULL,
`password` VARCHAR(255) NOT NULL,
`address` VARCHAR(255) NULL,
`request` VARCHAR(255) NOT NULL default '승인대기',
`authorityCode` VARCHAR(255) NULL default 'FM',
`requestDate` DATE NOT NULL,
PRIMARY KEY (`requestCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Warehouse` (
`warehouseCode` VARCHAR(255) NOT NULL,
`warehouseName` VARCHAR(255) NOT NULL,
`capacityLimit` INT NOT NULL,
`address` VARCHAR(255) NOT NULL,
`memberCode` VARCHAR(255) NULL,
PRIMARY KEY (`warehouseCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Section` (
`sectionCode` VARCHAR(255) NOT NULL,
`warehouseCode` VARCHAR(255) NOT NULL,
`storageCapacity` INT NOT NULL,
`storedType` VARCHAR(255) NOT NULL,
`temperature` INT NULL,
PRIMARY KEY (`sectionCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Inventory` (
`inventoryCode` VARCHAR(255) NOT NULL,
`quantity` INT NULL,
`productCode` VARCHAR(255) NOT NULL,
`warehouseCode` VARCHAR(255) NOT NULL,
PRIMARY KEY (`inventoryCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Franchise` (
`franchiseCode` VARCHAR(255) NOT NULL,
`franchiseLocation` VARCHAR(255) NOT NULL,
`franchiseName` VARCHAR(255) NOT NULL,
`memberCode` VARCHAR(255) NULL,
`donutMinOrder` INT NULL,
PRIMARY KEY (`franchiseCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Category` (
`categoryCode` VARCHAR(255) NOT NULL,
`categoryMid` VARCHAR(255) NOT NULL,
`categorySub` VARCHAR(255) NOT NULL,
PRIMARY KEY (`categoryCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Product` (
`productCode` VARCHAR(255) NOT NULL,
`productName` VARCHAR(255) NOT NULL,
`productPrice` INT NOT NULL,
`categoryCode` VARCHAR(255) NOT NULL,
`storedType` VARCHAR(255) NULL,
PRIMARY KEY (`productCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Inbound` (
`inboundCode` VARCHAR(255) NOT NULL,
`inboundDate` DATE NOT NULL,
`inboundStatus` VARCHAR(255) NOT NULL,
`warehouseCode` VARCHAR(255) NOT NULL,
PRIMARY KEY (`inboundCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `InboundDetail` (
`inboundDetailCode` VARCHAR(255) NOT NULL,
`quantity` INT NOT NULL,
`inboundCode` VARCHAR(255) NOT NULL,
`productCode` VARCHAR(255) NOT NULL,
`sectionCode` VARCHAR(255) NOT NULL,
PRIMARY KEY (`inboundDetailCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Order` (
`orderCode` VARCHAR(255) NOT NULL,
`orderDate` DATE NOT NULL,
`orderStatus` VARCHAR(255) NOT NULL,
`franchiseCode` VARCHAR(255) NOT NULL,
`warehouseCode` VARCHAR(255) NOT NULL,
PRIMARY KEY (`orderCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `OrderDetail` (
`orderDetailCode` VARCHAR(255) NOT NULL,
`quantity` INT NOT NULL,
`orderCode` VARCHAR(255) NOT NULL,
`productCode` VARCHAR(255) NOT NULL,
PRIMARY KEY (`orderDetailCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2-1) 더미데이터 추가
-- =================================================================
-- 3) 더미데이터 삽입
INSERT INTO `Authority` (`authorityCode`,`authorityName`) VALUES ('QH','본사관리자'), ('WM','창고관리자'), ('FM','가맹점주');
-- 본사 : kya9505, wm5256
-- 창고 : cmk1031
-- 점주 : qwer123
INSERT INTO `MemberAccount` (`memberCode`,`authorityCode`,`name`,`phoneNumber`,`email`,`address`,`id`,`password`)
VALUES
('QH1','QH','고윤아','01048942574','[email protected]' ,'강남구 삼성동 테헤란로 8길 20','kya9505','kya9505!'),
('QH3','QH','박건희','01081242572','[email protected]' ,'경기도 수원 영통 덕영대로 1703 아이시티 803호','wm5256','wm5256!'),
('WM1','WM','최문규','01047852266','[email protected]' ,'서울 대림동 쌍용플래티넘S 1층 103호','cmk1031','cmk1031!'),
('WM2','WM','김세진','01023456789','[email protected]' ,'서울 마포구 월드컵북로 400 12층' ,'ksejin' ,'ksejin!'),
('WM3','WM','박수연','01034567890','[email protected]' ,'서울 강남구 도산대로 122' ,'psuyeon' ,'psuyeon!'),
('WM4','WM','오민재','01045678901','[email protected]' ,'서울 송파구 백제고분로 55' ,'omin0316' ,'omin0316!'),
('WM5','WM','이근면','01055987895','[email protected]' ,'서울 삼성117로 5층 523호' ,'lee233' ,'lee233!'),
('FM1','FM','박열정','01066654589','[email protected]' ,'경기도 화성 행궁동 곱창길 11-4 2층' ,'fire123' ,'fire123!'),
('FM2','FM','조아현','01056789012','[email protected]' ,'경기도 성남시 분당구 불정로 90' ,'ahyun90' ,'ahyun90!'),
('FM3','FM','백승우','01067890123','[email protected]','부산 남구 수영로 222' ,'woo999' ,'woo999!'),
('FM4','FM','윤가영','01078901234','[email protected]' ,'대전 유성구 대학로 99' ,'gyoon0528','gyoon0528!'),
('FM5','FM','김덕수','01069535414','[email protected]' ,'삼성로 117길 1번지','qwer123','qwer123!');
INSERT INTO `MemberRequest` (`requestCode`,`name`,`phoneNumber`,`email`,`id`,`password`,`address`,`request`,`authorityCode`,`requestDate`)
VALUES
('RQ1','김주현','01034981265','[email protected]','juhyun01','juhyun01!','서울특별시 동작구 사당로 22','승인대기','FM','2025-03-15'),
('RQ2','이채원','01082179033','[email protected]','cwlee99','cwlee99!','경기도 고양시 일산동구 중앙로 88','승인대기','FM','2025-03-27'),
('RQ3','박상우','01047268859','[email protected]','psw321','psw321!','인천광역시 연수구 센트럴로 11','승인대기','FM','2025-03-30'),
('RQ4','정예린','01063257104','[email protected]','yerin333','yerin333!','대전광역시 유성구 유성대로 135','승인대기','FM','2025-04-02'),
('RQ5','오태경','01029514480','[email protected]','otk_84','otk_84!','부산광역시 해운대구 마린시티2로 77','승인대기','FM','2025-04-04'),
('RQ6','한지우','01058192345','[email protected]','hjw0301','hjw0301!','광주광역시 서구 죽봉대로 50','승인대기','FM','2025-04-06');
INSERT INTO `Warehouse` (`warehouseCode`,`warehouseName`,`capacityLimit`,`address`,`memberCode`)
VALUES
('GG1','남양주DT센터',330000,'경기 남양주시 수동면 지둔로 191 프로킨도넛 남양주DT센터','WM1'),
('DJ1','대전DT센터' ,157000,'대전 동구 물류로14번길 13 프로킨도넛 대전DT센터' ,'WM2'),
('JB1','전주DT센터' ,127200,'전북특별자치도 전주시 덕진구 반월동 625-4 프로킨도넛 전주DT센터' ,NULL),
('BS1','부산서면DT센터',132200,'부산광역시 강서구 녹산산단262로50번길 14 프로킨도넛 부산서면DT센터' ,NULL);
INSERT INTO Section (sectionCode, warehouseCode, storageCapacity, storedType, temperature) VALUES
('GG1-R', 'GG1', 188111, '냉장', 4),
('GG1-F', 'GG1', 61292, '냉동', -21),
('GG1-A', 'GG1', 63653, '상온', 22),
('DJ1-R', 'DJ1', 86218, '냉장', 5),
('DJ1-F', 'DJ1', 29478, '냉동', -18),
('DJ1-A', 'DJ1', 29363, '상온', 28),
('JB1-R', 'JB1', 71109, '냉장', 3),
('JB1-F', 'JB1', 25440, '냉동', -25),
('JB1-A', 'JB1', 25440, '상온', 17),
('BS1-R', 'BS1', 76399, '냉장', 6),
('BS1-F', 'BS1', 26440, '냉동', -19),
('BS1-A', 'BS1', 24368, '상온', 30);
INSERT INTO `Inventory` (`inventoryCode`,`warehouseCode`,`productCode`,`quantity`)
VALUES
('BS1-BGL1','BS1','BGL1', 350),
('BS1-CDC1','BS1','CDC1', 1114),
('BS1-DGL1','BS1','DGL1', 294),
('BS1-DGL2','BS1','DGL2', 810),
('BS1-DGL5','BS1','DGL5', 663),
('BS1-DLW3','BS1','DLW3', 107),
('BS1-DPN3','BS1','DPN3', 800),
('BS1-DPN4','BS1','DPN4', 501),
('BS1-DPN5','BS1','DPN5', 1236),
('BS1-DPN6','BS1','DPN6', 1303),
('BS1-MPK1','BS1','MPK1', 556),
('BS1-TZR1','BS1','TZR1', 2072),
('DJ1-BGL1','DJ1','BGL1', 678),
('DJ1-DGL1','DJ1','DGL1', 815),
('DJ1-DGL4','DJ1','DGL4', 514),
('DJ1-DGL6','DJ1','DGL6', 593),
('DJ1-DLW1','DJ1','DLW1', 559),
('DJ1-DLW2','DJ1','DLW2', 1092),
('DJ1-DLW3','DJ1','DLW3', 604),
('DJ1-DLW4','DJ1','DLW4', 177),
('DJ1-DLW5','DJ1','DLW5', 1409),
('DJ1-DLW6','DJ1','DLW6', 1033),
('DJ1-DPN3','DJ1','DPN3', 1264),
('DJ1-DPN4','DJ1','DPN4', 122),
('DJ1-DPN5','DJ1','DPN5', 1075),
('DJ1-TZR1','DJ1','TZR1', 2037),
('GG1-BGL1','GG1','BGL1', 589),
('GG1-CDC1','GG1','CDC1', 902),
('GG1-DGL1','GG1','DGL1', 169),
('GG1-DGL2','GG1','DGL2', 583),
('GG1-DGL3','GG1','DGL3', 541),
('GG1-DGL4','GG1','DGL4', 914),
('GG1-DGL5','GG1','DGL5', 116),
('GG1-DGL6','GG1','DGL6', 797),
('GG1-DLW1','GG1','DLW1', 199),
('GG1-DLW2','GG1','DLW2', 794),
('GG1-DLW3','GG1','DLW3', 253),
('GG1-DLW4','GG1','DLW4', 608),
('GG1-DLW5','GG1','DLW5', 65),
('GG1-DLW6','GG1','DLW6', 874),
('GG1-DPN1','GG1','DPN1', 959),
('GG1-DPN2','GG1','DPN2', 913),
('GG1-DPN3','GG1','DPN3', 406),
('GG1-DPN4','GG1','DPN4', 98),
('GG1-DPN5','GG1','DPN5', 77),
('GG1-DPN6','GG1','DPN6', 1711),
('GG1-MPK1','GG1','MPK1', 1340),
('GG1-TPK1','GG1','TPK1', 50),
('GG1-TZR1','GG1','TZR1', 55),
('JB1-BGL1','JB1','BGL1', 728),
('JB1-DGL1','JB1','DGL1', 1041),
('JB1-DGL2','JB1','DGL2', 909),
('JB1-DGL3','JB1','DGL3', 813),
('JB1-DGL4','JB1','DGL4', 282),
('JB1-DGL5','JB1','DGL5', 662),
('JB1-DGL6','JB1','DGL6', 859),
('JB1-DLW1','JB1','DLW1', 701),
('JB1-DLW2','JB1','DLW2', 1254),
('JB1-DLW3','JB1','DLW3', 1856),
('JB1-DLW5','JB1','DLW5', 1677),
('JB1-DLW6','JB1','DLW6', 550),
('JB1-DPN1','JB1','DPN1', 1979),
('JB1-DPN2','JB1','DPN2', 545),
('JB1-DPN3','JB1','DPN3', 969),
('JB1-DPN5','JB1','DPN5', 530),
('JB1-DPN6','JB1','DPN6', 1374);
INSERT INTO `Franchise` (`franchiseCode`,`franchiseName`,`franchiseLocation`,`donutMinOrder`,`memberCode`)
VALUES
('GGF1','수원영통점' ,'경기 수원시 영통구 덕영대로 1703 프로킨도넛 수원영통점',100,'FM1'),
('DJF1','대전둔산점' ,'대전광역시 서구 둔산남로165번길 21 프로킨도넛 대전둔산점' , 50,'FM2'),
('SLF1','서울강남1호점','서울특별시 강남구 강남대로 408 프로킨도넛 서울강남1호점' ,100, NULL),
('ICF1','인천연수역점' ,'인천광역시 연수구 용담로63번길 18 프로킨도넛 인천연수역점' ,100, NULL),
('GWF1','경포호수점' ,'강원특별자치도 강릉시 경포로 479 프로킨도넛 경포호수점' , 50, NULL),
('CBF1','충북대학교점' ,'충청북도 청주시 서원구 1순환로674번길 72 프로킨도넛 충북대학교점', 50, NULL),
('JBF1','전주한옥마을점','전북특별자치도 전주시 완산구 태조로 12 프로킨도넛 전주한옥마을점', 50, NULL);
INSERT INTO `Category` (`categoryCode`,`categoryMid`,`categorySub`)
VALUES
('DPN','도넛','프로틴 도넛'),
('DGL','도넛','글루텐 프리 도넛'),
('DLW','도넛','저당 도넛'),
('BGL','베이글','글루텐 프리 베이글'),
('CDC','커피','디카페인'),
('TZR','티','제로음료'),
('MPK','머그컵','프로킨머그컵'),
('TPK','텀블러','프로킨텀블러');
INSERT INTO `Product` (`productCode`,`productName`,`productPrice`,`categoryCode`,`storedType`)
VALUES
('DPN1','프로틴초코도넛' ,5000 ,'DPN','냉장'),
('DPN2','프로틴딸기도넛' ,5000 ,'DPN','냉장'),
('DPN3','프로틴글레이즈드도넛' ,5000 ,'DPN','냉장'),
('DPN4','냉동프로틴초코도넛' ,4000 ,'DPN','냉동'),
('DPN5','냉동프로틴딸기도넛' ,4000 ,'DPN','냉동'),
('DPN6','냉동프로틴글레이즈드도넛' ,4000 ,'DPN','냉동'),
('DGL1','글루텐프리초코도넛' ,5000 ,'DGL','냉장'),
('DGL2','글루텐프리딸기도넛' ,5000 ,'DGL','냉장'),
('DGL3','글루텐프리글레이즈드도넛' ,5000 ,'DGL','냉장'),
('DGL4','냉동글루텐프리초코도넛' ,4000 ,'DGL','냉동'),
('DGL5','냉동글루텐프리딸기도넛' ,4000 ,'DGL','냉동'),
('DGL6','냉동글루텐프리글레이즈드도넛',4000 ,'DGL','냉동'),
('DLW1','저당초코도넛' ,5000 ,'DLW','냉장'),
('DLW2','저당딸기도넛' ,5000 ,'DLW','냉장'),
('DLW3','저당글레이즈드도넛' ,5000 ,'DLW','냉장'),
('DLW4','냉동저당초코도넛' ,4000 ,'DLW','냉동'),
('DLW5','냉동저당딸기도넛' ,4000 ,'DLW','냉동'),
('DLW6','냉동저당글레이즈드도넛' ,4000 ,'DLW','냉동'),
('BGL1','글루텐프리베이글' ,5000 ,'BGL','냉동'),
('CDC1','디카페인커피' ,5000 ,'CDC','상온'),
('TZR1','제로아이스티' ,4000 ,'TZR','상온'),
('MPK1','프로킨머그컵' ,13000 ,'MPK','상온'),
('TPK1','프로킨텀블러' ,30000,'TPK','상온');
2-2) 입고-입고상세 더미데이터 추가
-- =================================================================
-- 과거데이터 : 입고완료, 입고취소로 조합
-- 미래데이터(4월말 ~ 5월초) : 승인대기
-- 미래데이터(5월 중순) : 입고요청
INSERT INTO `Inbound` (`inboundCode`,`inboundDate`,`inboundStatus`,`warehouseCode`)
VALUES
('IN1','2025-01-26','입고완료','GG1'),
('IN2','2025-01-22','입고취소','DJ1'),
('IN3','2025-01-30','입고완료','GG1'),
('IN4','2025-01-13','입고완료','GG1'),
('IN5','2025-01-08','입고완료','DJ1'),
('IN6','2025-01-01','입고취소','DJ1'),
('IN7','2025-01-30','입고완료','DJ1'),
('IN8','2025-01-10','입고완료','DJ1'),
('IN9','2025-01-01','입고완료','GG1'),
('IN10','2025-01-19','입고완료','DJ1'),
('IN11','2025-01-12','입고완료','GG1'),
('IN12','2025-01-24','입고완료','DJ1'),
('IN13','2025-01-13','입고취소','DJ1'),
('IN14','2025-01-18','입고완료','DJ1'),
('IN15','2025-01-22','입고완료','DJ1'),
('IN16','2025-01-21','입고취소','GG1'),
('IN17','2025-01-08','입고완료','GG1'),
('IN18','2025-01-01','입고완료','GG1'),
('IN19','2025-01-19','입고취소','GG1'),
('IN20','2025-01-04','입고완료','DJ1'),
('IN21','2025-01-22','입고완료','GG1'),
('IN22','2025-01-19','입고완료','GG1'),
('IN23','2025-01-21','입고완료','DJ1'),
('IN24','2025-01-07','입고완료','GG1'),
('IN25','2025-01-21','입고완료','GG1'),
('IN26','2025-01-26','입고완료','GG1'),
('IN27','2025-01-28','입고완료','GG1'),
('IN28','2025-01-17','입고완료','DJ1'),
('IN29','2025-01-10','입고취소','GG1'),
('IN30','2025-01-27','입고완료','GG1'),
('IN31','2025-01-31','입고완료','DJ1'),
('IN32','2025-01-01','입고완료','GG1'),
('IN33','2025-01-25','입고완료','GG1'),
('IN34','2025-01-13','입고취소','GG1'),
('IN35','2025-01-29','입고완료','DJ1'),
('IN36','2025-01-07','입고완료','DJ1'),
('IN37','2025-01-07','입고완료','GG1'),
('IN38','2025-01-13','입고완료','GG1'),
('IN39','2025-01-12','입고완료','DJ1'),
('IN40','2025-01-07','입고완료','GG1');
INSERT INTO `Inbound` (`inboundCode`,`inboundDate`,`inboundStatus`,`warehouseCode`)
VALUES
('IN41','2025-02-09','입고완료','GG1'),
('IN42','2025-02-25','입고취소','GG1'),
('IN43','2025-02-25','입고완료','GG1'),
('IN44','2025-02-04','입고취소','GG1'),
('IN45','2025-02-23','입고완료','GG1'),
('IN46','2025-02-07','입고완료','GG1'),
('IN47','2025-02-11','입고취소','DJ1'),
('IN48','2025-02-26','입고완료','GG1'),
('IN49','2025-02-22','입고완료','GG1'),
('IN50','2025-02-14','입고완료','GG1'),
('IN51','2025-02-24','입고완료','GG1'),
('IN52','2025-02-07','입고완료','GG1'),
('IN53','2025-02-23','입고완료','GG1'),
('IN54','2025-02-10','입고취소','DJ1'),
('IN55','2025-02-16','입고완료','GG1'),
('IN56','2025-02-28','입고완료','DJ1'),
('IN57','2025-02-13','입고취소','GG1'),
('IN58','2025-02-12','입고완료','DJ1'),
('IN59','2025-02-07','입고완료','DJ1'),
('IN60','2025-02-10','입고취소','GG1'),
('IN61','2025-02-07','입고취소','GG1'),
('IN62','2025-02-03','입고취소','GG1'),
('IN63','2025-02-06','입고완료','GG1'),
('IN64','2025-02-15','입고완료','DJ1'),
('IN65','2025-02-13','입고취소','DJ1'),
('IN66','2025-02-08','입고료','DJ1'),
('IN67','2025-02-09','입고완료','GG1'),
('IN68','2025-02-06','입고완료','GG1'),
('IN69','2025-02-12','입고완료','GG1'),
('IN70','2025-02-28','입고완료','GG1');
INSERT INTO `Inbound` (`inboundCode`,`inboundDate`,`inboundStatus`,`warehouseCode`)
VALUES
('IN71','2025-03-16','입고완료','GG1'),
('IN72','2025-03-30','입고완료','DJ1'),
('IN73','2025-03-24','입고완료','DJ1'),
('IN74','2025-03-16','입고완료','GG1'),
('IN75','2025-03-07','입고완료','DJ1'),
('IN76','2025-03-27','입고취소','DJ1'),
('IN77','2025-03-01','입고완료','DJ1'),
('IN78','2025-03-29','입고완료','DJ1'),
('IN79','2025-03-09','입고완료','DJ1'),
('IN80','2025-03-25','입고완료','GG1'),
('IN81','2025-03-31','입고완료','GG1'),
('IN82','2025-03-03','입고완료','DJ1'),
('IN83','2025-03-23','입고완료','DJ1'),
('IN84','2025-03-05','입고취소','DJ1'),
('IN85','2025-03-24','입고완료','DJ1'),
('IN86','2025-03-11','입고완료','GG1'),
('IN87','2025-03-27','입고취소','GG1'),
('IN88','2025-03-20','입고완료','GG1'),
('IN89','2025-03-16','입고취소','DJ1'),
('IN90','2025-03-29','입고완료','GG1'),
('IN91','2025-03-14','입고완료','GG1'),
('IN92','2025-03-21','입고완료','GG1'),
('IN93','2025-03-11','입고취소','DJ1'),
('IN94','2025-03-23','입고완료','GG1'),
('IN95','2025-03-25','입고완료','DJ1'),
('IN96','2025-03-04','입고완료','GG1'),
('IN97','2025-03-21','입고완료','DJ1'),
('IN98','2025-03-09','입고완료','GG1'),
('IN99','2025-03-29','입고완료','GG1'),
('IN100','2025-03-22','입고완료','DJ1'),
('IN101','2025-03-19','입고취소','GG1'),
('IN102','2025-03-13','입고취소','GG1'),
('IN103','2025-03-11','입고취소','GG1'),
('IN104','2025-03-02','입고완료','GG1'),
('IN105','2025-03-25','입고완료','DJ1'),
('IN106','2025-03-14','입고취소','DJ1'),
('IN107','2025-03-28','입고완료','DJ1'),
('IN108','2025-03-23','입고완료','DJ1'),
('IN109','2025-03-17','입고완료','DJ1'),
('IN110','2025-03-07','입고완료','GG1');
INSERT INTO `Inbound` (`inboundCode`,`inboundDate`,`inboundStatus`,`warehouseCode`)
VALUES
('IN111','2025-03-30','입고완료','DJ1'),
('IN112','2025-03-04','입고취소','GG1'),
('IN113','2025-03-13','입고완료','DJ1'),
('IN114','2025-03-12','입고완료','GG1'),
('IN115','2025-03-03','입고취소','DJ1'),
('IN116','2025-03-05','입고완료','GG1'),
('IN117','2025-03-05','입고완료','GG1'),
('IN118','2025-03-05','입고완료','DJ1'),
('IN119','2025-03-05','입고완료','DJ1'),
('IN120','2025-04-08','입고완료','GG1'),
('IN121','2025-04-14','입고완료','DJ1'),
('IN122','2025-04-09','입고완료','GG1'),
('IN123','2025-04-23','입고취소','GG1'),
('IN124','2025-04-23','입고취소','DJ1'),
('IN125','2025-04-17','입고취소','DJ1'),
('IN126','2025-04-04','입고완료','GG1'),
('IN127','2025-04-01','입고완료','DJ1'),
('IN138','2025-04-28','입고취소','GG1'),
('IN139','2025-04-04','입고완료','DJ1'),
('IN140','2025-04-08','입고완료','GG1');
INSERT INTO `Inbound` (`inboundCode`,`inboundDate`,`inboundStatus`,`warehouseCode`)
VALUES
('IN149','2025-04-11','입고완료','GG1'),
('IN150','2025-04-06','입고완료','DJ1'),
('IN151','2025-04-10','입고완료','GG1'),
('IN152','2025-04-02','입고완료','GG1'),
('IN153','2025-04-01','입고취소','DJ1'),
('IN154','2025-04-01','입고완료','DJ1'),
('IN171','2025-04-05','입고완료','DJ1'),
('IN172','2025-04-02','입고완료','DJ1'),
('IN173','2025-04-20','입고완료','GG1'),
('IN174','2025-04-25','입고완료','DJ1'),
('IN175','2025-04-21','입고취소','GG1'),
('IN176','2025-04-16','입고완료','DJ1'),
('IN177','2025-04-06','입고완료','DJ1'),
('IN178','2025-04-26','입고취소','GG1');
INSERT INTO `Inbound` (`inboundCode`,`inboundDate`,`inboundStatus`,`warehouseCode`)
VALUES
('IN161','2025-04-29','승인대기','GG1'),
('IN158','2025-04-30','승인대기','GG1'),
('IN179','2025-04-29','승인대기','GG1'),
('IN182','2025-04-30','승인대기','DJ1'),
('IN185','2025-05-02','승인대기','GG1'),
('IN188','2025-05-04','승인대기','DJ1');
INSERT INTO `Inbound` (`inboundCode`,`inboundDate`,`inboundStatus`,`warehouseCode`)
VALUES
('IN180','2025-05-07','입고요청','DJ1'),
('IN181','2025-05-06','입고요청','GG1'),
('IN183','2025-05-26','입고요청','DJ1'),
('IN184','2025-05-18','입고요청','DJ1'),
('IN186','2025-05-22','입고요청','GG1'),
('IN187','2025-05-29','입고요청','GG1');
-- -----------------------------------------------------------------------------------------------------------------
INSERT INTO `InboundDetail` (`inboundDetailCode`, `quantity`, `inboundCode`, `productCode`, `sectionCode`)
VALUES
('IND2-1', 15, 'IN2', 'DPN1', 'GG1-R'),
('IND6-1', 17, 'IN6', 'DPN2', 'DJ1-R'),
('IND13-1', 13, 'IN13', 'DGL1', 'DJ1-R'),
('IND16-1', 18, 'IN16', 'DPN3', 'GG1-R'),
('IND19-1', 16, 'IN19', 'DGL2', 'GG1-R'),
('IND29-1', 19, 'IN29', 'DPN4', 'GG1-R'),
('IND34-1', 14, 'IN34', 'DGL3', 'GG1-R'),
('IND42-1', 12, 'IN42', 'DLW1', 'GG1-R'),
('IND44-1', 15, 'IN44', 'DLW2', 'GG1-R'),
('IND47-1', 11, 'IN47', 'DLW3', 'DJ1-R'),
('IND54-1', 17, 'IN54', 'DLW4', 'DJ1-R'),
('IND57-1', 18, 'IN57', 'DPN5', 'GG1-R'),
('IND60-1', 16, 'IN60', 'DGL5', 'GG1-R'),
('IND61-1', 19, 'IN61', 'DLW5', 'GG1-R'),
('IND62-1', 15, 'IN62', 'DPN6', 'GG1-R'),
('IND65-1', 14, 'IN65', 'DGL6', 'DJ1-R'),
('IND76-1', 17, 'IN76', 'DPN1', 'DJ1-R'),
('IND84-1', 18, 'IN84', 'DPN2', 'DJ1-R'),
('IND87-1', 19, 'IN87', 'DGL1', 'GG1-R'),
('IND89-1', 13, 'IN89', 'DGL2', 'DJ1-R'),
('IND93-1', 15, 'IN93', 'DLW1', 'DJ1-R'),
('IND101-1', 12, 'IN101', 'DLW2', 'GG1-R'),
('IND102-1', 17, 'IN102', 'DLW3', 'GG1-R'),
('IND103-1', 16, 'IN103', 'DLW4', 'GG1-R'),
('IND106-1', 18, 'IN106', 'DPN5', 'DJ1-R'),
('IND112-1', 14, 'IN112', 'DGL5', 'GG1-R'),
('IND115-1', 19, 'IN115', 'DGL6', 'DJ1-R'),
('IND123-1', 15, 'IN123', 'DLW5', 'GG1-R'),
('IND125-1', 13, 'IN125', 'DLW6', 'DJ1-R'),
('IND153-1', 17, 'IN153', 'DPN6', 'DJ1-R');
INSERT INTO `InboundDetail` (`inboundDetailCode`,`inboundCode`,`productCode`,`sectionCode`,`quantity`)
VALUES
('IN1-1', 'IN1', 'DPN1', 'GG1-R', 480),
('IN2-1', 'IN2', 'DPN2', 'DJ1-R', 520),
('IN3-1', 'IN3', 'DPN3', 'GG1-R', 430),
('IN4-1', 'IN4', 'DPN4', 'GG1-F', 500),
('IN5-1', 'IN5', 'DPN5', 'DJ1-F', 410),
('IN6-1', 'IN6', 'DPN6', 'DJ1-F', 550),
('IN7-1', 'IN7', 'DGL1', 'DJ1-R', 395),
('IN8-1', 'IN8', 'DGL2', 'DJ1-R', 510),
('IN9-1', 'IN9', 'DGL3', 'GG1-R', 470),
('IN10-1', 'IN10', 'DGL4', 'DJ1-F', 505),
('IN11-1', 'IN11', 'DGL5', 'GG1-F', 425),
('IN12-1', 'IN12', 'DGL6', 'DJ1-F', 390),
('IN13-1', 'IN13', 'DLW1', 'DJ1-R', 530),
('IN14-1', 'IN14', 'DLW2', 'DJ1-R', 495),
('IN15-1', 'IN15', 'DLW3', 'DJ1-R', 450),
('IN16-1', 'IN16', 'DLW4', 'GG1-F', 570),
('IN17-1', 'IN17', 'DLW5', 'GG1-F', 360),
('IN18-1', 'IN18', 'DLW6', 'GG1-F', 580),
('IN19-1', 'IN19', 'BGL1', 'GG1-F', 400),
('IN20-1', 'IN20', 'CDC1', 'DJ1-A', 550),
('IN21-1', 'IN21', 'TZR1', 'GG1-A', 480),
('IN22-1', 'IN22', 'MPK1', 'GG1-A', 500),
('IN23-1', 'IN23', 'TPK1', 'DJ1-A', 460),
('IN24-1', 'IN24', 'DPN1', 'GG1-R', 430),
('IN25-1', 'IN25', 'DPN2', 'GG1-R', 530),
('IN26-1', 'IN26', 'DPN3', 'GG1-R', 500),
('IN27-1', 'IN27', 'DPN4', 'GG1-F', 410),
('IN28-1', 'IN28', 'DPN5', 'DJ1-F', 470),
('IN29-1', 'IN29', 'DPN6', 'GG1-F', 525),
('IN30-1', 'IN30', 'DGL1', 'GG1-R', 450),
('IN31-1', 'IN31', 'DGL2', 'DJ1-R', 490),
('IN32-1', 'IN32', 'DGL3', 'GG1-R', 520),
('IN33-1', 'IN33', 'DGL4', 'GG1-F', 400),
('IN34-1', 'IN34', 'DGL5', 'GG1-F', 490),
('IN35-1', 'IN35', 'DGL6', 'DJ1-F', 470),
('IN36-1', 'IN36', 'DLW1', 'DJ1-R', 510),
('IN37-1', 'IN37', 'DLW2', 'GG1-R', 460),
('IN38-1', 'IN38', 'DLW3', 'GG1-R', 445),
('IN39-1', 'IN39', 'DLW4', 'DJ1-F', 500),
('IN40-1', 'IN40', 'DLW5', 'GG1-F', 520);
INSERT INTO `InboundDetail` (`inboundDetailCode`,`inboundCode`,`productCode`,`sectionCode`,`quantity`)
VALUES
('IN41-1', 'IN41', 'DPN4', 'GG1-F', 510),
('IN42-1', 'IN42', 'DPN5', 'GG1-F', 445),
('IN43-1', 'IN43', 'DGL6', 'GG1-F', 430),
('IN44-1', 'IN44', 'DGL2', 'GG1-R', 400),
('IN45-1', 'IN45', 'DPN2', 'GG1-R', 540),
('IN46-1', 'IN46', 'DLW1', 'GG1-R', 480),
('IN47-1', 'IN47', 'DLW5', 'DJ1-F', 460),
('IN48-1', 'IN48', 'DGL5', 'GG1-F', 450),
('IN49-1', 'IN49', 'DGL1', 'GG1-R', 495),
('IN50-1', 'IN50', 'DLW4', 'GG1-F', 470),
('IN51-1', 'IN51', 'DPN6', 'GG1-F', 530),
('IN52-1', 'IN52', 'DLW2', 'GG1-R', 490),
('IN53-1', 'IN53', 'DGL3', 'GG1-R', 510),
('IN54-1', 'IN54', 'CDC1', 'DJ1-A', 425),
('IN55-1', 'IN55', 'BGL1', 'GG1-F', 520),
('IN56-1', 'IN56', 'TPK1', 'DJ1-A', 395),
('IN57-1', 'IN57', 'DLW6', 'GG1-F', 440),
('IN58-1', 'IN58', 'TZR1', 'DJ1-A', 510),
('IN59-1', 'IN59', 'MPK1', 'DJ1-A', 430),
('IN60-1', 'IN60', 'DPN1', 'GG1-R', 500),
('IN61-1', 'IN61', 'DPN3', 'GG1-R', 475),
('IN62-1', 'IN62', 'DGL4', 'GG1-F', 445),
('IN63-1', 'IN63', 'DLW3', 'GG1-R', 505),
('IN64-1', 'IN64', 'DLW2', 'DJ1-R', 465),
('IN65-1', 'IN65', 'DGL5', 'DJ1-F', 480),
('IN66-1', 'IN66', 'BGL1', 'DJ1-F', 450),
('IN67-1', 'IN67', 'DPN2', 'GG1-R', 495),
('IN68-1', 'IN68', 'DLW5', 'GG1-F', 520),
('IN69-1', 'IN69', 'DLW6', 'GG1-F', 470),
('IN70-1', 'IN70', 'DGL2', 'GG1-R', 500);
INSERT INTO `InboundDetail` (`inboundDetailCode`,`inboundCode`,`productCode`,`sectionCode`,`quantity`)
VALUES
('IN71-1', 'IN71', 'DLW1', 'GG1-R', 480),
('IN72-1', 'IN72', 'DPN1', 'DJ1-R', 520),
('IN73-1', 'IN73', 'DPN4', 'DJ1-F', 470),
('IN74-1', 'IN74', 'DGL3', 'GG1-R', 495),
('IN75-1', 'IN75', 'DLW3', 'DJ1-R', 450),
('IN76-1', 'IN76', 'BGL1', 'DJ1-F', 490),
('IN77-1', 'IN77', 'DPN5', 'DJ1-F', 530),
('IN78-1', 'IN78', 'DGL6', 'DJ1-F', 410),
('IN79-1', 'IN79', 'CDC1', 'DJ1-A', 550),
('IN80-1', 'IN80', 'DPN3', 'GG1-R', 440),
('IN81-1', 'IN81', 'DGL4', 'GG1-F', 460),
('IN82-1', 'IN82', 'TZR1', 'DJ1-A', 500),
('IN83-1', 'IN83', 'DGL2', 'DJ1-R', 480),
('IN84-1', 'IN84', 'DLW4', 'DJ1-F', 495),
('IN85-1', 'IN85', 'MPK1', 'DJ1-A', 420),
('IN86-1', 'IN86', 'DPN6', 'GG1-F', 510),
('IN87-1', 'IN87', 'DPN2', 'GG1-R', 470),
('IN88-1', 'IN88', 'DLW6', 'GG1-F', 440),
('IN89-1', 'IN89', 'DGL5', 'DJ1-F', 460),
('IN90-1', 'IN90', 'DPN1', 'GG1-R', 485),
('IN91-1', 'IN91', 'DLW2', 'GG1-R', 455),
('IN92-1', 'IN92', 'DGL1', 'GG1-R', 470),
('IN93-1', 'IN93', 'DPN4', 'DJ1-F', 495),
('IN94-1', 'IN94', 'DGL2', 'GG1-R', 460),
('IN95-1', 'IN95', 'DGL3', 'DJ1-R', 430),
('IN96-1', 'IN96', 'DGL6', 'GG1-F', 510),
('IN97-1', 'IN97', 'DPN5', 'DJ1-F', 525),
('IN98-1', 'IN98', 'DPN2', 'GG1-R', 450),
('IN99-1', 'IN99', 'DLW5', 'GG1-F', 520),
('IN100-1', 'IN100', 'TZR1', 'DJ1-A', 540),
('IN101-1', 'IN101', 'TPK1', 'GG1-A', 470),
('IN102-1', 'IN102', 'MPK1', 'GG1-A', 440),
('IN103-1', 'IN103', 'DPN3', 'GG1-R', 495),
('IN104-1', 'IN104', 'DPN6', 'GG1-F', 490),
('IN105-1', 'IN105', 'DLW1', 'DJ1-R', 520),
('IN106-1', 'IN106', 'DGL1', 'DJ1-R', 410),
('IN107-1', 'IN107', 'DLW4', 'DJ1-F', 460),
('IN108-1', 'IN108', 'DGL5', 'DJ1-F', 480),
('IN109-1', 'IN109', 'DGL6', 'DJ1-F', 470),
('IN110-1', 'IN110', 'DGL2', 'GG1-R', 455);
INSERT INTO `InboundDetail` (`inboundDetailCode`,`inboundCode`,`productCode`,`sectionCode`,`quantity`)
VALUES
('IN111-1', 'IN111', 'DLW5', 'DJ1-F', 470),
('IN113-1', 'IN113', 'DGL2', 'DJ1-R', 495),
('IN114-1', 'IN114', 'DPN1', 'GG1-R', 480),
('IN116-1', 'IN116', 'DGL6', 'GG1-F', 510),
('IN117-1', 'IN117', 'DPN6', 'GG1-F', 465),
('IN118-1', 'IN118', 'DLW1', 'DJ1-R', 450),
('IN119-1', 'IN119', 'TZR1', 'DJ1-A', 520),
('IN120-1', 'IN120', 'DGL1', 'GG1-R', 500),
('IN121-1', 'IN121', 'DLW6', 'DJ1-F', 530),
('IN122-1', 'IN122', 'TPK1', 'GG1-A', 510),
('IN124-1', 'IN124', 'DPN2', 'DJ1-R', 460),
('IN126-1', 'IN126', 'BGL1', 'GG1-F', 470),
('IN127-1', 'IN127', 'DPN4', 'DJ1-F', 495),
('IN138-1', 'IN138', 'DGL5', 'GG1-F', 480),
('IN139-1', 'IN139', 'DLW3', 'DJ1-R', 450),
('IN140-1', 'IN140', 'DPN5', 'GG1-F', 520);
INSERT INTO `InboundDetail` (`inboundDetailCode`,`inboundCode`,`productCode`,`sectionCode`,`quantity`)
VALUES
('IN149-1', 'IN149', 'DGL3', 'GG1-R', 470),
('IN150-1', 'IN150', 'DPN5', 'DJ1-F', 490),
('IN151-1', 'IN151', 'DLW1', 'GG1-R', 510),
('IN152-1', 'IN152', 'TZR1', 'GG1-A', 450),
('IN154-1', 'IN154', 'DPN2', 'DJ1-R', 480),
('IN171-1', 'IN171', 'DGL6', 'DJ1-F', 500),
('IN172-1', 'IN172', 'DLW5', 'DJ1-F', 460),
('IN173-1', 'IN173', 'DPN1', 'GG1-R', 520),
('IN174-1', 'IN174', 'DGL2', 'DJ1-R', 495),
('IN175-1', 'IN175', 'DGL5', 'GG1-F', 480),
('IN176-1', 'IN176', 'BGL1', 'DJ1-F', 470),
('IN177-1', 'IN177', 'DPN4', 'DJ1-F', 490),
('IN178-1', 'IN178', 'MPK1', 'GG1-A', 505);
INSERT INTO `InboundDetail` (`inboundDetailCode`,`inboundCode`,`productCode`,`sectionCode`,`quantity`)
VALUES
('IN161-1','IN161','DLW3','GG1-R',350),
('IN161-2','IN161','DPN5','GG1-F',383),
('IN161-3','IN161','MPK1','GG1-A',520),
('IN158-1','IN158','DGL1','GG1-R',468),
('IN158-2','IN158','TZR1','GG1-A',560),
('IN158-3','IN158','DLW6','GG1-F',383),
('IN179-1','IN179','TPK1','GG1-A',566),
('IN179-2','IN179','DPN6','GG1-F',495),
('IN179-3','IN179','DGL4','GG1-F',479),
('IN182-1','IN182','DPN4','DJ1-F',552),
('IN182-2','IN182','CDC1','DJ1-A',528),
('IN182-3','IN182','DGL2','DJ1-R',535),
('IN185-1','IN185','BGL1','GG1-F',433),
('IN185-2','IN185','DPN1','GG1-R',332),
('IN185-3','IN185','TZR1','GG1-A',429),
('IN185-4','IN185','DGL6','GG1-F',521),
('IN188-1','IN188','MPK1','DJ1-A',448),
('IN188-2','IN188','DLW2','DJ1-R',515),
('IN188-3','IN188','MPK1','DJ1-A',448);
INSERT INTO `InboundDetail` (`inboundDetailCode`,`inboundCode`,`productCode`,`sectionCode`,`quantity`)
VALUES
('IN180-1','IN180','DGL3','DJ1-R',508),
('IN180-2','IN180','DPN3','DJ1-R',436),
('IN181-1','IN181','DPN2','GG1-R',574),
('IN181-2','IN181','DLW1','GG1-R',587),
('IN183-1','IN183','DGL5','DJ1-F',472),
('IN183-2','IN183','DPN2','DJ1-R',587),
('IN183-3','IN183','DPN6','DJ1-F',470),
('IN183-4','IN183','DPN5','DJ1-F',425),
('IN184-1','IN184','DPN1','DJ1-R',493),
('IN184-2','IN184','DLW2','DJ1-R',335),
('IN184-3','IN184','DPN3','DJ1-R',303),
('IN186-1','IN186','DLW3','GG1-R',372),
('IN186-2','IN186','DPN4','GG1-F',364),
('IN186-3','IN186','DPN2','GG1-R',310),
('IN186-4','IN186','DPN3','GG1-R',449),
('IN187-1','IN187','BGL1','GG1-F',475),
('IN187-2','IN187','DLW4','GG1-F',567),
('IN187-3','IN187','DGL3','GG1-R',547),
('IN187-4','IN187','DPN2','GG1-R',405),
('IN187-5','IN187','DGL4','GG1-F',569);
2-1) 발주/출고-발주/출고상세 더미데이터 추가
-- =================================================================
-- 과거데이터 : 출고완료
-- 미래데이터(4월말 ~ 5월초) : 출고대기
-- 미래데이터(5월 중순) : 출고대기
INSERT INTO `Order` (`orderCode`,`orderDate`,`orderStatus`,`franchiseCode`,`warehouseCode`)
VALUES
('ORD1','2025-01-21','출고완료','GGF1','GG1'),
('ORD2','2025-01-01','출고완료','CBF1','DJ1'),
('ORD3','2025-01-09','출고완료','DJF1','DJ1'),
('ORD4','2025-01-08','출고완료','DJF1','DJ1'),
('ORD5','2025-01-24','출고완료','GGF1','GG1'),
('ORD6','2025-01-22','출고완료','CBF1','DJ1'),
('ORD7','2025-01-29','출고완료','GWF1','DJ1'),
('ORD8','2025-01-03','출고완료','GWF1','DJ1'),
('ORD9','2025-01-14','출고완료','GGF1','GG1'),
('ORD10','2025-01-01','출고완료','GGF1','GG1');
INSERT INTO `Order` (`orderCode`,`orderDate`,`orderStatus`,`franchiseCode`,`warehouseCode`)
VALUES
('ORD11','2025-02-07','출고완료','DJF1','DJ1'),
('ORD12','2025-02-17','출고완료','GWF1','DJ1'),
('ORD13','2025-02-01','출고완료','GWF1','DJ1'),
('ORD14','2025-02-07','출고완료','CBF1','DJ1'),
('ORD15','2025-02-21','출고완료','CBF1','DJ1'),
('ORD16','2025-02-18','출고완료','ICF1','GG1'),
('ORD17','2025-02-08','출고완료','ICF1','GG1'),
('ORD18','2025-02-19','출고완료','SLF1','GG1'),
('ORD19','2025-02-26','출고완료','JBF1','DJ1'),
('ORD20','2025-02-01','출고완료','JBF1','DJ1'),
('ORD21','2025-02-26','출고완료','DJF1','DJ1'),
('ORD22','2025-02-23','출고완료','ICF1','GG1'),
('ORD23','2025-02-11','출고완료','SLF1','GG1'),
('ORD24','2025-02-05','출고완료','DJF1','DJ1'),
('ORD25','2025-02-25','출고완료','SLF1','GG1'),
('ORD26','2025-02-04','출고완료','GGF1','GG1'),
('ORD27','2025-02-13','출고완료','GGF1','GG1'),
('ORD28','2025-02-12','출고완료','JBF1','DJ1'),
('ORD29','2025-02-12','출고완료','GWF1','DJ1'),
('ORD30','2025-02-09','출고완료','JBF1','DJ1');
INSERT INTO `Order` (`orderCode`,`orderDate`,`orderStatus`,`franchiseCode`,`warehouseCode`)
VALUES
('ORD31','2025-03-02','출고완료','CBF1','DJ1'),
('ORD32','2025-03-15','출고완료','GWF1','DJ1'),
('ORD33','2025-03-04','출고완료','ICF1','GG1'),
('ORD34','2025-03-03','출고완료','GWF1','DJ1'),
('ORD35','2025-03-10','출고완료','JBF1','DJ1'),
('ORD36','2025-03-21','출고완료','GWF1','DJ1'),
('ORD37','2025-03-29','출고완료','JBF1','DJ1'),
('ORD38','2025-03-12','출고완료','GWF1','DJ1'),
('ORD39','2025-03-07','출고완료','CBF1','DJ1'),
('ORD40','2025-03-03','출고완료','GGF1','GG1'),
('ORD41','2025-03-22','출고완료','DJF1','DJ1'),
('ORD42','2025-03-25','출고완료','SLF1','GG1'),
('ORD43','2025-03-03','출고완료','JBF1','DJ1'),
('ORD44','2025-03-08','출고완료','JBF1','DJ1'),
('ORD45','2025-03-04','출고완료','ICF1','GG1'),
('ORD46','2025-03-09','출고완료','ICF1','GG1'),
('ORD47','2025-03-21','출고완료','JBF1','DJ1'),
('ORD48','2025-03-12','출고완료','DJF1','DJ1'),
('ORD49','2025-03-12','출고완료','SLF1','GG1'),
('ORD50','2025-03-07','출고완료','CBF1','DJ1'),
('ORD51','2025-03-09','출고완료','CBF1','DJ1'),
('ORD52','2025-03-30','출고완료','CBF1','DJ1'),
('ORD53','2025-03-21','출고완료','GGF1','GG1'),
('ORD54','2025-03-20','출고완료','CBF1','DJ1'),
('ORD55','2025-03-06','출고완료','GWF1','DJ1'),
('ORD56','2025-03-24','출고완료','DJF1','DJ1'),
('ORD57','2025-03-06','출고완료','ICF1','GG1'),
('ORD58','2025-03-13','출고완료','SLF1','GG1'),
('ORD59','2025-03-30','출고완료','CBF1','DJ1'),
('ORD60','2025-03-23','출고완료','GWF1','DJ1'),
('ORD61','2025-03-08','출고완료','CBF1','DJ1'),
('ORD62','2025-03-11','출고완료','JBF1','DJ1'),
('ORD63','2025-03-25','출고완료','JBF1','DJ1'),
('ORD64','2025-03-02','출고완료','DJF1','DJ1'),
('ORD65','2025-03-27','출고완료','GGF1','GG1'),
('ORD66','2025-03-26','출고완료','SLF1','GG1'),
('ORD67','2025-03-13','출고완료','SLF1','GG1'),
('ORD68','2025-03-03','출고완료','DJF1','DJ1'),
('ORD69','2025-03-30','출고완료','GWF1','DJ1'),
('ORD70','2025-03-29','출고완료','CBF1','DJ1');
INSERT INTO `Order` (`orderCode`,`orderDate`,`orderStatus`,`franchiseCode`,`warehouseCode`)
VALUES
('ORD71','2025-03-11','출고완료','DJF1','DJ1'),
('ORD72','2025-03-21','출고완료','ICF1','GG1'),
('ORD73','2025-03-13','출고완료','CBF1','DJ1'),
('ORD74','2025-03-15','출고완료','DJF1','DJ1'),
('ORD75','2025-03-09','출고완료','DJF1','DJ1'),
('ORD76','2025-03-08','출고완료','CBF1','DJ1'),
('ORD77','2025-03-18','출고완료','GWF1','DJ1'),
('ORD78','2025-03-09','출고완료','CBF1','DJ1'),
('ORD79','2025-03-19','출고완료','ICF1','GG1'),
('ORD80','2025-03-29','출고완료','GWF1','DJ1'),
('ORD81','2025-03-13','출고완료','SLF1','GG1'),
('ORD82','2025-03-08','출고완료','DJF1','DJ1'),
('ORD83','2025-03-17','출고완료','ICF1','GG1'),
('ORD84','2025-03-03','출고완료','JBF1','DJ1'),
('ORD85','2025-03-02','출고완료','JBF1','DJ1'),
('ORD86','2025-03-04','출고완료','DJF1','DJ1'),
('ORD87','2025-04-20','출고완료','GGF1','GG1'),
('ORD88','2025-03-26','출고완료','CBF1','DJ1'),
('ORD89','2025-03-14','출고완료','GWF1','DJ1'),
('ORD90','2025-03-03','출고완료','ICF1','GG1'),
('ORD91','2025-03-13','출고완료','GWF1','DJ1'),
('ORD92','2025-03-15','출고완료','GWF1','DJ1'),
('ORD93','2025-03-09','출고완료','GWF1','DJ1'),
('ORD94','2025-03-28','출고완료','GGF1','GG1'),
('ORD95','2025-03-22','출고완료','CBF1','DJ1'),
('ORD96','2025-03-04','출고완료','CBF1','DJ1'),
('ORD97','2025-03-29','출고완료','GWF1','DJ1'),
('ORD98','2025-03-25','출고완료','SLF1','GG1'),
('ORD99','2025-03-25','출고완료','CBF1','DJ1'),
('ORD100','2025-03-11','출고완료','GGF1','GG1'),
('ORD101','2025-03-10','출고완료','ICF1','GG1'),
('ORD102','2025-03-06','출고완료','ICF1','GG1'),
('ORD103','2025-03-01','출고완료','CBF1','DJ1'),
('ORD104','2025-03-29','출고완료','CBF1','DJ1'),
('ORD105','2025-03-09','출고완료','GWF1','DJ1');
INSERT INTO `Order` (`orderCode`,`orderDate`,`orderStatus`,`franchiseCode`,`warehouseCode`)
VALUES
('ORD106','2025-04-25','출고완료','DJF1','DJ1'),
('ORD107','2025-04-17','출고완료','GGF1','GG1'),
('ORD108','2025-04-28','출고완료','CBF1','DJ1'),
('ORD109','2025-04-15','출고완료','JBF1','DJ1'),
('ORD110','2025-04-21','출고완료','GWF1','DJ1'),
('ORD111','2025-04-20','출고완료','DJF1','DJ1'),
('ORD112','2025-04-05','출고완료','SLF1','GG1'),
('ORD113','2025-04-25','출고완료','DJF1','DJ1'),
('ORD114','2025-04-18','출고완료','JBF1','DJ1'),
('ORD115','2025-04-17','출고완료','GGF1','GG1'),
('ORD116','2025-04-20','출고완료','SLF1','GG1'),
('ORD117','2025-04-16','출고완료','GGF1','GG1'),
('ORD118','2025-04-04','출고완료','SLF1','GG1'),
('ORD119','2025-04-27','출고완료','JBF1','DJ1'),
('ORD120','2025-04-16','출고완료','DJF1','DJ1'),
('ORD121','2025-04-02','출고완료','DJF1','DJ1'),
('ORD122','2025-04-28','출고완료','GGF1','GG1'),
('ORD123','2025-04-03','출고완료','CBF1','DJ1'),
('ORD124','2025-04-16','출고완료','JBF1','DJ1'),
('ORD125','2025-04-03','출고완료','JBF1','DJ1'),
('ORD126','2025-04-18','출고완료','JBF1','DJ1'),
('ORD127','2025-04-05','출고완료','DJF1','DJ1'),
('ORD128','2025-04-22','출고완료','ICF1','GG1'),
('ORD129','2025-04-18','출고완료','DJF1','DJ1'),
('ORD130','2025-04-14','출고완료','GWF1','DJ1'),
('ORD131','2025-04-28','출고완료','GWF1','DJ1'),
('ORD132','2025-04-14','출고완료','DJF1','DJ1'),
('ORD133','2025-04-18','출고완료','JBF1','DJ1'),
('ORD134','2025-04-24','출고완료','CBF1','DJ1'),
('ORD135','2025-04-07','출고완료','CBF1','DJ1'),
('ORD136','2025-04-10','출고완료','ICF1','GG1'),
('ORD137','2025-04-22','출고완료','CBF1','DJ1'),
('ORD138','2025-04-14','출고완료','ICF1','GG1'),
('ORD139','2025-04-17','출고완료','ICF1','GG1'),
('ORD140','2025-04-04','출고완료','DJF1','DJ1'),
('ORD141','2025-04-08','출고완료','GGF1','GG1'),
('ORD142','2025-04-14','출고완료','GGF1','GG1'),
('ORD143','2025-04-19','출고완료','GWF1','DJ1'),
('ORD144','2025-04-08','출고완료','GWF1','DJ1'),
('ORD145','2025-04-08','출고완료','GGF1','GG1'),
('ORD146','2025-04-03','출고완료','CBF1','DJ1'),
('ORD147','2025-04-21','출고완료','GGF1','GG1'),
('ORD148','2025-04-08','출고완료','GGF1','GG1'),
('ORD149','2025-04-02','출고완료','JBF1','DJ1'),
('ORD150','2025-04-11','출고완료','GGF1','GG1');
INSERT INTO `Order` (`orderCode`,`orderDate`,`orderStatus`,`franchiseCode`,`warehouseCode`)
VALUES
('ORD151','2025-04-29','출고대기','GGF1','GG1'),
('ORD152','2025-04-29','출고대기','SLF1','GG1'),
('ORD153','2025-04-30','출고대기','GGF1','GG1'),
('ORD154','2025-05-04','출고대기','DJF1','DJ1'),
('ORD155','2025-05-05','출고대기','GWF1','DJ1'),
('ORD156','2025-05-04','출고대기','ICF1','GG1'),
('ORD157','2025-05-01','출고대기','JBF1','DJ1'),
('ORD158','2025-05-03','출고대기','JBF1','DJ1');
INSERT INTO `Order` (`orderCode`,`orderDate`,`orderStatus`,`franchiseCode`,`warehouseCode`)
VALUES
('ORD159','2025-05-12','출고대기','GGF1','GG1'),
('ORD160','2025-05-12','출고대기','SLF1','GG1'),
('ORD161','2025-05-13','출고대기','DJF1','DJ1'),
('ORD162','2025-05-07','출고대기','ICF1','GG1'),
('ORD163','2025-05-11','출고대기','DJF1','DJ1'),
('ORD164','2025-05-09','출고대기','ICF1','GG1'),
('ORD165','2025-05-13','출고대기','GGF1','GG1'),
('ORD166','2025-05-10','출고대기','GGF1','GG1'),
('ORD167','2025-05-13','출고대기','DJF1','DJ1');
-- ----------------------------------------------------------------------------------------------------------------------
INSERT INTO `OrderDetail` (`orderDetailCode`, `orderCode`, `productCode`, `quantity`)
VALUES
('ORD4-1', 'ORD4', 'DPN5', 45),
('ORD11-1', 'ORD11', 'DGL6', 33),
('ORD15-1', 'ORD15', 'DGL5', 36),
('ORD18-1', 'ORD18', 'DGL4', 48),
('ORD37-1', 'ORD37', 'DLW5', 40),
('ORD41-1', 'ORD41', 'DPN2', 39),
('ORD46-1', 'ORD46', 'DPN6', 41),
('ORD48-1', 'ORD48', 'DGL3', 34),
('ORD53-1', 'ORD53', 'DLW6', 42),
('ORD55-1', 'ORD55', 'DGL2', 38),
('ORD57-1', 'ORD57', 'DPN3', 37),
('ORD65-1', 'ORD65', 'DPN4', 44),
('ORD76-1', 'ORD76', 'DLW3', 50),
('ORD83-1', 'ORD83', 'DPN1', 46),
('ORD90-1', 'ORD90', 'DGL1', 40),
('ORD103-1', 'ORD103', 'DLW4', 47);
INSERT INTO `OrderDetail` (`orderDetailCode`, `orderCode`, `productCode`, `quantity`)
VALUES
('ORD1-1', 'ORD1', 'DPN1', 25),
('ORD2-1', 'ORD2', 'DLW4', 30),
('ORD3-1', 'ORD3', 'DGL2', 40),
('ORD5-1', 'ORD5', 'DPN2', 35),
('ORD6-1', 'ORD6', 'TZR1', 28),
('ORD7-1', 'ORD7', 'DGL5', 22),
('ORD8-1', 'ORD8', 'DPN5', 31),
('ORD9-1', 'ORD9', 'DGL1', 45),
('ORD10-1', 'ORD10', 'DPN3', 50);
INSERT INTO `OrderDetail` (`orderDetailCode`, `orderCode`, `productCode`, `quantity`)
VALUES
('ORD12-1', 'ORD12', 'DPN3', 40),
('ORD13-1', 'ORD13', 'DGL2', 35),
('ORD14-1', 'ORD14', 'DLW5', 28),
('ORD16-1', 'ORD16', 'DPN2', 45),
('ORD17-1', 'ORD17', 'DGL5', 22),
('ORD19-1', 'ORD19', 'TZR1', 30),
('ORD20-1', 'ORD20', 'DLW1', 37),
('ORD21-1', 'ORD21', 'DPN5', 32),
('ORD22-1', 'ORD22', 'DPN6', 41),
('ORD23-1', 'ORD23', 'DGL6', 26),
('ORD24-1', 'ORD24', 'DLW2', 44),
('ORD25-1', 'ORD25', 'MPK1', 21),
('ORD26-1', 'ORD26', 'DGL1', 36),
('ORD27-1', 'ORD27', 'DGL2', 47),
('ORD28-1', 'ORD28', 'CDC1', 39),
('ORD29-1', 'ORD29', 'DPN1', 48),
('ORD30-1', 'ORD30', 'DGL4', 34);
INSERT INTO `OrderDetail` (`orderDetailCode`, `orderCode`, `productCode`, `quantity`)
VALUES
('ORD31-1', 'ORD31', 'DPN4', 28),
('ORD32-1', 'ORD32', 'DGL2', 43),
('ORD33-1', 'ORD33', 'DLW3', 32),
('ORD34-1', 'ORD34', 'DPN6', 25),
('ORD35-1', 'ORD35', 'DGL5', 41),
('ORD36-1', 'ORD36', 'DLW5', 37),
('ORD38-1', 'ORD38', 'DPN2', 29),
('ORD39-1', 'ORD39', 'DLW2', 35),
('ORD40-1', 'ORD40', 'DGL1', 38),
('ORD42-1', 'ORD42', 'TZR1', 31),
('ORD43-1', 'ORD43', 'DPN5', 40),
('ORD44-1', 'ORD44', 'DGL4', 36),
('ORD45-1', 'ORD45', 'DGL3', 42),
('ORD47-1', 'ORD47', 'DGL2', 34),
('ORD49-1', 'ORD49', 'DLW6', 47),
('ORD50-1', 'ORD50', 'MPK1', 27),
('ORD51-1', 'ORD51', 'DPN1', 24),
('ORD52-1', 'ORD52', 'DGL5', 30),
('ORD54-1', 'ORD54', 'CDC1', 39),
('ORD56-1', 'ORD56', 'DLW1', 28),
('ORD58-1', 'ORD58', 'DPN3', 45),
('ORD59-1', 'ORD59', 'DLW2', 32),
('ORD60-1', 'ORD60', 'BGL1', 26),
('ORD61-1', 'ORD61', 'DGL6', 48),
('ORD62-1', 'ORD62', 'DLW3', 37),
('ORD63-1', 'ORD63', 'DGL1', 44),
('ORD64-1', 'ORD64', 'DPN2', 30),
('ORD66-1', 'ORD66', 'TZR1', 41),
('ORD67-1', 'ORD67', 'DGL4', 35),
('ORD68-1', 'ORD68', 'DPN5', 29),
('ORD69-1', 'ORD69', 'DPN6', 46),
('ORD70-1', 'ORD70', 'MPK1', 34);
INSERT INTO `OrderDetail` (`orderDetailCode`, `orderCode`, `productCode`, `quantity`)
VALUES
('ORD71-1', 'ORD71', 'DGL3', 36),
('ORD72-1', 'ORD72', 'DLW6', 48),
('ORD73-1', 'ORD73', 'DGL1', 34),
('ORD74-1', 'ORD74', 'DGL6', 42),
('ORD75-1', 'ORD75', 'DPN4', 39),
('ORD77-1', 'ORD77', 'DPN1', 45),
('ORD78-1', 'ORD78', 'DLW2', 28),
('ORD79-1', 'ORD79', 'DGL2', 44),
('ORD80-1', 'ORD80', 'DLW3', 40),
('ORD81-1', 'ORD81', 'TZR1', 30),
('ORD82-1', 'ORD82', 'DPN5', 27),
('ORD84-1', 'ORD84', 'DPN6', 29),
('ORD85-1', 'ORD85', 'DGL5', 32),
('ORD86-1', 'ORD86', 'DLW5', 41),
('ORD87-1', 'ORD87', 'BGL1', 33),
('ORD88-1', 'ORD88', 'DGL1', 38),
('ORD89-1', 'ORD89', 'DPN2', 43),
('ORD91-1', 'ORD91', 'DLW2', 26),
('ORD92-1', 'ORD92', 'DPN6', 37),
('ORD93-1', 'ORD93', 'DGL3', 39),
('ORD94-1', 'ORD94', 'MPK1', 31),
('ORD95-1', 'ORD95', 'CDC1', 34),
('ORD96-1', 'ORD96', 'DGL5', 41),
('ORD97-1', 'ORD97', 'DPN4', 35),
('ORD98-1', 'ORD98', 'TZR1', 46),
('ORD99-1', 'ORD99', 'DGL2', 30),
('ORD100-1', 'ORD100', 'DPN3', 44),
('ORD101-1', 'ORD101', 'DLW1', 47),
('ORD102-1', 'ORD102', 'DPN5', 32),
('ORD104-1', 'ORD104', 'DPN6', 40),
('ORD105-1', 'ORD105', 'DLW3', 36);
INSERT INTO `OrderDetail` (`orderDetailCode`, `orderCode`, `productCode`, `quantity`)
VALUES
('ORD106-1', 'ORD106', 'DLW2', 43),
('ORD107-1', 'ORD107', 'DPN5', 46),
('ORD108-1', 'ORD108', 'DGL3', 41),
('ORD109-1', 'ORD109', 'DGL1', 45),
('ORD110-1', 'ORD110', 'DLW3', 37),
('ORD111-1', 'ORD111', 'DPN6', 39),
('ORD112-1', 'ORD112', 'TZR1', 48),
('ORD113-1', 'ORD113', 'DPN4', 35),
('ORD114-1', 'ORD114', 'DLW1', 34),
('ORD115-1', 'ORD115', 'DGL5', 46),
('ORD116-1', 'ORD116', 'MPK1', 44),
('ORD117-1', 'ORD117', 'DGL6', 39),
('ORD118-1', 'ORD118', 'DPN2', 38),
('ORD119-1', 'ORD119', 'DPN3', 49),
('ORD120-1', 'ORD120', 'DPN1', 41),
('ORD121-1', 'ORD121', 'DGL2', 42),
('ORD122-1', 'ORD122', 'DGL3', 45),
('ORD123-1', 'ORD123', 'DLW4', 33),
('ORD124-1', 'ORD124', 'DPN5', 36),
('ORD125-1', 'ORD125', 'DGL5', 29),
('ORD126-1', 'ORD126', 'DLW5', 47),
('ORD127-1', 'ORD127', 'DGL4', 48),
('ORD128-1', 'ORD128', 'DGL2', 50),
('ORD129-1', 'ORD129', 'DGL1', 44),
('ORD130-1', 'ORD130', 'DLW6', 46),
('ORD131-1', 'ORD131', 'DPN6', 47),
('ORD132-1', 'ORD132', 'DGL1', 34),
('ORD133-1', 'ORD133', 'DPN2', 30),
('ORD134-1', 'ORD134', 'DLW3', 43),
('ORD135-1', 'ORD135', 'DGL5', 37),
('ORD136-1', 'ORD136', 'DPN6', 39),
('ORD137-1', 'ORD137', 'DPN5', 40),
('ORD138-1', 'ORD138', 'DLW5', 42),
('ORD139-1', 'ORD139', 'DPN1', 32),
('ORD140-1', 'ORD140', 'DLW2', 41),
('ORD141-1', 'ORD141', 'DGL6', 45),
('ORD142-1', 'ORD142', 'BGL1', 44),
('ORD143-1', 'ORD143', 'DLW4', 47),
('ORD144-1', 'ORD144', 'DLW3', 36),
('ORD145-1', 'ORD145', 'DPN5', 38),
('ORD146-1', 'ORD146', 'DLW6', 49),
('ORD147-1', 'ORD147', 'DPN1', 48),
('ORD148-1', 'ORD148', 'DGL4', 46),
('ORD149-1', 'ORD149', 'DPN6', 41),
('ORD150-1', 'ORD150', 'DPN3', 50);
INSERT INTO `OrderDetail` (`orderDetailCode`,`orderCode`,`productCode`,`quantity`)
VALUES
('ORD151-1','ORD151','DPN1',25),
('ORD151-2','ORD151','DGL3',24),
('ORD152-1','ORD152','DPN4',15),
('ORD152-2','ORD152','MPK1',47),
('ORD152-3','ORD152','DLW6',37),
('ORD153-1','ORD153','DPN1',23),
('ORD153-2','ORD153','DPN3',24),
('ORD154-1','ORD154','DLW6',44),
('ORD154-2','ORD154','DGL1',36),
('ORD155-1','ORD155','DLW3',10),
('ORD155-2','ORD155','BGL1',20),
('ORD155-3','ORD155','DGL3',37),
('ORD156-1','ORD156','DGL3',16),
('ORD156-2','ORD156','DPN5',15),
('ORD156-3','ORD156','DGL1',34),
('ORD156-4','ORD156','DGL5',16),
('ORD157-1','ORD157','DGL6',39),
('ORD157-2','ORD157','CDC1',44),
('ORD157-3','ORD157','DGL3',17),
('ORD157-4','ORD157','DPN2',34),
('ORD158-1','ORD158','DLW6',50),
('ORD158-2','ORD158','DGL4',49);
INSERT INTO `OrderDetail` (`orderDetailCode`,`orderCode`,`productCode`,`quantity`)
VALUES
('ORD159-1','ORD159','DGL4',19),
('ORD159-2','ORD159','TPK1',39),
('ORD160-1','ORD160','MPK1',48),
('ORD160-2','ORD160','TPK1',41),
('ORD160-3','ORD160','DPN4',48),
('ORD160-4','ORD160','DLW3',11),
('ORD161-1','ORD161','BGL1',35),
('ORD161-2','ORD161','DGL6',21),
('ORD161-3','ORD161','CDC1',13),
('ORD161-4','ORD161','DLW6',16),
('ORD161-5','ORD161','DLW2',17),
('ORD162-1','ORD162','DPN5',18),
('ORD162-2','ORD162','DPN4',34),
('ORD162-3','ORD162','DPN6',48),
('ORD162-4','ORD162','DLW5',42),
('ORD162-5','ORD162','DLW4',27),
('ORD163-1','ORD163','DLW1',47),
('ORD163-2','ORD163','DPN3',13),
('ORD163-3','ORD163','DPN2',21),
('ORD164-1','ORD164','DPN6',27),
('ORD164-2','ORD164','DGL4',14),
('ORD165-1','ORD165','BGL1',13),
('ORD165-2','ORD165','TZR1',16),
('ORD166-1','ORD166','TZR1',38),
('ORD166-2','ORD166','DGL5',20),
('ORD166-3','ORD166','DLW5',18),
('ORD166-4','ORD166','MPK1',15),
('ORD166-5','ORD166','TPK1',30),
('ORD167-1','ORD167','BGL1',33),
('ORD167-2','ORD167','DGL3',21);
SET FOREIGN_KEY_CHECKS=1;
3) 제약조건 추가
-- =================================================================
-- 4) FOREIGN KEY 제약조건 추가
-- 2. MemberRequest.authorityCode → Authority.authorityCode
-- · ON DELETE CASCADE: Authority 삭제 시 해당 요청도 함께 삭제
ALTER TABLE `MemberRequest`
ADD CONSTRAINT `fk_mr_authority`
FOREIGN KEY (`authorityCode`)
REFERENCES `Authority` (`authorityCode`)
ON DELETE CASCADE;
-- 5. Section.warehouseCode → Warehouse.warehouseCode
-- · ON DELETE CASCADE: Warehouse 삭제 시 참조하는 Section도 함께 삭제
ALTER TABLE `Section`
ADD CONSTRAINT `fk_sec_wh`
FOREIGN KEY (`warehouseCode`)
REFERENCES `Warehouse` (`warehouseCode`)
ON DELETE CASCADE;
-- 6. Inventory.productCode → Product.productCode
-- · ON DELETE RESTRICT: 재고 존재 시 제품 삭제 차단
-- 7. Inventory.warehouseCode → Warehouse.warehouseCode
-- · ON DELETE RESTRICT: 재고 존재 시 창고 삭제 차단
ALTER TABLE `Inventory`
ADD CONSTRAINT `fk_inv_prod`
FOREIGN KEY (`productCode`)
REFERENCES `Product` (`productCode`)
ON DELETE RESTRICT,
ADD CONSTRAINT `fk_inv_wh`
FOREIGN KEY (`warehouseCode`)
REFERENCES `Warehouse` (`warehouseCode`)
ON DELETE RESTRICT;
-- 9. InboundDetail.inboundCode → Inbound.inboundCode
-- · ON DELETE CASCADE: 입고 기록 삭제 시 상세도 함께 삭제
ALTER TABLE `InboundDetail`
ADD CONSTRAINT `fk_idt_inb`
FOREIGN KEY (`inboundCode`)
REFERENCES `Inbound` (`inboundCode`)
ON DELETE CASCADE;
-- 12. OrderDetail.orderCode → Order.orderCode
-- · ON DELETE CASCADE: 발주 기록 삭제 시 상세도 함께 삭제
-- 13. OrderDetail.productCode → Product.productCode
-- · ON DELETE RESTRICT: 발주 상세 존재 시 제품 삭제 차단
ALTER TABLE `OrderDetail`
ADD CONSTRAINT `fk_od_ord`
FOREIGN KEY (`orderCode`)
REFERENCES `Order` (`orderCode`)
ON DELETE CASCADE,
ADD CONSTRAINT `fk_od_prod`
FOREIGN KEY (`productCode`)
REFERENCES `Product` (`productCode`)
ON DELETE RESTRICT;
-- 14. Product.categoryCode → Category.categoryCode
-- · ON DELETE RESTRICT: 제품 존재 시 카테고리 삭제 차단
ALTER TABLE `Product`
ADD CONSTRAINT `fk_prod_cat`
FOREIGN KEY (`categoryCode`)
REFERENCES `Category` (`categoryCode`)
ON DELETE RESTRICT;
-- =================================================================
-- 5) CHECK 제약조건 추가
-- 1. 제품 가격은 항상 0보다 커야 한다
ALTER TABLE `Product`
ADD CONSTRAINT `chk_prod_price_positive`
CHECK (`productPrice` > 0);
-- 2. 저장 타입은 ‘냉장’, ‘냉동’, ‘상온’ 중 하나여야 한다
ALTER TABLE `Product`
ADD CONSTRAINT `chk_prod_storedType`
CHECK (`storedType` IN ('냉장','냉동','상온'));
-- 3. Section.storedType 유효성 검사
ALTER TABLE `Section`
ADD CONSTRAINT `chk_section_storedType`
CHECK (`storedType` IN ('냉장','냉동','상온'));
-- 4. Section.temperature 범위 검사
ALTER TABLE `Section`
ADD CONSTRAINT `chk_section_temperature`
CHECK (
(`storedType` = '냉장' AND `temperature` BETWEEN 0 AND 10) OR
(`storedType` = '냉동' AND `temperature` BETWEEN -9999 AND -18) OR
(`storedType` = '상온' AND `temperature` BETWEEN 1 AND 35)
);
-- 5. Warehouse.capacityLimit (창고 수용한도) 는 0보다 커야 한다
ALTER TABLE `Warehouse`
ADD CONSTRAINT `chk_wh_capacity_positive`
CHECK (`capacityLimit` > 0);
-- 6. Section.storageCapacity (구역 남은수용한도) 는 0보다 크거나 같아야 한다
ALTER TABLE `Section`
ADD CONSTRAINT `chk_section_capacity_positive`
CHECK (`storageCapacity` >= 0);
-- 7. Inventory.quantity (재고 수량) 은 0보다 커야 한다
ALTER TABLE `Inventory`
ADD CONSTRAINT `chk_inventory_quantity_posiive`
CHECK (`quantity` > 0);
-- =================================================================
-- 6) UNIQUE 제약조건 추가
-- 1. Franchise: franchiseLocation, franchiseName 유니크 추가
ALTER TABLE `Franchise`
ADD CONSTRAINT `unq_franchise_location` UNIQUE (`franchiseLocation`),
ADD CONSTRAINT `unq_fm_code` UNIQUE (`memberCode`),
ADD CONSTRAINT `unq_franchise_name` UNIQUE (`franchiseName`);
-- 2. Warehouse: warehouseName, memberCode, address 유니크 추가
ALTER TABLE `Warehouse`
ADD CONSTRAINT `unq_warehouse_name` UNIQUE (`warehouseName`),
ADD CONSTRAINT `unq_wm_code` UNIQUE (`memberCode`),
ADD CONSTRAINT `unq_warehouse_address` UNIQUE (`address`);
-- 3. Category: 중분류(categoryMid) + 소분류(categorySub) 조합 유니크 추가
ALTER TABLE `Category`
ADD CONSTRAINT `unq_category_mid_sub` UNIQUE (`categoryMid`,`categorySub`);
-- 4. Product: 카테고리(categoryCode) + 제품명(productName) 조합 유니크 추가
ALTER TABLE `Product`
ADD CONSTRAINT `unq_product_cat_name` UNIQUE (`categoryCode`,`productName`);
SET FOREIGN_KEY_CHECKS=1;
CREATE OR REPLACE VIEW OrderInventory AS
SELECT
p.productName,
IFNULL(SUM(odetail.quantity), 0) AS totalOrderRequest,
IFNULL(inv.quantity, 0) AS totalInventory
FROM
product p
LEFT JOIN
orderdetail odetail ON p.productCode = odetail.productCode
LEFT JOIN
`order` o ON odetail.orderCode = o.orderCode
AND o.orderDate >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
AND o.orderStatus = '승인완료'
LEFT JOIN
inventory inv ON p.productCode = inv.productCode
GROUP BY
p.productName
ORDER BY
(totalOrderRequest/totalInventory) DESC;
4)Veiws table
CREATE OR REPLACE VIEW OrderInventory AS
SELECT
p.productName,
IFNULL(SUM(odetail.quantity), 0) AS totalOrderRequest,
IFNULL(SUM(inv.quantity), 0) AS totalInventory
FROM
Product p
LEFT JOIN OrderDetail odetail ON p.productCode = odetail.productCode
LEFT JOIN `Order` o ON odetail.orderCode = o.orderCode
AND o.orderDate >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
AND o.orderStatus = '승인완료'
LEFT JOIN Inventory inv ON p.productCode = inv.productCode
GROUP BY p.productName
ORDER BY (totalOrderRequest / totalInventory) DESC;
5) 조회 SQL 문
-- =================================================================
-- 7) 조회 SQL 문
-- OrderDetail
SELECT * FROM `OrderDetail`;
DESCRIBE `OrderDetail`;
-- Order
SELECT * FROM `Order`;
DESCRIBE `Order`;
-- InboundDetail
SELECT * FROM `InboundDetail`;
DESCRIBE `InboundDetail`;
-- Inbound
SELECT * FROM `Inbound`;
DESCRIBE `Inbound`;
-- Inventory
SELECT * FROM `Inventory`;
DESCRIBE `Inventory`;
-- Product
SELECT * FROM `Product`;
DESCRIBE `Product`;
-- Category
SELECT * FROM `Category`;
DESCRIBE `Category`;
-- Section
SELECT * FROM `Section`;
DESCRIBE `Section`;
-- Warehouse
SELECT * FROM `Warehouse`;
DESCRIBE `Warehouse`;
-- Franchise
SELECT * FROM `Franchise`;
DESCRIBE `Franchise`;
-- MemberRequest
SELECT * FROM `MemberRequest`;
DESCRIBE `MemberRequest`;
-- MemberAccount
SELECT * FROM `MemberAccount`;
DESCRIBE `MemberAccount`;
-- Authority
SELECT * FROM `Authority`;
DESCRIBE `Authority`;
수정기록 (버전관리)
- 0423
- 이메일 발송을 위한 실제 이메일을 가진 계정 추가 (QH1, QH2, QH3)
- 입고, 발주 더미데이터 수정
- product 테이블의 check 제약조건 제거
- 테스트용 팀원 아이디 추가
- 0427
- 회원 전화번호에서 하이픈 제거
- 창고수용량 현실적으로 변경
- 창고수용량 변동, 그리고 현행 재고에 따른 섹션 남은수용한도 수치 변경
- 0428
- 차트용 뷰테이블 추가（OrderInventory）
- 입고-입고상세, 발주/출고-발주/출고상세 더미데이터 추가