qimall/addons/AvoidTax/install.sql

CREATE TABLE `qimall_addons_tax_account`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0,
  `external_agreement_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '代扣协议中标示用户的唯一签约号（确保在商户系统中唯一）。	',
  `agreement_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '支付宝系统中用以唯一标识用户签约记录的编号。',
  `alipay_logon_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '返回脱敏的支付宝账号，如需要返回不脱敏的支付宝用户账号，需要用户在签约页面上授权',
  `personal_product_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '协议产品码，商户和支付宝签约时确定，不同业务场景对应不同的签约产品码。	',
  `valid_time` int(10) NULL DEFAULT 0 COMMENT '用户代扣协议的实际生效时间',
  `sign_time` int(10) NULL DEFAULT 0 COMMENT '支付宝代扣协议的实际签约时间',
  `is_default` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否默认',
  `status` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '协议当前状态\r\n1. TEMP：暂存，协议未生效过；\r\n2. NORMAL：正常；\r\n3. STOP：暂停',
  `sign_scene` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '签约协议的场景。	',
  `third_party_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '签约第三方主体类型。对于三方协议，表示当前用户和哪一类的第三方主体进行签约。\r\n取值范围：\r\n1. PARTNER（平台商户）;\r\n2. MERCHANT（集团商户），集团下子商户可共享用户签约内容;\r\n默认为PARTNER。',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

CREATE TABLE `qimall_addons_tax_account_book`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id` int(10) NOT NULL DEFAULT 0 COMMENT '账户ID',
  `merchant_user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `account_book_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `ext_card_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `created_at` int(10) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

CREATE TABLE `qimall_addons_tax_config`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0 COMMENT 'mall_id',
  `app_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'app_id',
  `private_key` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用私钥',
  `app_public_key` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '应用公钥证书',
  `alipay_public_key` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付宝公钥证书',
  `alipay_root` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付宝根证书',
  `use_scene` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '使用场景1商城提现2o2o提现',
  `created_at` int(11) NULL DEFAULT NULL,
  `updated_at` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '支付宝配置表' ROW_FORMAT = DYNAMIC;

CREATE TABLE `qimall_addons_tax_order`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `account_id` int(10) NULL DEFAULT 0 COMMENT '记账方ID',
  `withdraw_id` int(11) NULL DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '标题',
  `batch_num` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '批次号',
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '汇款方名称',
  `account_num` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '汇款方账户',
  `amount` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '汇款金额',
  `remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `type` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '汇款订单类型 1默认 2商城提现',
  `account_type` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 打款 2 出款',
  `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 未打款 1 已打款记帐本 2 打款成功',
  `error` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '错误信息',
  `updated_at` int(10) NULL DEFAULT 0,
  `created_at` int(10) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '交易列表' ROW_FORMAT = DYNAMIC;

CREATE TABLE `qimall_addons_tax_order_book`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) NULL DEFAULT 0,
  `order_id` int(10) NULL DEFAULT 0,
  `out_biz_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '订单号',
  `identity` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '记账本',
  `agreement_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '支付宝系统中用以唯一标识用户签约记录的编号',
  `amount` decimal(12, 2) UNSIGNED NULL DEFAULT 0.00,
  `created_at` int(10) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '记帐本充值记录' ROW_FORMAT = DYNAMIC;

CREATE TABLE `qimall_addons_tax_order_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_sn` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `updated_at` datetime,
  `created_at` datetime,
  `msg` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_tax_user_auth`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `accountId` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mall_id` int(11) NULL DEFAULT NULL,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `id_card_no` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证',
  `mobile` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号码',
  `status` tinyint(4) NULL DEFAULT 1 COMMENT '1未签约 2签约中 3签约成功',
  `id_card_front` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '身份证正面',
  `id_card_back` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '身份证反面',
  `auth_url` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '实名认证连接',
  `created_at` int(11) NULL DEFAULT NULL,
  `updated_at` int(11) NULL DEFAULT NULL,
  `sign_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '签约响应',
  `remark` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '签约错误信息',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户实名认证表' ROW_FORMAT = Dynamic;

CREATE TABLE `qimall_addons_tax_user_auth_liepin`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `work_user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mall_id` int(11) NULL DEFAULT NULL,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `id_card_no` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证',
  `mobile` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号码',
  `status` tinyint(4) NULL DEFAULT NULL COMMENT '1未认证2已认证',
  `id_card_front` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '身份证正面',
  `id_card_back` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '身份证反面',
  `auth_url` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '实名认证连接',
  `created_at` int(11) NULL DEFAULT NULL,
  `updated_at` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户实名认证表' ROW_FORMAT = Dynamic;

ALTER TABLE `qimall_addons_tax_account_book` ADD COLUMN `mall_id` int(11) NULL DEFAULT NULL AFTER `account_id`;

ALTER TABLE `qimall_addons_tax_account_book` ADD COLUMN `is_default` tinyint(1) NULL DEFAULT 0 COMMENT '是否默认 0 1' AFTER `mall_id`;

ALTER TABLE `qimall_addons_tax_account_book` ADD COLUMN `agreement_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL AFTER `created_at`;

ALTER TABLE `qimall_addons_tax_config` ADD COLUMN `ali_config` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '支付宝配置' AFTER `updated_at`;

ALTER TABLE `qimall_addons_tax_config` ADD COLUMN `tax_config` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '税务方配置' AFTER `ali_config`;

ALTER TABLE `qimall_addons_tax_order` DROP COLUMN `account_num`;

ALTER TABLE `qimall_addons_tax_order` ADD COLUMN `user_id` int(11) NULL DEFAULT NULL AFTER `account_id`;

ALTER TABLE `qimall_addons_tax_order` ADD COLUMN `order_sn` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单号' AFTER `batch_num`;

ALTER TABLE `qimall_addons_tax_order` MODIFY COLUMN `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '收款名称' AFTER `order_sn`;

ALTER TABLE `qimall_addons_tax_order` ADD COLUMN `mobile` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '收款账户' AFTER `name`;

ALTER TABLE `qimall_addons_tax_order` MODIFY COLUMN `amount` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '金额' AFTER `mobile`;

ALTER TABLE `qimall_addons_tax_order` ADD COLUMN `pay_fee` decimal(12, 2) NULL DEFAULT NULL COMMENT '手续费' AFTER `amount`;

ALTER TABLE `qimall_addons_tax_order` MODIFY COLUMN `type` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '汇款订单类型 1手动打款 2商城提现' AFTER `remarks`;

ALTER TABLE `qimall_addons_tax_order` ADD COLUMN `sub_type` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1用户订单2税务方订单' AFTER `type`;

ALTER TABLE `qimall_addons_tax_order` MODIFY COLUMN `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '-1打款失败 1 待提交审核 2 已提交 税务方审核中 3 税务方审核成功，待打款 4 已从企业支付宝扣款至记账本(待出款) 5已从记账本出款(完成)' AFTER `account_type`;

ALTER TABLE `qimall_addons_tax_order` ADD COLUMN `job_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL AFTER `status`;

ALTER TABLE `qimall_addons_tax_order` ADD COLUMN `job_detail_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL AFTER `job_id`;

ALTER TABLE `qimall_addons_tax_config` ADD COLUMN `settle` tinyint(2) NOT NULL DEFAULT 1 COMMENT '1支付宝结算 2银行卡结算' AFTER `tax_config`;