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>
/// 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;
}