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

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

        #region Métodos

        public void Insert(entidadeRamal objRamal)
        {
            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 + "DESCRICAO               = ?DESCRICAO, ";
                    vFILTER = vFILTER + "IDRAMAL_FORMACAO         = ?IDRAMAL_FORMACAO, ";
                    vFILTER = vFILTER + "IDRAMAL_ISOLACAO        = ?IDRAMAL_ISOLACAO, ";
                    vFILTER = vFILTER + "IDRAMAL_MATERIAL         = ?IDRAMAL_MATERIAL, ";
                    vFILTER = vFILTER + "IDRAMAL_TENSAO_ISOLACAO  = ?IDRAMAL_TENSAO_ISOLACAO, ";
                    vFILTER = vFILTER + "IDSITUACAO              = ?IDSITUACAO, ";
                    vFILTER = vFILTER + "IDPROPRIETARIO           = ?IDPROPRIETARIO, ";
                    vFILTER = vFILTER + "FASE_LIGACAO             = ?FASE_LIGACAO, ";
                    vFILTER = vFILTER + "TIPO_LIGACAO            = ?TIPO_LIGACAO, ";
                    vFILTER = vFILTER + "BITOLA                 = ?BITOLA, ";
                    vFILTER = vFILTER + "COMPRIMENTO            = ?COMPRIMENTO, ";
                    vFILTER = vFILTER + "SITUACAO                = ?SITUACAO, ";
                    vFILTER = vFILTER + "IDUSUARIOS             = ?IDUSUARIOS ";

                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?DESCRICAO", objRamal.DESCRICAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?IDRAMAL_FORMACAO", objRamal.IDRAMAL_FORMACAO);
                    cmd.Parameters.AddWithValue("?IDRAMAL_ISOLACAO", objRamal.IDRAMAL_ISOLACAO);
                    cmd.Parameters.AddWithValue("?IDRAMAL_MATERIAL", objRamal.IDRAMAL_MATERIAL);
                    cmd.Parameters.AddWithValue("?IDRAMAL_TENSAO_ISOLACAO", objRamal.IDRAMAL_TENSAO_ISOLACAO);
                    cmd.Parameters.AddWithValue("?IDSITUACAO", objRamal.IDSITUACAO);
                    cmd.Parameters.AddWithValue("?IDPROPRIETARIO", objRamal.IDPROPRIETARIO);
                    cmd.Parameters.AddWithValue("?FASE_LIGACAO", objRamal.FASE_LIGACAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?TIPO_LIGACAO", objRamal.TIPO_LIGACAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?BITOLA", objRamal.BITOLA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?COMPRIMENTO", objRamal.COMPRIMENTO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?SITUACAO", objRamal.SITUACAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?IDUSUARIOS", objRamal.IDUSUARIOS);

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

        public void Update(entidadeRamal objRamal)
        {
            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 + "DESCRICAO               = ?DESCRICAO, ";
                    vFILTER = vFILTER + "IDRAMAL_FORMACAO         = ?IDRAMAL_FORMACAO, ";
                    vFILTER = vFILTER + "IDRAMAL_ISOLACAO        = ?IDRAMAL_ISOLACAO, ";
                    vFILTER = vFILTER + "IDRAMAL_MATERIAL         = ?IDRAMAL_MATERIAL, ";
                    vFILTER = vFILTER + "IDRAMAL_TENSAO_ISOLACAO  = ?IDRAMAL_TENSAO_ISOLACAO, ";
                    vFILTER = vFILTER + "IDSITUACAO              = ?IDSITUACAO, ";
                    vFILTER = vFILTER + "IDPROPRIETARIO           = ?IDPROPRIETARIO, ";
                    vFILTER = vFILTER + "FASE_LIGACAO             = ?FASE_LIGACAO, ";
                    vFILTER = vFILTER + "TIPO_LIGACAO            = ?TIPO_LIGACAO, ";
                    vFILTER = vFILTER + "BITOLA                 = ?BITOLA, ";
                    vFILTER = vFILTER + "COMPRIMENTO            = ?COMPRIMENTO, ";
                    vFILTER = vFILTER + "SITUACAO                = ?SITUACAO, ";
                    vFILTER = vFILTER + "IDUSUARIOS             = ?IDUSUARIOS ";

                    vAND = vAND + "AND ID = ?ID ";

                    cmd = new MySqlCommand(this.QueryUpdate);


                    cmd.Parameters.AddWithValue("?DESCRICAO", objRamal.DESCRICAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?IDRAMAL_FORMACAO", objRamal.IDRAMAL_FORMACAO);
                    cmd.Parameters.AddWithValue("?IDRAMAL_ISOLACAO", objRamal.IDRAMAL_ISOLACAO);
                    cmd.Parameters.AddWithValue("?IDRAMAL_MATERIAL", objRamal.IDRAMAL_MATERIAL);
                    cmd.Parameters.AddWithValue("?IDRAMAL_TENSAO_ISOLACAO", objRamal.IDRAMAL_TENSAO_ISOLACAO);
                    cmd.Parameters.AddWithValue("?IDSITUACAO", objRamal.IDSITUACAO);
                    cmd.Parameters.AddWithValue("?IDPROPRIETARIO", objRamal.IDPROPRIETARIO);
                    cmd.Parameters.AddWithValue("?FASE_LIGACAO", objRamal.FASE_LIGACAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?TIPO_LIGACAO", objRamal.TIPO_LIGACAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?BITOLA", objRamal.BITOLA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?COMPRIMENTO", objRamal.COMPRIMENTO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?SITUACAO", objRamal.SITUACAO.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?IDUSUARIOS", objRamal.IDUSUARIOS);
                    cmd.Parameters.AddWithValue("?ID", objRamal.ID);


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

        public void Update_1(entidadeRamal objRamal)
        {
            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 + "SITUACAO = 'INATIVO'  ";

                    vAND = vAND + "AND ID = ?ID ";

                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?ID", objRamal.ID);

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

        public DataTable Select(entidadeRamal objRamal)
        {
            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 + "DESCRICAO ,";
                    vFILTER = vFILTER + "IDRAMAL_FORMACAO ,";
                    vFILTER = vFILTER + "IDRAMAL_ISOLACAO ,";
                    vFILTER = vFILTER + "IDRAMAL_MATERIAL ,";
                    vFILTER = vFILTER + "IDRAMAL_TENSAO_ISOLACAO ,";
                    vFILTER = vFILTER + "IDSITUACAO ,";
                    vFILTER = vFILTER + "IDPROPRIETARIO ,";
                    vFILTER = vFILTER + "FASE_LIGACAO ,";
                    vFILTER = vFILTER + "TIPO_LIGACAO ,";
                    vFILTER = vFILTER + "BITOLA ,";
                    vFILTER = vFILTER + "COMPRIMENTO ,";
                    vFILTER = vFILTER + "SITUACAO ,";
                    vFILTER = vFILTER + "IDUSUARIOS ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

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

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


                    cmd = new MySqlCommand(this.QuerySelect);

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

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectMax(entidadeRamal objRamal)
        {
            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 + "MAX(ID) AS ID ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = " ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectOthersTables(entidadeRamal objRamal)
        {
            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 + "RAMAL.ID,  ";
                    vFILTER = vFILTER + "RAMAL.IDRAMAL_FORMACAO,  ";
                    vFILTER = vFILTER + "RAMAL_FORMACAO.DESCRICAO AS DESCRICAO_RAMAL_FORMACAO,  ";
                    vFILTER = vFILTER + "RAMAL.IDRAMAL_ISOLACAO,  ";
                    vFILTER = vFILTER + "RAMAL_ISOLACAO.DESCRICAO AS DESCRICAO_RAMAL_ISOLACAO,  ";
                    vFILTER = vFILTER + "RAMAL.IDRAMAL_MATERIAL,  ";
                    vFILTER = vFILTER + "RAMAL_MATERIAL.DESCRICAO AS DESCRICAO_RAMAL_MATERIAL,  ";
                    vFILTER = vFILTER + "RAMAL.IDRAMAL_TENSAO_ISOLACAO,  ";
                    vFILTER = vFILTER + "RAMAL_TENSAO_ISOLACAO.DESCRICAO AS DESCRICAO_RAMAL_TENSAO_ISOLACAO,  ";
                    vFILTER = vFILTER + "RAMAL.IDSITUACAO,  ";
                    vFILTER = vFILTER + "SITUACAO.DESCRICAO AS DESCRICAO_SITUACAO,  ";
                    vFILTER = vFILTER + "RAMAL.IDPROPRIETARIO,  ";
                    vFILTER = vFILTER + "PROPRIETARIO.DESCRICAO AS DESCRICAO_PROPRIETARIO,  ";
                    vFILTER = vFILTER + "RAMAL.FASE_LIGACAO,  ";
                    vFILTER = vFILTER + "RAMAL.TIPO_LIGACAO,  ";
                    vFILTER = vFILTER + "RAMAL.BITOLA,  ";
                    vFILTER = vFILTER + "RAMAL.COMPRIMENTO,  ";
                    vFILTER = vFILTER + "RAMAL.DESCRICAO AS RAMAL_DESCRICAO  ";

                    vINNERJOIN = vINNERJOIN + "    LEFT JOIN PROPRIETARIO  ";
                    vINNERJOIN = vINNERJOIN + "           ON PROPRIETARIO.ID = RAMAL.IDPROPRIETARIO  ";
                    vINNERJOIN = vINNERJOIN + "   LEFT JOIN SITUACAO  ";
                    vINNERJOIN = vINNERJOIN + "          ON SITUACAO.ID = RAMAL.IDSITUACAO  ";
                    vINNERJOIN = vINNERJOIN + "  LEFT JOIN RAMAL_FORMACAO  ";
                    vINNERJOIN = vINNERJOIN + "         ON RAMAL_FORMACAO.ID = RAMAL.IDRAMAL_FORMACAO  ";
                    vINNERJOIN = vINNERJOIN + " LEFT JOIN RAMAL_ISOLACAO  ";
                    vINNERJOIN = vINNERJOIN + "        ON RAMAL_ISOLACAO.ID = RAMAL.IDRAMAL_ISOLACAO  ";
                    vINNERJOIN = vINNERJOIN + "LEFT JOIN RAMAL_MATERIAL  ";
                    vINNERJOIN = vINNERJOIN + "       ON RAMAL_MATERIAL.ID = RAMAL.IDRAMAL_MATERIAL  ";
                    vINNERJOIN = vINNERJOIN + "  LEFT JOIN RAMAL_TENSAO_ISOLACAO  ";
                    vINNERJOIN = vINNERJOIN + "      ON RAMAL_TENSAO_ISOLACAO.ID = RAMAL.IDRAMAL_TENSAO_ISOLACAO  ";


                    vORDERBY = "ORDER BY RAMAL.ID DESC LIMIT 0, 1 ";

                    if (objRamal.ID != 0)
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND RAMAL.ID = ?ID "; }
                        else
                        { vAND = "AND RAMAL.ID = ?ID "; }

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


                    cmd = new MySqlCommand(this.QuerySelect);

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

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Delete(entidadeRamal objRamal)
        {
            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 (objRamal.ID != 0)
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND ID = ?ID "; }
                        else
                        { vAND = "AND ID = ?ID "; }

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


                    cmd = new MySqlCommand(this.QueryDelete);


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

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

                return sb.ToString();
            }
        }

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

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

                return sb.ToString();
            }
        }

        #endregion

    }
}
