@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;
}
@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;
}