void UpdateLoan1(Loans1 l)
{
Loans1C inter = new Loans1C();
Loan1Checks(l);
inter.UpdateLoan1(l);
}
public void approveLoan1(Loans1 l)
{
ClientC inter = new ClientC();
Client c = inter.FindClient(l.ClientId);
c.MiddleProcess = "1app";
inter.UpdateClient(c);
}
void Loan1Checks(Loans1 l)//check if all the files filled
{
if (l.BusinessName == "")
{
throw new Exception("The Business Legal Name Field Is Empty");
}
if (l.Pcontact == "")
{
throw new Exception("The Pirmary Contact Field Is Empty");
}
if (l.TaxID == "")
{
throw new Exception("The Tax ID Field Is Empty");
}
if (l.SS == "")
{
throw new Exception("The Social Security Field Is Empty");
}
if (l.Tel == "")
{
throw new Exception("The Telephone Field Is Empty");
}
if (l.Mail == "")
{
throw new Exception("The Email Address Field Is Empty");
}
if (l.Street == "")
{
throw new Exception("The Street Address Field Is Empty");
}
if (l.City == "")
{
throw new Exception("The City Field Is Empty");
}
if (l.State == "")
{
throw new Exception("The State Field Is Empty");
}
if (l.Zip == "")
{
throw new Exception("The Zip Field Is Empty");
}
if (l.DateBE == "")
{
throw new Exception("The Date Business Established Field Is Empty");
}
if (l.StateoOI == "")
{
throw new Exception("The State of Incorporatin Field Is Empty");
}
if (l.EmployeesNum == "")
{
throw new Exception("The Number of Employees After Financing Field Is Empty");
}
}
public List <Loans1> AllOpenLoans1()
{
List <Loans1> myL = new List <Loans1>();
//search
while (con.State == ConnectionState.Open)
{
System.Threading.Thread.Sleep(500);
}
con.Open();
MySqlCommand cmd = new MySqlCommand("select * from Loans1 where Close=@close", con);
cmd.Parameters.AddWithValue("@close", Convert.ToInt32(false));
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Loans1 l = new Loans1();
l.Id = int.Parse(reader["id"].ToString());
l.BusinessName = reader["BusinessName"].ToString();
l.DBAName = reader["DBAName"].ToString();
l.Pcontact = reader["Pcontact"].ToString();
l.TaxID = reader["TaxID"].ToString();
l.SS = reader["SS"].ToString();
l.Tel = reader["Tel"].ToString();
l.Street = reader["Street"].ToString();
l.Own = Convert.ToBoolean(int.Parse(reader["Own"].ToString()));
l.City = reader["City"].ToString();
l.State = reader["State"].ToString();
l.Zip = reader["Zip"].ToString();
l.Mstreet = reader["Mstreet"].ToString();
l.Mcity = reader["Mcity"].ToString();
l.Mstate = reader["Mstate"].ToString();
l.Mzip = reader["Mzip"].ToString();
l.Chose1 = reader["Chose1"].ToString();
l.DateBE = reader["DateBE"].ToString();
l.StateoOI = reader["StateoOI"].ToString();
l.EmployeesNum = reader["EmployeesNum"].ToString();
l.Explains = reader["Explains"].ToString();
l.Chose2 = reader["Chose2"].ToString();
l.Acompanies = reader["Acompanies"].ToString();
l.Approved = Convert.ToBoolean(int.Parse(reader["Approved"].ToString()));
l.ClientId = reader["ClientId"].ToString();
l.Close = Convert.ToBoolean(int.Parse(reader["Close"].ToString()));
myL.Add(l);
}
con.Close();
return(myL);
}
public void CreateLoan1(Loans1 l)
{
l.Id = getId();
while (con.State == ConnectionState.Open)
{
System.Threading.Thread.Sleep(500);
}
con.Open();
string query = "INSERT INTO Loans1 (Id,BusinessName,DBAName,Pcontact,TaxID,SS,Tel,Mail,Street,Own,City,State,Zip,Mstreet,Mcity,Mstate,Mzip,Chose1,DateBE,StateoOI,EmployeesNum,Explains,Chose2,Acompanies,Approved,ClientId,Close) VALUES (@Id,@BusinessName,@DBAName,@Pcontact,@TaxID,@SS,@Tel,@Mail,@Street,@Own,@City,@State,@Zip,@Mstreet,@Mcity,@Mstate,@Mzip,@Chose1,@DateBE,@StateoOI,@EmployeesNum,@Explain,@Chose2,@Acompanies,@Approved,@ClientId,@Close)";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@Id", l.Id);
cmd.Parameters.AddWithValue("@BusinessName", l.BusinessName);
cmd.Parameters.AddWithValue("@DBAName", l.DBAName);
cmd.Parameters.AddWithValue("@Pcontact", l.Pcontact);
cmd.Parameters.AddWithValue("@TaxID", l.TaxID);
cmd.Parameters.AddWithValue("@SS", l.SS);
cmd.Parameters.AddWithValue("@Tel", l.Tel);
cmd.Parameters.AddWithValue("@Mail", l.Mail);
cmd.Parameters.AddWithValue("@Street", l.Street);
cmd.Parameters.AddWithValue("@Own", l.Own);
cmd.Parameters.AddWithValue("@City", l.City);
cmd.Parameters.AddWithValue("@State", l.State);
cmd.Parameters.AddWithValue("@Zip", l.Zip);
cmd.Parameters.AddWithValue("@Mstreet", l.Mstreet);
cmd.Parameters.AddWithValue("@Mcity", l.Mcity);
cmd.Parameters.AddWithValue("@Mstate", l.Mstate);
cmd.Parameters.AddWithValue("@Mzip", l.Mzip);
cmd.Parameters.AddWithValue("@Chose1", l.Chose1);
cmd.Parameters.AddWithValue("@DateBE", l.DateBE);
cmd.Parameters.AddWithValue("@StateoOI", l.StateoOI);
cmd.Parameters.AddWithValue("@EmployeesNum", l.EmployeesNum);
cmd.Parameters.AddWithValue("@Explain", l.Explains);
cmd.Parameters.AddWithValue("@Chose2", l.Chose2);
cmd.Parameters.AddWithValue("@Acompanies", l.Acompanies);
cmd.Parameters.AddWithValue("@Approved", l.Approved);
cmd.Parameters.AddWithValue("@ClientId", l.ClientId);
cmd.Parameters.AddWithValue("@Close", Convert.ToInt32(l.Close));
cmd.ExecuteNonQuery();
con.Close();
ClientC inter = new ClientC();
Client c = inter.FindClient(l.ClientId);
c.MiddleProcess = "1";
inter.UpdateClient(c);
}
void CreatLoans1(Loans1 l)//create a loan
{
Loan1Checks(l);
Loans1C inter = new Loans1C();
inter.CreateLoan1(l);
ClientC client = new ClientC();
Client c = client.FindClient(l.ClientId);
c.MiddleProcess = "1";
client.UpdateClient(c);
//send mail to the manager
#region send mail to employee
//need to change
string managerMail = "*****@*****.**";
var fromAddress = new MailAddress("*****@*****.**", "Cavalry Associates");
var toAddress = new MailAddress(managerMail, c.Fname + " " + c.Lname + " Ask For Loan");
const string fromPassword = "******";
string subject = c.Fname + " " + c.Lname + " Ask For Loan";
string body = "<h1> The Client: " + c.Fname + " " + c.Lname + " " + c.Mail + " Ask For A Loan</h1>" +
"<img src=\"https://app.synccos.com/assets/images/cavalry-signin.png\" alt=\"Trulli\" width=\"500\" height=\"333\"></p>";
var smtp = new SmtpClient
{
Host = "smtp.gmail.com",
Port = 587,
EnableSsl = true,
DeliveryMethod = SmtpDeliveryMethod.Network,
UseDefaultCredentials = false,
Credentials = new NetworkCredential(fromAddress.Address, fromPassword)
};
using (var message = new MailMessage(fromAddress, toAddress)
{
Subject = subject,
Body = body
})
{
message.IsBodyHtml = true;
smtp.Send(message);
}
#endregion
}
public void Submit(Loans1 l, string status, string uid)
{
if (status == "Client")
{
ClientC interc = new ClientC();
if (interc.FindIfClientHasLoan(l.ClientId))
{
//ClientGlobal global = new ClientGlobal();
UpdateLoan1(FindLoan1ByClient(l.ClientId));
throw new Exception("The First Stage Has Been Successfully Updated");
}
else
{
Loans1 l1 = new Loans1(FindLoan1ByClient(l.ClientId));
l.Id = l1.Id;
CreatLoans1(l);
UserC u = new UserC();
SendEmailC send = new SendEmailC();
ClientC c = new ClientC();
Client c1 = c.FindClient(uid);
string message = "Client: " + c1.Mail + " " + c1.MobileNum + " " + c1.Fname + " " + c1.Lname + "Want a loan";
send.SendEmailF(u.FindNeedEmail("Manager", uid), "Ask For Loan" + c1.Fname + " " + c1.Lname, message);
}
}
else
{
//status will be the employee id
UpdateLoan1(FindLoan1ByClient(l.ClientId));
ClientGlobal global = new ClientGlobal();
Client c = global.FindClient(l.ClientId);
if (c.MiddleProcess == "1")
{
c.MiddleProcess = "1app";
global.UpdateClient(c);
}
SendEmailC send = new SendEmailC();
ClientC clientC = new ClientC();
Client c1 = clientC.FindClient(uid);
string message = "Hello " + c1.Fname + " " + c1.Lname + "We wanted to let you know that the application approved";
send.SendEmailF(c1.Mail, "Approved Application", message);
}
}
public void UpdateLoan1(Loans1 l)
{
//change
while (con.State == ConnectionState.Open)
{
System.Threading.Thread.Sleep(500);
}
con.Open();
string query = "UPDATE Loans1 SET BusinessName=@BusinessName,DBAName=@DBAName,Pcontact=@Pcontact,TaxID=@TaxID,SS=@SS,Tel=@Tel,Mail=@Mail,Street=@Street,Own=@Own,City=@City,State=@State,Zip=@Zip,Mstreet=@Mstreet,Mcity=@Mcity,Mstate=@Mstate,Mzip=@Mzip,Chose1=@Chose1,DateBE=@DateBE,StateoOI=@StateoOI,EmployeesNum=@EmployeesNum,Explains=@Explain,Chose2=@Chose2,Acompanies=@Acompanies,Approved=@Approved,ClientId=@ClientId,Close=@Close WHERE Id=@id";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@Id", l.Id);
cmd.Parameters.AddWithValue("@BusinessName", l.BusinessName);
cmd.Parameters.AddWithValue("@DBAName", l.DBAName);
cmd.Parameters.AddWithValue("@Pcontact", l.Pcontact);
cmd.Parameters.AddWithValue("@TaxID", l.TaxID);
cmd.Parameters.AddWithValue("@SS", l.SS);
cmd.Parameters.AddWithValue("@Tel", l.Tel);
cmd.Parameters.AddWithValue("@Mail", l.Mail);
cmd.Parameters.AddWithValue("@Street", l.Street);
cmd.Parameters.AddWithValue("@Own", l.Own);
cmd.Parameters.AddWithValue("@City", l.City);
cmd.Parameters.AddWithValue("@State", l.State);
cmd.Parameters.AddWithValue("@Zip", l.Zip);
cmd.Parameters.AddWithValue("@Mstreet", l.Mstreet);
cmd.Parameters.AddWithValue("@Mcity", l.Mcity);
cmd.Parameters.AddWithValue("@Mstate", l.Mstate);
cmd.Parameters.AddWithValue("@Mzip", l.Mzip);
cmd.Parameters.AddWithValue("@Chose1", l.Chose1);
cmd.Parameters.AddWithValue("@DateBE", l.DateBE);
cmd.Parameters.AddWithValue("@StateoOI", l.StateoOI);
cmd.Parameters.AddWithValue("@EmployeesNum", l.EmployeesNum);
cmd.Parameters.AddWithValue("@Explain", l.Explains);
cmd.Parameters.AddWithValue("@Chose2", l.Chose2);
cmd.Parameters.AddWithValue("@Acompanies", l.Acompanies);
cmd.Parameters.AddWithValue("@Approved", l.Approved);
cmd.Parameters.AddWithValue("@ClientId", l.ClientId);
cmd.Parameters.AddWithValue("@Close", Convert.ToInt32(l.Close));
cmd.ExecuteNonQuery();
con.Close();
}
public Loans1 pageLoad(string clientId)
{
Loans1 l = FindLoan1ByClient(clientId);
if (l.Id == -1)
{
ClientGlobal global = new ClientGlobal();
Client c = global.FindClient(clientId);
l.Tel = c.MobileNum;
l.Mail = c.Mail;
l.Street = c.Address;
l.Mstreet = c.Address;
l.City = c.City;
l.Mcity = c.City;
l.State = c.State;
l.Mstate = c.State;
l.Zip = c.Zip;
l.Mzip = c.Zip;
l.DateBE = " / ";
}
return(l);
}
protected void fill(Loans1 l)
{
BusinessLegalName.Text = l.BusinessName;
BusinessLegalNameE.Text = l.BusinessName;
dbaName.Text = l.DBAName;
dbaNameE.Text = l.DBAName;
PrimaryContact.Text = l.Pcontact;
PrimaryContactE.Text = l.Pcontact;
taxid.Text = l.TaxID;
taxidE.Text = l.TaxID;
sstxt.Text = l.SS;
sstxtE.Text = l.SS;
telephone.Text = l.Tel;
telephoneE.Text = l.Tel;
emailAddress.Text = l.Mail;
emailAddressE.Text = l.Mail;
Address.Text = l.Street;
AddressE.Text = l.Street;
own.Checked = l.Own;
ownE.Checked = l.Own;
rent.Checked = !l.Own;
rentE.Checked = !l.Own;
city.Text = l.City;
cityE.Text = l.City;
state.Text = l.State;
stateE.Text = l.State;
zip.Text = l.Zip;
zipE.Text = l.Zip;
mailAddress.Text = l.Mstreet;
mailAddressE.Text = l.Mstreet;
mailCity.Text = l.Mcity;
mailCityE.Text = l.Mcity;
mailState.Text = l.Mstate;
mailStateE.Text = l.Mstate;
mailZip.Text = l.Mzip;
mailZipE.Text = l.Mzip;
month.Text = l.DateBE.Split('/')[0];
year.Text = l.DateBE.Split('/')[1];
monthE.Text = l.DateBE.Split('/')[0];
yearE.Text = l.DateBE.Split('/')[1];
incorporationState.Text = l.StateoOI;
incorporationStateE.Text = l.StateoOI;
employeesNum.Text = l.EmployeesNum;
employeesNumE.Text = l.EmployeesNum;
explain.Text = l.Explains;
explainE.Text = l.Explains;
listCompanies.Text = l.Acompanies;
listCompaniesE.Text = l.Acompanies;
string list1 = l.Chose1;
string list2 = l.Chose2;
othertxt2.Visible = false;
othertxt.Visible = false;
othertxt2E.Visible = false;
othertxtE1.Visible = false;
bool f = true;
if (list1 == "C Corporation")
{
Ccorporation.Checked = true;
Ccorporation.Checked = true;
f = false;
}
if (list1 == "S Corporation")
{
f = false;
SCorporation.Checked = true;
SCorporationE.Checked = true;
}
if (list1 == "Limited Liability")
{
f = false;
LLC.Checked = true;
LLCE.Checked = true;
}
if (list1 == "Business Individual")
{
f = false;
businessIndividual.Checked = true;
businessIndividualE.Checked = true;
}
if (list1 == "Non-Profit")
{
f = false;
NonProfit.Checked = true;
NonProfitE.Checked = true;
}
if (list1 == "Sole Proprietor")
{
f = false;
soleProprietor.Checked = true;
soleProprietorE.Checked = true;
}
if (list1 == "Limited Partnership")
{
f = false;
limitedPartnership.Checked = true;
limitedPartnershipE.Checked = true;
}
if (list1 == "General Partnership")
{
f = false;
generalPartnership.Checked = true;
generalPartnershipE.Checked = true;
}
if (list1 == "LLP")
{
f = false;
LLP.Checked = true;
LLPE.Checked = true;
}
if (f)
{
other.Checked = true;
othertxt.Visible = true;
othertxt.Text = list1;
otherE1.Checked = true;
othertxtE1.Visible = true;
othertxtE1.Text = list1;
}
f = true;
if (list2 == "Manufacturing")
{
f = false;
manufacturing.Checked = true;
manufacturingE.Checked = true;
}
if (list2 == "Wholesale")
{
f = false;
wholesaleE.Checked = true;
wholesale.Checked = true;
}
if (list2 == "Retail")
{
f = false;
retailE.Checked = true;
retail.Checked = true;
}
if (list2 == "Service")
{
f = false;
service.Checked = true;
serviceE.Checked = true;
}
if (list2 == "Agricultural")
{
f = false;
agriculturalE.Checked = true;
agricultural.Checked = true;
}
if (list2 == "Construction")
{
f = false;
construction.Checked = true;
constructionE.Checked = true;
}
if (f)
{
other2.Checked = true;
othertxt2.Visible = true;
othertxt2.Text = list2;
other2E.Checked = true;
othertxt2E.Visible = true;
othertxt2E.Text = list2;
}
}
public void SubmitLoan1(Loans1 l1, string clientId)
{
Loans1 l = new Loans1();
AllLoansFunction allLF = new AllLoansFunction();
if (allLF.ClientHasLoan(clientId))
{
l = new Loans1(FindLoan1ByClient(clientId));
l1.Id = l.Id;
l = new Loans1(l1);
}
else
{
l = new Loans1(l1);
l.Id = -1;
}
ClientC client = new ClientC();
if (client.FindIfClientHasLoan(l.ClientId))
{
throw new Exception("You Are In The Middle Of A Loan Application Process");
}
if (l.BusinessName == "")
{
throw new Exception("The Business Legal Name Field Is Empty");
}
if (l.Pcontact == "")
{
throw new Exception("The Pirmary Contact Field Is Empty");
}
if (l.TaxID == "")
{
throw new Exception("The Tax ID Field Is Empty");
}
if (l.SS == "")
{
throw new Exception("The Social Security Field Is Empty");
}
if (l.Tel == "")
{
throw new Exception("The Telephone Field Is Empty");
}
if (l.Mail == "")
{
throw new Exception("The Email Address Field Is Empty");
}
if (l.Street == "")
{
throw new Exception("The Street Address Field Is Empty");
}
if (l.City == "")
{
throw new Exception("The City Field Is Empty");
}
if (l.State == "")
{
throw new Exception("The State Field Is Empty");
}
if (l.Zip == "")
{
throw new Exception("The Zip Field Is Empty");
}
if (l.DateBE == "")
{
throw new Exception("The Date Business Established Field Is Empty");
}
if (l.StateoOI == "")
{
throw new Exception("The State of Incorporatin Field Is Empty");
}
if (l.EmployeesNum == "")
{
throw new Exception("The Number of Employees After Financing Field Is Empty");
}
if (l.Id == -1)
{
CreatLoans1(l);
}
else
{
UpdateLoan1(l);
}
}
private void BtnSaveApplication_Click(object sender, EventArgs e)
{
if (Program.GetMemberSystemID(txtidnumber.Text) == "")
{
MessageBox.Show("Please provide a valid Identification Number", "SACCO MIS", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtidnumber.Focus();
return;
}
if (txtamount.Value < 1)
{
MessageBox.Show("The loan amount can not be zero or less than zero", "SACCO MIS", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtamount.Focus();
return;
}
//Process and Save Loan Details
string sql = "Insert into Loans(MemberID,LoanAmount,Period,Status,ApplicationDate,ApprovalDate,DisburseDate,TotalInterest,PaymentAmount)" +
"values(@MID,@LAmount,@Period,@Status,@ApplicationDate,@ApprovalDate,@DisburseDate,@TotalInterest,@Amount)";
string status = Properties.Settings.Default.AppliedLoanStatus;
string ApprovalDate = "", DisburseDate = "";
if (chkapprove.Checked)
{
status = Properties.Settings.Default.ApprovedLoanStatus;
ApprovalDate = txtdate.Value.ToString();
}
if (chkdisburse.Checked)
{
status = Properties.Settings.Default.DisbursedLoanStatus;
ApprovalDate = txtdate.Value.ToString();
DisburseDate = txtdate.Value.TimeOfDay.ToString();
}
//Calculate Interest & Repayable Amount
int years = (int)(Convert.ToDecimal(txtperiod.Text) / 12);
// int InstallationsPerYear = (int)txtinstallations.Value / years;
double TotalAmount = Loans1.SimpleInterest((double)txtamount.Value, Properties.Settings.Default.interest, years);
double interest = TotalAmount - (double)txtamount.Value;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("MID", Program.GetMemberSystemID(txtidnumber.Text));
cmd.Parameters.AddWithValue("LAmount", txtamount.Value);
cmd.Parameters.AddWithValue("Period", txtperiod.Text);
cmd.Parameters.AddWithValue("Status", status);
cmd.Parameters.AddWithValue("ApplicationDate", DateTime.Now);
cmd.Parameters.AddWithValue("ApprovalDate", ApprovalDate);
cmd.Parameters.AddWithValue("DisburseDate", DisburseDate);
cmd.Parameters.AddWithValue("TotalInterest", interest);
cmd.Parameters.AddWithValue("Amount", TotalAmount);
Database.CreateRecord(cmd);
MessageBox.Show("Loan Applied", "ISUGURU MIS", MessageBoxButtons.OK, MessageBoxIcon.Information);
//Clear fields
txtidnumber.Text = "";
txtamount.Text = "";
txtperiod.Text = "";
}