java-bbc/db/2024-03-25 库存修改.sql

注意，请把这几行注释掉执行sql，写这行的目的是进行必要的说明：
1. 本次更新会将库存从数据库中移除，并使用redis进行存储。
2. 这里的redis是持久化的，所以需要先部署好mall4j-aof-redis。这里的reids与mall4d-bbc中的redis是不同的两个，所以需要重新配置，并且是需要挂载持久化的。
3. 把库存放在redis进行持久化存储，是为了减少数据库压力，提高性能。所以这个redis是持久化的，不能随便删除挂载的文件，否则会导致数据丢失。

库存初始化步骤：
1.根据`mall4d-bbc`项目中提供的部署模板，部署持久化redis（mall4j-aof-redis）
2.执行以下sql, 重新配置各个服务的redis并重启服务
3.到xxl-job的控制台执行一次`初始化商品库存`的定时任务，执行后即可删除该定时任务



# 修改菜单

INSERT INTO `tz_shop_menu`(`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`) VALUES
                                               (742, 221, '', '', 0, '', 0, 0),
                                               (743, 742, 'stock/stowage/warehouse-manage/index', '', 1, '', 0, 0),
                                               (744, 742, 'stock/stowage/transfer-order/index', '', 1, '', 2, 0),
                                               (745, 742, 'stock/stowage/transfer-order-create/index', '', 1, '', 3, 1);


INSERT INTO `tz_shop_menu_lang`(`menu_id`, `lang`, `name`) VALUES
(742, 0, '仓库门店'),
(743, 0, '仓库管理'),
(744, 0, '调拨订单'),
(745, 0, '新建调拨订单');


UPDATE `tz_shop_menu` SET `order_num` = 1 WHERE `menu_id` = 222;

UPDATE `tz_shop_menu` SET `order_num` = 2 WHERE `menu_id` = 237;

UPDATE `tz_shop_menu` SET `order_num` = 3 WHERE `menu_id` = 225;

UPDATE `tz_shop_menu` SET `order_num` = 4 WHERE `menu_id` = 229;

UPDATE `tz_shop_menu` SET `order_num` = 5 WHERE `menu_id` = 259;

UPDATE `tz_shop_menu` SET `parent_id` = 742, `url` = 'stock/stowage/admin-station/index', `order_num` = 1 WHERE `menu_id` = 38;


