<commit_before>/*
 * Copyright (c) 2016, Mirante and/or its affiliates. All rights reserved.
 * Mirante PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */

package br.net.mirante.singular.bam.dao;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.Period;
import java.time.ZoneId;
import java.time.temporal.Temporal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.joda.time.LocalDate;
import org.springframework.stereotype.Repository;

import br.net.mirante.singular.bam.dto.InstanceDTO;
import br.net.mirante.singular.bam.dto.StatusDTO;
import br.net.mirante.singular.flow.core.TaskType;
import br.net.mirante.singular.persistence.entity.ProcessInstanceEntity;

@Repository
public class InstanceDAO extends BaseDAO{

    public static final int MAX_FEED_SIZE = 30;

    @SuppressWarnings("unchecked")
    public List<InstanceDTO> retrieveAll(int first, int size, String orderByProperty, boolean asc, Integer processDefinitionCod) {
        Query hql = getSession().createQuery("select ti.cod as cod, (ti.task.name||' - '||pi.description) as descricao, pi.beginDate as dataInicial, ti.beginDate as dataAtividade, au.nome as usuarioAlocado "
            + "from TaskInstanceEntity ti join ti.processInstance pi join pi.processVersion pv join pv.processDefinition pd left join ti.allocatedUser au where ti.endDate is null and pd.cod = :cod");
        hql.setParameter("cod", processDefinitionCod);
        hql.setFirstResult(first);
        hql.setMaxResults(size);
        hql.setResultTransformer(Transformers.aliasToBean(InstanceDTO.class));
        return hql.list();
    }

    public int countAll(Integer processDefinitionCod) {
        Query query = getSession().createQuery("select count(ct) from ProcessDefinitionEntity pd join pd.processInstances pi with pi.endDate is null join pi.currentTasks ct where pd.cod = :cod");
        query.setParameter("cod", processDefinitionCod);
        return ((Number)query.uniqueResult()).intValue();
    }

    public List<Map<String, String>> retrieveTransactionQuantityLastYear(String processCode, Set<String> processCodeWithAccess) {
        List<Map<String, String>> newTransactions = retrieveNewQuantityLastYear(processCode, processCodeWithAccess);
        List<Map<String, String>> finishedTransations = retrieveFinishedQuantityLastYear(processCode, processCodeWithAccess);
        for (Map<String, String> map : finishedTransations) {
            Map<String, String> m = retrieveResultMap(map, newTransactions);
            if (m == null) {
                newTransactions.add(map);
            } else {
                m.put("QTD_CLS", map.get("QTD_CLS"));
            }
        }
        return newTransactions;
    }

