コード例 #1
0
        public SelectPagnationExDictionary GetMonitorLinkmanList(string lm_uname, string lm_udept, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            if (!String.IsNullOrEmpty(lm_uname))
            {
                where += " and lm_uname like '%" + lm_uname + "%'";
            }

            if (!String.IsNullOrEmpty(lm_udept))
            {
                where += " and lm_udept like '%" + lm_udept + "%'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            //string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);
            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetStatisticDBName();
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"" + maindbName + @".dbo.monitor_linkman", "*", pageIndex + 1, pageSize, orderby, where, "");


            return(result);
        }
コード例 #2
0
        public JsonFlexiGridData QueryApplyDeviceBindList(PageView view)
        {
            string maindbName = DbSqlHelper.GetMainDBName();
            string column     = "ID,UserUid,DeviceId,Ip,Status,ApplyTime,Description";
            string sql        = "select * from " + maindbName + ".dbo.ApplyDeviceBind where status = 0 ";

            return(base.QueryDataForFlexGridByPager(column, string.Format("({0}) as temp", sql), view.OrderBy.ToString(), "ID", string.Empty, view));
        }
コード例 #3
0
        public SelectPagnationExDictionary GetDeviceDisableAuthorizationSys(string uid, string model, string u_unitcode, string u_auth_submit_time_start, string u_auth_submit_time_end, int deviceAuthStatus, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            if (deviceAuthStatus >= 0)
            {
                where += " and dua.status=" + deviceAuthStatus + "";
            }

            if (!String.IsNullOrEmpty(uid))
            {
                where += " and u.u_uid like '%" + uid + "%'";
            }

            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and u.u_unitcode like '%" + u_unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and u.u_unitcode like '%" + u_unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(model))
            {
                where += " and d.model like '%" + model + "%'";
            }

            if (!String.IsNullOrEmpty(u_auth_submit_time_start))
            {
                where += " and dua.applytime >= '" + u_auth_submit_time_start + "'";
            }

            if (!String.IsNullOrEmpty(u_auth_submit_time_end))
            {
                where += " and dua.applytime <= '" + u_auth_submit_time_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            //string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);
            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetStatisticDBName();
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"deviceuserapply dua 
join deviceuser du on du.id=dua.userdeviceid 
join " + maindbName + @".dbo.smc_user u on u.u_uid=du.uid 
join device d on d.id=du.deviceid", "dua.id, u.u_uid useruid,u.u_unitname unitname,u.u_unitcode unitcode,u.u_name username,du.deviceid,d.model,d.description,dua.applytime,dua.status", pageIndex + 1, pageSize, orderby, where, "");


            return(result);
        }
コード例 #4
0
        public SelectPagnationExDictionary GetDeviceAuthorization(string uid, string deviceid, string u_unitcode, string u_auth_submit_time_start, string u_auth_submit_time_end, int deviceAuthStatus, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            if (deviceAuthStatus >= 0)
            {
                where += " and adb.status=" + deviceAuthStatus + "";
            }

            if (!String.IsNullOrEmpty(uid))
            {
                where += " and u_uid like '%" + uid + "%'";
            }

            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and u.u_unitcode like '%" + u_unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and u_unitcode like '%" + u_unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(deviceid))
            {
                where += " and adb.deviceid like '%" + deviceid + "%'";
            }

            if (!String.IsNullOrEmpty(u_auth_submit_time_start))
            {
                where += " and adb.applytime >= '" + u_auth_submit_time_start + "'";
            }

            if (!String.IsNullOrEmpty(u_auth_submit_time_end))
            {
                where += " and adb.applytime <= '" + u_auth_submit_time_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);

            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }

            string maindbName = DbSqlHelper.GetMainDBName();

            SelectPagnationExDictionary result = this.SelectPaginationExDictionary("applydevicebind adb join " + maindbName + ".dbo.smc_user u on adb.useruid=u.u_uid", "id,useruid,u.u_name username,u.u_unitname unitname,adb.status,u_unitcode unitcode,deviceid,description,applytime", pageIndex + 1, pageSize, orderby, where, "");


            return(result);
        }
