--Name:- GUI Login Languages
--Description:- Information about logged in Languages

SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 5
DECLARE @surveyXML NVARCHAR(MAX)

-- When the login locale is changed there will be an Audit logged.
-- Find the changed locale and count from the GUI Audit trail entry.

DECLARE @relId int

select @relId = releaseId from CommServ..APP_Client where id = 2

IF (OBJECT_ID('tempdb.dbo.#tblLocale') IS NOT NULL)
BEGIN
	DROP TABLE #tblLocale
END

Create Table #tblLocale(
	locale nvarchar(100), 
	countlocale int
)

DECLARE @sql NVARCHAR(MAX)

if (@relId = 14)
BEGIN
	--SET @surveyXML = (
	SET @sql = ' insert into #tblLocale \
	SELECT \
		CASE WHEN LEN(data)>8 \
			THEN SUBSTRING(data, 9, LEN(data)-8)  \
			ELSE data END, \
		COUNT(*) \
	FROM evParam ep	JOIN evMsg em  \
	ON ep.evMsgId = em.id \
	WHERE  \
	em.id in ( \
		SELECT EvGuiAuditParameter.evMsgId FROM EvGuiAuditParameter \
		WHERE opId in  \
		( \
			SELECT EvGuiAuditOperation.id FROM EvGuiAuditOperation \
			WHERE evMsgId in  \
			( \
				SELECT id FROM evMsg \
				WHERE messageId=1409286147	 \
			) \
		) \
	) \
	AND messageId = ''1426063592'' \
	group by data '
END
ELSE IF (@relId >= 15)
BEGIN
	SET @sql = ' insert into #tblLocale \
	SELECT \
		data, \
		COUNT(*) \
	FROM evParam ep	JOIN evMsg em  \
	ON ep.evMsgId = em.id and ep.commcellId = em.commCellID \
	WHERE  \
	em.id in ( \
		SELECT EvGuiAuditParameter.evMsgId FROM EvGuiAuditParameter \
		WHERE opId in \
		( \
			SELECT EvGuiAuditOperation.id FROM EvGuiAuditOperation \
			WHERE evMsgId in  \
			( \
				SELECT id FROM evMsg \
				WHERE messageId=1409286147	 \
			) \
		) \
	) \
	AND messageId = ''1426064206''\
	AND em.commCellID = 2 \
	group by data '
END

exec sp_executesql @sql

SET @surveyXML = (
	SELECT locale AS '@Locale', 
		countlocale AS '@Count'
	FROM #tblLocale
	FOR XML PATH('GUILoginLanguages'))

IF (OBJECT_ID('tempdb.dbo.#tblLocale') IS NOT NULL)
BEGIN
	DROP TABLE #tblLocale
END

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF