/// <summary> /// todoComment /// </summary> /// <param name="precalculatedColumns"></param> /// <returns></returns> public TVariant Precalculate(TVariant[] precalculatedColumns) { String strCalculation; TRptDataCalcCalculation rptDataCalcCalculation; TRptCalculation rptCalculation; TVariant ReturnValue = new TVariant(); // calculation is used for display in the GUI, formula is used for adding ledgers if ((!GetParameters().Exists("param_calculation", column, Depth))) { return(ReturnValue); } if (GetParameters().Exists("param_formula", column, Depth)) { strCalculation = GetParameters().Get("param_formula", column, Depth).ToString(); } else { strCalculation = GetParameters().Get("param_calculation", column, Depth).ToString(); } rptCalculation = ReportStore.GetCalculation(CurrentReport, strCalculation); if (rptCalculation == null) { ReturnValue = EvaluateFunctionCalculation(strCalculation, precalculatedColumns); } else { rptDataCalcCalculation = new TRptDataCalcCalculation(this); if (!rptDataCalcCalculation.EvaluateCalculationFunction(rptCalculation.rptGrpQuery, ref precalculatedColumns, ref ReturnValue)) { ReturnValue = rptDataCalcCalculation.EvaluateCalculationAll(rptCalculation, null, rptCalculation.rptGrpTemplate, rptCalculation.rptGrpQuery).VariantValue; if (ReturnValue.IsZeroOrNull()) { ReturnValue.ApplyFormatString(rptCalculation.strReturnsFormat); return(ReturnValue); } // Removed some code from here due to Bug 3081 Reports crash if data contains the word SELECT // Even if there were a case for running SQL at this point, it wouldn't work anyway due // to the "" parameter in the call to DatabaseConnection.SelectDT(). } ReturnValue.ApplyFormatString(rptCalculation.strReturnsFormat); } return(ReturnValue); }
/// <summary> /// todoComment /// </summary> protected void InitColumnCaptions() { TRptCalculation rptCalculation; TRptDataCalcValue rptDataCalcValue; int column; for (column = 0; column <= Results.GetMaxDisplayColumns() - 1; column += 1) { rptCalculation = ReportStore.GetCalculation(CurrentReport, Parameters.Get("param_calculation", column).ToString()); if (rptCalculation != null) { if (rptCalculation.strAlign.Length != 0) { Parameters.Add("ColumnAlign", new TVariant(rptCalculation.strAlign), column); } else { if (StringHelper.IsCurrencyFormatString(rptCalculation.strReturnsFormat) || (rptCalculation.strReturnsFormat.ToLower().IndexOf("percentage") != -1)) { Parameters.Add("ColumnAlign", new TVariant("right"), column); } else { Parameters.Add("ColumnAlign", new TVariant("left"), column); } } if (rptCalculation.strReturnsFormat.Length != 0) { Parameters.Add("ColumnFormat", new TVariant(rptCalculation.strReturnsFormat), column, -1); } if (rptCalculation.rptGrpCaption != null) { rptDataCalcValue = new TRptDataCalcValue(this, -1, column, -1, 0); FormatCaption("ColumnCaption", rptDataCalcValue.Calculate(rptCalculation.rptGrpCaption), column); } if (rptCalculation.rptGrpShortCaption != null) { rptDataCalcValue = new TRptDataCalcValue(this, -1, column, -1, 0); FormatCaption("ColumnShortCaption", rptDataCalcValue.Calculate(rptCalculation.rptGrpShortCaption), column); } } } }
/// <summary> /// todoComment /// </summary> /// <param name="columnNr"></param> protected void InitColumnFormat(Int32 columnNr) { TRptCalculation rptCalculation; String calculation; Int32 columnReferenced; String format; Int32 posColumn; format = ""; // if the format already has been set, leave it if (Parameters.Exists("ColumnFormat", columnNr, -1, eParameterFit.eExact)) { return; } calculation = Parameters.Get("param_calculation", columnNr).ToString(); // check the calculation for the format rptCalculation = ReportStore.GetCalculation(CurrentReport, calculation); if (rptCalculation != null) { format = rptCalculation.strReturnsFormat; } else if (calculation.IndexOf("column(") != -1) { // if it is a calculation based on other columns, get the format of the first column posColumn = calculation.IndexOf("column(") + 7; columnReferenced = Convert.ToInt32(calculation.Substring(posColumn, calculation.IndexOf(')', posColumn) - posColumn)); format = Parameters.Get("ColumnFormat", columnReferenced).ToString(); } if (format.Length != 0) { Parameters.Add("ColumnFormat", new TVariant(format), columnNr); } }
/// <summary> /// function selection /// </summary> /// <param name="f">function name</param> /// <param name="ops">if ops[1] is nil, and f cannot be evaluated, return nil; otherwise if ops is not nil, print an error if f cannot be evaluated</param> /// <returns>void</returns> private TVariant FunctionSelector(String f, TVariant[] ops) { TVariant ReturnValue = null; TRptUserFunctions rptUserFunctions; String s; String s2; System.Int32 start; System.Int32 length; String logMessage; bool FunctionFound; int counter; f = f.ToLower(); TLogging.SetContext("call to function " + f); TParameterList myParams = GetParameters(); if ((f == "eq") || (f == "ne")) { // check if at least one of the parameters is a variable; otherwise give warning if ((ops[1].ToString().IndexOf('{') == -1) && (ops[2].ToString().IndexOf('{') == -1)) { TLogging.Log( "Warning: comparison should contain at least one variable: " + f.ToString() + '(' + ops[1].ToString() + ',' + ops[2].ToString( ) + ')', TLoggingType.ToLogfile | TLoggingType.ToConsole); } } if ((f == "isnull") || (f == "exists") || (f == "or") || (f == "and") || (f == "iif") || (f == "assign")) { // need to replace the variables manually // either because we don't want them replaced at all (isnull or exists needs the variable name), // or because we don't want to evaluate the second parameter if the first one already defines the result (e.g. or) } else { for (counter = 1; counter <= ReportingConsts.MAX_FUNCTION_PARAMETER; counter += 1) { ops[counter] = EvaluateOperand(ops[counter]); } } if (f == "eq") { ReturnValue = new TVariant(ops[1].CompareToI(ops[2]) == 0); } else if (f == "ne") { ReturnValue = new TVariant(ops[1].CompareToI(ops[2]) != 0); } else if (f == "lt") { ReturnValue = new TVariant(ops[1].CompareTo(ops[2]) < 0); } else if (f == "le") { ReturnValue = new TVariant(ops[1].CompareTo(ops[2]) <= 0); } else if (f == "gt") { ReturnValue = new TVariant(ops[1].CompareTo(ops[2]) > 0); } else if (f == "ge") { ReturnValue = new TVariant(ops[1].CompareTo(ops[2]) >= 0); } else if (f == "sub") { ReturnValue = new TVariant(ops[1].ToDecimal() - ops[2].ToDecimal()); } else if (f == "adddays") { ReturnValue = new TVariant(ops[1].ToDate().AddDays(ops[2].ToDouble())); } else if (f == "add") { ReturnValue = new TVariant(ops[1].ToDecimal() + ops[2].ToDecimal()); } else if (f == "additems") { length = ops[1].ToInt() + 1; decimal result = 0.0M; for (counter = 2; counter <= length; ++counter) { result += ops[counter].ToDecimal(); } ReturnValue = new TVariant(result); } else if (f == "mul") { ReturnValue = new TVariant(ops[1].ToDecimal() * ops[2].ToDecimal()); } else if (f == "div") { if (ops[2].ToDecimal() == 0) { ReturnValue = new TVariant(0.0); } else { ReturnValue = new TVariant(ops[1].ToDecimal() / ops[2].ToDecimal()); } } else if (f == "mod") { if (ops[2].ToDecimal() == 0) { ReturnValue = new TVariant(0.0); } else { ReturnValue = new TVariant(ops[1].ToInt64() % ops[2].ToInt64()); } } else if (f == "floor") { ReturnValue = new TVariant(Math.Floor(ops[1].ToDecimal())); } else if (f == "round") { ReturnValue = new TVariant(Math.Round(ops[1].ToDecimal())); } else if (f == "not") { ReturnValue = new TVariant(!ops[1].ToBool()); } else if (f == "iif") { // iif ( condition, value_if_true, value_if_false ) ops[1] = EvaluateOperand(ops[1]); if (ops[1].ToBool() == true) { ops[2] = EvaluateOperand(ops[2]); ReturnValue = new TVariant(ops[2]); } else { ops[3] = EvaluateOperand(ops[3]); ReturnValue = new TVariant(ops[3]); } } else if (f == "or") { ops[1] = EvaluateOperand(ops[1]); if (ops[1].ToBool() == false) { ops[2] = EvaluateOperand(ops[2]); ReturnValue = new TVariant(ops[2].ToBool()); } else { ReturnValue = new TVariant(true); } } else if (f == "and") { ops[1] = EvaluateOperand(ops[1]); if (ops[1].ToBool() == true) { ops[2] = EvaluateOperand(ops[2]); ReturnValue = new TVariant(ops[2].ToBool()); } else { ReturnValue = new TVariant(false); } } else if (f == "log") { if (ops[2] != null) { ReturnValue = new TVariant(ops[1].ToString() + " " + ops[2].ToString()); } else { if (myParams.Exists(ops[1].ToString())) { myParams.Debug(ops[1].ToString()); ReturnValue = new TVariant(); } else { ReturnValue = ops[1]; } } if (!ReturnValue.IsNil()) { TLogging.Log(ReturnValue.ToString()); } } else if (f == "length") { ReturnValue = new TVariant(ops[1].ToString().Length); } else if (StringHelper.IsSame(f, "ContainsCSV")) { ReturnValue = new TVariant(StringHelper.ContainsCSV(ops[1].ToString(), ops[2].ToString())); } else if (f == "replace") { ReturnValue = new TVariant(ops[1].ToString().Replace(ops[2].ToString(), ops[3].ToString())); } else if ((f == "substring") || (f == "substr")) { s = ops[1].ToString(); start = ops[2].ToInt(); length = ops[3].ToInt(); if ((start < s.Length) && (start + length <= s.Length) && (length > 0)) { ReturnValue = new TVariant(s.Substring(start, length)); } else { ReturnValue = new TVariant(""); TLogging.Log("Text is not long enough or length is wrong: " + s + ' ' + start.ToString() + ' ' + length.ToString()); } } else if ((f == "substringright") || (f == "substrright")) { s = ops[1].ToString(); length = ops[2].ToInt(); start = s.Length - length; if ((start < s.Length) && (start + length <= s.Length) && (length > 0)) { ReturnValue = new TVariant(s.Substring(start, length)); } else { ReturnValue = new TVariant(""); TLogging.Log("Text is not long enough or length is wrong: " + s + ' ' + start.ToString() + ' ' + length.ToString()); } } else if ((f == "substringwithoutright") || (f == "substrwithoutright")) { s = ops[1].ToString(); length = s.Length - ops[2].ToInt(); start = 0; if ((start < s.Length) && (start + length <= s.Length) && (length > 0)) { ReturnValue = new TVariant(s.Substring(start, length)); } else { ReturnValue = new TVariant(""); TLogging.Log("Text is not long enough or length is wrong: " + s + ' ' + start.ToString() + ' ' + length.ToString()); } } else if (f == "concatenate") { s = ops[1].ToString(); s = s + ops[2].ToString(); ReturnValue = new TVariant(s); } else if (f == "concatenateww") { s = ops[1].ToString(); length = ops[3].ToInt(); s = s.PadRight(s.Length + length); s = s + ops[2].ToString(); ReturnValue = new TVariant(s); } else if (f == "concatenatewithcomma") { s = ops[1].ToString(); s2 = ops[2].ToString(); if ((s.Length > 0) && (s2.Length > 0)) { s = s + ", "; } s = s + s2; ReturnValue = new TVariant(s); } else if (f == "format") { ReturnValue = new TVariant(ops[1].ToFormattedString(ops[2].ToString())); } else if (f == "formattime") { String separator = ops[1].ToString(); String hour = ops[2].ToString(); String min = ops[3].ToString(); String sec = ""; if ((ops.Length > 4) && (ops[4] != null)) { sec = ops[4].ToString(); } if (hour.Length < 2) { hour = "0" + hour; } if (min.Length < 2) { min = "0" + min; } if ((sec.Length < 2) && (sec.Length > 0)) { sec = "0" + sec; } if (sec.Length > 0) { ReturnValue = new TVariant(hour + separator + min + separator + sec); } else { ReturnValue = new TVariant(hour + separator + min); } } else if (f == "assign") { string targetVariableName = ops[1].ToString(); if (targetVariableName.StartsWith("{") && targetVariableName.EndsWith("}")) { targetVariableName = targetVariableName.Substring(1, targetVariableName.Length - 2); } ops[2] = EvaluateOperand(ops[2]); if (myParams.Exists(targetVariableName)) { // we should overwrite the existing variable, not add on another level TParameter origParameter = myParams.GetParameter(targetVariableName); origParameter.value = ops[2]; } else { myParams.Add(targetVariableName, ops[2], -1, -1, null, null, ReportingConsts.CALCULATIONPARAMETERS); } // TLogging.Log("Assign: " + targetVariableName + "=" + ops[2].ToString()); ReturnValue = ops[2]; } else if (f == "exists") { ReturnValue = new TVariant(myParams.Exists(ops[1].ToString(), column, Depth)); } else if (f == "isnull") { ReturnValue = new TVariant((!myParams.Exists(ops[1].ToString(), column, Depth) || myParams.Get(ops[1].ToString(), column, Depth).IsZeroOrNull())); } else if (f == "template") { TRptCalculation rptTemplate = ReportStore.GetCalculation(CurrentReport, ops[1].ToString()); TRptDataCalcCalculation rptTempCalculation = new TRptDataCalcCalculation(this); ReturnValue = rptTempCalculation.Calculate(rptTemplate, null).VariantValue; } else if (f == "columnexist") { String ColumnID = ops[1].ToString(); bool ColumnExist = false; System.Data.DataTable TempTable = myParams.ToDataTable(); int numColumns = TempTable.Columns.Count; foreach (System.Data.DataRow Row in TempTable.Rows) { for (int Counter = 0; Counter < numColumns; ++Counter) { if (Row[Counter].ToString() == ColumnID) { ColumnExist = true; break; } } if (ColumnExist) { break; } } ReturnValue = new TVariant(ColumnExist); } else if (f == "conditionrow") { ReturnValue = new TVariant(ops[1]); if (ReturnValue.ToBool() == false) { // clear this row, we don't want to display it // set all parameters of this row to NULL myParams.Add("DONTDISPLAYROW", new TVariant(true)); } else { myParams.Add("DONTDISPLAYROW", new TVariant(false), -1, -1, null, null, ReportingConsts.CALCULATIONPARAMETERS); } } else if (f == "column") { if ((ops[1].ToInt() >= 0) && (ops[1].ToInt() < CurrentColumns.Length)) { ReturnValue = new TVariant(CurrentColumns[ops[1].ToInt()]); } else { TLogging.Log("referenced column does not exist: " + ops[1].ToString()); ReturnValue = new TVariant(); } } else if (f == "HasColumns".ToLower()) { ReturnValue = new TVariant(Results.HasColumns(this.LineId)); } else if (f == "HasChildRows".ToLower()) { ReturnValue = new TVariant(Results.HasChildRows(this.LineId)); } else if (f == "CountChildRows".ToLower()) { ReturnValue = new TVariant(Results.CountChildRows(this.LineId)); } else if (f == "HasChildColumns".ToLower()) { ReturnValue = new TVariant(Results.HasChildColumns(this.LineId)); } else if (f == "invisible".ToLower()) { // need to return true so that calculation happens. ReturnValue = new TVariant(true); } else if (f == "fatherColumn") { ReturnValue = GetParentValue(ParentRowId, ops[1].ToInt()); } else if (f == "childColumn") { ReturnValue = GetChildValue(LineId, ops[1].ToInt()); } else if (StringHelper.IsSame(f, "SecondLevelColumn")) { ReturnValue = Get2ndLevelValue(ParentRowId, ops[1].ToInt()); } else if (StringHelper.IsSame(f, "FirstLevelColumn")) { ReturnValue = Get1stLevelValue(ParentRowId, ops[1].ToInt()); } else if (StringHelper.IsSame(f, "GetShortCaption")) { ReturnValue = new TVariant(GetShortCaption(ops[1].ToInt())); } else if (StringHelper.IsSame(f, "GetCaption")) { ReturnValue = new TVariant(GetCaption(ops[1].ToInt())); } else if (StringHelper.IsSame(f, "getSumLower2Report")) { if (ops[3] == null) { ReturnValue = new TVariant(GetSumLower2Report(ops[1].ToInt(), ops[2].ToInt(), true), "currency"); } else { ReturnValue = new TVariant(GetSumLower2Report(ops[1].ToInt(), ops[2].ToInt(), ops[3].ToBool()), "currency"); } } else if (StringHelper.IsSame(f, "getSumLowerReport")) { if (ops[3] == null) { ReturnValue = new TVariant(GetSumLowerReport(ops[1].ToInt(), ops[2].ToInt(), true), "currency"); } else { ReturnValue = new TVariant(GetSumLowerReport(ops[1].ToInt(), ops[2].ToInt(), ops[3].ToBool()), "currency"); } } else if (StringHelper.IsSame(f, "getSumLowerReportCredit")) { ReturnValue = new TVariant(GetSumLowerReportCredit(ops[1].ToInt(), ops[2].ToInt()), "currency"); } else { FunctionFound = false; foreach (System.Type userFunctionsClass in FUserFunctions) { if (!FunctionFound) { rptUserFunctions = (TRptUserFunctions)Activator.CreateInstance(userFunctionsClass); if (rptUserFunctions.FunctionSelector(this, f, ops, out ReturnValue)) { FunctionFound = true; break; } rptUserFunctions = null; } } if (!FunctionFound) { TRptCalculation calculation = ReportStore.GetCalculation(CurrentReport, f); if (calculation != null) { TRptDataCalcCalculation calc = new TRptDataCalcCalculation(this); ReturnValue = calc.EvaluateCalculation(calculation, null, String.Empty, -1); } else if (ops[1] == null) { // don't print an error if ops[1] is null; // just return f; // this is needed e.g. for HasChildRows etc, called from TRptEvaluator.evaluateOperator ReturnValue = null; } else { ReturnValue = new TVariant(); logMessage = "unknown function " + f; if (ops[1] != null) { logMessage = logMessage + ' ' + ops[1].ToString(); } if (ops[2] != null) { logMessage = logMessage + ' ' + ops[2].ToString(); } TLogging.Log(logMessage); } } } TLogging.SetContext(""); return(ReturnValue); }
/// <summary> /// todoComment /// </summary> /// <param name="precalculatedColumns"></param> /// <returns></returns> public TVariant Precalculate(TVariant[] precalculatedColumns) { TVariant ReturnValue; String strCalculation; TRptDataCalcCalculation rptDataCalcCalculation; TRptCalculation rptCalculation; DataTable tab; ReturnValue = new TVariant(); // calculation is used for display in the GUI, formula is used for adding ledgers if ((!GetParameters().Exists("param_calculation", column, Depth))) { return(ReturnValue); } if (GetParameters().Exists("param_formula", column, Depth)) { strCalculation = GetParameters().Get("param_formula", column, Depth).ToString(); } else { strCalculation = GetParameters().Get("param_calculation", column, Depth).ToString(); } rptCalculation = ReportStore.GetCalculation(CurrentReport, strCalculation); if (rptCalculation == null) { ReturnValue = EvaluateFunctionCalculation(strCalculation, precalculatedColumns); } else { rptDataCalcCalculation = new TRptDataCalcCalculation(this); if (!rptDataCalcCalculation.EvaluateCalculationFunction(rptCalculation.rptGrpQuery, ref precalculatedColumns, ref ReturnValue)) { ReturnValue = rptDataCalcCalculation.EvaluateCalculationAll(rptCalculation, null, rptCalculation.rptGrpTemplate, rptCalculation.rptGrpQuery); if (ReturnValue.IsZeroOrNull()) { ReturnValue.ApplyFormatString(rptCalculation.strReturnsFormat); return(ReturnValue); } int SelectPos = ReturnValue.ToString().ToUpper().IndexOf("SELECT"); if ((SelectPos >= 0) && (SelectPos <= 3)) { // this is an sql statement and not a function result tab = DatabaseConnection.SelectDT(ReturnValue.ToString(), "", DatabaseConnection.Transaction); if (tab.Rows.Count > 0) { if (tab.Rows[0][0].GetType() == typeof(String)) { ReturnValue = new TVariant(Convert.ToString(tab.Rows[0][0])); } else { ReturnValue = new TVariant(tab.Rows[0][0]); } } } } ReturnValue.ApplyFormatString(rptCalculation.strReturnsFormat); } return(ReturnValue); }