SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Split](@String ntext, @Delimiter char(1))
    RETURNS @Results TABLE (Items nvarchar(4000))
    AS
    BEGIN
        DECLARE @STARTINDEX INT, @ENDINDEX INT, @STRINGLEN int, @LOOPSTRING NVARCHAR(4000)
        SET @STRINGLEN = DATALENGTH(ISNULL(@STRING, N''))/2
        SET @STARTINDEX = 1

        DECLARE @SLICE nvarchar(4000)
        DECLARE @SUBSTR nvarchar(4000)

        SET @SLICE  = N''

        WHILE @STARTINDEX < @STRINGLEN+1 BEGIN
            
            SET @SUBSTR = SUBSTRING(@STRING, @STARTINDEX, 1)

            IF @SUBSTR = @Delimiter BEGIN
                IF @SLICE != N''
                    INSERT INTO @Results(Items) VALUES(@SLICE)
                SET @SLICE = N''
            END
            ELSE
                SET @SLICE = @SLICE+@SUBSTR

            SET @STARTINDEX = @STARTINDEX + 1
        END
        IF LEN(@SLICE) > 0
            INSERT INTO @Results(Items) VALUES(@SLICE)
        RETURN
    END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_User]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIds <> ''
	BEGIN	
		IF @OperationType = 1			-- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_User] 
							SET		[IsActive] = 1,
									[ModifiedDate] = GETDATE() 
							WHERE	[UserId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	-- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_User] 
							SET		[IsActive] = 0,
									[ModifiedDate] = GETDATE() 
							WHERE	[UserId] IN (' + @MultiIds + ')'
		END
		IF @OperationType = 3			-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_User] 
							SET		[IsDeleted] = 1,
									[ModifiedDate] = GETDATE() 
							WHERE	[UserId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_Role]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		
		IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	DELETE FROM [dbo].[HMS_Role] 
                            WHERE		[RoleId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_Report]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	DELETE FROM	[dbo].[HMS_Report] 
							WHERE	[ReportId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_PaymentCategory]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_PaymentCategory] 
							SET		[IsActive] = 1
							WHERE	[PaymentCategoryId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_PaymentCategory] 
							SET		[IsActive] = 0
							WHERE	[PaymentCategoryId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_PaymentCategory] 
							SET		[IsDeleted] = 1
							WHERE	[PaymentCategoryId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_Payment]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Payment] 
							SET		[IsDeleted] = 1
							WHERE	[PaymentId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 1		-- Paid
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Payment] 
							SET		[PaymentStatus] = 1
							WHERE	[PaymentId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_PatientTest]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_PatientTest] 
							SET		[IsDeleted] = 1
							WHERE	[PatientTestId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 1		-- Paid
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_PatientTest] 
							SET		[ReportStatus] = 1
							WHERE	[PatientTestId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_Patient]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIds <> ''
	BEGIN	
		IF @OperationType = 1			-- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_User] 
							SET		[IsActive] = 1,
									[ModifiedDate] = GETDATE() 
							WHERE	[UserId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	-- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_User] 
							SET		[IsActive] = 0,
									[ModifiedDate] = GETDATE() 
							WHERE	[UserId] IN (' + @MultiIds + ')'
		END
		IF @OperationType = 3			-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_User] 
							SET		[IsDeleted] = 1,
									[ModifiedDate] = GETDATE() 
							WHERE	[UserId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_MedicineCategory]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_MedicineCategory] 
							SET		[IsActive] = 1
							WHERE	[MedicineCategoryId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_MedicineCategory] 
							SET		[IsActive] = 0
							WHERE	[MedicineCategoryId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_MedicineCategory] 
							SET		[IsDeleted] = 1
							WHERE	[MedicineCategoryId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_Medicine]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Medicine] 
							SET		[IsActive] = 1
							WHERE	[MedicineId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Medicine] 
							SET		[IsActive] = 0
							WHERE	[MedicineId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Medicine] 
							SET		[IsDeleted] = 1
							WHERE	[MedicineId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_LoginHistory] 
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIds <> ''
	BEGIN	
		IF @OperationType = 3			-- Delete
		BEGIN
			SET @strSQL = '	DELETE FROM [dbo].[HMS_LoginHistory]  
							WHERE		[LoginHistoryId] IN (' + @MultiIds + ')'
			EXEC (@strSQL)	
		END
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_LabTestCategory]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_LabTestCategory] 
							SET		[IsActive] = 1
							WHERE	[LabTestCategoryId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_LabTestCategory] 
							SET		[IsActive] = 0
							WHERE	[LabTestCategoryId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_LabTestCategory] 
							SET		[IsDeleted] = 1
							WHERE	[LabTestCategoryId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_LabTest]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_LabTest] 
							SET		[IsDeleted] = 1
							WHERE	[LabTestId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_ExpenseCategory]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_ExpenseCategory] 
							SET		[IsActive] = 1
							WHERE	[ExpenseCategoryId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_ExpenseCategory] 
							SET		[IsActive] = 0
							WHERE	[ExpenseCategoryId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_ExpenseCategory] 
							SET		[IsDeleted] = 1
							WHERE	[ExpenseCategoryId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_Expense]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Expense] 
							SET		[IsDeleted] = 1
							WHERE	[ExpenseId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_EmailTemplate]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	-- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_EmailTemplate] 
							SET		[IsActive] = 1,
									[ModifiedDate] = GETDATE() 
							WHERE	[EmailTemplateId] IN (' + @MultiIDs + ')'
		END			
		ELSE IF @OperationType = 2	-- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[HMS_EmailTemplate] 
							SET		[IsActive] = 0,
									[ModifiedDate] = GETDATE() 
							WHERE	[EmailTemplateId] IN (' + @MultiIDs + ')'
		END
		ELSE IF @OperationType = 3			-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[HMS_EmailTemplate] 
							SET		[IsDeleted] = 1,
									[ModifiedDate] = GETDATE() 
							WHERE	[EmailTemplateId] IN (' + @MultiIDs + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_Customer]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[Customer] 
							SET		[IsActive] = 1,
									[ModifiedDate] = GETDATE()
							WHERE	[CustomerId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[Customer] 
							SET		[IsActive] = 0,
									[ModifiedDate] = GETDATE()                                    
							WHERE	[CustomerId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[Customer] 
							SET		[IsDeleted] = 1
							WHERE	[CustomerId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_Company]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Company] 
							SET		[IsActive] = 1
							WHERE	[CompanyId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Company] 
							SET		[IsActive] = 0
							WHERE	[CompanyId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Company] 
							SET		[IsDeleted] = 1
							WHERE	[CompanyId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_BedCategory]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_BedCategory] 
							SET		[IsActive] = 1
							WHERE	[BedCategoryId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_BedCategory] 
							SET		[IsActive] = 0
							WHERE	[BedCategoryId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_BedCategory] 
							SET		[IsDeleted] = 1
							WHERE	[BedCategoryId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_BedAllotment]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_BedAllotment] 
							SET		[IsActive] = 1
							WHERE	[BedAllotmentId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_BedAllotment] 
							SET		[IsActive] = 0
							WHERE	[BedAllotmentId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	DELETE	FROM [dbo].[HMS_BedAllotment] 
							WHERE	[BedAllotmentId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spM_Bed]
@MultiIds		VARCHAR(8000)	= NULL,
@OperationType	INT				= NULL
AS
BEGIN
	DECLARE @strSQL  VARCHAR(MAX)
	
	IF @MultiIDs <> ''
	BEGIN	
		IF @OperationType = 1	        -- Active
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Bed] 
							SET		[IsActive] = 1
							WHERE	[BedId] IN (' + @MultiIds + ')'
		END			
		ELSE IF @OperationType = 2	    -- InActive
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Bed] 
							SET		[IsActive] = 0
							WHERE	[BedId] IN (' + @MultiIds + ')'
		END
		ELSE IF @OperationType = 3		-- Delete
		BEGIN
			SET @strSQL = '	UPDATE	[dbo].[HMS_Bed] 
							SET		[IsDeleted] = 1
							WHERE	[BedId] IN (' + @MultiIds + ')'
		END
		
		EXEC (@strSQL)
		
		SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC HMS_spS_BedAllotment @SortField = 'BedAllotmentName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_BedAllotment]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[HMS_BedAllotment].[BedAllotmentId],
								[HMS_BedAllotment].[PatientId],
								[HMS_BedAllotment].[BedId],
								[HMS_BedAllotment].[BedCategoryId],
								[HMS_BedAllotment].[AllotmentDate],
								[HMS_BedAllotment].[DischargeDate],
								[HMS_BedAllotment].[Notes],
								[HMS_Bed].[BedName],
								[HMS_User].[FirstName] + '' '' +  [HMS_User].[LastName] AS ''PatientName'' ,
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_BedAllotment]
						INNER JOIN [HMS_Bed] ON ([HMS_BedAllotment].[BedId] =  [HMS_Bed].[BedId])
						INNER JOIN [HMS_User] ON ([HMS_BedAllotment].[PatientId] =  [HMS_User].[UserId])
						WHERE	[HMS_BedAllotment].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX))
						
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		IF @SearchField = 'BedName'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_Bed].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END
		IF @SearchField = 'PatientName'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND ([HMS_User].[FirstName] LIKE ''' + @SearchValue + '%'')'
		END
		ELSE
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_BedAllotment].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END			
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	print @strSQL
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC HMS_spS_Bed @SortField = 'BedName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_Bed]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[HMS_Bed].[BedId],
								[HMS_Bed].[BedName],
								[HMS_Bed].[Description],
								[HMS_Bed].[BedCategoryId],
								[HMS_Bed].[IsActive],
								[HMS_Bed].[IsAvailable],
								[HMS_BedCategory].[BedCategory],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_Bed]
						INNER JOIN [HMS_BedCategory] ON ([HMS_Bed].[BedCategoryId] =  [HMS_BedCategory].[BedCategoryId])
						WHERE	[HMS_Bed].[IsDeleted] = 0
						AND	[HMS_Bed].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		IF @SearchField = 'BedCategory'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_BedCategory].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END
		ELSE
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_Bed].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END			
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	print @strSQL
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spS_User @RoleId=2, @SearchField = 'FirstName', @SearchValue = '', @SortField = 'FirstName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_BedCategory]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[BedCategoryId],
								[BedCategory],
								[Description],
								[IsActive],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_BedCategory]
						WHERE	[IsDeleted] = 0
						AND	[HMS_BedCategory].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spS_User @RoleId=2, @SearchField = 'FirstName', @SearchValue = '', @SortField = 'FirstName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_Company]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[CompanyId],
								[CompanyName],
								[Address1],
								[Address2],
								[City],
								[State],
								[ZipCode],
								[Email],
								[Phone],
								[Fax],
								[IsActive],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_Company]
						WHERE	[IsDeleted] = 0'
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	print @strSQL
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spS_EmailTemplate @SearchField = 'EmailTemplateCode', @SearchValue = 'fir', @SortField = 'EmailTemplateCode', @SortOrder = 'ASC', @PageNo = 3, @PageSize = 15

CREATE PROCEDURE [dbo].[HMS_spS_EmailTemplate]
@SearchField	VARCHAR(100)	= NULL,
@SearchValue	VARCHAR(100)	= NULL,
@SortField		VARCHAR(100)	= NULL,
@SortOrder		VARCHAR(5)		= NULL,
@PageNo			INT				= NULL,
@PageSize		INT				= NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT  [EmailTemplateId],
								[Code],
								[Title],
								[Subject],
								[FromEmail],
								[Message],
								[Signature],
								[IsActive],
								[IsDeleted],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_EmailTemplate]
						WHERE	[IsDeleted] = 0'
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
			
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC HMS_spS_Expense @SortField = 'ExpenseName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_Expense]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[HMS_Expense].[ExpenseId],
								[HMS_Expense].[ExpenseAmount],
								[HMS_Expense].[ExpenseDate],
								[HMS_Expense].[ExpenseCategoryId],								
								[HMS_ExpenseCategory].[ExpenseCategory],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_Expense]
						INNER JOIN [HMS_ExpenseCategory] ON ([HMS_Expense].[ExpenseCategoryId] =  [HMS_ExpenseCategory].[ExpenseCategoryId])
						WHERE	[HMS_Expense].[IsDeleted] = 0
						AND	[HMS_Expense].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		IF @SearchField = 'ExpenseCategory'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_ExpenseCategory].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END
		ELSE
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_Expense].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END			
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	print @strSQL
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spS_User @RoleId=2, @SearchField = 'FirstName', @SearchValue = '', @SortField = 'FirstName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_ExpenseCategory]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[ExpenseCategoryId],
								[ExpenseCategory],
								[Description],
								[IsActive],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_ExpenseCategory]
						WHERE	[IsDeleted] = 0
						AND	[HMS_ExpenseCategory].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC HMS_spS_LabTest @SortField = 'LabTestName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_LabTest]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[HMS_LabTest].[LabTestId],
								[HMS_LabTest].[LabTestName],
								[HMS_LabTest].[TestUnit],
								[HMS_LabTest].[ReferenceRange],
								[HMS_LabTest].[IsActive],
								[HMS_LabTest].[LabTestCategoryId],								
								[HMS_LabTestCategory].[LabTestCategoryName],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_LabTest]
						INNER JOIN [HMS_LabTestCategory] ON ([HMS_LabTest].[LabTestCategoryId] =  [HMS_LabTestCategory].[LabTestCategoryId])
						WHERE	[HMS_LabTest].[IsDeleted] = 0
						AND	[HMS_LabTest].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		IF @SearchField = 'LabTestCategoryName'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_LabTestCategory].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END
		ELSE
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_LabTest].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END			
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	print @strSQL
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spS_User @RoleId=2, @SearchField = 'FirstName', @SearchValue = '', @SortField = 'FirstName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_LabTestCategory]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[LabTestCategoryId],
								[LabTestCategoryName],
								[Description],
								[IsActive],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_LabTestCategory]
						WHERE	[IsDeleted] = 0
						AND	[HMS_LabTestCategory].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC HMS_spS_Medicine @SortField = 'MedicineName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_Medicine]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[HMS_Medicine].[MedicineId],
								[HMS_Medicine].[MedicineName],
								[HMS_Medicine].[Description],
								[HMS_Medicine].[MedicineCategoryId],
								[HMS_Medicine].[IsActive],
								[HMS_MedicineCategory].[MedicineCategory],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_Medicine]
						INNER JOIN [HMS_MedicineCategory] ON ([HMS_Medicine].[MedicineCategoryId] =  [HMS_MedicineCategory].[MedicineCategoryId])
						WHERE	[HMS_Medicine].[IsDeleted] = 0
						AND	[HMS_Medicine].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		IF @SearchField = 'MedicineCategory'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_MedicineCategory].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END
		ELSE
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_Medicine].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END			
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	print @strSQL
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC [spS_LoginHistory] @SearchField='ActionDate',@SearchValue='08/14/2012', @SortField = 'ActionDate', @SortOrder = 'DESC', @PageNo = 1, @PageSize = 15

CREATE PROCEDURE [dbo].[HMS_spS_LoginHistory]
@SearchField	VARCHAR(100)	= NULL,
@SearchValue	VARCHAR(100)	= NULL,
@SortField		VARCHAR(100)	= NULL,
@SortOrder		VARCHAR(5)		= NULL,
@PageNo			INT				= NULL,
@PageSize		INT				= NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT		[HMS_LoginHistory].[LoginHistoryId],
									[HMS_LoginHistory].[UserId],
									[HMS_LoginHistory].[Action],
									[HMS_LoginHistory].[IpAddress],
									[HMS_LoginHistory].[ActionDate],
									[HMS_User].[Email] AS ''UserName'',
									ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM		[dbo].[HMS_LoginHistory]
						INNER JOIN	[dbo].[HMS_User] ON [HMS_LoginHistory].[UserId] = [HMS_User].[UserId]
						'
	IF (@SearchField IS NOT NULL AND @SearchField != '') 
	BEGIN
			IF @SearchField = 'ActionDate'
			BEGIN
				SET @SelectQuery = @SelectQuery	+ ' WHERE CONVERT(VARCHAR(10), ActionDate, 101) = ''' + @SearchValue + ''''
			END
			ELSE
			BEGIN
				SET @SelectQuery = @SelectQuery	+ ' WHERE ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
			END	
	END
	
	SET @strSQL  =	'	SELECT *
						FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
	print @strSQL
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
									
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spS_User @RoleId=2, @SearchField = 'FirstName', @SearchValue = '', @SortField = 'FirstName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_MedicineCategory]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[MedicineCategoryId],
								[MedicineCategory],
								[IsActive],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_MedicineCategory]
						WHERE	[IsDeleted] = 0
						AND	[HMS_MedicineCategory].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spS_User @RoleId=2, @SearchField = 'FirstName', @SearchValue = '', @SortField = 'FirstName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_Patient]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[HMS_User].[UserId],
								[FirstName],
								[MiddleName],
								[LastName],
								[Email],
								[Password],
								[Phone],
								[RoleId],
								[IsActive],
								[CompanyId],
								[HMS_PatientDetail].[BirthDate],
								[HMS_PatientDetail].[Gender],
								[HMS_PatientDetail].[BloodGroup],
								[HMS_User].[Address1],
								[HMS_User].[ProfilePic],
								[HMS_PatientDetail].[PatientCode],
								'''' AS [CompanyName],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_User]
						INNER JOIN [HMS_PatientDetail] ON ([HMS_User].[UserId] = [HMS_PatientDetail].[UserId])
						WHERE	[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) + '
						AND		[IsDeleted] = 0'
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		IF @SearchField = 'PatientCode'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_PatientDetail].' + @SearchField + ' LIKE ''%' + @SearchValue + '%'''
		END
		ELSE
		BEGIN
				SET @SelectQuery = @SelectQuery + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END	
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	PRINT(@strSQL)
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC HMS_spS_PatientTest @CompanyId =1 ,@SortField = 'PatientTestId', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_PatientTest]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[HMS_PatientTest].[PatientTestId],
								[HMS_PatientTest].[PatientId],
								[HMS_PatientTest].[CompanyId],
								[HMS_PatientTest].[ConsultantId],
								[HMS_PatientTest].[TestDate],
								[HMS_PatientTest].[DeliveryDate],
								[HMS_PatientTest].[ReportStatus],
								[HMS_User].[FirstName] + '' '' +  [HMS_User].[LastName] AS ''PatientName'' ,
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_PatientTest]
						INNER JOIN [HMS_User] ON ([HMS_PatientTest].[PatientId] =  [HMS_User].[UserId])
						WHERE	[HMS_PatientTest].[IsDeleted] = 0
						AND	[HMS_PatientTest].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		IF @SearchField = 'PatientName'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND ([HMS_User].[FirstName] LIKE ''' + @SearchValue + '%'')'
		END
		ELSE
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_PatientTest].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END			
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	

	EXEC(@strSQL)	
		
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC HMS_spS_Payment @CompanyId =1 ,@SortField = 'PaymentId', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_Payment]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[HMS_Payment].[PaymentId],
								[HMS_Payment].[InvoiceNumber],
								[HMS_Payment].[PatientId],
								[HMS_Payment].[CompanyId],
								[HMS_Payment].[PaymentDate],
								[HMS_Payment].[SubTotal],
								[HMS_Payment].[Discount],
								[HMS_Payment].[Tax],
								[HMS_Payment].[GrandTotal],
								[HMS_Payment].[PaymentStatus],
								[HMS_User].[FirstName] + '' '' +  [HMS_User].[LastName] AS ''PatientName'' ,
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_Payment]
						INNER JOIN [HMS_User] ON ([HMS_Payment].[PatientId] =  [HMS_User].[UserId])
						WHERE	[HMS_Payment].[IsDeleted] = 0
						AND	[HMS_Payment].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		IF @SearchField = 'PatientName'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND ([HMS_User].[FirstName] LIKE ''' + @SearchValue + '%'')'
		END
		ELSE
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND [HMS_Payment].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END			
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	

	EXEC(@strSQL)	
		PRINT(@strSQL)
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spS_User @RoleId=2, @SearchField = 'FirstName', @SearchValue = '', @SortField = 'FirstName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_PaymentCategory]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[PaymentCategoryId],
								[PaymentCategory],
								[Description],
								[IsActive],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_PaymentCategory]
						WHERE	[IsDeleted] = 0
						AND	[HMS_PaymentCategory].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC HMS_spS_LabTest @SortField = 'Description', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_Report]
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL,
@ReportTypeId	BIGINT		= NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[HMS_Report].[ReportId],
								[HMS_Report].[Description],
								[HMS_Report].[ReportDate],
								[HMS_Report].[PatientId],
								[HMS_Report].[ReportTypeId],
								[HMS_Report].[ReportFile],
								[HMS_Report].[CompanyId],
								[HMS_User].[FirstName] + '' '' +  [HMS_User].[LastName] AS ''PatientName'' ,								
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_Report]
						INNER JOIN [HMS_User] ON ([HMS_Report].[PatientId] =  [HMS_User].[UserId])
						WHERE	[HMS_Report].[IsDeleted] = 0
						AND	[HMS_Report].[CompanyId] = ' + CAST(@CompanyId AS VARCHAR(MAX))  + '
						AND [HMS_Report].[ReportTypeId] = ' + CAST(@ReportTypeId AS VARCHAR(MAX))
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		IF @SearchField = 'PatientName'
		BEGIN
			SET @SelectQuery = @SelectQuery + ' AND ([HMS_User].[FirstName] LIKE ''' + @SearchValue + '%'')'
		END
		ELSE
		BEGIN
		SET @SelectQuery = @SelectQuery + ' AND [HMS_Report].' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
		END		
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spS_User @RoleId=2, @SearchField = 'FirstName', @SearchValue = '', @SortField = 'FirstName', @SortOrder = 'ASC', @PageNo = 1, @PageSize = 10

CREATE PROCEDURE [dbo].[HMS_spS_User]
@RoleId			BIGINT		= NULL,
@SearchField	VARCHAR(100)= NULL,
@SearchValue	VARCHAR(100)= NULL,
@SortField		VARCHAR(100)= NULL,
@SortOrder		VARCHAR(5)	= NULL,
@PageNo			INT			= NULL,
@PageSize		INT			= NULL,
@CompanyId		BIGINT		= NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery = 'SELECT	[UserId],
								[FirstName],
								[MiddleName],
								[LastName],
								[Email],
								[Password],
								[Phone],
								[RoleId],
								[IsActive],
								[CompanyId],
								[ProfilePic],
								ROW_NUMBER() OVER (ORDER BY '+ @SortField + ' ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_User]
						WHERE	[RoleId] = ' + CAST(@RoleId AS VARCHAR(MAX)) + '
						AND		[IsDeleted] = 0'
	
	IF @CompanyId IS NOT NULL
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND CompanyId = ' + CAST(@CompanyId AS VARCHAR(MAX)) 
	END
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'''
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
						
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)	
	PRINT(@strSQL)
	
	SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	
	
	EXEC(@strSQL)	
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_Role] 
@SearchField	VARCHAR(100)	= NULL,
@SearchValue	VARCHAR(100)	= NULL,
@SortField		VARCHAR(100)	= NULL,
@SortOrder		VARCHAR(5)		= NULL,
@PageNo			INT				= NULL,
@PageSize		INT				= NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @strSQL VARCHAR(MAX) 
	DECLARE @SelectQuery VARCHAR(MAX)
	
	SET @SelectQuery =' SELECT  [HMS_Role].[RoleId],
								[HMS_Role].[RoleName],
								ROW_NUMBER() OVER (ORDER BY [HMS_Role].['+ @SortField + '] ' + @SortOrder + ') AS [RowNo]
						FROM	[dbo].[HMS_Role]
                        WHERE   1=1'
	
	IF @SearchField IS NOT NULL AND @SearchField <> ''
	BEGIN
		SET @SelectQuery = @SelectQuery + ' AND [HMS_Role].[' + @SearchField + '] LIKE ''' + @SearchValue + '%'''
	END
	
	SET @strSQL  =	'	SELECT * FROM (' + @SelectQuery + ') Result'
			
	IF (@PageSize > 0)
	BEGIN
		SET @strSQL  =	@strSQL  + ' WHERE	[Result].[RowNo] > ' + CAST((@PageNo-1)* @PageSize AS VARCHAR(10)) + '
									 AND	[Result].[RowNo] < ' + CAST(@PageNo * @PageSize + 1 AS VARCHAR(10))
	END
						
	EXEC(@strSQL)		

    SET @strSQL  =	'	SELECT COUNT(*) AS TotalRecordCount 
						FROM (' + @SelectQuery + ') Result'
	EXEC(@strSQL)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_ReportById]
