-- 创建新表 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 '周期评估是否下发'; -- 处理evaluation_project表字段summary_state 初始值 UPDATE evaluation_project SET summary_state = 1 WHERE end_time <= now() AND deleted_at ISNULL; -- 变更user表字段 ALTER TABLE "public"."user" ADD COLUMN "parent_id" int8 DEFAULT 0; 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'