DemandDAO.java

/*
 * Copyright (c) 2002-2025, 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.notificationstore.business;

import fr.paris.lutece.plugins.grubusiness.business.customer.Customer;
import fr.paris.lutece.plugins.grubusiness.business.demand.Demand;
import fr.paris.lutece.plugins.grubusiness.business.demand.IDemandDAO;
import fr.paris.lutece.plugins.grubusiness.business.notification.NotificationFilter;
import fr.paris.lutece.plugins.notificationstore.service.NotificationStorePlugin;
import fr.paris.lutece.portal.service.util.AppLogService;
import fr.paris.lutece.util.sql.DAOUtil;

import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.commons.lang3.StringUtils;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.DeserializationFeature;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

/**
 * This class provides Data Access methods for Demand objects stored in SQL database
 */
public final class DemandDAO implements IDemandDAO
{
    // Columns
    private static final String COLUMN_UID = "uid";
    private static final String COLUMN_DEMAND_ID = "id";
    private static final String COLUMN_TYPE_ID = "demand_type_id";
    private static final String COLUMN_SUBTYPE_ID = "subtype_id";
    private static final String COLUMN_REFERENCE = "reference";
    private static final String COLUMN_STATUS_ID = "status_id";
    private static final String COLUMN_CUSTOMER_ID = "customer_id";
    private static final String COLUMN_CREATION_DATE = "creation_date";
    private static final String COLUMN_CLOSURE_DATE = "closure_date";
    private static final String COLUMN_MAX_STEPS = "max_steps";
    private static final String COLUMN_CURRENT_STEP = "current_step";
    private static final String COLUMN_MODIFY_DATE = "modify_date";
    private static final String COLUMN_META_DATA = "meta_data";

    // SQL queries
    private static final String SQL_QUERY_DEMAND_ALL_FIELDS_WITH_NO_DEMAND_ID = " id, demand_type_id, subtype_id, reference, status_id, customer_id, creation_date, closure_date, max_steps, current_step, modify_date, meta_data";
    private static final String SQL_QUERY_DEMAND_ALL_FIELDS = " uid, " + SQL_QUERY_DEMAND_ALL_FIELDS_WITH_NO_DEMAND_ID;
    private static final String SQL_QUERY_DEMAND_SELECT_BY_ID = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS + " FROM notificationstore_demand WHERE uid = ? ";
    private static final String SQL_QUERY_DEMAND_SELECT_BY_DEMAND_ID = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS + " FROM notificationstore_demand WHERE id = ? ";
    private static final String SQL_QUERY_DEMAND_SELECT_BY_DEMAND_ID_AND_TYPE_ID = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS
            + " FROM notificationstore_demand WHERE id = ? AND  demand_type_id = ? AND customer_id  = ? ";
    private static final String SQL_QUERY_DEMAND_SELECT_ALL = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS + " FROM notificationstore_demand";
    private static final String SQL_QUERY_DEMAND_SELECT_DEMAND_IDS = "SELECT uid FROM notificationstore_demand ";
    private static final String SQL_QUERY_DEMAND_SELECT_BY_IDS = SQL_QUERY_DEMAND_SELECT_ALL + " where uid in ( %s )";

    private static final String SQL_QUERY_DEMAND_INSERT = "INSERT INTO notificationstore_demand ( " + SQL_QUERY_DEMAND_ALL_FIELDS_WITH_NO_DEMAND_ID
            + " ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?) ";
    private static final String SQL_QUERY_DEMAND_UPDATE = "UPDATE notificationstore_demand SET status_id = ?, customer_id = ?, closure_date = ?, current_step = ?, subtype_id = ?, modify_date = ?, meta_data = ? WHERE uid = ? AND demand_type_id = ? ";
    private static final String SQL_QUERY_DEMAND_UPDATE_LINK = "UPDATE notificationstore_demand SET customer_id = ? WHERE customer_id = ?";
    private static final String SQL_QUERY_DEMAND_DELETE = "DELETE FROM notificationstore_demand WHERE id = ? AND demand_type_id = ? AND customer_id = ? ";
    private static final String SQL_QUERY_DEMAND_DELETE_BY_UID = "DELETE FROM notificationstore_demand WHERE uid = ? ";
    private static final String SQL_QUERY_DEMAND_SELECT_BY_CUSTOMER_ID = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS
            + " FROM notificationstore_demand WHERE customer_id = ?";
    private static final String SQL_QUERY_DEMAND_SELECT_BY_REFERENCE = "SELECT " + SQL_QUERY_DEMAND_ALL_FIELDS
            + " FROM notificationstore_demand WHERE reference = ?";

