<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of browse
 *
 * @author may
 */
class browse {
    private $PDO;
    
    private $conditions = array (
        'pubType' => 0,
        'affiliation1' => 0,
        'affiliation2' => 0,
        'affil1auth1' => 0,
        'affil2auth2' => 0,
        'topic' => array(),
        'topic_logic' => 0,
        'item' => array(),
        'item_logic' => 0,
        'module' => array(),
        'module_logic' => 0,
        'author' => '',
        'title_words' => '',
        'journal' => '',
        'round' => 0,
        'jField' => 0);
    
    private $sql_cond = "";
    private $query_result;
    
    public function __construct() {
        global $PDO;
        
        $this->PDO = $PDO;
        $this->PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    }
    
    public function getRecords() {
        // first, create condition base on posted data
        $this->_post2arr();
        $this->_sql_cond();
        return $this->_sql_query();
        
    }

/**
 * Returns number of all (online) records in the database
 * 
 * @return type integer
 */    
    public function numRecords() {
        // first, create condition base on posted data
        $this->_post2arr();
        $this->_sql_cond();
        return count($this->_sql_query());
        
    }
    
    public function item_citations() {
        if (intval($_POST['d'])>0) {
            $this->sql_cond = ' AND publication.id IN (SELECT id_publication FROM dict_pub_item WHERE id_item = ' .intval($_POST['d']) .') ';
            return $this->_sql_query();
        }
        
    }
    
    public function spreadsheet_export() {
        // first, create condition base on posted data
        $this->_post2arr();
        $this->_sql_cond();
        return $this->_spreadsheet_query();
    }
    
    public function spreadsheet_item() {
        // first, create condition base on posted data
        if (intval($_POST['id'])>0) {
            $this->sql_cond = ' AND publication.id IN (SELECT id_publication FROM dict_pub_item WHERE id_item = ' .intval($_POST['id']) .')';
            return $this->_spreadsheet_query();
        }
        else {
        }
    }
    
