public int IncluirLoteNfeSaida(int pLoja, NotaFiscal TipoNF)
{
try
{
Dt = new DataTable();
BancoDados.OpenConection();
NpgsqlCommand command;
if (TipoNF == NotaFiscal.Saida)
{
command = new NpgsqlCommand("SpIncluirLoteNFeSaida", BancoDados.conexao);
}
else
{
command = new NpgsqlCommand("SpIncluirLoteNFeEntrada", BancoDados.conexao);
}
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("loja", pLoja);
//command.Parameters.Add("NextNrLote", NpgsqlTypes.NpgsqlDbType.Integer).Direction = ParameterDirection.Output;
//command.ExecuteNonQuery();
var Id = command.ExecuteScalar();
BancoDados.CloseConection();
//return Convert.ToInt32(command.Parameters["@NextNrLote"].Value);
return(Convert.ToInt32(Id));
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
public DataTable ConsultarNotasFiscalSemRetornoOuDuplicadas()
{
try
{
Dt = new DataTable();
BancoDados.OpenConection();
NpgsqlCommand command = new NpgsqlCommand("SpConsultaNFesSemRetorno", BancoDados.conexao);
command.CommandType = CommandType.StoredProcedure;
dtReader = command.ExecuteReader();
Dt.Load(dtReader);
BancoDados.CloseConection();
return(Dt);
}
catch (Exception ex)
{
BancoDados.CloseConection();
StreamWriter vWriter = new StreamWriter(@"c:\ServicoCancelamentoNfe.txt", true);
vWriter.WriteLine("OCORREU O SEGUINTE EERRO: " + ex.Message.ToString());
vWriter.Flush();
vWriter.Close();
return(null);
}
}
public DataTable ConsultarNotasFiscalReferidaSaida(int pLoja, int pNotaFiscal, string pSerieNf)
{
try
{
Dt = new DataTable();
BancoDados.OpenConection();
NpgsqlCommand command = new NpgsqlCommand("dbo.SpConsultaReferidaNFeSaida", BancoDados.conexao);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@id_loja", pLoja);
command.Parameters.AddWithValue("@nrNF", pNotaFiscal);
command.Parameters.AddWithValue("@serienf", pSerieNf);
dtReader = command.ExecuteReader();
Dt.Load(dtReader);
BancoDados.CloseConection();
return(Dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
public static string UfIbgeEmpresa(int Loja)
{
try
{
dtReturn = new DataTable();
BancoDados.OpenConection();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM SpNfeConsultaLojasEmitentes() WHERE idloja=@idloja", BancoDados.conexao);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("idloja", Loja);
dtReader = command.ExecuteReader();
dtReturn.Load(dtReader);
BancoDados.CloseConection();
return(dtReturn.Rows[0]["id_uf"].ToString().ToUpper());
}
catch (Exception ex)
{
BancoDados.CloseConection();
throw new Exception(ex.Message.ToString());
}
}
public DataTable Pesquisar()
{
try
{
dtReturn = new DataTable();
BancoDados.OpenConection();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM vw_inutilizacaonfe", BancoDados.conexao);
command.CommandType = CommandType.Text;
dtReader = command.ExecuteReader();
dtReturn.Load(dtReader);
BancoDados.CloseConection();
return(dtReturn);
}
catch (SqlException ex)
{
sbMsgErro = new StringBuilder();
for (int i = 0; i < ex.Errors.Count; i++)
{
sbMsgErro.Append("Index #" + i + "\n" +
"Mensagem: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedimento: " + ex.Errors[i].Procedure + "\n");
}
BancoDados.CloseConection();
StreamWriter vWriter = new StreamWriter(@"c:\C:\Nota_Fiscal_Eletronica\ErroTransacional\ServicoCancelamentoNfe.txt", true);
vWriter.WriteLine("OCORREU O SEGUINTE EERRO: " + sbMsgErro.ToString());
vWriter.Flush();
vWriter.Close();
return(null);
}
catch (Exception ex)
{
BancoDados.CloseConection();
StreamWriter vWriter = new StreamWriter(@"c:\C:\Nota_Fiscal_Eletronica\ErroTransacional\ServicoCancelamentoNfe.txt", true);
vWriter.WriteLine("OCORREU O SEGUINTE EERRO: " + ex.Message.ToString());
vWriter.Flush();
vWriter.Close();
return(null);
}
}
public void InsertConsNFDest(Entidade_ConsNFDest ObjConsNFDest)
{
BancoDados.OpenConection();
NpgsqlCommand command;
BeginTrans = BancoDados.conexao.BeginTransaction();
command = new NpgsqlCommand("spincluirconsultadestinatario", BancoDados.conexao);
command.Transaction = BeginTrans;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("codstatus", ObjConsNFDest.codstatus);
command.Parameters.AddWithValue("dhresp", ObjConsNFDest.dhresp);
command.Parameters.AddWithValue("indcont", ObjConsNFDest.indcont);
var Id = command.ExecuteScalar();
foreach (var item in ObjConsNFDest.ListItemConsDest)
{
command = new NpgsqlCommand("spincluiritemconsultanfdest", BancoDados.conexao);
command.Transaction = BeginTrans;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("id_consdest", Convert.ToInt32(Id));
command.Parameters.AddWithValue("nsu", item.nsu);
command.Parameters.AddWithValue("chacessonfe", item.chacessonfe);
command.Parameters.AddWithValue("cnpjcpf", item.cnpjcpf.Trim());
command.Parameters.AddWithValue("nomeemitente", item.nomeemitente);
command.Parameters.AddWithValue("dtemissao", item.dtemissao);
command.Parameters.AddWithValue("tpnfe", item.tpnfe);
command.Parameters.AddWithValue("vlnfe", item.vlnfe);
command.Parameters.AddWithValue("dhautorizacao", item.dhautorizacao);
command.Parameters.AddWithValue("sitnfe", item.sitnfe);
command.Parameters.AddWithValue("sit_manifestacao_dest", item.sit_manifestacao_dest);
command.ExecuteNonQuery();
}
BeginTrans.Commit();
BancoDados.CloseConection();
}
public DataTable ConsultarNotasFiscalItens(int pLoja, int pNotaFiscal, string pSerieNf, int CodFornec, NotaFiscal TipoNF)
{
try
{
Dt = new DataTable();
BancoDados.OpenConection();
if (TipoNF == NotaFiscal.Saida)
{
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM vw_itemnfesaida WHERE cdloja=@id_loja AND nrnf=@nrnf AND nmserienf=@serienf", BancoDados.conexao);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("id_loja", pLoja);
command.Parameters.AddWithValue("nrNF", pNotaFiscal);
command.Parameters.AddWithValue("serienf", pSerieNf);
dtReader = command.ExecuteReader();
}
else
{
NpgsqlCommand command = new NpgsqlCommand("SpConsultaNFeItensEntrada", BancoDados.conexao);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@id_loja", pLoja);
command.Parameters.AddWithValue("@nrNF", pNotaFiscal);
command.Parameters.AddWithValue("@serienf", pSerieNf);
command.Parameters.AddWithValue("@CdFornec", CodFornec);
dtReader = command.ExecuteReader();
}
Dt.Load(dtReader);
BancoDados.CloseConection();
return(Dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
public static DataTable LojasEmitentes()
{
try
{
dtReturn = new DataTable();
BancoDados.OpenConection();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM SpNfeConsultaLojasEmitentes()", BancoDados.conexao);
command.CommandType = CommandType.Text;
dtReader = command.ExecuteReader();
dtReturn.Load(dtReader);
BancoDados.CloseConection();
return(dtReturn);
}
catch (Exception ex)
{
BancoDados.CloseConection();
throw new Exception(ex.Message.ToString());
}
}
public bool TramitacaoNfe(TipoTramitacao TramitacaoNfe, int Loja, int NotaFiscal, string SerieNf, int Fornecedor)
{
try
{
BancoDados.OpenConection();
NpgsqlCommand command = new NpgsqlCommand("SpIncluirTramitacaoNFe", BancoDados.conexao);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@id_loja", Loja);
command.Parameters.AddWithValue("@serienf", SerieNf);
command.Parameters.AddWithValue("@NrNf", NotaFiscal);
command.Parameters.AddWithValue("@CdFornec", Fornecedor);
command.Parameters.AddWithValue("@FlErro", 0);
if (TramitacaoNfe == TipoTramitacao.SolicitacaoCancelamento)
{
command.Parameters.AddWithValue("@TxTramitacao", "Cancelamento Solicitado");
command.Parameters.AddWithValue("@cdStatusNFe", 6);
}
else if (TramitacaoNfe == TipoTramitacao.Cancelamento)
{
command.Parameters.AddWithValue("@TxTramitacao", "Cancelamento Autorizado");
command.Parameters.AddWithValue("@cdStatusNFe", 7);
}
else if (TramitacaoNfe == TipoTramitacao.SolicitacaoConsultaNfe)
{
command.Parameters.AddWithValue("@TxTramitacao", "Consulta de Nfe Solicitado.");
command.Parameters.AddWithValue("@cdStatusNFe", 3);
}
else if (TramitacaoNfe == TipoTramitacao.ConsultaNfe)
{
command.Parameters.AddWithValue("@TxTramitacao", "Nota Fiscal Autorizada");
command.Parameters.AddWithValue("@cdStatusNFe", 11);
}
else if (TramitacaoNfe == TipoTramitacao.SolicitacaoInutilizacao)
{
command.Parameters.AddWithValue("@TxTramitacao", "Inutilizacao Solicitada");
command.Parameters.AddWithValue("@cdStatusNFe", 6);
}
else if (TramitacaoNfe == TipoTramitacao.Inutilizacao)
{
command.Parameters.AddWithValue("@TxTramitacao", "Inutilizacao Autorizada");
command.Parameters.AddWithValue("@cdStatusNFe", 6);
}
else if (TramitacaoNfe == TipoTramitacao.AutorizacaoNFe)
{
command.Parameters.AddWithValue("@TxTramitacao", "Atualizando NFe de acordo com a SEFAZ");
command.Parameters.AddWithValue("@cdStatusNFe", 6);
}
else if (TramitacaoNfe == TipoTramitacao.Indefinido)
{
command.Parameters.AddWithValue("@TxTramitacao", "Retorno indefinido");
command.Parameters.AddWithValue("@cdStatusNFe", 6);
}
else
{
command.Parameters.AddWithValue("@TxTramitacao", "");
command.Parameters.AddWithValue("@cdStatusNFe", 6);
}
command.ExecuteNonQuery();
BancoDados.CloseConection();
return(true);
}
catch (Exception ex)
{
BancoDados.CloseConection();
StreamWriter vWriter = new StreamWriter(@"c:\ServicoCancelamentoNfe.txt", true);
vWriter.WriteLine("OCORREU O SEGUINTE EERRO: " + ex.Message.ToString());
vWriter.Flush();
vWriter.Close();
return(false);
}
}