コード例 #5
0
        public SelectPagnationExDictionary GetMonitorLogList(string timeStart, string timeEnd, string log_status, string log_df_lever, string log_df_item, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            int _log_status = -1;//

            if (!String.IsNullOrEmpty(log_status))
            {
                _log_status = Convert.ToInt32(log_status);
            }
            if (_log_status >= 0)
            {
                where += " and log_status=" + _log_status + "";
            }

            int _log_df_lever = -1;//

            if (!String.IsNullOrEmpty(log_df_lever))
            {
                _log_df_lever = Convert.ToInt32(log_df_lever);
            }
            if (_log_df_lever >= 0)
            {
                where += " and log_df_lever=" + _log_df_lever + "";
            }

            if (!String.IsNullOrEmpty(log_df_item))
            {
                where += " and log_df_item like '%" + log_df_item + "%'";
            }

            if (!String.IsNullOrEmpty(timeStart))
            {
                where += " and log_datetime >= '" + timeStart + "'";
            }

            if (!String.IsNullOrEmpty(timeEnd))
            {
                where += " and log_datetime <= '" + timeEnd + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            //string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);
            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetStatisticDBName();
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"" + maindbName + @".dbo.Monitor_Log", "*", pageIndex + 1, pageSize, orderby, where, "");


            return(result);
        }
コード例 #6
0
        public SelectPagnationExDictionary GetAppPackageSyncList(string appName, string application, string unitcode, string auth_time_start, string auth_time_end, string syncstatus, string orderby, int pageIndex, int pageSize)
        {
            string where = "pe.pe_authstatus=1";

            if (!String.IsNullOrEmpty(appName))
            {
                where += " and pe.pe_Name like '%" + appName + "%'";
            }

            if (!String.IsNullOrEmpty(syncstatus))
            {
                where += " and pe.pe_SyncStatus = '" + syncstatus + "'";
            }
            else
            {
                where += " and (pe.pe_syncstatus=0 or pe.pe_syncstatus=2)";
            }

            if (!String.IsNullOrEmpty(unitcode))
            {
                where += " and pe.pe_unitcode like '%" + unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(application))
            {
                where += " and pe.pe_ApplicationName like '%" + application + "%'";
            }

            if (!String.IsNullOrEmpty(auth_time_start))
            {
                where += " and pe.pe_AuthTime >= '" + auth_time_start + "'";
            }

            if (!String.IsNullOrEmpty(auth_time_end))
            {
                where += " and pe.pe_AuthTime <= '" + auth_time_end + "'";
            }


            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }

            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetStatisticDBName();
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"" + maindbName + @".dbo.smc_packageext pe", "*", pageIndex + 1, pageSize, orderby, where, "");


            return(result);
        }
コード例 #7
0
        public SelectPagnationExDictionary GetApplicationExtList(string appName, string application, string u_unitcode, string u_auth_submit_time_start, string u_auth_submit_time_end, string categoryID, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            if (!String.IsNullOrEmpty(categoryID))
            {
                where += " and pe.pe_CategoryID like '%" + categoryID + "%'";
            }

            if (!String.IsNullOrEmpty(appName))
            {
                where += " and pe.pe_displayName like '%" + appName + "%'";
            }

            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and pe.pe_unitcode like '%" + u_unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and pe.pe_unitcode like '%" + u_unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(application))
            {
                where += " and pe.pe_ApplicationName like '%" + application + "%'";
            }

            if (!String.IsNullOrEmpty(u_auth_submit_time_start))
            {
                where += " and pe.pe_AuthSubmitTime >= '" + u_auth_submit_time_start + "'";
            }

            if (!String.IsNullOrEmpty(u_auth_submit_time_end))
            {
                where += " and pe.pe_AuthSubmitTime <= '" + u_auth_submit_time_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            //string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);
            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetStatisticDBName();
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"" + maindbName + @".dbo.smc_packageext pe", "*", pageIndex + 1, pageSize, orderby, where, "");


            return(result);
        }
