using Sarand.Net.Standard.Dbas.Data; using Sarand.Net.Standard.Base.App; using Sarand.Net.Standard.Base.Bus; using Sarand.Net.Standard.Base.Core; using Sarand.Net.Standard.Base.Dbas; using Saxonite.Base.App; using Saxonite.Net.Standard.Dbas.Bus; using Saxonite.Net.Standard.Dbas.Internal; using Saxonite.Net.Standard.Base.Bus; using System; using System.Collections.Generic; using System.Data.Common; using System.Text; using Saxonite.Net.Standard.Base.App; using System.Linq; using Sarand.Net.Standard.Base.Utils; using Saxonite.Net.Standard.Dbas.Internal.Core; namespace Saxonite.Net.Standard.Dbas.Mgr { //======================================================================= partial class SaxAccountMgr_Dbas { //******************************************************************* // Account Mgr - User Role //******************************************************************* // Utilities. private List GetLogProperties(SaxUserOnline userOnline) { return new List() { new StringPair("UserAccountId",SysId.ToString(userOnline.UserAccountId)), new StringPair("OnlineAt",DateUtil.Format(userOnline.OnlineAt, "d-MMM-yyyy HH:mm:ss")), }; } public override bool CommitUsersOnline(IEnumerable usersOnline) { String fn = GetType().Name + ".CommitUsersOnline"; try { // Merge query - insert missing User Online records and update existing. List query = new List() { "Merge", " Into " + SaxDbDfn_Core.GetTableName(SaxDbDfn_Core.TableIdx.UserOnline) + " As UsrOnl", // Virtual table defining Activity Item Ids currently listed. " Using", " (", " Select UserAccountId, OnlineAt From (Select 0 UserAccountId, null OnlineAt" + usersOnline.Select(item => " Union Select " + item.UserAccountId.ToString() + ", " + DbQuery.FormatDateTimeForCondition(item.OnlineAt)).ToList().Concat("", " ") + ") Dat Where Dat.UserAccountId>0 And Dat.OnlineAt Is Not Null", " ) As DatOnl", " On UsrOnl.UserAccountId=DatOnl.UserAccountId", // Matched - Update the User Online to store latest Online data. " When Matched Then", " Update Set OnlineAt=DatOnl.OnlineAt", // Not Matched By Target - Insert the User Online with latest data. " When Not Matched Then", " Insert", " (UserAccountId, OnlineAt)", " Values (DatOnl.UserAccountId, DatOnl.OnlineAt)", ";" }; String sql = query.Concat("\n"); //SaLogger.Debug(fn + " Query:\n" + sql); ISacsDatabase db = CoreDatabase; int rows = db.ExecuteNonQuery(sql); if (db.HasErrorState()) SaLogger.Error(fn + " Database error: " + db.ErrorStateMessage); SaLogger.Debug(fn + " UserOnline rows modified: " + rows); } catch (Exception exc) { SaLogger.Warn(fn + " " + ExceptionUtil.GetLogMessage(exc, 0)); return false; } return true; } //------------------------------------------------------------------- // Create item. public override SaxUserOnline CreateUserOnline(SysId userAccountId) { ClearErrorState(); return new SaxUserOnline_Dbas(CoreDatabase, userAccountId); } private SaxUserOnline_Dbas CreateUserOnline(UserOnline_Db dbUserOnline) { ClearErrorState(); SaxUserOnline_Dbas userOnline = new SaxUserOnline_Dbas(CoreDatabase, new SysId(dbUserOnline._UserAccountId)); PopulateFrom(dbUserOnline, userOnline); return userOnline; } //------------------------------------------------------------------- // Get item. public override SaxUserOnline GetUserOnline(SaxUserOnline.Params prm) { ClearErrorState(); return GetUserOnline(GetUserOnlineQueryFilter(prm).Concat(" And ", "(", ")")); } private SaxUserOnline GetUserOnline(String filter) { ClearErrorState(); long count = GetUserOnlineCount(filter); if (count == 0) return null; if (count != 1) { SetErrorState("Multiple items found"); return null; } SaxUserOnline userOnline = null; // Query data. using (var dbConnection = CoreDatabase.CreateConnection()) using (var dbCommand = dbConnection.CreateCommand()) { try { dbConnection.Open(); UserOnline_Db dbUserOnline = new UserOnline_Db(CoreDbInfo); dbUserOnline.PopulateFrom(dbCommand, aliasUserOnline, filter); userOnline = CreateUserOnline(dbUserOnline); } catch (Exception exc) { SetErrorState("Database query exception"); SaLogger.Warn($"{SysUtil.Fn}\n\tQuery: {dbCommand.CommandText}\n\t{ExceptionUtil.GetErrorStateMessage(exc)}"); } } return userOnline; } //------------------------------------------------------------------- // Validate item. public override bool IsValidUserOnlineData(SaxUserOnline.Data data, SaxUserOnline.ErrField errField) { ClearErrorState(); if (!base.IsValidUserOnlineData(data, errField)) return false; return true; } //------------------------------------------------------------------- // Insert item. public override bool InsertUserOnline(SaxUserOnline userOnline, DbUpdateInfo dbUpdateInfo) { String fn = GetType().Name + ".InsertUserOnline"; ClearErrorState(); if (!IsValidUserOnline(userOnline)) return false; if (SysId.IsValid(userOnline.Id)) return SetErrorState(ErrorMessage.ValidItemId); using (var dbConnection = CoreDatabase.CreateConnection()) using (var dbCommand = dbConnection.CreateCommand()) { try { dbConnection.Open(); UserOnline_Db dbUserOnline = new UserOnline_Db(CoreDbInfo); PopulateTo(dbUserOnline, userOnline, dbUpdateInfo); if (UserOnline_Db.FieldsNonId.Contains("Id")) { // User Online has no Id. } dbUserOnline.ExecuteInsertCommand(dbCommand); userOnline.Changed = false; if (DebugMode) SaLogger.Debug(fn + " Inserted. " + GetLogProperties(userOnline).Concat(": ").Concat("", "\n\t")); } catch (Exception exc) { SetErrorState(exc.Message); } } return !HasErrorState(); } //------------------------------------------------------------------- // Update item. public override bool UpdateUserOnline(SaxUserOnline userOnline, DbUpdateInfo dbUpdateInfo) { String fn = GetType().Name + ".UpdateUserOnline"; ClearErrorState(); if (!IsValidUserOnline(userOnline)) return false; if (!SysId.IsValid(userOnline.Id)) return SetErrorState(ErrorMessage.InvalidItemId); if (!userOnline.Changed) { if (DebugMode) SaLogger.Debug(fn + " Data not changed. " + GetLogProperties(userOnline).Concat(": ").Concat("", "\n\t")); return true; } using (var dbConnection = CoreDatabase.CreateConnection()) using (var dbCommand = dbConnection.CreateCommand()) { try { // Retrieve item. dbConnection.Open(); UserOnline_Db dbUserOnline = new UserOnline_Db(CoreDbInfo); dbUserOnline.PopulateFrom(dbCommand, aliasUserOnline, "UserAccountId=" + SysId.ToString(userOnline.UserAccountId)); // ToDo SaxUpdateLogItemMonitor_UserOnline updateLogMonitor = new SaxUpdateLogItemMonitor_UserOnline(SessionContext, CreateUserOnline(dbUserOnline), dbUpdateInfo); // Update item. PopulateTo(dbUserOnline, userOnline, dbUpdateInfo); dbUserOnline.ExecuteUpdateCommand(dbCommand); userOnline.Changed = false; if (DebugMode) SaLogger.Debug(fn + " Updated. " + GetLogProperties(userOnline).Concat(": ").Concat("", "\n\t")); // Record updates. // ToDo updateLogMonitor.RecordUpdatedValues(userOnline); // ToDo updateLogMonitor.LogUpdates(); } catch (Exception exc) { SetErrorState(exc.Message); } } return !HasErrorState(); } //------------------------------------------------------------------- // Delete item. public override bool UserOnlineCanBeDeleted(SaxUserOnline userOnline) { ClearErrorState(); if (!base.UserOnlineCanBeDeleted(userOnline)) return false; return true; } public override bool DeleteUserOnline(SaxUserOnline userOnline, DbUpdateInfo dbUpdateInfo) { String fn = GetType().Name + ".DeleteUserOnline"; ClearErrorState(); if (!UserOnlineCanBeDeleted(userOnline)) return false; using (var dbConnection = CoreDatabase.CreateConnection()) using (var dbCommand = dbConnection.CreateCommand()) { try { // Retrieve item. dbConnection.Open(); UserOnline_Db dbUserOnline = new UserOnline_Db(CoreDbInfo); dbUserOnline.PopulateFrom(dbCommand, aliasUserOnline, "Id=" + userOnline.Id.ToString()); // Delete item. dbCommand.CommandType = System.Data.CommandType.Text; dbCommand.CommandText = "DELETE FROM " + SaxDbDfn_Core.GetTableName(SaxDbDfn_Core.TableIdx.UserOnline) + " WHERE UserAccountId=" + SysId.ToString(userOnline.UserAccountId) + ";"; dbCommand.ExecuteNonQuery(); userOnline.Changed = false; if (DebugMode) SaLogger.Debug(fn + " Deleted. " + GetLogProperties(userOnline).Concat(": ").Concat("", "\n\t")); } catch (Exception exc) { SetErrorState(exc.Message); } } return !HasErrorState(); } //------------------------------------------------------------------- // Copy data properties to and from database. private void PopulateTo(UserOnline_Db dbUserOnline, SaxUserOnline userOnline, DbUpdateInfo dbUpdateInfo) { dbUserOnline._UserAccountId = SysId.ToInt(userOnline.UserAccountId); dbUserOnline._OnlineAt = userOnline.OnlineAt; } private void PopulateFrom(UserOnline_Db dbUserOnline, SaxUserOnline userOnline) { //userOnline.UserAccountId = new SysId(dbUserOnline._UserAccountId); userOnline.OnlineAt = dbUserOnline._OnlineAt; } //------------------------------------------------------------------- // Item column definitions. private static readonly String aliasUserOnline = "UsrRol"; private static readonly String aliasUserOnline_UserAccount = "UsrRol_Usr"; private static readonly String prefixUserOnline_UserAccount = "UsrRol_Usr_"; private static readonly String aliasUserOnline_UserSport = "UsrOnl_UsrSpt"; private void SetUserOnlineColDfn (ColumnDfn col, String caption, int width, SaxDbDfn_Core.ColumnIdx dbCol, ColumnAlignment align = ColumnAlignment.Default, DataValue.DataType dataType = DataValue.DataType.String, String alias = "", String prefix = "") { if (String.IsNullOrEmpty(alias) && SaxDbDfn_Core.GetTableForColumn(dbCol) == SaxDbDfn_Core.TableIdx.UserOnline) alias = aliasUserOnline; col.Set(caption, width, (int)dbCol, prefix + SaxDbDfn_Core.GetColumnName(dbCol, false), SaxDbDfn_Core.GetColumnNameQual(dbCol, alias), dataType, align); if (dataType == DataValue.DataType.String && dbCol != SaxDbDfn_Core.ColumnIdx.Unknown) col.MaxLength = (int)SaxDbDfn_Core.GetColTextLen(dbCol); } public override ColumnDfn GetUserOnlineColumnDfn(SaxUserOnline.Column columnId, string caption = "", int width = 0) { ColumnDfn columnDfn = new ColumnDfn((int)columnId, caption, width); switch (columnId) { //----------------------------------------------------------- case SaxUserOnline.Column.Pos: SetUserOnlineColDfn(columnDfn, "Pos", 60, SaxDbDfn_Core.ColumnIdx.Unknown, ColumnAlignment.Right); columnDfn.SortMode = ColumnDfn.ColumnSortMode.Off; columnDfn.ReadOnly = true; columnDfn.Resizable = false; break; case SaxUserOnline.Column.UserAccountId: SetUserOnlineColDfn(columnDfn, "User Id", 80, SaxDbDfn_Core.ColumnIdx.UserOnline_UserAccountId, ColumnAlignment.Right, DataValue.DataType.Long); columnDfn.ReadOnly = true; break; case SaxUserOnline.Column.OnlineOn: SetUserOnlineColDfn(columnDfn, "Online", 80, SaxDbDfn_Core.ColumnIdx.UserOnline_OnlineAt, ColumnAlignment.Center, DataValue.DataType.Date); columnDfn.ReadOnly = true; break; case SaxUserOnline.Column.OnlineAt: SetUserOnlineColDfn(columnDfn, "Online", 100, SaxDbDfn_Core.ColumnIdx.UserOnline_OnlineAt, ColumnAlignment.Center, DataValue.DataType.DateTime); columnDfn.ReadOnly = true; break; //------------------------------------------------------- // User properties. case SaxUserOnline.Column.EmailAddress: SetUserOnlineColDfn(columnDfn, "Email", 160, SaxDbDfn_Core.ColumnIdx.UserAccount_EmailAddress, ColumnAlignment.Left, DataValue.DataType.String, aliasUserOnline_UserAccount, prefixUserOnline_UserAccount); columnDfn.ReadOnly = true; break; case SaxUserOnline.Column.DisplayName: SetUserOnlineColDfn(columnDfn, "User", 160, SaxDbDfn_Core.ColumnIdx.UserAccount_LastName, ColumnAlignment.Left, DataValue.DataType.String, aliasUserOnline_UserAccount, prefixUserOnline_UserAccount); columnDfn.ReadOnly = true; break; case SaxUserOnline.Column.Title: SetUserOnlineColDfn(columnDfn, "Title", 70, SaxDbDfn_Core.ColumnIdx.UserAccount_Title, ColumnAlignment.Left, DataValue.DataType.String, aliasUserOnline_UserAccount, prefixUserOnline_UserAccount); columnDfn.ReadOnly = true; break; case SaxUserOnline.Column.FirstName: SetUserOnlineColDfn(columnDfn, "First name", 120, SaxDbDfn_Core.ColumnIdx.UserAccount_FirstName, ColumnAlignment.Left, DataValue.DataType.String, aliasUserOnline_UserAccount, prefixUserOnline_UserAccount); columnDfn.ReadOnly = true; break; case SaxUserOnline.Column.LastName: SetUserOnlineColDfn(columnDfn, "Last name", 120, SaxDbDfn_Core.ColumnIdx.UserAccount_LastName, ColumnAlignment.Left, DataValue.DataType.String, aliasUserOnline_UserAccount, prefixUserOnline_UserAccount); columnDfn.ReadOnly = true; break; case SaxUserOnline.Column.LastLoginOn: SetUserOnlineColDfn(columnDfn, "Last login on", 80, SaxDbDfn_Core.ColumnIdx.UserAccount_LastLoginAt, ColumnAlignment.Center, DataValue.DataType.Date, aliasUserOnline_UserAccount, prefixUserOnline_UserAccount); columnDfn.ReadOnly = true; break; case SaxUserOnline.Column.LastLoginAt: SetUserOnlineColDfn(columnDfn, "Last login", 100, SaxDbDfn_Core.ColumnIdx.UserAccount_LastLoginAt, ColumnAlignment.Center, DataValue.DataType.DateTime, aliasUserOnline_UserAccount, prefixUserOnline_UserAccount); columnDfn.ReadOnly = true; break; //------------------------------------------------------- } if (!String.IsNullOrEmpty(caption)) columnDfn.Caption = caption; if (width > 0) columnDfn.Width = width; return columnDfn; } //------------------------------------------------------------------- // Item query filter. public override List GetUserOnlineQueryFilter(SaxUserOnline.Params prm) { List filter = new List(); // User accounts. String userAccountIdsIncluded = prm.UserAccountIdsIncluded.Concat(","); if (!userAccountIdsIncluded.IsEmpty()) filter.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserOnline_UserAccountId, aliasUserOnline) + " IN (" + userAccountIdsIncluded + ")"); // Online On. if (DateUtil.IsValid(prm.OnlineOnFrom)) filter.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserOnline_OnlineAt, aliasUserOnline) + ">=" + DbQuery.FormatDateForCondition(prm.OnlineOnFrom)); if (DateUtil.IsValid(prm.OnlineOnTo)) filter.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserOnline_OnlineAt, aliasUserOnline) + "<" + DbQuery.FormatDateForCondition(DateUtil.IncrementDate(prm.OnlineOnTo, 1))); // User Account filters. String aliasUserOnline_UserAccount = StringUtil.OnEmpty(prm.Alias_UserAccount, SaxAccountMgr_Dbas.aliasUserOnline_UserAccount); List userAccountFilters = new List(); // User filter - partial. userAccountFilters.AddRange(GetUserAccountFilters(prm.UserFilter, aliasUserOnline_UserAccount)); userAccountFilters.AddRange(GetPartialUserAccountFilters(prm.UserFilterPartial, aliasUserOnline_UserAccount)); // Sport Ids. String aliasUserOnline_UserSport = StringUtil.OnEmpty(prm.Alias_UserSport, SaxAccountMgr_Dbas.aliasUserOnline_UserSport); String sportIdsIncluded = prm.SportIdsIncluded.Concat(","); if (!sportIdsIncluded.IsEmpty()) { List userSportFilters = new List(); userSportFilters.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserSport_SportId, aliasUserOnline_UserSport) + " IN (" + sportIdsIncluded + ")"); if (!userSportFilters.IsEmpty()) { if (prm.Alias_UserSport.IsEmpty()) { // User Sport table is not linked in query. DbQuery userSportQuery = new DbQuery(CoreDatabase.DbmsType); userSportQuery.Tables.Add(SaxDbDfn_Core.GetTableName(SaxDbDfn_Core.TableIdx.UserSport), aliasUserOnline_UserSport); userSportQuery.Columns.Add("1"); userSportQuery.Filters.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserSport_UserAccountId, aliasUserOnline_UserSport) + "=" + SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserAccount_Id, aliasUserOnline_UserAccount)); userSportQuery.Filters.AddRange(userSportFilters); filter.Add("Exists(" + userSportQuery.BuildQuery() + ")"); } else // User Sport table is already linked in the query. filter.AddRange(userSportFilters); } } // Region Ids. String regionIdsIncluded = prm.RegionIdsIncluded.Concat(","); if (!regionIdsIncluded.IsEmpty()) { List userRegionFilters = new List(); userRegionFilters.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserAccount_RegionIdCountry, aliasUserOnline_UserAccount) + " IN (" + regionIdsIncluded + ")"); userRegionFilters.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserAccount_RegionIdProvince, aliasUserOnline_UserAccount) + " IN (" + regionIdsIncluded + ")"); userAccountFilters.Add(userRegionFilters.Concat(" OR ", "", "")); } // User Account filter. if (!userAccountFilters.IsEmpty()) { if (prm.Alias_UserAccount.IsEmpty()) { // User Account table is not linked in query. DbQuery userAccountQuery = new DbQuery(CoreDatabase.DbmsType); userAccountQuery.Tables.Add(SaxDbDfn_Core.GetTableName(SaxDbDfn_Core.TableIdx.UserAccount), aliasUserOnline_UserAccount); userAccountQuery.Columns.Add("1"); userAccountQuery.Filters.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserAccount_Id, aliasUserOnline_UserAccount) + "=" + SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserOnline_UserAccountId, aliasUserOnline)); userAccountQuery.Filters.AddRange(userAccountFilters); filter.Add("Exists(" + userAccountQuery.BuildQuery() + ")"); } else // User Account table is already linked in the query. filter.AddRange(userAccountFilters); } if (filter.Count < 1) filter.Add("1=1"); return filter; } // Item sort columns. public List GetUserOnlineSortColumns(SaxUserOnline.Params prm) { // Sort sequence: List sortColumnNames = new List(); foreach (var columnDfn in prm.SortColumns) { // Need to ensure correct alias used for UserOnline columns in case of conflict. String sortCol = String.Empty; if (SaxDbDfn_Core.GetTableForColumn((SaxDbDfn_Core.ColumnIdx)columnDfn.DbColumn) == SaxDbDfn_Core.TableIdx.UserOnline) { sortCol = SaxDbDfn_Core.GetColumnNameQual((SaxDbDfn_Core.ColumnIdx)columnDfn.DbColumn, aliasUserOnline); } else { sortCol = columnDfn.DataName; if (!StringUtil.IsEmpty(columnDfn.SortDataName)) sortCol = columnDfn.SortDataName; } if (!sortCol.IsEmpty()) { DbOrderByNullMode orderByNullMode = DbOrderByNullMode.Default; // eSaOrderByDemoteNulls, eSaOrderByPromoteNulls sortColumnNames.Add(DbQuery.FormatForOrderBy(sortCol, columnDfn.SortAscending, orderByNullMode).Concat(", ")); } } sortColumnNames.RemoveBlanks(); if (sortColumnNames.IsEmpty()) { // Default sort columns. sortColumnNames.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserOnline_OnlineAt, aliasUserOnline) + " DESC"); } return sortColumnNames; } //------------------------------------------------------------------- // Item count. private long GetUserOnlineCount(String filter) { ClearErrorState(); return CoreDatabase.GetRowCount(SaxDbDfn_Core.GetTableName(SaxDbDfn_Core.TableIdx.UserOnline) + " As " + aliasUserOnline, filter); } public override long GetUserOnlineCount(SaxUserOnline.Params prm) { ClearErrorState(); return GetUserOnlineCount(GetUserOnlineQueryFilter(prm).Concat(" AND ", "(", ")")); } //------------------------------------------------------------------- // Item list. public override List GetUserOnlineList(SaxUserOnline.Params prm) { ClearErrorState(); List userOnlines = new List(); DbQuery qry = new DbQuery(CoreDatabase.DbmsType); qry.Tables.Add(SaxDbDfn_Core.GetTableName(SaxDbDfn_Core.TableIdx.UserOnline), aliasUserOnline); UserOnline_Db dbUserOnline = new UserOnline_Db(CoreDbInfo); qry.Columns.AddRange(dbUserOnline.ExistingColumnNames); qry.Filters.AddRange(GetUserOnlineQueryFilter(prm)); qry.OrderBy.AddRange(GetUserOnlineSortColumns(prm)); // Query data. using (var dbConnection = CoreDatabase.CreateConnection()) using (var dbCommand = dbConnection.CreateCommand()) { try { dbConnection.Open(); dbCommand.CommandText = qry.BuildQuery(prm.ResultsPaging); using (DbDataReader dbDataReader = dbCommand.ExecuteReader()) { while (dbDataReader.Read()) { dbUserOnline = new UserOnline_Db(CoreDbInfo); dbUserOnline.PopulateFrom(dbDataReader); userOnlines.Add(CreateUserOnline(dbUserOnline)); } } } catch (Exception exc) { SetErrorState("Database query exception"); SaLogger.Warn($"{SysUtil.Fn}\n\tQuery: {dbCommand.CommandText}\n\t{ExceptionUtil.GetErrorStateMessage(exc)}"); } } return userOnlines; } //------------------------------------------------------------------- // Item query execution. public override DbResultSet ExecuteUserOnlineQuery(List columns, SaxUserOnline.Params prm) { ClearErrorState(); //--------------------------------------------------------------------- // Construct query. DbQuery query = new DbQuery(CoreDatabase.DbmsType); query.Tables.Add(SaxDbDfn_Core.GetTableName(SaxDbDfn_Core.TableIdx.UserOnline), aliasUserOnline); // Add columns as defined in query list. if (columns.HasId(SaxUserOnline.Column.UserAccountId)) query.Columns.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserOnline_UserAccountId, aliasUserOnline)); if (columns.HasId(SaxUserOnline.Column.OnlineOn) || columns.HasId(SaxUserOnline.Column.OnlineAt)) query.Columns.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserOnline_OnlineAt, aliasUserOnline)); //--------------------------------------------------------------------- // User Account. bool hasUserAccountCols = columns.HasId(SaxUserOnline.Column.EmailAddress) || columns.HasId(SaxUserOnline.Column.DisplayName) || columns.HasId(SaxUserOnline.Column.Title) || columns.HasId(SaxUserOnline.Column.FirstName) || columns.HasId(SaxUserOnline.Column.LastName) || columns.HasId(SaxUserOnline.Column.LastLoginOn) || columns.HasId(SaxUserOnline.Column.LastLoginAt); if (hasUserAccountCols) { prm.Alias_UserAccount = aliasUserOnline_UserAccount; DbQueryJoin joinUserAccount = new DbQueryJoin(DbJoinType.LeftOuter); joinUserAccount.Table = new StringPair(SaxDbDfn_Core.GetTableName(SaxDbDfn_Core.TableIdx.UserAccount), aliasUserOnline_UserAccount); joinUserAccount.Filters.Add(SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserAccount_Id, aliasUserOnline_UserAccount) + "=" + SaxDbDfn_Core.GetColumnNameQual(SaxDbDfn_Core.ColumnIdx.UserOnline_UserAccountId, aliasUserOnline)); List cols = new List(); if (columns.HasId(SaxUserOnline.Column.EmailAddress)) cols.Add(SaxDbDfn_Core.ColumnIdx.UserAccount_EmailAddress); if (columns.HasId(SaxUserOnline.Column.DisplayName) || columns.HasId(SaxUserOnline.Column.Title)) cols.Add(SaxDbDfn_Core.ColumnIdx.UserAccount_Title); if (columns.HasId(SaxUserOnline.Column.DisplayName) || columns.HasId(SaxUserOnline.Column.FirstName)) cols.Add(SaxDbDfn_Core.ColumnIdx.UserAccount_FirstName); if (columns.HasId(SaxUserOnline.Column.DisplayName) || columns.HasId(SaxUserOnline.Column.LastName)) cols.Add(SaxDbDfn_Core.ColumnIdx.UserAccount_LastName); if (columns.HasId(SaxUserOnline.Column.LastLoginOn) || columns.HasId(SaxUserOnline.Column.LastLoginAt)) cols.Add(SaxDbDfn_Core.ColumnIdx.UserAccount_LastLoginAt); if (cols.Count > 0) { foreach (var col in cols) query.Columns.Add(SaxDbDfn_Core.GetColumnNameQual(col, aliasUserOnline_UserAccount), prefixUserOnline_UserAccount + SaxDbDfn_Core.GetColumnName(col, false)); query.Joins.Add(joinUserAccount); } } //--------------------------------------------------------------------- // Populate filter. query.Filters.AddRange(GetUserOnlineQueryFilter(prm)); if (prm.UserAccountIdsIncluded.Count != 1) { // Configure sort sequence. foreach (String sortColumn in GetUserOnlineSortColumns(prm)) query.OrderBy.Add(sortColumn); } //--------------------------------------------------------------------- //return new DbResultSet_DataTableReader(db, query.BuildQuery(prm.ResultsPaging)); return DbResultSet_DataReader.ExecuteQuery(CoreDatabase, query.BuildQuery(prm.ResultsPaging)); } //------------------------------------------------------------------- } //======================================================================= }