private BigDecimal buscaSaldoAnt() {
BigDecimal bigRetorno = new BigDecimal("0.00");
StringBuilder sSQL = new StringBuilder();
sSQL.append("SELECT S.SALDOSL FROM FNSALDOLANCA S, FNCONTA C ");
sSQL.append("WHERE C.NUMCONTA=? AND C.CODEMP=? AND C.CODFILIAL=? ");
sSQL.append("AND S.CODEMP=C.CODEMPPN AND S.CODFILIAL=C.CODFILIALPN ");
sSQL.append("AND S.CODPLAN=C.CODPLAN AND S.DATASL=");
sSQL.append("(SELECT MAX(S1.DATASL) FROM FNSALDOLANCA S1 ");
sSQL.append("WHERE S1.DATASL < ? AND S1.CODPLAN=S.CODPLAN ");
sSQL.append("AND S1.CODEMP=S.CODEMP AND S1.CODFILIAL=S.CODFILIAL)");
try {
PreparedStatement ps = con.prepareStatement(sSQL.toString());
ps.setString(1, txtCodConta.getVlrString());
ps.setInt(2, Aplicativo.iCodEmp);
ps.setInt(3, ListaCampos.getMasterFilial("FNCONTA"));
ps.setDate(4, Funcoes.dateToSQLDate(txtDataini.getVlrDate()));
ResultSet rs = ps.executeQuery();
if (rs.next()) {
bigRetorno = new BigDecimal(rs.getString("SaldoSL"));
}
rs.close();
ps.close();
} catch (Exception e) {
e.printStackTrace();
Funcoes.mensagemErro(this, "Erro ao buscar saldo anterior!\n" + e.getMessage(), true, con, e);
}
return bigRetorno;
}
public String format(
Object obj, ETipo tipo, int tam, int dec, boolean maiusculo, boolean tiraacento) {
String retorno = null;
String str = null;
if (obj == null) {
str = "";
} else {
str = obj.toString();
if (maiusculo) {
str = str.toUpperCase();
}
if (tiraacento) {
str = tiratodoacento(str);
}
}
if (tipo == ETipo.$9) {
if (dec > 0) {
retorno = Funcoes.transValor(str, tam, dec, true);
} else {
retorno = StringFunctions.strZero(str, tam);
}
} else {
retorno = Funcoes.adicionaEspacos(str, tam);
}
return retorno;
}
public ResultSet getResultSet() {
String NomeCli = "";
NomeCli = "'" + txtCliente.getVlrString() + "%'";
String sSQL =
" select vdcliente.Nomecli as nome, fnbanco.Nomebanco as banco, "
+ " sgcheque.NCHEQUE, sgcheque.predata, sgcheque.valor, "
+ " sgcheque.codbanco, sgcheque.codcli, sgscheque.NSAIDA ,sgscheque.NCHEQUE, sgscheque.TSAIDA, "
+ " sgscheque.DESCRICAO, sgscheque.DTINS as Dates, sgrcheque.NSAIDA, sgrcheque.NCHEQUE, sgrcheque.DTINS as Dater, "
+ " sgrcheque.codigor from vdcliente, fnbanco, sgcheque, sgscheque, sgrcheque where "
+ " vdcliente.CODCLI=sgcheque.CODCLI and "
+ " fnbanco.codbanco=sgcheque.codbanco and UPPER(vdcliente.Nomecli) LIKE UPPER( "
+ NomeCli
+ " ) "
+ " and sgcheque.NCHEQUE=sgscheque.NCHEQUE and sgcheque.CODBANCO=sgscheque.CODBANCO and sgscheque.TSAIDA=1 "
+ " and sgrcheque.NCHEQUE=sgscheque.NCHEQUE and sgrcheque.CODBANCO=sgscheque.CODBANCO and sgrcheque.NSAIDA=sgscheque.NSAIDA and sgscheque.dtins BETWEEN ? AND ? ORDER BY sgscheque.DTINS";
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = con.prepareStatement(sSQL);
ps.setDate(1, Funcoes.dateToSQLDate(txtDataini.getVlrDate()));
ps.setDate(2, Funcoes.dateToSQLDate(txtDatafim.getVlrDate()));
rs = ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public Object[] loadReceber(
Integer codemprc,
Integer codfilialrc,
Integer codempcl,
Integer codfilialcl,
Integer codcli,
Integer codemppd,
Integer codfilialpd,
Integer codprod,
Date dtini,
Date dtfim)
throws Exception {
Object[] result = new Object[RESULT_RECEBER.values().length];
try {
StringBuilder sql = new StringBuilder();
sql.append("select sum(ir.vlrapagitrec) total_aberto ");
sql.append(
", sum(case when ir.dtvencitrec<cast('now' as date) then ir.vlrapagitrec else 0 end) total_atraso ");
sql.append(", avg(case when ir.dtvencitrec<coalesce(ir.dtliqitrec,cast('now' as date)) ");
sql.append(
"then coalesce(ir.dtliqitrec,cast('now' as date)) - ir.dtvencitrec else 0 end) atraso_medio ");
// sql.append( ", min(datarec), max(datarec) " );
sql.append("from fnreceber rc, fnitreceber ir ");
sql.append(
"where rc.codemp=ir.codemp and rc.codfilial=ir.codfilial and rc.codrec=ir.codrec and ");
sql.append(
"ir.codemp=? and ir.codfilial=? and rc.codempcl=? and rc.codfilialcl=? and codcli=? ");
sql.append("and ir.dtvencitrec between ? and ? ");
PreparedStatement ps = getConn().prepareStatement(sql.toString());
int param = 1;
ps.setInt(param++, codemprc);
ps.setInt(param++, codfilialrc);
ps.setInt(param++, codempcl);
ps.setInt(param++, codfilialcl);
ps.setInt(param++, codcli);
ps.setDate(param++, Funcoes.dateToSQLDate(dtini));
ps.setDate(param++, Funcoes.dateToSQLDate(dtfim));
ResultSet rs = ps.executeQuery();
if (rs.next()) {
// result[RESULT_RECEBER.TOTAL_VENDAS.ordinal()] = rs.getBigDecimal(
// RESULT_RECEBER.TOTAL_VENDAS.name() );
result[RESULT_RECEBER.TOTAL_ABERTO.ordinal()] =
rs.getBigDecimal(RESULT_RECEBER.TOTAL_ABERTO.name());
result[RESULT_RECEBER.TOTAL_ATRASO.ordinal()] =
rs.getBigDecimal(RESULT_RECEBER.TOTAL_ATRASO.name());
result[RESULT_RECEBER.ATRASO_MEDIO.ordinal()] =
rs.getBigDecimal(RESULT_RECEBER.ATRASO_MEDIO.name());
}
rs.close();
ps.close();
getConn().commit();
} catch (SQLException e) {
getConn().rollback();
throw new Exception(e.getMessage());
}
return result;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(getTipoNota());
sb.append(EbsContabil.format(getNumeroNota(), 6));
sb.append(EbsContabil.format(getModelo(), 2));
sb.append(EbsContabil.format(getSerie(), 3));
sb.append(EbsContabil.format(getCfop(), 4));
if (getCpfCnpj() == null || getCpfCnpj().trim().length() == 0) {
sb.append(EbsContabil.format("", 18));
} else if (getCpfCnpj().length() == 14) {
sb.append(EbsContabil.format(Funcoes.setMascara(getCpfCnpj(), "##.###.###/####-##"), 18));
} else if (getCpfCnpj().length() == 11) {
sb.append(EbsContabil.format(Funcoes.setMascara(getCpfCnpj(), "###.###.###-##"), 18));
}
sb.append(EbsContabil.format(getCodigo(), 10));
sb.append(EbsContabil.format(getValor(), 12, 2));
sb.append(EbsContabil.format(getQuantidade(), 13, 3));
sb.append(EbsContabil.format(getDesconto(), 12, 2));
sb.append(EbsContabil.format(getBaseICMS(), 12, 2));
sb.append(EbsContabil.format(getAliquotaICMS(), 5, 2));
sb.append(EbsContabil.format(getValorIPI(), 12, 2));
sb.append(EbsContabil.format(getBaseICMSSubTributaria(), 12, 2));
sb.append(EbsContabil.format(getAliquotaIPI(), 5, 2));
sb.append(EbsContabil.format(getPercRedBaseIcms(), 5, 2));
sb.append(EbsContabil.format(getSitTributaria(), 3));
sb.append(EbsContabil.format(getIdentificacao(), 15));
sb.append(EbsContabil.format(getSitTributariaIPI(), 3));
sb.append(EbsContabil.format(getBaseIPI(), 12, 2));
sb.append(EbsContabil.format(getSitTributariaPIS(), 3));
sb.append(EbsContabil.format(getBasePIS(), 12, 2));
sb.append(EbsContabil.format(getAliquotaPISPercento(), 5, 2));
sb.append(EbsContabil.format(getQtdeBaseCalculoPIS(), 12, 2));
sb.append(EbsContabil.format(getAliquotaPISValor(), 12, 2));
sb.append(EbsContabil.format(getValorPIS(), 12, 2));
sb.append(EbsContabil.format(getSitTributariaCofins(), 3));
sb.append(EbsContabil.format(getBaseCofins(), 12, 2));
sb.append(EbsContabil.format(getAliquotaCofinsPercento(), 5, 2));
sb.append(EbsContabil.format(getQtdeBaseCalcCofins(), 12, 2));
sb.append(EbsContabil.format(getAliquotaCofinsValor(), 12, 2));
sb.append(EbsContabil.format(getValorCofins(), 12, 2));
sb.append(EbsContabil.format(getValorICMSSubTributaria(), 12, 2));
sb.append(EbsContabil.format(getAliquotaICMSSubTributaria(), 5, 2));
sb.append(EbsContabil.format("", 195));
sb.append(EbsContabil.format("", 5));
sb.append(EbsContabil.format(getSequencial(), 6));
return sb.toString();
}
private void geraOPS() {
StringBuffer sql = new StringBuffer();
Vector<Integer> ops = new Vector<Integer>();
BigDecimal qtdsugerida = null;
DLLoading loading = new DLLoading();
try {
for (int i = 0; i < tabDet.getNumLinhas(); i++) {
loading.start();
qtdsugerida = (BigDecimal) (tabDet.getValor(i, DETALHAMENTO.QTDAPROD.ordinal()));
// Caso o item do grid esteja selecionado...
if ((Boolean) (tabDet.getValor(i, DETALHAMENTO.MARCACAO.ordinal()))
&& qtdsugerida.floatValue() > 0) {
try {
PPGeraOP geraop = new PPGeraOP();
geraop.setCodempop(Aplicativo.iCodEmp);
geraop.setCodfilialop(Aplicativo.iCodFilial);
geraop.setCodemppd((Integer) tabDet.getValor(i, DETALHAMENTO.CODEMPPD.ordinal()));
geraop.setCodfilialpd((Integer) tabDet.getValor(i, DETALHAMENTO.CODFILIALPD.ordinal()));
geraop.setCodprod((Integer) tabDet.getValor(i, DETALHAMENTO.CODPROD.ordinal()));
geraop.setQtdSugProdOp(
(BigDecimal) tabDet.getValor(i, DETALHAMENTO.QTDAPROD.ordinal()));
geraop.setDtFabOp(
Funcoes.strDateToDate(
(String) tabDet.getValor(i, DETALHAMENTO.DTFABROP.ordinal())));
geraop.setSeqest((Integer) tabDet.getValor(i, DETALHAMENTO.SEQEST.ordinal()));
ResultSet rs = daopush.geraOP(geraop);
if (rs.next()) {
ops.addElement(rs.getInt(1));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
carregaItens();
} catch (Exception e) {
e.printStackTrace();
} finally {
loading.stop();
Funcoes.mensagemInforma(
this, "As seguintes ordens de produção foram geradas:\n" + ops.toString());
}
}
private boolean comRef() {
boolean bRetorno = false;
String sSQL = "SELECT USAREFPROD FROM SGPREFERE1 WHERE CODEMP=? AND CODFILIAL=?";
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = Aplicativo.getInstance().getConexao().prepareStatement(sSQL);
ps.setInt(1, Aplicativo.iCodEmp);
ps.setInt(2, ListaCampos.getMasterFilial("SGPREFERE1"));
rs = ps.executeQuery();
if (rs.next()) if (rs.getString("UsaRefProd").trim().equals("S")) bRetorno = true;
} catch (SQLException err) {
Funcoes.mensagemErro(
this, "Erro ao carregar a tabela PREFERE1!\n" + err.getMessage(), true, con, err);
} finally {
sSQL = null;
ps = null;
rs = null;
}
return bRetorno;
}
private void imprimiGrafico(final ResultSet rs, final TYPE_PRINT bVisualizar, final String sCab) {
FPrinterJob dlGr = null;
HashMap<String, Object> hParam = new HashMap<String, Object>();
BigDecimal bAnt = buscaSaldoAnt();
hParam.put("CODEMP", Aplicativo.iCodEmp);
hParam.put("CODFILIAL", ListaCampos.getMasterFilial("FNCONTA"));
hParam.put("RAZAOEMP", Aplicativo.empresa.toString());
hParam.put("FILTROS", sCab);
hParam.put("SALDO_ANT", bAnt);
dlGr =
new FPrinterJob("relatorios/Extrato.jasper", "Extrato de contas", sCab, rs, hParam, this);
if (bVisualizar == TYPE_PRINT.VIEW) {
dlGr.preview();
} else {
try {
dlGr.print(true);
} catch (Exception err) {
Funcoes.mensagemErro(
this, "Erro na impressão de Extratos de contas!" + err.getMessage(), true, con, err);
}
}
}
private void insertFaltaJustificada(String horaini, String horafin) {
txtCodAtend.setVlrInteger(daoatend.getAtendente(txtMatempr.getVlrInteger()));
try {
if (daoatend.getPrefs()[PREFS.CODMODELFJ.ordinal()] != null) {
daoatend.insertFaltaJustificada(
Aplicativo.iCodEmp,
ListaCampos.getMasterFilial("ATATENDIMENTO"),
txtDtFalta.getVlrDate(),
txtDtFalta.getVlrDate(),
horaini,
horafin,
Aplicativo.iCodEmp,
ListaCampos.getMasterFilial("ATATENDENTE"),
txtCodAtend.getVlrInteger(),
Aplicativo.iCodEmp,
ListaCampos.getMasterFilial("SGUSUARIO"),
Aplicativo.getUsuario().getIdusu());
}
} catch (Exception e) {
Funcoes.mensagemErro(
this, "Erro inserindo lançamento automatizado de intervalo !\n" + e.getMessage());
e.printStackTrace();
}
}
private void imprimiGrafico(final TYPE_PRINT bVisualizar, final ResultSet rs, final String sCab) {
FPrinterJob dlGr = null;
HashMap<String, Object> hParam = new HashMap<String, Object>();
hParam.put("CODEMP", Aplicativo.iCodEmp);
hParam.put("CODFILIAL", ListaCampos.getMasterFilial("VDCLIENTE"));
hParam.put("RAZAOEMP", Aplicativo.empresa.toString());
hParam.put("FILTROS", sCab);
dlGr =
new FPrinterJob(
"layout/rel/REL_CLI_SEM_VD_01.jasper",
"Relatório de clientes sem movimento",
sCab,
rs,
hParam,
this);
if (bVisualizar == TYPE_PRINT.VIEW) {
dlGr.preview();
} else {
try {
dlGr.print(true);
} catch (Exception err) {
Funcoes.mensagemErro(
this, "Erro na impressão do relatório!" + err.getMessage(), true, con, err);
}
}
}
private void exportar() {
if (invalidos == null) {
return;
}
try {
String localFile = null;
FileDialog fileDialog = new FileDialog(Aplicativo.telaPrincipal, "Selecionar diretorio.");
fileDialog.setFile("*.txt");
fileDialog.setVisible(true);
if (fileDialog.getDirectory() != null) {
localFile = fileDialog.getDirectory() + fileDialog.getFile();
}
File file = new File(localFile);
file.createNewFile();
FileWriter fw = new FileWriter(file);
BufferedWriter bw = new BufferedWriter(fw);
for (Object[] campos : invalidos) {
bw.write(String.valueOf(campos[0]) + ";");
bw.write(String.valueOf(campos[2]) + ";");
bw.write(String.valueOf(campos[1]) + ";");
bw.write(campos[3] + "\n");
}
bw.flush();
bw.close();
Funcoes.mensagemInforma(this, "Itens inválidos salvos.");
this.dispose();
} catch (IOException e) {
e.printStackTrace();
Funcoes.mensagemErro(this, "Erro ao exportar produtos!\n" + e.getMessage(), true, con, e);
}
}
private void processaOPS(boolean det) {
if (Funcoes.mensagemConfirma(this, "Confirma o processamento dos itens selecionados?")
== JOptionPane.YES_OPTION) {
if (det) {
geraOPS();
} else {
}
}
}
public void actionPerformed(ActionEvent evt) {
String sUsu = txtUsuario.getText().trim().toLowerCase();
if (evt.getSource() == btOK) {
if (sUsu.trim().equals("")) {
Funcoes.mensagemInforma(this, "Usuario em branco!");
txtUsuario.requestFocus();
return;
} else if (txpSenha.getVlrString().trim().equals("")) {
Funcoes.mensagemInforma(this, "Senha em branco!");
txpSenha.requestFocus();
return;
}
if (sUsu.equals("sysdba")) bAdmin = true;
if (!sUsuAnt.equals(sUsu)) {
if (!execConexao(sUsu, txpSenha.getVlrString().trim())) return;
montaCombo(sUsu);
cbEmp.requestFocus();
if (cbEmp.getItemCount() == 1) btOK.doClick();
return;
} else if ("".equals(cbEmp.getVlrString().trim()) && !bAdmin) {
if (sUsuAnt.equals(sUsu)) {
Funcoes.mensagemInforma(this, "Filial não foi selecionada!");
cbEmp.requestFocus();
return;
}
}
// Setar a filial independentemente do usuário
// Incluído para corrigir problemas nas procedures
String[] codemp_codfilial = cbEmp.getVlrString().split("-");
if (Aplicativo.iCodEmp == 0) {
Aplicativo.iCodEmp = Integer.parseInt(codemp_codfilial[0]);
}
iFilialPadrao = Integer.parseInt(codemp_codfilial[1]);
iFilialMz = Integer.parseInt(codemp_codfilial[2]);
}
super.actionPerformed(evt);
}
private boolean dbAbrirCaixa() {
boolean abriuCaixa = false;
if (!ecf.leituraX()) {
Funcoes.mensagemErro(this, ecf.getMessageLog());
return abriuCaixa;
}
if (!ecf.suprimento(txtValor.getVlrBigDecimal())) {
Funcoes.mensagemErro(this, ecf.getMessageLog());
return abriuCaixa;
}
try {
PreparedStatement ps = con.prepareStatement("EXECUTE PROCEDURE PVABRECAIXASP(?,?,?,?,?,?,?)");
ps.setInt(1, AplicativoPDV.iCodCaixa);
ps.setInt(2, Aplicativo.iCodFilial);
ps.setInt(3, Aplicativo.iCodEmp);
ps.setBigDecimal(4, txtValor.getVlrBigDecimal());
ps.setDate(5, Funcoes.dateToSQLDate(new Date()));
ps.setInt(6, Aplicativo.iCodFilialPad);
ps.setString(7, Aplicativo.getUsuario().getIdusu());
ps.execute();
ps.close();
con.commit();
abriuCaixa = true;
} catch (SQLException e) {
Funcoes.mensagemErro(this, "Erro ao abrir o caixa!\n" + e.getMessage(), true, con, e);
e.printStackTrace();
}
ecf.abrirGaveta();
return abriuCaixa;
}
public Object[] loadUltimaVenda(
Integer codempvd,
Integer codfilialvd,
Integer codempcl,
Integer codfilialcl,
Integer codcli,
Date dtini,
Date dtfim)
throws Exception {
Object[] result = new Object[RESULT_ULTVENDA.values().length];
try {
StringBuilder sql = new StringBuilder();
sql.append("select first 1 v.dtemitvenda, v.vlrliqvenda, v.codvenda ");
sql.append("from vdvenda v, vdcliente c ");
sql.append(
"where v.codemp=? and v.codfilial=? and v.tipovenda='V' and v.dtemitvenda between ? and ? ");
sql.append("and c.codemp=v.codempcl and c.codfilial=v.codfilialcl and c.codcli=v.codcli ");
sql.append("and c.codemp=? and c.codfilial=? and c.codcli=? ");
sql.append("order by v.dtemitvenda desc, v.docvenda desc");
PreparedStatement ps = getConn().prepareStatement(sql.toString());
int param = 1;
ps.setInt(param++, codempvd);
ps.setInt(param++, codfilialvd);
ps.setDate(param++, Funcoes.dateToSQLDate(dtini));
ps.setDate(param++, Funcoes.dateToSQLDate(dtfim));
ps.setInt(param++, codempcl);
ps.setInt(param++, codfilialcl);
ps.setInt(param++, codcli);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
result[RESULT_ULTVENDA.DTEMITVENDA.ordinal()] =
rs.getDate(RESULT_ULTVENDA.DTEMITVENDA.name());
result[RESULT_ULTVENDA.VLRLIQVENDA.ordinal()] =
rs.getBigDecimal(RESULT_ULTVENDA.VLRLIQVENDA.name());
}
} catch (SQLException e) {
getConn().rollback();
throw new Exception(e.getMessage());
}
return result;
}
public void actionPerformed(ActionEvent evt) {
if (evt.getSource() == btOK) {
codprod = txtCodProd.getVlrInteger();
if (codprod <= 0) {
Funcoes.mensagemInforma(null, "Código do produto é obrigatório!!!");
return;
}
}
super.actionPerformed(evt);
}
public void setConexao(DbConnection cn) {
super.setConexao(cn);
lcCliente.setConexao(con);
lcProd.setConexao(con);
lcSecao.setConexao(con);
lcProd2.setConexao(con);
try {
daopush = new DAOPush(cn);
} catch (Exception e) {
Funcoes.mensagemErro(this, "Erro ao instanciar DAOPush!!!");
}
}
public void setConexao(DbConnection cn) {
super.setConexao(cn);
lcEmpr.setConexao(cn);
lcTurno.setConexao(cn);
lcAtendimento.setConexao(cn);
lcAtend.setConexao(cn);
lcAtend.carregaDados();
try {
daoatend =
new DAOAtendimento(cn, Aplicativo.iCodEmp, ListaCampos.getMasterFilial("ATATENDIMENTO"));
daoatend.setPrefs(Aplicativo.iCodEmp, ListaCampos.getMasterFilial("SGPREFERE3"));
} catch (SQLException e) {
Funcoes.mensagemErro(this, "Erro carregando preferências !\b" + e.getMessage());
}
}
private void exportar() {
if (produtos == null || produtos.size() == 0) {
Funcoes.mensagemInforma(this, "Não a produtos a exportar!");
return;
}
try {
File file = new File(txtDiretorio.getVlrString().trim());
file.createNewFile();
FileWriter fw = new FileWriter(file);
BufferedWriter bw = new BufferedWriter(fw);
int indice = 1;
status.setString("Exportando ...");
status.setMaximum(produtos.size());
for (String linha : produtos) {
bw.write(linha + "\n");
status.setValue(indice++);
}
bw.flush();
bw.close();
status.setString("Concluido !");
status.setMaximum(0);
btExeportar.setEnabled(false);
} catch (IOException e) {
e.printStackTrace();
Funcoes.mensagemErro(this, "Erro ao exportar produtos!\n" + e.getMessage(), true, con, e);
}
}
private void imprimir(TYPE_PRINT bVisualizar) {
PreparedStatement ps = null;
ResultSet rs = null;
StringBuffer sSQL = new StringBuffer();
DLRUf dl = new DLRUf();
try {
dl.setVisible(true);
if (dl.OK == false) {
dl.dispose();
return;
}
sSQL.append("SELECT U.SIGLAUF, U.CODPAIS, U.NOMEUF, U.CODUF, U.REGIAOUF, P.NOMEPAIS ");
sSQL.append("FROM SGUF U, SGPAIS P ");
sSQL.append("WHERE U.CODPAIS=P.CODPAIS ORDER BY " + dl.getValor());
ps = con.prepareStatement(sSQL.toString());
rs = ps.executeQuery();
if ("T".equals(dl.getTipo())) {
imprimirTexto(bVisualizar, rs);
} else if ("G".equals(dl.getTipo())) {
imprimirGrafico(bVisualizar, rs);
}
rs.close();
ps.close();
con.commit();
dl.dispose();
} catch (Exception err) {
Funcoes.mensagemErro(
this, "Erro ao montar relatório de UF!\n" + err.getMessage(), true, con, err);
err.printStackTrace();
}
}
private void geraTabTemp() {
try {
daopush.deletaTabTemp();
for (int i = 0; i < tabDet.getNumLinhas(); i++) {
if ((((Boolean) tabDet.getValor(i, DETALHAMENTO.MARCACAO.ordinal())).booleanValue())
&& ((BigDecimal) tabDet.getValor(i, DETALHAMENTO.QTDAPROD.ordinal())).floatValue()
> 0) {
daopush.insertPPProcessaOpTmp(
Funcoes.strDateToDate((String) tabDet.getValor(i, DETALHAMENTO.DTFABROP.ordinal())),
(BigDecimal) tabDet.getValor(i, DETALHAMENTO.QTDAPROD.ordinal()),
Aplicativo.iCodEmp,
Aplicativo.iCodFilial,
Aplicativo.getInstance().getCodest());
}
}
con.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
private void imprimirTexto(final TYPE_PRINT bVisualizar, final ResultSet rs) {
String sLinhaFina = StringFunctions.replicate("-", 125);
ImprimeOS imp = new ImprimeOS("", con);
int linPag = imp.verifLinPag() - 1;
imp.montaCab();
try {
imp.limpaPags();
imp.montaCab();
imp.setTitulo("Relatório de UF");
while (rs.next()) {
if (imp.pRow() == linPag) {
imp.pulaLinha(1, imp.comprimido());
imp.say(0, "+" + sLinhaFina + "+");
imp.eject();
imp.incPags();
}
if (imp.pRow() == 0) {
imp.impCab(126, false);
imp.say(0, imp.normal());
imp.say(2, "Cód.UF");
imp.say(10, "|");
imp.say(14, "Sigla.UF");
imp.say(24, "|");
imp.say(28, "Nome.UF");
imp.say(60, "|");
imp.say(64, "Região.UF");
imp.say(75, "|");
imp.say(79, "Cód País");
imp.say(89, "|");
imp.say(93, "Nome País");
imp.say(126, "|");
imp.pulaLinha(1, imp.normal());
imp.say(0, StringFunctions.replicate("-", 125));
}
imp.pulaLinha(1, imp.normal());
imp.say(2, rs.getString("CodUf").trim());
imp.say(10, "|");
imp.say(14, rs.getString("SiglaUf").trim());
imp.say(24, "|");
imp.say(28, rs.getString("NomeUf").trim());
imp.say(60, "|");
imp.say(64, rs.getString("RegiaoUf").trim());
imp.say(75, "|");
imp.say(79, rs.getString("CodPais").trim());
imp.say(89, "|");
imp.say(93, rs.getString("NomePais").trim());
imp.say(126, "|");
if (imp.pRow() >= linPag) {
imp.incPags();
imp.eject();
}
}
imp.pulaLinha(1, imp.normal());
imp.say(0, StringFunctions.replicate("=", 125));
imp.pulaLinha(1, imp.normal());
imp.eject();
imp.fechaGravacao();
if (bVisualizar == TYPE_PRINT.VIEW) {
imp.preview(this);
} else {
imp.print();
}
} catch (SQLException err) {
err.printStackTrace();
Funcoes.mensagemErro(this, "Erro consulta UF!\n" + err.getMessage(), true, con, err);
}
}
public Vector<Vector<Object>> loadProdVendas(
Integer codempvd,
Integer codfilialvd,
Integer codempcl,
Integer codfilialcl,
Integer codcli,
Integer codemppd,
Integer codfilialpd,
Integer codprod,
Date dtini,
Date dtfim)
throws Exception {
Vector<Vector<Object>> result = new Vector<Vector<Object>>();
try {
StringBuilder sql = new StringBuilder();
/*
STATUSVENDA, STATUSPGTO, CODVENDA, DOCVENDA, DTEMITVENDA, CODPROD, DESCPROD, QTDITVENDA, PRECOITVENDA, PERCDESCITVENDA
, VLRDESCITVENDA, VLRLIQITVENDA, TIPOVENDA;*/
sql.append("select pd.codprod, pd.descprod, max(vd.dtemitvenda) dtemitvenda ");
sql.append("from vdvenda vd ");
sql.append("inner join vditvenda iv ");
sql.append(
"on iv.codemp=vd.codemp and iv.codfilial=vd.codfilial and iv.tipovenda=vd.tipovenda and iv.codvenda=vd.codvenda ");
sql.append("inner join eqproduto pd ");
sql.append(
"on pd.codemp=iv.codemppd and pd.codfilial=iv.codfilialpd and pd.codprod=iv.codprod ");
sql.append("where vd.codemp=? and vd.codfilial=? and vd.dtemitvenda between ? and ? ");
sql.append("and vd.tipovenda='V' and vd.codempcl=? and vd.codfilialcl=? and vd.codcli=? ");
sql.append("and substring(vd.statusvenda from 1 for 1) not in ('C','N') ");
sql.append("and pd.codemp=? and pd.codfilial=? ");
if (codprod > 0) {
sql.append("and pd.codprod=? ");
}
sql.append("group by 1, 2 ");
sql.append("order by 3 desc, 2 ");
PreparedStatement ps = getConn().prepareStatement(sql.toString());
int iparam = 1;
ps.setInt(iparam++, codempvd);
ps.setInt(iparam++, codfilialvd);
ps.setDate(iparam++, Funcoes.dateToSQLDate(dtini));
ps.setDate(iparam++, Funcoes.dateToSQLDate(dtfim));
ps.setInt(iparam++, codempcl);
ps.setInt(iparam++, codfilialcl);
ps.setInt(iparam++, codcli);
ps.setInt(iparam++, codemppd);
ps.setInt(iparam++, codfilialpd);
if (codprod.intValue() > 0) {
ps.setInt(iparam++, codprod);
}
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Integer codprodvd = rs.getInt("CODPROD");
Date dtemitvenda = rs.getDate("DTEMITVENDA");
Vector<Object> row =
loadProdItemVenda(
codempvd,
codfilialvd,
dtemitvenda,
codempcl,
codfilialcl,
codcli,
codemppd,
codfilialpd,
codprodvd);
result.addElement(row);
}
rs.close();
ps.close();
getConn().commit();
} catch (SQLException e) {
getConn().rollback();
throw new Exception(e.getMessage());
}
return result;
}
public Vector<Object> loadProdItemVenda(
Integer codempvd,
Integer codfilialvd,
Date dtemitvenda,
Integer codempcl,
Integer codfilialcl,
Integer codcli,
Integer codemppd,
Integer codfilialpd,
Integer codprod)
throws Exception {
Vector<Object> row = new Vector<Object>();
try {
StringBuilder sql = new StringBuilder();
/*
STATUSVENDA, STATUSPGTO, CODVENDA, DOCVENDA, DTEMITVENDA, CODPROD, DESCPROD, QTDITVENDA, PRECOITVENDA, PERCDESCITVENDA
, VLRDESCITVENDA, VLRLIQITVENDA, TIPOVENDA;*/
sql.append("select first 1 vd.statusvenda, vd.codvenda, vd.docvenda, vd.dtemitvenda");
sql.append(", pd.codprod, pd.descprod, iv.qtditvenda, iv.precoitvenda, iv.percdescitvenda ");
sql.append(
", iv.vlrdescitvenda, iv.vlrliqitvenda, vd.tipovenda, vd.codplanopag, pg.descplanopag ");
sql.append(", vo.codvend, vo.nomevend, ax.codalmox, ax.descalmox ");
sql.append("from vdvenda vd ");
sql.append("inner join vditvenda iv ");
sql.append(
"on iv.codemp=vd.codemp and iv.codfilial=vd.codfilial and iv.tipovenda=vd.tipovenda and iv.codvenda=vd.codvenda ");
sql.append("and iv.codemppd=? and iv.codfilialpd=? and iv.codprod=? ");
sql.append("inner join eqproduto pd ");
sql.append(
"on pd.codemp=iv.codemppd and pd.codfilial=iv.codfilialpd and pd.codprod=iv.codprod ");
sql.append("inner join fnplanopag pg ");
sql.append(
"on pg.codemp=vd.codemppg and pg.codfilial=vd.codfilialpg and pg.codplanopag=vd.codplanopag ");
sql.append("inner join vdvendedor vo ");
sql.append(
"on vo.codemp=vd.codempvd and vo.codfilial=vd.codfilialvd and vo.codvend=vd.codvend ");
sql.append("inner join eqalmox ax ");
sql.append(
"on ax.codemp=pd.codempax and ax.codfilial=pd.codfilialax and ax.codalmox=pd.codalmox ");
sql.append("where vd.codemp=? and vd.codfilial=? and vd.tipovenda='V' and dtemitvenda=? ");
sql.append("and substring(vd.statusvenda from 1 for 1) not in ('C','N') ");
sql.append("and vd.codempcl=? and vd.codfilialcl=? and vd.codcli=? ");
sql.append("order by vd.dtemitvenda desc");
PreparedStatement ps = getConn().prepareStatement(sql.toString());
int iparam = 1;
ps.setInt(iparam++, codemppd);
ps.setInt(iparam++, codfilialpd);
ps.setInt(iparam++, codprod);
ps.setInt(iparam++, codempvd);
ps.setInt(iparam++, codfilialvd);
ps.setDate(iparam++, Funcoes.dateToSQLDate(dtemitvenda));
ps.setInt(iparam++, codempcl);
ps.setInt(iparam++, codfilialcl);
ps.setInt(iparam++, codcli);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
String tipovenda = rs.getString("TIPOVENDA");
Integer codvenda = rs.getInt("CODVENDA");
String statusvenda = rs.getString("STATUSVENDA");
java.sql.Date emaberto = null;
Integer pago = null;
Integer atraso = null;
Object[] result_receber2 = loadReceber2(codempvd, codfilialvd, tipovenda, codvenda);
if (result_receber2 != null) {
pago = (Integer) result_receber2[RESULT_RECEBER2.PAGO.ordinal()];
emaberto = (java.sql.Date) result_receber2[RESULT_RECEBER2.EMABERTO.ordinal()];
atraso = (Integer) result_receber2[RESULT_RECEBER2.ATRASO.ordinal()];
}
imgColuna = getImgStatus(statusvenda);
imgVencimento = getImgVencimento(statusvenda, emaberto, pago, atraso);
row.addElement(imgColuna);
row.addElement(imgVencimento);
row.addElement(codvenda);
row.addElement(rs.getString("DOCVENDA"));
row.addElement(Funcoes.dateToStrDate(rs.getDate("DTEMITVENDA")));
row.addElement(rs.getInt("CODPROD"));
row.addElement(rs.getString("DESCPROD"));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("QTDITVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("PRECOITVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("PERCDESCITVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("VLRDESCITVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("VLRLIQITVENDA")));
row.addElement(tipovenda);
row.addElement(rs.getInt("CODPLANOPAG"));
row.addElement(rs.getString("DESCPLANOPAG"));
row.addElement(rs.getInt("CODVEND"));
row.addElement(rs.getString("NOMEVEND"));
row.addElement(rs.getInt("CODALMOX"));
row.addElement(rs.getString("DESCALMOX"));
}
rs.close();
ps.close();
} catch (SQLException e) {
getConn().rollback();
throw new Exception(e.getMessage());
}
return row;
}
public Vector<Vector<Object>> loadVendas(
Integer codempvd,
Integer codfilialvd,
Integer codempcl,
Integer codfilialcl,
Integer codcli,
Integer codemppd,
Integer codfilialpd,
Integer codprod,
Date dtini,
Date dtfim)
throws Exception {
Vector<Vector<Object>> result = new Vector<Vector<Object>>();
try {
StringBuilder sql = new StringBuilder();
sql.append("select v.codvenda, v.docvenda, v.dtemitvenda, v.statusvenda, v.codplanopag");
sql.append(
", p.descplanopag, v.codvend, vd.nomevend, coalesce(v.vlrprodvenda,0) vlrprodvenda, coalesce(v.vlrdescvenda,0) vlrdescvenda");
sql.append(
", coalesce(v.vlradicvenda,0) vlradicvenda , coalesce(v.vlrfretevenda,0) vlrfretevenda, coalesce(v.vlrliqvenda,0) vlrliqvenda, v.tipovenda");
sql.append(
" , coalesce((select vf.tipofretevd from vdfretevd vf where vf.codemp = v.codemp and vf.codfilial = v.codfilial and vf.tipovenda = v.tipovenda and vf.codvenda = v.codvenda), 'N') as tipofrete ");
sql.append("from vdvenda v, fnplanopag p, vdvendedor vd, eqtipomov tm ");
sql.append(
"where v.codemp=? and v.codfilial=? and v.tipovenda='V' and v.dtemitvenda between ? and ?");
// sql.append( "and substring( v.statusvenda from 1 for 1) not in ('D','C') ");
sql.append(
"and p.codemp=v.codemppg and p.codfilial=v.codfilialpg and p.codplanopag=v.codplanopag ");
sql.append(
"and vd.codemp=v.codempvd and vd.codfilial=v.codfilialvd and vd.codvend=v.codvend");
if (codcli.intValue() > 0) {
sql.append(" and v.codempcl=? and v.codfilialcl=? AND codcli=? ");
}
if (codprod > 0) {
sql.append(" and exists (");
sql.append(" select codvenda from vditvenda iv where ");
sql.append(
" iv.codemp=v.codemp and iv.codfilial=v.codfilial and iv.codvenda=v.codvenda and iv.tipovenda=v.tipovenda ");
sql.append(" and iv.codemppd=? and iv.codfilialpd=? and iv.codprod=? ) ");
}
sql.append(
" and tm.codemp=v.codemptm and tm.codfilial=v.codfilialtm and tm.codtipomov=v.codtipomov ");
sql.append(" and tm.somavdtipomov='S' ");
sql.append(" order by v.dtemitvenda desc, v.docvenda desc");
PreparedStatement ps = getConn().prepareStatement(sql.toString());
int iparam = 1;
ps.setInt(iparam++, codempvd);
ps.setInt(iparam++, codfilialvd);
ps.setDate(iparam++, Funcoes.dateToSQLDate(dtini));
ps.setDate(iparam++, Funcoes.dateToSQLDate(dtfim));
if (codcli.intValue() > 0) {
ps.setInt(iparam++, codempcl);
ps.setInt(iparam++, codfilialcl);
ps.setInt(iparam++, codcli);
}
if (codprod.intValue() > 0) {
ps.setInt(iparam++, codemppd);
ps.setInt(iparam++, codfilialpd);
ps.setInt(iparam++, codprod);
}
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Vector<Object> row = new Vector<Object>();
String tipovenda = rs.getString("TIPOVENDA");
Integer codvenda = rs.getInt("CODVENDA");
String statusvenda = rs.getString("STATUSVENDA");
java.sql.Date emaberto = null;
Integer pago = null;
Integer atraso = null;
StringDireita stratraso = new StringDireita("");
Object[] result_receber2 = loadReceber2(codempvd, codfilialvd, tipovenda, codvenda);
if (result_receber2 != null) {
pago = (Integer) result_receber2[RESULT_RECEBER2.PAGO.ordinal()];
emaberto = (java.sql.Date) result_receber2[RESULT_RECEBER2.EMABERTO.ordinal()];
atraso = (Integer) result_receber2[RESULT_RECEBER2.ATRASO.ordinal()];
if ((atraso != null) && (atraso.intValue() != 0)) {
stratraso = new StringDireita(atraso.toString());
}
}
imgColuna = getImgStatus(statusvenda);
imgVencimento = getImgVencimento(statusvenda, emaberto, pago, atraso);
row.addElement(imgColuna);
row.addElement(imgVencimento);
row.addElement(stratraso);
row.addElement(codvenda);
row.addElement(rs.getString("DOCVENDA"));
row.addElement(Funcoes.dateToStrDate(rs.getDate("DTEMITVENDA")));
row.addElement(rs.getString("DESCPLANOPAG"));
row.addElement(rs.getString("NOMEVEND"));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("VLRPRODVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("VLRDESCVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("VLRADICVENDA")));
row.addElement(getTipoFrete(rs.getString("TIPOFRETE")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("VLRLIQVENDA")));
row.addElement(tipovenda);
result.addElement(row);
}
rs.close();
ps.close();
getConn().commit();
} catch (SQLException e) {
getConn().rollback();
throw new Exception(e.getMessage());
}
return result;
}
public Vector<Vector<Object>> loadItensVenda(
Integer codempvd,
Integer codfilialvd,
String tipovenda,
Integer codvenda,
Integer codemppd,
Integer codfilialpd,
Integer codprod)
throws Exception {
Vector<Vector<Object>> result = new Vector<Vector<Object>>();
try {
StringBuilder sql = new StringBuilder();
sql.append(
"select i.coditvenda, i.codprod, coalesce(obsitvenda, p.descprod) descprod, coalesce(i.codlote,'') codlote ");
sql.append(", coalesce(i.qtditvenda,0) qtditvenda, coalesce(i.precoitvenda,0) precoitvenda ");
sql.append(
", coalesce(i.vlrdescitvenda,0) vlrdescitvenda, coalesce(i.vlrfreteitvenda,0) vlrfreteitvenda ");
sql.append(", coalesce(i.vlrliqitvenda,0) vlrliqitvenda ");
sql.append(
", pg.codplanopag, pg.descplanopag, vo.codvend, vo.nomevend, ax.codalmox, ax.descalmox ");
sql.append("from vditvenda i ");
sql.append("inner join eqproduto p ");
sql.append("on p.codemp=i.codemppd and p.codfilial=i.codfilialpd and p.codprod=i.codprod ");
sql.append("inner join vdvenda v ");
sql.append(
"on v.codemp=i.codemp and v.codfilial=i.codfilial and v.tipovenda=i.tipovenda and v.codvenda=i.codvenda ");
sql.append("inner join fnplanopag pg ");
sql.append(
"on pg.codemp=v.codemppg and pg.codfilial=v.codfilialpg and pg.codplanopag=v.codplanopag ");
sql.append("inner join vdvendedor vo ");
sql.append(
"on vo.codemp=v.codempvd and vo.codfilial=v.codfilialvd and vo.codvend=v.codvend ");
sql.append("inner join eqalmox ax ");
sql.append(
"on ax.codemp=p.codempax and ax.codfilial=p.codfilialax and ax.codalmox=p.codalmox ");
sql.append("where i.codemp=? and i.codfilial=? and i.codvenda=? and i.tipovenda=? ");
if (codprod.intValue() > 0) {
sql.append(" and p.codprod=? ");
}
sql.append("order by i.coditvenda");
PreparedStatement ps = getConn().prepareStatement(sql.toString());
int iparam = 1;
ps.setInt(iparam++, codempvd);
ps.setInt(iparam++, codfilialvd);
ps.setInt(iparam++, codvenda);
ps.setString(iparam++, tipovenda);
if (codprod.intValue() > 0) {
ps.setInt(iparam++, codprod);
}
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Vector<Object> row = new Vector<Object>();
row.addElement(rs.getInt("CODITVENDA"));
row.addElement(rs.getInt("CODPROD"));
row.addElement(rs.getString("DESCPROD"));
row.addElement(rs.getString("CODLOTE"));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("QTDITVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("PRECOITVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("VLRDESCITVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("VLRFRETEITVENDA")));
row.addElement(Funcoes.bdToStr(rs.getBigDecimal("VLRLIQITVENDA")));
row.addElement(tipovenda);
row.addElement(rs.getInt("CODPLANOPAG"));
row.addElement(rs.getString("DESCPLANOPAG"));
row.addElement(rs.getInt("CODVEND"));
row.addElement(rs.getString("NOMEVEND"));
row.addElement(rs.getInt("CODALMOX"));
row.addElement(rs.getString("DESCALMOX"));
result.addElement(row);
}
rs.close();
ps.close();
getConn().commit();
} catch (SQLException e) {
getConn().rollback();
throw new Exception(e.getMessage());
}
return result;
}
public void imprimir(TYPE_PRINT bVisualizar) {
PreparedStatement ps = null;
ResultSet rs = null;
StringBuilder sCab = new StringBuilder();
StringBuilder sql = new StringBuilder();
Blob fotoemp = FPrinterJob.getLogo(con);
if (txtDatafim.getVlrDate().before(txtDataini.getVlrDate())) {
Funcoes.mensagemInforma(this, "Data inicial maior que a data final!");
return;
}
sCab.append("Período de " + txtDataini.getVlrString() + " a " + txtDatafim.getVlrString());
if (txtCodAtend.getVlrInteger().intValue() > 0) {
sCab.append(txtCodAtend.getVlrInteger().toString() + " - " + txtNomeAtend.getVlrString());
}
if (!"".equals(txtCodUsu.getVlrString())) {
sCab.append(" - Usuário: " + txtCodUsu.getVlrString());
}
sql.append(
"select e.nomeatend, a.idusuins, a.codatend, a.dataatendo, a.dtins , a.horaatendofin, a.hins, cast ( ( ( ( case when a.hins-a.horaatendofin>0 then ");
sql.append(" a.hins-a.horaatendofin else 0 end) / 60 / 60 ) + ( ");
sql.append("(a.dtins-a.dataatendo) * 24) / 24 ) as decimal(15,2) ) numdias , ");
sql.append("cast ( ( (case when a.hins-a.horaatendofin>0 then ");
sql.append("a.hins-a.horaatendofin else 0 end) / 60 / 60 ) + ( ");
sql.append("(a.dtins-a.dataatendo) * 24) as decimal(15,2) ) qtdhorasint , ");
sql.append(
"cast( ( a.horaatendofin- a.horaatendo) / 60 / 60 as decimal(15,2) ) qtdhoras , ea.descespec, ea.codespec ");
sql.append(
"from atatendimento a, atatendente e, atespecatend ea where a.codemp=? and a.codfilial=? and a.dataatendo between ? and ? ");
sql.append("and e.codemp=a.codempae and e.codfilial=a.codfilialae");
if (txtCodAtend.getVlrInteger() > 0) {
sql.append(" and a.codatend=? ");
}
sql.append(" and e.codatend=a.codatend ");
if (!"".equals(txtCodUsu.getVlrString())) {
sql.append(" and a.idusuins=? ");
}
sql.append(
" and ea.codemp=a.codempea and ea.codfilial=a.codfilialea and ea.codespec=a.codespec ");
sql.append("order by ");
sql.append(cbOrdem.getVlrString());
try {
ps = con.prepareStatement(sql.toString());
int param = 1;
ps.setInt(param++, Aplicativo.iCodEmp);
ps.setInt(param++, ListaCampos.getMasterFilial("ATATENDIMENTO"));
ps.setDate(param++, Funcoes.dateToSQLDate(txtDataini.getVlrDate()));
ps.setDate(param++, Funcoes.dateToSQLDate(txtDatafim.getVlrDate()));
if (txtCodAtend.getVlrInteger() > 0) {
ps.setInt(param++, txtCodAtend.getVlrInteger());
}
if (!"".equals(txtCodUsu.getVlrString())) {
ps.setString(param++, txtCodUsu.getVlrString().toUpperCase());
}
rs = ps.executeQuery();
} catch (Exception err) {
Funcoes.mensagemErro(
this, "Erro consulta Relatório de premiação!\n" + err.getMessage(), true, con, err);
err.printStackTrace();
}
imprimiGrafico(bVisualizar, rs, sCab.toString(), fotoemp);
}
private void carregaItens() {
try {
tabDet.limpa();
ResultSet rs =
daopush.carregaItens(
Aplicativo.iCodEmp,
ListaCampos.getMasterFilial("EQPRODUTO"),
txtCodProd.getVlrInteger(),
lcSecao.getCodEmp(),
lcSecao.getCodFilial(),
txtCodSecao.getVlrString());
int row = 0;
BigDecimal totqtdminimo = new BigDecimal(0);
BigDecimal totqtdestoq = new BigDecimal(0);
BigDecimal totqtdreq = new BigDecimal(0);
BigDecimal totqtdemprod = new BigDecimal(0);
BigDecimal totqtdaprod = new BigDecimal(0);
ResultSet rs2 = null;
PreparedStatement ps2 = null;
while (rs.next()) {
BigDecimal qtdreserv = new BigDecimal(0);
BigDecimal qtdminimo =
rs.getBigDecimal(DETALHAMENTO.QTDMINPROD.toString()).setScale(Aplicativo.casasDec);
BigDecimal qtdestoque =
rs.getBigDecimal(DETALHAMENTO.QTDESTOQUE.toString()).setScale(Aplicativo.casasDec);
BigDecimal qtdemprod =
rs.getBigDecimal(DETALHAMENTO.QTDEMPROD.toString()).setScale(Aplicativo.casasDec);
BigDecimal qtdreq =
rs.getBigDecimal(DETALHAMENTO.QTDREQ.toString()).setScale(Aplicativo.casasDec);
BigDecimal qtdaprod =
new BigDecimal(
qtdminimo.floatValue()
+ qtdreq.floatValue()
- qtdestoque.floatValue()
- qtdemprod.floatValue());
totqtdminimo = totqtdminimo.add(qtdminimo);
totqtdestoq = totqtdestoq.add(qtdestoque);
totqtdreq = totqtdreq.add(qtdreq);
totqtdemprod = totqtdemprod.add(qtdemprod);
totqtdaprod = totqtdaprod.add(qtdaprod);
if (qtdaprod.floatValue() > 0) {
tabDet.adicLinha();
tabDet.setColColor(-1, DETALHAMENTO.DTFABROP.ordinal(), Color.WHITE, Color.RED);
tabDet.setColColor(-1, DETALHAMENTO.QTDAPROD.ordinal(), Color.WHITE, Color.RED);
tabDet.setValor(new Boolean(false), row, DETALHAMENTO.MARCACAO.ordinal());
if (qtdreq.floatValue() > 0) {
imgColuna = imgUrgente;
} else {
imgColuna = imgNormal;
}
tabDet.setValor(imgColuna, row, DETALHAMENTO.STATUS.ordinal());
tabDet.setValor(Funcoes.dateToStrDate(new Date()), row, DETALHAMENTO.DTFABROP.ordinal());
tabDet.setValor(
rs.getInt(DETALHAMENTO.CODEMPPD.toString()), row, DETALHAMENTO.CODEMPPD.ordinal());
tabDet.setValor(
rs.getInt(DETALHAMENTO.CODFILIALPD.toString()),
row,
DETALHAMENTO.CODFILIALPD.ordinal());
tabDet.setValor(
rs.getInt(DETALHAMENTO.CODPROD.toString()), row, DETALHAMENTO.CODPROD.ordinal());
tabDet.setValor(
rs.getString(DETALHAMENTO.REFPROD.toString()), row, DETALHAMENTO.REFPROD.ordinal());
tabDet.setValor(
rs.getInt(DETALHAMENTO.SEQEST.toString()), row, DETALHAMENTO.SEQEST.ordinal());
tabDet.setValor(
rs.getString(DETALHAMENTO.DESCPROD.toString().trim()),
row,
DETALHAMENTO.DESCPROD.ordinal());
tabDet.setValor(qtdminimo, row, DETALHAMENTO.QTDMINPROD.ordinal());
tabDet.setValor(qtdestoque, row, DETALHAMENTO.QTDESTOQUE.ordinal());
tabDet.setValor(qtdreq, row, DETALHAMENTO.QTDREQ.ordinal());
tabDet.setValor(qtdemprod, row, DETALHAMENTO.QTDEMPROD.ordinal());
tabDet.setValor(qtdaprod, row, DETALHAMENTO.QTDAPROD.ordinal());
row++;
}
if (totqtdaprod.floatValue() < 0) {
totqtdaprod = new BigDecimal(0);
}
txtQtdRequisitada.setVlrBigDecimal(totqtdreq);
txtQtdEstoque.setVlrBigDecimal(totqtdestoq);
if (txtCodProd.getVlrInteger() > 0) {
txtQtdProducao.setVlrBigDecimal(totqtdemprod);
txtQtdProduzir.setVlrBigDecimal(totqtdaprod);
} else {
txtQtdProducao.setVlrString("-");
txtQtdProduzir.setVlrString("-");
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
private void montaRel() {
imprimeRodape(false);
Vector<Vector<Object>> vData = new Vector<Vector<Object>>();
double dVlrOutros = 0.0;
double dVlrTotal = 0.0;
double dValor = 0.0;
double dValorPerc = 0.0;
String sLabel = "";
try {
while (rs.next()) {
Vector<Object> vLinha = new Vector<Object>();
vLinha.addElement(rs.getString(2).trim());
vLinha.addElement(new Double(rs.getDouble(4)));
vData.addElement(vLinha);
dVlrTotal += rs.getDouble(4);
}
} catch (SQLException e) {
Funcoes.mensagemInforma(this, "Erro na consulta de valores!\n" + e.getMessage());
}
for (int i2 = 0; vData.size() > i2; i2++) {
dValor = ((Double) vData.elementAt(i2).elementAt(1)).doubleValue();
dValorPerc = (dValor * 100) / dVlrTotal;
if (dValorPerc < 3.0) {
dVlrOutros += dValor;
} else {
sLabel = ((String) vData.elementAt(i2).elementAt(0));
sLabel = sLabel + " (" + Funcoes.strDecimalToStrCurrency(14, 2, dValor + "") + " ) ";
datasetBar.addValue(dValor, sLabel, "");
}
}
if (dVlrOutros > 0.0) datasetBar.addValue(dVlrOutros, "Outros valores", "");
JFreeChart chart = createChart(datasetBar);
setBordaRel();
int iY = 35;
drawLinha(0, iY, 0, 0, AL_LL);
iY += 14;
setFonte(fnTopEmp);
drawTexto(
sTitulo1, 0, iY, getFontMetrics(fnCabEmp).stringWidth(" " + sTitulo1 + " "), AL_CEN);
setFonte(fnCabEmpNeg);
iY += 6;
drawLinha(0, iY, 0, 0, AL_LL);
iY += 14;
setFonte(fnTopEmp);
drawTexto(
sTitulo2, 0, iY, getFontMetrics(fnCabEmp).stringWidth(" " + sTitulo2 + " "), AL_CEN);
setFonte(fnCabEmpNeg);
iY += 6;
drawLinha(0, iY, 0, 0, AL_LL);
iY += 50;
drawGrafico(chart, 15, iY, 500, 550);
iY += 12;
setFonte(fnTopEmp);
drawTexto(
"Valor total:" + Funcoes.strDecimalToStrCurrency(14, 2, dVlrTotal + ""),
0,
iY,
getFontMetrics(fnCabEmp)
.stringWidth(
" Valor total:" + Funcoes.strDecimalToStrCurrency(14, 2, dVlrTotal + "") + " "),
AL_CEN);
termPagina();
finaliza();
}
public void imprimir(TYPE_PRINT bVisualizar) {
if (txtDatafim.getVlrDate().before(txtDataini.getVlrDate())) {
Funcoes.mensagemInforma(this, "Data final maior que a data inicial!");
return;
}
PreparedStatement ps = null;
ResultSet rs = null;
StringBuffer sql = new StringBuffer();
StringBuffer sCab = new StringBuffer();
sCab.append(
"Período de : "
+ Funcoes.dateToStrDate(txtDataini.getVlrDate())
+ "Até : "
+ Funcoes.dateToStrDate(txtDatafim.getVlrDate()));
try {
sql.append(
"SELECT CL.CODVEND, VO.NOMEVEND, CL.CODCLI, CL.RAZCLI, ( SELECT MAX(DTEMITVENDA) FROM VDVENDA VD, VDCLIENTE SB ");
sql.append("WHERE SB.CODEMPPQ=CL.CODEMP AND ");
sql.append("SB.CODFILIALPQ=CL.CODFILIAL AND ");
sql.append("SB.CODPESQ=CL.CODCLI AND ");
sql.append(
"VD.CODEMPCL=SB.CODEMP AND VD.CODFILIALCL=SB.CODFILIAL AND VD.CODCLI=SB.CODCLI) ULTIMACOMPRA ");
sql.append("FROM VDCLIENTE CL LEFT OUTER JOIN VDVENDEDOR VO ON ");
sql.append(
"VO.CODEMP=CL.CODEMPVD AND VO.CODFILIAL=CL.CODFILIALVD AND VO.CODVEND=CL.CODVEND ");
sql.append("WHERE ");
sql.append(
"CL.CODEMP=CL.CODEMPPQ AND CL.CODFILIAL=CL.CODFILIALPQ AND CL.CODCLI=CL.CODPESQ AND ");
sql.append("NOT EXISTS( SELECT * FROM VDVENDA VD, VDCLIENTE SB ");
sql.append("WHERE SB.CODEMPPQ=CL.CODEMP AND ");
sql.append("SB.CODFILIALPQ=CL.CODFILIAL AND ");
sql.append("SB.CODPESQ=CL.CODCLI AND ");
sql.append(
"VD.CODEMPCL=SB.CODEMP AND VD.CODFILIALCL=SB.CODFILIAL AND VD.CODCLI=SB.CODCLI AND ");
sql.append("VD.DTEMITVENDA BETWEEN ? AND ? ) ");
sql.append("AND CL.ATIVOCLI='S' ");
if (txtCodComiss.getVlrInteger() > 0) {
sql.append(" and cl.codempvd=? and cl.codfilialvd=? and cl.codvend=? ");
}
sql.append("order by cl.codvend, cl.razcli ");
ps = con.prepareStatement(sql.toString());
int param = 1;
ps.setDate(param++, Funcoes.dateToSQLDate(txtDataini.getVlrDate()));
ps.setDate(param++, Funcoes.dateToSQLDate(txtDatafim.getVlrDate()));
if (txtCodComiss.getVlrInteger() > 0) {
ps.setInt(param++, lcComiss.getCodEmp());
ps.setInt(param++, lcComiss.getCodFilial());
ps.setInt(param++, txtCodComiss.getVlrInteger());
}
rs = ps.executeQuery();
imprimiGrafico(bVisualizar, rs, sCab.toString());
con.commit();
} catch (Exception e) {
e.printStackTrace();
Funcoes.mensagemInforma(this, "Erro ao buscar dados do relatório!");
}
}