107 lines
3.9 KiB
PL/PgSQL
107 lines
3.9 KiB
PL/PgSQL
-- 岗位薪资构成、福利待遇、工作时间安排字段及业务字典初始化。
|
||
-- 执行目标：对应环境 HighGo 数据库的 shz schema。
|
||
-- 可重复执行：字段使用 IF NOT EXISTS，字典类型和字典项分别按 dict_type、dict_value 去重。
|
||
|
||
BEGIN;
|
||
|
||
ALTER TABLE "shz"."job"
|
||
ADD COLUMN IF NOT EXISTS "salary_composition" VARCHAR(1000),
|
||
ADD COLUMN IF NOT EXISTS "welfare_benefits" VARCHAR(1000),
|
||
ADD COLUMN IF NOT EXISTS "work_schedule" VARCHAR(1000);
|
||
|
||
COMMENT ON COLUMN "shz"."job"."salary_composition"
|
||
IS '薪资范围与构成（数值范围使用 min_salary/max_salary），多项及手动输入值以英文逗号分隔，对应业务字典 salary_composition';
|
||
COMMENT ON COLUMN "shz"."job"."welfare_benefits"
|
||
IS '福利待遇，多项及手动输入值以英文逗号分隔，对应业务字典 welfare_benefits';
|
||
COMMENT ON COLUMN "shz"."job"."work_schedule"
|
||
IS '工作时间安排，多项及手动输入值以英文逗号分隔，对应业务字典 work_schedule';
|
||
|
||
WITH seed("dict_sort", "dict_name", "dict_type", "remark") AS (
|
||
VALUES
|
||
(1, '薪资范围与构成', 'salary_composition', '岗位薪资范围与构成，可与手动输入值组合使用'),
|
||
(2, '福利待遇', 'welfare_benefits', '岗位福利待遇，可与手动输入值组合使用'),
|
||
(3, '工作时间安排', 'work_schedule', '岗位工作时间安排，可与手动输入值组合使用')
|
||
),
|
||
new_rows AS (
|
||
SELECT
|
||
ROW_NUMBER() OVER (ORDER BY seed."dict_sort") AS rn,
|
||
seed.*
|
||
FROM seed
|
||
WHERE NOT EXISTS (
|
||
SELECT 1
|
||
FROM "shz"."bussiness_dict_type" existing_type
|
||
WHERE existing_type."dict_type" = seed."dict_type"
|
||
)
|
||
),
|
||
base AS (
|
||
SELECT COALESCE(MAX("dict_id"), 0) AS max_id
|
||
FROM "shz"."bussiness_dict_type"
|
||
)
|
||
INSERT INTO "shz"."bussiness_dict_type" (
|
||
"dict_id", "dict_name", "dict_type", "status", "create_by", "create_time", "remark"
|
||
)
|
||
SELECT
|
||
base.max_id + new_rows.rn,
|
||
new_rows."dict_name",
|
||
new_rows."dict_type",
|
||
'0',
|
||
'system',
|
||
CURRENT_TIMESTAMP,
|
||
new_rows."remark"
|
||
FROM new_rows
|
||
CROSS JOIN base;
|
||
|
||
WITH seed("dict_type", "dict_sort", "dict_label", "dict_value") AS (
|
||
VALUES
|
||
('salary_composition', 1, '基本工资', '基本工资'),
|
||
('salary_composition', 2, '绩效工资', '绩效工资'),
|
||
('salary_composition', 3, '奖金', '奖金'),
|
||
('welfare_benefits', 1, '五险一金', '五险一金'),
|
||
('welfare_benefits', 2, '带薪年假', '带薪年假'),
|
||
('welfare_benefits', 3, '节日福利', '节日福利'),
|
||
('welfare_benefits', 4, '定期体检', '定期体检'),
|
||
('welfare_benefits', 5, '培训机会', '培训机会'),
|
||
('welfare_benefits', 6, '员工团建', '员工团建'),
|
||
('welfare_benefits', 7, '餐补', '餐补'),
|
||
('welfare_benefits', 8, '房补', '房补'),
|
||
('work_schedule', 1, '早10点-下午7点', '早10点-下午7点'),
|
||
('work_schedule', 2, '午休2小时', '午休2小时')
|
||
),
|
||
new_rows AS (
|
||
SELECT
|
||
ROW_NUMBER() OVER (ORDER BY seed."dict_type", seed."dict_sort") AS rn,
|
||
seed.*
|
||
FROM seed
|
||
WHERE NOT EXISTS (
|
||
SELECT 1
|
||
FROM "shz"."bussiness_dict_data" data
|
||
WHERE data."dict_type" = seed."dict_type"
|
||
AND data."dict_value" = seed."dict_value"
|
||
)
|
||
),
|
||
base AS (
|
||
SELECT COALESCE(MAX("dict_code"), 0) AS max_code
|
||
FROM "shz"."bussiness_dict_data"
|
||
)
|
||
INSERT INTO "shz"."bussiness_dict_data" (
|
||
"dict_code", "dict_sort", "dict_label", "dict_value", "dict_type",
|
||
"css_class", "list_class", "is_default", "status", "create_by", "create_time", "remark"
|
||
)
|
||
SELECT
|
||
base.max_code + new_rows.rn,
|
||
new_rows."dict_sort",
|
||
new_rows."dict_label",
|
||
new_rows."dict_value",
|
||
new_rows."dict_type",
|
||
NULL,
|
||
'default',
|
||
'N',
|
||
'0',
|
||
'system',
|
||
CURRENT_TIMESTAMP,
|
||
'岗位详情标签'
|
||
FROM new_rows
|
||
CROSS JOIN base;
|
||
|
||
COMMIT;
|