USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[usp_DB_TblRowCOUNT]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[usp_DB_TblRowCOUNT]
AS 

/******************************************************************************
**		File: usp_DB_TblRowCOUNT.sql
**		Name: usp_DB_TblRowCOUNT
**		Desc: This procedure counts the number of rows in each user table for current database.
**
**		Return values:
**
**		Called by: SSIS for row count
**
**		Parameters:
**		Input							Output
**		----------					-----------
**
**		Auth:swarns
**		Date: 06/24/2010
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:			Description:
**		----------	-------------	----------------------------------------------
**		07/02/2010      swarns          Added DBSize functionality
**		
**********************************************************************************/


BEGIN
set nocount on
                --Declare temp table
declare @temp table
(
Report_Date varchar(100)
,Table_Name varchar(100)
,Num_Rows varchar(100)
)

--populate header
insert into @temp
select 
QUOTENAME('REPORT_DATE','"'), QUOTENAME('TABLE_NAME','"'), QUOTENAME('NUM_ROWS','"')

--Populate DB size
insert into @temp
SELECT 
QUOTENAME(UPPER(REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-')),'"'),
quotename('DBSIZE','"'),
      cast(sum(cast(Fileproperty(name,'SpaceUsed') as numeric (18,2))*8/1024) as numeric(18,2)) AS spaceused
FROM   sysfiles

--populate table rows
insert into @temp
SELECT 
                                QUOTENAME(UPPER(REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-')),'"') as [DateChecked]
                                , QUOTENAME(so.[name],'"') as [TableName]
                                , QUOTENAME(rows,'"') as RecordCount
    FROM 
                                sysindexes si
        INNER JOIN sysobjects so ON si.id = so.id
    WHERE 
                                si.indid < 2 
        AND so.type = 'U' 
        AND so.[name] != 'dtproperties'
        AND so.[name] not like 'sys%'
    ORDER BY so.[name]    


select * from @temp
END
GO
/****** Object:  StoredProcedure [dbo].[GetSearchUserList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSearchUserList] 
	-- Add the parameters for the stored procedure here
	@sFirstName varchar(100) = '', @sLastName varchar(100) = '', @sEmailId varchar(100) = '', 
	@sAccoutNumber varchar(100) ='', @iUserTypeId int, @iAccountTypeId int, @iLoginUserType int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	
	DECLARE @SQL NVARCHAR(MAX)
	-- ALTER a temporary table to store the desired results of user on the basis of parameter
	CREATE TABLE #UserResult
	(
		Id INT,
		FirstName VARCHAR(100),
		LastName VARCHAR(100),
		LoginId VARCHAR(50),
		EmailId VARCHAR(50),
		UserTypeTitle VARCHAR(50),
		Password VARCHAR(50),
		CreationDate DATETIME,
		ModifiedDate DATETIME,
		AccountNumber VARCHAR(50) DEFAULT '',
		AccountTypeTitle VARCHAR(50) DEFAULT '',
		EditionType VARCHAR(50) DEFAULT '',
		UserStatus VARCHAR(8),
		UserTypeId INT,
		EditionTypeId INT DEFAULT ''
	)
	/*SET @sFirstName = REPLACE(@sFirstName,' ',' OR ')
	SET @sLastName = REPLACE(@sLastName,' ',' OR ')*/
	SET @SQL = ''
	IF LEN(@sAccoutNumber) > 0 OR @iAccountTypeId > 0
	BEGIN
		-- fetch account number, state, zip, country of the license to which the user is belonged		
	
		SET @SQL = 'INSERT INTO #UserResult (Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,
			ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId, EditionTypeId)
			SELECT  AIAUser.Id, ISNULL(AIAUser.FirstName,''''), ISNULL(AIAUser.LastName,''''), AIAUser.LoginId, ISNULL(AIAUser.EmailId,'''') as EmailId,
			UserType.Title as UserTypeTitle, AIAUser.Password, AIAUser.CreationDate, ISNULL(AIAUser.ModifiedDate,'''') as ModifiedDate, 
			ISNULL(License.AccountNumber,'''') as AccountNumber, ISNULL(AccountType.Title,'''') as AccountTypeTitle,
			ISNULL(Edition.Title,'''') as EditionType, 
			(CASE AIAUser.IsActive WHEN 1 THEN ''Active'' ELSE ''Inactive'' END) as UserStatus, 
			UserType.Id as UserTypeId, ISNULL(Edition.Id,'''') as EditionTypeId
			FROM AIAUser 
			INNER JOIN UserType ON UserType.Id = AIAUser.UserTypeId
			INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
			INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
			INNER JOIN License ON LicenseToEdition.LicenseId = License.Id
			INNER JOIN AccountType ON AccountType.Id = License.AccountTypeId
			INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId
   			WHERE
			License.IsActive = 1
			AND UserType.Priority >' +CONVERT(VARCHAR(20),@iLoginUserType)

		IF LEN(@sAccoutNumber)>0
		BEGIN
			SET @SQL = @SQL + ' AND License.AccountNumber = '''+@sAccoutNumber+''''
		END
		IF @iAccountTypeId > 0
		BEGIN
			SET @SQL = @SQL + ' AND License.AccountTypeId = '''+CONVERT(VARCHAR(20),@iAccountTypeId)+''''
		END
		IF LEN(@sFirstName)>0
		BEGIN
			SET @SQL = @SQL + ' AND (AIAUser.FirstName LIKE ''%'+@sFirstName+'%'')' --CONTAINS(AIAUser.FirstName, '''+@sFirstName+''')'
		END
		IF LEN(@sLastName)>0
		BEGIN
			SET @SQL = @SQL + ' AND (AIAUser.LastName LIKE ''%'+@sLastName+'%'')'--CONTAINS(AIAUser.LastName, '''+@sLastName+''')'
		END
		IF LEN(@sEmailId)>0
		BEGIN
			SET @SQL = @SQL + ' AND AIAUser.EmailId = '''+@sEmailId+''''
		END
		IF @iUserTypeId>0
		BEGIN
			SET @SQL = @SQL + ' AND AIAUser.UserTypeId = '''+CONVERT(VARCHAR(20),@iUserTypeId)+''''
		END
		
		EXEC SP_EXECUTESQL @SQL
			
	END
	ELSE
	BEGIN

		SET @SQL = 'INSERT INTO #UserResult (Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,
			ModifiedDate, UserStatus, UserTypeId) 
			SELECT AIAUser.Id, ISNULL(AIAUser.FirstName,''''), ISNULL(AIAUser.LastName,''''), 
			AIAUser.LoginId, ISNULL(AIAUser.EmailId,''''), UserType.Title, AIAUser.Password, AIAUser.CreationDate, 
			ISNULL(AIAUser.ModifiedDate,''''), (CASE AIAUser.IsActive WHEN 1 THEN ''Active'' ELSE ''Inactive'' END), 
			UserType.Id 
			FROM AIAUser 
			INNER JOIN UserType ON UserType.Id = AIAUser.UserTypeId
			WHERE UserType.Title in (''General Admin'')'
		
		IF LEN(@sFirstName)>0
		BEGIN
			SET @SQL = @SQL + ' AND (AIAUser.FirstName LIKE ''%'+@sFirstName+'%'')'--CONTAINS(AIAUser.FirstName, '''+@sFirstName+''')'
		END
		IF LEN(@sLastName)>0
		BEGIN
			SET @SQL = @SQL + ' AND (AIAUser.LastName LIKE ''%'+@sLastName+'%'')'--CONTAINS(AIAUser.LastName, '''+@sLastName+''')'
		END
		IF LEN(@sEmailId)>0
		BEGIN
			SET @SQL = @SQL + ' AND AIAUser.EmailId = '''+@sEmailId+''''
		END
		IF @iUserTypeId>0
		BEGIN
			SET @SQL = @SQL + ' AND AIAUser.UserTypeId = '''+CONVERT(VARCHAR(20),@iUserTypeId)+''''
		END
		
		EXEC SP_EXECUTESQL @SQL
		
		-- fetch account number, state, zip, country of the license to which the user is belonged
		SET @SQL = 'INSERT INTO #UserResult (Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,
			ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId, EditionTypeId)	
			SELECT  AIAUser.Id, ISNULL(AIAUser.FirstName,''''), ISNULL(AIAUser.LastName,''''), AIAUser.LoginId, ISNULL(AIAUser.EmailId,''''), 
			UserType.Title,	AIAUser.Password, AIAUser.CreationDate, ISNULL(AIAUser.ModifiedDate,''''), 
			License.AccountNumber, AccountType.Title, Edition.Title, 
			(CASE AIAUser.IsActive WHEN 1 THEN ''Active'' ELSE ''Inactive'' END), UserType.Id, Edition.Id
			FROM AIAUser 
			INNER JOIN UserType ON UserType.Id = AIAUser.UserTypeId
			INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
			INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
			INNER JOIN License ON LicenseToEdition.LicenseId = License.Id
			INNER JOIN AccountType ON AccountType.Id = License.AccountTypeId
			INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId
       		WHERE 
			UserType.Title NOT IN (''Super Admin'',''General Admin'')
			AND License.IsActive = 1'
			
		IF LEN(@sAccoutNumber)>0
		BEGIN
			SET @SQL = @SQL + ' AND License.AccountNumber = '''+@sAccoutNumber+''''
		END
		IF @iAccountTypeId > 0
		BEGIN
			SET @SQL = @SQL + ' AND License.AccountTypeId = '''+CONVERT(VARCHAR(20),@iAccountTypeId)+''''
		END
		IF LEN(@sFirstName)>0
		BEGIN
			SET @SQL = @SQL + ' AND (AIAUser.FirstName LIKE ''%'+@sFirstName+'%'')'--CONTAINS(AIAUser.FirstName, '''+@sFirstName+''')'
		END
		IF LEN(@sLastName)>0
		BEGIN
			SET @SQL = @SQL + ' AND (AIAUser.LastName LIKE ''%'+@sLastName+'%'')'--CONTAINS(AIAUser.LastName, '''+@sLastName+''')'
		END
		IF LEN(@sEmailId)>0
		BEGIN
			SET @SQL = @SQL + ' AND AIAUser.EmailId = '''+@sEmailId+''''
		END
		IF @iUserTypeId>0
		BEGIN
			SET @SQL = @SQL + ' AND AIAUser.UserTypeId = '''+CONVERT(VARCHAR(20),@iUserTypeId)+''''
		END

		EXEC SP_EXECUTESQL @SQL

	END
	-- Selecting the desired result from temporary table
	SELECT Id, FirstName, LastName, LoginId, EmailId, UserTypeTitle, Password, CreationDate,
		ModifiedDate, AccountNumber, AccountTypeTitle, EditionType, UserStatus, UserTypeId, EditionTypeId FROM #UserResult
	-- Dropping the temporary table
	DROP TABLE #UserResult	
END
GO
/****** Object:  StoredProcedure [dbo].[GetSchedulerAlertEmail]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSchedulerAlertEmail] @sSchedulerEmail VARCHAR(255) OUTPUT, @sDeploymentEnvironment VARCHAR(255) OUTPUT
AS
BEGIN
	DECLARE @FileName varchar(255)
	DECLARE @ExecCmd VARCHAR(255)
	DECLARE @y INT
	DECLARE @x INT
	DECLARE @FileContents VARCHAR(8000)
	DECLARE @idoc INT
	DECLARE @sSchedulerAlertEMailVariable VARCHAR(255) = 'SchedulerAlertEMail'
	DECLARE @sEnvironmentVariable VARCHAR(255) = 'DeploymentEnvironment'
	DECLARE @datapath VARCHAR(255)

	CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

	EXEC master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\A.D.A.M.','DBConnectionXMLLocation',@datapath OUTPUT
	
	SET @FileName = '"'+@datapath+'"'
	SET @ExecCmd = 'type ' + @FileName
	SET @FileContents = ''

	INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
	SELECT @y = COUNT(*) from #tempXML

	SET @x = 0
	WHILE @x <> @y
		BEGIN
			SET @x = @x + 1
			SELECT @FileContents = @FileContents + ThisLine FROM #tempXML WHERE PK
	 = @x
		END
	SET @FileContents = (SELECT REPLACE(@FileContents,'<?xml version="1.0" standalone="yes"?>','<?xml version="1.0" encoding="ISO8859-1"?>'))
	DROP TABLE #tempXML 

	EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents

	SET @sSchedulerEmail = (SELECT Value FROM OPENXML(@idoc,'/DBConnection/EnvironmentVariable',2)
	WITH (Name VARCHAR(MAX),
			Value VARCHAR(MAX) ) WHERE Name = @sSchedulerAlertEMailVariable)
			
	SET @sDeploymentEnvironment = (SELECT Value FROM OPENXML(@idoc,'/DBConnection/EnvironmentVariable',2)
	WITH (Name VARCHAR(MAX),
			Value VARCHAR(MAX) ) WHERE Name = @sEnvironmentVariable)			

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetSubscriptionEndDate]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 15/4/2009
-- Description:	Fetch Price of given subscriptionId
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetSubscriptionEndDate] 
	-- Add the parameters for the stored procedure here
@tiDuration tinyint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT  CONVERT(datetime, DATEADD(mm, @tiDuration, GETDATE()))  as SubscriptionEndDate

END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetPolygonForId]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get Polygon for given Polygon Id
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetPolygonForId] 
	-- Add the parameters for the stored procedure here
@polygon_id int , 
@zoom int 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT  dbv.DissectiblePolygonId
		    ,dbv.PolygonSequence
		    ,dbv.PointX
		    ,dbv.PointY
	FROM DissectiblePolygon as dp
	INNER JOIN DissectiblePolygonVertices as dbv
	ON dp.Id=dbv.DissectiblePolygonId
	WHERE  (zoom = @zoom) AND (id = @polygon_id)
    ORDER BY PolygonSequence

END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetGenderList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get  GetGenderList from Sex Table
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetGenderList] 
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT
	(
	SELECT [Sex] as gr
      ,[Description] as descp
	FROM [Sex] it
	FOR XML AUTO )
	as XMLString 


END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetBodyRegionViewList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get BodyRegionViewList
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetBodyRegionViewList] 
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT
	(SELECT  Id as id, BodyViewId as voId, RegionId as brId,Zorder as zr, Zoom as zm,  
        OffsetX as ox, OffsetY as oy, Width as wd, Height as ht,  
        ISNULL(IsMirrored, 'N') as ismr,  
        ISNULL(MirrorOffset, 0) as  mro,  
        IsPrimary as isp, CanvasBounds as cnb 
        FROM    DissectibleRegionView it 
        ORDER BY BodyViewId, Zorder, RegionId 
		FOR XML AUTO)
		as XMLString 

END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetBodyRegionView]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get BodyRegionViewList
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetBodyRegionView] 
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT  Id , BodyViewId , RegionId ,Zorder , Zoom ,  
        OffsetX , OffsetY , Width , Height ,  
        ISNULL(IsMirrored, 'N') AS IsMirrored ,  
        ISNULL(MirrorOffset, 0) AS  MirrorOffset ,  
        IsPrimary , CanvasBounds  
        FROM    DissectibleRegionView 
        ORDER BY BodyViewId, Zorder, RegionId 
		
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetBodyRegionList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get Body Region List
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetBodyRegionList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--BodyRegionList(Retreive Body Region  List from DissectibleRegion )  
	SELECT
	(SELECT [Id] as id  
			,[Name] as nm  
			FROM [DissectibleRegion] it
			For XML AUTO )
	as XMLString 
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetBodyRegion]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 4/4/2009
-- Description:	Get List of all body regions 
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetBodyRegion] 
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--BodyRegionList(Retreive Body Region  List from DissectibleRegion )  
	SELECT [Id]  
		   ,[Name] 
			FROM [DissectibleRegion] 
			
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetBitmask]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 5/13/2009
-- Description:	Get List of Bitmask  of given id
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetBitmask] 
	-- Add the parameters for the stored procedure here

@image_id int,
@zoom int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT  [Id]
		    ,[Zoom]
			,[FileLength]
			,[Bitmask]
			,[LastModified]
	FROM DissectibleBitmasks
	WHERE [Id] = @image_id  and [Zoom] = @zoom 
			
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetViewOrientationList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get  Body ViewOrientation from DissectibleBodyView
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetViewOrientationList] 
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT
	(
	SELECT [Id] as id  
       ,[Name] as nm  
       ,ISNULL([Subject],'') as sub  
		FROM [DissectibleBodyView] it
		FOR XML AUTO)
	as XMLString   



END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetViewOrientation]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get  Body ViewOrientation from DissectibleBodyView
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetViewOrientation] 
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT [Id]  
       ,[Name]  
       ,ISNULL([Subject],'') as [Subject] 
		FROM [DissectibleBodyView] 
END
GO
/****** Object:  StoredProcedure [dbo].[GetCAMSearch]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
-- Author:		Magic
-- ALTER date: 08-Mar-2011
-- Description:	Get the result based on the keyword search
-- =============================================================
ALTER PROCEDURE [dbo].[GetCAMSearch]
	-- Add the parameters for the stored procedure here
	@sSearchKeyword nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT COUNT(DISTINCT CAMWeightage.Keyword) AS hitcount, 
    SUM(CAMWeightage.Relevancy) AS score, 
    CAMMetadata.Title, CAMMetadata.ProjectTypeId, CAMMetadata.GenContentId, 
    CAMMetadata.SubContent
	FROM CAMMetadata
	INNER JOIN CAMWeightage 
	ON CAMMetadata.Id = CAMWeightage.MetadataId
	WHERE CAMMetadata.LexiconId = 1
	AND CAMWeightage.Keyword = @sSearchKeyword
	GROUP BY CAMWeightage.Keyword, CAMWeightage.Relevancy, CAMMetadata.Title, 
	CAMMetadata.ProjectTypeId, CAMMetadata.GenContentId,CAMMetadata.SubContent
	UNION
	SELECT 1 AS hitcount, 1 AS score, CAMMetadata.Title, CAMMetadata.ProjectTypeId, 
	CAMMetadata.GenContentId, CAMMetadata.SubContent 
    FROM CAMMetadata WHERE (' ' + CAMMetadata.Title+ ' ') LIKE '%'+ @sSearchKeyword +'%'
	AND CAMMetadata.Id NOT IN 
	(SELECT CAMWeightage.MetadataId FROM CAMWeightage WHERE CAMWeightage.Keyword = @sSearchKeyword)
	ORDER BY hitcount DESC, score DESC	

END
GO
/****** Object:  StoredProcedure [dbo].[GetAccountModule]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		madan.prajapati@magicsw.com
-- ALTER date: 10/12/2010
-- Description:	Fetch Module value corresponding to given account Id
-- =============================================
ALTER PROCEDURE [dbo].[GetAccountModule] 
	-- Add the parameters for the stored procedure here
	@LicenseId int = 0
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Select statements for procedure here
Select ModuleToLicense.Id, ResourceModule.Title,ModuleToLicense.Status from dbo.ModuleToLicense,ResourceModule 
	WHERE ModuleToLicense.ModuleId = ResourceModule.Id AND ModuleToLicense.LicenseId = @LicenseId 
		AND ResourceModule.Status = 1

END
GO
/****** Object:  StoredProcedure [dbo].[GetAllModuleStatus]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<magicsw>
-- ALTER date: <10/12/2010>
-- Description:	<Fetch all the module list>
-- =============================================
ALTER PROCEDURE [dbo].[GetAllModuleStatus]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT ResourceModule.Id,ResourceModule.Title
	FROM ResourceModule
END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetSubscriptionDuration]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 15/4/2009
-- Description:	Fetch Price of given subscriptionId
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetSubscriptionDuration] 
	-- Add the parameters for the stored procedure here
@siSubscriptionId smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Duration from subscriptionplan where id=@siSubscriptionId

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetStateList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 31/3/2009
-- Description:	Fetch State List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetStateList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Id,StateName
from State

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetSecurityQuestionList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 31/3/2009
-- Description:	Fetch Security Question List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetSecurityQuestionList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Id,Title
from SecurityQuestion

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetReferList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 17/4/2009
-- Description:	Fetch Refer List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetReferList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Id,ReferedBy
from Refer

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetProductRequiredList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 17/4/2009
-- Description:	Fetch Refer List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetProductRequiredList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Id,RecommendedType
from AIARequired

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetMultimediaProductList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 17/4/2009
-- Description:	Fetch Mutimedia Product List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetMultimediaProductList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Id,MultimediaProduct
from MultimediaProduct

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetInternetProductList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 17/4/2009
-- Description:	Fetch Mutimedia Product List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetInternetProductList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Id,InternetProduct
from InternetProduct

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetInstitutionList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 17/4/2009
-- Description:	Fetch Institution List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetInstitutionList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Id,InstitutionName
from Institution

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetCourseConductedList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 17/4/2009
-- Description:	Fetch Refer List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetCourseConductedList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Id,CourseConduct
from CourseConduct

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetCountryList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 31/3/2009
-- Description:	Fetch Country List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetCountryList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT Id, CountryName
FROM Country
ORDER BY (case CountryCode when 'US' THEN 0 ELSE Id END)

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetAccountTypeList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 7/4/2009
-- Description:	Fetch AccountType List
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetAccountTypeList] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select Id,Title
from AccountType where IsActive=1

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetSubscriptionPrice]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 15/4/2009
-- Description:	Fetch Price of given subscriptionId
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetSubscriptionPrice] 
	-- Add the parameters for the stored procedure here
@siSubscriptionId smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select CONVERT(NUMERIC(14,2),price) as price from subscriptionplan where id=@siSubscriptionId

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetSubscriptionPlanInfo]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 3/16/2015
-- Description:	Fetch subscription plan,price corresponding to given Edition Id
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetSubscriptionPlanInfo] 
	-- Add the parameters for the stored procedure here
	@iEditioId tinyint,
	@iDuration tinyint,
	@rowId tinyint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select * from 
	(select row_number() over (order by s.Id) as rowId, CONVERT(NUMERIC(14,2),s.price) as price  ,s.title ,s.Id ,s.Duration
	from edition e 
	inner join
	subscriptionplan s 
	on
	e.Id = s.EditionId
	where
	e.id=@iEditioId  and s.isactive=1 and s.duration=@iDuration) tbl where rowId = @rowId
END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetSubscriptionPlan]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 14/4/2009
-- Description:	Fetch subscription plan,price corresponding to given Edition Id
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetSubscriptionPlan] 
	-- Add the parameters for the stored procedure here
	@iEditioId tinyint,
	@iDuration tinyint
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select CONVERT(NUMERIC(14,2),s.price) as price  ,s.title ,s.Id ,s.Duration
	from edition e 
	inner join
	subscriptionplan s 
	on
	e.Id = s.EditionId
	where
	e.id=@iEditioId  and s.isactive=1 and s.duration=@iDuration
END
GO
/****** Object:  StoredProcedure [dbo].[EC_InsertPaymentTransaction]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================================
-- Author:		Magic
-- ALTER date: 27-May-2009
-- Description:	To insert record into PaymentTransactionDetail table
-- ===================================================================
ALTER PROCEDURE [dbo].[EC_InsertPaymentTransaction]
	-- Add the parameters for the stored procedure here
	@iTransactionId varchar(20), @iEditionId varchar(20), @iSubscriptionPlanId varchar(20), @iUserId varchar(20), @sFirstName varchar(50)='',
	@sLastName varchar(50) = '', @sStreet varchar(100)='', @sCity varchar(50) = '', @sState varchar(50),
	@sZip varchar(20), @sCountry varchar(50), @sEmail varchar(50), @sAmount money, @sPaymentStatus varchar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;
	
	INSERT INTO PaymentTransactionDetail (TransactionId, EditionId, SubscriptionPlanId, UserId, FirstName,
	LastName, Street, City, State, Zip, Country, Email, Amount, PurchaseDate, PaymentStatus)
	VALUES (@iTransactionId, @iEditionId, @iSubscriptionPlanId, @iUserId, @sFirstName, @sLastName, @sStreet, 
	@sCity, @sState, @sZip, @sCountry,@sEmail,@sAmount, GETDATE(), @sPaymentStatus)
	
END
GO
/****** Object:  StoredProcedure [dbo].[GetDiscountedPrice]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	MAGIC SOFTWARE PVT LTD
-- ALTER date:	22th Dec, 2009
-- Description:	This SP is used calculated discounted price and return discounted price
--		and Error or success code		
-- =============================================

ALTER PROCEDURE [dbo].[GetDiscountedPrice]

	@inDiscountCode VARCHAR(255),
	@outReturnCode TINYINT output,
	@outDiscountPercentage DECIMAL (5,2) output,
	@outDiscountCodeId int output

AS
BEGIN
	
	--Declaring variable to be used
	--DECLARE @iTotalPrice INT
	DECLARE @iReturnCode TINYINT,
		@iDiscountPercentage DECIMAL (5,2),
		@irActive bit,
		@brDiscountDatePassed bit,
		@brDiscountDateNotStarted bit,
		@iDiscountCodeId int,
		@iExist bit
	
	SET @iExist = 0;
	
	SELECT @irActive = IsActive, @iExist = 1,
		@brDiscountDatePassed = ( CASE WHEN EndDate < CAST( GETDATE() as DATE ) THEN 1 ELSE 0 END ),
		@brDiscountDateNotStarted = ( CASE WHEN StartDate > CAST( GETDATE() as DATE ) THEN 1 ELSE 0 END )
	FROM Discount 
	WHERE DiscountCode = @inDiscountCode
	
	SET NOCOUNT OFF

	IF ( @iExist = 0)
	BEGIN

		SET @outReturnCode = 4

	END
	
	ELSE IF ( @irActive = 0 OR @brDiscountDatePassed = 1 )
	BEGIN

		SET @outReturnCode = 1

	END
	
	-- Chekcing whether discount start date is still to come, if yes return with error code 6
	ELSE IF ( @brDiscountDateNotStarted = 1 )
	BEGIN

		SET @outReturnCode = 2

	END

	-- Checking Whether discount code is available for any of the Images
	ELSE
	BEGIN

		-- getting discount percentage for which discount code is valid
		SELECT @iDiscountPercentage = Percentage, @iDiscountCodeId = Id 
		FROM Discount 
		WHERE DiscountCode = @inDiscountCode 

		-- Setting all the out parameter
		SET @outDiscountPercentage = @iDiscountPercentage
		SET @outReturnCode = 3
		SET @outDiscountCodeId = @iDiscountCodeId

	END

	SELECT @outReturnCode, @outDiscountPercentage, @outDiscountCodeId
		
END
GO
/****** Object:  StoredProcedure [dbo].[GetDiscountDetails]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Author:		Magic Software
-- ALTER date: 23-Dec-2009
-- Description:	To get the details of all discounts
-- ====================================================
ALTER PROCEDURE [dbo].[GetDiscountDetails] 
	-- Add the parameters for the stored procedure here
	@sDiscountCode VARCHAR(255) = '', @sStartDate VARCHAR(20) = '', @sEndDate VARCHAR(20) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @dtStartDate DATETIME, @dtEndDate DATETIME
	
	-- convert the datatype of startdate & enddate parameter to datetime
	SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
	SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
	
	SELECT Id, DiscountCode, Percentage, CONVERT(VARCHAR(10),StartDate,101) as StartDate, CONVERT(VARCHAR(10),EndDate,101) as EndDate, 
		(CASE IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END)  AS Status
		FROM Discount WHERE StartDate >= (CASE WHEN LEN(@sStartDate) > 0 THEN @dtStartDate ELSE StartDate END)
		AND EndDate <= (CASE WHEN LEN(@sEndDate) > 0 THEN @dtEndDate ELSE EndDate END)
		AND DiscountCode = (CASE WHEN LEN(@sDiscountCode) > 0 THEN @sDiscountCode ELSE DiscountCode END) ORDER BY Status
END
GO
/****** Object:  StoredProcedure [dbo].[getEditionData]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getEditionData]
AS
	SET NOCOUNT ON;
SELECT     Edition.*
FROM         Edition
GO
/****** Object:  StoredProcedure [dbo].[GetLoginFailureErrorReport]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		MAGIC SOFTWARE PVT LTD
-- ALTER date: 17 Jan, 2011
-- Description:	Stored Procedure to get LoginFailureErrorReport
-- =============================================
ALTER PROCEDURE [dbo].[GetLoginFailureErrorReport]
	-- Add the parameters for the stored procedure here
	-- FromDate & ToDate are mandatory
	@sFromDate VARCHAR(20), 
	@sToDate VARCHAR(20),  
	@sAccoutNumber CHAR(16)='',
	@sFailureCause TINYINT
AS
BEGIN
	SET NOCOUNT ON;
   
    DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME	
	
	-- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
	
	SELECT UserLoginLog.AccountNumber as AccountNumber,
	(CASE WHEN ActiveEdition.Title IS NULL 
		THEN ISNULL(UserLoginLog.Edition,'')+' (Unknown)' 
		ELSE ActiveEdition.Title 
	END) AS EditionTitle,
	UserLoginLog.ReferalUrl as ReferalUrl,
	UserLoginLog.HttpReferer as HttpReferer,
	LoginFailureCause.Description as FailureCause,
	CONVERT(VARCHAR,UserLoginLog.LogDate,101) as LogDate
	FROM UserLoginLog
	LEFT JOIN (SELECT Edition.Title, Edition.Id FROM Edition WHERE Edition.IsActive=1) ActiveEdition
	ON UserLoginLog.Edition = cast(ActiveEdition.Id AS NVARCHAR)
	INNER JOIN LoginFailureCause
	ON UserLoginLog.FailureId=LoginFailureCause.Id 
	WHERE UserLoginLog.LogDate BETWEEN @dtFromDate AND @dtToDate 
	AND UserLoginLog.FailureId = (CASE WHEN @sFailureCause > 0 
										THEN @sFailureCause 
										ELSE UserLoginLog.FailureId 
								  END)
	AND UserLoginLog.AccountNumber = (CASE WHEN LEN(@sAccoutNumber) > 0 
											THEN @sAccoutNumber 
											ELSE UserLoginLog.AccountNumber 
									  END)
	
END
GO
/****** Object:  StoredProcedure [dbo].[GetEncyclopediaSearch]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
-- Author:		Magic
-- ALTER date: 28-May-09
-- Description:	Get the result based on the keyword search
-- =============================================================
ALTER PROCEDURE [dbo].[GetEncyclopediaSearch]
	-- Add the parameters for the stored procedure here
	@iLexiconId int, @sSearchKeyword nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT COUNT(DISTINCT EncyclopediaWeightage.Keyword) AS hitcount, 
    SUM(EncyclopediaWeightage.Relevancy) AS score, 
    EncyclopediaMetadata.Title, EncyclopediaMetadata.ProjectTypeId, EncyclopediaMetadata.GenContentId, 
    EncyclopediaMetadata.SubContent
	FROM EncyclopediaMetadata
	INNER JOIN EncyclopediaWeightage 
	ON EncyclopediaMetadata.Id = EncyclopediaWeightage.MetadataId
	WHERE EncyclopediaMetadata.LexiconId = @iLexiconId
	AND EncyclopediaWeightage.Keyword = @sSearchKeyword
	GROUP BY EncyclopediaWeightage.Keyword, EncyclopediaWeightage.Relevancy, EncyclopediaMetadata.Title, 
	EncyclopediaMetadata.ProjectTypeId, EncyclopediaMetadata.GenContentId,EncyclopediaMetadata.SubContent
	UNION
	SELECT 1 AS hitcount, 1 AS score, EncyclopediaMetadata.Title, EncyclopediaMetadata.ProjectTypeId, 
	EncyclopediaMetadata.GenContentId, EncyclopediaMetadata.SubContent 
    FROM EncyclopediaMetadata WHERE (' ' + EncyclopediaMetadata.Title+ ' ') LIKE '%'+ @sSearchKeyword +'%'
	AND EncyclopediaMetadata.Id NOT IN 
	(SELECT InDepthWeightage.MetadataId FROM InDepthWeightage WHERE InDepthWeightage.Keyword = @sSearchKeyword)
    ORDER BY hitcount DESC, score DESC
	
	
END
GO
/****** Object:  StoredProcedure [dbo].[GetLabExcerciseByUserId]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- ALTER date: <ALTER Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetLabExcerciseByUserId] 
	-- Add the parameters for the stored procedure here
	@UserId int,
	@Identifier nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Select 
			le.Id,
			le.UserId,
			le.LabExerciseIdentifier,
			le.LastQuestion,
			le.TotalQuestions,
			led.StateObject,
			led.UserAnswers,
			led.Score,
			led.MaxScore,
			led.QuestionNo,
			led.CorrectAnswers,
			led.DragItems
	from LabExercise le
	inner join LabExerciseDetails led
	on le.Id = led.LabQuizId
	where 
	le.UserId = @UserId and le.LabExerciseIdentifier = @Identifier and le.IsActive =1
	
END
GO
/****** Object:  StoredProcedure [dbo].[GetInDepthSearch]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
-- Author:		Magic
-- ALTER date: 08-Mar-2011
-- Description:	Get the result based on the keyword search
-- =============================================================
ALTER PROCEDURE [dbo].[GetInDepthSearch]
	-- Add the parameters for the stored procedure here
	@sSearchKeyword nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT COUNT(DISTINCT InDepthWeightage.Keyword) AS hitcount, 
	SUM(InDepthWeightage.Relevancy) AS score, 
	InDepthMetadata.Title, InDepthMetadata.ProjectTypeId, 
	InDepthMetadata.GenContentId, InDepthMetadata.SubContent
	FROM InDepthMetadata
	INNER JOIN InDepthWeightage 
	ON InDepthMetadata.Id = InDepthWeightage.MetadataId
	WHERE InDepthMetadata.LexiconId = 1 
	AND InDepthWeightage.Keyword = @sSearchKeyword
	GROUP BY InDepthWeightage.Keyword, InDepthWeightage.Relevancy, InDepthMetadata.Title, 
	InDepthMetadata.ProjectTypeId, InDepthMetadata.GenContentId,InDepthMetadata.SubContent
	UNION
	SELECT 1 AS hitcount, 1 AS score, InDepthMetadata.Title, InDepthMetadata.ProjectTypeId, 
	InDepthMetadata.GenContentId, InDepthMetadata.SubContent
	FROM InDepthMetadata 
	WHERE (' ' + InDepthMetadata.Title+ ' ') LIKE '%'+ @sSearchKeyword +'%'
	AND InDepthMetadata.Id NOT IN 
	(SELECT InDepthWeightage.MetadataId FROM InDepthWeightage WHERE InDepthWeightage.Keyword = @sSearchKeyword)
	ORDER BY hitcount DESC, score DESC	
	
END
GO
/****** Object:  StoredProcedure [dbo].[GetModuleStatusByLicenseId]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- ALTER date: <ALTER Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetModuleStatusByLicenseId]
	-- Add the parameters for the stored procedure here
	@iLicenseId int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT ResourceModule.Id,ResourceModule.Title,ModuleToLicense.Status
	FROM ResourceModule
	INNER JOIN ModuleToLicense ON ResourceModule.Id = ModuleToLicense.ModuleId
	WHERE ModuleToLicense.LicenseId = @iLicenseId
END
GO
/****** Object:  StoredProcedure [dbo].[NewUpdateCommand]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NewUpdateCommand]
(
	@Title varchar(50),
	@Priority tinyint,
	@IsActive bit,
	@Original_Id tinyint,
	@Original_Title varchar(50),
	@Original_Priority tinyint,
	@Original_IsActive bit,
	@Id tinyint
)
AS
	SET NOCOUNT OFF;
UPDATE [Edition] SET [Title] = @Title, [Priority] = @Priority, [IsActive] = @IsActive WHERE (([Id] = @Original_Id) AND ([Title] = @Original_Title) AND ([Priority] = @Original_Priority) AND ([IsActive] = @Original_IsActive));
	
SELECT Id, Title, Priority, IsActive FROM Edition WHERE (Id = @Id)
GO
/****** Object:  StoredProcedure [dbo].[NewInsertCommand]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NewInsertCommand]
(
	@Title varchar(50),
	@Priority tinyint,
	@IsActive bit
)
AS
	SET NOCOUNT OFF;
INSERT INTO [Edition] ([Title], [Priority], [IsActive]) VALUES (@Title, @Priority, @IsActive);
	
SELECT Id, Title, Priority, IsActive FROM Edition WHERE (Id = SCOPE_IDENTITY())
GO
/****** Object:  StoredProcedure [dbo].[NewDeleteCommand]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NewDeleteCommand]
(
	@Original_Id tinyint,
	@Original_Title varchar(50),
	@Original_Priority tinyint,
	@Original_IsActive bit
)
AS
	SET NOCOUNT OFF;
DELETE FROM [Edition] WHERE (([Id] = @Original_Id) AND ([Title] = @Original_Title) AND ([Priority] = @Original_Priority) AND ([IsActive] = @Original_IsActive))
GO
/****** Object:  StoredProcedure [dbo].[InsertNewDiscount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Author:		Magic Software
-- ALTER date: 23-Dec-2009
-- Description:	To ALTER new discount
-- ====================================================
ALTER PROCEDURE [dbo].[InsertNewDiscount] 
	-- Add the parameters for the stored procedure here
	@dPercentage DECIMAL(5,2), @sStartDate VARCHAR(20), @sEndDate VARCHAR(20), @sDiscountCode VARCHAR(255)=''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @iDiscountId INT, @iDiscountExists INT
			DECLARE @iActive TINYINT
			DECLARE @dtStartDate DATETIME, @dtEndDate DATETIME
			DECLARE @sErrorStatus CHAR(2)
			
			SET @iActive = 1
			SET @sErrorStatus = 'ok'
			
			-- convert the datatype of startdate & enddate parameter to datetime
			SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
			SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
			
			INSERT INTO Discount (Percentage, StartDate, EndDate, IsActive) 
				VALUES(@dPercentage, @dtStartDate, @dtEndDate, @iActive)
			-- to get the last inserted discount id identity value in the current session
			SET @iDiscountId = SCOPE_IDENTITY()
			
			IF @sDiscountCode = ''
			BEGIN
				SET @sDiscountCode = 'InteractiveAnatomy'+RIGHT('000'+CAST(@iDiscountId AS VARCHAR(10)), 3)
				SET @iDiscountExists = (SELECT Id FROM Discount WHERE DiscountCode = @sDiscountCode)
				IF @iDiscountExists > 0
				BEGIN
					UPDATE Discount SET IsActive = 0 WHERE Id = @iDiscountExists
				END
			END
			UPDATE Discount SET DiscountCode = @sDiscountCode WHERE Id = @iDiscountId
			
		COMMIT
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
   
END
GO
/****** Object:  StoredProcedure [dbo].[InsertLoginErrorLog]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ALTER date: 2 July, 2009
-- Description:	This SP is used to add failure cause
-- =============================================
ALTER PROCEDURE [dbo].[InsertLoginErrorLog]
@nvAccountNumber NVARCHAR(50) = '',
@dtLogDate DATETIME,
@tiFailureId TINYINT,
@nvReferalUrl NVARCHAR(100) = NULL,
@nvEdition NVARCHAR(100) = '',
@nvHttpReferer NVARCHAR(100)= NULL


AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [UserLoginLog] ([AccountNumber],[LogDate],[FailureId],[ReferalUrl],[Edition],[HttpReferer])
	VALUES
	(@nvAccountNumber, @dtLogDate,NullIf(@tiFailureId,0), @nvReferalUrl,@nvEdition,@nvHttpReferer)
	
	DELETE FROM [AIADatabaseV5].[dbo].[UserLoginLog] where DATEDIFF(day, [LogDate], GETDATE()) >= 180

	
					
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateDiscountStatus]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- ALTER date: <ALTER Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[UpdateDiscountStatus] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	UPDATE Discount
	SET    IsActive =0
	WHERE  EndDate<= (Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()+.5)))
   
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateDiscount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Author:		Magic Software
-- ALTER date: 23-Dec-2009
-- Description:	To update the details of discount
-- ====================================================
ALTER PROCEDURE [dbo].[UpdateDiscount] 
	-- Add the parameters for the stored procedure here
	@iDiscountId INT, @dPercentage DECIMAL(5,2), @sStartDate VARCHAR(20), @sEndDate VARCHAR(20), @iActive TINYINT, @sDiscountCode VARCHAR(255)=''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @dtStartDate DATETIME, @dtEndDate DATETIME
			DECLARE @sErrorStatus CHAR(2)
			
			SET @sErrorStatus = 'ok'
			
			-- convert the datatype of startdate & enddate parameter to datetime
			SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
			SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
			
			UPDATE Discount SET Percentage = @dPercentage, StartDate = @dtStartDate, EndDate = @dtEndDate,
				IsActive = @iActive, DiscountCode = @sDiscountCode WHERE Id = @iDiscountId
			
		COMMIT
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
   
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateLicenseModule]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic Software
-- ALTER date: 10/12/2010
-- Description:	update the module status of a license
-- =============================================
ALTER PROCEDURE [dbo].[UpdateLicenseModule] @iLicenseId INT, @sModuleTitle VARCHAR(50), @iStatus INT
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    BEGIN TRY
		BEGIN TRANSACTION
			
			DECLARE @sErrorStatus CHAR(2) = 'ok'
		    DECLARE @iModuleId INT
		    
		    SELECT @iModuleId = ResourceModule.Id FROM ResourceModule INNER JOIN ModuleToLicense ON ResourceModule.Id = ModuleToLicense.ModuleId
		    WHERE ResourceModule.Title = @sModuleTitle AND ModuleToLicense.LicenseId = @iLicenseId
		    IF @@ROWCOUNT > 0
		    BEGIN
				UPDATE ModuleToLicense SET Status = @iStatus WHERE LicenseId = @iLicenseId 
				AND ModuleId = @iModuleId
		    END
		    ELSE
		    BEGIN
				INSERT INTO ModuleToLicense (LicenseId, ModuleId, Status) 
				SELECT @iLicenseId, ResourceModule.Id, @iStatus FROM ResourceModule WHERE ResourceModule.Title = @sModuleTitle
		    END
		COMMIT TRANSACTION	
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[GetThreeDHighLightData]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetThreeDHighLightData]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @cGetImageContent CURSOR
	DECLARE @iImageContentId INT
	DECLARE @xmlData VARCHAR(MAX)
	
	TRUNCATE TABLE TempXMLStore
	SET @cGetImageContent = CURSOR FAST_FORWARD 
	FOR
		SELECT  ic.Id
		FROM ContentDescriptor cd 
		INNER JOIN ImageContent ic ON ic.ContentDescriptorId = cd.id AND cd.isActive = 'Y' AND cd.ContentTypeId = 3
	OPEN @cGetImageContent
	FETCH NEXT FROM @cGetImageContent INTO @iImageContentId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @xmlData = '<?xml version="1.0" encoding="utf-8"?><root>'
		SET @xmlData = @xmlData+(SELECT(SELECT ThreeDTermsToHighLight.HighLightId as hid, ThreeDTermsToHighLight.TermNumberId as tn
			FROM VocabTerms Inner join ThreeDTermsToHighLight on VocabTerms.TermNumber=ThreeDTermsToHighLight.TermNumberId
			WHERE VocabTerms.VocabLexiconId = 1 AND IcId = @iImageContentId ORDER BY hid
			FOR XML RAW('it')) as XMLSTRING)
		SET @xmlData = @xmlData+'</root>'
		INSERT INTO TempXMLStore SELECT @iImageContentId, @xmlData
		
		FETCH NEXT FROM @cGetImageContent INTO @iImageContentId
	END
END
GO
/****** Object:  StoredProcedure [dbo].[GetTermNumberData]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetTermNumberData]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @cGetTermNumber CURSOR
	DECLARE @iImageContentId INT
	DECLARE @xmlData VARCHAR(MAX)
	
	TRUNCATE TABLE TempXMLStore
	SET @cGetTermNumber = CURSOR FAST_FORWARD 
	FOR
		SELECT  ic.Id
		FROM ContentDescriptor cd 
		INNER JOIN ImageContent ic ON ic.ContentDescriptorId = cd.id AND cd.isActive = 'Y' AND cd.ContentTypeId = 2
	OPEN @cGetTermNumber
	FETCH NEXT FROM @cGetTermNumber INTO @iImageContentId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @xmlData = '<?xml version="1.0" encoding="utf-8"?><root>'
		SET @xmlData = @xmlData+(SELECT(SELECT DISTINCT vt.TermNumber as tn, vt.VocabLexiconId as lId, vt.TermText as tt
		FROM VocabTerms vt
		INNER JOIN AnnotationPin ap ON vt.TermNumber = ap.VocabTermNumbers
		INNER JOIN ImageContent ic ON ap.ImageContentId = ic.Id  
		WHERE ic.Id = @iImageContentId FOR XML RAW('it')) as XMLSTRING)
		SET @xmlData = @xmlData+'</root>'
		INSERT INTO TempXMLStore SELECT @iImageContentId, @xmlData
		
		FETCH NEXT FROM @cGetTermNumber INTO @iImageContentId
	END
END
GO
/****** Object:  StoredProcedure [dbo].[GetSiteLicenseUsageReport]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Magic Software>
-- ALTER date: <14-Jan-2011>
-- Description:	<To get the list of Site License Usage Report>
-- =============================================
ALTER PROCEDURE [dbo].[GetSiteLicenseUsageReport] 
	-- Add the parameters for the stored procedure here
	@sFromDate varchar(20), @sToDate varchar(20), @sAccoutNumber varchar(50)='', @iEditionId tinyint = 0
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON
	DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME

    -- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
	
	SELECT UserLoginLog.AccountNumber, Edition.Title AS EditionTitle, UserLoginLog.ReferalUrl,
	(SELECT License.InstitutionName FROM License WHERE License.AccountNumber = UserLoginLog.AccountNumber) as InstitutionName,
	(SELECT CONVERT(VARCHAR,License.CreationDate,101) FROM License WHERE License.AccountNumber = UserLoginLog.AccountNumber) as LicenseCreationDate,
	COUNT(DISTINCT UserLoginLog.LogDate) AS TotalLogins, 
	CONVERT(VARCHAR,MAX(UserLoginLog.LogDate),101) AS LastLogin FROM 
	UserLoginLog INNER JOIN Edition ON UserLoginLog.Edition = CAST(Edition.Id AS NVARCHAR) 
	WHERE UserLoginLog.FailureId IS NULL
	AND UserLoginLog.LogDate BETWEEN @dtFromDate AND @dtToDate
	AND UserLoginLog.AccountNumber = (CASE WHEN LEN(@sAccoutNumber) > 0 THEN @sAccoutNumber ELSE UserLoginLog.AccountNumber END)
	AND Edition.IsActive = 1
	AND Edition.Id = (CASE WHEN @iEditionId > 0 THEN @iEditionId ELSE Edition.Id END)
	GROUP BY UserLoginLog.AccountNumber, Edition.Title, UserLoginLog.ReferalUrl
END
GO
/****** Object:  StoredProcedure [dbo].[GetSearchTerms]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 19/5/2009
-- Description:	Fetch Editions for building level accounts for given Account Number and SiteId.
-- =============================================
ALTER PROCEDURE [dbo].[GetSearchTerms]
	
AS
BEGIN
	
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    Select * from (
	Select CASE WHEN trm.SearchText is not null THEN trm.SearchText ELSE phr.SearchText END as SearchText, 
		isnull(trm.TID,0) as VocabTermId, isnull(phr.PID,0) as PhraseId,
		(CASE WHEN trm.LID IS NOT NULL THEN trm.LID ELSE phr.LID END) as LexiconId 
	From 
			(Select vt.TermText + 'T' as SearchText,max(vtn.TermNumber) as TID, vt.VocabLexiconId as LID
			From dbo.VocabTerms vt 
				INNER JOIN dbo.VocabTermNumbers vtn on vt.TermNumber=vtn.TermNumber
				INNER JOIN dbo.VocabLexicon vl on vt.VocabLexiconId = vl.Id
			Where vtn.IsCurrent='Y' and vl.IsCurrent = 'Y'
			Group by vt.TermText + 'T', vt.VocabLexiconId
			) trm
		FULL OUTER JOIN
			(Select sp.Phrase + 'T' as SearchText, max(sp.Id) as PID, 1 as LID
			From ContentDescriptor cd 
				INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.Id=cdpm.ContentDescriptorId 
				INNER JOIN dbo.SearchPhrase sp on cdpm.SearchPhraseId=sp.Id 
				Group by sp.Phrase + 'T'
			Union
			Select sp.Phrase + 'T' as SearchText, max(sp.Id) as PID, 2 as LID
			From ContentDescriptor cd 
				INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.Id=cdpm.ContentDescriptorId 
				INNER JOIN dbo.SearchPhrase sp on cdpm.SearchPhraseId=sp.Id 
				Group by sp.Phrase + 'T') phr
		ON trm.SearchText = phr.SearchText and trm.LID = phr.LID) ads
	Order by LexiconId,SearchText

END
GO
/****** Object:  StoredProcedure [dbo].[GetUserTyeByAccountNumber]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic Software
-- ALTER date: 29-Apr-2009
-- Description:	To get the list of user type on the basis of account number and type of logged in user
-- =============================================
ALTER PROCEDURE [dbo].[GetUserTyeByAccountNumber] 
	-- Add the parameters for the stored procedure here
	@iUserTypeId tinyint, @iLicenseId int
AS
BEGIN
	-- returns the metadata
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @sUserType varchar(50)
	DECLARE @sLicenseType varchar(50)
	-- ALTER a temporary table to store the usertype according to the role and accountnumber
	CREATE TABLE #UserTypeToAccountNumber 
	(
		Id tinyint,
		Title varchar(50)
	)

	--SELECT @sUserType = Title FROM UserType WHERE Id = @iUserTypeId

	IF  @iLicenseId = 0
	BEGIN
		IF @iUserTypeId = 1
		BEGIN
			INSERT INTO #UserTypeToAccountNumber SELECT Id, Title FROM UserType WHERE Title = 'General Admin' AND IsActive = 1
		END
	END
	ELSE
	BEGIN
		SELECT @sLicenseType = LicenseType.Title FROM License INNER JOIN LicenseType ON LicenseType.Id = License.LicenseTypeId
		WHERE License.Id = @iLicenseId
		IF @sLicenseType = 'Site License'
		BEGIN
			INSERT INTO #UserTypeToAccountNumber SELECT Id, Title FROM UserType WHERE Title IN ('Client Admin', 'District Admin')
		END
		ELSE IF @sLicenseType = 'Concurrent License'
		BEGIN 	
			INSERT INTO #UserTypeToAccountNumber SELECT Id, Title FROM UserType WHERE Title IN ('Client Admin', 'Concurrent User') ORDER BY Priority ASC
		END
	END
	SELECT Id,Title FROM #UserTypeToAccountNumber
	-- Dropping the temporary table
	DROP TABLE #UserTypeToAccountNumber
END
GO
/****** Object:  StoredProcedure [dbo].[GetProductFeatures]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 03/4/2009
-- Description:	Fetch subscription plan,price corresponding to given Edition Id
-- =============================================
ALTER PROCEDURE [dbo].[GetProductFeatures] 
	-- Add the parameters for the stored procedure here
	@EditionId int = 0
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
Select ProductFeature.Id, ProductFeature.Title, (CASE WHEN (Select 1 from dbo.EditionToProductFeature
	WHERE dbo.EditionToProductFeature.editionid = @EditionId AND ProductFeature.id = dbo.EditionToProductFeature.FeatureId) = 1 THEN 1 ELSE 0 END) AS IsActive
From ProductFeature

END
GO
/****** Object:  StoredProcedure [dbo].[GetImagePinData]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- alter script of GetImagePinData SP

ALTER PROCEDURE [dbo].[GetImagePinData]
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
DECLARE @cGetImageContentId CURSOR
DECLARE @iImageContentId INT
DECLARE @xmlData VARCHAR(MAX)

TRUNCATE TABLE TempXMLStore
SET @cGetImageContentId = CURSOR FAST_FORWARD
	FOR
		SELECT  ic.Id
		FROM ContentDescriptor cd 
		INNER JOIN ImageContent ic ON ic.ContentDescriptorId = cd.id AND cd.isActive = 'Y' AND cd.ContentTypeId = 2
	  
	OPEN @cGetImageContentId
	FETCH NEXT FROM @cGetImageContentId INTO @iImageContentId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @xmlData = '<?xml version="1.0" encoding="utf-8"?>'
		SET @xmlData = @xmlData+(SELECT Tag, Parent, [root!1!aoi], [it!2!pId], [it!2!hX], [it!2!hY], [it!2!pX], 
			[it!2!pY], [it!2!tId], [it!2!bsId], [it!2!bs] 
			FROM (SELECT DISTINCT 1 AS Tag, NULL AS Parent,
			aoi.FileName AS [root!1!aoi],
			NULL AS [it!2!pId],
			NULL AS [it!2!hX],
			NULL AS [it!2!hY],
			NULL AS [it!2!pX],
			NULL AS [it!2!pY],
			NULL AS [it!2!tId],
			NULL AS [it!2!bsId],
			NULL AS [it!2!bs]
		FROM AtlasOrientImage aoi
		RIGHT JOIN ImageContent ic ON aoi.ContentDescriptorId = ic.ContentDescriptorId
		WHERE ic.Id = @iImageContentId
		UNION ALL
		SELECT DISTINCT 2 AS Tag, 1 AS Parent,
			aoi1.FileName AS [root!1!aoi],
			ap.Id AS [it!2!pId],
			ap.HeadX AS [it!2!hX],
			ap.HeadY AS [it!2!hY],
			ap.PointX AS [it!2!pX],
			ap.PointY AS [it!2!pY],
			ap.VocabTermNumbers AS [it!2!tId],
			vtn.VocabSystemsNumbersId AS [it!2!bsId],
			vs.Name AS [it!2!bs]
		FROM AnnotationPin ap
		INNER JOIN ImageContent ic ON ap.ImageContentId = ic.Id
		INNER JOIN VocabTermNumbers vtn ON ap.VocabTermNumbers = vtn.TermNumber
		INNER JOIN VocabSystems vs ON vs.SystemNumber = vtn.VocabSystemsNumbersId
		LEFT JOIN AtlasOrientImage aoi1 ON aoi1.ContentDescriptorId = ic.ContentDescriptorId 
			WHERE ImageContentId = @iImageContentId ) as A
		ORDER BY [root!1!aoi],[it!2!pId] FOR XML Explicit)
		
		INSERT INTO TempXMLStore SELECT @iImageContentId, @xmlData
		FETCH NEXT FROM @cGetImageContentId INTO @iImageContentId
	END
END
GO
/****** Object:  StoredProcedure [dbo].[GetContentList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic
-- ALTER date: 22/4/2009
-- Description:	Fetch the content list for a particular content type
-- =============================================
ALTER PROCEDURE [dbo].[GetContentList] 
	-- Add the parameters for the stored procedure here
	-- ContentTypeId is mandatory
	-- To fix - needs to be sent as a parameter from the calling area
	@iContentTypeId INT = 1
	
AS
BEGIN
	
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT  ct.Id,cd.id as ContentDescriptorId, ci.Title  
	FROM ContentType ct
	inner join ContentDescriptor  cd ON cd.ContentTypeId = ct.Id
	inner join ContentIdentifier ci ON ci.Id = cd.ContentIdentifierId 
	WHERE ct.Id = @iContentTypeId and cd.IsActive='Y'

END
GO
/****** Object:  StoredProcedure [dbo].[EC_GetUsername]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 9/4/2009
-- Description:	Retreive user name
-- =============================================
ALTER PROCEDURE [dbo].[EC_GetUsername] 
	-- Add the parameters for the stored procedure here
@sUserName varchar(50)
AS

Declare @iCount int

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select @iCount =Count(LoginId) 
from AIAUser where LoginId=@sUserName

Return @iCount  

END
GO
/****** Object:  StoredProcedure [dbo].[GetAttributeTypeList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic
-- ALTER date: 22/4/2009
-- Description:	Fetch the list of Attribute types for a particular content type
-- =============================================
ALTER PROCEDURE [dbo].[GetAttributeTypeList]
	-- Add the parameters for the stored procedure here
	-- ContentTypeId is mandatory
	-- To fix - needs to be sent as a parameter from the calling area
	@iContentTypeId int = 4
AS
BEGIN
	
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT cat.Name, cat.ShortName 
	FROM ContentType ct
	inner join ContentTypeToAttributeTypeMap ctam ON ctam.ContentTypeId = ct.Id
	inner join ContentAttributeType cat ON cat.id = ctam.ContentAttributeTypeId
	WHERE ct.Id = @iContentTypeId and cat.IsFilter = 'Y'
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetVocabTermModal]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get  Vocab Term for the lexicon ID
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetVocabTermModal] 
	-- Add the parameters for the stored procedure here
@lexiconID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT t.TermNumber , t.TermText, n.VocabSystemsNumbersId

	FROM VocabTermNumbers n INNER JOIN VocabTerms t

	ON n.TermNumber = t.TermNumber

	WHERE n.IsCurrent = 'Y' AND t.VocabLexiconId = @lexiconID


END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetBaseLayer]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 05/08/2009
-- Description:	Get BaseLayer for given layer id
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetBaseLayer] 
	-- Add the parameters for the stored procedure here
@layer_id int

AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT 
	baseMap.Id, baseMap.RegionLayerMappingId, baseMap.Zoom,
	baseMap.LayerNumberInternal, baseMap.TermNumber, baseMap.ImageId,  
	LayerMap.DissectibleRegionId
	FROM DissectibleRegionLayerBaseMapArt baseMap 
	INNER JOIN DissectibleRegionLayerMapping LayerMap 
	ON baseMap.RegionLayerMappingId = LayerMap.Id
	WHERE (LayerMap.DissectibleLayerId = @layer_id)
	ORDER BY LayerMap.DissectibleRegionId, baseMap.LayerNumberInternal

END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetBackGroundArtList]    Script Date: 06/06/2018 07:54:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 1/5/2009
-- Description:	Get BackGround Art List
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetBackGroundArtList] 
	-- Add the parameters for the stored procedure here
@body_view_id int , 
@sex char
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT
	(
	SELECT [Id] as Id
      ,[DissectibleBodyViewId] as voId
      ,[DissectibleRegionId] as brId
      ,[IsForeground] as isfr
      ,[Zoom] as zm
      ,[IsBetadine] as isbt
      ,[SkinTone] as sktn
      ,[Sex] as gr
      ,[LayerNumberStart] as lns
      ,[LayerNumberEnd] as lne
      ,[OffsetX] as ox
      ,[OffsetY] as oy
      ,[ImageId] as imId
	FROM [DissectibleBodyViewBackgroundArt] as it where 
	IsBetadine='N' 
	and Sex=@sex 
	and DissectibleBodyViewId=@body_view_id
	For XML AUTO )
	as XMLString 

END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetLexiconTermList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get LexiconTermList from VocabTerms
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetLexiconTermList] 
	-- Add the parameters for the stored procedure here
@lexicon_id varchar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT
	(
	SELECT [TermNumber] as tn
		   ,[TermText] as tt
    FROM [VocabTerms] AS it
	WHERE VocabLexiconId IN (select * from udf_SplitString(@lexicon_id))
	For XML AUTO)
	as XMLString 
	
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetOverLayLayerList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 05/08/2009
-- Description:	Get OverlayLayerList
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetOverLayLayerList] 
	-- Add the parameters for the stored procedure here
@body_view_id int,
@sex char(1)

AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT 
	Id, DissectibleRegionId, SkinTone, IsForeground,LayerNumberStart,LayerNumberEnd,OffsetX,OffsetY,ImageId
	FROM DissectibleBodyViewBackgroundArt
	WHERE (DissectibleBodyViewId = @body_view_id) 
	AND (sex = @sex OR sex = '+') 
	AND IsBetadine = 'N' 
	

END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetStructureGroupList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get Structure Group List
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetStructureGroupList] 
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT
	(
	SELECT [Id] as id  
       ,[DissectibleBodyViewId] as voId  
       ,[Sex] as gr 
        FROM [DissectibleStructureGroup] it
        For XML AUTO)
	as XMLString   
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetStructureGroupId]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 30/4/2009
-- Description:	Get  body view id and gender for given Structure Group Id
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetStructureGroupId] 
	-- Add the parameters for the stored procedure here
@structure_group_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT [DissectibleBodyViewId] ,
	Sex 
    FROM [DissectibleStructureGroup] it where
	Id=@structure_group_id
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetPLRModel]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 2/5/2009
-- Description:	Get Pixel Level Recognition
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetPLRModel] 
	-- Add the parameters for the stored procedure here
@structure_group_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT [Id] 
		  ,[ParentId] 
		  ,ISNULL(IsSystemStructure,'N')  as SystemStructure
		  ,[LayerNumberInternal] 
		  ,[LayerNumber]
		  ,[IsSkin] 
		  ,[IsBaseArt] 
		  ,[TermNumber] 
		  ,[PlrStructureId] 
		  ,[PlrStructureIndex] 
		  ,[DissectiblePolygonId] 
		  ,[BoundLeft] 
		  ,[BoundRight] 
		  ,[BoundTop] 
		  ,[BoundBottom] 
	  FROM [DissectibleStructures]
	  WHERE (DissectibleStructureGroupId=@structure_group_id) 
	  ORDER BY ParentId,PlrStructureId,PlrStructureIndex 	
	
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetNavigatorModel]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get Dissectible Navigator Images from DissectibleNavigatorImages
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetNavigatorModel] 
	-- Add the parameters for the stored procedure here
@content_descriptor_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--BodyRegionList(Retreive Body Region  List from DissectibleRegion )  
	SELECT Id, SkinTone, FigLeaf, GrayScale
	FROM DissectibleNavigatorImages
	where ContentDescriptorId = @content_descriptor_id


END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetLayerModel]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 17/4/2009
-- Description:	Fetch Layer  Data for given body view id and gender
-- =============================================
ALTER PROCEDURE [dbo].[DA_GetLayerModel] 
	-- Add the parameters for the stored procedure here
@body_view_id int , 
@sex char 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT	dl.Id , dl.LayerNumber , dl.LayerNumberInternal , dl.LayerName , 
			ISNULL(dl.IsSkin, 'N') AS IsSkin ,
			ISNULL(dl.IsBase, 'N')  AS IsBase , map.DissectibleRegionId , 
			map.LayerNumberInternal ,  art.Zoom ,
			ISNULL(art1.ImageId, 0)   AS ISOID , 
			art.ImageId AS COMPID
			FROM DissectibleRegionLayerArt art INNER JOIN
			DissectibleRegionLayerMapping map ON art.DissectibleRegionLayerMappingId = map.Id INNER JOIN
			DissectibleLayer dl ON map.DissectibleLayerId = dl.Id LEFT OUTER JOIN
			DissectibleRegionLayerArt art1 ON map.Id = art1.DissectibleRegionLayerMappingId AND art1.IsIsolate = 'Y' AND art1.IsBetadine <> 'Y' AND 
			art1.IsDermatome <> 'Y' AND art1.IsBald <> 'Y' AND art1.IsHair <> 'Y'
			WHERE     (map.BodyViewId = @body_view_id) AND (ISNULL(map.Sex, '+') = @sex) AND (art.IsBetadine <> 'Y') AND (art.IsDermatome <> 'Y') AND (art.IsBald <> 'Y') AND 
			(art.IsHair <> 'Y') AND (art.IsIsolate = 'N')
			ORDER BY dl.Id DESC, dl.LayerNumber DESC, map.DissectibleRegionId DESC 
			
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetLayerdata]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 17/4/2009
-- Description:	Fetch Layer  List
-- =============================================
ALTER PROCEDURE [dbo].[DA_GetLayerdata] 
	-- Add the parameters for the stored procedure here
@body_view_id int , 
@sex char 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
   SELECT(
   SELECT	dl.Id AS id, dl.LayerNumber AS ln, dl.LayerNumberInternal AS lni, dl.LayerName AS lnm, 
			ISNULL(dl.IsSkin, 'N') AS issk,
			ISNULL(dl.IsBase, 'N') AS isbs, map.DissectibleRegionId AS brId, 
			map.LayerNumberInternal AS lni, art.SkinTone AS sktn, art.Zoom AS zm,
			ISNULL(art1.ImageId, 0) AS isoId, 
			art.ImageId AS compId
			FROM DissectibleRegionLayerArt art INNER JOIN
			DissectibleRegionLayerMapping map ON art.DissectibleRegionLayerMappingId = map.Id INNER JOIN
			DissectibleLayer dl ON map.DissectibleLayerId = dl.Id LEFT OUTER JOIN
			DissectibleRegionLayerArt art1 ON map.Id = art1.DissectibleRegionLayerMappingId AND art1.IsIsolate = 'Y' AND art1.IsBetadine <> 'Y' AND 
			art1.IsDermatome <> 'Y' AND art1.IsBald <> 'Y' AND art1.IsHair <> 'Y'
			WHERE     (map.BodyViewId = @body_view_id) AND (ISNULL(map.Sex, '+') = @sex) AND (art.IsBetadine <> 'Y') AND (art.IsDermatome <> 'Y') AND (art.IsBald <> 'Y') AND 
			(art.IsHair <> 'Y') AND (art.IsIsolate = 'N')
			ORDER BY dl.Id DESC, dl.LayerNumber DESC, map.DissectibleRegionId DESC FOR XML AUTO)
			as XMLString  
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetDissectibleContent]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 8/5/2009
-- Description:	Get Dissectible Content Description
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetDissectibleContent] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--BodyRegionList(Retreive Body Region  List from DissectibleRegion )  
	SELECT 
	dc.Id 
	,dc.ContentDescriptorId 
	,dsg.Id AS StructureGroupId 
	,dsg.DissectibleBodyViewId 
	,dsg.Sex 
	,dc.SexPresentation 
	,dc.PlrOffsetX 
	,dc.PlrOffsetY 
	FROM DissectibleContent dc , DissectibleStructureGroup dsg 
	WHERE (dc.DissectibleStructureGroupId = dsg.Id)
	ORDER BY dc.Id        

END
GO
/****** Object:  StoredProcedure [dbo].[DA_LayerNumberInternal]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get [DA_GetTermList] from VocabTerms
-- =============================================
ALTER  PROCEDURE [dbo].[DA_LayerNumberInternal] 
	-- Add the parameters for the stored procedure here

@structure_group_id int,
@layer_number_internal int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	 select distinct LayerNumberInternal from DissectibleStructures where LayerNumberInternal>=@layer_number_internal 
 and DissectibleStructureGroupId = @structure_group_id order by LayerNumberInternal desc
	
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetTermNumberForContentId]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 20/5/2009
-- Description:	Get Term numbers for given Dissectible Content Id
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetTermNumberForContentId] 
	-- Add the parameters for the stored procedure here
@dissectible_content_id int 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;
	SELECT DISTINCT(ds.TermNumber) 
	FROM DissectibleStructures  ds 
	inner join
	DissectibleContent dc 
	ON 
	dc.DissectibleStructureGroupId =ds.DissectibleStructureGroupId
	WHERE dc.Id =@dissectible_content_id 
	ORDER BY ds.TermNumber

END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetTermList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get [DA_GetTermList] from VocabTerms
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetTermList] 
	-- Add the parameters for the stored procedure here
@lexicon_id varchar(20),
@structure_group_id int,
@layer_number_internal int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	select * from VocabTerms where TermNumber in(
	select distinct TermNumber from DissectibleStructures 
	where DissectibleStructureGroupId = @structure_group_id and LayerNumberInternal=@layer_number_internal)
	and VocabLexiconId=@lexicon_id order by TermText
	
END
GO
/****** Object:  StoredProcedure [dbo].[DA_GetStructureList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 25/4/2009
-- Description:	Get Term Number from DissectibleStructures
-- =============================================
ALTER  PROCEDURE [dbo].[DA_GetStructureList] 
	-- Add the parameters for the stored procedure here
@dissectible_struct_group_id int,
@vocab_lexicon_id int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT
	(
	SELECT distinct it.TermNumber as tn,vt.TermText as tt
    FROM [DissectibleStructures] AS it
	inner join vocabterms vt on it.TermNumber = vt.TermNumber
	WHERE it.DissectibleStructureGroupId=@dissectible_struct_group_id 
	and vt.VocabLexiconId=@vocab_lexicon_id
	order by vt.TermText
	FOR XML AUTO) 
	AS XMLString 
END
GO
/****** Object:  StoredProcedure [dbo].[GetAllEditionForLicense]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==============================================================================================
-- Author:		Magic Software
-- ALTER date: 22-May-2009
-- Description:	To fetch all the editions mapped with the license as well as the rest of editions
-- ==============================================================================================

ALTER PROCEDURE [dbo].[GetAllEditionForLicense]
@iLicenseId int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT OFF
	
	SELECT Edition.Id, Edition.Title, (CASE WHEN LicenseToEdition.Id IS NOT NULL THEN 1 ELSE 0 END) as IsSelected, 
	ISNULL(LicenseToEdition.TotalLogins,'') as TotalLogins 
	FROM 
	Edition LEFT JOIN LicenseToEdition 
	ON Edition.Id = LicenseToEdition.EditionId AND LicenseToEdition.LicenseId = @iLicenseId 
	WHERE Edition.IsActive = 1 
	ORDER BY Edition.Priority
	
END
GO
/****** Object:  StoredProcedure [dbo].[GetAttributeValueList]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic
-- ALTER date: 22/4/2009
-- Description:	Get the attribute value list for a particular content attribute type
-- =============================================
ALTER PROCEDURE [dbo].[GetAttributeValueList] 
	-- Add the parameters for the stored procedure here
	-- ContentTypeId is mandatory
	-- AttributeTypeId is mandatory
	-- To fix - needs to be sent as a parameter from the calling area
	@iContentTypeId int = 5,
	@iAttributeTypeId int = 4
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    -- Insert statements for procedure here
	SELECT ca.AttributeValue FROM dbo.ContentAttribute ca
	WHERE ca.Id in 
	(SELECT DISTINCT cdtam.ContentAttributeId FROM ContentDescriptor cd
	inner join dbo.ContentDescriptorToAttributeMap cdtam ON cdtam.ContentDescriptorId = cd.Id
	WHERE cd.ContentTypeId = @iContentTypeId and cd.IsActive='Y') and ca.AttributeTypeId = @iAttributeTypeId
END
GO
/****** Object:  StoredProcedure [dbo].[GetDiscountReport]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Author:		Magic Software
-- ALTER date: 23-Dec-2009
-- Description:	To get the report of discounts on the basis of given parameters
-- ====================================================
ALTER PROCEDURE [dbo].[GetDiscountReport] 
	-- Add the parameters for the stored procedure here
	@sStartDate VARCHAR(20) = '', @sEndDate VARCHAR(20) = '', @intDiscountID INT, 
	@sAccoutNumber VARCHAR(16)=''
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @dtStartDate DATETIME, @dtEndDate DATETIME
	
	-- convert the datatype of startdate & enddate parameter to datetime
	SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
	SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
	IF LEN(@sAccoutNumber) > 0
	BEGIN
		SELECT Discount.DiscountCode, Discount.Percentage, CONVERT(VARCHAR(10),Discount.StartDate,101) as StartDate, 
			CONVERT(VARCHAR(10),Discount.EndDate,101) as EndDate, 
			(CASE Discount.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) AS DiscountStatus,
			COUNT(DiscountToLicense.LicenseId) AS TotalLicenses
			FROM Discount INNER JOIN DiscountToLicense ON Discount.Id = DiscountToLicense.DiscountId
			INNER JOIN License ON License.Id = DiscountToLicense.LicenseId
			WHERE Discount.StartDate >= (CASE WHEN LEN(@sStartDate) > 0 THEN @dtStartDate ELSE Discount.StartDate END)
			AND Discount.EndDate <= (CASE WHEN LEN(@sEndDate) > 0 THEN @dtEndDate ELSE Discount.EndDate END)
			AND Discount.Id = (CASE WHEN @intDiscountID > 0 THEN @intDiscountID ELSE Discount.Id END) 
			AND License.AccountNumber = @sAccoutNumber
			GROUP BY Discount.DiscountCode, Discount.Percentage, Discount.StartDate, Discount.EndDate, Discount.IsActive	
	END
	ELSE
	BEGIN
		SELECT Discount.DiscountCode, Discount.Percentage, CONVERT(VARCHAR(10),Discount.StartDate,101) as StartDate, 
			CONVERT(VARCHAR(10),Discount.EndDate,101) as EndDate, 
			(CASE Discount.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) AS DiscountStatus,
			COUNT(DiscountToLicense.LicenseId) AS TotalLicenses
			FROM Discount LEFT JOIN DiscountToLicense ON Discount.Id = DiscountToLicense.DiscountId
			WHERE Discount.StartDate >= (CASE WHEN LEN(@sStartDate) > 0 THEN @dtStartDate ELSE Discount.StartDate END)
			AND Discount.EndDate <= (CASE WHEN LEN(@sEndDate) > 0 THEN @dtEndDate ELSE Discount.EndDate END)
			AND Discount.Id = (CASE WHEN @intDiscountID > 0 THEN @intDiscountID ELSE Discount.Id END) 
			GROUP BY Discount.DiscountCode, Discount.Percentage, Discount.StartDate, Discount.EndDate, Discount.IsActive		
	END
END
GO
/****** Object:  StoredProcedure [dbo].[GetContentAttributeData]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetContentAttributeData] @iContentTypeId INT
AS
BEGIN TRY
	DECLARE @cGetID CURSOR
	DECLARE @cGetProcessedData CURSOR
	DECLARE @iContentDescriptorId INT
	DECLARE @sContentAttributeType VARCHAR(256)
	DECLARE	@iImageContentId INT
	DECLARE @sImageContentTitle VARCHAR(256)
	DECLARE @sImageFileName VARCHAR(50)
	DECLARE @sThumbnailFileName VARCHAR(50)
	DECLARE @sContentAttributeId VARCHAR(8000)
	DECLARE @sContentAttributeValue NVARCHAR(MAX)
	DECLARE @iPrevContentDescriptorId INT
	DECLARE @sAttributeTypeColumnName VARCHAR(3)
	DECLARE @SQL NVARCHAR(MAX)

	SET @sContentAttributeValue = ''
	SET @iPrevContentDescriptorId = 0

	-- ALTER temporary tables
	CREATE TABLE #DataProcess
	(
		ContentDescriptorId INT,
		ContentAttributeType VARCHAR(50),
		ImageContentId INT,
		ImageContentTitle VARCHAR(256),
		ImageFileName VARCHAR(50),
		ThumbnailFileName VARCHAR(50),
		ContentAttributeIds VARCHAR(8000),
		ContentAttributeValues NVARCHAR(MAX)
	)

	CREATE TABLE #XMLData
	(
		id INT,
		icId INT,
		tl VARCHAR(256),
		cp VARCHAR(50),
		ti VARCHAR(50),
		bs NVARCHAR(MAX),
		br NVARCHAR(MAX),
		vo NVARCHAR(MAX),
		ms NVARCHAR(MAX),
		im NVARCHAR(MAX),
		sm NVARCHAR(MAX),
		lsm NVARCHAR(MAX),
		gr VARCHAR(6)
	)

	-- define the forward only, read-only cursor 
	SET @cGetID = CURSOR FAST_FORWARD 
	FOR
	SELECT cd.Id, cat.Name, ic.Id as icId, ic.Title, ic.FileName, ic.ThumbnailFileName, left(aval,LEN(aval)-1) as aval
	 FROM 
	  ContentDescriptor cd 
	  INNER JOIN ImageContent ic ON ic.ContentDescriptorId = cd.id AND cd.isActive = 'Y'
	  INNER JOIN ContentDescriptorToAttributeMap cdmap ON cd.id = cdmap.ContentDescriptorId AND cd.ContentTypeId = @iContentTypeId
	  INNER JOIN ContentAttribute ca ON ca.id = cdmap.ContentAttributeId
	  INNER JOIN ContentAttributeType cat ON ca.AttributeTypeId = cat.id AND 
	  cat.Name IN ('Body System','Body Region','View Orientation','Medical Specialty','Gender','Image Type','Description','Long Description') 
	  CROSS APPLY (SELECT CAST(ca1.id as VARCHAR)+','
	 FROM 
	  ContentDescriptorToAttributeMap cdmap1 
	  INNER JOIN ContentAttribute ca1 ON ca1.id = cdmap1.ContentAttributeId
	  INNER JOIN ContentAttributeType cat1 ON ca1.AttributetypeId = cat1.id AND cat1.id=cat.id
	  WHERE cdmap1.ContentDescriptorId = cd.id FOR XML PATH('')) pre_trimmed(aval)
	  GROUP BY cd.id,cat.name,ic.Id,ic.Title,ic.FileName,ic.ThumbnailFileName,aval 

	-- open & fetch the cursor variables into the local variables
	OPEN @cGetID 
	FETCH NEXT FROM @cGetID INTO @iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle,
		@sImageFileName, @sThumbnailFileName, @sContentAttributeId

	-- start of while loop
	 WHILE @@FETCH_STATUS = 0
	  BEGIN
		SET @sContentAttributeValue = ''
		
		SELECT @sContentAttributeValue = CONVERT(NVARCHAR(MAX), ContentAttribute.AttributeValue) + ', ' +@sContentAttributeValue
		FROM ContentAttribute WHERE ContentAttribute.Id IN (SELECT item from dbo.fnSplit(@sContentAttributeId,',')) 
		order by CONVERT(NVARCHAR(MAX), ContentAttribute.AttributeValue) DESC
		IF LEN(@sContentAttributeValue) > 0
		BEGIN
			SET @sContentAttributeValue = SUBSTRING(@sContentAttributeValue,1,LEN(@sContentAttributeValue)-1)
		END
		INSERT INTO #DataProcess(ContentDescriptorId, ContentAttributeType, ImageContentId, ImageContentTitle,
			ImageFileName, ThumbnailFileName, ContentAttributeIds, ContentAttributeValues)	
		VALUES(@iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle, 
			@sImageFileName, @sThumbnailFileName, @sContentAttributeId, @sContentAttributeValue)

		FETCH NEXT FROM @cGetID INTO @iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle,
		@sImageFileName, @sThumbnailFileName, @sContentAttributeId
	-- end of while loop
	END
	-- close the cursor to free up resources
	CLOSE @cGetID
	DEALLOCATE @cGetID

	SET @cGetProcessedData = CURSOR FAST_FORWARD 
	FOR
	SELECT ContentDescriptorId, ContentAttributeType, ImageContentId, ImageContentTitle, ImageFileName, 
		ThumbnailFileName, ContentAttributeIds, ContentAttributeValues FROM #DataProcess ORDER BY ContentDescriptorId
	
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetProcessedData 
	FETCH NEXT FROM @cGetProcessedData INTO @iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle, 
		@sImageFileName, @sThumbnailFileName, @sContentAttributeId, @sContentAttributeValue
	WHILE @@FETCH_STATUS = 0
	  BEGIN
		SET @SQL = ''
		SET @sAttributeTypeColumnName = CASE @sContentAttributeType WHEN 'Body System' THEN 'bs'
			WHEN 'Body Region' THEN 'br'
			WHEN 'View Orientation' THEN 'vo'
			WHEN 'Medical Specialty' THEN 'ms'
			WHEN 'Gender' THEN 'gr'
			WHEN 'Image Type' THEN 'im'
			WHEN 'Description' THEN 'sm'
			WHEN 'Long Description' THEN 'lsm'
			END
		-- to handle the single quotes in the attribute value	
		SET @sContentAttributeValue = REPLACE(@sContentAttributeValue,'''','''''')
		SET @sImageContentTitle = REPLACE(@sImageContentTitle,'''','''''')
		
		IF @iContentDescriptorId != @iPrevContentDescriptorId
		BEGIN
			SET @SQL = 'INSERT INTO #XMLData (id, icId, tl, cp, ti, '+@sAttributeTypeColumnName+')
			VALUES('+CONVERT(VARCHAR(20),@iContentDescriptorId)+','''+CONVERT(VARCHAR(20),@iImageContentId)+''','''+@sImageContentTitle+'''
			,'''+@sImageFileName+''','''+@sThumbnailFileName+''','''+@sContentAttributeValue+''')'
		END
		ELSE
		BEGIN
			SET @SQL = 'UPDATE #XMLData SET '+@sAttributeTypeColumnName+' = '''+@sContentAttributeValue+''' WHERE id = '+CONVERT(VARCHAR(20),@iContentDescriptorId)
		END
		
		-- to execute the dynamic query
		EXEC sp_executesql @SQL
		SET @iPrevContentDescriptorId = @iContentDescriptorId
		
		FETCH NEXT FROM @cGetProcessedData INTO @iContentDescriptorId, @sContentAttributeType, @iImageContentId, @sImageContentTitle, 
		@sImageFileName, @sThumbnailFileName, @sContentAttributeId, @sContentAttributeValue

	END
	SELECT * FROM #XMLData FOR XML RAW ('it')
	-- close the cursor to free up resources
	CLOSE @cGetProcessedData
	DEALLOCATE @cGetProcessedData
	-- drop the temporary tables
	DROP TABLE #XMLData
	DROP TABLE #DataProcess
END TRY
BEGIN CATCH
		SELECT Error_Message() as SPStatus
END CATCH
GO
/****** Object:  StoredProcedure [dbo].[GetCancelledLicenses]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCancelledLicenses] 
	-- Add the parameters for the stored procedure here
	@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), @iLicenseTypeId tinyint,
	@iAccountTypeId tinyint , @sZip varchar(20) = '', @iStateId int, @iCountryId int
AS
BEGIN
	
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME
	DECLARE @cGetLicenseID CURSOR
	DECLARE @iLicenseId INT
	DECLARE @iLicenseSubscriptionDetail INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @dtStartDate DATETIME
	DECLARE @dtEndDate DATETIME
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @mSubscriptionPrice MONEY
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @sEdition VARCHAR(200)
	DECLARE @iCardNumber INT
	

	-- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
	
	-- ALTER a temporary table to store the desired results of cancelled licenses on the basis of parameter
	CREATE TABLE #CancelledLicenseReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		InstitutionName VARCHAR(100),
		Edition VARCHAR(200),
		ValidFrom DATETIME,
		ValidThrough DATETIME,
		LicenseCreationDate DATETIME,
		Price MONEY,
		AccountType VARCHAR(50),
		CardNumber INT
	)

	-- define the forward only, read-only cursor 
	SET @cGetLicenseID = CURSOR FAST_FORWARD 
	FOR 
		SELECT LicenseSubscriptionDetail.LicenseId, MAX(LicenseSubscriptionDetail.Id) 
		FROM LicenseSubscriptionDetail 
		INNER JOIN License ON License.Id = LicenseSubscriptionDetail.LicenseId
		WHERE (License.CancellationDate BETWEEN @dtFromDate AND @dtToDate) AND
		(TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END)) 
		AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END))
		GROUP BY LicenseSubscriptionDetail.LicenseId 
		--HAVING (MAX(SubscriptionValidThrough) BETWEEN @dtFromDate AND @dtToDate) 
	
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetLicenseID 
		FETCH NEXT FROM @cGetLicenseID INTO @iLicenseId, @iLicenseSubscriptionDetail
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			SET @sEdition = ''
			-- fetch the accountnumber, licenseename, licensetype, startdate, enddate, subscriptionprice, accountype of a license
			SELECT @sAccountNumber = AccountNumber, @sLicenseeName = LicenseeName, @sLicenseType = LicenseType,
			@sInstitutionName = InstitutionName,
			@dtStartDate = SubscriptionValidFrom, @dtEndDate = SubscriptionValidThrough,
			@dtLicenseCreationDate = CreationDate,
			@mSubscriptionPrice = TotalAmount, @sAccountType = AccountType, @iCardNumber = CardNumber
			FROM 
			(
				SELECT AccountNumber, (LicenseeFirstName+' '+LicenseeLastName) as LicenseeName,
				LicenseType.Title as LicenseType, License.InstitutionName,
				AccountType.Title as AccountType, LicenseSubscriptionDetail.TotalAmount,
				LicenseSubscriptionDetail.SubscriptionValidFrom, LicenseSubscriptionDetail.SubscriptionValidThrough, 
				License.CreationDate,
				DATEDIFF(dd,GETDATE(),License.CancellationDate) as DaysRemaining, (CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber
				FROM License 
				INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id 
				INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id 
				INNER JOIN State ON License.StateId = State.Id
				INNER JOIN Country ON License.CountryId = Country.Id 
				INNER JOIN LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId 
				WHERE License.IsActive = 0 
				AND License.LicenseTypeId = (CASE WHEN @iLicenseTypeId > 0 THEN @iLicenseTypeId ELSE License.LicenseTypeId END)
				AND License.AccountTypeId = (CASE WHEN @iAccountTypeId > 0 THEN @iAccountTypeId ELSE License.AccountTypeId END)
				AND State.Id = (CASE WHEN @iStateId > 0 THEN @iStateId ELSE State.Id END)
				AND Country.Id = (CASE WHEN @iCountryId > 0 THEN @iCountryId ELSE Country.Id END)
				AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
				AND LicenseSubscriptionDetail.Id =  @iLicenseSubscriptionDetail
				AND License.LicenseTypeId <> 5 
			) t1 

			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				-- fetch all the editions mapped as a string with a license
				SELECT @sEdition = Edition.Title + '; ' + @sEdition 
				FROM LicenseToEdition INNER JOIN Edition 
				ON LicenseToEdition.EditionId = Edition.Id 
				WHERE LicenseToEdition.LicenseId = @iLicenseId
				-- remove the trailing comma-separator from the edition-string
				--AMI SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1);
				IF LEN(@sEdition)> 1
						-- remove the trailing comma-separator from the edition-string
						SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
					ELSE
						SET @sEdition = @sEdition
				
				-- insert into the temporary table
				INSERT INTO #CancelledLicenseReport 
				(AccountNumber,LicenseeName,LicenseType,InstitutionName,Edition,ValidFrom,ValidThrough,LicenseCreationDate,Price,AccountType,CardNumber) 
				VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sInstitutionName,@sEdition,@dtStartDate,@dtEndDate,@dtLicenseCreationDate,@mSubscriptionPrice,@sAccountType,@iCardNumber)
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseID INTO @iLicenseId,@iLicenseSubscriptionDetail
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetLicenseID
	DEALLOCATE @cGetLicenseID

	-- Selecting the desired result from temporary table
	SELECT AccountNumber, LicenseeName, LicenseType,InstitutionName,AccountType, Edition, CONVERT(VARCHAR,ValidFrom,101) as StartDate,
	 CONVERT(VARCHAR,ValidThrough,101) as EndDate,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate,  CONVERT(NUMERIC(14,2),Price) as SubscriptionPrice, CardNumber
	FROM #CancelledLicenseReport ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #CancelledLicenseReport
END
GO
/****** Object:  StoredProcedure [dbo].[GetCustomerSummary_bkp]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCustomerSummary_bkp] 
	-- Add the parameters for the stored procedure here
	@sAccoutNumber varchar(50)='', @sLicenseeFullName varchar(100)='', @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), 
	@iLicenseType tinyint, @iAccountType tinyint, @sZip varchar(20) = '', @iState int, 
	@iCountry int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON
	DECLARE @cGetLicenseDetails CURSOR
	DECLARE @iLicenseId INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @iLicenseTypeId TINYINT
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @dtStartDate DATETIME
	DECLARE @dtEndDate DATETIME
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @iAccountTypeId TINYINT
	DECLARE @sLicenseStatus VARCHAR(8)
	DECLARE @sEdition VARCHAR(200)
	DECLARE @bExists bit
    DECLARE @sLicenseState VARCHAR(50)
	DECLARE @sLicenseZip VARCHAR(20)
	DECLARE @sLicenseCountry VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @mSubscriptionPrice MONEY
	DECLARE @iLicenseSubscriptionId INT
	DECLARE @sEmailId VARCHAR(100)
	DECLARE @iCardNumber INT

	-- ALTER a temporary table to store the desired results of licenses on the basis of parameter
	CREATE TABLE #CustomerReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		Edition VARCHAR(200),
		Email VARCHAR(100),
		ValidFrom DATETIME,
		ValidThrough DATETIME,
		AccountType VARCHAR(50),
		LicenseStatus VARCHAR(8),
		Price MONEY,
        LicenseState VARCHAR(50),
		LicenseZip VARCHAR(20),
		LicenseCountry VARCHAR(50),
		InstitutionName VARCHAR(100),
		LicenseCreationDate DATETIME,
		CardNumber INT
	)

	SET @sLicenseeFullName = REPLACE(@sLicenseeFullName,' ',' OR ')
	
	-- define the forward only, read-only cursor 
	SET @cGetLicenseDetails = CURSOR FAST_FORWARD 
	FOR 
		SELECT License.Id, License.AccountNumber, (License.LicenseeFirstName+' '+License.LicenseeLastName), 
		License.LicenseTypeId, License.AccountTypeId, License.EmailId,
		(CASE License.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
		State.StateName, License.Zip, Country.CountryName,
		License.InstitutionName,License.CreationDate,
		(CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber
		FROM License
        INNER JOIN State ON License.StateId = State.Id
		INNER JOIN Country ON License.CountryId = Country.Id
 		WHERE 
		License.AccountNumber = (CASE WHEN LEN(@sAccoutNumber)>0 THEN @sAccoutNumber ELSE License.AccountNumber END)
		AND License.LicenseTypeId = (CASE WHEN @iLicenseType > 0 THEN @iLicenseType ELSE License.LicenseTypeId END)
		AND License.AccountTypeId = (CASE WHEN @iAccountType > 0 THEN @iAccountType ELSE License.AccountTypeId END)
		AND State.Id = (CASE WHEN @iState > 0 THEN @iState ELSE State.Id END)
		AND Country.Id = (CASE WHEN @iCountry > 0 THEN @iCountry ELSE Country.Id END)
		AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
					
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetLicenseDetails 
		FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, 
		@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber 

		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
		
			SET @sEdition = ''
			SET @bExists = 1
			-- fetch the latest license start/end date of a license on the basis of Subscription Start & End price if any
			SELECT @dtStartDate = MAX(LicenseSubscriptionDetail.SubscriptionValidFrom),
			@dtEndDate = MAX(LicenseSubscriptionDetail.SubscriptionValidThrough),
			@iLicenseSubscriptionId = MAX(LicenseSubscriptionDetail.Id)
			FROM LicenseSubscriptionDetail 
			WHERE LicenseSubscriptionDetail.LicenseId = @iLicenseId
			AND (TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END)) 
			AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END))
			GROUP BY LicenseSubscriptionDetail.LicenseId
			
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN

				-- check whether the name of licensse matches the name entered by user

				IF LEN(@sLicenseeFullName) > 0
					BEGIN
						SELECT @bExists = 1 
						FROM License 
						WHERE Id = @iLicenseId AND (LicenseeFirstName LIKE '%'+@sLicenseeFullName+'%' OR LicenseeLastName LIKE '%'+@sLicenseeFullName+'%') --CONTAINS((LicenseeFirstName,LicenseeLastName)
						IF @@Rowcount = 0
							BEGIN
								SET @bExists = 0
							END				
					END

				-- check whether the above query returns any row
				IF @bExists = 1
				BEGIN
					-- fetch the licensetype of the license
					SELECT @sLicenseType = LicenseType.Title FROM LicenseType 
					WHERE LicenseType.Id = @iLicenseTypeId
					-- fetch the accounttype of the license
					SELECT @sAccountType = AccountType.Title FROM AccountType 
					WHERE AccountType.Id = @iAccountTypeId

					-- fetch all the editions mapped as a string with a license
					SELECT @sEdition = Edition.Title + '; ' + @sEdition 
					FROM LicenseToEdition INNER JOIN Edition 
					ON LicenseToEdition.EditionId = Edition.Id 
					WHERE LicenseToEdition.LicenseId = @iLicenseId

					-- remove the trailing comma-separator from the edition-string
					SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
					
					-- fetch the price of the license
					SELECT @mSubscriptionPrice = TotalAmount FROM LicenseSubscriptionDetail 
					WHERE Id = @iLicenseSubscriptionId

					-- insert into the temporary table
					INSERT INTO #CustomerReport 
					(AccountNumber, LicenseeName, LicenseType, Edition, Email, ValidFrom, ValidThrough, AccountType, LicenseStatus, Price, LicenseState, LicenseZip, LicenseCountry, InstitutionName, LicenseCreationDate, CardNumber) 
					VALUES(@sAccountNumber, @sLicenseeName, @sLicenseType, @sEdition, @sEmailId, @dtStartDate, @dtEndDate, @sAccountType, @sLicenseStatus, @mSubscriptionPrice, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber)
				END
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, 
			@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry, @sInstitutionName, @dtLicenseCreationDate, @iCardNumber
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetLicenseDetails
	DEALLOCATE @cGetLicenseDetails

	-- Selecting the desired result from temporary table
	SELECT AccountNumber, LicenseeName, LicenseType, AccountType, Edition, Email, CONVERT(VARCHAR,ValidFrom,101) as StartDate, 
	CONVERT(VARCHAR,ValidThrough,101) as EndDate, LicenseStatus, CONVERT(NUMERIC(14,2),Price) as Price, 
	LicenseZip, LicenseState, LicenseCountry,InstitutionName, CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate , CardNumber 
	FROM #CustomerReport ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #CustomerReport
END
GO
/****** Object:  StoredProcedure [dbo].[GetCustomerSummary_25042017]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCustomerSummary_25042017] 
	-- Add the parameters for the stored procedure here
	@sAccoutNumber varchar(50)='', @sLicenseeFullName varchar(100)='', @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), 
	@iLicenseType tinyint, @iAccountType tinyint, @sZip varchar(20) = '', @iState int, 
	@iCountry int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON
	DECLARE @cGetLicenseDetails CURSOR
	DECLARE @iLicenseId INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @iLicenseTypeId TINYINT
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @dtStartDate DATETIME
	DECLARE @dtEndDate DATETIME
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @iAccountTypeId TINYINT
	DECLARE @sLicenseStatus VARCHAR(8)
	DECLARE @sEdition VARCHAR(200)
	DECLARE @bExists bit
    DECLARE @sLicenseState VARCHAR(50)
	DECLARE @sLicenseZip VARCHAR(20)
	DECLARE @sLicenseCountry VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @mSubscriptionPrice MONEY
	DECLARE @iLicenseSubscriptionId INT
	DECLARE @sEmailId VARCHAR(100)
	DECLARE @iCardNumber INT

	-- ALTER a temporary table to store the desired results of licenses on the basis of parameter
	CREATE TABLE #CustomerReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		Edition VARCHAR(200),
		Email VARCHAR(100),
		ValidFrom DATETIME,
		ValidThrough DATETIME,
		AccountType VARCHAR(50),
		LicenseStatus VARCHAR(8),
		Price MONEY,
        LicenseState VARCHAR(50),
		LicenseZip VARCHAR(20),
		LicenseCountry VARCHAR(50),
		InstitutionName VARCHAR(100),
		LicenseCreationDate DATETIME,
		CardNumber INT
	)

	SET @sLicenseeFullName = REPLACE(@sLicenseeFullName,' ',' OR ')
	
	-- define the forward only, read-only cursor 
	SET @cGetLicenseDetails = CURSOR FAST_FORWARD 
	FOR 
		SELECT License.Id, License.AccountNumber, (License.LicenseeFirstName+' '+License.LicenseeLastName), 
		License.LicenseTypeId, License.AccountTypeId, License.EmailId,
		(CASE License.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
		State.StateName, License.Zip, Country.CountryName,
		License.InstitutionName,License.CreationDate,
		(CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber
		FROM License  WITH (NOLOCK)
        INNER JOIN  State WITH (NOLOCK) ON License.StateId = State.Id
		INNER JOIN Country WITH (NOLOCK) ON License.CountryId = Country.Id
 		WHERE 
		License.AccountNumber = (CASE WHEN LEN(@sAccoutNumber)>0 THEN @sAccoutNumber ELSE License.AccountNumber END)
		AND License.LicenseTypeId = (CASE WHEN @iLicenseType > 0 THEN @iLicenseType ELSE License.LicenseTypeId END)
		AND License.AccountTypeId = (CASE WHEN @iAccountType > 0 THEN @iAccountType ELSE License.AccountTypeId END)
		AND State.Id = (CASE WHEN @iState > 0 THEN @iState ELSE State.Id END)
		AND Country.Id = (CASE WHEN @iCountry > 0 THEN @iCountry ELSE Country.Id END)
		AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
					
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetLicenseDetails 
		FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, 
		@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber 

		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
		
			SET @sEdition = ''
			SET @bExists = 1
			-- fetch the latest license start/end date of a license on the basis of Subscription Start & End price if any
			SELECT @dtStartDate = MAX(LicenseSubscriptionDetail.SubscriptionValidFrom),
			@dtEndDate = MAX(LicenseSubscriptionDetail.SubscriptionValidThrough),
			@iLicenseSubscriptionId = MAX(LicenseSubscriptionDetail.Id)
			FROM LicenseSubscriptionDetail  WITH (NOLOCK)
			WHERE LicenseSubscriptionDetail.LicenseId = @iLicenseId
			AND (TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END)) 
			AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END))
			GROUP BY LicenseSubscriptionDetail.LicenseId
			
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN

				-- check whether the name of licensse matches the name entered by user

				IF LEN(@sLicenseeFullName) > 0
					BEGIN
						SELECT @bExists = 1 
						FROM License WITH (NOLOCK)
						WHERE Id = @iLicenseId AND (LicenseeFirstName LIKE '%'+@sLicenseeFullName+'%' OR LicenseeLastName LIKE '%'+@sLicenseeFullName+'%') --CONTAINS((LicenseeFirstName,LicenseeLastName)
						IF @@Rowcount = 0
							BEGIN
								SET @bExists = 0
							END				
					END

				-- check whether the above query returns any row
				IF @bExists = 1
				BEGIN
					-- fetch the licensetype of the license
					SELECT @sLicenseType = LicenseType.Title FROM LicenseType WITH (NOLOCK)
					WHERE LicenseType.Id = @iLicenseTypeId
					-- fetch the accounttype of the license
					SELECT @sAccountType = AccountType.Title FROM AccountType  WITH (NOLOCK)
					WHERE AccountType.Id = @iAccountTypeId

					-- fetch all the editions mapped as a string with a license
					SELECT @sEdition = Edition.Title + '; ' + @sEdition 
					FROM LicenseToEdition  WITH (NOLOCK) INNER JOIN Edition WITH (NOLOCK)
					ON LicenseToEdition.EditionId = Edition.Id 
					WHERE LicenseToEdition.LicenseId = @iLicenseId

					IF LEN(@sEdition)> 1
						-- remove the trailing comma-separator from the edition-string
						SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
					ELSE
						SET @sEdition = @sEdition
					
					-- fetch the price of the license
					SELECT @mSubscriptionPrice = TotalAmount FROM LicenseSubscriptionDetail  WITH (NOLOCK)
					WHERE Id = @iLicenseSubscriptionId

					-- insert into the temporary table
					INSERT INTO #CustomerReport 
					(AccountNumber, LicenseeName, LicenseType, Edition, Email, ValidFrom, ValidThrough, AccountType, LicenseStatus, Price, LicenseState, LicenseZip, LicenseCountry, InstitutionName, LicenseCreationDate, CardNumber) 
					VALUES(@sAccountNumber, @sLicenseeName, @sLicenseType, @sEdition, @sEmailId, @dtStartDate, @dtEndDate, @sAccountType, @sLicenseStatus, @mSubscriptionPrice, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber)
				END
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, 
			@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry, @sInstitutionName, @dtLicenseCreationDate, @iCardNumber
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetLicenseDetails
	DEALLOCATE @cGetLicenseDetails

	-- Selecting the desired result from temporary table
	SELECT AccountNumber, LicenseeName, LicenseType, AccountType, Edition, Email, CONVERT(VARCHAR,ValidFrom,101) as StartDate, 
	CONVERT(VARCHAR,ValidThrough,101) as EndDate, LicenseStatus, CONVERT(NUMERIC(14,2),Price) as Price, 
	LicenseZip, LicenseState, LicenseCountry,InstitutionName, CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate , CardNumber 
	FROM #CustomerReport  WITH (NOLOCK) ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #CustomerReport
END
GO
/****** Object:  StoredProcedure [dbo].[GetCustomerSummary]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCustomerSummary] 
	-- Add the parameters for the stored procedure here
	@sAccoutNumber varchar(50)='', @sLicenseeFullName varchar(100)='', @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), 
	@iLicenseType tinyint, @iAccountType tinyint, @sZip varchar(20) = '', @iState int, 
	@iCountry int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON
	DECLARE @cGetLicenseDetails CURSOR
	DECLARE @iLicenseId INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @iLicenseTypeId TINYINT
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @dtStartDate DATETIME
	DECLARE @dtEndDate DATETIME
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @iAccountTypeId TINYINT
	DECLARE @sLicenseStatus VARCHAR(8)
	DECLARE @sEdition VARCHAR(200)
	DECLARE @bExists bit
    DECLARE @sLicenseState VARCHAR(50)
	DECLARE @sLicenseZip VARCHAR(20)
	DECLARE @sLicenseCountry VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @mSubscriptionPrice MONEY
	DECLARE @iLicenseSubscriptionId INT
	DECLARE @sEmailId VARCHAR(100)
	DECLARE @iCardNumber INT

	-- ALTER a temporary table to store the desired results of licenses on the basis of parameter
	CREATE TABLE #CustomerReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		Edition VARCHAR(200),
		Email VARCHAR(100),
		ValidFrom DATETIME,
		ValidThrough DATETIME,
		AccountType VARCHAR(50),
		LicenseStatus VARCHAR(8),
		Price MONEY,
        LicenseState VARCHAR(50),
		LicenseZip VARCHAR(20),
		LicenseCountry VARCHAR(50),
		InstitutionName VARCHAR(100),
		LicenseCreationDate DATETIME,
		CardNumber INT
	)

	SET @sLicenseeFullName = REPLACE(@sLicenseeFullName,' ',' OR ')
	
	-- define the forward only, read-only cursor 
	SET @cGetLicenseDetails = CURSOR FAST_FORWARD 
	FOR 
		SELECT License.Id, License.AccountNumber, (License.LicenseeFirstName+' '+License.LicenseeLastName), 
		License.LicenseTypeId, License.AccountTypeId, License.EmailId,
		(CASE License.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
		State.StateName, License.Zip, Country.CountryName,
		License.InstitutionName,License.CreationDate,
		(CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber
		FROM License
        INNER JOIN State ON License.StateId = State.Id
		INNER JOIN Country ON License.CountryId = Country.Id
 		WHERE 
		License.AccountNumber = (CASE WHEN LEN(@sAccoutNumber)>0 THEN @sAccoutNumber ELSE License.AccountNumber END)
		AND License.LicenseTypeId = (CASE WHEN @iLicenseType > 0 THEN @iLicenseType ELSE License.LicenseTypeId END)
		AND License.AccountTypeId = (CASE WHEN @iAccountType > 0 THEN @iAccountType ELSE License.AccountTypeId END)
		AND State.Id = (CASE WHEN @iState > 0 THEN @iState ELSE State.Id END)
		AND Country.Id = (CASE WHEN @iCountry > 0 THEN @iCountry ELSE Country.Id END)
		AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
					
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetLicenseDetails 
		FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, 
		@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber 

		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
		
			SET @sEdition = ''
			SET @bExists = 1
			-- fetch the latest license start/end date of a license on the basis of Subscription Start & End price if any
			SELECT @dtStartDate = MAX(LicenseSubscriptionDetail.SubscriptionValidFrom),
			@dtEndDate = MAX(LicenseSubscriptionDetail.SubscriptionValidThrough),
			@iLicenseSubscriptionId = MAX(LicenseSubscriptionDetail.Id)
			FROM LicenseSubscriptionDetail 
			WHERE LicenseSubscriptionDetail.LicenseId = @iLicenseId
			AND (TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END)) 
			AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END))
			GROUP BY LicenseSubscriptionDetail.LicenseId
			
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN

				-- check whether the name of licensse matches the name entered by user

				IF LEN(@sLicenseeFullName) > 0
					BEGIN
						SELECT @bExists = 1 
						FROM License 
						WHERE Id = @iLicenseId AND (LicenseeFirstName LIKE '%'+@sLicenseeFullName+'%' OR LicenseeLastName LIKE '%'+@sLicenseeFullName+'%') --CONTAINS((LicenseeFirstName,LicenseeLastName)
						IF @@Rowcount = 0
							BEGIN
								SET @bExists = 0
							END				
					END

				-- check whether the above query returns any row
				IF @bExists = 1
				BEGIN
					-- fetch the licensetype of the license
					SELECT @sLicenseType = LicenseType.Title FROM LicenseType 
					WHERE LicenseType.Id = @iLicenseTypeId
					-- fetch the accounttype of the license
					SELECT @sAccountType = AccountType.Title FROM AccountType 
					WHERE AccountType.Id = @iAccountTypeId

					-- fetch all the editions mapped as a string with a license
					SELECT @sEdition = Edition.Title + '; ' + @sEdition 
					FROM LicenseToEdition INNER JOIN Edition 
					ON LicenseToEdition.EditionId = Edition.Id 
					WHERE LicenseToEdition.LicenseId = @iLicenseId

					-- remove the trailing comma-separator from the edition-string
					-- AMI SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
						IF LEN(@sEdition)> 1
						-- remove the trailing comma-separator from the edition-string
						SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
					ELSE
						SET @sEdition = @sEdition

					
					-- fetch the price of the license
					SELECT @mSubscriptionPrice = TotalAmount FROM LicenseSubscriptionDetail 
					WHERE Id = @iLicenseSubscriptionId

					-- insert into the temporary table
					INSERT INTO #CustomerReport 
					(AccountNumber, LicenseeName, LicenseType, Edition, Email, ValidFrom, ValidThrough, AccountType, LicenseStatus, Price, LicenseState, LicenseZip, LicenseCountry, InstitutionName, LicenseCreationDate, CardNumber) 
					VALUES(@sAccountNumber, @sLicenseeName, @sLicenseType, @sEdition, @sEmailId, @dtStartDate, @dtEndDate, @sAccountType, @sLicenseStatus, @mSubscriptionPrice, @sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName,@dtLicenseCreationDate, @iCardNumber)
				END
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseDetails INTO @iLicenseId, @sAccountNumber, @sLicenseeName, 
			@iLicenseTypeId, @iAccountTypeId, @sEmailId, @sLicenseStatus, @sLicenseState, @sLicenseZip, @sLicenseCountry, @sInstitutionName, @dtLicenseCreationDate, @iCardNumber
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetLicenseDetails
	DEALLOCATE @cGetLicenseDetails

	-- Selecting the desired result from temporary table
	SELECT AccountNumber, LicenseeName, LicenseType, AccountType, Edition, Email, CONVERT(VARCHAR,ValidFrom,101) as StartDate, 
	CONVERT(VARCHAR,ValidThrough,101) as EndDate, LicenseStatus, CONVERT(NUMERIC(14,2),Price) as Price, 
	LicenseZip, LicenseState, LicenseCountry,InstitutionName, CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate , CardNumber 
	FROM #CustomerReport ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #CustomerReport
END
GO
/****** Object:  StoredProcedure [dbo].[GetExportedImageDetails]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ADAM 
-- ALTER date: 16 July 2013
-- Description:	Get details of exported image based on parameters
-- =============================================
ALTER PROCEDURE [dbo].[GetExportedImageDetails]
	@sStartDate varchar(20) = '', @sEndDate varchar(20) = '', @sAccoutNumber varchar(50)=''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT   
			LID.LicenseId,
			LID.ExportedDate,
			LID.ImageName,
			L.AccountNumber,
			LID.OriginalFileName,
			LID.Title,
			LID.ModuleName,
			(SELECT TOP(1) LSD.NoofImages FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = LID.LicenseId order by LSD.SubscriptionValidFrom desc) as ExportLimit,
			USR.FirstName + ' '+ USR.LastName as UserName,
			(SELECT COUNT(LID1.Id) FROM LicenseImageExportDetail LID1 WHERE LID1.LicenseId = LID.LicenseId group by LID1.LicenseId) as imageCount
	FROM         
		 LicenseImageExportDetail LID 	
		 LEFT JOIN License L ON LID.LicenseId =L.Id
		 INNER JOIN  AIAUser USR ON LID.UserId = USR.Id	 
	WHERE
		 ((LEN(@sStartDate)=0) OR (LID.ExportedDate >= (CONVERT(DATETIME,@sStartDate)))) AND
		 ((LEN(@sEndDate)=0) OR (LID.ExportedDate <= (DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))))) AND
		 ((LEN(@sAccoutNumber)=0) OR (AccountNumber LIKE '%'+@sAccoutNumber+'%'))
END
GO
/****** Object:  StoredProcedure [dbo].[GetExpiringLicenses]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetExpiringLicenses] 
	-- Add the parameters for the stored procedure here
	@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2),
	@iLicenseTypeId int, @iAccountTypeId int, @sZip varchar(20) = '', @iStateId int, @iCountryId int
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END

	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME
	DECLARE @cGetLicenseId CURSOR
	DECLARE @iLicenseId INT
	DECLARE @iLicenseSubscriptionDetail INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @dtStartDate DATETIME
	DECLARE @dtEndDate DATETIME
	DECLARE @mSubscriptionPrice MONEY
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @sEdition VARCHAR(200)
	DECLARE @iDaysRemaining INT
	DECLARE @iCardNumber INT

	-- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
	
	-- ALTER a temporary table to store the desired results of license which are going to be expire on the basis of parameter
	CREATE TABLE #ExpiringLicenseReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		InstitutionName VARCHAR(100),
		Edition VARCHAR(200),
		ValidFrom DATETIME,
		ValidThrough DATETIME,
		LicenseCreationDate DATETIME,
		Price MONEY,
		AccountType VARCHAR(50),
		DaysRemaining INT,
		CardNumber INT
	)

	-- define the forward only, read-only cursor 
	SET @cGetLicenseId = CURSOR FAST_FORWARD 
	FOR 
		SELECT LicenseSubscriptionDetail.LicenseId, MAX(LicenseSubscriptionDetail.Id) 
		FROM LicenseSubscriptionDetail WHERE 
		(TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END)) 
		AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END)) 
		GROUP BY LicenseSubscriptionDetail.LicenseId 
		HAVING (MAX(SubscriptionValidThrough) BETWEEN @dtFromDate AND @dtToDate)
	
	-- open & fetch the cursor variables into the local variables
		OPEN @cGetLicenseId 
		FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId, @iLicenseSubscriptionDetail
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			SET @sEdition = ''
			-- fetch the accountnumber, licenseename, licensetype, startdate, enddate, subscriptionprice, accountype & days remaining to expire for a license
			SELECT @sAccountNumber = AccountNumber, @sLicenseeName = LicenseeName, @sLicenseType = LicenseType,
			@sInstitutionName = InstitutionName, @dtLicenseCreationDate = CreationDate,
			@dtStartDate = SubscriptionValidFrom, @dtEndDate = SubscriptionValidThrough,
			@mSubscriptionPrice = TotalAmount, @sAccountType = AccountType, @iDaysRemaining = DaysRemaining, @iCardNumber = CardNumber
			FROM 
			(
				SELECT AccountNumber, (LicenseeFirstName+' '+LicenseeLastName) as LicenseeName,
				LicenseType.Title as LicenseType, AccountType.Title as AccountType,
				License.InstitutionName,License.CreationDate,
				LicenseSubscriptionDetail.TotalAmount,
				LicenseSubscriptionDetail.SubscriptionValidFrom, LicenseSubscriptionDetail.SubscriptionValidThrough, 
				DATEDIFF(dd,GETDATE(),LicenseSubscriptionDetail.SubscriptionValidThrough) as DaysRemaining, (CASE WHEN License.CardNumber > 0 THEN License.CardNumber END) as CardNumber
				FROM License 
				INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id 
				INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id 
				INNER JOIN LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId 
				INNER JOIN State ON License.StateId = State.Id
				INNER JOIN Country ON License.CountryId = Country.Id
				WHERE License.IsActive = 1
				AND License.LicenseTypeId = (CASE WHEN @iLicenseTypeId > 0 THEN @iLicenseTypeId ELSE License.LicenseTypeId END)
				AND License.AccountTypeId = (CASE WHEN @iAccountTypeId > 0 THEN @iAccountTypeId ELSE License.AccountTypeId END)
				AND State.Id = (CASE WHEN @iStateId > 0 THEN @iStateId ELSE State.Id END)
				AND Country.Id = (CASE WHEN @iCountryId > 0 THEN @iCountryId ELSE Country.Id END)
				AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
				AND LicenseSubscriptionDetail.Id =  @iLicenseSubscriptionDetail
				AND License.LicenseTypeId <> 5 
			) t1 
			WHERE DaysRemaining>=0
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				-- fetch all the editions mapped as a string with a license
				SELECT @sEdition = Edition.Title + '; ' + @sEdition 
				FROM LicenseToEdition INNER JOIN Edition 
				ON LicenseToEdition.EditionId = Edition.Id 
				WHERE LicenseToEdition.LicenseId = @iLicenseId
				-- remove the trailing comma-separator from the edition-string
				--AMI SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1);
					IF LEN(@sEdition)> 1
						-- remove the trailing comma-separator from the edition-string
						SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
					ELSE
						SET @sEdition = @sEdition
				
				-- insert into the temporary table
				INSERT INTO #ExpiringLicenseReport 
				(AccountNumber, LicenseeName, LicenseType,InstitutionName, Edition, ValidFrom, ValidThrough,LicenseCreationDate, Price, AccountType, DaysRemaining,CardNumber) 
				VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sInstitutionName,@sEdition,@dtStartDate,@dtEndDate,@dtLicenseCreationDate, @mSubscriptionPrice,@sAccountType,@iDaysRemaining,@iCardNumber)
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId,@iLicenseSubscriptionDetail
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetLicenseId
	DEALLOCATE @cGetLicenseId

	-- Selecting the desired result from temporary table
	SELECT AccountNumber,LicenseeName,LicenseType,InstitutionName,Edition,
	CONVERT(VARCHAR,ValidFrom,101) as StartDate,CONVERT(VARCHAR,ValidThrough,101) as EndDate,
	CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate,
	CONVERT(NUMERIC(14,2),Price) as SubscriptionPrice,AccountType,DaysRemaining, CardNumber
	FROM #ExpiringLicenseReport ORDER BY AccountNumber

    
	-- Dropping the temporary table
	DROP TABLE #ExpiringLicenseReport
END
GO
/****** Object:  StoredProcedure [dbo].[GetNetAdSummaryReport]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetNetAdSummaryReport] 
	-- Add the parameters for the stored procedure here
	-- FromDate & ToDate are mandatory
	@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), @iLicenseTypeId tinyint
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END

	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME
	DECLARE @cGetSummary CURSOR
	DECLARE @iLicenseId INT
	DECLARE @iLicenseSubscriptioId INT
	DECLARE @iActiveSubscription INT
	DECLARE @iRenewSubscription INT
	DECLARE @iCancelSubscription INT
	DECLARE @iNetAdSubscription INT
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @sInstitutionname VARCHAR(100)
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @IsActive BIT
	DECLARE @sRenew BIT
	
	-- set the default parameters to 0
	SET @iActiveSubscription = 0
	SET @iRenewSubscription = 0
	SET @iCancelSubscription = 0
	
	-- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
	
	-- ALTER a temporary table to store the first-level of result shown in the netad subscription report on the basis of parameter
	CREATE TABLE #NetAdSummaryReport
	(
		LicenseType VARCHAR(50),
		AccountType VARCHAR(50),
		InstitutionName VARCHAR(100),
		LicenseCreationDate DATETIME,
		IsActive BIT,
		IsRenew BIT
	)
	CREATE CLUSTERED INDEX IK_NetAdSummaryReport_1 ON #NetAdSummaryReport (LicenseType, AccountType) 
	CREATE NONCLUSTERED INDEX IK_NetAdSummaryReport_2 ON #NetAdSummaryReport (IsActive) 	
	
	-- ALTER a temporary table
	CREATE TABLE #NetAdResult
	(
		LicenseType VARCHAR(50),
		AccountType VARCHAR(50),
		InstitutionName VARCHAR(100),
		LicenseCreationDate DATETIME,
		ActiveSubscription INT,
		RenewSubscription INT,
		InActiveSubscription INT,
		NetAdSubscription INT
	)
	
	-- define the forward only, read-only cursor 
	SET @cGetSummary = CURSOR FAST_FORWARD 
	FOR 
		SELECT License.Id, LicenseSubscriptionDetail.Id
		FROM LicenseSubscriptionDetail
		INNER JOIN License ON LicenseSubscriptionDetail.LicenseId = License.Id WHERE 
		((License.CancellationDate BETWEEN @dtFromDate AND @dtToDate AND License.IsActive = 0 )
		OR (License.CreationDate BETWEEN @dtFromDate AND @dtToDate )
		OR (RenewalDate BETWEEN @dtFromDate AND @dtToDate))
		AND License.LicenseTypeId = (CASE WHEN @iLicenseTypeId > 0 THEN @iLicenseTypeId ELSE License.LicenseTypeId END)
		AND (TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END)) 
		AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END)) 
		AND License.LicenseTypeId <> 5 
		GROUP BY License.Id, LicenseSubscriptionDetail.Id
		
	
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetSummary 
		FETCH NEXT FROM @cGetSummary INTO @iLicenseId, @iLicenseSubscriptioId
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
				
			-- fetch the licensetype, accountype & the status of a license
			SELECT @sLicenseType = LicenseType.Title, @sAccountType = AccountType.Title, 
			@sInstitutionname = License.InstitutionName, @dtLicenseCreationDate = License.CreationDate,
			@IsActive = License.IsActive, 
			@sRenew = (CASE WHEN LicenseSubscriptionDetail.RenewalDate IS NULL  THEN 0 ELSE 1 END)
			FROM License 
			INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id 
			INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id 
			INNER JOIN LicenseSubscriptionDetail ON LicenseSubscriptionDetail.LicenseId = License.Id
			WHERE License.Id = @iLicenseId 
			AND LicenseSubscriptionDetail.Id = @iLicenseSubscriptioId
			
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				
				IF @IsActive = 1 
				BEGIN
					IF @sRenew = 1
					BEGIN
						SET @iRenewSubscription = @iRenewSubscription + 1
					END
					ELSE
					BEGIN
						SET @iActiveSubscription = @iActiveSubscription + 1
					END
				END
				ELSE
				BEGIN
					IF @sRenew = 1
					BEGIN
						SET @iRenewSubscription = @iRenewSubscription + 1
					END
					ELSE
					BEGIN
						SET @iCancelSubscription = @iCancelSubscription + 1
					END
				END
				
				-- insert into the temporary table
				INSERT INTO #NetAdSummaryReport 
				(LicenseType,AccountType,InstitutionName,LicenseCreationDate,IsActive,IsRenew) 
				VALUES(@sLicenseType,@sAccountType,@sInstitutionname,@dtLicenseCreationDate,@IsActive,@sRenew)
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetSummary INTO @iLicenseId, @iLicenseSubscriptioId
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetSummary
	DEALLOCATE @cGetSummary

	-- Selecting the desired result from temporary table
	INSERT INTO #NetAdResult (LicenseType, AccountType,InstitutionName,LicenseCreationDate,ActiveSubscription, RenewSubscription, InActiveSubscription,
	NetAdSubscription)
	SELECT LicenseType,AccountType,MAX(InstitutionName) as InstitutionName, MAX(LicenseCreationDate) as LicenseCreationDate,(SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 1 AND IsRenew = 0) as ActiveSubscription,
	(SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsRenew = 1) as RenewSubscription,
	(SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 0 AND IsRenew = 0) as InActiveSubscription,
	((SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 1 AND IsRenew = 0) + (SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsRenew = 1) - (SELECT COUNT(1) FROM #NetAdSummaryReport 
	WHERE LicenseType = N1.LicenseType AND AccountType = N1.AccountType AND IsActive = 0)) as NetAdSubscription
	FROM #NetAdSummaryReport N1 GROUP BY LicenseType,AccountType

	-- to show the sum of active, renew, cancel & netad subscriptions
	INSERT INTO #NetAdResult (LicenseType,LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
	NetAdSubscription) SELECT 'Total',@dtLicenseCreationDate, @iActiveSubscription, @iRenewSubscription, @iCancelSubscription,
	(@iActiveSubscription+@iRenewSubscription-@iCancelSubscription) 

	SELECT LicenseType, AccountType,InstitutionName,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, ActiveSubscription, RenewSubscription, InActiveSubscription,
		NetAdSubscription from #NetAdResult

	-- Dropping the temporary tables
	DROP TABLE #NetAdSummaryReport
	DROP TABLE #NetAdResult
END
GO
/****** Object:  StoredProcedure [dbo].[GetNetAdDetailReport]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetNetAdDetailReport] 
	-- Add the parameters for the stored procedure here
	-- FromDate, ToDate, LicenseType & AccountType are mandatory
	@sFromDate varchar(20), @sToDate varchar(20), @sLicenseType varchar(50), @sAccountType varchar(50),
	@iStartPrice numeric(14,2), @iEndPrice numeric(14,2)
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END

	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME
	DECLARE @cGetLicenseId CURSOR
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @iLicenseId INT
	DECLARE @iLicenseSubscriptionDetailId INT
	DECLARE @sEdition VARCHAR(200)
	DECLARE @dtRenewalDate DATETIME
	DECLARE @LicenseStatus VARCHAR(10)

	-- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
	
	-- ALTER a temporary table to store the second-level after clicking on the first-level of result shown in the netad subscription report on the basis of parameter
	CREATE TABLE #NetAdDetailReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		Edition VARCHAR(200),
		RenewalDate DATETIME
	)
	
	-- define the forward only, read-only cursor 
	SET @cGetLicenseId = CURSOR FAST_FORWARD 
	FOR 
		SELECT License.Id, LicenseSubscriptionDetail.Id
		FROM LicenseSubscriptionDetail
		INNER JOIN License ON LicenseSubscriptionDetail.LicenseId = License.Id WHERE 
		((License.CancellationDate BETWEEN @dtFromDate AND @dtToDate AND License.IsActive = 0 )
		OR (SubscriptionValidFrom BETWEEN @dtFromDate AND @dtToDate ))
		AND (TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END)) 
		AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END)) 
		GROUP BY License.Id, LicenseSubscriptionDetail.Id
	
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetLicenseId 
		FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId, @iLicenseSubscriptionDetailId
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @sEdition = ''
			-- fetch the accountnumber, licenseename of a license
			SELECT @sAccountNumber = AccountNumber, @sLicenseeName = (LicenseeFirstName+' '+LicenseeLastName)
			FROM License 
			INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id 
			INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id
			WHERE License.Id = @iLicenseId  
			AND License.IsActive = 1
			AND LicenseType.Title = @sLicenseType
			AND AccountType.Title = @sAccountType
			
		
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				-- fetch the renewal date of the license
				SELECT @dtRenewalDate = LicenseSubscriptionDetail.RenewalDate
				FROM LicenseSubscriptionDetail
				WHERE LicenseSubscriptionDetail.Id = @iLicenseSubscriptionDetailId
		
				-- fetch all the editions mapped as a string with a license
				SELECT @sEdition = Edition.Title + '; ' + @sEdition 
				FROM LicenseToEdition INNER JOIN Edition 
				ON LicenseToEdition.EditionId = Edition.Id 
				WHERE LicenseToEdition.LicenseId = @iLicenseId
				-- remove the trailing comma-separator from the edition-string
				SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1);

				-- insert into the temporary table
				INSERT INTO #NetAdDetailReport 
				(AccountNumber,LicenseeName,LicenseType,Edition,RenewalDate) 
				VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sEdition,@dtRenewalDate)
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseId INTO @iLicenseId, @iLicenseSubscriptionDetailId
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetLicenseId
	DEALLOCATE @cGetLicenseId

	-- Selecting the desired result from temporary table
	SELECT AccountNumber, LicenseeName, LicenseType, Edition, ISNULL(CONVERT(VARCHAR,RenewalDate,101),'') as RenewDate
	FROM #NetAdDetailReport

    
	-- Dropping the temporary table
	DROP TABLE #NetAdDetailReport
END
GO
/****** Object:  StoredProcedure [dbo].[GetModulesByUserId]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Magic Software
-- ALTER date: 12-May-2009
-- Description:	To get the list of blocked user who have attempt 5 times wrong login
-- =============================================
ALTER PROCEDURE [dbo].[GetModulesByUserId] 
	-- Add the parameters for the stored procedure here
	@iUserId int
AS
BEGIN
	-- returns the metadata
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	SELECT * FROM ((SELECT * FROM (SELECT DISTINCT A1.Id, A1.Title, A1.ParentId, ISNULL((SELECT Activity.Title FROM Activity
	WHERE Activity.Id = A1.ParentId ),'') as Parent, Priority
	FROM AIAUserActivity INNER JOIN RoleToActivity 
	ON AIAUserActivity.RoleId = RoleTOActivity.RoleId
	INNER JOIN Activity A1
	ON RoleToActivity.ActivityId = A1.Id
	WHERE AIAUserActivity.UserId = @iUserId AND A1.IsActive = 1 ) RoleModel
	WHERE ParentId!=0
	)
	UNION
	(SELECT A1.Id, A1.Title, A1.ParentId, (SELECT Activity.Title FROM Activity
	WHERE Activity.Id = A1.ParentId) as Parent, Priority
	FROM AIAUserActivity INNER JOIN RoleToActivity 
	ON AIAUserActivity.ActivityId = RoleTOActivity.ActivityId
	INNER JOIN Activity A1
	ON RoleToActivity.ActivityId = A1.Id
	WHERE AIAUserActivity.UserId = @iUserId AND A1.IsActive = 1)) Roles 
	ORDER BY (CASE ParentId WHEN 0 THEN 9999 ELSE ParentId END), Priority 
END
GO
/****** Object:  StoredProcedure [dbo].[GetIncorrectLoginAttempt]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetIncorrectLoginAttempt] 
	-- Add the parameters for the stored procedure here
	-- User Id is  mandatory
	@iUserId int
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END

	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @dtLoginTime DATETIME 
	SET @dtLoginTime = getdate()

	SELECT CntIncorrectLogins FROM IncorrectLoginAttempts
	WHERE IncorrectLoginAttempts.UserId =  @iUserId 
	AND datediff(day,@dtLoginTime,loginTime) = 0
	IF @@RowCount = 0
	BEGIN
		SELECT 0 as CntIncorrectLogins
	END
END
GO
/****** Object:  StoredProcedure [dbo].[GetUserlActivities]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUserlActivities] 
	-- Add the parameters for the stored procedure here
	-- User Id is  mandatory
	@iUserId int
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END

	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	
	-- Selecting the desired result from Activity, AIAUserActivity tables
	SELECT Activity.Title, AIAUserActivity.ActivityId
	FROM Activity, AIAUserActivity WHERE  Activity.Id = AIAUserActivity.ActivityId
	AND AIAUserActivity.UserId = @iUserId

	
END
GO
/****** Object:  StoredProcedure [dbo].[IncorrectLoginAttempt]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[IncorrectLoginAttempt] 
	-- Add the parameters for the stored procedure here
	-- User Id is  mandatory
	@iUserId int
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END

	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
   
   -- Selecting the desired result from temporary table	
   SELECT TOP 1 LoginDetail.LoginTime FROM LoginDetail WHERE LoginDetail.UserId = @iUserId ORDER BY LoginDetail.Id DESC

END
GO
/****** Object:  StoredProcedure [dbo].[GetSearchDetails]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 21/5/2009
-- Description:	Fetch Content Descriptor Ids for Different Resources.
-- =============================================
ALTER PROCEDURE [dbo].[GetSearchDetails]
	
AS
BEGIN
	
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	Select distinct TermNumber, ContentTypeId,Id 
	From (
		Select ds.TermNumber, cd.ContentTypeId, cd.Id
		From DissectibleStructures ds 
				INNER JOIN DissectibleStructureGroup  dsg on ds.Dissectiblestructuregroupid = dsg.id
				INNER JOIN DissectibleBodyView dbv on dsg.Dissectiblebodyviewid = dbv.id
				INNER JOIN DissectibleContent dc on dsg.id = dc.Dissectiblestructuregroupid
				INNER JOIN ContentDescriptor cd on dc.contentdescriptorid = cd.id
		Where cd.IsActive = 'Y'and ds.TermNumber is not null
		Union
		-- Query which will use to get Atlas anatomy data.
		Select distinct ap.VocabTermNumbers as TermNumber,cd.ContentTypeId,ic.ContentdescriptorId
		From ImageContent ic 
			INNER JOIN AnnotationPin ap on ic.Id = ap.ImageContentId
			INNER JOIN ContentDescriptor cd on cd.Id = ic.ContentDescriptorId
		Where ap.VocabTermNumbers is not null
		Union
		----Query for CI by term ID
		Select ca.TermNumber,cd.ContentTypeId,cd.Id
		From ContentAttribute ca
			INNER JOIN ContentDescriptorToAttributeMap cdm on ca.Id = cdm.ContentAttributeId
			INNER JOIN ContentDescriptor cd on cdm.ContentDescriptorId = cd.Id
		Where cd.IsActive='Y' and ca.TermNumber is not null
		Union
		Select tdt.TermNumberId as TermNumber,cd.ContentTypeId,cd.Id 
		From ContentDescriptor cd 
			INNER JOIN ImageContent ic on cd.Id = ic.ContentDescriptorId
			INNER JOIN ThreeDTermsToHighLight tdt on ic.Id = tdt.IcId
		Where cd.IsActive='Y'
		Union
		Select distinct cdpm.SearchPhraseId as TermNumber, cd.ContentTypeId, cd.Id  from ContentDescriptor cd 
			INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.id =  cdpm.ContentDescriptorId
			Where cd.IsActive='Y' 
		) a
	Order by TermNumber, ContentTypeId, Id
END
GO
/****** Object:  StoredProcedure [dbo].[InsertIncorrectLoginAttempt]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertIncorrectLoginAttempt] 
	-- Add the parameters for the stored procedure here
	-- User Id is  mandatory
	@iUserId int
AS
BEGIN

	DECLARE @dtLoginTime DATETIME 
	--DECLARE @cLoginAttempt CURSOR
	DECLARE @iCount INT;
	CREATE TABLE #tmpTable
	(
		RowReturn INT DEFAULT 1
	)
	SET @iCount = 0
	SET @dtLoginTime = getdate()
INSERT INTO IncorrectLoginAttempts (UserId, LoginTime, CntIncorrectLogins)
			VALUES(@iUserId,@dtLoginTime,1) 
SELECT @iCount = SCOPE_IDENTITY()
/*
	SET @cLoginAttempt = CURSOR FAST_FORWARD 
	FOR
	SELECT 1 
	FROM IncorrectLoginAttempts WHERE IncorrectLoginAttempts.UserId =  @iUserId  
	
	OPEN @cLoginAttempt 
		FETCH NEXT FROM @cLoginAttempt INTO @iCount
		-- start of while loop

		IF @iCount = 0
		BEGIN
			INSERT INTO IncorrectLoginAttempts (UserId, LoginTime, CntIncorrectLogins)
			VALUES(@iUserId,@dtLoginTime,1)   
		END	
		ELSE
		BEGIN
			UPDATE IncorrectLoginAttempts SET LoginTime=@dtLoginTime, CntIncorrectLogins = CntIncorrectLogins+1 
			WHERE UserId = @iUserId 
		END
	INSERT INTO #tmpTable values(1)*/
	 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertUpdateSiteAccount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sachin Verma
-- ALTER date: 28-Apr-2009
-- Description:	To ALTER or update a new site account
-- =============================================
ALTER PROCEDURE [dbo].[InsertUpdateSiteAccount] 
	-- Add the parameters for the stored procedure here
	@iSiteId int, @sSiteIP varchar(2000), @sTitle varchar(100), @sInstituteName varchar(100), @sDepartment varchar(50),
    @sAddress1 varchar(100), @sAddress2 varchar(100), @sCity varchar(50), @Zip varchar(20), @Phone varchar(30),
    @StateId int, @CountryId int, @IsMaster bit, @CreationDate datetime, @ModifiedDate datetime,
    @IsActive bit, @IsModesty bit, @UserId int, @sSiteIPTo varchar(100)
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRY
		BEGIN TRANSACTION

			IF @iSiteId = 0 
			BEGIN
				INSERT INTO [dbo].[Site]([SiteIP],[Title],[InstituteName],[Department],[Address1],[Address2],
					[City],[Zip],[Phone],[StateId],[CountryId],[IsMaster],[CreationDate],[ModifiedDate],[IsActive],[SiteIPTo])
				VALUES(@sSiteIP, @sTitle, @sInstituteName, @sDepartment, @sAddress1, @sAddress2, @sCity, @Zip, @Phone,
					@StateId, @CountryId, @IsMaster, @CreationDate, @ModifiedDate, @IsActive, @sSiteIPTo)
				-- to get the last inserted identity value in the current session
				SET @iSiteId=SCOPE_IDENTITY()
			END
			ELSE
			BEGIN
				UPDATE [dbo].[Site]	SET [SiteIP]=@sSiteIP, [Title]=@sTitle,[InstituteName]=@sInstituteName,
					[Department]=@sDepartment, [Address1]=@sAddress1, [Address2]=@sAddress2,[City]=@sCity, 
					[Zip]=@Zip, [Phone]=@Phone, [StateId]=@StateId, [CountryId]=@CountryId,
					[ModifiedDate]=@ModifiedDate, [IsActive]=@IsActive, [SiteIPTo]=@sSiteIPTo
				WHERE [Id]=@iSiteId
			END
			
			-- Delete Old record from AIAUserToSite.
			DELETE FROM AIAUserToSite Where SiteId=@iSiteId
			
			-- Insert New record record from AIAUserToSite.
			INSERT INTO [dbo].[AIAUserToSite] ([UserId],[SiteId])
				VALUES(@UserId,@iSiteId)



		COMMIT TRANSACTION
		SELECT CONVERT( varchar(4000), @iSiteId) as Message1, @iSiteId as Message2
		
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as Message1, Error_Line() as Message2
	END CATCH
    
END
GO
/****** Object:  StoredProcedure [dbo].[StartResellerSubscription]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[StartResellerSubscription] 
	-- Add the parameters for the stored procedure here
	@iLicenseId int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	
	DECLARE @iSubscriptionDiff INT
	DECLARE @iLicenseSubscriptionId INT
	
	SET @iLicenseSubscriptionId = 0
	SET @iSubscriptionDiff = 0
	
	SET @iLicenseSubscriptionId = (SELECT MAX(Id) FROM LicenseSubscriptionDetail WHERE LicenseId = @iLicenseId)
	
	-- calculate the date difference of the reseller subscription 
	SET @iSubscriptionDiff = (SELECT DATEDIFF(D,SubscriptionValidFrom,SubscriptionValidThrough) 
		FROM LicenseSubscriptionDetail WHERE Id = @iLicenseSubscriptionId)
	
	
	UPDATE LicenseSubscriptionDetail SET SubscriptionValidFrom = GETDATE(),
	SubscriptionValidThrough = DATEADD(D,@iSubscriptionDiff,GETDATE())
	WHERE Id = @iLicenseSubscriptionId
	
		
END
GO
/****** Object:  StoredProcedure [dbo].[GetSubscribedLicenses]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSubscribedLicenses] 
	-- Add the parameters for the stored procedure here
	@sFromDate varchar(20), @sToDate varchar(20), @iStartPrice numeric(14,2), @iEndPrice numeric(14,2), @iLicenseTypeId tinyint,
	@iAccountTypeId tinyint, @sZip varchar(20) = '', @iStateId int, @iCountryId int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME
	DECLARE @cGetLicenseID CURSOR
	DECLARE @iLicenseId INT
	DECLARE @iLicenseSubscriptionDetail INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @sLicenseeName VARCHAR(100)
	DECLARE @sLicenseType VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @dtStartDate DATETIME
	DECLARE @dtEndDate DATETIME
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @mSubscriptionPrice MONEY
	DECLARE @sAccountType VARCHAR(50)
	DECLARE @sEdition VARCHAR(200)
	DECLARE @iCardNumber INT
	

	-- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))

	-- ALTER a temporary table to store the desired results of subscribed licenses on the basis of parameter
	CREATE TABLE #SubscribedLicenseReport
	(
		AccountNumber VARCHAR(50),
		LicenseeName VARCHAR(100),
		LicenseType VARCHAR(50),
		InstitutionName VARCHAR(100),
		Edition VARCHAR(200),
		ValidFrom DATETIME,
		ValidThrough DATETIME,
		LicenseCreationDate DATETIME,
		Price MONEY,
		AccountType varchar(50),
		CardNumber INT
	)

	-- define the forward only, read-only cursor 
	SET @cGetLicenseID = CURSOR FAST_FORWARD 
	FOR 
		SELECT LicenseSubscriptionDetail.LicenseId, MAX(LicenseSubscriptionDetail.Id) 
		FROM LicenseSubscriptionDetail WHERE 
		(TotalAmount >= (CASE WHEN @iStartPrice > 0 THEN @iStartPrice ELSE 0 END)) 
		AND (TotalAmount <= (CASE WHEN @iEndPrice = 0 THEN 0 WHEN @iEndPrice > 0 THEN @iEndPrice ELSE 9999999999 END))
		GROUP BY LicenseSubscriptionDetail.LicenseId
		HAVING (MAX(SubscriptionValidFrom) BETWEEN @dtFromDate AND @dtToDate) 
	
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetLicenseID 
		FETCH NEXT FROM @cGetLicenseID INTO @iLicenseId, @iLicenseSubscriptionDetail
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			SET @sEdition = ''

			-- fetch the accountnumber, licenseename, licensetype, accountype of a license
			SELECT @sAccountNumber = AccountNumber, @sLicenseeName = (LicenseeFirstName+' '+LicenseeLastName),
			@sLicenseType = LicenseType.Title, @sAccountType = AccountType.Title,
			@iCardNumber = (CASE WHEN License.CardNumber > 0 THEN License.CardNumber END),
			@sInstitutionName = License.InstitutionName,@dtLicenseCreationDate = License.CreationDate
			FROM License 
			INNER JOIN LicenseType ON License.LicenseTypeId = LicenseType.Id 
			INNER JOIN AccountType ON License.AccountTypeId = AccountType.Id 
			INNER JOIN State ON License.StateId = State.Id
			INNER JOIN Country ON License.CountryId = Country.Id
			WHERE License.Id = @iLicenseId AND License.IsActive = 1 
			AND License.LicenseTypeId = (CASE WHEN @iLicenseTypeId > 0 THEN @iLicenseTypeId ELSE License.LicenseTypeId END)
			AND License.AccountTypeId = (CASE WHEN @iAccountTypeId > 0 THEN @iAccountTypeId ELSE License.AccountTypeId END)
			AND State.Id = (CASE WHEN @iStateId > 0 THEN @iStateId ELSE State.Id END)
			AND Country.Id = (CASE WHEN @iCountryId > 0 THEN @iCountryId ELSE Country.Id END)
			AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)
			AND License.LicenseTypeId <> 5 
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				-- fetch startdate, enddate, subscriptionprice of a license
				SELECT @mSubscriptionPrice = LicenseSubscriptionDetail.TotalAmount,
				@dtStartDate = LicenseSubscriptionDetail.SubscriptionValidFrom, 
				@dtEndDate = LicenseSubscriptionDetail.SubscriptionValidThrough
				FROM LicenseSubscriptionDetail 
				WHERE LicenseSubscriptionDetail.Id = @iLicenseSubscriptionDetail

				-- fetch all the editions mapped as a string with a license
				SELECT @sEdition = Edition.Title + '; ' + @sEdition 
				FROM LicenseToEdition INNER JOIN Edition 
				ON LicenseToEdition.EditionId = Edition.Id 
				WHERE LicenseToEdition.LicenseId = @iLicenseId
				-- remove the trailing comma-separator from the edition-string
				-- AMI SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1);
				IF LEN(@sEdition)> 1
						-- remove the trailing comma-separator from the edition-string
						SET @sEdition = SUBSTRING(@sEdition,1,LEN(@sEdition)-1)
					ELSE
						SET @sEdition = @sEdition

				-- insert into the temporary table
				INSERT INTO #SubscribedLicenseReport 
				(AccountNumber, LicenseeName, LicenseType, InstitutionName, Edition, ValidFrom, ValidThrough,LicenseCreationDate, Price, AccountType,CardNumber) 
				VALUES(@sAccountNumber,@sLicenseeName,@sLicenseType,@sInstitutionName,@sEdition,@dtStartDate,@dtEndDate,@dtLicenseCreationDate,@mSubscriptionPrice,@sAccountType,@iCardNumber)
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetLicenseID INTO @iLicenseId,@iLicenseSubscriptionDetail
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetLicenseID
	DEALLOCATE @cGetLicenseID

	-- Selecting the desired result from temporary table
	SELECT AccountNumber, LicenseeName, LicenseType,InstitutionName, AccountType, Edition, 
	CONVERT(VARCHAR,ValidFrom,101) as StartDate, CONVERT(VARCHAR,ValidThrough,101) as EndDate, 
	CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, 
	CONVERT(NUMERIC(14,2),Price) as SubscriptionPrice,CardNumber
	FROM #SubscribedLicenseReport ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #SubscribedLicenseReport
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateLicenseStatus]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Dany Ellement
-- ALTER date: 16-May-2013
-- Description:	update the license status on the basis of subscription date
-- =============================================
ALTER PROCEDURE [dbo].[UpdateLicenseStatus] 
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    BEGIN TRY
		BEGIN TRANSACTION
		
			DECLARE @sRecipientsMail VARCHAR(255)
			DECLARE @sDeploymentEnvironment VARCHAR(255)
			DECLARE @sMessageSubject VARCHAR(1000)
			
			SET @sRecipientsMail = 'edsales@adamcorp.com'
			SET @sDeploymentEnvironment = 'PRD'
								
			-- fetch the email id of recipient from the environment vairable
			--EXEC GetSchedulerAlertEmail @sRecipientsMail OUTPUT, @sDeploymentEnvironment OUTPUT
						
			SET @sMessageSubject = @sDeploymentEnvironment+': Job Scheduler - active/inactive license'
			
			
			EXEC msdb..sp_send_dbmail
			@profile_name = 'MailProfile',
            @recipients = @sRecipientsMail, 
            @body = 'Job Scheduler Started - active/inactive license', 
            @body_format = 'HTML',
            @subject =  @sMessageSubject
            
			DECLARE @cLicenseToInactive CURSOR
			DECLARE @cLicenseToActive CURSOR
			DECLARE @dtCurrentDate DATETIME = GETDATE()
			DECLARE @iLicenseId INT
			DECLARE @dtSubscriptionValidFrom DATETIME
			DECLARE @iDayDifference INT
			DECLARE @sErrorStatus CHAR(2) = 'ok'
		    
			-- fetch the license which are currently active and whose subscription enddate is smaller than current date
			SET @cLicenseToInactive = CURSOR FAST_FORWARD FOR
			
			SELECT Distinct R1.Id FROM(
								SELECT License.Id, SubscriptionValidFrom, (SELECT Max(SubscriptionValidThrough) FROM LicenseSubscriptionDetail WHERE LicenseId = License.Id GROUP BY LicenseId) AS MaxDateForLicense FROM License
								INNER JOIN LicenseSubscriptionDetail ON ( License.Id = LicenseSubscriptionDetail.LicenseId )
								WHERE License.IsActive = 1
							 )
			AS R1 WHERE DATEDIFF(DAY,GETDATE(),MaxDateForLicense)<0
			
			-- *** OLD CODE SUPPORT ONLY ONE SUBSCRIPTION BY LICENSE ***
			--SELECT DISTINCT License.Id FROM License 
			--	INNER JOIN LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId
			--	WHERE License.IsActive = 1 
			--	AND DATEDIFF(DAY,@dtCurrentDate,LicenseSubscriptionDetail.SubscriptionValidThrough)<0
				
				-- --AND CONVERT(CHAR(10),LicenseSubscriptionDetail.SubscriptionValidThrough,101) < CONVERT(CHAR(10),@dtCurrentDate,101)
				
			-- open & fetch the cursor variables into the local variables
			OPEN @cLicenseToInactive
			FETCH NEXT FROM @cLicenseToInactive INTO @iLicenseId
			WHILE @@FETCH_STATUS = 0
			BEGIN
			
				SET @dtSubscriptionValidFrom = (SELECT Distinct R1.SubscriptionValidFrom FROM(
												SELECT Distinct License.Id, SubscriptionValidFrom, (SELECT Max(SubscriptionValidThrough) FROM LicenseSubscriptionDetail WHERE LicenseId = @iLicenseId GROUP BY LicenseId) AS MaxDateForLicense FROM License
												INNER JOIN LicenseSubscriptionDetail ON ( @iLicenseId = LicenseSubscriptionDetail.LicenseId )
												WHERE License.IsActive = 1 AND License.Id = @iLicenseId)
												AS R1 WHERE DATEDIFF(DAY,GETDATE(),MaxDateForLicense)>=0 AND R1.SubscriptionValidFrom = MaxDateForLicense )
				
				-- *** OLD CODE SUPPORT ONLY ONE SUBSCRIPTION BY LICENSE ***
				--(SELECT LicenseSubscriptionDetail.SubscriptionValidFrom FROM LicenseSubscriptionDetail 
				--	WHERE LicenseId = @iLicenseId 
				--	AND DATEDIFF(DAY,@dtCurrentDate,SubscriptionValidThrough)>=0)
	
					-- --AND CONVERT(CHAR(10),SubscriptionValidThrough,101) >= CONVERT(CHAR(10),@dtCurrentDate,101))
					
				IF @dtSubscriptionValidFrom IS NULL
				BEGIN
					UPDATE License SET IsActive=0, CancellationDate = @dtCurrentDate WHERE Id = @iLicenseId
				END
				ELSE
				BEGIN
					SET @iDayDifference = (SELECT DATEDIFF(DAY,@dtCurrentDate,@dtSubscriptionValidFrom))
					IF @iDayDifference > 0 
					BEGIN
						UPDATE License SET IsActive=0, CancellationDate = @dtCurrentDate WHERE Id = @iLicenseId
					END
				END
				FETCH NEXT FROM @cLicenseToInactive INTO @iLicenseId
			END
			-- close the cursor to free up resources
			CLOSE @cLicenseToInactive
			DEALLOCATE @cLicenseToInactive
			
			-- fetch the license which are currently inactive and whose subscription fromdate starts today
			SET @cLicenseToActive = CURSOR FAST_FORWARD FOR
			SELECT DISTINCT License.Id FROM License 
				INNER JOIN LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId
				WHERE License.IsActive = 0 
				AND DATEDIFF(DAY,@dtCurrentDate,LicenseSubscriptionDetail.SubscriptionValidFrom) = 0
				--AND CONVERT(CHAR(10),LicenseSubscriptionDetail.SubscriptionValidFrom,101) = CONVERT(CHAR(10),@dtCurrentDate,101)
			-- open & fetch the cursor variables into the local variables
			OPEN @cLicenseToActive
			FETCH NEXT FROM @cLicenseToActive INTO @iLicenseId
			WHILE @@FETCH_STATUS = 0
			BEGIN
				UPDATE License SET IsActive=1 WHERE Id = @iLicenseId
				FETCH NEXT FROM @cLicenseToActive INTO @iLicenseId
			END
			-- close the cursor to free up resources
			CLOSE @cLicenseToActive
			DEALLOCATE @cLicenseToActive
		COMMIT TRANSACTION
		
		EXEC msdb..sp_send_dbmail
			@profile_name = 'MailProfile',
           @recipients = @sRecipientsMail, 
            @body = 'Job Scheduler Ended - active/inactive license', 
            @body_format = 'HTML',
            @subject = @sMessageSubject
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateLicenseAccount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic Software
-- ALTER date: 27-May-2009
-- Description:	To update a license account
-- =============================================
ALTER PROCEDURE [dbo].[UpdateLicenseAccount] 
	-- Add the parameters for the stored procedure here
	@iLicenseId int, @sLicenseeFname varchar(50), @sLicenseeLname varchar(50),
	@iLicenseTypeId tinyint, @iAccountTypeId tinyint, @sInstitutionName varchar(100)='', @sAddress1 varchar(100)='', 
	@sAddress2 varchar(100)='', @sCity varchar(50)='', @sZip varchar(20)='', @iStateId int, @iCountryId int, 
	@sPhone varchar(30) = '', @sEmailId varchar(50), @iIsActive tinyint, @iTotalLogins int = 0, @iIsRennew tinyint,
	@sStartDate varchar(20), @sEndDate varchar(20), @sRenewDate varchar(20), @sMasterIP varchar(100) = '', 
	@sEditionList varchar(256), @iPrice numeric(14,2), @sProductKey	varchar(50), @sSiteIPTo varchar(100) = '', @sSiteMasterIPTo varchar(100) = '',
	@iNoofImages int
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @cEditionLogins CURSOR
			DECLARE @iSiteId INT
			DECLARE @iLicenseEditionId INT
			DECLARE @iModesty TINYINT
			DECLARE @dtStartDate DATETIME
			DECLARE @dtEndDate DATETIME
			DECLARE @dtRenewDate DATETIME
			DECLARE @sErrorStatus CHAR(2)
			DECLARE @dtCurrentDate DATETIME
			DECLARE @sitem VARCHAR(100)
			DECLARE @sRecordDelimiter CHAR(1)
			DECLARE @sEditionLoginDelimiter CHAR(1)
			DECLARE @sPaymentMode VARCHAR(10)
			DECLARE @iLicenseSubscriptionId INT
			DECLARE @iSubscriptionId SMALLINT
			DECLARE @dtCancellationDate DATETIME
			DECLARE @iEditionExists TINYINT
			DECLARE @sCountryCode VARCHAR(10)
			
			-- set the parameters to default values
			SET @iModesty = 0
			SET @sRecordDelimiter = '|'
			SET @sEditionLoginDelimiter = '-'
			SET @iSubscriptionId = NULL
			SET @dtCancellationDate = NULL
			SET @dtCurrentDate = getdate()
			SET @sPaymentMode = 'CASH'
			SET @sErrorStatus = 'ok'
			
			IF @iStateId = 0
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			
			-- set the state to Other if the country is Non-US
			SET @sCountryCode = (SELECT CountryCode from Country WHERE Id = @iCountryId)
			IF @sCountryCode != 'US'
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			
			-- convert the datatype of startdate & enddate parameter to datetime
			SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
			SELECT @dtRenewDate = CONVERT(DATETIME,@sRenewDate)
			SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
			-- if user inactive the license then set the cancellation date to current date
			IF @iIsActive = 0 
			BEGIN
				SET @dtCancellationDate = 	@dtCurrentDate
			END
						
			UPDATE License SET LicenseeFirstName = @sLicenseeFname, LicenseeLastName = @sLicenseeLname,
			AccountTypeId = @iAccountTypeId, InstitutionName = @sInstitutionName, EmailId = @sEmailId,
			Address1 = @sAddress1, Address2 = @sAddress2, City = @sCity, Zip = @sZip, StateId = @iStateId,
			CountryId = @iCountryId, Phone = @sPhone, TotalLogins = @iTotalLogins, IsActive = @iIsActive,
			ModifiedDate = @dtCurrentDate, CancellationDate = @dtCancellationDate, ProductId = @sProductKey WHERE Id = @iLicenseId
			
			SET @iLicenseSubscriptionId = (SELECT MAX(Id) FROM LicenseSubscriptionDetail WHERE LicenseId = @iLicenseId)
			-- if the subscription of license is renew
			IF @iIsRennew = 1
			BEGIN
				-- check if license is single license
				IF @iLicenseTypeId = 2
				BEGIN
					SET @iSubscriptionId = (SELECT SubscriptionPlanId FROM LicenseSubscriptionDetail WHERE Id = @iLicenseSubscriptionId)
				END
				INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionPlanId, SubscriptionValidFrom, 
				SubscriptionValidThrough, RenewalDate, PaymentMode, TotalAmount, AmountPaid,NoofImages) 
				VALUES(@iLicenseId, @iSubscriptionId, @dtStartDate, @dtEndDate, @dtRenewDate, @sPaymentMode, @iPrice, @iPrice,@iNoofImages)
				UPDATE License SET NoOfRenewals = NoOfRenewals + 1 WHERE Id = @iLicenseId
			END
			ELSE
			BEGIN
				UPDATE LicenseSubscriptionDetail SET SubscriptionValidFrom = @dtStartDate, 
				SubscriptionValidThrough = @dtEndDate, TotalAmount = @iPrice, AmountPaid = @iPrice , NoofImages =@iNoofImages
				WHERE Id = @iLicenseSubscriptionId
			END
	
			-- check if license is site license
			IF @iLicenseTypeId = 3
			BEGIN
			
				SET @iSiteId = (SELECT DISTINCT Max(Site.Id) FROM LicenseToEdition 
				INNER JOIN SiteToLicenseEdition ON LicenseToEdition.Id = SiteToLicenseEdition.LicenseEditionId 
				INNER JOIN Site ON SiteToLicenseEdition.SiteId = Site.Id
				WHERE LicenseToEdition.LicenseId=@iLicenseId AND Site.IsMaster=1 AND Site.IsActive=1)
				
				UPDATE Site SET SiteIP = @sMasterIP, Title = @sMasterIP, ModifiedDate = @dtCurrentDate,
							SiteIPTo = @sSiteIPTo, SiteMasterIPTo = @sSiteMasterIPTo
				WHERE Id = @iSiteId
			END
			
			SET @cEditionLogins = CURSOR FAST_FORWARD FOR SELECT item FROM dbo.fnSplit(@sEditionList,@sRecordDelimiter)
			OPEN @cEditionLogins
			FETCH NEXT FROM @cEditionLogins INTO @sitem
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @iEditionExists = (SELECT 1 FROM LicenseToEdition WHERE LicenseId = @iLicenseId AND EditionId = SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1))
				
				IF @iEditionExists IS NULL OR @iEditionExists = 0
				BEGIN
					INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty) 
					SELECT @iLicenseId, SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1), 
						SUBSTRING(@sitem,CHARINDEX(@sEditionLoginDelimiter,@sitem)+1,LEN(@sitem)), @iModesty
					-- check if license is site license
					IF @iLicenseTypeId = 3
					BEGIN
						-- to get the last inserted licenseedition id identity value in the current session
						SET @iLicenseEditionId = SCOPE_IDENTITY()
						INSERT INTO SiteToLicenseEdition (SiteId, LicenseEditionId, IsModesty) VALUES (@iSiteId, @iLicenseEditionId, @iModesty)		
					END
				END
				ELSE
				BEGIN
					UPDATE LicenseToEdition SET TotalLogins = SUBSTRING(@sitem,CHARINDEX(@sEditionLoginDelimiter,@sitem)+1,LEN(@sitem))
					WHERE LicenseId = @iLicenseId 
					AND EditionId = SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1)
				END
				FETCH NEXT FROM @cEditionLogins INTO @sitem
			END
		
		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
    
END
GO
/****** Object:  StoredProcedure [dbo].[InsertTestLicenseAccount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic Software
-- ALTER date: 22-Apr-2009
-- Description:	To ALTER a new test license account
-- =============================================
ALTER PROCEDURE [dbo].[InsertTestLicenseAccount] 
	-- Add the parameters for the stored procedure here
	@sAccountNumber varchar(50), @sLicenseeFname varchar(50), @sLicenseeLname varchar(50), @sLoginId varchar(50), @sPassword varchar(50),
	@sEmailId varchar(50), @iAccountTypeId tinyint, @iEditionId tinyint, @sAddress varchar(100)='', @sCity varchar(50)='',
	@sZip varchar(20)='', @iStateId int, @iCountryId int, @sPhone varchar(30)='', @sStartDate varchar(20), @sEndDate varchar(20), @iCreatorId int ,@iNoofImages int
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @iLicenseId int
			DECLARE @iLicenseEditionId int
			DECLARE @iAIAUserId int
			DECLARE @iLicenseTypeId tinyint
			DECLARE @iUserTypeId tinyint
			DECLARE @iAmount tinyint
			DECLARE @iTotalLogins tinyint
			DECLARE @iActive tinyint
			DECLARE @iModesty tinyint
			DECLARE @dtStartDate datetime
			DECLARE @dtEndDate datetime
			DECLARE @sErrorStatus char(2)
			DECLARE @dtCurrentDate datetime
			DECLARE @sCountryCode VARCHAR(10)

			-- set the parameters to default values
			SET @iTotalLogins = 1
			SET @iActive = 1
			SET @iAmount = 0
			SET @iModesty = 0
			SET @dtCurrentDate = getdate()
			SET @sErrorStatus = 'ok'
			
			IF @iStateId = 0
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			-- set the state to Other if the country is Non-US
			SET @sCountryCode = (SELECT CountryCode from Country WHERE Id = @iCountryId)
			IF @sCountryCode != 'US'
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			
			-- fetch the licensetypeid of the test account license
			SELECT @iLicenseTypeId = Id from LicenseType WHERE Title = 'Test Account License'
			-- fetch the usertypeid of the test account user
			SELECT @iUserTypeId = Id from UserType WHERE Title = 'Test Account'

			-- convert the datatype of startdate & enddate parameter to datetime
			SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
			SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
			
			INSERT INTO License(AccountNumber, LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId, 
			EmailId, Address1, City, Zip, StateId, CountryId, Phone, TotalLogins, IsActive, IsDistrictSiteLicense, 
			CreationDate) VALUES (@sAccountNumber, @sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, @iAccountTypeId,
			@sEmailId, @sAddress, @sCity, @sZip, @iStateId, @iCountryId, @sPhone, @iTotalLogins, @iActive, 0, @dtCurrentDate )
			-- to get the last inserted identity value in the current session
			SET @iLicenseId = SCOPE_IDENTITY()
			
			INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
			TotalAmount, AmountPaid, AmountPending ,NoofImages) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @iAmount, @iAmount, @iAmount ,@iNoofImages)

			IF @iEditionId <= 4
			BEGIN
				-- insert All resource module of license for Instructor Edition
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id > 7 then 0 else 1 end as Status FROM ResourceModule;
			END
			ELSE IF @iEditionId = 8
			BEGIN
				-- insert All resource module of license for Library Edition
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6 or ResourceModule.id = 13 then 0 else 1 end as Status FROM ResourceModule;
			END
			ELSE IF @iEditionId = 9
			BEGIN
				-- insert All resource module of license for Library Edition
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6  then 0 else 1 end as Status FROM ResourceModule;
			END

			INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty) 
			VALUES(@iLicenseId, @iEditionId, @iTotalLogins, @iModesty)
			SET @iLicenseEditionId = SCOPE_IDENTITY()
			
			INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive, 
			CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sPassword, @sLicenseeFname, @sLicenseeLname, 
			@iUserTypeId, @sEmailId, @iActive, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
			SET @iAIAUserId = SCOPE_IDENTITY()
			
			INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId)

		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
    
END
GO
/****** Object:  StoredProcedure [dbo].[InsertSingleLicenseAccount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic Software
-- ALTER date: 22-Apr-2009
-- Description:	To ALTER a new test license account
-- =============================================
ALTER PROCEDURE [dbo].[InsertSingleLicenseAccount] 
	-- Add the parameters for the stored procedure here
	@sAccountNumber varchar(50), @sLicenseeFname varchar(50), @sLicenseeLname varchar(50), @iAccountTypeId tinyint,
	@sInstitutionName varchar(100)='', @sAddress1 varchar(100)='', @sAddress2 varchar(100)='', @sCity varchar(50)='', 
	@sZip varchar(20)='', @iStateId int, @iCountryId int, @sPhone varchar(30)='', @sEmailId varchar(50), @iTotalLogins int,
	@sStartDate varchar(20), @sEndDate varchar(20), @sEditionList varchar(256), @iPrice numeric(14,2),@sProductKey varchar(50),
	@sLoginId varchar(50), @sPassword varchar(50), @iSecurityQuesId tinyint, @sSecurityAnswer varchar(50), @iCreatorId int,@iNoofImages int
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @cEditionLogins CURSOR
			DECLARE @iLicenseId INT
			DECLARE @iSiteId INT
			DECLARE @iLicenseEditionId INT
			DECLARE @iIsDistrictSiteAccount TINYINT
			DECLARE @iLicenseTypeId TINYINT
			DECLARE @iUserTypeId TINYINT
			DECLARE @iAIAUserId INT
			DECLARE @iActive TINYINT
			DECLARE @iIsMasterIP TINYINT
			DECLARE @iModesty TINYINT
			DECLARE @dtStartDate DATETIME
			DECLARE @dtEndDate DATETIME
			DECLARE @sErrorStatus CHAR(2)
			DECLARE @dtCurrentDate DATETIME
			DECLARE @sitem VARCHAR(100)
			DECLARE @sRecordDelimiter CHAR(1)
			DECLARE @sEditionLoginDelimiter CHAR(1)
			DECLARE @sCountryCode VARCHAR(10)
			DECLARE @iIsInsEditionSelected TINYINT
			DECLARE @iIsLibEditionSelected TINYINT
			DECLARE @iIsAcademicLibEditionSelected TINYINT
			
			-- set the parameters to default values
			SET @iActive = 1
			SET @iIsDistrictSiteAccount = 0
			SET @iModesty = 0
			SET @sRecordDelimiter = '|'
			SET @sEditionLoginDelimiter = '-'
			SET @dtCurrentDate = getdate()
			SET @sErrorStatus = 'ok'
			SET @iIsInsEditionSelected = 0;
			SET @iIsLibEditionSelected = 0;
			SET @iIsAcademicLibEditionSelected = 0;
			
			IF @iStateId = 0
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			-- set the state to Other if the country is Non-US
			SET @sCountryCode = (SELECT CountryCode from Country WHERE Id = @iCountryId)
			IF @sCountryCode != 'US'
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			IF @iSecurityQuesId = 0
			BEGIN
				SET @iSecurityQuesId = NULL
			END
			IF LEN(@sSecurityAnswer) = 0
			BEGIN
				SET @sSecurityAnswer = NULL
			END
			-- convert the datatype of startdate & enddate parameter to datetime
			SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
			SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
						
			-- fetch the licensetypeid of the single license
			SELECT @iLicenseTypeId = Id from LicenseType WHERE Title = 'Single License'
			-- fetch the usertypeid of the single user
			SELECT @iUserTypeId = Id from UserType WHERE Title = 'Single User'

			
			INSERT INTO License(AccountNumber, LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId, 
			InstitutionName, EmailId, Address1, Address2, City, Zip, StateId, CountryId, Phone, TotalLogins, IsActive, 
			IsDistrictSiteLicense, CreationDate,ProductId) VALUES (@sAccountNumber, @sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, 
			@iAccountTypeId, @sInstitutionName, @sEmailId, @sAddress1, @sAddress2, @sCity, @sZip, @iStateId, @iCountryId,
			@sPhone, @iTotalLogins, @iActive, @iIsDistrictSiteAccount, @dtCurrentDate,@sProductKey)
			-- to get the last inserted license id identity value in the current session
			SET @iLicenseId = SCOPE_IDENTITY()
			
			INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
			TotalAmount, AmountPaid,NoofImages) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @iPrice, @iPrice ,@iNoofImages)

			SET @cEditionLogins = CURSOR FAST_FORWARD FOR SELECT item FROM dbo.fnSplit(@sEditionList,@sRecordDelimiter)
			OPEN @cEditionLogins
			FETCH NEXT FROM @cEditionLogins INTO @sitem
			WHILE @@FETCH_STATUS = 0
			BEGIN
				INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty) 
				SELECT @iLicenseId, SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1), 
					SUBSTRING(@sitem,CHARINDEX(@sEditionLoginDelimiter,@sitem)+1,LEN(@sitem)), @iModesty
				
				-- chekc if selected edition is instructor or library edition
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) <= 4
				BEGIN
					SET @iIsInsEditionSelected = 1;
				END
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) > 4
				BEGIN
					SET @iIsLibEditionSelected = 1;
				END
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) = 9
				BEGIN
					SET @iIsAcademicLibEditionSelected = 1;
				END

				FETCH NEXT FROM @cEditionLogins INTO @sitem
			END
			SET @iLicenseEditionId = SCOPE_IDENTITY()
			INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive, SecurityQuestionId, SecurityAnswer, 
			CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sPassword, @sLicenseeFname, @sLicenseeLname, 
			@iUserTypeId, @sEmailId, @iActive, @iSecurityQuesId, @sSecurityAnswer, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
			SET @iAIAUserId = SCOPE_IDENTITY()
			
			INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId)

			IF @iIsInsEditionSelected = 1 AND @iIsLibEditionSelected = 1
			BEGIN
				-- insert All resource module of license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id in (8,9,10) then 0 else 1 end as Status FROM ResourceModule  WHERE ResourceModule.Id <> 13;
			END
			ELSE IF @iIsInsEditionSelected = 1 AND @iIsLibEditionSelected = 0
			BEGIN
				-- insert All resource module of license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id > 7 then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
			END
			ELSE IF @iIsInsEditionSelected = 0 AND @iIsLibEditionSelected = 1
			BEGIN
				-- insert All resource module of license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6  then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
			END
			
			IF @iIsAcademicLibEditionSelected = 1
			BEGIN
				-- insert ADAM Image Resouce to license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId,  1 as Status FROM ResourceModule WHERE ResourceModule.Id = 13;
			END
			ELSE
			BEGIN
				-- insert ADAM Image Resouce to license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId,  0 as Status FROM ResourceModule WHERE ResourceModule.Id = 13;
			END

		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
    
END
GO
/****** Object:  StoredProcedure [dbo].[InsertResellerLicenseAccount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Author:		Magic Software
-- ALTER date: 20-May-2009
-- Description:	To ALTER new reseller license account
-- ====================================================
ALTER PROCEDURE [dbo].[InsertResellerLicenseAccount] 
	-- Add the parameters for the stored procedure here
	@sLicenseeFname varchar(50), @sLicenseeLname varchar(50), @iLicenseTypeId tinyint, @iAccountTypeId tinyint, 
	@sInstitutionName varchar(100)='', @sAddress1 varchar(100)='', @sAddress2 varchar(100)='', @sCity varchar(50)='', 
	@sZip varchar(20)='', @iStateId int, @iCountryId int, @sPhone varchar(30) = '', @sEmailId varchar(50), 
	@iTotalLogins int, @sStartDate varchar(20), @sEndDate varchar(20), @sEditionList varchar(256), 
	@iTotalPrice numeric(14,2), @iCreatorId	int, @sProductKey varchar(50),@iNoofImages int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @cEditionLogins CURSOR
			DECLARE @iLicenseId INT
			DECLARE @sAccountNumber VARCHAR(50)
			DECLARE @iLicenseEditionId INT
			DECLARE @iIsDistrictSiteAccount TINYINT
			DECLARE @iActive TINYINT
			DECLARE @iModesty TINYINT
			DECLARE @dtStartDate DATETIME
			DECLARE @dtEndDate DATETIME
			DECLARE @sErrorStatus CHAR(2)
			DECLARE @dtCurrentDate DATETIME
			DECLARE @sitem VARCHAR(100)
			DECLARE @sRecordDelimiter CHAR(1)
			DECLARE @sEditionLoginDelimiter CHAR(1)
			DECLARE @iCount INT
			DECLARE @iLicenseLogins INT
			DECLARE @iPrice NUMERIC(14,2)
			DECLARE @sPaymentMode VARCHAR(10)
			DECLARE @iUserTypeId TINYINT
			DECLARE @sLoginId VARCHAR(50)
			DECLARE @iAIAUserId INT
			DECLARE @iLastAIAUserId INT
			DECLARE @iUserExists TINYINT
			DECLARE @sCountryCode VARCHAR(10)
			
			-- ALTER temporary table to store newly created account number, loginid & password
			CREATE TABLE #LicenseDetail
			(
				AccountNumber VARCHAR(50),
				LoginId VARCHAR(50),
				Password VARCHAR(50)
			)
			
			-- set the parameters to default values
			SET @iCount = 1
			SET @iLicenseLogins = 1;
			SET @iActive = 1
			SET @iIsDistrictSiteAccount = 0
			SET @iModesty = 0
			SET @sPaymentMode = 'CASH'
			SET @sRecordDelimiter = '|'
			SET @sEditionLoginDelimiter = '-'
			SET @dtCurrentDate = getdate()
			SET @sErrorStatus = 'ok'
			
			IF @iStateId = 0
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			
			-- set the state to Other if the country is Non-US
			SET @sCountryCode = (SELECT CountryCode from Country WHERE Id = @iCountryId)
			IF @sCountryCode != 'US'
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			
			-- convert the datatype of startdate & enddate parameter to datetime
			SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
			SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
			
			-- fetch the id for the Reseller user type
			SELECT @iUserTypeId = Id FROM UserType WHERE Title = 'Reseller'
			
			-- calculate the price of an individual reseller license
			SET @iPrice = @iTotalPrice / @iTotalLogins
			
			-- get the edition id and total number of logins 
			SET @cEditionLogins = CURSOR FAST_FORWARD FOR SELECT item FROM dbo.fnSplit(@sEditionList,@sRecordDelimiter)
			OPEN @cEditionLogins
			FETCH NEXT FROM @cEditionLogins INTO @sitem
						
			WHILE(@iCount <= @iTotalLogins)
			BEGIN
				SET @iUserExists = 0
				-- ALTER a new reseller license
				INSERT INTO License(LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId, 
				InstitutionName, EmailId, Address1, Address2, City, Zip, StateId, CountryId, Phone, TotalLogins, IsActive, 
				IsDistrictSiteLicense, CreationDate, ProductId) VALUES (@sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, 
				@iAccountTypeId, @sInstitutionName, @sEmailId, @sAddress1, @sAddress2, @sCity, @sZip, @iStateId, @iCountryId,
				@sPhone, @iLicenseLogins, @iActive, @iIsDistrictSiteAccount, @dtCurrentDate, @sProductKey)
				-- to get the last inserted license id identity value in the current session
				SET @iLicenseId = SCOPE_IDENTITY()
				
				-- get a unique account number
				SET @sAccountNumber = 'AIARS'+RIGHT('000000'+CONVERT(varchar(10),@iLicenseId),6)
				
				UPDATE License SET AccountNumber = @sAccountNumber WHERE Id = @iLicenseId
				
				-- insert the subscription detail of license
				INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
				PaymentMode, TotalAmount, AmountPaid,NoofImages) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @sPaymentMode, @iPrice, @iPrice,@iNoofImages)
				
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) <= 4
				BEGIN
					-- insert All resource module of license for Instructor Edition
					INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id > 7 then 0 else 1 end as Status FROM ResourceModule;
				END
				ELSE IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) = 8
				BEGIN
					-- insert All resource module of license for Library Edition
					INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6 or ResourceModule.id = 13 then 0 else 1 end as Status FROM ResourceModule;
				END
				ELSE IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) = 9
				BEGIN
					-- insert All resource module of license for Library Edition
					INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6 then 0 else 1 end as Status FROM ResourceModule;
				END
				
				-- insert the mapping of license with edition
				INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty) 
				SELECT @iLicenseId, SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1), 
					@iLicenseLogins, @iModesty
				
				-- to get the last inserted licenseedition id identity value in the current session
				SET @iLicenseEditionId = SCOPE_IDENTITY()
				
				-- fetch the last auto incremented id of aiauser table
				SET @iLastAIAUserId = (SELECT MAX(Id) FROM AIAUser)
				
				-- ALTER a loginid for user
				SET @sLoginId = 'RS'+CAST(convert(varchar,@dtCurrentDate,12)as varchar)+char(@iLastAIAUserId/260000%26+65)+
					char(@iLastAIAUserId/10000%26+65)+ char(@iLastAIAUserId/1000%10+48)+char(@iLastAIAUserId/100%10+48)+
					char(@iLastAIAUserId/10%10+48)+char(@iLastAIAUserId%10+48)
				
				-- check if the created loginid already exists if yes then ALTER a new one
				SET @iUserExists = (SELECT 1 FROM AIAUser WHERE LoginId = @sLoginId)
				WHILE @iUserExists > 0
				BEGIN
					SET @iLastAIAUserId = @iLastAIAUserId + 1
					SET @sLoginId = 'RS'+CAST(convert(varchar,@dtCurrentDate,12)as varchar)+char(@iLastAIAUserId/260000%26+65)+
						char(@iLastAIAUserId/10000%26+65)+ char(@iLastAIAUserId/1000%10+48)+char(@iLastAIAUserId/100%10+48)+
						char(@iLastAIAUserId/10%10+48)+char(@iLastAIAUserId%10+48)
					SET @iUserExists = (SELECT 1 FROM AIAUser WHERE LoginId = @sLoginId)
				END
				
				-- ALTER a user for reseller account
				INSERT INTO AIAUser (LoginId, Password, UserTypeId, IsActive, CreatorId, CreationDate, ModifierId, ModifiedDate)
					VALUES(@sLoginId, @sLoginId, @iUserTypeId, @iActive, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
				-- to get the last inserted aiauser id identity value in the current session
				SET @iAIAUserId = SCOPE_IDENTITY()
				-- insert the mapping of user with license edition
				INSERT INTO AIAUserToLicenseEdition (UserId, LicenseEditionId) VALUES (@iAIAUserId, @iLicenseEditionId)
				
				-- insert newly created account number, loginid & password into temporary table
				INSERT INTO #LicenseDetail (AccountNumber,LoginId,Password) VALUES(@sAccountNumber, @sLoginId, @sLoginId)
				
				SET @iCount = @iCount+1
			END
		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus,AccountNumber,LoginId,Password FROM #LicenseDetail
		-- drop the temporary table
		DROP TABLE #LicenseDetail
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus,'' as AccountNumber,'' as LoginId,'' as Password 
	END CATCH
    
END
GO
/****** Object:  StoredProcedure [dbo].[InsertNewLicenseAccount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic Software
-- ALTER date: 22-Apr-2009
-- Description:	To ALTER a new license account
-- =============================================
ALTER PROCEDURE [dbo].[InsertNewLicenseAccount] 
	-- Add the parameters for the stored procedure here
	@sAccountNumber varchar(50), @sLicenseeFname varchar(50), @sLicenseeLname varchar(50),
	@iLicenseTypeId tinyint, @iAccountTypeId tinyint, @sInstitutionName varchar(100)='', @sAddress1 varchar(100)='', 
	@sAddress2 varchar(100)='', @sCity varchar(50)='', @sZip varchar(20)='', @iStateId int, @iCountryId int, 
	@sPhone varchar(30) = '', @sEmailId varchar(50), @iTotalLogins int, @sStartDate varchar(20), 
	@sEndDate varchar(20), @sMasterIP varchar(100) = '', @sEditionList varchar(256), @iPrice numeric(14,2),@sProductKey varchar(50),	
	@sSiteIPTo varchar(100) = '',@sSiteMasterIPTo varchar(100) = '',@iNoofImages int
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @cEditionLogins CURSOR
			DECLARE @iLicenseId INT
			DECLARE @iSiteId INT
			DECLARE @iLicenseEditionId INT
			DECLARE @iIsDistrictSiteAccount TINYINT
			DECLARE @iActive TINYINT
			DECLARE @iIsMasterIP TINYINT
			DECLARE @iModesty TINYINT
			DECLARE @dtStartDate DATETIME
			DECLARE @dtEndDate DATETIME
			DECLARE @sErrorStatus CHAR(2)
			DECLARE @dtCurrentDate DATETIME
			DECLARE @sitem VARCHAR(100)
			DECLARE @sRecordDelimiter CHAR(1)
			DECLARE @sEditionLoginDelimiter CHAR(1)
			DECLARE @sCountryCode VARCHAR(10)
			DECLARE @iIsInsEditionSelected TINYINT
			DECLARE @iIsLibEditionSelected TINYINT
			DECLARE @iIsAcademicLibEditionSelected TINYINT

			-- set the parameters to default values
			SET @iActive = 1
			SET @iIsMasterIP = 1
			SET @iIsDistrictSiteAccount = 0
			SET @iModesty = 0
			SET @sRecordDelimiter = '|'
			SET @sEditionLoginDelimiter = '-'
			SET @dtCurrentDate = getdate()
			SET @sErrorStatus = 'ok'
			SET @iIsInsEditionSelected = 0;
			SET @iIsLibEditionSelected = 0;
			SET @iIsAcademicLibEditionSelected = 0;
			
			IF @iStateId = 0
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			-- set the state to Other if the country is Non-US
			SET @sCountryCode = (SELECT CountryCode from Country WHERE Id = @iCountryId)
			IF @sCountryCode != 'US'
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			
			-- convert the datatype of startdate & enddate parameter to datetime
			SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
			SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
			
			INSERT INTO License(AccountNumber, LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId, 
			InstitutionName, EmailId, Address1, Address2, City, Zip, StateId, CountryId, Phone, TotalLogins, IsActive, 
			IsDistrictSiteLicense, CreationDate,ProductId) VALUES (@sAccountNumber, @sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, 
			@iAccountTypeId, @sInstitutionName, @sEmailId, @sAddress1, @sAddress2, @sCity, @sZip, @iStateId, @iCountryId,
			@sPhone, @iTotalLogins, @iActive, @iIsDistrictSiteAccount, @dtCurrentDate,@sProductKey)
			-- to get the last inserted license id identity value in the current session
			SET @iLicenseId = SCOPE_IDENTITY()
			
			INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
			TotalAmount, AmountPaid,NoofImages) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @iPrice, @iPrice,@iNoofImages)
			
			-- check if license is site license
			IF @iLicenseTypeId = 3
			BEGIN
				INSERT INTO Site (SiteIP, Title, InstituteName, Address1, Address2, City, Zip, Phone,
				StateId, CountryId, IsMaster, IsActive, CreationDate, SiteIPTo, SiteMasterIpTo)
				VALUES(@sMasterIP, @sMasterIP, @sInstitutionName, @sAddress1, @sAddress2, @sCity, @sZip, @sPhone,
				@iStateId, @iCountryId, @iIsMasterIP, @iActive, @dtCurrentDate,@sSiteIPTo, @sSiteMasterIPTo)
				-- to get the last inserted site id identity value in the current session
				SET @iSiteId = SCOPE_IDENTITY()
			END
			
			SET @cEditionLogins = CURSOR FAST_FORWARD FOR SELECT item FROM dbo.fnSplit(@sEditionList,@sRecordDelimiter)
			OPEN @cEditionLogins
			FETCH NEXT FROM @cEditionLogins INTO @sitem
			WHILE @@FETCH_STATUS = 0
			BEGIN
				INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty) 
				SELECT @iLicenseId, SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1), 
					SUBSTRING(@sitem,CHARINDEX(@sEditionLoginDelimiter,@sitem)+1,LEN(@sitem)), @iModesty
				
				-- chekc if selected edition is instructor or library edition
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) <= 4
				BEGIN
					SET @iIsInsEditionSelected = 1;
				END
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) >  4
				BEGIN
					SET @iIsLibEditionSelected = 1;
				END
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) =  9
				BEGIN
					SET @iIsAcademicLibEditionSelected = 1;
				END

				-- check if license is site license
				IF @iLicenseTypeId = 3
				BEGIN
					-- to get the last inserted licenseedition id identity value in the current session
					SET @iLicenseEditionId = SCOPE_IDENTITY()
					INSERT INTO SiteToLicenseEdition (SiteId, LicenseEditionId, IsModesty) VALUES (@iSiteId, @iLicenseEditionId, @iModesty)		
				END
				FETCH NEXT FROM @cEditionLogins INTO @sitem
			END
			
			IF @iIsInsEditionSelected = 1 AND @iIsLibEditionSelected = 1
			BEGIN
				-- insert All resource module of license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id in (8,9,10) then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
			END
			ELSE IF @iIsInsEditionSelected = 1 AND @iIsLibEditionSelected = 0
			BEGIN
				-- insert All resource module of license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id > 7 then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
			END
			ELSE IF @iIsInsEditionSelected = 0 AND @iIsLibEditionSelected = 1
			BEGIN
				-- insert All resource module of license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6  then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
			END

			IF @iIsAcademicLibEditionSelected = 1
			BEGIN
				-- insert ADAM Image Resouce to license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId,  1 as Status FROM ResourceModule WHERE ResourceModule.Id = 13;
			END
			ELSE
			BEGIN
				-- insert ADAM Image Resouce to license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId,  0 as Status FROM ResourceModule WHERE ResourceModule.Id = 13;
			END
			
			
		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
    
END
GO
/****** Object:  StoredProcedure [dbo].[InsertDemoLicenseAccount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertDemoLicenseAccount]             
AS            
BEGIN            
             
 -- SET NOCOUNT ON added to prevent extra result sets from            
 -- interfering with SELECT statements.            
 SET NOCOUNT ON;            
             
 BEGIN TRY            
  BEGIN TRANSACTION            
   DECLARE @sAccountNumber varchar(50)             
   DECLARE @sLoginId varchar(50)            
   DECLARE @iLicenseId int            
   DECLARE @iLicenseEditionId int            
   DECLARE @iAIAUserId int            
   DECLARE @dtStartDate datetime            
   DECLARE @dtEndDate datetime            
   DECLARE @iLastAIAUserId int            
   DECLARE @sMailBody  varchar(1000)            
               
   DECLARE @sLicenseeFname varchar(50) = 'Guest'            
   DECLARE @sLicenseeLname varchar(50) = 'Account'            
   DECLARE @sEmailId varchar(50) = 'edsales@adamcorp.com'            
   -- license type of test account            
   DECLARE @iLicenseTypeId tinyint = 5            
   -- user type of test account            
   DECLARE @iUserTypeId tinyint = 8            
   -- set account type to High School            
   DECLARE @iAccountTypeId tinyint = 1            
   DECLARE @iAmount tinyint = 0            
   DECLARE @iTotalLogins tinyint = 100            
   DECLARE @iActive tinyint = 1            
   DECLARE @iModesty tinyint = 0            
   DECLARE @dtCurrentDate datetime = getdate()            
   DECLARE @iCountryId int = 233            
   DECLARE @iStateId int = 51            
   -- set edition to Instructor's Edition            
   DECLARE @iEditionId tinyint = 1            
   DECLARE @iCreatorId int = 1            
   DECLARE @iLicenseExists tinyint = 0            
   DECLARE @iLicenseCounter int            
   DECLARE @sErrorStatus char(2) = 'ok'            
            
   -- set the startdate to current date & enddate to seven days ahead of current date            
   SELECT @dtStartDate = @dtCurrentDate            
   SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,7,CONVERT(DATETIME,(CONVERT(VARCHAR,@dtCurrentDate,101)))))            
               
   -- ALTER a new guest license account            
   INSERT INTO License(LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId,             
   EmailId, Zip, StateId, CountryId, TotalLogins, IsActive, IsDistrictSiteLicense,             
   CreationDate) VALUES (@sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, @iAccountTypeId,            
   @sEmailId, '', @iStateId, @iCountryId, @iTotalLogins, @iActive, 0, @dtCurrentDate )            
   -- to get the last inserted identity value in the current session            
   SET @iLicenseId = SCOPE_IDENTITY()            
               
   SET @sAccountNumber = 'AIAGA'+RIGHT('000000'+CONVERT(varchar(10),@iLicenseId),6)            
               
   -- check if the created account number already exists if yes then ALTER a new one            
   SET @iLicenseExists = (SELECT 1 FROM License WHERE AccountNumber = @sAccountNumber)            
   SET @iLicenseCounter = @iLicenseId            
   WHILE @iLicenseExists > 0            
   BEGIN            
    SET @iLicenseCounter = @iLicenseCounter + 1            
    SET @sAccountNumber = 'AIAGA'+RIGHT('000000'+CONVERT(varchar(10),@iLicenseCounter),6)            
    SET @iLicenseExists = (SELECT 1 FROM License WHERE AccountNumber = @sAccountNumber)            
   END            
   UPDATE License SET AccountNumber = @sAccountNumber WHERE Id = @iLicenseId            
               
   INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,            
   TotalAmount, AmountPaid, AmountPending) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @iAmount, @iAmount, @iAmount)            
               
   -- insert resource module of license            
   INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId,1 as Status             
   FROM ResourceModule            
               
   INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty)             
   VALUES(@iLicenseId, @iEditionId, @iTotalLogins, @iModesty)            
   SET @iLicenseEditionId = SCOPE_IDENTITY()            
               
   -- fetch the last auto incremented id of aiauser table            
   SET @iLastAIAUserId = (SELECT MAX(Id) FROM AIAUser)            
               
   -- ALTER a loginid for user            
   SET @sLoginId = 'GA'+char(@iLastAIAUserId/260000%26+65)+            
    char(@iLastAIAUserId/10000%26+65)+ char(@iLastAIAUserId/1000%10+48)+char(@iLastAIAUserId/100%10+48)+            
    char(@iLastAIAUserId/10%10+48)+char(@iLastAIAUserId%10+48)            
               
   INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive,             
   CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sLoginId, @sLicenseeFname, @sLicenseeLname,             
   @iUserTypeId, @sEmailId, @iActive, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)            
   SET @iAIAUserId = SCOPE_IDENTITY()            
               
   INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId)            
               
   SET @sMailBody = 'Daily demo Account has been created:<br><br>            
       <b>User Id</b>: '+@sLoginId+              
       '<br><b>Password</b>: '+@sLoginId+            
       '<br><br> This demo account will be active for 7 days effective '+CONVERT(VARCHAR,@dtStartDate,101)+' to '+CONVERT(VARCHAR,@dtEndDate,101);            
               
  COMMIT TRANSACTION            
              
  EXEC msdb..sp_send_dbmail            
   @profile_name='MailProfile',            
            @recipients = 'info@tfei.org.uk;rahulr@Ebix.com;kpaul@ebix.com;digitalproducts@flr.follett.com;jennifer.hickey@ebix.com;renata.nascimento@ebix.com;thaisa.braguim@ebix.com;DigitalContentTrials@FollettSoftware.com;luis.maida@rackspace.ebix.com;Daniela.Laiati@rackspace.ebix.com;Deshawn.cousette@ebix.com;Melanie.foye@ebix.com;Deidre.Friday@Ebix.com;Ashish.jain@ebix.com;Amrita.vishnoi@ebix.com;ytyagi@ebix.com;naina.sehgal@ebix.com;',
            @body = @sMailBody,             
            @body_format = 'HTML',            
            @subject = 'AIA - Demo Account Created.'             
              
  SELECT @sErrorStatus as SPStatus            
 END TRY            
 BEGIN CATCH            
  IF @@TRANCOUNT > 0            
   ROLLBACK TRANSACTION            
  SELECT Error_Message() as SPStatus            
 END CATCH            
END
GO
/****** Object:  StoredProcedure [dbo].[InsertAIAUser]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic Software
-- ALTER date: 29-Apr-2009
-- Description:	To ALTER a new AIA User
-- =============================================
ALTER PROCEDURE [dbo].[InsertAIAUser] 
	-- Add the parameters for the stored procedure here
	@sLoginId varchar(50), @sPassword varchar(50), @sFirstname varchar(50), @sLastname varchar(50),
	@iUserTypeId tinyint, @sEmailId varchar(50), @iSecurityQuesId tinyint, @sSecurityAnswer varchar(50)='',
	@iCreatorId int, @iLicenseId int, @iEditionId tinyint
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @iLicenseEditionId int
			DECLARE @iAIAUserId int
			DECLARE @iActive tinyint
			DECLARE @dtCurrentDate datetime
			DECLARE @sErrorStatus char(2)
			DECLARE @sInvalidLicenseToEdition varchar(100)
			-- to store the user type id of general admin
			DECLARE @iGAUserTypeId tinyint
			-- to store the role id of general admin
			DECLARE @iGARoleId tinyint

			-- set the parameters to default values
			SET @iActive = 1
			SET @dtCurrentDate = getdate()
			SET @sErrorStatus = 'ok'
			SET @sInvalidLicenseToEdition = 'Edition does not exists for this license.'

			-- fetch the usertype id of the general admin
			SELECT @iGAUserTypeId = Id FROM UserType WHERE Title = 'General Admin'
			-- fetch the role id of the general admin
			SELECT @iGARoleId = Id FROM Role WHERE Title = 'General Admin Role'
			
			IF @iSecurityQuesId = 0
			BEGIN
				SET @iSecurityQuesId = NULL
			END
			IF LEN(@sSecurityAnswer) = 0
			BEGIN
				SET @sSecurityAnswer = NULL
			END
			-- insert the user detail in AIAUser 
			INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive, SecurityQuestionId, 
			SecurityAnswer, CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sPassword, @sFirstname, 
			@sLastname, @iUserTypeId, @sEmailId, @iActive, @iSecurityQuesId, @sSecurityAnswer,
			@iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
			SET @iAIAUserId = SCOPE_IDENTITY()
			-- if user type is general admin then inserts map its role with newly generated UserId
			IF @iUserTypeId = @iGAUserTypeId
			BEGIN
				-- insert the mapping of user with role into AIAUserActivity
				INSERT INTO AIAUserActivity(UserId, RoleId) VALUES(@iAIAUserId, @iGARoleId)		
			END
			ELSE
			BEGIN
				-- select the id of edition mapped with the license id
				SELECT @iLicenseEditionId = LicenseToEdition.Id FROM LicenseToEdition
				WHERE LicenseToEdition.LicenseId = @iLicenseId AND LicenseToEdition.EditionId = @iEditionId
				IF @@ROWCOUNT = 0
				BEGIN
					RAISERROR(@sInvalidLicenseToEdition,16,61)
				END
				-- insert the mapping of user with license edition into AIAUserToLicenseEdition 
				INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId)		
			END 
	
		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
    
END
GO
/****** Object:  StoredProcedure [dbo].[GetSiteIpByAccountNumber]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- alter script of GetSiteIpByAccountNumber SP

ALTER PROCEDURE [dbo].[GetSiteIpByAccountNumber]
@AccountNumner varchar(max)
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;

SELECT DISTINCT Site.Id,ISNULL(SUBSTRING(REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''),0,
	CHARINDEX('/',REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''))),'0') as SiteIp,
	ISNULL(SUBSTRING(REPLACE(REPLACE(REPLACE(Site.SiteIPTo+'/','www.',''),'http://',''),'https://',''),0,
	CHARINDEX('/',REPLACE(REPLACE(REPLACE(Site.SiteIPTo+'/','www.',''),'http://',''),'https://',''))),'0') as SiteIpTo,
	ISNULL(SUBSTRING(REPLACE(REPLACE(REPLACE(Site.SiteMasterIpTo+'/','www.',''),'http://',''),'https://',''),0,
	CHARINDEX('/',REPLACE(REPLACE(REPLACE(Site.SiteMasterIpTo+'/','www.',''),'http://',''),'https://',''))),'0') as SiteMasterIpTo
FROM (((SITE INNER JOIN SiteToLicenseEdition on Site.Id = SiteToLicenseEdition.SiteId)
		   INNER JOIN LicenseToEdition on SiteToLicenseEdition.LicenseEditionId = LicenseToEdition.Id)
		   INNER JOIN License on LicenseToEdition.LicenseId = License.Id)
WHERE ISNUMERIC(REPLACE(SUBSTRING(REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''),0,
	CHARINDEX('/',REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''))),'.',''))=1
AND License.AccountNumber = @AccountNumner
	
END
GO
/****** Object:  StoredProcedure [dbo].[GetSiteAccoutDetail]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 14/4/2009
-- Description:	Fetch building level accounts details for corresponding given Account Number.
-- =============================================
ALTER PROCEDURE [dbo].[GetSiteAccoutDetail] 
	-- Add the parameters for the stored procedure here
	@strAccountNumber varchar(50)=''
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Select Site.Id,Site.SiteIp,Site.Title,ISNULL(Site.SiteIPTo,'') as SiteIPTo,ISNULL(Site.SiteMasterIPTo,'') as SiteMasterIPTo,
	CONVERT(VARCHAR,Site.CreationDate,101) as CreationDate,
	CONVERT(VARCHAR,Site.ModifiedDate,101) as ModifiedDate,
		Site.InstituteName,Site.Department, AIAUser.Id as UserId,AIAUser.FirstName,AIAUser.EmailId
		From ((Site INNER JOIN AIAUserToSite on Site.Id=AIAUserToSite.SiteId)
					INNER JOIN AIAUser on AIAUserToSite.UserId = AIAUser.Id)
		Where Site.IsActive=1 and Site.id in 
			(Select SiteID From SiteToLicenseEdition Where LicenseEditionId in 
				(Select Id From LicenseToEdition Where LicenseId in 
					(Select Id From License Where LicenseTypeId=3 and AccountNumber=@strAccountNumber)))
END
GO
/****** Object:  StoredProcedure [dbo].[GetUserInGroup]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Magic Software
-- ALTER date: 18-May-2009
-- Description:	To get the list of all user of a account with the associated group
-- =============================================
ALTER PROCEDURE [dbo].[GetUserInGroup] 
	-- Add the parameters for the stored procedure here
	@iLicenseId int, @iGroupId int
AS
BEGIN
	-- returns the metadata
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	SELECT AIAUser.Id, AIAUser.FirstName, AIAUser.LastName, AIAUser.LoginId, 
	AIAUser.EmailId, Edition.Title, 
	(CASE WHEN UserGroupToAIAUser.UserId IS NULL THEN 0 ELSE 1 END) AS InGroup
			FROM 
			AIAUser 
			INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
			INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
			INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId
			INNER JOIN License ON LicenseToEdition.LicenseId = License.Id AND License.IsActive = 1
			INNER JOIN UserGroupToAIAUser ON UserGroupToAIAUser.UserId = AIAUser.Id 
			AND UserGroupToAIAUser.UserGroupId = @iGroupId 
			WHERE License.Id = @iLicenseId AND AIAUser.UserTypeId = 6
			
END
GO
/****** Object:  StoredProcedure [dbo].[GetUsageReport]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUsageReport] 
	-- Add the parameters for the stored procedure here
	-- FromDate & ToDate are mandatory parameters
	@sFromDate varchar(20), @sToDate varchar(20), @sAccoutNumber varchar(50)='',
	@sZip varchar(20) = '', @iState int, @iCountry int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON
	DECLARE @cGetUserDetails CURSOR
	DECLARE @iUserId INT
	DECLARE @sAccountNumber VARCHAR(50)
	DECLARE @iCardNumber INT
	DECLARE @sLoginId VARCHAR(50)
	DECLARE @sFirstName VARCHAR(100)
	DECLARE @sLastName VARCHAR(100)
	DECLARE @sUserType VARCHAR(50)
	DECLARE @dtFromDate DATETIME
	DECLARE @dtToDate DATETIME
	DECLARE @dtLicenseCreationDate DATETIME
	DECLARE @sLicenseState VARCHAR(50)
	DECLARE @sLicenseZip VARCHAR(20)
	DECLARE @sLicenseCountry VARCHAR(50)
	DECLARE @sInstitutionName VARCHAR(100)
	DECLARE @iTotalLogins INT
	DECLARE @dtLastLogin DATETIME

		-- convert the datatype of fromdate & todate parameter to datetime
	SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
	SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
	
	-- ALTER a temporary table to store the results of users logged into the system within a particular time period
	CREATE TABLE #UsageReport
	(
		LoginId VARCHAR(50),
		FirstName VARCHAR(100),
		LastName VARCHAR(100),
		AccountNumber VARCHAR(50),
		CardNumber INT,
		UserType VARCHAR(50),
		LicenseCreationDate DATETIME,
		LicenseState VARCHAR(50),
		LicenseZip VARCHAR(20),
		LicenseCountry VARCHAR(50),
		InstitutionName VARCHAR(100),
		TotalLogins INT,
		LastLoginDate DATETIME
	)

	-- define the forward only, read-only cursor 
	SET @cGetUserDetails = CURSOR FAST_FORWARD 
	FOR 
		SELECT LoginDetail.UserId, COUNT(1) as TotalLogins, MAX(LoginDetail.LoginTime)
		FROM LoginDetail WHERE
		(LoginTime) BETWEEN @dtFromDate AND @dtToDate
		GROUP BY LoginDetail.UserId
		
	-- open & fetch the cursor variables into the local variables
	OPEN @cGetUserDetails 
		FETCH NEXT FROM @cGetUserDetails INTO @iUserId, @iTotalLogins, @dtLastLogin  
		-- start of while loop
		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- fetch account number, state, zip, country of the license to which the user is belonged		
			SELECT @sAccountNumber = License.AccountNumber,
			@dtLicenseCreationDate = License.CreationDate,
			@sInstitutionName = License.InstitutionName,
			@sLicenseState =  State.StateName,
			@sLicenseZip = License.Zip, 
			@sLicenseCountry = Country.CountryName, 
			@iCardNumber = (CASE WHEN License.CardNumber > 0 THEN License.CardNumber END)
			FROM AIAUserToLicenseEdition
			INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
			INNER JOIN License ON LicenseToEdition.LicenseId = License.Id
            INNER JOIN State ON License.StateId = State.Id
		    INNER JOIN Country ON License.CountryId = Country.Id
			WHERE AIAUserToLicenseEdition.UserId = @iUserId
			AND License.IsActive = 1
			AND License.AccountNumber = (CASE WHEN LEN(@sAccoutNumber)>0 THEN @sAccoutNumber ELSE License.AccountNumber END)
			AND State.Id = (CASE WHEN @iState > 0 THEN @iState ELSE State.Id END)
			AND Country.Id = (CASE WHEN @iCountry > 0 THEN @iCountry ELSE Country.Id END)
			AND License.Zip = (CASE WHEN LEN(@sZip)>0 THEN @sZip ELSE License.Zip END)	
			--AND License.LicenseTypeId <> 5 
			--AND License.Country = (CASE WHEN LEN(@sCountry)>0 THEN @sCountry ELSE License.Country END)
			
			-- check whether the above query returns any row
			IF @@Rowcount > 0
			BEGIN
				-- fetch loginid, firstname, lastname, usertype of the user
				SELECT @sLoginId = AIAUser.LoginId, @sFirstName = AIAUser.Firstname, 
				@sLastName = AIAUser.LastName, @sUserType = UserType.Title
				FROM AIAUser 
				INNER JOIN UserType ON AIAUser.UserTypeId = UserType.Id
				WHERE AIAUser.Id = @iUserId
				AND AIAUser.IsActive = 1
				
				IF @@Rowcount > 0
				BEGIN
				-- insert into the temporary table
				INSERT INTO #UsageReport 
				(LoginId, FirstName, LastName, AccountNumber,CardNumber ,UserType,LicenseCreationDate, LicenseState, LicenseZip,
				LicenseCountry,InstitutionName, TotalLogins, LastLoginDate) 
				VALUES(@sLoginId, @sFirstName, @sLastName, @sAccountNumber, @iCardNumber, @sUserType,@dtLicenseCreationDate,
				@sLicenseState, @sLicenseZip, @sLicenseCountry,@sInstitutionName, @iTotalLogins, @dtLastLogin)
				END
			END
			-- fetch the next record from cursor
			FETCH NEXT FROM @cGetUserDetails INTO @iUserId, @iTotalLogins, @dtLastLogin
		-- end of while loop
		END
	-- close the cursor to free up resources
	CLOSE @cGetUserDetails
	DEALLOCATE @cGetUserDetails

	-- Selecting the desired result from temporary table
	SELECT LoginId, FirstName, LastName, AccountNumber, CardNumber,UserType,CONVERT(VARCHAR,LicenseCreationDate,101) as LicenseCreationDate, LicenseZip, LicenseState,
	LicenseCountry,InstitutionName, TotalLogins, CONVERT(VARCHAR,LastLoginDate,101) as LastLogin FROM #UsageReport ORDER BY AccountNumber
    
	-- Dropping the temporary table
	DROP TABLE #UsageReport
END
GO
/****** Object:  StoredProcedure [dbo].[GetLicenseIdByUserId]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetLicenseIdByUserId]
(
	@iUserId int
)
AS
	SET NOCOUNT ON;
SELECT LicenseId FROM LicenseToEdition
INNER JOIN AIAUserToLicenseEdition on AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
WHERE  AIAUserToLicenseEdition.UserId = @iUserId
GO
/****** Object:  StoredProcedure [dbo].[GetLicenseIdBySiteUrl]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<magicsw>
-- ALTER date: <16/02/2010>
-- Description:	<Fetch all the module list>
-- =============================================
ALTER PROCEDURE [dbo].[GetLicenseIdBySiteUrl]
	-- Add the parameters for the stored procedure here
	@sLicenseAccount varchar(100), @iEditionId int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	SELECT distinct Site.Id,
	ISNULL(SUBSTRING(REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''),0,
		CHARINDEX('/',REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''))),'') as SiteIp,
	ISNULL(SUBSTRING(REPLACE(REPLACE(REPLACE(Site.SiteIPTo+'/','www.',''),'http://',''),'https://',''),0,
		CHARINDEX('/',REPLACE(REPLACE(REPLACE(Site.SiteIPTo+'/','www.',''),'http://',''),'https://',''))),'') as SiteIPTo,
	ISNULL(SUBSTRING(REPLACE(REPLACE(REPLACE(Site.SiteMasterIPTo+'/','www.',''),'http://',''),'https://',''),0,
		CHARINDEX('/',REPLACE(REPLACE(REPLACE(Site.SiteMasterIPTo+'/','www.',''),'http://',''),'https://',''))),'') as SiteMasterIPTo,Site.IsMaster
	FROM License INNER JOIN LicenseToEdition ON License.Id = LicenseToEdition.LicenseId 
	INNER JOIN SiteToLicenseEdition ON LicenseToEdition.Id = SiteToLicenseEdition.LicenseEditionId 
	INNER JOIN Site ON SiteToLicenseEdition.SiteId = Site.Id WHERE 
	(License.AccountNumber = @sLicenseAccount) AND (LicenseToEdition.EditionId = @iEditionId)


END
GO
/****** Object:  StoredProcedure [dbo].[GetLicenseEditionsForModesty]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 23/4/2009
-- Description:	Fetch Editions for modesty setting for given Account Number.
-- =============================================
ALTER PROCEDURE [dbo].[GetLicenseEditionsForModesty]
	-- Add the parameters for the stored procedure here
	@iLicenseId int, @iBuildingLevelId int
	
AS
BEGIN
	
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF @iBuildingLevelId = 0 
	BEGIN	
		SELECT Edition.Title, LicenseToEdition.IsModesty as IsModesty 
		FROM LicenseToEdition 
		INNER JOIN Edition ON LicenseToEdition.EditionId = Edition.Id
		WHERE LicenseToEdition.LicenseId=@iLicenseId and Edition.IsActive=1 ORDER BY Edition.Priority
	END
	ELSE
	BEGIN
		SELECT Edition.Title, SiteToLicenseEdition.IsModesty as IsModesty 
		FROM SiteToLicenseEdition 
		INNER JOIN LicenseToEdition ON SiteToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
		INNER JOIN Edition ON LicenseToEdition.EditionId = Edition.Id
		WHERE SiteToLicenseEdition.SiteId=@iBuildingLevelId and Edition.IsActive=1 ORDER BY Edition.Priority
	END
	
END
GO
/****** Object:  StoredProcedure [dbo].[GetLicenseBySiteId]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- ALTER date: <ALTER Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetLicenseBySiteId] 
	-- Add the parameters for the stored procedure here
	@sSiteId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT distinct License.AccountNumber, License.AccountTypeId, License.Address1, 
	License.Address2, License.City, License.CountryId, License.CreationDate, 
	License.EmailId, License.Id, License.InstitutionName, License.IsActive, 
	License.IsDistrictSiteLicense, License.IsTermsAccepted, License.LicenseTypeId, 
	License.LicenseeFirstName, License.LicenseeLastName, License.ModifiedDate, 
	License.NoOfRenewals, License.Phone, License.StateId, License.TotalLogins, 
	License.Zip, SiteToLicenseEdition.IsModesty FROM License INNER JOIN LicenseToEdition 
	ON License.Id = LicenseToEdition.LicenseId 
	INNER JOIN SiteToLicenseEdition 
	ON LicenseToEdition.Id = SiteToLicenseEdition.LicenseEditionId 
	INNER JOIN Site ON SiteToLicenseEdition.SiteId = Site.Id 
	WHERE Site.Id = @sSiteId
END
GO
/****** Object:  StoredProcedure [dbo].[GetLicenseByIPAndAccount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- ALTER date: <ALTER Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetLicenseByIPAndAccount] 
	-- Add the parameters for the stored procedure here
	@sSiteIP VARCHAR(100), @sAccountNumber VARCHAR(16), @iEditionId TINYINT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT License.AccountNumber, License.AccountTypeId, License.Address1, 
	License.Address2, License.City, License.CountryId, License.CreationDate, 
	License.EmailId, License.Id, License.InstitutionName, License.IsActive, 
	License.IsDistrictSiteLicense, License.IsTermsAccepted, License.LicenseTypeId, 
	License.LicenseeFirstName, License.LicenseeLastName, License.ModifiedDate, 
	License.NoOfRenewals, License.Phone, License.StateId, License.TotalLogins, 
	License.Zip, SiteToLicenseEdition.IsModesty FROM License INNER JOIN LicenseToEdition 
	ON License.Id = LicenseToEdition.LicenseId 
	INNER JOIN SiteToLicenseEdition 
	ON LicenseToEdition.Id = SiteToLicenseEdition.LicenseEditionId 
	INNER JOIN Site ON SiteToLicenseEdition.SiteId = Site.Id 
	WHERE (SUBSTRING(REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''),0,
	CHARINDEX('/',REPLACE(REPLACE(REPLACE(Site.SiteIP+'/','www.',''),'http://',''),'https://',''))) = @sSiteIP)
	AND (License.AccountNumber = @sAccountNumber) 
	AND (LicenseToEdition.EditionId = @iEditionId)
END
GO
/****** Object:  StoredProcedure [dbo].[GetEditionsBySiteAccount]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		magic
-- ALTER date: 23/4/2009
-- Description:	Fetch Editions for building level accounts for given Account Number and SiteId.
-- =============================================
ALTER PROCEDURE [dbo].[GetEditionsBySiteAccount]
	-- Add the parameters for the stored procedure here
	@intSiteId int,
	@strAccountNumber varchar(50)=''
	
AS
BEGIN
	
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT LicenseToEdition.Id,Edition.Title,
		(CASE WHEN (Select sum(1) From LicenseToEdition a  where id in
		(Select LicenseEditionId From SiteToLicenseEdition Where SiteId = @intSiteId) 
			and a.editionId = Edition.Id)>0 THEN 1 ELSE 0 END) as IsActive
	FROM (Edition Inner Join LicenseToEdition on Edition.Id = LicenseToEdition.EditionId)
	WHERE LicenseToEdition.LicenseId in 
		(SELECT Id FROM License WHERE LicenseTypeId=3 and AccountNumber= @strAccountNumber)
	ORDER BY Priority
END
GO
/****** Object:  StoredProcedure [dbo].[GetBlockedUserByAccNoAndType]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Magic Software
-- ALTER date: 12-May-2009
-- Description:	To get the list of blocked user who have attempt 5 times wrong login
-- =============================================
ALTER PROCEDURE [dbo].[GetBlockedUserByAccNoAndType] 
	-- Add the parameters for the stored procedure here
	@iUserTypeId tinyint, @iLicenseId int
AS
BEGIN
	-- returns the metadata
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	SELECT DISTINCT 
				AIAUser.Id, 
				AIAUser.FirstName, 
				AIAUser.LastName, 
				AIAUser.LoginId,
				AIAUser.Password,
				AIAUser.EmailId, 
				ISNULL(License.AccountNumber,'') AccountNumber, 
				IncorrectLoginAttempts.LoginTime
	FROM 
				IncorrectLoginAttempts 
				INNER JOIN AIAUser ON IncorrectLoginAttempts.UserId = AIAUser.Id
				INNER JOIN UserType ON AIAUser.UserTypeId = UserType.Id
				LEFT JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
				LEFT JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
				LEFT JOIN License ON LicenseToEdition.LicenseId = License.Id 
	WHERE 
			IncorrectLoginAttempts.CntIncorrectLogins >= 5 
			AND UserType.Priority >= (SELECT UserType.Priority FROM UserType WHERE UserType.Id=@iUserTypeId)
			AND ((@iLicenseId =0) OR (License.Id = @iLicenseId))
			AND License.IsActive = 1
END
GO
/****** Object:  StoredProcedure [dbo].[GetAllUserWithGroup]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Magic Software
-- ALTER date: 18-May-2009
-- Description:	To get the list of all user of a account with the associated group
-- =============================================
ALTER PROCEDURE [dbo].[GetAllUserWithGroup] 
	-- Add the parameters for the stored procedure here
	@iLicenseId int, @iGroupId int
AS
BEGIN
	-- returns the metadata
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	SELECT AIAUser.Id, AIAUser.FirstName, AIAUser.LastName, AIAUser.LoginId, 
	AIAUser.EmailId, Edition.Title, 
	(CASE WHEN UserGroupToAIAUser.UserId IS NULL THEN 0 ELSE 1 END) AS InGroup
			FROM 
			AIAUser 
			INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
			INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
			INNER JOIN Edition ON Edition.Id = LicenseToEdition.EditionId
			INNER JOIN License ON LicenseToEdition.LicenseId = License.Id AND License.IsActive = 1
			LEFT JOIN UserGroupToAIAUser ON UserGroupToAIAUser.UserId = AIAUser.Id 
			AND UserGroupToAIAUser.UserGroupId = @iGroupId 
			WHERE License.Id = @iLicenseId AND AIAUser.UserTypeId = 6
			
END
GO
/****** Object:  StoredProcedure [dbo].[GetAIALicenseDetails]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- ALTER date: <ALTER Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetAIALicenseDetails]
	-- Add the parameters for the stored procedure here
	@sStartDate varchar(20) = '', @sEndDate varchar(20) = '', @sAccoutNumber varchar(50)='', 
	@sLicenseeFirstName varchar(50)='', @sLicenseeLastName varchar(50)='', @iLicenseTypeId tinyint, 
	@sInstituteName varchar(100) = '', @sEmail varchar(50) = '', @iStateId int, @iCountryId int, @bisActive bit = 1
AS
BEGIN

	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select 
	LD.id as LicenseId,
	LD.AccountNumber,
	LD.LicenseType,
	LD.AccountType,
	LD.InstitutionName,
	LD.StateName as LicenseState,
	LD.CountryName as LicenseCountry,
	LD.EmailId,
	LD.CardNumber,
	LD.ProductId as ProductKey,
	(case 
    when len(LD.AdminName)>=990
    then left(LD.AdminName, 990) + '...' 
    else LD.AdminName end)
	 as ClientAdmin,
	(LD.LicenseeFirstName + ' ' + LD.LicenseeLastName) as  LicenseeName,
	(ISNULL(LD.Address1,'')+' '+ ISNULL(LD.Address2,'')+' ' +ISNULL(LD.City,'')) as ContactAddress,
	CONVERT(VARCHAR,LD.CreationDate,101) as EntryDate,
	(CASE LD.IsActive WHEN 1 THEN 'Active' ELSE 'Inactive' END) as LicenseStatus,
	ISNULL(CONVERT(VARCHAR,LD.ModifiedDate,101),'') as ModifyDate, 
	CONVERT(VARCHAR,LD.SubscriptionStartDate,101) as StartDate,
	ISNULL(CONVERT(VARCHAR,LD.SubscriptionRenewalDate,101),'') as RenewDate,
	CONVERT(VARCHAR,LD.SubscriptionEndDate,101) as EndDate,
	LD.NoofImages
	from
			(Select 
			L.id,
			L.AccountNumber,
			L.LicenseeFirstName,
			L.LicenseeLastName,
			L.LicenseTypeId,
			LT.Title as LicenseType,
			A.Title as AccountType,
			L.InstitutionName,
			L.Address1,
			L.Address2,
			L.City,
			L.StateId,
			S.StateName,
			L.CountryId,
			C.CountryName,
			L.EmailId,
			L.CreationDate,
			L.IsActive,
			L.ModifiedDate, 
			L.CardNumber,
			L.ProductId,
			STUFF((Select  ',' +  AIAUser.FirstName + ' ' + AIAUser.LastName 
					FROM LicenseToEdition 
					INNER JOIN AIAUserToLicenseEdition ON LicenseToEdition.Id = AIAUserToLicenseEdition.LicenseEditionId 
					INNER JOIN AIAUser ON AIAUserToLicenseEdition.UserId = AIAUser.Id 
					INNER JOIN UserType ON AIAUser.UserTypeId = UserType.Id
					WHERE LicenseToEdition.LicenseId = L.Id AND AIAUser.IsActive = 1
					AND UserType.Title in ('District Admin','Client Admin','Single User','Reseller')
			 FOR XML PATH ('')), 1, 1, '') AS AdminName,
			 (SELECT MAX(lsd.SubscriptionValidFrom)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionStartDate,
			 (SELECT MAX(lsd.RenewalDate)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionRenewalDate,
			 (SELECT MAX(lsd.SubscriptionValidThrough)  FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id) AS SubscriptionEndDate,
			 (SELECT TOP(1) lsd.NoofImages FROM LicenseSubscriptionDetail LSD WHERE LSD.LicenseId = L.Id order by lsd.SubscriptionValidFrom desc) AS NoofImages
			 from License L 
			 inner join LicenseType LT on L.LicenseTypeId = LT.Id
			 inner join AccountType A on L.AccountTypeId=A.Id
			 inner join State S on L.StateId =S.Id
			 inner join Country C on L.CountryId = C.Id) 
	as LD
	where
	((LEN(@sStartDate)=0) OR (SubscriptionStartDate >= (CONVERT(DATETIME,@sStartDate)))) AND
	((LEN(@sEndDate)=0) OR (SubscriptionEndDate <= (DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))))) AND
	((LEN(@sAccoutNumber)=0) OR (AccountNumber LIKE '%'+@sAccoutNumber+'%')) AND 
	((LEN(@sLicenseeFirstName)=0) OR (LicenseeFirstName LIKE '%'+@sLicenseeFirstName+'%')) AND 
	((LEN(@sLicenseeLastName)=0) OR (LicenseeLastName LIKE '%'+@sLicenseeLastName+'%')) AND 
	((LEN(@sInstituteName)=0) OR (InstitutionName LIKE '%'+@sInstituteName+ '%')) AND 
	((@iLicenseTypeId = 0) OR (LicenseTypeId = @iLicenseTypeId)) AND 
	((LEN(@sEmail)=0) OR (EmailId = @sEmail)) AND 
	((@iStateId =0) OR (StateId = @iStateId)) AND 
	((@iCountryId =0) OR (CountryId = @iCountryId))  AND 
	((@bisActive = 0) OR (isActive = @bisActive))
	
	order by CreationDate
	  
END
GO
/****** Object:  StoredProcedure [dbo].[EC_UpdateUser]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================================
-- Author:		Magic
-- ALTER date: 27-May-2009
-- Description:	To update record into AIAUser,License and LicenseSubscriptionDetail table
-- ===================================================================
ALTER PROCEDURE [dbo].[EC_UpdateUser]
	-- Add the parameters for the stored procedure here
	@sLoginID varchar(50),
	@sAmountPaid money, 
	@sAmountPending money
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;
DECLARE @iLicenseId varchar(20)	
SET @iLicenseId = (SELECT DISTINCT License.Id FROM AIAUser
     INNER JOIN AIAUserToLicenseEdition ON AIAUser.Id = AIAUserToLicenseEdition.UserId
     INNER JOIN LicenseToEdition ON AIAUserToLicenseEdition.LicenseEditionId = LicenseToEdition.Id
     INNER JOIN License ON LicenseToEdition.LicenseId = License.Id
     WHERE AIAUser.LoginId = @sLoginID)

-- Update the LicenseSubscriptionDetail table for the supplied LoginID
UPDATE LicenseSubscriptionDetail SET AmountPaid = @sAmountPaid, AmountPending = @sAmountPending
WHERE LicenseId = @iLicenseId

--Update the License status to Active for the LoginID
UPDATE License SET IsActive = 1 WHERE Id = @iLicenseId

--Update the AIAUser status to Active for the LooginID
UPDATE AIAUser SET IsActive = 1 WHERE LoginId = @sLoginID 
	
END
GO
/****** Object:  StoredProcedure [dbo].[EC_CreateUser]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EC_CreateUser] 
(
	-- Add the parameters for Licence table 
	@sAccountNumber varchar(50),
	@sLicenseeFirstName varchar(50),
	@sLicenseeLastName varchar(50),
	@sInstitutionName varchar(100),
	@sAddress1 varchar(100),
	@sAddress2 varchar(100),
	@iCountryId	int,
	@iStateId int,
	@sCity varchar(50),
	@sZip varchar(20),
	@sPhone varchar(30)=NULL,
	@sEmailId varchar(50),
	@tiAccountTypeId tinyint,
	@bIsActive bit,
	@dtCreationDate datetime,
	@ioutId int output,
	-- Add the parameters for LicenceSubscriptiontable
	@siSubscriptionPlanId smallint,
	@dtSubscriptionValidFrom datetime,
	@dtSubscriptionValidThrough datetime,
	@sPaymentMode varchar(20),
	@mTotalAmount money,
	@mAmountPaid money,	
	@mAmountPending money,
	-- Add the parameters for SingleUsertable
	@siClassSize smallint,
	@iInstitutionType int,
	@sDepartment varchar(50),
	@iAIARequired int,
	@sCourses varchar(500),
	@iCoursesConduct int,
	@iReferedBy int,
	@iInternetProduct int,
	@iMultimediaProduct int,
	@sOtherAdamProduct varchar(50),
	@bIsOtherAdamProduct bit,
	-- Add the parameters for AIAUser
	@sLoginId varchar(50),				
	@sPassword varchar(50),	
	@sFirstName varchar(100), 
	@sLastName varchar(100),
    @tiSecurityQuestionId tinyint,
	@sSecurityAnswer varchar(50),	
	@dtDeactivationDate datetime=NULL,
	@ioutUserId int out,
	-- Add the parameters for LicenseToEdition
	@tiEditionId tinyint,
	-- Add the parameters for LicenseToEdition
	@sStudentID varchar(50)=NULL,
	@ioutLicenseEditionId int out,
	@sVersion varchar(50),
	@iCardNumber int,
	@iDiscountCodeId Int =0,
    @desDiscountPercentage Decimal(5,2) =0,
	-- Add the Aod and Lite Parameters
	@iAod bit,
	@iLite bit
)
AS

--insert data into License table
INSERT INTO License (LicenseeFirstName,LicenseeLastName,LicenseTypeId,InstitutionName,Address1,Address2,CountryId,StateId,City,Zip,Phone,EmailId,TotalLogins,AccountTypeId,IsActive,IsDistrictSiteLicense,CreationDate,ModifiedDate,CancellationDate,NoOfRenewals,IsTermsAccepted,CardNumber)
			VALUES	(@sLicenseeFirstName,@sLicenseeLastName,2,@sInstitutionName,@sAddress1,@sAddress2,@iCountryId,@iStateId,@sCity,@sZip,@sPhone,@sEmailId,1,@tiAccountTypeId,@bIsActive,'False',@dtCreationDate,null,null,0,1,@iCardNumber) 
SELECT @ioutId = SCOPE_IDENTITY()

-- get a unique account number
IF @tiEditionId = 1
BEGIN
	SET @sAccountNumber = 'AIAI'+RIGHT('0000000'+CONVERT(varchar(10),@ioutId),6)
END
ELSE
BEGIN
	SET @sAccountNumber = 'AIAS'+RIGHT('0000000'+CONVERT(varchar(10),@ioutId),6)
END
UPDATE License SET AccountNumber = @sAccountNumber WHERE Id = @ioutId

--insert data into License subscription table
INSERT INTO LicenseSubscriptionDetail 
									(
										LicenseId,
										SubscriptionPlanId,
										SubscriptionValidFrom,
										SubscriptionValidThrough,
										PaymentMode,
										TotalAmount,
										AmountPaid,
										AmountPending,
										NoofImages
									)
							VALUES	(
										@ioutId,
										@siSubscriptionPlanId,
										@dtSubscriptionValidFrom,
										@dtSubscriptionValidThrough,
										@sPaymentMode,
										@mTotalAmount,
										@mAmountPaid,
										@mAmountPending,
										CASE @tiEditionId
											WHEN '1' THEN '100'
											WHEN '2' THEN '100'
											WHEN '8' THEN '100'
											WHEN '9' THEN '100'
											ELSE '0'
										END
									)
--insert data into SingleUserDetail
INSERT INTO SingleUserDetail (LicenseId,StudentID,ClassSize,InstitutionTypeId,Department,AIARequiredId,Courses,CourseConductId,ReferId,InternetProductId,MultimediaProductId,OtherAdamProduct,IsOtherAdamProduct)
			VALUES	(@ioutId,@sStudentID,@siClassSize,@iInstitutionType,@sDepartment,@iAIARequired,@sCourses,@iCoursesConduct,@iReferedBy,@iInternetProduct,@iMultimediaProduct,@sOtherAdamProduct,@bIsOtherAdamProduct)
			

IF @tiEditionId <= 4
BEGIN
	IF @iLite = 0
	BEGIN
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id <8
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id >7 and ResourceModule.Id < 1017
		IF (@iAod = 0)
		BEGIN
			INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id = 1017
		END
		IF (@iAod = 1)
		BEGIN
			INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id = 1017
		END
	END
	IF @iLite = 1
	BEGIN
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id in (1, 2, 4, 5, 7, 8, 10)
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id in (3, 6, 9)
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id >10 and ResourceModule.Id < 1017
		IF @iAod = 0
		BEGIN
			INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id in (1017)
		END
		IF @iAod = 1
		BEGIN
			INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id in (1017)
		END
	END
END
ELSE IF @tiEditionId = 8
BEGIN
	-- insert All resource module of license
	INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id < 11 and ResourceModule.Id <> 6;
	-- insert AP resource module of license
	INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id in (6,11,12);
END
ELSE 
BEGIN
	-- insert All resource module of license
	INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id < 11 and ResourceModule.Id <> 6;
	-- insert AP resource module of license
	INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id in (6,11,12,13);
END

-- insert the mapping of discount with license
IF @iDiscountCodeId != 0
BEGIN
	INSERT INTO DiscountToLicense (DiscountId, LicenseId, DiscountPercentage) 
		VALUES(@iDiscountCodeId, @ioutId, @desDiscountPercentage)
END
--insert data into AIAUser
INSERT INTO AIAUser (LoginId,Password,FirstName,LastName,UserTypeId,EmailId,IsActive,SecurityQuestionId,SecurityAnswer,CreatorId,CreationDate,ModifierId,ModifiedDate,DeactivationDate)
			VALUES	(@sLoginId,@sPassword,@sFirstName,@sLastName,5,@sEmailId,@bIsActive,@tiSecurityQuestionId,@sSecurityAnswer,null,@dtCreationDate,null,@dtCreationDate,@dtDeactivationDate)			
			
SELECT @ioutUserId = SCOPE_IDENTITY()
--update AIAUser and set createrid with user id 	
UPDATE	AIAUser SET CreatorId=@ioutUserId, ModifierId=@ioutUserId where Id=@ioutUserId		
--insert data into LicenseToEdition
INSERT INTO LicenseToEdition (LicenseId,EditionId,TotalLogins,IsModesty)
			VALUES	(@ioutId,@tiEditionId,1,'True')

SELECT @ioutLicenseEditionId = SCOPE_IDENTITY()
--insert data into AIAUserToLicenseEdition table
INSERT INTO AIAUserToLicenseEdition (UserId,LicenseEditionId)
			VALUES (@ioutUserId,@ioutLicenseEditionId)

--update SingleUserDetail with StudentID ,if user is student then StudentId is updated with user id with prefix 'STU' else StudentId is null
/*IF  @sVersion='Student'
BEGIN
		UPDATE SingleUserDetail set StudentID='STU' + cast(@ioutUserId as varchar) where LicenseId=@ioutId
END
ELSE

BEGIN
		UPDATE SingleUserDetail set StudentID=null where LicenseId=@ioutId
END*/
GO
/****** Object:  StoredProcedure [dbo].[DeleteLicense]    Script Date: 06/06/2018 07:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic Software
-- ALTER date: 24-Sep-2009
-- Description:	to delete the license
-- =============================================
ALTER PROCEDURE [dbo].[DeleteLicense] 
	@iLicenseId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @iLicenseTypeId TINYINT
			DECLARE @cGetDetail CURSOR
			DECLARE @iSiteId INT
			DECLARE @iUserId INT
			DECLARE @sErrorStatus CHAR(2)
			
			SET @sErrorStatus = 'ok'
			SET @iLicenseTypeId = (SELECT LicenseTypeId FROM License WHERE Id = @iLicenseId)
			
			-- check if license is site license
			IF @iLicenseTypeId = 3 
			BEGIN
				-- delete records from tables which store information about building level account
				SET @cGetDetail = CURSOR FAST_FORWARD FOR SELECT SiteId FROM SiteToLicenseEdition WHERE LicenseEditionId IN (SELECT Id FROM LicenseToEdition WHERE LicenseId = @iLicenseId)
				OPEN @cGetDetail
				FETCH NEXT FROM @cGetDetail INTO @iSiteId
				WHILE @@FETCH_STATUS = 0
				BEGIN
					DELETE FROM AIAUserToSite WHERE SiteId = @iSiteId
					DELETE FROM SiteToLicenseEdition WHERE SiteId = @iSiteId
					DELETE FROM Site WHERE Id = @iSiteId
					
					FETCH NEXT FROM @cGetDetail INTO @iSiteId
				END
				CLOSE @cGetDetail
			END
			-- delete records from tables which store information about user
			SET @cGetDetail = CURSOR FAST_FORWARD FOR SELECT Userid FROM AIAUserToLicenseEdition WHERE LicenseEditionId IN (SELECT Id FROM LicenseToEdition WHERE LicenseId = @iLicenseId)
			OPEN @cGetDetail
			FETCH NEXT FROM @cGetDetail INTO @iUserId
			WHILE @@FETCH_STATUS = 0
			BEGIN
				DELETE FROM AIAUserToLicenseEdition WHERE Userid = @iUserId
				DELETE FROM LoginDetail WHERE UserId = @iUserId
				DELETE FROM SessionManager WHERE UserId = @iUserId
				DELETE FROM IncorrectLoginAttempts WHERE UserId = @iUserId
				DELETE FROM UserGroupToAIAUser WHERE UserId = @iUserId
				DELETE FROM AIAUser WHERE Id = @iUserId
								
				FETCH NEXT FROM @cGetDetail INTO @iUserId
			END
			CLOSE @cGetDetail
			-- delete records from tables which store information about the license
			DELETE FROM UserGroup WHERE LicenseId = @iLicenseId
			DELETE FROM LicenseToEdition WHERE LicenseId = @iLicenseId
			DELETE FROM SingleUserDetail WHERE LicenseId = @iLicenseId
			DELETE FROM LicenseSubscriptionDetail WHERE LicenseId = @iLicenseId
			DELETE FROM License WHERE Id = @iLicenseId
			
		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
	
END
GO