protected List <SqlFilterCondition> MapWhereClauseFields(
HluGISLayer.incid_mm_polygonsDataTable _hluLayerStructure, List <SqlFilterCondition> whereClause)
{
List <SqlFilterCondition> outWhereClause = new List <SqlFilterCondition>();
for (int i = 0; i < whereClause.Count; i++)
{
SqlFilterCondition cond = whereClause[i];
if (!_hluLayerStructure.Columns.Contains(cond.Column.ColumnName))
{
if ((!String.IsNullOrEmpty(cond.CloseParentheses)) && (outWhereClause.Count > 0))
{
SqlFilterCondition condPrev = outWhereClause[outWhereClause.Count - 1];
condPrev.CloseParentheses += cond.CloseParentheses;
outWhereClause[outWhereClause.Count - 1] = condPrev;
}
if ((!String.IsNullOrEmpty(cond.OpenParentheses)) && (i < whereClause.Count - 1))
{
SqlFilterCondition condNext = whereClause[i + 1];
condNext.OpenParentheses += cond.OpenParentheses;
whereClause[i + 1] = condNext;
}
continue;
}
string columnName = GetFieldName(_hluLayerStructure.Columns[cond.Column.ColumnName].Ordinal);
if (!String.IsNullOrEmpty(columnName))
{
cond.Column = new DataColumn(columnName, cond.Column.DataType);
outWhereClause.Add(cond);
}
}
return(outWhereClause);
}
/// <summary>
/// Create a list of sql filter conditions using a table of selected Incids.
/// </summary>
/// <param name="incidSelection">A DataTable containing the selected Incids.</param>
/// <param name="gisApp">Which GIS system is being used.</param>
/// <returns>
/// A list of sql filter conditions.
/// </returns>
public static List<SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp, bool useIncidTable)
{
List<SqlFilterCondition> whereClause = new List<SqlFilterCondition>();
SqlFilterCondition cond = new SqlFilterCondition();
//StringBuilder incidList = new StringBuilder();
// Split the table of selected Incids into chunks of continuous Incids so
// that each chunk contains a continuous series of one or more Incids.
var query = incidSelection.AsEnumerable().Select((r, index) => new
{
RowIndex = RecordIds.IncidNumber(r.Field<string>(0)) - index,
Incid = r.Field<string>(0)
}).ChunkBy(r => r.RowIndex);
// Create a temporary list for storing some of the Incids.
List<string> inList = new List<string>();
// Determine which table to base the conditions on.
DataTable condTable;
if (useIncidTable)
condTable = _incidTable;
else
condTable = _incidMMTable;
// Loop through each chunk/series of Incids. If there are at least three
// then it is worth processing them within ">=" and "<=" operators (as
// this keeps the filter conditions short). If there are only one or two
// Incids in the chunk then add them to the temporary 'inList' for
// processing later.
foreach (var item in query)
{
if (item.Count() < 3)
{
if (gisApp != null)
inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
else
inList.AddRange(item.Select(t => t.Incid));
}
else
{
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidMMTable.incidColumn;
cond.Table = condTable;
cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
cond.Operator = ">=";
cond.Value = item.First().Incid;
cond.CloseParentheses = String.Empty;
whereClause.Add(cond);
cond = new SqlFilterCondition();
cond.BooleanOperator = "AND";
cond.OpenParentheses = String.Empty;
cond.Column = _incidMMTable.incidColumn;
cond.Table = condTable;
cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
cond.Operator = "<=";
cond.Value = item.Last().Incid;
cond.CloseParentheses = ")";
whereClause.Add(cond);
}
}
// Any Incids that are not part of a continuous series of at least
// three Incid must be queried using an "=" or "IN" sql operator.
// So loop through all these Incids and lump them together into
// strings of 254 Incids at a time so that each string can be used
// in an "IN" statement.
int i = 0;
while (i < inList.Count)
{
int numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
string[] oneList = new string[numElems];
inList.CopyTo(i, oneList, 0, numElems);
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidMMTable.incidColumn;
cond.Table = condTable;
cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
//---------------------------------------------------------------------
// FIX: 001 Improve speed of 'Select current Incid on Map'
// Use " INCID =" in SQL statement instrad of "INCID IN ()"
// if there is only on item in the list (as it is much quicker)
if (inList.Count == 1)
cond.Operator = "=";
else
cond.Operator = "IN ()";
//---------------------------------------------------------------------
cond.Value = String.Join(",", oneList);
cond.CloseParentheses = ")";
whereClause.Add(cond);
i += numElems;
}
return whereClause;
}
public static List <SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp)
{
List <SqlFilterCondition> whereClause = new List <SqlFilterCondition>();
SqlFilterCondition cond = new SqlFilterCondition();
StringBuilder incidList = new StringBuilder();
var query = incidSelection.AsEnumerable().Select((r, index) => new
{
RowIndex = RecordIds.IncidNumber(r.Field <string>(0)) - index,
Incid = r.Field <string>(0)
}).ChunkBy(r => r.RowIndex);
List <string> inList = new List <string>();
foreach (var item in query)
{
if (item.Count() < 3)
{
inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
}
else
{
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidTable.incidColumn;
cond.Table = _incidTable;
cond.ColumnSystemType = _incidTable.incidColumn.DataType;
cond.Operator = ">=";
cond.Value = item.First().Incid;
cond.CloseParentheses = String.Empty;
whereClause.Add(cond);
cond = new SqlFilterCondition();
cond.BooleanOperator = "AND";
cond.OpenParentheses = String.Empty;
cond.Column = _incidTable.incidColumn;
cond.Table = _incidTable;
cond.ColumnSystemType = _incidTable.incidColumn.DataType;
cond.Operator = "<=";
cond.Value = item.Last().Incid;
cond.CloseParentheses = ")";
whereClause.Add(cond);
}
}
int i = 0;
while (i < inList.Count)
{
int numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
string[] oneList = new string[numElems];
inList.CopyTo(i, oneList, 0, numElems);
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidMMTable.incidColumn;
cond.Table = _incidMMTable;
cond.ColumnSystemType = _incidTable.incidColumn.DataType;
cond.Operator = "IN ()";
cond.Value = String.Join(",", oneList);
cond.CloseParentheses = ")";
whereClause.Add(cond);
i += numElems;
}
return(whereClause);
}
//---------------------------------------------------------------------
/// <summary>
/// Following a feature split performed by the user with regular GIS tools, updates the toid_fragment_id of
/// the newly created feature(s), incrementing from the highest toid_fragment_id in use for that toid, which is
/// passed in as parameter lastToidFragmentID. toid_fragment_id is an integer stored as varchar(5) with
/// leading zeros (.ToString("D5")).
/// </summary>
/// <param name="currentToidFragmentID">Current toid_fragment_id for the selected toid.</param>
/// <param name="lastToidFragmentID">Highest toid_fragment_id for the selected toid.</param>
/// <param name="selectionWhereClause">Where clause underlying the current selection set.</param>
/// <param name="historyColumns">All columns of the GIS layer structure except the geometry property columns.</param>
/// <returns>DataTable with the columns in the historyColumns parameter, containing the attributes of all
/// the features resulting from the split.
/// The first row corresponds to the original feature (the one that was split).</returns>
public override DataTable SplitFeature(string currentToidFragmentID, string lastToidFragmentID,
List<SqlFilterCondition> selectionWhereClause, DataColumn[] historyColumns)
{
bool rollbackChanges = false;
try
{
if (String.IsNullOrEmpty(_selName)) _selName = _mapInfoApp.Eval(String.Format(
"SelectionInfo({0})", (int)MapInfoConstants.SelectionInfo.SEL_INFO_TABLENAME));
if (!TableExists(_selName)) return null;
string origSelName = _selName;
DataTable selTable = CreateHistoryTable(!String.IsNullOrEmpty(_selName) &&
_mapInfoApp.Eval(String.Format("SelectionInfo({0})",
(int)MapInfoConstants.SelectionInfo.SEL_INFO_TABLENAME)) ==
_hluLayer ? _selName : _hluLayer, false, historyColumns);
ChangeWhereClauseTable(new KeyValuePair<DataTable, DataTable>[] { new KeyValuePair<DataTable, DataTable>(_hluLayerStructure, selTable) },
ref selectionWhereClause);
// get history data for selected features
DataTable historyTable = SqlSelect(false, false, selTable.Columns.Cast<DataColumn>().ToArray(),
_selName, true, false, selectionWhereClause, null);
if ((historyTable == null) || (historyTable.Rows.Count < 2)) return null;
// Get the number of selected rows from MapInfo
int selNum = Int32.Parse(_mapInfoApp.Eval(String.Format("SelectionInfo({0})",
(int)MapInfoConstants.SelectionInfo.SEL_INFO_NROWS)));
// Set the new ToidFragmentID
string numFormat = String.Format("D{0}", lastToidFragmentID.Length);
int newToidFragmentIDnum = Int32.Parse(lastToidFragmentID);
string toidFragFieldName = GetFieldName(_hluLayerStructure.toid_fragment_idColumn.Ordinal);
rollbackChanges = true;
//---------------------------------------------------------------------
// FIXED: KI106 (Shape area and length values)
// Includes updates for the geom1 and geom2 columns as the features
// have changed in size
string lastColName = GetFieldName(_hluLayerStructure.ihs_summaryColumn.Ordinal);
int ixGeom1 = Int32.Parse(_mapInfoApp.Eval(String.Format("ColumnInfo({0}, {1}, {2})", _selName,
QuoteValue(lastColName), (int)MapInfoConstants.ColumnInfo.COL_INFO_NUM))) + 1;
int ixGeom2 = ixGeom1 + 1;
string geomCol1Name = _mapInfoApp.Eval(String.Format("ColumnInfo({0}, {1}, {2})", _selName,
QuoteValue(String.Format("Col{0}", ixGeom1)), (int)MapInfoConstants.ColumnInfo.COL_INFO_NAME));
string geomCol2Name = _mapInfoApp.Eval(String.Format("ColumnInfo({0}, {1}, {2})", _selName,
QuoteValue(String.Format("Col{0}", ixGeom2)), (int)MapInfoConstants.ColumnInfo.COL_INFO_NAME));
double geom1;
double geom2;
string fetchCommand = String.Format("Fetch Next From {0}", _selName);
_mapInfoApp.Do(String.Format("Fetch First From {0}", _selName));
// For each of the selected rows (there will be more than one if the feature
// was split into more than two parts)
for (int i = 1; i <= selNum; i++)
{
// Get the geometry values from the layer
GetGeometryInfo(_selName, out geom1, out geom2);
// Update the geometry values
_mapInfoApp.Do(String.Format("Update {0} Set {1} = {3}, {2} = {4} Where RowID = {5}",
_selName, QuoteIdentifier(geomCol1Name), QuoteIdentifier(geomCol2Name), geom1, geom2, i));
// Update the toid_fragment_id for all but the first feature (which can keep the existing
// value)
if (i > 1)
{
_mapInfoApp.Do(String.Format("Update {0} Set {1} = {2} Where RowID = {3}", _selName,
toidFragFieldName, QuoteValue((++newToidFragmentIDnum).ToString(numFormat)), i));
}
// Fetch the next row
_mapInfoApp.Do(fetchCommand);
}
//---------------------------------------------------------------------
rollbackChanges = false;
// remove cutter object from cosmetic layer
_mapInfoApp.Do(String.Format("Delete From LayerInfo({0}, 0, 1)", _hluMapWindowID));
// remove cutter object from HLU layer
DataTable cleanupTable = selTable.Clone();
cleanupTable.TableName = _hluLayer;
DataTable retTable = SqlSelect(true, false, cleanupTable.Columns.Cast<DataColumn>().ToArray(),
_hluLayer, false, false, new List<SqlFilterCondition>(new SqlFilterCondition[]
{ new SqlFilterCondition( cleanupTable, cleanupTable.Columns[_hluLayerStructure.incidColumn.ColumnName],
String.Empty) }), null);
if ((retTable != null) && TableExists(_selName) && (_selName != origSelName) &&
(Int32.Parse(_mapInfoApp.Eval(String.Format("TableInfo({0}, {1})", _selName,
(int)MapInfoConstants.TableInfo.TAB_INFO_NROWS))) > 0))
{
_mapInfoApp.Do(String.Format("Delete From {0}", _selName));
_mapInfoApp.Do(String.Format("Close Table {0}", _selName));
}
CommitChanges();
// re-select split polygons (all rows in orignal selection)
List<SqlFilterCondition> reSelectionWhereClause;
//---------------------------------------------------------------------
// FIX: 029 Ignore case when comparing column names
// Ignore case when comparing column names so that GIS layer
// names may be mixed/upper case.
var q = selectionWhereClause.Where(c => c.Column.ColumnName.ToLower() == _hluLayerStructure.toidColumn.ColumnName.ToLower());
if (q.Count() == 1)
{
selTable = CreateHistoryTable(_hluLayer, false, historyColumns);
SqlFilterCondition cond = new SqlFilterCondition("AND", selTable,
_hluLayerStructure.toidColumn, q.ElementAt(0).Value);
cond.Table = selTable;
reSelectionWhereClause = new List<SqlFilterCondition>();
reSelectionWhereClause.Add(cond);
//---------------------------------------------------------------------
// FIXED: KI110 (Physical split)
// The filter should search for the currentToidFragmentID (pre-split)
// and the newTOIDFragmentID that was created (post-split).
cond = new SqlFilterCondition("AND", selTable,
selTable.Columns[_hluLayerStructure.toid_fragment_idColumn.ColumnName], typeof(DataColumn), "(", String.Empty, currentToidFragmentID);
cond.Operator = "=";
reSelectionWhereClause.Add(cond);
cond = new SqlFilterCondition("OR", selTable,
selTable.Columns[_hluLayerStructure.toid_fragment_idColumn.ColumnName], typeof(DataColumn), "(", String.Empty, lastToidFragmentID);
cond.Operator = ">";
reSelectionWhereClause.Add(cond);
cond = new SqlFilterCondition("AND", selTable,
selTable.Columns[_hluLayerStructure.toid_fragment_idColumn.ColumnName], typeof(DataColumn), String.Empty, "))", newToidFragmentIDnum.ToString(numFormat));
cond.Operator = "<=";
reSelectionWhereClause.Add(cond);
//---------------------------------------------------------------------
SqlSelect(true, true, selTable.Columns.Cast<DataColumn>().ToArray(),
_hluLayer, false, false, reSelectionWhereClause, null);
}
else if (TableExists(origSelName))
{
reSelectionWhereClause = new List<SqlFilterCondition>();
SqlFilterCondition cond = selectionWhereClause[0];
cond.Operator = "IS NOT NULL";
cond.BooleanOperator = "AND";
cond.OpenParentheses = String.Empty;
cond.CloseParentheses = String.Empty;
selTable.TableName = origSelName;
reSelectionWhereClause.Add(cond);
retTable = SqlSelect(true, true, selTable.Columns.Cast<DataColumn>().ToArray(),
origSelName, false, false, reSelectionWhereClause, null);
}
if (TableExists(origSelName))
_mapInfoApp.Do(String.Format("Close Table {0}", origSelName));
return historyTable;
}
catch
{
if (rollbackChanges)
_mapInfoApp.RunCommand(String.Format("Rollback Table {0}", _hluLayer));
return null;
}
}
private Regex _queryValueRegex = new Regex(@"\s+:\s+", RegexOptions.IgnoreCase); // @"\A(?<code>[^:\s]+)\s+:\s+(?<desc>[^:]+)\z", RegexOptions.IgnoreCase);
#endregion
#region Constructor
public QueryItem()
{
_sqlCond = new SqlFilterCondition();
_sqlCond.OpenParentheses = "(";
_sqlCond.CloseParentheses = ")";
}
public QueryItem()
{
_sqlCond = new SqlFilterCondition();
_sqlCond.OpenParentheses = "(";
_sqlCond.CloseParentheses = ")";
}
public static List <List <SqlFilterCondition> > GisSelectionToWhereClause <T>(
DataRow[] selectedRows, int[] keyColumOrdinals, int blockSize, T targetTable)
where T : DataTable
{
DataTable selectionTable = selectedRows[0].Table;
List <List <SqlFilterCondition> > whereClause = new List <List <SqlFilterCondition> >();
int i = 0;
while (i < selectedRows.Length)
{
List <SqlFilterCondition> whereClauseBlock = new List <SqlFilterCondition>();
int j = i;
while (j < selectedRows.Length)
{
DataRow r = selectedRows[j];
for (int k = 0; k < keyColumOrdinals.Length; k++)
{
SqlFilterCondition cond = new SqlFilterCondition();
if (k == 0)
{
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
}
else
{
cond.BooleanOperator = "AND";
cond.OpenParentheses = String.Empty;
}
cond.Column = selectionTable.Columns[keyColumOrdinals[k]];
cond.Table = targetTable;
cond.ColumnSystemType = selectionTable.Columns[k].DataType;
cond.Operator = "=";
cond.Value = r[keyColumOrdinals[k]];
if (k == keyColumOrdinals.Length - 1)
{
cond.CloseParentheses = ")";
}
else
{
cond.CloseParentheses = String.Empty;
}
whereClauseBlock.Add(cond);
}
j++;
if (whereClauseBlock.Count >= blockSize)
{
break;
}
}
if (whereClauseBlock.Count > 0)
{
whereClause.Add(whereClauseBlock);
}
i = j;
}
return(whereClause);
}
private void Export(int userExportId, bool exportDescriptions, bool selectedOnly)
{
string tempPath = null;
try
{
string tableAlias = GetTableAlias();
if (tableAlias == null)
throw new Exception("Failed to find a table alias that does not match a table name in the HLU dataset.");
// refresh export fields from DB
_viewModelMain.HluTableAdapterManager.exportsFieldsTableAdapter.ClearBeforeFill = true;
_viewModelMain.HluTableAdapterManager.exportsFieldsTableAdapter.Fill(
_viewModelMain.HluDataset.exports_fields, String.Format("{0} = {1} ORDER BY {2}, {3}",
_viewModelMain.DataBase.QuoteIdentifier(
_viewModelMain.HluDataset.exports_fields.export_idColumn.ColumnName), userExportId,
_viewModelMain.DataBase.QuoteIdentifier(
_viewModelMain.HluDataset.exports_fields.table_nameColumn.ColumnName),
_viewModelMain.DataBase.QuoteIdentifier(
_viewModelMain.HluDataset.exports_fields.field_ordinalColumn.ColumnName)));
if (_viewModelMain.HluDataset.exports_fields.Count == 0)
throw new Exception(String.Format("No export fields are defined for format '{0}'.",
_viewModelMain.HluDataset.exports.FindByexport_id(userExportId).export_name));
DataTable exportTable;
List<int> fieldCountList;
StringBuilder targetList;
StringBuilder fromClause;
int incidOrdinal;
ExportJoins(exportDescriptions, tableAlias, out exportTable,
out fieldCountList, out targetList, out fromClause, out incidOrdinal);
string whereClause = String.Empty;
List<List<SqlFilterCondition>> exportFilter = null;
if (!_viewModelExport.SelectedOnly)
{
SqlFilterCondition cond = new SqlFilterCondition("AND",
_viewModelMain.IncidTable, _viewModelMain.IncidTable.incidColumn, null);
cond.Operator = "IS NOT NULL";
exportFilter = new List<List<SqlFilterCondition>>(new List<SqlFilterCondition>[] {
new List<SqlFilterCondition>(new SqlFilterCondition[] { cond }) });
}
else
{
if ((_viewModelMain.IncidSelectionWhereClause == null) &&
(_viewModelMain.GisSelection != null) && (_viewModelMain.GisSelection.Rows.Count > 0))
_viewModelMain.IncidSelectionWhereClause = ViewModelWindowMainHelpers.GisSelectionToWhereClause(
_viewModelMain.GisSelection.Select(), _viewModelMain.GisIDColumnOrdinals,
ViewModelWindowMain.IncidPageSize, _viewModelMain.HluDataset.incid);
exportFilter = _viewModelMain.IncidSelectionWhereClause;
}
string sql = ScratchDb.UnionQuery(targetList.ToString(), fromClause.ToString(),
incidOrdinal, exportFilter, _viewModelMain.DataBase);
_viewModelMain.ChangeCursor(Cursors.Wait, "Exporting ...");
int exportRowCount;
tempPath = ExportMdb(sql, exportTable, incidOrdinal, fieldCountList, out exportRowCount);
if (!String.IsNullOrEmpty(tempPath) && (exportRowCount > 0))
_viewModelMain.GISApplication.Export(tempPath, exportTable.TableName, exportRowCount);
else
throw new Exception("Export query did not retrieve any rows");
_viewModelMain.IncidSelection = null;
_viewModelMain.GisSelection = null;
_viewModelMain.OnPropertyChanged("IsFiltered");
_viewModelMain.OnPropertyChanged("StatusIncid");
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Export failed. The error message was:\n\n{0}.",
ex.Message), "HLU: Export", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if (!String.IsNullOrEmpty(tempPath))
{
string[] tempFiles = Directory.GetFiles(Path.GetDirectoryName(tempPath),
Path.GetFileNameWithoutExtension(tempPath) + ".*");
foreach (string fName in tempFiles)
{
try { File.Delete(fName); }
catch { _viewModelMain.ExportMdbs.Add(fName); }
}
}
_viewModelMain.ChangeCursor(Cursors.Arrow, null);
}
}
public static List<SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp)
{
List<SqlFilterCondition> whereClause = new List<SqlFilterCondition>();
SqlFilterCondition cond = new SqlFilterCondition();
StringBuilder incidList = new StringBuilder();
var query = incidSelection.AsEnumerable().Select((r, index) => new
{
RowIndex = RecordIds.IncidNumber(r.Field<string>(0)) - index,
Incid = r.Field<string>(0)
}).ChunkBy(r => r.RowIndex);
List<string> inList = new List<string>();
foreach (var item in query)
{
if (item.Count() < 3)
{
inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
}
else
{
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidTable.incidColumn;
cond.Table = _incidTable;
cond.ColumnSystemType = _incidTable.incidColumn.DataType;
cond.Operator = ">=";
cond.Value = item.First().Incid;
cond.CloseParentheses = String.Empty;
whereClause.Add(cond);
cond = new SqlFilterCondition();
cond.BooleanOperator = "AND";
cond.OpenParentheses = String.Empty;
cond.Column = _incidTable.incidColumn;
cond.Table = _incidTable;
cond.ColumnSystemType = _incidTable.incidColumn.DataType;
cond.Operator = "<=";
cond.Value = item.Last().Incid;
cond.CloseParentheses = ")";
whereClause.Add(cond);
}
}
int i = 0;
while (i < inList.Count)
{
int numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
string[] oneList = new string[numElems];
inList.CopyTo(i, oneList, 0, numElems);
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidMMTable.incidColumn;
cond.Table = _incidMMTable;
cond.ColumnSystemType = _incidTable.incidColumn.DataType;
cond.Operator = "IN ()";
cond.Value = String.Join(",", oneList);
cond.CloseParentheses = ")";
whereClause.Add(cond);
i += numElems;
}
return whereClause;
}
private void Export(int userExportId, bool exportDescriptions, bool selectedOnly)
{
string tempPath = null;
try
{
string tableAlias = GetTableAlias();
if (tableAlias == null)
{
throw new Exception("Failed to find a table alias that does not match a table name in the HLU dataset.");
}
// refresh export fields from DB
_viewModelMain.HluTableAdapterManager.exportsFieldsTableAdapter.ClearBeforeFill = true;
_viewModelMain.HluTableAdapterManager.exportsFieldsTableAdapter.Fill(
_viewModelMain.HluDataset.exports_fields, String.Format("{0} = {1} ORDER BY {2}, {3}",
_viewModelMain.DataBase.QuoteIdentifier(
_viewModelMain.HluDataset.exports_fields.export_idColumn.ColumnName), userExportId,
_viewModelMain.DataBase.QuoteIdentifier(
_viewModelMain.HluDataset.exports_fields.table_nameColumn.ColumnName),
_viewModelMain.DataBase.QuoteIdentifier(
_viewModelMain.HluDataset.exports_fields.field_ordinalColumn.ColumnName)));
if (_viewModelMain.HluDataset.exports_fields.Count == 0)
{
throw new Exception(String.Format("No export fields are defined for format '{0}'.",
_viewModelMain.HluDataset.exports.FindByexport_id(userExportId).export_name));
}
DataTable exportTable;
List <int> fieldCountList;
StringBuilder targetList;
StringBuilder fromClause;
int incidOrdinal;
ExportJoins(exportDescriptions, tableAlias, out exportTable,
out fieldCountList, out targetList, out fromClause, out incidOrdinal);
string whereClause = String.Empty;
List <List <SqlFilterCondition> > exportFilter = null;
if (!_viewModelExport.SelectedOnly)
{
SqlFilterCondition cond = new SqlFilterCondition("AND",
_viewModelMain.IncidTable, _viewModelMain.IncidTable.incidColumn, null);
cond.Operator = "IS NOT NULL";
exportFilter = new List <List <SqlFilterCondition> >(new List <SqlFilterCondition>[] {
new List <SqlFilterCondition>(new SqlFilterCondition[] { cond })
});
}
else
{
if ((_viewModelMain.IncidSelectionWhereClause == null) &&
(_viewModelMain.GisSelection != null) && (_viewModelMain.GisSelection.Rows.Count > 0))
{
_viewModelMain.IncidSelectionWhereClause = ViewModelWindowMainHelpers.GisSelectionToWhereClause(
_viewModelMain.GisSelection.Select(), _viewModelMain.GisIDColumnOrdinals,
ViewModelWindowMain.IncidPageSize, _viewModelMain.HluDataset.incid);
}
exportFilter = _viewModelMain.IncidSelectionWhereClause;
}
string sql = ScratchDb.UnionQuery(targetList.ToString(), fromClause.ToString(),
incidOrdinal, exportFilter, _viewModelMain.DataBase);
_viewModelMain.ChangeCursor(Cursors.Wait, "Exporting ...");
int exportRowCount;
tempPath = ExportMdb(sql, exportTable, incidOrdinal, fieldCountList, out exportRowCount);
if (!String.IsNullOrEmpty(tempPath) && (exportRowCount > 0))
{
_viewModelMain.GISApplication.Export(tempPath, exportTable.TableName, exportRowCount);
}
else
{
throw new Exception("Export query did not retrieve any rows");
}
_viewModelMain.IncidSelection = null;
_viewModelMain.GisSelection = null;
_viewModelMain.OnPropertyChanged("IsFiltered");
_viewModelMain.OnPropertyChanged("StatusIncid");
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Export failed. The error message was:\n\n{0}.",
ex.Message), "HLU: Export", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if (!String.IsNullOrEmpty(tempPath))
{
string[] tempFiles = Directory.GetFiles(Path.GetDirectoryName(tempPath),
Path.GetFileNameWithoutExtension(tempPath) + ".*");
foreach (string fName in tempFiles)
{
try { File.Delete(fName); }
catch { _viewModelMain.ExportMdbs.Add(fName); }
}
}
_viewModelMain.ChangeCursor(Cursors.Arrow, null);
}
}
/// <summary>
/// Exports the combined GIS and database data using the
/// specified export format.
/// </summary>
/// <param name="userExportId">The export format selected by the user.</param>
/// <param name="selectedOnly">If set to <c>true</c> export only selected incids/features.</param>
/// <exception cref="System.Exception">
/// Failed to find a table alias that does not match a table name in the HLU dataset
/// or
/// No export fields are defined for format 'x'
/// or
/// Export query did not retrieve any rows
/// </exception>
private void Export(int userExportId, bool selectedOnly)
{
string tempPath = null;
try
{
_viewModelMain.ChangeCursor(Cursors.Wait, "Creating export table ...");
// Create a new unique table name to export to.
string tableAlias = GetTableAlias();
if (tableAlias == null)
throw new Exception("Failed to find a table alias that does not match a table name in the HLU dataset");
// Retrieve the export fields for the export format
// selected by the user from the database.
_viewModelMain.HluTableAdapterManager.exportsFieldsTableAdapter.ClearBeforeFill = true;
_viewModelMain.HluTableAdapterManager.exportsFieldsTableAdapter.Fill(
_viewModelMain.HluDataset.exports_fields, String.Format("{0} = {1} ORDER BY {2}, {3}",
_viewModelMain.DataBase.QuoteIdentifier(_viewModelMain.HluDataset.exports_fields.export_idColumn.ColumnName),
userExportId,
_viewModelMain.DataBase.QuoteIdentifier(_viewModelMain.HluDataset.exports_fields.table_nameColumn.ColumnName),
_viewModelMain.DataBase.QuoteIdentifier(_viewModelMain.HluDataset.exports_fields.field_ordinalColumn.ColumnName)));
// Exit if there are no export fields for this format
// (this should not be possible).
if (_viewModelMain.HluDataset.exports_fields.Count == 0)
throw new Exception(String.Format("No export fields are defined for format '{0}'",
_viewModelMain.HluDataset.exports.FindByexport_id(userExportId).export_name));
//---------------------------------------------------------------------
// FIX: 041 Check the selected export format contains the incid column.
//
// Exit if there is no incid field for this format.
if (_viewModelMain.HluDataset.exports_fields.Count(f => f.column_name == _viewModelMain.IncidTable.incidColumn.ColumnName) == 0)
throw new Exception(String.Format("The export format '{0}' does not contain the column 'incid'",
_viewModelMain.HluDataset.exports.FindByexport_id(userExportId).export_name));
//---------------------------------------------------------------------
// Build a new export data table and also determine the
// number of output fields, a string of the output field
// names, the from SQL clause, and the incid
// field ordinal.
DataTable exportTable;
int[][] fieldMapTemplate;
StringBuilder targetList;
StringBuilder fromClause;
int[] sortOrdinals;
int[] matrixOrdinals;
int[] formationOrdinals;
int[] managementOrdinals;
int[] complexOrdinals;
int[] bapOrdinals;
int[] sourceOrdinals;
List<ExportField> exportFields = new List<ExportField>();
ExportJoins(tableAlias, ref exportFields, out exportTable,
out fieldMapTemplate, out targetList, out fromClause, out sortOrdinals,
out matrixOrdinals, out formationOrdinals, out managementOrdinals, out complexOrdinals,
out bapOrdinals, out sourceOrdinals);
// Add the length of the GIS layer fields to the length of
// field attributes exported from the database (excluding
// the incid field which is already included).
_attributesLength += 93;
// Set the export filter conditions, depending if all the
// records are to be exported or only the selected features.
List<List<SqlFilterCondition>> exportFilter = null;
if (selectedOnly)
{
// If the where clause is not already set then get it
// using the GIS selection.
if ((_viewModelMain.IncidSelectionWhereClause == null) &&
(_viewModelMain.GisSelection != null) && (_viewModelMain.GisSelection.Rows.Count > 0))
{
_viewModelMain.IncidSelectionWhereClause = ViewModelWindowMainHelpers.GisSelectionToWhereClause(
_viewModelMain.GisSelection.Select(), _viewModelMain.GisIDColumnOrdinals,
250, _viewModelMain.HluDataset.incid);
// Set the export filter to the where clause.
exportFilter = _viewModelMain.IncidSelectionWhereClause;
}
else
{
// Combine all the where clauses into a single list (so
// that it can be re-chunked later into larger chunks
// than the standard chunk based on the IncidPageSize.
exportFilter = new List<List<SqlFilterCondition>>();
exportFilter.Add(_viewModelMain.IncidSelectionWhereClause.SelectMany(l => l).ToList());
}
}
else
{
SqlFilterCondition cond = new SqlFilterCondition("AND",
_viewModelMain.IncidTable, _viewModelMain.IncidTable.incidColumn, null);
cond.Operator = "IS NOT NULL";
exportFilter = new List<List<SqlFilterCondition>>(new List<SqlFilterCondition>[] {
new List<SqlFilterCondition>(new SqlFilterCondition[] { cond }) });
}
//---------------------------------------------------------------------
// FIX: 042 Warn the user when the export will be very large.
//
// Count the number of incids to be exported.
int rowCount = 0;
if (selectedOnly)
rowCount = _viewModelMain.IncidsSelectedMapCount;
else
rowCount = _viewModelMain.IncidRowCount(false);
// Warn the user if the export is very large.
if (rowCount > 5000)
{
MessageBoxResult userResponse = MessageBoxResult.No;
userResponse = MessageBox.Show("This export operation may take some time.\n\nDo you wish to proceed?", "HLU: Export",
MessageBoxButton.YesNo, MessageBoxImage.Question);
// Cancel the export.
if (userResponse != MessageBoxResult.Yes)
return;
}
//---------------------------------------------------------------------
// Export the attribute data to a temporary database.
int exportRowCount;
tempPath = ExportMdb(targetList.ToString(), fromClause.ToString(), exportFilter,
_viewModelMain.DataBase, exportFields, exportTable, sortOrdinals,
matrixOrdinals, formationOrdinals, managementOrdinals, complexOrdinals, bapOrdinals, sourceOrdinals,
fieldMapTemplate, out exportRowCount);
// Exit if there was an error creating the database or the
// database is empty.
if (String.IsNullOrEmpty(tempPath))
return;
_viewModelMain.ChangeCursor(Cursors.Wait, "Exporting from GIS ...");
// Call the GIS application export method to join the
// temporary attribute data to the GIS feature layer
// and save them as a new GIS layer.
_viewModelMain.GISApplication.Export(tempPath, exportTable.TableName, _attributesLength, selectedOnly);
// Remove the current record filter.
//_viewModelMain.IncidSelection = null;
//_viewModelMain.GisSelection = null;
//_viewModelMain.OnPropertyChanged("IsFiltered");
//_viewModelMain.OnPropertyChanged("StatusIncid");
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Export failed. The error message was:\n\n{0}.",
ex.Message), "HLU: Export", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if (!String.IsNullOrEmpty(tempPath))
{
string[] tempFiles = Directory.GetFiles(Path.GetDirectoryName(tempPath),
Path.GetFileNameWithoutExtension(tempPath) + ".*");
foreach (string fName in tempFiles)
{
try { File.Delete(fName); }
catch { _viewModelMain.ExportMdbs.Add(fName); }
}
}
_viewModelMain.ChangeCursor(Cursors.Arrow, null);
}
}
/// <summary>
/// Following a feature split performed by the user with regular GIS tools, updates the toid_fragment_id of
/// the newly created feature(s), incrementing from the highest toid_fragment_id in use for that toid, which is
/// passed in as parameter lastToidFragmentID. toid_fragment_id is an integer stored as varchar(5) with
/// leading zeros (.ToString("D5")).
/// </summary>
/// <param name="lastToidFragmentID">Highest toid_fragment_id for the selected toid.</param>
/// <param name="selectionWhereClause">Where clause underlying the current selection set.</param>
/// <param name="historyColumns">All columns of the GIS layer structure except the geometry property columns.</param>
/// <returns>DataTable with the columns in the historyColumns parameter, containing the attributes of all
/// the features resulting from the split.
/// The first row corresponds to the original feature (the one that was split).</returns>
public override DataTable SplitFeature(string lastToidFragmentID,
List<SqlFilterCondition> selectionWhereClause, DataColumn[] historyColumns)
{
bool rollbackChanges = false;
try
{
if (String.IsNullOrEmpty(_selName)) _selName = _mapInfoApp.Eval(String.Format(
"SelectionInfo({0})", (int)MapInfoConstants.SelectionInfo.SEL_INFO_TABLENAME));
if (String.IsNullOrEmpty(_selName) || !TableExists(_selName)) return null;
string origSelName = _selName;
DataTable selTable = CreateHistoryTable(!String.IsNullOrEmpty(_selName) &&
_mapInfoApp.Eval(String.Format("SelectionInfo({0})",
(int)MapInfoConstants.SelectionInfo.SEL_INFO_TABLENAME)) ==
_hluLayer ? _selName : _hluLayer, false, historyColumns);
ChangeWhereClauseTable(new KeyValuePair<DataTable, DataTable>[] { new KeyValuePair<DataTable, DataTable>(_hluLayerStructure, selTable) },
ref selectionWhereClause);
// get history data for selected features
DataTable historyTable = SqlSelect(false, false, selTable.Columns.Cast<DataColumn>().ToArray(),
_selName, true, false, selectionWhereClause, null);
if ((historyTable == null) || (historyTable.Rows.Count < 2)) return null;
// update toid_fragment_id
int selNum = Int32.Parse(_mapInfoApp.Eval(String.Format("SelectionInfo({0})",
(int)MapInfoConstants.SelectionInfo.SEL_INFO_NROWS)));
string numFormat = String.Format("D{0}", lastToidFragmentID.Length);
int newToidFragmentIDnum = Int32.Parse(lastToidFragmentID);
string toidFragFieldName = GetFieldName(_hluLayerStructure.toid_fragment_idColumn.Ordinal);
rollbackChanges = true;
for (int i = 2; i <= selNum; i++)
_mapInfoApp.Do(String.Format("Update {0} Set {1} = {2} Where RowID = {3}", _selName,
toidFragFieldName, QuoteValue((++newToidFragmentIDnum).ToString(numFormat)), i));
rollbackChanges = false;
// remove cutter object from cosmetic layer
_mapInfoApp.Do(String.Format("Delete From LayerInfo({0}, 0, 1)", _hluMapWindowID));
// remove cutter object from HLU layer
DataTable cleanupTable = selTable.Clone();
cleanupTable.TableName = _hluLayer;
DataTable retTable = SqlSelect(true, false, cleanupTable.Columns.Cast<DataColumn>().ToArray(),
_hluLayer, false, false, new List<SqlFilterCondition>(new SqlFilterCondition[]
{ new SqlFilterCondition( cleanupTable, cleanupTable.Columns[_hluLayerStructure.incidColumn.ColumnName],
String.Empty) }), null);
if ((retTable != null) && !String.IsNullOrEmpty(_selName) && (_selName != origSelName) &&
TableExists(_selName) && (Int32.Parse(_mapInfoApp.Eval(String.Format("TableInfo({0}, {1})", _selName,
(int)MapInfoConstants.TableInfo.TAB_INFO_NROWS))) > 0))
{
_mapInfoApp.Do(String.Format("Delete From {0}", _selName));
_mapInfoApp.Do(String.Format("Close Table {0}", _selName));
}
CommitChanges();
// re-select split polygons (all rows in orignal selection)
List<SqlFilterCondition> reSelectionWhereClause;
var q = selectionWhereClause.Where(c => c.Column.ColumnName == _hluLayerStructure.toidColumn.ColumnName);
if (q.Count() == 1)
{
selTable = CreateHistoryTable(_hluLayer, false, historyColumns);
SqlFilterCondition cond = new SqlFilterCondition("AND", selTable,
_hluLayerStructure.toidColumn, q.ElementAt(0).Value);
cond.Table = selTable;
reSelectionWhereClause = new List<SqlFilterCondition>();
reSelectionWhereClause.Add(cond);
cond = new SqlFilterCondition("AND", selTable,
selTable.Columns[_hluLayerStructure.toid_fragment_idColumn.ColumnName], lastToidFragmentID);
cond.Operator = ">=";
reSelectionWhereClause.Add(cond);
cond = new SqlFilterCondition("AND", selTable, selTable.Columns[_hluLayerStructure.toid_fragment_idColumn.ColumnName],
newToidFragmentIDnum.ToString(numFormat));
cond.Operator = "<=";
reSelectionWhereClause.Add(cond);
SqlSelect(true, true, selTable.Columns.Cast<DataColumn>().ToArray(),
_hluLayer, false, false, reSelectionWhereClause, null);
}
else if (TableExists(origSelName))
{
reSelectionWhereClause = new List<SqlFilterCondition>();
SqlFilterCondition cond = selectionWhereClause[0];
cond.Operator = "IS NOT NULL";
cond.BooleanOperator = "AND";
cond.OpenParentheses = String.Empty;
cond.CloseParentheses = String.Empty;
selTable.TableName = origSelName;
reSelectionWhereClause.Add(cond);
retTable = SqlSelect(true, true, selTable.Columns.Cast<DataColumn>().ToArray(),
origSelName, false, false, reSelectionWhereClause, null);
}
if (TableExists(origSelName))
_mapInfoApp.Do(String.Format("Close Table {0}", origSelName));
return historyTable;
}
catch
{
if (rollbackChanges)
_mapInfoApp.RunCommand(String.Format("Rollback Table {0}", _hluLayer));
return null;
}
}
/// <summary>
/// Create a list of sql filter conditions using a table of selected Incids.
/// </summary>
/// <param name="incidSelection">A DataTable containing the selected Incids.</param>
/// <param name="gisApp">Which GIS system is being used.</param>
/// <returns>
/// A list of sql filter conditions.
/// </returns>
public static List <SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp, bool useIncidTable)
{
List <SqlFilterCondition> whereClause = new List <SqlFilterCondition>();
SqlFilterCondition cond = new SqlFilterCondition();
//StringBuilder incidList = new StringBuilder();
// Split the table of selected Incids into chunks of continuous Incids so
// that each chunk contains a continuous series of one or more Incids.
var query = incidSelection.AsEnumerable().Select((r, index) => new
{
RowIndex = RecordIds.IncidNumber(r.Field <string>(0)) - index,
Incid = r.Field <string>(0)
}).ChunkBy(r => r.RowIndex);
// Create a temporary list for storing some of the Incids.
List <string> inList = new List <string>();
// Determine which table to base the conditions on.
DataTable condTable;
if (useIncidTable)
{
condTable = _incidTable;
}
else
{
condTable = _incidMMTable;
}
// Loop through each chunk/series of Incids. If there are at least three
// then it is worth processing them within ">=" and "<=" operators (as
// this keeps the filter conditions short). If there are only one or two
// Incids in the chunk then add them to the temporary 'inList' for
// processing later.
foreach (var item in query)
{
if (item.Count() < 3)
{
if (gisApp != null)
{
inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
}
else
{
inList.AddRange(item.Select(t => t.Incid));
}
}
else
{
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidMMTable.incidColumn;
cond.Table = condTable;
cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
cond.Operator = ">=";
cond.Value = item.First().Incid;
cond.CloseParentheses = String.Empty;
whereClause.Add(cond);
cond = new SqlFilterCondition();
cond.BooleanOperator = "AND";
cond.OpenParentheses = String.Empty;
cond.Column = _incidMMTable.incidColumn;
cond.Table = condTable;
cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
cond.Operator = "<=";
cond.Value = item.Last().Incid;
cond.CloseParentheses = ")";
whereClause.Add(cond);
}
}
// Any Incids that are not part of a continuous series of at least
// three Incid must be queried using an "=" or "IN" sql operator.
// So loop through all these Incids and lump them together into
// strings of 254 Incids at a time so that each string can be used
// in an "IN" statement.
int i = 0;
while (i < inList.Count)
{
int numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
string[] oneList = new string[numElems];
inList.CopyTo(i, oneList, 0, numElems);
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidMMTable.incidColumn;
cond.Table = condTable;
cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
//---------------------------------------------------------------------
// FIX: 001 Improve speed of 'Select current Incid on Map'
// Use " INCID =" in SQL statement instrad of "INCID IN ()"
// if there is only on item in the list (as it is much quicker)
if (inList.Count == 1)
{
cond.Operator = "=";
}
else
{
cond.Operator = "IN ()";
}
//---------------------------------------------------------------------
cond.Value = String.Join(",", oneList);
cond.CloseParentheses = ")";
whereClause.Add(cond);
i += numElems;
}
return(whereClause);
}