# 添加库存管理表
CREATE TABLE `tz_stock_point_sku` (
                                      `stock_point_sku_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '库存商品关联skuid',
                                      `stock_point_id` bigint NOT NULL COMMENT '库存点id',
                                      `stock_point_type` tinyint NOT NULL COMMENT '库存点类型 1仓库 2门店',
                                      `prod_id` bigint DEFAULT NULL COMMENT 'prod_id',
                                      `sku_id` bigint DEFAULT NULL COMMENT 'skuid',
                                      `status` tinyint DEFAULT '0' COMMENT '状态 1:enable, 0:disable, -1:deleted',
                                      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                      `type` tinyint DEFAULT '1' COMMENT '仓库类型（0默认仓库，1区域仓库）',
                                      `stock_mode` tinyint DEFAULT NULL COMMENT '库存模式 1共享总部库存 2独立销售库存',
                                      PRIMARY KEY (`stock_point_sku_id`),
                                      KEY `idx_stock_point_id` (`stock_point_id`) USING BTREE,
                                      KEY `idx_prod_id` (`prod_id`) USING BTREE,
                                      KEY `idx_sku_id` (`sku_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

# 新增仓库表
CREATE TABLE `tz_warehouse` (
                                `warehouse_id` bigint NOT NULL COMMENT '仓库id',
                                `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间',
                                `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                `shop_id` bigint DEFAULT NULL COMMENT '店铺id',
                                `warehouse_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '仓库名称',
                                `type` tinyint DEFAULT NULL COMMENT '仓库类型（0默认仓库，1区域仓库）',
                                `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '仓库地址',
                                `manage` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '管理人',
                                `phone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '管理电话',
                                `sys_type` tinyint DEFAULT NULL COMMENT '系统类型',
                                `province_id` bigint DEFAULT NULL COMMENT '省ID',
                                `province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
                                `city_id` bigint DEFAULT NULL COMMENT '城市ID',
                                `city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
                                `area_id` bigint DEFAULT NULL COMMENT '区ID',
                                `area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
                                PRIMARY KEY (`warehouse_id`),
                                KEY `shop_id` (`shop_id`) USING BTREE,
                                KEY `idx_province_id` (`province_id`) USING BTREE,
                                KEY `idx_city_id` (`city_id`) USING BTREE,
                                KEY `idx_area_id` (`area_id`) USING BTREE,
                                KEY `idx_warehouse_id` (`warehouse_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `tz_warehouse` (`warehouse_id`,`shop_id`, `warehouse_name`, `type`, `address`, `manage`, `phone`, `sys_type`, `province_id`, `province`, `city_id`, `city`, `area_id`, `area`)
VALUES
    (300000000000000,0,'默认仓库',0,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,NULL,NULL),
    (300000000001000,1,'默认仓库',0,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL);

# 为旧门店数据创建门店仓库
INSERT INTO `tz_warehouse` ( `warehouse_id`, `shop_id`, `sys_type`, `type`, `warehouse_name`, `phone`, `province_id`, `province`, `city_id`, `city`, `area_id`, `area`, `address` )
(
    SELECT
        station_id warehouse_id,
        station_id shop_id,
        '3' sys_type,
        '1' type,
        station_name warehouse_name,
        phone,
        province_id,
        province,
        city_id,
        city,
        area_id,
        area,
        addr address
    FROM
        `tz_station`);



CREATE TABLE `tz_warehouse_area` (
                                     `warehouse_area_id` bigint NOT NULL AUTO_INCREMENT,
                                     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间',
                                     `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                     `warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
                                     `area_id` bigint DEFAULT NULL COMMENT '城市id',
                                     PRIMARY KEY (`warehouse_area_id`) USING BTREE,
                                     KEY `idx_warehouse_id` (`warehouse_id`) USING BTREE,
                                     KEY `idx_city_id` (`area_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='仓库项和供货城市关联信息';




INSERT INTO `tz_leaf_alloc`(`biz_tag`, `max_id`, `step`, `update_time`, `description`) VALUES
    ('mall4j-stock-point', 1000, 1000, '2023-12-15 17:28:15', '库存点id'),
    ('mall4j-allot-order', 1000, 1000, '2023-12-15 17:28:15', '调拨订单id');



# 所有门店添加库存模式，并统一为共享总部库存
ALTER TABLE `tz_station`
    ADD COLUMN `stock_mode` tinyint DEFAULT NULL COMMENT '库存模式 1共享总部库存 2独立销售库存';

update tz_station set stock_mode = 1;




ALTER TABLE `tz_seckill`
    ADD COLUMN  `replenish` int DEFAULT NULL COMMENT '补充库存 0不补充 1补充';


CREATE TABLE `tz_seckill_stock_point_sku` (
                                              `seckill_stock_point_sku_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '库存商品关联skuid',
                                              `stock_point_id` bigint NOT NULL COMMENT '库存点id',
                                              `stock_point_type` tinyint NOT NULL COMMENT '库存点类型 1仓库 2门店',
                                              `sku_id` bigint DEFAULT NULL COMMENT 'sku_id',
                                              `seckill_sku_id` bigint DEFAULT NULL COMMENT '秒杀活动skuid',
                                              `status` tinyint DEFAULT '0' COMMENT '状态 1:enable, 0:disable, -1:deleted',
                                              `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                              `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                              `type` tinyint DEFAULT NULL COMMENT '仓库类型（0默认仓库，1区域仓库）',
                                              `seckill_origin_stocks` int DEFAULT NULL COMMENT '秒杀库存点原始库存',
                                              `stock_mode` tinyint DEFAULT NULL COMMENT '库存模式 1共享总部库存 2独立销售库存',
                                              PRIMARY KEY (`seckill_stock_point_sku_id`),
                                              KEY `idx_stock_point_id` (`stock_point_id`) USING BTREE,
                                              KEY `idx_sku_id` (`sku_id`) USING BTREE,
                                              KEY `idx_seckill_sku_id` (`seckill_sku_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

# 商品组合商品相关字段
ALTER TABLE `tz_prod`
    MODIFY `mold` tinyint NOT NULL COMMENT '商品类别 0.实物商品 1.虚拟商品 2.组合商品',
    ADD COLUMN `is_all_entity` INT DEFAULT NULL COMMENT '是否为全部实物商品的组合商品 1.是 0.否';

ALTER TABLE `tz_sku` ADD COLUMN `stock_warning_status` tinyint NOT NULL DEFAULT '1' COMMENT '库存预警状态 2:库存充足 1:库存预警';


CREATE TABLE `tz_sku_combo` (
                             `sku_id` bigint NOT NULL COMMENT '规格id',
                             `combo_sku_id` bigint NOT NULL COMMENT '组合的skuid',
                             `combo_prod_id` bigint NOT NULL COMMENT '组合的spuid',
                             `combo_count` int NOT NULL COMMENT '组合数量',
                             `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                             `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
                             PRIMARY KEY (`sku_id`,`combo_sku_id`),
                             KEY `idx_spu_id` (`combo_prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='规格组合商品关联表';

ALTER TABLE `tz_order`
    ADD COLUMN main_order_number BIGINT NOT NULL DEFAULT '0' COMMENT '主单号,在组合/套餐商品拆单时会存在' AFTER `is_refund`,
    ADD KEY `idx_main_order_number` (`main_order_number`);

ALTER TABLE `tz_order_item`
    ADD COLUMN `activity_id` bigint DEFAULT NULL COMMENT '活动id',
    ADD COLUMN `mold` tinyint NOT NULL COMMENT '商品类别 0.实物商品 1.虚拟商品 2.组合商品',
    ADD COLUMN `stock_point_id` bigint DEFAULT NULL COMMENT '库存点id',
    ADD COLUMN `activity_type` tinyint DEFAULT '0' COMMENT '活动类型 具体类型参考枚举类:OrderActivityType',
    ADD KEY `idx_activity_id` (`activity_id`);


# 新增调拨订单
CREATE TABLE `tz_allot_order` (
		`allot_order_id` BIGINT ( 0 ) NOT NULL COMMENT '调拨订单id',
		`out_warehouse_id` BIGINT ( 0 ) NULL DEFAULT NULL COMMENT '调出点仓库id',
		`out_stock_point_type` INT ( 0 ) NULL DEFAULT NULL COMMENT '调出点库存点类型(1:仓库, 2:门店)',
		`in_warehouse_id` BIGINT ( 0 ) NULL DEFAULT NULL COMMENT '调入点仓库id',
		`in_stock_point_type` INT ( 0 ) NULL DEFAULT NULL COMMENT '调入点库存点类型(1:仓库, 2:门店)',
		`dvy_company_id` BIGINT ( 0 ) NULL DEFAULT NULL COMMENT '物流公司id',
		`dvy_type` INT ( 0 ) NULL DEFAULT NULL COMMENT '物流方式(1:快递, 3:无需快递)',
		`dvy_order_number` VARCHAR ( 20 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '物流单号',
		`status` INT ( 0 ) NULL DEFAULT NULL COMMENT '状态(0:作废, 1:待入库, 2:部分入库, 3:已完成)',
		`total_allot_count` INT ( 0 ) NULL DEFAULT NULL COMMENT '总调拨数量',
		`remark` VARCHAR ( 255 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
		`shop_id` BIGINT ( 0 ) NULL COMMENT '店铺id',
		`sys_type` INT ( 1 ) NULL COMMENT '系统类型',
		`create_time` datetime ( 0 ) NULL DEFAULT NULL COMMENT '创建时间',
		`update_time` datetime ( 0 ) NULL DEFAULT NULL COMMENT '更新时间',
	    PRIMARY KEY ( `allot_order_id` ) USING BTREE,
	    KEY `idx_out_warehouse_id` (`out_warehouse_id`),
	    KEY `idx_in_warehouse_id` (`in_warehouse_id`),
	    KEY `idx_dvy_company_id` (`dvy_company_id`),
	    KEY `idx_shop_id` (`shop_id`)
);

# 新增调拨订单项
CREATE TABLE `tz_allot_order_item` (
        `allot_order_item_id` BIGINT NOT NULL COMMENT '调拨订单商品id',
        `allot_order_id` BIGINT NULL COMMENT '调拨订单id',
        `spu_id` BIGINT NULL COMMENT '商品spuId',
        `sku_id` BIGINT NULL COMMENT '商品skuId',
        `allot_count` INT ( 255 ) NULL COMMENT '调拨数量',
        `allot_in_count` INT ( 255 ) NULL COMMENT '调拨入库数量',
        `create_time` datetime NULL COMMENT '创建时间',
        `update_time` datetime NULL COMMENT '创建时间',
        PRIMARY KEY ( `allot_order_item_id` ),
        KEY `idx_allot_order_id` (`allot_order_id`),
        KEY `idx_spu` (`spu_id`, `sku_id`)
);

# 库存流水字段补充
ALTER TABLE `tz_stock_bill_log`
ADD COLUMN `stock_point_id` bigint(0) NULL COMMENT '库存点Id' AFTER `maker_mobile`,
ADD COLUMN `stock_point_type` tinyint(1) NULL COMMENT '库存点类型(1:仓库， 2:门店)' AFTER `stock_point_id`,
ADD KEY `idx_stock_point_id` (`stock_point_id`);

# 采购订单字段补充
ALTER TABLE `tz_purchase_order`
ADD COLUMN `warehouse_id` bigint(0) NULL COMMENT '仓库id' AFTER `employee_id`,
ADD COLUMN `stock_point_type` tinyint(1) NULL COMMENT '库存点类型(1:仓库, 2:门店)' AFTER `warehouse_id`,
ADD KEY `idx_warehouse_id` (`warehouse_id`);

# 实物盘点字段补充
ALTER TABLE `tz_take_stock`
ADD COLUMN `warehouse_id` bigint(0) NULL COMMENT '仓库id' AFTER `maker_mobile`,
ADD COLUMN `stock_point_type` tinyint(1) NULL COMMENT '库存点类型(1:仓库, 2:门店)' AFTER `warehouse_id`,
ADD KEY `idx_warehouse_id` (`warehouse_id`);

# 移除多余的菜单 1046和1047是无用的的路径分析子菜单
delete from tz_sys_menu_lang where menu_id in (1046,1047);
delete from tz_sys_menu where menu_id in (1046,1047);

INSERT INTO `tz_leaf_alloc` (`biz_tag`, `max_id`, `step`, `description`)
VALUES
    ("mall4j-purchases-order", 1, 100, "采购订单编号"),
    ("mall4j-ent-pay-order-no", 1, 100, "分销提现商家转账订单号"),
    ("mall4j-distribution-card-no", 1000000000000, 100, "分销员卡号"),
    ("mall4j-distribution-ticket", 1000000000000, 100, "分销商品二维码"),
    ("mall4j-seckill-order-path", 1000000000000, 100, "秒杀订单路径");
# 补全旧的赠品订单项数据
UPDATE `tz_order_item` SET activity_type = 5, activity_id = giveaway_order_item_id  WHERE giveaway_order_item_id IS NOT NULL AND giveaway_order_item_id > 0;


# 添加定时任务后只需要执行一次，将旧的商品数据转换
insert into `xxl_job`.`xxl_job_info`(`id`, `job_group`, `job_desc`, `add_time`, `update_time`, `author`, `alarm_email`,
                           `schedule_type`, `schedule_conf`, `misfire_strategy`, `executor_route_strategy`,
                           `executor_handler`, `executor_param`, `executor_block_strategy`, `executor_timeout`,
                           `executor_fail_retry_count`, `glue_type`, `glue_source`, `glue_remark`, `glue_updatetime`,
                           `child_jobid`, `trigger_status`, `trigger_last_time`, `trigger_next_time`)
values
(48,2, '初始化商品库存', '2024-03-06 10:07:30', '2024-03-06 10:07:30', 'admin', '', 'CRON',
    '0 0/30 * * * ?', 'DO_NOTHING', 'FIRST', 'intiStock', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN',
    '', 'GLUE代码初始化', '2023-12-25 10:07:30', '', 0, 0, 0),
(49,2, '退款订单库存处理', '2024-03-06 10:07:30', '2024-03-06 10:07:30', 'admin', '', 'CRON',
    '0 0/5 * * * ?', 'DO_NOTHING', 'FIRST', 'refundOrderStock', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN',
    '', 'GLUE代码初始化', '2023-12-25 10:07:30', '', 1, 0, 0),
(50,2, '恢复商品sku库存', '2024-03-06 10:07:30', '2024-03-06 10:07:30', 'admin', '', 'CRON',
    '0 0/30 * * * ?', 'DO_NOTHING', 'FIRST', 'restoreStock', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN',
    '', 'GLUE代码初始化', '2023-12-25 10:07:30', '', 0, 0, 0)
;

delete from `xxl_job`.`xxl_job_info` where executor_handler in ('hotStockPlacement', 'coldStockPlacement')