public async Task <FamilyModel> GetFamilyByInsureeId(string insureeNumber)
{
FamilyModel familyModel;
using (var imisContext = new ImisDB())
{
familyModel = await
//(from i in imisContext.TblInsuree
// join f in imisContext.TblFamilies on i.FamilyId equals f.FamilyId
// where i.ValidityTo == null && i.Chfid == insureeId && f.ValidityTo == null
// select FamilyModel.FromTblFamilies(f))
imisContext.TblInsuree
.Where(i => i.ValidityTo == null && i.Chfid == insureeNumber)
.Join(imisContext.TblFamilies, i => i.FamilyId, f => f.FamilyId, (i, f) => f)
.Where(f => f.ValidityTo == null)
.Include(f => f.TblInsuree)
.Select(f => FamilyModel.FromTblFamilies(f))
.FirstOrDefaultAsync();
if (familyModel == null)
{
return(null);
}
}
return(familyModel);
}
public FamilyModel GetByCHFID(string chfid, Guid userUUID)
{
FamilyModel response = new FamilyModel();
try
{
using (var imisContext = new ImisDB())
{
var locationIds = (from UD in imisContext.TblUsersDistricts
join U in imisContext.TblUsers on UD.UserId equals U.UserId
where U.UserUUID == userUUID && UD.ValidityTo == null
select UD.LocationId)
.ToList();
var familyId = (from I in imisContext.TblInsuree
join F in imisContext.TblFamilies on I.FamilyId equals F.FamilyId
join V in imisContext.TblVillages on F.LocationId equals V.VillageId
join W in imisContext.TblWards on V.WardId equals W.WardId
join D in imisContext.TblDistricts on W.DistrictId equals D.DistrictId
where (I.Chfid == chfid &&
locationIds.Contains(D.DistrictId) &&
F.ValidityTo == null &&
I.ValidityTo == null &&
V.ValidityTo == null &&
W.ValidityTo == null &&
D.ValidityTo == null)
select F.FamilyId)
.FirstOrDefault();
response = imisContext.TblInsuree
.Where(i => i.ValidityTo == null && i.FamilyId == familyId)
.Join(imisContext.TblFamilies, i => i.FamilyId, f => f.FamilyId, (i, f) => f)
.Join(imisContext.TblFamilySMS, f => f.FamilyId, fsms => fsms.FamilyId, (f, fsms) => f)
.Where(f => f.ValidityTo == null)
.Include(f => f.TblInsuree)
.ThenInclude(f => f.Photo)
.Include(f => f.TblFamilySMS)
.Select(f => FamilyModel.FromTblFamilies(f))
.FirstOrDefault();
}
if (response == null)
{
return(null);
}
return(response);
}
catch (SqlException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
public async Task <FamilyModel> GetFamilyByFamilyId(int familyId)
{
FamilyModel familyModel;
using (var imisContext = new ImisDB())
{
familyModel = await imisContext.TblFamilies
.Where(f => f.ValidityTo == null && f.FamilyId == familyId)
.Include(f => f.TblInsuree)
.Select(f => FamilyModel.FromTblFamilies(f))
.FirstOrDefaultAsync();
if (familyModel == null)
{
return(null);
}
}
return(familyModel);
}
/// <summary>
///
/// </summary>
/// <param name="familyId"></param>
/// <param name="family"></param>
/// <returns></returns>
/// TODO: make a copy of the record before updating it
/// TODO: check if there are any insuree in the family to be updated
public async Task <FamilyModel> UpdateFamilyAsync(int familyId, FamilyModel family)
{
using (var imisContext = new ImisDB())
{
TblFamilies familyToUpdate = await imisContext.TblFamilies
.Where(f => f.ValidityTo == null && f.FamilyId == familyId)
//.Include(f => f.TblInsuree)
.Select(f => f)
.FirstOrDefaultAsync();
if (familyToUpdate == null)
{
throw new ValidationException(FamilyErrors.FAMILY_NOT_FOUND_ERROR);
}
familyToUpdate.FamilyType = family.FamilyType;
familyToUpdate.LocationId = family.LocationId;
familyToUpdate.Poverty = family.Poverty;
familyToUpdate.FamilyAddress = family.FamilyAddress;
familyToUpdate.Ethnicity = family.Ethnicity;
familyToUpdate.ConfirmationNo = family.ConfirmationNo;
familyToUpdate.ConfirmationType = family.ConfirmationType;
familyToUpdate.IsOffline = family.IsOffline;
//await imisContext.SaveChangesAsync();
//foreach (TblInsuree tblInsuree in tblFamily.TblInsuree)
//{
// if (tblInsuree.IsHead)
// {
// tblFamily.InsureeId = tblInsuree.InsureeId;
// break;
// }
//}
await imisContext.SaveChangesAsync();
return(FamilyModel.FromTblFamilies(familyToUpdate));
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
/// TODO: add location constraint
public async Task <FamilyModel[]> GetFamilies(int page = 1, int resultsPerPage = 20)
{
FamilyModel[] families;
using (var imisContext = new ImisDB())
{
families = await imisContext.TblFamilies
.Where(f => f.ValidityTo == null)
.Include(f => f.TblInsuree)
.Page(resultsPerPage <= 0 ? 20 : resultsPerPage, page <= 1 ? 1 : page) /// TODO: remove this as validation in Logic?
.Select(f => FamilyModel.FromTblFamilies(f))
.ToArrayAsync();
if (families == null)
{
return(null);
}
}
return(families);
}
/// <summary>
/// Adds a new Family and the associated Insurees into the database
/// </summary>
/// <param name="family">The Family to be added</param>
/// <returns></returns>
public async Task <FamilyModel> AddNewFamilyAsync(FamilyModel family)
{
using (var imisContext = new ImisDB())
{
var tblFamily = family.ToTblFamilies();
imisContext.Add(tblFamily);
await imisContext.SaveChangesAsync();
foreach (TblInsuree tblInsuree in tblFamily.TblInsuree)
{
if (tblInsuree.IsHead)
{
tblFamily.InsureeId = tblInsuree.InsureeId;
break;
}
}
await imisContext.SaveChangesAsync();
return(FamilyModel.FromTblFamilies(tblFamily));
}
}