public Page<StockAllot> page( int pageNo, int pageSize, Map<String, Object> filter, Integer qryType) { String userView = Person.tableName; List<Object> parame = new ArrayList<Object>(); StringBuffer sql = new StringBuffer(" from "); sql.append(tableName); sql.append(" t left join "); sql.append(Depot.tableName); sql.append(" dout on dout.id=t.out_depot_id left join "); sql.append(Depot.tableName); sql.append(" dto on dto.id=t.to_depot_id left join "); sql.append(userView); sql.append(" c on c.id=t.creater_id left join "); sql.append(userView); sql.append(" u on u.id=t.updater_id left join "); sql.append(userView); sql.append(" h on h.id=t.head_id "); sql.append(" where t.company_id=? "); parame.add(filter.get("company_id")); String keyword = (String) filter.get("keyword"); if (StringUtils.isNotEmpty(keyword)) { sql.append(" and t.billsn like ?"); keyword = "%" + keyword + "%"; parame.add(keyword); } String start_date = (String) filter.get("start_date"); if (StringUtils.isNotEmpty(start_date)) { sql.append(" and (t.create_datetime >= ? or t.bill_date>=?)"); parame.add(start_date + " 00:00:00"); parame.add(start_date); } String end_date = (String) filter.get("end_date"); if (StringUtils.isNotEmpty(end_date)) { sql.append(" and (t.create_datetime <= ? or t.bill_date<=?)"); parame.add(end_date + " 23:59:59"); parame.add(end_date); } Integer submit_status = (Integer) filter.get("submit_status"); // 提交状态 if (submit_status != null) { sql.append(" and t.submit_status = ?"); parame.add(submit_status); } Integer is_deleted = (Integer) filter.get("is_deleted"); // 是否删除 if (is_deleted != null) { sql.append(" and t.is_deleted = ?"); parame.add(is_deleted); } String user_id = (String) filter.get("user_id"); // 当前用户id String position_id = (String) filter.get("position_id"); // 当前用户岗位id String sql_ = "WITH RECURSIVE d AS (SELECT d1.id,d1.pid,d1.name,d1.sort_num,d1.type FROM v_user_position d1 where d1.id=?" + "union ALL SELECT d2.id,d2.pid,d2.name,d2.sort_num,d2.type FROM v_user_position d2, d WHERE d2.pid = d.id) " + "SELECT distinct id FROM d where type=10 and d.id not in (select id from sso_user where position_id =? and id!=?)"; switch (qryType) { case -1: // 我创建的及我负责的 sql.append(" and (t.creater_id=? or t.head_id=?)"); parame.add(user_id); parame.add(user_id); break; case 0: // 我创建的 sql.append(" and t.creater_id=?"); parame.add(user_id); break; case 1: // 我负责的 sql.append(" and t.head_id=?"); parame.add(user_id); break; case 2: // 下属创建的 sql.append(" and t.creater_id in("); sql.append(sql_); sql.append(") and t.creater_id !=?"); parame.add(position_id); parame.add(position_id); parame.add(user_id); parame.add(user_id); break; case 3: // 下属负责的 sql.append(" and t.head_id in("); sql.append(sql_); sql.append(") and t.head_id !=?"); parame.add(position_id); parame.add(position_id); parame.add(user_id); parame.add(user_id); break; case 5: // 我及下属的 sql.append(" and (t.head_id in("); sql.append(sql_); sql.append(")or t.creater_id in("); sql.append(sql_); sql.append(")or t.head_id =? or t.creater_id=?)"); parame.add(position_id); parame.add(position_id); parame.add(user_id); parame.add(position_id); parame.add(position_id); parame.add(user_id); parame.add(user_id); parame.add(user_id); break; } String sortField = (String) filter.get("_sortField"); if (StringUtils.isNotEmpty(sortField)) { sql.append(" order by "); sql.append(sortField); sql.append(" "); sql.append((String) filter.get("_sort")); } return dao.paginate( pageNo, pageSize, "select t.*,h.realname as head_name,c.realname as creater_name,u.realname as updater_name,dout.name out_depot_name,dto.name to_depot_name ", sql.toString(), parame.toArray()); }