private void buttonsearch_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = Database_Connection.OpenCon();
string query = "select * from user_info where user_name='" + bunifuMaterialTextboxusername.Text + "'";
SqlCommand cmd = new SqlCommand(query, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dataGridView1.DataSource = dt;
//cmd.Parameters.Add("@fullname", bunifuMaterialTextboxfullname.Text);
//cmd.Parameters.Add("@username", bunifuMaterialTextboxusername.Text);
//cmd.Parameters.Add("@password", bunifuMaterialTextboxpassword.Text);
//cmd.Parameters.Add("@email", bunifuMaterialTextboxemail.Text);
//cmd.Parameters.Add("@usertype", bunifuMaterialTextboxusertype.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
}
catch (Exception ex)
{
MessageBox.Show("Check");
}
}
private void Buttonupdate_Click(object sender, EventArgs e)
{
if (bunifuMaterialTextboxusername.Text == string.Empty)
{
MessageBox.Show("For update enter username");
}
else
{
try
{
SqlConnection con = Database_Connection.OpenCon();
string query = "update user_info set full_name='" + bunifuMaterialTextboxfullname.Text + "',password='******',email='" + bunifuMaterialTextboxemail.Text + "',user_type='" + bunifuMaterialTextboxusertype.Text + "' where user_name='" + bunifuMaterialTextboxusername.Text + "'";
SqlCommand cmd = new SqlCommand(query, con);
//cmd.Parameters.Add("@fullname", bunifuMaterialTextboxfullname.Text);
//cmd.Parameters.Add("@username", bunifuMaterialTextboxusername.Text);
//cmd.Parameters.Add("@password", bunifuMaterialTextboxpassword.Text);
//cmd.Parameters.Add("@email", bunifuMaterialTextboxemail.Text);
//cmd.Parameters.Add("@usertype", bunifuMaterialTextboxusertype.Text);
cmd.ExecuteNonQuery();
MessageBox.Show("update successfully");
display();
Database_Connection.CloseCon();
}
catch (Exception ex)
{
MessageBox.Show("Check");
}
}
}
/// <summary>
/// Verify Documents from the list
/// </summary>
/// <param name="eid"></param>
/// <param name="lid"></param>
/// <param name="model"></param>
/// <returns></returns>
public ActionResult Verify(int eid, int lid, DocumentViewModel model)
{
string cmd = "SELECT * FROM Lookup";
SqlDataReader reader = Database_Connection.get_instance().Getdata(cmd);
while (reader.Read())
{
if (reader.GetString(1) == "Verified")
{
model.Status = reader.GetInt32(0);
}
}
EmployeeLoan ob = new EmployeeLoan();
string cmmd = "SELECT * FROM Lookup";
SqlDataReader readerrr = Database_Connection.get_instance().Getdata(cmmd);
while (readerrr.Read())
{
if (readerrr.GetString(1) == "Approve")
{
ob.Status = readerrr.GetInt32(0);
}
}
string cmd3 = string.Format("UPDATE Document SET Status = '{0}' WHERE EmployeeId= '{1}' AND LoanId= '{2}'", model.Status, eid, lid);
int rows = Database_Connection.get_instance().Executequery(cmd3);
string cmd4 = string.Format("UPDATE EmployeeLoan SET Status = '{0}' WHERE EmployeeId= '{1}' AND LoanId= '{2}'", ob.Status, eid, lid);
int row = Database_Connection.get_instance().Executequery(cmd4);
return(RedirectToAction("Listofdocuments", "Admin"));
}
private void Buttondelete_Click(object sender, EventArgs e)
{
if (bunifuMaterialTextboxid.Text == string.Empty)
{
MessageBox.Show("for delete please enter your id");
}
else
{
try
{
SqlConnection con = Database_Connection.OpenCon();
string query = "delete from Bus_info where Id='" + bunifuMaterialTextboxid.Text + "'";
SqlCommand cmd = new SqlCommand(query, con);
//cmd.Parameters.Add("@id", bunifuMaterialTextboxid.Text);
//cmd.Parameters.Add("@name", bunifuMaterialTextboxname.Text);
//cmd.Parameters.Add("@to", bunifuDropdownto.selectedValue);
//cmd.Parameters.Add("@from", bunifuDropdownfrom.selectedValue);
//cmd.Parameters.Add("@time", bunifuDropdowntime.selectedValue);
//cmd.Parameters.Add("@date", bunifuDatepickerdate.Value);
//cmd.Parameters.Add("@fare", bunifuMaterialTextboxfare.Text);
cmd.ExecuteNonQuery();
MessageBox.Show("delete successfully");
display();
Database_Connection.CloseCon();
}
catch (Exception ex)
{
MessageBox.Show("exception");
}
}
}
private void Buttonupdate_Click(object sender, EventArgs e)
{
if (bunifuMaterialTextboxid.Text == string.Empty)
{
MessageBox.Show("For update information please enter your id");
}
try
{
SqlConnection con = Database_Connection.OpenCon();
string query = "update Bus_info set Name='" + bunifuMaterialTextboxname.Text + "',To_1='" + bunifuDropdownto.selectedValue + "',From_1='" + bunifuDropdownfrom.selectedValue + "',Time='" + bunifuDropdowntime.selectedValue + "',Date='" + bunifuDatepickerdate.Value + "',Fare='" + bunifuMaterialTextboxfare.Text + "' where ID='" + bunifuMaterialTextboxid.Text + "'";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add("@id", bunifuMaterialTextboxid.Text);
cmd.Parameters.Add("@name", bunifuMaterialTextboxname.Text);
cmd.Parameters.Add("@to", bunifuDropdownto.selectedValue);
cmd.Parameters.Add("@from", bunifuDropdownfrom.selectedValue);
cmd.Parameters.Add("@time", bunifuDropdowntime.selectedValue);
cmd.Parameters.Add("@date", bunifuDatepickerdate.Value);
cmd.Parameters.Add("@fare", bunifuMaterialTextboxfare.Text);
cmd.ExecuteNonQuery();
display();
MessageBox.Show("Update successfully");
Database_Connection.CloseCon();
}
catch (Exception ex)
{
MessageBox.Show("you r missed values");
}
}
/// <summary>
/// Show pending request's to admin
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult Index(string id)
{
try
{
Request obj = new Request();
int Id = Int32.Parse(id);
string userid = User.Identity.GetUserName();
var person = db.Employees.Where(y => y.Email == userid).First();
obj.EmployeeId = person.Id;
obj.LoanId = Id;
string cmd = "SELECT * FROM Lookup";
SqlDataReader reader = Database_Connection.get_instance().Getdata(cmd);
while (reader.Read())
{
if (reader.GetString(1) == "Pending")
{
obj.Status = reader.GetInt32(0);
}
}
db.Requests.Add(obj);
db.SaveChanges();
return(RedirectToAction("Index", "Account"));
}
catch (Exception ex)
{
throw (ex);
}
}
public ActionResult AddInstallments(int id, InstallmentViewModel model)
{
if (ModelState.IsValid)
{
model.LoanId = id;
string cmd = "SELECT * FROM Loan";
SqlDataReader reader = Database_Connection.get_instance().Getdata(cmd);
while (reader.Read())
{
if (reader.GetInt32(0) == id)
{
model.Amount = reader.GetInt32(3) / model.Plan;
}
}
string cmd2 = string.Format("INSERT INTO Installement(LoanId,InstallementPlan,Amount) VALUES('{0}','{1}','{2}')", model.LoanId, model.Plan, model.Amount);
int rows = Database_Connection.get_instance().Executequery(cmd2);
// con.Close();
return(RedirectToAction("ViewInstallment", "Admin", new { id = id }));
}
// If we got this far, something failed, redisplay form
return(View(model));
}
private void Buttondelete_Click(object sender, EventArgs e)
{
if (bunifuMaterialTextboxusername.Text == string.Empty)
{
MessageBox.Show("For delete enter username");
}
else
{
try
{
SqlConnection con = Database_Connection.OpenCon();
string query = "delete from user_info where user_name='" + bunifuMaterialTextboxusername.Text + "'";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
MessageBox.Show("delete successfully");
display();
Database_Connection.CloseCon();
}
catch (Exception ex)
{
MessageBox.Show("Check");
}
}
}
public ActionResult Index()
{
//database connection
Database_Connection.get_instance().connectionstring = "Data Source=HAIER-PC;Initial Catalog=DB64;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework";
var con = Database_Connection.get_instance().Getconnection();
return(View());
}
public void display()
{
SqlConnection con = Database_Connection.OpenCon();
string query = "select * from Bus_info";
SqlCommand cmd = new SqlCommand(query, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dataGridView1.DataSource = dt;
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
}
private void Buttoninsert_Click(object sender, EventArgs e)
{
if (bunifuMaterialTextboxfullname.Text == string.Empty)
{
MessageBox.Show("enter your fullname");
}
else if (bunifuMaterialTextboxusername.Text == string.Empty)
{
MessageBox.Show("enter your username");
}
else if (bunifuMaterialTextboxpassword.Text == string.Empty)
{
MessageBox.Show("enter your password");
}
else if (bunifuMaterialTextboxemail.Text == string.Empty)
{
MessageBox.Show("enter your email");
}
else if (bunifuMaterialTextboxusertype.Text == string.Empty)
{
MessageBox.Show("enter your usertype");
}
else
{
try
{
SqlConnection con = Database_Connection.OpenCon();
string query = "insert into user_info (full_name,user_name,password,email,user_type) values (@fullname,@username,@password,@email,@usertype)";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add("@fullname", bunifuMaterialTextboxfullname.Text);
cmd.Parameters.Add("@username", bunifuMaterialTextboxusername.Text);
cmd.Parameters.Add("@password", bunifuMaterialTextboxpassword.Text);
cmd.Parameters.Add("@email", bunifuMaterialTextboxemail.Text);
cmd.Parameters.Add("@usertype", bunifuMaterialTextboxusertype.Text);
cmd.ExecuteNonQuery();
MessageBox.Show("insert successfully");
display();
Database_Connection.CloseCon();
}
catch (Exception ex)
{
MessageBox.Show("check");
}
}
}
public ActionResult AddDocuments(int id, DocumentViewModel model, IEnumerable <HttpPostedFileBase> ImageFile)
{
model.temp = temp2.Count();
string userid = User.Identity.GetUserName();
var person = db.Employees.Where(y => y.Email == userid).First();
int i = 0;
foreach (var files in ImageFile)
{
Document obj = new Document();
obj.Title = temp2[i];
obj.EmployeeId = person.Id;
obj.LoanId = id;
string cmd = "SELECT * FROM Lookup";
SqlDataReader reader = Database_Connection.get_instance().Getdata(cmd);
while (reader.Read())
{
if (reader.GetString(1) == "NotVerified")
{
obj.Status = reader.GetInt32(0);
}
}
string fileName = Path.GetFileName(files.FileName);
string extension = Path.GetExtension(files.FileName);
fileName = fileName + DateTime.Now.ToString("yymmssfff") + extension;
model.Document = "~/Image/" + fileName;
fileName = Path.Combine(Server.MapPath("~/Image/"), fileName);
model.ImageFile.SaveAs(fileName);
obj.RequiredDocuments = model.Document;
db.Documents.Add(obj);
db.SaveChanges();
i++;
}
//foreach (EmployeeLoan e in db.EmployeeLoans)
//{
// if (e.EmployeeId != person.Id && e.LoanId != id && e.InstallementId != tem)
// {
// }
// else {
DateTime date = DateTime.Now.Date;
string cmd2 = string.Format("INSERT INTO EmployeeLoan(EmployeeId,LoanId,InstallementId,Status,AssignDate) VALUES('{0}','{1}','{2}','{3}','{4}')", person.Id, id, tem, 2, date);
int rows = Database_Connection.get_instance().Executequery(cmd2);
temp2 = null;
return(RedirectToAction("Index", "Account"));
}
protected void Page_Load(object sender, EventArgs e)
{
HttpCookie cookie = Request.Cookies["user_id"];//getting the user_id
Database_Connection con = new Database_Connection();
//checking if the cookie exists and if it doesn't to create it
if (cookie == null)
{
Unnamed_LoggingOut();
//creating a default user id
cookie = new HttpCookie("user_id");
cookie.Value = "0";
cookie.Expires = DateTime.Now.AddDays(1);
// cookie.Path = Request.ApplicationPath;
Response.Cookies.Add(cookie);
}
//checking if the user is an default user or custom user and update the UI appropraitely
if (cookie.Value == "0")
{
cart.Visible = false;
carts.HRef = "";
cart_amount.HRef = "";
add_prod.Visible = false;
}
else
{
Process_Executor exec = new Process_Executor();
cart.Visible = true;
carts.HRef = "~/CART_DATA/CART_INFO.aspx";
cart_amount.HRef = "~/CART_DATA/CART_INFO.aspx";
user.InnerText = "Hello, " + exec.get_user_name(cookie.Value);
login_state.Visible = true;
logout_state.Visible = false;
cart_amount.InnerText = exec.cart_items_count(cookie.Value);
//checking if the user is an admin user to unlock the admin functionality appropriately
if (exec.get_user_type(cookie.Value) == "ADMIN")
{
add_prod.Visible = true;
}
else
{
add_prod.Visible = false;
}
}
}
public ActionResult AddPackages(LoanViewModel model)
{
if (ModelState.IsValid)
{
string cmd2 = string.Format("INSERT INTO Loan(Detail,TermsAndConditions,Amount,DocumentRequired) VALUES('{0}','{1}','{2}','{3}')", model.Details, model.TermsAndConditions, model.Amount, model.Documentsrequired);
int rows = Database_Connection.get_instance().Executequery(cmd2);
// con.Close();
return(RedirectToAction("Dashboard", "Admin"));
}
// If we got this far, something failed, redisplay form
return(View(model));
}
public ActionResult AddPolicy(int id, PolicyViewModel model)
{
if (ModelState.IsValid)
{
model.LoanId = id;
string cmd2 = string.Format("INSERT INTO PolicyAndRules(LoanId,Detail,Rank,Salary) VALUES('{0}','{1}','{2}','{3}')", model.LoanId, model.Details, model.Rank, model.Salary);
int rows = Database_Connection.get_instance().Executequery(cmd2);
// con.Close();
return(RedirectToAction("Loanlist", "Admin"));
}
// If we got this far, something failed, redisplay form
return(View(model));
}
public async Task <ActionResult> Register(RegisterViewModel model)
{
if (ModelState.IsValid)
{
var user = new ApplicationUser {
UserName = model.Email, Email = model.Email
};
var result = await UserManager.CreateAsync(user, model.Password);
if (result.Succeeded)
{
await SignInManager.SignInAsync(user, isPersistent : false, rememberBrowser : false);
// For more information on how to enable account confirmation and password reset please visit https://go.microsoft.com/fwlink/?LinkID=320771
// Send an email with this link
// string code = await UserManager.GenerateEmailConfirmationTokenAsync(user.Id);
// var callbackUrl = Url.Action("ConfirmEmail", "Account", new { userId = user.Id, code = code }, protocol: Request.Url.Scheme);
// await UserManager.SendEmailAsync(user.Id, "Confirm your account", "Please confirm your account by clicking <a href=\"" + callbackUrl + "\">here</a>");
if (model.Email == "*****@*****.**" && model.Password == "Secret-1")
{
string cmd2 = string.Format("INSERT INTO Admin(EmailId,Password) VALUES('{0}','{1}')", model.Email, model.Password);
int rows = Database_Connection.get_instance().Executequery(cmd2);
return(RedirectToAction("dashboard", "Admin"));
}
else
{
string cmd2 = string.Format("INSERT INTO Employee(FirstName,LastName,Contact,Email,Rank,Salary) VALUES('{0}','{1}','{2}','{3}','{4}','{5}')", model.FirstName, model.LastName, model.Contact, model.Email, model.Rank, model.Salary);
int rows = Database_Connection.get_instance().Executequery(cmd2);
// con.Close();
return(RedirectToAction("Index", "Home"));
}
}
AddErrors(result);
}
// If we got this far, something failed, redisplay form
return(View(model));
}
protected override void Execute(CodeActivityContext context)
{
var table = DT.Get(context);
var connection = Database_Connection.Get(context);
var t = Table.Get(context);
connection.Open();
MySqlCommand com = connection.CreateCommand();
for (int i = 0; i < table.Rows.Count; i++)
{
com.CommandText = "INSERT INTO " + t + " VALUES(@First_Name, @Last_Name)";
com.Parameters.AddWithValue("@First_Name", table.Rows[i]["First_Name"]);
com.Parameters.AddWithValue("@Last_Name", table.Rows[i]["Last_Name"]);
com.ExecuteNonQuery();
}
connection.Close();
}
public void display()
{
SqlConnection con = Database_Connection.OpenCon();
string query = "select * from user_info";
SqlCommand cmd = new SqlCommand(query, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dataGridView1.DataSource = dt;
//cmd.Parameters.Add("@fullname", bunifuMaterialTextboxfullname.Text);
//cmd.Parameters.Add("@username", bunifuMaterialTextboxusername.Text);
//cmd.Parameters.Add("@password", bunifuMaterialTextboxpassword.Text);
//cmd.Parameters.Add("@email", bunifuMaterialTextboxemail.Text);
//cmd.Parameters.Add("@usertype", bunifuMaterialTextboxusertype.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
}
public void Ss()
{
string s = bunifuMaterialTextboxid.Text;
int i_id = Int32.Parse(s);
SqlConnection con = Database_Connection.OpenCon();
string query1 = "insert into seat_status (seat_no , status, trip_id) values ('a1' , 'free' , '" + i_id + "' )";
SqlCommand cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query2 = "insert into seat_status (seat_no , status, trip_id) values ('a2' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query2, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query3 = "insert into seat_status (seat_no , status, trip_id) values ('a3' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query3, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query4 = "insert into seat_status (seat_no , status, trip_id) values ('a4' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query4, con);
cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query5 = "insert into seat_status (seat_no , status, trip_id) values ('b1' , 'free' , @tip )";
cmd = new SqlCommand(query5, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query6 = "insert into seat_status (seat_no , status, trip_id) values ('b2' , 'free' , @tip )";
cmd = new SqlCommand(query6, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query7 = "insert into seat_status (seat_no , status, trip_id) values ('b3' , 'free' , @tip )";
cmd = new SqlCommand(query7, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query8 = "insert into seat_status (seat_no , status, trip_id) values ('b4' , 'free' , @tip )";
cmd = new SqlCommand(query8, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query9 = "insert into seat_status (seat_no , status, trip_id) values ('c1' , 'free' , @tip )";
cmd = new SqlCommand(query9, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query10 = "insert into seat_status (seat_no , status, trip_id) values ('c2' , 'free' , @tip )";
cmd = new SqlCommand(query10, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query11 = "insert into seat_status (seat_no , status, trip_id) values ('c3' , 'free' , @tip )";
cmd = new SqlCommand(query11, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query12 = "insert into seat_status (seat_no , status, trip_id) values ('c4' , 'free' , @tip )";
cmd = new SqlCommand(query12, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query13 = "insert into seat_status (seat_no , status, trip_id) values ('d1' , 'free' , @tip )";
cmd = new SqlCommand(query13, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query14 = "insert into seat_status (seat_no , status, trip_id) values ('d2' , 'free' , @tip )";
cmd = new SqlCommand(query14, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query15 = "insert into seat_status (seat_no , status, trip_id) values ('d3' , 'free' , @tip )";
cmd = new SqlCommand(query15, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query16 = "insert into seat_status (seat_no , status, trip_id) values ('d4' , 'free' , @tip )";
cmd = new SqlCommand(query16, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query17 = "insert into seat_status (seat_no , status, trip_id) values ('e1' , 'free' , @tip )";
cmd = new SqlCommand(query17, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query18 = "insert into seat_status (seat_no , status, trip_id) values ('e2' , 'free' , @tip )";
cmd = new SqlCommand(query18, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query19 = "insert into seat_status (seat_no , status, trip_id) values ('e3' , 'free' , @tip )";
cmd = new SqlCommand(query19, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query20 = "insert into seat_status (seat_no , status, trip_id) values ('e4' , 'free' , @tip )";
cmd = new SqlCommand(query20, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query21 = "insert into seat_status (seat_no , status, trip_id) values ('f1' , 'free' , @tip )";
cmd = new SqlCommand(query21, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query22 = "insert into seat_status (seat_no , status, trip_id) values ('f2' , 'free' , @tip )";
cmd = new SqlCommand(query22, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query23 = "insert into seat_status (seat_no , status, trip_id) values ('f3' , 'free' , @tip )";
cmd = new SqlCommand(query23, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
con = Database_Connection.OpenCon();
string query24 = "insert into seat_status (seat_no , status, trip_id) values ('f4' , 'free' , @tip )";
cmd = new SqlCommand(query24, con);
cmd.Parameters.Add("@tip", bunifuMaterialTextboxid.Text);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
}
/// <summary>
/// Add repayment schedule for approved loan's in database
/// </summary>
/// <param name="id"></param>
/// <param name="eid"></param>
/// <param name="insid"></param>
/// <param name="lid"></param>
/// <returns></returns>
public ActionResult AddRepaymentSchedule(int id, int eid, int insid, int lid)
{
int instId;
int instPlan;
foreach (RepaymentSchedule d in db.RepaymentSchedules)
{
if (d.EmployeeId == eid && d.InstallementId == insid)
{
TempData["msg"] = "<script>alert('Repayment Schedule for this particular Employee loan is already generated.');</script>";
return(RedirectToAction("EmployeeLoan", "Admin"));
}
}
foreach (EmployeeLoan u in db.EmployeeLoans)
{
if (u.Id == id)
{
instId = u.InstallementId;
foreach (Installement i in db.Installements)
{
if (i.Id == instId)
{
int j = 0;
instPlan = i.InstallementPlan;
//add installments list with number of installment's selected and monthly date
while (instPlan != 0)
{
RepaymentSchedule obj = new RepaymentSchedule();
obj.EmployeeId = u.EmployeeId;
obj.InstallementId = i.Id;
obj.LoanId = i.LoanId;
obj.Amount = i.Amount;
obj.Date = DateTime.Now.Date.AddMonths(j);
string cmdd = "SELECT * FROM Lookup";
SqlDataReader readerr = Database_Connection.get_instance().Getdata(cmdd);
while (readerr.Read())
{
if (readerr.GetString(1) == "Due")
{
obj.Status = readerr.GetInt32(0);
}
}
//Insert in repaymentSchedule table in database
SqlConnection cn = new SqlConnection(@"Data Source=HAIER-PC;Initial Catalog=DB64;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework");
cn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO RepaymentSchedule(EmployeeId,InstallementId,LoanId,Amount,Date,Status) VALUES (@eid,@iid,@lid,@amount,@date,@status)", cn);
cmd.Parameters.AddWithValue("@eid", obj.EmployeeId);
cmd.Parameters.AddWithValue("@iid", obj.InstallementId);
cmd.Parameters.AddWithValue("@lid", obj.LoanId);
cmd.Parameters.AddWithValue("@amount", obj.Amount);
cmd.Parameters.AddWithValue("@date", obj.Date);
cmd.Parameters.AddWithValue("@status", obj.Status);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
j++;
instPlan--;
}
}
}
}
}
return(RedirectToAction("RepaymentSchedule", "Admin"));
}
/// <summary>
/// deduct salary of employee monthly
/// </summary>
/// <param name="date"></param>
/// <param name="lid"></param>
/// <param name="inid"></param>
/// <param name="eid"></param>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult SalaryDeduct(DateTime date, int lid, int inid, int eid, int id)
{
foreach (RepaymentSchedule s in db.RepaymentSchedules)
{
if (s.Status == 6 && s.EmployeeId == eid && s.LoanId == lid && s.InstallementId == inid && s.Id == id)
{
TempData["msg"] = "<script>alert('Salary Deduction for this particular Employee loan is already done.');</script>";
return(RedirectToAction("EmployeeLoan", "Admin"));
}
}
SalaryDeduction obj = new SalaryDeduction();
int tempamount = 0;
string cmd0 = "SELECT * FROM RepaymentSchedule";
SqlDataReader reader0 = Database_Connection.get_instance().Getdata(cmd0);
while (reader0.Read())
{
if (reader0.GetInt32(0) == eid && reader0.GetInt32(1) == inid)
{
tempamount = reader0.GetInt32(2);
break;
}
}
obj.Month = DateTimeFormatInfo.CurrentInfo.GetAbbreviatedMonthName(date.Month);
//if (date.Month == 01)
// obj.Month = "January";
//else if (date.Month == 02)
// obj.Month = "Feburary";
//else
var person = db.Employees.Where(y => y.Id == eid).First();
int temp = person.Salary;
obj.SalaryAfterDeduction = person.Salary - tempamount;
obj.LoanId = lid;
obj.InstallementId = inid;
obj.EmployeeId = eid;
obj.Date = DateTime.Now;
SqlConnection cn = new SqlConnection(@"Data Source=HAIER-PC;Initial Catalog=DB64;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework");
cn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO SalaryDeduction(EmployeeId,InstallementId,LoanId,SalaryAfterDeduction,Date,Month) VALUES (@eid,@inid,@lid,@amount,@date,@month)", cn);
cmd.Parameters.AddWithValue("@eid", obj.EmployeeId);
cmd.Parameters.AddWithValue("@inid", obj.InstallementId);
cmd.Parameters.AddWithValue("@lid", obj.LoanId);
cmd.Parameters.AddWithValue("@amount", obj.SalaryAfterDeduction);
cmd.Parameters.AddWithValue("@date", DateTime.Now.Date);
cmd.Parameters.AddWithValue("@month", obj.Month);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
//now updating status in repayment schedule table
RepaymentSchedule model = new RepaymentSchedule();
string cmd1 = "SELECT * FROM Lookup";
SqlDataReader reader = Database_Connection.get_instance().Getdata(cmd1);
while (reader.Read())
{
if (reader.GetString(1) == "Paid")
{
model.Status = reader.GetInt32(0);
}
}
string cmd3 = string.Format("UPDATE RepaymentSchedule SET Status = '{0}' WHERE EmployeeId= '{1}' AND LoanId= '{2}' AND InstallementId= '{3}' AND Id= '{4}'", model.Status, eid, lid, inid, id);
int rows = Database_Connection.get_instance().Executequery(cmd3);
return(RedirectToAction("RepaymentSchedule", "Admin"));
}
private void Buttoninsert_Click(object sender, EventArgs e)
{
if (bunifuMaterialTextboxname.Text == string.Empty)
{
MessageBox.Show("enter your name");
}
else if (bunifuMaterialTextboxid.Text == string.Empty)
{
MessageBox.Show("enter your id");
}
else if (bunifuDropdownto.selectedValue == string.Empty)
{
MessageBox.Show("enter your value");
}
else if (bunifuDropdownfrom.selectedValue == string.Empty)
{
MessageBox.Show("enter your value");
}
else if (bunifuDropdowntime.selectedValue == string.Empty)
{
MessageBox.Show("enter your time");
}
else if (bunifuMaterialTextboxfare.Text == string.Empty)
{
MessageBox.Show("enter your fare");
}
else
{
try
{
SqlConnection con = Database_Connection.OpenCon();
string query = "insert into Bus_info (Name,ID,To_1,From_1,Date,Time,Fare,Type,seat) values (@name,@id,@to,@from,@date,@time,@fare,@type,@seat)";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add("@name", bunifuMaterialTextboxname.Text);
cmd.Parameters.Add("@id", bunifuMaterialTextboxid.Text);
cmd.Parameters.Add("@to", bunifuDropdownto.selectedValue);
cmd.Parameters.Add("@from", bunifuDropdownfrom.selectedValue);
cmd.Parameters.Add("@time", bunifuDropdowntime.selectedValue);
cmd.Parameters.Add("@date", bunifuDatepickerdate.Value);
cmd.Parameters.Add("@fare", bunifuMaterialTextboxfare.Text);
cmd.Parameters.Add("@type", bunifuMaterialTextboxtype.Text);
cmd.Parameters.Add("@seat", bunifuMaterialTextboxseat.Text);
cmd.ExecuteNonQuery();
MessageBox.Show("insert successfully");
display();
Database_Connection.CloseCon();
Ss();
}
catch (Exception ex)
{
MessageBox.Show("check");
}
}
}
public void seatStts()
{
string s = bunifuMaterialTextboxid.Text;
int i_id = Int32.Parse(s);
SqlConnection con = Database_Connection.OpenCon();
string query1 = "insert into seat_status (seat_no , status, trip_id) values ('a1' , 'free' , '" + i_id + "' )";
SqlCommand cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('a2' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('a3' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('a4' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('b1' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('b2' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('b3' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('b4' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('c1' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('c2' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('c3' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('c4' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('d1' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('d2' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('d3' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('d4' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('e1' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('e2' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('e3' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('e4' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('f1' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('f2' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
query1 = "insert into seat_status (seat_no , status, trip_id) values ('f3' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
////query1 = "insert into seat_status (seat_no , status, trip_id) values ('f4' , 'free' , '" + i_id + "' )";
cmd = new SqlCommand(query1, con);
//cmd.Parameters.Add("@tip", s);
cmd.ExecuteNonQuery();
Database_Connection.CloseCon();
}