public void Add(AlertStock alertStock)
{
foreach (DictionaryEntry entry in alertStock)
{
AddAlert((Alert)entry.Value);
}
}
public AlertStock SelectLoansByLoanOfficer(int pLoanOfficerId)
{
string addLoanOfficer = "";
if (pLoanOfficerId != 0)
addLoanOfficer = " AND (Credit.loanofficer_id = @loanOfficerId) ";
string sqlTextRepaymentAlert =
string.Format(@"SELECT Credit.id AS contract_id,
Credit.interest_rate,
Contracts.contract_code,
Contracts.creation_date,
Contracts.start_date,
Contracts.align_disbursed_date,
Contracts.close_date,
CASE Contracts.status
WHEN 1 THEN 'Pending'
WHEN 2 THEN 'Validated'
WHEN 3 THEN 'Refused'
WHEN 4 THEN 'Abandoned'
WHEN 5 THEN 'Active'
WHEN 6 THEN 'Closed'
WHEN 7 THEN 'WrittenOff'
ELSE '-'
END AS loan_status,
InstallmentTypes.name AS installment_types,
ISNULL(Groups.name, ISNULL(Persons.first_name + ' ' + Persons.last_name,Corporates.name)) AS client_name,
Districts.name as district_name, Installments.capital_repayment + Installments.interest_repayment
- Installments.paid_capital - Installments.paid_interest AS amount,
Installments.expected_date AS effect_date, ISNULL(( SELECT SUM(principal) FROM contractEvents
INNER JOIN repaymentEvents ON repaymentEvents.id = contractEvents.id WHERE is_deleted = 0
AND contract_id = Contracts.id ), 0) AS olb FROM Credit
INNER JOIN Contracts ON Contracts.id = Credit.id
INNER JOIN Installments ON Installments.contract_id = Credit.id
INNER JOIN InstallmentTypes ON dbo.Credit.installment_type = dbo.InstallmentTypes.id
INNER JOIN Projects ON Contracts.project_id = Projects.id
INNER JOIN Tiers ON Projects.tiers_id = Tiers.id
LEFT OUTER JOIN Corporates ON Tiers.id=Corporates.id
LEFT OUTER JOIN Persons ON dbo.Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON dbo.Tiers.id = Groups.id
LEFT OUTER JOIN Districts ON dbo.Tiers.district_id = Districts.id
WHERE ( Installments.capital_repayment + Installments.interest_repayment - Installments.paid_capital - Installments.paid_interest > 0.02 ) AND Contracts.status = 5
{0}
ORDER BY contract_id, effect_date DESC", addLoanOfficer);
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmdSelectRepayment = new OpenCbsCommand(sqlTextRepaymentAlert, conn))
{
if (pLoanOfficerId != 0)
cmdSelectRepayment.AddParam("@loanOfficerId", pLoanOfficerId);
using (OpenCbsReader reader = cmdSelectRepayment.ExecuteReader())
{
if(reader.Empty) return new AlertStock();
AlertStock alertStock = new AlertStock();
while (reader.Read())
{
alertStock.Add(GetAlert(reader, 'R'));
}
return alertStock;
}
}
}
public void Add(AlertStock alertStock)
{
foreach(DictionaryEntry entry in alertStock)
{
AddAlert((Alert)entry.Value);
}
}