@ReportId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
		SELECT	[HMS_Report].[ReportId],
				[HMS_Report].[Description],
				[HMS_Report].[ReportDate],
				[HMS_Report].[PatientId],
				[HMS_Report].[ReportTypeId],
				[HMS_Report].[ReportFile],
				[HMS_Report].[CompanyId],
				'' AS 'PatientName'
		FROM	[dbo].[HMS_Report]
		WHERE	[HMS_Report].[ReportId] = @ReportId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_SiteMenuLTEByStatus]
@RoleId				INT = NULL,
@IsExcludeInactive	BIT = NULL
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT		[HMS_SiteMenuLTE].[MenuId],
				[HMS_SiteMenuLTE].[ParentMenuId],
				[HMS_SiteMenuLTE].[MenuCode],
				[HMS_SiteMenuLTE].[MenuName],
				[HMS_SiteMenuLTE].[MenuPageName],
				[HMS_SiteMenuLTE].[MenuImageName],
				[HMS_SiteMenuLTE].[MenuOrderNo],
				[HMS_SiteMenuLTE].[IsActive]
	FROM		[dbo].[HMS_SiteMenuLTE] 
	INNER JOIN	[dbo].[HMS_RoleSiteMenu] ON [HMS_SiteMenuLTE].[MenuCode] = [HMS_RoleSiteMenu].[MenuCode]
	WHERE		[HMS_RoleSiteMenu].[RoleId] = @RoleId
	AND			[HMS_SiteMenuLTE].[IsDeleted] = 0
	AND			[HMS_SiteMenuLTE].[IsActive] = COALESCE(@IsExcludeInactive,[HMS_SiteMenuLTE].[IsActive])
	ORDER BY	[HMS_SiteMenuLTE].[MenuOrderNo]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_Setting]
AS
BEGIN

	SET NOCOUNT ON;
	
	SELECT  [SettingId],
			[SiteTitle],
			[CopyrightText],
			[AdminEmail],
			[SupportEmail],
			[ContactEmail],
			[TollFreeNo],
			[ContactNo],
			[SMTPHost],
			[SMTPPort],
			[SMTPUserName],
			[SMTPPassword],
			[PageSize]
	FROM	[dbo].[HMS_Setting]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_RoleById]
