public int NotaFiscalUpdateEmailEnviado(int CdNotaFiscalSaida, string datasource, string schema)
{
List <OracleParameter> lstParam = new List <OracleParameter>();
lstParam.Add(new OracleParameter("P_CDNOTAFISCALSAIDA", CdNotaFiscalSaida));
return(OracleHelper.ExecProcedureNonQuery("PK_NFE.SP_NOTASFISCAISATUASTATUSEMAIL", lstParam, CommandType.StoredProcedure, datasource, schema));
}
public int NotafiscalAtualizarGNREStatus(int cdnotafiscalsaida, int cdstatusgnre, string datasource, string schema, int P_TIPO_GNRE)
{
string campostatusGnre = P_TIPO_GNRE == 1 ? "CDSTATUSGNRE=" + cdstatusgnre.ToString() : "CDSTATUSGNREFCP=" + cdstatusgnre.ToString();
string query = string.Format("UPDATE NOTAFISCALSAIDA SET {0} WHERE CDNOTAFISCALSAIDA ={1}", campostatusGnre, cdnotafiscalsaida);
return(OracleHelper.ExecProcedureNonQuery(query, null, CommandType.Text, datasource, schema));
}
public int NotafiscalAtualizarGNREStatusProt(int cdnotafiscalsaida, string numeroprotocolo, string datasource, string schema, int P_TIPO_GNRE)
{
string campoProtGnre = P_TIPO_GNRE == 1 ? "PROTGNRE" : "PROTGNREFCP";
string campostatusGnre = P_TIPO_GNRE == 1 ? " ,CDSTATUSGNRE=1" : ",CDSTATUSGNREFCP=1";
string query = string.Format("UPDATE NOTAFISCALSAIDA SET {0} = {1} {2} WHERE CDNOTAFISCALSAIDA ={3}", campoProtGnre, numeroprotocolo, campostatusGnre, cdnotafiscalsaida);
return(OracleHelper.ExecProcedureNonQuery(query, null, CommandType.Text, datasource, schema));
}
//SP_UPDATE_HIST
public int UpdateHistorico(string xmlaut, string cdnotafiscalsaida, string datasource, string schema)
{
List <OracleParameter> lstParam = new List <OracleParameter>();
lstParam.Add(new OracleParameter("P_CDNOTAFISCALSAIDA", cdnotafiscalsaida));
lstParam.Add(new OracleParameter("P_XMLNFEAUT", OracleDbType.Clob, xmlaut, ParameterDirection.Input));
return(OracleHelper.ExecProcedureNonQuery("pk_HISTORICONFE.SP_UPDATE_HIST", lstParam, CommandType.StoredProcedure, datasource, schema));
}
public int InserirHistoricoItem(string xmlretorno, string nrprotocolo, string cdstatsefaz, string txdescsefaz, string cdnotafiscalsaida, string datasource, string schema)
{
List <OracleParameter> lstParam = new List <OracleParameter>();
lstParam.Add(new OracleParameter("P_XMLRETORNO", OracleDbType.Clob, xmlretorno, ParameterDirection.Input));
lstParam.Add(new OracleParameter("P_NRPROTOCOLO", nrprotocolo));
lstParam.Add(new OracleParameter("P_CDSTATSEFAZ", cdstatsefaz));
lstParam.Add(new OracleParameter("P_TXDESCSEFAZ", txdescsefaz));
lstParam.Add(new OracleParameter("P_CDNOTAFISCALSAIDA", cdnotafiscalsaida));
return(OracleHelper.ExecProcedureNonQuery("pk_HISTORICONFE.SP_INSERT_HISTITENS", lstParam, CommandType.StoredProcedure, datasource, schema));
}
/*
* PROCEDURE SP_UPDATENOTANAOPROCESSADA
* (
* P_NRNFECHAVE IN NOTAFISCALSAIDA.NRNFECHAVE%TYPE,
* P_TXFILENFE IN NOTAFISCALSAIDA.TXFILENFE%TYPE,
* P_TXFILENFEAUT IN NOTAFISCALSAIDA.TXFILENFEAUT%TYPE,
* P_TSNFEAUTORIZACAO IN NOTAFISCALSAIDA.TSNFEAUTORIZACAO%TYPE,
* P_NRNFEAUTORIZACAO IN NOTAFISCALSAIDA.NRNFEAUTORIZACAO%TYPE,
* P_TXNFERETORNO IN NOTAFISCALSAIDA.TXNFERETORNO%TYPE
*
* )
*/
public int NotaFiscalAtualizarNaoProcessada(string nrchavenfe, string txfilenfe, string txfilenfeaut, DateTime tsnfeautorizacao, string nrnfeautorizacao, string txnferetorno, string datasource, string schema)
{
List <OracleParameter> lstParam = new List <OracleParameter>();
lstParam.Add(new OracleParameter("P_NRNFECHAVE", nrchavenfe));
lstParam.Add(new OracleParameter("P_TXFILENFE", OracleDbType.Clob, txfilenfe, ParameterDirection.Input));
lstParam.Add(new OracleParameter("P_TXFILENFEAUT", OracleDbType.Clob, txfilenfeaut, ParameterDirection.Input));
lstParam.Add(new OracleParameter("P_TSNFEAUTORIZACAO", tsnfeautorizacao));
lstParam.Add(new OracleParameter("P_NRNFEAUTORIZACAO", nrnfeautorizacao));
lstParam.Add(new OracleParameter("P_TXNFERETORNO", txnferetorno));
return(OracleHelper.ExecProcedureNonQuery("PK_NFE.SP_UPDATENOTANAOPROCESSADA", lstParam, CommandType.StoredProcedure, datasource, schema));
}
//public int NotaFiscalEntradaValidar(string P_DEST_CNPJ, string P_EMIT_CNPJ, int P_IDE_NNF, int P_IDE_SERIE, DateTime P_IDE_DEMI)
//{
// List<OracleParameter> lstParam = new List<OracleParameter>();
// lstParam.Add(new OracleParameter("P_DEST_CNPJ", P_DEST_CNPJ));
// lstParam.Add(new OracleParameter("P_EMIT_CNPJ", P_EMIT_CNPJ));
// lstParam.Add(new OracleParameter("P_IDE_NNF", P_IDE_NNF));
// lstParam.Add(new OracleParameter("P_IDE_SERIE", P_IDE_SERIE));
// lstParam.Add(new OracleParameter("P_IDE_DEMI", P_IDE_DEMI));
// lstParam.Add(new OracleParameter("P_COUNT", OracleDbType.Decimal, ParameterDirection.Output));
// return OracleHelper.ExecProcedureNonQuery("PK_NFE.SP_VALIDA_NFE", lstParam, CommandType.StoredProcedure, OracleHelper.BancoOracle.optimus, "P_COUNT");
//}
public int NotaFiscalEntradaValidar(string P_DEST_CNPJ, string P_EMIT_CNPJ, int P_IDE_NNF, int P_IDE_SERIE, DateTime P_IDE_DEMI, string datasource, string schema)
{
List <OracleParameter> lstParam = new List <OracleParameter>();
lstParam.Add(new OracleParameter("P_DEST_CNPJ", P_DEST_CNPJ));
lstParam.Add(new OracleParameter("P_EMIT_CNPJ", P_EMIT_CNPJ));
lstParam.Add(new OracleParameter("P_IDE_NNF", P_IDE_NNF));
lstParam.Add(new OracleParameter("P_IDE_SERIE", P_IDE_SERIE));
lstParam.Add(new OracleParameter("P_IDE_DEMI", P_IDE_DEMI));
lstParam.Add(new OracleParameter("P_COUNT", OracleDbType.Decimal, ParameterDirection.Output));
return(OracleHelper.ExecProcedureNonQuery("PK_NFE.SP_VALIDA_NFE", lstParam, CommandType.StoredProcedure, OracleHelper.BancoOracle.optimus, "P_COUNT", datasource, schema));
}
public int NotaFiscalAtualizarGNREErro(
int cdnotafiscalsaida,
string txerro,
string datasource,
string schema,
int P_TIPO_GNRE
)
{
List <OracleParameter> lstParam = new List <OracleParameter>();
lstParam.Add(new OracleParameter("P_CDNOTAFISCALSAIDA", cdnotafiscalsaida));
lstParam.Add(new OracleParameter("P_TXERROGNRE", txerro));
lstParam.Add(new OracleParameter("P_TIPO_GNRE", P_TIPO_GNRE));
return(OracleHelper.ExecProcedureNonQuery("PK_NFE.SP_GNREATUALIZARERRO", lstParam, CommandType.StoredProcedure, datasource, schema));
}
/// <summary>
/// Grava o log no banco do DOC-e
/// </summary>
/// <param name="txlog">log workflow</param>
/// <param name="nrtempoexecucao">tempo gasto na execução</param>
/// <param name="txexception">exception caso haja</param>
/// <param name="nmservico">nome do servico.método</param>
/// <param name="cnpjcliente">cnpj do cliente que esta usando o serviço</param>
/// <returns></returns>
//public int InserirLogDOCe(string txlog, double nrtempoexecucao, string txexception, string nmservico, string nmmetodo, decimal cnpjcliente, string datasource, string schema)
//{
// List<OracleParameter> lstParam = new List<OracleParameter>();
// lstParam.Add(new OracleParameter("P_TXLOG", OracleDbType.Clob, txlog, ParameterDirection.Input));
// lstParam.Add(new OracleParameter("P_NRTEMPOEXECUCAO", nrtempoexecucao));
// lstParam.Add(new OracleParameter("P_TXEXCEPTION", OracleDbType.Clob, txexception, ParameterDirection.Input));
// lstParam.Add(new OracleParameter("P_NMSERVICO", nmservico));
// lstParam.Add(new OracleParameter("P_CNPJCLIENTE", cnpjcliente));
// lstParam.Add(new OracleParameter("P_NMMETODO", nmmetodo));
// return OracleHelper.ExecProcedureNonQuery("PK_NFELOG.SP_INSERT_LOG", lstParam, CommandType.StoredProcedure, datasource, schema);
//}
public int InserirHistoricoNFE(string cnpj, string nrnota, string serie, string cdnotafiscalsaida, string cdentifilial,
string dtemissao, string cdstatusnfe, string xmlnfeenv, string xmlnfeaut, string datasource, string schema)
{
List <OracleParameter> lstParam = new List <OracleParameter>();
lstParam.Add(new OracleParameter("P_CNPJ", cnpj));
lstParam.Add(new OracleParameter("P_NRNOTA", nrnota));
lstParam.Add(new OracleParameter("P_SERIE", serie));
lstParam.Add(new OracleParameter("P_CDNOTAFISCALSAIDA", cdnotafiscalsaida));
lstParam.Add(new OracleParameter("P_CDENTIFILIAL", cdentifilial));
lstParam.Add(new OracleParameter("P_DTEMISSAO", OracleDbType.Date, Convert.ToDateTime(dtemissao, new CultureInfo("pt-BR")), ParameterDirection.Input));
lstParam.Add(new OracleParameter("P_CDSTATUSNFE", cdstatusnfe));
lstParam.Add(new OracleParameter("P_XMLNFEENV", OracleDbType.Clob, xmlnfeenv, ParameterDirection.Input));
lstParam.Add(new OracleParameter("P_XMLNFEAUT", OracleDbType.Clob, xmlnfeaut, ParameterDirection.Input));
return(OracleHelper.ExecProcedureNonQuery("pk_HISTORICONFE.SP_INSERT_HIST", lstParam, CommandType.StoredProcedure, datasource, schema));
}
//public int NotaFiscalAtualizarStatus(int CdNotaFiscalSaida, int StatusNFe, string erroValidacao = "")
//{
// List<OracleParameter> lstParam = new List<OracleParameter>();
// lstParam.Add(new OracleParameter("P_CDNOTAFISCALSAIDA", CdNotaFiscalSaida));
// lstParam.Add(new OracleParameter("P_CDNFESTATUS", StatusNFe));
// lstParam.Add(new OracleParameter("P_TXNFEERRO", OracleDbType.Clob, erroValidacao, ParameterDirection.Input));
// //P_TXNFEERRO IN NOTAFISCALSAIDA.txnfeerro%TYPE
// return OracleHelper.ExecProcedureNonQuery("PK_NFE.SP_NOTASFISCAISATUALIZAR", lstParam, CommandType.StoredProcedure, OracleHelper.BancoOracle.optimus);
//}
/// <summary>
/// Forma de emissão da NF-e
///1 - Normal;
///2 - Contingência FS
///3 - Contingência SCAN
///4 - Contingência DPEC
///5 - Contingência FSDA
///6 - Contingência SVC - AN
///7 - Contingência SVC - RS
///9 - Contingência off-line NFC-e
/// </summary>
/// <param name="CdNotaFiscalSaida"></param>
/// <param name="StatusNFe"></param>
/// <param name="NrChaveNfeDpec"></param>
/// <param name="datasource"></param>
/// <param name="schema"></param>
/// <param name="erroValidacao"></param>
/// <returns></returns>
public int NotaFiscalAtualizarStatus(int CdNotaFiscalSaida, int StatusNFe, string NrChaveNfeDpec, string datasource, string schema, string erroValidacao = "")
{
List <OracleParameter> lstParam = new List <OracleParameter>();
lstParam.Add(new OracleParameter("P_CDNOTAFISCALSAIDA", CdNotaFiscalSaida));
lstParam.Add(new OracleParameter("P_CDNFESTATUS", StatusNFe));
lstParam.Add(new OracleParameter("P_NRCHAVEDPEC", NrChaveNfeDpec));
lstParam.Add(new OracleParameter("P_TXNFEERRO", OracleDbType.Clob, erroValidacao, ParameterDirection.Input));
/*
* P_CDNOTAFISCALSAIDA IN NOTAFISCALSAIDA.CDNOTAFISCALSAIDA%TYPE,
* P_CDNFESTATUS IN NOTAFISCALSAIDA.CDNFESTATUS%TYPE,
* P_NRCHAVEDPEC IN NOTAFISCALSAIDA.NRNFECHAVEDPEC%TYPE,
* P_TXNFEERRO in notafiscalsaida.txnfeerro%TYPE
*/
return(OracleHelper.ExecProcedureNonQuery("PK_NFE.SP_NOTASFISCAISATUALIZAR", lstParam, CommandType.StoredProcedure, datasource, schema));
}
/*
* PROCEDURE SP_GNREATUALIZAR
* (
* P_CDNOTAFISCALSAIDA IN NOTAFISCALSAIDA.CDNOTAFISCALSAIDA%TYPE,
* P_PROTGNRE IN NOTAFISCALSAIDA.PROTGNRE%TYPE,
* P_DTAUTGNRE IN NOTAFISCALSAIDA.DTAUTGNRE%TYPE,
* P_VRJUROSGNRE IN NOTAFISCALSAIDA.VRJUROSGNRE%TYPE,
* P_VRMULTAGNRE IN NOTAFISCALSAIDA.VRMULTAGNRE%TYPE,
* P_VRPRINCGNRE IN NOTAFISCALSAIDA.VRPRINCGNRE%TYPE,
* P_CODBARRAGNRE IN NOTAFISCALSAIDA.CODBARRAGNRE%TYPE,
* P_CDSTATUSGNRE IN NOTAFISCALSAIDA.CDSTATUSGNRE%TYPE,
* P_TXMESANOREFEGNRE IN NOTAFISCALSAIDA.TXMESANOREFEGNRE%TYPE,
* P_CODREPRENUMEGNRE IN NOTAFISCALSAIDA.CODREPRENUMEGNRE%TYPE,
* P_TXINFOCOMPLEGNRE IN NOTAFISCALSAIDA.TXINFOCOMPLEGNRE%TYPE,
* P_CODRECEITAGNRE IN NOTAFISCALSAIDA.CODRECEITAGNRE%TYPE
* P_TIPO_GNRE
* );
*/
public int NotaFiscalAtualizarGNRE(
int cdnotafiscalsaida,
string protgnre,
DateTime dtautgnre,
double vrjurosgnre,
double vrmultagnre,
double vrprincgnre,
string codbarragnre,
int cdstatusgnre,
string txmesanorefegnre,
string codreprenumegnre,
string txinfocomplegnre,
string codreceitagnre,
string datasource,
string schema,
int P_TIPO_GNRE,
double vratuamonetaria,
DateTime dtlimitepagto
)
{
List <OracleParameter> lstParam = new List <OracleParameter>();
lstParam.Add(new OracleParameter("P_CDNOTAFISCALSAIDA", cdnotafiscalsaida));
lstParam.Add(new OracleParameter("P_PROTGNRE", protgnre));
lstParam.Add(new OracleParameter("P_DTAUTGNRE", dtautgnre));
lstParam.Add(new OracleParameter("P_VRJUROSGNRE", vrjurosgnre));
lstParam.Add(new OracleParameter("P_VRMULTAGNRE", vrmultagnre));
lstParam.Add(new OracleParameter("P_VRPRINCGNRE", vrprincgnre));
lstParam.Add(new OracleParameter("P_CODBARRAGNRE", codbarragnre));
lstParam.Add(new OracleParameter("P_CDSTATUSGNRE", cdstatusgnre));
lstParam.Add(new OracleParameter("P_TXMESANOREFEGNRE", txmesanorefegnre));
lstParam.Add(new OracleParameter("P_CODREPRENUMEGNRE", codreprenumegnre));
lstParam.Add(new OracleParameter("P_TXINFOCOMPLEGNRE", txinfocomplegnre));
lstParam.Add(new OracleParameter("P_CODRECEITAGNRE", codreceitagnre));
lstParam.Add(new OracleParameter("P_TIPO_GNRE", P_TIPO_GNRE));
lstParam.Add(new OracleParameter("P_VRATUAMONEGNRE", vratuamonetaria));
lstParam.Add(new OracleParameter("P_DTLIMITEPAGTOGNRE", dtlimitepagto));
return(OracleHelper.ExecProcedureNonQuery("PK_NFE.SP_GNREATUALIZAR", lstParam, CommandType.StoredProcedure, datasource, schema));
}
public int UpdateStatusInutilizacao(int CDNOTAFISCALINUTNUMERACAO, int cdstatus, int cdstatussefaz, string datasource, string schema)
{
string query = string.Format("UPDATE NOTAFISCALINUTNUMERACAO SET CDSTATUS = {0} , CDSTATUSsefaz = {1} WHERE CDNOTAFISCALINUTNUMERACAO = {2}", cdstatus, cdstatussefaz, CDNOTAFISCALINUTNUMERACAO);
return(OracleHelper.ExecProcedureNonQuery(query, null, CommandType.Text, datasource, schema));
}
public int UpdateStatusCC(int cdnotafiscalsaidaCC, int cdstatus, int cdstatussefaz, string datasource, string schema)
{
string query = string.Format("UPDATE NOTAFISCALSAIDACC SET CDSTATUS = {0} , CDSTATUSsefaz = {1} WHERE CDNOTAFISCALSAIDACC = {2}", cdstatus, cdstatussefaz, cdnotafiscalsaidaCC);
return(OracleHelper.ExecProcedureNonQuery(query, null, CommandType.Text, datasource, schema));
}
/// <summary>
/// Atualiza o status da NFE
/// </summary>
/// <param name="cdnotafiscalsaida"></param>
/// <param name="CdStatusNfe"></param>
/// <returns></returns>
public int UpdateStatusNf(string cdnotafiscalsaida, string CdStatusNfe, string datasource, string schema)
{
string query = string.Format("UPDATE NOTAFISCALSAIDA SET CDNFESTATUS = {0} WHERE CDNOTAFISCALSAIDA = {1}", CdStatusNfe, cdnotafiscalsaida);
return(OracleHelper.ExecProcedureNonQuery(query, null, CommandType.Text, datasource, schema));
}
public int NotaFiscalUpdateEmailEnviado(int CdNotaFiscalSaida, int status, string datasource, string schema)
{
string query = string.Format("UPDATE NOTAFISCALSAIDA SET STEMAILENVIADO = {0} ,TSEMAILENVIADO = SYSDATE WHERE CDNOTAFISCALSAIDA ={1}", status, CdNotaFiscalSaida);
return(OracleHelper.ExecProcedureNonQuery(query, null, CommandType.Text, datasource, schema));
}