183 lines
16 KiB
SQL
183 lines
16 KiB
SQL
#师傅端
|
||
ALTER TABLE `la_staff`
|
||
ADD COLUMN `head_portrait` varchar(128) NULL COMMENT '头像链接' AFTER `sn`,
|
||
ADD COLUMN `id_card_img` varchar(1000) NULL COMMENT '身份证正反面图片 逗号隔开' AFTER `head_portrait`,
|
||
ADD COLUMN `id_card` varchar(18) NOT NULL COMMENT '身份证' AFTER `id_card_img`,
|
||
ADD COLUMN `age` int(11) NULL COMMENT '年龄' AFTER `id_card`,
|
||
ADD COLUMN `physical_examination` varchar(1200) NULL COMMENT '体检报告 多图片逗号隔开' AFTER `mobile`,
|
||
ADD COLUMN `score` double(10, 1) NULL DEFAULT 5.0 COMMENT '师傅评分 默认评分：5' AFTER `is_delete`,
|
||
ADD COLUMN `basic_score` double(10, 2) NULL DEFAULT 5.0 COMMENT '基础评分 默认评分：5' AFTER `is_delete`,
|
||
ADD COLUMN `is_receive_order` tinyint(1) NULL DEFAULT 1 COMMENT '是否可以接单：1-可以；0-不可接单;' AFTER `score`,
|
||
ADD COLUMN `is_order` tinyint(1) NULL DEFAULT 0 COMMENT '师傅是否接单：1-接单中；0-休息中;' AFTER `is_receive_order`,
|
||
ADD COLUMN `is_authentication` tinyint(1) NULL DEFAULT 0 COMMENT '是否实名认证：1-已实名认证；0-未实名认证;' AFTER `is_order`,
|
||
ADD COLUMN `can_withdraw_commission` decimal(10, 2) NOT NULL DEFAULT 0 COMMENT '可提现佣金' AFTER `is_authentication`,
|
||
ADD COLUMN `already_withdraw` decimal(10, 2) NOT NULL DEFAULT 0 COMMENT '已提现佣金' AFTER `can_withdraw_commission`,
|
||
MODIFY COLUMN `create_time` datetime NOT NULL COMMENT '创建时间' AFTER `is_recommend`,
|
||
MODIFY COLUMN `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间' AFTER `create_time`,
|
||
MODIFY COLUMN `delete_time` datetime NULL DEFAULT NULL COMMENT '删除时间' AFTER `update_time`,
|
||
|
||
DROP TABLE IF EXISTS `la_staff_apply_for`;
|
||
CREATE TABLE `la_staff_apply_for` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`user_id` int(11) NOT NULL COMMENT '用户id',
|
||
`head_portrait` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '头像链接',
|
||
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '师傅姓名',
|
||
`sex` tinyint(1) NOT NULL COMMENT '性别:1-男;2-女;',
|
||
`mobile` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '手机号码',
|
||
`id_card` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '身份证',
|
||
`province_id` int(11) NULL DEFAULT NULL COMMENT '省id',
|
||
`city_id` int(11) NULL DEFAULT NULL COMMENT '市id',
|
||
`district_id` int(11) NULL DEFAULT NULL COMMENT '区id',
|
||
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '详细地址',
|
||
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
|
||
`goods_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '服务项目',
|
||
`id_card_img` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '身份证正反面图片 逗号隔开',
|
||
`physical_examination` varchar(1200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '体检报告 多图片逗号隔开',
|
||
`status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '申请状态：0-申请中 1-申请通过 2-拒绝',
|
||
`refuse_reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '拒绝理由',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`is_delete` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除 0：否 1：是',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 63 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '师傅申请表' ROW_FORMAT = DYNAMIC;
|
||
|
||
DROP TABLE IF EXISTS `la_staff_commission`;
|
||
CREATE TABLE `la_staff_commission` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`staff_id` bigint(20) NOT NULL COMMENT '师傅id',
|
||
`order_id` bigint(20) NOT NULL COMMENT '订单id',
|
||
`goods_commission_rate` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '服务抽佣比',
|
||
`commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '佣金',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`status` int(11) NOT NULL DEFAULT 0 COMMENT '佣金状态：0-冻结 1-可提现',
|
||
`is_delete` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除：0-未删除 1已删除',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 37 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '师傅佣金表' ROW_FORMAT = DYNAMIC;
|
||
|
||
DROP TABLE IF EXISTS `la_staff_physical_examination`;
|
||
CREATE TABLE `la_staff_physical_examination` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
|
||
`staff_id` int(11) NOT NULL COMMENT '师傅id',
|
||
`physical_examination` varchar(1200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '体检报告',
|
||
`status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '申请状态：0-申请中 1-申请通过 2-拒绝',
|
||
`refuse_reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '拒绝理由',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`is_read` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已读：0-未读；1-已读；',
|
||
`is_delete` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除 0：否 1：是',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 33 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '师傅体检报告申请表' ROW_FORMAT = DYNAMIC;
|
||
|
||
ALTER TABLE `la_goods`
|
||
CHANGE COLUMN `commission_rate` `distributor_commission_rate` decimal(10, 2) NOT NULL DEFAULT 0 COMMENT '分销商抽佣比例' AFTER `is_delete`,
|
||
ADD COLUMN `staff_commission_rate` decimal(10, 2) NOT NULL DEFAULT 0 COMMENT '师傅抽佣比例' AFTER `distributor_commission_rate`;
|
||
|
||
ALTER TABLE `la_distributor_withdraw`
|
||
ADD COLUMN `staff_id` bigint NULL DEFAULT NULL COMMENT '师傅id' AFTER `id`,
|
||
MODIFY COLUMN `distributor_id` bigint NULL DEFAULT NULL COMMENT '分销商id' AFTER `id`;
|
||
ADD COLUMN `refuse_reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '拒绝理由' AFTER `is_delete`;
|
||
|
||
ALTER TABLE `la_order_log`
|
||
MODIFY COLUMN `type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '类型:1-系统;2-后台;3-用户;4-师傅' AFTER `id`;
|
||
|
||
ALTER TABLE `la_goods_comment`
|
||
ADD COLUMN `customer_service_appraise` tinyint(1) NULL COMMENT '客服评价星级 \r\n师傅申述后，客服核实修改的评价星级影响师傅总体评分' AFTER `service_comment`,
|
||
ADD COLUMN `staff_id` int NOT NULL COMMENT '师傅id' AFTER `order_goods_id`,
|
||
MODIFY COLUMN `service_comment` tinyint(1) NULL DEFAULT NULL COMMENT '服务评价星级 0为系统自动评分 1 一星 2 二星 3三星 4四星 5五星' AFTER `staff_id`;
|
||
|
||
ALTER TABLE `la_order_goods`
|
||
ADD COLUMN `staff_commission_rate` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '师傅抽佣比例' AFTER `distributor_commission_rate`;
|
||
|
||
ALTER TABLE `la_staff`
|
||
MODIFY COLUMN `is_receive_order` tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT '是否可以接单：0-可以；1-不可接单;' AFTER `score`,
|
||
MODIFY COLUMN `is_order` tinyint(1) NULL DEFAULT 0 COMMENT '师傅是否接单：0-接单中；1-休息中;' AFTER `is_receive_order`,
|
||
CHANGE COLUMN `basic_score` `add_score` double(10, 2) NULL DEFAULT 0 COMMENT '追加评分' AFTER `is_delete`;
|
||
|
||
ALTER TABLE `la_order`
|
||
ADD COLUMN `add_score` double(10, 2) NULL DEFAULT NULL COMMENT '追加评分' AFTER `deduction_money`;
|
||
|
||
INSERT INTO `la_system_config` (`type`, `name`, `value`, `create_time`, `update_time`) VALUES ('staff_channel', 'appId', 'wx9ab7116e6cb02aac', 1698139343, 0);
|
||
INSERT INTO `la_system_config` (`type`, `name`, `value`, `create_time`, `update_time`) VALUES ('staff_channel', 'appSecret', 'f517c3268331d6c23d4c4a86ced24f8c', 1698139343, 0);
|
||
INSERT INTO `la_system_config` (`type`, `name`, `value`, `create_time`, `update_time`) VALUES ('staff_channel', 'name', '粤好生活师傅端', 1698141388, 0);
|
||
|
||
INSERT INTO `la_crontab` (`name`, `types`, `command`, `rules`, `remark`, `error`, `status`, `strategy`, `concurrent`, `is_delete`, `start_time`, `end_time`, `task_time`, `task_max_time`, `create_time`, `update_time`, `delete_time`) VALUES ('解冻师傅佣金', 'default', 'staff.unfreezingCommission', '0 0 0 * * ?', '每天凌晨12点解冻师傅佣金', '', 2, 1, 0, 0, 1698930300, 1698930300, 70, 0, 1698739883, 1698750694, 0);
|
||
INSERT INTO `la_crontab` (`name`, `types`, `command`, `rules`, `remark`, `error`, `status`, `strategy`, `concurrent`, `is_delete`, `start_time`, `end_time`, `task_time`, `task_max_time`, `create_time`, `update_time`, `delete_time`) VALUES ('系统自动评分和计算师傅分数', 'default', 'staff.systemAutoScoreAndCalculateScore', '0 0 0 * * ?', '每天凌晨12点执行', '', 2, 1, 0, 0, 1698975370, 1698975370, 50, 0, 1698739836, 1699165927, 0);
|
||
UPDATE `la_system_config` SET `type` = 'website_map_key', `name` = 'tencent_map_key', `value` = 'IFUBZ-WRIKQ-7HB5M-2WOCK-DPKNT-2YF3D', `create_time` = 1631255140, `update_time` = 1683359609 WHERE `name` = 'tencent_map_key';
|
||
|
||
INSERT INTO `la_system_auth_menu`( `pid`, `menu_type`, `menu_name`, `menu_icon`, `menu_sort`, `perms`, `paths`, `component`, `selected`, `params`, `is_cache`, `is_show`, `is_disable`, `create_time`, `update_time`) VALUES ( 770, 'C', '师傅申请列表', '', 0, '', 'apply', 'master_worker/apply', '', '', 1, 1, 0, 1697509752, 1697509752);
|
||
INSERT INTO `la_system_auth_menu`( `pid`, `menu_type`, `menu_name`, `menu_icon`, `menu_sort`, `perms`, `paths`, `component`, `selected`, `params`, `is_cache`, `is_show`, `is_disable`, `create_time`, `update_time`) VALUES ( 770, 'C', '师傅体检报告申请', '', 0, '', 'physical', 'master_worker/physical', '', '', 1, 1, 0, 1698047513, 1698047513);
|
||
|
||
INSERT INTO `la_notice_setting` (`scene`, `name`, `remarks`, `recipient`, `type`, `system_notice`, `sms_notice`, `oa_notice`, `mnp_notice`, `is_delete`, `create_time`, `update_time`, `delete_time`) VALUES (105, '实名认证验证码', '师傅端师傅进行实名认证时发送', 1, 2, '{}', '{\"type\":\"sms\",\"templateId\":\"1954500\",\"content\":\"您正在进行粤好生活平台实名认证，验证码为：{1}，5分钟有效，为保障帐户安全，请勿向任何人提供此验证码。\",\"tips\":\"[\\\"可选变量 验证码:code\\\",\\\"示例：您正在进行粤好生活平台实名认证，验证码为：${code}，5分钟有效，为保障帐户安全，请勿向任何人提供此验证码。\\\",\\\"生效条件：1、管理后台完成短信设置。2、第三方短信平台申请模板。\\\"]\",\"status\":\"1\"}', '{\"tpl\":\"null\",\"name\":\"\"}', '{}', 0, 1648696695, 1684916440, 0);
|
||
|
||
#师傅和分销商提现 修复取消订单
|
||
CREATE TABLE `la_cancel_order_config` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT,
|
||
`type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '模版类型：0-师傅；1-订单',
|
||
`goods_id` int(11) NULL DEFAULT NULL COMMENT '服务id（type为1才使用）',
|
||
`interval_time` int(11) NOT NULL COMMENT '间隔时间（时）',
|
||
`value` double(10, 2) NOT NULL DEFAULT 0.00 COMMENT '值',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 285 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '取消订单配置表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `la_commission_withdraw` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
|
||
`sn` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '提现单号',
|
||
`detail_sn` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '提现详情单号',
|
||
`batch_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '微信提现回执单号',
|
||
`distributor_id` bigint(20) NULL DEFAULT NULL COMMENT '分销商id',
|
||
`staff_id` bigint(20) NULL DEFAULT NULL COMMENT '师傅id',
|
||
`withdraw_money` decimal(10, 2) NOT NULL COMMENT '提现金额',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间（到账时间）',
|
||
`status` int(11) NOT NULL COMMENT '提现状态：0-提现中 1提现成功 2提现失败',
|
||
`is_delete` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除：0-未删除 1已删除',
|
||
`fail_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '失败原因',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '分销商提现表' ROW_FORMAT = DYNAMIC;
|
||
|
||
CREATE TABLE `la_staff_reassignment` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
|
||
`order_id` int(11) NOT NULL COMMENT '订单id',
|
||
`user_id` int(11) NULL DEFAULT NULL COMMENT '用户id',
|
||
`system_auth_admin_id` int(11) NULL DEFAULT NULL COMMENT '系统认证管理员id',
|
||
`staff_id` int(11) NOT NULL COMMENT '师傅id',
|
||
`time_before` int(10) NULL DEFAULT NULL COMMENT '距离服务时间多少分钟前',
|
||
`deduct_score` double(10, 2) NULL DEFAULT 0.00 COMMENT '扣除分数',
|
||
`create_time` datetime NOT NULL COMMENT '创建时间',
|
||
`status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '状态：0 - 申请中；1 - 已改派；',
|
||
`is_delete` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除：0-否；1-是；',
|
||
PRIMARY KEY (`id`) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 65 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '师傅改派表' ROW_FORMAT = Dynamic;
|
||
|
||
ALTER TABLE `la_staff`
|
||
MODIFY COLUMN `score` double(10, 2) NULL DEFAULT 5.0 COMMENT '师傅评分 默认评分：5' AFTER `add_score`;
|
||
|
||
ALTER TABLE `la_user`
|
||
ADD COLUMN `type` tinyint(3) NOT NULL DEFAULT 0 COMMENT '用户类型：[0=用户端，1=师傅端]' AFTER `channel`;
|
||
|
||
update la_user SET type = 1 where id in (select user_id from la_staff where la_staff.is_delete = 0)
|
||
|
||
ALTER TABLE `la_order_refund`
|
||
ADD COLUMN `refund_reason` varchar(255) NULL COMMENT '退款原因' AFTER `refund_status`,
|
||
ADD COLUMN `service_time_before_minute` int(10) NULL DEFAULT 0 COMMENT '距离上门服务时间（分钟）' AFTER `refund_reason`,
|
||
ADD COLUMN `deduction_ratio` decimal(10, 2) NULL DEFAULT 0 COMMENT '扣款比例' AFTER `service_time_before_minute`,
|
||
ADD COLUMN `practical_deduction_ratio` decimal(10, 2) NULL DEFAULT 0 COMMENT '实际扣款比例' AFTER `deduction_ratio`;
|
||
|
||
ALTER TABLE `la_staff_reassignment`
|
||
MODIFY COLUMN `user_id` int(11) NULL DEFAULT NULL COMMENT '用户id' AFTER `order_id`,
|
||
ADD COLUMN `system_auth_admin_id` int(11) NULL DEFAULT NULL COMMENT '系统认证管理员id' AFTER `user_id`;
|
||
|
||
INSERT INTO `la_crontab` (`name`, `types`, `command`, `rules`, `remark`, `error`, `status`, `strategy`, `concurrent`, `is_delete`, `start_time`, `end_time`, `task_time`, `task_max_time`, `create_time`, `update_time`, `delete_time`) VALUES ('处理提现结果', 'default', 'distributor.checkDistributorWithdraw', '0 * * * * ?', '处理师傅和分销商的提现结果', '', 1, 1, 0, 0, 1700559360, 1700559360, 19, 0, 1700477346, 1700553857, 0);
|
||
|
||
#新增运营师傅
|
||
ALTER TABLE `la_staff`
|
||
ADD COLUMN `is_operational` tinyint(1) NULL DEFAULT 0 COMMENT '是否运营师傅' AFTER `already_withdraw`,
|
||
ADD COLUMN `work_start_time` datetime NULL COMMENT '工作开始时间' AFTER `is_operational`,
|
||
ADD COLUMN `work_end_time` datetime NULL COMMENT '工作结束时间' AFTER `work_start_time`;
|
||
|
||
#修改师傅服务项目为服务类目
|
||
ALTER TABLE `la_staff`
|
||
CHANGE COLUMN `goods_ids` `goods_category_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '服务类目ids' AFTER `physical_examination`;
|
||
|
||
#自动派单
|
||
INSERT INTO `la_crontab`(`name`, `types`, `command`, `rules`, `remark`, `error`, `status`, `strategy`, `concurrent`, `is_delete`, `start_time`, `end_time`, `task_time`, `task_max_time`, `create_time`, `update_time`, `delete_time`) VALUES ('自动派单', 'default', 'staff.autoSendOrders', '* * * * * ?', '每隔1分钟查询15分钟前的订单是否有师傅抢单，没有则自动派单', '', 1, 1, 0, 0, 1704718106, 1704718106, 34, 0, 1704700975, 1704718101, 0);
|