public static IQueryable <CC.Data.Client> GetClients(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
var filtered = db.Clients.Where(permissions.ClientsFilter);
if (param.AgencyId.HasValue)
{
filtered = filtered.Where(f => f.AgencyId == param.AgencyId.Value);
}
if (param.AgencyGroupId.HasValue)
{
filtered = filtered.Where(f => f.Agency.GroupId == param.AgencyGroupId.Value);
}
if (param.RegionId.HasValue)
{
filtered = filtered.Where(f => f.Agency.AgencyGroup.Country.RegionId == param.RegionId.Value);
}
if (param.ApprovalStatusId.HasValue)
{
filtered = filtered.Where(f => f.ApprovalStatusId == param.ApprovalStatusId);
}
if (!string.IsNullOrWhiteSpace(param.FirstName))
{
filtered = filtered.Where(f => f.FirstName.Contains(param.FirstName.Trim()));
}
if (!string.IsNullOrWhiteSpace(param.LastName))
{
filtered = filtered.Where(f => f.LastName.Contains(param.LastName.Trim()));
}
if (param.Id.HasValue)
{
filtered = filtered.Where(f => f.Id == param.Id);
}
if (!string.IsNullOrWhiteSpace(param.NationalId))
{
filtered = filtered.Where(f => f.NationalId == param.NationalId);
}
if (param.Active.HasValue)
{
if (param.Active.Value)
{
filtered = filtered.Where(c => (c.DeceasedDate == null && c.LeaveDate == null && c.LeaveReasonId == null));
}
else
{
filtered = filtered.Where(c => !(c.DeceasedDate == null && c.LeaveDate == null && c.LeaveReasonId == null));
}
}
if (param.AustrianEligible)
{
filtered = filtered.Where(f => f.AustrianEligible == true);
}
if (param.RomanianEligible)
{
filtered = filtered.Where(f => f.RomanianEligible == true);
}
if (param.SC)
{
filtered = filtered.Where(f => f.SC_Client == true);
}
if (param.DCC)
{
filtered = filtered.Where(f => f.DCC_Client == true);
}
if (param.GGReportedOnly)
{
filtered = filtered.Where(f => f.GGReportedCount > 0);
}
if (param.CreateDateFrom.HasValue)
{
filtered = filtered.Where(f => f.CreatedAt >= param.CreateDateFrom);
}
if (param.CreateDateTo.HasValue)
{
filtered = filtered.Where(f => f.CreatedAt <= param.CreateDateTo);
}
if (param.HcWaitlistOnly)
{
filtered = filtered.Where(f => f.HomecareWaitlist);
}
if (param.OtherWaitlistOnly)
{
filtered = filtered.Where(f => f.OtherServicesWaitlist);
}
return(filtered);
}
public static IEnumerable <LeaveEntriesExportModel> LeaveEntriesExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
var q = (from c in GetClients(db, permissions, param)
join h in db.Histories on c.Id equals h.ReferenceId
where h.TableName == "Clients" && h.FieldName == "LeaveDate"
join hl in db.Histories on c.Id equals hl.ReferenceId
where hl.TableName == "Clients" && hl.FieldName == "LeaveReasonId" && hl.UpdateDate == h.UpdateDate
where h.NewValue != null && hl.NewValue != null
select new LeaveEntriesExportModel
{
ClientId = c.Id,
LeaveDate = h.NewValue,
LeaveReason = hl.NewValue,
}).OrderBy(f => f.ClientId).ToList();
DateTime tempDate;
int templrid;
return(from item in q
select new LeaveEntriesExportModel
{
ClientId = item.ClientId,
LeaveDate = DateTime.TryParseExact(item.LeaveDate, "M/d/yyyy h:mm:ss tt", new System.Globalization.CultureInfo("en-US"), System.Globalization.DateTimeStyles.None, out tempDate) ? tempDate.ToString("dd MMM yyyy") : "",
LeaveReason = int.TryParse(item.LeaveReason, out templrid) && db.LeaveReasons.Any(f => f.Id == templrid) ? db.LeaveReasons.FirstOrDefault(f => f.Id == templrid).Name : ""
});
}
public static IEnumerable <HASExportModel> HASExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
return((from c in GetClients(db, permissions, param)
join has in db.ClientHcStatuses on c.Id equals has.ClientId
select new HASExportModel
{
ClientId = c.Id,
StatusDate = has.StartDate,
FundStatus = has.FundStatusId.HasValue != null ? has.FundStatus.Name : "N/A",
ApprovalStatus = has.ApprovalStatusId.HasValue != null ? has.ApprovalStatus.Name : "N/A",
HCStatus = has.HcStatusId
}).OrderBy(f => f.ClientId));
}
public static IEnumerable <UnmetNeedsOtherExportModel> GetUnmetNeedsOtherExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
return(from c in GetClients(db, permissions, param)
join uno in db.UnmetNeedsOthers on c.Id equals uno.ClientId
select new UnmetNeedsOtherExportModel
{
ClientId = c.Id,
ServiceType = uno.ServiceType.Name,
ServiceTypeImportId = uno.ServiceType.ServiceTypeImportId ?? uno.ServiceTypeId,
Amount = uno.Amount,
CUR = uno.CurrencyId,
AgencyGroupId = c.Agency.GroupId,
AgencyId = c.AgencyId
});
}
public static IEnumerable <GovHcHoursExportModel> GetGovHcHoursExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
return((from c in GetClients(db, permissions, param)
join gh in db.GovHcHours on c.Id equals gh.ClientId
select new GovHcHoursExportModel
{
ClientId = c.Id,
StartDate = gh.StartDate,
Value = gh.Value
}).OrderBy(f => f.ClientId));
}
public static IEnumerable <FunctionalityScoresExportModel> GetFunctionalityScoresExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
return((from c in GetClients(db, permissions, param)
join fs in db.FunctionalityScores on c.Id equals fs.ClientId
select new FunctionalityScoresExportModel
{
Id = c.Id,
DiagnosticScore = fs.DiagnosticScore,
StartDate = fs.StartDate
}).OrderBy(f => f.Id).ThenBy(f => f.StartDate));
}
public static IEnumerable <ApprovalStatusChangesExportModel> GetApprovalStatusChangesExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
var q = (from c in GetClients(db, permissions, param)
join h in db.Histories.Where(f => f.TableName == "Clients" && f.FieldName == "ApprovalStatusId") on c.Id equals h.ReferenceId
select new
{
ClientId = c.Id,
ApprovalStatusId = h.NewValue,
ApprovalStatusDate = h.UpdateDate
}).ToList();
List <tempApprovalStatus> tempList = new List <tempApprovalStatus>();
foreach (var item in q)
{
int apsId;
int.TryParse(item.ApprovalStatusId, out apsId);
tempList.Add(new tempApprovalStatus {
ClientId = item.ClientId, ApprovalStatusId = apsId, ApprovalStatusDate = item.ApprovalStatusDate
});
}
return((from a in tempList
join aps in db.ApprovalStatuses on a.ApprovalStatusId equals aps.Id
select new ApprovalStatusChangesExportModel
{
ClientId = a.ClientId,
ApprovalStatusName = aps.Name,
ApprovalStatusDate = a.ApprovalStatusDate
}).OrderBy(f => f.ClientId));
}
public static IEnumerable <EligibilityPeriodsExportModel> GetEligibilityPeriodsExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
var filtered = (from c in GetClients(db, permissions, param)
join ep in db.HomeCareEntitledPeriods on c.Id equals ep.ClientId
select new EligibilityPeriodsExportModel
{
Id = c.Id,
StartDate = ep.StartDate,
EndDate = ep.EndDate
}).OrderBy(f => f.Id).ThenBy(f => f.StartDate).ToList();
return(filtered.Select(f => new EligibilityPeriodsExportModel
{
Id = f.Id,
StartDate = f.StartDate,
EndDate = f.EndDate.HasValue ? f.EndDate.Value.AddDays(-1) : f.EndDate
}));
}
public static IEnumerable <DuplicateExportModel> GetDuplicateExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
var clients = GetClients(db, permissions, param);
var filtered = clients.Where(f => f.MasterId != null).Select(c => new { c.MasterId });
var result = (from c in clients
join f in filtered on(c.MasterIdClcd) equals f.MasterId
select new DuplicateExportModel
{
Id = c.Id,
AgencyName = c.Agency.Name,
MasterId = c.MasterId != null ? c.MasterId : c.Id
}).Distinct();
return(result);
}
public static IEnumerable <ClientsExportModel> GetExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
var now = DateTime.Now;
var filtered = from a in GetClients(db, permissions, param)
join c in db.viewClientDetails on a.Id equals c.CCID
//join d in db.HcCapsTableRaws on c.CCID equals d.ClientId
// where d.EndDate ==null
let CurHcCap = db.HcCapsTableRaws
.Where(cap => cap.ClientId == c.CCID)
.Where(cap => cap.StartDate <= DateTime.Now)
.Where(cap => cap.EndDate == null || cap.EndDate > DateTime.Now)
.OrderByDescending(cap => cap.StartDate)
.Select(cap => cap.HcCap)
.FirstOrDefault()
select new ClientsExportModel
{
InternalAgencyID = c.InternalAgencyID,
ORGID = c.ORGID,
Agency = c.Agency,
FirstName = c.FirstName,
LastName = c.LastName,
MiddleName = c.MiddleName,
OtherFirstName = c.OtherFirstName,
OtherLastName = c.OtherLastName,
PreviousFirstName = c.PreviousFirstName,
PreviousLastName = c.PreviousLastName,
Address = c.Address,
City = c.City,
ZIP = c.ZIP,
State = c.State,
CountryName = c.CountryName,
BirthDate = c.BirthDate,
Otherdateofbirth = c.Otherdateofbirth,
BirthCity = c.BirthCity,
BirthCountry = c.BirthCountry,
IDType = c.IDType,
OtherIDcard = c.OtherIDcard,
OtherIDtype = c.OtherIDtype,
GovernmentIssuedID = c.GovernmentIssuedID,
DeceasedDate = c.DeceasedDate,
LeaveDate = c.LeaveDate,
JoinDate = c.JoinDate,
LeaveReason = c.LeaveReason,
Remarks = c.Remarks,
IncomeCriteriaComplied = c.IncomeCriteriaComplied,
Gender = c.Gender == (int)Client.Genders.Female ? "Female" : c.Gender == (int)Client.Genders.Male ? "Male" : "",
CCID = c.CCID,
MasterId = c.MasterId,
CreateDate = c.CreatedAt,
FunctionalityLevelName = c.FunctionalityLevelName,
//HCHours = d.HcCap,
//HCHours = c.HcHours,
HCHours = CurHcCap == null ? 0 : CurHcCap,
GrandfatheredHours = c.GrandfatheredHours,
GFStartDate = c.GFStartDate,
GFType = c.GFType,
ApprovalStatus = c.ApprovalStatus,
NaziPersecutionDetails = c.NaziPersecutionDetails,
CompensationProgram = c.CompensationProgram,
HomecareEligibilityStartDate = c.HomecareEligibilityStartDate,
//inclusive end date is desired here
HomecareEligibilityEndDate = c.HomecareEligibilityEndDate,
GovtHChours = c.GovtHChours,
GovtHChoursStartDate = c.GovtHChoursStartDate,
DiagnosticScore = c.DiagnosticScore,
HighestStartDateofDiagnosticScore = c.HighestStartDateofDiagnosticScore,
Deceased = (c.DeceasedDate != null) ? true : false,
Otheraddress = c.Otheraddress,
PreviousaddressinIsrael = c.PreviousaddressinIsrael,
Phone = c.Phone,
AustrianEligible = c.AustrianEligible,
RomanianEligible = c.RomanianEligible,
GGReportedOnly = c.GGReportedCount != null && c.GGReportedCount > 0 ? "Yes" : "No",
UnmetNeedsStartDate = c.UnmetNeedsStartDate,
UnmetNeedsValue = c.UnmetNeedsValue,
HomecareApprovalStatusName = c.HomecareApprovalStatusName,
AppearedAtLeastOnce = c.AppearedAtLeastOnce == true ? "Yes" : "No",
IsCeefRecipient = c.IsCeefRecipient ? "Yes" : "No",
CeefId = c.CeefId,
HomecareWaitlist = c.HomecareWaitlist ? "Yes" : "No",
UnableToSign = c.UnableToSign ? "Yes" : "No",
OtherServicesWaitlist = c.OtherServicesWaitlist ? "Yes" : "No",
MAFDate = c.MAFDate,
MAF105Date = c.MAF105Date,
HAS2Date = c.Has2Date,
HomeCareEntitled = c.HomecareEligibilityEndDate == null || c.HomecareEligibilityEndDate > now ? "Yes" : "No",
CommPrefs = c.CommPrefs,
CareReceivedVia = c.CareReceivedVia
};
return(filtered);
}
public static IEnumerable <BegExportModel> GetBegExportData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions, ClientsListDataTableModel param)
{
var filtered = GetClients(db, permissions, param);
return(filtered.Select(c => new BegExportModel
{
Id = c.Id,
AgencyId = c.AgencyId,
AgencyName = c.Agency.Name,
FundStatusName = c.FundStatus.Name,
ApprovalStatusName = c.ApprovalStatus.Name,
CompensationProgramName = c.CompensationProgramName,
AddCompName = c.AddCompName,
AddCompRegNum = c.AddCompId,
LastName = c.LastName,
FirstName = c.FirstName,
PrevLastName = c.PrevLastName,
PrevFirstName = c.PrevFirstName,
OtherLastName = c.OtherLastName,
PobCity = c.PobCity,
BirthCountryName = c.BirthCountry.Name,
BirthDate = c.BirthDate,
OtherBirthDate = c.OtherDob,
Address = c.Address,
City = c.City,
Zip = c.ZIP,
StateCode = c.State.Code,
CountryCode = c.Country.Code,
}));
}
public static IQueryable <ClientsListEntry> GetClientsList(CC.Data.Services.IPermissionsBase permissions, CC.Data.ccEntities db, ClientsListDataTableModel param)
{
var q = from c in GetClients(db, permissions, param)
let curfl = c.FunctionalityScores.OrderByDescending(s => s.StartDate).Where(s => s.StartDate < DateTime.Now).Select(s => s.FunctionalityLevel).FirstOrDefault()
let CurGovHcHours = c.GovHcHours1.Where(a => a.StartDate < DateTime.Now).OrderByDescending(a => a.StartDate).Select(a => (decimal?)a.Value).FirstOrDefault()
let CurUnmetNeeds = c.UnmetNeeds1.Where(a => a.StartDate < DateTime.Now).OrderByDescending(a => a.StartDate).FirstOrDefault()
let CurGFHours = c.GrandfatherHours.Where(a => a.StartDate < DateTime.Now).OrderByDescending(a => a.StartDate).Select(a => (decimal?)a.Value).FirstOrDefault()
let BirthCountry = c.BirthCountryId == null ? null : db.BirthCountries.Where(p => p.Id == c.BirthCountryId).FirstOrDefault()
let CurHcCap = db.HcCapsTableRaws
.Where(cap => cap.ClientId == c.Id)
.Where(cap => cap.StartDate <= DateTime.Now)
.Where(cap => cap.EndDate == null || cap.EndDate > DateTime.Now)
.OrderByDescending(cap => cap.StartDate)
.Select(cap => cap.HcCap)
.FirstOrDefault()
select new ClientsListEntry
{
FirstName = c.FirstName,
LastName = c.LastName,
Id = c.Id,
NationalId = c.NationalId,
JoinDate = c.JoinDate,
Address = c.Address,
City = c.City,
Phone = c.Phone,
BirthCountry = BirthCountry.Name,
BirthDate = c.BirthDate,
ApprovalStatusId = c.ApprovalStatusId,
AgencyId = c.AgencyId,
AgencyGroupId = c.Agency.AgencyGroup.Id,
AgencyName = c.Agency.Name,
AgencyGroupName = c.Agency.AgencyGroup.DisplayName,
AgencyRegionName = c.Agency.AgencyGroup.Country.Region.Name,
ApprovalStatusName = c.ApprovalStatus.Name,
GfHours = CurGFHours,
FunctionalityLevelName = curfl.Name,
CurFuncHcHours = (int?)curfl.HcHoursLimit,
SubstractGovHcHours = (bool?)curfl.SubstractGovHours,
CurGovHcHours = CurGovHcHours,
AllowedHcHours = CurHcCap,
InternalId = c.InternalId,
MasterId = c.MasterId,
UploadDate = c.CreatedAt,
Active = c.DeceasedDate == null && c.LeaveDate == null && c.LeaveReasonId == null,
AustrianEligible = c.AustrianEligible,
RomanianEligible = c.RomanianEligible,
GGReportedOnly = c.GGReportedCount != null && c.GGReportedCount > 0,
UnmetNeedsStartDate = CurUnmetNeeds.StartDate,
UnmetNeedsValue = CurUnmetNeeds.WeeklyHours
};
return(q);
}