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

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

        #region Métodos

        public void Insert(entidadeMunic objMunic)
        {
            DataTable dt = null;
            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 + "COUNT(*) ";

                    vINNERJOIN = " ";

                    vORDERBY = " ";

                    vAND = " ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);

                    if (dt.Rows.Count != 0)
                    {
                        idCod = Convert.ToInt32(dt.Rows[0].ItemArray[0].ToString());
                    }
                    else
                    {
                        idCod = 0;
                    }

                    if (idCod != 0)
                    {
                        cmd = null;

                        idCod = 0;

                        vFILTER = " ";

                        vFILTER = vFILTER + "MAX(CODNUM) AS CODNUM ";

                        vINNERJOIN = " ";

                        vORDERBY = " ";

                        vAND = " ";

                        cmd = new MySqlCommand(this.QuerySelect);

                        idCod = (int)objGerenciadorTransacao.ExecuteScalar(cmd);

                        idCod = idCod + 1;

                    }
                    else
                    {
                        idCod = idCod + 1;
                    }

                    objMunic.CODNUM = idCod;

                    objMunic.COD = objMunic.CODUF.ToString("00") + idCod.ToString("0000");

                    cmd = null;

                    vFILTER = " ";

                    vFILTER = vFILTER + "COD = ?COD, NOME = ?NOME, CODNUM = ?CODNUM, CODUF = ?CODUF, ZONA = ?ZONA, IBGE = ?IBGE ";


                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?COD", objMunic.COD.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NOME", objMunic.NOME.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CODNUM", objMunic.CODNUM);
                    cmd.Parameters.AddWithValue("?CODUF", objMunic.CODUF);
                    cmd.Parameters.AddWithValue("?ZONA", objMunic.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?IBGE", objMunic.IBGE.ToString().ToUpper());

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

        public void Insert2(entidadeMunic objMunic)
        {
            DataTable dt = null;
            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 + "MAX(CODNUM) AS CODNUM ";

                    //vINNERJOIN = " ";

                    //vORDERBY = " ";

                    //vAND = " ";

                    //cmd = new MySqlCommand(this.QuerySelect);

                    //idCod = (int)objGerenciadorTransacao.ExecuteScalar(cmd);

                    //idCod = idCod + 1;

                    //objMunic.CODNUM = idCod;
                    objMunic.COD = objMunic.CODUF.ToString("00") + objMunic.CODNUM.ToString("0000");

                    cmd = null;

                    vFILTER = " ";

                    vFILTER = vFILTER + "COD = ?COD, NOME = ?NOME, CODNUM = ?CODNUM, CODUF = ?CODUF, ZONA = ?ZONA, IBGE = ?IBGE ";


                    cmd = new MySqlCommand(this.QueryInsert);

                    cmd.Parameters.AddWithValue("?COD", objMunic.COD.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NOME", objMunic.NOME.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CODNUM", objMunic.CODNUM);
                    cmd.Parameters.AddWithValue("?CODUF", objMunic.CODUF);
                    cmd.Parameters.AddWithValue("?ZONA", objMunic.ZONA.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?IBGE", objMunic.IBGE.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?NOME", objMunic.CONTA_FATURAMENTO.ToString().ToUpper());

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

        public void Update(entidadeMunic objMunic)
        {
            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 = ?COD, NOME = ?NOME, CODNUM = ?CODNUM, CODUF = ?CODUF, ZONA = ?ZONA, IBGE = ?IBGE ";

                    vAND = vAND + "AND COD = ?COD";

                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?COD", objMunic.COD.ToString());
                    cmd.Parameters.AddWithValue("?NOME", objMunic.NOME.ToString().ToUpper());
                    cmd.Parameters.AddWithValue("?CODNUM", objMunic.CODNUM);
                    cmd.Parameters.AddWithValue("?CODUF", objMunic.CODUF);
                    cmd.Parameters.AddWithValue("?ZONA", objMunic.ZONA.ToString());
                    cmd.Parameters.AddWithValue("?IBGE", objMunic.IBGE.ToString());


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

        public void Update_Conta(entidadeMunic objMunic)
        {
            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 + "CONTA_FATURAMENTO = ?CONTA_FATURAMENTO ";

                    vAND = vAND + "AND COD = ?COD";

                    cmd = new MySqlCommand(this.QueryUpdate);

                    cmd.Parameters.AddWithValue("?CONTA_FATURAMENTO", objMunic.CONTA_FATURAMENTO.ToString());
                    cmd.Parameters.AddWithValue("?COD", objMunic.COD.ToString());

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


        public void Delete(entidadeMunic objMunic)
        {
            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 = " ";

                    vAND = vAND + "AND COD = ?COD ";


                    cmd = new MySqlCommand(this.QueryDelete);

                    cmd.Parameters.AddWithValue("?COD", objMunic.COD.ToString());

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

        public DataTable Select(entidadeMunic objMunic)
        {
            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 + "MUNIC.COD  AS CODEND, ";
                    vFILTER = vFILTER + "MUNIC.NOME AS NOMCID, ";
                    vFILTER = vFILTER + "MUNIC.CODNUM AS IDCID,    ";
                    vFILTER = vFILTER + "MUNIC.ZONA AS ZONA, ";
                    vFILTER = vFILTER + "MUNIC.IBGE AS IBGE,   ";
                    vFILTER = vFILTER + "UF.UF AS NOMUF,   ";
                    vFILTER = vFILTER + "UF.CODNUM AS IDUF, ";
                    vFILTER = vFILTER + "MUNIC.CONTA_FATURAMENTO AS CONTA_FATURAMENTO ";

                    vINNERJOIN = vINNERJOIN + "INNER JOIN UF ON MUNIC.CODUF = UF.CODNUM ";

                    vORDERBY = "ORDER BY UF.UF ASC, MUNIC.NOME ASC ";
                    vAND = " ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_1(entidadeMunic objMunic)
        {
            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 + "NOME, ";
                    vFILTER = vFILTER + "CODNUM, ";
                    vFILTER = vFILTER + "CODUF, ";
                    vFILTER = vFILTER + "ZONA, ";
                    vFILTER = vFILTER + "IBGE  ";

                    vINNERJOIN = " ";

                    vORDERBY = " ORDER BY ZONA ASC, NOME ASC  ";

                    vAND = vAND + " AND ZONA IS NOT NULL  ";
                    vAND = vAND + " AND ZONA <> '' ";

                    cmd = new MySqlCommand(this.QuerySelect);



                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable Select_2(entidadeMunic objMunic)
        {
            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 + "CODNUM ";


                    vINNERJOIN = " ";

                    vORDERBY = "   ";

                    if (objMunic.NOME != String.Empty)
                    {
                        if (vAND != String.Empty)
                        { vAND = vAND + "AND NOME = ?NOME "; }
                        else
                        { vAND = "AND NOME = ?NOME "; }

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



                    cmd = new MySqlCommand(this.QuerySelect);

                    if (objMunic.NOME != String.Empty)
                    {
                        cmd.Parameters.AddWithValue("?NOME", objMunic.NOME.ToString().ToUpper());
                    }

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectOthersTables(entidadeMunic objMunic)
        {
            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 + "CODNUM, ";
                    vFILTER = vFILTER + "NOME ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY NOME ASC ";

                    vAND = vAND + "AND  LEFT(COD,?COD0) = ?COD1 ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?COD0", objMunic.COD.ToString().Length);
                    cmd.Parameters.AddWithValue("?COD1", objMunic.COD.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectOthersTables_1(entidadeMunic objMunic)
        {
            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 + "NOME ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY NOME ASC ";

                    vAND = vAND + "AND LEFT(COD,?COD0) = ?COD1 ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?COD0", objMunic.COD.ToString().Length);
                    cmd.Parameters.AddWithValue("?COD1", objMunic.COD.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectOthersTables_2(entidadeMunic objMunic)
        {
            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 + "CODNUM, ";
                    vFILTER = vFILTER + "NOME ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY NOME 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_3(entidadeMunic objMunic)
        {
            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 + "NOME ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY NOME ASC ";

                    vAND = vAND + "AND COD = ?COD0 ";
                    
                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?COD0", objMunic.COD.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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


        public DataTable SelectOthersTables4(entidadeMunic objMunic)
        {
            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 + "CODNUM, ";
                    vFILTER = vFILTER + "NOME ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY NOME ASC ";

                    vAND = vAND + "AND COD = ?COD1 ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?COD0", objMunic.COD.ToString().Length);
                    cmd.Parameters.AddWithValue("?COD1", objMunic.COD.ToString().ToUpper());

                    dt = objGerenciadorTransacao.GetDataTable(cmd);
                }

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

        public DataTable SelectOthersTables_5(entidadeMunic objMunic)
        {
            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 + "NOME ";

                    vINNERJOIN = " ";

                    vORDERBY = "ORDER BY NOME ASC ";

                    vAND = vAND + "AND NOME = ?NOME ";

                    cmd = new MySqlCommand(this.QuerySelect);

                    cmd.Parameters.AddWithValue("?NOME", objMunic.NOME.ToString());
                    //cmd.Parameters.AddWithValue("?COD1", objMunic.COD.ToString().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("MUNIC SET  ");
                this.sb.AppendLine(vFILTER);

                return sb.ToString();
            }
        }

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

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

                return sb.ToString();
            }
        }

        #endregion


    }
}
