public static void Write(this CalcFormulaProperty property, bool isLastProperty, PropertiesStyle style, CSideWriter writer)
{
var requiresSquareBrackets = property.Value.TableFilter.Any(f => f.Value.Any(c => "{}".Contains(c)));
var openingBracket = requiresSquareBrackets ? "[" : "";
var closingBracket = requiresSquareBrackets ? "]" : "";
var sign = property.Value.ReverseSign ? "-" : "";
writer.Write($"{property.Name}={openingBracket}{sign}{property.Value.Method}(");
switch (property.Value.Method.Value)
{
case CalcFormulaMethod.Exist:
case CalcFormulaMethod.Count:
writer.Write(property.Value.TableName.QuotedExcept('-', '/', '.'));
break;
case CalcFormulaMethod.Lookup:
case CalcFormulaMethod.Average:
case CalcFormulaMethod.Max:
case CalcFormulaMethod.Min:
case CalcFormulaMethod.Sum:
writer.Write("{0}.{1}", property.Value.TableName.QuotedExcept('-', '/', '.'), property.Value.FieldName.QuotedExcept('-', '/', '.'));
break;
}
if (property.Value.TableFilter.Any())
{
writer.Write(" WHERE (");
writer.Indent(writer.Column);
foreach (var tableFilterLine in property.Value.TableFilter)
{
var value = tableFilterLine.Value;
if (tableFilterLine.ValueIsFilter)
{
value = string.Format("FILTER({0})", value);
}
if (tableFilterLine.OnlyMaxLimit)
{
value = string.Format("UPPERLIMIT({0})", value);
}
writer.Write("{0}={1}({2})", tableFilterLine.FieldName, tableFilterLine.Type.AsString(), value);
if (tableFilterLine != property.Value.TableFilter.Last())
{
writer.WriteLine(",");
}
}
writer.Write(")");
writer.Unindent();
}
writer.Write(")");
writer.Write(closingBracket);
if (!isLastProperty)
{
writer.WriteLine(";");
}
}
internal static void SetCalcFormulaProperty(this CalcFormulaProperty property, string propertyValue)
{
// CalcFormula=-Sum("Detailed Vendor Ledg. Entry".Amount WHERE (Vendor No.=FIELD(Vendor Filter), Initial Entry Global Dim. 1=FIELD(Global Dimension 1 Filter), Initial Entry Global Dim. 2=FIELD(Global Dimension 2 Filter), Initial Entry Due Date=FIELD(Date Filter), Posting Date=FIELD(UPPERLIMIT(Date Filter)), Currency Code=FIELD(Code)))
// In some cases (possibly if it contains curly brackets), the calc formula is surrounded with square brackets - remove those
// [Count("User Security Status" WHERE (Reviewed=CONST(No), User Security ID = FILTER(<>{ 00000000 - 0000 - 0000 - 0000 - 000000000000})))]
propertyValue = Regex.Replace(propertyValue, @"^\[(.*)\]$", "$1");
// Remove closing bracket at the end of the property value, or
// "Lookup(Table.Field)" in table 5329 will fail.
if (propertyValue.EndsWith(")"))
{
propertyValue = propertyValue.Substring(0, propertyValue.Length - 1);
}
var reverseSign = GetCalcFormulaReverseSign(ref propertyValue);
var method = GetCalcFormulaMethodText(ref propertyValue).ToEnum <CalcFormulaMethod>();
var tableName = GetCalcFormulaTableName(ref propertyValue);
var fieldName = GetCalcFormulaFieldName(ref propertyValue);
var filters = GetCalcFormulaFilters(ref propertyValue);
property.Value.ReverseSign = reverseSign;
property.Value.Method = method;
property.Value.TableName = tableName;
property.Value.FieldName = fieldName;
foreach (var filter in filters)
{
var calcFormulaTableFilterLine = property.Value.TableFilter.Add(new CalcFormulaTableFilterLine(filter.FieldName, filter.Type.ToEnum <TableFilterType>(), filter.Value));
calcFormulaTableFilterLine.ValueIsFilter = filter.ValueIsFilter;
calcFormulaTableFilterLine.OnlyMaxLimit = filter.OnlyMaxLimit;
}
}
