context: //
  // DO NOT REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  //
  // @Authors:
  //       timop
  //       Tim Ingham
  //
  // 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 System.Data;
  using System.Data.Odbc;
  using System.Text;
  using System.Threading;
  using System.Collections;
  using Ict.Petra.Shared;
  using Ict.Common;
  using Ict.Common.DB;
  using Ict.Common.Data;
  using Ict.Common.Verification;
  using Ict.Common.Remoting.Shared;
  using Ict.Common.Remoting.Server;
  using Ict.Petra.Server.MCommon;
  using Ict.Petra.Shared.MCommon;
  using Ict.Petra.Shared.MFinance;
  using Ict.Common.Conversion;
  using Ict.Petra.Shared.MFinance.AP.Data;
  using Ict.Petra.Server.MFinance.AP.Data.Access;
  using Ict.Petra.Shared.MPartner.Partner.Data;
  using Ict.Petra.Shared.Interfaces.MFinance;
  using Ict.Petra.Shared.MFinance.Account.Data;
  using Ict.Petra.Server.MFinance.Account.Data.Access;
  
  namespace Ict.Petra.Server.MFinance.AP.UIConnectors
  {
      ///<summary>
      /// This UIConnector provides data for the finance Accounts Payable screens
      ///
      /// UIConnector Objects are instantiated by the Client's User Interface via the
      /// Instantiator classes.
      /// They handle requests for data retrieval and saving of data (including data
      /// verification).
      ///
      /// Their role is to
      ///   - retrieve (and possibly aggregate) data using Business Objects,
      ///   - put this data into *one* DataSet that is passed to the Client and make
      ///     sure that no unnessary data is transferred to the Client,
      ///   - optionally provide functionality to retrieve additional, different data
      ///     if requested by the Client (for Client screens that load data initially
      ///     as well as later, eg. when a certain tab on the screen is clicked),
      ///   - save data using Business Objects.
      ///
      /// @Comment These Objects would usually not be instantiated by other Server
      ///          Objects, but only by the Client UI via the Instantiator classes.
      ///          However, Server Objects that derive from these objects and that
      ///          are also UIConnectors are feasible.
      ///</summary>
      public class TFindUIConnector : TConfigurableMBRObject, IAPUIConnectorsFind
      {
          /// <summary>Paged query object</summary>
          TPagedDataSet FPagedDataSetObject;
  
          /// <summary>Asynchronous execution control object</summary>
          TAsynchronousExecutionProgress FAsyncExecProgress;
  
          /// <summary>Thread that is used for asynchronously executing the Find query</summary>
          Thread FFindThread;
  
          /// <summary>Returns reference to the Asynchronous execution control object to the caller</summary>
          public IAsynchronousExecutionProgress AsyncExecProgress
          {
              get
              {
                  return (IAsynchronousExecutionProgress)TCreateRemotableObject.CreateRemotableObject(
                      typeof(TAsynchronousExecutionProgressRemote),
                      FAsyncExecProgress);
              }
          }
  
          /// <summary>
          /// default constructor
          /// </summary>
          public TFindUIConnector() : base()
          {
          }
  
          /// <summary>
          /// If this is true, then search behaves a bit differently, with few filters on the server.
          /// Other filters may applied by the user on the client.
          /// </summary>
          private bool FSearchTransactions = false;
  
          /// <summary>if true, then search for supplier; if false, then search for invoice</summary>
          private bool FSearchSupplierOrInvoice = false;
  
          /// <summary>
          /// Find a supplier or a list of suppliers matching the search criteria
          /// </summary>
          /// <param name="ACriteriaData">HashTable containing non-empty Find parameters</param>
          public void FindSupplier(DataTable ACriteriaData)
          {
              FSearchTransactions = false;
              FSearchSupplierOrInvoice = true;
              PerformSearch(ACriteriaData);
          }
  
          /// <summary>
          /// Find a list of invoices matching the search criteria
          /// </summary>
          /// <param name="ACriteriaData">Optional HashTable containing "DaysPlus" for "due by" calculation</param>
          public void FindInvoices(DataTable ACriteriaData)
          {
              FSearchTransactions = false;
              FSearchSupplierOrInvoice = false;
              PerformSearch(ACriteriaData);
          }
  
          /// <summary>
          ///
          /// </summary>
          /// <param name="ALedgerNumber"></param>
          /// <param name="ASupplierKey"></param>
          public void FindSupplierTransactions(Int32 ALedgerNumber, Int64 ASupplierKey)
          {
              FSearchTransactions = true;
  
              DataTable CriteriaTable = new DataTable();
              CriteriaTable.Columns.Add("PartnerKey", typeof(Int64));
              CriteriaTable.Columns.Add("LedgerNumber", typeof(Int32));
  
              DataRow Row = CriteriaTable.NewRow();
              Row["PartnerKey"] = ASupplierKey;
              Row["LedgerNumber"] = ALedgerNumber;
              CriteriaTable.Rows.Add(Row);
  
              PerformSearch(CriteriaTable);
          }
  
          /// <summary>
          /// Procedure to execute a Find query. Although the full
          /// query results are retrieved from the DB and stored internally in an object,
          /// data will be returned in 'pages' of data, each page holding a defined number
          /// of records.
          ///
          /// </summary>
          /// <param name="ACriteriaData">HashTable containing non-empty Find parameters</param>
          public void PerformSearch(DataTable ACriteriaData)
          {
              string PaymentNumberSQLPart;
  
              FAsyncExecProgress = new TAsynchronousExecutionProgress();
  
              FPagedDataSetObject = new TPagedDataSet(null);
  
              // Pass the TAsynchronousExecutionProgress object to FPagedDataSetObject
              // so that it can update execution status
              FPagedDataSetObject.AsyncExecProgress = FAsyncExecProgress;
  
              // Register Event Handler for the StopAsyncronousExecution event
              FAsyncExecProgress.StopAsyncronousExecution += new System.EventHandler(this.StopSearch);
  
              DataRow CriteriaRow = PrepareDataRow(ACriteriaData);
              Int32 ledgerNumber = (Int32)CriteriaRow["LedgerNumber"];
  
              if (FSearchTransactions)
              {
                  if (CommonTypes.ParseDBType(DBAccess.GDBAccessObj.DBType) == TDBType.SQLite)
                  {
                      // Fix for SQLite: it does not support the 'to_char' Function
                      PaymentNumberSQLPart = "PUB_a_ap_payment.a_payment_number_i as InvNum, ";
                  }
                  else
                  {
                      // whereas PostgreSQL does!
                      PaymentNumberSQLPart = "to_char(PUB_a_ap_payment.a_payment_number_i, '99999') as InvNum, ";
                  }
  
                  Int64 PartnerKey = Convert.ToInt64(CriteriaRow["PartnerKey"]);
                  String SqlQuery = "SELECT DISTINCT " +
                                    "0 as ApDocumentId, " +
                                    "PUB_a_ap_payment.a_payment_number_i as ApNum, " +
                                    PaymentNumberSQLPart +
                                    "true as CreditNote, " +
                                    "'Payment' as Type, " +
                                    "PUB_a_ap_payment.a_currency_code_c as Currency, " +
                                    "PUB_a_ap_payment.a_amount_n as Amount, " +
                                    "0 AS OutstandingAmount, " +
                                    "'' as Status, " +
                                    "0 as DiscountPercent, " +
                                    "0 as DiscountDays, " +
                                    "PUB_a_ap_payment.s_date_created_d as Date " +
                                    " FROM PUB_a_ap_payment LEFT JOIN PUB_a_ap_document_payment on PUB_a_ap_payment.a_payment_number_i = PUB_a_ap_document_payment.a_payment_number_i"
                                    +
                                    " LEFT JOIN PUB_a_ap_document on PUB_a_ap_document_payment.a_ap_document_id_i = PUB_a_ap_document.a_ap_document_id_i\n"
                                    +
                                    " WHERE PUB_a_ap_document_payment.a_ledger_number_i=" + ledgerNumber +
                                    " AND p_partner_key_n=" + PartnerKey +
                                    "\n UNION\n" +
                                    " SELECT " +
                                    "a_ap_document_id_i as ApDocumentId, " +
                                    "a_ap_number_i as ApNum, " +
                                    "a_document_code_c as InvNum, " +
                                    "a_credit_note_flag_l as CreditNote, " +
                                    "'Invoice' as Type, " +
                                    "a_currency_code_c AS Currency, " +
                                    "a_total_amount_n as Amount, " +
                                    "a_total_amount_n AS OutstandingAmount, " +
                                    "a_document_status_c as Status, " +
                                    "a_discount_percentage_n as DiscountPercent, " +
                                    "a_discount_days_i as DiscountDays, " +
                                    "a_date_issued_d as Date " +
                                    "FROM PUB_a_ap_document " +
  
gt:                                   "WHERE a_ledger_number_i=" + ledgerNumber + " " +