using C4IT.API.Contracts; using C4IT.API.CustomerPanel; using C4IT.Logging; using Matrix42.Pandora.Contracts.Internationalization; using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Globalization; using System.Linq; using System.Net; using System.Reflection; using System.Web; using System.Web.Caching; using update4u.SPS.DataLayer; using update4u.SPS.DataLayer.Command; using update4u.SPS.DataLayer.DataReader; using update4u.SPS.DataLayer.Transaction; using static C4IT.Logging.cLogManager; namespace C4IT.API { public class CustomerPanelHelper { #region SQLStatements private const string ticketSQLStatement = @" --DECLARE @user_id UNIQUEIDENTIFIER = '7edaa9c7-7b33-e511-80f6-0050562f9516', --@LCID_long INT = 1031, --@LCID_short INT = 7; SELECT acb.NewInformationReceived, acb.[Expression-ObjectID] AS eoid, acb.Subject, ccb.State AS stateValue, ISNULL( (SELECT TOP 1 cpo.DisplayString FROM [SPSCommonPickupObjectStatus-CI] AS cpo WITH(NOLOCK) WHERE scpo.ID = cpo.[Owner] AND cpo.LCID IN (@LCID_long, @LCID_short, 127) ORDER BY CASE cpo.LCID WHEN @LCID_long THEN 1 WHEN @LCID_short THEN 2 WHEN 127 THEN 3 END ASC), scpo.DisplayString) AS [state], acb.CreatedDate, lj2.CreatedDate as maxJournalDate, lj2.ActivityAction AS journalAction, sot.name AS sysentity, acb.TicketNumber, ISNULL( (SELECT TOP 1 bso.DisplayName FROM [BasicSchemaObjectType-CI] AS bso WITH(NOLOCK) WHERE bso.Owner = sot.[ID] AND bso.LCID IN (@LCID_long, @LCID_short, 127) ORDER BY CASE bso.LCID WHEN @LCID_long THEN 1 WHEN @LCID_short THEN 2 WHEN 127 THEN 3 END ASC), sot.DisplayName) AS [TicketType] FROM SPSActivityClassBase acb WITH(NOLOCK) LEFT JOIN C4IT_CustomerPanelActivityClassAdditionalRelations a WITH(NOLOCK) on a.[Expression-ObjectID] = acb.[Expression-ObjectID] LEFT JOIN SPSActivityClassUnitOfWork lj2 WITH(NOLOCK) on lj2.ID = a.LastJournalEntry JOIN SPSCommonClassBase ccb WITH(NOLOCK) ON ccb.[expression-objectid] = acb.[expression-objectid] LEFT JOIN SPSCommonPickupObjectStatus scpo WITH(NOLOCK) ON scpo.Value = ccb.State LEFT JOIN SchemaObjectType sot WITH(NOLOCK) ON sot.id = ccb.TypeID LEFT JOIN [BasicSchemaObjectType-CI] AS bso ON bso.Owner = sot.[ID] AND bso.LCID IN (@LCID_long, @LCID_short, 127) LEFT JOIN [SPSCommonPickupObjectStatus-CI] AS cpo ON scpo.ID = cpo.[Owner] AND cpo.LCID IN (@LCID_long, @LCID_short, 127) WHERE (ccb.[State] <> 204 OR GETUTCDATE() < DATEADD(WEEK, 1, acb.ClosedDate)) AND ( acb.[UsedInTypeSPSActivityTypeTicket] IS NOT NULL OR acb.[UsedInTypeSPSActivityTypeServiceRequest] IS NOT NULL OR acb.[UsedInTypeSPSActivityTypeIncident] IS NOT NULL ) AND acb.Initiator = @user_id ORDER BY lj2.CreatedDate DESC, acb.CreatedDate DESC; "; private const string configSQLStatement = @" SELECT name, version, announcementEnabled, incidentEnabled, informationEnabled, updateInterval, UpdateIntervallRegularAnnouncements, UpdateIntervalAdHocAnnouncements, logoUrl, startMinimized, remoteApp, trayIcon, sspEnabled, customLinks, isDraggable, activeButtonColor, inactiveButtonColor, backgroundColor, headerColor, iconColor, mainIconTextColor, disableClosing, encryptedApiToken, reloginIntervalDays FROM [C4IT_CustomerPanel_SetupClassBase] WITH(NOLOCK)"; private const string adHocAnnouncementsSQLStatement = @" -- Get announcements (AdHoc) DECLARE @CurrentDate DATETIME = GETUTCDATE(); SELECT sab.[Expression-ObjectID] as eoid, COALESCE(als.Subject, sab.Subject) AS AnnouncementSubject, sab.CreatedDate AS AnnouncementCreatedDate, sab.VisibleFrom AS AnnouncementVisibleFrom, sab.Visible AS AnnouncementVisibility, COALESCE(amh.MessageHTML, sab.MessageHTML) AS [Message], col.Color AS prioColor, col.Priority as priority FROM SVMAnnouncementClassBase AS sab WITH(NOLOCK) LEFT JOIN C4it_CP_AnnouncementClassBase AS cpa WITH(NOLOCK) ON sab.[Expression-ObjectID] = cpa.[Expression-ObjectID] LEFT JOIN C4IT_CP_AnnouncementPriortyPickupType AS col WITH(NOLOCK) ON cpa.[priority] = col.[Value] OUTER APPLY ( SELECT TOP 1 als.Subject FROM [SVMAnnouncementClassBase-CI] AS als WITH(NOLOCK) WHERE als.Owner = sab.ID AND als.LCID IN (@LCID_long, @LCID_short, 127) ORDER BY CASE als.LCID WHEN @LCID_long THEN 1 WHEN @LCID_short THEN 2 WHEN 127 THEN 3 END ASC ) als OUTER APPLY ( SELECT TOP 1 amh.MessageHTML FROM [SVMAnnouncementClassBase-CI] AS amh WITH(NOLOCK) WHERE amh.Owner = sab.ID AND amh.LCID IN (@LCID_long, @LCID_short, 127) ORDER BY CASE amh.LCID WHEN @LCID_long THEN 1 WHEN @LCID_short THEN 2 WHEN 127 THEN 3 END ASC ) amh WHERE (sab.Visible = 1 OR (sab.Visible = 2 AND (sab.VisibleFrom IS NULL OR sab.VisibleFrom < @CurrentDate) AND (sab.VisibleUntil IS NULL OR sab.VisibleUntil > @CurrentDate)) ) AND (sab.Restricted IS NULL OR sab.Restricted = 0 ) OPTION(OPTIMIZE FOR (@CurrentDate UNKNOWN, @LCID_long UNKNOWN,@LCID_short UNKNOWN)) "; private const string regularAnnouncementsSQLStatement = @" --DECLARE @CurrentUserId UNIQUEIDENTIFIER = '7edaa9c7-7b33-e511-80f6-0050562f9516', --@LCID_long INT = 1031, --@LCID_short INT = 7, --@RecursiveOU TINYINT = 1; DECLARE @CurrentDate DATETIME = GETUTCDATE(); WITH RoleCTE AS ( SELECT ar.Roles FROM [SchemaRelation-SVMAnnouncementClassBase2SPSSecurityClassRole] AS ar WITH(NOLOCK) INNER JOIN [SchemaRelation-SPSSecurityClassRole2SPSUserClassBase] AS rr WITH(NOLOCK) ON ar.RelatedAnnouncements = rr.Members WHERE rr.MemberOf = @CurrentUserId ), ParentOrgUnits AS ( SELECT IIF( @RecursiveOU = 1 , op.ID , uou.ID ) AS OrgUnitID FROM SPSUserClassBase AS u WITH(NOLOCK) INNER JOIN SPSCommonClassBase AS uc WITH(NOLOCK) ON u.[Expression-ObjectID] = uc.[Expression-ObjectID] INNER JOIN SPSOrgUnitClassBase AS uou WITH(NOLOCK) ON uc.OU = uou.ID LEFT JOIN [Schema-PC-SPSOrgUnitType-SPSCommonClassBase-OU] AS pcou WITH(NOLOCK) ON uou.ID = pcou.Child LEFT JOIN SPSOrgUnitClassBase AS op WITH(NOLOCK) ON op.ID = pcou.Parent WHERE u.ID = @CurrentUserId ) -- Get announcements SELECT sab.[Expression-ObjectID] as eoid, COALESCE(als.Subject, sab.Subject) AS AnnouncementSubject, sab.CreatedDate AS AnnouncementCreatedDate, sab.VisibleFrom AS AnnouncementVisibleFrom, sab.Visible AS AnnouncementVisibility, COALESCE(amh.MessageHTML, sab.MessageHTML) AS [Message], col.Color AS prioColor, col.Priority AS priority FROM SVMAnnouncementClassBase AS sab WITH(NOLOCK) LEFT JOIN C4it_CP_AnnouncementClassBase AS cpa WITH(NOLOCK) ON sab.[Expression-ObjectID] = cpa.[Expression-ObjectID] LEFT JOIN C4IT_CP_AnnouncementPriortyPickupType AS col WITH(NOLOCK) ON cpa.[priority] = col.[Value] LEFT JOIN [SchemaRelation-SVMAnnouncementClassBase2SPSUserClassBase] AS rel WITH(NOLOCK) ON sab.ID = rel.UserS AND rel.Announcements = @CurrentUserId LEFT JOIN RoleCTE AS rc ON sab.ID = rc.Roles LEFT JOIN [SchemaRelation-SVMAnnouncementClassBase2SPSOrgUnitClassBase] AS ao WITH(NOLOCK) ON ao.OrgUnits = sab.ID LEFT JOIN SPSOrgUnitClassBase AS ou WITH(NOLOCK) ON ou.ID = ao.Announcements OUTER APPLY ( SELECT TOP 1 als.Subject FROM [SVMAnnouncementClassBase-CI] AS als WITH(NOLOCK) WHERE als.Owner = sab.ID AND als.LCID IN (@LCID_long, @LCID_short, 127) ORDER BY CASE als.LCID WHEN @LCID_long THEN 1 WHEN @LCID_short THEN 2 WHEN 127 THEN 3 END ASC ) als OUTER APPLY ( SELECT TOP 1 amh.MessageHTML FROM [SVMAnnouncementClassBase-CI] AS amh WITH(NOLOCK) WHERE amh.Owner = sab.ID AND amh.LCID IN (@LCID_long, @LCID_short, 127) ORDER BY CASE amh.LCID WHEN @LCID_long THEN 1 WHEN @LCID_short THEN 2 WHEN 127 THEN 3 END ASC ) amh WHERE (sab.Visible = 1 OR (sab.Visible = 2 AND (sab.VisibleFrom IS NULL OR sab.VisibleFrom < @CurrentDate) AND (sab.VisibleUntil IS NULL OR sab.VisibleUntil > @CurrentDate)) ) AND (sab.Restricted = 1      AND (rel.Users IS NOT NULL        OR rc.Roles IS NOT NULL        OR EXISTS (SELECT 1 FROM ParentOrgUnits AS pou WITH(NOLOCK) WHERE ou.ID = pou.OrgUnitID)      ) ) OPTION(OPTIMIZE FOR (@CurrentDate UNKNOWN, @CurrentUserId UNKNOWN, @LCID_long UNKNOWN,@LCID_short UNKNOWN,@RecursiveOU UNKNOWN )) "; #endregion private readonly Guid CustomerPanelConfigID = new Guid("83F7BBCA-32FD-E811-CC82-000C29A7A20A"); public Version getVersion() { var CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); try { var classId = SPSDataEngineSchemaReader.ClassGetIDFromName("C4IT_CustomerPanel_SetupClassBase"); DataTable dtCustomerPanelConfig = FragmentRequestBase.SimpleLoad(classId, "version", "id='" + CustomerPanelConfigID.ToString("D") + "'"); if (dtCustomerPanelConfig == null || dtCustomerPanelConfig.Rows.Count == 0) { LogEntry("No configuration found for CustomerPanelConfigID: " + CustomerPanelConfigID, LogLevels.Warning); return new Version(0, 0, 0, 0); } DataRow drCustomerPanelConfig = dtCustomerPanelConfig.Rows[0]; string versionString = drCustomerPanelConfig["Version"]?.ToString() ?? "0.0.0.0"; if (Version.TryParse(versionString, out Version parsedVersion)) { return parsedVersion; } return new Version(0, 0, 0, 0); } catch (Exception ex) { LogException(ex); return new Version(0, 0, 0, 0); } finally { LogMethodEnd(CM); } } internal CustomerPanelConfig GetConfig(bool noCache) { var CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); CustomerPanelConfig cPconfig = new CustomerPanelConfig(); try { // Cache-Handling wie bisher var _cacheConfig = ApiCache.GetValue("config") as CustomerPanelConfig; if (noCache && _cacheConfig != null) { ApiCache.Delete("config"); _cacheConfig = null; } if (_cacheConfig != null) { return _cacheConfig; } // Transaktion + Reader via neues Framework using (var sPSTransactionScope = new SPSTransactionScope()) using (var dataReader = DirectDbCompat.ExecuteReader( dbCmd => { dbCmd.CommandText = configSQLStatement; dbCmd.CommandType = CommandType.Text; }, SPSTransaction.Current, CommandBehavior.SingleRow)) { if (dataReader.Read()) { // Spalten-Indizes int versionColumnIndex = dataReader.GetOrdinal("Version"); int timerIntervalTicketColumnIndex = dataReader.GetOrdinal("updateInterval"); int timerIntervalAdHocColumnIndex = dataReader.GetOrdinal("UpdateIntervalAdHocAnnouncements"); int timerIntervalRegularColumnIndex = dataReader.GetOrdinal("UpdateIntervallRegularAnnouncements"); int isStartApplicationMinimizedColumnIndex = dataReader.GetOrdinal("startMinimized"); int iconColorColumnIndex = dataReader.GetOrdinal("iconColor"); int mainIconTextColorColumnIndex = dataReader.GetOrdinal("mainIconTextColor"); int logoUrlColumnIndex = dataReader.GetOrdinal("logoUrl"); int remoteAppColumnIndex = dataReader.GetOrdinal("remoteApp"); int trayIconColumnIndex = dataReader.GetOrdinal("trayIcon"); int isDraggableColumnIndex = dataReader.GetOrdinal("isDraggable"); int disableClosingColumnIndex = dataReader.GetOrdinal("disableClosing"); int activeButtonColorColumnIndex = dataReader.GetOrdinal("activeButtonColor"); int inactiveButtonColorColumnIndex = dataReader.GetOrdinal("inactiveButtonColor"); int backgroundColorColumnIndex = dataReader.GetOrdinal("backgroundColor"); int headerColorColumnIndex = dataReader.GetOrdinal("headerColor"); int announcementEnabledColumnIndex = dataReader.GetOrdinal("announcementEnabled"); int incidentEnabledColumnIndex = dataReader.GetOrdinal("incidentEnabled"); int sspEnabledColumnIndex = dataReader.GetOrdinal("sspEnabled"); int informationEnabledColumnIndex = dataReader.GetOrdinal("informationEnabled"); int customLinksColumnIndex = dataReader.GetOrdinal("customLinks"); int encryptedApiTokenColumnIndex = dataReader.GetOrdinal("encryptedApiToken"); int reloginIntervalDaysColumnIndex = dataReader.GetOrdinal("reloginIntervalDays"); // Version sicher parsen string versionString = dataReader.IsDBNull(versionColumnIndex) ? "0.0.0.0" : dataReader.GetString(versionColumnIndex); Version serverVersion; if (!Version.TryParse(versionString, out serverVersion)) { serverVersion = new Version(0, 0, 0, 0); } // Grundobjekt befüllen cPconfig = new CustomerPanelConfig { _ServerVersion = serverVersion, _timerIntervalTicket = dataReader.IsDBNull(timerIntervalTicketColumnIndex) ? 10 : dataReader.GetInt32(timerIntervalTicketColumnIndex), _timerIntervalAdHocAnnouncements = dataReader.IsDBNull(timerIntervalAdHocColumnIndex) ? 2 : dataReader.GetInt32(timerIntervalAdHocColumnIndex), _timerIntervalRegularAnnouncements = dataReader.IsDBNull(timerIntervalRegularColumnIndex) ? 10 : dataReader.GetInt32(timerIntervalRegularColumnIndex), _isStartApplicationMinimized = ConverterHelper.ObjectToBoolConverter(dataReader[isStartApplicationMinimizedColumnIndex]?.ToString()), _iconColor = dataReader.GetInt32(iconColorColumnIndex), _mainIconTextColor = dataReader.GetInt32(mainIconTextColorColumnIndex), _logoUrl = dataReader.IsDBNull(logoUrlColumnIndex) ? string.Empty : dataReader.GetString(logoUrlColumnIndex), _trayIconUrl = dataReader.IsDBNull(trayIconColumnIndex) ? string.Empty : dataReader.GetString(trayIconColumnIndex), _isDraggable = ConverterHelper.ObjectToBoolConverter(dataReader[isDraggableColumnIndex]?.ToString()), _disableClosing = ConverterHelper.ObjectToBoolConverter(dataReader[disableClosingColumnIndex]?.ToString()), _encryptedApiToken = dataReader.IsDBNull(encryptedApiTokenColumnIndex) ? string.Empty : dataReader.GetString(encryptedApiTokenColumnIndex), _reloginIntervalDays = dataReader.IsDBNull(reloginIntervalDaysColumnIndex) ? 14 : dataReader.GetInt32(reloginIntervalDaysColumnIndex), _remoteAppPath = dataReader.IsDBNull(remoteAppColumnIndex) ? string.Empty : dataReader.GetString(remoteAppColumnIndex) }; // UI-Farben cPconfig._uiColors["activeButtonColor"] = dataReader.IsDBNull(activeButtonColorColumnIndex) ? string.Empty : dataReader.GetString(activeButtonColorColumnIndex); cPconfig._uiColors["inactiveButtonColor"] = dataReader.IsDBNull(inactiveButtonColorColumnIndex) ? string.Empty : dataReader.GetString(inactiveButtonColorColumnIndex); cPconfig._uiColors["backgroundColor"] = dataReader.IsDBNull(backgroundColorColumnIndex) ? string.Empty : dataReader.GetString(backgroundColorColumnIndex); cPconfig._uiColors["headerColor"] = dataReader.IsDBNull(headerColorColumnIndex) ? string.Empty : dataReader.GetString(headerColorColumnIndex); // Token neu kodieren var encryptedToken = PrivateCustomerPanelSecurePassword.Instance.Decode(cPconfig._encryptedApiToken); cPconfig._encryptedApiToken = CustomerPanelSecurePassword.Instance.Encode(encryptedToken); // Custom Links var linkList = dataReader.IsDBNull(customLinksColumnIndex) ? string.Empty : dataReader.GetString(customLinksColumnIndex); if (!string.IsNullOrWhiteSpace(linkList)) { foreach (string s in linkList.Split(';')) { var temp = s.Split(','); if (temp.Length == 2) { cPconfig._linkList[temp[1].Trim()] = temp[0].Trim(); } } } cPconfig.MainFunctionActivation = new Dictionary { [enumMainFunctions.Announcement] = ConverterHelper.ObjectToBoolConverter(dataReader[announcementEnabledColumnIndex]?.ToString()), [enumMainFunctions.Incident] = ConverterHelper.ObjectToBoolConverter(dataReader[incidentEnabledColumnIndex]?.ToString()), [enumMainFunctions.Ssp] = ConverterHelper.ObjectToBoolConverter(dataReader[sspEnabledColumnIndex]?.ToString()), [enumMainFunctions.Information] = ConverterHelper.ObjectToBoolConverter(dataReader[informationEnabledColumnIndex]?.ToString()), [enumMainFunctions.CustomLinks] = !string.IsNullOrEmpty(dataReader[customLinksColumnIndex]?.ToString()) }; sPSTransactionScope.Complete(); } } string suiteUuxUrl = GetSuiteUuxUrl(); cPconfig._createNewTicketDirectLink = GetCreateTicketDeeplink(); // LogoUrl korrigieren if (!string.IsNullOrEmpty(cPconfig._logoUrl) && !Uri.IsWellFormedUriString(cPconfig._logoUrl, UriKind.Absolute)) { if (!cPconfig._logoUrl.StartsWith("m42Services/", StringComparison.InvariantCultureIgnoreCase)) cPconfig._logoUrl = $"m42Services/{cPconfig._logoUrl}"; if (Uri.TryCreate(suiteUuxUrl, UriKind.Absolute, out Uri baseUri)) { var logoUri = new Uri(baseUri, cPconfig._logoUrl); cPconfig._logoUrl = logoUri.ToString(); } else { LogException(new UriFormatException($"Ungültige SuiteUuxUrl: {suiteUuxUrl}")); } } // TrayIconUrl korrigieren if (!string.IsNullOrEmpty(cPconfig._trayIconUrl) && !Uri.IsWellFormedUriString(cPconfig._trayIconUrl, UriKind.Absolute)) { if (!cPconfig._trayIconUrl.StartsWith("m42Services/", StringComparison.InvariantCultureIgnoreCase)) cPconfig._trayIconUrl = $"m42Services/{cPconfig._trayIconUrl}"; if (Uri.TryCreate(suiteUuxUrl, UriKind.Absolute, out Uri baseTrayUri)) { var trayIconUri = new Uri(baseTrayUri, cPconfig._trayIconUrl); cPconfig._trayIconUrl = trayIconUri.ToString(); } else { LogException(new UriFormatException($"Ungültige SuiteUuxUrl: {suiteUuxUrl}")); } } ApiCache.Add("config", cPconfig, DateTime.Now.AddMinutes(5)); return cPconfig; } catch (Exception ex) { LogException(ex); return cPconfig; } finally { LogMethodEnd(CM); } } internal string GetCreateTicketDeeplink(string suiteUuxUrl = null) { var CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); string deeplink = string.Empty; try { // suiteUuxUrl einmalig ermitteln (oder vom Aufrufer übernehmen) if (string.IsNullOrWhiteSpace(suiteUuxUrl)) suiteUuxUrl = GetSuiteUuxUrl(); // Flag aus SPSGlobalConfigurationClassServiceDesk via SimpleLoad bool ticketAndServiceRequestEnabled = false; var sdClassId = SPSDataEngineSchemaReader.ClassGetIDFromName("SPSGlobalConfigurationClassServiceDesk"); DataTable sdCfg = FragmentRequestBase.SimpleLoad(sdClassId, "TicketAndServiceRequestEnabled", "1=1"); if (sdCfg != null && sdCfg.Rows.Count > 0) { string colName = sdCfg.Columns.Contains("TicketAndServiceRequestEnabled") ? "TicketAndServiceRequestEnabled" : (sdCfg.Columns.Contains("ticketAndServiceRequestEnabled") ? "ticketAndServiceRequestEnabled" : null); if (colName != null) { string val = sdCfg.Rows[0][colName]?.ToString(); ticketAndServiceRequestEnabled = ConverterHelper.ObjectToBoolConverter(val); } } // DeepLink bauen if (!string.IsNullOrEmpty(suiteUuxUrl) && Uri.TryCreate(suiteUuxUrl, UriKind.Absolute, out Uri uri)) { string host = $"{uri.Scheme}://{uri.Host}"; string type = ticketAndServiceRequestEnabled ? "SPSActivityTypeTicket" : "SPSActivityTypeIncident"; const string appName = "SelfServicePortal"; const ViewType viewType = ViewType.New; deeplink = DeepLinkBuilder.CreateDeepLink(host, appName, viewType, type); } else { LogException(new UriFormatException($"Ungültige SuiteUuxUrl: {suiteUuxUrl}")); } } catch (Exception ex) { LogException(ex); } finally { LogMethodEnd(CM); } return deeplink; } private string GetSuiteUuxUrl() { var CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); const string cacheKey = "SuiteUuxUrl"; if (ApiCache.GetValue(cacheKey) is string cached && !string.IsNullOrEmpty(cached)) { LogMethodEnd(CM); return cached; } string suiteUuxUrl = string.Empty; try { var classId = SPSDataEngineSchemaReader.ClassGetIDFromName("SPSAlertingConfiguration"); DataTable result = FragmentRequestBase.SimpleLoad(classId, "suiteUuxUrl", "1=1"); if (result != null && result.Rows.Count > 0) { string colName = result.Columns.Contains("suiteUuxUrl") ? "suiteUuxUrl" : (result.Columns.Contains("SuiteUuxUrl") ? "SuiteUuxUrl" : null); if (colName != null) { suiteUuxUrl = result.Rows[0][colName]?.ToString(); if (!string.IsNullOrWhiteSpace(suiteUuxUrl)) suiteUuxUrl = suiteUuxUrl.Trim().TrimEnd('/'); else suiteUuxUrl = string.Empty; } } ApiCache.Add(cacheKey, suiteUuxUrl, DateTime.Now.AddMinutes(60)); return suiteUuxUrl; } catch (Exception ex) { LogException(ex); return string.Empty; } finally { LogMethodEnd(CM); } } internal bool ResetCache() { var CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); try { ApiCache.Delete("AdHocMessage"); ApiCache.Delete("config"); return true; } catch (Exception ex) { LogException(ex); return false; } finally { LogMethodEnd(CM); } } internal List GetTickets(Guid? userId = null) { var CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); var tickets = new List(); if (userId == null || !userId.HasValue) { LogEntry("UserId is null", LogLevels.Error); return tickets; } try { var _globalSettings = CustomerPanelController.controller.GlobalSettings; // Client-Sprache aus dem Header bestimmen (fallback: en) string defaultClientLanguageTwoLetterCode = "en"; string acceptLanguageHeader = HttpContext.Current?.Request?.Headers["Accept-Language"]; if (!string.IsNullOrWhiteSpace(acceptLanguageHeader)) { try { var cultureInfo = new CultureInfo(acceptLanguageHeader.Split(',')[0]); defaultClientLanguageTwoLetterCode = cultureInfo.TwoLetterISOLanguageName; } catch { LogEntry("Identifying the client user culture based on 'Accept-Language' parameter '" + acceptLanguageHeader + "' failed", LogLevels.Debug); } } // Sprache aus Settings (fallback: en; falls beides nicht vorhanden, harte Defaults) var languageInfo = _globalSettings.GetLanguages().FirstOrDefault(lang => lang.TwoLetterCode == defaultClientLanguageTwoLetterCode) ?? _globalSettings.GetLanguages().FirstOrDefault(lang => lang.TwoLetterCode == "en"); int lcidLong = languageInfo?.LCID ?? 1033; // en-US int lcidShort = languageInfo?.ParentLCID ?? 9; // EN using (var sPSTransactionScope = new SPSTransactionScope()) using (var dataReader = DirectDbCompat.ExecuteReader( dbCmd => { dbCmd.CommandText = ticketSQLStatement; dbCmd.CommandType = CommandType.Text; var pLong = dbCmd.CreateParameter(); pLong.ParameterName = "@LCID_long"; pLong.DbType = DbType.Int32; pLong.Value = lcidLong; dbCmd.Parameters.Add(pLong); var pShort = dbCmd.CreateParameter(); pShort.ParameterName = "@LCID_short"; pShort.DbType = DbType.Int32; pShort.Value = lcidShort; dbCmd.Parameters.Add(pShort); var pUser = dbCmd.CreateParameter(); pUser.ParameterName = "@user_id"; pUser.DbType = DbType.Guid; pUser.Value = userId.Value; dbCmd.Parameters.Add(pUser); }, SPSTransaction.Current, CommandBehavior.Default)) { // Spalten-Indizes einmalig ermitteln int subjectColumnIndex = dataReader.GetOrdinal("Subject"); int expressionObjectIdColumnIndex = dataReader.GetOrdinal("eoid"); int createdDateColumnIndex = dataReader.GetOrdinal("CreatedDate"); int stateColumnIndex = dataReader.GetOrdinal("state"); int newInformationReceivedColumnIdx = dataReader.GetOrdinal("NewInformationReceived"); int sysEntityColumnIndex = dataReader.GetOrdinal("sysentity"); int ticketTypeColumnIndex = dataReader.GetOrdinal("TicketType"); int ticketNumberColumnIndex = dataReader.GetOrdinal("TicketNumber"); int lastJournalActionColumnIndex = dataReader.GetOrdinal("journalAction"); int stateValueColumnIndex = dataReader.GetOrdinal("stateValue"); int lastJournalEntryDateColumnIndex = dataReader.GetOrdinal("maxJournalDate"); while (dataReader.Read()) { var ticket = new Ticket { _subject = dataReader.IsDBNull(subjectColumnIndex) ? string.Empty : dataReader.GetString(subjectColumnIndex), _objectID = dataReader.GetGuid(expressionObjectIdColumnIndex), _createdDate = dataReader.GetDateTime(createdDateColumnIndex), _state = dataReader.IsDBNull(stateColumnIndex) ? string.Empty : dataReader.GetString(stateColumnIndex), _newInformation = ConverterHelper.ObjectToBoolConverter(dataReader[newInformationReceivedColumnIdx]?.ToString()), _sysEntity = dataReader.IsDBNull(sysEntityColumnIndex) ? string.Empty : dataReader.GetString(sysEntityColumnIndex), _ticketType = dataReader.IsDBNull(ticketTypeColumnIndex) ? string.Empty : dataReader.GetString(ticketTypeColumnIndex), _ticketNumber = dataReader.IsDBNull(ticketNumberColumnIndex) ? string.Empty : dataReader.GetString(ticketNumberColumnIndex), _lastJournalEntryAction = dataReader.IsDBNull(lastJournalActionColumnIndex) ? -1 : dataReader.GetInt32(lastJournalActionColumnIndex), _stateValue = dataReader.GetInt32(stateValueColumnIndex), _lastJournalEntryDate = dataReader.IsDBNull(lastJournalEntryDateColumnIndex) ? DateTime.MinValue : dataReader.GetDateTime(lastJournalEntryDateColumnIndex) }; tickets.Add(ticket); } sPSTransactionScope.Complete(); } } catch (Exception ex) { LogException(ex); } finally { LogMethodEnd(CM); } return tickets; } internal bool GetCloseAllClients() { var CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); try { var classId = SPSDataEngineSchemaReader.ClassGetIDFromName("C4IT_CustomerPanel_SetupClassBase"); DataTable result = FragmentRequestBase.SimpleLoad(classId, "closeAllClients", $"id='{CustomerPanelConfigID:D}'"); if (result == null || result.Rows.Count == 0) { return false; } string closeAllClients = result.Rows[0]["closeAllClients"]?.ToString(); return ConverterHelper.ObjectToBoolConverter(closeAllClients); } catch (Exception ex) { LogException(ex); return false; } finally { LogMethodEnd(CM); } } internal List GetAdHocAnnouncements(bool noCache) { var CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); try { var _globalSettings = CustomerPanelController.controller.GlobalSettings; // Client-Sprache ermitteln (Fallback: en) string defaultClientLanguageTwoLetterCode = "en"; string acceptLanguageHeader = HttpContext.Current?.Request?.Headers["Accept-Language"]; if (!string.IsNullOrWhiteSpace(acceptLanguageHeader)) { try { var cultureInfo = new CultureInfo(acceptLanguageHeader.Split(',')[0]); defaultClientLanguageTwoLetterCode = cultureInfo.TwoLetterISOLanguageName; } catch { LogEntry("Identifying the client user culture based on 'Accept-Language' parameter '" + acceptLanguageHeader + "' failed", LogLevels.Debug); } } // Sprache aus Settings (Fallbacks, falls nicht vorhanden) var languageInfo = _globalSettings.GetLanguages().FirstOrDefault(lang => lang.TwoLetterCode == defaultClientLanguageTwoLetterCode) ?? _globalSettings.GetLanguages().FirstOrDefault(lang => lang.TwoLetterCode == "en"); int lcidLong = languageInfo?.LCID ?? 1033; // en-US int lcidShort = languageInfo?.ParentLCID ?? 9; // EN string langCode = languageInfo?.TwoLetterCode ?? "en"; // Cache-Key pro Sprache string cacheKey = "AdHocMessages_" + langCode; var _cachedAnnouncements = ApiCache.GetValue(cacheKey) as List; if (noCache && _cachedAnnouncements != null) { ApiCache.Delete(cacheKey); _cachedAnnouncements = null; } if (_cachedAnnouncements == null) { // Cache-Dauer aus Konfiguration (Fallback 2 Minuten wie in GetConfig-Defaults) int timerIntervalAdHoc = 2; try { timerIntervalAdHoc = GetConfig(noCache)._timerIntervalAdHocAnnouncements; } catch { /* defensiv */ } using (var sPSTransactionScope = new SPSTransactionScope()) using (var dataReader = DirectDbCompat.ExecuteReader( dbCmd => { dbCmd.CommandText = adHocAnnouncementsSQLStatement; dbCmd.CommandType = CommandType.Text; var pLong = dbCmd.CreateParameter(); pLong.ParameterName = "@LCID_long"; pLong.DbType = DbType.Int32; pLong.Value = lcidLong; dbCmd.Parameters.Add(pLong); var pShort = dbCmd.CreateParameter(); pShort.ParameterName = "@LCID_short"; pShort.DbType = DbType.Int32; pShort.Value = lcidShort; dbCmd.Parameters.Add(pShort); }, SPSTransaction.Current, CommandBehavior.SingleResult)) { // Spalten-Indizes einmalig ermitteln int announcementSubjectColumnIndex = dataReader.GetOrdinal("AnnouncementSubject"); int eoidColumnIndex = dataReader.GetOrdinal("eoid"); int announcemntCreatedDateColumnIndex = dataReader.GetOrdinal("AnnouncementCreatedDate"); int announcementVisibleFromColumnIndex = dataReader.GetOrdinal("AnnouncementVisibleFrom"); int messageColumnIndex = dataReader.GetOrdinal("Message"); int prioColorColumnIndex = dataReader.GetOrdinal("prioColor"); int priorityColumnIndex = dataReader.GetOrdinal("priority"); _cachedAnnouncements = new List(); while (dataReader.Read()) { var announcement = new Announcement { _subject = dataReader.IsDBNull(announcementSubjectColumnIndex) ? string.Empty : dataReader.GetString(announcementSubjectColumnIndex), _objectID = dataReader.GetGuid(eoidColumnIndex), _createdDate = dataReader.IsDBNull(announcemntCreatedDateColumnIndex) ? DateTime.MinValue : dataReader.GetDateTime(announcemntCreatedDateColumnIndex), _visibleFrom = dataReader.IsDBNull(announcementVisibleFromColumnIndex) ? (DateTime?)null : dataReader.GetDateTime(announcementVisibleFromColumnIndex), _message = dataReader.IsDBNull(messageColumnIndex) ? string.Empty : dataReader.GetString(messageColumnIndex), _prioColor = dataReader.IsDBNull(prioColorColumnIndex) ? string.Empty : dataReader.GetString(prioColorColumnIndex), _priority = dataReader.IsDBNull(priorityColumnIndex) ? 0 : dataReader.GetInt32(priorityColumnIndex), _isAdhoc = true }; // Tags entfernen und HTML decodieren announcement._message = ConverterHelper.Html2Plaintext(announcement._message); _cachedAnnouncements.Add(announcement); } // Cache setzen nach erfolgreichem Read ApiCache.Add(cacheKey, _cachedAnnouncements, DateTime.Now.AddMinutes(timerIntervalAdHoc)); sPSTransactionScope.Complete(); } } return _cachedAnnouncements; } catch (Exception ex) { LogException(ex); return null; } finally { LogMethodEnd(CM); } } internal List GetRegularAnnouncements(Guid? userId = null) { var CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); var announcements = new List(); if (userId == null || !userId.HasValue) { LogEntry("UserId is null", LogLevels.Error); return announcements; } try { var _globalSettings = CustomerPanelController.controller.GlobalSettings; // Client-Sprache bestimmen (Fallback: en) string defaultClientLanguageTwoLetterCode = "en"; string acceptLanguageHeader = HttpContext.Current?.Request?.Headers["Accept-Language"]; if (!string.IsNullOrWhiteSpace(acceptLanguageHeader)) { try { var cultureInfo = new CultureInfo(acceptLanguageHeader.Split(',')[0]); defaultClientLanguageTwoLetterCode = cultureInfo.TwoLetterISOLanguageName; } catch { LogEntry("Identifying the client user culture based on 'Accept-Language' parameter '" + acceptLanguageHeader + "' failed", LogLevels.Debug); } } // Sprache aus Settings (Fallbacks, falls keine passende Sprache konfiguriert ist) var languageInfo = _globalSettings.GetLanguages().FirstOrDefault(lang => lang.TwoLetterCode == defaultClientLanguageTwoLetterCode) ?? _globalSettings.GetLanguages().FirstOrDefault(lang => lang.TwoLetterCode == "en"); int lcidLong = languageInfo?.LCID ?? 1033; // en-US int lcidShort = languageInfo?.ParentLCID ?? 9; // EN int recursiveOU = 0; using (var sPSTransactionScope = new SPSTransactionScope()) using (var dataReader = DirectDbCompat.ExecuteReader( dbCmd => { dbCmd.CommandText = regularAnnouncementsSQLStatement; dbCmd.CommandType = CommandType.Text; // @LCID_long var pLong = dbCmd.CreateParameter(); pLong.ParameterName = "@LCID_long"; pLong.DbType = DbType.Int32; pLong.Value = lcidLong; dbCmd.Parameters.Add(pLong); // @LCID_short var pShort = dbCmd.CreateParameter(); pShort.ParameterName = "@LCID_short"; pShort.DbType = DbType.Int32; pShort.Value = lcidShort; dbCmd.Parameters.Add(pShort); // @CurrentUserId var pUser = dbCmd.CreateParameter(); pUser.ParameterName = "@CurrentUserId"; pUser.DbType = DbType.Guid; pUser.Value = userId.Value; dbCmd.Parameters.Add(pUser); // @RecursiveOU var pRec = dbCmd.CreateParameter(); pRec.ParameterName = "@RecursiveOU"; pRec.DbType = DbType.Int32; pRec.Value = recursiveOU; dbCmd.Parameters.Add(pRec); }, SPSTransaction.Current, CommandBehavior.SingleResult)) { // Spalten-Indizes nur einmal bestimmen int announcementSubjectColumnIndex = dataReader.GetOrdinal("AnnouncementSubject"); int eoidColumnIndex = dataReader.GetOrdinal("eoid"); int announcemntCreatedDateColumnIndex = dataReader.GetOrdinal("AnnouncementCreatedDate"); int messageColumnIndex = dataReader.GetOrdinal("Message"); int prioColorColumnIndex = dataReader.GetOrdinal("prioColor"); int priorityColumnIndex = dataReader.GetOrdinal("priority"); int announcementVisibleFromColumnIndex = dataReader.GetOrdinal("AnnouncementVisibleFrom"); while (dataReader.Read()) { var announcement = new Announcement { _subject = dataReader.IsDBNull(announcementSubjectColumnIndex) ? string.Empty : dataReader.GetString(announcementSubjectColumnIndex), _objectID = dataReader.GetGuid(eoidColumnIndex), _createdDate = dataReader.IsDBNull(announcemntCreatedDateColumnIndex) ? DateTime.MinValue : dataReader.GetDateTime(announcemntCreatedDateColumnIndex), _visibleFrom = dataReader.IsDBNull(announcementVisibleFromColumnIndex) ? (DateTime?)null : dataReader.GetDateTime(announcementVisibleFromColumnIndex), _message = dataReader.IsDBNull(messageColumnIndex) ? string.Empty : dataReader.GetString(messageColumnIndex), _prioColor = dataReader.IsDBNull(prioColorColumnIndex) ? string.Empty : dataReader.GetString(prioColorColumnIndex), _priority = dataReader.IsDBNull(priorityColumnIndex) ? 0 : dataReader.GetInt32(priorityColumnIndex) }; // Tags entfernen & HTML decodieren announcement._message = ConverterHelper.Html2Plaintext(announcement._message); announcements.Add(announcement); } sPSTransactionScope.Complete(); } } catch (Exception ex) { LogException(ex); } finally { LogMethodEnd(CM); } return announcements; } internal string Encode(string str) { return PrivateCustomerPanelSecurePassword.Instance.Encode(str); } } }