qimall/addons/JoinPay/install.sql

-- ----------------------------
-- Table structure for qimall_addons_joinpay_fastpay_order
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_joinpay_fastpay_order`;
CREATE TABLE `qimall_addons_joinpay_fastpay_order`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '平台id',
  `user_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
  `order_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '订单号，系统生成',
  `pay_fee` decimal(12, 2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
  `bank_card_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '开通快捷支付银行卡id',
  `title` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '订单title',
  `jp_order_no` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '汇聚平台流水号',
  `is_sign` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否签约，1-是，0-否',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '添加时间戳',
  `updated_at` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间戳',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_order_no`(`order_no`) USING BTREE COMMENT '订单号唯一'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '汇聚支付-快捷支付签约订单表';

-- ----------------------------
-- Table structure for qimall_addons_joinpay_user_bankcard
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_joinpay_user_bankcard`;
CREATE TABLE `qimall_addons_joinpay_user_bankcard`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) UNSIGNED NOT NULL COMMENT '商城id',
  `user_id` int(10) UNSIGNED NOT NULL COMMENT '用户id',
  `card_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '银行卡id,qimall_user_bank_card 表的id',
  `payer_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '持卡人姓名',
  `id_type` tinyint(3) NOT NULL DEFAULT 1 COMMENT '持卡人证件类型：1-身份证，2-军官证，3-士兵证，4-护照等',
  `id_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '持卡人证件号码',
  `bank_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '银行名称',
  `bank_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '银行简码',
  `bank_card_no` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '银行卡号',
  `mobile_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '银行卡绑定的手机号',
  `expire_date` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '信用卡有效期，格式：yy/mm格式',
  `cvv` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '信用卡cvv安全码',
  `sign_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '快捷支付-签约id',
  `is_sign` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否开通快捷支付',
  `card_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '银行卡类型，dc->储蓄卡,cc->信用卡',
  `card_type_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '银行卡类型名称',
  `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '状态[-1:删除;0:禁用;1启用]',
  `created_at` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '添加时间戳',
  `updated_at` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间戳',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_mall_user_card`(`mall_id`, `user_id`, `card_id`) USING BTREE,
  INDEX `idx_card_no`(`bank_card_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '会员汇聚支付银行卡信息记录表';

-- 更新汇聚代付订单状态 定时任务
INSERT INTO `qimall_crontab_task` (`name`, `command`, `type`, `rule`, `is_warning`, `status`, `created_at`, `updated_at`) VALUES ('更新汇聚代付订单状态', 'join-pay/joinpay-crontab/deal-pay-log', 2, '*/10 * * * * *', 0, 1, 1645609867, 1645609867);