ngds/sql/upgrade/1602.sql

CREATE TABLE `AR_APPROACH_ORDER` (
  `ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `SN` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `DEC_SN` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `USER_ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `USER_NAME` varchar(16) COLLATE utf8mb4_bin NOT NULL,
  `ORDER_TYPE` varchar(12) COLLATE utf8mb4_bin NOT NULL,
  `ORDER_AMOUNT` decimal(16,2) NOT NULL DEFAULT 0.00,
  `PV` decimal(16,2) NOT NULL DEFAULT 0.00,
  `PAY_AMOUNT` decimal(16,2) NOT NULL DEFAULT 0.00,
  `PAY_PV` decimal(16,2) NOT NULL DEFAULT 0.00,
  `ORDER_AMOUNT_STANDARD` float(16,2) NOT NULL DEFAULT 0.00 COMMENT '订单价格-标准价格',
  `PAY_AMOUNT_STANDARD` float(16,2) NOT NULL DEFAULT 0.00 COMMENT '标准价格',
  `EXCHANGE_RATE` float(10,4) NOT NULL DEFAULT 0.0000 COMMENT '汇率',
  `PAY_AT` int(10) NOT NULL DEFAULT 0,
  `FREIGHT` float(10,2) NOT NULL DEFAULT 0.00,
  `PAY_FREIGHT` float(10,2) NOT NULL DEFAULT 0.00,
  `DELIVERY_STATUS` tinyint(2) NOT NULL DEFAULT 0,
  `DELIVERY_PERIOD` int(10) NOT NULL DEFAULT 0,
  `DELIVERY_AT` int(10) NOT NULL DEFAULT 0,
  `EXPRESS_COMPANY` varchar(128) COLLATE utf8mb4_bin DEFAULT '',
  `ORDER_TRACK_NO` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `EXPRESS_TYPE` tinyint(1) NOT NULL DEFAULT 0,
  `FRONT_REMARK` varchar(1000) COLLATE utf8mb4_bin DEFAULT '',
  `REMARK` text COLLATE utf8mb4_bin DEFAULT NULL,
  `NOTE` text COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注内容',
  `PERIOD_NUM` int(10) NOT NULL DEFAULT 0,
  `STATUS` int(3) NOT NULL DEFAULT 0,
  `CONSIGNEE` varchar(120) COLLATE utf8mb4_bin DEFAULT '',
  `MOBILE` varchar(11) COLLATE utf8mb4_bin DEFAULT '',
  `TEL` varchar(16) COLLATE utf8mb4_bin DEFAULT '',
  `PROVINCE` int(6) NOT NULL DEFAULT 0,
  `CITY` int(6) DEFAULT 0,
  `COUNTY` int(9) DEFAULT 0,
  `LGA_NAME` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `CITY_NAME` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `ADDRESS` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `P_CALC_MONTH` date NOT NULL,
  `CREATED_AT` int(10) NOT NULL DEFAULT 0,
  `CREATE_USER` varchar(16) COLLATE utf8mb4_bin NOT NULL,
  `UPDATED_AT` int(10) NOT NULL DEFAULT 0,
  `UPDATER` varchar(16) COLLATE utf8mb4_bin DEFAULT '',
  `IS_DELETE` int(10) NOT NULL DEFAULT 0,
  `DELETED_AT` int(10) NOT NULL DEFAULT 0,
  `PAY_TYPE` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT 'cash',
  `WAREHOUSE` varchar(16) COLLATE utf8mb4_bin NOT NULL DEFAULT '01',
  `EMAIL` varchar(32) COLLATE utf8mb4_bin DEFAULT '' COMMENT '邮箱',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `SN` (`SN`) USING BTREE,
  KEY `USER_ID` (`USER_ID`) USING BTREE,
  KEY `USER_NAME` (`USER_NAME`) USING BTREE,
  KEY `PERIOD_NUM` (`PERIOD_NUM`) USING BTREE,
  KEY `IS_DELETE` (`IS_DELETE`) USING BTREE,
  KEY `ORDER_TYPE` (`ORDER_TYPE`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='商品中间订单';

CREATE TABLE `AR_APPROACH_ORDER_GOODS` (
  `ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `ORDER_SN` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `GOODS_ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `GOODS_TITLE` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `PRICE` decimal(16,2) NOT NULL DEFAULT 0.00,
  `REAL_PRICE` decimal(16,2) NOT NULL DEFAULT 0.00,
  `PV` decimal(16,2) NOT NULL DEFAULT 0.00,
  `REAL_PV` decimal(16,2) NOT NULL DEFAULT 0.00,
  `STANDARD_PRICE` float(16,2) NOT NULL DEFAULT 0.00 COMMENT '标准价格',
  `REAL_STANDARD_PRICE` float(16,2) NOT NULL DEFAULT 0.00 COMMENT '支付标准价格',
  `EXCHANGE_RATE` float(10,4) NOT NULL DEFAULT 0.0000 COMMENT '汇率',
  `TAX_RATE` float(16,2) NOT NULL DEFAULT 0.00 COMMENT '税率',
  `SKU_CODE` varchar(16) COLLATE utf8mb4_bin NOT NULL,
  `BUY_NUMS` int(10) NOT NULL DEFAULT 0,
  `P_CALC_MONTH` date NOT NULL,
  `POINT` smallint(5) unsigned NOT NULL DEFAULT 0,
  `CATEGORY_TYPE` tinyint(1) unsigned NOT NULL DEFAULT 1 COMMENT '商品分类：1普通商品 4旅游积分商品 5名车积分商品 6豪宅积分商品',
  `PAY_TYPE` varchar(30) COLLATE utf8mb4_bin NOT NULL DEFAULT 'cash' COMMENT '支付方式：1-cash-余额支付 3-exchange-兑换点数 4-travel_points-旅游积分 5-car_points-名车积分 6-house_points-豪宅积分',
  `EMAIL` varchar(32) COLLATE utf8mb4_bin DEFAULT '' COMMENT '邮箱',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `GOODS_ID` (`GOODS_ID`) USING BTREE,
  KEY `ORDER_SN` (`ORDER_SN`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='订单中包含的商品';



-- 订单表增加NOTE字段,保存PayStack支付订单回调日志
ALTER TABLE `AR_ORDER` ADD COLUMN `NOTE` text NULL COMMENT '备注内容' AFTER `REMARK`;