public SelectPagnationExDictionary GetApplicationManageList(string app, string unitcode, string orderby, int pageSize, int pageIndex)
{
string where = " 1=1 ";
if (!String.IsNullOrEmpty(app))
{
where += " and DisplayName like '%" + app + "%'";
}
if (!String.IsNullOrEmpty(unitcode))
{
where += " and unit= '" + unitcode + "'";
}
if (!String.IsNullOrEmpty(orderby))
{
//orderby = " order by " + orderby;
}
if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
{
where = where.Substring(5);
}
SelectPagnationExDictionary ex = this.SelectPaginationExDictionary("[application]", "*", pageIndex + 1, pageSize, orderby, where, "");
return(ex);
}
public virtual SelectPagnationExDictionary GetUnImportedAppPackages(string name, string displayName, string clientType, string lost_time_start, string lost_time_end, string type, string orderby, int pageSize, int pageIndex)
{
Package4AIDao dao = new Package4AIDao(AppConfig.mainDbKey);
SelectPagnationExDictionary result = dao.GetUnImportedAppPackages(name, displayName, clientType, lost_time_start, lost_time_end, type, orderby, pageSize, pageIndex);
return(result);
}
public SelectPagnationExDictionary SelectPaginationExDictionary(string tableName, string columns, int currentPageIndex, int pageSize, string orderBy, string where, string with)
{
SelectPagnationEx ex = SelectPaginationEx(tableName, columns, currentPageIndex, pageSize, orderBy, where, with);
SelectPagnationExDictionary exusr = SelectPagnationExDictionaryHelper.Translate(ex);
return(exusr);
}
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);
}
public SelectPagnationExDictionary GetPrivilegeManageList(string app, string unitcode, string orderby, int pageSize, int pageIndex)
{
string where = " 1=1 ";
if (!String.IsNullOrEmpty(app))
{
where += " and app.DisplayName like '%" + app + "%'";
}
if (!String.IsNullOrEmpty(unitcode))
{
where += " and app.unit= '" + unitcode + "'";
}
if (!String.IsNullOrEmpty(orderby))
{
//orderby = " order by " + orderby;
}
if (where.StartsWith(" and ", StringComparison.CurrentCultureIgnoreCase))
{
where = where.Substring(5);
}
SelectPagnationExDictionary ex = this.SelectPaginationExDictionary("[application] app left join [AppPrivilege] ap on app.PrivilegeID=ap.ID", "app.id,app.createtime created_time,app.Name,app.DisplayName,ap.BuaAppCode,ap.EnableSync,ap.SyncIntervalTime,ap.SyncLastTime,ap.id apid,ap.DisplayName PrivilegeName,ap.BuaPrivilegeCode", pageIndex + 1, pageSize, orderby, where, "");
return(ex);
}
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 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);
}
/// <summary>
/// 查询用户的设备
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public List <IDictionary <string, object> > GetUserDevices(string uid)
{
//string s = "select d.id,d.os,d.model,d.description,d.remark from device d join deviceuser du on du.deviceid=d.id
//where du.uid='liuyan'";
SelectPagnationExDictionary result = this.SelectPaginationExDictionary("device d join deviceuser du on du.deviceid=d.id", "d.id,d.os+'-'+d.model model", 1, 200, "d.id", "du.uid='" + uid + "'", "");
return(result.Result);
}
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);
}
public SelectPagnationExDictionary GetUserDisableAuthorizationException(string uid, string username, 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 = "ue.type=2";
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(username))
{
where += " and u.u_name like '%" + username + "%'";
}
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(@"smc_userexception ue
join smc_user u on u.u_uid=ue.uid ", @"ue.id, u.u_uid useruid,u.u_unitname unitname,
u.u_unitcode unitcode,u.u_name username ", pageIndex + 1, pageSize, orderby, where, "");
return(result);
}
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);
}
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);
}
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 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);
}
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);
}
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);
}
public SelectPagnationEx <_OnLineUser> GetUserInfoManage(string uid, string name, string unitcode, string orderby, int pageSize, int pageIndex)
{
string mainDbName = SmartBox.Console.Common.DbSqlHelper.GetMainDBName();
string statisticDbName = SmartBox.Console.Common.DbSqlHelper.GetStatisticDBName();
// string with = @"with x as(
//select createtime lastusetime ,SessionValue.value('(/Session/UserInfo/Uid)[1]', 'varchar(50)') uid
//,SessionValue.value('(/Session/UserInfo/Status)[1]', 'varchar(50)') Status
//,SessionValue.value('(/Session/ClientInfo/ID)[1]', 'varchar(50)') DeviceID
//,SessionValue.value('(/Session/ClientInfo/Resource)[1]', 'varchar(50)') ClientType
//from " + mainDbName + @"..SessionStore
//),y as (
//select lastusetime,uid,Status,DeviceID,ClientType,rank() over (partition by uid order by lastusetime desc) r from x
//),
//
//t as (
//select id,uid,DeviceID,ClientType,status,lastlogintime,lastlogouttime,RANK() over (partition by uid order by lastlogintime desc) r from " + mainDbName + @"..useronline where status=1
//)
//,p as(
//select * from t where r>=1 and r=1
//)
//,q as(
//select p.UID,p.DeviceID,p.ClientType,p.Status,p.LastLoginTime,p.LastLogoutTime,y.lastusetime from p join y on p.UID=y.uid where y.r=1
// ),r as(
// select q.*,u.u_id,u.u_name,u.u_unitcode,u.u_unitname,u.u_enable_time,u.u_disable_time from q join " + statisticDbName+@"..smc_user u on q.UID=u.u_uid
// )";
string with = @"with t as (
select id,uid,deviceid,clienttype,status,lastlogintime,lastlogouttime,RANK() over (partition by uid order by lastlogintime desc) r from " + mainDbName + @"..useronline where status=1
),u as(
select createtime lastusetime ,SessionValue.value('(/Session/UserInfo/Uid)[1]', 'varchar(50)') uid
,SessionValue.value('(/Session/UserInfo/Status)[1]', 'varchar(50)') Status
,SessionValue.value('(/Session/ClientInfo/ID)[1]', 'varchar(50)') DeviceID
,SessionValue.value('(/Session/ClientInfo/Resource)[1]', 'varchar(50)') ClientType
from " + mainDbName + @"..SessionStore
),r as(
select t.*,u.u_id,u.u_name,u.u_unitcode,u.u_unitname,u.u_enable_time,u.u_disable_time from t join " + statisticDbName + @"..smc_user u on t.UID=u.u_uid
),s as(
select r.*,(select top 1 logintime from " + mainDbName + @"..useronlinelog where uid=r.uid and deviceid=r.DeviceID order by logintime desc) lastusetime from r where r=1)";
string where = "";
if (!String.IsNullOrEmpty(unitcode))
{
where += " and u_unitcode='" + unitcode + "'";
}
if (!String.IsNullOrEmpty(uid))
{
where += " and uid = '" + uid + "'";
}
if (!String.IsNullOrEmpty(name))
{
where += " and u_name like '%" + name + "%'";
}
if (where.StartsWith(" and"))
{
where = where.Substring(4);
}
SelectPagnationExDictionary ex = this.SelectPaginationExDictionary("s", "*", pageIndex + 1, pageSize, orderby, where, with);
SelectPagnationEx <_OnLineUser> ex2 = new SelectPagnationEx <_OnLineUser>();
List <_OnLineUser> lst = new List <_OnLineUser>();
foreach (IDictionary <string, object> u in ex.Result)
{
_OnLineUser _u = new _OnLineUser();
_u.U_ID = u["u_id"].ToString();
_u.U_NAME = u["u_name"].ToString();
_u.U_UID = u["uid"].ToString();
_u.ClientType = u["clienttype"].ToString();
_u.DeviceID = u["deviceid"].ToString();
_u.U_UnitCode = u["u_unitcode"].ToString();
_u.u_enable_time = u["u_enable_time"] == null ? DateTime.MinValue: (DateTime)u["u_enable_time"];
_u.u_disable_time = u["u_disable_time"] == null ? DateTime.MinValue : (DateTime)u["u_disable_time"];
_u.last_ip = "";
_u.lastusetime = u["lastusetime"] == null ? DateTime.MinValue : (DateTime)u["lastusetime"];
_u.U_UnitName = u["u_unitname"].ToString();
_u.Status = u["status"].ToString() == "1" ? "在线" : "不在线"; //_get_useronline_status(mainDbName, u.U_UID);
_u.LastLoginTime = u["lastlogintime"] == null ? DateTime.MinValue : (DateTime)u["lastlogintime"]; //_get_useronline_LastLoginTime(mainDbName, u.U_UID);
_u.LastLogoutTime = u["lastlogouttime"] == null ? DateTime.MinValue : (DateTime)u["lastlogouttime"]; // _get_useronline_LastLogoutTime(mainDbName, u.U_UID);
lst.Add(_u);
}
ex2.Result = lst;
ex2.RecordCount = ex.RecordCount;
ex2.PageCount = ex.PageCount;
ex2.ReturnValue = ex.ReturnValue;
return(ex2);
}
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);
}
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);
}
