public JsonResult GetOptions(int id)
{
List <List <object> > lists = new List <List <object> >();
List <object> locationList = new List <object>();
List <object> bu = new List <object>();
BeaconUnit bus = null;
if (Database.BeaconUnits.AsNoTracking().Where(n => n.BeaconId == id).Count() > 0)
{
bus = Database.BeaconUnits.AsNoTracking().Include(m => m.Map).Include(m => m.Unit).Single(n => n.BeaconId == id);
bu.Add(new { l = bus.Map.LocationId, m = bus.MapId, b = bus.Unit.Block, u = bus.UnitId });
}
foreach (Location l in Database.Locations.AsNoTracking())
{
locationList.Add(new
{
i = l.LocationId,
n = l.LocationName
});
}
lists.Add(locationList);
lists.Add(bu);
return(new JsonResult(lists));
}
public IActionResult GetProject(int id)
{
Project project = Database.Projects.AsNoTracking().Include(p => p.Unit).Single(input => input.ProjectId == id);
List <string> imageList = Database.Images.AsNoTracking().Where(input => input.ProjectId == project.ProjectId).Select(i => i.ImageURL).ToList();
BeaconUnit bu = Database.BeaconUnits.AsNoTracking().Include(b => b.Map).Where(b => b.UnitId == project.Unit.UnitId).FirstOrDefault();
return(new JsonResult(new { id = project.ProjectId, images = imageList, name = project.ProjectName, desc = project.ProjectInfo, bid = project.BeaconId, uid = project.UnitId, loc = bu.Map.LocationId, block = project.Unit.Block, map = bu.MapId }));
}
public IActionResult AssignUnit([FromBody] string value)
{
dynamic beaconInput = JSON.DeserializeDynamic(value);
int mi = int.Parse((string)beaconInput[2]);
int ui = int.Parse((string)beaconInput[1]);
int bi = int.Parse((string)beaconInput[0]);
if (ui == 0)
{
return(BadRequest(new { message = "Please select a unit" }));
}
if (Database.BeaconUnits.AsNoTracking().Where(b => b.BeaconId == bi && b.UnitId == ui && b.MapId == mi).Count() != 0)
{
return(BadRequest(new { message = "Record already exists" }));
}
if (Database.Projects.AsNoTracking().Where(p => p.BeaconId == bi).Count() > 0)
{
return(BadRequest(new { message = "Beacon is already being used by the exhibit " + Database.Projects.AsNoTracking().FirstOrDefault(p => p.BeaconId == bi).ProjectName }));
}
DbSet <BeaconUnit> bus = Database.BeaconUnits;
if (bus.Where(n => n.BeaconId == bi).Count() > 0)
{
BeaconUnit existing = bus.Single(b => b.BeaconId == bi);
if (bus.Where(u => u.UnitId == existing.UnitId).Count() == 1)
{
existing.BeaconId = null;
}
else
{
Database.Remove(existing);
}
}
Map map = Database.Maps.AsNoTracking().Single(m => m.MapId == mi);
if (bus.Where(b => b.UnitId == ui && b.BeaconId == null).Count() == 0)
{
BeaconUnit bu = new BeaconUnit();
bu.BeaconId = bi;
bu.UnitId = ui;
bu.MapId = mi;
bu.Level = map.Level;
Database.BeaconUnits.Add(bu);
}
else
{
BeaconUnit available = bus.Single(b => b.UnitId == ui && b.BeaconId == null);
available.BeaconId = bi;
}
Database.Beacons.Single(b => b.BeaconId == bi).LocationId = map.LocationId;
Database.SaveChanges();
return(new OkObjectResult(new { message = "Assigned beacon" }));
}
public IActionResult GetMultiple(int i)
{
try
{
List <object> facilityList = new List <object>();
List <string> imageList = new List <string>();
List <string> unitList = new List <string>();
Unit unit = null;
Facility facility = Database.Facilities.AsNoTracking().Single(b => b.FacilityId == i);
BeaconUnit bu = null;
int mainImage = 0, count = 0;
foreach (Image j in Database.Images.AsNoTracking().Where(b => b.FacilityId == facility.FacilityId))
{
imageList.Add(j.ImageURL);
if (j.Main == 1)
{
mainImage = count;
}
count++;
}
foreach (Unit u in Database.Units.AsNoTracking().Where(b => b.FacilityId == facility.FacilityId))
{
unitList.Add(u.UnitId.ToString());
unit = u;
}
bu = Database.BeaconUnits.AsNoTracking().Single(j => j.UnitId == unit.UnitId);
facilityList.Add(new
{
id = facility.FacilityId,
images = imageList,
name = facility.FacilityName,
description = facility.FacilityDescription,
loc = Database.Maps.AsNoTracking().Single(m => m.MapId == bu.MapId).LocationId,
map = bu.MapId,
block = unit.Block,
unit = unitList,
main = mainImage
});
return(new JsonResult(facilityList));
}
catch (Exception ex)
{
return(BadRequest(new { Message = "Unable to obtain facility information. " + ex.Message }));
}
}
public async Task <IActionResult> SaveUnitExcel()
{
List <string> errors = new List <string>();
try
{
IFormFileCollection files = Request.Form.Files;
if (files.Count != 0)
{
// Save file
IFormFile oneFile = files[0];
string contentType = oneFile.FileName.Substring(oneFile.FileName.Length - 4);
if (!contentType.Equals("xlsx"))
{
throw new Exception("The file has to be an excel spreadsheet (.xlsx)");
}
string path = Path.Combine(_hostingEnvironment.WebRootPath, "temp/", Guid.NewGuid().ToString() + "." + contentType);
using (FileStream fileStream = new FileStream(path, FileMode.Create))
await oneFile.CopyToAsync(fileStream);
List <string> nos = new List <string>();
StringBuilder error;
int id = Convert.ToInt32(Request.Form["locationId"]);
// Use file
using (ExcelPackage package = new ExcelPackage(new FileInfo(path)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
if (rowCount < 2)
{
throw new Exception("No rows found");
}
if (worksheet.Dimension.Columns != 4)
{
throw new Exception("Invalid number of columns");
}
int level = 0;
IQueryable <Map> maps = Database.Maps.AsNoTracking();
IQueryable <int> ints;
for (int row = 2; row <= rowCount; row++)
{
var block = worksheet.Cells[row, 1].Value;
var unitno = worksheet.Cells[row, 2].Value;
var unitname = worksheet.Cells[row, 3].Value;
error = new StringBuilder();
if (unitno == null)
{
error.Append("Row ").Append(row).Append(": unit no. required, ");
unitno = "";
}
else
{
error.Append(unitno).Append(": ");
ints = maps.Where(l => l.LocationId == id).Select(j => j.MapId);
foreach (Unit unit in Database.BeaconUnits.Where(x => ints.Contains((int)x.MapId)).Select(p => p.Unit))
{
if (unit.UnitNo == unitno.ToString())
{
error.Append("this unit no. is already being used in this location, ");
}
}
if (unitno.ToString().Length > 50)
{
error.Append("unit no. cannot contain more than 50 characters, ");
}
}
if (block == null)
{
error.Append("block required, ");
}
else
{
if (block.ToString().Length > 50)
{
error.Append("block cannot contain more than 50 characters, ");
}
}
if (unitname == null)
{
error.Append("unit name required, ");
}
else
{
if (unitname.ToString().Length > 100)
{
error.Append("name cannot contain more than 100 characters, ");
}
}
if (worksheet.Cells[row, 4].Value != null)
{
if (int.TryParse(worksheet.Cells[row, 4].Value.ToString(), out level))
{
ints = maps.Where(l => l.LocationId == id).Select(j => j.Level);
if (!ints.Contains(level))
{
error.Append("level does not exist in this location, ");
}
}
else
{
error.Append("level invalid, ");
}
}
else
{
error.Append("level required, ");
}
if (error.Length > ((string)unitno).Length + 4)
{
errors.Add(error.ToString());
}
else
{
nos.Add(unitno.ToString());
}
}
if (errors.Count == 0)
{
IEnumerable <IGrouping <string, string> > g = nos.GroupBy(i => i);
foreach (IGrouping <string, string> grp in g)
{
if (grp.Count() > 1)
{
errors.Add("The Unit No " + grp.Key + " is used more than once");
throw new Exception("");
}
}
Unit u;
BeaconUnit bu;
Map m;
IList <Unit> us = new List <Unit>();
IList <BeaconUnit> bus = new List <BeaconUnit>();
for (int row = 2; row <= rowCount; row++)
{
u = new Unit();
u.Block = worksheet.Cells[row, 1].Value.ToString().ToUpper();
u.UnitNo = worksheet.Cells[row, 2].Value.ToString();
u.UnitName = worksheet.Cells[row, 3].Value.ToString();
u.LocationId = id;
us.Add(u);
}
Database.Units.AddRange(us);
for (int row = 2; row <= rowCount; row++)
{
bu = new BeaconUnit();
m = maps.Single(x => x.LocationId == id && x.Level == Convert.ToInt32(worksheet.Cells[row, 4].Value));
bu.UnitId = us[row - 2].UnitId;
bu.MapId = m.MapId;
bu.Level = m.Level;
bus.Add(bu);
}
Database.BeaconUnits.AddRange(bus);
Database.SaveChanges();
}
else
{
throw new Exception("");
}
}
System.IO.File.Delete(path);
}
else
{
throw new Exception("File not found");
}
}
catch (Exception ex)
{
if (ex.Message == "")
{
return(BadRequest(new { message = errors }));
}
else
{
return(BadRequest(new { message = ex.Message }));
}
}
return(new OkObjectResult(new { message = "Successfully imported unit data" }));
}
public async Task <IActionResult> SaveBeaconExcel()
{
List <string> errors = new List <string>();
try
{
IFormFileCollection files = Request.Form.Files;
if (files.Count != 0)
{
// Save file
IFormFile oneFile = files[0];
string contentType = oneFile.FileName.Substring(oneFile.FileName.Length - 4);
if (!contentType.Equals("xlsx"))
{
throw new Exception("The file has to be an excel spreadsheet (.xlsx)");
}
string path = Path.Combine(_hostingEnvironment.WebRootPath, "temp/", Guid.NewGuid().ToString() + "." + contentType);
using (FileStream fileStream = new FileStream(path, FileMode.Create))
await oneFile.CopyToAsync(fileStream);
List <string> ids = new List <string>();
StringBuilder error;
Regex alphanumeric = new Regex(@"^[a-zA-Z0-9\-]+$");
Regex anl = new Regex(@"^[a-zA-Z0-9_\-@.,()]+$");
// Use file
using (ExcelPackage package = new ExcelPackage(new FileInfo(path)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
if (rowCount < 2)
{
throw new Exception("No rows found");
}
if (worksheet.Dimension.Columns != 8)
{
throw new Exception("Invalid number of columns");
}
int major = 0, minor = 0, uid = 0, lid = 0;
IQueryable <Beacon> beacons = Database.Beacons.AsNoTracking();
IQueryable <Location> locations = Database.Locations.AsNoTracking();
IQueryable <Unit> units = Database.Units.AsNoTracking();
for (int row = 2; row <= rowCount; row++)
{
var name = worksheet.Cells[row, 3].Value;
var uuid = worksheet.Cells[row, 4].Value;
var brand = worksheet.Cells[row, 2].Value;
var id = worksheet.Cells[row, 1].Value;
var location = worksheet.Cells[row, 7].Value;
var unit = worksheet.Cells[row, 8].Value;
error = new StringBuilder();
if (name == null)
{
error.Append("Row ").Append(row).Append(": name required, ");
name = "";
}
else
{
if (!anl.IsMatch(name.ToString()))
{
error.Append("Row ").Append(row).Append(": name contains invalid characters, ");
}
else
{
error.Append(name).Append(": ");
}
if (name.ToString().Length > 100)
{
error.Append("name cannot contain more than 100 characters, ");
}
}
if (uuid != null)
{
if (uuid.ToString().Length < 36)
{
error.Append("uuid too short, ");
}
else if (!alphanumeric.IsMatch(uuid.ToString()))
{
error.Append("uuid should only contain alphanumeric characters, ");
}
}
else
{
error.Append("uuid required, ");
}
if (brand == null)
{
error.Append("brand required, ");
}
else
{
if (!anl.IsMatch(brand.ToString()))
{
error.Append("brand contains invalid characters, ");
}
if (brand.ToString().Length > 50)
{
error.Append("brand cannot contain more than 50 characters, ");
}
}
if (id != null)
{
if (beacons.Where(i => i.Id == id.ToString()).Count() != 0)
{
error.Append("ID already exists, ");
}
else
{
if (!anl.IsMatch(id.ToString()))
{
error.Append("ID contains invalid characters, ");
}
if (id.ToString().Length > 50)
{
error.Append("ID cannot contain more than 50 characters, ");
}
}
}
if (worksheet.Cells[row, 5].Value != null)
{
if (int.TryParse(worksheet.Cells[row, 5].Value.ToString(), out major))
{
if (major < 1 && major > 65535)
{
error.Append("major out of range (1 - 65535), ");
}
}
else
{
error.Append("major invalid, ");
}
}
else
{
error.Append("major required, ");
}
if (worksheet.Cells[row, 6].Value != null)
{
if (int.TryParse(worksheet.Cells[row, 6].Value.ToString(), out minor))
{
if (minor < 1 && minor > 65535)
{
error.Append("minor out of range (1 - 65535), ");
}
}
else
{
error.Append("minor invalid, ");
}
}
else
{
error.Append("minor required, ");
}
if (location != null && unit != null)
{
if (anl.IsMatch(location.ToString()))
{
if (locations.Where(i => i.LocationName.Equals(location.ToString(), StringComparison.CurrentCultureIgnoreCase)).Count() == 0)
{
error.Append("location does not exist, ");
}
else
{
lid = locations.Single(i => i.LocationName.Equals(location.ToString(), StringComparison.CurrentCultureIgnoreCase)).LocationId;
}
}
else
{
error.Append("location contains invalid characters, ");
}
if (anl.IsMatch(unit.ToString()))
{
if (units.Where(i => i.UnitNo.Equals(unit.ToString(), StringComparison.CurrentCultureIgnoreCase)).Count() == 0)
{
error.Append("unit does not exist, ");
}
else
{
if (units.Single(i => i.UnitNo.Equals(unit.ToString(), StringComparison.CurrentCultureIgnoreCase)).LocationId != lid)
{
error.Append("unit does not exist in that location, ");
}
}
}
else
{
error.Append("unit contains invalid characters, ");
}
}
else if (location != null && unit == null)
{
error.Append("a unit is required if you want to associate a location, ");
}
else if (location == null && unit != null)
{
error.Append("a location is required if you want to associate a unit, ");
}
if (error.Length > ((string)name).Length + 4)
{
errors.Add(error.ToString());
}
else
if (id != null)
{
ids.Add(id.ToString());
}
}
if (errors.Count == 0)
{
if (ids.Count > 1)
{
IEnumerable <IGrouping <string, string> > g = ids.GroupBy(i => i);
foreach (IGrouping <string, string> grp in g)
{
if (grp.Count() > 1)
{
errors.Add("The ID " + grp.Key + " is used more than once");
throw new Exception("");
}
}
}
Beacon b;
BeaconUnit bu;
Map m;
IList <Beacon> bs = new List <Beacon>();
IList <BeaconUnit> bus = new List <BeaconUnit>();
IQueryable <BeaconUnit> buss = Database.BeaconUnits;
for (int row = 2; row <= rowCount; row++)
{
b = new Beacon();
b.Id = (worksheet.Cells[row, 1].Value != null) ? worksheet.Cells[row, 1].Value.ToString() : null;
b.Brand = worksheet.Cells[row, 2].Value.ToString();
b.BeaconName = worksheet.Cells[row, 3].Value.ToString();
b.UUID = worksheet.Cells[row, 4].Value.ToString();
b.Major = Convert.ToInt32(worksheet.Cells[row, 5].Value);
b.Minor = Convert.ToInt32(worksheet.Cells[row, 6].Value);
b.Distance = -999;
b.LocationId = (worksheet.Cells[row, 7].Value != null) ? locations.Single(l => l.LocationName.Equals(worksheet.Cells[row, 7].Value.ToString(), StringComparison.CurrentCultureIgnoreCase)).LocationId : (int?)null;;
bs.Add(b);
}
Database.Beacons.AddRange(bs);
for (int row = 2; row <= rowCount; row++)
{
if (worksheet.Cells[row, 8].Value != null)
{
uid = units.Single(l => l.UnitNo.Equals(worksheet.Cells[row, 8].Value.ToString(), StringComparison.CurrentCultureIgnoreCase)).UnitId;
if (buss.Where(n => n.UnitId == uid && n.BeaconId == null).Count() == 0)
{
bu = new BeaconUnit();
bu.BeaconId = bs[row - 2].BeaconId;
bu.UnitId = uid;
m = buss.Include(u => u.Map).FirstOrDefault(u => u.UnitId == bu.UnitId).Map;
bu.MapId = m.MapId;
bu.Level = m.Level;
bus.Add(bu);
}
else
{
buss.Single(n => n.UnitId == uid && n.BeaconId == null).BeaconId = bs[row - 2].BeaconId;
}
}
}
Database.BeaconUnits.AddRange(bus);
Database.SaveChanges();
}
else
{
throw new Exception("");
}
}
System.IO.File.Delete(path);
}
else
{
throw new Exception("File not found");
}
}
catch (Exception ex)
{
if (ex.Message == "")
{
return(BadRequest(new { message = errors }));
}
else
{
return(BadRequest(new { message = ex.Message }));
}
}
return(new OkObjectResult(new { message = "Successfully imported beacon data" }));
}
public async Task <IActionResult> SaveUnitDataAndPhoto(IList <IFormFile> fileInput, IFormCollection formData)
{
StringBuilder error = new StringBuilder();
string unitName = formData["unitName"];
string unitNo = formData["unitNo"];
string block = formData["block"];
string map = formData["mapId"];
try
{
if (unitName == "")
{
error.Append("name required, ");
}
else
{
if (unitName.Length > 100)
{
error.Append("name cannot contain more than 100 characters, ");
}
}
if (unitNo == "")
{
error.Append("unit no. required, ");
}
else
{
List <int> ids = Database.Maps.AsNoTracking().Where(l => l.LocationId == int.Parse(formData["locationId"])).Select(l => l.MapId).ToList();
foreach (BeaconUnit z in Database.BeaconUnits.AsNoTracking().Include(y => y.Unit).Where(x => ids.Contains((int)x.MapId)))
{
if (z.Unit.UnitNo == unitNo)
{
error.Append("unit no already exists in that location, ");
throw new Exception();
}
}
if (unitNo.Length > 50)
{
error.Append("unit no cannot contain more than 50 characters, ");
}
}
if (block == "")
{
error.Append("block required, ");
}
else
{
if (block.Length > 50)
{
error.Append("block cannot contain more than 50 characters, ");
}
}
if (map == "null" || map == "0")
{
error.Append("level required");
}
if (error.Length == 0)
{
Unit newUnit = new Unit();
BeaconUnit bu = new BeaconUnit();
int mid = int.Parse(map);
newUnit.UnitName = unitName;
newUnit.UnitNo = unitNo.ToUpper();
newUnit.Block = block.ToUpper();
newUnit.LocationId = int.Parse(formData["locationId"]);
Database.Units.Add(newUnit);
bu.UnitId = newUnit.UnitId;
bu.MapId = mid;
bu.Level = Database.Maps.AsNoTracking().Single(m => m.MapId == mid).Level;
Database.BeaconUnits.Add(bu);
if (fileInput.Count != 0)
{
IFormFile oneFile = fileInput[0];
Image unitPhoto = new Image();
System.Drawing.Image img;
string contentType = oneFile.ContentType.Substring(6);
string fileId = Guid.NewGuid().ToString().Replace("-", "");
string path = Path.Combine(_hostingEnvironment.WebRootPath, "images/uploads/", fileId + "." + contentType);
using (FileStream fileStream = new FileStream(path, FileMode.Create))
await oneFile.CopyToAsync(fileStream);
using (FileStream fileStream = new FileStream(path, FileMode.Open))
img = System.Drawing.Image.FromStream(fileStream);
if ((img.Width / 4) * 3 != img.Height)
{
System.IO.File.Delete(path);
error.Append("Image ").Append(oneFile.FileName).Append(" has invalid dimensions (4:3 aspect ratio needed) ");
throw new Exception();
}
unitPhoto.ImageURL = "/images/uploads/" + fileId + "." + contentType;
Database.Images.Add(unitPhoto);
newUnit.ImageId = unitPhoto.ImageId;
}
Database.SaveChanges();
}
else
{
throw new Exception();
}
}
catch (Exception ex)
{
return(BadRequest(new { error = error.ToString() }));
}
return(new OkObjectResult(new { message = "Saved unit photo and data" }));
}