org.cgiar.ccafs.marlo.data.dao.mysql.FundingSourceMySQLDAO.java Source code

Java tutorial

Introduction

Here is the source code for org.cgiar.ccafs.marlo.data.dao.mysql.FundingSourceMySQLDAO.java

Source

/*****************************************************************
 * This file is part of Managing Agricultural Research for Learning &
 * Outcomes Platform (MARLO).
 * MARLO 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.
 * MARLO 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 MARLO. If not, see <http://www.gnu.org/licenses/>.
 *****************************************************************/

package org.cgiar.ccafs.marlo.data.dao.mysql;

import org.cgiar.ccafs.marlo.action.funding.dto.FundingSourceSearchSummary;
import org.cgiar.ccafs.marlo.data.dao.FundingSourceDAO;
import org.cgiar.ccafs.marlo.data.dao.FundingSourceInfoDAO;
import org.cgiar.ccafs.marlo.data.model.FundingSource;
import org.cgiar.ccafs.marlo.data.model.GlobalUnit;
import org.cgiar.ccafs.marlo.data.model.Phase;
import org.cgiar.ccafs.marlo.data.model.PhaseDescription;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import javax.inject.Inject;
import javax.inject.Named;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.transform.AliasToBeanResultTransformer;

@Named
public class FundingSourceMySQLDAO extends AbstractMarloDAO<FundingSource, Long> implements FundingSourceDAO {

    public FundingSourceInfoDAO fundingSourceInfoDAO;

    @Inject
    public FundingSourceMySQLDAO(SessionFactory sessionFactory, FundingSourceInfoDAO fundingSourceInfoDAO) {
        super(sessionFactory);
        this.fundingSourceInfoDAO = fundingSourceInfoDAO;
    }

    @Override
    public void deleteFundingSource(long fundingSourceId) {
        FundingSource fundingSource = this.find(fundingSourceId);
        fundingSource.setActive(false);
        this.save(fundingSource);
    }

    @Override
    public boolean existFundingSource(long fundingSourceID) {
        FundingSource fundingSource = this.find(fundingSourceID);
        if (fundingSource == null) {
            return false;
        }
        return true;

    }

    @Override
    public FundingSource find(long id) {
        return super.find(FundingSource.class, id);

    }

    @Override
    public List<FundingSource> findAll() {
        String query = "from " + FundingSource.class.getName() + " where is_active=1";
        List<FundingSource> list = super.findAll(query);
        if (list.size() > 0) {
            return list;
        }
        return null;

    }

    @Override
    public List<Map<String, Object>> getFundingSource(long userId, String crp) {
        List<Map<String, Object>> list = new ArrayList<>();
        StringBuilder builder = new StringBuilder();
        builder.append("select DISTINCT project_id from user_permission where  crp_acronym='" + crp
                + "' and permission_id = 438 and project_id is not null");
        if (super.getTemTableUserId() == userId) {
            list = super.findCustomQuery(builder.toString());
        } else {
            list = super.excuteStoreProcedure(" call getPermissions(" + userId + ")", builder.toString());
        }
        return list;
    }

    @Override
    public List<FundingSource> getFundingSourceSummaries(GlobalUnit globalUnit, Phase phase,
            Set<Integer> statusTypes) {

        /**
         * Ideally it would be great if we could just fetch the required properties rather than the entity graph,
         * but because we need to show multiple fundingSourceInstitutions we would need to detect and then
         * remove duplicate fundingSources from the resultSet in the cases where there is more than one leadPartner.
         * Therefore I have opted to do a HQL query instead, which will handle this scenario well.
         */
        String queryString = "SELECT DISTINCT f FROM FundingSource f "
                + "inner join fetch f.fundingSourceInfos fsi " + "left join fetch fsi.budgetType "
                + "left join fetch f.fundingSourceInstitutions fsint " + "left join fetch fsint.institution "
                + "left join fetch fsi.directDonor " + "left join fetch fsi.originalDonor "
                + "left join fetch f.sectionStatuses ss " + "left join fetch f.fundingSourceBudgets fsb "
                + "WHERE f.active = TRUE " + "AND f.crp = :globalUnit " + "AND fsi.phase = :phase "
                + "AND ( fsint IS NULL OR fsint.phase = :phase ) " /** I think SectionStatus should be updated to use a phase **/
                + "AND ( ss IS NULL OR (ss.cycle = :phaseDescription AND ss.year = :phaseYear ) ) "
                // + "AND ( fsb IS NULL OR (fsb.year = :phaseYear AND fsb.phase = :phase ) ) "
                // The above line should be what we use, but it appears we have some data corruption, excluding the year works for
                // now, so using the below statement.
                + "AND ( fsb IS NULL OR fsb.phase = :phase ) " + "AND ( fsi.status IS NULL OR ( fsi.status IN ( "
                + statusTypes.stream().map(i -> i.toString()).collect(Collectors.joining(",")) + " ) ) ) "
                + "ORDER BY fsi.endDate NULLS FIRST";

        /**
         * We are using PhaseDescription.PLANNING because if REPORTING is entered the section status results will
         * filter out the results.
         */
        @SuppressWarnings("unchecked")
        List<FundingSource> fundingSources = this.getSessionFactory().getCurrentSession().createQuery(queryString)
                .setParameter("globalUnit", globalUnit).setParameter("phase", phase)
                .setParameter("phaseDescription", PhaseDescription.PLANNING.toString())
                .setParameter("phaseYear", phase.getYear()).list();

        return fundingSources;

    }

