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