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