2023-05-04.sql
3.3 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
-- 数据修复
-- 修复node_task表数据
WITH t_node_task AS (
SELECT
*
FROM (
SELECT
row_number() OVER (PARTITION BY project_id,
node_type) AS row_id,
*
FROM
node_task
WHERE
cycle_id = 1653724699349225472) AS t_node_task
WHERE
row_id = 1
ORDER BY
project_id)
UPDATE
node_task
SET
deleted_at = now()
WHERE
cycle_id = 1653724699349225472
AND node_task.id NOT IN (
SELECT
"id"
FROM
t_node_task);
-- 修复staff_assess 数据
WITH t_staff_assess AS (
SELECT
row_number() OVER (PARTITION BY evaluation_project_id,
"types") AS row_id,
*
FROM
staff_assess
WHERE
1 = 1
AND cycle_id = 1653724699349225472
AND NOT EXISTS (
SELECT
*
FROM
staff_assess_content
WHERE
staff_assess_content.staff_assess_id = staff_assess.id)
),
t_staff_assess_2 AS (
SELECT
*
FROM
t_staff_assess
WHERE
row_id = 1)
UPDATE
staff_assess
SET
deleted_at = now()
WHERE
1 = 1
AND cycle_id = 1653724699349225472
AND staff_assess.id NOT IN (
SELECT
id
FROM
t_staff_assess_2);
-- 修复staff_assess_task 数据
UPDATE
staff_assess_task
SET
step_list = '[{"sortBy":1,"endTime":"2023-05-04T08:30:00+08:00","beginTime":"2023-05-03T00:00:00+08:00","linkNodeId":1651883218342776833,"linkNodeName":"填写自评反馈","linkNodeType":1},{"sortBy":2,"endTime":"2023-05-04T08:30:00+08:00","beginTime":"2023-05-03T00:00:00+08:00","linkNodeId":1651883218342776834,"linkNodeName":"360°邀请","linkNodeType":2},{"sortBy":3,"endTime":"2023-05-04T08:30:00+08:00","beginTime":"2023-05-03T00:00:00+08:00","linkNodeId":1651883218342776835,"linkNodeName":"360°评估","linkNodeType":3},{"sortBy":4,"endTime":"2023-05-04T08:30:00+08:00","beginTime":"2023-05-03T00:00:00+08:00","linkNodeId":1651883218342776836,"linkNodeName":"上级评估","linkNodeType":4},{"sortBy":5,"endTime":"2023-05-04T08:30:00+08:00","beginTime":"2023-05-03T00:00:00+08:00","linkNodeId":1651883218342776837,"linkNodeName":"绩效结果查看","linkNodeType":5}]'
WHERE
1 = 1
AND cycle_id = 1653724699349225472
AND begin_day = '2023-05-03';
UPDATE
staff_assess_task
SET
step_list = '[{"sortBy":1,"endTime":"2023-05-05T08:30:00+08:00","beginTime":"2023-05-04T00:00:00+08:00","linkNodeId":1653723396699066369,"linkNodeName":"填写自评反馈","linkNodeType":1},{"sortBy":2,"endTime":"2023-05-05T08:30:00+08:00","beginTime":"2023-05-04T00:00:00+08:00","linkNodeId":1653723396699066370,"linkNodeName":"360°邀请","linkNodeType":2},{"sortBy":3,"endTime":"2023-05-05T08:30:00+08:00","beginTime":"2023-05-04T00:00:00+08:00","linkNodeId":1653723396699066371,"linkNodeName":"360°评估","linkNodeType":3},{"sortBy":4,"endTime":"2023-05-05T08:30:00+08:00","beginTime":"2023-05-04T00:00:00+08:00","linkNodeId":1653723396699066372,"linkNodeName":"上级评估","linkNodeType":4},{"sortBy":5,"endTime":"2023-05-05T08:30:00+08:00","beginTime":"2023-05-04T00:00:00+08:00","linkNodeId":1653723396699066373,"linkNodeName":"绩效结果查看","linkNodeType":5}]'
WHERE
1 = 1
AND cycle_id = 1653724699349225472
AND begin_day = '2023-05-04'