DECLARE @CompanyName AS NVARCHAR(max)
DECLARE @querySARI88 AS NVARCHAR(max)
DECLARE @querySARI90 AS NVARCHAR(max)
DECLARE @queryDB88 AS NVARCHAR(max)
DECLARE @queryDB90 AS NVARCHAR(max)
DECLARE @queryDBCompanyName AS NVARCHAR(max)
DECLARE @querycoresuite AS NVARCHAR(max)
DECLARE @queryonDemandmodules AS NVARCHAR(max)
DECLARE @queryCOR_COR_MODULES AS NVARCHAR(max)

SELECT
	@CompanyName = (SELECT
			      CompnyName
		      FROM   OADM)

SET @querySARI88 = 'SELECT 
[AddOnId] AS ''addon id''
,CONVERT(VARCHAR(30),[AName]) AS ''addon name''
,CONVERT(VARCHAR(15),[AddOnVer]) AS ''addon version'' 
FROM [SBO-COMMON].[dbo].[SARI] 
ORDER BY [AddOnId]';

SET @querySARI90 = 'SELECT 
[AddOnId] AS ''addon id''
,CONVERT(VARCHAR(30),[AName]) AS ''addon name''
,CASE ISNULL([AddOnVer], '' '') 
 WHEN '' '' THEN ''currently not installed''
 ELSE  CONVERT(VARCHAR(15),[AddOnVer])
 END AS ''addon version''
,CASE 
 WHEN [AddPlat] = ''N'' THEN ''32-bit'' 
 WHEN [AddPlat] = ''X'' THEN ''64-bit'' 
 END  AS ''addon platform'' 
FROM [SBO-COMMON].[dbo].[SARI]
ORDER BY [AddOnId]';

SET @queryDB88 = 'SELECT 
T0.[AddOnId] AS ''addon id''
,CAST(T2.Version AS NVARCHAR) AS ''SBO-COMMON version''
,CAST(T4.Version AS NVARCHAR) AS ''company version''
,T3.[CompnyName]AS ''company name''
,(T0.[AName] + '' '' + '' ('' + T0.[AddOnVer]) + '')''AS ''addon assigned (incl. version)''
,CASE 
 WHEN T1.[AStatus] = ''Y'' THEN ''Addon is set to active'' 
 WHEN T1.[AStatus] = ''N'' THEN ''Addon is NOT set to active'' 
 END AS ''addon status''
,CASE 
 WHEN T1.[EGroup] = ''M'' THEN ''Addon start set to MANUAL'' 
 WHEN T1.[EGroup] = ''A'' THEN ''Addon start set to AUTOMATIC'' 
 WHEN T1.[EGroup] = ''C'' THEN ''Addon start set to MANDATORY'' 
 WHEN T1.[EGroup] = ''D'' THEN ''Addon starts set to DISABLED'' 
 END AS ''general addon start''
FROM [SBO-COMMON].[dbo].[SARI] T0 
INNER JOIN [dbo].[OARI] T1 
ON T0.[AddOnId] = T1.[AddOnID],
[SBO-COMMON].[dbo].[SINF] T2,
[dbo].[OADM] T3,
[dbo].[CINF] T4
ORDER BY T0.[AddOnId]';

SET @queryDB90 = 'SELECT 
T0.[AddOnId] AS ''addon id''
,CAST(T2.Version AS NVARCHAR) AS ''SBO-COMMON version''
,CONVERT(VARCHAR(23),T3.Version) AS ''company version''
,CONVERT(VARCHAR(35),T3.[CompnyName]) AS ''company name''
,CONVERT(VARCHAR(40),(T0.[AName] + '' '' + T0.[AddOnVer] + '' ('' + CASE WHEN T0.[AddPlat] = ''N'' THEN ''32-bit'' WHEN T0.[AddPlat]= ''X'' THEN ''64-bit''END + '')'')) AS ''addon assigned (incl. version and platform)''
,CASE 
 WHEN T1.[AStatus] = ''Y'' THEN ''Addon is set to Active'' 
 WHEN T1.[AStatus] = ''N'' THEN ''Addon is NOT set to Active'' 
 END AS ''addon status''
,CASE 
 WHEN T1.[EGroup] = ''M'' THEN ''Addon start is set to MANUAL'' 
 WHEN T1.[EGroup] = ''A'' THEN ''Addon start is set to AUTOMATIC'' 
 WHEN T1.[EGroup] = ''C'' THEN ''Addon start is set to MANDATORY'' 
 WHEN T1.[EGroup] = ''D'' THEN ''Addon start is set to DISABLED'' 
 END AS ''general addon start''
