/// <summary>
/// Set derived values for a single calc function field
/// </summary>
/// <param name="sourceColumnType"></param>
/// <param name="cfc"></param>
void SetDerivedColumnValues(
MetaColumnType sourceColumnType,
CalcFieldColumn cfc)
{
cfc.FunctionEnum = CalcFuncEnum.Unknown;
cfc.ConstantDouble = 0;
cfc.ResultColumnType = MetaColumnType.Unknown;
if (sourceColumnType == MetaColumnType.Unknown)
{
return;
}
if (!String.IsNullOrEmpty(cfc.Function))
{
cfc.FunctionEnum = ConvertCalcFuncStringToEnum(cfc.Function);
}
else
{
cfc.FunctionEnum = CalcFuncEnum.None;
}
double.TryParse(cfc.Constant, out cfc.ConstantDouble);
// Get datatype after applying function
cfc.ResultColumnType = sourceColumnType;
if (sourceColumnType == MetaColumnType.Date)
{
if (cfc.FunctionEnum == CalcFuncEnum.DaysSince)
{
cfc.ResultColumnType = MetaColumnType.Integer;
}
else if (cfc.FunctionEnum == CalcFuncEnum.None)
{
}
else
{
throw new Exception("Invalid function for field");
}
}
else if (MetaColumn.IsNumericMetaColumnType(sourceColumnType))
{
if (cfc.FunctionEnum == CalcFuncEnum.DaysSince)
{
throw new Exception("Invalid function for field");
}
}
else
{
if (cfc.FunctionEnum != CalcFuncEnum.None)
{
throw new Exception("Invalid function for field");
}
}
}
/// <summary>
/// Convert a single basic column to advanced form
/// </summary>
/// <param name="mc"></param>
/// <param name="function"></param>
/// <param name="constant"></param>
/// <param name="tableAlias"></param>
/// <returns></returns>
public static string BuildColumnExpr(
CalcFieldColumn cfc,
string tableAlias)
{
MetaColumn mc = cfc.MetaColumn;
string function = cfc.Function;
string constant = cfc.Constant;
string exp;
if (mc == null)
{
return("");
}
CalcFuncEnum cfe = ConvertCalcFuncStringToEnum(function);
if (cfe == CalcFuncEnum.None)
{
if (mc.DataType == MetaColumnType.Date)
{
exp = "trunc(<v>)"; // truncate all dates
}
else
{
exp = "<v>";
}
}
else if (cfe == CalcFuncEnum.Abs)
{
exp = "abs(<v>)";
}
else if (cfe == CalcFuncEnum.Neg)
{
exp = "-(<v>)";
}
else if (cfe == CalcFuncEnum.Log)
{
exp = "log(10,<v>)";
}
else if (cfe == CalcFuncEnum.NegLog)
{
exp = "-log(10,<v>)";
}
else if (cfe == CalcFuncEnum.Ln)
{
exp = "ln(<v>)";
}
else if (cfe == CalcFuncEnum.NegLn)
{
exp = "-ln(<v>)";
}
else if (cfe == CalcFuncEnum.Sqrt)
{
exp = "sqrt(<v>)";
}
else if (cfe == CalcFuncEnum.Square)
{
exp = "power(<v>,2)";
}
else if (cfe == CalcFuncEnum.AddConst)
{
exp = "(<v> + " + constant + ")";
}
else if (cfe == CalcFuncEnum.MultConst)
{
exp = "(<v> * " + constant + ")";
}
else if (cfe == CalcFuncEnum.NegLogMolConc)
{
exp = GetNegLogConcExpr(mc, 1);
}
else if (cfe == CalcFuncEnum.DaysSince)
{
exp = "(trunc(sysdate) - trunc(<v>))"; // truncate to whole days
}
else
{
throw new Exception("BuildFunctionExpression unexpected function " + function);
}
string fieldRef = '"' + mc.MetaTable.Name + "." + mc.Name + '"';
exp = exp.Replace("<v>", fieldRef);
return(exp);
}
/// <summary>
/// Parse serialized form of calculated field
/// </summary>
/// <param name="content"></param>
/// <returns></returns>
public static CalcField DeserializeOld(
string content)
{
MetaTable mt, mt2;
string mtName;
CalcField cf = new CalcField();
CalcFieldColumn c1 = cf.Column1;
CalcFieldColumn c2 = cf.Column2;
string[] sa = content.Split('\t');
cf.CalcType = CalcTypeEnum.Basic; // assume basic type
if (sa[0] != "")
{
mt = MetaTableCollection.GetWithException(sa[0]);
if (mt.SummarizedExists && !mt.UseSummarizedData)
{ // use summarized form if exists
mtName = mt.Name + MetaTable.SummarySuffix;
mt2 = MetaTableCollection.Get(mtName);
if (mt2 != null && mt2.GetMetaColumnByName(sa[1]) != null)
{
mt = mt2;
}
}
c1.MetaColumn = mt.GetMetaColumnByNameWithException(sa[1]);
if (c1.MetaColumn == null)
{
return(null);
}
c1.Function = sa[2];
c1.Constant = sa[3];
cf.Operation = sa[4];
}
if (sa.Length <= 5 || // old form with only first field defined
(sa.Length == 6 && String.IsNullOrEmpty(sa[5])))
{
cf.SetDerivedValues();
return(cf);
}
if (sa[6] != "")
{
mt = MetaTableCollection.GetWithException(sa[5]);
if (mt == null)
{
return(null);
}
if (mt.SummarizedExists && !mt.UseSummarizedData)
{ // use summarized form if exists
mtName = mt.Name + MetaTable.SummarySuffix;
mt2 = MetaTableCollection.Get(mtName);
if (mt2 != null && mt2.GetMetaColumnByName(sa[6]) != null)
{
mt = mt2;
}
}
c2.MetaColumn = mt.GetMetaColumnByNameWithException(sa[6]);
if (c2.MetaColumn == null)
{
return(null);
}
c2.Function = sa[7];
c2.Constant = sa[8];
}
if (sa.Length >= 13) // get other values if new form
{
EnumUtil.TryParse(sa[9], out cf.CalcType);
cf.AdvancedExpr = sa[10];
string obsoleteIncludedQueryXml = sa[11]; // obsolete cf.IncludedQueryXml
cf.Description = sa[12];
}
cf.SetDerivedValues();
return(cf);
}
/// <summary>
/// Set values derived from primary values
/// </summary>
public void SetDerivedValuesWithException()
{
MetaColumnType firstColumnType = MetaColumnType.Unknown;
MetaColumnType column2Type = MetaColumnType.Unknown;
List <MetaColumn> inputMetaColumns = GetInputMetaColumnList();
if (SourceColumnType == MetaColumnType.Unknown)
{
SourceColumnType = MetaColumnType.Number; // default type
}
if (CalcType == CalcTypeEnum.Basic)
{
if (MetaColumn1 != null)
{
SourceColumnType = MetaColumn1.DataType;
}
if (!String.IsNullOrEmpty(Operation))
{
OpEnum = ConvertCalcOpStringToEnum(ref Operation);
}
else
{
OpEnum = CalcOpEnum.None;
}
for (int ci = 0; ci < CfCols.Count; ci++)
{
CalcFieldColumn cfc = CfCols[ci];
SetDerivedColumnValues(SourceColumnType, cfc); // set the result type for the col
if (ci == 0)
{
firstColumnType = cfc.ResultColumnType;
}
else if (ci == 1)
{
column2Type = cfc.ResultColumnType;
}
if (ci > 0 && cfc.MetaColumn != null &&
MetaColumn1 != null && OpEnum != CalcOpEnum.None)
{ // be sure column types are compatible if binary op
if (!MetaColumn.AreCompatibleMetaColumnTypes(Column1.ResultColumnType, cfc.ResultColumnType))
{
throw new Exception("Incompatible column types");
}
if (MetaColumn.IsNumericMetaColumnType(MetaColumn1.DataType))
{
} // all operations allowed
else if (MetaColumn1.DataType == MetaColumnType.Date)
{
if (OpEnum != CalcOpEnum.Sub)
{
throw new Exception("Invalid date operation");
}
}
else if (MetaColumn1.DataType == MetaColumnType.Image)
{
if (OpEnum != CalcOpEnum.Overlay)
{
throw new Exception("Invalid Curve/Image operation");
}
}
else
{
throw new Exception("Invalid operation");
}
}
}
// Determine the result type from the source column types, functions, operations and mapping
PreclassificationlResultType = MetaColumnType.Unknown;
// Set result type before any classification is applied
if (firstColumnType == MetaColumnType.Integer && // see if integer type output
(column2Type == MetaColumnType.Integer || OpEnum == CalcOpEnum.None) &&
(OpEnum == CalcOpEnum.None || OpEnum == CalcOpEnum.Add || OpEnum == CalcOpEnum.Sub))
{
PreclassificationlResultType = MetaColumnType.Integer;
}
else if (MetaColumn.IsNumericMetaColumnType(firstColumnType))
{
PreclassificationlResultType = MetaColumnType.Number;
}
else if (firstColumnType == MetaColumnType.Date)
{
if (Column1.FunctionEnum == CalcFuncEnum.None && OpEnum == CalcOpEnum.None)
{
PreclassificationlResultType = MetaColumnType.Date;
}
else
{
PreclassificationlResultType = MetaColumnType.Integer; // either days elapsed or difference in date
}
}
else
{
PreclassificationlResultType = firstColumnType;
}
if (Classification != null) // set the type info for the classification
{
Classification.ColumnType = PreclassificationlResultType;
}
}
else if (CalcType == CalcTypeEnum.Advanced) // user defines result type via input
{
if (PreclassificationlResultType == MetaColumnType.Unknown)
{
PreclassificationlResultType = MetaColumnType.Number; // default to number
}
}
// Set final result type
FinalResultType = PreclassificationlResultType; // default type without classification
if (IsClassificationDefined)
{ // if mapping to class then class names determine the type
FinalResultType = MetaColumnType.Integer; // start assuming integer type
foreach (CondFormatRule rule in Classification.Rules)
{
if (Lex.IsInteger(rule.Name))
{
continue;
}
else if (Lex.IsDouble(rule.Name))
{
FinalResultType = MetaColumnType.Number;
}
else
{
FinalResultType = MetaColumnType.String;
break;
}
}
}
return;
}
/// <summary>
/// Deserialize CalcField from an XmlTextReader
/// </summary>
/// <param name="tr"></param>
/// <returns></returns>
public static CalcField Deserialize(
XmlTextReader tr)
{
string mtName, mcName, txt, errMsg = "";
MetaTable mt;
MetaColumn mc;
CalcField cf = new CalcField();
XmlUtil.GetStringAttribute(tr, "Name", ref cf.UserObject.Name);
txt = tr.GetAttribute("CalcType");
EnumUtil.TryParse(txt, out cf.CalcType);
if (cf.CalcType != CalcTypeEnum.Basic && cf.CalcType != CalcTypeEnum.Advanced)
{
cf.CalcType = CalcTypeEnum.Basic; // default to basic type
}
txt = tr.GetAttribute("SourceColumnType");
if (txt != null)
{
cf.SourceColumnType = MetaColumn.ParseMetaColumnTypeString(txt);
}
txt = tr.GetAttribute("PreclassificationlResultType");
if (txt != null)
{
cf.PreclassificationlResultType = MetaColumn.ParseMetaColumnTypeString(txt);
}
for (int ci = 1; ; ci++) // get the set of columns
{
mtName = tr.GetAttribute("Table" + ci);
if (String.IsNullOrEmpty(mtName))
{
//if (ci == 1) continue; // first col may be undefined
//else
if (ci > 1)
{
break; // if beyond the first then col then all done
}
}
if (ci > cf.CfCols.Count)
{
cf.CfCols.Add(new CalcFieldColumn());
}
CalcFieldColumn cfc = cf.CfCols[ci - 1];
mt = MetaTableCollection.Get(mtName);
if (mt != null)
{
mcName = tr.GetAttribute("Column" + ci);
if (mcName != null)
{
cfc.MetaColumn = mt.GetMetaColumnByName(mcName);
if (cfc.MetaColumn == null)
{
errMsg += "Unable to find column: " + mcName + " in data table " + mt.Label + "(" + mt.Name + ")\r\n";
}
}
}
else if (ci != 1)
{
errMsg += "Unable to find data table: " + mtName + "\r\n";
}
txt = tr.GetAttribute("Function" + ci);
if (txt != null)
{
cfc.Function = txt;
}
txt = tr.GetAttribute("Constant" + ci);
if (txt != null)
{
cfc.Constant = txt;
}
}
txt = tr.GetAttribute("Operation");
if (txt != null)
{
cf.Operation = txt;
}
XmlUtil.GetStringAttribute(tr, "AdvancedExpr", ref cf.AdvancedExpr);
XmlUtil.GetStringAttribute(tr, "OuterJoinRoot", ref cf.OuterJoinRoot);
XmlUtil.GetStringAttribute(tr, "ColumnLabel", ref cf.ColumnLabel);
XmlUtil.GetStringAttribute(tr, "Description", ref cf.Description);
XmlUtil.GetStringAttribute(tr, "Prompt", ref cf.Prompt);
if (!tr.IsEmptyElement)
{
tr.Read();
tr.MoveToContent();
if (Lex.Eq(tr.Name, "CondFormat")) // and cond format
{
if (!tr.IsEmptyElement)
{
cf.Classification = CondFormat.Deserialize(tr);
}
tr.Read(); // get next element
tr.MoveToContent();
}
if (!Lex.Eq(tr.Name, "CalcField") || tr.NodeType != XmlNodeType.EndElement)
{
throw new Exception("CalcField.Deserialize - Expected CalcField end element");
}
}
cf.SetDerivedValues();
return(cf);
}
public void Serialize(
XmlTextWriter tw)
{
tw.WriteStartElement("CalcField");
XmlUtil.WriteAttributeIfDefined(tw, "Name", Name);
tw.WriteAttributeString("CalcType", CalcType.ToString());
tw.WriteAttributeString("SourceColumnType", SourceColumnType.ToString());
if (PreclassificationlResultType != MetaColumnType.Unknown)
{
tw.WriteAttributeString("PreclassificationlResultType", PreclassificationlResultType.ToString());
}
for (int ci = 0; ci < CfCols.Count; ci++)
{
CalcFieldColumn cfc = CfCols[ci];
if (cfc == null)
{
continue;
}
int ci1 = ci + 1;
if (cfc.MetaColumn != null)
{
tw.WriteAttributeString("Table" + ci1, cfc.MetaColumn.MetaTable.Name);
tw.WriteAttributeString("Column" + ci1, cfc.MetaColumn.Name);
}
if (cfc.Function != null)
{
tw.WriteAttributeString("Function" + ci1, cfc.Function);
}
if (cfc.Constant != "")
{
tw.WriteAttributeString("Constant" + ci1, cfc.Constant);
}
}
tw.WriteAttributeString("Operation", Operation);
if (!String.IsNullOrEmpty(AdvancedExpr))
{
tw.WriteAttributeString("AdvancedExpr", AdvancedExpr);
}
if (!String.IsNullOrEmpty(OuterJoinRoot))
{
tw.WriteAttributeString("OuterJoinRoot", OuterJoinRoot);
}
if (!String.IsNullOrEmpty(ColumnLabel))
{
tw.WriteAttributeString("ColumnLabel", ColumnLabel);
}
if (!String.IsNullOrEmpty(Description))
{
tw.WriteAttributeString("Description", Description);
}
if (!String.IsNullOrEmpty(Prompt))
{
tw.WriteAttributeString("Prompt", Prompt);
}
if (Classification != null)
{
Classification.Serialize(tw);
}
tw.WriteEndElement();
}