public static DateTime GetDateVo(
object[] vo,
int voi)
{
if (vo == null || voi < 0 || voi >= vo.Length)
{
return(DateTime.MinValue);
}
object obj = vo[voi];
if (NullValue.IsNull(obj))
{
return(DateTime.MinValue);
}
else if (obj is DateTime)
{
return((DateTime)obj);
}
else if (obj is DateTimeMx)
{
return((obj as DateTimeMx).Value);
}
else
{
throw new Exception("Unexpected type: " + obj.GetType().ToString());
}
}
/// <summary>
/// Try to convert a vo to a numeric qualifier
/// </summary>
/// <param name="vo"></param>
/// <param name="qualifier"></param>
/// <returns></returns>
public static bool TryConvertToQualifier(
object vo,
out string qualifier)
{
qualifier = null;
if (vo == null || vo is DBNull || NullValue.IsNull(vo))
{
return(false);
}
string s = vo.ToString().Trim();
if (s == "" || s == "=")
{
qualifier = "";
}
else if (s == "<" || s == "<=")
{
qualifier = "<";
}
else if (s == ">" || s == ">=")
{
qualifier = ">";
}
else
{
return(false);
}
return(true);
}
/// <summary>
/// Convert the specified object to the corresponding MobiusDataType
/// </summary>
/// <param name="o"></param>
/// <returns></returns>
public static StringMx ConvertTo(
object o)
{
StringMx sx;
if (o is StringMx)
{
return((StringMx)o);
}
else if (NullValue.IsNull(o))
{
return(new StringMx());
}
else if (o is MobiusDataType)
{
MobiusDataType mdt = o as MobiusDataType;
sx = new StringMx(mdt.ToString());
mdt.MemberwiseCopy(sx);
return(sx);
}
else
{
return(new StringMx(o.ToString()));
}
}
public static int GetIntVo(
object[] vo,
int voi)
{
if (vo == null || voi < 0 || voi >= vo.Length)
{
return(NullValue.NullNumber);
}
object obj = vo[voi];
if (NullValue.IsNull(obj))
{
return(NullValue.NullNumber);
}
else if (obj is decimal)
{
return(decimal.ToInt32((decimal)obj));
}
else if (obj is int)
{
return((int)obj);
}
else if (obj is MobiusDataType)
{
return((int)(obj as MobiusDataType).NumericValue);
}
else
{
throw new Exception("Unexpected type: " + obj.GetType().ToString());
}
}
/// <summary>
/// General constructor from an object
/// </summary>
/// <param name="o"></param>
public QualifiedNumber(
object o)
{
if (NullValue.IsNull(o))
{
return;
}
if (o is int)
{
NumberValue = (int)o;
}
else if (o is long)
{
NumberValue = (long)o;
}
else if (o is float)
{
NumberValue = (float)o;
}
else if (o is double)
{
NumberValue = (double)o;
}
else if (o is decimal)
{
NumberValue = decimal.ToDouble((decimal)o);
}
else if (o is NumberMx)
{
NumberValue = ((NumberMx)o).NumericValue;
}
else if (o is StringMx)
{
TextValue = ((StringMx)o).Value;
}
else if (o is string)
{
if (!TryParse((string)o, this))
{
throw new Exception("Invalid qualified number: " + (string)o);
}
}
else
{
throw new InvalidCastException(o.GetType().ToString());
}
}
/// <summary>
/// Convert the specified object to the corresponding MobiusDataType
/// </summary>
/// <param name="o"></param>
/// <returns></returns>
public static ImageMx ConvertTo(
object o)
{
if (o is ImageMx)
{
return((ImageMx)o);
}
else if (NullValue.IsNull(o))
{
return(new ImageMx());
}
else
{
return(new ImageMx(o.ToString())); // create image comtaining the link info
}
}
/// <summary>
/// Convert the specified object to the corresponding MobiusDataType
/// </summary>
/// <param name="o"></param>
/// <returns></returns>
public static CompoundId ConvertTo(
object o)
{
if (o is CompoundId)
{
return((CompoundId)o);
}
else if (NullValue.IsNull(o))
{
return(new CompoundId());
}
else if (o is string)
{
return(new CompoundId((string)o));
}
throw new InvalidCastException(o.GetType().ToString());
}
/// <summary>
/// Compare two vo field values
/// </summary>
/// <param name="direction"></param>
/// <param name="x"></param>
/// <param name="y"></param>
/// <returns></returns>
public static int CompareValues(
object x,
object y,
SortOrder direction)
{
int result;
bool reverseResult = (direction == SortOrder.Descending);
if (NullValue.IsNull(x))
{
x = null; // set any MobiusDataType null to real null
}
if (NullValue.IsNull(y))
{
y = null;
}
if (x != null && y != null) // both not null
{
result = ((IComparable)x).CompareTo((IComparable)y);
if (reverseResult)
{
result = -result;
}
return(result);
}
else if (x == null && y == null) // both null, say equal
{
return(0);
}
else if (x != null) // x not null, y is null, put x first
{
return(-1); // say x < y, non-null always comes before null
}
else // x is null, y not null, put y first
{
return(1); // x > y, non-null always comes before null
}
}
public static string GetStringVo(
object[] vo,
int voi)
{
if (vo == null || voi < 0 || voi >= vo.Length)
{
return(null);
}
object obj = vo[voi];
if (NullValue.IsNull(obj))
{
return(null);
}
else
{
return(obj.ToString());
}
}
/// <summary>
/// Convert the specified object to the corresponding MobiusDataType
/// </summary>
/// <param name="o"></param>
/// <returns></returns>
public static DateTimeMx ConvertTo(
object o)
{
if (o is DateTimeMx)
{
return((DateTimeMx)o);
}
else if (NullValue.IsNull(o))
{
return(new DateTimeMx());
}
else if (o is DateTime)
{
return(new DateTimeMx((DateTime)o));
}
else if (o is string)
{
return(new DateTimeMx((string)o));
}
throw new InvalidCastException(o.GetType().ToString());
}
/// <summary>
/// General constructor from an object
/// </summary>
/// <param name="o"></param>
public NumberMx(
object o)
{
if (NullValue.IsNull(o))
{
return;
}
if (o is int)
{
Value = (int)o;
}
else if (o is double)
{
Value = (double)o;
}
else if (o is string)
{
if (!double.TryParse((string)o, out Value))
{
throw new Exception("Invalid number: " + (string)o);
}
}
else if (o is QualifiedNumber)
{
QualifiedNumber qn = o as QualifiedNumber;
Value = qn.NumberValue;
((MobiusDataType)o).MemberwiseCopy(this); // copy base values
}
else
{
throw new InvalidCastException(o.GetType().ToString());
}
}
/// <summary>
/// Convert MobiusDataType value or to a primitive type or just return value if not a MDT
/// (does this add anything over the ConvertToPrimitiveValue method above?)
/// </summary>
/// <param name="vo"></param>
/// <param name="mc"></param>
/// <returns></returns>
public static object ConvertToPrimitiveValue(
object vo,
MetaColumn mc)
{
MobiusDataType mdt;
DateTimeMx dex;
string cid;
int iVal;
double dVal, d2;
object value = null;
if (NullValue.IsNull(vo))
{
return(null);
}
if (mc.DataType == MetaColumnType.CompoundId)
{
cid = vo.ToString();
if (mc.IsNumeric)
{
if (int.TryParse(cid, out iVal))
{
value = iVal;
}
}
else
{
value = cid;
}
}
else if (mc.IsNumeric)
{
mdt = vo as MobiusDataType;
if (mdt != null)
{
dVal = mdt.NumericValue;
}
else if (vo is int)
{
dVal = (int)vo;
}
else if (vo is double)
{
dVal = (double)vo;
}
else if (vo is string && double.TryParse((string)vo, out d2))
{
dVal = d2;
}
else
{
throw new Exception("Unexpected type: " + vo.GetType());
}
if (mc.DataType == MetaColumnType.Integer)
{
value = (int)dVal;
}
else
{
value = dVal;
}
}
else if (mc.DataType == MetaColumnType.Date)
{
dex = vo as DateTimeMx;
if (dex != null)
{
value = dex.Value.Date; // ignore time component
}
else if (vo is DateTime)
{
value = ((DateTime)vo).Date;
}
else
{
throw new Exception("Unexpected type: " + vo.GetType());
}
}
else
{
value = vo.ToString(); // convert everything else to a string
}
return(value);
}
/// <summary>
/// Convert a value object[] into a ResultsRow
/// </summary>
/// <param name="vo"></param>
/// <param name="voi"></param>
/// <returns></returns>
new public void FromValueObject(
object[] vo,
UnpivotedAssayResultFieldPositionMap voMap)
{
string txt;
int i1;
double d1;
DateTime dt;
base.FromValueObject(vo, voMap);
UnpivotedAssayResult row = this;
row.CompoundId = GetStringVo(vo, voMap.CompoundId.Voi);
row.Structure = GetStructureVo(vo, voMap.Structure.Voi);
row.ActivityBin = GetIntVo(vo, voMap.ActivityBin.Voi);
row.ActivityClass = GetSxVo(vo, voMap.ActivityClass.Voi);
row.ResultValue = GetQnVo(vo, voMap.ResultValueQn.Voi);
if (!NullValue.IsNull(row.ResultValue))
{ // copy values to individual fields in case not in the vo
row.ResultQualifier = row.ResultValue.Qualifier;
row.ResultNumericValue = row.ResultValue.NumberValue;
row.ResultTextValue = row.ResultValue.TextValue;
row.NValue = row.ResultValue.NValue;
row.NValueTested = row.ResultValue.NValueTested;
row.StdDev = row.ResultValue.StandardDeviation;
row.StdErr = row.ResultValue.StandardError;
}
txt = GetStringVo(vo, voMap.ResultQualifier.Voi);
if (txt != null)
{
row.ResultQualifier = txt;
}
d1 = GetDoubleVo(vo, voMap.ResultNumericValue.Voi);
if (d1 != NullValue.NullNumber)
{
row.ResultNumericValue = d1;
}
txt = GetStringVo(vo, voMap.ResultTextValue.Voi);
if (txt != null)
{
row.ResultTextValue = txt;
}
i1 = GetIntVo(vo, voMap.NValue.Voi);
if (d1 != NullValue.NullNumber)
{
row.NValue = i1;
}
i1 = GetIntVo(vo, voMap.NValueTested.Voi);
if (i1 != NullValue.NullNumber)
{
row.NValueTested = i1;
}
d1 = GetDoubleVo(vo, voMap.StdDev.Voi);
if (d1 != NullValue.NullNumber)
{
row.StdDev = d1;
}
row.Units = GetStringVo(vo, voMap.Units.Voi);
row.Conc = GetDoubleVo(vo, voMap.Conc.Voi);
row.ConcUnits = GetStringVo(vo, voMap.ConcUnits.Voi);
row.RunDate = GetDateVo(vo, voMap.RunDate.Voi);
row.MostPotentVal = GetQnVo(vo, voMap.MostPotentValueQn.Voi);
row.ActivityBinMostPotent = GetIntVo(vo, voMap.ActivityBinMostPotent.Voi);
row.ResultDetail = GetStringVo(vo, voMap.ResultDetailId.Voi);
//row.TargetMapX = GetIntVo(vo, voMap.TargetMapX.Voi);
//row.TargetMapY = GetIntVo(vo, voMap.TargetMapY.Voi);
row.Sources = GetStringVo(vo, voMap.Sources.Voi);
row.DrawingOrder = GetDoubleVo(vo, voMap.DrawingOrder.Voi);
row.CidOrder = GetIntVo(vo, voMap.CidOrder.Voi);
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
/// <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;
}
/// <summary>
/// Convert to just a double value and optional qualifier
/// </summary>
/// <param name="vo"></param>
/// <param name="dVal"></param>
/// <param name="qualifier"></param>
/// <returns></returns>
public static bool TryConvertToQualifiedDouble(
object vo,
out double dVal,
out string qualifier)
{
QualifiedNumber qn;
string s = null;
dVal = NullValue.NullNumber;
qualifier = null;
if (vo == null || vo is DBNull || NullValue.IsNull(vo))
{
return(false);
}
qualifier = ""; // provide a blank qualifier if not null
if (vo is double)
{
dVal = (double)vo;
}
else if (vo is float)
{
dVal = (float)vo;
}
else if (vo is decimal)
{
dVal = decimal.ToDouble((decimal)vo);
}
else if (vo is QualifiedNumber)
{
qn = (QualifiedNumber)vo;
dVal = qn.NumberValue;
if (!String.IsNullOrWhiteSpace(qn.Qualifier))
{
qualifier = qn.Qualifier;
}
}
else if (vo is NumberMx)
{
NumberMx nmx = (NumberMx)vo;
dVal = nmx.NumericValue;
}
else if (vo is int)
{
dVal = (int)vo;
}
else if (vo is Int16)
{
dVal = (Int16)vo;
}
else if (vo is Int64)
{
dVal = (Int64)vo;
}
else if (vo is byte)
{
dVal = (byte)vo;
}
else // try to convert to number
{
s = vo.ToString();
if (QualifiedNumber.TryParse(s, out qn))
{
dVal = qn.NumberValue;
if (!String.IsNullOrWhiteSpace(qn.Qualifier))
{
qualifier = qn.Qualifier;
}
}
else
{
return(false); // null
}
}
return(true);
}
/// <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>
/// Sort a dataset
/// </summary>
/// <param name="results">Data to sort</param>
/// <param name="sortColumns">Columns to sort on</param>
/// <param name="query">Query information with vo positions of sort cols</param>
/// <param name="keyValueVoPos">Index of added key value</param>
/// <param name="ResultKeys">Ordered keys are returned here</param>
/// <returns></returns>
///
public List <object[]> Sort(
List <object[]> results,
List <SortColumn> sortColumns,
Query query,
int keyValueVoPos,
out List <string> ResultKeys)
{
object [] vo, vo2;
int qti, ri, ti, sci, i1, i2, i3;
SortItem currentVal = null; // current top composite value (high or low) for current key
object value;
object [] sourceVo, destVo;
string key;
SortItem sItem = null, sItem2;
int keyOffset = 0; // pk is stored in first element of vo and must be offset
ValidateSortColumns(sortColumns);
// Get array of sort columns associated with each metatable. This will be
// used in the sort routine to do the local sorts in the context of
// each table
bool[][] mtSortCols = new bool[query.Tables.Count][];
MetaTableSortItems mtsi;
int mtsiIdx;
int firstQtWithSorting = -1;
for (sci = 0; sci < sortColumns.Count; sci++)
{
QueryColumn qc = sortColumns[sci].QueryColumn;
for (qti = 0; qti < query.Tables.Count; qti++)
{
// if (Query.Tables[qti] == qc.QueryTable) break; // (fails for preview)
if (Lex.Eq((query.Tables[qti]).MetaTable.Name, qc.QueryTable.MetaTable.Name))
{
break; // todo: properly handle when same metatable in query multiple times
}
}
if (qti >= query.Tables.Count)
{
throw new Exception("Sort column not found in table");
}
if (mtSortCols[qti] == null) // allocate array first time
{
mtSortCols[qti] = new bool[sortColumns.Count];
}
mtSortCols[qti][sci] = true;
if (firstQtWithSorting < 0)
{
firstQtWithSorting = qti;
}
}
MultiColumnSortComparer sortComp = // sort including context of 1st table with sorting
new MultiColumnSortComparer(sortColumns, mtSortCols[firstQtWithSorting]);
// Build the array of values to be sorted. Each element of the array consists of
// one or more primary values (i.e. the max or min value for each compound id
// depending on the SortDirection for the column), the compound id and
// one or more secondary values (i.e. the actual value for the sort key for that row)
string currentKey = "";
int firstRowForKey = -1, rowsForKey;
List <object> sArray = new List <object>();
object[] voResults = null;
for (ri = 0; ri < results.Count; ri++)
{
vo = (object [])results[ri];
key = (string)vo[keyValueVoPos];
sItem = new SortItem();
sItem.SortValue = new IComparable[sortColumns.Count];
sItem.SortValueB = new IComparable[sortColumns.Count];
sArray.Add(sItem);
sItem.Key = key;
sItem.TupleIndex = ri;
for (sci = 0; sci < sortColumns.Count; sci++)
{ // copy the primary & secondary values from result buffer into sort value array
QueryColumn qc = sortColumns[sci].QueryColumn;
SortOrder direction = sortColumns[sci].Direction;
if (qc.IsKey) // if key be sure to get non-null value
{
sItem.SortValue[sci] = sItem.SortValueB[sci] = key;
}
else // copy non-key values
{
object o = null;
if (qc.VoPosition >= 0 && qc.VoPosition < vo.Length) // be sure in range
{
o = vo[qc.VoPosition]; // get primitive or Mobius data type
}
else if (NullValue.IsNull(o))
{
o = null;
}
else if (o is MobiusDataType) // convert Mobius types to a comparable primitive type
{
if (o is NumberMx)
{
o = (o as NumberMx).Value;
}
else if (o is QualifiedNumber)
{
QualifiedNumber qn = o as QualifiedNumber;
if (MetaColumn.IsNumericMetaColumnType(qc.MetaColumn.DataType))
{
o = qn.NumberValue;
}
else if (qc.MetaColumn.DataType == MetaColumnType.String)
{
o = qn.TextValue;
}
else
{
o = qn.NumberValue; // shouldn't happen
}
}
else if (o is StringMx)
{
o = (o as StringMx).Value;
}
else if (o is DateTimeMx)
{
o = (o as DateTimeMx).Value;
}
else if (o is CompoundId)
{
o = (o as CompoundId).Value;
}
}
else if (o is byte || o is sbyte || // convert all numbers to doubles so they compare properly (e.g. int that has cond formatting NumberMx values for some rows)
o is Int16 || o is Int32 || o is Int64 ||
o is float || o is decimal)
{
o = Convert.ToDouble(o);
}
if (!(o is IComparable))
{
o = null; // be sure it's a IComparable
}
sItem.SortValue[sci] = sItem.SortValueB[sci] = (IComparable)o;
}
}
if (key != null && key != currentKey) // new key value
{
firstRowForKey = ri;
currentKey = key;
currentVal = sItem;
}
else // another tuple for same key
{
if (sortComp.CompareSortItems(currentVal, sItem) > 0) // new primary value?
{
currentVal = sItem;
for (i1 = firstRowForKey; i1 < ri; i1++)
{ // reset primary value for preceeding rows for this key to the value of this row
sItem2 = (SortItem)sArray[i1];
for (sci = 0; sci < sortColumns.Count; sci++)
{
sItem2.SortValue[sci] = currentVal.SortValue[sci];
}
}
}
else // copy current primary values to this sort item
{
for (sci = 0; sci < sortColumns.Count; sci++)
{
sItem.SortValue[sci] = currentVal.SortValue[sci];
}
}
}
}
// Do the first sort which is overall for key order & relative for
// first table with sort columns
sArray.Sort(sortComp);
// Reorder arraylist of raw tuples according to initial search results
List <object[]> newResults = new List <object[]>(results.Count);
for (ri = 0; ri < sArray.Count; ri++)
{
sItem = (SortItem)sArray[ri];
newResults.Add(results[sItem.TupleIndex]);
}
// Setup range in Vo for each query table and its key
List <SortTableData> std = new List <SortTableData>();
foreach (QueryTable qt0 in query.Tables)
{
SortTableData st = new SortTableData();
std.Add(st);
foreach (QueryColumn qc0 in qt0.QueryColumns)
{
MetaColumn mc = qc0.MetaColumn;
if (mc.IsKey)
{
st.KeyColPos = qc0.VoPosition;
}
if (qc0.VoPosition >= 0 && st.FirstColumn < 0)
{
st.FirstColumn = qc0.VoPosition;
}
if (qc0.Selected)
{
st.SelectCount++;
}
}
}
// Do table-relative sort for other tables with sort fields
ArrayList CopyBuf = new ArrayList(); // used for copying
int voLen = 0; // length of vo
if (results != null && results.Count > 0 && results[0] != null)
{
voLen = ((object [])results[0]).Length;
}
for (ti = 0; ti < std.Count; ti++) // check all tables
{
int voTablePos = std[ti].FirstColumn; // +std[ti].KeyColPos + keyOffset; // position of first element of vo for table
int voTableLen = std[ti].SelectCount;
if (ti == firstQtWithSorting)
{
continue; // did this table first
}
else if (mtSortCols[ti] == null)
{
continue; // no sorting on this one
}
sortComp = // proper sort comparer for this table
new MultiColumnSortComparer(sortColumns, mtSortCols[ti]);
sArray.Sort(sortComp); // re-sort original results for this table
currentKey = "";
firstRowForKey = -1;
rowsForKey = 0;
for (ri = 0; ri <= sArray.Count; ri++) // reorder table vo entries for new results
{
if (ri < sArray.Count)
{
sItem = (SortItem)sArray[ri];
vo = (object [])results[sItem.TupleIndex]; // address the vo
key = (string)vo[keyValueVoPos];
}
else
{
key = "<end>"; // past end of data, force copy back of last chunk
}
if (key != null && key != currentKey) // new key value
{
if (firstRowForKey >= 0) // anything to copy back
{
for (i1 = 0; i1 < rowsForKey; i1++)
{ // copy back to results buffer in proper order
sourceVo = (Object[])CopyBuf[i1];
destVo = (Object[])newResults[firstRowForKey + i1];
Array.Copy(sourceVo, voTablePos, destVo, voTablePos, voTableLen);
}
}
if (ri >= sArray.Count)
{
break; // really done?
}
currentKey = key;
firstRowForKey = ri; // first row in newResults for the key
rowsForKey = 0;
}
if (CopyBuf.Count < rowsForKey + 1) // be sure copy buffer big enough
{
CopyBuf.Add(new object[voLen]);
}
sourceVo = (Object[])results[sItem.TupleIndex]; // copy from original results row to buffer
destVo = (Object[])CopyBuf[rowsForKey];
Array.Copy(sourceVo, voTablePos, destVo, voTablePos, voTableLen);
rowsForKey++;
} // end of loop on results entries
} // end of loop on query tables
// For each key shift non-null metatable data to top of section for key
ResultKeys = new List <string>(); // reorder result keys also
currentKey = "";
for (ri = 0; ri < newResults.Count; ri++)
{
vo = (object [])newResults[ri];
key = (string)vo[keyValueVoPos];
if (key != null && key != currentKey) // new key value
{
firstRowForKey = ri;
currentKey = key;
ResultKeys.Add(key);
}
//else // another row for key, shift up data as needed to fill gaps
{
if (query.Tables.Count <= 1)
{
continue; // only need to do if more than one table
}
for (ti = 0; ti < std.Count; ti++) // check all tables (sorted tables should be ok except for root table is sorted on key
{
int voTablePos = std[ti].FirstColumn; // +std[ti].KeyColPos + keyOffset; // position of first element of vo for table
int voTableLen = std[ti].SelectCount;
if (NullValue.IsNull(vo[voTablePos]))
{
continue; // skip if null data for this table (i.e. key is null)
}
for (i2 = firstRowForKey; i2 < ri; i2++) // look for empty slot
{
vo2 = (object [])newResults[i2];
object o = vo2[voTablePos];
if (!NullValue.IsNull(o))
{
continue; // already full
}
Array.Copy(vo, voTablePos, vo2, voTablePos, voTableLen);
Array.Clear(vo, voTablePos, voTableLen);
break;
}
}
}
}
return(newResults); // substitute new ordered set of results
}
/// <summary>
/// Serialize a DataRow to a StringBuilder object
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public static StringBuilder SerializeToText(
object[] oa,
int startPos,
int length)
{
StringBuilder sb = new StringBuilder();
ConvertMoleculesToChimeStrings(oa);
for (int vi = startPos; vi < startPos + length; vi++)
{
object o = oa[vi];
if (NullValue.IsNull(o))
{
sb.Append("<>");
}
else if (o is MobiusDataType)
{
MobiusDataType mdt = o as MobiusDataType;
sb.Append(mdt.Serialize());
}
else
{
sb.Append("<");
if (o is string)
{
sb.Append('s');
o = MobiusDataType.NormalizeForSerialize(o.ToString());
}
else if (o is int)
{
sb.Append('i');
}
else if (o is float)
{
sb.Append('f');
}
else if (o is double)
{
sb.Append('f');
}
else if (o is decimal)
{
sb.Append('f');
}
else if (o is DateTime)
{ // write datetime as number of ticks
sb.Append('d');
o = ((DateTime)o).Ticks;
}
else
{
throw new Exception("Invalid type: " + o.GetType());
}
sb.Append(',');
sb.Append(o);
sb.Append(">");
}
}
return(sb);
}