审查视图

sql/2023-03-15.sql 4.7 KB
tangxvhui authored
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
-- 创建新表 summary_evaluation
CREATE TABLE public.summary_evaluation (
    id bigserial NOT NULL,
    company_id int8 NULL,
    evaluation_project_id int8 NULL,
    evaluation_project_name text NULL,
    cycle_id int8 NULL,
    cycle_name text NULL,
    target_user jsonb NULL,
    target_department jsonb NULL,
    executor jsonb NULL,
    "types" int8 NULL,
    status text NULL,
    check_result text NULL,
    begin_time timestamptz NULL,
    end_time timestamptz NULL,
    total_score text NULL,
    created_at timestamptz NULL,
    updated_at timestamptz NULL,
    deleted_at timestamptz NULL,
    node_id int8 NULL DEFAULT 0,
    total_rating jsonb NULL,
    CONSTRAINT summary_evaluation_pkey PRIMARY KEY (id)
);

-- 创建新表 summary_evaluation_value
CREATE TABLE public.summary_evaluation_value (
    id bigserial NOT NULL,
    evaluation_item_id int8 NULL,
    summary_evaluation_id int8 NULL,
    value text NULL,
    score text NULL,
    "types" int8 NULL,
    remark text NULL,
    created_at timestamptz NULL,
    updated_at timestamptz NULL,
    weight numeric NULL DEFAULT 0,
    rating jsonb NULL,
    CONSTRAINT summary_evaluation_value_pkey PRIMARY KEY (id)
);

-- 创建新表 evaluation_item_used
CREATE TABLE public.evaluation_item_used (
    id bigserial NOT NULL,
    company_id int8 NULL,
    evaluation_project_id int8 NULL,
    node_id int8 NULL,
    node_type int8 NULL,
    sort_by int8 NULL,
    category text NULL,
    "name" text NULL,
    prompt_title text NULL,
    prompt_text text NULL,
    entry_items jsonb NULL,
    rule_type int8 NULL,
    "rule" jsonb NULL,
    weight numeric NULL DEFAULT 0,
    required int8 NULL,
    created_at timestamptz NULL,
    updated_at timestamptz NULL,
    evaluator_id int8 NULL DEFAULT 0,
    CONSTRAINT evaluation_item_used_pkey PRIMARY KEY (id)
);

-- 创建新表 log_sms
CREATE TABLE public.log_sms (
    id bigserial NOT NULL,
    phone text NULL,
    template_id int8 NULL,
    "template" text NULL,
    value jsonb NULL,
    created_at timestamptz NULL,
    "result" text NULL,
    status text NULL,
    "from" text NULL,
    "index" int8 NULL,
    execute_at timestamptz NULL,
    CONSTRAINT log_sms_pkey PRIMARY KEY (id)
);

-- 变更evaluation_project表字段
ALTER TABLE public.evaluation_project
    ADD summary_state int4 NOT NULL DEFAULT 0;

COMMENT ON COLUMN public.evaluation_project.summary_state IS '周期评估是否下发';
tangxvhui authored
87 88 89 90 91 92 93
-- 处理evaluation_project表字段summary_state 初始值
UPDATE
    evaluation_project
SET
    summary_state = 1
WHERE
    end_time <= now()
tangxvhui authored
94 95 96 97 98
    AND deleted_at ISNULL;

-- 变更user表字段
ALTER TABLE "public"."user"
    ADD COLUMN "parent_id" int8 DEFAULT 0;
tangxvhui authored
99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173

COMMENT ON COLUMN "public"."user"."parent_id" IS '上级ID';

-- 抽取evaluation_project旧数据到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
    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)
    INSERT INTO public.evaluation_item_used (company_id, evaluation_project_id, node_id, node_type, sort_by, category, "name", prompt_title, prompt_text, entry_items, rule_type, "rule", weight, required, created_at, updated_at, evaluator_id)
    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,
    0,
    t2.node_contents ->> 'category' AS "category",
    t2.node_contents ->> 'name' AS "name",
    t2.node_contents ->> 'promptTitle' AS "prompt_title",
    t2.node_contents ->> 'promptText' AS "prompt_text",
    t2.node_contents -> 'entryItems' AS "entry_items",
    cast(t2.node_contents #>> '{rule,type}' AS int8) AS rule_type,
    t2.node_contents -> 'rule' AS "rule",
    cast(t2.node_contents ->> 'weight' AS decimal) AS "weight",
    cast(t2.node_contents ->> 'required' AS int8) AS "required",
    now(),
    now(),
    cast(t2.node_contents ->> 'evaluatorId' AS int8) AS "evaluator_id"
FROM
    t2;

-- evaluation_item_used 初始赋值 sort_by
UPDATE
    evaluation_item_used
SET
    sort_by = evaluation_item_used.id;

-- evaluation_item_used 修正required 的值
UPDATE
    evaluation_item_used
SET
    required = 1
WHERE
    required = 0
    OR required ISNULL;

--  evaluation_item_used修正weight的 值
UPDATE
    evaluation_item_used
SET
    weight = 0
WHERE
    weight ISNULL;

-- evaluation_item_used 修正rule的值
UPDATE
    evaluation_item_used
SET
    "rule" = '{}'
WHERE
    "rule" = 'null'