public void InsertNewShiftgroup()
{
ShiftGroup ShiftGroup = new VacationManagement.ShiftGroup("bff0f95a-5b1a-42cc-a1cc-8f520477c37a","d2dd4ad0-7c53-497e-aa5e-d9d09132076b");
bool Result = false;
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"INSERT INTO [dbo].[Shiftgroup] VALUES (@EmployeeID01, @EmployeeID02)";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("EmployeeID01", ShiftGroup.getEmployeeID01());
cmd.Parameters.AddWithValue("EmployeeID02", ShiftGroup.getEmployeeID02());
try
{
connection.Open();
cmd.ExecuteNonQuery();//insert/delete/update is nonQuery
Result = true;
}
catch (SqlException e) //if Failure in Database then Result = false
{
{
Result = false;
}
}
}
}
Assert.AreEqual(true, Result);
}
public ActionResult Create(ShiftGroup ShiftGroup)
{
try
{
_dbq.InsertNewShiftgroup(ShiftGroup);
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// [ValidateAntiForgeryToken]
public ActionResult Details(EmployeeDetailsViewItem EmployeeDetails, string id, string Lastname, string LastName)
{
try
{
//context.Entry(Roles).State = System.Data.Entity.EntityState.Modified;
//context.SaveChanges();
_dbq.ChangeRole(id, EmployeeDetails.RoleName);
if (EmployeeDetails.ShiftGroupPartnerName == null)
{
return RedirectToAction("Index");
}
if (_dbq.CheckForShiftgroup(id) == true)
{
_dbq.DeleteShiftGroup(id);
}
ShiftGroup UpdateShiftGroup = new ShiftGroup(EmployeeDetails.ShiftGroupPartnerName, id);
_dbq.InsertNewShiftgroup(UpdateShiftGroup);
EmployeeDetails.UserId = id;
_dbq.UpdateEmployeebyDetail(EmployeeDetails);
return RedirectToAction("Index");
}
catch
{
return View();
}
}
public Boolean InsertNewShiftgroup(ShiftGroup Shiftgroup)
{
bool Result = false;
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"INSERT INTO [dbo].[Shiftgroup] VALUES (@EmployeeID01, @EmployeeID02)";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("EmployeeID01", Shiftgroup.getEmployeeID01());
cmd.Parameters.AddWithValue("EmployeeID02", Shiftgroup.getEmployeeID02());
try
{
connection.Open();
cmd.ExecuteNonQuery();//insert/delete/update is nonQuery
UpdateNewShiftgroupInUser(Shiftgroup);
Result = true;
}
catch (SqlException e) //if Failure in Database then Result = false
{
{
Result = false;
}
}
}
}
return Result;
}
public Boolean UpdateNewShiftgroupInUser(ShiftGroup Shiftgroup)
{
bool Result = false;
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"UPDATE dbo.[AspNetUsers] SET ShiftGroupID = @ShiftgroupId WHERE Id = @EmployeeID01 OR Id = @EmployeeID02";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("ShiftgroupId", SelectShiftGroupId(Shiftgroup.getEmployeeID01()));
cmd.Parameters.AddWithValue("EmployeeID01", Shiftgroup.getEmployeeID01());
cmd.Parameters.AddWithValue("EmployeeID02", Shiftgroup.getEmployeeID02());
try
{
connection.Open();
cmd.ExecuteNonQuery();//insert/delete/update is nonQuery
Result = true;
}
catch (SqlException e) //if Failure in Database then Result = false
{
{
Result = false;
}
}
}
}
return Result;
}
public List<ShiftGroup> SelectShiftGroupOfDivision()
{
string EmployeeID01tmp = "c";
string EmployeeID02tmp = "c";
int ShiftGroupIDtmp = 0;
string EmployeeID01_FullName_tmp;
string EmployeeID02_FullName_tmp;
var ShiftList = new List<ShiftGroup>();
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT * FROM dbo.ShiftGroup";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check if the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
// Save DB-Return into variables
// wihout Role and StaffID
EmployeeID01tmp = reader.GetString(reader.GetOrdinal("EmployeeID01"));
EmployeeID02tmp = reader.GetString(reader.GetOrdinal("EmployeeID02"));
ShiftGroupIDtmp = reader.GetInt32(reader.GetOrdinal("ShiftGroupID"));
EmployeeID01_FullName_tmp = SelectEmployeeFullName(EmployeeID01tmp);
EmployeeID02_FullName_tmp = SelectEmployeeFullName(EmployeeID02tmp);
ShiftGroup DivShiftgroup = new ShiftGroup(ShiftGroupIDtmp, EmployeeID01tmp, EmployeeID02tmp, EmployeeID01_FullName_tmp, EmployeeID02_FullName_tmp);
ShiftList.Add(DivShiftgroup);
}
}
}
}
}
return ShiftList;
}
public ShiftGroup SelectShiftGroup(String EmployeeId)
{
string EmployeeID01tmp = "c";
string EmployeeID02tmp = "c";
int ShiftGroupIDtmp = 0;
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT * FROM dbo.ShiftGroup WHERE EmployeeID01 = @EmployeeId OR EmployeeID02 = @EmployeeId";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("EmployeeId", EmployeeId);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
cmd.Parameters.AddWithValue("EmployeeID01", EmployeeId);
// Check if the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
// Save DB-Return into variables
// wihout Role and StaffID
EmployeeID01tmp = reader.GetString(reader.GetOrdinal("EmployeeID01"));
EmployeeID02tmp = reader.GetString(reader.GetOrdinal("EmployeeID02"));
ShiftGroupIDtmp = reader.GetInt32(reader.GetOrdinal("ShiftGroupID"));
ShiftGroup Shiftgroup = new ShiftGroup(ShiftGroupIDtmp, EmployeeID01tmp, EmployeeID02tmp);
return Shiftgroup;
}
}
}
return null;
}
}
}