qimall/qimall.sql

-- qimall_user_bank_card 表修改
alter table qimall_user_bank_card add `bank_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '银行名称';
alter table qimall_user_bank_card add `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '状态,-1:已删除;0:禁用;1:正常';

-- qimall_payment_trade 表添加一个字段记录聚合支付具体的支付方式
ALTER TABLE `qimall_payment_trade` ADD COLUMN `trade_type` varchar(100) NOT NULL DEFAULT '' COMMENT '聚合支付具体的支付方式';

CREATE TABLE `qimall_middle_mch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mid` int(11) DEFAULT '0' COMMENT '商戶ID',
  `mall_id` int(11) NOT NULL DEFAULT '0' COMMENT 'mall_id',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '商城名称',
  `app_id` int(11) NOT NULL DEFAULT '0' COMMENT '应用ID',
  `app_secret` varchar(64) NOT NULL DEFAULT '' COMMENT '应用秘钥',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态[-1:删除;0:禁用;1启用]',
  `domain` varchar(128) NOT NULL DEFAULT '' COMMENT '域名',
  `ip` varchar(64) NOT NULL DEFAULT '' COMMENT 'ip地址',
  `created_at` int(11) DEFAULT '0' COMMENT '创建时间',
  `updated_at` int(11) DEFAULT '0' COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mall_id` (`mall_id`,`domain`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='中台商户信息表';

INSERT INTO `qimall_crontab_task` (`name`, `command`, `type`, `rule`, `is_warning`, `status`, `created_at`, `updated_at`) VALUES ('订单自动确认收货', 'order/auto-confirm', 2, '0 0 */1 * * *', 0, 1, 1652325120, 1652325120);
INSERT INTO `qimall_crontab_task` (`id`, `name`, `command`, `type`, `rule`, `is_warning`, `status`, `created_at`, `updated_at`) VALUES ('订单自动过售后', 'order/auto-complete', 2, '0 0 */1 * * *', 0, 1, 1652325200, 1652325200);

CREATE TABLE `qimall_census_mall`  (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `mall_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商城ID',
    `total_users` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '总会员数',
    `total_orders` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '总订单量',
    `total_pay_orders` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付订单量',
    `total_pay_people` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付人数',
    `number_of_orders` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '下单人数',
    `repurchase_people` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '复购人数',
    `share_people` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '分享人数',
    `total_visitors` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '总访客数',
    `total_views` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '总浏览量',
    `new_customers` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '新客户，未支付过',
    `regular_customers` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '老客户，已支付过',
    `goods_listings` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品上架量',
    `goods_total_visitors` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品访客数',
    `goods_total_views` int(11) NOT NULL DEFAULT 0 COMMENT '商品浏览量',
    `new_customers_pay_peoples` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '新客户成交人数',
    `regular_customers_pay_peoples` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '老客户成交人数',
    `total_order_money` decimal(12, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '下单金额',
    `total_pay_money` decimal(12, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '支付金额',
    `repurchase_money` decimal(12, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '复购金额',
    `new_customers_pay_money` decimal(12, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '新客户成交金额',
    `regular_customers_pay_money` decimal(12, 2) UNSIGNED NOT NULL DEFAULT 0.00 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`(`mall_id`) USING BTREE
) ENGINE = InnoDB  CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '统计商城总数据';

CREATE TABLE `qimall_census_mall_analysis` (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `mall_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
    `user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
    `buy_numbers` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '购买次数',
    `total_pay_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '交易金额',
    `date` varchar(20) NOT NULL DEFAULT '' COMMENT '日期',
    `status` tinyint(4) 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,
    KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城价值分析数据';
CREATE TABLE `qimall_census_mall_goods` (
   `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
   `goods_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
   `total_views` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '浏览量',
   `total_sales_volume` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '销量',
   `total_pay_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '销售金额',
   `add_card_numbers` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '加购数量',
   `total_visitors` int(11) NOT NULL DEFAULT '0' COMMENT '访客数',
   `status` tinyint(4) 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,
   KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城商品数据';

CREATE TABLE `qimall_census_mall_purchasing_power` (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
    `purchasing_power` tinyint(3) NOT NULL DEFAULT '0' COMMENT '购买力',
    `numbers` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '数量',
    `status` tinyint(4) 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,
    KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城用户购买力';

CREATE TABLE `qimall_census_mall_region` (
     `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
     `province_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '省份id',
     `total_orders` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '订单量',
     `total_pay_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '交易金额',
     `status` tinyint(4) 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,
     KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城地区交易情况';

CREATE TABLE `qimall_census_mall_rf_analysis` (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
    `purchase_interval` tinyint(3) NOT NULL DEFAULT '0' COMMENT '购买间隔时间天',
    `f_users5` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '购买频率>=5客户数',
     `f_users4` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '购买频率=4客户数',
    `f_users3` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '购买频率=3客户数',
     `f_users2` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '购买频率=2客户数',
    `f_users1` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '购买频率=1客户数',
    `f_total_pay_money5` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '购买频率>=5累计支付金额',
    `f_total_pay_money4` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '购买频率=4累计支付金额',
    `f_total_pay_money3` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '购买频率=3累计支付金额',
    `f_total_pay_money2` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '购买频率=2累计支付金额',
    `f_total_pay_money1` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '购买频率=1累计支付金额',
    `status` tinyint(4) 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,
     KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城价值rf分析';

CREATE TABLE `qimall_census_mall_rm_analysis` (
     `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
     `purchase_interval` tinyint(3) NOT NULL DEFAULT '0' COMMENT '购买间隔时间天',
     `m_users1` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'M-消费金额>=2000客户数',
     `m_users2` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'M-消费金额1000<=M<2000客户数',
     `m_users3` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'M-消费金额500<=M<=1000客户数',
     `m_users4` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'M-消费金额200<=M<=500客户数',
     `m_users5` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'M-消费金额m<200客户数',
     `m_total_pay_money1` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'M-消费金额>=2000累计支付金额',
     `m_total_pay_money2` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'M-消费金额1000<=M<2000累计支付金额',
     `m_total_pay_money3` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'M-消费金额500<=M<=1000累计支付金额',
     `m_total_pay_money4` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'M-消费金额200<=M<=500累计支付金额',
     `m_total_pay_money5` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'M-消费金额m<200累计支付金额',
     `status` tinyint(4) 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,
     KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城价值rm分析';

CREATE TABLE `qimall_census_mall_single_day` (
   `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
   `add_users` int(11) NOT NULL DEFAULT '0' COMMENT '今日新增会员',
   `total_users` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计会员数',
   `total_visitors` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '今日访客数',
   `total_actives` int(11) NOT NULL DEFAULT '0' COMMENT '今日活跃数',
   `total_views` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '今日浏览量',
   `total_order_money` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '今日下单金额',
   `total_pay_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '今日交易金额',
   `date` varchar(20) NOT NULL DEFAULT '' COMMENT '日期',
   `status` tinyint(4) NOT 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,
   KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城单日数据';

CREATE TABLE `qimall_census_mall_single_hour` (
     `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
     `hour` varchar(5) NOT NULL DEFAULT '' COMMENT '小时',
     `date` varchar(20) NOT NULL DEFAULT '' COMMENT '日期',
     `total_pay_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '交易额',
     `status` tinyint(4) 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,
     KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城小时交易额';

CREATE TABLE `qimall_census_mall_user` (
     `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
     `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
     `total_views` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '浏览量',
     `total_pay_money` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '交易额',
     `status` tinyint(4) NOT 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,
     KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城用户浏览量';

CREATE TABLE `qimall_census_mall_user_level` (
     `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
     `level` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户等级',
     `numbers` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '数量',
     `status` tinyint(4) 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,
     KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城用户等级分布';

CREATE TABLE `qimall_census_mall_user_source` (
     `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
     `source` varchar(30) NOT NULL DEFAULT '' COMMENT '来源',
     `numbers` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '数量',
     `status` tinyint(4) 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,
     KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城用户来源';

SELECT @pid:=(select id from qimall_menu where url='mall/statistics/data-statistics' and level=1 and pid = 0 limit 1);
INSERT INTO qimall_menu
(title,app_id,cate_id,addons_name,is_addon,pid,url,icon,`level`,dev,sort,params,tree,`status`,created_at,updated_at) VALUES
('总裁驾驶窗','backend','2','','0',@pid,'mall/data-census/driving-window','','1','0','999','[]',CONCAT('tr_0 tr_',id),'1',UNIX_TIMESTAMP(now()),UNIX_TIMESTAMP(now()));

ALTER TABLE qimall_census_mall add total_follow_num int(10) not null default 0 COMMENT '商城关注数量';

alter table qimall_commission_log add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_commission_log add contributor_name varchar(255) not null default '' COMMENT '名称';
alter table qimall_commission_log add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_commission_log add contributor_id int(11) not null default 0 COMMENT '贡献者id';

alter table qimall_commission_log_1 add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_commission_log_1 add contributor_name varchar(255) not null default '' COMMENT '名称';
alter table qimall_commission_log_1 add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_commission_log_1 add contributor_id int(11) not null default 0 COMMENT '贡献者id';

alter table qimall_commission_log_2 add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_commission_log_2 add contributor_name varchar(255) not null default '' COMMENT '名称';
alter table qimall_commission_log_2 add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_commission_log_2 add contributor_id int(11) not null default 0 COMMENT '贡献者id';

alter table qimall_commission_log_3 add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_commission_log_3 add contributor_name varchar(255) not null default '' COMMENT '名称';
alter table qimall_commission_log_3 add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_commission_log_3 add contributor_id int(11) not null default 0 COMMENT '贡献者id';

alter table qimall_commission_log_4 add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_commission_log_4 add contributor_name varchar(255) not null default '' COMMENT '名称';
alter table qimall_commission_log_4 add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_commission_log_4 add contributor_id int(11) not null default 0 COMMENT '贡献者id';

alter table qimall_commission_frozen_log add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_commission_frozen_log add contributor_name varchar(255) not null default '' COMMENT '名称';

alter table qimall_commission_frozen_log add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_commission_frozen_log add contributor_id int(11) not null default 0 COMMENT '贡献者id';

alter table qimall_balance_frozen_log add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_balance_frozen_log add contributor_name varchar(255) not null default '' COMMENT '名称';
alter table qimall_balance_frozen_log add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_balance_frozen_log add contributor_id int(11) not null default 0 COMMENT '贡献者id';

alter table qimall_balance_log add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_balance_log add contributor_name varchar(255) not null default '' COMMENT '名称';
alter table qimall_balance_log add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_balance_log add contributor_id int(11) not null default 0 COMMENT '贡献者id';

alter table qimall_score_log add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_score_log add contributor_name varchar(255) not null default '' COMMENT '名称';
alter table qimall_score_log add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_score_log add contributor_id int(11) not null default 0 COMMENT '贡献者id';

alter table qimall_score_frozen_log add order_no varchar(100) not null default '' COMMENT '订单编号';
alter table qimall_score_frozen_log add contributor_name varchar(255) not null default '' COMMENT '名称';
alter table qimall_score_frozen_log add contributor_money decimal(12,2) not null default 0.00 COMMENT '分佣金额';
alter table qimall_score_frozen_log add contributor_id int(11) not null default 0 COMMENT '贡献者id';

ALTER TABLE qimall_commission_log add index idx_order_no (`order_no`);
ALTER TABLE qimall_commission_log_1 add index idx_order_no (`order_no`);
ALTER TABLE qimall_commission_log_2 add index idx_order_no (`order_no`);
ALTER TABLE qimall_commission_log_3 add index idx_order_no (`order_no`);
ALTER TABLE qimall_commission_log_4 add index idx_order_no (`order_no`);
ALTER TABLE qimall_commission_frozen_log add index idx_order_no (`order_no`);
ALTER TABLE qimall_balance_log add index idx_order_no (`order_no`);
ALTER TABLE qimall_balance_frozen_log add index idx_order_no (`order_no`);
ALTER TABLE qimall_score_log add index idx_order_no (`order_no`);
ALTER TABLE qimall_score_frozen_log add index idx_order_no (`order_no`);

ALTER TABLE `qimall_goods`
MODIFY COLUMN `freight_rules_type`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '运费规则 【1运费模板 2自定义运费】';
alter  table  qimall_goods add   `check_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '审核状态 0：待审核；1；：已通过；2：未通过';
alter  table  qimall_goods add   `check_remark` varchar(255) NOT NULL DEFAULT '' COMMENT '审核备注';

ALTER table qimall_order add `store_id` int(11) NOT NULL DEFAULT '0' COMMENT '门店id';
ALTER table qimall_order_detail add `store_id` int(11) NOT NULL DEFAULT '0' COMMENT '门店id';
ALTER table qimall_order_comments add `store_id` int(11) NOT NULL DEFAULT '0' COMMENT '门店id';

ALTER TABLE `qimall_user`
ADD COLUMN `area_code` varchar(20) NULL DEFAULT '' COMMENT '手机区号' AFTER `transaction_password`;

CREATE TABLE `qimall_enum_type` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '' COMMENT '标识',
  `title` varchar(50) DEFAULT '' COMMENT '中文标识',
  `type` varchar(50) DEFAULT '' COMMENT '类型',
  `is_addons` tinyint(2) DEFAULT '0' COMMENT '是否是插件[0:否,1:是]',
  `status` tinyint(2) DEFAULT '1' COMMENT '状态',
  `created_at` int(10) DEFAULT '0' COMMENT '创建时间',
  `updated_at` int(10) DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT INTO `qimall_global_var_map`(`name`, `title`, `type`, `is_addons`, `status`, `created_at`, `updated_at`) VALUES
('consume', '消费', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('recharge', '充值', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('transfer', '转送', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('refund', '退款', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('reward', '奖励', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('consume_order', '商城订单消费抵扣', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('consume_order_close', '订单关闭', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('consume_order_give', '订单支付赠送', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('refund_order', '订单退款', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('transfer_balance', '转入到余额', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('recharge_platform', '后台充值', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('balance_transfer', '余额转出', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('transfer_give', '转送', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('first', '直推奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('second', '间推奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('third', '分销奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('team', '渠道奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('extra', '额外奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('equal', '平级奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('over', '越级奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('withdrawal_balance_transfer', '提现余额转积分', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('withdrawal_commission_transfer', '提现佣金转积分', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('give', '赠送奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('deduct', '扣除', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('signin', '签到奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('user_coupon_give', '优惠券使用赠送', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('register', '注册', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('short_video_reward', '短视频观看奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('short_video_goods', '短视频观看奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('user_recharge', '会员充值', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('fill', '补货', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('bouns', '分红', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('attend_reward', '参团奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('win_reward', '拼中奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('recommend', '推荐奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('withdrawal', '提现', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('watch_reward', '观看奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('browse_reward', '浏览奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('withdrawal_refund', '提现被驳回', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('commission_transfer', '转入余额', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('boss_forever', '永久分红', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('boss_extra', '额外分红', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('direct_reward', '提现直推荐奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('indirect_reward', '提现间推荐奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('payment_for_goods_fill', '补货货款', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('payment_for_goods_buy', '会员买货货款', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('achievement_reward', '业绩奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('first_prize', '直推奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('invite_reward', '邀请奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('pool_reward', '奖金池奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888);


CREATE TABLE `qimall_wechat_transfer_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT '0',
  `out_trade_no` varchar(32) NOT NULL DEFAULT '',
  `transfer_data` text COMMENT '提交微信转账信息，json格式',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0',
  `updated_at` int(11) unsigned NOT NULL DEFAULT '0',
  `transfer_result` text COMMENT '接口返回信息',
  `batch_status` varchar(64) NOT NULL DEFAULT '' COMMENT '微信批次状态:WAIT_PAY-待付款，ACCEPTED-已受理，PROCESSING-转账中，FINISHED-已完成，CLOSED-已关闭',
  `detail_status` varchar(64) NOT NULL DEFAULT '' COMMENT '微信明细状态：PROCESSING-转账中，SUCCESS-转账成功，FAIL-转账失败',
  `transfer_detail` text COMMENT '明细详情',
  `cash_id` int(11) NOT NULL DEFAULT '0' COMMENT '提现表记录ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='微信商家转账到零钱记录表';



ALTER TABLE `qimall_wechat_material_article`
    ADD COLUMN `publish_status` tinyint NOT NULL DEFAULT 1 COMMENT '素材发布状态，0:成功, 1:发布中，2:原创失败, 3: 常规失败, 4:平台审核不通过, 5:成功后用户删除所有文章, 6: 成功后系统封禁所有文章' AFTER `status`,
    ADD COLUMN `publish_id` varchar(64) NOT NULL DEFAULT '' COMMENT '发布任务的id，如果需要调用发布状态轮询接口时需要用到' AFTER `publish_status`,
    ADD COLUMN `article_id` varchar(255) NOT NULL DEFAULT '' COMMENT '图文的 article_id，可用于“客服消息”场景' AFTER `publish_id`,
    ADD COLUMN `article_url` varchar(255) NOT NULL DEFAULT '' COMMENT '图文的永久链接' AFTER `article_id`,
    ADD COLUMN `article_index` int NOT NULL COMMENT '文章索引编号' AFTER `article_url`;


CREATE TABLE `qimall_census_mall_payment_single_month` (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
    `pay_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '支付方式',
    `total_pay_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '支付金额',
    `month` varchar(20) NOT NULL DEFAULT '' COMMENT '月份',
    `status` tinyint(4) NOT 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,
    KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城支付方式单月数据';

CREATE TABLE `qimall_census_mall_single_month` (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
    `total_order_money` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '下单金额',
    `total_pay_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '交易金额',
    `total_refund_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '退款金额',
    `total_deduction_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '抵扣金额',
    `total_recharge_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '充值金额',
    `month` varchar(20) NOT NULL DEFAULT '' COMMENT '月份',
    `status` tinyint(4) NOT 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,
    KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城单月数据';


ALTER TABLE  qimall_user_import_log add `upload_file` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '上传文件存在路径';




CREATE TABLE `qimall_census_mall_payment_single_month` (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
    `pay_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '支付方式',
    `total_pay_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '支付金额',
    `month` varchar(20) NOT NULL DEFAULT '' COMMENT '月份',
    `status` tinyint(4) NOT 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,
    KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城支付方式单月数据';

CREATE TABLE `qimall_census_mall_single_month` (
   `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
   `total_order_money` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '下单金额',
   `total_pay_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '交易金额',
   `total_refund_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '退款金额',
   `total_deduction_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '抵扣金额',
   `total_recharge_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '充值金额',
   `month` varchar(20) NOT NULL DEFAULT '' COMMENT '月份',
   `status` tinyint(4) NOT 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,
   KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城单月数据';


ALTER TABLE  qimall_census_mall_single_day add `add_goods` int(11) NOT NULL DEFAULT '0' COMMENT '今日新增商品数';
ALTER TABLE  qimall_census_mall_single_day add `add_orders` int(11) NOT NULL DEFAULT '0' COMMENT '今日新增订单数';
ALTER TABLE  qimall_census_mall_single_day add `add_pay_orders` int(11) NOT NULL DEFAULT '0' COMMENT '今日新增支付订单数';
ALTER TABLE  qimall_census_mall_single_day add `add_pay_nums` int(11) NOT NULL DEFAULT '0' COMMENT '今日新增支付件数';
ALTER TABLE  qimall_census_mall_single_day add `add_pay_people_nums` int(11) NOT NULL DEFAULT '0' COMMENT '今日新增支付人次';
ALTER TABLE  qimall_census_mall_single_day add `add_refund_orders` int(11) NOT NULL DEFAULT '0' COMMENT '今日新增售后订单数';
ALTER TABLE  qimall_census_mall_single_day add `settlement_commission`  DECIMAL(12,2) NOT NULL DEFAULT '0' COMMENT '已结算佣金';
ALTER TABLE  qimall_census_mall_single_day add `expenditure_commission`  DECIMAL(12,2) NOT NULL DEFAULT '0' COMMENT '佣金支出';
ALTER TABLE  qimall_census_mall_single_hour add `add_pay_orders` int(11) NOT NULL DEFAULT '0' COMMENT '支付订单数';
ALTER TABLE  qimall_census_mall_single_hour add `add_pay_nums` int(11) NOT NULL DEFAULT '0' COMMENT '支付件数';
ALTER TABLE  qimall_census_mall_single_hour add `add_pay_people_nums` int(11) NOT NULL DEFAULT '0' COMMENT '支付人次';
ALTER TABLE  qimall_census_mall_user add `add_pay_nums` int(11) NOT NULL DEFAULT '0' COMMENT '支付件数';
ALTER TABLE  qimall_census_mall_user add `add_orders` int(11) NOT NULL DEFAULT '0' COMMENT '订单数';
ALTER TABLE  qimall_census_mall_user add `add_order_money` DECIMAL(12,2) NOT NULL DEFAULT '0' COMMENT '订单金额';
ALTER TABLE  qimall_census_mall_user add `add_pay_orders` int(11) NOT NULL DEFAULT '0' COMMENT '支付订单数';
ALTER TABLE  qimall_census_mall_user add `add_sale_orders` int(11) NOT NULL DEFAULT '0' COMMENT '完成订单数';
ALTER TABLE  qimall_census_mall_user add `add_sale_money`  DECIMAL(12,2) NOT NULL DEFAULT '0' COMMENT '完成订单金额';

CREATE TABLE `qimall_census_mall_single_day_capital` (
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
    `type` varchar(255) NOT NULL DEFAULT '0' COMMENT '类型',
    `total_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '累计数量',
    `use_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '使用数量',
    `surplus_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '剩余数量',
    `withdrawal_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '提现数量',
    `service_charge` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '手续费',
    `cancel_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '无效数量',
    `unsettled` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '待结算数量',
    `date` varchar(20) NOT NULL DEFAULT '' COMMENT '日期',
    `status` tinyint(4) NOT 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,
    KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城单日资金数据';


CREATE TABLE `qimall_census_mall_capital` (
   `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
   `type` varchar(255) NOT NULL DEFAULT '0' COMMENT '类型',
   `total_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '累计数量',
   `use_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '使用数量',
   `surplus_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '剩余数量',
   `withdrawal_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '提现数量',
   `service_charge` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '手续费',
   `cancel_nums` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '无效数量',
   `unsettled` decimal(14,2) unsigned NOT NULL DEFAULT '0' COMMENT '待结算数量',
   `status` tinyint(4) NOT 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,
   KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城资金数据';

CREATE TABLE `qimall_census_mall_user_level_single_day` (
   `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `mall_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商城ID',
   `level` int(10)  NOT NULL DEFAULT '0' COMMENT '用户等级',
   `numbers` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '数量',
  `date` varchar(20) NOT NULL DEFAULT '' COMMENT '日期',
   `status` tinyint(4) 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,
   KEY `mall_id` (`mall_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='统计商城单日用户等级分布';


ALTER TABLE  qimall_census_mall add `can_use_commission`  DECIMAL(14,2) NOT NULL DEFAULT '0' COMMENT '待提现佣金';
ALTER TABLE  qimall_census_mall add `can_use_balance`  DECIMAL(14,2) NOT NULL DEFAULT '0' COMMENT '未使用余额';
ALTER TABLE  qimall_census_mall add `can_use_score`  DECIMAL(14,2) NOT NULL DEFAULT '0' COMMENT '未使用积分';


ALTER TABLE  qimall_census_mall add `add_pay_nums` int(11) NOT NULL DEFAULT '0' COMMENT '支付件数';
ALTER TABLE  qimall_census_mall add `add_goods` int(11) NOT NULL DEFAULT '0' COMMENT '新增商品数';







ALTER TABLE  qimall_user_import_log add `upload_file` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '上传文件存在路径';




ALTER TABLE `qimall_poster_code`
MODIFY COLUMN `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '二维码参数' AFTER `code`;

ALTER TABLE `qimall_payment_refund`
ADD COLUMN `trade_order_no`  varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '交易流水订单号';



ALTER TABLE `qimall_payment_refund`
ADD COLUMN `trade_order_no`  varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '交易流水订单号';



ALTER TABLE `qimall_order_express_log`
  ADD COLUMN `table_express_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '物流状态0/未完成 1/已完成',
  ADD COLUMN `express_status` tinyint(2) NOT NULL DEFAULT '9' COMMENT '当前物流状态(物流公司状态)',
  MODIFY COLUMN `status` tinyint(5) NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  ADD KEY `idx_table_exp_status_idx_mall_id` (`mall_id`,`table_express_status`);


ALTER TABLE qimall_user_level_upgrade_condition add `cate_ids` varchar(3000) NOT NULL DEFAULT '' COMMENT '商品分类id集合';
ALTER TABLE qimall_user_level_upgrade_condition add `buy_cate_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '订单完成0；支付完成：1';
ALTER TABLE qimall_order_marketing add `give_score_settlement` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '赠送积分的结算方式';


CREATE TABLE `qimall_global_var_map` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '' COMMENT '标识',
  `title` varchar(50) DEFAULT '' COMMENT '中文标识',
  `type` varchar(50) DEFAULT '' COMMENT '类型',
  `is_addons` tinyint(2) DEFAULT '0' COMMENT '是否是插件[0:否,1:是]',
  `status` tinyint(2) DEFAULT '1' COMMENT '状态',
  `created_at` int(10) DEFAULT '0' COMMENT '创建时间',
  `updated_at` int(10) DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT INTO `qimall_global_var_map`(`name`, `title`, `type`, `is_addons`, `status`, `created_at`, `updated_at`) VALUES
('consume', '消费', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('recharge', '充值', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('transfer', '转送', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('refund', '退款', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('reward', '奖励', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('consume_order', '商城订单消费抵扣', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('consume_order_close', '订单关闭', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('consume_order_give', '订单支付赠送', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('refund_order', '订单退款', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('transfer_balance', '转入到余额', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('recharge_platform', '后台充值', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('balance_transfer', '余额转出', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('transfer_give', '转送', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('first', '直推奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('second', '间推奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('third', '分销奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('team', '渠道奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('extra', '额外奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('equal', '平级奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('over', '越级奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('withdrawal_balance_transfer', '提现余额转积分', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('withdrawal_commission_transfer', '提现佣金转积分', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('give', '赠送奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('deduct', '扣除', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('signin', '签到奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('user_coupon_give', '优惠券使用赠送', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('register', '注册', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('short_video_reward', '短视频观看奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('short_video_goods', '短视频观看奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('user_recharge', '会员充值', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('fill', '补货', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('bouns', '分红', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('attend_reward', '参团奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('win_reward', '拼中奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('recommend', '推荐奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('withdrawal', '提现', 'FromTypeMap', 0, 1, 1654569888, 1654569888),
('watch_reward', '观看奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('browse_reward', '浏览奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('withdrawal_refund', '提现被驳回', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('commission_transfer', '转入余额', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('boss_forever', '永久分红', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('boss_extra', '额外分红', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('direct_reward', '提现直推荐奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('indirect_reward', '提现间推荐奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('payment_for_goods_fill', '补货货款', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('payment_for_goods_buy', '会员买货货款', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('achievement_reward', '业绩奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('first_prize', '直推奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('invite_reward', '邀请奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888),
('pool_reward', '奖金池奖励', 'CapitalTypeMap', 0, 1, 1654569888, 1654569888);


INSERT INTO `qimall_express`(`id`, `name`, `code`, `status`, `created_at`, `updated_at`) VALUES (1024, '极兔快递', 'JTSD', 1, 1631687277, 1631687277);

ALTER TABLE `qimall_order_batch_delivery`
MODIFY COLUMN `delivery_file` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '批量发货文件地址' AFTER `operator_name`;


INSERT INTO `qimall_global_var_map`(`name`, `title`, `type`, `created_at`, `updated_at`) VALUES ('withdrawal_decr', '提现', 'CapitalTypeMap', 1661841988, 1661841988);


DELETE FROM `qimall_town` WHERE `district_id` = 2953;
INSERT INTO `qimall_town`(`district_id`, `adcode`, `name`, `created_at`, `updated_at`) VALUES
(2953, 620103, '白银路街道', 1594557804, 1594557804),
(2953, 620103, '草场街街道', 1594557804, 1594557804),
(2953, 620103, '东岗街道', 1594557804, 1594557804),
(2953, 620103, '东岗西路街道', 1594557804, 1594557804),
(2953, 620103, '伏龙坪街道', 1594557804, 1594557804),
(2953, 620103, '皋兰路街道', 1594557804, 1594557804),
(2953, 620103, '高新区', 1594557804, 1594557804),
(2953, 620103, '拱星墩街道', 1594557804, 1594557804),
(2953, 620103, '广武门街道', 1594557804, 1594557804),
(2953, 620103, '火车站街道', 1594557804, 1594557804),
(2953, 620103, '嘉峪关路街道', 1594557804, 1594557804),
(2953, 620103, '焦家湾街道', 1594557804, 1594557804),
(2953, 620103, '靖远路街道', 1594557804, 1594557804),
(2953, 620103, '酒泉路街道', 1594557804, 1594557804),
(2953, 620103, '临夏路街道', 1594557804, 1594557804),
(2953, 620103, '青白石街道', 1594557804, 1594557804),
(2953, 620103, '铁路东村街道', 1594557804, 1594557804),
(2953, 620103, '铁路西村街道', 1594557804, 1594557804),
(2953, 620103, '团结新村街道', 1594557804, 1594557804),
(2953, 620103, '渭源路街道', 1594557804, 1594557804),
(2953, 620103, '五泉街道', 1594557804, 1594557804),
(2953, 620103, '雁北街道', 1594557804, 1594557804),
(2953, 620103, '盐场路街道', 1594557804, 1594557804),
(2953, 620103, '雁南街道', 1594557804, 1594557804),
(2953, 620103, '张掖路街道', 1594557804, 1594557804);


ALTER TABLE `qimall_user_team_statistics`
MODIFY COLUMN `user_team_order_price_total` decimal(12, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '团队会员订单总额(不包括本人)' AFTER `user_indirect_order_total`,
MODIFY COLUMN `user_direct_order_price_total` decimal(12, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '直推会员订单总额(jxmall_user_children.level=1)' AFTER `user_team_order_price_total`,
MODIFY COLUMN `user_indirect_order_price_total` decimal(12, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '间推会员订单总额(jxmall_user_children.level=2)' AFTER `user_direct_order_price_total`;

ALTER TABLE `qimall_wechat_reply_rule`
MODIFY COLUMN `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;


ALTER TABLE `qimall_user_team_statistics`
ADD COLUMN `team_finish_order_total` int(10) NULL DEFAULT 0 COMMENT '团队完成订单数量(不包括本人)',
ADD COLUMN `team_finish_order_price_total` decimal(12,2) NULL DEFAULT 0.00 COMMENT '团队完成订单金额(不包括本人)';


ALTER TABLE qimall_mall add is_show_copyright tinyint(1) not null default 0 comment '是否显示版权：1；显示；0不显示';


ALTER TABLE qimall_free_shipping_rules add `select_com` tinyint(1) DEFAULT '0' COMMENT '选择地区组件[0=多选地区，1=单选地区]';


INSERT INTO `qimall_diy_components`(`code`, `title`, `group`, `icon`, `count`, `is_top`, `permission`, `sort`, `source`, `status`, `created_at`, `updated_at`) VALUES
('best-sellers', '全城热卖', 'store', '', 999, 0, '{\"is_drag\":\"true\",\"is_up\":\"true\",\"id_down\":\"true\",\"is_copy\":\"true\"}', 999, 'Store', 1, 1652929474, 1652929474),
('img-button-group', '瓷片区', 'img_text', '', 999, 0, '{\"is_drag\":\"true\",\"is_up\":\"true\",\"id_down\":\"true\",\"is_copy\":\"true\"}', 999, '', 1, 1634538238, 1634538238),
('store-commodity-classify-list', '商品分类', 'store', '', 999, 0, '{\"is_drag\":\"true\",\"is_up\":\"true\",\"id_down\":\"true\",\"is_copy\":\"true\"}', 999, 'Store', 1, 1634538238, 1634538238),
('shop-cate-recommend', '爆款商品聚合页', 'store', '', 999, 0, '{\"is_drag\":\"true\",\"is_up\":\"true\",\"id_down\":\"true\",\"is_copy\":\"true\"}', 999, 'Store', 1, 1634538238, 1634538238),
('store-prioritizing', '小白优选', 'store', '', 999, 0, '{\"is_drag\":\"true\",\"is_up\":\"true\",\"id_down\":\"true\",\"is_copy\":\"true\"}', 999, 'Store', 1, 1634538238, 1634538238),
('store-cate-recommend', '门店聚合页', 'store', '', 999, 0, '{\"is_drag\":\"true\",\"is_up\":\"true\",\"id_down\":\"true\",\"is_copy\":\"true\"}', 999, 'Store', 1, 1634538238, 1634538238),
('store', '推荐商家', 'store', '', 999, 0, '{\"is_drag\":\"true\",\"is_up\":\"true\",\"id_down\":\"true\",\"is_copy\":\"true\"}', 999, 'Store', 1, 1634538238, 1634538238);


CREATE TABLE `qimall_supply_chain_goods_pic` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `mall_id` int(10) NOT NULL COMMENT '商城ID',
    `goods_id` int(10) NOT NULL COMMENT '商品ID',
    `chain_url` varchar(255) NOT NULL COMMENT '供应链商品主图',
    `url` varchar(255) NOT NULL COMMENT '商品主图',
    `updated_at` int(10) DEFAULT NULL,
    `created_at` int(10) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;