1 /* 2 * Copyright (c) 2002-2018, Mairie de Paris 3 * All rights reserved. 4 * 5 * Redistribution and use in source and binary forms, with or without 6 * modification, are permitted provided that the following conditions 7 * are met: 8 * 9 * 1. Redistributions of source code must retain the above copyright notice 10 * and the following disclaimer. 11 * 12 * 2. Redistributions in binary form must reproduce the above copyright notice 13 * and the following disclaimer in the documentation and/or other materials 14 * provided with the distribution. 15 * 16 * 3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its 17 * contributors may be used to endorse or promote products derived from 18 * this software without specific prior written permission. 19 * 20 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 21 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 22 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 23 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE 24 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 25 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 26 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS 27 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 28 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 29 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 30 * POSSIBILITY OF SUCH DAMAGE. 31 * 32 * License 1.0 33 */ 34 package fr.paris.lutece.plugins.stock.modules.recommendation.business; 35 36 import fr.paris.lutece.portal.service.plugin.Plugin; 37 import fr.paris.lutece.util.sql.DAOUtil; 38 import java.sql.Timestamp; 39 import java.util.ArrayList; 40 import java.util.List; 41 42 /** 43 * AvailableProductsDAO 44 */ 45 public class AvailableProductsDAO 46 { 47 48 private static final String SQL_QUERY_SELECTALL = "SELECT a.id_product FROM stock_product a, stock_product_attribute_date b, stock_product_attribute_date c " 49 + " WHERE a.id_product = b.owner_id AND b.attribute_key = 'start' AND a.id_product = c.owner_id " 50 + " AND c.attribute_key = 'end' AND b.attribute_value < ? AND c.attribute_value > ?"; 51 52 private static final String SQL_QUERY_PRODUCT = "SELECT name FROM stock_product WHERE id_product = ?"; 53 54 /** 55 * Get the list of available products IDs at a given time 56 * 57 * @param time 58 * The time 59 * @param plugin 60 * The plugin 61 * @return The list 62 */ 63 public List<Integer> selectAvailableProductsIdList( Timestamp time, Plugin plugin ) 64 { 65 List<Integer> list = new ArrayList<Integer>( ); 66 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ); 67 daoUtil.setTimestamp( 1, time ); 68 daoUtil.setTimestamp( 2, time ); 69 daoUtil.executeQuery( ); 70 71 while ( daoUtil.next( ) ) 72 { 73 list.add( daoUtil.getInt( 1 ) ); 74 } 75 76 daoUtil.free( ); 77 78 return list; 79 } 80 81 /** 82 * Gets product infos 83 * 84 * @param product 85 * The product 86 * @param plugin 87 * The plugin 88 */ 89 public void getProductInfos( RecommendedProduct product, Plugin plugin ) 90 { 91 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_PRODUCT, plugin ); 92 daoUtil.setInt( 1, product.getProductId( ) ); 93 daoUtil.executeQuery( ); 94 95 if ( daoUtil.next( ) ) 96 { 97 product.setProductName( daoUtil.getString( 1 ) ); 98 } 99 100 daoUtil.free( ); 101 } 102 }