@RoleId  		BIGINT          	=	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
        SELECT  [HMS_Role].[RoleId],
				[HMS_Role].[RoleName]
	    FROM	[dbo].[HMS_Role]
	    WHERE	[HMS_Role].[RoleId] = @RoleId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[HMS_spS_PrescriptionByPatientId]
@PatientId	BIGINT = NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT [PrescriptionId]
      ,[PatientCheckupId]
      ,[PatientId]
      ,[TreatmentId]
      ,[CaseHistory]
      ,[ExtraNotes]
      ,[PrescriptionDate]
  FROM [dbo].[HMS_Prescription]
  WHERE [HMS_Prescription].[PatientId] = @PatientId
  
  
	SELECT [PrescriptionDetailId]
      ,[PrescriptionId]
      ,[HMS_PrescriptionDetail].[MedicineId]
	  ,[PatientId]
      ,[NoOfDays]
      ,[WhenToTake]
      ,[IsBeforeMeal]
      ,[HMS_Medicine].[MedicineName]
  FROM [dbo].[HMS_PrescriptionDetail]
  INNER JOIN [HMS_Medicine] ON ([HMS_PrescriptionDetail].[MedicineId] = [HMS_Medicine].[MedicineId])
  WHERE [HMS_PrescriptionDetail].[PatientId] = @PatientId
  
	
  
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[HMS_spS_PrescriptionByCheckupId]
@PatientCheckupId	BIGINT = NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT [PrescriptionId]
      ,[PatientCheckupId]
      ,[PatientId]
      ,[TreatmentId]
      ,[CaseHistory]
      ,[ExtraNotes]
      ,[PrescriptionDate]
  FROM [dbo].[HMS_Prescription]
  WHERE [HMS_Prescription].[PatientCheckupId] = @PatientCheckupId
  
  
	SELECT [PrescriptionDetailId]
      ,[HMS_PrescriptionDetail].[PrescriptionId]
      ,[HMS_PrescriptionDetail].[MedicineId]
	  ,[HMS_PrescriptionDetail].[PatientId]
      ,[NoOfDays]
      ,[WhenToTake]
      ,[IsBeforeMeal]
      ,[HMS_Medicine].[MedicineName]
  FROM [dbo].[HMS_PrescriptionDetail]
  INNER JOIN [HMS_Prescription] ON ([HMS_PrescriptionDetail].[PrescriptionId] = [HMS_Prescription].[PrescriptionId])
  INNER JOIN [HMS_Medicine] ON ([HMS_PrescriptionDetail].[MedicineId] = [HMS_Medicine].[MedicineId])
 WHERE [HMS_Prescription].[PatientCheckupId] = @PatientCheckupId
  
	
  
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_PaymentDetailByPaymentId]
@PaymentId	BIGINT = NULL,
@CompanyId BIGINT = NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT 
		[HMS_PaymentCategory].[PaymentCategoryId],
		[HMS_PaymentCategory].[PaymentCategory], 
		[HMS_PaymentDetail].[PaymentAmount]
	FROM [HMS_PaymentCategory]
	LEFT JOIN [HMS_PaymentDetail] 
			ON (HMS_PaymentDetail.PaymentCategoryId = HMS_PaymentCategory.PaymentCategoryId
			AND HMS_PaymentDetail.PaymentId = @PaymentId)
    WHERE [HMS_PaymentCategory].[CompanyId] = @CompanyId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_PaymentCategoryById]
@PaymentCategoryId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[PaymentCategoryId],
				[PaymentCategory],
				[Description],
				[IsActive]
	    FROM	[dbo].[HMS_PaymentCategory]
	    WHERE	[HMS_PaymentCategory].[PaymentCategoryId] = @PaymentCategoryId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_User]
@UserId			BIGINT		 = NULL,	
@FirstName		VARCHAR(50)  = NULL,
@MiddleName		VARCHAR(50)  = NULL,
@LastName		VARCHAR(50)  = NULL,
@Email			VARCHAR(100) = NULL,
@Password		VARCHAR(50)  = NULL,
@Phone			VARCHAR(20)  = NULL,
@RoleId			BIGINT		 = NULL,
@IsActive		BIT			 = NULL,
@CreatedBy		BIGINT		 = NULL,
@CompanyId		BIGINT		 = NULL,
@ProfilePic		NVARCHAR(200)	= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
	IF EXISTS(
				SELECT	[UserId] 
				FROM	[dbo].[HMS_User] 
				WHERE	[UserId] = @UserId
			  )
	BEGIN
		
			IF NOT EXISTS(
							SELECT	[UserId] 
							FROM	[dbo].[HMS_User] 
							WHERE	[Email] = @Email 
							AND		[UserId] <> @UserId
							AND		[CompanyId] <> @CompanyId
							AND		[IsDeleted] = 0
						  )
			BEGIN
				UPDATE	[dbo].[HMS_User]
				SET		[FirstName]		= COALESCE(@FirstName,[FirstName]),
						[MiddleName]	= COALESCE(@MiddleName,[MiddleName]),
						[LastName]		= COALESCE(@LastName,[LastName]),
						[Email]			= COALESCE(@Email,[Email]),
						[Password]		= COALESCE(@Password,[Password]),
						[Phone]			= COALESCE(@Phone,[Phone]),
						[IsActive]		= COALESCE(@IsActive,[IsActive]),
						[CompanyId]		= COALESCE(@CompanyId,[CompanyId]),
						[ProfilePic]		= COALESCE(@ProfilePic,[ProfilePic]),
						[ModifiedDate]	= GETDATE()
				WHERE	[UserId]		= @UserId
				
				SELECT @UserId AS RETURNVAL,  '' AS [DuplicateColumn]
			END
			ELSE
			BEGIN
				SELECT -1 AS RETURNVAL,  'Email' AS [DuplicateColumn] -- Email already exists with other user.
			END
		
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL,  '' AS [DuplicateColumn] -- User does not exists.
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Setting]
@SiteTitle			VARCHAR(50)  = NULL,
@CopyrightText		VARCHAR(50)  = NULL,
@AdminEmail			VARCHAR(50)  = NULL,
@SupportEmail		VARCHAR(50)  = NULL,
@ContactEmail		VARCHAR(50)  = NULL,
@TollFreeNo			VARCHAR(50)  = NULL,
@ContactNo			VARCHAR(50)  = NULL,
@SMTPHost			VARCHAR(50)  = NULL,
@SMTPPort			INT			 = NULL,
@SMTPUserName		VARCHAR(50)  = NULL,
@SMTPPassword		VARCHAR(50)  = NULL,
@PageSize			INT			 = NULL,
@PagerSize			INT			 = NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
	IF EXISTS(SELECT [HMS_Setting].[SettingId] FROM [HMS_Setting])
	BEGIN
			UPDATE	[dbo].[HMS_Setting]
			SET		[SiteTitle]		= COALESCE(@SiteTitle,[SiteTitle]),
					[CopyrightText] = COALESCE(@CopyrightText,[CopyrightText]),
					[AdminEmail]	= COALESCE(@AdminEmail,[AdminEmail]),
					[SupportEmail]	= COALESCE(@SupportEmail,[SupportEmail]),
					[ContactEmail]	= COALESCE(@ContactEmail,[ContactEmail]),
					[TollFreeNo]	= COALESCE(@TollFreeNo,[TollFreeNo]),
					[ContactNo]		= COALESCE(@ContactNo,[ContactNo]),
					[SMTPHost]		= COALESCE(@SMTPHost,[SMTPHost]),
					[SMTPPort]		= COALESCE(@SMTPPort,[SMTPPort]),
					[SMTPUserName]	= COALESCE(@SMTPUserName,[SMTPUserName]),
					[SMTPPassword]	= COALESCE(@SMTPPassword,[SMTPPassword]),
					[PageSize]		= COALESCE(@PageSize,[PageSize])
			
			SELECT 1 AS RETURNVAL
	END
	ELSE
	BEGIN
		INSERT INTO [dbo].[HMS_Setting]
        (
			[SiteTitle],
			[CopyrightText],
			[AdminEmail],
			[SupportEmail],
			[ContactEmail],
			[TollFreeNo],
			[ContactNo],
			[SMTPHost],
			[SMTPPort],
			[SMTPUserName],
			[SMTPPassword],
			[PageSize]
        )
		VALUES
        (
			@SiteTitle,
			@CopyrightText,
			@AdminEmail,
			@SupportEmail,
			@ContactEmail,
			@TollFreeNo,
			@ContactNo,
			@SMTPHost,
			@SMTPPort,
			@SMTPUserName,
			@SMTPPassword,
			@PageSize
        )
           
		SELECT @@IDENTITY AS RETURNVAL
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Role]
@RoleId  		BIGINT          	=	NULL,
@RoleName		VARCHAR(50)     	=	NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
	IF EXISTS(
				SELECT	[RoleId] 
				FROM	[dbo].[HMS_Role] 
				WHERE	[RoleId] = @RoleId
			 )
	BEGIN
		UPDATE  [HMS_Role]
		SET     [RoleName]	=	COALESCE(@RoleName,[RoleName])
		WHERE   [HMS_Role].[RoleId] = @RoleId

        SELECT @RoleId AS [IdentityColumn], '' AS [DuplicateColumn]
	END
	ELSE
	BEGIN
		SELECT 0 AS [IdentityColumn], '' AS [DuplicateColumn] -- Role does not exists.
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Report]
@ReportId  			BIGINT			=	NULL,
@ReportTypeId		BIGINT			=	NULL,
@PatientId			BIGINT			=	NULL,
@CompanyId			BIGINT			=	NULL,
@Description		NVARCHAR(2000)	=	NULL,
@ReportDate			DATETIME		=	NULL,
@ReportFile			NVARCHAR(100) = NULL,
@CreatedBy			BIGINT			=	NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
	IF NOT EXISTS(
			SELECT	[ReportId] 
					FROM	[dbo].[HMS_Report] 
					WHERE	[PatientId] = @PatientId AND [ReportTypeId] = 2
					AND		[ReportId] <> @ReportId
			 )
		BEGIN
	
			UPDATE  [HMS_Report]
			SET		[ReportTypeId]  	=	COALESCE(@ReportTypeId,[ReportTypeId]),     
					[Description] 	=	COALESCE(@Description,[Description]),
					[ReportDate] 		=	COALESCE(@ReportDate,[ReportDate]),
					[ReportFile] 		=	COALESCE(@ReportFile,[ReportFile]),
					[PatientId] 	=	COALESCE(@PatientId,[PatientId]),
					[ModifiedDate]	= GETDATE(),
					[ModifiedBy] = @CreatedBy
			WHERE   [HMS_Report].[ReportId] = @ReportId

		   SELECT @ReportId AS RETURNVAL, '' AS DuplicateColumn
       
       
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'Report' AS DuplicateColumn
	END

END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[HMS_spU_PrescriptionDetail]
@PrescriptionId  		BIGINT          	=	NULL,
@PatientId  			BIGINT          	=	NULL,
@PrescriptionData			VARCHAR(MAX)		=	NULL 				
AS
BEGIN	
	DECLARE @strTerm VARCHAR(100)
		DECLARE @strLength INT
		DECLARE @N INT
		DECLARE @Index INT
		DECLARE @strTerm1 VARCHAR(10)
		DECLARE @strTermLength INT
		DECLARE @Index1 INT
		DECLARE @NoOfDays INT
		DECLARE @IsBeforeMeal BIT
		DECLARE @MedicineId BIGINT
		DECLARE @WhenToTake NVARCHAR(20)
			
		SET @strTerm = ''
		SET @strLength = LEN(@PrescriptionData)
		SET @Index = 0
		SET @N = (select count(*) from dbo.fn_Split(@PrescriptionData,'#'))
		
		DELETE FROM [HMS_PrescriptionDetail] WHERE [PrescriptionId] = @PrescriptionId
				
		WHILE (@Index < @N)
		BEGIN						
			SET @strTerm = (select top 1 * from dbo.fn_Split(@PrescriptionData,'#'))			
			SET @strTermLength = LEN(@strTerm)
			SET @strTerm1 = @strTerm
			SET @Index1 = 0			
			WHILE (@Index1 < 4)
			BEGIN	
				SET @strTerm1 = (select top 1 * from dbo.fn_Split(@strTerm,','))			
				
				if(@Index1 = 0)		
					SET @MedicineId = convert(BIGINT,@strTerm1)
				else if(@Index1 = 1)		
					SET @NoOfDays =convert(INT,@strTerm1)		
				else if(@Index1 = 2)
					SET @WhenToTake = @strTerm1 					
				else if(@Index1 = 3)
					SET @IsBeforeMeal = CONVERT(BIT,@strTerm1) 				
								
				SET @strTerm = (SUBSTRING(@strTerm,LEN(@strTerm1)+2,@strTermLength))
				SET @Index1 = @Index1 + 1			
			END
			
			
			
			SET @PrescriptionData = (SUBSTRING(@PrescriptionData,@strTermLength+2,@strLength-@strTermLength))						
											
			INSERT INTO [HMS_PrescriptionDetail]
			(
				[PrescriptionId],
				[PatientId],
				[MedicineId],
				[NoOfDays],
				[WhenToTake],
				[IsBeforeMeal]
			)
			VALUES
			(
				@PrescriptionId,
				@PatientId,
				@MedicineId,
				@NoOfDays,
				@WhenToTake,
				@IsBeforeMeal
			)
			

			SET @Index = @Index + 1
			
		END	
		SELECT 1 AS RETURNVAL	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Prescription]
