rem	Report to show what has been passed/failed by dodo
rem
rem	mdf 4/9/2003
rem     ces 2/8/2006 - added prep fails summary   
rem   
rem

rem:entry title=Email


set pause off
set recsep off
set linesize 120
set pagesize 32000
set feed off
set verify off

column clonename	heading "Clone"		format A12
column id_prep		heading "Prep ID"	format 999999
column prep_number	heading "Prep"	        format 99
column description	heading "Status"	format A25
column statusdate	heading "Date"		format A10
column operator		heading "Email"		format A5
column remark		heading "Remarks"	format A30 word_wrapped

ttitle center "Dodo Pass/Fails by &1 - Summary" skip 2

select DSD.DESCRIPTION,COUNT(D.ID_PREP) as "Number updated"
from   DNAPREP D, DNAPREP_STATUS DS, DNAPREPSTATUSDICT DSD
where  D.ID_PREP   = DS.ID_PREP
AND    DS.STATUS   = DSD.ID_DICT
AND    DS.OPERATOR = '&1'
group by DSD.DESCRIPTION;


ttitle center "Dodo Prep Fails by &1 - Summary" skip 2

select  D.PREP_NUMBER , DSD.DESCRIPTION , COUNT(*) as "Number updated" 
from    DNAPREP D, DNAPREP_STATUS DS, DNAPREPSTATUSDICT DSD
where   D.ID_PREP       = DS.ID_PREP
AND     DS.STATUS       = DSD.ID_DICT
AND     DS.OPERATOR     = '&1'
AND     DSD.DESCRIPTION = 'Failed QC'
GROUP BY D.PREP_NUMBER, DSD.DESCRIPTION;


ttitle center "Dodo Pass/Fails by &1 - Details" skip 2

select D.CLONENAME, D.ID_PREP, D.PREP_NUMBER, DSD.DESCRIPTION, DS.STATUSDATE, DS.OPERATOR, ds.remark
from DNAPREP D, DNAPREP_STATUS DS, DNAPREPSTATUSDICT DSD
where D.ID_PREP = DS.ID_PREP
AND DS.STATUS   = DSD.ID_DICT
AND DS.OPERATOR = '&1'
order by description,clonename;

quit;
