LinksPortletDAO

/*
 * Copyright (c) 2002-2017, Mairie de 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.links.business.portlet;

import fr.paris.lutece.plugins.links.business.Link;
import fr.paris.lutece.portal.business.portlet.Portlet;
import fr.paris.lutece.util.ReferenceList;
import fr.paris.lutece.util.sql.DAOUtil;

import java.util.ArrayList;
import java.util.Collection;


/**
 * This class provides Data Access methods for LinksPortlet objects
 */
public final class LinksPortletDAO implements ILinksPortletDAO
{
    // Constants
    private static final String SQL_QUERY_SELECT = " SELECT id_portlet FROM core_portlet WHERE id_portlet = ? ";
    private static final String SQL_QUERY_INSERT = " INSERT INTO link_list_portlet ( id_portlet, id_link, link_order ) VALUES ( ? , ? , ? )";
    private static final String SQL_QUERY_DELETE = " DELETE FROM link_list_portlet WHERE id_portlet = ? ";
    private static final String SQL_QUERY_DELETE_LINK_PORTLET = " DELETE FROM link_portlet WHERE id_portlet = ? ";
    private static final String SQL_QUERY_DELETE_LINK = " DELETE FROM link_list_portlet WHERE id_portlet=? AND id_link = ? ";
    private static final String SQL_QUERY_SELECT_LINK = " SELECT id_link, name, url FROM link ORDER BY name ";
    private static final String SQL_QUERY_SELECT_ID_LINK = " SELECT id_link FROM link_list_portlet WHERE  id_portlet = ? AND  id_link = ? ";
    private static final String SQL_QUERY_SELECT_LINK_IN_PORTLET_LIST = " SELECT a.id_link, a.name, a.url, a.description, a.image_content, a.mime_type, a.workgroup_key " +
        " FROM link a, link_list_portlet b WHERE a.id_link = b.id_link AND b.id_portlet = ? " +
        " ORDER BY b.link_order";
    private static final String SQL_QUERY_SELECT_LINK_ORDER = " SELECT link_order FROM link_list_portlet WHERE id_portlet = ? AND id_link = ? ";
    private static final String SQL_QUERY_SELECT_MAX_ORDER = " SELECT max( link_order ) FROM link_list_portlet WHERE id_portlet= ? ";
    private static final String SQL_QUERY_SELECT_PORTLET_MAX_ORDER = " SELECT max( portlet_link_order ) FROM link_portlet ";
    private static final String SQL_QUERY_UPDATE_LINK_ORDER = " UPDATE link_list_portlet SET link_order = ? WHERE id_portlet = ? AND id_link = ? ";
    private static final String SQL_QUERY_SELECT_LINK_ID_BY_ORDER = " SELECT id_link FROM link_list_portlet WHERE id_portlet = ? AND link_order = ? ";
    private static final String SQL_QUERY_SELECT_UNSELECTED_PORTLET = " SELECT a.id_portlet, a.name FROM core_portlet a " +
        " LEFT JOIN link_portlet b ON a.id_portlet=b.id_portlet WHERE b.id_portlet is NULL " +
        " AND a.id_portlet_type= ? ";
    private static final String SQL_QUERY_SELECT_PORTLET_LINK_PAGE = " SELECT a.id_portlet, a.portlet_link_order, b.name " +
        " FROM link_portlet a, core_portlet b WHERE a.portlet_link_order > -1 " +
        " AND a.id_portlet=b.id_portlet ORDER BY a.portlet_link_order ";
    private static final String SQL_QUERY_SELECT_PORTLET_LINK_ORDER = " SELECT portlet_link_order FROM link_portlet WHERE id_portlet = ? ";
    private static final String SQL_QUERY_DELETE_PORTLET = " DELETE FROM link_portlet WHERE id_portlet= ? ";
    private static final String SQL_QUERY_DELETE_LINK_FROM_PORTLET = " DELETE FROM link_list_portlet WHERE id_link= ? ";
    private static final String SQL_QUERY_INSERT_INTO_PORTLET = " INSERT INTO link_portlet ( id_portlet, portlet_link_order ) VALUES ( ? , ? ) ";
    private static final String SQL_QUERY_SELECT_PORTLET_ID = " SELECT id_portlet FROM link_portlet WHERE portlet_link_order = ? ";
    private static final String SQL_QUERY_UPDATE_PORTLET_LINK = " UPDATE link_portlet SET portlet_link_order = ? WHERE id_portlet = ? ";
    private static final String SQL_QUERY_SELECT_URL_LIST = " SELECT virtual_host_key, url FROM link_virtual_host WHERE id_link = ? ";

    ///////////////////////////////////////////////////////////////////////////////////////
    //Access methods to data

    /**
     * Insert a new record in the table.
     *
     * @param portlet The identifier of the portlet
     */
    public void insert( Portlet portlet )
    {
    }

