$bySlug[$slug]['house'] = null;
}
}
foreach ($slugs as $slug) {
if ($bySlug[$slug]['house'] != $curHouse) {
if (!MCHouseLock($curHouse) || !MCHouseLock($bySlug[$slug]['house'])) {
break;
}
PrintImportantMessage("{$region} {$slug} changing from " . (is_null($bySlug[$slug]['house']) ? 'null' : $bySlug[$slug]['house']) . " to {$curHouse}");
DBQueryWithError($db, sprintf('UPDATE tblRealm SET house = %d WHERE region = \'%s\' AND slug = \'%s\'', $curHouse, $db->escape_string($region), $db->escape_string($slug)));
$bySlug[$slug]['house'] = $curHouse;
}
}
if (MCHouseLock($curHouse)) {
DBQueryWithError($db, sprintf('UPDATE tblRealm SET canonical = NULL WHERE house = %d', $curHouse));
DBQueryWithError($db, sprintf('UPDATE tblRealm SET canonical = \'%s\' WHERE house = %d AND region = \'%s\' AND slug = \'%s\'', $db->escape_string($canon), $curHouse, $db->escape_string($region), $db->escape_string($rep)));
MCHouseUnlock($curHouse);
} else {
PrintImportantMessage("Could not lock {$curHouse} to set canonical to {$canon}");
}
MCHouseUnlock();
}
$memcache->delete('realms_' . $region);
}
//CleanOldHouses();
//DebugMessage('Skipped cleaning old houses!');
PrintImportantMessage('Done! Started ' . TimeDiff($startTime));
function GetDataRealms($region, $hash)
{
heartbeat();
$region = strtolower($region);
function ReportUserWatches($now, $userRow)
{
global $houseNameCache, $itemClassOrderSql;
$locale = $userRow['locale'];
$LANG = GetLang($locale);
$message = '';
$db = DBConnect();
$sql = <<<'EOF'
select 0 ispet, uw.seq, uw.region, uw.house,
uw.item, uw.bonusset, ifnull(GROUP_CONCAT(distinct bs.`tagid` ORDER BY 1 SEPARATOR '.'), '') tagurl,
i.name_%1$s name,
ifnull(group_concat(distinct ind.`desc_%1$s` separator ' '),'') bonustag,
case i.class %2$s else 999 end classorder,
uw.direction, uw.quantity, uw.price, uw.currently
from tblUserWatch uw
join tblDBCItem i on uw.item = i.id
left join tblBonusSet bs on uw.bonusset = bs.`set`
left join tblDBCItemNameDescription ind on ind.id = bs.tagid
where uw.user = ?
and uw.deleted is null
and uw.observed > ifnull(uw.reported, '2000-01-01')
group by uw.seq
union
select 1 ispet, uw.seq, uw.region, uw.house,
uw.species, uw.breed, ifnull(uw.breed, '') breedurl,
p.name_%1$s name,
null bonustag,
p.type classorder,
uw.direction, uw.quantity, uw.price, uw.currently
from tblUserWatch uw
JOIN tblDBCPet p on uw.species=p.id
where uw.user = ?
and uw.deleted is null
and uw.observed > ifnull(uw.reported, '2000-01-01')
order by if(region is null, 0, 1), house, region, ispet, classorder, name, bonustag, seq
EOF;
$sql = sprintf($sql, $locale, $itemClassOrderSql);
$prevHouse = false;
$houseCount = 0;
$updateSeq = [];
$lastItem = '';
$userId = $userRow['id'];
$stmt = $db->prepare($sql);
$stmt->bind_param('ii', $userId, $userId);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$updateSeq[] = $row['seq'];
if ($prevHouse !== $row['house']) {
$houseCount++;
$prevHouse = $row['house'];
$message .= '<br><b>' . $houseNameCache[$prevHouse]['names'] . '</b><br><br>';
}
$url = sprintf('https://theunderminejournal.com/#%s/%s/%s/%s%s', strtolower($houseNameCache[$prevHouse]['region']), $houseNameCache[$prevHouse]['slug'], $row['ispet'] ? 'battlepet' : 'item', $row['item'], $row['tagurl'] ? '.' . $row['tagurl'] : '');
$bonusTag = $row['bonustag'];
if ($row['ispet']) {
if ($row['bonusset'] && isset($LANG['breedsLookup'][$row['bonusset']])) {
$bonusTag = $LANG['breedsLookup'][$row['bonusset']];
}
}
if ($bonusTag) {
$bonusTag = ' ' . $bonusTag;
}
$lastItem = sprintf('[%s]%s', $row['name'], $bonusTag);
$message .= sprintf('<a href="%s">[%s]%s</a>', $url, $row['name'], $bonusTag);
$direction = $LANG[strtolower($row['direction'])];
if (!is_null($row['price'])) {
$value = FormatPrice($row['price'], $LANG);
$currently = FormatPrice($row['currently'], $LANG);
if (!is_null($row['quantity'])) {
$condition = $LANG['priceToBuy'] . ' ' . $row['quantity'];
} else {
$condition = $LANG['marketPrice'];
}
} else {
$value = $row['quantity'];
$currently = $row['currently'];
$condition = $LANG['availableQuantity'];
}
$message .= sprintf(' %s %s %s: %s <b>%s</b><br>', $condition, $direction, $value, $LANG['now'], $currently);
}
$result->close();
$stmt->close();
if (!count($updateSeq)) {
return false;
}
if (count($updateSeq) == 1) {
$subject = $lastItem;
} else {
$subject = '' . count($updateSeq) . ' ' . $LANG['marketNotifications'];
}
$houseSubject = '';
if ($houseCount == 1) {
$houseSubject = $houseNameCache[$prevHouse]['names'];
}
$sql = 'update tblUserWatch set reported = \'%s\' where user = %d and seq in (%s)';
$chunks = array_chunk($updateSeq, 200);
foreach ($chunks as $seqs) {
DBQueryWithError($db, sprintf($sql, $now, $userId, implode(',', $seqs)));
}
return [$subject, $message, $houseSubject];
}
function UpdatePetInfo($house, &$petInfo, $snapshot)
{
global $db, $maxPacketSize;
$hour = date('H', $snapshot);
$dateString = date('Y-m-d', $snapshot);
$snapshotString = date('Y-m-d H:i:s', $snapshot);
$sqlStart = 'INSERT INTO tblPetSummary (house, species, breed, price, quantity, lastseen) VALUES ';
$sqlEnd = ' on duplicate key update quantity=values(quantity), price=if(quantity=0,price,values(price)), lastseen=if(quantity=0,lastseen,values(lastseen))';
$sql = '';
$sqlHistoryStart = sprintf('INSERT INTO tblPetHistoryHourly (house, species, breed, `when`, `silver%1$s`, `quantity%1$s`) VALUES ', $hour);
$sqlHistoryEnd = sprintf(' on duplicate key update `silver%1$s`=if(values(`quantity%1$s`) > ifnull(`quantity%1$s`,0), values(`silver%1$s`), `silver%1$s`), `quantity%1$s`=if(values(`quantity%1$s`) > ifnull(`quantity%1$s`,0), values(`quantity%1$s`), `quantity%1$s`)', $hour);
$sqlHistory = '';
foreach ($petInfo as $species => &$breeds) {
foreach ($breeds as $breed => &$info) {
$price = GetMarketPrice($info);
$sqlBit = sprintf('(%d,%u,%u,%u,%u,\'%s\')', $house, $species, $breed, $price, $info['tq'], $snapshotString);
if (strlen($sql) + strlen($sqlBit) + strlen($sqlEnd) + 5 > $maxPacketSize) {
DBQueryWithError($db, $sql . $sqlEnd);
$sql = '';
}
$sql .= ($sql == '' ? $sqlStart : ',') . $sqlBit;
if ($info['tq'] > 0) {
$sqlHistoryBit = sprintf('(%d,%u,%u,\'%s\',%u,%u)', $house, $species, $breed, $dateString, round($price / 100), $info['tq']);
if (strlen($sqlHistory) + strlen($sqlHistoryBit) + strlen($sqlHistoryEnd) + 5 > $maxPacketSize) {
DBQueryWithError($db, $sqlHistory . $sqlHistoryEnd);
$sqlHistory = '';
}
$sqlHistory .= ($sqlHistory == '' ? $sqlHistoryStart : ',') . $sqlHistoryBit;
}
}
unset($info);
}
unset($breeds);
if ($sql != '') {
DBQueryWithError($db, $sql . $sqlEnd);
}
if ($sqlHistory != '') {
DBQueryWithError($db, $sqlHistory . $sqlHistoryEnd);
}
}
function GetBonusSet($bonusList)
{
global $bonusTagIdCache, $db;
static $bonusSetCache = [];
$tagIds = [];
for ($y = 0; $y < count($bonusList); $y++) {
if (isset($bonusList[$y]['bonusListId'])) {
$bonus = intval($bonusList[$y]['bonusListId'], 10);
if (isset($bonusTagIdCache[$bonus])) {
$tagIds[$bonusTagIdCache[$bonus]] = $bonusTagIdCache[$bonus];
}
}
}
if (count($tagIds) == 0) {
return 0;
}
sort($tagIds, SORT_NUMERIC);
// check local static cache
$tagIdsKey = implode(':', $tagIds);
if (isset($bonusSetCache[$tagIdsKey])) {
return $bonusSetCache[$tagIdsKey];
}
// not in cache, check db
if (!DBQueryWithError($db, 'lock tables tblBonusSet write')) {
return 0;
}
$stmt = $db->prepare('SELECT `set`, GROUP_CONCAT(`tagid` ORDER BY 1 SEPARATOR \':\') `tagidkey` from tblBonusSet GROUP BY `set`');
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$bonusSetCache[$row['tagidkey']] = $row['set'];
}
$result->close();
$stmt->close();
// check updated local cache now that we're synced with db
if (isset($bonusSetCache[$tagIdsKey])) {
DBQueryWithError($db, 'unlock tables');
return $bonusSetCache[$tagIdsKey];
}
// still don't have it, make a new one
$newSet = 0;
$stmt = $db->prepare('select ifnull(max(`set`),0)+1 from tblBonusSet');
$stmt->execute();
$stmt->bind_result($newSet);
$stmt->fetch();
$stmt->close();
if ($newSet) {
$sql = 'insert into tblBonusSet (`set`, `tagid`) VALUES ';
$x = 0;
foreach ($tagIds as $tagId) {
$sql .= ($x++ > 0 ? ',' : '') . sprintf('(%d,%d)', $newSet, $tagId);
}
if (!DBQueryWithError($db, $sql)) {
$newSet = 0;
}
}
DBQueryWithError($db, 'unlock tables');
if ($newSet) {
$bonusSetCache[$tagIdsKey] = $newSet;
}
return $newSet;
}