public async Task <bool> UpdateGuarantor(GuarantorModel g, int id)
{
try
{
using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString()))
{
conn.Open();
cmd = new SQLiteCommand($"UPDATE Guarantors SET Name = '{g.Name}' , Relation = '{g.Relationship}', ContactNumber = '{g.ContactNumber}', " +
$"Address = '{g.Address}' WHERE LoanId = '{id}';", conn);
int result = await cmd.ExecuteNonQueryAsync();
if (result > 0)
{
return(true);
}
else
{
msg = "Failed to update Guarantor";
}
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return(false);
}
public async Task <bool> AddGuarantor(GuarantorModel g, int id)
{
try
{
using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString()))
{
conn.Open();
cmd = new SQLiteCommand("INSERT INTO Guarantors (LoanId, Name, Relation, ContactNumber, Address) " +
$"VALUES('{id}', '{g.Name}','{g.Relationship}','{g.ContactNumber}','{g.Address}');", conn);
int result = await cmd.ExecuteNonQueryAsync();
if (result > 0)
{
return(true);
}
else
{
msg = "Failed to add Guarantor";
}
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return(false);
}
public async Task <bool> Update(ILoanModel l, GuarantorModel g, CollateralModel c)
{
try
{
if (await UpdateCollateral(c, l.Id) && await UpdateGuarantor(g, l.Id))
{
msg = "Loan is Updated Successfully!";
return(true);
}
msg = "Failed To Update Loan";
}
catch (Exception ex)
{
msg = ex.Message;
}
return(false);
}
public async Task <bool> Add(ILoanModel l, GuarantorModel g, CollateralModel c)
{
try
{
using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString()))
{
conn.Open();
string year = Convert.ToString(DateTime.Now.Year);
string query = "INSERT INTO Loans (BorrowerId, CollectorId, PrincipalLoan, InterestId, Interest, MaturityValue," +
"PerRemittance, Duration, EffectiveDate, MaturityDate,TotalBalance, Prefix) " +
$"VALUES('{l.BorrowerId}','{l.CollectorId}','{l.PrincipalLoan}','{l.InterestId}','{l.Interest}','{l.MaturityValue}','{l.PerRemittance}','{l.Duration}'," +
$"'{l.EffectiveDate.ToShortDateString()}', '{l.MaturityDate.ToShortDateString()}', '{l.TotalBalance}', '{year}')";
cmd = new SQLiteCommand(query, conn);
int result = await cmd.ExecuteNonQueryAsync();
if (result > 0)
{
cmd = new SQLiteCommand("SELECT last_insert_rowid()", conn);
Int64 id = Convert.ToInt64(await cmd.ExecuteScalarAsync());
int _loanId = (int)id;
loanId = _loanId;
if (await AddCollateral(c, _loanId) && await AddGuarantor(g, loanId))
{
msg = "New Loan is Added Successfully!";
return(true);
}
else
{
msg = "Failed to add collateral and guarantor";
}
}
else
{
msg = "Failed to Add New Loan! Please try again.";
}
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return(false);
}
public async Task <GuarantorModel> GetGuarantor(int id)
{
dt.Clear();
GuarantorModel guarantor = new GuarantorModel();
using (SQLiteConnection conn = new SQLiteConnection(Database.LoadConnectionString()))
{
string query = $"SELECT * FROM Guarantors WHERE LoanId = '{id}';";
conn.Open();
cmd = new SQLiteCommand(query, conn);
await cmd.ExecuteNonQueryAsync();
da = new SQLiteDataAdapter(cmd);
da.Fill(dt);
DataRow g = dt.Rows[0];
guarantor.Id = Convert.ToInt32(g["Id"]);
guarantor.Name = Convert.ToString(g["Name"]);
guarantor.Address = Convert.ToString(g["Address"]);
guarantor.ContactNumber = Convert.ToString(g["ContactNumber"]);
guarantor.Relationship = Convert.ToString(g["Relation"]);
}
return(guarantor);
}