ngds/sql/upgrade/1548.sql

-- 聘级表增加旅游奖比例、车房奖比例
ALTER TABLE `AR_EMPLOY_LEVEL` ADD COLUMN `TOURISM_PERCENT` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '旅游奖比例';
ALTER TABLE `AR_EMPLOY_LEVEL` ADD COLUMN `GARAGE_PERCENT` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '车房奖比例';
-- 更新聘级表中旅游奖、车房奖比例
UPDATE `AR_EMPLOY_LEVEL` SET `TOURISM_PERCENT` = 1.0, `GARAGE_PERCENT` = 1.0 WHERE ID = 'E121497617216708616';
UPDATE `AR_EMPLOY_LEVEL` SET `TOURISM_PERCENT` = 1.1, `GARAGE_PERCENT` = 1.5 WHERE ID = '67BE6A4D03C52288E055736AECE8644D';
UPDATE `AR_EMPLOY_LEVEL` SET `TOURISM_PERCENT` = 1.2, `GARAGE_PERCENT` = 2.0 WHERE ID = '67BE6EA2070D22EBE055736AECE8644D';
UPDATE `AR_EMPLOY_LEVEL` SET `TOURISM_PERCENT` = 1.3, `GARAGE_PERCENT` = 2.5 WHERE ID = '67BE742A336F2370E055736AECE8644D';
UPDATE `AR_EMPLOY_LEVEL` SET `TOURISM_PERCENT` = 1.4, `GARAGE_PERCENT` = 3.0 WHERE ID = '67BE805032C22492E055736AECE8644D';
-- 新增荣衔星级表
CREATE TABLE `AR_STAR_LEVEL` (
  `ID` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT 'ID',
  `LEVEL_NAME` varchar(48) COLLATE utf8mb4_bin NOT NULL COMMENT '级别名称',
  `ICON_TYPE` tinyint(1) NOT NULL DEFAULT 0 COMMENT '图表类型',
  `ICON_NUM` tinyint(2) NOT NULL DEFAULT 0 COMMENT '图标个数',
  `MIN_LEVEL_ID` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '上级ID',
  `LEVEL_SCORE` decimal(12,2) unsigned NOT NULL DEFAULT 0.00 COMMENT '级别分数',
  `UPGRADE_SCORE` decimal(12,2) unsigned NOT NULL DEFAULT 0.00 COMMENT '升级分数',
  `SORT` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '排序值',
  `TOURISM_PERCENT` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '旅游奖比例',
  `GARAGE_PERCENT` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '车房奖比例',
  `CREATED_AT` int(10) NOT NULL COMMENT '创建时间',
  `UPDATED_AT` int(10) NOT NULL DEFAULT 0 COMMENT '更新时间',
  `CREATE_ADMIN` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '创建管理员',
  `UPDATE_ADMIN` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新管理员',
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `LEVEL_NAME` (`LEVEL_NAME`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='荣衔星级表';
INSERT INTO `AR_STAR_LEVEL` (`ID`, `LEVEL_NAME`, `ICON_TYPE`, `ICON_NUM`, `MIN_LEVEL_ID`, `LEVEL_SCORE`, `UPGRADE_SCORE`, `SORT`, `TOURISM_PERCENT`, `GARAGE_PERCENT`, `CREATED_AT`, `UPDATED_AT`, `CREATE_ADMIN`, `UPDATE_ADMIN`) VALUES ('DCBEDD8E0EAD3DF59D81CCC0C2ACAEFF', '0-Star Crown', 2, 0, ' ', 0.00, 10000.00, 0, 0.00, 0.00, 1655779138, 1655779138, 'admin', 'admin');
INSERT INTO `AR_STAR_LEVEL` (`ID`, `LEVEL_NAME`, `ICON_TYPE`, `ICON_NUM`, `MIN_LEVEL_ID`, `LEVEL_SCORE`, `UPGRADE_SCORE`, `SORT`, `TOURISM_PERCENT`, `GARAGE_PERCENT`, `CREATED_AT`, `UPDATED_AT`, `CREATE_ADMIN`, `UPDATE_ADMIN`) VALUES ('E0B75B89C94F35EF8EE0054FA34B759C', '1-Star Crown', 2, 0, 'DCBEDD8E0EAD3DF59D81CCC0C2ACAEFF', 10000.00, 20000.00, 1, 1.50, 3.50, 1655779138, 1655779138, 'admin', 'admin');
INSERT INTO `AR_STAR_LEVEL` (`ID`, `LEVEL_NAME`, `ICON_TYPE`, `ICON_NUM`, `MIN_LEVEL_ID`, `LEVEL_SCORE`, `UPGRADE_SCORE`, `SORT`, `TOURISM_PERCENT`, `GARAGE_PERCENT`, `CREATED_AT`, `UPDATED_AT`, `CREATE_ADMIN`, `UPDATE_ADMIN`) VALUES ('7FEBE5DE95D530578915F4072E7CD842', '2-Star Crown', 2, 0, 'E0B75B89C94F35EF8EE0054FA34B759C', 20000.00, 35000.00, 2, 2.00, 4.00, 1655779138, 1655779138, 'admin', 'admin');
INSERT INTO `AR_STAR_LEVEL` (`ID`, `LEVEL_NAME`, `ICON_TYPE`, `ICON_NUM`, `MIN_LEVEL_ID`, `LEVEL_SCORE`, `UPGRADE_SCORE`, `SORT`, `TOURISM_PERCENT`, `GARAGE_PERCENT`, `CREATED_AT`, `UPDATED_AT`, `CREATE_ADMIN`, `UPDATE_ADMIN`) VALUES ('391691E01CD5373F8D23F01FAD750222', '3-Star Crown', 2, 0, '7FEBE5DE95D530578915F4072E7CD842', 35000.00, 50000.00, 3, 2.50, 4.50, 1655779138, 1655779138, 'admin', 'admin');
INSERT INTO `AR_STAR_LEVEL` (`ID`, `LEVEL_NAME`, `ICON_TYPE`, `ICON_NUM`, `MIN_LEVEL_ID`, `LEVEL_SCORE`, `UPGRADE_SCORE`, `SORT`, `TOURISM_PERCENT`, `GARAGE_PERCENT`, `CREATED_AT`, `UPDATED_AT`, `CREATE_ADMIN`, `UPDATE_ADMIN`) VALUES ('8ACAEA736F5331B79283C895F36084E5', '4-Star Crown', 2, 0, '391691E01CD5373F8D23F01FAD750222', 50000.00, 100000.00, 4, 3.00, 5.00, 1655779138, 1655779138, 'admin', 'admin');
INSERT INTO `AR_STAR_LEVEL` (`ID`, `LEVEL_NAME`, `ICON_TYPE`, `ICON_NUM`, `MIN_LEVEL_ID`, `LEVEL_SCORE`, `UPGRADE_SCORE`, `SORT`, `TOURISM_PERCENT`, `GARAGE_PERCENT`, `CREATED_AT`, `UPDATED_AT`, `CREATE_ADMIN`, `UPDATE_ADMIN`) VALUES ('CE46B39D07633217AA5711438649D1D6', '5-Star Crown', 2, 0, '8ACAEA736F5331B79283C895F36084E5', 100000.00, 250000.00, 5, 3.50, 5.50, 1655779138, 1655779138, 'admin', 'admin');
INSERT INTO `AR_STAR_LEVEL` (`ID`, `LEVEL_NAME`, `ICON_TYPE`, `ICON_NUM`, `MIN_LEVEL_ID`, `LEVEL_SCORE`, `UPGRADE_SCORE`, `SORT`, `TOURISM_PERCENT`, `GARAGE_PERCENT`, `CREATED_AT`, `UPDATED_AT`, `CREATE_ADMIN`, `UPDATE_ADMIN`) VALUES ('24396B85E74A3005947F1F90EBA5F5CF', '6-Star Crown', 2, 0, 'CE46B39D07633217AA5711438649D1D6', 250000.00, 500000.00, 6, 4.00, 6.00, 1655779138, 1655779138, 'admin', 'admin');
INSERT INTO `AR_STAR_LEVEL` (`ID`, `LEVEL_NAME`, `ICON_TYPE`, `ICON_NUM`, `MIN_LEVEL_ID`, `LEVEL_SCORE`, `UPGRADE_SCORE`, `SORT`, `TOURISM_PERCENT`, `GARAGE_PERCENT`, `CREATED_AT`, `UPDATED_AT`, `CREATE_ADMIN`, `UPDATE_ADMIN`) VALUES ('8C253968EB6F3F3FA5BE84F63136FFFA', '7-Star Crown', 2, 0, '24396B85E74A3005947F1F90EBA5F5CF', 500000.00, 100000000.00, 7, 4.50, 6.50, 1655779138, 1655779138, 'admin', 'admin');
-- 团队奖/绩效奖增加最新星级
ALTER TABLE `AR_CALC_BONUS_QY` ADD COLUMN `LAST_STAR_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '' COMMENT '最新星级' AFTER `LAST_EMP_LV`;
-- 奖金表增加用户星级
ALTER TABLE `AR_CALC_BONUS` ADD COLUMN `LAST_STAR_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '' COMMENT '最新星级' AFTER `LAST_EMP_LV`;
-- 用户表增加星级
ALTER TABLE `AR_USER` ADD COLUMN `STAR_LV` varchar(32) COLLATE utf8mb4_bin DEFAULT '' COMMENT '最新星级' AFTER `EMP_LV`;
-- 更新团队奖/绩效奖个人奖金封顶值
UPDATE `AR_DECLARATION_LEVEL` SET `INCOME_CAP` = 1500 WHERE ID = '67ABCB8C1F7D5519E055736AECE8644D';
UPDATE `AR_DECLARATION_LEVEL` SET `INCOME_CAP` = 10000 WHERE ID = '67ABCCE20A9F5553E055736AECE8644D';
UPDATE `AR_DECLARATION_LEVEL` SET `INCOME_CAP` = 50000 WHERE ID = '67ABCE0ECE705575E055736AECE8644D';