Пример #1
0
        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(";");
            }
        }
Пример #2
0
        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;
            }
        }