public function testToArrayCreatesArrayOfArraysRepresentingRows() { $count = rand(3, 75); $dataSource = $this->getArrayDataSource($count); $this->resultSet->initialize($dataSource); $test = $this->resultSet->toArray(); $this->assertEquals($dataSource->getArrayCopy(), $test, var_export($test, 1)); }
/** * Get unused categories * * @param integer $limit * @return array */ public function getUnusedCategories($limit) { $select = $this->select(); $select->from('slideshow_category')->columns(['id', 'name'])->limit($limit)->where->and->isNull('language'); $statement = $this->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); return $resultSet->toArray(); }
/** * Get all empty transactions * * @param integer $limit * @return array */ public function getEmptyTransactions($limit) { $predicate = new Predicate(); $select = $this->select(); $select->from(['a' => 'payment_transaction_list'])->columns(['id', 'slug'])->join(['b' => 'payment_transaction_item'], 'a.id = b.transaction_id', [], 'left')->where([$predicate->isNull('b.transaction_id')])->group('a.id')->limit($limit); $statement = $this->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); return $resultSet->toArray(); }
/** * Get all expired memberships connections * * @param integer $limit * @return array */ public function getExpiredMembershipsConnections($limit) { $predicate = new Predicate(); $select = $this->select(); $select->from(['a' => 'membership_level_connection'])->columns(['id', 'user_id'])->join(['b' => 'membership_level'], 'a.membership_id = b.id', ['role_id'], 'left')->join(['c' => 'user_list'], 'a.user_id = c.user_id', ['nick_name', 'email', 'language'])->join(['d' => 'acl_role'], 'd.id = b.role_id', ['role_name' => 'name'], 'left')->limit($limit)->where(['a.active' => self::MEMBERSHIP_LEVEL_CONNECTION_ACTIVE, $predicate->lessThanOrEqualTo('a.expire_date', time())])->where->or->equalTo('a.active', self::MEMBERSHIP_LEVEL_CONNECTION_ACTIVE)->and->isNull('a.membership_id'); $statement = $this->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); return $resultSet->toArray(); }
protected function sequence($sequence) { return $this->generateCode(function () use($sequence) { $query = $this->getTableGateway()->adapter->getDriver()->createStatement("SELECT currval('{$sequence}') AS retorno")->execute(); $resultSet = new ResultSet(); $resultSet->initialize($query); $retorno = $resultSet->toArray(); return $retorno[0]['retorno']; }); }
public function toArray() { if ($this->dataSource instanceof Result) { return $this->dataSource->getResource()->fetchAll(\PDO::FETCH_ASSOC); } // todo: реализовать подгрузку mysqli значений // if ($this->dataSource instanceof \Zend\Db\Adapter\Driver\Mysqli\Result) { // $this->dataSource->getResource() // } return parent::toArray(); }
public function select($where) { $sql = $this->tableGateway->getSql(); $select = $this->tableGateway->getSql()->select(); $select->where($where); $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); $resultSet = new ResultSet(); $resultSet->initialize($results); return $resultSet->toArray(); }
public function getIntitulesConditionReglement($sm) { $query = "SELECT intitule_condition_reglement\n FROM condition_reglement"; $statement = $sm->get('Zend\\Db\\Adapter\\Adapter')->query($query); $results = $statement->execute(); if ($results->isQueryResult()) { $resultSet = new ResultSet(); $resultSet->initialize($results); return $resultSet->toArray(); } return array(); }
public function getEligibleDispoRecipient() { $sql = $this->getObjDbSql(); $select = $sql->select(); $select->columns(array('u_kode', 'u_nama', 'u_kode_fungsional', 'u_gelar_diplomatik', 'u_urutan')); $select->from($this->table); $select->where(array('u_status=1', 'u_urutan > 0')); $select->order('u_urutan ASC'); $statement = $sql->prepareStatementForSqlObject($select); $result = $statement->execute(); $resultSet = new ResultSet(); $resultSet->initialize($result); return $resultSet->toArray(); }
/** * Определить число записей. * @param $adapter (адаптер к базе или таблица) * @param string $sql (если адаптер, то текст запроса, иначе не использовать) * @return integer */ private function getTotal($adapter, $sql = null) { if ($adapter instanceof TableGateway) { $table = $adapter; $select = $table->getSql()->select()->columns(array("count" => new Expression("count(*)"))); $totalSet = $table->selectWith($select); } else { $statement = $adapter->createStatement($sql); $totalSet = new ResultSet(); $totalSet->initialize($statement->execute()); } $total = $totalSet->toArray(); $total = $total[0]["count"]; return (int) $total; }
/** * Get all countries. * * @param string|null $continentCode The continent code * @return array */ public function getAll($continentCode = null) { $sql = new Sql($this->adapter); $select = $sql->select('iso_3166_1'); if (null !== $continentCode) { $select->where(array('continent_alpha_2' => $continentCode)); } $statement = $sql->prepareStatementForSqlObject($select); $result = $statement->execute(); if ($result instanceof ResultInterface && $result->isQueryResult()) { $resultSet = new ResultSet(); $resultSet->initialize($result); return $resultSet->toArray(); } return array(); }
/** * Get all classes * * @return array */ public function getClasses() { // generate cache name $cacheName = CacheUtility::getCacheName(self::CACHE_XMLRPC_CLASSES); // check data in cache if (null === ($classes = $this->staticCacheInstance->getItem($cacheName))) { $select = $this->select(); $select->from(['a' => 'xmlrpc_class'])->columns(['namespace', 'path', 'module'])->join(['b' => 'application_module'], new Expression('a.module = b.id and b.status = ?', [self::MODULE_STATUS_ACTIVE]), []); $statement = $this->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); $classes = $resultSet->toArray(); // save data in cache $this->staticCacheInstance->setItem($cacheName, $classes); $this->staticCacheInstance->setTags($cacheName, [self::CACHE_XMLRPC_DATA_TAG]); } return $classes; }
/** * Get default active layouts * * @return array */ public function getDefaultActiveLayouts() { // generate cache name $cacheName = CacheUtility::getCacheName(self::CACHE_LAYOUTS_ACTIVE); // check data in cache if (null === ($layouts = $this->staticCacheInstance->getItem($cacheName))) { $defaultActiveCustomLayout = (int) SettingService::getSetting('layout_active'); $select = $this->select(); $select->from('layout_list')->columns(['name'])->order('type')->where(['type' => self::LAYOUT_TYPE_SYSTEM]); if ($defaultActiveCustomLayout) { $select->where->or->equalTo('id', $defaultActiveCustomLayout)->and->equalTo('type', self::LAYOUT_TYPE_CUSTOM); } $statement = $this->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); $layouts = $resultSet->toArray(); // save data in cache $this->staticCacheInstance->setItem($cacheName, $layouts); $this->staticCacheInstance->setTags($cacheName, [self::CACHE_LAYOUTS_DATA_TAG]); } return $layouts; }
/** * Get footer menu * * @param string $language * @return array */ public function getFooterMenu($language) { $cacheName = CacheUtility::getCacheName(self::CACHE_FOOTER_MENU . $language); // check data in a cache if (null === ($footerMenu = $this->staticCacheInstance->getItem($cacheName))) { // get the footer menu $select = $this->select(); $select->from(['a' => 'page_structure'])->columns(['slug', 'title', 'type'])->join(['b' => 'page_system'], 'b.id = a.system_page', ['system_title' => 'title'], 'left')->where(['a.footer_menu' => PageNestedSet::PAGE_IN_FOOTER_MENU, 'a.language' => $language])->order('a.footer_menu_order'); $statement = $this->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); $footerMenu = $resultSet->toArray(); // save data in cache $this->staticCacheInstance->setItem($cacheName, $footerMenu); $this->staticCacheInstance->setTags($cacheName, [self::CACHE_PAGES_DATA_TAG]); } return $footerMenu; }
public function getProduitsFinisId($sm) { $id = $this->getId(); if ($id) { $query = "SELECT ref_produit_fini\n FROM client_a_produit_fini\n WHERE ref_client = {$id}"; $statement = $sm->get('Zend\\Db\\Adapter\\Adapter')->query($query); $results = $statement->execute(); if ($results->isQueryResult()) { $resultSet = new ResultSet(); $resultSet->initialize($results); return $resultSet->toArray(); } } return array(); }
public function getCentresProfit($sm) { $query = "SELECT id, CONCAT_WS('-',numero, intitule_centre) as intitule_centre FROM centre_de_profit ORDER BY intitule_centre ASC "; $statement = $sm->get('Zend\\Db\\Adapter\\Adapter')->query($query); $results = $statement->execute(); if ($results->isQueryResult()) { $resultSet = new ResultSet(); $resultSet->initialize($results); return $resultSet->toArray(); } return array(); }
/** * @param $id * @param null $group * @return array */ public function getLabels($id, $group = null) { $sql = $this->sql(); $select = $sql->select()->from('sys_entity_labels')->where(['entity_id' => $id])->columns(['_label_group', '_label_name']); if ($group) { $select->where(['_label_group' => $group]); } $statement = $sql->prepareStatementForSqlObject($select); $result = $statement->execute(); $resultSet = new ResultSet(); $resultSet->initialize($result); return $resultSet->toArray(); }
/** * Permet d'afficher seulement les informations présentes sur l'écran de recherche Interlocuteur (qui sont également les critères) * @author Ophélie * @param ServiceLocator $sm * @param array $critere * @since 1.0 * @return array */ public function getInterlocuteurs($sm, $critere, $limit = 100) { $sql = new Sql($sm->get('Zend\\Db\\Adapter\\Adapter')); $select = $sql->select(); $select->columns(array('id', 'nom_complet' => new Expression("CONCAT_WS(' ',titre_civilite,prenom,nom)"), 'email', 'envoi_vers_outlook'))->from(array('i' => 'interlocuteur_fournisseur'))->join(array('f' => 'fournisseur'), 'i.ref_societe_fournisseur = f.id', array('code_fournisseur', 'raison_sociale'), $select::JOIN_LEFT)->order('nom ASC, prenom ASC')->limit($limit); $where = new Where(); $where->equalTo('f.supprime', 0)->and->nest()->like('i.prenom', $critere)->or->like('i.nom', $critere)->or->like('f.code_fournisseur', $critere)->or->like('f.raison_sociale', $critere)->or->like('i.email', $critere)->unnest(); $select->where($where); //\Zend\Debug\Debug::dump($select->getSqlString());die(); $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); if ($results->isQueryResult()) { $resultSet = new ResultSet(); $resultSet->initialize($results); return $resultSet->toArray(); } return array(); }
/** * Get all active users * * @return array */ public function getAllActiveUsers() { // generate a cache name $cacheName = CacheUtility::getCacheName(self::CACHE_ACTIVE_USERS); // check data in cache if (null === ($users = $this->staticCacheInstance->getItem($cacheName))) { $select = $this->select(); $select->from('user_list')->columns(['user_id', 'nick_name', 'slug', 'date_edited'])->where(['status' => self::STATUS_APPROVED]); $statement = $this->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); $users = $resultSet->toArray(); // save data in cache $this->staticCacheInstance->setItem($cacheName, $users); $this->staticCacheInstance->setTags($cacheName, [self::CACHE_USER_DATA_TAG]); } return $users; }
/** * Return an json version of the resultset * @return string Json encoded version */ public function toJson() { return json_encode($this->dataSource->toArray()); }
/** * Execute the given query and return the result as an array of arrays * * @param PreparableSqlInterface $query Query to be executed * @return array */ protected function executeAndGetResultsAsArray(PreparableSqlInterface $query) { $statement = $this->getSqlObject()->prepareStatementForSqlObject($query); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); return $resultSet->toArray(); }
public function getIntitulesProduits($sm, $locale, $code = null, $intitule = null, $limit = null) { if ($locale == 'en_US') { $lang = 'en'; } else { $lang = 'fr'; } $query = "SELECT p.id, p.code_produit, t.{$lang} as intitule_produit\n FROM produit AS p\n LEFT JOIN traduction AS t\n ON p.ref_intitule_produit = t.id "; if (!is_null($code)) { $query .= " WHERE p.code_produit LIKE '{$code}%' "; } else { if (!is_null($intitule)) { $query .= " WHERE t.{$lang} LIKE '{$intitule}%' "; } } if (!is_null($limit)) { $query .= " LIMIT {$limit} "; } $statement = $sm->get('Zend\\Db\\Adapter\\Adapter')->query($query); $results = $statement->execute(); if ($results->isQueryResult()) { $resultSet = new ResultSet(); $resultSet->initialize($results); return $resultSet->toArray(); } }
public function getNotifyData($flag, $uid) { $adapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter'); $flag = trim($flag); $uid = trim($uid); if ($flag == 'cnt') { $stmt = $adapter->createStatement("SELECT count(user_notification_history.user_id) as cnt\n FROM notification_master LEFT JOIN user_notification_history ON notification_master.notify_id=user_notification_history.notify_id where notification_master.end_date >= CURDATE()\n and user_notification_history.is_read='no' and user_notification_history.user_id={$uid}"); } if ($flag == "all") { $stmt = $adapter->createStatement("SELECT notification_master.*, user_notification_history.*\n FROM notification_master LEFT JOIN user_notification_history ON notification_master.notify_id=user_notification_history.notify_id where notification_master.end_date >= CURDATE()\n and user_notification_history.user_id={$uid} order by user_notification_history.is_read"); } $stmt->prepare(); $result = $stmt->execute(); $resultSet = new ResultSet(); $resultSet->initialize($result); $data = $resultSet->toArray(); if ($flag == "all") { foreach ($data as $key => $value) { $data[$key]['start_date'] = $this->Timesince($value['start_date']); } } return $data; }
public function getStagingActivity() { $proActivity = new ProductionActivity(); $adapter = $this->getServiceLocator()->get('stagingDB'); $query = "SELECT * FROM activities"; $statement = $adapter->createStatement($query); $result = $statement->execute(); if ($result instanceof ResultInterface && $result->isQueryResult()) { $resultSet = new ResultSet(); $resultSet->initialize($result); $activityList = $resultSet->toArray(); foreach ($activityList as $key => $activity) { $proActivity->exchangeArray($activity); $trackerInfo = $this->getActivityTable()->saveActivity($proActivity); } } }
public function getListeDevis($sm) { $id = (int) $this->getId(); $query = "SELECT id, code_devis, version, date_devis, date_envoi, date_signature, remarques\n FROM devis\n WHERE ref_affaire = {$id}\n ORDER BY version ASC "; $statement = $sm->get('Zend\\Db\\Adapter\\Adapter')->query($query); $results = $statement->execute(); if ($results->isQueryResult()) { $resultSet = new ResultSet(); $resultSet->initialize($results); return $resultSet->toArray(); } return array(); }
public function getFournisseurssByActivitiesAndCategories($sm, $activites = null, $categories = null, $motCle = null) { $query = "SELECT f.id, f.code_fournisseur, f.raison_sociale, a.code_postal, a.ville, a.pays \n FROM fournisseur AS f \n LEFT JOIN adresse AS a \n ON a.ref_fournisseur = f.id \n WHERE f.supprime = 0 AND ( a.adresse_principale = 1 OR a.adresse_principale IS NULL ) "; if (!is_null($activites)) { $idActivites = implode(',', $activites); $query .= " AND f.ref_activite IN ({$idActivites}) "; } if (!is_null($categories)) { $idCategories = implode(',', $categories); $query .= " AND f.ref_categorie IN ({$idCategories}) "; } if (!is_null($motCle)) { $query .= " AND (f.code_fournisseur LIKE '%{$motCle}%' \n OR f.raison_sociale LIKE '%{$motCle}%' \n OR a.code_postal LIKE '%{$motCle}%' \n OR a.ville LIKE '%{$motCle}%' \n OR a.pays LIKE '%{$motCle}%')\n "; } $statement = $sm->get('Zend\\Db\\Adapter\\Adapter')->query($query); $results = $statement->execute(); if ($results->isQueryResult()) { $resultSet = new ResultSet(); $resultSet->initialize($results); return $resultSet->toArray(); } return array(); }
/** * Run RAW query and Fetch First row * * @param string $sql * @param string|int $field * @return mixed|null */ public function rawFetchRow($sql, $field = null) { $result = $this->rawQuery($sql); if ($result instanceof ResultSet\ResultSetInterface && $result->count()) { $resultSet = new ResultSet\ResultSet(); $resultSet->initialize($result); $row = $resultSet->toArray(); if (empty($field)) { return $row; } else { return isset($row[0][$field]) ? $row[0][$field] : false; } } else { return false; } }
/** * Retourne les enregistrement de la query donnée au format Array. * Contrairement a la methode fetchAll, si vous ne passer pas de $prototypeClass, * alors il n'y a pas de conversion vers l'entité lié a cette table. * @param \Zend\Db\Sql\Select $oQuery * @param string | null $prototypeClass * @return \Zend\Db\ResultSet\ResultSet */ public function fetchAllArray(\Zend\Db\Sql\Select $oQuery = null, $prototypeClass = null) { if (!$oQuery) { $oQuery = $this->getBaseQuery(); } $oStmt = $this->getSql()->prepareStatementForSqlObject($oQuery); $oRes = $oStmt->execute(); $oResultSet = new ResultSet(); if ($prototypeClass != null) { $oResultSet->setArrayObjectPrototype(new $prototypeClass()); } $oResultSet->initialize($oRes); return $oResultSet->toArray(); }
protected function _authenticateQuerySelect(Select $dbSelect) { $statement = $this->zendDb->createStatement(); $dbSelect->prepareStatement($this->zendDb, $statement); $resultSet = new ResultSet(); try { $resultSet->initialize($statement->execute(array($this->identity))); $resultIdentities = $resultSet->toArray(); } catch (\Exception $e) { throw new Exception\RuntimeException('The supplied parameters to DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.', 0, $e); } return $resultIdentities; }
/** * Get news categories * * @param integer $newsId * @return array */ public function getNewsCategories($newsId) { if (isset(self::$newsCategories[$newsId])) { return self::$newsCategories[$newsId]; } $select = $this->select(); $select->from(['a' => 'news_category_connection'])->columns(['category_id'])->join(['b' => 'news_category'], 'a.category_id = b.id', ['name', 'slug'])->where(['news_id' => $newsId])->order('b.name'); $statement = $this->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); self::$newsCategories[$newsId] = $resultSet->toArray(); return self::$newsCategories[$newsId]; }