コード例 #1
0
  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;
  }
コード例 #2
0
 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;
 }
コード例 #3
0
  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;
  }