/**
* 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());
}
/**
* 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;
}
/**
* Loads the names of types and regions.
*
* @return void
*/
protected static function loadNames()
{
//load IDs of items to track on market
$typeRes = static::$sde->query("SELECT typeID, typeName\n FROM invTypes\n WHERE (marketGroupID IS NOT NULL OR published = 1);");
while ($tmp = $typeRes->fetch_array(MYSQL_NUM)) {
static::$marketTypes[(int) $tmp[0]] = $tmp[1];
}
//load regionIDs
$regionRes = static::$sde->query("SELECT regionID, regionName\n FROM mapRegions;");
while ($tmp = $regionRes->fetch_array(MYSQL_NUM)) {
static::$regions[(int) $tmp[0]] = $tmp[1];
}
}
/**
* Loads the data from the marketPrices table
*
* @param \iveeCore\SDE $sde for the DB connection
* @param string $iveeDbName for the iveeCore DB schema name
*
* @return void
*/
protected function loadFromMarketPrices(SDE $sde, $iveeDbName)
{
$res = $sde->query('SELECT
UNIX_TIMESTAMP(date) as date,
sell,
buy,
supplyIn5,
demandIn5,
avgSell5OrderAge,
avgBuy5OrderAge
FROM ' . $iveeDbName . '.marketPrices
WHERE typeID = ' . $this->id . '
AND regionID = ' . $this->regionId . ';');
while ($row = $res->fetch_assoc()) {
$date = (int) $row['date'];
//save the min and max dates
if ($date < $this->oldestDate or !isset($this->oldestDate)) {
$this->oldestDate = $date;
}
if ($date > $this->newestDate) {
$this->newestDate = $date;
}
//we store the values by column instead of row to conserve memory
if (isset($row['sell'])) {
$this->sell[$date] = (double) $row['sell'];
}
if (isset($row['buy'])) {
$this->buy[$date] = (double) $row['buy'];
}
if (isset($row['supplyIn5'])) {
$this->supplyIn5[$date] = (int) $row['supplyIn5'];
}
if (isset($row['demandIn5'])) {
$this->demandIn5[$date] = (int) $row['demandIn5'];
}
if (isset($row['avgSell5OrderAge'])) {
$this->avgSell5OrderAge[$date] = (int) $row['avgSell5OrderAge'];
}
if (isset($row['avgBuy5OrderAge'])) {
$this->avgBuy5OrderAge[$date] = (int) $row['avgBuy5OrderAge'];
}
}
}
public function testSde()
{
$this->assertTrue(SDE::instance() instanceof SDE);
}
