function DataIntervalsData() { $cacheKey = 'dataintervalstable'; if (($tr = MCGet($cacheKey)) !== false) { return $tr; } $db = DBConnect(); $sql = <<<'EOF' select t.house, t.lastupdate, t.mindelta, modes.delta as modedelta, t.avgdelta, t.maxdelta, r.region, group_concat(r.name order by 1 separator ', ') nms from ( select deltas.house, max(deltas.updated) lastupdate, round(min(delta)/5)*5 mindelta, round(avg(delta)/5)*5 avgdelta, round(max(delta)/5)*5 maxdelta from ( select sn.updated, if(@prevhouse = sn.house and sn.updated > timestampadd(hour, -48, now()), unix_timestamp(sn.updated) - @prevdate, null) delta, @prevdate := unix_timestamp(sn.updated) updated_ts, @prevhouse := sn.house house from (select @prevhouse := null, @prevdate := null) setup, tblSnapshot sn order by sn.house, sn.updated) deltas group by deltas.house) t left join ( select house, delta from ( select if(@prev = house, @rownum := @rownum + 1, @rownum := 0) o, delta, (@prev := house) as house, c from ( select house, delta, count(*) c from ( select sn.updated, round(if(@prevh = sn.house, unix_timestamp(sn.updated) - @prevd, null)/5)*5 delta, @prevd := unix_timestamp(sn.updated) updated_ts, @prevh := sn.house house from (select @prevh := null, @prevd := null) setup, tblSnapshot sn where sn.updated > timestampadd(hour, -48, now()) order by sn.house, sn.updated) deltas where delta is not null group by house, delta order by house, c desc ) tosort, (select @rownum := 0, @prev := null) setup) filtered where o=0 ) modes on modes.house = t.house join tblRealm r on r.house = t.house and r.locale is not null group by r.house order by 4 asc, 3 asc, region asc, nms asc EOF; $stmt = $db->prepare($sql); $stmt->execute(); $result = $stmt->get_result(); $tr = DBMapArray($result, null); $stmt->close(); MCSet($cacheKey, $tr, 60); return $tr; }
function HouseTimestamps($house) { global $db; $cacheKey = MCGet('housecheck_' . $house); if ($cacheKey === false) { $cacheKey = 0; } $cacheKey = 'house_timestamps_' . $cacheKey; if (($tr = MCGetHouse($house, $cacheKey)) !== false) { return $tr; } DBConnect(); $tr = ['scheduled' => 0, 'delayednext' => 0, 'lastupdate' => 0, 'mindelta' => 0, 'avgdelta' => 0, 'maxdelta' => 0, 'lastcheck' => ['ts' => 0, 'json' => 0], 'lastsuccess' => ['ts' => 0, 'json' => 0]]; $sql = <<<EOF select unix_timestamp(timestampadd(second, least(ifnull(min(delta)+15, 45*60), 150*60), max(deltas.updated))) scheduled, unix_timestamp(hc.nextcheck), unix_timestamp(max(deltas.updated)) lastupdate, min(delta) mindelta, round(avg(delta)) avgdelta, max(delta) maxdelta, unix_timestamp(hc.lastcheck), hc.lastcheckresult, unix_timestamp(hc.lastchecksuccess), hc.lastchecksuccessresult from ( select sn.updated, if(sn.updated > timestampadd(hour, -72, now()), unix_timestamp(sn.updated) - @prevdate, null) delta, @prevdate := unix_timestamp(sn.updated) updated_ts from (select @prevdate := null) setup, tblSnapshot sn where sn.house = ? order by sn.updated) deltas left join tblHouseCheck hc on hc.house = ? EOF; $stmt = $db->prepare($sql); $stmt->bind_param('ii', $house, $house); $stmt->execute(); $stmt->bind_result($tr['scheduled'], $tr['delayednext'], $tr['lastupdate'], $tr['mindelta'], $tr['avgdelta'], $tr['maxdelta'], $tr['lastcheck']['ts'], $tr['lastcheck']['json'], $tr['lastsuccess']['ts'], $tr['lastsuccess']['json']); $stmt->fetch(); $stmt->close(); foreach (['lastcheck', 'lastsuccess'] as $k) { if (!is_null($tr[$k]['json'])) { $decoded = json_decode($tr[$k]['json'], true); if (json_last_error() == JSON_ERROR_NONE) { $tr[$k]['json'] = $decoded; } } } MCSetHouse($house, $cacheKey, $tr); return $tr; }
function GetSellerList() { $cacheKey = 'extra:multirealm:sellers'; $sellers = MCGet($cacheKey); if ($sellers !== false) { return $sellers; } $sellers = []; $sql = <<<'EOF' select z2.sscnt, z2.itemcnt, s.name sellername, r.name realmname, s.firstseen, s.lastseen, r.house, r.slug from ( select seller, count(distinct `snapshot`) sscnt, count(distinct item) itemcnt from ( SELECT seller, item, `snapshot` FROM `tblSellerItemHistory` where item in (128159, 127736, 127738, 127732, 127731, 127737, 127735, 127730, 127734, 127733, 127718, 128158, 127720, 127714, 127713, 127719, 127717, 127712, 127716, 127715) ) z1 group by seller having (count(distinct item) = 2 or count(distinct item) > 3) ) z2 left join tblSellerItemHistory h on h.seller = z2.seller and h.item not in (128159, 127736, 127738, 127732, 127731, 127737, 127735, 127730, 127734, 127733, 127718, 128158, 127720, 127714, 127713, 127719, 127717, 127712, 127716, 127715) join tblSeller s on s.id = z2.seller join tblRealm r on s.realm = r.id where h.seller is null and r.region = 'US' and s.firstseen > timestampadd(day, -14, now()) and s.lastseen > timestampadd(hour, -36, now()) order by r.house, s.firstseen, z2.sscnt desc, s.lastseen desc EOF; $db = DBConnect(); $db->query('set transaction isolation level read uncommitted, read only'); $db->begin_transaction(); $stmt = $db->prepare($sql); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { $sellers[$row['house']][] = $row; } $result->close(); $stmt->close(); $db->commit(); // end transaction MCSet($cacheKey, $sellers); return $sellers; }
function GetBattleNetURL($region, $path) { $region = trim(strtolower($region)); if (substr($path, 0, 1) == '/') { $path = substr($path, 1); } $start = microtime(true); $finalUrl = ''; while (!$finalUrl && $start + 5 > microtime(true)) { $cacheKey = 'BattleNetKeyUsage'; if (!MCAdd($cacheKey . '_critical', 1, 5 * BATTLE_NET_REQUEST_PERIOD)) { usleep(50000); continue; } $apiHits = MCGet($cacheKey); if ($apiHits === false) { $apiHits = []; } $hitCount = count($apiHits); if ($hitCount >= BATTLE_NET_REQUEST_LIMIT) { $now = microtime(true); while ($apiHits[0] < $now && $now < $apiHits[0] + BATTLE_NET_REQUEST_PERIOD) { usleep(50000); $now = microtime(true); } } $apiHits[] = microtime(true); $hitCount++; if ($hitCount > BATTLE_NET_REQUEST_LIMIT) { array_splice($apiHits, 0, $hitCount - BATTLE_NET_REQUEST_LIMIT); } MCSet($cacheKey, $apiHits, 10 * BATTLE_NET_REQUEST_PERIOD); MCDelete($cacheKey . '_critical'); $pattern = $region == 'cn' ? 'https://api.battlenet.com.%s/%s%sapikey=%s' : 'https://%s.api.battle.net/%s%sapikey=%s'; $finalUrl = sprintf($pattern, $region, $path, strpos($path, '?') !== false ? '&' : '?', BATTLE_NET_KEY); } return $finalUrl ? $finalUrl : false; }
function APIMaintenance($when = -1, $expire = false) { if (!function_exists('MCGet')) { DebugMessage('Tried to test for APIMaintenance without memcache loaded!', E_USER_ERROR); } $cacheKey = 'APIMaintenance'; if ($when == -1) { return MCGet($cacheKey); } if ($when === false) { $when = 0; } if (!is_numeric($when)) { $when = strtotime($when); } if ($when) { if ($expire == false) { $expire = $when + 72 * 60 * 60; } elseif (!is_numeric($expire)) { $expire = strtotime($expire); } DebugMessage('Setting API maintenance mode, expected to end ' . TimeDiff($when) . ', maximum ' . TimeDiff($expire)); MCSet($cacheKey, $when, $expire); } else { DebugMessage('Ending API maintenance mode.'); MCDelete($cacheKey); } return $when; }
function GetRareWatches($loginState, $house = 0) { $userId = $loginState['id']; $house = intval($house, 10); $limit = SUBSCRIPTION_RARE_LIMIT_TOTAL; if ($house) { $allWatches = GetRareWatches($loginState); $otherHouseCount = 0; foreach ($allWatches['watches'] as $watch) { if ($watch['house'] != $house) { $otherHouseCount++; } } $limit = min(SUBSCRIPTION_RARE_LIMIT_HOUSE, SUBSCRIPTION_RARE_LIMIT_TOTAL - $otherHouseCount); } $cacheKey = SUBSCRIPTION_RARE_CACHEKEY . $userId . '_' . $house; $json = MCGet($cacheKey); if ($json !== false) { return ['maximum' => $limit, 'watches' => $json]; } $db = DBConnect(); $sql = 'SELECT seq, house, itemclass, minquality, minlevel, maxlevel, flags & 1 as includecrafted, flags & 2 as includevendor, days from tblUserRare where user = ?'; if ($house) { $sql .= ' and house = ?'; } $stmt = $db->prepare($sql); if ($house) { $stmt->bind_param('ii', $userId, $house); } else { $stmt->bind_param('i', $userId); } $stmt->execute(); $result = $stmt->get_result(); $json = DBMapArray($result); $stmt->close(); MCSet($cacheKey, $json); return ['maximum' => $limit, 'watches' => $json]; }
function PetGlobalNow($region, $species) { global $db; $key = 'battlepet_globalnow2_' . $region . '_' . $species; if (($tr = MCGet($key)) !== false) { return $tr; } DBConnect(); $sql = <<<EOF SELECT i.breed, r.house, i.price, i.quantity, unix_timestamp(i.lastseen) as lastseen FROM `tblPetSummary` i join tblRealm r on i.house = r.house and r.region = ? WHERE i.species=? group by i.breed, r.house EOF; $stmt = $db->prepare($sql); $stmt->bind_param('si', $region, $species); $stmt->execute(); $result = $stmt->get_result(); $tr = DBMapArray($result, array('breed', null)); $stmt->close(); MCSet($key, $tr); return $tr; }
function CategoryRecipeMap($skill) { $cacheKey = 'category_recipe_map2_' . $skill; $map = MCGet($cacheKey); if ($map !== false) { return $map; } $sql = <<<'EOF' SELECT i.id recipe, s.crafteditem crafted FROM `tblDBCItemSpell` dis join tblDBCSpell s on dis.spell = s.id join tblDBCItem i on dis.item = i.id join tblDBCItem ii on ii.id = s.crafteditem where s.skillline = ? and i.auctionable = 1 and ii.auctionable = 1 EOF; $db = DBConnect(); $stmt = $db->stmt_init(); $stmt->prepare($sql); $stmt->bind_param('i', $skill); $stmt->execute(); $result = $stmt->get_result(); $map = $result->fetch_all(MYSQLI_ASSOC); $result->close(); $stmt->close(); MCSet($cacheKey, $map, 43200); return $map; }
function SearchItems($house, $search, $locale) { global $db; $suffixes = MCGet('search_itemsuffixes_' . $locale); if ($suffixes === false) { $sql = <<<EOF SELECT lower(suffix) FROM tblDBCItemRandomSuffix where locale='{$locale}' union select lower(ind.`desc_{$locale}`) from tblDBCItemBonus ib join tblDBCItemNameDescription ind on ind.id = ib.nameid where ind.`desc_{$locale}` is not null EOF; $stmt = $db->prepare($sql); $stmt->execute(); $result = $stmt->get_result(); $suffixes = DBMapArray($result, null); $stmt->close(); MCSet('search_itemsuffixes_' . $locale, $suffixes, 86400); } $terms = preg_replace('/\\s+/', '%', " {$search} "); $nameSearch = "i.name_{$locale} like ?"; $terms2 = ''; $barewords = trim(preg_replace('/ {2,}/', ' ', preg_replace('/[^ a-zA-Z0-9\'\\.]/', '', $search))); for ($x = 0; $x < count($suffixes); $x++) { if (substr($barewords, -1 * strlen($suffixes[$x])) == $suffixes[$x]) { $terms2 = '%' . str_replace(' ', '%', substr($barewords, 0, -1 * strlen($suffixes[$x]) - 1)) . '%'; $nameSearch = "(i.name_{$locale} like ? or i.name_{$locale} like ?)"; } } $sql = <<<EOF select results.*, (select round(avg(case hours.h when 0 then ihh.silver00 when 1 then ihh.silver01 when 2 then ihh.silver02 when 3 then ihh.silver03 when 4 then ihh.silver04 when 5 then ihh.silver05 when 6 then ihh.silver06 when 7 then ihh.silver07 when 8 then ihh.silver08 when 9 then ihh.silver09 when 10 then ihh.silver10 when 11 then ihh.silver11 when 12 then ihh.silver12 when 13 then ihh.silver13 when 14 then ihh.silver14 when 15 then ihh.silver15 when 16 then ihh.silver16 when 17 then ihh.silver17 when 18 then ihh.silver18 when 19 then ihh.silver19 when 20 then ihh.silver20 when 21 then ihh.silver21 when 22 then ihh.silver22 when 23 then ihh.silver23 else null end) * 100) from tblItemHistoryHourly ihh, (select 0 h union select 1 h union select 2 h union select 3 h union select 4 h union select 5 h union select 6 h union select 7 h union select 8 h union select 9 h union select 10 h union select 11 h union select 12 h union select 13 h union select 14 h union select 15 h union select 16 h union select 17 h union select 18 h union select 19 h union select 20 h union select 21 h union select 22 h union select 23 h) hours where ihh.house = ? and ihh.item = results.id and ihh.bonusset = results.bonusset) avgprice, ifnull(GROUP_CONCAT(bs.`tagid` ORDER BY 1 SEPARATOR '.'), '') tagurl, ifnull((select concat_ws(':', nullif(bonus1,0), nullif(bonus2,0), nullif(bonus3,0), nullif(bonus4,0)) FROM `tblItemBonusesSeen` ibs WHERE ibs.item=results.id and ibs.bonusset=results.bonusset order by ibs.observed desc limit 1),'') bonusurl from ( select i.id, i.quality, i.icon, i.class as classid, s.price, s.quantity, unix_timestamp(s.lastseen) lastseen, ifnull(s.bonusset,0) bonusset, i.level from tblDBCItem i left join tblItemSummary s on s.house=? and s.item=i.id where {$nameSearch} and (s.item is not null or ifnull(i.auctionable,1) = 1) group by i.id, ifnull(s.bonusset,0) limit ? ) results left join tblBonusSet bs on results.bonusset = bs.`set` group by results.id, results.bonusset EOF; $limit = 50 * strlen(preg_replace('/\\s/', '', $search)); $stmt = $db->prepare($sql); if ($terms2 == '') { $stmt->bind_param('iisi', $house, $house, $terms, $limit); } else { $stmt->bind_param('iissi', $house, $house, $terms, $terms2, $limit); } $stmt->execute(); $result = $stmt->get_result(); $tr = $result->fetch_all(MYSQLI_ASSOC); $stmt->close(); return $tr; }
function ActFetch() { $required = ['endpoint']; foreach ($required as $v) { if (!isset($_POST[$v])) { ReturnBadRequest(); } $_POST[$v] = substr($_POST[$v], 0, 512); } $key = 'tokennotify-' . md5($_POST['endpoint']); $msg = MCGet($key); if ($msg == false) { $msg = 'Couldn\'t find notification data, but something probably happened that you should check out at WoWToken.info.'; } else { MCDelete($key); } echo json_encode(['title' => 'WoWToken.info', 'notification' => ['body' => $msg, 'tag' => 'wowtoken', 'icon' => '/images/token-192x192.jpg']]); }
function ItemGlobalMonthly($region, $item) { global $db; $key = 'item_globalmonthly2_' . $region . '_' . $item; if (($tr = MCGet($key)) !== false) { return $tr; } DBConnect(); $sqlCols = ''; for ($x = 1; $x <= 31; $x++) { $padded = str_pad($x, 2, '0', STR_PAD_LEFT); $sqlCols .= ", round(avg(mktslvr{$padded})*100) mkt{$padded}, ifnull(sum(qty{$padded}),0) qty{$padded}"; } $sql = <<<EOF SELECT bonusset, month {$sqlCols} FROM `tblItemHistoryMonthly` ihm join tblRealm r on ihm.house = r.house and r.region = ? and r.canonical is not null WHERE ihm.item=? group by bonusset, month EOF; $stmt = $db->prepare($sql); $stmt->bind_param('si', $region, $item); $stmt->execute(); $result = $stmt->get_result(); $bonusRows = DBMapArray($result, array('bonusset', null)); $stmt->close(); $tr = array(); $today = strtotime(date('Y-m-d')); foreach ($bonusRows as $bonusSet => &$rows) { $prevPrice = 0; for ($x = 0; $x < count($rows); $x++) { $year = 2014 + floor(($rows[$x]['month'] - 1) / 12); $monthNum = $rows[$x]['month'] % 12; if ($monthNum == 0) { $monthNum = 12; } $month = ($monthNum < 10 ? '0' : '') . $monthNum; for ($dayNum = 1; $dayNum <= 31; $dayNum++) { $day = ($dayNum < 10 ? '0' : '') . $dayNum; if ($year == 2015 && $monthNum == 12 && ($dayNum >= 16 && $dayNum <= 22)) { continue; } if (!is_null($rows[$x]['mkt' . $day])) { $tr[$bonusSet][] = array('date' => "{$year}-{$month}-{$day}", 'silver' => round($rows[$x]['mkt' . $day] / 100, 2), 'quantity' => $rows[$x]['qty' . $day]); $prevPrice = round($rows[$x]['mkt' . $day] / 100, 2); } else { if (!checkdate($monthNum, $dayNum, $year)) { break; } if (strtotime("{$year}-{$month}-{$day}") >= $today) { break; } if ($prevPrice) { $tr[$bonusSet][] = array('date' => "{$year}-{$month}-{$day}", 'silver' => $prevPrice, 'quantity' => 0); } } } } } unset($rows); MCSet($key, $tr); return $tr; }
function HouseETag($house, $includeFetches = false) { $curTag = $includeFetches ? MCGet('housecheck_' . $house) : ''; if ($curTag === false) { $curTag = 'x'; } $curTag = 'W/"' . MCGetHouse($house) . $curTag . '"'; $theirTag = isset($_SERVER['HTTP_IF_NONE_MATCH']) ? $_SERVER['HTTP_IF_NONE_MATCH'] : ''; if ($curTag && $curTag == $theirTag) { header('HTTP/1.1 304 Not Modified'); exit; } header('ETag: ' . $curTag); }
function MCHouseLock($house, $waitSeconds = 30) { global $MCHousesLocked; static $registeredShutdown = false; if (isset($MCHousesLocked[$house])) { return true; } $giveUpAt = microtime(true) + $waitSeconds; $me = ['pid' => getmypid(), 'script' => $_SERVER["SCRIPT_FILENAME"], 'when' => time()]; do { if (MCAdd('mchouselock_' . $house, $me, 30 * 60)) { $MCHousesLocked[$house] = true; if (!$registeredShutdown) { $registeredShutdown = true; register_shutdown_function('MCHouseUnlock'); } return true; } usleep(500000); } while ($giveUpAt > microtime(true)); $currentLock = MCGet('mchouselock_' . $house); DebugMessage("Could not get house lock for {$house}, owned by " . $currentLock['pid'] . ' ' . $currentLock['script'] . ' ' . TimeDiff($currentLock['when'])); return false; }
function GetUserPaidUntil($userId) { $cacheKey = SUBSCRIPTION_PAID_CACHEKEY . $userId; $ts = MCGet($cacheKey); if ($ts === false) { $db = DBConnect(); $stmt = $db->prepare('SELECT ifnull(unix_timestamp(u.paiduntil),0) FROM tblUser u WHERE u.id=?'); $stmt->bind_param('i', $userId); $stmt->execute(); $stmt->bind_result($ts); if (!$stmt->fetch()) { $ts = 0; } $stmt->close(); MCSet($cacheKey, $ts); } if (!$ts) { $ts = null; } return $ts; }
<?php require_once '../../incl/incl.php'; require_once '../../incl/memcache.incl.php'; require_once '../../incl/api.incl.php'; if (isset($_GET['throttletest'])) { $k = 'throttle_%s_' . $_SERVER['REMOTE_ADDR']; $kTime = sprintf($k, 'time'); $kCount = sprintf($k, 'count'); $memcache->set($kTime, time(), false, THROTTLE_PERIOD); $memcache->set($kCount, THROTTLE_MAXHITS + 1, false, THROTTLE_PERIOD * 2); } if (!isset($_GET['answer'])) { json_return(false); } $cacheKey = 'captcha_' . $_SERVER['REMOTE_ADDR']; if (($details = MCGet($cacheKey)) === false) { json_return(array()); } $memcache->delete($cacheKey); if ($_GET['answer'] == $details['answer']) { UserThrottleCount(true); json_return(array()); } BotCheck(); json_return(array());
<?php require_once __DIR__ . '/../incl/memcache.incl.php'; require_once __DIR__ . '/../incl/wowtoken-twitter.credentials.php'; header('Content-type: text/plain'); if (isset($_GET['newkey'])) { $oauth = new OAuth($twitterCredentials['consumerKey'], $twitterCredentials['consumerSecret']); $requestTokenInfo = $oauth->getRequestToken('https://api.twitter.com/oauth/request_token', 'https://wowtoken.info/twittertoken.php?callback=showkey'); if (!empty($requestTokenInfo)) { MCSet('twittertoken-' . $requestTokenInfo['oauth_token'], $requestTokenInfo, 30 * 60); header('Location: https://api.twitter.com/oauth/authorize?oauth_token=' . rawurlencode($requestTokenInfo['oauth_token'])); } else { echo 'No request token info.'; } } if (isset($_GET['callback']) && $_GET['callback'] == 'showkey' && isset($_GET['oauth_token'])) { $requestTokenInfo['oauth_token'] = $_GET['oauth_token']; $verifier = $_GET['oauth_verifier']; $requestTokenInfo = MCGet('twittertoken-' . $requestTokenInfo['oauth_token']); if ($requestTokenInfo === false) { echo 'Could not find cached token'; exit; } MCDelete('twittertoken-' . $requestTokenInfo['oauth_token']); $oauth = new OAuth($twitterCredentials['consumerKey'], $twitterCredentials['consumerSecret']); $oauth->setToken($requestTokenInfo['oauth_token'], $requestTokenInfo['oauth_token_secret']); $accessToken = $oauth->getAccessToken('https://api.twitter.com/oauth/access_token', '', $verifier); header('Content-type: text/plain'); print_r($accessToken); }