diff options
Diffstat (limited to 'lib/swp/actions.ts')
| -rw-r--r-- | lib/swp/actions.ts | 21 |
1 files changed, 14 insertions, 7 deletions
diff --git a/lib/swp/actions.ts b/lib/swp/actions.ts index 7411f414..f58793e2 100644 --- a/lib/swp/actions.ts +++ b/lib/swp/actions.ts @@ -110,7 +110,7 @@ export async function fetchSwpDocuments(params: SwpTableParams) { sync_status: swpDocuments.sync_status, last_synced_at: swpDocuments.last_synced_at, revision_count: sql<number>`COUNT(DISTINCT ${swpDocumentRevisions.id})::int`, - file_count: sql<number>`COUNT(${swpDocumentFiles.id})::int`, + file_count: sql<number>`COUNT(DISTINCT ${swpDocumentFiles.id})::int`, }) .from(swpDocuments) .leftJoin(swpDocumentRevisions, eq(swpDocuments.DOC_NO, swpDocumentRevisions.DOC_NO)) @@ -162,14 +162,21 @@ export async function fetchDocumentRevisions(docNo: string) { OFDC_NO: swpDocumentRevisions.OFDC_NO, sync_status: swpDocumentRevisions.sync_status, last_synced_at: swpDocumentRevisions.last_synced_at, - file_count: sql<number>`( - SELECT COUNT(*)::int - FROM swp.swp_document_files f - WHERE f.revision_id = ${swpDocumentRevisions.id} - )`, + file_count: sql<number>`COUNT(DISTINCT ${swpDocumentFiles.id})::int`, }) .from(swpDocumentRevisions) + .leftJoin(swpDocumentFiles, eq(swpDocumentRevisions.id, swpDocumentFiles.revision_id)) .where(eq(swpDocumentRevisions.DOC_NO, docNo)) + .groupBy( + swpDocumentRevisions.id, + swpDocumentRevisions.DOC_NO, + swpDocumentRevisions.REV_NO, + swpDocumentRevisions.STAGE, + swpDocumentRevisions.ACTV_NO, + swpDocumentRevisions.OFDC_NO, + swpDocumentRevisions.sync_status, + swpDocumentRevisions.last_synced_at + ) .orderBy(desc(swpDocumentRevisions.REV_NO)); return revisions; @@ -268,7 +275,7 @@ export async function fetchSwpStats(projNo?: string) { .select({ total_documents: sql<number>`COUNT(DISTINCT ${swpDocuments.DOC_NO})::int`, total_revisions: sql<number>`COUNT(DISTINCT ${swpDocumentRevisions.id})::int`, - total_files: sql<number>`COUNT(${swpDocumentFiles.id})::int`, + total_files: sql<number>`COUNT(DISTINCT ${swpDocumentFiles.id})::int`, last_sync: sql<Date>`MAX(${swpDocuments.last_synced_at})`, }) .from(swpDocuments) |