qimall/addons/Agent/install.sql

-- ----------------------------
-- Table structure for qimall_addons_agent
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_agent`;
CREATE TABLE `qimall_addons_agent`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0 COMMENT '商城ID',
  `user_id` int(11) NOT NULL DEFAULT 0 COMMENT '用户ID',
  `remarks` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '备注',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
  `updated_at` int(11) NOT NULL DEFAULT 0 COMMENT '修改时间',
  `upgrade_level_at` int(11) NOT NULL DEFAULT 0 COMMENT '分销商等级升级时间',
  `total_commission` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '累计佣金',
  `total_score` int(11) NOT NULL DEFAULT 0 COMMENT '累计积分',
  `upgrade_status` smallint(6) NOT NULL DEFAULT 0 COMMENT '1条件升级  2 购买指定商品升级   3手动升级',
  `level` tinyint(4) NOT NULL DEFAULT 0 COMMENT '渠道等级',
  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 = Dynamic;

-- ----------------------------
-- Table structure for qimall_addons_agent_commission_basis_log
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_agent_commission_basis_log`;
CREATE TABLE `qimall_addons_agent_commission_basis_log`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0 COMMENT '商城ID',
  `source_table` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '来源表',
  `source_table_id` int(11) NOT NULL DEFAULT 0 COMMENT '来源表id',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '积分：score；佣金：commission',
  `from_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '来源类型',
  `basic_config` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '基础配置',
  `level_config` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '等级配置',
  `goods_config` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '商品独立配置',
  `relation_ship` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '关系链等级',
  `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
  `updated_at` int(11) NOT NULL DEFAULT 0 COMMENT '修改时间',
  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_agent_commission_item_setting
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_agent_commission_item_setting`;
CREATE TABLE `qimall_addons_agent_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是',
  `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_team` tinyint(1) NOT NULL DEFAULT 0 COMMENT '渠道奖类型 0百分比 1 固定金额',
  `is_percent_equal` tinyint(1) NOT NULL DEFAULT 0 COMMENT '平级奖类型 0百分比 1 固定金额',
  `is_percent_over` 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`(`mall_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '渠道项目分佣设置' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for qimall_addons_agent_commission_item_setting_detail
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_agent_commission_item_setting_detail`;
CREATE TABLE `qimall_addons_agent_commission_item_setting_detail`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL COMMENT '商城ID',
  `agent_commission_item_setting_id` int(11) NOT NULL,
  `level` int(11) NOT NULL DEFAULT 0,
  `agent_team_prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '渠道奖',
  `agent_equal_prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '平级奖',
  `agent_over_prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '越级奖',
  `agent_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`(`mall_id`) USING BTREE,
  INDEX `agent_commission_item_setting_id`(`agent_commission_item_setting_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '渠道项目分佣设置详情' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for qimall_addons_agent_commission_level_setting
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_agent_commission_level_setting`;
CREATE TABLE `qimall_addons_agent_commission_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 '分佣类型：渠道Agent;幸运平团：Lucky_group',
  `is_percent_team` tinyint(1) NOT NULL DEFAULT 0 COMMENT '渠道奖类型 0百分比 1 固定金额',
  `is_percent_equal` tinyint(1) NOT NULL DEFAULT 0 COMMENT '平级奖类型 0百分比 1 固定金额',
  `is_percent_over` tinyint(1) NOT NULL DEFAULT 0 COMMENT '越级奖类型 0百分比 1 固定金额',
  `is_percent_extra` tinyint(1) NOT NULL DEFAULT 0 COMMENT '额外奖类型 0百分比 1 固定金额',
  `agent_team_prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '渠道奖',
  `agent_equal_prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '平级奖',
  `agent_over_prize` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '越级奖',
  `agent_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`(`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_agent_goods_exchange
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_agent_goods_exchange`;
CREATE TABLE `qimall_addons_agent_goods_exchange`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0 COMMENT '商城ID',
  `goods_id` int(11) NOT NULL DEFAULT 0 COMMENT '商品ID',
  `level` int(11) NOT NULL DEFAULT 0 COMMENT '等级',
  `order_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '1/支付完成 2/订单完成',
  `indicators_num` int(11) NOT NULL DEFAULT 0 COMMENT '单量指标',
  `created_at` int(11) NOT NULL DEFAULT 0,
  `updated_at` int(11) NOT NULL,
  `is_delete` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_goods_id`(`goods_id`) USING BTREE,
  INDEX `imdex_mall_id`(`mall_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '等级商品兑换单量数量' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for qimall_addons_agent_goods_setting
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_agent_goods_setting`;
CREATE TABLE `qimall_addons_agent_goods_setting`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) NOT NULL DEFAULT 0 COMMENT '商城ID',
  `goods_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '独立配置详情',
  `status` tinyint(4) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `updated_at` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id+goods_id`(`mall_id`, `goods_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '渠道管理-商品单独设置表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for qimall_addons_agent_level
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_agent_level`;
CREATE TABLE `qimall_addons_agent_level`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL COMMENT '商城ID',
  `level` int(11) NOT NULL DEFAULT -1,
  `name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '等级名称',
  `is_auto_upgrade` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否开启自动升级',
  `upgrade_type_condition` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否开启条件升级',
  `checked_condition_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '升级条件',
  `checked_condition_keys` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '升级条件选中的key集合',
  `condition_type` tinyint(1) NOT NULL DEFAULT 1 COMMENT '0 未选择、1满足其一  2、满足所有',
  `upgrade_type_goods` tinyint(1) NOT NULL DEFAULT 0 COMMENT '购买升级商品,0关闭，1开启',
  `buy_goods_type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '购买升级商品触发升级:0订单完成 1下单完成',
  `goods_type` tinyint(1) NOT NULL DEFAULT 1 COMMENT '商品升级类型，1- 任意商品 2- 指定商品',
  `goods_ids` varchar(3000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '商品id集合',
  `goods_list` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '商品列表json',
  `explain` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' 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 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '渠道等级' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for qimall_addons_agent_setting
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_agent_setting`;
CREATE TABLE `qimall_addons_agent_setting`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL,
  `key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
  `updated_at` int(11) NOT NULL DEFAULT 0 COMMENT '修改时间',
  `is_delete` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除 0--未删除 1--已删除',
  `deleted_at` int(11) NOT NULL DEFAULT 0 COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id`(`mall_id`) USING BTREE,
  INDEX `key`(`key`(191)) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '经销插件设置' ROW_FORMAT = Dynamic;

INSERT INTO `qimall_global_var_map`(`name`, `title`, `type`, `is_addons`, `status`, `created_at`, `updated_at`) VALUES
('Agent', '渠道分红', 'IncomeMap', 1, 1, 1654569888, 1654569888);


ALTER TABLE qimall_addons_agent_level add is_open_equal_settle_type tinyint(1) DEFAULT 0 COMMENT '开启独立平级奖结算';
ALTER TABLE qimall_addons_agent_level add equal_settle_type tinyint(1) DEFAULT 0 COMMENT '平级奖结算方式';


ALTER TABLE qimall_addons_agent_commission_level_setting add  `team_achievement` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '渠道业绩额外奖励';


INSERT INTO `qimall_global_var_map`(`name`, `title`, `type`, `is_addons`, `status`, `created_at`, `updated_at`) VALUES('Agent', '渠道分红', 'FromTypeMap', 1, 1, 1654569888, 1654569888);
INSERT INTO `qimall_global_var_map`(`name`, `title`, `type`, `is_addons`, `status`, `created_at`, `updated_at`) VALUES ( 'team_achievement_extra', '渠道业绩额外奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888);


INSERT INTO `qimall_crontab_task`( `name`, `command`, `type`, `rule`, `is_warning`, `status`, `created_at`, `updated_at`) VALUES ('按月结算渠道业绩额外奖励', 'agent/agent/index', 2, '0 0 0 0 1 *', 0, 1, 1666835191, 1666835191);