protected virtual string GetQuerySolicitResultsString(BeachDataSubmissionDataType data)
{
StringBuilder sb = new StringBuilder();
if (data == null)
{
sb.AppendFormat("Did not find any BEACHES data that matched the query parameters.");
}
else
{
sb.AppendFormat("Found the following BEACHES data that matched the query parameters: ");
int i = 0;
AppendCountString("Organization Details", data.OrganizationDetail, ++i == 1, sb);
AppendCountString("Beach Details", data.BeachDetail, ++i == 1, sb);
int beachActivityCount = 0;
CollectionUtils.ForEach(data.BeachDetail, delegate(BeachDetailDataType beachDetailDataType)
{
if (beachDetailDataType.BeachActivityDetail != null)
{
beachActivityCount += beachDetailDataType.BeachActivityDetail.Length;
}
});
sb.AppendFormat(", {0} {1}", beachActivityCount.ToString(), "Beach Activities");
}
return(sb.ToString());
}
protected virtual string GenerateSubmissionFile(BeachDataSubmissionDataType data)
{
ISettingsProvider settingsProvider;
GetServiceImplementation(out settingsProvider);
string tempXmlFilePath = settingsProvider.NewTempFilePath(".xml");
try
{
AppendAuditLogEvent("Generating submission file from results");
_serializationHelper.Serialize(data, tempXmlFilePath);
}
catch (Exception)
{
FileUtils.SafeDeleteFile(tempXmlFilePath);
throw;
}
return(tempXmlFilePath);
}
protected virtual string GenerateQuerySolicitFileAndAddToTransaction(BeachDataSubmissionDataType data)
{
string querySolicitFile = GenerateQuerySolicitFile(data);
try
{
AppendAuditLogEvent("Attaching submission document to transaction \"{0}\"",
_dataRequest.TransactionId);
_documentManager.AddDocument(_dataRequest.TransactionId,
CommonTransactionStatusCode.Completed,
null, querySolicitFile);
}
catch (Exception e)
{
AppendAuditLogEvent("Failed to attach submission document \"{0}\" to transaction \"{1}\" with exception: {2}",
querySolicitFile, _dataRequest.TransactionId, ExceptionUtils.ToShortString(e));
FileUtils.SafeDeleteFile(querySolicitFile);
throw;
}
return(querySolicitFile);
}
protected virtual string GenerateQuerySolicitFile(BeachDataSubmissionDataType data)
{
string tempXmlFilePath = _settingsProvider.NewTempFilePath(".xml");
string tempZipFilePath = _settingsProvider.NewTempFilePath(".zip");
try
{
AppendAuditLogEvent("Generating submission file from results");
_serializationHelper.Serialize(data, tempXmlFilePath);
_compressionHelper.CompressFile(tempXmlFilePath, tempZipFilePath);
}
catch (Exception)
{
FileUtils.SafeDeleteFile(tempZipFilePath);
throw;
}
finally
{
FileUtils.SafeDeleteFile(tempXmlFilePath);
}
return(tempZipFilePath);
}
protected override string DoProcessQuerySolicit(out CommonContentType contentType)
{
contentType = CommonContentType.ZIP;
Dictionary <string, DbAppendSelectWhereClause> selectClauses = new Dictionary <string, DbAppendSelectWhereClause>();
string beachActivityClause = null;
List <object> beachActivityParams = null;
string beachClause = null;
List <object> beachParams = null;
string beachActivityMonStationClause = null;
List <object> beachActivityMonStationParams = null;
string activityIndicatorClause = null;
List <object> activityIndicatorParams = null;
const string organizationAlias = "O";
const string beachAlias = "B";
const string beachActivityAlias = "BA";
const string beachActivityMonStationAlias = "BAMS";
const string activityIndicatorAlias = "AI";
if (_actualStartDate.HasValue)
{
beachActivityClause = AppendToDbClause(beachActivityClause, beachActivityAlias + "." + "ACTUALSTARTDATE >= ?");
beachActivityParams = CollectionUtils.Add(_actualStartDate.Value, beachActivityParams);
}
if (_actualStopDate.HasValue)
{
beachActivityClause = AppendToDbClause(beachActivityClause, beachActivityAlias + "." + "ACTUALSTOPDATE <= ?");
beachActivityParams = CollectionUtils.Add(_actualStopDate.Value, beachActivityParams);
}
if (!string.IsNullOrEmpty(_beachName))
{
beachClause = AppendToDbClause(beachClause, "UPPER(" + beachAlias + "." + "BEACHNAME) LIKE " + _baseDao.SqlConcat("'%'", "UPPER(?)", "'%'"));
beachParams = CollectionUtils.Add(_beachName, beachParams);
}
if (!string.IsNullOrEmpty(_beachIdentifier))
{
beachClause = AppendToDbClause(beachClause, "UPPER(" + beachAlias + "." + "BEACHIDENTIFIER) = UPPER(?)");
beachParams = CollectionUtils.Add(_beachIdentifier, beachParams);
}
if (!string.IsNullOrEmpty(_monitoringStationIdentifier))
{
beachActivityMonStationClause = AppendToDbClause(beachActivityMonStationClause, "UPPER(" + beachActivityMonStationAlias + "." + "MONITORINGSTATIONIDENTIFIER) = UPPER(?)");
beachActivityMonStationParams = CollectionUtils.Add(_monitoringStationIdentifier, beachActivityMonStationParams);
}
if (!string.IsNullOrEmpty(_beachAccessType))
{
beachClause = AppendToDbClause(beachClause, "UPPER(" + beachAlias + "." + "BEACHACCESSTYPE) = UPPER(?)");
beachParams = CollectionUtils.Add(_beachAccessType, beachParams);
}
if (!string.IsNullOrEmpty(_activityIndicatorType))
{
activityIndicatorClause = AppendToDbClause(activityIndicatorClause, "UPPER(" + activityIndicatorAlias + "." + "INDICATORTYPE) = UPPER(?)");
activityIndicatorParams = CollectionUtils.Add(_activityIndicatorType, activityIndicatorParams);
}
if (_activeAdvisoryIndicator.HasValue)
{
if (_activeAdvisoryIndicator.Value)
{
beachActivityClause = AppendToDbClause(beachActivityClause, beachActivityAlias + "." + "ACTUALSTOPDATE IS NULL");
}
}
if (beachActivityMonStationClause != null)
{
selectClauses.Add("NOTIF_BEACHACTIVITYMONSTATION", new DbAppendSelectWhereClause(beachActivityMonStationAlias, _baseDao, beachActivityMonStationClause, beachActivityMonStationParams));
beachActivityClause = AppendToDbClause(beachActivityClause,
string.Format("{2}.ID IN (SELECT {1}.ACTIVITY_ID FROM NOTIF_BEACHACTIVITYMONSTATION {1} WHERE {0})",
beachActivityMonStationClause, beachActivityMonStationAlias, beachActivityAlias));
CollectionUtils.AddRange(beachActivityMonStationParams, ref beachActivityParams);
}
if (activityIndicatorClause != null)
{
selectClauses.Add("NOTIF_ACTIVITYINDICATOR", new DbAppendSelectWhereClause(activityIndicatorAlias, _baseDao, activityIndicatorClause, activityIndicatorParams));
beachActivityClause = AppendToDbClause(beachActivityClause,
string.Format("{2}.ID IN (SELECT {1}.ACTIVITY_ID FROM NOTIF_ACTIVITYINDICATOR {1} WHERE {0})",
activityIndicatorClause, activityIndicatorAlias, beachActivityAlias));
CollectionUtils.AddRange(activityIndicatorParams, ref beachActivityParams);
}
if (beachActivityClause != null)
{
selectClauses.Add("NOTIF_BEACHACTIVITY", new DbAppendSelectWhereClause(beachActivityAlias, _baseDao, beachActivityClause, beachActivityParams));
beachClause = AppendToDbClause(beachClause, string.Format("{2}.ID IN (SELECT {1}.BEACH_ID FROM NOTIF_BEACHACTIVITY {1} WHERE {0})",
beachActivityClause, beachActivityAlias, beachAlias));
CollectionUtils.AddRange(beachActivityParams, ref beachParams);
}
if (beachClause != null)
{
selectClauses.Add("NOTIF_BEACH", new DbAppendSelectWhereClause(beachAlias, _baseDao, beachClause, beachParams));
}
AppendAuditLogEvent("Querying database for BEACHES data ...");
List <BeachDetailDataType> beachDetails = _objectsFromDatabase.LoadFromDatabase <BeachDetailDataType>(_baseDao, selectClauses);
List <OrganizationDetailDataType> organizationDetails = null;
if (!CollectionUtils.IsNullOrEmpty(beachDetails))
{
string organizationClause;
if (beachClause != null)
{
organizationClause =
string.Format("({2}.ID IN (SELECT DISTINCT OBR.ORGANIZATION_ID FROM NOTIF_ORGANIZATIONBEACHROLE OBR WHERE OBR.BEACH_ID IN (SELECT {1}.ID FROM NOTIF_BEACH {1} WHERE {0})))",
beachClause, beachAlias, organizationAlias);
selectClauses.Add("NOTIF_ORGANIZATION", new DbAppendSelectWhereClause(organizationAlias, _baseDao, organizationClause, beachParams));
}
else
{
organizationClause =
string.Format("({0}.ID IN (SELECT DISTINCT OBR.ORGANIZATION_ID FROM NOTIF_ORGANIZATIONBEACHROLE OBR))",
organizationAlias);
}
organizationDetails = _objectsFromDatabase.LoadFromDatabase <OrganizationDetailDataType>(_baseDao, selectClauses);
}
BeachDataSubmissionDataType data = new BeachDataSubmissionDataType();
data.BeachDetail = CollectionUtils.IsNullOrEmpty(beachDetails) ? null : beachDetails.ToArray();
data.OrganizationDetail = CollectionUtils.IsNullOrEmpty(organizationDetails) ? null : organizationDetails.ToArray();
AppendAuditLogEvent(GetQuerySolicitResultsString(data));
data.AfterLoadFromDatabase();
string submissionFile = GenerateQuerySolicitFileAndAddToTransaction(data);
return(submissionFile);
}
protected virtual void PerformSubmission()
{
var beachActivitySelect = "(SENTTOEPA IS NULL OR SENTTOEPA <> 'Y') AND (ACTUALSTOPDATE IS NOT NULL)";
List <object> beachActivitySelectParams = null;
Dictionary <string, DbAppendSelectWhereClause> selectClauses = new Dictionary <string, DbAppendSelectWhereClause>();
if (_lastUpdateDate.HasValue)
{
AppendAuditLogEvent("Using last update date of \"{0}\" to build query...",
_lastUpdateDate.Value.ToString());
beachActivitySelect += " AND (NOTIFUPDATEDATE >= ?)";
beachActivitySelectParams = new List <object>(new object[] { _lastUpdateDate.Value });
}
selectClauses.Add("NOTIF_BEACHACTIVITY", (beachActivitySelectParams == null) ? new DbAppendSelectWhereClause(_baseDao, beachActivitySelect) :
new DbAppendSelectWhereClause(_baseDao, beachActivitySelect, beachActivitySelectParams));
//if (_onlySendBeachesWithActivities)
//{
// var beachSelect = "ID IN (SELECT DISTINCT BEACH_ID FROM NOTIF_BEACHACTIVITY WHERE " + beachActivitySelect + ")";
// selectClauses.Add("NOTIF_BEACH", (beachActivitySelectParams == null) ? new DbAppendSelectWhereClause(_baseDao, beachSelect) :
// new DbAppendSelectWhereClause(_baseDao, beachSelect, beachActivitySelectParams));
// var beachProcedureSelect = "BEACH_ID IN (SELECT DISTINCT BEACH_ID FROM NOTIF_BEACHACTIVITY WHERE " + beachActivitySelect + ")";
// selectClauses.Add("NOTIF_BEACHPROCEDURE", (beachActivitySelectParams == null) ? new DbAppendSelectWhereClause(_baseDao, beachProcedureSelect) :
// new DbAppendSelectWhereClause(_baseDao, beachProcedureSelect, beachActivitySelectParams));
// var procedureSelect = "ID IN (SELECT DISTINCT PROCEDURE_ID FROM NOTIF_BEACHPROCEDURE WHERE " + beachProcedureSelect + ")";
// selectClauses.Add("NOTIF_PROCEDURE", (beachActivitySelectParams == null) ? new DbAppendSelectWhereClause(_baseDao, procedureSelect) :
// new DbAppendSelectWhereClause(_baseDao, procedureSelect, beachActivitySelectParams));
//}
AppendAuditLogEvent("Querying database for BEACHES data ...");
List <OrganizationDetailDataType> organizationDetails = _objectsFromDatabase.LoadFromDatabase <OrganizationDetailDataType>(_baseDao, null);
List <BeachDetailDataType> beachDetails = _objectsFromDatabase.LoadFromDatabase <BeachDetailDataType>(_baseDao, selectClauses);
List <BeachProcedureDetailDataType> beachProcedureDetails = _objectsFromDatabase.LoadFromDatabase <BeachProcedureDetailDataType>(_baseDao, selectClauses);
List <YearCompletionIndicatorDataType> yearCompletionIndicators = _objectsFromDatabase.LoadFromDatabase <YearCompletionIndicatorDataType>(_baseDao, null);
if (!CollectionUtils.IsNullOrEmpty(yearCompletionIndicators) && (yearCompletionIndicators.Count > 1))
{
throw new ArgumentException(string.Format("More than one \"Year Completion Indicator\" was found to submit"));
}
BeachDataSubmissionDataType data = new BeachDataSubmissionDataType();
data.YearCompletionIndicators = CollectionUtils.IsNullOrEmpty(yearCompletionIndicators) ? null : yearCompletionIndicators[0];
data.BeachDetail = CollectionUtils.IsNullOrEmpty(beachDetails) ? null : beachDetails.ToArray();
data.BeachProcedureDetail = CollectionUtils.IsNullOrEmpty(beachProcedureDetails) ? null : beachProcedureDetails.ToArray();
data.OrganizationDetail = CollectionUtils.IsNullOrEmpty(organizationDetails) ? null : organizationDetails.ToArray();
AppendAuditLogEvent(GetSubmissionResultsString(data));
data.AfterLoadFromDatabase();
string submissionFile = GenerateSubmissionFileAndAddToTransaction(data);
ProcessSubmissionFile(submissionFile);
if (_updateSentToEpaFlag)
{
int rowsSet;
if (beachActivitySelectParams == null)
{
rowsSet = _baseDao.AdoTemplate.ExecuteNonQuery(CommandType.Text, "UPDATE NOTIF_BEACHACTIVITY SET SENTTOEPA = 'Y' WHERE " + beachActivitySelect);
}
else
{
IDbParameters parameters;
var selectWhereQuery = _baseDao.LoadGenericParametersFromValueList(beachActivitySelect, out parameters, beachActivitySelectParams);
rowsSet = _baseDao.AdoTemplate.ExecuteNonQuery(CommandType.Text, "UPDATE NOTIF_BEACHACTIVITY SET SENTTOEPA = 'Y' WHERE " + selectWhereQuery,
parameters);
}
AppendAuditLogEvent("Set {0} NOTIF_BEACHACTIVITY.SENTTOEPA columns to 'Y'", rowsSet.ToString());
}
else
{
AppendAuditLogEvent("{0} param is false, did not update any NOTIF_BEACHACTIVITY.SENTTOEPA columns", PARAM_UPDATE_SENT_TO_EPA_FLAG);
}
}
