public ActionResult OLD_Clients_getItems()
{
var parameters = AjaxModel.GetParameters(HttpContext);
var mng = new CRMManager();
//var items = mng.GetClients().AsQueryable();
var items = mng.GetSQLClients("GetCRMClients").AsQueryable();
if (parameters.filter != null && parameters.filter.Count > 0)
{
var text = parameters.filter.ContainsKey("text") ? parameters.filter["text"].ToString() : "";
var sourceID = parameters.filter.ContainsKey("sourceName") ? RDL.Convert.StrToInt(parameters.filter["sourceName"].ToString(), 0) : 0;
items = items.Where(x =>
(sourceID == 0 || x.sourceID == sourceID)
);
List<int?> statusIDs = new List<int?>();
if (parameters.filter.ContainsKey("statusName"))
{
statusIDs = (parameters.filter["statusName"] as ArrayList).ToArray().Select(x => (int?)RDL.Convert.StrToInt(x.ToString(), 0)).ToList();
}
items = items.Where(x =>
(statusIDs.Count == 0 || statusIDs.Contains(x.statusID))
);
var needActive = parameters.filter.ContainsKey("needActive") ? RDL.Convert.StrToInt(parameters.filter["needActive"].ToString(), -1) : -1;
items = items.Where(x =>
//фильтр в этом месте не срабатывает для needActive=NULL
(needActive == -1 || x.needActive == (needActive==1 ? true : false))
);
DateTime nextContactMin = DateTime.MinValue, nextContactMax = DateTime.MaxValue;
if (parameters.filter.ContainsKey("nextContact") && parameters.filter["nextContact"] != null)
{
var dates = parameters.filter["nextContact"].ToString().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
if (dates.Length > 0)
{
nextContactMin = RDL.Convert.StrToDateTime(dates[0].Trim(), DateTime.MinValue);
}
if (dates.Length > 1)
{
nextContactMax = RDL.Convert.StrToDateTime(dates[1].Trim(), DateTime.MaxValue).AddDays(1);
}
items = items.Where(x =>
(nextContactMin <= x.nextContact && x.nextContact <= nextContactMax));
}
if (text != "")
{
items = items.ToList().Where(x =>
x.fio != null && x.fio.IndexOf(text, StringComparison.CurrentCultureIgnoreCase) >= 0 ||
x.city != null && x.city.IndexOf(text, StringComparison.CurrentCultureIgnoreCase) >= 0 ||
x.note != null && x.note.IndexOf(text, StringComparison.CurrentCultureIgnoreCase) >= 0 ||
x.username != null && x.username.IndexOf(text, StringComparison.CurrentCultureIgnoreCase) >= 0 ||
x.addedBy != null && x.addedBy.IndexOf(text, StringComparison.CurrentCultureIgnoreCase) >= 0
).AsQueryable();
}
}
var sorts = parameters.sort.Split(",".ToArray(), StringSplitOptions.RemoveEmptyEntries);
var directions = parameters.direction.Split(",".ToArray(), StringSplitOptions.RemoveEmptyEntries);
var sort1 = sorts.Length > 0 ? sorts[0] : "";
var direction1 = directions.Length > 0 ? directions[0] : "";
var sort2 = sorts.Length > 1 ? sorts[1] : "";
var direction2 = directions.Length > 1 ? directions[1] : "";
IOrderedQueryable<crm_clients> orderedItems = items.OrderByDescending(p=>p.created);
switch (sort1)
{
case "fio":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.fio);
else orderedItems = items.OrderByDescending(x => x.fio);
break;
case "city":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.city);
else orderedItems = items.OrderByDescending(x => x.city);
break;
case "note":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.note);
else orderedItems = items.OrderByDescending(x => x.note);
break;
case "addedBy":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.addedBy);
else orderedItems = items.OrderByDescending(x => x.addedBy);
break;
case "statusName":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.crm_clientStatuses.name);
else orderedItems = items.OrderByDescending(x => x.crm_clientStatuses.name);
break;
case "sourceName":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.crm_sources.name);
else orderedItems = items.OrderByDescending(x => x.crm_sources.name);
break;
case "subchannel":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.subchannel);
else orderedItems = items.OrderByDescending(x => x.subchannel);
break;
case "username":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.username);
else orderedItems = items.OrderByDescending(x => x.username);
break;
case "needActive":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.needActive);
else orderedItems = items.OrderByDescending(x => x.needActive);
break;
case "nextContact":
if (direction1 == "up") orderedItems = items.OrderBy(x => x.nextContact);
else orderedItems = items.OrderByDescending(x => x.nextContact);
break;
default:
if (direction1 == "up") orderedItems = items.OrderBy(x => x.created);
else orderedItems = items.OrderByDescending(x => x.created);
break;
}
if (sort2 != "")
{
switch (sort2)
{
case "fio":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.fio);
else orderedItems = orderedItems.ThenByDescending(x => x.fio);
break;
case "city":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.city);
else orderedItems = orderedItems.ThenByDescending(x => x.city);
break;
case "note":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.note);
else orderedItems = orderedItems.ThenByDescending(x => x.note);
break;
case "addedBy":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.addedBy);
else orderedItems = orderedItems.ThenByDescending(x => x.addedBy);
break;
case "statusName":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.crm_clientStatuses.name);
else orderedItems = orderedItems.ThenByDescending(x => x.crm_clientStatuses.name);
break;
case "sourceName":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.crm_sources.name);
else orderedItems = orderedItems.ThenByDescending(x => x.crm_sources.name);
break;
case "subchannel":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.subchannel);
else orderedItems = orderedItems.ThenByDescending(x => x.subchannel);
break;
case "username":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.username);
else orderedItems = orderedItems.ThenByDescending(x => x.username);
break;
case "needActive":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.needActive);
else orderedItems = orderedItems.ThenByDescending(x => x.needActive);
break;
case "nextContact":
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.nextContact);
else orderedItems = orderedItems.ThenByDescending(x => x.nextContact);
break;
default:
if (direction2 == "up") orderedItems = orderedItems.ThenBy(x => x.created);
else orderedItems = orderedItems.ThenByDescending(x => x.created);
break;
}
}
var total = orderedItems.Count();
var res2 = orderedItems.Skip(parameters.pageSize * (parameters.page - 1)).Take(parameters.pageSize).ToList();
return Json(new
{
items = res2.Select(x => new
{
x.id,
fio = x.fio ?? "",
city = x.city ?? "",
note = x.note ?? "",
x.sourceID,
sourceName = x.crm_sources != null ? x.crm_sources.name : "",
x.statusID,
statusName = x.crm_clientStatuses != null ? x.crm_clientStatuses.name : "",
addedBy = x.addedBy ?? "",
created = x.created.GetValueOrDefault().ToString("dd.MM.yyyy"),
nextContact = x.nextContact != null ? x.nextContact.GetValueOrDefault().ToString("dd.MM.yyyy") : "",
subchannel = x.subchannel ?? "",
username = x.username ?? "",
needActive=x.needActive.HasValue && x.needActive==true ? "Да" : "Нет"
}),
total = total
}, JsonRequestBehavior.AllowGet);
}