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