-- Create the Events table
CREATE TABLE Events (
id INT AUTO_INCREMENT PRIMARY KEY,
Author INT NOT NULL,
Title VARCHAR(255) NOT NULL,
Description TEXT NOT NULL,
Start DATETIME NOT NULL,
End DATETIME NOT NULL,
Button VARCHAR(255) DEFAULT "",
URL VARCHAR(255) NOT NULL,
Location VARCHAR(255) NOT NULL,
Location_URL VARCHAR(255) NOT NULL,
`Virtual` TINYINT NOT NULL,
Multiple_dates TINYINT DEFAULT 0,
Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
Recurring TINYINT DEFAULT 0,
On_Demand TINYINT DEFAULT 0
) ENGINE=InnoDB;
-- Create the Events_Vendors table
CREATE TABLE Events_Vendors (
vid INT AUTO_INCREMENT PRIMARY KEY,
Code VARCHAR(3) DEFAULT "",
Vendor VARCHAR(255) NOT NULL,
url VARCHAR(18) DEFAULT ""
) ENGINE=InnoDB;
-- Create the Events_Vendors_Tags table
CREATE TABLE Events_Vendors_Tags (
event_id INT NOT NULL,
vendor_id INT NOT NULL,
PRIMARY KEY (event_id, vendor_id)
-- FOREIGN KEY (event_id) REFERENCES Events(id) ON DELETE CASCADE,
-- FOREIGN KEY (vendor_id) REFERENCES Events_Vendors(vid) ON DELETE CASCADE
) ENGINE=InnoDB;
-- Create the Events_Countries table
CREATE TABLE Events_Countries (
cid INT AUTO_INCREMENT PRIMARY KEY,
Code VARCHAR(3) DEFAULT "",
Country VARCHAR(255) NOT NULL
) ENGINE=InnoDB;
-- Create the Events_Countries_Tags table
CREATE TABLE Events_Countries_Tags (
event_id INT NOT NULL,
country_id INT NOT NULL,
PRIMARY KEY (event_id, country_id)
-- FOREIGN KEY (event_id) REFERENCES Events(id) ON DELETE CASCADE,
-- FOREIGN KEY (country_id) REFERENCES Events_Countries(cid) ON DELETE CASCADE
) ENGINE=InnoDB;
-- Insert sample events
INSERT INTO Events (Author, Title, Description, Start, End, URL, Location, Location_URL, `Virtual`, Multiple_dates, Recurring, On_Demand)
VALUES
(1, 'Event 1', 'Description for Event 1', '2024-01-01 10:00:00', '2024-01-01 12:00:00', 'https://event1.com', 'Location 1', 'https://location1.com', 1, 0, 0, 0),
(2, 'Event 2', 'Description for Event 2', '2024-02-01 10:00:00', '2024-02-01 12:00:00', 'https://event2.com', 'Location 2', 'https://location2.com', 0, 0, 0, 0);
-- Insert sample vendors
INSERT INTO Events_Vendors (Vendor, Code, url) VALUES ('Avaya','ava', 'avaya.svg'), ('Cisco','cis', 'cisco.svg'), ('Extreme','ext','extreme.svg');
-- Insert sample vendor-event relationships
INSERT INTO Events_Vendors_Tags (event_id, vendor_id) VALUES (1, 1), (1, 2), (2, 3);
-- Insert sample countries
INSERT INTO Events_Countries (Country, Code) VALUES ('Singapore','sg'), ('UK','uk'), ('Germany','de');
-- Insert sample country-event relationships
INSERT INTO Events_Countries_Tags (event_id, country_id) VALUES (1, 1), (1, 2), (2, 3);
<?php
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: content-type, accept, origin,accept-language ");
// ini_set('display_errors', 'On');
ini_set('default_charset', 'utf-8');
// error_reporting(E_ALL | E_STRICT);
// include '../../connection.php';
if (!$mysqli) {
echo [];
exit;
}
$vendors = array();
$countries = array();
$tz = 'Europe/London';
$searchConditions = [];
$c = '';
$v = 'cis';
if (isset($v) && $v !== '') {
// `v` will contain an array of vendor codes (e.g., "ava cis pal")
$vendors = explode(' ', $v);
$vendorCodes = implode("','", array_map('addslashes', $vendors));
// Updated condition to check if the vendor code exists in the array
$searchConditions[] = "EXISTS (
SELECT 1
FROM `Events_Vendors_Tags` evt2
INNER JOIN `Events_Vendors` ev ON ev.vid = evt2.vendor_id
WHERE evt2.event_id = e.id
AND ev.Code IN ('$vendorCodes')
)";
}
if (isset($c) && $c !== '') {
// `c` will contain an array of country codes (e.g., "uk us ca")
$countries = explode(' ', $c);
$countryCodes = implode("','", array_map('addslashes', $countries));
// Updated condition to check if the country code exists in the array
$searchConditions[] = "EXISTS (
SELECT 1
FROM `Events_Countries_Tags` ect2
INNER JOIN `Events_Countries` ec ON ec.cid = ect2.country_id
WHERE ect2.event_id = e.id
AND ec.Code IN ('$countryCodes')
)";
} else {
// Exclude events tagged with the global country code (assuming "global" is represented by "38")
$searchConditions[] = "NOT EXISTS (
SELECT 1
FROM `Events_Countries_Tags` ect2
WHERE ect2.event_id = e.id
AND ect2.country_id = 38
)";
}
// Combine all conditions
$search = implode(' AND ', $searchConditions);
if (empty($search)) {
$search = "1"; // Default condition (no filtering)
}
//tz will be a string timezone that I will use to convert the start and end dates to the correct timezone later
if (isset($_GET['tz'])){
$tz = $_GET['tz'];
}
// Build and execute the SQL query
$sql = "
SELECT
e.Title,
e.Description,
e.Start,
e.End,
e.Button,
e.URL,
e.Location,
e.Location_URL,
e.Virtual,
GROUP_CONCAT(
CONCAT(
'{\"url\":\"', IFNULL(v.url, ''), '\",',
'\"Vendor\":\"', IFNULL(v.Vendor, ''), '\",',
'\"Code\":\"', IFNULL(v.Code, ''), '\"}'
)
SEPARATOR ','
) AS Vendors
FROM `Events` e
LEFT JOIN `Events_Vendors_Tags` evt ON evt.event_id = e.id
LEFT JOIN `Events_Vendors` v ON v.vid = evt.vendor_id
LEFT JOIN `Events_Countries_Tags` ect ON ect.event_id = e.id
LEFT JOIN `Events_Countries` c ON c.cid = ect.country_id
WHERE $search
GROUP BY e.id
ORDER BY e.`Start` ASC;
";
// echo $sql;
try {
$query = $mysqli->query($sql);
if (!$query) {
echo [];
}
// Process each row to transform the data
$events = [];
while ($row = $query->fetch_assoc()) {
// Decode strings
foreach ($row as $key => $value) {
if (is_string($value)) {
$row[$key] = htmlspecialchars_decode($value, ENT_QUOTES);
}
}
// Parse Vendors column
if (!empty($row['Vendors'])) {
$vendors = '[' . $row['Vendors'] . ']'; // Wrap in array brackets
$row['Vendors'] = json_decode($vendors, true);
$unique_vendors = [];
foreach ($vendors as $vendor) {
$unique_vendors[$vendor['Code']] = $vendor;
}
$row['Vendors'] = array_values($unique_vendors);
} else {
$row['Vendors'] = [];
}
// Initialize event if not already in $events
$eventId = $row['EventID'];
if (!isset($events[$eventId])) {
$events[$eventId] = [
'Title' => $row['Title'],
'Description' => $row['Description'],
'Start' => $row['Start'],
'End' => $row['End'],
'Button' => $row['Button'],
'URL' => $row['URL'],
'Location' => $row['Location'],
'Location_URL' => $row['Location_URL'],
'Virtual' => $row['Virtual'],
'Vendors' => $row['Vendors'],
];
}
}
// Convert Vendors back to indexed arrays and handle timezone adjustments
$search_results = [];
foreach ($events as $event) {
$event['Vendors'] = array_values($event['Vendors']);
// Convert dates to desired timezone
$startDate = new DateTime($event['Start'], new DateTimeZone('UTC'));
$endDate = new DateTime($event['End'], new DateTimeZone('UTC'));
$startDate->setTimezone(new DateTimeZone($tz));
$endDate->setTimezone(new DateTimeZone($tz));
if ($event['Virtual'] == 1) {
$event['StartTime'] = $startDate->format("G:i T");
$event['StartTime'] = str_replace('+08', 'SGT', $event['StartTime']);
}
$event['ShortMonth'] = $startDate->format("M");
$diff = $endDate->diff($startDate);
$event['Duration'] = $diff->d > 1
? $diff->format('%d days')
: $diff->format('%hh %Im');
$event['Start'] = $startDate->format('Y-m-d\TH:i:s');
$event['End'] = $endDate->format('Y-m-d\TH:i:s');
$search_results[] = $event;
}
echo json_encode($search_results);
} catch (Exception $e) {
error_log("SQL Error: " . $e->getMessage()); // Log the error
echo json_encode(["error" => $e->getMessage()]); // Output error to browser
exit; // Stop execution
}
// $mysqli->close();
?>