.
*/
include("../../oms/include/include-sub.php");
require_once(FILESYSTEMROOTPATH."/oms/include/classes/ImportLayout.php");
require_once(FILESYSTEMROOTPATH."include/classes/CharSet.php");
require_once(FILESYSTEMROOTPATH."include/classes/Validator.php");
$layout = new ImportLayout(Access::ANONYMOUS, "./");
$layout->init();
$layout->echoln("Events import
");
$error = false;
// Download ranges data
$query = "SELECT `id`, `name` FROM `ranges`";
if( $sql_events->query($query) AND $sql_events->getNumRows()>0 ) {
$ranges = array();
while( $row = $sql_events->fetchArray() ) {
$ranges[ $row['name'] ] = $row['id'];
}
$layout->echoln("
INFO: Loaded data from events.ranges (".count($ranges)." items).");
}else {
$layout->echoln("
ERROR: Failed loading data FROM events.ranges.");
$error = true;
}
// Download types data
$query = "SELECT `id`, `name` FROM `types`";
if( $sql_events->query($query) AND $sql_events->getNumRows()>0 ) {
$types = array();
while( $row = $sql_events->fetchArray() ) {
$types[ $row['name'] ] = $row['id'];
}
$layout->echoln("
INFO: Loaded data from events.types (".count($types)." items).");
}else {
$layout->echoln("
ERROR: Failed loading data FROM events.types.");
$error = true;
}
// Download intranetID to bodyCode translation table
$query = "SELECT `BodyCode`, `Intranet_ID` FROM `bodies_historic` ORDER BY `BodyCode`";
if( $sql_ab->query($query) AND $sql_ab->getNumRows()>0 ) {
$bodycodes = array();
while( $row = $sql_ab->fetchArray() ) {
$bodycodes[ $row['Intranet_ID'] ] = $row['BodyCode'];
}
$layout->echoln("
INFO: Loaded data from AB.bodycodes 1, (".count($bodycodes)." items).");
}else {
$layout->echoln("
ERROR: Failed loading data from AB.bodycodes 1.");
$error = true;
}
// Download BodyName to bodyCode translation table
$query = "SELECT `BodyName`, `BodyCode`, `BodyCategory` FROM `bodies_historic` ORDER BY `BodyCode`";
if( $sql_ab->query($query) AND $sql_ab->getNumRows()>0 ) {
$bodynames = array();
while( $row = $sql_ab->fetchArray() ) {
$bodynames[ $row['BodyCode'] ] = array( "BodyName" => $row['BodyName'], "BodyCategory" => $row['BodyCategory']);
}
$layout->echoln("
INFO: Loaded data from AB.bodycodes 2, (".count($bodycodes)." items).");
}else {
$layout->echoln("
ERROR: Failed loading data from AB.bodycodes 2.");
$error = true;
}
// Download data from intranet
$intranetdata = array();
$query = "SELECT e.event_id, e.short_description, e.description, e.approve, e.web_site, e.phone_number, e.email, e.participation_number,
e.participation_fee, e.organizer, e.location, e.activity_level, e.type, e.app_end_date, e.app_start_date,
e.to_date, e.from_date, e.owner_group_id AS `Intranet_ID`, g.group_name,
(SELECT group_id FROM groups WHERE group_name=e.co_organizer1 OR group_name_latin=e.co_organizer1) co_org1,
(SELECT group_id FROM groups WHERE group_name=e.co_organizer2 OR group_name_latin=e.co_organizer2) co_org2
FROM events e
INNER JOIN groups g ON e.group_id=g.group_id
WHERE e.approve!='FALSE' AND NOT(e.approve='PENDING' AND e.app_end_date<=now())";
if( $sql_intranet->query($query) AND $sql_intranet->getnumrows()>0 ) {
while( $row = $sql_intranet->fetchAssoc() ) {
if( array_key_exists($row['Intranet_ID'], $bodycodes) ) {
$curitem = $row['event_id'];
}else {
$layout->echoln("
ERROR: cannot find bodyCode for ".$row['group_name'].", skipping");
continue;
}
if ( in_array($bodynames[ $bodycodes[ $row['Intranet_ID'] ] ]['BodyCategory'], array('Locals', 'Contacts'))){
$intranetdata[$curitem]['localOrganisers'] = $bodycodes[ $row['Intranet_ID']];
}else{
$intranetdata[$curitem]['europeanOrganisers'] = $bodycodes[ $row['Intranet_ID']];
}
if ( in_array($bodynames[ $bodycodes[ $row['co_org1'] ] ]['BodyCategory'], array('Locals', 'Contacts'))){
if( stripos( $intranetdata[$curitem]['localOrganisers'], $bodycodes[ $row['co_org1']] ) === false ){
$intranetdata[$curitem]['localOrganisers'] .= (strlen($intranetdata[$curitem]['localOrganisers'])>0?",":"").$bodycodes[ $row['co_org1']];
}
}else if (array_key_exists($row['co_org1'], $bodycodes) ){
if( stripos( $intranetdata[$curitem]['europeanOrganisers'], $bodycodes[ $row['co_org1']] ) === false ){
$intranetdata[$curitem]['europeanOrganisers'] .= (strlen($intranetdata[$curitem]['europeanOrganisers'])>0?",":"").$bodycodes[ $row['co_org1']];
}
}
if ( in_array($bodynames[ $bodycodes[ $row['co_org2'] ] ]['BodyCategory'], array('Locals', 'Contacts'))){
if( stripos( $intranetdata[$curitem]['localOrganisers'], $bodycodes[ $row['co_org2']] ) === false ){
$intranetdata[$curitem]['localOrganisers'] .= (strlen($intranetdata[$curitem]['localOrganisers'])>0?",":"").$bodycodes[ $row['co_org2']];
}
}else if (array_key_exists($row['co_org2'], $bodycodes) ){
if( stripos( $intranetdata[$curitem]['europeanOrganisers'], $bodycodes[ $row['co_org2']] ) === false ){
$intranetdata[$curitem]['europeanOrganisers'] .= (strlen($intranetdata[$curitem]['europeanOrganisers'])>0?",":"").$bodycodes[ $row['co_org2']];
}
}
$intranetdata[$curitem]['title'] = $row['group_name'];
if( $row['phone_number'] == "" ) {
// Do nothing
}else if( Validator::phone($row['phone_number']) ) {
$intranetdata[$curitem]['contactPhone'] = $row['phone_number'];
}else if( Validator::phone("+".$row['phone_number']) ) {
$intranetdata[$curitem]['contactPhone'] = "+".$row['phone_number'];
}else if( substr($row['phone_number'], 0, 2) == "00" ) {
if( Validator::phone("+".substr($row['phone_number'], 2)) ) {
$intranetdata[$curitem]['contactPhone'] = "+".substr($row['phone_number'], 2);
}
}
if( Validator::sqlDate($row['app_end_date']) ) {
$intranetdata[$curitem]['applicationEnd'] = $row['app_end_date'];
}else {
$layout->echoln("
ERROR: Invalid app_end_date date for ".$row['group_name'].": ".$row['app_end_date']);
}
if( Validator::sqlDate($row['app_start_date']) ) {
$intranetdata[$curitem]['applicationStart'] = $row['app_start_date'];
}else {
$layout->echoln("
ERROR: Invalid app_start_date date for ".$row['group_name'].": ".$row['app_start_date']);
}
if( Validator::sqlDate($row['to_date']) ) {
$intranetdata[$curitem]['departure'] = $row['to_date'];
}else {
$layout->echoln("
ERROR: Invalid to_date date for ".$row['group_name'].": ".$row['to_date']);
}
if( Validator::sqlDate($row['from_date']) ) {
$intranetdata[$curitem]['arrival'] = $row['from_date'];
}else {
$layout->echoln("
ERROR: Invalid from_date date for ".$row['group_name'].": ".$row['FoundedDate']);
}
if( strlen($row['web_site'])>0 ) {
$intranetdata[$curitem]['url'] = $row['web_site'];
if( strlen($intranetdata[$curitem]['url'])>4 AND substr($intranetdata[$curitem]['url'], 0, 7)!="http://" ) $intranetdata[$curitem]['url'] = "http://".$intranetdata[$curitem]['url'];
if( strlen($intranetdata[$curitem]['url'])>4 AND strrpos($intranetdata[$curitem]['url'], "/")<10 ) $intranetdata[$curitem]['url'] .= "/";
}
if( array_key_exists(ucwords(strtolower($row['activity_level'])), $ranges) ) {
$intranetdata[$curitem]['range'] = $ranges[ucwords(strtolower($row['activity_level']))];
}else {
$intranetdata[$curitem]['range'] = $ranges['Local'];
}
if( array_key_exists(ucwords(strtolower($row['type'])), $types) ) {
$intranetdata[$curitem]['type'] = $types[ucwords(strtolower($row['type']))];
}else if ( stripos($intranetdata[$curitem]['europeanOrganisers'], "XNE") !== false) {
$intranetdata[$curitem]['type'] = $types['Network Meeting'];
}else{
$intranetdata[$curitem]['type'] = $types['Recreation'];
}
if( strlen($row['email'])>0 ) $intranetdata[$curitem]['contactEmail'] = $row['email'];
if( strlen($row['description'])>0 ) $intranetdata[$curitem]['description'] = $row['description'];
if( strlen($row['short_description'])>0 ) $intranetdata[$curitem]['shortDescription'] = $row['short_description'];
if ( $row['approve'] == "TRUE" AND $row['from_date']<=date("Y-m-d") ) {
$intranetdata[$curitem]['status'] = 10;
$intranetdata[$curitem]['omsHandlesApplications'] = 0;
}else if ( $row['approve'] == "TRUE" ) {
$intranetdata[$curitem]['status'] = 4;
$intranetdata[$curitem]['omsHandlesApplications'] = 1;
}else if ( $row['approve'] == "PENDING" ) {
$intranetdata[$curitem]['status'] = 3;
$intranetdata[$curitem]['omsHandlesApplications'] = 1;
}else {
$layout->echoln("
ERROR: Invalid status for ".$row['group_name'].": ".$row['status']);
}
if( is_numeric($row['participation_fee']) ) {
$intranetdata[$curitem]['fee'] = $row['participation_fee'];
}else if ( $row['participation_fee'] == "" ){
$intranetdata[$curitem]['fee'] = "0.00";
}else {
$row['participation_fee'] = str_replace(" ", "", $row['participation_fee']);
$row['participation_fee'] = str_replace(",", ".", $row['participation_fee']);
$row['participation_fee'] = str_replace(".-", ".00", $row['participation_fee']);
$row['participation_fee'] = str_replace("€", "", $row['participation_fee']);
$row['participation_fee'] = str_replace("£", "", $row['participation_fee']);
$row['participation_fee'] = str_replace("~", "", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("euros", "", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("euro", "", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("eur", "", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("max.", "", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("max", "", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("none", "0.00", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("non", "0.00", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("free", "0.00", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("nofee", "0.00", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("-", "0.00", $row['participation_fee']);
$row['participation_fee'] = str_ireplace("Noparticipationfee", "0.00", $row['participation_fee']);
if( is_numeric($row['participation_fee']) ) {
$intranetdata[$curitem]['fee'] = $row['participation_fee'];
}else if( is_numeric(substr($row['participation_fee'], 0, 3) ) ){
$intranetdata[$curitem]['fee'] = substr($row['participation_fee'], 0, 3);
}else if( is_numeric(substr($row['participation_fee'], 0, 2) ) ){
$intranetdata[$curitem]['fee'] = substr($row['participation_fee'], 0, 3);
}
}
$intranetdata[$curitem]['place'] = $row['location'];
$intranetdata[$curitem]['participants'] = $row['participation_number'];
$intranetdata[$curitem]['id'] = $row['event_id'];
}
}else {
$layout->echoln("
ERROR: Failed loading ".$types[$k]." data from Intranet.");
$error = true;
}
$layout->echoln("
INFO: Loaded data from intranet: ".count($intranetdata)." out of ".$sql_intranet->getnumrows()." events.");
//time to insert the data to the database!
if( !$error ) {
if ( ! $sql_events->query("TRUNCATE TABLE events.events")) {
$layout->echoln("
TRUNCATE ERROR: ".$sql_events->getErrorMsg().".");
exit;
}
foreach ($intranetdata AS $k => $event) {
$query = "INSERT INTO events.events SET ";
foreach ($event AS $key => $value){
$value=str_replace("'", "\'", $value);
$query .= "`".$key."` = '".$value."', ";
}
$query=substr($query, 0, -2);
if ($sql_events->query($query)) $w++;
else {
$layout->echoln("
INSERT ERROR: ".$sql_events->getErrorMsg().".");
$layout->echoln("
".$query);
}
}
$layout->echoln("
INFO: Inserted to OMS: ".$w." events.");
}else {
$layout->echoln("
ERROR: blocking error found, not synchronising.");
}
$layout->echoln("
");
$layout->echoln("Finished");
$layout->page_footer();
?>