@PrescriptionId  	BIGINT          	=	NULL,
@PatientCheckupId	BIGINT		=	NULL,
@PatientId   		BIGINT     	=	NULL,
@TreatmentId   		BIGINT     	=	NULL,
@CaseHistory  		NVARCHAR(MAX)     	=	NULL,
@ExtraNotes    		NVARCHAR(MAX)     	=	NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		
		UPDATE  [HMS_Prescription]
		SET     
				[PatientCheckupId] 	=	COALESCE(@PatientCheckupId,[PatientCheckupId]),
				[PatientId] 	=	COALESCE(@PatientId,[PatientId]),
				[TreatmentId]		=	COALESCE(@TreatmentId,[TreatmentId]),
				[CaseHistory]  	=	COALESCE(@CaseHistory,[CaseHistory]),
				[ExtraNotes]     =	COALESCE(@ExtraNotes,[ExtraNotes])
		WHERE   [HMS_Prescription].[PrescriptionId] = @PrescriptionId

       SELECT @PrescriptionId AS RETURNVAL
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[HMS_spU_PaymentDetail]
@PaymentId  		BIGINT          	=	NULL,
@CategoryChargeData			VARCHAR(MAX)		=	NULL 				
AS
BEGIN	
		DECLARE @strTerm VARCHAR(100)
		DECLARE @strLength INT
		DECLARE @N INT
		DECLARE @Index INT
		DECLARE @strTerm1 VARCHAR(10)
		DECLARE @strTermLength INT
		DECLARE @Index1 INT
		DECLARE @CategoryCharge MONEY
		DECLARE @PaymentCategoryId BIGINT
			
		SET @strTerm = ''
		SET @strLength = LEN(@CategoryChargeData)
		SET @Index = 0
		SET @N = (select count(*) from dbo.fn_Split(@CategoryChargeData,'#'))
		
		DELETE FROM [HMS_PaymentDetail] WHERE [PaymentId] = @PaymentId
				
		WHILE (@Index < @N)
		BEGIN						
			SET @strTerm = (select top 1 * from dbo.fn_Split(@CategoryChargeData,'#'))			
			SET @strTermLength = LEN(@strTerm)
			SET @strTerm1 = @strTerm
			SET @Index1 = 0			
			WHILE (@Index1 < 4)
			BEGIN	
				SET @strTerm1 = (select top 1 * from dbo.fn_Split(@strTerm,','))			
				
				if(@Index1 = 0)		
					SET @PaymentCategoryId = convert(BIGINT,@strTerm1)
				else if(@Index1 = 1)		
					SET @CategoryCharge =convert(MONEY,@strTerm1)		
								
				SET @strTerm = (SUBSTRING(@strTerm,LEN(@strTerm1)+2,@strTermLength))
				SET @Index1 = @Index1 + 1			
			END
			
			
			
			SET @CategoryChargeData = (SUBSTRING(@CategoryChargeData,@strTermLength+2,@strLength-@strTermLength))						
											
			INSERT INTO [HMS_PaymentDetail]
			(
				[PaymentId],
				[PaymentCategoryId],
				[PaymentAmount]
			
			)
			VALUES
			(
				@PaymentId,
				@PaymentCategoryId,
				@CategoryCharge
			)
			

			SET @Index = @Index + 1
			
		END	
		SELECT 1 AS RETURNVAL	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_PaymentCategory]
@PaymentCategoryId  	BIGINT          	=	NULL,
@PaymentCategory   	NVARCHAR(200)     	=	NULL,
@Description   	NVARCHAR(500)     	=	NULL,
@CompanyId				BIGINT		=	NULL,
@IsActive    			BIT             	=	NULL,
@CreatedBy				BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		IF NOT EXISTS(
					SELECT	[PaymentCategoryId] 
							FROM	[dbo].[HMS_PaymentCategory] 
							WHERE	[PaymentCategory] = @PaymentCategory 
							AND		[PaymentCategoryId] <> @PaymentCategoryId 
							AND		[CompanyId] <> @CompanyId
							AND [IsDeleted] = 0
				 )
	BEGIN
		UPDATE  [HMS_PaymentCategory]
		SET     [PaymentCategory] 	=	COALESCE(@PaymentCategory,[PaymentCategory]),
				[Description] 	=	COALESCE(@Description,[Description]),
				[IsActive]  	=	COALESCE(@IsActive,[IsActive]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_PaymentCategory].[PaymentCategoryId] = @PaymentCategoryId

       SELECT @PaymentCategoryId AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL, 'PaymentCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Payment]
@PaymentId  		BIGINT          =	NULL,
@InvoiceNumber   	NVARCHAR(10)    =	NULL,
@PatientId		   	BIGINT     		=	NULL,
@SubTotal			MONEY     		=	NULL,
@Discount			MONEY     		=	NULL,
@Tax				MONEY     		=	NULL,
@GrandTotal			MONEY     		=	NULL,
@PaymentStatus		INT				=	NULL,
@CompanyId			BIGINT			=	NULL,
@CreatedBy			BIGINT			=	NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		UPDATE  [HMS_Payment]
		SET     [PatientId] 	=	COALESCE(@PatientId,[PatientId]),
				[SubTotal]  	=	COALESCE(@SubTotal,[SubTotal]),
				[Discount]  	=	COALESCE(@Discount,[Discount]),
				[Tax]  			=	COALESCE(@Tax,[Tax]),
				[GrandTotal]  	=	COALESCE(@GrandTotal,[GrandTotal]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_Payment].[PaymentId] = @PaymentId

       SELECT @PaymentId AS RETURNVAL, '' AS DuplicateColumn

END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[HMS_spU_PatientTestDetail]
@PatientTestId  		BIGINT          	=	NULL,
@TestData			VARCHAR(MAX)		=	NULL 				
AS
BEGIN	
	DECLARE @strTerm VARCHAR(100)
		DECLARE @strLength INT
		DECLARE @N INT
		DECLARE @Index INT
		DECLARE @strTerm1 VARCHAR(10)
		DECLARE @strTermLength INT
		DECLARE @Index1 INT
		DECLARE @Result NVARCHAR(10)
		DECLARE @LabTestId BIGINT
		DECLARE @Remarks NVARCHAR(100)
			
		SET @strTerm = ''
		SET @strLength = LEN(@TestData)
		SET @Index = 0
		SET @N = (select count(*) from dbo.fn_Split(@TestData,'#'))
		
		DELETE FROM [HMS_PatientTestDetail] WHERE [PatientTestId] = @PatientTestId
				
		WHILE (@Index < @N)
		BEGIN						
			SET @strTerm = (select top 1 * from dbo.fn_Split(@TestData,'#'))			
			SET @strTermLength = LEN(@strTerm)
			SET @strTerm1 = @strTerm
			SET @Index1 = 0			
			WHILE (@Index1 < 3)
			BEGIN	
				SET @strTerm1 = (select top 1 * from dbo.fn_Split(@strTerm,','))			
				
				if(@Index1 = 0)		
					SET @LabTestId = convert(BIGINT,@strTerm1)
				else if(@Index1 = 1)		
					SET @Result =@strTerm1		
				else if(@Index1 = 2)
					SET @Remarks = @strTerm1 					
								
				SET @strTerm = (SUBSTRING(@strTerm,LEN(@strTerm1)+2,@strTermLength))
				SET @Index1 = @Index1 + 1			
			END
			
			
			
			SET @TestData = (SUBSTRING(@TestData,@strTermLength+2,@strLength-@strTermLength))						
											
			INSERT INTO [HMS_PatientTestDetail]
			(
				[PatientTestId],
				[LabTestId],
				[Result],
				[Remarks]
			)
			VALUES
			(
				@PatientTestId,
				@LabTestId,
				@Result,
				@Remarks
			)
			

			SET @Index = @Index + 1
			
		END	
		SELECT 1 AS RETURNVAL	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_PatientTest]
@PatientTestId  		BIGINT          =	NULL,
@PatientId			BIGINT     		=	NULL,
@ConsultantId		BIGINT     		=	NULL,
@TestDate			DATETIME    	=	NULL,
@DeliveryDate		DATETIME    	=	NULL,
@ReportStatus		INT				=	NULL,
@CompanyId			BIGINT			=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		UPDATE  [HMS_PatientTest]
		SET     [PatientId] 	=	COALESCE(@PatientId,[PatientId]),
				[ConsultantId]  =	COALESCE(@ConsultantId,[ConsultantId]),
				[TestDate]  	=	COALESCE(@TestDate,[TestDate]),
				[DeliveryDate]  =	COALESCE(@DeliveryDate,[DeliveryDate]),
				[ReportStatus]  =	COALESCE(@ReportStatus,[ReportStatus]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_PatientTest].[PatientTestId] = @PatientTestId

       SELECT @PatientTestId AS RETURNVAL, '' AS DuplicateColumn

END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_PatientCheckup]
@PatientCheckupId  	BIGINT          	=	NULL,
@PatientId   		BIGINT     			=	NULL,
@DoctorId   		BIGINT     			=	NULL,
@Symptoms  			NVARCHAR(MAX)     	=	NULL,
@Diagnosis    		NVARCHAR(MAX)     	=	NULL,
@CheckupDate    	DATETIME     		=	NULL,
@CreatedBy			BIGINT				=	NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		
		UPDATE  [HMS_PatientCheckup]
		SET     [PatientId] 	=	COALESCE(@PatientId,[PatientId]),
				[DoctorId]		=	COALESCE(@DoctorId,[DoctorId]),
				[Symptoms]  	=	COALESCE(@Symptoms,[Symptoms]),
				[Diagnosis]     =	COALESCE(@Diagnosis,[Diagnosis]),
				[CheckupDate]   =	COALESCE(@CheckupDate,[CheckupDate]),
				[ModifiedBy]  	=	@CreatedBy,
				[ModifiedDate]	=   GETDATE()
		WHERE   [HMS_PatientCheckup].[PatientCheckupId] = @PatientCheckupId

       SELECT @PatientCheckupId AS RETURNVAL
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Patient]
@UserId			BIGINT		 = NULL,	
@FirstName		VARCHAR(50)  = NULL,
@MiddleName		VARCHAR(50)  = NULL,
@LastName		VARCHAR(50)  = NULL,
@Email			VARCHAR(100) = NULL,
@Password		VARCHAR(50)  = NULL,
@Phone			VARCHAR(20)  = NULL,
@RoleId			BIGINT		 = NULL,
@IsActive		BIT			 = NULL,
@CreatedBy		BIGINT		 = NULL,
@CompanyId		BIGINT		 = NULL,
@BirthDate		DATETIME		= NULL,
@Gender			VARCHAR(10)		= NULL,
@BloodGroup		NVARCHAR(10)	= NULL,
@Address1		NVARCHAR(500)	= NULL,
@ProfilePic		NVARCHAR(200)	= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
	IF EXISTS(
				SELECT	[UserId] 
				FROM	[dbo].[HMS_User] 
				WHERE	[UserId] = @UserId
			  )
	BEGIN
		
			IF NOT EXISTS(
							SELECT	[UserId] 
							FROM	[dbo].[HMS_User] 
							WHERE	[Email] = @Email 
							AND		[UserId] <> @UserId
							AND		[CompanyId] = @CompanyId
							AND		[IsDeleted] = 0
						  )
			BEGIN
				UPDATE	[dbo].[HMS_User]
				SET		[FirstName]		= COALESCE(@FirstName,[FirstName]),
						[MiddleName]	= COALESCE(@MiddleName,[MiddleName]),
						[LastName]		= COALESCE(@LastName,[LastName]),
						[Email]			= COALESCE(@Email,[Email]),
						[Password]		= COALESCE(@Password,[Password]),
						[Phone]			= COALESCE(@Phone,[Phone]),
						[IsActive]		= COALESCE(@IsActive,[IsActive]),
						[CompanyId]		= COALESCE(@CompanyId,[CompanyId]),
						[Address1]		= COALESCE(@Address1,[Address1]),
						[ProfilePic]		= COALESCE(@ProfilePic,[ProfilePic]),
						[ModifiedDate]	= GETDATE()
				WHERE	[UserId]		= @UserId
				
				UPDATE	[dbo].[HMS_PatientDetail]
				SET		[BirthDate]		= COALESCE(@BirthDate,[BirthDate]),
						[Gender]		= COALESCE(@Gender,[Gender]),
						[BloodGroup]		= COALESCE(@BloodGroup,[BloodGroup])
				WHERE	[UserId]		= @UserId
				
				
				SELECT @UserId AS RETURNVAL,  '' AS [DuplicateColumn]
			END
			ELSE
			BEGIN
				SELECT -1 AS RETURNVAL,  'Email' AS [DuplicateColumn] -- Email already exists with other user.
			END
		
	END
	ELSE
	BEGIN
		SELECT 0 AS RETURNVAL,  '' AS [DuplicateColumn] -- User does not exists.
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_MedicineCategory]
@MedicineCategoryId  	BIGINT          	=	NULL,
@MedicineCategory   	NVARCHAR(500)     	=	NULL,
@CompanyId				BIGINT		=	NULL,
@IsActive    			BIT             	=	NULL,
@CreatedBy				BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		IF NOT EXISTS(
					SELECT	[MedicineCategoryId] 
							FROM	[dbo].[HMS_MedicineCategory] 
							WHERE	[MedicineCategory] = @MedicineCategory 
							AND		[MedicineCategoryId] <> @MedicineCategoryId 
							AND		[CompanyId] <> @CompanyId
							AND [IsDeleted] = 0
				 )
	BEGIN
		UPDATE  [HMS_MedicineCategory]
		SET     [MedicineCategory] 	=	COALESCE(@MedicineCategory,[MedicineCategory]),
				[IsActive]  	=	COALESCE(@IsActive,[IsActive]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_MedicineCategory].[MedicineCategoryId] = @MedicineCategoryId

       SELECT @MedicineCategoryId AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL, 'MedicineCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Medicine]
