function AddHourlyData()
{
global $db, $caughtKill;
if ($caughtKill) {
return;
}
$sqlPattern = <<<'EOF'
insert into tblSellerHistoryHourly (seller, `when`, `new%1$s`, `total%1$s`)
(select seller, date(`snapshot`), `new`, `total`
from tblSellerHistory
where hour(`snapshot`) = %2$d
order by `snapshot`
)
on duplicate key update `new%1$s` = values(`new%1$s`), `total%1$s` = values(`total%1$s`)
EOF;
for ($hour = 0; $hour < 24; $hour++) {
heartbeat();
if ($caughtKill) {
break;
}
$hourPadded = str_pad($hour, 2, '0', STR_PAD_LEFT);
$sql = sprintf($sqlPattern, $hourPadded, $hour);
$queryOk = $db->real_query($sql);
if (!$queryOk) {
DebugMessage("SQL error: " . $db->errno . ' ' . $db->error . " - " . substr(preg_replace('/[\\r\\n]/', ' ', $sql), 0, 500), E_USER_WARNING);
} else {
$rowCount = $db->affected_rows;
DebugMessage("{$rowCount} seller hourly rows updated for hour {$hour}");
}
}
}
function NextDataFile()
{
$dir = scandir(substr(SNAPSHOT_PATH, 0, -1), SCANDIR_SORT_ASCENDING);
$gotFile = false;
$wait = false;
foreach ($dir as $fileName) {
if (preg_match('/^(\\d+)-(US|EU|CN|TW|KR)\\.lua$/', $fileName, $res)) {
if (filemtime(SNAPSHOT_PATH . $fileName) > time() - 5) {
$wait = true;
continue;
}
if (filesize(SNAPSHOT_PATH . $fileName) == 0) {
continue;
}
if (($handle = fopen(SNAPSHOT_PATH . $fileName, 'rb')) === false) {
continue;
}
if (!flock($handle, LOCK_EX | LOCK_NB)) {
fclose($handle);
continue;
}
if (feof($handle)) {
fclose($handle);
unlink(SNAPSHOT_PATH . $fileName);
continue;
}
$gotFile = $fileName;
break;
}
}
unset($dir);
if ($wait && !$gotFile) {
heartbeat();
sleep(5);
return true;
}
if (!$gotFile) {
return false;
}
$snapshot = intval($res[1], 10);
$region = $res[2];
DebugMessage("Region {$region} data file from " . TimeDiff($snapshot, array('parts' => 2, 'precision' => 'second')));
$lua = LuaDecode(fread($handle, filesize(SNAPSHOT_PATH . $fileName)), true);
ftruncate($handle, 0);
fclose($handle);
unlink(SNAPSHOT_PATH . $fileName);
if (!$lua) {
DebugMessage("Region {$region} {$snapshot} data file corrupted!", E_USER_WARNING);
return true;
}
return ParseTokenData($region, $snapshot, $lua);
}
function BuildAddonData($region)
{
global $db, $caughtKill;
heartbeat();
if ($caughtKill) {
return;
}
DebugMessage("Starting region {$region}");
$globalSpots = 3;
// number of global prices in front of every string
$stmt = $db->prepare('select distinct house from tblRealm where region = ? and canonical is not null order by 1');
$stmt->bind_param('s', $region);
$stmt->execute();
$result = $stmt->get_result();
$houses = DBMapArray($result, null);
$stmt->close();
$item_global = [];
$item_avg = [];
$item_stddev = [];
$item_recent = [];
$item_days = [];
DebugMessage('Finding global prices');
$itemExcludeSql = <<<EOF
and (i.quality > 0 or i.class in (2,4))
and not (i.class = 0 and i.subclass = 5 and 0 = (select count(*) from tblDBCItemReagents ir where ir.item = i.id) and i.quality < 2)
EOF;
$sql = <<<EOF
SELECT g.item, g.bonusset, g.median, g.mean, g.stddev
FROM tblItemGlobal g
join tblDBCItem i on g.item=i.id
where g.region = ?
{$itemExcludeSql}
EOF;
$stmt = $db->prepare($sql);
$stmt->bind_param('s', $region);
$stmt->execute();
$result = $stmt->get_result();
while ($priceRow = $result->fetch_assoc()) {
$item = '' . $priceRow['item'] . ($priceRow['bonusset'] != '0' ? 'x' . $priceRow['bonusset'] : '');
$item_global[$item] = pack('LLL', round($priceRow['median'] / 100), round($priceRow['mean'] / 100), round($priceRow['stddev'] / 100));
}
$result->close();
$stmt->close();
$stmt = $db->prepare('SELECT species, breed, avg(price) `mean`, stddev(price) `stddev` FROM tblPetSummary group by species, breed');
$stmt->execute();
$result = $stmt->get_result();
while ($priceRow = $result->fetch_assoc()) {
$item = '' . $priceRow['species'] . 'b' . $priceRow['breed'];
$item_global[$item] = pack('LLL', 0, round($priceRow['mean'] / 100), round($priceRow['stddev'] / 100));
}
$result->close();
$stmt->close();
$sql = <<<EOF
SELECT tis.item, tis.bonusset,
datediff(now(), tis.lastseen) since,
round(ifnull(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), tis.price/100)) price,
round(ifnull(avg(if(timestampadd(hour, 72 + hours.h, ihh.`when`) > now(), 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, null)), tis.price/100)) pricerecent,
round(stddev(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)) pricestddev,
ceil(ivc.copper/100) vendorprice
FROM tblItemSummary tis
join tblDBCItem i on i.id = tis.item
join (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
left join tblItemHistoryHourly ihh on ihh.item = tis.item and ihh.house = tis.house and ihh.bonusset = tis.bonusset
left join tblDBCItemVendorCost ivc on ivc.item = i.id
WHERE tis.house = ?
{$itemExcludeSql}
group by tis.item, tis.bonusset
EOF;
for ($hx = 0; $hx < count($houses); $hx++) {
heartbeat();
if ($caughtKill) {
return;
}
DebugMessage('Finding item prices in house ' . $houses[$hx] . ' (' . round($hx / count($houses) * 100) . '%) ' . round(memory_get_usage() / 1048576));
$stmt = $db->prepare($sql);
$stmt->bind_param('i', $houses[$hx]);
$stmt->execute();
$result = $stmt->get_result();
while ($priceRow = $result->fetch_assoc()) {
$item = '' . $priceRow['item'] . ($priceRow['bonusset'] != '0' ? 'x' . $priceRow['bonusset'] : '');
if (!isset($item_avg[$item])) {
$item_avg[$item] = '';
}
if (!isset($item_stddev[$item])) {
$item_stddev[$item] = '';
}
if (!isset($item_recent[$item])) {
$item_recent[$item] = '';
}
if (!isset($item_days[$item])) {
$item_days[$item] = '';
}
$prc = intval($priceRow['price'], 10);
$usingVendor = $priceRow['vendorprice'] && intval($priceRow['vendorprice'], 10) < $prc && $priceRow['bonusset'] == '0';
if ($usingVendor) {
$prc = intval($priceRow['vendorprice'], 10);
}
$item_avg[$item] .= str_repeat(chr(0), 4 * $hx - strlen($item_avg[$item])) . pack('L', $prc);
$item_stddev[$item] .= str_repeat(chr(0), 4 * $hx - strlen($item_stddev[$item])) . pack('L', !$usingVendor && $priceRow['pricestddev'] ? intval($priceRow['pricestddev'], 10) : 0);
$item_recent[$item] .= str_repeat(chr(0), 4 * $hx - strlen($item_recent[$item])) . pack('L', !$usingVendor && $priceRow['pricerecent'] ? intval($priceRow['pricerecent'], 10) : $prc);
$item_days[$item] .= str_repeat(chr(255), $hx - strlen($item_days[$item])) . chr($priceRow['vendorprice'] ? 252 : min(251, intval($priceRow['since'], 10)));
}
$result->close();
$stmt->close();
}
$sql = <<<EOF
SELECT tps.species, tps.breed,
datediff(now(), tps.lastseen) since,
round(ifnull(avg(case hours.h
when 0 then phh.silver00 when 1 then phh.silver01 when 2 then phh.silver02 when 3 then phh.silver03
when 4 then phh.silver04 when 5 then phh.silver05 when 6 then phh.silver06 when 7 then phh.silver07
when 8 then phh.silver08 when 9 then phh.silver09 when 10 then phh.silver10 when 11 then phh.silver11
when 12 then phh.silver12 when 13 then phh.silver13 when 14 then phh.silver14 when 15 then phh.silver15
when 16 then phh.silver16 when 17 then phh.silver17 when 18 then phh.silver18 when 19 then phh.silver19
when 20 then phh.silver20 when 21 then phh.silver21 when 22 then phh.silver22 when 23 then phh.silver23
else null end), tps.price/100)) price,
round(ifnull(avg(if(timestampadd(hour, 72 + hours.h, phh.`when`) > now(), case hours.h
when 0 then phh.silver00 when 1 then phh.silver01 when 2 then phh.silver02 when 3 then phh.silver03
when 4 then phh.silver04 when 5 then phh.silver05 when 6 then phh.silver06 when 7 then phh.silver07
when 8 then phh.silver08 when 9 then phh.silver09 when 10 then phh.silver10 when 11 then phh.silver11
when 12 then phh.silver12 when 13 then phh.silver13 when 14 then phh.silver14 when 15 then phh.silver15
when 16 then phh.silver16 when 17 then phh.silver17 when 18 then phh.silver18 when 19 then phh.silver19
when 20 then phh.silver20 when 21 then phh.silver21 when 22 then phh.silver22 when 23 then phh.silver23
else null end, null)), tps.price/100)) pricerecent,
round(stddev(case hours.h
when 0 then phh.silver00 when 1 then phh.silver01 when 2 then phh.silver02 when 3 then phh.silver03
when 4 then phh.silver04 when 5 then phh.silver05 when 6 then phh.silver06 when 7 then phh.silver07
when 8 then phh.silver08 when 9 then phh.silver09 when 10 then phh.silver10 when 11 then phh.silver11
when 12 then phh.silver12 when 13 then phh.silver13 when 14 then phh.silver14 when 15 then phh.silver15
when 16 then phh.silver16 when 17 then phh.silver17 when 18 then phh.silver18 when 19 then phh.silver19
when 20 then phh.silver20 when 21 then phh.silver21 when 22 then phh.silver22 when 23 then phh.silver23
else null end)) pricestddev
FROM tblPetSummary tps
join (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
left join tblPetHistoryHourly phh on phh.species=tps.species and phh.house = tps.house and phh.breed=tps.breed
WHERE tps.house = ?
group by tps.species, tps.breed
EOF;
for ($hx = 0; $hx < count($houses); $hx++) {
heartbeat();
if ($caughtKill) {
return;
}
DebugMessage('Finding pet prices in house ' . $houses[$hx] . ' (' . round($hx / count($houses) * 100) . '%) ' . round(memory_get_usage() / 1048576));
$stmt = $db->prepare($sql);
$stmt->bind_param('i', $houses[$hx]);
$stmt->execute();
$result = $stmt->get_result();
while ($priceRow = $result->fetch_assoc()) {
$item = '' . $priceRow['species'] . 'b' . $priceRow['breed'];
if (!isset($item_avg[$item])) {
$item_avg[$item] = '';
}
if (!isset($item_stddev[$item])) {
$item_stddev[$item] = '';
}
if (!isset($item_recent[$item])) {
$item_recent[$item] = '';
}
if (!isset($item_days[$item])) {
$item_days[$item] = '';
}
$prc = intval($priceRow['price'], 10);
$item_avg[$item] .= str_repeat(chr(0), 4 * $hx - strlen($item_avg[$item])) . pack('L', $prc);
$item_stddev[$item] .= str_repeat(chr(0), 4 * $hx - strlen($item_stddev[$item])) . pack('L', $priceRow['pricestddev'] ? intval($priceRow['pricestddev'], 10) : 0);
$item_recent[$item] .= str_repeat(chr(0), 4 * $hx - strlen($item_recent[$item])) . pack('L', $priceRow['pricerecent'] ? intval($priceRow['pricerecent'], 10) : $prc);
$item_days[$item] .= str_repeat(chr(255), $hx - strlen($item_days[$item])) . chr(min(251, intval($priceRow['since'], 10)));
}
$result->close();
$stmt->close();
}
heartbeat();
if ($caughtKill) {
return;
}
DebugMessage('Making lua strings');
$priceLua = '';
$luaLines = 0;
$dataFuncIndex = 0;
foreach ($item_global as $item => $globalPriceList) {
heartbeat();
if ($caughtKill) {
return;
}
$globalPrices = array_values(unpack('L*', $globalPriceList));
$prices = isset($item_avg[$item]) ? array_values(unpack('L*', $item_avg[$item])) : [];
$recents = isset($item_recent[$item]) ? array_values(unpack('L*', $item_recent[$item])) : [];
$stddevs = isset($item_stddev[$item]) ? array_values(unpack('L*', $item_stddev[$item])) : [];
$priceBytes = 0;
for ($x = 0; $x < $globalSpots; $x++) {
if (!isset($globalPrices[$x])) {
$globalPrices[$x] = 0;
continue;
}
for ($y = 4; $y >= $priceBytes; $y--) {
if ($globalPrices[$x] >= pow(2, 8 * $y)) {
$priceBytes = $y + 1;
}
}
}
for ($x = 0; $x < count($houses); $x++) {
if (!isset($stddevs[$x])) {
$stddevs[$x] = 0;
}
if (!isset($recents[$x])) {
$recents[$x] = 0;
}
if (!isset($prices[$x])) {
$prices[$x] = 0;
continue;
}
for ($y = 4; $y >= $priceBytes; $y--) {
if ($prices[$x] >= pow(2, 8 * $y)) {
$priceBytes = $y + 1;
} elseif ($stddevs[$x] >= pow(2, 8 * $y)) {
$priceBytes = $y + 1;
} elseif ($recents[$x] >= pow(2, 8 * $y)) {
$priceBytes = $y + 1;
}
}
}
if ($priceBytes == 0) {
continue;
}
$priceString = chr($priceBytes);
for ($x = 0; $x < $globalSpots; $x++) {
$priceBin = '';
$price = $globalPrices[$x];
for ($y = 0; $y < $priceBytes; $y++) {
$priceBin = chr($price % 256) . $priceBin;
$price = $price >> 8;
}
$priceString .= $priceBin;
}
for ($x = 0; $x < count($prices); $x++) {
if (!isset($item_days[$item]) || ($thisPriceString = substr($item_days[$item], $x, 1)) === false) {
$thisPriceString = chr(255);
}
$priceBin = '';
$price = $prices[$x];
for ($y = 0; $y < $priceBytes; $y++) {
$priceBin = chr($price % 256) . $priceBin;
$price = $price >> 8;
}
$thisPriceString .= $priceBin;
$priceBin = '';
$price = $stddevs[$x];
for ($y = 0; $y < $priceBytes; $y++) {
$priceBin = chr($price % 256) . $priceBin;
$price = $price >> 8;
}
$thisPriceString .= $priceBin;
$priceBin = '';
$price = $recents[$x];
for ($y = 0; $y < $priceBytes; $y++) {
$priceBin = chr($price % 256) . $priceBin;
$price = $price >> 8;
}
$thisPriceString .= $priceBin;
$priceString .= $thisPriceString;
}
if ($luaLines == 0) {
$dataFuncIndex++;
$priceLua .= "dataFuncs[{$dataFuncIndex}] = function()\n";
}
$priceLua .= sprintf("addonTable.marketData['%s']=crop(%d,%s)\n", $item, $priceBytes, luaQuote($priceString));
if (++$luaLines >= 2000) {
$priceLua .= "end\n";
$luaLines = 0;
}
}
unset($items);
if ($luaLines > 0) {
$priceLua .= "end\n";
}
heartbeat();
if ($caughtKill) {
return;
}
DebugMessage('Setting realm indexes');
$houseLookup = array_flip($houses);
$stmt = $db->prepare('select rgh.realmguid, rgh.house from tblRealmGuidHouse rgh join tblRealm r on rgh.house = r.house and r.canonical is not null where r.region = ?');
$stmt->bind_param('s', $region);
$stmt->execute();
$result = $stmt->get_result();
$guids = DBMapArray($result);
$stmt->close();
$guidLua = '';
foreach ($guids as $guidRow) {
if (isset($houseLookup[$guidRow['house']])) {
$guidLua .= ($guidLua == '' ? '' : ',') . '[' . $guidRow['realmguid'] . ']=' . $houseLookup[$guidRow['house']];
}
}
heartbeat();
if ($caughtKill) {
return;
}
DebugMessage('Building final lua');
$dataAge = time();
$lua = <<<EOF
local addonName, addonTable = ...
addonTable.dataLoads = addonTable.dataLoads or {}
local realmIndex
local dataFuncs = {}
local tuj_substr = string.sub
local tuj_concat = table.concat
local function crop(priceSize, b)
local headerSize = 1 + priceSize * 3
local recordSize = 1 + priceSize * 3
local offset = 1 + headerSize + recordSize * realmIndex
return tuj_substr(b, 1, headerSize)..tuj_substr(b, offset, offset + recordSize - 1)
end
EOF;
$luaEnd = <<<EOF
local dataLoad = function(realmId)
local realmGuids = {{$guidLua}}
realmIndex = realmGuids[realmId]
if not realmIndex then
wipe(dataFuncs)
return false
end
addonTable.marketData = {}
addonTable.realmIndex = realmIndex
addonTable.dataAge = {$dataAge}
addonTable.region = "{$region}"
for i=1,#dataFuncs,1 do
dataFuncs[i]()
dataFuncs[i]=nil
end
wipe(dataFuncs)
return true
end
table.insert(addonTable.dataLoads, dataLoad)
EOF;
return pack('CCC', 239, 187, 191) . $lua . $priceLua . $luaEnd;
}
function GetRussianOwnerRealms($region)
{
global $db, $caughtKill;
$ruID = 0;
$ruSlug = '';
$ruToRun = array();
$stmt = $db->prepare('select id, slug from tblRealm where region=? and locale=\'ru_RU\' and ownerrealm is null');
$stmt->bind_param('s', $region);
$stmt->execute();
$stmt->bind_result($ruID, $ruSlug);
while ($stmt->fetch()) {
heartbeat();
if ($caughtKill) {
return;
}
DebugMessage("Getting ownerrealm for russian slug {$ruSlug}");
$url = GetBattleNetURL($region, 'wow/realm/status?realms=' . $ruSlug . '&locale=ru_RU');
$ruRealm = json_decode(FetchHTTP($url), true, 512, JSON_BIGINT_AS_STRING);
if (json_last_error() != JSON_ERROR_NONE) {
DebugMessage("{$url} did not return valid JSON");
continue;
}
if (!isset($ruRealm['realms']) || count($ruRealm['realms']) == 0) {
DebugMessage("{$url} returned no realms");
continue;
}
$ruOwner = str_replace(' ', '', $ruRealm['realms'][0]['name']);
$ruToRun[] = sprintf('update tblRealm set ownerrealm = \'%s\' where id = %d', $db->escape_string($ruOwner), $ruID);
}
$stmt->close();
if ($caughtKill) {
return;
}
foreach ($ruToRun as $sql) {
heartbeat();
if ($caughtKill) {
return;
}
if (!$db->real_query($sql)) {
DebugMessage(sprintf("%s: %s", $sql, $db->error), E_USER_WARNING);
}
}
}
function CleanOldHouses()
{
global $db, $caughtKill;
if ($caughtKill) {
return;
}
$sql = 'SELECT DISTINCT house FROM tblAuction WHERE house NOT IN (SELECT DISTINCT house FROM tblRealm)';
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$oldIds = DBMapArray($result);
$stmt->close();
$sql = 'DELETE FROM tblAuction WHERE house = %d LIMIT 2000';
foreach ($oldIds as $oldId) {
if ($caughtKill) {
return;
}
PrintImportantMessage('Clearing out auctions from old house ' . $oldId);
while (!$caughtKill) {
heartbeat();
$ok = $db->real_query(sprintf($sql, $oldId));
if (!$ok || $db->affected_rows == 0) {
break;
}
}
}
if ($caughtKill) {
return;
}
$sql = 'SELECT DISTINCT house FROM tblHouseCheck WHERE house NOT IN (SELECT DISTINCT house FROM tblRealm)';
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$oldIds = DBMapArray($result);
$stmt->close();
if (count($oldIds)) {
$db->real_query(sprintf('DELETE FROM tblHouseCheck WHERE house IN (%s)', implode(',', $oldIds)));
}
if ($caughtKill) {
return;
}
$sql = 'SELECT DISTINCT house FROM tblItemHistoryHourly WHERE house NOT IN (SELECT DISTINCT house FROM tblRealm)';
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$oldIds = DBMapArray($result);
$stmt->close();
$sql = 'DELETE FROM tblItemHistoryHourly WHERE house = %d LIMIT 2000';
foreach ($oldIds as $oldId) {
if ($caughtKill) {
return;
}
PrintImportantMessage('Clearing out item history from old house ' . $oldId);
while (!$caughtKill) {
heartbeat();
$ok = $db->real_query(sprintf($sql, $oldId));
if (!$ok || $db->affected_rows == 0) {
break;
}
}
}
if ($caughtKill) {
return;
}
$sql = 'SELECT DISTINCT house FROM tblItemSummary WHERE house NOT IN (SELECT DISTINCT house FROM tblRealm)';
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$oldIds = DBMapArray($result);
$stmt->close();
$sql = 'DELETE FROM tblItemSummary WHERE house = %d LIMIT 2000';
foreach ($oldIds as $oldId) {
if ($caughtKill) {
return;
}
PrintImportantMessage('Clearing out item summary from old house ' . $oldId);
while (!$caughtKill) {
heartbeat();
$ok = $db->real_query(sprintf($sql, $oldId));
if (!$ok || $db->affected_rows == 0) {
break;
}
}
}
if ($caughtKill) {
return;
}
$sql = 'SELECT DISTINCT house FROM tblPetHistoryHourly WHERE house NOT IN (SELECT DISTINCT house FROM tblRealm)';
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$oldIds = DBMapArray($result);
$stmt->close();
$sql = 'DELETE FROM tblPetHistoryHourly WHERE house = %d LIMIT 2000';
foreach ($oldIds as $oldId) {
if ($caughtKill) {
return;
}
PrintImportantMessage('Clearing out pet history from old house ' . $oldId);
while (!$caughtKill) {
heartbeat();
$ok = $db->real_query(sprintf($sql, $oldId));
if (!$ok || $db->affected_rows == 0) {
break;
}
}
}
if ($caughtKill) {
return;
}
$sql = 'SELECT DISTINCT house FROM tblPetSummary WHERE house NOT IN (SELECT DISTINCT house FROM tblRealm)';
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$oldIds = DBMapArray($result);
$stmt->close();
$sql = 'DELETE FROM tblPetSummary WHERE house = %d LIMIT 2000';
foreach ($oldIds as $oldId) {
if ($caughtKill) {
return;
}
PrintImportantMessage('Clearing out pet summary from old house ' . $oldId);
while (!$caughtKill) {
heartbeat();
$ok = $db->real_query(sprintf($sql, $oldId));
if (!$ok || $db->affected_rows == 0) {
break;
}
}
}
if ($caughtKill) {
return;
}
$sql = 'SELECT DISTINCT house FROM tblSnapshot WHERE house NOT IN (SELECT DISTINCT house FROM tblRealm)';
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$oldIds = DBMapArray($result);
$stmt->close();
$sql = 'DELETE FROM tblSnapshot WHERE house = %d LIMIT 2000';
foreach ($oldIds as $oldId) {
if ($caughtKill) {
return;
}
PrintImportantMessage('Clearing out snapshots from old house ' . $oldId);
while (!$caughtKill) {
heartbeat();
$ok = $db->real_query(sprintf($sql, $oldId));
if (!$ok || $db->affected_rows == 0) {
break;
}
}
}
}
*
****************************************************************************/
require_once "/libs/engine.php";
require_once "/libs/functions.php";
session_start();
$xml = "<result><code>";
if (checkVariable("key")) {
$key = $_REQUEST['key'];
$check_key = CheckKey($key);
if (!empty($check_key)) {
$engine = new HydroEngine("hydro");
if ($_REQUEST["f"] == "results") {
$xml .= results($engine);
} else {
if ($_REQUEST["f"] == "heartbeat") {
$xml .= heartbeat($engine);
} else {
$xml .= "3</code><codedesc>Invalid format";
}
}
} else {
$xml .= "1</code><codedesc>Invalid key";
}
} else {
$xml .= "2</code><codedesc>Required parameter missing";
}
$xml .= "</codedesc></result>";
echo $xml;
function checkVariable($name)
{
/****************************************************************************
} else {
switch ($data['task']['action']) {
case 'kill':
kill_client($data['task']['cid']);
break;
}
}
}
if ($data == NULL || !isset($data['action'])) {
p("Json decoding failed or in bad format. ( cid = {$connection->cid}, IP = {$connection->IP} )");
return;
}
switch ($data['action']) {
case 'heartbeat':
// 评测端信息更新
heartbeat($connection, $data);
break;
case 'update_state':
// 评测中更新状态
update_state($connection, $data);
break;
case 'update':
// 评测完更新结果
update($connection, $data);
break;
case 'login':
// 评测端登录
login($connection, $data);
break;
default:
p("Unknown Action ( cid = {$connection->cid}, IP = {$connection->IP} )");
function GetGuild(&$characterNames, $guild, $realmName)
{
global $db, $caughtKill, $allRealms;
heartbeat();
if ($caughtKill) {
return;
}
if (!isset($allRealms[$realmName])) {
DebugMessage('Could not find realm ' . $realmName);
return;
}
$guildId = 0;
$scanned = 0;
$stmt = $db->prepare('select id, ifnull(scanned,\'2000-01-01\') from tblGuild where realm = ? and name = ?');
$stmt->bind_param('is', $allRealms[$realmName]['id'], $guild);
$stmt->execute();
$stmt->bind_result($guildId, $scanned);
$hasRow = $stmt->fetch() === true;
$stmt->close();
if ($hasRow) {
if (strtotime($scanned) >= time() - 14 * 24 * 60 * 60) {
return;
}
} else {
$stmt = $db->prepare('insert into tblGuild (realm, name) values (?, ?)');
$stmt->bind_param('is', $allRealms[$realmName]['id'], $guild);
$stmt->execute();
$stmt->close();
$guildId = $db->insert_id;
}
DebugMessage("Getting guild <{$guild}> on {$realmName}");
$url = GetBattleNetURL($allRealms[$realmName]['region'], "wow/guild/" . $allRealms[$realmName]['slug'] . "/" . rawurlencode($guild) . "?fields=members");
$json = FetchHTTP($url);
if (!$json) {
return;
}
heartbeat();
if ($caughtKill) {
return;
}
$dta = json_decode($json, true);
if (json_last_error() != JSON_ERROR_NONE) {
return;
}
if (!isset($dta['members'])) {
return;
}
$charCount = 0;
$side = $dta['side'] + 1;
foreach ($dta['members'] as $member) {
if (!isset($member['character'])) {
continue;
}
if (!isset($member['character']['name'])) {
continue;
}
if (!isset($member['character']['realm'])) {
continue;
}
if (!isset($allRealms[$member['character']['realm']])) {
continue;
}
$charCount++;
$member['character']['gender']++;
// line up with db enum
$stmt = $db->prepare('insert into tblCharacter (name, realm, scanned, race, class, gender, level) values (?, ?, NOW(), ?, ?, ?, ?) on duplicate key update lastmodified=null, scanned=values(scanned), race=values(race), class=values(class), gender=values(gender), level=values(level)');
$stmt->bind_param('siiiii', $member['character']['name'], $allRealms[$member['character']['realm']]['id'], $member['character']['race'], $member['character']['class'], $member['character']['gender'], $member['character']['level']);
$stmt->execute();
$stmt->close();
unset($characterNames[$allRealms[$member['character']['realm']]['ownerrealm']][$member['character']['name']]);
}
$stmt = $db->prepare('update tblGuild set scanned=now(), side=?, members=? where id = ?');
$stmt->bind_param('iii', $side, $charCount, $guildId);
$stmt->execute();
$stmt->close();
}
$stmt->bind_result($id, $level);
while ($stmt->fetch()) {
$bonusLevelCache[$id] = $level;
}
$stmt->close();
$maxPacketSize = 0;
$stmt = $db->prepare('show variables like \'max_allowed_packet\'');
$stmt->execute();
$stmt->bind_result($nonsense, $maxPacketSize);
$stmt->fetch();
$stmt->close();
unset($nonsense);
$loopStart = time();
$toSleep = 0;
while (!$caughtKill && time() < $loopStart + 60 * 30) {
heartbeat();
sleep(min($toSleep, 10));
if ($caughtKill || APIMaintenance()) {
break;
}
ob_start();
$toSleep = NextDataFile();
ob_end_flush();
if ($toSleep === false) {
break;
}
}
DebugMessage('Done! Started ' . TimeDiff($startTime));
function GetDBLock($db, $lockName)
{
$now = microtime(true);
$stat['rx'] = $net['rx'] - $oldnet['rx'];
$stat['tx'] = $net['tx'] - $oldnet['tx'];
if ($stat['rx'] < 0) {
$stat['rx'] = $net['rx'];
}
if ($stat['tx'] < 0) {
$stat['tx'] = $net['tx'];
}
if ($stat['tx'] <= 0) {
die("Invalid time interval {$stat['tx']}");
}
$stat['tx'] /= $stat['ts'];
$stat['rx'] /= $stat['ts'];
$uptime = null;
sscanf(file_get_contents('/proc/uptime'), '%d', $uptime);
$stat['uptime'] = $uptime;
$ret = heartbeat(SERVER_ADDRESS, $stat['uptime'], $stat['rx'] * 8, $stat['tx'] * 8);
die($ret);
function heartbeat($name, $uptime, $rx, $tx)
{
$pass = md5($name . SERVER_PING_SALT);
$ch = curl_init(HEARTBEAT_API . "?address={$name}&uptime={$uptime}&rxrate={$rx}&txrate={$tx}&password={$pass}");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, REPORT_TIMEOUT);
$ret = curl_exec($ch);
if ($ret == false) {
$ret = curl_error($ch);
}
curl_close($ch);
return $ret;
}
function DeleteLimitLoop($db, $query, $limit = 5000)
{
global $caughtKill;
$rowCount = 0;
if ($caughtKill) {
return $rowCount;
}
$query .= " LIMIT {$limit}";
do {
heartbeat();
$ok = $db->real_query($query);
$rowCount += $affectedRows = $db->affected_rows;
} while (!$caughtKill && $ok && $affectedRows >= $limit);
return $rowCount;
}
function UpdateGlobalDataJson()
{
global $caughtKill, $db;
if ($caughtKill) {
return;
}
heartbeat();
DebugMessage("Updating global data json");
$stmt = $db->prepare('SELECT item, floor(avg(median)) median FROM tblItemGlobal where bonusset=0 group by item');
$stmt->execute();
$result = $stmt->get_result();
$prices = DBMapArray($result, null);
$stmt->close();
$json = [];
foreach ($prices as $priceRow) {
$json[$priceRow['item']] = $priceRow['median'];
}
file_put_contents(__DIR__ . '/../public/globalprices.json', json_encode($json, JSON_NUMERIC_CHECK | JSON_FORCE_OBJECT), LOCK_EX);
}
function AddDailyData()
{
global $db, $caughtKill;
if ($caughtKill) {
return;
}
$sql = <<<'EOF'
select distinct hc.house, date(sn.updated) dt
from tblHouseCheck hc
join tblSnapshot sn on sn.house = hc.house
where ifnull(hc.lastdaily, '2000-01-01') < date(timestampadd(day, -1, now()))
and sn.updated > timestampadd(day, 1, ifnull(hc.lastdaily, '2000-01-01'))
and sn.updated < date(now())
and sn.flags & 1 = 0
order by 1, 2
EOF;
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
$houses = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
DebugMessage(count($houses) . " houses need updates");
$sqlPattern = <<<'EOF'
replace into tblItemHistoryDaily
(SELECT `ihh`.item, `ihh`.house, `ihh`.`when`,
nullif(least(
ifnull(silver00, 4294967295),ifnull(silver01, 4294967295),ifnull(silver02, 4294967295),ifnull(silver03, 4294967295),
ifnull(silver04, 4294967295),ifnull(silver05, 4294967295),ifnull(silver06, 4294967295),ifnull(silver07, 4294967295),
ifnull(silver08, 4294967295),ifnull(silver09, 4294967295),ifnull(silver10, 4294967295),ifnull(silver11, 4294967295),
ifnull(silver12, 4294967295),ifnull(silver13, 4294967295),ifnull(silver14, 4294967295),ifnull(silver15, 4294967295),
ifnull(silver16, 4294967295),ifnull(silver17, 4294967295),ifnull(silver18, 4294967295),ifnull(silver19, 4294967295),
ifnull(silver20, 4294967295),ifnull(silver21, 4294967295),ifnull(silver22, 4294967295),ifnull(silver23, 4294967295)),4294967295) pricemin,
round((
ifnull(silver00, 0)+ifnull(silver01, 0)+ifnull(silver02, 0)+ifnull(silver03, 0)+
ifnull(silver04, 0)+ifnull(silver05, 0)+ifnull(silver06, 0)+ifnull(silver07, 0)+
ifnull(silver08, 0)+ifnull(silver09, 0)+ifnull(silver10, 0)+ifnull(silver11, 0)+
ifnull(silver12, 0)+ifnull(silver13, 0)+ifnull(silver14, 0)+ifnull(silver15, 0)+
ifnull(silver16, 0)+ifnull(silver17, 0)+ifnull(silver18, 0)+ifnull(silver19, 0)+
ifnull(silver20, 0)+ifnull(silver21, 0)+ifnull(silver22, 0)+ifnull(silver23, 0)
) / (24 -
isnull(silver00)-isnull(silver01)-isnull(silver02)-isnull(silver03)-
isnull(silver04)-isnull(silver05)-isnull(silver06)-isnull(silver07)-
isnull(silver08)-isnull(silver09)-isnull(silver10)-isnull(silver11)-
isnull(silver12)-isnull(silver13)-isnull(silver14)-isnull(silver15)-
isnull(silver16)-isnull(silver17)-isnull(silver18)-isnull(silver19)-
isnull(silver20)-isnull(silver21)-isnull(silver22)-isnull(silver23))) priceavg,
nullif(greatest(
ifnull(silver00, 0),ifnull(silver01, 0),ifnull(silver02, 0),ifnull(silver03, 0),
ifnull(silver04, 0),ifnull(silver05, 0),ifnull(silver06, 0),ifnull(silver07, 0),
ifnull(silver08, 0),ifnull(silver09, 0),ifnull(silver10, 0),ifnull(silver11, 0),
ifnull(silver12, 0),ifnull(silver13, 0),ifnull(silver14, 0),ifnull(silver15, 0),
ifnull(silver16, 0),ifnull(silver17, 0),ifnull(silver18, 0),ifnull(silver19, 0),
ifnull(silver20, 0),ifnull(silver21, 0),ifnull(silver22, 0),ifnull(silver23, 0)),0) pricemax,
coalesce(
silver00, silver01, silver02, silver03, silver04, silver05,
silver06, silver07, silver08, silver09, silver10, silver11,
silver12, silver13, silver14, silver15, silver16, silver17,
silver18, silver19, silver20, silver21, silver22, silver23) pricestart,
coalesce(
silver23, silver22, silver21, silver20, silver19, silver18,
silver17, silver16, silver15, silver14, silver13, silver12,
silver11, silver10, silver09, silver08, silver07, silver06,
silver05, silver04, silver03, silver02, silver01, silver00) priceend,
nullif(least(
ifnull(quantity00, 4294967295),ifnull(quantity01, 4294967295),ifnull(quantity02, 4294967295),ifnull(quantity03, 4294967295),
ifnull(quantity04, 4294967295),ifnull(quantity05, 4294967295),ifnull(quantity06, 4294967295),ifnull(quantity07, 4294967295),
ifnull(quantity08, 4294967295),ifnull(quantity09, 4294967295),ifnull(quantity10, 4294967295),ifnull(quantity11, 4294967295),
ifnull(quantity12, 4294967295),ifnull(quantity13, 4294967295),ifnull(quantity14, 4294967295),ifnull(quantity15, 4294967295),
ifnull(quantity16, 4294967295),ifnull(quantity17, 4294967295),ifnull(quantity18, 4294967295),ifnull(quantity19, 4294967295),
ifnull(quantity20, 4294967295),ifnull(quantity21, 4294967295),ifnull(quantity22, 4294967295),ifnull(quantity23, 4294967295)),4294967295) quantitymin,
round((
ifnull(quantity00, 0)+ifnull(quantity01, 0)+ifnull(quantity02, 0)+ifnull(quantity03, 0)+
ifnull(quantity04, 0)+ifnull(quantity05, 0)+ifnull(quantity06, 0)+ifnull(quantity07, 0)+
ifnull(quantity08, 0)+ifnull(quantity09, 0)+ifnull(quantity10, 0)+ifnull(quantity11, 0)+
ifnull(quantity12, 0)+ifnull(quantity13, 0)+ifnull(quantity14, 0)+ifnull(quantity15, 0)+
ifnull(quantity16, 0)+ifnull(quantity17, 0)+ifnull(quantity18, 0)+ifnull(quantity19, 0)+
ifnull(quantity20, 0)+ifnull(quantity21, 0)+ifnull(quantity22, 0)+ifnull(quantity23, 0)
) / (24 -
isnull(quantity00)-isnull(quantity01)-isnull(quantity02)-isnull(quantity03)-
isnull(quantity04)-isnull(quantity05)-isnull(quantity06)-isnull(quantity07)-
isnull(quantity08)-isnull(quantity09)-isnull(quantity10)-isnull(quantity11)-
isnull(quantity12)-isnull(quantity13)-isnull(quantity14)-isnull(quantity15)-
isnull(quantity16)-isnull(quantity17)-isnull(quantity18)-isnull(quantity19)-
isnull(quantity20)-isnull(quantity21)-isnull(quantity22)-isnull(quantity23))) quantityavg,
nullif(greatest(
ifnull(quantity00, 0),ifnull(quantity01, 0),ifnull(quantity02, 0),ifnull(quantity03, 0),
ifnull(quantity04, 0),ifnull(quantity05, 0),ifnull(quantity06, 0),ifnull(quantity07, 0),
ifnull(quantity08, 0),ifnull(quantity09, 0),ifnull(quantity10, 0),ifnull(quantity11, 0),
ifnull(quantity12, 0),ifnull(quantity13, 0),ifnull(quantity14, 0),ifnull(quantity15, 0),
ifnull(quantity16, 0),ifnull(quantity17, 0),ifnull(quantity18, 0),ifnull(quantity19, 0),
ifnull(quantity20, 0),ifnull(quantity21, 0),ifnull(quantity22, 0),ifnull(quantity23, 0)),0) quantitymax
FROM `tblItemHistoryHourly` ihh
JOIN `tblDBCItem` `i` ON `i`.`id` = `ihh`.`item`
WHERE i.stacksize > 1 and ihh.house = ? and ihh.`when` = ?)
EOF;
foreach ($houses as $houseRow) {
heartbeat();
if ($caughtKill) {
return;
}
if (!MCHouseLock($houseRow['house'])) {
continue;
}
$stmt = $db->prepare($sqlPattern);
$stmt->bind_param('is', $houseRow['house'], $houseRow['dt']);
$queryOk = $stmt->execute();
$rowCount = $db->affected_rows;
$stmt->close();
if (!$queryOk) {
DebugMessage("SQL error: " . $db->errno . ' ' . $db->error . " - " . substr(preg_replace('/[\\r\\n]/', ' ', $sqlPattern), 0, 500), E_USER_WARNING);
$rowCount = -1;
} else {
DebugMessage("{$rowCount} item daily rows updated for house {$houseRow['house']} for date {$houseRow['dt']}");
}
if ($rowCount >= 0) {
$stmt = $db->prepare('INSERT INTO tblHouseCheck (house, lastdaily) VALUES (?, ?) ON DUPLICATE KEY UPDATE lastdaily = values(lastdaily)');
$stmt->bind_param('is', $houseRow['house'], $houseRow['dt']);
$stmt->execute();
$stmt->close();
}
MCHouseUnlock($houseRow['house']);
}
}
function IndexRegionDay($region, $timestamp)
{
global $db, $indexCategories, $caughtKill;
heartbeat();
if ($caughtKill) {
exit;
}
$indexValues = [];
$total = 0;
$when = date('Y-m-d', $timestamp);
$month = (intval(date('Y', $timestamp), 10) - 2014) * 12 + intval(date('m', $timestamp), 10);
$day = date('d', $timestamp);
$dayPadded = str_pad($day, 2, '0', STR_PAD_LEFT);
DebugMessage("Processing {$region} {$when}");
$sql = <<<'EOF'
SELECT `mktslvr%1$s`
FROM `tblItemHistoryMonthly` `ihm`
JOIN `tblRealm` `r` ON `r`.`house` = `ihm`.`house` AND `r`.`canonical` IS NOT NULL
WHERE `r`.`region` = ?
AND `ihm`.`item` IN (%2$s)
AND `ihm`.`bonusset` = 0
AND `ihm`.`month` = ?
AND `mktslvr%1$s` IS NOT NULL
ORDER BY 1
EOF;
foreach ($indexCategories as $catName => $catItems) {
heartbeat();
if ($caughtKill) {
exit;
}
$stmt = $db->prepare(sprintf($sql, $dayPadded, implode(',', $catItems)));
$stmt->bind_param('si', $region, $month);
$stmt->execute();
$result = $stmt->get_result();
$prices = DBMapArray($result, null);
$stmt->close();
$total += $indexValues[$catName] = MedianValue($prices);
}
$sql = 'REPLACE INTO `tblMarketIndex` (`region`, `when`, `total`';
$sqlVals = ') VALUES (?, ?, ?';
$types = 'ssi';
$valOrdered = [null, null];
$valOrdered[] =& $region;
$valOrdered[] =& $when;
$valOrdered[] =& $total;
foreach (array_keys($indexValues) as $catName) {
$sql .= ", `{$catName}`";
$sqlVals .= ", ?";
$valOrdered[] =& $indexValues[$catName];
$types .= 'i';
}
$sql .= $sqlVals . ')';
$stmt = $db->prepare($sql);
$valOrdered[0] =& $stmt;
$valOrdered[1] =& $types;
call_user_func_array('mysqli_stmt_bind_param', $valOrdered);
if (!$stmt->execute()) {
DebugMessage("Error saving {$region} {$when}\n{$sql}\n" . print_r($valOrdered, true));
}
$stmt->close();
}
function heartbeatsleep($t)
{
global $caughtKill;
$interval = 10;
// seconds
$howMany = floor($t / $interval);
$until = time() + $t;
for ($x = 0; $x < $howMany && $until > time(); $x++) {
heartbeat();
if ($caughtKill) {
exit;
}
DebugMessage("Sleeping " . ($until - time()) . " seconds..");
sleep($interval);
}
heartbeat();
if ($caughtKill) {
exit;
}
if ($until > time()) {
DebugMessage("Sleeping " . ($until - time()) . " seconds..");
sleep($until - time());
}
}
function FetchPets($pets)
{
global $petMap;
$results = array();
foreach ($pets as &$id) {
heartbeat();
DebugMessage('Fetching pet ' . $id);
$url = GetBattleNetURL('us', 'wow/battlePet/species/' . $id);
$json = \Newsstand\HTTP::Get($url);
$dta = json_decode($json, true);
if (json_last_error() != JSON_ERROR_NONE || !isset($dta['speciesId'])) {
DebugMessage('Error fetching pet ' . $id . ' from battle.net..');
continue;
}
$results[$dta['speciesId']] = array('json' => $json);
foreach ($petMap as $ours => $details) {
if (!isset($dta[$details['name']])) {
if ($details['required']) {
DebugMessage('Pet ' . $dta['speciesId'] . ' did not have required column ' . $details['name'], E_USER_WARNING);
unset($results[$dta['speciesId']]);
continue 2;
}
$dta[$details['name']] = null;
}
if (is_bool($dta[$details['name']])) {
$results[$dta['speciesId']][$ours] = $dta[$details['name']] ? 1 : 0;
} else {
$results[$dta['speciesId']][$ours] = $dta[$details['name']];
}
}
}
return $results;
}
