public async Task <bool> Update(BorrowerModel b) { try { using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString())) { conn.Open(); string year = Convert.ToString(DateTime.Now.Year); string query = $"UPDATE Borrowers SET Firstname = '{b.FirstName}', LastName = '{b.LastName}', " + $"MiddleName = '{b.MiddleName}', Address = '{b.Address}', ContactNumber = '{b.ContactNumber}', Gender = '{b.Gender}'," + $"CivilStatus = '{b.CivilStatus}', Birthday ='{b.Birthday}' ,Occupation = '{b.Occupation}', MonthlySalary = '{b.MonthlySalary}', Image = '{b.Image}' " + $"WHERE Id = '{b.Id}'"; cmd = new SQLiteCommand(query, conn); int result = await cmd.ExecuteNonQueryAsync(); if (result > 0) { msg = "Borrower is Updated Successfully!"; return(true); } else { msg = "Failed to Update Borrower! Please try again."; } } } catch (Exception ex) { msg = ex.Message; } return(false); }
public async Task <bool> Add(BorrowerModel b) { try { using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString())) { conn.Open(); string year = Convert.ToString(DateTime.Now.Year); string query = "INSERT INTO Borrowers (FirstName, LastName, MiddleName, Address, ContactNumber, Birthday," + "Occupation, MonthlySalary, Image, Gender, CivilStatus, Prefix) " + $"VALUES('{b.FirstName}','{b.LastName}','{b.MiddleName}','{b.Address}','{b.ContactNumber}','{b.Birthday}','{b.Occupation}','{b.MonthlySalary}'," + $"'{b.Image}', '{b.Gender}', '{b.CivilStatus}', '{year}')"; cmd = new SQLiteCommand(query, conn); int result = await cmd.ExecuteNonQueryAsync(); if (result > 0) { msg = "New Borrower is Added Successfully!"; return(true); } else { msg = "Failed to Add New Borrower! Please try again."; } } } catch (Exception ex) { msg = ex.Message; } return(false); }
public async Task <BorrowerModel> GetSingle(int id) { BorrowerModel borrower = null; msg = ""; try { using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString())) { dt.Clear(); conn.Open(); string query = $"SELECT * FROM Borrowers WHERE Id = '{id}';"; cmd = new SQLiteCommand(query, conn); await cmd.ExecuteNonQueryAsync(); da = new SQLiteDataAdapter(cmd); da.Fill(dt); DataRow row = dt.Rows[0]; borrower = new BorrowerModel() { Id = Convert.ToInt32(row["Id"]), FirstName = Convert.ToString(row["FirstName"]), LastName = Convert.ToString(row["LastName"]), MiddleName = Convert.ToString(row["MiddleName"]), ContactNumber = Convert.ToString(row["ContactNumber"]), Address = Convert.ToString(row["Address"]), Occupation = Convert.ToString(row["Occupation"]), Birthday = Convert.ToDateTime(row["Birthday"]), MonthlySalary = Convert.ToDecimal(row["MonthlySalary"]), Prefix = Convert.ToString(row["Prefix"]), Image = Convert.ToString(row["Image"]), CivilStatus = Convert.ToString(row["CivilStatus"]), Gender = Convert.ToString(row["Gender"]) }; } } catch (Exception ex) { msg = ex.Message; } return(borrower); }
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); }