/**
* Gets cost metering data
*
* @param string $accountId Client identifier
* @param array $criteria Filter array. ['fieldName' => 'fieldValue'] or ['fieldName' => ['value1', 'value2']]
* @param DateTime $begin Begin date
* @param DateTime $end End date
*
* @param array|string $breakdown optional The identifier of the tag or list
* looks like ['day', TagEntity::TAG_ID_FARM ...]
* The interval to group data [12 hours, day, week, month]
*
* @param bool $rawResult optional Whether it should return raw result
*
* @return AggregationCollection|array Returns collection or array with raw result
* @throws InvalidArgumentException
*/
public function getFarmData($accountId, array $criteria, DateTime $begin, DateTime $end, $breakdown = null, $rawResult = false)
{
$now = new DateTime("now", new DateTimeZone('UTC'));
$usageHourly = false;
if ($end > $now) {
$end = $now;
}
if (!$begin instanceof DateTime || !$end instanceof DateTime) {
throw new InvalidArgumentException(sprintf("Both Start end End time should be instance of DateTime."));
}
if ($breakdown !== null) {
if (!is_array($breakdown)) {
$breakdown = [$breakdown];
}
if (in_array('hour', $breakdown)) {
$usageHourly = true;
}
}
if (isset($criteria['hourly'])) {
$usageHourly = true;
unset($criteria['hourly']);
}
$selectFields = "SUM(`u`.`cost`) AS `cost`, `u`.`cloud_location`, `u`.`platform`, `u`.`project_id`, `u`.`account_id`, `u`.`env_id`";
if ($usageHourly) {
$obj = new UsageHourlyEntity();
if ($breakdown !== null) {
$selectFields .= ", MIN(`u`.`num`) AS `min_usage`, MAX(`u`.`num`) AS `max_usage`,";
} else {
$selectFields .= ", `u`.`num` AS `min_usage`, `u`.`num` AS `max_usage`,";
}
$selectFields .= "`u`.`num` AS `usage_hours`, 1 AS `working_hours`";
$dtime = 'dtime';
} else {
$obj = new FarmUsageDailyEntity();
if ($breakdown !== null) {
$selectFields .= ", MIN(`u`.`min_usage`) AS `min_usage`, MAX(`u`.`max_usage`) AS `max_usage`, SUM(`u`.`usage_hours`) AS `usage_hours`, SUM(`u`.`working_hours`) AS `working_hours`";
} else {
$selectFields .= ", `u`.`min_usage`, `u`.`max_usage`, `u`.`usage_hours`, `u`.`working_hours`";
}
$dtime = 'date';
}
$aFields = ['cost', 'projectId', 'minUsage', 'maxUsage', 'cloudLocation', 'usageHours', 'workingHours', 'platform', 'accountId', 'envId'];
$where = ' u.account_id = ' . $this->cadb->escape($accountId);
$it = $obj->getIterator();
$getValue = function (Field $field, $value) {
$value = $field->type->toDb($value);
if ($field->getType() instanceof UuidType) {
$value = "UNHEX(" . $this->cadb->qstr($value) . ")";
}
return $value;
};
foreach ($criteria as $name => $value) {
$field = $it->getField($name);
if (is_null($field)) {
throw new InvalidArgumentException(sprintf("Invalid field name: %s", $name));
}
if (!is_array($value)) {
$where .= ' AND ' . $field->getColumnName('u') . '=' . $getValue($field, $value);
} else {
$values = [];
$operator = 'IN';
if (count($value) == 1) {
list($k, $v) = each($value);
if ($k === '$in') {
$operator = 'IN';
$value = $v;
} elseif ($k === '$nin') {
$operator = 'NOT IN';
$value = $v;
}
}
foreach ($value as $val) {
$values[] = $getValue($field, $val);
}
$where .= ' AND ' . $field->getColumnName('u') . $operator . " ('" . implode("','", $values) . "')";
}
}
//Group rules according to ChartPeriodIterator
$groupFields = ['hour' => [true, "`u`.`date` `period`", null], 'day' => [true, "DATE(`u`.`date`) `period`", null], 'week' => [true, "YEARWEEK(`u`.`date`, 0) `period`", null], 'month' => [true, "DATE_FORMAT(`u`.`date`, '%Y-%m') `period`", null], 'year' => [true, "YEAR(`u`.`date`) `period`", null], TagEntity::TAG_ID_ENVIRONMENT => ['envId', 'u'], TagEntity::TAG_ID_PLATFORM => ['platform', 'u'], TagEntity::TAG_ID_FARM => ['farmId', 'u'], TagEntity::TAG_ID_FARM_ROLE => ['farmRoleId', 'u'], TagEntity::TAG_ID_PROJECT => ['projectId', 'u'], 'cloudLocation' => ['cloudLocation', 'u'], 'usageItem' => ['name', 'ui'], 'usageType' => ['id', 'ut'], 'distributionType' => ['costDistrType', 'ut']];
$group = '';
$join = '';
$subtotals = [];
if (!empty($breakdown)) {
foreach ($breakdown as $t) {
if (!isset($groupFields[$t])) {
throw new InvalidArgumentException(sprintf("Tag %d is not supported as breakdown in %s call.", $t, __FUNCTION__));
}
if ($groupFields[$t][0] === true) {
$subtotals[] = 'period';
$selectFields = $groupFields[$t][1] . ', ' . $selectFields;
$group .= ($groupFields[$t][2] ?: "`period`") . ', ';
} else {
if ($t == 'usageItem' || $t == 'usageType' || $t == 'distributionType') {
$subtotals[] = $t;
if ($t == 'usageItem') {
$entity = new UsageItemEntity();
$selectFields = '`ui`.`id`,' . $selectFields;
} else {
$entity = new UsageTypeEntity();
if ($t == 'usageType') {
$selectFields = '`ut`.`name`, `ut`.`display_name`,' . $selectFields;
}
}
$uiIterator = $entity->getIterator();
$field = $uiIterator->getField($groupFields[$t][0]);
$selectFields = $field->getColumnName($groupFields[$t][1], $t) . ', ' . $selectFields;
$group .= '`' . $t . '`, ';
} else {
$field = $it->getField($groupFields[$t][0]);
$subtotals[] = $field->name;
if ($t !== TagEntity::TAG_ID_ENVIRONMENT) {
$selectFields = $field->getColumnName('u') . ', ' . $selectFields;
}
$group .= $field->getColumnName('u') . ', ';
}
}
}
if (in_array('usageItem', $breakdown) || in_array('usageType', $breakdown)) {
$join = "\n JOIN usage_items ui ON ui.id = u.usage_item\n JOIN usage_types ut ON ut.id = ui.usage_type\n ";
}
$group = 'GROUP BY ' . substr($group, 0, -2);
}
$order = in_array('period', $subtotals) ? 'ORDER BY `period`' : '';
if ($rawResult) {
$ret = [];
} else {
$ret = new AggregationCollection($subtotals, ['cost' => 'sum', 'minUsage' => 'min', 'maxUsage' => 'max', 'usageHours' => 'sum', 'workingHours' => 'sum']);
}
$dtimeType = $it->getField($dtime)->type;
if (!$usageHourly) {
//Selects from daily usage table
$statement = "\n SELECT " . $selectFields . "\n FROM `farm_usage_d` u\n " . $join . "\n WHERE " . $where . "\n AND u.`date` >= ? AND u.`date` <= ?\n " . $group . "\n " . $order . "\n ";
} else {
//Selects from hourly usage table
$statement = "\n SELECT " . str_replace('`date`', '`dtime`', $selectFields) . "\n FROM `usage_h` u\n " . $join . "\n WHERE " . $where . "\n AND u.`dtime` >= ? AND u.`dtime` <= ?\n " . $group . "\n " . $order . "\n ";
}
$res = $obj->db()->Execute($statement, array($dtimeType->toDb($begin), $dtimeType->toDb($end)));
$aFields = array_diff(array_merge($aFields, $subtotals), ['period', 'usageItem', 'usageType', 'distributionType']);
while ($rec = $res->FetchRow()) {
$item = new FarmUsageDailyEntity();
$item->load($rec);
$arr = [];
foreach ($aFields as $col) {
$arr[$col] = $item->{$col};
}
if (isset($rec['period'])) {
$arr['period'] = (string) $rec['period'];
}
if (isset($rec['usageItem'])) {
$arr['usageItem'] = (string) $rec['usageItem'];
$uiEntity = new UsageItemEntity();
$uiIterator = $uiEntity->getIterator();
$type = $uiIterator->getField('id')->getType();
$arr['id'] = $type->toPhp($rec['id']);
}
if (isset($rec['usageType'])) {
$utEntity = new UsageTypeEntity();
$utIterator = $utEntity->getIterator();
$type = $utIterator->getField($groupFields['usageType'][0])->getType();
$arr['usageType'] = $type->toPhp($rec['usageType']);
$arr['displayName'] = $rec['display_name'];
$arr['name'] = (string) $rec['name'];
}
if (isset($rec['distributionType'])) {
$arr['distributionType'] = (int) $rec['distributionType'];
}
if ($rawResult) {
$ret[] = $arr;
} else {
$ret->append($arr);
}
}
//Calculates percentage
if (!$rawResult && !empty($subtotals)) {
$ret->calculatePercentage();
}
return $ret;
}
/**
* {@inheritdoc}
* @see \Scalr\System\Zmq\Cron\AbstractTask::enqueue
*/
public function enqueue()
{
$logger = $this->getLogger();
if (!\Scalr::getContainer()->analytics->enabled) {
$logger->info("CA has not been enabled in config!\n");
}
$db = \Scalr::getDb();
$cadb = \Scalr::getContainer()->cadb;
$pricing = new stdPricing();
$quarters = new Quarters(SettingEntity::getQuarters());
$logger->info('Started AnalyticsDemo process');
$tzUtc = new DateTimeZone('UTC');
/* @var $projects stdProject[] */
$projects = [];
/* @var $ccs stdCc[] */
$ccs = [];
/* @var $farms stdFarm[] */
$farms = [];
/* @var $environments stdEnv[] */
$environments = [];
/* @var $farmRoles stdFarmRole[] */
//$farmRoles = [];
//Analytics container
$analytics = \Scalr::getContainer()->analytics;
$logger->debug('CC & PROJECTS ---');
foreach ($analytics->ccs->all(true) as $cc) {
/* @var $cc \Scalr\Stats\CostAnalytics\Entity\CostCentreEntity */
$co = new stdCc();
$co->cc = $cc;
$ccs[$cc->ccId] = $co;
$logger->debug("Cost center: '%s'", $cc->name);
foreach ($cc->getProjects() as $project) {
/* @var $project \Scalr\Stats\CostAnalytics\Entity\ProjectEntity */
$project->loadProperties();
$po = new stdProject();
$po->project = $project;
$po->cc = $co;
$projects[$project->projectId] = $po;
$logger->debug("-- Project: '%s'", $project->name);
}
}
//Ordering cost centers
$number = 0;
foreach ($ccs as $obj) {
$obj->number = $number++;
}
//Ordering projects
$number = 0;
foreach ($projects as $obj) {
$obj->number = $number++;
}
$logger->debug("FARMS ---");
$pastIterations = static::PAST_HOURS_INIT;
//Current time
$start = new DateTime('now', $tzUtc);
$dt = clone $start;
do {
$timestamp = $dt->format('Y-m-d H:00:00');
$period = $quarters->getPeriodForDate($dt->format('Y-m-d'));
$logger->info("Processing time:%s, year:%d, quarter:%d", $timestamp, $period->year, $period->quarter);
//Gets farms for each project
foreach ($projects as $po) {
foreach ($analytics->projects->getFarmsList($po->project->projectId) as $farmId => $farmName) {
if (!isset($farms[$farmId])) {
$fo = new stdFarm();
$fo->farm = \DBFarm::LoadByID($farmId);
$fo->project = $po;
$fo->cc = $po->cc;
//$po->farms[] = $fo;
$farms[$farmId] = $fo;
if (!isset($environments[$fo->farm->EnvID])) {
$eo = new stdEnv();
$eo->env = $fo->farm->getEnvironmentObject();
//$eo->farms = [$farmId => $fo];
$environments[$fo->farm->EnvID] = $eo;
$fo->env = $eo;
} else {
//$environments[$fo->farm->EnvID]->farms[$farmId] = $fo;
$fo->env = $environments[$fo->farm->EnvID];
}
$fo->farmRoles = [];
foreach ($fo->farm->GetFarmRoles() as $farmRole) {
$fro = new stdFarmRole();
$fro->farmRole = $farmRole;
$fro->farm = $fo;
$fro->min = $farmRole->GetSetting(\DBFarmRole::SETTING_SCALING_MIN_INSTANCES);
$fro->max = $farmRole->GetSetting(\DBFarmRole::SETTING_SCALING_MAX_INSTANCES);
$fo->farmRoles[$farmRole->ID] = $fro;
//$farmRoles[$farmRole->ID] = $fro;
}
} else {
$fo = $farms[$farmId];
}
$logger->debug("Farm:'%s':%d from Env:'%s':%d corresponds to Project:'%s' -> CC:'%s'", $fo->farm->Name, $fo->farm->ID, $fo->farm->getEnvironmentObject()->name, $fo->farm->EnvID, $po->project->name, $po->cc->cc->name);
foreach ($fo->farmRoles as $fro) {
/* @var $fro stdFarmRole */
$countInstances = rand(max(1, floor($fro->max * 0.7)), min((int) $fro->max, 2));
$cost = $pricing->getPrice($dt, $fro->farmRole->Platform, $fro->farmRole->CloudLocation, $fro->getInstanceType(), $fo->env->getUrl($fro->farmRole->Platform), PriceEntity::OS_LINUX);
$type = UsageTypeEntity::NAME_COMPUTE_BOX_USAGE;
$costDistType = UsageTypeEntity::COST_DISTR_TYPE_COMPUTE;
$usageTypeEntity = UsageTypeEntity::findOne([['costDistrType' => $costDistType], ['name' => $type]]);
/* @var $usageTypeEntity UsageTypeEntity */
if ($usageTypeEntity === null) {
$usageTypeEntity = new UsageTypeEntity();
$usageTypeEntity->costDistrType = $costDistType;
$usageTypeEntity->name = $type;
$usageTypeEntity->displayName = 'Compute instances';
$usageTypeEntity->save();
}
$item = $fro->getInstanceType();
$usageItemEntity = UsageItemEntity::findOne([['usageType' => $usageTypeEntity->id], ['name' => $item]]);
/* @var $usageItemEntity UsageItemEntity */
if ($usageItemEntity === null) {
$usageItemEntity = new UsageItemEntity();
$usageItemEntity->usageType = $usageTypeEntity->id;
$usageItemEntity->name = $item;
$usageItemEntity->save();
}
//Hourly usage
$rec = new UsageHourlyEntity();
$rec->usageId = \Scalr::GenerateUID();
$rec->accountId = $fro->farm->farm->ClientID;
$rec->ccId = $po->cc->cc->ccId;
$rec->projectId = $po->project->projectId;
$rec->cloudLocation = $fro->farmRole->CloudLocation;
$rec->dtime = new DateTime($timestamp, $tzUtc);
$rec->envId = $fo->farm->EnvID;
$rec->farmId = $fo->farm->ID;
$rec->farmRoleId = $fro->farmRole->ID;
$rec->usageItem = $usageItemEntity->id;
$rec->platform = $fro->farmRole->Platform;
$rec->url = $fo->env->getUrl($fro->farmRole->Platform);
$rec->os = PriceEntity::OS_LINUX;
$rec->num = $countInstances;
$rec->cost = $cost * $countInstances;
$rec->save();
$logger->log(static::PAST_HOURS_INIT > 0 ? 'DEBUG' : 'INFO', "-- role:'%s':%d platform:%s, min:%d - max:%d, cloudLocation:'%s', usageItem:'%s', " . "cost:%0.4f * %d = %0.3f", $fro->farmRole->Alias, $fro->farmRole->ID, $fro->farmRole->Platform, $fro->min, $fro->max, $fro->farmRole->CloudLocation, $usageItemEntity->id, $cost, $countInstances, $rec->cost);
//Update Daily table
$cadb->Execute("\n INSERT usage_d\n SET date = ?,\n platform = ?,\n cc_id = UNHEX(?),\n project_id = UNHEX(?),\n farm_id = ?,\n env_id = ?,\n cost = ?\n ON DUPLICATE KEY UPDATE cost = cost + ?\n ", [$rec->dtime->format('Y-m-d'), $rec->platform, $rec->ccId ? str_replace('-', '', $rec->ccId) : '00000000-0000-0000-0000-000000000000', $rec->projectId ? str_replace('-', '', $rec->projectId) : '00000000-0000-0000-0000-000000000000', $rec->farmId ? $rec->farmId : 0, $rec->envId ? $rec->envId : 0, $rec->cost, $rec->cost]);
//Updates Quarterly Budget
if ($rec->ccId) {
$cadb->Execute("\n INSERT quarterly_budget\n SET year = ?,\n subject_type = ?,\n subject_id = UNHEX(?),\n quarter = ?,\n budget = 1000,\n cumulativespend = ?\n ON DUPLICATE KEY UPDATE cumulativespend = cumulativespend + ?\n ", [$period->year, QuarterlyBudgetEntity::SUBJECT_TYPE_CC, str_replace('-', '', $rec->ccId), $period->quarter, $rec->cost, $rec->cost]);
}
if ($rec->projectId) {
$cadb->Execute("\n INSERT quarterly_budget\n SET year = ?,\n subject_type = ?,\n subject_id = UNHEX(?),\n quarter = ?,\n budget = 1000,\n cumulativespend = ?\n ON DUPLICATE KEY UPDATE cumulativespend = cumulativespend + ?\n ", [$period->year, QuarterlyBudgetEntity::SUBJECT_TYPE_PROJECT, str_replace('-', '', $rec->projectId), $period->quarter, $rec->cost, $rec->cost]);
}
}
unset($fo);
}
}
$dt->modify('-1 hour');
} while ($pastIterations-- > 0);
$dt = clone $start;
$start->modify(sprintf("-%d hour", static::PAST_HOURS_INIT));
$start->setTime(0, 0, 0);
$date = $dt->format('Y-m-d');
$hours = (int) $dt->format('H');
do {
$cadb->Execute("\n INSERT INTO `farm_usage_d` (\n `account_id`,\n `farm_role_id`,\n `usage_item`,\n `cc_id`,\n `project_id`,\n `date`,\n `platform`,\n `cloud_location`,\n `env_id`,\n `farm_id`,\n `role_id`,\n `cost`,\n `min_usage`,\n `max_usage`,\n `usage_hours`,\n `working_hours`)\n SELECT\n `account_id`,\n IFNULL(`farm_role_id`, 0) `farm_role_id`,\n `usage_item`,\n IFNULL(`cc_id`, '') `cc_id`,\n IFNULL(`project_id`, '') `project_id`,\n ? `date`,\n `platform`,\n `cloud_location`,\n IFNULL(`env_id`, 0) `env_id`,\n IFNULL(`farm_id`, 0) `farm_id`,\n IFNULL(`role_id`, 0) `role_id`,\n SUM(`cost`) `cost`,\n (CASE WHEN COUNT(`dtime`) >= ? THEN MIN(`num`) ELSE 0 END) `min_usage`,\n MAX(`num`) `max_usage`,\n SUM(`num`) `usage_hours`,\n COUNT(`dtime`) `working_hours`\n FROM `usage_h` `uh`\n WHERE `uh`.`dtime` BETWEEN ? AND ?\n AND `uh`.`farm_id` > 0\n AND `uh`.`farm_role_id` > 0\n GROUP BY `uh`.`account_id` , `uh`.`farm_role_id` , `uh`.`usage_item`\n ON DUPLICATE KEY UPDATE\n `cost` = VALUES(`cost`),\n `min_usage` = VALUES(`min_usage`),\n `max_usage` = VALUES(`max_usage`),\n `usage_hours` = VALUES(`usage_hours`),\n `working_hours` = VALUES(`working_hours`)\n ", ["{$date} 00:00:00", $hours, "{$date} 00:00:00", "{$date} 23:00:00"]);
$dt->modify('-1 day');
$date = $dt->format('Y-m-d');
$hours = 24;
} while ($dt >= $start);
$logger->info("Finished AnalyticsDemo process");
$logger->info("Memory usage: %0.3f Mb", memory_get_usage() / 1024 / 1024);
return new ArrayObject();
}
/**
* Initializes UsageItem
*
* @param string $item The name of the Usage Item
* @param string $type optional The name of the Usage Type
* @param int $costDistType optional The cost distribution type
*/
private function initUsageItem($item, $type = UsageTypeEntity::NAME_COMPUTE_BOX_USAGE, $costDistType = UsageTypeEntity::COST_DISTR_TYPE_COMPUTE)
{
if (!isset($this->usageItems[$costDistType][$type][$item])) {
if (!isset($this->usageItems[$costDistType][$type]['_type_'])) {
//Finds Usage Type entity by unique key
$usageType = UsageTypeEntity::findOne([['costDistrType' => $costDistType], ['name' => $type]]);
if ($usageType === null) {
$usageType = new UsageTypeEntity();
$usageType->costDistrType = $costDistType;
$usageType->name = $type;
if ($type == UsageTypeEntity::NAME_COMPUTE_BOX_USAGE) {
$usageType->displayName = 'Compute instances';
}
$usageType->save();
}
$this->usageItems[$costDistType][$type]['_type_'] = $usageType;
}
$usageType = $this->usageItems[$costDistType][$type]['_type_'];
//Finds UsageItem entity by unique key
$usageItem = UsageItemEntity::findOne([['usageType' => $usageType->id], ['name' => $item]]);
if ($usageItem === null) {
$usageItem = new UsageItemEntity();
$usageItem->usageType = $usageType->id;
$usageItem->name = $item;
$usageItem->save();
}
$this->usageItems[$costDistType][$type][$item] = $usageItem;
}
}
