private Object[] loadReceber2(
Integer codempvd, Integer codfilialvd, String tipovenda, Integer codvenda)
throws SQLException {
Object[] result = null;
StringBuilder sql = new StringBuilder();
sql.append("select codemp, codfilial, codrec");
sql.append(", (select first 1 ir.dtvencitrec ");
sql.append(" from fnitreceber ir ");
sql.append(
" where ir.codemp=rc.codemp and ir.codfilial=rc.codfilial and ir.codrec=rc.codrec and ir.statusitrec = 'R1' ");
sql.append(" order by ir.dtvencitrec) ");
sql.append(" emaberto ");
sql.append(", (select first 1 ir.dtvencitrec - ir.dtliqitrec ");
sql.append(" from fnitreceber ir ");
sql.append(
" where ir.codemp=rc.codemp and ir.codfilial=rc.codfilial and ir.codrec=rc.codrec and ir.statusitrec = 'RP' ");
sql.append(" order by ir.dtvencitrec) ");
sql.append(" pago ");
sql.append(
", (select max( 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 ) ");
sql.append(" from fnitreceber ir ");
sql.append(" where ir.codemp=rc.codemp and ir.codfilial=rc.codfilial and ir.codrec=rc.codrec ");
sql.append(" ) ");
sql.append(" atraso ");
sql.append("from fnreceber rc ");
sql.append("where rc.codempva=? and rc.codfilialva=? and rc.tipovenda=? and rc.codvenda=?");
PreparedStatement ps = getConn().prepareStatement(sql.toString());
int param = 1;
ps.setInt(param++, codempvd);
ps.setInt(param++, codfilialvd);
ps.setString(param++, tipovenda);
ps.setInt(param++, codvenda);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
result = new Object[RESULT_RECEBER2.values().length];
result[RESULT_RECEBER2.EMABERTO.ordinal()] = rs.getDate(RESULT_RECEBER2.EMABERTO.name());
result[RESULT_RECEBER2.PAGO.ordinal()] = new Integer(rs.getInt(RESULT_RECEBER2.PAGO.name()));
result[RESULT_RECEBER2.ATRASO.ordinal()] =
new Integer(rs.getInt(RESULT_RECEBER2.ATRASO.name()));
}
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;
}