Category=Status
Name=Show Full Status For Every Document
Description=Show Page, Image, Indexing, and Transcription data for all documents
Heading=Show Page, Image, Indexing, and Transcription data for all documents
SQL=		\
	SELECT	\
		DOCUMENTID, 	\
		PageCount, \
		ImageCount, 	\
		case when PagesIndexed > 0 then CONCAT(PagesIndexed, ' (', coalesce(ROUND(PagesIndexed / PageCount * 100), 0), '% ', VerseCount, ' verses)') else 0 end PagesIndexed,	\
		IndexingAssignedTo,	\
		case when PagesTranscribed > 0 then CONCAT(PagesTranscribed, ' (', coalesce(ROUND(PagesTranscribed / PageCount * 100), 0), '%)') else 0 end PagesTranscribed,	\
		TranscribingAssignedTo	\
	\
	FROM \
		(SELECT h.*, \
			(SELECT COUNT(1)               FROM PAGE                   d WHERE d.DOCUMENTID = h.DOCUMENTID) PageCount, 	\
			(SELECT COUNT(1)               FROM SURROGATEIMAGE         d WHERE d.DOCUMENTID = h.DOCUMENTID) ImageCount,	\
			(SELECT COUNT(DISTINCT PAGEID) FROM BIBLICALCONTENT        d WHERE d.DOCUMENTID = h.DOCUMENTID) PagesIndexed,	\
			(SELECT min(ASSIGNEDTO)        FROM TASK                   d WHERE d.OBJECTID = h.DOCUMENTID and TASKTYPEID=2) IndexingAssignedTo,	\
			(SELECT min(ASSIGNEDTO)        FROM TASK                   d WHERE d.OBJECTID = h.DOCUMENTID and TASKTYPEID=3) TranscribingAssignedTo,	\
			(SELECT count(VERSE)           FROM BIBLICALCONTENT        d WHERE d.DOCUMENTID = h.DOCUMENTID) VerseCount,		\
			(SELECT count(SURROGATEID)     FROM SURROGATETRANSCRIPTION d WHERE d.DOCUMENTID = h.DOCUMENTID) PagesTranscribed \
			 FROM DOCUMENT h	\
		) h2	\
 		 order by DOCUMENTID