/// <summary>
/// 按月统计
/// </summary>
/// <returns></returns>
public List<JsonEModel> GetDataByMonth()
{
DataTable dt = new DataTable();
List<JsonEModel> list = new List<JsonEModel>();
using (SqlHelper sqlhelper = new SqlHelper(constr))
{
string sql = @"
WITH t as (
select convert(varchar(7),dateadd(mm,number,'2015-6-1'),120) as months
from master..spt_values with(nolock)
where type='P' and number>=0
and dateadd(mm,number,'2015-6-1')<'2016-8-1'
)
,t3 as (
SELECT businessId,months,BusinessName
from Sms_Business b with(nolock),t
where 1 = 1
AND b.BusinessID IN(select TOP 1 BusinessID from(SELECT COUNT(*) num,BusinessID from Sms_SendLog with(nolock) group BY BusinessID )x order BY num desc)
)
,t2 as (
SELECT convert(varchar(7),dateadd(mm,2,SendTime),120) months , count(Mobile) num,BusinessID
from Sms_SendLog with(nolock)
where SendTime >='2015-6-1' AND SendTime<'2016-8-1'
group by businessid,convert(varchar(7),dateadd(mm,2,SendTime),120)
)
select a.months,ISNULL(b.num,0) num,a.BusinessID,a.BusinessName from t3 a
left join t2 b on a.months = b.months and a.businessId = b.BusinessId
order by a.businessid,a.months";
dt = sqlhelper.ExecuteSql(sql);
if (dt == null) return null;
if (dt.Rows.Count > 0)
{
JsonEModel model = new JsonEModel();
foreach (DataRow dr in dt.Rows)
{
model.Name = "短信发送量";
model.Data +=dr["num"].ToString()+",";
}
model.Data = "["+model.Data.Remove(model.Data.Length - 1)+"]";
list.Add(model);
}
}
return list;
}
/// <summary>
/// 按星期统计
/// </summary>
/// <returns></returns>
public List<JsonEModel> GetData()
{
DataTable dt = new DataTable();
List<JsonEModel> list = new List<JsonEModel>();
using (SqlHelper sqlhelper = new SqlHelper(constr))
{
string sql = @"WITH t as (
select COUNT(*) Num, datename(weekday,a.sendtime) Weekday,b.BusinessName FROM Sms_SendLog a with(nolock)
inner JOIN Sms_Business b with(nolock) on a.BusinessID=b.businessId and b.dataState=1
where SendTime>='2016-1-1'
GROUP BY b.businessName,datename(weekday,sendtime))
SELECT * FROM t
ORDER BY BusinessName";
dt = sqlhelper.ExecuteSql(sql);
if (dt == null) return null;
if (dt.Rows.Count > 0)
{
JsonEModel model = new JsonEModel();
WeekdaysModel week=new WeekdaysModel();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (string.IsNullOrWhiteSpace(model.Name))
{
model.Name = dt.Rows[i]["BusinessName"].ToString();
}
if (model.Name != dt.Rows[i]["BusinessName"].ToString())
{
model.Data = week.ToString();
list.Add(model);
model=new JsonEModel();
week=new WeekdaysModel();
}
switch (dt.Rows[i]["Weekday"].ToString())
{
case "星期一":
week.Monday = dt.Rows[i]["Num"] == null
? 0
: int.Parse(dt.Rows[i]["Num"].ToString());
break;
case "星期二":
week.Tuesday = dt.Rows[i]["Num"] == null
? 0
: int.Parse(dt.Rows[i]["Num"].ToString());
break;
case "星期三":
week.Wednesday = dt.Rows[i]["Num"] == null
? 0
: int.Parse(dt.Rows[i]["Num"].ToString());
break;
case "星期四":
week.Thursday = dt.Rows[i]["Num"] == null
? 0
: int.Parse(dt.Rows[i]["Num"].ToString());
break;
case "星期五":
week.Friday = dt.Rows[i]["Num"] == null
? 0
: int.Parse(dt.Rows[i]["Num"].ToString());
break;
case "星期六":
week.Saturday = dt.Rows[i]["Num"] == null
? 0
: int.Parse(dt.Rows[i]["Num"].ToString());
break;
case "星期日":
week.Sunday = dt.Rows[i]["Num"] == null
? 0
: int.Parse(dt.Rows[i]["Num"].ToString());
break;
}
}
}
}
return list;
}
/// <summary>
/// 按天统计
/// </summary>
/// <returns></returns>
public List<JsonEModel> GetDataByDay()
{
DataTable dt = new DataTable();
List<JsonEModel> list = new List<JsonEModel>();
using (SqlHelper sqlhelper = new SqlHelper(constr))
{
string sql = @"
WITH t as (
SELECT
number + 1 as Dayss
FROM master..spt_values
WHERE type = 'p' AND DATEADD(DAY, number, CAST(CONVERT(varchar(7), '2016-05-03') + '-01' AS datetime)) < DATEADD(MONTH, 1, CAST(CONVERT(varchar(7), '2016-05-03') + '-01' AS datetime))
)
,t3 as (
SELECT businessId,Dayss,BusinessName from Sms_Business with(nolock),t where DataState = 1
)
,t2 as (
SELECT BusinessID, DATEPART(dd,sendtime) dayss , count(Mobile) num
from Sms_SendLog with(nolock)
where SendTime >='2016-5-26 00:00:00'
group by BusinessID ,DATEPART(dd,sendtime)
)
select a.Dayss,a.BusinessID,a.BusinessName,ISNULL(b.num,0) num from t3 a
left join t2 b on a.Dayss = b.dayss and a.businessId = b.BusinessId
order by a.businessid,a.dayss";
dt = sqlhelper.ExecuteSql(sql);
if (dt == null) return null;
if (dt.Rows.Count > 0)
{
JsonEModel model = new JsonEModel();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (string.IsNullOrWhiteSpace(model.Name))
{
model.Name = dt.Rows[i]["BusinessName"].ToString();
}
if (model.Name != dt.Rows[i]["BusinessName"].ToString())
{
model.Data = "["+model.Data.Remove(model.Data.Length - 1)+"]";
list.Add(model);
model = new JsonEModel();
}
model.Data += dt.Rows[i]["num"].ToString() + ",";
}
}
}
return list;
}