示例#1
0
  @Override
  public Element search(Map parms) {
    Filter filter = cookFilter(parms);

    if (filter.count() == 0) throw new InvalidDataException("请设置查询过滤条件.");

    int count = this.getCount(filter);
    if (count > VSSConfig.getInstance().getRowsLimitHard())
      throw new InvalidDataException("满足条件的记录数已超过系统处理上限,请重新设置查询条件.");

    String sql =
        " SELECT "
            + " t.sheetid, status4purchase( t.status ) status, t.validdays, "
            + " t.orderdate, t.venderid, v.vendername, sg.categoryname majorname, pay.paytypename, "
            + " t.deadline, name4code(t.logistics,'logistics') as logistics, t.note, "
            + " t.editor, t.editdate, t.checker, t.checkdate, t.releasedate, t.readtime ,status4purchasetype(t.purchasetype) purchasetype "
            + " FROM (SELECT DISTINCT p0.sheetid,p0.validdays,p0.orderdate,p0.venderid,p0.purchasetype, "
            + " p0.deadline,p0.logistics,p0.note,p0.editor,p0.editdate,p0.checker,p0.checkdate, "
            + " p0.paytypeid,p0.sgroupid,c.status,c.releasedate, c.readtime  FROM purchase0_bak  p0  "
            + " JOIN purchase_bak  p ON ( p0.sheetid = p.refsheetid ) "
            + " JOIN cat_order_bak c ON ( c.sheetid = p0.sheetid ) WHERE "
            + filter.toString()
            + ") t "
            + " JOIN vender v ON ( v.venderid = t.venderid ) "
            + " JOIN paytype pay ON ( pay.paytypeid = t.paytypeid ) "
            + " left JOIN category sg ON ( sg.categoryid = t.sgroupid ) "
            + " ORDER BY t.checkdate DESC, t.status";

    Element elm_cat = SqlUtil.getRowSetElement(conn, sql, "rowset");
    elm_cat.setAttribute("row_total", "" + count);
    return elm_cat;
  }
示例#2
0
  public Filter cookFilter(Map map) throws InvalidDataException {
    Filter filter = new Filter();
    String[] ss = null;

    ss = (String[]) map.get("venderid");
    if (ss != null && ss.length > 0 && ss[0] != null && ss[0].length() > 0) {
      Values val_vender = new Values(ss);
      filter.add("p.venderid IN (" + val_vender.toString4String() + ") ");
    }

    ss = (String[]) map.get("month");
    if (ss != null && ss.length > 0) {
      Calendar cal = Calendar.getInstance();
      SimpleDateFormat oSdf = new SimpleDateFormat("", Locale.ENGLISH);
      oSdf.applyPattern("yyyy-MM");
      try {
        cal.setTime(oSdf.parse(ss[0]));
      } catch (ParseException e) {
        throw new InvalidDataException(e);
      }
      int num2 = cal.getActualMaximum(Calendar.DAY_OF_MONTH);

      filter.add(" (p.sdate) >= " + ValueAdapter.std2mdy(ss[0] + "-01"));
      filter.add(" (p.sdate) <= " + ValueAdapter.std2mdy(ss[0] + "-" + num2));
    }

    ss = (String[]) map.get("shopid");
    if (ss != null && ss.length > 0) {
      Values val_shopid = new Values(ss);
      filter.add("p.shopid IN (" + val_shopid.toString4String() + ") ");
    }
    return filter;
  }
示例#3
0
  @Override
  public int getCount(Filter filter) {
    String sql =
        " SELECT count( DISTINCT p0.sheetid ) FROM purchase0_bak p0 "
            + " JOIN purchase_bak p ON ( p0.sheetid = p.refsheetid ) "
            + " JOIN cat_order_bak c ON ( c.sheetid = p0.sheetid ) WHERE "
            + filter.toString();

    String temp = SqlUtil.querySQL4SingleColumn(conn, sql).get(0);
    int rows = 0;
    if (temp != null) {
      rows = Integer.parseInt(temp);
    }

    return rows;
  }
示例#4
0
  public Filter cookFilter(Map parms) {
    Filter filter = new Filter();
    String[] ss = null;

    ss = (String[]) parms.get("venderid");
    if (ss != null && ss.length > 0 && ss[0] != null && ss[0].length() > 0) {
      Values val_vender = new Values(ss);
      filter.add("c.venderid IN (" + val_vender.toString4String() + ") ");
    }

    ss = (String[]) parms.get("sheetid");
    if (ss != null && ss.length > 0 && ss[0] != null && ss[0].length() > 0) {
      Values val_sheetid = new Values(ss);
      filter.add("c.sheetid IN (" + val_sheetid.toString4String() + ") ");
      return filter;
    }

    ss = (String[]) parms.get("sheetid_purchase");
    if (ss != null && ss.length > 0 && ss[0] != null && ss[0].length() > 0) {
      Values val_sheetid = new Values(ss);
      filter.add("p.sheetid IN (" + val_sheetid.toString4String() + ") ");
      return filter;
    }

    /** 根据订货地过滤 */
    ss = (String[]) parms.get("shopid");
    if (ss != null && ss.length > 0) {
      Values val_shopid = new Values(ss);
      filter.add("p.shopid IN (" + val_shopid.toString4String() + ") ");
    }

    /** 根据收货地过滤 */
    ss = (String[]) parms.get("destshopid");
    if (ss != null && ss.length > 0) {
      Values val_shopid = new Values(ss);
      filter.add("p.destshopid IN (" + val_shopid.toString4String() + ") ");
    }

    ss = (String[]) parms.get("status");
    if (ss != null && ss.length > 0) {
      Values val_status = new Values(ss);
      filter.add("c.status IN (" + val_status.toString4String() + ") ");
    }

    ss = (String[]) parms.get("logistics");
    if (ss != null && ss.length > 0) {
      Values val_logistics = new Values(ss);
      filter.add("p0.logistics IN (" + val_logistics.toString4String() + ") ");
    }

    ss = (String[]) parms.get("editdate_min");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(p0.editdate) >= " + ValueAdapter.std2mdy(date));
    }

    ss = (String[]) parms.get("editdate_max");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(p0.editdate) <= " + ValueAdapter.std2mdy(date));
    }

    ss = (String[]) parms.get("checkdate_min");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(p0.checkdate) >= " + ValueAdapter.std2mdy(date));
    }

    ss = (String[]) parms.get("checkdate_max");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(p0.checkdate) <= " + ValueAdapter.std2mdy(date));
    }

    ss = (String[]) parms.get("deadline_min");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(p0.deadline) >= " + ValueAdapter.std2mdy(date));
    }

    ss = (String[]) parms.get("deadline_max");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(p0.deadline) <= " + ValueAdapter.std2mdy(date));
    }

    ss = (String[]) parms.get("orderdate_min");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(p0.orderdate) >= " + ValueAdapter.std2mdy(date));
    }

    ss = (String[]) parms.get("orderdate_max");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(p0.orderdate) <= " + ValueAdapter.std2mdy(date));
    }

    ss = (String[]) parms.get("releasedate_min");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(c.releasedate) >= " + ValueAdapter.std2mdy(date));
    }

    ss = (String[]) parms.get("releasedate_max");
    if (ss != null && ss.length > 0) {
      String date = ss[0];
      filter.add("trunc(c.releasedate) <= " + ValueAdapter.std2mdy(date));
    }
    return filter;
  }