FROM [SBO-COMMON].[dbo].[SARI] T0 
INNER JOIN [dbo].[OARI] T1 
ON T0.[AddOnId] = T1.[AddOnID] AND T0.[AddOnVer] IS NOT NULL,
[SBO-COMMON].[dbo].[SINF] T2,
[dbo].[CINF] T3
ORDER BY T0.[AddOnId]';

SET @queryDBCompanyName = 'SELECT
CAST(T1.Version AS NVARCHAR) AS ''SBO-COMMON version''
,CONVERT(VARCHAR(23),T0.Version) AS ''company version''
,CONVERT(VARCHAR(35),T0.[CompnyName]) AS ''company name''
FROM 
[dbo].[CINF] T0,
[SBO-COMMON].[dbo].[SINF] T1';

SET @querycoresuite = 'SELECT 
CONVERT(VARCHAR(40),[sipname]) AS ''module''
,[sipversion] AS ''module version''
,[toinstall] AS ''to install''
,[hidden] 
,[releasedate]
FROM [coresuite].[dbo].[modules] 
ORDER BY [id]';

SET @queryonDemandmodules = 'SELECT 

DISTINCT CONVERT(VARCHAR(40),[U_SipName]) AS ''module'' 
,[U_SipVersion]
,[U_ToInstall] AS ''to install''
,[U_Hidden] 
,[Code]
FROM [@SWA_MODULES]
GROUP BY 
[U_DataId]
,[U_SipName]
,[U_SipVersion]
,[U_ToInstall] 
,[U_Hidden]
,[Code]
ORDER BY [Code]';


SET @queryCOR_COR_MODULES = 'SELECT 
CONVERT(VARCHAR(40),[U_ProductName]) AS ''module''
,CONVERT(VARCHAR(20),[U_ProductVersion]) AS ''module version''
,CONVERT(VARCHAR(13),[U_IsActive] ) AS ''status''
,CONVERT(VARCHAR(11),[U_LicCount] ) AS ''licenced''
  FROM [dbo].[@COR_COR_MODULES]';

PRINT( '(1) Addons installed in SQL enviroment - Data from the SARI table of the SBO-COMMON where Standard or Silent Installer used;')
PRINT( '    if this Query does not list coresuite, then either the Lightweight Installer was used or we are dealing with an onDemand environment.' )
PRINT(' ')

IF (SELECT
	    Version
    FROM   [SBO-COMMON].[dbo].[SINF]) < '900056'
  EXEC Sp_executesql
	  @querySARI88;
ELSE
  EXEC Sp_executesql
	  @querySARI90;


PRINT(' ')
PRINT( '(2) Addons assigned to Company incl. Company and SBO-COMMON version, addon status and startup method.')
PRINT( '    if this Query does not list coresuite, then either the Lightweight Installer was used or we are dealing with an onDemand environment.' )

PRINT(' ')

IF (SELECT
	    Version
    FROM   [SBO-COMMON].[dbo].[SINF]) < '900056'
  EXEC Sp_executesql
	  @queryDB88;
ELSE
  EXEC Sp_executesql
	  @queryDB90;


PRINT(' ')
PRINT( '(3) Company Name and Version plus SBO-COMMON version for onDemand environment or when Lightweight Installer is used;')
PRINT( '    this allows us to get those details which for Standard Installations are displayed in Query (2), and removes the factor that the SLD Database might be renamed or the access to the SLD for Database User is restricted')
PRINT(' ')

EXEC Sp_executesql
	@queryDBCompanyName;


PRINT(' ')
PRINT( '(4) Module sips available in the modules table of the coresuite database or the SWA_MODULES table in the Company DB incl. module versions and install status' )
PRINT(' ')

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'coresuite') 
  EXEC Sp_executesql
	  @queryonDemandmodules;
IF  EXISTS (SELECT * FROM sys.databases WHERE name = 'coresuite') 
  EXEC Sp_executesql
	  @querycoresuite;



PRINT(' ')
PRINT( '(5) Modules installed on' + ' "'
       + Cast(@CompanyName AS NVARCHAR(max)) + '" '
       + 'incl. installed module version and module status' )
PRINT(' ')

EXEC Sp_executesql
	@queryCOR_COR_MODULES;