// GET: FlightPersonnel/Edit/5
public ActionResult Edit(int?id)
{
TempData["eMessage"] = null;
// Stop accessing the action if not logged in
// or account not in the "Staff" role
if ((HttpContext.Session.GetString("Role") == null) || (HttpContext.Session.GetString("Role") != "Admin"))
{
return(RedirectToAction("Index", "Home"));
}
if (id == null)
{
//Query string parameter not provided
//Return to listing page, not allowed to edit
return(RedirectToAction("Index"));
}
FlightPersonnel flightPersonnel = staffContext.GetDetails(id.Value);
if (flightPersonnel == null)
{
//Return to listing page, not allowed to edit
return(RedirectToAction("Index"));
}
ViewData["StatusList"] = GetStatus();
return(View(flightPersonnel));
}
// GET: FlightPersonnelController/Delete/5
public ActionResult Delete(int?id)
{
//Stop accessing the action if not logged in
// or account not in the "Staff" role
//if ((HttpContext.Session.GetString("Role") == null) ||
// (HttpContext.Session.GetString("Role") != "Staff"))
//{
// return RedirectToAction("Index", "Home");
//}
if (id == null)
{
//Query string parameter not provided
//Return to listing page, not allowed to edit
return(RedirectToAction("Index"));
}
FlightPersonnel flightpersonnel = flightpersonnelContext.GetDetails(id.Value);
if (flightpersonnel == null)
{
//Return to listing page, not allowed to edit
return(RedirectToAction("Index"));
}
return(View(flightpersonnel));
}
public int Add(FlightPersonnel Personnel)
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"insert into Staff (StaffName, Gender, EmailAddr, Vocation, DateEmployed)
OUTPUT INSERTED.StaffId
VALUES(@StaffName, @Gender, @EmailAddr, @Vocation, @DateEmployed)";
cmd.Parameters.AddWithValue("@StaffName", Personnel.StaffName);
//cmd.Parameters.AddWithValue("@StaffId", flightPersonnel.StaffId);
cmd.Parameters.AddWithValue("@Gender", Personnel.Gender);
cmd.Parameters.AddWithValue("@EmailAddr", Personnel.EmailAddr);
cmd.Parameters.AddWithValue("@Vocation", Personnel.Vocation);
cmd.Parameters.AddWithValue("@DateEmployed", Personnel.DateEmployed);
conn.Open();
Personnel.StaffId = (int)cmd.ExecuteScalar(); //object reference not set to an instance of an object (error) FIXED
//Personnel.Status = (string)cmd.ExecuteScalar();
//Personnel.Password = (string)cmd.ExecuteScalar();
conn.Close();
return(Personnel.StaffId);
}
public int Add(FlightPersonnel flightPersonnel)
{
//Create a SqlCommand object from connection object
SqlCommand cmd = conn.CreateCommand();
//Specify an INSERT SQL statement which will
//return the auto-generated StaffID after insertion
cmd.CommandText = @"INSERT INTO Staff (StaffName, Gender, DateEmployed, Vocation, EmailAddr, Status)
OUTPUT INSERTED.StaffID VALUES(@StaffName, @Gender, @DateEmployed, @Vocation, @EmailAddr, @Status)";
//Define the parameters used in SQL statement, value for each parameter
//is retrieved from respective class's property.
cmd.Parameters.AddWithValue("@StaffName", flightPersonnel.StaffName);
if (flightPersonnel.Gender == null)
{
cmd.Parameters.AddWithValue("@Gender", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Gender", flightPersonnel.Gender);
}
if (flightPersonnel.DateEmployed == null || flightPersonnel.DateEmployed.ToString() == "")
{
cmd.Parameters.AddWithValue("@DateEmployed", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@DateEmployed", flightPersonnel.DateEmployed);
}
if (flightPersonnel.Vocation == null || flightPersonnel.Vocation.ToString() == "")
{
cmd.Parameters.AddWithValue("@Vocation", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Vocation", flightPersonnel.Vocation);
}
cmd.Parameters.AddWithValue("@EmailAddr", flightPersonnel.EmailAddr);
cmd.Parameters.AddWithValue("@Status", flightPersonnel.Status);
//A connection to database must be opened before any operations made.
conn.Open();
//ExecuteScalar is used to retrieve the auto-generated
////StaffID after executing the INSERT SQL statement
//flightPersonnel.StaffID = (int)cmd.ExecuteScalar();
flightPersonnel.StaffID = (int)cmd.ExecuteNonQuery();
//A connection should be closed after operations.
conn.Close();
//Return id when no error occurs.
return(flightPersonnel.StaffID);
}
// GET: FlightPersonnel/Details/5
public ActionResult Details(int id)
{
// Stop accessing the action if not logged in
// or account not in the "Staff" role
if ((HttpContext.Session.GetString("Role") == null) || (HttpContext.Session.GetString("Role") != "Admin"))
{
return(RedirectToAction("Index", "Home"));
}
FlightPersonnel flightPersonnel = staffContext.GetDetails(id);
List <StaffViewModel> staffVM = MapToStaffVM(flightPersonnel);
return(View(staffVM));
}
public FlightPersonnel GetDetails(int staffId)
{
FlightPersonnel flightpersonnel = new FlightPersonnel();
//cr8 sql command object from connection object
SqlCommand cmd = conn.CreateCommand();
//Specify an INSERT SQL statement which will
//return the auto-generated StaffID after insertion
cmd.CommandText = @"SELECT * FROM Staff WHERE StaffID = @selectStaffID";
//Define the parameters used in SQL statement, value for each parameter
//is retrieved from respective class's property.
cmd.Parameters.AddWithValue("@selectStaffID", staffId);
//open db
conn.Open();
//execute select sql through datareader
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
//read record from db
while (reader.Read())
{
// Fill staff object with values from the data reader
flightpersonnel.StaffId = staffId;
flightpersonnel.StaffName = !reader.IsDBNull(1) ? reader.GetString(1) : null;
// (char) 0 - ASCII Code 0 - null value
flightpersonnel.Gender = !reader.IsDBNull(2) ?
reader.GetString(2)[0] : (char)0;
flightpersonnel.DateEmployed = !reader.IsDBNull(3) ?
reader.GetDateTime(3) : (DateTime?)null;
flightpersonnel.Vocation = !reader.IsDBNull(4) ?
reader.GetString(4) : null;
flightpersonnel.EmailAddr = !reader.IsDBNull(5) ?
reader.GetString(5) : null;
flightpersonnel.Password = !reader.IsDBNull(6) ?
reader.GetString(6) : null;
flightpersonnel.Status = !reader.IsDBNull(7) ?
reader.GetString(7) : null;
}
}
//close data reader
reader.Close();
//close db
conn.Close();
return(flightpersonnel);
}
public FlightPersonnel GetDetails(int staffId)
{
FlightPersonnel flightPersonnel = new FlightPersonnel();
//Create a SqlCommand object from connection object
SqlCommand cmd = conn.CreateCommand();
//Specify the SELECT SQL statement that
//retrieves all attributes of a staff record.
cmd.CommandText = @"SELECT * FROM Staff WHERE StaffID = @selectedStaffID";
//Define the parameter used in SQL statement, value for the
//parameter is retrieved from the method parameter “staffId”.
cmd.Parameters.AddWithValue("@selectedStaffID", staffId);
//Open a database connection
conn.Open();
//Execute SELCT SQL through a DataReader
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
//Read the record from database
while (reader.Read())
{
flightPersonnel.StaffID = reader.GetInt32(0);
flightPersonnel.StaffName = reader.GetString(1);
//flightPersonnel.Gender = reader.GetString(2)[0];
//flightPersonnel.DateEmployed = reader.GetDateTime(3);
flightPersonnel.Gender = !reader.IsDBNull(2) ? reader.GetString(2) : null;
flightPersonnel.DateEmployed = !reader.IsDBNull(3) ? reader.GetDateTime(3) : (DateTime?)null;
//flightPersonnel.Vocation = reader.GetString(4);
flightPersonnel.Vocation = !reader.IsDBNull(4) ? reader.GetString(4) : (string)null;
flightPersonnel.EmailAddr = reader.GetString(5);
flightPersonnel.Status = reader.GetString(7);
}
}
//Close data reader
reader.Close();
//Close database connection
conn.Close();
return(flightPersonnel);
}
public ActionResult Create(FlightPersonnel FlightPersonnel)
{
//in case of the need to return to Create.cshtml view
if (ModelState.IsValid)
{
//Add staff record to database
FlightPersonnel.StaffId = flightpersonnelContext.Add(FlightPersonnel);
//Redirect user to FlightSchedule/Index view
return(RedirectToAction("Index"));
}
else
{
//Input validation fails, return to the Create view
//to display error message
return(View(FlightPersonnel));
}
}
public List <StaffViewModel> MapToStaffVM(FlightPersonnel flightPersonnel)
{
string flightno = "";
int routeid = 0;
int aircraftid = 0;
string status = "";
List <StaffViewModel> staffvmList = new List <StaffViewModel>();
List <FlightSchedule> fslist = scheduleContext.GetAllFlightSchedule();
List <FlightCrew> fcList = crewContext.GetFlightCrew(flightPersonnel.StaffID);
if (flightPersonnel.StaffID != null)
{
foreach (FlightCrew fc in fcList)
{
foreach (FlightSchedule fs in fslist)
{
if (fc.ScheduleID == fs.ScheduleID)
{
flightno = fs.FlightNumber;
routeid = fs.RouteID;
aircraftid = fs.AircraftID;
status = fs.Status;
staffvmList.Add(new StaffViewModel
{
StaffID = flightPersonnel.StaffID,
StaffName = flightPersonnel.StaffName,
ScheduleID = fc.ScheduleID,
Role = fc.Role,
FlightNumber = flightno,
AircraftID = aircraftid,
RouteID = routeid,
Status = status,
});
break;
}
}
}
}
return(staffvmList);
}
public ActionResult Edit(FlightPersonnel flightPersonnel)
{
TempData["eMessage"] = null;
FlightSchedule fs = scheduleContext.GetFlightSchedule(flightPersonnel.StaffID);
DateTime currentDate = DateTime.Today;
bool check = false;
if (fs != null)
{
if (fs.DepartureDateTime > currentDate)
{
check = true;
}
else
{
check = false;
}
}
//Get status list for drop-down list
//in case of the need to return to Edit.cshtml view
if (ModelState.IsValid)
{
if (check == true)
{
//Update staff record to database
staffContext.Update(flightPersonnel);
return(RedirectToAction("Index"));
}
else
{
TempData["eMessage"] = "You cannot change the status of this staff!";
ViewData["ScheduleList"] = GetStatus();
return(View());
}
}
else
{
//Input validation fails, return to the view
//to display error message
return(View());
}
}
public ActionResult Create(FlightPersonnel flightPersonnel)
{
//Get country list for drop-down list
//in case of the need to return to Create.cshtml view
ViewData["VocationList"] = GetVocation();
ViewData["GenderList"] = GetGender();
ViewData["StatusList"] = GetStatus();
if (ModelState.IsValid)
{
//Add staff record to database
flightPersonnel.StaffID = staffContext.Add(flightPersonnel);
//Redirect user to Staff/Index view
return(RedirectToAction("Index"));
}
else
{
//Input validation fails, return to the Create view
//to display error message
return(View(flightPersonnel));
}
}
public int Update(FlightPersonnel flightPersonnel)
{
//Create a SqlCommand object from connection object
SqlCommand cmd = conn.CreateCommand();
//Specify an UPDATE SQL statement
cmd.CommandText = @"UPDATE Staff SET Status=@status WHERE StaffID = @selectedStaffID";
//Define the parameters used in SQL statement, value for each parameter
//is retrieved from respective class's property.
cmd.Parameters.AddWithValue("@status", flightPersonnel.Status);
cmd.Parameters.AddWithValue("@selectedStaffID", flightPersonnel.StaffID);
//Open a database connection
conn.Open();
//ExecuteNonQuery is used for UPDATE and DELETE
int count = (int)(cmd.ExecuteNonQuery());
//Close the database connection
conn.Close();
return(count);
}
public ActionResult Delete(FlightPersonnel flightpersonnel)
{
// Delete the staff record from database
flightpersonnelContext.Delete(flightpersonnel.StaffId);
return(RedirectToAction("Index"));
}