public static SelectPagnationExDictionary Translate(SelectPagnationEx ex)
{
SelectPagnationExDictionary result = new SelectPagnationExDictionary();
result.PageCount = ex.PageCount;
result.RecordCount = ex.RecordCount;
result.ReturnValue = ex.ReturnValue;
result.Result = new List <IDictionary <string, object> >();
foreach (System.Data.DataRow dr in ex.Result.Tables[0].Rows)
{
IDictionary <string, object> Obj = new Dictionary <string, object>();
foreach (DataColumn clmn in dr.Table.Columns)
{
if (dr.IsNull(clmn.ColumnName))
{
Obj[clmn.ColumnName] = null;
}
else
{
Obj[clmn.ColumnName] = dr[clmn.ColumnName];
}
}
result.Result.Add(Obj);
}
return(result);
}
public static SelectPagnationEx <T> Translate(SelectPagnationEx re)
{
SelectPagnationEx <T> result = new SelectPagnationEx <T>();
result.PageCount = re.PageCount;
result.RecordCount = re.RecordCount;
result.ReturnValue = re.ReturnValue;
result.Result = new List <T>();
if (re.Result.Tables.Count > 0 && re.Result.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in re.Result.Tables[0].Rows)
{
T t = new T();
Type tp = typeof(T);
System.Reflection.PropertyInfo[] properties = tp.GetProperties();
if (properties != null && properties.Length > 0)
{
foreach (System.Reflection.PropertyInfo p in properties)
{
switch (p.PropertyType.FullName)
{
//case "System.Int32":
// p.SetValue(t, dr[p.Name], null);
// break;
//case "System.String":
// p.SetValue(t, dr[p.Name].ToString(), null);
// break;
default:
if (p.Name != "ObjectEntryState")
{
if (dr.IsNull(p.Name))
{
p.SetValue(t, null, null);
}
else
{
p.SetValue(t, dr[p.Name], null);
}
}
break;
}
}
}
result.Result.Add(t);
}
}
return(result);
}
public JsonFlexiGridData QueryPushNotificationList(PageView view)
{
string tableName = "NotificationReport";
string columns = "id,NotificationID,ReportCode,ReportMessage,AppCode,DeviceToken,Payload,NotificationIdentifier,ExpirationData,Priority";
string orderby = "id desc";
string where = "";
string with = "";
SmartBox.Console.Common.SelectPagnationEx r = base.SelectPaginationEx(tableName, columns, view.PageIndex + 1, view.PageSize, orderby, where, with);
JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(r.Result.Tables[0], view, "id");
result.page = r.PageCount;
result.total = r.RecordCount;
return(result);
}
public JsonFlexiGridData QueryShow(PageView view)
{
/*string column = @"UnitName,UserCount,PadAndroid,PadiOS,PCWindows,PhoneAndroid,PhoneiOS,UsageCount";
*
* 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)'PadAndroid',");
* sql.AppendLine(@"sum(case Device when 'PAd/iOS' then UsageCount else 0 end)'PadiOS',");
* sql.AppendLine(@"sum(case Device when 'PC/Windows' then UsageCount else 0 end)'PCWindows',");
* sql.AppendLine(@"sum(case Device when 'Phone/Android' then UsageCount else 0 end)'PhoneAndroid',");
* sql.AppendLine(@"sum(case Device when 'Phone/iOS' then UsageCount else 0 end)'PhoneiOS'");
* sql.AppendLine(",SUM(UsageCount) as UsageCount from (select * from [UsageLogDaily] where 1=1");
*
* sql.AppendLine(")a group by UnitName");
* return base.QueryDataForFlexGridByPager(column, string.Format("({0}) as temp", sql.ToString()), view.OrderBy.ToString(), "UnitName", string.Empty, view);*/
string column = @"UnitName,UserCount,PadAndroid,PadiOS,PCWindows,PhoneAndroid,PhoneiOS,UsageCount";
StringBuilder sql = new StringBuilder();
sql.Append(@"select UnitName,COUNT(distinct UserName) as UserCount,");
sql.Append(@"sum(case Device when 'PAd/Android' then UsageCount else 0 end) PadAndroid,");
sql.Append(@"sum(case Device when 'PAd/iOS' then UsageCount else 0 end) PadiOS,");
sql.Append(@"sum(case Device when 'PC/Windows' then UsageCount else 0 end) PCWindows,");
sql.Append(@"sum(case Device when 'Phone/Android' then UsageCount else 0 end) PhoneAndroid,");
sql.Append(@"sum(case Device when 'Phone/iOS' then UsageCount else 0 end) PhoneiOS");
sql.Append(",SUM(UsageCount) as UsageCount from (select * from ");
string statisticsDbName = DbSqlHelper.GetStatisticDBName();
sql.Append(statisticsDbName);
sql.Append("..[UsageLogDaily] where 1=1");
sql.Append(")a group by UnitName");
string with = "with t as (" + sql.ToString() + ") ";
string tableName = "t";
string orderBy = "UnitName Asc";
string where = "UnitName is not null";
Log4NetHelper.Info("tableName:" + tableName + " column:" + column + " orderBy:" + orderBy + " where:" + where + " with:" + with);
SmartBox.Console.Common.SelectPagnationEx r = base.SelectPaginationEx(tableName, column, view.PageIndex + 1, view.PageSize, orderBy, where, with);
JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(r.Result.Tables[0], view, "UnitName");
result.page = r.PageCount;
result.total = r.RecordCount;
return(result);
}
public JsonFlexiGridData QueryNeedImportedPackageList(PageView view)
{
string maindbName = DbSqlHelper.GetMainDBName();
string tableName = maindbName + ".dbo.package4ai";
string columns = "id,name,displayname,type,clienttype,version,buildver,downloaduri";
string orderby = "id desc";
string where = "id not in (select tableid from smc_packageext)";
string with = "";
SmartBox.Console.Common.SelectPagnationEx r = base.SelectPaginationEx(tableName, columns, view.PageIndex + 1, view.PageSize, orderby, where, with);
JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(r.Result.Tables[0], view, "id");
result.page = r.PageCount;
result.total = r.RecordCount;
return(result);
}
public JsonFlexiGridData QueryUser(PageView view)
{
string column = @"UserName,UnitName,PadAndroid,PadiOS,PCWindows,PhoneAndroid,PhoneiOS";
string maindbName = DbSqlHelper.GetMainDBName();
string statisticDBName = DbSqlHelper.GetStatisticDBName();
StringBuilder sql = new StringBuilder();
sql.Append("select UserName,UnitName,");
sql.AppendLine("sum(case Device when 'PAD/ANDROID' then UsageCount else 0 end)'PadAndroid',");
sql.AppendLine("sum(case Device when 'PAD/IOS' then UsageCount else 0 end)'PadiOS',");
sql.AppendLine("sum(case Device when 'PC/Windows' then UsageCount else 0 end)'PCWindows',");
sql.AppendLine("sum(case Device when 'PHONE/ANDROID' then UsageCount else 0 end)'PhoneAndroid',");
sql.AppendLine("sum(case Device when 'PHONE/IOS' then UsageCount else 0 end)'PhoneiOS'");
sql.AppendLine(" from (select * from ");
sql.AppendLine(statisticDBName);
sql.AppendLine("..[UsageLogDaily] where 1=1");
sql.AppendLine(")a group by UserName,UnitName");
string with = "with t as (" + sql.ToString() + ") ";
with = with.Replace("[UserLoginInfo]", " " + maindbName + ".dbo.[UserLoginInfo]").Replace("V_BUA_USER", maindbName + ".dbo.V_BUA_USER");
string tableName = "t";
string orderBy = view.OrderBy.ToString().ToLower().Replace(" order by ", "");
string where = "UserName is not null";
with = with.Replace("\r\n", "");
Log4NetHelper.Info("QueryUser-tableName:" + tableName + " column:" + column + " orderBy:" + orderBy + " where:" + where + " with:" + with);
SmartBox.Console.Common.SelectPagnationEx rs = base.SelectPaginationEx(tableName, column, view.PageIndex + 1, view.PageSize, orderBy, where, with);
JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(rs.Result.Tables[0], view, "UnitName");
result.page = rs.PageCount;
result.total = rs.RecordCount;
return(result);
//return base.QueryDataForFlexGridByPager(column, string.Format("({0}) as temp", sql.ToString()), view.OrderBy.ToString(), "UnitName", string.Empty, view);
}
public JsonFlexiGridData QueryTime(SearchStatisticOnlineTime view)
{
string column = @"UserUid,User_Full_Name,ORG_NAME,PadAndroid,PadiOS,PhoneAndroid,PhoneiOS";
StringBuilder sunSql = new StringBuilder();
string startSunSql = string.Empty;
string startTimeSql = string.Empty;
string endSql = string.Empty;
if (!string.IsNullOrEmpty(view.StartTime))
{
startSunSql = string.Format(" when datediff(day,logintime,'{0}')>0 then CONVERT(numeric(8,2),DateDiff(ss,'{0}',LogoutTime)/(60.00*60))", view.StartTime);
startTimeSql = string.Format("(case when datediff(day,LoginTime,'{0}')>0 then '{0}' else LoginTime end)", view.StartTime);
}
else
{
startTimeSql = "LoginTime";
}
if (!string.IsNullOrEmpty(view.EndTime))
{
sunSql.AppendFormat(@"case when LogoutTime is null then CONVERT(numeric(8,2),DATEDIFF(ss,LoginTime,GETDATE())/(60.00*60))
when DateDiff(day,LogoutTime,'{0}')<0 then CONVERT(numeric(8,2),DATEDIFF(ss,LoginTime,'{0}'+' 23:59:59.999')/(60.00*60)) " , view.EndTime);
sunSql.Append(startSunSql);
sunSql.AppendFormat(@" else CONVERT(numeric(8,2),DateDiff(ss,LoginTime,LogoutTime)/(60.00*60)) end ", view.EndTime);
}
else
{
sunSql.AppendFormat("case when LogoutTime is null then CONVERT(numeric(8,2),DateDiff(ss,LoginTime,getdate())/(60.00*60)) else CONVERT(numeric(8,2),DateDiff(ss,{0},LogoutTime)/(60.00*60)) end", startTimeSql);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"select UserUid,User_Full_Name,ORG_NAME,
sum(case Device when 'PAD/ANDROID' then {0} else 0 end)'PadAndroid',
sum(case Device when 'PAD/IOS' then {0} else 0 end)'PadiOS',
sum(case Device when 'PAD/ANDROID' then {0} else 0 end)'PhoneAndroid',
sum(case Device when 'PHONE/IOS' then {0} else 0 end)'PhoneiOS'
from (select info.*,u.USER_FULL_NAME,org.ORG_NAME from [UserLoginInfo] info
inner join V_BUA_USER u on info.UserUid=u.USER_UID
left join bua.dbo.BUA_ORGANIZATION org on u.ORG_ID=org.ORG_ID where 1=1 ", sunSql);
if (!string.IsNullOrEmpty(view.StartTime))
{
sql.AppendFormat(@" and (datediff(day,info.LoginTime,'{0}')<=0 or (info.LogoutTime is not null and datediff(day,info.LogoutTime,'{0}')<=0 and info.LoginTime<='{0}')) ", view.StartTime);
}
if (!string.IsNullOrEmpty(view.EndTime))
{
sql.AppendFormat(" and datediff(day,info.LoginTime,'{0}')>=0 ", view.EndTime);
}
sql.AppendLine(")a group by UserUid,User_Full_Name,ORG_NAME");
string with = "with t as (" + sql.ToString() + ") ";
string maindbName = DbSqlHelper.GetMainDBName();
with = with.Replace("[UserLoginInfo]", " " + maindbName + ".dbo.[UserLoginInfo]").Replace("V_BUA_USER", maindbName + ".dbo.V_BUA_USER");
string tableName = "t";
string orderBy = view.OrderBy.ToString().ToLower().Replace(" order by ", "");
string where = "";
SmartBox.Console.Common.SelectPagnationEx rs = base.SelectPaginationEx(tableName, column, view.PageIndex + 1, view.PageSize, orderBy, where, with);
JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(rs.Result.Tables[0], view, "UserUid");
result.page = rs.PageCount;
result.total = rs.RecordCount;
return(result);
//return base.QueryDataForFlexGridByPager(column, string.Format("({0}) as temp", sql.ToString()), view.OrderBy.ToString(), "UserUid", string.Empty, view);
}
public JsonFlexiGridData QueryAppName(PageView view)
{
List <string> AppNameList = new List <string>(); //所有应用
List <string> AppDisplayNamelist = new List <string>(); //所有应用名称
string conStringMainDb = ConfigurationManager.ConnectionStrings[AppConfig.mainDbKey].ToString(); //AppConfig.mainDbKey;
SqlConnection conn = new SqlConnection(conStringMainDb);
string statisticsDbName = DbSqlHelper.GetStatisticDBName();
try
{
conn.Open();
string sql = "select distinct name,displayName from application where name in (select distinct appname from [" + statisticsDbName + "].[dbo].[UsageLogDaily])";
Log4NetHelper.Info("QueryAppName-sql:" + sql);
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables.Count > 0)
{
foreach (DataRow r in ds.Tables[0].Rows)
{
AppNameList.Add(r.ItemArray[0].ToString());
AppDisplayNamelist.Add(r.ItemArray[1].ToString());
}
}
}
catch (Exception e)
{
Log4NetHelper.Error(e);
}
finally
{
conn.Close();
}
try
{
StringBuilder columns = new StringBuilder("UserName,UnitName");
StringBuilder sql = new StringBuilder();
sql.Append("select UserName,UnitName");
string fformat = ",sum(case AppName when '{0}' then UsageCount else 0 end)'{1}'";
foreach (string s in AppNameList)
{
string ns = s.Replace(".", "@");
string sqlCondition = "";
columns.Append("," + ns);
sqlCondition = string.Format(fformat, s, ns);
sql.Append(sqlCondition);
}
string column = columns.ToString();
sql.Append(" from (select * from ");
sql.Append(statisticsDbName);
sql.Append("..[UsageLogDaily] where 1=1");
sql.Append(")a group by UserName,UnitName");
string with = "with t as (" + sql.ToString() + ") ";
string tableName = "t";
string orderBy = "UnitName Asc";
string where = "UserName is not null";
SmartBox.Console.Common.SelectPagnationEx rs = base.SelectPaginationEx(tableName, column, view.PageIndex + 1, view.PageSize, orderBy, where, with);
JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(rs.Result.Tables[0], view, "UnitName");
result.page = rs.PageCount;
result.total = rs.RecordCount;
return(result);
}
catch (Exception e)
{
Log4NetHelper.Error(e);
return(null);
}
//return base.QueryDataForFlexGridByPager(column, string.Format("({0}) as temp", sql.ToString()), view.OrderBy.ToString(), "UnitName", string.Empty, view);
}