コード例 #8
0
        public SelectPagnationExDictionary GetUnImportedAppPackages(string name, string displayName, string clientType, string lost_time_start, string lost_time_end, string type, string orderby, int pageSize, int pageIndex)
        {
            string where = " id not in (select tableid from {0}..smc_packageext) ";

            if (!String.IsNullOrEmpty(name))
            {
                where += " and [name] like '%" + name + "%'";
            }

            if (!String.IsNullOrEmpty(displayName))
            {
                where += " and [displayName] like '%" + displayName + "%'";
            }

            if (!String.IsNullOrEmpty(clientType))
            {
                where += " and [clientType] like '%" + clientType + "%'";
            }

            if (!String.IsNullOrEmpty(type))
            {
                where += " and [type] like '%" + type + "%'";
            }

            if (!String.IsNullOrEmpty(lost_time_start))
            {
                //where += " and d.locktime >= '" + lost_time_start + "'";
            }

            if (!String.IsNullOrEmpty(lost_time_end))
            {
                //where += " and d.locktime <= '" + lost_time_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }

            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetMainDBName();
            string appDBName  = DbSqlHelper.GetStatisticDBName();
            string tableName  = maindbName + ".dbo.package4ai";

            where = String.Format(where, appDBName);
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(tableName, "id,name,displayname,type,clienttype,version,buildver,downloaduri", pageIndex + 1, pageSize, "id desc", where, "");


            return(result);
        }
コード例 #9
0
        public SelectPagnationExDictionary GetMonitorConfigList(string hostname, string updatestatus, string isuse, string enalbe_time_start, string enalbe_time_end, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            int _updatestatus = -1;//

            if (!String.IsNullOrEmpty(updatestatus))
            {
                _updatestatus = Convert.ToInt32(updatestatus);
            }
            if (_updatestatus >= 0)
            {
                where += " and cfg_updatestatus=" + _updatestatus + "";
            }

            if (!String.IsNullOrEmpty(hostname))
            {
                where += " and cfg_hostname like '%" + hostname + "%'";
            }

            if (!String.IsNullOrEmpty(isuse))
            {
                where += " and cfg_isuse like '%" + isuse + "%'";
            }

            if (!String.IsNullOrEmpty(enalbe_time_start))
            {
                where += " and cfg_usedate >= '" + enalbe_time_start + "'";
            }

            if (!String.IsNullOrEmpty(enalbe_time_end))
            {
                where += " and cfg_usedate <= '" + enalbe_time_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            //string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);
            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetStatisticDBName();
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"" + maindbName + @".dbo.Monitor_Config", "*", pageIndex + 1, pageSize, orderby, where, "");


            return(result);
        }
コード例 #10
0
        public SelectPagnationExDictionary GetMonitorCmdList(string cmd_title, string cmd_senddate_start, string cmd_senddate_end, string cmd_executeresult, string cmd_code, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            int _cmd_executeresult = -1;//

            if (!String.IsNullOrEmpty(cmd_executeresult))
            {
                _cmd_executeresult = Convert.ToInt32(cmd_executeresult);
            }
            if (_cmd_executeresult >= 0)
            {
                where += " and cmd_executeresult=" + _cmd_executeresult + "";
            }

            if (!String.IsNullOrEmpty(cmd_title))
            {
                where += " and cmd_title like '%" + cmd_title + "%'";
            }

            if (!String.IsNullOrEmpty(cmd_code))
            {
                where += " and cmd_code like '%" + cmd_code + "%'";
            }

            if (!String.IsNullOrEmpty(cmd_senddate_start))
            {
                where += " and cmd_senddate >= '" + cmd_senddate_start + "'";
            }

            if (!String.IsNullOrEmpty(cmd_senddate_end))
            {
                where += " and cmd_senddate <= '" + cmd_senddate_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            //string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);
            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetStatisticDBName();
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"" + maindbName + @".dbo.Monitor_Cmd", "*", pageIndex + 1, pageSize, orderby, where, "");


            return(result);
        }
コード例 #11
0
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="UntitName">用户名称</param>
        /// <param name="OpTime">访问时间</param>
        /// <returns></returns>
        public List <Statist> GetTimeList(string UserName)
        {
            List <Statist> list = new List <Statist>();
            StringBuilder  sql  = new StringBuilder();

            sql.Append("select UserUid,");
            sql.AppendLine("sum(case Device when 'PAD/ANDROID' then DateDiff(HOUR,LoginTime,LogoutTime) else 0 end)'Pad/Android',");
            sql.AppendLine("sum(case Device when 'PAD/IOS' then DateDiff(HOUR,LoginTime,LogoutTime) else 0 end)'Pad/iOS',");
            sql.AppendLine("sum(case Device when 'PAD/ANDROID' then DateDiff(HOUR,LoginTime,LogoutTime) else 0 end)'Phone/Android',");
            sql.AppendLine("sum(case Device when 'PHONE/IOS' then DateDiff(HOUR,LoginTime,LogoutTime) else 0 end)'Phone/iOS'");
            sql.AppendLine(" from (select * from [UserLoginInfo] where 1=1");
            if (!string.IsNullOrEmpty(UserName))
            {
                sql.Append(string.Format(" and UserUid='{0}'", UserName));
            }
            sql.AppendLine(")a group by UserUid");
            DbSqlHelper.connectionString = @"Data Source=192.168.2.16\sql2008r2,2244;Initial Catalog=SmartBox;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("UserUid"))
                {
                    entity.UserUid = row["UserUid"].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);
        }
コード例 #12
0
        public SelectPagnationExDictionary GetUserRetryLock(string uid, string username, string u_unitcode, string u_lock_time_start, string u_lock_time_end, int lockStatus, string orderby, int pageSize, int pageIndex)
        {
            string where = "u_lock_status=1";

            if (lockStatus >= 0)
            {
                where += " and u.u_lock_status=" + lockStatus + "";
            }

            if (!String.IsNullOrEmpty(uid))
            {
                where += " and u.u_uid like '%" + uid + "%'";
            }

            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and u.u_unitcode like '%" + u_unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(username))
            {
                where += " and u.u_name like '%" + username + "%'";
            }

            if (!String.IsNullOrEmpty(u_lock_time_start))
            {
                where += " and u_lock_time >= '" + u_lock_time_start + "'";
            }

            if (!String.IsNullOrEmpty(u_lock_time_end))
            {
                where += " and u_lock_time <= '" + u_lock_time_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            //string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);
            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetStatisticDBName();
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"dbo.smc_user u", "u.u_id id, u.u_uid useruid,u.u_unitname unitname,u.u_unitcode unitcode,u.u_name username,u.u_enable_status,u_lock_status,u_lock_time,u_lock_expire_time", pageIndex + 1, pageSize, orderby, where, "");


            return(result);
        }
コード例 #13
0
ファイル: Program.cs プロジェクト: ATai2/initData
        static void Main(string[] args)
        {
            IOperator iOperator = new DbSqlHelper();
            Stopwatch sw        = new Stopwatch();

            sw.Start();
            Console.WriteLine("任务开始执行……");
            iOperator.InitTable();
            iOperator.DataTransfer();
//            iOperator.Close();

            Console.WriteLine("任务结束。查看日志请移步日志记录……");
            sw.Stop();
            Console.WriteLine("共计用时: " + sw.ElapsedMilliseconds / 1000 + "s.");
            Console.ReadKey();
        }
コード例 #14
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);
        }
