private void ResetDb()
{
if (db == null)
{
return;
}
var cs = db.Connection.ConnectionString;
db.Dispose();
db = new CMSDataContext(cs);
}
public static void LogActivity(string activity, string name = null, int?orgid = null, int?pid = null)
{
var db = new CMSDataContext(Util.ConnectionString);
int?uid = Util.UserId;
if (uid == 0)
{
uid = null;
}
var a = new ActivityLog
{
ActivityDate = Util.Now,
UserId = uid,
Activity = activity,
Machine = System.Environment.MachineName,
OrgId = orgid,
PeopleId = pid
};
db.ActivityLogs.InsertOnSubmit(a);
db.SubmitChanges();
db.Dispose();
if (orgid.HasValue)
{
var mru = Util2.MostRecentOrgs;
var i = mru.SingleOrDefault(vv => vv.Id == orgid);
if (i != null)
{
mru.Remove(i);
}
mru.Insert(0, new Util2.MostRecentItem()
{
Id = orgid.Value, Name = name
});
if (mru.Count > 5)
{
mru.RemoveAt(mru.Count - 1);
}
}
else if (pid.HasValue && pid != Util.UserPeopleId)
{
var mru = Util2.MostRecentPeople;
var i = mru.SingleOrDefault(vv => vv.Id == pid);
if (i != null)
{
mru.Remove(i);
}
mru.Insert(0, new Util2.MostRecentItem()
{
Id = pid.Value, Name = name
});
if (mru.Count > 5)
{
mru.RemoveAt(mru.Count - 1);
}
}
}
public ActionResult Index(int id)
{
var mm = DbUtil.Db.Meetings.SingleOrDefault(m => m.MeetingId == id);
if (mm == null)
return Content("error: no meeting");
DbUtil.LogActivity("Delete meeting for {0}".Fmt(Session["ActiveOrganization"]));
var runningtotals = new DeleteMeetingRun
{
Started = DateTime.Now,
Count = mm.Attends.Count(a => a.EffAttendFlag == true || a.AttendanceFlag == true),
Processed = 0,
Meetingid = id
};
DbUtil.Db.DeleteMeetingRuns.InsertOnSubmit(runningtotals);
DbUtil.Db.SubmitChanges();
var host = Util.Host;
System.Threading.Tasks.Task.Factory.StartNew(() =>
{
Thread.CurrentThread.Priority = ThreadPriority.BelowNormal;
var Db = new CMSDataContext(Util.GetConnectionString(host));
var cul = Db.Setting("Culture", "en-US");
Thread.CurrentThread.CurrentUICulture = new CultureInfo(cul);
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(cul);
var meeting = Db.Meetings.SingleOrDefault(m => m.MeetingId == id);
var q = from a in Db.Attends
where a.MeetingId == id
where a.AttendanceFlag == true || a.EffAttendFlag == true
select a.PeopleId;
var list = q.ToList();
foreach (var pid in list)
{
Db.Dispose();
Db = new CMSDataContext(Util.GetConnectionString(host));
Attend.RecordAttendance(Db, pid, id, false);
var r = Db.DeleteMeetingRuns.Where(m => m.Meetingid == id).OrderByDescending(m => m.Id).First();
r.Processed++;
Db.SubmitChanges();
}
var rr = Db.DeleteMeetingRuns.Where(m => m.Meetingid == id).OrderByDescending(m => m.Id).First();
rr.Processed--;
Db.SubmitChanges();
Db.ExecuteCommand(
"delete dbo.SubRequest WHERE EXISTS(SELECT NULL FROM Attend a WHERE a.AttendId = AttendId AND a.MeetingId = {0})",
id);
Db.ExecuteCommand("DELETE dbo.VolRequest where MeetingId = {0}", id);
Db.ExecuteCommand("delete attend where MeetingId = {0}", id);
Db.ExecuteCommand("delete MeetingExtra where MeetingId = {0}", id);
Db.ExecuteCommand("delete meetings where MeetingId = {0}", id);
rr.Processed++;
rr.Completed = DateTime.Now;
Db.SubmitChanges();
});
return Redirect("/DeleteMeeting/Progress/" + id);
}
public EnrollmentTransaction Drop(CMSDataContext Db, bool addToHistory)
{
Db.SubmitChanges();
int ntries = 2;
while (true)
{
try
{
var q = from o in Db.Organizations
where o.OrganizationId == OrganizationId
let count = Db.Attends.Count(a => a.PeopleId == PeopleId &&
a.OrganizationId == OrganizationId &&
(a.MeetingDate < DateTime.Today || a.AttendanceFlag == true))
select new { count, Organization.DaysToIgnoreHistory };
var i = q.Single();
if (!EnrollmentDate.HasValue)
{
EnrollmentDate = CreatedDate;
}
var droptrans = new EnrollmentTransaction
{
OrganizationId = OrganizationId,
PeopleId = PeopleId,
MemberTypeId = MemberTypeId,
OrganizationName = Organization.OrganizationName,
TransactionDate = Util.Now,
TransactionTypeId = 5,
// drop
CreatedBy = Util.UserId1,
CreatedDate = Util.Now,
Pending = Pending,
AttendancePercentage = AttendPct,
};
Db.EnrollmentTransactions.InsertOnSubmit(droptrans);
Db.OrgMemMemTags.DeleteAllOnSubmit(this.OrgMemMemTags);
Db.OrganizationMembers.DeleteOnSubmit(this);
Db.ExecuteCommand("DELETE FROM dbo.SubRequest WHERE EXISTS(SELECT NULL FROM Attend a WHERE a.AttendId = AttendId AND a.OrganizationId = {0} AND a.MeetingDate > {1} AND a.PeopleId = {2})", OrganizationId, Util.Now, PeopleId);
Db.ExecuteCommand("DELETE dbo.Attend WHERE OrganizationId = {0} AND MeetingDate > {1} AND PeopleId = {2} AND ISNULL(Commitment, 1) = 1", OrganizationId, Util.Now, PeopleId);
return(droptrans);
}
catch (SqlException ex)
{
if (ex.Number == 1205)
{
if (--ntries > 0)
{
Db.Dispose();
System.Threading.Thread.Sleep(500);
continue;
}
}
throw;
}
}
}
private void ResetDb()
{
if (db == null)
{
return;
}
var dbname = db.Host;
db.Dispose();
db = DbUtil.Create(dbname);
}
private void ResetDb()
{
if (db == null)
{
return;
}
var dbname = db.Host;
db.Dispose();
db = new CMSDataContext(Util.GetConnectionString(dbname));
db.Host = dbname;
}
public ActionResult Start(int tag, int meetingid, bool addasmembers)
{
var runningtotals = new AddToOrgFromTagRun
{
Started = DateTime.Now,
Count = 0,
Processed = 0,
Orgid = meetingid
};
DbUtil.Db.AddToOrgFromTagRuns.InsertOnSubmit(runningtotals);
DbUtil.Db.SubmitChanges();
var host = Util.Host;
var qid = DbUtil.Db.FetchLastQuery().Id;
System.Threading.Tasks.Task.Factory.StartNew(() =>
{
System.Threading.Thread.CurrentThread.Priority = System.Threading.ThreadPriority.BelowNormal;
var Db = new CMSDataContext(Util.GetConnectionString(host));
IEnumerable<int> q = null;
if (tag == -1) // (last query)
q = Db.PeopleQuery(qid).Select(pp => pp.PeopleId);
else
q = from t in Db.TagPeople
where t.Id == tag
select t.PeopleId;
var pids = q.ToList();
var meeting = Db.Meetings.SingleOrDefault(mm => mm.MeetingId == meetingid);
var joindate = meeting.MeetingDate.Value.AddMinutes(-1);
var orgid = meeting.OrganizationId;
foreach (var pid in pids)
{
Db.Dispose();
Db = new CMSDataContext(Util.GetConnectionString(host));
if (addasmembers)
OrganizationMember.InsertOrgMembers(Db,
orgid, pid, MemberTypeCode.Member, joindate, null, false);
Db.RecordAttendance(meetingid, pid, true);
var r = Db.AddToOrgFromTagRuns.Where(mm => mm.Orgid == meetingid).OrderByDescending(mm => mm.Id).First();
r.Processed++;
r.Count = pids.Count;
Db.SubmitChanges();
}
var rr = Db.AddToOrgFromTagRuns.Where(mm => mm.Orgid == meetingid).OrderByDescending(mm => mm.Id).First();
rr.Completed = DateTime.Now;
Db.SubmitChanges();
Db.UpdateMainFellowship(orgid);
});
return Redirect("/AddAttendeesFromTag/Progress/" + meetingid);
}
public ActionResult Start(int tag, int orgid, bool pending = false, bool prospect = false)
{
var runningtotals = new AddToOrgFromTagRun
{
Started = DateTime.Now,
Count = 0,
Processed = 0,
Orgid = orgid
};
DbUtil.Db.AddToOrgFromTagRuns.InsertOnSubmit(runningtotals);
DbUtil.Db.SubmitChanges();
var host = Util.Host;
var qid = DbUtil.Db.FetchLastQuery().Id;
System.Threading.Tasks.Task.Factory.StartNew(() =>
{
Thread.CurrentThread.Priority = ThreadPriority.BelowNormal;
var Db = new CMSDataContext(Util.GetConnectionString(host));
var cul = Db.Setting("Culture", "en-US");
Thread.CurrentThread.CurrentUICulture = new CultureInfo(cul);
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(cul);
IEnumerable<int> q = null;
if (tag == -1) // (last query)
q = Db.PeopleQuery(qid).Select(pp => pp.PeopleId);
else
q = from t in Db.TagPeople
where t.Id == tag
select t.PeopleId;
var pids = q.ToList();
foreach (var pid in pids)
{
Db.Dispose();
Db = new CMSDataContext(Util.GetConnectionString(host));
OrganizationMember.InsertOrgMembers(Db, orgid, pid,
prospect ? MemberTypeCode.Prospect : MemberTypeCode.Member,
DateTime.Now, null, pending);
var r = Db.AddToOrgFromTagRuns.Where(mm => mm.Orgid == orgid).OrderByDescending(mm => mm.Id).First();
r.Processed++;
r.Count = pids.Count;
Db.SubmitChanges();
}
var rr = Db.AddToOrgFromTagRuns.Where(mm => mm.Orgid == orgid).OrderByDescending(mm => mm.Id).First();
rr.Completed = DateTime.Now;
Db.SubmitChanges();
Db.UpdateMainFellowship(orgid);
});
return Redirect("/AddToOrgFromTag/Progress/" + orgid);
}
public ActionResult Upload(string text, bool noupdate)
{
string host = Util.Host;
var runningtotals = new UploadPeopleRun { Started = DateTime.Now, Count = 0, Processed = 0 };
DbUtil.Db.UploadPeopleRuns.InsertOnSubmit(runningtotals);
DbUtil.Db.SubmitChanges();
var pid = Util.UserPeopleId;
var cs = Util.GetConnectionString(host);
Alias.Task.Factory.StartNew(() =>
{
Thread.CurrentThread.Priority = ThreadPriority.Lowest;
var Db = new CMSDataContext(cs);
Db.Host = host;
try
{
var m = new UploadPeopleModel(Db, pid ?? 0, noupdate, cs);
m.DoUpload(text, testing: true);
Db.Dispose();
Db = new CMSDataContext(cs);
Db.Host = host;
runningtotals = new UploadPeopleRun { Started = DateTime.Now, Count = 0, Processed = 0 };
Db.UploadPeopleRuns.InsertOnSubmit(runningtotals);
Db.SubmitChanges();
m = new UploadPeopleModel(Db, pid ?? 0, noupdate, cs);
m.DoUpload(text);
}
catch (Exception ex)
{
Db.Dispose();
Db = new CMSDataContext(cs);
Db.Host = host;
var q = from r in Db.UploadPeopleRuns
where r.Id == Db.UploadPeopleRuns.Max(rr => rr.Id)
select r;
var rt = q.Single();
rt.Error = ex.Message.Truncate(200);
Db.SubmitChanges();
}
});
return Redirect("/UploadPeople/Progress");
}
public static void LogActivity(string activity, string name = null, int? orgid = null, int? pid = null)
{
var db = new CMSDataContext(Util.ConnectionString);
int? uid = Util.UserId;
if (uid == 0)
uid = null;
var a = new ActivityLog
{
ActivityDate = Util.Now,
UserId = uid,
Activity = activity,
Machine = System.Environment.MachineName,
OrgId = orgid,
PeopleId = pid,
};
db.ActivityLogs.InsertOnSubmit(a);
db.SubmitChanges();
db.Dispose();
if (orgid.HasValue)
{
var mru = Util2.MostRecentOrgs;
var i = mru.SingleOrDefault(vv => vv.Id == orgid);
if (i != null)
mru.Remove(i);
mru.Insert(0, new Util2.MostRecentItem() { Id = orgid.Value, Name = name });
if (mru.Count > 5)
mru.RemoveAt(mru.Count - 1);
}
else if (pid.HasValue && pid != Util.UserPeopleId)
{
var mru = Util2.MostRecentPeople;
var i = mru.SingleOrDefault(vv => vv.Id == pid);
if (i != null)
mru.Remove(i);
mru.Insert(0, new Util2.MostRecentItem() { Id = pid.Value, Name = name });
if (mru.Count > 5)
mru.RemoveAt(mru.Count - 1);
}
// else if (qid.HasValue && pid != Util.UserPeopleId)
// {
// var mru = Util2.MostRecentQueries;
// var i = mru.SingleOrDefault(vv => vv.Id == pid);
// if (i != null)
// mru.Remove(i);
// mru.Insert(0, new Util2.MostRecentItem() { Id = pid.Value, Name = name });
// if (mru.Count > 5)
// mru.RemoveAt(mru.Count-1);
// }
}
public void MoveAndDeleteAsync(string text)
{
AsyncManager.OutstandingOperations.Increment();
string host = Util.Host;
ThreadPool.QueueUserWorkItem((e) =>
{
var sb = new StringBuilder();
sb.Append("<h2>done</h2>\n<p><a href='/'>home</a></p>\n");
using (var csv = new CsvReader(new StringReader(text), false, '\t'))
{
while (csv.ReadNextRecord())
{
if (csv.FieldCount != 2)
{
sb.AppendFormat("expected two ids, row {0}<br/>\n", csv[0]);
continue;
}
var fromid = csv[0].ToInt();
var toid = csv[1].ToInt();
var Db = new CMSDataContext(Util.GetConnectionString(host));
var p = Db.LoadPersonById(fromid);
if (p == null)
{
sb.AppendFormat("fromid {0} not found<br/>\n", fromid);
Db.Dispose();
continue;
}
var tp = Db.LoadPersonById(toid);
if (tp == null)
{
sb.AppendFormat("toid {0} not found<br/>\n", toid);
Db.Dispose();
continue;
}
try
{
p.MovePersonStuff(Db, toid);
Db.SubmitChanges();
}
catch (Exception ex)
{
sb.AppendFormat("error on move ({0}, {1}): {2}<br/>\n", fromid, toid, ex.Message);
Db.Dispose();
continue;
}
try
{
Db.PurgePerson(fromid);
sb.AppendFormat("moved ({0}, {1}) successful<br/>\n", fromid, toid);
}
catch (Exception ex)
{
sb.AppendFormat("error on delete ({0}): {1}<br/>\n", fromid, ex.Message);
}
finally
{
Db.Dispose();
}
}
}
AsyncManager.Parameters["results"] = sb.ToString();
AsyncManager.OutstandingOperations.Decrement();
});
}
private static Person PersonFound(CMSDataContext ctx, IQueryable<Person> q)
{
var pid = q.Select(p => p.PeopleId).SingleOrDefault();
ctx.Dispose();
return DbUtil.Db.LoadPersonById(pid);
}
public static Person FindPerson(string first, string last, DateTime? DOB, string email, string phone, out int count)
{
count = 0;
if (!first.HasValue() || !last.HasValue())
return null;
first = first.Trim();
last = last.Trim();
var fone = Util.GetDigits(phone);
var ctx = new CMSDataContext(Util.ConnectionString);
ctx.SetNoLock();
var q = from p in ctx.People
where (p.FirstName == first || p.NickName == first || p.MiddleName == first)
where (p.LastName == last || p.MaidenName == last)
select p;
var list = q.ToList();
count = list.Count;
if (count == 0) // not going to find anything
{
ctx.Dispose();
return null;
}
if (DOB.HasValue && DOB > DateTime.MinValue)
{
var dt = DOB.Value;
if (dt > Util.Now)
dt = dt.AddYears(-100);
var q2 = from p in q
where p.BirthDay == dt.Day && p.BirthMonth == dt.Month && p.BirthYear == dt.Year
select p;
count = q2.Count();
if (count == 1) // use only birthday if there and unique
return PersonFound(ctx, q2);
}
if (email.HasValue())
{
var q2 = from p in q
where p.EmailAddress == email
select p;
count = q2.Count();
if (count == 1)
return PersonFound(ctx, q2);
}
if (phone.HasValue())
{
var q2 = from p in q
where p.CellPhone.Contains(fone) || p.Family.HomePhone.Contains(fone)
select p;
count = q2.Count();
if (count == 1)
return PersonFound(ctx, q2);
}
return null;
}
public EnrollmentTransaction Drop(CMSDataContext Db, DateTime dropdate, bool addToHistory)
{
Db.SubmitChanges();
int ntries = 2;
while (true)
{
try
{
var q = from o in Db.Organizations
where o.OrganizationId == OrganizationId
let count = Db.Attends.Count(a => a.PeopleId == PeopleId
&& a.OrganizationId == OrganizationId
&& (a.MeetingDate < DateTime.Today || a.AttendanceFlag == true))
select new { count, Organization.DaysToIgnoreHistory };
var i = q.Single();
if (!EnrollmentDate.HasValue)
EnrollmentDate = CreatedDate;
var droptrans = new EnrollmentTransaction
{
OrganizationId = OrganizationId,
PeopleId = PeopleId,
MemberTypeId = MemberTypeId,
OrganizationName = Organization.OrganizationName,
TransactionDate = dropdate,
TransactionTypeId = 5,
// drop
CreatedBy = Util.UserId1,
CreatedDate = Util.Now,
Pending = Pending,
AttendancePercentage = AttendPct,
};
Db.EnrollmentTransactions.InsertOnSubmit(droptrans);
Db.OrgMemMemTags.DeleteAllOnSubmit(this.OrgMemMemTags);
Db.OrganizationMembers.DeleteOnSubmit(this);
Db.ExecuteCommand("DELETE FROM dbo.SubRequest WHERE EXISTS(SELECT NULL FROM Attend a WHERE a.AttendId = AttendId AND a.OrganizationId = {0} AND a.MeetingDate > {1} AND a.PeopleId = {2})", OrganizationId, Util.Now, PeopleId);
Db.ExecuteCommand("DELETE dbo.Attend WHERE OrganizationId = {0} AND MeetingDate > {1} AND PeopleId = {2} AND ISNULL(Commitment, 1) = 1", OrganizationId, Util.Now, PeopleId);
return droptrans;
}
catch (SqlException ex)
{
if (ex.Number == 1205)
if (--ntries > 0)
{
Db.Dispose();
System.Threading.Thread.Sleep(500);
continue;
}
throw;
}
}
}
