USE [admin]
GO
/****** Object:  StoredProcedure [dbo].[usp_ssis_log_viewer]    Script Date: 03/17/2011 08:13:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER  proc [dbo].[usp_ssis_log_viewer]
    @PackageName varchar(100) = null
,	@bdt char(8) = null
,	@edt char(8) = null
as
if @bdt is null
begin
	set @bdt = convert(char(8), getdate()-1, 112)
	set @edt = convert(char(8), getdate()+1, 112)
end

if @PackageName is null
begin
	SELECT top 1000
		a.PackageName
	,	b.TaskName
	,   convert(varchar,datediff(second,a.ExecStartTime,isnull(a.ExecEndTime, getdate()))/60) + ':' +
	    convert(varchar,datediff(second,a.ExecStartTime,isnull(a.ExecEndTime, getdate())) % 60) +
	    case when a.ExecEndTime is not null then '(End)' else '' end as Exec_Gap
	,   convert(varchar,datediff(second,b.ExecStartTime,isnull(b.ExecEndTime, getdate()))/60) + ':' +
	    convert(varchar,datediff(second,b.ExecStartTime,isnull(b.ExecEndTime, getdate())) % 60) +
	    case when b.ExecEndTime is not null then '(End)' else '' end as Exec_Gap
	,	CASE WHEN b.Status = 'S' THEN 'Success'
         WHEN b.Status = 'R' THEN 'Running' 
         WHEN b.Status = 'E' THEN 'Failed' 
        END   TaskStatus
	,	a.StdBDT
	,	isnull(a.StdEDT,'') as StdEDT
	,	a.ExecStartTime
	,	a.ExecEndTime
	,	CASE WHEN a.Status = 'S' THEN 'Success'
			 WHEN a.Status = 'R' THEN 'Running' 
			 WHEN a.Status = 'E' THEN 'Failed' 
		END  PackageStatus
	
	,	b.ExecStartTime TaskExecStartTime
	,	b.ExecEndTime TaskExecEndTime
	,   b.StdEDT
	,	ISNULL(c.ErrorCode, 0) ErrorCode
	,	ISNULL(c.ErrorDesc, '') ErrorDesc
	FROM dbo.T_PackageHistory a
		INNER JOIN dbo.T_TaskHistory b
			ON a.ExecInstanceID = b.ExecInstanceID
		LEFT JOIN dbo.T_PackageErrorHistory c
			ON b.ExecInstanceID = c.ExecInstanceID
			AND b.TaskID = c.TaskID 
	WHERE a.ExecStartTime >= @bdt
	AND a.ExecStartTime < @edt
	AND (a.Status <> 'S' OR b.Status <> 'R')
	order by 
		a.ExecStartTime desc
	,	b.ExecStartTime desc
end ELSE
begin
	SELECT top 1000
		a.PackageName
	,	b.TaskName
	,   convert(varchar,datediff(second,a.ExecStartTime,isnull(a.ExecEndTime, getdate()))/60) + ':' +
	    convert(varchar,datediff(second,a.ExecStartTime,isnull(a.ExecEndTime, getdate())) % 60) +
	    case when a.ExecEndTime is not null then '(End)' else '' end as Exec_Gap
	,   convert(varchar,datediff(second,b.ExecStartTime,isnull(b.ExecEndTime, getdate()))/60) + ':' +
	    convert(varchar,datediff(second,b.ExecStartTime,isnull(b.ExecEndTime, getdate())) % 60) +
	    case when b.ExecEndTime is not null then '(End)' else '' end as Exec_Gap
	,	CASE WHEN b.Status = 'S' THEN 'Success'
         WHEN b.Status = 'R' THEN 'Running' 
         WHEN b.Status = 'E' THEN 'Failed' 
        END   TaskStatus
	,	a.StdBDT
	,	isnull(a.StdEDT,'') as StdEDT
	,	a.ExecStartTime
	,	a.ExecEndTime
	,	CASE WHEN a.Status = 'S' THEN 'Success'
			 WHEN a.Status = 'R' THEN 'Running' 
			 WHEN a.Status = 'E' THEN 'Failed' 
		END  PackageStatus
	
	,	b.ExecStartTime TaskExecStartTime
	,	b.ExecEndTime TaskExecEndTime
	,   b.StdEDT
	,	ISNULL(c.ErrorCode, 0) ErrorCode
	,	ISNULL(c.ErrorDesc, '') ErrorDesc
	FROM dbo.T_PackageHistory a
		INNER JOIN dbo.T_TaskHistory b
			ON a.ExecInstanceID = b.ExecInstanceID
		LEFT JOIN dbo.T_PackageErrorHistory c
			ON b.ExecInstanceID = c.ExecInstanceID
			AND b.TaskID = c.TaskID 
	WHERE a.PackageName like @PackageName
	And a.ExecStartTime >= @bdt
	AND a.ExecStartTime < @edt
	AND (a.Status <> 'S' OR b.Status <> 'R')
	AND c.ErrorCode < 0
	order by 
		a.ExecStartTime desc
	,	b.ExecStartTime desc
end