qimall/addons/QiJuhe/install.sql

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_category
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_category`;
CREATE TABLE `qimall_addons_qijuhe_category`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supply_id` int(11) NULL DEFAULT 0 COMMENT '供应商id',
  `category_id` int(11) NULL DEFAULT 0 COMMENT '分类ID',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '分类名称',
  `sort` int(11) NULL DEFAULT 0 COMMENT '排序',
  `level` tinyint(4) NULL DEFAULT 0 COMMENT '分类层级，从0开始',
  `desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `is_display` tinyint(2) NULL DEFAULT 1 COMMENT '1, 状态 1启用 0关闭',
  `image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `icon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `parent_id` int(11) NULL DEFAULT 0 COMMENT '分类父id（对应三方分类id父id）',
  `source` int(11) NULL DEFAULT 0 COMMENT '来源1自营2京东6阿里巴巴7天猫',
  `created_at` int(11) NULL DEFAULT 0,
  `updated_at` int(11) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_title`(`name`) USING BTREE,
  INDEX `index_source`(`source`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '分类表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_express
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_express`;
CREATE TABLE `qimall_addons_qijuhe_express`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `express_id` int(10) NOT NULL DEFAULT 0,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of qimall_addons_qijuhe_express
-- ----------------------------
INSERT INTO `qimall_addons_qijuhe_express` VALUES (1, 1, '顺丰', 'SFEXPRESS');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (2, 2, '申通', 'STO');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (3, 3, '韵达快递', 'YUNDA');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (4, 3, '韵达快运', 'YUNDA56');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (5, 4, '圆通', 'YTO');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (6, 5, '中通', 'ZTO');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (7, 5, '中通快运', 'ZTO56');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (8, 6, '百世快递', 'HTKY');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (10, 7, 'EMS', 'EMS');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (11, 8, '天天快递', 'TTKDEX');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (12, 9, '邮政包裹', 'CHINAPOST');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (13, 10, '宅急送', 'ZJS');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (14, 11, '国通', 'GTO');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (15, 12, '全峰', 'QFKD');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (16, 13, '优速', 'UC56');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (17, 14, '中铁快运', 'CRE');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (18, 15, '中铁物流', 'ZTKY');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (19, 16, '亚马逊', 'AMAZON');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (20, 17, '城际快递', 'CJKD');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (21, 18, '德邦', 'DEPPON');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (22, 19, '汇丰物流', 'HFWL');
INSERT INTO `qimall_addons_qijuhe_express` VALUES (24, 20, '百世快运', 'BSKY');

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_goods
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_goods`;
CREATE TABLE `qimall_addons_qijuhe_goods`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NULL DEFAULT 0,
  `supply_id` int(11) NULL DEFAULT 0,
  `mall_goods_id` int(11) NULL DEFAULT 0 COMMENT '商城的id',
  `goods_id` int(11) NULL DEFAULT 0 COMMENT '中台商品id',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '商品名称',
  `image_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '商品主图',
  `source` tinyint(4) NULL DEFAULT 0 COMMENT '来源 0中台 1云仓（聚合供应链） 2京东 6阿里 7天猫',
  `agreement_price` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '协议价',
  `guide_price` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '指导价',
  `activity_price` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '营销价',
  `market_price` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '市场价',
  `sale_price` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '销售价',
  `cost_price` decimal(12, 2) NULL DEFAULT 0.00 COMMENT '成本价',
  `stock` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
  `sales` int(11) NOT NULL DEFAULT 0 COMMENT '销量',
  `sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '商品编号',
  `is_display` tinyint(4) NULL DEFAULT 0 COMMENT '上下架 0下1上',
  `desc` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '商品描述',
  `unit` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品单位',
  `barcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品条码',
  `gallery` varchar(2555) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品组图json',
  `detail_images` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '商品详情',
  `attrs` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '商品属性json',
  `skus` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '商品规格json',
  `brand` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '商品品牌json',
  `category_1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `category_2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `category_3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_order
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_order`;
CREATE TABLE `qimall_addons_qijuhe_order`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) NOT NULL DEFAULT 0,
  `supply_id` int(10) NOT NULL DEFAULT 0 COMMENT '中台ID',
  `order_id` int(10) NOT NULL DEFAULT 0 COMMENT '订单ID',
  `user_id` int(10) NOT NULL DEFAULT 0 COMMENT '用户ID',
  `order_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '商城订单号',
  `supply_order_sn` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '聚合订单号',
  `goods_id` int(10) NOT NULL DEFAULT 0 COMMENT '商品ID',
  `sku_id` int(10) NOT NULL DEFAULT 0 COMMENT '聚合商品SKUID',
  `source` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '来源',
  `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '1未推单 2推单中 3推单成功(支付成功) 4推单失败(支付失败) 5已退单',
  `push_num` int(10) NOT NULL DEFAULT 0 COMMENT '推单次数',
  `order_data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '订单信息',
  `error` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '失败信息',
  `created_at` int(10) NULL DEFAULT 0,
  `updated_at` int(10) NULL DEFAULT 0,
  `is_refund` int(10) NULL DEFAULT 0 COMMENT '是否系统退款 1未退 2已退 只有退单该字段才有用',
  `refund_id` int(10) NULL DEFAULT 0 COMMENT '售后ID',
  `refund_money` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '退款金额',
  `err_id` int(10) NULL DEFAULT 0 COMMENT '推单失败的错误ID,重新发起的时候需要',
  `order_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_mall_id`(`mall_id`) USING BTREE,
  INDEX `index_order_no`(`order_no`) USING BTREE,
  INDEX `index_juhe_order_sn`(`supply_order_sn`) USING BTREE,
  INDEX `index_order_id`(`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '聚合订单表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_setting
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_setting`;
CREATE TABLE `qimall_addons_qijuhe_setting`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) NOT NULL DEFAULT 0,
  `setting` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `token` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT 'apitoken',
  `expire_time` int(11) NULL DEFAULT 0 COMMENT '过期时间',
  `created_at` int(10) NULL DEFAULT 0,
  `updated_at` int(10) NULL DEFAULT 0,
  `is_delete` int(10) NULL DEFAULT 0,
  `deleted_at` int(255) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_mall_id`(`mall_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '聚合供应链设置表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_setting_log
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_setting_log`;
CREATE TABLE `qimall_addons_qijuhe_setting_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL,
  `before_setting` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '修改前设置',
  `created_at` int(10) NULL DEFAULT 0,
  `updated_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;

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_storage
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_storage`;
CREATE TABLE `qimall_addons_qijuhe_storage`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NULL DEFAULT 0,
  `supply_id` int(11) NULL DEFAULT 0,
  `goods_id` int(11) NULL DEFAULT 0 COMMENT 'addons_qijuhe_goods的id',
  `middle_goods_id` int(11) NULL DEFAULT 0 COMMENT '中台的商品id',
  `mall_goods_id` int(11) NULL DEFAULT 0 COMMENT '对应的系统商品id',
  `batch_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `is_delete` tinyint(4) NULL DEFAULT 0,
  `created_at` int(11) NULL DEFAULT 0,
  `updated_at` int(11) NULL DEFAULT 0,
  `is_import` tinyint(4) NULL DEFAULT 0 COMMENT '是否添加到了系统商品库 1导入中 2导入成功',
  `import_at` int(11) NULL DEFAULT 0 COMMENT '导入时间',
  `import_msg` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '导入错误信息',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_mall_id`(`mall_id`) USING BTREE,
  INDEX `index_goods_id`(`middle_goods_id`) USING BTREE,
  INDEX `index_mall_goods_id`(`mall_goods_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '选品库表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_supplys
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_supplys`;
CREATE TABLE `qimall_addons_qijuhe_supplys`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NOT NULL DEFAULT 0,
  `app_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '供应链名称',
  `app_key` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'appKey',
  `app_secret` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'appSecret',
  `token` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'toekn',
  `expire_time` int(11) NULL DEFAULT 0 COMMENT '过期时间',
  `app_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '供应链地址',
  `sell` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '售价策略',
  `cost` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '成本价策略',
  `original` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '原计策略',
  `updated_at` int(10) NOT NULL DEFAULT 0,
  `created_at` int(10) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_mall_id`(`mall_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_notice
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_notice`;
CREATE TABLE `qimall_addons_qijuhe_notice`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mall_id` int(11) NULL DEFAULT 0,
  `supply_id` int(11) NULL DEFAULT 0,
  `notice_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '通知id',
  `type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '类型',
  `push_time` int(11) NULL DEFAULT 0 COMMENT '推送时间',
  `data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '数据',
  `created_at` int(11) NULL DEFAULT 0,
  `updated_at` int(11) NULL DEFAULT 0,
  `response` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '响应',
  `response_time` int(11) NULL DEFAULT 0 COMMENT '响应时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `mall_id`(`mall_id`, `notice_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '通知记录表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_goods_attr
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_goods_attr`;
CREATE TABLE `qimall_addons_qijuhe_goods_attr`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) NOT NULL DEFAULT 0,
  `supply_id` int(10) NOT NULL DEFAULT 0 COMMENT '中台ID',
  `sku_id` int(10) NOT NULL DEFAULT 0 COMMENT '商品线上SKU',
  `goods_id` int(10) NOT NULL DEFAULT 0 COMMENT '商品表商品ID',
  `supply_goods_id` int(10) NOT NULL DEFAULT 0 COMMENT '中台商品表ID',
  `attr_id` int(10) NOT NULL DEFAULT 0 COMMENT '商品属性表ID',
  `is_delete` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_sku_id`(`sku_id`) USING BTREE,
  INDEX `index_goods_id`(`goods_id`) USING BTREE,
  INDEX `index_attr_id`(`attr_id`) USING BTREE,
  INDEX `index_juhe_goods_id`(`supply_goods_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '商品属性和线上SKU关联表' ROW_FORMAT = DYNAMIC;

ALTER TABLE `qimall_addons_qijuhe_supplys`
ADD COLUMN `app_host` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'appHost' AFTER `app_name`;

-- ----------------------------
-- Table structure for qimall_addons_qijuhe_order_refund
-- ----------------------------
DROP TABLE IF EXISTS `qimall_addons_qijuhe_order_refund`;
CREATE TABLE `qimall_addons_qijuhe_order_refund` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mall_id` int(10) unsigned DEFAULT '0',
  `supply_id` int(10) DEFAULT NULL COMMENT '中台ID',
  `order_id` int(10) unsigned DEFAULT '0' COMMENT '订单ID',
  `num` int(10) unsigned DEFAULT '0' COMMENT '售后数量',
  `order_no` varchar(64) DEFAULT '' COMMENT '商城订单编号',
  `order_refund_id` int(10) unsigned DEFAULT '0' COMMENT '商城售后订单ID',
  `after_sales_id` int(10) DEFAULT NULL COMMENT '中台售后ID',
  `data` text COMMENT '售后数据',
  `status` tinyint(4) unsigned DEFAULT '0' COMMENT '是否推送售后',
  `refund_status` tinyint(4) unsigned DEFAULT '0',
  `shipping_address_id` int(10) unsigned DEFAULT '0' COMMENT '中台退货地址ID',
  `error_msg` varchar(255) DEFAULT '' COMMENT '错误信息',
  `return_data` text,
  `created_at` int(10) unsigned DEFAULT '0',
  `updated_at` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `mall_id` (`mall_id`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET=utf8 COMMENT='七件事供应链售后表';