context: //
  // DO NOT REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  //
  // @Authors:
  //       wolfgangb
  //
  // Copyright 2004-2011 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 System.Collections.Generic;
  using System.Data;
  using System.Data.Odbc;
  using Ict.Common;
  using Ict.Common.DB;
  using Ict.Common.Data;
  using Ict.Common.Verification;
  using Ict.Petra.Shared.MReporting;
  using Ict.Petra.Server.MPartner.Extracts;
  
  namespace Ict.Petra.Server.MCommon.queries
  {
      /// <summary>
      /// base class for extract queries on server side
      /// </summary>
      public abstract class ExtractQueryBase
      {
          /// <summary>
          /// Extracts who need special treatment need to set this to true. It is needed if there is more
          /// than one query involved in running the extract.
          /// </summary>
          protected bool FSpecialTreatment = false;
  
          /// <summary>
          /// calculate an extract from a report: all partners of a given type (or selection of multiple types)
          /// </summary>
          /// <param name="AParameters"></param>
          /// <param name="ASqlStmt"></param>
          /// <param name="AResults"></param>
          /// <returns></returns>
          protected bool CalculateExtractInternal(TParameterList AParameters, string ASqlStmt, TResultList AResults)
          {
              int ExtractId;
  
              return CalculateExtractInternal(AParameters, ASqlStmt, AResults, out ExtractId);
          }
  
          /// <summary>
          /// calculate an extract from a report: all partners of a given type (or selection of multiple types)
          /// </summary>
          /// <param name="AParameters"></param>
          /// <param name="ASqlStmt"></param>
          /// <param name="AResults"></param>
          /// <param name="AExtractId"></param>
          /// <returns></returns>
          protected bool CalculateExtractInternal(TParameterList AParameters, string ASqlStmt, TResultList AResults, out int AExtractId)
          {
              Boolean ReturnValue = false;
              Boolean NewTransaction;
              TDBTransaction Transaction;
  
              List <OdbcParameter>SqlParameterList = new List <OdbcParameter>();
              bool AddressFilterAdded;
  
              AExtractId = -1;
  
              Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
  
              // get the partner keys from the database
              try
              {
                  if (FSpecialTreatment)
                  {
                      ReturnValue = RunSpecialTreatment(AParameters, Transaction, out AExtractId);
                  }
                  else
                  {
                      // call to derived class to retrieve parameters specific for extract
                      RetrieveParameters(AParameters, ref ASqlStmt, ref SqlParameterList);
  
                      // add address filter information to sql statement and parameter list
                      AddressFilterAdded = AddAddressFilter(AParameters, ref ASqlStmt, ref SqlParameterList);
  
                      // now run the database query
                      TLogging.Log("getting the data from the database", TLoggingType.ToStatusBar);
                      DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(ASqlStmt, "partners", Transaction,
                          SqlParameterList.ToArray());
  
                      if (NewTransaction)
                      {
                          DBAccess.GDBAccessObj.RollbackTransaction();
                      }
  
                      // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
                      // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
                      if (AParameters.Get("CancelReportCalculation").ToBool() == true)
                      {
                          return false;
                      }
  
                      TLogging.Log("preparing the extract", TLoggingType.ToStatusBar);
  
                      // create an extract with the given name in the parameters
                      ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
                          AParameters.Get("param_extract_name").ToString(),
                          AParameters.Get("param_extract_description").ToString(),
                          out AExtractId,
                          partnerkeys,
                          0,
                          AddressFilterAdded,
                          true);
                  }
  
                  if (ReturnValue)
                  {
                      DBAccess.GDBAccessObj.CommitTransaction();
                  }
                  else
                  {
                      DBAccess.GDBAccessObj.RollbackTransaction();
                  }
  
                  return ReturnValue;
              }
              catch (Exception Exc)
              {
                  TLogging.Log("An Exception occured in CalculateExtractInternal:" + Environment.NewLine + Exc.ToString());
  
                  if (NewTransaction)
                  {
                      DBAccess.GDBAccessObj.RollbackTransaction();
                  }
  
                  throw;
              }
          }
  
          /// <summary>
          /// extend query statement and query parameter list by address filter information given in extract parameters
          /// </summary>
          /// <param name="AParameters"></param>
          /// <param name="ASqlStmt"></param>
          /// <param name="AOdbcParameterList"></param>
          /// <returns>true if address tables and fields were added</returns>
          protected bool AddAddressFilter(TParameterList AParameters, ref string ASqlStmt,
              ref List <OdbcParameter>AOdbcParameterList)
          {
              string WhereClause = "";
              string TableNames = "";
              string FieldNames = "";
              string OrderByClause = "";
              string StringValue;
              DateTime DateValue;
              string PostCodeFrom = "";
              string PostCodeTo = "";
              bool LocationTableNeeded = false;
              bool PartnerLocationTableNeeded = false;
              bool RegionTableNeeded = false;
              bool AddressFilterAdded = false;
  
              // add check for mailing addresses only
              if (AParameters.Exists("param_mailing_addresses_only"))
              {
                  if (AParameters.Get("param_mailing_addresses_only").ToBool())
                  {
                      WhereClause = WhereClause + " AND pub_p_partner_location.p_send_mail_l";
                      PartnerLocationTableNeeded = true;
                  }
              }
  
              // add city statement (allow any city that begins with search string)
              if (AParameters.Exists("param_city"))
              {
                  StringValue = AParameters.Get("param_city").ToString();
  
                  if ((StringValue.Trim().Length > 0) && (StringValue != "*"))
                  {
                      AOdbcParameterList.Add(new OdbcParameter("param_city", OdbcType.VarChar)
                          {
                              Value = StringValue + "%"
                          });
                      WhereClause = WhereClause + " AND pub_p_location.p_city_c LIKE ?";
                      LocationTableNeeded = true;
                  }
              }
  
              // add county statement (allow any county that begins with search string)
              if (AParameters.Exists("param_county"))
              {
                  StringValue = AParameters.Get("param_county").ToString();
  
  
gt:                 if ((StringValue.Trim().Length > 0) && (StringValue != "*"))