org.generationcp.middleware.dao.ProjectDAO.java Source code

Java tutorial

Introduction

Here is the source code for org.generationcp.middleware.dao.ProjectDAO.java

Source

/*******************************************************************************
 * Copyright (c) 2012, All Rights Reserved.
 *
 * Generation Challenge Programme (GCP)
 *
 *
 * This software is licensed for use under the terms of the GNU General Public License (http://bit.ly/8Ztv8M) and the provisions of Part F
 * of the Generation Challenge Programme Amended Consortium Agreement (http://bit.ly/KQX1nL)
 *
 *******************************************************************************/

package org.generationcp.middleware.dao;

import org.generationcp.middleware.domain.workbench.RoleType;
import org.generationcp.middleware.exceptions.MiddlewareException;
import org.generationcp.middleware.exceptions.MiddlewareQueryException;
import org.generationcp.middleware.pojos.workbench.CropType;
import org.generationcp.middleware.pojos.workbench.Project;
import org.generationcp.middleware.pojos.workbench.WorkbenchUser;
import org.generationcp.middleware.service.api.program.ProgramFilters;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.AliasToEntityMapResultTransformer;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * DAO class for {@link Project}.
 *
 */
public class ProjectDAO extends GenericDAO<Project, Long> {

    public static final String GET_PROJECTS_BY_USER_ID = "SELECT  " + "    p.* " + "   FROM "
            + "       workbench_project p " + "           INNER JOIN "
            + "       crop_persons cp ON cp.crop_name = p.crop_type " + "         INNER JOIN "
            + "       users u ON u.personid = cp.personid " + "         INNER JOIN "
            + "      users_roles ur ON ur.userid = u.userid " + "         INNER JOIN "
            + "      role r on ur.role_id = r.id " + "   WHERE " + "      u.userid = :userId and r.active = 1 "
            + "      AND ( r.role_type_id = " + RoleType.INSTANCE.getId() + "            OR ( r.role_type_id = "
            + RoleType.CROP.getId() + " and ur.crop_name = p.crop_type ) " + "            OR ( r.role_type_id = "
            + RoleType.PROGRAM.getId() + " and ur.crop_name = p.crop_type "
            + "                  and ur.workbench_project_id = p.project_id ) " + "         ) "
            + "      AND ( :cropName IS NULL OR p.crop_type = :cropName ) ";

    public Project getByUuid(final String projectUuid) throws MiddlewareQueryException {

        try {
            if (projectUuid != null) {
                final Criteria criteria = this.getSession().createCriteria(Project.class)
                        .add(Restrictions.eq("uniqueID", projectUuid)).setMaxResults(1);
                return (Project) criteria.uniqueResult();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    "Error with getByUuid(uniqueID=" + projectUuid + ") query from Project: " + e.getMessage(), e);
        }
        return null;
    }

    @Override
    public Project getById(final Long projectId) throws MiddlewareQueryException {
        try {
            if (projectId != null) {
                final Criteria criteria = this.getSession().createCriteria(Project.class)
                        .add(Restrictions.eq("projectId", projectId)).setMaxResults(1);
                return (Project) criteria.uniqueResult();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    "Error with getById(projectId=" + projectId + ") query from Project: " + e.getMessage(), e);
        }
        return null;
    }

    public Project getByUuid(final String projectUuid, final String cropType) throws MiddlewareQueryException {

        try {
            if (projectUuid != null) {
                final Criteria criteria = this.getSession().createCriteria(Project.class)
                        .add(Restrictions.eq("uniqueID", projectUuid))
                        .add(Restrictions.eq("cropType.cropName", cropType)).setMaxResults(1);
                return (Project) criteria.uniqueResult();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    "Error with getByUuid(uniqueID=" + projectUuid + ") query from Project: " + e.getMessage(), e);
        }
        return null;
    }

    public void deleteProject(final String projectName) {
        // Please note we are manually flushing because non hibernate based deletes and updates causes the Hibernate session to get out of
        // synch with
        // underlying database. Thus flushing to force Hibernate to synchronize with the underlying database before the delete
        // statement
        this.getSession().flush();

        final SQLQuery query = this.getSession()
                .createSQLQuery("delete from workbench_project where project_name= '" + projectName + "';");

        query.executeUpdate();
    }

    public Project getProjectByNameAndCrop(final String projectName, final CropType cropType)
            throws MiddlewareQueryException {
        final Criteria criteria = this.getSession().createCriteria(Project.class)
                .add(Restrictions.eq("projectName", projectName)).add(Restrictions.eq("cropType", cropType))
                .setMaxResults(1);
        return (Project) criteria.uniqueResult();
    }

