public Integer getObsAnswerBetweenDates(
Integer patientId,
List<Integer> questions,
Integer answerId,
Date beforeDate,
Date afterDate,
Date targetDate) {
SQLQuery obsBeforeDate =
sessionFactory
.getCurrentSession()
.createSQLQuery(
"select obs_id from obs where person_id = :patientId and value_coded = :conceptId and concept_id in (:questions) and voided = 0 and obs_dateTime > :beforeDate and obs_dateTime < :afterDate ORDER BY abs(:targetDate - obs_dateTime)");
obsBeforeDate.setInteger("patientId", patientId);
obsBeforeDate.setInteger("conceptId", answerId);
obsBeforeDate.setDate("beforeDate", beforeDate);
obsBeforeDate.setDate("afterDate", afterDate);
obsBeforeDate.setDate("targetDate", targetDate);
obsBeforeDate.setParameterList("questions", questions);
List<Integer> obs = obsBeforeDate.list();
if (obs != null && obs.size() > 0) {
return obs.get(0);
}
return null;
}
public void applyParameters(SQLQuery query) throws SQLException {
for (Map.Entry<QueryParameter, Object> e : parameters.entrySet()) {
QueryParameter p = e.getKey();
Object value = e.getValue();
String name = p.getSqlName();
if (value != null && p.getArray()) {
switch (p.getType()) {
case DATE:
query.setParameterList(name, (Object[]) value, new DateType());
break;
case NUMBER:
query.setParameterList(name, (Object[]) value, new BigDecimalType());
break;
case STRING:
query.setParameterList(name, (Object[]) value, new StringType());
break;
case CLOB:
case BLOB:
throw new ClientErrorException(
Status.CLIENT_ERROR_BAD_REQUEST,
String.format("LOBs are not supported as parameters: %s", name));
}
} else {
switch (p.getType()) {
case DATE:
query.setDate(name, (Date) value);
break;
case NUMBER:
query.setBigDecimal(name, (BigDecimal) value);
break;
case STRING:
query.setString(name, (String) value);
break;
case CLOB:
case BLOB:
throw new ClientErrorException(
Status.CLIENT_ERROR_BAD_REQUEST,
String.format("LOBs are not supported as parameters: %s", name));
}
}
}
}
public Integer getEncounterBetweenDates(
Integer patientId,
List<Integer> encounterTypes,
Date beforeDate,
Date afterDate,
Date targetDate) {
SQLQuery obsBeforeDate =
sessionFactory
.getCurrentSession()
.createSQLQuery(
"select encounter_id from encounter where patient_id = :patientId and encounter_type in (:encounterTypes) and voided = 0 and encounter_datetime > :beforeDate and encounter_datetime < :afterDate ORDER BY abs(:targetDate - encounter_datetime)");
obsBeforeDate.setInteger("patientId", patientId);
obsBeforeDate.setDate("beforeDate", beforeDate);
obsBeforeDate.setDate("afterDate", afterDate);
obsBeforeDate.setDate("targetDate", targetDate);
obsBeforeDate.setParameterList("encounterTypes", encounterTypes);
List<Integer> obs = obsBeforeDate.list();
if (obs != null && obs.size() > 0) {
return obs.get(0);
}
return null;
}
public List<BlogEntry> findBlogEntriesInDirectory(
WikiDirectory startDir,
WikiDocument ignoreDoc,
Pager pager,
Integer year,
Integer month,
Integer day,
String tag,
boolean countComments) {
final Map<Long, BlogEntry> blogEntryMap = new HashMap<Long, BlogEntry>();
StringBuilder queryString = new StringBuilder();
queryString.append("select").append(" ");
for (int i = 0; i < getWikiDocumentSQLColumnNames().length; i++) {
queryString.append(getWikiDocumentSQLColumnNames()[i]);
if (i != getWikiDocumentSQLColumnNames().length - 1) queryString.append(", ");
}
queryString.append(", '0' as COMMENT_COUNT").append(" ");
queryString.append(getblogEntryFromClause(tag));
queryString.append(getBlogEntryWhereClause(ignoreDoc, year, month, day, tag));
queryString.append(" ");
queryString.append("order by doc2.CREATED_ON desc");
SQLQuery query = getSession().createSQLQuery(queryString.toString());
bindBlogEntryWhereClause(query, startDir, ignoreDoc, year, month, day, tag);
query.setComment("Finding all blogEntry documents recursively in dir: " + startDir.getName());
query.addEntity(WikiDocument.class);
query.addScalar("COMMENT_COUNT", Hibernate.LONG);
query.setFirstResult(pager.getQueryFirstResult());
query.setMaxResults(pager.getQueryMaxResults());
query.setResultTransformer(
new ResultTransformer() {
public Object transformTuple(Object[] result, String[] aliases) {
BlogEntry be = new BlogEntry();
be.setEntryDocument((WikiDocument) result[0]);
blogEntryMap.put(
be.getEntryDocument().getId(),
be); // Put in map so we can attach comment count later
return be;
}
public List transformList(List list) {
return list;
}
});
List<BlogEntry> result = (List<BlogEntry>) query.list();
if (countComments && result.size() > 0) {
// The risk here is that pager.getQueryMaxResults() is too large for the IN() operator of some
// DBs...
StringBuilder commentQueryString = new StringBuilder();
commentQueryString
.append("select doc.NODE_ID as DOC_ID, count(c1.NODE_ID) as COMMENT_COUNT")
.append(" ");
commentQueryString.append("from WIKI_DOCUMENT doc").append(" ");
commentQueryString
.append("left outer join WIKI_NODE c1 on doc.NODE_ID = c1.PARENT_NODE_ID")
.append(" ");
commentQueryString.append("where doc.NODE_ID in (:blogEntriesIds)").append(" ");
commentQueryString.append("group by doc.NODE_ID");
SQLQuery commentQuery = getSession().createSQLQuery(commentQueryString.toString());
commentQuery.setComment("Finding comment count for blog entries");
commentQuery.addScalar("DOC_ID");
commentQuery.addScalar("COMMENT_COUNT");
commentQuery.setParameterList("blogEntriesIds", blogEntryMap.keySet());
commentQuery.setResultTransformer(
new ResultTransformer() {
public Object transformTuple(Object[] result, String[] aliases) {
BlogEntry be = blogEntryMap.get(((BigInteger) result[0]).longValue());
be.setCommentCount(((BigInteger) result[1]).longValue());
return null;
}
public List transformList(List list) {
return list;
}
});
commentQuery.list();
}
return result;
}