/// <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>
/// 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;
System.Object CountryCode = String.Empty;
ReturnValue = false;
List <OdbcParameter> odbcparameters = new List <OdbcParameter>();
odbcparameters.Add(new OdbcParameter("partnerKey", OdbcType.BigInt)
{
Value = APartnerKey
});
odbcparameters.Add(new OdbcParameter("startOfCommitment", OdbcType.DateTime)
{
Value = AGivenDate
});
odbcparameters.Add(new OdbcParameter("endOfCommitment", OdbcType.DateTime)
{
Value = AGivenDate
});
strSql = "SELECT pm_start_of_commitment_d, pm_end_of_commitment_d, p_country_code_c " +
"FROM PUB_pm_staff_data, PUB_p_unit " +
"WHERE PUB_pm_staff_data.pm_receiving_field_n = PUB_p_unit.p_partner_key_n " +
"AND PUB_pm_staff_data.p_partner_key_n = ? AND pm_start_of_commitment_d <= ? " +
"AND (pm_end_of_commitment_d >= ? OR pm_end_of_commitment_d IS NULL) " +
"ORDER BY pm_start_of_commitment_d ASC";
formatQuery = new TRptFormatQuery(strSql, odbcparameters, null, -1, -1);
formatQuery.ReplaceVariables();
tab = situation.GetDatabaseConnection().SelectDT(formatQuery.SQLStmt, "table", situation.GetDatabaseConnection().Transaction, formatQuery.OdbcParameters.ToArray());
formatQuery = null;
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"];
CountryCode = tab.Rows[tab.Rows.Count - 1]["p_country_code_c"];
}
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, p_country_code_c " +
"FROM PUB_pm_staff_data " +
"LEFT JOIN PUB_p_unit ON PUB_pm_staff_data.pm_receiving_field_n = PUB_p_unit.p_partner_key_n " +
"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"];
CountryCode = tab.Rows[tab.Rows.Count - 1]["p_country_code_c"];
}
}
if (!ReturnValue)
{
situation.GetParameters().RemoveVariable("CommitmentStart", -1, -1, eParameterFit.eExact);
situation.GetParameters().RemoveVariable("CommitmentEnd", -1, -1, eParameterFit.eExact);
situation.GetParameters().RemoveVariable("CountryOfService", -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);
situation.GetParameters().Add("CountryOfService", new TVariant(CountryCode), -1, -1, null, null, ReportingConsts.CALCULATIONPARAMETERS);
}
return(ReturnValue);
}
