@Override
public List<Report595DTO> getTopSkippedQuestions(
List<Integer> clinicIds, String fromDate, String toDate) {
List<Report595DTO> resultList = new ArrayList<>();
Query q =
entityManager.createNativeQuery(
"SELECT count(*), measure_id FROM veteran_assessment_question_presence vsaq "
+ "INNER JOIN veteran_assessment va ON vsaq.veteran_assessment_id = va.veteran_assessment_id "
+ " WHERE skipped = -1 AND date_completed >= :fromDate AND date_completed <= :toDate "
+ "AND clinic_id IN (:clinicIds) AND date_completed IS NOT NULL "
+ " GROUP BY measure_id "
+ " ORDER BY count(*) DESC ");
setParametersFor593(q, fromDate, toDate, clinicIds);
List r = q.getResultList();
if (r != null && r.size() > 0) {
for (Object aRow : q.getResultList()) {
Object[] data = (Object[]) aRow;
int count = ((Number) data[0]).intValue();
if (count == 0) break;
int measureId = ((Number) data[1]).intValue();
Measure m = measureRepository.findOne(measureId);
// only allow singleSelect or multiSelect
int measureType = m.getMeasureType().getMeasureTypeId();
if (measureType != 1 && measureType != 2 && measureType != 3) {
continue;
}
Report595DTO dto = new Report595DTO();
Query q1 =
entityManager.createNativeQuery(
"select count(*) from veteran_assessment_question_presence vsaq "
+ "inner join veteran_assessment va on vsaq.veteran_assessment_id = va.veteran_assessment_id "
+ " where date_completed >= :fromDate and date_completed <= :toDate "
+ "and clinic_id in (:clinicIds) and date_completed is not null "
+ " and measure_id ="
+ measureId);
setParametersFor593(q1, fromDate, toDate, clinicIds);
int total = ((Number) q1.getSingleResult()).intValue();
DecimalFormat formatter = new DecimalFormat("###.##");
String percent = formatter.format(count * 100d / total);
dto.setPercentage(percent + "% (" + count + "/" + total + ")");
dto.setQuestions(((Number) data[1]).intValue() + "");
dto.setQuestions(m.getMeasureText());
dto.setVariableName(m.getVariableName());
resultList.add(dto);
}
}
Collections.sort(
resultList,
new Comparator<Report595DTO>() {
@Override
public int compare(Report595DTO o1, Report595DTO o2) {
String[] a1 = o1.getPercentage().split("%");
String[] a2 = o2.getPercentage().split("%");
float one = Float.parseFloat(a1[0]);
float two = Float.parseFloat(a2[0]);
if (one > two) return 1;
else if (one < two) return -1;
return 0;
}
});
int index = 1;
List<Report595DTO> results = new ArrayList<>(20);
for (Report595DTO dto : resultList) {
dto.setOrder(Integer.toString(index++));
results.add(dto);
if (index > 20) break;
}
return results;
}
@Override
public List<Report593ByTimeDTO> getBatteriesByTimeFor593(
String fromDate, String toDate, List<Integer> clinicIds) {
HashMap<String, Report593ByTimeDTO> cache = new HashMap<>();
Report593ByTimeDTO dto = null;
Query q =
entityManager.createNativeQuery(
"SELECT date_format(date_completed, '%m/%d/%Y'), date_format(date_completed, '%W'), "
+ " count(*)"
+ " FROM veteran_assessment "
+ " WHERE assessment_status_id <> 7 AND date_completed >= :fromDate AND date_completed <= :toDate "
+ "AND clinic_id IN (:clinicIds) AND date_completed IS NOT NULL "
+ "AND extract(HOUR FROM date_completed) >= :fr AND extract(HOUR FROM date_completed) <= :to "
+ "GROUP BY date_format( date_completed, '%Y%m%d' ) "
+ "ORDER BY date_format( date_completed, '%Y%m%d' ) ");
setParametersFor593(q, fromDate, toDate, clinicIds);
List result = getData(q, 6, 7);
if (result != null && result.size() > 0) {
for (Object aRow : result) {
Object[] objs = (Object[]) aRow;
dto = new Report593ByTimeDTO();
dto.setDate((String) objs[0]);
dto.setDayOfWeek((String) objs[1]);
dto.setSixToEight(Integer.toString(((Number) objs[2]).intValue()));
cache.put(dto.getDate(), dto);
}
}
setParametersFor593(q, fromDate, toDate, clinicIds);
result = getData(q, 8, 9);
if (result != null && result.size() > 0) {
for (Object aRow : result) {
Object[] objs = (Object[]) aRow;
dto = cache.get((String) objs[0]);
if (dto == null) {
dto = new Report593ByTimeDTO();
}
dto.setDate((String) objs[0]);
dto.setDayOfWeek((String) objs[1]);
dto.setEightToTen(Integer.toString(((Number) objs[2]).intValue()));
cache.put(dto.getDate(), dto);
}
}
result = getData(q, 10, 11);
if (result != null && result.size() > 0) {
for (Object aRow : result) {
Object[] objs = (Object[]) aRow;
dto = cache.get((String) objs[0]);
if (dto == null) {
dto = new Report593ByTimeDTO();
}
dto.setDate((String) objs[0]);
dto.setDayOfWeek((String) objs[1]);
dto.setTenToTwelve(Integer.toString(((Number) objs[2]).intValue()));
cache.put(dto.getDate(), dto);
}
}
result = getData(q, 12, 13);
if (result != null && result.size() > 0) {
for (Object aRow : result) {
Object[] objs = (Object[]) aRow;
dto = cache.get((String) objs[0]);
if (dto == null) {
dto = new Report593ByTimeDTO();
}
dto.setDate((String) objs[0]);
dto.setDayOfWeek((String) objs[1]);
dto.setTwelveToTwo(Integer.toString(((Number) objs[2]).intValue()));
cache.put(dto.getDate(), dto);
}
}
result = getData(q, 14, 15);
if (result != null && result.size() > 0) {
for (Object aRow : result) {
Object[] objs = (Object[]) aRow;
dto = cache.get((String) objs[0]);
if (dto == null) {
dto = new Report593ByTimeDTO();
}
dto.setDate((String) objs[0]);
dto.setDayOfWeek((String) objs[1]);
dto.setTwoToFour(Integer.toString(((Number) objs[2]).intValue()));
cache.put(dto.getDate(), dto);
}
}
result = getData(q, 16, 17);
if (result != null && result.size() > 0) {
for (Object aRow : result) {
Object[] objs = (Object[]) aRow;
dto = cache.get((String) objs[0]);
if (dto == null) {
dto = new Report593ByTimeDTO();
}
dto.setDate((String) objs[0]);
dto.setDayOfWeek((String) objs[1]);
dto.setFourToSix(Integer.toString(((Number) objs[2]).intValue()));
cache.put(dto.getDate(), dto);
}
}
List<Report593ByTimeDTO> resultList = new ArrayList<>();
if (!cache.isEmpty()) {
resultList.addAll(cache.values());
for (Report593ByTimeDTO d : resultList) {
if (d.getEightToTen() == null) {
d.setEightToTen("0");
}
if (d.getFourToSix() == null) {
d.setFourToSix("0");
}
if (d.getSixToEight() == null) {
d.setSixToEight("0");
}
if (d.getTenToTwelve() == null) {
d.setTenToTwelve("0");
}
if (d.getTwelveToTwo() == null) {
d.setTwelveToTwo("0");
}
if (d.getTwoToFour() == null) {
d.setTwoToFour("0");
}
d.setTotal(
Integer.toString(
Integer.parseInt(d.getEightToTen())
+ Integer.parseInt(d.getFourToSix())
+ Integer.parseInt(d.getSixToEight())
+ Integer.parseInt(d.getTenToTwelve())
+ Integer.parseInt(d.getTwelveToTwo())
+ Integer.parseInt(d.getTwoToFour())));
}
Collections.sort(
resultList,
new Comparator<Report593ByTimeDTO>() {
@Override
public int compare(Report593ByTimeDTO o1, Report593ByTimeDTO o2) {
SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
try {
return sdf.parse(o1.getDate()).compareTo(sdf.parse(o2.getDate()));
} catch (Exception e) {
return 0;
}
}
});
}
return resultList;
}