context: //
  // DO NOT REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  //
  // @Authors:
  //       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.Data.Odbc;
  using System.Collections.Generic;
  using Ict.Petra.Shared.MReporting;
  using Ict.Common;
  
  namespace Ict.Petra.Server.MReporting
  {
      /// <summary>
      /// tools to format an SQL query so that it is useful for the SQL server
      /// </summary>
      public class TRptFormatQuery
      {
          private TParameterList parameters;
          private int column;
          private int depth;
          private List<OdbcParameter> FOdbcParameters;
          private string FSQLStmt;
          private TVariant FVariantValue = new TVariant();
  
          /// <summary>
          /// constructor
          /// </summary>
          public TRptFormatQuery(string ASQLStmt = "", List<OdbcParameter> AOdbcParameters = null, TParameterList parameters = null, int column = -1, int depth = -1)
          {
              this.FSQLStmt = ASQLStmt;
  
              if (AOdbcParameters == null)
              {
                  this.FOdbcParameters = new List<OdbcParameter>();
              }
              else
              {
                  this.FOdbcParameters = AOdbcParameters;
              }
  
              this.parameters = parameters;
              this.column = column;
              this.depth = depth;
          }
  
          /// read the sql statement
          public string SQLStmt
          {
              get
              {
                  return this.FSQLStmt.Replace("PARAMETER?", "?");
              }
          }
  
          /// <summary>
          /// returns true if this is not an sql statement but a single value that can be accessed through the VariantValue property
          /// </summary>
          /// <returns></returns>
          public bool IsVariant
          {
              get
              {
                  return this.FSQLStmt == "TVariant";
              }
          }
          
          /// <summary>
          /// returns true if the value is zero or null
          /// </summary>
          public bool IsZeroOrNull()
          {
              if (IsVariant)
              {
                  return FVariantValue.IsZeroOrNull();
              }
              TVariant v = new TVariant(this.FSQLStmt);
              return v.IsZeroOrNull();
          }
  
          /// <summary>
          /// returns the value if it is not an SQL statement.
          /// </summary>
          public TVariant VariantValue
          {
              get
              {
                  if (!IsVariant)
                  {
                      if (this.FOdbcParameters.Count == 0)
                      {
                          // we assume this is a single value, not an SQL statement
                          return new TVariant(this.FSQLStmt);
                      }
  
                      TLogging.Log("expected a single variant value: " + this.FSQLStmt);
                      throw new Exception("expected a single variant value, not an SQL string");
                  }
  
                  return this.FVariantValue;
              }
          }
  
          /// <summary>
          /// only a workaround for text on the report that is not used for sql queries
          /// </summary>
          /// <returns></returns>
          public TVariant ConvertToVariant()
          {
              return this.VariantValue;
  #if notneeded
              if (IsVariant)
              {
                  return this.VariantValue;
              }
  
              if (this.FOdbcParameters.Count != 0 && this.FSQLStmt.StartsWith("SELECT "))
              {
                  TLogging.Log("ConvertToString is only meant for text that is not an SQL query: " + this.FSQLStmt);
                  throw new Exception("expected a text string, not an SQL string");
              }
  
              string s = this.FSQLStmt;
              int pos = -1;
              int count = 0;
  
              while((pos = s.IndexOf("PARAMETER?")) != -1)
              {
                  s = s.Substring(0, pos) + FOdbcParameters[count].Value.ToString() + s.Substring(pos + "PARAMETER?".Length);
                  count++;
              }
  
              return new TVariant(s);
  #endif
          }
  
          /// convert to string for debugging purposes only
          public override string ToString()
          {
              string result = String.Empty;
  
              if (IsVariant)
              {
                  result = this.VariantValue.EncodeToString();
              }
              else
              {
                  result = this.FSQLStmt;
              }
  
              return result + " with " + this.FOdbcParameters.Count.ToString() + " Parameters";
          }
  
          /// read the odbc parameters
          public List<OdbcParameter> OdbcParameters
          {
              get
              {
                  return this.FOdbcParameters;
              }
          }
  
          /// <summary>
          /// add more text to the sql statement
          /// </summary>
          public void Add(string sql)
          {
              if (IsVariant)
              {
                  this.FVariantValue.Add(new TVariant(sql, true));
              }
              else
              {
                  this.FSQLStmt += sql;
              }
          }
  
          /// <summary>
          /// add more to the variant
          /// </summary>
          public void Add(TVariant v, string format = "")
          {
              if (FSQLStmt == string.Empty)
              {
                  FSQLStmt = "TVariant";
              }
              else if (!IsVariant)
              {
                  this.FVariantValue = new TVariant(this.FSQLStmt);
                  FSQLStmt = "TVariant";
              }
  
              this.FVariantValue.Add(v, format);
          }
  
          /// <summary>
          /// combine two sql statements with parameters
          /// </summary>
          public void Add(TRptFormatQuery AQueryToAdd)
          {
              if (AQueryToAdd.IsVariant)
              {
                  if (this.IsVariant)
                  {
                      this.Add(AQueryToAdd.VariantValue);
                  }
                  else
                  {
                      this.Add(AQueryToAdd.VariantValue.ToString());
                  }
              }
              else
              {
                  if (this.IsVariant)
                  {
                      this.FSQLStmt = VariantValue.ToString();
                  }
  
                  this.FSQLStmt += AQueryToAdd.SQLStmt;
              }
  
              foreach (OdbcParameter p in AQueryToAdd.FOdbcParameters)
              {
                  this.FOdbcParameters.Add(p);
              }
          }
  
          // do not print warning too many times for the same variable
          private SortedList <string, Int32>VariablesNotFound = new SortedList <string, int>();
  
          /// <summary>
          /// Replace parameters with ODBC Parameters
          /// </summary>
          /// <param name="searchOpen"></param>
          /// <param name="searchClose"></param>
          protected void ReplaceVariablesPattern(
              String searchOpen,
              String searchClose)
          {
              if (IsVariant)
              {
                  this.FSQLStmt = this.VariantValue.ToString();
              }
  
              int bracket = this.FSQLStmt.IndexOf(searchOpen);
  
              while (bracket != -1)
              {
  
gt:                 int firstRealChar = bracket + searchOpen.Length;