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