    private static final String SQL_QUERY_IDS_BY_CUSTOMER_ID_AND_DEMANDTYPE_ID = "SELECT distinct(gd.uid) " + " FROM notificationstore_demand gd "
            + " INNER JOIN notificationstore_notification gn ON ( gd.id = gn.demand_id and gd.demand_type_id = gn.demand_type_id and gd.customer_id = gn.customer_id ) "
            + " INNER JOIN notificationstore_notification_content gc ON ( gn.id = gc.notification_id ) " + " WHERE gd.customer_id = ? ";

    private static final String SQL_QUERY_IDS_BY_STATUS = "SELECT distinct gd.uid " + " FROM notificationstore_demand gd "
            + " JOIN notificationstore_notification gn ON ( gd.id = gn.demand_id and gd.demand_type_id=gn.demand_type_id and gd.customer_id=gn.customer_id) "
            + " JOIN notificationstore_notification_content gc ON gn.id = gc.notification_id " + " WHERE gd.customer_id = ? " + " AND gd.status_id IN ( ";

    private static final String SQL_QUERY_DEMAND_UPDATE_STATUS_ID = "UPDATE notificationstore_demand d"
            + " INNER JOIN notificationstore_notification n2 ON (d.id = n2.demand_id AND d.demand_type_id = n2.demand_type_id AND d.customer_id = n2.customer_id ) "
            + " INNER JOIN notificationstore_notification_content c2 ON (n2.id = c2.notification_id  AND c2.notification_type ='MYDASHBOARD' AND c2.id_temporary_status = ? ) "
            + " SET d.status_id = ?" + " WHERE n2.id IN (" + "    SELECT MAX(n.id) " + "    FROM notificationstore_notification n "
            + "    INNER JOIN notificationstore_notification_content c ON (n.id = c.notification_id " + "    AND c.notification_type ='MYDASHBOARD') "
            + "    WHERE d.id = n.demand_id " + "    AND d.demand_type_id = n.demand_type_id " + "    AND d.customer_id = n.customer_id "
            + "    GROUP BY n.demand_id, n.demand_type_id, n.customer_id)";

    private static final String SQL_QUERY_FILTER_WHERE_BASE = " WHERE 1 ";
    private static final String SQL_FILTER_BY_DEMAND_ID = " AND id = ? ";
    private static final String SQL_FILTER_BY_DEMAND_TYPE_ID = " AND demand_type_id = ? ";
    private static final String SQL_FILTER_BY_CUSTOMER_ID = " AND customer_id = ? ";
    private static final String SQL_FILTER_BY_DEMAND_TYPE_GD_ID = " AND gd.demand_type_id = ? ";
    private static final String SQL_FILTER_BY_DEMAND_TYPE_GD_ID_IN = " AND gd.demand_type_id IN ( ";
    private static final String SQL_FILTER_BY_START_DATE = " AND creation_date >= ? ";
    private static final String SQL_FILTER_BY_END_DATE = " AND creation_date <= ? ";
    private static final String SQL_FILTER_NOTIFICATION_TYPE = " AND gc.notification_type = ? ";
    private static final String SQL_QUERY_FILTER_ORDER = " ORDER BY uid ASC";
    private static final String SQL_QUERY_DATE_ORDER_DESC = " ORDER BY modify_date DESC";
    private static final String SQL_QUERY_DATE_ORDER_ASC = " ORDER BY modify_date ASC";

    private static ObjectMapper _mapper = ( new ObjectMapper( ) ).configure( DeserializationFeature.UNWRAP_ROOT_VALUE, false )
            .configure( DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false );

    private static TypeReference<HashMap<String, String>> hashMapTypeRef = new TypeReference<HashMap<String, String>>( )
    {
    };