@MedicineId  		BIGINT          	=	NULL,
@MedicineName   	NVARCHAR(500)     	=	NULL,
@Description  		NVARCHAR(1000)     	=	NULL,
@MedicineCategoryId BIGINT     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		IF NOT EXISTS(
					SELECT	[MedicineId] 
							FROM	[dbo].[HMS_Medicine] 
							WHERE	[MedicineName] = @MedicineName 
							AND		[MedicineId] <> @MedicineId AND [IsDeleted] = 0
				 )
	BEGIN
		UPDATE  [HMS_Medicine]
		SET     [MedicineName] 	=	COALESCE(@MedicineName,[MedicineName]),
				[Description]		=	COALESCE(@Description,[Description]),
				[MedicineCategoryId]  	=	COALESCE(@MedicineCategoryId,[MedicineCategoryId]),
				[IsActive]  	=	COALESCE(@IsActive,[IsActive]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_Medicine].[MedicineId] = @MedicineId

       SELECT @MedicineId AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL, 'Medicine' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_LabTestCategory]
@LabTestCategoryId  	BIGINT          	=	NULL,
@LabTestCategoryName   	NVARCHAR(200)     	=	NULL,
@Description   	NVARCHAR(500)     	=	NULL,
@CompanyId				BIGINT		=	NULL,
@IsActive    			BIT             	=	NULL,
@CreatedBy				BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		IF NOT EXISTS(
					SELECT	[LabTestCategoryId] 
							FROM	[dbo].[HMS_LabTestCategory] 
							WHERE	[LabTestCategoryName] = @LabTestCategoryName 
							AND		[LabTestCategoryId] <> @LabTestCategoryId 
							AND		[CompanyId] <> @CompanyId
							AND [IsDeleted] = 0
				 )
	BEGIN
		UPDATE  [HMS_LabTestCategory]
		SET     [LabTestCategoryName] 	=	COALESCE(@LabTestCategoryName,[LabTestCategoryName]),
				[Description] 	=	COALESCE(@Description,[Description]),
				[IsActive]  	=	COALESCE(@IsActive,[IsActive]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_LabTestCategory].[LabTestCategoryId] = @LabTestCategoryId

       SELECT @LabTestCategoryId AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL, 'LabTestCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_LabTest]
@LabTestId  		BIGINT          	=	NULL,
@LabTestCategoryId	BIGINT     	=	NULL,
@LabTestName		NVARCHAR(100) = NULL,
@TestUnit			NVARCHAR(10) = NULL,
@ReferenceRange		NVARCHAR(50) = NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive			BIT			=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		IF NOT EXISTS(
					SELECT	[LabTestId] 
					FROM	[dbo].[HMS_LabTest] 
					WHERE	[LabTestName] = @LabTestName AND [IsDeleted] = 0
					AND [LabTestId] <> @LabTestId
					AND [LabTestCategoryId] = @LabTestCategoryId
					AND		[CompanyId] = @CompanyId
				 )
	BEGIN
	
		UPDATE  [HMS_LabTest]
		SET     [LabTestName] 	=	COALESCE(@LabTestName,[LabTestName]),
				[TestUnit] 		=	COALESCE(@TestUnit,[TestUnit]),
				[ReferenceRange] 	=	COALESCE(@ReferenceRange,[ReferenceRange]),
				[LabTestCategoryId]  	=	COALESCE(@LabTestCategoryId,[LabTestCategoryId]),
				[IsActive]  	=	COALESCE(@IsActive,[IsActive]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_LabTest].[LabTestId] = @LabTestId

       SELECT @LabTestId AS RETURNVAL, '' AS DuplicateColumn
       
       END
      ELSE
      BEGIN
		SELECT -1 AS RETURNVAL, 'Lab Test' AS DuplicateColumn
      END
       
      

END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_ExpenseCategory]
@ExpenseCategoryId  	BIGINT          	=	NULL,
@ExpenseCategory   	NVARCHAR(200)     	=	NULL,
@Description   	NVARCHAR(500)     	=	NULL,
@CompanyId				BIGINT		=	NULL,
@IsActive    			BIT             	=	NULL,
@CreatedBy				BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		IF NOT EXISTS(
					SELECT	[ExpenseCategoryId] 
							FROM	[dbo].[HMS_ExpenseCategory] 
							WHERE	[ExpenseCategory] = @ExpenseCategory 
							AND		[ExpenseCategoryId] <> @ExpenseCategoryId 
							AND		[CompanyId] <> @CompanyId
							AND [IsDeleted] = 0
				 )
	BEGIN
		UPDATE  [HMS_ExpenseCategory]
		SET     [ExpenseCategory] 	=	COALESCE(@ExpenseCategory,[ExpenseCategory]),
				[Description] 	=	COALESCE(@Description,[Description]),
				[IsActive]  	=	COALESCE(@IsActive,[IsActive]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_ExpenseCategory].[ExpenseCategoryId] = @ExpenseCategoryId

       SELECT @ExpenseCategoryId AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL, 'ExpenseCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Expense]
@ExpenseId  		BIGINT          	=	NULL,
@ExpenseCategoryId	BIGINT     	=	NULL,
@ExpenseAmount   	MONEY     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		UPDATE  [HMS_Expense]
		SET     [ExpenseAmount] 	=	COALESCE(@ExpenseAmount,[ExpenseAmount]),
				[ExpenseCategoryId]  	=	COALESCE(@ExpenseCategoryId,[ExpenseCategoryId]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_Expense].[ExpenseId] = @ExpenseId

       SELECT @ExpenseId AS RETURNVAL, '' AS DuplicateColumn

END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_EmailTemplate]
@EmailTemplateId		BIGINT			= NULL,	
@Code					VARCHAR(50)		= NULL,
@Title					VARCHAR(100)	= NULL,
@Subject				VARCHAR(100)	= NULL,
@FromEmail				VARCHAR(100)	= NULL,
@Message				NVARCHAR(MAX)	= NULL,
@Signature				NVARCHAR(MAX)	= NULL,
@IsActive				BIT				= NULL 
AS
BEGIN	
	SET NOCOUNT ON;

		IF NOT EXISTS(
						SELECT	[EmailTemplateId] 
						FROM	[dbo].[HMS_EmailTemplate] 
						WHERE	[Code] = @Code 
						AND		[EmailTemplateId] <> @EmailTemplateId AND [IsDeleted] = 0
					)
		BEGIN
			UPDATE	[dbo].[HMS_EmailTemplate]
			SET		[Code]			= COALESCE(@Code,[Code]),
					[Title]			= COALESCE(@Title,[Title]),
					[Subject]		= COALESCE(@Subject,[Subject]),
					[FromEmail]		= COALESCE(@FromEmail,[FromEmail]),
					[Message]		= COALESCE(@Message,[Message]),
					[Signature]		= COALESCE(@Signature,[Signature]),
					[IsActive]		= COALESCE(@IsActive,[IsActive]),
					[ModifiedDate]	= GETDATE()
			WHERE	[EmailTemplateId] = @EmailTemplateId
			
			SELECT @EmailTemplateId AS RETURNVAL,'' AS DuplicateColumn
		END
		ELSE
		BEGIN
			SELECT -1 AS RETURNVAL, 'EmailTemplate' AS DuplicateColumn -- Code already exists with other EmailTemplate.
		END
		
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Company]
@CompanyId  		BIGINT          	=	NULL,
@CompanyName   		NVARCHAR(500)     	=	NULL,
@Address1  		NVARCHAR(250)     	=	NULL,
@Address2    		NVARCHAR(250)     	=	NULL,
@City    		NVARCHAR(50)     	=	NULL,
@State    		NVARCHAR(50)     	=	NULL,
@ZipCode    		NVARCHAR(20)     	=	NULL,
@Email       		VARCHAR(255)    	=	NULL,
@Phone       		NVARCHAR(20)     	=	NULL,
@Fax       		NVARCHAR(20)     	=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy		BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		IF NOT EXISTS(
					SELECT	[CompanyId] 
							FROM	[dbo].[HMS_Company] 
							WHERE	[CompanyName] = @CompanyName 
							AND		[CompanyId] <> @CompanyId AND [IsDeleted] = 0
				 )
	BEGIN
		UPDATE  [HMS_Company]
		SET     [CompanyName] 	=	COALESCE(@CompanyName,[CompanyName]),
				[Address1]		=	COALESCE(@Address1,[Address1]),
				[Address2]  	=	COALESCE(@Address2,[Address2]),
				[City]     		=	COALESCE(@City,[City]),
				[State]     	=	COALESCE(@State,[State]),
				[ZipCode]     	=	COALESCE(@ZipCode,[ZipCode]),
				[Email]     	=	COALESCE(@Email,[Email]),
				[Phone]     	=	COALESCE(@Phone,[Phone]),
				[Fax]       	=	COALESCE(@Fax,[Fax]),
				[IsActive]  	=	COALESCE(@IsActive,[IsActive]),
				[ModifiedDate]	= GETDATE()
		WHERE   [HMS_Company].[CompanyId] = @CompanyId

       SELECT @CompanyId AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL, 'Hospital' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[HMS_spU_BloodInventory]
@CompanyId  		BIGINT          	=	NULL,
@InventoryData		VARCHAR(MAX)		=	NULL, 				
@ModifiedBy			BIGINT				=	NULL
AS
BEGIN	
		DECLARE @strTerm VARCHAR(100)
		DECLARE @strLength INT
		DECLARE @N INT
		DECLARE @Index INT
		DECLARE @strTerm1 VARCHAR(10)
		DECLARE @strTermLength INT
		DECLARE @Index1 INT
		DECLARE @Availability INT
		DECLARE @BloodGroup NVARCHAR(10)
			
		SET @strTerm = ''
		SET @strLength = LEN(@InventoryData)
		SET @Index = 0
		SET @N = (select count(*) from dbo.fn_Split(@InventoryData,'#'))
		
		WHILE (@Index < @N)
		BEGIN						
			SET @strTerm = (select top 1 * from dbo.fn_Split(@InventoryData,'#'))			
			SET @strTermLength = LEN(@strTerm)
			SET @strTerm1 = @strTerm
			SET @Index1 = 0			
			WHILE (@Index1 < 2)
			BEGIN	
				SET @strTerm1 = (select top 1 * from dbo.fn_Split(@strTerm,','))			
				
				if(@Index1 = 0)		
					SET @BloodGroup = @strTerm1
				else if(@Index1 = 1)		
					SET @Availability =convert(INT,@strTerm1)		
								
				SET @strTerm = (SUBSTRING(@strTerm,LEN(@strTerm1)+2,@strTermLength))
				SET @Index1 = @Index1 + 1			
			END
			
			SET @InventoryData = (SUBSTRING(@InventoryData,@strTermLength+2,@strLength-@strTermLength))						
			
			IF EXISTS(SELECT [CompanyId] FROM [HMS_BloodInventory] WHERE [CompanyId] = @CompanyId AND [BloodGroup] = @BloodGroup)
			BEGIN
				UPDATE  [HMS_BloodInventory]
				SET     [Availability] 	=	COALESCE(@Availability,[Availability]),
						[ModifiedDate]	= GETDATE(),
						[ModifiedBy] = @ModifiedBy
				WHERE   [CompanyId] = @CompanyId AND [BloodGroup] = @BloodGroup
			END
			ELSE
			BEGIN
				INSERT INTO [HMS_BloodInventory]
				(
					[BloodGroup],
					[Availability],
					[CompanyId],
					[ModifiedDate],
					[ModifiedBy]
				)
				VALUES
				(
					@BloodGroup,
					@Availability,
					@CompanyId,
					GETDATE(),
					@ModifiedBy
				)
			END

			SET @Index = @Index + 1
			
		END	
		SELECT 1 AS RETURNVAL	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_BedCategory]
@BedCategoryId  	BIGINT          	=	NULL,
@BedCategory   	NVARCHAR(200)     	=	NULL,
@Description   	NVARCHAR(500)     	=	NULL,
@CompanyId				BIGINT		=	NULL,
@IsActive    			BIT             	=	NULL,
@CreatedBy				BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		IF NOT EXISTS(
					SELECT	[BedCategoryId] 
							FROM	[dbo].[HMS_BedCategory] 
							WHERE	[BedCategory] = @BedCategory 
							AND		[BedCategoryId] <> @BedCategoryId 
							AND		[CompanyId] <> @CompanyId
							AND [IsDeleted] = 0
				 )
	BEGIN
		UPDATE  [HMS_BedCategory]
		SET     [BedCategory] 	=	COALESCE(@BedCategory,[BedCategory]),
				[Description] 	=	COALESCE(@Description,[Description]),
				[IsActive]  	=	COALESCE(@IsActive,[IsActive]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_BedCategory].[BedCategoryId] = @BedCategoryId

       SELECT @BedCategoryId AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL, 'BedCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_BedAllotment]
@BedAllotmentId  		BIGINT          	=	NULL,
@PatientId				BIGINT     	=	NULL,
@BedCategoryId			BIGINT     	=	NULL,
@BedId					BIGINT     	=	NULL,
@AllotmentDate			DATETIME     	=	NULL,
@DischargeDate			DATETIME     	=	NULL,
@Notes  				NVARCHAR(500)     	=	NULL,
@CompanyId				BIGINT		=	NULL,
@CreatedBy				BIGINT			= NULL

