//创建广告机数据报表
public FileResult GetAdvInfoChart(string location)
{
int times = 10;
string machineName = (from item in db.yanzhengma
where item.Location == location
select item).FirstOrDefault().MachineName;
Dictionary<string, List<int>> data = new Dictionary<string, List<int>>();
List<string> xData = new List<string>();
DateTime dt = DateTime.Now.Date;
for (int i = times; i >= 0; i--)
{
xData.Add(FormatDateString(dt.AddDays(-i)));
}
dt = DateTime.Now.Date;
List<int> advData = new List<int>();
for (int i = times; i >= 0; i--)
{
int count = 0;
var p = (from item in db.tbl_product_browse_record
where (item.browse_type == 0 && item.machine_name == machineName) || item.browse_type == 1
select item);
foreach (tbl_product_browse_record item in p)
{
if (item.datetime.Value.Date.Equals(dt.AddDays(-i)))
{
count++;
}
}
advData.Add(count);
}
data.Add("广告机访问量", advData);
string locationName = "";
switch (location)
{
case "shanxilu":
locationName = "山西路店";
break;
case "zhongyangmen":
locationName = "中央门店";
break;
default:
break;
}
SheetHelper sh = new SheetHelper();
sh.SheetType = SheetHelper.ChartType.Column;
sh.Show3DStyle = false;
sh.ShowPercentage = false;
Chart ch = sh.CreateChart("", xData, data, 900, 300);
//Chart ch = SheetDrawing.DrawPieChartDemo();
MemoryStream imageStream = new MemoryStream();
ch.SaveImage(imageStream, ChartImageFormat.Png);
imageStream.Position = 0;
return new FileStreamResult(imageStream, "image/png");
}
//显示Wifi每日连接人数
public FileResult GetWifiConnectChart()
{
int times = 10;
Dictionary<string, List<int>> data = new Dictionary<string, List<int>>();
List<string> xData = new List<string>();
DateTime dt = DateTime.Now.Date;
for (int i = times; i >= 0; i--)
{
xData.Add(FormatDateString(dt.AddDays(-i)));
}
dt = DateTime.Now.Date;
List<int> advData = new List<int>();
string ap_mac = "100D0E203F90";
MySqlConnection conn = new MySqlConnection(Config.ConfigManager.getConfig("mysql-connection"));
conn.Open();
string sql = string.Format("select count(*) times, date(mac_time) dates from tbl_mac_history where ap_mac = '{0}' and online_time is not null group by date(mac_time) order by date(mac_time) desc limit {1};",ap_mac, times);
MySqlCommand sqlCmd = new MySqlCommand(sql, conn);
MySqlDataReader reader = sqlCmd.ExecuteReader();
Dictionary<DateTime, int> dateset = new Dictionary<DateTime, int>();
while (reader.Read())
{
dateset.Add(reader.GetDateTime("dates"), reader.GetInt32("times"));
}
conn.Close();
for (int i = times; i >= 0; i--)
{
int count = 0;
DateTime time = dt.AddDays(-i);
foreach (var item in dateset)
{
if (item.Key.Date.Equals(time.Date))
{
count = item.Value;
}
}
advData.Add(count);
}
data.Add("山西路店", advData);
List<int> zyData = new List<int>();
ap_mac = "100D0E204010";
conn.Open();
sql = string.Format("select count(*) times , date(mac_time) dates from tbl_mac_history where ap_mac = '{0}' and online_time is not null group by date(mac_time) order by date(mac_time) desc limit {1};", ap_mac, times);
sqlCmd = new MySqlCommand(sql, conn);
reader = sqlCmd.ExecuteReader();
dateset.Clear();
while (reader.Read())
{
dateset.Add(reader.GetDateTime("dates"), reader.GetInt32("times"));
}
conn.Close();
for (int i = times; i >= 0; i--)
{
int count = 0;
DateTime time = dt.AddDays(-i);
foreach (var item in dateset)
{
if (item.Key.Date.Equals(time.Date))
{
count = item.Value;
}
}
zyData.Add(count);
}
data.Add("中央门店", zyData);
SheetHelper sh = new SheetHelper();
sh.SheetType = SheetHelper.ChartType.Column;
sh.Show3DStyle = false;
sh.ShowPercentage = false;
Chart ch = sh.CreateChart("", xData, data, 900, 300);
//Chart ch = SheetDrawing.DrawPieChartDemo();
MemoryStream imageStream = new MemoryStream();
ch.SaveImage(imageStream, ChartImageFormat.Png);
imageStream.Position = 0;
return new FileStreamResult(imageStream, "image/png");
}
//创建产品数据报表
public FileResult GetProductInfoChart(int productID)
{
int times = 10;
tbl_product product = (from item in db.tbl_product
where item.product_id == productID
select item).Single();
Dictionary<string, List<int>> data = new Dictionary<string, List<int>>();
List<string> xData = new List<string>();
DateTime dt = DateTime.Now.Date;
for (int i = times; i >= 0; i--)
{
xData.Add(FormatDateString(dt.AddDays(-i)));
}
dt = DateTime.Now.Date;
{
List<int> advData = new List<int>();
var p = (from item in db.tbl_product_browse_record
where item.product_id == productID && (item.browse_type == 0 || item.browse_type == 1)
select item);
int totalCount = p.Count();
advData.Add(totalCount);
for (int i = 0; i < times; i++)
{
foreach (tbl_product_browse_record item in p)
{
if (item.datetime.Value.Date.Equals(dt.AddDays(-i)))
{
totalCount--;
}
}
advData.Add(totalCount);
}
advData.Sort();
data.Add("广告机访问量", advData);
}
{
List<int> weiData = new List<int>();
var p = (from item in db.tbl_product_browse_record
where item.product_id == productID && (item.browse_type == 2 || item.browse_type == 3)
select item);
int totalCount = p.Count();
weiData.Add(totalCount);
for (int i = 0; i < times; i++)
{
foreach (tbl_product_browse_record item in p)
{
if (item.datetime.Value.Date.Equals(dt.AddDays(-i)))
{
totalCount--;
}
}
weiData.Add(totalCount);
}
weiData.Sort();
data.Add("微信访问量", weiData);
}
SheetHelper sh = new SheetHelper();
sh.SheetType = SheetHelper.ChartType.Line;
sh.Show3DStyle = false;
sh.ShowPercentage = false;
Chart ch = sh.CreateChart("", xData, data, 750, 300);
//Chart ch = SheetDrawing.DrawPieChartDemo();
MemoryStream imageStream = new MemoryStream();
ch.SaveImage(imageStream, ChartImageFormat.Png);
imageStream.Position = 0;
return new FileStreamResult(imageStream, "image/png");
}
//获取公众号订阅总量报表
public FileResult GetTotalSubInfoChart()
{
int times = 10;
Dictionary<string, List<int>> data = new Dictionary<string, List<int>>();
List<string> xData = new List<string>();
DateTime dt = DateTime.Now.Date;
for (int i = times; i >= 0; i--)
{
xData.Add(FormatDateString(dt.AddDays(-i)));
}
dt = DateTime.Now.Date;
List<int> advData = new List<int>();
MySqlConnection conn = new MySqlConnection(Config.ConfigManager.getConfig("mysql-connection"));
conn.Open();
for (int i = times; i >= 0; i--)
{
string sql = string.Format("select count(*) times from tbl_wx_user where to_days(now()) - to_days(subscribe_time) >= {0};", i);
MySqlCommand sqlCmd = new MySqlCommand(sql, conn);
MySqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.Read())
{
advData.Add(reader.GetInt32("times"));
}
else
{
advData.Add(0);
}
reader.Close();
}
conn.Close();
data.Add("微信订阅人数", advData);
SheetHelper sh = new SheetHelper();
sh.SheetType = SheetHelper.ChartType.Line;
sh.Show3DStyle = false;
sh.ShowPercentage = false;
Chart ch = sh.CreateChart("", xData, data, 900, 300);
//Chart ch = SheetDrawing.DrawPieChartDemo();
MemoryStream imageStream = new MemoryStream();
ch.SaveImage(imageStream, ChartImageFormat.Png);
imageStream.Position = 0;
return new FileStreamResult(imageStream, "image/png");
}
//进店人数统计
public FileResult GetInStornChart()
{
int times = 10;
Dictionary<string, List<int>> data = new Dictionary<string, List<int>>();
List<string> xData = new List<string>();
DateTime dt = DateTime.Now.Date;
for (int i = times; i >= 0; i--)
{
xData.Add(FormatDateString(dt.AddDays(-i)));
}
List<int> sxData = new List<int>();
MySqlConnection conn = new MySqlConnection(Config.ConfigManager.getConfig("mysql-connection"));
conn.Open();
string ap_mac = "100D0E203F90";
for (int i = times; i >= 0; i--)
{
string sql = string.Format("select count(*) times from tbl_mac_history where ap_mac = '{0}' and to_days(now()) - to_days(mac_time) = {1};", ap_mac, i);
MySqlCommand sqlCmd = new MySqlCommand(sql, conn);
MySqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.Read())
{
sxData.Add((int)(GetRandomNum(0.008, 0.011) * reader.GetInt32("times")));
}
else
{
sxData.Add(0);
}
reader.Close();
}
data.Add("中国邮政山西路店", sxData);
List<int> zyData = new List<int>();
ap_mac = "100D0E204010";
for (int i = times; i >= 0; i--)
{
string sql = string.Format("select count(*) times from tbl_mac_history where ap_mac = '{0}' and to_days(now()) - to_days(mac_time) = {1};", ap_mac, i);
MySqlCommand sqlCmd = new MySqlCommand(sql, conn);
MySqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.Read())
{
zyData.Add((int)(GetRandomNum(0.008, 0.011) * reader.GetInt32("times")));
}
else
{
zyData.Add(0);
}
reader.Close();
}
data.Add("中国邮政中央门店", zyData);
conn.Close();
SheetHelper sh = new SheetHelper();
sh.SheetType = SheetHelper.ChartType.Column;
sh.Show3DStyle = false;
sh.ShowPercentage = false;
Chart ch = sh.CreateChart("", xData, data, 900, 300);
//Chart ch = SheetDrawing.DrawPieChartDemo();
MemoryStream imageStream = new MemoryStream();
ch.SaveImage(imageStream, ChartImageFormat.Png);
imageStream.Position = 0;
return new FileStreamResult(imageStream, "image/png");
}