public ITrackerReportResults BuildReport(IReportSpecification specification)
{
try
{
var results = new SqlTrackerResults(specification);
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
if (Configurator.UseBuckets && (specification.OffsetTotalsByHours == TimeSpan.Zero
|
specification.OffsetTotalsByHours.Hours !=
Configurator.DayTotalTZOffset().Hours))
{
command.CommandText = "dbo.GenerateReportUsingBuckets";
}
else
{
command.CommandText = "dbo.GenerateReport";
command.Parameters.Add("@OffsetTotalsTo", SqlDbType.SmallInt);
command.Parameters["@OffsetTotalsTo"].Value = specification.OffsetTotalsByHours.Hours;
}
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@StartDt", SqlDbType.DateTime);
command.Parameters["@StartDt"].Value = specification.FromDateUtc;
command.Parameters.Add("@EndDt", SqlDbType.DateTime);
command.Parameters["@EndDt"].Value = specification.ToDateUtc;
command.Parameters.Add("@Resolution", SqlDbType.SmallInt);
command.Parameters["@Resolution"].Value = (int)specification.Resolution;
SqlParameter flParameter;
flParameter = command.Parameters.AddWithValue("@FilterList", createFilterDataTable(specification));
flParameter.SqlDbType = SqlDbType.Structured;
flParameter.TypeName = "dbo.FilterList";
SqlParameter tnParameter;
tnParameter = command.Parameters.AddWithValue("@TypeNameList", createTypeNameDataTable(specification));
tnParameter.SqlDbType = SqlDbType.Structured;
tnParameter.TypeName = "dbo.TypeName";
var sqlReader = command.ExecuteReader();
string query = command.CommandText;
foreach (SqlParameter p in command.Parameters)
{
query = query.Replace(p.ParameterName, p.Value.ToString());
}
int timeSlotC = sqlReader.GetOrdinal("MeasureTime");
var mesurementTypeList = new Dictionary<string, IMeasurement>();
while (sqlReader.Read())
{
DateTime? measurementDate = null;
if (!sqlReader.IsDBNull(timeSlotC))
{
measurementDate = sqlReader.GetDateTime(timeSlotC);
string typeName = null;
if (sqlReader["TypeName"] != DBNull.Value)
typeName = (string) sqlReader["TypeName"];
string measurementName = null;
if (sqlReader["MeasurementName"] != DBNull.Value)
measurementName = (string) sqlReader["MeasurementName"];
IMeasurement measurement = null;
string typeFullyName = null;
if (typeName != null && measurementName != null)
{
typeFullyName = string.Concat(typeName, ".", measurementName);
mesurementTypeList.TryGetValue(typeFullyName, out measurement);
}
if (measurement == null)
{
if (typeFullyName != null)
{
measurement = specification.Counters.FirstOrDefault(
x => x.FullyQualifiedPropertyName == typeFullyName) ??
new SqlMeasurement()
{
TrackerTypeName = typeName,
Description = typeName,
DisplayName = typeName,
PropertyName = measurementName
};
mesurementTypeList.Add(typeFullyName, measurement);
}
else
{
measurement = new SqlMeasurement()
{
TrackerTypeName = typeName,
Description = typeName,
DisplayName = typeName,
PropertyName = measurementName
};
}
}
results.AddAggregationResult(measurementDate, typeName,
(sqlReader.HasColumn("Filter") && sqlReader["Filter"] != DBNull.Value)
? (string) sqlReader["Filter"]
: null,
measurement, (long) sqlReader["MeasurementValue"]);
}
}
}
}
return results;
}
catch (Exception ex)
{
_logger.Error(ex.Message, ex);
throw;
}
}
public ITrackerReportResults BuildReport(IReportSpecification specification)
{
try
{
var results = new SqlTrackerResults(specification);
var retryStrategy = new Incremental(_maxRetries, TimeSpan.FromMilliseconds(_initialRetry), TimeSpan.FromSeconds(_incrementalRetry));
var retryPolicy = new RetryPolicy <SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);
using (var connection = new SqlConnection(_connectionString))
{
connection.OpenWithRetry(retryPolicy);
using (var command = connection.CreateCommand())
{
if (Configurator.Configuration.UseBuckets && (specification.OffsetTotalsByHours == TimeSpan.Zero |
specification.OffsetTotalsByHours.Hours != Configurator.Configuration.DayTotalTZOffset.Hours))
{
command.CommandText = "dbo.GenerateReportUsingBuckets";
}
else
{
command.CommandText = "dbo.GenerateReport";
command.Parameters.Add("@OffsetTotalsTo", SqlDbType.SmallInt);
command.Parameters["@OffsetTotalsTo"].Value = specification.OffsetTotalsByHours.Hours;
}
command.CommandTimeout = _commandTimeout;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@StartDt", SqlDbType.DateTime);
command.Parameters["@StartDt"].Value = specification.FromDateUtc;
command.Parameters.Add("@EndDt", SqlDbType.DateTime);
command.Parameters["@EndDt"].Value = specification.ToDateUtc;
command.Parameters.Add("@Resolution", SqlDbType.SmallInt);
command.Parameters["@Resolution"].Value = (int)specification.Resolution;
SqlParameter flParameter;
flParameter = command.Parameters.AddWithValue("@FilterList", createFilterDataTable(specification));
flParameter.SqlDbType = SqlDbType.Structured;
flParameter.TypeName = "dbo.FilterList";
SqlParameter tnParameter;
tnParameter = command.Parameters.AddWithValue("@TypeNameList", createTypeNameDataTable(specification));
tnParameter.SqlDbType = SqlDbType.Structured;
tnParameter.TypeName = "dbo.TypeName";
SqlParameter xFlParameter;
xFlParameter = command.Parameters.AddWithValue("@ExcludeFilterList", createFilterDataTable(specification, true));
xFlParameter.SqlDbType = SqlDbType.Structured;
xFlParameter.TypeName = "dbo.FilterList";
var sqlReader = command.ExecuteReaderWithRetry(retryPolicy);
string query = command.CommandText;
foreach (SqlParameter p in command.Parameters)
{
query = query.Replace(p.ParameterName, p.Value.ToString());
}
int timeSlotC = sqlReader.GetOrdinal("MeasureTime");
var mesurementTypeList = new Dictionary <string, IMeasurement>();
while (sqlReader.Read())
{
DateTime?measurementDate = null;
if (!sqlReader.IsDBNull(timeSlotC))
{
measurementDate = new DateTimeOffset(sqlReader.GetDateTime(timeSlotC),
specification.OffsetTotalsByHours.Negate()).UtcDateTime;
string typeName = null;
if (sqlReader["TypeName"] != DBNull.Value)
{
typeName = (string)sqlReader["TypeName"];
}
string measurementName = null;
if (sqlReader["MeasurementName"] != DBNull.Value)
{
measurementName = (string)sqlReader["MeasurementName"];
}
IMeasurement measurement = null;
string typeFullyName = null;
if (typeName != null && measurementName != null)
{
typeFullyName = string.Concat(typeName, ".", measurementName);
mesurementTypeList.TryGetValue(typeFullyName, out measurement);
}
if (measurement == null)
{
if (typeFullyName != null)
{
measurement = specification.Counters.FirstOrDefault(
x => x.FullyQualifiedPropertyName == typeFullyName) ??
new SqlMeasurement()
{
TrackerTypeName = typeName,
Description = typeName,
DisplayName = typeName,
PropertyName = measurementName
};
mesurementTypeList.Add(typeFullyName, measurement);
}
else
{
measurement = new SqlMeasurement()
{
TrackerTypeName = typeName,
Description = typeName,
DisplayName = typeName,
PropertyName = measurementName
};
}
}
results.AddAggregationResult(measurementDate, typeName,
(sqlReader.HasColumn("Filter") && sqlReader["Filter"] != DBNull.Value)
? (string)sqlReader["Filter"]
: null,
measurement, (long)sqlReader["MeasurementValue"]);
}
}
}
}
return(results);
}
catch (Exception ex)
{
_logger.Error(ex.Message, ex);
throw;
}
}
