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