public async Task <LoanModel> GetSingle(int loanId, int borrowerId = 0)
{
dt.Clear();
LoanModel loan = new LoanModel();
try
{
using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString()))
{
string query = "";
if (borrowerId != 0)
{
query = $"SELECT * FROM Loans WHERE BorrowerId = '{borrowerId}';";
}
else
{
query = $"SELECT * FROM Loans WHERE Id = '{loanId}';";
}
conn.Open();
cmd = new SQLiteCommand(query, conn);
await cmd.ExecuteNonQueryAsync();
da = new SQLiteDataAdapter(cmd);
da.Fill(dt);
DataRow row = dt.Rows[0];
Users users = new Users();
loan.Id = Convert.ToInt32(row["Id"]);
loan.BorrowerId = Convert.ToInt32(row["BorrowerId"]);
loan.CollectorId = Convert.ToInt32(row["CollectorId"]);
loan.PrincipalLoan = Convert.ToDecimal(row["PrincipalLoan"]);
loan.Interest = Convert.ToDecimal(row["Interest"]);
loan.InterestId = Convert.ToInt32(row["InterestId"]);
loan.PerRemittance = Convert.ToDecimal(row["PerRemittance"]);
loan.MaturityValue = Convert.ToDecimal(row["MaturityValue"]);
loan.Duration = Convert.ToInt32(row["Duration"]);
loan.EffectiveDate = Convert.ToDateTime(row["EffectiveDate"]);
loan.MaturityDate = Convert.ToDateTime(row["MaturityDate"]);
loan.TotalBalance = Convert.ToDecimal(row["TotalBalance"]);
loan.Prefix = Convert.ToString(row["Prefix"]);
loan.Guarantor = await GetGuarantor(loan.Id);
loan.Collateral = await GetColleteral(loan.Id);
UserModel u = users.GetSingle("", loan.CollectorId);
loan.Collector = u.Name;
}
}
catch (Exception e)
{
msg = e.Message;
}
return(loan);
}
public async Task <IEnumerable <CollectionModel> > GetAll()
{
List <CollectionModel> collections = new List <CollectionModel>();
try
{
using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString()))
{
dt.Clear();
conn.Open();
string query = "SELECT * FROM Collections";
cmd = new SQLiteCommand(query, conn);
await cmd.ExecuteNonQueryAsync();
da = new SQLiteDataAdapter(cmd);
da.Fill(dt);
Loans loans = new Loans();
Borrowers borrowers = new Borrowers();
foreach (DataRow row in dt.Rows)
{
LoanModel l = await loans.GetSingle(Convert.ToInt32(row["LoanId"]));
BorrowerModel b = await borrowers.GetSingle(Convert.ToInt32(row["BorrowerId"]));
collections.Add(new CollectionModel()
{
Id = Convert.ToInt32(row["Id"]),
Amount = Convert.ToDecimal(row["Amount"]),
Date = Convert.ToDateTime(row["Date"]),
Borrower = b,
Loan = l
});
}
}
}
catch (Exception e)
{
msg = e.Message;
}
return(collections);
}
public async Task <CollectablesModel> GetSingle(int id)
{
CollectablesModel collectables = new CollectablesModel();
try
{
using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString()))
{
dt.Clear();
conn.Open();
string query = $"SELECT * FROM Collectables WHERE LoanId = '{id}'";
cmd = new SQLiteCommand(query, conn);
await cmd.ExecuteNonQueryAsync();
da = new SQLiteDataAdapter(cmd);
da.Fill(dt);
DataRow row = dt.Rows[0];
Loans loans = new Loans();
Borrowers borrowers = new Borrowers();
LoanModel l = await loans.GetSingle(Convert.ToInt32(row["LoanId"]));
BorrowerModel b = await borrowers.GetSingle(Convert.ToInt32(row["BorrowerId"]));
collectables.Id = Convert.ToInt32(row["Id"]);
collectables.DueDate = Convert.ToDateTime(row["DueDate"]);
collectables.Loan = l;
collectables.Borrower = b;
}
}
catch (Exception e)
{
msg = e.Message;
}
return(collectables);
}