-- =============================================
-- Author:		Berni Francesco
-- Create date: 24 Aprile 2009
-- Description: 
-- =============================================
-- [C6MartPeriodico].[GC_CONVALIDAREPORTSELECT] 
CREATE procedure [C6MartPeriodico].[GC_CONVALIDAREPORTSELECT]
AS
BEGIN
Declare @DiagnosiVerificati as int
Declare @DiagnosiNonVerificati as int
Declare @DiagnosiOK as int
Declare @DiagnosiKO as int
Declare @MonitoraggioVerificati as int
Declare @MonitoraggioNonVerificati as int
Declare @MonitoraggioOK as int
Declare @MonitoraggioKO as int
-- Totali
Declare @TotaleDiagnosi as int
Declare @TotaleMonitoraggio as int
Declare @TotaleVerificati as int
Declare @TotaleNonVerificati as int
Declare @TotaleOK as int
Declare @TotaleKO as int
-- Diagnosi
-- Diagnosi Verificati
SELECT @DiagnosiVerificati = count(distinct B.IDREPORT) 
	FROM
		C6Mart.CONTRATTOSEI  A INNER JOIN
        C6MartPeriodico.GESTIONE_PDF_FTP B ON 
		A.Rete = B.Rete AND 
        A.Cod_Fiscale = B.CodiceFiscale 
	Left outer JOIN C6MartPeriodico.VERIFICHECAMPIONE C
	ON B.IDREPORT  = C.IDREPORT
	WHERE  
	B.[TipoReport] = 'D' 
	AND ISNULL(B.FlagInvio,'N') = 'N'
	AND ISNULL(B.DEFINITIVO, 0) = 0
	AND C.datafine is  null 
	AND ISNULL(C.Verifica, 0) = 1
-- Diagnosi Non Verificati
SELECT @DiagnosiNonVerificati = count(distinct B.IDREPORT) 
	FROM
		C6Mart.CONTRATTOSEI  A INNER JOIN
        C6MartPeriodico.GESTIONE_PDF_FTP B ON 
		A.Rete = B.Rete AND 
        A.Cod_Fiscale = B.CodiceFiscale 
	Left outer JOIN C6MartPeriodico.VERIFICHECAMPIONE C
	ON B.IDREPORT  = C.IDREPORT
	WHERE  
	B.[TipoReport] = 'D' 
	AND ISNULL(B.FlagInvio,'N') = 'N'
	AND ISNULL(B.DEFINITIVO, 0) = 0
	AND C.datafine is  null 
	AND ISNULL(C.Verifica, 0) = 0
-- Diagnosi OK
SELECT @DiagnosiOK = count(distinct B.IDREPORT)
	FROM
		C6Mart.CONTRATTOSEI  A INNER JOIN
        C6MartPeriodico.GESTIONE_PDF_FTP B ON 
		A.Rete = B.Rete AND 
        A.Cod_Fiscale = B.CodiceFiscale 
	Left outer JOIN C6MartPeriodico.VERIFICHECAMPIONE C
	ON B.IDREPORT  = C.IDREPORT
	WHERE  
	B.[TipoReport] = 'D' 
	AND ISNULL(B.FlagInvio,'N') = 'N'
	AND ISNULL(B.DEFINITIVO, 0) = 0
	AND C.datafine is  null 
	and ISNULL(C.Esito,'-') = 'OK' 
-- Diagnosi KO
SELECT @DiagnosiKO = count(distinct B.IDREPORT) 
	FROM
		C6Mart.CONTRATTOSEI  A INNER JOIN
        C6MartPeriodico.GESTIONE_PDF_FTP B ON 
		A.Rete = B.Rete AND 
        A.Cod_Fiscale = B.CodiceFiscale 
	Left outer JOIN C6MartPeriodico.VERIFICHECAMPIONE C
	ON B.IDREPORT  = C.IDREPORT
	WHERE  
	B.[TipoReport] = 'D' 
	AND ISNULL(B.FlagInvio,'N') = 'N'
	AND ISNULL(B.DEFINITIVO, 0) = 0
	AND C.datafine is  null 
	and ISNULL(C.Esito,'-') = 'KO' 
-- Monitoraggio
-- Monitoraggio Verificati
SELECT @MonitoraggioVerificati = count(distinct B.IDREPORT) 
		FROM
		C6Mart.CONTRATTOSEI  A INNER JOIN
        C6MartPeriodico.GESTIONE_PDF_FTP B ON 
		A.Rete = B.Rete AND 
        A.Cod_Fiscale = B.CodiceFiscale 
	Left outer JOIN C6MartPeriodico.VERIFICHECAMPIONE C
	ON B.IDREPORT  = C.IDREPORT
	WHERE  
	B.[TipoReport] = 'M' 
	AND ISNULL(B.FlagInvio,'N') = 'N'
	AND ISNULL(B.DEFINITIVO, 0) = 0
	AND C.datafine is  null 
	AND ISNULL(C.Verifica, 0) = 1
-- Monitoraggio Non Verificati
SELECT @MonitoraggioNonVerificati = count(distinct B.IDREPORT) 
	FROM
		C6Mart.CONTRATTOSEI  A INNER JOIN
        C6MartPeriodico.GESTIONE_PDF_FTP B ON 
		A.Rete = B.Rete AND 
        A.Cod_Fiscale = B.CodiceFiscale 
	Left outer JOIN C6MartPeriodico.VERIFICHECAMPIONE C
	ON B.IDREPORT  = C.IDREPORT
	WHERE  
	B.[TipoReport] = 'M' 
	AND ISNULL(B.FlagInvio,'N') = 'N'
	AND ISNULL(B.DEFINITIVO, 0) = 0
	AND C.datafine is  null 
	AND ISNULL(C.Verifica, 0) = 0
