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; }
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; }
/** * Checkin::checkbookingin() * * This function checks in a booking. * * @param integer $bookingid bookingid * @access public * @since 2003-09-02 * @author Christian Ehret <*****@*****.**> */ function checkbookingin($bookingid) { global $gDatabase, $tbl_booking, $errorhandler; $guests = array(); $query = sprintf("UPDATE {$tbl_booking} " . "SET checked_in = %s " . "WHERE pk_booking_id = %s ", MetabaseGetBooleanFieldValue($gDatabase, true), $bookingid); $result = MetabaseQuery($gDatabase, $query); if (!$result) { $errorhandler->display('SQL', 'Checkin::checkin()', $query); } }
/** * createEmailConfirmation() * * Create a mailto link for email confirmation * * @param number $bookid id of booking * @return string mailto link * @access public * @since 2003-10-12 * @author Christian Ehret <*****@*****.**> */ function createEmailConfirmation($bookid) { global $tbl_booking, $tbl_guest, $tbl_address, $tbl_guest_address, $tbl_bookingcat, $tbl_country, $gDatabase, $errorhandler, $request; include_once 'guestclass.inc.php'; $guest = new Guest(); $query = "SELECT {$tbl_booking}.fk_bookingcat_id, {$tbl_booking}.fk_guest_id, \n\t UNIX_TIMESTAMP(start_date), UNIX_TIMESTAMP(end_date), \n\t\t\t TO_DAYS( end_date ) - TO_DAYS( start_date ) AS days, \n\t\t\t {$tbl_booking}.persons, children, {$tbl_guest}.firstname, \n\t\t\t {$tbl_guest}.lastname, {$tbl_bookingcat}.bookingcat, \n\t\t\t DATE_FORMAT({$tbl_guest}.date_of_birth , '%d.%m.%Y'), \n\t\t\t postalcode, city, address, country_de, email, \n\t\t\t booking_type, {$tbl_guest}.formal_greeting, \n\t\t\t {$tbl_guest}.gender, \n\t\t\t COALESCE( CASE WHEN {$tbl_bookingcat}.description = '' THEN NULL ELSE {$tbl_bookingcat}.description END, CASE WHEN {$tbl_bookingcat}.bookingcat = '' THEN NULL ELSE {$tbl_bookingcat}.bookingcat END, '' ),\n\t\t\t children2, children3, children0 \n\t\t\t FROM {$tbl_booking} \n\t\t\t LEFT JOIN {$tbl_guest} ON ({$tbl_booking}.fk_guest_id = {$tbl_guest}.pk_guest_id) \n\t\t\t LEFT JOIN {$tbl_guest_address} ON ({$tbl_guest_address}.default_address = " . MetabaseGetBooleanFieldValue($gDatabase, true) . " \n\t\t\t AND {$tbl_guest}.pk_guest_id = {$tbl_guest_address}.pk_fk_guest_id) \n\t\t\t LEFT JOIN {$tbl_address} ON ({$tbl_address}.pk_address_id = {$tbl_guest_address}.pk_fk_address_id) \n\t\t\t LEFT JOIN {$tbl_bookingcat} ON ({$tbl_booking}.fk_bookingcat_id = {$tbl_bookingcat}.pk_bookingcat_id) \n\t\t\t LEFT JOIN {$tbl_country} ON (fk_country_id = {$tbl_country}.pk_country_id )\n\t\t\t WHERE pk_booking_id = " . $bookid; $result = MetabaseQuery($gDatabase, $query); if (!$result) { $errorhandler->display('SQL', 'Booking::emailConfirmation()', $query); } else { if (MetabaseNumberOfRows($gDatabase, $result) == 1) { $booking = array(); $str = ""; $bookingtype = ""; $bookingstr = ""; $isformal = MetabaseFetchBooleanResult($gDatabase, $result, 0, 16); $dirihnen = "Dir"; if ($isformal) { $dirihnen = "Ihnen"; } switch (MetabaseFetchResult($gDatabase, $result, 0, 16)) { case 'R': $bookingtype = "Reservierungsbestätigung"; $bookingstr = "Reservierung"; break; case 'B': $bookingtype = "Buchungsbestätigung"; $bookingstr = "Buchung"; break; case 'P': $bookingtype = "Buchungsbestätigung"; $bookingstr = "Buchung"; break; } $body = ""; $body .= $guest->GetGreeting(MetabaseFetchResult($gDatabase, $result, 0, 1)) . "\n"; $body .= "hiermit bestätigen wir " . $dirihnen . " folgende " . $bookingstr . ":\n\n"; $body .= "Anreise: " . date("d. m. Y", MetabaseFetchResult($gDatabase, $result, 0, 2)) . "\n"; $body .= "Abreise: " . date("d. m. Y", MetabaseFetchResult($gDatabase, $result, 0, 3)) . "\n"; $body .= "Kategorie: " . MetabaseFetchResult($gDatabase, $result, 0, 19) . "\n"; $body .= "Erwachsene: " . MetabaseFetchResult($gDatabase, $result, 0, 5) . "\n"; $body .= $request->GetVar('children0', 'session') . ": " . MetabaseFetchResult($gDatabase, $result, 0, 22) . "\n"; $body .= $request->GetVar('children1', 'session') . ": " . MetabaseFetchResult($gDatabase, $result, 0, 6) . "\n"; $body .= $request->GetVar('children2', 'session') . ": " . MetabaseFetchResult($gDatabase, $result, 0, 20) . "\n"; $body .= $request->GetVar('children3', 'session') . ": " . MetabaseFetchResult($gDatabase, $result, 0, 21) . "\n\n"; $body .= "für:\n"; $body .= MetabaseFetchResult($gDatabase, $result, 0, 7) . " " . MetabaseFetchResult($gDatabase, $result, 0, 8) . "\n"; $body .= MetabaseFetchResult($gDatabase, $result, 0, 13) . "\n"; $body .= MetabaseFetchResult($gDatabase, $result, 0, 11) . " " . MetabaseFetchResult($gDatabase, $result, 0, 12) . "\n"; $body .= MetabaseFetchResult($gDatabase, $result, 0, 14) . "\n\n"; $str = "mailto:" . MetabaseFetchResult($gDatabase, $result, 0, 7) . "%20" . MetabaseFetchResult($gDatabase, $result, 0, 8) . "%20<" . MetabaseFetchResult($gDatabase, $result, 0, 15) . ">?subject={$bookingtype}" . "&body=" . rawurlencode($body); } return $str; } }
function query($query, $obj) { global $mdb; //$this->model->query_arr["query"][] = $query ; //$db_res = MetabaseQuery($mdb,$query_chartset); $db_res = MetabaseQuery($mdb, $query); if (!$db_res) { if ($obj->config->debug_mod == "Y") { $this->sqlError($mdb, $query); } else { $obj->message->alertErr("system_err", $obj); } } return $db_res; }
} } if ($support_lobs && isset($tests["lobnulls"]) && $success) { $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}"; } }
/** * Category::del() * * Deletes a category * * @param number $catid category * @access public * @since 2003-07-24 * @author Christian Ehret <*****@*****.**> */ function del($catid) { global $gDatabase, $tbl_paycat, $request, $errorhandler; $query = sprintf("UPDATE {$tbl_paycat} \n\t\t\t\t\t\tSET deleted_date = NOW(), \n\t\t\t\t\t\tfk_deleted_user_id = %s \n\t\t\t\t\t\tWHERE pk_paycat_id = %s ", $request->GetVar('uid', 'session'), $catid); $result = MetabaseQuery($gDatabase, $query); if (!$result) { $errorhandler->display('SQL', 'PayCategory::del()', $query); } }
/** * 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; }
/** * bcat::saveupdate() * * save or update a booking category * * @return number booking category id * @access public * @since 2003-07-24 * @author Christian Ehret <*****@*****.**> */ function saveupdate() { global $gDatabase, $request, $tbl_bookingcat, $errorhandler; $bcatid = $request->GetVar('frm_bcatid', 'post'); // update if ($bcatid !== '0') { $query = sprintf("UPDATE {$tbl_bookingcat} SET \n\t\t\t\t\t\t\t bookingcat = %s, \n\t\t\t\t\t\t\t color = %s, \n\t\t\t\t\t\t\t description = %s, \n\t\t\t\t\t\t\t days = %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_bookingcat_id = %s ", MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_name', 'post')), MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_color', 'post')), MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_description', 'post')), $request->GetVar('frm_days', 'post'), $request->GetVar('uid', 'session'), $bcatid); } else { // new $name = "zvs_pk_bookingcat_id"; $sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$bcatid); $query = sprintf("INSERT INTO {$tbl_bookingcat}\n\t\t\t\t\t\t\t (pk_bookingcat_id, bookingcat, color, description, days, inserted_date, fk_inserted_user_id )\n\t\t\t\t\t\t\t VALUES (%s, %s, %s, %s, %s, NOW(), %s )", $bcatid, MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_name', 'post')), MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_color', 'post')), MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_description', 'post')), $request->GetVar('frm_days', 'post'), $request->GetVar('uid', 'session')); } $result = MetabaseQuery($gDatabase, $query); if (!$result) { $errorhandler->display('SQL', 'BookingCategory::saveupdate()', $query); } else { return $bcatid; } }
function query($query) { parent::query($query); $this->_count = 0; $result = MetabaseQuery($this->db, $query); if ($result == 0) { $this->error = MetabaseError($this->db); return false; } else { $this->result = $result; return true; } }
if (!$result) { $success = MetabaseRollbackTransaction($gDatabase); $errorhandler->display('SQL', 'database', $query); } $success = splitSqlFile($queries, $sql); for ($i = 0; $i < count($queries); ++$i) { if (substr($queries[$i], 0, 3) == "USE") { $query = "USE {$schema}"; } else { $query = $queries[$i]; } $result = MetabaseQuery($gDatabase, $query); if (!$result) { $success = MetabaseRollbackTransaction($gDatabase); $errorhandler->display('SQL', 'database', $query); } } $query = "SET FOREIGN_KEY_CHECKS = 0"; $result = MetabaseQuery($gDatabase, $query); if (!$result) { $success = MetabaseRollbackTransaction($gDatabase); $errorhandler->display('SQL', 'database', $query); } $success = MetabaseCommitTransaction($gDatabase); // end transaction $auto_commit = true; $success = MetabaseAutoCommitTransactions($gDatabase, $auto_commit); $smarty->assign('tpl_ready', 'true'); } } $smarty->display('database.tpl');
/** * 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::save() * * save prices for a season * * @access public * @since 2004-01-01 * @author Christian Ehret <*****@*****.**> */ function save() { global $gDatabase, $request, $tbl_price, $tbl_price2, $errorhandler, $rcat, $bcat; // turn on Transaction Control $auto_commit = false; $success = MetabaseAutoCommitTransactions($gDatabase, $auto_commit); $seasonids = array(); $seasonids = $request->getVar('frm_seasonid', 'post'); $bcats = $bcat->Get(); $rcats = $rcat->get('N'); $rcats2 = $rcat->get('A'); for ($i = 0; $i < count($seasonids); $i++) { // delete all old entries for the season for price normal $query = sprintf("DELETE FROM {$tbl_price} WHERE fk_season_id = %s", $seasonids[$i]); $result = MetabaseQuery($gDatabase, $query); if (!$result) { $success = MetabaseRollbackTransaction($gDatabase); $errorhandler->display('SQL', 'Price::save()', $query); } // delete all old entries for the season for price advanced $query = sprintf("DELETE FROM {$tbl_price2} WHERE fk_season_id = %s", $seasonids[$i]); $result = MetabaseQuery($gDatabase, $query); if (!$result) { $success = MetabaseRollbackTransaction($gDatabase); $errorhandler->display('SQL', 'Price::save()', $query); } // insert new values for ($j = 0; $j < count($bcats); $j++) { for ($k = 0; $k < count($rcats); $k++) { $frmperson = "frm_price_adult_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmperson_short = "frm_price_adult_short_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmchildren = "frm_price_child_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmchildren_short = "frm_price_child_short_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmchildren2 = "frm_price_child2_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmchildren2_short = "frm_price_child2_short_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmchildren3 = "frm_price_child3_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmchildren3_short = "frm_price_child3_short_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmabsolute = "frm_price_pausch_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmabsolute_short = "frm_price_pausch_short_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $frmtype = "frm_type_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats[$k][catid]; $priceperson = 0.0; $pricepersonshort = 0.0; $pricechildren = 0.0; $pricechildrenshort = 0.0; $pricechildren2 = 0.0; $pricechildren2short = 0.0; $pricechildren3 = 0.0; $pricechildren3short = 0.0; if ($request->GetVar($frmperson, 'post') !== "") { $priceperson = $request->GetVar($frmperson, 'post'); } if ($request->GetVar($frmperson_short, 'post') !== "") { $pricepersonshort = $request->GetVar($frmperson_short, 'post'); } if ($request->GetVar($frmchildren, 'post') !== "") { $pricechildren = $request->GetVar($frmchildren, 'post'); } if ($request->GetVar($frmchildren_short, 'post') !== "") { $pricechildrenshort = $request->GetVar($frmchildren_short, 'post'); } if ($request->GetVar($frmchildren2, 'post') !== "") { $pricechildren2 = $request->GetVar($frmchildren2, 'post'); } if ($request->GetVar($frmchildren2_short, 'post') !== "") { $pricechildren2short = $request->GetVar($frmchildren2_short, 'post'); } if ($request->GetVar($frmchildren3, 'post') !== "") { $pricechildren3 = $request->GetVar($frmchildren3, 'post'); } if ($request->GetVar($frmchildren3_short, 'post') !== "") { $pricechildren3short = $request->GetVar($frmchildren3_short, 'post'); } $name = "zvs_pk_price_id"; $sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$priceid); $query = sprintf("INSERT INTO {$tbl_price}\n\t\t\t\t\t\t\t\t (pk_price_id, fk_roomcat_id, fk_bookingcat_id,\n\t\t\t\t\t\t\t\t fk_season_id, price_person, price_children, \n\t\t\t\t\t\t\t\t price_type, inserted_date, fk_inserted_user_id, \n\t\t\t\t\t\t\t\t price_person_short, price_children_short,\n\t\t\t\t\t\t\t\t price_children2, price_children2_short, price_children3, price_children3_short) \n\t\t\t\t\t\t\t\t VALUES\n\t\t\t\t\t\t\t\t (%s, %s, %s, %s, %s, %s, %s, NOW(), %s, %s, %s, %s, %s, %s, %s) ", $priceid, $rcats[$k][catid], $bcats[$j][bcatid], $seasonids[$i], $priceperson, $pricechildren, MetabaseGetTextFieldValue($gDatabase, 'PP'), $request->GetVar('uid', 'session'), $pricepersonshort, $pricechildrenshort, $pricechildren2, $pricechildren2short, $pricechildren3, $pricechildren3short); $result = MetabaseQuery($gDatabase, $query); if (!$result) { $success = MetabaseRollbackTransaction($gDatabase); $errorhandler->display('SQL', 'Price::save()', $query); } } for ($k = 0; $k < count($rcats2); $k++) { $frmprice = "frm_price_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats2[$k][catid]; $frmprice_short = "frm_price_short_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats2[$k][catid]; $frmperson = "frm_person_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats2[$k][catid]; $frmadditional = "frm_additional_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats2[$k][catid]; $frmadditional_short = "frm_additional_short_" . $seasonids[$i] . "_" . $bcats[$j][bcatid] . "_" . $rcats2[$k][catid]; $price = 0.0; $priceshort = 0.0; $additional = 0.0; $additionalshort = 0.0; if ($request->GetVar($frmprice, 'post') !== "") { $price = $request->GetVar($frmprice, 'post'); } if ($request->GetVar($frmprice_short, 'post') !== "") { $priceshort = $request->GetVar($frmprice_short, 'post'); } if ($request->GetVar($frmadditional, 'post') !== "") { $additional = $request->GetVar($frmadditional, 'post'); } if ($request->GetVar($frmadditional_short, 'post') !== "") { $additionalshort = $request->GetVar($frmadditional_short, 'post'); } $name = "zvs_pk_price2_id"; $sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$priceid); $query = sprintf("INSERT INTO {$tbl_price2}\n\t\t\t\t\t\t\t\t (pk_price2_id, fk_roomcat_id, fk_bookingcat_id,\n\t\t\t\t\t\t\t\t fk_season_id, price, price_short, \n\t\t\t\t\t\t\t\t persons_included, price_additional, price_short_additional,\n\t\t\t\t\t\t\t\t inserted_date, fk_inserted_user_id) \n\t\t\t\t\t\t\t\t VALUES\n\t\t\t\t\t\t\t\t (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), %s) ", $priceid, $rcats2[$k][catid], $bcats[$j][bcatid], $seasonids[$i], $price, $priceshort, MetabaseGetTextFieldValue($gDatabase, $request->GetVar($frmperson, 'post')), $additional, $additionalshort, $request->GetVar('uid', 'session')); $result = MetabaseQuery($gDatabase, $query); if (!$result) { $success = MetabaseRollbackTransaction($gDatabase); $errorhandler->display('SQL', 'Price::save()', $query); } } } } $success = MetabaseCommitTransaction($gDatabase); // end transaction $auto_commit = true; $success = MetabaseAutoCommitTransactions($gDatabase, $auto_commit); }
/** * Calendar::getdates() * * This function returns an array with all dates. * * @return array dates * @access public * @since 2005-01-11 * @author Christian Ehret <*****@*****.**> */ function getdates() { global $gDatabase, $tbl_booking_detail, $request, $errorhandler; $dates = array(); $j = 0; $query = "SELECT DATE_FORMAT(min( start_date ) ,'%Y') \n\t\t FROM {$tbl_booking_detail} "; $result = MetabaseQuery($gDatabase, $query); if (!$result) { $errorhandler->display('SQL', 'Calendar::getdates()', $query); } else { $startyear = MetabaseFetchResult($gDatabase, $result, 0, 0); $todaydate = getdate(); $todayyear = $todaydate['year']; $i = 0; for ($year = $startyear; $year <= $todayyear; ++$year) { $dates[$i] = $year; ++$i; } for ($j = 0; $j <= 20; ++$j) { $dates[$i] = $year + $j; ++$i; } } return $dates; }
/** * 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; }
/** * Kassa::setinactive() * * This function sets a user inactive. * * @param $guestid guest id * @return array articles * @access public * @since 2004-01-06 * @author Christian Ehret <*****@*****.**> */ function setinactive($guestid) { global $gDatabase2, $tbl_barguest, $request, $errorhandler, $wwwroot; $query = sprintf("UPDATE {$tbl_barguest}\n\t\t\t\t\t\t\t\t SET deleted_date = NOW(),\n\t\t\t\t\t\t\t\t fk_deleted_user_id = %s\n\t\t\t\t\t\t\t\t WHERE pk_barguest_id = %s", $request->GetVar('uid', 'session'), $guestid); $result = MetabaseQuery($gDatabase2, $query); if (!$result) { $errorhandler->display('SQL', 'Kassa::setinactive()', $query); } else { $startpage = $wwwroot . "index.php"; header("Location: {$startpage}"); } }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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); } } }
/** * User::del() * * Deletes an Barguestcat * * @param number $Barguestcatid Barguestcat id * @access public * @since 2006-02-17 * @author Christian Ehret <*****@*****.**> */ function del($barguestcatid) { global $gDatabase, $tbl_barguestcat, $errorhandler, $request; $query = sprintf("UPDATE {$tbl_barguestcat} SET \n\t\t\t\t\t\t\t deleted_date = NOW(), \n\t\t\t\t\t\t\t fk_deleted_user_id = %s \n\t\t\t\t\t\t\t WHERE pk_barguestcat_id = %s ", $request->GetVar('uid', 'session'), $barguestcatid); $result = MetabaseQuery($gDatabase, $query); if (!$result) { $errorhandler->display('SQL', 'Barguestcat::del()', $query); } }
/** * 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; }
/** * 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; }
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; } }
/** * room::saveupdate() * * save a new room or update an existing one * * @return number room id * @access public * @since 2003-07-24 * @author Christian Ehret <*****@*****.**> */ function saveupdate() { global $gDatabase, $request, $tbl_room, $errorhandler; $roomid = $request->GetVar('frm_roomid', 'post'); // update if ($roomid !== '0') { $query = sprintf("UPDATE {$tbl_room} SET " . "room = %s, " . "capacity = %s, " . "description = %s, " . "fk_roomcat_id = %s, " . "updated_date = NOW(), " . "fk_updated_user_id = %s " . "WHERE pk_room_id = %s ", MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_name', 'post')), MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_persons', 'post')), MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_info', 'post')), $request->GetVar('frm_roomcat', 'post'), $request->GetVar('uid', 'session'), $roomid); } else { // new $name = "zvs_pk_room_id"; $sequence = MetabaseGetSequenceNextValue($gDatabase, $name, &$roomid); $query = sprintf("INSERT INTO {$tbl_room}" . "(pk_room_id, room, capacity, description, " . "fk_roomcat_id, inserted_date, fk_inserted_user_id )" . "VALUES (%s, %s, %s, %s, %s, NOW(), %s )", $roomid, MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_name', 'post')), MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_persons', 'post')), MetabaseGetTextFieldValue($gDatabase, $request->GetVar('frm_info', 'post')), $request->GetVar('frm_roomcat', 'post'), $request->GetVar('uid', 'session')); } $result = MetabaseQuery($gDatabase, $query); if (!$result) { $errorhandler->display('SQL', 'room::saveupdate()', $query); } else { return $roomid; } }
/** * Employee::del() * * Deletes an employee * * @param number $userid user id * @access public * @since 2004-10-03 * @author Christian Ehret <*****@*****.**> */ function del($userid) { global $gDatabase2, $tbl_employee, $errorhandler, $request; $query = sprintf("UPDATE {$tbl_employee} SET \n\t\t\t\t\t\t\t locked = %s,\n\t\t\t\t\t\t\t deleted_date = NOW(), \n\t\t\t\t\t\t\t fk_deleted_user_id = %s \n\t\t\t\t\t\t\t WHERE pk_employee_id = %s ", MetabaseGetBooleanFieldValue($gDatabase2, true), $request->GetVar('uid', 'session'), $userid); $result = MetabaseQuery($gDatabase2, $query); if (!$result) { $errorhandler->display('SQL', 'Employee::del()', $query); } }
/** * roomchangelist::getdates() * * This function returns an array with all dates. * * @return array dates * @access public * @since 2004-06-12 * @author Christian Ehret <*****@*****.**> */ function getdates() { global $gDatabase, $tbl_booking, $request, $errorhandler; $dates = array(); $j = 0; $query = "SELECT DATE_FORMAT(min( start_date ) ,'%Y'), DATE_FORMAT(max( end_date ),'%Y') \n\t\t FROM {$tbl_booking} "; $result = MetabaseQuery($gDatabase, $query); if (!$result) { $errorhandler->display('SQL', 'roomchangelist::getdates()', $query); } else { $startyear = MetabaseFetchResult($gDatabase, $result, 0, 0); $endyear = MetabaseFetchResult($gDatabase, $result, 0, 1); for ($year = $startyear; $year <= $endyear; ++$year) { for ($i = 1; $i <= 12; $i++) { $dates[$j] = $i . '/' . $year; $j++; } } } return $dates; }
/** * 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)); } } } }
/** * 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; }