private MetaTable GetMetaTableLevel2( string name) { UserObject uo; Query incQry = null; // included query QueryTable qt, qt1, qt2; QueryColumn qc, qc1, qc2; CalcFieldMetaTable mt; MetaTable mt1, mt2; MetaColumn mc, mc1, mc2, cfMc, cfMcModel; ExecuteQueryParms eqp; string cfLabel = "", mcName, mcLabel; string sql = null, tables = null, exprs = null, criteria = null; int objectId, startingSuffix, ci, i1; string prefix = "calcfield_"; // calculated field metatable names begin with "calcfield_" if (name.ToLower().IndexOf(prefix) != 0) { return(null); } if (Lex.Eq(name, "CALCFIELD_826476")) { name = name; // debug } string tok = name.Substring(prefix.Length); // get the object id try { objectId = Int32.Parse(tok); } catch (Exception ex) { return(null); } uo = UserObjectDao.Read(objectId); if (uo == null) { return(null); } if (!Permissions.UserHasReadAccess(Security.UserName, uo)) { return(null); } CalcField cf = CalcField.Deserialize(uo.Content); if (cf == null) { return(null); } if (cf.FinalResultType == MetaColumnType.Unknown) { return(null); // need a final result type } mt = new CalcFieldMetaTable(); mt.Name = name; mt.CalcField = cf; // include associated CalcField object mt.Label = uo.Name; if (MetaTableFactory.ShowDataSource) // add source to label if requested { mt.Label = MetaTableFactory.AddSourceToLabel(mt.Name, mt.Label); } mt.Description = cf.Description; mt.MetaBrokerType = MetaBrokerType.CalcField; bool keyAdded = false; cfLabel = cf.ColumnLabel; // get any user defined label for result // Add metacolumns for basic calculated field if (cf.CalcType == CalcTypeEnum.Basic && cf.Column1.MetaColumn != null) { cfMcModel = cf.Column1.MetaColumn; tok = cf.Operation; if (Lex.IsNullOrEmpty(cfLabel)) { if (tok.IndexOf("/") == 0) { cfLabel = "Ratio"; } else if (tok.IndexOf("*") == 0) { cfLabel = "Product"; } else if (tok.IndexOf("+") == 0) { cfLabel = "Sum"; } else if (tok.IndexOf("-") == 0) { cfLabel = "Difference"; } else { if (cf.Column1.FunctionEnum == CalcFuncEnum.None) // just use existing col name { cfLabel = cf.Column1.MetaColumn.Label; } else // use function & col name { tok = cf.Column1.Function; i1 = tok.IndexOf("("); if (i1 >= 0) { tok = tok.Substring(0, i1); } if (!tok.EndsWith(" ")) { tok += " "; } tok += cf.MetaColumn1.Label; cfLabel = tok; } } if (cf.IsClassificationDefined) { cfLabel += " Class"; // add class suffix if classifier } } } else if (cf.CalcType == CalcTypeEnum.Advanced && cf.AdvancedExpr != "") { List <MetaColumn> mcList = cf.GetInputMetaColumnList(); if (mcList.Count == 0) { return(null); } cfMcModel = mcList[0]; if (cfLabel == "") { cfLabel = "Value"; } } else { return(null); } mt.Parent = cfMcModel.MetaTable.Parent; // set parent if (mt.Root == mt) { mt.Parent = cfMcModel.MetaTable; // if F1 table is a root set it as parent } // Add key value mc = cfMcModel.MetaTable.KeyMetaColumn; // make copy of first table key field for our key if (mc == null) { return(null); } mc = mc.Clone(); mc.ColumnMap = mc.Name; // map to self mc.MetaTable = mt; mt.AddMetaColumn(mc); keyAdded = true; mc.IsSearchable = cf.IsSearchable; // Add calculated value column mc = new MetaColumn(); mt.AddMetaColumn(mc); mc.MetaTable = mt; mc.Name = "CALC_FIELD"; mc.Label = cfLabel; mc.DataType = cf.FinalResultType; // final result type mc.InitialSelection = ColumnSelectionEnum.Selected; mc.Format = cfMcModel.Format; mc.Width = cfMcModel.Width; mc.Decimals = cfMcModel.Decimals; if (cf.FinalResultType == MetaColumnType.Integer) { mc.Format = ColumnFormatEnum.Decimal; mc.Decimals = 0; } if (cf.FinalResultType == MetaColumnType.Image) { mc.Width = (int)(mc.Width * 2.0); // make CF images wider } mc.IsSearchable = cfMcModel.IsSearchable; if (mc.IsSearchable) // refine searchability { mc.IsSearchable = cf.IsSearchable; } cfMc = mc; // Add metacolumns for the underlying columns that go into the calculation foreach (CalcFieldColumn cfc in cf.CfCols) { if (cfc.MetaColumn == null) { continue; } if (cfc.MetaColumn.IsKey) { continue; // don't add additional key } mc = cfc.MetaColumn.Clone(); mcName = mc.MetaTable.Name + "_" + mc.Name; mc.Name = mt.GetValidMetaColumnName(mcName); mc.DetailsAvailable = false; // no drill-down for now (can cause issues) mc.IsSearchable = false; // no searching for now (can cause issues) mc.ColumnMap = mc.Name; // map to self if (Lex.Eq(mc.Label, cfLabel)) // if same as result start suffix checking at 2 { startingSuffix = 1; } else { startingSuffix = 2; } mc.Label = mt.GetUniqueMetaColumnLabel(mc.Label, startingSuffix); mc.InitialSelection = ColumnSelectionEnum.Hidden; // hide for now mc.TableMap = cfc.MetaColumn.MetaTable.Name + "." + cfc.MetaColumn.Name; // save source table and column //mc.TableMap = cfc.MetaColumn.MetaTable.Name; // save source table and column //mc.ColumnMap = cfc.MetaColumn.Name; mt.AddMetaColumn(mc); } //mt.MetaColumns.Remove(cfMc); // move cf result column to end after the inputs //mt.MetaColumns.Add(cfMc); return(mt); }
/// <summary> /// Build a set of rows from data in the buffer /// </summary> public override void BuildDataFromOtherRetrievedData( QueryEngine qe, int ti, List <object[]> results, int firstResult, int resultCount) { // This routine takes a set of results in the buffer, computes the calculated field value // and stores the results back in the buffer. For a calc field derived from a single column // the process is simple; however, for two column calculations there are two cases to consider. // 1. If no values then nothing to compute. // 2. If both values are present in the buffer row then just use them. // 3. If a single value is present then pick up the most recent other value that // exists for the same key. // Note that if the query only retrieves one of the two fields the the other field // will be retrieved into the calc field buffer. QueryColumn qc; MetaColumn mc; CalcFieldColumn cfc, cfc2; int voiCfKey, voiCf, voi; object v, v1, v2, vn; object[] vo, mergedVo; const int keyOffset = 1; throw new NotImplementedException(); #if false Query q = qe.Query; CalcFieldMetaTable cfMt = GetCalcFieldMetaTable(q.Tables[ti].MetaTable); CalcField cf = cfMt.CalcField; // Setup if (resultCount <= 0) { return; } if (SelectList.Count <= 1) { return; // just return if only key selected } voiCfKey = Qt.KeyQueryColumn.VoPosition; // calc field key value buffer index voiCf = Qt.GetQueryColumnByNameWithException("CALC_FIELD").VoPosition; // calculated value buffer index mergedVo = new object[results[0].Length]; // merged vo containing latest values for each column // Process each result row for (int ri = firstResult; ri < firstResult + resultCount; ri++) // result loop { vo = results[ri]; if (vo[0] == null) { continue; } if (mergedVo[0] == null || Lex.Ne(vo[0].ToString(), mergedVo[0].ToString())) { // if new key init voVals Array.Clear(mergedVo, 0, mergedVo.Length); mergedVo[0] = vo[0]; } int nonNullCount = 0; int nullCount = 0; QueryColumn[] colMap = _dataMap.InputColMap; for (int mi = 0; mi < colMap.Length; mi++) { if (colMap[mi] == null) { continue; // } voi = colMap[mi].VoPosition; v = vo[voi]; if (!NullValue.IsNull(v)) { mergedVo[voi] = v; nonNullCount++; } else { nullCount++; } } if (nonNullCount == 0) { continue; // if all inputs are null then all done (assume all outputs are null also) } vo[voiCfKey] = vo[0]; // copy key value QueryTableData qtd = qe.Qtd[ti]; // Pick up each retrieved value in the from other data fields for (int cfci = 0; cfci < qtd.SelectedColumns.Count; cfci++) // columns to retrieve/calculate loop { qc = qtd.SelectedColumns[cfci]; mc = qc.MetaColumn; voi = qc.VoPosition; bool calcFieldCol = Lex.Eq(mc.Name, "CALC_FIELD"); bool selectedCol = !calcFieldCol; // Input value column if (selectedCol) // retrieved input value { QueryColumn cmi = _dataMap.SelectedColMap[cfci]; if (cmi != null && cmi.VoPosition > 0) { v = mergedVo[cmi.VoPosition]; if (mc.DataType == MetaColumnType.Image) // convert to proper image reference { MetaTable sourceMt = cmi.QueryTable.MetaTable; GenericMetaBroker gmb = QueryEngine.GetGlobalBroker(cmi.QueryTable.MetaTable); v = gmb.ConvertValueToImageReference(v); } vo[voi] = v; } } // Calculate the CF value else { cfc = cf.CfCols[0]; vn = vo[voiCf]; // get possible single retrieved value vo[voiCf] = null; // clear calc value (may hold one of two values) v = mergedVo[colMap[0].VoPosition]; // get first value if (NullValue.IsNull(v)) { continue; // if null then result must be null } v = ApplyFunction(cfc, v); for (int mi = 1; mi < colMap.Length; mi++) // combine with { v1 = v; // reference previous value if (colMap[mi] == null) { continue; } cfc2 = cf.CfCols[mi]; voi = colMap[mi].VoPosition; v2 = mergedVo[voi]; v2 = ApplyFunction(cfc2, v2); v = CalculateValue(cf, v1, cfc2, v2); if (v == null) { break; } } v = ApplyClassification(cf, v); vo[voiCf] = v; // store calculated value } } // column loop vo = vo; // debug to check row loop at end } // row loop return; #endif }