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