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 dalSobreTaxa
    {
        string vFILTER = null;
        string vINNERJOIN = null;
        string vAND = null;
        string vORDERBY = null;

        #region Métodos

        public DataTable Select(entidadeSobreTaxa objSobreTaxa)
        {
            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 + "ID, ";
                    vFILTER = vFILTER + "CONS_ID, ";
                    vFILTER = vFILTER + "UNID_ID, ";
                    vFILTER = vFILTER + "ZONA, ";
                    vFILTER = vFILTER + "LIVRO, ";
                    vFILTER = vFILTER + "CONTA, ";
                    vFILTER = vFILTER + "CODIGO, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "MESANO, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "FLAG  ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY CODIGO ASC  ";

                    vAND = vAND + "AND CODIGO BETWEEN 9070000030 AND 9070000039 ";
                    vAND = vAND + "AND REFERENCIA = ?REFERENCIA ";


                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?REFERENCIA", objSobreTaxa.REFERENCIA.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_1(entidadeSobreTaxa objSobreTaxa)
        {
            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 + "ID, ";
                    vFILTER = vFILTER + "CONS_ID, ";
                    vFILTER = vFILTER + "UNID_ID, ";
                    vFILTER = vFILTER + "ZONA, ";
                    vFILTER = vFILTER + "LIVRO, ";
                    vFILTER = vFILTER + "CONTA, ";
                    vFILTER = vFILTER + "CODIGO, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "MESANO, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "FLAG  ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = vAND + " AND  ZONA = ?ZONA ";
                    vAND = vAND + " AND LIVRO = ?LIVRO ";
                    vAND = vAND + " AND CONTA = ?CONTA ";
                    vAND = vAND + " AND((FLAG =  OR FLAG IS NULL) OR(FLAG ='P' AND MESANO = ?MESANO)) ";





                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?ZONA", objSobreTaxa.ZONA.ToString().ToUpper());

                    cmd.Parameters.AddWithValue("?LIVRO", objSobreTaxa.LIVRO.ToString().ToUpper());

                    cmd.Parameters.AddWithValue("?CONTA", objSobreTaxa.CONTA.ToString().ToUpper());

                    cmd.Parameters.AddWithValue("?MESANO", objSobreTaxa.MESANO.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_2(entidadeSobreTaxa objSobreTaxa)
        {
            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 + "ID, ";
                    vFILTER = vFILTER + "CONS_ID, ";
                    vFILTER = vFILTER + "UNID_ID, ";
                    vFILTER = vFILTER + "ZONA, ";
                    vFILTER = vFILTER + "LIVRO, ";
                    vFILTER = vFILTER + "CONTA, ";
                    vFILTER = vFILTER + "CODIGO, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "MESANO, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "FLAG  ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

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





                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?ZONA", objSobreTaxa.ZONA.ToString().ToUpper());

                    cmd.Parameters.AddWithValue("?LIVRO", objSobreTaxa.LIVRO.ToString().ToUpper());

                    cmd.Parameters.AddWithValue("?CONTA", objSobreTaxa.CONTA.ToString().ToUpper());

                    cmd.Parameters.AddWithValue("?MESANO", objSobreTaxa.MESANO.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_3(entidadeSobreTaxa objSobreTaxa)
        {
            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 + "ID, ";
                    vFILTER = vFILTER + "CONS_ID, ";
                    vFILTER = vFILTER + "UNID_ID, ";
                    vFILTER = vFILTER + "ZONA, ";
                    vFILTER = vFILTER + "LIVRO, ";
                    vFILTER = vFILTER + "CONTA, ";
                    vFILTER = vFILTER + "CODIGO, ";
                    vFILTER = vFILTER + "VALOR, ";
                    vFILTER = vFILTER + "MESANO, ";
                    vFILTER = vFILTER + "REFERENCIA, ";
                    vFILTER = vFILTER + "FLAG  ";

                    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 ((FLAG = '' OR FLAG IS NULL) OR (FLAG = ?FLAG AND MESANO = ?MESANO )) ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?ZONA", objSobreTaxa.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objSobreTaxa.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objSobreTaxa.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MESANO", objSobreTaxa.MESANO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONS_ID", objSobreTaxa.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objSobreTaxa.UNID_ID);
                    cmd.Parameters.AddWithValue("?FLAG", objSobreTaxa.FLAG.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public void Insert(entidadeSobreTaxa objSobreTaxa)
        {
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;
            int idCod = 0;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {
                    vFILTER = vFILTER + "           ZONA       = ?ZONA, ";
                    vFILTER = vFILTER + "           LIVRO      = ?LIVRO, ";
                    vFILTER = vFILTER + "           CONTA      = ?CONTA, ";
                    vFILTER = vFILTER + "           CONS_ID    = ?CONS_ID, ";
                    vFILTER = vFILTER + "           UNID_ID    = ?UNID_ID, ";
                    vFILTER = vFILTER + "           CODIGO     = ?CODIGO, ";
                    vFILTER = vFILTER + "           VALOR      = ?VALOR,  ";
                    vFILTER = vFILTER + "           REFERENCIA = ?REFERENCIA  ";

                    cmd = new MySqlCommand(this.QueryInsert);


                    cmd.Parameters.AddWithValue("?ZONA", objSobreTaxa.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objSobreTaxa.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objSobreTaxa.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONS_ID", objSobreTaxa.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objSobreTaxa.UNID_ID);
                    cmd.Parameters.AddWithValue("?CODIGO", objSobreTaxa.CODIGO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?VALOR", objSobreTaxa.VALOR);
                    cmd.Parameters.AddWithValue("?REFERENCIA", objSobreTaxa.REFERENCIA.ToString().ToUpper());

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

        public void Update(entidadeSobreTaxa objSobreTaxa)
        {
            MySqlCommand cmd = null;
            IGerenciadorTransacao objGerenciadorTransacao = null;
            ConexaoBD objconexaoBD = null;
            int idCod = 0;

            try
            {
                objconexaoBD = new ConexaoBD();

                objconexaoBD.SetarConexaoBanco(TipoBDConexao.fat_BD);

                using (objGerenciadorTransacao = FabricaGerenciadorTransacao.GetGerenciador(TipoBDGerenciador.MySql))
                {
                    vFILTER = vFILTER + "           FLAG       = ?FLAG ";


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

                    cmd = new MySqlCommand(this.QueryUpdate);


                    cmd.Parameters.AddWithValue("?ZONA", objSobreTaxa.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objSobreTaxa.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objSobreTaxa.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?MESANO", objSobreTaxa.MESANO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?FLAG", objSobreTaxa.FLAG.ToString().ToUpper());


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

        public void Update_1(entidadeSobreTaxa objSobreTaxa)
        {
            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 + "           FLAG         = ?FLAG     , ";
                    vFILTER = vFILTER + "           MESANO       = ?MESANO   , ";
                    vFILTER = vFILTER + "           CONS_ID      = ?CONS_ID  , ";
                    vFILTER = vFILTER + "           UNID_ID      = ?UNID_ID    ";

                    vAND = vAND + " AND  ZONA = ?ZONA ";
                    vAND = vAND + " AND LIVRO = ?LIVRO ";
                    vAND = vAND + " AND CONTA = ?CONTA ";
                    vAND = vAND + " AND CODIGO = ?CODIGO ";
                    vAND = vAND + " AND CONS_ID = ?CONS_ID ";
                    vAND = vAND + " AND UNID_ID = ?UNID_ID ";

                    if (!string.IsNullOrEmpty(objSobreTaxa.REFERENCIA))
                    {
                        vAND = vAND + " AND REFERENCIA = ?REFERENCIA ";
                    }

                    vAND = vAND + " AND ((FLAG = '' OR FLAG IS NULL) OR (FLAG = ?FLAG AND MESANO = ?MESANO )) ";

                    cmd = new MySqlCommand(this.QueryUpdate);


                    cmd.Parameters.AddWithValue("?ZONA", objSobreTaxa.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?LIVRO", objSobreTaxa.LIVRO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONTA", objSobreTaxa.CONTA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CODIGO", objSobreTaxa.CODIGO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CONS_ID", objSobreTaxa.CONS_ID);
                    cmd.Parameters.AddWithValue("?UNID_ID", objSobreTaxa.UNID_ID);

                    if (!string.IsNullOrEmpty(objSobreTaxa.REFERENCIA))
                    {
                        cmd.Parameters.AddWithValue("?REFERENCIA", objSobreTaxa.REFERENCIA.ToString().ToUpper());
                    }

                    cmd.Parameters.AddWithValue("?FLAG", objSobreTaxa.FLAG.ToString().ToUpper());


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

        public void Delete(entidadeSobreTaxa objSobreTaxa)
        {
            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 (objSobreTaxa.ZONA != string.Empty)
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND ZONA = ?ZONA "; }
                        else
                        { vAND = "AND ZONA = ?ZONA "; }

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


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

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


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

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

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

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

                    if (objSobreTaxa.REFERENCIA != string.Empty)
                    {
                        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.QueryDelete);


                    if (objSobreTaxa.ZONA != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?ZONA", objSobreTaxa.ZONA);
                    }

                    if (objSobreTaxa.LIVRO != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?LIVRO", objSobreTaxa.LIVRO);
                    }

                    if (objSobreTaxa.CONTA != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?CONTA", objSobreTaxa.CONTA);
                    }

                    if (objSobreTaxa.CODIGO != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?CODIGO", objSobreTaxa.CODIGO);
                    }

                    if (objSobreTaxa.REFERENCIA != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?REFERENCIA", objSobreTaxa.REFERENCIA);
                    }


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

                return sb.ToString();
            }
        }

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

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

                return sb.ToString();
            }
        }

        #endregion
    }
}
