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];
}