AS
BEGIN	
	SET NOCOUNT ON;
	
		UPDATE  [HMS_BedAllotment]
		SET     [BedId] 	=	COALESCE(@BedId,[BedId]),
				[BedCategoryId] 	=	COALESCE(@BedCategoryId,[BedCategoryId]),
				[AllotmentDate] 	=	COALESCE(@AllotmentDate,[AllotmentDate]),
				[DischargeDate] 	=	COALESCE(@DischargeDate,[DischargeDate]),
				[Notes]				=	COALESCE(@Notes,[Notes]),
				[ModifiedDate]		= GETDATE(),
				[ModifiedBy]		= @CreatedBy
		WHERE   [HMS_BedAllotment].[BedAllotmentId] = @BedAllotmentId

       SELECT @BedAllotmentId AS RETURNVAL, '' AS DuplicateColumn
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spU_Bed]
@BedId  		BIGINT          	=	NULL,
@BedName   	NVARCHAR(500)     	=	NULL,
@Description  		NVARCHAR(1000)     	=	NULL,
@BedCategoryId BIGINT     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN	
	SET NOCOUNT ON;
	
		IF NOT EXISTS(
					SELECT	[BedId] 
							FROM	[dbo].[HMS_Bed] 
							WHERE	[BedName] = @BedName 
							AND		[BedId] <> @BedId AND [IsDeleted] = 0
				 )
	BEGIN
		UPDATE  [HMS_Bed]
		SET     [BedName] 	=	COALESCE(@BedName,[BedName]),
				[Description]		=	COALESCE(@Description,[Description]),
				[BedCategoryId]  	=	COALESCE(@BedCategoryId,[BedCategoryId]),
				[IsActive]  	=	COALESCE(@IsActive,[IsActive]),
				[ModifiedDate]	= GETDATE(),
				[ModifiedBy] = @CreatedBy
		WHERE   [HMS_Bed].[BedId] = @BedId

       SELECT @BedId AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL, 'Bed' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_ValidateLogin]
@Email		VARCHAR(50) = NULL,
@Password		VARCHAR(50) = NULL
AS
BEGIN

	SET NOCOUNT ON;
	
	IF EXISTS(
				SELECT	[UserId] 
				FROM	[dbo].[HMS_User] 
				WHERE	[Email] = @Email 
				AND		[Password] = @Password 
				AND		[IsActive] = 1 
				AND		[IsDeleted] = 0
			 )
	BEGIN
		SELECT		[HMS_User].[UserId],
					[HMS_User].[FirstName],
					[HMS_User].[MiddleName],
					[HMS_User].[LastName],
					[HMS_User].[Email],
					[HMS_User].[Password],
					[HMS_User].[Phone],
					[HMS_User].[RoleId],
					[HMS_Role].[RoleName],
					[HMS_User].[IsActive],
					[HMS_User].[CompanyId],
					[HMS_User].[ProfilePic]
		FROM		[dbo].[HMS_User] 
		INNER JOIN	[dbo].[HMS_Role] ON [HMS_User].[RoleId] = [HMS_Role].[RoleId]
		WHERE		[Email] = @Email 
		AND			[Password]  = @Password 
		AND			[IsActive]  = 1 
		AND			[IsDeleted] = 0
	END
	ELSE IF EXISTS(
					SELECT	[UserId] 
					FROM	[dbo].[HMS_User] 
					WHERE	[Email] = @Email 
					AND		[Password] = @Password 
					AND		[IsDeleted] = 1
				  )
	BEGIN
		SELECT	-1 AS [UserId],
				'' AS [FirstName],
				'' AS [MiddleName],
				'' AS [LastName],
				'' AS [Email],
				'' AS [Password],
				'' AS [Phone],
				0 AS [RoleId],
				'' AS [RoleName],
				0 AS [CompanyId],
				0 AS [IsActive],	 -- User is not exists.
				'' AS [ProfilePic]
	END
	ELSE IF EXISTS(
					SELECT	[UserId] 
					FROM	[dbo].[HMS_User] 
					WHERE	[Email] = @Email
					AND		[Password] = @Password 
					AND		[IsActive] = 0
				  )
	BEGIN
		SELECT	-2 AS [UserId],
				'' AS [FirstName],
				'' AS [MiddleName],
				'' AS [LastName],
				'' AS [Email],
				'' AS [Password],
				'' AS [Phone],
				0 AS [RoleId],
				'' AS [RoleName],
				0 AS [CompanyId],
				0 AS [IsActive],	 -- User is not active.
				'' AS [ProfilePic]
	END	
	ELSE
	BEGIN
		SELECT	0 AS [UserId],
				'' AS [FirstName],
				'' AS [MiddleName],
				'' AS [LastName],
				'' AS [Email],
				'' AS [Password],
				'' AS [Phone],
				0 AS [RoleId],
				'' AS [RoleName],
				0 AS [CompanyId],
				0 AS [IsActive],	 -- UserName and Password doesn't exist
				'' AS [ProfilePic]
	END	
  
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_UserById]
@UserId	 BIGINT = NULL,
@RoleId	 BIGINT	= NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT	[UserId],
			[FirstName],
			[MiddleName],
			[LastName],
			[Email],
			[Password],
			[Phone],
			[RoleId],
			[IsActive],
			[CompanyId],
			[ProfilePic]
	FROM	[dbo].[HMS_User]
	WHERE	[UserId] = @UserId  
	AND		[RoleId] = ISNULL(@RoleId,[RoleId])
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_UserByEmail]
@Email	 VARCHAR(100) = NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT	[UserId],
			[FirstName],
			[MiddleName],
			[LastName],
			[Email],
			[Password],
			[Phone],
			[RoleId],
			[IsActive],
			[CompanyId],
			[ProfilePic]
	FROM	[dbo].[HMS_User]
	WHERE	[Email] = @Email
	AND		[IsActive] = 1
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_PaymentById]
@PaymentId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
      SELECT	[HMS_Payment].[PaymentId],
				[HMS_Payment].[InvoiceNumber],
				[HMS_Payment].[PatientId],
				[HMS_Payment].[CompanyId],
				[HMS_Payment].[PaymentDate],
				[HMS_Payment].[SubTotal],
				[HMS_Payment].[Discount],
				[HMS_Payment].[Tax],
				[HMS_Payment].[GrandTotal],
				[HMS_Payment].[PaymentStatus],
				'' AS [PatientName]
	    FROM	[dbo].[HMS_Payment]
	    WHERE	[HMS_Payment].[PaymentId] = @PaymentId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[HMS_spS_PatientTestDetailByPatientTestId]
@PatientTestId	BIGINT = NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT [PatientTestDetailId]
      ,[HMS_PatientTestDetail].[PatientTestId]
      ,[HMS_PatientTestDetail].[LabTestId]
      ,[Result]
      ,[Remarks]
      ,[HMS_LabTest].[LabTestName]
  FROM [dbo].[HMS_PatientTestDetail]
  INNER JOIN [HMS_LabTest] ON ([HMS_PatientTestDetail].[LabTestId] = [HMS_LabTest].[LabTestId])
 WHERE [HMS_PatientTestDetail].[PatientTestId] = @PatientTestId
  
	
  
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_PatientTestById]
@PatientTestId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
      SELECT	[HMS_PatientTest].[PatientTestId],
				[HMS_PatientTest].[PatientId],
				[HMS_PatientTest].[CompanyId],
				[HMS_PatientTest].[ConsultantId],
				[HMS_PatientTest].[TestDate],
				[HMS_PatientTest].[DeliveryDate],
				[HMS_PatientTest].[ReportStatus],
				'' AS [PatientName]
	    FROM	[dbo].[HMS_PatientTest]
	    WHERE	[HMS_PatientTest].[PatientTestId] = @PatientTestId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_PatientById]
@UserId	 BIGINT = NULL,
@RoleId	 BIGINT	= NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT	[HMS_User].[UserId],
			[HMS_User].[FirstName],
			[HMS_User].[MiddleName],
			[HMS_User].[LastName],
			[HMS_User].[Email],
			[HMS_User].[Password],
			[HMS_User].[Phone],
			[HMS_User].[RoleId],
			[HMS_User].[IsActive],
			[HMS_User].[CompanyId],
			[HMS_PatientDetail].[BirthDate],
			[HMS_PatientDetail].[Gender],
			[HMS_PatientDetail].[BloodGroup],
			[HMS_User].[Address1],
			[HMS_User].[ProfilePic],
			[HMS_PatientDetail].[PatientCode],
			[HMS_Company].[CompanyName]
	FROM	[dbo].[HMS_User]
	INNER JOIN [HMS_PatientDetail] ON ([HMS_User].[UserId] = [HMS_PatientDetail].[UserId])
	INNER JOIN [HMS_Company] ON ([HMS_Company].[CompanyId] = [HMS_User].[CompanyId])
	WHERE	[HMS_User].[UserId] = @UserId
	AND		[HMS_User].[RoleId] = ISNULL(@RoleId, [HMS_User].[RoleId])
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_MedicineCategoryById]
@MedicineCategoryId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[MedicineCategoryId],
				[MedicineCategory],
				[IsActive]
	    FROM	[dbo].[HMS_MedicineCategory]
	    WHERE	[HMS_MedicineCategory].[MedicineCategoryId] = @MedicineCategoryId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_MedicineById]
@MedicineId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[MedicineId],
				[MedicineName],
				[Description],
				[MedicineCategoryId],
				[IsActive],
				'' AS [MedicineCategory]
	    FROM	[dbo].[HMS_Medicine]
	    WHERE	[HMS_Medicine].[MedicineId] = @MedicineId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_LabTestCategoryById]
@LabTestCategoryId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[LabTestCategoryId],
				[LabTestCategoryName],
				[Description],
				[IsActive]
	    FROM	[dbo].[HMS_LabTestCategory]
	    WHERE	[HMS_LabTestCategory].[LabTestCategoryId] = @LabTestCategoryId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_LabTestById]
@LabTestId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[LabTestId],
				[LabTestName],
				[TestUnit],
				[ReferenceRange],
				[IsActive],
				[LabTestCategoryId],
				'' AS [LabTestCategoryName]
	    FROM	[dbo].[HMS_LabTest]
	    WHERE	[HMS_LabTest].[LabTestId] = @LabTestId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_ExpenseCategoryById]
@ExpenseCategoryId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[ExpenseCategoryId],
				[ExpenseCategory],
				[Description],
				[IsActive]
	    FROM	[dbo].[HMS_ExpenseCategory]
	    WHERE	[HMS_ExpenseCategory].[ExpenseCategoryId] = @ExpenseCategoryId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_ExpenseById]
@ExpenseId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[ExpenseId],
				[ExpenseAmount],
				[ExpenseDate],
				[ExpenseCategoryId],
				'' AS [ExpenseCategory]
	    FROM	[dbo].[HMS_Expense]
	    WHERE	[HMS_Expense].[ExpenseId] = @ExpenseId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_EmailTemplateById]
@EmailTemplateId	BIGINT = NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT	[EmailTemplateId],
			[Code],
			[Title],
			[Subject],
			[FromEmail],
			[Message],
			[Signature],
			[IsActive],
			[IsDeleted]      
	FROM	[dbo].[HMS_EmailTemplate]
	WHERE	[EmailTemplateId] = @EmailTemplateId  
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_EmailTemplateByCode]
@Code	VARCHAR(50) = NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT	[EmailTemplateId],
			[Code],
			[Title],
			[Subject],
			[FromEmail],
			[Message],
			[Signature],
			[IsActive],
			[IsDeleted]      
	FROM	[dbo].[HMS_EmailTemplate]
	WHERE	[Code] = @Code
	AND		[IsActive] = 1
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC spS_Dashboard

