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
}
