function ParseAuctionData($house, $snapshot, &$json)
{
global $maxPacketSize;
global $houseRegionCache;
global $equipBaseItemLevel;
global $usefulBonusesCache, $enoughBonusesSeenCache;
global $TIMELEFT_ENUM;
$snapshotString = date('Y-m-d H:i:s', $snapshot);
$startTimer = microtime(true);
$ourDb = DBConnect(true);
$region = $houseRegionCache[$house]['region'];
$existingIds = [];
$stmt = $ourDb->prepare(EXISTING_SQL);
$stmt->bind_param('i', $house);
$stmt->execute();
$id = $bid = $buy = $timeLeft = $infoKey = null;
$stmt->bind_result($id, $bid, $buy, $timeLeft, $infoKey);
while ($stmt->fetch()) {
$existingIds[$id] = [$bid, $buy, $timeLeft, $infoKey];
}
$stmt->close();
$stmt = $ourDb->prepare('SELECT id, species, breed FROM tblAuctionPet WHERE house = ?');
$stmt->bind_param('i', $house);
$stmt->execute();
$result = $stmt->get_result();
$existingPetIds = DBMapArray($result);
$stmt->close();
$naiveMax = 0;
$lowMax = -1;
$highMax = -1;
$hasRollOver = false;
$jsonAuctions = [];
if (isset($json['auctions']['auctions'])) {
$jsonAuctions =& $json['auctions']['auctions'];
} elseif (isset($json['auctions']) && count($json['auctions']) > 5) {
$jsonAuctions =& $json['auctions'];
}
if ($jsonAuctions) {
$auctionCount = count($jsonAuctions);
for ($x = 0; $x < $auctionCount; $x++) {
$auctionId = $jsonAuctions[$x]['auc'];
$naiveMax = max($naiveMax, $auctionId);
if ($auctionId < 0x20000000) {
$lowMax = max($lowMax, $auctionId);
}
if ($auctionId > 0x60000000) {
$highMax = max($highMax, $auctionId);
}
}
}
if ($lowMax != -1 && $highMax != -1) {
$hasRollOver = true;
$max = $lowMax;
// rolled over
} else {
$max = $naiveMax;
}
unset($naiveMax, $lowMax, $highMax);
$stmt = $ourDb->prepare('SELECT ifnull(maxid,0) FROM tblSnapshot s WHERE house = ? AND updated = (SELECT max(s2.updated) FROM tblSnapshot s2 WHERE s2.house = ? AND s2.updated < ?)');
$stmt->bind_param('iis', $house, $house, $snapshotString);
$stmt->execute();
$stmt->bind_result($lastMax);
if ($stmt->fetch() !== true) {
$lastMax = 0;
}
$stmt->close();
$stmt = $ourDb->prepare('UPDATE tblSnapshot SET maxid = ? WHERE house = ? AND updated = ?');
$stmt->bind_param('iis', $max, $house, $snapshotString);
$stmt->execute();
$stmt->close();
$stmt = $ourDb->prepare('SELECT unix_timestamp(updated) updated, maxid FROM tblSnapshot WHERE house = ? AND updated BETWEEN timestampadd(HOUR, -49, ?) AND ? ORDER BY updated ASC');
$stmt->bind_param('iss', $house, $snapshotString, $snapshotString);
$stmt->execute();
$result = $stmt->get_result();
$snapshotList = DBMapArray($result, null);
$stmt->close();
$prevSnapshot = $snapshot;
if (count($snapshotList)) {
$prevSnapshot = intval($snapshotList[count($snapshotList) - 1]['updated'], 10);
}
$snapshotWindow = $snapshot - $prevSnapshot;
$expiredLength = 1;
// any missing shorts can be considered expired
if ($snapshotWindow > 1800) {
// 30 mins
$expiredLength = 2;
// any missing shorts or mediums can be expired
}
$sqlStart = 'REPLACE INTO tblAuction (house, id, item, quantity, bid, buy, seller, timeleft) VALUES ';
$sqlStartPet = 'REPLACE INTO tblAuctionPet (house, id, species, breed, `level`, quality) VALUES ';
$sqlStartExtra = 'REPLACE INTO tblAuctionExtra (house, id, `rand`, `seed`, `context`, `lootedlevel`, `level`, `bonusset`';
for ($x = 1; $x <= MAX_BONUSES; $x++) {
$sqlStartExtra .= ", bonus{$x}";
}
$sqlStartExtra .= ') VALUES ';
$sqlStartBonusesSeen = 'INSERT INTO tblItemBonusesSeen (item, bonusset, bonus1, bonus2, bonus3, bonus4, observed) VALUES ';
$sqlEndBonusesSeen = ' ON DUPLICATE KEY UPDATE observed = observed + 1';
$sqlStartLevelsSeen = 'INSERT IGNORE INTO tblItemLevelsSeen (item, bonusset, `level`) VALUES ';
$totalAuctions = 0;
$itemInfo = array();
$petInfo = array();
$sellerInfo = array();
$expiredItemInfo = array();
if ($jsonAuctions) {
$auctionCount = count($jsonAuctions);
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " prepping {$auctionCount} auctions");
$sellerCount = 0;
for ($x = 0; $x < $auctionCount; $x++) {
$auction =& $jsonAuctions[$x];
if ($auction['owner'] == '???') {
continue;
}
if (!isset($sellerInfo[$auction['ownerRealm']])) {
$sellerInfo[$auction['ownerRealm']] = array();
}
if (!isset($sellerInfo[$auction['ownerRealm']][$auction['owner']])) {
$sellerCount++;
$sellerInfo[$auction['ownerRealm']][$auction['owner']] = array('new' => 0, 'total' => 0, 'id' => 0, 'items' => []);
}
$sellerInfo[$auction['ownerRealm']][$auction['owner']]['total']++;
if ((!$hasRollOver || $auction['auc'] < 0x20000000) && $auction['auc'] > $lastMax) {
$sellerInfo[$auction['ownerRealm']][$auction['owner']]['new']++;
$itemId = intval($auction['item'], 10);
if (!isset($sellerInfo[$auction['ownerRealm']][$auction['owner']]['items'][$itemId])) {
$sellerInfo[$auction['ownerRealm']][$auction['owner']]['items'][$itemId] = [0, 0];
}
$sellerInfo[$auction['ownerRealm']][$auction['owner']]['items'][$itemId][0]++;
$sellerInfo[$auction['ownerRealm']][$auction['owner']]['items'][$itemId][1] += $auction['quantity'];
}
}
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " getting {$sellerCount} seller IDs");
GetSellerIds($region, $sellerInfo, $snapshot);
$sql = $sqlPet = $sqlExtra = $sqlBonusesSeen = $sqlLevelsSeen = '';
$delayedAuctionSql = [];
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " parsing {$auctionCount} auctions");
while ($auction = array_pop($jsonAuctions)) {
if (isset($auction['petBreedId'])) {
$auction['petBreedId'] = ($auction['petBreedId'] - 3) % 10 + 3;
// squash gender
}
$auction['timeLeft'] = isset($TIMELEFT_ENUM[$auction['timeLeft']]) ? $TIMELEFT_ENUM[$auction['timeLeft']] : 0;
$auction['lootedLevel'] = null;
if (isset($auction['modifiers'])) {
foreach ($auction['modifiers'] as $modObj) {
if (isset($modObj['type']) && $modObj['type'] == 9) {
$auction['lootedLevel'] = intval($modObj['value']);
}
}
}
$totalAuctions++;
$itemInfoKey = false;
$bonusSet = 0;
$bonuses = [];
$bonusItemLevel = null;
$priceScaling = null;
if (!isset($auction['petSpeciesId']) && isset($equipBaseItemLevel[$auction['item']]) && isset($auction['bonusLists'])) {
for ($y = 0; $y < count($auction['bonusLists']); $y++) {
if (isset($auction['bonusLists'][$y]['bonusListId']) && $auction['bonusLists'][$y]['bonusListId']) {
$bonuses[] = intval($auction['bonusLists'][$y]['bonusListId'], 10);
}
}
$bonuses = array_unique($bonuses, SORT_NUMERIC);
sort($bonuses, SORT_NUMERIC);
$bonusSet = $bonuses ? GetBonusSet($bonuses) : 0;
$bonusItemLevel = GetBonusItemLevel($bonuses, $equipBaseItemLevel[$auction['item']], $auction['lootedLevel']);
if ($bonusItemLevel - $equipBaseItemLevel[$auction['item']] != 0) {
$priceScaling = pow(1.15, ($bonusItemLevel - $equipBaseItemLevel[$auction['item']]) / 15);
}
}
if ($auction['buyout'] != 0) {
if (isset($auction['petSpeciesId'])) {
if (!isset($petInfo[$auction['petSpeciesId']][$auction['petBreedId']])) {
$petInfo[$auction['petSpeciesId']][$auction['petBreedId']] = array('a' => array(), 'tq' => 0);
}
$petInfo[$auction['petSpeciesId']][$auction['petBreedId']]['a'][] = array('q' => $auction['quantity'], 'p' => $auction['buyout']);
$petInfo[$auction['petSpeciesId']][$auction['petBreedId']]['tq'] += $auction['quantity'];
} else {
$itemInfoKey = str_pad($auction['item'], ITEM_ID_PAD, '0', STR_PAD_LEFT) . ":{$bonusSet}";
if (!isset($itemInfo[$itemInfoKey])) {
$itemInfo[$itemInfoKey] = array('a' => array(), 'tq' => 0);
}
$itemInfo[$itemInfoKey]['a'][] = array('q' => $auction['quantity'], 'p' => isset($priceScaling) ? $auction['buyout'] / $priceScaling : $auction['buyout']);
$itemInfo[$itemInfoKey]['tq'] += $auction['quantity'];
}
}
if (isset($existingIds[$auction['auc']])) {
$needUpdate = $auction['bid'] != $existingIds[$auction['auc']][EXISTING_COL_BID];
$needUpdate |= $auction['timeLeft'] != $existingIds[$auction['auc']][EXISTING_COL_TIMELEFT];
unset($existingIds[$auction['auc']]);
unset($existingPetIds[$auction['auc']]);
if (!$needUpdate) {
continue;
}
} else {
// new auction
if ($auction['buyout'] != 0) {
if ($itemInfoKey !== false) {
if (!isset($expiredItemInfo['n'][$itemInfoKey])) {
$expiredItemInfo['n'][$itemInfoKey] = 0;
}
$expiredItemInfo['n'][$itemInfoKey]++;
}
}
if (isset($equipBaseItemLevel[$auction['item']])) {
if (!isset($enoughBonusesSeenCache["{$auction['item']}:{$bonusSet}"])) {
$usefulBonuses = [];
foreach ($bonuses as $bonus) {
if (isset($usefulBonusesCache[$bonus])) {
$usefulBonuses[$bonus] = $bonus;
}
}
sort($usefulBonuses, SORT_NUMERIC);
switch (count($usefulBonuses)) {
case 0:
$usefulBonuses[] = 0;
case 1:
$usefulBonuses[] = 0;
case 2:
$usefulBonuses[] = 0;
case 3:
$usefulBonuses[] = 0;
}
$thisSql = sprintf('(%u,%u,%u,%u,%u,%u,1)', $auction['item'], $bonusSet, $usefulBonuses[0], $usefulBonuses[1], $usefulBonuses[2], $usefulBonuses[3]);
if (strlen($sqlBonusesSeen) + 5 + strlen($thisSql) + strlen($sqlEndBonusesSeen) > $maxPacketSize) {
if (GetDBLock($ourDb, DB_LOCK_SEEN_BONUSES)) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating seen bonuses (" . round($totalAuctions / $auctionCount * 100) . '%)');
DBQueryWithError($ourDb, $sqlBonusesSeen . $sqlEndBonusesSeen);
ReleaseDBLock($ourDb, DB_LOCK_SEEN_BONUSES);
} else {
DebugMessage("Could not obtain " . DB_LOCK_SEEN_BONUSES . " DB lock, skipping update of seen bonuses.", E_USER_WARNING);
}
$sqlBonusesSeen = '';
}
$sqlBonusesSeen .= ($sqlBonusesSeen ? ',' : $sqlStartBonusesSeen) . $thisSql;
}
if (!is_null($bonusItemLevel)) {
$thisSql = sprintf('(%u,%u,%u)', $auction['item'], $bonusSet, $bonusItemLevel);
if (strlen($sqlLevelsSeen) + 5 + strlen($thisSql) > $maxPacketSize) {
if (GetDBLock($ourDb, DB_LOCK_SEEN_ILVLS)) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating seen levels (" . round($totalAuctions / $auctionCount * 100) . '%)');
DBQueryWithError($ourDb, $sqlLevelsSeen);
ReleaseDBLock($ourDb, DB_LOCK_SEEN_ILVLS);
} else {
DebugMessage("Could not obtain " . DB_LOCK_SEEN_ILVLS . " DB lock, skipping update of seen levels.", E_USER_WARNING);
}
$sqlLevelsSeen = '';
}
$sqlLevelsSeen .= ($sqlLevelsSeen ? ',' : $sqlStartLevelsSeen) . $thisSql;
}
}
}
$thisSql = sprintf('(%u, %u, %u, %u, %u, %u, %u, %u)', $house, $auction['auc'], $auction['item'], $auction['quantity'], $auction['bid'], $auction['buyout'], $auction['owner'] == '???' ? 0 : $sellerInfo[$auction['ownerRealm']][$auction['owner']]['id'], $auction['timeLeft']);
if (strlen($sql) + 5 + strlen($thisSql) > $maxPacketSize) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating tblAuction (" . round($totalAuctions / $auctionCount * 100) . '%)');
DBQueryWithError($ourDb, $sql);
$sql = '';
}
$sql .= ($sql == '' ? $sqlStart : ',') . $thisSql;
if (isset($auction['petSpeciesId'])) {
$thisSql = sprintf('(%u, %u, %u, %u, %u, %u)', $house, $auction['auc'], $auction['petSpeciesId'], $auction['petBreedId'], $auction['petLevel'], $auction['petQualityId']);
if (strlen($sqlPet) + 5 + strlen($thisSql) > $maxPacketSize) {
$delayedAuctionSql[] = $sqlPet;
// delayed since tblAuction row must be inserted first for foreign key
$sqlPet = '';
}
$sqlPet .= ($sqlPet == '' ? $sqlStartPet : ',') . $thisSql;
} else {
if (isset($equipBaseItemLevel[$auction['item']])) {
if (count($bonuses) || $auction['rand'] || $auction['context']) {
for ($y = count($bonuses); $y < MAX_BONUSES; $y++) {
$bonuses[] = 'null';
}
$bonuses = implode(',', $bonuses);
$thisSql = sprintf('(%u,%u,%d,%d,%u,%s,%s,%u,%s)', $house, $auction['auc'], $auction['rand'], $auction['seed'], $auction['context'], isset($auction['lootedLevel']) ? $auction['lootedLevel'] : 'null', isset($bonusItemLevel) ? $bonusItemLevel : 'null', $bonusSet, $bonuses);
if (strlen($sqlExtra) + 5 + strlen($thisSql) > $maxPacketSize) {
$delayedAuctionSql[] = $sqlExtra;
// delayed since tblAuction row must be inserted first for foreign key
$sqlExtra = '';
}
$sqlExtra .= ($sqlExtra == '' ? $sqlStartExtra : ',') . $thisSql;
}
}
}
}
if ($sqlBonusesSeen != '') {
if (GetDBLock($ourDb, DB_LOCK_SEEN_BONUSES)) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating seen bonuses");
DBQueryWithError($ourDb, $sqlBonusesSeen . $sqlEndBonusesSeen);
ReleaseDBLock($ourDb, DB_LOCK_SEEN_BONUSES);
} else {
DebugMessage("Could not obtain " . DB_LOCK_SEEN_BONUSES . " DB lock, skipping update of seen bonuses.", E_USER_WARNING);
}
}
if ($sqlLevelsSeen != '') {
if (GetDBLock($ourDb, DB_LOCK_SEEN_ILVLS)) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating seen levels");
DBQueryWithError($ourDb, $sqlLevelsSeen);
ReleaseDBLock($ourDb, DB_LOCK_SEEN_ILVLS);
} else {
DebugMessage("Could not obtain " . DB_LOCK_SEEN_ILVLS . " DB lock, skipping update of seen levels.", E_USER_WARNING);
}
}
if ($sql != '') {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating tblAuction");
DBQueryWithError($ourDb, $sql);
}
if ($sqlPet != '') {
$delayedAuctionSql[] = $sqlPet;
}
if ($sqlExtra != '') {
$delayedAuctionSql[] = $sqlExtra;
}
if (count($delayedAuctionSql)) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating tblAuctionExtra, tblAuctionPet");
}
while (count($delayedAuctionSql)) {
DBQueryWithError($ourDb, array_pop($delayedAuctionSql));
}
unset($sqlBonusesSeen, $sqlLevelsSeen, $sqlPet, $sqlExtra, $delayedAuctionSql);
$sql = <<<EOF
insert ignore into tblAuctionRare (house, id, prevseen) (
select a.house, a.id, tis.lastseen
from tblAuction a
left join tblAuctionExtra ae on ae.house=a.house and ae.id=a.id
left join tblItemSummary tis on tis.house=a.house and tis.item=a.item and tis.bonusset=ifnull(ae.bonusset,0)
where a.house = %d
and a.id > %d
and a.item not in (82800)
%s
and ifnull(tis.lastseen, '2000-01-01') < timestampadd(day,-14,'%s'))
EOF;
$sql = sprintf($sql, $house, $lastMax, $hasRollOver ? ' and a.id < 0x20000000 ' : '', $snapshotString);
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating tblAuctionRare");
DBQueryWithError($ourDb, $sql);
}
foreach ($existingIds as $existingId => &$oldRow) {
// all missing auctions
if (!isset($existingPetIds[$existingId])) {
// missing item auction
if ($oldRow[EXISTING_COL_BUY] > 0 && $oldRow[EXISTING_COL_TIMELEFT] > 0 && $oldRow[EXISTING_COL_TIMELEFT] <= $expiredLength) {
// probably expired item with buyout
$expiredPosted = date('Y-m-d', $snapshot - GetAuctionAge($existingId, $snapshot, $snapshotList));
if (!isset($expiredItemInfo[$expiredPosted][$oldRow[EXISTING_COL_INFOKEY]])) {
$expiredItemInfo[$expiredPosted][$oldRow[EXISTING_COL_INFOKEY]] = 0;
}
$expiredItemInfo[$expiredPosted][$oldRow[EXISTING_COL_INFOKEY]]++;
}
}
}
unset($oldRow);
$rareDeletes = [];
$preDeleted = count($itemInfo);
foreach ($existingIds as $existingId => &$oldRow) {
if (!isset($existingPetIds[$existingId]) && !isset($itemInfo[$oldRow[EXISTING_COL_INFOKEY]])) {
list($itemId, $bonusSet) = explode(':', $oldRow[EXISTING_COL_INFOKEY]);
$rareDeletes[$bonusSet][] = $itemId;
$itemInfo[$oldRow[EXISTING_COL_INFOKEY]] = array('tq' => 0, 'a' => array());
}
}
unset($oldRow);
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating " . count($itemInfo) . " item info (including " . (count($itemInfo) - $preDeleted) . " no longer available)");
UpdateItemInfo($house, $itemInfo, $snapshot);
$sql = 'delete from tblUserRareReport where house = %d and bonusset = %d and item in (%s)';
foreach ($rareDeletes as $bonusSet => $itemIds) {
$chunked = array_chunk($itemIds, 200);
foreach ($chunked as $chunk) {
DBQueryWithError($ourDb, sprintf($sql, $house, $bonusSet, implode(',', $chunk)));
}
}
$preDeleted = count($petInfo);
foreach ($existingPetIds as &$oldRow) {
if (!isset($petInfo[$oldRow['species']][$oldRow['breed']])) {
$petInfo[$oldRow['species']][$oldRow['breed']] = array('tq' => 0, 'a' => array());
}
}
unset($oldRow);
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating " . count($petInfo) . " pet info (including " . (count($petInfo) - $preDeleted) . " no longer available)");
UpdatePetInfo($house, $petInfo, $snapshot);
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " updating seller history");
UpdateSellerInfo($sellerInfo, $house, $snapshot);
if (count($expiredItemInfo) > 0) {
$sqlStart = 'INSERT INTO tblItemExpired (item, bonusset, house, `when`, created, expired) VALUES ';
$sqlEnd = ' ON DUPLICATE KEY UPDATE created=created+values(created), expired=expired+values(expired)';
$sql = '';
if (isset($expiredItemInfo['n'])) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " adding new auctions for " . count($expiredItemInfo['n']) . " items");
$snapshotDay = date('Y-m-d', $snapshot);
$expiredCount = 0;
foreach ($expiredItemInfo['n'] as $infoKey => $createdCount) {
$keyParts = explode(':', $infoKey);
$sqlPart = sprintf('(%u, %u, %u, \'%s\', %u, %u)', $keyParts[0], $keyParts[1], $house, $snapshotDay, $createdCount, $expiredCount);
if (strlen($sql) + 10 + strlen($sqlPart) + strlen($sqlEnd) > $maxPacketSize) {
DBQueryWithError($ourDb, $sql . $sqlEnd);
$sql = '';
}
$sql .= ($sql == '' ? $sqlStart : ',') . $sqlPart;
}
unset($expiredItemInfo['n']);
}
if ($sql != '') {
DBQueryWithError($ourDb, $sql . $sqlEnd);
$sql = '';
}
$createdCount = 0;
$snapshotDays = array_keys($expiredItemInfo);
foreach ($snapshotDays as $snapshotDay) {
//DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " adding expired auctions from $snapshotDay for ".count($expiredItemInfo[$snapshotDay])." items");
foreach ($expiredItemInfo[$snapshotDay] as $infoKey => $expiredCount) {
$keyParts = explode(':', $infoKey);
$sqlPart = sprintf('(%u, %u, %u, \'%s\', %u, %u)', $keyParts[0], $keyParts[1], $house, $snapshotDay, $createdCount, $expiredCount);
if (strlen($sql) + 10 + strlen($sqlPart) + strlen($sqlEnd) > $maxPacketSize) {
DBQueryWithError($ourDb, $sql . $sqlEnd);
$sql = '';
}
$sql .= ($sql == '' ? $sqlStart : ',') . $sqlPart;
}
}
if ($sql != '') {
DBQueryWithError($ourDb, $sql . $sqlEnd);
}
}
if (count($existingIds) > 0) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " deleting " . count($existingIds) . " auctions");
$sqlStart = sprintf('DELETE FROM tblAuction WHERE house = %d AND id IN (', $house);
$sql = '';
foreach ($existingIds as $lostId => &$lostRow) {
if (strlen($sql) + 10 + strlen($lostId) > $maxPacketSize) {
DBQueryWithError($ourDb, $sql . ')');
$sql = '';
}
$sql .= ($sql == '' ? $sqlStart : ',') . $lostId;
}
unset($lostRow);
if ($sql != '') {
DBQueryWithError($ourDb, $sql . ')');
}
}
$snapshotHourStart = date('Y-m-d H', $snapshot) . ':00:00';
$stmt = $ourDb->prepare('UPDATE tblSnapshot SET flags = flags | 1 WHERE house = ? AND updated between ? and timestampadd(second, 3599, ?) AND updated != ?');
$stmt->bind_param('isss', $house, $snapshotHourStart, $snapshotHourStart, $snapshotString);
$stmt->execute();
$stmt->close();
$ourDb->close();
MCSetHouse($house, 'ts', $snapshot);
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " finished with {$totalAuctions} auctions in " . round(microtime(true) - $startTimer, 2) . " sec");
}
function ParseAuctionData($house, $snapshot, &$json)
{
global $maxPacketSize;
global $houseRegionCache;
global $auctionExtraItemsCache;
$snapshotString = date('Y-m-d H:i:s', $snapshot);
$startTimer = microtime(true);
$region = $houseRegionCache[$house]['region'];
$lastMax = 0;
$lastMaxUpdated = 0;
$jsonAuctions = [];
if (isset($json['auctions']['auctions'])) {
$jsonAuctions =& $json['auctions']['auctions'];
} elseif (isset($json['auctions']) && count($json['auctions']) > 5) {
$jsonAuctions =& $json['auctions'];
}
$ourDb = DBConnect(true);
$ourDb->query('set transaction isolation level read uncommitted, read only');
$ourDb->begin_transaction();
$stmt = $ourDb->prepare('SELECT ifnull(maxid,0), unix_timestamp(updated) FROM tblSnapshot s WHERE house = ? AND updated = (SELECT max(s2.updated) FROM tblSnapshot s2 WHERE s2.house = ? AND s2.updated < ?)');
$stmt->bind_param('iis', $house, $house, $snapshotString);
$stmt->execute();
$stmt->bind_result($lastMax, $lastMaxUpdated);
if ($stmt->fetch() !== true) {
$lastMax = 0;
$lastMaxUpdated = 0;
}
$stmt->close();
$itemBuyouts = [];
$itemBids = [];
$petBuyouts = [];
$petBids = [];
$newAuctionItems = [];
$oldAuctionItems = [];
$emptyItemInfo = [ARRAY_INDEX_QUANTITY => 0, ARRAY_INDEX_AUCTIONS => []];
if ($jsonAuctions) {
$auctionCount = count($jsonAuctions);
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " parsing {$auctionCount} auctions");
while ($auction = array_pop($jsonAuctions)) {
$isNewAuction = $auction['auc'] - $lastMax;
$isNewAuction = $isNewAuction > 0 || $isNewAuction < -0x20000000;
if (isset($auction['petBreedId'])) {
$auction['petBreedId'] = ($auction['petBreedId'] - 3) % 10 + 3;
// squash gender
}
$hasBuyout = $auction['buyout'] != 0;
if (isset($auction['petSpeciesId'])) {
if ($hasBuyout) {
$aucList =& $petBuyouts;
} else {
$aucList =& $petBids;
}
if (!isset($aucList[$auction['petSpeciesId']][ARRAY_INDEX_ALLBREEDS])) {
$aucList[$auction['petSpeciesId']][ARRAY_INDEX_ALLBREEDS] = $emptyItemInfo;
}
if ($hasBuyout) {
AuctionListInsert($aucList[$auction['petSpeciesId']][ARRAY_INDEX_ALLBREEDS][ARRAY_INDEX_AUCTIONS], $auction['quantity'], $auction['buyout']);
}
$aucList[$auction['petSpeciesId']][ARRAY_INDEX_ALLBREEDS][ARRAY_INDEX_QUANTITY] += $auction['quantity'];
if (!isset($aucList[$auction['petSpeciesId']][$auction['petBreedId']])) {
$aucList[$auction['petSpeciesId']][$auction['petBreedId']] = $emptyItemInfo;
}
if ($hasBuyout) {
AuctionListInsert($aucList[$auction['petSpeciesId']][$auction['petBreedId']][ARRAY_INDEX_AUCTIONS], $auction['quantity'], $auction['buyout']);
}
$aucList[$auction['petSpeciesId']][$auction['petBreedId']][ARRAY_INDEX_QUANTITY] += $auction['quantity'];
} else {
if ($hasBuyout) {
$aucList =& $itemBuyouts;
} else {
$aucList =& $itemBids;
}
$bonusSet = 0;
if (isset($auctionExtraItemsCache[$auction['item']]) && isset($auction['bonusLists'])) {
$bonusSet = GetBonusSet($auction['bonusLists']);
}
$itemInfoKey = $auction['item'] . ":{$bonusSet}";
if (!isset($aucList[$itemInfoKey])) {
$aucList[$itemInfoKey] = $emptyItemInfo;
}
AuctionListInsert($aucList[$itemInfoKey][ARRAY_INDEX_AUCTIONS], $auction['quantity'], $hasBuyout ? $auction['buyout'] : $auction['bid']);
$aucList[$itemInfoKey][ARRAY_INDEX_QUANTITY] += $auction['quantity'];
if ($isNewAuction) {
if (!isset($oldAuctionItems[$itemInfoKey])) {
$newAuctionItems[$itemInfoKey] = true;
}
} else {
$oldAuctionItems[$itemInfoKey] = true;
unset($newAuctionItems[$itemInfoKey]);
}
}
}
}
unset($json, $jsonAuctions, $oldAuctionItems);
$newAuctionItems = array_keys($newAuctionItems);
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " found " . count($itemBuyouts) . " distinct items, " . count($petBuyouts) . " species, " . count($newAuctionItems) . " new items");
$watchesSet = [];
$watchesSetCount = 0;
$watchesUnset = [];
$watchesUnsetCount = 0;
$updateObserved = [];
$watchesCount = 0;
$sql = <<<'EOF'
select uw.`user`, uw.seq, uw.item, uw.bonusset, uw.species, uw.breed, uw.direction, uw.quantity, uw.price,
if(uw.observed is null, 0, 1) isset, if(uw.reported > uw.observed, 1, 0) wasreported
from tblUserWatch uw
join tblUser u on uw.user = u.id
where uw.deleted is null
and ((uw.region = ? and uw.observed is null) or uw.house = ?)
and (uw.observed is null or uw.observed < ?)
and (u.paiduntil > now() or u.lastseen > timestampadd(day, ?, now()))
order by uw.item, uw.species
EOF;
$stmt = $ourDb->prepare($sql);
$freeDays = -1 * SUBSCRIPTION_WATCH_FREE_LAST_LOGIN_DAYS;
$stmt->bind_param('sisi', $region, $house, $snapshotString, $freeDays);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$watchSatisfied = false;
$watchesCount++;
if ($row['item']) {
$itemInfoKey = $row['item'] . ':' . ($row['bonusset'] ?: 0);
if (!isset($itemBuyouts[$itemInfoKey])) {
// none of this item for sale
if (is_null($row['quantity'])) {
// market price notification, without quantity it does not change
continue;
}
if (is_null($row['price']) && $row['direction'] == 'Over' && $row['quantity'] == 0 && isset($itemBids[$itemInfoKey])) {
// when no quantity avail for buyout, and we're looking for any quantity over X, check bids too
$watchSatisfied = $itemBids[$itemInfoKey][ARRAY_INDEX_QUANTITY];
} else {
$watchSatisfied = WatchSatisfied($emptyItemInfo, $row['direction'], $row['quantity'], $row['price']);
}
} else {
$watchSatisfied = WatchSatisfied($itemBuyouts[$itemInfoKey], $row['direction'], $row['quantity'], $row['price']);
}
} else {
if ($row['species']) {
$breed = $row['breed'] ?: ARRAY_INDEX_ALLBREEDS;
if (!isset($petBuyouts[$row['species']][$breed])) {
// none of this pet for sale
if (is_null($row['quantity'])) {
// market price notification, without quantity it does not change
continue;
}
if (is_null($row['price']) && $row['direction'] == 'Over' && $row['quantity'] == 0 && isset($petBids[$row['species']][$breed])) {
// when no quantity avail for buyout, and we're looking for any quantity over X, check bids too
$watchSatisfied = $petBids[$row['species']][$breed][ARRAY_INDEX_QUANTITY];
} else {
$watchSatisfied = WatchSatisfied($emptyItemInfo, $row['direction'], $row['quantity'], $row['price']);
}
} else {
$watchSatisfied = WatchSatisfied($petBuyouts[$row['species']][$breed], $row['direction'], $row['quantity'], $row['price']);
}
}
}
if ($watchSatisfied === false) {
if ($row['isset']) {
$watchesUnset[$row['user']][] = $row['seq'];
$watchesUnsetCount++;
}
} elseif (!$row['wasreported']) {
$watchesSet[$row['user']][$row['seq']] = $watchSatisfied;
$watchesSetCount++;
if (!$row['isset']) {
$updateObserved[$row['user']] = true;
}
}
}
$result->close();
$stmt->close();
$ourDb->commit();
// end read-uncommitted transaction
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " reviewed {$watchesCount} watches, {$watchesSetCount} set, {$watchesUnsetCount} unset");
$queryCount = 0;
$sql = 'update tblUserWatch set house = if(region is null, house, null), currently = null, observed = null where user = %d and seq in (%s)';
foreach ($watchesUnset as $user => $allSeqs) {
$chunks = array_chunk($allSeqs, 200);
foreach ($chunks as $seqs) {
DBQueryWithError($ourDb, sprintf($sql, $user, implode(',', $seqs)));
$queryCount++;
}
}
$sql = 'update tblUserWatch set house = ?, currently = ?, observed = ifnull(observed, ?) where user = ? and seq = ?';
if (count($watchesSet)) {
$stmt = $ourDb->prepare($sql);
$boundCurrently = $boundUser = $boundSeq = null;
$stmt->bind_param('issii', $house, $boundCurrently, $snapshotString, $boundUser, $boundSeq);
foreach ($watchesSet as $user => $allSeqs) {
foreach ($allSeqs as $seq => $currently) {
$boundCurrently = $currently;
$boundUser = $user;
$boundSeq = $seq;
$stmt->execute();
$queryCount++;
}
}
$stmt->close();
}
// check unusual items
$ok = DBQueryWithError($ourDb, 'create temporary table ttblRareStage like ttblRareStageTemplate');
if ($ok) {
$sqlStart = 'insert into ttblRareStage (item, bonusset, price) values ';
$sql = '';
foreach ($newAuctionItems as $itemInfoKey) {
list($itemId, $bonusSet) = explode(':', $itemInfoKey);
$thisSql = sprintf('(%d,%d,%s)', $itemId, $bonusSet, isset($itemBuyouts[$itemInfoKey]) ? GetMarketPrice($itemBuyouts[$itemInfoKey]) : GetMarketPrice($itemBids[$itemInfoKey], 1));
if (strlen($sql) + 5 + strlen($thisSql) > $maxPacketSize) {
$ok &= DBQueryWithError($ourDb, $sql);
$queryCount++;
$sql = '';
}
$sql .= ($sql == '' ? $sqlStart : ',') . $thisSql;
}
if ($sql != '') {
$ok &= DBQueryWithError($ourDb, $sql);
$queryCount++;
}
DBQueryWithError($ourDb, 'set transaction isolation level repeatable read');
$ourDb->begin_transaction();
$sqls = [];
$sqls[] = <<<'EOF'
select rs.item, rs.bonusset, unix_timestamp(s.lastseen)
from ttblRareStage rs
join tblItemSummary s on rs.item = s.item and rs.bonusset = s.bonusset
where s.house = ?
EOF;
$sqls[] = <<<'EOF'
select rs.item, rs.bonusset, max(unix_timestamp(ar.prevseen))
from ttblRareStage rs
join tblAuction a on a.item = rs.item + 0
join tblAuctionRare ar on ar.house = a.house and ar.id = a.id
left join tblAuctionExtra ae on ae.house = a.house and ae.id = a.id
where ifnull(ae.bonusset, 0) = rs.bonusset
and a.house = ?
group by rs.item, rs.bonusset
EOF;
$dated = [];
$summaryLate = $summaryRows = $addedRows = $updatedRows = 0;
for ($x = 0; $x < count($sqls); $x++) {
$stmt = $ourDb->prepare($sqls[$x]);
$stmt->bind_param('i', $house);
$stmt->execute();
$item = $bonusSet = $lastSeen = null;
$stmt->bind_result($item, $bonusSet, $lastSeen);
while ($stmt->fetch()) {
$k = "{$item}:{$bonusSet}";
if (!isset($dated[$k])) {
if ($x == 0) {
$summaryRows++;
if ($lastSeen == $snapshot) {
$summaryLate++;
}
} else {
$addedRows++;
}
$dated[$k] = $lastSeen;
} elseif ($dated[$k] == $snapshot) {
$updatedRows++;
$dated[$k] = $lastSeen;
} elseif ($dated[$k] != $lastSeen) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " rares: {$k} was " . date('Y-m-d H:i:s', $dated[$k]) . ", then " . date('Y-m-d H:i:s', $lastSeen) . ", snapshot {$snapshotString}");
}
}
$stmt->close();
}
unset($sqls);
$ourDb->commit();
// end read txn
$item = $bonusSet = $lastSeen = null;
$stmt = $ourDb->prepare('update ttblRareStage set lastseen = from_unixtime(?) where item = ? and bonusset = ?');
$stmt->bind_param('iii', $lastSeen, $item, $bonusSet);
foreach ($dated as $k => $lastSeenVal) {
list($item, $bonusSet) = explode(':', $k);
$lastSeen = $lastSeenVal;
// in case of byref weirdness
$ok &= $stmt->execute();
if ($ok) {
$queryCount++;
} else {
break;
}
}
$stmt->close();
unset($dated);
$stmt = $ourDb->prepare('delete from ttblRareStage where lastseen = ?');
$stmt->bind_param('s', $snapshotString);
$ok &= $stmt->execute();
$stmt->close();
$removed = $rowsWithoutDates = 0;
$stmt = $ourDb->prepare('select count(*), sum(if(lastseen is null, 1, 0)) from ttblRareStage');
$stmt->execute();
$stmt->bind_result($removed, $rowsWithoutDates);
$stmt->fetch();
$stmt->close();
$totalRows = count($newAuctionItems);
$removed = $totalRows - $removed;
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " rares: {$summaryRows} ({$summaryLate} late, {$removed} removed) tblItemSummary, added {$addedRows} & updated {$updatedRows} tblAuctionRare, {$rowsWithoutDates} without dates, {$totalRows} total");
if (!$ok) {
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " failed while populating ttblRareStage");
}
}
if ($ok) {
$sql = <<<'EOF'
replace into tblUserRareReport (user, house, item, bonusset, prevseen, price, snapshot) (
SELECT ur.user, ur.house, rs.item, rs.bonusset, rs.lastseen, rs.price, ?
FROM ttblRareStage rs
join tblUserRare ur
join tblDBCItem i on i.id = rs.item and i.class = ur.itemclass and i.quality >= ur.minquality and i.level between ifnull(ur.minlevel, i.level) and ifnull(ur.maxlevel, i.level)
left join tblDBCItemVendorCost ivc on ivc.item = rs.item
where (ur.flags & 2 > 0 or ivc.item is null)
and (ur.flags & 1 > 0 or (select count(*) from tblDBCSpell sc where sc.crafteditem = rs.item) = 0)
and (datediff(?, rs.lastseen) >= ur.days or rs.lastseen is null)
and ur.house = ?
group by rs.item, rs.bonusset
)
EOF;
$stmt = $ourDb->prepare($sql);
$stmt->bind_param('ssi', $snapshotString, $snapshotString, $house);
$stmt->execute();
$stmt->close();
$stmt = $ourDb->prepare('select distinct user from tblUserRareReport where house = ? and snapshot = ?');
$stmt->bind_param('is', $house, $snapshotString);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$updateObserved[$row['user']] = true;
}
$result->close();
$stmt->close();
}
DBQueryWithError($ourDb, 'drop temporary table if exists ttblRareStage');
$observedUsers = array_chunk(array_keys($updateObserved), 200);
$sql = 'update tblUser set watchesobserved = \'%1$s\' where id in (%2$s) and ifnull(watchesobserved, \'2000-01-01\') < \'%1$s\'';
foreach ($observedUsers as $users) {
DBQueryWithError($ourDb, sprintf($sql, $snapshotString, implode(',', $users)));
$queryCount++;
}
$ourDb->close();
DebugMessage("House " . str_pad($house, 5, ' ', STR_PAD_LEFT) . " finished with {$queryCount} update queries in " . round(microtime(true) - $startTimer, 2) . " sec");
}
