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

        #region Métodos

        public void Insert(entidadeICReferencia objICReferencia)
        {
            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 + "ANO = ?ANO, ";
                    vFILTER = vFILTER + "ID_TENSAO =  (SELECT ID_TENSAO FROM IC_REFERENCIA  WHERE ANO = ?ANO_OLD ), ";
                    vFILTER = vFILTER + "ID_REGIAO =  (SELECT ID_REGIAO FROM IC_REFERENCIA  WHERE ANO = ? ANO_OLD ), ";
                    vFILTER = vFILTER + "ID_IC_DICFICDMIC = (SELECT ID_IC_DICFICDMIC FROM IC_REFERENCIA  WHERE ANO = ? ANO_OLD ), ";
                    vFILTER = vFILTER + "ID_DEC         = (SELECT ID_DEC FROM IC_REFERENCIA  WHERE ANO = ? ANO_OLD ), ";
                    vFILTER = vFILTER + "ID_FEC         = (SELECT ID_FEC FROM IC_REFERENCIA  WHERE ANO = ? ANO_OLD ), ";
                    vFILTER = vFILTER + "DEC_TRIMESTRAL = (SELECT DEC_TRIMESTRAL FROM IC_REFERENCIA  WHERE ANO = ? ANO_OLD ), ";
                    vFILTER = vFILTER + "DEC_MENSAL     = (SELECT DEC_MENSAL FROM IC_REFERENCIA  WHERE ANO = ? ANO_OLD ), ";
                    vFILTER = vFILTER + "FEC_TRIMESTRAL = (SELECT FEC_TRIMESTRAL FROM IC_REFERENCIA  WHERE ANO = ? ANO_OLD ), ";
                    vFILTER = vFILTER + "FEC_MENSAL     = (SELECT FEC_MENSAL FROM IC_REFERENCIA  WHERE ANO = ? ANO_OLD ), ";

    
                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?ANO", objICReferencia.ANO);
                    cmd.Parameters.AddWithValue("?ANO_OLD", objICReferencia.ID_DEC);

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

        public void Update(entidadeICReferencia objICReferencia)
        {
            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_DEC         = ?ID_DEC, ";
                    vFILTER = vFILTER + "ID_FEC         = ?ID_FEC, ";
                    vFILTER = vFILTER + "DEC_TRIMESTRAL = ?DEC_TRIMESTRAL, ";
                    vFILTER = vFILTER + "DEC_MENSAL     = ?DEC_MENSAL, ";
                    vFILTER = vFILTER + "FEC_TRIMESTRAL = ?FEC_TRIMESTRAL, ";
                    vFILTER = vFILTER + "FEC_MENSAL     = ?FEC_MENSAL ";

                    vAND = vAND + "AND ANO = ?ANO ";

                    cmd = new MySqlCommand(this.QueryUpdate);


                    cmd.Parameters.AddWithValue("?ID_DEC", objICReferencia.ID_DEC);
                    cmd.Parameters.AddWithValue("?ID_FEC", objICReferencia.ID_FEC);
                    cmd.Parameters.AddWithValue("?DEC_TRIMESTRAL", objICReferencia.DEC_TRIMESTRAL);
                    cmd.Parameters.AddWithValue("?DEC_MENSAL", objICReferencia.DEC_MENSAL);
                    cmd.Parameters.AddWithValue("?FEC_TRIMESTRAL", objICReferencia.FEC_TRIMESTRAL);
                    cmd.Parameters.AddWithValue("?FEC_MENSAL", objICReferencia.FEC_MENSAL);
                    cmd.Parameters.AddWithValue("?ANO", objICReferencia.ANO);


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

        public DataTable Select(entidadeICReferencia objICReferencia)
        {
            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 + "ANO, ";
                    vFILTER = vFILTER + "ID_TENSAO, ";
                    vFILTER = vFILTER + "ID_REGIAO, ";
                    vFILTER = vFILTER + "ID_IC_DICFICDMIC, ";
                    vFILTER = vFILTER + "ID_DEC, ";
                    vFILTER = vFILTER + "ID_FEC, ";
                    vFILTER = vFILTER + "DEC_TRIMESTRAL, ";
                    vFILTER = vFILTER + "DEC_MENSAL, ";
                    vFILTER = vFILTER + "FEC_TRIMESTRAL, ";
                    vFILTER = vFILTER + "FEC_MENSAL ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

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

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

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

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

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

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


                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objICReferencia.ANO != 0)
                    {
                        cmd.Parameters.AddWithValue("?ANO", objICReferencia.ANO);
                    }

                    if (objICReferencia.ID_TENSAO != 0)
                    {
                        cmd.Parameters.AddWithValue("?ID_TENSAO", objICReferencia.ID_TENSAO);
                    }

                    if (objICReferencia.ID_REGIAO != 0)
                    {
                        cmd.Parameters.AddWithValue("?ID_REGIAO", objICReferencia.ID_REGIAO);
                    }

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_1(entidadeICReferencia objICReferencia)
        {
            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 + "ANO, ";
                    vFILTER = vFILTER + "ID_TENSAO, ";
                    vFILTER = vFILTER + "ID_REGIAO, ";
                    vFILTER = vFILTER + "ID_IC_DICFICDMIC, ";
                    vFILTER = vFILTER + "ID_DEC, ";
                    vFILTER = vFILTER + "ID_FEC, ";
                    vFILTER = vFILTER + "DEC_TRIMESTRAL, ";
                    vFILTER = vFILTER + "DEC_MENSAL, ";
                    vFILTER = vFILTER + "FEC_TRIMESTRAL, ";
                    vFILTER = vFILTER + "FEC_MENSAL ";

                    vINNERJOIN = " ";

                    vORDERBY = "LIMIT 0, 1 ";

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

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


                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objICReferencia.ANO != 0)
                    {
                        cmd.Parameters.AddWithValue("?ANO", objICReferencia.ANO);
                    }

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

                return sb.ToString();
            }
        }

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

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

                return sb.ToString();
            }
        }

        #endregion

    }
}
