/// <summary>
/// Replace Variables
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
protected TRptFormatQuery ReplaceVariables(String s)
{
TRptFormatQuery formatQuery = new TRptFormatQuery(s, null, Parameters, column, Depth);
formatQuery.ReplaceVariables();
return(formatQuery);
}
private void ReplaceFunctionVariables(ref TVariant v)
{
String s;
TVariant testS;
TVariant[] ops = new TVariant[ReportingConsts.MAX_FUNCTION_PARAMETER + 1 - 0 + 1];
Int32 counter;
if (v.TypeVariant == eVariantTypes.eString)
{
s = v.ToString().Trim();
s = s.Replace("{{lineId}}", StringHelper.IntToStr(LineId));
s = s.Replace("{{column}}", StringHelper.IntToStr(column));
s = s.Replace("{{level}}", StringHelper.IntToStr(Depth));
// make sure that e.g. HasChildRows is evaluated
for (counter = 1; counter <= ReportingConsts.MAX_FUNCTION_PARAMETER; counter += 1)
{
ops[counter] = null;
}
testS = FunctionSelector(s, ops);
if (testS != null)
{
s = testS.ToString();
}
TRptFormatQuery query = new TRptFormatQuery(s, null, Parameters, column, Depth);
query.ReplaceVariables();
v = query.VariantValue;
}
}
/// <summary>
/// combine two sql statements with parameters
/// </summary>
public void Add(TRptFormatQuery AQueryToAdd)
{
if (AQueryToAdd.IsVariant)
{
if (this.IsVariant)
{
this.Add(AQueryToAdd.VariantValue);
}
else
{
this.Add(AQueryToAdd.VariantValue.ToString());
}
}
else
{
if (this.IsVariant)
{
this.FSQLStmt = VariantValue.ToString();
}
this.FSQLStmt += AQueryToAdd.SQLStmt;
}
foreach (OdbcParameter p in AQueryToAdd.FOdbcParameters)
{
this.FOdbcParameters.Add(p);
}
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="s"></param>
/// <param name="withQuotes"></param>
/// <returns></returns>
protected TVariant ReplaceVariables(String s, Boolean withQuotes)
{
TVariant ReturnValue;
TRptFormatQuery formatQuery;
formatQuery = new TRptFormatQuery(Parameters, column, Depth);
ReturnValue = formatQuery.ReplaceVariables(s, withQuotes);
return(ReturnValue);
}
/// <summary>
/// This functions finds the commitment period of a given partner, at a given time
/// The result is stored in the variables CommitmentStart
/// and CommitmentEnd. The end date might be empty, even if the start date is set
///
/// It will find the most recent commitment,
/// that starts on or before the given date and
/// lasts till or beyond the given date (also open ended).
/// If no such commitment exists, the most recent commitment of all will be returned.
///
/// </summary>
/// <returns>s true if a current commitment period was found
/// </returns>
private bool GetCurrentCommitmentPeriod(Int64 APartnerKey, DateTime AGivenDate)
{
bool ReturnValue;
string strSql;
DataTable tab;
TRptFormatQuery formatQuery;
System.Object StartDate = DateTime.MinValue;
System.Object EndDate = DateTime.MinValue;
ReturnValue = false;
strSql = "SELECT pm_start_of_commitment_d, pm_end_of_commitment_d " + "FROM PUB_pm_staff_data " +
"WHERE PUB_pm_staff_data.p_partner_key_n = " + APartnerKey.ToString() + ' ' + "AND pm_start_of_commitment_d <= {#" +
StringHelper.DateToStr(AGivenDate, "dd/MM/yyyy") + "#} " + "AND (pm_end_of_commitment_d >= {#" + StringHelper.DateToStr(
AGivenDate,
"dd/MM/yyyy") + "#} " + " OR pm_end_of_commitment_d IS NULL) " + "ORDER BY pm_start_of_commitment_d ASC";
formatQuery = new TRptFormatQuery(null, -1, -1);
strSql = formatQuery.ReplaceVariables(strSql).ToString();
formatQuery = null;
tab = situation.GetDatabaseConnection().SelectDT(strSql, "table", situation.GetDatabaseConnection().Transaction);
if (tab.Rows.Count > 0)
{
// take the last row, the most recent start date
ReturnValue = true;
StartDate = tab.Rows[tab.Rows.Count - 1]["pm_start_of_commitment_d"];
EndDate = tab.Rows[tab.Rows.Count - 1]["pm_end_of_commitment_d"];
}
else
{
// no commitment period for the given date was found, so find the most recent commitment
strSql = "SELECT pm_start_of_commitment_d, pm_end_of_commitment_d " + "FROM PUB_pm_staff_data " +
"WHERE PUB_pm_staff_data.p_partner_key_n = " + APartnerKey.ToString() + ' ' + "ORDER BY pm_start_of_commitment_d ASC";
tab = situation.GetDatabaseConnection().SelectDT(strSql, "table", situation.GetDatabaseConnection().Transaction);
if (tab.Rows.Count > 0)
{
// take the last row, the most recent start date
ReturnValue = true;
StartDate = tab.Rows[tab.Rows.Count - 1]["pm_start_of_commitment_d"];
EndDate = tab.Rows[tab.Rows.Count - 1]["pm_end_of_commitment_d"];
}
}
if (!ReturnValue)
{
situation.GetParameters().RemoveVariable("CommitmentStart", -1, -1, eParameterFit.eExact);
situation.GetParameters().RemoveVariable("CommitmentEnd", -1, -1, eParameterFit.eExact);
}
else
{
situation.GetParameters().Add("CommitmentStart", new TVariant(StartDate), -1, -1, null, null, ReportingConsts.CALCULATIONPARAMETERS);
situation.GetParameters().Add("CommitmentEnd", new TVariant(EndDate), -1, -1, null, null, ReportingConsts.CALCULATIONPARAMETERS);
}
return ReturnValue;
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="s"></param>
/// <param name="withQuotes"></param>
/// <returns></returns>
protected TVariant ReplaceVariables(String s, Boolean withQuotes)
{
TVariant ReturnValue;
TRptFormatQuery formatQuery;
formatQuery = new TRptFormatQuery(Parameters, column, Depth);
ReturnValue = formatQuery.ReplaceVariables(s, withQuotes);
return ReturnValue;
}