public IDbCommand GetDbCommand()
{
IDbCommand comando = (IDbCommand)HlpFuncoes.getObjeto(sDriverBaseDados,
sClasseDbCommandBaseDados);
return(comando);
}
public IDbDataAdapter GetDataAdapter()
{
IDbDataAdapter da = (IDbDataAdapter)HlpFuncoes.getObjeto(sDriverBaseDados,
sClasseDbDataAdapterBaseDados);
return(da);
}
private static IDbCommand GetObjetoDbCommand(IDbConnection conexao,
string sBibliotecaDll, string sClasseDbCommand, string sExpressaoSql)
{
IDbCommand comando = (IDbCommand)HlpFuncoes.getObjeto(sBibliotecaDll,
sClasseDbCommand);
if (conexao.State != ConnectionState.Open)
{
conexao.Open();
}
comando.Connection = conexao;
comando.CommandText = sExpressaoSql;
return(comando);
}
private IDataReader GetDataReaderExistenciaObjeto(StringBuilder strExpressao,
IDbConnection conexao)
{
IDbCommand comando = (IDbCommand)HlpFuncoes.getObjeto(
GetDriverBaseDados(),
GetClasseDbCommandBaseDados());
comando.Connection = conexao;
comando.CommandText = strExpressao.ToString();
IDataReader dr = comando.ExecuteReader();
return(dr);
}
public void ConfigurarConectarBase(string sservidor, string sbanco,
bool MantemConexaoAberta)
{
//Rotina implementada por Renato - 22/03/2006 - OS 14461
EfetuarFechamentoConexao(objConexao);
if (objConexao == null)
{
objConexao = (IDbConnection)HlpFuncoes.getObjeto(
objFuncoesBanco.GetDriverBaseDados(),
objFuncoesBanco.GetClasseConexaoBaseDados());
}
string sPorta = WebConfigurationManager.AppSettings["PORT"];
objConexao.ConnectionString = objFuncoesBanco.GetStringConexao(sservidor,
sbanco, MS_USERNAME, MS_SENHA, sPorta);
objConexao.Open();
if (!MantemConexaoAberta)
{
objConexao.Close();
}
}
public static DataTable QrySeekRet(IDbConnection conexao,
string sBibliotecaDll, string sClasseDataAdapter, string sClasseDbCommand,
string sExpressaoSql, bool bFechaConexao)
{
DbDataAdapter da = (DbDataAdapter)HlpFuncoes.getObjeto(sBibliotecaDll,
sClasseDataAdapter);
da.SelectCommand = (DbCommand)GetObjetoDbCommand(conexao, sBibliotecaDll,
sClasseDbCommand, sExpressaoSql);
if (conexao.State != ConnectionState.Open)
{
conexao.Open();
}
DataSet ds = new DataSet("dadoshlp");
da.Fill(ds, "registro");
DataTable dt = ds.Tables[0];
if (bFechaConexao)
{
conexao.Close();
}
return(dt);
}
public bool ValorValido()
{
return(HlpFuncoes.DataValida(this.Text.Trim()));
}
private bool ValoresValidosCliente()
{
Dictionary <HlpWebTextBox, HlpWebLabel> CamposObrigatorios =
new Dictionary <HlpWebTextBox, HlpWebLabel>();
CamposObrigatorios.Add(txtNmGuerra, lblNmGuerra);
CamposObrigatorios.Add(txtNmClifor, lblNmClifor);
bool bPessoaJuridica = cbxStPessoaJ.SelectedValue.Equals("S");
if (bPessoaJuridica)
{
CamposObrigatorios.Add(txtCNPJ, lblCdCNPJ);
}
else
{
CamposObrigatorios.Add(txtCdCpf, lblCdCpf);
CamposObrigatorios.Add(txtCdRg, lblCdRg);
}
CamposObrigatorios.Add(txtDsEndnor, lblDsEndnor);
CamposObrigatorios.Add(txtNumero, lblNumero);
CamposObrigatorios.Add(txtNM_BAIRRONOR, lblNM_BAIRRONOR);
CamposObrigatorios.Add(txtCdCepnor, lblCdCepnor);
CamposObrigatorios.Add(txtCdFonenor, lblCdFonenor);
bool bCamposNaoPreenchidos = false;
foreach (HlpWebTextBox campo in CamposObrigatorios.Keys)
{
HlpWebLabel label = CamposObrigatorios[campo];
if (campo.EmBranco())
{
label.Visible = true;
bCamposNaoPreenchidos = true;
}
else
{
label.Visible = false;
}
}
if (bCamposNaoPreenchidos)
{
MessageHLP.ShowPopUpMsg("Os campos com '*' devem ser obrigatoriamente preenchidos", this.Page);
return(false);
}
if (bPessoaJuridica)
{
if (!FormatoValidoCampo("CNPJ", "XX.XXX.XXX/XXXX-XX", txtCNPJ))
{
return(false);
}
if (!HlpFuncoes.CNPJValido(txtCNPJ.GetValor().ToString()))
{
MessageHLP.ShowPopUpMsg("O número de CNPJ informado é inválido!", this.Page);
return(false);
}
//Inicio OS - 21175 Colocar verificação na IE.
if (!txtCdInsest.Text.ToUpper().Equals("ISENTO"))
{
if (!ValidarInscEstadual(txtCdInsest.Text.Replace(".", "")))
{
MessageHLP.ShowPopUpMsg("Inscrição Estadual só pode conter números." + Environment.NewLine +
"Caso o cliente seja ISENTO preencha o campo com o valor ISENTO.", this.Page);
return(false);
}
}
//FIM OS - 21175 Colocar verificação na IE.
}
else
{
if (!FormatoValidoCampo("CPF", "XXX.XXX.XXX-XX", txtCdCpf))
{
return(false);
}
if (!HlpFuncoes.CPFValido(txtCdCpf.GetValor().ToString()))
{
MessageHLP.ShowPopUpMsg("O número de CPF informado é inválido!", this.Page);
return(false);
}
}
if (!FormatoValidoCampo("CEP", "XXXXX-XXX", txtCdCepnor))
{
return(false);
}
if ((!txtCdEmail.EmBranco()) && (!txtCdEmail.ValorValido()))
{
MessageHLP.ShowPopUpMsg("O endereço de e-mail informato é inválido!", this.Page);
return(false);
}
return(true);
}
public bool ValorValido()
{
return(HlpFuncoes.InteiroValido(this.Text.Trim()));
}
private void IncluiItens()
{
UsuarioWeb objUsuario = (UsuarioWeb)Session["ObjetoUsuario"];
DataTable dtVendas = (DataTable)Session["Vendas"];
// Criando a tabela de itens
DataTable dtItens = new DataTable("ITENS");
DataColumn dcColunasItem;
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "NR_LANC";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "CD_EMPRESA";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "CD_VEND";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "CD_NOTAFIS";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.Decimal");
dcColunasItem.ColumnName = "CD_PEDIDO";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.Decimal");
dcColunasItem.ColumnName = "CD_SEQPED";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "CD_RELACAO";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "CD_PROD";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "CD_ALTER";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "DS_PROD";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "CD_PEDCLI";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.String");
dcColunasItem.ColumnName = "CD_TPUNID";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.Decimal");
dcColunasItem.ColumnName = "VL_PERCOMI1";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.Double");
dcColunasItem.ColumnName = "QT_PROD";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.Decimal");
dcColunasItem.ColumnName = "VL_COMISSAO";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dcColunasItem = new DataColumn();
dcColunasItem.DataType = System.Type.GetType("System.Decimal");
dcColunasItem.ColumnName = "VL_TOTLIQ";
dcColunasItem.ReadOnly = true;
dtItens.Columns.Add(dcColunasItem);
dtItens.Columns.IndexOf("CD_RELACAO" + "DS_PROD");
//Fim tabela itens
DataTable dtMovitem = new DataTable("MOVITEM");
//Criando Tabela NF
DataTable dtNF = new DataTable("NF");
DataColumn dcColNF;
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "NR_LANC";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "CD_EMPRESA";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "CD_NFSEQ";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "CD_CLIFOR";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "CD_VEND";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "CD_NOTAFIS";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "CD_PEDIDO";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "CD_SEQPED";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "CD_RELACAO";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "NM_GUERRA";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "NM_VEND";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.String");
dcColNF.ColumnName = "NM_CLIFOR";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.DateTime");
dcColNF.ColumnName = "DT_EMI";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.Decimal");
dcColNF.ColumnName = "VL_TOTPROD";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.Decimal");
dcColNF.ColumnName = "VL_TOTCOMI";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dcColNF = new DataColumn();
dcColNF.DataType = System.Type.GetType("System.DateTime");
dcColNF.ColumnName = "DT_REFCOMI";
dcColNF.ReadOnly = true;
dtNF.Columns.Add(dcColNF);
dtNF.Columns.IndexOf("CD_EMPRESA" + "CD_VEND" + "NM_GUERRA" + "DT_EMI" +
"CD_REFCOMI" + "CD_NOTAFIS" + "CD_PEDIDO" + "CD_SEQPED");
//Fim tabela NF
DataTable dtConsulta = new DataTable("Consulta");
//Criando tabela Duplicata
DataTable dtDuplicata = new DataTable("DUPLICATA");
DataColumn dcColDupli;
dcColDupli = new DataColumn();
dcColDupli.DataType = System.Type.GetType("System.String");
dcColDupli.ColumnName = "CD_DUPLI";
dcColDupli.ReadOnly = true;
dtDuplicata.Columns.Add(dcColDupli);
dcColDupli = new DataColumn();
dcColDupli.DataType = System.Type.GetType("System.String");
dcColDupli.ColumnName = "CD_RELACAO";
dcColDupli.ReadOnly = true;
dtDuplicata.Columns.Add(dcColDupli);
dcColDupli = new DataColumn();
dcColDupli.DataType = System.Type.GetType("System.Decimal");
dcColDupli.ColumnName = "VL_DOC";
dcColDupli.ReadOnly = true;
dtDuplicata.Columns.Add(dcColDupli);
dcColDupli = new DataColumn();
dcColDupli.DataType = System.Type.GetType("System.DateTime");
dcColDupli.ColumnName = "DT_VENC";
dcColDupli.ReadOnly = true;
dtDuplicata.Columns.Add(dcColDupli);
dtDuplicata.Columns.IndexOf("CD_RELACAO" + "CD_DUPLI");
//Fim tabela Duplicata
decimal TotComi = 0;
DataRow drDupli;
DataRow drItenNF;
DataRow drNF;
ListBox lbCodigo = new ListBox();
foreach (DataRow dr in dtVendas.Rows)
{
if (lbCodigo.Items.IndexOf(lbCodigo.Items.FindByText(dr["CD_RELACAO"].ToString())) >= 0)
{
continue;
}
else
{
lbCodigo.Items.Add(dr["CD_RELACAO"].ToString());
}
StringBuilder strSelect = new StringBuilder();
// strSelect.Append("SELECT CD_EMPRESA, CD_EMPRESA, ((CASE WHEN VL_COEF < 1 THEN VL_COEF ELSE 1 END * VL_TOTLIQ)-coalesce(VL_DESCSUFRAMA,0)) AS VL_TOTLIQ, VL_DESCPISSUFRAMA, VL_DESCCOFINSSUFRAMA, ");
strSelect.Append("SELECT CD_EMPRESA, VL_TOTLIQ, VL_DESCPISSUFRAMA, VL_DESCCOFINSSUFRAMA, ");
strSelect.Append("CD_PROD, DS_PROD,QT_PROD,CD_TPUNID,CD_ALTER, ");
strSelect.Append("CD_PEDCLI, CD_VEND1, VL_PERCOMI1 ");
strSelect.Append("FROM MOVITEM INNER JOIN OPEREVE ON (MOVITEM.CD_OPER = OPEREVE.CD_OPER) ");
strSelect.Append("WHERE (CD_EMPRESA = '001') AND (CD_NFSEQ = '" + dr["CD_NFSEQ"] + "') AND (OPEREVE.ST_TPOPER = '0')");
dtMovitem = objUsuario.oTabelas.hlpDbFuncoes.qrySeekRet(strSelect.ToString());
// Preencher Duplicatas.
//PreencheDuplicata(sWhere.Substring(0, sWhere.Length - 4), dtVendas);
StringBuilder strSelectDuplic = new StringBuilder();
strSelectDuplic.Append("SELECT CD_DUPLI, DT_VENCI, VL_DOC ");
strSelectDuplic.Append("FROM DOC_CTR ");
strSelectDuplic.Append("WHERE ");
if (dr["CD_NFSEQ"].ToString() != "")
{
strSelectDuplic.Append("(CD_NFSEQ = '" + dr["CD_NFSEQ"] + "') AND ");
}
else
{
strSelectDuplic.Append("(CD_PEDIDO = '" + dr["CD_PEDIDO"] + "') AND (CD_SEQPED = '" + dr["CD_SEQPED"] + "') AND ");
}
strSelectDuplic.Append("(CD_EMPRESA = '001') ");
strSelectDuplic.Append("ORDER BY CD_EMPRESA, CD_DUPLI");
dtConsulta = objUsuario.oTabelas.hlpDbFuncoes.qrySeekRet(strSelectDuplic.ToString());
foreach (DataRow drItensDupli in dtConsulta.Rows)
{
drDupli = dtDuplicata.NewRow();
//if (strComparacao != drItensDupli["CD_DUPLI"].ToString())
//{
if (dr["CD_NOTAFIS"].ToString() == "")
{
drDupli["CD_RELACAO"] = dr["CD_EMPRESA"] + "-" + dr["CD_VEND1"] + "-" +
dr["CD_PEDIDO"] + "-" + dr["CD_SEQPED"];
}
else
{
drDupli["CD_RELACAO"] = dr["CD_EMPRESA"] + "-" + dr["CD_VEND"] + "-" +
dr["CD_NOTAFIS"];
}
drDupli["CD_DUPLI"] = drItensDupli["CD_DUPLI"];
drDupli["DT_VENC"] = drItensDupli["DT_VENCI"];
drDupli["VL_DOC"] = drItensDupli["VL_DOC"];
dtDuplicata.Rows.Add(drDupli);
//}
}
// Fim Preencher Duplicatas.
foreach (DataRow drItem in dtMovitem.Rows)
{
drItenNF = dtItens.NewRow();
drItenNF["NR_LANC"] = HlpFuncoes.AlinharZerosEsquerda(Convert.ToString(dtItens.Rows.Count + 1), 7);
drItenNF["CD_EMPRESA"] = "001";
drItenNF["CD_NOTAFIS"] = dr["CD_NOTAFIS"];
drItenNF["CD_VEND"] = objUsuario.CodigoVendedor.ToString();
if (descontopiscofinssuframa(dr["CD_NFSEQ"].ToString(), "001"))
{
drItenNF["VL_TOTLIQ"] = Convert.ToDecimal((drItem["VL_TOTLIQ"].ToString() == "" ? "0" : drItem["VL_TOTLIQ"].ToString()))
- Convert.ToDecimal((drItem["VL_DESCCOFINSSUFRAMA"].ToString() == "" ? "0" : drItem["VL_DESCCOFINSSUFRAMA"].ToString()))
- Convert.ToDecimal((drItem["VL_DESCPISSUFRAMA"].ToString() == "" ? "0" : drItem["VL_DESCPISSUFRAMA"].ToString()));
}
else
{
drItenNF["VL_TOTLIQ"] = drItem["VL_TOTLIQ"];
}
if (drItem["CD_PEDCLI"] != null)
{
drItenNF["CD_PEDCLI"] = drItem["CD_PEDCLI"];
}
if (dr["CD_NOTAFIS"].ToString().Trim() == "")
{
drItenNF["CD_RELACAO"] = drItem["CD_EMPRESA"] + "-" +
drItem["CD_VEND"] + "-" +
drItem["CD_PEDIDO"] + "-" +
drItem["CD_SEQPED"];
}
else
{
drItenNF["CD_RELACAO"] = drItem["CD_EMPRESA"] + "-" +
drItem["CD_VEND1"] + "-" +
dr["CD_NOTAFIS"];
}
drItenNF["QT_PROD"] = drItem["QT_PROD"];
drItenNF["CD_TPUNID"] = drItem["CD_TPUNID"];
drItenNF["VL_PERCOMI1"] = drItem["VL_PERCOMI1"];
drItenNF["VL_COMISSAO"] = (Convert.ToDecimal(drItem["VL_PERCOMI1"].ToString()) * Convert.ToDecimal(drItem["VL_TOTLIQ"].ToString())) / 100;
drItenNF["CD_PROD"] = drItem["CD_PROD"];
drItenNF["DS_PROD"] = drItem["DS_PROD"];
drItenNF["CD_ALTER"] = drItem["CD_ALTER"];
// drItenNF["CD_INDEX"] = drItem["CD_ALTER"];
//dtNF.Columns.IndexOf("CD_EMPRESA" + "NM_CLIFOR" + "DT_REFCOMI" + "CD_NOTAFIS" + "CD_PEDIDO" + "CD_SEQPES");
TotComi += Convert.ToDecimal(drItenNF["VL_COMISSAO"].ToString());
dtItens.Rows.Add(drItenNF);
}
drNF = dtNF.NewRow();
drNF["NR_LANC"] = HlpFuncoes.AlinharZerosEsquerda(Convert.ToString(dtNF.Rows.Count + 1), 7);
drNF["CD_EMPRESA"] = dr["CD_EMPRESA"];
drNF["CD_NFSEQ"] = dr["CD_NFSEQ"];
drNF["CD_VEND"] = dr["CD_VEND"];
drNF["NM_GUERRA"] = dr["NM_GUERRA"];
drNF["NM_VEND"] = dr["NM_VEND"];
drNF["CD_CLIFOR"] = dr["CD_CLIFOR"];
drNF["CD_NOTAFIS"] = dr["CD_NOTAFIS"];
drNF["NM_CLIFOR"] = dr["NM_CLIFOR"];
drNF["DT_EMI"] = dr["DT_EMI"];
drNF["VL_TOTPROD"] = dr["VL_TOTPROD"];
drNF["VL_TOTCOMI"] = TotComi;
drNF["CD_RELACAO"] = dr["CD_RELACAO"];
drNF["DT_REFCOMI"] = dr["DT_REFCOMI"];
dtNF.Rows.Add(drNF);
TotComi = 0;
}
Session["NF"] = dtNF;
Session["ITENS"] = dtItens;
Session["DUPLICATAS"] = dtDuplicata;
}