protected void SaveDataFileToTransaction(string transactionId, WQXDataType data) { string tempXmlFilePath = _settingsProvider.NewTempFilePath(); tempXmlFilePath = Path.ChangeExtension(tempXmlFilePath, ".xml"); try { AppendAuditLogEvent("Saving generated WQX xml file to transaction ..."); _serializationHelper.Serialize(data, tempXmlFilePath); _documentManager.AddDocument(transactionId, CommonTransactionStatusCode.Completed, null, tempXmlFilePath); } catch (Exception e) { AppendAuditLogEvent("Failed to save generated WQX xml file to transaction: {0}", ExceptionUtils.GetDeepExceptionMessage(e)); throw; } finally { FileUtils.SafeDeleteFile(tempXmlFilePath); } }
protected override object GetInsertUpdateData() { object data = base.GetInsertUpdateData(); WQXDataType wqxData = (WQXDataType)data; SpringBaseDao destDao = ValidateDBProvider(DEST_PROVIDER_KEY, typeof(NamedNullMappingDataReader)); IObjectsToDatabase objectsToDatabase; GetServiceImplementation(out objectsToDatabase); AppendAuditLogEvent("Building database if it does not exist"); objectsToDatabase.BuildDatabase(data.GetType(), destDao); AppendAuditLogEvent("Storing {0} Projects, {1} Activities, {2} Biological Habitat Indexes, {3} Monitoring Locations, and {4} Activity Groups into the database", CollectionUtils.Count(wqxData.Organization.Project).ToString(), CollectionUtils.Count(wqxData.Organization.Activity).ToString(), CollectionUtils.Count(wqxData.Organization.BiologicalHabitatIndex).ToString(), CollectionUtils.Count(wqxData.Organization.MonitoringLocation).ToString(), CollectionUtils.Count(wqxData.Organization.ActivityGroup).ToString()); objectsToDatabase.SaveToDatabase(data, destDao); return(data); }
private void ValidateOrganizationExists(WQXDataType organization) { int count = (int)_dao.DoJDBCExecuteScalar("SELECT COUNT(*) FROM Organization WHERE UPPER(OrganizationIdentifier) = UPPER(?)", organization.Organization.OrganizationDescription.OrganizationIdentifier); if (count < 1) { throw new ArgException("A WQX organization with id \"{0}\" does not exist in the database", organization.Organization.OrganizationDescription.OrganizationIdentifier); } }
public static int TotalResultCount(WQXDataType data) { int count = 0; if ((data != null) && (data.Organization != null) && (data.Organization.Activity != null)) { foreach (Windsor.Node2008.WNOSPlugin.WQX2XsdOrm.ActivityDataType activity in data.Organization.Activity) { count += (activity.Result == null) ? 0 : activity.Result.Length; } } return(count); }
public void MergeWQXData(WQXDataType organization, SpringBaseDao dao, bool setLastUpdatedDateTime, bool submitToEpa, out Dictionary <string, int> insertCounts, out Dictionary <string, int> updateCounts) { ExceptionUtils.ThrowIfNull(organization, "organization"); ExceptionUtils.ThrowIfNull(organization.Organization, "organization.Organization"); ExceptionUtils.ThrowIfNull(organization.Organization.OrganizationDescription, "organization.Organization.OrganizationDescription"); ExceptionUtils.ThrowIfEmptyString(organization.Organization.OrganizationDescription.OrganizationIdentifier, "organization.Organization.OrganizationDescription.OrganizationIdentifier"); const int commandTimeout = 1800; _submitToEpa = submitToEpa; _insertCounts = new Dictionary <string, int>(); _updateCounts = new Dictionary <string, int>(); if (!CollectionUtils.IsNullOrEmpty(organization.Organization.Activity)) { if (CollectionUtils.IsNullOrEmpty(organization.Organization.Project)) { throw new ArgException("The input WQX data does not contain any projects"); } _dao = dao; _dao.AdoTemplate.CommandTimeout = commandTimeout; _lastUpdatedDateTime = DateTime.Now; _setLastUpdatedDateTime = setLastUpdatedDateTime; ValidateOrganizationExists(organization); ValidateProjectsExist(organization); ValidateMonitoringLocationsExist(organization); dao.TransactionTemplate.Execute(delegate(Spring.Transaction.ITransactionStatus status) { dao.AdoTemplate.ClassicAdoTemplate.Execute(delegate(IDbCommand dbCommand) { MergeActivities(dbCommand, organization); InsertResults(dbCommand, organization); return(null); }); return(null); }); } insertCounts = _insertCounts; updateCounts = _updateCounts; }
private void ValidateProjectsExist(WQXDataType organization) { foreach (ProjectDataType project in organization.Organization.Project) { int count = (int)_dao.DoJDBCExecuteScalar("SELECT COUNT(*) FROM Project WHERE UPPER(OrganizationIdentifier) = UPPER(?) AND UPPER(ProjectIdentifier) = UPPER(?)", organization.Organization.OrganizationDescription.OrganizationIdentifier, project.ProjectIdentifier); if (count < 1) { throw new ArgException("A WQX project with id \"{0}\" does not exist in the database for organization \"{1}\"", project.ProjectIdentifier, organization.Organization.OrganizationDescription.OrganizationIdentifier); } } }
private void ValidateMonitoringLocationsExist(WQXDataType organization) { foreach (MonitoringLocationDataType location in organization.Organization.MonitoringLocation) { int count = (int)_dao.DoJDBCExecuteScalar("SELECT COUNT(*) FROM MonitoringLocation WHERE UPPER(OrganizationIdentifier) = UPPER(?) AND UPPER(MonitoringLocationIdentifier) = UPPER(?)", organization.Organization.OrganizationDescription.OrganizationIdentifier, location.MonitoringLocationIdentity.MonitoringLocationIdentifier); if (count < 1) { throw new ArgException("A WQX monitoring location with id \"{0}\" does not exist in the database for organization \"{1}\"", location.MonitoringLocationIdentity.MonitoringLocationIdentifier, organization.Organization.OrganizationDescription.OrganizationIdentifier); } } }
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); } }
protected virtual WQXDataType GetWQXData(string transactionId, string docId, out Windsor.Node2008.WNOSPlugin.WQX1XsdOrm.WQXDataType data1, out WQXDeleteDataType deleteData, out string attachmentsFolderPath) { WQXDataType data = null; data1 = null; deleteData = null; string tempXmlFilePath = _settingsProvider.NewTempFilePath(); attachmentsFolderPath = null; string tempFolder = null; try { IHeaderDocumentHelper headerDocumentHelper; GetServiceImplementation(out headerDocumentHelper); AppendAuditLogEvent("Getting data for document with id \"{0}\"", docId); Document document = _documentManager.GetDocument(transactionId, docId, true); if (document.IsZipFile) { tempFolder = _settingsProvider.CreateNewTempFolderPath(); AppendAuditLogEvent("Decompressing document to temporary folder"); _compressionHelper.UncompressDirectory(document.Content, tempFolder); string[] xmlFiles = Directory.GetFiles(tempFolder, "*.xml"); if (xmlFiles.Length == 0) { throw new ArgException("Failed to locate a 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"); } tempXmlFilePath = xmlFiles[0]; } else { AppendAuditLogEvent("Writing document data to temporary file"); File.WriteAllBytes(tempXmlFilePath, document.Content); } XmlElement loadElement = null; try { AppendAuditLogEvent("Attempting to load document with Exchange Header"); headerDocumentHelper.Load(tempXmlFilePath); loadElement = headerDocumentHelper.GetPayload("Update-Insert"); if (loadElement == null) { loadElement = headerDocumentHelper.GetPayload("Delete"); if (loadElement == null) { throw new ArgumentException("The submitted document does not contain an \"Update-Insert\" or \"Delete\" payload"); } } } catch (Exception) { AppendAuditLogEvent("Document does not contain an Exchange Header"); // Assume, for now, that document does not have a header } AppendAuditLogEvent("Deserializing document data to WQX data"); if (loadElement != null) { try { data = _serializationHelper.Deserialize <WQXDataType>(loadElement); attachmentsFolderPath = tempFolder; } catch (Exception) { AppendAuditLogEvent("Failed to deserialize WQX v2, trying WQX v1 ..."); try { data1 = _serializationHelper.Deserialize <Windsor.Node2008.WNOSPlugin.WQX1XsdOrm.WQXDataType>(loadElement); } catch (Exception) { AppendAuditLogEvent("Failed to deserialize WQX v1, trying WQX v2 Delete data ..."); deleteData = _serializationHelper.Deserialize <WQXDeleteDataType>(loadElement); } } } else { try { data = _serializationHelper.Deserialize <WQXDataType>(tempXmlFilePath); attachmentsFolderPath = tempFolder; } catch (Exception) { AppendAuditLogEvent("Failed to deserialize WQX v2, trying WQX v1 ..."); try { data1 = _serializationHelper.Deserialize <Windsor.Node2008.WNOSPlugin.WQX1XsdOrm.WQXDataType>(tempXmlFilePath); } catch (Exception) { AppendAuditLogEvent("Failed to deserialize WQX v1, trying WQX v2 Delete data ..."); deleteData = _serializationHelper.Deserialize <WQXDeleteDataType>(tempXmlFilePath); } } } } catch (Exception) { FileUtils.SafeDeleteAllFilesAndFoldersInFolder(tempFolder); throw; } finally { FileUtils.SafeDeleteFile(tempXmlFilePath); } return(data); }
protected virtual void ProcessSubmitDocument(string transactionId, string docId) { string attachmentsFolderPath = null; try { WQXDataType data = null; WQXDeleteDataType deleteData = null; Windsor.Node2008.WNOSPlugin.WQX1XsdOrm.WQXDataType data1 = null; data = GetWQXData(transactionId, docId, out data1, out deleteData, out attachmentsFolderPath); AppendAuditLogEvent("Storing WQX data into database"); string orgId; object addObject; if (data != null) { if (data.Organization == null) { throw new InvalidDataException("Deserialized WQX data does not contain an Organization element"); } if ((data.Organization.OrganizationDescription == null) || string.IsNullOrEmpty(data.Organization.OrganizationDescription.OrganizationIdentifier)) { throw new InvalidDataException("WQX data does not contain an OrganizationIdentifier element"); } orgId = data.Organization.OrganizationDescription.OrganizationIdentifier; addObject = data; AppendAuditLogEvent("WQX data contains {0} Projects, {1} Activities, {2} Biological Habitat Indexes, {3} Monitoring Locations, and {4} Activity Groups for Organization Id \"{5}\"", CollectionUtils.Count(data.Organization.Project).ToString(), CollectionUtils.Count(data.Organization.Activity).ToString(), CollectionUtils.Count(data.Organization.BiologicalHabitatIndex).ToString(), CollectionUtils.Count(data.Organization.MonitoringLocation).ToString(), CollectionUtils.Count(data.Organization.ActivityGroup).ToString(), orgId); } else if (data1 != null) { if (data1.Organization == null) { throw new InvalidDataException("Deserialized WQX data does not contain an Organization element"); } if ((data1.Organization.OrganizationDescription == null) || string.IsNullOrEmpty(data1.Organization.OrganizationDescription.OrganizationIdentifier)) { throw new InvalidDataException("WQX data does not contain an OrganizationIdentifier element"); } orgId = data1.Organization.OrganizationDescription.OrganizationIdentifier; addObject = data1; AppendAuditLogEvent("WQX data contains {0} Projects, {1} Activities, {2} Biological Habitat Indexes, {3} Monitoring Locations, and {4} Activity Groups for Organization Id \"{5}\"", CollectionUtils.Count(data1.Organization.Project).ToString(), CollectionUtils.Count(data1.Organization.Activity).ToString(), "0", CollectionUtils.Count(data1.Organization.MonitoringLocation).ToString(), CollectionUtils.Count(data1.Organization.ActivityGroup).ToString(), data1.Organization.OrganizationDescription.OrganizationIdentifier); } else { if (deleteData.OrganizationDelete == null) { throw new InvalidDataException("Deserialized WQX data does not contain an Organization element"); } if (string.IsNullOrEmpty(deleteData.OrganizationDelete.OrganizationIdentifier)) { throw new InvalidDataException("WQX data does not contain an OrganizationIdentifier element"); } orgId = deleteData.OrganizationDelete.OrganizationIdentifier; addObject = deleteData; AppendAuditLogEvent("WQX delete data contains {0} Projects, {1} Activities, {2} Biological Habitat Indexes, {3} Monitoring Locations, and {4} Activity Groups for Organization Id \"{5}\"", CollectionUtils.Count(deleteData.OrganizationDelete.ProjectIdentifier).ToString(), CollectionUtils.Count(deleteData.OrganizationDelete.ActivityIdentifier).ToString(), CollectionUtils.Count(deleteData.OrganizationDelete.IndexIdentifier).ToString(), CollectionUtils.Count(deleteData.OrganizationDelete.MonitoringLocationIdentifier).ToString(), CollectionUtils.Count(deleteData.OrganizationDelete.ActivityGroupIdentifier).ToString(), deleteData.OrganizationDelete.OrganizationIdentifier); } if (_authorizedWqxUsers != null) { AppendAuditLogEvent("Validating that User \"{0}\" is authorized to submit WQX data for Organization Id \"{1}\" ...", _submitUsername, orgId); List <string> usernames = null; if (_authorizedWqxUsers.TryGetValue(orgId.ToUpper(), out usernames)) { if (!usernames.Contains("*") && !usernames.Contains(_submitUsername.ToUpper())) { string orgName = _settingsProvider.NodeOrganizationName; throw new UnauthorizedAccessException(string.Format("The User \"{0}\" is not authorized to provide data to the {1} for the Organization ID \"{2}.\" If you feel you have received this message in error, please contact the {1} for further assistance.", _submitUsername, orgName, orgId)); } } else if (_authorizedWqxUsers.TryGetValue("*", out usernames)) { if (!usernames.Contains("*") && !usernames.Contains(_submitUsername.ToUpper())) { string orgName = _settingsProvider.NodeOrganizationName; throw new UnauthorizedAccessException(string.Format("The User \"{0}\" is not authorized to provide data to the {1} for the Organization ID \"{2}.\" If you feel you have received this message in error, please contact the {1} for further assistance.", _submitUsername, orgName, orgId)); } } else { string orgName = _settingsProvider.NodeOrganizationName; throw new UnauthorizedAccessException(string.Format("Organization ID \"{0}\" has not been authorized to provide data to the {1}. If you feel you have received this message in error, please contact the {1} for further assistance.", orgId, orgName)); } } _baseDao.TransactionTemplate.Execute(delegate { if (_deleteExistingDataBeforeInsert) { AppendAuditLogEvent("Deleting existing WQX data from the data store for organization \"{0}\" ...", orgId); int numRowsDeleted = 0; string groupWhere = string.Format("PARENTID IN (SELECT RECORDID FROM WQX_ORGANIZATION WHERE UPPER(ORGID) = UPPER('{0}'))", orgId); _baseDao.DoSimpleDelete("WQX_ACTIVITYGROUP", groupWhere, null); numRowsDeleted = _baseDao.DoSimpleDelete("WQX_ORGANIZATION", "ORGID", orgId); if (numRowsDeleted > 0) { AppendAuditLogEvent("Deleted {0} existing WQX data rows from the data store for organization \"{1}\"", numRowsDeleted.ToString(), orgId); } else { AppendAuditLogEvent("Did not find any existing WQX data to delete from the data store for organization \"{0}\"", orgId); } } AppendAuditLogEvent("Storing WQX data for organization \"{0}\" into data store ...", orgId); Dictionary <string, int> tableRowCounts = _objectsToDatabase.SaveToDatabase(addObject, _baseDao); if (attachmentsFolderPath != null) { DatabaseHelper.StoreAttachmentFilesFromFolder(_objectsToDatabase, _baseDao, data.Organization, attachmentsFolderPath); } string recordId = ReflectionUtils.GetFieldOrPropertyValueByName <string>(addObject, "RecordId"); AppendAuditLogEvent("Stored WQX data content with organization primary key \"{0}\" into data store with the following table row counts: {1}", recordId, CreateTableRowCountsString(tableRowCounts)); return(null); }); } catch (Exception e) { AppendAuditLogEvent("Failed to process document with id \"{0}.\" EXCEPTION: {1}", docId.ToString(), ExceptionUtils.ToShortString(e)); throw; } finally { FileUtils.SafeDeleteAllFilesAndFoldersInFolder(attachmentsFolderPath); } }
protected override object GetInsertUpdateData() { IObjectsFromDatabase objectsFromDatabase; GetServiceImplementation(out objectsFromDatabase); Dictionary <string, DbAppendSelectWhereClause> selectClauses = new Dictionary <string, DbAppendSelectWhereClause>(); selectClauses.Add("WQX_ORGANIZATION", new DbAppendSelectWhereClause(_baseDao, "ORGID = ?", _organizationIdentifier)); if ((_wqxStartDate != DateTime.MinValue) && (_wqxEndDate != DateTime.MinValue)) { if (_wqxStartDate > _wqxEndDate) { throw new ArgException("The query start date parameter must be less than or equal to the end date parameter"); } AppendAuditLogEvent("Querying WQX data with OrgId \"{0}\", StartDate \"{1}\", and EndDate \"{1}\" ...", _organizationIdentifier, _wqxStartDate, _wqxEndDate); string activityElementSelect = "(ACTIVITYSTARTDATE >= ?) AND (ACTIVITYSTARTDATE <= ?) AND PARENTID IN (SELECT RECORDID FROM WQX_ORGANIZATION WHERE ORGID = ?)"; string activityRecordIdsSelect = "SELECT RECORDID FROM WQX_ACTIVITY WHERE " + activityElementSelect; string biologicalElementSelect = "(INDEXCALCULATEDDATE >= ?) AND (INDEXCALCULATEDDATE <= ?) AND PARENTID IN (SELECT RECORDID FROM WQX_ORGANIZATION WHERE ORGID = ?)"; string biologicalRecordIdsSelect = "SELECT RECORDID FROM WQX_BIOLOGICALHABITATINDEX WHERE " + biologicalElementSelect; string projectElementSelect = string.Format("RECORDID IN (SELECT PROJECTPARENTID FROM WQX_PROJECTACTIVITY WHERE ACTIVITYPARENTID IN ({0}))", activityRecordIdsSelect); string activityGroupElementSelect = string.Format("RECORDID IN (SELECT ACTIVITYGROUPPARENTID FROM WQX_ACTIVITYACTIVITYGROUP WHERE ACTIVITYPARENTID IN ({0}))", activityRecordIdsSelect); string monitoringLocationElementSelect = string.Format("((MONITORINGLOCATIONID IN (SELECT MONLOCID FROM WQX_ACTIVITY WHERE {0})) OR (MONITORINGLOCATIONID IN (SELECT MONLOCID FROM WQX_BIOLOGICALHABITATINDEX WHERE {1})))", activityElementSelect, biologicalElementSelect); string resultElementSelect = string.Format("PARENTID IN ({0})", activityRecordIdsSelect); selectClauses.Add("WQX_ACTIVITY", new DbAppendSelectWhereClause(_baseDao, activityElementSelect, _wqxStartDate, _wqxEndDate, _organizationIdentifier)); selectClauses.Add("WQX_PROJECT", new DbAppendSelectWhereClause(_baseDao, projectElementSelect, _wqxStartDate, _wqxEndDate, _organizationIdentifier)); selectClauses.Add("WQX_ACTIVITYGROUP", new DbAppendSelectWhereClause(_baseDao, activityGroupElementSelect, _wqxStartDate, _wqxEndDate, _organizationIdentifier)); selectClauses.Add("WQX_MONITORINGLOCATION", new DbAppendSelectWhereClause(_baseDao, monitoringLocationElementSelect, _wqxStartDate, _wqxEndDate, _organizationIdentifier, _wqxStartDate, _wqxEndDate, _organizationIdentifier)); selectClauses.Add("WQX_BIOLOGICALHABITATINDEX", new DbAppendSelectWhereClause(_baseDao, biologicalElementSelect, _wqxStartDate, _wqxEndDate, _organizationIdentifier)); selectClauses.Add("WQX_RESULT", new DbAppendSelectWhereClause(_baseDao, resultElementSelect, _wqxStartDate, _wqxEndDate, _organizationIdentifier)); } else if (_activityStartDate == DateTime.MinValue) { AppendAuditLogEvent("Querying WQX data with OrgId \"{0}\" and WQXUpdateDate \"{1}\" ...", _organizationIdentifier, _wqxUpdateDate); string primaryElementSelect = "(WQXUPDATEDATE >= ?) AND PARENTID IN (SELECT RECORDID FROM WQX_ORGANIZATION WHERE ORGID = ?)"; string activityGroupElementSelect = primaryElementSelect; string activityElementSelect = string.Format("({0}) OR (RECORDID IN (SELECT ACTIVITYPARENTID FROM WQX_ACTIVITYACTIVITYGROUP WHERE ACTIVITYGROUPPARENTID IN (SELECT RECORDID FROM WQX_ACTIVITYGROUP WHERE {1})))", primaryElementSelect, activityGroupElementSelect); string resultElementSelect = string.Format("PARENTID IN (SELECT RECORDID FROM WQX_ACTIVITY WHERE {0})", activityElementSelect); string projectElementSelect = string.Format("({0}) OR (RECORDID IN (SELECT PROJECTPARENTID FROM WQX_PROJECTACTIVITY WHERE ACTIVITYPARENTID IN (SELECT RECORDID FROM WQX_ACTIVITY WHERE {1})))", primaryElementSelect, activityElementSelect); selectClauses.Add("WQX_ACTIVITYGROUP", new DbAppendSelectWhereClause(_baseDao, activityGroupElementSelect, _wqxUpdateDate, _organizationIdentifier)); selectClauses.Add("WQX_ACTIVITY", new DbAppendSelectWhereClause(_baseDao, activityElementSelect, _wqxUpdateDate, _organizationIdentifier, _wqxUpdateDate, _organizationIdentifier)); selectClauses.Add("WQX_PROJECT", new DbAppendSelectWhereClause(_baseDao, projectElementSelect, _wqxUpdateDate, _organizationIdentifier, _wqxUpdateDate, _organizationIdentifier, _wqxUpdateDate, _organizationIdentifier)); selectClauses.Add("WQX_MONITORINGLOCATION", new DbAppendSelectWhereClause(_baseDao, primaryElementSelect, _wqxUpdateDate, _organizationIdentifier)); selectClauses.Add("WQX_BIOLOGICALHABITATINDEX", new DbAppendSelectWhereClause(_baseDao, primaryElementSelect, _wqxUpdateDate, _organizationIdentifier)); selectClauses.Add("WQX_RESULT", new DbAppendSelectWhereClause(_baseDao, resultElementSelect, _wqxUpdateDate, _organizationIdentifier, _wqxUpdateDate, _organizationIdentifier)); } else { AppendAuditLogEvent("Querying WQX data with OrgId \"{0}\" and ActivityStartDate \"{1}\" ...", _organizationIdentifier, _activityStartDate); string activityElementSelect = "(ACTIVITYSTARTDATE >= ?) AND PARENTID IN (SELECT RECORDID FROM WQX_ORGANIZATION WHERE ORGID = ?)"; string activityRecordIdsSelect = "SELECT RECORDID FROM WQX_ACTIVITY WHERE " + activityElementSelect; string projectElementSelect = string.Format("RECORDID IN (SELECT PROJECTPARENTID FROM WQX_PROJECTACTIVITY WHERE ACTIVITYPARENTID IN ({0}))", activityRecordIdsSelect); string activityGroupElementSelect = string.Format("RECORDID IN (SELECT ACTIVITYGROUPPARENTID FROM WQX_ACTIVITYACTIVITYGROUP WHERE ACTIVITYPARENTID IN ({0}))", activityRecordIdsSelect); string monitoringLocationElementSelect = string.Format("MONITORINGLOCATIONID IN (SELECT MONLOCID FROM WQX_ACTIVITY WHERE {0})", activityElementSelect); string biologicalHabitatElementSelect = string.Format("MONLOCID IN (SELECT MONLOCID FROM WQX_ACTIVITY WHERE {0})", activityElementSelect); string resultElementSelect = string.Format("PARENTID IN ({0})", activityRecordIdsSelect); selectClauses.Add("WQX_ACTIVITY", new DbAppendSelectWhereClause(_baseDao, activityElementSelect, _activityStartDate, _organizationIdentifier)); selectClauses.Add("WQX_PROJECT", new DbAppendSelectWhereClause(_baseDao, projectElementSelect, _activityStartDate, _organizationIdentifier)); selectClauses.Add("WQX_ACTIVITYGROUP", new DbAppendSelectWhereClause(_baseDao, activityGroupElementSelect, _activityStartDate, _organizationIdentifier)); selectClauses.Add("WQX_MONITORINGLOCATION", new DbAppendSelectWhereClause(_baseDao, monitoringLocationElementSelect, _activityStartDate, _organizationIdentifier)); selectClauses.Add("WQX_BIOLOGICALHABITATINDEX", new DbAppendSelectWhereClause(_baseDao, biologicalHabitatElementSelect, _activityStartDate, _organizationIdentifier)); selectClauses.Add("WQX_RESULT", new DbAppendSelectWhereClause(_baseDao, resultElementSelect, _activityStartDate, _organizationIdentifier)); } List <WQXDataType> dataList = objectsFromDatabase.LoadFromDatabase <WQXDataType>(_baseDao, selectClauses); if (CollectionUtils.IsNullOrEmpty(dataList)) { return(new WQXDataType()); } 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("Found {0} Projects, {1} Activities, {2} Biological Habitat Indexes, {3} Monitoring Locations, {4} Activity Groups, and {5} Results that matched the query", CollectionUtils.Count(data.Organization.Project).ToString(), CollectionUtils.Count(data.Organization.Activity).ToString(), CollectionUtils.Count(data.Organization.BiologicalHabitatIndex).ToString(), CollectionUtils.Count(data.Organization.MonitoringLocation).ToString(), CollectionUtils.Count(data.Organization.ActivityGroup).ToString(), TotalResultCount(data).ToString()); return(dataList[0]); } }
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 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, 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); }
public static void WriteAttachmentFilesToFolder(SpringBaseDao baseDao, WQXDataType data, string folderPath) { OrganizationDataType org = data.Organization; if (org.Activity != null) { CollectionUtils.ForEach(org.Activity, delegate(ActivityDataType activity) { if (activity.AttachedBinaryObject != null) { CollectionUtils.ForEach(activity.AttachedBinaryObject, delegate(ActivityAttachedBinaryObjectDataType attachment) { if (string.IsNullOrEmpty(attachment.BinaryObjectFileName)) { throw new ArgException("An attachment for the activity with id \"{0}\" does not have an attachment name.", activity.ActivityDescription.ActivityIdentifier); } string filePath = Path.Combine(folderPath, attachment.BinaryObjectFileName); if (File.Exists(filePath)) { throw new ArgException("Failed to write the attachment \"{0}\" for the activity with id \"{1}\" because another file with the same name already exists in the temporary folder: \"{2}\"", attachment.BinaryObjectFileName, activity.ActivityDescription.ActivityIdentifier, filePath); } byte[] content = null; baseDao.DoJDBCQueryWithRowCallbackDelegate("SELECT BINARYOBJECTCONTENT FROM WQX_ACTATTACHEDBINARYOBJECT WHERE PARENTID = ? AND BINARYOBJECTFILE = ?", delegate(IDataReader dataReader) { content = (byte[])dataReader.GetValue(0); }, activity.ActivityDescription.ActivityIdentifier, attachment.BinaryObjectFileName); if (content == null) { throw new ArgException("Failed to load attachment content for the file \"{0}\" from the database for the activity with id \"{1}\"", attachment.BinaryObjectFileName, activity.ActivityDescription.ActivityIdentifier); } UncompressFile(content, filePath); }); } }); } ; if (org.Project != null) { CollectionUtils.ForEach(org.Project, delegate(ProjectDataType project) { if (project.AttachedBinaryObject != null) { CollectionUtils.ForEach(project.AttachedBinaryObject, delegate(ProjectAttachedBinaryObjectDataType attachment) { if (string.IsNullOrEmpty(attachment.BinaryObjectFileName)) { throw new ArgException("An attachment for the project \"{0}\" with id \"{1}\" does not have an attachment name.", project.ProjectName, project.ProjectIdentifier); } string filePath = Path.Combine(folderPath, attachment.BinaryObjectFileName); if (File.Exists(filePath)) { throw new ArgException("Failed to write the attachment \"{0}\" for the project \"{1}\" with id \"{2}\" because another file with the same name already exists in the temporary folder: \"{3}\"", attachment.BinaryObjectFileName, project.ProjectName, project.ProjectIdentifier, filePath); } byte[] content = null; baseDao.DoJDBCQueryWithRowCallbackDelegate("SELECT BINARYOBJECTCONTENT FROM WQX_PROJATTACHEDBINARYOBJECT WHERE PARENTID = ? AND BINARYOBJECTFILE = ?", delegate(IDataReader dataReader) { content = (byte[])dataReader.GetValue(0); }, project.ProjectIdentifier, attachment.BinaryObjectFileName); if (content == null) { throw new ArgException("Failed to load attachment content for the file \"{0}\" from the database for the project \"{1}\" with id \"{2}\"", attachment.BinaryObjectFileName, project.ProjectName, project.ProjectIdentifier); } UncompressFile(content, filePath); }); } }); } if (org.MonitoringLocation != null) { CollectionUtils.ForEach(org.MonitoringLocation, delegate(MonitoringLocationDataType monitoringLocation) { if (monitoringLocation.AttachedBinaryObject != null) { CollectionUtils.ForEach(monitoringLocation.AttachedBinaryObject, delegate(MonitoringLocationAttachedBinaryObjectDataType attachment) { if (string.IsNullOrEmpty(attachment.BinaryObjectFileName)) { throw new ArgException("An attachment for the monitoring location \"{0}\" with id \"{1}\" does not have an attachment name.", monitoringLocation.WellInformation, monitoringLocation.MonitoringLocationIdentity.MonitoringLocationIdentifier); } string filePath = Path.Combine(folderPath, attachment.BinaryObjectFileName); if (File.Exists(filePath)) { throw new ArgException("Failed to write the attachment \"{0}\" for the monitoring location \"{1}\" with id \"{2}\" because another file with the same name already exists in the temporary folder: \"{3}\"", attachment.BinaryObjectFileName, monitoringLocation.WellInformation, monitoringLocation.MonitoringLocationIdentity.MonitoringLocationIdentifier, filePath); } byte[] content = null; baseDao.DoJDBCQueryWithRowCallbackDelegate("SELECT BINARYOBJECTCONTENT FROM WQX_MONLOCATTACHEDBINARYOBJECT WHERE PARENTID = ? AND BINARYOBJECTFILE = ?", delegate(IDataReader dataReader) { content = (byte[])dataReader.GetValue(0); }, monitoringLocation.MonitoringLocationIdentity.MonitoringLocationIdentifier, attachment.BinaryObjectFileName); if (content == null) { throw new ArgException("Failed to load attachment content for the file \"{0}\" from the database for the monitoring location \"{1}\" with id \"{2}\"", attachment.BinaryObjectFileName, monitoringLocation.WellInformation, monitoringLocation.MonitoringLocationIdentity.MonitoringLocationIdentifier); } UncompressFile(content, filePath); }); } }); } }
protected override WQXDataType GetWQXData(string transactionId, string docId, out Windsor.Node2008.WNOSPlugin.WQX1XsdOrm.WQXDataType data1, out WQXDeleteDataType deleteData, out string attachmentsFolderPath) { WQXDataType data = null; data1 = null; deleteData = null; attachmentsFolderPath = null; string tempFolderPath = Path.Combine(_settingsProvider.TempFolderPath, Guid.NewGuid().ToString()); try { AppendAuditLogEvent("Extracting contents of document with id \"{0}\" to temporary folder", docId); _compressionHelper.UncompressDirectory(_documentManager.GetContent(transactionId, docId), tempFolderPath); string projectsFile, monitoringFile, resultsFile; GetFlatFiles(tempFolderPath, out projectsFile, out monitoringFile, out resultsFile); AppendAuditLogEvent("Generating WQX data from flat files for Organization Id \"{0}\" and Name \"{1}\"", _organizationId, _organizationName); Dictionary <string, MonitoringLocationDataType> monitoringLocations = WQXFlatFileParser.ParseMonitoringLocations(monitoringFile); if (CollectionUtils.IsNullOrEmpty(monitoringLocations)) { throw new InvalidDataException(string.Format("The document does not contain any monitoring locations inside the flat file")); } Dictionary <string, ProjectDataType> projects = WQXFlatFileParser.ParseProjects(projectsFile); if (CollectionUtils.IsNullOrEmpty(projects)) { throw new InvalidDataException(string.Format("The document does not contain any projects inside the flat file")); } Dictionary <string, ActivityDataType> activities = WQXFlatFileParser.ParseResults(resultsFile, projects, monitoringLocations, _resultAnalyticalMethodLookups, _sampleCollectionMethodLookups, false); data = new WQXDataType(); data.Organization = new OrganizationDataType(); data.Organization.OrganizationDescription = new OrganizationDescriptionDataType(); data.Organization.OrganizationDescription.OrganizationIdentifier = _organizationId; data.Organization.OrganizationDescription.OrganizationFormalName = _organizationName; if (!CollectionUtils.IsNullOrEmpty(monitoringLocations)) { data.Organization.MonitoringLocation = new List <MonitoringLocationDataType>(monitoringLocations.Values).ToArray(); } if (!CollectionUtils.IsNullOrEmpty(projects)) { data.Organization.Project = new List <ProjectDataType>(projects.Values).ToArray(); } if (!CollectionUtils.IsNullOrEmpty(activities)) { data.Organization.Activity = new List <ActivityDataType>(activities.Values).ToArray(); } SaveDataFileToTransaction(transactionId, data); } finally { FileUtils.SafeDeleteDirectory(tempFolderPath); } return(data); }
private void MergeActivities(IDbCommand dbCommand, WQXDataType organization) { const string ACT_PK_COLUMN = "ActivityIdentifier"; const string ACT_UPDATE_COLUMNS = ACT_PK_COLUMN + ";ActivityTypeCode;ActivityMediaName;OrganizationIdentifier;ActivityStartDate;ActivityStartTime;ActivityStartTimeZoneCode;ActivityEndDate;" + "ActivityEndTime;ActivityEndTimeZoneCode;ActivityMeasureValue;ActivityMeasureUnitCode;ProjectIdentifier;MonitoringLocationIdentifier;ActivityCommentText;" + "AssemblageSampledName;SaCollMethodIdentifier;SaCollMethodIdentifierContext;SaCollMethodName;SaCollmethodQualifierTypeName;SaCollMethodDescriptionText;" + "SaCollEquipmentName;LastChangeDate;SubmitToEPA;SubmitToNWIFC;Delete"; const string ACT_TABLE_NAME = "Activity"; if (CollectionUtils.IsNullOrEmpty(organization.Organization.Activity)) { return; } Dictionary <string, string> addedActivities = new Dictionary <string, string>(organization.Organization.Activity.Length); Dictionary <string, string> existingActivities; int count = (int)_dao.DoJDBCExecuteScalar("SELECT COUNT(*) FROM " + ACT_TABLE_NAME + " WHERE UPPER(OrganizationIdentifier) = UPPER(?)", organization.Organization.OrganizationDescription.OrganizationIdentifier); if (count > 0) { existingActivities = new Dictionary <string, string>(count); DoSimpleQueryWithRowCallbackDelegate(dbCommand, ACT_TABLE_NAME, "OrganizationIdentifier", new object[] { organization.Organization.OrganizationDescription.OrganizationIdentifier }, ACT_PK_COLUMN, delegate(IDataReader reader) { string activityId = reader.GetString(0); existingActivities[activityId] = activityId; }); } else { existingActivities = new Dictionary <string, string>(); } int maxColCount = ACT_UPDATE_COLUMNS.Split(';').Length; object[] values = new object[maxColCount]; CollectionUtils.ForEach(organization.Organization.Activity, delegate(ActivityDataType activity) { ExceptionUtils.ThrowIfNull(activity, "activity"); ExceptionUtils.ThrowIfNull(activity.ActivityDescription, "activity.ActivityDescription"); if (CollectionUtils.IsNullOrEmpty(activity.ActivityDescription.ProjectIdentifier)) { throw new ArgException("The activity \"{0}\" does not have an associated project", activity.ActivityDescription.ActivityIdentifier); } if (string.IsNullOrEmpty(activity.ActivityDescription.MonitoringLocationIdentifier)) { throw new ArgException("The activity \"{0}\" does not have an associated monitoring location", activity.ActivityDescription.ActivityIdentifier); } int valueIndex = 0; values[valueIndex++] = activity.ActivityDescription.ActivityIdentifier; values[valueIndex++] = activity.ActivityDescription.ActivityTypeCode; values[valueIndex++] = activity.ActivityDescription.ActivityMediaName; values[valueIndex++] = organization.Organization.OrganizationDescription.OrganizationIdentifier; values[valueIndex++] = activity.ActivityDescription.ActivityStartDate; if (activity.ActivityDescription.ActivityStartTime != null) { values[valueIndex++] = activity.ActivityDescription.ActivityStartTime.Time; values[valueIndex++] = activity.ActivityDescription.ActivityStartTime.TimeZoneCode; } else { values[valueIndex++] = null; values[valueIndex++] = null; } if (activity.ActivityDescription.ActivityEndDateSpecified) { values[valueIndex++] = activity.ActivityDescription.ActivityEndDate; } else { values[valueIndex++] = null; } if (activity.ActivityDescription.ActivityEndTime != null) { values[valueIndex++] = activity.ActivityDescription.ActivityEndTime.Time; values[valueIndex++] = activity.ActivityDescription.ActivityEndTime.TimeZoneCode; } else { values[valueIndex++] = null; values[valueIndex++] = null; } if (activity.ActivityDescription.ActivityDepthHeightMeasure != null) { values[valueIndex++] = activity.ActivityDescription.ActivityDepthHeightMeasure.MeasureValue; values[valueIndex++] = activity.ActivityDescription.ActivityDepthHeightMeasure.MeasureUnitCode; } else { values[valueIndex++] = null; values[valueIndex++] = null; } values[valueIndex++] = activity.ActivityDescription.ProjectIdentifier[0]; values[valueIndex++] = activity.ActivityDescription.MonitoringLocationIdentifier; values[valueIndex++] = activity.ActivityDescription.ActivityCommentText; if (activity.BiologicalActivityDescription != null) { values[valueIndex++] = activity.BiologicalActivityDescription.AssemblageSampledName; } else { values[valueIndex++] = null; } if (activity.SampleDescription != null) { if (activity.SampleDescription.SampleCollectionMethod != null) { values[valueIndex++] = activity.SampleDescription.SampleCollectionMethod.MethodIdentifier; values[valueIndex++] = activity.SampleDescription.SampleCollectionMethod.MethodIdentifierContext; values[valueIndex++] = activity.SampleDescription.SampleCollectionMethod.MethodName; values[valueIndex++] = activity.SampleDescription.SampleCollectionMethod.MethodQualifierTypeName; values[valueIndex++] = activity.SampleDescription.SampleCollectionMethod.MethodDescriptionText; } else { values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; } values[valueIndex++] = activity.SampleDescription.SampleCollectionEquipmentName; } else { values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; } if (_setLastUpdatedDateTime) { values[valueIndex++] = _lastUpdatedDateTime; } else { values[valueIndex++] = null; } values[valueIndex++] = _submitToEpa ? 1 : 0; values[valueIndex++] = 0; values[valueIndex++] = 0; //string insertValueString = StringUtils.Join("','", values); try { if (existingActivities.ContainsKey(activity.ActivityDescription.ActivityIdentifier) || addedActivities.ContainsKey(activity.ActivityDescription.ActivityIdentifier)) { // There is a cascade delete on Results: DoSimpleDelete(dbCommand, ACT_TABLE_NAME, ACT_PK_COLUMN, activity.ActivityDescription.ActivityIdentifier); IncUpdateCount(ACT_TABLE_NAME); } else { IncInsertCount(ACT_TABLE_NAME); } DoInsertWithValues(dbCommand, ACT_TABLE_NAME, ACT_UPDATE_COLUMNS, values); } catch (Exception) { throw; } addedActivities[activity.ActivityDescription.ActivityIdentifier] = activity.ActivityDescription.ActivityIdentifier; }); }
private void InsertResults(IDbCommand dbCommand, WQXDataType organization) { const string RSLT_UPDATE_COLUMNS = "ActivityIdentifier;DataLoggerLineName;SampleDateTime;ResultDetectionConditionText;CharacteristicName;ResultSampleFractionText;ResultMeasureValue;" + "ResultMeasureUnitCode;ResultStatusIdentifier;StatisticalBaseCode;ResultValueTypeName;ResultWeightBasisText;ResultTimeBasisText;ResultTemperatureBasisText;" + "ResultParticleSizeBasisText;ResultCommentText;BiologicalIntentName;BiologicalIndividualIdentifier;SubjectTaxonomicName;UnidentifiedSpeciesIdentifier;" + "SampleTissueAnatomyName;RGSCWMeasureValue;RGSCWMeasureUnitCode;ResAMMethodIdentifier;ResAMMethodIdentifierContext;ResAMMethodName;LaboratoryName;" + "AnalysisStartDate;AnalysisEndDate;ResultLaboratoryCommentCode;DetectionQuantitationLimitTypeName;RDQLMTMeasureValue;RDQLMTMeasureUnitCode;RLSPRPMethodIdentifier;" + "RLSPRPMethodIdentifierContext;RLSPRPMethodName;LastChangeDate;SubmitToEPA;SubmitToNWIFC;Delete"; const string RSLT_TABLE_NAME = "Result"; const string CHARACTERISTIC_TABLE_NAME = "Characteristic"; const string RESULT_STATUS_TABLE_NAME = "ResultStatus"; if (CollectionUtils.IsNullOrEmpty(organization.Organization.Activity)) { return; } List <ResultDataType> results = new List <ResultDataType>(128); CollectionUtils.ForEach(organization.Organization.Activity, delegate(ActivityDataType activity) { ExceptionUtils.ThrowIfNull(activity, "activity"); CollectionUtils.ForEach(activity.Result, delegate(ResultDataType result) { result.ParentId = activity.ActivityDescription.ActivityIdentifier; // Will be used below results.Add(result); }); }); if (results.Count == 0) { return; } int count = (int)_dao.DoJDBCExecuteScalar("SELECT COUNT(*) FROM " + CHARACTERISTIC_TABLE_NAME, null); Dictionary <string, string> characteristicNames = new Dictionary <string, string>(count); DoSimpleQueryWithRowCallbackDelegate(dbCommand, CHARACTERISTIC_TABLE_NAME, null, null, "CName", delegate(IDataReader reader) { string cName = reader.GetString(0); characteristicNames.Add(cName, cName); }); count = (int)_dao.DoJDBCExecuteScalar("SELECT COUNT(*) FROM " + RESULT_STATUS_TABLE_NAME, null); Dictionary <string, string> resultStatusIds = new Dictionary <string, string>(count); DoSimpleQueryWithRowCallbackDelegate(dbCommand, RESULT_STATUS_TABLE_NAME, null, null, "RSName", delegate(IDataReader reader) { string rsName = reader.GetString(0); resultStatusIds.Add(rsName, rsName); }); int maxColCount = RSLT_UPDATE_COLUMNS.Split(';').Length; object[] values = new object[maxColCount]; try { DoBulkInsert(dbCommand, RSLT_TABLE_NAME, RSLT_UPDATE_COLUMNS, delegate(int currentInsertIndex) { if (currentInsertIndex < results.Count) { ResultDataType result = results[currentInsertIndex]; int valueIndex = 0; values[valueIndex++] = result.ParentId; if (result.ResultDescription != null) { values[valueIndex++] = result.ResultDescription.DataLoggerLineName; values[valueIndex++] = null; // SampleDateTime? values[valueIndex++] = result.ResultDescription.ResultDetectionConditionText; if (string.IsNullOrEmpty(result.ResultDescription.CharacteristicName)) { throw new ArgException("A result is missing a CharacteristicName for Activity: {0}", result.ParentId); } if (!characteristicNames.ContainsKey(result.ResultDescription.CharacteristicName)) { throw new ArgException("A result has an unrecognized CharacteristicName: {0}", result.ResultDescription.CharacteristicName); } values[valueIndex++] = result.ResultDescription.CharacteristicName; values[valueIndex++] = result.ResultDescription.ResultSampleFractionText; if (result.ResultDescription.ResultMeasure != null) { values[valueIndex++] = result.ResultDescription.ResultMeasure.ResultMeasureValue; values[valueIndex++] = result.ResultDescription.ResultMeasure.MeasureUnitCode; } else { values[valueIndex++] = null; values[valueIndex++] = null; } if (string.IsNullOrEmpty(result.ResultDescription.ResultStatusIdentifier)) { throw new ArgException("A result is missing a ResultStatusIdentifier for Activity: {0}", result.ParentId); } if (!resultStatusIds.ContainsKey(result.ResultDescription.ResultStatusIdentifier)) { throw new ArgException("A result has an unrecognized ResultStatusIdentifier: {0}", result.ResultDescription.ResultStatusIdentifier); } values[valueIndex++] = result.ResultDescription.ResultStatusIdentifier; values[valueIndex++] = result.ResultDescription.StatisticalBaseCode; values[valueIndex++] = result.ResultDescription.ResultValueTypeName; values[valueIndex++] = result.ResultDescription.ResultWeightBasisText; values[valueIndex++] = result.ResultDescription.ResultTimeBasisText; values[valueIndex++] = result.ResultDescription.ResultTemperatureBasisText; values[valueIndex++] = result.ResultDescription.ResultParticleSizeBasisText; values[valueIndex++] = result.ResultDescription.ResultCommentText; } else { throw new ArgException("A result is missing a ResultDescription element"); } if (result.BiologicalResultDescription != null) { values[valueIndex++] = result.BiologicalResultDescription.BiologicalIntentName; values[valueIndex++] = result.BiologicalResultDescription.BiologicalIndividualIdentifier; values[valueIndex++] = result.BiologicalResultDescription.SubjectTaxonomicName; values[valueIndex++] = result.BiologicalResultDescription.UnidentifiedSpeciesIdentifier; values[valueIndex++] = result.BiologicalResultDescription.SampleTissueAnatomyName; if (result.BiologicalResultDescription.GroupSummaryCountWeight != null) { values[valueIndex++] = result.BiologicalResultDescription.GroupSummaryCountWeight.MeasureValue; values[valueIndex++] = result.BiologicalResultDescription.GroupSummaryCountWeight.MeasureUnitCode; } else { values[valueIndex++] = null; values[valueIndex++] = null; } } else { values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; } if (result.ResultAnalyticalMethod != null) { values[valueIndex++] = result.ResultAnalyticalMethod.MethodIdentifier; values[valueIndex++] = result.ResultAnalyticalMethod.MethodIdentifierContext; values[valueIndex++] = result.ResultAnalyticalMethod.MethodName; } else { values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; } if (result.ResultLabInformation != null) { values[valueIndex++] = result.ResultLabInformation.LaboratoryName; if (result.ResultLabInformation.AnalysisStartDateSpecified) { values[valueIndex++] = result.ResultLabInformation.AnalysisStartDate; } else { values[valueIndex++] = null; } if (result.ResultLabInformation.AnalysisEndDateSpecified) { values[valueIndex++] = result.ResultLabInformation.AnalysisEndDate; } else { values[valueIndex++] = null; } values[valueIndex++] = result.ResultLabInformation.ResultLaboratoryCommentCode; if (!CollectionUtils.IsNullOrEmpty(result.ResultLabInformation.ResultDetectionQuantitationLimit)) { DetectionQuantitationLimitDataType detectionQuantitationLimitDataType = result.ResultLabInformation.ResultDetectionQuantitationLimit[0]; values[valueIndex++] = detectionQuantitationLimitDataType.DetectionQuantitationLimitTypeName; if (detectionQuantitationLimitDataType.DetectionQuantitationLimitMeasure != null) { values[valueIndex++] = detectionQuantitationLimitDataType.DetectionQuantitationLimitMeasure.MeasureValue; values[valueIndex++] = detectionQuantitationLimitDataType.DetectionQuantitationLimitMeasure.MeasureUnitCode; } else { values[valueIndex++] = null; values[valueIndex++] = null; } } else { values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; } } else { values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; } if (!CollectionUtils.IsNullOrEmpty(result.LabSamplePreparation)) { LabSamplePreparationDataType labSamplePreparationDataType = result.LabSamplePreparation[0]; if (labSamplePreparationDataType.LabSamplePreparationMethod != null) { values[valueIndex++] = labSamplePreparationDataType.LabSamplePreparationMethod.MethodIdentifier; values[valueIndex++] = labSamplePreparationDataType.LabSamplePreparationMethod.MethodIdentifierContext; values[valueIndex++] = labSamplePreparationDataType.LabSamplePreparationMethod.MethodName; } else { values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; } } else { values[valueIndex++] = null; values[valueIndex++] = null; values[valueIndex++] = null; } if (_setLastUpdatedDateTime) { values[valueIndex++] = _lastUpdatedDateTime; } else { values[valueIndex++] = null; } values[valueIndex++] = _submitToEpa ? 1 : 0; values[valueIndex++] = 0; values[valueIndex++] = 0; IncInsertCount(RSLT_TABLE_NAME); return(values); } else { return(null); } }); } catch (Exception) { throw; } }