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 dalCodigo
    {

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

        #region Métodos

        public void Insert(entidadeCodigo objCodigo)
        {
            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 + "VALOR        = ?VALOR , ";
                    vFILTER = vFILTER + "DATANOVA        = ?DATANOVA , ";
                    vFILTER = vFILTER + "FLGCONV115        = ?FLGCONV115 , ";
                    vFILTER = vFILTER + "FLGCASC         = ?FLGCASC  ";
                    //vFILTER = vFILTER + "CTA_CRED_REC_ASS         = ?CTA_CRED_REC_ASS,  ";
                    //vFILTER = vFILTER + "CTA_CRED_REC_NASS         = ?CTA_CRED_REC_NASS,  ";
                    //vFILTER = vFILTER + "CTA_CRE_ARRE         = ?CTA_CRE_ARRE,  ";
                    //vFILTER = vFILTER + "CTA_DEB_ARRE         = ?CTA_DEB_ARRE, ";
                    //vFILTER = vFILTER + "CTA_DEB_FAT         = ?CTA_DEB_FAT,  ";

                    //vFILTER = vFILTER + "ID_CTA_CRED_REC_ASS         = ?ID_CTA_CRED_REC_ASS , ";
                    //vFILTER = vFILTER + "ID_CTA_CRED_REC_NASS         = ?ID_CTA_CRED_REC_NASS , ";
                    //vFILTER = vFILTER + "ID_CTA_CRE_ARRE         = ?ID_CTA_CRE_ARRE , ";
                    //vFILTER = vFILTER + "ID_CTA_DEB_ARRE         = ?ID_CTA_DEB_ARRE , ";
                    //vFILTER = vFILTER + "ID_CTA_DEB_FAT         = ?ID_CTA_DEB_FAT  ";

                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objCodigo.DESCRI.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?VALOR", objCodigo.VALOR.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DATANOVA", objCodigo.DATANOVA);
                    cmd.Parameters.AddWithValue("?FLGCONV115", objCodigo.FLGCONV115.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?FLGCASC", objCodigo.FLGCASC.ToString().ToUpper());
                    //cmd.Parameters.AddWithValue("?CTA_CRED_REC_ASS", objCodigo.CTA_CRED_REC_ASS.ToString().ToUpper());
                    //cmd.Parameters.AddWithValue("?CTA_CRED_REC_NASS", objCodigo.CTA_CRED_REC_NASS.ToString().ToUpper());
                    //cmd.Parameters.AddWithValue("?CTA_CRE_ARRE", objCodigo.CTA_CRE_ARRE.ToString().ToUpper());
                    //cmd.Parameters.AddWithValue("?CTA_DEB_ARRE", objCodigo.CTA_DEB_ARRE.ToString().ToUpper());
                    //cmd.Parameters.AddWithValue("?CTA_DEB_FAT", objCodigo.CTA_DEB_FAT.ToString().ToUpper());

                    //cmd.Parameters.AddWithValue("?ID_CTA_CRED_REC_ASS", objCodigo.ID_CTA_CRED_REC_ASS);
                    //cmd.Parameters.AddWithValue("?ID_CTA_CRED_REC_NASS", objCodigo.ID_CTA_CRED_REC_NASS);
                    //cmd.Parameters.AddWithValue("?ID_CTA_CRE_ARRE", objCodigo.ID_CTA_CRE_ARRE);
                    //cmd.Parameters.AddWithValue("?ID_CTA_DEB_ARRE", objCodigo.ID_CTA_DEB_ARRE);
                    //cmd.Parameters.AddWithValue("?ID_CTA_DEB_FAT", objCodigo.ID_CTA_DEB_FAT);

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

        public void Insert_1(entidadeCodigo objCodigo)
        {
            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 ";

                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objCodigo.DESCRI.ToString().ToUpper());


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

        public void Insert_2(entidadeCodigo objCodigo)
        {
            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        = ?UN , ";
                    vFILTER = vFILTER + "VALOR        = ?VALOR , ";
                    vFILTER = vFILTER + "DATANOVA        = ?DATANOVA  ";


                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objCodigo.DESCRI.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?UN", objCodigo.UN.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?VALOR", objCodigo.VALOR.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DATANOVA", objCodigo.DATANOVA);


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

        public void Update(entidadeCodigo objCodigo)
        {
            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 + "VALOR        = ?VALOR , ";
                    vFILTER = vFILTER + "DATANOVA        = ?DATANOVA , ";
                    vFILTER = vFILTER + "FLGCONV115        = ?FLGCONV115 , ";
                    vFILTER = vFILTER + "FLGCASC         = ?FLGCASC ,  ";
                    vFILTER = vFILTER + "CTA_CRED_REC_ASS         = ?CTA_CRED_REC_ASS , ";
                    vFILTER = vFILTER + "CTA_CRED_REC_NASS         = ?CTA_CRED_REC_NASS , ";
                    vFILTER = vFILTER + "CTA_CRE_ARRE         = ?CTA_CRE_ARRE , ";
                    vFILTER = vFILTER + "CTA_DEB_ARRE         = ?CTA_DEB_ARRE , ";
                    vFILTER = vFILTER + "CTA_DEB_FAT         = ?CTA_DEB_FAT,  ";

                    vFILTER = vFILTER + "ID_CTA_CRED_REC_ASS         = ?ID_CTA_CRED_REC_ASS , ";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_NASS         = ?ID_CTA_CRED_REC_NASS , ";
                    vFILTER = vFILTER + "ID_CTA_CRE_ARRE         = ?ID_CTA_CRE_ARRE , ";
                    vFILTER = vFILTER + "ID_CTA_DEB_ARRE         = ?ID_CTA_DEB_ARRE , ";
                    vFILTER = vFILTER + "ID_CTA_DEB_FAT         = ?ID_CTA_DEB_FAT  ";

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

                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objCodigo.DESCRI.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?VALOR", objCodigo.VALOR.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DATANOVA", objCodigo.DATANOVA);
                    cmd.Parameters.AddWithValue("?FLGCONV115", objCodigo.FLGCONV115.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?FLGCASC", objCodigo.FLGCASC.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CTA_CRED_REC_ASS", objCodigo.CTA_CRED_REC_ASS.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CTA_CRED_REC_NASS", objCodigo.CTA_CRED_REC_NASS.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CTA_CRE_ARRE", objCodigo.CTA_CRE_ARRE.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CTA_DEB_ARRE", objCodigo.CTA_DEB_ARRE.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CTA_DEB_FAT", objCodigo.CTA_DEB_FAT.ToString().ToUpper());

                    cmd.Parameters.AddWithValue("?ID_CTA_CRED_REC_ASS", objCodigo.ID_CTA_CRED_REC_ASS);
                    cmd.Parameters.AddWithValue("?ID_CTA_CRED_REC_NASS", objCodigo.ID_CTA_CRED_REC_NASS);
                    cmd.Parameters.AddWithValue("?ID_CTA_CRE_ARRE", objCodigo.ID_CTA_CRE_ARRE);
                    cmd.Parameters.AddWithValue("?ID_CTA_DEB_ARRE", objCodigo.ID_CTA_DEB_ARRE);
                    cmd.Parameters.AddWithValue("?ID_CTA_DEB_FAT", objCodigo.ID_CTA_DEB_FAT);

                    cmd.Parameters.AddWithValue("?NUMOLD", objCodigo.SITUACAO.ToString().ToUpper());

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

        public void Update_1(entidadeCodigo objCodigo)
        {
            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 ";

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

                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objCodigo.DESCRI.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUMOLD", objCodigo.SITUACAO.ToString().ToUpper());

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

        public void Update_2(entidadeCodigo objCodigo)
        {
            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 + "DESCRI    = ?DESCRI ,";
                    vFILTER = vFILTER + "UN        = ?UN,";
                    vFILTER = vFILTER + "VALOR     = ?VALOR,";
                    vFILTER = vFILTER + "DATANOVA  = ?DATANOVA ";

                    vAND = vAND + "AND NUM = ?NUM ";
                    vAND = vAND + "AND DATANOVA = ?DATANOVA_OLD ";

                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DESCRI", objCodigo.DESCRI.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?UN", objCodigo.UN.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?VALOR", objCodigo.VALOR.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?DATANOVA", objCodigo.DATANOVA);
                    cmd.Parameters.AddWithValue("?DATANOVA_OLD", Convert.ToDateTime(objCodigo.SITUACAO));

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

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

            string vNumMaior = 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 + "FLGPISCOF,";
                    vFILTER = vFILTER + "FLGCONV115,";
                    vFILTER = vFILTER + "CLASCONV115,";
                    vFILTER = vFILTER + "SITUACAO,";
                    vFILTER = vFILTER + "FLGSITUC,";
                    vFILTER = vFILTER + "FLGPROC,";
                    vFILTER = vFILTER + "FLGMEDIDOR,";
                    vFILTER = vFILTER + "FLG_MEDIDORES,";
                    vFILTER = vFILTER + "FLG_RAMAL,";
                    vFILTER = vFILTER + "FLG_VERIFICAMEDIDOR,";
                    vFILTER = vFILTER + "CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_FAT,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "ID_CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_FAT";
                    //vFILTER = vFILTER + "CCLASS,";
                    //vFILTER = vFILTER + "CPROD";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    if (objCodigo.NUM != string.Empty)
                    {
                        vNumMaior = objCodigo.NUM + "000000";

                        if (objCodigo.DESCRI == "S")
                        { vAND = "AND LEFT(NUM, 4) = ?NUM AND NUM <> ?NUMMAIOR ORDER BY DESCRI, NUM "; }
                        else
                        { vAND = "AND LEFT(NUM, 4) = ?NUM AND NUM <> ?NUMMAIOR ORDER BY NUM "; }

                    }
                    else
                    {
                        vAND = " ";
                    }


                    cmd = new MySqlCommand(this.QuerySelect);

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

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_1(entidadeCodigo objCodigo)
        {
            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 + "FLGPISCOF,";
                    vFILTER = vFILTER + "FLGCONV115,";
                    vFILTER = vFILTER + "CLASCONV115,";
                    vFILTER = vFILTER + "SITUACAO,";
                    vFILTER = vFILTER + "FLGSITUC,";
                    vFILTER = vFILTER + "FLGPROC,";
                    vFILTER = vFILTER + "FLGMEDIDOR,";
                    vFILTER = vFILTER + "FLG_MEDIDORES,";
                    vFILTER = vFILTER + "FLG_RAMAL,";
                    vFILTER = vFILTER + "FLG_VERIFICAMEDIDOR,";
                    vFILTER = vFILTER + "CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_FAT,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "ID_CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_FAT,";
                    vFILTER = vFILTER + "CODCLASS,";
                    vFILTER = vFILTER + "CODPROD";



                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    if (objCodigo.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 = " "; }
                    }


                    cmd = new MySqlCommand(this.QuerySelect);

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

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_2(entidadeCodigo objCodigo)
        {
            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 + "FLGPISCOF,";
                    vFILTER = vFILTER + "FLGCONV115,";
                    vFILTER = vFILTER + "CLASCONV115,";
                    vFILTER = vFILTER + "SITUACAO,";
                    vFILTER = vFILTER + "FLGSITUC,";
                    vFILTER = vFILTER + "FLGPROC,";
                    vFILTER = vFILTER + "FLGMEDIDOR,";
                    vFILTER = vFILTER + "FLG_MEDIDORES,";
                    vFILTER = vFILTER + "FLG_RAMAL,";
                    vFILTER = vFILTER + "FLG_VERIFICAMEDIDOR,";
                    vFILTER = vFILTER + "CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_FAT,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "ID_CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_FAT,";
                    vFILTER = vFILTER + "CODCLASS,";
                    vFILTER = vFILTER + "CODPROD";

                    vINNERJOIN = " ";


                    vORDERBY = "ORDER BY DATANOVA DESC, NUM ASC";


                    if (vAND != String.Empty)
                    { vAND = vAND + "AND MID(NUM, 1, 5) = '40PIS' OR MID(NUM, 1, 5) = '40COF'"; }
                    else
                    { vAND = "AND MID(NUM, 1, 5) = '40PIS' OR MID(NUM, 1, 5) = '40COF' "; }



                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_3(entidadeCodigo objCodigo)
        {
            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 + "FLGPISCOF,";
                    vFILTER = vFILTER + "FLGCONV115,";
                    vFILTER = vFILTER + "CLASCONV115,";
                    vFILTER = vFILTER + "SITUACAO,";
                    vFILTER = vFILTER + "FLGSITUC,";
                    vFILTER = vFILTER + "FLGPROC,";
                    vFILTER = vFILTER + "FLGMEDIDOR,";
                    vFILTER = vFILTER + "FLG_MEDIDORES,";
                    vFILTER = vFILTER + "FLG_RAMAL,";
                    vFILTER = vFILTER + "FLG_VERIFICAMEDIDOR,";
                    vFILTER = vFILTER + "CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_FAT,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "ID_CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_FAT,";
                    vFILTER = vFILTER + "CODCLASS,";
                    vFILTER = vFILTER + "CODPROD";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = "AND NUM = ?NUM ";
                    
                    cmd = new MySqlCommand(this.QuerySelect);

                    //cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUM", Convert.ToInt64(objCodigo.NUM.ToString().ToUpper()));

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                  
                }

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

        public DataTable Select_4(entidadeCodigo objCodigo)
        {
            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 + "FLGPISCOF,";
                    vFILTER = vFILTER + "FLGCONV115,";
                    vFILTER = vFILTER + "CLASCONV115,";
                    vFILTER = vFILTER + "SITUACAO,";
                    vFILTER = vFILTER + "FLGSITUC,";
                    vFILTER = vFILTER + "FLGPROC,";
                    vFILTER = vFILTER + "FLGMEDIDOR,";
                    vFILTER = vFILTER + "FLG_MEDIDORES,";
                    vFILTER = vFILTER + "FLG_RAMAL,";
                    vFILTER = vFILTER + "FLG_VERIFICAMEDIDOR,";
                    vFILTER = vFILTER + "CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_FAT,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "ID_CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_FAT,";
                    vFILTER = vFILTER + "CODCLASS,";
                    vFILTER = vFILTER + "CODPROD";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = "AND NUM = ?NUM ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    //cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUM", Convert.ToInt64(objCodigo.NUM.ToString().ToUpper()));

                    dt = objGerenciadorTransacao.GetDataTable(cmd);

                }

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

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

            try
            {
                objconexaoBD = new ConexaoBD();

                //objconexaoBD.SetarConexaoBanco(TipoBDConexao.fatwin_BD); //Fatwin nao tem as infos.
                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 + "FLGPISCOF,";
                    vFILTER = vFILTER + "FLGCONV115,";
                    vFILTER = vFILTER + "CLASCONV115,";
                    vFILTER = vFILTER + "SITUACAO,";
                    vFILTER = vFILTER + "FLGSITUC,";
                    vFILTER = vFILTER + "FLGPROC,";
                    vFILTER = vFILTER + "FLGMEDIDOR,";
                    vFILTER = vFILTER + "FLG_MEDIDORES,";
                    vFILTER = vFILTER + "FLG_RAMAL,";
                    vFILTER = vFILTER + "FLG_VERIFICAMEDIDOR,";
                    vFILTER = vFILTER + "CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "CTA_DEB_FAT,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_ASS,";
                    vFILTER = vFILTER + "ID_CTA_CRED_REC_NASS,";
                    vFILTER = vFILTER + "ID_CTA_CRE_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_ARRE,";
                    vFILTER = vFILTER + "ID_CTA_DEB_FAT,";
                    vFILTER = vFILTER + "CODCLASS,";
                    vFILTER = vFILTER + "CODPROD";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = "AND NUM = ?NUM ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    //cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUM", Convert.ToInt64(objCodigo.NUM.ToString().ToUpper()));

                    dt = objGerenciadorTransacao.GetDataTable(cmd);

                }

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }
        public void Delete(entidadeCodigo objCodigo)
        {
            MySqlCommand cmd = null;

            IGerenciadorTransacao objGerenciadorTransacao = null;

            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {
                    vORDERBY = " ";

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

                    cmd = new MySqlCommand(this.QueryDelete);

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

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

        public void Delete_1(entidadeCodigo objCodigo)
        {
            MySqlCommand cmd = null;

            IGerenciadorTransacao objGerenciadorTransacao = null;

            ConexaoBD objconexaoBD = null;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {
                    vORDERBY = " ";

                    if (objCodigo.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 (objCodigo.DATANOVA != Convert.ToDateTime("01/01/0001"))
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND DATANOVA = ?DATANOVA "; }
                        else
                        { vAND = "AND DATANOVA = ?DATANOVA "; }

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


                    cmd = new MySqlCommand(this.QueryDelete);

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

                    if (objCodigo.DATANOVA != Convert.ToDateTime("01/01/0001"))
                    {
                        cmd.Parameters.AddWithValue("?DATANOVA", objCodigo.DATANOVA);
                    }
                    objGerenciadorTransacao.Execute(cmd);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public DataTable SelectMax(entidadeCodigo objCodigo)
        {
            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 + "RIGHT(MAX(NUM), 6) NUMNOVO ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = vAND + "AND LEFT(NUM, 4) = ?NUM ";

                    cmd = new MySqlCommand(this.QuerySelect);


                    cmd.Parameters.AddWithValue("?NUM", objCodigo.NUM.ToString().ToUpper());

                    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("CODIGOS SET  ");
                this.sb.AppendLine(vFILTER);

                return sb.ToString();
            }
        }

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

                this.sb.AppendLine("UPDATE  ");
                this.sb.AppendLine("CODIGOS 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 CODIGOS ");
                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("CODIGOS  ");
                this.sb.AppendLine(vINNERJOIN);
                this.sb.AppendLine("WHERE 1 = 1  ");
                this.sb.AppendLine(vAND);
                this.sb.AppendLine(vORDERBY);

                return sb.ToString();
            }
        }

        #endregion


    }
}
