/** * 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; }