QuserJdbcDaoImpl

package fr.ifremer.quadrige3.core.dao.administration.user;

/*-
 * #%L
 * Quadrige3 Core :: Quadrige3 Client Core
 * $Id:$
 * $HeadURL:$
 * %%
 * Copyright (C) 2017 Ifremer
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * #L%
 */

import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import fr.ifremer.quadrige3.core.dao.referential.StatusCode;
import fr.ifremer.quadrige3.core.dao.technical.Assert;
import fr.ifremer.quadrige3.core.dao.technical.jdbc.OptionalDataSourceJdbcDaoSupport;
import fr.ifremer.quadrige3.core.exception.QuadrigeTechnicalException;
import fr.ifremer.quadrige3.core.vo.administration.program.ProgramVO;
import fr.ifremer.quadrige3.core.vo.administration.user.LightQuserVO;
import fr.ifremer.quadrige3.core.vo.administration.user.PrivilegeVO;
import fr.ifremer.quadrige3.core.vo.administration.user.QuserVO;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * <p>
 * QuserJdbcDaoImpl class.
 * </p>
 *
 * @see fr.ifremer.quadrige3.core.dao.administration.user.Quser
 */
@Repository("quserJdbcDao")
@Lazy
public class QuserJdbcDaoImpl
		extends OptionalDataSourceJdbcDaoSupport
		implements QuserJdbcDao, InitializingBean {

	private final static String QUERIES_FILE_PATH = "queries.jdbc.xml";

	@Resource(name = "queriesJdbcProperties")
	private Properties queriesJdbcProperties;

	protected Properties connectionProperties;

	/**
	 * Constructor used by Spring
	 *
	 * @param dataSource
	 *            a {@link javax.sql.DataSource} object.
	 */
	@Autowired
	public QuserJdbcDaoImpl(DataSource dataSource) {
		super(dataSource);
	}

	/**
	 * Constructor without Spring (e.g. for synchro)
	 */
	public QuserJdbcDaoImpl() {
		this((Properties) null/* use default connection properties */);
	}

	/**
	 * Constructor without Spring (e.g. for synchro)
	 *
	 * @param connectionProperties
	 *            a {@link java.util.Properties} object.
	 */
	public QuserJdbcDaoImpl(Properties connectionProperties) {
		super();
		this.connectionProperties = connectionProperties;

		// Load properties
		Properties properties = new Properties();
		InputStream is = null;
		try {
			is = getClass().getClassLoader().getResourceAsStream(QUERIES_FILE_PATH);
			properties.loadFromXML(is);

			this.queriesJdbcProperties = properties;
		} catch (IOException e) {
			throw new QuadrigeTechnicalException(
					String.format("Unable to read file [%s] from classpath", QUERIES_FILE_PATH),
					e);
		} finally {
			IOUtils.closeQuietly(is);
		}

		// Check all queries
		checkAllQueriesExists();
	}

	/** {@inheritDoc} */
	@Override
	public void afterPropertiesSet() {
		// Check queries exists on queries file
		checkAllQueriesExists();
	}

	/** {@inheritDoc} */
	@Override
	public Integer getUserIdByUsername(String username) {
		return getUserIdByUsername(connectionProperties, username);
	}

	/** {@inheritDoc} */
	@Override
	public Integer getUserIdByUsername(Properties connectionProperties, String username) {
		// Find the user, by id
		String sql = queriesJdbcProperties.getProperty("quserIdByLoginAndStatus");

		Map<String, Object> paramMap = Maps.newHashMap();
		paramMap.put("username", username);
		paramMap.put("statusCd", StatusCode.ENABLE.getValue());

		return query(connectionProperties, sql, paramMap, rs -> {
            Object value = rs.getObject(1);
            if (value == null) {
                return null;
            }

            // Oracle DBMS return a BigDecimal from NUMBER(10) columns
            if (value instanceof BigDecimal) {
                return ((BigDecimal) value).intValue();
            }

            // for all other java types: convert into Integer
            return Integer.parseInt(value.toString());
        });
	}

	/** {@inheritDoc} */
	@Override
	public LightQuserVO getLightUserById(int personId) {
		return getLightUserById(connectionProperties, personId);
	}

	/** {@inheritDoc} */
	@Override
	public LightQuserVO getLightUserById(Properties connectionProperties, int quserId) {
		// Find the user, by id
		String sql = queriesJdbcProperties.getProperty("lightQuserById");

		Map<String, Object> paramMap = Maps.newHashMap();
		paramMap.put("quserId", quserId);

		return query(connectionProperties, sql, paramMap, this::toLightQuserVO);
	}

	/** {@inheritDoc} */
	@Override
	public List<LightQuserVO> getUsersByIds(List<Integer> ids) {
		return getUsersByIds(connectionProperties, ids);
	}

	/** {@inheritDoc} */
	@Override
	public List<LightQuserVO> getUsersByIds(Properties connectionProperties, List<Integer> ids) {
		// Find users, by ids
		String sql = queriesJdbcProperties.getProperty("lightQusersByIds");
		sql = sql.replace(":ids", Joiner.on(",").skipNulls().join(ids));

		Map<String, Object> paramMap = Maps.newHashMap();

		return query(connectionProperties, sql, paramMap, (rs, rowNum) -> toLightQuserVO(rs));
	}

	/** {@inheritDoc} */
	@Override
	public QuserVO getUserById(int userId) {
		return getUserById(connectionProperties, userId);
	}

	/** {@inheritDoc} */
	@Override
	public QuserVO getUserById(Properties connectionProperties, int quserId) {
		// Find the user, by id
		String sql = queriesJdbcProperties.getProperty("quserById");

		Map<String, Object> paramMap = Maps.newHashMap();
		paramMap.put("quserId", quserId);

		return query(connectionProperties, sql, paramMap, this::toQuserVO);
	}

	/** {@inheritDoc} */
	@Override
	public List<PrivilegeVO> getPrivilegesByUserId(int userId) {
		return getPrivilegesByUserId(connectionProperties, userId);
	}

	/** {@inheritDoc} */
	@Override
	public List<PrivilegeVO> getPrivilegesByUserId(Properties connectionProperties, int userId) {
		// Find the user, by id
		String sql = queriesJdbcProperties.getProperty("privilegesByQuserId");

		Map<String, Object> paramMap = Maps.newHashMap();
		paramMap.put("quserId", userId);

		final List<PrivilegeVO> result = Lists.newArrayList();

		// Execute the query, and fill the result list
		query(connectionProperties, sql, paramMap, source -> {
            PrivilegeVO target = toPrivilegeVO(source);
            result.add(target);
        });

		return result;
	}

	/* -- Internal methods -- */

	/**
	 * <p>
	 * toLightQuserVO.
	 * </p>
	 *
	 * @param source
	 *            a {@link java.sql.ResultSet} object.
	 * @return a {@link fr.ifremer.quadrige3.core.vo.administration.user.LightQuserVO} object.
	 * @throws java.sql.SQLException
	 *             if any.
	 */
	protected LightQuserVO toLightQuserVO(ResultSet source) throws SQLException {
		int row = 0;
		return new LightQuserVO(
				safeGetInteger(source, ++row),
				source.getString(++row),
				source.getString(++row));
	}

	/**
	 * <p>
	 * toQuserVO.
	 * </p>
	 *
	 * @param source
	 *            a {@link java.sql.ResultSet} object.
	 * @return a {@link fr.ifremer.quadrige3.core.vo.administration.user.QuserVO} object.
	 * @throws java.sql.SQLException
	 *             if any.
	 */
	protected QuserVO toQuserVO(ResultSet source) throws SQLException {
		QuserVO target = new QuserVO();

		int row = 0;
		target.setQuserId(safeGetInteger(source, ++row));
		target.setQuserLastNm(source.getString(++row));
		target.setQuserFirstNm(source.getString(++row));
		target.setQuserAddress(source.getString(++row));
		target.setQuserCreationDt(source.getDate(++row));
		target.setUpdateDt(source.getTimestamp(++row));
		target.setStatusCd(source.getString(++row));
		target.setDepId(safeGetInteger(source, ++row));
		target.setQuserIntranetLg(source.getString(++row));
		target.setQuserExtranetLg(source.getString(++row));
		target.setQuserEMail(source.getString(++row));
		target.setQuserPhone(source.getString(++row));
		target.setQuserOrgan(source.getString(++row));
		target.setQuserAdminCenter(source.getString(++row));
		target.setQuserSite(source.getString(++row));
		target.setQuserLdapPresent(source.getString(++row));

		return target;
	}

	/**
	 * <p>
	 * toProgramVO.
	 * </p>
	 *
	 * @param source
	 *            a {@link java.sql.ResultSet} object.
	 * @return a {@link fr.ifremer.quadrige3.core.vo.administration.program.ProgramVO} object.
	 * @throws java.sql.SQLException
	 *             if any.
	 */
	protected ProgramVO toProgramVO(ResultSet source) throws SQLException {
		ProgramVO target = new ProgramVO();

		int row = 0;
		target.setProgCd(source.getString(++row));
		target.setProgNm(source.getString(++row));

		return target;
	}

	/**
	 * <p>
	 * toPrivilegeVO.
	 * </p>
	 *
	 * @param source
	 *            a {@link java.sql.ResultSet} object.
	 * @return a {@link fr.ifremer.quadrige3.core.vo.administration.user.PrivilegeVO} object.
	 * @throws java.sql.SQLException
	 *             if any.
	 */
	protected PrivilegeVO toPrivilegeVO(ResultSet source) throws SQLException {
		PrivilegeVO target = new PrivilegeVO();

		int row = 0;
		target.setPrivilegeCd(source.getString(++row));
		target.setPrivilegeNm(source.getString(++row));
		target.setPrivilegeDc(source.getString(++row));
		target.setStatusCd(source.getString(++row));
		target.setUpdateDt(source.getTimestamp(++row));

		return target;
	}

	/**
	 * Check queries exists on queries file
	 */
	protected void checkAllQueriesExists() {
		Assert.notNull(queriesJdbcProperties);

		checkQueryExists("quserIdByLoginAndStatus");
		checkQueryExists("lightQuserById");
		checkQueryExists("quserById");
		checkQueryExists("privilegesByQuserId");
	}

	/**
	 * Check if a query exists on the queries properties file
	 *
	 * @param queryName
	 *            a {@link java.lang.String} object.
	 */
	protected void checkQueryExists(String queryName) {
		if (StringUtils.isBlank(queriesJdbcProperties.getProperty(queryName))) {
			throw new QuadrigeTechnicalException(String.format("Property with name [%s] not exists on JDBC queries file", queryName));
		}
	}
}