示例#1
0
function MCGetHouse($house, $key = 'ts')
{
    global $memcache;
    static $houseKeys = array();
    if ($key == 'ts') {
        if (isset($houseKeys[$house])) {
            return $houseKeys[$house];
        }
        $houseKeys[$house] = $memcache->get('h' . $house . '_ts');
        if ($houseKeys[$house] === false) {
            $houseKeys[$house] = 1;
            MCSetHouse($house, $key, $houseKeys[$house]);
            // so we don't query a billion times on this key
            $altDb = DBConnect(true);
            if ($altDb) {
                $stmt = $altDb->prepare('SELECT max(unix_timestamp(updated)) FROM tblSnapshot WHERE house = ?');
                $stmt->bind_param('i', $house);
                $stmt->execute();
                $stmt->bind_result($houseKeys[$house]);
                $gotHouse = $stmt->fetch() === true;
                $stmt->close();
                if ($gotHouse) {
                    MCSetHouse($house, $key, $houseKeys[$house]);
                } else {
                    $houseKeys[$house] = 1;
                }
                $altDb->close();
            }
        }
        return $houseKeys[$house];
    }
    return MCGet('h' . $house . '_' . MCGetHouse($house) . '_' . $key);
}
示例#2
0
function TransmogGenericItemList($house, $params)
{
    global $db, $canCache;
    $key = 'transmog_gi2_' . md5(json_encode($params));
    if ($canCache && ($tr = MCGetHouse($house, $key)) !== false) {
        return $tr;
    }
    DBConnect();
    if (is_array($params)) {
        $joins = isset($params['joins']) ? $params['joins'] : '';
        $where = isset($params['where']) ? ' and ' . $params['where'] : '';
        $group = isset($params['group']) ? array_merge($params['group'], [false]) : null;
    } else {
        $joins = '';
        $group = null;
        $where = $params == '' ? '' : ' and ' . $params;
    }
    $sql = <<<EOF
select ab.id, ab.display, ab.buy, ab.class, ab.subclass, ifnull(ab.type, -1 & ab.subclass) `type`, ab.subclassname
from (
    select aa.*, if(@previd = aa.display, 0, @previd := aa.display) previd
    from (select @previd := 0) aasetup, (
        SELECT i.id, i.display, a.buy, i.class, i.subclass, i.type, concat_ws('-', i.class, i.subclass) subclassname
        FROM `tblDBCItem` i
        join tblAuction a on a.item=i.id
        {$joins}
        WHERE i.auctionable=1
        and i.quality > 1
        {$where}
        and i.display is not null
        and i.flags & 2 = 0
        and a.house = ?
        and a.buy > 0
        order by i.display, a.buy) aa
    ) ab
where ab.previd > 0
EOF;
    $stmt = $db->prepare($sql);
    if (!$stmt) {
        DebugMessage("Bad SQL: \n" . $sql, E_USER_ERROR);
    }
    $stmt->bind_param('i', $house);
    $stmt->execute();
    $result = $stmt->get_result();
    $tr = DBMapArray($result, $group);
    $stmt->close();
    MCSetHouse($house, $key, $tr);
    return $tr;
}
示例#3
0
function PetAuctions($house, $species)
{
    global $db;
    $key = 'battlepet_auctions2_' . $species;
    if (($tr = MCGetHouse($house, $key)) !== false) {
        return $tr;
    }
    DBConnect();
    $sql = <<<EOF
SELECT ap.breed, quantity, bid, buy, ap.level, ap.quality, s.realm sellerrealm, ifnull(s.name, '???') sellername
FROM `tblAuction` a
JOIN `tblAuctionPet` ap on a.house = ap.house and a.id = ap.id
left join tblSeller s on a.seller=s.id
WHERE a.house=? and a.item=82800 and ap.species=?
EOF;
    $stmt = $db->prepare($sql);
    $stmt->bind_param('ii', $house, $species);
    $stmt->execute();
    $result = $stmt->get_result();
    $tr = DBMapArray($result, array('breed', null));
    $stmt->close();
    MCSetHouse($house, $key, $tr);
    return $tr;
}
示例#4
0
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");
}
示例#5
0
function HouseBotSellers($house)
{
    global $db;
    $cacheKey = 'house_botsellers';
    if (($tr = MCGetHouse($house, $cacheKey)) !== false) {
        return $tr;
    }
    DBConnect();
    $items = '128159, 127736, 127738, 127732, 127731, 127737, 127735, 127730, 127734, 127733, 127718, 128158, 127720, 127714, 127713, 127719, 127717, 127712, 127716, 127715';
    $sql = <<<'EOF'
select s.realm, s.name
from (
    select seller, count(distinct `snapshot`) cnt
    from (
        SELECT sih.seller, sih.item, sih.`snapshot`
        FROM `tblSellerItemHistory` sih
        join tblSeller s on sih.seller = s.id
        join tblRealm r on s.realm = r.id
        where r.house = ?
        and sih.item in (%1$s)
        and s.firstseen > timestampadd(day, -14, now())
    ) z1
    group by seller
    having count(distinct item) >= 4
) z2
join tblSeller s on s.id = z2.seller
left join tblSellerItemHistory h on h.seller = z2.seller and h.item not in (%1$s)
where h.seller is null
and z2.cnt >= 3
and s.lastseen > timestampadd(hour, -48, now())
order by s.lastseen desc
limit 20
EOF;
    $stmt = $db->prepare(sprintf($sql, $items));
    $stmt->bind_param('i', $house);
    $stmt->execute();
    $result = $stmt->get_result();
    $tr = $result->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
    MCSetHouse($house, $cacheKey, $tr);
    return $tr;
}
示例#6
0
function CategoryDealsItemListCached($house, $iidList, $flags)
{
    if (count($iidList) == 0) {
        return array();
    }
    $auctionIds = [];
    $sortBy = [];
    $sql = '(';
    foreach ($iidList as $row) {
        $sql .= (strlen($sql) == 1 ? '' : ' or ') . '(i.id = ' . $row['item'] . ' and s.bonusset = ' . $row['bonusset'] . ')';
        $itemKey = $row['item'] . ':' . $row['bonusset'];
        $sortBy[] = $itemKey;
        if (isset($row['cheapestid'])) {
            $auctionIds[$itemKey] = $row['cheapestid'];
        }
    }
    $sql .= ')';
    $sortBy = array_flip($sortBy);
    if ($flags & CATEGORY_FLAGS_WITH_BONUSES) {
        $tr = CategoryBonusItemList($house, ['where' => $sql, 'cols' => 'g.median globalmedian', 'outside' => 'r2.globalmedian']);
    } else {
        $tr = CategoryRegularItemList($house, ['where' => $sql, 'cols' => 'g.median globalmedian']);
    }
    usort($tr, function ($a, $b) use($sortBy) {
        return $sortBy[$a['id'] . ':' . $a['bonusset']] - $sortBy[$b['id'] . ':' . $b['bonusset']];
    });
    static $allRecentDates = [];
    if (isset($allRecentDates[$house])) {
        $recentDates = $allRecentDates[$house];
    } else {
        $recentDates = MCGetHouse($house, 'category_disnapshots');
        if ($recentDates === false) {
            $db = DBConnect();
            $stmt = $db->stmt_init();
            $stmt->prepare('SELECT unix_timestamp(updated) upd, maxid FROM `tblSnapshot` WHERE house=? and updated > timestampadd(hour, -60, now()) order by updated');
            $stmt->bind_param('i', $house);
            $stmt->execute();
            $result = $stmt->get_result();
            $recentDates = DBMapArray($result, null);
            $stmt->close();
            MCSetHouse($house, 'category_disnapshots', $recentDates);
        }
        $allRecentDates[$house] = $recentDates;
    }
    if (count($recentDates) < 2) {
        return $tr;
    }
    $rolloverBump = 0;
    if ($recentDates[count($recentDates) - 1]['maxid'] < $recentDates[0]['maxid']) {
        $rolloverBump = 0x80000000;
    }
    foreach ($tr as &$row) {
        $row['posted'] = null;
        $itemKey = $row['id'] . ':' . $row['bonusset'];
        if (!isset($auctionIds[$itemKey])) {
            continue;
        }
        $myId = $auctionIds[$itemKey];
        if ($myId < 0x20000000) {
            $myId += $rolloverBump;
        }
        $x = count($recentDates) - 1;
        do {
            $maxId = $recentDates[$x]['maxid'];
            if ($maxId < 0x20000000) {
                $maxId += $rolloverBump;
            }
            if ($maxId < $myId) {
                break;
            }
            $row['posted'] = $recentDates[$x]['upd'];
        } while (--$x >= 0);
    }
    unset($row);
    return $tr;
}
示例#7
0
$locale = GetLocale();
$searchCacheKey = 'search_b2_' . $locale . '_' . md5($search);
if ($json = MCGetHouse($house, $searchCacheKey)) {
    PopulateLocaleCols($json['battlepets'], [['func' => 'GetPetNames', 'key' => 'id', 'name' => 'name']]);
    PopulateLocaleCols($json['items'], [['func' => 'GetItemNames', 'key' => 'id', 'name' => 'name'], ['func' => 'GetItemBonusTagsByTag', 'key' => 'tagurl', 'name' => 'bonustag']]);
    json_return($json);
}
DBConnect();
$json = array('items' => SearchItems($house, $search, $locale), 'sellers' => SearchSellers($house, $search), 'battlepets' => SearchBattlePets($house, $search, $locale));
$ak = array_keys($json);
foreach ($ak as $k) {
    if (count($json[$k]) == 0) {
        unset($json[$k]);
    }
}
MCSetHouse($house, $searchCacheKey, $json);
PopulateLocaleCols($json['battlepets'], [['func' => 'GetPetNames', 'key' => 'id', 'name' => 'name']]);
PopulateLocaleCols($json['items'], [['func' => 'GetItemNames', 'key' => 'id', 'name' => 'name'], ['func' => 'GetItemBonusTagsByTag', 'key' => 'tagurl', 'name' => 'bonustag']]);
json_return($json);
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
示例#8
0
function SellerPetAuctions($house, $seller)
{
    $cacheKey = 'seller_petauctions_' . $seller;
    if (($tr = MCGetHouse($house, $cacheKey)) !== false) {
        PopulateLocaleCols($tr, [['func' => 'GetPetNames', 'key' => 'species', 'name' => 'name']], true);
        return $tr;
    }
    $db = DBConnect();
    $sql = <<<EOF
SELECT ap.species, ap.breed, quantity, bid, buy, ap.level, ap.quality, p.icon, p.type, p.npc,
(SELECT ifnull(sum(quantity),0)
from tblAuctionPet ap2
join tblAuction a2 on a2.house = ap2.house and a2.id = ap2.id
where ap2.house=a.house and ap2.species = ap.species and ap2.level >= ap.level and
((a.buy > 0 and a2.buy > 0 and (a2.buy / a2.quantity < a.buy / a.quantity)) or (a.buy = 0 and (a2.bid / a2.quantity < a.bid / a.quantity)))) cheaper
FROM `tblAuction` a
JOIN `tblAuctionPet` ap on a.house = ap.house and a.id = ap.id
JOIN `tblDBCPet` `p` on `p`.`id` = `ap`.`species`
WHERE a.house = ? and a.seller = ? and a.item = 82800
EOF;
    $stmt = $db->prepare($sql);
    $stmt->bind_param('ii', $house, $seller);
    $stmt->execute();
    $result = $stmt->get_result();
    $tr = $result->fetch_all(MYSQLI_ASSOC);
    $result->close();
    $stmt->close();
    MCSetHouse($house, $cacheKey, $tr);
    PopulateLocaleCols($tr, [['func' => 'GetPetNames', 'key' => 'species', 'name' => 'name']], true);
    return $tr;
}
示例#9
0
文件: item.php 项目: erorus/newsstand
function ItemAuctions($house, $item)
{
    global $db;
    $cacheKey = 'item_auctions_lb3_' . $item;
    if (($tr = MCGetHouse($house, $cacheKey)) !== false) {
        foreach ($tr as &$rows) {
            PopulateLocaleCols($rows, [['func' => 'GetItemBonusNames', 'key' => 'bonuses', 'name' => 'bonusname'], ['func' => 'GetItemBonusTags', 'key' => 'bonuses', 'name' => 'bonustag'], ['func' => 'GetRandEnchantNames', 'key' => 'rand', 'name' => 'randname']], true);
        }
        unset($rows);
        return $tr;
    }
    DBConnect();
    $sql = <<<EOF
SELECT ifnull(ae.bonusset,0) bonusset, a.quantity, a.bid, a.buy, ifnull(ae.`rand`,0) `rand`, ifnull(ae.seed,0) `seed`, 
ifnull(@lootedLevel := ae.lootedlevel,0) `lootedlevel`, ifnull(ae.level, i.level) level,
s.realm sellerrealm, ifnull(s.name, '???') sellername,
concat_ws(':',ae.bonus1,ae.bonus2,ae.bonus3,ae.bonus4,ae.bonus5,ae.bonus6) bonuses
FROM `tblAuction` a
join tblDBCItem i on a.item=i.id
left join tblSeller s on a.seller=s.id
left join tblAuctionExtra ae on ae.house=a.house and ae.id=a.id
left join tblDBCRandEnchants re on re.id = ae.rand
WHERE a.house=? and a.item=?
group by a.id
EOF;
    $stmt = $db->prepare($sql);
    $stmt->bind_param('ii', $house, $item);
    $stmt->execute();
    $result = $stmt->get_result();
    $tr = DBMapArray($result, array('bonusset', null));
    $stmt->close();
    MCSetHouse($house, $cacheKey, $tr);
    foreach ($tr as &$rows) {
        PopulateLocaleCols($rows, [['func' => 'GetItemBonusNames', 'key' => 'bonuses', 'name' => 'bonusname'], ['func' => 'GetItemBonusTags', 'key' => 'bonuses', 'name' => 'bonustag'], ['func' => 'GetRandEnchantNames', 'key' => 'rand', 'name' => 'randname']], true);
    }
    unset($rows);
    return $tr;
}