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 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 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 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> 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> 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> 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 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> 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 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 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> 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> 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> 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 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 string KeyTable()
{
var codigo = string.Empty;
var _db = new Models.banco.bd();
SqlCommand _cmd;
SqlDataReader _ler;
StringBuilder sb2 = new StringBuilder();
sb2.Append("EXEC PROC_PORTALAVANTLED_ULTIMOSZ4 ");
_db.Conectar();
try
{
_cmd = new SqlCommand(sb2.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> 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 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 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> 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 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 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;
}