Category=Status
Name=Show Potential Transcribing Candidates
Description=Show documents with indexing data for pages without transcription data
Heading=Show documents with indexing data for pages without transcription data
SQL=		\
	SELECT	\
		DOCUMENTID, 	\
		PageCount, \
		ImageCount, 	\
		case when PagesIndexed > 0 then CONCAT(PagesIndexed, ' (', coalesce(ROUND(PagesIndexed / PageCount * 100), 0), '% ', VerseCount, ' verses)') else 0 end PagesIndexed,	\
		PagesTranscribed,	\
		AssignedTo	\
	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=3) AssignedTo,	\
			(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	\
			 WHERE EXISTS (	\
				 SELECT 'x'	\
					 FROM SURROGATEIMAGE d	\
					 WHERE d.DOCUMENTID = h.DOCUMENTID	\
			)	\
		) h2	\
		 WHERE PagesIndexed > 0 AND PagesTranscribed < PagesIndexed	\
 		 order by DOCUMENTID