public static PartyRole CreateAndUpdateCurrentLoanApplicationRole(LoanApplication loanApplication, Party party, RoleType roletype, DateTime today)
{
PartyRole role = GetPartyRoleFromLoanApplication(loanApplication, roletype);
if (role != null)
role.EndDate = today;
return CreateLoanApplicationRole(loanApplication, roletype, party, today);
}
public static PartyRole Create(RoleType roletype, int party, DateTime today)
{
PartyRole partyRole = new PartyRole();
partyRole.PartyId = party;
partyRole.RoleTypeId = roletype.Id;
partyRole.EffectiveDate = today;
partyRole.EndDate = null;
return partyRole;
}
public static PartyRole CreateChequeRole(Cheque cheque, RoleType roleType, Party party, DateTime today)
{
PartyRole partyRole = new PartyRole();
partyRole.Party = party;
partyRole.RoleTypeId = roleType.Id;
partyRole.EffectiveDate = today;
partyRole.EndDate = null;
cheque.PartyRole = partyRole;
Context.Cheques.AddObject(cheque);
return partyRole;
}
public static RoleType GetType(RoleType parentRole, RoleType subRole)
{
return Context.RoleTypes.SingleOrDefault(entity => entity.Name == subRole.Name && entity.ParentRoleTypeId == parentRole.Id);
}
public static PartyRole CreateLoanApplicationRole(LoanApplication loanApplication, RoleType roletype, Party party, DateTime today)
{
PartyRole partyRole = new PartyRole();
partyRole.Party = party;
partyRole.RoleTypeId = roletype.Id;
partyRole.EffectiveDate = today;
partyRole.EndDate = null;
LoanApplicationRole loanApplicationRole = new LoanApplicationRole();
loanApplicationRole.LoanApplication = loanApplication;
loanApplicationRole.PartyRole = partyRole;
Context.LoanApplicationRoles.AddObject(loanApplicationRole);
return partyRole;
}
public static PartyRole GetPartyRoleFromLoanApplication(LoanApplication loanApplication, RoleType roletype)
{
var loanApplicationRole = loanApplication.LoanApplicationRoles.FirstOrDefault(entity => entity.PartyRole.RoleTypeId == roletype.Id
&& entity.PartyRole.EndDate == null);
if (loanApplicationRole != null)
return loanApplicationRole.PartyRole;
else
return null;
}
public static IQueryable<PartyRole> GetAllRoleTypeFromLoanApplication(LoanApplication loanApplication, RoleType roletype)
{
return from lar in Context.LoanApplicationRoles.Where(entity => entity.PartyRole.RoleTypeId == roletype.Id
&& entity.PartyRole.EndDate == null && entity.ApplicationId == loanApplication.ApplicationId)
select lar.PartyRole;
}
public static PartyRole GetByPartyIdAndRole(int partyId, RoleType role)
{
return Context.PartyRoles.FirstOrDefault(entity => entity.PartyId == partyId && entity.RoleTypeId == role.Id && entity.EndDate == null);
}
public static PartyRole CreateOrRetrieve(RoleType roletype, int party, DateTime today)
{
var role = GetByPartyIdAndRole(party, roletype);
if (role == null)
role = Create(roletype, party, today);
return role;
}
public static IQueryable<PartyRole> GetAllByPartyIdAndRole(int partyId, RoleType role)
{
return Context.PartyRoles.Where(entity => entity.PartyId == partyId && entity.RoleTypeId == role.Id && entity.EndDate == null);
}
public static IQueryable<LoanApplication> GetAllLoanApplicationOf(Party party, RoleType role)
{
var applications = from lar in Context.LoanApplicationRoles.Where(entity => entity.PartyRole.PartyId == party.Id
&& entity.PartyRole.RoleTypeId == role.Id
&& entity.PartyRole.EndDate == null)
join la in Context.LoanApplications on lar.ApplicationId equals la.ApplicationId
select la;
return applications;
}
public static IEnumerable<CustomerWithLoan> GetAllCustomersWithLoanOriginal(RoleType role)
{
var parties = from la in Context.LoanApplications
join lar in Context.LoanApplicationRoles on la.ApplicationId equals lar.ApplicationId
join status in Context.LoanApplicationStatus on la.ApplicationId equals status.ApplicationId
join ag in Context.Agreements on la.ApplicationId equals ag.ApplicationId
join fa in Context.FinancialAccounts on ag.Id equals fa.AgreementId
join r in Context.Receivables on fa.Id equals r.FinancialAccountId
join rs in Context.ReceivableStatus on r.Id equals rs.ReceivableId
where status.IsActive == true && (status.LoanApplicationStatusType.Id == LoanApplicationStatusType.ClosedType.Id
|| status.LoanApplicationStatusType.Id == LoanApplicationStatusType.PendingInFundingType.Id)
&& lar.PartyRole.RoleTypeId == role.Id && lar.PartyRole.EndDate == null
group lar by lar.PartyRole.Party into uniqueParty
select uniqueParty.Key;
List<CustomerWithLoan> customerWithLoans = new List<CustomerWithLoan>();
foreach (var party in parties)
{
customerWithLoans.Add(new CustomerWithLoan(party));
}
return customerWithLoans;
}
public static IEnumerable<CustomerWithLoan> GetAllCustomersWithLoan(RoleType role)
{
//var parties = from la in Context.LoanApplications
// join lar in Context.LoanApplicationRoles.Where(entity =>
// entity.PartyRole.RoleTypeId == role.Id && entity.PartyRole.EndDate == null) on la.ApplicationId equals lar.ApplicationId
// join status in Context.LoanApplicationStatus on la.ApplicationId equals status.ApplicationId
// join ag in Context.Agreements on la.ApplicationId equals ag.ApplicationId
// join fa in Context.FinancialAccounts on ag.Id equals fa.AgreementId
// join r in Context.Receivables on fa.Id equals r.FinancialAccountId
// join rs in Context.ReceivableStatus on r.Id equals rs.ReceivableId
// where rs.IsActive == true && (rs.ReceivableStatusType.Id == ReceivableStatusType.OpenType.Id
// || rs.ReceivableStatusType.Id == ReceivableStatusType.PartiallyPaidType.Id) && status.IsActive == true &&
// (status.LoanApplicationStatusType.Id == LoanApplicationStatusType.ClosedType.Id ||
// status.LoanApplicationStatusType.Id == LoanApplicationStatusType.PendingInFundingType.Id)
// group lar by lar.PartyRole.Party into uniqueParty
// select uniqueParty.Key;
var parties = from la in Context.LoanApplications
join lar in Context.LoanApplicationRoles.Where(entity =>
entity.PartyRole.RoleTypeId == role.Id && entity.PartyRole.EndDate == null) on la.ApplicationId equals lar.ApplicationId
join status in Context.LoanApplicationStatus on la.ApplicationId equals status.ApplicationId
join ag in Context.Agreements on la.ApplicationId equals ag.ApplicationId
join fa in Context.FinancialAccounts on ag.Id equals fa.AgreementId
join ls in Context.LoanAccountStatus on fa.Id equals ls.FinancialAccountId
where ls.IsActive == true && ls.StatusTypeId != LoanAccountStatusType.PaidOffType.Id && status.IsActive == true &&
(status.LoanApplicationStatusType.Id == LoanApplicationStatusType.ClosedType.Id ||
status.LoanApplicationStatusType.Id == LoanApplicationStatusType.PendingInFundingType.Id)
group lar by lar.PartyRole.Party into uniqueParty
select uniqueParty.Key;
List<CustomerWithLoan> customerWithLoans = new List<CustomerWithLoan>();
foreach (var party in parties)
{
customerWithLoans.Add(new CustomerWithLoan(party));
}
return customerWithLoans;
}
public static IEnumerable<CustomerWithLoan> GetAllCustomersWithDisbursedLoans(RoleType role, int employeePartyId)
{
var parties = from la in Context.LoanAccounts
join fa in Context.FinancialAccounts.Where(entity => entity.FinancialAccountTypeId == FinancialAccountType.LoanAccountType.Id)
on la.FinancialAccountId equals fa.Id
join far in Context.FinancialAccountRoles.Where(entity => entity.PartyRole.RoleTypeId == role.Id)
on fa.Id equals far.FinancialAccountId
join ag in Context.Agreements on fa.AgreementId equals ag.Id
join ldv in Context.LoanDisbursementVcrs on ag.Id equals ldv.AgreementId
join dvs in Context.DisbursementVcrStatus on ldv.Id equals dvs.LoanDisbursementVoucherId
join las in Context.LoanAccountStatus on la.FinancialAccountId equals las.FinancialAccountId
join ags in Context.AgreementTypes on ag.AgreementTypeId equals ags.Id
where las.IsActive == true && (las.StatusTypeId == LoanAccountStatusType.CurrentType.Id
|| las.StatusTypeId == LoanAccountStatusType.DelinquentType.Id
|| las.StatusTypeId == LoanAccountStatusType.UnderLitigationType.Id)
&& ags.Id == AgreementType.LoanAgreementType.Id && la.LoanBalance != 0 && ag.EndDate == null
&& dvs.IsActive == true && (dvs.DisbursementVoucherStatTypId == DisbursementVcrStatusEnum.FullyDisbursedType.Id
|| dvs.DisbursementVoucherStatTypId == DisbursementVcrStatusEnum.PartiallyDisbursedType.Id)
&& far.PartyRole.PartyId != employeePartyId
group far by far.PartyRole.Party into uniqueParty
select uniqueParty.Key;
List<CustomerWithLoan> customerWithLoans = new List<CustomerWithLoan>();
foreach (var party in parties)
{
customerWithLoans.Add(new CustomerWithLoan(party));
}
return customerWithLoans;
}
public static IEnumerable<CustomerWithLoan> GetAllCustomersWithBilledLoans(RoleType role, DateTime today)
{
var yesterday = today.AddDays(-1);
var tommorow = today.AddDays(1);
var parties = from la in Context.LoanApplications
join lar in Context.LoanApplicationRoles.Where(entity =>
entity.PartyRole.RoleTypeId == role.Id && entity.PartyRole.EndDate == null) on la.ApplicationId equals lar.ApplicationId
join status in Context.LoanApplicationStatus on la.ApplicationId equals status.ApplicationId
join ag in Context.Agreements on la.ApplicationId equals ag.ApplicationId
join fa in Context.FinancialAccounts on ag.Id equals fa.AgreementId
join r in Context.Receivables on fa.Id equals r.FinancialAccountId
join rs in Context.ReceivableStatus on r.Id equals rs.ReceivableId
where rs.IsActive == true && (rs.ReceivableStatusType.Id == ReceivableStatusType.OpenType.Id
|| rs.ReceivableStatusType.Id == ReceivableStatusType.PartiallyPaidType.Id) && status.IsActive == true &&
(status.LoanApplicationStatusType.Id == LoanApplicationStatusType.ClosedType.Id ||
status.LoanApplicationStatusType.Id == LoanApplicationStatusType.PendingInFundingType.Id)
&& r.Date > yesterday && r.Date < tommorow
group lar by lar.PartyRole.Party into uniqueParty
select uniqueParty.Key;
List<CustomerWithLoan> customerWithLoans = new List<CustomerWithLoan>();
foreach (var party in parties)
{
customerWithLoans.Add(new CustomerWithLoan(party));
}
return customerWithLoans;
}
