2023-05-04.sql 3.3 KB
-- 数据修复
-- 修复node_task表数据
WITH t_node_task AS (
    SELECT
        *
    FROM (
        SELECT
            row_number() OVER (PARTITION BY project_id) AS row_id,
        *
    FROM
        node_task
    WHERE
        cycle_id = 1653724699349225472
        AND node_type = 1) 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'