QuestionDAO.java

/*
 * Copyright (c) 2002-2022, City of Paris
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 *  1. Redistributions of source code must retain the above copyright notice
 *     and the following disclaimer.
 *
 *  2. Redistributions in binary form must reproduce the above copyright notice
 *     and the following disclaimer in the documentation and/or other materials
 *     provided with the distribution.
 *
 *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
 *     contributors may be used to endorse or promote products derived from
 *     this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 *
 * License 1.0
 */
package fr.paris.lutece.plugins.forms.business;

import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

import org.apache.commons.collections.CollectionUtils;

import fr.paris.lutece.plugins.genericattributes.business.Entry;
import fr.paris.lutece.plugins.genericattributes.business.EntryHome;
import fr.paris.lutece.portal.service.plugin.Plugin;
import fr.paris.lutece.util.ReferenceList;
import fr.paris.lutece.util.sql.DAOUtil;

/**
 * This class provides Data Access methods for Question objects
 */
public final class QuestionDAO implements IQuestionDAO
{
    // Constants

    private static final String SQL_QUERY_SELECT_ALL = "SELECT id_question, title, code, description, id_entry, id_step, is_visible_multiview_global, is_visible_multiview_form_selected, column_title, is_filterable_multiview_global, is_filterable_multiview_form_selected, multiview_column_order, export_display_order FROM forms_question";
    private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_ALL + " WHERE id_question = ?";
    private static final String SQL_QUERY_SELECT_BY_CODE = SQL_QUERY_SELECT_ALL + " WHERE code = ?";
    private static final String SQL_QUERY_SELECT_BY_CODE_AND_ENTRY_ID = SQL_QUERY_SELECT_BY_CODE + " AND id_entry = ? ";
    private static final String SQL_QUERY_INSERT = "INSERT INTO forms_question ( title, code, description, id_entry, id_step, is_visible_multiview_global, is_visible_multiview_form_selected, column_title, is_filterable_multiview_global, is_filterable_multiview_form_selected,multiview_column_order, export_display_order ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
    private static final String SQL_QUERY_DELETE = "DELETE FROM forms_question WHERE id_question = ? ";
    private static final String SQL_QUERY_UPDATE = "UPDATE forms_question SET id_question = ?, title = ?, code = ?, description = ?, id_entry = ?, id_step = ?, is_visible_multiview_global = ?, is_visible_multiview_form_selected = ?, column_title = ?, is_filterable_multiview_global = ?, is_filterable_multiview_form_selected = ?, multiview_column_order = ?, export_display_order = ? WHERE id_question = ?";
    private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_question FROM forms_question";
    private static final String SQL_QUERY_SELECT_BY_STEP = SQL_QUERY_SELECT_ALL + " WHERE id_step = ?";
    private static final String SQL_QUERY_SELECTALL_BY_FORM = "SELECT fq.id_question, fq.title, fq.code, fq.description, fq.id_entry, fq.id_step, fq.is_visible_multiview_global, fq.is_visible_multiview_form_selected , fq.column_title, fq.is_filterable_multiview_global, fq.is_filterable_multiview_form_selected,fq.multiview_column_order,fq.export_display_order FROM forms_question fq INNER JOIN forms_step fs ON fq.id_step = fs.id_step WHERE fs.id_form = ?";
    private static final String SQL_QUERY_SELECT_IN = SQL_QUERY_SELECT_ALL + " WHERE id_question IN ( ";
    
