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); }