/**
* 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;
}