    /**
     * {@inheritDoc }
     */
    @Override
    public void insert( Question question, Plugin plugin )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS, plugin ) )
        {
            int nIndex = 1;
            daoUtil.setString( nIndex++, question.getTitle( ) );
            daoUtil.setString( nIndex++, question.getCode( ) );
            daoUtil.setString( nIndex++, question.getDescription( ) );
            daoUtil.setInt( nIndex++, question.getIdEntry( ) );
            daoUtil.setInt( nIndex++, question.getIdStep( ) );
            daoUtil.setBoolean( nIndex++, question.isVisibleMultiviewGlobal( ) );
            daoUtil.setBoolean( nIndex++, question.isVisibleMultiviewFormSelected( ) );
            daoUtil.setString( nIndex++, question.getColumnTitle( ) );
            daoUtil.setBoolean( nIndex++, question.isFiltrableMultiviewGlobal( ) );
            daoUtil.setBoolean( nIndex++, question.isFiltrableMultiviewFormSelected( ) );
            daoUtil.setInt( nIndex++, question.getMultiviewColumnOrder( ) );
            daoUtil.setInt( nIndex++, question.getExportDisplayOrder() );

            daoUtil.executeUpdate( );
            if ( daoUtil.nextGeneratedKey( ) )
            {
                question.setId( daoUtil.getGeneratedKeyInt( 1 ) );
            }
        }
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public Question load( int nKey, Plugin plugin )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
        {

            daoUtil.setInt( 1, nKey );
            daoUtil.executeQuery( );
            Question question = null;

            if ( daoUtil.next( ) )
            {
                question = dataToObject( daoUtil );
            }

            if ( question != null )
            {

                question.setEntry( getQuestionEntry( question.getIdEntry( ) ) );
            }

            return question;
        }
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public List<Question> loadByCode( String strCode, Plugin plugin )
    {
        List<Question> questionList = new ArrayList<>( );
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CODE, plugin ) )
        {
            daoUtil.setString( 1, strCode );
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                Question question = dataToObject( daoUtil );
                question.setEntry( getQuestionEntry( question.getIdEntry( ) ) );
                questionList.add( question );
            }
            return questionList;
        }
    }

    @Override
    public Question loadByCodeAndEntry( String strCode, int idEntry, Plugin plugin )
    {
        Question question = null;
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CODE_AND_ENTRY_ID, plugin ) )
        {
            daoUtil.setString( 1, strCode );
            daoUtil.setInt( 2, idEntry );
            daoUtil.executeQuery( );

            if ( daoUtil.next( ) )
            {
                question = dataToObject( daoUtil );
                question.setEntry( getQuestionEntry( question.getIdEntry( ) ) );
            }
        }
        return question;
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public void delete( int nKey, Plugin plugin )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
        {
            daoUtil.setInt( 1, nKey );
            daoUtil.executeUpdate( );
        }
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public void store( Question question, Plugin plugin )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
        {
            int nIndex = 1;

            daoUtil.setInt( nIndex++, question.getId( ) );
            daoUtil.setString( nIndex++, question.getTitle( ) );
            daoUtil.setString( nIndex++, question.getCode( ) );
            daoUtil.setString( nIndex++, question.getDescription( ) );
            daoUtil.setInt( nIndex++, question.getIdEntry( ) );
            daoUtil.setInt( nIndex++, question.getIdStep( ) );
            daoUtil.setBoolean( nIndex++, question.isVisibleMultiviewGlobal( ) );
            daoUtil.setBoolean( nIndex++, question.isVisibleMultiviewFormSelected( ) );
            daoUtil.setString( nIndex++, question.getColumnTitle( ) );
            daoUtil.setBoolean( nIndex++, question.isFiltrableMultiviewGlobal( ) );
            daoUtil.setBoolean( nIndex++, question.isFiltrableMultiviewFormSelected( ) );
            daoUtil.setInt( nIndex++, question.getMultiviewColumnOrder( ) );
            daoUtil.setInt( nIndex++, question.getExportDisplayOrder() );

            daoUtil.setInt( nIndex, question.getId( ) );

            daoUtil.executeUpdate( );
        }
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public List<Question> selectQuestionsList( Plugin plugin )
    {
        List<Question> questionList = new ArrayList<>( );
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin ) )
        {
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                questionList.add( dataToObject( daoUtil ) );
            }
        }

        for ( Question quest : questionList )
        {
            quest.setEntry( getQuestionEntry( quest.getIdEntry( ) ) );
        }
        return questionList;
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public List<Question> selectQuestionsListByStep( int nIdStep, Plugin plugin )
    {
        List<Question> questionList = new ArrayList<>( );
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_STEP, plugin ) )
        {
            daoUtil.setInt( 1, nIdStep );
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                questionList.add( dataToObject( daoUtil ) );
            }
        }
        for ( Question quest : questionList )
        {
            quest.setEntry( getQuestionEntry( quest.getIdEntry( ) ) );
        }
        return questionList;
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public List<Integer> selectIdQuestionsList( Plugin plugin )
    {
        List<Integer> questionList = new ArrayList<>( );
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin ) )
        {
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                questionList.add( daoUtil.getInt( 1 ) );
            }
        }
        return questionList;
    }

    @Override
    public List<Question> selectQuestionsListUncomplete( Plugin plugin )
    {
        List<Question> questionList = new ArrayList<>( );
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin ) )
        {
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                questionList.add( dataToObjectWithoutStepEntry( daoUtil ) );
            }
        }
        return questionList;
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public ReferenceList selectQuestionsReferenceList( Plugin plugin )
    {
        ReferenceList questionList = new ReferenceList( );
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin ) )
        {
            daoUtil.executeQuery( );
            while ( daoUtil.next( ) )
            {
                questionList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
            }
        }
        return questionList;
    }

    @Override
    public List<Question> selectQuestionsListByFormId( int nIdForm, Plugin plugin )
    {
        List<Question> questionList = new ArrayList<>( );

        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_BY_FORM, plugin ) )
        {
            daoUtil.setInt( 1, nIdForm );
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                questionList.add( dataToObject( daoUtil ) );
            }
            for ( Question quest : questionList )
            {

                quest.setEntry( getQuestionEntry( quest.getIdEntry( ) ) );

            }
        }
        return questionList;

    }

    @Override
    public List<Question> selectQuestionsListByFormIdUncomplete( int nIdForm, Plugin plugin )
    {
        List<Question> questionList = new ArrayList<>( );
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_BY_FORM, plugin ) )
        {
            daoUtil.setInt( 1, nIdForm );
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                questionList.add( dataToObjectWithoutStepEntry( daoUtil ) );
            }
        }
        return questionList;
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public ReferenceList selectQuestionsReferenceListByForm( int nIdForm, Plugin plugin )
    {
        ReferenceList questionList = new ReferenceList( );
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_BY_FORM, plugin ) )
        {
            daoUtil.setInt( 1, nIdForm );
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                questionList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
            }
        }
        return questionList;
    }

    /**
     * @param nIdEntry
     *            the entry primary key
     * @return the Entry
     */
    private Entry getQuestionEntry( int nIdEntry )
    {
        return EntryHome.findByPrimaryKey( nIdEntry );
    }

    /**
     * @param nIdStep
     *            the step primary key
     * @return the Step
     */
    private Step getQuestionStep( int nIdStep )
    {
        return StepHome.findByPrimaryKey( nIdStep );
    }

    /**
     * 
     * @param daoUtil
     *            The daoutil
     * @return The populated Question object
     */
    private Question dataToObject( DAOUtil daoUtil )
    {
        Question question = new Question( );

        question.setId( daoUtil.getInt( "id_question" ) );
        question.setTitle( daoUtil.getString( "title" ) );
        question.setCode( daoUtil.getString( "code" ) );
        question.setDescription( daoUtil.getString( "description" ) );
        question.setIdEntry( daoUtil.getInt( "id_entry" ) );
        question.setIdStep( daoUtil.getInt( "id_step" ) );
        question.setVisibleMultiviewGlobal( daoUtil.getBoolean( "is_visible_multiview_global" ) );
        question.setVisibleMultiviewFormSelected( daoUtil.getBoolean( "is_visible_multiview_form_selected" ) );
        question.setColumnTitle( daoUtil.getString( "column_title" ) );
        question.setFiltrableMultiviewGlobal( daoUtil.getBoolean( "is_filterable_multiview_global" ) );
        question.setFiltrableMultiviewFormSelected( daoUtil.getBoolean( "is_filterable_multiview_form_selected" ) );
        question.setMultiviewColumnOrder( daoUtil.getInt( "multiview_column_order" ) );
        question.setExportDisplayOrder( daoUtil.getInt( "export_display_order" ) );
        question.setStep( getQuestionStep( question.getIdStep( ) ) );

        return question;
    }

    @Override
    public List<Question> loadMultiple( List<Integer> keyList, Plugin plugin )
    {
        List<Question> list = new ArrayList<>( );
        if ( CollectionUtils.isEmpty( keyList ) )
        {
            return list;
        }

        String query = SQL_QUERY_SELECT_IN + keyList.stream( ).distinct( ).map( i -> "?" ).collect( Collectors.joining( "," ) ) + " )";
        try ( DAOUtil daoUtil = new DAOUtil( query, plugin ) )
        {
            for ( int i = 0; i < keyList.size( ); i++ )
            {
                daoUtil.setInt( i + 1, keyList.get( i ) );
            }
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                list.add( dataToObjectWithoutStepEntry( daoUtil ) );
            }
        }
        return list;
    }

    /**
     * 
     * @param daoUtil
     *            The daoutil
     * @return The populated Question object
     */
    private Question dataToObjectWithoutStepEntry( DAOUtil daoUtil )
    {
        Question question = new Question( );

        question.setId( daoUtil.getInt( "id_question" ) );
        question.setTitle( daoUtil.getString( "title" ) );
        question.setCode( daoUtil.getString( "code" ) );
        question.setDescription( daoUtil.getString( "description" ) );
        question.setIdEntry( daoUtil.getInt( "id_entry" ) );
        question.setIdStep( daoUtil.getInt( "id_step" ) );
        question.setVisibleMultiviewGlobal( daoUtil.getBoolean( "is_visible_multiview_global" ) );
        question.setVisibleMultiviewFormSelected( daoUtil.getBoolean( "is_visible_multiview_form_selected" ) );
        question.setColumnTitle( daoUtil.getString( "column_title" ) );
        question.setFiltrableMultiviewGlobal( daoUtil.getBoolean( "is_filterable_multiview_global" ) );
        question.setFiltrableMultiviewFormSelected( daoUtil.getBoolean( "is_filterable_multiview_form_selected" ) );
        question.setMultiviewColumnOrder( daoUtil.getInt( "multiview_column_order" ) );
        question.setExportDisplayOrder( daoUtil.getInt( "export_display_order" ) );

        return question;
    }
 
}