Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
        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);
        }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
0
        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);
        }