public JsonResult QueryTime(FormCollection form)
{
JsonFlexiGridData data = null;
SearchStatisticOnlineTime view = new SearchStatisticOnlineTime(form);
data = SBO.QueryTime(view);
return(Json(data));
}
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 QueryTime(SearchStatisticOnlineTime view)
{
string column = @"UserUid,User_Full_Name,ORG_NAME,PadAndroid,PadiOS,PhoneAndroid,PhoneiOS";
StringBuilder sunSql = new StringBuilder();
string startSunSql = string.Empty;
string startTimeSql = string.Empty;
string endSql = string.Empty;
if (!string.IsNullOrEmpty(view.StartTime))
{
startSunSql = string.Format(" when datediff(day,logintime,'{0}')>0 then CONVERT(numeric(8,2),DateDiff(ss,'{0}',LogoutTime)/(60.00*60))", view.StartTime);
startTimeSql = string.Format("(case when datediff(day,LoginTime,'{0}')>0 then '{0}' else LoginTime end)", view.StartTime);
}
else
{
startTimeSql = "LoginTime";
}
if (!string.IsNullOrEmpty(view.EndTime))
{
sunSql.AppendFormat(@"case when LogoutTime is null then CONVERT(numeric(8,2),DATEDIFF(ss,LoginTime,GETDATE())/(60.00*60))
when DateDiff(day,LogoutTime,'{0}')<0 then CONVERT(numeric(8,2),DATEDIFF(ss,LoginTime,'{0}'+' 23:59:59.999')/(60.00*60)) " , view.EndTime);
sunSql.Append(startSunSql);
sunSql.AppendFormat(@" else CONVERT(numeric(8,2),DateDiff(ss,LoginTime,LogoutTime)/(60.00*60)) end ", view.EndTime);
}
else
{
sunSql.AppendFormat("case when LogoutTime is null then CONVERT(numeric(8,2),DateDiff(ss,LoginTime,getdate())/(60.00*60)) else CONVERT(numeric(8,2),DateDiff(ss,{0},LogoutTime)/(60.00*60)) end", startTimeSql);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"select UserUid,User_Full_Name,ORG_NAME,
sum(case Device when 'PAD/ANDROID' then {0} else 0 end)'PadAndroid',
sum(case Device when 'PAD/IOS' then {0} else 0 end)'PadiOS',
sum(case Device when 'PAD/ANDROID' then {0} else 0 end)'PhoneAndroid',
sum(case Device when 'PHONE/IOS' then {0} else 0 end)'PhoneiOS'
from (select info.*,u.USER_FULL_NAME,org.ORG_NAME from [UserLoginInfo] info
inner join V_BUA_USER u on info.UserUid=u.USER_UID
left join bua.dbo.BUA_ORGANIZATION org on u.ORG_ID=org.ORG_ID where 1=1 ", sunSql);
if (!string.IsNullOrEmpty(view.StartTime))
{
sql.AppendFormat(@" and (datediff(day,info.LoginTime,'{0}')<=0 or (info.LogoutTime is not null and datediff(day,info.LogoutTime,'{0}')<=0 and info.LoginTime<='{0}')) ", view.StartTime);
}
if (!string.IsNullOrEmpty(view.EndTime))
{
sql.AppendFormat(" and datediff(day,info.LoginTime,'{0}')>=0 ", view.EndTime);
}
sql.AppendLine(")a group by UserUid,User_Full_Name,ORG_NAME");
string with = "with t as (" + sql.ToString() + ") ";
string maindbName = DbSqlHelper.GetMainDBName();
with = with.Replace("[UserLoginInfo]", " " + maindbName + ".dbo.[UserLoginInfo]").Replace("V_BUA_USER", maindbName + ".dbo.V_BUA_USER");
string tableName = "t";
string orderBy = view.OrderBy.ToString().ToLower().Replace(" order by ", "");
string where = "";
SmartBox.Console.Common.SelectPagnationEx rs = base.SelectPaginationEx(tableName, column, view.PageIndex + 1, view.PageSize, orderBy, where, with);
JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(rs.Result.Tables[0], view, "UserUid");
result.page = rs.PageCount;
result.total = rs.RecordCount;
return(result);
//return base.QueryDataForFlexGridByPager(column, string.Format("({0}) as temp", sql.ToString()), view.OrderBy.ToString(), "UserUid", string.Empty, view);
}
public JsonFlexiGridData QueryTime2(SearchStatisticOnlineTime view)
{
string maindbName = DbSqlHelper.GetMainDBName();
string buadb = ConfigurationManager.AppSettings["bua_db_name"];
string statisticsDbName = DbSqlHelper.GetStatisticDBName();
string sql = @"with t as(
select useruid,device,logintime,isnull(logouttime,getdate()) logouttime,datediff(mi,logintime,isnull(logouttime,getdate())) times from " + maindbName + @".dbo.UserLoginInfo where logouttime is not null
";
if (!string.IsNullOrEmpty(view.StartTime))
{
sql += " where logintime >= '" + view.StartTime + " 0:00:00'";
}
if (!string.IsNullOrEmpty(view.EndTime))
{
sql += " and logintime <= '" + view.EndTime + " 23:59:59'";//logouttime
}
sql +=
@"),pc as(
select * from t where device='PC/WINDOWS' or device='pc/windows8'
),apad as (
select * from t where device='Pad/Android' or device='ANDROID_PAD'
),aphone as (
select * from t where device='Phone/Android' or device='ANDROID_PHONE'
),ipad as (
select * from t where device='PAD/IOS'
),iphone as (
select * from t where device='PHONE/IOS'
),u as (
select u_uid user_uid,u_name user_name,(select unit_name from " + statisticsDbName + @"..smc_unit where unit_id=bu.u_unitcode) org_name from " + statisticsDbName + @"..smc_user bu where u_unitcode is not null
),suser as(
select distinct useruid from " + maindbName + @".dbo.UserLoginInfo
)
, siphone as(
select useruid,cast(cast(sum(times) as decimal(18,2)) / 60 as decimal(18,2)) time_iphone from iphone group by useruid
), sipad as(
select useruid,cast(cast(sum(times) as decimal(18,2)) / 60 as decimal(18,2)) time_ipad from ipad group by useruid
), saphone as(
select useruid,cast(cast(sum(cast(times as decimal(15,2))) as decimal(18,2)) / 60 as decimal(18,2)) time_aphone from aphone group by useruid
), sapad as(
select useruid,cast(cast(sum(times) as decimal(18,2)) / 60 as decimal(18,2)) time_apad from apad group by useruid
), spc as(
select useruid,cast(cast(sum(times) as decimal(18,2)) / 60 as decimal(18,2)) time_pc from pc group by useruid
),st as(
select suser.useruid,u.user_name User_Full_Name,u.org_name,
isnull(siphone.time_iphone, 0.00) PhoneiOS,
isnull(sipad.time_ipad, 0.00) PadiOS,
isnull(saphone.time_aphone, 0.00) PhoneAndroid,
isnull(sapad.time_apad, 0.00) PadAndroid
from suser left join siphone on suser.useruid=siphone.useruid
left join sipad on suser.useruid=sipad.useruid
left join saphone on suser.useruid=saphone.useruid
left join sapad on suser.useruid=sapad.useruid
join u on u.user_uid=suser.useruid
)
select * from st order by org_name
";
DataSet ds = base.ExecuteDataset(sql, CommandType.Text);
JsonFlexiGridData result = BaseDao <object> .ConvertJosnFlexGridData(ds.Tables[0], view, "UserUid");
result.page = 1;
result.total = ds.Tables[0].Rows.Count;
return(result);
}
public virtual JsonFlexiGridData QueryTime(SearchStatisticOnlineTime view)
{
dll = new StatisticsDAO("mainDB");
return(dll.QueryTime3(view));
}
