/**
* Checkin::checkbookingin()
*
* This function checks in a booking.
*
* @param integer $bookingid bookingid
* @access public
* @since 2003-09-02
* @author Christian Ehret <*****@*****.**>
*/
function checkbookingin($bookingid)
{
global $gDatabase, $tbl_booking, $errorhandler;
$guests = array();
$query = sprintf("UPDATE {$tbl_booking} " . "SET checked_in = %s " . "WHERE pk_booking_id = %s ", MetabaseGetBooleanFieldValue($gDatabase, true), $bookingid);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Checkin::checkin()', $query);
}
}
/**
* createEmailConfirmation()
*
* Create a mailto link for email confirmation
*
* @param number $bookid id of booking
* @return string mailto link
* @access public
* @since 2003-10-12
* @author Christian Ehret <*****@*****.**>
*/
function createEmailConfirmation($bookid)
{
global $tbl_booking, $tbl_guest, $tbl_address, $tbl_guest_address, $tbl_bookingcat, $tbl_country, $gDatabase, $errorhandler, $request;
include_once 'guestclass.inc.php';
$guest = new Guest();
$query = "SELECT {$tbl_booking}.fk_bookingcat_id, {$tbl_booking}.fk_guest_id, \n\t UNIX_TIMESTAMP(start_date), UNIX_TIMESTAMP(end_date), \n\t\t\t TO_DAYS( end_date ) - TO_DAYS( start_date ) AS days, \n\t\t\t {$tbl_booking}.persons, children, {$tbl_guest}.firstname, \n\t\t\t {$tbl_guest}.lastname, {$tbl_bookingcat}.bookingcat, \n\t\t\t DATE_FORMAT({$tbl_guest}.date_of_birth , '%d.%m.%Y'), \n\t\t\t postalcode, city, address, country_de, email, \n\t\t\t booking_type, {$tbl_guest}.formal_greeting, \n\t\t\t {$tbl_guest}.gender, \n\t\t\t COALESCE( CASE WHEN {$tbl_bookingcat}.description = '' THEN NULL ELSE {$tbl_bookingcat}.description END, CASE WHEN {$tbl_bookingcat}.bookingcat = '' THEN NULL ELSE {$tbl_bookingcat}.bookingcat END, '' ),\n\t\t\t children2, children3, children0 \n\t\t\t FROM {$tbl_booking} \n\t\t\t LEFT JOIN {$tbl_guest} ON ({$tbl_booking}.fk_guest_id = {$tbl_guest}.pk_guest_id) \n\t\t\t LEFT JOIN {$tbl_guest_address} ON ({$tbl_guest_address}.default_address = " . MetabaseGetBooleanFieldValue($gDatabase, true) . " \n\t\t\t AND {$tbl_guest}.pk_guest_id = {$tbl_guest_address}.pk_fk_guest_id) \n\t\t\t LEFT JOIN {$tbl_address} ON ({$tbl_address}.pk_address_id = {$tbl_guest_address}.pk_fk_address_id) \n\t\t\t LEFT JOIN {$tbl_bookingcat} ON ({$tbl_booking}.fk_bookingcat_id = {$tbl_bookingcat}.pk_bookingcat_id) \n\t\t\t LEFT JOIN {$tbl_country} ON (fk_country_id = {$tbl_country}.pk_country_id )\n\t\t\t WHERE pk_booking_id = " . $bookid;
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Booking::emailConfirmation()', $query);
} else {
if (MetabaseNumberOfRows($gDatabase, $result) == 1) {
$booking = array();
$str = "";
$bookingtype = "";
$bookingstr = "";
$isformal = MetabaseFetchBooleanResult($gDatabase, $result, 0, 16);
$dirihnen = "Dir";
if ($isformal) {
$dirihnen = "Ihnen";
}
switch (MetabaseFetchResult($gDatabase, $result, 0, 16)) {
case 'R':
$bookingtype = "Reservierungsbestätigung";
$bookingstr = "Reservierung";
break;
case 'B':
$bookingtype = "Buchungsbestätigung";
$bookingstr = "Buchung";
break;
case 'P':
$bookingtype = "Buchungsbestätigung";
$bookingstr = "Buchung";
break;
}
$body = "";
$body .= $guest->GetGreeting(MetabaseFetchResult($gDatabase, $result, 0, 1)) . "\n";
$body .= "hiermit bestätigen wir " . $dirihnen . " folgende " . $bookingstr . ":\n\n";
$body .= "Anreise: " . date("d. m. Y", MetabaseFetchResult($gDatabase, $result, 0, 2)) . "\n";
$body .= "Abreise: " . date("d. m. Y", MetabaseFetchResult($gDatabase, $result, 0, 3)) . "\n";
$body .= "Kategorie: " . MetabaseFetchResult($gDatabase, $result, 0, 19) . "\n";
$body .= "Erwachsene: " . MetabaseFetchResult($gDatabase, $result, 0, 5) . "\n";
$body .= $request->GetVar('children0', 'session') . ": " . MetabaseFetchResult($gDatabase, $result, 0, 22) . "\n";
$body .= $request->GetVar('children1', 'session') . ": " . MetabaseFetchResult($gDatabase, $result, 0, 6) . "\n";
$body .= $request->GetVar('children2', 'session') . ": " . MetabaseFetchResult($gDatabase, $result, 0, 20) . "\n";
$body .= $request->GetVar('children3', 'session') . ": " . MetabaseFetchResult($gDatabase, $result, 0, 21) . "\n\n";
$body .= "für:\n";
$body .= MetabaseFetchResult($gDatabase, $result, 0, 7) . " " . MetabaseFetchResult($gDatabase, $result, 0, 8) . "\n";
$body .= MetabaseFetchResult($gDatabase, $result, 0, 13) . "\n";
$body .= MetabaseFetchResult($gDatabase, $result, 0, 11) . " " . MetabaseFetchResult($gDatabase, $result, 0, 12) . "\n";
$body .= MetabaseFetchResult($gDatabase, $result, 0, 14) . "\n\n";
$str = "mailto:" . MetabaseFetchResult($gDatabase, $result, 0, 7) . "%20" . MetabaseFetchResult($gDatabase, $result, 0, 8) . "%20<" . MetabaseFetchResult($gDatabase, $result, 0, 15) . ">?subject={$bookingtype}" . "&body=" . rawurlencode($body);
}
return $str;
}
}
/**
* auth::auth_validatelogin()
*
* validate login
*
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
* @return number user id
*/
function auth_validatelogin()
{
global $gDatabase2, $tbl_employee, $tbl_hotel, $uid, $firstname, $request, $sess, $smarty, $errorhandler;
$auth_challenge = session_id();
$auth_username = $request->GetVar('username', 'post');
$auth_password = $request->GetVar('password', 'post');
$auth_response = $request->GetVar('response', 'post');
$this->auth["uname"] = $auth_username;
## This provides access for "loginform.ihtml"
$query = sprintf("SELECT e.pk_employee_id, e.password, e.firstname, e.lastname, \n\t\t e.login, e.fk_hotel_id, h.database_schema, h.hotel_code \n\t\t\t\t\t\t FROM {$tbl_employee} e, {$tbl_hotel} h \n\t\t\t\t\t\t WHERE e.login = %s \n\t\t\t\t\t\t AND e.locked = %s \n\t\t\t\t\t\t AND ISNULL(deleted_date)\n\t\t\t\t\t\t AND e.fk_hotel_id = h.pk_hotel_id ", MetabaseGetTextFieldValue($gDatabase2, addslashes($auth_username)), MetabaseGetBooleanFieldValue($gDatabase2, false));
$result = MetabaseQuery($gDatabase2, $query);
if (!$result) {
$errorhandler->display('SQL', 'auth::auth_validatelogin()', $query);
} else {
if (MetabaseNumberOfRows($gDatabase2, $result) != 0) {
if ($result) {
$uid = MetabaseFetchResult($gDatabase2, $result, 0, 0);
$pass = MetabaseFetchResult($gDatabase2, $result, 0, 1);
## Password is stored as a md5 hash
$firstname = MetabaseFetchResult($gDatabase2, $result, 0, 2);
$lastname = MetabaseFetchResult($gDatabase2, $result, 0, 3);
$login = MetabaseFetchResult($gDatabase2, $result, 0, 4);
$hotelid = MetabaseFetchResult($gDatabase2, $result, 0, 5);
$schema = MetabaseFetchResult($gDatabase2, $result, 0, 6);
$hotel_code = MetabaseFetchResult($gDatabase2, $result, 0, 7);
}
}
}
$exspected_response = md5("{$auth_username}:{$pass}:{$auth_challenge}");
// # True when JS is disabled
if ($auth_response == "") {
if (md5($auth_password) != $pass) {
// # md5 hash for non-JavaScript browsers
return false;
} else {
$sess->SetVar("firstname", $firstname);
$sess->SetVar("lastname", $lastname);
$sess->SetVar("login", $login);
$sess->SetVar("uid", $uid);
$sess->SetVar("authenticated", 'valid');
$sess->SetVar("hotelid", $hotelid);
$sess->SetVar("schema", $schema);
$sess->SetVar("hotel_code", $hotel_code);
return $uid;
}
}
// # Response is set, JS is enabled
if ($exspected_response != $auth_response) {
return false;
} else {
$sess->SetVar('firstname', $firstname);
$sess->SetVar('lastname', $lastname);
$sess->SetVar('login', $login);
$sess->SetVar('uid', $uid);
$sess->SetVar('authenticated', 'valid');
$sess->SetVar("hotelid", $hotelid);
$sess->SetVar("schema", $schema);
$sess->SetVar("hotel_code", $hotel_code);
return $uid;
}
}
/**
* Article::saveupdatePeriod
*
* Save period as new or update existing one
*
* @access public
* @since 2004-12-13
* @author Christian Ehret <*****@*****.**>
*/
function saveupdatePeriod()
{
global $gDatabase, $request, $tbl_period, $tbl_bararticle, $errorhandler;
$periodid = $request->GetVar('frm_theperiodid', 'post');
$active = false;
$auto_commit = false;
$success = MetabaseAutoCommitTransactions($gDatabase, $auto_commit);
if ($request->GetVar('frm_periodact', 'post') == 'true') {
$active = true;
$query = sprintf("UPDATE {$tbl_period} SET \n\t\t\t\t\t\t\t active = %s,\n\t\t\t\t\t\t\t updated_date = NOW(), \n\t\t\t\t\t\t\t fk_updated_user_id = %s ", MetabaseGetBooleanFieldValue($gDatabase, false), $request->GetVar('uid', 'session'));
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Article::saveupdatePeriod()', $query);
}
}
// update
if ($periodid !== '-1') {
$query = sprintf("UPDATE {$tbl_period} SET \n\t\t\t period = %s, \n\t\t\t\t\t\t\t active = %s,\n\t\t\t\t\t\t\t updated_date = NOW(), \n\t\t\t\t\t\t\t fk_updated_user_id = %s \n\t\t\t\t\t\t\t WHERE pk_period_id = %s ", MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_perioddesc', 'post')), MetabaseGetBooleanFieldValue($gDatabase, $active), $request->GetVar('uid', 'session'), $periodid);
} else {
// new
$name = "zvs_pk_period_id";
$sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$periodid);
$query = sprintf("INSERT INTO {$tbl_period}\n\t\t\t (pk_period_id, period, active, inserted_date, fk_inserted_user_id, updated_date, fk_updated_user_id)\n\t\t\t\t\t\t\t VALUES (%s, %s, %s, NOW(), %s, NULL, NULL )", $periodid, MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_perioddesc', 'post')), MetabaseGetBooleanFieldValue($gDatabase, $active), $request->GetVar('uid', 'session'));
}
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Article::saveupdatePeriod()', $query);
} else {
if ($request->GetVar('frm_cpyperiod', 'post') != -1) {
$query = "SELECT fk_bararticlecat_id, description, price, hotkey\n\t\t\t\t\t FROM {$tbl_bararticle} \n\t\t\t\t\t WHERE ISNULL(deleted_date) AND\n\t\t\t\t\t fk_period_id = " . $request->GetVar('frm_cpyperiod', 'post');
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Article::saveupdatePeriod()', $query);
}
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$name = "zvs_pk_bararticle_id";
$sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$bararticleid);
$query = sprintf("INSERT INTO {$tbl_bararticle} \n\t\t\t\t\t (pk_bararticle_id, fk_period_id, fk_bararticlecat_id,\n\t\t\t\t\t\t\t description, price, hotkey, inserted_date, \n\t\t\t\t\t\t\t fk_inserted_user_id)\n\t\t\t\t\t\t\t VALUES (%s, %s, %s, %s, %s, %s, NOW(), %s) ", $bararticleid, $periodid, MetabaseFetchResult($gDatabase, $result, $row, 0), MetabaseGetTextFieldValue($gDatabase, MetabaseFetchResult($gDatabase, $result, $row, 1)), MetabaseGetTextFieldValue($gDatabase, MetabaseFetchResult($gDatabase, $result, $row, 2)), MetabaseGetTextFieldValue($gDatabase, MetabaseFetchResult($gDatabase, $result, $row, 3)), $request->GetVar('uid', 'session'));
$result2 = MetabaseQuery($gDatabase, $query);
if (!$result2) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Article::saveupdatePeriod()', $query);
}
}
}
$success = MetabaseCommitTransaction($gDatabase);
// end transaction
$auto_commit = true;
$success = MetabaseAutoCommitTransactions($gDatabase, $auto_commit);
}
return $periodid;
}
/**
* Guest::getListWithoutEscorts()
*
* get a list of all guests
*
* @param int $bookingdetailid booking detail id
* @param int $oldguestid guest id of the original guest
* @access public
* @since 2004-08-13
* @author Christian Ehret <*****@*****.**>
*/
function getListWithoutEscorts($bookingdetailid, $oldguestid)
{
global $gDatabase, $tbl_guest, $tbl_guest_address, $tbl_booking_detail_guest, $tbl_address, $errorhandler;
$query = "SELECT pk_fk_guest_id\n\t\t\t\t FROM {$tbl_booking_detail_guest} \n\t\t\t \t WHERE pk_fk_booking_detail_id = {$bookingdetailid}\n\t\t\t\t AND pk_fk_guest_id != {$oldguestid}";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Guest::getListWithoutEscorts()', $query);
}
$row = 0;
$guests = array();
$guestids = '';
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
if ($guestids == '') {
$guestids = MetabaseFetchResult($gDatabase, $result, $row, 0);
} else {
$guestids = $guestids . ", " . MetabaseFetchResult($gDatabase, $result, $row, 0);
}
}
$query = "SELECT pk_guest_id, firstname, lastname, \n\t\t\t\t IF ( a.city = '' OR a.city IS NULL , '-', a.city )city\n\t\t\t\t FROM {$tbl_guest} g \n\t\t\t \t LEFT JOIN {$tbl_guest_address} ga ON g.pk_guest_id = ga.pk_fk_guest_id AND ga.default_address = " . MetabaseGetBooleanFieldValue($gDatabase, true) . " \n\t\t\t\t LEFT JOIN {$tbl_address} a ON ga.pk_fk_address_id = a.pk_address_id ";
if ($guestids = '') {
$query .= "WHERE pk_guest_id NOT IN ({$guestids}) ";
}
$query .= "ORDER BY lastname ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Guest::getListWithoutEscorts()', $query);
}
$row = 0;
$guests = array();
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$guests[$row] = array('guest_id' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'firstname' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'lastname' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'city' => MetabaseFetchResult($gDatabase, $result, $row, 3));
}
return $guests;
}
/**
* Employee::del()
*
* Deletes an employee
*
* @param number $userid user id
* @access public
* @since 2004-10-03
* @author Christian Ehret <*****@*****.**>
*/
function del($userid)
{
global $gDatabase2, $tbl_employee, $errorhandler, $request;
$query = sprintf("UPDATE {$tbl_employee} SET \n\t\t\t\t\t\t\t locked = %s,\n\t\t\t\t\t\t\t deleted_date = NOW(), \n\t\t\t\t\t\t\t fk_deleted_user_id = %s \n\t\t\t\t\t\t\t WHERE pk_employee_id = %s ", MetabaseGetBooleanFieldValue($gDatabase2, true), $request->GetVar('uid', 'session'), $userid);
$result = MetabaseQuery($gDatabase2, $query);
if (!$result) {
$errorhandler->display('SQL', 'Employee::del()', $query);
}
}
/**
* article::addroom()
*
* add the link to a roomcategory
*
* @param number $roomcatid room category id
* @param number $articleid article id
* @param enum $pricetype price type PP or PR
* @param boolean $included price included in room or not
* @access public
* @since 2004-03-07
* @author Christian Ehret <*****@*****.**>
*/
function addroom($roomcatid, $articleid, $pricetype, $included)
{
global $gDatabase, $request, $tbl_roomcat_article, $errorhandler;
$person = false;
$children = false;
if ($request->GetVar('frm_person', 'post') == 'true') {
$person = true;
}
if ($request->GetVar('frm_children', 'post') == 'true') {
$children = true;
}
if ($request->GetVar('frm_children2', 'post') == 'true') {
$children2 = true;
}
if ($request->GetVar('frm_children3', 'post') == 'true') {
$children3 = true;
}
$query = sprintf("SELECT pk_fk_article_id \n\t\t\t\t\t\t FROM {$tbl_roomcat_article}\n\t\t\t\t\t\t WHERE pk_fk_roomcat_id = %s \n\t\t\t\t AND pk_fk_article_id = %s", $roomcatid, $articleid);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Article::addroom()', $query);
} elseif (MetabaseNumberOfRows($gDatabase, $result) == 0) {
$query = sprintf("INSERT INTO {$tbl_roomcat_article}\n\t\t\t\t \t\t (pk_fk_roomcat_id, pk_fk_article_id, price_type,\n\t\t\t\t\t\t person, children, children2, children3, included)\n\t\t\t\t\t\t VALUES (%s,%s, %s, %s, %s, %s, %s, %s)", $roomcatid, $articleid, MetabaseGetTextFieldValue($gDatabase, $pricetype), MetabaseGetBooleanFieldValue($gDatabase, $person), MetabaseGetBooleanFieldValue($gDatabase, $children), MetabaseGetBooleanFieldValue($gDatabase, $children2), MetabaseGetBooleanFieldValue($gDatabase, $children3), MetabaseGetBooleanFieldValue($gDatabase, $included));
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Article::addroom()', $query);
}
} else {
$query = sprintf("UPDATE {$tbl_roomcat_article} SET\n\t\t\t\t\t\t price_type = %s,\n\t\t\t\t\t\t person = %s, \n\t\t\t\t\t\t children = %s, \n\t\t\t\t\t\t children2 = %s, \n\t\t\t\t\t\t children3 = %s,\n\t\t\t\t\t\t included = %s\n\t\t\t\t\t\t WHERE pk_fk_roomcat_id = %s \n\t\t\t\t\t\t AND pk_fk_article_id = %s ", MetabaseGetTextFieldValue($gDatabase, $pricetype), MetabaseGetBooleanFieldValue($gDatabase, $person), MetabaseGetBooleanFieldValue($gDatabase, $children), MetabaseGetBooleanFieldValue($gDatabase, $children2), MetabaseGetBooleanFieldValue($gDatabase, $children3), MetabaseGetBooleanFieldValue($gDatabase, $included), $roomcatid, $articleid);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Article::addroom()', $query);
}
}
}
/**
* Guestlist::getBirthdayReminders()
*
* This function returns a list of guests wich have their birthday and the reminder set.
*
* @return array guests
* @access public
* @since 2004-07-26
* @author Christian Ehret <*****@*****.**>
*/
function getBirthdayReminders()
{
global $gDatabase, $tbl_guest, $request, $errorhandler;
$days = $request->GetVar('birthday_days', 'Session');
$guests = array();
$query = "SELECT pk_guest_id, firstname, lastname, DATE_FORMAT(date_of_birth ,'%d.%m.%Y'),\n\t\t\t\t\t\t DATE_FORMAT(now(), '%Y') - DATE_FORMAT(date_of_birth ,'%Y')\n\t\t FROM {$tbl_guest}\n\t\t\t\t\t\t WHERE DATE_FORMAT( date_of_birth, '%m%d' ) BETWEEN DATE_FORMAT( now(), '%m%d' ) AND DATE_FORMAT( DATE_ADD(now(), INTERVAL {$days} DAY), '%m%d' ) \t\t\t\t \n\t\t\t\t\t\t AND birthday_reminder = " . MetabaseGetBooleanFieldValue($gDatabase, true) . "\n\t\t\t\t\t\t ORDER BY DATE_FORMAT( date_of_birth, '%d') ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Guestlist::getBirthdayReminders()', $query);
} else {
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$guests[$row] = array('guestid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'firstname' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'lastname' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'birthday' => MetabaseFetchResult($gDatabase, $result, $row, 3), 'age' => MetabaseFetchResult($gDatabase, $result, $row, 4), 'color' => $color);
}
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
return $guests;
}
}
/**
* Kassa::pay()
*
* This function sets an article paid.
*
* @param $boughtid bought id
* @access public
* @since 2004-01-08
* @author Christian Ehret <*****@*****.**>
*/
function pay($boughtid)
{
global $gDatabase2, $tbl_bought, $request, $errorhandler;
$query = sprintf("UPDATE {$tbl_bought}\n\t\t\t\t\t\t SET paid = %s,\n\t\t\t\t\t\t updated_date = NOW(),\n\t\t\t\t\t\t fk_updated_user_id = %s\n\t\t\t\t\t\t WHERE pk_bought_id = %s", MetabaseGetBooleanFieldValue($gDatabase2, true), $request->GetVar('uid', 'session'), $boughtid);
$result = MetabaseQuery($gDatabase2, $query);
if (!$result) {
$errorhandler->display('SQL', 'Kassa::pay()', $query);
}
}
/**
* account::book()
*
* book
*
* @param integer $guestid guest id
* @param integer $bookingid booking id
* @param datetime $dateofpayment date of payment
* @param text $description description
* @param number $amount amount
* @param integer $paycatid paycat id
* @param integer $receiptid receipt it (-1 if no receipt)
* @access public
* @since 2004-03-10
* @author Christian Ehret <*****@*****.**>
*/
function book($guestid, $bookingid, $dateofpayment, $description, $amount, $paycatid, $receiptid, $on_receipt)
{
global $gDatabase, $tbl_account, $tbl_booking, $errorhandler, $request;
$name = "zvs_pk_account_id";
$sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$accountid);
if (($on_receipt === true || $on_receipt == 'true') && $receiptid != -1) {
$on_receipt = true;
} else {
$on_receipt = false;
}
list($day, $month, $year) = split('[.]', $dateofpayment);
$dateofpayment = mktime(0, 0, 0, $month, $day, $year);
if ($receiptid == -1) {
$query = sprintf("INSERT INTO {$tbl_account}\n\t\t\t\t\t\t\t (pk_account_id, fk_guest_id, fk_booking_id, date_payment, description, amount,\n\t\t\t\t\t\t\t inserted_date, fk_inserted_user_id, fk_paycat_id, on_receipt)\n\t\t\t\t\t\t\t VALUES (%s, %s, %s, %s, %s, %s, NOW(), %s, %s, %s )", $accountid, $guestid, $bookingid, MetabaseGetTextFieldValue($gDatabase, date("Y-m-d", $dateofpayment)), MetabaseGetTextFieldValue($gDatabase, $description), $amount, $request->GetVar('uid', 'session'), $paycatid, MetabaseGetBooleanFieldValue($gDatabase, $on_receipt));
} else {
$query = sprintf("INSERT INTO {$tbl_account}\n\t\t\t\t\t\t\t (pk_account_id, fk_guest_id, fk_booking_id, date_payment, description, amount, \n\t\t\t\t\t\t\t fk_receipt_id, allocated,\n\t\t\t\t\t\t\t inserted_date, fk_inserted_user_id, fk_paycat_id, on_receipt)\n\t\t\t\t\t\t\t VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW(), %s, %s, %s )", $accountid, $guestid, $bookingid, MetabaseGetTextFieldValue($gDatabase, date("Y-m-d", $dateofpayment)), MetabaseGetTextFieldValue($gDatabase, $description), $amount, $receiptid, MetabaseGetBooleanFieldValue($gDatabase, true), $request->GetVar('uid', 'session'), $paycatid, MetabaseGetBooleanFieldValue($gDatabase, $on_receipt));
}
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Account::book()', $query);
} else {
$this->setStatus($receiptid, $bookingid);
/*
$receiptsum = $this->getReceiptSum($receiptid);
$receiptsum = $receiptsum['sum'];
$query = "SELECT booking_type FROM $tbl_booking
WHERE pk_booking_id = $bookingid";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Account::book()', $query);
} else {
if (MetabaseFetchResult($gDatabase, $result, 0, 0) == 'R') {
if ($receiptsum <= 0 && $receiptid != -1) {
$type = 'P';
} else {
$type = 'B';
}
$query = sprintf("UPDATE $tbl_booking
SET booking_type = %s
WHERE pk_booking_id = %s",
MetabaseGetTextFieldValue($gDatabase, $type),
$bookingid
);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Account::book()', $query);
}
} elseif (MetabaseFetchResult($gDatabase, $result, 0, 0) == 'B') {
if ($receiptsum <= 0 && $receiptid != -1) {
$query = sprintf("UPDATE $tbl_booking
SET booking_type = %s
WHERE pk_booking_id = %s",
MetabaseGetTextFieldValue($gDatabase, 'P'),
$bookingid
);
print $query;
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Account::book()', $query);
}
}
}
}
*/
}
}
/**
* Receipt::getAddress()
*
* Get the address of a guests
*
* @param number $guestid id of a guest
* @return string guest adress
* @access public
* @since 2004-04-21
* @author Christian Ehret <*****@*****.**>
*/
function getAddress($guestid)
{
global $tbl_guest, $tbl_guest_address, $tbl_address, $tbl_salutation, $tbl_country, $gDatabase, $errorhandler;
$query = "SELECT g.firstname, g.lastname, g.academic_title,\n\t\t\t\t\t\t a.postalcode, a.city, a.address, sal.salutation_de, coun.country_de\n\t\t\t\t FROM {$tbl_guest} g \n\t\t\t\t \tLEFT JOIN {$tbl_guest_address} ga ON (ga.default_address = " . MetabaseGetBooleanFieldValue($gDatabase, true) . " \n\t\t\t\t\t \tAND g.pk_guest_id = ga.pk_fk_guest_id) \n\t\t\t\t \tLEFT JOIN {$tbl_address} a ON (a.pk_address_id = ga.pk_fk_address_id) \n\t\t\t\t\tLEFT JOIN {$tbl_salutation} sal ON (g.fk_salutation_id = sal.pk_salutation_id) \n\t\t\t\t\tLEFT JOIN {$tbl_country} coun ON (a.fk_country_id = coun.pk_country_id )\t\n\t\t\t\t\tWHERE g.pk_guest_id = " . $guestid;
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Receipt::getAddress()', $query);
}
$address = "";
if (MetabaseFetchResult($gDatabase, $result, 0, 6) !== 'n/a') {
$address .= MetabaseFetchResult($gDatabase, $result, 0, 6) . "\n";
}
if (trim(MetabaseFetchResult($gDatabase, $result, 0, 2)) !== "") {
$address .= MetabaseFetchResult($gDatabase, $result, 0, 2) . " ";
}
$address .= MetabaseFetchResult($gDatabase, $result, 0, 0) . " " . MetabaseFetchResult($gDatabase, $result, 0, 1) . "\n";
$address .= MetabaseFetchResult($gDatabase, $result, 0, 5) . "\n";
$address .= MetabaseFetchResult($gDatabase, $result, 0, 3) . " " . MetabaseFetchResult($gDatabase, $result, 0, 4) . "\n\n";
if (MetabaseFetchResult($gDatabase, $result, 0, 7) !== 'n/a') {
$address .= MetabaseFetchResult($gDatabase, $result, 0, 7);
}
return $address;
}
/**
* Guest::importZVSGuest()
*
* Import ZVS User
*
* @access public
* @since 2004-12-14
* @author Christian Ehret <*****@*****.**>
*/
function importZVSGuest()
{
global $gDatabase, $tbl_bookingcat, $tbl_barguest, $tbl_zvs_bookingcat, $tbl_zvs_guest, $tbl_zvs_booking, $tbl_zvs_booking_detail, $gZVSDatabase, $tbl_user, $errorhandler, $request;
$guests = array();
$query = "SELECT pk_guest_id, firstname, lastname, fk_bookingcat_id " . sprintf("FROM {$tbl_zvs_booking}, {$tbl_zvs_guest}, {$tbl_zvs_booking_detail} \n\t\t\t\t\t\t WHERE checked_in = %s \n\t\t\t\t\t\t AND checked_out = %s\n\t\t\t\t\t\t AND pk_guest_id = fk_guest_id \n\t\t\t\t\t\t AND pk_booking_id = fk_booking_id \n\t\t\t\t\t\t AND ISNULL({$tbl_zvs_booking}.deleted_date) \n\t\t\t\t\t\t ORDER BY lastname", MetabaseGetBooleanFieldValue($gZVSDatabase, true), MetabaseGetBooleanFieldValue($gZVSDatabase, false));
$zvsresult = MetabaseQuery($gZVSDatabase, $query);
if (!$zvsresult) {
$errorhandler->display('SQL', 'Guest::importZVSGuest()', $query);
} else {
for ($row = 0; ($eor = MetabaseEndOfResult($gZVSDatabase, $zvsresult)) == 0; ++$row) {
$query = "SELECT pk_bookingcat_id \n\t\t\t\t FROM {$tbl_bookingcat}\n\t\t\t\t\t\t WHERE fk_zvsbookingcat_id = " . MetabaseFetchResult($gZVSDatabase, $zvsresult, $row, 3);
$catresult = MetabaseQuery($gDatabase, $query);
if (!$catresult) {
$errorhandler->display('SQL', 'Guest::importZVSGuest()', $query);
} else {
$query = "SELECT pk_barguest_id \n\t\t\t\t FROM {$tbl_barguest}\n\t\t\t\t\t\t WHERE fk_zvsguest_id = " . MetabaseFetchResult($gZVSDatabase, $zvsresult, $row, 0);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Guest::importZVSGuest()', $query);
}
if (MetabaseNumberOfRows($gDatabase, $result) == 1) {
$query = sprintf("UPDATE {$tbl_barguest} \n\t\t\t\t\t\t\t\t\t\t SET firstname = %s, \n\t\t\t\t\t\t\t\t\t\t lastname = %s,\n\t\t\t\t\t\t\t\t\t\t fk_bookingcat_id = %s,\n\t\t\t\t\t\t\t\t\t\t deleted_date = NULL,\n\t\t\t\t\t\t\t\t\t\t fk_deleted_user_id = NULL\n\t\t\t\t\t\t\t\t\t\t WHERE pk_barguest_id = %s", MetabaseGetTextFieldValue($gDatabase, MetabaseFetchResult($gZVSDatabase, $zvsresult, $row, 1)), MetabaseGetTextFieldValue($gDatabase, MetabaseFetchResult($gZVSDatabase, $zvsresult, $row, 2)), MetabaseFetchResult($gDatabase, $catresult, 0, 0), MetabaseFetchResult($gDatabase, $result, 0, 0));
} else {
$name = "zvs_pk_barguest_id";
$sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$barguestid);
$query = sprintf("INSERT INTO {$tbl_barguest}\n\t\t\t\t\t\t\t\t\t\t (pk_barguest_id, fk_zvsguest_id, fk_bookingcat_id, firstname, lastname, inserted_date, fk_inserted_user_id)\n\t\t\t\t\t\t\t\t\t\t VALUES (%s, %s, %s, %s, %s, NOW(), %s)", $barguestid, MetabaseFetchResult($gZVSDatabase, $zvsresult, $row, 0), MetabaseFetchResult($gDatabase, $catresult, 0, 0), MetabaseGetTextFieldValue($gDatabase, MetabaseFetchResult($gZVSDatabase, $zvsresult, $row, 1)), MetabaseGetTextFieldValue($gDatabase, MetabaseFetchResult($gZVSDatabase, $zvsresult, $row, 2)), 1);
}
$result2 = MetabaseQuery($gDatabase, $query);
if (!$result2) {
$errorhandler->display('SQL', 'Guest::importZVSGuest()', $query);
}
}
}
}
}
/**
* Booking::showPossibleEscorts()
*
* show all guest who have a booking with a guest without actual escorts
*
* @param int $bookingdetailid booking detail id
* @param int $guestid guest id
* @return array guest data
* @access public
* @since 2004-06-27
* @author Christian Ehret <*****@*****.**>
*/
function showPossibleEscorts($bookingdetailid, $guestid)
{
global $gDatabase, $tbl_guest, $tbl_address, $tbl_guest_address, $tbl_booking_detail_guest, $tbl_country, $errorhandler, $request;
$guest = array();
$exclguest = array();
$query = "SELECT pk_fk_guest_id FROM {$tbl_booking_detail_guest} WHERE pk_fk_booking_detail_id = {$bookingdetailid}";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Booking::showPossibleEscorts()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$exclguest[$row] = MetabaseFetchResult($gDatabase, $result, $row, 0);
}
}
MetabaseFreeResult($gDatabase, $result);
$query = "( \n\t\t\t\t\tSELECT DISTINCT b.fk_guest_id AS pk_guest_id, g.lastname AS lastname, \n\t\t\t\t\t\tg.firstname AS firstname, a.city AS city, c.country_de AS country_de \n\t\t\t\t\tFROM zvs_booking_detail_guest bdg \n\t\t\t\t\tLEFT JOIN zvs_booking_detail bd ON bdg.pk_fk_booking_detail_id = bd.pk_booking_detail_id \n\t\t\t\t\tLEFT JOIN zvs_booking b ON bd.fk_booking_id = b.pk_booking_id \n\t\t\t\t\tLEFT JOIN zvs_guest g ON b.fk_guest_id = g.pk_guest_id \n\t\t\t\t LEFT JOIN {$tbl_guest_address} ga ON g.pk_guest_id = ga.pk_fk_guest_id \n\t\t\t\t \t\t\t\t\t\t\tAND ga.default_address = " . MetabaseGetBooleanFieldValue($gDatabase, true) . "\t\t\t\t \n\t\t\t\t LEFT JOIN {$tbl_address} a ON a.pk_address_id = ga.pk_fk_address_id\n\t LEFT JOIN {$tbl_country} c ON c.pk_country_id = a.fk_country_id \t\t\t\t\t\n\t\t\t\t\tWHERE bdg.pk_fk_guest_id = {$guestid} \n\t\t\t\t\t\tAND b.fk_guest_id != {$guestid} \n\t\t\t\t";
if (count($exclguest > 0)) {
$query .= " AND g.pk_guest_id ";
if (count($exclguest) > 1) {
$query .= " NOT IN (";
for ($i = 0; $i < count($exclguest); $i++) {
if ($i != 0) {
$query .= ", ";
}
$query .= $exclguest[$i];
}
$query .= ") ";
} else {
$query .= "<> {$exclguest['0']} ";
}
}
$query .= "\tGROUP BY pk_guest_id \t\t\t\t\t\n\t\t\t\t ) UNION ( \n\t\t\t\t SELECT DISTINCT bdg2.pk_fk_guest_id AS pk_guest_id, g.lastname AS lastname, \n\t\t\t\t\t\tg.firstname AS firstname, a.city AS city, c.country_de AS country_de\n\t\t\t\t\tFROM zvs_booking_detail_guest bdg \n\t\t\t\t\tLEFT JOIN zvs_booking_detail bd ON bdg.pk_fk_booking_detail_id = bd.pk_booking_detail_id \n\t\t\t\t\tLEFT JOIN zvs_booking b ON bd.fk_booking_id = b.pk_booking_id \n\t\t\t\t\tLEFT JOIN zvs_booking_detail bd2 ON b.pk_booking_id = bd2.fk_booking_id \n\t\t\t\t\tLEFT JOIN zvs_booking_detail_guest bdg2 ON bd2.pk_booking_detail_id = bdg2.pk_fk_booking_detail_id \n\t\t\t\t\tLEFT JOIN zvs_guest g ON bdg2.pk_fk_guest_id = g.pk_guest_id \n\t\t\t\t LEFT JOIN {$tbl_guest_address} ga ON g.pk_guest_id = ga.pk_fk_guest_id \n\t\t\t\t \tAND ga.default_address = " . MetabaseGetBooleanFieldValue($gDatabase, true) . "\n\t\t\t\t LEFT JOIN {$tbl_address} a ON a.pk_address_id = ga.pk_fk_address_id\n\t LEFT JOIN {$tbl_country} c ON c.pk_country_id = a.fk_country_id \t\t\t\t\t\n\t\t\t\t\tWHERE bdg.pk_fk_guest_id = {$guestid} \n\t\t\t\t\t\t AND bdg2.pk_fk_guest_id != {$guestid} \n\t\t\t\t";
if (count($exclguest > 0)) {
$query .= " AND g.pk_guest_id ";
if (count($exclguest) > 1) {
$query .= " NOT IN (";
for ($i = 0; $i < count($exclguest); $i++) {
if ($i != 0) {
$query .= ", ";
}
$query .= $exclguest[$i];
}
$query .= " ) ";
} else {
$query .= "<> {$exclguest['0']} ";
}
}
$query .= " GROUP BY pk_guest_id \n\t\t\t\t) \n\t\t\t\tORDER BY lastname, firstname ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Booking::showPossibleEscorts()', $query);
} else {
$row = 0;
for ($row = 0; $row < MetabaseNumberOfRows($gDatabase, $result); ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$guest[$row] = array('guestid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'firstname' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'lastname' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'city' => MetabaseFetchResult($gDatabase, $result, $row, 3), 'country_name' => MetabaseFetchResult($gDatabase, $result, $row, 4), 'color' => $color);
}
}
return $guest;
}
