private string GetAllAccountDescendants(int pv_ledger_number_i, string pv_account_code_c, string pv_account_hierarchy_c)
{
string ReturnValue = "";
parameters.Add("param_parentaccountcode", pv_account_code_c);
TRptCalculation rptCalculation = situation.GetReportStore().GetCalculation(situation.GetCurrentReport(), "Select AllAccountDescendants");
TRptDataCalcCalculation rptDataCalcCalculation = new TRptDataCalcCalculation(situation);
TVariant calculationResult = rptDataCalcCalculation.EvaluateCalculationAll(rptCalculation,
null,
rptCalculation.rptGrpTemplate,
rptCalculation.rptGrpQuery);
if (calculationResult.IsZeroOrNull())
{
return ReturnValue;
}
if ((calculationResult.ToString().ToUpper().IndexOf("SELECT") >= 0) && (calculationResult.ToString().ToUpper().IndexOf("SELECT") <= 3))
{
// this is an sql statement and not a function result
DataTable tab = AccountDescendantsCache.GetDataTable(situation.GetDatabaseConnection(), calculationResult.ToString());
foreach (DataRow row in tab.Rows)
{
if (!Convert.ToBoolean(row["a_posting_status_l"]))
{
ReturnValue =
StringHelper.ConcatCSV(ReturnValue,
GetAllAccountDescendants(pv_ledger_number_i, Convert.ToString(row["line_a_account_code_c"]), pv_account_hierarchy_c));
}
else
{
ReturnValue =
StringHelper.AddCSV(ReturnValue,
Convert.ToString(row["line_a_account_code_c"]));
ReturnValue =
StringHelper.AddCSV(ReturnValue,
Convert.ToString(row["line_a_account_alias_c"]));
ReturnValue =
StringHelper.AddCSV(ReturnValue,
Convert.ToString(row["account_code_short_desc"]));
ReturnValue = StringHelper.AddCSV(ReturnValue, Convert.ToString(row["debit_credit_indicator"]));
}
}
}
return ReturnValue;
}
private bool GetPartnerLabelValues()
{
PPartnerTable PartnerTable;
System.Data.DataTable mTable;
System.Int32 col;
TRptCalculation mRptCalculation;
TRptDataCalcCalculation mRptDataCalcCalculation;
TVariant mRptCalcResult;
mRptCalculation = situation.GetReportStore().GetCalculation(situation.GetCurrentReport(), "PartnerLabelValue");
mRptDataCalcCalculation = new TRptDataCalcCalculation(situation);
mRptCalcResult = mRptDataCalcCalculation.EvaluateCalculationAll(mRptCalculation,
null,
mRptCalculation.rptGrpTemplate,
mRptCalculation.rptGrpQuery);
List <int>AddedColumns = new List <int>();
if (mRptCalcResult.IsZeroOrNull())
{
return false;
}
mTable = situation.GetDatabaseConnection().SelectDT(mRptCalcResult.ToString(), "table", situation.GetDatabaseConnection().Transaction);
foreach (DataRow mRow in mTable.Rows)
{
TVariant LabelValue = new TVariant();
// Data Type: (char | integer | float | currency | boolean | date | time | partnerkey | lookup)
if (mRow["LabelDataType"].ToString() == "char")
{
LabelValue = new TVariant(mRow["LabelValueChar"].ToString());
}
else if (mRow["LabelDataType"].ToString() == "integer")
{
LabelValue = new TVariant(mRow["LabelValueInt"]);
}
else if (mRow["LabelDataType"].ToString() == "float")
{
// todo p_num_decimal_places_i
LabelValue = new TVariant(mRow["LabelValueNum"]);
}
else if (mRow["LabelDataType"].ToString() == "currency")
{
LabelValue = new TVariant(StringHelper.FormatUsingCurrencyCode(
Convert.ToDecimal(mRow["LabelValueCurrency"]), mRow["CurrencyCode"].ToString()) +
' ' + mRow["CurrencyCode"].ToString());
}
else if (mRow["LabelDataType"].ToString() == "boolean")
{
LabelValue = new TVariant(mRow["LabelValueBool"]);
}
else if (mRow["LabelDataType"].ToString() == "date")
{
LabelValue = new TVariant(mRow["LabelValueDate"]);
}
else if (mRow["LabelDataType"].ToString() == "time")
{
// todo needs testing
LabelValue = new TVariant(Conversions.Int32TimeToDateTime(Convert.ToInt32(mRow["LabelValueTime"])).ToString("t"));
}
else if (mRow["LabelDataType"].ToString() == "lookup")
{
// todo p_lookup_category_code_c
LabelValue = new TVariant(mRow["LabelValueLookup"]);
}
else if (mRow["LabelDataType"].ToString() == "partnerkey")
{
// retrieve the shortname of this partner
LabelValue = new TVariant(mRow["LabelValuePartnerKey"]);
PartnerTable = PPartnerAccess.LoadByPrimaryKey(LabelValue.ToInt64(),
StringHelper.StrSplit(PPartnerTable.GetPartnerShortNameDBName(), ","), situation.GetDatabaseConnection().Transaction);
if (PartnerTable.Rows.Count != 0)
{
LabelValue = new TVariant(PartnerTable[0].PartnerShortName);
}
}
else
{
LabelValue = new TVariant("unknown data label type");
}
for (col = 0; col <= situation.GetParameters().Get("MaxDisplayColumns").ToInt() - 1; col += 1)
{
if (!AddedColumns.Contains(col))
{
situation.GetParameters().RemoveVariable("LabelValue", col, situation.GetDepth(), eParameterFit.eBestFit);
}
if (situation.GetParameters().Exists("param_label", col, -1, eParameterFit.eExact))
{
if (mRow["LabelName"].ToString() == situation.GetParameters().Get("param_label", col, -1, eParameterFit.eExact).ToString())
{
if (!LabelValue.IsNil())
{
situation.GetParameters().Add("LabelValue",
LabelValue,
col, situation.GetDepth(),
null, null, ReportingConsts.CALCULATIONPARAMETERS);
AddedColumns.Add(col);
}
}
}
}
}
return true;
}
/// <summary>
/// Returns a data table that is the result of a sql statement from the xml file.
/// If ReplaceString and Replacement are not null then the ReplaceString that is in
/// the sql command is replaced by replacement.
/// </summary>
/// <param name="ASqlID">the identifier of the sql statement</param>
/// <param name="ADataTable">Result</param>
/// <param name="ReplaceString">String in the sql command that should be replaced</param>
/// <param name="Replacement">the replacement</param>
/// <returns>true if successful, otherwise false</returns>
private bool GetDataTableFromXmlSqlStatement(String ASqlID, out DataTable ADataTable, String ReplaceString, String Replacement)
{
ADataTable = null;
TRptCalculation ReportCalculation;
TRptDataCalcCalculation ReportDataCalcCalculation;
TVariant ReportCalcResult;
ReportCalculation = situation.GetReportStore().GetCalculation(situation.GetCurrentReport(), ASqlID);
ReportDataCalcCalculation = new TRptDataCalcCalculation(situation);
ReportCalcResult = ReportDataCalcCalculation.EvaluateCalculationAll(ReportCalculation,
null, ReportCalculation.rptGrpTemplate, ReportCalculation.rptGrpQuery);
if (ReportCalcResult.IsZeroOrNull())
{
return false;
}
String SqlStatement = ReportCalcResult.ToString();
if ((ReplaceString != null)
&& (Replacement != null))
{
SqlStatement = SqlStatement.Replace(ReplaceString, Replacement);
}
ADataTable = situation.GetDatabaseConnection().SelectDT(SqlStatement, "table", situation.GetDatabaseConnection().Transaction);
return true;
}
/// <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);
}
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 == "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="rptGrpValue"></param>
/// <param name="AWithSpaceBetweenValues"></param>
/// <returns></returns>
public TVariant Calculate(List <TRptValue>rptGrpValue, Boolean AWithSpaceBetweenValues)
{
TVariant ReturnValue;
String listValue;
String listText;
Boolean first;
TRptDataCalcCalculation rptDataCalcCalculation;
ReturnValue = new TVariant();
foreach (TRptValue rptValue in rptGrpValue)
{
if (EvaluateCondition(rptValue.strCondition))
{
if ((AWithSpaceBetweenValues == true) && (ReturnValue.TypeVariant != eVariantTypes.eEmpty))
{
ReturnValue.Add(new TVariant(' '));
}
if (rptValue.strFunction == "csv")
{
// this is only for sql queries
listValue = Parameters.Get(rptValue.strVariable, column, Depth).ToString();
if ((listValue.Length > 3) && (listValue.Substring(0, 4) == "CSV:"))
{
listValue = listValue.Substring(4);
}
first = true;
ReturnValue.Add(new TVariant(" ("));
if (listValue.Length == 0)
{
// this would make the sql query invalid; so insert a false condition to at least make it run, and return no records
ReturnValue.Add(new TVariant(" 1 = 0 "));
}
while (listValue.Length != 0)
{
bool IsFirstPair = true;
string value = StringHelper.GetNextCSV(ref listValue).Trim();
if (first)
{
first = false;
}
else
{
ReturnValue.Add(new TVariant(" OR "));
}
if (rptValue.strText.IndexOf(',') != -1)
{
// there are two things to compare (e.g. motivation group and motivation detail)
// motivation group and detail come in pairs, separated by comma, enclosed by quotes (getNextCSV strips the quotes)
string valuePair = value;
listText = rptValue.strText;
string calculationText = rptValue.strCalculation;
// if the format is defined in the xml file, it overwrites the value we get from (new TVariant(value).TypeVariant == eVariantTypes.xxx
bool ValueIsNumber = (rptValue.strFormat == "Number");
bool ValueIsText = (rptValue.strFormat == "Text");
ReturnValue.Add(new TVariant('('));
while (listText.Length != 0)
{
if (IsFirstPair)
{
// we have taken out the first value already from listValue
value = valuePair;
IsFirstPair = false;
}
else
{
value = StringHelper.GetNextCSV(ref listValue).Trim();
}
if (listText.Length < rptValue.strText.Length)
{
if (calculationText.Length > 0)
{
ReturnValue.Add(new TVariant(" " + calculationText + " "));
}
else
{
ReturnValue.Add(new TVariant(" AND "));
}
}
//todo: allow integer as well; problem with motivation detail codes that are just numbers;
//todo: specify type with text, variable names and type
//if (new TVariant(value).TypeVariant == eVariantTypes.eString)
if (!ValueIsNumber || ValueIsText)
{
ReturnValue.Add(new TVariant(StringHelper.GetNextCSV(ref listText).Trim() + " = '"));
ReturnValue.Add(new TVariant(value));
ReturnValue.Add(new TVariant("' "));
}
else
{
ReturnValue.Add(new TVariant(StringHelper.GetNextCSV(ref listText).Trim() + " = "));
ReturnValue.Add(new TVariant(value));
ReturnValue.Add(new TVariant(' '));
}
}
ReturnValue.Add(new TVariant(')'));
}
else
{
// if the format is defined in the xml file, it overwrites the value we get from (new TVariant(value).TypeVariant == eVariantTypes.xxx
bool ValueIsNumber = (rptValue.strFormat == "Number");
bool ValueIsText = (rptValue.strFormat == "Text");
if ((ValueIsText || (new TVariant(value).TypeVariant == eVariantTypes.eString))
&& !ValueIsNumber)
{
ReturnValue.Add(new TVariant(' ' + rptValue.strText + " = '"));
ReturnValue.Add(new TVariant(value));
ReturnValue.Add(new TVariant("' "));
}
else
{
ReturnValue.Add(new TVariant(' ' + rptValue.strText + " = "));
ReturnValue.Add(new TVariant(value));
ReturnValue.Add(new TVariant(' '));
}
}
}
ReturnValue.Add(new TVariant(") "));
}
else
{
if (rptValue.strText.Length != 0)
{
ReturnValue.Add(new TVariant(rptValue.strText), rptValue.strFormat);
}
if (rptValue.strVariable.Length != 0)
{
ReturnValue.Add(Parameters.Get(rptValue.strVariable, column, Depth), rptValue.strFormat);
}
if (rptValue.strCalculation.Length != 0)
{
rptDataCalcCalculation = new TRptDataCalcCalculation(this);
ReturnValue.Add(rptDataCalcCalculation.EvaluateHelperCalculation(rptValue.strCalculation), rptValue.strFormat);
}
if (rptValue.strFunction.Length != 0)
{
ReturnValue.Add(EvaluateFunctionString(rptValue.strFunction), rptValue.strFormat);
}
}
}
}
return ReturnValue;
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="rptField"></param>
public void Calculate(TRptField rptField)
{
int cmpos;
string whichField;
TRptDataCalcCalculation rptDataCalcCalculation;
String list;
String element;
column = -1;
whichField = rptField.strWhichfield;
if (whichField.IndexOf("columns") == 0)
{
column = ReportingConsts.ALLCOLUMNS;
}
else if (whichField.IndexOf("column ") == 0)
{
if (whichField[7] == '{')
{
String ParameterName = whichField.Substring(8, whichField.Length - 9);
column = GetParameters().Get(ParameterName).ToInt();
}
else
{
column = (Int32)StringHelper.StrToInt(whichField.Substring(7, whichField.Length - 7));
}
}
else if (whichField.IndexOf("left ") == 0)
{
column = ReportingConsts.COLUMNLEFT + (Int32)StringHelper.StrToInt(whichField.Substring(5, whichField.Length - 5)) + 1;
}
else if (whichField.IndexOf("header ") == 0)
{
column = ReportingConsts.HEADERCOLUMN + (Int32)StringHelper.StrToInt(whichField.Substring(7, whichField.Length - 7)) + 1;
}
else
{
if (whichField == "title1")
{
column = ReportingConsts.HEADERTITLE1;
}
if (whichField == "title2")
{
column = ReportingConsts.HEADERTITLE2;
}
if (whichField == "descr1")
{
column = ReportingConsts.HEADERDESCR1;
}
if (whichField == "descr2")
{
column = ReportingConsts.HEADERDESCR2;
}
if (whichField == "descr3")
{
column = ReportingConsts.HEADERDESCR3;
}
if (whichField == "period1")
{
column = ReportingConsts.HEADERPERIOD;
}
if (whichField == "period2")
{
column = ReportingConsts.HEADERPERIOD2;
}
if (whichField == "period3")
{
column = ReportingConsts.HEADERPERIOD3;
}
if (whichField == "left1")
{
column = ReportingConsts.HEADERPAGELEFT1;
}
if (whichField == "left2")
{
GetParameters().Add("FOOTER2LINES", new TVariant(true), -1, Depth);
column = ReportingConsts.HEADERPAGELEFT2;
}
if (whichField == "type")
{
column = ReportingConsts.HEADERTYPE;
}
}
if (rptField.strAlign != "")
{
GetParameters().Add("ColumnAlign", new TVariant(rptField.strAlign), column, Depth);
}
if (rptField.strFormat.Length != 0)
{
GetParameters().Add("ColumnFormat", new TVariant(rptField.strFormat), column, Depth);
}
if (rptField.strLine.IndexOf("above") != -1)
{
GetParameters().Add("LineAbove", new TVariant(true), column, Depth);
}
if (rptField.strLine.IndexOf("below") != -1)
{
GetParameters().Add("LineBelow", new TVariant(true), column, Depth);
}
if (rptField.strPos.IndexOf("indented") != -1)
{
GetParameters().Add("indented", new TVariant(true), column, Depth);
}
cmpos = rptField.strPos.IndexOf("cm");
if (cmpos != -1)
{
GetParameters().Add("ColumnPosition", new TVariant(StringHelper.TryStrToDecimal(rptField.strPos.Substring(0,
cmpos), 0.0M)), column, Depth);
}
cmpos = rptField.strWidth.IndexOf("cm");
if (cmpos != -1)
{
GetParameters().Add("ColumnWidth", new TVariant(StringHelper.TryStrToDecimal(rptField.strWidth.Substring(0,
cmpos), 0.0M)), column, Depth);
}
if (rptField.strCalculation.Length != 0)
{
rptDataCalcCalculation = new TRptDataCalcCalculation(this);
// allow several helper calculations to be executed after each other;
// e.g. giftbatchexport; first get the accumulated description of the gift details, then get bank account details
list = rptField.strCalculation;
while (list.Length != 0)
{
element = StringHelper.GetNextCSV(ref list).Trim();
rptDataCalcCalculation.EvaluateHelperCalculation(element);
}
}
if (column != -1)
{
if (rptField.rptGrpValue != null)
{
// overwrite the global column calculation
GetParameters().Add("param_calculation", new TVariant(), column, Depth, null, null, ReportingConsts.CALCULATIONPARAMETERS);
}
Calculate(rptField.rptGrpValue, rptField.rptGrpFieldDetail);
}
else
{
throw new Exception("ERROR: whichField problem: " + whichField);
}
}
/// <summary>
/// calculate the next level deeper
/// </summary>
/// <param name="rptLowerLevel"></param>
/// <param name="masterRow"></param>
public void Calculate(TRptLowerLevel rptLowerLevel, int masterRow)
{
TRptDataCalcLevel rptDataCalcLevel;
TRptCalculation rptCalculation;
TRptDataCalcCalculation rptDataCalcCalculation;
TRptDataCalcParameter rptDataCalcParameter;
if (!EvaluateCondition(rptLowerLevel.strCondition))
{
return;
}
if (rptLowerLevel.rptGrpParameter != null)
{
rptDataCalcParameter = new TRptDataCalcParameter(this);
rptDataCalcParameter.Calculate("", rptLowerLevel.rptGrpParameter);
}
if (rptLowerLevel.strCalculation.Length == 0)
{
rptDataCalcLevel = new TRptDataCalcLevel(this);
rptDataCalcLevel.Depth++;
rptDataCalcLevel.Calculate(CurrentReport.GetLevel(rptLowerLevel.strLevel), masterRow);
}
else
{
rptCalculation = ReportStore.GetCalculation(CurrentReport, rptLowerLevel.strCalculation);
if (rptCalculation == null)
{
TLogging.Log("calculation not found:" + rptLowerLevel.strCalculation);
return;
}
rptDataCalcCalculation = new TRptDataCalcCalculation(this);
rptDataCalcCalculation.EvaluateCalculation(rptCalculation, rptLowerLevel.rptGrpParameter, rptLowerLevel.strLevel, masterRow);
}
}
/// <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;
}
