using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using fatd_faturamento.ENTIDADE;
using fatd_faturamento.DAL.Conexao;

namespace fatd_faturamento.DAL.Persistencia
{
    public class dalTarifaAntiga
    {


        string vFILTER = null;
        string vINNERJOIN = null;
        string vAND = null;
        string vORDERBY = null;

        #region Métodos

        public void Insert(entidadeTarifaAntiga objTarifaAntiga)
        {
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {
                    vFILTER = vFILTER + "NUM        = ?NUM ,  ";
                    vFILTER = vFILTER + "DESCRI     = ?DESCRI,  ";
                    vFILTER = vFILTER + "LIMITKWH   = ?LIMITKWH,  ";
                    vFILTER = vFILTER + "DATANOVA   = ?DATANOVA ,  ";
                    vFILTER = vFILTER + "REFERENCIA = ?REFERENCIA,  ";
                    vFILTER = vFILTER + "VALOR      = ?VALOR  ";

                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objTarifaAntiga.DESCRI.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIMITKWH", objTarifaAntiga.LIMITKWH.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DATANOVA", objTarifaAntiga.DATANOVA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);
                    cmd.Parameters.AddWithValue("?VALOR", objTarifaAntiga.VALOR);


                    objGerenciadorTransacao.Execute(cmd);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public void Insert_1(entidadeTarifaAntiga objTarifaAntiga)
        {
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM = ?NUM, ";
                    vFILTER = vFILTER + "DESCRI = ?DESCRI, ";
                    vFILTER = vFILTER + "UN = NULL, ";
                    vFILTER = vFILTER + "LIMITKWH = ?LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR = ?VALOR, ";
                    vFILTER = vFILTER + "DATANOVA = ?DATANOVA, ";
                    vFILTER = vFILTER + "REFERENCIA = ?REFERENCIA, ";
                    vFILTER = vFILTER + "FLGCASC = NULL, ";
                    vFILTER = vFILTER + "VALOR_AMARELA = ?VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA = ?VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA = ?VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO = ?VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B = ?VALOR_TUSD_FIO_B ";

                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objTarifaAntiga.DESCRI.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?UN", objTarifaAntiga.UN.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIMITKWH", objTarifaAntiga.LIMITKWH.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?VALOR", objTarifaAntiga.VALOR);
                    cmd.Parameters.AddWithValue("?DATANOVA", objTarifaAntiga.DATANOVA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);
                    cmd.Parameters.AddWithValue("?FLGCASC", objTarifaAntiga.FLGCASC.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?VALOR_AMARELA", objTarifaAntiga.VALOR_AMARELA);
                    cmd.Parameters.AddWithValue("?VALOR_VERMELHA", objTarifaAntiga.VALOR_VERMELHA);
                    cmd.Parameters.AddWithValue("?VALOR_BASE_ECONOMICA", objTarifaAntiga.VALOR_BASE_ECONOMICA);
                    cmd.Parameters.AddWithValue("?VALOR_SEM_SUBSIDIO", objTarifaAntiga.VALOR_SEM_SUBSIDIO);
                    cmd.Parameters.AddWithValue("?VALOR_TUSD_FIO_B", objTarifaAntiga.VALOR_TUSD_FIO_B);


                    objGerenciadorTransacao.Execute(cmd);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public void Update(entidadeTarifaAntiga objTarifaAntiga)
        {
            MySqlCommand cmd = null;

            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM        = ?NUM ,  ";
                    vFILTER = vFILTER + "DESCRI     = ?DESCRI,  ";
                    vFILTER = vFILTER + "LIMITKWH   = ?LIMITKWH,  ";
                    vFILTER = vFILTER + "DATANOVA   = ?DATANOVA ,  ";
                    vFILTER = vFILTER + "REFERENCIA = ?REFERENCIA,  ";
                    vFILTER = vFILTER + "VALOR      = ?VALOR  ";

                    vAND = vAND + "AND NUM = ?NUM_OLD  ";
                    vAND = vAND + "AND REFERENCIA = ?REFERENCIA_OLD  ";


                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objTarifaAntiga.DESCRI.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIMITKWH", objTarifaAntiga.LIMITKWH.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DATANOVA", objTarifaAntiga.DATANOVA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);
                    cmd.Parameters.AddWithValue("?VALOR", objTarifaAntiga.VALOR);
                    cmd.Parameters.AddWithValue("?REFERENCIA_OLD", Convert.ToDateTime(objTarifaAntiga.UN));
                    cmd.Parameters.AddWithValue("?NUM_OLD", objTarifaAntiga.NUM_OITO.ToString().ToUpper());

                    objGerenciadorTransacao.Execute(cmd);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public void Update_1(entidadeTarifaAntiga objTarifaAntiga)
        {
            MySqlCommand cmd = null;

            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM = ?NUM, ";
                    vFILTER = vFILTER + "DESCRI = ?DESCRI, ";
                    vFILTER = vFILTER + "UN = NULL, ";
                    vFILTER = vFILTER + "LIMITKWH = ?LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR = ?VALOR, ";
                    vFILTER = vFILTER + "DATANOVA = ?DATANOVA, ";
                    vFILTER = vFILTER + "REFERENCIA = ?REFERENCIA, ";
                    vFILTER = vFILTER + "FLGCASC = NULL, ";
                    vFILTER = vFILTER + "VALOR_AMARELA = ?VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA = ?VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA = ?VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO = ?VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B = ?VALOR_TUSD_FIO_B ";

                    vAND = vAND + "AND NUM = ?NUM ";
                    vAND = vAND + "AND REFERENCIA = ?REFERENCIA  ";

                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objTarifaAntiga.DESCRI.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?UN", objTarifaAntiga.UN.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIMITKWH", objTarifaAntiga.LIMITKWH.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?VALOR", objTarifaAntiga.VALOR);
                    cmd.Parameters.AddWithValue("?DATANOVA", objTarifaAntiga.DATANOVA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);
                    cmd.Parameters.AddWithValue("?FLGCASC", objTarifaAntiga.FLGCASC.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?VALOR_AMARELA", objTarifaAntiga.VALOR_AMARELA);
                    cmd.Parameters.AddWithValue("?VALOR_VERMELHA", objTarifaAntiga.VALOR_VERMELHA);
                    cmd.Parameters.AddWithValue("?VALOR_BASE_ECONOMICA", objTarifaAntiga.VALOR_BASE_ECONOMICA);
                    cmd.Parameters.AddWithValue("?VALOR_SEM_SUBSIDIO", objTarifaAntiga.VALOR_SEM_SUBSIDIO);
                    cmd.Parameters.AddWithValue("?VALOR_TUSD_FIO_B", objTarifaAntiga.VALOR_TUSD_FIO_B);

                    objGerenciadorTransacao.Execute(cmd);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {
                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY REFERENCIA DESC, NUM ASC LIMIT 0,2";

                    if (objTarifaAntiga.NUM != string.Empty)
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND LEFT(NUM, 8)  = LEFT(?NUM, 8) "; }
                        else
                        { vAND = "AND LEFT(NUM, 8)  = LEFT(?NUM, 8) "; }

                    }
                    else
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + " "; }
                        else
                        { vAND = " "; }
                    }

                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND REFERENCIA <> '1951-01-01' AND REFERENCIA <= ?REFERENCIA "; }
                        else
                        { vAND = "AND REFERENCIA <> '1951-01-01' AND REFERENCIA <= ?REFERENCIA "; }

                    }
                    else
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + " "; }
                        else
                        { vAND = " "; }
                    }



                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objTarifaAntiga.NUM != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM.ToString().ToUpper());
                    }

                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);
                    }

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select_1(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY REFERENCIA DESC LIMIT 0, 2 ";


                    if (vAND != String.Empty)
                    { vAND = vAND + "AND NOT ISNULL(DATANOVA) "; }
                    else
                    { vAND = "AND NOT ISNULL(DATANOVA) "; }



                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND REFERENCIA <> '1951-01-01' AND REFERENCIA <= ?REFERENCIA "; }
                        else
                        { vAND = "AND REFERENCIA <> '1951-01-01' AND REFERENCIA <= ?REFERENCIA "; }

                    }
                    else
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + " "; }
                        else
                        { vAND = " "; }
                    }



                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);
                    }

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select_2(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY NUM ASC";

                    if (objTarifaAntiga.NUM != string.Empty)
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND MID(NUM, 1, ?NUM_COUNT ) = ?NUM_PART  AND NUM <> ?NUM "; }
                        else
                        { vAND = "AND MID(NUM, 1, ?NUM_COUNT ) = ?NUM_PART  AND NUM <> ?NUM "; }

                    }
                    else
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + " "; }
                        else
                        { vAND = " "; }
                    }

                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND REFERENCIA = ?REFERENCIA "; }
                        else
                        { vAND = "AND REFERENCIA = ?REFERENCIA "; }

                    }
                    else
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + " "; }
                        else
                        { vAND = " "; }
                    }



                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objTarifaAntiga.NUM != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?NUM_COUNT", Convert.ToInt32(objTarifaAntiga.NUM_OITO.ToString().ToUpper()));
                        cmd.Parameters.AddWithValue("?NUM_PART", objTarifaAntiga.LIMITKWH.ToString().ToUpper());
                        cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM.ToString().ToUpper());
                    }

                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);
                    }

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select_3(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";


                    vORDERBY = "ORDER BY NUM ASC";



                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND REFERENCIA = ?REFERENCIA "; }
                        else
                        { vAND = "AND REFERENCIA = ?REFERENCIA "; }

                    }
                    else
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + " "; }
                        else
                        { vAND = " "; }
                    }



                    cmd = new MySqlCommand(this.QuerySelect);


                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);
                    }

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select_4(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY `REFERENCIA` DESC LIMIT 0, 1 ";


                    if (vAND != String.Empty)
                    { vAND = vAND + "AND NUM = ?NUM "; }
                    else
                    { vAND = "AND NUM = ?NUM "; }



                    cmd = new MySqlCommand(this.QuerySelect);


                    cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM);


                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select_5(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    vORDERBY = vORDERBY + " ORDER BY REFERENCIA DESC, NUM ASC ";


                    vAND = vAND + "   AND LEFT(TARIFA.NUM, 8) = ?NUM ";
                    vAND = vAND + "   AND TARIFA.REFERENCIA <> '1951-01-01' ";
                    vAND = vAND + "   AND TARIFA.REFERENCIA <= ?REFERENCIA ";



                    cmd = new MySqlCommand(this.QuerySelect);


                    cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM);
                    cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);


                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select_6(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    vORDERBY = vORDERBY + "ORDER BY STR_TO_DATE(DATANOVA,'%d/%m/%Y') DESC, NUM ASC   ";

                    vAND = vAND + objTarifaAntiga.DESCRI.ToString().ToUpper();
                    vAND = vAND + "   AND STR_TO_DATE(DATANOVA,'%d/%m/%Y%') <> '1951-01-01' ";
                    vAND = vAND + "   AND DATE_FORMAT( DATANOVA, '%d/%m/%Y' ) =  ?DATANOVA ";
                    ;



                    cmd = new MySqlCommand(this.QuerySelect);


                    cmd.Parameters.AddWithValue("?DATANOVA", objTarifaAntiga.DATANOVA);



                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable Select_7(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    //vORDERBY = vORDERBY + "ORDER BY STR_TO_DATE(REFERENCIA,'%d/%m/%Y') DESC, NUM ASC   ";

                    vORDERBY = vORDERBY + "ORDER BY REFERENCIA DESC, NUM ASC   ";

                    // vAND = vAND + objTarifaAntiga.NUM.ToString().ToUpper();
                    //vAND = vAND + "   AND STR_TO_DATE(REFERENCIA,'%d/%m/%Y%') <> '1951-01-01' ";
                    //vAND = vAND + "   AND DATE_FORMAT( REFERENCIA, '%d/%m/%Y' ) =  ?REFERENCIA ";
                    //;

                    vAND = vAND + objTarifaAntiga.NUM.ToString().ToUpper();
                    vAND = vAND + "   AND REFERENCIA <> '1951-01-01' ";
                    vAND = vAND + "   AND REFERENCIA <=  ?REFERENCIA ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable SelectOthersTables(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    vORDERBY = "GROUP BY NUM ASC";


                    if (vAND != String.Empty)
                    { vAND = vAND + "AND MID(NUM, -4, 4) = '0000'  AND NUM NOT IN( '0100000000','01B1000000','01B2000000','01B3000000','01B4000000','01BU000000') "; }
                    else
                    { vAND = "AND MID(NUM, -4, 4) = '0000'  AND NUM NOT IN( '0100000000','01B1000000','01B2000000','01B3000000','01B4000000','01BU000000') "; }


                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable SelectOthersTables_1(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    vORDERBY = "GROUP BY REFERENCIA DESC";


                    if (objTarifaAntiga.NUM != string.Empty)
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND LEFT(NUM, 4) = ?NUM  AND RIGHT(NUM, 6) <> '000000'  AND REFERENCIA <> '1951-01-01' "; }
                        else
                        { vAND = "AND LEFT(NUM, 4) = ?NUM  AND RIGHT(NUM, 6) <> '000000'  AND REFERENCIA <> '1951-01-01' "; }

                    }
                    else
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + " "; }
                        else
                        { vAND = " "; }
                    }

                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objTarifaAntiga.NUM != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM.ToString().ToUpper());
                    }

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable SelectOthersTables_2(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {


                    vFILTER = vFILTER + "DISTINCT REFERENCIA ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY REFERENCIA DESC ";


                    if (vAND != String.Empty)
                    { vAND = vAND + "AND REFERENCIA <> '01/01/1951' "; }
                    else
                    { vAND = "AND REFERENCIA <> '01/01/1951' "; }


                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable SelectOthersTables_3(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "NUM, ";
                    vFILTER = vFILTER + "DESCRI, ";
                    vFILTER = vFILTER + "UN, ";
                    vFILTER = vFILTER + "LIMITKWH, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "DATANOVA, ";
                    vFILTER = vFILTER + "FLGCASC, ";
                    vFILTER = vFILTER + "VALOR_AMARELA, ";
                    vFILTER = vFILTER + "VALOR_VERMELHA, ";
                    vFILTER = vFILTER + "VALOR_BASE_ECONOMICA, ";
                    vFILTER = vFILTER + "VALOR_SEM_SUBSIDIO, ";
                    vFILTER = vFILTER + "VALOR_TUSD_FIO_B, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "NUM_OITO  ";

                    vINNERJOIN = " ";

                    vORDERBY = "GROUP BY NUM ASC";


                    if (vAND != String.Empty)
                    { vAND = vAND + " AND NUM NOT IN( '0100000000','01B1000000','01B2000000','01B3000000','01B4000000','01BU000000') "; }
                    else
                    { vAND = " AND NUM NOT IN( '0100000000','01B1000000','01B2000000','01B3000000','01B4000000','01BU000000') "; }


                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable SelectCount(entidadeTarifaAntiga objTarifaAntiga)
        {
            DataTable dt = null;
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {

                    vFILTER = vFILTER + "COUNT(*) ";

                    vINNERJOIN = " ";

                    vORDERBY = "  ";

                    if (objTarifaAntiga.NUM != string.Empty)
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND NUM =  ?NUM "; }
                        else
                        { vAND = "AND NUM =  ?NUM "; }

                    }
                    else
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + " "; }
                        else
                        { vAND = " "; }
                    }

                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND REFERENCIA = ?REFERENCIA "; }
                        else
                        { vAND = "AND REFERENCIA = ?REFERENCIA "; }

                    }
                    else
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + " "; }
                        else
                        { vAND = " "; }
                    }

                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objTarifaAntiga.NUM != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?NUM", objTarifaAntiga.NUM.ToString().ToUpper());
                    }

                    if (objTarifaAntiga.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaAntiga.REFERENCIA);
                    }


                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        #endregion

        #region " Comandos SQL "

        StringBuilder sb = new StringBuilder();

        protected string QueryInsert
        {
            get
            {
                this.sb.Clear();

                this.sb.AppendLine("INSERT INTO  ");
                this.sb.AppendLine("TARIFA_ANTIGA SET  ");
                this.sb.AppendLine(vFILTER);

                return sb.ToString();
            }
        }

        protected string QueryUpdate
        {
            get
            {
                this.sb.Clear();

                this.sb.AppendLine("UPDATE  ");
                this.sb.AppendLine("TARIFA_ANTIGA SET  ");
                this.sb.AppendLine(vFILTER);
                this.sb.AppendLine("WHERE 1 = 1 ");
                this.sb.AppendLine(vAND);

                return sb.ToString();
            }
        }

        protected string QueryDelete
        {
            get
            {
                this.sb.Clear();

                this.sb.AppendLine("DELETE FROM TARIFA_ANTIGA ");
                this.sb.AppendLine("WHERE 1 = 1 ");
                this.sb.AppendLine(vAND);
                this.sb.AppendLine(vORDERBY);

                return sb.ToString();
            }
        }

        protected string QuerySelect
        {
            get
            {
                this.sb.Clear();

                this.sb.AppendLine("SELECT ");
                this.sb.AppendLine(vFILTER);
                this.sb.AppendLine("FROM ");
                this.sb.AppendLine("TARIFA_ANTIGA  ");
                this.sb.AppendLine(vINNERJOIN);
                this.sb.AppendLine("WHERE 1 = 1  ");
                this.sb.AppendLine(vAND);
                this.sb.AppendLine(vORDERBY);

                return sb.ToString();
            }
        }

        #endregion

    }
}
