au.org.theark.report.model.dao.ReportDao.java Source code

Java tutorial

Introduction

Here is the source code for au.org.theark.report.model.dao.ReportDao.java

Source

/*******************************************************************************
 * Copyright (c) 2011  University of Western Australia. All rights reserved.
 * 
 * This file is part of The Ark.
 * 
 * The Ark is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 3
 * of the License, or (at your option) any later version.
 * 
 * The Ark is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 ******************************************************************************/
package au.org.theark.report.model.dao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import au.org.theark.core.model.pheno.entity.PhenoDataSetField;
import au.org.theark.core.model.pheno.entity.PhenoDataSetGroup;
import au.org.theark.report.model.vo.*;
import au.org.theark.report.model.vo.report.*;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.Subqueries;
import org.hibernate.sql.JoinType;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import au.org.theark.core.dao.HibernateSessionDao;
import au.org.theark.core.exception.EntityNotFoundException;
import au.org.theark.core.model.lims.entity.BioTransaction;
import au.org.theark.core.model.pheno.entity.PhenoDataSetCollection;
import au.org.theark.core.model.pheno.entity.PhenoDataSetData;
import au.org.theark.core.model.report.entity.ReportOutputFormat;
import au.org.theark.core.model.report.entity.ReportTemplate;
import au.org.theark.core.model.study.entity.Address;
import au.org.theark.core.model.study.entity.ArkFunction;
import au.org.theark.core.model.study.entity.ArkModule;
import au.org.theark.core.model.study.entity.ArkUser;
import au.org.theark.core.model.study.entity.Consent;
import au.org.theark.core.model.study.entity.CustomField;
import au.org.theark.core.model.study.entity.CustomFieldGroup;
import au.org.theark.core.model.study.entity.LinkSubjectStudy;
import au.org.theark.core.model.study.entity.OtherID;
import au.org.theark.core.model.study.entity.Phone;
import au.org.theark.core.model.study.entity.Study;
import au.org.theark.core.model.study.entity.StudyComp;
import au.org.theark.core.model.worktracking.entity.BillableItem;
import au.org.theark.core.model.worktracking.entity.Researcher;
import au.org.theark.core.service.IArkCommonService;
import au.org.theark.report.service.Constants;

/**
 * Provide the backend Data Access Object for Reporting
 * 
 * @author elam
 * 
 */
@Repository("reportDao")
public class ReportDao extends HibernateSessionDao implements IReportDao {

    private static Logger log = LoggerFactory.getLogger(ReportDao.class);

    private IArkCommonService<Void> iArkCommonService;

    @Autowired
    public void setiArkCommonService(IArkCommonService<Void> iArkCommonService) {
        this.iArkCommonService = iArkCommonService;
    }