    @Override
    public FundingSource save(FundingSource fundingSource) {
        if (fundingSource.getId() == null) {
            super.saveEntity(fundingSource);
        } else {
            fundingSource = super.update(fundingSource);
        }

        return fundingSource;
    }

    @Override
    public FundingSource save(FundingSource fundingSource, String sectionName, List<String> relationsName,
            Phase phase) {
        if (fundingSource.getId() == null) {
            super.saveEntity(fundingSource, sectionName, relationsName, phase);
        } else {
            fundingSource = super.update(fundingSource, sectionName, relationsName, phase);
        }

        return fundingSource;
    }

    @Override
    public List<FundingSourceSearchSummary> searchFundingSources(String query, int year, long crpID, long phaseID) {

        return this.searchFundingSourcesByInstitution(query, null, year, crpID, phaseID);
    }

    @Override
    public List<FundingSource> searchFundingSourcesByFinanceCode(String ocsCode) {
        StringBuilder q = new StringBuilder();
        q.append("from " + FundingSource.class.getName());
        q.append(" where finance_code=" + ocsCode);

        List<FundingSource> fundingSources = super.findAll(q.toString());
        return fundingSources;
    }

    @Override
    public List<FundingSourceSearchSummary> searchFundingSourcesByInstitution(String userInput, Long institutionID,
            int year, long crpID, long phaseID) {
        StringBuilder q = new StringBuilder();

        q.append(
                "SELECT sub.id AS id, sub.name AS name, sub.type AS type, sub.typeId AS typeId, sub.financeCode AS financeCode, sub.w1w2 AS w1w2, sub.budget AS budget, SUM(pb.amount) AS usedAmount ");
        q.append("FROM ");

        q.append(
                "(SELECT DISTINCT fs.id AS `id`, fsi.title AS `name`, bt.name AS `type`, bt.id AS `typeId`, fsi.finance_code AS `financeCode`, fsi.w1w2 AS `w1w2`, fsb.budget AS `budget` ");

        q.append("FROM funding_sources_info fsi ");
        q.append(
                "INNER JOIN funding_sources fs ON fs.id = fsi.funding_source_id AND fs.is_active AND fs.global_unit_id = "
                        + crpID + " ");

        // Only add the INNER JOIN if an institutionId was provided.
        if (institutionID != null) {
            q.append(
                    "INNER JOIN funding_source_institutions fsin ON fs.id = fsin.funding_source_id AND fsin.institution_id = "
                            + institutionID + " AND fsin.id_phase = " + phaseID + " ");
        }
        q.append("LEFT JOIN budget_types bt ON bt.id = fsi.type ");
        q.append("LEFT JOIN funding_source_budgets fsb ON fs.id = fsb.funding_source_id ");
        q.append("WHERE 1=1 ");
        q.append("AND fsi.title IS NOT NULL ");
        q.append("AND (fsi.status IS NULL OR fsi.status IN (1,2,4,7) ) ");
        q.append("AND (fsi.title LIKE '%" + userInput + "%' ");
        q.append("OR fsi.funding_source_id LIKE '%" + userInput + "%' ");
        q.append("OR CONCAT('FS', fsi.funding_source_id) LIKE '%" + userInput + "%' ");
        q.append("OR fsi.finance_code LIKE '%" + userInput + "%' ");
        q.append("OR (SELECT NAME FROM budget_types bt WHERE bt.id = fsi.type) LIKE '%" + userInput + "%' ) ");
        q.append("AND fsi.id_phase = " + phaseID);
        q.append(" AND fsi.end_date IS NOT NULL ");
        q.append("AND ( ( fsb.id IS NULL OR ( fsb.year = " + year + " AND fsb.id_phase = " + phaseID + " ) ) ");
        // q.append("AND ( ( fsb.id IS NULL OR ( fsb.id_phase = " + phaseID + " ) ) ");
        q.append(" AND (" + year + " <= YEAR(fsi.end_date) OR " + year + " <= YEAR(fsi.extended_date) ) ) ");
        q.append(") AS sub ");
        q.append("LEFT JOIN project_budgets pb ON pb.funding_source_id = sub.id AND pb.is_active=1 ");
        q.append("WHERE pb.id IS NULL OR (pb.year= " + year + " AND pb.id_phase=" + phaseID + ") ");
        q.append("GROUP BY sub.id, sub.name, sub.type, sub.typeId, sub.financeCode, sub.w1w2, sub.budget ");
        q.append("ORDER BY sub.id, sub.name");

        Query query = this.getSessionFactory().getCurrentSession().createSQLQuery(q.toString());
        query.setResultTransformer(new AliasToBeanResultTransformer(FundingSourceSearchSummary.class));
        List<FundingSourceSearchSummary> result = query.list();

        return result;

    }

