java-bbc/db/2024-12-31 达达配送对接.sql

USE `yami_bbc`;

# 新增达达配送配置
INSERT INTO `tz_sys_config`(`param_key`, `param_value`, `remark`)
VALUES
    ('DADA_CONFIG', '{\"appKey\":\"\",\"appSecret\":\"\",\"shopSourceId\":\"\",\"isTestEnv\":false,\"enable\":false}', '达达配送配置');

# 新增达达配送品类表
CREATE TABLE `tz_dada_business`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dada_business_id` int NULL COMMENT '达达品类id',
  `lang` int NULL COMMENT '语言类型',
  `dada_business_name` varchar(255) NULL COMMENT '达达品类名称',
  PRIMARY KEY (`id`)
);

INSERT INTO `tz_dada_business` ( `dada_business_id`, `lang`, `dada_business_name` )
VALUES
	( 1, 0, "食品小吃" ),
	( 2, 0, "饮料" ),
	( 3, 0, "鲜花绿植" ),
	( 5, 0, "其他" ),
	( 8, 0, "文印票务" ),
	( 9, 0, "便利店" ),
	( 13, 0, "水果生鲜" ),
	( 19, 0, "同城电商" ),
	( 20, 0, "医药" ),
	( 21, 0, "蛋糕" ),
	( 24, 0, "酒品" ),
	( 25, 0, "小商品市场" ),
	( 26, 0, "服装" ),
	( 27, 0, "汽修零配" ),
	( 28, 0, "数码家电" ),
	( 29, 0, "小龙虾/烧烤" ),
	( 31, 0, "超市" ),
	( 50, 0, "个人" ),
	( 51, 0, "火锅" ),
	( 53, 0, "个护美妆" ),
	( 55, 0, "母婴 " ),
	( 57, 0, "家居家纺" ),
	( 59, 0, "手机" ),
	( 61, 0, "家装" ),
	( 63, 0, "成人用品" ),
	( 65, 0, "校园" ),
	( 66, 0, "高端市场" ),
	( 1, 1, "Food Snacks" ),
	( 2, 1, "Beverages" ),
	( 3, 1, "Flowers and Greenery" ),
	( 5, 1, "Other" ),
	( 8, 1, "Printing and Ticketing" ),
	( 9, 1, "Convenience Stores" ),
	( 13, 1, "Fresh Fruits and Vegetables" ),
	( 19, 1, "Local E-commerce" ),
	( 20, 1, "Pharmaceuticals" ),
	( 21, 1, "Cakes" ),
	( 24, 1, "Alcoholic Beverages" ),
	( 25, 1, "Small Commodities Market" ),
	( 26, 1, "Apparel" ),
	( 27, 1, "Automobile Repair and Parts" ),
	( 28, 1, "Digital Electronics and Household Appliances" ),
	( 29, 1, "Crayfish/Barbecue" ),
	( 31, 1, "Supermarkets" ),
	( 50, 1, "Personal" ),
	( 51, 1, "Hot Pot" ),
	( 53, 1, "Personal Care and Beauty" ),
	( 55, 1, "Maternity and Baby" ),
	( 57, 1, "Home Textiles and Furnishings" ),
	( 59, 1, "Mobile Phones" ),
	( 61, 1, "Home Decoration" ),
	( 63, 1, "Adult Products" ),
	( 65, 1, "Campus" ),
	( 66, 1, "High-end Market" );

# 门店新增字段，索引
ALTER TABLE `tz_station`
ADD COLUMN `contact_name` varchar(255) NULL COMMENT '联系人名称' AFTER `img_urls`,
ADD COLUMN `delivery_type` tinyint(1) NULL DEFAULT 0 COMMENT '配送方式(0:自行配送, 1:达达配送)' AFTER `custom_marking`,
ADD COLUMN `dada_business_id` int(0) NULL COMMENT '达达配送品类id' AFTER `delivery_type`,
ADD COLUMN `dada_account_type` tinyint(1) NULL DEFAULT 0 COMMENT '达达账号类型(0:自动注册, 1:平台绑定)' AFTER `dada_business_id`,
ADD COLUMN `dada_username` varchar(50) NULL COMMENT '达达商家用户名' AFTER `dada_business_id`,
ADD COLUMN `dada_password` varchar(255) NULL COMMENT '达达商家密码' AFTER `dada_username`,
ADD COLUMN `dada_station_created` tinyint(1) NULL DEFAULT 0 COMMENT '达达门店是否已创建(0:否, 1:是)' AFTER `dada_password`,
ADD INDEX `dada_business_idx`(`dada_business_id`) USING BTREE;

# 订单新增达达相关字段
ALTER TABLE `tz_order`
ADD COLUMN `dada_order_create_time` datetime(0) NULL COMMENT '达达订单创建时间' AFTER `jst_order_id`;

# 用户订单地址添加经纬度
ALTER TABLE`tz_user_addr_order`
ADD COLUMN `lng` decimal(12, 6) NULL DEFAULT NULL COMMENT '经度' AFTER `addr`,
ADD COLUMN `lat` decimal(12, 6) NULL DEFAULT NULL COMMENT '纬度' AFTER `lng`;

# 新增消息模板通知
INSERT INTO `tz_notify_template`(`template_id`, `send_type`, `message`, `template_types`, `template_code`, `mp_code`, `msg_type`, `status`, `create_time`)
VALUES
    (1838, 112, '同城配送订单编号${orderNumber}自动发货失败，请尽快处理', '1,3', NULL, NULL, 2, 1, NULL);


# 商家端-会员列表-更新用户等级按钮默认为不隐藏
UPDATE `tz_shop_menu` SET hidden = 0 WHERE perms = 'user:userLevel:updateUserLevel';


# 添加通联定时任务,开启通联的情况下需要开启这两个定时任务
USE `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,'查询一个小时前待审核的商家在通联那边的审核状态,如果已经是全部审核通过需要将商家的审核状态改成以通过(开启通联后需要开启此定时任务)','2024-12-19 17:14:32','2024-12-20 15:17:52','admin','','CRON','0 1 * * * ?','DO_NOTHING','FIRST','checkAllinpayShopStatus','','SERIAL_EXECUTION','0','0','BEAN','','GLUE代码初始化','2024-12-19 17:14:32','','0','0','0'),
(2,'通联的商家影印件上传,定时任务20s执行一次,每个图片需要间隔5s(开启通联后需要开启此定时任务)','2024-12-19 10:38:34','2024-12-19 10:38:34','admin','','CRON','0/20 * * * * ?','DO_NOTHING','FIRST','uploadFileToAllinpay','','SERIAL_EXECUTION','0','0','BEAN','','GLUE代码初始化','2024-12-19 10:38:34','','0','0','0');