    /**
     * Insert a new record in the table.
     *
     * @param nPortletId The identifier of the portlet
     * @param nLinkId The identifier of the link
     * @param nOrder The order of the portlet to insert
     */
    public void insertLink( int nPortletId, int nLinkId, int nOrder )
    {
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.setInt( 2, nLinkId );
            daoUtil.setInt( 3, nOrder );

            daoUtil.executeUpdate(  );
        }
    }

    /**
     * Remove a specified link from a specified portlet
     *
     * @param nPortletId The identifier of the portlet
     * @param nLinkId The identifier of the link
     */
    public void deleteLink( int nPortletId, int nLinkId )
    {
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINK ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.setInt( 2, nLinkId );

            daoUtil.executeUpdate(  );
        }
    }

    /**
     * Delete a record from the table
     * @param nPortletId The identifier of the portlet
     */
    public void delete( int nPortletId )
    {
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.executeUpdate(  );
        }

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINK_PORTLET ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.executeUpdate(  );
        }
    }

    /**
     * Gets the data from database
     * @param nPortletId The identifier of the portlet
     * @return portlet The instance of the object portlet
     */
    public Portlet load( int nPortletId )
    {
        LinksPortletnks/business/portlet/LinksPortlet.html#LinksPortlet">LinksPortlet portlet = new LinksPortlet(  );

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.executeQuery(  );

            if ( daoUtil.next(  ) )
            {
                portlet.setId( daoUtil.getInt( 1 ) );
            }
        }

        return portlet;
    }

    /**
     * Update the record in the table
     * @param portlet The instance of the object portlet
     */
    public void store( Portlet portlet )
    {
    }

    /**
     * Returns a list of all the links
     * @return A list of links in form of a ReferenceList object
     */
    public ReferenceList selectLinksList(  )
    {
        ReferenceList list = new ReferenceList(  );

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LINK ) )
        {
            daoUtil.executeQuery(  );

            while ( daoUtil.next(  ) )
            {
                list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) + " " + daoUtil.getString( 3 ) );
            }
        }

        return list;
    }

    /**
     * Check if a specified links is not already registered in a specified portlet
     * @param nPortletId The identifier of the portlet
     * @param nLinkId The identifier of the link
     * @return The result(boolean)
     */
    public boolean testDuplicate( int nPortletId, int nLinkId )
    {
        boolean bResult;

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ID_LINK ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.setInt( 2, nLinkId );
            daoUtil.executeQuery(  );

            bResult = daoUtil.next(  );
        }

        return bResult;
    }

    /**
     * Return a list of links wich belong to a specified portlet
     *
     * @param nPortletId The identifier of the portlet
     * @return A collection of links objects
     */
    public Collection<Link> selectLinksInPortletList( int nPortletId )
    {
        ArrayList<Link> list = new ArrayList<>(  );

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LINK_IN_PORTLET_LIST ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.executeQuery(  );

            while ( daoUtil.next(  ) )
            {
                Link/plugins/links/business/Link.html#Link">Link link = new Link(  );
                link.setId( daoUtil.getInt( 1 ) );
                link.setName( daoUtil.getString( 2 ) );
                link.setUrl( daoUtil.getString( 3 ) );
                link.setDescription( daoUtil.getString( 4 ) );
                link.setImageContent( daoUtil.getBytes( 5 ) );
                link.setMimeType( daoUtil.getString( 6 ) );
                link.setWorkgroupKey( daoUtil.getString( 7 ) );
                link.setOptionalUrls( this.selectUrlsList( link.getId(  ) ) );
                list.add( link );
            }
        }

        return list;
    }

    /**
     * Return the order of a specified link in a specified portlet
     *
     * @param nPortletId The identifier of the portlet
     * @param nLinkId The identifier of the link
     * @return The link's order
     */
    public int selectLinkOrder( int nPortletId, int nLinkId )
    {
        int nOrder = 0;

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LINK_ORDER ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.setInt( 2, nLinkId );
            daoUtil.executeQuery(  );

            if ( daoUtil.next(  ) )
            {
                nOrder = daoUtil.getInt( 1 );
            }
        }

        return nOrder;
    }

    /**
     * Calculate a new primary key to add a new link
     * @param nPortletId The identifier of the portlet
     * @return The new key.
     */
    public int selectMaxOrder( int nPortletId )
    {
        int nOrder = 0;

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_MAX_ORDER ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.executeQuery(  );

            if ( daoUtil.next(  ) )
            {
                nOrder = daoUtil.getInt( 1 );
            }
        }

        return nOrder;
    }

    /**
     * Returns the maximum order of the portlets in the links page
     * @return the max order
     */
    public int selectPortletMaxOrder(  )
    {
        int nOrder = 0;

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_MAX_ORDER ) )
        {
            daoUtil.executeQuery(  );

            if ( daoUtil.next(  ) )
            {
                nOrder = daoUtil.getInt( 1 );
            }
        }

        return nOrder;
    }

    /**
     * Update the order of a specified link in a specified portlet
     *
     * @param nPortletId The identifier of the portlet
     * @param nLinkId The identifier of the link
     * @param nOrder The new order
     */
    public void storeLinkOrder( int nOrder, int nPortletId, int nLinkId )
    {
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_LINK_ORDER ) )
        {
            daoUtil.setInt( 1, nOrder );
            daoUtil.setInt( 2, nPortletId );
            daoUtil.setInt( 3, nLinkId );

            daoUtil.executeUpdate(  );
        }
    }

    /**
     * Returns the id of an link wich has a specified order in a specified portlet
     * @param nPortletId The identifier of the portlet
     * @param nOrder The link's order
     * @return The identifier of the link
     */
    public int selectLinkIdByOrder( int nPortletId, int nOrder )
    {
        int nResult = 0;

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_LINK_ID_BY_ORDER ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.setInt( 2, nOrder );
            daoUtil.executeQuery(  );

            if ( daoUtil.next(  ) )
            {
                nResult = daoUtil.getInt( 1 );
            }
        }

        return nResult;
    }

    /**
     * Finds the portlets which have not been selected in the links page
     * @return the list of the unselected portlets
     */
    public ReferenceList findUnselectedPortlets(  )
    {
        ReferenceList list = new ReferenceList(  );
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_UNSELECTED_PORTLET ) )
        {
            String strPortletTypeId = LinksPortletHome.getInstance(  ).getPortletTypeId(  );

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

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

        return list;
    }

    /**
     * Selects the list of the portlets in the links page
     *
     * @return a collection of the unselected portlets
     */
    public Collection<Portlet> selectPortletsInLinksPage(  )
    {
        ArrayList<Portlet> list = new ArrayList<>(  );
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LINK_PAGE ) )
        {
            daoUtil.executeQuery(  );

            while ( daoUtil.next(  ) )
            {
                LinksPortletnks/business/portlet/LinksPortlet.html#LinksPortlet">LinksPortlet portlet = new LinksPortlet(  );
                portlet.setId( daoUtil.getInt( 1 ) );
                portlet.setPortletOrder( daoUtil.getInt( 2 ) );
                portlet.setName( daoUtil.getString( 3 ) );
                list.add( portlet );
            }
        }

        return list;
    }

    /**
     * Selects the order of a portlet in the links page
     * @param nPortletId The identifier of the portlet
     * @return the order
     */
    public int selectPortletOrder( int nPortletId )
    {
        int nOrder = 0;

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_LINK_ORDER ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.executeQuery(  );

            if ( daoUtil.next(  ) )
            {
                nOrder = daoUtil.getInt( 1 );
            }
        }

        return nOrder;
    }

    /**
     * Remove a portlet from the links page
     * @param nPortletId The identifier of the portlet
     */
    public void removePortlet( int nPortletId )
    {
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PORTLET ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.executeUpdate(  );
        }
    }

    /**
     * Removes a link from all the portlets
     * @param nLinkId The identifier of the link
     */
    public void removeLinkFromPortlets( int nLinkId )
    {
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_LINK_FROM_PORTLET ) )
        {
            daoUtil.setInt( 1, nLinkId );
            daoUtil.executeUpdate(  );
        }
    }

    /**
     * Insert a new portlet in the links page
     * @param nPortletId The identifier of the portlet
     * @param nOrder The order of the portlet
     */
    public void insertPortlet( int nPortletId, int nOrder )
    {
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_INTO_PORTLET ) )
        {
            daoUtil.setInt( 1, nPortletId );
            daoUtil.setInt( 2, nOrder );
            daoUtil.executeUpdate(  );
        }
    }

    /**
     * Selects a portlet Id from the links page by its order
     * @param nOrder The order of the portlet
     * @return the portlet Id
     */
    public int selectPortletIdByOrder( int nOrder )
    {
        int nResult = 0;

        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PORTLET_ID ) )
        {
            daoUtil.setInt( 1, nOrder );
            daoUtil.executeQuery(  );

            if ( daoUtil.next(  ) )
            {
                nResult = daoUtil.getInt( 1 );
            }
        }

        return nResult;
    }

    /**
     * Stores the order of a portlet in the links page

     * @param nOrder The order of the portlet
     * @param nPortletId The identifier of the portlet
     */
    public void storePortletOrder( int nOrder, int nPortletId )
    {
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_PORTLET_LINK ) )
        {
            daoUtil.setInt( 1, nOrder );
            daoUtil.setInt( 2, nPortletId );
            daoUtil.executeUpdate(  );
        }
    }

    /**
     * load all the optional urls
     *
     * @param idLink the link's id
     * @return the optional urls ReferenceList
     */
    private ReferenceList selectUrlsList( int idLink )
    {
        ReferenceList list = new ReferenceList(  );
        try( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_URL_LIST ) )
        {
            daoUtil.setInt( 1, idLink );
            daoUtil.executeQuery(  );

            while ( daoUtil.next(  ) )
            {
                list.addItem( daoUtil.getString( 1 ), daoUtil.getString( 2 ) );
            }
        }

        return list;
    }
}