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 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));
}
