protected function buildFrom() { $this->_selectBuilder->from->add(Gpf_Db_Table_LoginsHistory::getName(), 'l'); $this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'u', 'l.accountuserid=u.accountuserid'); $this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'u.authid=au.authid'); $this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Roles::getName(), 'r', 'r.roleid=u.roleid'); }
private function deleteLoginshistory() { $delete = new Gpf_SqlBuilder_DeleteBuilder(); $delete->from->add(Gpf_Db_Table_LoginsHistory::getName()); $delete->where->add(Gpf_Db_Table_LoginsHistory::LAST_REQUEST, '<', $this->getLastDate(Gpf_Settings::get(Gpf_Settings_Gpf::AUTO_DELETE_LOGINSHISTORY))->toDateTime()); $delete->limit->set('', Gpf_Log_DeleteTaskBase::DELETE_LIMIT); return $delete->delete(); }
/** * Return count of online users per role * * @service online_user read * @param Gpf_Rpc_Params $params */ public function getOnlineRolesCount(Gpf_Rpc_Params $params) { $sql = new Gpf_SqlBuilder_SelectBuilder(); $sql->from->add(Gpf_Db_Table_Roles::getName(), 'r'); $sql->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'u', 'u.roleid=r.roleid'); $sql->from->addInnerJoin(Gpf_Db_Table_LoginsHistory::getName(), 'l', 'l.accountuserid=u.accountuserid'); $sql->select->add(Gpf_Db_Table_Roles::NAME, Gpf_Db_Table_Roles::NAME, 'r'); $sql->select->add('count(*)', 'usersCount'); $sql->where->add(Gpf_Db_Table_LoginsHistory::LOGOUT, 'is', 'NULL', 'AND', false); $sql->where->add(Gpf_Db_Table_LoginsHistory::LAST_REQUEST, '>', "('" . $this->createDatabase()->getDateString() . "' - INTERVAL 1800 SECOND)", 'AND', false); Gpf_Plugins_Engine::extensionPoint('Gpf_Report_OnlineUsersGadget.getOnlineRolesCount', $sql->where); $sql->groupBy->add('r.' . Gpf_Db_Table_Roles::ID); return $this->translateRoleNames($sql->getAllRows()); }
/** * Load Location of last 20 IP addresses of logins of selected affiliate * * @service online_user read * @return Gpf_Data_RecordSet */ public function getAffiliateLogins(Gpf_Rpc_Params $params) { $sql = new Gpf_SqlBuilder_SelectBuilder(); $sql->select->add('l.'.Gpf_Db_Table_LoginsHistory::IP); $sql->select->add('MAX(l.'.Gpf_Db_Table_LoginsHistory::LOGIN . ')', 'login'); $sql->from->add(Gpf_Db_Table_LoginsHistory::getName(), 'l'); $sql->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'u', 'l.accountuserid=u.accountuserid'); $sql->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'u.authid=au.authid'); $sql->where->add(Gpf_Db_Table_AuthUsers::USERNAME, '=', $params->get('username')); $sql->where->add('l.'.Gpf_Db_Table_LoginsHistory::IP, '<>', '127.0.0.1'); $sql->orderBy->add(Gpf_Db_Table_LoginsHistory::LOGIN, false); $sql->groupBy->add('l.'.Gpf_Db_Table_LoginsHistory::IP); $sql->limit->set(0, 20); $recordset = $sql->getAllRows(); $recordset->addColumn('countryCode', ''); $recordset->addColumn('countryName', ''); $recordset->addColumn('city', ''); $recordset->addColumn('latitude', ''); $recordset->addColumn('longitude', ''); $recordset->addColumn('postalCode', ''); $recordset->addColumn('region', ''); foreach ($recordset as $record) { $location = new GeoIp_Location(); $location->setIpString($record->get('ip')); $location->load(); $record->set('countryCode', $location->getCountryCode()); $record->set('countryName', $location->getCountryName()); $record->set('city', $location->getCity()); $record->set('latitude', $location->getLatitude()); $record->set('longitude', $location->getLongitude()); $record->set('postalCode', $location->getPostalCode()); $record->set('region', $location->getRegion()); $record->set('login', Gpf_Common_DateUtils::getHumanRelativeTime(Gpf_Common_DateUtils::getClientTime( Gpf_Common_DateUtils::mysqlDateTime2Timestamp($record->get('login'))))); } return $recordset; }
protected function buildFrom() { $this->_selectBuilder->from->add(Pap_Db_Table_Users::getName(), 'u'); $this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'gu', 'u.accountuserid=gu.accountuserid AND u.' . Pap_Db_Table_Users::DELETED . ' = \'' . Gpf::NO . '\''. ' AND u.' .Pap_Db_Table_Users::TYPE . ' = \'' . Pap_Application::ROLETYPE_AFFILIATE . '\''); $this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'au.authid=gu.authid'); $this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Users::getName(), 'pu', 'u.parentuserid=pu.userid'); $this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_Users::getName(), 'pgu', 'pu.accountuserid=pgu.accountuserid'); $this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_AuthUsers::getName(), 'pau', 'pau.authid=pgu.authid'); $this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_FieldGroups::getName(), 'pay', 'pay.fieldgroupid=u.payoutoptionid AND pay.rtype=\'P\' AND pay.rstatus=\'' . Gpf_Db_FieldGroup::ENABLED . '\''); if ($this->areColumnsRequiredOrInFilter(array('lastlogin', 'loginscount', 'loginsnr'))) { $select = new Gpf_SqlBuilder_SelectBuilder(); $select->select->add('MAX(login)', 'lastlogin'); $select->select->add('accountuserid'); $select->select->add('COUNT(*)', 'loginsnr'); $select->from->add(Gpf_Db_Table_LoginsHistory::getName()); $select->groupBy->add(Gpf_Db_Table_LoginsHistory::ACCOUNTUSERID); $this->_selectBuilder->from->addLeftJoin('('.$select->toString().')', 'lo', 'u.' . Gpf_Db_Table_LoginsHistory::ACCOUNTUSERID . '=lo.'.Gpf_Db_Table_LoginsHistory::ACCOUNTUSERID); } $this->buildStatsFrom(); }
/** * * @service online_user delete * @param ids, status * @return Gpf_Rpc_Action */ public function deleteLogins(Gpf_Rpc_Params $params) { $action = new Gpf_Rpc_Action($params); $date = array(); switch ($action->getParam("status")) { case self::DATE_ONE_WEEK: $filter = new Gpf_SqlBuilder_Filter(array("", "DP", "L7D")); $date = $filter->addDateValueToArray($date); $olderThan = $this->_("one week"); break; case self::DATE_TWO_WEEKS: $dateFrom = Gpf_DbEngine_Database::getDateString(Gpf_Common_DateUtils::getServerTime(mktime(0, 0, 0, date("m"), date("d") - 14, date("Y")))); $date = array("dateFrom" => $dateFrom); $olderThan = $this->_("two weeks"); break; case self::DATE_ONE_MONTH: $filter = new Gpf_SqlBuilder_Filter(array("", "DP", "L30D")); $date = $filter->addDateValueToArray($date); $olderThan = $this->_("one month"); break; } $action->setInfoMessage($this->_("Login history older than %s was deleted", $olderThan)); $action->setErrorMessage($this->_("Failed to delete login history")); $delete = new Gpf_SqlBuilder_DeleteBuilder(); $delete->from->add(Gpf_Db_Table_LoginsHistory::getName()); $delete->where->add(Gpf_Db_Table_LoginsHistory::LAST_REQUEST, "<", $date["dateFrom"]); try { $delete->delete(); $action->addOk(); } catch (Gpf_DbEngine_NoRowException $e) { $action->addError(); } return $action; }