SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_Report]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HMS_Report](
	[ReportId] [bigint] IDENTITY(1,1) NOT NULL,
	[ReportTypeId] [bigint] NULL,
	[PatientId] [bigint] NULL,
	[CompanyId] [bigint] NULL,
	[Description] [nvarchar](2000) NULL,
	[ReportDate] [datetime] NULL,
	[ReportFile] [nvarchar](100) NULL,
	[IsDeleted] [bit] NULL,
	[CreatedDate] [datetime] NULL,
	[ModifiedDate] [datetime] NULL,
	[CreatedBy] [bigint] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_HMS_Report] PRIMARY KEY CLUSTERED 
(
	[ReportId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_BloodInventory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HMS_BloodInventory](
	[BloodInventoryId] [bigint] IDENTITY(1,1) NOT NULL,
	[CompanyId] [bigint] NULL,
	[BloodGroup] [nvarchar](10) NULL,
	[Availability] [nvarchar](10) NULL,
	[ModifiedDate] [datetime] NULL,
	[ModifiedBy] [bigint] NULL,
 CONSTRAINT [PK_HMS_BloodInventory] PRIMARY KEY CLUSTERED 
(
	[BloodInventoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_BloodGroup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HMS_BloodGroup](
	[BloodGroup] [nvarchar](10) NULL
) ON [PRIMARY]
END
GO



--- Script



/****** Object:  StoredProcedure [dbo].[HMS_spS_Patient]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spS_Patient]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spS_Patient]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spS_PatientById]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spS_PatientById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spS_PatientById]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spI_Patient]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spI_Patient]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spI_Patient]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spS_CompanyByBloodGroup]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spS_CompanyByBloodGroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spS_CompanyByBloodGroup]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spM_Report]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spM_Report]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spM_Report]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spU_Report]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spU_Report]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spU_Report]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spI_Report]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spI_Report]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spI_Report]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spS_Report]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spS_Report]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spS_Report]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spS_ReportById]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spS_ReportById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spS_ReportById]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spS_BloodInventory]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spS_BloodInventory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spS_BloodInventory]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spU_BloodInventory]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spU_BloodInventory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spU_BloodInventory]
GO

/****** Object:  StoredProcedure [dbo].[HMS_spS_User]    Script Date: 06/02/2017 01:07:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HMS_spS_User]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HMS_spS_User]
GO



/****** Object:  StoredProcedure [dbo].[HMS_spS_Patient]    Script Date: 06/02/2017 01:07:58 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spS_PatientById]    Script Date: 06/02/2017 01:07:58 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spI_Patient]    Script Date: 06/02/2017 01:07:58 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spS_CompanyByBloodGroup]    Script Date: 06/02/2017 01:07:59 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spM_Report]    Script Date: 06/02/2017 01:07:59 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spU_Report]    Script Date: 06/02/2017 01:07:59 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spI_Report]    Script Date: 06/02/2017 01:07:59 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spS_Report]    Script Date: 06/02/2017 01:07:59 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spS_ReportById]    Script Date: 06/02/2017 01:07:59 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spS_BloodInventory]    Script Date: 06/02/2017 01:07:59 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spU_BloodInventory]    Script Date: 06/02/2017 01:07:59 ******/
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

/****** Object:  StoredProcedure [dbo].[HMS_spS_User]    Script Date: 06/02/2017 01:07:59 ******/
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


-- Default Data 


INSERT [dbo].[HMS_SiteMenuLTE] ([MenuId], [ParentMenuId], [MenuCode], [MenuName], [MenuPageName], [MenuImageName], [MenuOrderNo], [IsActive], [IsDeleted]) VALUES (1301, 0, N'REPORT_MGMT', N'Reports', N'', N'fa-heartbeat', 10, 1, 0)
INSERT [dbo].[HMS_SiteMenuLTE] ([MenuId], [ParentMenuId], [MenuCode], [MenuName], [MenuPageName], [MenuImageName], [MenuOrderNo], [IsActive], [IsDeleted]) VALUES (1302, 1301, N'BIRTH_REPORT', N'Birth Report', N'Birth', N'fa-wpforms', 1, 1, 0)
INSERT [dbo].[HMS_SiteMenuLTE] ([MenuId], [ParentMenuId], [MenuCode], [MenuName], [MenuPageName], [MenuImageName], [MenuOrderNo], [IsActive], [IsDeleted]) VALUES (1303, 1301, N'DEATH_REPORT', N'Death Report', N'Death', N'fa-wpforms', 2, 1, 0)
INSERT [dbo].[HMS_SiteMenuLTE] ([MenuId], [ParentMenuId], [MenuCode], [MenuName], [MenuPageName], [MenuImageName], [MenuOrderNo], [IsActive], [IsDeleted]) VALUES (1401, 0, N'BLOOD_BANK', N'Blood Bank', N'', N'fa-tint', 11, 1, 0)
INSERT [dbo].[HMS_SiteMenuLTE] ([MenuId], [ParentMenuId], [MenuCode], [MenuName], [MenuPageName], [MenuImageName], [MenuOrderNo], [IsActive], [IsDeleted]) VALUES (1402, 1401, N'PRIVATE_BLOOD_BANK', N'Private Blood Bank', N'PrivateBloodBank', N'fa-circle-o', 1, 1, 0)
INSERT [dbo].[HMS_SiteMenuLTE] ([MenuId], [ParentMenuId], [MenuCode], [MenuName], [MenuPageName], [MenuImageName], [MenuOrderNo], [IsActive], [IsDeleted]) VALUES (1403, 1401, N'PUBLIC_BLOOD_BANK', N'Public Bood Bank', N'PublicBloodBank', N'fa-circle-o', 2, 1, 0)

SET IDENTITY_INSERT [dbo].[HMS_RoleSiteMenu] ON
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (86, 2, 1301, N'REPORT_MGMT')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (87, 3, 1301, N'REPORT_MGMT')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (88, 4, 1301, N'REPORT_MGMT')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (89, 2, 1302, N'BIRTH_REPORT')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (90, 3, 1302, N'BIRTH_REPORT')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (91, 4, 1302, N'BIRTH_REPORT')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (92, 2, 1303, N'DEATH_REPORT')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (93, 3, 1303, N'DEATH_REPORT')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (94, 4, 1303, N'DEATH_REPORT')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (95, 2, 1401, N'BLOOD_BANK')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (96, 6, 1401, N'BLOOD_BANK')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (97, 2, 1402, N'PRIVATE_BLOOD_BANK')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (98, 6, 1402, N'PRIVATE_BLOOD_BANK')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (99, 2, 1403, N'PUBLIC_BLOOD_BANK')
INSERT [dbo].[HMS_RoleSiteMenu] ([RoleSiteMenuId], [RoleId], [MenuId], [MenuCode]) VALUES (100, 6, 1403, N'PUBLIC_BLOOD_BANK'
SET IDENTITY_INSERT [dbo].[HMS_RoleSiteMenu] OFF


INSERT [dbo].[HMS_BloodGroup] ([BloodGroup]) VALUES (N'A+')
INSERT [dbo].[HMS_BloodGroup] ([BloodGroup]) VALUES (N'A-')
INSERT [dbo].[HMS_BloodGroup] ([BloodGroup]) VALUES (N'AB+')
INSERT [dbo].[HMS_BloodGroup] ([BloodGroup]) VALUES (N'AB-')
INSERT [dbo].[HMS_BloodGroup] ([BloodGroup]) VALUES (N'B+')
INSERT [dbo].[HMS_BloodGroup] ([BloodGroup]) VALUES (N'B-')
INSERT [dbo].[HMS_BloodGroup] ([BloodGroup]) VALUES (N'O+')
INSERT [dbo].[HMS_BloodGroup] ([BloodGroup]) VALUES (N'O-')
