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;
  }
コード例 #2
0
ファイル: Handler.java プロジェクト: eimarfandino/resthub
  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;
  }
コード例 #4
0
ファイル: BlogDAO.java プロジェクト: subaochen/seam
  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;
  }