    /**
     * {@inheritDoc}
     */
    @Override
    public Demand load( int nId )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_BY_ID, NotificationStorePlugin.getPlugin( ) ) )
        {
            daoUtil.setInt( 1, nId );
            daoUtil.executeQuery( );

            Demand demand = null;

            if ( daoUtil.next( ) )
            {
                demand = dao2Demand( daoUtil );
            }

            return demand;
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Collection<Demand> loadByCustomerId( String strCustomerId )
    {
        Collection<Demand> collectionDemands = new ArrayList<>( );

        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_BY_CUSTOMER_ID, NotificationStorePlugin.getPlugin( ) ) )
        {

            daoUtil.setString( 1, strCustomerId );
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                collectionDemands.add( dao2Demand( daoUtil ) );
            }

        }

        return collectionDemands;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public List<Demand> loadByIds( List<Integer> listIds )
    {
        List<Demand> listDemands = new ArrayList<>( );

        if ( listIds == null || listIds.isEmpty( ) )
            return listDemands;

        String sql = String.format( SQL_QUERY_DEMAND_SELECT_BY_IDS, listIds.stream( ).map( v -> "?" ).collect( Collectors.joining( ", " ) ) );

        try ( DAOUtil daoUtil = new DAOUtil( sql, NotificationStorePlugin.getPlugin( ) ) )
        {

            int index = 1;
            for ( Integer strId : listIds )
            {
                daoUtil.setInt( index++, strId );
            }

            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                listDemands.add( dao2Demand( daoUtil ) );
            }

        }

        return listDemands;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Collection<Demand> loadByReference( String strReference )
    {
        Collection<Demand> collectionDemands = new ArrayList<>( );

        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_BY_REFERENCE, NotificationStorePlugin.getPlugin( ) ) )
        {

            daoUtil.setString( 1, strReference );
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                collectionDemands.add( dao2Demand( daoUtil ) );
            }

        }

        return collectionDemands;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Collection<Demand> loadByFilter( NotificationFilter filter )
    {
        Collection<Demand> collectionDemands = new ArrayList<>( );
        StringBuilder sql = new StringBuilder( SQL_QUERY_DEMAND_SELECT_ALL + SQL_QUERY_FILTER_WHERE_BASE );

        buildSql( sql, filter );

        try ( DAOUtil daoUtil = new DAOUtil( sql.toString( ), NotificationStorePlugin.getPlugin( ) ) )
        {

            fillDao( daoUtil, filter );

            daoUtil.executeQuery( );
            while ( daoUtil.next( ) )
            {
                collectionDemands.add( dao2Demand( daoUtil ) );
            }

        }

        return collectionDemands;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public List<Integer> loadIdsByFilter( NotificationFilter filter )
    {
        List<Integer> listIds = new ArrayList<>( );
        StringBuilder sql = new StringBuilder( SQL_QUERY_DEMAND_SELECT_DEMAND_IDS + SQL_QUERY_FILTER_WHERE_BASE );

        buildSql( sql, filter );

        try ( DAOUtil daoUtil = new DAOUtil( sql.toString( ), NotificationStorePlugin.getPlugin( ) ) )
        {
            fillDao( daoUtil, filter );

            daoUtil.executeQuery( );
            while ( daoUtil.next( ) )
            {
                listIds.add( daoUtil.getInt( 1 ) );
            }

        }

        return listIds;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Demand insert( Demand demand )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_INSERT, Statement.RETURN_GENERATED_KEYS, NotificationStorePlugin.getPlugin( ) ) )
        {

            int nIndex = 1;

            daoUtil.setString( nIndex++, demand.getId( ) );
            daoUtil.setString( nIndex++, demand.getTypeId( ) );
            daoUtil.setString( nIndex++, demand.getSubtypeId( ) );
            daoUtil.setString( nIndex++, demand.getReference( ) );
            daoUtil.setInt( nIndex++, demand.getStatusId( ) );
            daoUtil.setString( nIndex++, demand.getCustomer( ).getCustomerId( ) );
            daoUtil.setTimestamp( nIndex++, demand.getCreationDate( ) > 0 ? new Timestamp( demand.getCreationDate( ) ) : null );
            daoUtil.setTimestamp( nIndex++, demand.getClosureDate( ) > 0 ? new Timestamp( demand.getClosureDate( ) ) : null );
            daoUtil.setInt( nIndex++, demand.getMaxSteps( ) );
            daoUtil.setInt( nIndex++, demand.getCurrentStep( ) );
            daoUtil.setTimestamp( nIndex++, demand.getModifyDate( ) > 0 ? new Timestamp( demand.getModifyDate( ) ) : null );
            daoUtil.setString( nIndex++, hashMapToJson( demand.getMetaData( ) ) );

            daoUtil.executeUpdate( );
            if ( daoUtil.nextGeneratedKey( ) )
            {
                demand.setUID( daoUtil.getGeneratedKeyInt( 1 ) );
            }
        }

        return demand;
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public Demand store( Demand demand )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_UPDATE, NotificationStorePlugin.getPlugin( ) ) )
        {
            int nIndex = 1;

            // update
            daoUtil.setInt( nIndex++, demand.getStatusId( ) );
            daoUtil.setString( nIndex++, demand.getCustomer( ).getCustomerId( ) );
            daoUtil.setTimestamp( nIndex++, demand.getClosureDate( ) > 0 ? new Timestamp( demand.getClosureDate( ) ) : null );
            daoUtil.setInt( nIndex++, demand.getCurrentStep( ) );
            daoUtil.setString( nIndex++, demand.getSubtypeId( ) );
            daoUtil.setTimestamp( nIndex++, demand.getModifyDate( ) > 0 ? new Timestamp( demand.getModifyDate( ) ) : null );
            daoUtil.setString( nIndex++, hashMapToJson( demand.getMetaData( ) ) );

            // where primary_key
            daoUtil.setInt( nIndex++, demand.getUID( ) );
            daoUtil.setString( nIndex++, demand.getTypeId( ) );

            daoUtil.executeUpdate( );

            return demand;
        }
    }

    /**
     * {@inheritDoc }
     */
    @Override
    public void reassignDemands( String strOldCustomerId, String strNewCustomerId )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_UPDATE_LINK, NotificationStorePlugin.getPlugin( ) ) )
        {
            int nIndex = 1;

            // update
            daoUtil.setString( nIndex++, strNewCustomerId );
            daoUtil.setString( nIndex, strOldCustomerId );

            daoUtil.executeUpdate( );
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void delete( String strDemandId, String strDemandTypeId, String strCustomerId )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_DELETE, NotificationStorePlugin.getPlugin( ) ) )
        {
            daoUtil.setString( 1, strDemandId );
            daoUtil.setString( 2, strDemandTypeId );
            daoUtil.setString( 2, strCustomerId );

            daoUtil.executeUpdate( );
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void deleteByUid( int nUid )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_DELETE_BY_UID, NotificationStorePlugin.getPlugin( ) ) )
        {
            daoUtil.setInt( 1, nUid );

            daoUtil.executeUpdate( );
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public List<String> loadAllIds( )
    {
        List<String> collectionIds = new ArrayList<>( );

        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_ALL, NotificationStorePlugin.getPlugin( ) ) )
        {
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                collectionIds.add( String.valueOf( dao2Demand( daoUtil ).getId( ) ) );
            }

            return collectionIds;
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Demand loadByDemandId( String strDemandId )
    {
        Demand demand = null;

        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_BY_DEMAND_ID, NotificationStorePlugin.getPlugin( ) ) )
        {
            daoUtil.setString( 1, strDemandId );
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                demand = dao2Demand( daoUtil );
                break;
            }
            return demand;
        }
    }

    @Override
    public Demand loadByDemandIdAndTypeIdAndCustomerId( String strDemandId, String strDemandTypeId, String strCustomerId )
    {
        Demand demand = null;

        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_SELECT_BY_DEMAND_ID_AND_TYPE_ID, NotificationStorePlugin.getPlugin( ) ) )
        {
            daoUtil.setString( 1, strDemandId );
            daoUtil.setString( 2, strDemandTypeId );
            daoUtil.setString( 3, strCustomerId );
            daoUtil.executeQuery( );

            while ( daoUtil.next( ) )
            {
                demand = dao2Demand( daoUtil );
                break;
            }
            return demand;
        }
    }

    /**
     * Converts data from DAO to a Demand object
     * 
     * @param daoUtil
     *            the DAO containing the data to convert
     * @return a Demand object
     */
    private Demand dao2Demand( DAOUtil daoUtil )
    {
        Demand demand = new Demand( );

        demand.setUID( daoUtil.getInt( COLUMN_UID ) );
        demand.setId( daoUtil.getString( COLUMN_DEMAND_ID ) );
        demand.setTypeId( daoUtil.getString( COLUMN_TYPE_ID ) );
        demand.setSubtypeId( daoUtil.getString( COLUMN_SUBTYPE_ID ) );
        demand.setStatusId( daoUtil.getInt( COLUMN_STATUS_ID ) );

        Customer customer = new Customer( );
        customer.setCustomerId( daoUtil.getString( COLUMN_CUSTOMER_ID ) );
        demand.setCustomer( customer );

        demand.setReference( daoUtil.getString( COLUMN_REFERENCE ) );
        demand.setCreationDate( daoUtil.getTimestamp( COLUMN_CREATION_DATE ) != null ? daoUtil.getTimestamp( COLUMN_CREATION_DATE ).getTime( ) : 0 );
        demand.setClosureDate( daoUtil.getTimestamp( COLUMN_CLOSURE_DATE ) != null ? daoUtil.getTimestamp( COLUMN_CLOSURE_DATE ).getTime( ) : 0 );
        demand.setMaxSteps( daoUtil.getInt( COLUMN_MAX_STEPS ) );
        demand.setCurrentStep( daoUtil.getInt( COLUMN_CURRENT_STEP ) );
        demand.setModifyDate( daoUtil.getTimestamp( COLUMN_MODIFY_DATE ) != null ? daoUtil.getTimestamp( COLUMN_MODIFY_DATE ).getTime( ) : 0 );
        demand.setMetaData( jsonToHashMap( daoUtil.getString( COLUMN_META_DATA ) ) );

        return demand;
    }

    /**
     * convert json string as hashmap
     * 
     * @param json
     * @return the hashmap
     */
    private Map<String, String> jsonToHashMap( String json )
    {
        if ( json == null )
        {
            return null;
        }

        try
        {
            return _mapper.readValue( json, hashMapTypeRef );
        }
        catch( JsonProcessingException e )
        {
            AppLogService.error( "A problem occurred to generate a map from Json", e );
            return null;
        }
    }

    /**
     * generate Json String of a hashmap
     * 
     * @param map
     * @return the json
     */
    private String hashMapToJson( Map<String, String> map )
    {
        try
        {
            return _mapper.writeValueAsString( map );
        }
        catch( JsonProcessingException e )
        {
            AppLogService.error( "A problem occurred to generate Json from map", e );
            return null;
        }
    }

    /**
     * build the sql with selected filters
     * 
     * @param sql
     * @param filter
     */
    private void buildSql( StringBuilder sql, NotificationFilter filter )
    {

        if ( filter.containsDemandId( ) )
        {
            sql.append( SQL_FILTER_BY_DEMAND_ID );
        }

        if ( filter.containsDemandTypeId( ) )
        {
            sql.append( SQL_FILTER_BY_DEMAND_TYPE_ID );
        }

        if ( filter.containsCustomerId( ) )
        {
            sql.append( SQL_FILTER_BY_CUSTOMER_ID );
        }

        if ( filter.containsStartDate( ) )
        {
            sql.append( SQL_FILTER_BY_START_DATE );
        }

        if ( filter.containsEndDate( ) )
        {
            sql.append( SQL_FILTER_BY_END_DATE );
        }

        sql.append( SQL_QUERY_FILTER_ORDER );
    }

    /**
     * fill the dao with filter's values
     * 
     * @param daoUtil
     * @param filter
     */
    private void fillDao( DAOUtil daoUtil, NotificationFilter filter )
    {
        int i = 1;
        if ( filter.containsDemandId( ) )
        {
            daoUtil.setString( i++, filter.getDemandId( ) );
        }

        if ( filter.containsDemandTypeId( ) )
        {
            daoUtil.setString( i++, filter.getDemandTypeId( ) );
        }

        if ( filter.containsCustomerId( ) )
        {
            daoUtil.setString( i++, filter.getCustomerId( ) );
        }

        if ( filter.containsStartDate( ) )
        {
            daoUtil.setTimestamp( i++, new Timestamp( filter.getStartDate( ) ) );
        }

        if ( filter.containsEndDate( ) )
        {
            daoUtil.setTimestamp( i++, new Timestamp( filter.getEndDate( ) ) );
        }
    }

    @Override
    public List<Integer> loadIdsByCustomerIdAndIdDemandType( String strCustomerId, String strNotificationType, String strIdDemandType )
    {
        return this.loadIdsByCustomerIdAndIdDemandType( strCustomerId, strNotificationType, strIdDemandType, null );
    }

    @Override
    public List<Integer> loadIdsByCustomerIdAndIdDemandType( final String strCustomerId, final String strNotificationType, final String strIdDemandType,
            final String strDirectionDateOrderBy )
    {
        List<Integer> listIds = new ArrayList<>( );
        String strSql = SQL_QUERY_IDS_BY_CUSTOMER_ID_AND_DEMANDTYPE_ID;

        if ( StringUtils.isNotEmpty( strNotificationType ) )
        {
            strSql += SQL_FILTER_NOTIFICATION_TYPE;
        }

        if ( StringUtils.isNotEmpty( strIdDemandType ) )
        {
            strSql += SQL_FILTER_BY_DEMAND_TYPE_GD_ID;
        }

        if ( StringUtils.isNotEmpty( strDirectionDateOrderBy ) && List.of( "ASC", "DESC" ).contains( strDirectionDateOrderBy ) )
        {
            if ( "ASC".equalsIgnoreCase( strDirectionDateOrderBy ) )
            {
                strSql += SQL_QUERY_DATE_ORDER_ASC;
            }
            else
            {
                strSql += SQL_QUERY_DATE_ORDER_DESC;
            }
        }

        try ( DAOUtil daoUtil = new DAOUtil( strSql, NotificationStorePlugin.getPlugin( ) ) )
        {
            int nIndex = 1;
            daoUtil.setString( nIndex++, strCustomerId );

            if ( StringUtils.isNotEmpty( strNotificationType ) )
            {
                daoUtil.setString( nIndex++, strNotificationType );
            }

            if ( StringUtils.isNotEmpty( strIdDemandType ) )
            {
                daoUtil.setString( nIndex++, strIdDemandType );
            }

            daoUtil.executeQuery( );

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

            return listIds;
        }
    }

    @Override
    public List<Integer> loadIdsByStatus( String strCustomerId, List<String> listStatus, String strNotificationType, String strIdDemandType )
    {
        List<Integer> listIds = new ArrayList<>( );
        String strQuery = SQL_QUERY_IDS_BY_STATUS;

        if ( !listStatus.isEmpty( ) )
        {
            strQuery += listStatus.stream( ).map( i -> "?" ).collect( Collectors.joining( "," ) ) + " ) ";
        }

        List<Integer> listIdsDemandType = new ArrayList<>( );
        if ( StringUtils.isNotEmpty( strIdDemandType ) )
        {
            listIdsDemandType = Arrays.stream( strIdDemandType.split( "," ) ).map( Integer::parseInt ).collect( Collectors.toList( ) );
            strQuery += SQL_FILTER_BY_DEMAND_TYPE_GD_ID_IN;
            strQuery += listIdsDemandType.stream( ).map( i -> "?" ).collect( Collectors.joining( "," ) ) + " ) ";

        }

        if ( StringUtils.isNotEmpty( strNotificationType ) )
        {
            strQuery += SQL_FILTER_NOTIFICATION_TYPE;
        }

        strQuery += SQL_QUERY_DATE_ORDER_DESC;

        try ( DAOUtil daoUtil = new DAOUtil( strQuery, NotificationStorePlugin.getPlugin( ) ) )
        {
            int nIndexIn = 1;
            daoUtil.setString( nIndexIn++, strCustomerId );

            for ( String strStatus : listStatus )
            {
                daoUtil.setString( nIndexIn, strStatus );
                nIndexIn++;
            }
            for ( Integer nIdDemandType : listIdsDemandType )
            {
                daoUtil.setInt( nIndexIn, nIdDemandType );
                nIndexIn++;
            }
            if ( StringUtils.isNotEmpty( strNotificationType ) )
            {
                daoUtil.setString( nIndexIn++, strNotificationType );
            }

            daoUtil.executeQuery( );

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

            return listIds;
        }
    }

    @Override
    public void updateDemandsStatusId( int nNewStatusId, int nTemporaryStatusId )
    {
        try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DEMAND_UPDATE_STATUS_ID, NotificationStorePlugin.getPlugin( ) ) )
        {
            daoUtil.setInt( 1, nTemporaryStatusId );
            daoUtil.setInt( 2, nNewStatusId );

            daoUtil.executeUpdate( );
        }
    }
}