public bool AprovarBonificacao(SZ3 sz3_)
        {
            StringBuilder sb = new StringBuilder();
            var _db = new Models.banco.bd();
            SqlCommand _cmd;
            var enviar = false;
            sb.Append(" EXEC PROC_PORTALAVANTLED_APROVACAOBONIFICACAO '" + sz3_.Z3_NPEDWEB + "' ");

            try
            {
                _db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), _db.conn);
                _cmd.ExecuteNonQuery();
                enviar = true;

            }
            catch (SqlException g)
            {
                throw new Exception("Erro na Inserção de Dados" + g.Message);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _db.Desconectar();
            }

            return enviar;
        }
        public bool AtualizacaoTotalValor(SZ3 sz3_)
        {
            StringBuilder sb = new StringBuilder();
            var _db = new Models.banco.bd();
            SqlCommand _cmd;
            var enviar = false;
            sb.Append(" EXEC PROC_PORTALAVANTLED_ATUALIZARTOTALVALORPEDIDO '" + sz3_.Z3_NPEDWEB + "','"+sz3_.Z3_TOTPED.ToString().Replace(",", ".") + "'");
            try
            {
                _db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), _db.conn);
                _cmd.ExecuteNonQuery();
                enviar = true;

            }
            catch (SqlException g)
            {
                throw new Exception("Erro na Inserção de Dados" + g.Message);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _db.Desconectar();
            }

            return enviar;
        }
        public List<Object> BonificacaoeVenda(string login, string cliente, string datade, string dataate, string pg)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();

            if (cliente.Equals("''"))
                cliente = "";
            if (datade.Equals("''"))
                datade = "";

            if (dataate.Equals("''"))
                dataate = "";

            sb.Append(" EXEC PROC_PORTALAVANTLED_BONIFICACAOVENDA '" + login.Trim() + "','" + cliente.Trim() + "','" + datade.Trim() + "','" + dataate.Trim() + "', '" + pg.Trim() + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sz3 = new SZ3();

                        sz3.Z3_NPEDWEB = float.Parse(rw["BONIFICAÇÃO"].ToString().Trim());
                        sz3.Z3_NPEDCLI = Convert.ToString(rw["VENDA"].ToString().Trim());
                        sz3.Z3_TOTPED = float.Parse(rw["TOTAL VENDA"].ToString().Trim());
                        sz3.Z3_VLMKT = float.Parse(rw["TOTAL BONIFICACAO"].ToString().Trim());
                        sz3.Z3_EMISSAO = Convert.ToString(rw["EMISSÃO"].ToString().Trim());
                        sz3.Z3_RAZASOC = Convert.ToString(rw["CLIENTE"].ToString().Trim());
                        sz3.Z3_DSCPGTO = Convert.ToString(rw["DESC.PAGAMENTO"]);
                        sz3.Z3_OBS = Convert.ToString(rw["SITUACAO"]).ToString();
                        sz3.Z3_WA1_LOJ = Convert.ToString(rw["LOJA"]).ToString();
                        sz3.Z3_MOTIVOR = Convert.ToString(rw["REPRESENTANTE"]);
                        dados.Add(sz3);

                    }

                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public List<Object> CreditosUtilizados(string login)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();

            sb.Append(" EXEC PROC_PORTALAVANTLED_BONIFICACAO_CREDMKT_CREDUTILIZADO '" + login.Trim() + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sz3 = new Z3_Z4_Bonificacao();

                        sz3.Z3_EMISSAO = Convert.ToString(rw["DTEMISSAO"].ToString().Trim());
                        sz3.Z3_RAZASOC = Convert.ToString(rw["CLIENTE"].ToString().Trim());
                        sz3.Z3_OBS = Convert.ToString(rw["NOTA FISCAL"].ToString().Trim());
                        sz3.Z3_TOTPED = float.Parse(string.Format("{0:n2}", decimal.Round(decimal.Parse(rw["BONIFICACAO"].ToString()), 2)));
                        sz3.Z3_REGIAO = Convert.ToString(rw["REGIONAL"].ToString());
                        sz3.Z3_WA1_COD = Convert.ToString(rw["A1_COD"].ToString());
                        sz3.Z4_CODPROD = Convert.ToString(rw["PRODUTO"].ToString());
                        sz3.Z4_DESCPRO = Convert.ToString(rw["DESCRICAO"].ToString());
                        sz3.Z4_DESCRA = float.Parse(rw["DESC.RAMO"].ToString());
                        sz3.Z4_DESCGE = float.Parse(rw["DESC.GERENCIAL"].ToString());
                        sz3.Z4_DESCESP = float.Parse(rw["DESC.ESPECIAL"].ToString());
                        sz3.Pedidoweb = Convert.ToString(rw["PEDIDO"].ToString());
                        sz3.Z4_PRVEN = float.Parse(rw["PREÇO"].ToString());
                        sz3.Z4_VLRTTIT = float.Parse(rw["VALOR"].ToString());
                        dados.Add(sz3);

                    }

                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public bool Inserir(SZ5 sz5)
        {
            StringBuilder sb = new StringBuilder();
            var _db = new Models.banco.bd();
            SqlCommand _cmd;
            bool enviar = false;
            sb.Append("EXEC PROC_PORTALAVANTLED_NOVO_CLIENTE '" + sz5.Z5_FILIAL + "','" + sz5.Z5_CODVEND + "','" +
                      sz5.Z5_CGC + "','" + sz5.Z5_RAZASOC + "' , ");
            sb.Append(" '" + sz5.Z5_NOMEABR + "','" + sz5.Z5_ENDEREC + "','" + sz5.Z5_ENDERNR + "','" +
                      sz5.Z5_ENDCOMP + "','" + sz5.Z5_BAIRRO + "','" + sz5.Z5_CIDADE + "', ");
            sb.Append(" '" + sz5.Z5_CDMUNIC + "','" + sz5.Z5_UF + "','" + sz5.Z5_PAIS + "','" + sz5.Z5_INSCEST +
                      "','" + sz5.Z5_CEP + "','" + sz5.Z5_TELEFDD + "', ");
            sb.Append(" '" + sz5.Z5_TELEFON + "','" + sz5.Z5_CELULAR + "','" + sz5.Z5_FAX + "','" +
                      sz5.Z5_CONTATO + "','" + sz5.Z5_SETOR + "', '" + sz5.Z5_DTCADAS + "' , ");
            sb.Append(" '" + sz5.Z5_CONDPAG + "','" + sz5.Z5_ENDPAG + "','" + sz5.Z5_ENDNRPG + "','" +
                      sz5.Z5_COMPPAG + "','" + sz5.Z5_BAIRROP + "','" + sz5.Z5_CEPPG + "' , ");
            sb.Append(" '" + sz5.Z5_CIDADEP + "','" + sz5.Z5_CDMUNPG + "','" + sz5.Z5_UFPG + "','" +
                      sz5.Z5_ENDENT + "','" + sz5.Z5_ENDNREN + "','" + sz5.Z5_COMPEN + "','" + sz5.Z5_BAIRROE +
                      "' ,");
            sb.Append(" '" + sz5.Z5_CEPEN + "','" + sz5.Z5_CIDADEN + "','" + sz5.Z5_CDMUNEN + "','" +
                      sz5.Z5_UFEN + "','" + sz5.Z5_EMAIL + "','" + sz5.Z5_EMAIL1 + "','" + sz5.Z5_OBSERV + "', ");
            sb.Append(" '" + sz5.Z5_OBS2 + "','" + sz5.Z5_COMIS + "','" + sz5.Z5_CONSFIN + "','" +
                      sz5.Z5_NMCOMPR + "','" + sz5.Z5_NMGEREN + "','" + sz5.Z5_PROPRIE + "', ");
            sb.Append(" '" + sz5.Z5_ANIVCOM + "','" + sz5.Z5_ANIVGER + "','" + sz5.Z5_ANIVPRO + "','" +
                      sz5.Z5_SUFRAMA + "','" + sz5.Z5_SUFRACR + "','" + sz5.Z5_SUFRADT + "', ");
            sb.Append(" '" + sz5.Z5_CNAE + "','" + sz5.Z5_CNAESEC + "','" + sz5.Z5_STATUS + "','" +
                      sz5.Z5_DESCWEB + "',  " + KeyTable() + ", '" + sz5.Z5_X_HORA + "' , '" + sz5.Z5_X_CANAL + "','" + sz5.Z5_X_SEGME + "','" + sz5.Z5_X_PERFI + "', '" + sz5.Z5_GRPTRIB + "' , '" + sz5.Z5_XREGESP + "' ");

            try
            {
                _db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), _db.conn);
                _cmd.ExecuteNonQuery();
                enviar = true;
            }
            catch (SqlException h)
            {
                throw new Exception("Erro na inserção de dados" + h.Message);
            }
            catch (Exception t)
            {
                throw new Exception("Erro no acesso de dados" + t.Message);
            }
            finally
            {
                _db.Desconectar();
            }

            return enviar;
        }
        public List<object> CalculoImpostoBonificacao(string estado, string produto, string cliente, string loja, string filial)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            SB1 sb1 = new SB1();
            SF7 sf7 = new SF7();

            List<object> dados = new List<Object>();

            sb.Append(" EXEC PROC_PORTALAVANTLED_CALCULO_IMPOSTOS_BONIFICACAO '"+estado.Trim()+"','"+produto.Trim()+"','"+cliente.Trim()+"','"+loja.Trim()+"','"+filial+"' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        sb1.B1_COD = rw["CODIGO"].ToString().Trim();
                        sb1.B1_DESC = rw["DESCRICAO"].ToString().Trim();
                        sb1.B1_POSIPI = rw["CL_FISCAL"].ToString().Trim();
                        sb1.B1_IPI = rw["IPI"].ToString().Trim();

                        sf7.F7_MARGEM = rw["IVA_ST"].ToString().Trim();
                        sf7.F7_ALIQEXT = rw["ICMS_EXT"].ToString().Trim();
                        sf7.F7_ALIQINT = rw["ICMS_INT"].ToString().Trim();
                        sf7.F7_GRPCLI = rw["GRUPO"].ToString().Trim();
                        sf7.F7_PERC_ST = rw["PERC_ST"].ToString().Trim();
                    }
                    dados.Add(sb1);
                    dados.Add(sf7);
                }

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally {
                db.Desconectar();
            }
            return dados;
        }
        public List<Object> CreditosGerados(string login)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();

            sb.Append(" EXEC PROC_PORTALAVANTLED_BONIFICACAO_CREDMKT_CREDGERADO '" + login.Trim() + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sz3 = new SZ3();

                        sz3.Z3_EMISSAO = Convert.ToString(rw["DTEMISSAO"].ToString().Trim());
                        sz3.Z3_RAZASOC = Convert.ToString(rw["CLIENTE"].ToString().Trim());
                        sz3.Z3_OBS = Convert.ToString(rw["NOTA FISCAL"].ToString().Trim());
                        sz3.Z3_TOTPED = float.Parse(string.Format("{0:n2}", decimal.Round(decimal.Parse(rw["BONIFICACAO"].ToString()), 2)));
                        sz3.Z3_REGIAO = Convert.ToString(rw["REGIONAL"].ToString());
                        sz3.Z3_APROVA = Convert.ToString(rw["REPRESENTANTE"].ToString());
                        sz3.Z3_WA1_COD = Convert.ToString(rw["COD.CLIENTE"].ToString());

                        dados.Add(sz3);
                    }

                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public List<object> ComissaoPorProduto(string regiao)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();

            List<Object> dados = new List<Object>();

            sb.Append(" EXEC PROC_PORTALAVANTLED_COMISSAO_POR_PRODUTO '"+regiao.Trim()+"' ");

            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var zzc = new ZZC();

                        zzc.ZZC_TIPO = Convert.ToString(rw["ZZC_TIPO"].ToString().Trim());
                        zzc.ZZC_AJUSTE = float.Parse(rw["ZZC_AJUSTE"].ToString().Trim());
                        zzc.ZZC_COMISS = float.Parse(rw["ZZC_COMISS"].ToString().Trim());
                        zzc.ZZC_AJ_ATE = float.Parse(rw["ZZC_AJ_ATE"].ToString().Trim());
                        dados.Add(zzc);

                    }

                }

            }
            catch (Exception d)
            {

                throw new Exception("Erro no Acesso ao dados" + d.Message);
            }
            finally
            {
                db.Desconectar();
            }
            return dados;
        }
        public List<object> Acrescimo(string loja, string cliente)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();

            List<Object> dados = new List<Object>();

            sb.Append(" EXEC PROC_PROC_PORTALAVANTLED_ACRESCIMOCLIENTE '" + loja.Trim() + "','" + cliente.Trim() + "' ");

            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var zzc = new ZZC();

                        zzc.ZZC_AJUSTE = float.Parse(rw["ZZC_AJUSTE"].ToString().Trim());

                        dados.Add(zzc);

                    }

                }

            }
            catch (Exception d)
            {

                throw new Exception("Erro no Acesso ao dados" + d.Message);
            }
            finally
            {
                db.Desconectar();
            }
            return dados;
        }
        public List<Object> PrazoMedio(string grupo)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();

            List<Object> dados = new List<Object>();
            sb.Append("EXEC PROC_PORTALAVANTLED_PRAZOMEDIOCLIENTE '" + grupo + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();

                while (_ler.Read()) {
                    var sa1 = new SA1();
                    var se4 = new SE4();

                    se4.E4_CODIGO = Convert.ToString(_ler["E4_CODIGO"]).Trim();
                        se4.E4_DESCRI = Convert.ToString(_ler["E4_DESCRI"]).Trim();
                        se4.E4_XPMEDIO = Convert.ToString(_ler["E4_XPMEDIO"]).Trim();
                        sa1.A1_XPMEDIO = Convert.ToString(_ler["A1_XPMEDIO"]).Trim();

                    dados.Add(se4);
                    dados.Add(sa1);

                }
                _ler.Close();

            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao banco de dados" + f.Message);
            }
            finally {
                db.Desconectar();
            }

            return dados;
        }
        public List<Object> PorRepresentante(string codigorepresentante)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();
            var sa3 = new SA3();
            sb.Append("EXEC PROC_PORTALAVANTLED_DESCONTOGERENCIALREPRESENTANTE '" + codigorepresentante.Trim() + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        sa3.A3_X_DSCGE = Convert.ToString(rw["A3_X_DSCGE"]);
                    }

                    dados.Add(sa3);
                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public List<Object> PorFrete(string codcliente)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();
            var c09 = new C09();
            sb.Append("EXEC PROC_PORTALAVANTLED_DESCONTOGERENCIALPORFRETE '"+codcliente.Trim()+"' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0) {
                    foreach (DataRow rw in dt.Rows) {
                        c09.C09_PFRETE = Convert.ToString(rw["C09_PFRETE"].ToString());
                    }

                    dados.Add(c09);
                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados "+f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public string KeyTable()
        {
            var codigo = string.Empty;

            StringBuilder sb = new StringBuilder();
            var _db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;

            sb.Append(" SELECT MAX(R_E_C_N_O_) + 1 R_E_C_N_O_  FROM SZ5010  ");

            _db.Conectar();
            try
            {
                _cmd = new SqlCommand(sb.ToString(), _db.conn);
                _ler = _cmd.ExecuteReader();
                while (_ler.Read())
                {
                    codigo = Convert.ToString(_ler["R_E_C_N_O_"].ToString());
                }
                _ler.Close();
                return codigo;
            }
            catch (SqlException f)
            {
                throw new Exception("Erro no Acesso de Dados" + f.Message);
            }
            catch (Exception g)
            {
                throw new Exception("Erro na Consulta de dados" + g.Message);
            }
            finally
            {
                _db.Desconectar();
            }
        }
        public List<object> DadosPedidosBonificacao(string codigo, string login)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            SA1 sa1 = new SA1();
            SA3 sa3 = new SA3();
            Z1 z1 = new Z1();

            List<Object> dados = new List<Object>();

            sb.Append(" EXEC PROC_PORTALAVANTLED_DADOS_PEDIDO_BONIFICADO '"+codigo.Trim()+"','"+login.Trim()+"' ");
            // tem que criar uma logica para trazer o codigo quando for gerente
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        sa1.A1_CGC = rw["A1_CGC"].ToString().Trim();
                        sa1.A1_NOME = rw["A1_NOME"].ToString().Trim();
                        sa3.A3_CARGO = rw["A1_CGC"].ToString();
                        sa3.A3_SENHA = rw["A1_NOME"].ToString().Trim();
                        sa3.A3_ACMMKT = float.Parse(rw["A3_ACMMKT"].ToString());
                        sa1.A1_LOJA = rw["A1_LOJA"].ToString().Trim();
                        sa1.A1_CEP = rw["A1_CEP"].ToString().Trim();
                        sa1.A1_END = rw["A1_END"].ToString().Trim();
                        sa1.A1_BAIRRO = rw["A1_BAIRRO"].ToString().Trim();
                        sa1.A1_EST = rw["A1_EST"].ToString().Trim();
                        sa1.A1_MUN = rw["A1_MUN"].ToString().Trim();
                        sa1.A1_NREDUZ = rw["A1_NREDUZ"].ToString().Trim();
                        sa1.A1_DDD = rw["A1_DDD"].ToString().Trim();
                        sa1.A1_TEL = rw["A1_TEL"].ToString().Trim();
                        sa1.A1_XPMEDIO = rw["A1_XPMEDIO"].ToString().Trim();
                        sa1.A1_COND = rw["A1_COND"].ToString().Trim();
                        sa1.A1_GRPTRIB = rw["A1_GRPTRIB"].ToString().Trim();

                        z1.Z1_NMTRANS = rw["Z1_NMTRANS"].ToString();
                        sa1.A1_TIPO = rw["A1_TIPO"].ToString().Trim();
                        z1.Z1_TRANSP = rw["Z1_TRANSP"].ToString();
                        sa1.A1_DESCWEB = rw["A1_DESCWEB"].ToString();
                        sa1.A1_COD = rw["A1_COD"].ToString();
                        sa3.A3_EMAIL = rw["A3_NOME"].ToString();
                        sa1.A1_CONTATO = rw["A1_CONTATO"].ToString();
                        sa1.A1_X_DESCT = rw["A1_X_DESCT"].ToString();
                        sa3.A3_EMAIL = rw["A3_EMAIL"].ToString();
                        sa1.A1_REGIAO = rw["A1_REGIAO"].ToString();
                        sa1.A1_EMAIL = rw["A1_EMAIL"].ToString();

                    }

                    dados.Add(sa1);
                    dados.Add(sa3);
                    dados.Add(z1);

                }

            }
            catch (Exception d)
            {

                throw new Exception("Erro no Acesso ao dados" + d.Message);
            }
            finally
            {
                db.Desconectar();
            }
            return dados;
        }
        public List<object> Tipos(string loja, string cliente)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            var sa1 = new SA1();

            List<Object> dados = new List<Object>();

            sb.Append(" EXEC PROC_PORTALAVANTLED_TIPOSDESCONTOCLIENTE '" + loja.Trim() + "','" + cliente.Trim() + "' ");

            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        sa1.A1_REGIAO = Convert.ToString(rw["A1_REGIAO"]).Trim();
                        sa1.A1_TIPO = Convert.ToString(rw["ZZC_TIPO"]).Trim();
                    }

                    dados.Add(sa1);

                }

            }
            catch (Exception d)
            {

                throw new Exception("Erro no Acesso ao dados" + d.Message);
            }
            finally
            {
                db.Desconectar();
            }
            return dados;
        }
        public List<object> EditarPedido(string pedido)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();
            sb.Append("EXEC PROC_PORTALAVANTLED_EDITAR_PEDIDOSVENDA_DADOS '" + pedido + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sa1 = new SA1();
                        var sa3 = new SA3();
                        var sz3 = new SZ3();

                        sz3.Z3_CNPJ = Convert.ToString(rw["CNPJ"]).Trim();
                        sz3.Z3_RAZASOC = Convert.ToString(rw["RAZAO SOCIAL"]).Trim();
                        sz3.Z3_WA1_COD = Convert.ToString(rw["CODICLIENTE"]).Trim();
                        sz3.Z3_WA1_LOJ = Convert.ToString(rw["LOJA"]).Trim();
                        sz3.Z3_NMFANTA = Convert.ToString(rw["NOME FANTASIA"]).Trim();
                        sz3.Z3_CODTRAN = Convert.ToString(rw["CODTRANS"]).Trim();
                        sz3.Z3_NMTRANS = Convert.ToString(rw["NOME TRANSP"]).Trim();
                        sz3.Z3_CODPGTO = Convert.ToString(rw["CODPAGTO"]).Trim();
                        sz3.Z3_DSCPGTO = Convert.ToString(rw["DESC.PAGTO"]).Trim();
                        sz3.Z3_PRODMKT = Convert.ToString(rw["OP.TRIANGULAR"]).Trim();
                        sz3.Z3_GTRCLI = Convert.ToString(rw["GRUPO TRIBUTARIO"]).Trim();
                        sz3.Z3_REGIAO = Convert.ToString(rw["REGIONAL"]).Trim();
                        sz3.Z3_EMAIL = Convert.ToString(rw["EMAIL CLIENTE"]).Trim();
                        sz3.Z3_CEPCLI = Convert.ToString(rw["CEP"].ToString().Trim());
                        sz3.Z3_ENDCLI = Convert.ToString(rw["ENDERECO"]);
                        sz3.Z3_UF = Convert.ToString(rw["ESTADO"]);
                        sz3.Z3_MUN = Convert.ToString(rw["MUNICIPIO"]);
                        sz3.Z3_BAIRROC = Convert.ToString(rw["BAIRRO"]);
                        sz3.Z3_TOTPED = float.Parse(rw["VALOR TOTAL"].ToString().Trim());
                        sz3.Z3_EMISSAO = Convert.ToString(rw["DT.EMISSAO"].ToString().Trim());

                        sa3.A3_ACMMKT = float.Parse(rw["VL.CREDITO"].ToString().Trim());
                        sa3.A3_CARGO = Convert.ToString(rw["CARGO"]);
                        sa3.A3_NOME = Convert.ToString(rw["REPPRSENTANTE"].ToString().Trim());
                        sa3.A3_EMAIL = Convert.ToString(rw["EMAIL RC"].ToString().Trim());

                        sa1.A1_DESCWEB = Convert.ToString(rw["DESC.RAMO"]).Trim();
                        sa1.A1_DDD = Convert.ToString(rw["DDD"].ToString().Trim());
                        sa1.A1_TEL = Convert.ToString(rw["TELEFONE"].ToString().Trim());
                        sa1.A1_CONTATO = Convert.ToString(rw["CONTATO"].ToString().Trim());
                        sa1.A1_XPMEDIO = Convert.ToString(rw["XPMEDIO"].ToString().Trim());
                        sa1.A1_COND = Convert.ToString(rw["CONDPGTO"]);
                        sa1.A1_XCONTRA = float.Parse(rw["A1_XCONTRA"].ToString().Trim());
                        sa1.A1_CONSF = float.Parse(rw["A1_CONSF"].ToString().Trim());
                        sa1.A1_XVLACOR = float.Parse(rw["A1_XVLACOR"].ToString().Trim());

                        dados.Add(sa1);
                        dados.Add(sz3);
                        dados.Add(sa3);

                    }

                }

            }
            catch (Exception d)
            {

                throw new Exception("Erro no Acesso ao dados" + d.Message);
            }
            finally
            {
                db.Desconectar();
            }
            return dados;
        }
        public bool ExclusaoItens(SZ4 sz4_)
        {
            StringBuilder sb = new StringBuilder();
            var _db = new Models.banco.bd();
            SqlCommand _cmd;
            var enviar = false;
            sb.Append(" EXEC PROC_PORTALAVANTLED_EXCLUSAOITENS '" + sz4_.Z4_NUMPEDW + "'");
            try
            {
                _db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), _db.conn);
                _cmd.ExecuteNonQuery();
                enviar = true;

            }
            catch (SqlException g)
            {
                throw new Exception("Erro na Inserção de Dados" + g.Message);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _db.Desconectar();
            }

            return enviar;
        }
        public List<Object> SolicitacaoBonificacao(string login, string cliente, string datade, string dataate, string pg)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();

            if (cliente.Equals("''"))
                cliente = "";
            if (datade.Equals("''"))
                datade = "";

            if (dataate.Equals("''"))
                dataate = "";

            sb.Append(" EXEC PROC_PORTALAVANTLED_SOLICITACAOBONIFICACAO '" + login.Trim() + "','" + cliente.Trim() + "','" + datade.Trim() + "','" + dataate.Trim() + "','" + pg.Trim() + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sz3 = new SZ3();

                        sz3.Z3_NPEDWEB = float.Parse(rw["WEB"].ToString().Trim());
                        sz3.Z3_EMISSAO = Convert.ToString(rw["EMISSAO"].ToString().Trim());
                        sz3.Z3_RAZASOC = Convert.ToString(rw["CLIENTE"].ToString().Trim());
                        sz3.Z3_WA1_LOJ = Convert.ToString(rw["LOJA"].ToString().Trim());
                        sz3.Z3_CAMKT = Convert.ToString(rw["CAMPANHA"].ToString().Trim());
                        sz3.Z3_VLMKT = float.Parse(string.Format("{0:n2}", decimal.Round(decimal.Parse(rw["VALOR"].ToString().Trim()), 2)));
                        sz3.Z3_VEND = Convert.ToString(rw["CODIGO"]).ToString().Trim();
                        sz3.Z3_MOTIVOR = Convert.ToString(rw["REPRESENTANTE"]).ToString().Trim();

                        dados.Add(sz3);

                    }

                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public bool AtualizarItensPedidoVendaPreco(SZ4 sz4_)
        {
            StringBuilder sb = new StringBuilder();
            var _db = new Models.banco.bd();
            SqlCommand _cmd;
            var enviar = false;

            sb.Append(" EXEC PROC_PORTALAVANTLED_ATUALIZARITENSPEDIDO "+sz4_.Z4_PRVEN.ToString().Replace(",", ".") + ","+sz4_.Z4_PRLIQ.ToString().Replace(",", ".") + ","+sz4_.Z4_VLRTTIT.ToString().Replace(",", ".") + ", "+ sz4_.R_E_C_N_O_+" ");
            try
            {
                _db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), _db.conn);
                _cmd.ExecuteNonQuery();
                enviar = true;

            }
            catch (SqlException g)
            {
                throw new Exception("Erro na Inserção de Dados" + g.Message);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _db.Desconectar();
            }
            return enviar;
        }
        public List<Object> SaldoRepresentante(string login)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();

            sb.Append(" EXEC PROC_PORTALAVANTLED_SALDOPORGERENTE '" + login.Trim() + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sz3 = new SZ3();

                        sz3.Z3_VEND = Convert.ToString(rw["COD.RC"].ToString().Trim());
                        sz3.Z3_WA1_COD = Convert.ToString(rw["COD.GEREN"].ToString());
                        sz3.Z3_RAZASOC = Convert.ToString(rw["REPRESENTANTE"].ToString().Trim());
                        sz3.Z3_TOTPED = float.Parse(string.Format("{0:n2}", decimal.Round(decimal.Parse(rw["SALDO"].ToString()), 2)));
                        sz3.Z3_REGIAO = Convert.ToString(rw["A3_REGIAO"].ToString());
                        dados.Add(sz3);

                    }

                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public List<Object> AtualizarItens(string pedido)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();
            sb.Append("EXEC PROC_PORTALAVANTLED_PRECOACIMAPEDIDO '" + pedido+"'");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sz4 = new Z3_Z4_SA3_SBM_SB1();

                        sz4.B1_PRV1 = float.Parse(rw["PRECO LISTA"].ToString());
                        sz4.Z4_PRVEN = float.Parse(rw["PRECO VENDA"].ToString());
                        sz4.Z4_DESCRA = float.Parse(rw["DESC.RAMO"].ToString());
                        sz4.Z4_DESCGE = float.Parse(rw["DESC.GERENCIAL"].ToString());
                        sz4.Z4_PRCLIQ = float.Parse(rw["PRECO LIQ"].ToString());
                        sz4.Z4_VLRTTIT = float.Parse(rw["VALOR TOTAL"].ToString());
                        sz4.Z4_QTDE = float.Parse(rw["QUANTIDADE"].ToString());
                        sz4.Z4_DESCPRO = Convert.ToString(rw["Z4_DESCPRO"].ToString());
                        sz4.R_E_C_N_O_ = Convert.ToInt32(rw["R_E_C_N_O_"].ToString());
                        dados.Add(sz4);

                    }

                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public List<object> ItensPedidoVenda(string pedido)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();
            sb.Append("EXEC PROC_PORTALAVANTLED_ITENSEDITARVENDAS '" + pedido + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {

                        var sz4 = new SZ4();
                        sz4.Z4_CODPROD = rw["CODIGO"].ToString().Trim();
                        sz4.Z4_DESCPRO = rw["PRODUTO"].ToString().Trim();
                        sz4.Z4_PRVEN = float.Parse(rw["PRC.VENDA"].ToString().Trim());
                        sz4.Z4_DESCRA = float.Parse(rw["RAMO"].ToString().Trim());
                        sz4.Z4_DESCGE = float.Parse(rw["GERENCIAL"].ToString().Trim());
                        sz4.Z4_DESCESP = float.Parse(rw["ESPECIAL"].ToString().Trim());
                        sz4.Z4_PRODESC = float.Parse(rw["PROMOCIONAL"].ToString().Trim());
                        sz4.Z4_VLCOMIS = float.Parse(rw["COMISSAO"].ToString().Trim());
                        sz4.Z4_PRLIQ = float.Parse(rw["PRC.LIQ"].ToString().Trim());
                        sz4.Z4_QTDE = float.Parse(rw["QTDE"].ToString().Trim());
                        sz4.Z4_VLRTTIT = float.Parse(rw["TOTAL"].ToString().Trim());

                        dados.Add(sz4);

                    }

                }

            }
            catch (Exception d)
            {

                throw new Exception("Erro no Acesso ao dados" + d.Message);
            }
            finally
            {
                db.Desconectar();
            }
            return dados;
        }
        public List<Object> Faturamento(string login, string datade, string dataate, string cliente, string municipio, string estado,string pg)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();

            if (cliente.Equals("''"))
                cliente = "";
            if (datade.Equals("''"))
                datade = "";

            if (dataate.Equals("''"))
                dataate = "";

            if (estado.Equals("''"))
                estado = "";

            if (municipio.Equals("''"))
                municipio = "";

            sb.Append(" EXEC PROC_PORTALVENDAS_FATURAMENTO '" + login.Trim() + "','" + datade.Trim() + "','" + dataate.Trim() + "', '" + cliente.Trim() + "' , '" + municipio.Trim() + "' , '" + estado.Trim() + "', '"+pg.Trim()+"'  ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sz3 = new SZ3();

                        sz3.Z3_TOTPED = float.Parse(rw["Vlr.com Imposto"].ToString().Trim());
                        sz3.Z3_VLMKT = float.Parse(rw["Vlr.sem Imposto"].ToString().Trim());
                        sz3.Z3_EMISSAO = Convert.ToString(rw["Data de Emissão"].ToString().Trim());
                        sz3.Z3_RAZASOC = Convert.ToString(rw["Razão Social"].ToString().Trim());
                        sz3.Z3_OBS = Convert.ToString( string.Format("{0:n2}", decimal.Round( decimal.Parse(rw["Previa Comissão"].ToString()),2))).ToString();
                        sz3.Z3_MOTIVOR = Convert.ToString(rw["Numero da Nota"]);
                        sz3.Z3_MUN = Convert.ToString(rw["Municipio"]);
                        sz3.Z3_UF = Convert.ToString(rw["Estado"]);

                        dados.Add(sz3);

                    }

                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public bool Inserir(SZ4 z4)
        {
            StringBuilder sb = new StringBuilder();
            var _db = new Models.banco.bd();
            SqlCommand _cmd;
            var enviar = false;
            sb.Append(" EXEC PROC_PORTALAVANTLED_SZ4 '" + z4.Z4_FILIAL + "', " + z4.Z4_NUMPEDW + ",'" +
                      z4.Z4_ITEMPED + "','" + z4.Z4_CODPROD + "' ,");
            sb.Append(" '" + z4.Z4_DESCPRO + "','" + z4.Z4_UNVENDA + "'," + z4.Z4_QTDE + ", " +
                      z4.Z4_PRVEN.ToString().Replace(",", ".") + ", ");
            sb.Append(" " + z4.Z4_VLRTTIT.ToString().Replace(",", ".") + "," + z4.Z4_DESCRA.ToString().Replace(",",".") + "," +
                      z4.Z4_DESCGE.ToString().Replace(",",".") + ",0,'1', " + z4.Z4_NUMCX + ", '" + z4.Z4_NUMPED + "', ");
            sb.Append(" '" + z4.Z4_ORIGEM + "'," + z4.Z4_PRLIQ.ToString().Replace(",", ".") + ",'" +
                      z4.Z4_TPOPERW + "'," + z4.Z4_DESCESP + "," + z4.Z4_PRODESC + ", ");
            sb.Append(" " + KeyTable() + ",'" + z4.Z4_MKTCRD + "'," + z4.Z4_PRORI.ToString().Replace(",", ".") +
                      ",'" + z4.Z4_RESERVA + "', '"+ z4.Z4_TPDESC+ "', "+z4.Z4_VLCOMIS+"  ");

            try
            {
                _db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), _db.conn);
                _cmd.ExecuteNonQuery();
                enviar = true;

            }
            catch (SqlException g)
            {
                throw new Exception("Erro na Inserção de Dados" + g.Message);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _db.Desconectar();
            }

            return enviar;
        }
        public List<Object> PedidoWMS(string login, string cliente, string datade, string dataate, string web, string interno, string pg)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();

            if (cliente.Equals("''"))
                cliente = "";
            if (datade.Equals("''"))
                datade = "";

            if (dataate.Equals("''"))
                dataate = "";

            if (web.Equals("''"))
                web = "";

            if (interno.Equals("''"))
                interno = "";

            sb.Append(" EXEC PROC_PORTALAVANTLED_ACPEDIDO '" + login.Trim() + "','" + cliente.Trim() + "','" + datade.Trim() + "','" + dataate.Trim() + "', '"+web+"', '"+interno+"', '" + pg.Trim() + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sz3 = new SZ3();

                        sz3.Z3_NPEDWEB = float.Parse(rw["Z3_NPEDWEB"].ToString().Trim());
                        sz3.Z3_EMISSAO = Convert.ToString(rw["Z3_EMISSAO"].ToString().Trim());
                        sz3.Z3_RAZASOC = Convert.ToString(rw["Z3_RAZASOC"].ToString().Trim());
                        sz3.Z3_NPEDCLI = Convert.ToString(rw["C5_NUM"]).ToString();
                        sz3.Z3_STATUS = Convert.ToString(rw["Z3_STATUS"]).ToString();
                        sz3.Z3_OBS = Convert.ToString(rw["SITUACAO"]).ToString();
                        dados.Add(sz3);

                    }

                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public List<Object> ConsultaEstoquePorFiltro(string grupo, string familia, string descricao, string codigo, string filial)
        {
            StringBuilder sb = new StringBuilder();

            if (grupo.Equals("''"))
                grupo = "";
            if (familia.Equals("''"))
                familia = "";

            if (descricao.Equals("''"))
                descricao = "";

            if (codigo.Equals("''"))
                codigo = "";

            sb.Append(" EXEC PROC_PORTALAVANTLED_DESCRICAO_ESTOQUEONLINE_2 '" + Convert.ToString(grupo.Trim()).ToUpper() + "','" + Convert.ToString(familia.Trim()).ToUpper() + "','" + Convert.ToString(descricao.Trim()).ToUpper() + "', '" + codigo.Trim() + "' , '" + filial + "'  ");
            var db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sb1 = new SB1_SBM_SB2_ZZF() {
                            B1_COD = Convert.ToString(rw["CÓDIGO"].ToString().Trim()),
                            B1_DESC = Convert.ToString(rw["DESCRIÇÃO"].ToString().Trim()),
                            B1_PRV1 = float.Parse( Convert.ToString( string.Format("{0:n2}", rw["PREÇO"].ToString().Trim().Replace(".",",")))),
                            B1_GRTRIB = Convert.ToString(rw["B1_GRTRIB"].ToString().Trim()),
                            B1_CONV = float.Parse(rw["B1_CONV"].ToString().Trim()),
                            B1_ORIGEM = Convert.ToString(rw["B1_ORIGEM"].ToString().Trim()),
                            B1_MULTWEB = Convert.ToString(rw["B1_MULTWEB"].ToString().Trim()),
                            BM_DESC = Convert.ToString(rw["GRUPO"]),
                            B2_LOCAL = Convert.ToString(rw["B2_LOCAL"].ToString().Trim()),
                            B2_QATU = float.Parse(rw["B2_QATU"].ToString().Trim()),
                             ZZF_DESCRI = Convert.ToString(rw["FAMILIA"])

                        };

                        dados.Add(sb1);

                    }

                }

            }
            catch (Exception f)
            {

                throw new Exception(f.Message);
            }
            finally
            {
                db.Desconectar();
            }

            return dados;
        }
        public List<Object> EnviadoeFaturados(string login, string cliente, string datade, string dataate, string pg)
        {
            StringBuilder sb = new StringBuilder();
            Models.banco.bd db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;
            DataTable dt = new DataTable();
            List<Object> dados = new List<Object>();

            if (cliente.Equals("''"))
                cliente = "";
            if (datade.Equals("''"))
                datade = "";

            if (dataate.Equals("''"))
                dataate = "";

            sb.Append(" EXEC PROC_PORTALAVANTLED_PEDIDOENVIADOFATURADO '" + login.Trim() + "','" + cliente.Trim() + "','" + datade.Trim() + "','" + dataate.Trim() + "','" + pg.Trim() + "' ");
            try
            {
                db.Conectar();
                _cmd = new SqlCommand(sb.ToString(), db.conn);
                _ler = _cmd.ExecuteReader();
                dt.Load(_ler);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow rw in dt.Rows)
                    {
                        var sz3 = new SZ3();

                        sz3.Z3_NPEDWEB = float.Parse(rw["N° WEB"].ToString().Trim());
                        sz3.Z3_EMISSAO = Convert.ToString(rw["EMISSÃO"].ToString().Trim());
                        sz3.Z3_NPEDCLI = Convert.ToString(rw["INTERNO"].ToString().Trim());
                        sz3.Z3_RAZASOC = Convert.ToString(rw["CLIENTE"].ToString().Trim());
                        sz3.Z3_WA1_LOJ = Convert.ToString(rw["LOJA"].ToString().Trim());
                        sz3.Z3_DSCPGTO = Convert.ToString(rw["DESC.PAGAMENTO"].ToString().Trim());
                        sz3.Z3_TOTPED = float.Parse(string.Format("{0:n2}", decimal.Round(decimal.Parse(rw["VL COM IMPOSTO"].ToString().Trim()), 2)));
                        sz3.Z3_STATUS = Convert.ToString(rw["SITUACAO"].ToString().Trim());

                        dados.Add(sz3);

                    }

                }
                return dados;
            }
            catch (Exception f)
            {

                throw new Exception("Erro no acesso ao dados " + f.Message);
            }
            finally
            {
                db.Desconectar();
            }
        }
        public List<SZ5> Verificar(string cpf)
        {
            var codigo = string.Empty;
            var sz5 = new SZ5();
            var dados = new List<SZ5>();
            StringBuilder sb = new StringBuilder();
            var _db = new Models.banco.bd();
            SqlCommand _cmd;
            SqlDataReader _ler;

            sb.Append(" SELECT Z5_CGC FROM SZ5010 WHERE Z5_CGC = '" + cpf + "' AND D_E_L_E_T_ = ''  ");

            _db.Conectar();
            try
            {
                _cmd = new SqlCommand(sb.ToString(), _db.conn);
                _ler = _cmd.ExecuteReader();
                while (_ler.Read())
                {
                    sz5.Z5_CGC = Convert.ToString(_ler["Z5_CGC"].ToString());
                }
                _ler.Close();
                dados.Add(sz5);
             }
            catch (SqlException f)
            {
                throw new Exception("Erro no Acesso de Dados" + f.Message);
            }
            catch (Exception g)
            {
                throw new Exception("Erro na Consulta de dados" + g.Message);
            }
            finally
            {
                _db.Desconectar();
            }

            return dados;
        }