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