Example #1
0
 /**
  * Obtener las cuentas de una búsqueda.
  *
  * @return bool Resultado de la consulta
  */
 public function getAccounts()
 {
     $isAdmin = Session::getUserIsAdminApp() || Session::getUserIsAdminAcc();
     $arrFilterCommon = array();
     $arrFilterSelect = array();
     $arrFilterUser = array();
     $arrQueryWhere = array();
     if ($this->_txtSearch) {
         // Analizar la cadena de búsqueda por etiquetas especiales
         $stringFilters = $this->analyzeQueryString();
         if ($stringFilters !== false) {
             $i = 0;
             foreach ($stringFilters as $column => $value) {
                 $parameter = 'P_' . $column . $i;
                 $rel = '=';
                 if (preg_match('/name/i', $column)) {
                     $rel = 'LIKE';
                     $value = '%' . $value . '%';
                 }
                 $arrFilterCommon[] = $column . ' ' . $rel . ' :' . $parameter;
                 $data[$parameter] = $value;
                 $i++;
             }
         } else {
             $arrFilterCommon[] = 'account_name LIKE :name';
             $arrFilterCommon[] = 'account_login LIKE :login';
             $arrFilterCommon[] = 'account_url LIKE :url';
             $arrFilterCommon[] = 'account_notes LIKE :notes';
             $data['name'] = '%' . $this->_txtSearch . '%';
             $data['login'] = '******' . $this->_txtSearch . '%';
             $data['url'] = '%' . $this->_txtSearch . '%';
             $data['notes'] = '%' . $this->_txtSearch . '%';
         }
     }
     if ($this->_categoryId !== 0) {
         $arrFilterSelect[] = 'category_id = :categoryId';
         $data['categoryId'] = $this->_categoryId;
     }
     if ($this->_customerId !== 0) {
         $arrFilterSelect[] = 'account_customerId = :customerId';
         $data['customerId'] = $this->_customerId;
     }
     if (count($arrFilterCommon) > 0) {
         $arrQueryWhere[] = '(' . implode(' OR ', $arrFilterCommon) . ')';
     }
     if (count($arrFilterSelect) > 0) {
         $arrQueryWhere[] = '(' . implode(' AND ', $arrFilterSelect) . ')';
     }
     if (!$isAdmin && !$this->_globalSearch) {
         $subQueryGroupsA = '(SELECT user_groupId FROM usrData WHERE user_id = :userIduA UNION ALL SELECT usertogroup_groupId FROM usrToGroups WHERE usertogroup_userId = :userIdgA)';
         $subQueryGroupsB = '(SELECT user_groupId FROM usrData WHERE user_id = :userIduB UNION ALL SELECT usertogroup_groupId FROM usrToGroups WHERE usertogroup_userId = :userIdgB)';
         $arrFilterUser[] = 'account_userGroupId IN ' . $subQueryGroupsA;
         $arrFilterUser[] = 'accgroup_groupId IN ' . $subQueryGroupsB;
         $arrFilterUser[] = 'account_userId = :userId';
         $arrFilterUser[] = 'accuser_userId = :accuser_userId';
         // Usuario/Grupo principal de la cuenta
         $data['userId'] = Session::getUserId();
         $data['accuser_userId'] = Session::getUserId();
         $data['userIduA'] = Session::getUserId();
         $data['userIduB'] = Session::getUserId();
         $data['userIdgA'] = Session::getUserId();
         $data['userIdgB'] = Session::getUserId();
         // Usuario/Grupo secundario de la cuenta
         //            $data['userGroupId'] = Session::getUserGroupId();
         //            $data['accgroup_groupId'] = Session::getUserGroupId();
         $arrQueryWhere[] = '(' . implode(' OR ', $arrFilterUser) . ')';
     }
     if ($this->_limitCount != 99) {
         $queryLimit = 'LIMIT :limitStart,:limitCount';
         $data['limitStart'] = $this->_limitStart;
         $data['limitCount'] = $this->_limitCount;
     }
     if (count($arrQueryWhere) === 1) {
         $queryWhere = ' WHERE ' . implode($arrQueryWhere);
     } elseif (count($arrQueryWhere) > 1) {
         $queryWhere = ' WHERE ' . implode(' AND ', $arrQueryWhere);
     } else {
         $queryWhere = '';
     }
     $query = 'SELECT DISTINCT ' . 'account_id,' . 'account_customerId,' . 'category_name,' . 'account_name,' . 'account_login,' . 'account_url,' . 'account_notes,' . 'account_userId,' . 'account_userGroupId,' . 'BIN(account_otherUserEdit) AS account_otherUserEdit,' . 'BIN(account_otherGroupEdit) AS account_otherGroupEdit,' . 'usergroup_name,' . 'customer_name,' . 'count(accfile_id) as num_files ' . 'FROM accounts ' . 'LEFT JOIN accFiles ON account_id = accfile_accountId ' . 'LEFT JOIN categories ON account_categoryId = category_id ' . 'LEFT JOIN usrGroups ug ON account_userGroupId = usergroup_id ' . 'LEFT JOIN customers ON customer_id = account_customerId ' . 'LEFT JOIN accUsers ON accuser_accountId = account_id ' . 'LEFT JOIN accGroups ON accgroup_accountId = account_id ' . $queryWhere . ' ' . 'GROUP BY account_id ' . $this->getOrderString() . ' ' . $queryLimit;
     //        print_r($query);
     //        error_log($query);
     // Obtener el número total de cuentas visibles por el usuario
     DB::setFullRowCount();
     // Obtener los resultados siempre en array de objetos
     DB::setReturnArray();
     // Consulta de la búsqueda de cuentas
     $queryRes = DB::getResults($query, __FUNCTION__, $data);
     if ($queryRes === false) {
         //            print_r($query);
         //            var_dump($data);
         return false;
     }
     // Obtenemos el número de registros totales de la consulta sin contar el LIMIT
     self::$queryNumRows = DB::$lastNumRows;
     // Establecer el filtro de búsqueda en la sesión como un objeto
     Session::setSearchFilters($this);
     return $queryRes;
 }