Exemple #1
0
        private static IList <string> GetImportFiles(string importZipFile, string importCSVFileSearchPattern,
                                                     string tempFolderPath, ICompressionHelper compressionHelper)
        {
            if (string.IsNullOrEmpty(tempFolderPath))
            {
                tempFolderPath = Path.GetTempPath();
            }
            if (!Directory.Exists(tempFolderPath))
            {
                throw new DirectoryNotFoundException(string.Format("The node temporary folder does not exist: {0}",
                                                                   tempFolderPath));
            }
            string outputPath = Path.Combine(tempFolderPath, Guid.NewGuid().ToString());

            Directory.CreateDirectory(outputPath);
            try
            {
                compressionHelper.UncompressDirectory(importZipFile, outputPath);
                string[] fileList = Directory.GetFiles(outputPath, importCSVFileSearchPattern);
                if (CollectionUtils.IsNullOrEmpty(fileList))
                {
                    throw new FileNotFoundException(string.Format("No valid CSV import files were included in the input zip file \"{0}\"",
                                                                  importZipFile));
                }
                return(fileList);
            }
            catch (Exception)
            {
                FileUtils.SafeDeleteDirectory(outputPath);
                throw;
            }
        }
        internal string Execute(DateTime changeDate, string targetXmlPath, ISerializationHelper serializationHelper,
                                ICompressionHelper compressionHelper, HEREBaseService hereBaseService)
        {
            hereBaseService.AppendAuditLogEvent("Getting data.");

            CAFO ds = _dbService.GetCAFOAddData(changeDate);

            hereBaseService.AppendAuditLogEvent("Data retrieved. (Record Count = {0}).", ds.CAFO_FAC.Rows.Count);

            hereBaseService.AppendAuditLogEvent("Transforming results.");

            CAFOFacilityList facs = CAFOTransform.Transform(ds);

            if (facs == null || facs.CAFOFacilities == null || facs.CAFOFacilities.Count == 0)
            {
                return(null);
            }
            else
            {
                hereBaseService.AppendAuditLogEvent("Results transformed: (Record Count: {0})", facs.CAFOFacilities.Count);

                hereBaseService.AppendAuditLogEvent(
                    "Serializing transformed results to file (File = {0}).",
                    targetXmlPath);

                serializationHelper.Serialize(facs, targetXmlPath);

                return(compressionHelper.CompressFile(targetXmlPath));
            }
        }
        internal string Execute(DateTime changeDate, string targetXmlPath, ISerializationHelper serializationHelper,
                                ICompressionHelper compressionHelper, HEREBaseService hereBaseService)
        {
            hereBaseService.AppendAuditLogEvent("Getting data.");

            HERE10.DomainDataSet ds = _dbService.GetDomainData(changeDate);

            hereBaseService.AppendAuditLogEvent("Data retrieved. (List Count = {0}).", ds.List.Rows.Count);

            hereBaseService.AppendAuditLogEvent("Transforming results.");

            List <HERE10.DomainList> lists = new List <HERE10.DomainList>();

            foreach (HERE10.DomainDataSet.ListRow listRow in ds.List.Rows)
            {
                HERE10.DomainList item = new HERE10.DomainList();

                item.DomainListNameText     = listRow.ListName;
                item.OriginatingPartnerName = listRow.ListOwnerName;

                List <DomainValueItemDataType> listItems = new List <DomainValueItemDataType>();

                foreach (HERE10.DomainDataSet.ListItemRow listItemRow in listRow.GetListItemRows())
                {
                    DomainValueItemDataType listItem = new DomainValueItemDataType();

                    listItem.ItemCode            = listItemRow.ItemCode;
                    listItem.ItemText            = listItemRow.ItemValue;
                    listItem.ItemDescriptionText = listItemRow.ItemDesc;

                    listItems.Add(listItem);
                }

                item.DomainListItem = listItems.ToArray();

                lists.Add(item);
            }

            if (lists.Count == 0)
            {
                return(null);
            }
            else
            {
                HERE10.DomainValueListDataType domain = new HERE10.DomainValueListDataType();
                domain.DomainList = lists.ToArray();

                hereBaseService.AppendAuditLogEvent("Results transformed.");

                hereBaseService.AppendAuditLogEvent(
                    "Serializing transformed results to file (File = {0}).",
                    targetXmlPath);

                serializationHelper.Serialize(domain, targetXmlPath);

                return(compressionHelper.CompressFile(targetXmlPath));
            }
        }
        public static WQXDataType GenerateWqxObjectsFromSubmissionFile(IAppendAuditLogEvent appendAuditLogEvent, string submissionFilePath,
                                                                       string sysTempFolderPath, ISerializationHelper serializationHelper,
                                                                       ICompressionHelper compressionHelper, out string attachmentsFolderPath)
        {
            string validationErrorsFile;

            return(GenerateWqxObjectsFromSubmissionFile(appendAuditLogEvent, submissionFilePath, sysTempFolderPath, null, null, null,
                                                        serializationHelper, compressionHelper, out attachmentsFolderPath,
                                                        out validationErrorsFile));
        }
