D-Net

package eu.dnetlib.data.claims.migration.handler;

import eu.dnetlib.data.claims.migration.JsonldBuilder;

import eu.dnetlib.data.claims.migration.entity.*;

import eu.dnetlib.data.claimsDemo.ClaimUtils;

import eu.dnetlib.data.claimsDemo.QueryGenerator;

import eu.dnetlib.data.claimsDemo.SQLStoreException;

import eu.dnetlib.data.claimsDemo.SqlDAO;

import org.apache.log4j.Logger;

import java.sql.Array;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

/**

 * Created by argirok on 9/3/2016.

 */

public class FetchClaimHandler {

    private Logger log = Logger.getLogger(this.getClass());

    SqlDAO sqlDAO = null;

    QueryGenerator queryGenerator = null;

    public FetchClaimHandler(){

//        ApplicationContext context = new ClassPathXmlApplicationContext("../claims/migration/springContext-claimsDemo.xml");

//        sqlDAO = context.getBean(SqlDAO.class);

    }

    public String claims2JSON(List<Claim> claims) throws Exception {

        return JsonldBuilder.toJsonld(claims);

    }

    public String claim2JSON(Claim claim) throws Exception {

        return JsonldBuilder.toJsonld(claim);

    }

    /**

     *

     * @param user - user e-mail stored in claims DB

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByUser(String user, Integer limit, Integer offset, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {

        return fetchClaimsByUser(user,limit,offset,null,"claim.claim_date",true, types, addCurationInfo);

    }

    /**

     *

     * @param user - user e-mail stored in claims DB

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @param keyword filters in specific fields per type (result: {title,doi},project: {name.acronym. funder_name, funder_acronym}, context: {name})

     * @param orderField

     * @param descending

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByUser(String user, Integer limit, Integer offset,String keyword,String orderField,boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {

        log.info("Fetching claims for user"+user);

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateFetchClaimsByUser(user, limit, offset,keyword,orderField,descending, types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return fetchClaimsByResultSet(rs, addCurationInfo);

    }

    /**

     *

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @return

     * @throws Exception

     */