コード例 #15
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);
        }
コード例 #16
0
        public string GetDeviceOnlineUser(string deviceid)
        {
            string maindbName = DbSqlHelper.GetMainDBName();
            string sql        = String.Format(@"select top 1 uid  from {0}..UserAuthLog where deviceid='{1}' and result=1
 order by logintime desc", maindbName, deviceid);

            object obj = this.ExecuteScalar(sql);

            if (obj == null || obj.ToString() == "")
            {
                return("");
            }
            else
            {
                return(obj.ToString());
            }
        }
コード例 #17
0
        public JsonFlexiGridData QueryTime3(SearchStatisticOnlineTime view)
        {
            string maindbName       = DbSqlHelper.GetMainDBName();
            string buadb            = ConfigurationManager.AppSettings["bua_db_name"];
            string statisticsDbName = DbSqlHelper.GetStatisticDBName();
            string sql = @"with t as(
select uid,deviceid,begintime,lasttime,cast(datediff(mi,begintime,isnull(lasttime,getdate())) as numeric(12,3)) times from userusinglog where 1=1 ";

            if (!string.IsNullOrEmpty(view.StartTime))
            {
                sql += " and begintime >= '" + view.StartTime + " 0:00:00'";
            }
            if (!string.IsNullOrEmpty(view.EndTime))
            {
                sql += " and lasttime <= '" + view.EndTime + " 23:59:59'";//logouttime
            }
            sql += @"),u as
(select uid,deviceid,begintime,lasttime,cast(times /60 as numeric(12,2)) times from t),
v as(
select isnull(d.resource,'') clienttype,u.uid,u.deviceid,u.begintime,u.lasttime,u.times from u left join device d on u.deviceid=d.id
),w as(
select clienttype,uid,sum(times) times from v group by clienttype,uid
),x
as (
select usr.u_uid UserUid,usr.u_name User_Full_Name,usr.u_unitname ORG_NAME, 
isnull((select times from w where w.uid=usr.u_uid and w.clienttype='phone/android'),0) as PhoneAndroid,
isnull((select times from w where w.uid=usr.u_uid and w.clienttype='pad/android'),0) as PadAndroid,
isnull((select times from w where w.uid=usr.u_uid and w.clienttype='phone/ios'),0) as PhoneiOS,
isnull((select times from w where w.uid=usr.u_uid and w.clienttype='pad/ios'),0) as PadiOS
 from " + statisticsDbName + @".dbo.smc_user usr
)
select * from x";
            if (!string.IsNullOrEmpty(view.UID))
            {
                sql += " where UserUid = '" + view.UID + "'";//logouttime
            }
            DataSet ds = base.ExecuteDataset(sql, CommandType.Text);

            JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(ds.Tables[0], view, "UserUid");

            result.page  = 1;
            result.total = ds.Tables[0].Rows.Count;
            return(result);
        }
コード例 #18
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);
        }
