public string BuscaRetorno(tcIdentificacaoPrestador Prestador, KryptonLabel lblStatus, ProgressBar ProgresStatus)
{
bool parar = false;
Globais glob = new Globais();
string sMensagemErro = "";
int iCountBuscaRet = 0;
ProgresStatus.Step = 1;
ProgresStatus.Minimum = 0;
ProgresStatus.Maximum = 20;
ProgresStatus.MarqueeAnimationSpeed = 20;
ProgresStatus.Value = 0;
try
{
for (; ; )
{
ProgresStatus.PerformStep();
ProgresStatus.Refresh();
lblStatus.Text = "Sistema tentando buscar retorno!!" + Environment.NewLine + "Tentativas: " + iCountBuscaRet.ToString() + " de 21";
lblStatus.Refresh();
string sRetConsulta = BuscaRetornoWebService(Prestador);
XmlDocument xmlRet = new XmlDocument();
xmlRet.LoadXml(sRetConsulta);
XmlNodeList xNodeList = xmlRet.GetElementsByTagName("ns4:MensagemRetorno");
if (xNodeList.Count > 0)
{
sMensagemErro = "{3}Lote: " + NumeroLote + "{3}{3}Código: {0}{3}{3}Mensagem: {1}{3}{3}Correção: {2}{3}{3}Protocolo: " + Protocolo;
foreach (XmlNode node in xNodeList)
{
sCodigoRetorno = node["ns4:Codigo"].InnerText;
if (sCodigoRetorno.Equals("E4") && iCountBuscaRet <= 20)
{
iCountBuscaRet++;
}
else
{
sMensagemErro = string.Format(sMensagemErro, node["ns4:Codigo"].InnerText,
"Esse RPS ainda não se encontra em nossa base de dados.",
node["ns4:Correcao"].InnerText, Environment.NewLine);
parar = true;
}
}
}
else if (xmlRet.GetElementsByTagName("ns3:CompNfse").Count > 0)
{
this.sCodigoRetorno = "";
sMensagemErro = "";
Globais objGlobais = new Globais();
bool bAlteraDupl = Convert.ToBoolean(objGlobais.LeRegConfig("GravaNumNFseDupl"));
for (int i = 0; i < xmlRet.GetElementsByTagName("ns3:CompNfse").Count; i++)
{
#region Salva Arquivo por arquivo
string sPasta = Convert.ToDateTime(xmlRet.GetElementsByTagName("ns4:InfNfse")[i]["ns4:DataEmissao"].InnerText).ToString("MM/yy").Replace("/", "");
//Numero da nota no sefaz + numero da sequencia no sistema
string sNomeArquivo = sPasta + (xmlRet.GetElementsByTagName("ns4:InfNfse")[i]["ns4:Numero"].InnerText.PadLeft(6, '0'))
+ (xmlRet.GetElementsByTagName("ns4:IdentificacaoRps")[i]["ns4:Numero"].InnerText.PadLeft(6, '0'));
XmlDocument xmlSaveNfes = new XmlDocument();
xmlSaveNfes.LoadXml(xmlRet.GetElementsByTagName("ns4:Nfse")[i].InnerXml);
DirectoryInfo dPastaData = new DirectoryInfo(belStaticPastas.ENVIADOS + "\\Servicos\\" + sPasta);
if (!dPastaData.Exists) { dPastaData.Create(); }
xmlSaveNfes.Save(belStaticPastas.ENVIADOS + "\\Servicos\\" + sPasta + "\\" + sNomeArquivo + "-nfes.xml");
#endregion
objNfseRetorno = new TcInfNfse();
objNfseRetorno.Numero = xmlRet.GetElementsByTagName("ns4:InfNfse")[i]["ns4:Numero"].InnerText;
objNfseRetorno.CodigoVerificacao = xmlRet.GetElementsByTagName("ns4:InfNfse")[i]["ns4:CodigoVerificacao"].InnerText;
tcIdentificacaoRps objIdentRps = BuscatcIdentificacaoRps(xmlRet.GetElementsByTagName("ns4:IdentificacaoRps")[i]["ns4:Numero"].InnerText.PadLeft(6, '0'));
belGerarXML objBelGeraXml = new belGerarXML();
if (belStatic.sNomeEmpresa == "LORENZON")
{
AlteraDuplicataNumNFse(objIdentRps, xmlRet.GetElementsByTagName("ns4:InfNfse")[i]["ns4:Numero"].InnerText);
}
if (xmlRet.GetElementsByTagName("ns4:SubstituicaoNfse")[i] != null)
{
objNfseRetorno.NfseSubstituida = xmlRet.GetElementsByTagName("ns4:SubstituicaoNfse")[i]["ns4:NfseSubstituidora"].InnerText;
}
objNfseRetorno.IdentificacaoRps = objIdentRps;
objListaNfseRetorno.Add(objNfseRetorno);
}
parar = true;
}
if (parar) break;
}
return sMensagemErro;
}
catch (Exception ex)
{
throw ex;
}
}
public string RetornaBlob(StringBuilder sComando, string sEmp, FbConnection Conn, belGerarXML objbelGeraXml)
{
string texto = "";
FbCommand comando = new FbCommand(sComando.ToString(), Conn);
FbDataReader Reader = comando.ExecuteReader();
Byte[] blob = null;
MemoryStream ms = new MemoryStream();
while (Reader.Read())
{
blob = new Byte[(Reader.GetBytes(0, 0, null, 0, int.MaxValue))];
try
{
Reader.GetBytes(0, 0, blob, 0, blob.Length);
}
catch
{
texto = "";
//return texto;
}
ms = new MemoryStream(blob);
}
StreamReader Ler = new StreamReader(ms);
Ler.ReadLine();
while (Ler.Peek() != -1)
{
texto += Ler.ReadLine();
}
//Claudinei - o.s. 24078 - 04/03/2010
if ((belStatic.sNomeEmpresa == "MACROTEX") || (belStatic.sNomeEmpresa == "PAVAX"))
{
string sVendedor = string.Empty;
string sPedidoCliente = string.Empty;
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
FbCommand cmd = new FbCommand(sComando.ToString().Replace("nf.ds_anota ,", ""), Conn);
cmd.ExecuteNonQuery();
FbDataReader dr = cmd.ExecuteReader();
dr.Read();
sVendedor = dr["nm_vend"].ToString();
sPedidoCliente = dr["DS_DOCORIG"].ToString();
if ((belStatic.sNomeEmpresa == "MACROTEX"))
{
if (texto == "")
{
texto += string.Format("Vendedor.: {0} Pedido N.: {1}",
sVendedor,
sPedidoCliente);
}
else
{
texto += string.Format(" Vendedor.: {0} Pedido N.: {1}",
sVendedor,
sPedidoCliente);
}
}
else
{
if (texto == "")
{
texto += string.Format("Pedido N.: {0}",
sPedidoCliente);
}
else
{
texto += string.Format(" Pedido N.: {0}",
sPedidoCliente);
}
}
}
if (((belStatic.sNomeEmpresa == "MOGPLAST") || (belStatic.sNomeEmpresa == "TSA")) && (sEmp == "003"))
{
string sNFOrigem = string.Empty;
string sEmiOrigem = string.Empty;
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
FbCommand cmd = new FbCommand(sComando.ToString().Replace("nf.ds_anota ,", ""), Conn);
cmd.ExecuteNonQuery();
FbDataReader dr = cmd.ExecuteReader();
dr.Read();
//Claudinei - o.s. sem - 02/03/2010
if (dr["cd_nfseq_fat_origem"].ToString() != "")
{
//Fim - Claudinei - o.s. sem - 02/03/2010
StringBuilder sSqlNFOrigem = new StringBuilder();
sSqlNFOrigem.Append("Select ");
sSqlNFOrigem.Append("cd_notafis, ");
sSqlNFOrigem.Append("dt_emi ");
sSqlNFOrigem.Append("From NF ");
sSqlNFOrigem.Append("Where nf.cd_empresa = '");
sSqlNFOrigem.Append("001");
sSqlNFOrigem.Append("'");
sSqlNFOrigem.Append(" and ");
sSqlNFOrigem.Append("cd_nfseq = '");
sSqlNFOrigem.Append(dr["cd_nfseq_fat_origem"].ToString());
sSqlNFOrigem.Append("'");
FbCommand cmdNFOrigem = new FbCommand(sSqlNFOrigem.ToString(), Conn);
cmdNFOrigem.ExecuteNonQuery();
FbDataReader drNFOrigem = cmdNFOrigem.ExecuteReader();
drNFOrigem.Read();
sNFOrigem = drNFOrigem["cd_notafis"].ToString();
sEmiOrigem = System.DateTime.Parse(drNFOrigem["dt_emi"].ToString()).ToString("dd/MM/yyyy");
if (texto == "")
{
texto += string.Format("DEV TOTAL REF A NF {0} DE {1}",
sNFOrigem,
sEmiOrigem);
}
else
{
texto += string.Format(" DEV TOTAL REF A NF {0} DE {1}",
sNFOrigem,
sEmiOrigem);
}
}
}
return Util.Util.TiraCaracterEstranho(texto).Trim();
}
/// <summary>
/// Gera o numero do Lote.
/// </summary>
/// <param name="sEmp"></param>
/// <returns></returns>
public string NomeArqNFe(string sEmp)
{
string sNomeArqNfe = "";
belGerarXML BuscaGen = new belGerarXML();
sNomeArqNfe = "Nfe_" + sEmp + BuscaGen.RetornaGenString("SP_CHAVEPRI", 15) + ".xml";
return sNomeArqNfe;
}
private decimal BuscaDescTotal(string sEmp, string sNF, FbConnection Conn)
{
decimal dvlTotProd = 0;
decimal dvlTotItens = 0;
decimal vl_Desconto = 0;
decimal dQTItens = 0;
StringBuilder SqlTotProd = new StringBuilder();
SqlTotProd.Append("Select (vl_totprod + vl_servico) vl_totprod ");
SqlTotProd.Append("from nf ");
SqlTotProd.Append("where ");
SqlTotProd.Append("(nf.cd_empresa ='");
SqlTotProd.Append(sEmp);
SqlTotProd.Append("') and ");
SqlTotProd.Append("(nf.cd_nfseq = '");
SqlTotProd.Append(sNF);
SqlTotProd.Append("') ");
using (FbCommand cmd = new FbCommand(SqlTotProd.ToString(), Conn))
{
dvlTotProd = Math.Round(Convert.ToDecimal(cmd.ExecuteScalar()), 2); //Claudinei - o.s. 24248 - 26/03/2010
}
StringBuilder SqlTotItens = new StringBuilder();
SqlTotItens.Append("Select sum(vl_totbruto) ");
SqlTotItens.Append("from movitem ");
SqlTotItens.Append("where ");
SqlTotItens.Append("(movitem.cd_empresa ='");
SqlTotItens.Append(sEmp);
SqlTotItens.Append("') and ");
SqlTotItens.Append("(movitem.cd_nfseq = '");
SqlTotItens.Append(sNF);
SqlTotItens.Append("') ");
using (FbCommand cmd = new FbCommand(SqlTotItens.ToString(), Conn))
{
dvlTotItens = Math.Round(Convert.ToDecimal(cmd.ExecuteScalar()), 2); //Claudinei - o.s. 24248 - 26/03/2010
}
StringBuilder SqlQtItens = new StringBuilder();
SqlQtItens.Append("Select count(nr_lanc) ");
SqlQtItens.Append("from movitem ");
SqlQtItens.Append("where ");
SqlQtItens.Append("(movitem.cd_empresa ='");
SqlQtItens.Append(sEmp);
SqlQtItens.Append("') and ");
SqlQtItens.Append("(movitem.cd_nfseq = '");
SqlQtItens.Append(sNF);
SqlQtItens.Append("') ");
using (FbCommand cmd = new FbCommand(SqlQtItens.ToString(), Conn))
{
dQTItens = Math.Round(Convert.ToDecimal(cmd.ExecuteScalar()), 2); //Claudinei - o.s. 24248 - 26/03/2010
}
if (dvlTotProd < dvlTotItens)
{
vl_Desconto = ((dvlTotItens - dvlTotProd) / dQTItens);
}
belGerarXML cx = new belGerarXML();
if (pbIndustri)
{
vl_Desconto = 0;
}
else if (belStatic.sNomeEmpresa == "EMEB")
{
vl_Desconto = 0;
}
return vl_Desconto;
}
public belInfAdic BuscaObs(string sEmp,
string sNF,
belDest objbeldest,
List<belDet> objbelDet, belGerarXML objbelGeraXml)
{
belInfAdic objinfadic = new belInfAdic();
try
{
StringBuilder sSql = new StringBuilder();
//Campos do Select
sSql.Append("Select ");
sSql.Append("nf.ds_anota ");
if (((belStatic.sNomeEmpresa == "MOGPLAST") || (belStatic.sNomeEmpresa == "TSA")) && (sEmp == "003"))
{
sSql.Append(", nf.cd_nfseq_fat_origem ");
}
if ((belStatic.sNomeEmpresa == "MACROTEX") || (belStatic.sNomeEmpresa == "PAVAX"))
{
sSql.Append(", coalesce(vendedor.nm_vend,'0')nm_vend , ");
sSql.Append("coalesce(nf.DS_DOCORIG,'0')DS_DOCORIG ");
}
//Tabela
sSql.Append("From NF ");
//Relacionamentos
sSql.Append("left join vendedor on (vendedor.cd_vend = nf.cd_vend1) ");
//Where
sSql.Append("Where ");
sSql.Append("(NF.cd_empresa ='");
sSql.Append(sEmp);
sSql.Append("')");
sSql.Append(" and ");
sSql.Append("(nf.cd_nfseq = '");
sSql.Append(sNF);
sSql.Append("') ");
string sObs = "";
sObs = RetornaBlob(sSql, sEmp, cx.get_Conexao(), objbelGeraXml);
if (sObs.IndexOf("\\fs") != -1)// DIEGO - OS_24854
{
sObs = sObs.Substring((sObs.IndexOf("\\fs") + 6), sObs.Length - (sObs.IndexOf("\\fs") + 6));
}
if (belStatic.sNomeEmpresa == "MARPA")
{
sObs += MontaObsAgrup(sEmp, sNF, cx.get_Conexao());
}
Globais LeRegWin = new Globais();
string sBanco = LeRegWin.LeRegConfig("BancoDados");
{
//Fim - Danner - o.s. 24383 - 22/04/2010
//belGerarXML cx = new belGerarXML();
//FbConnection Conn = cx.Conn;
try
{
if (sBanco.ToUpper().IndexOf("CERAMICAC0") == -1) //Claudinei - o.s. 24245 - 08/03/2010
{
StringBuilder sSuframa = new StringBuilder();
sSuframa.Append("Select First 1 ");
sSuframa.Append("nf.ds_anota, ");
sSuframa.Append("clifor.st_descsuframa, ");
sSuframa.Append("clifor.cd_suframa, ");
sSuframa.Append("clifor.ST_PISCOFINS_SUFRAMA, ");
sSuframa.Append("nf.vl_aliqcofins_suframa, ");
sSuframa.Append("nf.vl_aliqpis_suframa, ");
sSuframa.Append("nf.vl_cofins_suframa, ");
sSuframa.Append("NF.vl_pis_suframa, ");
//Claudinei - o.s. 23683 - 11/09/2009
sSuframa.Append("(select Sum(movitem.vl_descsuframa) from movitem where (movitem.cd_empresa = nf.cd_empresa) and (movitem.cd_nfseq = nf.cd_nfseq)) vl_suframa, ");
sSuframa.Append("icm.vl_aliquot vl_persuframa ");
//Fim - Claudinei - o.s. 23683 - 11/09/2009
//Claudinei - o.s. 23827 - 17/11/2009
sSuframa.Append(", ");
sSuframa.Append("case when empresa.vl_aliqfatcred > 0 then ");
sSuframa.Append("(nf.vl_totnf * empresa.vl_aliqfatcred)/100 ");
sSuframa.Append("else ");
sSuframa.Append("0 ");
sSuframa.Append("end aliq, ");
sSuframa.Append("empresa.vl_aliqfatcred, ");
sSuframa.Append("coalesce(tpdoc.st_hevenda,'N') st_hevenda ");
//Fim - Claudinei - o.s. 23827 - 17/11/2009
//Tabela
sSuframa.Append("From NF ");
//Relacionamentos
sSuframa.Append("left join clifor on (clifor.cd_clifor = nf.cd_clifor) ");
sSuframa.Append("left join icm on (icm.cd_ufnor = clifor.cd_ufnor) ");
sSuframa.Append("left join movitem on (movitem.cd_empresa = nf.cd_empresa) ");
sSuframa.Append("and ");
sSuframa.Append("(movitem.cd_nfseq = nf.cd_nfseq) ");
//Claudinei - o.s. 23827 - 17/11/2009
sSuframa.Append("Inner join ");
sSuframa.Append("Empresa on ");
sSuframa.Append("(Empresa.cd_empresa = nf.cd_empresa) ");
sSuframa.Append("Left join ");
sSuframa.Append("TPDoc on ");
sSuframa.Append("(TPDoc.cd_tipodoc = nf.cd_tipodoc) ");
//Fim - Claudinei - o.s. 23827 - 17/11/2009
//Where
sSuframa.Append("Where ");
sSuframa.Append("(NF.cd_empresa ='");
sSuframa.Append(sEmp);
sSuframa.Append("')");
sSuframa.Append(" and ");
sSuframa.Append("(nf.cd_nfseq = '");
sSuframa.Append(sNF);
sSuframa.Append("') ");
//Conn.Open();
FbCommand cmdSuframa = new FbCommand(sSuframa.ToString(), cx.get_Conexao());
cmdSuframa.ExecuteNonQuery();
FbDataReader drSuframa = cmdSuframa.ExecuteReader();
drSuframa.Read();
if (drSuframa["st_descsuframa"].ToString() == "S")
{
//Claudinei - o.s. 23683 - 11/09/2009
decimal dvlSuframa = Math.Round(Convert.ToDecimal(drSuframa["vl_suframa"].ToString()), 2); //Claudinei - o.s. 24248 - 26/03/2010
decimal dvlPerSuframa = Math.Round(Convert.ToDecimal(drSuframa["vl_persuframa"].ToString()), 2); //Claudinei - o.s. 24248 - 26/03/2010
//Fim - Claudinei - o.s. 23683 - 11/09/2009
if (sObs.Trim() != "")
{
sObs += string.Format(" - DESCONTO DE {0:C2} REF. AO ICMS {1:f2}% CODIGO SUFRAMA: {2}",
dvlSuframa,
dvlPerSuframa,
drSuframa["cd_suframa"].ToString());
}
else
{
sObs += string.Format("DESCONTO DE {0:C2} REF. AO ICMS 7.00% CODIGO SUFRAMA: {1}",
dvlSuframa,
drSuframa["cd_suframa"].ToString());
}
}
//Claudinei - o.s. sem - 01/09/2009
if (drSuframa["ST_PISCOFINS_SUFRAMA"].ToString() == "S")
{
decimal dvl_aliqcofins_suframa = Math.Round(Convert.ToDecimal(drSuframa["vl_aliqcofins_suframa"].ToString()), 2); //Claudinei - o.s. 24248 - 26/03/2010
decimal dvl_cofins_suframa = Math.Round(Convert.ToDecimal(drSuframa["vl_cofins_suframa"].ToString()), 2); //Claudinei - o.s. 24248 - 26/03/2010
decimal dvl_aliqpis_suframa = Math.Round(Convert.ToDecimal(drSuframa["vl_aliqpis_suframa"].ToString()), 2); //Claudinei - o.s. 24248 - 26/03/2010
decimal dvl_pis_suframa = Math.Round(Convert.ToDecimal(drSuframa["vl_pis_suframa"].ToString()), 2); //Claudinei - o.s. 24248 - 26/03/2010
if (sObs.Trim() != "")
{
sObs += string.Format(" - ABATIMENTO COFINS ({0}%) - VALOR R$ {1} - ABATIMENTO PIS ({2}%) - VALOR R$ {3} ",
dvl_aliqcofins_suframa.ToString("#0.00").Replace(',', '.'),
dvl_cofins_suframa.ToString("#0.00").Replace(',', '.'),
dvl_aliqpis_suframa.ToString("#0.00").Replace(',', '.'),
dvl_pis_suframa.ToString("#0.00").Replace(',', '.'));
}
else
{
sObs += string.Format("ABATIMENTO COFINS ({0}%) - VALOR R$ {1} - ABATIMENTO PIS ({2}%) - VALOR R$ {3} ",
dvl_aliqcofins_suframa.ToString("#0.0000").Replace(',', '.'),
dvl_cofins_suframa.ToString("#0.0000").Replace(',', '.'),
dvl_aliqpis_suframa.ToString("#0.0000").Replace(',', '.'),
dvl_pis_suframa.ToString("#0.0000").Replace(',', '.'));
}
}
decimal dvlnf = 0;
if (drSuframa["aliq"].ToString() != "")
{
dvlnf = Math.Round(Convert.ToDecimal(drSuframa["aliq"].ToString()), 2); //Claudinei - o.s. 24248 - 26/03/2010
}
}
StringBuilder sDevolucao = new StringBuilder();
//Tabela
sDevolucao.Append("Select ");
sDevolucao.Append("movitem.cd_doc, ");
sDevolucao.Append("nf.cd_clifor, ");
sDevolucao.Append("nf.dt_emi, ");
sDevolucao.Append("movensai.dt_emi EmissaoEntrada, ");
sDevolucao.Append("movitem.vl_totbruto ");
sDevolucao.Append("From Movitem ");
sDevolucao.Append("inner join NF on ");
sDevolucao.Append("(nf.cd_empresa = Movitem.cd_empresa) ");
sDevolucao.Append("and ");
sDevolucao.Append("(nf.cd_nfseq = movitem.cd_nfseq) ");
sDevolucao.Append("inner join opereve on ");
sDevolucao.Append("(opereve.cd_oper = movitem.cd_oper) ");
sDevolucao.Append("left join movensai on (movensai.cd_empresa = movitem.cd_empresa) ");
sDevolucao.Append("and ");
sDevolucao.Append("(movensai.cd_doc = movitem.cd_doc) ");
sDevolucao.Append("and ");
sDevolucao.Append("(movensai.cd_clifor = nf.cd_clifor) ");
//Where
sDevolucao.Append("Where ");
sDevolucao.Append("(Movitem.cd_empresa ='");
sDevolucao.Append(sEmp);
sDevolucao.Append("')");
sDevolucao.Append(" and ");
sDevolucao.Append("(Movitem.cd_nfseq = '");
sDevolucao.Append(sNF);
sDevolucao.Append("') ");
sDevolucao.Append("and ");
sDevolucao.Append("(opereve.ST_ESTTERC = 'S') ");
sDevolucao.Append("and ");
sDevolucao.Append("Movitem.cd_oper <> '202' ");
sDevolucao.Append("and ");
sDevolucao.Append("Movitem.cd_oper <> '227' ");//TESTE-DANI
sDevolucao.Append("Order by movitem.cd_doc");
//if (Conn.State != ConnectionState.Open)
{
//Conn.Open();
}
FbCommand cmdDevolucoes = new FbCommand(sDevolucao.ToString(), cx.get_Conexao());
cmdDevolucoes.ExecuteNonQuery();
FbDataReader drDevolucoes = cmdDevolucoes.ExecuteReader();
List<strucDevolucoes> Devolucoes = new List<strucDevolucoes>();
decimal dvlTotBruto = 0;
string scdDoc = string.Empty;
while (drDevolucoes.Read())
{
if (scdDoc != drDevolucoes["cd_doc"].ToString())
{
dvlTotBruto = 0;
}
dvlTotBruto += Math.Round(Convert.ToDecimal(drDevolucoes["vl_totbruto"].ToString()), 2); //Claudinei - o.s. 24248 - 26/03/2010
StringBuilder sStore = new StringBuilder();
sStore.Append("SELECT ");
sStore.Append("QT_SALDOEN ");
sStore.Append("FROM SP_SALDOTER('");
sStore.Append(sEmp);
sStore.Append("', '");
sStore.Append(drDevolucoes["cd_clifor"].ToString());
sStore.Append("', '");
sStore.Append(" "); //Claudinei - o.s. 24075 - 29/01/2010
sStore.Append("', '");
sStore.Append("|||||||"); //Claudinei - o.s. 24075 - 29/01/2010
sStore.Append("', '");
sStore.Append("X");
sStore.Append("', '");
sStore.Append("N");
sStore.Append("', '");
sStore.Append(sNF);
sStore.Append("', '");
sStore.Append(Convert.ToDateTime(drDevolucoes["dt_emi"]).ToString("dd.MM.yyyy"));
sStore.Append("') ");
sStore.Append("where SP_SALDOTER.cd_doc ='");
sStore.Append(drDevolucoes["cd_doc"].ToString().Trim());
sStore.Append("'");
sStore.Append(" and ");
sStore.Append("SP_SALDOTER.qt_saldoen > 0");
FbCommand cmd = new FbCommand();
cmd.Connection = cx.get_Conexao();
cmd.CommandText = sStore.ToString();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
Int32 iSaldoTer = Convert.ToInt32(cmd.ExecuteScalar());
strucDevolucoes Devolucao = new strucDevolucoes();
if (drDevolucoes["cd_doc"].ToString() != "")
{
Devolucao.scdNotafis = drDevolucoes["cd_doc"].ToString();
if (drDevolucoes["EmissaoEntrada"] != System.DBNull.Value)
{
Devolucao.dDtEmi = Convert.ToDateTime(drDevolucoes["EmissaoEntrada"]);
}
Devolucao.dValorNF = dvlTotBruto.ToString("#0.00");
Devolucao.sSaldo = (iSaldoTer > 0 ? "Parcial" : "Total");
if (!Devolucoes.Exists(c => c.scdNotafis == Devolucao.scdNotafis))
{
Devolucoes.Add(Devolucao);
}
else
{
for (int i = 0; i < Devolucoes.Count; i++)
{
if ((Devolucoes[i].scdNotafis == Devolucao.scdNotafis) && (Devolucoes[i].dDtEmi == Devolucao.dDtEmi)) //OS_25220
{
Devolucoes[i] = Devolucao;
break;
}
}
}
}
scdDoc = drDevolucoes["cd_doc"].ToString();
}
if ((belStatic.sNomeEmpresa != "JAMAICA")) //25618
{
for (int i = 0; i < Devolucoes.Count; i++)
{
if (sObs.Trim().Length > 0)
{
sObs += string.Format(" - Retorno {0} ref. sua NF {1} de {2} de valor R$ {3}", //Claudinei - o.s. 24043 - 25/01/2010
Devolucoes[i].sSaldo,
Devolucoes[i].scdNotafis,
Devolucoes[i].dDtEmi.ToString("dd/MM/yyyy"),
Devolucoes[i].dValorNF); //Claudinei - o.s. 24043 - 25/01/2010
}
else
{
sObs += string.Format("Retorno {0} ref. sua NF {1} de {2} de valor R$ {3}", //Claudinei - o.s. 24043 - 25/01/2010
Devolucoes[i].sSaldo,
Devolucoes[i].scdNotafis,
Devolucoes[i].dDtEmi.ToString("dd/MM/yyyy"),
Devolucoes[i].dValorNF); //Claudinei - o.s. 24043 - 25/01/2010
}
}
}
string sMensagemSuperSimples = MensagemSuperSimples(sEmp, sNF, cx.get_Conexao());
bool cfopsValidos = (objbelDet.Count(p => (p.belProd.Cfop.Equals("5101"))
|| (p.belProd.Cfop.Equals("6107"))
|| (p.belProd.Cfop.Equals("6101"))) > 0 ? true : false);
if ((sMensagemSuperSimples != "")
&& (objbeldest.Cnpj != null && objbeldest.Cnpj != "")
|| (belStatic.sNomeEmpresa == "TERRAVIS")
|| (belStatic.sNomeEmpresa == "TREVISO")
&& (cfopsValidos)) // OS_25182
{
if (sObs.Trim().Length > 0)
{
sObs += " - " + sMensagemSuperSimples;
}
else
{
sObs += sMensagemSuperSimples;
}
}
//Claudinei - o.s. 24118 - 11/02/2010
}
catch (Exception Ex)
{
sExecao = " - Problemas ao Buscar Suframa";
throw new Exception(Ex.Message + sExecao);
}
finally
{
//Conn.Close();
}
}
// Diego - 22/06/2010 - OS_24576
//Monta Mensagem de PIS, COFINS
try
{
//belGerarXML cx = new belGerarXML();
//FbConnection Conn = cx.Conn;
//if (Conn.State != ConnectionState.Open)
{
//Conn.Open();
}
StringBuilder sQuery = new StringBuilder();
sQuery.Append("SELECT ");
sQuery.Append("NF.VL_COFINS, NF.VL_PIS, ");
sQuery.Append("empresa.vl_aliqpis_suframa,empresa.vl_aliqcofins_suframa, ");
sQuery.Append("CLIFOR.st_desc_piscofins_dupl ");
sQuery.Append("from nf inner join empresa ");
sQuery.Append("on (nf.cd_empresa = empresa.cd_empresa)");
sQuery.Append("inner join clifor ");
sQuery.Append("on (nf.cd_clifor = clifor.cd_clifor) ");
sQuery.Append("where (empresa.cd_empresa = '");
sQuery.Append(sEmp);
sQuery.Append("') ");
sQuery.Append("and ( nf.cd_nfseq = '");
sQuery.Append(sNF);
sQuery.Append("') ");
FbCommand cmd = new FbCommand(sQuery.ToString(), cx.get_Conexao());
FbDataReader drPisCofins = cmd.ExecuteReader();
while (drPisCofins.Read())
{
if ((drPisCofins["st_desc_piscofins_dupl"].ToString() == "S"))
{
if ((drPisCofins["vl_aliqpis_suframa"].ToString() != "") && (drPisCofins["vl_aliqcofins_suframa"].ToString() != ""))
{
string sMensagemPisCofins = "(PIS e COFINS retido conforme artigo 3º paragrafo 4º da |lei 10.485/02, PIS "
+ drPisCofins["vl_aliqpis_suframa"].ToString()
+ "% R$" + drPisCofins["VL_PIS"].ToString()
+ " , COFINS "
+ drPisCofins["vl_aliqcofins_suframa"].ToString()
+ "% R$"
+ drPisCofins["VL_COFINS"].ToString()
+ " Total R$" + ((Convert.ToDouble(drPisCofins["VL_PIS"].ToString())) + (Convert.ToDouble(drPisCofins["VL_COFINS"].ToString()))).ToString() + ")";
sObs += (sObs != "" ? " - " : "") + sMensagemPisCofins;
}
}
}
}
catch (Exception Ex)
{
throw Ex;
}// Diego - 22/06/2010 - OS_24576 - FIM
// Diego - 15/07/2010 - OS_24665
//Obs de ICMS Recolhido por Substituição
try
{
//belGerarXML cx = new belGerarXML();
//FbConnection Conn = cx.Conn;
StringBuilder sQuery = new StringBuilder();
sQuery.Append("SELECT ");
sQuery.Append("movitem.cd_sittrib, EMPRESA.ST_SUPERSIMPLES, EMPRESA.ST_IMP_SUPERSIMPLES ");
sQuery.Append("from empresa INNER JOIN movitem ON (EMPRESA.cd_empresa = movitem.cd_empresa)");
sQuery.Append("where (empresa.cd_empresa = '");
sQuery.Append(sEmp);
sQuery.Append("') ");
sQuery.Append("and ( movitem.cd_nfseq = '");
sQuery.Append(sNF);
sQuery.Append("') ");
FbCommand cmd = new FbCommand(sQuery.ToString(), cx.get_Conexao());
FbDataReader drIcmsRecolhido = cmd.ExecuteReader();
string sMensagemIcmsRecolhido = "";
while (drIcmsRecolhido.Read())
{
if ((drIcmsRecolhido["ST_SUPERSIMPLES"].ToString() == "S") && (drIcmsRecolhido["ST_IMP_SUPERSIMPLES"].ToString() == "S"))
{
if ((drIcmsRecolhido["cd_sittrib"].ToString().Equals("010")) ||
(drIcmsRecolhido["cd_sittrib"].ToString().Equals("030")) ||
(drIcmsRecolhido["cd_sittrib"].ToString().Equals("060")) ||
(drIcmsRecolhido["cd_sittrib"].ToString().Equals("070")))
{
sMensagemIcmsRecolhido = "ICMS RECOLHIDO POR SUBSTITUICAO TRIBUTARIA CONFORME DECRETO 54251/09 ART 313 RICMS/2000";
break;
}
}
}
if (sMensagemIcmsRecolhido != "")
{
sObs += (sObs.Trim() != "" ? " - " : "") + sMensagemIcmsRecolhido;
}
}
catch (Exception Ex)
{
throw Ex;
}
// Diego - 15/07/2010 - OS_24665 - FIM
//OS_25201 - DIEGO
if (LeRegWin.LeRegConfig("TotalizaCFOP").Equals("True"))
{
sObs += " " + MessagemTotalizaCFOP(sEmp, sNF, cx.get_Conexao());
}//OS_25201 - FIM
//OS_25224 - INICIO
string sCNPJdest = (objbeldest.Cnpj != null ? Util.Util.RetiraCaracterCNPJ(objbeldest.Cnpj) : "");
try
{
//belGerarXML cx = new belGerarXML();
//FbConnection Conn = cx.Conn;
if (objbelGeraXml.Equals("JAMAICA"))
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("select coalesce(clifor.cd_alter2,'') cd_alter2 from clifor ");
sQuery.Append("where clifor.cd_cgc ='" + sCNPJdest + "'");
FbCommand cmd = new FbCommand(sQuery.ToString(), cx.get_Conexao());
FbDataReader dr = cmd.ExecuteReader();
string sMesgCodDest = "";
while (dr.Read())
{
sMesgCodDest = dr["cd_alter2"].ToString();
}
if (sMesgCodDest != "")
{
sObs = "<<COD FORNECEDOR " + sMesgCodDest + ">> " + sObs;
}
}
if (belStatic.sNomeEmpresa.Equals("LORENZON"))
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("select prazos.ds_prazo, vendedor.nm_vend , clifor.cd_clifor from nf ");
sQuery.Append("inner join clifor on nf.cd_clifor = clifor.cd_clifor ");
sQuery.Append("inner join prazos on nf.cd_prazo = prazos.cd_prazo ");
sQuery.Append(" inner join vendedor on nf.cd_vendint = vendedor.cd_vend ");
sQuery.Append("where nf.cd_nfseq = '" + sNF + "' ");
sQuery.Append("and nf.cd_empresa = '" + sEmp + "' ");
FbCommand cmd = new FbCommand(sQuery.ToString(), cx.get_Conexao());
FbDataReader dr = cmd.ExecuteReader();
string sMsgLorenzon = "";
while (dr.Read())
{
sMsgLorenzon = "COND.PGTO = " + dr["ds_prazo"].ToString() + " | VENDEDOR = " + dr["nm_vend"].ToString() + " | COD. CLIENTE = " + dr["cd_clifor"].ToString();
}
if (sMsgLorenzon != "")
{
sObs = "<< " + sMsgLorenzon + " >> " + sObs;
}
}
}
catch (Exception ex)
{
throw ex;
}
//OS_25224 - FIM
if (sObs.Trim() != "")
{
objinfadic.Infcpl = sObs.Trim();
}
//Fim - Montagem do XML
}
catch (Exception Ex)
{
sExecao = " - Problemas nas Obs´s da Nota";
throw new Exception(Ex.Message + sExecao);
}
return objinfadic; //Danner - o.s. sem - 16/11/2009
}
public belTransp BuscaFrete(string sEmp,
string sNF)
{
belGerarXML objbelGeraXml = new belGerarXML();
belTransp objtransp = new belTransp();
string snm_Cliente = belStatic.sNomeEmpresa;
try
{
//Conn.Open();
StringBuilder sSql = new StringBuilder();
//Campos do Select
sSql.Append("Select ");
sSql.Append("coalesce(nf.st_frete, 0) modFrete, ");
//Clauidnei - o.s. 23507 - 10-07-2009
sSql.Append("transpor.st_pessoaj, ");
sSql.Append("transpor.cd_cpf, ");
//Fim - Clauidnei - o.s. 23507 - 10-07-2009
sSql.Append("transpor.cd_cgc CNPJ, ");
sSql.Append("transpor.nm_trans xNome, ");
sSql.Append("transpor.cd_insest IE, ");
sSql.Append("transpor.ds_endnor xEnder, ");
sSql.Append("transpor.nm_cidnor xMun, ");
sSql.Append("transpor.cd_ufnor UF, ");
sSql.Append("coalesce(nf.qt_volumes, 0) qVol, ");
sSql.Append((snm_Cliente == "ZINCOBRIL" ? "coalesce(nf.ds_especie, '') esp, " : "coalesce(nf.ds_especie, 'VOLUME') esp, "));//os_25207
sSql.Append("COALESCE(nf.vl_pesoliq, 0) pesoL, ");
sSql.Append("COALESCE(nf.vl_pesobru,0) pesoB, ");
sSql.Append((snm_Cliente == "ZINCOBRIL" ? "COALESCE(nf.ds_marca, '') marca, " : "COALESCE(nf.ds_marca, 'MARCA') marca, "));//os_25207
//Claudinei - o.s. 23674 - 10/09/2009
//Danner - o.s. 24322 - 31/03/2010
//sSql.Append(", transpor.cd_placa placa, ");
sSql.Append("CASE WHEN COALESCE (NF.cd_placa, '') <> '' then NF.cd_placa ELSE TRANSPOR.cd_placa END placa,");
sSql.Append("transpor.cd_ufvei UF, ");
sSql.Append("transpor.ds_rntc RNTC ");
//Fim - Claudinei - o.s. 23674 - 10/09/2009
//Danner - o.s. 24385 - 26/04/2010
sSql.Append(", coalesce(nf.ds_numero,'') nVol ");
//Fim - Danner - o.s. 24385 - 26/04/2010
//Tabela
sSql.Append("From NF ");
//Relacionamentos
sSql.Append("inner join transpor on (transpor.cd_trans = nf.cd_trans) ");
//Where
sSql.Append("Where ");
sSql.Append("(NF.cd_empresa ='");
sSql.Append(sEmp);
sSql.Append("')");
sSql.Append(" and ");
sSql.Append("(nf.cd_nfseq = '");
sSql.Append(sNF);
sSql.Append("') ");
//Claudinei - o.s 23507 - 25/05/2009
FbCommand cmdTranspor = new FbCommand(sSql.ToString(), cx.get_Conexao());
cx.Open_Conexao();
cmdTranspor.ExecuteNonQuery();
FbDataReader drTranspor = cmdTranspor.ExecuteReader();
if (drTranspor.Read()) //Claudinei - o.s. 24210 - 03/03/2010
{
StringBuilder sSql2 = new StringBuilder();
sSql2.Append("Select ");
sSql2.Append("transpor.nm_trans xNome ");
//Tabela
sSql2.Append("From NF ");
//Relacionamentos
sSql2.Append("inner join transpor on (transpor.cd_trans = nf.cd_trans) ");
//Where
sSql2.Append("Where ");
sSql2.Append("(NF.cd_empresa ='");
sSql2.Append(sEmp);
sSql2.Append("')");
sSql2.Append(" and ");
sSql2.Append("(nf.cd_nfseq = '");
sSql2.Append(sNF);
sSql2.Append("') ");
string razao_transp = string.Empty;
using (FbCommand cmd = new FbCommand(sSql2.ToString(), cx.get_Conexao()))
{
cx.Open_Conexao();
razao_transp = Convert.ToString(cmd.ExecuteScalar()).Trim();
}
if (drTranspor["modFrete"].ToString() == "0") // Remetnete(Emitente)
{
objtransp.Modfrete = "0";
}
else if (drTranspor["modFrete"].ToString() == "1") // destinatario
{
objtransp.Modfrete = "1";
}
else if (drTranspor["modFrete"].ToString() == "2")
{
objtransp.Modfrete = "2";
}
else
{
objtransp.Modfrete = "9";
}
belTransportadora objtransportadora = new belTransportadora();
if (drTranspor["st_pessoaj"].ToString() == "S")
{
if (drTranspor["CNPJ"].ToString() != "")
{
objtransportadora.Cnpj = belUtil.TiraSimbolo(drTranspor["CNPJ"].ToString().PadLeft(14, '0'), "");
}
}
else
{
if (drTranspor["CD_CPF"].ToString() != "")
{
objtransportadora.Cpf = belUtil.TiraSimbolo(drTranspor["CD_CPF"].ToString().PadLeft(11, '0'), "");
}
}
if (drTranspor["xnome"] != null)
{
razao_transp = drTranspor["xNome"].ToString();
}
if (razao_transp != "")
{
int iTamanho = razao_transp.Length - 1;
if (iTamanho > 59)
{
iTamanho = 59;
// Diego - 0S 24039 - 26-01-10
objtransportadora.Xnome = belUtil.TiraSimbolo(razao_transp.ToString().Substring(0, iTamanho), "");
}
else
{
objtransportadora.Xnome = belUtil.TiraSimbolo(razao_transp.ToString().Substring(0, razao_transp.Length), "");
}
// Diego - 0S 24039 - 26-01-10 - FIM
}
if (drTranspor["IE"].ToString() != "")
{
objtransportadora.Ie = belUtil.TiraSimbolo(drTranspor["IE"].ToString(), "");
}
if (drTranspor["xEnder"].ToString() != "")
{
objtransportadora.Xender = belUtil.TiraSimbolo(drTranspor["xEnder"].ToString(), "");
}
if (drTranspor["xMun"].ToString() != "")
{
objtransportadora.Xmun = belUtil.TiraSimbolo(drTranspor["xMun"].ToString(), "");
}
if (drTranspor["UF"].ToString() != "")
{
objtransportadora.Uf = drTranspor["UF"].ToString();
}
objtransp.belTransportadora = objtransportadora;
//Claudinei - o.s. 23674 - 10/09/2009
if ((drTranspor["placa"].ToString() != "") && (drTranspor["placa"].ToString() != null))
{
belVeicTransp objveictransp = new belVeicTransp();
objveictransp.Placa = belUtil.TiraSimbolo(drTranspor["placa"].ToString().Replace(" ", "").Trim(), "");//Danner - o.s. sem - 30/03/2010
if (drTranspor["UF"].ToString() != "")
{
objveictransp.Uf = drTranspor["UF"].ToString().Trim();
}
if (drTranspor["RNTC"].ToString() != "")//Tag não Obrigatoria
{
objveictransp.Rntc = drTranspor["RNTC"].ToString().Trim();
}
else
{
objveictransp.Rntc = "00";
}
objtransp.belVeicTransp = objveictransp;
}
belVol objvol = new belVol();
if (drTranspor["qVol"].ToString() != "")
{
try
{
decimal dqVol = Convert.ToDecimal(drTranspor["qVol"].ToString());
//Claudinei - o.s. 24276 - 26/03/2010
if ((drTranspor["qVol"].ToString() == "") || (drTranspor["qVol"].ToString() == "0"))
{
if (snm_Cliente != "ZINCOBRIL")
{
dqVol = 1;
}
else
{
dqVol = 0;
}
}
//Fim - Claudinei - o.s. 24276 - 26/03/2010
objvol.Qvol = dqVol;
}
catch (Exception ex)
{
throw new Exception(string.Format("{0} - Campo de Quantidade de Volumes na tela de Montar NF",
ex.Message));
}
//Fim - Claudinei - o.s. 23776 - 22/10/2009
}
if (drTranspor["nVol"].ToString() != "")
{
objvol.Nvol = drTranspor["nVol"].ToString();//Danner - o.s. 24432 - 04/05/2010
}
if (drTranspor["esp"].ToString() != "")
{
objvol.Esp = belUtil.TiraSimbolo(drTranspor["esp"].ToString(), "");
}
if (drTranspor["marca"].ToString() != "")
{
objvol.Marca = drTranspor["marca"].ToString();
}
if (drTranspor["pesoL"].ToString() != "")
{
try
{
decimal dpesoL = Math.Round(Convert.ToDecimal(drTranspor["pesoL"].ToString()), 3); //Claudinei - o.s. 24248 - 26/03/2010
objvol.PesoL = dpesoL;
}
catch (Exception ex)
{
throw new Exception(string.Format("{0} - Campo Peso Liquido",
ex.Message));
}
}
if (drTranspor["pesoB"].ToString() != "")
{
try
{
decimal dpesoB = Math.Round(Convert.ToDecimal(drTranspor["pesoB"].ToString()), 3); //Claudinei - o.s. 24248 - 26/03/2010
objvol.PesoB = dpesoB;
}
catch (Exception ex)
{
throw new Exception(string.Format("{0} - Campo Peso Bruto",
ex.Message));
}
}
objtransp.belVol = objvol;
}
//Claudinei - o.s. 24210 - 03/03/2010
else
{
throw new Exception("Transportadora não selecionada na nota!");
}
}
catch (Exception Ex)
{
sExecao = " - Problemas no Frete da Nota";
throw new Exception(Ex.Message + sExecao);
}
finally
{
//Conn.Close();
}
return objtransp;
}
