ngds/sql/upgrade/1359.sql

-- 删除名车奖、豪宅奖配置
DELETE FROM `AR_CONFIG` WHERE `CONFIG_NAME` IN ('openTravel', 'openCar', 'openHouse');
-- 增加车房奖配置
INSERT INTO `AR_CONFIG` (`CONFIG_NAME`, `TITLE`, `UNIT`, `INPUT_TYPE`, `OPTIONS`, `VALUE`, `TYPE`, `SORT`, `CREATED_AT`, `UPDATED_AT`) VALUES ('openTourism', '是否开启旅游奖', NULL, 8, '{\"employLevel\":[\"E121497617216708616\",\"67BE6A4D03C52288E055736AECE8644D\",\"67BE6EA2070D22EBE055736AECE8644D\"],\"declarationLevel\":[\"67ABCB8C1F7D5519E055736AECE8644D\",\"67ABCCE20A9F5553E055736AECE8644D\",\"67ABCE0ECE705575E055736AECE8644D\"]}', '3', 'bonus', 44, 1521085263, 1649063120);
INSERT INTO `AR_CONFIG` (`CONFIG_NAME`, `TITLE`, `UNIT`, `INPUT_TYPE`, `OPTIONS`, `VALUE`, `TYPE`, `SORT`, `CREATED_AT`, `UPDATED_AT`) VALUES ('openGarage', '是否开启车房奖', NULL, 8,  '{\"employLevel\":[\"67BE6EA2070D22EBE055736AECE8644D\"],\"declarationLevel\":\"67ABCE0ECE705575E055736AECE8644D\"}', '4', 'bonus', 44, 1521085263, 1649063120);
-- 车房奖个人上限值
INSERT INTO `AR_CONFIG` (`CONFIG_NAME`, `TITLE`, `UNIT`, `INPUT_TYPE`, `OPTIONS`, `VALUE`, `TYPE`, `SORT`, `CREATED_AT`, `UPDATED_AT`) VALUES ('openGarageCap', '车房奖个人封顶值', '$', 1, '', '10000', 'bonus', 44, 1521085263, 1649063120);
-- 删除无用车房游列
ALTER TABLE `AR_CALC_BONUS` DROP COLUMN `BONUS_TRAVEL`;
ALTER TABLE `AR_CALC_BONUS` DROP COLUMN `BONUS_CAR`;
ALTER TABLE `AR_CALC_BONUS` DROP COLUMN `BONUS_HOUSE`;
-- 增加旅游奖、车房奖
ALTER TABLE `AR_CALC_BONUS` ADD COLUMN `BONUS_TOURISM` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '旅游奖';
ALTER TABLE `AR_CALC_BONUS` ADD COLUMN `BONUS_GARAGE`  decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '车房奖';
-- 车房积分调整
DELETE FROM `AR_DEAL_TYPE` WHERE ID IN ('365753716726435840', '365343108638052352', '364305230252347392', '364305038266470400', '364305160249413632', '365343018452127744', '365343060634243072', '365753533326299136', '365753643942678528');
INSERT INTO `AR_DEAL_TYPE` (`ID`, `TYPE_NAME`, `CREATE_ADMIN`, `UPDATE_ADMIN`, `CREATE_REMARK`, `UPDATE_REMARK`, `IS_PRESET`, `IS_ENABLE`, `CREATED_AT`, `UPDATED_AT`, `SORT_ORDER`) VALUES ('364305038266470400', '旅游积分发放', '', NULL, '', NULL, 1, 1, 1652079974, 0, 1);
INSERT INTO `AR_DEAL_TYPE` (`ID`, `TYPE_NAME`, `CREATE_ADMIN`, `UPDATE_ADMIN`, `CREATE_REMARK`, `UPDATE_REMARK`, `IS_PRESET`, `IS_ENABLE`, `CREATED_AT`, `UPDATED_AT`, `SORT_ORDER`) VALUES ('364305160249413632', '车房积分发放', '', NULL, '', NULL, 1, 1, 1652080003, 0, 1);
INSERT INTO `AR_DEAL_TYPE` (`ID`, `TYPE_NAME`, `CREATE_ADMIN`, `UPDATE_ADMIN`, `CREATE_REMARK`, `UPDATE_REMARK`, `IS_PRESET`, `IS_ENABLE`, `CREATED_AT`, `UPDATED_AT`, `SORT_ORDER`) VALUES ('365343018452127744', '旅游积分兑换', '', NULL, '', NULL, 1, 1, 1652327448, 0, 1);
INSERT INTO `AR_DEAL_TYPE` (`ID`, `TYPE_NAME`, `CREATE_ADMIN`, `UPDATE_ADMIN`, `CREATE_REMARK`, `UPDATE_REMARK`, `IS_PRESET`, `IS_ENABLE`, `CREATED_AT`, `UPDATED_AT`, `SORT_ORDER`) VALUES ('365343060634243072', '车房积分兑换', '', NULL, '', NULL, 1, 1, 1652327459, 0, 1);
INSERT INTO `AR_DEAL_TYPE` (`ID`, `TYPE_NAME`, `CREATE_ADMIN`, `UPDATE_ADMIN`, `CREATE_REMARK`, `UPDATE_REMARK`, `IS_PRESET`, `IS_ENABLE`, `CREATED_AT`, `UPDATED_AT`, `SORT_ORDER`) VALUES ('365753533326299136', '旅游积分余额调整', '', NULL, '', NULL, 0, 1, 1652425322, 0, 1);
INSERT INTO `AR_DEAL_TYPE` (`ID`, `TYPE_NAME`, `CREATE_ADMIN`, `UPDATE_ADMIN`, `CREATE_REMARK`, `UPDATE_REMARK`, `IS_PRESET`, `IS_ENABLE`, `CREATED_AT`, `UPDATED_AT`, `SORT_ORDER`) VALUES ('365753643942678528', '车房积分余额调整', '', NULL, '', NULL, 0, 1, 1652425349, 0, 1);
-- 删除车房表
DROP TABLE `AR_FLOW_ZONE_POINTS`;
-- 创建旅游积分流水
CREATE TABLE `AR_FLOW_TOURISM_POINTS` (
  `ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `USER_ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `LAST_DEC_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `LAST_EMP_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `LAST_STAR_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `LAST_STATUS` tinyint(1) NOT NULL DEFAULT 0,
  `CALC_ID` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000,
  `AMOUNT_STANDARD` float(16,2) NOT NULL DEFAULT 0.00 COMMENT '奖金数额-标准数值',
  `EXCHANGE_RATE` float(10,4) NOT NULL DEFAULT 0.0000 COMMENT '汇率',
  `TOTAL` decimal(16,3) NOT NULL DEFAULT 0.000,
  `IS_INCR` tinyint(1) NOT NULL DEFAULT 1,
  `REMARK` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `REMARK_IS_SHOW` tinyint(1) NOT NULL DEFAULT 1,
  `DEAL_TYPE_ID` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `DEAL_TYPE_IS_PRESET` tinyint(1) NOT NULL DEFAULT 1,
  `ADMIN_NAME` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `PERIOD_NUM` int(10) NOT NULL,
  `CALC_MONTH` int(10) NOT NULL,
  `P_MONTH` date NOT NULL,
  `CREATED_AT` int(10) NOT NULL,
  `DELETED` tinyint(1) NOT NULL DEFAULT 0,
  `DELETED_AT` int(10) NOT NULL DEFAULT 0,
  `TRANSFER_SN` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `SORT` int(10) NOT NULL DEFAULT 0,
  `AUTO_SORT` int(10) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `IDX_USER_ID` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='旅游积分流水';
-- 创建车房积分流水
CREATE TABLE `AR_FLOW_GARAGE_POINTS` (
  `ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `USER_ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `LAST_DEC_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `LAST_EMP_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `LAST_STAR_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `LAST_STATUS` tinyint(1) NOT NULL DEFAULT 0,
  `CALC_ID` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000,
  `AMOUNT_STANDARD` float(16,2) NOT NULL DEFAULT 0.00 COMMENT '奖金数额-标准数值',
  `EXCHANGE_RATE` float(10,4) NOT NULL DEFAULT 0.0000 COMMENT '汇率',
  `TOTAL` decimal(16,3) NOT NULL DEFAULT 0.000,
  `IS_INCR` tinyint(1) NOT NULL DEFAULT 1,
  `REMARK` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `REMARK_IS_SHOW` tinyint(1) NOT NULL DEFAULT 1,
  `DEAL_TYPE_ID` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `DEAL_TYPE_IS_PRESET` tinyint(1) NOT NULL DEFAULT 1,
  `ADMIN_NAME` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `PERIOD_NUM` int(10) NOT NULL,
  `CALC_MONTH` int(10) NOT NULL,
  `P_MONTH` date NOT NULL,
  `CREATED_AT` int(10) NOT NULL,
  `DELETED` tinyint(1) NOT NULL DEFAULT 0,
  `DELETED_AT` int(10) NOT NULL DEFAULT 0,
  `TRANSFER_SN` varchar(32) COLLATE utf8mb4_bin DEFAULT '',
  `SORT` int(10) NOT NULL DEFAULT 0,
  `AUTO_SORT` int(10) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `IDX_USER_ID` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='车房积分流水';
-- 删除用户账户奖金类型
ALTER TABLE `AR_USER_BONUS` DROP COLUMN `TRAVEL_POINTS`;
ALTER TABLE `AR_USER_BONUS` DROP COLUMN `TRAVEL_POINTS_TOTAL`;
ALTER TABLE `AR_USER_BONUS` DROP COLUMN `CAR_POINTS`;
ALTER TABLE `AR_USER_BONUS` DROP COLUMN `CAR_POINTS_TOTAL`;
ALTER TABLE `AR_USER_BONUS` DROP COLUMN `HOUSE_POINTS`;
ALTER TABLE `AR_USER_BONUS` DROP COLUMN `HOUSE_POINTS_TOTAL`;
-- 新增用户账户奖金类型
ALTER TABLE `AR_USER_BONUS` ADD COLUMN `TOURISM_POINTS` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '旅游积分';
ALTER TABLE `AR_USER_BONUS` ADD COLUMN `TOURISM_POINTS_TOTAL` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '旅游积分累计';
ALTER TABLE `AR_USER_BONUS` ADD COLUMN `GARAGE_POINTS` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '车房积分';
ALTER TABLE `AR_USER_BONUS` ADD COLUMN `GARAGE_POINTS_TOTAL` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '车房积分累计';
-- 删除车房旅游奖总表
DROP TABLE `AR_CALC_BONUS_ZONE`;
CREATE TABLE `AR_CALC_BONUS_TOURISM` (
  `ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `USER_ID` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '会员ID',
  `LAST_DEC_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '' COMMENT '结算时会员等级',
  `LAST_EMP_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '' COMMENT '计算时会员聘级',
  `LAST_STAR_LV` varchar(32) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '计算时会员星级',
  `LEVEL_ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '会员最新聘级.聘级ID,对应AR_EMPLOY_LEVEL表主键',
  `LAST_STATUS` tinyint(1) NOT NULL DEFAULT 0 COMMENT '结算时状态',
  `AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '奖金数',
  `POINT` float(10,4) unsigned NOT NULL DEFAULT 0.0000 COMMENT '奖金比例',
  `PERIOD_NUM` int(10) NOT NULL COMMENT '结算期数',
  `CALC_YEAR` int(10) NOT NULL COMMENT '结算年份',
  `CALC_MONTH` int(10) NOT NULL COMMENT '结算月份',
  `P_CALC_MONTH` date NOT NULL,
  `LOGS` varchar(2000) COLLATE utf8mb4_bin DEFAULT '',
  `CREATED_AT` int(10) NOT NULL COMMENT '创建时间',
  KEY `USER_ID` (`USER_ID`),
  KEY `PERIOD_NUM` (`PERIOD_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='旅游奖结算记录';
CREATE TABLE `AR_CALC_BONUS_GARAGE` (
  `ID` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `USER_ID` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '会员ID',
  `LAST_DEC_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '' COMMENT '结算时会员等级',
  `LAST_EMP_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '' COMMENT '计算时会员聘级',
  `LAST_STAR_LV` varchar(32) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '计算时会员星级',
  `LEVEL_ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '会员最新聘级.聘级ID,对应AR_EMPLOY_LEVEL表主键',
  `LAST_STATUS` tinyint(1) NOT NULL DEFAULT 0 COMMENT '结算时状态',
  `AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '奖金数',
  `POINT` float(10,4) unsigned NOT NULL DEFAULT 0.0000 COMMENT '奖金比例',
  `PERIOD_NUM` int(10) NOT NULL COMMENT '结算期数',
  `CALC_YEAR` int(10) NOT NULL COMMENT '结算年份',
  `CALC_MONTH` int(10) NOT NULL COMMENT '结算月份',
  `P_CALC_MONTH` date NOT NULL,
  `LOGS` varchar(2000) COLLATE utf8mb4_bin DEFAULT '',
  `CREATED_AT` int(10) NOT NULL COMMENT '创建时间',
  KEY `USER_ID` (`USER_ID`),
  KEY `PERIOD_NUM` (`PERIOD_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='车房奖结算记录';