|
|
/*
|
|
|
Navicat Premium Dump SQL
|
|
|
|
|
|
Source Server : localhost
|
|
|
Source Server Type : MySQL
|
|
|
Source Server Version : 90100 (9.1.0)
|
|
|
Source Host : localhost:3306
|
|
|
Source Schema : demo
|
|
|
|
|
|
Target Server Type : MySQL
|
|
|
Target Server Version : 90100 (9.1.0)
|
|
|
File Encoding : 65001
|
|
|
|
|
|
Date: 29/12/2024 21:43:27
|
|
|
*/
|
|
|
|
|
|
SET NAMES utf8mb4;
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for customer
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `customer`;
|
|
|
CREATE TABLE `customer` (
|
|
|
`CustomerID` int NOT NULL AUTO_INCREMENT,
|
|
|
`CustomerName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`ContactNumber` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`Email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`CustomerAddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
PRIMARY KEY (`CustomerID`) USING BTREE
|
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of customer
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `customer` VALUES (1, '客户A', '13800000001', 'customerA@example.com', '北京市海淀区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (2, '客户B', '13800000002', 'customerB@example.com', '上海市黄浦区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (3, '客户C', '13800000003', 'customerC@example.com', '广州市越秀区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (4, '客户D', '13800000004', 'customerD@example.com', '深圳市福田区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (5, '客户E', '13800000005', 'customerE@example.com', '成都市锦江区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (6, '客户F', '13800000006', 'customerF@example.com', '杭州市拱墅区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (7, '客户G', '13800000007', 'customerG@example.com', '武汉市武昌区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (8, '客户H', '13800000008', 'customerH@example.com', '南京市玄武区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (9, '客户I', '13800000009', 'customerI@example.com', '西安市碑林区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (10, '客户J', '13800000010', 'customerJ@example.com', '重庆市江北区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (11, '客户A', '13800000001', 'customerA@example.com', '北京市海淀区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (12, '客户B', '13800000002', 'customerB@example.com', '上海市黄浦区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (13, '客户C', '13800000003', 'customerC@example.com', '广州市越秀区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (14, '客户D', '13800000004', 'customerD@example.com', '深圳市福田区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (15, '客户E', '13800000005', 'customerE@example.com', '成都市锦江区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (16, '客户F', '13800000006', 'customerF@example.com', '杭州市拱墅区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (17, '客户G', '13800000007', 'customerG@example.com', '武汉市武昌区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (18, '客户H', '13800000008', 'customerH@example.com', '南京市玄武区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (19, '客户王继威', '13800000009', 'customerI@example.com', '西安市碑林区XX路XX号');
|
|
|
INSERT INTO `customer` VALUES (20, '客户李保航', '13800000010', 'customerJ@example.com', '重庆市江北区XX路XX号');
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for deleted_goods
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `deleted_goods`;
|
|
|
CREATE TABLE `deleted_goods` (
|
|
|
`DeletedGoodsID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`GoodsName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`DeletedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP
|
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of deleted_goods
|
|
|
-- ----------------------------
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for employee
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `employee`;
|
|
|
CREATE TABLE `employee` (
|
|
|
`EmployeeID` int NOT NULL AUTO_INCREMENT,
|
|
|
`EmployeeName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`EmployeePosition` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`ContactNumber` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`Email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
PRIMARY KEY (`EmployeeID`) USING BTREE,
|
|
|
INDEX `idx_employee_name`(`EmployeeName` ASC) USING BTREE,
|
|
|
UNIQUE INDEX `idx_employee_employeeid`(`EmployeeID` ASC) USING BTREE,
|
|
|
INDEX `idx_employee_EmployeePosition`(`EmployeePosition` ASC) USING BTREE,
|
|
|
INDEX `idx_employee_employeename`(`EmployeeName` ASC) USING BTREE
|
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of employee
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `employee` VALUES (1, '员工A', '仓库管理员', '13900000001', 'employeeA@example.com');
|
|
|
INSERT INTO `employee` VALUES (2, '员工B', '物流配送人员', '13900000002', 'employeeB@example.com');
|
|
|
INSERT INTO `employee` VALUES (3, '员工C', '采购人员', '13900000003', 'employeeC@example.com');
|
|
|
INSERT INTO `employee` VALUES (4, '员工D', '仓库管理员', '13900000004', 'employeeD@example.com');
|
|
|
INSERT INTO `employee` VALUES (5, '员工E', '物流配送人员', '13900000005', 'employeeE@example.com');
|
|
|
INSERT INTO `employee` VALUES (6, '员工F', '采购人员', '13900000006', 'employeeF@example.com');
|
|
|
INSERT INTO `employee` VALUES (7, '员工G', '仓库管理员', '13900000007', 'employeeG@example.com');
|
|
|
INSERT INTO `employee` VALUES (8, '员工H', '物流配送人员', '13900000008', 'employeeH@example.com');
|
|
|
INSERT INTO `employee` VALUES (9, '员工I', '采购人员', '13900000009', 'employeeI@example.com');
|
|
|
INSERT INTO `employee` VALUES (10, '员工J', '仓库管理员', '13900000010', 'employeeJ@example.com');
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for employee_log
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `employee_log`;
|
|
|
CREATE TABLE `employee_log` (
|
|
|
`LogID` int NOT NULL AUTO_INCREMENT,
|
|
|
`EmployeeID` int NULL DEFAULT NULL,
|
|
|
`OldEmployeeName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`NewEmployeeName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`OldDepartment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`NewDepartment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`ChangeDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
PRIMARY KEY (`LogID`) USING BTREE
|
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of employee_log
|
|
|
-- ----------------------------
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for goods
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `goods`;
|
|
|
CREATE TABLE `goods` (
|
|
|
`GoodsID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`GoodsName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`GoodsType` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`ProductionDate` date NULL DEFAULT NULL,
|
|
|
`SafetyStock` int NOT NULL,
|
|
|
`PurchaseUnitPrice` decimal(8, 2) NOT NULL,
|
|
|
`SupplierID` int NULL DEFAULT NULL,
|
|
|
PRIMARY KEY (`GoodsID`) USING BTREE,
|
|
|
INDEX `FK_Goods_Supplier`(`SupplierID` ASC) USING BTREE,
|
|
|
INDEX `idx_goods_name`(`GoodsName` ASC) USING BTREE,
|
|
|
INDEX `idx_goods_type`(`GoodsType` ASC) USING BTREE,
|
|
|
INDEX `idx_production_date`(`ProductionDate` ASC) USING BTREE,
|
|
|
UNIQUE INDEX `idx_goods_goodsid`(`GoodsID` ASC) USING BTREE,
|
|
|
INDEX `idx_goods_GoodsType`(`GoodsType` ASC) USING BTREE,
|
|
|
INDEX `idx_goods_PurchaseUnitPrice`(`PurchaseUnitPrice` ASC) USING BTREE,
|
|
|
INDEX `idx_goods_GoodsType_PurchaseUnitPrice`(`GoodsType` ASC, `PurchaseUnitPrice` ASC) USING BTREE,
|
|
|
CONSTRAINT `FK_Goods_Supplier` FOREIGN KEY (`SupplierID`) REFERENCES `supplier` (`SupplierID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `goods_chk_1` CHECK (`SafetyStock` >= 0),
|
|
|
CONSTRAINT `goods_chk_2` CHECK (`PurchaseUnitPrice` >= 0)
|
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of goods
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `goods` VALUES ('G001', '电脑显示器', '电子产品', '2023-01-01', 10, 600.00, 1);
|
|
|
INSERT INTO `goods` VALUES ('G002', '运动鞋', '服装', '2023-02-01', 20, 80.00, 2);
|
|
|
INSERT INTO `goods` VALUES ('G003', '苹果手机', '电子产品', '2023-03-01', 15, 800.00, 3);
|
|
|
INSERT INTO `goods` VALUES ('G004', '牛奶', '食品', '2023-04-01', 30, 5.00, 4);
|
|
|
INSERT INTO `goods` VALUES ('G005', '衬衫', '服装', '2023-05-01', 18, 50.00, 5);
|
|
|
INSERT INTO `goods` VALUES ('G006', '打印机', '电子产品', '2023-06-01', 8, 300.00, 6);
|
|
|
INSERT INTO `goods` VALUES ('G007', '面包', '食品', '2023-07-01', 25, 3.00, 7);
|
|
|
INSERT INTO `goods` VALUES ('G008', '牛仔裤', '服装', '2023-08-01', 22, 60.00, 8);
|
|
|
INSERT INTO `goods` VALUES ('G009', '平板电脑', '电子产品', '2023-09-01', 12, 600.00, 9);
|
|
|
INSERT INTO `goods` VALUES ('G010', '果汁', '食品', '2023-10-01', 35, 4.00, 10);
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for inboundrecord
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `inboundrecord`;
|
|
|
CREATE TABLE `inboundrecord` (
|
|
|
`InboundRecordID` int NOT NULL AUTO_INCREMENT,
|
|
|
`GoodsID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`WarehouseID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`InboundQuantity` int NOT NULL,
|
|
|
`InboundDate` date NOT NULL,
|
|
|
`EmployeeID` int NOT NULL,
|
|
|
PRIMARY KEY (`InboundRecordID`) USING BTREE,
|
|
|
INDEX `idx_inbound_record_goods_id`(`GoodsID` ASC) USING BTREE,
|
|
|
INDEX `idx_inbound_record_warehouse_id`(`WarehouseID` ASC) USING BTREE,
|
|
|
INDEX `idx_inbound_record_employee_id`(`EmployeeID` ASC) USING BTREE,
|
|
|
CONSTRAINT `FK_InboundRecord_Employee` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `FK_InboundRecord_Goods` FOREIGN KEY (`GoodsID`) REFERENCES `goods` (`GoodsID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `FK_InboundRecord_Warehouse` FOREIGN KEY (`WarehouseID`) REFERENCES `warehouse` (`WarehouseID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `inboundrecord_chk_1` CHECK (`InboundQuantity` > 0)
|
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of inboundrecord
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `inboundrecord` VALUES (1, 'G001', 'WH01', 10, '2024-01-01', 1);
|
|
|
INSERT INTO `inboundrecord` VALUES (2, 'G002', 'WH02', 20, '2024-01-02', 2);
|
|
|
INSERT INTO `inboundrecord` VALUES (3, 'G003', 'WH03', 15, '2024-01-03', 3);
|
|
|
INSERT INTO `inboundrecord` VALUES (4, 'G004', 'WH04', 30, '2024-01-04', 4);
|
|
|
INSERT INTO `inboundrecord` VALUES (5, 'G005', 'WH05', 18, '2024-01-05', 5);
|
|
|
INSERT INTO `inboundrecord` VALUES (6, 'G006', 'WH06', 8, '2024-01-06', 6);
|
|
|
INSERT INTO `inboundrecord` VALUES (7, 'G007', 'WH07', 25, '2024-01-07', 7);
|
|
|
INSERT INTO `inboundrecord` VALUES (8, 'G008', 'WH08', 22, '2024-01-08', 8);
|
|
|
INSERT INTO `inboundrecord` VALUES (9, 'G009', 'WH09', 12, '2024-01-09', 9);
|
|
|
INSERT INTO `inboundrecord` VALUES (10, 'G010', 'WH10', 35, '2024-01-10', 10);
|
|
|
INSERT INTO `inboundrecord` VALUES (11, 'G001', 'WH01', 5, '2024-01-11', 1);
|
|
|
INSERT INTO `inboundrecord` VALUES (12, 'G002', 'WH02', 10, '2024-01-12', 2);
|
|
|
INSERT INTO `inboundrecord` VALUES (13, 'G003', 'WH03', 8, '2024-01-13', 3);
|
|
|
INSERT INTO `inboundrecord` VALUES (14, 'G004', 'WH04', 15, '2024-01-14', 4);
|
|
|
INSERT INTO `inboundrecord` VALUES (15, 'G005', 'WH05', 10, '2024-01-15', 5);
|
|
|
INSERT INTO `inboundrecord` VALUES (16, 'G006', 'WH06', 5, '2024-01-16', 6);
|
|
|
INSERT INTO `inboundrecord` VALUES (17, 'G007', 'WH07', 15, '2024-01-17', 7);
|
|
|
INSERT INTO `inboundrecord` VALUES (18, 'G008', 'WH08', 10, '2024-01-18', 8);
|
|
|
INSERT INTO `inboundrecord` VALUES (19, 'G009', 'WH09', 8, '2024-01-19', 9);
|
|
|
INSERT INTO `inboundrecord` VALUES (20, 'G010', 'WH10', 20, '2024-01-20', 10);
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for order
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `order`;
|
|
|
CREATE TABLE `order` (
|
|
|
`OrderID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`OrderDate` datetime NOT NULL,
|
|
|
`CustomerID` int NOT NULL,
|
|
|
`OrderStatus` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`customer_id` int NULL DEFAULT NULL,
|
|
|
PRIMARY KEY (`OrderID`) USING BTREE,
|
|
|
INDEX `idx_order_date`(`OrderDate` ASC) USING BTREE,
|
|
|
INDEX `idx_customer_id`(`CustomerID` ASC) USING BTREE,
|
|
|
UNIQUE INDEX `idx_order_orderid`(`OrderID` ASC) USING BTREE,
|
|
|
INDEX `idx_order_customerid`(`CustomerID` ASC) USING BTREE,
|
|
|
INDEX `idx_order_orderdate`(`OrderDate` ASC) USING BTREE,
|
|
|
INDEX `idx_order_status_date`(`OrderStatus` ASC, `OrderDate` ASC) USING BTREE,
|
|
|
CONSTRAINT `FK_Order_Customer` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`) ON DELETE RESTRICT ON UPDATE RESTRICT
|
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of order
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `order` VALUES ('1', '2024-01-01 00:00:00', 1, 'Completed', 101);
|
|
|
INSERT INTO `order` VALUES ('10', '2024-01-10 00:00:00', 5, 'Delivered', 105);
|
|
|
INSERT INTO `order` VALUES ('2', '2024-01-02 00:00:00', 2, 'Pending', 102);
|
|
|
INSERT INTO `order` VALUES ('3', '2024-01-03 00:00:00', 3, 'Processing', 103);
|
|
|
INSERT INTO `order` VALUES ('4', '2024-01-04 00:00:00', 4, 'Shipped', 104);
|
|
|
INSERT INTO `order` VALUES ('5', '2024-01-05 00:00:00', 5, 'Cancelled', 105);
|
|
|
INSERT INTO `order` VALUES ('6', '2024-01-06 00:00:00', 1, 'Completed', 101);
|
|
|
INSERT INTO `order` VALUES ('7', '2024-01-07 00:00:00', 2, 'Pending', 102);
|
|
|
INSERT INTO `order` VALUES ('8', '2024-01-08 00:00:00', 3, 'Processing', 103);
|
|
|
INSERT INTO `order` VALUES ('9', '2024-01-09 00:00:00', 4, 'Shipped', 104);
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for order_log
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `order_log`;
|
|
|
CREATE TABLE `order_log` (
|
|
|
`LogID` int NOT NULL AUTO_INCREMENT,
|
|
|
`OrderID` int NULL DEFAULT NULL,
|
|
|
`OldStatus` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`NewStatus` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`ChangeDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
PRIMARY KEY (`LogID`) USING BTREE
|
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of order_log
|
|
|
-- ----------------------------
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for orderdetail
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `orderdetail`;
|
|
|
CREATE TABLE `orderdetail` (
|
|
|
`OrderDetailID` int NOT NULL AUTO_INCREMENT,
|
|
|
`OrderID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`GoodsID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`GoodsName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`GoodsSpecification` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`OrderQuantity` int NOT NULL,
|
|
|
`UnitPrice` decimal(10, 2) NOT NULL,
|
|
|
`TotalPrice` decimal(10, 2) NOT NULL,
|
|
|
PRIMARY KEY (`OrderDetailID`) USING BTREE,
|
|
|
INDEX `idx_order_detail_order_id`(`OrderID` ASC) USING BTREE,
|
|
|
INDEX `idx_order_detail_goods_id`(`GoodsID` ASC) USING BTREE,
|
|
|
CONSTRAINT `FK_OrderDetail_Goods` FOREIGN KEY (`GoodsID`) REFERENCES `goods` (`GoodsID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `FK_OrderDetail_Order` FOREIGN KEY (`OrderID`) REFERENCES `order` (`OrderID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `orderdetail_chk_1` CHECK (`OrderQuantity` >= 0),
|
|
|
CONSTRAINT `orderdetail_chk_2` CHECK (`UnitPrice` >= 0)
|
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 111 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of orderdetail
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `orderdetail` VALUES (1, '1', 'G001', '电脑显示器', '24 英寸高清显示器', 2, 500.00, 1000.00);
|
|
|
INSERT INTO `orderdetail` VALUES (2, '1', 'G003', '苹果手机', 'iPhone 13 128GB', 1, 800.00, 800.00);
|
|
|
INSERT INTO `orderdetail` VALUES (3, '2', 'G002', '运动鞋', '男款跑步鞋，尺码 42', 3, 80.00, 240.00);
|
|
|
INSERT INTO `orderdetail` VALUES (4, '2', 'G005', '衬衫', '白色纯棉衬衫，尺码 M', 2, 50.00, 100.00);
|
|
|
INSERT INTO `orderdetail` VALUES (5, '3', 'G004', '牛奶', '1L 装纯牛奶', 5, 5.00, 25.00);
|
|
|
INSERT INTO `orderdetail` VALUES (6, '3', 'G006', '打印机', '彩色喷墨打印机', 1, 300.00, 300.00);
|
|
|
INSERT INTO `orderdetail` VALUES (7, '4', 'G007', '面包', '全麦面包，500g', 4, 3.00, 12.00);
|
|
|
INSERT INTO `orderdetail` VALUES (8, '4', 'G008', '牛仔裤', '蓝色牛仔裤，尺码 32', 1, 60.00, 60.00);
|
|
|
INSERT INTO `orderdetail` VALUES (9, '5', 'G009', '平板电脑', 'iPad Air 10.5 英寸', 2, 600.00, 1200.00);
|
|
|
INSERT INTO `orderdetail` VALUES (10, '5', 'G010', '果汁', '橙汁，500ml', 6, 4.00, 24.00);
|
|
|
INSERT INTO `orderdetail` VALUES (101, '1', 'G001', '27寸高清电脑显示器', '分辨率：2560x1440，刷新率：144Hz', 3, 450.00, 1350.00);
|
|
|
INSERT INTO `orderdetail` VALUES (102, '1', 'G002', '专业跑鞋', '适合长跑，轻便透气，尺码：43', 2, 120.00, 240.00);
|
|
|
INSERT INTO `orderdetail` VALUES (103, '2', 'G003', '智能手机', '存储：256GB，摄像头：4800万像素', 1, 750.00, 750.00);
|
|
|
INSERT INTO `orderdetail` VALUES (104, '2', 'G004', '纯牛奶', '有机，2L装', 4, 8.00, 32.00);
|
|
|
INSERT INTO `orderdetail` VALUES (105, '3', 'G005', '男士衬衫', '纯棉材质，长袖，尺码：L', 5, 60.00, 300.00);
|
|
|
INSERT INTO `orderdetail` VALUES (106, '3', 'G006', '彩色激光打印机', '打印速度：20页/分钟', 1, 400.00, 400.00);
|
|
|
INSERT INTO `orderdetail` VALUES (107, '4', 'G007', '全麦面包', '无麸质，700g装', 6, 5.00, 30.00);
|
|
|
INSERT INTO `orderdetail` VALUES (108, '4', 'G008', '女士牛仔裤', '修身版型，蓝色，尺码：28', 3, 80.00, 240.00);
|
|
|
INSERT INTO `orderdetail` VALUES (109, '5', 'G009', '平板电脑', '10.1寸屏幕，64GB存储', 2, 550.00, 1100.00);
|
|
|
INSERT INTO `orderdetail` VALUES (110, '5', 'G010', '苹果汁', '鲜榨，1L装', 7, 6.00, 42.00);
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for outboundrecord
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `outboundrecord`;
|
|
|
CREATE TABLE `outboundrecord` (
|
|
|
`OutboundRecordID` int NOT NULL AUTO_INCREMENT,
|
|
|
`GoodsID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`WarehouseID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`OutboundQuantity` int NOT NULL,
|
|
|
`OutboundDate` date NOT NULL,
|
|
|
`OrderID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
|
`EmployeeID` int NOT NULL,
|
|
|
PRIMARY KEY (`OutboundRecordID`) USING BTREE,
|
|
|
INDEX `idx_outbound_record_goods_id`(`GoodsID` ASC) USING BTREE,
|
|
|
INDEX `idx_outbound_record_warehouse_id`(`WarehouseID` ASC) USING BTREE,
|
|
|
INDEX `idx_outbound_record_order_id`(`OrderID` ASC) USING BTREE,
|
|
|
INDEX `idx_outbound_record_employee_id`(`EmployeeID` ASC) USING BTREE,
|
|
|
UNIQUE INDEX `idx_outboundrecord_recordid`(`OutboundRecordID` ASC) USING BTREE,
|
|
|
INDEX `idx_outboundrecord_warehouseid`(`WarehouseID` ASC) USING BTREE,
|
|
|
INDEX `idx_outboundrecord_orderid`(`OrderID` ASC) USING BTREE,
|
|
|
INDEX `idx_outboundrecord_warehouse_date`(`WarehouseID` ASC, `OutboundDate` ASC) USING BTREE,
|
|
|
CONSTRAINT `FK_OutboundRecord_Employee` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `FK_OutboundRecord_Goods` FOREIGN KEY (`GoodsID`) REFERENCES `goods` (`GoodsID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `FK_OutboundRecord_Order` FOREIGN KEY (`OrderID`) REFERENCES `order` (`OrderID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `FK_OutboundRecord_Warehouse` FOREIGN KEY (`WarehouseID`) REFERENCES `warehouse` (`WarehouseID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `outboundrecord_chk_1` CHECK (`OutboundQuantity` > 0)
|
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 141 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of outboundrecord
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `outboundrecord` VALUES (1, 'G001', 'WH01', 20, '2024-01-01', '1', 1);
|
|
|
INSERT INTO `outboundrecord` VALUES (2, 'G002', 'WH02', 10, '2024-01-02', '2', 2);
|
|
|
INSERT INTO `outboundrecord` VALUES (3, 'G003', 'WH03', 8, '2024-01-03', '3', 3);
|
|
|
INSERT INTO `outboundrecord` VALUES (4, 'G004', 'WH04', 3, '2024-01-04', '4', 4);
|
|
|
INSERT INTO `outboundrecord` VALUES (5, 'G005', 'WH05', 12, '2024-01-05', '5', 5);
|
|
|
INSERT INTO `outboundrecord` VALUES (6, 'G006', 'WH06', 7, '2024-01-06', '6', 6);
|
|
|
INSERT INTO `outboundrecord` VALUES (7, 'G007', 'WH07', 9, '2024-01-07', '7', 7);
|
|
|
INSERT INTO `outboundrecord` VALUES (8, 'G008', 'WH08', 4, '2024-01-08', '8', 8);
|
|
|
INSERT INTO `outboundrecord` VALUES (9, 'G009', 'WH09', 6, '2024-01-09', '9', 9);
|
|
|
INSERT INTO `outboundrecord` VALUES (10, 'G010', 'WH10', 15, '2024-01-10', '10', 10);
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for supplier
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `supplier`;
|
|
|
CREATE TABLE `supplier` (
|
|
|
`SupplierID` int NOT NULL AUTO_INCREMENT,
|
|
|
`SupplyPrice` decimal(10, 2) NOT NULL,
|
|
|
`SupplierName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
PRIMARY KEY (`SupplierID`) USING BTREE,
|
|
|
INDEX `idx_supplier_name`(`SupplierName` ASC) USING BTREE,
|
|
|
CONSTRAINT `supplier_chk_1` CHECK (`SupplyPrice` >= 0)
|
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of supplier
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `supplier` VALUES (1, 10.50, '供应商A');
|
|
|
INSERT INTO `supplier` VALUES (2, 12.30, '供应商B');
|
|
|
INSERT INTO `supplier` VALUES (3, 9.80, '供应商C');
|
|
|
INSERT INTO `supplier` VALUES (4, 15.20, '供应商D');
|
|
|
INSERT INTO `supplier` VALUES (5, 11.00, '供应商E');
|
|
|
INSERT INTO `supplier` VALUES (6, 13.50, '供应商F');
|
|
|
INSERT INTO `supplier` VALUES (7, 14.10, '供应商G');
|
|
|
INSERT INTO `supplier` VALUES (8, 8.90, '供应商H');
|
|
|
INSERT INTO `supplier` VALUES (9, 16.00, '供应商I');
|
|
|
INSERT INTO `supplier` VALUES (10, 12.80, '供应商J');
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for warehouse
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `warehouse`;
|
|
|
CREATE TABLE `warehouse` (
|
|
|
`WarehouseID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`WarehouseName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`WarehouseAddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`WarehouseArea` int NOT NULL,
|
|
|
PRIMARY KEY (`WarehouseID`) USING BTREE,
|
|
|
INDEX `idx_warehouse_name`(`WarehouseName` ASC) USING BTREE,
|
|
|
INDEX `idx_warehouse_address`(`WarehouseAddress` ASC) USING BTREE,
|
|
|
UNIQUE INDEX `idx_warehouse_warehouseid`(`WarehouseID` ASC) USING BTREE,
|
|
|
CONSTRAINT `warehouse_chk_1` CHECK (`WarehouseArea` > 0)
|
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of warehouse
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `warehouse` VALUES ('WH01', '北京仓库', '北京市朝阳区XX路XX号', 1000);
|
|
|
INSERT INTO `warehouse` VALUES ('WH02', '上海仓库', '上海市浦东新区XX路XX号', 1200);
|
|
|
INSERT INTO `warehouse` VALUES ('WH03', '广州仓库', '广州市天河区XX路XX号', 800);
|
|
|
INSERT INTO `warehouse` VALUES ('WH04', '深圳仓库', '深圳市南山区XX路XX号', 900);
|
|
|
INSERT INTO `warehouse` VALUES ('WH05', '成都仓库', '成都市高新区XX路XX号', 700);
|
|
|
INSERT INTO `warehouse` VALUES ('WH06', '杭州仓库', '杭州市西湖区XX路XX号', 600);
|
|
|
INSERT INTO `warehouse` VALUES ('WH07', '武汉仓库', '武汉市江汉区XX路XX号', 500);
|
|
|
INSERT INTO `warehouse` VALUES ('WH08', '南京仓库', '南京市鼓楼区XX路XX号', 400);
|
|
|
INSERT INTO `warehouse` VALUES ('WH09', '西安仓库', '西安市雁塔区XX路XX号', 300);
|
|
|
INSERT INTO `warehouse` VALUES ('WH10', '重庆仓库', '重庆市渝中区XX路XX号', 550);
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Table structure for warehousestoragearea
|
|
|
-- ----------------------------
|
|
|
DROP TABLE IF EXISTS `warehousestoragearea`;
|
|
|
CREATE TABLE `warehousestoragearea` (
|
|
|
`AreaID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`WarehouseID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`AreaType` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
|
`AreaCapacity` int NOT NULL,
|
|
|
PRIMARY KEY (`AreaID`) USING BTREE,
|
|
|
INDEX `FK_Area_Warehouse`(`WarehouseID` ASC) USING BTREE,
|
|
|
INDEX `idx_area_type`(`AreaType` ASC) USING BTREE,
|
|
|
CONSTRAINT `FK_Area_Warehouse` FOREIGN KEY (`WarehouseID`) REFERENCES `warehouse` (`WarehouseID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
|
CONSTRAINT `warehousestoragearea_chk_1` CHECK (`AreaCapacity` >= 0)
|
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Records of warehousestoragearea
|
|
|
-- ----------------------------
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A01', 'WH01', '货架区', 500);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A02', 'WH01', '堆垛区', 300);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A03', 'WH02', '货架区', 400);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A04', 'WH02', '堆垛区', 250);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A05', 'WH03', '货架区', 350);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A06', 'WH03', '堆垛区', 200);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A07', 'WH04', '货架区', 300);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A08', 'WH04', '堆垛区', 180);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A09', 'WH05', '货架区', 250);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A10', 'WH05', '堆垛区', 150);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A11', 'WH06', '货架区', 200);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A12', 'WH06', '堆垛区', 120);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A13', 'WH07', '货架区', 180);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A14', 'WH07', '堆垛区', 100);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A15', 'WH08', '货架区', 150);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A16', 'WH08', '堆垛区', 80);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A17', 'WH09', '货架区', 120);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A18', 'WH09', '堆垛区', 60);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A19', 'WH10', '货架区', 180);
|
|
|
INSERT INTO `warehousestoragearea` VALUES ('A20', 'WH10', '堆垛区', 100);
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- View structure for employee_order_info
|
|
|
-- ----------------------------
|
|
|
DROP VIEW IF EXISTS `employee_order_info`;
|
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `employee_order_info` AS select `e`.`EmployeeName` AS `EmployeeName`,`e`.`EmployeePosition` AS `EmployeePosition`,`o`.`OrderID` AS `OrderID`,`o`.`OrderDate` AS `OrderDate`,`o`.`OrderStatus` AS `OrderStatus` from (`employee` `e` join `order` `o` on((`e`.`EmployeeID` = `o`.`OrderID`)));
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- View structure for goods_with_outbound
|
|
|
-- ----------------------------
|
|
|
DROP VIEW IF EXISTS `goods_with_outbound`;
|
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `goods_with_outbound` AS select `g`.`GoodsID` AS `GoodsID`,`g`.`GoodsName` AS `GoodsName`,`g`.`GoodsType` AS `GoodsType`,`g`.`ProductionDate` AS `ProductionDate`,`g`.`SafetyStock` AS `SafetyStock`,`g`.`PurchaseUnitPrice` AS `PurchaseUnitPrice`,`g`.`SupplierID` AS `SupplierID`,`orr`.`OutboundQuantity` AS `OutboundQuantity`,`orr`.`OutboundDate` AS `OutboundDate` from (`goods` `g` join `outboundrecord` `orr` on((`g`.`GoodsID` = `orr`.`GoodsID`)));
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- View structure for high_value_orders
|
|
|
-- ----------------------------
|
|
|
DROP VIEW IF EXISTS `high_value_orders`;
|
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `high_value_orders` AS select `o`.`OrderID` AS `OrderID`,`o`.`OrderDate` AS `OrderDate`,`o`.`CustomerID` AS `CustomerID`,sum(`od`.`TotalPrice`) AS `OrderTotalPrice` from (`order` `o` join `orderdetail` `od` on((`o`.`OrderID` = `od`.`OrderID`))) group by `o`.`OrderID`,`o`.`OrderDate`,`o`.`CustomerID` having (sum(`od`.`TotalPrice`) > 1000);
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- View structure for incomplete_orders
|
|
|
-- ----------------------------
|
|
|
DROP VIEW IF EXISTS `incomplete_orders`;
|
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `incomplete_orders` AS select `o`.`OrderID` AS `OrderID`,`o`.`OrderDate` AS `OrderDate`,`o`.`CustomerID` AS `CustomerID`,`o`.`OrderStatus` AS `OrderStatus`,`od`.`GoodsID` AS `GoodsID`,`od`.`GoodsName` AS `GoodsName` from (`order` `o` join `orderdetail` `od` on((`o`.`OrderID` = `od`.`OrderID`))) where (`o`.`OrderStatus` <> 'Completed');
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- View structure for order_with_customer
|
|
|
-- ----------------------------
|
|
|
DROP VIEW IF EXISTS `order_with_customer`;
|
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `order_with_customer` AS select `o`.`OrderID` AS `OrderID`,`o`.`OrderDate` AS `OrderDate`,`o`.`CustomerID` AS `CustomerID`,`o`.`OrderStatus` AS `OrderStatus`,`o`.`customer_id` AS `customer_id`,`c`.`CustomerName` AS `CustomerName`,`c`.`CustomerAddress` AS `CustomerAddress` from (`order` `o` join `customer` `c` on((`o`.`CustomerID` = `c`.`CustomerID`)));
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- View structure for warehouse_outbound_total
|
|
|
-- ----------------------------
|
|
|
DROP VIEW IF EXISTS `warehouse_outbound_total`;
|
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `warehouse_outbound_total` AS select `w`.`WarehouseID` AS `WarehouseID`,`w`.`WarehouseName` AS `WarehouseName`,sum(`orr`.`OutboundQuantity`) AS `TotalOutboundQuantity` from (`warehouse` `w` join `outboundrecord` `orr` on((`w`.`WarehouseID` = `orr`.`WarehouseID`))) group by `w`.`WarehouseID`,`w`.`WarehouseName`;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Procedure structure for CalculateWarehouseOutboundTotal
|
|
|
-- ----------------------------
|
|
|
DROP PROCEDURE IF EXISTS `CalculateWarehouseOutboundTotal`;
|
|
|
delimiter ;;
|
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculateWarehouseOutboundTotal`(
|
|
|
IN p_WarehouseID VARCHAR(255),
|
|
|
OUT p_TotalOutbound INT
|
|
|
)
|
|
|
BEGIN
|
|
|
SELECT SUM(OutboundQuantity) INTO p_TotalOutbound
|
|
|
FROM outboundrecord
|
|
|
WHERE WarehouseID = p_WarehouseID;
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Procedure structure for DeleteOrderAndOutbound
|
|
|
-- ----------------------------
|
|
|
DROP PROCEDURE IF EXISTS `DeleteOrderAndOutbound`;
|
|
|
delimiter ;;
|
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteOrderAndOutbound`(
|
|
|
IN p_OrderID INT
|
|
|
)
|
|
|
BEGIN
|
|
|
DELETE FROM outboundrecord WHERE OrderID = p_OrderID;
|
|
|
DELETE FROM `order` WHERE OrderID = p_OrderID;
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Procedure structure for GetOutboundRecordsByDateRange
|
|
|
-- ----------------------------
|
|
|
DROP PROCEDURE IF EXISTS `GetOutboundRecordsByDateRange`;
|
|
|
delimiter ;;
|
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetOutboundRecordsByDateRange`(
|
|
|
IN p_StartDate DATE,
|
|
|
IN p_EndDate DATE
|
|
|
)
|
|
|
BEGIN
|
|
|
SELECT *
|
|
|
FROM outboundrecord
|
|
|
WHERE OutboundDate BETWEEN p_StartDate AND p_EndDate;
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Procedure structure for InsertNewOrder
|
|
|
-- ----------------------------
|
|
|
DROP PROCEDURE IF EXISTS `InsertNewOrder`;
|
|
|
delimiter ;;
|
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertNewOrder`(
|
|
|
IN p_OrderDate DATE,
|
|
|
IN p_CustomerID INT,
|
|
|
IN p_OrderStatus VARCHAR(50)
|
|
|
)
|
|
|
BEGIN
|
|
|
INSERT INTO `order` (OrderDate, CustomerID, OrderStatus)
|
|
|
VALUES (p_OrderDate, p_CustomerID, p_OrderStatus);
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Procedure structure for InsertOutboundRecordAndUpdateStock
|
|
|
-- ----------------------------
|
|
|
DROP PROCEDURE IF EXISTS `InsertOutboundRecordAndUpdateStock`;
|
|
|
delimiter ;;
|
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOutboundRecordAndUpdateStock`(
|
|
|
IN p_GoodsID VARCHAR(255),
|
|
|
IN p_WarehouseID VARCHAR(255),
|
|
|
IN p_OutboundQuantity INT,
|
|
|
IN p_OutboundDate DATE,
|
|
|
IN p_OrderID INT,
|
|
|
IN p_EmployeeID INT
|
|
|
)
|
|
|
BEGIN
|
|
|
DECLARE current_stock INT;
|
|
|
SELECT StockQuantity INTO current_stock FROM goods WHERE GoodsID = p_GoodsID;
|
|
|
IF current_stock >= p_OutboundQuantity THEN
|
|
|
INSERT INTO outboundrecord (GoodsID, WarehouseID, OutboundQuantity, OutboundDate, OrderID, EmployeeID)
|
|
|
VALUES (p_GoodsID, p_WarehouseID, p_OutboundQuantity, p_OutboundDate, p_OrderID, p_EmployeeID);
|
|
|
UPDATE goods
|
|
|
SET StockQuantity = StockQuantity - p_OutboundQuantity
|
|
|
WHERE GoodsID = p_GoodsID;
|
|
|
ELSE
|
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock for the outbound operation.';
|
|
|
END IF;
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Procedure structure for UpdateOrderStatus
|
|
|
-- ----------------------------
|
|
|
DROP PROCEDURE IF EXISTS `UpdateOrderStatus`;
|
|
|
delimiter ;;
|
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrderStatus`(
|
|
|
IN p_OrderID INT,
|
|
|
IN p_NewStatus VARCHAR(50)
|
|
|
)
|
|
|
BEGIN
|
|
|
UPDATE `order`
|
|
|
SET OrderStatus = p_NewStatus
|
|
|
WHERE OrderID = p_OrderID;
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Triggers structure for table employee
|
|
|
-- ----------------------------
|
|
|
DROP TRIGGER IF EXISTS `tr_before_update_employee`;
|
|
|
delimiter ;;
|
|
|
CREATE TRIGGER `tr_before_update_employee` BEFORE UPDATE ON `employee` FOR EACH ROW BEGIN
|
|
|
INSERT INTO employee_log (EmployeeID, OldEmployeeName, NewEmployeeName, OldEmployeePosition, NewEmployeePosition)
|
|
|
VALUES (OLD.EmployeeID, OLD.EmployeeName, NEW.EmployeeName, OLD.EmployeePosition, NEW.EmployeePosition);
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Triggers structure for table goods
|
|
|
-- ----------------------------
|
|
|
DROP TRIGGER IF EXISTS `tr_before_delete_goods`;
|
|
|
delimiter ;;
|
|
|
CREATE TRIGGER `tr_before_delete_goods` BEFORE DELETE ON `goods` FOR EACH ROW BEGIN
|
|
|
INSERT INTO deleted_goods (DeletedGoodsID, GoodsName)
|
|
|
VALUES (OLD.GoodsID, OLD.GoodsName);
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Triggers structure for table order
|
|
|
-- ----------------------------
|
|
|
DROP TRIGGER IF EXISTS `tr_before_insert_order`;
|
|
|
delimiter ;;
|
|
|
CREATE TRIGGER `tr_before_insert_order` BEFORE INSERT ON `order` FOR EACH ROW BEGIN
|
|
|
SET NEW.OrderDate = CURDATE();
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Triggers structure for table order
|
|
|
-- ----------------------------
|
|
|
DROP TRIGGER IF EXISTS `tr_after_update_order_status`;
|
|
|
delimiter ;;
|
|
|
CREATE TRIGGER `tr_after_update_order_status` AFTER UPDATE ON `order` FOR EACH ROW BEGIN
|
|
|
IF OLD.OrderStatus!= NEW.OrderStatus THEN
|
|
|
INSERT INTO order_log (OrderID, OldStatus, NewStatus)
|
|
|
VALUES (OLD.OrderID, OLD.OrderStatus, NEW.OrderStatus);
|
|
|
END IF;
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Triggers structure for table outboundrecord
|
|
|
-- ----------------------------
|
|
|
DROP TRIGGER IF EXISTS `tr_after_insert_outboundrecord`;
|
|
|
delimiter ;;
|
|
|
CREATE TRIGGER `tr_after_insert_outboundrecord` AFTER INSERT ON `outboundrecord` FOR EACH ROW BEGIN
|
|
|
UPDATE goods
|
|
|
SET StockQuantity = StockQuantity - NEW.OutboundQuantity
|
|
|
WHERE GoodsID = NEW.GoodsID;
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
-- ----------------------------
|
|
|
-- Triggers structure for table outboundrecord
|
|
|
-- ----------------------------
|
|
|
DROP TRIGGER IF EXISTS `tr_before_delete_outboundrecord`;
|
|
|
delimiter ;;
|
|
|
CREATE TRIGGER `tr_before_delete_outboundrecord` BEFORE DELETE ON `outboundrecord` FOR EACH ROW BEGIN
|
|
|
UPDATE goods
|
|
|
SET StockQuantity = StockQuantity + OLD.OutboundQuantity
|
|
|
WHERE GoodsID = OLD.GoodsID;
|
|
|
END
|
|
|
;;
|
|
|
delimiter ;
|
|
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
