<commit_before>/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.harvard.iq.dataverse;

import edu.harvard.iq.dataverse.authorization.providers.builtin.BuiltinUser;
import edu.harvard.iq.dataverse.authorization.users.AuthenticatedUser;
import edu.harvard.iq.dataverse.authorization.users.User;
import static edu.harvard.iq.dataverse.util.JsfHelper.JH;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.faces.application.FacesMessage;
import javax.faces.component.UIComponent;
import javax.faces.component.UIInput;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.inject.Named;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

/**
 *
 * @author skraffmiller
 */
@Stateless
@Named
public class GuestbookResponseServiceBean {

    @PersistenceContext(unitName = "VDCNet-ejbPU")
    private EntityManager em;

    public List<GuestbookResponse> findAll() {
        return em.createQuery("select object(o) from GuestbookResponse as o order by o.responseTime desc", GuestbookResponse.class).getResultList();
    }

    public List<Long> findAllIds() {
        return findAllIds(null);
    }

    public List<Long> findAllIds(Long dataverseId) {
        if (dataverseId == null) {
            return em.createQuery("select o.id from GuestbookResponse as o order by o.responseTime desc", Long.class).getResultList();
        }
        return em.createQuery("select o.id from GuestbookResponse  o, Dataset d where o.dataset.id = d.id and d.owner.id = " + dataverseId + " order by o.responseTime desc", Long.class).getResultList();
    }

    public List<GuestbookResponse> findAllByGuestbookId(Long guestbookId) {

        if (guestbookId == null) {
        } else {
            return em.createQuery("select o from GuestbookResponse as o where o.guestbook.id = " + guestbookId + " order by o.responseTime desc", GuestbookResponse.class).getResultList();
        }
        return null;
    }
    
    public List<Object[]> findArrayByDataverseId (Long dataverseId){

        String queryString = "select r.id, g.name, v.value,  r.responsetime, r.downloadtype,  m.label, r.dataFile_id, r.name, r.email, r.institution, r.position from guestbookresponse r,"
                + " datasetfieldvalue v, filemetadata m, dvobject o, guestbook g  "
                + " where "  
                + " v.datasetfield_id = (select id from datasetfield f where datasetfieldtype_id = 1 "
                + " and datasetversion_id = (select max(id) from datasetversion where dataset_id =r.dataset_id )) "
                + " and m.datasetversion_id = (select max(datasetversion_id) from filemetadata where datafile_id =r.datafile_id ) "
                + "  and m.datafile_id = r.datafile_id "
                + "  and r.dataset_id = o.id "
                + "  and r.guestbook_id = g.id "
                + " and  o.owner_id = "
                + dataverseId.toString()
                + ";";           
        
        return findArray(queryString);       
    }
    
    public List<Object[]> findArrayByDataverseIdAndGuestbookId (Long dataverseId, Long guestbookId){

        String queryString = "select r.id, g.name, v.value,  r.responsetime, r.downloadtype,  m.label, r.dataFile_id, r.name, r.email, r.institution, r.position from guestbookresponse r,"
                + " datasetfieldvalue v, filemetadata m, dvobject o, guestbook g  "
                + " where "  
                + " v.datasetfield_id = (select id from datasetfield f where datasetfieldtype_id = 1 "
                + " and datasetversion_id = (select max(id) from datasetversion where dataset_id =r.dataset_id )) "
                + " and m.datasetversion_id = (select max(datasetversion_id) from filemetadata where datafile_id =r.datafile_id ) "
                + "  and m.datafile_id = r.datafile_id "
                + "  and r.dataset_id = o.id "
                + "  and r.guestbook_id = g.id "
                + " and  o.owner_id = "
                + dataverseId.toString()
                + " and r.guestbook_id = "
                + guestbookId.toString()
                + ";";           
        
        return findArray(queryString);       
    }

    
    
    
    
