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