public List <EmployeeM> GetEmployees()
{
List <EmployeeM> employees = new List <EmployeeM>();
_command.CommandText = "SELECT * FROM Employee WHERE CprNO IS NOT NULL";
PrepareSql();
SqlDataReader reader = null;
reader = _command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
EmployeeM e = new EmployeeM();
e.Id = reader["ID"] != DBNull.Value ? Convert.ToInt32(reader["ID"]) : default(int);
e.CprNo = reader["CprNo"] != DBNull.Value ? reader["CprNo"].ToString() : string.Empty;
e.FirstName = reader["FirstName"] != DBNull.Value ? reader["FirstName"].ToString() : string.Empty;
e.LastName = reader["LastName"] != DBNull.Value ? reader["LastName"].ToString() : string.Empty;
e.Address = reader["Address"] != DBNull.Value ? reader["Address"].ToString() : string.Empty;
e.PostNo = reader["PostNo"] != DBNull.Value ? Convert.ToInt32(reader["PostNo"]) : default(int);
e.Email = reader["Email"] != DBNull.Value ? reader["Email"].ToString() : string.Empty;
e.TlfNo = reader["TlfNo"] != DBNull.Value ? reader["TlfNo"].ToString() : string.Empty;
e.StartDate = Convert.ToDateTime(reader["StartDate"]);
e.Position = reader["Position"] != DBNull.Value ? reader["Position"].ToString() : string.Empty;
e.PayRatePrHour = reader["PayRatePrHour"] != DBNull.Value ? Convert.ToDecimal(reader["PayRatePrHour"]) : default(decimal);
employees.Add(e);
}
}
_connection.Close();
return(employees);;
}
public void addEmployee(EmployeeM EM)
{
string spName = @"[dbo].[addEmployee]";
using (SqlCommand cmd = new SqlCommand(spName, SQLConnection))
{
SqlParameter[] parms = new SqlParameter[13];
string[] names =
{
"@EPassword", "@Lname", "@Fname", "@EAddress", "@Bank_acc_no", "@Salary_rate", "@Hourly_rate", "@Pay_type", "@MecFlag", "@CFlag", "@ManFlag", "@AFlag", "@Manager_id"
};
SqlDbType[] DBtypes =
{
SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.VarChar, SqlDbType.BigInt, SqlDbType.Decimal, SqlDbType.Decimal, SqlDbType.Bit, SqlDbType.Bit, SqlDbType.Bit, SqlDbType.Bit, SqlDbType.Bit, SqlDbType.Int
};
Object[] values =
{
EM.Password, EM.Lname, EM.Fname, EM.Address, EM.Bank_acc_no, EM.Salary_rate, EM.Hourly_rate, EM.Pay_Type, EM.MecFlag, EM.CFlag, EM.ManFlag, EM.AFlag, EM.Manager_id
};
for (int i = 0; i < parms.Length; i++)
{
parms[i] = new SqlParameter();
parms[i].ParameterName = names[i];
parms[i].SqlDbType = DBtypes[i];
parms[i].Value = values[i];
cmd.Parameters.Add(parms[i]);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
}
public EmployeeM getEmployee(int employee_id)
{
EmployeeM EM = null;
string spName = @"[dbo].[getEmployee]";
using (SqlCommand cmd = new SqlCommand(spName, SQLConnection))
{
SqlParameter Emp_id_Parameter = new SqlParameter();
Emp_id_Parameter.ParameterName = "@Employee_id";
Emp_id_Parameter.SqlDbType = SqlDbType.Int;
Emp_id_Parameter.Value = employee_id;
cmd.Parameters.Add(Emp_id_Parameter);
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
int test = 0;
while (reader.Read())
{
if (test > 0)
{
throw new Exception("more then one employee with the PK: " + employee_id + ", key values have been destroyed");
}
EM = new EmployeeM(reader);
}
}
}
}
return(EM);
}
// Constructor.
public EmployeeVM()
{
myEmployeeService = new EmployeeService();
LoadData();
SelectedEmployee = new EmployeeM();
addCommand = new RelayCommand(Add);
searchCommand = new RelayCommand(Search);
updateCommand = new RelayCommand(Update);
deleteCommand = new RelayCommand(Delete);
}
internal int UpdateEmployee(int id, string fName, string lName, string address, int postNo, string eMail, string tlf, string position, decimal money)
{
EmployeeM emp = new EmployeeM();
emp.Id = id;
emp.FirstName = fName;
emp.LastName = lName;
emp.Address = address;
emp.PostNo = postNo;
emp.Email = eMail;
emp.TlfNo = tlf;
emp.Position = position;
emp.PayRatePrHour = money;
return(_dbEmployee.UpdateEmployee(emp));
}
internal int NewEmployee(string cpr, string fName, string lName, string address, int postNo,
string eMail, string tlf, DateTime start, string position, decimal money)
{
EmployeeM employee = new EmployeeM();
employee.CprNo = cpr;
employee.FirstName = fName;
employee.LastName = lName;
employee.Address = address;
employee.PostNo = postNo;
employee.Email = eMail;
employee.TlfNo = tlf;
employee.StartDate = start;
employee.Position = position;
employee.PayRatePrHour = money;
return(_dbEmployee.NewEmployee(employee));
}
public int UpdateEmployee(EmployeeM e)
{
_command.CommandText = "UPDATE Employee SET FirstName = @fName, LastName = @lName, " +
"Address = @Address, PostNo = @PostNo, Email = @Email, TlfNo = @TlfNo," +
"Position = @Position, PayRatePrHour = @PayRatePrHour WHERE ID = @id";
_command.Parameters.Clear();
_command.Parameters.Add(new SqlParameter("@fName", e.FirstName));
_command.Parameters.Add(new SqlParameter("@lName", e.LastName));
_command.Parameters.Add(new SqlParameter("@id", e.Id));
_command.Parameters.Add(new SqlParameter("@Address", e.Address));
_command.Parameters.Add(new SqlParameter("@PostNo", e.PostNo));
_command.Parameters.Add(new SqlParameter("@Email", e.Email));
_command.Parameters.Add(new SqlParameter("@TlfNo", e.TlfNo));
_command.Parameters.Add(new SqlParameter("@Position", e.Position));
_command.Parameters.Add(new SqlParameter("@PayRatePrHour", e.PayRatePrHour));
return(ExecuteNonQuery());
}
public int NewEmployee(EmployeeM e)
{
string sqlString = "INSERT INTO Employee (CprNo, FirstName, LastName, Address, PostNo, Email, TlfNo, StartDate, Position, PayRatePrHour) VALUES " +
" (@CprNo, @FirstName, @LastName, @Address, @PostNo, @Email, @TlfNo, @StartDate, @Position, @PayRatePrHour)";
_command.CommandText = sqlString;
_command.Parameters.Clear();
_command.Parameters.Add(new SqlParameter("@CprNo", e.CprNo));
_command.Parameters.Add(new SqlParameter("@FirstName", e.FirstName));
_command.Parameters.Add(new SqlParameter("@LastName", e.LastName));
_command.Parameters.Add(new SqlParameter("@Address", e.Address));
_command.Parameters.Add(new SqlParameter("@PostNo", e.PostNo));
_command.Parameters.Add(new SqlParameter("@Email", e.Email));
_command.Parameters.Add(new SqlParameter("@TlfNo", e.TlfNo));
_command.Parameters.Add(new SqlParameter("@StartDate", e.StartDate));
_command.Parameters.Add(new SqlParameter("@Position", e.Position));
_command.Parameters.Add(new SqlParameter("@PayRatePrHour", e.PayRatePrHour));
return(ExecuteNonQuerySqlReturnId());
}
public ActionResult Create_Post()
{
EmployeeM Employee = new EmployeeM();
TryUpdateModel(Employee);
if (ModelState.IsValid)
{
string ErrMsg = clsM_Employee.SaveEmployee(Employee, commonFunctions.GetTransactionUser());
if (ErrMsg == string.Empty)
{
return(RedirectToAction("Create"));
}
}
ViewBag.EmployeeCategory = getEmployeeCategory();
ViewBag.Bank = getBank();
return(View(Employee));
}
public ActionResult Create_Post()
{
EmployeeM Employee = new EmployeeM();
TryUpdateModel(Employee);
if (ModelState.IsValid)
{
string ErrMsg = clsM_Employee.SaveEmployee(Employee, commonFunctions.GetTransactionUser());
if (ErrMsg == string.Empty)
{
TempData["Message"] = new MessageBox {
CssClassName = "alert-success", Title = "Success!", Message = "Employee has been created"
};
return(RedirectToAction("Create"));
}
}
ViewBag.EmployeeCategory = getEmployeeCategory();
ViewBag.Bank = getBank();
return(View(Employee));
}
public int NewEmployee(EmployeeM employee)
{
return(1);
}
public int UpdateEmployee(EmployeeM emp)
{
return(1);
}
public string SaveEmployee(EmployeeM Employee, string username)
{
p = new SqlParameter[17];
p[0] = new SqlParameter("@EmployeeCategory", SqlDbType.VarChar)
{
Value = Employee.EmployeeCategory
};
p[1] = new SqlParameter("@FirstName", SqlDbType.VarChar)
{
Value = Employee.FirstName
};
p[2] = new SqlParameter("@LastName", SqlDbType.VarChar)
{
Value = Employee.LastName
};
p[3] = new SqlParameter("@NickName", SqlDbType.VarChar)
{
Value = Employee.NickName
};
p[4] = new SqlParameter("@Address1", SqlDbType.VarChar)
{
Value = Employee.Address1
};
p[5] = new SqlParameter("@Address2", SqlDbType.VarChar)
{
Value = Employee.Address2
};
p[6] = new SqlParameter("@Address3", SqlDbType.VarChar)
{
Value = Employee.Address3
};
p[7] = new SqlParameter("@Telephone1", SqlDbType.VarChar)
{
Value = Employee.Telephone1
};
p[8] = new SqlParameter("@Telephone2", SqlDbType.VarChar)
{
Value = Employee.Telephone2
};
p[9] = new SqlParameter("@BankCode", SqlDbType.VarChar)
{
Value = Employee.BankCode
};
p[10] = new SqlParameter("@BranchCode", SqlDbType.VarChar)
{
Value = Employee.BranchCode
};
p[11] = new SqlParameter("@AccountNo", SqlDbType.VarChar)
{
Value = Employee.AccountNo
};
p[12] = new SqlParameter("@Email", SqlDbType.VarChar)
{
Value = Employee.Email
};
p[13] = new SqlParameter("@NICNo", SqlDbType.VarChar)
{
Value = Employee.NICNo
};
p[14] = new SqlParameter("@Dob", SqlDbType.Date)
{
Value = Employee.Dob
};
p[15] = new SqlParameter("@Username", SqlDbType.VarChar)
{
Value = username
};
p[16] = new SqlParameter("@ERRMSG", SqlDbType.VarChar, 400);
p[16].Direction = ParameterDirection.Output;
SqlHelper.ExecuteNonQuery(clsConnectionString.getConnectionString(), CommandType.StoredProcedure, "spInsertEmployee", p);
return(p[16].Value.ToString());
}
