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
87
88
89
90
91
92
93
94
|
CREATE VIEW "public"."gtc_documents_view" AS (select "gtc_documents"."id", "gtc_documents"."type", "gtc_documents"."project_id", "gtc_documents"."revision", "gtc_documents"."file_name", "gtc_documents"."file_path", "gtc_documents"."file_size", "gtc_documents"."created_at", "gtc_documents"."created_by_id", "gtc_documents"."updated_at", "gtc_documents"."updated_by_id", "gtc_documents"."edit_reason", "gtc_documents"."is_active", "projects"."code", "projects"."name", created_by_user.name as "created_by_name", created_by_user.email as "created_by_email", updated_by_user.name as "updated_by_name", updated_by_user.email as "updated_by_email",
(
SELECT count(*)
FROM gtc_documents gd2
WHERE gd2.type = "gtc_documents"."type"
AND gd2.is_active = true
AND (
("gtc_documents"."type" = 'project' AND gd2.project_id = "gtc_documents"."project_id") OR
("gtc_documents"."type" = 'standard' AND gd2.project_id IS NULL)
)
)
as "total_documents_in_group",
(
SELECT max(revision)
FROM gtc_documents gd3
WHERE gd3.type = "gtc_documents"."type"
AND gd3.is_active = true
AND (
("gtc_documents"."type" = 'project' AND gd3.project_id = "gtc_documents"."project_id") OR
("gtc_documents"."type" = 'standard' AND gd3.project_id IS NULL)
)
)
as "latest_revision",
"gtc_documents"."revision" = (
SELECT max(revision)
FROM gtc_documents gd4
WHERE gd4.type = "gtc_documents"."type"
AND gd4.is_active = true
AND (
("gtc_documents"."type" = 'project' AND gd4.project_id = "gtc_documents"."project_id") OR
("gtc_documents"."type" = 'standard' AND gd4.project_id IS NULL)
)
)
as "is_latest_revision",
(
SELECT id
FROM gtc_documents gd5
WHERE gd5.type = "gtc_documents"."type"
AND gd5.is_active = true
AND gd5.revision < "gtc_documents"."revision"
AND (
("gtc_documents"."type" = 'project' AND gd5.project_id = "gtc_documents"."project_id") OR
("gtc_documents"."type" = 'standard' AND gd5.project_id IS NULL)
)
ORDER BY gd5.revision DESC
LIMIT 1
)
as "previous_revision_id",
(
SELECT id
FROM gtc_documents gd6
WHERE gd6.type = "gtc_documents"."type"
AND gd6.is_active = true
AND gd6.revision > "gtc_documents"."revision"
AND (
("gtc_documents"."type" = 'project' AND gd6.project_id = "gtc_documents"."project_id") OR
("gtc_documents"."type" = 'standard' AND gd6.project_id IS NULL)
)
ORDER BY gd6.revision ASC
LIMIT 1
)
as "next_revision_id",
CASE
WHEN "gtc_documents"."file_size" IS NULL THEN NULL
WHEN "gtc_documents"."file_size" < 1024 THEN "gtc_documents"."file_size" || ' B'
WHEN "gtc_documents"."file_size" < 1024 * 1024 THEN round("gtc_documents"."file_size" / 1024.0, 1) || ' KB'
WHEN "gtc_documents"."file_size" < 1024 * 1024 * 1024 THEN round("gtc_documents"."file_size" / (1024.0 * 1024), 1) || ' MB'
ELSE round("gtc_documents"."file_size" / (1024.0 * 1024 * 1024), 1) || ' GB'
END
as "file_size_formatted",
CASE
WHEN "gtc_documents"."project_id" IS NOT NULL THEN (
SELECT count(*)
FROM gtc_documents gd7
WHERE gd7.project_id = "gtc_documents"."project_id"
AND gd7.is_active = true
)
ELSE NULL
END
as "project_total_documents",
(
SELECT array_agg(revision ORDER BY revision)
FROM gtc_documents gd8
WHERE gd8.type = "gtc_documents"."type"
AND gd8.is_active = true
AND (
("gtc_documents"."type" = 'project' AND gd8.project_id = "gtc_documents"."project_id") OR
("gtc_documents"."type" = 'standard' AND gd8.project_id IS NULL)
)
)
as "revision_history",
"gtc_documents"."created_by_id" != "gtc_documents"."updated_by_id" OR
"gtc_documents"."created_at" != "gtc_documents"."updated_at"
as "has_edit_history" from "gtc_documents" left join "projects" on "gtc_documents"."project_id" = "projects"."id" left join users created_by_user on "gtc_documents"."created_by_id" = created_by_user.id left join users updated_by_user on "gtc_documents"."updated_by_id" = updated_by_user.id);
|
