internal string Execute(DateTime changeDate, string flowName, string targetXmlPath,
ISerializationHelper serializationHelper, ICompressionHelper compressionHelper,
IObjectsFromDatabase objectsFromDatabase,
IAppendAuditLogEvent appendAuditLogEvent)
{
appendAuditLogEvent.AppendAuditLogEvent("Querying TANKS data ...");
string whereQuery =
string.Format("FAC_SITE_IDEN IN (SELECT ST_FAC_IND FROM CHANGED_FACILITIES WHERE IS_DELETED = 0 AND UPPER(FLOW_TYPE) = UPPER('{0}') AND UPDATE_DATE >= '{1}')",
flowName, changeDate.ToString("dd-MMM-yyyy").ToUpper());
Dictionary <string, DbAppendSelectWhereClause> selectClauses = new Dictionary <string, DbAppendSelectWhereClause>();
selectClauses.Add("TANKS_FAC_SITE", new DbAppendSelectWhereClause(whereQuery, null));
List <TanksSubmissionDataType> dataList =
objectsFromDatabase.LoadFromDatabase <TanksSubmissionDataType>(_baseDao, selectClauses);
TanksSubmissionDataType data;
if (CollectionUtils.IsNullOrEmpty(dataList))
{
appendAuditLogEvent.AppendAuditLogEvent("Did not find any TANKS data in database.");
return(null);
}
else if (dataList.Count > 1)
{
throw new InvalidOperationException("More than one set of TANKS data was found in database.");
}
else
{
data = dataList[0];
if (CollectionUtils.IsNullOrEmpty(data.TanksFacilitySite))
{
appendAuditLogEvent.AppendAuditLogEvent("Did not find any tanks facilities.");
return(null);
}
else
{
appendAuditLogEvent.AppendAuditLogEvent("Found {0} facilities and {1} unique tanks.",
data.TanksFacilitySite.Length,
GetUniqueTankCount(data));
}
}
appendAuditLogEvent.AppendAuditLogEvent(
"Serializing transformed results to file (File = {0}).",
targetXmlPath);
serializationHelper.Serialize(data, targetXmlPath);
return(compressionHelper.CompressFile(targetXmlPath));
}
public static string GenerateAndValidateWqxQueryFile(IAppendAuditLogEvent appendAuditLogEvent, IObjectsFromDatabase objectsFromDatabase, SpringBaseDao baseDao,
string queryOrganizationName, string queryOrganizationIdentifier, string sysTempFolderPath,
Assembly xmlSchemaZippedResourceAssembly, string xmlSchemaZippedQualifiedResourceName,
string xmlSchemaRootFileName, ISerializationHelper serializationHelper, ICompressionHelper compressionHelper,
out string validationErrorsFile)
{
validationErrorsFile = null;
WQXDataType wqx = GenerateWqxQueryFromDatabase(appendAuditLogEvent, objectsFromDatabase, baseDao, queryOrganizationIdentifier);
if (wqx == null)
{
return(null);
}
appendAuditLogEvent.AppendAuditLogEvent("Generating WQX xml file from query results ...");
string tempFolderPath = Path.Combine(sysTempFolderPath, Guid.NewGuid().ToString());
string fileName = Guid.NewGuid().ToString();
string tempXmlFilePath = Path.Combine(tempFolderPath, WQX_FILE_PREFIX + fileName + ".xml");
string zipXmlFilePath = Path.ChangeExtension(Path.Combine(sysTempFolderPath, fileName), ".zip");
Directory.CreateDirectory(tempFolderPath);
try
{
serializationHelper.Serialize(wqx, tempXmlFilePath);
appendAuditLogEvent.AppendAuditLogEvent("Inserting header into WQX xml file");
tempXmlFilePath = MakeHeaderFile(tempXmlFilePath, tempFolderPath, queryOrganizationName, queryOrganizationIdentifier, serializationHelper);
appendAuditLogEvent.AppendAuditLogEvent("Inserted header into WQX xml file");
appendAuditLogEvent.AppendAuditLogEvent("Generated WQX xml file from query results");
validationErrorsFile =
BaseWNOSPlugin.ValidateXmlFile(tempXmlFilePath, xmlSchemaZippedResourceAssembly, xmlSchemaZippedQualifiedResourceName,
xmlSchemaRootFileName, sysTempFolderPath, appendAuditLogEvent, compressionHelper);
if (validationErrorsFile != null)
{
compressionHelper.CompressFile(tempXmlFilePath, zipXmlFilePath);
return(zipXmlFilePath);
}
try
{
appendAuditLogEvent.AppendAuditLogEvent("Writing attachment files to temp folder ...");
WriteAttachmentFilesToFolder(baseDao, wqx, tempFolderPath);
appendAuditLogEvent.AppendAuditLogEvent("Wrote attachment files to temp folder.");
appendAuditLogEvent.AppendAuditLogEvent("Compressing WQX xml data file and attachments ...");
compressionHelper.CompressDirectory(zipXmlFilePath, tempFolderPath);
appendAuditLogEvent.AppendAuditLogEvent("Compressed WQX xml data file and attachments.");
}
catch (Exception ex)
{
FileUtils.SafeDeleteFile(zipXmlFilePath);
throw ex;
}
finally
{
FileUtils.SafeDeleteDirectory(tempFolderPath);
}
return(zipXmlFilePath);
}
catch (Exception)
{
FileUtils.SafeDeleteFile(tempXmlFilePath);
throw;
}
}
public static WQXDataType GenerateWqxQueryFromDatabase(IAppendAuditLogEvent appendAuditLogEvent, IObjectsFromDatabase objectsFromDatabase, SpringBaseDao baseDao,
string organizationIdentifier)
{
ExceptionUtils.ThrowIfNull(objectsFromDatabase);
ExceptionUtils.ThrowIfNull(baseDao);
ExceptionUtils.ThrowIfEmptyString(organizationIdentifier);
appendAuditLogEvent.AppendAuditLogEvent("Querying database for WQX results ...");
Dictionary <string, DbAppendSelectWhereClause> selectClauses = new Dictionary <string, DbAppendSelectWhereClause>();
selectClauses.Add("WQX_ORGANIZATION",
new DbAppendSelectWhereClause(baseDao, "ORGID = ?", organizationIdentifier));
List <WQXDataType> dataList = objectsFromDatabase.LoadFromDatabase <WQXDataType>(baseDao, selectClauses);
if (CollectionUtils.IsNullOrEmpty(dataList) || dataList.Count == 0)
{
return(null);
}
else if (dataList.Count > 1)
{
throw new ArgumentException(string.Format("More than one set of data was found for WQX organization '{0}'", organizationIdentifier));
}
else
{
WQXDataType data = dataList[0];
appendAuditLogEvent.AppendAuditLogEvent("Found {0} Projects, {1} Monitoring Locations, {2} Biological Habitat Indexes, {3} Activities, {4} Activity Groups, and {5} Results that matched the query",
CollectionUtils.Count(data.Organization.Project).ToString(),
CollectionUtils.Count(data.Organization.MonitoringLocation).ToString(),
CollectionUtils.Count(data.Organization.BiologicalHabitatIndex).ToString(),
CollectionUtils.Count(data.Organization.Activity).ToString(),
CollectionUtils.Count(data.Organization.ActivityGroup).ToString(),
TotalResultCount(data).ToString());
return(data);
}
}
public static string GenerateAndValidateWqxQueryFile(IAppendAuditLogEvent appendAuditLogEvent, IObjectsFromDatabase objectsFromDatabase, SpringBaseDao baseDao,
string queryOrganizationName, string queryOrganizationIdentifier, string naasUserName, string sysTempFolderPath,
Assembly xmlSchemaZippedResourceAssembly, string xmlSchemaZippedQualifiedResourceName,
string xmlSchemaRootFileName, ISerializationHelper serializationHelper, ICompressionHelper compressionHelper,
out string validationErrorsFile)
{
// Jaime had an additional "string naasUserName" parameter to this method, but he did not check in code, so not sure what it does.
return(GenerateAndValidateWqxQueryFile(appendAuditLogEvent, objectsFromDatabase, baseDao, queryOrganizationName, queryOrganizationIdentifier, sysTempFolderPath,
xmlSchemaZippedResourceAssembly, xmlSchemaZippedQualifiedResourceName, xmlSchemaRootFileName, serializationHelper,
compressionHelper, out validationErrorsFile));
}
internal string Execute(DateTime changeDate, string flowName, string targetXmlPath,
ISerializationHelper serializationHelper, ICompressionHelper compressionHelper,
IObjectsFromDatabase objectsFromDatabase,
IAppendAuditLogEvent appendAuditLogEvent)
{
appendAuditLogEvent.AppendAuditLogEvent("Querying FACID data ...");
Dictionary <string, DbAppendSelectWhereClause> selectClauses = new Dictionary <string, DbAppendSelectWhereClause>();
string facIdQuery = string.Format("SELECT ST_FAC_IND FROM CHANGED_FACILITIES WHERE IS_DELETED = 0 AND UPPER(FLOW_TYPE) = UPPER('{0}') AND UPDATE_DATE >= '{1}'",
flowName, changeDate.ToString("dd-MMM-yyyy").ToUpper());
string whereQuery = string.Format("FAC_SITE_IDEN_VAL IN ({0})", facIdQuery);
selectClauses.Add("FACID_FAC", new DbAppendSelectWhereClause(whereQuery, null));
string affiliateTableClause = string.Format(
"AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_FAC_FAC_AFFL WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0}) UNION " +
"SELECT AFFL_IDEN FROM FACID_ENVR_INTR_FAC_AFFL WHERE ENVR_INTR_ID IN (SELECT ENVR_INTR_ID FROM FACID_ENVR_INTR WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0})))",
whereQuery);
//string affiliateTableClause = string.Format("(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_FAC_FAC_AFFL WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0}))) OR " +
// "(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_ENVR_INTR_FAC_AFFL WHERE ENVR_INTR_ID IN (SELECT ENVR_INTR_ID FROM FACID_ENVR_INTR WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0}))))",
// whereQuery);
//string affiliateTableClause = string.Format("(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_FAC_FAC_AFFL WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0})))",
// whereQuery);
//string affiliateTableClause = string.Format("(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_ENVR_INTR_FAC_AFFL WHERE ENVR_INTR_ID IN (SELECT ENVR_INTR_ID FROM FACID_ENVR_INTR WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0}))))",
// whereQuery);
//string affiliateTableClause = string.Format("(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_FAC_FAC_AFFL WHERE FAC_ID IN ({0}))) OR " +
// "(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_ENVR_INTR_FAC_AFFL WHERE ENVR_INTR_ID IN (SELECT ENVR_INTR_ID FROM FACID_ENVR_INTR WHERE FAC_ID IN ({0}))))",
// facIdQuery);
selectClauses.Add("FACID_AFFL", new DbAppendSelectWhereClause(affiliateTableClause, null));
List <FacilityDetailsDataType> dataList =
objectsFromDatabase.LoadFromDatabase <FacilityDetailsDataType>(_baseDao, selectClauses);
FacilityDetailsDataType data = FacIdHelper.CombineFacilityDetailsQueryResults(dataList);
#if DEBUG
{
}
#endif // DEBUG
if (CollectionUtils.IsNullOrEmpty(data.FacilityList))
{
appendAuditLogEvent.AppendAuditLogEvent("Did not find any FACID data in database.");
return(null);
}
else
{
appendAuditLogEvent.AppendAuditLogEvent("Found {0} facilities and {1} affiliates.",
data.FacilityList.Length,
(data.AffiliateList != null) ? data.AffiliateList.Length : 0);
}
appendAuditLogEvent.AppendAuditLogEvent(
"Serializing transformed results to file (File = {0}).",
targetXmlPath);
serializationHelper.Serialize(data, targetXmlPath);
return(compressionHelper.CompressFile(targetXmlPath));
}
