qimall/addons/BossWeight/install.sql

CREATE TABLE `qimall_addons_boss_weight`  (
  `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 '股东等级升级时间',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `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`(`mall_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `mall_id+user_id`(`mall_id`, `user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '股东用户表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_boss_weight_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',
  `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 AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '股东项目分佣设置' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_boss_weight_commission_item_setting_detail`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL COMMENT '商城ID',
  `boss_commission_item_setting_id` int(11) NOT NULL,
  `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 '平级奖',
  `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+boss_commission_item_setting_id`(`mall_id`, `boss_commission_item_setting_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '股东项目分佣设置详情' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_boss_weight_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 AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '股东分红已结算日期记录表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_boss_weight_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',
  `amounts` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '结算总金额，单位：元',
  `commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '永久/额外佣金，单位：元',
  `type` tinyint(4) NOT NULL DEFAULT 1 COMMENT '奖励类型：1永久分红，2额外分红，3股东奖励',
  `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`(`mall_id`, `user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '股东分红记录表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_boss_weight_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 '股东等级',
  `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 AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '股东等级表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_boss_weight_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 '分佣类型：股东;',
  `prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '永久分红，单位：百分比',
  `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 '额外分红上限金额，单位：元',
  `weight_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否开启加权分红。0-不开启，1-开启',
  `weight_member_num` int(11) NOT NULL DEFAULT 0 COMMENT '加权分红条件，直推会员数量',
  `weight_product_num` int(11) NOT NULL DEFAULT 0 COMMENT '加权分红条件，购买产品数量',
  `weight_num` int(11) NOT NULL DEFAULT 0 COMMENT '加权分红配置，满足条件奖励的权重数量',
  `weight_level_ratio` int(11) NOT NULL DEFAULT 0 COMMENT '加权分红配置，等级分红比例',
  `weight_turnover_ratio` int(11) NOT NULL DEFAULT 0 COMMENT '加权分红配置，营业额比例',
  `weight_max_price` int(11) NOT NULL DEFAULT 0 COMMENT '加权分红配置。分红上限金额',
  `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 AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '股东分佣设置' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_boss_weight_price`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT 0,
  `level` int(11) NOT NULL DEFAULT 0 COMMENT '等级',
  `price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '分红总金额',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'boss用户分红金额表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_boss_weight_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 '结算时用户的股东等级',
  `average_count` int(10) NOT NULL DEFAULT 0 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 '数据生成日期',
  `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+user_id`(`mall_id`, `user_id`) USING BTREE,
  INDEX `order_id`(`order_id`) USING BTREE,
  INDEX `mall_id+date`(`mall_id`, `date`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 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 ('股东分红加权分红', 'boss-weight/boss-weight-crontab/send-commission', 2, '0 30 0 * * *', 0, 1, 1649757214, 1649757214);

-- 2022-7-16
ALTER TABLE `qimall_addons_boss_weight_level_setting`
ADD COLUMN `weight_is_limit`  tinyint(11) NOT NULL DEFAULT 0 COMMENT '是否开启权重限制条件 0 否 1 是',
ADD COLUMN `checked_weight_values`  varchar(1000) NOT NULL DEFAULT '' COMMENT '权重限制规则';


ALTER TABLE `qimall_addons_boss_weight`
ADD COLUMN `boss_weight_status`  tinyint(1) NOT NULL DEFAULT 0 COMMENT '股东权重状态 0 正常 1 达指定分红未复购商品权重失效 2 规定时间内未消费权重失效';

INSERT INTO `qimall_crontab_task` (`name`, `command`, `type`, `rule`, `is_warning`, `status`) VALUES ('股东加权分红状态每天更新', 'boss-weight/boss-weight-crontab/update-weight-rule', '2', '0     0    1    *    *    *', '0', '1');