/****** Object:  View [hips].[MonitoringBackgroundProcessorMessages]    Script Date: 07/12/2013 10:22:59 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringBackgroundProcessorMessages]'))
DROP VIEW [hips].[MonitoringBackgroundProcessorMessages]
GO

/****** Object:  View [hips].[MonitoringBackgroundProcessorMessages]    Script Date: 07/12/2013 10:22:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringBackgroundProcessorMessages]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 7
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME())

GO

/****** Object:  View [hips].[MonitoringConsentHeartBeat]     ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringConsentHeartBeat]'))
DROP VIEW [hips].[MonitoringConsentHeartBeat]
GO

/****** Object:  View [hips].[MonitoringConsentHeartBeat]    ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringConsentHeartBeat]
AS

SELECT TOP 1 ExceptionMessage, DateCreated
FROM hips.[MonitoringSyslog]
	WHERE LogMessageId = 113
	ORDER BY SystemErrorLogId DESC

GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringHIPSErrors]'))
DROP VIEW [hips].[MonitoringHIPSErrors]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringHIPSErrors]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 1
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME())
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringIHILookupConnectionErrors]'))
DROP VIEW [hips].[MonitoringIHILookupConnectionErrors]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringIHILookupConnectionErrors]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 3
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME())
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringIHILookupDataErrors]'))
DROP VIEW [hips].[MonitoringIHILookupDataErrors]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringIHILookupDataErrors]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 4
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME())

GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringInfoMessages]'))
DROP VIEW [hips].[MonitoringInfoMessages]
GO

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringInfoMessages]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 2
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME()) 
		AND NOT (
		ExceptionMessage LIKE '%Only the survivor was found for enterprise merge%'
		OR ExceptionMessage LIKE '%Neither patient was found for enterprise merge%'
		OR ExceptionMessage LIKE '%Source patient not found for merge%'
		OR ExceptionMessage LIKE '%Source patient not found for move%'
		OR ExceptionMessage LIKE '%Episode not found for move visit%'
		)

GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringMergeInfoMessages]'))
DROP VIEW [hips].[MonitoringMergeInfoMessages]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringMergeInfoMessages]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 2
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME()) 
		AND (
		ExceptionMessage LIKE '%Only the survivor was found for enterprise merge%'
		OR ExceptionMessage LIKE '%Neither patient was found for enterprise merge%'
		OR ExceptionMessage LIKE '%Source patient not found for merge%'
		OR ExceptionMessage LIKE '%Source patient not found for move%'
		OR ExceptionMessage LIKE '%Episode not found for move visit%'
		)

GO

/****** Object:  View [hips].[MonitoringMsmqMessages]    Script Date: 07/12/2013 10:22:59 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringMsmqMessages]'))
DROP VIEW [hips].[MonitoringMsmqMessages]
GO

/****** Object:  View [hips].[MonitoringMsmqMessages]    Script Date: 07/12/2013 10:22:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringMsmqMessages]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 8
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME())

GO

/****** Object:  View [hips].[MonitoringPcehrErrors]    Script Date: 07/12/2013 10:22:59 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringPcehrErrors]'))
DROP VIEW [hips].[MonitoringPcehrErrors]
GO

/****** Object:  View [hips].[MonitoringPcehrErrors]    Script Date: 07/12/2013 10:22:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringPcehrErrors]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 5
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME())
GO

/****** Object:  View [hips].[MonitoringPcehrInfoMessages]    Script Date: 07/12/2013 10:22:59 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringPcehrInfoMessages]'))
DROP VIEW [hips].[MonitoringPcehrInfoMessages]
GO

/****** Object:  View [hips].[MonitoringPcehrInfoMessages]    Script Date: 07/12/2013 10:22:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringPcehrInfoMessages]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 6
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME())

GO

/****** Object:  View [hips].[MonitoringPcehrUploadErrors]    Script Date: 07/12/2013 10:22:59 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringPcehrUploadErrors]'))
DROP VIEW [hips].[MonitoringPcehrUploadErrors]
GO

/****** Object:  View [hips].[MonitoringPcehrUploadErrors]    Script Date: 07/12/2013 10:22:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringPcehrUploadErrors]
AS
	SELECT 
		hp.Mrn as 'Patient MRN',
        SUBSTRING(pmi.Ihi, 1, 4) + ' '
        + SUBSTRING(pmi.Ihi, 4, 4) + ' '
        + SUBSTRING(pmi.Ihi, 8, 4) + ' '
        + SUBSTRING(pmi.Ihi, 12, 4) "IHI" ,
        ihis.Description "IHI Status" ,
		h.Description AS 'Hospital',
		e.AdmissionDate AS 'Episode Admission Date',
		pm.PatientMasterId,
		pmq.EpisodeId,
		pmq.PcehrMessageQueueId,
		pmq.DateCreated,
		pmq.Details AS 'Error Details'
		FROM  hips.PcehrMessageQueue pmq WITH (NOLOCK) 
		JOIN hips.Episode e WITH (NOLOCK) ON e.EpisodeId = pmq.EpisodeId
		JOIN hips.HospitalPatient hp WITH (NOLOCK) ON hp.PatientId = e.PatientId
		JOIN hips.Hospital h WITH (NOLOCK) ON hp.HospitalId = h.HospitalId
		JOIN hips.HealthProviderOrganisation hpo WITH (NOLOCK) ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
		JOIN hips.PatientMaster pm WITH (NOLOCK) ON pm.PatientMasterId = hp.PatientMasterId 
		JOIN hips.PatientMasterIhi pmi WITH (NOLOCK) ON pm.PatientMasterId = pmi.PatientMasterId AND hpo.HealthProviderOrganisationNetworkId = pmi.HealthProviderOrganisationNetworkId
		JOIN hips.PatientMasterName pmn WITH (NOLOCK) ON pmn.PatientMasterId = pm.PatientMasterId and NameTypeId = 2
		JOIN hips.IhiStatus ihis WITH (NOLOCK) ON ihis.IhiStatusId = pmi.IhiStatusId
		LEFT JOIN hips.ClinicalDocument cd WITH (NOLOCK) ON cd.EpisodeId = e.EpisodeId
		LEFT JOIN hips.ClinicalDocumentVersion cdv WITH (NOLOCK) ON cd.ClinicalDocumentId = cdv.ClinicalDocumentId
		WHERE QueueStatusId = 3
		AND (cdv.UploadedDate IS NULL OR cdv.UploadedDate < pmq.DateCreated)
GO

/****** Object:  View [hips].[MonitoringPumaErrorMessages]    Script Date: 07/12/2013 10:22:59 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringPumaErrorMessages]'))
DROP VIEW [hips].[MonitoringPumaErrorMessages]
GO

/****** Object:  View [hips].[MonitoringPumaErrorMessages]    Script Date: 07/12/2013 10:22:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringPumaErrorMessages]
AS
	SELECT Message, ExceptionMessage, DateCreated, LogMessage
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 9
		AND DateCreated >= DATEADD(hour, -24, SYSDATETIME())

GO

/****** Object:  View [hips].[MonitoringSyslog]     ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringSyslog]'))
DROP VIEW [hips].[MonitoringSyslog]
GO

/****** Object:  View [hips].[MonitoringSyslog]    ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringSyslog]
AS

	SELECT  sel.*, 
			mt.MonitorTabId, 
			mt.description,
			lm.Class,
			lm.Method,
			lm.description AS logdescription,
			'(#'+CAST(lm.LogMessageId AS VARCHAR)+') '+ lm.description+'  ['+lm.Class+':'+lm.Method+']' AS LogMessage
	FROM hips.SystemErrorLog sel WITH (NOLOCK)
	JOIN hips.MonitorTabMessage mtm WITH (NOLOCK) ON sel.LogMessageId = mtm.LogMessageId
	JOIN hips.MonitorTab mt WITH (NOLOCK) ON mt.MonitorTabId = mtm.MonitorTabId 
	JOIN hips.LogMessage lm WITH (NOLOCK) ON lm.LogMessageId = mtm.LogMessageId

GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[hips].[MonitoringUploadedDocuments]'))
DROP VIEW [hips].[MonitoringUploadedDocuments]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [hips].[MonitoringUploadedDocuments]
AS
	SELECT  UploadedDate "Upload DateTime",
        SUBSTRING(SourceSystemDocumentId,
                  CHARINDEX('^', SourceSystemDocumentId) + 1,
                  LEN(SourceSystemDocumentId)) "Source Doc ID" ,
        CodeSystem.Description "Document ID Type" ,
        PcehrAudit.UserName "Sender Name" ,
        hospital.description "Hospital" ,
        HospitalPatient.Mrn "MRN" ,
        Episode.SourceSystemEpisodeId "Visit Number" ,
        Episode.AdmissionDate "Admission DateTime" ,
        Episode.DischargeDate "Discharge DateTime"
	FROM    hips.ClinicalDocumentVersion
			INNER JOIN hips.ClinicalDocument ON hips.ClinicalDocument.ClinicalDocumentId = hips.ClinicalDocumentVersion.ClinicalDocumentId
			INNER JOIN hips.CodeSystem ON hips.CodeSystem.Oid = SUBSTRING(hips.ClinicalDocumentVersion.SourceSystemDocumentId,1, LEN(hips.CodeSystem.Oid)) 
			INNER JOIN hips.Episode ON hips.Episode.EpisodeId = hips.ClinicalDocument.EpisodeId
			INNER JOIN hips.HospitalPatient ON hips.HospitalPatient.PatientId = hips.Episode.PatientId
			INNER JOIN hips.Hospital ON hips.Hospital.HospitalId = hips.HospitalPatient.HospitalId
			INNER JOIN hips.HealthProviderOrganisation hpo WITH (NOLOCK) ON hips.Hospital.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
			INNER JOIN hips.PatientMaster ON hips.PatientMaster.PatientMasterId = hips.HospitalPatient.PatientMasterId
			INNER JOIN hips.PatientMasterName ON hips.PatientMasterName.PatientMasterId = hips.PatientMaster.PatientMasterId
												 AND NameTypeId = 2
			LEFT JOIN hips.PcehrAudit ON hips.PcehrAudit.PatientMasterId = hips.PatientMaster.PatientMasterId
										 AND PcehrAudit.ServiceName = 'Upload Document'
										 AND ABS(DATEDIFF(ss,
														  PCehrAudit.DateCreated,
														  ClinicalDocumentVersion.DateCreated)) < 2
			LEFT JOIN hips.PatientMasterIhi ON hips.PatientMasterIhi.PatientMasterId = hips.PatientMaster.PatientMasterId and hips.PatientMasterIhi.HealthProviderOrganisationNetworkId = hpo.HealthProviderOrganisationNetworkId
	WHERE
		ClinicalDocumentVersion.DateCreated >= DATEADD(day, -7, SYSDATETIME()) 

GO

