public List <Statist> GetAppNameList(string UserName, Nullable <DateTime> start, Nullable <DateTime> end)
{
List <Statist> list = new List <Statist>();
StringBuilder sql = new StringBuilder();
sql.Append("select UserName,UnitName,");
sql.AppendLine("sum(case AppName when 'mail' then UsageCount else 0 end)'mail',");
sql.AppendLine("sum(case AppName when 'pt.calendar' then UsageCount else 0 end)'calendar',");
sql.AppendLine("sum(case AppName when 'magazine' then UsageCount else 0 end)'magazine',");
sql.AppendLine("sum(case AppName when 'contact' then UsageCount else 0 end)'contact',");
sql.AppendLine("sum(case AppName when 'callboard' then UsageCount else 0 end)'callboard',");
sql.AppendLine("sum(case AppName when 'sms' then UsageCount else 0 end)'sms',");
sql.AppendLine("sum(case AppName when 'pt.keywork' then UsageCount else 0 end)'keywork',");
sql.AppendLine("sum(case AppName when 'pt.dowork' then UsageCount else 0 end)'dowork',");
sql.AppendLine("sum(case AppName when 'pt.yqzg' then UsageCount else 0 end)'yqzg',");
sql.AppendLine("sum(case AppName when 'pt.instruction' then UsageCount else 0 end)'instruction',");
sql.AppendLine("sum(case AppName when 'pt.dbd' then UsageCount else 0 end)'dbd',");
sql.AppendLine("sum(case AppName when 'pt.fourdo' then UsageCount else 0 end)'fourdo'");
sql.AppendLine(" from (select * from [UsageLogDaily] where 1=1");
if (!string.IsNullOrEmpty(UserName))
{
sql.Append(string.Format(" and UserName='******'", UserName));
}
if (start != null && end == null)
{
sql.AppendLine(" and OpTime='" + start + "'");
}
if (end != null && start == null)
{
sql.AppendLine(" and OpTime='" + end + "'");
}
if (start != null && end != null)
{
if (start <= end)
{
sql.Append(string.Format(" and OpTime between'" + start + "' and '" + end + "'"));
}
}
sql.AppendLine(")a group by UserName,UnitName");
DbSqlHelper.connectionString = @"Data Source=192.168.2.16\sql2008r2,2244;Initial Catalog=SmartBoxApp;User ID=SmartBox;Password=App1234";
var ds = DbSqlHelper.Query(sql.ToString());
foreach (DataRow row in ds.Tables[0].Rows)
{
Statist entity = new Statist();
if (!row.IsNull("UserName"))
{
entity.UserName = row["UserName"].ToString();
}
if (!row.IsNull("UnitName"))
{
entity.UnitName = row["UnitName"].ToString();
}
if (!row.IsNull("mail"))
{
entity.mail = row["mail"].ToString();
}
if (!row.IsNull("calendar"))
{
entity.calendar = row["calendar"].ToString();
}
if (!row.IsNull("magazine"))
{
entity.magazine = row["magazine"].ToString();
}
if (!row.IsNull("contact"))
{
entity.contact = row["contact"].ToString();
}
if (!row.IsNull("callboard"))
{
entity.callboard = row["callboard"].ToString();
}
if (!row.IsNull("sms"))
{
entity.sms = row["sms"].ToString();
}
if (!row.IsNull("keywork"))
{
entity.keywork = row["keywork"].ToString();
}
if (!row.IsNull("dowork"))
{
entity.dowork = row["dowork"].ToString();
}
if (!row.IsNull("yqzg"))
{
entity.yqzg = row["yqzg"].ToString();
}
if (!row.IsNull("instruction"))
{
entity.instruction = row["instruction"].ToString();
}
if (!row.IsNull("dbd"))
{
entity.dbd = row["dbd"].ToString();
}
if (!row.IsNull("fourdo"))
{
entity.fourdo = row["fourdo"].ToString();
}
list.Add(entity);
}
return(list);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="UntitName">单位名称</param>
/// <param name="OpTime">访问时间</param>
/// <returns></returns>
public List <Statist> GetList(string UntitName, Nullable <DateTime> start, Nullable <DateTime> end)
{
List <Statist> list = new List <Statist>();
StringBuilder sql = new StringBuilder();
sql.Append("select UnitName,COUNT(distinct UserName) as UserCount,");
sql.AppendLine("sum(case Device when 'PAd/Android' then UsageCount else 0 end)'Pad/Android',");
sql.AppendLine("sum(case Device when 'PAd/iOS' then UsageCount else 0 end)'Pad/iOS',");
sql.AppendLine("sum(case Device when 'PC/Windows' then UsageCount else 0 end)'PC/Windows',");
sql.AppendLine("sum(case Device when 'Phone/Android' then UsageCount else 0 end)'Phone/Android',");
sql.AppendLine("sum(case Device when 'Phone/iOS' then UsageCount else 0 end)'Phone/iOS'");
sql.AppendLine(",SUM(UsageCount) as UsageCount from (select * from [UsageLogDaily] where 1=1");
if (!string.IsNullOrEmpty(UntitName))
{
sql.Append(string.Format(" and unitName='{0}'", UntitName));
}
if (start != null && end == null)
{
sql.AppendLine(" and OpTime='" + start + "'");
}
if (end != null && start == null)
{
sql.AppendLine(" and OpTime='" + end + "'");
}
if (start != null && end != null)
{
if (start <= end)
{
sql.Append(string.Format(" and OpTime between'" + start + "' and '" + end + "'"));
}
}
sql.AppendLine(")a group by UnitName");
DbSqlHelper.connectionString = @"Data Source=192.168.200.141;Uid=sa;Pwd=App1234;Initial Catalog=SmartBoxApp";
var ds = DbSqlHelper.Query(sql.ToString());
foreach (DataRow row in ds.Tables[0].Rows)
{
Statist entity = new Statist();
if (!row.IsNull("UsageCount"))
{
entity.UsageCount = Convert.ToInt32(row["UsageCount"].ToString());
}
if (!row.IsNull("UserCount"))
{
entity.UserCount = Convert.ToInt32(row["UserCount"].ToString());
}
if (!row.IsNull("UnitName"))
{
entity.UnitName = row["UnitName"].ToString();
}
if (!row.IsNull("PAd/Android"))
{
entity.Pad_Android = Convert.ToInt32(row["PAd/Android"].ToString());
}
if (!row.IsNull("Pad/iOS"))
{
entity.Pad_iOS = Convert.ToInt32(row["Pad/iOS"].ToString());
}
if (!row.IsNull("PC/Windows"))
{
entity.PC_Windows = Convert.ToInt32(row["PC/Windows"].ToString());
}
if (!row.IsNull("Phone/Android"))
{
entity.Phone_Android = Convert.ToInt32(row["Phone/Android"].ToString());
}
if (!row.IsNull("Phone/iOS"))
{
entity.Phone_iOS = Convert.ToInt32(row["Phone/iOS"].ToString());
}
list.Add(entity);
}
return(list);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="UntitName">用户名称</param>
/// <param name="OpTime">访问时间</param>
/// <returns></returns>
public List <Statist> GetUserList(string UserName, Nullable <DateTime> start, Nullable <DateTime> end)
{
List <Statist> list = new List <Statist>();
StringBuilder sql = new StringBuilder();
sql.Append("select UserName,UnitName,");
sql.AppendLine("sum(case Device when 'PAD/ANDROID' then UsageCount else 0 end)'PAD/ANDROID',");
sql.AppendLine("sum(case Device when 'PAD/IOS' then UsageCount else 0 end)'PAD/IOS',");
sql.AppendLine("sum(case Device when 'PHONE/ANDROID' then UsageCount else 0 end)'PHONE/ANDROID',");
sql.AppendLine("sum(case Device when 'PHONE/IOS' then UsageCount else 0 end)'PHONE/IOS'");
sql.AppendLine(" from (select * from [UsageLogDaily] where 1=1");
if (!string.IsNullOrEmpty(UserName))
{
sql.Append(string.Format(" and UserName='******'", UserName));
}
if (start != null && end == null)
{
sql.AppendLine(" and OpTime='" + start + "'");
}
if (end != null && start == null)
{
sql.AppendLine(" and OpTime='" + end + "'");
}
if (start != null && end != null)
{
if (start <= end)
{
sql.Append(string.Format(" and OpTime between'" + start + "' and '" + end + "'"));
}
}
sql.AppendLine(")a group by UserName,UnitName");
DbSqlHelper.connectionString = @"Data Source=192.168.2.16\sql2008r2,2244;Initial Catalog=SmartBoxApp;User ID=SmartBox;Password=App1234";
var ds = DbSqlHelper.Query(sql.ToString());
foreach (DataRow row in ds.Tables[0].Rows)
{
Statist entity = new Statist();
if (!row.IsNull("UserName"))
{
entity.UserName = row["UserName"].ToString();
}
if (!row.IsNull("UnitName"))
{
entity.UnitName = row["UnitName"].ToString();
}
if (!row.IsNull("PAD/ANDROID"))
{
entity.Pad_Android = Convert.ToInt32(row["PAD/ANDROID"].ToString());
}
if (!row.IsNull("PAD/IOS"))
{
entity.Pad_iOS = Convert.ToInt32(row["PAD/IOS"].ToString());
}
if (!row.IsNull("PHONE/ANDROID"))
{
entity.Phone_Android = Convert.ToInt32(row["PHONE/ANDROID"].ToString());
}
if (!row.IsNull("PHONE/IOS"))
{
entity.Phone_iOS = Convert.ToInt32(row["PHONE/IOS"].ToString());
}
list.Add(entity);
}
return(list);
}