public DataTable SearchDateTime(Common.Enums.StatisticalTime style)
{
StringBuilder strSql = new StringBuilder();
var parameters = new List <SqlParameter>();
strSql.Append(string.Format("select convert(varchar({0}),CreateTime,120) as Time,count(*) as Count from eduUser where 1=1", (int)style));
strSql.Append(string.Format(" group by convert(varchar({0}),CreateTime,120)", (int)style));
var paraarry = parameters.ToArray();
return(SqlHelper.SqlHelper.ExecuteDataTable(strSql.ToString(), paraarry));
}
public DataTable SearchCount(bool IsCountByTerritory, bool IsCountByTime, Common.Enums.StatisticalTime countStyle, bool IsSearchTerritory, string territory, Common.Info.PageInfo pageInfo)
{
StringBuilder strSql = new StringBuilder();
var parameters = new List <SqlParameter>();
strSql.Append("select count(*) as Count ");
if (IsCountByTerritory)
{
strSql.Append(",territory as Territory");
}
if (IsCountByTime)
{
strSql.Append(string.Format(",convert(varchar({0}),CreateTime,120) as Time", (int)countStyle));
}
//查询条件
strSql.Append(" from eduCompany where 1=1 ");
if (IsSearchTerritory)
{
if (string.IsNullOrEmpty(territory))
{
strSql.Append(" and territory is null");
}
else
{
strSql.Append(" and territory like '%' +@Territory+'%' ");
var para = new SqlParameter("@Territory", SqlDbType.NVarChar);
para.Value = territory;
parameters.Add(para);
}
}
//统计方式
if (IsCountByTerritory)
{
strSql.Append(" group by ");
strSql.Append(" territory");
if (IsCountByTime)
{
strSql.Append(string.Format(",convert(varchar({0}),CreateTime,120)", (int)countStyle));
}
}
else
{
if (IsCountByTime)
{
strSql.Append(" group by ");
strSql.Append(string.Format(" convert(varchar({0}),CreateTime,120)", (int)countStyle));
}
}
var paraarry = parameters.ToArray();
string pageSql = SqlHelper.SqlHelper.DoPageInfo(strSql, paraarry, pageInfo);
return(SqlHelper.SqlHelper.ExecuteDataTable(pageSql, paraarry));
}
private static string GetListByTime()
{
string strreturn;
bool isCountByTime = Convert.ToBoolean(NetSchool.Common.Library.GetPostBack.GetPostBackStr("isCountByTime"));
string countStyle = NetSchool.Common.Library.GetPostBack.GetPostBackStr("countStyle");
Common.Enums.StatisticalTime style = new Common.Enums.StatisticalTime();
switch (countStyle)
{
case "Y":
style = Common.Enums.StatisticalTime.Year;
break;
case "M":
style = Common.Enums.StatisticalTime.Month;
break;
case "D":
style = Common.Enums.StatisticalTime.Day;
break;
case "H":
style = Common.Enums.StatisticalTime.Hour;
break;
case "mi":
style = Common.Enums.StatisticalTime.Minuter;
break;
default:
style = Common.Enums.StatisticalTime.Second;
break;
}
var eduUserList = NetSchool.BLL.EduUser.GetCountListByDateTime(style: style);
List <object> lists = new List <object>();
if (eduUserList.Rows.Count > 0)
{
foreach (DataRow dr in eduUserList.Rows)
{
var obj = new { name = dr["Time"], value = dr["Count"] };
lists.Add(obj);
}
}
strreturn = JsonConvert.SerializeObject(new { state = "success", msg = "OK", list = lists }, new Newtonsoft.Json.Converters.IsoDateTimeConverter()
{
DateTimeFormat = "yyyy-MM-dd HH:mm:ss"
});
return(strreturn);
}
public static DataTable GetCountListByDateTime(Common.Enums.StatisticalTime style = Common.Enums.StatisticalTime.Year)
{
return(dal.SearchDateTime(style));
}
public static DataTable SearchCount(bool IsCountByTerritory = true, bool IsCountByTime = true, Common.Enums.StatisticalTime countStyle = Common.Enums.StatisticalTime.Year, bool IsSearchTerritory = false, string territory = null, Common.Info.PageInfo pageInfo = null)
{
return(dal.SearchCount(IsCountByTerritory, IsCountByTime, countStyle, IsSearchTerritory, territory, pageInfo));
}
private static string GetListByTime()
{
string strreturn;
bool isCountByTime = Convert.ToBoolean(NetSchool.Common.Library.GetPostBack.GetPostBackStr("isCountByTime"));
bool isCountByTerrtiory = Convert.ToBoolean(NetSchool.Common.Library.GetPostBack.GetPostBackStr("isCountByTerrtiory"));
bool isSearchTerritory = false;
string territory = NetSchool.Common.Library.GetPostBack.GetPostBackStr("strSearch");
if (!string.IsNullOrEmpty(territory))
{
isSearchTerritory = true;
}
string countStyle = NetSchool.Common.Library.GetPostBack.GetPostBackStr("countStyle");
Common.Enums.StatisticalTime style = new Common.Enums.StatisticalTime();
switch (countStyle)
{
case "Y":
style = Common.Enums.StatisticalTime.Year;
break;
case "M":
style = Common.Enums.StatisticalTime.Month;
break;
case "D":
style = Common.Enums.StatisticalTime.Day;
break;
case "H":
style = Common.Enums.StatisticalTime.Hour;
break;
case "mi":
style = Common.Enums.StatisticalTime.Minuter;
break;
default:
style = Common.Enums.StatisticalTime.Second;
break;
}
var eduCompanyList = NetSchool.BLL.EduCompany.SearchCount(IsCountByTerritory: isCountByTerrtiory, IsCountByTime: isCountByTime, countStyle: style, IsSearchTerritory: isSearchTerritory, territory: territory);
List <object> lists = new List <object>();
if (isCountByTerrtiory)
{
if (isCountByTime)
{
}
else
{
foreach (DataRow dr in eduCompanyList.Rows)
{
var obj = new { name = dr["Territory"], value = dr["Count"] };
lists.Add(obj);
}
}
}
else
{
if (isCountByTime)
{
foreach (DataRow dr in eduCompanyList.Rows)
{
var obj = new { name = dr["Time"], value = dr["Count"] };
lists.Add(obj);
}
}
}
strreturn = JsonConvert.SerializeObject(new { state = "success", msg = "OK", list = lists }, new Newtonsoft.Json.Converters.IsoDateTimeConverter()
{
DateTimeFormat = "yyyy-MM-dd HH:mm:ss"
});
return(strreturn);
}