Exemple #5
0
        internal string Execute(DateTime changeDate, string flowName, string targetXmlPath,
                                ISerializationHelper serializationHelper, ICompressionHelper compressionHelper,
                                IObjectsFromDatabase objectsFromDatabase,
                                IAppendAuditLogEvent appendAuditLogEvent)
        {
            appendAuditLogEvent.AppendAuditLogEvent("Querying TANKS data ...");

            string whereQuery =
                string.Format("FAC_SITE_IDEN IN (SELECT ST_FAC_IND FROM CHANGED_FACILITIES WHERE IS_DELETED = 0 AND UPPER(FLOW_TYPE) = UPPER('{0}') AND UPDATE_DATE >= '{1}')",
                              flowName, changeDate.ToString("dd-MMM-yyyy").ToUpper());

            Dictionary <string, DbAppendSelectWhereClause> selectClauses = new Dictionary <string, DbAppendSelectWhereClause>();

            selectClauses.Add("TANKS_FAC_SITE", new DbAppendSelectWhereClause(whereQuery, null));
            List <TanksSubmissionDataType> dataList =
                objectsFromDatabase.LoadFromDatabase <TanksSubmissionDataType>(_baseDao, selectClauses);

            TanksSubmissionDataType data;

            if (CollectionUtils.IsNullOrEmpty(dataList))
            {
                appendAuditLogEvent.AppendAuditLogEvent("Did not find any TANKS data in database.");
                return(null);
            }
            else if (dataList.Count > 1)
            {
                throw new InvalidOperationException("More than one set of TANKS data was found in database.");
            }
            else
            {
                data = dataList[0];
                if (CollectionUtils.IsNullOrEmpty(data.TanksFacilitySite))
                {
                    appendAuditLogEvent.AppendAuditLogEvent("Did not find any tanks facilities.");
                    return(null);
                }
                else
                {
                    appendAuditLogEvent.AppendAuditLogEvent("Found {0} facilities and {1} unique tanks.",
                                                            data.TanksFacilitySite.Length,
                                                            GetUniqueTankCount(data));
                }
            }

            appendAuditLogEvent.AppendAuditLogEvent(
                "Serializing transformed results to file (File = {0}).",
                targetXmlPath);

            serializationHelper.Serialize(data, targetXmlPath);

            return(compressionHelper.CompressFile(targetXmlPath));
        }
        internal string Execute(DateTime changeDate, string targetXmlPath, ISerializationHelper serializationHelper,
                                ICompressionHelper compressionHelper, HEREBaseService hereBaseService)
        {
            hereBaseService.AppendAuditLogEvent("Getting data.");

            Tier2DataSet ds = _dbService.GetTier2DataSet(changeDate);

            hereBaseService.AppendAuditLogEvent("Data retrieved. (Record Count = {0}).",
                                                ds.T2_SUBMISSION.Rows.Count);

            hereBaseService.AppendAuditLogEvent("Transforming results.");

            TierIIDataType facs = Tier2Transform.Transform(ds);

            if (facs == null || facs.Submission == null || facs.Submission.Length == 0)
            {
                return(null);
            }
            else
            {
                hereBaseService.AppendAuditLogEvent("Results transformed. (Record Count = {0}).",
                                                    facs.Submission.Length);

                hereBaseService.AppendAuditLogEvent("Serializing transformed results to file (File = {0}).",
                                                    targetXmlPath);

                serializationHelper.Serialize(facs, targetXmlPath);

                hereBaseService.AppendAuditLogEvent("Transformed results Serialized.");

                if (compressionHelper != null)
                {
                    return(compressionHelper.CompressFile(targetXmlPath));
                }
                else
                {
                    return(targetXmlPath);
                }
            }
        }
 /// <summary>
 /// 设置压缩解压缩辅助器。
 /// </summary>
 /// <param name="compressionHelper">要设置的压缩解压缩辅助器。</param>
 public static void SetCompressionHelper(ICompressionHelper compressionHelper)
 {
     s_CompressionHelper = compressionHelper;
 }
