<commit_before>package org.grobid.core.utilities;

import java.io.InputStream;
import java.io.StringReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.StringTokenizer;

import org.apache.commons.lang3.StringUtils;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.grobid.core.data.BiblioItem;
import org.grobid.core.exceptions.GrobidException;
import org.grobid.core.sax.CrossrefUnixrefSaxParser;
import org.xml.sax.InputSource;
import org.xml.sax.helpers.DefaultHandler;

/**
 * Class for managing the extraction of bibliographical informations from pdf documents.
 *
 * @author <NAME>
 */
public class Consolidation {

    // Cache crossref BD (allowed by the user crossref service agreement)
    private Connection cCon = null;
  
    public Consolidation() {
    }

    /**
     * Open database connection
     */
    public void openDb() throws ClassNotFoundException,
            InstantiationException,
            IllegalAccessException,
            SQLException {
        // compose database url: jdbc:mysql://<hostname>:<port>/<database>
        String dbUrl2 = "jdbc:mysql://"
                + GrobidProperties.getInstance().getMySQLHost()
                + ":"
                + GrobidProperties.getInstance().getMySQLPort()
                + "/" + GrobidProperties.getInstance().getMySQLDBName() + "?useUnicode=true&characterEncoding=utf8";

        // instantiate mysql driver manager, get database connection
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            cCon = DriverManager.getConnection(dbUrl2, GrobidProperties.getInstance().getMySQLUsername(), GrobidProperties.getInstance().getMySQLPw());
            if (cCon != null) {
                cCon.createStatement().execute("SET NAMES utf8");
            }
        } catch (Exception e) {
            System.err.println("The connection to the MySQL database could not be established. \n"
                    + "The call to Crossref service will not be cached.");
            //e.printStackTrace();
        }
    }

    /**
     * Close database connection
     */
    public void closeDb() {
        try {
            if (cCon != null) {
                cCon.close();
            }
        } catch (SQLException se) {
        }
    }

	/**
	 *  SQL queries for the DOI cache
	 */
    static final String INSERT_CROSSREF_SQL =
            "INSERT INTO AuthorTitle (Author, Title, Unixref) VALUES (?,?,?)";
    static final String INSERT_CROSSREF_SQL2 =
            "INSERT INTO AllSubFields (Request, Unixref) VALUES (?,?)";
    static final String INSERT_CROSSREF_SQL3 =
            "INSERT INTO DOIRequest (Request, Unixref) VALUES (?,?)";

    static final String QUERY_CROSSREF_SQL =
            "SELECT Unixref FROM AuthorTitle WHERE Author LIKE ? AND Title LIKE ?";
    static final String QUERY_CROSSREF_SQL2 =
            "SELECT Unixref FROM AllSubFields WHERE Request LIKE ?";
    static final String QUERY_CROSSREF_SQL3 =
            "SELECT Unixref FROM DOIRequest WHERE Request DOIRequest ?";

    /**
     * Lookup by DOI - 3 parameters are id, password, doi.
     */
    private static final String DOI_BASE_QUERY =
            "openurl?url_ver=Z39.88-2004&pid=%s:%s&rft_id=info:doi/%s&noredirect=true&format=unixref";

    /**
     * Lookup by journal title, volume and first page - 6 parameters are id, password, journal title, author, volume, firstPage.
     */
    private static final String JOURNAL_AUTHOR_BASE_QUERY =
            //"query?usr=%s&pwd=%s&type=a&format=unixref&qdata=|%s||%s||%s|||KEY|";
		    "query?usr=%s&pwd=%s&type=a&format=unixref&qdata=|%s|%s|%s||%s|||KEY|";

    // ISSN|TITLE/ABBREV|FIRST AUTHOR|VOLUME|ISSUE|START PAGE|YEAR|RESOURCE TYPE|KEY|DOI

     /**
     * Lookup by journal title, volume and first page - 6 parameters are id, password, journal title, volume, firstPage.
     */       
     private static final String JOURNAL_BASE_QUERY =
            //"query?usr=%s&pwd=%s&type=a&format=unixref&qdata=|%s||%s||%s|||KEY|";
            "query?usr=%s&pwd=%s&type=a&format=unixref&qdata=|%s||%s||%s|||KEY|";

    /**
     * Lookup first author surname and  article title - 4 parameters are id, password, title, author.
     */
    private static final String TITLE_BASE_QUERY =
            "query?usr=%s&pwd=%s&type=a&format=unixref&qdata=%s|%s||key|";


	/**
     * Try to consolidate some uncertain bibliographical data with crossref web service based on
     * core metadata
     */
	public boolean consolidate(BiblioItem bib, List<BiblioItem> additionalBiblioInformation) throws Exception {
	 	boolean result = false;
		//List<BiblioItem> additionalBiblioInformation = new ArrayList<Biblio>();
		boolean valid = false;

		String doi = bib.getDOI();
        String aut = bib.getFirstAuthorSurname();
        String title = bib.getTitle();
		String journalTitle = bib.getJournal();
		String volume = bib.getVolume();
        if (StringUtils.isBlank(volume))<commit_msg>Update crossref url<commit_after>package org.grobid.core.utilities;

import java.io.InputStream;
import java.io.StringReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.StringTokenizer;

import org.apache.commons.lang3.StringUtils;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.grobid.core.data.BiblioItem;
import org.grobid.core.exceptions.GrobidException;
import org.grobid.core.sax.CrossrefUnixrefSaxParser;
import org.xml.sax.InputSource;
import org.xml.sax.helpers.DefaultHandler;

/**
 * Class for managing the extraction of bibliographical informations from pdf documents.
 *
 * @author <NAME>
 */
public class Consolidation {

    // Cache crossref BD (allowed by the user crossref service agreement)
    private Connection cCon = null;
  
    public Consolidation() {
    }

    /**
     * Open database connection
     */
    public void openDb() throws ClassNotFoundException,
            InstantiationException,
            IllegalAccessException,
            SQLException {
        // compose database url: jdbc:mysql://<hostname>:<port>/<database>
        String dbUrl2 = "jdbc:mysql://"
                + GrobidProperties.getInstance().getMySQLHost()
                + ":"
                + GrobidProperties.getInstance().getMySQLPort()
                + "/" + GrobidProperties.getInstance().getMySQLDBName() + "?useUnicode=true&characterEncoding=utf8";

        // instantiate mysql driver manager, get database connection
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            cCon = DriverManager.getConnection(dbUrl2, GrobidProperties.getInstance().getMySQLUsername(), GrobidProperties.getInstance().getMySQLPw());
            if (cCon != null) {
                cCon.createStatement().execute("SET NAMES utf8");
            }
        } catch (Exception e) {
            System.err.println("The connection to the MySQL database could not be established. \n"
                    + "The call to Crossref service will not be cached.");
            //e.printStackTrace();
        }
    }

    /**
     * Close database connection
     */
    public void closeDb() {
        try {
            if (cCon != null) {
                cCon.close();
            }
        } catch (SQLException se) {
        }
    }

	/**
	 *  SQL queries for the DOI cache
	 */
    static final String INSERT_CROSSREF_SQL =
            "INSERT INTO AuthorTitle (Author, Title, Unixref) VALUES (?,?,?)";
    static final String INSERT_CROSSREF_SQL2 =
            "INSERT INTO AllSubFields (Request, Unixref) VALUES (?,?)";
    static final String INSERT_CROSSREF_SQL3 =
            "INSERT INTO DOIRequest (Request, Unixref) VALUES (?,?)";

    static final String QUERY_CROSSREF_SQL =
            "SELECT Unixref FROM AuthorTitle WHERE Author LIKE ? AND Title LIKE ?";
    static final String QUERY_CROSSREF_SQL2 =
            "SELECT Unixref FROM AllSubFields WHERE Request LIKE ?";
    static final String QUERY_CROSSREF_SQL3 =
            "SELECT Unixref FROM DOIRequest WHERE Request DOIRequest ?";

    /**
     * Lookup by DOI - 3 parameters are id, password, doi.
     */
    private static final String DOI_BASE_QUERY =
            "openurl?url_ver=Z39.88-2004&pid=%s:%s&rft_id=info:doi/%s&noredirect=true&format=unixref";

    /**
     * Lookup by journal title, volume and first page - 6 parameters are id, password, journal title, author, volume, firstPage.
     */
    private static final String JOURNAL_AUTHOR_BASE_QUERY =
            //"query?usr=%s&pwd=%s&type=a&format=unixref&qdata=|%s||%s||%s|||KEY|";
		    "servlet/query?usr=%s&pwd=%s&type=a&format=unixref&qdata=|%s|%s|%s||%s|||KEY|";

    // ISSN|TITLE/ABBREV|FIRST AUTHOR|VOLUME|ISSUE|START PAGE|YEAR|RESOURCE TYPE|KEY|DOI

     /**
     * Lookup by journal title, volume and first page - 6 parameters are id, password, journal title, volume, firstPage.
     */       
     private static final String JOURNAL_BASE_QUERY =
            //"query?usr=%s&pwd=%s&type=a&format=unixref&qdata=|%s||%s||%s|||KEY|";
            "servlet/query?usr=%s&pwd=%s&type=a&format=unixref&qdata=|%s||%s||%s|||KEY|";

    /**
     * Lookup first author surname and  article title - 4 parameters are id, password, title, author.
     */
    private static final String TITLE_BASE_QUERY =
            "servlet/query?usr=%s&pwd=%s&type=a&format=unixref&qdata=%s|%s||key|";


	/**
     * Try to consolidate some uncertain bibliographical data with crossref web service based on
     * core metadata
     */
	public boolean consolidate(BiblioItem bib, List<BiblioItem> additionalBiblioInformation) throws Exception {
	 	boolean result = false;
		//List<BiblioItem> additionalBiblioInformation = new ArrayList<Biblio>();
		boolean valid = false;

		String doi = bib.getDOI();
        String aut = bib.getFirstAuthorSurname();
        String title = bib.getTitle();
		String journalTitle = bib.getJournal();
		String volume = bib.getVolume();
        if (StringUtils.isBlank(volume))
{'fraction_non_alphanumeric': 0.11745094303676891, 'fraction_numerical': 0.0040007620499142695, 'mean_word_length': 2.9738834216502648, 'pattern_counts': {'":': 2, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 8}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5397174', 'n_tokens_mistral': 3302, 'n_tokens_neox': 3085, 'n_words': 733}