コード例 #19
0
ファイル: Program.cs プロジェクト: ATai2/initData
        private static void Do()
        {
            ILog log = LogManager.GetLogger("Main");


            IOperator iOperator = new DbSqlHelper();
            Stopwatch sw        = new Stopwatch();

            sw.Start();
            Console.WriteLine("任务开始执行……");
            iOperator.CopyTableList();
            iOperator.InitTable();
            iOperator.DataTransfer();
            //            iOperator.Close();

            Console.WriteLine("任务结束。查看日志请移步日志记录……");
            sw.Stop();
            Console.WriteLine("共计用时: " + sw.ElapsedMilliseconds / 1000 + "s.");
            log.Info("共计用时: " + sw.ElapsedMilliseconds / 1000 + "s.");
        }
コード例 #20
0
        public SelectPagnationExDictionary GetTimeIndex(string uid, string model, string u_unitcode, string lost_time_start, string lost_time_end, string unlost_time_start, string unlost_time_end, int status, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            if (status >= 0)
            {
                //where += " and d.status=" + status + "";
            }



            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and Org_Code like '%" + u_unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(model))
            {
                //where += " and d.model like '%" + model + "%'";
            }

            if (!String.IsNullOrEmpty(lost_time_start))
            {
                //where += " and d.losttime >= '" + lost_time_start + "'";
            }

            if (!String.IsNullOrEmpty(lost_time_end))
            {
                //where += " and d.losttime <= '" + lost_time_end + "'";
            }

            if (!String.IsNullOrEmpty(unlost_time_start))
            {
                //where += " and d.unlosttime >= '" + unlost_time_start + "'";
            }

            if (!String.IsNullOrEmpty(unlost_time_end))
            {
                //where += " and d.unlosttime <= '" + unlost_time_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            //string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);
            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string maindbName = DbSqlHelper.GetStatisticDBName();

            string sql = @"with t as(
select uid,deviceid,begintime,lasttime,cast(datediff(mi,begintime,isnull(lasttime,getdate())) as numeric(12,3)) times from userusinglog where 1=1 ";

            if (!string.IsNullOrEmpty(lost_time_start))
            {
                sql += " and begintime >= '" + lost_time_start + " 0:00:00'";
            }
            if (!string.IsNullOrEmpty(lost_time_end))
            {
                sql += " and lasttime <= '" + lost_time_end + "'";//logouttime
            }
            sql += @"),u as
(select uid,deviceid,begintime,lasttime,cast(times /60 as numeric(12,2)) times from t),
v as(
select isnull(d.resource,'') clienttype,u.uid,u.deviceid,u.begintime,u.lasttime,u.times from u left join device d on u.deviceid=d.id
),w as(
select clienttype,uid,sum(times) times from v group by clienttype,uid
),x
as (
select usr.u_uid UserUid,usr.u_name User_Full_Name,usr.u_unitcode Org_Code,usr.u_unitname ORG_NAME, 
isnull((select times from w where w.uid=usr.u_uid and w.clienttype='phone/android'),0) as PhoneAndroid,
isnull((select times from w where w.uid=usr.u_uid and w.clienttype='pad/android'),0) as PadAndroid,
isnull((select times from w where w.uid=usr.u_uid and w.clienttype='phone/ios'),0) as PhoneiOS,
isnull((select times from w where w.uid=usr.u_uid and w.clienttype='pad/ios'),0) as PadiOS
 from " + maindbName + @".dbo.smc_user usr
)";


            if (!String.IsNullOrEmpty(uid))
            {
                where += " UserUid like '%" + uid + "%'";
            }
            string cte = sql;
            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"x", "*", pageIndex + 1, pageSize, orderby, where, cte);


            return(result);
        }
