//Getting shelf names for the dropdown in Blue Oister bar
internal List <string> GetShelfName()
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SelectShelfName", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
List <string> shelfNames = new List <string>();
try
{
while (reader.Read())
{
//EditStorageLocationModel output = new EditStorageLocationModel();
StorageLocationModel shelfName = new StorageLocationModel((string)reader["shelfName"], 0, 0, null);
shelfNames.Add(shelfName.ShelfName);
}
}
catch (Exception)
{
throw;
}
con.Close();
return(shelfNames);
}
public IActionResult GetLocations(EditDeviceModel data)
{
//initializing DB managers
DBManagerDevice dbManager = new DBManagerDevice(configuration);
DBManagerShared shared = new DBManagerShared(configuration);
//get the logs back again
List <DeviceModel> logs = dbManager.GetDeviceLogs(data.Device.DeviceID);
int modelID = shared.GetModelID(data.Device.Model.ModelName);
data.Logs = logs;
EditDeviceModel newdata = data;
//check if image exists
string filename = $"Capture_{modelID}.png";
string imagepath = (string)AppDomain.CurrentDomain.GetData("webRootPath") + "\\DeviceContent\\" + filename;
if (System.IO.File.Exists(imagepath))
{
newdata.ImagePath = filename;
}
//fetch storage locations if user has typed a valid room
if (data.Room != null)
{
//prep data for database
string[] splittedRoom = data.Room.Split('.');
//prep data model
EditDeviceModel editData = new EditDeviceModel();
DeviceModel device = new DeviceModel();
BuildingModel building = new BuildingModel(splittedRoom[0], Convert.ToByte(splittedRoom[1]));
StorageLocationModel storageLocation = new StorageLocationModel();
storageLocation.Location = building;
device.Location = storageLocation;
editData.Device = device;
//get storagelocations
EditDeviceModel locations = dbManager.GetStorageLocations(editData);
newdata.Shelfs = locations.Shelfs;
newdata.Shelf = null;
}
//return the same data without having selected anything
else
{
EditDeviceModel storagelocation = dbManager.GetStorageLocations(null);
newdata.Rooms = storagelocation.Rooms;
newdata.Shelf = null;
}
// clear model
ModelState.Clear();
return(View("EditView", newdata));
}
/// <summary>
/// Acquires the location of a random Device of the modelName which is currently in storage.
/// </summary>
/// <param name="modelName"></param>
/// <returns></returns>
internal StorageLocationModel GetModelLocation(string modelName)
{
StorageLocationModel location = new StorageLocationModel();
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetAStorageLocationForModel", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@modelName", modelName);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
location.ShelfName = (string)reader["shelfName"];
location.ShelfLevel = (byte)reader["shelfLevel"];
location.ShelfSpot = (byte)reader["shelfSpot"];
location.Location = new BuildingModel(
(string)reader["buildingName"],
(byte)reader["roomNr"]
);
}
con.Close();
return(location);
}
//saves new location on device to database
public IActionResult EditLocation(EditDeviceModel data)
{
//initializing DB managers
DBManagerDevice dbManager = new DBManagerDevice(configuration);
DBManagerShared shared = new DBManagerShared(configuration);
data.Device.ChangedBy = HttpContext.Session.GetString("uniLogin");
//prep data for database
string[] splittedRoom = data.Room.Split('.');
string[] splittedShelf = data.Shelf.Split('.');
//set data models
BuildingModel building = new BuildingModel(splittedRoom[0], Convert.ToByte(splittedRoom[1]));
StorageLocationModel storageLocation = new StorageLocationModel(splittedShelf[0], Convert.ToByte(splittedShelf[1]), Convert.ToByte(splittedShelf[2]), building);
data.Device.Location = storageLocation;
data.Device.Notes = "Placering redigeret";
//send data to database
data = dbManager.EditDeviceLocation(data);
List <DeviceModel> logs = dbManager.GetDeviceLogs(data.Device.DeviceID);
data.Logs = logs;
//save Device name & other important things
//send data to database
int success = dbManager.EditDevice(data);
int modelID = shared.GetModelID(data.Device.Model.ModelName);
//check if image exists
string filename = $"Capture_{modelID}.png";
string imagepath = (string)AppDomain.CurrentDomain.GetData("webRootPath") + "\\DeviceContent\\" + filename;
if (System.IO.File.Exists(imagepath))
{
data.ImagePath = filename;
}
//set message to be shown in view
if (success > 0)
{
ViewBag.Location = "Placering Gemt";
}
else
{
ViewBag.Location = "Placering ikke Gemt";
}
return(View("EditView", data));
}
internal StorageLocationModel CreateLocation(StorageLocationModel dummy)
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("StoredProcedureName", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
con.Close();
return(null);
}
//get device info from database before edit
internal DeviceModel GetDeviceInfoWithLocation(int deviceID)
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("GetDeviceWithLocation", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@deviceID", System.Data.SqlDbType.Int).Value = deviceID;
//execute query
// cmd.ExecuteNonQuery();
#region //model for data transfer
SqlDataReader reader = cmd.ExecuteReader();
DeviceModel device = new DeviceModel();
ModelModel model = new ModelModel();
CategoryModel category = new CategoryModel();
BuildingModel building = new BuildingModel();
StorageLocationModel location = new StorageLocationModel();
model.Category = category;
location.Location = building;
#endregion
while (reader.Read())
{
device.DeviceID = (int)reader["deviceID"];
device.SerialNumber = (string)reader["serialNumber"];
device.Status = (byte)reader["status"];
model.Category.Category = (string)reader["categoryName"];
model.ModelName = (string)reader["modelName"];
model.ModelDescription = (string)reader["modelDescription"];
location.Location.RoomNumber = (byte)reader["roomNr"];
location.Location.Building = (string)reader["buildingName"];
location.ShelfName = (string)reader["shelfName"];;
location.ShelfLevel = (byte)reader["shelfLevel"];
location.ShelfSpot = (byte)reader["shelfSpot"];
}
device.Model = model;
device.Location = location;
con.Close();
return(device);
}
//Getting specific room, based on the choosen building and roomNr in the mass destruction.
internal List <StorageLocationModel> GetSpecificRoom(EditStorageLocationModel dataFromView)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SelectRoomBasedOnBuildingNameAndRoomNr", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (dataFromView.StorageLocation.Location.Building != null)
{
cmd.Parameters.Add("@buildingName", System.Data.SqlDbType.VarChar).Value = dataFromView.StorageLocation.Location.Building;
}
else
{
cmd.Parameters.Add("@buildingName", System.Data.SqlDbType.VarChar).Value = null;
}
if (dataFromView.StorageLocation.Location.RoomNumber > 0)
{
cmd.Parameters.Add("@roomNr", System.Data.SqlDbType.TinyInt).Value = dataFromView.StorageLocation.Location.RoomNumber;
}
else
{
cmd.Parameters.Add("@roomNr", System.Data.SqlDbType.TinyInt).Value = null;
}
List <StorageLocationModel> selectedRoom = new List <StorageLocationModel>();
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
StorageLocationModel selectedRooms = new StorageLocationModel();
BuildingModel buildingModel = new BuildingModel();
buildingModel.Building = (string)reader["buildingName"];
buildingModel.RoomNumber = (byte)reader["roomNr"];
selectedRooms.Location = buildingModel;
selectedRoom.Add(selectedRooms);
}
con.Close();
return(selectedRoom);
}
public EditStorageLocationModel(StorageLocationModel storageLocation, List <StorageLocationModel> storageLocations, List <string> buildings, List <byte> roomNumbers,
List <string> shelfNames, List <byte> shelfLevels, List <byte> shelfSpots, byte filter,
List <string> deleteBuildings, List <byte> deleteRoomNumbers, string deleteRoom, List <string> deleteShelfNames, List <byte> deleteShelfLevels, List <byte> deleteShelfSpots)
{
this.storageLocation = storageLocation;
this.storageLocations = storageLocations;
this.buildings = buildings;
this.roomNumbers = roomNumbers;
this.shelfNames = shelfNames;
this.shelfLevels = shelfLevels;
this.shelfSpots = shelfSpots;
this.filter = filter;
this.deleteBuildings = deleteBuildings;
this.deleteRoomNumbers = deleteRoomNumbers;
this.deleteRoom = deleteRoom;
this.deleteShelfNames = deleteShelfNames;
this.deleteShelfLevels = deleteShelfLevels;
this.deleteShelfSpots = deleteShelfSpots;
}
public IActionResult LocationAdmin(EditStorageLocationModel dataFromView)
{
//Add standard values to the model
EditStorageLocationModel initialData = new EditStorageLocationModel();
StorageLocationModel selectedStorageLocation = new StorageLocationModel();
BuildingModel buildingModel = new BuildingModel();
//SortFilterModel sortFilterModel = new SortFilterModel();
selectedStorageLocation.Location = buildingModel;
initialData.StorageLocation = selectedStorageLocation;
initialData.StorageLocation.Location.Building = null;
initialData.StorageLocation.Location.RoomNumber = 0;
initialData.StorageLocation.ShelfName = null;
initialData.StorageLocation.ShelfLevel = 0;
initialData.StorageLocation.ShelfSpot = 0;
//dummy.Filter = 1;
DBManagerAdministration manager = new DBManagerAdministration(configuration);
List <string> buildings = manager.GetBuildings();
List <byte> roomNumbers = manager.GetRoomNumbers();
List <string> shelfNames = manager.GetShelfName();
List <byte> shelfLevels = manager.GetShelfLevel();
List <byte> shelfspots = manager.GetShelfSpot();
List <StorageLocationModel> storageLocations = manager.GetSelectedStorageLocations(initialData);
EditStorageLocationModel dropDownData = new EditStorageLocationModel();
dropDownData.Buildings = buildings;
dropDownData.RoomNumbers = roomNumbers;
dropDownData.ShelfNames = shelfNames;
dropDownData.ShelfLevels = shelfLevels;
dropDownData.ShelfSpots = shelfspots;
dropDownData.StorageLocations = storageLocations;
//dropDownData.SortFilters = sortFilters;
return(View(dropDownData));
}
//Getting the list of storagelocations, based on the choices med in the Blue Oister bar.
internal List <StorageLocationModel> GetSelectedStorageLocations(EditStorageLocationModel dataFromView)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SelectLocationIDBasedOnInputFields", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (dataFromView.StorageLocation.Location.Building != null)
{
cmd.Parameters.Add("@buildingName", System.Data.SqlDbType.VarChar).Value = dataFromView.StorageLocation.Location.Building;
}
else
{
cmd.Parameters.Add("@buildingName", System.Data.SqlDbType.VarChar).Value = null;
}
if (dataFromView.StorageLocation.Location.RoomNumber > 0)
{
cmd.Parameters.Add("@roomNr", System.Data.SqlDbType.TinyInt).Value = dataFromView.StorageLocation.Location.RoomNumber;
}
else
{
cmd.Parameters.Add("@roomNr", System.Data.SqlDbType.TinyInt).Value = null;
}
if (dataFromView.StorageLocation.ShelfName != null)
{
cmd.Parameters.Add("@shelfName", System.Data.SqlDbType.VarChar).Value = dataFromView.StorageLocation.ShelfName;
}
else
{
cmd.Parameters.Add("@shelfName", System.Data.SqlDbType.VarChar).Value = null;
}
if (dataFromView.StorageLocation.ShelfLevel > 0)
{
cmd.Parameters.Add("@shelfLevel", System.Data.SqlDbType.TinyInt).Value = dataFromView.StorageLocation.ShelfLevel;
}
else
{
cmd.Parameters.Add("@shelfLevel", System.Data.SqlDbType.TinyInt).Value = null;
}
if (dataFromView.StorageLocation.ShelfSpot > 0)
{
cmd.Parameters.Add("@shelfSpot", System.Data.SqlDbType.TinyInt).Value = dataFromView.StorageLocation.ShelfSpot;
}
else
{
cmd.Parameters.Add("@shelfSpot", System.Data.SqlDbType.TinyInt).Value = null;
}
if (dataFromView.Filter > 0)
{
cmd.Parameters.Add("@filter", System.Data.SqlDbType.TinyInt).Value = dataFromView.Filter;
}
else
{
cmd.Parameters.Add("@filter", System.Data.SqlDbType.TinyInt).Value = 1;
}
List <StorageLocationModel> selectedStorageLocations = new List <StorageLocationModel>();
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
StorageLocationModel selectedStorageLocation = new StorageLocationModel();
BuildingModel buildingModel = new BuildingModel();
buildingModel.Building = (string)reader["buildingName"];
buildingModel.RoomNumber = (byte)reader["roomNr"];
selectedStorageLocation.Location = buildingModel;
selectedStorageLocation.ShelfName = (string)reader["shelfName"];
selectedStorageLocation.ShelfLevel = (byte)reader["shelfLevel"];
selectedStorageLocation.ShelfSpot = (byte)reader["shelfSpot"];
selectedStorageLocations.Add(selectedStorageLocation);
}
con.Close();
return(selectedStorageLocations);
}
//saves all edits on device to database
public IActionResult EditDevice(EditDeviceModel data)
{
//initializing DB managers
DBManagerDevice dbManager = new DBManagerDevice(configuration);
EditDeviceModel storagelocation = dbManager.GetStorageLocations(null);
//check categories
bool Category_validated = checkUserInput(data.Categories, data.Device.Model.Category.Category);
//check modelNames
bool ModelName_validated = checkUserInput(data.ModelNames, data.Device.Model.ModelName);
//check BuildingNames
bool Building_validated = checkUserInput(storagelocation.Rooms, data.Room);
//check RoomNames
bool Room_validated = checkUserInput(storagelocation.Shelfs, data.Shelf);
if (Category_validated && ModelName_validated && Building_validated && Room_validated)
{
Debug.WriteLine("success");
}
else
{
//set view bags
if (!Category_validated)
{
ViewBag.Error = "indtast en gyldig kategori";
}
else if (!ModelName_validated)
{
ViewBag.Error = "indtast et gyldigt model navn";
}
else if (!Building_validated)
{
ViewBag.LocationError = "indtast en gyldig lokation";
}
else if (!Room_validated)
{
ViewBag.LocationError = "indtast en gyldig hylde";
}
return(View("EditView", data));
}
data.Device.ChangedBy = HttpContext.Session.GetString("uniLogin");
data.Device.Notes = "Enhed redigeret";
#region saving new location
//prep data for database
string[] splittedRoom = data.Room.Split('.');
string[] splittedShelf = data.Shelf.Split('.');
//set data models
BuildingModel building = new BuildingModel(splittedRoom[0], Convert.ToByte(splittedRoom[1]));
StorageLocationModel storageLocation = new StorageLocationModel(splittedShelf[0], Convert.ToByte(splittedShelf[1]), Convert.ToByte(splittedShelf[2]), building);
data.Device.Location = storageLocation;
//data.Device.Notes = "Placering redigeret";
//send data to database
data = dbManager.EditDeviceLocation(data);
#endregion
//get the logs again
List <DeviceModel> logs = dbManager.GetDeviceLogs(data.Device.DeviceID);
data.Logs = logs;
//send data to database
int success = dbManager.EditDevice(data);
//set message to be shown in view
if (success > 0)
{
ViewBag.edit = "Enhed Gemt";
}
else
{
ViewBag.edit = "Enhed ikke Gemt";
}
return(View("EditView", data));
}
// get all devices based on search query
internal ModelInfoModel GetDeviceInventory(ModelInfoModel SearchModel)
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SearchDevices", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (SearchModel.Category != null)
{
cmd.Parameters.Add("@category", System.Data.SqlDbType.VarChar).Value = SearchModel.Category;
}
else
{
cmd.Parameters.Add("@category", System.Data.SqlDbType.VarChar).Value = null;
}
if (SearchModel.Filter > 0)
{
cmd.Parameters.Add("@filter", System.Data.SqlDbType.TinyInt).Value = SearchModel.Filter;
}
else
{
cmd.Parameters.Add("@filter", System.Data.SqlDbType.TinyInt).Value = null;
}
if (SearchModel.SearchName != null)
{
cmd.Parameters.Add("@searchName", System.Data.SqlDbType.VarChar).Value = SearchModel.SearchName;
}
else
{
cmd.Parameters.Add("@searchName", System.Data.SqlDbType.VarChar).Value = null;
}
if (SearchModel.InStock > 0)
{
cmd.Parameters.Add("@inStock", System.Data.SqlDbType.TinyInt).Value = SearchModel.InStock;
}
else
{
cmd.Parameters.Add("@inStock", System.Data.SqlDbType.TinyInt).Value = null;
}
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DeviceModel device = new DeviceModel();
ModelModel model = new ModelModel();
CategoryModel category = new CategoryModel();
StorageLocationModel location = new StorageLocationModel();
BuildingModel building = new BuildingModel();
device.DeviceID = (int)reader["deviceID"];
category.Category = (string)reader["categoryName"];
model.ModelName = (string)reader["modelName"];
building.RoomNumber = (byte)reader["roomNr"];
building.Building = (string)reader["buildingName"];
location.ShelfName = (string)reader["shelfName"];;
location.ShelfLevel = (byte)reader["shelfLevel"];
location.ShelfSpot = (byte)reader["shelfSpot"];
model.Category = category;
device.Model = model;
location.Location = building;
device.Location = location;
SearchModel.BorrowedDevices.Add(device);
}
con.Close();
return(SearchModel);
}