/*
* OpenClinica is distributed under the
* GNU Lesser General Public License (GNU LGPL).
* For details see: http://www.openclinica.org/license
* copyright 2003-2005 Akaza Research
*/
package org.akaza.openclinica.dao.managestudy;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.akaza.openclinica.bean.core.DataEntryStage;
import org.akaza.openclinica.bean.core.DiscrepancyNoteType;
import org.akaza.openclinica.bean.core.ResolutionStatus;
import org.akaza.openclinica.bean.core.Status;
import org.akaza.openclinica.bean.login.UserAccountBean;
import org.akaza.openclinica.bean.managestudy.DiscrepancyNoteBean;
import org.akaza.openclinica.bean.managestudy.StudyBean;
import org.akaza.openclinica.bean.managestudy.StudySubjectBean;
import org.akaza.openclinica.dao.QueryStore;
import org.akaza.openclinica.service.DiscrepancyNotesSummary;
import org.akaza.openclinica.service.managestudy.ViewNotesFilterCriteria;
import org.akaza.openclinica.service.managestudy.ViewNotesSortCriteria;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
/**
* @author Doug Rodrigues (douglas.rodrigues@openclinica.com)
* @author Leonel Gayard (leonel.gayard@gmail.com)
*/
public class ViewNotesDaoImpl extends NamedParameterJdbcDaoSupport implements ViewNotesDao {
private static final Logger LOG = LoggerFactory.getLogger(ViewNotesDaoImpl.class);
private static final String QUERYSTORE_FILE = "viewnotes";
private QueryStore queryStore;
private static final RowMapper<DiscrepancyNoteBean> DISCREPANCY_NOTE_ROW_MAPPER = new RowMapper<DiscrepancyNoteBean>() {
@Override
public DiscrepancyNoteBean mapRow(ResultSet rs, int rowNum) throws SQLException {
DiscrepancyNoteBean b = new DiscrepancyNoteBean();
b.setId(rs.getInt("discrepancy_note_id"));
b.setEntityId(rs.getInt("entity_id"));
b.setColumn(rs.getString("column_name"));
b.setStudyId(rs.getInt("study_id"));
b.setSubjectId(rs.getInt("study_subject_id"));
StudySubjectBean studySubjectBean = new StudySubjectBean();
studySubjectBean.setId(b.getSubjectId());
studySubjectBean.setLabel(rs.getString("label"));
studySubjectBean.setStatus(Status.get(rs.getInt("ss_status_id")));
b.setStudySub(studySubjectBean);
b.setDiscrepancyNoteTypeId(rs.getInt("discrepancy_note_type_id"));
b.setDisType(DiscrepancyNoteType.get(b.getDiscrepancyNoteTypeId()));
b.setResolutionStatusId(rs.getInt("resolution_status_id"));
b.setResStatus(ResolutionStatus.get(b.getResolutionStatusId()));
b.setSiteId(rs.getString("site_id"));
b.setCreatedDate(rs.getDate("date_created"));
b.setUpdatedDate(rs.getDate("date_updated"));
b.setDays(rs.getInt("days"));
if (rs.wasNull()) {
b.setDays(null);
}
b.setAge(rs.getInt("age"));
if (rs.wasNull()) {
b.setAge(null);
}
b.setEventName(rs.getString("event_name"));
b.setEventStart(rs.getDate("date_start"));
b.setCrfName(rs.getString("crf_name"));
int statusId = rs.getInt("status_id");
if (statusId != 0) {
b.setCrfStatus(DataEntryStage.get(statusId).getName());
}
b.setEntityName(rs.getString("entity_name"));
b.setEntityValue(rs.getString("value"));
b.setEntityType(rs.getString("entity_type"));
b.setDescription(rs.getString("description"));
b.setDetailedNotes(rs.getString("detailed_notes"));
b.setNumChildren(rs.getInt("total_notes"));
String userName = rs.getString("user_name");
if (!StringUtils.isEmpty(userName)) {
UserAccountBean userBean = new UserAccountBean();
userBean.setName(userName);
userBean.setFirstName(rs.getString("first_name"));
userBean.setLastName(rs.getString("last_name"));
b.setAssignedUser(userBean);
}
String ownerUserName = rs.getString("owner_user_name");
if (!StringUtils.isEmpty(ownerUserName)) {
UserAccountBean userBean = new UserAccountBean();
userBean.setName(ownerUserName);
userBean.setFirstName(rs.getString("owner_first_name"));
userBean.setLastName(rs.getString("owner_last_name"));
b.setOwner(userBean);
}
// The discrepancy note's item ID is not null only when type =
// 'itemData'
if (b.getEntityType().equals("itemData")) {
b.setItemId(rs.getInt("item_id"));
}
return b;
}
};
@Override
public List<DiscrepancyNoteBean> findAllDiscrepancyNotes(StudyBean currentStudy, ViewNotesFilterCriteria filter, ViewNotesSortCriteria sort) {
Map<String, Object> arguments = listNotesArguments(currentStudy);
List<DiscrepancyNoteBean> result =
getNamedParameterJdbcTemplate().query(listNotesSql(filter, sort, arguments, currentStudy.isSite(currentStudy.getParentStudyId())), arguments,
DISCREPANCY_NOTE_ROW_MAPPER);
return result;
}
@Override
public DiscrepancyNotesSummary calculateNotesSummary(StudyBean currentStudy, ViewNotesFilterCriteria filter) {
Map<String, Object> arguments = new HashMap<String, Object>(2);
arguments.put("studyId", currentStudy.getId());
List<String> terms = new ArrayList<String>();
terms.add(queryStore.query(QUERYSTORE_FILE, "countDiscrepancyNotes.main"));
// terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.filter.studyHideCrf"));
if (currentStudy.isSite(currentStudy.getParentStudyId())) {
terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.filter.siteHideCrf"));
}
// Reuse the filter criteria from #findAllDiscrepancyNotes, as both
// queries load data from the same view
if (filter != null) {
for (String filterKey : filter.getFilters().keySet()) {
String filterQuery = queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.filter." + filterKey);
terms.add(filterQuery);
arguments.put(filterKey, filter.getFilters().get(filterKey));
}
}
terms.add(queryStore.query(QUERYSTORE_FILE, "countDiscrepancyNotes.group"));
String query = StringUtils.join(terms, ' ');
final Integer[][] result = new Integer[ResolutionStatus.list.size() + 1][DiscrepancyNoteType.list.size() + 1];
LOG.debug("SQL: " + query);
getNamedParameterJdbcTemplate().query(query, arguments, new RowMapper<Void>() {
// Using 'void' as return type as the extractor uses the
// pre-populated 'result' object
@Override
public Void mapRow(ResultSet rs, int rowNum) throws SQLException {
result[rs.getInt("resolution_status_id")][rs.getInt("discrepancy_note_type_id")] = rs.getInt("total");
return null;
}
});
return new DiscrepancyNotesSummary(result);
}
protected String listNotesSql(ViewNotesFilterCriteria filter, ViewNotesSortCriteria sort, Map<String, Object> arguments, boolean isSite) {
List<String> terms = new ArrayList<String>();
terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.main"));
if(!isSite)
{
//terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.filter.studyHideCrf"));
}
if (isSite) {
terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.filter.siteHideCrf"));
}
// Append query filters
if (filter != null) {
for (String filterKey : filter.getFilters().keySet()) {
String filterQuery = queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.filter." + filterKey);
terms.add(filterQuery);
arguments.put(filterKey, filter.getFilters().get(filterKey));
}
}
// Append sort criteria
if (sort != null) {
if (!sort.getSorters().isEmpty()) {
terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.orderby"));
}
for (String sortKey : sort.getSorters().keySet()) {
String sortQuery = queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.sort." + sortKey);
terms.add(sortQuery);
terms.add(sort.getSorters().get(sortKey));
}
}
if (filter.getPageNumber() != null && filter.getPageSize() != null) {
if (queryStore.hasQuery(QUERYSTORE_FILE, "findAllDiscrepancyNotes.paginationPrefix")) {
terms.add(0, queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.paginationPrefix"));
terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.paginationSuffix"));
arguments.put("first", 1 + ((filter.getPageNumber() - 1) * filter.getPageSize()));
arguments.put("last", filter.getPageSize() * filter.getPageNumber());
} else {
// Limit number of results (pagination)
terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.limit"));
arguments.put("limit", filter.getPageSize());
arguments.put("offset", (filter.getPageNumber() - 1) * filter.getPageSize());
}
}
String result = StringUtils.join(terms, ' ');
LOG.debug("SQL: " + result);
return result;
}
protected Map<String, Object> listNotesArguments(StudyBean currentStudy) {
Map<String, Object> arguments = new HashMap<String, Object>();
arguments.put("studyId", currentStudy.getId());
arguments.put("limit", 50);
return arguments;
}
public QueryStore getQueryStore() {
return queryStore;
}
public void setQueryStore(QueryStore queryStore) {
this.queryStore = queryStore;
}
}