function getIfSportIsEnabled($sport)
{
$request = getDb()->prepare("SELECT actif FROM sport WHERE nom = :sport");
$request->bindParam(':sport', $sport, PDO::PARAM_STR);
$request->execute();
return $request->fetchAll(PDO::FETCH_ASSOC);
}
/**
* Create a new version of the photo with ID $id as specified by $width, $height and $options.
*
* @param string $id ID of the photo to create a new version of.
* @param string $hash Hash to validate this request before creating photo.
* @param int $width The width of the photo to which this URL points.
* @param int $height The height of the photo to which this URL points.
* @param int $options The options of the photo wo which this URL points.
* @return string HTML
*/
public function create($id, $hash, $width, $height, $options = null)
{
$fragment = $this->photo->generateFragment($width, $height, $options);
// We cannot call the API since this may not be authenticated.
// Rely on the hash to confirm it was a valid request
$db = getDb();
$photo = $db->getPhoto($id);
if ($photo) {
}
// check if this size exists
if (isset($photo["path{$fragment}"]) && stristr($photo["path{$fragment}"], "/{$hash}/") === false) {
$url = $this->photo->generateUrlPublic($photo, $width, $height, $options);
$this->route->redirect($url, 301, true);
return;
} else {
// TODO, this should call a method in the API
$photo = $this->photo->generate($id, $hash, $width, $height, $options);
// TODO return 404 graphic
if ($photo) {
header('Content-Type: image/jpeg');
readfile($photo);
unlink($photo);
return;
}
}
$this->route->run('/error/500');
}
function checkSession()
{
$oDb = getDb();
$key = $_COOKIE['SN_KEY'];
if (empty($key)) {
return false;
}
$data = $oDb->fetchFirstArray("SELECT * FROM tb_session WHERE `key` = '{$key}'");
//$data = $this->where(array('key'=>$key))->find();
if (empty($data)) {
return false;
}
//超过2小时过期
if ($data['expire'] + 7200 < time()) {
return false;
}
//ip变化
if (get_client_ip() != $data['ip']) {
return false;
}
//更新有效期
$oDb->update('tb_session', array('expire' => time() + 7200), "id = " . $data['id']);
//$this->where(array('id'=>$data['id']))->data(array('expire' => time() + 7200))->save();
return $data['uid'];
}
function setupAdmin($config)
{
checkDbAccess($config);
$user_sql = "CREATE TABLE `users` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,\n`name` VARCHAR(32) NOT NULL ,\n`password` TEXT NOT NULL ,\n`auth_key` TEXT NOT NULL ,\n`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\nPRIMARY KEY (`id`),\nUNIQUE (`name`)\n) ENGINE = InnoDB;";
$group_sql = "CREATE TABLE `groups` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(64) NOT NULL , `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`), UNIQUE (`name`)) ENGINE = InnoDB;";
$agent_group = "CREATE TABLE `agent_group` (`agent_id` INT UNSIGNED NOT NULL, `group_id` INT UNSIGNED NOT NULL, UNIQUE (`agent_id`, `group_id`)) ENGINE = InnoDB;";
$task_group = "CREATE TABLE `task_group` (`task_id` INT UNSIGNED NOT NULL, `group_id` INT UNSIGNED NOT NULL, UNIQUE (`task_id`, `group_id`)) ENGINE = InnoDB;";
$task_types = "CREATE TABLE `task_types` (`id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(64) NOT NULL , PRIMARY KEY (`id`), UNIQUE (`name`)) ENGINE = InnoDB";
$fk_group_id = "ALTER TABLE `task_group` ADD CONSTRAINT `fk_task_group_id` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;";
$fk_task_id = "ALTER TABLE `task_group` ADD CONSTRAINT `fk_task_task_id` FOREIGN KEY (`task_id`) REFERENCES `task`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;";
$fk_agent_group_id = "ALTER TABLE `agent_group` ADD CONSTRAINT `fk_agent_group_id` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;";
$fk_agent_agent_id = "ALTER TABLE `agent_group` ADD CONSTRAINT `fk_agent_agent_id` FOREIGN KEY (`agent_id`) REFERENCES `agent`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;";
$default_task_types = "INSERT INTO task_types (`name`) VALUES ('DOWNLOADFILE'),('SELFDESTRUCTION')";
$db = getDb($config);
$t = $db->beginTransaction();
if (!$t) {
die("cannot create DB transaction");
}
execSql($db, $user_sql, "create user table");
execSql($db, $group_sql, "create groups table");
execSql($db, $task_types, "create task types table");
execSql($db, $default_task_types, "add default task types");
execSql($db, $agent_group, "create agent-group table");
execSql($db, $task_group, "create task-group table");
execSql($db, $fk_group_id, "make foreign key on task_group ('group_id')");
execSql($db, $fk_task_id, "make foreign key on task_group ('task_id')");
execSql($db, $fk_agent_agent_id, "make foreign key on agent_group ('agent_id')");
execSql($db, $fk_agent_group_id, "make foreign key on agent_group ('group_id')");
}
function getPersons()
{
$db = getDb();
$query = $db->prepare('SELECT DISTINCT person FROM photo2person ORDER BY person ASC');
$query->execute();
return $query->fetchAll();
}
public function __construct()
{
$this->config = getConfig()->get();
$this->db = getDb();
$this->utility = new Utility();
$this->user = new User();
}
function addUser($email, $first_name, $last_name, $phone, $address, $town, $organization, $country, $zip, $october6, $october7, $october8, $october9, $october10, $msg_email, $msg_phone)
{
$db = getDb();
//$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
try {
$stmt = $db->prepare('INSERT INTO users (email, first_name, last_name, phone, address, town, organization, country, zip, october6, october7, october8, october9, october10, msg_email, msg_phone)
VALUES ("' . $email . '", "' . $first_name . '", "' . $last_name . '", "' . $phone . '", "' . $address . '", "' . $town . '", "' . $organization . '", "' . $country . '", "' . $zip . '", ' . $october6 . ', ' . $october7 . ', ' . $october8 . ', ' . $october9 . ', ' . $october10 . ', ' . $msg_email . ', ' . $msg_phone . ')');
// $stmt = $db->prepare('INSERT INTO users (email, first_name, last_name, phone, address, town, organization, country, zip, october6, october7, october8, october9, october10, msg_email, msg_phone)
// VALUES ("' . $email .'", "' . $first_name .'", "' . $last_name .'", "' . $phone .'", "' . $address .'", "' . $town .'", "' . $organization .'", "' . $country .'", "' . $zip .'", true, false, true, true, false, true, true)');
/*$stmt->bindValue(':email', $email, PDO::PARAM_STR);
$stmt->bindValue(':first_name', $first_name, PDO::PARAM_STR);
$stmt->bindValue(':last_name', $last_name, PDO::PARAM_STR);
$stmt->bindValue(':phone', $phone, PDO::PARAM_STR);
$stmt->bindValue(':address', $address, PDO::PARAM_STR);
$stmt->bindValue(':town', $town, PDO::PARAM_STR);
$stmt->bindValue(':organization', $organization, PDO::PARAM_STR);
$stmt->bindValue(':country', $country, PDO::PARAM_STR);
$stmt->bindValue(':zip', $zip, PDO::PARAM_STR);
$stmt->bindValue(':october6', $october6, PDO::PARAM_BOOL);
$stmt->bindValue(':october7', $october7, PDO::PARAM_BOOL);
$stmt->bindValue(':october8', $october8, PDO::PARAM_BOOL);
$stmt->bindValue(':october9', $october9, PDO::PARAM_BOOL);
$stmt->bindValue(':october10', $october10, PDO::PARAM_STR);
$stmt->bindValue(':msg_email', $msg_email, PDO::PARAM_BOOL);
$stmt->bindValue(':msg_phone', $msg_phone, PDO::PARAM_BOOL); */
$stmt->execute();
// TODO: This does not work, please fix
} catch (PDOException $e) {
// echo $e->getMessage();
}
sendEmail($first_name, $last_name, $october6, $october7, $october8, $october9, $october10, $email);
// print_r($db->errorInfo());
}
function get_name_artist($limit)
{
$request = getDb()->prepare('SELECT nameArtist FROM artists LIMIT :limitArtist');
$request->bindParam(':limitArtist', $limit, PDO::PARAM_INT);
$request->execute();
return $request->fetchAll(PDO::FETCH_ASSOC);
}
function getRow()
{
$test = getId();
$server = getDb();
$sql = "SELECT id, imdb_id, adult, backdrop_path, genres, original_title, overview, popularity, poster_path, runtime, release_date, vote_average, vote_count, director, cast, trailer FROM tmdb_movies WHERE id='{$test}' LIMIT 1";
$query = mysqli_query($server, $sql);
return $row = mysqli_fetch_row($query);
}
function getUserRow($name)
{
$db = getDb();
$sqlStr = 'SELECT * FROM user WHERE name = ?';
$sqlOperation = $db->prepare($sqlStr);
$sqlOperation->execute(array($name));
return $sqlOperation->fetchAll();
}
function insert_comment_schedule($idUser, $idSchedule, $content)
{
$request = getDb()->prepare("INSERT INTO `festival`.`comment` (`idComment`, `idUser`, `idSchedule`, `idArtist`, `dateCommentaire`, `content`, `isArtist`) VALUES (NULL, :idUser, :idSchedule, 0, :date, :content, 0);");
$request->bindParam(':content', $content, PDO::PARAM_STR, 200);
$request->bindParam(':idSchedule', $idSchedule, PDO::PARAM_INT);
$request->bindParam(':idUser', $idUser, PDO::PARAM_INT);
$request->bindParam(':date', date("Y-m-j"), PDO::PARAM_STR);
$request->execute();
}
public function list_()
{
getAuthentication()->requireAuthentication();
$res = getDb()->getCredentials();
if ($res !== false) {
return $this->success('Oauth Credentials', $res);
} else {
return $this->error('Could not retrieve credentials', false);
}
}
public function isAllowed()
{
$query = getDb()->prepare("SELECT id FROM module_allowed WHERE module_name = ? AND username = ?");
$query->execute(array($this->module, $this->username));
if ($query->rowCount() > 0) {
return true;
} else {
return false;
}
}
/**
* API to get versions of the source, filesystem and database
*
* @return string Standard JSON envelope
*/
public function version()
{
getAuthentication()->requireAuthentication();
$apiVersion = Request::getLatestApiVersion();
$systemVersion = getConfig()->get('site')->lastCodeVersion;
$databaseVersion = getDb()->version();
$databaseType = getDb()->identity();
$filesystemVersion = '0.0.0';
$filesystemType = getFs()->identity();
return $this->success('System versions', array('api' => $apiVersion, 'system' => $systemVersion, 'database' => $databaseVersion, 'databaseType' => $databaseType, 'filesystem' => $filesystemVersion, 'filesystemType' => $filesystemType));
}
public function isAlive()
{
$query = getDb()->prepare("SELECT timestamp FROM alive WHERE localisation = ?");
$query->execute(array($this->nom));
$row = $query->fetch();
if ($row['timestamp'] / 1000 > time() - 15000) {
return true;
} else {
return false;
}
}
function reportNewSpeakersPerCon()
{
$conn = getDb();
$sql = "SELECT event.start_date, event.name, talks_count, num_speakers, new_speakers, FORMAT((new_speakers/event.num_speakers)*100, 1) AS percent_new\n FROM event\n WHERE start_date >= '2010-01-01'\n ORDER BY start_date";
$stmt = $conn->executeQuery($sql);
$rows = $stmt->fetchAll();
$header = ['Date', 'Event', 'Total sessions', 'Speakers', 'New speakers', 'Percent new'];
$stmt = $conn->executeQuery("SELECT 'N/A', 'Average', FORMAT(AVG(talks_count), 1), FORMAT(AVG(num_speakers), 1), FORMAT(AVG(new_speakers), 1), FORMAT(AVG(percent_new), 1) FROM ({$sql}) AS stuff");
$averages = $stmt->fetch();
return makeHtmlTable('First time speakers', $header, $rows, $averages);
}
private function execute($sql, indirizzi $indirizzi)
{
$statement = $this->getDb()->prepare($sql);
$this->executeStatement($statement, $this->getParams($indirizzi));
if (!$indirizzi->getID()) {
return $this->findById($this - getDb()->lastInsertId());
}
if (!$statement->rowCount()) {
throw new NotFoundException('indirizzi with ID "' . $indirizzi->getID() . ' "does not exist.');
}
return $indirizzi;
}
function makeFirstAppearanceIndex()
{
$conn = getDb();
$conn->transactional(function (Connection $conn) {
$conn->executeQuery("DELETE FROM first_appearance");
$result = $conn->executeQuery("SELECT DISTINCT speaker FROM talk");
$stmt = $conn->prepare("INSERT INTO first_appearance\n SELECT talk.speaker as speaker, event.uri as event_uri, event.start_date as event_date, event.name as event_name\n FROM event\n INNER JOIN talk ON event.uri = talk.event_uri\n WHERE talk.speaker = :name\n ORDER BY event.start_date\n LIMIT 1");
foreach ($result as $record) {
$stmt->execute(['name' => $record['speaker']]);
}
});
}
function delete($delete_no)
{
try {
$db = getDb();
$stt = $db->prepare("delete from bbs_data where no = :delete_no and user_id = :user_id");
$stt->bindValue(':delete_no', $delete_no);
$stt->bindValue(':user_id', $_SESSION['user_id']);
$stt->execute();
} catch (Exception $e) {
die("エラーメッセージ:{$e->getMessage()}");
}
}
public function getNbProduits()
{
if (!isset($this->nbProduits[$this->magasin][$this->date])) {
$query = getDb()->prepare("SELECT COUNT(*) FROM produits_encaisses\n\t\t\t\tWHERE date = ? AND magasin = ?");
$query->execute(array($this->date, $this->magasin));
$res = $query->fetch();
$this->nbProduits[$this->magasin][$this->date] = $res["COUNT(*)"];
return $res["COUNT(*)"];
} else {
return $this->nbProduits[$this->magasin][$this->date];
}
}
/**
* Update a group
*
* @param string $id id of the group to update
* @return string Standard JSON envelope
*/
public function postGroup($id = null)
{
getAuthentication()->requireAuthentication();
if (!$id) {
$id = $this->user->getNextId('group');
}
$res = getDb()->postGroup($id, $_POST);
if ($res) {
return $this->success("Group {$id} was updated", array_merge(array('id' => $id), $_POST));
} else {
return $this->error("Could not updated group {$id}", false);
}
}
function recuperer_musique_valider($limite)
{
if ($limite > 0) {
$request = getDb()->prepare("SELECT Titre, Piste, IdAlbum, IdMusique FROM musique WHERE estValide = 1 LIMIT :limit");
$request->bindParam(':limit', $limite, PDO::PARAM_INT);
$request->execute();
return $request->fetchAll(PDO::FETCH_ASSOC);
} else {
$request = getDb()->prepare("SELECT Titre, Piste, IdAlbum, IdMusique FROM musique WHERE estValide = 1");
$request->execute();
return $request->fetchAll(PDO::FETCH_ASSOC);
}
}
function register_data($user_id, $password)
{
try {
$db = getDb();
$stt = $db->prepare('insert into login(user_id, password) values(:user_id, :password)');
$stt->bindvalue(':user_id', $user_id);
$stt->bindvalue(':password', $password);
$stt->execute();
$db = NULL;
} catch (Exception $e) {
die("エラーメッセージ:{$e->getMessage()}");
}
}
/**
* Initializes the database with a fresh, empty schema.
*/
function init()
{
$conn = getDb();
/** @var \Doctrine\DBAL\Schema\AbstractSchemaManager $sm */
$sm = $conn->getSchemaManager();
if ($sm->tablesExist('talk')) {
$sm->dropTable('talk');
}
if ($sm->tablesExist('first_appearance')) {
$sm->dropTable('first_appearance');
}
$schema = new Schema();
$table = $schema->createTable('event');
$table->addColumn("uri", "string", ["length" => 128]);
$table->addColumn("url_friendly_name", "string", ["length" => 128]);
$table->addColumn("name", "string", ["length" => 128]);
$table->addColumn("start_date", "date");
$table->addColumn("end_date", "date");
$table->addColumn("tz_continent", "string", ["length" => 64]);
$table->addColumn("tz_place", "string", ["length" => 64]);
$table->addColumn("location", "string", ["length" => 64]);
$table->addColumn("talks_count", "integer", ["unsigned" => true]);
$table->addColumn('num_speakers', 'integer', ['unsigned' => true]);
$table->addColumn('new_speakers', 'integer', ['unsigned' => true]);
$table->setPrimaryKey(["uri"]);
// $table->addUniqueIndex(["username"]);
// $schema->createSequence("users_seq");
$sm->dropAndCreateTable($table);
$schema = new Schema();
$table = $schema->createTable('talk');
$table->addColumn("uri", "string", ["length" => 128]);
$table->addColumn("url_friendly_talk_title", "string", ["length" => 128]);
$table->addColumn("event_uri", "string", ["length" => 128]);
$table->addColumn("talk_title", "string", ["length" => 128]);
$table->addColumn("type", "string", ["length" => 128]);
$table->addColumn("duration", "integer", ["unsigned" => true]);
$table->addColumn("speaker", "string", ["length" => 128]);
$table->addColumn("average_rating", "integer", ["unsigned" => true]);
$table->setPrimaryKey(["uri"]);
$table->addForeignKeyConstraint('event', ['event_uri'], ['uri']);
$sm->dropAndCreateTable($table);
$schema = new Schema();
$table = $schema->createTable('first_appearance');
$table->addColumn("speaker", "string", ["length" => 128]);
$table->addColumn("event_uri", "string", ["length" => 128]);
$table->addColumn("event_date", "date");
$table->addColumn("event_name", "string", ["length" => 128]);
$table->setPrimaryKey(["speaker"]);
$table->addForeignKeyConstraint('event', ['event_uri'], ['uri']);
$sm->dropAndCreateTable($table);
}
public function __construct()
{
$this->api = getApi();
$this->config = getConfig()->get();
$this->logger = getLogger();
$this->route = getRoute();
$this->session = getSession();
$this->cache = getCache();
// really just for setup when the systems don't yet exist
if (isset($this->config->systems)) {
$this->db = getDb();
$this->fs = getFs();
}
}
public function home()
{
$db = getDb();
$fs = getFs();
$params = array();
$params['systems'] = array('FileSystem' => $this->config->systems->fileSystem);
$params['aws'] = array('bucket' => $this->config->aws->s3BucketName);
$params['email'] = $this->config->user->email;
//$params['diagnostics'] = array('db' => $db->diagnostics(), 'fs' => $fs->diagnostics());
$params['navigation'] = $this->manageController->getNavigation('account');
$bodyTemplate = sprintf('%s/account.php', $this->config->paths->templates);
$body = $this->template->get($bodyTemplate, $params);
$this->theme->display('template.php', array('body' => $body, 'page' => 'account'));
}
function insertion_musique($titre, $chemin, $idAlbum)
{
try {
getDb()->beginTransaction();
$request = getDb()->prepare("INSERT INTO `" . DB_NAME . "`.`musique` (`IdMusique`, `Titre`, `Piste`, `IdAlbum`) VALUES (NULL, :titre, :piste, :idAlbum);");
$request->bindParam(':titre', $titre, PDO::PARAM_STR);
$request->bindParam(':piste', $chemin, PDO::PARAM_STR);
$request->bindParam(':idAlbum', $idAlbum, PDO::PARAM_STR);
$request->execute();
getDb()->commit();
} catch (PDOException $ex) {
getDb()->rollback();
return $ex;
}
}
function writeData()
{
global $user;
global $message;
try {
$db = getDb();
$stt = $db->prepare('INSERT INTO bbs_data(user,message) VALUES (:user, :message)');
$stt->bindValue(':user', $user);
$stt->bindValue(':message', $message);
$stt->execute();
$db = NULL;
} catch (Exception $e) {
die("エラーメッセージ:{$e->getMessage()}");
}
}
public function __construct($config = null, $params = null)
{
if (is_null($config)) {
$this->config = getConfig()->get();
} else {
$this->config = $config;
}
if (!is_null($params) && isset($params['db'])) {
$this->db = $params['db'];
} else {
$this->db = getDb();
}
$this->root = $this->config->localfs->fsRoot;
$this->host = $this->config->localfs->fsHost;
}
/**
* Activate-Method.
* @return bool
*/
public static function onActivate()
{
$bReturn = false;
try {
$bReturn = (bool) getDb()->Execute('ALTER TABLE oxdelivery ' . 'ADD forb3nislandaddition TINYINT( 1 ) NOT NULL DEFAULT "0" ' . 'COMMENT "Is this delivery used for the island delivery?", ' . 'ADD INDEX (forb3nislandaddition)');
if (class_exists('oxDbMetaDataHandler')) {
oxNew('oxDbMetaDataHandler')->updateViews();
}
// if
} catch (Exception $oExc) {
// silent ignore.
}
// catch
return $bReturn;
}
