public List <ChartDataEntity> GetChartMessage(string username, DateTime start, DateTime end) { var account = (from x in this.DB.Set <WxAccountEntity>() where x.UserName == username select x).First(); DateTime endTime = end.AddDays(1); var query = from y in ( (from x in this.DB.Set <WxTextEntity>() where x.ToUserName == account.WeiXinNo && x.RecieveTime >= start && x.RecieveTime < endTime select new { Type = "文本", x.RecieveTime, x.ID }) .Union(from x in this.DB.Set <WxImageEntity>() where x.ToUserName == account.WeiXinNo && x.RecieveTime >= start && x.RecieveTime < endTime select new { Type = "图片", x.RecieveTime, x.ID }) .Union(from x in this.DB.Set <WxRecognitionEntity>() where x.ToUserName == account.WeiXinNo && x.RecieveTime >= start && x.RecieveTime < endTime select new { Type = "语音", x.RecieveTime, x.ID }) ) group y by new { y.Type, Year = SqlFunctions.DateName("yyyy", y.RecieveTime), Month = SqlFunctions.DateName("MM", y.RecieveTime), Day = SqlFunctions.DateName("d", y.RecieveTime), } into g select new { DateTime = g.Key.Year + "-" + g.Key.Month + "-" + g.Key.Day, Name = g.Key.Type, Data = g.Count() }; var list = new List <ChartItemEntity>(); string[] sites = new string[] { "文本", "图片", "语音" }; //获取日期列表 var dir = new Dictionary <string, int>(); DateTime temp = start; while (temp <= end) { dir.Add(temp.ToString("yyyy-MM-d"), 0); temp = temp.AddDays(1); } //包含名称的dict var dirDate = sites.ToDictionary(x => x, x => (Dictionary <string, int>)CloneCommonHelper.Clone(dir)); foreach (var item in query) { dirDate[item.Name][item.DateTime] = item.Data; } list.AddRange(dirDate.SelectMany(keyValuePair => keyValuePair.Value, (keyValuePair, kv) => new ChartItemEntity { Name = keyValuePair.Key, Data = kv.Value })); List <ChartDataEntity> datas = new List <ChartDataEntity>(); foreach (var item in list) { var entity = (from x in datas where x.name == item.Name select x).FirstOrDefault(); if (entity == null) { ChartDataEntity data = new ChartDataEntity(); data.name = item.Name; data.data.Add(item.Data ?? 0); datas.Add(data); } else { entity.data.Add(item.Data ?? 0); } } return(datas); }
protected override IQueryable <ChartItemEntity> LogicDataSourceForXY(IQueryable <VisitInfoEntity> query, DateTime start, DateTime end, int?sourceType) { bool isDay = Math.Floor((end - start).TotalDays) >= 1; string[] sites; var list = new List <ChartItemEntity>(); if (isDay) { // 按天 var day = from x in query group x by new { x.RefererType, Year = SqlFunctions.DateName("yyyy", x.VisitTime), Month = SqlFunctions.DateName("MM", x.VisitTime), Day = SqlFunctions.DateName("dd", x.VisitTime), } into g select new { DateTime = g.Key.Year + "-" + g.Key.Month + "-" + g.Key.Day, Name = g.Key.RefererType == "0" ? "直接输入" : "搜索引擎", Data = g.Count() }; list.Clear(); sites = (day.Select(x => x.Name)).Distinct().ToArray(); //获取日期dict var dir = new Dictionary <string, int>(); DateTime temp = start; while (temp <= end) { dir.Add(temp.ToString("yyyy-MM-d"), 0); temp = temp.AddDays(1); } //包含名称的dict var dirDate = sites.ToDictionary(site => site, site => (Dictionary <string, int>)CloneCommonHelper.Clone(dir)); foreach (var item in day) { if (dirDate.ContainsKey(item.Name)) { dirDate[item.Name][item.DateTime] = item.Data; } } list.AddRange(dirDate.SelectMany(keyValuePair => keyValuePair.Value, (keyValuePair, kv) => new ChartItemEntity { Name = keyValuePair.Key, Data = kv.Value })); #region 修改成Linq前代码 //foreach (KeyValuePair<string, Dictionary<string, int>> keyValuePair in dirDate) //{ // foreach (KeyValuePair<string, int> kv in keyValuePair.Value) // { // var chartItem = new ChartItemEntity { Name = keyValuePair.Key, Data = kv.Value }; // list.Add(chartItem); // } //} #endregion #region 第一版 //获取日期数组 //var datetime = new List<string>(); //DateTime temp = start; //while (temp <= end) //{ // datetime.Add(temp.ToString("yyyy-MM-dd")); // temp = temp.AddDays(1); //} //foreach (var site in sites) //{ // foreach (var date in datetime) // { // var now = (from x in day // where x.DateTime == date // && x.Name == site // select x).FirstOrDefault(); // var item = new ChartItemEntity(); // if (now == null) // { // item.Name = site; // item.Data = 0; // } // else // { // item.Name = now.Name; // item.Data = now.Data; // } // list.Add(item); // } //} #endregion return(list.AsQueryable()); } else { // 按小时 var nows = from x in query group x by new { x.RefererType, Year = SqlFunctions.DatePart("yyyy", x.VisitTime), Month = SqlFunctions.DatePart("MM", x.VisitTime), Day = SqlFunctions.DatePart("dd", x.VisitTime), Hour = SqlFunctions.DatePart("HH", x.VisitTime), } into g select new { g.Key.Hour, Name = g.Key.RefererType == "0" ? "直接输入" : "搜索引擎", Data = g.Count() }; list.Clear(); var dirHours = new Dictionary <int, int>(); int time = DateTime.Now.Hour; if (start != DateTime.Now.Date) { time = 23; } for (int i = 0; i <= time; i++) { dirHours.Add(i, 0); } sites = (from x in nows select x.Name).Distinct().ToArray(); var dirDate = sites.ToDictionary(site => site, site => (Dictionary <int, int>)CloneCommonHelper.Clone(dirHours)); foreach (var item in nows) { dirDate[item.Name][(int)item.Hour] = item.Data; } list.AddRange(dirDate.SelectMany(keyValuePair => keyValuePair.Value, (keyValuePair, kv) => new ChartItemEntity { Name = keyValuePair.Key, Data = kv.Value })); #region 第一版 //List<int> hours; //if (start == DateTime.Now.Date) //{ // int time = DateTime.Now.Hour; // hours = new List<int>(); // for (int i = 0; i <= time; i++) // { // hours.Add(i); // } //} //else //{ // hours = new List<int> { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 }; //} //sites = (from x in nows select x.Name).Distinct().ToArray(); //foreach (var site in sites) //{ // foreach (var hour in hours) // { // var now = (from x in nows // where x.Hour == hour // && x.Name == site // select x).FirstOrDefault(); // var item = new ChartItemEntity(); // if (now == null) // { // item.Name = site; // item.Data = 0; // } // else // { // item.Name = now.Name; // item.Data = now.Data; // } // list.Add(item); // } //} #endregion return(list.AsQueryable()); } }