public function xListFarmsAction()
{
$this->request->restrictFarmAccess();
$this->request->defineParams(array('clientId' => array('type' => 'int'), 'farmId' => array('type' => 'int'), 'sort' => array('type' => 'json'), 'expirePeriod' => array('type' => 'int')));
$governance = new Scalr_Governance($this->getEnvironmentId());
$leaseStatus = $governance->isEnabled(Scalr_Governance::CATEGORY_GENERAL, Scalr_Governance::GENERAL_LEASE);
$sql = 'SELECT f.clientid, f.id, f.name, f.status, f.dtadded, f.created_by_id, f.created_by_email, ats.name AS team_name, ats.description AS team_description, ats.id as team_id FROM farms f LEFT JOIN account_teams ats ON ats.id = f.team_id WHERE env_id = ? AND :FILTER:';
$args = array($this->getEnvironmentId());
if ($leaseStatus && $this->getParam('expirePeriod')) {
$dt = new DateTime();
$dt->add(new DateInterval('P' . $this->getParam('expirePeriod') . 'D'));
$sql = str_replace('FROM farms f', 'FROM farms f LEFT JOIN farm_settings fs ON f.id = fs.farmid', $sql);
$sql = str_replace('WHERE', 'WHERE fs.name = ? AND fs.value < ? AND fs.value != "" AND f.status = ? AND', $sql);
array_unshift($args, Entity\FarmSetting::LEASE_TERMINATE_DATE, $dt->format('Y-m-d H:i:s'), FARM_STATUS::RUNNING);
}
if ($this->getParam('farmId')) {
$sql .= ' AND f.id = ?';
$args[] = $this->getParam('farmId');
}
if ($this->getParam('clientId')) {
$sql .= ' AND clientid = ?';
$args[] = $this->getParam('clientId');
}
if ($this->getParam('status') != '') {
$sql .= ' AND status = ?';
$args[] = $this->getParam('status');
}
$owner = $this->getParam('owner');
$permission = $this->getParam('manageable') ? Acl::PERM_FARMS_MANAGE : null;
$allowedResourceFarms = $this->request->isAllowed(Acl::RESOURCE_FARMS, $permission);
if (!$allowedResourceFarms || $owner) {
$q = [];
if (($this->request->isAllowed(Acl::RESOURCE_TEAM_FARMS, $permission) || $allowedResourceFarms) && ($owner == '' || $owner == 'team')) {
$t = array_map(function ($t) {
return $t['id'];
}, $this->user->getTeams());
if (count($t)) {
$q[] = 'team_id IN(' . join(',', $t) . ')';
}
}
if (($this->request->isAllowed(Acl::RESOURCE_OWN_FARMS, $permission) || $allowedResourceFarms) && ($owner == '' || $owner == 'me')) {
$q[] = 'created_by_id = ?';
$args[] = $this->request->getUser()->getId();
}
if (count($q)) {
$sql .= ' AND (' . join(' OR ', $q) . ')';
} else {
$sql .= ' AND false';
// no permissions
}
}
if ($this->getParam('chefServerId')) {
$sql .= ' AND f.id IN (
SELECT fr.farmid
FROM farm_roles fr
INNER JOIN farm_role_settings frs1 ON fr.id = frs1.farm_roleid AND frs1.name = ? AND frs1.value = ?
INNER JOIN farm_role_settings frs2 ON fr.id = frs2.farm_roleid AND frs2.name = ? AND frs2.value = ?
)';
$args[] = \Scalr_Role_Behavior_Chef::ROLE_CHEF_SERVER_ID;
$args[] = (int) $this->getParam('chefServerId');
$args[] = \Scalr_Role_Behavior_Chef::ROLE_CHEF_BOOTSTRAP;
$args[] = 1;
}
if ($this->getContainer()->analytics->enabled) {
if ($this->getParam('projectId')) {
$sql .= " AND EXISTS (\n SELECT 1 FROM farm_settings\n WHERE farm_settings.farmid = f.id\n AND farm_settings.name = " . $this->db->qstr(Entity\FarmSetting::PROJECT_ID) . "\n AND farm_settings.value = ?) ";
$args[] = $this->getParam('projectId');
}
}
$response = $this->buildResponseFromSql2($sql, array('id', 'name', 'dtadded', 'created_by_email', 'status', 'team_name'), array('f.name', 'f.id', 'f.comments'), $args);
foreach ($response["data"] as &$row) {
$servers = $this->db->GetRow("\n SELECT SUM(IF(`status` IN (?,?,?,?,?),1,0)) AS running_servers,\n SUM(IF(`status` IN (?,?),1,0)) AS suspended_servers,\n SUM(IF(`status` IN (?,?),1,0)) AS non_running_servers\n FROM `servers` WHERE `farm_id` = ?\n ", [Entity\Server::STATUS_PENDING, Entity\Server::STATUS_INIT, Entity\Server::STATUS_RUNNING, Entity\Server::STATUS_TEMPORARY, Entity\Server::STATUS_RESUMING, Entity\Server::STATUS_SUSPENDED, Entity\Server::STATUS_PENDING_SUSPEND, Entity\Server::STATUS_TERMINATED, Entity\Server::STATUS_PENDING_TERMINATE, $row['id']]);
if (is_null($servers['running_servers'])) {
$servers = ['running_servers' => 0, 'suspended_servers' => 0, 'non_running_servers' => 0];
}
$row = array_merge($row, $servers);
$row["roles"] = $this->db->GetOne("SELECT COUNT(*) FROM farm_roles WHERE farmid='{$row['id']}'");
$row["zones"] = $this->db->GetOne("SELECT COUNT(*) FROM dns_zones WHERE farm_id='{$row['id']}'");
//TODO: Use Alerts class
$row['alerts'] = $this->db->GetOne("SELECT COUNT(*) FROM server_alerts WHERE farm_id='{$row['id']}' AND status='failed'");
$row['dtadded'] = Scalr_Util_DateTime::convertTz($row["dtadded"], 'M j, Y H:i');
$dbFarm = DBFarm::LoadByID($row['id']);
$row['lock'] = $dbFarm->GetSetting(Entity\FarmSetting::LOCK);
if ($row['lock']) {
$row['lock_comment'] = $dbFarm->isLocked(false);
}
if ($leaseStatus && $dbFarm->GetSetting(Entity\FarmSetting::LEASE_STATUS)) {
$row['lease'] = $dbFarm->GetSetting(Entity\FarmSetting::LEASE_NOTIFICATION_SEND) ? 'Expire' : $dbFarm->GetSetting(Entity\FarmSetting::LEASE_STATUS);
if ($row['lease'] == 'Expire') {
$dt = new DateTime();
$td = new DateTime($dbFarm->GetSetting(Entity\FarmSetting::LEASE_TERMINATE_DATE));
$days = 0;
$hours = 1;
$interval = $dt->diff($td);
if ($interval) {
$days = $interval->days;
$hours = $interval->h ? $interval->h : 1;
}
$row['leaseMessage'] = sprintf('Your farm lease is about to expire in %d %s, after which this farm will be terminated', $days ? $days : $hours, $days ? $days > 1 ? 'days' : 'day' : ($hours > 1 ? 'hours' : 'hour'));
}
}
$b = (array) $this->db->GetAll("SELECT DISTINCT(behavior) FROM farm_roles\n INNER JOIN role_behaviors ON role_behaviors.role_id = farm_roles.role_id WHERE farmid = ?", array($row['id']));
$behaviors = array();
foreach ($b as $behavior) {
$behaviors[] = $behavior['behavior'];
}
$row["havemysqlrole"] = in_array(ROLE_BEHAVIORS::MYSQL, $behaviors);
$row["havemysql2role"] = in_array(ROLE_BEHAVIORS::MYSQL2, $behaviors);
$row["havepgrole"] = in_array(ROLE_BEHAVIORS::POSTGRESQL, $behaviors);
$row["haveredisrole"] = in_array(ROLE_BEHAVIORS::REDIS, $behaviors);
$row["haverabbitmqrole"] = in_array(ROLE_BEHAVIORS::RABBITMQ, $behaviors);
$row["havemongodbrole"] = in_array(ROLE_BEHAVIORS::MONGODB, $behaviors);
$row["haveperconarole"] = in_array(ROLE_BEHAVIORS::PERCONA, $behaviors);
$row["havemariadbrole"] = in_array(ROLE_BEHAVIORS::MARIADB, $behaviors);
$row['status_txt'] = FARM_STATUS::GetStatusName($row['status']);
if ($row['status'] == FARM_STATUS::RUNNING) {
$row['shortcuts'] = [];
foreach (\Scalr\Model\Entity\ScriptShortcut::find([['farmId' => $row['id']], ['farmRoleId' => null]]) as $shortcut) {
/* @var $shortcut \Scalr\Model\Entity\ScriptShortcut */
$row['shortcuts'][] = array('id' => $shortcut->id, 'name' => $shortcut->getScriptName());
}
}
$row['farmTeamIdPerm'] = $row['team_id'] && $this->user->isInTeam($row['team_id']);
$row['farmOwnerIdPerm'] = $row['created_by_id'] && $this->user->getId() == $row['created_by_id'];
}
$this->response->data($response);
}
public function xListFarmsAction()
{
$this->request->restrictAccess(Acl::RESOURCE_FARMS);
$this->request->defineParams(array('clientId' => array('type' => 'int'), 'farmId' => array('type' => 'int'), 'sort' => array('type' => 'json'), 'expirePeriod' => array('type' => 'int')));
$governance = new Scalr_Governance($this->getEnvironmentId());
$leaseStatus = $governance->isEnabled(Scalr_Governance::CATEGORY_GENERAL, Scalr_Governance::GENERAL_LEASE);
$sql = 'SELECT f.clientid, f.id, f.name, f.status, f.dtadded, f.created_by_id, f.created_by_email FROM farms f WHERE env_id = ? AND :FILTER:';
$args = array($this->getEnvironmentId());
if ($leaseStatus && $this->getParam('expirePeriod')) {
$dt = new DateTime();
$dt->add(new DateInterval('P' . $this->getParam('expirePeriod') . 'D'));
$sql = str_replace('FROM farms f', 'FROM farms f LEFT JOIN farm_settings fs ON f.id = fs.farmid', $sql);
$sql = str_replace('WHERE', 'WHERE fs.name = ? AND fs.value < ? AND f.status = ? AND', $sql);
array_unshift($args, DBFarm::SETTING_LEASE_TERMINATE_DATE, $dt->format('Y-m-d H:i:s'), FARM_STATUS::RUNNING);
}
if ($this->getParam('farmId')) {
$sql .= ' AND id = ?';
$args[] = $this->getParam('farmId');
}
if ($this->getParam('clientId')) {
$sql .= ' AND clientid = ?';
$args[] = $this->getParam('clientId');
}
if ($this->getParam('status') != '') {
$sql .= ' AND status = ?';
$args[] = $this->getParam('status');
}
if ($this->getParam('showOnlyMy') || !$this->request->isAllowed(Acl::RESOURCE_FARMS, Acl::PERM_FARMS_NOT_OWNED_FARMS)) {
$sql .= ' AND created_by_id = ?';
$args[] = $this->user->getId();
}
$response = $this->buildResponseFromSql2($sql, array('id', 'name', 'dtadded', 'created_by_email', 'status'), array('name', 'id', 'comments'), $args);
foreach ($response["data"] as &$row) {
$row["running_servers"] = $this->db->GetOne("SELECT COUNT(*) FROM servers WHERE farm_id='{$row['id']}' AND status IN ('Pending', 'Initializing', 'Running', 'Temporary','Resuming')");
$row["suspended_servers"] = $this->db->GetOne("SELECT COUNT(*) FROM servers WHERE farm_id='{$row['id']}' AND status IN ('Suspended', 'Pending suspend')");
$row["non_running_servers"] = $this->db->GetOne("SELECT COUNT(*) FROM servers WHERE farm_id='{$row['id']}' AND status NOT IN ('Suspended', 'Pending suspend', 'Resuming', 'Pending', 'Initializing', 'Running', 'Temporary', 'Pending launch')");
$row["roles"] = $this->db->GetOne("SELECT COUNT(*) FROM farm_roles WHERE farmid='{$row['id']}'");
$row["zones"] = $this->db->GetOne("SELECT COUNT(*) FROM dns_zones WHERE farm_id='{$row['id']}'");
//TODO: Use Alerts class
$row['alerts'] = $this->db->GetOne("SELECT COUNT(*) FROM server_alerts WHERE farm_id='{$row['id']}' AND status='failed'");
$row['dtadded'] = Scalr_Util_DateTime::convertTz($row["dtadded"]);
$dbFarm = DBFarm::LoadByID($row['id']);
$row['lock'] = $dbFarm->GetSetting(DBFarm::SETTING_LOCK);
if ($row['lock']) {
$row['lock_comment'] = $dbFarm->isLocked(false);
}
if ($leaseStatus && $dbFarm->GetSetting(DBFarm::SETTING_LEASE_STATUS)) {
$row['lease'] = $dbFarm->GetSetting(DBFarm::SETTING_LEASE_NOTIFICATION_SEND) ? 'Expire' : $dbFarm->GetSetting(DBFarm::SETTING_LEASE_STATUS);
if ($row['lease'] == 'Expire') {
$dt = new DateTime();
$td = new DateTime($dbFarm->GetSetting(DBFarm::SETTING_LEASE_TERMINATE_DATE));
$days = 0;
$hours = 1;
$interval = $dt->diff($td);
if ($interval) {
$days = $interval->days;
$hours = $interval->h ? $interval->h : 1;
}
$row['leaseMessage'] = sprintf('Your farm lease is about to expire in %d %s, after which this farm will be terminated', $days ? $days : $hours, $days ? $days > 1 ? 'days' : 'day' : ($hours > 1 ? 'hours' : 'hour'));
}
}
$b = (array) $this->db->GetAll("SELECT DISTINCT(behavior) FROM farm_roles\n INNER JOIN role_behaviors ON role_behaviors.role_id = farm_roles.role_id WHERE farmid = ?", array($row['id']));
$behaviors = array();
foreach ($b as $behavior) {
$behaviors[] = $behavior['behavior'];
}
$row["havemysqlrole"] = in_array(ROLE_BEHAVIORS::MYSQL, $behaviors);
$row["havemysql2role"] = in_array(ROLE_BEHAVIORS::MYSQL2, $behaviors);
$row["havepgrole"] = in_array(ROLE_BEHAVIORS::POSTGRESQL, $behaviors);
$row["haveredisrole"] = in_array(ROLE_BEHAVIORS::REDIS, $behaviors);
$row["haverabbitmqrole"] = in_array(ROLE_BEHAVIORS::RABBITMQ, $behaviors);
$row["havemongodbrole"] = in_array(ROLE_BEHAVIORS::MONGODB, $behaviors);
$row["haveperconarole"] = in_array(ROLE_BEHAVIORS::PERCONA, $behaviors);
$row["havemariadbrole"] = in_array(ROLE_BEHAVIORS::MARIADB, $behaviors);
$row['status_txt'] = FARM_STATUS::GetStatusName($row['status']);
if ($row['status'] == FARM_STATUS::RUNNING) {
$row['shortcuts'] = [];
foreach (\Scalr\Model\Entity\ScriptShortcut::find(array(array('farmId' => $row['id']), array('farmRoleId' => NULL))) as $shortcut) {
/* @var \Scalr\Model\Entity\ScriptShortcut $shortcut */
$row['shortcuts'][] = array('id' => $shortcut->id, 'name' => $shortcut->getScriptName());
}
}
}
$this->response->data($response);
}
public function xListFarmsAction()
{
$this->request->defineParams(array('clientId' => array('type' => 'int'), 'farmId' => array('type' => 'int'), 'sort' => array('type' => 'json', 'default' => array('property' => 'id', 'direction' => 'asc'))));
$sql = "SELECT clientid, id, name, status, dtadded FROM farms WHERE env_id='" . $this->getEnvironmentId() . "'";
if ($this->getParam('farmId')) {
$sql .= " AND id=" . $this->db->qstr($this->getParam('farmId'));
}
if ($this->getParam('clientId')) {
$sql .= " AND clientid=" . $this->db->qstr($this->getParam('clientId'));
}
if ($this->getParam('status') != '') {
$sql .= " AND status=" . $this->db->qstr($this->getParam('status'));
}
$response = $this->buildResponseFromSql($sql, array("name", "id", "comments"));
foreach ($response["data"] as &$row) {
$row["running_servers"] = $this->db->GetOne("SELECT COUNT(*) FROM servers WHERE farm_id='{$row['id']}' AND status IN ('Pending', 'Initializing', 'Running', 'Temporary', 'Pending launch')");
$row["non_running_servers"] = $this->db->GetOne("SELECT COUNT(*) FROM servers WHERE farm_id='{$row['id']}' AND status NOT IN ('Pending', 'Initializing', 'Running', 'Temporary')");
$row["roles"] = $this->db->GetOne("SELECT COUNT(*) FROM farm_roles WHERE farmid='{$row['id']}'");
$row["zones"] = $this->db->GetOne("SELECT COUNT(*) FROM dns_zones WHERE farm_id='{$row['id']}'");
$row['dtadded'] = Scalr_Util_DateTime::convertTz($row["dtadded"]);
$row["havemysqlrole"] = (bool) $this->db->GetOne("SELECT id FROM farm_roles WHERE role_id IN (SELECT role_id FROM role_behaviors WHERE behavior=?) AND farmid=? AND platform != ?", array(ROLE_BEHAVIORS::MYSQL, $row['id'], SERVER_PLATFORMS::RDS));
$row["havemysql2role"] = (bool) $this->db->GetOne("SELECT id FROM farm_roles WHERE role_id IN (SELECT role_id FROM role_behaviors WHERE behavior=?) AND farmid=? AND platform != ?", array(ROLE_BEHAVIORS::MYSQL2, $row['id'], SERVER_PLATFORMS::RDS));
$row["havepgrole"] = (bool) $this->db->GetOne("SELECT id FROM farm_roles WHERE role_id IN (SELECT role_id FROM role_behaviors WHERE behavior=?) AND farmid=? AND platform != ?", array(ROLE_BEHAVIORS::POSTGRESQL, $row['id'], SERVER_PLATFORMS::RDS));
$row["haveredisrole"] = (bool) $this->db->GetOne("SELECT id FROM farm_roles WHERE role_id IN (SELECT role_id FROM role_behaviors WHERE behavior=?) AND farmid=? AND platform != ?", array(ROLE_BEHAVIORS::REDIS, $row['id'], SERVER_PLATFORMS::RDS));
$row["haverabbitmqrole"] = (bool) $this->db->GetOne("SELECT id FROM farm_roles WHERE role_id IN (SELECT role_id FROM role_behaviors WHERE behavior=?) AND farmid=? AND platform != ?", array(ROLE_BEHAVIORS::RABBITMQ, $row['id'], SERVER_PLATFORMS::RDS));
$row["havemongodbrole"] = (bool) $this->db->GetOne("SELECT id FROM farm_roles WHERE role_id IN (SELECT role_id FROM role_behaviors WHERE behavior=?) AND farmid=? AND platform != ?", array(ROLE_BEHAVIORS::MONGODB, $row['id'], SERVER_PLATFORMS::RDS));
$row['status_txt'] = FARM_STATUS::GetStatusName($row['status']);
if ($row['status'] == FARM_STATUS::RUNNING) {
$row['shortcuts'] = $this->db->GetAll("SELECT * FROM farm_role_scripts WHERE farmid=? AND (farm_roleid IS NULL OR farm_roleid='0') AND ismenuitem='1'", array($row['id']));
foreach ($row['shortcuts'] as &$shortcut) {
$shortcut['name'] = $this->db->GetOne("SELECT name FROM scripts WHERE id=?", array($shortcut['scriptid']));
}
}
}
$this->response->data($response);
}