public ActionResult Grid_Create([DataSourceRequest] DataSourceRequest request, ExplosionView eV)
{
bool errorFlag = false;
if (ModelState.IsValid)
{
try
{
using (AdmShipDataContext dc = new AdmShipDataContext())
{
if (eV == null)
{
ModelState.AddModelError("EmptyField", "Не введены данные для сохранения!");
errorFlag = true;
return(GetUpDT(request, errorFlag, eV));
}
var tmp = dc.FireExplosions.Where(w => w.OID == eV.Id).ToList();
if (tmp.Count > 0)
{
ModelState.AddModelError("AlreadyExist", "Такая связь уже существует!");
errorFlag = true;
return(GetUpDT(request, errorFlag, eV));
}
eV.Id = dc.ExplosionInsertUpdateDelete(eV.Id, eV.Fss, eV.Fz, eV.Pue, eV.Name, "Insert");
}
}
catch (Exception ex) {
errorFlag = true;
ModelState.AddModelError("SqlError", "Невозможно добавить такую запись. Возможные причины: \n 1. Такая запись уже существует в базе данных.\n 2. Предложенная запись не соответствует требованиям базы данных.");
return(GetUpDT(request, errorFlag, eV));
}
}
return(GetUpDT(request, errorFlag, eV));
}
public JsonResult ExcelExport(string[] data, int subdiv = 0)
{
//var numbers = data.Split(',');
Dictionary <string, List <GetRoomsForExcelFullResult> > dict = new Dictionary <string, List <GetRoomsForExcelFullResult> >();
List <GetRoomsForExcelFullResult> roomsToExport = new List <GetRoomsForExcelFullResult>();
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var rooms = db3.GetRoomsForExcelFull().ToList();//db3.GetTable<Room>().Where(r => data.Contains(r.uniqueRoomNumber).ToList();
if (data != null)
{
if (data.Length != 0)
{
if (subdiv != 0)
{
roomsToExport = rooms.Where(w => data.Any(a => w.uniqueRoomNumber == a) && w.DepartmentId == subdiv).ToList();
}
else
{
roomsToExport = rooms.Where(w => data.Any(a => w.uniqueRoomNumber == a)).ToList();
}
}
}
}
dict.Add("list", roomsToExport);
return(Json(dict));
}
public ActionResult Grid_Update([DataSourceRequest] DataSourceRequest request, ExplosionView eV)
{
bool errorFlag = false;
if (ModelState.IsValid)
{
using (AdmShipDataContext dc = new AdmShipDataContext())
{
if (eV == null)
{
ModelState.AddModelError("EmptyField", "Не введены данные для сохранения");
errorFlag = true;
return(GetUpDT(request, errorFlag, eV));
}
var tmp = dc.FireExplosions.Where(w => w.Name_Room == eV.Name).ToList();
if (tmp.Count > 0)
{
ModelState.AddModelError("AlreadyExist", "Такая связь уже существует!");
errorFlag = true;
return(GetUpDT(request, errorFlag, eV));
}
dc.ExplosionInsertUpdateDelete(eV.Id, eV.Fss, eV.Fz, eV.Pue, eV.Name, "Update");
}
}
return(GetUpDT(request, errorFlag, eV));
}
public JsonResult GetSingleRoom2D(string fileName)//ПЕРЕПИСАТЬ!!!!
{
Room2 room = new Room2();
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var sRoom = db3.GetSingleRoom2D(fileName);
foreach (var r in sRoom)
{
room.floor = r.RoomFloor;
room.roomNumber = r.roomNumber;
room.planRoomNumber = r.roomPlanNumber;
room.roomFunction = r.roomFunction;
room.roomSquare = (decimal)r.roomSquare;
room.totalLiveSpace = (decimal)r.totalLivingSpace;
room.specialFunction = (decimal)r.roomSpecFunction;
room.ancillarySquare = (decimal)r.ancillarySquare;
room.roomHeight = r.roomHeight;
room.balconySquare = (decimal)r.balconySquare;
room.roomFilename = r.roomFilename3ds;
room.roomId = r.roomId;
}
}
return(Json(room));
}
public JsonResult GetRoomEmployees(int roomId, int subdiv)
{
using (AdmShipDataContext db = new AdmShipDataContext())
{
var rooms = db.GetRoomsForEdit(roomId, subdiv).ToList();
return(Json(rooms));
}
}
public JsonResult GetBuildings3D()
{
using (AdmShipDataContext db = new AdmShipDataContext())
{
var buildings = db.GetBuildings3d().ToList();
return(Json(buildings));
}
}
public JsonResult GetSubdivisions2D()
{
using (AdmShipDataContext db = new AdmShipDataContext())
{
var subdivisionsU = db.GetSubdivisions2D().ToList();
return(Json(subdivisionsU));
}
}
public JsonResult ChiefChanged(int id)
{
dynamic room;
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
room = db3.GetBuildsByChief(id).ToList();
}
return(Json(room));
}
public JsonResult EmployeeChanged(int empId)
{
dynamic room;
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
room = db3.GetBuildsByEmpl(empId).ToList();
}
return(Json(room));
}
public JsonResult GetRoomEmployees(int roomId, int subdiv)
{
JsonResult jsonResult = new JsonResult();
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var rooms = db3.GetRoomsForEdit(roomId, subdiv).ToList();
jsonResult = Json(rooms);
}
return(jsonResult);
}
public JsonResult IsEmplInEditor(int id)
{
JsonResult jsonResult = new JsonResult();
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var emps = db3.IsEmplEdit(id).ToList();
jsonResult = Json(emps);
}
return(jsonResult);
}
public JsonResult UpdateGridNew(string statementType, int roomId, int id, int subdivId, int empId = 0, string empText = "", string empPhone = "", int chiefId = 0, string chiefFio = "")
{
JsonResult result = null;
char[] trimChars = new char[] { '_', '-' };
string phone = empPhone.TrimEnd(trimChars);
using (AdmShipDataContext db3 = new AdmShipDataContext()) {
db3.MasterInsertUpdateDelete(id, roomId, subdivId, empId, empText, chiefId, chiefFio, phone, statementType);
}
result = GetRoomEmployees(roomId, subdivId);
return(result);
}
public ActionResult Grid_Destroy([DataSourceRequest] DataSourceRequest request, ExplosionView eV)
{
bool errorFlag = false;
if (ModelState.IsValid)
{
using (AdmShipDataContext dc = new AdmShipDataContext())
{
dc.ExplosionInsertUpdateDelete(eV.Id, eV.Fss, eV.Fz, eV.Pue, eV.Name, "Delete");
}
}
return(GetUpDT(request, errorFlag, eV));
}
public ActionResult Grid_Read([DataSourceRequest] DataSourceRequest request)
{
using (AdmShipDataContext dc = new AdmShipDataContext())
{
var res = dc.GetIdForExplosion()
.Select(s => new ExplosionView()
{
Id = s.RoomId, Fss = s.CategoryFSS, Fz = s.CategoryFZ, Pue = s.CategoryPUE, RoomName = s.RoomName, BuildInvNumber = s.BuildInvNumber
})
.OrderBy(o => o.RoomName)
.ToDataSourceResult(request);
return(Json(res));
}
}
public JsonResult GetRooms2D()
{
using (AdmShipDataContext db = new AdmShipDataContext())
{
var rooms = db.GetRooms2D().OrderBy(o => o.roomId).ToList();
JsonSerializerSettings settings = new JsonSerializerSettings();
settings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
JsonResult jsonResult = Json(rooms);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
}
public ActionResult Grid_Read([DataSourceRequest] DataSourceRequest request)
{
using (AdmShipDataContext dc = new AdmShipDataContext())
{
var result = dc.GetBuildSubdivConnection().Select(r => new SubBuildEditModel()
{
buildId = r.id,
buildNumber = r.buildNumber,
buildName = r.buildName,
subdivisionName = r.subdivisionName,
subdivId = r.subdivId
}).ToList();
return(Json(result.OrderBy(o => o.nameOfBuild).ThenBy(t => t.nameOfSubdiv).ToDataSourceResult(request)));
}
}
public JsonResult CheckSubdiv(int subdiv)
{
ObjectToReturn objToReturn = new ObjectToReturn();
using (AdmShipDataContext dc = new AdmShipDataContext()) {
var check = dc.IsAnyConnection().ToList();
if (check.Any(x => x.subdivId == subdiv))
{
objToReturn.flag = true;
}
else
{
objToReturn.flag = false;
}
}
return(Json(objToReturn, JsonRequestBehavior.AllowGet));
}
public JsonResult SubdivChanged(int id)
{
string json = "";
JsonResult jsonResult = new JsonResult();
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var builds = db3.GetBuildings3d().Where(s => s.subdivId == id).ToList();
if (builds != null)
{
json = JsonConvert.SerializeObject(builds, Formatting.Indented);
}
jsonResult = Json(json);
jsonResult.MaxJsonLength = int.MaxValue;
}
return(jsonResult);
}
public ActionResult Grid_Destroy([DataSourceRequest] DataSourceRequest request, SubBuildEditModel build)
{
bool errorFlag = false;
DbTransaction dbt = null;
dynamic result = null;
if (ModelState.IsValid)
{
try
{
int subdivId = 0;
int tmpS = 0;
if (Int32.TryParse(build.nameOfSubdiv, out tmpS))
{
subdivId = Int32.Parse(build.nameOfSubdiv);
}
string bnumber = "";
foreach (var b in builds)
{
if (b.buildName == build.nameOfBuild)
{
bnumber = b.InvNumber;
}
}
using (AdmShipDataContext dc = new AdmShipDataContext())
{
if (dc.Connection.State == System.Data.ConnectionState.Closed)
{
dc.Connection.Open();
}
dbt = dc.Connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
dc.Transaction = dbt;
dc.SubdivBuildEdit(build.buildId, subdivId, build.nameOfBuild, bnumber, "Delete");
dbt.Commit();
}
}
catch (Exception ex)
{
errorFlag = true;
ModelState.AddModelError("Del", "Невозможно удалить запись!");
}
}
return(GetUpDT(request, errorFlag, build));
}
// GET: Explosion
public ActionResult Index()
{
List <string> fss = new List <string>();
List <string> fz = new List <string>();
List <string> pue = new List <string>();
List <RoomExpl> rooms = new List <RoomExpl>();
using (AdmShipDataContext dc = new AdmShipDataContext())
{
var tFss = dc.GetFssCategories().ToList();
foreach (var t in tFss)
{
fss.Add(t.categoryFSS);
}
var tFz = dc.GetFzCategories().ToList();
foreach (var t in tFz)
{
fz.Add(t.categoryFZ);
}
var tPue = dc.GetPueCategories().ToList();
foreach (var t in tPue)
{
pue.Add(t.categoryPUE);
}
string query = @"select distinct r3.Name, r3.Назначение_отдельных_помещений+' ('+ r3.этаж+' этаж, пом.'+cast(cast(r3.номер_комнаты_по_плану as int) as nvarchar)+')' RoomName,
r3.Инвентарный_номер_здания BuildInvNumber from cyclone.dbo.ROOM_3D r3";
var dbRooms = dc.ExecuteQuery <GetIdForExplosionResult>(query);
foreach (var d in dbRooms)
{
rooms.Add(new RoomExpl()
{
valField = d.Name, textField = d.BuildInvNumber + " (" + d.RoomName + ")"
});
}
}
ViewData["fss"] = fss; //fssList;
ViewData["fz"] = fz; //fzList;
ViewData["pue"] = pue; //pueList;
ViewData["rooms"] = rooms;
return(View());
}
private JsonResult GetUpDT(DataSourceRequest request, bool errorFlag, SubBuildEditModel build)
{
using (AdmShipDataContext dc = new AdmShipDataContext())
{
if (build != null)
{
var ls = dc.GetBuildSubDiv(build.InvNumber, build.SubDiv);
if (ls.Any())
{
var b = ls.First();
build.buildId = b.id;
build.buildNumber = b.buildNumber;
build.buildName = b.buildName;
build.subdivisionName = b.subdivisionName;
build.subdivId = b.subdivId;
}
}
return(Json(new[] { build }.ToDataSourceResult(request, ModelState)));
}
}
private JsonResult GetUpDT(DataSourceRequest request, bool errorFlag, ExplosionView eV)
{
using (AdmShipDataContext dc = new AdmShipDataContext())
{
if (eV != null)
{
var ls = dc.GetExplosionById(eV.Id).ToList();
if (ls.Any())
{
var b = ls.First();
eV.Id = b.RoomId;
eV.Fss = b.CategoryFSS;
eV.Fz = b.CategoryFZ;
eV.Pue = b.CategoryPUE;
eV.BuildInvNumber = b.BuildInvNumber;
eV.RoomName = b.RoomName;
}
}
return(Json(new[] { eV }.ToDataSourceResult(request, ModelState)));
}
}
private ActionResult UpdateCreate(DataSourceRequest request, SubBuildEditModel build, string type)
{
bool errorFlag = false;
if (ModelState.IsValid)
{
using (AdmShipDataContext dc = new AdmShipDataContext())
{
if (build == null)
{
ModelState.AddModelError("EmptyField", "Не заполнено одно или несколько из обязательных полей!");
errorFlag = true;
return(GetUpDT(request, errorFlag, build));
}
if (String.IsNullOrEmpty(build?.InvNumber))
{
ModelState.AddModelError("EmptyBuild", "Не выбрано здание!");
errorFlag = true;
return(GetUpDT(request, errorFlag, build));
}
if (build.SubDiv == 0)
{
ModelState.AddModelError("EmptySubdiv", "Не выбрано подразделение!");
errorFlag = true;
return(GetUpDT(request, errorFlag, build));
}
if (!subdivs.Any(r => r.Id == build.SubDiv))
{
errorFlag = true;
ModelState.AddModelError("NotExistSubdiv", "Такого подразделения не существует!");
return(GetUpDT(request, errorFlag, build));
}
if (!builds.Any(r => r.InvNumber == build.InvNumber))
{
errorFlag = true;
ModelState.AddModelError("NotExistBuild", "Такого здания не существует!");
return(GetUpDT(request, errorFlag, build));
}
var ls = dc.GetBuildSubDiv(build.InvNumber, build.SubDiv);
if (ls.Any())
{
errorFlag = true;
ModelState.AddModelError("Exists", "Такая связь существует!");
return(GetUpDT(request, errorFlag, build));
}
var b = builds.First(r => r.InvNumber == build.InvNumber);
try
{
dc.SubdivBuildEdit(build.buildId, build.SubDiv, b.Name, b.InvNumber, type);
}
catch (Exception e)
{
errorFlag = true;
ModelState.AddModelError("Error", e.Message);
}
}
}
return(GetUpDT(request, errorFlag, build));
}
public JsonResult Index(string id, int?subdiv = null, int?chief = null, int?roomId = null, string data = "", int?emplId = null)
{
//interfereDataBases();
TableToFront2 table = new TableToFront2();
List <TableToFront2> lst = new List <TableToFront2>();
List <TableToFront2> lstChief = new List <TableToFront2>();
//List<ObjectToReturn2D> dict = new List<ObjectToReturn2D>();
List <IsAnyConnectionResult> check = new List <IsAnyConnectionResult>();
using (AdmShipDataContext dc = new AdmShipDataContext()) {
check = dc.IsAnyConnection().ToList();
}
ObjectToReturn objToReturn = new ObjectToReturn();
if (check.Any(x => x.buildNumber == id))
{
List <GetRoomByAllFields2DResult> rooms = new List <GetRoomByAllFields2DResult>();
if (data != "")
{
List <FromFilter> parsedData = (List <FromFilter>)JsonConvert.DeserializeObject(data, typeof(List <FromFilter>));
string declare = "declare @buildNum nvarchar(6) = '" + id + "';";
if (subdiv == null)
{
declare += "declare @subDivId int;";
}
else
{
declare += "declare @subDivId int = " + subdiv + ";";
}
if (roomId == null)
{
declare += "declare @roomId int;";
}
else
{
declare += "declare @roomId int= " + roomId + ";";
}
if (chief == null)
{
declare += "declare @chiefId int;";
}
else
{
declare += "declare @chiefId int= " + chief + ";";
}
if (emplId == null)
{
declare += "declare @empId int;";
}
else
{
declare += "declare @empId int= " + emplId + ";";
}
string action = @"SELECT distinct r2.invNumber, r2.buildName, r7.SubdivisionName, r7.DepartmentId, r5.roomFloor, r1.roomId, r1.roomFilename3ds, r1.uniqueRoomNumber, r8.OBJECTID,
[dbo].GetChiefFio(r1.roomId, @subDivId) chiefFio, [dbo].GetEmpFio(r1.roomId, @subDivId) empFio from [dbo].new_Buildings r2
left join [dbo].[new_SubdivBuild] r9 on (r9.buildNumber = r2.invNumber)
left join [dbo].new_Subdivisions r7 on(r9.subdivId = r7.DepartmentId)
join [dbo].new_Rooms r1 on(r2.buildId = r1.buildId)
left join new_EmployeesInRoom r3 on (r3.roomId = r1.roomId)
left join [dbo].new_RoomDetails r5 on (r1.roomId = r5.roomId)
left join cyclone.dbo.ROOM_2D r8 on (r1.roomFilename3ds = r8.Name)
where (@buildNum is null or r2.invNumber = @buildNum) and
(@subDivId is null or r7.DepartmentId = @subDivId)
and (@roomId is null or r1.roomId = @roomId)
and (@chiefId is null or r3.chiefId = @chiefId) and (@empId is null or r3.empId = @empId) and ";
string oper = "";
string action2 = declare + action;
//var predicate = PredicateBuilder.True<Room>();
//predicate = predicate.And(r => r.buildInvNumber.Contains(id) && r.subdivisionId == subdiv);//&& r.idChief == chief
string action3 = CreateQuery(action2, parsedData);
string query = SubstringQuery(action3);
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var rooms_pars = db3.ExecuteQuery <GetRoomByAllFields2DResult>(query).OrderBy(o => o.roomId).ToList();//db3.GetTable<Room>().FromSql($"SELECT * FROM dbo.rooms({action})").ToList();
rooms = rooms_pars;
}
}
else
{
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
if (id != "")
{
rooms = db3.GetRoomByAllFields2D(id, subdiv, roomId, chief, emplId).OrderBy(o => o.roomId).ToList();//GetTable<Room>().Where(r => r.subdivisionId == subdiv).OrderBy(o => o.uniqueRoomNumber).ToList();&& r.idChief == chief - добавим позже
}
}
}
foreach (var room in rooms)
{
TableToFront2 tbl2 = new TableToFront2();
tbl2.OBJECTID = room.OBJECTID;
tbl2.roomFloor = room.roomFloor;
tbl2.roomNumber = room.uniqueRoomNumber;
tbl2.roomId = (int)room.roomId;
if (room.empFio != null)
{
tbl2.employeeFio = room.empFio;//.Split(',');
}
else
{
tbl2.employeeFio = /*new string[] {*/ string.Empty;
}
if (room.chiefFio != null)
{
tbl2.chiefFio = room.chiefFio;
}
else
{
tbl2.chiefFio = string.Empty;
}
tbl2.subdivisionId = room.DepartmentId;
tbl2.subdivision = room.subdivisionName;
tbl2.depId = room.DepartmentId;
tbl2.buildInvNumber = room.invNumber;
tbl2.buildName = room.buildName;
tbl2.filename3ds = room.roomFilename3ds;
lst.Add(tbl2);
}
foreach (var t in lst)
{
if (t.subdivisionId == null)
{
t.isSubdivNull = 0;
}
else
{
t.isSubdivNull = 1;
}
if (t.OBJECTID == null)
{
t.isVisible = 0;
}
else
{
t.isVisible = 1;
}
}
objToReturn.flag = true;
objToReturn.list = lst;
}
else
{
List <GetRoomByAllFields_oldResult> rooms = new List <GetRoomByAllFields_oldResult>();
if (data != "")
{
List <FromFilter> parsedData = (List <FromFilter>)JsonConvert.DeserializeObject(data, typeof(List <FromFilter>));
string declare = "declare @buildNum nvarchar(6) = '" + id + "';";
if (roomId == null)
{
declare += "declare @roomId int;";
}
else
{
declare += "declare @roomId int= " + roomId + ";";
}
string action = @"select distinct r1.roomId, r1.roomFilename3ds, r1.uniqueRoomNumber, r2.invNumber, r2.buildName, r5.roomFloor, r8.OBJECTID, r7.SubdivisionName, r7.DepartmentId, [dbo].GetChiefFio(r1.roomId, r7.DepartmentId) chiefFio, [dbo].GetEmpFio(r1.roomId, r7.DepartmentId) empFio
from [dbo].new_Rooms r1
join [dbo].new_Buildings r2 on(r2.buildId = r1.buildId)
left join new_EmployeesInRoom r3 on (r3.roomId = r1.roomId)
left join [dbo].new_RoomDetails r5 on (r1.roomId = r5.roomId)
left join cyclone.dbo.ROOM_2D r8 on (r1.roomFilename3ds = r8.Name)
left join [dbo].[new_SubdivBuild] r9 on (r2.invNumber = r9.buildNumber)
left join new_Subdivisions r7 on(r9.subdivId = r7.DepartmentId)
where (@buildNum is null or r2.invNumber = @buildNum) and (@roomId is null or r1.roomId=@roomId) and " ;
string oper = "";
string action2 = declare + action;
//var predicate = PredicateBuilder.True<Room>();
//predicate = predicate.And(r => r.buildInvNumber.Contains(id) && r.subdivisionId == subdiv);//&& r.idChief == chief
string action3 = CreateQuery(action2, parsedData);
string query = SubstringQuery(action3);
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var rooms_pars = db3.ExecuteQuery <GetRoomByAllFields_oldResult>(query).OrderBy(o => o.roomId).ToList();//db3.GetTable<Room>().FromSql($"SELECT * FROM dbo.rooms({action})").ToList();
rooms = rooms_pars;
}
}
else
{
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
rooms = db3.GetRoomByAllFields_old(id, roomId).ToList();
}
}
foreach (var room in rooms)
{
TableToFront2 tbl2 = new TableToFront2();
tbl2.OBJECTID = room.OBJECTID;
tbl2.roomFloor = room.roomFloor;
tbl2.roomNumber = room.uniqueRoomNumber;
tbl2.roomId = (int)room.roomId;
if (room.empFio != null)
{
tbl2.employeeFio = room.empFio;//.Split(',');
}
else
{
tbl2.employeeFio = /*new string[] {*/ string.Empty;
}
if (room.chiefFio != null)
{
tbl2.chiefFio = room.chiefFio;
}
else
{
tbl2.chiefFio = string.Empty;
}
tbl2.subdivisionId = room.DepartmentId;
tbl2.subdivision = room.SubdivisionName;
tbl2.depId = room.DepartmentId;
tbl2.buildInvNumber = room.invNumber;
tbl2.buildName = room.buildName;
tbl2.filename3ds = room.roomFilename3ds;
lst.Add(tbl2);
}
foreach (var t in lst)
{
if (t.subdivisionId == null)
{
t.isSubdivNull = 0;
}
else
{
t.isSubdivNull = 1;
}
if (t.OBJECTID == null)
{
t.isVisible = 0;
}
else
{
t.isVisible = 1;
}
}
objToReturn.flag = false;
objToReturn.list = lst;
//dict.Add(objToReturn);
}
return(Json(objToReturn));
}
public JsonResult GetFilteringCombobox(string value)//переписать
{
string json = "";
switch (value)
{
case "buildName":
Dictionary <string, List <FilterCombobox> > dict = new Dictionary <string, List <FilterCombobox> >();
List <FilterCombobox> lst = new List <FilterCombobox>();
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var result = db3.GetBuildsForFilter().ToList();
foreach (var b in result)
{
FilterCombobox fcb = new FilterCombobox()
{
value = b.buildName,
text = b.buildName
};
lst.Add(fcb);
}
dict.Add("list", lst.OrderBy(o => o.value).ToList());
}
json = JsonConvert.SerializeObject(dict, Formatting.Indented);
break;
case "roomFunction":
Dictionary <string, List <FilterCombobox> > dict2 = new Dictionary <string, List <FilterCombobox> >();
List <FilterCombobox> lst2 = new List <FilterCombobox>();
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var result2 = db3.GetRoomFuncsForFilter().ToList();
foreach (var r in result2)
{
FilterCombobox fcb = new FilterCombobox()
{
value = r.roomFunction,
text = r.roomFunction
};
lst2.Add(fcb);
}
}
dict2.Add("list", lst2.OrderBy(o => o.value).ToList());
json = JsonConvert.SerializeObject(dict2, Formatting.Indented);
break;
case "floor":
Dictionary <string, List <FilterCombobox> > dict3 = new Dictionary <string, List <FilterCombobox> >();
List <FilterCombobox> lst3 = new List <FilterCombobox>();
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var result3 = db3.GetFloorsForFilter().ToList();
foreach (var r in result3)
{
FilterCombobox fcb = new FilterCombobox()
{
value = r.roomFloor,
text = r.roomFloor
};
lst3.Add(fcb);
}
}
dict3.Add("list", lst3.OrderBy(o => o.value).ToList());
json = JsonConvert.SerializeObject(dict3, Formatting.Indented);
break;
}
return(Json(json));
}
public JsonResult Index3D(int subdiv, string id = "", int?chief = null, int?roomId = null, string data = "", int?emplId = null)
{
//interfereDataBases();
List <GetRoomByAllFieldsResult> rooms;
if (data != "")
{
List <FromFilter> parsedData = (List <FromFilter>)JsonConvert.DeserializeObject(data, typeof(List <FromFilter>));
string declare = "declare @subDivId int = " + subdiv + ";";
if (id == "")
{
declare += "declare @buildNum nvarchar(6);";
}
else
{
declare += "declare @buildNum nvarchar(6) = '" + id + "';";
}
if (roomId == null)
{
declare += "declare @roomId int;";
}
else
{
declare += "declare @roomId int= " + roomId + ";";
}
if (chief == null)
{
declare += "declare @chiefId int;";
}
else
{
declare += "declare @chiefId int= " + chief + ";";
}
if (emplId == null)
{
declare += "declare @empId int;";
}
else
{
declare += "declare @empId int= " + emplId + ";";
}
string action = @"SELECT distinct r7.SubdivisionName, r7.DepartmentId, r2.invNumber, r2.buildName, r5.roomFloor, r1.roomId, r1.roomFilename3ds, r1.uniqueRoomNumber, r8.OBJECTID,
[dbo].GetChiefFio(r1.roomId, @subDivId) chiefFio, [dbo].GetEmpFio(r1.roomId, @subDivId) empFio from [dbo].new_Subdivisions r7
left join [dbo].[new_SubdivBuild] r9 on (r7.DepartmentId = r9.subdivId)
left join [dbo].new_Buildings r2 on(r9.buildNumber = r2.invNumber)
join [dbo].new_Rooms r1 on(r2.buildId = r1.buildId)
left join new_EmployeesInRoom r3 on (r3.roomId = r1.roomId)
left join [dbo].new_RoomDetails r5 on (r1.roomId = r5.roomId)
left join cyclone.dbo.ROOM_3D r8 on (r1.roomFilename3ds = r8.Name)
where (@subDivId is null or r7.DepartmentId = @subDivId)" +
" and (@buildNum is null or r2.invNumber = @buildNum)" +
" and (@roomId is null or r1.roomId = @roomId)" +
" and (@chiefId is null or r3.chiefId = @chiefId) and (@empId is null or r3.empId = @empId) and ";
string oper = "";
string action2 = declare + action;
//var predicate = PredicateBuilder.True<Room>();
//predicate = predicate.And(r => r.buildInvNumber.Contains(id) && r.subdivisionId == subdiv);//&& r.idChief == chief
for (int i = 0, j = 1; i < parsedData.Count; i++, j++)
{
var item = parsedData[i];
FromFilter item2 = new FromFilter();
if (j <= parsedData.Count - 1)
{
item2 = parsedData[j];
}
switch (item.parametr)
{
case "floor":
switch (item.type)
{
case "contains":
action2 += "r5.roomFloor like '%" + item.value + "%'";
break;
case "eq":
action2 += "r5.roomFloor = '" + item.value + "'";
break;
case "gt":
action2 += "r5.roomFloor > '" + item.value + "'";
break;
case "lt":
action2 += "r5.roomFloor < '" + item.value + "'";
break;
}
break;
case "buildName":
switch (item.type)
{
case "contains":
action2 += "r2.buildName like '%" + item.value + "%'";
break;
case "eq":
action2 += "r2.buildName = '" + item.value + "'";
break;
//case "gt":
// predicate = predicate.And(r => r.floor > Convert.ToDecimal(item.value));
// break;
//case "lt":
// predicate = predicate.And(r => r.floor < Convert.ToDecimal(item.value));
// break;
}
break;
case "roomFunction":
switch (item.type)
{
case "contains":
action2 += "r5.roomFunction like '%" + item.value + "%'";
break;
case "eq":
action2 += "r5.roomFunction = '" + item.value + "'";
break;
//case "gt":
// predicate = predicate.And(r => r.floor > Convert.ToDecimal(item.value));
// break;
//case "lt":
// predicate = predicate.And(r => r.floor < Convert.ToDecimal(item.value));
// break;
}
break;
case "roomSquare":
decimal value = Convert.ToDecimal(item.value, CultureInfo.InvariantCulture);
switch (item.type)
{
//case "contains":
// action2 += "room_square like '%" + item.value + "%' and ";
// break;
case "eq":
action2 += "r5.roomSquare = " + item.value;
break;
case "gt":
action2 += "r5.roomSquare > " + item.value;
break;
case "lt":
action2 += "r5.roomSquare < " + item.value;
break;
}
break;
case "totalLiveSpace":
decimal value2 = Convert.ToDecimal(item.value, CultureInfo.InvariantCulture);
switch (item.type)
{
//case "contains":
// action2 += "total_living_space like '%" + item.value + "%' and ";
// break;
case "eq":
action2 += "r5.totalLivingSpace = " + item.value;
break;
case "gt":
action2 += "r5.totalLivingSpace > " + item.value;
break;
case "lt":
action2 += "r5.totalLivingSpace < " + item.value;
break;
}
break;
case "specialFunction":
decimal value3 = Convert.ToDecimal(item.value, CultureInfo.InvariantCulture);
switch (item.type)
{
//case "contains":
// predicate = predicate.And(r => r.floor.Contains(item.value));
// break;
case "eq":
action2 += "r5.roomSpecFunction = " + item.value;
break;
case "gt":
action2 += "r5.roomSpecFunction > " + item.value;
break;
case "lt":
action2 += "r5.roomSpecFunction < " + item.value;
break;
}
break;
case "ancillarySquare":
decimal value4 = Convert.ToDecimal(item.value, CultureInfo.InvariantCulture);
switch (item.type)
{
//case "contains":
// predicate = predicate.And(r => r.floor.Contains(item.value));
// break;
case "eq":
action2 += "r5.ancillarySquare = " + item.value;
break;
case "gt":
action2 += "r5.ancillarySquare > " + item.value;
break;
case "lt":
action2 += "r5.ancillarySquare < " + item.value;
break;
}
break;
case "roomHeight":
double value5 = Convert.ToDouble(item.value, CultureInfo.InvariantCulture);
switch (item.type)
{
//case "contains":
// predicate = predicate.And(r => r.floor.Contains(item.value));
// break;
case "eq":
action2 += "r5.roomHeight = '" + item.value.ToString() + "'";
break;
case "gt":
action2 += "r5.roomHeight > '" + item.value.ToString() + "'";
break;
case "lt":
action2 += "r5.roomHeight < '" + item.value.ToString() + "'";
break;
}
break;
case "balconySquare":
decimal value6 = Convert.ToDecimal(item.value, CultureInfo.InvariantCulture);
switch (item.type)
{
//case "contains":
// predicate = predicate.And(r => r.floor.Contains(item.value));
// break;
case "eq":
action2 += "r5.balconySquare = '" + item.value + "'";
break;
case "gt":
action2 += "r5.balconySquare > '" + item.value + "'";
break;
case "lt":
action2 += "r5.balconySquare < '" + item.value + "'";
break;
}
break;
}
if (i != parsedData.Count - 1)
{
if (item.parametr.Equals(item2.parametr))
{
oper = "or";
}
else
{
oper = "and";
}
action2 += " " + oper + " ";
}
}
string query = SubstringQuery(action2);
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
var rooms_pars = db3.ExecuteQuery <GetRoomByAllFieldsResult>(query).OrderBy(o => o.roomId).ToList();//db3.GetTable<Room>().FromSql($"SELECT * FROM dbo.rooms({action})").ToList();
rooms = rooms_pars;
}
}
else
{
using (AdmShipDataContext db3 = new AdmShipDataContext())
{
if (id != "")
{
rooms = db3.GetRoomByAllFields(subdiv, id, roomId, chief, emplId).OrderBy(o => o.roomId).ToList();//GetTable<Room>().Where(r => r.subdivisionId == subdiv).OrderBy(o => o.uniqueRoomNumber).ToList();&& r.idChief == chief - добавим позже
}
else
{
rooms = db3.GetRoomByAllFields(subdiv, null, roomId, chief, emplId).OrderBy(o => o.roomId).ToList();
}
}
}
List <TableToFront2> lst = new List <TableToFront2>();
List <TableToFront2> lstChief = new List <TableToFront2>();
Dictionary <string, List <TableToFront2> > dict = new Dictionary <string, List <TableToFront2> >();
foreach (var room in rooms)
{
TableToFront2 tbl2 = new TableToFront2();
tbl2.roomFloor = room.roomFloor;
tbl2.roomNumber = room.uniqueRoomNumber;
tbl2.roomId = room.roomId;
tbl2.OBJECTID = room.OBJECTID;
tbl2.subdivisionId = (int)room.DepartmentId;
tbl2.subdivision = room.SubdivisionName;
tbl2.depId = (int)room.DepartmentId;
tbl2.buildInvNumber = room.invNumber;
tbl2.buildName = room.buildName;
tbl2.filename3ds = room.roomFilename3ds;
if (room.chiefFio != null)
{
tbl2.chiefFio = room.chiefFio;
}
else
{
tbl2.chiefFio = string.Empty;
}
if (room.empFio != null)
{
tbl2.employeeFio = room.empFio;//.Split(',');
}
else
{
tbl2.employeeFio = /*new string[] {*/ string.Empty;
}
lst.Add(tbl2);
}
foreach (var t in lst)
{
if (t.OBJECTID != null)
{
t.isVisible = 1;
}
else
{
t.isVisible = 0;
}
}
//int point3 = 0;
dict.Add("list", lst);
var jsonRes = Json(dict);
jsonRes.MaxJsonLength = int.MaxValue;
return(Json(JsonConvert.SerializeObject(dict)));
}