/** * Processes data for facilities (stations and player built outposts) * * @param stdClass $item to be processed * * @return string the UPSERT SQL queries */ protected function processDataItemToSQL(\stdClass $item) { $exceptionClass = Config::getIveeClassName('CrestException'); $sdeClass = Config::getIveeClassName('SDE'); $update = []; if (!isset($item->facilityID)) { throw new $exceptionClass("facilityID missing from facilities CREST data"); } $facilityId = (int) $item->facilityID; if (!isset($item->owner)) { throw new $exceptionClass("owner missing from facilities CREST data"); } $update['ownerID'] = (int) $item->owner->id; //only store if station is conquerable if ($facilityId >= 61000000 or $facilityId >= 60014861 and $facilityId <= 60014928) { $update['solarSystemID'] = (int) $item->solarSystem->id; $update['stationName'] = $item->name; $update['stationTypeID'] = (int) $item->type->id; $table = Config::getIveeDbName() . '.outposts'; } else { return ''; } $insert = $update; $insert['facilityID'] = $facilityId; $this->updatedIds[] = $facilityId; return $sdeClass::makeUpsertQuery($table, $insert, $update); }
/** * Fetches the weekle averages of vol and tx, triggering a CREST history update if data is too old * * @param int $typeId of the type * @param int $regionId of the region * * @return array */ protected function getWeeklyAverages($typeId, $regionId) { $sql = "SELECT UNIX_TIMESTAMP(lastHistUpdate) as lastHistUpdate,\n avgVol, avgTx\n FROM " . Config::getIveeDbName() . ".trackedMarketData\n WHERE typeID = " . $typeId . "\n AND regionID = " . $regionId . ";"; $row = static::$sde->query($sql)->fetch_assoc(); //if history update was run today, use that value if ($row['lastHistUpdate'] > mktime(0, 0, 0)) { return array('avgVol' => (double) $row['avgVol'], 'avgTx' => (double) $row['avgTx'], 'lastHistUpdate' => (int) $row['lastHistUpdate'], 'lastPriceUpdate' => time()); } //history data was too old, trigger CREST update $this->marketProcessor->getNewestHistoryData($typeId, $regionId, false); //fetch from DB again $row = static::$sde->query($sql)->fetch_assoc(); return array('avgVol' => (double) $row['avgVol'], 'avgTx' => (double) $row['avgTx'], 'lastHistUpdate' => (int) $row['lastHistUpdate'], 'lastPriceUpdate' => time()); }
/** * Processes data objects to SQL * * @param stdClass $item to be processed * * @return string the SQL queries */ protected function processDataItemToSQL(\stdClass $item) { $exceptionClass = Config::getIveeClassName('CrestException'); if (!isset($item->solarSystem->id)) { throw new $exceptionClass('systemID missing in Industry Systems CREST data'); } $systemId = (int) $item->solarSystem->id; $update = []; foreach ($item->systemCostIndices as $indexObj) { if (!isset($indexObj->activityID)) { throw new $exceptionClass('activityID missing in Industry Systems CREST data for systemID ' . $systemId); } if (!isset($indexObj->costIndex)) { throw new $exceptionClass('costIndex missing in Industry Systems CREST data for systemID ' . $systemId); } switch ($indexObj->activityID) { case 1: $update['manufacturingIndex'] = (double) $indexObj->costIndex; break; case 3: $update['teResearchIndex'] = (double) $indexObj->costIndex; break; case 4: $update['meResearchIndex'] = (double) $indexObj->costIndex; break; case 5: $update['copyIndex'] = (double) $indexObj->costIndex; break; case 7: $update['reverseIndex'] = (double) $indexObj->costIndex; break; case 8: $update['inventionIndex'] = (double) $indexObj->costIndex; break; default: throw new $exceptionClass('Unknown activityID received from Industry Systems CREST data for systemID ' . $systemId); } } $insert = $update; $insert['systemID'] = $systemId; $insert['date'] = date('Y-m-d'); $this->updatedIds[] = $systemId; $sdeClass = Config::getIveeClassName('SDE'); return $sdeClass::makeUpsertQuery(Config::getIveeDbName() . '.systemIndustryIndices', $insert, $update); }
/** * Processes data objects to SQL. * * @param stdClass $item to be processed * * @return string the SQL queries */ protected function processDataItemToSQL(\stdClass $item) { $exceptionClass = Config::getIveeClassName('CrestException'); if (!isset($item->type->id)) { throw new $exceptionClass('typeID missing in Market Prices CREST data'); } $typeId = (int) $item->type->id; $this->updatedIds[] = $typeId; $update = array(); if (!isset($item->adjustedPrice)) { throw new $exceptionClass('Missing adjustedPrice in CREST MarketPrices data for typeID ' . $typeId); } $update['adjustedPrice'] = (double) $item->adjustedPrice; if (isset($item->averagePrice)) { $update['averagePrice'] = (double) $item->averagePrice; } $insert = $update; $insert['typeID'] = $typeId; $insert['date'] = date('Y-m-d'); $sdeClass = Config::getIveeClassName('SDE'); return $sdeClass::makeUpsertQuery(Config::getIveeDbName() . '.globalPrices', $insert, $update); }
/** * Inserts or updates the pricing values in the DB. * * @param array $priceData with the values * @param int $typeId * @param int $regionId * * @return void */ protected function upsertPriceDb(array $priceData, $typeId, $regionId) { $sdeClass = Config::getIveeClassName('SDE'); //clear columns that don't belong in this update if (isset($priceData['avgVol'])) { unset($priceData['avgVol']); } if (isset($priceData['avgTx'])) { unset($priceData['avgTx']); } if (isset($priceData['lastHistUpdate'])) { unset($priceData['lastHistUpdate']); } if (isset($priceData['lastPriceUpdate'])) { unset($priceData['lastPriceUpdate']); } if (count($priceData) > 0) { //check if row already exists $res = static::$sde->query("SELECT regionID\n FROM " . Config::getIveeDbName() . ".marketPrices\n WHERE regionID = " . (int) $regionId . "\n AND typeID = " . (int) $typeId . "\n AND date = '" . date('Y-m-d', time()) . "';"); $where = array('typeID' => $typeId, 'regionID' => $regionId, 'date' => date('Y-m-d', time())); //if row already exists if ($res->num_rows == 1) { //build update query $this->submitSql($sdeClass::makeUpdateQuery(Config::getIveeDbName() . '.marketPrices', $priceData, $where)); } else { //build insert query $this->submitSql($sdeClass::makeUpsertQuery(Config::getIveeDbName() . '.marketPrices', array_merge($priceData, $where))); } } //add stored procedure call to complete the update $this->submitSql("CALL " . Config::getIveeDbName() . ".completePriceUpdate(" . (int) $typeId . ", " . (int) $regionId . ", '" . date('Y-m-d H:i:s', time()) . "');"); if ($this->verboseBatch) { static::printTypeAndRegion('P', $typeId, $regionId); } //TODO: Decide if we should invalidate caches or not. }
/** * Gets all necessary data from SQL. * * @return array with attributes queried from DB * @throws \iveeCore\Exceptions\NoPriceDataAvailableException when a typeId is not found */ protected function queryAttributes() { //lookup IveeCore class $sdeClass = Config::getIveeClassName('SDE'); $row = $sdeClass::instance()->query("SELECT UNIX_TIMESTAMP(date) as priceDate,\n averagePrice,\n adjustedPrice\n FROM " . Config::getIveeDbName() . ".globalPrices\n WHERE typeID = " . $this->id . "\n ORDER BY date DESC LIMIT 1;")->fetch_assoc(); if (empty($row)) { self::throwException('NoPriceDataAvailableException', "No global price data for " . $this->getType()->getName() . " (typeId=" . $this->id . ") found"); } return $row; }
/** * Fetches the typeIds of the items that need updating in a region * * @param int $regionId to be checked * @param int $cutoffTs the unix timestamp to be used to decide if data is too old * @param string $dateColumn the DB column to check the timestamp on, either 'lastHistUpdate' or 'lastPriceUpdate' * @param \iveeCrest\EndpointHandler $eph to be used * * @return array */ protected static function getTypeIdsToUpdate($regionId, $cutoffTs, $dateColumn, EndpointHandler $eph) { //get matket typeIds from CREST $marketTypeIds = array_keys($eph->getMarketTypeHrefs()); //get the subset Ids that need updating and are not Dust-only $res = static::$sde->query("SELECT typeID\n FROM invTypes\n WHERE typeID IN (" . implode(', ', $marketTypeIds) . ")\n AND typeID < 350000\n AND typeID NOT IN (\n SELECT typeID\n FROM " . Config::getIveeDbName() . ".trackedMarketData\n WHERE regionID = " . (int) $regionId . "\n AND " . $dateColumn . " > '" . date('Y-m-d H:i:s', $cutoffTs) . "'\n )\n ORDER BY typeID ASC;"); $ret = []; while ($tmp = $res->fetch_array(MYSQL_NUM)) { $ret[] = (int) $tmp[0]; } return $ret; }
/** * Fetches the data from DB. * * @return array * @throws \iveeCore\Exceptions\NoPriceDataAvailableException if no region market data is found */ protected static function getDataFromDb($typeId, $regionId) { $sdeClass = Config::getIveeClassName('SDE'); //get market data $row = $sdeClass::instance()->query("SELECT\n atp.typeID,\n UNIX_TIMESTAMP(lastPriceUpdate) AS lastPriceUpdate,\n UNIX_TIMESTAMP(lastHistUpdate) AS lastHistUpdate,\n avgVol,\n avgTx,\n ap.sell,\n ap.buy,\n ap.supplyIn5,\n ap.demandIn5,\n ap.avgSell5OrderAge,\n ap.avgBuy5OrderAge\n FROM " . Config::getIveeDbName() . ".trackedMarketData as atp\n LEFT JOIN " . Config::getIveeDbName() . ".marketPrices AS ap ON atp.newestPriceData = ap.id\n WHERE atp.typeID = " . (int) $typeId . "\n AND atp.regionID = " . (int) $regionId . ";")->fetch_assoc(); if (empty($row)) { self::throwException('NoPriceDataAvailableException', "No region market data for typeId=" . (int) $typeId . " and regionId=" . (int) $regionId . " found"); } return $row; }
/** * Constructor. Use getByIdAndRegion() instead. * * @param int $typeId of type * @param int $regionId of the region * * @throws \iveeCore\Exceptions\NotOnMarketException if requested type is not on market * @throws \iveeCore\Exceptions\NoPriceDataAvailableException if no region market data is found */ protected function __construct($typeId, $regionId) { $this->id = (int) $typeId; $this->regionId = (int) $regionId; $type = Type::getById($this->id); if (!$type->onMarket()) { $this->throwNotOnMarketException($type); } //get timestamp for today, 0h 05m $ts = mktime(0, 5, 0); //calc expiry as the next occurance of 0h 05m $this->expiry = time() < $ts ? $ts : $ts + 24 * 3600; //lookup SDE class $sdeClass = Config::getIveeClassName('SDE'); $sde = $sdeClass::instance(); $iveeDbName = Config::getIveeDbName(); $row = $sde->query('SELECT UNIX_TIMESTAMP(lastHistUpdate) as lastHistUpdate ' . 'FROM ' . $iveeDbName . '.trackedMarketData ' . 'WHERE typeID = ' . $this->id . ' AND regionID = ' . $this->regionId . ';')->fetch_assoc(); $this->lastHistUpdate = (int) $row['lastHistUpdate']; //fetch the complete history $this->loadFromMarketHistory($sde, $iveeDbName); $this->loadFromMarketPrices($sde, $iveeDbName); }