Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.registryKit.survey; import com.registryKit.client.clientDAO; import com.registryKit.client.dataElements; import com.registryKit.client.engagementDAO; import com.registryKit.client.modifiedEngagementFields; import com.registryKit.client.programClientFields; import java.sql.Timestamp; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Locale; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.SessionFactory; import org.hibernate.criterion.Order; import org.hibernate.criterion.Restrictions; import org.hibernate.transform.Transformers; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; /** * * @author chadmccue */ @Repository public class surveyDAO { @Autowired private SessionFactory sessionFactory; @Autowired private engagementDAO engagementDAO; @Autowired private clientDAO clientDAO; /** * The 'getProgramSurveys' function will return a list of surveys for the passed in programId. * * @param programId The id of the program to return surveys. * @return * @throws Exception */ @SuppressWarnings("unchecked") public List<surveys> getProgramSurveys(Integer programId) throws Exception { Criteria criteria = sessionFactory.getCurrentSession().createCriteria(surveys.class); criteria.add(Restrictions.eq("programId", programId)); criteria.add(Restrictions.eq("status", true)); List<surveys> surveyList = criteria.list(); return surveyList; } /** * The 'getSurveyDetails' function will return the details of the selected survey. * * @param surveyId The id of the selected survey * @return This function will return a single Survey object * @throws Exception */ public surveys getSurveyDetails(Integer surveyId) throws Exception { /* Get a list of client details */ Query q1 = sessionFactory.getCurrentSession().createQuery("from surveys where id = :surveyId"); q1.setParameter("surveyId", surveyId); return (surveys) q1.uniqueResult(); } /** * The 'getSurveyPageDetails' function will return the details of the selected survey page. * * @param pageId The id of the selected survey page * @return This function will return a single Survey object * @throws Exception */ public SurveyPages getSurveyPageDetails(Integer pageId) throws Exception { /* Get a list of client details */ Query q1 = sessionFactory.getCurrentSession().createQuery("from SurveyPages where id = :pageId"); q1.setParameter("pageId", pageId); return (SurveyPages) q1.uniqueResult(); } /** * The 'getSurveyPages' function will return all pages associated with the survey. * * @param surveyId The id of the passed in survey. * @param getQuestions Boolean value to decide to pull questions for each page * @return * @throws Exception */ @SuppressWarnings("unchecked") public List<SurveyPages> getSurveyPages(Integer surveyId, boolean getQuestions, Integer clientId, Integer engagementId, Integer completedSurveyId) throws Exception { List<SurveyPages> surveyPagesList; if (completedSurveyId > 0) { Query query = sessionFactory.getCurrentSession().createSQLQuery( "SELECT * from survey_Pages where surveyId = :surveyId and survey_Pages.id in (select surveyPageId from patient_completedSurveyAnswers where completedSurveyid = :completedSurveyId)") .setParameter("surveyId", surveyId).setParameter("completedSurveyId", completedSurveyId); query.setResultTransformer(Transformers.aliasToBean(SurveyPages.class)); surveyPagesList = query.list(); } else { Criteria criteria = sessionFactory.getCurrentSession().createCriteria(SurveyPages.class); criteria.add(Restrictions.eq("surveyId", surveyId)); criteria.addOrder(Order.asc("pageNum")); surveyPagesList = criteria.list(); } List<SurveyPages> surveyPagesListForReturn = new ArrayList<SurveyPages>(); if (getQuestions) { //we populate the survey questions here for (SurveyPages sp : surveyPagesList) { List<SurveyQuestions> sqs = getSurveyQuestions(sp.getId(), 0, completedSurveyId); for (SurveyQuestions question : sqs) { if (question.getAnswerTypeId() == 1 || question.getAnswerTypeId() == 2) { question.setquestionChoices( getQuestionChoices(question.getId(), question.isAlphabeticallySort())); } if (completedSurveyId > 0) { Query qAns = sessionFactory.getCurrentSession().createQuery( "from patientCompletedSurveyAnswers where completedSurveyId = :completedSurveyId and questionId = :questionId"); qAns.setParameter("completedSurveyId", completedSurveyId); qAns.setParameter("questionId", question.getId()); patientCompletedSurveyAnswers surveyAnswer = (patientCompletedSurveyAnswers) qAns .uniqueResult(); if (surveyAnswer != null) { if (surveyAnswer.getAnswerId() > 0) { question.setQuestionValue(String.valueOf(surveyAnswer.getAnswerId())); } else { question.setQuestionValue(surveyAnswer.getAnswerText()); } } } else { if (question.isAutoPopulateFromField() == true) { dataElements field = clientDAO.getFieldDetails(question.getSaveToFieldId()); question.setQuestionValue(engagementDAO.getTableData(field.getSaveToTableName(), field.getSaveToTableCol(), engagementId, clientId)); } } } sp.setSurveyQuestions(sqs); surveyPagesListForReturn.add(sp); } } else { surveyPagesListForReturn = surveyPagesList; } return surveyPagesListForReturn; } /** * The 'getSurveyPage' function will return a single page from the survey. * * @param surveyId The id of the passed in survey. * @param pageNum The survey page to return. * @param getQuestions Boolean value to decide to pull questions for each page * @return * @throws Exception */ @SuppressWarnings("unchecked") public SurveyPages getSurveyPage(Integer surveyId, boolean getQuestions, Integer pageNum, Integer clientId, Integer engagementId, Integer goToQuestion, Integer completedSurveyId) throws Exception { Criteria criteria = sessionFactory.getCurrentSession().createCriteria(SurveyPages.class); criteria.add(Restrictions.eq("surveyId", surveyId)); criteria.add(Restrictions.eq("pageNum", pageNum)); criteria.addOrder(Order.asc("pageNum")); SurveyPages page = (SurveyPages) criteria.uniqueResult(); if (getQuestions) { /* Always pass 0 for the completedSurveyId parameter, we are editing/taking a survey so we want all questions, not just the ones answered */ List<SurveyQuestions> sqs = getSurveyQuestions(page.getId(), goToQuestion, 0); for (SurveyQuestions question : sqs) { if (question.getAnswerTypeId() == 1 || question.getAnswerTypeId() == 2) { question.setquestionChoices( getQuestionChoices(question.getId(), question.isAlphabeticallySort())); } if (completedSurveyId > 0) { Query qAns = sessionFactory.getCurrentSession().createQuery( "from patientCompletedSurveyAnswers where completedSurveyId = :completedSurveyId and questionId = :questionId"); qAns.setParameter("completedSurveyId", completedSurveyId); qAns.setParameter("questionId", question.getId()); patientCompletedSurveyAnswers surveyAnswer = (patientCompletedSurveyAnswers) qAns .uniqueResult(); if (surveyAnswer != null) { if (surveyAnswer.getAnswerId() > 0) { question.setQuestionValue(String.valueOf(surveyAnswer.getAnswerId())); } else { question.setQuestionValue(surveyAnswer.getAnswerText()); } } } else { if (question.isAutoPopulateFromField() == true) { dataElements field = clientDAO.getFieldDetails(question.getSaveToFieldId()); question.setQuestionValue(engagementDAO.getTableData(field.getSaveToTableName(), field.getSaveToTableCol(), engagementId, clientId)); } } } page.setSurveyQuestions(sqs); } return page; } /** * The 'getSurveyQuestions' function will return the questions associated with the survey * * @param surveyPageId The id for the current page * @return * @throws Exception */ @SuppressWarnings("unchecked") public List<SurveyQuestions> getSurveyQuestions(Integer surveyPageId, Integer goToQuestion, Integer completedSurveyId) throws Exception { List<SurveyQuestions> surveyQuestions = null; if (completedSurveyId > 0) { Query query = sessionFactory.getCurrentSession().createSQLQuery( "SELECT * from survey_questions where surveyPageId = :surveyPageId and survey_questions.id in (select questionId from patient_completedSurveyAnswers where completedSurveyid = :completedSurveyId) order by questionNum") .setParameter("surveyPageId", surveyPageId) .setParameter("completedSurveyId", completedSurveyId); query.setResultTransformer(Transformers.aliasToBean(SurveyQuestions.class)); surveyQuestions = query.list(); } else { Criteria criteria = sessionFactory.getCurrentSession().createCriteria(SurveyQuestions.class); criteria.add(Restrictions.eq("surveyPageId", surveyPageId)); criteria.add(Restrictions.eq("deleted", false)); criteria.add(Restrictions.eq("hide", false)); if (goToQuestion > 0) { Criteria goToQuestioncriteria = sessionFactory.getCurrentSession() .createCriteria(SurveyQuestions.class); goToQuestioncriteria.add(Restrictions.eq("id", goToQuestion)); SurveyQuestions goToQuestionDetails = (SurveyQuestions) goToQuestioncriteria.uniqueResult(); criteria.add(Restrictions.ge("questionNum", goToQuestionDetails.getQuestionNum())); } criteria.addOrder(Order.asc("questionNum")); surveyQuestions = criteria.list(); } for (SurveyQuestions question : surveyQuestions) { if (question.getValidationId() > 0) { question.setValidation(clientDAO.getValidationType(question.getValidationId())); } } return surveyQuestions; } /** * The 'getQuestionChoices' function will return the list of choices set for a question. * * @param questionId The id of the selected question * @return This function will return a list of survey question choices * @throws Exception */ public List<SurveyQuestionChoices> getQuestionChoices(Integer questionId, boolean isAlphabeticallySort) throws Exception { Query query; if (isAlphabeticallySort) { query = sessionFactory.getCurrentSession().createQuery( "from SurveyQuestionChoices where questionId = :questionId order by choiceText asc"); } else { query = sessionFactory.getCurrentSession() .createQuery("from SurveyQuestionChoices where questionId = :questionId order by id asc"); } query.setParameter("questionId", questionId); return query.list(); } /** * The 'getSurveyQuestionChoice' function will return the details for the selected choice. * * @param choiceId The selected choice Id. * @return * @throws Exception */ public SurveyQuestionChoices getSurveyQuestionChoice(Integer choiceId) throws Exception { return (SurveyQuestionChoices) sessionFactory.getCurrentSession().get(SurveyQuestionChoices.class, choiceId); } /** * The 'saveCompletedSurvey' function will save the survey. * * @param survey The object containing all the survey information * @throws Exception */ public Integer saveCompletedSurvey(Integer userId, Integer programId, survey survey) throws Exception { patientCompletedSurveys newSurvey = new patientCompletedSurveys(); newSurvey.setEngagementId(survey.getEngagementId()); newSurvey.setProgramId(programId); newSurvey.setProgramPatientId(survey.getClientId()); newSurvey.setSurveyId(survey.getSurveyId()); newSurvey.setSystemUserId(userId); Integer completedSurveyId = (Integer) sessionFactory.getCurrentSession().save(newSurvey); return completedSurveyId; } /** * The 'saveSurveyAnswers' function will save the answers for the completed survey. * * @param completdSurveyId The id for the submitted survey * @param survey The object containing the survey * @param questionAnswers The list of questionAnswer objects for the submitted survey * @throws Exception */ public void saveSurveyAnswers(Integer completedSurveyId, survey survey, List<surveyQuestionAnswers> questionAnswers) throws Exception { if (questionAnswers != null) { if (survey.getCompletedSurveyId() > 0) { /* Delete existing anwers */ String deleteQuery = "delete from patient_completedSurveyAnswers where completedSurveyId = " + completedSurveyId; Query query = sessionFactory.getCurrentSession().createSQLQuery(deleteQuery); query.executeUpdate(); } for (surveyQuestionAnswers qAnswers : questionAnswers) { /* Save to the patient and engagement tables if this is a new survey and not editing and existing survey. */ patientCompletedSurveyAnswers surveyAnswer = new patientCompletedSurveyAnswers(); surveyAnswer.setCompletedSurveyId(completedSurveyId); surveyAnswer.setAnswerId(qAnswers.getAnswerId()); surveyAnswer.setAnswerText(qAnswers.getAnswerText()); surveyAnswer.setProgramPatientId(survey.getClientId()); surveyAnswer.setQuestionId(qAnswers.getQuestionId()); surveyAnswer.setSurveyPageId(qAnswers.getSurveyPageId()); sessionFactory.getCurrentSession().save(surveyAnswer); if (survey.getCompletedSurveyId() == 0) { if (qAnswers.getSaveToFieldId() > 0) { String sqlQuery; dataElements fieldDetails = clientDAO.getFieldDetails(qAnswers.getSaveToFieldId()); /* Check to see if the table is a patient table */ if (fieldDetails.getSaveToTableName().contains("patient")) { /* Check to see if the field is a date field */ if (fieldDetails.getAnswerType() == 6) { DateFormat format = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH); Date date = format.parse(qAnswers.getAnswerText()); Timestamp timestamp = new Timestamp(date.getTime()); sqlQuery = "update " + fieldDetails.getSaveToTableName() + " set " + fieldDetails.getSaveToTableCol() + " = '" + qAnswers.getAnswerText() + "' where programpatientId = " + survey.getClientId(); } else { if (qAnswers.getAnswerId() > 0) { sqlQuery = "update " + fieldDetails.getSaveToTableName() + " set " + fieldDetails.getSaveToTableCol() + " = '" + qAnswers.getAnswerId() + "' where programpatientId = " + survey.getClientId(); } else { sqlQuery = "update " + fieldDetails.getSaveToTableName() + " set " + fieldDetails.getSaveToTableCol() + " = '" + qAnswers.getAnswerText() + "' where programpatientId = " + survey.getClientId(); } } Query query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery); query.executeUpdate(); } else { /* See if an entry exists in the table */ Query findEngagement = sessionFactory.getCurrentSession() .createSQLQuery("select id from " + fieldDetails.getSaveToTableName() + " where engagementId = " + survey.getEngagementId()); if (findEngagement.list().size() > 0) { if (qAnswers.getAnswerId() > 0 || !"".equals(qAnswers.getAnswerText())) { if (fieldDetails.getAnswerType() == 6) { DateFormat format = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH); Date date = format.parse(qAnswers.getAnswerText()); Timestamp timestamp = new Timestamp(date.getTime()); sqlQuery = "update " + fieldDetails.getSaveToTableName() + " set " + fieldDetails.getSaveToTableCol() + " = '" + timestamp + "' where engagementId = " + survey.getEngagementId(); } else { /* If fieldValue contain multilple items then clear out the values first */ if (qAnswers.getAnswerText().contains(",") && "storage_engagementMedicalServices" .equals(fieldDetails.getSaveToTableName())) { String deleteQuery = "delete from " + fieldDetails.getSaveToTableName() + " where engagementId = " + survey.getEngagementId() + " and engagementFieldId = " + qAnswers.getSaveToFieldId(); Query query = sessionFactory.getCurrentSession() .createSQLQuery(deleteQuery); query.executeUpdate(); List<String> fieldValues = Arrays .asList(qAnswers.getAnswerText().split(",")); sqlQuery = "insert into " + fieldDetails.getSaveToTableName() + " (engagementId, engagementFieldId, codeId, " + fieldDetails.getSaveToTableCol() + ") values "; for (Iterator it = fieldValues.iterator(); it.hasNext();) { String itValue = it.next().toString(); if (itValue.contains("~")) { Integer codeId = 0; String textValue = ""; List<String> values = Arrays.asList(itValue.split("~")); codeId = Integer.parseInt(values.get(0)); textValue = values.get(1); sqlQuery += "(" + survey.getEngagementId() + "," + qAnswers.getSaveToFieldId() + "," + codeId + ", '" + textValue + "')"; } else { sqlQuery += "(" + survey.getEngagementId() + "," + qAnswers.getSaveToFieldId() + ",0, '" + itValue + "')"; } if (it.hasNext()) { sqlQuery += ","; } } } else { if (qAnswers.getAnswerId() > 0) { sqlQuery = "update " + fieldDetails.getSaveToTableName() + " set " + fieldDetails.getSaveToTableCol() + " = '" + qAnswers.getAnswerId() + "' where engagementId = " + survey.getEngagementId(); } else { sqlQuery = "update " + fieldDetails.getSaveToTableName() + " set " + fieldDetails.getSaveToTableCol() + " = '" + qAnswers.getAnswerText() + "' where engagementId = " + survey.getEngagementId(); } } } Query query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery); query.executeUpdate(); } } else { if (fieldDetails.getAnswerType() == 6) { DateFormat format = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH); Date date = format.parse(qAnswers.getAnswerText()); Timestamp timestamp = new Timestamp(date.getTime()); sqlQuery = "insert into " + fieldDetails.getSaveToTableName() + " (engagementId, " + fieldDetails.getSaveToTableCol() + ") values (" + survey.getEngagementId() + ",'" + timestamp + "')"; Query insertQuery = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery); insertQuery.executeUpdate(); } else { if (qAnswers.getAnswerId() > 0) { sqlQuery = "insert into " + fieldDetails.getSaveToTableName() + " (engagementId, " + fieldDetails.getSaveToTableCol() + ") values (" + survey.getEngagementId() + ",'" + qAnswers.getAnswerId() + "')"; } else { sqlQuery = "insert into " + fieldDetails.getSaveToTableName() + " (engagementId, " + fieldDetails.getSaveToTableCol() + ") values (" + survey.getEngagementId() + ",'" + qAnswers.getAnswerText() + "')"; } Query insertQuery = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery); insertQuery.executeUpdate(); } } } } } } } } /** * The 'getPatientSurveysNoEngagement' function will return a list of surveys not associated to any * engagement. * * @param clientId * @return * @throws Exception */ public List<patientCompletedSurveys> getPatientSurveysNoEngagement(Integer clientId, Integer programId) throws Exception { Query query = sessionFactory.getCurrentSession().createSQLQuery( "SELECT a.*, b.title as surveyTitle, CONCAT(c.firstName, ' ', c.lastName) as staffMember FROM patient_completedSurveys a inner join surveys b on b.id = a.surveyId inner join users c on c.id = a.systemUserId where a.programId = :programId and a.programpatientId = :clientId order by a.dateCreated desc") .setParameter("programId", programId).setParameter("clientId", clientId); query.setResultTransformer(Transformers.aliasToBean(patientCompletedSurveys.class)); List<patientCompletedSurveys> surveys = query.list(); return surveys; } /** * * @param clientId * @param engagementId * @return * @throws Exception */ public List<patientCompletedSurveys> getPatientEngagementSurveys(Integer clientId, Integer engagementId) throws Exception { Query query = sessionFactory.getCurrentSession().createSQLQuery( "SELECT a.*, b.title as surveyTitle, CONCAT(c.firstName, ' ', c.lastName) as staffMember FROM patient_completedSurveys a inner join surveys b on b.id = a.surveyId inner join users c on c.id = a.systemUserId where a.engagementId = :engagementId and a.programpatientId = :clientId order by a.dateCreated desc") .setParameter("engagementId", engagementId).setParameter("clientId", clientId); query.setResultTransformer(Transformers.aliasToBean(patientCompletedSurveys.class)); List<patientCompletedSurveys> surveys = query.list(); return surveys; } /** * The 'getCompletedSurvey' function will return the completed survey. * * @param completedSurveyId * @return * @throws Exception */ public patientCompletedSurveys getCompletedSurvey(Integer completedSurveyId) throws Exception { Query q1 = sessionFactory.getCurrentSession() .createQuery("from patientCompletedSurveys where id = :completedSurveyId"); q1.setParameter("completedSurveyId", completedSurveyId); return (patientCompletedSurveys) q1.uniqueResult(); } }