using NLog;
using System;
using System.Configuration;
using System.Data.Odbc;

namespace DataUploader
{
  public class GestData
  {

    private static Logger logger = LogManager.GetCurrentClassLogger();

    protected GestData()
    {
      setupConnString();
    }
    protected OdbcConnection conn;
    /// <summary>
    /// configurazione stringa connesisone dati
    /// </summary>
    private void setupConnString()
    {
      conn = new OdbcConnection();
      // ho creato un DSN di sistema a nome "DB_Sigla" cui faccio riferimento (32 perché 32 bit x mia demo... ho dovuto convertire accdb in mdb...)
      //string connectionString = "Dsn=DB_Sigla32;Initial Catalog=TESDOCUM;"; // Questa va su un MDB di cui esiste un DSN
      //string connectionString = "Driver={Microsoft Access Driver(*.mdb, *.accdb)};DBQ=C:\\DB_SIGLA.accdb;";
      //string connectionString = "Dsn=DITTA1;Initial Catalog=TESDOCUM;"; // Questa non funziona senza una password eslpicita, anche se esiste un DSN DITTA1 con pwd salvata
      //string connectionString = "Dsn=DITTA1;Uid=DB2;Pwd=ccL21x$1wow;Initial Catalog=TESDOCUM;"; // Questa funziona se esiste un DSN DITTA1
      //string connectionString = "Driver={IBM DB2 ODBC DRIVER};DataBase=DITTA1; HostName=RigaSRV02; Protocol=TCPIP;Port=50000;Uid=DB2;Pwd=ccL21x$1wow";  //questa funziona anche senza DSN esistente
      string connectionString = ConfigurationManager.AppSettings["connString"];
      conn.ConnectionString = connectionString;
    }

    public static GestData proc = new GestData();