コード例 #21
0
        public SelectPagnationExDictionary GetStatisticsByUnit(string uid, string model, string u_unitcode, string lost_time_start, string lost_time_end, string unlost_time_start, string unlost_time_end, int status, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            if (status >= 0)
            {
                //where += " and d.status=" + status + "";
            }



            if (!String.IsNullOrEmpty(model))
            {
                //where += " and d.model like '%" + model + "%'";
            }

            if (!String.IsNullOrEmpty(lost_time_start))
            {
                //where += " and d.losttime >= '" + lost_time_start + "'";
            }

            if (!String.IsNullOrEmpty(lost_time_end))
            {
                //where += " and d.losttime <= '" + lost_time_end + "'";
            }

            if (!String.IsNullOrEmpty(unlost_time_start))
            {
                //where += " and d.unlosttime >= '" + unlost_time_start + "'";
            }

            if (!String.IsNullOrEmpty(unlost_time_end))
            {
                //where += " and d.unlosttime <= '" + unlost_time_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }
            //string sql = String.Format("select * from [dbo].SMC_User where 1=1 {0}", where);
            if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
            {
                where = where.Substring(5);
            }
            string statisticsDbName = DbSqlHelper.GetStatisticDBName();


            string column = @"UnitCode,isnull (UnitName, UnitCode) UnitName ,UserCount,PadAndroid,PadiOS,PCWindows,PhoneAndroid,PhoneiOS,UsageCount";

            StringBuilder sql = new StringBuilder();

            sql.Append(@"select UnitCode,(select unit_name from ");
            sql.Append(statisticsDbName);
            sql.Append(@"..[smc_unit] where unit_id =unitcode) 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 ");
            sql.Append(statisticsDbName);

            sql.Append("..[UsageLogDaily] where 1=1");
            sql.Append(")a group by UnitCode");

            string with      = "with t as (" + sql.ToString() + ") ";
            string tableName = "t";

            //string orderBy = "UnitName Asc";
            where += " UnitCode is not null";
            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and UnitCode = '" + u_unitcode + "'";
            }


//            string sql = @"with t as(
//select uid,deviceid,begintime,lasttime,cast(datediff(mi,begintime,isnull(lasttime,getdate())) as numeric(12,3)) times from userusinglog where 1=1 ";
//            if (!string.IsNullOrEmpty(lost_time_start))
//            {
//                sql += " and begintime >= '" + lost_time_start + " 0:00:00'";
//            }
//            if (!string.IsNullOrEmpty(lost_time_end))
//            {
//                sql += " and lasttime <= '" + lost_time_end + "'";//logouttime
//            }
//            sql += @"),u as
//(select uid,deviceid,begintime,lasttime,cast(times /60 as numeric(12,2)) times from t),
//v as(
//select isnull(d.resource,'') clienttype,u.uid,u.deviceid,u.begintime,u.lasttime,u.times from u left join device d on u.deviceid=d.id
//),w as(
//select clienttype,uid,sum(times) times from v group by clienttype,uid
//),x
//as (
//select usr.u_uid UserUid,usr.u_name User_Full_Name,usr.u_unitcode Org_Code,usr.u_unitname ORG_NAME,
//isnull((select times from w where w.uid=usr.u_uid and w.clienttype='phone/android'),0) as PhoneAndroid,
//isnull((select times from w where w.uid=usr.u_uid and w.clienttype='pad/android'),0) as PadAndroid,
//isnull((select times from w where w.uid=usr.u_uid and w.clienttype='phone/ios'),0) as PhoneiOS,
//isnull((select times from w where w.uid=usr.u_uid and w.clienttype='pad/ios'),0) as PadiOS
// from " + maindbName + @".dbo.smc_user usr
//)";


            if (!String.IsNullOrEmpty(uid))
            {
                where += " UserUid like '%" + uid + "%'";
            }

            SelectPagnationExDictionary result = this.SelectPaginationExDictionary(tableName, column, pageIndex + 1, pageSize, orderby, where, with);


            return(result);
        }
