java-bbc/db/2025-04-22 店铺总销量统计.sql

USE `xxl_job`;

-- 新增sql后，需去xxl-job后台，手动执行一次下面的定时任务
INSERT INTO `xxl_job_info`(`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
    (2, '店铺销量初始化统计', '2025-04-07 10:04:38', '2025-04-07 10:04:38', 'admin', '', 'NONE', '', 'DO_NOTHING', 'FIRST', 'initShopSoldNum', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2025-04-07 10:04:38', '', 0, 0, 0);


# 商家端仓库门店菜单位置修改
USE yami_bbc;

UPDATE tz_shop_menu
SET url = 'shop/stowage/admin-station/index'
WHERE menu_id = 38;

update tz_shop_menu
SET parent_id = 2 , order_num = 6
WHERE menu_id = 742;

update tz_shop_menu
SET url = 'shop/stowage/warehouse-manage/index'
WHERE menu_id = 743;

update tz_shop_menu
SET parent_id = 222 , url = 'stock/manage/transfer-order/index' , order_num = 3
WHERE menu_id = 744;

UPDATE tz_shop_menu
SET
    parent_id = 998,
    url = 'stock/manage/transfer-order/index',
    order_num = 3
WHERE
    menu_id = 884;

update tz_shop_menu
SET parent_id = 222 , url = 'stock/manage/transfer-order-create/index'
WHERE menu_id = 745;

UPDATE tz_shop_menu
SET
    parent_id = 998,
    url = 'stock/manage/transfer-order-create/index'
WHERE
    menu_id = 883;

update tz_shop_menu
SET url = 'shop/stowage/admin-station/add-or-update'
WHERE menu_id = 746;

update tz_shop_menu_lang
SET name = '库存调拨'
WHERE menu_id = 744 and lang = 0;

update tz_shop_menu_lang
SET name = 'Inventory Transfer'
WHERE menu_id = 744 and lang = 1;

UPDATE tz_shop_menu_lang
SET name = '库存调拨'
WHERE menu_id = 884 and lang = 0;

UPDATE tz_shop_menu_lang
SET name = 'Inventory Transfer'
WHERE menu_id = 884 and lang = 1;

update tz_shop_menu_lang
SET name = '新建库存调拨'
WHERE menu_id = 745 and lang = 0;

update tz_shop_menu_lang
SET name = '新建库存调拨'
WHERE menu_id = 883 and lang = 0;

#新增上传图片按钮权限,parent_id为182是商家图片管理菜单1052是门店图片管理菜单

insert into `tz_shop_menu` (`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`, `router_name`, `sys_type`) values
('1421','182','','admin:file:save','2','','0','1',NULL,'1'),
('1432','1052','','admin:file:save','2','','0','1',NULL,'3');

insert into `tz_shop_menu_lang` (`menu_id`, `lang`, `name`) values
('1421','0','上传图片'),
('1421','1','Upload pictures'),
('1432','0','上传图片'),
('1432','1','Upload pictures');

insert into `tz_sys_menu` (`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`, `router_name`) values
('1202','743','','admin:file:save','2','','0','1',NULL);

insert into `tz_sys_menu_lang` (`menu_id`, `lang`, `name`) values
(1202,0,'上传图片'),
(1202,1,'Upload pictures');


# 以下为代客下单功能的增量sql
CREATE TABLE `tz_order_on_behalf`
(
    `behalf_id`                 bigint(20)      NOT NULL AUTO_INCREMENT COMMENT 'id',
    `behalf_code`               varchar(50)     DEFAULT NULL COMMENT '编码（用于生成分享链接）',
    `system_type`               int             DEFAULT NULL COMMENT '系统类型',
    `service_unit`              int             DEFAULT NULL COMMENT '服务单位（1-商家； 2-门店）',
    `shop_id`                   bigint(20)      DEFAULT NULL COMMENT '商家id',
    `station_id`                bigint(20)      DEFAULT NULL COMMENT '门店id',
    `is_change_price`           int             COMMENT '订单改价(false-未改价；true-改价)',
    `change_total_price`        decimal(15, 2)  COMMENT '用户支付金额',
    `change_freight_amount`     decimal(15, 2)  COMMENT '运费金额',
    `reduce_amount`             decimal(15, 2)  COMMENT '优惠金额',
    `employee_id`               bigint(20)      DEFAULT NULL  COMMENT '操作员工id',
    `is_pre_sell`               int             DEFAULT '0' COMMENT '是否选择预售商品（0-否；1-是）',
    `status`                    int             DEFAULT 1 COMMENT '状态（-1：已删除；0：已取消；1：正常；2：已下单）',
    `code_create_time`          datetime        DEFAULT NULL COMMENT '编码创建时间（以此为准，30分钟后自动关闭）',
    `create_time`               datetime        DEFAULT CURRENT_TIMESTAMP,
    `update_time`               datetime        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `remark`                    varchar(255)    DEFAULT NULL COMMENT '备注',
    PRIMARY KEY (`behalf_id`),
    UNIQUE KEY `idx_behalf_code` (`behalf_code`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = 'utf8mb4_general_ci' COMMENT ='代客下单';

CREATE TABLE `tz_order_on_behalf_sku`
(
    `behalf_sku_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '记录ID',
    `behalf_id` BIGINT DEFAULT NULL COMMENT '代客下单表id',
    `sku_id` BIGINT DEFAULT NULL COMMENT '规格id',
    `prod_id` BIGINT DEFAULT NULL COMMENT '商品id',
    `count` INT DEFAULT NULL COMMENT '下单数量',
    PRIMARY KEY (`behalf_sku_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='代客下单选择的商品规格表';

INSERT INTO `tz_shop_menu` (`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`, `router_name`, `sys_type`) VALUES
(1422, 203, 'order/order-on-behalf/index', '', 1, '', 0, 0, 'order-on-behalf', 1),
(1423, 1422, '', 'order:orderOnBehalf:save', 2, '', 0, 0, NULL, 1),
(1424, 1422, '', 'order:orderOnBehalf:update', 2, '', 0, 0, NULL, 1),
(1425, 1422, '', 'order:orderOnBehalf:delete', 2, '', 0, 0, NULL, 1),
(1426, 203, 'order/order-on-behalf/add-or-update', '', 1, '', 0, 1, 'behalf-add-or-update', 1),
(1427, 1107, 'order/order-on-behalf/index', '', 1, '', 0, 0, 'order-on-behalf', 3),
(1428, 1427, '', 'order:orderOnBehalf:save', 2, '', 0, 0, NULL, 3),
(1429, 1427, '', 'order:orderOnBehalf:update', 2, '', 0, 0, NULL, 3),
(1430, 1427, '', 'order:orderOnBehalf:delete', 2, '', 0, 0, NULL, 3),
(1431, 1107, 'order/order-on-behalf/add-or-update', '', 1, '', 0, 1, 'behalf-add-or-update', 3);

INSERT INTO `tz_shop_menu_lang` (`menu_id`, `lang`, `name`) VALUES
(1422, 0, '代客下单'),
(1423, 0, '保存'),
(1424, 0, '修改'),
(1425, 0, '删除'),
(1426, 0, '代客下单详情'),
(1427, 0, '代客下单'),
(1428, 0, '保存'),
(1429, 0, '修改'),
(1430, 0, '删除'),
(1431, 0, '代客下单详情');

ALTER TABLE `tz_order`
    ADD COLUMN `behalf_id` bigint NULL DEFAULT NULL COMMENT '代客下单表id';

INSERT INTO `tz_leaf_alloc` (`biz_tag`, `max_id`, `step`, `description`)
VALUES ('mall4j-behalf-code', 1000000000000, 10, '代客下单唯一编码（用于生成分享二维码）');

-- 更换为唯一索引
ALTER TABLE `tz_stock_point_sku`
DROP INDEX `idx_sku_id`,
ADD UNIQUE INDEX `idx_sku_stock_point_id`(`sku_id`, `stock_point_id`) USING BTREE;

ALTER TABLE `tz_flow_log`
    MODIFY COLUMN `biz_data` varbinary(1000) NULL DEFAULT NULL COMMENT '业务数据' AFTER `visit_type`;