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>
/// Set contents of calculated field form
/// </summary>
/// <param name="cf"></param>
void SetupForm()
{
InSetup = true;
CalcField cf = CalcField;
CalcFieldColumn cfc;
CalcFieldColumnControl cfcc;
CondFormatRules rules;
int ci, cci;
MetaColumnType mcType = MetaColumnType.Number; // be sure source column type is set
MetaColumn cfMc = null;
Tabs.SelectedTabPageIndex = 0; // be sure first tab is selected
SetupControlVisibility();
foreach (CalcFieldColumnControl cfc0 in CfColCtls) // clear field selector controls on form
{
cfc0.FieldSelectorControl.Query = QueriesControl.Instance.CurrentQuery; // cfc0.CfCol.Query = QueriesControl.Instance.CurrentQuery;
cfc0.FieldSelectorControl.MetaColumn = null;
}
AdvancedExpr.Text = "";
FormatAdvancedExpr(cf.AdvancedExpr, out AdvancedEditorPseudoQuery); // setup advanced field menu
if (cf.CalcType == CalcTypeEnum.Basic)
{
cfMc = cf.MetaColumn1;
BasicOptionButton.Checked = true;
if (cf.Operation != "")
{
Operation.Text = cf.Operation;
}
else
{
Operation.Text = "/ (Division)";
}
cf.SetDerivedValues();
}
else // advanced type
{
AdvancedOptionButton.Checked = true;
AdvancedExpr.Text = FormatAdvancedExpr(cf.AdvancedExpr, out AdvancedEditorPseudoQuery);
List <MetaColumn> mcList = cf.GetInputMetaColumnList();
if (mcList.Count > 0)
{
cfMc = mcList[0];
}
// Setup result type
if (cf.PreclassificationlResultType == MetaColumnType.Unknown)
{
cf.PreclassificationlResultType = MetaColumnType.Number;
}
string txt = cf.PreclassificationlResultType.ToString();
if (Lex.Eq(txt, "String"))
{
txt = "Text"; // fixup
}
ResultDataType.Text = txt;
// Setup outer join table
JoinAnchorComboBox.Text = "";
if (Lex.IsDefined(cf.OuterJoinRoot))
{
for (ci = 0; ci < cf.CfCols.Count; ci++)
{
cfc = cf.CfCols[ci];
if (cfc.MetaColumn == null)
{
continue;
}
MetaTable mt = cfc.MetaColumn.MetaTable;
if (Lex.Eq(cf.OuterJoinRoot, cfc.MetaColumn.MetaTable.Name))
{
JoinAnchorComboBox.Text = cfc.MetaColumn.MetaTable.Label;
break;
}
}
}
}
// Setup column controls for source column type
if (cfMc != null)
{
cf.SourceColumnType = cfMc.DataType;
}
else
{
cf.SourceColumnType = MetaColumnType.Number;
}
SetupFormForColumnType(cf.SourceColumnType);
// Add column controls MetaColumn references and function info
cci = -1; // col control index
for (ci = 0; ci < cf.CfCols.Count; ci++)
{
cfc = cf.CfCols[ci];
cci++;
if (cci >= CfColCtls.Count)
{
throw new Exception("Number of calculated field columns exceeds limit of: " + CfColCtls.Count);
}
cfcc = CfColCtls[cci];
cfcc.FieldSelectorControl.MetaColumn = cfc.MetaColumn;
if (Lex.IsDefined(cfc.Function))
{
cfcc.Function.Text = cfc.Function;
}
else
{
cfcc.Function.Text = "None";
}
cfcc.Constant.Text = cfc.Constant;
cfcc.Constant.Enabled = Lex.Contains(cfc.Function, "constant");
}
CalcField.ColumnLabel = FieldColumnName.Text;
Description.Text = cf.Description;
if (cf.Operation != "")
{
Operation.SelectedItem = cf.Operation;
}
if (cf.Classification != null)
{
rules = cf.Classification.Rules;
}
else
{
rules = new CondFormatRules();
}
if (rules.Count == 0)
{
rules.Add(new CondFormatRule()); // include initial rule
}
CondFormatRulesCtl.SetupControl(CalcField.PreclassificationlResultType, rules.ColoringStyle);
CondFormatRulesCtl.SetupControl(CalcField.PreclassificationlResultType, rules);
InSetup = false;
return;
}
