PHP dibi::select方法代碼示例

 public function near($lat, $lon, $distance = 100000)
 {
     $key = 'events' . $lat . $lon . $distance;
     $cache = new Nette\Caching\Cache($this->context->cacheStorage);
     $ids = $cache->load($key);
     $ids = NULL;
     if ($ids === NULL) {
         /* Vytáhnu si všechny koordináty */
         $res = dibi::select('id, lat, lon')->from('subject')->where('deleted = 0')->and('map_coords !=""')->fetchAll();
         /* Zjistím vzdálenost jednotlivých ID od středu */
         $locs = array();
         foreach ($res as $r => $n) {
             $dist = Geolocation::getDistance($lat, $lon, $n['lat'], $n['lon']);
             if ($dist <= $distance && $dist > 1) {
                 $locs[] = array('id' => $n['id'], 'dist' => $dist);
             }
         }
         /* Nestliže nic nenajdu, tak vyhodím chybu. */
         if (count($locs) == 0) {
             throw new DibiException('There is no places', 1);
         }
         foreach ($locs as $key => $row) {
             $ids[$key] = $row['id'];
             $distances[$key] = $row['dist'];
         }
         foreach ($locs as $key => $row) {
             $aa[$row['id']] = $row['dist'];
         }
         array_multisort($distances, SORT_ASC, $ids, SORT_ASC, $locs);
         $cache->save($key, $ids, array(Nette\Caching\Cache::TAGS => array("events", "event", "distance"), Nette\Caching\Cache::EXPIRATION => '4 hours'));
     }
     $this->where('subject_id', $ids);
     return $this;
 }

 static function createTable($table, $group)
 {
     // header
     $header = array();
     $res = dibi::select('COLUMN_COMMENT, COLUMN_NAME')->from('information_schema.COLUMNS')->where('(TABLE_NAME = %s AND COLUMN_NAME = %s)', 'general', 'name')->or('(TABLE_NAME = %s AND COLUMN_NAME = %s)', 'general', 'id')->or('(TABLE_NAME = %s)', $table)->execute();
     $header = $res->fetchAll();
     // Changing header so that it fits the JOINed select
     $header[1]['COLUMN_COMMENT'] = 'Summoner name';
     $header[1]['COLUMN_NAME'] = 'summoner_name';
     // body
     $existing_players = $group->getExistingPlayers();
     $t1_select = dibi::select('summoner_name, id')->as('id_general')->from('general');
     $t2_select = dibi::select("*")->from($table);
     $res = dibi::select('*')->from($t1_select)->as('t1')->innerJoin($t2_select)->as('t2')->on('t1.id_general = t2.id')->where('( id, region) ')->in($existing_players);
     // create column names to check if table can be ordered
     $column_names = array();
     foreach ($header as $column) {
         array_push($column_names, $column["COLUMN_NAME"]);
     }
     // check if ordering is set and according to it, rearrange selection from database
     if (isset($_SESSION["orderby"]) && isset($_SESSION["way"])) {
         if (in_array($_SESSION["orderby"], $column_names)) {
             $res = $res->orderBy($_SESSION["orderby"]);
             if ($_SESSION["way"] == "asc") {
                 $res = $res->asc();
             } else {
                 $res = $res->desc();
             }
         }
     }
     $res = $res->execute();
     $body = $res->fetchAll();
     $table_elements = array($header, $body);
     return $table_elements;
 }

 public function renderDefault()
 {
     //uvod
     $home = dibi::fetch("SELECT * FROM [menu_item] WHERE home = 1 AND lang = %s", $this->lang);
     if (!$home) {
         $home = dibi::fetch("SELECT * FROM [menu_item] WHERE lang = %s", $this->lang, "ORDER BY sequence LIMIT 1");
     }
     $this->template->id_menu_item = $home['id_menu_item'];
     /*
      * META INFO
      */
     $this['header']->addTitle($home['meta_title']);
     $this['header']->setDescription($home['meta_description']);
     $node = $this->getService('Node');
     $query = $node->getAll($this->template->id_menu_item);
     $this->template->node_list = $query->fetchAll();
     //produkty na uvode
     $list = dibi::select('id_product')->from('product')->where('home = 1');
     $count_list = clone $list;
     $count = $count_list->removeClause('select')->select('COUNT(id_product)')->fetchSingle();
     $vp = new VisualPaginator($this, 'paginator');
     $paginator = $vp->getPaginator();
     $paginator->itemsPerPage = 12;
     $paginator->itemCount = (int) $count;
     $this->template->products = $list->limit($paginator->offset . ',' . $paginator->itemsPerPage)->fetchAll();
     $this->template->paginator = $paginator;
     foreach ($this->template->products as $k => $p) {
         $this->template->products[$k] = ProductModel::getProductWithParams($p['id_product'], $this->id_lang, $this->user);
     }
     /* widget */
     $this['productNewsSaleAvaiableTable']->setIdLang($this->id_lang);
     $this['productNewsSaleAvaiableTable']->setUser($this->user);
 }

 /**
  * get owners' ids of packages containing checked file
  *
  * @return array
  */
 public function getOwnersOfPackagesContainingFile()
 {
     if (empty($this->ownersOfPackagesContainingFile)) {
         $this->ownersOfPackagesContainingFile = dibi::select('owner_id')->from(BaseModel::CLIENT_PACKAGES_TABLE)->as('cp')->join(BaseModel::FILES_2_CLIENT_PACKAGES_TABLE)->as('f2cp')->on('f2cp.client_packages_id = cp.id')->where('f2cp.files_id = %i', $this->id)->fetchAssoc('owner_id');
         $this->ownersOfPackagesContainingFile = array_keys($this->ownersOfPackagesContainingFile);
     }
     return $this->ownersOfPackagesContainingFile;
 }

 function beforeRender()
 {
     parent::beforeRender();
     $this->template->id_category = $this->id;
     $this->template->category_parents = CategoryModel::getParents($this->template->id_category, $this->id_lang);
     //ak je iba jeden parent zobraz kategorie, inak zobraz produkty
     if (count($this->template->category_parents) == 1) {
         $id_parent = current($this->template->category_parents);
         $category_list = CategoryModel::getFluent('id_category')->where('id_parent = %i', $id_parent)->fetchAll();
         $this->template->categories = array();
         foreach ($category_list as $l) {
             $_tmp = CategoryModel::get($l->id_category, $this->id_lang);
             $_tmp['product_count'] = dibi::fetchSingle("SELECT COUNT(id_product) FROM [product] JOIN [category_product] USING(id_product) WHERE id_category = %i", $l->id_category);
             $this->template->categories[] = $_tmp;
         }
         $this->template->product_count = dibi::fetchSingle("SELECT COUNT(id_product) FROM [product] JOIN [category_product] USING(id_product) WHERE id_category = %i", $this->id);
     } else {
         $list = dibi::select('id_product')->from('product')->join('category_product')->using('(id_product)')->join('product_param')->using('(id_product)')->where('id_category = ', $this->id, 'AND product.active = 1');
         /*
          * Filter
          */
         $orderSession = $this['quickFilter']->getSession();
         //			dde($orderSession['order']);
         //			$orderSession['order'] = 'price';
         if ($orderSession['order']) {
             $list->orderBy($orderSession['order']);
         } else {
             $order_array = $this['quickFilter']->getOrderFilterArray();
             $list->orderBy(key($order_array));
         }
         $list->groupBy('id_product');
         //			dump($order);
         //		print_r($list);
         $count_list = clone $list;
         //			$count = $count_list->removeClause('select')->select('COUNT(id_product)')->fetchSingle();
         $count = count($count_list);
         $vp = new VisualPaginator($this, 'paginator');
         $paginator = $vp->getPaginator();
         $numOnPageSession = $this['quickFilter']->getSession();
         if ($numOnPageSession['num']) {
             $paginator->itemsPerPage = $numOnPageSession['num'];
         } else {
             $num_on_page_array = $this['quickFilter']->getNumOnPageFilterArray();
             $paginator->itemsPerPage = key($num_on_page_array);
         }
         $paginator->itemCount = (int) $count;
         $this->template->product_count = $count;
         $this->template->products = $list->limit($paginator->offset . ',' . $paginator->itemsPerPage)->fetchAll();
         //dump($this->template->products);
         $this->template->paginator = $paginator;
         foreach ($this->template->products as $k => $p) {
             $this->template->products[$k] = ProductModel::getProductWithParams($p['id_product'], $this->id_lang, $this->user);
         }
         //		};
     }
     //		print_r($this->template->products);exit;
 }

 private function getkey($id)
 {
     if (!isset($this->key_stmt)) {
         $this->key_stmt = \dibi::select('session_key')->from('session')->where('id = %i', $id)->fetchSingle();
     }
     if ($this->key_stmt) {
         return $this->key_stmt;
     } else {
         $random_key = hash('sha512', uniqid(mt_rand(1, mt_getrandmax()), true));
         return $random_key;
     }
 }

 /**
  * Vraci vsechny polozky omezene poctem a smerem
  * @param $offset scalar
  * @param $limit scalar
  * @param $orderBy string
  * @param $sort string
  * @return DibiResult
  */
 public function fetchAll($page = 1, $limit = 15, $orderBy = 'created', $sort = 'desc')
 {
     $filters = $this->prepareFilter($this->session->filter);
     $offset = ($page - 1) * $limit;
     $res = dibi::select('*')->from('[guestbook]');
     //     	$res->where('[deleted]','=','0');
     if ($filters) {
         $res->where('1', $filters);
     }
     $res->orderBy($orderBy, $sort);
     $res->offset($offset);
     $res->limit($limit);
     return $res->fetchAll();
 }

 /**
  * Performs an authentication
  * @param  array
  * @return IIdentity
  * @throws AuthenticationException
  */
 public function authenticate(array $credentials)
 {
     $username = strtolower($credentials[self::USERNAME]);
     $password = strtolower($credentials[self::PASSWORD]);
     $row = dibi::select('*')->from('users')->where('username=%s', $username)->fetch();
     if (!$row) {
         throw new AuthenticationException("User '{$username}' not found.", self::IDENTITY_NOT_FOUND);
     }
     if ($row->password !== $password) {
         throw new AuthenticationException("Invalid password.", self::INVALID_CREDENTIAL);
     }
     unset($row->password);
     return new Nette\Security\Identity($row->id, NULL, $row);
 }

 /**
  * Get one article
  * @param $id
  * @return bool|Row|FALSE
  */
 public function getArticle($id)
 {
     if (!$id) {
         return False;
     }
     try {
         $article = \dibi::select('*')->from('articles')->where('id = %s', $id)->fetch();
         if ($article instanceof Row) {
             return $article;
         } else {
             return False;
         }
     } catch (Exception $e) {
         throw new $e();
     }
 }

 public function fileUploadSuccess(AppForm $form)
 {
     try {
         foreach ($form->values->image as $image) {
             $file = new \File();
             $d = $image->getImageSize();
             if ($d[0] < 300 && $d[1] < 300) {
                 throw new InvalidArgumentException('Obrázek je příliš malý. Musí mít minimálně jeden rozměr vyšší než 300px. Nahrajte prosím větší obrázek.');
             }
             if ($d[0] > 3000 || $d[1] > 3000) {
                 throw new InvalidArgumentException('Obrázek je až zbytečně veliký. Žádný rozměr by neměl být vyšší než 3000px. Nahrajte prosím menší obrázek.');
             }
             $file->file_prefix = "";
             $file->file_sufix = "_u" . $this->context->user->id . "_" . substr(sha1(time()), 0, 4);
             $file->user_id = $this->context->user->id;
             $file->insertUploaded($image, FILESTORAGE_DIR);
             $file->visible = 1;
             // automaticka publikace
             $file->save();
             if ($form->values->type == "event") {
                 dibi::query('INSERT INTO event_x_file', array('event_id' => $form->values->id, 'file_id' => $file->id));
                 $res = dibi::select('*')->from('event_x_file')->where('first', '=', 1)->where('event_id', '=', $form->values->id);
                 if (count($res) == 0) {
                     $res = dibi::select('*')->from('event_x_file')->where('event_id', '=', $form->values->id)->fetch();
                     dibi::query('UPDATE event_x_file SET ', array('first' => 1), 'WHERE event_id = ', $res->event_id, 'AND file_id =', $res->file_id);
                 }
             } elseif ($form->values->type == "place") {
                 dibi::query('INSERT INTO subject_x_file', array('subject_id' => $form->values->id, 'file_id' => $file->id));
                 $res = dibi::select('*')->from('subject_x_file')->where('first', '=', 1)->where('subject_id', '=', $form->values->id);
                 if (count($res) == 0) {
                     $res = dibi::select('*')->from('subject_x_file')->where('subject_id', '=', $form->values->id)->fetch();
                     dibi::query('UPDATE subject_x_file SET ', array('first' => 1), 'WHERE subject_id = ', $res->subject_id, 'AND file_id =', $res->file_id);
                 }
             }
         }
     } catch (Exception $e) {
         $form->addError($e->getMessage());
     }
     if ($form->isSuccess()) {
         $cache = new \Nette\Caching\Cache($this->context->cacheStorage);
         $cache->clean(array('files', 'images', 'file', 'image', $form->values->type, $form->values->type . 's'));
         $this->presenter->flashMessage('Dobrá zpráva, fotografie se podařilo vložit!', 'success');
         $this->redirect('this');
     }
 }

 /**
  * nacita prelozene stringy do slovnika
  * 
  * @param array $supportedLangs pole podporovanych jazykov
  * @param bool $rebuild rebuild dictionary by pulling phrases out of .php and .phtml and updating database
  * @return void
  *
  */
 public function buildDictionary($supportedLangs = NULL, $rebuild = false)
 {
     if (!isset($this->langs) && !$supportedLangs) {
         throw new Exception('nastav podporovane jazyky');
     } elseif ($supportedLangs) {
         $this->langs = $supportedLangs;
     }
     if ($rebuild) {
         $this->findAllStrings();
         // calls buildDictionary() so return
         return;
     }
     $lang = Environment::getVariable('lang');
     $dictionary = Environment::getCache('dictionary');
     if ($dictionary['data'] === null) {
         $data = array();
         foreach ($this->langs as $lang) {
             $data[$lang] = dibi::select("msg_id, IF(msg_{$lang} = '', msg_id, msg_{$lang}) AS msg")->from(self::TABLE)->fetchPairs('msg_id', 'msg');
         }
         $dictionary['data'] = $data;
     }
     $this->dictionary = $dictionary['data'][$lang];
     //		dump($this->dictionary);
 }

 protected function createComponentNavigation($name)
 {
     $nav = new NavigationControl($this, $name);
     $menuItems = dibi::select("id, label_{$this->lang} AS label, nette_link, nette_link_args, current_link, uri")->from('menu')->where('is_visible = 1')->orderBy('sort')->fetchAll();
     foreach ($menuItems as $item) {
         $args = !is_null($item->nette_link_args) ? Basic::string2array(str_replace('%id%', $item->id, $item->nette_link_args)) : array();
         // expand nette link args
         $uri = $item->nette_link !== null ? $this->link($item->nette_link, $args) : $item->uri;
         // nette link or standard URI
         $nav->add($item->label, $uri, $item->current_link);
     }
 }

 public function generateAllTimes($times = 30, $term_id = 0, $limit = 120)
 {
     $limit = new DibiDateTime(strtotime('+' . $limit . ' days', time()));
     dibi::begin();
     if ($term_id) {
         dibi::query('DELETE FROM event_time_all WHERE event_time_id = %i', $term_id);
         // smaze tabulku casu s daným ID termínu
         $res = dibi::select('*, DATEDIFF(event_time.date_to, event_time.date_from) AS diff_dates')->from('event_time')->where('visible', '=', '1')->where('id', '=', $term_id)->fetchAll();
     } else {
         //dibi::query('TRUNCATE TABLE event_time_all'); // smaze celou tabulku casu
         dibi::query('UPDATE event_time SET visible = 0 WHERE (DATEDIFF(event_time.date_from, CURDATE()) < 0 AND DATEDIFF(event_time.date_to, CURDATE()) < 0) OR (DATEDIFF(event_time.date_from, CURDATE()) < 0 AND isnull(`event_time`.`date_to`))');
         // prevede vsechny starsi casy do neaktivni polohy
         $res = dibi::select('*, DATEDIFF(event_time.date_to, event_time.date_from) AS diff_dates')->from('event_time')->where('visible', '=', '1')->fetchAll();
     }
     foreach ($res as $key => $event) {
         $interval = date_diff($event->date_from, date_create());
         if ($interval->days > 0 && $interval->invert) {
             $date_from = $event->date_from;
             //dump('od '.$date_from);
         } else {
             $date_from = new DibiDateTime();
             //dump('od dnes');
         }
         $nextmonday = new DibiDateTime(date('N', strtotime('today')) == 1 ? strtotime('today') : strtotime('next monday', $date_from->getTimestamp()));
         $nexttuesday = new DibiDateTime(date('N', strtotime('today')) == 2 ? strtotime('today') : strtotime('next tuesday', $date_from->getTimestamp()));
         $nextwednesday = new DibiDateTime(date('N', strtotime('today')) == 3 ? strtotime('today') : strtotime('next wednesday', $date_from->getTimestamp()));
         $nextthursday = new DibiDateTime(date('N', strtotime('today')) == 4 ? strtotime('today') : strtotime('next thursday', $date_from->getTimestamp()));
         $nextfriday = new DibiDateTime(date('N', strtotime('today')) == 5 ? strtotime('today') : strtotime('next friday', $date_from->getTimestamp()));
         $nextsaturday = new DibiDateTime(date('N', strtotime('today')) == 6 ? strtotime('today') : strtotime('next saturday', $date_from->getTimestamp()));
         $nextsunday = new DibiDateTime(date('N', strtotime('today')) == 7 ? strtotime('today') : strtotime('next sunday', $date_from->getTimestamp()));
         //dump($date_from);
         if ($event->repeat == 1) {
             if ($event->every_monday == 1) {
                 for ($i = 0; $i < $times * 7; $i = $i + 7) {
                     $day = new DibiDateTime(strtotime('+' . $i . ' days', $nextmonday->getTimestamp()));
                     if ($day->getTimestamp() > $limit->getTimestamp()) {
                         continue;
                     }
                     $query = "INSERT IGNORE INTO event_time_all (event_time_id, date_from, date_to) VALUES ('" . $event->id . "', '" . $day . "', '" . $event->date_to . "'); \r\n";
                     dibi::query($query);
                 }
             }
             if ($event->every_tuesday == 1) {
                 for ($i = 0; $i < $times * 7; $i = $i + 7) {
                     $day = new DibiDateTime(strtotime('+' . $i . ' days', $nexttuesday->getTimestamp()));
                     if ($day->getTimestamp() > $limit->getTimestamp()) {
                         continue;
                     }
                     $query = "INSERT IGNORE INTO event_time_all (event_time_id, date_from, date_to) VALUES ('" . $event->id . "', '" . $day . "', '" . $event->date_to . "'); \r\n";
                     dibi::query($query);
                 }
             }
             if ($event->every_wednesday == 1) {
                 for ($i = 0; $i < $times * 7; $i = $i + 7) {
                     $day = new DibiDateTime(strtotime('+' . $i . ' days', $nextwednesday->getTimestamp()));
                     if ($day->getTimestamp() > $limit->getTimestamp()) {
                         continue;
                     }
                     $query = "INSERT IGNORE INTO event_time_all (event_time_id, date_from, date_to) VALUES ('" . $event->id . "', '" . $day . "', '" . $event->date_to . "'); \r\n";
                     dibi::query($query);
                 }
             }
             if ($event->every_thursday == 1) {
                 for ($i = 0; $i < $times * 7; $i = $i + 7) {
                     $day = new DibiDateTime(strtotime('+' . $i . ' days', $nextthursday->getTimestamp()));
                     if ($day->getTimestamp() > $limit->getTimestamp()) {
                         continue;
                     }
                     $query = "INSERT IGNORE INTO event_time_all (event_time_id, date_from, date_to) VALUES ('" . $event->id . "', '" . $day . "', '" . $event->date_to . "'); \r\n";
                     dibi::query($query);
                 }
             }
             if ($event->every_friday == 1) {
                 for ($i = 0; $i < $times * 7; $i = $i + 7) {
                     $day = new DibiDateTime(strtotime('+' . $i . ' days', $nextfriday->getTimestamp()));
                     if ($day->getTimestamp() > $limit->getTimestamp()) {
                         continue;
                     }
                     $query = "INSERT IGNORE INTO event_time_all (event_time_id, date_from, date_to) VALUES ('" . $event->id . "', '" . $day . "', '" . $event->date_to . "'); \r\n";
                     dibi::query($query);
                 }
             }
             if ($event->every_saturday == 1) {
                 for ($i = 0; $i < $times * 7; $i = $i + 7) {
                     $day = new DibiDateTime(strtotime('+' . $i . ' days', $nextsaturday->getTimestamp()));
                     if ($day->getTimestamp() > $limit->getTimestamp()) {
                         continue;
                     }
                     $query = "INSERT IGNORE INTO event_time_all (event_time_id, date_from, date_to) VALUES ('" . $event->id . "', '" . $day . "', '" . $event->date_to . "'); \r\n";
                     dibi::query($query);
                 }
             }
             if ($event->every_sunday == 1) {
                 for ($i = 0; $i < $times * 7; $i = $i + 7) {
                     $day = new DibiDateTime(strtotime('+' . $i . ' days', $nextsunday->getTimestamp()));
                     if ($day->getTimestamp() > $limit->getTimestamp()) {
                         continue;
                     }
                     $query = "INSERT IGNORE INTO event_time_all (event_time_id, date_from, date_to) VALUES ('" . $event->id . "', '" . $day . "', '" . $event->date_to . "'); \r\n";
                     dibi::query($query);
//.........這裏部分代碼省略.........

 /**
  * get supervisorId from DB
  *
  * @param int resourceId
  * @return int supervisorId
  */
 protected function getDependencies($id)
 {
     return dibi::select('COALESCE(supervisor_id, id)')->from(BaseModel::USERS_TABLE)->where('id = %i', $id)->fetchSingle();
 }

 /**
  * get ownerId from DB
  *
  * @param int resourceId
  * @return int ownerId
  */
 protected function getDependencies($id)
 {
     return dibi::select('owner_id')->from(BaseModel::LIGHTBOXES_TABLE)->where('id = %i', $id)->fetchSingle();
 }