qimall/addons/Achievement/install.sql

-- ----------------------------
-- Table structure for qimall_addons_achievement_award_log
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_achievement_award_log`;
CREATE TABLE `qimall_addons_achievement_award_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0,
  `grant_id` int(11) NOT NULL DEFAULT 0 COMMENT '发放规则日志id',
  `user_id` int(11) NOT NULL DEFAULT 0 COMMENT '用户id',
  `achievement_sum` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '总业绩',
  `team_achievement` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '团队业绩',
  `personal_achievement` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '个人业绩',
  `award_proportion` double(10, 2) NOT NULL DEFAULT 0.00 COMMENT '奖励比例',
  `award_money` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '奖励金额',
  `is_settlement` tinyint(4) NULL DEFAULT 0 COMMENT '是否已结算打款 1：是',
  `is_end` tinyint(4) NULL DEFAULT 0 COMMENT '是否结束周期：1：是',
  `award_sum` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '奖励总计',
  `parent_team` int(11) NULL DEFAULT 0 COMMENT '父级团长id',
  `proportion_money` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '比例结算金额',
  `actual_money` decimal(10, 0) NULL DEFAULT 0 COMMENT '实际应该结算金额',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id`(`mall_id`) USING BTREE,
  INDEX `grant_id`(`grant_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '奖励明细' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Table structure for qimall_addons_achievement_commission_item_setting
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_achievement_commission_item_setting`;
CREATE TABLE `qimall_addons_achievement_commission_item_setting`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL COMMENT '商城ID',
  `is_join` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否参与业绩奖励',
  `is_enable` 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 '分佣类型：积分score;佣金：commission',
  `is_percent` tinyint(1) NOT NULL DEFAULT 0 COMMENT '奖励类型 0百分比 1 固定金额',
  `price` 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`(`mall_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '业绩奖励设置商品独立设置' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for qimall_addons_achievement_grant_log
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_achievement_grant_log`;
CREATE TABLE `qimall_addons_achievement_grant_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0 COMMENT '商城id',
  `award_proportion` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '奖励比例',
  `cycle` tinyint(4) NOT NULL DEFAULT 0 COMMENT '结算周期：0：按周 1：按月',
  `is_wait` tinyint(4) NULL DEFAULT 0 COMMENT '是否进入待结算：1：是',
  `cycle_time` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '结算时间',
  `wait_settlement_time` int(11) NOT NULL DEFAULT 0 COMMENT '等待结算时间',
  `is_settlement` tinyint(4) NULL DEFAULT 0 COMMENT '是否已结算 1：是',
  `settlement_time` int(11) NULL DEFAULT 0 COMMENT '结算时间',
  `total_performance` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '总业绩',
  `user_num` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '会员数量',
  `award_money` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '奖励金额',
  `statistical_methods` tinyint(4) NULL DEFAULT 0 COMMENT '统计方式 0：订单完成 1：支付完成',
  `is_create` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否创建下期：1：是',
  `end_time` int(11) NOT NULL DEFAULT 0 COMMENT '本期结束时间',
  `settlement_mode` tinyint(4) NULL DEFAULT 0 COMMENT '结算方式    0：不含极差 1：含极差',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id`(`mall_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '发放日志' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Table structure for qimall_addons_achievement_order_log
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_achievement_order_log`;
CREATE TABLE `qimall_addons_achievement_order_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0,
  `user_id` int(11) NOT NULL DEFAULT 0,
  `grant_id` int(11) NOT NULL DEFAULT 0 COMMENT '规则id',
  `goods_id` int(11) NOT NULL DEFAULT 0 COMMENT '商品id',
  `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单编号',
  `order_detail_id` int(11) NOT NULL DEFAULT 0 COMMENT '订单详情id',
  `chief_user_id` int(11) NOT NULL DEFAULT 0 COMMENT '队长的用户id',
  `price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '订单金额（支付金额）',
  `is_cloud` tinyint(4) NULL DEFAULT 0 COMMENT '是否云库存商品',
  `order_create_time` int(11) NULL DEFAULT 0 COMMENT '订单创建时间',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id`(`mall_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `grant_id`(`grant_id`) USING BTREE,
  INDEX `order_detail_id`(`order_detail_id`) USING BTREE,
  INDEX `chief_user_id`(`chief_user_id`) USING BTREE,
  INDEX `goods_id`(`goods_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '业绩所参与订单日志' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Table structure for qimall_addons_achievement_user
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_achievement_user`;
CREATE TABLE `qimall_addons_achievement_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0 COMMENT '商城id',
  `user_id` int(11) NULL DEFAULT 0 COMMENT '用户id',
  `total_commission` 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,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id`(`mall_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '达成奖励条件用户' ROW_FORMAT = COMPACT;


INSERT INTO `qimall_crontab_task`( `name`, `command`, `type`, `rule`, `is_warning`, `status`, `created_at`, `updated_at`) VALUES ( '业绩：结算', 'achievement/achievement-crontab/auto-grant-achievement', 2, '0 */6 * * * *', 0, 1, 1666835120, 1666835120);
INSERT INTO `qimall_crontab_task`( `name`, `command`, `type`, `rule`, `is_warning`, `status`, `created_at`, `updated_at`) VALUES ( '业绩：统计', 'achievement/achievement-crontab/auto-to-be-settled-timer', 2, '0 */5 * * * *', 0, 1, 1666835083, 1666835083);
INSERT INTO `qimall_crontab_task`( `name`, `command`, `type`, `rule`, `is_warning`, `status`, `created_at`, `updated_at`) VALUES ( '业绩：添加规则', 'achievement/achievement-crontab/auto-add-grant-log', 2, '0 */5 * * * *', 0, 1, 1666835048, 1666835048);