private void ExportJoins(string tableAlias, ref List<ExportField> exportFields, out DataTable exportTable,
out int[][] fieldMapTemplate, out StringBuilder targetList, out StringBuilder fromClause, out int[] sortOrdinals,
out int[] matrixOrdinals, out int[] formationOrdinals, out int[] managementOrdinals, out int[] complexOrdinals,
out int[] bapOrdinals, out int[] sourceOrdinals)
{
exportTable = new DataTable("HluExport");
targetList = new StringBuilder();
List<string> fromList = new List<string>();
List<string> leftJoined = new List<string>();
fromClause = new StringBuilder();
sortOrdinals = null;
matrixOrdinals = null;
formationOrdinals = null;
managementOrdinals = null;
complexOrdinals = null;
bapOrdinals = null;
sourceOrdinals = null;
int tableAliasNum = 1;
bool firstJoin = true;
_lastTableName = null;
_fieldCount = 0;
int fieldLength = 0;
_attributesLength = 0;
_tableCount = 0;
//
foreach (HluDataSet.exports_fieldsRow r in
_viewModelMain.HluDataset.exports_fields.OrderBy(r => r.field_ordinal))
{
// Get the field length of the source table/column.
fieldLength = GetFieldLength(r.table_name, r.column_ordinal);
//---------------------------------------------------------------------
// FIX: 043 Enable new 'empty' fields to be included in exports.
if (r.table_name.ToLower() == "<none>")
{
// Override the source field length(s) if an export
// field length has been set.
if (!r.IsNull(_viewModelMain.HluDataset.exports_fields.field_lengthColumn) &&
r.field_length > 0)
fieldLength = r.field_length;
AddExportColumn(0, r.table_name, r.column_name, r.field_name,
r.field_type, fieldLength, !r.IsNull(_viewModelMain.HluDataset.exports_fields.field_formatColumn) ? r.field_format : null,
ref exportFields);
continue;
}
//---------------------------------------------------------------------
// Determine if this field is to be output multiple times,
// once for each row in the relevant table up to the
// maximum fields_count value.
bool multipleFields = false;
if (!r.IsNull(_viewModelMain.HluDataset.exports_fields.fields_countColumn))
multipleFields = true;
// Add the required table to the list of sql tables in
// the from clause.
string currTable = _viewModelMain.DataBase.QualifyTableName(r.table_name);
if (!fromList.Contains(currTable))
{
fromList.Add(currTable);
var incidRelation = _viewModelMain.HluDataset.incid.ChildRelations.Cast<DataRelation>()
.Where(dr => dr.ChildTable.TableName == r.table_name);
if (incidRelation.Count() == 0)
{
fromClause.Append(currTable);
}
else
{
DataRelation incidRel = incidRelation.ElementAt(0);
if (firstJoin)
firstJoin = false;
else
fromClause.Insert(0, "(").Append(")");
fromClause.Append(RelationJoinClause("LEFT", currTable, true,
_viewModelMain.DataBase.QuoteIdentifier(
incidRel.ParentTable.TableName), incidRel, fromList));
leftJoined.Add(currTable);
}
}
// Get the relationships for the table/column if a
// value from a lookup table is required.
string fieldFormat = !r.IsNull(_viewModelMain.HluDataset.exports_fields.field_formatColumn) ? r.field_format : null;
//---------------------------------------------------------------------
// CHANGED: CR14 (Exporting IHS codes or descriptions)
// Enable users to specify if individual fields should be
// exported with descriptions in the exports_fields table.
//
var relations = ((fieldFormat != null) && (fieldFormat.ToLower() == "both" || fieldFormat.ToLower() == "lookup")) ? _viewModelMain.HluDataRelations.Where(rel =>
rel.ChildTable.TableName == r.table_name && rel.ChildColumns
.Count(ch => ch.ColumnName == r.column_name) == 1) : new DataRelation[0];
//---------------------------------------------------------------------
switch (relations.Count())
{
case 0: // If this field does not have any related lookup tables.
// Add the field to the sql target list.
targetList.Append(String.Format(",{0}.{1} AS {2}", currTable,
_viewModelMain.DataBase.QuoteIdentifier(r.column_name), r.field_name.Replace("<no>", "")));
//---------------------------------------------------------------------
// FIX: 044 Enable text field lengths to be specified in
// the export format.
//
// Override the source field length(s) if an export
// field length has been set.
if (!r.IsNull(_viewModelMain.HluDataset.exports_fields.field_lengthColumn) &&
r.field_length > 0)
fieldLength = r.field_length;
//---------------------------------------------------------------------
// Add the field to the sql list of export table columns.
AddExportColumn(multipleFields ? r.fields_count : 0, r.table_name, r.column_name, r.field_name,
r.field_type, fieldLength,
!r.IsNull(_viewModelMain.HluDataset.exports_fields.field_formatColumn) ? r.field_format : String.Empty,
ref exportFields);
break;
case 1: // If this field has a related lookup table.
DataRelation lutRelation = relations.ElementAt(0);
string parentTable = _viewModelMain.DataBase.QualifyTableName(lutRelation.ParentTable.TableName);
string parentTableAlias = tableAlias + tableAliasNum++;
fromList.Add(parentTable);
// Determine the related lookup table field name and
// field ordinal.
string lutFieldName;
int lutFieldOrdinal;
if ((r.table_name == _viewModelMain.HluDataset.incid_sources.TableName) && (Regex.IsMatch(r.column_name, @"(_id)", RegexOptions.IgnoreCase)))
{
lutFieldName = ViewModelWindowMain.LutSourceFieldName;
lutFieldOrdinal = ViewModelWindowMain.LutSourceFieldOrdinal - 1;
}
else if ((r.table_name == _viewModelMain.HluDataset.incid.TableName) && (Regex.IsMatch(r.column_name, @"(_user_id)", RegexOptions.IgnoreCase)))
{
lutFieldName = ViewModelWindowMain.LutUserFieldName;
lutFieldOrdinal = ViewModelWindowMain.LutUserFieldOrdinal - 1;
}
else
{
lutFieldName = ViewModelWindowMain.LutDescriptionFieldName;
lutFieldOrdinal = ViewModelWindowMain.LutDescriptionFieldOrdinal - 1;
}
// Get the list of columns for the lookup table.
DataColumn[] lutColumns = new DataColumn[lutRelation.ParentTable.Columns.Count];
lutRelation.ParentTable.Columns.CopyTo(lutColumns, 0);
// If the lookup table contains the required field name.
if (lutRelation.ParentTable.Columns.Contains(lutFieldName))
{
//---------------------------------------------------------------------
// CHANGED: CR15 (Concatenate IHS codes and descriptions)
// Enable users to specify if individual fields should be
// exported with both codes and descriptions concatenated
// together.
//
// If both the original field and it's corresponding lookup
// table field are required then add them both to the sql
// target list.
if ((fieldFormat != null) && (fieldFormat.ToLower() == "both"))
{
// Add the corresponding lookup table field to the sql
// target list.
targetList.Append(String.Format(",{0}.{1} {5} {6} {5} {2}.{3} AS {4}",
currTable,
_viewModelMain.DataBase.QuoteIdentifier(r.column_name),
parentTableAlias,
_viewModelMain.DataBase.QuoteIdentifier(lutFieldName),
r.field_name.Replace("<no>", ""),
_viewModelMain.DataBase.ConcatenateOperator,
_viewModelMain.DataBase.QuoteValue(" : ")));
// Set the field length of the export field to the source
// field length plus the lookup table field length plus 3
// for the concatenation string length.
fieldLength += lutColumns.First(c => c.ColumnName == lutFieldName).MaxLength + 3;
}
//---------------------------------------------------------------------
else
{
// Add the corresponding lookup table field to the sql
// target list.
targetList.Append(String.Format(",{0}.{1} AS {2}",
parentTableAlias,
_viewModelMain.DataBase.QuoteIdentifier(lutFieldName),
r.field_name.Replace("<no>", "")));
// Set the field length of the lookup table field.
fieldLength = lutColumns.First(c => c.ColumnName == lutFieldName).MaxLength;
}
//---------------------------------------------------------------------
// FIX: 044 Enable text field lengths to be specified in
// the export format.
//
// Override the source field length(s) if an export
// field length has been set.
if (!r.IsNull(_viewModelMain.HluDataset.exports_fields.field_lengthColumn) &&
r.field_length > 0)
fieldLength = r.field_length;
//---------------------------------------------------------------------
// Add the field to the sql list of export table columns.
AddExportColumn(multipleFields ? r.fields_count : 0, r.table_name, r.column_name, r.field_name,
r.field_type, fieldLength, !r.IsNull(_viewModelMain.HluDataset.exports_fields.field_formatColumn) ? r.field_format : String.Empty,
ref exportFields);
}
// If the lookup table does not contains the required field
// name, but does contain the required field ordinal.
else if (lutRelation.ParentTable.Columns.Count >= lutFieldOrdinal)
{
//---------------------------------------------------------------------
// CHANGED: CR15 (Concatenate IHS codes and descriptions)
// Enable users to specify if individual fields should be
// exported with both codes and descriptions concatenated
// together.
//
// If both the original field and it's corresponding lookup
// table field are required then add them both to the sql
// target list.
if ((fieldFormat != null) && (fieldFormat.ToLower() == "both"))
{
// Add the corresponding lookup table field to the sql
// target list.
targetList.Append(String.Format(",{0}.{1} {5} {6} {5} {2}.{3} AS {4}",
currTable,
_viewModelMain.DataBase.QuoteIdentifier(r.column_name),
parentTableAlias,
_viewModelMain.DataBase.QuoteIdentifier(lutRelation.ParentTable.Columns[lutFieldOrdinal].ColumnName),
r.field_name.Replace("<no>", ""),
_viewModelMain.DataBase.ConcatenateOperator,
_viewModelMain.DataBase.QuoteValue(" : ")));
// Set the field length of the lookup table field.
fieldLength = lutColumns.First(c => c.ColumnName == lutFieldName).MaxLength;
}
//---------------------------------------------------------------------
else
{
// Add the corresponding lookup table field to the sql
// target list.
targetList.Append(String.Format(",{0}.{1} AS {2}",
parentTableAlias,
_viewModelMain.DataBase.QuoteIdentifier(lutRelation.ParentTable.Columns[lutFieldOrdinal].ColumnName),
r.field_name.Replace("<no>", "")));
// Set the field length of the lookup table field.
fieldLength = lutColumns.First(c => c.ColumnName == lutFieldName).MaxLength;
}
//---------------------------------------------------------------------
// FIX: 044 Enable text field lengths to be specified in
// the export format.
//
// Override the source field length(s) if an export
// field length has been set.
if (!r.IsNull(_viewModelMain.HluDataset.exports_fields.field_lengthColumn) &&
r.field_length > 0)
fieldLength = r.field_length;
//---------------------------------------------------------------------
// Add the field to the sql list of export table columns.
AddExportColumn(multipleFields ? r.fields_count : 0, r.table_name, r.column_name, r.field_name,
r.field_type, fieldLength, !r.IsNull(_viewModelMain.HluDataset.exports_fields.field_formatColumn) ? r.field_format : null,
ref exportFields);
}
else
{
continue;
}
string joinType;
if (leftJoined.Contains(currTable))
{
joinType = "LEFT";
leftJoined.Add(parentTableAlias);
}
else
{
joinType = "INNER";
}
if (firstJoin)
firstJoin = false;
else
fromClause.Insert(0, "(").Append(")");
fromClause.Append(RelationJoinClause(joinType, currTable,
false, parentTableAlias, lutRelation, fromList));
break;
}
}
//---------------------------------------------------------------------
// FIX: 045 Interweave multiple record fields from the same
// table together.
//
// Create a new field map template with as many items
// as there are input fields.
fieldMapTemplate = new int[exportFields.Max(e => e.FieldOrdinal) + 1][];
// Loop through all the export fields, adding them as columns
// in the export table and adding them to the field map template.
int fieldTotal = 0;
int primaryKeyOrdinal = -1;
_incidOrdinal = -1;
_matrixIdOrdinal = -1;
_formationIdOrdinal = -1;
_managementIdOrdinal = -1;
_complexIdOrdinal = -1;
_bapIdOrdinal = -1;
_sourceIdOrdinal = -1;
_sourceDateStartOrdinals = new List<int>();
_sourceDateEndOrdinals = new List<int>();
_sourceDateTypeOrdinals = new List<int>();
int sourceSortOrderOrdinal = -1;
List<int> sortFields = new List<int>();
List<int> matrixFields = new List<int>();
List<int> formationFields = new List<int>();
List<int> managementFields = new List<int>();
List<int> complexFields = new List<int>();
List<int> bapFields = new List<int>();
List<int> sourceFields = new List<int>();
foreach (ExportField f in exportFields.OrderBy(f => f.FieldOrder))
{
// Create a new data column for the field.
DataColumn c = new DataColumn(f.FieldName, f.FieldType);
// If the field is an autonumber set the relevant
// auto increment properties.
if (f.AutoNum == true) c.AutoIncrement = true;
// If the field is a text field and has a maximum length
// then set the maximum length property.
if ((f.FieldType == System.Type.GetType("System.String")) &&
(f.FieldLength > 0)) c.MaxLength = f.FieldLength;
// Add the field as a new column in the export table.
exportTable.Columns.Add(c);
// If the field will not be sourced from the database.
if (f.FieldOrdinal == -1)
{
// Increment the total number of fields to be exported.
fieldTotal += 1;
// Skip adding the field to the field map template.
continue;
}
// If the field is not repeated and refers to the incid column
// in the incid table.
if ((f.FieldsCount == 0) && ((f.TableName == _viewModelMain.HluDataset.incid.TableName) &&
(f.ColumnName == _viewModelMain.HluDataset.incid.incidColumn.ColumnName)))
{
// Store the input field position for use later
// when exporting the data.
_incidOrdinal = f.FieldOrdinal;
// Add the input field position to the list of fields
// that will be used to sort the input records.
sortFields.Add(f.FieldOrdinal + 1);
// Store the output field position for use later
// as the primary index field ordinal.
primaryKeyOrdinal = fieldTotal;
}
// If the table is the incid_ihs_matrix table.
if (f.TableName == _viewModelMain.HluDataset.incid_ihs_matrix.TableName)
{
// Add the output field position to the list of fields
// that are from the matrix table.
matrixFields.Add(fieldTotal);
// If the field refers to the matrix_id column then store
// the input field ordinal for use later as the unique
// incid_ihs_matrix field ordinal.
if (f.ColumnName == _viewModelMain.HluDataset.incid_ihs_matrix.matrix_idColumn.ColumnName)
_matrixIdOrdinal = f.FieldOrdinal;
}
// If the table is the incid_ihs_formation table.
else if (f.TableName == _viewModelMain.HluDataset.incid_ihs_formation.TableName)
{
// Add the output field position to the list of fields
// that are from the formation table.
formationFields.Add(fieldTotal);
// If the field refers to the formation_id column then store
// the input field ordinal for use later as the unique
// incid_ihs_formation field ordinal.
if (f.ColumnName == _viewModelMain.HluDataset.incid_ihs_formation.formation_idColumn.ColumnName)
_formationIdOrdinal = f.FieldOrdinal;
}
// If the table is the incid_ihs_management table.
else if (f.TableName == _viewModelMain.HluDataset.incid_ihs_management.TableName)
{
// Add the output field position to the list of fields
// that are from the management table.
managementFields.Add(fieldTotal);
// If the field refers to the management_id column then store
// the input field ordinal for use later as the unique
// incid_ihs_management field ordinal.
if (f.ColumnName == _viewModelMain.HluDataset.incid_ihs_management.management_idColumn.ColumnName)
_managementIdOrdinal = f.FieldOrdinal;
}
// If the table is the incid_ihs_complex table.
else if (f.TableName == _viewModelMain.HluDataset.incid_ihs_complex.TableName)
{
// Add the output field position to the list of fields
// that are from the complex table.
complexFields.Add(fieldTotal);
// If the field refers to the complex_id column then store
// the input field ordinal for use later as the unique
// incid_ihs_complex field ordinal.
if (f.ColumnName == _viewModelMain.HluDataset.incid_ihs_complex.complex_idColumn.ColumnName)
_complexIdOrdinal = f.FieldOrdinal;
}
// If the table is the incid_bap table.
else if (f.TableName == _viewModelMain.HluDataset.incid_bap.TableName)
{
// Add the output field position to the list of fields
// that are from the bap table.
bapFields.Add(fieldTotal);
// If the field refers to the bap_id column then store
// the input field ordinal for use later as the unique
// incid_bap field ordinal.
if (f.ColumnName == _viewModelMain.HluDataset.incid_bap.bap_idColumn.ColumnName)
_bapIdOrdinal = f.FieldOrdinal;
}
// If the table is the incid_sources table.
else if (f.TableName == _viewModelMain.HluDataset.incid_sources.TableName)
{
// Add the output field position to the list of fields
// that are from the sources table.
sourceFields.Add(fieldTotal);
// If the field refers to the source_id column and is
// retrieved in it's 'raw' integer state then store
// the input field ordinal for use later as the unique
// incid_source field ordinal.
if ((f.ColumnName == _viewModelMain.HluDataset.incid_sources.source_idColumn.ColumnName) &&
((string.IsNullOrEmpty(f.FieldFormat)) || (f.FieldFormat.ToLower() == "code")))
_sourceIdOrdinal = f.FieldOrdinal;
// If the field refers to the source_sort_order column then
// store the input field ordinal for use later.
else if (f.ColumnName == _viewModelMain.HluDataset.incid_sources.sort_orderColumn.ColumnName)
sourceSortOrderOrdinal = f.FieldOrdinal;
// If the field refers to the source_date_start column then
// store the input field ordinal for use later.
else if (f.ColumnName == _viewModelMain.HluDataset.incid_sources.source_date_startColumn.ColumnName)
_sourceDateStartOrdinals.Add(f.FieldOrdinal);
// If the field refers to the source_date_end column then
// store the input field ordinal for use later.
else if (f.ColumnName == _viewModelMain.HluDataset.incid_sources.source_date_endColumn.ColumnName)
_sourceDateEndOrdinals.Add(f.FieldOrdinal);
// If the field refers to the source_date_type column then
// store the input field ordinal for use later.
else if (f.ColumnName == _viewModelMain.HluDataset.incid_sources.source_date_typeColumn.ColumnName)
_sourceDateTypeOrdinals.Add(f.FieldOrdinal);
}
// Set the field mapping for the current field ordinal.
List<int> fieldMap;
if ((fieldMapTemplate[f.FieldOrdinal] != null) && (f.AutoNum != true))
fieldMap = fieldMapTemplate[f.FieldOrdinal].ToList();
else
{
fieldMap = new List<int>();
fieldMap.Add(f.FieldOrdinal);
}
// Add the current field number to the field map for
// this field ordinal.
fieldMap.Add(fieldTotal);
// Update the field map template for this field ordinal.
fieldMapTemplate[f.FieldOrdinal] = fieldMap.ToArray();
// Increment the total number of fields to be exported.
fieldTotal += 1;
}
//---------------------------------------------------------------------
// Get the last input field ordinal.
int lastFieldOrdinal = exportFields.Max(e => e.FieldOrdinal);
// If any incid_ihs_matrix fields are in the export file.
if ((exportFields.Count(f => f.TableName == _viewModelMain.HluDataset.incid_ihs_matrix.TableName) != 0))
{
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// If the matrix_id column is not included then add
// it so that different matrixs can be identified.
if (_matrixIdOrdinal == -1)
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_ihs_matrix.TableName,
_viewModelMain.HluDataset.incid_ihs_matrix.matrix_idColumn.ColumnName, _viewModelMain.HluDataset.incid_ihs_matrix.matrix_idColumn.ColumnName));
// Store the input field ordinal for use
// later as the unique incid_ihs_matrix field ordinal.
_matrixIdOrdinal = lastFieldOrdinal += 1;
}
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// CHANGED: CR43 (Sort multiple fields in exports)
//
// Add the input field position to the list of fields
// that will be used to sort the input records.
sortFields.Add(_matrixIdOrdinal + 1);
//---------------------------------------------------------------------
}
// If any incid_ihs_formation fields are in the export file.
if ((exportFields.Count(f => f.TableName == _viewModelMain.HluDataset.incid_ihs_formation.TableName) != 0))
{
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// If the formation_id column is not included then add
// it so that different formations can be identified.
if (_formationIdOrdinal == -1)
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_ihs_formation.TableName,
_viewModelMain.HluDataset.incid_ihs_formation.formation_idColumn.ColumnName, _viewModelMain.HluDataset.incid_ihs_formation.formation_idColumn.ColumnName));
// Store the input field ordinal for use
// later as the unique incid_ihs_formation field ordinal.
_formationIdOrdinal = lastFieldOrdinal += 1;
}
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// CHANGED: CR43 (Sort multiple fields in exports)
//
// Add the input field position to the list of fields
// that will be used to sort the input records.
sortFields.Add(_formationIdOrdinal + 1);
//---------------------------------------------------------------------
}
// If any incid_ihs_management fields are in the export file.
if ((exportFields.Count(f => f.TableName == _viewModelMain.HluDataset.incid_ihs_management.TableName) != 0))
{
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// If the management_id column is not included then add
// it so that different managements can be identified.
if (_managementIdOrdinal == -1)
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_ihs_management.TableName,
_viewModelMain.HluDataset.incid_ihs_management.management_idColumn.ColumnName, _viewModelMain.HluDataset.incid_ihs_management.management_idColumn.ColumnName));
// Store the input field ordinal for use
// later as the unique incid_ihs_management field ordinal.
_managementIdOrdinal = lastFieldOrdinal += 1;
}
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// CHANGED: CR43 (Sort multiple fields in exports)
//
// Add the input field position to the list of fields
// that will be used to sort the input records.
sortFields.Add(_managementIdOrdinal + 1);
//---------------------------------------------------------------------
}
// If any incid_ihs_complex fields are in the export file.
if ((exportFields.Count(f => f.TableName == _viewModelMain.HluDataset.incid_ihs_complex.TableName) != 0))
{
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// If the complex_id column is not included then add
// it so that different complexs can be identified.
if (_complexIdOrdinal == -1)
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_ihs_complex.TableName,
_viewModelMain.HluDataset.incid_ihs_complex.complex_idColumn.ColumnName, _viewModelMain.HluDataset.incid_ihs_complex.complex_idColumn.ColumnName));
// Store the input field ordinal for use
// later as the unique incid_ihs_complex field ordinal.
_complexIdOrdinal = lastFieldOrdinal += 1;
}
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// CHANGED: CR43 (Sort multiple fields in exports)
//
// Add the input field position to the list of fields
// that will be used to sort the input records.
sortFields.Add(_complexIdOrdinal + 1);
//---------------------------------------------------------------------
}
// If any incid_bap fields are in the export file.
if ((exportFields.Count(f => f.TableName == _viewModelMain.HluDataset.incid_bap.TableName) != 0))
{
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// If the bap_id column is not included then add
// it so that different baps can be identified.
if (_bapIdOrdinal == -1)
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_bap.TableName,
_viewModelMain.HluDataset.incid_bap.bap_idColumn.ColumnName, _viewModelMain.HluDataset.incid_bap.bap_idColumn.ColumnName));
// Store the input field ordinal for use
// later as the unique incid_bap field ordinal.
_bapIdOrdinal = lastFieldOrdinal += 1;
}
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// CHANGED: CR43 (Sort multiple fields in exports)
//
// Add a field to the input table to get the type of
// bap habitat so that 'None' habitats are listed after
// 'real' habitats.
if ((DbFactory.ConnectionType.ToString().ToLower() == "access") ||
(DbFactory.Backend.ToString().ToLower() == "access"))
targetList.Append(String.Format(", IIF({0}.{1} = {2}, 1, 0) AS {3}",
_viewModelMain.HluDataset.incid_bap.TableName,
_viewModelMain.HluDataset.incid_bap.bap_habitatColumn.ColumnName,
_viewModelMain.DataBase.QuoteValue(Settings.Default.BAPHabitatIgnore), "bap_habitat_type"));
else
targetList.Append(String.Format(", CASE {0}.{1} WHEN {2} THEN 1 ELSE 0 END AS {3}",
_viewModelMain.HluDataset.incid_bap.TableName,
_viewModelMain.HluDataset.incid_bap.bap_habitatColumn.ColumnName,
_viewModelMain.DataBase.QuoteValue(Settings.Default.BAPHabitatIgnore), "bap_habitat_type"));
// Store the input field ordinal for use later.
int bapTypeOrdinal = lastFieldOrdinal += 1;
// Add the input field position to the list of fields
// that will be used to sort the input records.
sortFields.Add(bapTypeOrdinal + 1);
//---------------------------------------------------------------------
// CHANGED: CR43 (Sort multiple fields in exports)
//
// Add a field to the input table to get the determination
// quality of the bap habitat so that 'not present' habitats
// are listed after 'present' habitats.
if ((DbFactory.ConnectionType.ToString().ToLower() == "access") ||
(DbFactory.Backend.ToString().ToLower() == "access"))
targetList.Append(String.Format(", IIF({0}.{1} = {2}, 1, 0) AS {3}",
_viewModelMain.HluDataset.incid_bap.TableName,
_viewModelMain.HluDataset.incid_bap.quality_determinationColumn.ColumnName,
_viewModelMain.DataBase.QuoteValue(Settings.Default.BAPDeterminationQualiltyUserAdded), "bap_habitat_quality"));
else
targetList.Append(String.Format(", CASE {0}.{1} WHEN {2} THEN 1 ELSE 0 END AS {3}",
_viewModelMain.HluDataset.incid_bap.TableName,
_viewModelMain.HluDataset.incid_bap.quality_determinationColumn.ColumnName,
_viewModelMain.DataBase.QuoteValue(Settings.Default.BAPDeterminationQualiltyUserAdded), "bap_habitat_quality"));
// Store the input field ordinal for use later.
int bapQualityOrdinal = lastFieldOrdinal += 1;
// Add the input field position to the list of fields
// that will be used to sort the input records.
sortFields.Add(bapQualityOrdinal + 1);
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// CHANGED: CR43 (Sort multiple fields in exports)
//
// Add the input field position to the list of fields
// that will be used to sort the input records.
sortFields.Add(_bapIdOrdinal + 1);
//---------------------------------------------------------------------
}
// If any incid_source fields are in the export file.
if ((exportFields.Count(f => f.TableName == _viewModelMain.HluDataset.incid_sources.TableName) != 0))
{
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// If the source_id column is not included then add
// it so that different sources can be identified.
if (_sourceIdOrdinal == -1)
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_sources.TableName,
_viewModelMain.HluDataset.incid_sources.source_idColumn.ColumnName, _viewModelMain.HluDataset.incid_sources.source_idColumn.ColumnName));
// Store the input field ordinal for use
// later as the unique incid_source field ordinal.
_sourceIdOrdinal = lastFieldOrdinal += 1;
}
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// CHANGED: CR43 (Sort multiple fields in exports)
//
// If the sort_order column is not included then add
// it so that the sources can be sorted.
if (sourceSortOrderOrdinal == -1)
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_sources.TableName,
_viewModelMain.HluDataset.incid_sources.sort_orderColumn.ColumnName, _viewModelMain.HluDataset.incid_sources.sort_orderColumn.ColumnName));
// Store the input field ordinal for use
// later as the unique incid_source field ordinal.
sourceSortOrderOrdinal = lastFieldOrdinal += 1;
}
// Add the input field position to the list of fields
// that will be used to sort the input records.
sortFields.Add(sourceSortOrderOrdinal + 1);
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// CHANGED: CR17 (Exporting date fields)
// Store all of the source date fields for use later when
// formatting the attribute data.
//
// If the source_date_start column is not included then add
// it for use later.
if ((_sourceDateStartOrdinals == null) || (_sourceDateStartOrdinals.Count() == 0))
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_sources.TableName,
_viewModelMain.HluDataset.incid_sources.source_date_startColumn.ColumnName, _viewModelMain.HluDataset.incid_sources.source_date_startColumn.ColumnName));
// Store the input field ordinal for use later.
_sourceDateStartOrdinals.Add(lastFieldOrdinal += 1);
}
// If the source_date_end column is not included then add
// it for use later.
if ((_sourceDateEndOrdinals == null) || (_sourceDateEndOrdinals.Count() == 0))
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_sources.TableName,
_viewModelMain.HluDataset.incid_sources.source_date_endColumn.ColumnName, _viewModelMain.HluDataset.incid_sources.source_date_endColumn.ColumnName));
// Store the input field ordinal for use later.
_sourceDateEndOrdinals.Add(lastFieldOrdinal += 1);
}
// If the source_date_type column is not included then add
// it for use later.
if ((_sourceDateTypeOrdinals == null) || (_sourceDateTypeOrdinals.Count() == 0))
{
// Add the field to the input table.
targetList.Append(String.Format(",{0}.{1} AS {2}", _viewModelMain.HluDataset.incid_sources.TableName,
_viewModelMain.HluDataset.incid_sources.source_date_typeColumn.ColumnName, _viewModelMain.HluDataset.incid_sources.source_date_typeColumn.ColumnName));
// Store the input field ordinal for use later.
_sourceDateTypeOrdinals.Add(lastFieldOrdinal += 1);
}
//---------------------------------------------------------------------
}
//---------------------------------------------------------------------
// CHANGED: CR43 (Sort multiple fields in exports)
//
// Store which export fields will be used to sort the
// input records.
sortOrdinals = sortFields.ToArray();
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// Store the field ordinals for all the fields for
// every child table.
matrixOrdinals = matrixFields.ToArray();
formationOrdinals = formationFields.ToArray();
managementOrdinals = managementFields.ToArray();
complexOrdinals = complexFields.ToArray();
bapOrdinals = bapFields.ToArray();
sourceOrdinals = sourceFields.ToArray();
//---------------------------------------------------------------------
// Set the incid field as the primary key to the table.
if (primaryKeyOrdinal != -1)
exportTable.PrimaryKey = new DataColumn[] { exportTable.Columns[primaryKeyOrdinal] };
// Remove the leading comma from the target list of fields.
if (targetList.Length > 1) targetList.Remove(0, 1);
}