private static string GetSessionConfiguration(string sessionId)
{
string sql = String.Format(Resources.SessionConfigurationSQL, sessionId);
DataTable table = new DataTable();
using (SqlAdapter adapter = new SqlAdapter(ReportingSqlConnection.ConnectionString))
{
using (SqlDataReader reader = adapter.ExecuteReader(sql))
{
if (reader.HasRows)
{
while (reader.Read())
{
try
{
return(reader.GetString(0));
}
catch (SqlNullValueException)
{
return(null);
}
}
}
}
}
return(null);
}
/// <summary>
/// Returns a <see cref="DbDataReader"/> using the specified SQLAdapter and SQL text.
/// </summary>
/// <param name="adapter">The SQL adapter</param>
/// <param name="sqlText">The SQL text to execute</param>
/// <returns>A <see cref="DbDataReader"/></returns>
private DbDataReader GetDataReader(SqlAdapter adapter, string sqlText)
{
DbDataReader reader = null;
Retry.WhileThrowing(() =>
{
reader = adapter.ExecuteReader(sqlText);
}, 3, TimeSpan.FromSeconds(5), _retryExceptions);
return(reader);
}
private void AdminControlPanel_Load(object sender, EventArgs e)
{
try
{
if (!ConnectToEnvironment())
{
Application.Exit();
}
}
catch (Exception ex)
{
// Inform the user of the error and allow them to send but do not kill the app unless they choose
// For example, they may instead want to change environment.
ApplicationExceptionHandler.UnhandledException(ex);
}
decimal version = 0;
using (SqlAdapter adapter = new SqlAdapter(EnterpriseTestSqlConnection.ConnectionString))
{
try
{
string sqlText = "SELECT value FROM fn_listextendedproperty(N'STF Revision', NULL, NULL, NULL, NULL, NULL, NULL)";
var reader = adapter.ExecuteReader(sqlText);
if (reader != null && reader.Read())
{
version = decimal.Parse(reader["value"].ToString());
}
}
catch (Exception)
{
}
}
if (version != 0)
{
versionLabel.Text = "STB v{0:#.##}".FormatWith(version);
}
else
{
versionLabel.Text = string.Empty;
}
}
/// <summary>
/// Gets the most recent session ID from the database.
/// </summary>
/// <returns></returns>
public string GetMostRecentSession()
{
using (SqlAdapter adapter = new SqlAdapter(_connectionString))
{
DbDataReader reader = adapter.ExecuteReader(Resource.RecentSessionSql);
while (reader.Read())
{
string sessionId = GetSessionId(reader);
if (!string.IsNullOrEmpty(sessionId))
{
_sessionId = sessionId;
return(_sessionId);
}
}
reader.Close();
}
return(string.Empty);
}
/// <summary>
/// Retrieves the job records from the HPCR database.
/// </summary>
public List <HpcrArchiveRecord> RetrieveRecords()
{
var result = new List <HpcrArchiveRecord>();
TraceFactory.Logger.Debug("Retrieving records from HPCR database that should be logged to STF");
using (SqlAdapter adapter = new SqlAdapter(_connectionString))
{
try
{
// Get records with a DocumentName that either haven't been logged (StfLogId = null)
// or have an updated delivery date within an expiration period
var sql = @"
select * from ArchiveTable
where
DATALENGTH([DocumentName]) > 0
and ([StfLogStatus] is null or [StfLogStatus] != 'Ignore')
and
(
[StfLogId] is null
or (
[StfLogStatus] = 'Partial'
and [prDateDelivered] is not null
and [ArchiveDate] < DATEADD(hh, -6, GETDATE())
)
or(
[StfLogStatus] = 'Partial'
and [prDateDelivered] is null
and ([prDestination] = 'scantome' or prDestination like '%-%-%-%-%')
)
)
";
using (DbDataReader reader = adapter.ExecuteReader(sql))
{
while (reader.Read())
{
Guid stfLogId = GetStfLogId(reader);
Guid stfDigitalSendServerJobid = GetStfDigitalSendServerJobId(reader);
HpcrArchiveRecord hpcrRecord = new HpcrArchiveRecord();
hpcrRecord.RowIdentifier = (int)reader[HpcrDatabaseColumns.RowIdentifier];
hpcrRecord.DateDelivered = GetValueAsDateTime(reader, HpcrDatabaseColumns.prDateDelivered);
hpcrRecord.DateSubmitted = GetValueAsDateTime(reader, HpcrDatabaseColumns.prDateSubmitted);
hpcrRecord.JobType = TranslateJobType(GetValueAsString(reader, HpcrDatabaseColumns.prDestination));
hpcrRecord.DocumentName = GetValueAsString(reader, HpcrDatabaseColumns.DocumentName);
hpcrRecord.FileType = GetValueAsString(reader, HpcrDatabaseColumns.prFinalFormCode);
hpcrRecord.FileSizeBytes = GetValueAsInt(reader, HpcrDatabaseColumns.DocumentBytes);
hpcrRecord.ScannedPages = GetValueAsInt(reader, HpcrDatabaseColumns.DocumentCount);
hpcrRecord.DssVersion = GetValueAsString(reader, HpcrDatabaseColumns.prHPCRServerVersion);
hpcrRecord.FinalStatus = GetValueAsString(reader, HpcrDatabaseColumns.prFinalStatusText);
hpcrRecord.StfLogStatus = GetValueAsString(reader, HpcrDatabaseColumns.StfLogStatus);
hpcrRecord.DeviceModel = GetValueAsString(reader, HpcrDatabaseColumns.DeviceModelName);
hpcrRecord.StfLogId = stfLogId;
hpcrRecord.StfDigitalSendServerJobId = stfDigitalSendServerJobid;
// public distributions and Send to Me are currently inserting two records into the archive table.
// ignore the one that has a Guid for the destination
if (!hpcrRecord.JobType.StartsWith("second"))
{
result.Add(hpcrRecord);
}
else
{
if (ProcessRecordForwarding(result, hpcrRecord))
{
UpdateHpcrDatabase(adapter, hpcrRecord);
}
}
}
reader.Close();
}
}
catch (SqlException sqlEx)
{
TraceFactory.Logger.Error("Error retrieving list of records. Aborting.", sqlEx);
return(result);
}
TraceFactory.Logger.Debug("Found {0} records.".FormatWith(result.Count));
}
return(result);
}
private static List <EnterpriseTestMapNode> LoadNodesFromDatabase(UserCredential user)
{
List <EnterpriseTestMapNode> nodes = new List <EnterpriseTestMapNode>();
bool needFilter = (user != null && !user.HasPrivilege(UserRole.Manager));
string sqlText = needFilter ? Resource.SelectTreeViewData.FormatWith(user.UserName) : Resource.SelectTreeViewDataNoFilter;
using (EnterpriseTestContext context = new EnterpriseTestContext())
{
Collection <ScenarioData> scenarioData = new Collection <ScenarioData>();
using (SqlAdapter adapter = new SqlAdapter(EnterpriseTestSqlConnection.ConnectionString))
{
DbDataReader reader = adapter.ExecuteReader(sqlText);
if (reader != null)
{
while (reader.Read())
{
scenarioData.Add(new ScenarioData()
{
MetadataEnabled = !string.IsNullOrEmpty(reader["MDE"].ToString()) && (bool)reader["MDE"],
MetadataFolderId = !string.IsNullOrEmpty(reader["MDFID"].ToString()) ? (Guid?)reader["MDFID"] : (Guid?)null,
MetadataId = !string.IsNullOrEmpty(reader["MDID"].ToString()) ? (Guid)reader["MDID"] : Guid.Empty,
MetadataName = !string.IsNullOrEmpty(reader["MDN"].ToString()) ? (string)reader["MDN"]: string.Empty,
MetadataType = !string.IsNullOrEmpty(reader["MDT"].ToString()) ? (string)reader["MDT"] : string.Empty,
ResourceEnabled = !string.IsNullOrEmpty(reader["VRE"].ToString()) && (bool)reader["VRE"],
ResourceFolderId = !string.IsNullOrEmpty(reader["VRFID"].ToString()) ? (Guid?)reader["VRFID"] : (Guid?)null,
ResourceId = !string.IsNullOrEmpty(reader["VRID"].ToString()) ? (Guid)reader["VRID"] : Guid.Empty,
ResourceName = !string.IsNullOrEmpty(reader["VRN"].ToString()) ? (string)reader["VRN"] : string.Empty,
ResourceType = !string.IsNullOrEmpty(reader["VRT"].ToString()) ? (string)reader["VRT"] : string.Empty,
ScenarioFolderId = !string.IsNullOrEmpty(reader["ESFID"].ToString()) ? (Guid?)reader["ESFID"] : (Guid?)null,
ScenarioId = (Guid)reader["ESID"],
ScenarioName = (string)reader["ESN"],
});
}
}
}
nodes.AddRange
(scenarioData
.Where(x => x.ScenarioId != (Guid?)null && x.ScenarioId != Guid.Empty)
.GroupBy(x => x.ScenarioId)
.Select(x =>
new EnterpriseTestMapNode(x.First().ScenarioId)
{
NodeType = ConfigurationObjectType.EnterpriseScenario,
Name = x.First().ScenarioName,
FolderId = x.First().ScenarioFolderId
})
);
nodes.AddRange
(scenarioData
.Where(x => x.ResourceId != (Guid?)null && x.ResourceId != Guid.Empty)
.GroupBy(x => x.ResourceId)
.Select(x =>
new EnterpriseTestMapNode(x.First().ResourceId)
{
NodeType = ConfigurationObjectType.VirtualResource,
Name = x.First().ResourceName,
FolderId = x.First().ResourceFolderId,
ParentId = x.First().ScenarioId,
Enabled = x.First().ResourceEnabled,
ResourceType = x.First().ResourceType
})
);
nodes.AddRange
(scenarioData
.Where(x => x.MetadataId != (Guid?)null && x.MetadataId != Guid.Empty)
.GroupBy(x => x.MetadataId)
.Select(x =>
new EnterpriseTestMapNode(x.First().MetadataId)
{
NodeType = ConfigurationObjectType.ResourceMetadata,
Name = x.First().MetadataName,
FolderId = x.First().MetadataFolderId,
ParentId = x.First().ResourceId,
Enabled = x.First().MetadataEnabled,
ResourceType = x.First().ResourceType,
MetadataType = x.First().MetadataType
})
);
// Load the folders based on the test objects that have already been loaded
if (needFilter)
{
LoadFolders(context, nodes);
}
else
{
foreach (ConfigurationTreeFolder folder in ConfigurationTreeFolder.Select(context))
{
nodes.Add(new EnterpriseTestMapNode(folder));
}
}
}
return(nodes);
}
/// <summary>
/// Gets the data for each job ID and logs it
/// </summary>
private void LogData(SqlAdapter adapter, DSSJobLogTable logTable, string sqlText, ref List <Guid> jobIds)
{
TraceFactory.Logger.Debug("Processing {0}.".FormatWith(logTable.ToString()));
DbDataReader jobsReader = null;
Retry.WhileThrowing(() =>
{
jobsReader = adapter.ExecuteReader(sqlText);
}, 3, TimeSpan.FromSeconds(5), _retryExceptions);
try
{
bool continueRead = true;
while (continueRead)
{
try
{
continueRead = jobsReader.Read();
}
catch (SqlException sqlEx)
{
if (sqlEx.Number == 1205)
{
TraceFactory.Logger.Debug("Deadlock encountered. Continue to process.");
}
else
{
TraceFactory.Logger.Error(sqlEx); //Unexpected SQL exception.
}
continue; //Skip to the next record
}
if (continueRead)
{
// Successful read. Log the entry
DigitalSendServerJobLogger log = null;
switch (logTable)
{
case DSSJobLogTable.JobLogDevice:
log = CreateLogFromDevice(jobsReader);
break;
case DSSJobLogTable.JobLogMaster:
log = CreateLogFromMaster(jobsReader);
break;
}
SubmitLog(log);
TraceFactory.Logger.Debug("Found job with ID " + log.DigitalSendJobId.ToString());
//Job successfully logged. Remove it from the list
jobIds.Remove(log.DigitalSendJobId);
// See if we've found a new session ID
if (log.SessionId != _sessionId)
{
_sessionId = log.SessionId;
if (RetrievedSession != null)
{
RetrievedSession(this, new SessionEventArgs(log.SessionId));
}
}
}
}
}
finally
{
if (jobsReader != null)
{
jobsReader.Close();
jobsReader.Dispose();
}
}
}
/// <summary>
/// Retrieves the job records from the DSS database.
/// For the records that are not found in the database, the following may be logged to the data logger:
/// unavailable - The job ID was found in the DSS database, and partial data was retrieved for the job. Represents job data that could not be retrieved.
/// unknown - While attempting to find the job ID in the database, a database error occurred, and after retrying the operation several times, processing moved on.
/// missing - The job was not found in either the Master or the Device table.
/// </summary>
public void RetrieveRecords()
{
TraceFactory.Logger.Debug("Retrieving Job IDs from STFTransactionLog table.");
using (SqlAdapter adapter = new SqlAdapter(_connectionString))
{
List <Guid> jobIds = new List <Guid>(); //List of records that have been updated
try
{
using (DbDataReader idsReader = adapter.ExecuteReader(Resource.TransactionRetrieveSql))
{
while (idsReader.Read())
{
jobIds.Add((Guid)idsReader["JobId"]);
}
idsReader.Close();
}
}
catch (SqlException sqlEx)
{
TraceFactory.Logger.Error("Error retrieving list of Job IDs. Aborting.", sqlEx);
return;
}
TraceFactory.Logger.Debug("Found {0} records.".FormatWith(jobIds.Count));
if (jobIds.Any())
{
// Capture list of job IDs now while we have the entire list, to be used to cleanup the transaction table later.
string jobIdList = "'" + string.Join("','", jobIds) + "'";
string notFoundText = "<missing>";
try
{
//Log data from JobLogMaster.
LogData(adapter, DSSJobLogTable.JobLogMaster, Resource.JobLogMasterSql.FormatWith(jobIdList), ref jobIds);
}
catch (SqlException sqlEx)
{
TraceFactory.Logger.Error("Unable to process JobLogMaster.", sqlEx);
notFoundText = "<unknown>";
}
//Check to see if there were any jobs that weren't processed
if (jobIds.Any())
{
// Recreate list of job IDs, since some of the jobs have already been processed and removed from jobIds
string unprocessedJobIdList = "'" + string.Join("','", jobIds) + "'";
try
{
//Log data from JobLogDevice.
LogData(adapter, DSSJobLogTable.JobLogDevice, Resource.JobLogDeviceSql.FormatWith(unprocessedJobIdList), ref jobIds);
}
catch (SqlException sqlEx)
{
TraceFactory.Logger.Error("Unable to process JobLogDevice.", sqlEx);
notFoundText = "<unknown>";
}
}
// Any job Ids left over at this point were not found in either the master nor the device tables
// Log an entry in the data log to flag data that was not found.
foreach (Guid jobId in jobIds)
{
DigitalSendServerJobLogger log = CreateLogForNotFound(jobId, notFoundText);
SubmitLog(log);
}
//At this point, all jobs Ids have been processed
jobIds.Clear();
//Remove the JobIds from the transaction table.
string sql = Resource.TransactionCleanupSql.FormatWith(jobIdList);
//TraceFactory.Logger.Debug(sql);
Retry.WhileThrowing(() =>
{
adapter.ExecuteNonQuery(sql);
}, 5, TimeSpan.FromSeconds(5), _retryExceptions);
}
}
}
