public void Post_MemberNotFound()
{
string post;
using (var db = new KcsarContext())
{
var existing = db.TrainingAward.First();
post = string.Format(
@"{{""Course"":{{""Id"":""{0}""}},""Member"":{{""Id"":""{1}""}},""Completed"":""{2}"",""ExpirySrc"":""default""}}",
existing.Course.Id,
Guid.NewGuid(),
existing.Completed);
}
try
{
DatabaseAutomation.PostJsonAsAdmin("/api/TrainingRecords/Post", post);
Assert.Fail("Request should have failed");
}
catch (WebException e)
{
Assert.AreEqual(HttpStatusCode.BadRequest, ((HttpWebResponse)e.Response).StatusCode, "Status code");
string response = DatabaseAutomation.ReadExceptionResponse(e);
Console.WriteLine(response);
var errors = JsonConvert.DeserializeObject <Dictionary <string, string> >(response);
Assert.AreEqual(1, errors.Count, "Count");
Assert.AreEqual("Not found", errors["Member"], "error text");
}
}
public void LogObjectDeleted()
{
Guid memberId;
string reportHtml;
using (var db = new KcsarContext(this.databaseLocation))
{
Member m = new Member {
FirstName = "RemoveMe"
};
db.Members.Add(m);
db.SaveChanges();
memberId = m.Id;
reportHtml = m.GetReportHtml();
}
DateTime checkpoint = GetCheckpoint();
using (var db = new KcsarContext(this.databaseLocation))
{
Member m = db.Members.Single(f => f.Id == memberId);
db.Members.Remove(m);
db.SaveChanges();
var log = db.GetLog(checkpoint);
Assert.AreEqual(1, log.Length, "log entries");
Assert.AreEqual("Deleted", log[0].Action, "action: " + log[0].Action);
Assert.AreEqual(reportHtml, log[0].Comment, "log msg: " + log[0].Comment);
}
}
public static void DeleteDatabase(string connString)
{
using (var context = new KcsarContext(connString, Console.WriteLine))
{
context.Database.Delete();
}
}
public void DuplicateRecordIsCaught()
{
TrainingAward existing;
using (var db = new KcsarContext())
{
existing = db.TrainingAward.Where(f => f.Member.Id == memberId).First();
var find = this.browser.Find;
var addAward = find.ById <HtmlButton>("addrecord", 3000);
Assert.IsNotNull(addAward, "Can't find add record button");
addAward.Click();
var courseDropdown = find.ById <HtmlSelect>("recordcourse", 2000);
courseDropdown.SelectByValue(existing.Course.Id.ToString());
var completedBox = find.ById <HtmlInputText>("recordcompleted");
completedBox.Text = existing.Completed.ToString("yyyy-MM-dd HH:mm:ss");
var commentBox = find.ById <HtmlTextArea>("recordcomments");
commentBox.Text = "Test Comment";
var saveButton = find.ById <HtmlDiv>("editRecordDialog").Parent <HtmlDiv>().Find.ByCustom <HtmlButton>(f => f.InnerText == "Save");
Assert.IsNotNull(saveButton);
saveButton.Click();
System.Threading.Thread.Sleep(3000);
var msgP = find.ById <HtmlContainerControl>("editRecordError");
Assert.IsNotNullOrEmpty(msgP.InnerText);
Assert.IsTrue(msgP.IsVisible());
}
}
public void LogObjectCreated()
{
Guid memberId;
DateTime checkpoint;
checkpoint = GetCheckpoint();
using (var db = new KcsarContext(this.databaseLocation))
{
Member m = new Member {
FirstName = "NewUser"
};
db.Members.Add(m);
db.SaveChanges();
memberId = m.Id;
}
using (var db = new KcsarContext(this.databaseLocation))
{
var member = db.Members.Single(f => f.Id == memberId);
var logs = db.GetLog(checkpoint);
Assert.AreEqual(1, logs.Length, "log entries");
Assert.AreEqual(member.GetReportHtml(), logs[0].Comment, "log msg: " + logs[0].Comment);
Assert.AreEqual("Added", logs[0].Action, "action: " + logs[0].Action);
}
}
public void LogPropertyChanged()
{
Guid memberId;
using (var db = new KcsarContext(this.databaseLocation))
{
Member m = new Member {
FirstName = "Log property",
Gender = Gender.Female,
BirthDate = new DateTime(2000, 12, 19)
};
db.Members.Add(m);
db.SaveChanges();
memberId = m.Id;
}
DateTime checkpoint = GetCheckpoint();
using (var db = new KcsarContext(this.databaseLocation))
{
Member m = db.Members.Single(f => f.Id == memberId);
m.FirstName = "Fixed";
m.BirthDate = new DateTime(1990, 3, 5);
m.Gender = Gender.Male;
db.SaveChanges();
var log = db.GetLog(checkpoint);
Assert.AreEqual(1, log.Length, "log entries");
Console.WriteLine(log[0].Comment);
Assert.IsTrue(log[0].Comment.Contains("FirstName: Log property => Fixed"), "log msg: " + log[0].Comment);
Assert.IsTrue(log[0].Comment.Contains("Gender: Female => Male"), "log msg gender: " + log[0].Comment);
Assert.IsTrue(log[0].Comment.Contains("BirthDate: 12/19/2000 => 3/5/1990"), "log msg date: " + log[0].Comment);
Assert.AreEqual("Modified", log[0].Action, "action: " + log[0].Action);
}
}
public void SetupDatabase()
{
using (var context = new KcsarContext())
{
Console.WriteLine(context.Members.Count());
}
}
private KcsarContext GetContext()
{
KcsarContext db = new KcsarContext(this.connectionString);
// db.Database.Log = Console.Write;
return(db);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private bool LoadMember()
{
if (member == null && searchedForMember == false)
{
searchedForMember = true;
string key = this.LinkKey;
if (string.IsNullOrEmpty(key))
{
return(false);
}
Guid id = new Guid(key);
using (var context = new KcsarContext())
{
member = (from m in context.Members where m.Id == id select m).FirstOrDefault();
// context.Detach(member);
}
if (member == null)
{
this.LinkKey = null;
}
}
return(member != null);
}
public void Setup()
{
using (var db = new KcsarContext())
{
this.member = db.Members.Where(f => f.ContactNumbers.Count > 1 &&
f.Addresses.Count > 1 &&
f.MissionRosters.Count > 1 &&
f.TrainingRosters.Count > 1).First();
}
this.browser = UIAutomation.GetAdminBrowser();
this.browser.NavigateToPath("/Members/Detail/" + this.member.Id.ToString());
}
public void GetById()
{
Guid id;
using (var db = new KcsarContext())
{
id = db.TrainingAward.First().Id;
}
string url = "/api/TrainingRecords/Get/" + id.ToString();
Console.WriteLine(url);
string content = DatabaseAutomation.DownloadPageAsAdmin(url);
Console.WriteLine(content);
}
public void Setup()
{
using (var db = new KcsarContext())
{
Guid empty = Guid.Empty;
this.memberId = db.Members.Where(f => f.TrainingAwards.Count > 1 && f.Id != empty).First().Id;
}
try
{
this.browser = UIAutomation.GetAdminBrowser();
this.browser.NavigateToPath("/Members/Detail/" + this.memberId.ToString());
}
catch
{
UIAutomation.Shutdown();
throw;
}
}
static void Main(string[] args)
{
using (var db = new KcsarContext())
{
DateTime cutoff = DateTime.Today.AddYears(1);
var course = db.TrainingCourses.Single(f => f.DisplayName == "HAM License");
var hams = db.PersonContact.Where(f => f.Type == "hamcall")
.Where(f => !f.Person.ComputedAwards.Any(g => g.Expiry > cutoff && g.Course.Id == course.Id))
.Select(f => new { Member = f.Person, Call = f.Value })
.OrderBy(f => f.Member.LastName).ThenBy(f => f.Member.FirstName).ToArray();
foreach (var ham in hams)
{
Console.WriteLine(ham.Call);
var lic = GetLicense(ham.Call);
if (lic == null)
{
Console.WriteLine("!! No license found");
continue;
}
if (lic.Name.StartsWith(ham.Member.ReverseName, StringComparison.OrdinalIgnoreCase))
{
// probably a match
Console.WriteLine("Grant HAM to {0}, effective {1:yyyy-MM-dd}, expires {2:yyyy-MM-dd}", ham.Member.FullName, lic.Issued, lic.Expires);
TrainingAward ta = new TrainingAward {
Completed = lic.Issued, Course = course, Expiry = lic.Expires, Member = ham.Member, metadata = "Via FCC: " + lic.Url, LastChanged = DateTime.Now, ChangedBy = "HamFromFCC"
};
db.TrainingAward.Add(ta);
db.SaveChanges();
db.RecalculateTrainingAwards(ham.Member.Id);
db.SaveChanges();
}
else
{
Console.WriteLine("Can't match names: {0} :: {1}", lic.Name, ham.Member.ReverseName);
}
}
}
}
public void Addresses()
{
var table = this.browser.Find.ById <HtmlTable>("address_table");
Assert.IsNotNull(table, "Need table of addresses");
using (var db = new KcsarContext())
{
var addresses = db.PersonAddress.Where(f => f.Person.Id == this.member.Id).ToList();
Assert.AreEqual(addresses.Count, table.Rows.Count, "Table rows should be equal");
addresses.ForEach(f =>
{
Assert.IsTrue(table.Rows.Any(g => g.Cells[1].TextContent == f.SimpleText), "Find address " + f.SimpleText);
});
}
Console.WriteLine("Found {0} addresses", table.Rows.Count);
}
public void LogPrincipalChanged()
{
Member first;
Member second;
PersonAddress address;
using (var db = new KcsarContext(this.databaseLocation))
{
first = new Member {
FirstName = "First"
};
db.Members.Add(first);
second = new Member {
FirstName = "Second"
};
db.Members.Add(second);
address = new PersonAddress {
Person = first, Street = "123", City = "Any", State = "WA", Zip = "98765"
};
first.Addresses.Add(address);
db.SaveChanges();
}
var checkpoint = GetCheckpoint();
using (var db = new KcsarContext(this.databaseLocation))
{
address = db.Members.Where(f => f.Id == first.Id).SelectMany(f => f.Addresses).Single();
address.Person = db.Members.Single(f => f.Id == second.Id);
db.SaveChanges();
}
using (var db = new KcsarContext(this.databaseLocation))
{
var logs = db.GetLog(checkpoint);
Assert.AreEqual(1, logs.Length, "log count");
Assert.AreEqual(string.Format("{0}<br/>{1} => {2}",
address, first, second), logs[0].Comment);
Assert.AreEqual("Modified", logs[0].Action);
}
}
static void Main(string[] args)
{
DateTime startTime = DateTime.Now;
Dictionary <Guid, string> memberIds = null;
using (var context = new KcsarContext())
{
memberIds = context.Members.OrderBy(f => f.LastName).ThenBy(f => f.FirstName).ToDictionary(f => f.Id, f => f.LastName + ", " + f.FirstName);
}
foreach (var pair in memberIds)
{
using (var ctx = new KcsarContext())
{
Console.WriteLine(pair.Value);
ctx.RecalculateTrainingAwards(pair.Key, DateTime.Now);
ctx.SaveChanges();
}
}
Console.WriteLine("Execution time: {0}", (DateTime.Now - startTime));
}
public void UnsubstantiatedRecordIsCaught()
{
TrainingAward existing;
using (var db = new KcsarContext())
{
existing = db.TrainingAward.Where(f => f.Member.Id == memberId).First();
var find = this.browser.Find;
var addAward = find.ById <HtmlButton>("addrecord", 3000);
Assert.IsNotNull(addAward, "Can't find add record button");
addAward.Click();
var courseDropdown = find.ById <HtmlSelect>("recordcourse", 2000);
courseDropdown.SelectByValue(existing.Course.Id.ToString());
DateTime date = existing.Completed;
do
{
date = date.AddDays(-1);
} while (db.TrainingAward.Any(f => f.Member.Id == memberId && f.Completed == date));
var completedBox = find.ById <HtmlInputText>("recordcompleted");
completedBox.Text = date.ToString("yyyy-MM-dd HH:mm:ss");
var saveButton = find.ById <HtmlDiv>("editRecordDialog").Parent <HtmlDiv>().Find.ByCustom <HtmlButton>(f => f.InnerText == "Save");
Assert.IsNotNull(saveButton);
saveButton.Click();
while (find.ById <HtmlDiv>("editRecordProcessing").InnerText == "true")
{
browser.RefreshDomTree();
System.Threading.Thread.Sleep(500);
}
var msgP = find.ById <HtmlContainerControl>("editRecordError");
Assert.IsNotNullOrEmpty(msgP.InnerText);
Assert.IsTrue(msgP.IsVisible());
}
}
public void Contacts()
{
var table = this.browser.Find.ById <HtmlTable>("contacts_table");
Assert.IsNotNull(table, "Need table of contact information");
for (int i = 0; i < 10; i++)
{
if (!table.InnerText.Contains(Strings.Loading))
{
break;
}
Thread.Sleep(1000);
this.browser.RefreshDomTree();
table.Refresh();
}
using (var db = new KcsarContext())
{
Assert.AreEqual(db.PersonContact.Where(f => f.Person.Id == this.member.Id).Count(), table.Rows.Count, "Table rows should be equal");
}
Console.WriteLine("Found {0} contacts", table.Rows.Count);
}
private static string CreateTestDatabase()
{
var path = string.Format(@"{0}\{1}.mdf", System.IO.Directory.GetCurrentDirectory(), Guid.NewGuid());
string connString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=" + path + @";Integrated Security=True;Connect Timeout=30";
Console.WriteLine("Using local database: " + path);
Stopwatch timer = new Stopwatch();
timer.Start();
using (var context = new KcsarContext(connString))
{
var initializer = new MigrateDbToLatestInitializerConnString <KcsarContext, Kcsar.Database.Model.Migrations.Configuration>();
initializer.InitializeDatabase(context);
}
timer.Stop();
Console.WriteLine("Database setup took {0}s", timer.Elapsed.TotalSeconds);
return(connString);
}
static void Main(string[] args)
{
DateTime exp0;
if (args.Length == 0 || !DateTime.TryParse(args[0], out exp0))
{
exp0 = DateTime.Today;
}
DateTime exp1 = exp0.AddDays(7);
DateTime exp2 = exp0.AddMonths(1);
DateTime exp3 = exp0.AddMonths(2);
DateTime day0 = exp0.AddDays(1);
DateTime day1 = exp1.AddDays(1);
DateTime day2 = exp2.AddDays(1);
DateTime day3 = exp3.AddDays(1);
string[] unitConfigs = (ConfigurationManager.AppSettings["UnitsConfig"] ?? "").Split(',');
ParticipatingUnit[] forUnits = new ParticipatingUnit[unitConfigs.Length];
for (int i = 0; i < unitConfigs.Length; i++)
{
var fields = unitConfigs[i].Split(':');
forUnits[i] = new ParticipatingUnit {
UnitId = new Guid(fields[0]), Email = fields[1], NotifyMembers = Boolean.Parse(fields[2])
};
}
if (forUnits.Length == 0)
{
Console.Error.WriteLine("No units configured");
return;
}
string logFile = "sent-mail.log";
MailAddress fromEmail = new MailAddress(ConfigurationManager.AppSettings["MailFrom"] ?? "*****@*****.**");
Dictionary <Guid, List <string> > byUnit = new Dictionary <Guid, List <string> >();
Dictionary <Guid, List <string> > byMember = new Dictionary <Guid, List <string> >();
Dictionary <Guid, string> memberEmails = new Dictionary <Guid, string>();
using (var db = new KcsarContext())
{
var courses = db.TrainingCourses.Where(f => f.WacRequired > 0).ToDictionary(f => f.Id, f => new { Name = f.DisplayName, Required = f.WacRequired });
var targets = db.ComputedTrainingAwards.Include("Member.ContactNumbers").Include("Member.Memberships.Status")
.Where(f => f.Course.WacRequired > 0)
.Where(f => f.Member.Memberships.Any(g => g.Status.IsActive && g.EndTime == null))
.Where(a => (a.Expiry >= exp0 && a.Expiry < day0) ||
(a.Expiry >= exp1 && a.Expiry < day1) ||
(a.Expiry >= exp2 && a.Expiry < day2) ||
(a.Expiry >= exp3 && a.Expiry < day3)
)
.OrderBy(f => f.Expiry).ThenBy(f => f.Member.LastName).ThenBy(f => f.Member.FirstName).ToArray();
// For every expiring training
foreach (var row in targets)
{
// Find all participating unit where this member is active.
var memberOfReportUnits = forUnits.Where(f => row.Member.Memberships.Any(g => g.Status.IsActive && g.EndTime == null && g.Unit.Id == f.UnitId));
// Pretty name of the expiring course
string courseName = courses[row.Course.Id].Name;
// For each unit participating in this report and for which this member is currently active:
foreach (var unit in memberOfReportUnits)
{
if (!byUnit.ContainsKey(unit.UnitId))
{
byUnit.Add(unit.UnitId, new List <string>());
}
byUnit[unit.UnitId].Add(string.Format("{0}: {1} expires on {2:D}", row.Member.FullName, courseName, row.Expiry));
}
// If the unit(s) don't want to notify members directly, skip the next step
if (memberOfReportUnits.Count(f => f.NotifyMembers) == 0)
{
continue;
}
// Add the member's email to the list of tracked emails
if (!byMember.ContainsKey(row.Member.Id))
{
byMember.Add(row.Member.Id, new List <string>());
memberEmails.Add(
row.Member.Id,
row.Member.ContactNumbers
.Where(f => f.Type == "email")
.OrderBy(f => f.Priority)
.Select(f => f.Value)
.FirstOrDefault());
}
byMember[row.Member.Id].Add(string.Format("{0} expires on {1:D}", courseName, row.Expiry));
}
SmtpClient client = new SmtpClient();
foreach (var member in byMember)
{
if (memberEmails[member.Key] == null)
{
continue;
}
MailMessage msg = new MailMessage();
msg.To.Add(memberEmails[member.Key]);
msg.Subject = "KCSARA Database : Expiring Training";
msg.Body = "According to the KCSARA database, your training in the following areas will expire soon:\n\n "
+ string.Join("\n ", member.Value.ToArray())
+ "\n\nPlease work with your unit's training chair to make sure these trainings are renewed."
+ " If you have a login for the KCSARA database, you can view your record at https://database.kcsara.org/Members/Detail/" + member.Key.ToString() + ".";
msg.From = fromEmail;
client.Send(msg);
//client.Send(
File.AppendAllText(logFile, string.Format("{0:yyyy-MM-dd}\t{1}\r\n", DateTime.Now, memberEmails[member.Key]));
}
foreach (var unit in byUnit)
{
string email = forUnits.Single(f => f.UnitId == unit.Key).Email;
if (email == "")
{
continue;
}
MailMessage msg = new MailMessage();
msg.To.Add(email);
msg.Subject = "KCSARA Database : Unit Expiring Training";
msg.Body = "According to the KCSARA database, the following members have training that will expire soon:\n\n " + string.Join("\n ", unit.Value.ToArray());
msg.From = fromEmail;
client.Send(msg);
File.AppendAllText(logFile, string.Format("{0:yyyy-MM-dd}\t{1}\r\n", DateTime.Now, email));
}
MailMessage finishMsg = new MailMessage();
finishMsg.To.Add("*****@*****.**");
finishMsg.Subject = "KCSARA Database expiration report ran successfully";
finishMsg.Body = "Completed with names\n - " + string.Join("\n - ", byUnit.SelectMany(f => f.Value).ToArray());
finishMsg.From = fromEmail;
client.Send(finishMsg);
}
}
public ContentResult UpdateDatabase(string updateKey)
{
StringBuilder result = new StringBuilder("Starting database update ...\n");
var key = ConfigurationManager.AppSettings["DatabaseUpdateKey"];
if (string.IsNullOrWhiteSpace(key))
{
return(Content("DatabaseUpdateKey not in AppSettings"));
}
if (!string.Equals(key, updateKey))
{
return(Content("UpdateKey is not correct"));
}
try
{
var setting = ConfigurationManager.ConnectionStrings["AuthStore"];
if (setting == null || string.IsNullOrWhiteSpace(setting.ConnectionString))
{
return(Content("ConnectionString 'AuthStore' not set"));
}
string authStore = setting.ConnectionString;
setting = ConfigurationManager.ConnectionStrings["DataStore"];
if (setting == null || string.IsNullOrWhiteSpace(setting.ConnectionString))
{
return(Content("ConnectionString 'DataStore' not set"));
}
string dataStore = setting.ConnectionString;
if (!dataStore.ToLowerInvariant().Contains("multipleactiveresultsets=true"))
{
return(Content("DataStore connection string should include MultipleActiveResultSets=true"));
}
result.AppendLine("Starting DataStore Migrations ...");
result.AppendLine(dataStore);
Kcsar.Database.Model.Migrations.Migrator.UpdateDatabase(dataStore);
result.AppendLine("Migrations complete.");
KcsarContext testContext = new KcsarContext();
result.AppendLine("Default Context connection string: " + testContext.Database.Connection.ConnectionString);
result.AppendFormat("Test Context returned {0} members\n", testContext.Members.Count());
using (SqlConnection conn = api.AdminController.CreateAndOpenConnection(authStore, result))
{
bool tableExists;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'aspnet_Applications'", conn))
{
tableExists = (int)cmd.ExecuteScalar() > 0;
}
if (!tableExists)
{
result.AppendLine("Creating authentication/authorization tables ...");
result.AppendLine();
api.AdminController.ExecuteSqlFile(conn, GetSqlContent("Sql", "AuthDatabase_Create.sql"), result);
result.AppendLine();
result.AppendLine("!!!! Default Admin user was created. Login = admin/password !!!!");
}
result.AppendLine();
result.AppendLine("Updating authentication/authorization tables ...");
api.AdminController.ExecuteSqlFile(conn, GetSqlContent("Sql", "AuthDatabase_Update.sql"), result);
}
result.AppendLine("Done");
}
catch (Exception ex)
{
result.AppendLine(ex.ToString());
}
return(Content(result.ToString(), "text/plain"));
}