function SearchCompleteValues(&$form, $text, &$found)
{
$error = '';
$found = array();
if (strlen($complete_expression = MetabaseBeginsWith($this->connection, $text)) == 0) {
return 'it was not possible to build the complete query expression: ' . MetabaseError($this->connection);
}
if (!strcmp($complete_values_query = str_replace('{BEGINSWITH}', $complete_expression, $this->complete_values_query), $this->complete_values_query)) {
return 'the complete values query does not contain the {BEGINSWITH} mark to insert the complete expression';
}
if (strlen($text) && $this->complete_values_limit) {
MetabaseSetSelectedRowRange($this->connection, 0, $this->complete_values_limit);
}
if ($r = MetabaseQuery($this->connection, $complete_values_query)) {
for ($l = 0; !MetabaseEndOfResult($this->connection, $r); $l++) {
if (!MetabaseFetchResultArray($this->connection, $r, $d, $l)) {
$error = 'Could not retrieve the complete values: ' . MetabaseError($this->connection);
break;
}
$found[$d[0]] = $this->FormatCompleteValue($d);
}
MetabaseFreeResult($this->connection, $r);
} else {
$error = 'Complete values query execution failed: ' . MetabaseError($this->connection);
}
return $error;
}
/**
* 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;
}
function GetGroups(&$g)
{
if (strlen($this->groups_query) == 0) {
return "it was not specified a valid query to retrieve all the options groups";
}
$g = array();
if (isset($this->default_option)) {
$g[] = $this->default_option;
}
$error = "";
if ($r = MetabaseQuery($this->connection, $this->groups_query)) {
for ($l = 0; !MetabaseEndOfResult($this->connection, $r); $l++) {
if (!MetabaseFetchResultArray($this->connection, $r, $d, $l)) {
$error = "Could not retrieve the options group: " . MetabaseError($this->connection);
break;
}
$g[] = $d[0];
}
if (count($g) == 0 && strlen($error) == 0) {
$error = "there are no group options";
}
MetabaseFreeResult($this->connection, $r);
} else {
$error = "Groups query execution failed: " . MetabaseError($this->connection);
}
if (strlen($error)) {
unset($g);
}
return $error;
}
/**
* article::get()
*
* get all articles
*
* @return array articles
* @access public
* @since 2004-03-07
* @author Christian Ehret <*****@*****.**>
*/
function get()
{
global $gDatabase, $tbl_article, $errorhandler, $request;
$article = array();
$query = "SELECT pk_article_id, article, price_netto, price_brutto, mwst\n\t\t\t\t FROM {$tbl_article} \n\t\t\t\t WHERE ISNULL(fk_deleted_user_id) \n\t\t\t\t ORDER BY article ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Article::get()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$article[$row] = array('articleid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'article' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'price_netto' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'price_brutto' => MetabaseFetchResult($gDatabase, $result, $row, 3), 'mwst' => MetabaseFetchResult($gDatabase, $result, $row, 4), 'color' => $color);
}
}
return $article;
}
/**
* Category::getall()
*
* This function returns all users.
*
* @return array users
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
*/
function getall()
{
global $gDatabase2, $tbl_user, $request, $errorhandler;
$user = array();
$query = sprintf("SELECT pk_user_id, lastname, firstname, login \n\t\t FROM {$tbl_user} \n\t\t\t\t\t\t WHERE fk_hotel_id = %s \n\t\t\t\t\t\t AND ISNULL(deleted_date)\n\t\t\t\t\t\t ORDER BY lastname ", $request->GetVar('hotelid', 'session'));
$result = MetabaseQuery($gDatabase2, $query);
if (!$result) {
$errorhandler->display('SQL', 'Guest::quickinsert()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase2, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$user[$row] = array('userid' => MetabaseFetchResult($gDatabase2, $result, $row, 0), 'lastname' => MetabaseFetchResult($gDatabase2, $result, $row, 1), 'firstname' => MetabaseFetchResult($gDatabase2, $result, $row, 2), 'login' => MetabaseFetchResult($gDatabase2, $result, $row, 3), 'color' => $color);
}
}
return $user;
}
/**
* Category::getall()
*
* This function returns all categories.
*
* @return array
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
*/
function getall()
{
global $gDatabase, $tbl_paycat, $errorhandler, $request;
$cat = array();
$query = "SELECT pk_paycat_id, paycat\n\t\t FROM {$tbl_paycat}\n\t\t\t\t WHERE ISNULL(fk_deleted_user_id) \n\t\t\t\t ORDER BY paycat";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'PayCategory::getall()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$cat[$row] = array('catid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'cat' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'color' => $color);
}
}
return $cat;
}
/**
* Checkin::get()
*
* This function returns a list of guests not checked in yet.
*
* @return array guests
* @access public
* @since 2003-09-02
* @author Christian Ehret <*****@*****.**>
*/
function get()
{
global $gDatabase, $tbl_booking, $tbl_guest, $tbl_booking_detail, $tbl_booking_detail_guest, $errorhandler;
$guests = array();
$query = "SELECT pk_booking_id, fk_guest_id, firstname, lastname, " . "DATE_FORMAT({$tbl_booking}.start_date, '%d.%m.%Y'), " . "DATE_FORMAT({$tbl_booking}.end_date, '%d.%m.%Y'), " . "pk_booking_detail_id " . sprintf("FROM {$tbl_booking}, {$tbl_guest}, {$tbl_booking_detail} " . "WHERE checked_in = %s " . "AND {$tbl_booking}.start_date <= NOW() " . "AND pk_guest_id = fk_guest_id " . "AND pk_booking_id = fk_booking_id " . "AND ISNULL({$tbl_booking}.deleted_date) " . "ORDER BY lastname", MetabaseGetBooleanFieldValue($gDatabase, false));
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Checkin::get()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$guests[$row] = array('bookingid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'guestid' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'firstname' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'lastname' => MetabaseFetchResult($gDatabase, $result, $row, 3), 'startdate' => MetabaseFetchResult($gDatabase, $result, $row, 4), 'enddate' => MetabaseFetchResult($gDatabase, $result, $row, 5), 'bookingdetailid' => MetabaseFetchResult($gDatabase, $result, $row, 6), 'color' => $color);
}
}
return $guests;
}
/**
* Barguestcat::getall()
*
* This function returns all barguestcategories.
*
* @return array articles
* @access public
* @since 2006-02-17
* @author Christian Ehret <*****@*****.**>
*/
function getall()
{
global $gDatabase2, $tbl_barguestcat, $request, $errorhandler;
$barguestcat = array();
$query = "SELECT pk_barguestcat_id, barguestcat\n\t\t FROM {$tbl_barguestcat}\n\t\t\t\t\t\t WHERE ISNULL(deleted_date)\n\t\t\t\t\t\t ORDER BY barguestcat ";
$result = MetabaseQuery($gDatabase2, $query);
if (!$result) {
$errorhandler->display('SQL', 'Barguestcat::getall()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase2, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$barguestcat[$row] = array('barguestcatid' => MetabaseFetchResult($gDatabase2, $result, $row, 0), 'barguestcat' => MetabaseFetchResult($gDatabase2, $result, $row, 1), 'color' => $color);
}
}
return $barguestcat;
}
/**
* bcat::get()
*
* get all categories
*
* @return array booking categories
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
*/
function get()
{
global $gDatabase, $tbl_bookingcat, $errorhandler, $request;
$bcat = array();
$query = "SELECT pk_bookingcat_id, bookingcat, color, description, days \n\t\t\t\t FROM {$tbl_bookingcat} \n\t\t\t\t WHERE ISNULL(fk_deleted_user_id)\n\t\t\t\t ORDER BY bookingcat ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'BookingCategory::getall()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$bcat[$row] = array('bcatid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'name' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'catcolor' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'description' => MetabaseFetchResult($gDatabase, $result, $row, 3), 'days' => MetabaseFetchResult($gDatabase, $result, $row, 4), 'color' => $color);
}
}
return $bcat;
}
/**
* room::get()
*
* get all rooms
*
* @return array room data
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
*/
function get()
{
global $gDatabase, $tbl_room, $tbl_roomcat, $errorhandler, $request;
$rooms = array();
$query = "SELECT pk_room_id, {$tbl_room}.room, capacity, " . "{$tbl_room}.description, fk_roomcat_id, roomcat " . "FROM {$tbl_room} " . "LEFT JOIN {$tbl_roomcat} ON(pk_roomcat_id = fk_roomcat_id) " . "WHERE ISNULL({$tbl_room}.fk_deleted_user_id) " . "ORDER BY {$tbl_room}.room";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'room::get()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$infotxt = "<strong>Bezeichnung:</strong> " . MetabaseFetchResult($gDatabase, $result, $row, 1) . "<br>" . "<strong>Personen:</strong> " . MetabaseFetchResult($gDatabase, $result, $row, 2) . "<br>" . "<strong>Kategorie:</strong> " . MetabaseFetchResult($gDatabase, $result, $row, 5) . "<br>";
if (MetabaseFetchResult($gDatabase, $result, $row, 3) !== "") {
$infotxt .= "<strong>Info:</strong> " . nl2br(MetabaseFetchResult($gDatabase, $result, $row, 3));
}
$rooms[$row] = array('roomid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'name' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'persons' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'info' => MetabaseFetchResult($gDatabase, $result, $row, 3), 'roomcatid' => MetabaseFetchResult($gDatabase, $result, $row, 4), 'catname' => MetabaseFetchResult($gDatabase, $result, $row, 5), 'color' => $color, 'infotxt' => $infotxt);
}
}
return $rooms;
}
/**
* 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;
}
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* This copyright notice MUST APPEAR in all copies of the script!
***************************************************************/
/**
* Get a list of all guests with firstname beginning with a proper string
*
* Guest
*
* @since 2006-02-24
* @author Christian Ehret <*****@*****.**>
*/
include_once "../includes/default.inc.php";
$auth->is_authenticated();
if ($_POST['autocomplete']) {
$entry = $_POST['autocomplete'];
echo '<ul>';
$query = sprintf("SELECT lastname, firstname, pk_guest_id FROM {$tbl_guest} \n\t\t WHERE firstname LIKE '%s%%' AND ISNULL(deleted_date)\n\t\t ORDER BY lastname LIMIT 25", $entry);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
} else {
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
echo "<li id=\"" . MetabaseFetchResult($gDatabase, $result, $row, 0) . "\"><span class=\"informal\">" . MetabaseFetchResult($gDatabase, $result, $row, 0) . ", </span>" . MetabaseFetchResult($gDatabase, $result, $row, 1) . "</li>";
}
}
echo '</ul>';
}
?>
/**
* DefaultData::geteditablefields()
*
* This function returns an array with editable fields. The value is either
* the hotel spezific one or - if none is set - the system default value
*
* @return array returns an array of data fields
* @access public
* @since 2003-08-01
* @author Christian Ehret <*****@*****.**>
*/
function geteditablefields()
{
global $gDatabase, $request, $tbl_default, $tbl_hotel_default, $sess, $errorhandler;
$query = "SELECT " . "def.pk_default_id, default_name, fieldtype, " . "IF(ISNULL(hotel.string_value),def.string_value,hotel.string_value) AS string_value, " . "IF(ISNULL(hotel.integer_value),def.integer_value,hotel.integer_value) AS integer_value, " . "IF(ISNULL(hotel.datetime_value),def.datetime_value,hotel.datetime_value) AS datetime_value, " . "IF(ISNULL(hotel.boolean_value),def.boolean_value,hotel.boolean_value) AS boolean_value, " . "description " . "FROM {$tbl_default} AS def " . "LEFT JOIN {$tbl_hotel_default} AS hotel " . "ON (hotel.pk_fk_default_id = def.pk_default_id " . "AND hotel.pk_fk_hotel_id = " . $request->GetVar('hotelid', 'session') . ") " . "WHERE def.editable = 'Y' " . "ORDER BY default_name";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'defaultdata::geteditablefields()', $query);
} else {
$row = 0;
$fields = array();
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
if (MetabaseFetchResult($gDatabase, $result, $row, 3) != null) {
$value = MetabaseFetchResult($gDatabase, $result, $row, 3);
} elseif (MetabaseFetchResult($gDatabase, $result, $row, 4) != null) {
$value = MetabaseFetchResult($gDatabase, $result, $row, 4);
} elseif (MetabaseFetchResult($gDatabase, $result, $row, 5) != null) {
$value = MetabaseFetchResult($gDatabase, $result, $row, 5);
} elseif (MetabaseFetchResult($gDatabase, $result, $row, 6) != null) {
$value = MetabaseFetchResult($gDatabase, $result, $row, 6);
} else {
$value = '';
}
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$fields[$row] = array('defaultid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'title' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'fieldtype' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'description' => MetabaseFetchResult($gDatabase, $result, $row, 7), 'value' => $value, 'color' => $color);
}
return $fields;
}
}
/**
* Guest::asearch()
*
* search adresses for a guest
*
* @param string $firstname firstname
* @param string $lastname lastname
* @return array guest data
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
*/
function asearch($firstname, $lastname)
{
global $gDatabase, $tbl_guest, $tbl_address, $tbl_guest_address, $tbl_country, $tbl_booking, $tbl_booking_detail_guest, $tbl_booking_detail, $errorhandler, $request;
$guest = array();
$query = "SELECT g.pk_guest_id, \n\t\t\t\t IF ( g.firstname = '' OR g.firstname IS NULL , '-', g.firstname )firstname, \n\t\t\t\t g.lastname, \n\t\t\t\t IF ( g.company = '' OR g.company IS NULL , '-', g.company )company \n\t\t\t\t FROM {$tbl_guest} g ";
if ($firstname != "" or $lastname != "") {
$query .= "WHERE ";
}
if ($firstname != "") {
$query .= "firstname LIKE '%" . $firstname . "%' ";
}
if ($firstname != "" and $lastname != "") {
$query .= "AND ";
}
if ($lastname != "") {
$query .= "lastname LIKE '%" . $lastname . "%' ";
}
$query .= "GROUP BY g.pk_guest_id ";
$query .= "ORDER BY lastname, firstname ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Guest::asearch()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
unset($addresses);
$query = "SELECT pk_fk_address_id, address_type FROM {$tbl_guest_address} WHERE pk_fk_guest_id = " . MetabaseFetchResult($gDatabase, $result, $row, 0);
$result2 = MetabaseQuery($gDatabase, $query);
if (!$result2) {
$errorhandler->display('SQL', 'Guest::asearch()', $query);
}
for ($row2 = 0; ($eor = MetabaseEndOfResult($gDatabase, $result2)) == 0; ++$row2) {
$addresses[$row2] = $this->getAddress(MetabaseFetchResult($gDatabase, $result2, $row2, 0), MetabaseFetchResult($gDatabase, $result2, $row2, 1));
}
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$guest[$row] = array('guestid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'firstname' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'lastname' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'company' => MetabaseFetchResult($gDatabase, $result, $row, 3), 'addresses' => $addresses, 'color' => $color);
}
}
return $guest;
}
/**
* season::getOneYear()
*
* get all season data of one year
*
* @param number $year year
* @return array season data
* @access public
* @since 2003-12-31
* @author Christian Ehret <*****@*****.**>
*/
function getOneYear($year)
{
global $gDatabase, $tbl_season, $errorhandler, $request;
$season = array();
$query = "SELECT pk_season_id, name \n\t\t FROM {$tbl_season} \n\t\t\t\t WHERE ISNULL(fk_deleted_user_id) \n\t\t\t\t AND DATE_FORMAT( start_date, '%Y' ) = {$year}\n\t\t\t\t ORDER BY start_date ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Season::getOneYear()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$season[$row] = array('seasonid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'name' => MetabaseFetchResult($gDatabase, $result, $row, 1));
}
}
return $season;
}
/**
* Calendar::getMonth()
*
* This function returns a month with bookings
*
* @param number $year year
* @param number $month month
* @param number $roomid roomid
* @return array month with bookings
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
*/
function getMonth($year, $month, $roomid)
{
global $time;
global $tbl_booking_detail, $tbl_booking, $tbl_bookingcat, $tbl_guest, $gDatabase, $request, $errorhandler;
$first_of_month = mktime(8, 0, 0, $month, 1, $year);
$maxdays = date('t', $first_of_month);
$date_info = getdate($first_of_month);
$month = $date_info['mon'];
$year = $date_info['year'];
if ($roomid != 0) {
$booked = array();
$query = "SELECT pk_booking_detail_id, fk_bookingcat_id, \n\t\t\t\t\t {$tbl_booking}.fk_guest_id, UNIX_TIMESTAMP({$tbl_booking_detail}.start_date), \n\t\t\t\t\t UNIX_TIMESTAMP({$tbl_booking_detail}.end_date), color, \n\t\t\t\t\t lastname, firstname, {$tbl_bookingcat}.bookingcat, {$tbl_booking_detail}.persons, \n\t\t\t\t\t {$tbl_booking_detail}.children, booking_type, \n\t\t\t\t\t DATE_FORMAT(reservation_until, '%d.%m.%Y'), \n\t\t\t\t\t {$tbl_booking}.additional_info, UNIX_TIMESTAMP({$tbl_booking}.start_date), \n\t\t\t\t\t UNIX_TIMESTAMP({$tbl_booking}.end_date), {$tbl_booking}.pk_booking_id,\n\t\t\t\t\t {$tbl_booking_detail}.children2, {$tbl_booking_detail}.children3, {$tbl_booking_detail}.children0 \n\t\t\t\t\t FROM {$tbl_booking_detail}, {$tbl_booking}, {$tbl_bookingcat}, {$tbl_guest} \n\t\t\t\t\t WHERE {$tbl_booking_detail}.fk_booking_id = {$tbl_booking}.pk_booking_id \n\t\t\t\t\t AND {$tbl_booking}.fk_bookingcat_id = {$tbl_bookingcat}.pk_bookingcat_id \n\t\t\t\t\t AND {$tbl_guest}.pk_guest_id = {$tbl_booking}.fk_guest_id \n\t\t\t\t\t AND fk_room_id = " . $roomid . " \n\t\t\t\t\t AND {$tbl_booking_detail}.deleted_date IS NULL \n\t\t\t\t\t ORDER BY {$tbl_booking_detail}.start_date";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Calendar::getMonth()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$bookingtype = "";
$bookingtypecolor = "";
switch (MetabaseFetchResult($gDatabase, $result, $row, 11)) {
case 'R':
$bookingtype = "Reservierung";
$bookingtypecolor = $request->GetVar('colorR', 'session');
break;
case 'B':
$bookingtype = "Buchung";
$bookingtypecolor = $request->GetVar('colorB', 'session');
break;
case 'P':
$bookingtype = "Abgerechnet";
$bookingtypecolor = $request->GetVar('colorP', 'session');
break;
}
$booked[$row] = array('bookingdetailid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'catid' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'gastid' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'start' => MetabaseFetchResult($gDatabase, $result, $row, 3), 'end' => MetabaseFetchResult($gDatabase, $result, $row, 4), 'color' => MetabaseFetchResult($gDatabase, $result, $row, 5), 'lastname' => MetabaseFetchResult($gDatabase, $result, $row, 6), 'firstname' => MetabaseFetchResult($gDatabase, $result, $row, 7), 'cat' => MetabaseFetchResult($gDatabase, $result, $row, 8), 'persons' => MetabaseFetchResult($gDatabase, $result, $row, 9), 'children0' => MetabaseFetchResult($gDatabase, $result, $row, 19), 'children' => MetabaseFetchResult($gDatabase, $result, $row, 10), 'children2' => MetabaseFetchResult($gDatabase, $result, $row, 17), 'children3' => MetabaseFetchResult($gDatabase, $result, $row, 18), 'bookingtype' => MetabaseFetchResult($gDatabase, $result, $row, 11), 'bookingtypename' => $bookingtype, 'bookingtypecolor' => $bookingtypecolor, 'reservationuntil' => MetabaseFetchResult($gDatabase, $result, $row, 12), 'description' => MetabaseFetchResult($gDatabase, $result, $row, 13), 'totalstart' => MetabaseFetchResult($gDatabase, $result, $row, 14), 'totalend' => MetabaseFetchResult($gDatabase, $result, $row, 15), 'bookid' => MetabaseFetchResult($gDatabase, $result, $row, 16));
}
}
}
$weekday = $date_info['wday'];
$day = 1;
$monthArray = array();
while ($day <= $maxdays) {
$firstday = "";
$lastday = "";
$lastdaydata = array();
$linkDate = mktime(8, 0, 0, $month, $day, $year);
$bookingdetailid = "";
$bookingtypecolor = "";
$bookid = "";
$color = "";
$infotxt = "";
if ($roomid != 0) {
$firstday = "";
$lastday = "";
foreach ($booked as $booking) {
$endarr = getdate($booking['end']);
$startarr = getdate($booking['start']);
$endstr = mktime(8, 0, 0, $endarr['mon'], $endarr['mday'], $endarr['year']);
$startstr = mktime(8, 0, 0, $startarr['mon'], $startarr['mday'], $startarr['year']);
if ($linkDate <= $endstr and $linkDate >= $startstr) {
$bookid = $booking['bookid'];
$bookingdetailid = $booking['bookingdetailid'];
$color = $booking['color'];
$bookingtypecolor = $booking['bookingtypecolor'];
$infotxt = "<b>Name:</b> " . $booking['firstname'] . " " . $booking['lastname'] . "<br>" . "<b>Anreisetag:</b> " . date("d. m. Y", $booking['totalstart']) . "<br>" . "<b>Abreisetag:</b> " . date("d. m. Y", $booking['totalend']) . "<br>" . "<b>Kategorie:</b> " . $booking['cat'] . "<br>" . "<b>Belegungsart:</b> " . $booking['bookingtypename'];
if ($booking['bookingtype'] == 'R') {
$infotxt .= " bis " . $booking['reservationuntil'];
}
$infotxt .= "<br><b>Personen:</b> " . $booking['persons'];
if ($booking['persons'] != 1) {
$infotxt .= " Erwachsene";
} else {
$infotxt .= " Erwachsener";
}
$childrentotal = $booking['children0'] + $booking['children'] + $booking['children2'] + $booking['children3'];
if ($childrentotal != 0) {
$infotxt .= ", " . $childrentotal;
if ($childrentotal != 1) {
$infotxt .= " Kinder";
} else {
$infotxt .= " Kind";
}
$infotxt .= " (" . $booking['children0'] . "/" . $booking['children'] . "/" . $booking['children2'] . "/" . $booking['children3'] . ")";
}
if ($booking['description'] != "") {
$infotxt .= "<br><b>Bemerkung:</b> " . $booking['description'];
}
if ($startstr == $linkDate) {
$firstday = "true";
// $lastdaydata = array();
}
if ($endstr == $linkDate) {
$lastday = "true";
$lastdaydata = array('date' => $day, 'linkDate' => $linkDate, 'weekday' => $this->returnDayName($weekday), 'bookid' => $bookid, 'bookingdetailid' => $bookingdetailid, 'color' => $color, 'infotxt' => $infotxt, 'bookingtypecolor' => $bookingtypecolor);
}
}
}
}
$monthArray[$day - 1] = array('date' => $day, 'linkDate' => $linkDate, 'weekday' => $this->returnDayName($weekday), 'bookid' => $bookid, 'bookingdetailid' => $bookingdetailid, 'color' => $color, 'infotxt' => $infotxt, 'firstday' => $firstday, 'lastday' => $lastday, 'lastdaydata' => $lastdaydata, 'bookingtypecolor' => $bookingtypecolor);
$day++;
$weekday++;
}
return $monthArray;
}
$test = "lobnulls";
echo "Testing lob nulls... ";
flush();
$pass = 1;
if (!MetabaseQuery($database, "DELETE FROM files")) {
$success = 0;
} else {
if ($prepared_query = MetabasePrepareQuery($database, "INSERT INTO files (document,picture) VALUES (?,?)")) {
MetabaseQuerySetNULL($database, $prepared_query, 1, "clob");
MetabaseQuerySetNULL($database, $prepared_query, 2, "blob");
$success = MetabaseExecuteQuery($database, $prepared_query);
MetabaseFreePreparedQuery($database, $prepared_query);
if (!$success || !($result = MetabaseQuery($database, "SELECT document,picture FROM files"))) {
$success = 0;
} else {
if (MetabaseEndOfResult($database, $result)) {
$pass = 0;
echo "FAILED!{$eol}";
$failed++;
echo "Test {$test}: the query result seem to have reached the end of result too soon.{$eol}";
} else {
if (!MetabaseResultIsNull($database, $result, 0, $field = "document") || !MetabaseResultIsNull($database, $result, 0, $field = "picture")) {
$pass = 0;
echo "FAILED!{$eol}";
$failed++;
echo "Test {$test}: a query result large object column is not NULL unlike what was expected{$eol}";
}
}
MetabaseFreeResult($database, $result);
}
} else {
/**
* Account::getlist()
*
* This function returns all receipts.
*
* @param date $thestart start date
* @param date $theend end date
* @param int $paycat paycat id (-1 for all)
* @return array turnovers
* @access public
* @since 2004-04-07
* @author Christian Ehret <*****@*****.**>
*/
function getlist($thestart, $theend, $paycat)
{
global $gDatabase, $tbl_account, $tbl_receipt, $tbl_paycat, $tbl_guest, $request, $errorhandler;
$turnovers = array();
$query = "SELECT a.amount, DATE_FORMAT(a.date_payment, '%d.%m.%Y'), a.description, \n\t\t\t\t p.paycat, DATE_FORMAT(r.receipt_date, '%d.%m.%Y'), r.receipt_reference_id, g.firstname, g.lastname\n\t\t FROM {$tbl_account} a\n\t\t\t\t\t\t LEFT JOIN {$tbl_paycat} p ON a.fk_paycat_id = p.pk_paycat_id\n\t\t\t\t\t\t LEFT JOIN {$tbl_receipt} r ON a.fk_receipt_id = r.pk_receipt_id\n \t\t\t\t\t\t LEFT JOIN {$tbl_guest} g ON a.fk_guest_id = g.pk_guest_id\t\t\t\t\t\t\t\t \n\t\t\t\t\t\t WHERE date_payment >= '{$thestart}'\n\t\t\t\t\t\t AND date_payment <= '{$theend}' ";
if ($paycat != -1) {
$query .= "AND a.fk_paycat_id = {$paycat} ";
}
$query .= "ORDER BY date_payment DESC";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Account::getlist()', $query);
} else {
$row = 0;
$sum = 0.0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$turnovers[$row] = array('amount' => number_format(MetabaseFetchResult($gDatabase, $result, $row, 0), 2, '.', ''), 'date_payment' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'description' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'paycat' => MetabaseFetchResult($gDatabase, $result, $row, 3), 'receipt_date' => MetabaseFetchResult($gDatabase, $result, $row, 4), 'receipt_reference_id' => MetabaseFetchResult($gDatabase, $result, $row, 5), 'firstname' => MetabaseFetchResult($gDatabase, $result, $row, 6), 'lastname' => MetabaseFetchResult($gDatabase, $result, $row, 7), 'color' => $color);
$sum += MetabaseFetchResult($gDatabase, $result, $row, 0);
}
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$turnovers[$row] = array('amount' => number_format($sum, 2, '.', ''), 'date_payment' => '', 'description' => 'Gesamt:', 'paycat' => '', 'receipt_date' => '', 'receipt_reference_id' => '', 'firstname' => '', 'lastname' => '', 'color' => $color);
}
return $turnovers;
}
/**
* Archive::get()
*
* This function returns the archived sql and depending on the parameters deletes data.
*
* @param timestamp $thedate date till data should be archived
* @param boolean $delete deletes the data if true
* @return string $output
* @access public
* @since 2004-07-09
* @author Christian Ehret <*****@*****.**>
*/
function get($thedate, $delete)
{
global $gDatabase, $tbl_bought, $tbl_bararticle, $tbl_barguest, $request, $errorhandler;
$output = "/*Archiv bis {$thedate}*/\n";
$output1 = "/*{$tbl_bought}*/\n";
$output2 = "/*{$tbl_bararticle}*/\n";
$output3 = "/*{$tbl_barguest}*/\n";
list($day, $month, $year) = split('[.]', $thedate);
$thedate = "{$year}-{$month}-{$day} 23:59:59";
$query = "SELECT pk_bought_id, fk_barguest_id, fk_bararticle_id, timestamp, num,\n\t\t paid, inserted_date, fk_inserted_user_id, updated_date, fk_updated_user_id\n\t\t FROM {$tbl_bought}\n\t\t WHERE timestamp <= '{$thedate}'";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Archive::get()', $query);
} else {
$guestids = array();
$articleids = array();
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
if (!in_array(MetabaseFetchResult($gDatabase, $result, $row, 1), $guestids)) {
array_push($guestids, MetabaseFetchResult($gDatabase, $result, $row, 1));
}
if (!in_array(MetabaseFetchResult($gDatabase, $result, $row, 2), $articleids)) {
array_push($articleids, MetabaseFetchResult($gDatabase, $result, $row, 2));
}
$output1 .= "\n";
$output1 .= "DELETE FROM {$tbl_bought} WHERE pk_bought_id = " . MetabaseFetchResult($gDatabase, $result, $row, 0) . ";";
$output1 .= "\n";
$output1 .= sprintf("INSERT INTO {$tbl_bought} (pk_bought_id, fk_barguest_id, fk_bararticle_id, timestamp, num, paid, inserted_date, fk_inserted_user_id, updated_date, fk_updated_user_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);", $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 0), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 1), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 2), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 3), 'yesifnotnull'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 4), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 5), 'yes'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 6), 'yesifnotnull'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 7), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 8), 'yesifnotnull'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 9), 'no'));
if ($delete) {
$query = "DELETE FROM {$tbl_bought} WHERE pk_bought_id = " . MetabaseFetchResult($gDatabase, $result, $row, 0);
$result2 = MetabaseQuery($gDatabase, $query);
if (!$result2) {
$errorhandler->display('SQL', 'Archive::get()', $query);
}
}
}
for ($i = 0; $i < count($articleids); $i++) {
$query = "SELECT pk_bararticle_id, description, price, hotkey, inserted_date,\n\t\t\t\t fk_inserted_user_id, updated_date, fk_updated_user_id, deleted_date, fk_deleted_user_id\n\t\t\t\t\t\t FROM {$tbl_bararticle} WHERE pk_bararticle_id = {$articleids[$i]}";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Archive::get()', $query);
} else {
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$output2 .= "\n";
$output2 .= "DELETE FROM {$tbl_bararticle} WHERE pk_bararticle_id = " . MetabaseFetchResult($gDatabase, $result, $row, 0) . ";";
$output2 .= "\n";
$output2 .= sprintf("INSERT INTO {$tbl_bararticle} (pk_bararticle_id, description, price, hotkey, inserted_date, fk_inserted_user_id, updated_date, fk_updated_user_id, deleted_date, fk_deleted_user_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s); ", $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 0), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 1), 'yes'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 2), 'yes'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 3), 'yes'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 4), 'yesifnotnull'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 5), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 6), 'yesifnotnull'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 7), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 8), 'yesifnotnull'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 9), 'no'));
}
}
}
for ($i = 0; $i < count($guestids); $i++) {
$query = "SELECT pk_barguest_id, firstname, lastname, inserted_date,\n\t\t\t\t fk_inserted_user_id, updated_date, fk_updated_user_id, deleted_date, fk_deleted_user_id\n\t\t\t\t\t\t FROM {$tbl_barguest} WHERE pk_barguest_id = {$guestids[$i]}";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Archive::get()', $query);
} else {
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$output3 .= "\n";
$output3 .= "DELETE FROM {$tbl_barguest} WHERE pk_barguest_id = " . MetabaseFetchResult($gDatabase, $result, $row, 0) . ";";
$output3 .= "\n";
$output3 .= sprintf("INSERT INTO {$tbl_barguest} (pk_barguest_id, firstname, lastname, inserted_date, fk_inserted_user_id, updated_date, fk_updated_user_id, deleted_date, fk_deleted_user_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s); ", $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 0), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 1), 'yes'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 2), 'yes'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 3), 'yesifnotnull'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 4), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 5), 'yesifnotnull'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 6), 'no'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 7), 'yesifnotnull'), $this->nullifempty(MetabaseFetchResult($gDatabase, $result, $row, 8), 'no'));
}
}
}
$output .= $output2 . "\n\n" . $output3 . "\n\n" . $output1;
}
return $output;
}
/**
* Receipt::getMwst()
*
* Get the Mwst
*
* @return array Mwst
* @access public
* @since 2004-06-10
* @author Christian Ehret <*****@*****.**>
*/
function getMwst()
{
global $tbl_receipt_item, $gDatabase, $errorhandler;
$query = "SELECT DISTINCT (mwst)FROM {$tbl_receipt_item} ORDER BY mwst;";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Receipt::getMwst()', $query);
}
$ret = array();
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$ret[$row] = MetabaseFetchResult($gDatabase, $result, $row, 0);
}
return $ret;
}
/**
* Kassa::getTimeline()
*
* This function returns all bought articles for one guest.
*
* @param number $guestid guest id
* @param date $start start date
* @param date $end end date
* @param string $order order desc or asc
* @param array $cats categories
* @return array articles
* @access public
* @since 2004-01-06
* @author Christian Ehret <*****@*****.**>
*/
function getTimeline($guestid, $start, $end, $order, $cats = array())
{
global $gDatabase2, $tbl_bararticle, $tbl_user, $tbl_bought, $tbl_barguest, $request, $errorhandler, $articlerows;
$article = array();
if (count($cats) > 0) {
$catstr = "AND ba.fk_bararticlecat_id IN (";
for ($i = 0; $i < count($cats); $i++) {
if ($i > 0) {
$catstr .= ", ";
}
$catstr .= $cats[$i];
}
$catstr .= ")";
} else {
return array();
}
list($day, $month, $year) = split('.', $start);
$query = "SELECT ba.pk_bararticle_id, ba.description, ba.price, DATE_FORMAT( b.timestamp, '%d.%m.%Y, %H:%i' ), num, pk_bought_id, paid,\n\t\t\t\t DATE_FORMAT( b.updated_date, '%d.%m.%Y, %H:%i Uhr' ), u2.firstname, u2.lastname,\n\t\t\t\t DATE_FORMAT( b.inserted_date, '%d.%m.%Y, %H:%i Uhr' ), u1.firstname, u1.lastname \n FROM {$tbl_bought} b\n\t\t\t\t \t\t LEFT JOIN {$tbl_barguest} bg ON bg.pk_barguest_id = b.fk_barguest_id\n\t\t\t\t \t LEFT JOIN {$tbl_bararticle} ba ON b.fk_bararticle_id = ba.pk_bararticle_id\n\t\t\t\t\t\t LEFT JOIN {$tbl_user} u1 ON b.fk_inserted_user_id = u1.pk_user_id\n\t\t\t\t\t\t LEFT JOIN {$tbl_user} u2 ON b.fk_updated_user_id = u2.pk_user_id\n\t\t\t\t\t\t WHERE bg.pk_barguest_id = {$guestid} \n\t\t\t\t\t\t {$catstr} ";
if ($start !== "") {
list($day, $month, $year) = split('[.]', $start);
$query .= "AND UNIX_TIMESTAMP(b.timestamp) >= " . mktime(0, 0, 0, $month, $day, $year) . " ";
}
if ($end !== "") {
list($day, $month, $year) = split('[.]', $end);
$query .= "AND UNIX_TIMESTAMP(b.timestamp) <= " . mktime(0, 0, 0, $month, $day + 1, $year) . " ";
}
$query .= "ORDER BY b.timestamp " . $order;
$result = MetabaseQuery($gDatabase2, $query);
if (!$result) {
$errorhandler->display('SQL', 'Kassa::getTimeline()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase2, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$total = MetabaseFetchResult($gDatabase2, $result, $row, 2) * MetabaseFetchResult($gDatabase2, $result, $row, 4);
// print "paid: ".MetabaseFetchBooleanResult($gDatabase2, $result, $row, 6);
if (MetabaseFetchBooleanResult($gDatabase2, $result, $row, 6)) {
$sum2 += $total;
$sum += $total;
$paid = $total;
} else {
$sum += $total;
$paid = "0.00";
}
$article[$row] = array('articleid' => MetabaseFetchResult($gDatabase2, $result, $row, 0), 'description' => MetabaseFetchResult($gDatabase2, $result, $row, 1), 'price' => MetabaseFetchResult($gDatabase2, $result, $row, 2), 'timestamp' => MetabaseFetchResult($gDatabase2, $result, $row, 3), 'num' => MetabaseFetchResult($gDatabase2, $result, $row, 4), 'boughtid' => MetabaseFetchResult($gDatabase2, $result, $row, 5), 'paid' => MetabaseFetchBooleanResult($gDatabase2, $result, $row, 6), 'updated' => MetabaseFetchResult($gDatabase2, $result, $row, 7), 'updateduser' => MetabaseFetchResult($gDatabase2, $result, $row, 8) . " " . MetabaseFetchResult($gDatabase2, $result, $row, 9), 'inserted' => MetabaseFetchResult($gDatabase2, $result, $row, 10), 'inserteduser' => MetabaseFetchResult($gDatabase2, $result, $row, 11) . " " . MetabaseFetchResult($gDatabase2, $result, $row, 12), 'total1' => number_format($total, 2, '.', ''), 'total2' => number_format($paid, 2, '.', ''), 'color' => $color);
}
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$article[$row] = array('articleid' => 0, 'description' => "Summe:", 'price' => "", 'timestamp' => "", 'num' => "", 'boughtid' => "", 'paid' => "", 'updated' => "", 'total1' => number_format($sum, 2, '.', ''), 'total2' => number_format($sum2, 2, '.', ''), 'color' => $color);
$sum -= $sum2;
$article[$row + 1] = array('articleid' => 0, 'description' => "zu zahlen:", 'price' => "", 'timestamp' => "", 'num' => "", 'boughtid' => "", 'paid' => "", 'updated' => "", 'total1' => number_format($sum, 2, '.', ''), 'total2' => "", 'color' => $color);
}
return $article;
}
/**
* RoomCategory::get()
*
* This function returns all categories of a price_type.
*
* @param char $price_type price type
* @return array categories
* @access public
* @since 2004-03-20
* @author Christian Ehret <*****@*****.**>
*/
function get($price_type)
{
global $gDatabase, $tbl_roomcat, $errorhandler, $request;
$cat = array();
$query = sprintf("SELECT pk_roomcat_id, roomcat, price_type \n\t\t\t\t FROM {$tbl_roomcat} \n\t\t\t\t WHERE ISNULL(fk_deleted_user_id) \n\t\t\t\t AND price_type = %s\n\t\t\t\t ORDER BY roomcat", MetabaseGetTextFieldValue($gDatabase, $price_type));
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'RoomCategory::get()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
$articles = "";
if ($row % 2 != 0) {
$color = 1;
}
$cat[$row] = array('catid' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'name' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'price_type' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'color' => $color);
}
}
return $cat;
}
/**
* 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;
}
}
/**
* price::getall()
*
* get all prices
*
* @param char $price_type price type 'N' normal 'A' advanced
* @access public
* @return array prices
* @since 2004-01-01
* @author Christian Ehret <*****@*****.**>
*/
function getall($price_type)
{
global $gDatabase, $request, $tbl_price, $tbl_price2, $errorhandler;
$prices = array();
if ($price_type == 'N') {
$query = "SELECT fk_roomcat_id, fk_bookingcat_id, fk_season_id,\n\t\t\t\t price_person, price_children, price_absolute, price_type,\n\t\t\t\t price_person_short, price_children_short, price_absolute_short,\n\t\t\t\t price_children2, price_children2_short, price_children3, price_children3_short\n\t\t\t\t FROM {$tbl_price} ";
$result = MetabaseQuery($gDatabase, $query);
if ($result) {
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['person'] = MetabaseFetchResult($gDatabase, $result, $row, 3);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['children'] = MetabaseFetchResult($gDatabase, $result, $row, 4);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['absolute'] = MetabaseFetchResult($gDatabase, $result, $row, 5);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['type'] = MetabaseFetchResult($gDatabase, $result, $row, 6);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['person_short'] = MetabaseFetchResult($gDatabase, $result, $row, 7);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['children_short'] = MetabaseFetchResult($gDatabase, $result, $row, 8);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['absolute_short'] = MetabaseFetchResult($gDatabase, $result, $row, 9);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['children2'] = MetabaseFetchResult($gDatabase, $result, $row, 10);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['children2_short'] = MetabaseFetchResult($gDatabase, $result, $row, 11);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['children3'] = MetabaseFetchResult($gDatabase, $result, $row, 12);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['children3_short'] = MetabaseFetchResult($gDatabase, $result, $row, 13);
}
}
if ($request->GetVar('debug', 'get') == 'true') {
print $query;
print '<br><br>';
print '<pre>';
print_r($result);
print '</pre><br><br>';
print '<pre>';
print_r($prices);
print '</pre>';
}
} else {
$query = "SELECT fk_roomcat_id, fk_bookingcat_id, fk_season_id,\n\t\t\t\t price, price_short, persons_included, price_additional,\n\t\t\t\t price_short_additional\n\t\t\t\t FROM {$tbl_price2} ";
$result = MetabaseQuery($gDatabase, $query);
if ($result) {
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['price'] = MetabaseFetchResult($gDatabase, $result, $row, 3);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['price_short'] = MetabaseFetchResult($gDatabase, $result, $row, 4);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['person'] = MetabaseFetchResult($gDatabase, $result, $row, 5);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['additional'] = MetabaseFetchResult($gDatabase, $result, $row, 6);
$prices[MetabaseFetchResult($gDatabase, $result, $row, 2)][MetabaseFetchResult($gDatabase, $result, $row, 0)][MetabaseFetchResult($gDatabase, $result, $row, 1)]['additional_short'] = MetabaseFetchResult($gDatabase, $result, $row, 7);
}
}
if ($request->GetVar('debug', 'get') == 'true') {
print $query;
print '<br><br>';
print '<pre>';
print_r($result);
print '</pre><br><br>';
print '<pre>';
print_r($prices);
print '</pre>';
}
}
return $prices;
}
/**
* Timetracker::getpresent()
*
* This function returns an array with all dates.
*
* @return array employees
* @access public
* @since 2004-11-02
* @author Christian Ehret <*****@*****.**>
*/
function getpresent()
{
global $gDatabase, $tbl_timetracker, $tbl_employee, $request, $errorhandler;
$employees = array();
$query = "SELECT DATE_FORMAT(tt.start_date, '%d.%m.%Y %H:%i' ), e.lastname, e.firstname\n\t\t FROM {$tbl_timetracker} tt\n\t\t\t\t\t\t LEFT JOIN {$tbl_employee} e ON tt.fk_employee_id = e.pk_employee_id\n\t\t\t\t\t\t WHERE ISNULL(tt.end_date) AND ISNULL(tt.deleted_date)\n\t\t\t\t\t\t ORDER BY tt.start_date";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Timetracker::getpresent()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$employees[$row] = array('start_date' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'lastname' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'firstname' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'color' => $color);
}
}
return $employees;
}
/**
* Category::getallwithstatus()
*
* Get all categories with status if
* subscribed or not
*
* @param number $guestid id of the guest
* @return array categories and
* @access public
* @since 2003-07-24
* @author Christian Ehret <*****@*****.**>
*/
function getallwithstatus($guestid)
{
global $gDatabase, $request, $tbl_guest_guestcat, $errorhandler;
$allcat = $this->getall();
$cat = array();
$query = sprintf("SELECT pk_fk_guestcat_id " . "FROM {$tbl_guest_guestcat} " . "WHERE pk_fk_guest_id = %s ", $guestid);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'GuestCategory::getallwithstatus()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$cat[$row] = array('catid' => MetabaseFetchResult($gDatabase, $result, $row, 0));
}
}
for ($i = 0; $i < count($allcat); $i++) {
$allcat[$i][subscribed] = 'no';
for ($j = 0; $j < count($cat); $j++) {
if ($allcat[$i][catid] == $cat[$j][catid]) {
$allcat[$i][subscribed] = 'yes';
}
}
}
return $allcat;
}
/**
* Statistics::getByDayAndHour()
*
* This function returns statistics of articles by day and hour.
*
* @param string $thestart start date
* @param string $theend end date
* @param int $id category
* @param int $from from hour
* @param int $till till hour
* @return array statistic
* @access public
* @since 2004-12-10
* @author Christian Ehret <*****@*****.**>
*/
function getByDayAndHour($thestart, $theend, $id, $from = 0, $till = 23)
{
global $gDatabase, $tbl_bought, $tbl_bararticle, $request, $errorhandler;
$statistic = array();
$query = "SELECT sum(num), sum( num * price ), DATE_FORMAT( timestamp, '%d.%m.%Y' ) AS DAY, DATE_FORMAT( timestamp, '%H' ) AS HOUR\n\t\t FROM {$tbl_bought} \n\t\t\t\t\t\t LEFT JOIN {$tbl_bararticle} ON {$tbl_bought}.fk_bararticle_id = {$tbl_bararticle}.pk_bararticle_id\n\t\t\t\t\t\t WHERE timestamp >= '{$thestart}'\n\t\t\t\t\t\t AND timestamp <= '{$theend}' ";
if ($id != -1) {
$query .= " AND {$tbl_bararticle}.fk_bararticlecat_id = {$id} ";
}
if (!($from == 0 && $till == 23)) {
$query .= " AND (";
if ($from <= $till) {
for ($i = $from; $i <= $till; $i++) {
$query .= "DATE_FORMAT( timestamp, '%H' ) = {$i}";
if ($i != $till) {
$query .= " OR ";
}
}
} else {
for ($i = $from; $i <= 23; $i++) {
$query .= "DATE_FORMAT( timestamp, '%H' ) = {$i}";
$query .= " OR ";
}
for ($i = 0; $i <= $till; $i++) {
$query .= "DATE_FORMAT( timestamp, '%H' ) = {$i}";
if ($i != $till) {
$query .= " OR ";
}
}
}
$query .= ") ";
}
$query .= "\t\t GROUP BY DAY, HOUR\n\t\t\t\t\t\t ORDER BY HOUR ";
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Statistics::get()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$statistic[$row] = array('num' => MetabaseFetchResult($gDatabase, $result, $row, 0), 'price' => MetabaseFetchResult($gDatabase, $result, $row, 1), 'hour' => MetabaseFetchResult($gDatabase, $result, $row, 2), 'color' => $color);
}
}
return $statistic;
}
/**
* Newsletter::send()
*
* This function sends eMail.
*
* @param array $cats categories
* @param string $sender sender
* @param string $senderemail sender email
* @param string $subject subject
* @param string $body body
* @param boolean $andop and operator
* @param boolean $salutation insert salutation
* @return number number of mails
* @access public
* @since 2004-07-10
* @author Christian Ehret <*****@*****.**>
*/
function send($cats, $sender, $senderemail, $subject, $body, $andop, $salutation)
{
global $gDatabase, $tbl_guest, $tbl_guestcat, $tbl_guest_guestcat, $tbl_guest_address, $tbl_address, $errorhandler, $request;
set_time_limit(0);
$num = 0;
if ($andop) {
$query = "SELECT t0.pk_fk_guest_id, a.email, g.firstname, g.lastname\n\t\t\t\t FROM ";
for ($i = 0; $i < count($cats); $i++) {
$query .= "{$tbl_guest_guestcat} t{$i} ";
if ($i !== count($cats) - 1) {
$query .= ", ";
}
}
$query .= "LEFT JOIN {$tbl_guest_address} ga ON t0.pk_fk_guest_id = ga.pk_fk_guest_id AND\n\t\t\t\t \t\t\t\t\t\t\t ga.default_address = 'Y'\t\t\n\t\t\t\t LEFT JOIN {$tbl_address} a ON ga.pk_fk_address_id = a.pk_address_id \n\t\t\t\t\t LEFT JOIN {$tbl_guest} g ON t0.pk_fk_guest_id = g.pk_guest_id ";
$query .= "WHERE ";
for ($i = 0; $i < count($cats); $i++) {
$query .= "t{$i}.pk_fk_guestcat_id = {$cats[$i]} ";
if ($i > 0) {
$query .= "AND t0.pk_fk_guest_id = t{$i}.pk_fk_guest_id ";
}
if ($i !== count($cats) - 1) {
$query .= "AND ";
}
}
$query .= "AND NOT ISNULL( a.email ) ";
} else {
$query = "SELECT DISTINCT ggc.pk_fk_guest_id, a.email, g.firstname, g.lastname \n\t\t\t\t FROM {$tbl_guest_guestcat} ggc\n\t\t\t\t LEFT JOIN {$tbl_guest_address} ga ON ggc.pk_fk_guest_id = ga.pk_fk_guest_id AND\n\t\t\t\t \t\t\t\t\t\t\t ga.default_address = 'Y'\t\t\n\t\t\t\t LEFT JOIN {$tbl_address} a ON ga.pk_fk_address_id = a.pk_address_id \n\t\t\t\t LEFT JOIN {$tbl_guest} g ON ggc.pk_fk_guest_id = g.pk_guest_id\t\t \n\t\t\t\t WHERE (";
for ($i = 0; $i < count($cats); $i++) {
$query .= "ggc.pk_fk_guestcat_id = " . $cats[$i];
if ($i !== count($cats) - 1) {
$query .= " OR ";
}
}
$query .= ") AND NOT ISNULL( a.email ) ";
}
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
$errorhandler->display('SQL', 'Newsletter::send()', $query);
} else {
$row = 0;
if ($salutation) {
include_once "guestclass.inc.php";
$guestclass = new Guest();
}
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
if ($salutation) {
$salutationtxt = $guestclass->getGreeting(MetabaseFetchResult($gDatabase, $result, $row, 0)) . "\n";
} else {
$salutationtxt = "";
}
$files = $_FILES;
$message = $salutationtxt;
$message .= $body;
$email = MetabaseFetchResult($gDatabase, $result, $row, 1);
$firstname = MetabaseFetchResult($gDatabase, $result, $row, 2);
$lastname = MetabaseFetchResult($gDatabase, $result, $row, 3);
$from = $sender . " <" . $senderemail . ">";
$recipient = $firstname . " " . $lastname . " <" . $email . ">";
$mime_boundary = "<<<:" . md5(uniqid(mt_rand(), 1));
$content = "";
$header = "From: {$from}\r\n";
$header .= "Reply-To: {$from}\r\n";
$header .= "X-Priority: 3 (Normal)\r\n";
$header .= "X-Mailer: PHP/" . phpversion() . "\r\n";
$header .= "MIME-Version: 1.0\r\n";
if (is_array($files)) {
$header .= "Content-Type: multipart/mixed;\r\n";
$header .= " boundary=\"" . $mime_boundary . "\"\r\n";
$content = "This is a multi-part message in MIME format.\r\n\r\n";
$content .= "--" . $mime_boundary . "\r\n";
}
$content .= "Content-Type: text/plain; charset=\"ISO-8859-1\"\r\n";
$content .= "Content-Transfer-Encoding: 8bit\r\n\r\n";
$content .= "{$message}\r\n\r\n";
if (is_array($files)) {
$content .= "--" . $mime_boundary . "\r\n";
foreach ($files as $filename => $filelocation) {
$filename = $filelocation['name'];
$filelocation = $filelocation['tmp_name'];
if (is_readable($filelocation)) {
$data = chunk_split(base64_encode(implode("", file($filelocation))));
$content .= "Content-Disposition: attachment;\r\n";
if (!function_exists("mime_content_type")) {
$mimecontenttype = "application/octet-stream";
} else {
$mimecontenttype = mime_content_type($filelocation);
}
$content .= "Content-Type: " . $mimecontenttype . ";";
$content .= " name=\"" . $filename . "\"\r\n";
$content .= "Content-Transfer-Encoding: base64\r\n\r\n";
$content .= $data . "\r\n";
$content .= "--" . $mime_boundary . "\r\n";
}
}
}
mail($recipient, $subject, $content, $header);
$num++;
}
}
return $num;
}
/**
* Get the suggestions from the database and write to array
*/
function getSuggestions($prefix, &$arr)
{
global $gDatabase, $tbl_guest, $errorhandler;
// firstly clean up the data
//$o = $prefix;
//setlocale (LC_ALL, 'de_DE');
// $prefix = ltrim(preg_replace("/^[[:alpha:]]+$/", '', strtolower($prefix)));
$prefix = preg_replace('/\\s+/', ' ', strtolower($prefix));
if (strlen($prefix) > 0) {
$query = sprintf("SELECT lastname, firstname FROM {$tbl_guest} \n\t\t\t\t\t WHERE lastname LIKE '%s%%' AND ISNULL(deleted_date)\n\t\t\t\t\t LIMIT %d", $prefix, $this->suggestion_limit);
$result = MetabaseQuery($gDatabase, $query);
if (!$result) {
return;
} else {
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase, $result)) == 0; ++$row) {
$arr[$row] = array(MetabaseFetchResult($gDatabase, $result, $row, 0), MetabaseFetchResult($gDatabase, $result, $row, 1));
}
}
}
}
/**
* Category::getall2()
*
* This function returns all employees from all hotels.
*
* @return array employees
* @access public
* @since 2004-10-03
* @author Christian Ehret <*****@*****.**>
*/
function getall2()
{
global $gDatabase2, $tbl_employee, $request, $errorhandler;
$user = array();
$query = "SELECT pk_employee_id, lastname, firstname, login, salary \n\t\t FROM {$tbl_employee} \n\t\t\t\t\t\t WHERE ISNULL(deleted_date)\n\t\t\t\t\t\t ORDER BY login";
$result = MetabaseQuery($gDatabase2, $query);
if (!$result) {
$errorhandler->display('SQL', 'Employee::getall2()', $query);
} else {
$row = 0;
for ($row = 0; ($eor = MetabaseEndOfResult($gDatabase2, $result)) == 0; ++$row) {
$color = 0;
if ($row % 2 != 0) {
$color = 1;
}
$user[$row] = array('userid' => MetabaseFetchResult($gDatabase2, $result, $row, 0), 'lastname' => MetabaseFetchResult($gDatabase2, $result, $row, 1), 'firstname' => MetabaseFetchResult($gDatabase2, $result, $row, 2), 'login' => MetabaseFetchResult($gDatabase2, $result, $row, 3), 'salary' => MetabaseFetchResult($gDatabase2, $result, $row, 4), 'color' => $color);
}
}
return $user;
}
