public override void LoadData() { base.LoadData(); this.StatusEditable = this.User.RoleId == (int)FixedRoles.Admin; this.CanAddAgencyRemarks = this.User.RoleId == (int)FixedRoles.Admin || (MainReport.EditableStatuses.Contains(this.Status) && (FixedRoles.AgencyUser | FixedRoles.Ser | FixedRoles.AgencyUserAndReviewer | FixedRoles.SerAndReviewer).HasFlag((FixedRoles)this.User.RoleId)); this.CanAddPoRemarks = this.User.RoleId == (int)FixedRoles.Admin || ( (this.Status == MainReport.Statuses.AwaitingProgramOfficerApproval || this.Status == MainReport.Statuses.AwaitingProgramAssistantApproval) && (FixedRoles.GlobalOfficer | FixedRoles.RegionOfficer | FixedRoles.RegionAssistant).HasFlag((FixedRoles)this.User.RoleId)); using (var db = new ccEntities()) { var subreports = db.SubReports.Where(this.Permissions.SubReportsFilter); var prevStatus = db.MainReportStatusAudits.Where(f => f.MainReportId == this.Id).OrderByDescending(f => f.StatusChangeDate).FirstOrDefault(); if (prevStatus != null) { this.PrevStatusId = prevStatus.OldStatusId; if (this.PoApprovalModel != null) { this.PoApprovalModel.PrevMainReportStatus = this.PrevStatus; } if (this.PaApprovalModel != null) { this.PaApprovalModel.PrevMainReportStatus = this.PrevStatus; } } var SerNameFE = (from cr in db.ClientReports join sbr in db.SubReports on cr.SubReportId equals sbr.Id join abs in db.AppBudgetServices on sbr.AppBudgetServiceId equals abs.Id join ser in db.Services on abs.ServiceId equals ser.Id where sbr.MainReportId == this.Id && ser.Id == 484//454 //&& cr.Amount != null select cr.Amount).Sum(); if (SerNameFE != 0 && SerNameFE != null) //FE there { var q = from agency in db.Agencies.Where(Permissions.AgencyFilter) where agency.GroupId == this.AgencyGroupId join a in (from c in ( from abs in db.AppBudgetServices where abs.AppBudgetId == this.AppBudgetId join sr in db.SubReports.Where(f => f.MainReportId == this.Id) on abs.Id equals sr.AppBudgetServiceId into srg from sr in srg.DefaultIfEmpty() select new { AgencyId = abs.AgencyId, Amount = sr.Amount, CcGrant = abs.CcGrant, MatchExp = sr.MatchingSum }) group c by c.AgencyId into eg select new { AgencyId = eg.Key, Amount = eg.Sum(f => f.Amount), Match = eg.Sum(f => f.MatchExp), CcGrant = eg.Sum(f => f.CcGrant) }) on agency.Id equals a.AgencyId into ag from a in ag.DefaultIfEmpty() join b in ( from sra in db.viewSubreportAmounts join appbs in db.AppBudgetServices on sra.AppBudgetServiceId equals appbs.Id where appbs.AppBudgetId == this.AppBudgetId join mr in db.MainReports.Where(MainReport.CurrentOrSubmitted(Id)) on sra.MainReportId equals mr.Id where mr.Start <= this.Start where sra.MainReportId == this.Id group sra by appbs.AgencyId into g select new { AgencyId = g.Key, Amount = g.Sum(f => f.Amount), MatchExp = g.Sum(f => f.MatchingSum) }) on a.AgencyId equals b.AgencyId into bg from b in bg.DefaultIfEmpty() select new MainReportDetailsModel.AgnencyTotalsClass() { AgencyId = agency.Id, AgencyName = agency.Name, //CcExp = (decimal?)a.Amount, //+ (decimal?)b.Amount , //CcExpFE = (decimal?)a.Amount + (decimal?)b.Amount, CcExp = (decimal?)b.Amount, //+ (decimal?)a.Amount, YtdMatchExp = (decimal?)b.MatchExp, YtdCcExp = (decimal?)b.Amount, CcGrant = (decimal?)a.CcGrant, CurrencyId = this.CurrencyId }; var ytdServicesQuery = (from appb in db.AppBudgets where appb.Id == this.AppBudgetId from appbs in appb.AppBudgetServices join sra in db.viewSubreportAmounts on appbs.Id equals sra.AppBudgetServiceId join mr in db.MainReports.Where(MainReport.CurrentOrSubmitted(Id)) on sra.MainReportId equals mr.Id where mr.Start <= this.Start select new YtdServicesQueryRow { ServiceId = appbs.ServiceId, ServiceTypeId = appbs.Service.TypeId, ReportingMethodId = appbs.Service.ReportingMethodId, Amount = sra.Amount, Quantity = sra.Quantity }); var ytdServiceTypesQuery = (from a in (from sra in ytdServicesQuery group sra by sra.ServiceTypeId into srag select new { ServiceTypeId = srag.Key, Amount = srag.Sum(f => f.Amount), Quantity = srag.Sum(f => f.Quantity) }) join st in db.ServiceTypes on a.ServiceTypeId equals st.Id select new { ServiceTypeId = st.Id, ServiceTypeName = st.Name, Amount = a.Amount, Quantity = a.Quantity ?? 1 }).ToList(); var ytdAppQuery = (from appb in db.AppBudgets where appb.Id == this.AppBudgetId from appbs in appb.AppBudgetServices join sra in db.viewSubreportAmounts on appbs.Id equals sra.AppBudgetServiceId join mr in db.MainReports on sra.MainReportId equals mr.Id where mr.Start <= this.Start select new YtdServicesQueryRow { ServiceId = appbs.ServiceId, ServiceTypeId = appbs.Service.TypeId, ReportingMethodId = appbs.Service.ReportingMethodId, Amount = sra.Amount, AppMatchBal = (decimal?)sra.MatchingSum, Quantity = sra.Quantity }); var ytdTotalReportedAppQuery = (from a in (from sra in ytdAppQuery group sra by sra.ServiceTypeId into srag select new { ServiceTypeId = srag.Key, Amount = srag.Sum(f => f.Amount), AppMatchBal = srag.Sum(f => f.AppMatchBal), Quantity = srag.Sum(f => f.Quantity) }) join st in db.ServiceTypes on a.ServiceTypeId equals st.Id select new { ServiceTypeId = st.Id, ServiceTypeName = st.Name, Amount = a.Amount, AppMatchBal = a.AppMatchBal, Quantity = a.Quantity ?? 1 }).ToList(); //HcYtdPercentage = ytdServiceTypesQuery.Where(f => f.ServiceTypeId == (int)Service.ServiceTypes.Homecare).Sum(f => f.Amount) / total, // CcExp = CcExp + ytdServicesQuery.y decimal?total = ytdServiceTypesQuery.Sum(f => f.Amount); if (total == 0) { total = null; } decimal?totalApp = total; decimal?totalAppMatchBal = ytdTotalReportedAppQuery.Sum(f => f.AppMatchBal); if (totalAppMatchBal == 0) { totalAppMatchBal = null; } this.Totals = new TotalsClass { AppAmount = this.AppAmount, AppMatch = this.AppMatch, CurrencyId = this.CurrencyId, AgencySubTotals = q.ToList(), HcYtdPercentage = ytdServiceTypesQuery.Where(f => f.ServiceTypeId == (int)Service.ServiceTypes.Homecare).Sum(f => f.Amount) / total, AoYtdPercentage = ytdServiceTypesQuery.Where(f => f.ServiceTypeId == (int)Service.ServiceTypes.AdministrativeOverhead).Sum(f => f.Amount) / total, TotalReportedApp = totalApp, TotalReportedAppMatchBalance = totalAppMatchBal, }; this.ReimbursementCosts = GetAvgReimrusement(ytdServicesQuery); } else { var q = from agency in db.Agencies.Where(Permissions.AgencyFilter) where agency.GroupId == this.AgencyGroupId join a in (from c in ( from abs in db.AppBudgetServices where abs.AppBudgetId == this.AppBudgetId join sr in db.SubReports.Where(f => f.MainReportId == this.Id) on abs.Id equals sr.AppBudgetServiceId into srg from sr in srg.DefaultIfEmpty() select new { AgencyId = abs.AgencyId, Amount = sr.Amount, CcGrant = abs.CcGrant, MatchExp = sr.MatchingSum }) group c by c.AgencyId into eg select new { AgencyId = eg.Key, Amount = eg.Sum(f => f.Amount), Match = eg.Sum(f => f.MatchExp), CcGrant = eg.Sum(f => f.CcGrant) }) on agency.Id equals a.AgencyId into ag from a in ag.DefaultIfEmpty() join b in ( from sra in db.viewSubreportAmounts join appbs in db.AppBudgetServices on sra.AppBudgetServiceId equals appbs.Id where appbs.AppBudgetId == this.AppBudgetId join mr in db.MainReports.Where(MainReport.CurrentOrSubmitted(Id)) on sra.MainReportId equals mr.Id where mr.Start <= this.Start where sra.MainReportId == this.Id //LenaK //join sbr in db.SubReports on sra.id equals sbr.Id group sra by appbs.AgencyId into g select new { AgencyId = g.Key, Amount = g.Sum(f => f.Amount), MatchExp = g.Sum(f => f.MatchingSum) }) on a.AgencyId equals b.AgencyId into bg from b in bg.DefaultIfEmpty() select new MainReportDetailsModel.AgnencyTotalsClass() { AgencyId = agency.Id, AgencyName = agency.Name, //CcExp = (decimal?)a.Amount, //+ (decimal?)b.Amount , // CcExpFE = (decimal?)a.Amount + (decimal?)b.Amount, CcExp = (decimal?)a.Amount, YtdMatchExp = (decimal?)b.MatchExp, YtdCcExp = (decimal?)b.Amount, CcGrant = (decimal?)a.CcGrant, CurrencyId = this.CurrencyId }; var ytdServicesQuery = (from appb in db.AppBudgets where appb.Id == this.AppBudgetId from appbs in appb.AppBudgetServices join sra in db.viewSubreportAmounts on appbs.Id equals sra.AppBudgetServiceId join mr in db.MainReports.Where(MainReport.CurrentOrSubmitted(Id)) on sra.MainReportId equals mr.Id where mr.Start <= this.Start select new YtdServicesQueryRow { ServiceId = appbs.ServiceId, ServiceTypeId = appbs.Service.TypeId, ReportingMethodId = appbs.Service.ReportingMethodId, Amount = sra.Amount, Quantity = sra.Quantity }); var ytdServiceTypesQuery = (from a in (from sra in ytdServicesQuery group sra by sra.ServiceTypeId into srag select new { ServiceTypeId = srag.Key, Amount = srag.Sum(f => f.Amount), Quantity = srag.Sum(f => f.Quantity) }) join st in db.ServiceTypes on a.ServiceTypeId equals st.Id select new { ServiceTypeId = st.Id, ServiceTypeName = st.Name, Amount = a.Amount, Quantity = a.Quantity ?? 1 }).ToList(); var ytdAppQuery = (from appb in db.AppBudgets where appb.Id == this.AppBudgetId from appbs in appb.AppBudgetServices join sra in db.viewSubreportAmounts on appbs.Id equals sra.AppBudgetServiceId join mr in db.MainReports on sra.MainReportId equals mr.Id where mr.Start <= this.Start select new YtdServicesQueryRow { ServiceId = appbs.ServiceId, ServiceTypeId = appbs.Service.TypeId, ReportingMethodId = appbs.Service.ReportingMethodId, Amount = sra.Amount, AppMatchBal = (decimal?)sra.MatchingSum, Quantity = sra.Quantity }); var ytdTotalReportedAppQuery = (from a in (from sra in ytdAppQuery group sra by sra.ServiceTypeId into srag select new { ServiceTypeId = srag.Key, Amount = srag.Sum(f => f.Amount), AppMatchBal = srag.Sum(f => f.AppMatchBal), Quantity = srag.Sum(f => f.Quantity) }) join st in db.ServiceTypes on a.ServiceTypeId equals st.Id select new { ServiceTypeId = st.Id, ServiceTypeName = st.Name, Amount = a.Amount, AppMatchBal = a.AppMatchBal, Quantity = a.Quantity ?? 1 }).ToList(); //HcYtdPercentage = ytdServiceTypesQuery.Where(f => f.ServiceTypeId == (int)Service.ServiceTypes.Homecare).Sum(f => f.Amount) / total, // CcExp = CcExp + ytdServicesQuery.y decimal?total = ytdServiceTypesQuery.Sum(f => f.Amount); decimal?totalApp = ytdTotalReportedAppQuery.Sum(f => f.Amount); decimal?totalAppMatchBal = ytdTotalReportedAppQuery.Sum(f => f.AppMatchBal); if (total == 0) { total = null; } if (totalAppMatchBal == 0) { totalAppMatchBal = null; } this.Totals = new TotalsClass { AppAmount = this.AppAmount, AppMatch = this.AppMatch, CurrencyId = this.CurrencyId, AgencySubTotals = q.ToList(), HcYtdPercentage = ytdServiceTypesQuery.Where(f => f.ServiceTypeId == (int)Service.ServiceTypes.Homecare).Sum(f => f.Amount) / total, AoYtdPercentage = ytdServiceTypesQuery.Where(f => f.ServiceTypeId == (int)Service.ServiceTypes.AdministrativeOverhead).Sum(f => f.Amount) / total, TotalReportedApp = totalApp, TotalReportedAppMatchBalance = totalAppMatchBal, }; this.ReimbursementCosts = GetAvgReimrusement(ytdServicesQuery); } if (this.PoApprovalModel != null || this.PaApprovalModel != null) { var appbudgetServices = from ab in db.AppBudgetServices.Where(this.Permissions.AppBudgetServicesFilter) join mr in db.MainReports on ab.AppBudgetId equals mr.AppBudgetId where mr.Id == this.Id select ab; var totalReported = (from a in appbudgetServices join v in db.viewSubreportAmounts on a.Id equals v.AppBudgetServiceId join mr in db.MainReports.Where(this.Permissions.MainReportsFilter).Where(MainReport.Submitted) on v.MainReportId equals mr.Id group v by a.AppBudgetId into vg select vg.Sum(f => f.Amount) ).SingleOrDefault(); if (this.PaApprovalModel != null) { this.PaApprovalModel.CancellationAmount = this.AppAmount - totalReported; this.PaApprovalModel.CurrencyId = this.CurrencyId; this.PaApprovalModel.LastReport = this.LastReport; } else { this.PoApprovalModel.CancellationAmount = this.AppAmount - totalReported; this.PoApprovalModel.CurrencyId = this.CurrencyId; this.PoApprovalModel.LastReport = this.LastReport; } } } }
public static HseapSummaryModel HseapDetailedData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, int id) { var result = db.MainReports.Where(permissions.MainReportsFilter) .Select(f => new HseapSummaryModel { Id = f.Id, Start = f.Start, End = f.End, AppId = f.AppBudget.AppId, }) .SingleOrDefault(f => f.Id == id); if (result == null) { throw new ArgumentException("Main report not found"); } var mrs = from sr in db.SubReports.Where(permissions.SubReportsFilter) where sr.MainReportId == result.Id join er in db.EmergencyReports.Where(permissions.EmergencyReportsFilter) on sr.Id equals er.SubReportId group er by new { MainReportId = sr.MainReportId, er.TypeId } into erg select new { MainReportId = erg.Key.MainReportId, TypeId = erg.Key.TypeId, ClientsCount = erg.Select(f => f.ClientId).Distinct().Count(), GrantsCount = erg.Count(), TotalAmount = ((decimal?)erg.Sum(f => f.Total ?? 0) ?? 0) }; var apps = from mr in db.MainReports.Where(permissions.MainReportsFilter).Where(MainReport.CurrentOrSubmitted(id)) where mr.AppBudget.AppId == result.AppId join sr in db.SubReports.Where(permissions.SubReportsFilter) on mr.Id equals sr.MainReportId join er in db.EmergencyReports.Where(permissions.EmergencyReportsFilter) on sr.Id equals er.SubReportId group er by new { AppId = mr.AppBudget.AppId, TypeId = er.TypeId } into erg select new { AppId = erg.Key.AppId, TypeId = erg.Key.TypeId, ClientsCount = erg.Select(f => f.ClientId).Distinct().Count(), GrantsCount = erg.Count(), TotalAmount = ((decimal?)erg.Sum(f => f.Total ?? 0) ?? 0) }; var q = from t in db.EmergencyReportTypes join a in mrs on t.Id equals a.TypeId into mrsg from a in mrsg.DefaultIfEmpty() join b in apps on t.Id equals b.TypeId into appsg from b in appsg.DefaultIfEmpty() select new DataRow { TypeId = t.Id, TypeName = t.Name, TypeDescription = t.Description, ReportSummary = new DataRowSummary { ClientsCount = ((int?)a.ClientsCount) ?? 0, GrantsCount = ((int?)a.GrantsCount) ?? 0, TotalAmount = ((decimal?)a.TotalAmount) ?? 0 }, AppSummary = new DataRowSummary { ClientsCount = ((int?)b.ClientsCount) ?? 0, GrantsCount = ((int?)b.GrantsCount) ?? 0, TotalAmount = ((decimal?)b.TotalAmount) ?? 0 } }; result.Data = q.ToList(); var mrst = from sr in db.SubReports.Where(permissions.SubReportsFilter) where sr.MainReportId == result.Id join er in db.EmergencyReports.Where(permissions.EmergencyReportsFilter) on sr.Id equals er.SubReportId group er by new { MainReportId = sr.MainReportId } into erg select new { MainReportId = erg.Key.MainReportId, ClientsCount = erg.Select(f => f.ClientId).Distinct().Count(), GrantsCount = erg.Count(), TotalAmount = ((decimal?)erg.Sum(f => f.Total ?? 0) ?? 0) }; var appst = from mr in db.MainReports.Where(permissions.MainReportsFilter).Where(MainReport.CurrentOrSubmitted(id)) where mr.AppBudget.AppId == result.AppId join sr in db.SubReports.Where(permissions.SubReportsFilter) on mr.Id equals sr.MainReportId join er in db.EmergencyReports.Where(permissions.EmergencyReportsFilter) on sr.Id equals er.SubReportId group er by new { AppId = mr.AppBudget.AppId } into erg select new { AppId = erg.Key.AppId, ClientsCount = erg.Select(f => f.ClientId).Distinct().Count(), GrantsCount = erg.Count(), TotalAmount = ((decimal?)erg.Sum(f => f.Total ?? 0) ?? 0) }; result.TotalsRow = new DataRow { TypeName = "TOTAL", TypeId = 0, TypeDescription = null, ReportSummary = mrst.Select(f => new DataRowSummary { ClientsCount = f.ClientsCount, GrantsCount = f.GrantsCount, TotalAmount = f.TotalAmount }).FirstOrDefault(), AppSummary = appst.Select(f => new DataRowSummary { ClientsCount = f.ClientsCount, GrantsCount = f.GrantsCount, TotalAmount = f.TotalAmount }).FirstOrDefault() }; return(result); }