public PageBean<VideoRmpPatrolJob> getPatrolJobPage(VideoRmpPatrolJobDTO dto, List<Integer> regionIDs, List<Map<String, Date>> dateMapList, Integer userId) { Integer pageNo = dto.getPageNo(); Integer pageSize = dto.getPageSize(); if (pageNo == 0) { pageNo = 1; } String listString = "("; if (regionIDs != null && regionIDs.size() > 0) { for (Integer regionID : regionIDs) { listString += regionID + ","; } listString = listString.substring(0, listString.length() - 1) + ")"; } else { listString = ""; } StringBuffer querySql = new StringBuffer(); querySql.append("SELECT\n" + "\tjob.*\n" + "FROM\n" + "\tvideo_rmp_patrol_job job\n" + "LEFT JOIN s_region s ON job.region_id = s.region_id\n" + "WHERE "); for (int i = 0; i < dateMapList.size(); i++) { if (i > 0) { querySql.append(" OR ("); } querySql.append("\t1 = 1\n" + "\t\t\tAND job.circle_start_date >= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.START_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + "\t\t\tAND job.circle_start_date <= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.END_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + "\t\t\tAND s.region_level = 0\n" + " and job.is_delete =0 "); if(userId!=null && userId!=4){ querySql.append(" and job.patrol_person_id = "+userId); } if (StringUtils.isNotBlank(dto.getStoreName())) { querySql.append(" and s.name like ‘%" + dto.getStoreName() + "%‘ ESCAPE ‘/‘ "); } querySql.append("\t\t\tAND job.region_id IN " + listString + " " + " "); if (i > 0) { querySql.append("\t) "); } querySql.append(" or(" + "1=1" + "\t\t\tAND job.circle_end_date >= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.START_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + "\t\t\tAND job.circle_end_date <= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.END_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + "\t\t\tAND s.region_level = 0\n" + " and job.is_delete =0 "); if(userId!=null && userId!=4){ querySql.append(" and job.patrol_person_id = "+userId); } if (StringUtils.isNotBlank(dto.getStoreName())) { querySql.append(" and s.name like ‘%" + dto.getStoreName() + "%‘ ESCAPE ‘/‘ "); } querySql.append("\t\t\tAND job.region_id IN " + listString + " " + " ) "); } querySql.append(" order BY " + " circle_start_date LIMIT " + pageSize + " OFFSET " + (pageNo - 1) * pageSize); final String sql = querySql.toString(); List<Object[]> list = (List<Object[]>) this.getHibernateTemplate().execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { SQLQuery query = session.createSQLQuery(sql); return query.list(); } }); ArrayList<VideoRmpPatrolJob> jobs = new ArrayList<>(); if (list != null && list.size() > 0) { for (Object[] o : list) { VideoRmpPatrolJob job = new VideoRmpPatrolJob(); job.setId((Integer) o[0]); job.setPatrolJobName((String) o[1]); job.setPlanId((Integer) o[2]); job.setCircleStartDate((Date) o[3]); job.setCircleEndDate((Date) o[4]); job.setPatrolPersonId((Integer) o[5]); job.setPatrolPersonName((String) o[6]); job.setStoreId((Integer) o[7]); job.setRegionId((Integer) o[8]); job.setStatus((Integer) o[9]); job.setCreateTime((Timestamp) o[10]); job.setUpdateTime((Timestamp) o[11]); job.setRemark((String) o[12]); job.setIsDelete((Integer) o[13]); jobs.add(job); } } //计算总数 StringBuffer stringBuffer2 = new StringBuffer(); stringBuffer2.append("SELECT\n" + " count(*) " + "FROM\n" + "\tvideo_rmp_patrol_job job\n" + "LEFT JOIN s_region s ON job.region_id = s.region_id\n" + "WHERE "); for (int i = 0; i < dateMapList.size(); i++) { if (i > 0) { stringBuffer2.append(" OR ("); } stringBuffer2.append("\t1 = 1\n" + "\t\t\tAND job.circle_start_date >= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.START_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + "\t\t\tAND job.circle_start_date <= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.END_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + "\t\t\tAND s.region_level = 0\n" + " and job.is_delete =0 "); if(userId!=null && userId!=4){ stringBuffer2.append(" and job.patrol_person_id = "+userId); } if (StringUtils.isNotBlank(dto.getStoreName())) { stringBuffer2.append(" and s.name like ‘%" + dto.getStoreName() + "%‘ ESCAPE ‘/‘ "); } stringBuffer2.append("\t\t\tAND job.region_id IN " + listString + " " + " "); if (i > 0) { stringBuffer2.append("\t) "); } stringBuffer2.append(" or(" + "1=1" + "\t\t\tAND job.circle_end_date >= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.START_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + "\t\t\tAND job.circle_end_date <= ‘" + RmpDateTimeUtil.dateToString( dateMapList.get(i).get(RmpReportConstant.END_TIME),RmpDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss) + "‘\n" + "\t\t\tAND s.region_level = 0\n" + " and job.is_delete =0 "); if(userId!=null && userId!=4){ stringBuffer2.append(" and job.patrol_person_id = "+userId); } if (StringUtils.isNotBlank(dto.getStoreName())) { stringBuffer2.append(" and s.name like ‘%" + dto.getStoreName() + "%‘ ESCAPE ‘/‘ "); } stringBuffer2.append("\t\t\tAND job.region_id IN " + listString + " " + " ) "); } final String sql2 = stringBuffer2.toString(); Integer count = (Integer) this.getHibernateTemplate().execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { SQLQuery query = session.createSQLQuery(sql2); return Integer.valueOf(query.uniqueResult().toString()); } }); PageBean<VideoRmpPatrolJob> page = new PageBean<>(count, pageNo, pageSize, jobs); return page; }