    public long getTotalSubjectCount(Study study) {
        Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class);
        criteria.add(Restrictions.eq("study", study));
        criteria.setProjection(Projections.rowCount());
        return (Long) criteria.uniqueResult();
    }

    public Map<String, Long> getSubjectStatusCounts(Study study) {
        Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class);
        criteria.add(Restrictions.eq("study", study));
        ProjectionList projectionList = Projections.projectionList();
        criteria.createAlias("subjectStatus", "subjectStatusAlias");
        projectionList.add(Projections.groupProperty("subjectStatusAlias.name"));
        projectionList.add(Projections.rowCount());
        criteria.setProjection(projectionList);
        List results = criteria.list();
        Map<String, Long> statusMap = new HashMap<String, Long>();
        for (Object r : results) {
            Object[] obj = (Object[]) r;
            String statusName = (String) obj[0];
            statusMap.put(statusName, (Long) obj[1]);
        }
        return statusMap;
    }

    public Map<String, Long> getStudyConsentCounts(Study study) {
        Map<String, Long> statusMap = new HashMap<String, Long>();

        Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class);
        criteria.add(Restrictions.eq("study", study));
        ProjectionList projectionList = Projections.projectionList();
        criteria.createAlias("consentStatus", "consentStatusAlias");
        projectionList.add(Projections.groupProperty("consentStatusAlias.name"));
        projectionList.add(Projections.rowCount());
        criteria.setProjection(projectionList);
        List results = criteria.list();
        for (Object r : results) {
            Object[] obj = (Object[]) r;
            String statusName = (String) obj[0];
            statusMap.put(statusName, (Long) obj[1]);
        }

        // Tack on count of when consentStatus = undefined (NULL)
        criteria = getSession().createCriteria(LinkSubjectStudy.class);
        criteria.add(Restrictions.eq("study", study));
        criteria.add(Restrictions.isNull("consentStatus"));
        projectionList = Projections.projectionList();
        projectionList.add(Projections.rowCount());
        criteria.setProjection(projectionList);
        Long undefCount = (Long) criteria.uniqueResult();
        String statusName = Constants.NOT_CONSENTED;
        statusMap.put(statusName, undefCount);

        return statusMap;
    }

    public Map<String, Long> getStudyCompConsentCounts(Study study, StudyComp studyComp) {
        Map<String, Long> statusMap = new HashMap<String, Long>();

        Criteria criteria = getSession().createCriteria(Consent.class);
        criteria.add(Restrictions.eq("study", study));
        criteria.add(Restrictions.eq("studyComp", studyComp));
        ProjectionList projectionList = Projections.projectionList();
        criteria.createAlias("consentStatus", "consentStatusAlias");
        projectionList.add(Projections.groupProperty("consentStatusAlias.name"));
        projectionList.add(Projections.rowCount());
        criteria.setProjection(projectionList);
        List results = criteria.list();
        if ((results != null) && (results.size() > 0)) {
            for (Object r : results) {
                Object[] obj = (Object[]) r;
                String statusName = (String) obj[0];
                statusMap.put(statusName, (Long) obj[1]);
            }
        } else {
            statusMap.put("(none found)", new Long(0));
        }
        return statusMap;
    }

    public Long getWithoutStudyCompCount(Study study) {

        /*
         * The following HQL implements this MySQL query: SELECT COUNT(*) FROM study.link_subject_study AS lss LEFT JOIN study.consent AS c ON lss.id =
         * c.subject_id -- this line is implicit from annotations on the entity classes WHERE lss.study_id = 2 AND c.id IS NULL;
         */
        String hqlString = "SELECT COUNT(*) FROM LinkSubjectStudy AS lss \n" + "LEFT JOIN lss.consents AS c \n"
                + "WHERE lss.study = :study \n" + "AND c.id IS NULL";
        Query q = getSession().createQuery(hqlString);
        // if (hqlString.contains(":study_id")) {
        // q.setParameter("study_id", study.getId());
        // }
        // if (hqlString.contains(":study")) {
        q.setParameter("study", study);
        // }
        Long undefCount = (Long) q.uniqueResult();

        return undefCount;
    }

    public List<ReportTemplate> getReportsForUser(ArkUser arkUser, Study study) {
        Criteria criteria = getSession().createCriteria(ReportTemplate.class, "rt");
        Collection<ArkModule> modules = iArkCommonService.getArkModulesLinkedWithStudy(study);
        /*
         * TODO : Filter reports based on security criteria For now we will implement security upon the selection of a report
         * 
         * // The following is not yet designed to work with super admins // criteria.add(Restrictions.eq("arkUser", arkUser)); DetachedCriteria
         * functionCriteria = DetachedCriteria.forClass(ArkRolePolicyTemplate.class, "arpt"); // Join FieldPhenoCollection and FieldData on ID FK
         * functionCriteria.add(Property.forName("rt.module").eqProperty("arpt." + "arkModule"));
         * functionCriteria.add(Property.forName("rt.function").eqProperty("arpt." + "arkFunction")); criteria.createAlias("arpt." + "arkFunction",
         * "aFn"); ArkFunction reportArkFnType = getArkFunctionByName(RoleConstants.REPORT_FUNCTION_TYPE);
         * functionCriteria.add(Restrictions.eq("aFn.arkFunctionType", reportArkFnType));
         * criteria.add(Subqueries.exists(functionCriteria.setProjection(Projections.property("arpt.id"))));
         */
        if (modules.size() > 0)
            criteria.add(Restrictions.in("module", modules));

        List<ReportTemplate> reportsAvailListing = criteria.list();

        return reportsAvailListing;
    }

    public List<ReportOutputFormat> getOutputFormats() {
        Criteria criteria = getSession().createCriteria(ReportOutputFormat.class);
        List<ReportOutputFormat> outputFormats = criteria.list();

        return outputFormats;
    }

    public List<LinkSubjectStudy> getStudyLevelConsentDetailsList(ConsentDetailsReportVO cdrVO) {
        Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class);

        // Add study in context to criteria first (linkSubjectStudy on the VO should never be null)
        criteria.add(Restrictions.eq(Constants.LINKSUBJECTSTUDY_STUDY, cdrVO.getLinkSubjectStudy().getStudy()));
        if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) {
            criteria.add(Restrictions.ilike(Constants.LINKSUBJECTSTUDY_SUBJECTUID,
                    cdrVO.getLinkSubjectStudy().getSubjectUID(), MatchMode.ANYWHERE));
        }
        if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) {
            criteria.add(Restrictions.eq(Constants.LINKSUBJECTSTUDY_SUBJECTSTATUS,
                    cdrVO.getLinkSubjectStudy().getSubjectStatus()));
        }

        // we are dealing with study-level consent
        if (cdrVO.getConsentStatus() != null) {
            if (cdrVO.getConsentStatus().getName().equals(Constants.NOT_CONSENTED)) {
                // Special-case: Treat the null FK for consentStatus as "Not Consented"
                criteria.add(Restrictions.or(
                        Restrictions.eq(Constants.LINKSUBJECTSTUDY_CONSENTSTATUS, cdrVO.getConsentStatus()),
                        Restrictions.isNull(Constants.LINKSUBJECTSTUDY_CONSENTSTATUS)));
            } else {
                criteria.add(Restrictions.eq(Constants.LINKSUBJECTSTUDY_CONSENTSTATUS, cdrVO.getConsentStatus()));
            }
        }
        if (cdrVO.getConsentDate() != null) {
            criteria.add(Restrictions.eq(Constants.LINKSUBJECTSTUDY_CONSENTDATE, cdrVO.getConsentDate()));
        }
        criteria.addOrder(Order.asc("consentStatus")); // although MySQL causes NULLs to come first
        criteria.addOrder(Order.asc("subjectUID"));

        return (List<LinkSubjectStudy>) criteria.list();
    }

    public List<ConsentDetailsDataRow> getStudyLevelConsentDetailsDataRowList(ConsentDetailsReportVO cdrVO) {
        List<ConsentDetailsDataRow> resultList = new ArrayList<ConsentDetailsDataRow>(0);

        Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class, "lss");
        // Add study in context to criteria first (linkSubjectStudy on the VO should never be null)
        criteria.add(
                Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_STUDY, cdrVO.getLinkSubjectStudy().getStudy()));
        if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) {
            criteria.add(Restrictions.ilike("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTUID,
                    cdrVO.getLinkSubjectStudy().getSubjectUID(), MatchMode.ANYWHERE));
        }
        if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) {
            criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTSTATUS,
                    cdrVO.getLinkSubjectStudy().getSubjectStatus()));
        }

        // we are dealing with study-level consent
        if (cdrVO.getConsentStatus() != null) {
            if (cdrVO.getConsentStatus().getName().equals(Constants.NOT_CONSENTED)) {
                // Special-case: Treat the null FK for consentStatus as "Not Consented"
                criteria.add(Restrictions.or(
                        Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTSTATUS,
                                cdrVO.getConsentStatus()),
                        Restrictions.isNull(Constants.LINKSUBJECTSTUDY_CONSENTSTATUS)));
            } else {
                criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTSTATUS,
                        cdrVO.getConsentStatus()));
            }
        }
        if (cdrVO.getConsentDate() != null) {
            criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTDATE, cdrVO.getConsentDate()));
        }

        criteria.createAlias("lss.consentStatus", "cs", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("lss.subjectStatus", "ss");
        criteria.createAlias("lss.person", "p");
        criteria.createAlias("lss.person.genderType", "genderType");

        // Restrict any addresses to the preferred mailing address
        //Criteria addressCriteria = 
        criteria.createAlias("lss.person.addresses", "a", JoinType.LEFT_OUTER_JOIN);
        //      addressCriteria.setMaxResults(1);
        //      addressCriteria.add(Restrictions.or(Restrictions.or(Restrictions.eq("a.preferredMailingAddress", true), Restrictions.isNull("a.preferredMailingAddress"),Restrictions.eq("a.preferredMailingAddress", false))));

        criteria.createAlias("lss.person.addresses.country", "c", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("lss.person.addresses.state", "state", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("lss.person.phones", "phone", JoinType.LEFT_OUTER_JOIN);

        //TODO: Get work phone returned as well
        //Criteria phoneCriteria = 
        criteria.createAlias("lss.person.phones.phoneType", "phoneType", JoinType.LEFT_OUTER_JOIN);/*.add(
                                                                                                   Restrictions.or(Restrictions.eq("phoneType.name", "Home"),
                                                                                                   (
                                                                                                   Restrictions.or(Restrictions.or(Restrictions.eq("phoneType.name", "Home"),
                                                                                                   Restrictions.isNull("phoneType.name"),Restrictions.eq("phoneType.name", "Mobile")))
                                                                                                       
                                                                                                   )
                                                                                                   )
                                                                                                   ));*/
        //phoneCriteria.setMaxResults(1);
        criteria.createAlias("lss.person.titleType", "title");

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("lss.subjectUID"), "subjectUID");
        projectionList.add(Projections.property("cs.name"), "consentStatus");
        projectionList.add(Projections.property("ss.name"), "subjectStatus");
        projectionList.add(Projections.property("title.name"), "title");
        projectionList.add(Projections.property("p.firstName"), "firstName");
        projectionList.add(Projections.property("p.lastName"), "lastName");
        projectionList.add(Projections.property("a.streetAddress"), "streetAddress");
        projectionList.add(Projections.property("a.city"), "suburb");
        projectionList.add(Projections.property("a.postCode"), "postcode");
        projectionList.add(Projections.property("state.name"), "state");
        projectionList.add(Projections.property("c.name"), "country");
        projectionList.add(Projections.property("phone.phoneNumber"), "homePhone");
        projectionList.add(Projections.property("p.preferredEmail"), "email");
        projectionList.add(Projections.property("genderType.name"), "sex");
        projectionList.add(Projections.property("lss.consentDate"), "consentDate");

        criteria.setProjection(projectionList); // only return fields required for report

        criteria.addOrder(Order.asc("lss.consentStatus")); // although MySQL causes NULLs to come first
        criteria.addOrder(Order.asc("lss.subjectUID"));

        criteria.setResultTransformer(Transformers.aliasToBean(ConsentDetailsDataRow.class));
        resultList = (criteria.list());

        return resultList;
    }

    public List<ConsentDetailsDataRow> getStudyLevelConsentOtherIDDetailsDataRowList(ConsentDetailsReportVO cdrVO) {
        List<ConsentDetailsDataRow> resultList = new ArrayList<ConsentDetailsDataRow>(0);

        Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class, "lss");
        // Add study in context to criteria first (linkSubjectStudy on the VO should never be null)
        criteria.add(
                Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_STUDY, cdrVO.getLinkSubjectStudy().getStudy()));
        if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) {
            criteria.add(Restrictions.ilike("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTUID,
                    cdrVO.getLinkSubjectStudy().getSubjectUID(), MatchMode.ANYWHERE));
        }
        if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) {
            criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTSTATUS,
                    cdrVO.getLinkSubjectStudy().getSubjectStatus()));
        }

        // we are dealing with study-level consent
        if (cdrVO.getConsentStatus() != null) {
            if (cdrVO.getConsentStatus().getName().equals(Constants.NOT_CONSENTED)) {
                // Special-case: Treat the null FK for consentStatus as "Not Consented"
                criteria.add(Restrictions.or(
                        Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTSTATUS,
                                cdrVO.getConsentStatus()),
                        Restrictions.isNull(Constants.LINKSUBJECTSTUDY_CONSENTSTATUS)));
            } else {
                criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTSTATUS,
                        cdrVO.getConsentStatus()));
            }
        }
        if (cdrVO.getConsentDate() != null) {
            criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_CONSENTDATE, cdrVO.getConsentDate()));
        }

        criteria.createAlias("lss.consentStatus", "cs", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("lss.subjectStatus", "ss");
        criteria.createAlias("lss.person", "p");
        criteria.createAlias("lss.person.genderType", "genderType");

        // Restrict any addresses to the preferred mailing address
        //Criteria addressCriteria = 
        criteria.createAlias("lss.person.addresses", "a", JoinType.LEFT_OUTER_JOIN);
        //      addressCriteria.setMaxResults(1);
        //      addressCriteria.add(Restrictions.or(Restrictions.or(Restrictions.eq("a.preferredMailingAddress", true), Restrictions.isNull("a.preferredMailingAddress"),Restrictions.eq("a.preferredMailingAddress", false))));

        criteria.createAlias("lss.person.addresses.country", "c", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("lss.person.addresses.state", "state", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("lss.person.phones", "phone", JoinType.LEFT_OUTER_JOIN);

        //TODO: Get work phone returned as well
        //Criteria phoneCriteria = 
        criteria.createAlias("lss.person.phones.phoneType", "phoneType", JoinType.LEFT_OUTER_JOIN);/*.add(
                                                                                                   Restrictions.or(Restrictions.eq("phoneType.name", "Home"),
                                                                                                   (
                                                                                                   Restrictions.or(Restrictions.or(Restrictions.eq("phoneType.name", "Home"),
                                                                                                   Restrictions.isNull("phoneType.name"),Restrictions.eq("phoneType.name", "Mobile")))
                                                                                                       
                                                                                                   )
                                                                                                   )
                                                                                                   ));*/
        //phoneCriteria.setMaxResults(1);
        criteria.createAlias("lss.person.titleType", "title");

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("lss.subjectUID"), "subjectUID");
        projectionList.add(Projections.property("cs.name"), "consentStatus");
        projectionList.add(Projections.property("ss.name"), "subjectStatus");
        projectionList.add(Projections.property("title.name"), "title");
        projectionList.add(Projections.property("p.firstName"), "firstName");
        projectionList.add(Projections.property("p.lastName"), "lastName");
        projectionList.add(Projections.property("a.streetAddress"), "streetAddress");
        projectionList.add(Projections.property("a.city"), "suburb");
        projectionList.add(Projections.property("a.postCode"), "postcode");
        projectionList.add(Projections.property("state.name"), "state");
        projectionList.add(Projections.property("c.name"), "country");
        projectionList.add(Projections.property("phone.phoneNumber"), "homePhone");
        projectionList.add(Projections.property("p.preferredEmail"), "email");
        projectionList.add(Projections.property("genderType.name"), "sex");
        projectionList.add(Projections.property("lss.consentDate"), "consentDate");

        criteria.setProjection(projectionList); // only return fields required for report

        criteria.addOrder(Order.asc("lss.consentStatus")); // although MySQL causes NULLs to come first
        criteria.addOrder(Order.asc("lss.subjectUID"));

        criteria.setResultTransformer(Transformers.aliasToBean(ConsentDetailsDataRow.class));
        resultList = (criteria.list());

        //removing duplicate entries from resultList

        List<ConsentDetailsDataRow> uniqueResults = new ArrayList();

        Iterator<ConsentDetailsDataRow> iterator = resultList.iterator();

        while (iterator.hasNext()) {
            ConsentDetailsDataRow o = iterator.next();
            if (!uniqueResults.contains(o))
                uniqueResults.add(o);
        }

        resultList.clear();
        resultList.addAll(uniqueResults);

        for (int j = 0; j < resultList.size(); j++) {
            ConsentDetailsDataRow c = resultList.get(j);
            if (c.getOtherID() == null && c.getOtherIDSource() == null) {
                List<OtherID> otherIDs = null;
                try {
                    otherIDs = iArkCommonService.getOtherIDs(iArkCommonService
                            .getSubjectByUID(c.getSubjectUID(), cdrVO.getLinkSubjectStudy().getStudy())
                            .getPerson());
                    if (otherIDs.size() >= 1) {
                        c.setOtherID(otherIDs.get(0).getOtherID());
                        c.setOtherIDSource(otherIDs.get(0).getOtherID_Source());
                    }
                    if (otherIDs.size() > 1) {
                        for (int i = 1; i < otherIDs.size(); i++) {
                            OtherID o = otherIDs.get(i);
                            ConsentDetailsDataRow clone = new ConsentDetailsDataRow(c.getSubjectUID(),
                                    o.getOtherID_Source(), o.getOtherID(), null, null, null, null, null, null, null,
                                    null, null, null, null, null, null, null, null);
                            resultList.add(clone);
                        }
                    }
                } catch (EntityNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return resultList;
    }

    public List<ConsentDetailsDataRow> getStudyCompConsentList(ConsentDetailsReportVO cdrVO) {
        // NB: There should only ever be one Consent record for a particular Subject for a particular StudyComp

        List<ConsentDetailsDataRow> results = new ArrayList<ConsentDetailsDataRow>();
        Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class, "lss");
        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("lss." + "subjectUID"), "subjectUID");

        criteria.add(
                Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_STUDY, cdrVO.getLinkSubjectStudy().getStudy()));
        if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) {
            criteria.add(Restrictions.ilike("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTUID,
                    cdrVO.getLinkSubjectStudy().getSubjectUID(), MatchMode.ANYWHERE));
        }
        if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) {
            criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTSTATUS,
                    cdrVO.getLinkSubjectStudy().getSubjectStatus()));
        }

        if (cdrVO.getConsentDate() != null) {
            // NB: constraint on consentDate or consentStatus automatically removes "Not Consented" state
            // So LinkSubjectStudy inner join to Consent ok for populated consentDate
            criteria.createAlias("lss." + Constants.LINKSUBJECTSTUDY_CONSENT, "c");
            criteria.createAlias("c." + Constants.CONSENT_CONSENTSTATUS, "cs");
            // constrain on studyComp
            criteria.add(Restrictions.eq("c." + Constants.CONSENT_STUDYCOMP, cdrVO.getStudyComp()));
            // constrain on consentDate
            criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTDATE, cdrVO.getConsentDate()));
            // ConsentStatus is optional for this query...
            if (cdrVO.getConsentStatus() != null) {
                criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTSTATUS, cdrVO.getConsentStatus()));
            }
            projectionList.add(Projections.property("cs.name"), "consentStatus");
            projectionList.add(Projections.property("c." + Constants.CONSENT_CONSENTDATE), "consentDate");

        } else if (cdrVO.getConsentStatus() != null) {
            if (cdrVO.getConsentStatus().getName().equals(Constants.NOT_CONSENTED)) {
                // Need to handle "Not Consented" status differently (since it doesn't have a Consent record)
                // Helpful website: http://www.cereslogic.com/pages/2008/09/22/hibernate-criteria-subqueries-exists/

                // Build subquery to find all Consent records for a Study Comp
                DetachedCriteria consentCriteria = DetachedCriteria.forClass(Consent.class, "c");
                // Constrain on StudyComponent
                consentCriteria.add(Restrictions.eq("c." + Constants.CONSENT_STUDYCOMP, cdrVO.getStudyComp()));
                // Just in case "Not Consented" is erroneously entered into a row in the Consent table
                // consentCriteria.add(Restrictions.ne("c." + Constants.CONSENT_CONSENTSTATUS, cdrVO.getConsentStatus()));
                // Join LinkSubjectStudy and Consent on ID FK
                consentCriteria.add(Property.forName("c.linkSubjectStudy.id").eqProperty("lss." + "id"));
                criteria.add(Subqueries.notExists(consentCriteria.setProjection(Projections.property("c.id"))));

                // If Consent records follows design for "Not Consented", then:
                // - consentStatus and consentDate are not populated
            } else {
                // NB: constraint on consentDate or consentStatus automatically removes "Not Consented" state
                // So LinkSubjectStudy inner join to Consent ok for all recordable consentStatus
                criteria.createAlias("lss." + Constants.LINKSUBJECTSTUDY_CONSENT, "c");
                criteria.createAlias("c." + Constants.CONSENT_CONSENTSTATUS, "cs");
                // Constrain on StudyComponent
                criteria.add(Restrictions.eq("c." + Constants.CONSENT_STUDYCOMP, cdrVO.getStudyComp()));
                // ConsentStatus is NOT optional for this query!
                criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTSTATUS, cdrVO.getConsentStatus()));
                if (cdrVO.getConsentDate() != null) {
                    criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTDATE, cdrVO.getConsentDate()));
                }
                projectionList.add(Projections.property("cs.name"), "consentStatus");
                projectionList.add(Projections.property("c." + Constants.CONSENT_CONSENTDATE), "consentDate");
            }
        } else {
            // Should not attempt to run this query with no consentDate nor consentStatus criteria provided
            log.error(
                    "reportDao.getStudyCompConsentList(..) is missing consentDate or consentStatus parameters in the VO");
            return null;
        }

        criteria.addOrder(Order.asc("lss." + "subjectUID"));
        criteria.setProjection(projectionList);
        criteria.setResultTransformer(Transformers.aliasToBean(ConsentDetailsDataRow.class));
        // This gives a list of subjects matching the specific studyComp and consentStatus
        results = criteria.list();

        return results;
    }

    /**
     * obviously you need it attached and preferably prefetched or you may take an exponential hit to the DB
     * 
     * @param lss
     * @return
     */
    public Address getBestAddressWithOutNewQueries(LinkSubjectStudy lss) {
        Address goodEnoughIfWeCantFindBetter = null;
        for (Address a : lss.getPerson().getAddresses()) {
            if (a != null && a.getPreferredMailingAddress() != null && a.getPreferredMailingAddress()) {
                return a;
            } else if (goodEnoughIfWeCantFindBetter == null) {
                goodEnoughIfWeCantFindBetter = a;
            }
        }
        return goodEnoughIfWeCantFindBetter;
    }

    public Address getBestAddress(LinkSubjectStudy subject) {
        Address result = null;
        // Attempt to get the preferred address first
        Criteria criteria = getSession().createCriteria(Address.class);
        criteria.add(Restrictions.eq("person", subject.getPerson()));
        criteria.add(Restrictions.eq("preferredMailingAddress", true));
        criteria.setMaxResults(1);
        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("streetAddress"), "streetAddress");
        projectionList.add(Projections.property("city"), "city");
        projectionList.add(Projections.property("country"), "country");
        projectionList.add(Projections.property("state"), "state");
        projectionList.add(Projections.property("otherState"), "otherState");
        projectionList.add(Projections.property("postCode"), "postCode");
        criteria.setProjection(projectionList); // only return fields required for report
        criteria.setResultTransformer(Transformers.aliasToBean(Address.class));

        if (criteria.uniqueResult() != null) {
            result = (Address) criteria.uniqueResult();
        } else {
            // Get any address
            criteria = getSession().createCriteria(Address.class);
            criteria.add(Restrictions.eq("person", subject.getPerson()));
            criteria.setMaxResults(1);
            criteria.setProjection(projectionList); // only return fields required for report
            criteria.setResultTransformer(Transformers.aliasToBean(Address.class));

            result = (Address) criteria.uniqueResult();
        }
        return result;
    }

    /**
     * obviously you need it attached an preferably prefetched or you may take an exponential hit to the DB
     * 
     * @param lss
     * @return
     */
    public Phone getWorkPhoneWithoutExponentialQueries(LinkSubjectStudy lss) {
        Phone goodEnoughIfWeCantFindBetter = null;
        for (Phone phone : lss.getPerson().getPhones()) {
            if (phone != null && phone.getPhoneType() != null
                    && phone.getPhoneType().getName().equalsIgnoreCase("Work")) {
                return phone;
            } else if (goodEnoughIfWeCantFindBetter == null) {
                goodEnoughIfWeCantFindBetter = phone;
            }
        }
        return goodEnoughIfWeCantFindBetter;
    }

    public Phone getWorkPhone(LinkSubjectStudy subject) {
        Phone result = null;
        Criteria criteria = getSession().createCriteria(Phone.class);
        criteria.add(Restrictions.eq("person", subject.getPerson()));
        criteria.createAlias("phoneType", "pt");
        criteria.add(Restrictions.eq("pt.name", "Work"));
        criteria.setMaxResults(1);

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("areaCode"), "areaCode");
        projectionList.add(Projections.property("phoneNumber"), "phoneNumber");
        criteria.setProjection(projectionList); // only return fields required for report
        criteria.setResultTransformer(Transformers.aliasToBean(Phone.class));

        if (criteria.uniqueResult() != null) {
            result = (Phone) criteria.uniqueResult();
        }
        return result;
    }

    /**
     * obviously you need it attached an preferably prefetched or you may take an exponential hit to the DB
     * 
     * @param lss
     * @return
     */
    public Phone getHomePhoneWithoutExponentialQueries(LinkSubjectStudy lss) {
        Phone goodEnoughIfWeCantFindBetter = null;
        for (Phone phone : lss.getPerson().getPhones()) {
            if (phone != null && phone.getPhoneType() != null
                    && phone.getPhoneType().getName().equalsIgnoreCase("Home")) {
                return phone;
            } else if (goodEnoughIfWeCantFindBetter == null) {
                goodEnoughIfWeCantFindBetter = phone;
            }
        }
        return goodEnoughIfWeCantFindBetter;
    }

    public Phone getHomePhone(LinkSubjectStudy subject) {
        Phone result = null;
        Criteria criteria = getSession().createCriteria(Phone.class);
        criteria.add(Restrictions.eq("person", subject.getPerson()));
        criteria.createAlias("phoneType", "pt");
        criteria.add(Restrictions.eq("pt.name", "Home"));
        criteria.setMaxResults(1);

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("areaCode"), "areaCode");
        projectionList.add(Projections.property("phoneNumber"), "phoneNumber");
        criteria.setProjection(projectionList); // only return fields required for report
        criteria.setResultTransformer(Transformers.aliasToBean(Phone.class));

        if (criteria.uniqueResult() != null) {
            result = (Phone) criteria.uniqueResult();
        }
        return result;
    }

    public List<LinkSubjectStudy> getSubjectsMatchingComponentConsent(ConsentDetailsReportVO cdrVO) {

        String qs = " select lss from LinkSubjectStudy lss " + "  left join fetch lss.person p "
                + "  left join fetch p.addresses a " + "  left join fetch p.phones ps "
                + "  left join fetch lss.consents c " + " where " + " lss.study =:study ";

        if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) {
            qs = qs + " and lss.id =:id ";

        }
        if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) {
            qs = qs + " and lss.subjectStatus=:subjectStatus ";
        }

        Query query = getSession().createQuery((qs + "order by lss.id "));
        query.setParameter("study", cdrVO.getLinkSubjectStudy().getStudy());

        if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) {
            query.setParameter("id", cdrVO.getLinkSubjectStudy().getId());
        }
        if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) {
            query.setParameter("subjectStatus", cdrVO.getLinkSubjectStudy().getSubjectStatus());
        }
        /*      next stage...just prefetch for now
                if (cdrVO.getConsentStatus() != null){
                 q.setParameter("studyId", cdrVO.getLinkSubjectStudy().getStudy());   
              }
              if (cdrVO.getStudyComp() != null){
                 q.setParameter("studyId", cdrVO.getLinkSubjectStudy().getStudy());
              }*/

        List<LinkSubjectStudy> results = query.list();
        return results;
    }

    public List<LinkSubjectStudy> getSubjects(ConsentDetailsReportVO cdrVO) {
        Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class);
        criteria.add(Restrictions.eq(Constants.LINKSUBJECTSTUDY_STUDY, cdrVO.getLinkSubjectStudy().getStudy()));
        if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) {
            criteria.add(Restrictions.ilike(Constants.LINKSUBJECTSTUDY_SUBJECTUID,
                    cdrVO.getLinkSubjectStudy().getSubjectUID(), MatchMode.ANYWHERE));
        }
        if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) {
            criteria.add(Restrictions.eq(Constants.LINKSUBJECTSTUDY_SUBJECTSTATUS,
                    cdrVO.getLinkSubjectStudy().getSubjectStatus()));
        }
        criteria.addOrder(Order.asc("subjectUID"));
        List<LinkSubjectStudy> results = criteria.list();
        return results;
    }

    public Consent getStudyCompConsent(Consent consent) {
        // Note: Should never be possible to have more than one Consent record for a
        // given a particular subject and study component
        Criteria criteria = getSession().createCriteria(Consent.class);
        if (consent != null) {
            criteria.add(Restrictions.eq("study.id", consent.getStudy().getId()));
            // must only get consents for subject in context
            criteria.add(Restrictions.eq("linkSubjectStudy.id", consent.getLinkSubjectStudy().getId()));
            // must only get consents for specific studyComp
            criteria.add(Restrictions.eq("studyComp.id", consent.getStudyComp().getId()));
            // Do NOT constrain against consentStatus or consentDate here, because we want to be able to
            // tell if they are "Not Consented" vs "Consented" with different consentStatus or consentDate.
            // if (consent.getConsentStatus() != null)
            // {
            // criteria.add(Restrictions.eq("consentStatus.id", consent.getConsentStatus().getId()));
            // }
            //
            // if (consent.getConsentDate() != null)
            // {
            // criteria.add(Restrictions.eq("consentDate", consent.getConsentDate()));
            // }

        }
        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("studyComp"), "studyComp");
        projectionList.add(Projections.property("consentStatus"), "consentStatus");
        projectionList.add(Projections.property("consentDate"), "consentDate");
        criteria.setProjection(projectionList);
        criteria.setMaxResults(1);
        criteria.setResultTransformer(Transformers.aliasToBean(Consent.class));
        Consent result = (Consent) criteria.uniqueResult();
        return result;
    }

    public List<PhenoDataSetCollection> getPhenoCollectionList(Study study) {
        List<PhenoDataSetCollection> results = null;
        Criteria criteria = getSession().createCriteria(PhenoDataSetCollection.class);
        criteria.add(Restrictions.eq("study", study));
        results = criteria.list();
        return results;
    }

    //TODO review
    public List<FieldDetailsDataRow> getPhenoFieldDetailsList(FieldDetailsReportVO fdrVO) {
        List<FieldDetailsDataRow> results = new ArrayList<FieldDetailsDataRow>();
        Criteria criteria = getSession().createCriteria(PhenoDataSetCollection.class, "fpc");
        criteria.createAlias("phenoCollection", "pc"); // Inner join to Field
        criteria.createAlias("field", "f"); // Inner join to Field
        criteria.createAlias("f.fieldType", "ft"); // Inner join to FieldType
        criteria.add(Restrictions.eq("study", fdrVO.getStudy()));
        if (fdrVO.getPhenoCollection() != null) {
            criteria.add(Restrictions.eq("phenoCollection", fdrVO.getPhenoCollection()));
        }
        if (fdrVO.getFieldDataAvailable()) {
            DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "fd");
            // Join FieldPhenoCollection and FieldData on ID FK
            fieldDataCriteria.add(Property.forName("f.id").eqProperty("fd." + "field.id"));
            fieldDataCriteria.add(Property.forName("pc.id").eqProperty("fd." + "collection.id"));
            criteria.add(Subqueries.exists(fieldDataCriteria.setProjection(Projections.property("fd.id"))));
        }
        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("pc.name"), "collection");
        projectionList.add(Projections.property("f.name"), "fieldName");
        projectionList.add(Projections.property("f.description"), "description");
        projectionList.add(Projections.property("f.minValue"), "minValue");
        projectionList.add(Projections.property("f.maxValue"), "maxValue");
        projectionList.add(Projections.property("f.encodedValues"), "encodedValues");
        projectionList.add(Projections.property("f.missingValue"), "missingValue");
        projectionList.add(Projections.property("f.units"), "units");
        projectionList.add(Projections.property("ft.name"), "type");
        criteria.setProjection(projectionList); // only return fields required for report
        criteria.setResultTransformer(Transformers.aliasToBean(FieldDetailsDataRow.class));
        criteria.addOrder(Order.asc("pc.id"));
        criteria.addOrder(Order.asc("f.name"));
        results = criteria.list();

        return results;
    }

    public List<CustomFieldDetailsDataRow> getPhenoCustomFieldDetailsList(CustomFieldDetailsReportVO fdrVO) {
        List<CustomFieldDetailsDataRow> results = new ArrayList<CustomFieldDetailsDataRow>();
        if (fdrVO.getCustomFieldDisplay() != null) {
            /*
             * Following query returns customFields whether or not they are 
             * associated with a customFieldGroups (via customFieldDisplay)
             */
            Criteria criteria = getSession().createCriteria(CustomField.class, "cf");
            criteria.createAlias("customFieldDisplay", "cfd", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldDisplay
            criteria.createAlias("cfd.customFieldGroup", "cfg", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldGroup
            criteria.createAlias("fieldType", "ft", JoinType.LEFT_OUTER_JOIN); // Left join to FieldType
            criteria.createAlias("unitType", "ut", JoinType.LEFT_OUTER_JOIN); // Left join to UnitType
            criteria.add(Restrictions.eq("cf.study", fdrVO.getStudy()));
            ArkFunction function = iArkCommonService
                    .getArkFunctionByName(au.org.theark.core.Constants.FUNCTION_KEY_VALUE_PHENO_COLLECTION);
            criteria.add(Restrictions.eq("cf.arkFunction", function));

            if (fdrVO.getCustomFieldDisplay().getCustomFieldGroup() != null) {
                criteria.add(
                        Restrictions.eq("cfg.id", fdrVO.getCustomFieldDisplay().getCustomFieldGroup().getId()));
            }
            if (fdrVO.getFieldDataAvailable()) {
                DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "pd");
                // Join CustomFieldDisplay and PhenoData on ID FK
                fieldDataCriteria.add(Property.forName("cfd.id").eqProperty("pd." + "customFieldDisplay.id"));
                criteria.add(Subqueries
                        .exists(fieldDataCriteria.setProjection(Projections.property("pd.customFieldDisplay"))));
            }

            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.property("cfg.name"), "questionnaire");
            projectionList.add(Projections.property("cf.name"), "fieldName");
            projectionList.add(Projections.property("cf.description"), "description");
            projectionList.add(Projections.property("cf.minValue"), "minValue");
            projectionList.add(Projections.property("cf.maxValue"), "maxValue");
            projectionList.add(Projections.property("cf.encodedValues"), "encodedValues");
            projectionList.add(Projections.property("cf.missingValue"), "missingValue");
            projectionList.add(Projections.property("ut.name"), "units");
            projectionList.add(Projections.property("ft.name"), "type");

            criteria.setProjection(projectionList); // only return fields required for report
            criteria.setResultTransformer(Transformers.aliasToBean(CustomFieldDetailsDataRow.class));
            criteria.addOrder(Order.asc("cfg.id"));
            criteria.addOrder(Order.asc("cfd.sequence"));
            results = criteria.list();
        }

        return results;
    }

    public List<PhenoDataSetFieldDetailsDataRow> getPhenoDataSetFieldDetailsList(
            PhenoDataSetFieldDetailsReportVO reportVO) {
        List<PhenoDataSetFieldDetailsDataRow> results = new ArrayList<PhenoDataSetFieldDetailsDataRow>();
        if (reportVO.getPhenoDataSetFieldDisplay() != null) {
            /*
             * Following query returns customFields whether or not they are
             * associated with a customFieldGroups (via customFieldDisplay)
             */
            Criteria criteria = getSession().createCriteria(PhenoDataSetField.class, "pf");
            criteria.createAlias("phenoDatasetFieldDisplay", "pdfd", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldDisplay
            criteria.createAlias("pdfd.phenoDatasetFieldGroup", "pdfg", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldGroup
            criteria.createAlias("fieldType", "ft", JoinType.LEFT_OUTER_JOIN); // Left join to FieldType
            criteria.createAlias("unitType", "ut", JoinType.LEFT_OUTER_JOIN); // Left join to UnitType
            criteria.add(Restrictions.eq("pf.study", reportVO.getStudy()));
            ArkFunction function = iArkCommonService
                    .getArkFunctionByName(au.org.theark.core.Constants.FUNCTION_KEY_VALUE_PHENO_COLLECTION);
            criteria.add(Restrictions.eq("pf.arkFunction", function));

            if (reportVO.getPhenoDataSetFieldDisplay().getPhenoDataSetGroup() != null) {
                criteria.add(Restrictions.eq("pdfg.id",
                        reportVO.getPhenoDataSetFieldDisplay().getPhenoDataSetGroup().getId()));
            }
            if (reportVO.getFieldDataAvailable()) {
                DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "pd");
                // Join CustomFieldDisplay and PhenoData on ID FK
                fieldDataCriteria
                        .add(Property.forName("pdfd.id").eqProperty("pd." + "phenoDatasetFieldDisplay.id"));
                criteria.add(Subqueries.exists(
                        fieldDataCriteria.setProjection(Projections.property("pd.phenoDatasetFieldDisplay"))));
            }

            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.property("pdfg.name"), "questionnaire");
            projectionList.add(Projections.property("pf.name"), "fieldName");
            projectionList.add(Projections.property("pf.description"), "description");
            projectionList.add(Projections.property("pf.minValue"), "minValue");
            projectionList.add(Projections.property("pf.maxValue"), "maxValue");
            projectionList.add(Projections.property("pf.encodedValues"), "encodedValues");
            projectionList.add(Projections.property("pf.missingValue"), "missingValue");
            projectionList.add(Projections.property("ut.name"), "units");
            projectionList.add(Projections.property("ft.name"), "type");

            criteria.setProjection(projectionList); // only return fields required for report
            criteria.setResultTransformer(Transformers.aliasToBean(PhenoDataSetFieldDetailsDataRow.class));
            criteria.addOrder(Order.asc("pdfg.id"));
            criteria.addOrder(Order.asc("pdfd.sequence"));
            results = criteria.list();
        }

        return results;
    }

    protected ArkFunction getArkFunctionByName(String functionName) {
        Criteria criteria = getSession().createCriteria(ArkFunction.class);
        criteria.add(Restrictions.eq("name", functionName));
        criteria.setMaxResults(1);
        ArkFunction arkFunction = (ArkFunction) criteria.uniqueResult();
        return arkFunction;
    }

    @SuppressWarnings("unchecked")
    public List<StudyUserRolePermissionsDataRow> getStudyUserRolePermissions(Study study) {
        String sqlString = "SELECT * FROM `study`.`study_user_role_permission_view` WHERE studyName = :studyName";
        Query q = getSession().createSQLQuery(sqlString);
        q.setParameter("studyName", study.getName());
        q.setResultTransformer(Transformers.aliasToBean(StudyUserRolePermissionsDataRow.class));
        return q.list();
    }

    public List<PhenoDataSetGroup> getQuestionnaireList(Study study) {
        List<PhenoDataSetGroup> results = null;
        Criteria criteria = getSession().createCriteria(PhenoDataSetGroup.class);
        criteria.add(Restrictions.eq("study", study));
        ArkFunction function = iArkCommonService
                .getArkFunctionByName(au.org.theark.core.Constants.FUNCTION_KEY_VALUE_PHENO_COLLECTION);
        criteria.add(Restrictions.eq("arkFunction", function));
        results = criteria.list();
        return results;
    }

    public List<ResearcherCostDataRow> getResearcherBillableItemTypeCostData(
            final ResearcherCostResportVO researcherCostResportVO) {
        List<ResearcherCostDataRow> results = new ArrayList<ResearcherCostDataRow>();
        Criteria criteria = getSession().createCriteria(BillableItem.class, "bi");
        criteria.createAlias("workRequest", "wr", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("billableItemType", "bit", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("bit.billableItemTypeStatus", "bitst", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("wr.researcher", "re", JoinType.LEFT_OUTER_JOIN);

        criteria.add(Restrictions.eq("re.id", researcherCostResportVO.getResearcherId()));
        criteria.add(Restrictions.eq("bi.studyId", researcherCostResportVO.getStudyId()));
        criteria.add(Restrictions.eq("bi.invoice", researcherCostResportVO.getInvoice()));
        criteria.add(Restrictions.le("bi.commenceDate", researcherCostResportVO.getToDate()));
        criteria.add(Restrictions.ge("bi.commenceDate", researcherCostResportVO.getFromDate()));
        criteria.add(Restrictions.eq("bitst.name", "ACTIVE"));

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.groupProperty("bit.id"));
        projectionList.add(Projections.property("bit.itemName"), "costType");
        projectionList.add(Projections.sum("bi.totalCost"), "totalCost");
        projectionList.add(Projections.sum("bi.totalGST"), "totalGST");

        criteria.setProjection(projectionList); // only return fields required for report
        criteria.setResultTransformer(Transformers.aliasToBean(ResearcherCostDataRow.class));

        criteria.addOrder(Order.asc("bit.itemName"));
        results = criteria.list();
        return results;
    }

    public List<Researcher> searchResearcherByStudyId(final Long studyId) {
        Criteria criteria = getSession().createCriteria(Researcher.class);
        criteria.add(Restrictions.eq("studyId", studyId));
        List<Researcher> list = criteria.list();
        return list;
    }

    public List<ResearcherDetailCostDataRow> getBillableItemDetailCostData(
            ResearcherCostResportVO researcherCostResportVO) {
        List<ResearcherDetailCostDataRow> results = new ArrayList<ResearcherDetailCostDataRow>();
        Criteria criteria = getSession().createCriteria(BillableItem.class, "bi");
        criteria.createAlias("workRequest", "wr", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("billableItemType", "bit", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("wr.researcher", "re", JoinType.LEFT_OUTER_JOIN);

        if (researcherCostResportVO.getResearcherId() != null) {
            criteria.add(Restrictions.eq("re.id", researcherCostResportVO.getResearcherId()));
        }

        criteria.add(Restrictions.eq("bi.studyId", researcherCostResportVO.getStudyId()));
        criteria.add(Restrictions.eq("bi.invoice", researcherCostResportVO.getInvoice()));
        criteria.add(Restrictions.le("bi.commenceDate", researcherCostResportVO.getToDate()));
        criteria.add(Restrictions.ge("bi.commenceDate", researcherCostResportVO.getFromDate()));

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("bi.description"), "description");
        projectionList.add(Projections.property("bi.commenceDate"), "commencedDate");
        projectionList.add(Projections.property("bi.invoice"), "invoice");
        projectionList.add(Projections.property("bi.quantity"), "quantity");
        projectionList.add(Projections.property("bi.totalCost"), "totalAmount");
        projectionList.add(Projections.property("bi.totalGST"), "totalGST");
        projectionList.add(Projections.property("bit.itemName"), "itemType");
        projectionList.add(Projections.property("bit.id"), "typeId");
        projectionList.add(Projections.property("bit.quantityType"), "quantityType");
        projectionList.add(Projections.property("wr.gstAllow"), "gstAllowed");

        projectionList.add(Projections.property("wr.name"), "requestName");

        criteria.setProjection(projectionList); // only return fields required for report
        criteria.setResultTransformer(Transformers.aliasToBean(ResearcherDetailCostDataRow.class));
        criteria.addOrder(Order.asc("bit.id"));
        criteria.addOrder(Order.asc("bi.commenceDate"));
        results = criteria.list();
        return results;
    }
    /*
    private Date toStartOfYear(int year) {
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.YEAR, year);
        calendar.set(Calendar.WEEK_OF_YEAR, 1);
        calendar.set(Calendar.DAY_OF_WEEK,1);
        return calendar.getTime();
    }
        
    private Date toEndOfYear(int year) {
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.YEAR, year);
        calendar.set(Calendar.MONTH,11);
        calendar.set(Calendar.DAY_OF_MONTH,31);
        return calendar.getTime();
    }
    */

    public List<BiospecimenSummaryDataRow> getBiospecimenSummaryData(
            BiospecimenSummaryReportVO biospecimenSummaryReportVO) {
        List<BiospecimenSummaryDataRow> results = new ArrayList<BiospecimenSummaryDataRow>();

        Criteria criteria = getSession().createCriteria(BioTransaction.class, "bt");

        criteria.createAlias("status", "bts", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("biospecimen", "bs", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("bs.study", "st", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("bs.linkSubjectStudy", "lss", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("bs.sampleType", "sat", JoinType.LEFT_OUTER_JOIN);

        criteria.add(Restrictions.eq("st.id", biospecimenSummaryReportVO.getStudy().getId()));
        if (biospecimenSummaryReportVO.getSubjectUID() != null) {
            criteria.add(Restrictions.eq("lss.subjectUID", biospecimenSummaryReportVO.getSubjectUID()));
        }

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.min("bt.id"));
        projectionList.add(Projections.groupProperty("bs.id"));

        projectionList.add(Projections.property("st.name"), "studyName");
        projectionList.add(Projections.property("lss.subjectUID"), "subjectUId");
        projectionList.add(Projections.property("bs.id"), "biospecimenId");
        projectionList.add(Projections.property("bs.biospecimenUid"), "biospecimenUid");
        projectionList.add(Projections.property("bs.parentUid"), "parentId");
        projectionList.add(Projections.property("sat.name"), "sampleType");
        projectionList.add(Projections.property("bs.quantity"), "quantity");
        projectionList.add(Projections.property("bts.name"), "initialStatus");

        criteria.setProjection(projectionList);
        criteria.setResultTransformer(Transformers.aliasToBean(BiospecimenSummaryDataRow.class));
        criteria.addOrder(Order.asc("lss.subjectUID"));
        criteria.addOrder(Order.asc("bs.biospecimenUid"));
        results = criteria.list();

        return results;
    }

    public List<BiospecimenDetailsDataRow> getBiospecimenDetailsData(
            BiospecimenDetailsReportVO biospecimenDetailReportVO) {
        List<BiospecimenDetailsDataRow> results = new ArrayList<BiospecimenDetailsDataRow>();

        Criteria criteria = getSession().createCriteria(BioTransaction.class, "bt");

        criteria.createAlias("status", "bts", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("biospecimen", "bs", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("bs.study", "st", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("bs.bioCollection", "bc", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("bs.linkSubjectStudy", "lss", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("bs.sampleType", "sat", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("bs.invCell", "inc", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("inc.invBox", "inb", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("inb.invRack", "inr", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("inr.invFreezer", "inf", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("inf.invSite", "ins", JoinType.LEFT_OUTER_JOIN);

        criteria.add(Restrictions.eq("st.id", biospecimenDetailReportVO.getStudy().getId()));
        if (biospecimenDetailReportVO.getSubjectUID() != null) {
            criteria.add(Restrictions.eq("lss.subjectUID", biospecimenDetailReportVO.getSubjectUID()));
        }

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.min("bt.id"));
        projectionList.add(Projections.groupProperty("bs.id"));

        projectionList.add(Projections.property("st.name"), "studyName");
        projectionList.add(Projections.property("lss.subjectUID"), "subjectUId");
        projectionList.add(Projections.property("bs.id"), "biospecimenId");
        projectionList.add(Projections.property("bc.biocollectionUid"), "biocollectionUid");
        projectionList.add(Projections.property("bs.sampleDate"), "sampleDate");
        projectionList.add(Projections.property("bs.biospecimenUid"), "biospecimenUid");
        projectionList.add(Projections.property("bs.parentUid"), "parentId");
        projectionList.add(Projections.property("sat.name"), "sampleType");
        projectionList.add(Projections.property("bs.quantity"), "quantity");
        projectionList.add(Projections.property("bts.name"), "initialStatus");

        projectionList.add(Projections.property("inb.name"), "box");
        projectionList.add(Projections.property("inr.name"), "rack");
        projectionList.add(Projections.property("inf.name"), "freezer");
        projectionList.add(Projections.property("ins.name"), "site");

        criteria.setProjection(projectionList);
        criteria.setResultTransformer(Transformers.aliasToBean(BiospecimenDetailsDataRow.class));
        criteria.addOrder(Order.asc("lss.subjectUID"));
        criteria.addOrder(Order.asc("bs.biospecimenUid"));
        results = criteria.list();

        return results;
    }

}