diff options
Diffstat (limited to 'db/migrations/0054_polite_darwin.sql')
| -rw-r--r-- | db/migrations/0054_polite_darwin.sql | 81 |
1 files changed, 81 insertions, 0 deletions
diff --git a/db/migrations/0054_polite_darwin.sql b/db/migrations/0054_polite_darwin.sql new file mode 100644 index 00000000..ead16a74 --- /dev/null +++ b/db/migrations/0054_polite_darwin.sql @@ -0,0 +1,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 + );
\ No newline at end of file |