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