ngds/sql/upgrade/1729.sql

-- 更新车奖配置
UPDATE `AR_CONFIG` SET `TITLE` = '是否开启车奖',`OPTIONS` = '{"declarationLevel":"67ABCE0ECE705575E055736AECE8644D"}',`VALUE` = '2' WHERE `CONFIG_NAME` = 'openGarage' LIMIT 1;
UPDATE `AR_CONFIG` SET `TITLE` = '车奖个人封顶值' WHERE `CONFIG_NAME` = 'openGarageCap' LIMIT 1;

-- 增加房奖配置
INSERT INTO `AR_CONFIG` (`CONFIG_NAME`, `TITLE`, `UNIT`, `INPUT_TYPE`, `OPTIONS`, `VALUE`, `TYPE`, `SORT`, `CREATED_AT`, `UPDATED_AT`) VALUES ('openVilla', '是否开启房奖', NULL, 8, '{"declarationLevel":"67ABCE0ECE705575E055736AECE8644D"}', '2', 'bonus', 44, 1521085263, 1657872447);
INSERT INTO `AR_CONFIG` (`CONFIG_NAME`, `TITLE`, `UNIT`, `INPUT_TYPE`, `OPTIONS`, `VALUE`, `TYPE`, `SORT`, `CREATED_AT`, `UPDATED_AT`) VALUES ('openVillaCap', '房奖个人封顶值', '$', 1, '', '10000', 'bonus', 44, 1521085263, 1657872447);


-- 星级表增加房奖比例
ALTER TABLE `AR_STAR_LEVEL` ADD COLUMN `VILLA_PERCENT` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '房奖比例' AFTER `TOURISM_PERCENT`;
ALTER TABLE `AR_STAR_LEVEL` MODIFY COLUMN `GARAGE_PERCENT` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '车奖比例';

UPDATE `AR_STAR_LEVEL` SET `VILLA_PERCENT` = 1.0 WHERE ID = 'E0B75B89C94F35EF8EE0054FA34B759C';
UPDATE `AR_STAR_LEVEL` SET `VILLA_PERCENT` = 1.5 WHERE ID = '7FEBE5DE95D530578915F4072E7CD842';
UPDATE `AR_STAR_LEVEL` SET `VILLA_PERCENT` = 2.0 WHERE ID = '391691E01CD5373F8D23F01FAD750222';
UPDATE `AR_STAR_LEVEL` SET `VILLA_PERCENT` = 2.5 WHERE ID = '8ACAEA736F5331B79283C895F36084E5';
UPDATE `AR_STAR_LEVEL` SET `VILLA_PERCENT` = 3.0 WHERE ID = 'CE46B39D07633217AA5711438649D1D6';
UPDATE `AR_STAR_LEVEL` SET `VILLA_PERCENT` = 3.5 WHERE ID = '24396B85E74A3005947F1F90EBA5F5CF';
UPDATE `AR_STAR_LEVEL` SET `VILLA_PERCENT` = 4.0 WHERE ID = '8C253968EB6F3F3FA5BE84F63136FFFA';


ALTER TABLE `AR_CALC_BONUS_GARAGE` DROP COLUMN `LEVEL_ID`;
ALTER TABLE `AR_CALC_BONUS_TOURISM` DROP COLUMN `LEVEL_ID`;

-- 增加业绩奖金字段
ALTER TABLE `AR_CALC_BONUS_GARAGE` ADD COLUMN `PERF` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '月总业绩' AFTER `POINT`;
ALTER TABLE `AR_CALC_BONUS_GARAGE` ADD COLUMN `TRANSFER_RATE` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '奖金总划拨比' AFTER `PERF`;
ALTER TABLE `AR_CALC_BONUS_GARAGE` ADD COLUMN `TRANSFER_AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '划拨奖金' AFTER `TRANSFER_RATE`;
ALTER TABLE `AR_CALC_BONUS_GARAGE` ADD COLUMN `CAP_AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '封顶前奖金' AFTER `TRANSFER_AMOUNT`;
ALTER TABLE `AR_CALC_BONUS_GARAGE` ADD COLUMN `POINT_COMPLEX` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '总点数' AFTER `CAP_AMOUNT`;
ALTER TABLE `AR_CALC_BONUS_GARAGE` CHANGE COLUMN `POINT` `POINT` decimal(10,4) NOT NULL DEFAULT 0.0000 COMMENT '奖金比例';

