/// <summary>
/// Executes the <see cref="ChangeNoticationService"/>, processes all
/// </summary>
/// <returns>
/// An awaitable <see cref="Task"/>
/// </returns>
public async Task Execute()
{
var sw = Stopwatch.StartNew();
var connection = new NpgsqlConnection(Services.Utils.GetConnectionString(AppConfig.Current.Backtier.Database));
// ensure an open connection
if (connection.State != ConnectionState.Open)
{
try
{
connection.Open();
var transaction = connection.BeginTransaction();
var personDao = this.container.Resolve <IPersonDao>();
var persons = personDao.Read(transaction, "SiteDirectory", null, true).ToList();
foreach (var person in persons)
{
var changelogBodyComposer = this.container.Resolve <IChangelogBodyComposer>();
if (!person.IsActive)
{
continue;
}
var emailAddresses = this.GetEmailAdressess(transaction, person).ToList();
if (!emailAddresses.Any())
{
continue;
}
var changeNotificationSubscriptionUserPreferences = this.GetChangeLogSubscriptionUserPreferences(transaction, person);
var endDateTime = this.GetEndDateTime(DayOfWeek.Monday);
var startDateTime = endDateTime.AddDays(-7);
var htmlStringBuilder = new StringBuilder();
var textStringBuilder = new StringBuilder();
var subject = $"Weekly Changelog from server '{AppConfig.Current.Midtier.HostName}'";
htmlStringBuilder.AppendLine($"<h3>{subject}<br />{startDateTime:R} - {endDateTime:R}</h3>");
textStringBuilder.AppendLine($"{subject}\n{startDateTime:R} - {endDateTime:R}");
foreach (var changeNotificationSubscriptionUserPreference in changeNotificationSubscriptionUserPreferences)
{
if (changeNotificationSubscriptionUserPreference.Value.ChangeNotificationSubscriptions.Any() &&
changeNotificationSubscriptionUserPreference.Value.ChangeNotificationReportType != ChangeNotificationReportType.None)
{
var changelogSections = changelogBodyComposer.CreateChangelogSections(
transaction,
this.container,
Guid.Parse(changeNotificationSubscriptionUserPreference.Key),
person,
changeNotificationSubscriptionUserPreference.Value,
startDateTime,
endDateTime
).ToList();
htmlStringBuilder.Append(changelogBodyComposer.CreateHtmlBody(changelogSections));
textStringBuilder.Append(changelogBodyComposer.CreateTextBody(changelogSections));
}
}
var emailService = this.container.Resolve <IEmailService>();
await emailService.Send(emailAddresses, subject, textStringBuilder.ToString(), htmlStringBuilder.ToString());
}
}
catch (PostgresException postgresException)
{
Logger.Error("Could not connect to the database to process Change Notifications. Error message: {0}", postgresException.Message);
}
catch (Exception ex)
{
Logger.Error(ex);
}
finally
{
if (connection?.State == ConnectionState.Open)
{
await connection.CloseAsync();
}
Logger.Info($"ChangeNotifications processed in {sw.ElapsedMilliseconds} [ms]");
}
}
}
public async Task <ActionResult <Models.Person> > AddPerson([FromBody] Models.Person person)
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandInsertPerson;
try
{
Models.Person returnValue = new Models.Person();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("INSERT INTO person ");
sqlStatement.Append(" (last_name, first_name, middle_name, generation_suffix, preferred_first_name, date_of_birth, ");
sqlStatement.Append(" record_added_date_time, record_last_updated_date_time) ");
sqlStatement.Append(" VALUES (@last_name, @first_name, @middle_name, @generation_suffix, @preferred_first_name, @date_of_birth, ");
sqlStatement.Append(" @record_added_date_time, @record_last_updated_date_time) RETURNING person_id ");
sqlCommandInsertPerson = sqlConnection.CreateCommand();
sqlCommandInsertPerson.CommandText = sqlStatement.ToString();
sqlCommandInsertPerson.CommandTimeout = 600;
sqlCommandInsertPerson.Parameters.Add(new NpgsqlParameter("@last_name", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandInsertPerson.Parameters.Add(new NpgsqlParameter("@first_name", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandInsertPerson.Parameters.Add(new NpgsqlParameter("@middle_name", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandInsertPerson.Parameters.Add(new NpgsqlParameter("@generation_suffix", NpgsqlTypes.NpgsqlDbType.Varchar, 10));
sqlCommandInsertPerson.Parameters.Add(new NpgsqlParameter("@preferred_first_name", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandInsertPerson.Parameters.Add(new NpgsqlParameter("@date_of_birth", NpgsqlTypes.NpgsqlDbType.Date));
sqlCommandInsertPerson.Parameters.Add(new NpgsqlParameter("@record_added_date_time", NpgsqlTypes.NpgsqlDbType.Timestamp));
sqlCommandInsertPerson.Parameters.Add(new NpgsqlParameter("@record_last_updated_date_time", NpgsqlTypes.NpgsqlDbType.Timestamp));
sqlCommandInsertPerson.Parameters["@last_name"].Value = "";
sqlCommandInsertPerson.Parameters["@first_name"].Value = "";
sqlCommandInsertPerson.Parameters["@middle_name"].Value = "";
sqlCommandInsertPerson.Parameters["@generation_suffix"].Value = "";
sqlCommandInsertPerson.Parameters["@preferred_first_name"].Value = "";
sqlCommandInsertPerson.Parameters["@date_of_birth"].Value = DateTime.MinValue;
sqlCommandInsertPerson.Parameters["@record_added_date_time"].Value = DateTime.MinValue;
sqlCommandInsertPerson.Parameters["@record_last_updated_date_time"].Value = DateTime.MinValue;
await sqlCommandInsertPerson.PrepareAsync();
sqlCommandInsertPerson.Parameters["@last_name"].Value = person.lastName;
sqlCommandInsertPerson.Parameters["@first_name"].Value = person.firstName;
if (person.middleName == null)
{
sqlCommandInsertPerson.Parameters["@middle_name"].Value = System.DBNull.Value;
}
else
{
sqlCommandInsertPerson.Parameters["@middle_name"].Value = person.middleName;
}
if (person.generationSuffix == null)
{
sqlCommandInsertPerson.Parameters["@generation_suffix"].Value = System.DBNull.Value;
}
else
{
sqlCommandInsertPerson.Parameters["@generation_suffix"].Value = person.generationSuffix;
}
sqlCommandInsertPerson.Parameters["@preferred_first_name"].Value = person.preferredFirstName;
if (person.dateOfBirth == DateTime.MinValue)
{
sqlCommandInsertPerson.Parameters["@date_of_birth"].Value = System.DBNull.Value;
}
else
{
sqlCommandInsertPerson.Parameters["@date_of_birth"].Value = person.dateOfBirth;
}
sqlCommandInsertPerson.Parameters["@record_added_date_time"].Value = DateTime.MinValue;
sqlCommandInsertPerson.Parameters["@record_last_updated_date_time"].Value = DateTime.MinValue;
using (var sqlDataReader = await sqlCommandInsertPerson.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
{
await sqlDataReader.ReadAsync();
returnValue.personId = sqlDataReader.GetInt32(0);
returnValue.lastName = person.lastName;
returnValue.firstName = person.firstName;
returnValue.middleName = person.middleName;
returnValue.generationSuffix = person.generationSuffix;
returnValue.preferredFirstName = person.preferredFirstName;
returnValue.dateOfBirth = person.dateOfBirth;
await sqlDataReader.CloseAsync();
};
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in PersonWSController::AddPerson(). Message is {0}", ex1.Message));
if (ex1.InnerException != null)
{
logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message));
if (ex1.InnerException.InnerException != null)
{
logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message));
}
}
logger.LogError(string.Format("{0}", ex1.StackTrace));
return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message));
}
} // AddPerson()
public async Task <int> UpdateUser(User user)
{
Thread.Sleep(1000);
Log.Information($"Updating {user.UserNameLastFM}");
if (UserUpdateFailures.Contains(user.UserNameLastFM))
{
Log.Information($"Skipped user {user.UserNameLastFM} in updating process");
return(0);
}
var lastPlay = await GetLastStoredPlay(user);
var recentTracks = await this._lastFMClient.User.GetRecentScrobbles(
user.UserNameLastFM,
count : 1000,
from : lastPlay?.TimePlayed ?? DateTime.UtcNow.AddDays(-14));
Statistics.LastfmApiCalls.Inc();
if (!recentTracks.Success || !recentTracks.Content.Any())
{
Log.Information($"Something went wrong getting recent tracks for {user.UserNameLastFM} | {recentTracks.Status}");
if (recentTracks.Success)
{
return(0);
}
Log.Information($"Added {user.UserNameLastFM} to update failure list");
UserUpdateFailures.Add(user.UserNameLastFM);
return(0);
}
var newScrobbles = recentTracks.Content
.Where(w => w.TimePlayed.HasValue && w.TimePlayed.Value.DateTime > user.LastScrobbleUpdate)
.ToList();
await using var connection = new NpgsqlConnection(this._connectionString);
await connection.OpenAsync();
await UpdatePlaysForUser(user, recentTracks, connection);
if (!newScrobbles.Any())
{
Log.Information($"No new scrobbles for {user.UserNameLastFM}");
await SetUserUpdateTime(user.UserId, DateTime.UtcNow, connection);
return(0);
}
var cachedArtistAliases = await GetCachedArtistAliases();
await UpdateArtistsForUser(user, newScrobbles, cachedArtistAliases, connection);
await UpdateAlbumsForUser(user, newScrobbles, cachedArtistAliases, connection);
await UpdateTracksForUser(user, newScrobbles, cachedArtistAliases, connection);
var latestScrobbleDate = GetLatestScrobbleDate(newScrobbles);
await SetUserUpdateAndScrobbleTime(user.UserId, DateTime.UtcNow, latestScrobbleDate, connection);
await connection.CloseAsync();
return(newScrobbles.Count);
}
public static async Task CloseConnection()
{
await connection.CloseAsync();
}
private async Task PrepConnectionPoolAsync(CancellationToken cancellationToken)
{
using (NpgsqlConnection conn = await OpenConnectionAsync(cancellationToken))
await conn.CloseAsync();
}
private async Task <byte[]> GetCacheItemAsync(string key, bool includeValue, CancellationToken cancellationToken)
{
var utcNow = SystemClock.UtcNow;
byte[] value = null;
using (var connection = new NpgsqlConnection(ConnectionString))
{
connection.Notice += LogNotice;
var command = new NpgsqlCommand($"{SchemaName}.{Functions.Names.UpdateCacheItemFormat}", connection)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters
.AddParamWithValue("SchemaName", NpgsqlTypes.NpgsqlDbType.Text, SchemaName)
.AddParamWithValue("TableName", NpgsqlTypes.NpgsqlDbType.Text, TableName)
.AddCacheItemId(key)
.AddWithValue("UtcNow", NpgsqlTypes.NpgsqlDbType.TimestampTz, utcNow);
await connection.OpenAsync(cancellationToken);
await command.ExecuteNonQueryAsync(cancellationToken);
if (includeValue)
{
command = new NpgsqlCommand($"{SchemaName}.{Functions.Names.GetCacheItemFormat}", connection)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters
.AddParamWithValue("SchemaName", NpgsqlTypes.NpgsqlDbType.Text, SchemaName)
.AddParamWithValue("TableName", NpgsqlTypes.NpgsqlDbType.Text, TableName)
.AddCacheItemId(key)
.AddWithValue("UtcNow", NpgsqlTypes.NpgsqlDbType.TimestampTz, utcNow);
var reader = await command.ExecuteReaderAsync(
CommandBehavior.SequentialAccess | CommandBehavior.SingleRow | CommandBehavior.SingleResult,
cancellationToken);
if (await reader.ReadAsync(cancellationToken))
{
_ = await reader.GetFieldValueAsync <string>(Columns.Indexes.CacheItemIdIndex, cancellationToken);
value = await reader.GetFieldValueAsync <byte[]>(Columns.Indexes.CacheItemValueIndex, cancellationToken);
_ = await reader.GetFieldValueAsync <DateTimeOffset>(Columns.Indexes.ExpiresAtTimeIndex, cancellationToken);
if (!await reader.IsDBNullAsync(Columns.Indexes.SlidingExpirationInSecondsIndex, cancellationToken))
{
_ = await reader.GetFieldValueAsync <long>(Columns.Indexes.SlidingExpirationInSecondsIndex, cancellationToken);
}
if (!await reader.IsDBNullAsync(Columns.Indexes.AbsoluteExpirationIndex, cancellationToken))
{
_ = await reader.GetFieldValueAsync <DateTimeOffset>(Columns.Indexes.AbsoluteExpirationIndex, cancellationToken);
}
}
}
await connection.CloseAsync();
connection.Notice -= LogNotice;
}
return(value);
}
public async Task PopulateAsync(DbSchema[] schemas)
{
Dictionary <Type, NpgsqlTypes.NpgsqlDbType> typeDict = new Dictionary <Type, NpgsqlTypes.NpgsqlDbType>();
typeDict.Add(typeof(int), NpgsqlTypes.NpgsqlDbType.Integer);
typeDict.Add(typeof(double), NpgsqlTypes.NpgsqlDbType.Double);
typeDict.Add(typeof(decimal), NpgsqlTypes.NpgsqlDbType.Numeric);
typeDict.Add(typeof(string), NpgsqlTypes.NpgsqlDbType.Varchar);
typeDict.Add(typeof(DateTime), NpgsqlTypes.NpgsqlDbType.Date);
typeDict.Add(typeof(char[]), NpgsqlTypes.NpgsqlDbType.Varchar);
typeDict.Add(typeof(Guid), NpgsqlTypes.NpgsqlDbType.Uuid);
using (var conn = new NpgsqlConnection(Connection.ConnectionString))
{
await conn.OpenAsync();
this.Logger.Information($"Opened connection to {conn.Database} on {conn.DataSource}");
try
{
foreach (var schema in schemas)
{
var cleanCmd = new NpgsqlCommand($"TRUNCATE TABLE \"{this.Connection.DbSchema}\".\"{schema.TableName}\"", conn);
await cleanCmd.ExecuteNonQueryAsync();
this.Logger.Information($"table {schema.TableName} truncated");
string sql = $"COPY \"{this.Connection.DbSchema}\".\"{schema.TableName}\" ( ";
foreach (System.Data.DataColumn col in schema.Records.Columns)
{
sql += ($"\"{col.ColumnName}\",");
}
sql = sql.TrimEnd(',') + ") FROM STDIN (FORMAT BINARY)";
int nRows = schema.Records.Rows.Count;
using (var BulkWrite = conn.BeginBinaryImport(sql))
{
for (int idRow = 0; idRow < nRows; idRow++)
{
BulkWrite.StartRow();
foreach (System.Data.DataColumn col in schema.Records.Columns)
{
if (schema.Records.Rows[idRow].IsNull(col))
{
BulkWrite.WriteNull();
}
else
{
if (col.DataType == typeof(string) && string.IsNullOrEmpty(schema.Records.Rows[idRow].Field <string>(col)))
{
BulkWrite.WriteNull();
}
else
{
BulkWrite.Write(schema.Records.Rows[idRow][col.Ordinal], typeDict[col.DataType]);
}
}
}
}
BulkWrite.Complete();
this.Logger.Information($"{schema.Records.Rows.Count} records have been imported into {schema.TableName}");
}
}
}
catch (Exception ex)
{
this.Logger.Error(ex, $"An error occured while doing operations. Rolling back all changes to {conn.Database} on {conn.DataSource}");
throw;
}
await conn.CloseAsync();
}
}
private static async Task CreateMarkupFile(string connectionString)
{
var content = new StringBuilder();
var header = new StringBuilder();
var settings = Settings.Value;
var schemas = settings.Schemas.Distinct().ToArray();
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
var file = Settings.FileName(connection);
header.AppendLine($"# Dictionary for database `{connection.Database}`");
header.AppendLine();
header.AppendLine(
$"- Server: PostgreSQL `{connection.Host}:{connection.Port}`, version `{connection.ServerVersion}`");
header.AppendLine($"- Local timestamp: `{DateTime.Now:o}`");
header.AppendLine($"- Schemas: {string.Join(", ", schemas.Select(s => $"`{s}`"))}");
header.AppendLine();
header.AppendLine("## Table of Contents");
header.AppendLine();
var tablesHeader = false;
var writeToc = false;
var anyTables = false;
var anyViews = false;
foreach (var schema in schemas)
{
await foreach (var result in connection.GetTables(schema, settings.SkipPattern).ValuesAsync)
{
if (!tablesHeader)
{
//content.AppendLine();
content.AppendLine("## Tables");
tablesHeader = true;
}
if (result.column == null)
{
Program.Dump("Writing table", result.table);
content.AppendLine();
if (writeToc)
{
content.AppendLine("<a href=\"#table-of-contents\" title=\"Table of Contents\">⇡</a>");
content.AppendLine();
}
else
{
writeToc = true;
}
content.AppendLine($"### Table `{schema}.{result.table}`");
header.AppendLine($"- Table [`{schema}.{result.table}`](#table-{schema.ToLower()}{result.table.ToLower()})");
content.AppendLine();
content.AppendLine(Settings.StartTag("table", $"{schema}.\"{result.table}\""));
if (result.comment != null)
{
content.AppendLine(result.comment);
}
if (!anyTables)
{
anyTables = true;
}
content.AppendLine(Settings.EndTag);
content.AppendLine();
content.AppendLine("| Column | | Type | Nullable | Default | Comment |");
content.AppendLine("| ------ | ----------- | -----| -------- | ------- | ------- |");
}
else
{
var name = $"{schema.ToLower()}-{result.table.ToLower()}-{result.column.ToLower()}";
content.AppendLine(
$"| {Settings.Hashtag(name)}`{result.column}` " +
$"| {result.constraintMarkup} " +
$"| `{result.columnType}` " +
$"| {result.nullable} " +
$"| {result.defaultMarkup} " +
$"| {Settings.StartTag("column", $"{schema}.\"{result.table}\".\"{result.column}\"")}{result.comment}{Settings.EndTag} |");
}
}
}
if (anyTables)
{
content.AppendLine();
content.AppendLine("<a href=\"#table-of-contents\" title=\"Table of Contents\">⇡</a>");
}
foreach (var schema in schemas)
{
if (!settings.IncludeViews)
{
break;
}
var viewsHeader = false;
await foreach (var result in connection.GetTables(schema, settings.SkipPattern, "VIEW").ValuesAsync)
{
if (!viewsHeader)
{
content.AppendLine();
content.AppendLine("## Views");
viewsHeader = true;
}
if (result.column == null)
{
Program.Dump("Writing view", result.table);
content.AppendLine();
if (writeToc)
{
content.AppendLine("<a href=\"#table-of-contents\" title=\"Table of Contents\">⇡</a>");
content.AppendLine();
}
else
{
writeToc = true;
}
if (!anyViews)
{
anyViews = true;
}
content.AppendLine($"### View `{schema}.{result.table}`");
header.AppendLine($"- View [`{schema}.{result.table}`](#view-{schema.ToLower()}{result.table.ToLower()})");
content.AppendLine();
content.AppendLine(Settings.StartTag("view", $"{schema}.\"{result.table}\""));
if (result.comment != null)
{
content.AppendLine(result.comment);
}
content.AppendLine(Settings.EndTag);
content.AppendLine();
content.AppendLine("| Column | Type | Comment |");
content.AppendLine("| ------ | ---- | --------|");
}
else
{
content.AppendLine(
$"| `{result.column}` " +
$"| `{result.columnType}` " +
$"| {Settings.StartTag("column", $"{schema}.\"{result.table}\".\"{result.column}\"")}{result.comment}{Settings.EndTag} |");
}
}
}
if (anyViews)
{
content.AppendLine();
content.AppendLine("<a href=\"#table-of-contents\" title=\"Table of Contents\">⇡</a>");
}
foreach (var schema in schemas)
{
if (!settings.IncludeRoutines)
{
break;
}
var routinesHeader = false;
await foreach (var result in connection.GetRoutines(schema, settings.SkipPattern).ValuesAsync)
{
if (!routinesHeader)
{
content.AppendLine();
content.AppendLine("## Routines");
routinesHeader = true;
}
Program.Dump("Writing routine", result.name);
content.AppendLine();
content.AppendLine(
$"### {result.type.First().ToString().ToUpper()}{result.type.Substring(1)} `{schema}.{result.signature}`");
var routineAnchor = result.signature.ToLower().Replace("(", "").Replace(")", "").Replace(",", "").Replace(" ", "-");
header.AppendLine($"- {result.type.First().ToString().ToUpper()}{result.type.Substring(1)} [`{schema}.{result.signature}`](#{result.type.ToLower()}-{schema.ToLower()}{routineAnchor})");
content.AppendLine();
content.AppendLine($"- Returns `{result.returns}`");
content.AppendLine();
content.AppendLine($"- Language is `{result.language}`");
content.AppendLine();
content.AppendLine(Settings.StartTag(result.type, $"{schema}.{result.signature.Replace(result.name, $"\"{result.name}\"")}"));
if (result.comment != null)
{
content.AppendLine(result.comment);
}
content.AppendLine(Settings.EndTag);
if (writeToc)
{
content.AppendLine();
content.AppendLine("<a href=\"#table-of-contents\" title=\"Table of Contents\">⇡</a>");
}
else
{
writeToc = true;
}
}
}
await connection.CloseAsync();
Program.Dump("Creating file", file);
await File.WriteAllTextAsync(file, content.ToString());
await using (var fileStream = new StreamWriter(file))
{
await fileStream.WriteLineAsync(header.ToString());
await fileStream.WriteLineAsync(content.ToString());
}
Console.WriteLine("Done!");
}
static async Task Main(string[] args)
{
string dcs = "";
if (args.Length > 0 && args.Length < 5)
{
Console.WriteLine("Usage: PGSqlUpdater.exe [dbhost] [dbport] [dbname] [dbuser] [dbpassword]");
Environment.Exit(1);
}
else if (args.Length == 5)
{
dcs = "Host=" + args[0] + ";Port=" + args[1] + ";Database=" + args[2] + ";Username="******";Password="******"dbhost.conf");
if (!File.Exists(conf))
{
Console.WriteLine("No configuration file found, please use commmand line arguments or create config file with database connection string, as dbhost.conf");
Environment.Exit(1);
}
dcs = File.ReadAllText(conf);
}
if (String.IsNullOrEmpty(dcs))
{
Console.WriteLine("Can't connect to database, with an empty connection string!");
Environment.Exit(2);
}
Console.WriteLine("PGSqlUpdater v0.1");
Console.WriteLine("Loading archive...");
XmlDocument archive = new XmlDocument();
archive.Load(Path.Combine(Directory.GetParent(Directory.GetParent(Directory.GetCurrentDirectory()).FullName).FullName, "GalnetArchive.xml"));
XmlNode root = archive.DocumentElement;
XmlNode last = root.LastChild;
XmlNode lastDate = last.SelectSingleNode("date");
DateTime lastUpdated = Convert.ToDateTime(lastDate.InnerText);
Console.WriteLine("Last archived article dated: " + lastUpdated.ToString("dd MMM yyyy"));
try
{
string lastStored;
Console.WriteLine("Trying database...");
await using var dbh = new NpgsqlConnection(dcs);
await dbh.OpenAsync();
await using (var query = new NpgsqlCommand("select max(_date) from articles", dbh))
await using (var rdr = await query.ExecuteReaderAsync())
{
await rdr.ReadAsync();
lastStored = rdr[0].ToString();
}
await dbh.CloseAsync();
PGSqlUpdater updater = new PGSqlUpdater();
if (String.IsNullOrEmpty(lastStored))
{
Console.WriteLine("Database is empty, archiving all articles...");
await updater.StoreAllArticlesAsync(dcs, archive);
}
else
{
Console.WriteLine("Updating database...");
await updater.IncrementalUpdateAsync(dcs, lastStored, archive);
Console.Read();
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
Console.Read();
}
catch (Exception e)
{
Console.WriteLine(e);
Console.Read();
}
}
/// <summary>
/// добавляет в базу новый отчёт, либо обновляет уже имеющийся
/// </summary>
/// <param name="address">IP - адрес</param>
/// <param name="ramInfo">информация об ОЗУ</param>
/// <param name="cpuInfo">информация о ЦП</param>
/// <param name="disksInfo">информация о дисках</param>
public async Task <bool> StoreSystemReport(string address, RAMInfo ramInfo, CPUInfo cpuInfo, DisksInfo disksInfo)
{
var result = false;
if (string.IsNullOrEmpty(address) || ramInfo == null || cpuInfo == null || disksInfo == null)
{
return(false);
}
var id = 0;
using (NpgsqlConnection conn = GetConnection(_conn))
{
using (var cmd = new NpgsqlCommand(GetIdQuery(), conn))
{
cmd.Parameters.AddWithValue("p_address", address);
try // ищем id
{
await conn.OpenAsync();
var searchResult = await cmd.ExecuteReaderAsync();
if (searchResult.HasRows)
{
await searchResult.ReadAsync();
id = (int)searchResult[0];
}
await conn.CloseAsync();
}
catch (Exception e)
{
return(false);
}
cmd.Parameters.AddWithValue("p_total_ram", ramInfo.TotalMb);
cmd.Parameters.AddWithValue("p_free_ram", ramInfo.FreeMb);
cmd.Parameters.AddWithValue("p_cpu_load", cpuInfo.UsedPercent);
cmd.Parameters.AddWithValue("p_free_disks", disksInfo.FreeMb);
cmd.Parameters.AddWithValue("p_total_disks", disksInfo.TotalMb);
try
{
if (id > 0) // если уже есть в базе, то обновляем
{
cmd.Parameters.AddWithValue("p_id", id);
cmd.CommandText = GetUpdateSystemReportQuery();
await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
else // иначе добавляем в бд
{
cmd.CommandText = GetInsertSystemReportQuery();
await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
result = true;
}
catch (Exception e)
{
result = false;
}
}
await conn.CloseAsync();
}
return(result);
}
public async Task <List <Post> > GetAllPOs()
{
var result = new List <Post>();
await conn.OpenAsync();
string command = "SELECT * FROM all_kraji();";
using (var com = new NpgsqlCommand(command, conn))
{
var r = com.ExecuteReader();
while (await r.ReadAsync())
{
result.Add(new Post(r));
}
}
await conn.CloseAsync();
return(result);
}
private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e)
{
connection.CloseAsync();
}
public async Task <ActionResult <Models.APICallResult> > UpdatePerson([FromBody] Models.Person person)
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandUpdatePerson;
try
{
Models.APICallResult returnValue = new Models.APICallResult();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("UPDATE person ");
sqlStatement.Append(" SET last_name = @last_name, first_name = @first_name, middle_name = @middle_name, generation_suffix = @generation_suffix, ");
sqlStatement.Append(" preferred_first_name = @preferred_first_name, date_of_birth = @date_of_birth, ");
sqlStatement.Append(" record_last_updated_date_time = @record_last_updated_date_time ");
sqlStatement.Append(" WHERE person_id = @person_id ");
sqlCommandUpdatePerson = sqlConnection.CreateCommand();
sqlCommandUpdatePerson.CommandText = sqlStatement.ToString();
sqlCommandUpdatePerson.CommandTimeout = 600;
sqlCommandUpdatePerson.Parameters.Add(new NpgsqlParameter("@last_name", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandUpdatePerson.Parameters.Add(new NpgsqlParameter("@first_name", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandUpdatePerson.Parameters.Add(new NpgsqlParameter("@middle_name", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandUpdatePerson.Parameters.Add(new NpgsqlParameter("@generation_suffix", NpgsqlTypes.NpgsqlDbType.Varchar, 10));
sqlCommandUpdatePerson.Parameters.Add(new NpgsqlParameter("@preferred_first_name", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandUpdatePerson.Parameters.Add(new NpgsqlParameter("@date_of_birth", NpgsqlTypes.NpgsqlDbType.Date));
sqlCommandUpdatePerson.Parameters.Add(new NpgsqlParameter("@record_last_updated_date_time", NpgsqlTypes.NpgsqlDbType.Timestamp));
sqlCommandUpdatePerson.Parameters.Add(new NpgsqlParameter("@person_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandUpdatePerson.Parameters["@last_name"].Value = "";
sqlCommandUpdatePerson.Parameters["@first_name"].Value = "";
sqlCommandUpdatePerson.Parameters["@middle_name"].Value = "";
sqlCommandUpdatePerson.Parameters["@generation_suffix"].Value = "";
sqlCommandUpdatePerson.Parameters["@preferred_first_name"].Value = "";
sqlCommandUpdatePerson.Parameters["@date_of_birth"].Value = DateTime.MinValue;
sqlCommandUpdatePerson.Parameters["@record_last_updated_date_time"].Value = DateTime.MinValue;
sqlCommandUpdatePerson.Parameters["@person_id"].Value = 0;
await sqlCommandUpdatePerson.PrepareAsync();
sqlCommandUpdatePerson.Parameters["@last_name"].Value = person.lastName;
sqlCommandUpdatePerson.Parameters["@first_name"].Value = person.firstName;
if (person.middleName == null)
{
sqlCommandUpdatePerson.Parameters["@middle_name"].Value = System.DBNull.Value;
}
else
{
sqlCommandUpdatePerson.Parameters["@middle_name"].Value = person.middleName;
}
if (person.generationSuffix == null)
{
sqlCommandUpdatePerson.Parameters["@generation_suffix"].Value = System.DBNull.Value;
}
else
{
sqlCommandUpdatePerson.Parameters["@generation_suffix"].Value = person.generationSuffix;
}
sqlCommandUpdatePerson.Parameters["@preferred_first_name"].Value = person.preferredFirstName;
if (person.dateOfBirth == System.DateTime.MinValue)
{
sqlCommandUpdatePerson.Parameters["@date_of_birth"].Value = System.DBNull.Value;
}
else
{
sqlCommandUpdatePerson.Parameters["@date_of_birth"].Value = person.dateOfBirth;
}
sqlCommandUpdatePerson.Parameters["@record_last_updated_date_time"].Value = DateTime.MinValue;
sqlCommandUpdatePerson.Parameters["@person_id"].Value = person.personId;
await sqlCommandUpdatePerson.ExecuteNonQueryAsync();
returnValue.resultCode = Models.APICallResult.RESULT_CODE_SUCCESS;
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in PersonWSController::UpdatePerson(). Message is {0}", ex1.Message));
if (ex1.InnerException != null)
{
logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message));
if (ex1.InnerException.InnerException != null)
{
logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message));
}
}
logger.LogError(string.Format("{0}", ex1.StackTrace));
return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message));
}
} // UpdatePerson()
public async Task <ActionResult <Models.Person> > GetPerson(int personId)
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandGetPerson;
NpgsqlDataReader sqlDataReaderGetPerson;
try
{
Models.Person returnValue = new Models.Person();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("SELECT p.last_name, p.first_name, p.middle_name, p.generation_suffix, p.preferred_first_name, p.date_of_birth ");
sqlStatement.Append(" FROM person p ");
sqlStatement.Append(" WHERE p.person_id = @person_id ");
sqlCommandGetPerson = sqlConnection.CreateCommand();
sqlCommandGetPerson.CommandText = sqlStatement.ToString();
sqlCommandGetPerson.CommandTimeout = 600;
sqlCommandGetPerson.Parameters.Add(new NpgsqlParameter("@person_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandGetPerson.Parameters["@person_id"].Value = personId;
await sqlCommandGetPerson.PrepareAsync();
using (sqlDataReaderGetPerson = await sqlCommandGetPerson.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
{
if (await sqlDataReaderGetPerson.ReadAsync())
{
returnValue.personId = personId;
returnValue.lastName = sqlDataReaderGetPerson.GetString(ApplicationValues.PERSON_QUERY_RESULT_COLUMN_OFFSET_LAST_NAME);
returnValue.firstName = sqlDataReaderGetPerson.GetString(ApplicationValues.PERSON_QUERY_RESULT_COLUMN_OFFSET_FIRST_NAME);
if (!await sqlDataReaderGetPerson.IsDBNullAsync(ApplicationValues.PERSON_QUERY_RESULT_COLUMN_OFFSET_MIDDLE_NAME))
{
returnValue.middleName = sqlDataReaderGetPerson.GetString(ApplicationValues.PERSON_QUERY_RESULT_COLUMN_OFFSET_MIDDLE_NAME);
}
if (!await sqlDataReaderGetPerson.IsDBNullAsync(ApplicationValues.PERSON_QUERY_RESULT_COLUMN_OFFSET_GENERATION_SUFFIX))
{
returnValue.generationSuffix = sqlDataReaderGetPerson.GetString(ApplicationValues.PERSON_QUERY_RESULT_COLUMN_OFFSET_GENERATION_SUFFIX);
}
returnValue.preferredFirstName = sqlDataReaderGetPerson.GetString(ApplicationValues.PERSON_QUERY_RESULT_COLUMN_OFFSET_PREFERRED_FIRST_NAME);
if (!await sqlDataReaderGetPerson.IsDBNullAsync(ApplicationValues.PERSON_QUERY_RESULT_COLUMN_OFFSET_DATE_OF_BIRTH))
{
returnValue.dateOfBirth = sqlDataReaderGetPerson.GetDateTime(ApplicationValues.PERSON_QUERY_RESULT_COLUMN_OFFSET_DATE_OF_BIRTH);
}
}
;
await sqlDataReaderGetPerson.CloseAsync();
};
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in PersonWSController::GetPerson(). Message is {0}", ex1.Message));
if (ex1.InnerException != null)
{
logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message));
if (ex1.InnerException.InnerException != null)
{
logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message));
}
}
logger.LogError(string.Format("{0}", ex1.StackTrace));
return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message));
}
} // GetPerson()
public async Task <ActionResult <Models.ElectionForTerritory> > GetDistinctElectedOfficeForTerritory(int distinctElectedOfficeForTerritoryId)
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandGetTerritory;
NpgsqlDataReader sqlDataReaderGetTerritory;
try
{
Models.DistinctElectedOfficeForTerritory returnValue = new Models.DistinctElectedOfficeForTerritory();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("SELECT deot.electedOfficeForTerritoryId, eot.territory_id, eo.reference_name, deot.distinctive_office_designator ");
sqlStatement.Append(" FROM distinct_elected_office_for_territory deot inner join elected_office_for_territory eot on deot.elected_office_for_territory_id = eot.elected_office_for_territory_id ");
sqlStatement.Append(" INNER JOIN elected_office eo on eot.elected_office_id = eo.elected_office_id ");
sqlStatement.Append(" WHERE deot.distinct_elected_office_for_territory_id = @distinct_elected_office_for_territory_id ");
sqlCommandGetTerritory = sqlConnection.CreateCommand();
sqlCommandGetTerritory.CommandText = sqlStatement.ToString();
sqlCommandGetTerritory.CommandTimeout = 600;
sqlCommandGetTerritory.Parameters.Add(new NpgsqlParameter("@election_for_territory_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandGetTerritory.Parameters["@distinct_elected_office_for_territory_id"].Value = 0;
await sqlCommandGetTerritory.PrepareAsync();
sqlCommandGetTerritory.Parameters["@distinct_elected_office_for_territory_id"].Value = distinctElectedOfficeForTerritoryId;
using (sqlDataReaderGetTerritory = await sqlCommandGetTerritory.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
{
if (await sqlDataReaderGetTerritory.ReadAsync())
{
returnValue.distinctElectedOfficeForTerritoryId = distinctElectedOfficeForTerritoryId;
returnValue.electedOfficeForTerritoryId = sqlDataReaderGetTerritory.GetInt32(ApplicationValues.DISTINCT_ELECTED_OFFICE_FOR_TERRITORY_QUERY_RESULT_COLUMN_OFFSET_ELECTED_OFFICE_FOR_TERRITORY_ID);
returnValue.electedOfficeReferenceName = sqlDataReaderGetTerritory.GetString(ApplicationValues.DISTINCT_ELECTED_OFFICE_FOR_TERRITORY_QUERY_RESULT_COLUMN_OFFSET_ELECTED_OFFICE_REFERENCE_NAME);
returnValue.distinctOfficeDesignator = sqlDataReaderGetTerritory.GetString(ApplicationValues.DISTINCT_ELECTED_OFFICE_FOR_TERRITORY_QUERY_RESULT_COLUMN_OFFSET_DISTINCT_OFFICE_DEISGNATOR);
}
;
await sqlDataReaderGetTerritory.CloseAsync();
};
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in DistinctElectedOfficeForTerritoryWSController::GetDistinctElectedOfficeForTerritory(). Message is {0}", ex1.Message));
if (ex1.InnerException != null)
{
logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message));
if (ex1.InnerException.InnerException != null)
{
logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message));
}
}
logger.LogError(string.Format("{0}", ex1.StackTrace));
return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message));
}
} // GetDistinctElectedOfficeForTerritory()
public async Task <IQueuedTaskToken> DequeueAsync(params string[] selectTaskTypes)
{
NpgsqlConnection conn = null;
QueuedTask dequeuedTask = null;
QueuedTaskResult dequeuedTaskResult = null;
PostgreSqlQueuedTaskToken dequeuedTaskToken = null;
MonotonicTimestamp startDequeue;
DateTimeOffset refNow = mTimestampProvider.GetNow();
CheckNotDisposedOrThrow();
try
{
mLogger.DebugFormat("Begin dequeue task. Looking for types: {0}.",
string.Join <string>(",", selectTaskTypes));
startDequeue = MonotonicTimestamp
.Now();
conn = await OpenQueueConnectionAsync();
if (conn == null)
{
return(null);
}
using (NpgsqlTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{
//1. Dequeue means that we acquire lock on a task in the queue
// with the guarantee that nobody else did, and respecting
// the priority and static locks (basically the task_locked_until which says
// that it should not be pulled out of the queue until the
// current abstract time reaches that tick value)
dequeuedTask = await TryDequeueTaskAsync(selectTaskTypes, refNow, conn, tx);
if (dequeuedTask != null)
{
//2. Mark the task as being "Processing" and pull result info
// The result is stored separately and it's what allows us to remove
// the task from the queue at step #2,
// whils also tracking it's processing status and previous results
dequeuedTaskResult = await TryUpdateTaskResultAsync(dequeuedTask, conn, tx);
if (dequeuedTaskResult != null)
{
await tx.CommitAsync();
dequeuedTaskToken = new PostgreSqlQueuedTaskToken(dequeuedTask,
dequeuedTaskResult,
refNow);
}
}
if (dequeuedTaskToken != null)
{
IncrementDequeueCount(MonotonicTimestamp.Since(startDequeue));
}
else
{
await tx.RollbackAsync();
}
}
}
finally
{
if (conn != null)
{
await conn.CloseAsync();
conn.Dispose();
}
}
return(dequeuedTaskToken);
}
public async Task StopAsync()
{
await _connection.CloseAsync();
await _database.StopAsync();
}
public async Task <ActionResult <List <Models.TerritoryLevel> > > GetTerritoryLevelList()
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandGetTerritoryLevelList;
NpgsqlDataReader sqlDataReaderGetTerritoryLevelList;
try
{
List <Models.TerritoryLevel> returnValue = new List <Models.TerritoryLevel>();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("SELECT t.territory_level_id, t.reference_name ");
sqlStatement.Append(" FROM territory_level t ");
sqlStatement.Append(" ORDER BY t.reference_name ");
sqlCommandGetTerritoryLevelList = sqlConnection.CreateCommand();
sqlCommandGetTerritoryLevelList.CommandText = sqlStatement.ToString();
sqlCommandGetTerritoryLevelList.CommandTimeout = 600;
await sqlCommandGetTerritoryLevelList.PrepareAsync();
using (sqlDataReaderGetTerritoryLevelList = await sqlCommandGetTerritoryLevelList.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
{
while (await sqlDataReaderGetTerritoryLevelList.ReadAsync())
{
Models.TerritoryLevel territoryLevel = new Models.TerritoryLevel();
territoryLevel.territoryLevelId = sqlDataReaderGetTerritoryLevelList.GetInt32(ApplicationValues.TERRITORY_LEVEL_LIST_QUERY_RESULT_COLUMN_OFFSET_TERRITORY_LEVEL_ID);
territoryLevel.referenceName = sqlDataReaderGetTerritoryLevelList.GetString(ApplicationValues.TERRITORY_LEVEL_LIST_QUERY_RESULT_COLUMN_OFFSET_REFERENCE_NAME);
returnValue.Add(territoryLevel);
}
;
await sqlDataReaderGetTerritoryLevelList.CloseAsync();
};
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in DictionaryWSController::GetTerritoryLevelList(). Message is {0}", ex1.Message));
if (ex1.InnerException != null)
{
logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message));
if (ex1.InnerException.InnerException != null)
{
logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message));
}
}
logger.LogError(string.Format("{0}", ex1.StackTrace));
return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message));
}
} // GetTerritoryLevelList()
protected override async Task OnMessageActivityAsync(ITurnContext <IMessageActivity> turnContext, CancellationToken cancellationToken)
{
string message = turnContext.Activity.Text.ToLower();
if (message.Equals("hello"))
{
await turnContext.SendActivityAsync(CreateActivityWithTextAndSpeak("Hello How are you?"), cancellationToken);
}
else if (message.Equals("test"))
{
Int32 usercount = 0;
using (var conn = new NpgsqlConnection(NEWconnString))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT count(DISTINCT \"menu1\") FROM public.\"DecisionTreeMapping\";"
, conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
usercount = reader.GetInt32(0);
}
await turnContext.SendActivityAsync(CreateActivityWithTextAndSpeak("Demo number = " + usercount.ToString()), cancellationToken);
await conn.CloseAsync();
}
//await turnContext.SendActivityAsync(MessageFactory.SuggestedActions("string1","null")), cancellationToken);
}
else if (message.Equals("file"))
{
try
{
await turnContext.SendActivityAsync(MessageFactory.Attachment(GetInlineAttachment()), cancellationToken);
//WebClient client = new WebClient();
//client.Proxy = WebRequest.DefaultWebProxy;
//client.Credentials = new NetworkCredential("inteleqt-admin", "P@ssword$$01");
//client.DownloadFile(@"\\\np.inteleqt.co.za\Reports\Cargo\Cargo Chatbot - Hourly.pdf", @"D:\Desktop\Blue Label Content\Final\Documents\Chatbot.PDF");
}
catch (Exception e)
{
string exception = e.ToString();
await turnContext.SendActivityAsync(CreateActivityWithTextAndSpeak(exception), cancellationToken);
}
}
else if (message.Equals("ask"))
{
var reply = MessageFactory.Text("What is your favorite color?");
reply.SuggestedActions = new SuggestedActions()
{
Actions = new List <CardAction>()
{
new CardAction()
{
Title = "Red", Type = ActionTypes.ImBack, Value = "#test"
},
new CardAction()
{
Title = "Yellow", Type = ActionTypes.ImBack, Value = "Yellow"
},
new CardAction()
{
Title = "Blue", Type = ActionTypes.ImBack, Value = "Blue"
},
},
};
await turnContext.SendActivityAsync(reply, cancellationToken);
}
else if (message.Equals("#test"))
{
await turnContext.SendActivityAsync(CreateActivityWithTextAndSpeak("Received the test"), cancellationToken);
}
else
{
await turnContext.SendActivityAsync(CreateActivityWithTextAndSpeak($"You said:\"{turnContext.Activity.Text}\""), cancellationToken);
}
}
public async Task <ActionResult <Models.TerritoryLevel> > GetTerritoryLevel(int territoryLevelId)
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandGetTerritoryLevel;
NpgsqlDataReader sqlDataReaderGetTerritoryLevel;
try
{
Models.TerritoryLevel returnValue = new Models.TerritoryLevel();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("SELECT t.reference_name");
sqlStatement.Append(" FROM territory_level t ");
sqlStatement.Append(" WHERE t.territory_level_id = @territory_level_id ");
sqlCommandGetTerritoryLevel = sqlConnection.CreateCommand();
sqlCommandGetTerritoryLevel.CommandText = sqlStatement.ToString();
sqlCommandGetTerritoryLevel.CommandTimeout = 600;
sqlCommandGetTerritoryLevel.Parameters.Add(new NpgsqlParameter("@territory_level_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandGetTerritoryLevel.Parameters["@territory_level_id"].Value = 0;
await sqlCommandGetTerritoryLevel.PrepareAsync();
sqlCommandGetTerritoryLevel.Parameters["@territory_level_id"].Value = territoryLevelId;
using (sqlDataReaderGetTerritoryLevel = await sqlCommandGetTerritoryLevel.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
{
if (await sqlDataReaderGetTerritoryLevel.ReadAsync())
{
returnValue.territoryLevelId = territoryLevelId;
returnValue.referenceName = sqlDataReaderGetTerritoryLevel.GetString(ApplicationValues.TERRITORY_LEVEL_QUERY_RESULT_COLUMN_OFFSET_REFERENCE_NAME);
}
;
await sqlDataReaderGetTerritoryLevel.CloseAsync();
};
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in DictionaryWSController::GetTerritoryLevel(). Message is {0}", ex1.Message));
if (ex1.InnerException != null)
{
logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message));
if (ex1.InnerException.InnerException != null)
{
logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message));
}
}
logger.LogError(string.Format("{0}", ex1.StackTrace));
return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message));
}
} // GetTerritoryLevel()
private async Task <bool> tryToAttainLockAndStartShards()
{
bool gotLock = false;
NpgsqlConnection conn = null;
try
{
conn = _tenant.CreateConnection();
await conn.OpenAsync(_cancellation.Token);
gotLock = (bool)await conn.CreateCommand("SELECT pg_try_advisory_lock(:id);")
.With("id", _settings.DaemonLockId)
.ExecuteScalarAsync(_cancellation.Token);
if (!gotLock)
{
await conn.CloseAsync();
}
}
catch (Exception e)
{
conn?.SafeDispose();
_logger.LogError("Error trying to attain the async daemon lock", e);
return(false);
}
if (gotLock)
{
_logger.LogInformation("Attained lock for the async daemon, attempting to start all shards");
try
{
await startAllProjections(conn);
stopPollingForOwnership();
return(true);
}
catch (Exception ex)
{
_logger.LogError("Failure while trying to start all async projection shards", ex);
}
}
else
{
_logger.LogDebug("Attempted to attain lock for async projections, but could not take leadership.");
}
if (_timer == null || !_timer.Enabled)
{
startPollingForOwnership();
}
else
{
_timer.Start();
}
return(false);
}
public async Task <ActionResult <Models.ReasonForDeparture> > GetReasonForDeparture(int reasonForDepartureId)
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandGetReasonForDeparture;
NpgsqlDataReader sqlDataReaderGetReasonForDeparture;
try
{
Models.ReasonForDeparture returnValue = new Models.ReasonForDeparture();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("SELECT r.description ");
sqlStatement.Append(" FROM reason_for_departure r ");
sqlStatement.Append(" WHERE r.reason_for_departure_id = @reason_for_departure_id ");
sqlCommandGetReasonForDeparture = sqlConnection.CreateCommand();
sqlCommandGetReasonForDeparture.CommandText = sqlStatement.ToString();
sqlCommandGetReasonForDeparture.CommandTimeout = 600;
sqlCommandGetReasonForDeparture.Parameters.Add(new NpgsqlParameter("@reason_for_departure_id", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandGetReasonForDeparture.Parameters["@reason_for_departure_id"].Value = 0;
await sqlCommandGetReasonForDeparture.PrepareAsync();
sqlCommandGetReasonForDeparture.Parameters["@reason_for_departure_id"].Value = reasonForDepartureId;
using (sqlDataReaderGetReasonForDeparture = await sqlCommandGetReasonForDeparture.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
{
if (await sqlDataReaderGetReasonForDeparture.ReadAsync())
{
returnValue.reasonForDepartureId = reasonForDepartureId;
returnValue.description = sqlDataReaderGetReasonForDeparture.GetString(ApplicationValues.REASON_FOR_DEPARTURE_QUERY_RESULT_COLUMN_OFFSET_DESCRIPTION);
}
;
await sqlDataReaderGetReasonForDeparture.CloseAsync();
};
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in DictionaryWSController::GetReasonForDeparture(). Message is {0}", ex1.Message));
if (ex1.InnerException != null)
{
logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message));
if (ex1.InnerException.InnerException != null)
{
logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message));
}
}
logger.LogError(string.Format("{0}", ex1.StackTrace));
return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message));
}
} // GetReasonForDeparture()
public async Task <GenericObject> GetTimeSeries(string tableName, string queryString)
{
NpgsqlConnection _npgsqlConnection = new NpgsqlConnection(_databaseSettings.DatabaseConnectionString);
await _npgsqlConnection.OpenAsync();
using var cmd = new NpgsqlCommand(queryString);
cmd.Connection = _npgsqlConnection;
var dataReader = await cmd.ExecuteReaderAsync();
var completeTimeData = new List <List <DateTime> >();
var completeData = new List <DataObject <Decimal> >();
do
{
var timeData = new List <DateTime>();
var data = new List <DataObject <Decimal> >();
List <(string, int)> tableColumns = this.GetColumnNames(dataReader);
foreach (var column in tableColumns)
{
data.Add(
new DataObject <Decimal>()
{
Data = new List <Decimal>()
}
);
}
while (dataReader.Read())
{
DateTime date = dataReader.GetFieldValue <DateTime>(dataReader.GetOrdinal("time")).ToUniversalTime();
timeData.Add(date);
for (var i = 0; i < tableColumns.Count(); i++)
{
var dataValue = dataReader.GetFieldValue <dynamic>(tableColumns[i].Item2);
Type dataType = dataValue.GetType();
ReadTimeSeriesData <Decimal>(dataReader, data[i], tableColumns[i].Item2);
}
}
;
if (timeData.Count() == 0)
{
break;
//throw new QueryException(ErrorBuilder.New().SetMessage("No data.").Build());
}
var startTime = timeData[0].Ticks;
var interval = timeData[1].Ticks - startTime;
for (var i = 0; i < tableColumns.Count(); i++)
{
data[i].Name = tableColumns[0].Item1;
data[i].StartTime = startTime;
data[i].Interval = interval;
}
completeTimeData.Add(timeData);
completeData.AddRange(data);
} while (dataReader.NextResult());
cmd.Parameters.Clear();
await dataReader.CloseAsync();
await _npgsqlConnection.CloseAsync();
var selectedTimeData = completeTimeData.FirstOrDefault() ?? new List <DateTime>();
var result = new GenericObject()
{
Table = tableName,
StartDate = selectedTimeData.Count > 0 ? selectedTimeData[0].ToUniversalTime() : DateTime.UtcNow,
EndDate = selectedTimeData.Count > 0 ? selectedTimeData[selectedTimeData.Count - 1].ToUniversalTime() : DateTime.UtcNow,
Time = selectedTimeData,
Data = completeData
};
return(result);
}
public async Task <ActionResult <List <Models.ReasonForDeparture> > > GetReasonForDepartureList()
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandGetResultOfCandidacyList;
NpgsqlDataReader sqlDataReaderGetResultOfCandidacyList;
try
{
List <Models.ReasonForDeparture> returnValue = new List <Models.ReasonForDeparture>();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("SELECT r.reason_for_departure_id, r.description ");
sqlStatement.Append(" FROM reason_for_departure r ");
sqlStatement.Append(" ORDER BY r.description ");
sqlCommandGetResultOfCandidacyList = sqlConnection.CreateCommand();
sqlCommandGetResultOfCandidacyList.CommandText = sqlStatement.ToString();
sqlCommandGetResultOfCandidacyList.CommandTimeout = 600;
await sqlCommandGetResultOfCandidacyList.PrepareAsync();
using (sqlDataReaderGetResultOfCandidacyList = await sqlCommandGetResultOfCandidacyList.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
{
while (await sqlDataReaderGetResultOfCandidacyList.ReadAsync())
{
Models.ReasonForDeparture reasonForDeparture = new Models.ReasonForDeparture();
reasonForDeparture.reasonForDepartureId = sqlDataReaderGetResultOfCandidacyList.GetInt32(ApplicationValues.REASON_FOR_DEPARTURE_LIST_QUERY_RESULT_COLUMN_OFFSET_REASON_FOR_DEPARTURE_ID);
reasonForDeparture.description = sqlDataReaderGetResultOfCandidacyList.GetString(ApplicationValues.REASON_FOR_DEPARTURE_LIST_QUERY_RESULT_COLUMN_OFFSET_DESCRIPTION);
returnValue.Add(reasonForDeparture);
}
;
await sqlDataReaderGetResultOfCandidacyList.CloseAsync();
};
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in DictionaryWSController::GetReasonForDepartureList(). Message is {0}", ex1.Message));
if (ex1.InnerException != null)
{
logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message));
if (ex1.InnerException.InnerException != null)
{
logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message));
}
}
logger.LogError(string.Format("{0}", ex1.StackTrace));
return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message));
}
} // GetReasonForDepartureList()
private async Task ProcessResultBatchAsync(Queue <PostgreSqlTaskResultQueueProcessRequest> currentBatch)
{
MonotonicTimestamp startWrite = MonotonicTimestamp
.Now();
//An explicit choice has been made not to use transactions
// since failing to update a result MUST NOT
// cause the other successful updates to be rolled back.
using (NpgsqlConnection conn = await OpenConnectionAsync(CancellationToken.None))
using (NpgsqlCommand updateCmd = new NpgsqlCommand(mUpdateSql, conn))
{
NpgsqlParameter pStatus = updateCmd.Parameters
.Add("t_status", NpgsqlDbType.Integer);
NpgsqlParameter pLastError = updateCmd.Parameters
.Add("t_last_error", NpgsqlDbType.Text);
NpgsqlParameter pErrorCount = updateCmd.Parameters
.Add("t_error_count", NpgsqlDbType.Integer);
NpgsqlParameter pLastErrorIsRecoverable = updateCmd.Parameters
.Add("t_last_error_recoverable", NpgsqlDbType.Boolean);
NpgsqlParameter pProcessingTime = updateCmd.Parameters
.Add("t_processing_time_milliseconds", NpgsqlDbType.Bigint);
NpgsqlParameter pFinalizedAt = updateCmd.Parameters
.Add("t_processing_finalized_at_ts", NpgsqlDbType.TimestampTz);
NpgsqlParameter pId = updateCmd.Parameters
.Add("t_id", NpgsqlDbType.Uuid);
await updateCmd.PrepareAsync();
while (currentBatch.Count > 0)
{
PostgreSqlTaskResultQueueProcessRequest processRq =
currentBatch.Dequeue();
try
{
pStatus.Value = ( int )processRq.ResultToUpdate.Status;
string strLastError = processRq.ResultToUpdate.LastError.ToJson();
if (strLastError != null)
{
pLastError.Value = strLastError;
}
else
{
pLastError.Value = DBNull.Value;
}
pErrorCount.Value = processRq.ResultToUpdate.ErrorCount;
pLastErrorIsRecoverable.Value = processRq.ResultToUpdate.LastErrorIsRecoverable;
pProcessingTime.Value = processRq.ResultToUpdate.ProcessingTimeMilliseconds;
if (processRq.ResultToUpdate.ProcessingFinalizedAtTs.HasValue)
{
pFinalizedAt.Value = processRq.ResultToUpdate.ProcessingFinalizedAtTs;
}
else
{
pFinalizedAt.Value = DBNull.Value;
}
pId.Value = processRq.ResultToUpdate.Id;
int affectedRows = await updateCmd.ExecuteNonQueryAsync();
processRq.SetCompleted(affectedRows);
IncrementResultWriteCount(MonotonicTimestamp
.Since(startWrite));
}
catch (OperationCanceledException)
{
processRq.SetCancelled();
throw;
}
catch (Exception exc)
{
processRq.SetFailed(exc);
if (processRq.CanBeRetried)
{
mResultProcessingQueue.Add(processRq);
}
mLogger.Error("Error processing result", exc);
}
}
await conn.CloseAsync();
}
}
[HttpPost] // RJB 2020-12-26 Considered incorrect to have data in body of GET method
public async Task <ActionResult <List <Models.Person> > > SearchPerson([FromBody] Models.PersonSearchParameterSet personSearchParamterSet)
{
System.Text.StringBuilder sqlStatement;
DateTime processingDateTime;
NpgsqlConnection sqlConnection;
NpgsqlCommand sqlCommandSearchPerson;
NpgsqlDataReader sqlDataReaderSearchPerson;
try
{
List <Models.Person> returnValue = new List <Models.Person>();
processingDateTime = System.DateTime.Now;
using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
{
await sqlConnection.OpenAsync();
sqlStatement = new System.Text.StringBuilder();
sqlStatement.Append("SELECT p.person_id, p.last_name, p.first_name, p.middle_name, p.generation_suffix, p.preferred_first_name, p.date_of_birth ");
sqlStatement.Append(" FROM person p ");
sqlStatement.Append(" WHERE UPPER(p.last_name) LIKE UPPER(@last_name_search_mask) AND UPPER(p.first_name) LIKE UPPER(@first_name_search_mask) AND ");
sqlStatement.Append(" UPPER(p.preferred_first_name) LIKE UPPER(@preferred_first_name_search_mask) ");
sqlStatement.Append(" ORDER BY p.last_name, p.first_name, p.preferred_first_name ");
sqlStatement.Append(" LIMIT @return_limit ");
sqlCommandSearchPerson = sqlConnection.CreateCommand();
sqlCommandSearchPerson.CommandText = sqlStatement.ToString();
sqlCommandSearchPerson.CommandTimeout = 600;
sqlCommandSearchPerson.Parameters.Add(new NpgsqlParameter("@last_name_search_mask", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandSearchPerson.Parameters.Add(new NpgsqlParameter("@first_name_search_mask", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandSearchPerson.Parameters.Add(new NpgsqlParameter("@preferred_first_name_search_mask", NpgsqlTypes.NpgsqlDbType.Varchar, 50));
sqlCommandSearchPerson.Parameters.Add(new NpgsqlParameter("@return_limit", NpgsqlTypes.NpgsqlDbType.Integer));
sqlCommandSearchPerson.Parameters["@last_name_search_mask"].Value = "";
sqlCommandSearchPerson.Parameters["@first_name_search_mask"].Value = "";
sqlCommandSearchPerson.Parameters["@preferred_first_name_search_mask"].Value = "";
sqlCommandSearchPerson.Parameters["@return_limit"].Value = 0;
await sqlCommandSearchPerson.PrepareAsync();
sqlCommandSearchPerson.Parameters["@last_name_search_mask"].Value = personSearchParamterSet.lastNameSearchMask;
sqlCommandSearchPerson.Parameters["@first_name_search_mask"].Value = personSearchParamterSet.firstNameSearchMask;
sqlCommandSearchPerson.Parameters["@preferred_first_name_search_mask"].Value = personSearchParamterSet.preferredFirstNameSearchMask;
sqlCommandSearchPerson.Parameters["@return_limit"].Value = int.Parse(configuration["AppSettings:SearchReturnLimit"]);
using (sqlDataReaderSearchPerson = await sqlCommandSearchPerson.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection))
{
while (await sqlDataReaderSearchPerson.ReadAsync())
{
Models.Person person = new Models.Person();
person.personId = sqlDataReaderSearchPerson.GetInt32(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_PERSON_ID);
person.lastName = sqlDataReaderSearchPerson.GetString(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_LAST_NAME);
person.firstName = sqlDataReaderSearchPerson.GetString(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_FIRST_NAME);
if (!await sqlDataReaderSearchPerson.IsDBNullAsync(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_MIDDLE_NAME))
{
person.middleName = sqlDataReaderSearchPerson.GetString(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_MIDDLE_NAME);
}
if (!await sqlDataReaderSearchPerson.IsDBNullAsync(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_GENERATION_SUFFIX))
{
person.generationSuffix = sqlDataReaderSearchPerson.GetString(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_GENERATION_SUFFIX);
}
if (!await sqlDataReaderSearchPerson.IsDBNullAsync(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_PREFERRED_FIRST_NAME))
{
person.preferredFirstName = sqlDataReaderSearchPerson.GetString(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_PREFERRED_FIRST_NAME);
}
if (!await sqlDataReaderSearchPerson.IsDBNullAsync(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_DATE_OF_BIRTH))
{
person.dateOfBirth = sqlDataReaderSearchPerson.GetDateTime(ApplicationValues.PERSON_LIST_QUERY_RESULT_COLUMN_OFFSET_DATE_OF_BIRTH);
}
returnValue.Add(person);
}
;
await sqlDataReaderSearchPerson.CloseAsync();
};
await sqlConnection.CloseAsync();
} // using (sqlConnection = new NpgsqlConnection(configuration["ConnectionStrings:PolitiScout"]))
return(Ok(returnValue));
}
catch (Exception ex1)
{
logger.LogError(string.Format("Unhandled exception occurred in PersonWSController::GetPerson(). Message is {0}", ex1.Message));
if (ex1.InnerException != null)
{
logger.LogError(string.Format(" -- Inner exception message is {0}", ex1.InnerException.Message));
if (ex1.InnerException.InnerException != null)
{
logger.LogError(string.Format(" -- -- Inner exception message is {0}", ex1.InnerException.InnerException.Message));
}
}
logger.LogError(string.Format("{0}", ex1.StackTrace));
return(StatusCode(StatusCodes.Status500InternalServerError, ex1.Message));
}
} // GetPerson()