Exemple #1
0
 /// <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;
 }
Exemple #2
0
        /// <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;
        }
Exemple #3
0
 /// <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;
 }