コード例 #22
0
        /// <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);
        }
コード例 #23
0
        public JsonFlexiGridData QueryTime2(SearchStatisticOnlineTime view)
        {
            string maindbName       = DbSqlHelper.GetMainDBName();
            string buadb            = ConfigurationManager.AppSettings["bua_db_name"];
            string statisticsDbName = DbSqlHelper.GetStatisticDBName();
            string sql = @"with t as(
select useruid,device,logintime,isnull(logouttime,getdate()) logouttime,datediff(mi,logintime,isnull(logouttime,getdate())) times from " + maindbName + @".dbo.UserLoginInfo  where logouttime is not null
";

            if (!string.IsNullOrEmpty(view.StartTime))
            {
                sql += " where logintime >= '" + view.StartTime + " 0:00:00'";
            }
            if (!string.IsNullOrEmpty(view.EndTime))
            {
                sql += " and logintime <= '" + view.EndTime + " 23:59:59'";//logouttime
            }
            sql +=
                @"),pc as(
select * from t where device='PC/WINDOWS' or device='pc/windows8'
),apad as (
select * from t where device='Pad/Android' or device='ANDROID_PAD'
),aphone as (
select * from t where device='Phone/Android' or device='ANDROID_PHONE'
),ipad as (
select * from t where device='PAD/IOS'
),iphone as (
select * from t where device='PHONE/IOS'
),u as (

select u_uid user_uid,u_name user_name,(select unit_name from " + statisticsDbName + @"..smc_unit where unit_id=bu.u_unitcode) org_name from " + statisticsDbName + @"..smc_user bu where u_unitcode is not null
),suser as(
select distinct useruid from " + maindbName + @".dbo.UserLoginInfo
)
, siphone as(
select useruid,cast(cast(sum(times) as decimal(18,2)) / 60 as decimal(18,2)) time_iphone from iphone group by useruid
), sipad as(
select useruid,cast(cast(sum(times) as decimal(18,2)) / 60 as decimal(18,2)) time_ipad from ipad group by useruid
), saphone as(
select useruid,cast(cast(sum(cast(times as decimal(15,2))) as decimal(18,2)) / 60 as decimal(18,2)) time_aphone from aphone group by useruid
), sapad as(
select useruid,cast(cast(sum(times) as decimal(18,2)) / 60 as decimal(18,2)) time_apad from apad group by useruid
), spc as(
select useruid,cast(cast(sum(times) as decimal(18,2)) / 60 as decimal(18,2)) time_pc from pc group by useruid
),st as(
select suser.useruid,u.user_name User_Full_Name,u.org_name,
isnull(siphone.time_iphone, 0.00) PhoneiOS,
isnull(sipad.time_ipad, 0.00) PadiOS,
isnull(saphone.time_aphone, 0.00) PhoneAndroid,
isnull(sapad.time_apad, 0.00) PadAndroid
from suser left join siphone on suser.useruid=siphone.useruid

 left join sipad on suser.useruid=sipad.useruid
  left join saphone on suser.useruid=saphone.useruid
   left join sapad on suser.useruid=sapad.useruid
   join u on u.user_uid=suser.useruid
   )
   select * from st order by org_name
   ";
            DataSet ds = base.ExecuteDataset(sql, CommandType.Text);

            JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(ds.Tables[0], view, "UserUid");

            result.page  = 1;
            result.total = ds.Tables[0].Rows.Count;
            return(result);
        }
