FIND CHART OPEN ISSUES

Crow

(@dustincrowell)

Posts: 104

Estimable Member

Topic starter

Does anyone have the script to create the FINDCHARTOPENISSUES Procedure. We recently lost this procedure.

Thanks.

Posted : August 26, 2014 4:00 am

mikeseale

(@mikeseale)

Posts: 344

Reputable Member

I added cus infront of the name so it won't be removed when we do patches/upgrades:

?

USE [DATABASENAME]
GO

/*** Object:  StoredProcedure [dbo].[cusFindChartOpenIssues]    Script Date: 08/26/2014 13:11:29 ***/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[cusFindChartOpenIssues]
    @TableToTest varchar(60) = NULL,
    @PID numeric(19,0) = NULL,
    @CheckNullsOnly bit = NULL
AS
SET NOCOUNT ON

DECLARE @PidSelect NVARCHAR(50) = ''
DECLARE @FlagPidSelect NVARCHAR(50) = ''
IF @PID IS NOT NULL
BEGIN
	  SET @PidSelect = ' AND PID = ' + CAST(@pid as varchar)
	  SET @FlagPidSelect = ' AND ContextID1 = ' + cast(@PID as varchar)
END

IF @CheckNullsOnly IS NULL
BEGIN

	DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), PidColumn VARCHAR(40), DataFound BIT)
		IF @TableToTest IS NULL
		BEGIN
	    		INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
				SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
				FROM    Information_Schema.Columns AS C
						INNER Join Information_Schema.Tables AS T
							ON C.Table_Name = T.Table_Name
					AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
				WHERE   Table_Type = 'Base Table'
						And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
						And C.COLUMN_NAME <> 'RegNote'  -- only computed column outside of PatientVisitProcs
						And C.Table_Name IN  ('PatientProfile',
											'ALLERGY',
											'APPT',
											'ASSESS',
											'DIRECTIV',
											'DOCIMAGES',
											'DOCUMENT',
											'FLAG',
											'InteractionOverride',
											'Med_Override',
											'MEDDX',
											'MEDICATE',
											'MEDICATIONHISTORY',
											'OBS',
											'ORDDX',
											'ORDERS',
											'ORDTOCOMPLETE',
											'PatientContacts',
											'PatientRelationship',
											'PRESCRIB',
											'PROBLEM',
											'PROBLEMLISTVIEWS',
											'UI_LAYOUT')
		END
		ELSE  -- Tablename specified
			INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
			SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
			FROM    Information_Schema.Columns AS C
					INNER Join Information_Schema.Tables AS T
						ON C.Table_Name = T.Table_Name
				AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
			WHERE   Table_Type = 'Base Table'
					And C.Table_Name = @TableToTest
					And C.COLUMN_NAME <> 'RegNote'  -- only computed column outside of PatientVisitProcs
					And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')

		update @Temp SET PidColumn = 'Pid' where TableName <> 'Flag'
		update @Temp SET PidColumn = 'ContextId1' where TableName = 'Flag'

	DECLARE @i INT
	DECLARE @MAX INT
	DECLARE @TableName sysname
	DECLARE @ColumnName sysname
	DECLARE @SchemaName sysname
	DECLARE @SQL NVARCHAR(4000)
	DECLARE @PARAMETERS NVARCHAR(4000)
	DECLARE @DataExists BIT
	DECLARE @SQLTemplate NVARCHAR(4000)

	SET @SQLTemplate = 'If Exists(Select top 1 *
									  From   ReplaceTableName Where '
	IF @PID is not null
	BEGIN
	  SET @SQLTemplate = @SQLTemplate + 'ReplacePidColumn = ' + CAST(@pid as varchar)
	END
	ELSE
	 SET @SQLTemplate = @SQLTemplate + '1=1'
	SELECT @SQLTemplate = @SQLTemplate +   -- tried doing this with function showInvalidXMLChars but performance was terrible
	' AND ( Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(01)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(02)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(03)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(04)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(05)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(06)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(07)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(08)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(11)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(12)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(14)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(15)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(16)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(17)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(18)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(19)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(20)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(21)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(22)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(23)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(24)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(25)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(26)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(27)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(28)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(29)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(30)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(31)+''%''
									  ))
										 Set @DataExists = 1
									 Else
										 Set @DataExists = 0'
								,
			@PARAMETERS = '@DataExists Bit OUTPUT',
			@i = 1

	SELECT @i = 1, @MAX = MAX(RowId)
	FROM   @Temp

	WHILE @i <= @MAX
		BEGIN
			SELECT  @SQL = REPLACE(REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName), 'ReplacePidColumn', PidColumn)
			FROM    @Temp
			WHERE   RowId = @i

			--PRINT @SQL
			EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

			IF @DataExists =1
				UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

			SET @i = @i + 1

		END

	DECLARE @Temp_TableName varchar(300)	                           -- jmm Aug 16, 2013
	SELECT  @Temp_TableName = MIN(TableName) FROM @Temp              -- jmm Aug 16, 2013
	PRINT 'Tables / Columns with bad characters ('+@Temp_TableName+')'
	SELECT  TableName, ColumnName
	FROM    @Temp
	WHERE   DataFound = 1

	PRINT 'Script to Correct -- Please review before executing any queries'

    -- Note: tried using replace instead of function call for performance but that does not work with text data type
	SELECT 'update ' + TableName + ' set ' + ColumnName +
		' = dbo.stripInvalidXMLChars(' + ColumnName + ') where ('
		+ 'Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(01)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(02)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(03)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(04)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(05)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(06)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(07)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(08)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(11)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(12)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(14)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(15)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(16)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(17)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(18)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(19)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(20)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(21)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(22)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(23)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(24)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(25)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(26)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(27)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(28)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(29)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(30)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(31)+''%'')'
	+ @PidSelect
		 FROM @Temp
		 WHERE DataFound = 1
		 AND TableName <> 'Flag'

	SELECT 'update ' + TableName + ' set ' + ColumnName +
		' = dbo.stripInvalidXMLChars(' + ColumnName + ') where ('
		+ 'Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(01)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(02)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(03)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(04)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(05)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(06)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(07)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(08)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(11)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(12)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(14)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(15)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(16)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(17)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(18)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(19)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(20)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(21)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(22)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(23)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(24)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(25)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(26)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(27)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(28)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(29)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(30)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(31)+''%'')'
				+ @FlagPidSelect
		 FROM @Temp
		 WHERE DataFound = 1
		 AND TableName = 'Flag'