    @Override
    public List<FundingSource> searchFundingSourcesByLocElement(long projectId, long locElementId, int year,
            long crpID, long phaseID) {

        StringBuilder query = new StringBuilder();
        query.append("SELECT DISTINCT  ");
        query.append("funding_sources.id as id ");
        query.append("FROM ");
        query.append("funding_source_locations ");
        query.append(
                "INNER JOIN funding_sources ON funding_source_locations.funding_source_id = funding_sources.id ");
        query.append("INNER JOIN project_budgets ON project_budgets.funding_source_id = funding_sources.id ");
        query.append("WHERE ");
        query.append("funding_source_locations.loc_element_id =" + locElementId
                + "   AND funding_sources.global_unit_id=" + crpID + " AND project_budgets.project_id=" + projectId
                + "  AND  funding_source_locations.is_active=1 and ");
        query.append("project_budgets.is_active = 1 AND ");
        query.append("funding_source_locations.`id_phase` =" + phaseID);

        List<Map<String, Object>> rList = super.findCustomQuery(query.toString());

        List<FundingSource> fundingSources = new ArrayList<>();

        if (rList != null) {
            for (Map<String, Object> map : rList) {
                FundingSource fundingSource = this.find(Long.parseLong(map.get("id").toString()));

                fundingSources.add(fundingSource);
            }
        }

        return fundingSources;

    }

    @Override
    public List<FundingSource> searchFundingSourcesByLocElementType(long projectId, long locElementTypeId, int year,
            long crpID) {

        StringBuilder query = new StringBuilder();
        query.append("SELECT DISTINCT  ");
        query.append("funding_sources.id as id ");
        query.append("FROM ");
        query.append("funding_source_locations ");
        query.append(
                "INNER JOIN funding_sources ON funding_source_locations.funding_source_id = funding_sources.id ");
        query.append("INNER JOIN project_budgets ON project_budgets.funding_source_id = funding_sources.id ");
        query.append("WHERE ");
        query.append("funding_source_locations.loc_element_type_id =" + locElementTypeId
                + " AND funding_sources.global_unit_id=" + crpID + " AND project_budgets.project_id=" + projectId
                + "  AND  funding_source_locations.is_active=1 and ");
        query.append("project_budgets.is_active = 1   ");
        // query.append(" AND project_budgets.`year` =" + year);

        List<Map<String, Object>> rList = super.findCustomQuery(query.toString());

        List<FundingSource> fundingSources = new ArrayList<>();

        if (rList != null) {
            for (Map<String, Object> map : rList) {
                FundingSource fundingSource = this.find(Long.parseLong(map.get("id").toString()));
                fundingSources.add(fundingSource);
            }
        }

        return fundingSources;

    }

}