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