public static void Run()
{
// ExStart:ReadingAndWritingQueryTable
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook from source excel file
Workbook workbook = new Workbook(dataDir + "Sample.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access first Query Table
QueryTable qt = worksheet.QueryTables[0];
// Print Query Table Data
Console.WriteLine("Adjust Column Width: " + qt.AdjustColumnWidth);
Console.WriteLine("Preserve Formatting: " + qt.PreserveFormatting);
// Now set Preserve Formatting to true
qt.PreserveFormatting = true;
// Save the workbook
workbook.Save(dataDir + "Output_out_.xlsx");
// ExEnd:ReadingAndWritingQueryTable
}
/// <summary>
/// Get the ResultsField associated with a PivotGridField
/// </summary>
/// <param name="Rf"></param>
/// <param name="f0"></param>
/// <returns></returns>
internal ResultsField GetPivotGridFieldResultsField(
PivotGridField pgf)
{
Query q = Rf.Query;
string[] sa = pgf.UnboundFieldName.Split('.');
if (sa.Length < 2)
{
return(null);
}
QueryTable qt = q.GetTableByName(sa[0]);
if (qt == null)
{
return(null);
}
QueryColumn qc = qt.GetQueryColumnByName(sa[1]);
if (qc == null)
{
return(null);
}
ResultsField rfld = Rf.GetResultsField(qc);
return(rfld);
}
/// <summary>
/// Get map of query table alias changes, return null if no alias changes
/// </summary>
/// <param name="q"></param>
/// <returns></returns>
public static Dictionary <string, string> GetAliasMap(
Query q)
{
q.AssignUndefinedAliases(); // be sure everything has an alias
bool aliasChanged = false;
Dictionary <string, string> tAliasMap = new Dictionary <string, string>();
for (int qti = 0; qti < q.Tables.Count; qti++)
{ // build map from old table aliases to new
QueryTable qt = q.Tables[qti];
string alias = "T" + (qti + 1).ToString();
if (qt.Alias != "")
{
tAliasMap[qt.Alias.ToUpper()] = alias;
}
if (!Lex.Eq(qt.Alias, alias))
{
aliasChanged = true;
}
}
if (aliasChanged)
{
return(tAliasMap);
}
else
{
return(null); // no map if no change
}
}
/// <summary>
/// Construct with intitial query table
/// </summary>
/// <param name="qt"></param>
public ExecuteQueryParms(
QueryEngine qe,
QueryTable qt)
{
Qe = qe;
QueryTable = qt;
}
/// <summary>
/// SetFieldGridDataRow
/// </summary>
/// <param name="dr"></param>
/// <param name="f"></param>
void SetFieldGridDataRow(DataRow dr, PivotGridFieldMx f)
{
string label, txt;
ResultsField rfld = f.ResultsField as ResultsField;
ResultsTable rt = rfld.ResultsTable;
ResultsFormat rf = rt.ResultsFormat;
MetaColumn mc = rfld.MetaColumn;
QueryColumn qc = rfld.QueryColumn;
QueryTable qt = qc.QueryTable;
Query q = qt.Query;
dr["ColTypeImageCol"] = QueryTableControl.GetMetaColumnDataTypeImage(mc);
dr["PivotFieldCol"] = f; // store reference to pivot field
dr["FieldNameCol"] = f.Caption;
dr["AggRoleCol"] = f.Aggregation.RoleLabel;
dr["AggTypeCol"] = f.Aggregation.TypeLabel;
// txt = GroupingTypeLabel(f);
// dr["HeaderBinningCol"] = txt;
dr["SourceColumnCol"] = PivotGridControlMx.BuildFieldCaption(f);
label = qt.ActiveLabel;
if (rf.Tables.Count > 1)
{
label = "T" + (rt.Position + 1) + " - " + label;
}
dr["SourceTableCol"] = label;
return;
}
private void SetDelimiter(QueryTable queryTable, string delimiter)
{
switch (delimiter.ToLower())
{
case "comma":
case ",":
queryTable.TextFileCommaDelimiter = true;
break;
case "semicolon":
case ";":
queryTable.TextFileSemicolonDelimiter = true;
break;
case "space":
case " ":
queryTable.TextFileSpaceDelimiter = true;
break;
case "tab":
case "\t":
queryTable.TextFileTabDelimiter = true;
break;
default:
queryTable.TextFileOtherDelimiter = delimiter.ToLower();
break;
}
}
/// <summary>
/// InitializeMapForDev
/// </summary>
/// <param name="dm"></param>
void InitializeMapForDev(DataTableMapMsx dm)
{
QueryTable qt = null;
QueryColumn qc = null;
string alias = "";
string colName = "";
if (dm.ColumnMapCollection != null && dm.ColumnMapCollection.Count > 0)
{
return;
}
dm.ColumnMapCollection = new ColumnMapCollection();
DataTableMsx dt = SVP.Doc.DataManager.TableCollection[0];
dm.SpotfireDataTable = dt;
foreach (DataColumnMsx dc in dt.Columns)
{
colName = dc.Name;
int i1 = colName.IndexOf(".");
if (i1 <= 0 || i1 + 1 >= colName.Length) // name without alias
{
alias = "T1";
}
else
{
alias = colName.Substring(0, i1);
colName = colName.Substring(i1 + 1);
}
int qti = Query.GetQueryTableIndexByAlias(alias);
if (qti < 0)
{
continue;
}
qt = Query.Tables[qti];
qc = qt.GetQueryColumnByLabel(colName);
if (qc == null)
{
continue;
}
ColumnMapMsx cm = new ColumnMapMsx();
cm.QueryColumn = qc;
cm.SpotfireColumn = dc;
cm.SpotfireColumnName = dc.Name;
cm.Role = dc.Name;
dm.ColumnMapCollection.Add(cm);
}
return;
}
/// <summary>
/// Setup the position maps between the columns and the value object array
/// </summary>
/// <param name="qt"></param>
public void SetupPositionMaps(QueryTable qt) // (obsolete)
{
int maxVoi = 0;
foreach (string colName in ColNameToFieldPosition.Keys)
{
QueryColumn qc = qt.GetQueryColumnByName(colName);
if (qc == null || !ColNameToFieldPosition.ContainsKey(colName))
{
throw new Exception("Column name not found in QueryTable: " + colName);
}
AssayAttributePosition tafp = ColNameToFieldPosition[colName];
tafp.Voi = qc.VoPosition;
if (qc.VoPosition > maxVoi)
{
maxVoi = qc.VoPosition;
}
}
VoiToTarColEnum = new TarColEnum[maxVoi + 1]; // setup voi to TarColEnum map
foreach (AssayAttributePosition tafp in ColNameToFieldPosition.Values)
{
if (tafp == null || tafp.Voi < 0)
{
continue;
}
VoiToTarColEnum[tafp.Voi] = tafp.TarColEnum;
}
}
public static INullableQueryTable<float> Lueftungswerte(this IDb db, string time, string interval, int windowFactor)
{
var table = new QueryTable<float>();
table.AddSerie(db.GetSerie<float>("Lueftung_Zulufttemperatur", time).Alias("Zuluft"));
table.AddSerie(db.GetSerie<float>("Lueftung_Ablufttemperatur", time).Alias("Abluft"));
table.AddSerie(db.GetSerie<float>("Lueftung_Aussenlufttemperatur", time).Alias("Aussenluft"));
table.AddSerie(db.GetSerie<float>("Lueftung_Fortlufttemperatur", time).Alias("Fortluft"));
table.AddSerie(db.GetSerie<float>("Lueftung_Fan_Level", time).Alias("FanLevel"));
table.AddSerie(db.GetSerie<float>("HmWetterstationTemperature", time).Alias("AussenTemp"));
table.AddSerie(db.GetSerie<float>("heizenAvgTemperatureOhneHobbyraum", time).Alias("InnenTemp"));
return table.Group(
g =>
g.ByTime.Expression(interval, "5m", windowFactor)
.ExpandTimeRange(TimeSpan.FromMinutes(windowFactor)).TimeStampIsMiddle()
.Aggregate(a => a.MeanExpWeighted()))
.Calc(t =>
{
t.SoleEntzugsleistung = AirPower(t.FanLevel, t.Aussenluft, t.AussenTemp);
t.WirkungsgradKwlAbluft = (float) 100.0/(t.Abluft - t.Aussenluft)*(t.Abluft - t.Fortluft);
t.WirkungsgradGesamt = (float)100.0 / (t.InnenTemp - t.AussenTemp) * (t.Zuluft - t.AussenTemp);
t.WirkungsgradOhneLeitungsverlust = (float)100.0 / (t.Abluft - t.AussenTemp) * (t.Zuluft - t.AussenTemp);
t.WirkungsgradKwlZuluft = (float)100.0 / (t.Abluft - t.Aussenluft) * (t.Zuluft - t.Aussenluft);
t.GewinnDurchSole = (float)t.SoleEntzugsleistung * ((100.0f - t.WirkungsgradKwlAbluft) / 100.0);
var arbeitsZahl = 4.5f;
t.GewinnDurchSoleElektrisch = (float)t.GewinnDurchSole / arbeitsZahl;
t.GewinnNettoDurchSoleElektrisch = (float)(t.GewinnDurchSole / arbeitsZahl) - 2f;
t.VerlustLeitungen = (float)AirPower(t.FanLevel, t.InnenTemp, t.Abluft);
t.VerlustHaus = AirPower(t.FanLevel, t.InnenTemp, t.Zuluft);
t.GewinnKwl = AirPower(t.FanLevel, t.Zuluft, t.Aussenluft);
t.VerlustKwlElektrisch = KwlElectricalPower(t.FanLevel);
t.GewinnNettoElektrisch = (t.GewinnKwl + t.GewinnDurchSole) / arbeitsZahl - KwlElectricalPower(t.FanLevel) - 2;
}).RemoveDbSeries();
}
/// <summary>
/// Edit the Cids criteris
/// </summary>
/// <returns></returns>
bool EditCids()
{
Query q;
QueryTable qt;
MetaTable mt;
QueryColumn qc;
mt = MetaTableCollection.GetWithException(Tso.GetSummarizedMetaTableName());
q = new Query();
qt = new QueryTable(q, mt);
qc = qt.KeyQueryColumn;
qc.Criteria = Cids.Text;
qc.CriteriaDisplay = Cids.Text;
if (!CriteriaEditor.GetCompoundIdCriteria(qc))
{
return(false);
}
if (qc.Criteria.StartsWith(" = ")) // make equality look nice
{
Cids.Text = qc.CriteriaDisplay;
}
else
{
Cids.Text = qc.Criteria;
}
return(true);
}
/// <summary>
/// InitializeMultiDbUnpivotedAssayResultFieldPositionMap
/// </summary>
/// <param name="qt"></param>
public void InitializeMultiDbUnpivotedAssayResultFieldPositionMap(QueryTable qt)
{
ColNameToFieldPosition[qt.MetaTable.KeyMetaColumn.Name] = CompoundId;
ColNameToFieldPosition[MultiDbAssayDataNames.Structure] = Structure;
ColNameToFieldPosition[MultiDbAssayDataNames.GeneFamily] = GeneFamily;
ColNameToFieldPosition[MultiDbAssayDataNames.GeneSymbol] = TargetSymbol;
ColNameToFieldPosition[MultiDbAssayDataNames.ResultType] = ResultTypeConcType;
ColNameToFieldPosition[MultiDbAssayDataNames.AssayType] = AssayType;
ColNameToFieldPosition[MultiDbAssayDataNames.AssayMode] = AssayMode;
ColNameToFieldPosition[MultiDbAssayDataNames.ActivityBin] = ActivityBin;
ColNameToFieldPosition[MultiDbAssayDataNames.AverageValueQn] = ResultValueQn;
ColNameToFieldPosition[MultiDbAssayDataNames.ResultQualifier] = ResultQualifier;
ColNameToFieldPosition[MultiDbAssayDataNames.AverageValue] = ResultNumericValue;
ColNameToFieldPosition[MultiDbAssayDataNames.NValue] = NValue;
ColNameToFieldPosition[MultiDbAssayDataNames.StdDev] = StdDev;
ColNameToFieldPosition[MultiDbAssayDataNames.Units] = Units;
ColNameToFieldPosition[MultiDbAssayDataNames.Conc] = Conc;
ColNameToFieldPosition[MultiDbAssayDataNames.MostPotentValueQn] = MostPotentValueQn;
ColNameToFieldPosition[MultiDbAssayDataNames.ActivityBinMostPotent] = ActivityBinMostPotent;
ColNameToFieldPosition[MultiDbAssayDataNames.ResultDetailId] = ResultDetailId;
ColNameToFieldPosition[MultiDbAssayDataNames.TargetMapX] = TargetMapX;
ColNameToFieldPosition[MultiDbAssayDataNames.TargetMapY] = TargetMapY;
ColNameToFieldPosition[MultiDbAssayDataNames.Sources] = Sources;
}
public static UnpivotedAssayResultFieldPositionMap NewMdbAssayMap(QueryTable qt)
{
UnpivotedAssayResultFieldPositionMap m = new UnpivotedAssayResultFieldPositionMap();
m.InitializeMultiDbUnpivotedAssayResultFieldPositionMap(qt);
return(m);
}
/// <summary>
/// Select an item in the querybuilder table list
/// </summary>
/// <param name="qt"></param>
public int SelectQueryTableTab(
QueryTable qt)
{
QueryTable qt2;
int qti = Query.GetQueryTableIndex(qt);
if (qti < 0)
{
qti = Query.GetQueryTableIndexByName(qt.MetaTable.Name);
if (qti < 0)
{
return(-1);
}
}
if (ShowCriteriaTab)
{
qti++;
}
if (Tabs.SelectedTabPageIndex != qti)
{
Tabs.SelectedTabPageIndex = qti;
}
return(qti);
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create workbook from source excel file
Workbook workbook = new Workbook(sourceDir + "sampleReadingAndWritingQueryTable.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access first Query Table
QueryTable qt = worksheet.QueryTables[0];
// Print Query Table Data
Console.WriteLine("Adjust Column Width: " + qt.AdjustColumnWidth);
Console.WriteLine("Preserve Formatting: " + qt.PreserveFormatting);
// Now set Preserve Formatting to true
qt.PreserveFormatting = true;
// Save the workbook
workbook.Save(outputDir + "outputReadingAndWritingQueryTable.xlsx");
Console.WriteLine("ReadingAndWritingQueryTable executed successfully.");
}
/// <summary>
/// Get any existing target summary options from option column
/// </summary>
/// <param name="qt"></param>
/// <returns></returns>
public static TargetSummaryOptions GetFromMdbAssayOptionsColumn(
QueryTable qt)
{
TargetSummaryOptions tso = null;
if (qt == null)
{
return(null);
}
QueryColumn qc = qt.GetQueryColumnByName(MultiDbAssayDataNames.MultiDbViewOptions);
if (qc == null)
{
return(null);
}
ParsedSingleCriteria psc = MqlUtil.ParseSingleCriteria(qc.Criteria);
if (psc == null)
{
return(null);
}
string tsoString = Lex.RemoveSingleQuotes(psc.Value);
if (Lex.IsNullOrEmpty(tsoString))
{
return(null);
}
tso = TargetSummaryOptions.Deserialize(tsoString);
return(tso);
}
/// <summary>
/// Build the query to display results
/// </summary>
Query BuildQuery()
{
Query q = RootQm.Query;
Query q2 = q.Clone();
int ti = 0;
while (ti < q2.Tables.Count)
{
QueryTable qt = q2.Tables[ti];
foreach (QueryColumn qc0 in qt.QueryColumns)
{
if (!qc0.Selected)
{
continue;
}
if (!qc0.IsKey &&
(qc0.CondFormat == null || qc0.CondFormat.Rules.Count == 0))
{
qc0.Selected = false;
}
}
if (qt.SelectedCount <= 1 && ti > 0) // remove table if no cf & not 1st table
{
q2.RemoveQueryTable(qt);
}
else
{
ti++;
}
}
return(q2);
}
public void CanConvert_AListOfObjects_ToSlimQueryTableFormat()
{
var objects = AListOfAnyClass();
var actual = new QueryTable<AnyClass>().For(objects);
var expected = new List<object>
{
new List<object>
{
new List<object> {"Any Property", "A1"},
new List<object> {"Any Other Property", "B1"},
new List<object> {"Yet Another Property", "C1"},
new List<object> {"A Date Time Property", "01-Jan-2012 13:30:55"},
},
new List<object>
{
new List<object> {"Any Property", "A2"},
new List<object> {"Any Other Property", "B2"},
new List<object> {"Yet Another Property", "C2"},
new List<object> {"A Date Time Property", "02-Feb-2013 13:30:55"},
},
new List<object>
{
new List<object> {"Any Property", "A3"},
new List<object> {"Any Other Property", "B3"},
new List<object> {"Yet Another Property", null},
new List<object> {"A Date Time Property", "03-Mar-2014 13:30:55"},
}
};
Assert.AreEqual(expected, actual);
}
public void DynamicSerieTests()
{
DummyLambda(a => a);
Dummy(3);
const int max = 100000;
var start = new DateTime(1000, 1, 1, 0, 0, 0, DateTimeKind.Utc);
var rowsA = new List<ISingleDataRow<int>>();
var rowsB = new List<ISingleDataRow<int>>();
for (var i = 0; i < max; i++)
{
rowsA.Add(new SingleDataRow<int>(start + TimeSpan.FromMinutes(i), i));
rowsB.Add(new SingleDataRow<int>(start + TimeSpan.FromMinutes(i), i*2));
}
var serieA = new QuerySerie<int>(rowsA, start, start + TimeSpan.FromMinutes(max)) {Name = "A", Key = "A"};
var serieB = new QuerySerie<int>(rowsB, start, start + TimeSpan.FromMinutes(max)) {Name = "B", Key = "B"};
var table = new QueryTable<int>();
table.AddSerie(serieA);
table.AddSerie(serieB);
//var sw = Stopwatch.StartNew();
//var result = table.Do(i => i.GroupByHours(1, t => t.Mean())).ZipToNew<int>("SumTable", t => t.A + t.B);
//sw.Stop();
var sw2 = Stopwatch.StartNew();
var result2 = table.Transform(i => i.GroupByMinutes(1, t => t.First()))
.ZipToNew("DiffTable", t => t.A - t.B);
sw2.Stop();
}
public IQueryTable <T> GetTable <T>(string measurementRegex, string timeExpression) where T : struct
{
var result = new QueryTable <T>();
foreach (var meas in MetadataInternal.MeasurementsWithAliases)
{
var match = Regex.Match(meas.Key, measurementRegex);
if (match.Success)
{
var serie = meas.Value.GetDataPoints <T>(timeExpression).Alias(meas.Key);
Group g = match.Groups["g"];
if (g.Success)
{
serie.GroupName = g.Value;
}
Group k = match.Groups["k"];
if (k.Success)
{
serie.Key = k.Value;
}
result.AddSerie(serie);
}
}
return(result);
}
/// <summary>
/// Get a simple comma separated list of allowed values for the column
/// </summary>
/// <param name="colname"></param>
/// <returns></returns>
static string GetListFromColCriteria(
QueryTable qt,
string colName)
{
string list = "";
QueryColumn qc = qt.GetQueryColumnByName(colName);
if (qc == null || Lex.IsNullOrEmpty(qc.Criteria))
{
return("");
}
ParsedSingleCriteria psc = MqlUtil.ParseSingleCriteria(qc.Criteria);
if (psc.ValueList == null)
{
return("");
}
foreach (string s in psc.ValueList)
{
if (list != "")
{
list += ", ";
}
list += s;
}
return(list);
}
/// <summary>
/// Get TargetSummaryOptions from QueryTable criteria values
/// </summary>
/// <param name="targetSumTable"></param>
/// <returns></returns>
public static TargetSummaryOptions GetFromQueryTable(
QueryTable targetSumTable)
{
QueryTable qt = targetSumTable;
TargetSummaryOptions tso = GetFromMdbAssayOptionsColumn(qt);
if (tso == null)
{
tso = new TargetSummaryOptions(); // use default values
}
if (qt == null)
{
return(tso);
}
if (qt.Query != null)
{
tso.CidCriteria = qt.Query.KeyCriteria;
}
else
{
tso.CidCriteria = qt.KeyQueryColumn.Criteria; // keep in variable criteria form
}
tso.TargetList = GetListFromColCriteria(qt, MultiDbAssayDataNames.GeneSymbol);
tso.GeneFamilies = GetListFromColCriteria(qt, MultiDbAssayDataNames.GeneFamily);
tso.AssayTypesToInclude = GetListFromColCriteria(qt, MultiDbAssayDataNames.AssayType);
tso.AssayModesToInclude = GetListFromColCriteria(qt, MultiDbAssayDataNames.AssayMode);
tso.ResultTypesToInclude = GetListFromColCriteria(qt, MultiDbAssayDataNames.ResultType);
//string criteria = qt.GetQueryColumnByName("top_lvl_rslt").Criteria;
//tso.UseTopLevelResultTypeOnly = Lex.IsNullOrEmpty(criteria) || Lex.Contains(criteria, "'Y'");
return(tso);
}
/// <summary>
/// Build unpivoted subquery QueryManager including the DataTableManger,
/// ResultsFormat and ResultsFormatter.
/// Build empty DataTable
/// </summary>
/// <returns></returns>
QueryManager InitializeUnpivotedSubqueryQm()
{
Query q2 = null;
DataTableManager dtm2;
DataTableMx dt2;
ResultsFormat rf2;
ResultsFormatFactory rff;
ResultsFormatter rfmtr;
QueryTable qt;
MetaTable mt;
QueryManager qm2 = new QueryManager(); // build output query here
// Setup unpivoted output query
q2 = new Query(qm2);
mt = MetaTableCollection.GetWithException(MultiDbAssayDataNames.CombinedNonSumTableName);
qt = new QueryTable(q2, mt); // be sure proper cols are selected
dtm2 = new DataTableManager(qm2);
dt2 = DataTableManager.BuildDataTable(qm2);
rff = new ResultsFormatFactory(qm2, OutputDest.WinForms);
rff.Build();
rfmtr = new ResultsFormatter(qm2);
return(qm2);
}
/// <summary>
/// Update display with latest stats
/// </summary>
/// <param name="qe"></param>
/// <param name="mbRowCounts"></param>
/// <param name="mbTimes"></param>
public void UpdateStatsDisplay2(
Query q,
QueryEngine qe,
List <MetaBrokerStats> mbStats)
{
DataRow dr;
//if (Query != q) // || Query.Tables.Count != DataTable.Rows.Count) // if query differs or doesn't match DataTable, show current query
// StartNewQueryExecution(q);
if (mbStats == null)
{
return;
}
Query = q;
Qe = qe;
Grid.BeginUpdate();
DataTable.Clear();
int sbc = 0; // search step brokers seen
int qtc = 0; // retrieval step brokers seen
MetatableTotalRows = OracleTotalRows = 0;
for (int si = 0; si < mbStats.Count; si++)
{
MetaBrokerStats mbs = mbStats[si];
if (Lex.IsDefined(mbs.Label)) // search step
{
dr = DataTable.NewRow();
SetDataRow(dr, null, mbs);
DataTable.Rows.Add(dr);
sbc++;
}
else // retrieval step
{
if (qtc >= Query.Tables.Count)
{
continue;
}
QueryTable qt = Query.Tables[qtc];
dr = DataTable.NewRow();
SetDataRow(dr, qt, mbs);
DataTable.Rows.Add(dr);
qtc++;
}
}
Grid.EndUpdate();
Grid.Refresh();
UpdateTotals();
return;
}
/// <summary>
/// Setup Parameters
/// </summary>
void GetParameters()
{
SpotfireViewProps sl = SpotfireViewProps;
sl.SpotfireLinkParameters = new Dictionary <string, SpotfireLinkParameter>();
QueryTable qt = CriteriaCols.QueryTable;
foreach (QueryColumn qc in qt.QueryColumns)
{
if (!qc.Selected)
{
continue;
}
MetaColumn mc = qc.MetaColumn;
SpotfireLinkParameter p = new SpotfireLinkParameter();
p.Name = mc.Name;
p.Value = "True";
SpotfireViewProps.SpotfireLinkParameters[p.Name] = p;
}
return;
}
public void SelectQueryRecord()
{
QueryTable table = QueryTable.Instance(new QueryDataAccess());
QueryResultModel expectedResult = new QueryResultModel()
{
QueryID = 2,
CustomerUUID = "CustUUID02",
Frequency = "Freq02",
PriceLimit = "PriceLimit02",
Category = "Cat02",
CategoryID = "CatID02"
};
QueryResultModel result = table.SelectRecord(new SelectQueryModel()
{
CustomerUUID = "CustUUID02"
});
Assert.AreEqual(expectedResult.QueryID, result.QueryID);
Assert.AreEqual(expectedResult.CustomerUUID, result.CustomerUUID);
Assert.AreEqual(expectedResult.Frequency, result.Frequency);
Assert.AreEqual(expectedResult.PriceLimit, result.PriceLimit);
Assert.AreEqual(expectedResult.Category, result.Category);
Assert.AreEqual(expectedResult.CategoryID, result.CategoryID);
}
private void RunDatabaseQuery_Click(object sender, EventArgs e)
{
DataRow dr;
UcdbDatabase ucdb;
UserObject uo;
int ri;
if (!GetFocusedUo(out ri, out dr, out ucdb, out uo))
{
return;
}
if (!UserData.BuildDatabaseQuery(uo))
{
return; // some error
}
QbUtil.RenderQuery();
QueryTable qt = QbUtil.Query.Tables[0]; // root table
QueryColumn qc = qt.GetQueryColumnByName("MolFormula");
if (qc == null)
{
throw new Exception("MolFormula not found in root table");
}
MetaColumn mc = qc.MetaColumn;
qc.CriteriaDisplay = "All Data Rows"; // do all data query on molformula
qc.Criteria = "(" + mc.Name + " IS NOT NULL OR " + mc.Name + " IS NULL)";
QbUtil.RenderQuery(0);
Response = "Command RunQuery";
DialogResult = DialogResult.OK;
}
/// <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);
}
public void CanChangeFormat_OfDateTimes_WhenConverting_ObjectsToTable()
{
var objects = AListOfAnyClass();
var table = new QueryTable<AnyClass>();
table.OnlyShowColumns("A Date Time Property");
table.UseDateTimeFormat("dd-MMM-yyyy");
var expected = new List<object>
{
new List<object>
{
new List<object> {"A Date Time Property", "01-Jan-2012"},
},
new List<object>
{
new List<object> {"A Date Time Property", "02-Feb-2013"},
},
new List<object>
{
new List<object> {"A Date Time Property", "03-Mar-2014"},
}
};
Assert.AreEqual(expected, table.For(objects));
}
/// <summary>
/// Setup the form values
/// </summary>
/// <param name="qc"></param>
public void Setup(
QueryColumn qc)
{
InSetup = true;
MetaTable mt = null;
if (qc != null)
{
qc.CopyCriteriaFromQueryKeyCriteria(); // sync the qc to query KeyCriteria
}
else // create a default qc
{
qc = QueryTable.GetDefaultRootQueryTable()?.KeyQueryColumn;
}
if (Lex.IsDefined(qc.CriteriaDisplay))
{
Cids.Text = qc.CriteriaDisplay;
}
else if (Lex.IsDefined(qc.Criteria))
{
Cids.Text = qc.Criteria;
}
else
{
Cids.Text = "";
}
QueryColumn = qc; // QueryColumn instance associated with this control
InSetup = false;
return;
}
/// <summary>
/// Set QueryTable criteria values from TargetSummaryOptions
/// </summary>
/// <param name="TargetSumTable"></param>
public bool SetInQueryTable(
QueryTable targetSumTable)
{
QueryTable qt = targetSumTable;
if (qt == null)
{
return(false);
}
Query q = qt.Query;
QueryColumn qc = qt.GetQueryColumnByNameWithException(MultiDbAssayDataNames.MultiDbViewOptions);
if (qc == null)
{
return(false);
}
string tsoString = Serialize();
qc.Criteria = qc.MetaColumn.Name + " = " + Lex.AddSingleQuotes(tsoString);
qc.CriteriaDisplay = "Edit...";
qt.KeyQueryColumn.Criteria = CidCriteria; // keep in variable criteria form
SetListCriteriaForCol(qt, MultiDbAssayDataNames.GeneSymbol, TargetList);
SetListCriteriaForCol(qt, MultiDbAssayDataNames.GeneFamily, GeneFamilies);
return(true);
}
/// <summary>
/// Set vo with specified QueryColumn name
/// </summary>
/// <param name="qt"></param>
/// <param name="colName"></param>
/// <param name="vo"></param>
/// <param name="voi"></param>
/// <param name="value"></param>
/// <param name="withException"></param>
public static void SetVo(
QueryTable qt,
string colName,
object[] vo,
object value)
{
bool withException = false; // always false for now
QueryColumn qc;
if (withException)
{
qc = qt.GetQueryColumnByNameWithException(colName);
}
else
{
qc = qt.GetQueryColumnByName(colName);
if (qc == null)
{
return;
}
}
int voPos = qc.VoPosition;
SetVo(vo, voPos, value);
return;
}
public void CanHide_Columns_WhenConverting_ObjectsToTable()
{
var objects = AListOfAnyClass();
var table = new QueryTable<AnyClass>();
table.HideColumns("Any Property,Yet Another Property,A Date Time Property");
var expected = new List<object>
{
new List<object>
{
new List<object> {"Any Other Property", "B1"},
},
new List<object>
{
new List<object> {"Any Other Property", "B2"},
},
new List<object>
{
new List<object> {"Any Other Property", "B3"},
}
};
Assert.AreEqual(expected, table.For(objects));
}
/// <summary>
/// Add cid criteria to the derived query to get set of compounds desired
/// </summary>
/// <param name="q"></param>
/// <param name="cidList"></param>
void ModifyQueryForCidSearch(
Query q,
List <string> cidList)
{
QueryTable qt = q.Tables[0];
QueryColumn keyQc = qt.KeyQueryColumn;
ParsedSingleCriteria psc = new ParsedSingleCriteria();
psc.QueryColumn = keyQc;
if (cidList.Count == 1)
{
psc.Op = "=";
psc.Value = cidList[0];
}
else
{
psc.Op = "in";
psc.ValueList = cidList;
}
MqlUtil.ConvertParsedSingleCriteriaToQueryColumnCriteria(psc, keyQc, false);
keyQc.CopyCriteriaToQueryKeyCritera(q);
return;
}
public static INullableQueryTable <float> Lueftungswerte(this IDb db, string time, string interval, int windowFactor)
{
var table = new QueryTable <float>();
table.AddSerie(db.GetSerie <float>("Lueftung_Zulufttemperatur", time).Alias("Zuluft"));
table.AddSerie(db.GetSerie <float>("Lueftung_Ablufttemperatur", time).Alias("Abluft"));
table.AddSerie(db.GetSerie <float>("Lueftung_Aussenlufttemperatur", time).Alias("Aussenluft"));
table.AddSerie(db.GetSerie <float>("Lueftung_Fortlufttemperatur", time).Alias("Fortluft"));
table.AddSerie(db.GetSerie <float>("Lueftung_Fan_Level", time).Alias("FanLevel"));
table.AddSerie(db.GetSerie <float>("HmWetterstationTemperature", time).Alias("AussenTemp"));
table.AddSerie(db.GetSerie <float>("heizenAvgTemperatureOhneHobbyraum", time).Alias("InnenTemp"));
return(table.Group(
g =>
g.ByTime.Expression(interval, "5m", windowFactor)
.ExpandTimeRange(TimeSpan.FromMinutes(windowFactor)).TimeStampIsMiddle()
.Aggregate(a => a.MeanExpWeighted()))
.Calc(t =>
{
t.SoleEntzugsleistung = AirPower(t.FanLevel, t.Aussenluft, t.AussenTemp);
t.WirkungsgradKwlAbluft = (float)100.0 / (t.Abluft - t.Aussenluft) * (t.Abluft - t.Fortluft);
t.WirkungsgradGesamt = (float)100.0 / (t.InnenTemp - t.AussenTemp) * (t.Zuluft - t.AussenTemp);
t.WirkungsgradOhneLeitungsverlust = (float)100.0 / (t.Abluft - t.AussenTemp) * (t.Zuluft - t.AussenTemp);
t.WirkungsgradKwlZuluft = (float)100.0 / (t.Abluft - t.Aussenluft) * (t.Zuluft - t.Aussenluft);
t.GewinnDurchSole = (float)t.SoleEntzugsleistung * ((100.0f - t.WirkungsgradKwlAbluft) / 100.0);
var arbeitsZahl = 4.5f;
t.GewinnDurchSoleElektrisch = (float)t.GewinnDurchSole / arbeitsZahl;
t.GewinnNettoDurchSoleElektrisch = (float)(t.GewinnDurchSole / arbeitsZahl) - 2f;
t.VerlustLeitungen = (float)AirPower(t.FanLevel, t.InnenTemp, t.Abluft);
t.VerlustHaus = AirPower(t.FanLevel, t.InnenTemp, t.Zuluft);
t.GewinnKwl = AirPower(t.FanLevel, t.Zuluft, t.Aussenluft);
t.VerlustKwlElektrisch = KwlElectricalPower(t.FanLevel);
t.GewinnNettoElektrisch = (t.GewinnKwl + t.GewinnDurchSole) / arbeitsZahl - KwlElectricalPower(t.FanLevel) - 2;
}).RemoveDbSeries());
}
public override void Convert(dynamic jsonElement, ref QueryTable query)
{
if (jsonElement.Value == null)
{
return;
}
var geometry = new CHXGeometry();
geometry.ReadGeoJson(jsonElement.Value.location.shape.ToString());
var distance = jsonElement.Value.location.distance == null ? 0 : double.Parse(jsonElement.Value.location.distance.ToString());
var relation = (CHXGeometryRelation)System.Enum.Parse(typeof(CHXGeometryRelation), jsonElement.Value.location.relation.ToString());
if (geometry == null)
{
return;
}
query.QueryGeometryList = new List <QueryGeometry>();
query.QueryGeometryList.Add(new QueryGeometry()
{
Geometry = geometry,
Relation = relation,
Distance = distance
});
}
public static INullableQueryTable<float> LueftungTemperaturen(this IDb db, string time, string interval,
int windowFactor)
{
var table = new QueryTable<float>();
table.AddSerie(db.GetSerie<float>("Lueftung_Zulufttemperatur", time).Alias("Zulufttemperatur"));
table.AddSerie(db.GetSerie<float>("Lueftung_Ablufttemperatur", time).Alias("Ablufttemperatur"));
table.AddSerie(db.GetSerie<float>("Lueftung_Aussenlufttemperatur", time).Alias("Aussenlufttemperatur"));
table.AddSerie(db.GetSerie<float>("Lueftung_Fortlufttemperatur", time).Alias("Fortlufttemperatur"));
table.AddSerie(db.GetSerie<float>("Lueftung_Fan_Level", time).Alias("Lüfterstufe"));
table.AddSerie(db.GetSerie<float>("HmWetterstationTemperature", time).Alias("Aussentemperatur Wetterstation"));
table.AddSerie(db.GetSerie<float>("heizenAvgTemperatureOhneHobbyraum", time).Alias("Innenraumtemperatur"));
return table.Group(
g =>
g.ByTime.Expression(interval, "5m", windowFactor)
.ExpandTimeRange(TimeSpan.FromMinutes(windowFactor)).TimeStampIsMiddle()
.Aggregate(a => a.MeanExpWeighted()));
}
public QueryTableReferenceInterceptor(QueryTable queryTable)
{
_queryTable = queryTable;
}
public void CanRename_MultipleColumns_AtTheSameTime_WhenConverting_ObjectsToTable()
{
var objects = AListOfAnyClass();
var table = new QueryTable<AnyClass>();
table.OverrideColumnNames("Any Property|New Name,Any Other Property|Another New Name");
var expected = new List<object>
{
new List<object>
{
new List<object> {"New Name", "A1"},
new List<object> {"Another New Name", "B1"},
new List<object> {"Yet Another Property", "C1"},
new List<object> {"A Date Time Property", "01-Jan-2012 13:30:55"},
},
new List<object>
{
new List<object> {"New Name", "A2"},
new List<object> {"Another New Name", "B2"},
new List<object> {"Yet Another Property", "C2"},
new List<object> {"A Date Time Property", "02-Feb-2013 13:30:55"},
},
new List<object>
{
new List<object> {"New Name", "A3"},
new List<object> {"Another New Name", "B3"},
new List<object> {"Yet Another Property", null},
new List<object> {"A Date Time Property", "03-Mar-2014 13:30:55"},
}
};
Assert.AreEqual(expected, table.For(objects));
}
public QueryField(string name, QueryTable table)
{
Name = name;
Table = table;
}
public void CanShow_SpecificColumns_WhenConverting_ObjectsToTable()
{
var objects = AListOfAnyClass();
var table = new QueryTable<AnyClass>();
table.OnlyShowColumns("Any Other Property,Yet Another Property");
var expected = new List<object>
{
new List<object>
{
new List<object> {"Any Other Property", "B1"},
new List<object> {"Yet Another Property", "C1"},
},
new List<object>
{
new List<object> {"Any Other Property", "B2"},
new List<object> {"Yet Another Property", "C2"},
},
new List<object>
{
new List<object> {"Any Other Property", "B3"},
new List<object> {"Yet Another Property", null},
}
};
Assert.AreEqual(expected, table.For(objects));
}
/// <summary>
/// 모든 QueryString 을 Table 로 반환한다.
/// </summary>
/// <returns>Query 정보</returns>
public virtual QueryTable GetQueries() {
var queryTable = new QueryTable();
if(IsDebugEnabled)
log.Debug("모든 쿼리 문장 정보를 로드합니다...");
foreach(IConfig config in QuerySource.Configs)
foreach(string queryName in config.GetKeys()) {
string queryKey = config.Name + SECTION_DELIMITER + queryName;
queryTable.Add(queryKey, config.GetString(queryName, string.Empty));
}
return queryTable;
}
public QueryJoin(Expression<Func<bool>> on, QueryTable queryTable)
{
QueryTable = queryTable;
On = @on;
}
protected QueryTable QueryCube(int layerid)
{
string database = (string)Session["DatabaseName"];
string siteDim = (string)Session["SiteDim"];
Dictionary<int, string> layerDictionary = (Dictionary<int, string>)Session["LayerDictionary"];
QueryTable qt = new QueryTable(database, siteDim, layerDictionary);
MemberState ms = (MemberState)Session["MemberState"];
bool regionIsInt = (bool)Session["RegionIsInt"];
qt.createTableFromQuery(layerid, ms, regionIsInt);
return qt;
}
private Classifier createClassifier(QueryTable queryTable, IServerContext mapContext, IFeatureLayer featureLayer)
{
IDisplayTable dt = (IDisplayTable)featureLayer;
ITable joinTable = dt.DisplayTable;
int breaks = Convert.ToInt32(this.DropDownList2.SelectedValue);
String classifierType = this.DropDownList3.SelectedValue;
queryTable.calcFrequencies();
//queryTable.calcFrequencies(joinTable);
double[] values = queryTable.getValues();
int[] counts = queryTable.getCounts();
Classifier classifier = new Classifier(mapContext, values, counts, classifierType, breaks);
return classifier;
}