    private List<Object[]> findArray (String queryString){

        List<Object[]> retVal =  new ArrayList<>();

        List<Object[]> guestbookResults = em.createNativeQuery(queryString).getResultList();
        
        for (Object[] result : guestbookResults) {
            Object[] singleResult = new Object[11];
            singleResult[0] = result[1];
            singleResult[1] = result[2];
            if (result[3] != null){
                singleResult[2] = new SimpleDateFormat("MM/d/yyyy").format((Date) result[3]); 
            } else {
                singleResult[2] = "N/A";
            }           
            singleResult[3] = result[4];
            singleResult[4] = result[5];
            singleResult[5] = result[6];
            singleResult[6] = result[7];
            singleResult[7] = result[8];
            singleResult[8] = result[9];
            singleResult[9] = result[10];
            String cqString = "select q.questionstring, r.response  from customquestionresponse r, customquestion q where q.id = r.customquestion_id and r.guestbookResponse_id = " + (Integer) result[0];
                singleResult[10]  = em.createNativeQuery(cqString).getResultList();
            
            /*
            List<CustomQuestionResponse> customResponses = em.createQuery("select o from CustomQuestionResponse as  o where o.guestbookResponse.id = " + (Integer) result[0] + " order by o.customQuestion.id ", CustomQuestionResponse.class).getResultList();
            if(!customResponses.isEmpty()){
                singleResult[8] =  customResponses;
            }*/
            
            retVal.add(singleResult);
        }
        guestbookResults = null;       
        
        return retVal;
        
    }
    

    
    public List<Object[]> findArrayByGuestbookIdAndDataverseId (Long guestbookId, Long dataverseId){

        Guestbook gbIn = em.find(Guestbook.class, guestbookId);
        boolean hasCustomQuestions = gbIn.getCustomQuestions() != null;
        List<Object[]> retVal =  new ArrayList<>();

        String queryString = "select  r.id, v.value, r.responsetime, r.downloadtype,  m.label, r.name from guestbookresponse r,"
                + " datasetfieldvalue v, filemetadata m , dvobject o    "
                + " where "  
                + " v.datasetfield_id = (select id from datasetfield f where datasetfieldtype_id = 1 "
                + " and datasetversion_id = (select max(id) from datasetversion where dataset_id =r.dataset_id )) "
                + " and m.datasetversion_id = (select max(datasetversion_id) from filemetadata where datafile_id =r.datafile_id ) "
                + "  and m.datafile_id = r.datafile_id "
                + "  and r.dataset_id = o.id "
                + " and  o.owner_id = "
                + dataverseId.toString()
                + " and  r.guestbook_id = "
                + guestbookId.toString()
                + ";";
        List<Object[]> guestbookResults = em.createNativeQuery(queryString).getResultList();

        for (Object[] result : guestbookResults) {
            Object[] singleResult = new Object[6];
            singleResult[0] = result[1];
            if (result[2] != null){
                            singleResult[1] = new SimpleDateFormat("MMMM d, yyyy").format((Date) result[2]);
            } else {
                singleResult[1] =  "N/A";
            }

            singleResult[2] = result[3];
            singleResult[3] = result[4];
            singleResult[4] = result[5];
            if(hasCustomQuestions){
                String cqString = "select q.questionstring, r.response  from customquestionresponse r, customquestion q where q.id = r.customquestion_id and r.guestbookResponse_id = " + (Integer) result[0];<commit_msg>#3662 Cleanup Response Service Bean<commit_after>/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.harvard.iq.dataverse;

import edu.harvard.iq.dataverse.authorization.users.AuthenticatedUser;
import edu.harvard.iq.dataverse.authorization.users.User;
import static edu.harvard.iq.dataverse.util.JsfHelper.JH;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.faces.application.FacesMessage;
import javax.faces.component.UIComponent;
import javax.faces.component.UIInput;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.inject.Named;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

/**
 *
 * @author skraffmiller
 */
@Stateless
@Named
public class GuestbookResponseServiceBean {
    
    public static final String BASE_QUERY_STRING_WITH_GUESTBOOK = "select r.id, g.name, v.value,  r.responsetime, r.downloadtype,  m.label, r.dataFile_id, r.name, r.email, r.institution, r.position from guestbookresponse r,"
                + " datasetfieldvalue v, filemetadata m, dvobject o, guestbook g  "
                + " where "  
                + " v.datasetfield_id = (select id from datasetfield f where datasetfieldtype_id = 1 "
                + " and datasetversion_id = (select max(id) from datasetversion where dataset_id =r.dataset_id )) "
                + " and m.datasetversion_id = (select max(datasetversion_id) from filemetadata where datafile_id =r.datafile_id ) "
                + "  and m.datafile_id = r.datafile_id "
                + "  and r.dataset_id = o.id "
                + "  and r.guestbook_id = g.id ";
    
    public static final String BASE_QUERY_STRING_WITHOUT_GUESTBOOK = "select  r.id, v.value, r.responsetime, r.downloadtype,  m.label, r.name from guestbookresponse r,"
                + " datasetfieldvalue v, filemetadata m , dvobject o    "
                + " where "  
                + " v.datasetfield_id = (select id from datasetfield f where datasetfieldtype_id = 1 "
                + " and datasetversion_id = (select max(id) from datasetversion where dataset_id =r.dataset_id )) "
                + " and m.datasetversion_id = (select max(datasetversion_id) from filemetadata where datafile_id =r.datafile_id ) "
                + "  and m.datafile_id = r.datafile_id "
                + "  and r.dataset_id = o.id ";

    @PersistenceContext(unitName = "VDCNet-ejbPU")
    private EntityManager em;

    public List<GuestbookResponse> findAll() {
        return em.createQuery("select object(o) from GuestbookResponse as o order by o.responseTime desc", GuestbookResponse.class).getResultList();
    }

    public List<Long> findAllIds() {
        return findAllIds(null);
    }

    public List<Long> findAllIds(Long dataverseId) {
        if (dataverseId == null) {
            return em.createQuery("select o.id from GuestbookResponse as o order by o.responseTime desc", Long.class).getResultList();
        }
        return em.createQuery("select o.id from GuestbookResponse  o, Dataset d where o.dataset.id = d.id and d.owner.id = " + dataverseId + " order by o.responseTime desc", Long.class).getResultList();
    }

    public List<GuestbookResponse> findAllByGuestbookId(Long guestbookId) {

        if (guestbookId == null) {
        } else {
            return em.createQuery("select o from GuestbookResponse as o where o.guestbook.id = " + guestbookId + " order by o.responseTime desc", GuestbookResponse.class).getResultList();
        }
        return null;
    }
    
    public List<Object[]> findArrayByDataverseId (Long dataverseId){

        String queryString = BASE_QUERY_STRING_WITH_GUESTBOOK
                + " and  o.owner_id = "
                + dataverseId.toString()
                + ";";           
        
        return findArray(queryString);       
    }
    
    public List<Object[]> findArrayByDataverseIdAndGuestbookId (Long dataverseId, Long guestbookId){

        String queryString = BASE_QUERY_STRING_WITH_GUESTBOOK
                + " and  o.owner_id = "
                + dataverseId.toString()
                + " and r.guestbook_id = "
                + guestbookId.toString()
                + ";";           
        
        return findArray(queryString);       
    }

    
    
    
    
    private List<Object[]> findArray (String queryString){

        List<Object[]> retVal =  new ArrayList<>();

        List<Object[]> guestbookResults = em.createNativeQuery(queryString).getResultList();
        
        for (Object[] result : guestbookResults) {
            Object[] singleResult = new Object[11];
            singleResult[0] = result[1];
            singleResult[1] = result[2];
            if (result[3] != null){
                singleResult[2] = new SimpleDateFormat("MM/d/yyyy").format((Date) result[3]); 
            } else {
                singleResult[2] = "N/A";
            }           
            singleResult[3] = result[4];
            singleResult[4] = result[5];
            singleResult[5] = result[6];
            singleResult[6] = result[7];
            singleResult[7] = result[8];
            singleResult[8] = result[9];
            singleResult[9] = result[10];
            String cqString = "select q.questionstring, r.response  from customquestionresponse r, customquestion q where q.id = r.customquestion_id and r.guestbookResponse_id = " + (Integer) result[0];
                singleResult[10]  = em.createNativeQuery(cqString).getResultList();
            
            /*
            List<CustomQuestionResponse> customResponses = em.createQuery("select o from CustomQuestionResponse as  o where o.guestbookResponse.id = " + (Integer) result[0] + " order by o.customQuestion.id ", CustomQuestionResponse.class).getResultList();
            if(!customResponses.isEmpty()){
                singleResult[8] =  customResponses;
            }*/
            
            retVal.add(singleResult);
        }
        guestbookResults = null;       
        
        return retVal;
        
    }
    

    
    public List<Object[]> findArrayByGuestbookIdAndDataverseId (Long guestbookId, Long dataverseId){

        Guestbook gbIn = em.find(Guestbook.class, guestbookId);
        boolean hasCustomQuestions = gbIn.getCustomQuestions() != null;
        List<Object[]> retVal =  new ArrayList<>();

        String queryString = BASE_QUERY_STRING_WITHOUT_GUESTBOOK
                + " and  o.owner_id = "
                + dataverseId.toString()
                + " and  r.guestbook_id = "
                + guestbookId.toString()
                + ";";
        List<Object[]> guestbookResults = em.createNativeQuery(queryString).getResultList();

        for (Object[] result : guestbookResults) {
            Object[] singleResult = new Object[6];
            singleResult[0] = result[1];
            if (result[2] != null){
                            singleResult[1] = new SimpleDateFormat("MMMM d, yyyy").format((Date) result[2]);
            } else {
                singleResult[1] =  "N/A";
            }

            singleResult[2] = result[3];
            singleResult[3] = result[4];
            singleResult[4] = result[5];
            if(hasCustomQuestions){
                String cqString = "select q.questionstring, r.response  from customquestionresponse r, customquestion q where q.id = r.customquestion_id and r.guestbookResponse_id = " + (Integer) result[0];
{'fraction_non_alphanumeric': 0.10490371008429213, 'fraction_numerical': 0.0059681289608072355, 'mean_word_length': 2.3589584624922506, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6649227', 'n_tokens_mistral': 4772, 'n_tokens_neox': 4628, 'n_words': 1028}