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