public string BuscaObs(string seqNF) { try { StringBuilder sSql = new StringBuilder(); sSql.Append("Select "); sSql.Append("nf.ds_anota "); if (((Acesso.NM_EMPRESA == "MOGPLAST") || (Acesso.NM_EMPRESA == "TSA")) && (Acesso.CD_EMPRESA == "003")) { sSql.Append(", nf.cd_nfseq_fat_origem "); } if ((Acesso.NM_EMPRESA == "MACROTEX") || (Acesso.NM_EMPRESA == "PAVAX")) { sSql.Append(", coalesce(vendedor.nm_vend,'0')nm_vend , "); sSql.Append("coalesce(nf.DS_DOCORIG,'0')DS_DOCORIG "); } sSql.Append("From NF "); sSql.Append("left join vendedor on (vendedor.cd_vend = nf.cd_vend1) "); sSql.Append("Where "); sSql.Append("(NF.cd_empresa ='"); sSql.Append(Acesso.CD_EMPRESA); sSql.Append("')"); sSql.Append(" and "); sSql.Append("(nf.cd_nfseq = '"); sSql.Append(seqNF); sSql.Append("') "); string sObs = ""; sObs = daoUtil.RetornaBlob(sSql.ToString()); if (sObs.IndexOf("\\fs") != -1)// DIEGO - OS_24854 { sObs = sObs.Substring((sObs.IndexOf("\\fs") + 6), sObs.Length - (sObs.IndexOf("\\fs") + 6)); } if (Acesso.NM_EMPRESA == "MARPA") { sObs += MontaObsAgrup(seqNF); } if (Acesso.NM_RAMO != Acesso.BancoDados.CERAMICA) { 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, "); 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 "); 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 "); sSuframa.Append("From NF "); 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) "); 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) "); //Where sSuframa.Append("Where "); sSuframa.Append("(NF.cd_empresa ='"); sSuframa.Append(Acesso.NM_EMPRESA); sSuframa.Append("')"); sSuframa.Append(" and "); sSuframa.Append("(nf.cd_nfseq = '"); sSuframa.Append(seqNF); sSuframa.Append("') "); DataTable dt = HLP.GeraXml.dao.ADO.HlpDbFuncoes.qrySeekRet(sSuframa.ToString()); if (dt.Rows.Count > 0) { DataRow drSuframa = dt.Rows[0]; if (drSuframa["st_descsuframa"].ToString() == "S") { decimal dvlSuframa = Math.Round(Convert.ToDecimal(drSuframa["vl_suframa"].ToString()), 2); decimal dvlPerSuframa = Math.Round(Convert.ToDecimal(drSuframa["vl_persuframa"].ToString()), 2); 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()); } } 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(Acesso.CD_EMPRESA); sDevolucao.Append("')"); sDevolucao.Append(" and "); sDevolucao.Append("(Movitem.cd_nfseq = '"); sDevolucao.Append(seqNF); 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' "); sDevolucao.Append("Order by movitem.cd_doc"); DataTable dtDev = HlpDbFuncoes.qrySeekRet(sDevolucao.ToString()); List <strucDevolucoes> Devolucoes = new List <strucDevolucoes>(); decimal dvlTotBruto = 0; string scdDoc = string.Empty; foreach (DataRow drDevolucoes in dtDev.Rows) { if (scdDoc != drDevolucoes["cd_doc"].ToString()) { dvlTotBruto = 0; } dvlTotBruto += Math.Round(Convert.ToDecimal(drDevolucoes["vl_totbruto"].ToString()), 2); StringBuilder sStore = new StringBuilder(); sStore.Append("SELECT "); sStore.Append("QT_SALDOEN "); sStore.Append("FROM SP_SALDOTER('"); sStore.Append(Acesso.CD_EMPRESA); sStore.Append("', '"); sStore.Append(drDevolucoes["cd_clifor"].ToString()); sStore.Append("', '"); sStore.Append(" "); sStore.Append("', '"); sStore.Append("|||||||"); sStore.Append("', '"); sStore.Append("X"); sStore.Append("', '"); sStore.Append("N"); sStore.Append("', '"); sStore.Append(seqNF); 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 = HlpDbFuncoesGeral.conexao; cmd.Connection.Open(); cmd.CommandText = sStore.ToString(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); decimal dSaldoTer = Convert.ToDecimal(cmd.ExecuteScalar()); cmd.Connection.Close(); 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 = (dSaldoTer > 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 ((Acesso.NM_EMPRESA != "JAMAICA")) //25618 { if (Acesso.IMPRIMI_RETORNO) { 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 } } } } return(Util.TiraCaracterEstranho(sObs)); } catch (Exception Ex) { throw Ex; } }
public string BuscaObs(string seqNF) { try { StringBuilder sSql = new StringBuilder(); sSql.Append("Select "); sSql.Append("nf.ds_anota "); if (((Acesso.NM_EMPRESA == "MOGPLAST") || (Acesso.NM_EMPRESA == "TSA")) && (Acesso.CD_EMPRESA == "003")) { sSql.Append(", nf.cd_nfseq_fat_origem "); } if ((Acesso.NM_EMPRESA == "MACROTEX") || (Acesso.NM_EMPRESA == "PAVAX")) { sSql.Append(", coalesce(vendedor.nm_vend,'0')nm_vend , "); sSql.Append("coalesce(nf.DS_DOCORIG,'0')DS_DOCORIG "); } sSql.Append("From NF "); sSql.Append("left join vendedor on (vendedor.cd_vend = nf.cd_vend1) "); sSql.Append("Where "); sSql.Append("(NF.cd_empresa ='"); sSql.Append(Acesso.CD_EMPRESA); sSql.Append("')"); sSql.Append(" and "); sSql.Append("(nf.cd_nfseq = '"); sSql.Append(seqNF); sSql.Append("') "); string sObs = ""; sObs = daoUtil.RetornaBlob(sSql.ToString()); if (sObs.IndexOf("\\fs") != -1)// DIEGO - OS_24854 { sObs = sObs.Substring((sObs.IndexOf("\\fs") + 6), sObs.Length - (sObs.IndexOf("\\fs") + 6)); } if (Acesso.NM_EMPRESA == "MARPA") { sObs += MontaObsAgrup(seqNF); } if (Acesso.NM_RAMO != Acesso.BancoDados.CERAMICA) { 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, "); 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 "); 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 "); sSuframa.Append("From NF "); 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) "); 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) "); //Where sSuframa.Append("Where "); sSuframa.Append("(NF.cd_empresa ='"); sSuframa.Append(Acesso.NM_EMPRESA); sSuframa.Append("')"); sSuframa.Append(" and "); sSuframa.Append("(nf.cd_nfseq = '"); sSuframa.Append(seqNF); sSuframa.Append("') "); DataTable dt = HLP.GeraXml.dao.ADO.HlpDbFuncoes.qrySeekRet(sSuframa.ToString()); if (dt.Rows.Count > 0) { DataRow drSuframa = dt.Rows[0]; if (drSuframa["st_descsuframa"].ToString() == "S") { decimal dvlSuframa = Math.Round(Convert.ToDecimal(drSuframa["vl_suframa"].ToString()), 2); decimal dvlPerSuframa = Math.Round(Convert.ToDecimal(drSuframa["vl_persuframa"].ToString()), 2); 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()); } } 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(Acesso.CD_EMPRESA); sDevolucao.Append("')"); sDevolucao.Append(" and "); sDevolucao.Append("(Movitem.cd_nfseq = '"); sDevolucao.Append(seqNF); 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' "); sDevolucao.Append("Order by movitem.cd_doc"); DataTable dtDev = HlpDbFuncoes.qrySeekRet(sDevolucao.ToString()); List<strucDevolucoes> Devolucoes = new List<strucDevolucoes>(); decimal dvlTotBruto = 0; string scdDoc = string.Empty; foreach (DataRow drDevolucoes in dtDev.Rows) { if (scdDoc != drDevolucoes["cd_doc"].ToString()) { dvlTotBruto = 0; } dvlTotBruto += Math.Round(Convert.ToDecimal(drDevolucoes["vl_totbruto"].ToString()), 2); StringBuilder sStore = new StringBuilder(); sStore.Append("SELECT "); sStore.Append("QT_SALDOEN "); sStore.Append("FROM SP_SALDOTER('"); sStore.Append(Acesso.CD_EMPRESA); sStore.Append("', '"); sStore.Append(drDevolucoes["cd_clifor"].ToString()); sStore.Append("', '"); sStore.Append(" "); sStore.Append("', '"); sStore.Append("|||||||"); sStore.Append("', '"); sStore.Append("X"); sStore.Append("', '"); sStore.Append("N"); sStore.Append("', '"); sStore.Append(seqNF); 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 = HlpDbFuncoesGeral.conexao; cmd.Connection.Open(); cmd.CommandText = sStore.ToString(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); decimal dSaldoTer = Convert.ToDecimal(cmd.ExecuteScalar()); cmd.Connection.Close(); 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 = (dSaldoTer > 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 ((Acesso.NM_EMPRESA != "JAMAICA")) //25618 { if (Acesso.IMPRIMI_RETORNO) { 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 } } } } return Util.TiraCaracterEstranho(sObs); } catch (Exception Ex) { throw Ex; } }
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 }