/// <summary>
/// Build map from query
/// </summary>
/// <param name="query"></param>
public static QueryResultsVoMap BuildFromQuery(
Query query,
bool includeKeyColsForAllTables = false)
{
QueryResultsVoMap qMap = new QueryResultsVoMap();
for (int qti = 0; qti < query.Tables.Count; qti++)
{
QueryTable qt = query.Tables[qti];
QueryTableVoMap tMap = new QueryTableVoMap();
qMap.Tables.Add(tMap);
tMap.Table = qt;
foreach (QueryColumn qc in qt.QueryColumns)
{
if (!qc.Is_Selected_or_GroupBy_or_Sorted)
{
continue; // must be selected, grouped or sorted on
}
if (!includeKeyColsForAllTables && qti > 0 && qc.IsKey)
{
continue; // don't include keys from tables other than the first
}
tMap.SelectedColumns.Add(qc);
}
}
return(qMap);
}
/// <summary>
/// Get the lowest available Spotfire export table index available
/// </summary>
/// <param name="rf"></param>
/// <returns></returns>
static int GetFreeSfxTableId(
QueryResultsVoMap voMap)
{
HashSet <int> inUse = new HashSet <int>();
foreach (QueryTableVoMap qtm in voMap.Tables)
{
QueryTable qt = qtm.Table;
int si = qt.SpotfireExportPos;
inUse.Add(si);
}
for (int free = 1; ; free++) // find free entry
{
if (!inUse.Contains(free))
{
return(free);
}
}
}
/// <summary>
/// Write results to single Spotfire data file merging results from all QueryTables in the query
/// Handles writing of both STDF and SBDF files
/// </summary>
/// <param name="query"></param>
/// <param name="Rows"></param>
/// <param name="ep"></param>
/// <returns></returns>
public string WriteMergedSpotfireDataFileForCombinedQueryTables(
Query query,
VoArrayList Rows,
ExportParms ep)
{
QueryTable qt;
QueryColumn qc, qcKey;
MetaTable mt;
MetaColumn mc, mcKey;
object vo, voKey;
string colName = "", molString = "";
int gci = 0;
int fileCount = 0;
int rowCount = 0;
Sff = SpotfireFileFormat.Text;
if (ep.ExportFileFormat == ExportFileFormat.Sbdf)
{
Sff = SpotfireFileFormat.Binary;
}
string fileName = ep.OutputFileName;
HashSet <MetaColumn> nValueMetaColumns = new HashSet <MetaColumn>();
string extraColNameSuffix = ColumnMapParms.SpotfireExportExtraColNameSuffix;
QueryResultsVoMap voMap = QueryResultsVoMap.BuildFromQuery(query);
//if (TextFormat)
//{
// if (!Lex.EndsWith(fileName, ".txt")) fileName += ".txt"; // needed for IIS use
//}
//else if (!Lex.EndsWith(fileName, ".bin")) fileName += ".bin"; // needed for IIS use
// Build the metadata for the table
SpotfireDataFileMetadataBuilder mdb = new SpotfireDataFileMetadataBuilder(Sff);
Dictionary <string, int> mtDict = // dictionary keyed on metatable name with the values incremented for each occurance of the metatable in the query
new Dictionary <string, int>(StringComparer.OrdinalIgnoreCase);
for (int ti = 0; ti < voMap.Tables.Count; ti++)
{
QueryTableVoMap qtMap = voMap.Tables[ti];
qt = qtMap.Table;
mt = qt.MetaTable;
mcKey = mt.KeyMetaColumn;
qcKey = qt.KeyQueryColumn;
if (!mtDict.ContainsKey(mt.Name))
{
mtDict.Add(mt.Name, 0);
}
mtDict[mt.Name]++;
string nameSuffix = "";
if (mtDict[mt.Name] > 1)
{
nameSuffix = "." + mtDict[mt.Name];
}
for (int fi = 0; fi < qtMap.SelectedColumns.Count; fi++)
{
qc = qtMap.SelectedColumns[fi];
mc = qc.MetaColumn;
if (ti > 0 && fi == 0 && mc.IsKey) // don't include keys for tables beyond the first
{
continue;
}
colName = mt.Name + "." + mc.Name + nameSuffix; // use internal mt.mc name
AddMetadataForColumn(qc, colName, extraColNameSuffix, mdb, ep, nValueMetaColumns);
}
} // table loop
SpotfireDataFileTableMetadata tableMetaData = mdb.Build(); // do build of metadata
FileUtil.DeleteFile(fileName);
SpotfireDataFileTableWriter tw = new SpotfireDataFileTableWriter(fileName, tableMetaData); // write the metadata to the stream
//String mdString = ""; // convert MD to readable string
//for (int mci = 0; mci < tableMetaData.Columns.Count; mci++)
//{
// StdfColumnMetadata cmd = tableMetaData.Columns[mci];
// mdString += mci.ToString() + ", " + cmd.Name + ", " + cmd.DataType.TypeName + "\r\n";
//}
// Write out the data for each row
for (int dri = 0; dri < Rows.TotalRowCount; dri++)
{
StdfWriteValueCount = 0;
StdfValueString = "";
object[] dr = Rows[dri];
string keyValueForRow = dr[KeyValueVoPos] as string; // get key value for row
// Process each table for row
for (int ti = 0; ti < voMap.Tables.Count; ti++)
{
QueryTableVoMap qtMap = voMap.Tables[ti];
qt = qtMap.Table;
mt = qt.MetaTable;
bool outputNValues = (QnSubcolumns.NValueIsSet(ep.QualifiedNumberSplit) && mt.UseSummarizedData); // should output n values for this table
int keyFieldPos = qt.KeyQueryColumn.VoPosition; // key field position in vo
string keyValueForTable = dr[keyFieldPos] as string; // get key value for table
bool noDataForTable = NullValue.IsNull(keyValueForTable); // if key not defined then no data for the table for this data table row
WriteRowValues(dr, qtMap, tw, ep, outputNValues, nValueMetaColumns, noDataForTable, ti, keyValueForRow);
} // table loop
rowCount++;
WriteValue(tw, null, null); // write end of line as appropriate
} // row loop
tw.Close();
fileCount++;
string response = "Data exported to file: " + fileName + "\r\n";
response +=
"- Data rows: " + rowCount;
return(response);
} // WriteMergedSpotfireDataFileForCombinedQueryTables
} // WriteMergedSpotfireDataFileForCombinedQueryTables
/// <summary>
/// Write results to individual Spotfire text data files
/// Handles writing of both STDF and SBDF files
/// </summary>
/// <param name="query"></param>
/// <param name="Rows"></param>
/// <param name="ep"></param>
/// <returns></returns>
public string WriteIndividualSpotfireDataFilesForEachQueryTable(
Query query,
VoArrayList Rows,
ExportParms ep)
{
QueryTable qt;
QueryColumn qc, qcKey;
MetaTable mt;
MetaColumn mc, mcKey;
SpotfireDataFileValueType sdfType;
object vo, vo2, voKey;
string outputFile = "", colName = "", molString = "";
int gci = 0;
int fileCount = 0;
int rowCount = 0;
Sff = SpotfireFileFormat.Text;
if (ep.ExportFileFormat == ExportFileFormat.Sbdf)
{
Sff = SpotfireFileFormat.Binary;
}
string baseOutputFileName = ep.OutputFileName;
bool outputNValues = QnSubcolumns.NValueIsSet(ep.QualifiedNumberSplit);
HashSet <MetaColumn> nValueMetaColumns = new HashSet <MetaColumn>();
string extraColNameSuffix = ColumnMapParms.SpotfireExportExtraColNameSuffix;
QueryResultsVoMap voMap = QueryResultsVoMap.BuildFromQuery(query, includeKeyColsForAllTables: true);
string baseStub = "";
int i1 = baseOutputFileName.IndexOf('.');
if (i1 >= 0)
{
baseStub = baseOutputFileName.Substring(0, i1);
}
else
{
baseStub = baseOutputFileName;
}
// Process each table
for (int ti = 0; ti < voMap.Tables.Count; ti++)
{
QueryTableVoMap qtMap = voMap.Tables[ti];
qt = qtMap.Table;
mt = qt.MetaTable;
mcKey = mt.KeyMetaColumn;
qcKey = qt.KeyQueryColumn;
outputFile = Lex.Replace(baseOutputFileName, baseStub, baseStub + "_" + mt.Name); // append meta table name to stub to get name of file to output
//if (TextFormat)
//{
// if (!Lex.EndsWith(outputFile, ".txt")) outputFile += ".txt"; // needed for IIS use
//}
//else if (!Lex.EndsWith(outputFile, ".bin")) outputFile += ".bin"; // needed for IIS use
SpotfireDataFileMetadataBuilder mdb = new SpotfireDataFileMetadataBuilder(Sff);
for (int fi = 0; fi < qtMap.SelectedColumns.Count; fi++)
{
qc = qtMap.SelectedColumns[fi];
mc = qc.MetaColumn;
sdfType = GetSpotfireDataFileType(mc);
qc.SpotfireExportType = sdfType;
colName = mt.Name + "." + mc.Name; // use internal mt.mc name
AddMetadataForColumn(qc, colName, extraColNameSuffix, mdb, ep, nValueMetaColumns);
}
SpotfireDataFileTableMetadata tableMetaData = mdb.Build(); // do build of metadata
// Write out the data for the table
FileUtil.DeleteFile(outputFile);
SpotfireDataFileTableWriter tw = new SpotfireDataFileTableWriter(outputFile, tableMetaData);
for (int dri = 0; dri < Rows.TotalRowCount; dri++)
{
object[] dr = Rows[dri];
int qcKeyValueVoPos = qcKey.VoPosition;
voKey = dr[qcKeyValueVoPos]; // get key value for row for this QueryTable
if (voKey == null)
{
continue;
}
voKey = MobiusDataType.ConvertToPrimitiveValue(voKey, mcKey);
if (NullValue.IsNull(voKey))
{
continue; // if key not defined then don't write anything for the row
}
WriteRowValues(dr, qtMap, tw, ep, outputNValues, nValueMetaColumns);
rowCount++;
WriteValue(tw, null, null); // write end of line as appropriate
} // row loop
tw.Close();
fileCount++;
} // table loop
string response;
if (fileCount == 1)
{
response =
"Data exported to file: " + outputFile + "\r\n";
}
else
{
response =
"Data exported to folder: " + baseOutputFileName + "\r\n" +
"- DataTable files: " + fileCount + "\r\n";
}
response +=
"- Data rows: " + rowCount;
return(response);
} // WriteIndividualSpotfireDataFilesForEachQueryTable