public AppController(DbService db)
{
this.db = db;
resultQueue = new ResultsQueue(this, db);
resultQueue.NewResult += ResultsQueueOnNewResult;
logController = new LogController();
options = new Options();
// Tracing
Trace.Listeners.Add(logController);
traceSwitch.Level = TraceLevel.Info;
DbService.dbTraceSwitch.Level = TraceLevel.Info;
if ( options.ReopenLastFile )
{
var lastFile = options.LastFile;
if ( !string.IsNullOrEmpty( lastFile ) && File.Exists( lastFile ) )
{
OpenRace( lastFile );
}
}
clockTime.ClockRunningHandler += (s, b) => clockRunning = b;
// Initial check of results
if ( db.IsDbOpen )
{
db.CheckResults();
}
}
public ActionResult Index(int? id)
{
if (id.HasValue)
page = id.Value;
var model = new DMAIndexModel
{
Tables = db.DMATables.Select(table => new SelectListItem { Value = table.DMATableID.ToString(), Text = table.Alias, Selected = selectedTableId == table.DMATableID }).ToList(),
TableSelected = hasTableSelected,
};
if (!hasTableSelected)
return View(model);
using (var dbs = new DbService(selectedTable.DMAConnectionString.Value))
{
model.SelectedTable = selectedTable;
var keys = selectedTable.GetKeyColumns();
model.Keys = keys.Select(col => col.Name).ToList();
foreach(var key in keys)
{
model.Aliases.Add(key.Name, key.Alias);
}
model.Columns = selectedTable.GetColumnsForListView().Select(col => col.Alias).ToList();
model.SearchableColumns = selectedTable.GetSearchableColumns().ToList();
var query = SqlQueryBuilder.PagedSelect(selectedTable, page, 20, null, null);
model.Data = dbs.Query(query, SqlQueryBuilder.Parameters);
model.RecordCount = dbs.QueryScalar<int>(SqlQueryBuilder.Count(selectedTable), SqlQueryBuilder.Parameters);
model.SelectedPage = page;
return View(model);
}
}
public RepoInfoCache(DbService dbService)
{
_repoInfo = dbService.ColRepoInfo;
}
public FlipCoinCommands(IDataCache data, ICurrencyService cs, DbService db)
{
_images = data.LocalImages;
_cs = cs;
_db = db;
}
public HomeController(ILogger <HomeController> _logger, DbService _dbService)
{
logger = _logger;
dbService = _dbService;
}
public CommandMapCommands(DbService db, DiscordSocketClient client)
{
_db = db;
_client = client;
}
public InhouseCommands(DbService db, IInhouseService service)
{
_db = db;
_service = service;
}
public SourceReviewService(DbService db) : base(db)
{
}
public QuoteCommands(DbService db)
{
_db = db;
}
public EvalLuaCommand(DiscordShardedClient client, MiscService misc, DbService db)
{
_client = client;
_misc = misc;
_db = db;
}
public PollRunner(DbService db, Poll poll)
{
_db = db;
Poll = poll;
}
public BaseController(string ConnectionString)
{
_service = new DbService(ConnectionString);
}
public EventsService(IMapper mapper, DbService dbService, IAccountService accountService)
{
this.mapper = mapper;
this.dbService = dbService;
this.accountService = accountService;
}
public SourceTrackPlaysService(DbService db) : base(db)
{
}
public RepeatCommands(DiscordSocketClient client, DbService db)
{
_client = client;
_db = db;
}
public ClubService(DbService db)
{
_db = db;
}
public NewPacientViewModel(DbService dbService)
{
this.dbService = dbService;
}
public PlantPickCommands(ICurrencyService cs, DbService db)
{
_cs = cs;
_db = db;
}
/// <summary>
/// Job that will run quick SQL queries on a schedule.
///
/// Called by the <see cref="IScheduler" /> when a
/// <see cref="ITrigger" /> fires that is associated with
/// the <see cref="IJob" />.
/// </summary>
public virtual void Execute(IJobExecutionContext context)
{
JobDataMap dataMap = context.JobDetail.JobDataMap;
// get job parms
bool runIntegrityCheck = dataMap.GetBoolean("RunIntegrityCheck");
bool runIndexRebuild = dataMap.GetBoolean("RunIndexRebuild");
bool runStatisticsUpdate = dataMap.GetBoolean("RunStatisticsUpdate");
int commandTimeout = dataMap.GetString("CommandTimeout").AsInteger();
int minimumIndexPageCount = dataMap.GetString("MinimumIndexPageCount").AsInteger();
int minimunFragmentationPercentage = dataMap.GetString("MinimumFragmentationPercentage").AsInteger();
int rebuildThresholdPercentage = dataMap.GetString("RebuildThresholdPercentage").AsInteger();
bool useONLINEIndexRebuild = dataMap.GetString("UseONLINEIndexRebuild").AsBoolean();
string alertEmail = dataMap.GetString("AlertEmail");
StringBuilder resultsMessage = new StringBuilder();
Stopwatch stopwatch;
bool errorsFound = false;
// run integrity check
if (runIntegrityCheck)
{
string databaseName = new RockContext().Database.Connection.Database;
string integrityQuery = $"DBCC CHECKDB('{ databaseName }',NOINDEX) WITH PHYSICAL_ONLY, NO_INFOMSGS";
stopwatch = Stopwatch.StartNew();
int errors = DbService.ExecuteCommand(integrityQuery, System.Data.CommandType.Text, null, commandTimeout);
stopwatch.Stop();
resultsMessage.Append($"Integrity Check took {(stopwatch.ElapsedMilliseconds / 1000)}s");
if (errors > 0)
{
// oh no...
errorsFound = true;
string errorMessage = $"Some errors were reported when running a database integrity check on your Rock database. We'd recommend running the command below under 'Admin Tools > Power Tools > SQL Command' to get further details. <p>DBCC CHECKDB ('{ databaseName }') WITH NO_INFOMSGS, ALL_ERRORMSGS</p>";
resultsMessage.Append(errorMessage);
if (alertEmail.IsNotNullOrWhiteSpace())
{
var globalAttributes = GlobalAttributesCache.Get();
string emailHeader = globalAttributes.GetValue("EmailHeader");
string emailFooter = globalAttributes.GetValue("EmailFooter");
string messageBody = $"{emailHeader} {errorMessage} <p><small>This message was generated from the Rock Database Maintenance Job</small></p>{emailFooter}";
var emailMessage = new RockEmailMessage();
emailMessage.SetRecipients(alertEmail.Split(',').ToList());
emailMessage.Subject = "Rock: Database Integrity Check Error";
emailMessage.Message = messageBody;
emailMessage.Send();
}
}
}
if (!errorsFound)
{
// rebuild fragmented indexes
if (runIndexRebuild)
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("@PageCountLimit", minimumIndexPageCount);
parms.Add("@MinFragmentation", minimunFragmentationPercentage);
parms.Add("@MinFragmentationRebuild", rebuildThresholdPercentage);
parms.Add("@UseONLINEIndexRebuild", useONLINEIndexRebuild);
stopwatch = Stopwatch.StartNew();
DbService.ExecuteCommand("spDbaRebuildIndexes", System.Data.CommandType.StoredProcedure, parms, commandTimeout);
stopwatch.Stop();
resultsMessage.Append($", Index Rebuild took {(stopwatch.ElapsedMilliseconds / 1000)}s");
}
// update statistics
if (runStatisticsUpdate)
{
// derived from http://www.sqlservercentral.com/scripts/Indexing/31823/
// NOTE: Can't use sp_MSForEachtable because it isn't supported on AZURE (and it is undocumented)
// NOTE: Can't use sp_updatestats because it requires membership in the sysadmin fixed server role, or ownership of the database (dbo)
string statisticsQuery = @"
DECLARE updatestats CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
OPEN updatestats
DECLARE @tablename NVARCHAR(max)
DECLARE @Statement NVARCHAR(max)
FETCH NEXT
FROM updatestats
INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'UPDATING STATISTICS [' + @tablename + ']'
SET @Statement = 'UPDATE STATISTICS [' + @tablename + ']'
EXEC sp_executesql @Statement
FETCH NEXT
FROM updatestats
INTO @tablename
END
CLOSE updatestats
DEALLOCATE updatestats
";
stopwatch = Stopwatch.StartNew();
DbService.ExecuteCommand(statisticsQuery, System.Data.CommandType.Text, null, commandTimeout);
stopwatch.Stop();
resultsMessage.Append($", Statistics Update took {(stopwatch.ElapsedMilliseconds / 1000)}s");
}
}
context.Result = resultsMessage.ToString().TrimStart(',');
}
public void deactivateRidePat(string active)
{
DbService db = new DbService();
db.ExecuteQuery("UPDATE RidePat SET statusRidePat='" + active + "' WHERE ridePatNum='" + RidePatNum + "'");
}
public StationController(DbService dbService)
{
_dbService = dbService;
}
public PlayingRotateService(DiscordSocketClient client, IBotConfigProvider bcp,
DbService db, IDataCache cache, NadekoBot bot, MusicService music)
{
_client = client;
_bcp = bcp;
_db = db;
_log = LogManager.GetCurrentClassLogger();
_cache = cache;
if (client.ShardId == 0)
{
_rep = new ReplacementBuilder()
.WithClient(client)
.WithMusic(music)
.Build();
_t = new Timer(async(objState) =>
{
try
{
// bcp.Reload();
var state = (TimerState)objState;
if (!BotConfig.RotatingStatuses)
{
return;
}
if (state.Index >= BotConfig.RotatingStatusMessages.Count)
{
state.Index = 0;
}
if (!BotConfig.RotatingStatusMessages.Any())
{
return;
}
var msg = BotConfig.RotatingStatusMessages[state.Index++];
var status = msg.Status;
if (string.IsNullOrWhiteSpace(status))
{
return;
}
status = _rep.Replace(status);
try
{
await bot.SetGameAsync(status, msg.Type).ConfigureAwait(false);
}
catch (Exception ex)
{
_log.Warn(ex);
}
}
catch (Exception ex)
{
_log.Warn("Rotating playing status errored.\n" + ex);
}
}, new TimerState(), TimeSpan.FromMinutes(1), TimeSpan.FromMinutes(1));
}
}
public StreamingCommands(DbService db, ITwitchPollService service)
{
_db = db;
_service = service;
}
public VcRoleService(DiscordSocketClient client, IEnumerable <GuildConfig> gcs, DbService db)
{
_log = LogManager.GetCurrentClassLogger();
_db = db;
_client = client;
_client.UserVoiceStateUpdated += ClientOnUserVoiceStateUpdated;
VcRoles = new ConcurrentDictionary <ulong, ConcurrentDictionary <ulong, IRole> >();
var missingRoles = new List <VcRoleInfo>();
foreach (var gconf in gcs)
{
var g = _client.GetGuild(gconf.GuildId);
if (g == null)
{
continue;
}
var infos = new ConcurrentDictionary <ulong, IRole>();
VcRoles.TryAdd(gconf.GuildId, infos);
foreach (var ri in gconf.VcRoleInfos)
{
var role = g.GetRole(ri.RoleId);
if (role == null)
{
missingRoles.Add(ri);
continue;
}
infos.TryAdd(ri.VoiceChannelId, role);
}
}
if (missingRoles.Any())
{
using (var uow = _db.UnitOfWork)
{
_log.Warn($"Removing {missingRoles.Count} missing roles from {nameof(VcRoleService)}");
uow._context.RemoveRange(missingRoles);
uow.Complete();
}
}
}
public Xp(DiscordSocketClient client, DbService db)
{
_client = client;
_db = db;
}
public XpService(DiscordSocketClient client, CommandHandler cmd, IBotConfigProvider bc,
NadekoBot bot, DbService db, NadekoStrings strings, IDataCache cache,
FontProvider fonts, IBotCredentials creds, ICurrencyService cs, IHttpClientFactory http)
{
_db = db;
_cmd = cmd;
_bc = bc;
_images = cache.LocalImages;
_log = LogManager.GetCurrentClassLogger();
_strings = strings;
_cache = cache;
_fonts = fonts;
_creds = creds;
_cs = cs;
_httpFactory = http;
InternalReloadXpTemplate();
if (client.ShardId == 0)
{
var sub = _cache.Redis.GetSubscriber();
sub.Subscribe(_creds.RedisKey() + "_reload_xp_template",
(ch, val) => InternalReloadXpTemplate());
}
//load settings
var allGuildConfigs = bot.AllGuildConfigs.Where(x => x.XpSettings != null);
_excludedChannels = allGuildConfigs
.ToDictionary(
x => x.GuildId,
x => new ConcurrentHashSet <ulong>(x.XpSettings
.ExclusionList
.Where(ex => ex.ItemType == ExcludedItemType.Channel)
.Select(ex => ex.ItemId)
.Distinct()))
.ToConcurrent();
_excludedRoles = allGuildConfigs
.ToDictionary(
x => x.GuildId,
x => new ConcurrentHashSet <ulong>(x.XpSettings
.ExclusionList
.Where(ex => ex.ItemType == ExcludedItemType.Role)
.Select(ex => ex.ItemId)
.Distinct()))
.ToConcurrent();
_excludedServers = new ConcurrentHashSet <ulong>(
allGuildConfigs.Where(x => x.XpSettings.ServerExcluded)
.Select(x => x.GuildId));
_cmd.OnMessageNoTrigger += _cmd_OnMessageNoTrigger;
updateXpTask = Task.Run(async() =>
{
while (true)
{
await Task.Delay(TimeSpan.FromSeconds(5));
try
{
var toNotify = new List <(IMessageChannel MessageChannel, IUser User, int Level, XpNotificationLocation NotifyType, NotifOf NotifOf)>();
var roleRewards = new Dictionary <ulong, List <XpRoleReward> >();
var curRewards = new Dictionary <ulong, List <XpCurrencyReward> >();
var toAddTo = new List <UserCacheItem>();
while (_addMessageXp.TryDequeue(out var usr))
{
toAddTo.Add(usr);
}
var group = toAddTo.GroupBy(x => (GuildId: x.Guild.Id, x.User));
if (toAddTo.Count == 0)
{
continue;
}
using (var uow = _db.GetDbContext())
{
foreach (var item in group)
{
var xp = item.Select(x => bc.BotConfig.XpPerMessage).Sum();
//1. Mass query discord users and userxpstats and get them from local dict
//2. (better but much harder) Move everything to the database, and get old and new xp
// amounts for every user (in order to give rewards)
var usr = uow.Xp.GetOrCreateUser(item.Key.GuildId, item.Key.User.Id);
var du = uow.DiscordUsers.GetOrCreate(item.Key.User);
var globalXp = du.TotalXp;
var oldGlobalLevelData = new LevelStats(globalXp);
var newGlobalLevelData = new LevelStats(globalXp + xp);
var oldGuildLevelData = new LevelStats(usr.Xp + usr.AwardedXp);
usr.Xp += xp;
du.TotalXp += xp;
if (du.Club != null)
{
du.Club.Xp += xp;
}
var newGuildLevelData = new LevelStats(usr.Xp + usr.AwardedXp);
if (oldGlobalLevelData.Level < newGlobalLevelData.Level)
{
du.LastLevelUp = DateTime.UtcNow;
var first = item.First();
if (du.NotifyOnLevelUp != XpNotificationLocation.None)
{
toNotify.Add((first.Channel, first.User, newGlobalLevelData.Level, du.NotifyOnLevelUp, NotifOf.Global));
}
}
if (oldGuildLevelData.Level < newGuildLevelData.Level)
{
usr.LastLevelUp = DateTime.UtcNow;
//send level up notification
var first = item.First();
if (usr.NotifyOnLevelUp != XpNotificationLocation.None)
{
toNotify.Add((first.Channel, first.User, newGuildLevelData.Level, usr.NotifyOnLevelUp, NotifOf.Server));
}
//give role
if (!roleRewards.TryGetValue(usr.GuildId, out var rrews))
{
rrews = uow.GuildConfigs.XpSettingsFor(usr.GuildId).RoleRewards.ToList();
roleRewards.Add(usr.GuildId, rrews);
}
if (!curRewards.TryGetValue(usr.GuildId, out var crews))
{
crews = uow.GuildConfigs.XpSettingsFor(usr.GuildId).CurrencyRewards.ToList();
curRewards.Add(usr.GuildId, crews);
}
var rrew = rrews.FirstOrDefault(x => x.Level == newGuildLevelData.Level);
if (rrew != null)
{
var role = first.User.Guild.GetRole(rrew.RoleId);
if (role != null)
{
var __ = first.User.AddRoleAsync(role);
}
}
//get currency reward for this level
var crew = crews.FirstOrDefault(x => x.Level == newGuildLevelData.Level);
if (crew != null)
{
//give the user the reward if it exists
await _cs.AddAsync(item.Key.User.Id, "Level-up Reward", crew.Amount);
}
}
}
uow.SaveChanges();
}
await Task.WhenAll(toNotify.Select(async x =>
{
if (x.NotifOf == NotifOf.Server)
{
if (x.NotifyType == XpNotificationLocation.Dm)
{
var chan = await x.User.GetOrCreateDMChannelAsync();
if (chan != null)
{
await chan.SendConfirmAsync(_strings.GetText("level_up_dm",
(x.MessageChannel as ITextChannel)?.GuildId,
"xp",
x.User.Mention, Format.Bold(x.Level.ToString()),
Format.Bold((x.MessageChannel as ITextChannel)?.Guild.ToString() ?? "-")));
}
}
else // channel
{
await x.MessageChannel.SendConfirmAsync(_strings.GetText("level_up_channel",
(x.MessageChannel as ITextChannel)?.GuildId,
"xp",
x.User.Mention, Format.Bold(x.Level.ToString())));
}
}
else
{
IMessageChannel chan;
if (x.NotifyType == XpNotificationLocation.Dm)
{
chan = await x.User.GetOrCreateDMChannelAsync();
}
else // channel
{
chan = x.MessageChannel;
}
await chan.SendConfirmAsync(_strings.GetText("level_up_global",
(x.MessageChannel as ITextChannel)?.GuildId,
"xp",
x.User.Mention, Format.Bold(x.Level.ToString())));
}
}));
}
catch (Exception ex)
{
_log.Warn(ex);
}
}
});
}
/// <summary>
/// Returns a list of each person and their GroupRequiremnt status for this group requirement
/// </summary>
/// <param name="rockContext">The rock context.</param>
/// <param name="personQry">The person qry.</param>
/// <param name="groupId">The group identifier.</param>
/// <param name="groupRoleId">The group role identifier.</param>
/// <returns></returns>
/// <exception cref="System.Exception">No dataview assigned to Group Requirement Type: " + this.GroupRequirementType.Name</exception>
public IEnumerable <PersonGroupRequirementStatus> PersonQueryableMeetsGroupRequirement(RockContext rockContext, IQueryable <Person> personQry, int groupId, int?groupRoleId)
{
if ((this.GroupRoleId != null) && (groupRoleId != null) && (this.GroupRoleId != groupRoleId))
{
// if this GroupRequirement is for a specific role, the groupRole we are checking for is something different
var result = personQry.ToList().Select(a =>
new PersonGroupRequirementStatus
{
PersonId = a.Id,
GroupRequirement = this,
MeetsGroupRequirement = MeetsGroupRequirement.NotApplicable
});
return(result);
}
if (this.GroupRequirementType.RequirementCheckType == RequirementCheckType.Dataview)
{
if (this.GroupRequirementType.DataViewId.HasValue)
{
var errorMessages = new List <string>();
var personService = new PersonService(rockContext);
var paramExpression = personService.ParameterExpression;
var dataViewWhereExpression = this.GroupRequirementType.DataView.GetExpression(personService, paramExpression, out errorMessages);
var dataViewQry = personService.Get(paramExpression, dataViewWhereExpression);
IQueryable <Person> warningDataViewQry = null;
if (this.GroupRequirementType.WarningDataViewId.HasValue)
{
var warningDataViewWhereExpression = this.GroupRequirementType.WarningDataView.GetExpression(personService, paramExpression, out errorMessages);
warningDataViewQry = personService.Get(paramExpression, warningDataViewWhereExpression);
}
if (dataViewQry != null)
{
var personWithRequirements = from p in personQry
join d in dataViewQry on p equals d into oj
from d in oj.DefaultIfEmpty()
select new { PersonId = p.Id, Included = d != null, WarningIncluded = false };
// if a Warning Database was specified, set the WarningIncluded flag to true if they are included in the Warning Dataview
if (warningDataViewQry != null)
{
personWithRequirements = personWithRequirements.Select(a => new
{
a.PersonId,
a.Included,
WarningIncluded = warningDataViewQry.Any(w => w.Id == a.PersonId)
});
}
var result = personWithRequirements.ToList().Select(a =>
new PersonGroupRequirementStatus
{
PersonId = a.PersonId,
GroupRequirement = this,
MeetsGroupRequirement = a.Included
? (a.WarningIncluded ? MeetsGroupRequirement.MeetsWithWarning : MeetsGroupRequirement.Meets)
: MeetsGroupRequirement.NotMet
});
return(result);
}
}
else
{
throw new Exception("No dataview assigned to Group Requirement Type: " + this.GroupRequirementType.Name);
}
}
else if (this.GroupRequirementType.RequirementCheckType == RequirementCheckType.Sql)
{
// if requirement set on GroupType, this.Group is null
var targetGroup = this.Group ?? new GroupService(rockContext).Get(groupId);
string formattedSql = this.GroupRequirementType.SqlExpression.ResolveMergeFields(this.GroupRequirementType.GetMergeObjects(targetGroup));
string warningFormattedSql = this.GroupRequirementType.WarningSqlExpression.ResolveMergeFields(this.GroupRequirementType.GetMergeObjects(targetGroup));
try
{
var tableResult = DbService.GetDataTable(formattedSql, System.Data.CommandType.Text, null);
if (tableResult.Columns.Count > 0)
{
IEnumerable <int> personIds = tableResult.Rows.OfType <System.Data.DataRow>().Select(r => Convert.ToInt32(r[0]));
IEnumerable <int> warningPersonIds = null;
// if a Warning SQL was specified, get a list of PersonIds that should have a warning with their status
if (!string.IsNullOrWhiteSpace(warningFormattedSql))
{
var warningTableResult = DbService.GetDataTable(warningFormattedSql, System.Data.CommandType.Text, null);
if (warningTableResult.Columns.Count > 0)
{
warningPersonIds = warningTableResult.Rows.OfType <System.Data.DataRow>().Select(r => Convert.ToInt32(r[0]));
}
}
var result = personQry.Select(a => a.Id).ToList().Select(a => new PersonGroupRequirementStatus
{
PersonId = a,
GroupRequirement = this,
MeetsGroupRequirement = personIds.Contains(a)
? ((warningPersonIds != null && warningPersonIds.Contains(a))
? MeetsGroupRequirement.MeetsWithWarning
: MeetsGroupRequirement.Meets
)
: MeetsGroupRequirement.NotMet,
});
return(result);
}
}
catch (Exception ex)
{
// Exception occurred (probably due to bad SQL)
ExceptionLogService.LogException(ex, System.Web.HttpContext.Current);
var result = personQry.Select(a => a.Id).ToList().Select(a => new PersonGroupRequirementStatus
{
PersonId = a,
GroupRequirement = this,
MeetsGroupRequirement = MeetsGroupRequirement.Error
});
return(result);
}
}
else
{
// manual
var groupMemberRequirementQry = new GroupMemberRequirementService(rockContext).Queryable().Where(a => a.GroupMember.GroupId == groupId && a.GroupRequirementId == this.Id && a.RequirementMetDateTime.HasValue);
var result = personQry.ToList().Select(a =>
new PersonGroupRequirementStatus
{
PersonId = a.Id,
GroupRequirement = this,
MeetsGroupRequirement = groupMemberRequirementQry.Any(r => r.GroupMember.PersonId == a.Id) ? MeetsGroupRequirement.Meets : MeetsGroupRequirement.NotMet
});
return(result);
}
// shouldn't happen
return(null);
}
/// <summary>
/// Executes the specified context.
/// </summary>
/// <param name="context">The context.</param>
public void Execute(IJobExecutionContext context)
{
var rockContext = new RockContext();
var metricService = new MetricService(rockContext);
var metricValueService = new MetricValueService(rockContext);
var metricSourceValueTypeDataviewGuid = Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_DATAVIEW.AsGuid();
var metricSourceValueTypeSqlGuid = Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_SQL.AsGuid();
var metricsQry = metricService.Queryable("Schedule").Where(
a => a.ScheduleId.HasValue &&
a.SourceValueTypeId.HasValue &&
(a.SourceValueType.Guid == metricSourceValueTypeDataviewGuid || a.SourceValueType.Guid == metricSourceValueTypeSqlGuid));
var metricsList = metricsQry.OrderBy(a => a.Title).ThenBy(a => a.Subtitle).ToList();
var metricExceptions = new List <Exception>();
foreach (var metric in metricsList)
{
try
{
var lastRunDateTime = metric.LastRunDateTime ?? metric.CreatedDateTime ?? metric.ModifiedDateTime;
if (lastRunDateTime.HasValue)
{
var currentDateTime = RockDateTime.Now;
// get all the schedule times that were supposed to run since that last time it was scheduled to run
var scheduledDateTimesToProcess = metric.Schedule.GetScheduledStartTimes(lastRunDateTime.Value, currentDateTime).Where(a => a > lastRunDateTime.Value).ToList();
foreach (var scheduleDateTime in scheduledDateTimesToProcess)
{
Dictionary <int, decimal> resultValues = new Dictionary <int, decimal>();
if (metric.SourceValueType.Guid == metricSourceValueTypeDataviewGuid)
{
// get the metric value from the DataView
if (metric.DataView != null)
{
var errorMessages = new List <string>();
var qry = metric.DataView.GetQuery(null, null, out errorMessages);
if (metric.EntityTypeId.HasValue)
{
throw new NotImplementedException("Partitioned Metrics using DataViews is not supported.");
}
else
{
resultValues.Add(0, qry.Count());
}
}
}
else if (metric.SourceValueType.Guid == metricSourceValueTypeSqlGuid)
{
// calculate the metricValue assuming that the SQL returns one row with one numeric field
if (!string.IsNullOrWhiteSpace(metric.SourceSql))
{
string formattedSql = metric.SourceSql.ResolveMergeFields(metric.GetMergeObjects(scheduleDateTime));
var tableResult = DbService.GetDataTable(formattedSql, System.Data.CommandType.Text, null);
foreach (var row in tableResult.Rows.OfType <System.Data.DataRow>())
{
int entityId = 0;
decimal countValue;
if (tableResult.Columns.Count >= 2)
{
if (tableResult.Columns.Contains("EntityId"))
{
entityId = Convert.ToInt32(row["EntityId"]);
}
else
{
// assume SQL is in the form "SELECT Count(*), EntityId FROM ..."
entityId = Convert.ToInt32(row[1]);
}
}
if (tableResult.Columns.Contains("Value"))
{
countValue = Convert.ToDecimal(row["Value"]);
}
else
{
// assume SQL is in the form "SELECT Count(*), EntityId FROM ..."
countValue = Convert.ToDecimal(row[0]);
}
resultValues.Add(entityId, countValue);
}
}
}
metric.LastRunDateTime = scheduleDateTime;
if (resultValues.Any())
{
foreach (var resultValue in resultValues)
{
var metricValue = new MetricValue();
metricValue.MetricId = metric.Id;
metricValue.MetricValueDateTime = scheduleDateTime;
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.YValue = resultValue.Value;
metricValue.EntityId = resultValue.Key > 0 ? resultValue.Key : (int?)null;
metricValueService.Add(metricValue);
}
}
rockContext.SaveChanges();
}
}
}
catch (Exception ex)
{
metricExceptions.Add(new Exception(string.Format("Exception when calculating metric for ", metric), ex));
}
}
if (metricExceptions.Any())
{
throw new AggregateException("One or more metric calculations failed ", metricExceptions);
}
}
public UserPunishCommands(DbService db, MuteService mute, ICurrencyService cs)
{
_db = db;
_cs = cs;
_mute = mute;
}
public virtual Recordset FetchRecordset(DbService dbService, bool addFields)
{
if (dbService == null)
{
throw new ArgumentNullException(nameof(dbService));
}
var source = dbService.Source as DbSource;
if (source != null)
{
switch (source.ServerType)
{
case enSourceType.SqlDatabase:
{
var broker = CreateDatabaseBroker();
var outputDescription = broker.TestService(dbService);
if (outputDescription?.DataSourceShapes == null || outputDescription.DataSourceShapes.Count == 0)
{
throw new Exception(ErrorResource.ErrorRetrievingShapeFromServiceOutput);
}
// Clear out the Recordset.Fields list because the sequence and
// number of fields may have changed since the last invocation.
//
// Create a copy of the Recordset.Fields list before clearing it
// so that we don't lose the user-defined aliases.
//
if (dbService.Recordset != null)
{
dbService.Recordset.Name = dbService.Method.ExecuteAction;
if (dbService.Recordset.Name != null)
{
dbService.Recordset.Name = dbService.Recordset.Name.Replace(".", "_");
}
dbService.Recordset.Fields.Clear();
ServiceMappingHelper smh = new ServiceMappingHelper();
smh.MapDbOutputs(outputDescription, ref dbService, addFields);
}
return(dbService.Recordset);
}
case enSourceType.MySqlDatabase:
{
var broker = new MySqlDatabaseBroker();
var outputDescription = broker.TestService(dbService);
if (outputDescription?.DataSourceShapes == null || outputDescription.DataSourceShapes.Count == 0)
{
throw new Exception(ErrorResource.ErrorRetrievingShapeFromServiceOutput);
}
dbService.Recordset.Fields.Clear();
ServiceMappingHelper smh = new ServiceMappingHelper();
smh.MySqlMapDbOutputs(outputDescription, ref dbService, addFields);
return(dbService.Recordset);
}
case enSourceType.PostgreSQL:
{
var broker = new PostgreSqlDataBaseBroker();
var outputDescription = broker.TestService(dbService);
if (outputDescription?.DataSourceShapes == null || outputDescription.DataSourceShapes.Count == 0)
{
throw new Exception(ErrorResource.ErrorRetrievingShapeFromServiceOutput);
}
dbService.Recordset.Fields.Clear();
ServiceMappingHelper smh = new ServiceMappingHelper();
smh.MySqlMapDbOutputs(outputDescription, ref dbService, addFields);
return(dbService.Recordset);
}
case enSourceType.Oracle:
{
var broker = new OracleDatabaseBroker();
var outputDescription = broker.TestService(dbService);
if (outputDescription?.DataSourceShapes == null || outputDescription.DataSourceShapes.Count == 0)
{
throw new Exception(ErrorResource.ErrorRetrievingShapeFromServiceOutput);
}
dbService.Recordset.Fields.Clear();
ServiceMappingHelper smh = new ServiceMappingHelper();
smh.MapDbOutputs(outputDescription, ref dbService, addFields);
return(dbService.Recordset);
}
case enSourceType.ODBC:
{
var broker = new ODBCDatabaseBroker();
var outputDescription = broker.TestService(dbService);
if (outputDescription?.DataSourceShapes == null || outputDescription.DataSourceShapes.Count == 0)
{
throw new Exception(ErrorResource.ErrorRetrievingShapeFromServiceOutput);
}
dbService.Recordset.Fields.Clear();
ServiceMappingHelper smh = new ServiceMappingHelper();
smh.MapDbOutputs(outputDescription, ref dbService, addFields);
dbService.Recordset.Name = @"Unnamed";
return(dbService.Recordset);
}
default: return(null);
}
}
return(null);
// Clear out the Recordset.Fields list because the sequence and
// number of fields may have changed since the last invocation.
//
// Create a copy of the Recordset.Fields list before clearing it
// so that we don't lose the user-defined aliases.
//
}
/// <summary>
/// Ensures that each Metric that has EnableAnalytics has a SQL View for it, and also deletes any AnalyticsFactMetric** views that no longer have metric (based on Metric.Name)
/// </summary>
public void EnsureMetricAnalyticsViews()
{
string analyticMetricViewsPrefix = "AnalyticsFactMetric";
string getAnalyticMetricViewsSQL = string.Format(
@"SELECT
OBJECT_NAME(sm.object_id) [view_name]
,sm.DEFINITION [view_definition]
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.type = 'V'
AND OBJECT_NAME(sm.object_id) LIKE '{0}%'",
analyticMetricViewsPrefix);
var dataTable = DbService.GetDataTable(getAnalyticMetricViewsSQL, System.Data.CommandType.Text, null);
var databaseAnalyticMetricViews = dataTable.Rows.OfType <DataRow>()
.Select(row => new
{
ViewName = row["view_name"] as string,
ViewDefinition = row["view_definition"] as string
}).ToList();
var metricsWithAnalyticsEnabled = this.Queryable().Where(a => a.EnableAnalytics).Include(a => a.MetricPartitions).AsNoTracking().ToList();
var metricViewNames = metricsWithAnalyticsEnabled.Select(a => $"{analyticMetricViewsPrefix}{a.Title.RemoveSpecialCharacters().RemoveSpaces() }").ToList();
var orphanedDatabaseViews = databaseAnalyticMetricViews.Where(a => !metricViewNames.Contains(a.ViewName)).ToList();
// DROP any Metric Analytic Views that are orphaned. In other words, there are views named 'AnalyticsFactMetric***' that don't have a metric.
// This could happen if Metric.EnableAnalytics changed from True to False, Metric Title changed, or if a Metric was deleted.
foreach (var orphanedView in orphanedDatabaseViews)
{
this.Context.Database.ExecuteSqlCommand($"DROP VIEW [{orphanedView.ViewName}]");
}
// Make sure that each Metric with EnableAnalytics=True has a SQL View and that the View Definition is correct
foreach (var metric in metricsWithAnalyticsEnabled)
{
string metricViewName = $"{analyticMetricViewsPrefix}{metric.Title.RemoveSpecialCharacters().RemoveSpaces() }";
var metricEntityPartitions = metric.MetricPartitions.Where(a => a.EntityTypeId.HasValue).OrderBy(a => a.Order).ThenBy(a => a.Label).Select(a => new
{
a.Label,
a.EntityTypeId
});
var viewPartitionSELECTClauses = metricEntityPartitions.Select(a => $" ,pvt.[{a.EntityTypeId}] as [{a.Label.RemoveSpecialCharacters().RemoveSpaces()}Id]").ToList().AsDelimited("\n");
List <string> partitionEntityLookupSELECTs = new List <string>();
List <string> partitionEntityLookupJOINs = new List <string>();
foreach (var metricPartition in metricEntityPartitions)
{
var metricPartitionEntityType = EntityTypeCache.Get(metricPartition.EntityTypeId.Value);
if (metricPartitionEntityType != null)
{
var tableAttribute = metricPartitionEntityType.GetEntityType().GetCustomAttribute <TableAttribute>();
if (tableAttribute != null)
{
if (metricPartitionEntityType.Id == EntityTypeCache.GetId <DefinedValue>())
{
partitionEntityLookupSELECTs.Add($"j{metricPartition.EntityTypeId}.Value [{metricPartition.Label.RemoveSpecialCharacters().RemoveSpaces()}Name]");
}
else if (metricPartitionEntityType.GetEntityType().GetProperty("Name") != null)
{
partitionEntityLookupSELECTs.Add($"j{metricPartition.EntityTypeId}.Name [{metricPartition.Label.RemoveSpecialCharacters().RemoveSpaces()}Name]");
}
partitionEntityLookupJOINs.Add($"LEFT JOIN [{tableAttribute.Name}] j{metricPartition.EntityTypeId} ON p.{metricPartition.Label.RemoveSpecialCharacters().RemoveSpaces()}Id = j{metricPartition.EntityTypeId}.Id");
}
}
}
var viewPIVOTInClauses = metricEntityPartitions.Select(a => $" [{a.EntityTypeId}]").ToList().AsDelimited(",\n");
if (string.IsNullOrEmpty(viewPIVOTInClauses))
{
// This metric only has the default partition, and with no EntityTypeId, so put in a dummy Pivot Clause
viewPIVOTInClauses = "[0]";
}
var viewJoinsSELECT = partitionEntityLookupSELECTs.Select(a => $" ,{a}").ToList().AsDelimited("\n");
var viewJoinsFROM = partitionEntityLookupJOINs.AsDelimited("\n");
var viewDefinition = $@"
/*
<auto-generated>
This view was generated by the Rock's MetricService.EnsureMetricAnalyticsViews() which gets called when a Metric is saved.
Changes to this view definition will be lost when the view is regenerated.
NOTE: Any Views with the prefix '{analyticMetricViewsPrefix}' are assumed to be Code Generated and may be deleted if there isn't a Metric associated with it
</auto-generated>
<doc>
<summary>
This VIEW helps present the data for the {metric.Title} metric.
</summary>
</doc>
*/
CREATE VIEW [{metricViewName}] AS
SELECT p.*
{viewJoinsSELECT}
FROM (
SELECT pvt.Id
,cast(pvt.MetricValueDateTime AS DATE) AS [MetricValueDateTime]
,pvt.YValue
{viewPartitionSELECTClauses}
FROM (
SELECT
mv.Id
,mv.YValue
,mv.MetricValueDateTime
,mvp.EntityId
,mp.EntityTypeId
FROM MetricValue mv
JOIN MetricValuePartition mvp ON mvp.MetricValueId = mv.Id
JOIN MetricPartition mp ON mvp.MetricPartitionId = mp.Id
WHERE mv.MetricId = {metric.Id}
) src
pivot(min(EntityId) FOR EntityTypeId IN ({viewPIVOTInClauses})) pvt
) p
{viewJoinsFROM}
";
var databaseViewDefinition = databaseAnalyticMetricViews.Where(a => a.ViewName == metricViewName).FirstOrDefault();
try
{
if (databaseViewDefinition != null)
{
if (databaseViewDefinition.ViewDefinition != viewDefinition)
{
// view already exists, but something has changed, so drop and recreate it
this.Context.Database.ExecuteSqlCommand($"DROP VIEW [{metricViewName}]");
this.Context.Database.ExecuteSqlCommand(viewDefinition);
}
}
else
{
this.Context.Database.ExecuteSqlCommand(viewDefinition);
}
}
catch (Exception ex)
{
// silently log the exception
ExceptionLogService.LogException(new Exception("Error creating Analytics view for " + metric.Title, ex), System.Web.HttpContext.Current);
}
}
}
public FlowerShopCommands(DbService db, ICurrencyService cs, DiscordSocketClient client)
{
_db = db;
_cs = cs;
_client = client;
}
private DbService CreateTestDb()
{
const string filename = "UnitTestDb.dbs";
if ( File.Exists( filename ) )
{
File.Delete( filename );
}
var db = new DbService();
db.Open(filename);
// Add data
for (var i = 0; i < 10; i++)
{
db.AddResultTime(new Result
{
Position = i + 1,
Time = new TimeSpan(0, i + 1, 0),
RaceNumber = i + 1,
WmaScore = i + 1
});
}
return db;
}
public LogCommandService(DiscordSocketClient client, NadekoStrings strings,
NadekoBot bot, DbService db, MuteService mute, ProtectionService prot, GuildTimezoneService tz)
{
_client = client;
_log = LogManager.GetCurrentClassLogger();
_strings = strings;
_db = db;
_mute = mute;
_prot = prot;
_tz = tz;
GuildLogSettings = bot.AllGuildConfigs
.ToDictionary(g => g.GuildId, g => g.LogSetting)
.ToConcurrent();
_timerReference = new Timer(async(state) =>
{
try
{
var keys = PresenceUpdates.Keys.ToList();
await Task.WhenAll(keys.Select(key =>
{
if (PresenceUpdates.TryRemove(key, out var msgs))
{
var title = GetText(key.Guild, "presence_updates");
var desc = string.Join(Environment.NewLine, msgs);
return(key.SendConfirmAsync(title, desc.TrimTo(2048)));
}
return(Task.CompletedTask);
}));
}
catch (Exception ex)
{
_log.Warn(ex);
}
}, null, TimeSpan.FromSeconds(15), TimeSpan.FromSeconds(15));
//_client.MessageReceived += _client_MessageReceived;
_client.MessageUpdated += _client_MessageUpdated;
_client.MessageDeleted += _client_MessageDeleted;
_client.UserBanned += _client_UserBanned;
_client.UserUnbanned += _client_UserUnbanned;
_client.UserJoined += _client_UserJoined;
_client.UserLeft += _client_UserLeft;
//_client.UserPresenceUpdated += _client_UserPresenceUpdated;
_client.UserVoiceStateUpdated += _client_UserVoiceStateUpdated;
_client.UserVoiceStateUpdated += _client_UserVoiceStateUpdated_TTS;
_client.GuildMemberUpdated += _client_GuildUserUpdated;
#if !GLOBAL_NADEKO
_client.UserUpdated += _client_UserUpdated;
#endif
_client.ChannelCreated += _client_ChannelCreated;
_client.ChannelDestroyed += _client_ChannelDestroyed;
_client.ChannelUpdated += _client_ChannelUpdated;
_mute.UserMuted += MuteCommands_UserMuted;
_mute.UserUnmuted += MuteCommands_UserUnmuted;
_prot.OnAntiProtectionTriggered += TriggeredAntiProtection;
}