Exemple #8
0
        public static string ProcessImportFile(string importFolder, string archiveFolder, string errorFolder,
                                               string importCSVFileSearchPattern, string importFileSearchToDBTableNamePattern,
                                               string cleanupTablesStoredProcName, string endProcessingStoredProcName,
                                               SpringBaseDao baseDao, string tempFolderPath,
                                               ICompressionHelper compressionHelper, out bool success, out bool foundFile)
        {
            StringBuilder statusString  = new StringBuilder();
            string        tempOutputDir = null;

            success   = false;
            foundFile = false;
            string importZipFile = null;

            try
            {
                ValidateFolder(importFolder, false);
                ValidateFolder(archiveFolder, true);
                ValidateFolder(errorFolder, true);

                importZipFile = FileUtils.GetOldestFile(importFolder, "*.zip", false);
                if (string.IsNullOrEmpty(importZipFile))
                {
                    OutputStatus(statusString, "Did not find any files to process within the import folder \"{0}\", stopping thread execution ...",
                                 importFolder);
                    success = true;
                    return(statusString.ToString());
                }
                foundFile = true;

                OutputStatus(statusString, "Extracting import files from the zip file \"{0}\"", importZipFile);
                IList <string> importCSVFiles = GetImportFiles(importZipFile, importCSVFileSearchPattern, tempFolderPath,
                                                               compressionHelper);
                OutputStatus(statusString, "Extracted {0} import files", importCSVFiles.Count.ToString());
                tempOutputDir = Path.GetDirectoryName(importCSVFiles[0]);

                CallStoredProc(cleanupTablesStoredProcName, baseDao, statusString);

                baseDao.TransactionTemplate.Execute(delegate
                {
                    foreach (string importFilePath in importCSVFiles)
                    {
                        string fileName = Path.GetFileName(importFilePath);
                        OutputStatus(statusString, "Processing import file \"{0}\"", fileName);
                        string dbTableName = GetDBTableName(importFilePath, importCSVFileSearchPattern, importFileSearchToDBTableNamePattern);
                        OutputStatus(statusString, "Determined DB table name of \"{0}\" for input file \"{1}\"",
                                     dbTableName, fileName);
                        using (TextFieldParser textFieldParser = new TextFieldParser(importFilePath))
                        {
                            textFieldParser.TextFieldType = FieldType.Delimited;
                            textFieldParser.Delimiters    = new string[] { COMMA };
                            string[] columnNames          = textFieldParser.ReadFields();
                            if (CollectionUtils.IsNullOrEmpty(columnNames))
                            {
                                throw new FileNotFoundException(string.Format("No column names were found in the CSV file \"{0}\"",
                                                                              fileName));
                            }
                            string semicolonSeparatedColumnNames = string.Join(SEMICOLON, columnNames);
                            OutputStatus(statusString, "Determined {0} insert column names as \"{1}\" for DB table \"{2}\"",
                                         columnNames.Length.ToString(), semicolonSeparatedColumnNames,
                                         dbTableName);
                            object[] insertValues = new object[columnNames.Length];
                            int totalRows         = 0;
                            baseDao.DoBulkInsert(dbTableName, semicolonSeparatedColumnNames,
                                                 delegate(int currentInsertIndex)
                            {
                                if (textFieldParser.EndOfData)
                                {
                                    return(null);
                                }
                                string[] columnValues;
                                try
                                {
                                    columnValues = textFieldParser.ReadFields();
                                    if (columnValues.Length != insertValues.Length)
                                    {
                                        throw new InvalidDataException(string.Format("The CSV file \"{0}\" does not contain a valid number of column values at row {1}: {2} values expected and {3} values found",
                                                                                     fileName, (currentInsertIndex + 1).ToString(),
                                                                                     insertValues.Length.ToString(), columnValues.Length.ToString()));
                                    }
                                }
                                catch (MalformedLineException)
                                {
                                    // This may be caused by single quotes inside the value instead of double quotes.
                                    // Try to fix up here:
                                    columnValues = textFieldParser.ErrorLine.Split(',');
                                    if (CollectionUtils.IsNullOrEmpty(columnValues) ||
                                        (columnValues.Length != insertValues.Length))
                                    {
                                        throw;
                                    }
                                    for (int j = 0; j < columnValues.Length; ++j)
                                    {
                                        string curValue = columnValues[j];
                                        if ((curValue.Length > 1) && (curValue[0] == '\"') &&
                                            (curValue[curValue.Length - 1] == '\"'))
                                        {
                                            columnValues[j] = curValue.Substring(1, curValue.Length - 2);
                                        }
                                    }
                                }
                                for (int i = 0; i < insertValues.Length; ++i)
                                {
                                    insertValues[i] = columnValues[i];
                                }
                                totalRows++;
                                return(insertValues);
                            });
                            OutputStatus(statusString, "Inserted {0} rows into DB table \"{1}\"",
                                         totalRows.ToString(), dbTableName);
                        }
                    }
                    return(null);
                });

                CallStoredProc(endProcessingStoredProcName, baseDao, statusString);

                // Finally, move the import zip file to the archive folder
                if (!string.IsNullOrEmpty(archiveFolder))
                {
                    string newFilePath = FileUtils.MoveFileToFolderAppendDateTimeTicksToName(importZipFile, archiveFolder);
                    OutputStatus(statusString, "Moved import file \"{0}\" to \"{1}\"",
                                 importZipFile, newFilePath);
                }
                success = true;
                OutputStatus(statusString, "Successfully finished processing {0} import files",
                             importCSVFiles.Count);
            }
            catch (Exception ex)
            {
                OutputStatus(statusString, "ERROR ********************* An error occurred processing the import files: {0}",
                             ExceptionUtils.ToLongString(ex));
            }
            finally
            {
                if (tempOutputDir != null)
                {
                    FileUtils.SafeDeleteDirectory(tempOutputDir);
                }
                // Finally, move the import zip file to the archive folder
                if (!success && (importZipFile != null) && !string.IsNullOrEmpty(errorFolder))
                {
                    try
                    {
                        string newFilePath = FileUtils.MoveFileToFolderAppendDateTimeTicksToName(importZipFile, errorFolder);
                        OutputStatus(statusString, "Moved import file \"{0}\" to \"{1}\"",
                                     importZipFile, newFilePath);
                    }
                    catch (Exception ex3)
                    {
                        OutputStatus(statusString, "Failed to move import file \"{0}\" to folder \"{1}\": {2}",
                                     importZipFile, errorFolder, ExceptionUtils.ToLongString(ex3));
                    }
                }
            }
            return(statusString.ToString());
        }
        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 GenerateAndValidateWqxQueryFile(IAppendAuditLogEvent appendAuditLogEvent, IObjectsFromDatabase objectsFromDatabase, SpringBaseDao baseDao,
                                                      string queryOrganizationName, string queryOrganizationIdentifier, string naasUserName, string sysTempFolderPath,
                                                      Assembly xmlSchemaZippedResourceAssembly, string xmlSchemaZippedQualifiedResourceName,
                                                      string xmlSchemaRootFileName, ISerializationHelper serializationHelper, ICompressionHelper compressionHelper,
                                                      out string validationErrorsFile)
 {
     // Jaime had an additional "string naasUserName" parameter to this method, but he did not check in code, so not sure what it does.
     return(GenerateAndValidateWqxQueryFile(appendAuditLogEvent, objectsFromDatabase, baseDao, queryOrganizationName, queryOrganizationIdentifier, sysTempFolderPath,
                                            xmlSchemaZippedResourceAssembly, xmlSchemaZippedQualifiedResourceName, xmlSchemaRootFileName, serializationHelper,
                                            compressionHelper, out validationErrorsFile));
 }
        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);
        }
