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