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
|
CREATE VIEW "public"."tbe_documents_view" AS (select dr.id as "document_review_id", COALESCE(dr.tbe_session_id, vd.tbe_session_id) as "tbe_session_id",
CASE
WHEN dr.id IS NOT NULL THEN dr.document_source
WHEN vd.id IS NOT NULL THEN 'vendor'
ELSE NULL
END
as "document_source", COALESCE(dr.buyer_attachment_id, vd.id) as "document_id", COALESCE(dr.document_type, vd.document_type) as "document_type", COALESCE(dr.document_name, vd.file_name) as "document_name", COALESCE(ba_rev.original_file_name, vd.original_file_name) as "original_file_name", COALESCE(ba_rev.file_path, vd.file_path) as "file_path", COALESCE(ba_rev.file_size, vd.file_size) as "file_size", COALESCE(ba_rev.file_type, vd.file_type) as "file_type", COALESCE(dr.review_status, vd.review_status, '미검토') as "review_status", dr.technical_compliance as "technical_compliance", dr.quality_acceptable as "quality_acceptable", COALESCE(dr.requires_revision, false) as "requires_revision", COALESCE(dr.has_pdftron_comments, false) as "has_pdftron_comments", dr.pdftron_document_id as "pdftron_document_id", COALESCE(dr.pdftron_annotation_count, 0) as "pdftron_annotation_count", COALESCE(dr.reviewed_by, vd.reviewed_by) as "reviewed_by", COALESCE(dr.reviewed_at, vd.reviewed_at) as "reviewed_at", COALESCE(dr.review_comments, vd.review_comments) as "review_comments", vd.submitted_by as "submitted_by", vd.submitted_at as "submitted_at", COALESCE(dr.created_at, vd.submitted_at) as "created_at", COALESCE(dr.updated_at, vd.submitted_at) as "updated_at" from (
SELECT * FROM rfq_last_tbe_document_reviews
) dr
FULL OUTER JOIN (
SELECT * FROM rfq_last_tbe_vendor_documents
) vd ON false
left join "rfq_last_attachments" "ba" on dr.buyer_attachment_id = "ba"."id" left join "rfq_last_attachment_revisions" "ba_rev" on dr.buyer_attachment_revision_id = "ba_rev"."id");--> statement-breakpoint
CREATE VIEW "public"."tbe_last_view" AS (select "rfq_last_tbe_sessions"."id" as "tbe_session_id", "rfq_last_tbe_sessions"."session_code" as "session_code", "rfq_last_tbe_sessions"."session_title" as "session_title", "rfq_last_tbe_sessions"."session_type" as "session_type", "rfq_last_tbe_sessions"."status" as "session_status", "rfq_last_tbe_sessions"."evaluation_result" as "evaluation_result", "rfq_last_tbe_sessions"."conditional_requirements" as "conditional_requirements", "rfq_last_tbe_sessions"."conditions_fulfilled" as "conditions_fulfilled", "rfqs_last"."id" as "rfq_id", "rfqs_last"."rfq_code" as "rfq_code", "rfqs_last"."rfq_type" as "rfq_type", "rfqs_last"."rfq_title" as "rfq_title", "rfqs_last"."series" as "series", "rfqs_last"."status" as "rfq_status", "rfqs_last"."due_date" as "rfq_due_date", "rfqs_last"."package_no" as "package_no", "rfqs_last"."package_name" as "package_name", "rfqs_last"."pr_number" as "pr_number", "rfqs_last"."pr_issue_date" as "pr_issue_date", "rfqs_last"."project_id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "rfqs_last"."project_company" as "project_company", "rfqs_last"."project_flag" as "project_flag", "rfqs_last"."project_site" as "project_site", "rfqs_last"."item_code" as "item_code", "rfqs_last"."item_name" as "item_name", "vendors"."id" as "vendor_id", "vendors"."vendor_code" as "vendor_code", "vendors"."vendor_name" as "vendor_name", "vendors"."country" as "vendor_country", "vendors"."email" as "vendor_email", "vendors"."status" as "vendor_status", "rfq_last_details"."id" as "rfq_detail_id", "rfq_last_details"."short_list" as "short_list", "rfq_last_details"."return_yn" as "return_yn", "rfq_last_tbe_sessions"."planned_start_date" as "planned_start_date", "rfq_last_tbe_sessions"."actual_start_date" as "actual_start_date", "rfq_last_tbe_sessions"."planned_end_date" as "planned_end_date", "rfq_last_tbe_sessions"."actual_end_date" as "actual_end_date", "rfq_last_tbe_sessions"."lead_evaluator_id" as "lead_evaluator_id", lead_user.name as "lead_evaluator_name", (
SELECT COUNT(*)
FROM rfq_pr_items
WHERE rfqs_last_id = "rfqs_last"."id"
) as "pr_items_count", (
SELECT COUNT(*)
FROM rfq_pr_items
WHERE rfqs_last_id = "rfqs_last"."id"
AND major_yn = true
) as "major_items_count", (
SELECT COUNT(*)
FROM rfq_last_tbe_document_reviews
WHERE tbe_session_id = "rfq_last_tbe_sessions"."id"
AND document_source = 'buyer'
) as "buyer_documents_count", (
SELECT COUNT(*)
FROM rfq_last_tbe_vendor_documents
WHERE tbe_session_id = "rfq_last_tbe_sessions"."id"
) as "vendor_documents_count", (
SELECT COUNT(*)
FROM rfq_last_tbe_document_reviews
WHERE tbe_session_id = "rfq_last_tbe_sessions"."id"
AND review_status IN ('검토완료', '승인')
) as "reviewed_documents_count", (
SELECT COUNT(*)
FROM rfq_last_tbe_pdftron_comments pc
JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id
WHERE dr.tbe_session_id = "rfq_last_tbe_sessions"."id"
) as "total_comments_count", (
SELECT COUNT(*)
FROM rfq_last_tbe_pdftron_comments pc
JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id
WHERE dr.tbe_session_id = "rfq_last_tbe_sessions"."id"
AND pc.status = 'open'
) as "unresolved_comments_count", "rfq_last_tbe_sessions"."created_at" as "created_at", "rfq_last_tbe_sessions"."updated_at" as "updated_at" from "rfq_last_tbe_sessions" left join "rfqs_last" on "rfq_last_tbe_sessions"."rfqs_last_id" = "rfqs_last"."id" left join "rfq_last_details" on "rfq_last_tbe_sessions"."rfq_last_details_id" = "rfq_last_details"."id" left join "vendors" on "rfq_last_tbe_sessions"."vendor_id" = "vendors"."id" left join "projects" on "rfqs_last"."project_id" = "projects"."id" left join "users" "lead_user" on "rfq_last_tbe_sessions"."lead_evaluator_id" = "lead_user"."id");
|
