} // WriteIndividualSpotfireDataFilesForEachQueryTable
/// <summary>
/// Build and add the metadata for a column
/// </summary>
/// <param name="qc"></param>
/// <param name="nameSuffix"></param>
/// <param name="extraColNameSuffix"></param>
/// <param name="mdb"></param>
/// <param name="nValueMetaColumns"></param>
void AddMetadataForColumn(
QueryColumn qc,
string colName,
string extraColNameSuffix,
SpotfireDataFileMetadataBuilder mdb,
ExportParms ep,
HashSet <MetaColumn> nValueMetaColumns)
{
QueryTable qt = qc.QueryTable;
MetaTable mt = qt.MetaTable;
MetaColumn mc = qc.MetaColumn;
SpotfireDataFileValueType sdfType = GetSpotfireDataFileType(mc);
qc.SpotfireExportType = sdfType;
// Structures get added as string type columns but the ContentType property for the DataTable Columns should be set to
// chemical/x-mdl-molfile, chemical/x-mdl-chime or chemical/x-daylight-smiles
// Also for visualizations columns that display structures the renderer must be set to a structure renderer
// if the structure format is chime (molfile and smiles autodetect)
if (mc.DataType == MetaColumnType.Structure)
{
mdb.AddColumn(colName + extraColNameSuffix, sdfType);
}
// qualified number -> 1 to 3 Spotfire columns
else if (mc.DataType == MetaColumnType.QualifiedNo)
{
if (QnSubcolumns.IsSplitFormat(ep.QualifiedNumberSplit))
{
mdb.AddColumn(colName + "_PRFX_TXT" + extraColNameSuffix, SpotfireDataFileValueType.String); // qualifier
mdb.AddColumn(colName + extraColNameSuffix, SpotfireDataFileValueType.Double); // main value, use basic colname
if (mt.SummarizedExists && mt.UseSummarizedData)
{
mdb.AddColumn(colName + "_NBR_VALS_CNSDRD" + extraColNameSuffix, SpotfireDataFileValueType.Int); // number of values included if summary value
nValueMetaColumns.Add(mc);
}
}
else // combined QNs
{
mdb.AddColumn(colName + extraColNameSuffix, SpotfireDataFileValueType.String); // combined value
}
}
// Other column types use single column in Spotfire of the specified type
else
{
mdb.AddColumn(colName + extraColNameSuffix, sdfType);
}
return;
}
/// <summary>
/// Format qualified number
/// </summary>
/// <param name="qn"></param>
/// <param name="qc"></param>
/// <param name="mergedField"></param>
/// <param name="displayFormat"></param>
/// <param name="decimals"></param>
/// <param name="qnFormat"></param>
/// <param name="outputDest"></param>
/// <returns></returns>
public string Format(
QueryColumn qc,
bool mergedField,
ColumnFormatEnum displayFormat,
int decimals,
QnfEnum qnFormat,
OutputDest outputDest)
{
string result, uri, href, txt, tok;
result = "";
QualifiedNumber qn = this;
MetaColumn mc = qc.MetaColumn;
if (qn.Qualifier == null)
{
qn.Qualifier = "";
}
if (qn.TextValue == null)
{
qn.TextValue = "";
}
if (qn.Hyperlink == null)
{
qn.Hyperlink = "";
}
if (QnSubcolumns.IsCombinedFormat(qnFormat)) // normal combined number
{
if (qn.NumberValue == NullValue.NullNumber && qn.NValue == NullValue.NullNumber &&
qn.NValueTested == NullValue.NullNumber && qn.TextValue == "")
{ // null value
result = NullValueString;
if (outputDest == OutputDest.Html && (NullValueString == "" || NullValueString == " "))
{
result = "<br>"; // html blank
}
return(result);
}
result = qn.Format(displayFormat, decimals);
if (qn.NValue > 1 || qn.NValueTested > 1 || // format with sd, n
(qn.NValue >= 0 && qn.NValueTested > qn.NValue) || // number tested > n
(qn.NumberValue == NullValue.NullNumber && // also null numbers if some nonzero nvalue
(qn.NValue > 0 || qn.NValueTested > 0)))
{
txt = "";
if (qn.NValue > 1 && (qnFormat & QnfEnum.StdDev) != 0 && // include sd
(qn.StandardDeviation != NullValue.NullNumber ||
qn.StandardError != NullValue.NullNumber))
{ // format standard deviation
if ((qnFormat & QnfEnum.DisplayStdDevLabel) != 0)
{
txt += "sd=";
}
if (qn.StandardDeviation == NullValue.NullNumber) // calc sd from se if don't have
{
qn.StandardDeviation = qn.StandardError * Math.Sqrt(qn.NValue);
}
txt += FormatNumber(qn.StandardDeviation, displayFormat, decimals);
}
if (qn.NValue > 1 && (qnFormat & QnfEnum.StdErr) != 0 && // include se
(qn.StandardError != NullValue.NullNumber ||
qn.StandardDeviation != NullValue.NullNumber))
{ // format standard error
if (txt != "")
{
txt += ", ";
}
if ((qnFormat & QnfEnum.DisplayStdErrLabel) != 0)
{
txt += "se=";
}
txt += FormatNumber(qn.StandardError, displayFormat, decimals);
}
if (qn.NValue != NullValue.NullNumber &&
(qnFormat & QnfEnum.NValue) != 0)
{ // format n value
if (txt != "")
{
txt += ", ";
}
if ((qnFormat & QnfEnum.DisplayNLabel) != 0)
{
txt += "n=";
}
txt += qn.NValue.ToString();
if (qn.NValueTested != NullValue.NullNumber && qn.NValueTested != qn.NValue)
{
txt += "/" + qn.NValueTested.ToString(); // add number tested if different
}
}
if (txt != "")
{
result += " (" + txt + ")"; // build complete string
}
}
if (qn.DbLink != null && qn.DbLink != "" && qn.DbLink != "." && !mergedField && // do we have an associated resultId
(outputDest == OutputDest.WinForms || outputDest == OutputDest.Html))
{
uri = qn.FormatHyperlink(qc);
//if (outputDest == OutputDest.Grid) // store link info in separate field
// qn.Hyperlink = uri;
//else if (outputDest == OutputDest.Html) // build full html tag
//{
if (uri != "")
{
result = result = "<a href=" + Lex.Dq(uri) + ">" + result + "</a>";
}
//}
}
}
// Format one piece of a split qualified number
else if ((qnFormat & QnfEnum.Qualifier) != 0) // qualifier
{
result = qn.Qualifier;
}
else if ((qnFormat & QnfEnum.NumericValue) != 0) // basic number
{
if (qn.NumberValue != NullValue.NullNumber)
{
result = FormatNumber(qn.NumberValue, displayFormat, decimals);
}
else
{
result = null;
}
//else (obsolete, don't output any text values into numeric column, add option for separate column later)
//{
// if (qn.NValue != NullValue.NullNumber)
// result = "ND"; // some NValue, return not determined
// else result = qn.TextValue; // return any text value
//}
}
else if ((qnFormat & QnfEnum.StdDev) != 0) // standard deviation
{
if (qn.StandardDeviation != NullValue.NullNumber)
{
result = FormatNumber(qn.StandardDeviation, displayFormat, decimals);
}
}
else if ((qnFormat & QnfEnum.StdErr) != 0) // standard error
{
if (qn.StandardError != NullValue.NullNumber)
{
result = FormatNumber(qn.StandardError, displayFormat, decimals);
}
}
else if ((qnFormat & QnfEnum.NValue) != 0) // N
{
if (qn.NValue != NullValue.NullNumber)
{
result = qn.NValue.ToString();
}
}
else if ((qnFormat & QnfEnum.NValueTested) != 0) // number tested
{
if (qn.NValueTested != NullValue.NullNumber)
{
result = qn.NValueTested.ToString();
}
}
else if ((qnFormat & QnfEnum.TextValue) != 0) // text value
{
if (!Lex.IsNullOrEmpty(qn.TextValue))
{
result = qn.TextValue;
}
}
return(result);
}
/// <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
/// <summary>
/// Write out the values for a data row
/// </summary>
/// <param name="dr"></param>
/// <param name="qtMap"></param>
/// <param name="tw"></param>
/// <param name="ep"></param>
/// <param name="outputNValues"></param>
/// <param name="nValueMetaColumns"></param>
/// <param name="noDataForTable"></param>
/// <param name="ti"></param>
/// <param name="keyValueForRow"></param>
void WriteRowValues(
object[] dr,
QueryTableVoMap qtMap,
SpotfireDataFileTableWriter tw,
ExportParms ep,
bool outputNValues,
HashSet <MetaColumn> nValueMetaColumns,
bool noDataForTable = false,
int ti = 0,
string keyValueForRow = "")
{
QueryColumn qc;
MetaColumn mc;
SpotfireDataFileValueType sdfType;
QualifiedNumber qn;
object vo, vo2;
double dVal;
string txt;
for (int fi = 0; fi < qtMap.SelectedColumns.Count; fi++)
{
qc = qtMap.SelectedColumns[fi];
mc = qc.MetaColumn;
sdfType = (SpotfireDataFileValueType)qc.SpotfireExportType;
if (noDataForTable && ti == 0 && qc.IsKey) // if this is the root table and no data then supply the row key value
{
vo = keyValueForRow;
noDataForTable = false; // now have data
}
if (noDataForTable)
{
vo = null;
}
else
{
vo = dr[qc.VoPosition];
}
bool isNull = NullValue.IsNull(vo);
if (isNull && (mc.DataType != MetaColumnType.QualifiedNo)) // write null value (unless QN which may require multiple value writes)
{
WriteValue(tw, sdfType, null);
}
else if (mc.DataType == MetaColumnType.Structure)
{
if (vo is MoleculeMx)
{
string molString = GetMoleculeString(vo, ep.ExportStructureFormat);
WriteValue(tw, sdfType, molString);
}
else
{
vo2 = MobiusDataType.ConvertToPrimitiveValue(vo, mc);
WriteValue(tw, sdfType, vo2);
}
}
else if (mc.DataType == MetaColumnType.QualifiedNo) // write 1-3 values for Qualified number
{
// Output a split QN
if (QnSubcolumns.IsSplitFormat(ep.QualifiedNumberSplit))
{
if (vo is QualifiedNumber && !isNull) // regular QN
{
qn = (QualifiedNumber)vo;
WriteValue(tw, SpotfireDataFileValueType.String, qn.Qualifier); // qualifier
WriteValue(tw, SpotfireDataFileValueType.Double, qn.NumberValue);
if (outputNValues && nValueMetaColumns.Contains(mc))
{
if (NullValue.IsNull(qn.NValueTested))
{
WriteValue(tw, SpotfireDataFileValueType.Int, null); // number in calc
}
else
{
WriteValue(tw, SpotfireDataFileValueType.Int, qn.NValueTested);
}
}
}
else if (!isNull) // non-qn
{
WriteValue(tw, SpotfireDataFileValueType.String, null); // qualifier
if (QualifiedNumber.TryConvertToDouble(vo, out dVal))
{
WriteValue(tw, SpotfireDataFileValueType.Double, dVal);
}
else
{
WriteValue(tw, SpotfireDataFileValueType.Double, null);
}
if (outputNValues && nValueMetaColumns.Contains(mc))
{
WriteValue(tw, SpotfireDataFileValueType.Int, null); // N value
}
}
else // null value
{
WriteValue(tw, SpotfireDataFileValueType.String, null); // qualifier
WriteValue(tw, SpotfireDataFileValueType.Double, null); // value
if (outputNValues && nValueMetaColumns.Contains(mc))
{
WriteValue(tw, SpotfireDataFileValueType.Int, null); // N value
}
}
}
// Output a non-split (combined) QN
else // combined
{
if (isNull)
{
WriteValue(tw, SpotfireDataFileValueType.String, null);
}
else if (vo is QualifiedNumber && !isNull) // regular QN
{
qn = (QualifiedNumber)vo;
txt = qn.Format(qc, false, mc.Format, mc.Decimals, ep.QualifiedNumberSplit, false);
WriteValue(tw, SpotfireDataFileValueType.String, txt);
}
else if (!isNull) // non-qn
{
txt = vo.ToString();
WriteValue(tw, SpotfireDataFileValueType.String, txt);
}
else // null value
{
WriteValue(tw, SpotfireDataFileValueType.String, null);
}
}
}
else // write other types as primitive value for now
{
vo2 = MobiusDataType.ConvertToPrimitiveValue(vo, mc);
WriteValue(tw, sdfType, vo2);
}
} // col loop
return;
}
} // 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
