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); }