public SelectPagnationEx <_OnLineUser> GetUserInfoManageList(string uid, string uname, string u_unitcode, string u_disable_time_start, string u_disable_time_end, string u_enable_time_start, string u_enable_time_end, int lockStatus, int enabledStatus, int authStatus, string orderby, int pageSize, int pageIndex)
{
string where = "";
if (lockStatus >= 0)
{
where += " and u_lock_status=" + lockStatus + "";
}
if (enabledStatus >= 0)
{
where += " and u_enable_status=" + enabledStatus + "";
}
if (authStatus >= 0)
{
where += " and u_auth_status=" + authStatus + "";
}
if (!String.IsNullOrEmpty(uid))
{
where += " and u_uid like '%" + uid + "%'";
}
if (!String.IsNullOrEmpty(u_unitcode))
{
where += " and u_unitcode like '%" + u_unitcode + "%'";
}
if (!String.IsNullOrEmpty(uname))
{
where += " and u_name like '%" + uname + "%'";
}
if (!String.IsNullOrEmpty(u_disable_time_start))
{
where += " and u_disable_time >= '" + u_disable_time_start + "'";
}
if (!String.IsNullOrEmpty(u_disable_time_end))
{
where += " and u_disable_time <= '" + u_disable_time_end + "'";
}
if (!String.IsNullOrEmpty(u_enable_time_start))
{
where += " and u_enable_time >= '" + u_enable_time_start + "'";
}
if (!String.IsNullOrEmpty(u_enable_time_end))
{
where += " and u_enable_time <= '" + u_enable_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);
}
SelectPagnationEx <SMC_User> ex = this.SelectPaginationExT("SMC_User", "*", pageIndex + 1, pageSize, orderby, where, "");
SelectPagnationEx <_OnLineUser> ex2 = new SelectPagnationEx <_OnLineUser>();
List <_OnLineUser> lst = new List <_OnLineUser>();
string mainDbName = SmartBox.Console.Common.DbSqlHelper.GetMainDBName();
foreach (SMC_User u in ex.Result)
{
_OnLineUser _u = new _OnLineUser();
_u.U_ID = u.U_ID.ToString();
_u.U_NAME = u.U_NAME;
_u.U_UID = u.U_UID;
_u.U_UnitCode = u.U_UNITCODE;
_u.u_enable_time = u.u_enable_time;
_u.u_disable_time = u.u_disable_time;
_u.last_ip = "";
_u.U_UnitName = u.u_unitname;
_u.Status = _get_useronline_status(mainDbName, u.U_UID);
_u.LastLoginTime = _get_useronline_LastLoginTime(mainDbName, u.U_UID);
_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;
//Hashtable pars = new Hashtable();
//SplitPageResult<SMC_User> re = this.QuerySplitPage<SMC_User>(sql, "*", orderby, pageSize, pageIndex, pars);
return(ex2);
}
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);
}