    /// <summary>
    /// restituisce un array di stringhe URL da chiamare per caricare i dati documento / cliente
    /// </summary>
    /// <param name="numeroFrom">numero ultimo doc caricato (restituirà docs successivi)</param>
    /// <returns></returns>
    public string[] getNewDocsUrls(int numeroFrom)
    {

      if (ConfigurationManager.AppSettings["debugMode"] == "true")
      {
        logger.Info("Inizio getNewDocsUrls");
      }

      // ipotesi: si caricano maxDocsToUpload documenti alal volta (e quindi altrettanti clienti)
      int maxDocsToUpload = Convert.ToInt32(ConfigurationManager.AppSettings["maxDocsToUpload"]);
      string[] answ = new string[maxDocsToUpload * 2]; // 2 stringhe, una x doc e 1 x cliente x ogni invio...
      string[] rDoc = new string[20];
      string[] rCli = new string[12];
      string comandoDoc;

      // qui va fatto ciclo x spazzare 1 ad 1 i documenti ed i relativi clienti
      if (false)
      {   // query in formato ACCESS 2010
        comandoDoc = string.Format(@"SELECT TOP {0}
                                            TESDOCUM.TIPOPROTOC, TESDOCUM.NUMERO, TESDOCUM.ESERPROTOC, 
                                            TESDOCUM.NUMEPROTOC, TESDOCUM.DATAPROTOC, TESDOCUM.ESERRIFERI, 
                                            TESDOCUM.NUMERIFERI, TESDOCUM.TIPORIFERI, TESDOCUM.DATARIFERI, 
                                            TESDOCUM.NUMEDOCRIF, TESDOCUM.DATADOCRIF, TESDOCUM.ESERCOLLEG, 
                                            TESDOCUM.CLI_FOR, TESDOCUM.DESTINATAR, TESDOCUM.RGSOC_DEST, 
                                            TESDOCUM.INDIR_DEST, TESDOCUM.LOCAL_DEST, TESDOCUM.CAP_DEST, 
                                            TESDOCUM.ULT_AGG, TESDOCUM.ANNOTAZION 
                                        FROM TESDOCUM
                                        WHERE (CLng(TESDOCUM.NUMERO) > ({1}))
                                            AND (TESDOCUM.TIPOPROTOC IN ('OC', 'DC', 'PR'))
                                        ORDER BY TESDOCUM.NUMERO
                                        ",
                                    maxDocsToUpload, numeroFrom);
      }
      else
      {   // query in formato DB2
        comandoDoc = string.Format(@"SELECT 
                                            TESDOCUM.TIPOPROTOC, TESDOCUM.NUMERO, TESDOCUM.ESERPROTOC, 
                                            TESDOCUM.NUMEPROTOC, TESDOCUM.DATAPROTOC, TESDOCUM.ESERRIFERI, 
                                            TESDOCUM.NUMERIFERI, TESDOCUM.TIPORIFERI, TESDOCUM.DATARIFERI, 
                                            TESDOCUM.NUMEDOCRIF, TESDOCUM.DATADOCRIF, TESDOCUM.ESERCOLLEG, 
                                            TESDOCUM.CLI_FOR, TESDOCUM.DESTINATAR, TESDOCUM.RGSOC_DEST, 
                                            TESDOCUM.INDIR_DEST, TESDOCUM.LOCAL_DEST, TESDOCUM.CAP_DEST, 
                                            TESDOCUM.ULT_AGG, TESDOCUM.ANNOTAZION 
                                        FROM TESDOCUM
                                        WHERE (INT(TESDOCUM.NUMERO) > ({1}))
                                            AND (TESDOCUM.TIPOPROTOC IN ('OC', 'DC', 'PR'))
                                        ORDER BY TESDOCUM.NUMERO
                                        FETCH FIRST {0} ROWS ONLY
                                        ",
                                    maxDocsToUpload, numeroFrom);
      }
      // try/catch x db e ciclo su dati...
      try
      {
        conn.Open();
        if (ConfigurationManager.AppSettings["debugMode"] == "true")
        {
          logger.Info("Aperta connessione ODBC");
        }

        // dati documento
        int num = 0;
        using (OdbcCommand com = new OdbcCommand(comandoDoc, conn))
        {
          using (OdbcDataReader reader = com.ExecuteReader())
          {
            while (reader.Read())
            {
              for (int i = 0; i < 20; i++)
              {
                try
                {
                  rDoc[i] = reader.GetString(i).Trim().Replace(" ", "+");
                }
                catch
                {
                  rDoc[i] = "";
                }
              }

              //if (ConfigurationManager.AppSettings["debugMode"] == "true")
              //{
              //    logger.Info("Completata lettura ODBC x DOC");
              //}

              // il campo 12 è il cod cliente!
              rCli[0] = rDoc[12];

              // cellulare e email trovati in USRCLIFO, relazionato su CODSIGLA
              string comandoCli = string.Format(@"SELECT CLIFO.CODICE, CLIFO.RAGIONESOC, CLIFO.COFI, 
                                                                    CLIFO.TEL, USRCLIFO.CELNM AS CELL, 
                                                                    CASE WHEN USRCLIFO.CELNM <> ' ' THEN 1 ELSE 0 END AS ENABLE_SMS, 
                                                                    USRCLIFO.E_MAILL AS EMAIL,  
                                                                    CLIFO.INDIR1, CLIFO.CAP, CLIFO.LOCALITA, 
                                                                    CLIFO.PROVINCIA, '' AS NoteCli 
                                                                FROM CLIFO INNER JOIN USRCLIFO
                                                                ON CLIFO.CODICE = USRCLIFO.CODSIGLA
                                                                WHERE CLIFO.TIPO='C' 
                                                                    AND CLIFO.CODICE = '{0}'",
                                                  rCli[0]);

              try
              {
                using (OdbcCommand comCli = new OdbcCommand(comandoCli, conn))
                {
                  using (OdbcDataReader readerCli = comCli.ExecuteReader())
                  {
                    while (readerCli.Read())
                    {
                      for (int j = 0; j < 12; j++)
                      {
                        try
                        {
                          rCli[j] = readerCli.GetString(j).Trim().Replace(" ", "+");
                        }
                        catch
                        {
                          rCli[j] = "";
                        }
                      }
                    }
                  }
                }

                //if (ConfigurationManager.AppSettings["debugMode"] == "true")
                //{
                //    logger.Info("Completata lettura ODBC x CLI");
                //}

                // formatto answ... prima CLI poi DOCS...
                answ[num++] = createUrlCli(rCli);
                answ[num++] = createUrlDoc(rDoc);
              }
              catch (Exception ex)
              {
                logger.Error("Eccezione in comandoCli{0}Comando: {1}{0}{2}", Environment.NewLine, comandoCli, ex);
              }
            }
          }
        }
      }
      catch (Exception ex)
      {
        logger.Error("Eccezione{0}Comando: {1}{0}{2}", Environment.NewLine, comandoDoc, ex);
      }

      finally
      {
        conn.Close();
      }

      if (ConfigurationManager.AppSettings["debugMode"] == "true")
      {
        logger.Info("Chiusura connessione ODBC");
      }

      // end ciclo, resituisco tutti i records
      return answ;
    }