    public List<Claim> fetchAllClaims( Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {

        log.info("Fetching all claims");

         return fetchAllClaims(limit,offset,null,"claim.claim_date",true,new ArrayList<String>(), addCurationInfo);

    }

    /**

     *

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @param keyword  filters in specific fields per type (result: {title,doi},project: {name.acronym. funder_name, funder_acronym}, context: {name})

     * @param orderField

     * @param descending

     * @return

     * @throws Exception

     */

    public List<Claim> fetchAllClaims( Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {

        log.info("Fetching all claims");

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateFetchClaims(limit, offset,keyword,orderField,descending,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return fetchClaimsByResultSet(rs, addCurationInfo);

    }

    /**

     *

     * @param dateFrom

     * @param dataTo

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByDate(String dateFrom, String dataTo, Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {

         return fetchClaimsByDate(dateFrom, dataTo, limit, offset, null, "claim.claim_date",true, new ArrayList<String>(),addCurationInfo);

    }

    /**

     *

     * @param dateFrom

     * @param dataTo

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByDate(String dateFrom, String dataTo, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateFetchClaimsByDate(dateFrom, dataTo, limit, offset,keyword, orderField, descending,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return fetchClaimsByResultSet(rs, addCurationInfo);

    }

    /**

     *

     * @param dateFrom

     * @param dataTo

     * @param openaireId for project

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @return

     * @throws Exception

     */

    public int fetchNumberOfClaimsByDateAndOpenaireId(String dateFrom, String dataTo, String openaireId, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateFetchNumberOfClaimsByDateAndOpenaireId(dateFrom, dataTo, openaireId, limit, offset,keyword, orderField, descending,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return countClaimsByResultSet(rs);

    }

    /**

     *

     * @param openaireId for project

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByProject(String openaireId, Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {

        return fetchClaimsByProject(openaireId,limit, offset, null, "claim.claim_date",true, new ArrayList<String>(), addCurationInfo);

    }

    /**

     *

     * @param openaireId for project

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @param orderField

     * @param descending

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByProject(String openaireId, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {

        log.info("Fetching claims by project ID:"+openaireId);

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateFetchClaimsByProject(openaireId, limit, offset, keyword, orderField,descending,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return fetchClaimsByResultSet(rs, addCurationInfo);

    }

    /**

     *

     * @param openaireId of Funder

     * @param limit

     * @param offset

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByFunder(String openaireId, Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {

         return fetchClaimsByFunder(openaireId,limit,offset,null,"claim.claim_date",true, new ArrayList<String>(), addCurationInfo);

    }

    /**

     *

     * @param openaireId of Funder

     * @param limit

     * @param offset

     * @param orderField

     * @param descending

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByFunder(String openaireId, Integer limit, Integer offset,String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateFetchClaimsByFunder(openaireId, limit, offset,keyword, orderField,descending,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return fetchClaimsByResultSet(rs, addCurationInfo);

    }

    /**

     *

     * @param openaireId for result

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByResult(String openaireId, Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {

         return fetchClaimsByResult(openaireId,limit,offset,null, "claim.claim_date",true, new ArrayList<String>(), addCurationInfo);

    }

    /**

     *

     * @param openaireId for result

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @param orderField

     * @param descending

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByResult(String openaireId, Integer limit, Integer offset,String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {

        log.info("Fetching claims by result ID:"+openaireId);

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateFetchClaimsByResult(openaireId, limit, offset,keyword, orderField, descending,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return fetchClaimsByResultSet(rs, addCurationInfo);

    }

    /**

     *

     * @param openaireId

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByContext(String openaireId, Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {

         return fetchClaimsByContext(openaireId,limit,offset,null,"claim.claim_date",true, new ArrayList<String>(), addCurationInfo);

    }

    /**

     *

     * @param openaireId

     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)

     * @param offset returns claims after 'offset' position (null for no offset)

     * @param orderField

     * @param descending

     * @return

     * @throws Exception

     */

    public List<Claim> fetchClaimsByContext(String openaireId, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {

        log.info("Fetching claims by context ID:"+openaireId);

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateFetchClaimsByContext(openaireId, limit, offset,keyword,orderField, descending,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return fetchClaimsByResultSet(rs, addCurationInfo);

    }

    public Claim fetchClaimById(String claimId, boolean addCurationInfo) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateSelectClaimQuery(claimId, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        if(rs.next()) {

            String sourceType =rs.getString(2);

//            String sourceId =rs.getString(3);

            String targetType =rs.getString(4);

//            String targetId =rs.getString(5);

            ArrayList<Object> params2 = new ArrayList<>();

            String query2 = queryGenerator.generateFetchClaimsByClaimId(claimId,sourceType,targetType, params2);

           rs = sqlDAO.executePreparedQuery(query2, params2);

        }else{

            log.info("No claim with id : "+ claimId+"\n");

        }

        Claim claim = null;

        List<Claim> claims=fetchClaimsByResultSet(rs, addCurationInfo);

        if(claims.size()==1){

            claim=claims.get(0);

        }

        return claim;

    }

    /*

    public List<Claim> fetchClaimsByToken(String token, String email, Integer limit, Integer offset, boolean addCurationInfo) throws Exception {

        return fetchClaimsByToken(token, email, limit, offset, null, "claim.claim_date",true, new ArrayList<String>(), addCurationInfo);

    }

    public List<Claim> fetchClaimsByToken(String token, String email, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception {

        ResultSet rs = sqlDAO.executePreparedQuery(queryGenerator.generateFetchClaimsByProjectToken(token, email, limit, offset, keyword, orderField,descending,types));

        return fetchClaimsByResultSet(rs, addCurationInfo);

    }

    */

    public List<Claim> fetchClaimsByResultSet(ResultSet rs, boolean addCurationInfo) throws Exception {

        List<Claim> claims= new ArrayList<Claim>();

        while(rs.next()) {

            Claim claim= new Claim();

            claims.add(claim);

            claim.setId(rs.getString(1));

            DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Date date = format.parse(rs.getString(2));

            claim.setDate(date);

            claim.setUserMail(rs.getString(3));

            claim.setSourceType(rs.getString(4));

            claim.setTargetType(rs.getString(5));

            claim.setSemantics(rs.getString(6));

            if(addCurationInfo) {

                String curationDate =rs.getString(7);

                if(curationDate != null) {

                    date = format.parse(curationDate);

                    claim.setCurationDate(date);

                }

                claim.setCuratedBy(rs.getString(8));

            }

            claim.setApproved(rs.getBoolean(9));

            claim.setSource(buildEntity(rs,10,claim.getSourceType()));

            claim.setTarget(buildEntity(rs,20,claim.getTargetType()));

        }

        return claims;

    }

    public Integer countClaimsByResultSet(ResultSet rs) throws Exception {

        while(rs.next()) {

            return rs.getInt(1);

        }

        return null;

    }

    public List<Project> getProjectsByResultSet(ResultSet rs, boolean addCurationInfo) throws Exception {

        List<Project> projects= new ArrayList<Project>();

        while(rs.next()) {

            Project project=(Project)(buildEntity(rs, 1, ClaimUtils.PROJECT));

             projects.add(project);

        }

        return projects;

    }

    public List<Context> getContextsByResultSet(ResultSet rs, boolean addCurationInfo) throws Exception {

        List<Context> contexts= new ArrayList<Context>();

        while(rs.next()) {

            Context context=(Context)(buildEntity(rs, 1, ClaimUtils.CONTEXT));

            contexts.add(context);

        }

        return contexts;

    }

    public Integer countAllClaims(String keyword, List<String> types) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateCountAllClaims(keyword,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return countClaimsByResultSet(rs);

    }

    public Integer countClaimsByUser(String user,String keyword, List<String> types) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateCountByUser(user,keyword,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return countClaimsByResultSet(rs);

    }

    public Integer countClaimsByProject(String projectId,String keyword, List<String> types) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateCountByProject(projectId,keyword,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return countClaimsByResultSet(rs);

    }

    /*

    public Integer countClaimsByProjectToken(String projectToken,String email,String keyword, List<String> types)throws Exception {

        ResultSet rs = sqlDAO.executePreparedQuery(queryGenerator.generateCountByProjectToken(projectToken,email,keyword,types));

        return countClaimsByResultSet(rs);

    }

    */

    public Integer countClaimsByFunder(String funderId,String keyword, List<String> types) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateCountByFunder(funderId,keyword,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return countClaimsByResultSet(rs);

    }

    public Integer countClaimsByResult(String resultId,String keyword, List<String> types) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateCountByResult(resultId,keyword,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return countClaimsByResultSet(rs);

    }

    public Integer countClaimsByContext(String contextId,String keyword, List<String> types) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateCountByContext(contextId,keyword,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return countClaimsByResultSet(rs);

    }

    public Integer countClaimsByDate(String dateFrom,String dateTo,String keyword, List<String> types) throws Exception, SQLStoreException {

        ArrayList<Object> params = new ArrayList<>();

        String query = queryGenerator.generateCountByDate(dateFrom,dateTo,keyword,types, params);

        ResultSet rs = sqlDAO.executePreparedQuery(query, params);

        return countClaimsByResultSet(rs);

    }

    public List<Project> getAllclaimedProjects(boolean addCurationInfo) throws Exception, SQLStoreException {

        ResultSet rs = sqlDAO.executePreparedQuery(queryGenerator.generateFetchAllProjectsQuery());

        return getProjectsByResultSet(rs,addCurationInfo);

    }

    public List<Context> getAllclaimedContexts( boolean addCurationInfo) throws Exception, SQLStoreException {

        ResultSet rs = sqlDAO.executePreparedQuery(queryGenerator.generateFetchAllContextsQuery());

        return getContextsByResultSet(rs,addCurationInfo);

    }

    /**

     *

     * @param rs

     * @param index

     * @return

     * @throws SQLException

     */

    private static Result buildResult(ResultSet rs, Integer index) throws SQLException {

        Result result = new Result();

        result.setOpenaireId(rs.getString(index));

        result.setTitle(rs.getString(index + 1));

        result.setResultType(rs.getString(index + 2));

        result.setDoi(rs.getString(index + 3));

        result.setOrcidworkid(rs.getString(index + 4));

        result.setAccessRights(rs.getString(index + 5));

        result.setEmbargoEndDate(rs.getString(index + 6));

        result.setBestLicense(rs.getString(index + 7));

        result.setExternalUrl(rs.getString(index + 8));

        result.setCollectedFrom(rs.getString(index + 9));

        return result;

    }

    private static Project buildProject(ResultSet rs, Integer index) throws SQLException {

        Project project = new Project();

        project.setOpenaireId(rs.getString(index));

        project.setName(rs.getString(index + 1));

        project.setAcronym(rs.getString(index + 2));

        project.setFunderId(rs.getString(index + 3));

        project.setFunderName(rs.getString(index + 4));

        return project;

    }

    private static Context buildContext(ResultSet rs, Integer index) throws SQLException {

        Context context = new Context();

        context.setOpenaireId(rs.getString(index));

        context.setTitle(rs.getString(index + 1));

        return context;

    }

    private static OpenaireEntity buildEntity(ResultSet rs, Integer index, String type) throws SQLException {

        OpenaireEntity openaireEntity = null;

        if(type == null){

            openaireEntity = null;

        }else if(type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET)||type.equals(ClaimUtils.SOFTWARE)||type.equals(ClaimUtils.OTHER)) {

            openaireEntity= buildResult(rs, index);

        }else if(type.equals(ClaimUtils.PROJECT)){

            openaireEntity = buildProject(rs,index);

        }else if(type.equals(ClaimUtils.CONTEXT)){

            openaireEntity = buildContext(rs,index);

        }

        return openaireEntity;

    }

    public SqlDAO getSqlDAO() {

        return sqlDAO;

    }

    public void setSqlDAO(SqlDAO sqlDAO) {

        this.sqlDAO = sqlDAO;

    }

    public QueryGenerator getQueryGenerator() {

        return queryGenerator;

    }

    public void setQueryGenerator(QueryGenerator queryGenerator) {

        this.queryGenerator = queryGenerator;

    }

}