コード例 #24
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);
        }
コード例 #25
0
        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);
        }
コード例 #26
0
        public List <IDictionary <string, object> > GetTaskCenter(string uid, string model, string u_unitcode, string u_auth_submit_time_start, string u_auth_submit_time_end, int deviceAuthStatus, string orderby, int pageSize, int pageIndex)
        {
            string where = "";

            if (deviceAuthStatus >= 0)
            {
                where += " and dua.status=" + deviceAuthStatus + "";
            }

            if (!String.IsNullOrEmpty(uid))
            {
                where += " and u.u_uid like '%" + uid + "%'";
            }



            if (!String.IsNullOrEmpty(u_unitcode))
            {
                where += " and u.u_unitcode like '%" + u_unitcode + "%'";
            }

            if (!String.IsNullOrEmpty(model))
            {
                where += " and d.model like '%" + model + "%'";
            }

            if (!String.IsNullOrEmpty(u_auth_submit_time_start))
            {
                where += " and dua.applytime >= '" + u_auth_submit_time_start + "'";
            }

            if (!String.IsNullOrEmpty(u_auth_submit_time_end))
            {
                where += " and dua.applytime <= '" + u_auth_submit_time_end + "'";
            }

            if (!String.IsNullOrEmpty(orderby))
            {
                //orderby = " order by " + orderby;
            }

            //bool isSystemManager = true;
            string sUnitCondition1 = "";

            if (!String.IsNullOrEmpty(u_unitcode))
            {
                sUnitCondition1 = " and u.u_unitcode = '" + u_unitcode + "'";
            }
            string s = String.Format(@"with t_deviceauth as(
select 'deviceauth' t,count(dua.id) cn from smartbox.dbo.deviceuserapply dua join smartbox.dbo.deviceuser
du on du.id=dua.DeviceUserID join smartboxapp.dbo.smc_user u on u.u_uid=du.uid 
where dua.status=0 {0})
,t_userauth as (
select 'userauth' t,count(u_id) cn from smartboxapp.dbo.smc_user u where u.u_enable_status = 2 {1}
),t_deviceunlock as (
select 'deviceunlock' t,count(d.id) cn from smartbox.dbo.device d 
join smartbox.dbo.deviceuser du on d.id=du.deviceid 
join smartboxapp.dbo.smc_user u on u.u_uid=du.uid
where d.status=1 {2}
),t_userunlock as (
select 'userunlock' t,count(u_uid) cn from smartboxapp.dbo.SMC_User u where u_lock_status=1 {3}
)
select * from t_deviceauth union all
select * from t_userauth union all
select * from t_deviceunlock union all
select * from t_userunlock", sUnitCondition1, sUnitCondition1, sUnitCondition1, sUnitCondition1);

            where = "";
            string statisticDBName = DbSqlHelper.GetStatisticDBName();
            string maindbName      = DbSqlHelper.GetMainDBName();

            s = s.Replace("smartboxapp.", statisticDBName + ".");
            s = s.Replace("smartbox.", maindbName + ".");

            DataSet ds = base.ExecuteDataset(s, System.Data.CommandType.Text);
            List <IDictionary <string, object> > r = TranslateTable(ds.Tables[0]);

            //SelectPagnationExDictionary result = this.SelectPaginationExDictionary(@"", "*", pageIndex + 1, pageSize, orderby, where, s);


            return(r);
        }
コード例 #27
0
        /// <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);
        }
コード例 #28
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);
        }