public List <LocationModel> GetLocations()
{
List <LocationModel> locations;
using (var imisContext = new ImisDB())
{
locations = imisContext.TblLocations
.Where(l => l.ValidityTo == null &&
!(l.LocationName == "Funding" ||
l.LocationCode == "FR" ||
l.LocationCode == "FD" ||
l.LocationCode == "FW" ||
l.LocationCode == "FV"
))
.Select(x => new LocationModel()
{
LocationId = x.LocationId,
LocationCode = x.LocationCode,
LocationName = x.LocationName,
ParentLocationId = x.ParentLocationId,
LocationType = x.LocationType
})
.ToList();
}
return(locations);
}
public int Delete(Guid uuid)
{
int response = 0;
try
{
using (var imisContext = new ImisDB())
{
var renewal = imisContext.TblPolicyRenewals.SingleOrDefault(pr => pr.RenewalUUID == uuid);
if (renewal == null)
{
return(-1);
}
renewal.ResponseStatus = 2;
renewal.ResponseDate = DateTime.Now;
imisContext.SaveChanges();
response = 1;
}
return(response);
}
catch (SqlException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
public List <UserData> FindUserByName(string UserName)
{
List <UserData> response = new List <UserData>();
try
{
using (var imisContext = new ImisDB())
{
response = imisContext.TblUsers
.Where(u => u.LoginName == UserName && u.ValidityTo == null)
.Select(x => new UserData()
{
UserUUID = x.UserUUID,
LoginName = Convert.ToString(x.LoginName),
PrivateKey = Convert.ToString(x.PrivateKey),
StoredPassword = Convert.ToString(x.StoredPassword)
})
.ToList();
}
return(response);
}
catch (Exception e)
{
throw e;
}
}
public int GetOfficerIdByUserUUID(Guid userUUID)
{
int response;
try
{
using (var imisContext = new ImisDB())
{
var loginName = imisContext.TblUsers
.Where(u => u.UserUUID == userUUID)
.Select(x => x.LoginName)
.FirstOrDefault();
response = imisContext.TblOfficer
.Where(o => o.Code == loginName)
.Select(x => x.OfficerId)
.FirstOrDefault();
}
return(response);
}
catch (SqlException e)
{
throw e;
}
}
public List <OfficerModel> GetOfficers()
{
List <OfficerModel> officers;
using (var imisContext = new ImisDB())
{
officers = imisContext.TblOfficer
.Where(o => o.ValidityTo == null)
.Select(x => new OfficerModel()
{
OfficerId = x.OfficerId,
OfficerUUID = x.OfficerUUID,
Code = x.Code,
LastName = x.LastName,
OtherNames = x.OtherNames,
Phone = x.Phone,
LocationId = x.LocationId,
OfficerIDSubst = x.OfficerIdsubst.ToString(),
WorksTo = x.WorksTo.GetValueOrDefault()
})
.ToList();
}
return(officers);
}
public void OnAuthorization(AuthorizationFilterContext context)
{
Guid userId = Guid.Parse(context.HttpContext.User.Claims
.Where(w => w.Type == "UserUUID")
.Select(x => x.Value)
.FirstOrDefault());
HashSet <int> rights;
using (var imisContext = new ImisDB())
{
rights = (from UR in imisContext.TblUserRole
join RR in imisContext.TblRoleRight.Where(x => x.ValidityTo == null) on UR.RoleID equals RR.RoleID
join US in imisContext.TblUsers.Where(x => x.ValidityTo == null) on UR.UserID equals US.UserId
where (US.UserUUID == userId && UR.ValidityTo == null)
select RR.RightID
).ToHashSet();
}
bool isAuthorized = userRights.Select(s => (int)s).All(x => rights.Contains(x));
if (!isAuthorized)
{
context.Result = new ForbidResult();
}
}
public FeedbackReportModel GetFeedbackStats(ReportRequestModel feedbackRequestModel, string officerCode)
{
FeedbackReportModel response = new FeedbackReportModel();
using (var imisContext = new ImisDB())
{
var feedbackSent = (from FP in imisContext.TblFromPhone
where FP.DocType == "F" &&
FP.LandedDate >= feedbackRequestModel.FromDate &&
FP.LandedDate <= feedbackRequestModel.ToDate &&
FP.OfficerCode == officerCode
select FP)
.ToList();
var feedbackAccepted = feedbackSent
.Where(f => f.DocStatus == "A")
.Count();
response = new FeedbackReportModel()
{
FeedbackSent = feedbackSent.Count(),
FeedbackAccepted = feedbackAccepted
};
}
return(response);
}
public EnrolmentReportModel GetEnrolmentStats(ReportRequestModel enrolmentRequestModel, string officerCode)
{
EnrolmentReportModel response = new EnrolmentReportModel();
using (var imisContext = new ImisDB())
{
var submitted = (from FP in imisContext.TblFromPhone
join O in imisContext.TblOfficer on FP.OfficerCode equals O.Code
where FP.DocType == "E" &&
FP.LandedDate >= enrolmentRequestModel.FromDate &&
FP.LandedDate <= enrolmentRequestModel.ToDate &&
O.ValidityTo == null &&
FP.OfficerCode == officerCode
select new { FromPhone = FP, Officer = O })
.ToList();
var assigned = (from S in submitted
from P in imisContext.TblPhotos
where P.ValidityTo == null &&
P.PhotoFileName == S.FromPhone.DocName &&
P.OfficerId == S.Officer.OfficerId
select S)
.ToList();
response = new EnrolmentReportModel()
{
TotalSubmitted = submitted.Select(x => x.FromPhone).Count(),
TotalAssigned = assigned.Count(),
};
}
return(response);
}
public RenewalReportModel GetRenewalStats(ReportRequestModel renewalRequestModel, string officerCode)
{
RenewalReportModel response = new RenewalReportModel();
using (var imisContext = new ImisDB())
{
var renewalSent = (from FP in imisContext.TblFromPhone
where FP.DocType == "R" &&
FP.LandedDate >= renewalRequestModel.FromDate &&
FP.LandedDate <= renewalRequestModel.ToDate &&
FP.OfficerCode == officerCode
select FP)
.ToList();
var renewalAccepted = renewalSent
.Where(f => f.DocStatus == "A")
.Count();
response = new RenewalReportModel()
{
RenewalSent = renewalSent.Count(),
RenewalAccepted = renewalAccepted
};
}
return(response);
}
public List <ClaimAdminModel> GetClaimAdministrators()
{
List <ClaimAdminModel> response = new List <ClaimAdminModel>();
try
{
using (var imisContext = new ImisDB())
{
response = imisContext.TblClaimAdmin
.Where(c => c.ValidityTo == null)
.Select(x => new ClaimAdminModel()
{
lastName = x.LastName,
otherNames = x.OtherNames,
claimAdminCode = x.ClaimAdminCode
}).ToList();
}
return(response);
}
catch (Exception e)
{
throw e;
}
}
public async Task DeleteFamilyAsync(int familyId)
{
using (var imisContext = new ImisDB())
{
TblFamilies familyToDelete = await imisContext.TblFamilies
.Where(f => f.ValidityTo == null && f.FamilyId == familyId)
.Include(f => f.TblInsuree)
.Select(f => f)
.FirstOrDefaultAsync();
if (familyToDelete == null)
{
throw new ValidationException(FamilyErrors.FAMILY_NOT_FOUND_ERROR);
}
familyToDelete.ValidityTo = DateTime.Now;
foreach (TblInsuree tblInsuree in familyToDelete.TblInsuree)
{
tblInsuree.ValidityTo = DateTime.Now;
}
await imisContext.SaveChangesAsync();
}
}
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 DataMessage GetCommissions(GetCommissionInputs model)
{
dynamic response;
int year = DateTime.UtcNow.Year;
int month = DateTime.UtcNow.Month;
try
{
year = Convert.ToInt32(model.year);
month = Convert.ToInt32(model.month);
}
catch (Exception)
{
throw;
}
DateTime minDate = new DateTime(year, month, 1);
DateTime maxDate = new DateTime(year, month, DateTime.DaysInMonth(year, month));
DataMessage message;
try
{
using (var imisContext = new ImisDB())
{
var res = (from PR in imisContext.TblPremium
join P in imisContext.TblPolicy.Where(p => p.ValidityTo == null) on PR.PolicyId equals P.PolicyId
join R in imisContext.TblReporting on PR.ReportingCommissionID equals R.ReportingId
join O in imisContext.TblOfficer on P.OfficerId equals O.OfficerId
where (PR.ReportingCommissionID == null &&
(PR.PayDate >= minDate && PR.PayDate <= maxDate) &&
PR.ValidityTo == null &&
O.Code == model.enrolment_officer_code)
select new
{
Commission = (R.CammissionRate == null ? 0.00M : R.CammissionRate) * PR.Amount,
PR.Amount
})
.ToList();
var c = res.Count > 0 ? res.Sum(x => x.Commission) : null;
var a = res.Count > 0 ? res.Sum(x => (decimal?)x.Amount) : null;
response = new List <dynamic>()
{
new { Commission = c, Amount = a }
};
message = new GetCommissionResponse(0, false, response, 0).Message;
}
}
catch (Exception e)
{
message = new GetCommissionResponse(e).Message;
}
return(message);
}
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 DataMessage Validate(IntentOfPay intent)
{
var context = new ImisDB();
foreach (var policy in intent.policies)
{
// Check if the Insurance number is not blank or null
if (String.IsNullOrEmpty(policy.insurance_number))
{
dataMessage.Code = 1;
dataMessage.MessageValue = new Responses.Messages.Language().GetMessage((int)UserLanguage, "WrongFormatInsureeNo");
}
// Check if the product is valid
else if (context.TblProduct.Where(p => p.ProductCode == policy.insurance_product_code && p.ValidityTo == null).FirstOrDefault() == null)
{
dataMessage.Code = 2;
dataMessage.MessageValue = new Responses.Messages.Language().GetMessage((int)UserLanguage, "InValidINmissingPC");
}
// Check if the officer code is valid
else if (context.TblOfficer.Where(o => o.Code == intent.enrolment_officer_code && o.ValidityTo == null).FirstOrDefault() == null)
{
dataMessage.Code = 3;
dataMessage.MessageValue = new Responses.Messages.Language().GetMessage((int)UserLanguage, "InValidEOC");
}
// Check if the officer can sell this product (based on the location)
else if (!CanOfficerSellProduct(intent.enrolment_officer_code, policy.insurance_product_code))
{
dataMessage.Code = 4;
dataMessage.MessageValue = new Responses.Messages.Language().GetMessage((int)UserLanguage, "IncompatibleEO_PC");
}
// Check if the family has existing product to renew (if the Enrolment Type is renewal(1))
else if (policy.IsRenewal() == 1 & (from prods in context.TblProduct
join pol in context.TblPolicy on prods.ProdId equals pol.ProdId
join i in context.TblInsuree on pol.FamilyId equals i.FamilyId
where (prods.ProductCode == policy.insurance_product_code &&
i.Chfid == policy.insurance_number &&
prods.ValidityTo == null &&
i.ValidityTo == null)
select pol.PolicyId).FirstOrDefault() == 0
)
{
dataMessage.Code = 5;
dataMessage.MessageValue = new Responses.Messages.Language().GetMessage((int)UserLanguage, "NoRenewalProduct");
}
// Check if requested product has any free control number left in pool
else if (!ControlNumberAvailable(policy.insurance_product_code))
{
dataMessage.Code = 11;
dataMessage.MessageValue = new Responses.Messages.Language().GetMessage((int)UserLanguage, "NoControlNumberAvailable");
}
}
dataMessage.ErrorOccured = dataMessage.Code != 0;
dataMessage.Data = new AssignedControlNumber();
return(dataMessage);
}
public User GetByUsername(string username)
{
TblUsers user;
using (var imisContext = new ImisDB())
{
user = imisContext.TblUsers.Where(u => u.LoginName == username).FirstOrDefault();
}
return(TypeCast.Cast <User>(user));
}
public async Task <User> GetByUsernameAsync(string username)
{
TblUsers user;
using (var imisContext = new ImisDB())
{
user = await imisContext.TblUsers.Where(u => u.LoginName == username).FirstOrDefaultAsync();
}
return(TypeCast.Cast <User>(user));
}
// TODO: Receiving RenewalUUID directly from SP
public List <GetPolicyRenewalModel> Get(string officerCode)
{
List <GetPolicyRenewalModel> response = new List <GetPolicyRenewalModel>();
using (var imisContext = new ImisDB())
{
var officerCodeParameter = new SqlParameter("@OfficerCode", SqlDbType.NVarChar, 8);
officerCodeParameter.Value = officerCode;
var sql = "exec uspGetPolicyRenewals @OfficerCode";
DbConnection connection = imisContext.Database.GetDbConnection();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(new[] { officerCodeParameter });
if (connection.State.Equals(ConnectionState.Closed))
{
connection.Open();
}
using (var reader = cmd.ExecuteReader())
{
do
{
while (reader.Read())
{
response.Add(new GetPolicyRenewalModel()
{
RenewalId = int.Parse(reader["RenewalId"].ToString()),
PolicyId = int.Parse(reader["PolicyId"].ToString()),
OfficerId = int.Parse(reader["OfficerId"].ToString()),
OfficerCode = reader["OfficerCode"].ToString(),
CHFID = reader["CHFID"].ToString(),
LastName = reader["LastName"].ToString(),
OtherNames = reader["OtherNames"].ToString(),
ProductCode = reader["ProductCode"].ToString(),
ProductName = reader["ProductName"].ToString(),
VillageName = reader["VillageName"].ToString(),
RenewalPromptDate = DateTime.Parse(reader["RenewalPromptDate"].ToStringWithDBNull()),
Phone = reader["Phone"].ToString(),
RenewalUUID = GetRenewalUUIDById(int.Parse(reader["RenewalId"].ToString()))
});
}
} while (reader.NextResult());
}
}
}
return(response);
}
public DiagnosisServiceItem GetDsi(DsiInputModel model)
{
DiagnosisServiceItem message = new DiagnosisServiceItem();
try
{
using (var imisContext = new ImisDB())
{
List <CodeName> diagnoses;
List <CodeNamePrice> items;
List <CodeNamePrice> services;
diagnoses = imisContext.TblIcdcodes
.Where(i => i.ValidityFrom >= Convert.ToDateTime(model.last_update_date) &&
i.ValidityTo == null)
.Select(x => new CodeName()
{
code = x.Icdcode,
name = x.Icdname
}).ToList();
items = imisContext.TblItems
.Where(i => i.ValidityFrom >= Convert.ToDateTime(model.last_update_date) &&
i.ValidityTo == null)
.Select(x => new CodeNamePrice()
{
code = x.ItemCode,
name = x.ItemName,
price = x.ItemPrice.ToString()
}).ToList();
services = imisContext.TblServices
.Where(i => i.ValidityFrom >= Convert.ToDateTime(model.last_update_date) &&
i.ValidityTo == null)
.Select(x => new CodeNamePrice()
{
code = x.ServCode,
name = x.ServName,
price = x.ServPrice.ToString()
}).ToList();
message.diagnoses = diagnoses;
message.items = items;
message.services = services;
message.update_since_last = DateTime.UtcNow;
}
return(message);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
/// TODO: add location constraint
public async Task <int> GetFamiliesCount()
{
int familiesCount = 0;
using (var imisContext = new ImisDB())
{
familiesCount = await imisContext.TblFamilies
.CountAsync(f => f.ValidityTo == null);
}
return(familiesCount);
}
public SnapshotResponseModel GetSnapshotIndicators(SnapshotRequestModel snapshotRequestModel, string officerCode)
{
SnapshotResponseModel response = new SnapshotResponseModel();
int officerId;
using (var imisContext = new ImisDB())
{
officerId = (from O in imisContext.TblOfficer
where O.Code == officerCode &&
O.ValidityTo == null
select O.OfficerId)
.FirstOrDefault();
var snapshotDateParameter = new SqlParameter("@SnapshotDate", snapshotRequestModel.SnapshotDate)
{
SqlDbType = SqlDbType.Date
};
var officerIdParameter = new SqlParameter("@OfficerId", officerId);
var sql = "SELECT Active, Expired, Idle, Suspended FROM udfGetSnapshotIndicators(@SnapshotDate,@OfficerId)";
DbConnection connection = imisContext.Database.GetDbConnection();
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(new[] { snapshotDateParameter, officerIdParameter });
if (connection.State.Equals(ConnectionState.Closed))
{
connection.Open();
}
using (var reader = cmd.ExecuteReader())
{
do
{
while (reader.Read())
{
response.Active = int.Parse(reader["Active"].ToString());
response.Expired = int.Parse(reader["Expired"].ToString());
response.Idle = int.Parse(reader["Idle"].ToString());
response.Suspended = int.Parse(reader["Suspended"].ToString());
}
} while (reader.NextResult());
}
}
}
return(response);
}
public bool Get(ReceiptRequestModel receipt)
{
bool response = false;
try
{
using (var imisContext = new ImisDB())
{
var districtId = (from F in imisContext.TblFamilies
join I in imisContext.TblInsuree on F.InsureeId equals I.InsureeId
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 (F.ValidityTo == null &&
I.ValidityTo == null &&
I.Chfid == receipt.CHFID)
select D.DistrictId)
.FirstOrDefault();
int?premiumId = (from PR in imisContext.TblPremium
join PL in imisContext.TblPolicy on PR.PolicyId equals PL.PolicyId
join F in imisContext.TblFamilies on PL.FamilyId equals F.FamilyId
join I in imisContext.TblInsuree on F.InsureeId equals I.InsureeId
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 (PR.ValidityTo == null &&
PL.ValidityTo == null &&
F.ValidityTo == null &&
I.ValidityTo == null &&
D.ValidityTo == null &&
PR.Receipt == receipt.ReceiptNo &&
D.DistrictId == districtId)
select PR.PremiumId)
.FirstOrDefault();
if (premiumId == 0)
{
response = true;
}
}
return(response);
}
catch (SqlException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
public async Task <ConfirmationTypeModel[]> GetAllConfirmationsTypes()
{
ConfirmationTypeModel[] confirmationTypes;
using (var imisContext = new ImisDB())
{
confirmationTypes = await imisContext.TblConfirmationTypes
.Select(ct => ConfirmationTypeModel.FromTblConfirmationType(ct))
.ToArrayAsync();
}
return(confirmationTypes);
}
public async Task <IdentificationTypeModel[]> GetAllIdentificationTypes()
{
IdentificationTypeModel[] identificationTypes;
using (var imisContext = new ImisDB())
{
identificationTypes = await imisContext.TblIdentificationTypes
.Select(it => IdentificationTypeModel.FromTblIdentificationTypes(it))
.ToArrayAsync();
}
return(identificationTypes);
}
public async Task <ProfessionTypeModel[]> GetAllProfessionTypes()
{
ProfessionTypeModel[] familyTypes;
using (var imisContext = new ImisDB())
{
familyTypes = await imisContext.TblProfessions
.Select(p => ProfessionTypeModel.FromTblProfessions(p))
.ToArrayAsync();
}
return(familyTypes);
}
public async Task <RelationTypeModel[]> GetAllRelationTypes()
{
RelationTypeModel[] relationTypes;
using (var imisContext = new ImisDB())
{
relationTypes = await imisContext.TblRelations
.Select(r => RelationTypeModel.FromTblRelations(r))
.ToArrayAsync();
}
return(relationTypes);
}
public async Task <EducationLevelModel[]> GetAllEducationLevels()
{
EducationLevelModel[] educationLevels;
using (var imisContext = new ImisDB())
{
educationLevels = await imisContext.TblEducations
.Select(e => EducationLevelModel.FromTblEducations(e))
.ToArrayAsync();
}
return(educationLevels);
}
/// <summary>
/// Get user by username and password by asychronious call
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public async Task <User> GetByUsernameAndPasswordAsync(string username, string password)
{
TblUsers user;
using (var imisContext = new ImisDB())
{
var userParameter = new SqlParameter("user", username);
var passwordParameter = new SqlParameter("password", password);
user = await imisContext.TblUsers.FromSql(GetByUsernameAndPasswordSQL(), userParameter, passwordParameter).SingleOrDefaultAsync();
}
return(TypeCast.Cast <User>(user));
}
/// <summary>
/// Get user by username and password by sychronious call
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public User GetByUsernameAndPassword(string username, string password)
{
User user;
using (var imisContext = new ImisDB())
{
var userParameter = new SqlParameter("user", username);
var passwordParameter = new SqlParameter("password", password);
user = (User)imisContext.TblUsers.FromSql(GetByUsernameAndPasswordSQL(), userParameter, passwordParameter).SingleOrDefault();
}
return(user);
}
public async Task <FamilyTypeModel[]> GetAllFamilyTypes()
{
FamilyTypeModel[] familyTypes;
using (var imisContext = new ImisDB())
{
familyTypes = await imisContext.TblFamilyTypes
.Select(ft => FamilyTypeModel.FromTblFamilyType(ft))
.ToArrayAsync();
}
return(familyTypes);
}