    /// <summary>
    /// restituisce un array di stringhe URL da chiamare per caricare i dati documento / cliente
    /// </summary>
    /// <param name="numeroFrom">numero ultimo doc caricato (restituirà docs successivi)</param>
    /// <param name="num2reload">numero di doc da ricaricare (prima dell'ultimo caricato...)</param>
    /// <returns></returns>
    public string[] reloadLastDocs(int numeroFrom, int num2reload)
    {

      if (ConfigurationManager.AppSettings["debugMode"] == "true")
      {
        logger.Info("Inizio getNewDocsUrls");
      }

      // ipotesi: si caricano maxDocsToUpload documenti alla volta (e quindi altrettanti clienti)
      int maxDocsToUpload = num2reload + 1;
      string[] answ = new string[maxDocsToUpload * 2]; // 2 stringhe, una x doc e 1 x cliente x ogni invio...
      string[] rDoc = new string[20];
      string[] rCli = new string[12];
      string comandoDoc;

      // qui va fatto ciclo x spazzare 1 ad 1 i documenti ed i relativi clienti
      if (false)
      {   // query in formato ACCESS 2010
        comandoDoc = string.Format(@"SELECT TOP {0}
                                            TESDOCUM.TIPOPROTOC, TESDOCUM.NUMERO, TESDOCUM.ESERPROTOC, 
                                            TESDOCUM.NUMEPROTOC, TESDOCUM.DATAPROTOC, TESDOCUM.ESERRIFERI, 
                                            TESDOCUM.NUMERIFERI, TESDOCUM.TIPORIFERI, TESDOCUM.DATARIFERI, 
                                            TESDOCUM.NUMEDOCRIF, TESDOCUM.DATADOCRIF, TESDOCUM.ESERCOLLEG, 
                                            TESDOCUM.CLI_FOR, TESDOCUM.DESTINATAR, TESDOCUM.RGSOC_DEST, 
                                            TESDOCUM.INDIR_DEST, TESDOCUM.LOCAL_DEST, TESDOCUM.CAP_DEST, 
                                            TESDOCUM.ULT_AGG, TESDOCUM.ANNOTAZION 
                                        FROM TESDOCUM
                                        WHERE (CLng(TESDOCUM.NUMERO) > ({1}))
                                            AND (TESDOCUM.TIPOPROTOC IN ('OC', 'DC', 'PR'))
                                        ORDER BY TESDOCUM.NUMERO
                                        ",
                                    maxDocsToUpload, numeroFrom);
      }
      else
      {   // query in formato DB2
        comandoDoc = string.Format(@"SELECT 
                                            TESDOCUM.TIPOPROTOC, TESDOCUM.NUMERO, TESDOCUM.ESERPROTOC, 
                                            TESDOCUM.NUMEPROTOC, TESDOCUM.DATAPROTOC, TESDOCUM.ESERRIFERI, 
                                            TESDOCUM.NUMERIFERI, TESDOCUM.TIPORIFERI, TESDOCUM.DATARIFERI, 
                                            TESDOCUM.NUMEDOCRIF, TESDOCUM.DATADOCRIF, TESDOCUM.ESERCOLLEG, 
                                            TESDOCUM.CLI_FOR, TESDOCUM.DESTINATAR, TESDOCUM.RGSOC_DEST, 
                                            TESDOCUM.INDIR_DEST, TESDOCUM.LOCAL_DEST, TESDOCUM.CAP_DEST, 
                                            TESDOCUM.ULT_AGG, TESDOCUM.ANNOTAZION 
                                        FROM TESDOCUM
                                        WHERE (INT(TESDOCUM.NUMERO) > ({1}))
                                            AND (TESDOCUM.TIPOPROTOC IN ('OC', 'DC', 'PR'))
                                        ORDER BY TESDOCUM.NUMERO
                                        FETCH FIRST {0} ROWS ONLY
                                        ",
                                    maxDocsToUpload, numeroFrom - num2reload);
      }
      // try/catch x db e ciclo su dati...
      try
      {
        conn.Open();
        if (ConfigurationManager.AppSettings["debugMode"] == "true")
        {
          logger.Info("Aperta connessione ODBC");
        }

        // dati documento
        int num = 0;
        using (OdbcCommand com = new OdbcCommand(comandoDoc, conn))
        {
          using (OdbcDataReader reader = com.ExecuteReader())
          {
            while (reader.Read())
            {
              for (int i = 0; i < 20; i++)
              {
                try
                {
                  rDoc[i] = reader.GetString(i).Trim().Replace(" ", "+");
                }
                catch
                {
                  rDoc[i] = "";
                }
              }

              //if (ConfigurationManager.AppSettings["debugMode"] == "true")
              //{
              //    logger.Info("Completata lettura ODBC x DOC");
              //}

              // il campo 12 è il cod cliente!
              rCli[0] = rDoc[12];

              // cellulare e email trovati in USRCLIFO, relazionato su CODSIGLA
              string comandoCli = string.Format(@"SELECT CLIFO.CODICE, CLIFO.RAGIONESOC, CLIFO.COFI, 
                                                                    CLIFO.TEL, USRCLIFO.CELNM AS CELL, 
                                                                    CASE WHEN USRCLIFO.CELNM <> ' ' THEN 1 ELSE 0 END AS ENABLE_SMS, 
                                                                    USRCLIFO.E_MAILL AS EMAIL,  
                                                                    CLIFO.INDIR1, CLIFO.CAP, CLIFO.LOCALITA, 
                                                                    CLIFO.PROVINCIA, '' AS NoteCli 
                                                                FROM CLIFO INNER JOIN USRCLIFO
                                                                ON CLIFO.CODICE = USRCLIFO.CODSIGLA
                                                                WHERE CLIFO.TIPO='C' 
                                                                    AND CLIFO.CODICE = '{0}'",
                                                  rCli[0]);

              try
              {
                using (OdbcCommand comCli = new OdbcCommand(comandoCli, conn))
                {
                  using (OdbcDataReader readerCli = comCli.ExecuteReader())
                  {
                    while (readerCli.Read())
                    {
                      for (int j = 0; j < 12; j++)
                      {
                        try
                        {
                          rCli[j] = readerCli.GetString(j).Trim().Replace(" ", "+");
                        }
                        catch
                        {
                          rCli[j] = "";
                        }
                      }
                    }
                  }
                }

                //if (ConfigurationManager.AppSettings["debugMode"] == "true")
                //{
                //    logger.Info("Completata lettura ODBC x CLI");
                //}

                // formatto answ... prima CLI poi DOCS...
                answ[num++] = createUrlCli(rCli);
                answ[num++] = createUrlDoc(rDoc);
              }
              catch (Exception ex)
              {
                logger.Error("Eccezione in comandoCli{0}Comando: {1}{0}{2}", Environment.NewLine, comandoCli, ex);
              }
            }
          }
        }
      }
      catch (Exception ex)
      {
        logger.Error("Eccezione{0}Comando: {1}{0}{2}", Environment.NewLine, comandoDoc, ex);
      }

      finally
      {
        conn.Close();
      }

      if (ConfigurationManager.AppSettings["debugMode"] == "true")
      {
        logger.Info("Chiusura connessione ODBC");
      }

      // end ciclo, resituisco tutti i records
      return answ;
    }
    /// <summary>
    /// restituisce un array di stringhe URL da chiamare per caricare i dati documento / cliente
    /// </summary>
    /// <param name="CodDoc">Codice documento richiesto</param>
    /// <param name="Anno">Anno documento richiesto</param>
    /// <returns></returns>
    public string[] getDocsUrls(string CodDoc, string Anno)
    {
      string[] answ = new string[2]; // 2 stringhe, una x doc e 1 x cliente x ogni invio...
                                     // per ogni record trovato compongo un record DOCS ed uno CLI
      string[] rDoc = new string[20];
      string[] rCli = new string[12];

      // effettuo chaimata ODBC e recupero dati
      try
      {
        conn.Open();
        string tipoDoc = CodDoc.Substring(0, 2);
        string NumDoc = CodDoc.Replace(tipoDoc, "");
        // questa è la vera stringa SQL
        string comandoDoc = string.Format(@"SELECT TESDOCUM.TIPOPROTOC, TESDOCUM.NUMERO, TESDOCUM.ESERPROTOC, 
                                                        TESDOCUM.NUMEPROTOC, TESDOCUM.DATAPROTOC, TESDOCUM.ESERRIFERI, 
                                                        TESDOCUM.NUMERIFERI, TESDOCUM.TIPORIFERI, TESDOCUM.DATARIFERI, 
                                                        TESDOCUM.NUMEDOCRIF, TESDOCUM.DATADOCRIF, TESDOCUM.ESERCOLLEG, 
                                                        TESDOCUM.CLI_FOR, TESDOCUM.DESTINATAR, TESDOCUM.RGSOC_DEST, 
                                                        TESDOCUM.INDIR_DEST, TESDOCUM.LOCAL_DEST, TESDOCUM.CAP_DEST, 
                                                        TESDOCUM.ULT_AGG, TESDOCUM.ANNOTAZION 
                                                    FROM TESDOCUM 
                                                    WHERE TESDOCUM.TIPOPROTOC='{0}'
                                                        AND TESDOCUM.NUMEPROTOC ='{1}' 
                                                        AND TESDOCUM.ESERPROTOC ='{2}';",
                                            tipoDoc, NumDoc, Anno);
        // dati documento
        using (OdbcCommand com = new OdbcCommand(comandoDoc, conn))
        {
          using (OdbcDataReader reader = com.ExecuteReader())
          {
            while (reader.Read())
            {
              for (int i = 0; i < 20; i++)
              {
                try
                {
                  rDoc[i] = reader.GetString(i).Trim().Replace(" ", "+");
                }
                catch
                {
                  rDoc[i] = "";
                }
              }
              // il campo 12 è il cod cliente!
              rCli[0] = rDoc[12];
            }
          }
        }

        // cellulare e email trovati in USRCLIFO, relazionato su CODSIGLA
        string comandoCli = string.Format(@"SELECT CLIFO.CODICE, CLIFO.RAGIONESOC, CLIFO.COFI, 
                                                        CLIFO.TEL, USRCLIFO.CELNM AS CELL,  
                                                        CASE WHEN USRCLIFO.CELNM <> ' ' THEN 1 ELSE 0 END AS ENABLE_SMS, 
                                                        USRCLIFO.E_MAILL AS EMAIL,  
                                                        CLIFO.INDIR1, CLIFO.CAP, CLIFO.LOCALITA, 
                                                        CLIFO.PROVINCIA, '' AS NoteCli 
                                                    FROM CLIFO INNER JOIN USRCLIFO
                                                    ON CLIFO.CODICE = USRCLIFO.CODSIGLA
                                                    WHERE CLIFO.TIPO='C' 
                                                        AND CLIFO.CODICE = '{0}';",
                                           rCli[0]);
        using (OdbcCommand comCli = new OdbcCommand(comandoCli, conn))
        {
          using (OdbcDataReader readerCli = comCli.ExecuteReader())
          {
            while (readerCli.Read())
            {
              for (int i = 0; i < 12; i++)
              {
                try
                {
                  rCli[i] = readerCli.GetString(i).Trim().Replace(" ", "+");
                }
                catch
                {
                  rCli[i] = "";
                }
              }
            }
          }
        }
        // formatto answ... prima CLI poi doc...
        answ[0] = createUrlCli(rCli);
        answ[1] = createUrlDoc(rDoc);
      }
      catch (Exception ex)
      {
        logger.Error("Eccezione{0}{1}", Environment.NewLine, ex);
      }
      finally
      {
        conn.Close();
      }
      return answ;
    }
    /// <summary>
    /// crea url formattato x upload DOC
    /// </summary>
    /// <param name="rDoc"></param>
    /// <returns></returns>
    protected string createUrlDoc(string[] rDoc)
    {
      string answ = "";
      try
      {
        answ = string.Format(@"{0}?TIPOPROTOC={1}&NUMERO={2}&ESERPROTOC={3}&NUMEPROTOC={4}&DATAPROTOC={5}&ESERRIFERI={6}&NUMERIFERI={7}&TIPORIFERI={8}&DATARIFERI={9}&NUMEDOCRIF={10}&DATADOCRIF={11}&ESERCOLLEG={12}&CLI_FOR={13}&DESTINATAR={14}&RGSOC_DEST={15}&INDIR_DEST={16}&LOCAL_DEST={17}&CAP_DEST={18}&ULT_AGG={19}&ANNOTAZION={20}", ConfigurationManager.AppSettings["remoteDocUrl"], rDoc[0], rDoc[1], rDoc[2], rDoc[3], rDoc[4], rDoc[5], rDoc[6], rDoc[7], rDoc[8], rDoc[9], rDoc[10], rDoc[11], rDoc[12], rDoc[13], rDoc[14], rDoc[15], rDoc[16], rDoc[17], rDoc[18], rDoc[19]);
      }
      catch (Exception exc)
      {
        logger.Error("Eccezione in createUrlDoc{0}{1}", Environment.NewLine, exc);
      }
      return answ;
    }
    /// <summary>
    /// crea url formattato x upload CLI
    /// </summary>
    /// <param name="rCli"></param>
    /// <returns></returns>
    protected string createUrlCli(string[] rCli)
    {
      string answ = "";
      try
      {
        answ = string.Format("{0}?CodCliente={1}&RagSoc={2}&CodFis={3}&Tel={4}&Cell={5}&EnableSMS={6}&Email={7}&Indir={8}&Cap={9}&Localita={10}&Prov={11}&Note={12}", ConfigurationManager.AppSettings["remoteCliUrl"], rCli[0], rCli[1], rCli[2], rCli[3], rCli[4], rCli[5], rCli[6], rCli[7], rCli[8], rCli[9], rCli[10], rCli[11]);
      }
      catch (Exception exc)
      {
        logger.Error("Eccezione in createUrlCli{0}{1}", Environment.NewLine, exc);
      }
      return answ;
    }
  }
}