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