-- ---------------------------- -- Table structure for qimall_addons_servebonus -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus`; CREATE TABLE `qimall_addons_servebonus` ( `id` int(10) NOT NULL AUTO_INCREMENT, `mall_id` int(10) NOT NULL DEFAULT 0 COMMENT '商城ID', `user_id` int(10) NOT NULL DEFAULT 0 COMMENT '用户ID', `level` tinyint(4) NOT NULL DEFAULT 0 COMMENT '服务商等级', `commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '累计出单奖励佣金，单位：元', `extra_commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '累计加权平分佣金，单位：元', `temp_extra_commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '加权平分金额，主要用于判断是否会导致超额', `total_commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '累计总佣金，单位：元', `upgrade_status` smallint(6) NOT NULL DEFAULT 0 COMMENT '1条件升级，2购买指定商品升级，3手动升级', `upgrade_level_at` int(10) NOT NULL DEFAULT 0 COMMENT '股东等级升级时间', `period_num` int(10) NOT NULL DEFAULT 0 COMMENT '已分红期数', `status` tinyint(4) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(10) NOT NULL DEFAULT 0 COMMENT '创建时间', `updated_at` int(10) NOT NULL DEFAULT 0 COMMENT '修改时间', PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id+user_id`(`mall_id`, `user_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '服务商用户表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for qimall_addons_servebonus_achievement -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus_achievement`; CREATE TABLE `qimall_addons_servebonus_achievement` ( `id` int(10) NOT NULL AUTO_INCREMENT, `mall_id` int(10) NOT NULL DEFAULT 0 COMMENT '商城ID', `user_id` int(10) NOT NULL DEFAULT 0 COMMENT '出单用户ID', `buy_uid` int(10) NOT NULL DEFAULT 0 COMMENT '购买者uid', `level` tinyint(4) NOT NULL DEFAULT -1 COMMENT '出单用户等级', `order_id` int(10) NOT NULL DEFAULT 0 COMMENT '订单ID', `order_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '订单编号', `prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '出单奖励，单位：元', `average_count` int(10) NOT NULL DEFAULT 0 COMMENT '结算人数', `date` int(8) NOT NULL DEFAULT 0 COMMENT '出单日期，格式:20220124', `is_settlement` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否已结算【0否，1是】', `status` tinyint(4) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(10) NOT NULL DEFAULT 0, `updated_at` int(10) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id+date`(`mall_id`, `date`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '服务商当日出单表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for qimall_addons_servebonus_commission_item_setting -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus_commission_item_setting`; CREATE TABLE `qimall_addons_servebonus_commission_item_setting` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mall_id` int(11) NOT NULL COMMENT '商城ID', `is_enable` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否启用服务商分红：0:否;1是', `is_alone` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否独立设置：0:否;1是', `is_extra` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否开启额外分红：0:否;1是', `item_id` int(11) NOT NULL DEFAULT 0 COMMENT '项目id', `item_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '项目类型：商品goods', `commission_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '分佣类型：佣金：commission', `is_percent_prize` tinyint(1) NOT NULL DEFAULT 0 COMMENT '出单奖励类型 0百分比 1 固定金额', `is_percent_extra` tinyint(1) NOT NULL DEFAULT 0 COMMENT '加权平分类型 0百分比 1 固定金额', `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(11) NOT NULL DEFAULT 0, `updated_at` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id+item_id`(`mall_id`, `item_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '服务商项目分佣设置' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for qimall_addons_servebonus_commission_item_setting_detail -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus_commission_item_setting_detail`; CREATE TABLE `qimall_addons_servebonus_commission_item_setting_detail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mall_id` int(11) NOT NULL COMMENT '商城ID', `sid` int(11) NOT NULL COMMENT '对应qimall_addons_servebonus_commission_item_setting.id', `level` int(11) NOT NULL DEFAULT 0, `prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '出单奖', `extra_prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '加权平分奖', `is_accumulation` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否开启叠加加权平分：0否，1是', `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(11) NOT NULL DEFAULT 0, `updated_at` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id+sid`(`mall_id`, `sid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '服务商项目分佣设置详情' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for qimall_addons_servebonus_commission_job -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus_commission_job`; CREATE TABLE `qimall_addons_servebonus_commission_job` ( `id` int(10) NOT NULL AUTO_INCREMENT, `mall_id` int(10) NOT NULL DEFAULT 0 COMMENT '商城ID', `date_limit` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '结算开始~结束日期', `compute_period` tinyint(1) NOT NULL DEFAULT 0 COMMENT '结算周期：1天，2周，3月', `status` tinyint(4) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(10) NOT NULL DEFAULT 0, `updated_at` int(10) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id`(`mall_id`, `date_limit`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '服务商分红已结算日期记录表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for qimall_addons_servebonus_commission_log -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus_commission_log`; CREATE TABLE `qimall_addons_servebonus_commission_log` ( `id` int(10) NOT NULL AUTO_INCREMENT, `mall_id` int(10) NOT NULL DEFAULT 0 COMMENT '商城ID', `user_id` int(10) NOT NULL DEFAULT 0 COMMENT '用户ID', `period_id` int(10) NOT NULL DEFAULT 1 COMMENT '对应qimall_addons_servebonus_period.id', `commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '出单奖励，单位：元', `extra_commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '加权平分，单位：元', `compute_period` tinyint(1) NOT NULL DEFAULT 0 COMMENT '结算周期：1天，2周，3月', `start_time` int(10) NOT NULL DEFAULT 0 COMMENT '结算开始日期', `end_time` int(10) NOT NULL DEFAULT 0 COMMENT '结算结束日期', `status` tinyint(4) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(10) NOT NULL DEFAULT 0, `updated_at` int(10) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id+user_id+period_id`(`mall_id`, `user_id`, `period_id`) USING BTREE, INDEX `mall_id+period_id`(`mall_id`, `period_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '服务商分红记录表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for qimall_addons_servebonus_level -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus_level`; CREATE TABLE `qimall_addons_servebonus_level` ( `id` int(10) NOT NULL AUTO_INCREMENT, `mall_id` int(10) NOT NULL DEFAULT 0 COMMENT '商城ID', `level` tinyint(4) NOT NULL DEFAULT -1 COMMENT '服务商等级', `serve_bonus_number` int(11) NULL DEFAULT 0 COMMENT '服务商数量', `name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '等级名称', `is_auto_upgrade` int(10) NOT NULL DEFAULT 0 COMMENT '是否自动升级【0否，1是】', `upgrade_type_condition` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否开启条件升级【0否，1是】', `condition_type` tinyint(4) NOT NULL DEFAULT 1 COMMENT '满足条件：1满足其一，2满足所有', `checked_condition_values` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '升级条件的值集合', `checked_condition_keys` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '被选中的升级条件的key集合', `upgrade_type_goods` tinyint(6) NOT NULL DEFAULT 0 COMMENT '是否开启购买商品升级【0否，1是】', `goods_ids` varchar(3000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '指定商品id集合', `goods_list` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '指定商品信息列表json', `goods_type` smallint(6) NOT NULL DEFAULT 0 COMMENT '商品升级类型：1任意商品，2指定商品', `buy_goods_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '购买商品升级条件：0订单完成，1支付完成', `explain` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '等级说明', `status` tinyint(4) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(10) NOT NULL DEFAULT 0, `updated_at` int(10) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id`(`mall_id`) USING BTREE, INDEX `mall_id+level`(`mall_id`, `level`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '服务商等级表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for qimall_addons_servebonus_level_setting -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus_level_setting`; CREATE TABLE `qimall_addons_servebonus_level_setting` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mall_id` int(11) NOT NULL COMMENT '商城ID', `level` int(11) NOT NULL DEFAULT 0, `commission_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '分佣类型：积分score;佣金：commission', `from_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '分佣类型：股东;', `is_percent_prize` tinyint(1) NOT NULL DEFAULT 0 COMMENT '出单奖励类型 0百分比 1 固定金额', `prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '出单奖励', `is_percent_extra` tinyint(1) NOT NULL DEFAULT 0 COMMENT '加权平分类型 0百分比 1 固定金额', `extra_prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '加权平分', `is_extra` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否开启加权分红【0否，1是】', `extra_type` tinyint(4) NOT NULL DEFAULT 1 COMMENT '获取加权分红的类型：1购物，2条件，3任意', `extra_is_limit` tinyint(4) NOT NULL DEFAULT 0 COMMENT '加权分红是否存在上限【0否，1是】', `extra_limit_price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '加权分红上限金额，单位：元', `is_accumulation` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否开启叠加加权平分：0否，1是', `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(11) NOT NULL DEFAULT 0, `updated_at` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id`(`mall_id`) USING BTREE, INDEX `level`(`level`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '服务商分佣设置' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for qimall_addons_servebonus_period -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus_period`; CREATE TABLE `qimall_addons_servebonus_period` ( `id` int(10) NOT NULL AUTO_INCREMENT, `mall_id` int(10) NOT NULL DEFAULT 0 COMMENT '商城ID', `period_num` int(10) NOT NULL DEFAULT 1 COMMENT '第N期', `amounts` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '出单奖励奖金池，单位：元', `amounts_num` int(10) NOT NULL DEFAULT 0 COMMENT '出单奖励奖金人数', `extra_amounts` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '加权分红奖金池，单位：元', `total_amounts_num` int(10) NOT NULL DEFAULT 0 COMMENT '结算总人数', `compute_period` tinyint(1) NOT NULL DEFAULT 0 COMMENT '结算周期：1天，2周，3月', `start_time` int(10) NOT NULL DEFAULT 0 COMMENT '结算开始日期', `end_time` int(10) NOT NULL DEFAULT 0 COMMENT '结算结束日期', `status` tinyint(4) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(10) NOT NULL DEFAULT 0, `updated_at` int(10) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id+period_num`(`mall_id`, `period_num`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '分红结算期数表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Table structure for qimall_addons_servebonus_reward_log -- ---------------------------- DROP TABLE IF EXISTS `qimall_addons_servebonus_reward_log`; CREATE TABLE `qimall_addons_servebonus_reward_log` ( `id` int(10) NOT NULL AUTO_INCREMENT, `mall_id` int(10) NOT NULL DEFAULT 0 COMMENT '商城ID', `order_id` int(10) NOT NULL DEFAULT 0 COMMENT '订单ID', `order_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '订单编号', `user_id` int(10) NOT NULL DEFAULT 0 COMMENT '得奖励用户ID', `type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '结算类型：1出单奖励，2加权平分', `level` tinyint(4) NOT NULL DEFAULT -1 COMMENT '结算时用户的服务商等级', `amounts` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '结算的总金额，单位：元', `commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '结算的佣金，单位：元', `settlement_config` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '结算时的配置', `settlement_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '结算时的数据', `is_settlement` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否已结算【0否，1是】', `date` int(8) NOT NULL DEFAULT 0 COMMENT '数据生成日期', `period_id` int(10) NULL DEFAULT 0 COMMENT '分红期数ID', `status` tinyint(4) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]', `created_at` int(10) NOT NULL DEFAULT 0, `updated_at` int(10) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `mall_id+user_id`(`mall_id`, `user_id`) USING BTREE, INDEX `order_id`(`order_id`) USING BTREE, INDEX `order_no`(`order_no`) USING BTREE, INDEX `mall_id+date`(`mall_id`, `date`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '服务商分红奖励记录表' ROW_FORMAT = DYNAMIC; -- ---------------------------- -- 插入定时任务表 -- ---------------------------- INSERT INTO `qimall_crontab_task`(`name`, `command`, `type`, `rule`, `is_warning`, `status`, `created_at`, `updated_at`) VALUES ('服务商分红定时发放', 'serve-bonus-crontab/send-commission', 2, '0 30 1 * * *', 0, 1, 1644634327, 1644634327);