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