END -- @CheckNullsOnly is NULL

-- Check for NULLs in specific fields
IF @PID is NULL
BEGIN
    IF EXISTS (SELECT TOP 1 ID from FLAG where MESSAGE is null)
		    SELECT 'update Flag set Message = '''' where Message is NULL'
    IF EXISTS (SELECT TOP 1 SDID from Document where ConfType is null)
		    SELECT 'update Document set ConfType = 0 where ConfType is NULL'
END
ELSE
BEGIN
    IF EXISTS (SELECT TOP 1 ID from FLAG where ContextId1 = @PID AND MESSAGE is null)
		    SELECT 'update Flag set Message = '''' where Message is NULL' + @FlagPidSelect
    IF EXISTS (SELECT TOP 1 SDID from Document where PID = @PID AND ConfType is null)
	        SELECT 'update Document set confType = 0 where confType is NULL' + @PidSelect
	FROM DOCUMENT where CONFTYPE is NULL and PID = @PID
END

GO

Posted : August 26, 2014 4:13 am

Crow

(@dustincrowell)

Posts: 104

Estimable Member

Topic starter

Thanks Alot!

Posted : August 26, 2014 4:25 am

mdoak

(@mdoak)

Posts: 92

Estimable Member

Does anyone have the stripInvalidXMLChars stored procedure that is necessary if the first part of this returns a result (an obs term with invalid xml characters). I am being instructed to run

update OBS set OBSVALUE = dbo.stripInvalidXMLChars(OBSVALUE) where (Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(01)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(02)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(03)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(04)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(05)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(06)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(07)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(08)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(11)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(12)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(14)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(15)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(16)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(17)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(18)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(19)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(20)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(21)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(22)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(23)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(24)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(25)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(26)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(27)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(28)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(29)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(30)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(31)+'%') AND PID = 1642703861723000

but I don't have the stripInvalidXMLChars function.

Thanks.

Posted : September 15, 2014 10:42 am

Welcome to the NEW Community Healthcare User Group ( the new CHUG)— your ultimate destination for insights and discussions at the intersection of healthcare and technology.

Please scroll to the bottom for “Centricity” related topics from the previous forum.