vn-fingerprint/LectorVerdnatura/database.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Globalization;

namespace LectorVerdnatura
{

    class database
    {
        private OdbcConnection conn = new OdbcConnection();
        //private const String serverdb = "db.verdnatura.es";
        //private const String serverport = "3306";
        //private const String databasedb = "vn";

        private const String serverdb = ""; // "test-db.verdnatura.es";
        private const String serverport = ""; // "3307";
        private const String databasedb = ""; // "postgresql";
        //private const String usuariodb = "root";
        //private const String passworddb = "";
        private OdbcTransaction myCommit;
        private Boolean hayconexion = false;

        public database()
        {
            try
            {
                if (conn.State == ConnectionState.Closed){
                    //conn.ConnectionString = "Driver={PostgreSQL Unicode};Connection Reset = True;Connection Lifetime = 600;integrated security=SSPI;Pooling=true;Min Pool Size=1;Max Pool Size=1;Server=" + serverdb +  ";database=" + databasedb + ";username=" + usuariodb + ";password=" + passworddb;
                    //conn.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Connection Reset = True;Connection Lifetime = 600;integrated security=SSPI;Pooling=true;Min Pool Size=1;Max Pool Size=1;Server=" + serverdb + ";port=" + serverport + ";database=" + databasedb + ";username=" + usuariodb + ";password=" + passworddb;
                    conn.ConnectionString = "DSN=LectorVerdnaturaV2";
                    conn.Open();
                    hayconexion = true;
                }
            }
            catch (Exception ex)
            {
                logevent.logeventwritefile("Error al conectar al Server " + serverdb + " Puerto: " + serverport + " o DSN: " + conn.ConnectionString);
                logevent.logeventwritefile("Error: " + ex.ToString());
                hayconexion = false;
            }
        }

        public Boolean Gethayconexion()
        {
            return this.hayconexion;
        }

        public String GetDBConnectionDescription()
        {
            return conn.ConnectionString + " Server: " + conn.DataSource.ToString() + " Database: " + conn.Database.ToString();
        }

        public Boolean insertarbd(int idemple_lector, String fecha, int warehouseFk)    // Esta funcion ya no se debe usar, se inserta directamente la fichada con TODOS los datos.
        {
            Boolean retorno = false;

            if (!hayconexion) return retorno;

            String Sql = "INSERT INTO transfer_reader_tmp (nis, date, warehouseFk) VALUES (" + idemple_lector + ", '" + fecha + "'," + warehouseFk + ");";
            OdbcCommand cmd = new OdbcCommand(Sql, conn); 
            myCommit = conn.BeginTransaction();
            cmd.Transaction=myCommit;

            try
            {
                cmd.ExecuteNonQuery();
                myCommit.Commit();
                retorno = true;
            }
            catch (OdbcException ex) when (ex.Errors[0].NativeError == 1062)
            {
                logevent.logeventwritefile("Error SQL 2601: Registro con clave duplicada. Ignorando!. SQL: " + Sql);
                myCommit.Rollback();
                return true;
            }
            catch (Exception ex)
            {
                logevent.logeventwritefile("error al ejecutar " + Sql + " Transaccion cancelada!");
                logevent.logeventwritefile("Error: " + ex.ToString());
                try
                {
                    myCommit.Rollback();
                }
                catch (OdbcException excommit)
                {
                    if (myCommit.Connection == null)
                    {
                        logevent.logeventwritefile("error Commit insertar fichajes ");
                        logevent.logeventwritefile("Error: " + excommit.ToString());
                    }
                }//catch
            }//catch
            return retorno;
        }//function

        public int ejecutarsql(String Sql)
        {
            if (!hayconexion) return -1;
            //try
            //{
                OdbcCommand cmd = new OdbcCommand(Sql, conn);
                return cmd.ExecuteNonQuery();
            //}
            //catch (OdbcException ex) when (ex.Errors[0].NativeError == 1062)
            //{
                // Ignoramos el error, la base de datos esta preparada para esto.
                // logevent.logeventwritefile("Error SQL 2601: Registro con clave duplicada. Ignorando!. SQL: " + Sql);
            //}
            //catch (Exception ex)
            //{
            //    logevent.logeventwritefile("Error al ejecutar " + Sql);
            //    logevent.logeventwritefile("Error: " + ex.ToString());
            //}
        }

        public int proximotimer(String horapc){

            const int intervalsinconexion = 600000; //si no hay conexion intervalo 10 minutos.

            if (!hayconexion)
            {
                logevent.logeventwritefile("Sin conexion a la BDD, próxima ejecución a las " + System.DateTime.Now.AddMilliseconds(intervalsinconexion).ToString()); //+ intervalsinconexion/1000 + " segundos");
                return intervalsinconexion;
            }
            String Sql = " SELECT time FROM vn.workerTimeControlSchedule WHERE time > '" + horapc + "' ORDER BY time ASC LIMIT 1;";

            try { 
                OdbcCommand cmd = new OdbcCommand(Sql, conn);
                OdbcDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows) {
                    reader.Read();
                    int interval;
                    DateTime hora = reader.GetDateTime(0);
                    logevent.logeventwritefile("Próxima descarga de fichajes a las: " + hora.TimeOfDay.ToString());
                    DateTime ahora = System.DateTime.Now;
                    TimeSpan diferencia = hora.Subtract(ahora);
                    interval = (diferencia.Hours * 3600 + diferencia.Minutes * 60 + diferencia.Seconds) * 1000;
                    reader.Close();
                    return interval;
                } else
                {
                    logevent.logeventwritefile("Aviso: No se ha encontrado proxima ejecucion con hora > " + horapc + ". Tabla vacia o no existe registro '23:59': reintentando en 10 minutos...");
                    return intervalsinconexion;
                }
            }
            catch (Exception ex) {
                logevent.logeventwritefile("error al ejecutar " + Sql);
                logevent.logeventwritefile("Error: " + ex.ToString());
                logevent.logeventwritefile("Error: reintentando en 10 minutos...");
                return intervalsinconexion;
            }
        }

        public void CargarFichadoresDesdeBDD(ref String[,] Fichadores)
        {
            if (!hayconexion)
            {
                logevent.logeventwritefile("Error: No esta conectado a la BDD!");
            }
            else
            {
                String Sql = ("SELECT WH.id, WH.name, WTCC.host FROM vn.workerTimeControlConfig AS WTCC INNER JOIN vn.warehouse AS WH ON (WTCC.warehouseFk= WH.id) ORDER BY WH.id");

                try
                {
                    OdbcCommand cmd = new OdbcCommand(Sql, conn);
                    OdbcDataReader reader = cmd.ExecuteReader();

                    for (int a = 0; a <= reader.RecordsAffected - 1; a++)
                    {
                        reader.Read();
                        Fichadores[a, 0] = reader.GetString(0);
                        Fichadores[a, 1] = reader.GetString(1);
                        Fichadores[a, 2] = reader.GetString(2);
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    logevent.logeventwritefile("error al ejecutar " + Sql);
                    logevent.logeventwritefile("Error: " + ex.ToString());
                }
            }
        }


        public void CerrarConexion()
        {
            try { conn.Dispose(); }
            catch (Exception ex)
            {
                logevent.logeventwritefile("Error al cerrar la conexión al Server " + serverdb);
                logevent.logeventwritefile("Error: " + ex.ToString());
            }
        }
    } //class
}//namespace