public ActionResult MeetingsForDateRange(DateTime dt1, DateTime dt2, OrgSearchModel m)
{
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
var q = cn.Query("MeetingsForDateRange", new
{
orgs,
startdate = dt1,
enddate = dt2,
}, commandType: CommandType.StoredProcedure, commandTimeout: 600);
var entity = (IDictionary <string, object>)q.First();
var cols = entity.Keys.Cast <string>().ToList();
var ep = new ExcelPackage();
var ws = ep.Workbook.Worksheets.Add("Sheet1");
int row = 1;
for (var i = 0; i < cols.Count; i++)
{
ws.Cells[row, i + 1].Value = cols[i];
}
row++;
foreach (var r in q)
{
var rr = (IDictionary <string, object>)r;
for (var i = 0; i < cols.Count; i++)
{
ws.Cells[row, i + 1].Value = rr[cols[i]];
}
row++;
}
ws.Cells[ws.Dimension.Address].AutoFitColumns();
return(new EpplusResult(ep, "MeetingsForDateRange.xlsx"));
}
public ActionResult ApplyType(int id, OrgSearchModel m)
{
var t = (id == -1 ? (int?)null : id);
if (t == 0)
{
return(Content(""));
}
var ot = CurrentDatabase.OrganizationTypes.SingleOrDefault(tt => tt.Id == id);
if (t.HasValue || ot != null)
{
var q = from o in CurrentDatabase.Organizations
join os in m.FetchOrgs() on o.OrganizationId equals os.OrganizationId
select o;
foreach (var o in q)
{
o.OrganizationTypeId = t;
}
}
else
{
return(Content("error: missing type"));
}
CurrentDatabase.SubmitChanges();
return(Content("ok"));
}
public ActionResult MeetingsForMonth(DateTime dt1, OrgSearchModel m)
{
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
ViewBag.Month = dt1.ToString("MMMM yyyy");
dt1 = new DateTime(dt1.Year, dt1.Month, 1);
var dt2 = dt1.AddMonths(1).AddDays(-1);
var hasmeetings = DbUtil.Db.MeetingsDataForDateRange(orgs, dt1, dt2).AsEnumerable().Any();
if (!hasmeetings)
{
return(RedirectShowError("No meetings to show"));
}
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
var q = cn.Query("MeetingsForDateRange", new
{
orgs,
startdate = dt1,
enddate = dt2,
}, commandType: CommandType.StoredProcedure, commandTimeout: 600);
return(View(q));
}
public ActionResult MeetingsForDateRange(DateTime dt1, DateTime dt2, OrgSearchModel m)
{
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
var q = cn.Query("MeetingsForDateRange", new
{
orgs,
startdate = dt1,
enddate = dt2,
}, commandType: CommandType.StoredProcedure, commandTimeout: 600);
var entity = (IDictionary<string, object>) q.First();
var cols = entity.Keys.Cast<string>().ToList();
var ep = new ExcelPackage();
var ws = ep.Workbook.Worksheets.Add("Sheet1");
int row = 1;
for (var i = 0; i < cols.Count; i++)
ws.Cells[row, i+1].Value = cols[i];
row++;
foreach (var r in q)
{
var rr = (IDictionary<string, object>) r;
for (var i = 0; i < cols.Count; i++)
ws.Cells[row, i+1].Value = rr[cols[i]];
row++;
}
ws.Cells[ws.Dimension.Address].AutoFitColumns();
return new EpplusResult(ep, "MeetingsForDateRange.xlsx");
}
public ActionResult MakeChildrenOf(int id, OrgSearchModel m)
{
var t = (id == -1 ? (int?)null : id);
if (t == 0)
{
return(Content(""));
}
var org = CurrentDatabase.LoadOrganizationById(id);
if (t.HasValue || org != null)
{
var q = from o in CurrentDatabase.Organizations
join os in m.FetchOrgs() on o.OrganizationId equals os.OrganizationId
where o.OrganizationId != id
select o;
foreach (var o in q)
{
o.ParentOrgId = t;
}
}
else
{
return(Content("error: missing type"));
}
CurrentDatabase.SubmitChanges();
return(Content("ok"));
}
public static IEnumerable <MemberInfo> List(OrgSearchModel model, string na = "", bool usecurrenttag = false)
{
var orgs = model.FetchOrgs();
var q = from m in DbUtil.Db.OrganizationMembers
join o in orgs on m.OrganizationId equals o.OrganizationId
select m;
if (usecurrenttag)
{
var tagid = DbUtil.Db.TagCurrent().Id;
q = from m in q
where m.Person.Tags.Any(tt => tt.Id == tagid)
select m;
}
var q2 = from m in q
where m.Person.LastName.StartsWith(na)
orderby m.Person.Name2
select new MemberInfo
{
Name = m.Person.Name2,
Id = m.PeopleId,
Organization = m.Organization.OrganizationName,
Location = m.Organization.Location,
MemberType = m.MemberType.Description,
};
return(q2);
}
public ActionResult SqlReport(OrgSearchModel m, string report, DateTime?dt1 = null, DateTime?dt2 = null)
{
try
{
var orgs = m.FetchOrgs();
var oids = string.Join(",", orgs.Select(oo => oo.OrganizationId));
ViewBag.ExcelUrl = $"/OrgSearch/SqlReportExcel/{report}";
ViewBag.DisplayName = report.SpaceCamelCase();
ViewBag.OrgIds = oids;
ViewBag.dt1 = dt1;
ViewBag.dt2 = dt2;
var content = CurrentDatabase.ContentOfTypeSql(report);
if (content.Contains("pagebreak", ignoreCase: true))
{
var p = m.GetSqlParameters(oids, dt1, dt2, content);
ViewBag.Results = PythonModel.PageBreakTables(CurrentDatabase, content, p);
return(View());
}
ViewBag.Results = m.SqlTable(report, oids, dt1, dt2);
return(View());
}
catch (Exception ex)
{
return(Message(ex));
}
}
public ActionResult CreateMeetings(DateTime dt, OrgSearchModel model)
{
foreach (var o in model.FetchOrgs())
{
var mt = DbUtil.Db.Meetings.SingleOrDefault(m => m.MeetingDate == dt &&
m.OrganizationId == o.OrganizationId);
if (mt != null)
{
continue;
}
mt = new CmsData.Meeting
{
CreatedDate = Util.Now,
CreatedBy = Util.UserId1,
OrganizationId = o.OrganizationId,
Location = o.Location,
MeetingDate = dt,
};
DbUtil.Db.Meetings.InsertOnSubmit(mt);
DbUtil.Db.SubmitChanges();
}
DbUtil.LogActivity("Creating new meetings from OrgSearch");
return(Content("done"));
}
public ActionResult MeetingsForMonth(string dt1, OrgSearchModel m)
{
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
var d1 = dt1.ToDate();
if (!d1.HasValue)
{
throw new ArgumentException($"invalid date: {dt1}", nameof(dt1));
}
ViewBag.Month = d1.Value.ToString("MMMM yyyy");
d1 = new DateTime(d1.Value.Year, d1.Value.Month, 1);
var dt2 = d1.Value.AddMonths(1).AddDays(-1);
var hasmeetings = CurrentDatabase.MeetingsDataForDateRange(orgs, d1, dt2).AsEnumerable().Any();
if (!hasmeetings)
{
return(RedirectShowError("No meetings to show"));
}
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
var q = cn.Query("MeetingsForDateRange", new
{
orgs,
startdate = dt1,
enddate = dt2,
}, commandType: CommandType.StoredProcedure, commandTimeout: 600);
return(View(q));
}
public ActionResult RepairTransactions(OrgSearchModel m)
{
foreach (var oid in m.FetchOrgs().Select(oo => oo.OrganizationId))
{
DbUtil.Db.PopulateComputedEnrollmentTransactions(oid);
}
return(new EmptyResult());
}
public ActionResult ApplyType(int id, OrgSearchModel m)
{
foreach (var o in m.FetchOrgs())
{
o.OrganizationTypeId = id == -1 ? (int?)null : id;
}
DbUtil.Db.SubmitChanges();
return(View("Results", m));
}
public ActionResult OrganizationStructure(bool?active, OrgSearchModel m)
{
var orgs = m.FetchOrgs();
var q = from os in CurrentDatabase.ViewOrganizationStructures
join o in orgs on os.OrgId equals o.OrganizationId
select os;
return(View(q.OrderBy(oo => oo.Program).ThenBy(oo => oo.Division).ThenBy(oo => oo.Organization)));
}
public static List<MissionTripTotal> List(OrgSearchModel m)
{
var orgids = string.Join(",", m.FetchOrgs().Select(mm => mm.OrganizationId));
var q = from t in DbUtil.Db.ViewMissionTripTotals
join i in DbUtil.Db.SplitInts(orgids) on t.OrganizationId equals i.ValueX
orderby t.OrganizationId, t.SortOrder
select t;
return q.ToList();
}
public ActionResult PasteSettings(OrgSearchModel m)
{
var frorg = (int)Session["OrgCopySettings"];
foreach (var o in m.FetchOrgs())
{
o.CopySettings(DbUtil.Db, frorg);
}
return(new EmptyResult());
}
public ActionResult RecentAbsents(OrgSearchModel m)
{
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
var q = cn.Query("RecentAbsentsSP2", new { orgs }, commandType: CommandType.StoredProcedure, commandTimeout: 600);
return(View(q));
}
public ActionResult Index(int id, OrgSearchModel osm)
{
var model = new RepairTransactionsOrgs(id);
var orgIds = osm.FetchOrgs().Select(oo => oo.OrganizationId).ToArray();
model.Orgs = string.Join(",", orgIds);
model.Count = orgIds.Length;
model.RemoveExistingLop(DbUtil.Db, id, RepairTransactionsOrgs.Op);
return(View(model));
}
private IEnumerable <OrgInfo> ReportList()
{
var orgs = OrgSearchModel == null
? OrgSearchModel.FetchOrgs(meeting?.OrganizationId ?? Filter?.Id ?? 0)
: OrgSearchModel.FetchOrgs();
var q = from o in orgs
orderby o.Division, o.OrganizationName
select new OrgInfo
{
OrgId = o.OrganizationId,
Division = o.Division,
Name = o.OrganizationName,
Teacher = o.LeaderName,
Location = o.Location,
Groups = NewMeetingInfo.GroupFilterPrefix
};
return(q);
}
public ActionResult CreateMeetings(DateTime dt, bool noautoabsents, OrgSearchModel model)
{
var orgIds = model.FetchOrgs().Select(oo => oo.OrganizationId).ToList();
foreach (var oid in orgIds)
{
Meeting.FetchOrCreateMeeting(CurrentDatabase, oid, dt, noautoabsents);
}
DbUtil.LogActivity("Creating new meetings from OrgSearch");
return(Content("done"));
}
public ActionResult OrgDayStats(DateTime?dt, OrgSearchModel m)
{
if (!dt.HasValue)
{
dt = ChurchAttendanceModel.MostRecentAttendedSunday();
}
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
var q = DbUtil.Db.OrgDayStats(orgs, dt);
return(q.ToDataTable().ToExcel("OrgDatStats.xlsx"));
}
public ActionResult PasteSettings(OrgSearchModel m)
{
var frorg = (int)Session["OrgCopySettings"];
var orgs = from os in m.FetchOrgs()
join o in DbUtil.Db.Organizations on os.OrganizationId equals o.OrganizationId
select o;
foreach (var o in orgs)
{
o.CopySettings(DbUtil.Db, frorg);
}
return(new EmptyResult());
}
public ActionResult PasteSettings(OrgSearchModel m)
{
var frorg = Util.OrgCopySettings.ToInt();
var orgs = from os in m.FetchOrgs()
join o in CurrentDatabase.Organizations on os.OrganizationId equals o.OrganizationId
select o;
foreach (var o in orgs)
{
o.CopySettings(CurrentDatabase, frorg);
}
return(new EmptyResult());
}
public RepairTransactionsOrgs(OrgSearchModel m)
{
Id = Util.UserPeopleId ?? 0;
var q = (from o in m.FetchOrgs()
select new OrgInfo
{
Id = o.OrganizationId,
Name = o.OrganizationName,
Count = o.MemberCount ?? 0
}).ToList();
Count = q.Count();
Orgs = JsonConvert.SerializeObject(q);
}
public ActionResult ShirtSizes(OrgSearchModel m)
{
var orgs = m.FetchOrgs();
var q = from om in CurrentDatabase.OrganizationMembers
join o in orgs on om.OrganizationId equals o.OrganizationId
group 1 by om.ShirtSize
into g
select new ShirtSizeInfo
{
Size = g.Key,
Count = g.Count(),
};
return(View(q));
}
public OrgSearchDrop(OrgSearchModel m)
{
QueryId = Guid.NewGuid();
var q = (from o in m.FetchOrgs()
select new OrgInfo
{
Id = o.OrganizationId,
Name = o.OrganizationName,
Count = o.MemberCount ?? 0
}).ToList();
Count = q.Sum(mm => mm.Count);
OrgCount = q.Count();
Orgs = JsonConvert.SerializeObject(q);
}
public ActionResult MovePendingToMember(OrgSearchModel m)
{
var orgids = string.Join(",", m.FetchOrgs().Select(mm => mm.OrganizationId));
var i = CurrentDatabase.Connection.ExecuteScalar($@"
UPDATE dbo.OrganizationMembers
SET Pending = NULL
FROM dbo.OrganizationMembers om
JOIN dbo.SplitInts('{orgids}') i ON i.Value = om.OrganizationId
WHERE om.Pending = 1
SELECT @@ROWCOUNT
");
return(Content($"changed {i} people"));
}
public ActionResult SqlReport(OrgSearchModel m, string report)
{
try
{
var orgs = m.FetchOrgs();
var oids = string.Join(",", orgs.Select(oo => oo.OrganizationId));
ViewBag.ExcelUrl = $"/OrgSearch/SqlReportExcel/{report}";
ViewBag.DisplayName = report.SpaceCamelCase();
ViewBag.Results = m.SqlTable(report, oids);
ViewBag.OrgIds = oids;
return(View());
}
catch (Exception ex)
{
return(Message(ex));
}
}
private IEnumerable <OrgInfo> ReportList()
{
var orgs = orgid.HasValue
? OrgSearchModel.FetchOrgs(orgid.Value)
: model.FetchOrgs();
var q = from o in orgs
select new OrgInfo
{
OrgId = o.OrganizationId,
Division = o.Division,
Name = o.OrganizationName,
Teacher = o.LeaderName,
Location = o.Location,
};
return(q);
}
public ActionResult ShirtSizes(string org, OrgSearchModel m)
{
int?orgid = org == "curr" ? Util2.CurrentOrgId : null;
IQueryable <Organization> orgs = m.FetchOrgs();
IQueryable <ShirtSizeInfo> q = from om in DbUtil.Db.OrganizationMembers
join o in orgs on om.OrganizationId equals o.OrganizationId
where o.OrganizationId == orgid || (orgid ?? 0) == 0
group 1 by om.ShirtSize
into g
select new ShirtSizeInfo
{
Size = g.Key,
Count = g.Count(),
};
return(View(q));
}
public ActionResult MeetingsForMonth(DateTime dt1, OrgSearchModel m)
{
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
ViewBag.Month = dt1.ToString("MMMM yyyy");
dt1 = new DateTime(dt1.Year, dt1.Month, 1);
var dt2 = dt1.AddMonths(1).AddDays(-1);
var q = cn.Query("MeetingsForDateRange", new
{
orgs,
startdate = dt1,
enddate = dt2,
}, commandType: CommandType.StoredProcedure, commandTimeout: 600);
return(View(q));
}
public ActionResult ShirtSizes(string org, OrgSearchModel m)
{
var orgid = org == "curr" ? DbUtil.Db.CurrentOrg.Id : null;
var orgs = orgid.HasValue
? OrgSearchModel.FetchOrgs(orgid.Value)
: m.FetchOrgs();
var q = from om in DbUtil.Db.OrganizationMembers
join o in orgs on om.OrganizationId equals o.OrganizationId
where o.OrganizationId == orgid || (orgid ?? 0) == 0
group 1 by om.ShirtSize
into g
select new ShirtSizeInfo
{
Size = g.Key,
Count = g.Count(),
};
return(View(q));
}
public ActionResult ApplyType(int id, OrgSearchModel m)
{
int?t = (id == -1 ? (int?)null : id);
if (t == 0)
{
return(Content(""));
}
var ot = DbUtil.Db.OrganizationTypes.SingleOrDefault(tt => tt.Id == id);
if (t.HasValue || ot != null)
{
foreach (var o in m.FetchOrgs())
{
o.OrganizationTypeId = t;
}
}
else
{
return(Content("error: missing type"));
}
DbUtil.Db.SubmitChanges();
return(Content("ok"));
}
public ActionResult ShirtSizes(string org, OrgSearchModel m)
{
var orgid = org == "curr" ? DbUtil.Db.CurrentOrg.Id : null;
var orgs = orgid.HasValue
? OrgSearchModel.FetchOrgs(orgid.Value)
: m.FetchOrgs();
var q = from om in DbUtil.Db.OrganizationMembers
join o in orgs on om.OrganizationId equals o.OrganizationId
where o.OrganizationId == orgid || (orgid ?? 0) == 0
group 1 by om.ShirtSize
into g
select new ShirtSizeInfo
{
Size = g.Key,
Count = g.Count(),
};
return View(q);
}
public static EpplusResult List(OrgSearchModel m)
{
var orgids = string.Join(",", m.FetchOrgs().Select(mm => mm.OrganizationId));
return Result(SenderGifts(orgids));
}
public ActionResult MeetingsForMonth(DateTime dt1, OrgSearchModel m)
{
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
ViewBag.Month = dt1.ToString("MMMM yyyy");
dt1 = new DateTime(dt1.Year, dt1.Month, 1);
var dt2 = dt1.AddMonths(1).AddDays(-1);
var hasmeetings = DbUtil.Db.MeetingsDataForDateRange(orgs, dt1, dt2).AsEnumerable().Any();
if (!hasmeetings)
return RedirectShowError("No meetings to show");
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
var q = cn.Query("MeetingsForDateRange", new
{
orgs,
startdate = dt1,
enddate = dt2,
}, commandType: CommandType.StoredProcedure, commandTimeout: 600);
return View(q);
}
public ActionResult OrgIds(OrgSearchModel m)
{
var orgs = m.FetchOrgs();
return(Content(string.Join(",", orgs.Select(oo => oo.OrganizationId))));
}
public ActionResult RecentAbsents(OrgSearchModel m)
{
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
var q = cn.Query("RecentAbsentsSP2", new { orgs }, commandType: CommandType.StoredProcedure, commandTimeout: 600);
return View(q);
}
public ActionResult OrgDayStats(DateTime? dt, OrgSearchModel m)
{
if(!dt.HasValue)
dt = ChurchAttendanceModel.MostRecentAttendedSunday();
var orgs = string.Join(",", m.FetchOrgs().Select(oo => oo.OrganizationId));
var q = DbUtil.Db.OrgDayStats(orgs, dt);
return q.ToDataTable().ToExcel("OrgDatStats.xlsx");
}
