public static function getInstance()
{
if (self::$instance === null) {
self::$instance = new self();
}
return self::$instance;
}
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;
}
public function init()
{
$this->setTable(Gpf_Db_Table_LoginsHistory::getInstance());
parent::init();
}