-- Monitoraggio OK
SELECT @MonitoraggioOK = count(distinct B.IDREPORT) 
	FROM
		C6Mart.CONTRATTOSEI  A INNER JOIN
        C6MartPeriodico.GESTIONE_PDF_FTP B ON 
		A.Rete = B.Rete AND 
        A.Cod_Fiscale = B.CodiceFiscale 
	Left outer JOIN C6MartPeriodico.VERIFICHECAMPIONE C
	ON B.IDREPORT  = C.IDREPORT
	WHERE  
	B.[TipoReport] = 'M' 
	AND ISNULL(B.FlagInvio,'N') = 'N'
	AND ISNULL(B.DEFINITIVO, 0) = 0
	AND C.datafine is  null 
	and ISNULL(C.Esito,'-') = 'OK' 
-- Monitoraggio KO
SELECT @MonitoraggioKO = count(distinct B.IDREPORT) 
		FROM
		C6Mart.CONTRATTOSEI  A INNER JOIN
        C6MartPeriodico.GESTIONE_PDF_FTP B ON 
		A.Rete = B.Rete AND 
        A.Cod_Fiscale = B.CodiceFiscale 
	Left outer JOIN C6MartPeriodico.VERIFICHECAMPIONE C
	ON B.IDREPORT  = C.IDREPORT
	WHERE  
	B.[TipoReport] = 'M' 
	AND ISNULL(B.FlagInvio,'N') = 'N'
	AND ISNULL(B.DEFINITIVO, 0) = 0
	AND C.datafine is  null 
	and ISNULL(C.Esito,'-') = 'KO' 
--print convert(varchar, @DiagnosiVerificati)
set @TotaleDiagnosi = @DiagnosiVerificati+@DiagnosiNonVerificati--+	@DiagnosiOK+@DiagnosiKO
set @TotaleMonitoraggio = @MonitoraggioVerificati+@MonitoraggioNonVerificati --+	@MonitoraggioOK+@MonitoraggioKO
set @TotaleVerificati = @DiagnosiVerificati+@MonitoraggioVerificati
set @TotaleNonVerificati = @DiagnosiNonVerificati+@MonitoraggioNonVerificati
set @TotaleOK = @DiagnosiOK+@MonitoraggioOK
set @TotaleKO = @DiagnosiKO+@MonitoraggioKO
Select riga, Report, Diagnosi,UrlDiagnosi, Monitoraggio,UrlMonitoraggio, UrlTotale, Totale
from
(
	Select	1 as riga, 
			'Totale' as Report, 
			@TotaleDiagnosi AS Diagnosi, 
			'VerificheCampione.aspx?tiporeport=D&prov=CR' As UrlDiagnosi,
			@TotaleMonitoraggio AS Monitoraggio,
			'VerificheCampione.aspx?tiporeport=M&prov=CR' As UrlMonitoraggio,
			'VerificheCampione.aspx?prov=CR' As UrlTotale,
			@TotaleDiagnosi + @TotaleMonitoraggio as Totale
    union
	Select	2 as riga, 
			'Verificati' as Report, 
			@DiagnosiVerificati AS Diagnosi,
			'VerificheCampione.aspx?tiporeport=D&verificati=1&prov=CR' As UrlDiagnosi, 
			@MonitoraggioVerificati AS Monitoraggio,
			'VerificheCampione.aspx?tiporeport=M&verificati=1&prov=CR' As UrlMonitoraggio,
			'VerificheCampione.aspx?verificati=1&prov=CR' As UrlTotale,
			@TotaleVerificati as Totale
 union
	Select	3 as riga, 
			'Non Verificati' as Report, 
			@DiagnosiNonVerificati AS Diagnosi, 
			'VerificheCampione.aspx?tiporeport=D&verificati=0&prov=CR' As UrlDiagnosi, 
			@MonitoraggioNonVerificati AS Monitoraggio,
			'VerificheCampione.aspx?tiporeport=M&verificati=0&prov=CR' As UrlMonitoraggio,
			'VerificheCampione.aspx?verificati=0&prov=CR' As UrlTotale,
			@TotaleNonVerificati  as Totale
 union
	Select	4 as riga, 
			'Esito OK' as Report, 
			@DiagnosiOK AS Diagnosi, 
			'VerificheCampione.aspx?tiporeport=D&esito=OK&prov=CR' As UrlDiagnosi, 
			@MonitoraggioOK AS Monitoraggio,
			'VerificheCampione.aspx?tiporeport=M&esito=OK&prov=CR' As UrlMonitoraggio,
			'VerificheCampione.aspx?esito=OK&prov=CR' As UrlTotale,
			@TotaleOK as Totale
 union
	Select	5 as riga, 
			'Esito KO' as Report, 
			@DiagnosiKO AS Diagnosi, 
			'VerificheCampione.aspx?tiporeport=D&esito=KO&prov=CR' As UrlDiagnosi, 
			@MonitoraggioKO AS Monitoraggio,
			'VerificheCampione.aspx?tiporeport=M&esito=KO&prov=CR' As UrlMonitoraggio,
			'VerificheCampione.aspx?esito=KO&prov=CR' As UrlTotale,
			@TotaleKO as Totale
) As Tabella
END