2023-04-01.sql
2.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
-- 数据修复
-- 需备份 evaluation_item_used 表数据
WITH t1 AS (
SELECT
evaluation_project.company_id,
evaluation_project.id AS project_id,
jsonb_array_elements(evaluation_project."template" #> '{linkNodes}') AS nodes
FROM
evaluation_project
WHERE evaluation_project."template" #> '{linkNodes}'<>'null'
ORDER BY
id
),
t2 AS (
SELECT
t1.company_id,
t1.project_id,
t1.nodes ->> 'id' AS node_id,
t1.nodes ->> 'type' AS node_type,
jsonb_array_elements(t1.nodes #> '{nodeContents}') AS node_contents
FROM
t1
WHERE t1.nodes #> '{nodeContents}'<>'null'
),
t3 AS(
SELECT
t2.company_id,
t2.project_id,
cast(t2.node_id AS int8) AS node_id,
cast(t2.node_type AS int8) AS node_type,
t2.node_contents ->> 'category' AS "category",
t2.node_contents ->> 'name' AS "name",
cast(t2.node_contents ->> 'ruleId' AS int8) AS "rule_id"
FROM
t2
),
t4 as (
SELECT
t3.project_id,
t3.node_type,
t3."category",
t3."name",
row_to_json( "evaluation_rule".*) as "rule",
"evaluation_rule"."type" as rule_type
FROM t3
join "evaluation_rule" on t3.rule_id= "evaluation_rule".id
),
t5 as (
SELECT
evaluation_item_used."id",
evaluation_item_used.evaluation_project_id,
evaluation_item_used.node_type,
evaluation_item_used.category,
evaluation_item_used."name"
FROM evaluation_item_used
WHERE "rule"='{}' or "rule" ISNULL
),
t6 as (
SELECT t5."id" ,
t4."rule",
t4.rule_type
FROM t5 ,t4
WHERE t5.evaluation_project_id=t4.project_id
and t5.node_type=t4.node_type
and t5.category=t4.category
and t5."name"=t4."name"
)
update evaluation_item_used
set "rule"=t6."rule",
rule_type=t6."rule_type"
FROM t6
WHERE evaluation_item_used."id"=t6."id"
;
-- 修复数据
with t1 as
( SELECT
evaluation_rule.id::TEXT,
evaluation_rule."name",
evaluation_rule.remark,
evaluation_rule.company_id::TEXT as "companyId",
evaluation_rule.creator_id::TEXT as "creatorId",
evaluation_rule."type",
evaluation_rule.rating,
evaluation_rule.score,
evaluation_rule.sys_type as "sysType"
FROM evaluation_rule
),
t2 as (
SELECT t1.id, row_to_json(t1.*) as row_j FROM t1
),
t3 as (
SELECT evaluation_item_used.id, t2.row_j
FROM evaluation_item_used
join t2 on t2.id= evaluation_item_used."rule"->>'id'
WHERE evaluation_item_used."rule"->'companyId' ISNULL
and evaluation_item_used."rule"->'id' is not null
)
update evaluation_item_used
SET "rule"=t3.row_j
FROM t3
WHERE evaluation_item_used.id = t3.id
;
-- 修改开始结束时间
UPDATE summary_evaluation
set begin_time = '2023-03-31 23:59:00+08',
end_time = '2023-04-02 23:59:00+08'
WHERE id=36
;
UPDATE summary_evaluation
set begin_time = '2023-04-02 23:59:00+08',
end_time = '2023-04-04 23:59:00+08'
WHERE id=37