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 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 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 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 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);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="UntitName">用户名称</param>
/// <param name="OpTime">访问时间</param>
/// <returns></returns>
public List <Statist> GetTimeList(string UserName)
{
List <Statist> list = new List <Statist>();
StringBuilder sql = new StringBuilder();
sql.Append("select UserUid,");
sql.AppendLine("sum(case Device when 'PAD/ANDROID' then DateDiff(HOUR,LoginTime,LogoutTime) else 0 end)'Pad/Android',");
sql.AppendLine("sum(case Device when 'PAD/IOS' then DateDiff(HOUR,LoginTime,LogoutTime) else 0 end)'Pad/iOS',");
sql.AppendLine("sum(case Device when 'PAD/ANDROID' then DateDiff(HOUR,LoginTime,LogoutTime) else 0 end)'Phone/Android',");
sql.AppendLine("sum(case Device when 'PHONE/IOS' then DateDiff(HOUR,LoginTime,LogoutTime) else 0 end)'Phone/iOS'");
sql.AppendLine(" from (select * from [UserLoginInfo] where 1=1");
if (!string.IsNullOrEmpty(UserName))
{
sql.Append(string.Format(" and UserUid='{0}'", UserName));
}
sql.AppendLine(")a group by UserUid");
DbSqlHelper.connectionString = @"Data Source=192.168.2.16\sql2008r2,2244;Initial Catalog=SmartBox;User ID=SmartBox;Password=App1234";
var ds = DbSqlHelper.Query(sql.ToString());
foreach (DataRow row in ds.Tables[0].Rows)
{
Statist entity = new Statist();
if (!row.IsNull("UserUid"))
{
entity.UserUid = row["UserUid"].ToString();
}
//if (!row.IsNull("UnitName"))
//{
// entity.UnitName = row["UnitName"].ToString();
//}
if (!row.IsNull("PAD/ANDROID"))
{
entity.Pad_Android = Convert.ToInt32(row["PAD/ANDROID"].ToString());
}
if (!row.IsNull("PAD/IOS"))
{
entity.Pad_iOS = Convert.ToInt32(row["PAD/IOS"].ToString());
}
if (!row.IsNull("PHONE/ANDROID"))
{
entity.Phone_Android = Convert.ToInt32(row["PHONE/ANDROID"].ToString());
}
if (!row.IsNull("PHONE/IOS"))
{
entity.Phone_iOS = Convert.ToInt32(row["PHONE/IOS"].ToString());
}
list.Add(entity);
}
return(list);
}
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);
}
static void Main(string[] args)
{
IOperator iOperator = new DbSqlHelper();
Stopwatch sw = new Stopwatch();
sw.Start();
Console.WriteLine("任务开始执行……");
iOperator.InitTable();
iOperator.DataTransfer();
// iOperator.Close();
Console.WriteLine("任务结束。查看日志请移步日志记录……");
sw.Stop();
Console.WriteLine("共计用时： " + sw.ElapsedMilliseconds / 1000 + "s.");
Console.ReadKey();
}
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 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 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 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);
}
private static void Do()
{
ILog log = LogManager.GetLogger("Main");
IOperator iOperator = new DbSqlHelper();
Stopwatch sw = new Stopwatch();
sw.Start();
Console.WriteLine("任务开始执行……");
iOperator.CopyTableList();
iOperator.InitTable();
iOperator.DataTransfer();
// iOperator.Close();
Console.WriteLine("任务结束。查看日志请移步日志记录……");
sw.Stop();
Console.WriteLine("共计用时： " + sw.ElapsedMilliseconds / 1000 + "s.");
log.Info("共计用时： " + sw.ElapsedMilliseconds / 1000 + "s.");
}
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);
}
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);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="UntitName">单位名称</param>
/// <param name="OpTime">访问时间</param>
/// <returns></returns>
public List <Statist> GetList(string UntitName, Nullable <DateTime> start, Nullable <DateTime> end)
{
List <Statist> list = new List <Statist>();
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)'Pad/Android',");
sql.AppendLine("sum(case Device when 'PAd/iOS' then UsageCount else 0 end)'Pad/iOS',");
sql.AppendLine("sum(case Device when 'PC/Windows' then UsageCount else 0 end)'PC/Windows',");
sql.AppendLine("sum(case Device when 'Phone/Android' then UsageCount else 0 end)'Phone/Android',");
sql.AppendLine("sum(case Device when 'Phone/iOS' then UsageCount else 0 end)'Phone/iOS'");
sql.AppendLine(",SUM(UsageCount) as UsageCount from (select * from [UsageLogDaily] where 1=1");
if (!string.IsNullOrEmpty(UntitName))
{
sql.Append(string.Format(" and unitName='{0}'", UntitName));
}
if (start != null && end == null)
{
sql.AppendLine(" and OpTime='" + start + "'");
}
if (end != null && start == null)
{
sql.AppendLine(" and OpTime='" + end + "'");
}
if (start != null && end != null)
{
if (start <= end)
{
sql.Append(string.Format(" and OpTime between'" + start + "' and '" + end + "'"));
}
}
sql.AppendLine(")a group by UnitName");
DbSqlHelper.connectionString = @"Data Source=192.168.200.141;Uid=sa;Pwd=App1234;Initial Catalog=SmartBoxApp";
var ds = DbSqlHelper.Query(sql.ToString());
foreach (DataRow row in ds.Tables[0].Rows)
{
Statist entity = new Statist();
if (!row.IsNull("UsageCount"))
{
entity.UsageCount = Convert.ToInt32(row["UsageCount"].ToString());
}
if (!row.IsNull("UserCount"))
{
entity.UserCount = Convert.ToInt32(row["UserCount"].ToString());
}
if (!row.IsNull("UnitName"))
{
entity.UnitName = row["UnitName"].ToString();
}
if (!row.IsNull("PAd/Android"))
{
entity.Pad_Android = Convert.ToInt32(row["PAd/Android"].ToString());
}
if (!row.IsNull("Pad/iOS"))
{
entity.Pad_iOS = Convert.ToInt32(row["Pad/iOS"].ToString());
}
if (!row.IsNull("PC/Windows"))
{
entity.PC_Windows = Convert.ToInt32(row["PC/Windows"].ToString());
}
if (!row.IsNull("Phone/Android"))
{
entity.Phone_Android = Convert.ToInt32(row["Phone/Android"].ToString());
}
if (!row.IsNull("Phone/iOS"))
{
entity.Phone_iOS = Convert.ToInt32(row["Phone/iOS"].ToString());
}
list.Add(entity);
}
return(list);
}
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 List <Statist> GetAppNameList(string UserName, Nullable <DateTime> start, Nullable <DateTime> end)
{
List <Statist> list = new List <Statist>();
StringBuilder sql = new StringBuilder();
sql.Append("select UserName,UnitName,");
sql.AppendLine("sum(case AppName when 'mail' then UsageCount else 0 end)'mail',");
sql.AppendLine("sum(case AppName when 'pt.calendar' then UsageCount else 0 end)'calendar',");
sql.AppendLine("sum(case AppName when 'magazine' then UsageCount else 0 end)'magazine',");
sql.AppendLine("sum(case AppName when 'contact' then UsageCount else 0 end)'contact',");
sql.AppendLine("sum(case AppName when 'callboard' then UsageCount else 0 end)'callboard',");
sql.AppendLine("sum(case AppName when 'sms' then UsageCount else 0 end)'sms',");
sql.AppendLine("sum(case AppName when 'pt.keywork' then UsageCount else 0 end)'keywork',");
sql.AppendLine("sum(case AppName when 'pt.dowork' then UsageCount else 0 end)'dowork',");
sql.AppendLine("sum(case AppName when 'pt.yqzg' then UsageCount else 0 end)'yqzg',");
sql.AppendLine("sum(case AppName when 'pt.instruction' then UsageCount else 0 end)'instruction',");
sql.AppendLine("sum(case AppName when 'pt.dbd' then UsageCount else 0 end)'dbd',");
sql.AppendLine("sum(case AppName when 'pt.fourdo' then UsageCount else 0 end)'fourdo'");
sql.AppendLine(" from (select * from [UsageLogDaily] where 1=1");
if (!string.IsNullOrEmpty(UserName))
{
sql.Append(string.Format(" and UserName='******'", UserName));
}
if (start != null && end == null)
{
sql.AppendLine(" and OpTime='" + start + "'");
}
if (end != null && start == null)
{
sql.AppendLine(" and OpTime='" + end + "'");
}
if (start != null && end != null)
{
if (start <= end)
{
sql.Append(string.Format(" and OpTime between'" + start + "' and '" + end + "'"));
}
}
sql.AppendLine(")a group by UserName,UnitName");
DbSqlHelper.connectionString = @"Data Source=192.168.2.16\sql2008r2,2244;Initial Catalog=SmartBoxApp;User ID=SmartBox;Password=App1234";
var ds = DbSqlHelper.Query(sql.ToString());
foreach (DataRow row in ds.Tables[0].Rows)
{
Statist entity = new Statist();
if (!row.IsNull("UserName"))
{
entity.UserName = row["UserName"].ToString();
}
if (!row.IsNull("UnitName"))
{
entity.UnitName = row["UnitName"].ToString();
}
if (!row.IsNull("mail"))
{
entity.mail = row["mail"].ToString();
}
if (!row.IsNull("calendar"))
{
entity.calendar = row["calendar"].ToString();
}
if (!row.IsNull("magazine"))
{
entity.magazine = row["magazine"].ToString();
}
if (!row.IsNull("contact"))
{
entity.contact = row["contact"].ToString();
}
if (!row.IsNull("callboard"))
{
entity.callboard = row["callboard"].ToString();
}
if (!row.IsNull("sms"))
{
entity.sms = row["sms"].ToString();
}
if (!row.IsNull("keywork"))
{
entity.keywork = row["keywork"].ToString();
}
if (!row.IsNull("dowork"))
{
entity.dowork = row["dowork"].ToString();
}
if (!row.IsNull("yqzg"))
{
entity.yqzg = row["yqzg"].ToString();
}
if (!row.IsNull("instruction"))
{
entity.instruction = row["instruction"].ToString();
}
if (!row.IsNull("dbd"))
{
entity.dbd = row["dbd"].ToString();
}
if (!row.IsNull("fourdo"))
{
entity.fourdo = row["fourdo"].ToString();
}
list.Add(entity);
}
return(list);
}
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);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="UntitName">用户名称</param>
/// <param name="OpTime">访问时间</param>
/// <returns></returns>
public List <Statist> GetUserList(string UserName, Nullable <DateTime> start, Nullable <DateTime> end)
{
List <Statist> list = new List <Statist>();
StringBuilder sql = new StringBuilder();
sql.Append("select UserName,UnitName,");
sql.AppendLine("sum(case Device when 'PAD/ANDROID' then UsageCount else 0 end)'PAD/ANDROID',");
sql.AppendLine("sum(case Device when 'PAD/IOS' then UsageCount else 0 end)'PAD/IOS',");
sql.AppendLine("sum(case Device when 'PHONE/ANDROID' then UsageCount else 0 end)'PHONE/ANDROID',");
sql.AppendLine("sum(case Device when 'PHONE/IOS' then UsageCount else 0 end)'PHONE/IOS'");
sql.AppendLine(" from (select * from [UsageLogDaily] where 1=1");
if (!string.IsNullOrEmpty(UserName))
{
sql.Append(string.Format(" and UserName='******'", UserName));
}
if (start != null && end == null)
{
sql.AppendLine(" and OpTime='" + start + "'");
}
if (end != null && start == null)
{
sql.AppendLine(" and OpTime='" + end + "'");
}
if (start != null && end != null)
{
if (start <= end)
{
sql.Append(string.Format(" and OpTime between'" + start + "' and '" + end + "'"));
}
}
sql.AppendLine(")a group by UserName,UnitName");
DbSqlHelper.connectionString = @"Data Source=192.168.2.16\sql2008r2,2244;Initial Catalog=SmartBoxApp;User ID=SmartBox;Password=App1234";
var ds = DbSqlHelper.Query(sql.ToString());
foreach (DataRow row in ds.Tables[0].Rows)
{
Statist entity = new Statist();
if (!row.IsNull("UserName"))
{
entity.UserName = row["UserName"].ToString();
}
if (!row.IsNull("UnitName"))
{
entity.UnitName = row["UnitName"].ToString();
}
if (!row.IsNull("PAD/ANDROID"))
{
entity.Pad_Android = Convert.ToInt32(row["PAD/ANDROID"].ToString());
}
if (!row.IsNull("PAD/IOS"))
{
entity.Pad_iOS = Convert.ToInt32(row["PAD/IOS"].ToString());
}
if (!row.IsNull("PHONE/ANDROID"))
{
entity.Phone_Android = Convert.ToInt32(row["PHONE/ANDROID"].ToString());
}
if (!row.IsNull("PHONE/IOS"))
{
entity.Phone_iOS = Convert.ToInt32(row["PHONE/IOS"].ToString());
}
list.Add(entity);
}
return(list);
}
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);
}