    private Map<String, String> retrieveResultMap(Map<String, String> map, List<Map<String, String>> list) {
        for (Map<String, String> m : list) {
            if (m.get("POS").equals(map.get("POS"))) {
                return m;
            }
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    private List<Map<String, String>> retrieveNewQuantityLastYear(String processCode, Set<String> processCodeWithAccess) {
        Query hqlQuery = getSession().createQuery("select trim(str(month(pi.beginDate))) || trim(str(year(pi.beginDate))) as POS, "
            + " trim(str(month(pi.beginDate))) ||'/'|| trim(str(year(pi.beginDate))) as MES, trim(str(count(pi))) as QTD_NEW from ProcessInstanceEntity pi "
            + " join pi.processVersion pv join pv.processDefinition pd "
            + " where pd.key in(:processCodeWithAccess) "
            + " and pi.beginDate >= :datalimite "
            + (processCode != null ? " and pd.key = :processCode" : "")
            + " group by month(pi.beginDate), year(pi.beginDate) order by month(pi.beginDate) asc, year(pi.beginDate) asc");
        if (processCode != null) {
            hqlQuery.setParameter("processCode", processCode);
        }
        hqlQuery.setParameter("datalimite", LocalDate.now().minusYears(1).toDate());
        hqlQuery.setParameterList("processCodeWithAccess", processCodeWithAccess);
        hqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return hqlQuery.list();
    }

    @SuppressWarnings("unchecked")
    private List<Map<String, String>> retrieveFinishedQuantityLastYear(String processCode, Set<String> processCodeWithAccess) {
        Query hqlQuery = getSession().createQuery("select trim(str(month(pi.endDate))) || trim(str(year(pi.endDate))) as POS, "
            + " trim(str(month(pi.endDate))) ||'/'|| trim(str(year(pi.endDate))) as MES, trim(str(count(pi))) as QTD_CLS from ProcessInstanceEntity pi "
            + " join pi.processVersion pv join pv.processDefinition pd "
            + " where pd.key in(:processCodeWithAccess) "
            + " and pi.endDate >= :datalimite "
            + (processCode != null ? " and pd.key = :processCode" : "")
            + " group by month(pi.endDate), year(pi.endDate) order by month(pi.endDate) asc, year(pi.endDate) asc");
        if (processCode != null) {
            hqlQuery.setParameter("processCode", processCode);
        }
        hqlQuery.setParameter("datalimite", LocalDate.now().minusYears(1).toDate());
        hqlQuery.setParameterList("processCodeWithAccess", processCodeWithAccess);
        hqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return hqlQuery.list();
    }

    @SuppressWarnings("unchecked")
    public List<Map<String, String>> retrieveEndStatusQuantityByPeriod(Period period, String processCode) {
        Query hqlQuery = getSession().createQuery("select ti.task.name as SITUACAO, "
            + " trim(str(count(ti))) as QUANTIDADE from TaskInstanceEntity ti "
            + " where ti.task.type = :taskType and ti.beginDate >= :startPeriod "
            + " and ti.task.processVersion.processDefinition.key = :processCode"
            + " group by ti.task.name");
        hqlQuery.setParameter("taskType", TaskType.End);
        hqlQuery.setParameter("processCode", processCode);
        hqlQuery.setParameter("startPeriod", periodFromNow(period));
        hqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        
        return hqlQuery.list();
    }

    @SuppressWarnings("unchecked")
    public List<Map<String, String>> retrieveAllDelayedBySigla(String processCode, BigDecimal media) {
        Query hqlQuery = getSession().createQuery("select pi.description as DESCRICAO, "<commit_msg>[FLOW-BAM] - Correções na pesquisa de movimento da instância.<commit_after>/*
 * Copyright (c) 2016, Mirante and/or its affiliates. All rights reserved.
 * Mirante PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */

package br.net.mirante.singular.bam.dao;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.Period;
import java.time.ZoneId;
import java.time.temporal.Temporal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.joda.time.LocalDate;
import org.springframework.stereotype.Repository;

import br.net.mirante.singular.bam.dto.InstanceDTO;
import br.net.mirante.singular.bam.dto.StatusDTO;
import br.net.mirante.singular.flow.core.TaskType;
import br.net.mirante.singular.persistence.entity.ProcessInstanceEntity;

@Repository
public class InstanceDAO extends BaseDAO{

    public static final int MAX_FEED_SIZE = 30;

    @SuppressWarnings("unchecked")
    public List<InstanceDTO> retrieveAll(int first, int size, String orderByProperty, boolean asc, Integer processDefinitionCod) {
        Query hql = getSession().createQuery("select ti.cod as cod, (ti.task.name||' - '||pi.description) as descricao, pi.beginDate as dataInicial, ti.beginDate as dataAtividade, au.nome as usuarioAlocado "
            + "from TaskInstanceEntity ti join ti.processInstance pi join pi.processVersion pv join pv.processDefinition pd left join ti.allocatedUser au where ti.endDate is null and pd.cod = :cod");
        hql.setParameter("cod", processDefinitionCod);
        hql.setFirstResult(first);
        hql.setMaxResults(size);
        hql.setResultTransformer(Transformers.aliasToBean(InstanceDTO.class));
        return hql.list();
    }

    public int countAll(Integer processDefinitionCod) {
        Query query = getSession().createQuery("select count(ct) from ProcessDefinitionEntity pd join pd.processInstances pi with pi.endDate is null join pi.currentTasks ct where pd.cod = :cod");
        query.setParameter("cod", processDefinitionCod);
        return ((Number)query.uniqueResult()).intValue();
    }

    public List<Map<String, String>> retrieveTransactionQuantityLastYear(String processCode, Set<String> processCodeWithAccess) {
        List<Map<String, String>> newTransactions = retrieveNewQuantityLastYear(processCode, processCodeWithAccess);
        List<Map<String, String>> finishedTransations = retrieveFinishedQuantityLastYear(processCode, processCodeWithAccess);
        for (Map<String, String> map : finishedTransations) {
            Map<String, String> m = retrieveResultMap(map, newTransactions);
            if (m == null) {
                newTransactions.add(map);
            } else {
                m.put("QTD_CLS", map.get("QTD_CLS"));
            }
        }
        return newTransactions;
    }

    private Map<String, String> retrieveResultMap(Map<String, String> map, List<Map<String, String>> list) {
        for (Map<String, String> m : list) {
            if (m.get("POS").equals(map.get("POS"))) {
                return m;
            }
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    private List<Map<String, String>> retrieveNewQuantityLastYear(String processCode, Set<String> processCodeWithAccess) {
        Query hqlQuery = getSession().createQuery("select trim(str(month(pi.beginDate))) || trim(str(year(pi.beginDate))) as POS, "
            + " trim(str(month(pi.beginDate))) ||'/'|| trim(str(year(pi.beginDate))) as MES, trim(str(count(pi))) as QTD_NEW from ProcessInstanceEntity pi "
            + " join pi.processVersion pv join pv.processDefinition pd "
            + " where pd.key in(:processCodeWithAccess) "
            + " and pi.beginDate >= :datalimite "
            + (processCode != null ? " and pd.key = :processCode" : "")
            + " group by trim(str(month(pi.beginDate))) || trim(str(year(pi.beginDate))), "
            + "          trim(str(month(pi.beginDate))) ||'/'|| trim(str(year(pi.beginDate))) "
            + " order by trim(str(month(pi.beginDate))) || trim(str(year(pi.beginDate))) asc, "
            + "          trim(str(month(pi.beginDate))) ||'/'|| trim(str(year(pi.beginDate))) asc ");
        if (processCode != null) {
            hqlQuery.setParameter("processCode", processCode);
        }
        hqlQuery.setParameter("datalimite", LocalDate.now().minusYears(1).toDate());
        hqlQuery.setParameterList("processCodeWithAccess", processCodeWithAccess);
        hqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return hqlQuery.list();
    }

    @SuppressWarnings("unchecked")
    private List<Map<String, String>> retrieveFinishedQuantityLastYear(String processCode, Set<String> processCodeWithAccess) {
        Query hqlQuery = getSession().createQuery("select trim(str(month(pi.endDate))) || trim(str(year(pi.endDate))) as POS, "
            + " trim(str(month(pi.endDate))) ||'/'|| trim(str(year(pi.endDate))) as MES, trim(str(count(pi))) as QTD_CLS from ProcessInstanceEntity pi "
            + " join pi.processVersion pv join pv.processDefinition pd "
            + " where pd.key in(:processCodeWithAccess) "
            + " and pi.endDate >= :datalimite "
            + (processCode != null ? " and pd.key = :processCode" : "")
            + " group by trim(str(month(pi.endDate))) || trim(str(year(pi.endDate))), "
            + "          trim(str(month(pi.endDate))) ||'/'|| trim(str(year(pi.endDate))) "
            + " order by trim(str(month(pi.endDate))) || trim(str(year(pi.endDate))) asc, "
            + "          trim(str(month(pi.endDate))) ||'/'|| trim(str(year(pi.endDate))) asc ");
        if (processCode != null) {
            hqlQuery.setParameter("processCode", processCode);
        }
        hqlQuery.setParameter("datalimite", LocalDate.now().minusYears(1).toDate());
        hqlQuery.setParameterList("processCodeWithAccess", processCodeWithAccess);
        hqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return hqlQuery.list();
    }

    @SuppressWarnings("unchecked")
    public List<Map<String, String>> retrieveEndStatusQuantityByPeriod(Period period, String processCode) {
        Query hqlQuery = getSession().createQuery("select ti.task.name as SITUACAO, "
            + " trim(str(count(ti))) as QUANTIDADE from TaskInstanceEntity ti "
            + " where ti.task.type = :taskType and ti.beginDate >= :startPeriod "
            + " and ti.task.processVersion.processDefinition.key = :processCode"
            + " group by ti.task.name");
        hqlQuery.setParameter("taskType", TaskType.End);
        hqlQuery.setParameter("processCode", processCode);
        hqlQuery.setParameter("startPeriod", periodFromNow(period));
        hqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        
        return hqlQuery.list();
    }

    @SuppressWarnings("unchecked")
    public List<Map<String, String>> retrieveAllDelayedBySigla(String processCode, BigDecimal media) {
        Query hqlQuery = getSession().createQuery("select pi.description as DESCRICAO, "
{'fraction_non_alphanumeric': 0.12503558212354113, 'fraction_numerical': 0.001138627953316254, 'mean_word_length': 3.5390826873385013, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 59, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2016648', 'n_tokens_mistral': 4199, 'n_tokens_neox': 4131, 'n_words': 823}