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

        #region Métodos

        public DataTable SelectOthersTables(entidadeUf objUf)
        {
            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 + "COD, ";
                    vFILTER = vFILTER + "UF, ";
                    vFILTER = vFILTER + "DESCR,    ";
                    vFILTER = vFILTER + "CODNUM ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY UF ASC ";

                    vAND = " ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectOthersTables_1(entidadeUf objUf)
        {
            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 + "COD, ";
                    vFILTER = vFILTER + "UF, ";
                    vFILTER = vFILTER + "DESCR    ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY UF ASC ";

                    if (objUf.UF != string.Empty)
                    {
                        vAND = "AND UF = ?UF ";
                    }
                    else
                    {
                        vAND = " ";
                    }

                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objUf.UF != string.Empty)
                    {
                        cmd.Parameters.AddWithValue("?UF", objUf.UF.ToString().ToUpper());
                    }


                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectOthersTables_2(entidadeUf objUf)
        {
            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 + "SELECT ";
                    vFILTER = vFILTER + "U.UF   AS NMUF , ";
                    vFILTER = vFILTER + "M.NOME AS NMCID,  ";
                    vFILTER = vFILTER + "D.NOME AS NMDIS,  ";
                    vFILTER = vFILTER + "B.NOME AS NMBAI, ";
                    vFILTER = vFILTER + "L.NOME AS NMRUA, ";
                    vFILTER = vFILTER + "L.CEP  AS NMCEP ";
                    vFILTER = vFILTER + "FROM UF       AS U, ";
                    vFILTER = vFILTER + "MUNIC    AS M, ";
                    vFILTER = vFILTER + "DISTRITO AS D,  ";
                    vFILTER = vFILTER + "BAIRRO   AS B, ";
                    vFILTER = vFILTER + "LOGRA    AS L  ";
                    vFILTER = vFILTER + "WHERE 1 = 1 ";
                    vFILTER = vFILTER + "AND L.COD = ?COD0 ";
                    vFILTER = vFILTER + "AND B.COD = ?COD1 ";
                    vFILTER = vFILTER + "AND D.COD = ?COD2 ";
                    vFILTER = vFILTER + "AND M.COD = ?COD3 ";
                    vFILTER = vFILTER + "AND U.COD = ?COD4 ";

                    vORDERBY = " ";

                    vAND = vFILTER;

                    cmd = new MySqlCommand(this.QueryExecute);

                    cmd.Parameters.AddWithValue("?COD0", objUf.COD.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?COD1", objUf.COD.ToString().Substring(0, 11).ToUpper());
                    cmd.Parameters.AddWithValue("?COD2", objUf.COD.ToString().Substring(0, 8).ToUpper());
                    cmd.Parameters.AddWithValue("?COD3", objUf.COD.ToString().Substring(0, 6).ToUpper());
                    cmd.Parameters.AddWithValue("?COD4", objUf.COD.ToString().Substring(0, 2).ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectOthersTables_3(entidadeUf objUf)
        {
            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 + "SELECT ";
                    vFILTER = vFILTER + "U.UF   AS NMUF , ";
                    vFILTER = vFILTER + "M.NOME AS NMCID,  ";
                    vFILTER = vFILTER + "D.NOME AS NMDIS,  ";
                    vFILTER = vFILTER + "B.NOME AS NMBAI, ";
                    vFILTER = vFILTER + "L.NOME AS NMRUA, ";
                    vFILTER = vFILTER + "L.CEP  AS NMCEP ";
                    vFILTER = vFILTER + "FROM UF       AS U, ";
                    vFILTER = vFILTER + "MUNIC    AS M, ";
                    vFILTER = vFILTER + "DISTRITO AS D,  ";
                    vFILTER = vFILTER + "BAIRRO   AS B, ";
                    vFILTER = vFILTER + "LOGRA    AS L  ";
                    vFILTER = vFILTER + "WHERE 1 = 1 ";
                    vFILTER = vFILTER + "AND L.COD = ?COD0 ";
                    vFILTER = vFILTER + "AND B.COD = ?COD1 ";
                    vFILTER = vFILTER + "AND D.COD = ?COD2 ";
                    vFILTER = vFILTER + "AND M.COD = ?COD3 ";
                    vFILTER = vFILTER + "AND U.COD = ?COD4 ";

                    vORDERBY = " ";

                    vAND = vFILTER;

                    cmd = new MySqlCommand(this.QueryExecute);

                    cmd.Parameters.AddWithValue("?COD0", objUf.COD.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?COD1", objUf.COD.ToString().Substring(0, 1).ToUpper());
                    cmd.Parameters.AddWithValue("?COD2", objUf.COD.ToString().Substring(0, 2).ToUpper());
                    cmd.Parameters.AddWithValue("?COD3", objUf.COD.ToString().Substring(0, 2).ToUpper());
                    cmd.Parameters.AddWithValue("?COD4", objUf.COD.ToString().Substring(0, 3).ToUpper());

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

                return sb.ToString();
            }
        }

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

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

                return sb.ToString();
            }
        }

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

                this.sb.AppendLine(vAND);
                this.sb.AppendLine(vORDERBY);

                return sb.ToString();
            }
        }

        #endregion

    }
}