Exemple #12
0
        internal string Execute(DateTime changeDate, string flowName, string targetXmlPath,
                                ISerializationHelper serializationHelper, ICompressionHelper compressionHelper,
                                IObjectsFromDatabase objectsFromDatabase,
                                IAppendAuditLogEvent appendAuditLogEvent)
        {
            appendAuditLogEvent.AppendAuditLogEvent("Querying FACID data ...");

            Dictionary <string, DbAppendSelectWhereClause> selectClauses = new Dictionary <string, DbAppendSelectWhereClause>();
            string facIdQuery = string.Format("SELECT ST_FAC_IND FROM CHANGED_FACILITIES WHERE IS_DELETED = 0 AND UPPER(FLOW_TYPE) = UPPER('{0}') AND UPDATE_DATE >= '{1}'",
                                              flowName, changeDate.ToString("dd-MMM-yyyy").ToUpper());
            string whereQuery = string.Format("FAC_SITE_IDEN_VAL IN ({0})", facIdQuery);

            selectClauses.Add("FACID_FAC", new DbAppendSelectWhereClause(whereQuery, null));

            string affiliateTableClause = string.Format(
                "AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_FAC_FAC_AFFL WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0}) UNION " +
                "SELECT AFFL_IDEN FROM FACID_ENVR_INTR_FAC_AFFL WHERE ENVR_INTR_ID IN (SELECT ENVR_INTR_ID FROM FACID_ENVR_INTR WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0})))",
                whereQuery);

            //string affiliateTableClause = string.Format("(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_FAC_FAC_AFFL WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0}))) OR " +
            //                                            "(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_ENVR_INTR_FAC_AFFL WHERE ENVR_INTR_ID IN (SELECT ENVR_INTR_ID FROM FACID_ENVR_INTR WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0}))))",
            //                                            whereQuery);
            //string affiliateTableClause = string.Format("(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_FAC_FAC_AFFL WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0})))",
            //                                            whereQuery);
            //string affiliateTableClause = string.Format("(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_ENVR_INTR_FAC_AFFL WHERE ENVR_INTR_ID IN (SELECT ENVR_INTR_ID FROM FACID_ENVR_INTR WHERE FAC_ID IN (SELECT FAC_ID FROM FACID_FAC WHERE {0}))))",
            //                                            whereQuery);

            //string affiliateTableClause = string.Format("(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_FAC_FAC_AFFL WHERE FAC_ID IN ({0}))) OR " +
            //                                            "(AFFL_IDEN IN (SELECT AFFL_IDEN FROM FACID_ENVR_INTR_FAC_AFFL WHERE ENVR_INTR_ID IN (SELECT ENVR_INTR_ID FROM FACID_ENVR_INTR WHERE FAC_ID IN ({0}))))",
            //                                            facIdQuery);
            selectClauses.Add("FACID_AFFL", new DbAppendSelectWhereClause(affiliateTableClause, null));
            List <FacilityDetailsDataType> dataList =
                objectsFromDatabase.LoadFromDatabase <FacilityDetailsDataType>(_baseDao, selectClauses);

            FacilityDetailsDataType data = FacIdHelper.CombineFacilityDetailsQueryResults(dataList);

#if DEBUG
            {
            }
#endif // DEBUG

            if (CollectionUtils.IsNullOrEmpty(data.FacilityList))
            {
                appendAuditLogEvent.AppendAuditLogEvent("Did not find any FACID data in database.");
                return(null);
            }
            else
            {
                appendAuditLogEvent.AppendAuditLogEvent("Found {0} facilities and {1} affiliates.",
                                                        data.FacilityList.Length,
                                                        (data.AffiliateList != null) ? data.AffiliateList.Length : 0);
            }

            appendAuditLogEvent.AppendAuditLogEvent(
                "Serializing transformed results to file (File = {0}).",
                targetXmlPath);

            serializationHelper.Serialize(data, targetXmlPath);

            return(compressionHelper.CompressFile(targetXmlPath));
        }