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;
}
}
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( );
}
}
}