/**
* roomchangelist::getlist()
*
* This function returns a list of room changes.
*
* @param date $start start date
* @param date $end end date
* @return array guests
* @access public
* @since 2004-06-12
* @author Christian Ehret <*****@*****.**>
*/
function getlist($start, $end)
{
global $gDatabase, $tbl_booking, $tbl_room, $tbl_guest, $tbl_booking_detail, $tbl_bookingcat, $tbl_booking_detail_guest, $errorhandler;
$bookings = array();
//$start = $start -1;
$end = $end - 1;
$query = "SELECT bd.end_date, DATE_FORMAT(bd.end_date, '%d.%m.%Y'), \n\t\t\t\t\t\t r.room, bd.fk_room_id " . sprintf("FROM {$tbl_booking} b, {$tbl_booking_detail} bd\n\t\t\t\t \t\t LEFT JOIN {$tbl_room} r ON r.pk_room_id = bd.fk_room_id\n\t\t\t\t\t\t WHERE (UNIX_TIMESTAMP(bd.end_date) BETWEEN %s AND %s) \n\t\t\t\t\t\t AND b.pk_booking_id = bd.fk_booking_id \n\t\t\t\t\t\t AND ISNULL(b.deleted_date) \n\t\t\t\t\t\t ORDER BY bd.end_date, r.room", MetabaseGetTextFieldValue($gDatabase, $start), MetabaseGetTextFieldValue($gDatabase, $end));
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Roomchangelist::getlist()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$enddate = MetabaseFetchResult($gDatabase, $result, $row, 0);
$roomid = MetabaseFetchResult($gDatabase, $result, $row, 3);
$query = "SELECT \n\t\t DATE_FORMAT(bd.start_date, '%d.%m.%Y'), \n\t\t\t\t \t\t bc.bookingcat,\n\t\t\t\t\t\t b.persons, b.children, b.children2, b.children3, b.booking_type,\n\t\t\t\t\t\t g.firstname, g.lastname, b.additional_info, DATE_FORMAT(bd.end_date, '%d.%m.%Y'),\n\t\t\t\t\t\t b.children0\n FROM {$tbl_booking} b, {$tbl_booking_detail} bd\n\t\t\t\t\t\t LEFT JOIN {$tbl_guest} g ON b.fk_guest_id = g.pk_guest_id\n\t\t\t\t \t\t LEFT JOIN {$tbl_room} r ON r.pk_room_id = bd.fk_room_id\n\t\t\t\t\t\t LEFT JOIN {$tbl_bookingcat} bc ON b.fk_bookingcat_id = bc.pk_bookingcat_id\n\t\t\t\t\t\t WHERE bd.fk_room_id = {$roomid} AND bd.start_date >= '{$enddate}'\n\t\t\t\t\t\t AND b.pk_booking_id = bd.fk_booking_id \n\t\t\t\t\t\t AND ISNULL(b.deleted_date) \n\t\t\t\t\t\t ORDER BY bd.start_date LIMIT 0,1";
$result2 = MetabaseQuery($gDatabase, $query);
if (!$result2) {
$errorhandler->display('SQL', 'Roomchangelist::getlist()', $query);
} elseif (MetabaseNumberOfRows($gDatabase, $result2) == 1) {
$bookings[$row] = array('enddate' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'room' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'color' => $color, 'startdate' => MetabaseFetchResult($gDatabase, $result2, 0, 0), 'bookingcat' => MetabaseFetchResult($gDatabase, $result2, 0, 1), 'person' => MetabaseFetchResult($gDatabase, $result2, 0, 2), 'children1' => MetabaseFetchResult($gDatabase, $result2, 0, 3), 'children2' => MetabaseFetchResult($gDatabase, $result2, 0, 4), 'children3' => MetabaseFetchResult($gDatabase, $result2, 0, 5), 'firstname' => MetabaseFetchResult($gDatabase, $result2, 0, 7), 'lastname' => MetabaseFetchResult($gDatabase, $result2, 0, 8), 'addinfo' => MetabaseFetchResult($gDatabase, $result2, 0, 9), 'enddate2' => MetabaseFetchResult($gDatabase, $result2, 0, 10), 'children0' => MetabaseFetchResult($gDatabase, $result2, 0, 11));
} else {
$bookings[$row] = array('enddate' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'room' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'color' => $color, 'startdate' => "", 'bookingcat' => "", 'person' => "", 'children1' => "", 'children2' => "", 'children3' => "", 'firstname' => "", 'lastname' => "", 'addinfo' => "", 'enddate2' => "");
}
}
}
return $bookings;
}
/**
* 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;
}
}
function DumpDatabase($arguments)
{
if (!isset($arguments["Output"])) {
return "it was not specified a valid output function";
}
$output = $arguments["Output"];
$eol = isset($arguments["EndOfLine"]) ? $arguments["EndOfLine"] : "\n";
$dump_definition = isset($arguments["Definition"]);
$sequences = array();
if (isset($this->database_definition["SEQUENCES"])) {
for ($error = "", Reset($this->database_definition["SEQUENCES"]), $sequence = 0; $sequence < count($this->database_definition["SEQUENCES"]); Next($this->database_definition["SEQUENCES"]), $sequence++) {
$sequence_name = Key($this->database_definition["SEQUENCES"]);
if (isset($this->database_definition["SEQUENCES"][$sequence_name]["on"])) {
$table = $this->database_definition["SEQUENCES"][$sequence_name]["on"]["table"];
} else {
$table = "";
}
$sequences[$table][] = $sequence_name;
}
}
$previous_database_name = strcmp($this->database_definition["name"], "") ? MetabaseSetDatabase($this->database, $this->database_definition["name"]) : "";
$output("<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>{$eol}");
$output("<database>{$eol}{$eol} <name>" . $this->database_definition["name"] . "</name>{$eol} <create>" . $this->database_definition["create"] . "</create>{$eol}");
for ($error = "", Reset($this->database_definition["TABLES"]), $table = 0; $table < count($this->database_definition["TABLES"]); Next($this->database_definition["TABLES"]), $table++) {
$table_name = Key($this->database_definition["TABLES"]);
$output("{$eol} <table>{$eol}{$eol} <name>{$table_name}</name>{$eol}");
$output("{$eol} <declaration>{$eol}");
$fields = $this->database_definition["TABLES"][$table_name]["FIELDS"];
for (Reset($fields), $field_number = 0; $field_number < count($fields); $field_number++, Next($fields)) {
$field_name = Key($fields);
$field = $fields[$field_name];
if (!isset($field["type"])) {
return "it was not specified the type of the field \"{$field_name}\" of the table \"{$table_name}\"";
}
$output("{$eol} <field>{$eol} <name>{$field_name}</name>{$eol} <type>" . $field["type"] . "</type>{$eol}");
switch ($field["type"]) {
case "integer":
if (isset($field["unsigned"])) {
$output(" <unsigned>1</unsigned>{$eol}");
}
break;
case "text":
case "clob":
case "blob":
if (isset($field["length"])) {
$output(" <length>" . $field["length"] . "</length>{$eol}");
}
break;
case "boolean":
case "date":
case "timestamp":
case "time":
case "float":
case "decimal":
break;
default:
return "type \"" . $field["type"] . "\" is not yet supported";
}
if (isset($field["notnull"])) {
$output(" <notnull>1</notnull>{$eol}");
}
if (isset($field["default"])) {
$output(" <default>" . $this->EscapeSpecialCharacters($field["default"]) . "</default>{$eol}");
}
$output(" </field>{$eol}");
}
if (isset($this->database_definition["TABLES"][$table_name]["INDEXES"])) {
$indexes = $this->database_definition["TABLES"][$table_name]["INDEXES"];
for (Reset($indexes), $index_number = 0; $index_number < count($indexes); $index_number++, Next($indexes)) {
$index_name = Key($indexes);
$index = $indexes[$index_name];
$output("{$eol} <index>{$eol} <name>{$index_name}</name>{$eol}");
if (isset($indexes[$index_name]["unique"])) {
$output(" <unique>1</unique>{$eol}");
}
for (Reset($index["FIELDS"]), $field_number = 0; $field_number < count($index["FIELDS"]); $field_number++, Next($index["FIELDS"])) {
$field_name = Key($index["FIELDS"]);
$field = $index["FIELDS"][$field_name];
$output(" <field>{$eol} <name>{$field_name}</name>{$eol}");
if (isset($field["sorting"])) {
$output(" <sorting>" . $field["sorting"] . "</sorting>{$eol}");
}
$output(" </field>{$eol}");
}
$output(" </index>{$eol}");
}
}
$output("{$eol} </declaration>{$eol}");
if ($dump_definition) {
if (isset($this->database_definition["TABLES"][$table_name]["initialization"])) {
$output("{$eol} <initialization>{$eol}");
$instructions = $this->database_definition["TABLES"][$table_name]["initialization"];
for (Reset($instructions), $instruction = 0; $instruction < count($instructions); $instruction++, Next($instructions)) {
switch ($instructions[$instruction]["type"]) {
case "insert":
$output("{$eol} <insert>{$eol}");
$fields = $instructions[$instruction]["FIELDS"];
for (Reset($fields), $field_number = 0; $field_number < count($fields); $field_number++, Next($fields)) {
$field_name = Key($fields);
$output("{$eol} <field>{$eol} <name>{$field_name}</name>{$eol} <value>" . $this->EscapeSpecialCharacters($fields[$field_name]) . "</value>{$eol} </field>{$eol}");
}
$output("{$eol} </insert>{$eol}");
break;
}
}
$output("{$eol} </initialization>{$eol}");
}
} else {
if (count($this->database_definition["TABLES"][$table_name]["FIELDS"]) == 0) {
return "the definition of the table \"{$table_name}\" does not contain any fields";
}
if (strcmp($error = $this->GetFields($table_name, $query_fields), "")) {
return $error;
}
if ($support_summary_functions = MetabaseSupport($this->database, "SummaryFunctions")) {
if (($result = MetabaseQuery($this->database, "SELECT COUNT(*) FROM {$table_name}")) == 0) {
return MetabaseError($this->database);
}
$rows = MetabaseFetchResult($this->database, $result, 0, 0);
MetabaseFreeResult($this->database, $result);
}
if (($result = MetabaseQuery($this->database, "SELECT {$query_fields} FROM {$table_name}")) == 0) {
return MetabaseError($this->database);
}
if (!$support_summary_functions) {
$rows = MetabaseNumberOfRows($this->database, $result);
}
if ($rows > 0) {
$output("{$eol} <initialization>{$eol}");
for ($row = 0; $row < $rows; $row++) {
$output("{$eol} <insert>{$eol}");
for (Reset($fields), $field_number = 0; $field_number < count($fields); $field_number++, Next($fields)) {
$field_name = Key($fields);
if (!MetabaseResultIsNull($this->database, $result, $row, $field_name)) {
$field = $fields[$field_name];
$output("{$eol} <field>{$eol} <name>{$field_name}</name>{$eol} <value>");
switch ($field["type"]) {
case "integer":
case "text":
$output($this->EscapeSpecialCharacters(MetabaseFetchResult($this->database, $result, $row, $field_name)));
break;
case "clob":
if (!($lob = MetabaseFetchCLOBResult($this->database, $result, $row, $field_name))) {
return MetabaseError($this->database);
}
while (!MetabaseEndOfLOB($lob)) {
if (MetabaseReadLOB($lob, $data, 8000) < 0) {
return MetabaseLOBError($lob);
}
$output($this->EscapeSpecialCharacters($data));
}
MetabaseDestroyLOB($lob);
break;
case "blob":
if (!($lob = MetabaseFetchBLOBResult($this->database, $result, $row, $field_name))) {
return MetabaseError($this->database);
}
while (!MetabaseEndOfLOB($lob)) {
if (MetabaseReadLOB($lob, $data, 8000) < 0) {
return MetabaseLOBError($lob);
}
$output(bin2hex($data));
}
MetabaseDestroyLOB($lob);
break;
case "float":
$output($this->EscapeSpecialCharacters(MetabaseFetchFloatResult($this->database, $result, $row, $field_name)));
break;
case "decimal":
$output($this->EscapeSpecialCharacters(MetabaseFetchDecimalResult($this->database, $result, $row, $field_name)));
break;
case "boolean":
$output($this->EscapeSpecialCharacters(MetabaseFetchBooleanResult($this->database, $result, $row, $field_name)));
break;
case "date":
$output($this->EscapeSpecialCharacters(MetabaseFetchDateResult($this->database, $result, $row, $field_name)));
break;
case "timestamp":
$output($this->EscapeSpecialCharacters(MetabaseFetchTimestampResult($this->database, $result, $row, $field_name)));
break;
case "time":
$output($this->EscapeSpecialCharacters(MetabaseFetchTimeResult($this->database, $result, $row, $field_name)));
break;
default:
return "type \"" . $field["type"] . "\" is not yet supported";
}
$output("</value>{$eol} </field>{$eol}");
}
}
$output("{$eol} </insert>{$eol}");
}
$output("{$eol} </initialization>{$eol}");
}
MetabaseFreeResult($this->database, $result);
}
$output("{$eol} </table>{$eol}");
if (isset($sequences[$table_name])) {
for ($sequence = 0; $sequence < count($sequences[$table_name]); $sequence++) {
if (!$this->DumpSequence($sequences[$table_name][$sequence], $output, $eol, $dump_definition)) {
return MetabaseError($this->database);
}
}
}
}
if (isset($sequences[""])) {
for ($sequence = 0; $sequence < count($sequences[""]); $sequence++) {
if (!$this->DumpSequence($sequences[""][$sequence], $output, $eol, $dump_definition)) {
return MetabaseError($this->database);
}
}
}
$output("{$eol}</database>{$eol}");
if (strcmp($previous_database_name, "")) {
MetabaseSetDatabase($this->database, $previous_database_name);
}
return $error;
}
/**
* 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::getselPeriod()
*
* This function returns a period.
*
* @param integer $periodid period id
* @return array period
* @access public
* @since 2004-12-13
* @author Christian Ehret <*****@*****.**>
*/
function getselPeriod($periodid)
{
global $gDatabase, $tbl_period, $request, $errorhandler;
$period = array();
$query = "SELECT period, active\n\t\t FROM {$tbl_period}\n\t\t\t\t\t\t WHERE pk_period_id = {$periodid} ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Article::getselPeriod()', $query);
} else {
if (MetabaseNumberOfRows($gDatabase, $result) > 0) {
$period = array("periodid" => $period, "period" => MetabaseFetchResult($gDatabase, $result, 0, 0), "active" => MetabaseFetchBooleanResult($gDatabase, $result, 0, 1));
}
}
return $period;
}
function numRows()
{
return MetabaseNumberOfRows($this->db, $this->result);
}
/**
* Guest::getEscort()
*
* get escorts
*
* @param number $guestid guestid
* @return array escorts
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
*/
function getEscort($guestid)
{
global $tbl_guest, $tbl_booking_detail_guest, $gDatabase, $errorhandler, $request;
$query = "( " . "SELECT DISTINCT b.fk_guest_id AS pk_guest_id, g.lastname AS lastname, g.firstname AS firstname " . "FROM zvs_booking_detail_guest bdg " . "LEFT JOIN zvs_booking_detail bd ON bdg.pk_fk_booking_detail_id = bd.pk_booking_detail_id " . "LEFT JOIN zvs_booking b ON bd.fk_booking_id = b.pk_booking_id " . "LEFT JOIN zvs_guest g ON b.fk_guest_id = g.pk_guest_id " . "WHERE bdg.pk_fk_guest_id = " . $guestid . " AND b.fk_guest_id != " . $guestid . " " . ") " . "UNION ( " . "SELECT DISTINCT bdg2.pk_fk_guest_id AS pk_guest_id, g.lastname AS lastname, g.firstname AS firstname " . "FROM zvs_booking_detail_guest bdg " . "LEFT JOIN zvs_booking_detail bd ON bdg.pk_fk_booking_detail_id = bd.pk_booking_detail_id " . "LEFT JOIN zvs_booking b ON bd.fk_booking_id = b.pk_booking_id " . "LEFT JOIN zvs_booking_detail bd2 ON b.pk_booking_id = bd2.fk_booking_id " . "LEFT JOIN zvs_booking_detail_guest bdg2 ON bd2.pk_booking_detail_id = bdg2.pk_fk_booking_detail_id " . "LEFT JOIN zvs_guest g ON bdg2.pk_fk_guest_id = g.pk_guest_id " . "WHERE bdg.pk_fk_guest_id = " . $guestid . " AND bdg2.pk_fk_guest_id != " . $guestid . " " . ") " . "ORDER BY lastname, firstname ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Guest::getEscort()', $query);
} else {
$ret = array();
$row = 0;
for ($row = 0; $row < MetabaseNumberOfRows($gDatabase, $result); ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$ret[$row] = array('guestid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'lastname' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'firstname' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'color' => $color);
}
return $ret;
}
}
$failed++;
echo "Test {$test}: the query result did not seem to have reached the end of result as expected starting row {$start_row} after fetching upto row {$row}{$eol}";
}
}
MetabaseFreeResult($database, $result);
}
}
}
if ($success) {
for ($rows = 2, $start_row = 0; $pass && $start_row < $total_rows; $start_row += $rows) {
MetabaseSetSelectedRowRange($database, $start_row, $rows);
if (!($result = MetabaseQuery($database, "SELECT user_name,user_password,subscribed,user_id,quota,weight,access_date,access_time,approved FROM users ORDER BY user_id"))) {
$success = 0;
break;
} else {
if (($result_rows = MetabaseNumberOfRows($database, $result)) > $rows) {
$pass = 0;
echo "FAILED!{$eol}";
$failed++;
echo "Test {$test}: expected a result of no more than {$rows} but the returned number of rows is {$result_rows}";
} else {
for ($row = 0; $row < $result_rows; $row++) {
if (MetabaseEndOfResult($database, $result)) {
$pass = 0;
echo "FAILED!{$eol}";
$failed++;
echo "Test {$test}: the query result seem to have reached the end of result at row {$row} that is before {$result_rows} as expected{$eol}";
break;
}
if (VerifyFetchedValues($database, $result, $row, $data[$row + $start_row], $value, $field)) {
$pass = 0;
/**
* 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);
}
}
}
/**
* Calendar::getExtensionTimes2()
*
* Get the dates the room is free before a date
*
* @param timestamp $date endday
* @param number $roomid Roomid
* @return array dates
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
*/
function getExtensionTimes2($date, $roomid)
{
global $tbl_booking_detail, $gDatabase, $errorhandler;
$query = sprintf("SELECT UNIX_TIMESTAMP(end_date), end_date \n\t\t FROM {$tbl_booking_detail} \n\t\t\t\t\t\t WHERE UNIX_TIMESTAMP(end_date) < %s \n\t\t\t\t\t\t AND fk_room_id = %s \n\t\t\t\t\t\t AND deleted_date IS NULL \n\t\t\t\t\t\t ORDER BY end_date DESC\n\t\t\t\t\t\t LIMIT 0,1 ", MetabaseGetTextFieldValue($gDatabase, $date), $roomid);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Calendar::$this->getExtensionTimes2()', $query);
} else {
$rows = MetabaseNumberOfRows($gDatabase, $result);
if ($rows > 0) {
$freenights = round(($date - MetabaseFetchResult($gDatabase, $result, 0, 0)) / 86400);
$starttmp = date("Y-m-d", MetabaseFetchResult($gDatabase, $result, 0, 0));
} else {
$freenights = 100;
$starttmp = date("Y-m-d", mktime(8, 0, 0, date("m", $date), date("d", $date) - $freenights, date("Y", $date)));
}
$days = array();
$nights = $freenights;
for ($i = 0; $i < $freenights && $i < 100; ++$i) {
$j = $i;
$days[$i]["count"] = $j;
$days[$i]["date"] = date("D, d. m. Y", strtotime("{$starttmp} + {$j} days"));
if ($nights > 1) {
$days[$i]["date"] .= " ({$nights} Nächte)";
} else {
$days[$i]["date"] .= " ({$nights} Nacht)";
}
$days[$i]["thedate"] = strtotime("{$starttmp} + {$j} days");
$nights -= 1;
}
return $days;
}
}
function getPageRecord($obj)
{
global $mdb;
$join_field = "";
$search_field = "";
$group_by_field = "";
$sort_field = "";
$field = $this->getSelectPageField($obj);
$group_by_field = $this->getGroupByField($obj);
$sort_field = $this->getSortField($obj);
$search_field = $this->getSearchField($obj);
$join_field = $this->getJoinField($obj);
if (isset($obj->configure["select"]["database_name"])) {
if ($obj->configure["select"]["database_name"] != "") {
$database_name = "`" . $obj->configure["select"]["database_name"] . "`.";
}
}
$prefix = $obj->config->default_prefix;
$prefix_where = "`" . $obj->configure["select"]["table_as_name"] . "`.`prefixid` = '" . $obj->config->default_prefix_id . "'";
if (isset($obj->configure["prefix"])) {
if ($obj->configure["prefix"] == 'N') {
$prefix = "";
$prefix_where = " 1=1 ";
}
}
if ($group_by_field != "") {
$row_field = " count( DISTINCT " . $group_by_field . ") as num";
} else {
$row_field = " count(*) as num";
}
$query = "SELECT " . $field . " FROM " . $database_name . "`" . $prefix . $obj->configure["select"]["table_name"] . "` AS `" . $obj->configure["select"]["table_as_name"] . "`";
$query_num = "SELECT " . $row_field . " FROM " . $database_name . "`" . $prefix . $obj->configure["select"]["table_name"] . "` AS `" . $obj->configure["select"]["table_as_name"] . "`";
if (!isset($obj->configure["select"]["max_page"])) {
$obj->configure["select"]["max_page"] = $obj->config->max_page;
}
if (!isset($obj->configure["select"]["max_range"])) {
$obj->configure["select"]["max_range"] = $obj->config->max_range;
}
if (is_array($join_field)) {
foreach ($join_field as $jfk => $jfv) {
$query .= "\n" . $jfv;
$query_num .= "\n" . $jfv;
}
}
$query .= "\nWHERE " . $prefix_where;
$query_num .= "\nWHERE " . $prefix_where;
if ($search_field != '') {
$query .= " " . $search_field;
$query_num .= " " . $search_field;
}
if ($group_by_field != '') {
$query .= "\nGROUP BY " . $group_by_field;
}
if ($sort_field != '') {
$query .= "\nORDER BY " . $sort_field;
}
if (isset($obj->configure["dev"])) {
if ($obj->configure["dev"] == 'Y') {
echo "<pre>";
echo $query_num;
echo "<br>";
echo "<br>";
echo $query;
exit;
}
}
$rows = $this->getPageRows($query_num, $obj);
$page = $this->recordPage($rows, $obj);
//echo "<pre>"; print_r($page); exit ;
$query_limit = " limit " . ($page["rec_start"] - 1) . "," . $obj->configure["select"]["max_page"];
$query .= $query_limit;
//echo "<pre>";print_r($this->getQueryRecord($query,$obj)); exit ;
$arr = "";
$this->queryAssign('getPageRecord', $query);
$db_res = $this->query($query, $obj);
$rows = @MetabaseNumberOfRows($mdb, $db_res);
@MetabaseGetColumnNames($mdb, $db_res, $db_field_name);
if ($rows) {
$sno = $page["rec_start"] - 1;
$gno = array();
$sgno = 0;
for ($row = 0; $row < $rows; $row++) {
if (is_array($db_field_name)) {
$sno++;
foreach ($db_field_name as $key => $value) {
$arr["record"][$row]["no"] = $row;
$arr["record"][$row]["sno"] = $sno;
$arr["record"][$row][$key] = @MetabaseFetchResult($mdb, $db_res, $row, $key);
}
}
}
}
$result["table"] = $arr;
$result["page"] = $page;
return $result;
}
/**
* DefaultData::´setfield()
*
* This function updates a given field and updates the session value
*
* @param number $id id of field
* @param string $value value
* @access public
* @since 2003-08-01
* @author Christian Ehret <*****@*****.**>
*/
function setfield($id, $value)
{
global $tbl_default, $tbl_hotel_default, $gDatabase, $request, $sess, $errorhandler;
$query = sprintf("SELECT fieldtype, default_name " . "FROM {$tbl_default} " . "WHERE pk_default_id = %s ", $id);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'defaultdata::setfield()', $query);
} else {
$fieldname = MetabaseFetchResult($gDatabase, $result, 0, 1);
if (MetabaseFetchResult($gDatabase, $result, 0, 0) == "int") {
$field = "integer_value";
$inputvalue = MetabaseGetTextFieldValue($gDatabase, $value);
} else {
if (MetabaseFetchResult($gDatabase, $result, 0, 0) == "boolean") {
$field = "boolean_value";
$inputvalue = MetabaseBooleanFieldValue($gDatabase, $value);
} else {
if (MetabaseFetchResult($gDatabase, $result, 0, 0) == "date") {
$field = "datetime_value";
$inputvalue = MetabaseGetTextFieldValue($gDatabase, $value);
} else {
$field = "string_value";
$inputvalue = MetabaseGetTextFieldValue($gDatabase, $value);
}
}
}
$query = sprintf("SELECT updated_date " . "FROM {$tbl_hotel_default} " . "WHERE pk_fk_hotel_id = %s " . "AND pk_fk_default_id = %s ", $request->GetVar('hotelid', 'session'), $id);
$result = MetabaseQuery($gDatabase, $query);
$rows = MetabaseNumberOfRows($gDatabase, $result);
if ($rows == 0) {
$query = sprintf("INSERT INTO {$tbl_hotel_default} " . "(pk_fk_hotel_id, pk_fk_default_id, {$field}, " . "fk_inserted_user_id, inserted_date) " . "VALUES (%s, %s, %s, %s, NOW()) ", $request->GetVar('hotelid', 'session'), $id, $inputvalue, $request->GetVar('uid', 'session'));
} else {
$query = sprintf("UPDATE {$tbl_hotel_default} " . "SET {$field} = %s, " . "fk_updated_user_id = %s, " . "updated_date = NOW(), " . "WHERE pk_fk_hotel_id = %s " . "AND pk_fk_default_id = %s ", $inputvalue, $request->GetVar('uid', 'session'), $request->GetVar('hotelid', 'session'), $id);
}
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'defaultdata::setfield()', $query);
} else {
$sess->SetVar($fieldname, $value);
}
}
}
/**
* Timetracker::getStatus()
*
* This function returns the status of a person.
*
* @param number $uid user id
* @return numer 0 if not logged in
* @access public
* @since 2004-10-06
* @author Christian Ehret <*****@*****.**>
*/
function getStatus($uid)
{
global $gDatabase, $tbl_timetracker, $errorhandler;
$query = sprintf("SELECT pk_timetracker_id \n\t\t FROM {$tbl_timetracker}\n\t\t\t\t\t\t WHERE fk_employee_id = %s \n\t\t\t\t\t\t AND ISNULL(end_date)\n\t\t\t\t\t\t AND ISNULL(deleted_date)", $uid);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Timetracker::getStatus()', $query);
} else {
if (MetabaseNumberOfRows($gDatabase, $result) == 0) {
return 0;
} else {
return MetabaseFetchResult($gDatabase, $result, 0, 0);
}
}
}
/**
* price::savedraft()
*
* save draft receipt
*
* @return $draftreceiptid draft receipt id
* @access public
* @since 2004-04-18
* @author Christian Ehret <*****@*****.**>
*/
function savedraft()
{
global $gDatabase, $tbl_draftreceipt, $tbl_draftreceipt_booking, $tbl_draftreceipt_item, $tbl_account, $errorhandler, $request;
$auto_commit = false;
$success = MetabaseAutoCommitTransactions($gDatabase, $auto_commit);
$receipt = array();
$doneitems = array();
$commissionids = array();
$commissionids = $request->GetVar('frm_commissionid', 'post');
$receipt = $this->recalculate($request->GetVar('frm_bookid', 'post'), $request->GetVar('length_short_stay', 'session'), $request->GetVar('frm_guestid', 'post'));
$receiptdate = explode(".", $receipt[data][receipt_date]);
$strreceiptdate = $receiptdate[2] . "-" . $receiptdate[1] . "-" . $receiptdate[0];
if ($request->GetVar('frm_bookids', 'post') !== $request->undefined) {
$bookids = $request->GetVar('frm_bookids', 'post');
} else {
$bookids = array($request->GetVar('frm_bookid', 'post'));
}
for ($i = 0; $i < count($receipt[data][start_date]); $i++) {
$start = explode(".", $receipt[data][start_date][$i]);
$strstart[$i] = $start[2] . "-" . $start[1] . "-" . $start[0];
$end = explode(".", $receipt[data][end_date][$i]);
$strend[$i] = $end[2] . "-" . $end[1] . "-" . $end[0];
}
if ($receipt[data][draftreceiptid] != -1 && $receipt[data][draftreceiptid] != "") {
$draftreceiptid = $receipt[data][draftreceiptid];
$query = sprintf("UPDATE {$tbl_draftreceipt} SET\n\t\t\t\t\t\t\t fk_guest_id = %s,\n\t\t\t\t\t\t\t address = %s,\n\t\t\t\t\t\t \t receipt_date = %s, \n\t\t\t\t\t\t\t sum_netto = %s, \n\t\t\t\t\t\t\t sum_brutto = %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_draftreceipt_id = %s", $receipt[data][guestid], MetabaseGetTextFieldValue($gDatabase, $receipt[data][address]), MetabaseGetTextFieldValue($gDatabase, $strreceiptdate), $receipt[data][price_netto_total], $receipt[data][price_total], $request->GetVar('uid', 'session'), $draftreceiptid);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
} else {
for ($i = 0; $i < count($bookids); $i++) {
$query = sprintf("UPDATE {$tbl_draftreceipt_booking}\n\t\t\t\t\t\t\t\t SET start_date = %s, end_date = %s\n\t\t\t\t\t\t\t\t WHERE pk_fk_draftreceipt_id = %s AND pk_fk_booking_id = %s", MetabaseGetTextFieldValue($gDatabase, $strstart[$i]), MetabaseGetTextFieldValue($gDatabase, $strend[$i]), $draftreceiptid, $bookids[$i]);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
}
}
// get all items which are in the system
$query = "SELECT pk_draftreceipt_item_id FROM {$tbl_draftreceipt_item} WHERE fk_draftreceipt_id = " . $receipt[data][draftreceiptid];
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
} else {
$olditems = array();
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$olditems[$row] = MetabaseFetchResult($gDatabase, $result, $row, 0);
}
for ($i = 0; $i < count($receipt[items]); ++$i) {
// is a new one
if ($receipt[items][$i][itemid] == -1 || !in_array($receipt[items][$i][itemid], $olditems)) {
$name = "zvs_pk_draftreceipt_item_id";
$sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$draftreceiptitemid);
$query = sprintf("INSERT INTO {$tbl_draftreceipt_item}\n\t\t\t\t\t\t\t (pk_draftreceipt_item_id, fk_draftreceipt_id, article, amount,\n\t\t\t\t\t\t\t price_netto, price_brutto, mwst, \n\t\t\t\t\t\t\t inserted_date, fk_inserted_user_id)\n\t\t\t\t\t\t\t VALUES (%s, %s, %s, %s, %s, %s, %s, NOW(), %s )", $draftreceiptitemid, $draftreceiptid, MetabaseGetTextFieldValue($gDatabase, $receipt[items][$i][article]), $receipt[items][$i][number], $receipt[items][$i][netto_single], $receipt[items][$i][brutto_single], $receipt[items][$i][mwst], $request->GetVar('uid', 'session'));
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
}
} else {
array_push($doneitems, $receipt[items][$i][itemid]);
$query = sprintf("UPDATE {$tbl_draftreceipt_item} SET\n\t\t\t\t\t\t\t\t\t article = %s, \n\t\t\t\t\t\t\t\t\t amount = %s,\n\t\t\t\t\t\t\t \t\t price_netto = %s, \n\t\t\t\t\t\t\t\t\t price_brutto = %s, \n\t\t\t\t\t\t\t\t\t mwst = %s, \n\t\t\t\t\t\t\t \t\t updated_date = NOW(), \n\t\t\t\t\t\t\t\t\t fk_updated_user_id = %s\n\t\t\t\t\t\t\t\t\t WHERE pk_draftreceipt_item_id = %s", MetabaseGetTextFieldValue($gDatabase, $receipt[items][$i][article]), $receipt[items][$i][number], $receipt[items][$i][netto_single], $receipt[items][$i][brutto_single], $receipt[items][$i][mwst], $request->GetVar('uid', 'session'), $receipt[items][$i][itemid]);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
}
}
}
// set deleted items delted
$deleteitems = array_values(array_diff($olditems, $doneitems));
for ($i = 0; $i < count($deleteitems); $i++) {
$query = sprintf("UPDATE {$tbl_draftreceipt_item} SET\n\t\t\t\t\t\t\t\t\t\tdeleted_date = NOW(),\n\t\t\t\t\t\t\t\t\t\tfk_deleted_user_id = %s\n\t\t\t\t\t\t\t\t\t\tWHERE pk_draftreceipt_item_id = %s ", $request->GetVar('uid', 'session'), $deleteitems[$i]);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
}
}
}
}
} else {
$name = "zvs_pk_draftreceipt_id";
$sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$draftreceiptid);
$query = sprintf("INSERT INTO {$tbl_draftreceipt}\n\t\t\t\t\t\t (pk_draftreceipt_id, fk_guest_id, address,\n\t\t\t\t\t\t receipt_date, sum_netto, sum_brutto, \n\t\t\t\t\t\t inserted_date, fk_inserted_user_id)\n\t\t\t\t\t\t VALUES (%s, %s, %s, %s, %s, %s, NOW(), %s )", $draftreceiptid, $receipt[data][guestid], MetabaseGetTextFieldValue($gDatabase, $receipt[data][address]), MetabaseGetTextFieldValue($gDatabase, $strreceiptdate), $receipt[data][price_netto_total], $receipt[data][price_total], $request->GetVar('uid', 'session'));
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
} else {
for ($i = 0; $i < count($bookids); $i++) {
$query = sprintf("INSERT INTO {$tbl_draftreceipt_booking}\n\t\t\t\t\t\t\t\t (pk_fk_draftreceipt_id, pk_fk_booking_id, start_date, end_date)\n\t\t\t\t\t\t\t\t VALUES (%s, %s, %s, %s)", $draftreceiptid, $bookids[$i], MetabaseGetTextFieldValue($gDatabase, $strstart[$i]), MetabaseGetTextFieldValue($gDatabase, $strend[$i]));
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
}
}
$sum = $receipt[data][price_total];
for ($k = 0; $k <= count($commissionids); ++$k) {
if ($commissionids[$k] != "") {
$query = sprintf("SELECT amount FROM {$tbl_account}\n\t\t\t\t\t\t\t\t\t WHERE pk_account_id = %s", $commissionids[$k]);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
} elseif (MetabaseNumberOfRows($gDatabase, $result) > 0) {
$sum += MetabaseFetchResult($gDatabase, $result, 0, 0) * -1;
}
}
}
for ($i = 0; $i < count($receipt[items]); ++$i) {
$name = "zvs_pk_draftreceipt_item_id";
$sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$draftreceiptitemid);
$query = sprintf("INSERT INTO {$tbl_draftreceipt_item}\n\t\t\t\t\t\t\t (pk_draftreceipt_item_id, fk_draftreceipt_id, article, amount,\n\t\t\t\t\t\t\t price_netto, price_brutto, mwst, \n\t\t\t\t\t\t\t inserted_date, fk_inserted_user_id)\n\t\t\t\t\t\t\t VALUES (%s, %s, %s, %s, %s, %s, %s, NOW(), %s )", $draftreceiptitemid, $draftreceiptid, MetabaseGetTextFieldValue($gDatabase, $receipt[items][$i][article]), $receipt[items][$i][number], $receipt[items][$i][netto_single], $receipt[items][$i][brutto_single], $receipt[items][$i][mwst], $request->GetVar('uid', 'session'));
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$success = MetabaseRollbackTransaction($gDatabase);
$errorhandler->display('SQL', 'Receipt::savedraft()', $query);
}
}
}
}
$success = MetabaseCommitTransaction($gDatabase);
// end transaction
$auto_commit = true;
$success = MetabaseAutoCommitTransactions($gDatabase, $auto_commit);
return $draftreceiptid;
}
/**
* 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;
}