using Microsoft.Data.SqlClient;
using MLL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class DALEmpresas
    {
        private readonly string connectionString;

        public DALEmpresas(string conexao)
        {
            connectionString = conexao;
        }

        #region CRUD
        public bool Inserir(ModeloEmpresas modelo)
        {
            try
            {
                using SqlConnection conn = new(connectionString);

                string sql = @"
        INSERT INTO Empresas
        (
            NOME,
            FANTASIA,
            ENDERECO,
            NUMERO,
            COMPLEM,
            BAIRRO,
            CIDADE,
            UF,
            CEP,
            CNPJ_CPF,
            RG_IE,
            EMAIL,
            SITE,
            TELEFONE,
            FAX,
            MOVEL,
            CONTATOS,
            CNAE,
            COD_MUNICIPIO,
            I_IRRF,
            I_IRPJ,
            I_CSLL,
            I_INSS,
            I_ISS,
            I_COFINS,
            I_ICMSF,
            I_PISPASEP,
            I_RETEM,
            OBS_NOTA_PADR,
            INS_MUNICIPAL,
            NF_ICMS_S,
            NF_FRETE,
            NF_SEGURO,
            NF_DESPESAS,
            NF_cin_FRETE,
            NF_cin_SEGURO,
            NF_cin_DESPESAS,
            NF_cin_DESCONTO,
            NF_ISS,
            NF_IRRF,
            NF_IRPJ,
            NF_PIS,
            NF_COFINS,
            NF_CSLL,
            NF_INSS,
            LCP116,
            CFOP_SERV,
            REGIME,
            MYLOGO_NFE,
            MYLOGO_RECIBOS,
            CARTEIRA_CRYPTOMOEDA1,
            CARTEIRA_CRYPTOMOEDA2,
            CARTEIRA_CRYPTOMOEDA3
        )
        VALUES
        (
            @NOME,
            @FANTASIA,
            @ENDERECO,
            @NUMERO,
            @COMPLEM,
            @BAIRRO,
            @CIDADE,
            @UF,
            @CEP,
            @CNPJ_CPF,
            @RG_IE,
            @EMAIL,
            @SITE,
            @TELEFONE,
            @FAX,
            @MOVEL,
            @CONTATOS,
            @CNAE,
            @COD_MUNICIPIO,
            @I_IRRF,
            @I_IRPJ,
            @I_CSLL,
            @I_INSS,
            @I_ISS,
            @I_COFINS,
            @I_ICMSF,
            @I_PISPASEP,
            @I_RETEM,
            @OBS_NOTA_PADR,
            @INS_MUNICIPAL,
            @NF_ICMS_S,
            @NF_FRETE,
            @NF_SEGURO,
            @NF_DESPESAS,
            @NF_cin_FRETE,
            @NF_cin_SEGURO,
            @NF_cin_DESPESAS,
            @NF_cin_DESCONTO,
            @NF_ISS,
            @NF_IRRF,
            @NF_IRPJ,
            @NF_PIS,
            @NF_COFINS,
            @NF_CSLL,
            @NF_INSS,
            @LCP116,
            @CFOP_SERV,
            @REGIME,
            @MYLOGO_NFE,
            @MYLOGO_RECIBOS,
            @CARTEIRA_CRYPTOMOEDA1,
            @CARTEIRA_CRYPTOMOEDA2,
            @CARTEIRA_CRYPTOMOEDA3
        )";

                using SqlCommand cmd = new(sql, conn);

                PreencherParametros(cmd, modelo);

                conn.Open();

                return cmd.ExecuteNonQuery() > 0;
            }
            catch
            {
                return false;
            }
        }
        public bool Alterar(ModeloEmpresas modelo)
        {
            try
            {
                using SqlConnection conn = new(connectionString);

                string sql = @"
        UPDATE Empresas
        SET
            CODIGO=@CODIGO,
            NOME=@NOME,
            FANTASIA=@FANTASIA,
            ENDERECO=@ENDERECO,
            NUMERO=@NUMERO,
            COMPLEM=@COMPLEM,
            BAIRRO=@BAIRRO,
            CIDADE=@CIDADE,
            UF=@UF,
            CEP=@CEP,
            CNPJ_CPF=@CNPJ_CPF,
            RG_IE=@RG_IE,
            EMAIL=@EMAIL,
            SITE=@SITE,
            TELEFONE=@TELEFONE,
            FAX=@FAX,
            MOVEL=@MOVEL,
            CONTATOS=@CONTATOS,
            CNAE=@CNAE,
            COD_MUNICIPIO=@COD_MUNICIPIO,
            I_IRRF=@I_IRRF,
            I_IRPJ=@I_IRPJ,
            I_CSLL=@I_CSLL,
            I_INSS=@I_INSS,
            I_ISS=@I_ISS,
            I_COFINS=@I_COFINS,
            I_ICMSF=@I_ICMSF,
            I_PISPASEP=@I_PISPASEP,
            I_RETEM=@I_RETEM,
            OBS_NOTA_PADR=@OBS_NOTA_PADR,
            INS_MUNICIPAL=@INS_MUNICIPAL,
            NF_ICMS_S=@NF_ICMS_S,
            NF_FRETE=@NF_FRETE,
            NF_SEGURO=@NF_SEGURO,
            NF_DESPESAS=@NF_DESPESAS,
            NF_cin_FRETE=@NF_cin_FRETE,
            NF_cin_SEGURO=@NF_cin_SEGURO,
            NF_cin_DESPESAS=@NF_cin_DESPESAS,
            NF_cin_DESCONTO=@NF_cin_DESCONTO,
            NF_ISS=@NF_ISS,
            NF_IRRF=@NF_IRRF,
            NF_IRPJ=@NF_IRPJ,
            NF_PIS=@NF_PIS,
            NF_COFINS=@NF_COFINS,
            NF_CSLL=@NF_CSLL,
            NF_INSS=@NF_INSS,
            LCP116=@LCP116,
            CFOP_SERV=@CFOP_SERV,
            REGIME=@REGIME,
            MYLOGO_NFE=@MYLOGO_NFE,
            MYLOGO_RECIBOS=@MYLOGO_RECIBOS,
            CARTEIRA_CRYPTOMOEDA1=@CARTEIRA_CRYPTOMOEDA1,
            CARTEIRA_CRYPTOMOEDA2=@CARTEIRA_CRYPTOMOEDA2,
            CARTEIRA_CRYPTOMOEDA3=@CARTEIRA_CRYPTOMOEDA3
        WHERE
            ID_EMPRESAS=@ID_EMPRESAS";

                using SqlCommand cmd = new(sql, conn);

                PreencherParametros(cmd, modelo);

                cmd.Parameters.AddWithValue("@CODIGO", modelo.CODIGO);
                cmd.Parameters.AddWithValue("@ID_EMPRESAS", modelo.ID_EMPRESAS);

                conn.Open();

                return cmd.ExecuteNonQuery() > 0;
            }
            catch
            {
                return false;
            }
        }
        public bool Excluir(int id)
        {
            try
            {
                using SqlConnection conn = new(connectionString);

                string sql =
                    @"DELETE FROM Empresas
              WHERE ID_EMPRESAS=@ID";

                using SqlCommand cmd = new(sql, conn);

                cmd.Parameters.AddWithValue("@ID", id);

                conn.Open();

                return cmd.ExecuteNonQuery() > 0;
            }
            catch
            {
                return false;
            }
        }
        #endregion
        #region CONSULTAS
        public ModeloEmpresas? Carregar(int id)
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT *
    FROM Empresas
    WHERE ID_EMPRESAS=@ID";

            using SqlCommand cmd = new(sql, conn);

            cmd.Parameters.AddWithValue("@ID", id);

            conn.Open();

            using SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
                return PreencherModelo(dr);

            return null;
        }

        public ModeloEmpresas? CarregarPorCodigo(string codigo)
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT *
    FROM Empresas
    WHERE CODIGO=@CODIGO";

            using SqlCommand cmd = new(sql, conn);

            cmd.Parameters.AddWithValue("@CODIGO", codigo);

            conn.Open();

            using SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
                return PreencherModelo(dr);

            return null;
        }

        public ModeloEmpresas? CarregarPorCNPJ(string cnpjCpf)
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT *
    FROM Empresas
    WHERE CNPJ_CPF=@CNPJ_CPF";

            using SqlCommand cmd = new(sql, conn);

            cmd.Parameters.AddWithValue("@CNPJ_CPF", cnpjCpf);

            conn.Open();

            using SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
                return PreencherModelo(dr);

            return null;
        }
        #endregion
        #region verificações de existência
        public bool ExisteCNPJ(string cnpjCpf)
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT COUNT(*)
    FROM Empresas
    WHERE CNPJ_CPF=@CNPJ_CPF";

            using SqlCommand cmd = new(sql, conn);

            cmd.Parameters.AddWithValue("@CNPJ_CPF", cnpjCpf);

            conn.Open();

            return Convert.ToInt32(
                cmd.ExecuteScalar()) > 0;
        }
        public bool ExisteCodigo(string codigo)
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT COUNT(*)
    FROM Empresas
    WHERE CODIGO=@CODIGO";

            using SqlCommand cmd = new(sql, conn);

            cmd.Parameters.AddWithValue("@CODIGO", codigo);

            conn.Open();

            return Convert.ToInt32(
                cmd.ExecuteScalar()) > 0;
        }
        public bool ExisteEmail(string email)
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT COUNT(*)
    FROM Empresas
    WHERE EMAIL=@EMAIL";

            using SqlCommand cmd = new(sql, conn);

            cmd.Parameters.AddWithValue("@EMAIL", email);

            conn.Open();

            return Convert.ToInt32(
                cmd.ExecuteScalar()) > 0;
        }
        public bool ExisteTelefone(string telefone)
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT COUNT(*)
    FROM Empresas
    WHERE TELEFONE=@TELEFONE";

            using SqlCommand cmd = new(sql, conn);

            cmd.Parameters.AddWithValue("@TELEFONE", telefone);

            conn.Open();

            return Convert.ToInt32(
                cmd.ExecuteScalar()) > 0;
        }
        public bool ExisteCidade(string cidade)
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT COUNT(*)
    FROM Empresas
    WHERE CIDADE=@CIDADE";

            using SqlCommand cmd = new(sql, conn);

            cmd.Parameters.AddWithValue("@CIDADE", cidade);

            conn.Open();

            return Convert.ToInt32(
                cmd.ExecuteScalar()) > 0;
        }
        #endregion
        #region DASHBOARD
        public DataTable Dashboard()
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT
        COUNT(*) TOTAL_EMPRESAS,
        COUNT(DISTINCT CIDADE) TOTAL_CIDADES,
        COUNT(DISTINCT UF) TOTAL_ESTADOS,
        COUNT(DISTINCT REGIME) TOTAL_REGIMES
    FROM Empresas";

            using SqlDataAdapter da = new(sql, conn);

            DataTable dt = new();

            da.Fill(dt);

            return dt;
        }
        #endregion
        #region LISTAGENS
        public List<ModeloEmpresas> Listar(
   string? codigo = null,
   string? nome = null,
   string? fantasia = null,
   string? endereco = null,
   string? numero = null,
   string? complem = null,
   string? bairro = null,
   string? cidade = null,
   string? uf = null,
   string? cep = null,
   string? cnpjCpf = null,
   string? rgIe = null,
   string? email = null,
   string? site = null,
   string? telefone = null,
   string? fax = null,
   string? movel = null,
   string? contatos = null,
   string? cnae = null,
   string? codMunicipio = null,
   string? regime = null,
   string? insMunicipal = null,
   string? lcp116 = null,
   string? cfopServ = null)
        {
            List<ModeloEmpresas> lista = [];

            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT *
    FROM Empresas
    WHERE 1 = 1";

            using SqlCommand cmd = new();

            cmd.Connection = conn;

            if (!string.IsNullOrWhiteSpace(codigo))
            {
                sql += " AND CODIGO LIKE @CODIGO";
                cmd.Parameters.AddWithValue("@CODIGO", "%" + codigo + "%");
            }

            if (!string.IsNullOrWhiteSpace(nome))
            {
                sql += " AND NOME LIKE @NOME";
                cmd.Parameters.AddWithValue("@NOME", "%" + nome + "%");
            }

            if (!string.IsNullOrWhiteSpace(fantasia))
            {
                sql += " AND FANTASIA LIKE @FANTASIA";
                cmd.Parameters.AddWithValue("@FANTASIA", "%" + fantasia + "%");
            }

            if (!string.IsNullOrWhiteSpace(endereco))
            {
                sql += " AND ENDERECO LIKE @ENDERECO";
                cmd.Parameters.AddWithValue("@ENDERECO", "%" + endereco + "%");
            }

            if (!string.IsNullOrWhiteSpace(numero))
            {
                sql += " AND NUMERO LIKE @NUMERO";
                cmd.Parameters.AddWithValue("@NUMERO", "%" + numero + "%");
            }

            if (!string.IsNullOrWhiteSpace(complem))
            {
                sql += " AND COMPLEM LIKE @COMPLEM";
                cmd.Parameters.AddWithValue("@COMPLEM", "%" + complem + "%");
            }

            if (!string.IsNullOrWhiteSpace(bairro))
            {
                sql += " AND BAIRRO LIKE @BAIRRO";
                cmd.Parameters.AddWithValue("@BAIRRO", "%" + bairro + "%");
            }

            if (!string.IsNullOrWhiteSpace(cidade))
            {
                sql += " AND CIDADE LIKE @CIDADE";
                cmd.Parameters.AddWithValue("@CIDADE", "%" + cidade + "%");
            }

            if (!string.IsNullOrWhiteSpace(uf))
            {
                sql += " AND UF=@UF";
                cmd.Parameters.AddWithValue("@UF", uf);
            }

            if (!string.IsNullOrWhiteSpace(cep))
            {
                sql += " AND CEP LIKE @CEP";
                cmd.Parameters.AddWithValue("@CEP", "%" + cep + "%");
            }

            if (!string.IsNullOrWhiteSpace(cnpjCpf))
            {
                sql += " AND CNPJ_CPF LIKE @CNPJ_CPF";
                cmd.Parameters.AddWithValue("@CNPJ_CPF", "%" + cnpjCpf + "%");
            }

            if (!string.IsNullOrWhiteSpace(rgIe))
            {
                sql += " AND RG_IE LIKE @RG_IE";
                cmd.Parameters.AddWithValue("@RG_IE", "%" + rgIe + "%");
            }

            if (!string.IsNullOrWhiteSpace(email))
            {
                sql += " AND EMAIL LIKE @EMAIL";
                cmd.Parameters.AddWithValue("@EMAIL", "%" + email + "%");
            }

            if (!string.IsNullOrWhiteSpace(site))
            {
                sql += " AND SITE LIKE @SITE";
                cmd.Parameters.AddWithValue("@SITE", "%" + site + "%");
            }

            if (!string.IsNullOrWhiteSpace(telefone))
            {
                sql += " AND TELEFONE LIKE @TELEFONE";
                cmd.Parameters.AddWithValue("@TELEFONE", "%" + telefone + "%");
            }

            if (!string.IsNullOrWhiteSpace(fax))
            {
                sql += " AND FAX LIKE @FAX";
                cmd.Parameters.AddWithValue("@FAX", "%" + fax + "%");
            }

            if (!string.IsNullOrWhiteSpace(movel))
            {
                sql += " AND MOVEL LIKE @MOVEL";
                cmd.Parameters.AddWithValue("@MOVEL", "%" + movel + "%");
            }

            if (!string.IsNullOrWhiteSpace(contatos))
            {
                sql += " AND CONTATOS LIKE @CONTATOS";
                cmd.Parameters.AddWithValue("@CONTATOS", "%" + contatos + "%");
            }

            if (!string.IsNullOrWhiteSpace(cnae))
            {
                sql += " AND CNAE LIKE @CNAE";
                cmd.Parameters.AddWithValue("@CNAE", "%" + cnae + "%");
            }

            if (!string.IsNullOrWhiteSpace(codMunicipio))
            {
                sql += " AND COD_MUNICIPIO LIKE @COD_MUNICIPIO";
                cmd.Parameters.AddWithValue("@COD_MUNICIPIO", "%" + codMunicipio + "%");
            }

            if (!string.IsNullOrWhiteSpace(regime))
            {
                sql += " AND REGIME LIKE @REGIME";
                cmd.Parameters.AddWithValue("@REGIME", "%" + regime + "%");
            }

            if (!string.IsNullOrWhiteSpace(insMunicipal))
            {
                sql += " AND INS_MUNICIPAL LIKE @INS_MUNICIPAL";
                cmd.Parameters.AddWithValue("@INS_MUNICIPAL", "%" + insMunicipal + "%");
            }

            if (!string.IsNullOrWhiteSpace(lcp116))
            {
                sql += " AND LCP116 LIKE @LCP116";
                cmd.Parameters.AddWithValue("@LCP116", "%" + lcp116 + "%");
            }

            if (!string.IsNullOrWhiteSpace(cfopServ))
            {
                sql += " AND CFOP_SERV LIKE @CFOP_SERV";
                cmd.Parameters.AddWithValue("@CFOP_SERV", "%" + cfopServ + "%");
            }

            sql += " ORDER BY ID_EMPRESAS DESC";

            cmd.CommandText = sql;

            conn.Open();

            using SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                lista.Add(
                    PreencherModelo(dr));
            }

            return lista;
        }
        public List<ModeloEmpresas> ListarPorNome(string nome)
        {
            return Listar(nome: nome);
        }
        public List<ModeloEmpresas> ListarPorCidade(string cidade)
        {
            return Listar(cidade: cidade);
        }
        public List<ModeloEmpresas> ListarPorUF(string uf)
        {
            return Listar(uf: uf);
        }
        public List<ModeloEmpresas> ListarPorRegime(string regime)
        {
            return Listar(regime: regime);
        }
        public List<ModeloEmpresas> ListarPorCNAE(string cnae)
        {
            return Listar(cnae: cnae);
        }
        public List<ModeloEmpresas> ListarPorCEP(string cep)
        {
            return Listar(cep: cep);
        }
        public List<ModeloEmpresas> ListarPorCNPJ(string cnpjCpf)
        {
            return Listar(cnpjCpf: cnpjCpf);
        }
        public List<ModeloEmpresas> ListarPorEmail(string email)
        {
            return Listar(email: email);
        }
        public List<ModeloEmpresas> ListarPorTelefone(string telefone)
        {
            return Listar(telefone: telefone);
        }
        #endregion
        #region OUTROS

        public ModeloEmpresas? CarregarPrimeiraEmpresa()
        {
            using SqlConnection conn = new(connectionString);

            string sql = @"
    SELECT TOP 1 *
    FROM Empresas
    ORDER BY ID_EMPRESAS";

            using SqlCommand cmd = new(sql, conn);

            conn.Open();

            using SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
                return PreencherModelo(dr);

            return null;
        }



        private void PreencherParametros(
            SqlCommand cmd,
            ModeloEmpresas modelo)
        {
            cmd.Parameters.AddWithValue("@NOME", modelo.NOME);
            cmd.Parameters.AddWithValue("@FANTASIA", modelo.FANTASIA);

            cmd.Parameters.AddWithValue("@ENDERECO", modelo.ENDERECO);
            cmd.Parameters.AddWithValue("@NUMERO", modelo.NUMERO);
            cmd.Parameters.AddWithValue("@COMPLEM", modelo.COMPLEM);
            cmd.Parameters.AddWithValue("@BAIRRO", modelo.BAIRRO);
            cmd.Parameters.AddWithValue("@CIDADE", modelo.CIDADE);
            cmd.Parameters.AddWithValue("@UF", modelo.UF);
            cmd.Parameters.AddWithValue("@CEP", modelo.CEP);

            cmd.Parameters.AddWithValue("@CNPJ_CPF", modelo.CNPJ_CPF);
            cmd.Parameters.AddWithValue("@RG_IE", modelo.RG_IE);

            cmd.Parameters.AddWithValue("@EMAIL", modelo.EMAIL);
            cmd.Parameters.AddWithValue("@SITE", modelo.SITE);

            cmd.Parameters.AddWithValue("@TELEFONE", modelo.TELEFONE);
            cmd.Parameters.AddWithValue("@FAX", modelo.FAX);
            cmd.Parameters.AddWithValue("@MOVEL", modelo.MOVEL);
            cmd.Parameters.AddWithValue("@CONTATOS", modelo.CONTATOS);

            cmd.Parameters.AddWithValue("@CNAE", modelo.CNAE);
            cmd.Parameters.AddWithValue("@COD_MUNICIPIO", modelo.COD_MUNICIPIO);

            cmd.Parameters.AddWithValue("@I_IRRF", modelo.I_IRRF);
            cmd.Parameters.AddWithValue("@I_IRPJ", modelo.I_IRPJ);
            cmd.Parameters.AddWithValue("@I_CSLL", modelo.I_CSLL);
            cmd.Parameters.AddWithValue("@I_INSS", modelo.I_INSS);
            cmd.Parameters.AddWithValue("@I_ISS", modelo.I_ISS);
            cmd.Parameters.AddWithValue("@I_COFINS", modelo.I_COFINS);
            cmd.Parameters.AddWithValue("@I_ICMSF", modelo.I_ICMSF);
            cmd.Parameters.AddWithValue("@I_PISPASEP", modelo.I_PISPASEP);
            cmd.Parameters.AddWithValue("@I_RETEM", modelo.I_RETEM);

            cmd.Parameters.AddWithValue("@OBS_NOTA_PADR", modelo.OBS_NOTA_PADR);
            cmd.Parameters.AddWithValue("@INS_MUNICIPAL", modelo.INS_MUNICIPAL);

            cmd.Parameters.AddWithValue("@NF_ICMS_S", modelo.NF_ICMS_S);
            cmd.Parameters.AddWithValue("@NF_FRETE", modelo.NF_FRETE);
            cmd.Parameters.AddWithValue("@NF_SEGURO", modelo.NF_SEGURO);
            cmd.Parameters.AddWithValue("@NF_DESPESAS", modelo.NF_DESPESAS);

            cmd.Parameters.AddWithValue("@NF_cin_FRETE", modelo.NF_cin_FRETE);
            cmd.Parameters.AddWithValue("@NF_cin_SEGURO", modelo.NF_cin_SEGURO);
            cmd.Parameters.AddWithValue("@NF_cin_DESPESAS", modelo.NF_cin_DESPESAS);
            cmd.Parameters.AddWithValue("@NF_cin_DESCONTO", modelo.NF_cin_DESCONTO);

            cmd.Parameters.AddWithValue("@NF_ISS", modelo.NF_ISS);
            cmd.Parameters.AddWithValue("@NF_IRRF", modelo.NF_IRRF);
            cmd.Parameters.AddWithValue("@NF_IRPJ", modelo.NF_IRPJ);
            cmd.Parameters.AddWithValue("@NF_PIS", modelo.NF_PIS);
            cmd.Parameters.AddWithValue("@NF_COFINS", modelo.NF_COFINS);
            cmd.Parameters.AddWithValue("@NF_CSLL", modelo.NF_CSLL);
            cmd.Parameters.AddWithValue("@NF_INSS", modelo.NF_INSS);

            cmd.Parameters.AddWithValue("@LCP116", modelo.LCP116);
            cmd.Parameters.AddWithValue("@CFOP_SERV", modelo.CFOP_SERV);

            cmd.Parameters.AddWithValue("@REGIME", modelo.REGIME);

            cmd.Parameters.AddWithValue("@MYLOGO_NFE", modelo.MYLOGO_NFE);
            cmd.Parameters.AddWithValue("@MYLOGO_RECIBOS", modelo.MYLOGO_RECIBOS);

            cmd.Parameters.AddWithValue("@CARTEIRA_CRYPTOMOEDA1", modelo.CARTEIRA_CRYPTOMOEDA1);
            cmd.Parameters.AddWithValue("@CARTEIRA_CRYPTOMOEDA2", modelo.CARTEIRA_CRYPTOMOEDA2);
            cmd.Parameters.AddWithValue("@CARTEIRA_CRYPTOMOEDA3", modelo.CARTEIRA_CRYPTOMOEDA3);
        }
        private ModeloEmpresas PreencherModelo(SqlDataReader dr)
        {
            return new ModeloEmpresas
            {
                ID_EMPRESAS = Convert.ToInt32(dr["ID_EMPRESAS"]),

                CODIGO = Convert.ToString(dr["CODIGO"]) ?? string.Empty,
                NOME = Convert.ToString(dr["NOME"]) ?? string.Empty,
                FANTASIA = Convert.ToString(dr["FANTASIA"]) ?? string.Empty,

                ENDERECO = Convert.ToString(dr["ENDERECO"]) ?? string.Empty,
                NUMERO = Convert.ToString(dr["NUMERO"]) ?? string.Empty,
                COMPLEM = Convert.ToString(dr["COMPLEM"]) ?? string.Empty,
                BAIRRO = Convert.ToString(dr["BAIRRO"]) ?? string.Empty,
                CIDADE = Convert.ToString(dr["CIDADE"]) ?? string.Empty,
                UF = Convert.ToString(dr["UF"]) ?? string.Empty,
                CEP = Convert.ToString(dr["CEP"]) ?? string.Empty,

                CNPJ_CPF = Convert.ToString(dr["CNPJ_CPF"]) ?? string.Empty,
                RG_IE = Convert.ToString(dr["RG_IE"]) ?? string.Empty,

                EMAIL = Convert.ToString(dr["EMAIL"]) ?? string.Empty,
                SITE = Convert.ToString(dr["SITE"]) ?? string.Empty,

                TELEFONE = Convert.ToString(dr["TELEFONE"]) ?? string.Empty,
                FAX = Convert.ToString(dr["FAX"]) ?? string.Empty,
                MOVEL = Convert.ToString(dr["MOVEL"]) ?? string.Empty,
                CONTATOS = Convert.ToString(dr["CONTATOS"]) ?? string.Empty,

                CNAE = Convert.ToString(dr["CNAE"]) ?? string.Empty,
                COD_MUNICIPIO = Convert.ToString(dr["COD_MUNICIPIO"]) ?? string.Empty,

                I_IRRF = Convert.ToString(dr["I_IRRF"]) ?? string.Empty,
                I_IRPJ = Convert.ToString(dr["I_IRPJ"]) ?? string.Empty,
                I_CSLL = Convert.ToString(dr["I_CSLL"]) ?? string.Empty,
                I_INSS = Convert.ToString(dr["I_INSS"]) ?? string.Empty,
                I_ISS = Convert.ToString(dr["I_ISS"]) ?? string.Empty,
                I_COFINS = Convert.ToString(dr["I_COFINS"]) ?? string.Empty,
                I_ICMSF = Convert.ToString(dr["I_ICMSF"]) ?? string.Empty,
                I_PISPASEP = Convert.ToString(dr["I_PISPASEP"]) ?? string.Empty,
                I_RETEM = Convert.ToString(dr["I_RETEM"]) ?? string.Empty,

                OBS_NOTA_PADR = Convert.ToString(dr["OBS_NOTA_PADR"]) ?? string.Empty,
                INS_MUNICIPAL = Convert.ToString(dr["INS_MUNICIPAL"]) ?? string.Empty,

                NF_ICMS_S = Convert.ToString(dr["NF_ICMS_S"]) ?? string.Empty,
                NF_FRETE = Convert.ToString(dr["NF_FRETE"]) ?? string.Empty,
                NF_SEGURO = Convert.ToString(dr["NF_SEGURO"]) ?? string.Empty,
                NF_DESPESAS = Convert.ToString(dr["NF_DESPESAS"]) ?? string.Empty,

                NF_cin_FRETE = Convert.ToString(dr["NF_cin_FRETE"]) ?? string.Empty,
                NF_cin_SEGURO = Convert.ToString(dr["NF_cin_SEGURO"]) ?? string.Empty,
                NF_cin_DESPESAS = Convert.ToString(dr["NF_cin_DESPESAS"]) ?? string.Empty,
                NF_cin_DESCONTO = Convert.ToString(dr["NF_cin_DESCONTO"]) ?? string.Empty,

                NF_ISS = Convert.ToString(dr["NF_ISS"]) ?? string.Empty,
                NF_IRRF = Convert.ToString(dr["NF_IRRF"]) ?? string.Empty,
                NF_IRPJ = Convert.ToString(dr["NF_IRPJ"]) ?? string.Empty,
                NF_PIS = Convert.ToString(dr["NF_PIS"]) ?? string.Empty,
                NF_COFINS = Convert.ToString(dr["NF_COFINS"]) ?? string.Empty,
                NF_CSLL = Convert.ToString(dr["NF_CSLL"]) ?? string.Empty,
                NF_INSS = Convert.ToString(dr["NF_INSS"]) ?? string.Empty,

                LCP116 = Convert.ToString(dr["LCP116"]) ?? string.Empty,
                CFOP_SERV = Convert.ToString(dr["CFOP_SERV"]) ?? string.Empty,

                REGIME = Convert.ToString(dr["REGIME"]) ?? string.Empty,

                MYLOGO_NFE = Convert.ToString(dr["MYLOGO_NFE"]) ?? string.Empty,
                MYLOGO_RECIBOS = Convert.ToString(dr["MYLOGO_RECIBOS"]) ?? string.Empty,

                CARTEIRA_CRYPTOMOEDA1 = Convert.ToString(dr["CARTEIRA_CRYPTOMOEDA1"]) ?? string.Empty,
                CARTEIRA_CRYPTOMOEDA2 = Convert.ToString(dr["CARTEIRA_CRYPTOMOEDA2"]) ?? string.Empty,
                CARTEIRA_CRYPTOMOEDA3 = Convert.ToString(dr["CARTEIRA_CRYPTOMOEDA3"]) ?? string.Empty
            };
        }
        #endregion

    }
}