context: //
  // DO NOT REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  //
  // @Authors:
  //       timop
  //
  // Copyright 2004-2013 by OM International
  //
  // This file is part of OpenPetra.org.
  //
  // OpenPetra.org is free software: you can redistribute it and/or modify
  // it under the terms of the GNU General Public License as published by
  // the Free Software Foundation, either version 3 of the License, or
  // (at your option) any later version.
  //
  // OpenPetra.org is distributed in the hope that it will be useful,
  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  // GNU General Public License for more details.
  //
  // You should have received a copy of the GNU General Public License
  // along with OpenPetra.org.  If not, see <http://www.gnu.org/licenses/>.
  //
  using System;
  
  
  using Ict.Common;
  using Ict.Common.Conversion;
  using Ict.Common.Data; // Implicit reference
  using Ict.Common.DB;
  using Ict.Common.IO; // Implicit reference
  using Ict.Common.Verification;
  using Ict.Petra.Server.MPartner.Mailroom.Data.Access;
  using Ict.Petra.Server.MPartner.Partner;
  using Ict.Petra.Server.MPartner.Partner.Data.Access;
  using Ict.Petra.Server.MPartner.Partner.Cacheable;
  using Ict.Petra.Server.MReporting;
  using Ict.Petra.Shared;
  using Ict.Petra.Shared.MFinance;
  using Ict.Petra.Shared.MPartner;
  using Ict.Petra.Shared.MPartner.Mailroom.Data;
  using Ict.Petra.Shared.MPartner.Partner.Data;
  using Ict.Petra.Shared.MReporting;
  using System.Collections;
  using System.Collections.Generic;
  using System.Collections.Specialized;
  using System.Data.Odbc;
  using System.Data;
  using System.Diagnostics;
  using Ict.Petra.Server.MPartner.Partner.UIConnectors;
  
  namespace Ict.Petra.Server.MReporting.MPartner
  {
      /// <summary>
      /// user defined functions for partner module
      /// </summary>
      public class TRptUserFunctionsPartner : TRptUserFunctions
      {
          #region members for publication statistical report
  
          /// <summary> Holds the results of the calculation of publication statistical report </summary>
          private static DataTable FStatisticalReportDataTable;
  
          /// <summary> Holds the result for the row "Percent:" of the publication statistical report </summary>
          private static Dictionary <String, String>FStatisticalReportPercentage;
  
          /// <summary> Holds the number of active partner. This is needed for calculation of the
          /// publication statistical report. </summary>
          private static int FNumberOfAcitvePartner;
  
          /// <summary> These constants define special rows for the publication statistical report </summary>
          private const String ROW_FOREIGN = "*FOREIGN*";
          private const String ROW_NONE = "*NONE*";
          private const String ROW_PERCENT = "Percent:";
          private const String ROW_TOTAL = "Totals:";
          private const String ROW_COUNT = "Counts:";
          private const String COLUMN_CHURCH = "Churches";
          private const String COLUMN_DONOR = "Donors";
          private const String COLUMN_APPLICANTS = "Applicants";
          private const String COLUMN_EXPARTICIPANTS = "ExParticipants";
          #endregion
  
          /// <summary>
          /// constructor
          /// </summary>
          public TRptUserFunctionsPartner() : base()
          {
          }
  
          /// <summary>
          /// functions need to be registered here
          /// </summary>
          /// <param name="ASituation"></param>
          /// <param name="f"></param>
          /// <param name="ops"></param>
          /// <param name="value"></param>
          /// <returns></returns>
          public override Boolean FunctionSelector(TRptSituation ASituation, String f, TVariant[] ops, out TVariant value)
          {
              if (base.FunctionSelector(ASituation, f, ops, out value))
              {
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetPartnerLabelValues"))
              {
                  value = new TVariant(GetPartnerLabelValues());
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetPartnerBestAddress"))
              {
                  value = new TVariant(GetPartnerBestAddress(ops[1].ToInt64()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "AddressMeetsPostCodeCriteriaOrEmpty"))
              {
                  value =
                      new TVariant(AddressMeetsPostCodeCriteriaOrEmpty(ops[1].ToBool(), ops[2].ToString(), ops[3].ToString(), ops[4].ToString(),
                              ops[5].ToString(), ops[6].ToString()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetPartnerShortname"))
              {
                  value = new TVariant(GetPartnerShortName(ops[1].ToInt64()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetFieldOfPartner"))
              {
                  value = new TVariant(GetFieldOfPartner(ops[1].ToInt64()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "CheckAccountNumber"))
              {
                  value = new TVariant(CheckAccountNumber(ops[1].ToString(), ops[2].ToString(), ops[3].ToString()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetCountyPublicationStatistic"))
              {
                  value = new TVariant(GetCountyPublicationStatistic(ops[1].ToString(), ops[2].ToString()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "CalculatePublicationStatisticPercentage"))
              {
                  value = new TVariant(FillStatisticalReportResultTable());
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetNumberOfAllPublications"))
              {
                  value = new TVariant(GetNumberOfAllPublications());
                  return true;
              }
  
              if (StringHelper.IsSame(f, "ConvertIntToTime"))
              {
                  value = new TVariant(ConvertIntToTime(ops[1].ToInt(), ops[2].ToInt()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "DetermineAddressDateStatus"))
              {
                  value = new TVariant(DetermineAddressDateStatus(ops[1].ToString(), ops[2].ToString()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetSubscriptions"))
              {
                  value = new TVariant(GetSubscriptions(ops[1].ToInt64(), ops[2].ToString()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetFirstEntryFromSQLStatement"))
              {
                  value = new TVariant(GetFirstEntryFromSQLStatement(ops[1].ToString(), ops[2].ToString(), ops[3].ToString()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetPartnerTypes"))
              {
                  value = new TVariant(GetPartnerTypes(ops[1].ToInt64()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetProfession"))
              {
                  value = new TVariant(GetProfession(ops[1].ToInt64()));
                  return true;
              }
  
              if (StringHelper.IsSame(f, "GetOccupation"))
              {
                  value = new TVariant(GetOccupation(ops[1].ToString()));
                  return true;
              }
  
              value = new TVariant();
              return false;
          }
  
          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>
          /// Determine the status of an address given the "date effective from" and
          /// "date good unil" dates.
          /// The format of the date is dd/mm/yyyy
          /// </summary>
          /// <param name="DateEffectiveString">Date from when the address is valid</param>
          /// <param name="DateGoodUntilString">Date until the address is valid</param>
          /// <returns> -1 if input String is not a date
          ///             1 if address is current
          ///             2 if address is future
          ///             3 if address is expiered
          ///  </returns>
          private int DetermineAddressDateStatus(String DateEffectiveString, String DateGoodUntilString)
          {
              DateTime DateEffective;
              DateTime DateGoodUntil;
              TVerificationResult VerificationResult = null;
  
              if (DateEffectiveString.Length == 0)
              {
                  DateEffective = new DateTime(1, 1, 1);
              }
              else
              {
                  DateEffective = TDate.LongDateStringToDateTime2(DateEffectiveString, "",
                      out VerificationResult, false);
              }
  
              if (VerificationResult != null)
              {
                  return -1;
              }
  
              if (DateGoodUntilString.Length == 0)
              {
                  DateGoodUntil = new DateTime(2999, 12, 31);
              }
              else
              {
                  DateGoodUntil = TDate.LongDateStringToDateTime2(DateGoodUntilString, "",
                      out VerificationResult, false);
              }
  
              if (VerificationResult != null)
              {
                  return -1;
              }
  
              DataSet DS = new DataSet();
              DS.Tables.Add(new PPartnerLocationTable());
              PPartnerLocationTable TempTable = (PPartnerLocationTable)DS.Tables[PPartnerLocationTable.GetTableName()];
  
              TempTable.Columns.Add(new System.Data.DataColumn("BestAddress", typeof(Boolean)));
              TempTable.Columns.Add(new System.Data.DataColumn("Icon", typeof(Int32)));
  
              DataRow row = TempTable.NewRow();
              row[PPartnerLocationTable.GetDateGoodUntilDBName()] = DateGoodUntil;
              row[PPartnerLocationTable.GetDateEffectiveDBName()] = DateEffective;
              // set the values that must not be null
              row[PPartnerLocationTable.GetPartnerKeyDBName()] = 0;
              row[PPartnerLocationTable.GetSiteKeyDBName()] = 0;
              row[PPartnerLocationTable.GetLocationKeyDBName()] = 0;
  
              TempTable.Rows.Add(row);
  
              Calculations.DeterminePartnerLocationsDateStatus(DS);
  
              int Status = (Int32)TempTable.Rows[0]["Icon"];
              return Status;
          }
  
          /// <summary>
          /// Get all subscriptions with a defined status of a Partner.
          /// </summary>
          /// <param name="APartnerKey">Partner key</param>
          /// <param name="AStatusString">List of statuses for which to get the subscriptions.</param>
          /// <returns>List of subscriptions</returns>
          private String GetSubscriptions(Int64 APartnerKey, String AStatusString)
          {
              String ReturnValue = "";
              PSubscriptionTable table;
              StringCollection fields;
  
              fields = new StringCollection();
              fields.Add(PSubscriptionTable.GetPublicationCodeDBName());
              fields.Add(PSubscriptionTable.GetSubscriptionStatusDBName());
  
              table = (PSubscriptionTable)PSubscriptionAccess.LoadViaPPartnerPartnerKey(APartnerKey, fields,
                  situation.GetDatabaseConnection().Transaction);
  
              foreach (DataRow dataRow in table.Rows)
              {
                  PSubscriptionRow SubscriptionRow = (PSubscriptionRow)dataRow;
  
                  if ((!SubscriptionRow.IsSubscriptionStatusNull())
                      && (AStatusString.Contains(SubscriptionRow.SubscriptionStatus)))
                  {
                      ReturnValue = ReturnValue + "  " + SubscriptionRow.PublicationCode;
                  }
              }
  
              if (ReturnValue.Length == 0)
              {
                  ReturnValue = "No Subscriptions";
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// Returns the first row of 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="AReplaceString">String in the sql command that should be replaced</param>
          /// <param name="AReplacement">the replacement</param>
          /// <returns>true if successful, otherwise false</returns>
          private bool GetFirstEntryFromSQLStatement(String ASqlID, String AReplaceString, String AReplacement)
          {
              DataTable ResultTable;
              bool ReturnValue = false;
  
              if (!GetDataTableFromXmlSqlStatement(ASqlID, out ResultTable, AReplaceString, AReplacement))
              {
                  return false;
              }
  
              foreach (DataColumn col in ResultTable.Columns)
              {
                  // remove the old value
                  situation.GetParameters().RemoveVariable(col.ColumnName);
              }
  
              if (ResultTable.Rows.Count > 0)
              {
                  DataRow row = ResultTable.Rows[0];
  
                  foreach (DataColumn col in ResultTable.Columns)
                  {
                      // add the new value
                      situation.GetParameters().Add(col.ColumnName, new TVariant(row[col.ColumnName]));
                  }
  
                  ReturnValue = true;
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// this will find the best p_partner_location, and export the values to parameters (p_street_name_c, etc.)
          /// </summary>
          /// <returns>void</returns>
          private bool GetPartnerBestAddress(Int64 APartnerKey)
          {
              bool ReturnValue = false;
  
              // reset the variables
              PLocationTable LocationTable = new PLocationTable();
  
              foreach (DataColumn col in LocationTable.Columns)
              {
                  situation.GetParameters().RemoveVariable(StringHelper.UpperCamelCase(col.ColumnName, true, true));
              }
  
              DataTable PartnerLocationTable = new PPartnerLocationTable();
  
              foreach (DataColumn col in PartnerLocationTable.Columns)
              {
                  // do not clear the partner key, in case we do not have an address for the partner
                  if (col.ColumnName != PPartnerLocationTable.GetPartnerKeyDBName())
                  {
                      situation.GetParameters().RemoveVariable(StringHelper.UpperCamelCase(col.ColumnName, true, true));
                  }
              }
  
              situation.GetParameters().RemoveVariable("FirstName");
              situation.GetParameters().RemoveVariable("FamilyName");
  
              StringCollection NameColumnNames = new StringCollection();
              NameColumnNames.Add(PPartnerTable.GetPartnerShortNameDBName());
              PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, NameColumnNames,
                  situation.GetDatabaseConnection().Transaction);
              string PartnerShortName = PartnerTable.Rows[0][PPartnerTable.GetPartnerShortNameDBName()].ToString();
  
              situation.GetParameters().AddCalculationParameter("NameWithTitle",
                  new TVariant(Ict.Petra.Shared.MPartner.Calculations.FormatShortName(PartnerShortName, eShortNameFormat.eReverseShortname)));
  
              DataSet PartnerLocationsDS = new DataSet();
              PartnerLocationsDS.Tables.Add(new PPartnerLocationTable());
              PartnerLocationTable = PartnerLocationsDS.Tables[PPartnerLocationTable.GetTableName()];
  
              // add special column BestAddress and Icon
              PartnerLocationTable.Columns.Add(new System.Data.DataColumn("BestAddress", typeof(Boolean)));
              PartnerLocationTable.Columns.Add(new System.Data.DataColumn("Icon", typeof(Int32)));
  
              // find all locations of the partner, put it into a dataset
              PPartnerLocationAccess.LoadViaPPartner(PartnerLocationsDS, APartnerKey, situation.GetDatabaseConnection().Transaction);
  
              // uses Ict.Petra.Shared.MPartner.Calculations.pas, DetermineBestAddress
              Calculations.DeterminePartnerLocationsDateStatus(PartnerLocationsDS);
              Calculations.DetermineBestAddress(PartnerLocationsDS);
  
              foreach (PPartnerLocationRow row in PartnerLocationTable.Rows)
              {
                  // find the row with BestAddress = 1
                  if (Convert.ToInt32(row["BestAddress"]) == 1)
                  {
                      // find the location record with that address
                      LocationTable = PLocationAccess.LoadByPrimaryKey(row.SiteKey, row.LocationKey, situation.GetDatabaseConnection().Transaction);
  
                      // put the found values in the parameters
                      if (LocationTable.Rows.Count > 0)
                      {
                          // get the location details into the parameters
                          foreach (DataColumn col in LocationTable.Columns)
                          {
                              situation.GetParameters().AddCalculationParameter(StringHelper.UpperCamelCase(col.ColumnName, true,
                                      true), new TVariant(LocationTable.Rows[0][col.ColumnName]));
                          }
  
                          // also put the phone number and email etc into the parameters
                          foreach (DataColumn col in PartnerLocationTable.Columns)
                          {
                              situation.GetParameters().AddCalculationParameter(StringHelper.UpperCamelCase(col.ColumnName, true,
                                      true), new TVariant(PartnerLocationTable.Rows[0][col.ColumnName]));
                          }
  
                          // get the Partner Firstname and Surname as well; depends on the partner class
                          // first try person
                          NameColumnNames = new StringCollection();
                          NameColumnNames.Add(PPersonTable.GetFirstNameDBName());
                          NameColumnNames.Add(PPersonTable.GetFamilyNameDBName());
                          PPersonTable PersonTable = PPersonAccess.LoadByPrimaryKey(APartnerKey, NameColumnNames,
                              situation.GetDatabaseConnection().Transaction);
  
                          if (PersonTable.Rows.Count > 0)
                          {
                              situation.GetParameters().AddCalculationParameter("FirstName",
                                  new TVariant(PersonTable.Rows[0][PPersonTable.GetFirstNameDBName()]));
                              situation.GetParameters().AddCalculationParameter("FamilyName",
                                  new TVariant(PersonTable.Rows[0][PPersonTable.GetFamilyNameDBName()]));
                          }
                          else
                          {
                              // then it was a family?
                              NameColumnNames = new StringCollection();
                              NameColumnNames.Add(PFamilyTable.GetFirstNameDBName());
                              NameColumnNames.Add(PFamilyTable.GetFamilyNameDBName());
                              PFamilyTable FamilyTable = PFamilyAccess.LoadByPrimaryKey(APartnerKey, NameColumnNames,
                                  situation.GetDatabaseConnection().Transaction);
  
                              if (FamilyTable.Rows.Count > 0)
                              {
                                  situation.GetParameters().AddCalculationParameter("FirstName",
                                      new TVariant(FamilyTable.Rows[0][PFamilyTable.GetFirstNameDBName()]));
                                  situation.GetParameters().AddCalculationParameter("FamilyName",
                                      new TVariant(FamilyTable.Rows[0][PFamilyTable.GetFamilyNameDBName()]));
                              }
                              else
                              {
                                  // it was an organisation or church, just use the shortname
                                  situation.GetParameters().RemoveVariable("FirstName");
                                  situation.GetParameters().RemoveVariable("FamilyName");
  
                                  if (PartnerTable.Rows.Count > 0)
                                  {
                                      situation.GetParameters().AddCalculationParameter("FamilyName",
                                          new TVariant(PartnerShortName));
                                  }
                              }
                          }
  
                          ReturnValue = true;
                      }
                  }
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// this will return true if the best address is in the given postal region, postcode range and county it will not reset the data returned by best address
          /// </summary>
          /// <returns>void</returns>
          private bool AddressMeetsPostCodeCriteriaOrEmpty(bool ABestAddressWasFound,
              String ARadioSelection,
              String APostalRegion,
              String APostCodeFrom,
              String APostCodeTo,
              String ACounty)
          {
              bool ReturnValue = false;
              Boolean NewTransaction;
              String postalCode;
  
              if (ARadioSelection != "DonorsCounty")
              {
                  ACounty = "";
              }
  
              if (ARadioSelection != "DonorsPostalRegion")
              {
                  APostalRegion = "";
              }
  
              if (ARadioSelection != "DonorsPostcode")
              {
                  APostCodeFrom = "";
                  APostCodeTo = "";
              }
  
              if (!ABestAddressWasFound)
              {
                  // only return true if the postcode parameters and county parameters are empty
                  return (APostalRegion.Length == 0) && (APostCodeFrom.Length == 0) && (APostCodeTo.Length == 0) && (ACounty.Length == 0);
              }
  
              if (APostalRegion.Length > 0)
              {
                  postalCode = situation.GetParameters().Get("PostalCode").ToString();
                  DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
                  try
                  {
  // todo, wrong combination of table/columns
  //          TmpTable = DBAccess.GDBAccessObj.SelectDT("SELECT 1 " + " FROM PUB." + PPostcodeRangeTable.GetTableDBName() + " AS p " + " WHERE p.p_region_c = '" + APostalRegion + "' " + " AND p.p_from_c <= '" + postalCode + "' AND p.p_to_c >= '" +
  // postalCode
  // + "'", "temp", ReadTransaction);
  //          if (TmpTable.Rows.Count > 0)
                      {
                          ReturnValue = true;
                      }
                  }
                  finally
                  {
                      if (NewTransaction)
                      {
                          DBAccess.GDBAccessObj.CommitTransaction();
                      }
                  }
              }
              else if ((APostCodeFrom.Length > 0) && (APostCodeTo.Length > 0))
              {
                  postalCode = situation.GetParameters().Get("PostalCode").ToString();
  
                  if ((String.CompareOrdinal(APostCodeFrom.ToLower(),
                           postalCode.ToLower()) <= 0) && (String.CompareOrdinal(APostCodeTo.ToLower(), postalCode.ToLower()) >= 0))
                  {
                      ReturnValue = true;
                  }
              }
              else if (ACounty.Length > 0)
              {
                  if (ACounty.ToLower() == situation.GetParameters().Get("County").ToString().ToLower())
                  {
                      ReturnValue = true;
                  }
              }
  
              return ReturnValue;
          }
  
          private String GetPartnerShortName(Int64 APartnerKey)
          {
              String ReturnValue;
              PPartnerTable table;
              StringCollection fields;
  
              ReturnValue = "N/A";
              fields = new StringCollection();
              fields.Add(PPartnerTable.GetPartnerShortNameDBName());
              table = PPartnerAccess.LoadByPrimaryKey(APartnerKey, fields, situation.GetDatabaseConnection().Transaction);
  
              if (table.Rows.Count > 0)
              {
                  ReturnValue = table[0].PartnerShortName;
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// Get the field name of one partner
          /// </summary>
          /// <param name="APartnerKey">partnerkey</param>
          /// <returns>The field name if it was found. Otherwise empty string</returns>
          private String GetFieldOfPartner(Int64 APartnerKey)
          {
              string FieldName = "";
  
              PPartnerGiftDestinationTable ResultTable = PPartnerGiftDestinationAccess.LoadViaPPartner(APartnerKey,
                  situation.GetDatabaseConnection().Transaction);
  
              foreach (PPartnerGiftDestinationRow Row in ResultTable.Rows)
              {
                  if (Row.Active)
                  {
                      FieldName = GetPartnerShortName(Row.FieldKey);
                      break;
                  }
              }
  
              //
              // If there was no result and the partner given is a family, I can see about the field for the family member with id=0.
              if (FieldName == "")
              {
                  PPartnerTable tbl = PPartnerAccess.LoadByPrimaryKey(APartnerKey, null);
  
                  if ((tbl.Rows.Count > 0) && (tbl[0].PartnerClass == TPartnerClass.FAMILY.ToString()))
                  {
                      PPersonTable familyMembers = PPersonAccess.LoadViaPFamily(APartnerKey, null);
                      familyMembers.DefaultView.RowFilter = "p_family_id_i = 0";
  
                      if (familyMembers.DefaultView.Count > 0)
                      {
                          FieldName = GetFieldOfPartner(((PPersonRow)familyMembers.DefaultView[0].Row).PartnerKey);
                      }
                  }
              }
  
              return FieldName;
          }
  
          /// <summary>
          /// Checks the validity of a bank account number.
          /// This function checks the validity of a bank account number by performing a
          /// country-specific check on the submitted account number.
          /// If a bank partner key is submitted, this function is looking
          /// up the bank's location country - this is convenient since the calling procedure
          /// does not need to know the bank's country in this case.
          /// </summary>
          /// <param name="AAccountNumber">Account number</param>
          /// <param name="ABankCountryCode">Country code of the bank
          /// (optional - needs only to be specified if bank_partner_key_n is not submitted)</param>
          /// <param name="ABankPartnerKey">Parner key of the bank
          /// (optional - needs only to be specified if bank_country_code_c is not submitted)</param>
          /// <returns> -1 = length check failed.
          ///            0 = invalid account number
          ///            1 = valid account number
          ///            2 = probably valid - account number cannot be validated by country-specific check
          ///            3 = account number could not be validated - no country-specific check implemented
          ///            4 = Bank partner could not be found
          /// </returns>
          private int CheckAccountNumber(String AAccountNumber, String ABankCountryCode, String ABankPartnerKey)
          {
              int ReturnValue;
              long PartnerKey = -1;
  
              ReturnValue = 1;
  
              PLocationTable LocationTable;
              PPartnerLocationRow PartnerLocationRow;
  
  
gt:             if (ABankPartnerKey.Length > 0)