/** * {@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(); }
/** * Gets the usage for specified cost centre breakdown by specified tags * * @param array $criteria The list of the criterias ['ccId' => [], 'projectId' => []] * @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_PROJECT, TagEntity::TAG_ID_FARM ...] * The inteval 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 */ public function get($criteria, DateTime $begin, DateTime $end, $breakdown = null, $rawResult = false) { //We do not take into cosideration not managed cost by default. It should not be shown on CC or Projects pages. $ignorenmusage = true; $now = new DateTime("now", new DateTimeZone('UTC')); 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.")); } $obj = new UsageHourlyEntity(); if ($breakdown !== null) { if (!is_array($breakdown)) { $breakdown = [$breakdown]; } } $ccId = null; $projectId = null; $aFields = ['ccId', 'projectId', 'cost']; $selectFields = "`u`.`cc_id`, `u`.`project_id`, SUM(`u`.`cost`) AS `cost`"; $selectNmFields = "`s`.`cc_id`, NULL AS `project_id`, SUM(`nu`.`cost`) AS `cost`"; $selectUnion = "`cc_id`, `project_id`, SUM(`cost`) AS `cost`"; if (isset($criteria) && array_key_exists('ccId', $criteria)) { $ccId = $criteria['ccId']; } if (isset($criteria) && array_key_exists('projectId', $criteria)) { $projectId = $criteria['projectId']; if ($projectId === '') { $projectId = null; } elseif (is_array($projectId)) { $projectId = array_map(function ($v) { return $v === '' ? null : $v; }, $projectId); } } $it = $obj->getIterator(); //Group rules according to ChartPeriodIterator $groupFields = ['hour' => [true, "`u`.`dtime` `period`", null], 'day' => [true, "DATE(`u`.`dtime`) `period`", null], 'week' => [true, "YEARWEEK(`u`.`dtime`, 0) `period`", null], 'month' => [true, "DATE_FORMAT(`u`.`dtime`, '%Y-%m') `period`", null], 'year' => [true, "YEAR(`u`.`dtime`) `period`", null], TagEntity::TAG_ID_ENVIRONMENT => ['envId', 's'], TagEntity::TAG_ID_PLATFORM => ['platform', 'nu'], TagEntity::TAG_ID_FARM => ['farmId', null], TagEntity::TAG_ID_FARM_ROLE => ['farmRoleId', null], TagEntity::TAG_ID_PROJECT => ['projectId', null], TagEntity::TAG_ID_COST_CENTRE => ['ccId', 's'], 'cloudLocation' => ['cloudLocation', 'nu']]; $notSupportDaily = ['hour', TagEntity::TAG_ID_FARM_ROLE, 'cloudLocation']; $group = ''; $groupNm = ''; $groupUnion = ''; $subtotals = []; if (!empty($breakdown)) { //We are intrested in value of the environment's identifier if farm breakdown is selected if (in_array(TagEntity::TAG_ID_FARM, $breakdown) && !in_array(TagEntity::TAG_ID_ENVIRONMENT, $breakdown)) { $selectFields = "u.`env_id`, " . $selectFields; $selectNmFields = "s.`env_id`, " . $selectNmFields; $selectUnion = "`env_id`, " . $selectUnion; $aFields[] = 'envId'; } 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; $selectNmFields = str_replace('`u`.', '`nu`.', $groupFields[$t][1]) . ', ' . $selectNmFields; $selectUnion = "`period`, " . $selectUnion; $group .= ($groupFields[$t][2] ?: "`period`") . ', '; $groupNm .= str_replace('`u`.', '`nu`.', $groupFields[$t][2] ?: "`period`") . ', '; $groupUnion .= "`period`, "; } else { $field = $it->getField($groupFields[$t][0]); $subtotals[] = $field->name; if ($field->name != 'ccId' && $field->name != 'projectId') { $selectFields = $field->getColumnName('u') . ', ' . $selectFields; $selectNmFields = (isset($groupFields[$t][1]) ? $field->getColumnName($groupFields[$t][1]) : 'NULL ' . $field->column->name) . ', ' . $selectNmFields; $selectUnion = $field->column->name . ', ' . $selectUnion; } $group .= $field->getColumnName('u') . ', '; //To avoid data duplication by environment we should exclude it from grouping. //It will return only first available identifier of the environment for the specified cost centre. if ($t != TagEntity::TAG_ID_ENVIRONMENT) { $groupNm .= isset($groupFields[$t][1]) ? $field->getColumnName($groupFields[$t][1]) . ', ' : ''; } $groupUnion .= $field->column->name . ', '; } } $group = 'GROUP BY ' . substr($group, 0, -2); $groupUnion = 'GROUP BY ' . substr($groupUnion, 0, -2); if (!empty($groupNm)) { $groupNm = 'GROUP BY ' . substr($groupNm, 0, -2); } } $orderUnion = in_array('period', $subtotals) ? 'ORDER BY `period`' : ''; if ($rawResult) { $ret = []; } else { $ret = new AggregationCollection($subtotals, ['cost' => 'sum']); if (!is_array($ccId)) { $ret->setId($ccId); } } $uwherestmt = 'TRUE'; $suwherestmt = 'TRUE'; if (isset($ccId)) { $uwherestmt .= ' AND ' . $obj->_buildQuery([['ccId' => is_array($ccId) ? ['$in' => $ccId] : $ccId]], 'AND', 'u')['where']; $suwherestmt = preg_replace('/`u`\\./', '`su`.', $uwherestmt); } if (isset($criteria) && array_key_exists('projectId', $criteria)) { if (is_string($projectId) && $projectId !== null || is_array($projectId) && !in_array(null, $projectId, true)) { $ignorenmusage = true; } $uwherestmt .= ' AND ' . $obj->_buildQuery([['projectId' => is_array($projectId) ? ['$in' => $projectId] : $projectId]], 'AND', 'u')['where']; } if (empty($breakdown) || count(array_intersect($notSupportDaily, array_values($breakdown))) == 0) { //Selects from daily usage table $statement = "\n SELECT " . str_replace('`dtime`', '`date`', $selectFields) . "\n FROM `usage_d` u\n WHERE " . $uwherestmt . "\n AND u.`date` >= ? AND u.`date` <= ?\n " . $groupUnion . "\n " . $orderUnion . "\n "; $res = $obj->db()->Execute($statement, array($begin->format('Y-m-d'), $end->format('Y-m-d'))); } else { $dtimeType = $it->getField('dtime')->type; //Selects from hourly usage table $statement = "\n SELECT " . $selectUnion . " FROM (\n SELECT " . $selectFields . "\n FROM `usage_h` u\n WHERE " . $uwherestmt . "\n AND u.`dtime` >= ? AND u.`dtime` <= ?\n " . $group . "\n " . (isset($ignorenmusage) ? "" : "\n UNION ALL\n\n SELECT " . $selectNmFields . "\n FROM `nm_usage_h` nu\n JOIN (\n SELECT us.usage_id, su.cc_id, su.env_id\n FROM `nm_subjects_h` su\n JOIN `nm_usage_subjects_h` us ON us.subject_id = su.subject_id\n WHERE " . $suwherestmt . "\n GROUP BY us.usage_id\n ) s ON nu.usage_id = s.usage_id\n WHERE nu.`dtime` >= ? AND nu.`dtime` <= ?\n " . $groupNm) . "\n ) t\n " . $groupUnion . "\n " . $orderUnion . "\n "; $res = $obj->db()->Execute($statement, array($dtimeType->toDb($begin), $dtimeType->toDb($end), $dtimeType->toDb($begin), $dtimeType->toDb($end))); } $aFields = array_diff(array_merge($aFields, $subtotals), ['period']); while ($rec = $res->FetchRow()) { $item = new UsageHourlyEntity(); $item->load($rec); $arr = []; foreach ($aFields as $col) { $arr[$col] = $item->{$col}; } if (isset($rec['period'])) { $arr['period'] = (string) $rec['period']; } if ($rawResult) { $ret[] = $arr; } else { $ret->append($arr); } } //Calculates percentage if (!$rawResult && !empty($subtotals)) { $ret->calculatePercentage(); } return $ret; }