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