@Override
public List<Number> getAgeStatistics(List<Integer> cList, String fromDate, String toDate) {
Query q =
entityManager.createNativeQuery(
"SELECT avg(datediff(CURDATE(), STR_TO_DATE(text_value, '%m/%d/%Y'))/365), min(datediff(CURDATE(), STR_TO_DATE(text_value, '%m/%d/%Y'))/365), "
+ "max(datediff(CURDATE(), STR_TO_DATE(text_value, '%m/%d/%Y'))/365)"
+ " FROM survey_measure_response WHERE measure_answer_id = 170 "
+ " AND text_value IS NOT NULL AND veteran_assessment_id IN "
+ "(SELECT veteran_assessment_id FROM veteran_assessment WHERE "
+ "assessment_status_id <> 7 AND clinic_id IN (:clinicIds) AND "
+ "date_completed >= :fromDate "
+ " AND date_completed <= :toDate AND date_completed IS NOT NULL)");
setParametersFor593(q, fromDate, toDate, cList);
List r = q.getResultList();
if (r == null || r.size() == 0) {
return null;
} else {
List<Number> result = new ArrayList<>();
Object[] aRow = (Object[]) r.get(0);
if (aRow[0] != null) {
result.add((Number) aRow[0]);
}
if (aRow[1] != null) {
result.add((Number) aRow[1]);
}
if (aRow[2] != null) {
result.add((Number) aRow[2]);
}
return result;
}
}
@Override
public List<Number> getNumOfDeploymentStatistics(
List<Integer> cList, String fromDate, String toDate) {
Query q =
entityManager.createNativeQuery(
"SELECT avg(numOfDeployment), min(numOfDeployment), max(numOfDeployment) FROM (SELECT count(*) AS numOfDeployment"
+ " FROM survey_measure_response WHERE measure_answer_id = 1210 "
+ " AND text_value IS NOT NULL AND veteran_assessment_id IN (SELECT veteran_assessment_id FROM veteran_assessment WHERE assessment_status_id <> 7 AND clinic_id IN (:clinicIds) AND date_completed >= :fromDate "
+ " AND date_completed <= :toDate AND date_completed IS NOT NULL)"
+ " GROUP BY veteran_assessment_id) a ");
setParametersFor593(q, fromDate, toDate, cList);
List r = q.getResultList();
if (r == null || r.size() == 0) {
return null;
} else {
List<Number> result = new ArrayList<>();
Object[] aRow = (Object[]) r.get(0);
if (aRow[0] != null) {
result.add((Number) aRow[0]);
}
if (aRow[1] != null) {
result.add((Number) aRow[1]);
}
if (aRow[2] != null) {
result.add((Number) aRow[2]);
}
return result;
}
}
@Override
public Date getDateModified(int veteranAssessmentId) {
String sql =
"SELECT UNIX_TIMESTAMP(max(date_created)), UNIX_TIMESTAMP(max(date_modified)) FROM survey_measure_response WHERE veteran_assessment_id = :veteranAssessmentId";
@SuppressWarnings("unchecked")
List<Object[]> rows =
entityManager
.createNativeQuery(sql)
.setParameter("veteranAssessmentId", veteranAssessmentId)
.getResultList();
if (rows.isEmpty()) {
// no results saved for this assessment
return new Date(0);
}
Object[] row = rows.get(0);
Long created = null;
try {
created = Long.valueOf(row[0].toString());
} catch (Exception e) {
}
Long modified = null;
try {
modified = Long.valueOf(row[1].toString());
} catch (Exception e) {
}
if (created == null) {
if (modified == null) {
// no results saved for this assessment
return new Date(0);
}
// this should never happen but we know what happens when we say that :)
return new Date(modified);
} else if (modified == null) {
return new Date(created);
}
// found both
return new Date(Math.max(created, modified));
}