public static string StoreWqxDataToDatabase(IAppendAuditLogEvent appendAuditLogEvent, WQXDataType data, IObjectsToDatabase objectsToDatabase,
SpringBaseDao baseDao, string attachmentsFolderPath)
{
appendAuditLogEvent.AppendAuditLogEvent("Storing WQX data into the database ...");
string countsString = string.Empty;
baseDao.TransactionTemplate.Execute(delegate(Spring.Transaction.ITransactionStatus status)
{
OrganizationDataType org = data.Organization;
appendAuditLogEvent.AppendAuditLogEvent("Storing WQX data into database for organization \"{0}\" ...", org.OrganizationDescription.OrganizationFormalName);
appendAuditLogEvent.AppendAuditLogEvent(DatabaseHelper.GetWqxOrgCountsString(org));
Dictionary <string, int> insertCounts = objectsToDatabase.SaveToDatabase(data, baseDao);
DatabaseHelper.StoreAttachmentFilesFromFolder(objectsToDatabase, baseDao, data.Organization, attachmentsFolderPath);
countsString += string.Format("Stored WQX data for organization \"{0}\" into the database with the following table row counts:{1}{2}",
org.OrganizationDescription.OrganizationFormalName, Environment.NewLine, CreateTableRowCountsString(insertCounts));
appendAuditLogEvent.AppendAuditLogEvent(countsString);
return(null);
});
return(countsString);
}
public static WQXDataType GenerateWqxObjectsFromSubmissionFile(IAppendAuditLogEvent appendAuditLogEvent, string submissionFilePath,
string sysTempFolderPath, ISerializationHelper serializationHelper,
ICompressionHelper compressionHelper, out string attachmentsFolderPath)
{
string validationErrorsFile;
return(GenerateWqxObjectsFromSubmissionFile(appendAuditLogEvent, submissionFilePath, sysTempFolderPath, null, null, null,
serializationHelper, compressionHelper, out attachmentsFolderPath,
out validationErrorsFile));
}
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 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 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 void UpsertAirQualityData(AirQualitySubmissionType data, IAppendAuditLogEvent appendAuditLogProvider)
{
IList <object> args;
System.Text.StringBuilder argNames;
foreach (FacilitySiteListType site in data.FacilitySiteList)
{
args = new List <object>();
argNames = new System.Text.StringBuilder();
if (site.BasicSiteInformation != null)
{
argNames.Append("ACTION_CD;AQCR_CD;CENSUS_BLOCK_CD;CENSUS_BLOCK_GRP_CD;CENSUS_TRACT_CD;CITY_CD;CLASS_AREA_CD;CONG_DIST_CODE;DIR_FROM_CITY_CD;DIR_TO_MET_SITE_CODE;DIST_FROM_CITY_MSR;DIST_TO_MET_SITE_MSR;HORIZ_COL_MTHD;HORIZ_REF_DATUM;HORIZONTAL_ACCURACY;HQ_EVAL_DATE;LAND_USE_ID;LOC_ADDR_TEXT;LOC_SETTING_ID;LOCAL_ID;LOCAL_NAME;LOCAL_REGION_CD;MET_SITE_ID;MET_SITE_TYPE_CD;POSTAL_CODE;REG_EVAL_DATE;SITE_EST_DATE;SITE_TERM_DATE;SRC_MAP_SCALE_NBR;SUPPORT_AGENCY_CD;TIME_ZONE_NAME;URBAN_AREA_CD;VERTICAL_ACCR_MSR;VERTICAL_DATUM_ID;VERTICAL_MEASURE;VERTICAL_MTHD_CD;");
args.Add(site.BasicSiteInformation.ActionCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.AQCRCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.CensusBlockCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.CensusBlockGroupCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.CensusTractCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.CityCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.ClassIAreaCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.CongressionalDistrictCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.DirectionFromCityCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.DirectionToMetSiteCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.DistanceFromCityMeasure);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.DistanceToMetSiteMeasure);
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.HorizontalCollectionMethodCode);
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.HorizontalReferenceDatumName);
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.HorizontalAccuracyMeasure);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.HQEvaluationDate);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.LandUseIdentifier);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.LocationAddressText);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.LocationSettingIdentifier);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.LocalIdentifier);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.LocalName);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.LocalRegionCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.MetSiteIdentifier);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.MetSiteTypeCode);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.AddressPostalCode.Value);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.RegionalEvaluationDate);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.SiteEstablishedDate);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.SiteTerminatedDate);
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.SourceMapScaleNumber);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.SupportAgencyCode);
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.TimeZoneName);
args.Add(site.BasicSiteInformation.FacilitySiteDetails.UrbanAreaCode);
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.VerticalAccuracyMeasure);
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.VerticalDatumIdentifier);
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.VerticalMeasure);
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.VerticalMethodCode);
for (int i = site.BasicSiteInformation.GeographicMonitoringLocation.Items.Length; i-- > 0;)
{
switch (site.BasicSiteInformation.GeographicMonitoringLocation.ItemsElementName[i])
{
case ItemsChoiceType1.LatitudeMeasure:
argNames.Append(';').Append("LATITUDE");
break;
case ItemsChoiceType1.LongitudeMeasure:
argNames.Append(';').Append("LONGITUDE");
break;
case ItemsChoiceType1.UTMEastingMeasure:
argNames.Append(';').Append("UTM_EASTING");
break;
case ItemsChoiceType1.UTMNorthingMeasure:
argNames.Append(';').Append("UTM_NORTHING");
break;
case ItemsChoiceType1.UTMZoneCode:
argNames.Append(';').Append("UTM_ZONE_CD");
break;
default:
continue; //don't pass unused parameters
}
args.Add(site.BasicSiteInformation.GeographicMonitoringLocation.Items[i]);
}
}
argNames.Append("FACILITY_SITE_ID");
args.Add(site.SiteIdentifierDetails.FacilitySiteIdentifier.Value);
for (int i = site.SiteIdentifierDetails.Items.Length; i-- > 0;)
{
switch (site.SiteIdentifierDetails.ItemsElementName[i])
{
case ItemsChoiceType.TribalCode:
argNames.Append(';').Append("TRIBAL_CD");
break;
case ItemsChoiceType.StateCode:
argNames.Append(';').Append("STATE_CD");
break;
case ItemsChoiceType.NonStateCode:
argNames.Append(';').Append("NON_STATE_CD");
break;
case ItemsChoiceType.CountyCode:
argNames.Append(';').Append("COUNTY_CD");
break;
default:
continue;
}
args.Add(site.SiteIdentifierDetails.Items[i]);
}
int siteID;
using (System.Data.DataTable dt = new System.Data.DataTable())
{
_baseDao.FillTableFromStoredProc(dt, "aqs_FacilitySiteUpsert", argNames.ToString(), args.ToArray());
siteID = (int)dt.Rows[0]["AQS_SITE_ID_PK"];
}
foreach (MonitorListType monitor in site.MonitorList)
{
argNames = new System.Text.StringBuilder("AQS_SITE_ID_FK;SUBST_ID;SUBST_OCCURENCE_CD;");
args = new List <object>();
args.Add(siteID);
args.Add(monitor.MonitorIdentifierDetails.SubstanceIdentifier.Value);
args.Add(monitor.MonitorIdentifierDetails.SubstanceOccurrenceCode);
if (monitor.BasicMonitoringInformation != null)
{
argNames.Append("ACTION_CD;APPLICABLE_NAAQS_IND;CMNTY_MONITOR_ZONE;DOMINANT_SCR_TXT;HORIZ_DIST_MSR;MEASUREMENT_SCALE_ID;MONITOR_CLOSE_DATE;OPEN_PATH_ID;PROBE_HEIGHT_MSR;PROBE_LOC_CODE;PROJECT_CLASS_CD;SAMPLE_RESID_TIME;SCHED_EXEMPT_IND;SPACIAL_AVG_IND;SURROGATE_IND;UNRESTR_AIR_FLOW_IND;VERT_DIST_MSR;WORST_SITE_TYPE_CD");
args.Add(monitor.BasicMonitoringInformation.ActionCode);
args.Add(monitor.BasicMonitoringInformation.ApplicableNAAQSIndicator);
args.Add(monitor.BasicMonitoringInformation.CommunityMonitoringZoneCode);
args.Add(monitor.BasicMonitoringInformation.DominantSourceText);
args.Add(monitor.BasicMonitoringInformation.HorizontalDistanceMeasure);
args.Add(monitor.BasicMonitoringInformation.MeasurementScaleIdentifier);
args.Add(monitor.BasicMonitoringInformation.MonitorCloseDate);
args.Add(monitor.BasicMonitoringInformation.OpenPathIdentifier);
//args.Add(monitor.BasicMonitoringInformation.PollutantAreaCode);
args.Add(monitor.BasicMonitoringInformation.ProbeHeightMeasure);
args.Add(monitor.BasicMonitoringInformation.ProbeLocationCode);
args.Add(monitor.BasicMonitoringInformation.ProjectClassCode);
args.Add(monitor.BasicMonitoringInformation.SampleResidenceTime);
args.Add(monitor.BasicMonitoringInformation.ScheduleExemptionIndicator);
args.Add(monitor.BasicMonitoringInformation.SpatialAverageIndicator);
args.Add(monitor.BasicMonitoringInformation.SurrogateIndicator);
args.Add(monitor.BasicMonitoringInformation.UnrestrictedAirFlowIndicator);
args.Add(monitor.BasicMonitoringInformation.VerticalDistanceMeasure);
args.Add(monitor.BasicMonitoringInformation.WorstSiteTypeCode);
}
int monitorID;
using (System.Data.DataTable dt = new System.Data.DataTable())
{
_baseDao.FillTableFromStoredProc(dt, "aqs_MonitorUpsert", argNames.ToString(), args.ToArray());
monitorID = (int)dt.Rows[0]["AQS_MONITOR_ID_PK"];
}
foreach (RawDataListType datum in monitor.RawDataList)
{
args = new List <object>();
argNames = new System.Text.StringBuilder("ALTERNATE_MDL_VALUE;AQS_MONITOR_ID_FK;MEASURE_UNIT_CD;METHOD_ID_CD");
args.Add(datum.TransactionProtocolDetails.AlternateMDLValue);
args.Add(monitorID);
args.Add(datum.TransactionProtocolDetails.MeasureUnitCode);
args.Add(datum.TransactionProtocolDetails.MethodIdentifierCode);
for (int i = datum.TransactionProtocolDetails.Items.Length; i-- > 0;)
{
switch (datum.TransactionProtocolDetails.ItemsElementName[i])
{
case ItemsChoiceType2.CompositeTypeIdentifier:
argNames.Append(';').Append("COMPOSITE_TYPE_ID");
break;
case ItemsChoiceType2.DurationCode:
argNames.Append(';').Append("DURATION_CD");
break;
case ItemsChoiceType2.FrequencyCode:
argNames.Append(';').Append("FREQUENCY_CD");
break;
default:
continue;
}
args.Add(datum.TransactionProtocolDetails.Items[i]);
}
int protocolID;
using (System.Data.DataTable dt = new System.Data.DataTable())
{
_baseDao.FillTableFromStoredProc(dt, "aqs_TransactionProtocolUpsert", argNames.ToString(), args.ToArray());
protocolID = (int)dt.Rows[0]["AQS_TRANS_PROTOCOL_PK"];
}
foreach (object item in datum.Items)
{
var result = item as RawResultsType;
if (result != null)
{
args = new List <object>();
argNames = new System.Text.StringBuilder("ACTION_CD;AQS_TRANS_PROTOCOL_FK;DATA_APPROVAL_IND;DATA_VALIDITY_CD;SMPL_COLL_START_DATE;SMPL_COLL_START_TIME");
args.Add(result.ActionCode);
args.Add(protocolID);
args.Add(result.RawValueDetails.DataApprovalIndicator);
args.Add(result.RawValueDetails.DataValidityCode);
args.Add(result.SampleCollectionStartDate);
args.Add(result.SampleCollectionStartTime);
for (int i = result.RawValueDetails.Items.Length; i-- > 0;)
{
switch (result.RawValueDetails.ItemsElementName[i])
{
case ItemsChoiceType3.MeasureValue:
argNames.Append(';').Append("MEASURE_VALUE");
break;
case ItemsChoiceType3.NullDataCode:
argNames.Append(';').Append("NULL_DATA_CD");
break;
case ItemsChoiceType3.UncertaintyValue:
argNames.Append(';').Append("UNCERTAINTY_VALUE");
break;
default:
continue;
}
args.Add(result.RawValueDetails.Items[i]);
}
_baseDao.DoStoredProcWithArgs("aqs_RawResultUpsert", argNames.ToString(), args);
}
else
{
var blankResult = item as BlankInformationType;
if (blankResult != null)
{
args = new List <object>();
argNames = new System.Text.StringBuilder("ACTION_CD;AQS_TRANS_PROTOCOL_FK;DATA_APPROVAL_IND;DATA_VALIDITY_CD;SMPL_COLL_START_DATE;SMPL_COLL_START_TIME");
args.Add(blankResult.ActionCode);
args.Add(protocolID);
args.Add(blankResult.RawValueDetails.DataApprovalIndicator);
args.Add(blankResult.RawValueDetails.DataValidityCode);
args.Add(blankResult.SampleCollectionStartDate);
args.Add(blankResult.SampleCollectionStartTime);
for (int i = blankResult.RawValueDetails.Items.Length; i-- > 0;)
{
switch (blankResult.RawValueDetails.ItemsElementName[i])
{
case ItemsChoiceType3.MeasureValue:
argNames.Append(';').Append("MEASURE_VALUE");
break;
case ItemsChoiceType3.NullDataCode:
argNames.Append(';').Append("NULL_DATA_CD");
break;
case ItemsChoiceType3.UncertaintyValue:
argNames.Append(';').Append("UNCERTAINTY_VALUE");
break;
default:
continue;
}
args.Add(blankResult.RawValueDetails.Items[i]);
}
argNames.Append(';').Append("BLANK_TYPE_CD");
args.Add(blankResult.BlankTypeCode);
_baseDao.DoStoredProcWithArgs("aqs_BlankResultUpsert", argNames.ToString(), args);
}
}
}
}
}
}
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// ExecuteMetaDataValidation - Executes SP that inserts the metadata
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Data.ExecuteMetaDataValidation(_baseDao, _clearMetadataBeforeRun);
}
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 GenerateWqxObjectsFromSubmissionFile(IAppendAuditLogEvent appendAuditLogEvent, string submissionFilePath,
string sysTempFolderPath, Assembly xmlSchemaZippedResourceAssembly,
string xmlSchemaZippedQualifiedResourceName, string xmlSchemaRootFileName,
ISerializationHelper serializationHelper, ICompressionHelper compressionHelper,
out string attachmentsFolderPath, out string validationErrorsFile)
{
WQXDataType data = null;
attachmentsFolderPath = null;
string wqxFilePath = null;
validationErrorsFile = null;
try
{
attachmentsFolderPath = Path.Combine(sysTempFolderPath, Guid.NewGuid().ToString());
Directory.CreateDirectory(attachmentsFolderPath);
appendAuditLogEvent.AppendAuditLogEvent("Decompressing the WQX data to a temporary folder ...");
try
{
compressionHelper.UncompressDirectory(submissionFilePath, attachmentsFolderPath);
}
catch (Exception ex)
{
throw new ArgException("An error occurred decompressing the WQX data: {0}", ExceptionUtils.GetDeepExceptionMessage(ex));
}
string[] xmlFiles = Directory.GetFiles(attachmentsFolderPath, "*.xml");
if (xmlFiles.Length == 0)
{
throw new ArgException("Failed to locate an WQX xml file in the WQX data");
}
else if (xmlFiles.Length > 1)
{
throw new ArgException("More than one xml file was found in the WQX data");
}
wqxFilePath = xmlFiles[0];
if (!string.IsNullOrEmpty(xmlSchemaZippedQualifiedResourceName))
{
validationErrorsFile =
BaseWNOSPlugin.ValidateXmlFile(wqxFilePath, xmlSchemaZippedResourceAssembly, xmlSchemaZippedQualifiedResourceName,
xmlSchemaRootFileName, sysTempFolderPath, appendAuditLogEvent, compressionHelper);
if (validationErrorsFile != null)
{
FileUtils.SafeDeleteDirectory(attachmentsFolderPath);
return(null);
}
}
//Remove header
wqxFilePath = RemoveHeaderFile(wqxFilePath, sysTempFolderPath, serializationHelper);
appendAuditLogEvent.AppendAuditLogEvent("Deserializing the WQX data xml file ...");
try
{
data = serializationHelper.Deserialize <WQXDataType>(wqxFilePath);
}
catch (Exception ex)
{
appendAuditLogEvent.AppendAuditLogEvent("Failed to deserialize the WQX data xml file: {0}", ExceptionUtils.GetDeepExceptionMessage(ex));
throw;
}
if (data == null)
{
appendAuditLogEvent.AppendAuditLogEvent("The WQX data does not contain any organizations, so no elements will be stored in the database.");
return(null);
}
}
catch (Exception ex)
{
FileUtils.SafeDeleteDirectory(attachmentsFolderPath);
throw ex;
}
finally
{
FileUtils.SafeDeleteFile(wqxFilePath);
}
return(data);
}
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));
}
public AirQualitySubmissionType GetAirQualityData(string imputPath, AqsFileType fileType, string format, IAppendAuditLogEvent appendAuditLogProvider)
{
try
{
IAbstractHeader <AirQualitySubmissionType> mappedParser = GetAqsHeader(fileType, format)
.Materialize(GetValueMappings(format))
.Materialize(GetColumnMappings(format));
Windsor.Commons.Core.CommaSeparatedFileParser parser = new Windsor.Commons.Core.CommaSeparatedFileParser(imputPath);
while (parser.NextLine())
{
mappedParser.ConsumeRow(parser);
}
return(mappedParser.Resolve());
}
catch (Exception x)
{
appendAuditLogProvider.AppendAuditLogEvent(x.Message);
throw;
}
}
