private IFilterStrategy DetermineFilter(FilterOptions options)
{
IFilterStrategy filterStrategy = null;
if (options.Filter == FilterBy.Name && !string.IsNullOrEmpty(options.Name))
{
filterStrategy = new NameFilter(options);
}
else if (options.Filter == FilterBy.Email && !string.IsNullOrEmpty(options.Email))
{
filterStrategy = new EmailFilter(options);
}
else if (options.Filter == FilterBy.Phone && !string.IsNullOrEmpty(options.Phone))
{
filterStrategy = new PhoneFilter(options);
}
else if (options.Filter == FilterBy.From && options.From.HasValue)
{
filterStrategy = new FromFilter(options);
}
else if (options.Filter == FilterBy.To && options.To.HasValue)
{
filterStrategy = new ToFilter(options);
}
else if (options.Filter == FilterBy.Status)
{
filterStrategy = new StatusFIlter(options);
}
else if (options.Filter == FilterBy.Date && options.Date.HasValue)
{
filterStrategy = new DateFilter(options);
}
return(filterStrategy);
}
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)));
}
private string CreateQuery(string action2, List <FromFilter> parsedData)
{
string oper = "";
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":
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":
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":
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":
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":
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":
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 + " ";
}
}
return(action2);
}
