function validate_requests($api_token = '')
{
global $status;
$sql = "SELECT request_count, last_login FROM `api_users` WHERE `token` = '" . $api_token . "'";
$select = query($sql);
$login = getRowList($select);
if (!empty($login)) {
$now = date('Y-m-d');
// count API requests per day
if ($login->last_login == $now) {
$sql = "UPDATE `api_users` SET \n `request_count` = request_count+1,\n WHERE `token` = '" . $api_token . "'";
} else {
$sql = "UPDATE `api_users` SET \n `request_count` = 1, \n `last_login` = CURDATE()\n WHERE `token` = '" . $api_token . "'";
}
$update = query($sql);
} else {
// create some error code/message
$status['code'] = 400;
$status['status'] = getStatusCodeMessage($status['code']);
}
// handle back the number of requests per day
return $login->request_count;
}
function querySQL($statement)
{
global $storno, $wait, $author, $cellid, $repeatid, $id, $value, $field, $searchquery;
$today = date('Y-m-d');
$yesterday = date('Y-m-d', time() - 86400);
$before_yesterday = date('Y-m-d', time() - 172800);
switch ($statement) {
case 'availability':
$result = query("SELECT reservation_time, SUM(reservation_pax) AS pax_total, COUNT(reservation_id) AS tbl_total\n\t\t\t\t\t\t\tFROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '0' \n\t\t\t\t\t\t\tAND `reservation_wait` = '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s'\n\t\t\t\t\t\t\tAND ( NOT `reservation_status` = 'DEP' AND NOT `reservation_status` = 'NSW')\n\t\t\t\t\t\t\tAND `reservation_id` != '%d'\n\t\t\t\t\t\t\tGROUP BY `reservation_time`\n\t\t\t\t\t\t\tORDER BY `reservation_time` ASC", $_SESSION['outletID'], $_SESSION['selectedDate'], $_SESSION['resID']);
return getRowList($result);
break;
case 'passerby_availability':
$result = query("SELECT reservation_time, SUM(reservation_pax) AS passerby_total\n\t\t\t\t\t\t\tFROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '0' \n\t\t\t\t\t\t\tAND `reservation_wait` = '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%s' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s'\n\t\t\t\t\t\t\tAND ( NOT `reservation_status` = 'DEP' AND NOT `reservation_status` = 'NSW')\n\t\t\t\t\t\t\tAND `reservation_hotelguest_yn` = 'PASS'\n\t\t\t\t\t\t\tGROUP BY `reservation_time`\n\t\t\t\t\t\t\tORDER BY `reservation_time` ASC", $_SESSION['outletID'], $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'maxcapacity':
$out1 = array();
$result = query("SELECT outlet_max_capacity, outlet_max_tables, passerby_max_pax FROM `outlets` \n\t\t\t\t\t\t\tWHERE `outlet_id`='%d'", $_SESSION['outletID']);
$out1 = getRowListarray($result);
$result = query("SELECT outlet_child_tables, outlet_child_capacity, outlet_child_passer_max_pax FROM `maitre` \n\t\t\t\t\t\t\tWHERE `maitre_outlet_id`='%d' \n\t\t\t\t\t\t\tAND `maitre_date`='%s'", $_SESSION['outletID'], $_SESSION['selectedDate']);
$out2 = getRowListarray($result);
if (is_array($out1) && is_array($out2)) {
return array_merge($out1, $out2);
} else {
return $out1;
}
break;
case 'passerby_max_pax':
$result = query("SELECT sum(reservation_pax) FROM reservations \n\t\t\t\t\t\t\tWHERE `reservation_date`='%s' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND `reservation_hidden`=0 \n\t\t\t\t\t\t\tAND `reservation_wait`=0 \n\t\t\t\t\t\t\tAND `reservation_hotelguest_yn`='PASS' \n\t\t\t\t\t\t\t", $_SESSION['selectedDate'], $_SESSION['outletID']);
return getResult($result);
break;
case 'max_id':
$result = query("SELECT MAX(reservation_id) FROM reservations\n\t\t\t\t\t\t\tWHERE `reservation_date`='%s' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND `reservation_hidden`=0 \n\t\t\t\t\t\t\t", $_SESSION['selectedDate'], $_SESSION['outletID']);
return getResult($result);
break;
case 'standard_outlet':
$result = query("SELECT outlet_id FROM `outlets` \n\t\t\t\t\t\t\tWHERE `property_id` = '%d' \n\t\t\t\t\t\t\tAND ( `saison_year` = 0 OR `saison_year` = YEAR(NOW()) )\n\t\t\t\t\t\t\tAND saison_start <= '%d' \n\t\t\t\t\t\t\tAND saison_end >= '%d' \n\t\t\t\t\t\t\tORDER BY outlet_id LIMIT 1", $_SESSION['property'], $_SESSION['selectedDate_saison'], $_SESSION['selectedDate_saison']);
return getResult($result);
break;
case 'web_standard_outlet':
$result = query("SELECT outlet_id FROM `outlets` \n\t\t\t\t\t\t\tWHERE `property_id` = '%d' \n\t\t\t\t\t\t\tAND ( `saison_year` = 0 OR `saison_year` = YEAR(NOW()) )\n\t\t\t\t\t\t\tAND `webform` ='1' \n\t\t\t\t\t\t\tORDER BY outlet_name DESC LIMIT 1", $_SESSION['property']);
return getResult($result);
break;
case 'num_outlets':
$result = query("SELECT COUNT(*) FROM `outlets` \n\t\t\t\t\t\t\tWHERE ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tAND `property_id` ='%d'\n\t\t\t\t\t\t\tAND `webform` = '1'", $_SESSION['selectedDate_year'], $_SESSION['property']);
return getResult($result);
break;
case 'check_web_outlet':
$result = query("SELECT COUNT(*) FROM `outlets` \n\t\t\t\t\t\t\tWHERE ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tAND `outlet_id` ='%d'\n\t\t\t\t\t\t\tAND `webform` = '1'", $_SESSION['selectedDate_year'], $_SESSION['outletID']);
return getResult($result);
break;
case 'property_id_outlet':
$result = query("SELECT `property_id` FROM `outlets` \n\t\t\t\t\t\t\tWHERE `outlet_id` ='%d'", $_SESSION['outletID']);
return getResult($result);
break;
case 'security_outlet':
$result = query("SELECT COUNT(*) FROM `outlets` \n\t\t\t\t\t\t\tWHERE ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tAND `property_id` ='%d'\n\t\t\t\t\t\t\tAND `outlet_id` ='%d'\n ", $_SESSION['selectedDate_year'], $_SESSION['property'], $_SESSION['outletID']);
return getResult($result);
break;
case 'db_outlet':
$result = query("SELECT outlet_name FROM `outlets` WHERE `outlet_id` ='%d' AND `property_id` ='%d' LIMIT 1", $_SESSION['outletID'], $_SESSION['property']);
return getResult($result);
break;
case 'db_prop_pic':
$result = query("SELECT img_filename FROM `properties` WHERE `id` ='%d' LIMIT 1", $_SESSION['property']);
return getResult($result);
break;
case 'db_property':
$result = query("SELECT name FROM `properties` WHERE `id` ='%d' LIMIT 1", $_SESSION['property']);
return getResult($result);
break;
case 'db_outlets':
$result = query("SELECT * FROM `outlets` \n\t\t\t\t\t\t\tWHERE ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tAND `property_id` ='%d' \n\t\t\t\t\t\t\tORDER BY outlet_name", $_SESSION['selectedDate_year'], $_SESSION['property']);
return getRowList($result);
break;
case 'db_outlets_web':
$result = query("SELECT outlet_id, outlet_name, outlet_description, cuisine_style, saison_start, saison_end \n\t\t\t\t\t\t\tFROM `outlets` \n\t\t\t\t\t\t\tWHERE ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tAND `property_id` ='%d'\n\t\t\t\t\t\t\tAND `webform` = '1'\n\t\t\t\t\t\t\tORDER BY outlet_name", $_SESSION['selectedDate_year'], $_SESSION['property']);
return getRowList($result);
break;
case 'db_all_outlets':
$result = query("SELECT outlet_id, outlet_name, outlet_description, cuisine_style, \n\t\t\t\t\t\t\toutlet_max_capacity, outlet_max_tables, outlet_open_time, outlet_close_time, \n\t\t\t\t\t\t\tsaison_start, saison_end, saison_year, webform, avg_duration \n\t\t\t\t\t\t\tFROM `outlets` \n\t\t\t\t\t\t\tWHERE `property_id` ='%d' \n\t\t\t\t\t\t\tAND ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tORDER BY saison_year ASC, outlet_name ASC", $_SESSION['property'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'db_all_outlets_old':
$result = query("SELECT outlet_id, outlet_name, outlet_description, cuisine_style, \n\t\t\t\t\t\t\toutlet_max_capacity, outlet_max_tables, outlet_open_time, outlet_close_time, \n\t\t\t\t\t\t\tsaison_start, saison_end, saison_year, webform, avg_duration \n\t\t\t\t\t\t\tFROM `outlets` \n\t\t\t\t\t\t\tWHERE `property_id` ='%d' \n\t\t\t\t\t\t\tAND `saison_year` < '%d'\n\t\t\t\t\t\t\tAND `saison_year` != 0\n\t\t\t\t\t\t\tORDER BY saison_year ASC, outlet_name ASC", $_SESSION['property'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'db_outlet_info':
$result = query("SELECT outlet_id, outlet_name, property_id, outlet_description, outlet_description_en,\t \n\t\t\t\t\t\tcuisine_style, property_id, outlet_max_capacity, outlet_max_tables, outlet_open_time, \t \n\t\t\t\t\t\toutlet_close_time, outlet_timestamp, outlet_closeday, saison_start, saison_end, \t \t \n\t\t\t\t\t\tsaison_year, webform, limit_password, confirmation_email, passerby_max_pax, avg_duration,\t \n\t\t\t\t\t\t1_open_time,1_close_time, 2_open_time,2_close_time, \t \n\t\t\t\t\t\t3_open_time,3_close_time, 4_open_time,4_close_time, \t \n\t\t\t\t\t\t5_open_time, 5_close_time, 6_open_time, 6_close_time, \t \n\t\t\t\t\t\t0_open_time, 0_close_time, 1_open_break, 1_close_break, \t \n\t\t\t\t\t\t2_open_break, 2_close_break, 3_open_break, 3_close_break, \t \n\t\t\t\t\t\t4_open_break, 4_close_break, 5_open_break, 5_close_break, \t \n\t\t\t\t\t\t6_open_break, 6_close_break, 0_open_break, 0_close_break\n\t\t\t\t\t\t\tFROM `outlets` \n\t\t\t\t\t\t\tWHERE `outlet_id` ='%d' \n\t\t\t\t\t\t\tAND `property_id` ='%d' ", $_SESSION['outletID'], $_SESSION['property']);
return getRowListarray($result);
break;
case 'outlet_info':
$result = query("SELECT * FROM `outlets` \n\t\t\t\t\t\t\tLEFT JOIN `properties` on outlets.property_id = properties.id \n\t\t\t\t\t\t\tWHERE `outlet_id` ='%d'\n\t\t\t\t\t\t\tAND `property_id` ='%d' \n\t\t\t\t\t\t\tLIMIT 1", $_SESSION['outletID'], $_SESSION['property']);
return getRowList($result);
break;
case 'db_propery_events':
$result = query("SELECT id, outlet_id, subject,\n\t\t\tdescription, event_date, start_time, end_time,\n\t\t\tadvertise_start, price \n\t\t\t\t\t\t\tFROM `events` \n\t\t\t\t\t\t\tWHERE `property_id` ='%d' \n\t\t\t\t\t\t\tORDER BY `event_date` DESC", $_SESSION['property']);
return getRowList($result);
break;
case 'db_outlet_events':
$result = query("SELECT id, outlet_id, subject,\n\t\t\tdescription, event_date, start_time, end_time,\n\t\t\tadvertise_start, price \n\t\t\t\t\t\t\tFROM `events` \n\t\t\t\t\t\t\tWHERE `outlet_id` ='%d' \n\t\t\t\t\t\t\tORDER BY `event_date` DESC", $_SESSION['outletID']);
return getRowList($result);
break;
case 'event_data_single':
$result = query("SELECT id, outlet_id, property_id, subject,\n\t\t\tdescription, event_date, start_time, end_time,\n\t\t\tadvertise_start, price \n\t\t\t\t\t\t\tFROM `events` \n\t\t\t\t\t\t\tWHERE `id` ='%d' \n\t\t\t\t\t\t\tLIMIT 1", $_SESSION['eventID']);
return getRowListarray($result);
break;
case 'event_advertise':
$result = query("SELECT events.id, events.outlet_id, events.property_id, events.subject, \n\t\t\tevents.description, events.event_date, events.start_time, events.end_time,\n\t\t\tevents.advertise_start, events.price, outlets.outlet_name FROM `events`\n\t\t\t\t\t\tLEFT JOIN `outlets` ON events.outlet_id = outlets.outlet_id\n\t\t\t\t\t\tWHERE DATE_SUB(`event_date`,INTERVAL `advertise_start` DAY) <= '%s'\n\t\t\t\t\t\tAND `event_date` > '%s'\n\t\t\t\t\t\tAND outlets.property_id ='%d' \n\t\t\t\t\t\tORDER BY advertise_start ASC", $_SESSION['selectedDate'], $_SESSION['selectedDate'], $_SESSION['property']);
return getRowList($result);
break;
case 'event_advertise_web':
$result = query("SELECT events.id, events.outlet_id, events.property_id, events.subject,\n\t\t\tevents.description, events.event_date, events.start_time, events.end_time,\n\t\t\tevents.advertise_start, events.price, outlets.outlet_name FROM `events`\n\t\t\t\t\t\tLEFT JOIN `outlets` ON events.outlet_id = outlets.outlet_id\n\t\t\t\t\t\tWHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `events` ) \n\t\t\t\t\t\tAND DATE_SUB(`event_date`,INTERVAL `advertise_start` DAY) <= CURDATE()\n\t\t\t\t\t\tAND `event_date` >= CURDATE()\n\t\t\t\t\t\tAND `event_date` >= '%s'\n\t\t\t\t\t\tAND `webform` = '1'\n\t\t\t\t\t\tORDER BY advertise_start,event_date ASC\n\t\t\t\t\t\tLIMIT 5", $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'event_data_day':
$result = query("SELECT id, outlet_id, property_id, subject,\n\t\t\t\t\t\tdescription, event_date, start_time, end_time,\n\t\t\t\t\t\tadvertise_start, price \n\t\t\t\t\t\tFROM `events` \n\t\t\t\t\t\tWHERE `event_date` ='%s' \n\t\t\t\t\t\tAND `outlet_id` ='%d' \n\t\t\t\t\t\tAND `property_id` ='%d'", $_SESSION['selectedDate'], $_SESSION['outletID'], $_SESSION['property']);
return getRowList($result);
break;
case 'user_data':
$result = query("SELECT userID,username,realname,password,email,role,\n\t\t \t\t\t\t\tproperty_id,active,confirm_code,last_ip,last_login,created,modified \n\t\t\t\t\t\t\tFROM `plc_users` \n\t\t\t\t\t\t\tWHERE `userID` ='%d' \n\t\t\t\t\t\t\tLIMIT 1", $_SESSION['userID']);
return getRowListarray($result);
break;
case 'user_confirm_code':
$result = query("UPDATE `plc_users` SET confirm_code = '%s', active = '0' \n\t\t\t\t\t\t\t\tWHERE `userID` ='%d' LIMIT 1", $_SESSION['confHash'], $id);
return $result;
break;
case 'check_confirm_code':
$result = query("SELECT active,confirm_code FROM `plc_users` WHERE confirm_code='%s'", $_SESSION['confHash']);
return getRowListarray($result);
break;
case 'user_confirm_activate':
$result = query("UPDATE `plc_users` SET confirm_code = '', active = '1' \n\t\t\t\t\t\t\t\tWHERE confirm_code='%s'", $_SESSION['confHash']);
return $result;
break;
case 'maitre_info':
$result = query("SELECT maitre_id, maitre_outlet_id,\n\t\t\t\t\t\t\tmaitre_date, maitre_comment_day,\n\t\t\t\t\t\t\tmaitre_comment_day_timestamp, maitre_comment_day_name,\n\t\t\t\t\t\t\tmaitre_timestamp, maitre_ip,\n\t\t\t\t\t\t\tmaitre_author, outlet_child_tables,\n\t\t\t\t\t\t\toutlet_child_capacity, outlet_capacity_timestamp,\n\t\t\t\t\t\t\toutlet_child_passer_max_pax, outlet_child_dayoff \n\t\t\t\t\t\t\tFROM `maitre` \n\t\t\t\t\t\t\tWHERE `maitre_outlet_id` ='%d' \n\t\t\t\t\t\t\tAND `maitre_date`='%s' \n\t\t\t\t\t\t\tLIMIT 1", $_SESSION['outletID'], $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'maitre_dayoffs':
$result = query("SELECT `maitre_date` FROM `maitre` \n\t\t\t\t\t\t\tWHERE `outlet_child_dayoff` = 'ON'\n\t\t\t\t\t\t\tAND YEAR(maitre_date) = '%s'\n\t\t\t\t\t\t\tAND `maitre_outlet_id` ='%d' \n\t\t\t\t\t\t\tORDER BY `maitre_date` ASC", date('Y'), $_SESSION['outletID']);
return getRowList($result);
break;
case 'outlet_closedays':
$result = query("SELECT `outlet_closeday` FROM `outlets` \n\t\t\t\t\t\t\tWHERE `outlet_id` ='%d' \n\t\t\t\t\t\t\t", $_SESSION['outletID']);
return getResult($result);
break;
case 'db_all_users':
$result = query("SELECT userID,username,realname,password,email,role,\n\t\t \t\t\t\t\tproperty_id,active,confirm_code,last_ip,last_login,created,modified\n\t\t\t\t\t\t\tFROM `plc_users` ORDER BY `username`");
return getRowList($result);
break;
case 'db_prp_users':
$result = query("SELECT userID,username,realname,password,email,role,\n\t\t \t\t\t\t\tproperty_id,active,confirm_code,last_ip,last_login,created,modified \n\t\t\t\t\t\t\tFROM `plc_users`\n\t\t\t\t\t\t\tWHERE `property_id` ='%d'\n\t\t\t\t\t\t\tORDER BY `username`", $_SESSION['property']);
return getRowList($result);
break;
case 'recent':
$result = query("SELECT reservation_id, reservation_bookingnumber, reservation_outlet_id,\n\t\t\treservation_date, reservation_time, reservation_title,\n\t\t\treservation_guest_name, reservation_guest_adress, reservation_guest_city,\n\t\t\treservation_guest_email, reservation_guest_phone, reservation_pax,\n\t\t\treservation_hotelguest_yn, reservation_notes, reservation_booker_name,\n\t\t\treservation_timestamp, reservation_ip, reservation_hidden,\n\t\t\treservation_wait, repeat_id, reservation_bill,\n\t\t\treservation_discount, reservation_bill_paid, reservation_billet_sent,\n\t\t\treservation_parkticket, reservation_table, reservation_status,\n\t\t\treservation_advertise,reservation_referer \n\t\t\t\t\t\tFROM `reservations` \n\t\t\t\t\t\tWHERE reservation_outlet_id='%d' \n\t\t\t\t\t\tORDER BY reservation_timestamp DESC LIMIT 0,4", $_SESSION['outletID']);
return getRowList($result);
break;
case 'tautologous':
$result = query("SELECT count(*) FROM `reservations` WHERE reservation_date='%s' AND reservation_hidden=0 AND reservation_wait=0 AND reservation_guest_name='%s' ", $_SESSION['selectedDate'], $_SESSION['reservation_guest_name']);
return getResult($result);
break;
case 'capability':
$result = query("SELECT `%d` FROM `capabilities` WHERE `capability`='%s'", $_SESSION['role'], $_SESSION['capability']);
return getResult($result);
break;
case 'capabilities':
$result = query("SELECT `capability`,`1`,`2`,`3`,`4`,`5`,`6` FROM `capabilities`");
//return getRowListarray($result);
return $result;
break;
case 'reservation_info':
$result = query("SELECT reservation_id, reservation_bookingnumber, reservation_outlet_id,\n\t\t\treservation_date, reservation_time, reservation_title,\n\t\t\treservation_guest_name, reservation_guest_adress, reservation_guest_city,\n\t\t\treservation_guest_email, reservation_guest_phone, reservation_pax,\n\t\t\treservation_hotelguest_yn, reservation_notes, reservation_booker_name,\n\t\t\treservation_timestamp, reservation_ip, reservation_hidden,\n\t\t\treservation_wait, repeat_id, reservation_bill,\n\t\t\treservation_discount, reservation_bill_paid, reservation_billet_sent,\n\t\t\treservation_parkticket, reservation_table, reservation_status,\n\t\t\treservation_advertise,reservation_referer,\n\t\t\toutlets.outlet_name,res_repeat.id,res_repeat.start_date,res_repeat.end_date \n\t\t\t\t\tFROM `reservations`\n\t\t\t\t\tLEFT JOIN `outlets` ON outlet_id = reservation_outlet_id\n\t\t\t\t\tLEFT JOIN `res_repeat` ON res_repeat.id = reservations.repeat_id \n\t\t\t\t\tWHERE reservations.reservation_id = '%d' LIMIT 1", $_SESSION['resID']);
return getRowList($result);
break;
case 'reservations':
$result = query("SELECT reservation_id, reservation_bookingnumber, reservation_outlet_id,\n\t\t\treservation_date, reservation_time, reservation_title,\n\t\t\treservation_guest_name, reservation_guest_adress, reservation_guest_city,\n\t\t\treservation_guest_email, reservation_guest_phone, reservation_pax,\n\t\t\treservation_hotelguest_yn, reservation_notes, reservation_booker_name,\n\t\t\treservation_timestamp, reservation_ip, reservation_hidden,\n\t\t\treservation_wait, repeat_id, reservation_bill,\n\t\t\treservation_discount, reservation_bill_paid, reservation_billet_sent,\n\t\t\treservation_parkticket, reservation_table, reservation_status,\n\t\t\treservation_advertise,reservation_referer\n\t\t\t\t\t\t\tFROM `reservations` \n\t\t\t\t\t\t\tINNER JOIN `outlets` ON `outlet_id` = `reservation_outlet_id` \n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '%d' \n\t\t\t\t\t\t\tAND `reservation_wait` = '%d' \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s' \n\t\t\t\t\t\t\tORDER BY `reservation_time` ASC", $_SESSION['storno'], $_SESSION['wait'], $_SESSION['outletID'], $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'all_reservations':
$result = query("SELECT reservation_id, reservation_bookingnumber, reservation_outlet_id,\n\t\t\treservation_date, reservation_time, reservation_title,\n\t\t\treservation_guest_name, reservation_guest_adress, reservation_guest_city,\n\t\t\treservation_guest_email, reservation_guest_phone, reservation_pax,\n\t\t\treservation_hotelguest_yn, reservation_notes, reservation_booker_name,\n\t\t\treservation_timestamp, reservation_ip, reservation_hidden,\n\t\t\treservation_wait, repeat_id, reservation_bill,\n\t\t\treservation_discount, reservation_bill_paid, reservation_billet_sent,\n\t\t\treservation_parkticket, reservation_table, reservation_status,\n\t\t\treservation_advertise,reservation_referer,outlets.outlet_name\n\t\t\t\t\t\t\tFROM `reservations` \n\t\t\t\t\t\t\tINNER JOIN `outlets` ON `outlet_id` = `reservation_outlet_id` \n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '0' \n\t\t\t\t\t\t\tAND `reservation_wait` = '%d' \n\t\t\t\t\t\t\tAND `property_id` = '%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s' \n\t\t\t\t\t\t\tORDER BY `reservation_time` ASC", $_SESSION['wait'], $_SESSION['propertyID'], $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'search':
$result = query("SELECT reservation_id, reservation_bookingnumber, reservation_outlet_id,\n\t\t\treservation_date, reservation_time, reservation_title,\n\t\t\treservation_guest_name, reservation_guest_adress, reservation_guest_city,\n\t\t\treservation_guest_email, reservation_guest_phone, reservation_pax,\n\t\t\treservation_hotelguest_yn, reservation_notes, reservation_booker_name,\n\t\t\treservation_timestamp, reservation_ip, reservation_hidden,\n\t\t\treservation_wait, repeat_id, reservation_bill,\n\t\t\treservation_discount, reservation_bill_paid, reservation_billet_sent,\n\t\t\treservation_parkticket, reservation_table, reservation_status,\n\t\t\treservation_advertise,reservation_referer, outlet_name \n\t\t\t\tFROM `reservations` \n\t\t\t\tINNER JOIN `outlets` ON `outlet_id` = `reservation_outlet_id` \n\t\t\t\tWHERE `property_id` = '%d' \n\t\t\t\tAND (`reservation_guest_name` LIKE '%s' \n\t\t\t\t\tOR `reservation_bookingnumber` LIKE '%s' \n\t\t\t\t\tOR `reservation_guest_phone` LIKE '%s') \n\t\t\t\tORDER BY reservation_guest_name ASC", $_SESSION['propertyID'], $searchquery, $searchquery, $searchquery);
return getRowList($result);
break;
case 'reservation_visits':
$result = query("SELECT COUNT(*) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_guest_name`='%s' \n\t\t\t\t\t\t\tAND `reservation_hidden`=0", $_SESSION['reservation_guest_name']);
return getResult($result);
break;
case 'reservation_last_visit':
$result = query("SELECT `reservation_date` FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_guest_name` = '%s' \n\t\t\t\t\t\t\tAND `reservation_hidden` = 0 AND `reservation_date` <= now() \n\t\t\t\t\t\t\tORDER BY `reservation_timestamp` DESC", $_SESSION['reservation_guest_name']);
return getResult($result);
break;
case 'reservation_history':
$result = query("SELECT DISTINCT `reservation_notes` FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_guest_name`='%s' \n\t\t\t\t\t\t\tAND `reservation_hidden`=0 ORDER BY reservation_timestamp DESC", $_SESSION['reservation_guest_name']);
return getRowList($result);
break;
case 'res_history':
$result = query("SELECT id, reservation_id, author, timestamp\n\t\t\t\t\t\t\tFROM `res_history` \n\t\t\t\t\t\t\tWHERE `reservation_id`='%d' \n\t\t\t\t\t\t\tORDER BY id DESC", $_SESSION['resID']);
return getRowList($result);
break;
case 'settings_inc':
$result = query("SELECT id, property_id, language,\n\t\t\ttimezone, timeformat, timeintervall,\n\t\t\tdateformat, dateformat_short, datepickerformat,\n\t\t\tapp_name, max_menu, old_days,\n\t\t\tmanual_lines, contactform_color_scheme, contactform_background, \n\t\t\tguest_type_text_HG, guest_type_text_PASS, guest_type_text_WALK\n\t\t\t\t\t\t\tFROM `settings` \n\t\t\t\t\t\t\tWHERE `property_id` = '%d'", $_SESSION['property']);
return getRowListarray($result);
break;
case 'timecontrol':
$result = query("SELECT reservation_time, SUM(reservation_pax) AS paxsum FROM reservations \n\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' AND `reservation_outlet_id`='%d' \n\t\t\t\t\t\tAND `reservation_date`='%s' GROUP BY reservation_time \n\t\t\t\t\t\tORDER BY paxsum DESC", $_SESSION['outletID'], $_SESSION['selectedDate']);
return getRowListarray($result);
break;
case 'del_res_single':
$result = query("UPDATE `reservations` \n\t\t\t\t\t\t\tSET `reservation_hidden`='1', `reservation_booker_name`='%s',\t`reservation_timestamp` = now()\n\t\t\t\t\t\t\tWHERE `reservation_id`='%d'", $author, $cellid);
return $result;
break;
case 'alw_res_single':
$result = query("UPDATE `reservations` SET `reservation_wait`='0',`reservation_timestamp` = now()\n\t\t\t\t\t\t\tWHERE `reservation_id`='%d'", $cellid);
return $result;
break;
case 'del_res_multi':
$result = query("UPDATE `reservations` \n\t\t\t\t\t\t\tSET `reservation_hidden`='1', `reservation_booker_name`='%s', `reservation_timestamp` = now() \n\t\t\t\t\t\t\tWHERE `repeat_id`='%d'", $author, $repeatid);
return $result;
break;
case 'del_user':
$result = query("DELETE FROM `plc_users` WHERE `userID`='%d' LIMIT 1", $cellid);
return $result;
break;
case 'del_event':
$result = query("DELETE FROM `events` WHERE `id`='%d' LIMIT 1", $cellid);
return $result;
break;
case 'del_outlet':
$result = query("DELETE FROM `outlets` WHERE `outlet_id`='%d' LIMIT 1", $cellid);
return $result;
break;
case 'update_status':
$result = query("UPDATE `reservations` \n\t\t\t\t\t\t\tSET `reservation_status`='%s' \n\t\t\t\t\t\t\tWHERE `reservation_id`='%d'", $value, $id);
return $result;
break;
case 'update_maitre_dayoff':
$result = query("INSERT INTO `maitre`\n\t\t\t\t \t\t\t\t(maitre_id,maitre_outlet_id,maitre_date,outlet_child_dayoff,maitre_ip,maitre_author) \n\t\t\t\t\t\t\t\tVALUES ('%d','%d','%s','%s','%s','%s') \n\t\t\t\t\t\t\t\tON DUPLICATE KEY UPDATE \n\t\t\t\t\t\t\t\t`outlet_child_dayoff`='%s',\n\t\t\t\t\t\t\t\t`maitre_ip`='%s',\n\t\t\t\t\t\t\t\t`maitre_author`='%s'", $id, $_SESSION['outletID'], $_SESSION['selectedDate'], $value, $_SERVER['REMOTE_ADDR'], $_SESSION['u_fullname'], $value, $_SERVER['REMOTE_ADDR'], $_SESSION['u_fullname']);
return $result;
break;
case 'inline_edit':
$result = query("UPDATE `reservations` SET `%s`='%s' WHERE `reservation_id`='%d'", $field, $value, $id);
return $result;
break;
case 'res_repeat':
$result = query("INSERT INTO `res_repeat` (\n\t\t\t\tid, \n\t\t\t\tstart_date,\n\t\t\t\tend_date,\n\t\t\t\tcreate_by\n\t\t\t\t) VALUES (\n\t\t\t\t'%d',\n\t\t\t\t'%s',\n\t\t\t\t'%s',\n\t\t\t\t'%s')\n\t\t\t\tON DUPLICATE KEY UPDATE \n\t\t\t\tid='%d',\n\t\t\t\tstart_date='%s',\n\t\t\t\tend_date='%s',\n\t\t\t\tcreate_by='%s'", $repeatid, $_SESSION['reservation_date'], $_SESSION['recurring_date'], $_SESSION['author'], $repeatid, $_SESSION['reservation_date'], $_SESSION['recurring_date'], $_SESSION['author']);
return mysql_insert_id();
break;
case 'statistic_month':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'statistic_month_last':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= 0 AND `reservation_hidden`= 0 \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year'] - 1);
return getRowList($result);
break;
case 'statistic_week_def':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait` = 0 AND `reservation_hidden` = 0 \n\t\t\t\t\t\t\tAND `reservation_outlet_id` ='%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s'", $_SESSION['outletID'], $_SESSION['statistic_week']);
return getRowList($result);
break;
case 'statistic_week_def_noon':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait` = 0 AND `reservation_hidden` = 0 \n\t\t\t\t\t\t\tAND `reservation_outlet_id` ='%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s'\n\t\t\t\t\t\t\tAND `reservation_time` < '%s'", $_SESSION['outletID'], $_SESSION['statistic_week'], $value);
return getRowList($result);
break;
case 'statistic_week_def_evening':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait` = 0 AND `reservation_hidden` = 0 \n\t\t\t\t\t\t\tAND `reservation_outlet_id` ='%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s'\n\t\t\t\t\t\t\tAND `reservation_time` >= '%s'", $_SESSION['outletID'], $_SESSION['statistic_week'], $value);
return getRowList($result);
break;
case 'statistic_type':
$result = query("SELECT reservation_hotelguest_yn, SUM(reservation_pax) AS paxsum FROM `reservations`\n\t\t\t\t\t\t\tWHERE `reservation_wait`= 0 AND `reservation_hidden`= 0 \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY `reservation_hotelguest_yn`", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'statistic_weekday':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY WEEKDAY(reservation_date)", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'statistic_referer':
$result = query("SELECT reservation_referer, COUNT(*) AS total FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY reservation_referer\n\t\t\t\t\t\t\tORDER BY total DESC\n\t\t\t\t\t\t\tLIMIT 0,7", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'statistic_res_days':
$result = query("SELECT ROUND(AVG(DATEDIFF(reservation_date,reservation_timestamp)),1)\n\t\t\t\t\t\t\tFROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d'\n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_guest_year':
$result = query("SELECT SUM(reservation_pax) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_all_guest_year':
$result = query("SELECT SUM(reservation_pax) FROM `reservations` \n\t\t\t\t\t\t\tINNER JOIN `outlets` ON `outlet_id` = `reservation_outlet_id` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `property_id` = '%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['propertyID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_cxl_year':
$result = query("SELECT COUNT(*) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_booker_year':
$result = query("SELECT COUNT(*) FROM (SELECT `reservation_id` FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_hidden`= '1' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY `reservation_booker_name` ) groups \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_wait_year':
$result = query("SELECT COUNT(*) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '1' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_online_year':
$result = query("SELECT COUNT(*) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND `reservation_referer` != ''\n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_top5_guest_year':
$result = query("SELECT reservation_guest_name, COUNT(*) as total FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY `reservation_guest_name`\n\t\t\t\t\t\t\tORDER BY total DESC\n\t\t\t\t\t\t\tLIMIT 0,7\n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'notifications':
$result = query("SELECT outlet_name,reservation_guest_name,reservation_time FROM `reservations` \n\t\t\t\t\t\t\tINNER JOIN `outlets` ON `outlet_id` = `reservation_outlet_id` \n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '0' \n\t\t\t\t\t\t\tAND `property_id` ='%d'\n\t\t\t\t\t\t\tAND `reservation_date` = '%s'\n\t\t\t\t\t\t\tAND DATE_SUB(NOW(),INTERVAL 1 minute) <= `reservation_timestamp`\n\t\t\t\t\t\t\tORDER BY `reservation_timestamp` ASC\n\t\t\t\t\t\t\tLIMIT 3", $_SESSION['property'], date('Y-m-d'));
return getRowList($result);
break;
case 'all_properties':
$result = query("SELECT id, name, street,\n\t\t\t\t\t\t\tzip, city, country,\n\t\t\t\t\t\t\tcontactperson, phone, fax,\n\t\t\t\t\t\t\temail, website, created,\n\t\t\t\t\t\t\timg_filename, logo_filename, \n\t\t\t\t\t\t\tstatus, social_fb, social_tw \n\t\t\t\t\t\t\tFROM `properties` ORDER BY name ASC");
return getRowList($result);
break;
case 'select_properties':
$result = query("SELECT id, name, street,\n\t\t\t\t\tzip, city, country,\n\t\t\t\t\tcontactperson, phone, fax,\n\t\t\t\t\temail, website, created,\n\t\t\t\t\timg_filename, logo_filename, \n\t\t\t\t\tstatus, social_fb, social_tw \n\t\t\t\t\tFROM `properties`\n\t\t\t\t\tWHERE `country` LIKE '%s'\n\t\t\t\t\tAND `city` LIKE '%s'\n\t\t\t\t\tORDER BY name ASC", $_SESSION['countryID'], $_SESSION['city']);
return getRowList($result);
break;
case 'num_admin':
$result = query("SELECT COUNT(*) FROM `plc_users` WHERE `role` ='1' OR `role` ='2'");
return getResult($result);
break;
case 'property_info':
$result = query("SELECT id, name, street,\n\t\t\t\t\tzip, city, country,\n\t\t\t\t\tcontactperson, phone, fax,\n\t\t\t\t\temail, website, created,\n\t\t\t\t\timg_filename, logo_filename, \n\t\t\t\t\tstatus, social_fb, social_tw\n\t\t\t\t\tFROM `properties` \n\t\t\t\t\tWHERE `id` ='%d'\n LIMIT 1", $_SESSION['propertyID']);
return getRowListarray($result);
break;
case 'property_countries':
$result = query("SELECT DISTINCT country FROM `properties` \n\t\t\t\t\tORDER BY country ASC");
return getRowList($result);
break;
case 'property_countries_num':
$result = query("SELECT DISTINCT country FROM `properties` \n\t\t\t\t\tORDER BY country ASC");
return mysql_num_rows($result);
break;
case 'property_cities':
$result = query("SELECT DISTINCT city,country FROM `properties`\n\t\t\t\t\tWHERE `country` ='%s'\n\t\t\t\t\tORDER BY city ASC", $_SESSION['countryID']);
return getRowList($result);
break;
case 'property_cities_num':
$result = query("SELECT DISTINCT city,country FROM `properties`\n\t\t\t\t\tWHERE `country` ='%s'\n\t\t\t\t\tORDER BY city ASC", $_SESSION['countryID']);
return mysql_num_rows($result);
break;
case 'view_img':
$result = query("SELECT img_filename FROM `properties` \n\t\t\t\t\tWHERE `id` ='%d'\n LIMIT 1", $_SESSION['property']);
return getResult($result);
break;
case 'featured_outlet':
$result = query("SELECT outlet_id, outlet_name, outlet_description, outlet_description_en,\t \n\t\t\t\t\t\tcuisine_style, property_id, outlet_max_capacity, outlet_max_tables, outlet_open_time, \t \n\t\t\t\t\t\toutlet_close_time, outlet_timestamp, outlet_closeday, saison_start, saison_end, \t \t \n\t\t\t\t\t\tsaison_year, webform, confirmation_email, passerby_max_pax, avg_duration,\t \n\t\t\t\t\t\t1_open_time,1_close_time, 2_open_time,2_close_time, \t \n\t\t\t\t\t\t3_open_time,3_close_time, 4_open_time,4_close_time, \t \n\t\t\t\t\t\t5_open_time, 5_close_time, 6_open_time, 6_close_time, \t \n\t\t\t\t\t\t0_open_time, 0_close_time, 1_open_break, 1_close_break, \t \n\t\t\t\t\t\t2_open_break, 2_close_break, 3_open_break, 3_close_break, \t \n\t\t\t\t\t\t4_open_break, 4_close_break, 5_open_break, 5_close_break, \t \n\t\t\t\t\t\t6_open_break, 6_close_break, 0_open_break, 0_close_break \n\t\t\t\t\tFROM `outlets`\n\t\t\t\t\tWHERE outlet_id >= (SELECT FLOOR( MAX(outlet_id) * RAND()) FROM `outlets` ) \n\t\t\t\t\tAND ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\tAND `webform` = '1'\n\t\t\t\t\tORDER BY outlet_id LIMIT 1", $_SESSION['selectedDate_year']);
return getRowListarray($result);
break;
case 'del_properties':
$result = query("DELETE FROM `properties` WHERE `id`='%d' LIMIT 1", $cellid);
return $result;
break;
case 'check_username':
$result = query("SELECT username FROM `plc_users` WHERE `username`='%s'", $value);
return $result;
break;
case 'check_unique_id':
$result = query("SELECT COUNT(*) FROM `reservations` WHERE `reservation_bookingnumber`='%s'", $_SESSION['PWD']);
return getResult($result);
break;
case 'store_unique_id':
$result = query("UPDATE `reservations` SET reservation_bookingnumber = '' WHERE `reservation_date`<'%s'", $today);
return $result;
break;
case 'sanitize_unique_id':
$result = query("UPDATE `reservations` SET reservation_bookingnumber = '' WHERE `reservation_date`<'%s'", $before_yesterday);
return $result;
break;
case 'cxl_list':
$result = query("SELECT reservation_title, reservation_guest_name, reservation_timestamp, COUNT(*) AS count \n\t\t\t\t\t\t\tFROM `reservations`\n\t\t\t\t\t\t\tLEFT JOIN `outlets` ON outlet_id = reservation_outlet_id\n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '1' \n\t\t\t\t\t\t\tAND `property_id` = '%d'\n\t\t\t\t\t\t\tGROUP BY `reservation_guest_name`\n\t\t\t\t\t\t\tORDER BY count DESC\n\t\t\t\t\t\t\tLIMIT 20", $_SESSION['propertyID']);
return getRowList($result);
break;
case 'active_plugins':
$plug = query("SELECT filename, action FROM `plugins` WHERE `action` = '1'");
return getRowList($plug);
break;
case 'update_plugins':
$result = query("UPDATE `plugins` SET `action` = '%d' WHERE `filename`='%s'", $value, $field);
return $result;
break;
case 'count_plugins':
$result = query("SELECT COUNT(*) FROM `plugins` WHERE `filename`='%s'", $field);
return getResult($result);
break;
case 'get_plugins':
$result = query("SELECT action FROM `plugins` WHERE `filename`='%s'", $field);
return getResult($result);
break;
case 'insert_plugins':
$result = query("INSERT INTO `plugins` (`filename`,`action`) VALUES ('%s','%d')", $field, $value);
return $result;
break;
case 'user_activate':
$result = query("UPDATE `plc_users` \n\t\t\t\t\t\t\tSET `active`='%d' \n\t\t\t\t\t\t\tWHERE `userID`='%d' LIMIT 1", $value, $id);
return $result;
break;
}
}
function querySQL($statement)
{
global $storno, $wait, $author, $cellid, $repeatid, $id, $value, $field, $searchquery;
$today = date('Y-m-d');
$yesterday = date('Y-m-d', time() - 86400);
$before_yesterday = date('Y-m-d', time() - 172800);
switch ($statement) {
case 'availability':
$result = query("SELECT reservation_time, SUM(reservation_pax) AS pax_total, COUNT(reservation_id) AS tbl_total\n\t\t\t\t\t\t\tFROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '0' \n\t\t\t\t\t\t\tAND `reservation_wait` = '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s'\n\t\t\t\t\t\t\tAND `reservation_id` != '%d'\n\t\t\t\t\t\t\tGROUP BY `reservation_time`\n\t\t\t\t\t\t\tORDER BY `reservation_time` ASC", $_SESSION['outletID'], $_SESSION['selectedDate'], $_SESSION['resID']);
return getRowList($result);
break;
case 'passerby_availability':
$result = query("SELECT reservation_time, SUM(reservation_pax) AS passerby_total\n\t\t\t\t\t\t\tFROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '0' \n\t\t\t\t\t\t\tAND `reservation_wait` = '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%s' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s'\n\t\t\t\t\t\t\tAND `reservation_status` != 'DEP'\n\t\t\t\t\t\t\tAND `reservation_hotelguest_yn` = 'PASS'\n\t\t\t\t\t\t\tGROUP BY `reservation_time`\n\t\t\t\t\t\t\tORDER BY `reservation_time` ASC", $_SESSION['outletID'], $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'maxcapacity':
$out1 = array();
$result = query("SELECT outlet_max_capacity, outlet_max_tables, passerby_max_pax FROM `outlets` \n\t\t\t\t\t\t\tWHERE `outlet_id`='%d'", $_SESSION['outletID']);
$out1 = getRowListarray($result);
$result = query("SELECT outlet_child_tables, outlet_child_capacity, outlet_child_passer_max_pax FROM `maitre` \n\t\t\t\t\t\t\tWHERE `maitre_outlet_id`='%d' \n\t\t\t\t\t\t\tAND `maitre_date`='%s'", $_SESSION['outletID'], $_SESSION['selectedDate']);
$out2 = getRowListarray($result);
if (is_array($out1) && is_array($out2)) {
return array_merge($out1, $out2);
} else {
return $out1;
}
break;
case 'passerby_max_pax':
$result = query("SELECT sum(reservation_pax) FROM reservations \n\t\t\t\t\t\t\tWHERE `reservation_date`='%s' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND `reservation_hidden`=0 \n\t\t\t\t\t\t\tAND `reservation_wait`=0 \n\t\t\t\t\t\t\tAND `reservation_hotelguest_yn`='PASS' \n\t\t\t\t\t\t\t", $_SESSION['selectedDate'], $_SESSION['outletID']);
return getResult($result);
break;
case 'standard_outlet':
$result = query("SELECT outlet_id FROM `outlets` \n\t\t\t\t\t\t\tWHERE `property_id` ='%d' \n\t\t\t\t\t\t\tAND `saison_year` = 0\n\t\t\t\t\t\t\tORDER BY outlet_id LIMIT 1", $_SESSION['property']);
return getResult($result);
break;
case 'web_standard_outlet':
$result = query("SELECT outlet_id FROM `outlets` \n\t\t\t\t\t\t\tWHERE `property_id` ='%d' \n\t\t\t\t\t\t\tAND `webform` ='1' \n\t\t\t\t\t\t\tAND ( `saison_year` = 0 OR `saison_year` = YEAR(NOW()) )\n\t\t\t\t\t\t\tORDER BY outlet_name LIMIT 1", $_SESSION['property']);
return getResult($result);
break;
case 'num_outlets':
$result = query("SELECT COUNT(*) FROM `outlets` \n\t\t\t\t\t\t\tWHERE ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tAND `property_id` ='%d'\n\t\t\t\t\t\t\tAND `webform` = '1'", $_SESSION['selectedDate_year'], $_SESSION['property']);
return getResult($result);
break;
case 'db_outlet':
$result = query("SELECT outlet_name FROM `outlets` WHERE `outlet_id` ='%d' AND `property_id` ='%d' LIMIT 1", $_SESSION['outletID'], $_SESSION['property']);
return getResult($result);
break;
case 'db_prop_pic':
$result = query("SELECT img_filename FROM `properties` WHERE `id` ='%d' LIMIT 1", $_SESSION['property']);
return getResult($result);
break;
case 'db_property':
$result = query("SELECT name FROM `properties` WHERE `id` ='%d' LIMIT 1", $_SESSION['property']);
return getResult($result);
break;
case 'db_outlets':
$result = query("SELECT * FROM `outlets` \n\t\t\t\t\t\t\tWHERE ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tAND `property_id` ='%d' \n\t\t\t\t\t\t\tORDER BY outlet_name", $_SESSION['selectedDate_year'], $_SESSION['property']);
return getRowList($result);
break;
case 'db_outlets_web':
$result = query("SELECT * FROM `outlets` \n\t\t\t\t\t\t\tWHERE ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tAND `property_id` ='%d'\n\t\t\t\t\t\t\tAND `webform` = '1'\n\t\t\t\t\t\t\tORDER BY outlet_name", $_SESSION['selectedDate_year'], $_SESSION['property']);
return getRowList($result);
break;
case 'db_all_outlets':
$result = query("SELECT * FROM `outlets` \n\t\t\t\t\t\t\tWHERE `property_id` ='%d' \n\t\t\t\t\t\t\tAND ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\t\t\tORDER BY saison_year ASC, outlet_name ASC", $_SESSION['property'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'db_all_outlets_old':
$result = query("SELECT * FROM `outlets` \n\t\t\t\t\t\t\tWHERE `property_id` ='%d' \n\t\t\t\t\t\t\tAND `saison_year` < '%d'\n\t\t\t\t\t\t\tAND `saison_year` != 0\n\t\t\t\t\t\t\tORDER BY saison_year ASC, outlet_name ASC", $_SESSION['property'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'db_outlet_info':
$result = query("SELECT * FROM `outlets` \n\t\t\t\t\t\t\tWHERE `outlet_id` ='%d' \n\t\t\t\t\t\t\tAND `property_id` ='%d' ", $_SESSION['outletID'], $_SESSION['property']);
return getRowListarray($result);
break;
case 'outlet_info':
$result = query("SELECT * FROM `outlets` \n\t\t\t\t\t\t\tLEFT JOIN `properties` on outlets.property_id = properties.id \n\t\t\t\t\t\t\tWHERE `outlet_id` ='%d'\n\t\t\t\t\t\t\tAND `property_id` ='%d' \n\t\t\t\t\t\t\tLIMIT 1", $_SESSION['outletID'], $_SESSION['property']);
return getRowList($result);
break;
case 'db_propery_events':
$result = query("SELECT * FROM `events` \n\t\t\t\t\t\t\tWHERE `property_id` ='%d' \n\t\t\t\t\t\t\tORDER BY `event_date` DESC", $_SESSION['property']);
return getRowList($result);
break;
case 'db_outlet_events':
$result = query("SELECT * FROM `events` \n\t\t\t\t\t\t\tWHERE `outlet_id` ='%d' \n\t\t\t\t\t\t\tORDER BY `event_date` DESC", $_SESSION['outletID']);
return getRowList($result);
break;
case 'event_data_single':
$result = query("SELECT * FROM `events` WHERE `id` ='%d' LIMIT 1", $_SESSION['eventID']);
return getRowListarray($result);
break;
case 'event_advertise':
$result = query("SELECT events.*,outlets.outlet_name FROM `events`\n\t\t\t\t\t\tLEFT JOIN `outlets` ON events.outlet_id = outlets.outlet_id\n\t\t\t\t\t\tWHERE DATE_SUB(`event_date`,INTERVAL `advertise_start` DAY) <= '%s'\n\t\t\t\t\t\tAND `event_date` > '%s'\n\t\t\t\t\t\tAND outlets.property_id ='%d' \n\t\t\t\t\t\tORDER BY advertise_start ASC", $_SESSION['selectedDate'], $_SESSION['selectedDate'], $_SESSION['property']);
return getRowList($result);
break;
case 'event_advertise_web':
$result = query("SELECT events.*,outlets.outlet_name FROM `events`\n\t\t\t\t\t\tLEFT JOIN `outlets` ON events.outlet_id = outlets.outlet_id\n\t\t\t\t\t\tWHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `events` ) \n\t\t\t\t\t\tAND DATE_SUB(`event_date`,INTERVAL `advertise_start` DAY) <= CURDATE()\n\t\t\t\t\t\tAND `event_date` >= CURDATE()\n\t\t\t\t\t\tAND `event_date` >= '%s'\n\t\t\t\t\t\tAND `webform` = '1'\n\t\t\t\t\t\tORDER BY advertise_start,event_date ASC\n\t\t\t\t\t\tLIMIT 5", $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'event_data_day':
$result = query("SELECT * FROM `events` \n\t\t\t\t\t\tWHERE `event_date` ='%s' \n\t\t\t\t\t\tAND `outlet_id` ='%d' \n\t\t\t\t\t\tAND `property_id` ='%d'", $_SESSION['selectedDate'], $_SESSION['outletID'], $_SESSION['property']);
return getRowList($result);
break;
case 'user_data':
$result = query("SELECT * FROM `plc_users` WHERE `userID` ='%d' LIMIT 1", $_SESSION['userID']);
return getRowListarray($result);
break;
case 'user_confirm_code':
$result = query("UPDATE `plc_users` SET confirm_code = '%s', active = '0' \n\t\t\t\t\t\t\t\tWHERE `userID` ='%d' LIMIT 1", $_SESSION['confHash'], $id);
return $result;
break;
case 'check_confirm_code':
$result = query("SELECT active,confirm_code FROM `plc_users` WHERE confirm_code='%s'", $_SESSION['confHash']);
return getRowListarray($result);
break;
case 'user_confirm_activate':
$result = query("UPDATE `plc_users` SET confirm_code = '', active = '1' \n\t\t\t\t\t\t\t\tWHERE confirm_code='%s'", $_SESSION['confHash']);
return $result;
break;
case 'maitre_info':
$result = query("SELECT * FROM `maitre` \n\t\t\t\t\t\t\tWHERE `maitre_outlet_id` ='%d' \n\t\t\t\t\t\t\tAND `maitre_date`='%s' \n\t\t\t\t\t\t\tLIMIT 1", $_SESSION['outletID'], $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'maitre_dayoffs':
$result = query("SELECT `maitre_date` FROM `maitre` \n\t\t\t\t\t\t\tWHERE `outlet_child_dayoff` = 'ON'\n\t\t\t\t\t\t\tAND YEAR(maitre_date) = '%s' \n\t\t\t\t\t\t\tORDER BY `maitre_date` ASC", date('Y'));
return getRowList($result);
break;
case 'outlet_closedays':
$result = query("SELECT `outlet_closeday` FROM `outlets` \n\t\t\t\t\t\t\tWHERE `outlet_id` ='%d' \n\t\t\t\t\t\t\t", $_SESSION['outletID']);
return getResult($result);
break;
case 'db_all_users':
$result = query("SELECT * FROM `plc_users` ORDER BY username");
return getRowList($result);
break;
case 'db_prp_users':
$result = query("SELECT * FROM `plc_users`\n\t\t\t\tWHERE `property_id` ='%d'\n\t\t\t\tORDER BY username\n\t\t\t\t", $_SESSION['property']);
return getRowList($result);
break;
case 'recent':
$result = query("SELECT * FROM `reservations` WHERE reservation_outlet_id='%d' ORDER BY reservation_timestamp DESC LIMIT 0,4", $_SESSION['outletID']);
return getRowList($result);
break;
case 'tautologous':
$result = query("SELECT count(*) FROM `reservations` WHERE reservation_date='%s' AND reservation_hidden=0 AND reservation_wait=0 AND reservation_guest_name='%s' ", $_SESSION['selectedDate'], $_SESSION['reservation_guest_name']);
return getResult($result);
break;
case 'capability':
$result = query("SELECT `%d` FROM `capabilities` WHERE `capability`='%s'", $_SESSION['role'], $_SESSION['capability']);
return getResult($result);
break;
case 'capabilities':
$result = query("SELECT `capability`,`1`,`2`,`3`,`4`,`5`,`6` FROM `capabilities`");
//return getRowListarray($result);
return $result;
break;
case 'reservation_info':
$result = query("SELECT reservations.*, outlets.outlet_name,res_repeat.* FROM `reservations`\n\t\t\t\t\tLEFT JOIN `outlets` ON outlet_id = reservation_outlet_id\n\t\t\t\t\tLEFT JOIN `res_repeat` ON res_repeat.id = reservations.repeat_id \n\t\t\t\t\tWHERE reservations.reservation_id = '%d' LIMIT 1", $_SESSION['resID']);
return getRowList($result);
break;
case 'reservations':
$result = query("SELECT * FROM `reservations` \n\t\t\t\t\t\t\tINNER JOIN `outlets` ON `outlet_id` = `reservation_outlet_id` \n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '%d' \n\t\t\t\t\t\t\tAND `reservation_wait` = '%d' \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s' \n\t\t\t\t\t\t\tORDER BY `reservation_time` ASC", $_SESSION['storno'], $_SESSION['wait'], $_SESSION['outletID'], $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'all_reservations':
$result = query("SELECT * FROM `reservations` \n\t\t\t\t\t\t\tINNER JOIN `outlets` ON `outlet_id` = `reservation_outlet_id` \n\t\t\t\t\t\t\tWHERE `reservation_hidden` = '0' \n\t\t\t\t\t\t\tAND `reservation_wait` = '%d' \n\t\t\t\t\t\t\tAND `property_id` = '%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s' \n\t\t\t\t\t\t\tORDER BY `reservation_time` ASC", $_SESSION['wait'], $_SESSION['propertyID'], $_SESSION['selectedDate']);
return getRowList($result);
break;
case 'search':
$result = query("SELECT * FROM `reservations` INNER JOIN `outlets` ON `outlet_id` = `reservation_outlet_id` \n\t\t\t\tWHERE `reservation_hidden` = '0' \n\t\t\t\tAND (`reservation_guest_name` LIKE '%s' \n\t\t\t\t\tOR `reservation_bookingnumber` LIKE '%s' \n\t\t\t\t\tOR `reservation_guest_phone` LIKE '%s') \n\t\t\t\tORDER BY reservation_guest_name ASC", $searchquery, $searchquery, $searchquery);
return getRowList($result);
break;
case 'reservation_visits':
$result = query("SELECT COUNT(*) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_guest_name`='%s' \n\t\t\t\t\t\t\tAND `reservation_hidden`=0", $_SESSION['reservation_guest_name']);
return getResult($result);
break;
case 'reservation_last_visit':
$result = query("SELECT `reservation_date` FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_guest_name` = '%s' \n\t\t\t\t\t\t\tAND `reservation_hidden` = 0 AND `reservation_date` <= now() \n\t\t\t\t\t\t\tORDER BY `reservation_timestamp` DESC", $_SESSION['reservation_guest_name']);
return getResult($result);
break;
case 'reservation_history':
$result = query("SELECT DISTINCT `reservation_notes` FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_guest_name`='%s' \n\t\t\t\t\t\t\tAND `reservation_hidden`=0 ORDER BY reservation_timestamp DESC", $_SESSION['reservation_guest_name']);
return getRowList($result);
break;
case 'res_history':
$result = query("SELECT * FROM `res_history` \n\t\t\t\t\t\t\tWHERE `reservation_id`='%d' \n\t\t\t\t\t\t\tORDER BY id DESC", $_SESSION['resID']);
return getRowList($result);
break;
case 'settings_inc':
$result = query("SELECT * FROM `settings` WHERE `property_id` = '%d'", $_SESSION['property']);
return getRowListarray($result);
break;
case 'timecontrol':
$result = query("SELECT reservation_time, SUM(reservation_pax) AS paxsum FROM reservations \n\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' AND `reservation_outlet_id`='%d' \n\t\t\t\t\t\tAND `reservation_date`='%s' GROUP BY reservation_time \n\t\t\t\t\t\tORDER BY paxsum DESC", $_SESSION['outletID'], $_SESSION['selectedDate']);
return getRowListarray($result);
break;
case 'del_res_single':
$result = query("UPDATE `reservations` \n\t\t\t\t\t\t\tSET `reservation_hidden`='1', `reservation_booker_name`='%s',\t`reservation_timestamp` = now()\n\t\t\t\t\t\t\tWHERE `reservation_id`='%d'", $author, $cellid);
return $result;
break;
case 'alw_res_single':
$result = query("UPDATE `reservations` SET `reservation_wait`='0',`reservation_timestamp` = now()\n\t\t\t\t\t\t\tWHERE `reservation_id`='%d'", $cellid);
return $result;
break;
case 'del_res_multi':
$result = query("UPDATE `reservations` \n\t\t\t\t\t\t\tSET `reservation_hidden`='1', `reservation_booker_name`='%s', `reservation_timestamp` = now() \n\t\t\t\t\t\t\tWHERE `repeat_id`='%d'", $author, $repeatid);
return $result;
break;
case 'del_user':
$result = query("DELETE FROM `plc_users` WHERE `userID`='%d' LIMIT 1", $cellid);
return $result;
break;
case 'del_event':
$result = query("DELETE FROM `events` WHERE `id`='%d' LIMIT 1", $cellid);
return $result;
break;
case 'del_outlet':
$result = query("DELETE FROM `outlets` WHERE `outlet_id`='%d' LIMIT 1", $cellid);
return $result;
break;
case 'update_status':
$result = query("UPDATE `reservations` \n\t\t\t\t\t\t\tSET `reservation_status`='%s' \n\t\t\t\t\t\t\tWHERE `reservation_id`='%d'", $value, $id);
return $result;
break;
case 'update_maitre_dayoff':
$result = query("INSERT INTO `maitre`\n\t\t\t\t \t\t\t\t(maitre_id,maitre_outlet_id,maitre_date,outlet_child_dayoff,maitre_ip,maitre_author) \n\t\t\t\t\t\t\t\tVALUES ('%d','%d','%s','%s','%s','%s') \n\t\t\t\t\t\t\t\tON DUPLICATE KEY UPDATE \n\t\t\t\t\t\t\t\t`outlet_child_dayoff`='%s',\n\t\t\t\t\t\t\t\t`maitre_ip`='%s',\n\t\t\t\t\t\t\t\t`maitre_author`='%s'", $id, $_SESSION['outletID'], $_SESSION['selectedDate'], $value, $_SERVER['REMOTE_ADDR'], $_SESSION['u_fullname'], $value, $_SERVER['REMOTE_ADDR'], $_SESSION['u_fullname']);
return $result;
break;
case 'inline_edit':
$result = query("UPDATE `reservations` SET `%s`='%s' WHERE `reservation_id`='%d'", $field, $value, $id);
return $result;
break;
case 'res_repeat':
$result = query("INSERT INTO `res_repeat` (\n\t\t\t\tid, \n\t\t\t\tstart_date,\n\t\t\t\tend_date,\n\t\t\t\tcreate_by\n\t\t\t\t) VALUES (\n\t\t\t\t'%d',\n\t\t\t\t'%s',\n\t\t\t\t'%s',\n\t\t\t\t'%s')\n\t\t\t\tON DUPLICATE KEY UPDATE \n\t\t\t\tid='%d',\n\t\t\t\tstart_date='%s',\n\t\t\t\tend_date='%s',\n\t\t\t\tcreate_by='%s'", $repeatid, $_SESSION['reservation_date'], $_SESSION['recurring_date'], $_SESSION['author'], $repeatid, $_SESSION['reservation_date'], $_SESSION['recurring_date'], $_SESSION['author']);
return mysql_insert_id();
break;
case 'statistic_month':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'statistic_month_last':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= 0 AND `reservation_hidden`= 0 \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year'] - 1);
return getRowList($result);
break;
case 'statistic_week_def':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait` = 0 AND `reservation_hidden` = 0 \n\t\t\t\t\t\t\tAND `reservation_outlet_id` ='%d' \n\t\t\t\t\t\t\tAND `reservation_date` = '%s'", $_SESSION['outletID'], $_SESSION['statistic_week']);
return getRowList($result);
break;
case 'statistic_type':
$result = query("SELECT reservation_hotelguest_yn, SUM(reservation_pax) AS paxsum FROM `reservations`\n\t\t\t\t\t\t\tWHERE `reservation_wait`= 0 AND `reservation_hidden`= 0 \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY `reservation_hotelguest_yn`", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'statistic_weekday':
$result = query("SELECT SUM(reservation_pax) AS paxsum FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY WEEKDAY(reservation_date)", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'statistic_referer':
$result = query("SELECT reservation_referer, COUNT(*) AS total FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s'\n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY reservation_referer\n\t\t\t\t\t\t\tORDER BY total DESC\n\t\t\t\t\t\t\tLIMIT 0,7", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'statistic_res_days':
$result = query("SELECT ROUND(AVG(DATEDIFF(reservation_date,reservation_timestamp)),1)\n\t\t\t\t\t\t\tFROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d'\n\t\t\t\t\t\t\tAND MONTH(reservation_date) = '%s' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['statistic_month'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_guest_year':
$result = query("SELECT SUM(reservation_pax) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_all_guest_year':
$result = query("SELECT SUM(reservation_pax) FROM `reservations` \n\t\t\t\t\t\t\tINNER JOIN `outlets` ON `outlet_id` = `reservation_outlet_id` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `property_id` = '%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['propertyID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_cxl_year':
$result = query("SELECT COUNT(*) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_booker_year':
$result = query("SELECT COUNT(*) FROM (SELECT `reservation_id` FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_hidden`= '1' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY `reservation_booker_name` ) groups \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_wait_year':
$result = query("SELECT COUNT(*) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '1' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s' \n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getResult($result);
break;
case 'statistic_online_year':
$result = query("SELECT COUNT(*) FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND `reservation_outlet_id` = '%s'\n\t\t\t\t\t\t\tAND `reservation_booker_name` = '%s'\n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year'], 'Contact Form');
return getResult($result);
break;
case 'statistic_top5_guest_year':
$result = query("SELECT reservation_guest_name, COUNT(*) as total FROM `reservations` \n\t\t\t\t\t\t\tWHERE `reservation_wait`= '0' AND `reservation_hidden`= '0' \n\t\t\t\t\t\t\tAND `reservation_outlet_id`='%d' \n\t\t\t\t\t\t\tAND YEAR(reservation_date) = '%s'\n\t\t\t\t\t\t\tGROUP BY `reservation_guest_name`\n\t\t\t\t\t\t\tORDER BY total DESC\n\t\t\t\t\t\t\tLIMIT 0,7\n\t\t\t\t\t\t\t", $_SESSION['outletID'], $_SESSION['selectedDate_year']);
return getRowList($result);
break;
case 'all_properties':
$result = query("SELECT * FROM `properties` ORDER BY name ASC");
return getRowList($result);
break;
case 'select_properties':
$result = query("SELECT * FROM `properties`\n\t\t\t\t\tWHERE `country` LIKE '%s'\n\t\t\t\t\tAND `city` LIKE '%s'\n\t\t\t\t\tORDER BY name ASC", $_SESSION['countryID'], $_SESSION['city']);
return getRowList($result);
break;
case 'property_info':
$result = query("SELECT * FROM `properties` \n\t\t\t\t\tWHERE `id` ='%d'\n LIMIT 1", $_SESSION['propertyID']);
return getRowListarray($result);
break;
case 'property_countries':
$result = query("SELECT DISTINCT country FROM `properties` \n\t\t\t\t\tORDER BY country ASC");
return getRowList($result);
break;
case 'property_countries_num':
$result = query("SELECT DISTINCT country FROM `properties` \n\t\t\t\t\tORDER BY country ASC");
return mysql_num_rows($result);
break;
case 'property_cities':
$result = query("SELECT DISTINCT city,country FROM `properties`\n\t\t\t\t\tWHERE `country` ='%s'\n\t\t\t\t\tORDER BY city ASC", $_SESSION['countryID']);
return getRowList($result);
break;
case 'property_cities_num':
$result = query("SELECT DISTINCT city,country FROM `properties`\n\t\t\t\t\tWHERE `country` ='%s'\n\t\t\t\t\tORDER BY city ASC", $_SESSION['countryID']);
return mysql_num_rows($result);
break;
case 'view_img':
$result = query("SELECT img_filename FROM `properties` \n\t\t\t\t\tWHERE `id` ='%d'\n LIMIT 1", $_SESSION['property']);
return getResult($result);
break;
case 'featured_outlet':
$result = query("SELECT * FROM `outlets`\n\t\t\t\t\tWHERE outlet_id >= (SELECT FLOOR( MAX(outlet_id) * RAND()) FROM `outlets` ) \n\t\t\t\t\tAND ( `saison_year` = 0 OR `saison_year` = '%d' )\n\t\t\t\t\tAND `webform` = '1'\n\t\t\t\t\tORDER BY outlet_id LIMIT 1", $_SESSION['selectedDate_year']);
return getRowListarray($result);
break;
case 'del_properties':
$result = query("DELETE FROM `properties` WHERE `id`='%d' LIMIT 1", $cellid);
return $result;
break;
case 'check_username':
$result = query("SELECT username FROM `plc_users` WHERE `username`='%s'", $value);
return $result;
break;
case 'check_unique_id':
$result = query("SELECT COUNT(*) FROM `reservations` WHERE `reservation_bookingnumber`='%s'", $_SESSION['PWD']);
return getResult($result);
break;
case 'store_unique_id':
$result = query("UPDATE `reservations` SET reservation_bookingnumber = '' WHERE `reservation_date`<'%s'", $today);
return $result;
break;
case 'sanitize_unique_id':
$result = query("UPDATE `reservations` SET reservation_bookingnumber = '' WHERE `reservation_date`<'%s'", $before_yesterday);
return $result;
break;
}
}
// basic settings
$max_requests = max_requests();
// Fetch GET data
$_SESSION['reservation_guest_name'] = $_GET['name'] ? $_GET['name'] : '';
// do not proceed when name GET is empty - cleat api token
$api_token = $_SESSION['reservation_guest_name'] != '' ? $_GET['token'] : '';
// *** DATABASE queries ***
// check token
if ($api_token != '') {
//check the user's API
$request_count = validate_requests($api_token);
// database request
if ($status['code'] != 400 && $request_count < $max_requests) {
// return database property results
$sql = "SELECT DISTINCT reservation_title,\n reservation_guest_name, reservation_guest_adress, reservation_guest_city,\n reservation_guest_email, reservation_guest_phone \n FROM reservations WHERE reservation_guest_name = '" . $_SESSION['reservation_guest_name'] . "' \n GROUP BY reservation_guest_name";
$data = getRowList(query($sql));
// create some status code/message
if (!empty($data)) {
$status['code'] = 200;
$status['status'] = getStatusCodeMessage($status['code']);
} else {
$status['code'] = 204;
$status['status'] = getStatusCodeMessage($status['code']);
}
}
} else {
// create some error code/message
$status['code'] = 401;
$status['status'] = getStatusCodeMessage($status['code']);
}
$result['status'] = $status;
$data = array();
$status = array();
$result = array();
// basic settings
$max_requests = max_requests();
// Fetch GET data
$_SESSION['outletID'] = $_GET['outletID'] ? $_GET['outletID'] : '';
$_SESSION['property'] = $_GET['propertyID'] ? $_GET['propertyID'] : '1';
$_SESSION['selectedDate_year'] = $_GET['year'] ? $_GET['year'] : date('Y');
$api_token = $_GET['token'];
// *** DATABASE queries ***
// check token
if ($api_token != '') {
$sql = "SELECT request_count, last_login FROM `api_users` WHERE `token` = '" . $api_token . "'";
$select = query($sql);
$login = getRowList($select);
$now = date('Y-m-d');
//check the user's API
$request_count = validate_requests($api_token);
// database request
if ($status['code'] != 400 && $request_count < $max_requests) {
// return database property results
// return database outlet results
if ($_SESSION['outletID'] != '') {
$data = querySQL('db_outlet_info');
} else {
$data = querySQL('db_all_outlets');
}
// create some status code/message
if (!empty($data)) {
$status['code'] = 200;
$data = array();
$status = array();
$result = array();
// basic settings
$_SESSION['storno'] = 0;
$_SESSION['wait'] = 0;
$max_requests = max_requests();
// Fetch GET data
$_SESSION['outletID'] = $_GET['outletID'];
$_SESSION['selectedDate'] = $_GET['selectedDate'];
$api_token = $_GET['token'];
// *** DATABASE queries ***
// check token
$sql = "SELECT request_count, last_login FROM `api_users` WHERE `token` = '" . $api_token . "'";
$result = query($sql);
$login = getRowList($result);
if (!empty($login)) {
$now = date('Y-m-d');
// count API requests per day
if ($login->last_login == $now) {
$sql = "UPDATE `api_users` SET \n `request_count` = request_count+1,\n WHERE `token` = '" . $api_token . "'";
} else {
$sql = "UPDATE `api_users` SET \n `request_count` = 1, \n `last_login` = CURDATE()\n WHERE `token` = '" . $api_token . "'";
}
$result = query($sql);
// database request
if ($login->request_count < $max_requests) {
// return database reservations results
$data = querySQL('reservations');
// create some status code/message
if (!empty($data)) {
$i++;
$table_updates[$i]['table'] = "settings";
$table_updates[$i]['field'] = "guest_type_text_WALK";
$table_updates[$i]['query'] = "ALTER TABLE `settings` ADD `guest_type_text_WALK` VARCHAR( 255 ) DEFAULT NULL";
$i++;
// ------------------------------------------------
//echo "<pre>";
//print_r($table_updates);
//echo "</pre>";
// BEGIN UPDATE FIELDS ----------------------------
foreach ($table_updates as $table_update) {
// reset update variable
$update = 'NO';
$query = "SHOW COLUMNS FROM `" . $table_update['table'] . "`;";
$sql = query($query);
$results = getRowList($sql);
foreach ($results as $field) {
//echo $field->Field." == ".$table_update['field']."<br>";
if ($field->Field == $table_update['field']) {
$update = 'YES';
}
}
if ($update == 'NO') {
$result = query($table_update['query']);
} else {
$errorMessage .= "The field '" . $table_update['field'] . "' does already exist.<br/>";
}
if (!$result) {
$errorMessage .= mysql_error() . "<br/>";
} else {
$errorMessage .= "New field '" . $table_update['field'] . "' has been successfully created.<br/>";
