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);
}