    public Project getLastOpenedProject(final Integer userId) throws MiddlewareQueryException {
        try {
            if (userId != null) {
                final StringBuilder sb = new StringBuilder();
                sb.append("SELECT {w.*} FROM workbench_project w ")
                        .append("INNER JOIN workbench_project_user_info r ON w.project_id = r.project_id ")
                        .append("WHERE r.user_id = :userId AND r.last_open_date IS NOT NULL ORDER BY r.last_open_date DESC LIMIT 1 ;");

                final SQLQuery query = this.getSession().createSQLQuery(sb.toString());
                query.addEntity("w", Project.class);
                query.setParameter("userId", userId);

                @SuppressWarnings("unchecked")
                final List<Project> projectList = query.list();

                return !projectList.isEmpty() ? projectList.get(0) : null;
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    "Error with getLastOpenedProject(userId=" + userId + ") query from Project " + e.getMessage(),
                    e);
        }
        return null;
    }

    public Project getLastOpenedProjectAnyUser() throws MiddlewareQueryException {
        try {

            final StringBuilder sb = new StringBuilder();
            sb.append("SELECT {w.*} FROM workbench_project w ")
                    .append("INNER JOIN workbench_project_user_info r ON w.project_id = r.project_id ")
                    .append("WHERE r.last_open_date IS NOT NULL ORDER BY r.last_open_date DESC LIMIT 1 ;");

            final SQLQuery query = this.getSession().createSQLQuery(sb.toString());
            query.addEntity("w", Project.class);

            @SuppressWarnings("unchecked")
            final List<Project> projectList = query.list();

            return !projectList.isEmpty() ? projectList.get(0) : null;

        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    "Error with getLastOpenedProjectAnyUser(" + ") query from Project " + e.getMessage(), e);
        }
    }

    @SuppressWarnings("unchecked")
    public List<Project> getProjectsByCrop(final CropType cropType) throws MiddlewareQueryException {
        final Criteria criteria = this.getSession().createCriteria(Project.class)
                .add(Restrictions.eq("cropType", cropType));
        return criteria.list();
    }

    public List<Project> getProjectsByFilter(final int pageNumber, final int pageSize,
            final Map<ProgramFilters, Object> filters) throws MiddlewareException {
        try {
            final Criteria criteria = this.getSession().createCriteria(Project.class);
            for (final Map.Entry<ProgramFilters, Object> entry : filters.entrySet()) {
                final ProgramFilters filter = entry.getKey();
                final Object value = entry.getValue();
                criteria.add(Restrictions.eq(filter.getStatement(), value));
            }

            final int start = pageSize * (pageNumber - 1);
            final int numOfRows = pageSize;

            criteria.setFirstResult(start);
            criteria.setMaxResults(numOfRows);
            return criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareException("Error in getProjectsByFilter(start=" + pageNumber + ", numOfRows="
                    + pageSize + "): " + e.getMessage(), e);
        }
    }

    public long countProjectsByFilter(final Map<ProgramFilters, Object> filters) throws MiddlewareException {
        try {
            final Criteria criteria = this.getSession().createCriteria(Project.class);
            for (final Map.Entry<ProgramFilters, Object> entry : filters.entrySet()) {
                final ProgramFilters filter = entry.getKey();
                final Object value = entry.getValue();
                criteria.add(Restrictions.eq(filter.getStatement(), value));
            }

            return criteria.list().size();
        } catch (final HibernateException e) {
            throw new MiddlewareException("Error in countProjectsByFilter(): " + e.getMessage(), e);
        }
    }

    @SuppressWarnings("unchecked")
    public List<Project> getProjectsByUser(final WorkbenchUser user, final String cropName) {
        final List<Project> projects = new ArrayList<>();
        try {
            if (user != null) {
                final SQLQuery query = this.getSession().createSQLQuery(GET_PROJECTS_BY_USER_ID);
                query.setParameter("userId", user.getUserid());
                query.setParameter("cropName", cropName);
                query.addScalar("project_id").addScalar("project_uuid").addScalar("project_name")
                        .addScalar("start_date").addScalar("user_id").addScalar("crop_type")
                        .addScalar("last_open_date");
                query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
                final List<Map<String, Object>> results = query.list();

                for (final Map<String, Object> result : results) {
                    final Long project_id = Long.valueOf((Integer) result.get("project_id"));
                    final String project_uuid = (String) result.get("project_uuid");
                    final String project_name = (String) result.get("project_name");
                    final Date start_date = (Date) result.get("start_date");
                    final Integer user_id = (Integer) result.get("user_id");
                    final CropType crop_type = new CropType((String) result.get("crop_type"));
                    final Date last_open_date = (Date) result.get("last_open_date");
                    final Project u = new Project(project_id, project_uuid, project_name, start_date, user_id,
                            crop_type, last_open_date);
                    projects.add(u);
                }
                return projects;
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException("Error in getProjectsByUser(user=" + user
                    + ") query from ProjectUserInfoDao: " + e.getMessage(), e);
        }
        return new ArrayList<>();
    }

    public List<Project> getProjectsByCropName(final String cropName) {
        final Criteria criteria = this.getSession().createCriteria(Project.class)
                .add(Restrictions.eq("cropType.cropName", cropName));
        return criteria.list();
    }

}