public SystemSummaryStatisticsData GetSummaryInfoByUser(Guid?companyId, Guid userId)
{
SystemSummaryStatisticsData result = null;
try
{
var data = _entitiesContext.Database.SqlQuery <SystemSummaryStatisticsData>(string.Format(@"exec GetSummaryInfoByUser '{0}','{1}'", companyId, userId));
if (data != null)
{
result = data.FirstOrDefault();
}
}
catch (Exception)
{
throw;
}
return(result);
}
public SystemSummaryStatisticsData GetVtSystemStatusCountByLicense(Guid?companyId, string licenseNo)
{
SystemSummaryStatisticsData result = new SystemSummaryStatisticsData();
var sqlWhere = string.Empty;
if (companyId.HasValue)
{
sqlWhere += string.Format(" and s.CompanyId='{0}'", companyId.ToString());
}
if (!string.IsNullOrWhiteSpace(licenseNo))
{
sqlWhere += string.Format(" and s.[LIC_NO]='{0}' ", licenseNo);
}
var sql = string.Format(@"declare @OfflineCount int;
declare @NormalCount int;
declare @ProtectionCount int;
declare @FaultCount int;
select @OfflineCount=count(SYSTEM_ID) from VT_SYSTEM as s with(nolock)
where DELETE_FLAG=0 and (LastUploadTime < Dateadd(minute, -30,GETDATE()) or LastUploadTime is null) {0}
select @NormalCount=count(SYSTEM_ID) from VT_SYSTEM as s with(nolock)
where DELETE_FLAG=0 and LastUploadTime >= Dateadd(minute, -30,GETDATE()) and EmsStatus='Normal' {0}
select @ProtectionCount=count(SYSTEM_ID) from VT_SYSTEM as s with(nolock)
where DELETE_FLAG=0 and LastUploadTime >= Dateadd(minute, -30,GETDATE()) and (EmsStatus='Warning' or EmsStatus='Protection') {0}
select @FaultCount=count(SYSTEM_ID) from VT_SYSTEM as s with(nolock)
where DELETE_FLAG=0 and LastUploadTime >= Dateadd(minute, -30,GETDATE()) and EmsStatus='Fault' {0}
select @OfflineCount as OfflineCount,@NormalCount as NormalCount,@ProtectionCount as ProtectionCount,@FaultCount as FaultCount", sqlWhere);
var q = _entitiesContext.Database.SqlQuery <SystemSummaryStatisticsData>(sql);
if (q.Any())
{
result = q.First();
}
return(result);
}