public List<HumanDto> load(
int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters) {
StringBuffer sql =
new StringBuffer(
" SELECT DISTINCT s.human_id,s.organization_id,s.org_root_id,s.first_name,s.last_name,s.full_name,s.avatar_url,s.birthday,s.national_id,s.province_id, "
+ "s.district_id,s.street_id,s.address,s.phone,s.tel,s.email_address,s.possition,s.gender,s.cmt,s.cmt_approve_date,s.cmt_issue_plance_id, "
+ "s.yahoo,s.skype, s.status human_status,s.comments,u.*, u.password as retype_password, s.modified_date "
+ "from human s LEFT JOIN users u on s.human_id = u.human_id ");
this.buildQuery(sql, filters);
if (sortField != null && sortOrder != SortOrder.UNSORTED) {
sql.append(" order by s.")
.append(sortField)
.append(" ")
.append(sortOrder == SortOrder.ASCENDING ? "ASC" : "DESC");
} else {
sql.append(" order by s.modified_date desc");
}
Query query = em.createNativeQuery(sql.toString(), HumanDto.class);
String birthday = (String) filters.get(IHumanDtoService.USER_BITHDAY);
if (birthday != null) {
query.setParameter(
1,
DateTimeUtils.convertStringToDate(birthday, DateTimeUtils.ddMMyyyy),
TemporalType.DATE);
}
query.setFirstResult(first);
query.setMaxResults(pageSize);
return query.getResultList();
}
public int counter(Map<String, Object> filters) {
StringBuffer counter =
new StringBuffer(
"select count(DISTINCT(s.human_id)) from human s LEFT JOIN users u on s.human_id = u.human_id ");
this.buildQuery(counter, filters);
Query counterQuery = em.createNativeQuery(counter.toString());
String birthday = (String) filters.get(IHumanDtoService.USER_BITHDAY);
if (birthday != null) {
counterQuery.setParameter(
1,
DateTimeUtils.convertStringToDate(birthday, DateTimeUtils.ddMMyyyy),
TemporalType.DATE);
}
return ((java.math.BigDecimal) counterQuery.getSingleResult()).intValue();
}