use openaxes

declare @investigation int
declare @importBatch int

set @investigation = (select top 1 id from investigation (nolock)
                    where status in (4, 30,31,32, 40, 41))
if @investigation is null
begin

    set @investigation = (select top 1 id from investigation (nolock)
                    where status in (5,6)
                    order by id desc)

end

select id, name, status,
    datediff(mi, ProcessingStartDate, isnull(processingenddate, getutcdate())) 'Processing (min)',
    datediff(mi, DataMoveStartDate, DataMoveenddate) 'DataMove (min)',
    datediff(mi, DataCleanupStartDate, DataCleanupEndDate) 'DataCleanup (min)',
    datediff(mi, IndexFlushStartDate, IndexFlushEndDate) 'IndexFlush (min)'
from investigation (nolock) where id = @investigation

select * from ImportBatch (nolock) where Investigation = @investigation
set @importBatch = (select top 1 id from ImportBatch (nolock) where investigation = @investigation)

select ibf.servername, ibf.status, count(1)
from ImportBatchFile (nolock) ibf
join ImportBatch (nolock) ib on ibf.ImportBatch = ib.id
where ib.investigation = @investigation
group by ibf.servername, ibf.status
order by ibf.servername, ibf.status

if (select count(1) from ProcessingItem (nolock)) = 0
begin

    select 'QueueItem' 'Table', status, count(1)
    from queueitem (nolock)
    where InvestigationId = @investigation
    group by status
    order by status

    SELECT * from InvestigationReport where investigation = @investigation

end
else
begin

    select servername, status, count(1)
    from ArchiveBatch (nolock)
    where CreatedDate > (select ProcessingStartDate from investigation where id = @investigation)
    group by ServerName, status
    order by ServerName, status


    select 'ProcessingItem' 'Table', status, servername, count(1)
    from ProcessingItem (nolock)
    group by status, servername
    order by status, servername

end
if (SELECT count(1) from ImportBatchFile (nolock)
    where ImportBatch = @importBatch
        and status = 99 and retries < 8
        and errorcode not in (3002, 3004, 3006)) > 0
BEGIN

    SELECT *
    from ImportBatchFile (nolock)
    where ImportBatch = @importBatch
        and status = 99 and retries < 8
        and errorcode not in (3002, 3004, 3006)

END