public static IEnumerable <FundStatusReportDataRow> LoadData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, CC.Data.User user, FundStatusReportFilter filter)
{
var dbData = from cr in db.ViewClientReports
join sra in db.viewSubreportAmounts on cr.SubReportId equals sra.id
join abs in db.AppBudgetServices.Where(f => !f.Agency.AgencyGroup.ExcludeFromReports) on sra.AppBudgetServiceId equals abs.Id
join mr in db.MainReports on sra.MainReportId equals mr.Id
join c in db.Clients on cr.ClientId equals c.Id
select new FundStatusReportDataRow
{
Active = c.DeceasedDate == null && c.LeaveDate == null,
AgencyName = c.Agency.Name,
AgencyId = c.AgencyId,
Amount = cr.Amount ?? sra.EstAmountPerClient,
AppId = mr.AppBudget.AppId,
AppName = mr.AppBudget.App.Name,
BirthDate = c.BirthDate,
ClientId = c.Id,
CurrencyId = mr.AppBudget.App.CurrencyId,
End = mr.End,
FirstName = c.FirstName,
FundId = mr.AppBudget.App.FundId,
FundName = mr.AppBudget.App.Fund.Name,
FundStatusId = c.FundStatusId,
FundStatusName = c.FundStatus.Name,
LastName = c.LastName,
SerId = c.Agency.GroupId,
SerName = c.Agency.AgencyGroup.Name,
ServiceId = abs.ServiceId,
ServiceName = abs.Service.Name,
Start = mr.Start,
Year = System.Data.Objects.SqlClient.SqlFunctions.DatePart("year", mr.Start),
};
if (filter.FundStatusesString.Any())
{
dbData = dbData.Where(f => filter.FundStatusesString.Contains(f.FundStatusId ?? 0));
}
if (filter.Funds.Any())
{
dbData = dbData.Where(f => filter.Funds.Contains(f.FundId ?? 0));
}
if (filter.Year.HasValue)
{
dbData = dbData.Where(f => f.Year == (int)filter.Year.Value);
}
var result = dbData.ToList();
return(result);
}
public void LoadData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, CC.Data.User user)
{
var dbData = db.MainReports
.Where(permissions.MainReportsFilter);
if (this.Filter != null)
{
if (this.Filter.RegionId.HasValue)
{
dbData = dbData.Where(f => f.AppBudget.App.AgencyGroup.Country.RegionId == this.Filter.RegionId);
}
if (this.Filter.CountryId.HasValue)
{
dbData = dbData.Where(f => f.AppBudget.App.AgencyGroup.CountryId == this.Filter.CountryId);
}
if (this.Filter.StateId.HasValue)
{
dbData = dbData.Where(f => f.AppBudget.App.AgencyGroup.StateId == this.Filter.StateId.Value);
}
if (this.Filter.AgencyGroupId.HasValue)
{
dbData = dbData.Where(f => f.AppBudget.App.AgencyGroupId == this.Filter.AgencyGroupId);
}
if (this.Filter.Start.HasValue)
{
dbData = dbData.Where(f => f.End > this.Filter.Start);
}
if (this.Filter.End.HasValue)
{
dbData = dbData.Where(f => f.Start < this.Filter.End);
}
if (this.Filter.StatusId.HasValue)
{
dbData = dbData.Where(f => f.StatusId == (int)this.Filter.StatusId.Value);
}
if (this.Filter.GGOnly)
{
dbData = dbData.Where(f => f.AppBudget.App.Fund.MasterFundId == 73);
}
}
}
public static IEnumerable <LeaveDateDataRow> LoadLeaveDateData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, CC.Data.User user, LeaveDateRemovedFilter filter)
{
var leaveDateHistory = from h in db.viewDatesHistories
where h.TableName == "Clients" && h.FieldName == "LeaveDate"
select h;
var q = from h in leaveDateHistory
where h.UpdateDate > filter.ChangedDate
where h.NewValue == null
join c in db.Clients.Where(permissions.ClientsFilter).Where(f => !f.Agency.AgencyGroup.ExcludeFromReports) on h.ReferenceId equals c.Id
join ch in leaveDateHistory on new { ClientId = c.Id, LeaveDate = c.LeaveDate } equals new { ClientId = ch.ReferenceId, LeaveDate = ch.NewValue } into chGroup
let ch = chGroup.OrderByDescending(f => f.UpdateDate).FirstOrDefault()
join s in
(from cr in db.viewClientReportsEsts
where (cr.ReportDate ?? cr.EstReportDate) >= filter.ChangedDate
join sr in db.SubReports on cr.SubReportId equals sr.Id
select new
{
ClientId = cr.ClientId,
ServiceName = sr.AppBudgetService.Service.ServiceType.Name + sr.AppBudgetService.Service.Name
}).Distinct() on c.Id equals s.ClientId into sg
select new LeaveDateDataRow
{
ClientId = c.Id,
FirstName = c.FirstName,
LastName = c.LastName,
AgencyName = c.Agency.Name,
SerName = c.Agency.AgencyGroup.Name,
LeaveDateEntered = h.UpdateDate,
Services = sg.Select(f => f.ServiceName),
ApprovalStatus = c.ApprovalStatus.Name,
CurrentLeaveDate = c.LeaveDate,
CurrentLeaveDateEntered = (DateTime?)ch.UpdateDate,
};
return(q.ToList());
}
