/// <summary>
/// Run a query to get all Officers
/// </summary>
public List <CommissioningOfficerModel> GetOfficers()
{
var Officers = new List <CommissioningOfficerModel>();
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["PhotoConsentDB"].ToString()))
{
cn.Open();
var sql = string.Format("SELECT * FROM CommissioningOfficer");
SqlCommand sqlCommand = new SqlCommand(sql, cn);
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var model = new CommissioningOfficerModel();
model.FormID = (int)reader["FormID"];
model.Name = (string)reader["Name"];
model.OfficerID = (int)reader["OfficerID"];
model.ContactNumber = reader["ContactNumber"] == DBNull.Value ? "" : (string)reader["ContactNumber"];
model.Email = reader["Email"] == DBNull.Value ? "" : (string)reader["Email"];
Officers.Add(model);
}
cn.Close();
}
return(Officers);
}
public ActionResult EditOfficer(CommissioningOfficerModel model)
{
// store the old details of the officer before the edit.
// get all instances of the officer using the old model.
var OldModel = _databaseService.GetOfficerByID(model.OfficerID);
var OfficerInstances = _databaseService.GetOfficers().Where(x => x.Name == OldModel.Name && x.Email == OldModel.Email && x.ContactNumber == OldModel.ContactNumber);
// for each instance, update the instance to the new model.
foreach (var Instance in OfficerInstances)
{
model.OfficerID = Instance.OfficerID;
_databaseService.UpdateCommissioningOfficer(model);
}
// if the form id is 1, then the user is on the manageCommOfficers view, and should be redirected there.
// Otherwise redirect back to the form view
if (model.FormID == 1)
{
return(RedirectToRoute("ManageCommOfficers"));
}
else
{
return(RedirectToRoute("ViewForm", new { ID = model.FormID }));
}
}
public ActionResult DeleteOfficer(CommissioningOfficerModel model)
{
_databaseService.DeleteCommissioningOfficer(model);
// if the form id is 1, then the user is on the manageCommOfficers view, and should be redirected there.
// Otherwise redirect back to the form view
if (model.FormID == 1)
{
return(RedirectToRoute("ManageCommOfficers"));
}
else
{
return(RedirectToRoute("ViewForm", new { ID = model.FormID }));
}
}
public ActionResult CreateOfficer(CommissioningOfficerModel model)
{
var FormId = model.FormID;
_databaseService.InsertCommissioningOfficer(model);
// If the formID is 1 then the user is on the ManageCommOfficers view and should be redirected there after creation.
if (model.FormID == 1)
{
return(RedirectToRoute("ManageCommOfficers"));
}
else
{
// If the formID is not 1 then user is on a forms view and should be redirected back to that forms view after creation.
// An instance of the officer is also created on the master form.
model.FormID = 1;
_databaseService.InsertCommissioningOfficer(model);
return(RedirectToRoute("ViewForm", new { ID = FormId }));
}
}
/// <summary>
/// Run a query to get a single Officer by id
/// </summary>
/// <param name="OfficerID"> int </param>
public CommissioningOfficerModel GetOfficerByID(int OfficerID)
{
var Officer = new CommissioningOfficerModel();
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["PhotoConsentDB"].ToString()))
{
cn.Open();
var sql = string.Format("SELECT * FROM CommissioningOfficer Where OfficerID = {0}", OfficerID);
SqlCommand sqlCommand = new SqlCommand(sql, cn);
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
Officer.FormID = (int)reader["FormID"];
Officer.Name = (string)reader["Name"];
Officer.OfficerID = (int)reader["OfficerID"];
Officer.ContactNumber = reader["ContactNumber"] == DBNull.Value ? "" : (string)reader["ContactNumber"];
Officer.Email = reader["Email"] == DBNull.Value ? "" : (string)reader["Email"];
}
cn.Close();
}
return(Officer);
}
public void DeleteCommissioningOfficer(CommissioningOfficerModel model)
{
_db.Execute("EXEC DeleteCommissioningOfficer @OfficerID", new { model.OfficerID });
}
public void UpdateCommissioningOfficer(CommissioningOfficerModel model)
{
_db.Execute("EXEC UpdateCommissioningOfficer @Name, @Email, @ContactNumber, @OfficerID", new { model.Name, model.Email, model.ContactNumber, model.OfficerID });
}
/// <summary>
/// Run SP to input a Commissioning Officer into the database
/// </summary>
/// <param name="model">CommissioningOfficerModel - </param>
public void InsertCommissioningOfficer(CommissioningOfficerModel model)
{
_db.Execute("EXEC InsertCommissioningOfficer @FormID, @Name, @Email, @ContactNumber", new { model.FormID, model.Name, model.Email, model.ContactNumber });
}
