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