public static function getAgendaConferenceIdsForNorditaEvents()
{
$agenda_cid = array();
// ------------------------
if (!function_exists("menu_events")) {
if (!file_exists(INCLEVEL . PATH_MENU . "/m_events.php")) {
return array();
}
include PATH_MENU . "/m_events.php";
}
// ------------------------
foreach (menu_events() as $path => $event) {
if (preg_match("#events/[^/]*#", $path) && isset($event["confid"])) {
$agenda_cid[] = $event["confid"];
}
}
rsort($agenda_cid);
return $agenda_cid;
}
public function updateOneEventFromAgenda($confId, $keepAllEvents = FALSE)
{
if (empty($confId) || !is_numeric($confId)) {
return FALSE;
}
$default_room = "";
// ------------------------------------------------------------------
// -- Get data for $confId event from Agenda API
// ------------------------------------------------------------------
//@@@ -- in updateOneEventFromAgenda() -- calls getOneEvent
$event = functions::callMethod('fromdb_agenda', 'fromdb/agenda', 'getOneEvent', $confId, TRUE, TRUE, FALSE, FALSE, FALSE, TRUE);
if (empty($event)) {
return FALSE;
}
// Remove startup programs (which have no assigned dates yet)
if (!$keepAllEvents && (!isset($event["start"]) || strpos($event["start"], "01-01") !== FALSE)) {
return FALSE;
}
//TODO: check if this $confId at all exists in the DB so that it can be updated
// ------------------------------------------------------------------
// -- Get additional data for $confId event from scraping Agenda page
// ------------------------------------------------------------------
//sleep(5); // to avoid DoS
$event_from_scraping["agendapageisopen"] = 1;
////$thepath = "http://www.nordita.org/".$event_from_file["subpath"]."/"; // must end with "/"
//$thepath = "http://agenda.albanova.se/conferenceDisplay.py?confId=".$confId;
//$data = functions::acurl($thepath); // assign to variable first
//$event_from_scraping["agendapageisopen"] = (strpos($data,"signInForm")===FALSE) ? 1 : 0;
// ------------------------------------------------------------------
// -- Get and update data for the same event in nw_events database
// ------------------------------------------------------------------
// -------------------------------
// -- Component 'event'
// -------------------------------
$sql = "SELECT event_id FROM " . $this->dbprefix . "event WHERE event_id='" . $confId . "'";
$res = $this->query($sql, IS_TESTSERVER);
// includes a call to connect
$record_exists = $this->num_rows($res) ? 1 : 0;
/* KEEP FOR REFERENCE
// Select basic info about a given event, including list of organizers:
SELECT
E.*,
GROUP_CONCAT(DISTINCT P.organizer SEPARATOR ', ') AS 'organizers'
FROM
event AS E
LEFT JOIN (
SELECT
event_id,
CONCAT(av_firstname,' ',av_lastname) as 'organizer'
FROM
person
WHERE
(event_id='4393') AND (person_type='organizer')
) AS P ON P.event_id=E.event_id
WHERE
E.event_id='4393'
GROUP BY
E.event_id
*/
foreach (array("title", "room", "attenders") as $field) {
if (!isset($event[$field])) {
$event[$field] = "";
}
}
foreach (array("start", "end", "regstart", "regend") as $field) {
if (!isset($event[$field])) {
$event[$field] = DATETIME_NULL;
}
}
if (!empty($event["room"])) {
$default_room = addslashes(strip_tags(substr($event["room"], 0, 49)));
}
if ($record_exists) {
// -- Update database record
$sql = "UPDATE " . $this->dbprefix . "event " . "SET " . " title='" . addslashes(strip_tags(substr($event["title"], 0, 199))) . "'," . " room='" . addslashes(strip_tags(substr($event["room"], 0, 49))) . "'," . " start='" . addslashes(strip_tags($event["start"])) . "'," . " end='" . addslashes(strip_tags($event["end"])) . "'," . " regstart='" . addslashes(strip_tags($event["regstart"])) . "'," . " regend='" . addslashes(strip_tags($event["regend"])) . "'," . " attenders='" . addslashes(strip_tags($event["attenders"])) . "', " . " agendapageisopen='" . addslashes(strip_tags($event_from_scraping["agendapageisopen"])) . "' " . "WHERE " . " event_id='" . $confId . "'";
} else {
// -- Get additional data for $confId event from manually kept data file
include_once PATH_MENU . "/m_events.php";
$m_events = menu_events();
$event_from_file = array();
foreach ($m_events as $key => $e) {
//TODO: not very efficient...
if (strpos($key, "events/") !== FALSE && isset($e["confid"]) && $e["confid"] == $confId) {
$event_from_file = $e;
$event_from_file["subpath"] = str_replace("events/", "", $key);
}
}
/*
do {
$event_from_file = array_shift($m_events);debug::rr($event_from_file);
} while (!isset($event_from_file["id"]) || ($event_from_file["id"]!=$confId));
*/
foreach (array("subpath", "blurb", "venue", "confid_parent", "surveyresponse", "youtube") as $field) {
if (!isset($event_from_file[$field])) {
$event_from_file[$field] = "";
}
}
foreach (array("surveyid") as $field) {
if (!isset($event_from_file[$field])) {
$event_from_file[$field] = "0";
}
}
$et = event::$eventTypeId;
$event_from_file["etype"] = isset($et[$event_from_file["etype"]]) ? $et[$event_from_file["etype"]] : "0";
// -- Insert database record
$sql = "INSERT INTO " . $this->dbprefix . "event " . " (event_id," . " title," . " room," . " start," . " end," . " regstart," . " regend," . " attenders," . " subpath," . " eventtype," . " blurb," . " venue," . " confid_parent," . " surveyid," . " surveyresponse," . " youtube," . " agendapageisopen) " . "VALUES " . " ('" . addslashes(strip_tags($confId)) . "'," . " '" . addslashes(strip_tags($event["title"])) . "'," . " '" . addslashes(strip_tags(substr($event["room"], 0, 49))) . "'," . " '" . addslashes(strip_tags($event["start"])) . "'," . " '" . addslashes(strip_tags($event["end"])) . "'," . " '" . addslashes(strip_tags($event["regstart"])) . "'," . " '" . addslashes(strip_tags($event["regend"])) . "'," . " '" . addslashes(strip_tags($event["attenders"])) . "'," . " '" . addslashes(strip_tags($event_from_file["subpath"])) . "'," . " '" . addslashes(strip_tags($event_from_file["etype"])) . "'," . " '" . addslashes(strip_tags($event_from_file["blurb"])) . "'," . " '" . addslashes(strip_tags($event_from_file["venue"])) . "'," . " '" . addslashes(strip_tags($event_from_file["confid_parent"])) . "'," . " '" . addslashes(strip_tags($event_from_file["surveyid"])) . "'," . " '" . addslashes(strip_tags($event_from_file["surveyresponse"])) . "'," . " '" . addslashes(strip_tags($event_from_file["youtube"])) . "'," . " '" . addslashes(strip_tags($event_from_scraping["agendapageisopen"])) . "'" . " ) ";
}
/*
[start] => 2015-08-24 08:00
[end] => 2015-08-28 18:00
[title] => Hawking Radiation
[id] => 5173
[regstart] => 2015-05-05
[regend] => 2015-08-01
[room] => 132:028
[chairs] => Array
*/
if (!(bool) $this->query($sql, IS_TESTSERVER)) {
return FALSE;
}
// includes a call to connect
// -------------------------------
// -- Component 'chairs'
// -------------------------------
if (isset($event["chairs"]) && !empty($event["chairs"])) {
$sql = "DELETE FROM " . $this->dbprefix . "person WHERE (event_id='" . $confId . "') AND (person_type='organizer')";
$this->query($sql, IS_TESTSERVER);
foreach ($event["chairs"] as $item) {
foreach (array("av_firstname", "av_lastname", "av_institute", "av_email") as $field) {
if (!isset($item[$field])) {
$item[$field] = "";
}
}
$sql = "REPLACE INTO " . $this->dbprefix . "person " . " (event_id," . " parent_id," . " person_type," . " av_firstname," . " av_lastname," . " av_institute," . " av_email) " . "VALUES " . " ('" . $confId . "'," . " NULL," . " 'organizer'," . " '" . addslashes(strip_tags(substr($item["av_firstname"], 0, 49))) . "'," . " '" . addslashes(strip_tags(substr($item["av_lastname"], 0, 99))) . "'," . " '" . addslashes(strip_tags(substr($item["av_institute"], 0, 199))) . "'," . " '" . addslashes(strip_tags(substr($item["av_email"], 0, 99))) . "' " . " )";
if ((bool) $this->query($sql, IS_TESTSERVER)) {
}
// includes a call to connect
}
// end foreach
}
// end if (isset($event["chairs"])
// -------------------------------
// -- Component 'session' [call before 'contribution']
// -------------------------------
/* KEEP FOR REFERENCE
// Select basic info about sessions for a given event, including conveners:
SELECT
S.*,
GROUP_CONCAT(DISTINCT C.convener SEPARATOR ', ') AS 'conveners'
FROM
session AS S
LEFT JOIN (
SELECT
parent_id,
CONCAT(av_firstname,' ',av_lastname) as 'convener'
FROM
person
WHERE
(person_type='convener')
) AS C ON C.parent_id=S.session_id
GROUP BY
S.session_id
// Select all conveners for a session:
SELECT
event_id,
parent_id,
GROUP_CONCAT(DISTINCT convener SEPARATOR ', ') AS 'conveners'
FROM
(
SELECT
*,
CONCAT(av_firstname,' ',av_lastname) as 'convener'
FROM
person
WHERE
(parent_id='25') AND (person_type='convener')
) AS C
GROUP BY
parent_id
*/
$session = array();
if (isset($event["session"]) && !empty($event["session"])) {
$sql = "DELETE FROM " . $this->dbprefix . "session WHERE (event_id='" . $confId . "')";
$this->query($sql, IS_TESTSERVER);
$sql = "DELETE FROM " . $this->dbprefix . "person WHERE (event_id='" . $confId . "') AND (person_type='convener')";
$this->query($sql, IS_TESTSERVER);
foreach ($event["session"] as $item) {
foreach (array("title") as $field) {
if (!isset($item[$field])) {
$item[$field] = "";
}
}
foreach (array("start", "end") as $field) {
if (!isset($item[$field])) {
$item[$field] = DATETIME_NULL;
}
}
$start = addslashes(strip_tags($item["start"]));
$sql = "REPLACE INTO " . $this->dbprefix . "session " . " (event_id," . " title," . " start," . " end) " . "VALUES " . " ('" . $confId . "'," . " '[" . $start . "] " . addslashes(strip_tags(substr($item["title"], 0, 199))) . "'," . " '" . $start . "'," . " '" . addslashes(strip_tags($item["end"])) . "' " . " )";
if ((bool) $this->query($sql, IS_TESTSERVER)) {
// includes a call to connect
$session_id = $this->insert_id();
$session[$item["title"]] = $session_id;
if (isset($item["conveners"])) {
foreach ($item["conveners"] as $con) {
foreach (array("parent_id", "person_type", "av_firstname", "av_lastname") as $field) {
if (!isset($con[$field])) {
$con[$field] = "";
}
}
$sql = "REPLACE INTO " . $this->dbprefix . "person " . " (event_id," . " parent_id," . " person_type," . " av_firstname," . " av_lastname) " . "VALUES " . " ('" . $confId . "'," . " '" . $session_id . "'," . " 'convener'," . " '" . addslashes(strip_tags(substr($con["av_firstname"], 0, 49))) . "'," . " '" . addslashes(strip_tags(substr($con["av_lastname"], 0, 99))) . "' " . " )";
if ((bool) $this->query($sql, IS_TESTSERVER)) {
}
// includes a call to connect
}
}
// end foreach convener
}
}
// end foreach session
}
// end if (isset($event["session"])
// -------------------------------
// -- Component 'contribution'
// -------------------------------
/* KEEP FOR REFERENCE
// Select basic info about contributions for a given event or session, including speakers:
//TODO:
*/
if (isset($event["contribution"]) && !empty($event["contribution"])) {
$sql = "DELETE FROM " . $this->dbprefix . "contribution WHERE (event_id='" . $confId . "')";
$this->query($sql, IS_TESTSERVER);
$contributionSpeakers[$confId] = array();
foreach ($event["contribution"] as $item) {
if (!isset($item["start"]) || strtolower($item["start"]) != "none") {
foreach (array("title", "description", "start", "end") as $field) {
if (!isset($item[$field])) {
$item[$field] = "";
}
}
if (!isset($item["room"])) {
$item["room"] = $default_room;
}
$session_id = 0;
if (isset($item["session_title"]) && isset($session[$item["session_title"]])) {
$session_id = $session[$item["session_title"]];
}
$end = functions::addDurationToDatetime($item["start"], $item["duration"]);
$sql = "REPLACE INTO " . $this->dbprefix . "contribution " . " (event_id," . " session_id," . " title," . " description," . " room," . " start," . " end) " . "VALUES " . " ('" . $confId . "'," . " '" . $session_id . "'," . " '" . addslashes(strip_tags(substr($item["subject"], 0, 199))) . "'," . " '" . addslashes(strip_tags(str_replace(" ", " ", $item["description"]))) . "'," . " '" . addslashes(strip_tags($item["room"])) . "'," . " '" . addslashes(strip_tags($item["start"])) . "'," . " '" . $end . "' " . " )";
if ((bool) $this->query($sql, IS_TESTSERVER)) {
// includes a call to connect
$contribution_id = $this->insert_id();
if (isset($item["speakers"])) {
foreach ($item["speakers"] as $speaker) {
list($firstname, $lastname) = functions::splitAgendaName($speaker);
$lastname = str_replace(",", "", $lastname);
$sql = "REPLACE INTO " . $this->dbprefix . "person " . " (event_id," . " parent_id," . " person_type," . " av_firstname," . " av_lastname) " . "VALUES " . " ('" . $confId . "'," . " '" . $contribution_id . "'," . " 'speaker'," . " '" . addslashes(strip_tags(substr($firstname, 0, 49))) . "'," . " '" . addslashes(strip_tags(substr($lastname, 0, 99))) . "' " . " )";
if ((bool) $this->query($sql, IS_TESTSERVER)) {
}
// includes a call to connect
}
}
// end foreach speaker
}
// end if query
}
}
// end foreach
}
// end if (isset($event["contribution"])
// -------------------------------
// -- Component 'registrant'
// -------------------------------
/* KEEP FOR REFERENCE
// Select basic info about regstrants for a given event:
//TODO:
*/
if (isset($event["registrant"]) && !empty($event["registrant"])) {
$sql = "DELETE FROM " . $this->dbprefix . "registrant WHERE (event_id='" . $confId . "')";
$this->query($sql, IS_TESTSERVER);
foreach ($event["registrant"] as $item) {
foreach (array("firstname", "familyname", "salutation", "institution", "email", "address", "city", "country", "position", "phone", "homepage") as $field) {
if (!isset($item[$field])) {
$item[$field] = "";
}
}
foreach (array("registrationdate", "arrivaldate", "departuredate") as $field) {
if (!isset($item[$field])) {
$item[$field] = DATETIME_NULL;
}
}
$registrationdate = date("Y-m-d", strtotime($item["registrationdate"]));
if (!$registrationdate) {
$registrationdate = DATETIME_NULL;
}
$accommodation = "";
//TODO
$sql = "REPLACE INTO " . $this->dbprefix . "registrant " . " (event_id," . " av_firstname," . " av_lastname," . " av_salutation," . " av_institute," . " av_email," . " av_address," . " av_city," . " av_residentship," . " position," . " registrationdate," . " phone," . " homepage," . " arrivaldate," . " departuredate," . " accommodation) " . "VALUES " . " ('" . $confId . "'," . " '" . addslashes(strip_tags(substr($item["firstname"], 0, 49))) . "'," . " '" . addslashes(strip_tags(substr($item["familyname"], 0, 99))) . "'," . " '" . addslashes(strip_tags($item["salutation"])) . "'," . " '" . addslashes(strip_tags(substr($item["institution"], 0, 199))) . "'," . " '" . addslashes(strip_tags(substr($item["email"], 0, 99))) . "'," . " '" . addslashes(strip_tags(substr($item["address"], 0, 199))) . "'," . " '" . addslashes(strip_tags(substr($item["city"], 0, 99))) . "'," . " '" . addslashes(strip_tags($item["country"])) . "'," . " '" . addslashes(strip_tags(substr($item["position"], 0, 199))) . "'," . " '" . addslashes(strip_tags($registrationdate)) . "'," . " '" . addslashes(strip_tags(substr($item["phone"], 0, 49))) . "'," . " '" . addslashes(strip_tags(substr($item["homepage"], 0, 199))) . "'," . " '" . addslashes(strip_tags($item["arrivaldate"])) . "'," . " '" . addslashes(strip_tags($item["departuredate"])) . "'," . " '" . addslashes(strip_tags($accommodation)) . "' " . " )";
if ((bool) $this->query($sql, IS_TESTSERVER)) {
}
// includes a call to connect
}
// end foreach
}
// end if (isset($event["registrant"]))
// ------------------------------------------------------------------
return TRUE;
}