/** * Finds collection of the values by any key * * @param array $criteria optional The search criteria. * @param array $group optional The group by looks like [property1, ...] * @param array $order optional The results order looks like [[property1 => true|false], ...] * @param int $limit optional The records limit * @param int $offset optional The offset * @param bool $countRecords optional True to calculate total number of the records without limit * @return ArrayCollection|EntityIterator|ADORecordSet_mysqli Returns collection of the entities. The type * of the result depends on resultType property of the class which can be set as * FooEntity::result(FooEntity::RESULT_ARRAY_COLLECTION)->find() */ private function _find(array $criteria = null, array $group = null, array $order = null, $limit = null, $offset = null, $countRecords = null) { $class = get_class($this); $iterator = $this->getIterator(); if (is_array($criteria) && array_key_exists(static::STMT_FROM, $criteria)) { $stmtFrom = "FROM " . $criteria[static::STMT_FROM]; unset($criteria[static::STMT_FROM]); } else { $stmtFrom = "FROM {$this->table()}"; } if (is_array($criteria) && array_key_exists(static::STMT_WHERE, $criteria)) { $stmtWhere = "WHERE " . $criteria[static::STMT_WHERE]; $rawWhere = !empty($criteria[static::STMT_WHERE]); unset($criteria[static::STMT_WHERE]); } else { $stmtWhere = "WHERE"; } if (!empty($criteria)) { $built = $this->_buildQuery($criteria); } if (!empty($group)) { $sGroup = ''; foreach ($group as $col) { $field = $iterator->getField($col); if (!$field) { throw new InvalidArgumentException(sprintf("Property %s does not exist in %s", $col, $class)); } $sGroup .= ', ' . $field->getColumnName(); } $sGroup = $sGroup != '' ? 'GROUP BY ' . substr($sGroup, 2) : ''; } if (!empty($order)) { $sOrder = ''; foreach ($order as $k => $v) { $field = $iterator->getField($k); if (!$field) { throw new InvalidArgumentException(sprintf("Property %s does not exist in %s", $k, $class)); } $sOrder .= ', ' . $field->getColumnName() . ($v ? '' : ' DESC'); } $sOrder = $sOrder != '' ? 'ORDER BY ' . substr($sOrder, 2) : ''; } $bcnt = $countRecords && isset($limit); $builtWhere = !empty($built['where']) ? $built['where'] : '1=1'; $stmt = "\n SELECT " . ($bcnt ? 'SQL_CALC_FOUND_ROWS ' : '') . (!empty($criteria[static::STMT_DISTINCT]) ? 'DISTINCT ' : '') . $this->fields() . " {$stmtFrom}\n {$stmtWhere} " . (!empty($rawWhere) ? "AND (" . $builtWhere . ")" : $builtWhere) . "\n " . (!empty($sOrder) ? $sOrder : "") . "\n " . (isset($limit) ? "LIMIT " . ($offset ? intval($offset) . ',' : '') . intval($limit) : "") . "\n "; $res = $this->db()->Execute($stmt); if ($this->resultType === self::RESULT_ENTITY_COLLECTION) { $ret = new ArrayCollection(); while ($item = $res->FetchRow()) { $obj = new $class(); $obj->load($item); $ret->append($obj); unset($obj); } } else { if ($this->resultType === self::RESULT_ENTITY_ITERATOR) { $ret = new EntityIterator($class, $res); } else { if ($this->resultType === self::RESULT_RAW) { $this->resultType = self::DEFAULT_RESULT_TYPE; return $res; } } } if ($bcnt) { $ret->totalNumber = $this->db()->getOne('SELECT FOUND_ROWS()'); } else { if ($countRecords) { $ret->totalNumber = $res->RowCount(); } } //Restores default result type $this->resultType = self::DEFAULT_RESULT_TYPE; return $ret; }
/** * xSavePriceAction * * @param string $platform The cloud platform * @param string $cloudLocation The cloud location * @param JsonData $prices Price list * @param string $url optional The url of the cloud * @param string $effectiveDate optional The date when the prices will be applied * @param boolean $forbidAutomaticUpdate optional */ public function xSavePriceAction($platform, $cloudLocation, JsonData $prices, $url = '', $effectiveDate = null, $forbidAutomaticUpdate = false) { list($curdate, $effectiveDate) = $this->handleEffectiveDate($effectiveDate); $service = $this->getContainer()->analytics->prices; $priceHistory = new PriceHistoryEntity(); $priceHistory->platform = $platform; $priceHistory->cloudLocation = $cloudLocation; $priceHistory->url = $service->normalizeUrl($url); $priceHistory->accountId = $this->user->getAccountId(); $priceHistory->applied = $effectiveDate; $this->getContainer()->analytics->prices->get($priceHistory); if ($priceHistory->priceId !== null) { //We have found recent prices if ($effectiveDate->format('Y-m-d') != $priceHistory->applied->format('Y-m-d')) { //We should generate a new version of the price $priceHistory->priceId = null; $priceHistory->applied = $effectiveDate; } } $details = new ArrayCollection(); $found = []; foreach ($prices as $price) { if ($price['type'] && $price['priceLinux']) { $item = new PriceEntity(); $item->instanceType = $price['type']; $item->os = PriceEntity::OS_LINUX; $item->name = $price['name']; $item->cost = floatval($price['priceLinux']); $found[$item->instanceType . '-' . $item->os] = $item; $details->append($item); } if ($price['type'] && $price['priceWindows']) { $item = new PriceEntity(); $item->instanceType = $price['type']; $item->os = PriceEntity::OS_WINDOWS; $item->name = $price['name']; $item->cost = floatval($price['priceWindows']); $found[$item->instanceType . '-' . $item->os] = $item; $details->append($item); } } //Gets actual pricing on date from databse $collection = $this->getContainer()->analytics->prices->getActualPrices($platform, $cloudLocation, $priceHistory->url, $priceHistory->applied); //Compares if some price has been changed. if (!$collection->count()) { //There aren't any prices yet. We need to save them. $bChanged = true; } else { $bChanged = false; //For each previous price compare difference with new one foreach ($collection as $priceEntity) { $key = $priceEntity->instanceType . '-' . $priceEntity->os; if (isset($found[$key])) { if (abs($found[$key]->cost - $priceEntity->cost) > 9.0E-7) { unset($found[$key]); $bChanged = true; break; } unset($found[$key]); } else { $bChanged = true; break; } } //Some new price for new instance type is added while other prices reman untouched if (!$bChanged && count($found)) { foreach ($found as $priceEntity) { //Zerro prices should not be taken into account if they don't exist before if ($priceEntity->cost >= 1.0E-6) { $bChanged = true; break; } } } } if ($bChanged) { //Saving actually only when there is some change $priceHistory->setDetails($details); $service->save($priceHistory); $this->getContainer()->analytics->events->fireChangeCloudPricingEvent($platform, $url); } if ($priceHistory->platform == SERVER_PLATFORMS::EC2) { SettingEntity::setValue(SettingEntity::ID_FORBID_AUTOMATIC_UPDATE_AWS_PRICES, $forbidAutomaticUpdate ? '1' : '0'); } $this->response->success('Prices have been updated'); }
/** * Gets actual prices on specified date * * @param string $platform The name of the cloud platform * @param string $cloudLocation The location of the cloud * @param string $url optional The keystone url for the private clouds * @param \DateTime $applied optional The date in UTC * @param int $accountId optional ID of the account (global level by default) * @param string $instanceType optional Type of the instance * @param int $os optional Os type [Linux - 0, Windows - 1] * @return ArrayCollection Returns a collection of price entities */ public function getActualPrices($platform, $cloudLocation, $url = null, \DateTime $applied = null, $accountId = null, $instanceType = null, $os = null) { $ret = new ArrayCollection(); if (!$applied instanceof \DateTime) { $applied = new \DateTime('now', new \DateTimeZone('UTC')); } $accountId = $accountId ?: 0; $url = $url ?: ''; $sql = "\n SELECT ph.cloud_location, ph.applied, ph.deny_override,\n p.instance_type, p.os, p.price_id, p.cost\n FROM price_history ph\n JOIN prices p ON p.price_id = ph.price_id\n LEFT JOIN price_history ph2 ON ph2.platform = ph.platform\n AND ph2.cloud_location = ph.cloud_location\n AND ph2.account_id = ph.account_id\n AND ph2.url = ph.url\n AND ph2.applied > ph.applied AND ph2.applied <= ?\n LEFT JOIN prices p2 ON p2.price_id = ph2.price_id\n AND p2.instance_type = p.instance_type\n AND p2.os = p.os\n WHERE ph.account_id = ? AND p2.price_id IS NULL\n AND ph.platform = ?\n AND ph.cloud_location = ?\n AND ph.url = ?\n AND ph.applied <= ?\n "; if ($instanceType !== null) { $sql .= " AND p.instance_type = '{$instanceType}'"; } if ($os !== null) { $sql .= " AND p.os =" . $os; } $res = $this->cadb->Execute($sql, [$applied->format('Y-m-d'), $accountId, $platform, $cloudLocation, $this->normalizeUrl($url), $applied->format('Y-m-d')]); $ph = []; while ($rec = $res->FetchRow()) { $item = new PriceEntity(); $item->load($rec); if (!isset($ph[$item->priceId])) { $rec['platform'] = $platform; $rec['url'] = $url; $ph[$item->priceId] = new PriceHistoryEntity(); $ph[$item->priceId]->load($rec); } $item->setPriceHistory($ph[$item->priceId]); $ret->append($item); } return $ret; }
/** * Gets the list of the Cost Centers which correspond to Account * * @return \Scalr\Model\Collections\ArrayCollection Returns collection of the entities */ public function getCostCenters() { $ccs = new ArrayCollection(); foreach (AccountCostCenterEntity::findByAccountId($this->id) as $accountCc) { $cc = CostCentreEntity::findPk($accountCc->ccId); if (!$cc instanceof CostCentreEntity) { continue; } $ccs->append($cc); } return $ccs; }
/** * Gets event list * * @param \DateTime $start Start date of the period * @param \DateTime $end End date of the period * @param string $ccId optional Cost center id * @param string $projectId optional Project id * @return ArrayCollection Returns collection of the TimelineEventEntity objects */ public function get($start, $end, $ccId = null, $projectId = null) { $eventEntity = new TimelineEventEntity(); $joinData = $this->buildJoin($ccId, $projectId); $fields = ''; foreach ($eventEntity->getIterator()->fields() as $field) { $fields .= ',`' . $field->column->name . '`'; } $result = $this->db->Execute("\n SELECT " . ltrim($fields, ',') . "\n FROM (\n SELECT " . $eventEntity->fields('e') . "\n FROM " . $eventEntity->table('e') . (isset($joinData['join']) ? $joinData['join'] : '') . "\n WHERE e.dtime BETWEEN " . $eventEntity->qstr('dtime', $start) . " AND " . $eventEntity->qstr('dtime', $end) . "\n " . (isset($joinData['join']) ? "\n UNION\n SELECT " . $eventEntity->fields('e2') . "\n FROM " . $eventEntity->table('e2') . "\n WHERE e2.event_type = " . $eventEntity::EVENT_TYPE_CHANGE_CLOUD_PRICING . "\n AND e2.dtime BETWEEN " . $eventEntity->qstr('dtime', $start) . " AND " . $eventEntity->qstr('dtime', $end) : "") . "\n ) p\n ORDER BY p.dtime DESC\n "); $events = new ArrayCollection(); while ($record = $result->FetchRow()) { $item = new TimelineEventEntity(); $item->load($record); $events->append($item); } return $events; }
/** * Finds webhook configs by even * * @param string $eventName Event type * @param int $farmId The identifier of the farm * @param int $accountId The identifier of the client's account * @param int $envId The identifier of the environment * @return ArrayCollection Gets collection of the WebhookConfig objects */ public static function findByEvent($eventName, $farmId, $accountId, $envId) { $ret = new ArrayCollection(); $cfg = new self(); $res = $cfg->db()->Execute("\n SELECT " . $cfg->fields('c') . "\n FROM " . $cfg->table() . " c\n JOIN `webhook_config_events` ce ON ce.webhook_id = c.webhook_id\n WHERE ce.event_type = ?\n AND (\n (c.account_id = ? AND c.env_id = ? AND `level` = 4) OR\n (c.account_id = ? AND c.env_id IS NULL AND `level` = 2) OR\n (c.account_id IS NULL AND c.env_id IS NULL AND `level` = 1)\n )\n AND EXISTS (\n SELECT 1 FROM `webhook_config_farms` cf\n WHERE cf.webhook_id = c.webhook_id\n AND (cf.farm_id = 0 OR cf.farm_id = ?)\n )\n ", array($eventName, $accountId, $envId, $accountId, $farmId)); while ($item = $res->FetchRow()) { $cfg = new self(); $cfg->load($item); $ret->append($cfg); unset($cfg); } return $ret; }
/** * Finds projects by key * It searches by name or billing number * * @param string $key optional Search key * @return ArrayCollection Returns collection of the ProjectEntity objects */ public function findByKey($key = null) { if (is_null($key) || $key === '') { return $this->all(); } $collection = new ArrayCollection(); $projectEntity = new ProjectEntity(); //Includes archived projects $projectPropertyEntity = new ProjectPropertyEntity(); //Cost center entity $ccEntity = new CostCentreEntity(); $rs = $this->db->Execute("\n SELECT " . $projectEntity->fields('p') . ", " . $ccEntity->fields('c', true) . "\n FROM " . $projectEntity->table('p') . "\n LEFT JOIN " . $ccEntity->table('c') . " ON c.`cc_id` = p.`cc_id`\n WHERE p.`name` LIKE ?\n OR EXISTS (\n SELECT 1 FROM " . $projectPropertyEntity->table('pp') . "\n WHERE `pp`.project_id = `p`.`project_id`\n AND `pp`.`name` = ? AND `pp`.`value` LIKE ?\n )\n ", ['%' . $key . '%', ProjectPropertyEntity::NAME_BILLING_CODE, '%' . $key . '%']); while ($rec = $rs->FetchRow()) { $item = new ProjectEntity(); $item->load($rec); if ($rec['c_cc_id']) { $cc = new CostCentreEntity(); $cc->load($rec, 'c'); $item->setCostCenter($cc); } $collection->append($item); } return $collection; }
/** * Finds collection of the values by any key * * @param array $criteria optional The search criteria. * @param array $order optional The results order * @param int $limit optional The records limit * @param int $offset optional The offset * @param bool $countRecords optional True to calculate totat number of the records without limit * @return ArrayCollection Returns collection of the entities */ private function _find(array $criteria = null, array $order = null, $limit = null, $offset = null, $countRecords = null) { $class = get_class($this); $iterator = $this->getIterator(); $stmtFields = ''; $arguments = array(); if (!empty($criteria)) { $built = $this->_buildQuery($criteria); } if (!empty($order)) { $sOrder = ''; foreach ($order as $k => $v) { $field = $iterator->getField($k); if (!$field) { throw new \InvalidArgumentException(sprintf("Property %s does not exist in %s", $k, $class)); } $sOrder .= ', ' . $field->getColumnName() . ($v ? '' : ' DESC'); } $sOrder = $sOrder != '' ? 'ORDER BY ' . substr($sOrder, 2) : ''; } $bcnt = $countRecords && isset($limit); $stmt = "\n SELECT " . ($bcnt ? 'SQL_CALC_FOUND_ROWS ' : '') . $this->fields() . " FROM {$this->table()}\n WHERE " . (!empty($built['where']) ? $built['where'] : '1=1') . "\n " . (!empty($sOrder) ? $sOrder : "") . "\n " . (isset($limit) ? "LIMIT " . ($offset ? intval($offset) . ',' : '') . intval($limit) : "") . "\n "; $res = $this->db()->Execute($stmt); $ret = new ArrayCollection(); if ($bcnt) { $ret->totalNumber = $this->db()->getOne('SELECT FOUND_ROWS()'); } else { if ($countRecords) { $ret->totalNumber = $res->RowCount(); } } while ($item = $res->FetchRow()) { $obj = new $class(); $obj->load($item); $ret->append($obj); unset($obj); } return $ret; }
/** * Finds cost centres by key * It searches by name or billing number * * @param string $key optional Search key * @param array $criteria optional Search criteria * @param bool $ignoreCache optional Should it ignore cache or not * @return ArrayCollection Returns collection of the CostCentreEntity objects */ public function findByKey($key = null, $criteria = null, $ignoreCache = false) { if (is_null($key) || $key === '') { return $this->all(false, $criteria, $ignoreCache); } $collection = new ArrayCollection(); $ccEntity = new CostCentreEntity(); $ccPropertyEntity = new CostCentrePropertyEntity(); $projectEntity = new ProjectEntity(); $where = ''; $join = ''; $this->parseFindCriteria($criteria, $join, $where); $rs = $this->db->Execute("\n SELECT " . $ccEntity->fields('c') . "\n FROM " . $ccEntity->table('c') . " " . $join . "\n WHERE (c.`name` LIKE ?\n OR EXISTS (\n SELECT 1 FROM " . $ccPropertyEntity->table('cp') . "\n WHERE `cp`.cc_id = `c`.`cc_id`\n AND `cp`.`name` = ? AND `cp`.`value` LIKE ?\n ))\n " . $where . "\n ", ['%' . $key . '%', CostCentrePropertyEntity::NAME_BILLING_CODE, '%' . $key . '%']); while ($rec = $rs->FetchRow()) { $item = new CostCentreEntity(); $item->load($rec); $collection->append($item); } return $collection; }
/** * Finds farms by key * It searches by name * * @param int $envId Current enviroment id * @param string $key optional Search key * @return ArrayCollection Returns collection of the farm objects */ public function findFarmsByKey($envId, $key = null) { $statement = "\n SELECT *\n FROM farms f\n WHERE f.env_id = " . intval($envId) . "\n "; if (!is_null($key) && $key !== '') { $statement .= "\n AND f.name LIKE '%" . $this->db->escape($key) . "%'\n "; } $collection = new ArrayCollection(); $rs = $this->db->Execute($statement); while ($rec = $rs->FetchRow()) { $item = DBFarm::loadFields($rec); $collection->append($item); } return $collection; }
/** * Finds cost centres by key * It searches by name or billing number * * @param string $key optional Search key * @return ArrayCollection Returns collection of the CostCentreEntity objects */ public function findByKey($key = null) { if (is_null($key) || $key === '') { return $this->all(); } $collection = new ArrayCollection(); $ccEntity = new CostCentreEntity(); $rs = $this->db->Execute("\n SELECT " . $ccEntity->fields('c') . "\n FROM " . $ccEntity->table('c') . "\n WHERE c.`name` LIKE ?\n OR EXISTS (\n SELECT 1 FROM cc_properties cp\n WHERE `cp`.cc_id = `c`.`cc_id`\n AND `cp`.`name` = ? AND `cp`.`value` LIKE ?\n )\n ", ['%' . $key . '%', CostCentrePropertyEntity::NAME_BILLING_CODE, '%' . $key . '%']); while ($rec = $rs->FetchRow()) { $item = new CostCentreEntity(); $item->load($rec); $collection->append($item); } return $collection; }