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


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

        #region Métodos

        public DataTable Select(entidadeMultaJuros objMultaJuros)
        {
            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 + "CODIGO , ";
                    vFILTER = vFILTER + "ZONA , ";
                    vFILTER = vFILTER + "LIVRO , ";
                    vFILTER = vFILTER + "CONTA , ";
                    vFILTER = vFILTER + "NUMNF , ";
                    vFILTER = vFILTER + "MESANO , ";
                    vFILTER = vFILTER + "DIASATRASO , ";
                    vFILTER = vFILTER + "VLRMULTA , ";
                    vFILTER = vFILTER + "VLRJUROS , ";
                    vFILTER = vFILTER + "VLRCM , ";
                    vFILTER = vFILTER + "COBRADO , ";
                    vFILTER = vFILTER + "ISENTO , ";
                    vFILTER = vFILTER + "USUARIO , ";
                    vFILTER = vFILTER + "DAT_PROCESSO , ";
                    vFILTER = vFILTER + "MESANO_FATUR , ";
                    vFILTER = vFILTER + "CONS_ID , ";
                    vFILTER = vFILTER + "UNID_ID ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";


                    vAND = vAND + " AND ZONA = ?ZONA  ";
                    vAND = vAND + " AND LIVRO = ?LIVRO ";
                    vAND = vAND + " AND CONTA = ?CONTA ";
                    vAND = vAND + " AND COBRADO = ?COBRADO ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?ZONA", objMultaJuros.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objMultaJuros.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objMultaJuros.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?COBRADO", objMultaJuros.COBRADO.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_1(entidadeMultaJuros objMultaJuros)
        {
            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 + "CODIGO , ";
                    vFILTER = vFILTER + "ZONA , ";
                    vFILTER = vFILTER + "LIVRO , ";
                    vFILTER = vFILTER + "CONTA , ";
                    vFILTER = vFILTER + "NUMNF , ";
                    vFILTER = vFILTER + "MESANO , ";
                    vFILTER = vFILTER + "DIASATRASO , ";
                    vFILTER = vFILTER + "VLRMULTA , ";
                    vFILTER = vFILTER + "VLRJUROS , ";
                    vFILTER = vFILTER + "VLRCM , ";
                    vFILTER = vFILTER + "COBRADO , ";
                    vFILTER = vFILTER + "ISENTO , ";
                    vFILTER = vFILTER + "USUARIO , ";
                    vFILTER = vFILTER + "DAT_PROCESSO , ";
                    vFILTER = vFILTER + "MESANO_FATUR , ";
                    vFILTER = vFILTER + "CONS_ID , ";
                    vFILTER = vFILTER + "UNID_ID ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";


                    vAND = vAND + " AND ZONA = ?ZONA  ";
                    vAND = vAND + " AND LIVRO = ?LIVRO ";
                    vAND = vAND + " AND CONTA = ?CONTA ";
                    vAND = vAND + " AND CONS_ID = ?CONS_ID ";
                    vAND = vAND + " AND UNID_ID = ?UNID_ID ";
                    vAND = vAND + " AND COBRADO = ?COBRADO ";
                    vAND = vAND + " AND MESANO_FATUR = ?MESANO_FATUR ";
                    vAND = vAND + " AND ISENTO = ?ISENTO ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?ZONA", objMultaJuros.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objMultaJuros.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objMultaJuros.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONS_ID", objMultaJuros.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objMultaJuros.UNID_ID);
                    cmd.Parameters.AddWithValue("?COBRADO", objMultaJuros.COBRADO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MESANO_FATUR", objMultaJuros.MESANO_FATUR);
                    cmd.Parameters.AddWithValue("?ISENTO", objMultaJuros.ISENTO);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectCount(entidadeMultaJuros objMultaJuros)
        {
            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(*) AS COUNT ";

                    vINNERJOIN = vINNERJOIN + " ";

                    vORDERBY = " ";

                    vAND = vAND + "AND ZONA = ?ZONA  ";
                    vAND = vAND + "AND LIVRO = ?LIVRO ";
                    vAND = vAND + "AND CONTA = ?CONTA ";
                    vAND = vAND + "AND NUMNF = ?NUMNF ";
                    vAND = vAND + "AND MESANO = ?MESANO ";
                    vAND = vAND + "AND ISENTO = ?ISENTO ";


                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?ZONA", objMultaJuros.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objMultaJuros.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objMultaJuros.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUMNF", objMultaJuros.NUMNF);
                    cmd.Parameters.AddWithValue("?MESANO", objMultaJuros.MESANO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?ISENTO", objMultaJuros.ISENTO);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectCount_1(entidadeMultaJuros objMultaJuros)
        {
            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(*) AS COUNT ";

                    vINNERJOIN = vINNERJOIN + " ";

                    vORDERBY = " ";

                    vAND = vAND + "AND ZONA = ?ZONA  ";
                    vAND = vAND + "AND LIVRO = ?LIVRO ";
                    vAND = vAND + "AND CONTA = ?CONTA ";
                    vAND = vAND + "AND NUMNF = ?NUMNF ";
                    vAND = vAND + "AND MESANO = ?MESANO ";
                    vAND = vAND + "AND ISENTO = ?ISENTO ";
                    vAND = vAND + "AND CONS_ID = ?CONS_ID ";
                    vAND = vAND + "AND UNID_ID = ?UNID_ID ";


                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?ZONA", objMultaJuros.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objMultaJuros.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objMultaJuros.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUMNF", objMultaJuros.NUMNF);
                    cmd.Parameters.AddWithValue("?MESANO", objMultaJuros.MESANO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?ISENTO", objMultaJuros.ISENTO);
                    cmd.Parameters.AddWithValue("?CONS_ID", objMultaJuros.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objMultaJuros.UNID_ID);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public void Delete(entidadeMultaJuros objMultaJuros)
        {

            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 ZONA = ?ZONA  ";
                    vAND = vAND + "AND LIVRO = ?LIVRO ";
                    vAND = vAND + "AND CONTA = ?CONTA ";
                    vAND = vAND + "AND NUMNF = ?NUMNF ";
                    vAND = vAND + "AND MESANO = ?MESANO ";
                    vAND = vAND + "AND ISENTO = ?ISENTO ";

                    cmd = new MySqlCommand(this.QueryDelete);

                    cmd.Parameters.AddWithValue("?ZONA", objMultaJuros.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objMultaJuros.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objMultaJuros.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUMNF", objMultaJuros.NUMNF);
                    cmd.Parameters.AddWithValue("?MESANO", objMultaJuros.MESANO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?ISENTO", objMultaJuros.ISENTO);


                    objGerenciadorTransacao.Execute(cmd);
                }

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

        public void Delete_1(entidadeMultaJuros objMultaJuros)
        {

            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 ZONA = ?ZONA  ";
                    vAND = vAND + "AND LIVRO = ?LIVRO ";
                    vAND = vAND + "AND CONTA = ?CONTA ";
                    vAND = vAND + "AND NUMNF = ?NUMNF ";
                    vAND = vAND + "AND MESANO = ?MESANO ";
                    vAND = vAND + "AND ISENTO = ?ISENTO ";
                    vAND = vAND + "AND CONS_ID = ?CONS_ID ";
                    vAND = vAND + "AND UNID_ID = ?UNID_ID ";

                    cmd = new MySqlCommand(this.QueryDelete);

                    cmd.Parameters.AddWithValue("?ZONA", objMultaJuros.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objMultaJuros.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objMultaJuros.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NUMNF", objMultaJuros.NUMNF);
                    cmd.Parameters.AddWithValue("?MESANO", objMultaJuros.MESANO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?ISENTO", objMultaJuros.ISENTO);
                    cmd.Parameters.AddWithValue("?CONS_ID", objMultaJuros.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objMultaJuros.UNID_ID);


                    objGerenciadorTransacao.Execute(cmd);
                }

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

        public void Update(entidadeMultaJuros objMultaJuros)
        {

            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 + " COBRADO = ?COBRADO ";

                    vAND = vAND + "AND ZONA = ?ZONA  ";
                    vAND = vAND + "AND LIVRO = ?LIVRO ";
                    vAND = vAND + "AND CONTA = ?CONTA ";


                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?ZONA", objMultaJuros.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objMultaJuros.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objMultaJuros.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?COBRADO", objMultaJuros.COBRADO.ToString().ToUpper());

                    objGerenciadorTransacao.Execute(cmd);

                }

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

                return sb.ToString();
            }
        }

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

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

                return sb.ToString();
            }
        }

        #endregion



    }
}
