public ActionResult _102(Rpt102 e) { if (e == null) { e = new Rpt102(); } OAMSEntities db = new OAMSEntities(); var v = db.SiteDetailMores .Where(r => r.Product != null && r.Product.Category1 != null && !string.IsNullOrEmpty(r.SiteDetail.Type) && (string.IsNullOrEmpty(e.Geo1FullName) || (r.SiteDetail.Site.Geo1 != null && r.SiteDetail.Site.Geo1.FullName == e.Geo1FullName)) && (string.IsNullOrEmpty(e.Cat1FullName) || r.Product.Category1.FullName == e.Cat1FullName)) .GroupBy(r => new { r.SiteDetail.Type, Cat1 = r.Product.Category1.Name, Cat2 = r.Product.Category2.Name }) .Select(r => new Rpt102Row { Type = r.Key.Type, Cat1 = r.Key.Cat1, Cat2 = r.Key.Cat2, Count = r.Count() }).ToList(); e.List = v; return(View(e)); }
public ActionResult _103(Rpt103 e) { if (e == null) { e = new Rpt103(); } OAMSEntities db = new OAMSEntities(); e.List = db.SiteDetailMores.Where(r => true && (string.IsNullOrEmpty(e.Geo1FullName) || (r.SiteDetail.Site.Geo1 != null && r.SiteDetail.Site.Geo1.FullName == e.Geo1FullName)) && !string.IsNullOrEmpty(r.SiteDetail.Type) ).GroupBy(r => new { Client = r.Product.Client.Name, Product = r.Product.Name, Type = r.SiteDetail.Type, }) .Select(r => new Rpt103Row { Type = r.Key.Type, Client = r.Key.Client, Product = r.Key.Product, Count = r.Count(), }).ToList(); return(View(e)); }
public JsonResult JsonList() { OAMSEntities db = new OAMSEntities(); var result = db.Sites.Where(r => r.Lng > 0 && r.Lat > 0) .Select(r => new { r.Lat, r.Lng, r.Code, Note = r.Code }) .ToList(); return(Json(result)); }
public JsonResult ListCodeMaster(string searchText, int maxResults, string type) { OAMSEntities db = new OAMSEntities(); var result = db.CodeMasters.Where(r => r.Note.Contains(searchText) && r.Type == type) .Take(maxResults) .Select(r => new { r.ID, r.Code, r.Note }) .ToList(); return(Json(result)); }
public ActionResult Index() { OAMSEntities db = new OAMSEntities(); //var v = db.Sites // .Include("SiteDetails") // .ToList(); var viewFee = QuoteDetailController.IsAuthorize(r => r.ViewFee()); var v = db.Sites.Select(r => new { r.ID, r.Code, GeoFullName = r.Geo3 != null ? r.Geo3.FullName : r.Geo2 != null ? r.Geo2.FullName : r.Geo1 != null ? r.Geo1.FullName : "", r.AddressLine1, r.AddressLine2, Type = r.SiteDetails.Select(r1 => r1.Type).Distinct(), Format = r.SiteDetails.Select(r1 => r1.Format).Distinct(), Product = r.SiteDetails.SelectMany(r1 => r1.SiteDetailMores).Select(r1 => r1.Product).Select(r1 => r1.Name), Client = r.SiteDetails.SelectMany(r1 => r1.SiteDetailMores).Select(r1 => r1.Product).Select(r1 => r1.Client).Select(r1 => r1.Name), r.Score, Count = r.SiteDetails.SelectMany(r1 => r1.SiteDetailPhotoes).Count(), r.LastUpdatedBy, r.LastUpdatedDate, r.CreatedBy, r.CreatedDate, QuoteDetailFees = r.QuoteDetails.SelectMany(r1 => r1.QuoteDetailFees).Where(r1 => r1.Months == 12).Select(r1 => r1.MediaRate), }).ToList() .Select(r => new { r.ID, r.Code, r.GeoFullName, r.AddressLine1, r.AddressLine2, Type = string.Join(",", r.Type.ToArray()), Format = string.Join(",", r.Format.ToArray()), Product = string.Join(",", r.Product.ToArray()), Client = string.Join(",", r.Client.ToArray()), r.Score, r.Count, r.LastUpdatedBy, r.LastUpdatedDate, r.CreatedBy, r.CreatedDate, Rate = viewFee ? string.Join(" | ", r.QuoteDetailFees.Where(r1 => r1.HasValue).Select(r1 => r1.Value.ToString("C"))) : "" }.ToExpando()) .ToList(); ; //return View("Index" + AppSetting.Realm, v); return(View("Index", v)); }
public ActionResult About2() { OAMSEntities db = new OAMSEntities(); var v = db.Sites.Where(r => r.Score < 50).ToList(); v.ForEach(r => r.UpdateScore()); db.SaveChanges(); return(View()); }
public JsonResult ListClient(string searchText, int maxResults) { OAMSEntities db = new OAMSEntities(); var result = db.Clients .Where(r => r.Name != null) .ToList() .Where(r => r.Name.ToLower().Contains(searchText.ToLower()) || r.Name.ToLower().RemoveDiacritics().Contains(searchText.ToLower())) .Take(maxResults) .Select(r => new { r.ID, r.Name }) .ToList(); return(Json(result)); }
public JsonResult ListCats(string searchText, int?level = null) { OAMSEntities db = new OAMSEntities(); var result = db.Categories.Where(r => (r.FullName.Contains(searchText) || r.FullNameNoDiacritics.Contains(searchText)) && (level == null || r.Level == level) ) .Distinct() //.Take(maxResults) .Select(r => new { r.ID, r.FullName }) .ToList(); return(Json(result)); }
public JsonResult ListInstallationPosition1(string searchText) { OAMSEntities db = new OAMSEntities(); var result = db.CodeMasters .Where(r => r.Type == CodeMasterType.InstallationPosition1 && r.Note.Contains(searchText) ) .Distinct() .Select(r => new { ID = r.Code, Name = r.Note }) .ToList(); return(Json(result)); }
public ActionResult _101(Rpt101 e) { OAMSEntities db = new OAMSEntities(); e.List = db.SiteDetails.Where(r => true && (r.Site.Geo1 != null && r.Site.Geo1.FullName == e.Geo1FullName) ).GroupBy(r => new { Geo2 = r.Site.Geo2.Name, r.Type }) .Select(r => new Rpt101Row { Geo2 = r.Key.Geo2, Type = r.Key.Type, Count = r.Count() }).ToList(); return(View(e)); }
public ActionResult _151(string user, DateTime?from, DateTime?to, string geo1) { OAMSEntities db = new OAMSEntities(); List <Site> l = new List <Site>(); if (!string.IsNullOrEmpty(user)) { l = db.Sites.Where(r => true && (r.CreatedDate != null && user == r.CreatedBy && from <= r.CreatedDate && r.CreatedDate <= to) && (r.Geo1 != null && r.Geo1.Name == geo1) ) .ToList() ; } return(View(l)); }
public JsonResult ListContractor(string searchText, int maxResults) { OAMSEntities db = new OAMSEntities(); var result = db.Contractors .ToList() .Where(r => r.Name != null) .Where(r => r.Name.ToLower().Contains(searchText.ToLower()) || r.Name.ToLower().RemoveDiacritics().Contains(searchText.ToLower()) || r.Name.Replace(" ", "").ToLower().Contains(searchText.Replace(" ", "").ToLower()) || (r.ContractorContacts.Count > 0 && r.ContractorContacts.SelectMany(r1 => r1.ContractorContactDetails).Count() > 0 && r.ContractorContacts .SelectMany(r1 => r1.ContractorContactDetails) .Where(r1 => r1.Value != null) .Select(r1 => r1.Value.Replace(" ", "").ToLower()) .Where(r1 => r1.Contains(searchText.Replace(" ", "").ToLower())) .Count() > 0 ) ) .Take(maxResults) .Select(r => new { r.ID, r.Name }) .ToList(); //var result = db.ContractorContactDetails // .ToList() // .Where(r => r.ContractorContact.Contractor.Name != null ) // .Where(r => r.ContractorContact.Contractor.Name.ToLower().Contains(searchText.ToLower()) // || r.ContractorContact.Contractor.Name.ToLower().RemoveDiacritics().Contains(searchText.ToLower()) // || (r.Value != null && r.Value.Replace(" ", "").ToLower().Contains(searchText.Replace(" ", "").ToLower())) // ) // .Select(r => r.ContractorContact.Contractor) // .Distinct() // .Take(maxResults) // .Select(r => new { r.ID, r.Name }) // .ToList(); return(Json(result)); }
//public ActionResult _130() //{ // return View(); //} /// <summary> /// Dynamic Rpt /// </summary> /// <param name="query"></param> /// <returns></returns> public ActionResult _130(string query) { if (string.IsNullOrEmpty(query)) { return(View()); } //query=[{%22Name%22:%22Geo1%22,%22Values%22:[%22HCMC%22]},{%22Name%22:%22Type%22,%22Values%22:[%22WMB%22]}] //query=[{%22Name%22:%22Type%22,%22Values%22:[%22WMB%22]}] //query=[{%22Name%22:%22Geo1%22,%22Values%22:[%22HCMC%22]}] //query=[{%22Name%22:%22Geo1%22,%22Values%22:[%22HCMC%22],%22IsShow%22:%22false%22},{%22Name%22:%22Geo2%22},{%22Name%22:%22Type%22,%22IsCount%22:%22true%22}] OAMSEntities db = new OAMSEntities(); List <Rpt130> paramsL = new List <Rpt130>(); JavaScriptSerializer ser = new JavaScriptSerializer(); paramsL = ser.Deserialize <List <Rpt130> >(query); foreach (var item in paramsL.Where(r => (r.Values == null || r.Values.Count == 0) && r.IsCount).ToList()) { switch (item.Name) { case "Type": item.Values = db.CodeMasters.Where(r => r.Type == CodeMasterType.Type).OrderBy(r => r.Order).Select(r => r.Code).ToList(); item.Values.Add(""); break; case "Format": var format = PropertyName.For <CodeMasterType>(r => r.Format); item.Values = db.CodeMasters.Where(r => r.Type == format).OrderBy(r => r.Order).Select(r => r.Code).ToList(); item.Values.Add(""); break; case "Geo1": item.Values = db.Geos.Where(r => r.Level == 1).Select(r => r.Name).ToList(); item.Values.Add(""); break; case "Geo2": var geo1 = paramsL.Where(r => r.Name == "Geo1" && r.Values != null && r.Values.Count > 0 && !r.IsCount).FirstOrDefault(); if (geo1 != null) { var name1 = geo1.Values.FirstOrDefault(); item.Values = db.Geos.Where(r => r.Level == 2 && r.Parent.Name == name1).Select(r => r.Name).ToList(); item.Values.Add(""); } break; case "Category1": item.Values = db.Categories.Where(r => r.Level == 1).Select(r => r.Name).ToList(); item.Values.Add(""); break; case "Category2": var cat1 = paramsL.Where(r => r.Name == "Category1" && r.Values != null && r.Values.Count > 0 && !r.IsCount).FirstOrDefault(); if (cat1 != null) { var name1 = cat1.Values.FirstOrDefault(); item.Values = db.Categories.Where(r => r.Level == 2 && r.Parent.Name == name1).Select(r => r.Name).ToList(); item.Values.Add(""); } break; case "Contractor": item.Values = db.Contractors.Select(r => r.Name).ToList(); item.Values.Add(""); break; case "Product": item.Values = db.Products.Select(r => r.Name).ToList(); item.Values.Add(""); break; case "Client": item.Values = db.Clients.Select(r => r.Name).ToList(); item.Values.Add(""); break; default: break; } } foreach (var item in paramsL.Where(r => r.Values != null && r.Values.Count > 0 && r.IsCount).ToList()) { var np = item.Values.Select(r => new Rpt130() { Name = item.Name, Values = new List <string>() { r }, IsCount = item.IsCount, IsShow = item.IsShow }) .ToList(); paramsL.InsertRange(paramsL.IndexOf(item), np); paramsL.Remove(item); } for (int i = 0; i < paramsL.Count; i++) { var item = paramsL[i]; item.Order = i; item.PName = "P_" + i.ToString(); item.IsShow = item.IsShow ?? true; } var lessThanParam = paramsL.FirstOrDefault(r => r.LessThan.HasValue); int?lessThan = lessThanParam != null ? lessThanParam.LessThan : null; ViewBag.ParamsL = paramsL; var r1 = db.SiteDetailMores.Select(r => r); //List<SiteDetailMore> r1 = db.SiteDetailMores.ToList(); var whereL = paramsL.Where(r => r.Values != null && r.Values.Count > 0 && !r.IsCount); foreach (var item in whereL) { //Should assign to local variable first, why? see the link //http://stackoverflow.com/questions/6096692/filter-iqueryable-in-a-loop-with-multiple-where-statements var values = item.Values; switch (item.Name) { case "Geo1": r1 = r1.Where(r => r.SiteDetail.Site.Geo1 != null && values.Contains(r.SiteDetail.Site.Geo1.Name)); break; case "Geo2": r1 = r1.Where(r => r.SiteDetail.Site.Geo2 != null && values.Contains(r.SiteDetail.Site.Geo2.Name)); break; case "Type": r1 = r1.Where(r => values.Contains(r.SiteDetail.Type)); break; case "Format": r1 = r1.Where(r => values.Contains(r.SiteDetail.Format)); break; case "Contractor": r1 = r1.Where(r => r.SiteDetail.Site.Contractor != null && values.Contains(r.SiteDetail.Site.Contractor.Name)); break; case "Category1": r1 = r1.Where(r => r.Product != null && r.Product.Category1 != null && values.Contains(r.Product.Category1.Name)); break; case "Category2": r1 = r1.Where(r => r.Product != null && r.Product.Category2 != null && values.Contains(r.Product.Category2.Name)); break; case "Client": r1 = r1.Where(r => r.Product != null && r.Product.Client != null && values.Contains(r.Product.Client.Name)); break; case "Product": r1 = r1.Where(r => r.Product != null && values.Contains(r.Product.Name)); break; } } var r2 = r1.ToList().Select(r => new { Geo1 = r.SiteDetail.Site.Geo1 != null ? r.SiteDetail.Site.Geo1.Name : "", Geo2 = r.SiteDetail.Site.Geo2 != null ? r.SiteDetail.Site.Geo2.Name : "", Type = r.SiteDetail.Type ?? "", Format = r.SiteDetail.Format ?? "", Contractor = r.SiteDetail.Site.Contractor != null ? r.SiteDetail.Site.Contractor.Name : "", Product = r.Product != null ? r.Product.Name : "", Client = (r.Product != null && r.Product.Client != null) ? r.Product.Client.Name : "", Category1 = (r.Product != null && r.Product.Category1 != null) ? r.Product.Category1.Name : "", Category2 = (r.Product != null && r.Product.Category2 != null) ? r.Product.Category2.Name : "", }).ToList(); List <ExpandoObject> l = new List <ExpandoObject>(); var selectL = paramsL.Where(r => r.IsShow.HasValue && r.IsShow.Value); foreach (var item in r2) { ExpandoObject row = new ExpandoObject(); var IDicRow = (IDictionary <String, Object>)row; foreach (var s in selectL.OrderBy(r => r.Order)) { switch (s.Name) { case "Geo1": IDicRow.Add(s.PName, item.Geo1); break; case "Geo2": IDicRow.Add(s.PName, item.Geo2); break; case "Type": IDicRow.Add(s.PName, item.Type); break; case "Format": IDicRow.Add(s.PName, item.Format); break; case "Contractor": IDicRow.Add(s.PName, item.Contractor); break; case "Category1": IDicRow.Add(s.PName, item.Category1); break; case "Category2": IDicRow.Add(s.PName, item.Category2); break; case "Client": IDicRow.Add(s.PName, item.Client); break; case "Product": IDicRow.Add(s.PName, item.Product); break; } } l.Add(row); } var l1 = l.GroupBy(r => r, new Rpt130Comparer(paramsL)); var countParams = paramsL.Where(r => r.IsCount); int totalOfLessThan = 0; foreach (var item in l1) { int total = 0; var dic = (IDictionary <string, object>)item.Key; foreach (var param in countParams) { var count = item.Where(r => ((r as IDictionary <string, object>)[param.PName]).ToString() == param.Values.FirstOrDefault()).Count(); dic[param.PName] = count; total += count; } if (countParams.Count() == 0) { dic["TotalCount"] = item.Count(); } else { dic["TotalCount"] = total; } if (lessThan.HasValue) { if ((int)dic["TotalCount"] < lessThan.Value) { totalOfLessThan += (int)dic["TotalCount"]; } } } var l2 = l1.Select(r => r.Key).ToList(); if (lessThan.HasValue) { //var l3 = l2.Where(r => (int)((r as IDictionary<string, object>)[""]) < lessThan.Value); l2.RemoveAll(r => (int)((r as IDictionary <string, object>)["TotalCount"]) < lessThan.Value); ExpandoObject row = new ExpandoObject(); var IDicRow = (IDictionary <String, Object>)row; var last = l2.Last() as IDictionary <string, object>; foreach (var item in last.Keys) { if (lessThanParam.PName == item) { IDicRow.Add(item, "·Other"); } else { var p = paramsL.FirstOrDefault(r => r.PName == item); if (p != null && p.IsCount) { IDicRow.Add(item, ""); } else { IDicRow.Add(item, last[item]); } } } IDicRow["TotalCount"] = totalOfLessThan; l2.Add(row); } var result = l2; return(View(result)); }
public ActionResult _150(Rpt150 rpt) { OAMSEntities db = new OAMSEntities(); if (rpt.userL != null) { var v1 = db.Sites.Where(r => true && (r.CreatedDate != null && rpt.userL.Contains(r.CreatedBy) && rpt.from <= r.CreatedDate && r.CreatedDate <= rpt.to) ) .GroupBy(r => new { r.CreatedBy, r.Geo1.Name }) .Select(r => new Rpt150.Row1 { Name = r.Key.CreatedBy, Geo1 = r.Key.Name, CreateCount = r.Count() } ) .ToList() ; var v2 = db.SitePhotoes.Where(r => true && (r.TakenDate != null && rpt.userL.Contains(r.CreatedBy) && rpt.from <= r.TakenDate && r.TakenDate <= rpt.to) ) .GroupBy(r => new { r.CreatedBy, r.Site.Geo1.Name }) .Select(r => new Rpt150.Row2 { Name = r.Key.CreatedBy, Geo1 = r.Key.Name, SiteCount = r.Select(r1 => r1.SiteID).Distinct().Count(), SitePhotoCount = r.Count() } ) .ToList() ; var v3 = db.SiteDetailPhotoes.Where(r => true && (r.TakenDate != null && rpt.userL.Contains(r.CreatedBy) && rpt.from <= r.TakenDate && r.TakenDate <= rpt.to) ) .GroupBy(r => new { r.CreatedBy, r.SiteDetail.Site.Geo1.Name }) .Select(r => new Rpt150.Row3 { Name = r.Key.CreatedBy, Geo1 = r.Key.Name, SiteDetailCount = r.Select(r1 => r1.SiteDetailID).Distinct().Count(), SiteDetailPhotoCount = r.Count() } ) .ToList() ; var geo1List = v1.Select(r => r.Geo1).Union(v2.Select(r => r.Geo1)).Union(v3.Select(r => r.Geo1)).Distinct().ToList(); //Rpt150 rpt = new Rpt150(); rpt.L1 = v1; rpt.L2 = v2; rpt.L3 = v3; rpt.Geo1L = geo1List; } return(View(rpt)); }
public JsonResult GetSiteInfo(int ID) { OAMSEntities db = new OAMSEntities(); List <SiteDetail> l = new List <SiteDetail>() { db.Sites.Single(r => r.ID == ID).SiteDetails.FirstOrDefault() }; CodeMasterRepository codeMasterRepo = new CodeMasterRepository(); Func <Site, List <C1> > f = r => { var v = r.SiteDetails.SelectMany(r1 => r1.SiteDetailPhotoes) .Select(r1 => new C1 { url = r1.Url.ToUrlPicasaPhotoResize(), id = r1.ID.ToString() }) .ToList(); //if (v.Count == 0) //{ // var v1 = r.SitePhotoes.Select(r1 => new // { // url = r1.Url.ToUrlPicasaPhotoResize(), // id = r1.ID // }.ToExpando()).ToList(); // return v1; //} return(v); }; //Func<Site, List<string>> f = r => //{ // var v = r.SiteDetails.SelectMany(r1 => r1.SiteDetailPhotoes) // .Select(r1 => r1.Url.ToUrlPicasaPhotoResize()).ToList(); // if (v.Count == 0) // { // v = r.SitePhotoes.Select(r1 => // r1.Url.ToUrlPicasaPhotoResize() // ).ToList(); // } // return v; //}; return(Json(l.Distinct().Select(r => new { r.Site.ID, r.Site.Lat, r.Site.Lng, AddressLine1 = r.Site.AddressLine1 ?? "", AddressLine2 = r.Site.AddressLine2 ?? "", Code = r.Site.Code ?? "", r.Format, Type = string.IsNullOrEmpty(r.Type) ? "" : codeMasterRepo.GetNote(CodeMasterType.Type, r.Type), CodeType = r.Type, r.Site.GeoFullName, Address = r.Site.AddressLine1 + " " + r.Site.AddressLine2, Orientation = r.Width >= r.Height ? "Horizontal" : "Vertical", Size = string.Format("{0}m x {1}m", r.Height.ToString(), r.Width.ToString()), Lighting = r.Site.FrontlitNumerOfLamps > 0 ? "Fontlit" : "Backlit", Contractor = r.Site.Contractor != null ? r.Site.Contractor.Name : "", CurrentProduct = r.ToStringProduct, CurrentClient = r.ToStringClient, r.Site.Score, Rating = r.Site.Score.ToRating(), AlbumID = string.IsNullOrEmpty(r.Site.AlbumUrl) ? "" : r.Site.AlbumUrl.Split('/')[9].Split('?')[0], AuthID = string.IsNullOrEmpty(r.Site.AlbumUrl) ? "" : r.Site.AlbumUrl.Split('?')[1].Split('=')[1], PhotoUrlList = f(r.Site), CategoryLevel1 = r.ToStringCategoryLevel1, CategoryLevel2 = r.ToStringCategoryLevel2, Geo2 = r.Site.Geo2 != null ? r.Site.Geo2.Name : "", Geo3 = r.Site.Geo3 != null ? r.Site.Geo3.Name : "", r.Site.TrafficCount, }))); }
private static List <SiteDetail> Find(FindSite e) { OAMSEntities DB = new OAMSEntities(); DB.CommandTimeout = 300; //var geo1 = DB.Geos.FirstOrDefault(r => r.Level == 1 && r.FullName == e.Geo1FullName); //Guid? geo1ID = null; //if (geo1 != null) geo1ID = geo1.ID; var l = DB.SiteDetails .Where(r => true //Find on its own properties && e.StyleList.Contains(r.Type) && (string.IsNullOrEmpty(e.Format) || r.Format == e.Format) //Find on 1 level relationship properties //&& (!e.ViewingDistance.HasValue || r.Site.ViewingDistance == e.ViewingDistance) && (string.IsNullOrEmpty(e.ViewingDistance) || r.Site.ViewingDistance == e.ViewingDistance) && (!e.InstallationPosition2.HasValue || r.Site.InstallationPosition2 == e.InstallationPosition2) && // Angle to Road (!e.RoadType2.HasValue || r.Site.RoadType2 == e.RoadType2) && //Traffic (e.ContractorList.Count == 0 || e.ContractorList.Contains(r.Site.ContractorID)) && (!e.ScoreFrom.HasValue || !e.ScoreTo.HasValue || (r.Site.Score >= e.ScoreFrom && r.Site.Score <= e.ScoreTo)) && (e.InstallationPosition1MarkList.Count == 0 || e.InstallationPosition1MarkList.Contains(r.Site.InstallationPosition1.HasValue ? r.Site.InstallationPosition1.Value : 0)) && ( (!e.NoPhotoFrom.HasValue && !e.NoPhotoTo.HasValue) || r.SiteDetailPhotoes.FirstOrDefault(r1 => r1.TakenDate.HasValue && (!e.NoPhotoFrom.HasValue || r1.TakenDate >= e.NoPhotoFrom) && (!e.NoPhotoTo.HasValue || r1.TakenDate <= e.NoPhotoTo) ) == null ) && ( (!e.HasPhotoFrom.HasValue && !e.HasPhotoTo.HasValue) || r.SiteDetailPhotoes.FirstOrDefault(r1 => r1.TakenDate.HasValue && (!e.HasPhotoFrom.HasValue || r1.TakenDate >= e.HasPhotoFrom) && (!e.HasPhotoTo.HasValue || r1.TakenDate <= e.HasPhotoTo) ) != null ) //&& (!e.HasPhotoFrom.HasValue || r.SiteDetailPhotoes.FirstOrDefault(r1 => r1.TakenDate.HasValue && r1.TakenDate >= e.HasPhotoFrom) != null) //&& (!e.HasPhotoTo.HasValue || r.SiteDetailPhotoes.FirstOrDefault(r1 => r1.TakenDate.HasValue && r1.TakenDate <= e.HasPhotoTo) != null) //&& (string.IsNullOrEmpty(e.Geo1FullName) || (r.Site.Geo1ID == geo1ID)) //Find on 2 level relationship properties && (string.IsNullOrEmpty(e.Geo1FullName) || (r.Site.Geo1 != null && r.Site.Geo1.FullName == e.Geo1FullName)) && (e.Geo2List.Count == 0 || (r.Site.Geo2 != null && e.Geo2List.Contains(r.Site.Geo2.FullName))) //Find on 3 level relationship properties && (e.ProductIDList.Count == 0 || e.ProductIDList.Intersect(r.SiteDetailMores.Select(r2 => r2.ProductID)).Count() > 0) && (e.ClientList.Count == 0 || e.ClientList.Intersect(r.SiteDetailMores.Select(r1 => r1.Product == null ? 0 : r1.Product.ClientID)).Count() > 0) && (e.CatList.Count == 0 || (e.CatList.Intersect(r.SiteDetailMores.Select(r1 => r1.Product.CategoryID1)).Count() > 0 || e.CatList.Intersect(r.SiteDetailMores.Select(r1 => r1.Product.CategoryID2)).Count() > 0 || e.CatList.Intersect(r.SiteDetailMores.Select(r1 => r1.Product.CategoryID3)).Count() > 0 ) ) ) .ToList() .Where(r => true && (!e.IsWithinCircle || Helper.DistanceBetweenPoints(r.Site.Lat, r.Site.Lng, e.Lat, e.Long) <= e.Distance) ).ToList(); return(l); }
public ActionResult _120(Rpt120 e) { bool usingSiteDetailMore = false; OAMSEntities db = new OAMSEntities(); IQueryable <SiteDetailMore> l = db.SiteDetailMores; if (!string.IsNullOrEmpty(e.Geo1FullName)) { l = l.Where(r => r.SiteDetail.Site.Geo1 != null && r.SiteDetail.Site.Geo1.FullName == e.Geo1FullName); } if (!string.IsNullOrEmpty(e.Cat1FullName)) { l = l.Where(r => r.Product != null && r.Product.Category1 != null && r.Product.Category1.FullName == e.Cat1FullName); usingSiteDetailMore = true; } if (!string.IsNullOrEmpty(e.Client)) { l = l.Where(r => r.Product != null && r.Product.Client != null && r.Product.Client.Name == e.Client); usingSiteDetailMore = true; } if (!string.IsNullOrEmpty(e.Type)) { l = l.Where(r => r.SiteDetail.Type == e.Type); } IQueryable <IGrouping <string, SiteDetail> > q1 = null; IQueryable <IGrouping <string, SiteDetailMore> > q2 = null; switch (e.GroupBy) { case "Type": if (usingSiteDetailMore) { q2 = l.GroupBy(r => r.SiteDetail.Type); } else { q1 = l.Select(r => r.SiteDetail).GroupBy(r => r.Type); } break; case "Geo2": if (usingSiteDetailMore) { q2 = l.GroupBy(r => r.SiteDetail.Site.Geo2.Name); } else { q1 = l.Select(r => r.SiteDetail).GroupBy(r => r.Site.Geo2.Name); } break; case "Product": q2 = l.GroupBy(r => r.Product.Name); break; case "Client": q2 = l.GroupBy(r => r.Product.Client.Name); break; default: break; } if (usingSiteDetailMore) { e.List = q2.Select(r => new Rpt120.Row { Note = r.Key, Count = r.Count(), }).ToList(); } else { e.List = q1.Select(r => new Rpt120.Row { Note = r.Key, Count = r.Count(), }).ToList(); } if (e.LessThan > 0) { Func <Rpt120.Row, bool> f = r => r.Count < e.LessThan; var nr = new Rpt120.Row() { Note = "Other" }; nr.Count = e.List.Where(f).Sum(r => r.Count); e.List.RemoveAll(f.ToPredicate()); e.List.Add(nr); } return(View(e)); }