db->query("SELECT * FROM " . DB_PREFIX . "events_scheduler ORDER BY date_time ASC");
return $query->rows;
}
public function getEvent($event_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$event_id . "'");
return $query->row;
}
public function addEvent($data) {
$date_end = strtotime($data['class_date'] . " + " . $data['class_length']);
$this->db->query("INSERT INTO " . DB_PREFIX . "events_scheduler SET class_name = '" . $this->db->escape($data['name']) . "', model = '" . $this->db->escape($data['model']) . "', skill_level = '" . $this->db->escape($data['skill_level']) . "', class_days = '" . $this->db->escape(serialize($data['class_days'])) . "', class_length = '" . $this->db->escape($data['class_length']) . "', date_time = '" . (int)strtotime($data['class_date'] . ' ' . $data['class_time']) . "', location = '" . $this->db->escape($data['location']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', cost = '" . (float)$data['cost'] . "', seats = '" . (int)$data['seats'] . "', instructor_id = '" . (int)$data['instructor'] . "', description = '" . $this->db->escape($data['description']) . "', instructor_tab = '" . $this->db->escape($data['instructor_tab']) . "', date_end = '" . (int)$date_end . "', refundable = '" . (int)$data['refundable'] . "'");
$event_id = $this->db->getLastId();
$end_date = date('Y-m-d', strtotime($data['class_date']));
$this->db->query("INSERT INTO " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['seats'] . "', price = '" . (float)$data['cost'] . "', subtract = '1', status = '" . (int)$data['status'] . "', end_date = '" . $this->db->escape($end_date) . "', event_id = '" . (int)$event_id . "', shipping = '0', date_added = NOW(), date_modified = NOW()");
$product_id = $this->db->getLastId();
$this->db->query("UPDATE " . DB_PREFIX . "events_scheduler SET product_id ='" .$product_id."' WHERE event_id = '" . (int)$event_id . "'");
$this->db->query("INSERT INTO " . DB_PREFIX . "product_description SET product_id = '" . (int)$product_id . "', language_id = '1', name = '" . $this->db->escape($data['name']) . "', description = '" . $this->db->escape($data['description']) . "'");
$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_store SET product_id = '" . (int)$product_id . "', store_id = '" . (int)$this->config->get('config_store_id') . "'");
return;
}
public function editEvent($event_id, $data) {
$date_end = strtotime($data['class_date'] . " + " . $data['class_length']);
$this->db->query("UPDATE " . DB_PREFIX . "events_scheduler SET class_name = '" . $this->db->escape($data['name']) . "', model = '" . $this->db->escape($data['model']) . "', skill_level = '" . $this->db->escape($data['skill_level']) . "', class_days = '" . $this->db->escape(serialize($data['class_days'])) . "', class_length = '" . $this->db->escape($data['class_length']) . "', date_time = '" . (int)strtotime($data['class_date'] . ' ' . $data['class_time']) . "', location = '" . $this->db->escape($data['location']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', cost = '" . (float)$data['cost'] . "', seats = '" . (int)$data['seats'] . "', instructor_id = '" . (int)$data['instructor'] . "', description = '" . $this->db->escape($data['description']) . "', instructor_tab = '" . $this->db->escape($data['instructor_tab']) . "', date_end = '" . (int)$date_end . "', refundable = '" . (int)$data['refundable'] . "' WHERE event_id = '" . (int)$event_id . "'");
$filled = $this->db->query("SELECT filled FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$event_id . "'");
$quantity = (int)$data['seats'] - (int)$filled->row['filled'];
$end_date = date('Y-m-d', strtotime($data['class_date']));
$this->db->query("UPDATE " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$quantity . "', price = '" . (float)$data['cost'] . "', status = '" . (int)$data['status'] . "', end_date = '" . $this->db->escape($end_date) . "', date_modified = NOW() WHERE event_id = '" . (int)$event_id . "'");
$product_id = $this->db->query("SELECT product_id FROM " . DB_PREFIX . "product WHERE event_id = '" . (int)$event_id . "'");
$this->db->query("UPDATE " . DB_PREFIX . "product_description SET name = '" . $this->db->escape($data['name']) . "', description = '" . $this->db->escape($data['description']) . "' WHERE product_id = '" . (int)$product_id->row['product_id'] . "'");
return;
}
public function deleteEvent($event_id) {
$this->db->query("DELETE FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$event_id . "'");
$this->db->query("DELETE FORM " . DB_PREFIX . "events_wait_list WHERE event_id = '" . (int)$event_id . "'");
$product_id = $this->db->query("SELECT product_id FROM " . DB_PREFIX . "product WHERE event_id = '" . (int)$event_id . "'");
$this->db->query("DELETE FROM " . DB_PREFIX . "product WHERE event_id = '" . (int)$event_id . "'");
$this->db->query("DELETE FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id->row['product_id'] . "'");
$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_store WHERE product_id = '" . (int)$product_id->row['product_id'] . "'");
return;
}
public function getInstructors($data = array()) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "instructors ORDER BY instructor_name ASC");
return $query->rows;
}
public function getInstructor($instructor_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "instructors WHERE instructor_id = '" . (int)$instructor_id . "'");
return $query->row;
}
public function getInstructorName($instructor_id) {
$query = $this->db->query("SELECT instructor_name FROM " . DB_PREFIX . "instructors WHERE instructor_id = '" . (int)$instructor_id . "'");
if ($query->num_rows) {
return $query->row['instructor_name'];
} else {
return;
}
}
public function addInstructor($data) {
$this->db->query("INSERT INTO " . DB_PREFIX . "instructors SET instructor_name = '" . $this->db->escape($data['instructor_name']) . "', bio = '" . $this->db->escape($data['bio']) . "'");
return;
}
public function editInstructor($instructor_id, $data) {
$this->db->query("UPDATE " . DB_PREFIX . "instructors SET instructor_name = '" . $this->db->escape($data['instructor_name']) . "', bio = '" . $this->db->escape($data['bio']) . "' WHERE instructor_id = '" . (int)$instructor_id . "'");
return;
}
public function deleteInstructor($instructor_id) {
$this->db->query("DELETE FROM " . DB_PREFIX . "instructors WHERE instructor_id = '" . (int)$instructor_id . "'");
return;
}
public function getRoster($event_id) {
$return_data = array();
$query = $this->db->query("SELECT roster FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$event_id . "'");
if (!empty($query->row['roster'])) {
foreach (unserialize($query->row['roster']) as $roster) {
$return_data[] = array(
'student_id' => $roster['student_id'],
'name' => $this->getStudentName($roster['student_id']),
'date_added' => $roster['date_added']
);
}
}
return $return_data;
}
public function getWaitListCount($event_id) {
$query = $this->db->query("SELECT COUNT(*) as total FROM " . DB_PREFIX . "events_wait_list WHERE event_id = '" . (int)$event_id . "'");
if ($query->num_rows) {
return $query->row['total'];
} else {
return 0;
}
}
public function getWaitListStudents($event_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "events_wait_list WHERE event_id = '" . (int)$event_id . "'");
return $query->rows;
}
public function addToClass($data) {
$student_data = array(
'student_id' => $data['customer_id'],
'event_id' => $data['event_id']
);
$this->addStudent($student_data);
$query = $this->db->query("SELECT seats, filled FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$data['event_id'] . "'");
if ($query->row['seats'] < $query->row['filled']) {
$this->db->query("UPDATE " . DB_PREFIX . "events_scheduler SET seats = '" . (int)$query->row['filled'] . "' WHERE event_id = '" . (int)$data['event_id'] . "'");
}
$this->db->query("DELETE FROM " . DB_PREFIX . "events_wait_list WHERE event_id = '" . (int)$data['event_id'] . "' AND customer_id = '" . (int)$data['customer_id'] . "'");
$event_info = $this->db->query("SELECT * FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$data['event_id'] . "'");
$customer_email = $this->db->query("SELECT email FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$data['customer_id'] . "'");
if ($customer_email->num_rows && $customer_email->row['email'] != "") {
$subject = sprintf($this->language->get('text_add_event_subject'), $event_info->row['class_name']);
$image = HTTP_IMAGE . $this->config->get('config_logo');
$logo = str_replace(' ', '%20', $image);
$html = '';
$html .= '';
$html .= sprintf($this->language->get('text_add_event_message'), $event_info->row['class_name']);
$html .= '
';
$html .= '';
$html .= '
';
$html .= '';
$html .= '| ' . $this->language->get('column_class_name') . ': | ';
$html .= '' . $event_info->row['class_name'] . ' | ';
$html .= '
';
$html .= '';
$html .= '| ' . $this->language->get('column_date_time') . ': | ';
$html .= '' . date($this->language->get('date_format_short'), $event_info->row['date_time']) . ' at ' . date($this->language->get('time_format'), $event_info->row['date_time']) . ' | ';
$html .= '
';
if ($event_info->row['location'] != "") {
$html .= '';
$html .= '| ' . $this->language->get('column_location') . ': | ';
$html .= '' . $event_info->row['location'] . ' | ';
$html .= '
';
}
if ($event_info->row['telephone'] != "") {
$html .= '';
$html .= '| ' . $this->language->get('column_telephone') . ': | ';
$html .= '' . $event_info->row['telephone'] . ' | ';
$html .= '
';
}
$html .= '
';
$html .= '
';
$html .= $this->config->get('config_name');
$html .= '
';
$mail = new Mail();
$mail->protocol = $this->config->get('config_mail_protocol');
$mail->parameter = $this->config->get('config_mail_parameter');
$mail->hostname = $this->config->get('config_smtp_host');
$mail->username = $this->config->get('config_smtp_username');
$mail->password = $this->config->get('config_smtp_password');
$mail->port = $this->config->get('config_smtp_port');
$mail->timeout = $this->config->get('config_smtp_timeout');
$mail->setTo($customer_email->row['email']);
$mail->setFrom($this->config->get('config_email'));
$mail->setSender($this->config->get('config_name'));
$mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
$mail->setHtml($html);
$mail->send();
}
return;
}
public function addToWaitList($data) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "events_wait_list WHERE event_id = '" . (int)$data['event_id'] . "' AND customer_id = '" . (int)$data['student_id'] . "'");
if (!$query->num_rows) {
$this->db->query("INSERT INTO " . DB_PREFIX . "events_wait_list SET event_id = '" . (int)$data['event_id'] . "', customer_id = '" . (int)$data['student_id'] . "'");
$event_info = $this->db->query("SELECT * FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$data['event_id'] . "'");
$customer_email = $this->db->query("SELECT email FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$data['student_id'] . "'");
if ($customer_email->num_rows && $customer_email->row['email'] != "") {
$subject = sprintf($this->language->get('text_add_wait_subject'), $event_info->row['class_name']);
$image = HTTP_IMAGE . $this->config->get('config_logo');
$logo = str_replace(' ', '%20', $image);
$html = '';
$html .= '';
$html .= sprintf($this->language->get('text_add_wait_message'), $event_info->row['class_name']);
$html .= '
';
$html .= '';
$html .= '
';
$html .= '';
$html .= '| ' . $this->language->get('column_class_name') . ': | ';
$html .= '' . $event_info->row['class_name'] . ' | ';
$html .= '
';
$html .= '';
$html .= '| ' . $this->language->get('column_date_time') . ': | ';
$html .= '' . date($this->language->get('date_format_short'), $event_info->row['date_time']) . ' at ' . date($this->language->get('time_format'), $event_info->row['date_time']) . ' | ';
$html .= '
';
if ($event_info->row['location'] != "") {
$html .= '';
$html .= '| ' . $this->language->get('column_location') . ': | ';
$html .= '' . $event_info->row['location'] . ' | ';
$html .= '
';
}
if ($event_info->row['telephone'] != "") {
$html .= '';
$html .= '| ' . $this->language->get('column_telephone') . ': | ';
$html .= '' . $event_info->row['telephone'] . ' | ';
$html .= '
';
}
$html .= '
';
$html .= '
';
$html .= $this->config->get('config_name');
$html .= '
';
$mail = new Mail();
$mail->protocol = $this->config->get('config_mail_protocol');
$mail->parameter = $this->config->get('config_mail_parameter');
$mail->hostname = $this->config->get('config_smtp_host');
$mail->username = $this->config->get('config_smtp_username');
$mail->password = $this->config->get('config_smtp_password');
$mail->port = $this->config->get('config_smtp_port');
$mail->timeout = $this->config->get('config_smtp_timeout');
$mail->setTo($customer_email->row['email']);
$mail->setFrom($this->config->get('config_email'));
$mail->setSender($this->config->get('config_name'));
$mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
$mail->setHtml($html);
$mail->send();
}
return true;
} else {
return false;
}
}
public function removeFromList($events_wait_list_id) {
$this->db->query("DELETE FROM " . DB_PREFIX . "events_wait_list WHERE events_wait_list_id = '" . (int)$events_wait_list_id . "'");
return;
}
public function emptyWaitList($event_id) {
$this->db->query("DELETE FROM " . DB_PREFIX . "events_wait_list WHERE event_id = '" . (int)$event_id . "'");
return;
}
public function getClassName($event_id) {
$query = $this->db->query("SELECT class_name FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$event_id . "'");
return $query->row['class_name'];
}
public function getSeats($event_id) {
$query = $this->db->query("SELECT seats FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$event_id . "'");
return $query->row['seats'];
}
public function getAvailable($event_id) {
$query = $this->db->query("SELECT seats, filled FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$event_id . "'");
$available = $query->row['seats'] - $query->row['filled'];
return $available;
}
public function updateSeats($event_id, $seats) {
$this->db->query("UPDATE " . DB_PREFIX . "events_scheduler SET filled = (filled - " . (int)$seats . ") WHERE event_id = '" . (int)$event_id . "'");
return;
}
public function getStudentName($student_id) {
$query = $this->db->query("SELECT CONCAT(firstname, ' ', lastname) as name FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$student_id . "'");
return $query->row['name'];
}
public function checkStudent($data) {
$exists = false;
$query = $this->db->query("SELECT roster FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$data['event_id'] . "'");
if (!empty($query->row['roster'])) {
foreach (unserialize($query->row['roster']) as $roster) {
if ($roster['student_id'] == $data['student_id']) {
$exists = true;
break;
}
}
}
return $exists;
}
public function addStudent($data) {
$new_array = array();
$query = $this->db->query("SELECT roster, seats, filled FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$data['event_id'] . "'");
$filled = $query->row['filled'];
$seats = $query->row['seats'];
if (!empty($query->row['roster'])) {
foreach (unserialize($query->row['roster']) as $student) {
$new_array[] = array(
'student_id' => $student['student_id'],
'date_added' => $student['date_added']
);
}
}
$new_array[] = array(
'student_id' => $data['student_id'],
'date_added' => time()
);
if ($filled > $seats) {
$seats = $filled;
}
$this->db->query("UPDATE " . DB_PREFIX . "events_scheduler SET roster = '" . $this->db->escape(serialize($new_array)) . "', seats = '" . (int)$seats . "', filled = (filled + 1) WHERE event_id = '" . (int)$data['event_id'] . "'");
$new_array = null;
unset($new_array);
return;
}
public function deleteStudent($event_id, $student_id) {
$new_array = array();
$query = $this->db->query("SELECT roster FROM " . DB_PREFIX . "events_scheduler WHERE event_id = '" . (int)$event_id . "'");
foreach (unserialize($query->row['roster']) as $roster) {
if ($student_id != $roster['student_id']) {
$new_array[] = array(
'student_id' => $roster['student_id'],
'date_added' => $roster['date_added']
);
}
}
$this->db->query("UPDATE " . DB_PREFIX . "events_scheduler SET roster = '" . $this->db->escape(serialize($new_array)) . "' WHERE event_id = '" . (int)$event_id . "'");
$new_array = null;
unset($new_array);
return;
}
public function setupDb() {
$this->db->query("CREATE TABLE IF NOT EXISTS " . DB_PREFIX . "events_scheduler (
event_id int(11) NOT NULL auto_increment,
class_name varchar(150) COLLATE utf8_general_ci NOT NULL DEFAULT '',
model varchar(50) COLLATE utf8_general_ci NOT NULL DEFAULT '',
sku varchar(50) COLLATE utf8_general_ci NOT NULL DEFAULT '',
skill_level varchar(40) COLLATE utf8_general_ci NOT NULL DEFAULT '',
class_length varchar(40) COLLATE utf8_general_ci NOT NULL DEFAULT '',
class_days text COLLATE utf8_general_ci NOT NULL DEFAULT '',
date_time int(11) NOT NULL DEFAULT 0,
location varchar(200) COLLATE utf8_general_ci NOT NULL DEFAULT '',
telephone varchar(25) COLLATE utf8_general_ci NOT NULL DEFAULT '',
cost decimal(15,4) NOT NULL DEFAULT 0.0000,
seats int(11) NOT NULL DEFAULT 0,
filled int(11) NOT NULL DEFAULT 0,
instructor_tab varchar(50) COLLATE utf8_general_ci NOT NULL DEFAULT '',
roster mediumtext COLLATE utf8_general_ci NOT NULL DEFAULT '',
instructor_id int(11) NOT NULL DEFAULT 0,
description mediumtext COLLATE utf8_general_ci NOT NULL DEFAULT '',
refundable tinyint(1) NOT NULL DEFAULT 0,
date_end int(11) NOT NULL DEFAULT 0,
product_id int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (event_id)
);");
$this->db->query("CREATE TABLE IF NOT EXISTS " . DB_PREFIX . "events_wait_list (
events_wait_list_id int(11) NOT NULL auto_increment,
event_id int(11) NOT NULL,
customer_id int(11) NOT NULL,
PRIMARY KEY (events_wait_list_id)
);");
$this->db->query("CREATE TABLE IF NOT EXISTS " . DB_PREFIX . "instructors (
instructor_id int(11) NOT NULL auto_increment,
instructor_name varchar(150) COLLATE utf8_general_ci NOT NULL DEFAULT '',
bio mediumtext COLLATE utf8_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (instructor_id)
);");
$results = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "events_scheduler LIKE 'instructor_tab'");
if ($results->num_rows < 1) {
$this->db->query("ALTER TABLE " . DB_PREFIX . "events_scheduler ADD COLUMN instructor_tab varchar(50) COLLATE utf8_general_ci NOT NULL DEFAULT ''");
}
$results = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "events_scheduler LIKE 'sku'");
if ($results->num_rows < 1) {
$this->db->query("ALTER TABLE " . DB_PREFIX . "events_scheduler ADD COLUMN sku varchar(50) COLLATE utf8_general_ci NOT NULL DEFAULT ''");
}
$results = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "events_scheduler LIKE 'date_end'");
if ($results->num_rows < 1) {
$this->db->query("ALTER TABLE " . DB_PREFIX . "events_scheduler ADD COLUMN date_end int(11) NOT NULL DEFAULT 0");
}
$results = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "product LIKE 'event_id'");
if ($results->num_rows < 1) {
$this->db->query("ALTER TABLE " . DB_PREFIX . "product ADD COLUMN event_id int(11) NOT NULL DEFAULT 0");
$this->db->query("ALTER TABLE " . DB_PREFIX . "product ADD COLUMN end_date datetime NOT NULL");
}
$results = $this->db->query("SHOW COLUMNS FROM " . DB_PREFIX . "product LIKE 'end_date'");
if ($results->num_rows < 1) {
$this->db->query("ALTER TABLE " . DB_PREFIX . "product ADD COLUMN end_date datetime NOT NULL");
}
return;
}
}
?>