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
|
DROP TABLE "vendor_documents" CASCADE;--> statement-breakpoint
CREATE VIEW "public"."vendor_documents_view" AS (
SELECT
d.id,
d.doc_number,
d.title,
d.status,
d.issued_date,
d.contract_id,
c.contract_no,
c.name AS contract_name,
c.vendor_id,
v.name AS vendor_name,
c.project_id,
p.code AS project_code,
p.name AS project_name,
p.type AS project_type,
(
SELECT id FROM issue_stages
WHERE document_id = d.id
ORDER BY created_at DESC LIMIT 1
) AS latest_stage_id,
(
SELECT stage_name FROM issue_stages
WHERE document_id = d.id
ORDER BY created_at DESC LIMIT 1
) AS latest_stage_name,
(
SELECT plan_date FROM issue_stages
WHERE document_id = d.id
ORDER BY created_at DESC LIMIT 1
) AS latest_stage_plan_date,
(
SELECT actual_date FROM issue_stages
WHERE document_id = d.id
ORDER BY created_at DESC LIMIT 1
) AS latest_stage_actual_date,
(
SELECT r.id FROM revisions r
JOIN issue_stages i ON r.issue_stage_id = i.id
WHERE i.document_id = d.id
ORDER BY r.created_at DESC LIMIT 1
) AS latest_revision_id,
(
SELECT r.revision FROM revisions r
JOIN issue_stages i ON r.issue_stage_id = i.id
WHERE i.document_id = d.id
ORDER BY r.created_at DESC LIMIT 1
) AS latest_revision,
(
SELECT r.uploader_type FROM revisions r
JOIN issue_stages i ON r.issue_stage_id = i.id
WHERE i.document_id = d.id
ORDER BY r.created_at DESC LIMIT 1
) AS latest_revision_uploader_type,
(
SELECT r.uploader_name FROM revisions r
JOIN issue_stages i ON r.issue_stage_id = i.id
WHERE i.document_id = d.id
ORDER BY r.created_at DESC LIMIT 1
) AS latest_revision_uploader_name,
(
SELECT COUNT(*) FROM document_attachments a
JOIN revisions r ON a.revision_id = r.id
JOIN issue_stages i ON r.issue_stage_id = i.id
WHERE i.document_id = d.id
) AS attachment_count,
d.created_at,
d.updated_at
FROM documents d
JOIN contracts c ON d.contract_id = c.id
JOIN vendors v ON c.vendor_id = v.id
JOIN projects p ON c.project_id = p.id
);
|
