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; }