ALTER TABLE `AR_CALC_BONUS_TOURISM` ADD COLUMN `PERF` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '月总业绩' AFTER `POINT`;
ALTER TABLE `AR_CALC_BONUS_TOURISM` ADD COLUMN `TRANSFER_RATE` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '奖金总划拨比' AFTER `PERF`;
ALTER TABLE `AR_CALC_BONUS_TOURISM` ADD COLUMN `TRANSFER_AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '划拨奖金' AFTER `TRANSFER_RATE`;
ALTER TABLE `AR_CALC_BONUS_TOURISM` ADD COLUMN `CAP_AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '封顶前奖金' AFTER `TRANSFER_AMOUNT`;
ALTER TABLE `AR_CALC_BONUS_TOURISM` ADD COLUMN `POINT_COMPLEX` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '总点数' AFTER `CAP_AMOUNT`;
ALTER TABLE `AR_CALC_BONUS_TOURISM` CHANGE COLUMN `POINT` `POINT` decimal(10,4) NOT NULL DEFAULT 0.0000 COMMENT '奖金比例';


-- 新增车奖结算记录表
CREATE TABLE `AR_CALC_BONUS_VILLA` (
    `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` decimal(10,4) NOT NULL DEFAULT 0.0000 COMMENT '奖金比例',
    `PERF` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '月总业绩',
    `TRANSFER_RATE` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '奖金总划拨比',
    `TRANSFER_AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '划拨奖金',
    `CAP_AMOUNT` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '封顶前奖金',
    `POINT_COMPLEX` decimal(10,4) 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='房奖结算记录';


-- 奖金总表增加房奖列
ALTER TABLE `AR_CALC_BONUS` ADD COLUMN `BONUS_VILLA` decimal(16,3) NOT NULL DEFAULT 0.000 COMMENT '房奖奖金' AFTER `BONUS_GARAGE`;


-- 增加房奖流水
CREATE TABLE `AR_FLOW_VILLA_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` ADD COLUMN `VILLA_POINTS` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '房奖积分';
ALTER TABLE `AR_USER_BONUS` ADD COLUMN `VILLA_POINTS_TOTAL` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '房奖积分累计';


ALTER TABLE `AR_DEAL_TYPE` CHANGE COLUMN `COMMENT` `COMMENT` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '说明';

UPDATE `AR_DEAL_TYPE` SET `TYPE_NAME` = 'Adjust car points balance',`COMMENT` = '车奖积分余额调整' WHERE `ID` = '365753643942678528' LIMIT 1;
UPDATE `AR_DEAL_TYPE` SET `TYPE_NAME` = 'Car points exchange',`COMMENT` = '车奖积分兑换' WHERE `ID` = '365343060634243072' LIMIT 1;
UPDATE `AR_DEAL_TYPE` SET `TYPE_NAME` = 'Car points grant',`COMMENT` = '车奖积分发放' WHERE `ID` = '364305160249413632' LIMIT 1;

INSERT INTO `AR_DEAL_TYPE` (`ID`, `TYPE_NAME`, `COMMENT`, `CREATE_ADMIN`, `UPDATE_ADMIN`, `CREATE_REMARK`, `UPDATE_REMARK`, `IS_PRESET`, `IS_ENABLE`, `CREATED_AT`, `UPDATED_AT`, `SORT_ORDER`) VALUES ('390990784847024128', 'Adjust villa points balance', '房奖积分余额调整', '354832362066022400', NULL, '', NULL, 0, 1, 1658467551, 0, 1);
INSERT INTO `AR_DEAL_TYPE` (`ID`, `TYPE_NAME`, `COMMENT`, `CREATE_ADMIN`, `UPDATE_ADMIN`, `CREATE_REMARK`, `UPDATE_REMARK`, `IS_PRESET`, `IS_ENABLE`, `CREATED_AT`, `UPDATED_AT`, `SORT_ORDER`) VALUES ('390992212424527872', 'Villa points exchange', '房奖积分兑换', '354832362066022400', NULL, '', NULL, 0, 1, 1658467892, 0, 1);
INSERT INTO `AR_DEAL_TYPE` (`ID`, `TYPE_NAME`, `COMMENT`, `CREATE_ADMIN`, `UPDATE_ADMIN`, `CREATE_REMARK`, `UPDATE_REMARK`, `IS_PRESET`, `IS_ENABLE`, `CREATED_AT`, `UPDATED_AT`, `SORT_ORDER`) VALUES ('390992293622059008', 'Villa points grant', '房奖积分发放', '354832362066022400', NULL, '', NULL, 0, 1, 1658467910, 0, 1);