function GetWatches($loginState)
{
$userId = $loginState['id'];
$cacheKey = SUBSCRIPTION_ITEM_CACHEKEY . $userId;
$items = MCGet($cacheKey);
if ($items === false) {
$sql = <<<EOF
select uw.seq, uw.region, uw.house,
uw.item, uw.bonusset,
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=i.id and ibs.bonusset=uw.bonusset order by ibs.observed desc limit 1),'') bonusurl,
i.level, i.icon, i.class,
uw.direction, uw.quantity, uw.price
from tblUserWatch uw
join tblDBCItem i on uw.item = i.id
left join tblBonusSet bs on uw.bonusset = bs.`set`
where uw.user = ?
and uw.deleted is null
group by uw.seq
EOF;
$db = DBConnect();
$stmt = $db->prepare($sql);
$stmt->bind_param('i', $userId);
$stmt->execute();
$result = $stmt->get_result();
$items = DBMapArray($result);
$stmt->close();
MCSet($cacheKey, $items);
}
PopulateLocaleCols($items, [['func' => 'GetItemNames', 'key' => 'item', 'name' => 'name'], ['func' => 'GetItemBonusTagsByTag', 'key' => 'tagurl', 'name' => 'bonustag']], true);
$cacheKey = SUBSCRIPTION_SPECIES_CACHEKEY . $userId;
$battlePets = MCGet($cacheKey);
if ($battlePets === false) {
$sql = <<<EOF
select uw.seq, uw.region, uw.house,
uw.species, uw.breed,
p.icon, p.type, p.npc,
uw.direction, uw.quantity, uw.price
from tblUserWatch uw
JOIN tblDBCPet p on uw.species=p.id
where uw.user = ?
and uw.deleted is null
EOF;
$db = DBConnect();
$stmt = $db->prepare($sql);
$stmt->bind_param('i', $userId);
$stmt->execute();
$result = $stmt->get_result();
$battlePets = DBMapArray($result);
$stmt->close();
MCSet($cacheKey, $battlePets);
}
PopulateLocaleCols($battlePets, [['func' => 'GetPetNames', 'key' => 'species', 'name' => 'name']], true);
$json = ['data' => $items['data'] + $battlePets['data'], 'hydrate' => array_merge($items['hydrate'], $battlePets['hydrate'])];
return $json;
}
function HouseDeals($house)
{
global $db;
$cacheKey = 'house_deals_l2';
if (($tr = MCGetHouse($house, $cacheKey)) !== false) {
PopulateLocaleCols($tr, [['func' => 'GetItemNames', 'key' => 'id', 'name' => 'name']]);
return $tr;
}
DBConnect();
$sql = <<<EOF
SELECT i.id
FROM `tblItemSummary` tis
join tblDBCItem i on tis.item = i.id
join tblItemGlobal g on g.item = tis.item and g.bonusset = tis.bonusset and g.region = ?
WHERE house = ?
and tis.quantity > 0
and i.quality > 0
and g.median > 1000000
order by tis.price / g.median asc
limit 10
EOF;
$region = GetRegion($house);
$stmt = $db->prepare($sql);
$stmt->bind_param('si', $region, $house);
$stmt->execute();
$result = $stmt->get_result();
$tr = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
MCSetHouse($house, $cacheKey, $tr);
PopulateLocaleCols($tr, [['func' => 'GetItemNames', 'key' => 'id', 'name' => 'name']]);
return $tr;
}
function CategoryBonusItemList($house, $params)
{
global $canCache;
$cacheKey = 'category_bil_' . md5(json_encode($params));
$skipLocales = is_array($params) && isset($params['locales']) && $params['locales'] == false;
if ($canCache && ($tr = MCGetHouse($house, $cacheKey)) !== false) {
if (!$skipLocales) {
PopulateLocaleCols($tr, [['func' => 'GetItemNames', 'key' => 'id', 'name' => 'name'], ['func' => 'GetItemBonusTags', 'key' => 'bonusurl', 'name' => 'bonustag']]);
}
return $tr;
}
$db = DBConnect();
if (is_array($params)) {
$cols = isset($params['cols']) ? ', ' . $params['cols'] : '';
$joins = isset($params['joins']) ? $params['joins'] : '';
$where = isset($params['where']) ? ' and ' . $params['where'] : '';
$outside = isset($params['outside']) ? $params['outside'] . ', ' : '';
$rowKey = isset($params['key']) ? $params['key'] : false;
} else {
$cols = $joins = $outside = '';
$where = $params == '' ? '' : ' and ' . $params;
$rowKey = false;
}
$sql = <<<EOF
select r2.id, r2.icon, r2.classid, r2.quantity, r2.lastseen, r2.bonusset, r2.quantity, r2.lastseen, r2.baselevel,
@level := ifnull(ae.level, ifnull((select ils.level from tblItemLevelsSeen ils where ils.item = r2.id and ils.bonusset=r2.bonusset order by if(ils.level=r2.baselevel,0,1), ils.level limit 1), r2.baselevel)) level,
ifnull(a.buy, round(r2.price * pow(1.15, (cast(@level as signed) - cast(r2.baselevel as signed))/15))) price,
round((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 = r2.id and ihh.bonusset = r2.bonusset)
* pow(1.15,(cast(@level as signed) - cast(r2.baselevel as signed))/15)) avgprice,
ifnull((select group_concat(distinct bs.tagid order by 1 separator '.') from tblBonusSet bs where bs.set = r2.bonusset), '') tagurl,
if(ae.id is null, r2.defaultbonusurl, concat_ws(':', nullif(ae.bonus1,0), nullif(ae.bonus2,0), nullif(ae.bonus3,0), nullif(ae.bonus4,0), nullif(ae.bonus5,0), nullif(ae.bonus6,0))) bonusurl,
{$outside} ae.lootedlevel, ae.`rand`, ae.seed
from (
select i.id, i.icon, i.class as classid, i.level baselevel,
s.price, s.quantity, unix_timestamp(s.lastseen) lastseen, s.bonusset,
(select concat_ws(':', nullif(ibs.bonus1,0), nullif(ibs.bonus2,0), nullif(ibs.bonus3,0), nullif(ibs.bonus4,0)) from tblItemBonusesSeen ibs where ibs.item=i.id and ibs.bonusset=s.bonusset order by ibs.observed desc limit 1) defaultbonusurl,
(select a.id
from tblAuction a
left join tblAuctionExtra ae on a.house = ae.house and a.id = ae.id
where a.house = ?
and a.item = i.id
and ifnull(ae.bonusset,0) = s.bonusset
and a.buy > 0
order by a.buy
limit 1) cheapestaucid {$cols}
from tblDBCItem i
join tblItemSummary s on s.item = i.id and s.house = ?
join tblItemGlobal g on g.item = i.id + 0 and g.bonusset = s.bonusset and g.region = ?
{$joins}
where ifnull(i.auctionable,1) = 1
{$where}
) r2
left join tblAuction a on a.house = ? and a.id = r2.cheapestaucid
left join tblAuctionExtra ae on ae.house = ? and ae.id = r2.cheapestaucid
EOF;
$region = GetRegion($house);
$stmt = $db->stmt_init();
if (!$stmt->prepare($sql)) {
DebugMessage("Bad SQL: \n" . $sql, E_USER_ERROR);
}
$stmt->bind_param('iiisii', $house, $house, $house, $region, $house, $house);
$stmt->execute();
$tr = [];
$row = [];
$params = [];
$fields = $stmt->result_metadata()->fetch_fields();
foreach ($fields as $field) {
$params[] =& $row[$field->name];
}
call_user_func_array([$stmt, 'bind_result'], $params);
while ($stmt->fetch()) {
$unreferenced = [];
foreach ($row as $k => $v) {
$unreferenced[$k] = $v;
}
if ($rowKey) {
$tr[$unreferenced[$rowKey]] = $unreferenced;
} else {
$tr[] = $unreferenced;
}
}
$stmt->close();
MCSetHouse($house, $cacheKey, $tr);
if (!$skipLocales) {
PopulateLocaleCols($tr, [['func' => 'GetItemNames', 'key' => 'id', 'name' => 'name'], ['func' => 'GetItemBonusTags', 'key' => 'bonusurl', 'name' => 'bonustag']]);
}
return $tr;
}
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
join tblDBCItemNameDescription ind on ind.id = ib.nameid
where ind.`desc_{$locale}` is not null
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;
}
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;
}
