<?php
define('_PURGE_DAYS_', 3);
define('_DEBUG_', false); // outputs sys_log messages on cli for  ./typo3/cli_dispatch.phpsh scheduler
libxml_use_internal_errors(true);
class tx_tuwinformations_sync extends \TYPO3\CMS\Scheduler\Task\AbstractTask {
    public function execute() {
        $syncedOids = array();
        $Orgoid2PubdbInst = array(28773 => 1031, 275965 => 1030, 264837 => 1022, 764753 => 1023, 15068573 => 1126, 797161 => 1037, 797417 => 1038, 1092121 => 1039, 802849 => 1040, 814349 => 1046, 814945 => 1047, 815493 => 1048, 774165 => 1049, 771961 => 1050, 762745 => 1051, 761697 => 1052, 258729 => 1053, 768949 => 1057, 766397 => 1060, 744261 => 1061, 3334673 => 1121, 815985 => 1065, 814809 => 1067, 4954085 => 1122, 814825 => 1071, 5086241 => 1072, 812113 => 1074, 401617 => 1091, 773289 => 1092, 807465 => 1080, 806597 => 1082, 3590685 => 1093, 1131873 => 1089, 783537 => 1002, 788853 => 1007, 788557 => 1009, 520033 => 1010, 11187273 => 1125, 1025365 => 1019, 786745 => 1014, 603793 => 1015, 785941 => 1017, 70949 => 1020, 784701 => 1095, 779889 => 1099, 784041 => 1100, 783801 => 1101, 782405 => 1103, 781481 => 1104, 779565 => 1106, 778593 => 1107, 775397 => 1110, 895229 => 1041, 896297 => 1042, 897773 => 1043, 898245 => 1044,);
        // pi1 - TUW Stafflist
        $orgoids = array();
        $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('pi_flexform', 'tt_content', 'list_type=\'tuw_informations_pi1\'');
        while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
            $xml = simplexml_load_string($row['pi_flexform']);
            if ($xml === false) {
                $this->logMe('tx_tuwinformations_pi1: Error loading pi_flexform');
            } else {
                foreach ($xml->xpath('//data/sheet/language/field[@index="orgoid"]') as $x) {
                    if (!in_array((string)$x->value[0], $orgoids)) {
                        $orgoids[] = (string)$x->value[0];
                    }
                }
            }
        }
        // pi2 - TUW Projectlist
        $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('pi_flexform', 'tt_content', 'list_type=\'tuw_informations_pi2\'');
        while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
            $xml = simplexml_load_string($row['pi_flexform']);
            if ($xml === false) {
                $this->logMe('tx_tuwinformations_pi2: Error loading pi_flexform');
            } else {
                foreach ($xml->xpath('//data/sheet/language/field[@index="orgoid"]') as $x) {
                    if (!in_array((string)$x->value[0], $orgoids)) {
                        $orgoids[] = (string)$x->value[0];
                    }
                }
            }
        }
        // pi3 - TUW Publicationslist
        $insts = array();
        $abts = array();
        $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('pi_flexform', 'tt_content', 'list_type=\'tuw_informations_pi3\'');
        while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
            $xml = simplexml_load_string($row['pi_flexform']);
            if ($xml === false) {
                $this->logMe('tx_tuwinformations_pi3: Error loading pi_flexform');
            } else {
                foreach ($xml->xpath('//data/sheet/language/field[@index="inst"]') as $x) {
                    if (((string)$x->value[0] != '') && (!in_array((string)$x->value[0], $insts))) {
                        $insts[] = (string)$x->value[0];
                    }
                }
                foreach ($xml->xpath('//data/sheet/language/field[@index="abt"]') as $x) {
                    if (((string)$x->value[0] != '') && (!in_array((string)$x->value[0], $abts))) {
                        $abts[] = (string)$x->value[0];
                    }
                }
            }
        }
        $this->logMe('tx_tuwinformations_sync: Scheduler Started');
        if (count($orgoids) > 0) {
            foreach ($orgoids as $orgoid) {
                $this->logMe('Starting syncStaff for orgoid: ' . $orgoid);
                $this->syncStaff($orgoid);
                $this->syncProjects($orgoid);
            }
        }
        if (count($insts) > 0) {
            foreach ($insts as $inst) {
                $this->logMe('Starting syncPublications for inst: ' . $inst);
                $this->syncPublications('inst=' . $inst);
            }
        }
        if (count($abts) > 0) {
            foreach ($abts as $abt) {
                $this->logMe('Starting syncPublications for abt: ' . $abt);
                $this->syncPublications('abt=' . $abt);
            }
        }
        $this->logMe('tx_tuwinformations_sync: Scheduler Finished');
        return true;
    }
    public function logMe($message) {
        $GLOBALS['BE_USER']->writelog(4, 0, 0, 0, 'EXT tuw_informations: ' . $message, '');
        // helpful for Scheduler cli-call debugging only
        if (_DEBUG_) {
            echo $message . "\n";
        }
        return true;
    }
    public function syncPerson($oid, $orgoid, $oxml) {
        global $syncedOids;
        $hash = md5($oid . "#" . $orgoid);
        if (!in_array($hash, $syncedOids)) {
            $syncedOids[] = $hash;
            if ($GLOBALS['TYPO3_DB']->exec_SELECTquery('*', 'tx_tuwinformations_persons', sprintf('oid=%d AND orgoid=%d AND DATEDIFF(NOW(),updated)<%d', $oid, $orgoid, 1)) != 0) {
                $url = sprintf('https://tiss.tuwien.ac.at/adressbuch/adressbuch/person_via_oid/%d.xml', $oid);
                $this->logMe('Fetching URL: ' . $url);
                $xml = file_get_contents($url);
                $xml = str_replace('tiss:phone_number', 'tiss_phone_number', $xml);
                $xml = str_replace('tiss:fax_number', 'tiss_fax_number', $xml);
                $xml = str_replace('tiss:email', 'tiss_email', $xml);
                $pxml = simplexml_load_string($xml)->person[0];
                if ($pxml === false) {
                    $this->logMe('tx_tuwinformations: Error loading person in XML-format');
                } else {
                    unset($newperson);
                    $newperson['orgoid'] = $orgoid;
                    $newperson['oid'] = $oid;
                    $newperson['firstname'] = (string)$pxml->firstname[0];
                    $newperson['lastname'] = (string)$pxml->lastname[0];
                    $newperson['title'] = (string)$pxml->preceding_titles[0];
                    $newperson['postpositioned_titles'] = (string)$pxml->postpositioned_titles[0];
                    $newperson['picture'] = (string)$pxml->picture_uri[0];
                    $exml = $pxml->employee[0]->employment[0];
                    foreach ($pxml->employee[0]->employment as $e) {
                        if ($e->organisational_unit[0]->attributes()->oid == $orgoid) {
                            $exml = $e;
                        }
                    }
                    $newperson['function'] = (string)$exml->function[0];
                    if (!empty($oxml->function_category[0])) {
                        $newperson['functioncategory'] = (string)$oxml->function_category[0];
                    }
                    if ((string)$exml->internal_code[0] != '') {
                        $newperson['internalcode'] = (string)$exml->internal_code[0];
                    }
                    $newperson['room'] = (string)$exml->room[0]->room_code[0];
                    $newperson['street'] = (string)$exml->room[0]->address[0]->street;
                    $newperson['zip'] = (string)$exml->room[0]->address[0]->zip_code;
                    $newperson['city'] = (string)$exml->room[0]->address[0]->city;
                    if (!empty($exml->phone_numbers[0]->tiss_phone_number)) {
                        $s = 0;
                        foreach ($exml->phone_numbers[0]->tiss_phone_number as $pn) {
                            $s++;
                            $sql = sprintf("INSERT INTO tx_tuwinformations_phones (oid,phone,sort,updated) VALUES (%d,'%s',%d,NOW()) ON DUPLICATE KEY UPDATE sort=%d,updated=NOW()", $oid, $pn, $s, $s);
                            $insert = $GLOBALS['TYPO3_DB']->sql_query($sql);
                        }
                    }
                    if (!empty($exml->fax_numbers[0]->tiss_fax_number)) {
                        $s = 0;
                        foreach ($exml->fax_numbers[0]->tiss_fax_number as $pn) {
                            $s++;
                            $sql = sprintf("INSERT INTO tx_tuwinformations_fax (oid,fax,sort,updated) VALUES (%d,'%s',%d,NOW()) ON DUPLICATE KEY UPDATE sort=%d,updated=NOW()", $oid, $pn, $s, $s);
                            $insert = $GLOBALS['TYPO3_DB']->sql_query($sql);
                        }
                    }
                    if (!empty($exml->emails[0]->tiss_email)) {
                        foreach ($exml->emails[0]->tiss_email as $em) {
                            $sql = sprintf("INSERT INTO tx_tuwinformations_emails (oid,email,type,updated) VALUES (%d,'%s','%s',NOW()) ON DUPLICATE KEY UPDATE updated=NOW()", $oid, $em, $em->attributes()->type);
                            $insert = $GLOBALS['TYPO3_DB']->sql_query($sql);
                        }
                    }
                    $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('oid', 'tx_tuwinformations_persons', sprintf('oid=%d AND orgoid=%d', $oid, $orgoid));
                    if ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
                        $update = $GLOBALS['TYPO3_DB']->exec_UPDATEquery('tx_tuwinformations_persons', sprintf('oid=%d AND orgoid=%d', $oid, $orgoid), $newperson);
                    } else {
                        $insert = $GLOBALS['TYPO3_DB']->exec_INSERTquery('tx_tuwinformations_persons', $newperson);
                    }
                    $res = $GLOBALS['TYPO3_DB']->sql_query(sprintf('UPDATE tx_tuwinformations_persons SET updated=NOW() WHERE oid=%d AND orgoid=%d', $oid, $orgoid));
                }
            }
        } else {
            $this->logMe('OID/ORGOID ' . $oid . "/" . $orgoid . " already synced in this job");
        }
        return true;
    }
    public function syncStaff($orgoid) {
        $n = 0;
        $url = sprintf('https://tiss.tuwien.ac.at/adressbuch/adressbuch/orgeinheit_via_oid/%d.xml?persons=true', $orgoid);
        $this->logMe('Fetching URL: ' . $url);
        $sxml = simplexml_load_file($url);
        if ($sxml === false) {
            $this->logMe(sprintf('Error loading Stafflist for Orgoid %s in XML-format',$orgoid));
        } else {
            foreach ($sxml->orgunit[0]->employees[0]->person as $p) {
                $n++;
                $oid = (string)$p->attributes()->oid;
                $this->syncPerson($oid, $orgoid, $p);
            }
            $GLOBALS['TYPO3_DB']->exec_DELETEquery('tx_tuwinformations_emails', sprintf('DATEDIFF(CURDATE(),updated)>%d', _PURGE_DAYS_));
            $GLOBALS['TYPO3_DB']->exec_DELETEquery('tx_tuwinformations_fax', sprintf('DATEDIFF(CURDATE(),updated)>%d', _PURGE_DAYS_));
            $GLOBALS['TYPO3_DB']->exec_DELETEquery('tx_tuwinformations_phones', sprintf('DATEDIFF(CURDATE(),updated)>%d', _PURGE_DAYS_));
            $GLOBALS['TYPO3_DB']->exec_DELETEquery('tx_tuwinformations_persons', sprintf('DATEDIFF(CURDATE(),updated)>%d', _PURGE_DAYS_));
        }
        return true;
    }
    public function syncProjects($orgoid) {
        $url = sprintf('https://tiss.tuwien.ac.at/api/pdb/internal/projectsearch/v2/?instituteOid=%d', $orgoid);
        $this->logMe('Fetching URL: ' . $url);
        $sxml = simplexml_load_file($url);
        if ($sxml === false) {
            $this->logMe(sprintf('Error loading projects for OrgOid %s in XML-format',$orgoid));
        } else {
            // TODO: add check that returned XML is valid and has minimum one project-node
            foreach ($sxml->project as $p) {
                $pid = (string)$p->projectId;
                $url = sprintf('https://tiss.tuwien.ac.at/api/pdb/internal/project/v2/%d', $pid);
                $this->logMe('Fetching URL: ' . $url);
                $pxml = simplexml_load_file($url);
                if ($pxml === false) {
                    $this->logMe(sprintf('tx_tuwinformations: Error loading project %d in XML-format', $pid));
                } else {
                    unset($project);
                    $project['orgoid'] = $orgoid;
                    $project['short_description'] = (string)$p->shortDescription;
                    $project['title_en'] = (string)$p->titleEn;
                    $project['title_de'] = (string)$p->titleDe;
                    $project['end'] = (string)$p->end;
                    $project['projectform'] = (string)$pxml->project[0]->projectForm;
                    $project['projecttype'] = (string)$pxml->project[0]->projectType;
                    $isManagerFromOrg = false;
                    $project['manageroid'] = '';
                    foreach ($pxml->project[0]->institutes[0]->institute as $institute) {
                        foreach ($institute->projectMembers[0]->projectMember as $pmem) {
                            if ((string)$pmem->roleDe[0] == 'Projektleiter') {
                                $manageroid = (string)$pmem->attributes() ['oid'];
                                if (($project['manageroid'] == '') || ((!$isManagerFromOrg) && ((string)$institute->attributes() ['oid'] == $orgoid))) {
                                    $manageroid = (string)$pmem->attributes()->oid;
                                    $project['manageroid'] = $manageroid;
                                    $isManagerFromOrg = ($manageroid == $orgoid);
                                    if (is_numeric($manageroid)) {
                                        $this->syncPerson($manageroid, '0', NULL);
                                    }
                                }
                            }
                        }
                    }
                    switch ((string)$pxml->project[0]->projectType) {
                        case 'FORSCHUNGS_FOERDERUNGS_PROJEKT':
                            $project['financed_de'] = (string)$pxml->project[0]->fundings[0]->funding[0]->organization[0]->de;
                            $project['financed_en'] = (string)$pxml->project[0]->fundings[0]->funding[0]->organization[0]->en;
                        break;
                        case 'STIPENDIUM':
                            $project['financed_de'] = (string)$pxml->project[0]->fundings[0]->funding[0]->organization[0]->de;
                            $project['financed_en'] = (string)$pxml->project[0]->fundings[0]->funding[0]->organization[0]->en;
                        break;
                        case 'MANAGEMENT_PROJEKT':
                            $project['financed_de'] = (string)$pxml->project[0]->fundingEducations[0]->fundingEducation[0]->type[0]->de;
                            $project['financed_en'] = (string)$pxml->project[0]->fundingEducations[0]->fundingEducation[0]->type[0]->en;
                        break;
                        case 'AUFTRAGS_FORSCHUNGS_PROJEKT':
                            $project['financed_de'] = (string)$pxml->project[0]->financiers[0]->financier[0]->de;
                            $project['financed_en'] = (string)$pxml->project[0]->financiers[0]->financier[0]->en;
                        break;
                        default:
                            $project['financed_de'] = '';
                            $project['financed_en'] = '';
                    }
                    $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('uid', 'tx_tuwinformations_projects', 'projectid=' . $pid);
                    if ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
                        $update = $GLOBALS['TYPO3_DB']->exec_UPDATEquery('tx_tuwinformations_projects', 'projectid=' . $pid, $project);
                    } else {
                        $project['projectid'] = $pid;
                        $insert = $GLOBALS['TYPO3_DB']->exec_INSERTquery('tx_tuwinformations_projects', $project);
                    }
                    $res = $GLOBALS['TYPO3_DB']->sql_query(sprintf('UPDATE tx_tuwinformations_projects SET updated=NOW() WHERE projectid=%d', $pid));
                }
            }
        }
        return true;
    }
    public function syncPublications($einheit) {
        $n = 0;
        global $Orgoid2PubdbInst;
        if (is_numeric($einheit)) {
            if (!array_key_exists($einheit, $Orgoid2PubdbInst)) {
                $this->logMe('Could not find PubDB-Inst-Code for ' . $einheit);
            } else {
                $oe = 'inst=' . $Orgoid2PubdbInst[$orgoid];
            }
        } else {
            $oe = $einheit;
        }
        $url = sprintf('http://publik.tuwien.ac.at/program/pubexport.php?lang=1&%s&dousealiases=0&namesearch=&func=0&sort=5&inv=0&zeit=1&from=1990&to=2099&sgrp=0&publiststruct=0&listwarnlevel=0&revonly=0&scionly=0&searchexactly=0&sfeld=0&stext=&save=0&nojava=0', $oe);
        $this->logMe('Fetching URL: ' . $url);
        $sxml = simplexml_load_file($url);
        if ($pxml === false) {
            $this->logMe('Error loading publications in XML-format');
        } else {
            foreach ($sxml->publikation as $p) {
                $n++;
                $spid = (string)$p->pub_id;
                if (preg_match('/TUW\-([0-9]*)/', $spid, $res)) {
                    $pid = $res[1];
                    $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('publicationid,updated', 'tx_tuwinformations_publications', 'publicationid=' . $pid);
                    if ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
                        if (preg_match('/inst=([0-9]+)/', $oe, $res1)) {
                            unset($publication);
                            $publication['institute'] = $res1[1];
                            $update = $GLOBALS['TYPO3_DB']->exec_UPDATEquery('tx_tuwinformations_publications', 'publicationid=' . $pid, $publication);
                        }
                        if (strtotime($row['updated']) <= strtotime((string)$p->letzte_aenderung)) {
                            unset($publication);
                            if (preg_match('/inst=([0-9]+)/', $oe, $res1)) {
                                $publication['institute'] = $res1[1];
                            }
                            $publication['publicationid'] = $pid;
                            $publication['publicationtype'] = (string)$p->type;
                            $publication['publicationtypeid'] = (string)$p->type_id;
                            $publication['title'] = (string)$p->titel;
                            $publication['doi'] = (string)$p->doi;
                            $publication['citation'] = trim((string)$p->reference);
                            $publication['year'] = '1990';
                            $years = $p->xpath('*/jahr');
                            foreach ((array)$years as $year) {
                                $publication['year'] = $year;
                            }
                            if ((string)$p->type == 'Zeitschriftenartikel') {
                                $publication['journal'] = (string)$p->zeitschriftenartikel->zeitschrift;
                            }
                            $update = $GLOBALS['TYPO3_DB']->exec_UPDATEquery('tx_tuwinformations_publications', 'publicationid=' . $pid, $publication);
                        }
                    } else {
                        unset($publication);
                        if (preg_match('/inst=([0-9]+)/', $oe, $res1)) {
                            $publication['institute'] = $res1[1];
                        }
                        $publication['publicationid'] = $pid;
                        $publication['publicationtype'] = (string)$p->type;
                        $publication['publicationtypeid'] = (string)$p->type_id;
                        $publication['title'] = (string)$p->titel;
                        $publication['doi'] = (string)$p->doi;
                        $publication['citation'] = trim((string)$p->reference);
                        $publication['year'] = '1990';
                        $years = $p->xpath('*/jahr');
                        foreach ((array)$years as $year) {
                            $publication['year'] = $year;
                        }
                        if ((string)$p->type == 'Zeitschriftenartikel') {
                            $publication['journal'] = (string)$p->zeitschriftenartikel->zeitschrift;
                        }
                        $insert = $GLOBALS['TYPO3_DB']->exec_INSERTquery('tx_tuwinformations_publications', $publication);
                        foreach ($p->autor_info as $a) {
                            unset($author);
                            $author['publicationid'] = $pid;
                            $author['oid'] = (string)$a->oid;
                            $author['firstname'] = (string)$a->vorname_lang;
                            $author['lastname'] = (string)$a->nachname;
                            $author['institute'] = (string)$a->institut;
                            $author['division'] = (string)$a->abteilung;
                            $author['sortid'] = (string)$a->reihung;
                            if (preg_match('/inst=([0-9]+)/', $oe, $res1)) {
                                $author['pubdbInstitute'] = $res1[1];
                            } else {
                                $author['pubdbInstitute'] = '';
                            }
                            $insert = $GLOBALS['TYPO3_DB']->exec_INSERTquery('tx_tuwinformations_authors', $author);
                        }
                    }
                    // add new divisions
                    if (preg_match('/abt=([0-9]+)/', $einheit, $res1)) {
                        unset($division);
                        $division['publicationid'] = $pid;
                        $division['division'] = $res1[1];
                        $division['updated'] = strftime('%Y-%m-%d');
                        // only insert if not existing yet, otherwise key idx1 will err
                        $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('uid', 'tx_tuwinformations_divisions', 'publicationid=' . $division['publicationid'] . ' AND division=' . $division['division']);
                        if ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
                            // udpate needed - dont think so.
                           // $update = $GLOBALS['TYPO3_DB']->exec_UPDATEquery('tx_tuwinformations_divisions', 'WHERE', $division);
                           $GLOBALS['TYPO3_DB']->sql_query(sprintf("UPDATE tx_tuwinformations_divisions SET updated=NOW() WHERE uid=%d",$row['uid']));
                        } else {
                            $project['projectid'] = $pid;
                            $insert = $GLOBALS['TYPO3_DB']->exec_INSERTquery('tx_tuwinformations_divisions', $division);
                        }
                    }
                }
                $res = $GLOBALS['TYPO3_DB']->sql_query(sprintf('UPDATE tx_tuwinformations_publications SET updated=NOW() WHERE publicationid=%d', $pid));
            }
            $GLOBALS['TYPO3_DB']->exec_DELETEquery('tx_tuwinformations_divisions', sprintf('DATEDIFF(CURDATE(),updated)>%d', _PURGE_DAYS_));
        }
        return true;
    }
}
?>
