context: //
  // DO NOT REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  //
  // @Authors:
  //       wolfgangb, timop
  //
  // Copyright 2004-2019 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.Specialized;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.Odbc;
  using Ict.Common.DB;
  using Ict.Common;
  using Ict.Common.Data;
  using Ict.Common.Verification;
  using Ict.Petra.Server.MCommon.Data.Access;
  using Ict.Petra.Server.MConference.Data.Access;
  using Ict.Petra.Server.MPartner.Partner.Data.Access;
  using Ict.Petra.Server.MPartner.Mailroom.Data.Access;
  using Ict.Petra.Shared;
  using Ict.Petra.Shared.MCommon.Data;
  using Ict.Petra.Shared.MConference.Data;
  using Ict.Petra.Shared.MPartner;
  using Ict.Petra.Shared.MPartner.Mailroom.Data;
  using Ict.Petra.Shared.MPartner.Partner.Data;
  using Ict.Petra.Server.App.Core.Security;
  
  
  namespace Ict.Petra.Server.MPartner.Partner.WebConnectors
  {
      /// <summary>
      /// Performs server-side lookups for the Client in the MCommon DataReader sub-namespace.
      ///
      /// </summary>
      public class TPartnerDataReaderWebConnector
      {
          /// <summary>
          /// return unit table which contains conferences that match a given search string
          /// Only returns key, name and outreach code fields at the moment.
          /// </summary>
          /// <returns></returns>
          [RequireModulePermission("PTNRUSER")]
          public static DataTable GetEventUnits()
          {
              List <OdbcParameter>SqlParameterList = new List <OdbcParameter>();
              DataColumn[] Key = new DataColumn[3];
              DataTable Events = new DataTable();
  
              if (TLogging.DL >= 9)
              {
                  Console.WriteLine("GetEventUnits called!");
              }
  
              TDBTransaction Transaction = new TDBTransaction();
  
              DBAccess.ReadTransaction(
                  ref Transaction,
                  delegate
                  {
                      string SqlStmt =
                          "SELECT DISTINCT " +
                          PPartnerTable.GetPartnerShortNameDBName() +
                          ", " + PPartnerTable.GetPartnerClassDBName() +
                          ", " + PUnitTable.GetOutreachCodeDBName() +
                          ", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
                          ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() +
                          ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() +
                          ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
                          ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() +
  
                          ", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
                          ", " + PUnitTable.GetUnitTypeCodeDBName() +
                          ", " + PUnitTable.GetUnitNameDBName() +
  
                          " FROM pub_" + PPartnerTable.GetTableDBName() +
                          ", pub_" + PUnitTable.GetTableDBName() +
                          ", pub_" + PLocationTable.GetTableDBName() +
                          ", pub_" + PPartnerLocationTable.GetTableDBName() +
                          ", pub_" + PCountryTable.GetTableDBName() +
  
                          " WHERE " +
                          PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
                          PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " +
  
                          PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
                          PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +
                          PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
                          PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
                          PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
                          PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
                          PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
                          PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " +
  
                          PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' " + " AND " +
                          PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' ";
  
                      // sort rows according to name
                      SqlStmt = SqlStmt + " ORDER BY " + PPartnerTable.GetPartnerShortNameDBName();
  
                      Events = Transaction.DataBaseObj.SelectDT(SqlStmt, "events",
                          Transaction, SqlParameterList.ToArray());
  
                      Key[0] = Events.Columns[PPartnerTable.GetPartnerKeyDBName()];
                      Key[1] = Events.Columns[PPartnerLocationTable.GetSiteKeyDBName()];
                      Key[2] = Events.Columns[PPartnerLocationTable.GetLocationKeyDBName()];
                      Events.PrimaryKey = Key;
                  });
  
              return Events;
          }
  
          /// <summary>
          /// Return unit table which contains fields (unit partners) that match a given search string and have
          /// status ACTIVE.
          /// Only returns key and name field at the moment.
          /// </summary>
          /// <returns></returns>
          [RequireModulePermission("PTNRUSER")]
          public static PUnitTable GetActiveFieldUnits()
          {
              PUnitTable UnitTable = new PUnitTable();
              PUnitRow UnitRow;
  
              TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetActiveFieldUnits called!");
  
              TDBTransaction Transaction = new TDBTransaction();
  
              DBAccess.ReadTransaction(
                  ref Transaction,
                  delegate
                  {
                      // Load data
                      string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                       ", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() +
                                       " FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() +
                                       " WHERE ((" + PUnitTable.GetOutreachCodeDBName() + " IS NULL)" +
                                       "        OR(" + PUnitTable.GetOutreachCodeDBName() + " = ''))" +
                                       " AND " + PUnitTable.GetUnitTypeCodeDBName() + " <> 'KEY-MIN'" +
                                       " AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                       " = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
                                       " AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'";
  
                      // sort rows according to name
                      SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName();
  
                      DataTable events = Transaction.DataBaseObj.SelectDT(SqlStmt, "fields", Transaction);
  
                      foreach (DataRow eventRow in events.Rows)
                      {
                          UnitRow = (PUnitRow)UnitTable.NewRow();
                          UnitRow.PartnerKey = Convert.ToInt64(eventRow[0]);
                          UnitRow.UnitName = Convert.ToString(eventRow[1]);
                          UnitTable.Rows.Add(UnitRow);
                      }
                  });
  
              return UnitTable;
          }
  
          /// <summary>
          /// Return unit table which contains ledger records (unit partners) that match a given search string
          /// Only returns key and name field at the moment.
          /// </summary>
          /// <returns></returns>
          [RequireModulePermission("PTNRUSER")]
          public static PUnitTable GetLedgerUnits()
          {
              PUnitTable UnitTable = new PUnitTable();
              PUnitRow UnitRow;
  
              TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetLedgerUnits called!");
  
              TDBTransaction Transaction = new TDBTransaction();
  
              DBAccess.ReadTransaction(
                  ref Transaction,
                  delegate
                  {
                      // Load data
                      string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                       ", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() +
                                       " FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTypeTable.GetTableDBName() +
                                       " WHERE pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                       " = pub_" + PPartnerTypeTable.GetTableDBName() + "." + PPartnerTypeTable.GetPartnerKeyDBName() +
                                       " AND " + PPartnerTypeTable.GetTypeCodeDBName() + " = '" + MPartnerConstants.PARTNERTYPE_LEDGER + "'";
  
                      // sort rows according to name
                      SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName();
  
                      DataTable events = Transaction.DataBaseObj.SelectDT(SqlStmt, "ledgers", Transaction);
  
                      foreach (DataRow eventRow in events.Rows)
                      {
                          UnitRow = (PUnitRow)UnitTable.NewRow();
                          UnitRow.PartnerKey = Convert.ToInt64(eventRow[0]);
                          UnitRow.UnitName = Convert.ToString(eventRow[1]);
                          UnitTable.Rows.Add(UnitRow);
                      }
                  });
  
              return UnitTable;
          }
  
          /// <summary>
          /// Check if a PUnit record has a corresponding PcConference record in the db
          /// </summary>
          /// <param name="APartnerKey">match long for partner key</param>
          /// <returns></returns>
          [RequireModulePermission("PTNRUSER")]
          public static Boolean IsPUnitAConference(Int64 APartnerKey)
          {
              Boolean ReturnValue = false;
  
gt:             TDBTransaction Transaction = new TDBTransaction();