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