qimall/addons/ServeBonus/install.sql

-- ----------------------------
-- 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);