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

        #region Métodos

        public DataTable Select(entidadeTarifaComposicao objTarifaComposicao)
        {
            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 + "REFERENCIA ,";
                    vFILTER = vFILTER + "DESCRICAO ,";
                    vFILTER = vFILTER + "ENERGIA_REAL ,";
                    vFILTER = vFILTER + "ENERGIA ,";
                    vFILTER = vFILTER + "TRANSMISSAO ,";
                    vFILTER = vFILTER + "DISTRIBUICAO ,";
                    vFILTER = vFILTER + "ENC_SETORIAIS ,";
                    vFILTER = vFILTER + "PERDAS ,";
                    vFILTER = vFILTER + "TRIBUTOS_ICMS ,";
                    vFILTER = vFILTER + "TRIBUTOS_PIS_COFINS ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";



                    if (objTarifaComposicao.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 (objTarifaComposicao.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaComposicao.REFERENCIA);
                    }

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public void Insert(entidadeTarifaComposicao objTarifaComposicao)
        {
            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 + "REFERENCIA = ?REFERENCIA  ,";
                    vFILTER = vFILTER + "DESCRICAO =  ?DESCRICAO ,";
                    vFILTER = vFILTER + "ENERGIA_REAL =  ?ENERGIA_REAL ,";
                    vFILTER = vFILTER + "ENERGIA = ?ENERGIA  ,";
                    vFILTER = vFILTER + "TRANSMISSAO =  ?TRANSMISSAO, ";
                    vFILTER = vFILTER + "DISTRIBUICAO = ?DISTRIBUICAO ,";
                    vFILTER = vFILTER + "ENC_SETORIAIS = ?ENC_SETORIAIS ,";
                    vFILTER = vFILTER + "PERDAS = ?PERDAS ,";
                    vFILTER = vFILTER + "TRIBUTOS_ICMS = ?TRIBUTOS_ICMS ,";
                    vFILTER = vFILTER + "TRIBUTOS_PIS_COFINS = ?TRIBUTOS_PIS_COFINS  ";

                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaComposicao.REFERENCIA);
                    cmd.Parameters.AddWithValue("?DESCRICAO", objTarifaComposicao.DESCRICAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?ENERGIA_REAL", objTarifaComposicao.ENERGIA_REAL);
                    cmd.Parameters.AddWithValue("?ENERGIA", objTarifaComposicao.ENERGIA);
                    cmd.Parameters.AddWithValue("?TRANSMISSAO", objTarifaComposicao.TRANSMISSAO);
                    cmd.Parameters.AddWithValue("?DISTRIBUICAO", objTarifaComposicao.DISTRIBUICAO);
                    cmd.Parameters.AddWithValue("?ENC_SETORIAIS", objTarifaComposicao.ENC_SETORIAIS);
                    cmd.Parameters.AddWithValue("?PERDAS", objTarifaComposicao.PERDAS);
                    cmd.Parameters.AddWithValue("?TRIBUTOS_ICMS", objTarifaComposicao.TRIBUTOS_ICMS);
                    cmd.Parameters.AddWithValue("?TRIBUTOS_PIS_COFINS", objTarifaComposicao.TRIBUTOS_PIS_COFINS);

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

        public DataTable SelectOthersTables(entidadeTarifaComposicao objTarifaComposicao)
        {
            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 Delete(entidadeTarifaComposicao objTarifaComposicao)
        {
            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))
                {
                    vORDERBY = " ";


                    if (objTarifaComposicao.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.QueryDelete);


                    if (objTarifaComposicao.REFERENCIA != Convert.ToDateTime("01/01/0001"))
                    {
                        cmd.Parameters.AddWithValue("?REFERENCIA", objTarifaComposicao.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("TARIFAS_COMPOSICAO SET  ");
                this.sb.AppendLine(vFILTER);

                return sb.ToString();
            }
        }

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

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

                return sb.ToString();
            }
        }

        #endregion

    }
}
