`
public static Map<String, Object> getPageListOrder(Map<String, String> map) throws Exception{
Map<String, Object> mapreturn = new HashMap<String, Object>();
StringBuffer selectEntry = new StringBuffer("select tb1.order_id,tb1.order_date,tb1.created_by,tb4.to_name,tb1.remaining_sub_total,tb2.amount as postage_amount,tb5.site_name,case when tb1.status_id='ORDER_APPROVED' then '支付成功，等待发货' when tb1.status_id='ORDER_CANCELLED' then '已取消' when tb1.status_id='ORDER_COMPLETED' and tb1.is_Viewed='Y' then '收货成功' when tb1.status_id='ORDER_COMPLETED' and tb1.is_Viewed='N' then '发货成功，等待收货' when tb1.status_id='ORDER_CREATED' then '创建成功，等待支付' when tb1.status_id='ORDER_APPROVED' then '支付成功，等待发货' end ");
StringBuffer selectCount = new StringBuffer("select count(1) as total ");
StringBuffer selectTable = new StringBuffer();
selectTable.append("from order_header as tb1 ");
selectTable.append("left join order_adjustment as tb2 on tb2.order_id = tb1.order_id ");
selectTable.append("left join order_contact_mech as tb3 on tb3.order_id = tb1.order_id ");
selectTable.append("left join postal_address as tb4 on tb4.contact_mech_id = tb3.contact_mech_id ");
selectTable.append("left join web_site as tb5 on tb5.web_site_id = tb1.web_site_id ");
selectTable.append("where tb1.order_id like 'BHGXCO%' ");
ArrayList parameters = new ArrayList();
// 订单状态查询
String statusId = map.get("statusId");
if(StringUtils.isNotBlank(statusId)){
parameters.add(statusId);
selectTable.append("and tb1.status_id = ? ");
}
/** 收货人姓名查询 /
String toName = map.get("to_name");
if(StringUtils.isNotBlank(toName)){
parameters.add(toName);
selectTable.append("and tb4.to_name = ? ");
}
/ 订单号查询 /
String orderId = map.get("orderId");
if(StringUtils.isNotBlank(orderId)){
parameters.add(orderId);
selectTable.append("and tb1.order_id = ? ");
}
/ 开始时间查询 /
String startTime = map.get("start_time");
if(StringUtils.isNotBlank(startTime)){
parameters.add(DateUtils.formatDate(startTime + " 00:00:00"));
selectTable.append("and tb1.order_date >= ? ");
}
/ 结束时间查询 /
String endTime = map.get("start_time");
if(StringUtils.isNotBlank(endTime)){
parameters.add(DateUtils.formatDate(endTime + " 23:59:59"));
selectTable.append("and tb1.order_date <= ? ");
}
/ 查询满足查询条件的记录数量 /
selectCount.append(selectTable);
SqlQuery totalQuery = Ebean.createSqlQuery(selectCount.toString());
for(int index = 0 ;index < parameters.size() ;index++){
totalQuery.setParameter(index + 1,parameters.get(index));
}
int total = totalQuery.findUnique().getInteger("total");
/ 查询满足查询条件的记录 /
/ 分页 /
Integer page = Integer.parseInt(map.get("p"));
Integer pagesize = Integer.parseInt(map.get("pagesize"));
List headers = new ArrayList();
if(total != 0){
/ 排序方式 **/
selectTable.append("order by tb1.created_stamp desc ");
parameters.add(pagesize);
parameters.add((page - 1) * pagesize);
selectTable.append("limit ? offset ? ");
selectEntry.append(selectTable);
SqlQuery entryQuery = Ebean.createSqlQuery(selectEntry.toString());
for(int index = 0 ;index < parameters.size() ;index++){
entryQuery.setParameter(index + 1,parameters.get(index));
}
List rows = entryQuery.findList();
for(SqlRow row :rows){
OrderHeader bean = new OrderHeader();
bean.setOrderId(row.getString("order_id"));
bean.setOrderDate(row.getDate("order_date"));
bean.setCreatedBy(row.getString("created_by"));
bean.setToName(row.getString("to_name"));
bean.setRemainingSubTotal(row.getBigDecimal("remaining_sub_total"));
bean.setOrderPostageCost(row.getDouble("postage_amount"));
bean.setWebSiteId(row.getString("site_name"));
bean.setStatusId(row.getString("case"));
headers.add(bean);
}
}

    mapreturn.put("num",total);
    mapreturn.put("page",page);
    mapreturn.put("pagesize",pagesize);
    mapreturn.put("datalist",headers);
    return mapreturn;
    `