CREATE PROCEDURE [dbo].[HMS_spS_Dashboard]
@RoleId		BIGINT = NULL,
@CompanyId	BIGINT = NULL
AS
BEGIN

	SET NOCOUNT ON;
	
	IF (@RoleId = 1)  -- superadmin
	BEGIN
		SELECT		
			(SELECT COUNT([CompanyId]) FROM [dbo].[HMS_Company] WHERE [IsActive] = 1 AND [IsDeleted] = 0) AS [CompanyCount],
			(SELECT	COUNT([UserId]) FROM [dbo].[HMS_User] WHERE [IsActive] = 1 AND [IsDeleted] = 0 AND [RoleId] = 2 ) AS [AdminCount]
	END
	ELSE IF (@RoleId IN (2,3,4,5,6,7))  -- Admin
	BEGIN
		SELECT		
			(SELECT	COUNT([UserId]) FROM [dbo].[HMS_User] WHERE [RoleId] = 3 AND [CompanyId] = @CompanyId AND [IsActive] = 1 AND [IsDeleted] = 0) AS [DoctorCount],
			(SELECT	COUNT([UserId]) FROM [dbo].[HMS_User] WHERE [RoleId] = 4 AND [CompanyId] = @CompanyId AND [IsActive] = 1 AND [IsDeleted] = 0) AS [NurseCount],
			(SELECT	COUNT([UserId]) FROM [dbo].[HMS_User] WHERE [RoleId] = 5 AND [CompanyId] = @CompanyId AND [IsActive] = 1 AND [IsDeleted] = 0) AS [PharmacistCount],
			(SELECT	COUNT([UserId]) FROM [dbo].[HMS_User] WHERE [RoleId] = 6 AND [CompanyId] = @CompanyId AND [IsActive] = 1 AND [IsDeleted] = 0) AS [LaboratoristCount],
			(SELECT	COUNT([UserId]) FROM [dbo].[HMS_User] WHERE [RoleId] = 7 AND [CompanyId] = @CompanyId AND [IsActive] = 1 AND [IsDeleted] = 0) AS [AccountantCount],
			(SELECT	COUNT([UserId]) FROM [dbo].[HMS_User] WHERE [RoleId] = 8 AND [CompanyId] = @CompanyId AND [IsActive] = 1 AND [IsDeleted] = 0) AS [PatientCount],
			(SELECT	COUNT([MedicineId]) FROM [dbo].[HMS_Medicine] WHERE [CompanyId] = @CompanyId AND [IsActive] = 1 AND [IsDeleted] = 0) AS [MedicineCount],
			(SELECT	COUNT([BedId]) FROM [dbo].[HMS_Bed] WHERE  [CompanyId] = @CompanyId AND [IsActive] = 1 AND [IsDeleted] = 0) AS [BedCount]
	END
	--ELSE IF (@RoleId = 3)
	--BEGIN
	--	SELECT 2
	--END -- Doctor
	--ELSE IF (@RoleId = 4)
	--BEGIN
	--	SELECT 3
	--END -- Nurse
	--ELSE IF (@RoleId = 5)
	--BEGIN
	--	SELECT 4
	--END -- Pharmacist
	--ELSE IF (@RoleId = 6)
	--BEGIN
	--	SELECT 5
	--END -- Laboratorist
	--ELSE IF (@RoleId = 7)
	--BEGIN
	--	SELECT 6
	--END -- Accountant
	--ELSE IF (@RoleId = 8)
	--BEGIN
	--	SELECT 7
	--END -- Patient
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_CompanyById]
@CompanyId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[CompanyId],
				[CompanyName],
				[Address1],
				[Address2],
				[City],
				[State],
				[ZipCode],
				[Email],
				[Phone],
				[Fax],
				[IsActive]
	    FROM	[dbo].[HMS_Company]
	    WHERE	[HMS_Company].[CompanyId] = @CompanyId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_CompanyByBloodGroup]
@BloodGroup  		NVARCHAR(10)      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[CompanyId],
				[CompanyName],
				[Address1],
				[Address2],
				[City],
				[State],
				[ZipCode],
				[Email],
				[Phone],
				[Fax],
				[IsActive]
	    FROM	[dbo].[HMS_Company]
	    WHERE	[HMS_Company].[CompanyId] IN (
					SELECT [CompanyId] FROM [HMS_BloodInventory] 
					WHERE [BloodGroup] = @BloodGroup
					AND [Availability] > 0	
			)	
			
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_CheckupDetailByPatientId]
@PatientId	BIGINT = NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT [PatientCheckupId]
      ,[HMS_PatientCheckup].[PatientId]
      ,[HMS_PatientCheckup].[CheckupDate]
      ,[HMS_PatientCheckup].[DoctorId]
      ,[HMS_PatientCheckup].[Symptoms]
      ,[HMS_PatientCheckup].[Diagnosis]
      ,[HMS_User].FirstName + ' '  + [HMS_User].[LastName] AS 'DoctorName'
  FROM [dbo].[HMS_PatientCheckup]
  INNER JOIN [HMS_User] ON [HMS_PatientCheckup].[DoctorId] = [HMS_User].[UserId]
  WHERE [HMS_PatientCheckup].[PatientId] = @PatientId
  
  ORDER BY [CheckupDate] DESC
  
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_CheckupDetailById]
@PatientCheckupId	BIGINT = NULL
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT [PatientCheckupId]
      ,[HMS_PatientCheckup].[PatientId]
      ,[HMS_PatientCheckup].[CheckupDate]
      ,[HMS_PatientCheckup].[DoctorId]
      ,[HMS_PatientCheckup].[Symptoms]
      ,[HMS_PatientCheckup].[Diagnosis]
  FROM [dbo].[HMS_PatientCheckup]
  WHERE [HMS_PatientCheckup].[PatientCheckupId] = @PatientCheckupId
  
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_BloodInventory]
@CompanyId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
   SELECT 
		[HMS_BloodGroup].[BloodGroup],
		ISNULL([HMS_BloodInventory].[Availability], 0) [Availability]
	FROM [HMS_BloodGroup]
	LEFT JOIN [HMS_BloodInventory] 
		ON ([HMS_BloodInventory].[BloodGroup]= [HMS_BloodGroup].[BloodGroup]
		AND [HMS_BloodInventory].[CompanyId] = @CompanyId)
    
	   
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_BedCategoryById]
@BedCategoryId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[BedCategoryId],
				[BedCategory],
				[Description],
				[IsActive]
	    FROM	[dbo].[HMS_BedCategory]
	    WHERE	[HMS_BedCategory].[BedCategoryId] = @BedCategoryId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_BedById]
@BedId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[BedId],
				[BedName],
				[Description],
				[BedCategoryId],
				[IsActive],
				[IsAvailable],
				'' AS [BedCategory]
	    FROM	[dbo].[HMS_Bed]
	    WHERE	[HMS_Bed].[BedId] = @BedId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_BedAllotmentById]
@BedAllotmentId  		BIGINT      =	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[BedAllotmentId],
				[PatientId],
				[BedId],
				[BedCategoryId],
				[AllotmentDate],
				[DischargeDate],
				[Notes],
				'' AS [BedName],
				'' AS [PatientName]
	    FROM	[dbo].[HMS_BedAllotment]
	    WHERE	[HMS_BedAllotment].[BedAllotmentId] = @BedAllotmentId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spS_AvailableBeds]
@BedCategoryId  	BIGINT      =	NULL,
@CompanyId  		BIGINT      =	NULL,
@BedId				BIGINT		=	NULL
AS
BEGIN
		SET NOCOUNT ON;
        
       SELECT	[BedId],
				[BedName],
				[Description],
				[BedCategoryId],
				[IsActive],
				[IsAvailable],
				'' AS [BedCategory]
	    FROM	[dbo].[HMS_Bed]
	    WHERE	[HMS_Bed].[IsDeleted] = 0
	    AND		[HMS_Bed].[IsActive] = 1
	    AND		[HMS_Bed].[BedCategoryId] = @BedCategoryId
	    AND		[HMS_Bed].[BedId] NOT IN
				(
					SELECT [BedId] FROM [HMS_BedAllotment]
					WHERE [AllotmentDate] < GETDATE () 
					AND ([DischargeDate]  IS NULL OR [DischargeDate] > GETDATE())
					AND [HMS_Bed].[BedId] <> @BedId
				)
		
		
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_User]
@FirstName		VARCHAR(50)		= NULL,
@MiddleName		VARCHAR(50)		= NULL,
@LastName		VARCHAR(50)		= NULL,
@Email			VARCHAR(100)	= NULL,
@Password		VARCHAR(50)		= NULL,
@Phone			VARCHAR(20)		= NULL,
@RoleId			BIGINT			= NULL,
@IsActive		BIT				= NULL,
@CreatedBy		BIGINT			= NULL,
@CompanyId		BIGINT			= NULL,
@ProfilePic		NVARCHAR(200)	= NULL
AS
BEGIN	
	
		IF NOT EXISTS(
						SELECT	[UserId] 
						FROM	[HMS_User] 
						WHERE	[Email] = @Email AND [IsDeleted] = 0 AND [CompanyId] <> @CompanyId
					  )
		BEGIN
			INSERT INTO [dbo].[HMS_User]
			(
				[FirstName],
				[MiddleName],
				[LastName],
				[Email],
				[Password],
				[Phone],
				[RoleId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[ModifiedDate],
				[CompanyId],
				[CreatedBy],
				[ProfilePic]
			)
			VALUES
			(
				@FirstName,
				@MiddleName,
				@LastName,
				@Email,
				@Password,
				@Phone,
				@RoleId,
				@IsActive,
				0,
				GETDATE(),
				GETDATE(),
				@CompanyId,
				@CreatedBy,
				@ProfilePic
			)
			
			SELECT @@IDENTITY AS RETURNVAL,  '' AS [DuplicateColumn]
		END
		ELSE
		BEGIN
				SELECT -1 AS RETURNVAL,  'Email' AS [DuplicateColumn] -- Email already exists with other user.
		END

	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_Role]
@RoleName		VARCHAR(50)     	=	NULL
AS
BEGIN
	INSERT INTO [dbo].[HMS_Role]
	(
		[RoleName]
	)
	VALUES
	(
		@RoleName
	)
		
	SELECT @@IDENTITY AS [IdentityColumn], '' AS [DuplicateColumn]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_Report]
@ReportTypeId	BIGINT     	=	NULL,
@Description		NVARCHAR(2000) = NULL,
@ReportDate			DATETIME = NULL,
@PatientId		BIGINT = NULL,
@ReportFile		NVARCHAR(100) = NULL,
@CompanyId			BIGINT		=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[ReportId] 
					FROM	[dbo].[HMS_Report] 
					WHERE	[PatientId] = @PatientId AND [ReportTypeId] = 2
			 )
		BEGIN

			INSERT INTO [dbo].[HMS_Report]
			(
				[ReportTypeId],
				[Description],
				[ReportDate],
				[PatientId],
				[CompanyId],
				[ReportFile],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@ReportTypeId,
				@Description,
				@ReportDate,
				@PatientId,
				@CompanyId,
				@ReportFile,
				0,
				GETDATE(),
				@CreatedBy
			)
			
			SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'Report' AS DuplicateColumn
	END
			
			
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_Prescription]
@PatientCheckupId	BIGINT		=	NULL,
@PatientId   		BIGINT     	=	NULL,
@TreatmentId   		BIGINT     	=	NULL,
@CaseHistory  		NVARCHAR(MAX)     	=	NULL,
@ExtraNotes    		NVARCHAR(MAX)     	=	NULL
AS
BEGIN


			 
			INSERT INTO [dbo].[HMS_Prescription]
			(
				[PatientCheckupId],
				[PatientId],
				[TreatmentId],
				[CaseHistory],
				[ExtraNotes],
				[PrescriptionDate]
			)
			VALUES
			(
				@PatientCheckupId,
				@PatientId,
				@TreatmentId,
				@CaseHistory,
				@ExtraNotes,
				GETDATE()
			)
				
		SELECT @@IDENTITY AS RETURNVAL
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_PaymentCategory]
@PaymentCategory   	NVARCHAR(200)     	=	NULL,
@Description   		NVARCHAR(500)     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[PaymentCategoryId] 
					FROM	[dbo].[HMS_PaymentCategory] 
					WHERE	[PaymentCategory] = @PaymentCategory AND [IsDeleted] = 0
					AND		[CompanyId] = @CompanyId
			 )
		BEGIN
			 
			INSERT INTO [dbo].[HMS_PaymentCategory]
			(
				[PaymentCategory],
				[Description],
				[CompanyId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@PaymentCategory,
				@Description,
				@CompanyId,
				@IsActive,
				0,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'PaymentCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_Payment]
@PatientId			BIGINT     		=	NULL,
@InvoiceNumber		NVARCHAR(10)	=	NULL,
@SubTotal			MONEY     		=	NULL,
@Discount			MONEY     		=	NULL,
@Tax				MONEY     		=	NULL,
@GrandTotal			MONEY     		=	NULL,
@PaymentStatus		INT				=	NULL,
@CompanyId			BIGINT			=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN

			INSERT INTO [dbo].[HMS_Payment]
			(
				[PatientId],
				[InvoiceNumber],
				[PaymentDate],
				[SubTotal],
				[Discount],
				[Tax],
				[GrandTotal],
				[PaymentStatus],
				[CompanyId],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@PatientId,
				@InvoiceNumber,
				GETDATE(),
				@SubTotal,
				@Discount,
				@Tax,
				@GrandTotal,
				@PaymentStatus,
				@CompanyId,
				0,
				GETDATE(),
				@CreatedBy
			)
				
			DECLARE @PaymentId BIGINT
			SET @PaymentId = @@IDENTITY	
			
			UPDATE [HMS_Payment] SET [InvoiceNumber] = 'INV-' + CONVERT(VARCHAR(10),@PaymentId) WHERE [PaymentId] = @PaymentId
				
		SELECT @PaymentId AS RETURNVAL, '' AS DuplicateColumn
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_PatientTest]
@PatientId			BIGINT     		=	NULL,
@ConsultantId		BIGINT     		=	NULL,
@TestDate			DATETIME    	=	NULL,
@DeliveryDate		DATETIME    	=	NULL,
@ReportStatus		INT				=	NULL,
@CompanyId			BIGINT			=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN

			INSERT INTO [dbo].[HMS_PatientTest]
			(
				[PatientId],
				[ConsultantId],
				[TestDate],
				[DeliveryDate],
				[ReportStatus],
				[CompanyId],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@PatientId,
				@ConsultantId,
				@TestDate,
				@DeliveryDate,
				@ReportStatus,
				@CompanyId,
				0,
				GETDATE(),
				@CreatedBy
			)
				
			SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_PatientCheckup]
@PatientId   		BIGINT     	=	NULL,
@DoctorId   		BIGINT     	=	NULL,
@Symptoms  		NVARCHAR(MAX)     	=	NULL,
@Diagnosis    		NVARCHAR(MAX)     	=	NULL,
@CheckupDate    		DATETIME     	=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN


			 
			INSERT INTO [dbo].[HMS_PatientCheckup]
			(
				[PatientId],
				[DoctorId],
				[Symptoms],
				[Diagnosis],
				[CheckupDate],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@PatientId,
				@DoctorId,
				@Symptoms,
				@Diagnosis,
				@CheckupDate,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_Patient]
