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