    // checks what came through the POST and sets the query conditions
    private function _post2arr() {
        // this is after passing the thing around PHP (it gets escaped so it's not an array any more)
        if ($_POST['spr'] == 1) {
            $_POST['d'] = json_decode ($_POST['d'], true);
        } 
                
        foreach ($_POST['d'] as $field) {
            
            if (is_array ($this->conditions[$field['name']])) {
               $this->conditions[$field['name']][] = intval($field['value']);
            }
            else {
                $this->conditions[$field['name']] = $field['value'];                
            }
        }
    }
    
    
    private function _spreadsheet_query () {
        
        $output = '"ONLINE ID",'
                . '"SCHOLAR SPSS ID",'
                . '"YEAR",'
                . '"AUTHORS' ."\n" .'(all)",'
                . '"AUTHOR 1",'
                . '"A1 AFFILIATION",'
                . '"AUTHOR 2",'
                . '"A2 AFFILIATION",'
                . '"AUTHOR 3",'
                . '"A3 AFFILIATION",'
                . '"AUTHOR 4",'
                . '"A4 AFFILIATION",'
                . '"AUTHOR 5",'
                . '"A5 AFFILIATION",'
                . '"TITLE (english)",'
                . '"TITLE (original)",'
                . '"PUBLICATION AFFILIATION",'
                . '"ENGLISH ABSTRACT",'
                . '"ORIGINAL ABSTRACT",'
                . '"BIBLIOGRAPHIC TYPE",'
                . '"JOURNAL, NEWSPAPER, MAGAZINE, BOOK CONFERENCE TITLE/NAME' ."\n" .'(only selected pub types)",'
                . '"VOLUME' ."\n" .'(journal article only)",'
                . '"ISSUE' ."\n" .'(journal article only)",'
                . '"FROM PAGE",'
                . '"TO PAGE",'
                . '"NUM PAGES",'
                . '"MAIN TOPIC",'
                . '"SECOND TOPIC",'
                . '"ALL TOPICS' ."\n" .'(incl. T1 and T2)",'
                . '"ITEMS USED",'
                . '"MODULES USED",'
                . '"ESS ROUNDS USED",'
                . '"CITATION' ."\n" .'(where provided)",'
                . '"DOI' ."\n" .'(where provided)",' 
                . "\n";       
        
        
        $query = "SELECT "
                     . "publication.id as id,"
                     . "publication.id_manual as id_manual,"
                     . "publication.year as year,"
                     . "GROUP_CONCAT(DISTINCT l_person.citation ORDER BY dict_pub_author.ord) as authors,"
                     . "A1.author as author1, "
                     . "A1.affiliation as affil_auth1,"
                     . "A2.author as author2, "
                     . "A2.affiliation as affil_auth2,"
                     . "A3.author as author3, "
                     . "A3.affiliation as affil_auth3,"
                     . "A4.author as author4, "
                     . "A4.affiliation as affil_auth4,"
                     . "A5.author as author5, "
                     . "A5.affiliation as affil_auth5,"
                     . "topic1.topic as topic1, "
                     . "topic2.topic as topic2, "
                     . "GROUP_CONCAT(DISTINCT topics.topic) as topics, "
                     . "GROUP_CONCAT(DISTINCT items.item) as items, "
                     . "GROUP_CONCAT(DISTINCT modules.module) as modules, "
                     . "GROUP_CONCAT(DISTINCT rounds.round) as rounds, "
                     . "publication.title_eng as title_eng, "
                     . "publication.title_orig as title_orig, "
                     . "pub_country.country as pub_country, "
                     . "publication.abstract_eng as abstract_eng, "
                     . "publication.abstract_orig as abstract_orig, "
                     . "l_pubType.name as bibtype, "
                     . "IF (publication.type=1,l_journal.name,"
                    . "     IF(publication.type=4,l_conference.name, "
                    . "     IF(publication.type=8,bookpart.title, "
                    . "     IF(publication.type=10,l_newspaper.name,'')))) as masterName, "
                     . "IF (publication.type=1,p_journalArticle.fromPage,IF (publication.type=8,p_bookChapter.fromPage,IF(publication.type=10,p_newspaperArticle.fromPage,''))) as fromPage,"
                     . "IF (publication.type=1,p_journalArticle.toPage,IF (publication.type=8,p_bookChapter.toPage,IF(publication.type=10,p_newspaperArticle.toPage,''))) as toPage,"
                     . "IF (publication.type=1 AND p_journalArticle.toPage>p_journalArticle.fromPage,(p_journalArticle.toPage-p_journalArticle.fromPage),"
                     ."     IF (publication.type=2,p_book.numPages,"
                     ."     IF (publication.type=3,p_workPaper.numPages,"
                     ."     IF (publication.type=5,p_report.numPages,"
                     ."     IF (publication.type=6,p_thesis.numPages,"
                     ."     IF (publication.type=11,p_manuscript.numPages,"
                     ."     IF (publication.type=8 AND p_journalArticle.toPage>p_journalArticle.fromPage,(p_bookChapter.toPage-p_bookChapter.fromPage),"
                    . "     IF(publication.type=10 AND p_journalArticle.toPage>p_journalArticle.fromPage,(p_newspaperArticle.toPage-p_newspaperArticle.fromPage),'')))))))) as numPages,"
                     . "p_journalArticle.volume as volume, "
                     . "p_journalArticle.issue as issue, "
                     . "publication.citation as citation, "
                     . "publication.doi as doi "
                     . "FROM publication "
                     . "LEFT JOIN dict_pub_author ON dict_pub_author.id_publication=publication.id "
                     . "LEFT JOIN l_person ON l_person.id=dict_pub_author.id_person "
                     . "LEFT JOIN p_journalArticle ON (publication.type=1 AND p_journalArticle.id=publication.id_parent ) "
                     . "LEFT JOIN l_journal ON l_journal.id=p_journalArticle.id_journal "
                     . "LEFT JOIN l_pubType ON l_pubType.id=publication.type "
                     . "LEFT JOIN p_bookChapter ON (publication.type=8 AND p_bookChapter.id=publication.id_parent) "
                     . "LEFT JOIN p_book bookpart ON (p_bookChapter.id_book=bookpart.id) "
                     . "LEFT JOIN p_newspaperArticle ON (publication.type=10 AND p_newspaperArticle.id=publication.id_parent) "
                     . "LEFT JOIN l_newspaper ON (p_newspaperArticle.id_newspaper=l_newspaper.id) "
                     . "LEFT JOIN p_book ON (publication.type=2 AND p_book.id=publication.id_parent) "
                     . "LEFT JOIN p_workPaper ON (publication.type=3 AND p_workPaper.id=publication.id_parent) "
                     . "LEFT JOIN p_report ON (publication.type=5 AND p_report.id=publication.id_parent) "
                     . "LEFT JOIN p_thesis ON (publication.type=6 AND p_thesis.id=publication.id_parent) "
                     . "LEFT JOIN p_manuscript ON (publication.type=11 AND p_manuscript.id=publication.id_parent) "
                     . "LEFT JOIN (SELECT l_person.citation as author, l_country.name as affiliation, dict_pub_author.id_publication as id_publication FROM dict_pub_author LEFT JOIN l_person ON l_person.id=dict_pub_author.id_person LEFT JOIN l_country ON l_country.id=dict_pub_author.id_country WHERE dict_pub_author.ord=1) A1 ON A1.id_publication=publication.id "
                     . "LEFT JOIN (SELECT l_person.citation as author, l_country.name as affiliation, dict_pub_author.id_publication as id_publication FROM dict_pub_author LEFT JOIN l_person ON l_person.id=dict_pub_author.id_person LEFT JOIN l_country ON l_country.id=dict_pub_author.id_country WHERE dict_pub_author.ord=2) A2 ON A2.id_publication=publication.id "
                     . "LEFT JOIN (SELECT l_person.citation as author, l_country.name as affiliation, dict_pub_author.id_publication as id_publication FROM dict_pub_author LEFT JOIN l_person ON l_person.id=dict_pub_author.id_person LEFT JOIN l_country ON l_country.id=dict_pub_author.id_country WHERE dict_pub_author.ord=3) A3 ON A3.id_publication=publication.id "
                     . "LEFT JOIN (SELECT l_person.citation as author, l_country.name as affiliation, dict_pub_author.id_publication as id_publication FROM dict_pub_author LEFT JOIN l_person ON l_person.id=dict_pub_author.id_person LEFT JOIN l_country ON l_country.id=dict_pub_author.id_country WHERE dict_pub_author.ord=4) A4 ON A4.id_publication=publication.id "
                     . "LEFT JOIN (SELECT l_person.citation as author, l_country.name as affiliation, dict_pub_author.id_publication as id_publication FROM dict_pub_author LEFT JOIN l_person ON l_person.id=dict_pub_author.id_person LEFT JOIN l_country ON l_country.id=dict_pub_author.id_country WHERE dict_pub_author.ord=5) A5 ON A5.id_publication=publication.id "
                     . "LEFT JOIN (SELECT l_topic.name as topic, dict_pub_topic.id_publication FROM dict_pub_topic, l_topic WHERE dict_pub_topic.weight=1 AND l_topic.id=dict_pub_topic.id_topic) topic1 ON (topic1.id_publication=publication.id ) "
                     . "LEFT JOIN (SELECT l_topic.name as topic, dict_pub_topic.id_publication FROM dict_pub_topic, l_topic WHERE dict_pub_topic.weight=2 AND l_topic.id=dict_pub_topic.id_topic) topic2 ON (topic2.id_publication=publication.id ) "
                     . "LEFT JOIN (SELECT l_topic.name as topic, dict_pub_topic.id_publication FROM dict_pub_topic, l_topic WHERE l_topic.id=dict_pub_topic.id_topic) topics ON (topics.id_publication=publication.id ) "
                     . "LEFT JOIN (SELECT l_ess_item.name as item, dict_pub_item.id_publication FROM dict_pub_item, l_ess_item WHERE l_ess_item.id=dict_pub_item.id_item) items ON (items.id_publication=publication.id ) "
                     . "LEFT JOIN (SELECT l_module.name as module, dict_pub_module.id_publication FROM dict_pub_module, l_module WHERE l_module.id=dict_pub_module.id_module) modules ON (modules.id_publication=publication.id ) "
                     . "LEFT JOIN (SELECT CONCAT (l_ess_round.name, ' (', l_ess_round.year, ')') as round, dict_pub_round.id_publication FROM dict_pub_round, l_ess_round WHERE l_ess_round.id=dict_pub_round.id_round) rounds ON (rounds.id_publication=publication.id ) "
                     . "LEFT JOIN (SELECT l_country.name as country, dict_pub_country.id_publication FROM  dict_pub_country, l_country WHERE dict_pub_country.id_country=l_country.id) pub_country ON (pub_country.id_publication=publication.id) "
                     . "LEFT JOIN p_conferencePP ON (publication.type=4 AND publication.id_parent=p_conferencePP.id) "
                     . "LEFT JOIN l_conference ON (l_conference.id = p_conferencePP.id_conference) "
                     . " WHERE 1=1 AND publication.status='Online' " .$this->sql_cond 
                     . " GROUP BY publication.id ";
       
        
        
        try {
             $stmt = $this->PDO->prepare($query);
             
                        //common::except ($query);

             
             $stmt->execute();

             $this->query_result = $stmt;
             // this instead of rowCount to make in compatible with non-mysql DB
             while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $output .= '"' .$r['id'] .'",'
                        .'"' .common::field2csv($r['id_manual']) .'",'
                        .'"' .common::field2csv($r['year']) .'",'
                        .'"' .common::field2csv($r['authors']) .'",'
                        .'"' .common::field2csv($r['author1']) .'",'
                        .'"' .common::field2csv($r['affil_auth1']) .'",'
                        .'"' .common::field2csv($r['author2']) .'",'
                        .'"' .common::field2csv($r['affil_auth2']) .'",'
                        .'"' .common::field2csv($r['author3']) .'",'
                        .'"' .common::field2csv($r['affil_auth3']) .'",'
                        .'"' .common::field2csv($r['author4']) .'",'
                        .'"' .common::field2csv($r['affil_auth4']) .'",'
                        .'"' .common::field2csv($r['author5']) .'",'
                        .'"' .common::field2csv($r['affil_auth5']) .'",'
                        .'"' .common::field2csv($r['title_eng']) .'",'
                        .'"' .common::field2csv($r['title_orig']) .'",'
                        .'"' .common::field2csv($r['pub_country']) .'",'
                        .'"' .common::field2csv($r['abstract_eng']) .'",'
                        .'"' .common::field2csv($r['abstract_orig']) .'",'
                        .'"' .common::field2csv($r['bibtype']) .'",'
                        .'"' .common::field2csv($r['masterName']) .'",'
                        .'"' .common::field2csv($r['volume']) .'",'
                        .'"' .common::field2csv($r['issue']) .'",'
                        .'"' .common::field2csv($r['fromPage']) .'",'
                        .'"' .common::field2csv($r['toPage']) .'",'
                        .'"' .common::field2csv($r['numPages']) .'",'
                        .'"' .common::field2csv($r['topic1']) .'",'
                        .'"' .common::field2csv($r['topic2']) .'",'
                        .'"' .common::field2csv($r['topics']) .'",'
                        .'"' .common::field2csv($r['items']) .'",'
                        .'"' .common::field2csv($r['modules']) .'",'
                        .'"' .common::field2csv($r['rounds']) .'",'
                        .'"' .common::field2csv($r['citation']) .'",'
                        .'"' .common::field2csv($r['doi']) .'"' ."\n";
             }
             return $output;
        }
        catch (exception $ex) {
           common::except ('Err: ' .$ex .' in ' .__DIR__ .'/' .__FILE__ .':' .__LINE__);
        }              
        
    }
        
    
    private function _sql_query () {
        try {
             $stmt = $this->PDO->prepare("SELECT IF(ISNULL(publication.citation),"
                     . "CONCAT ("
                     .   "GROUP_CONCAT(l_person.citation ORDER BY dict_pub_author.ord),"
                     .   "' (', publication.year, '). ', "
                     .   "publication.title_eng, '. ',"
                     // now fields for article
                     ."    IF (publication.type=1,CONCAT("
                     . "    l_journal.name, '. ', p_journalArticle.volume, '(', p_journalArticle.issue, '), ', p_journalArticle.fromPage, '-', p_journalArticle.toPage, '.')"
                     . ",'')   )"
                     . " ,"
                     . "publication.citation) as citation FROM publication "
                     . "LEFT JOIN dict_pub_author ON dict_pub_author.id_publication=publication.id "
                     . "LEFT JOIN l_person ON l_person.id=dict_pub_author.id_person "
                     . "LEFT JOIN p_journalArticle ON p_journalArticle.id=publication.id_parent "
                     . "LEFT JOIN l_journal ON l_journal.id=p_journalArticle.id_journal "
                     . " WHERE 1=1 AND publication.status='Online' " .$this->sql_cond 
                     . " GROUP BY publication.id ORDER BY publication.id DESC");
             $stmt->execute();

             $this->query_result = $stmt;
             // this instead of rowCount to make in compatible with non-mysql DB
             if ($r = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
                return $r;
             }
        }
        catch (exception $ex) {
           common::except ('Err: ' .$ex .' in ' .__DIR__ .'/' .__FILE__ .':' .__LINE__);
        }              
        
    }
    
    private function _sql_cond() {

        // publication type
        if (intval($this->conditions['pubType']) > 0) {
            $this->sql_cond .= ' AND publication.type=' .intval($this->conditions['pubType']) .' ';
        }

        
        // affiliation 1
        if (intval($this->conditions['affiliation1']) > 0) {
            $this->sql_cond .= ' AND publication.id IN (SELECT id_publication FROM dict_pub_author WHERE id_country=' .intval($this->conditions['affiliation1']) .(intval($this->conditions['affil1auth1'])==1?' AND ord=1':'') .') ';
        }
        
        // affiliation 2
        if (intval($this->conditions['affiliation2']) > 0) {
            $this->sql_cond .= ' AND publication.id IN (SELECT id_publication FROM dict_pub_author WHERE id_country=' .intval($this->conditions['affiliation2']) .(intval($this->conditions['affil2auth2'])==1?' AND ord=2':'') .') ';
        }

        if (count($this->conditions['topic'])>0) {
            // ANY of the selected
            if (intval($this->conditions['topic_logic']) == 0) {
                $this->sql_cond .= ' AND publication.id IN (SELECT id_publication FROM dict_pub_topic WHERE id_topic IN (' .implode (',', $this->conditions['topic']) .')) ';
            }
            
            else {
                
            }
        }
        if (count($this->conditions['item'])>0) {
            // ANY of the selected
            if (intval($this->conditions['item_logic']) == 0) {
                $this->sql_cond .= ' AND publication.id IN (SELECT id_publication FROM dict_pub_item WHERE id_item IN (' .implode (',', $this->conditions['item']) .')) ';
            }
            else {
                
            }
        }
        if (count($this->conditions['module'])>0) {
            // ANY of the selected
            
            if (intval($this->conditions['module_logic']) == 0) {
                $this->sql_cond .= ' AND publication.id IN (SELECT id_publication FROM dict_pub_module WHERE id_module IN (' .implode (',', $this->conditions['module']) .')) ';
            }
            else {
            }
        }
        
        if (intval($this->conditions['round']) > 0) {
            $this->sql_cond .= ' AND publication.id IN (SELECT id_publication FROM dict_pub_round WHERE id_round=' .intval($this->conditions['round']).') ';
        }
        if (intval($this->conditions['jField']) > 0) {
            $this->sql_cond .= ' AND publication.id IN (select publication.id FROM publication, p_journalArticle, dict_journal_field WHERE publication.type=1 AND dict_journal_field.id_field=' .intval($this->conditions['jField']) .' AND dict_journal_field.id_journal=p_journalArticle.id_journal AND publication.id_parent=p_journalArticle.id) ';
        }
        
        if ($this->conditions['author']!='') {
            $author = preg_replace ('/^[A-Z0-9,. ]/i', '', $this->conditions['author']);
            $this->sql_cond .= ' AND publication.id IN (select dict_pub_author.id_publication FROM dict_pub_author, l_person WHERE dict_pub_author.id_person=l_person.id AND (l_person.citation LIKE (\'%' . $this->conditions['author'] .'%\') OR l_person.last_name LIKE (\'%' . $this->conditions['author'] .'%\'))) ';
        }

        if ($this->conditions['title_words']!='') {
            $this->sql_cond .= ' AND (publication.title_eng LIKE (\'%' .preg_replace ('/^[A-Z0-9,. ]/i', '', $this->conditions['title_words']) .'%\') OR publication.title_orig LIKE (\'%' .preg_replace ('/^[A-Z0-9,. ]/i', '', $this->conditions['title_words']) .'%\' )) ';
        }
        
        
        if ($this->conditions['journal']!='') {
            $author = preg_replace ('/^[A-Z0-9,. ]/i', '', $this->conditions['journal']);
            $this->sql_cond .= ' AND publication.id IN (SELECT publication.id FROM publication, p_journalArticle, l_journal WHERE publication.type=1 AND publication.id_parent=p_journalArticle.id AND p_journalArticle.id_journal=l_journal.id AND l_journal.name LIKE (\'' . $this->conditions['journal']. '\')) ';
        }        
        
        return;
    }
    
}