@FirstName		VARCHAR(50)		= NULL,
@MiddleName		VARCHAR(50)		= NULL,
@LastName		VARCHAR(50)		= NULL,
@Email			VARCHAR(100)	= NULL,
@Password		VARCHAR(50)		= NULL,
@Phone			VARCHAR(20)		= NULL,
@RoleId			BIGINT			= NULL,
@IsActive		BIT				= NULL,
@CreatedBy		BIGINT			= NULL,
@CompanyId		BIGINT			= NULL,
@BirthDate		DATETIME		= NULL,
@Gender			VARCHAR(10)		= NULL,
@BloodGroup		NVARCHAR(10)	= NULL,
@Address1		NVARCHAR(500)	= NULL,
@ProfilePic		NVARCHAR(200)	= NULL
AS
BEGIN	

	DECLARE @UserId BIGINT
	
		IF NOT EXISTS(
						SELECT	[UserId] 
						FROM	[HMS_User] 
						WHERE	[Email] = @Email AND [IsDeleted] = 0 AND [CompanyId] = @CompanyId
					  )
		BEGIN
			INSERT INTO [dbo].[HMS_User]
			(
				[FirstName],
				[MiddleName],
				[LastName],
				[Email],
				[Password],
				[Phone],
				[RoleId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[ModifiedDate],
				[CompanyId],				
				[CreatedBy],
				[Address1],
				[ProfilePic]
			)
			VALUES
			(
				@FirstName,
				@MiddleName,
				@LastName,
				@Email,
				@Password,
				@Phone,
				@RoleId,
				@IsActive,
				0,
				GETDATE(),
				GETDATE(),
				@CompanyId,
				@CreatedBy,
				@Address1,
				@ProfilePic
			)
			
			SET @UserId = @@IDENTITY 
			
			INSERT INTO [HMS_PatientDetail]
			(
				[UserId],
				[BirthDate],
				[Gender],
				[BloodGroup]
			)
			VALUES
			(
				@UserId,
				@BirthDate,
				@Gender,
				@BloodGroup
			)
			
			DECLARE @PatientId BIGINT
			SET @PatientId = @@IDENTITY
			
			DECLARE @PatientCode VARCHAR(10)
			SET @PatientCode = 'P' + RIGHT('00000000'+CAST(@PatientId AS VARCHAR(9)),9)
			
			UPDATE [HMS_PatientDetail] SET [PatientCode] = @PatientCode WHERE [UserId] = @UserId
			
			
			
			SELECT @UserId AS RETURNVAL,  '' AS [DuplicateColumn]
		END
		ELSE
		BEGIN
				SELECT -1 AS RETURNVAL,  'Email' AS [DuplicateColumn] -- Email already exists with other user.
		END

	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_MedicineCategory]
@MedicineCategory   	NVARCHAR(500)     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy		BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[MedicineCategoryId] 
					FROM	[dbo].[HMS_MedicineCategory] 
					WHERE	[MedicineCategory] = @MedicineCategory AND [IsDeleted] = 0
					AND		[CompanyId] = @CompanyId
			 )
		BEGIN
			 
			INSERT INTO [dbo].[HMS_MedicineCategory]
			(
				[MedicineCategory],
				[CompanyId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@MedicineCategory,
				@CompanyId,
				@IsActive,
				0,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'MedicineCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_Medicine]
@MedicineName   	NVARCHAR(500)     	=	NULL,
@Description  		NVARCHAR(1000)     	=	NULL,
@MedicineCategoryId BIGINT     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy		BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[MedicineId] 
					FROM	[dbo].[HMS_Medicine] 
					WHERE	[MedicineName] = @MedicineName AND [IsDeleted] = 0
			 )
		BEGIN
			 
			INSERT INTO [dbo].[HMS_Medicine]
			(
				[MedicineName],
				[Description],
				[MedicineCategoryId],
				[CompanyId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@MedicineName,
				@Description,
				@MedicineCategoryId,
				@CompanyId,
				@IsActive,
				0,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'Medicine' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_LoginHistory]
@UserId		BIGINT			= NULL,
@Action		TINYINT			= NULL,
@IpAddress	VARCHAR(20)		= NULL
AS
BEGIN	
	
	IF (@UserId IS NOT NULL AND @Action IS NOT NULL)
	BEGIN
		INSERT INTO [dbo].[HMS_LoginHistory]
		(
			[UserId],
			[Action],
			[IpAddress],
			[ActionDate]
		)
		VALUES
		(
			@UserId,
			@Action,
			@IpAddress,
			GETDATE()
		)
		
		SELECT @@IDENTITY AS RETURNVAL
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL -- User ID or Action is Null.
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_LabTestCategory]
@LabTestCategoryName   	NVARCHAR(200)     	=	NULL,
@Description   		NVARCHAR(500)     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[LabTestCategoryId] 
					FROM	[dbo].[HMS_LabTestCategory] 
					WHERE	[LabTestCategoryName] = @LabTestCategoryName AND [IsDeleted] = 0
					AND		[CompanyId] = @CompanyId
			 )
		BEGIN
			 
			INSERT INTO [dbo].[HMS_LabTestCategory]
			(
				[LabTestCategoryName],
				[Description],
				[CompanyId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@LabTestCategoryName,
				@Description,
				@CompanyId,
				@IsActive,
				0,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'LabTestCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_LabTest]
@LabTestCategoryId	BIGINT     	=	NULL,
@LabTestName		NVARCHAR(100) = NULL,
@TestUnit			NVARCHAR(10) = NULL,
@ReferenceRange		NVARCHAR(50) = NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive			BIT			=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[LabTestId] 
					FROM	[dbo].[HMS_LabTest] 
					WHERE	[LabTestName] = @LabTestName AND [IsDeleted] = 0
					AND [LabTestCategoryId] = @LabTestCategoryId
					AND		[CompanyId] = @CompanyId
			 )
		BEGIN
			 
			INSERT INTO [dbo].[HMS_LabTest]
			(
				[LabTestCategoryId],
				[LabTestName],
				[TestUnit],
				[ReferenceRange],
				[CompanyId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@LabTestCategoryId,
				@LabTestName,
				@TestUnit,
				@ReferenceRange,
				@CompanyId,
				@IsActive,
				0,
				GETDATE(),
				@CreatedBy
			)
			
				SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
			
			END
			ELSE 
				BEGIN
				SELECT -1 AS RETURNVAL, 'LabTest' AS DuplicateColumn
			END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_ExpenseCategory]
@ExpenseCategory   	NVARCHAR(200)     	=	NULL,
@Description   		NVARCHAR(500)     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[ExpenseCategoryId] 
					FROM	[dbo].[HMS_ExpenseCategory] 
					WHERE	[ExpenseCategory] = @ExpenseCategory AND [IsDeleted] = 0
					AND		[CompanyId] = @CompanyId
			 )
		BEGIN
			 
			INSERT INTO [dbo].[HMS_ExpenseCategory]
			(
				[ExpenseCategory],
				[Description],
				[CompanyId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@ExpenseCategory,
				@Description,
				@CompanyId,
				@IsActive,
				0,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'ExpenseCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_Expense]
@ExpenseCategoryId	BIGINT     	=	NULL,
@ExpenseAmount		MONEY     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN


			 
			INSERT INTO [dbo].[HMS_Expense]
			(
				[ExpenseCategoryId],
				[ExpenseAmount],
				[ExpenseDate],
				[CompanyId],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@ExpenseCategoryId,
				@ExpenseAmount,
				GETDATE(),
				@CompanyId,
				0,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_EmailTemplate]
@Code			VARCHAR(50)		= NULL,
@Title			VARCHAR(100)	= NULL,
@Subject		VARCHAR(100)	= NULL,
@FromEmail		VARCHAR(100)	= NULL,
@Message		NVARCHAR(MAX)	= NULL,
@Signature		NVARCHAR(MAX)	= NULL,
@IsActive		BIT				= NULL
AS
BEGIN	
	
	IF NOT EXISTS(
					SELECT	[EmailTemplateId] 
					FROM	[dbo].[HMS_EmailTemplate] 
					WHERE	[HMS_EmailTemplate].[Code] = @Code AND [IsDeleted] = 0
				)
	BEGIN
		INSERT INTO [dbo].[HMS_EmailTemplate]
		(
			[Code],
			[Title],
			[Subject],
			[FromEmail],
			[Message],
			[Signature],
			[IsActive],
			[IsDeleted],
			[CreatedDate],
			[ModifiedDate]				
		)
		VALUES
		(
			@Code,
			@Title,
			@Subject,
			@FromEmail,
			@Message,
			@Signature,
			@IsActive,
			0,
			GETDATE(),
			GETDATE()
		)
		
		SELECT @@IDENTITY AS RETURNVAL,'' AS DuplicateColumn
	END
	ELSE
	BEGIN
		SELECT -1 AS RETURNVAL,'Code' AS DuplicateColumn  -- Code already exists.
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_Company]
@CompanyName   		NVARCHAR(500)     	=	NULL,
@Address1  		NVARCHAR(250)     	=	NULL,
@Address2    		NVARCHAR(250)     	=	NULL,
@City    		NVARCHAR(50)     	=	NULL,
@State    		NVARCHAR(50)     	=	NULL,
@ZipCode    		NVARCHAR(20)     	=	NULL,
@Email       		VARCHAR(255)    	=	NULL,
@Phone       		NVARCHAR(20)     	=	NULL,
@Fax       		NVARCHAR(20)     	=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy		BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[CompanyId] 
					FROM	[dbo].[HMS_Company] 
					WHERE	[CompanyName] = @CompanyName AND [IsDeleted] = 0
			 )
		BEGIN
			 
			INSERT INTO [dbo].[HMS_Company]
			(
				[CompanyName],
				[Address1],
				[Address2],
				[City],
				[State],
				[ZipCode],
				[Email],
				[Phone],
				[Fax],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@CompanyName,
				@Address1,
				@Address2,
				@City,
				@State,
				@ZipCode,
				@Email,
				@Phone,
				@Fax,
				@IsActive,
				0,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'Hospital' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_BedCategory]
@BedCategory   	NVARCHAR(200)     	=	NULL,
@Description   		NVARCHAR(500)     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy			BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[BedCategoryId] 
					FROM	[dbo].[HMS_BedCategory] 
					WHERE	[BedCategory] = @BedCategory AND [IsDeleted] = 0
					AND		[CompanyId] = @CompanyId
			 )
		BEGIN
			 
			INSERT INTO [dbo].[HMS_BedCategory]
			(
				[BedCategory],
				[Description],
				[CompanyId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@BedCategory,
				@Description,
				@CompanyId,
				@IsActive,
				0,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'BedCategory' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_BedAllotment]
@PatientId				BIGINT     	=	NULL,
@BedCategoryId			BIGINT     	=	NULL,
@BedId					BIGINT     	=	NULL,
@AllotmentDate			DATETIME     	=	NULL,
@DischargeDate			DATETIME     	=	NULL,
@Notes  				NVARCHAR(500)     	=	NULL,
@CompanyId				BIGINT		=	NULL,
@CreatedBy				BIGINT			= NULL
AS
BEGIN
 
			INSERT INTO [dbo].[HMS_BedAllotment]
			(
				[PatientId],
				[BedId],
				[BedCategoryId],
				[AllotmentDate],
				[DischargeDate],
				[Notes],
				[CompanyId],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@PatientId,
				@BedId,
				@BedCategoryId,
				@AllotmentDate,
				@DischargeDate,
				@Notes,
				@CompanyId,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spI_Bed]
@BedName   	NVARCHAR(500)     	=	NULL,
@Description  		NVARCHAR(1000)     	=	NULL,
@BedCategoryId BIGINT     	=	NULL,
@CompanyId			BIGINT		=	NULL,
@IsActive    		BIT             	=	NULL,
@CreatedBy		BIGINT			= NULL
AS
BEGIN

IF NOT EXISTS(
			SELECT	[BedId] 
					FROM	[dbo].[HMS_Bed] 
					WHERE	[BedName] = @BedName AND [IsDeleted] = 0
			 )
		BEGIN
			 
			INSERT INTO [dbo].[HMS_Bed]
			(
				[BedName],
				[Description],
				[BedCategoryId],
				[CompanyId],
				[IsActive],
				[IsDeleted],
				[CreatedDate],
				[CreatedBy]
			)
			VALUES
			(
				@BedName,
				@Description,
				@BedCategoryId,
				@CompanyId,
				@IsActive,
				0,
				GETDATE(),
				@CreatedBy
			)
				
		SELECT @@IDENTITY AS RETURNVAL, '' AS DuplicateColumn
	END
	ELSE 
	BEGIN
		SELECT -1 AS RETURNVAL, 'Bed' AS DuplicateColumn
	END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[HMS_spD_PatientCheckup]
@PatientCheckupId		BIGINT	= NULL
AS
BEGIN

	
	DELETE PD
	FROM [HMS_PrescriptionDetail] PD
	INNER JOIN [HMS_Prescription] P ON [PD].[PrescriptionId] = [P].[PrescriptionId]
	WHERE P.[PatientCheckupId] = @PatientCheckupId
	
	DELETE FROM [HMS_Prescription]
	WHERE [PatientCheckupId] = @PatientCheckupId
	
	DELETE FROM [HMS_PatientCheckup] 
	WHERE [PatientCheckupId] = @PatientCheckupId
	
	
	SELECT 1 AS RETURNVAL
	
END
GO
