protected function updateListings($listings)
{
$now = new DateTime();
$item = ItemQuery::create()->findOneByDataId($listings['id']);
$item->setLastUpdated($now);
$sell = $listings['sells'];
$buy = $listings['buys'];
$lowestSell = null;
$maxBuy = null;
$sellQuantityAmount = 0;
$sellListingsAmount = 0;
if (count($sell)) {
$lowestSell = min(array_map(function ($row) {
return $row['unit_price'];
}, $sell));
foreach ($sell as $s) {
$sellQuantityAmount += $s['quantity'];
$sellListingsAmount += $s['listings'];
}
}
$sellListing = new SellListing();
$sellListing->setItem($item);
$sellListing->setListingDatetime($now);
$sellListing->setQuantity($sellQuantityAmount);
$sellListing->setListings($sellListingsAmount);
if ($lowestSell) {
$sellListing->setUnitPrice($lowestSell);
$item->setMinSaleUnitPrice($lowestSell);
} else {
$sellListing->setUnitPrice($item->getMinSaleUnitPrice());
}
$item->setSaleAvailability($sellQuantityAmount);
$sellListing->save();
$buyQuantityAmount = 0;
$buyListingsAmount = 0;
if (count($buy)) {
$maxBuy = max(array_map(function ($row) {
return $row['unit_price'];
}, $buy));
foreach ($buy as $b) {
$buyQuantityAmount += $b['quantity'];
$buyListingsAmount += $b['listings'];
}
}
$buyListing = new BuyListing();
$buyListing->setItem($item);
$buyListing->setListingDatetime($now);
$buyListing->setQuantity($buyQuantityAmount);
$buyListing->setListings($buyListingsAmount);
if ($maxBuy) {
$buyListing->setUnitPrice($maxBuy);
$item->setMaxOfferUnitPrice($maxBuy);
} else {
$buyListing->setUnitPrice($item->getMaxOfferUnitPrice());
}
$item->setOfferAvailability($buyQuantityAmount);
$buyListing->save();
$item->save();
}
public function __construct($input)
{
if ($input instanceof Item) {
$this->item = $input;
} else {
$this->item = ItemQuery::create()->findPk($input);
}
}
/**
* @param $API_JSON
* @param $offset
*/
function processApiData($API_JSON, $offset)
{
$APIItems = APIItemV2::getMultipleItemsByJSON($API_JSON);
try {
$itemCount = $offset;
foreach ($APIItems as $APIItem) {
$itemCount++;
if ($APIItem == null) {
print "Skipped item {$itemCount} on page {$offset}.\n";
continue;
}
echo "{$itemCount}: {$APIItem->getName()} (ID: {$APIItem->getItemId()})\n";
$itemData = array('TypeId' => getOrCreateTypeID($APIItem->getMarketType()), 'DataId' => $APIItem->getItemId(), 'Name' => $APIItem->getName(), 'RestrictionLevel' => $APIItem->getLevel(), 'Rarity' => getRarityID($APIItem->getRarity()), 'VendorSellPrice' => $APIItem->getVendorValue(), 'Img' => $APIItem->getImageURL(), 'RarityWord' => $APIItem->getRarity(), 'UnsellableFlag' => $APIItem->isUnsellable());
$item = ItemQuery::create()->findPK($APIItem->getItemId());
if ($item === null) {
$item = new Item();
}
$item->fromArray($itemData);
$itemType = ItemTypeQuery::create()->findPk($itemData['TypeId']);
if ($itemType !== null) {
if ($APIItem->getSubType() !== null) {
$itemSubType = ItemSubTypeQuery::create()->findOneByTitle($APIItem->getDBSubType());
if ($itemSubType === null) {
//All of the below types are known to not exist in the market data with an ID (by this name).
//Rune/Sigil/Utility/Gem/Booze/Halloween/LargeBundle/RentableContractNpc/ContractNPC/UnlimitedConsumable
//TwoHandedToy/AppearanceChange/Immediate/Unknown
$itemSubTypes = ItemSubTypeQuery::create()->filterByMainTypeId($itemData['TypeId'])->withColumn('MAX(id)', 'MAXid')->find();
$SubTypeID = $itemSubTypes[0]->getMAXid() + 1;
$itemSubType = new ItemSubType();
$itemSubType->fromArray(array('Id' => $SubTypeID, 'MainTypeId' => $itemData['TypeId'], 'Title' => $APIItem->getDBSubType()));
$itemSubType->save();
$itemType->addSubType($itemSubType);
$item->setItemSubType($itemSubType);
}
$itemType->addSubType($itemSubType);
$item->setItemSubType($itemSubType);
}
$item->setItemType($itemType);
}
$item->save();
}
} catch (Exception $e) {
echo "failed [[ {$e->getMessage()} ]] .. \n";
}
}
public function superviseItemListingDB($type = null)
{
Propel::disableInstancePooling();
$q = ItemQuery::create();
$q->select('DataId');
$queueManager = $this->getItemListingDBQueueManager();
if ($type instanceof ItemType) {
$q->filterByType($type);
} else {
if (is_numeric($type)) {
$q->filterByTypeId($type);
}
}
$q->filterByUnsellableFlag(false);
list($exists, $nexists) = $queueManager->multi_exists($q->find()->toArray(), true);
if ($nexists) {
$lowestprio = $queueManager->getLowestPrio();
foreach ($nexists as $id) {
$queueItem = new ItemListingDBQueueItem($id);
$queueManager->enqueue($queueItem, $lowestprio);
}
}
Propel::enableInstancePooling();
}
$ii = 0;
foreach (array_chunk($data['recipes'], 1000) as $recipes) {
//Add all curl requests to the EpiCurl instance.
foreach ($recipes as $recipe_id) {
$i++;
$ch = curl_init(getAppConfig('gw2spidy.gw2api_url') . "/v1/recipe_details.json?recipe_id={$recipe_id}");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$recipe_curls[$recipe_id] = $multi_curl->addCurl($ch);
echo "[{$i} / {$recipe_count}]: {$recipe_id}\n";
}
foreach ($recipes as $recipe_id) {
$ii++;
try {
echo "[{$ii} / {$recipe_count}] ";
$recipe_details = json_decode($recipe_curls[$recipe_id]->data, true);
$created_item = ItemQuery::create()->findPK($recipe_details['output_item_id']);
if (!$created_item) {
throw new NoResultItemException("no result [[ {$recipe_details['output_item_id']} ]]");
}
echo $created_item->getName() . "\n";
//If a recipe has multiple disciplines, treat each one like a separate recipe to be inserted.
foreach ($recipe_details['disciplines'] as $discipline) {
$recipe = new stdClass();
$recipe->DataID = $recipe_id;
$recipe->Name = $created_item->getName();
$recipe->Rating = (int) $recipe_details['min_rating'];
$recipe->Type = $disciplines[$discipline];
$recipe->Count = (int) $recipe_details['output_item_count'];
$recipe->CreatedItemId = (int) $recipe_details['output_item_id'];
$recipe->RequiresRecipeItem = in_array("LearnedFromItem", $recipe_details['flags']);
$recipe->Ingredients = array();
use GW2Spidy\NewQueue\RequestSlotManager;
use GW2Spidy\NewQueue\QueueHelper;
use Symfony\Component\HttpFoundation\Request;
use GW2Spidy\DB\ItemQuery;
/**
* ----------------------
* route /
* ----------------------
*/
$app->get("/", function () use($app) {
// workaround for now to set active menu item
$app->setHomeActive();
$onehourago = new DateTime();
$onehourago->sub(new DateInterval('PT1H'));
$trendingUp = ItemQuery::create()->filterByLastPriceChanged($onehourago, \Criteria::GREATER_EQUAL)->filterBySalePriceChangeLastHour(500, \Criteria::LESS_EQUAL)->filterBySaleAvailability(200, \Criteria::GREATER_EQUAL)->filterByOfferAvailability(200, \Criteria::GREATER_EQUAL)->addDescendingOrderByColumn("sale_price_change_last_hour")->limit(3)->find();
$trendingDown = ItemQuery::create()->filterByLastPriceChanged($onehourago, \Criteria::GREATER_EQUAL)->filterBySalePriceChangeLastHour(500, \Criteria::LESS_EQUAL)->filterBySaleAvailability(200, \Criteria::GREATER_EQUAL)->filterByOfferAvailability(200, \Criteria::GREATER_EQUAL)->addAscendingOrderByColumn("sale_price_change_last_hour")->limit(3)->find();
$summary = gem_summary();
return $app['twig']->render('index.html.twig', array('trending_up' => $trendingUp, 'trending_down' => $trendingDown) + (array) $summary);
})->bind('homepage');
/**
* ----------------------
* route /faq
* ----------------------
*/
$app->get("/faq", function () use($app) {
$app->setFAQActive();
return $app['twig']->render('faq.html.twig');
})->bind('faq');
/**
* ----------------------
* route /status
<?php
use GW2Spidy\DB\ItemQuery;
use GW2Spidy\DB\SellListingQuery;
use GW2Spidy\NewQueue\ItemListingDBQueueItem;
use GW2Spidy\NewQueue\ItemListingDBQueueManager;
use GW2Spidy\NewQueue\ItemListingDBQueueWorker;
require dirname(__FILE__) . '/../autoload.php';
$queueManager = new ItemListingDBQueueManager();
$queueWorker = new ItemListingDBQueueWorker($queueManager);
$item = ItemQuery::create()->findPk($argv[1]);
if (!$item) {
die("failed to find item");
}
var_dump($item->getName(), $item->getItemTypeId());
$queueItem = new ItemListingDBQueueItem($item);
var_dump($queueItem->getItem()->getQueuePriority());
if (isset($argv[2]) && strstr("search", $argv[2])) {
$queueItem = array($queueItem);
}
var_dump(is_array($queueItem));
$queueWorker->work($queueItem);
$l = SellListingQuery::create()->filterByItemId($item->getDataId())->orderByListingDatetime(\Criteria::DESC)->limit(1)->findOne();
var_dump($l->toArray());
$r->setName($row['Name']);
$r->setRating($row['Rating']);
$r->setCount($row['Count']);
$r->setDisciplineId($row['Type']);
$r->setRequiresUnlock(isset($row['RequiresRecipeItem']) && $row['RequiresRecipeItem'] !== false);
if (!($result = ItemQuery::create()->findOneByDataId($row['CreatedItemId']))) {
throw new NoResultItemException("no result [[ {$row['CreatedItemId']} ]]");
} else {
$r->setResultItem($result);
}
// grab old ingredients
$oldRIs = $r->getIngredients();
// loop over new ingredients
foreach ($row['Ingredients'] as $ingrow) {
// check if we know the item
if (!($item = ItemQuery::create()->findOneByDataId($ingrow['ItemID']))) {
throw new NoIngredientItemException("no ingredient [[ {$ingrow['ItemID']} ]]");
} else {
// see if we can match a previously imported ingredient for this recipe
$foundOld = false;
foreach ($oldRIs as $oldRI) {
if ($oldRI->getItemId() == $item->getDataId()) {
// mark the recipe
$oldRI->setOkOnImport();
$foundOld = true;
// update the count if it changed
if ($oldRI->getCount() != $ingrow['Count']) {
$oldRI->setCount($ingrow['Count']);
$oldRI->save();
}
}
<?php
use GW2Spidy\Dataset\GemDatasetCleaner;
use GW2Spidy\DB\ItemQuery;
use GW2Spidy\Dataset\ItemDatasetCleaner;
require dirname(__FILE__) . '/../autoload.php';
$t = microtime(true);
function mytime()
{
$r = microtime(true) - $GLOBALS['t'];
$GLOBALS['t'] = microtime(true);
return $r;
}
$q = ItemQuery::create()->select('dataId');
if (isset($argv[1])) {
$q->filterByDataId($argv[1]);
}
$items = $q->find();
var_dump(mytime());
$cleaner = new GemDatasetCleaner(GemDatasetCleaner::TYPE_GEM_TO_GOLD);
$countM = $cleaner->clean(ItemDatasetCleaner::CLEANUP_MONTH);
$countW = $cleaner->clean(ItemDatasetCleaner::CLEANUP_WEEK);
unset($cleaner);
echo "[GEMS][GEM_TO_GOLD] cleaned [{$countM}] > month old and [{$countW}] > week old hours in " . mytime() . ", mem @ [" . memory_get_usage(true) . "] \n";
@ob_flush();
$cleaner = new GemDatasetCleaner(GemDatasetCleaner::TYPE_GOLD_TO_GEM);
$countM = $cleaner->clean(ItemDatasetCleaner::CLEANUP_MONTH);
$countW = $cleaner->clean(ItemDatasetCleaner::CLEANUP_WEEK);
unset($cleaner);
echo "[GEMS][GOLD_TO_GEM] cleaned [{$countM}] > month old and [{$countW}] > week old hours in " . mytime() . ", mem @ [" . memory_get_usage(true) . "] \n";
@ob_flush();
require dirname(__FILE__) . '/../autoload.php';
$t = microtime(true);
function mytime()
{
$r = microtime(true) - $GLOBALS['t'];
$GLOBALS['t'] = microtime(true);
return $r;
}
$modnum = null;
$modmod = null;
if (isset($argv[1], $argv[2])) {
$modnum = $argv[1];
$modmod = $argv[2];
}
$dm = DatasetManager::getInstance();
$q = ItemQuery::create();
if ($modnum && $modmod) {
$q->where("data_id % {$modmod} = {$modnum}");
}
var_dump(mytime());
if (!is_null($modnum) && $modnum != 0) {
do {
echo "[[ " . mytime() . " ]] [[ TYPE_GEM_TO_GOLD ]] \n";
$ds = $dm->getGemDataset(GemExchangeDataset::TYPE_GEM_TO_GOLD);
} while (!$ds->uptodate);
do {
echo "[[ " . mytime() . " ]] [[ TYPE_GOLD_TO_GEM ]] \n";
$ds = $dm->getGemDataset(GemExchangeDataset::TYPE_GOLD_TO_GEM);
} while (!$ds->uptodate);
}
foreach ($q->find() as $item) {
/**
* Removes this object from datastore and sets delete attribute.
*
* @param PropelPDO $con
* @return void
* @throws PropelException
* @throws Exception
* @see BaseObject::setDeleted()
* @see BaseObject::isDeleted()
*/
public function delete(PropelPDO $con = null)
{
if ($this->isDeleted()) {
throw new PropelException("This object has already been deleted.");
}
if ($con === null) {
$con = Propel::getConnection(ItemPeer::DATABASE_NAME, Propel::CONNECTION_WRITE);
}
$con->beginTransaction();
try {
$deleteQuery = ItemQuery::create()->filterByPrimaryKey($this->getPrimaryKey());
$ret = $this->preDelete($con);
if ($ret) {
$deleteQuery->delete($con);
$this->postDelete($con);
$con->commit();
$this->setDeleted(true);
} else {
$con->commit();
}
} catch (Exception $e) {
$con->rollBack();
throw $e;
}
}
/**
* Returns a new ItemQuery object.
*
* @param string $modelAlias The alias of a model in the query
* @param ItemQuery|Criteria $criteria Optional Criteria to build the query from
*
* @return ItemQuery
*/
public static function create($modelAlias = null, $criteria = null)
{
if ($criteria instanceof ItemQuery) {
return $criteria;
}
$query = new ItemQuery();
if (null !== $modelAlias) {
$query->setModelAlias($modelAlias);
}
if ($criteria instanceof Criteria) {
$query->mergeWith($criteria);
}
return $query;
}
/**
* If this collection has already been initialized with
* an identical criteria, it returns the collection.
* Otherwise if this ItemType is new, it will return
* an empty collection; or if this ItemType has previously
* been saved, it will retrieve related Items from storage.
*
* This method is protected by default in order to keep the public
* api reasonable. You can provide public methods for those you
* actually need in ItemType.
*
* @param Criteria $criteria optional Criteria object to narrow the query
* @param PropelPDO $con optional connection object
* @param string $join_behavior optional join type to use (defaults to Criteria::LEFT_JOIN)
* @return PropelObjectCollection|Item[] List of Item objects
*/
public function getItemsJoinItemSubType($criteria = null, $con = null, $join_behavior = Criteria::LEFT_JOIN)
{
$query = ItemQuery::create(null, $criteria);
$query->joinWith('ItemSubType', $join_behavior);
return $this->getItems($query, $con);
}
// a recipe item, should link to the recipe
if (strpos($item->getName(), "Recipe: ") === 0) {
$recipes = RecipeQuery::create()->findByName(substr($item->getName(), strlen("Recipe: ")));
$recipe = count($recipes) ? $recipes[0] : null;
} else {
$recipe = null;
}
return $app['twig']->render('item.html.twig', array('item' => $item, 'recipe' => $recipe, 'ingredientInRecipes' => $ingredientInRecipes));
})->assert('dataId', '\\d+')->bind('item');
/**
* ----------------------
* route /chart
* ----------------------
*/
$app->get("/chart/{dataId}", function ($dataId) use($app) {
$item = ItemQuery::create()->findPK($dataId);
if (!$item) {
return $app->abort(404, "Page does not exist.");
}
$chart = array();
$sellListings = DatasetManager::getInstance()->getItemDataset($item, ItemDataset::TYPE_SELL_LISTING);
$sellListingsVolume = DatasetManager::getInstance()->getItemVolumeDataset($item, ItemVolumeDataset::TYPE_SELL_LISTING);
$buyListings = DatasetManager::getInstance()->getItemDataset($item, ItemDataset::TYPE_BUY_LISTING);
$buyListingsVolume = DatasetManager::getInstance()->getItemVolumeDataset($item, ItemVolumeDataset::TYPE_BUY_LISTING);
/*---------------
* MV AVG VOLUME SELL
*---------------*/
$chart[] = array('data' => $sellListingsVolume->getDailyMvAvgDataForChart(), 'name' => "Sell Listings Volume 1 Day Mv Avg", 'visible' => true, 'yAxis' => 1, 'type' => 'column');
/*---------------
* MV AVG VOLUME BUY
*---------------*/
public function connect(Application $app)
{
$toInt = function ($val) {
return (int) $val;
};
$controllers = $app['controllers_factory'];
/**
* ----------------------
* route /api
* ----------------------
*/
$controllers->get("/{format}/{secret}", function ($format, $secret) use($app) {
$format = strtolower($format);
// check if the secret is in the configured allowed api_secrets
if (!in_array($secret, $app['gw2spidy']['api_secrets']) && !$app['debug']) {
return $app->redirect("/");
}
Propel::disableInstancePooling();
$items = ItemQuery::create()->find();
if ($format == 'csv') {
header('Content-type: text/csv');
header('Content-disposition: attachment; filename=item_data_' . date('Ymd-His') . '.csv');
ob_start();
echo implode(",", ItemPeer::getFieldNames(BasePeer::TYPE_FIELDNAME)) . "\n";
foreach ($items as $item) {
echo implode(",", $item->toArray(BasePeer::TYPE_FIELDNAME)) . "\n";
}
return ob_get_clean();
} else {
if ($format == 'json') {
header('Content-type: application/json');
header('Content-disposition: attachment; filename=item_data_' . date('Ymd-His') . '.json');
$json = array();
foreach ($items as $item) {
$json[$item->getDataId()] = $item->toArray(BasePeer::TYPE_FIELDNAME);
}
return json_encode($json);
}
}
})->assert('format', 'csv|json');
/**
* ----------------------
* route /api/item
* ----------------------
*/
$controllers->get("/listings/{dataId}/{type}/{format}/{secret}", function ($dataId, $type, $format, $secret) use($app) {
$format = strtolower($format);
// check if the secret is in the configured allowed api_secrets
if (!in_array($secret, $app['gw2spidy']['api_secrets']) && !$app['debug']) {
return $app->redirect("/");
}
$item = ItemQuery::create()->findPK($dataId);
if (!$item) {
return $app->abort(404, "Page does not exist.");
}
$fields = array();
$listings = array();
if ($type == 'sell') {
$fields = SellListingPeer::getFieldNames(BasePeer::TYPE_FIELDNAME);
$listings = SellListingQuery::create()->findByItemId($item->getDataId());
} else {
$fields = BuyListingPeer::getFieldNames(BasePeer::TYPE_FIELDNAME);
$listings = BuyListingQuery::create()->findByItemId($item->getDataId());
}
if ($format == 'csv') {
header('Content-type: text/csv');
header('Content-disposition: attachment; filename=listings_data_' . $item->getDataId() . '_' . $type . '_' . date('Ymd-His') . '.csv');
ob_start();
echo implode(",", $fields) . "\n";
foreach ($listings as $listing) {
$data = $listing->toArray(BasePeer::TYPE_FIELDNAME);
$date = new DateTime("{$listing->getListingDatetime()}");
$date->setTimezone(new DateTimeZone('UTC'));
$data['listing_datetime'] = $date->format("Y-m-d H:i:s");
echo implode(",", $data) . "\n";
}
return ob_get_clean();
} else {
if ($format == 'json') {
header('Content-type: application/json');
header('Content-disposition: attachment; filename=listings_data_' . $item->getDataId() . '_' . $type . '_' . date('Ymd-His') . '.json');
$json = array();
foreach ($listings as $listing) {
$json[$listing->getId()] = $listing->toArray(BasePeer::TYPE_FIELDNAME);
$date = new DateTime("{$listing->getListingDatetime()}");
$date->setTimezone(new DateTimeZone('UTC'));
$json[$listing->getId()]['listing_datetime'] = $date->format("Y-m-d H:i:s");
}
return json_encode($json);
}
}
})->assert('dataId', '\\d*')->assert('format', 'csv|json')->assert('type', 'sell|buy')->convert('dataId', $toInt);
/**
* ----------------------
* route /api/price
* ----------------------
*/
$controllers->get("/price/{format}/{secret}", function (Request $request, $format, $secret) use($app) {
$format = strtolower($format);
// check if the secret is in the configured allowed api_secrets
if (!in_array($secret, $app['gw2spidy']['api_secrets']) && !$app['debug']) {
return $app->redirect("/");
}
$q = ItemQuery::create();
if ($search = $request->get('search')) {
$q->filterByName($search);
} else {
if ($id = $request->get('id')) {
$q->filterByDataId($id);
} else {
return $app->redirect("/");
}
}
$item = $q->findOne();
if (!$item) {
return $app->abort(404, "Item does not exist.");
}
if ($format == 'csv') {
header('Content-type: text/csv');
header('Content-disposition: attachment; filename=item_price_' . $item->getDataId() . '_' . date('Ymd-His') . '.csv');
ob_start();
echo implode(",", array('min_sale_unit_price', 'max_offer_unit_price', 'sale_availability', 'offer_availability')) . "\n";
echo implode(",", array($item->getMinSaleUnitPrice(), $item->getMaxOfferUnitPrice(), $item->getSaleAvailability(), $item->getOfferAvailability())) . "\n";
return ob_get_clean();
} else {
if ($format == 'json') {
header('Content-type: application/json');
header('Content-disposition: attachment; filename=item_price' . $item->getDataId() . '_' . date('Ymd-His') . '.json');
$json = array('min_sale_unit_price' => $item->getMinSaleUnitPrice(), 'max_offer_unit_price' => $item->getMaxOfferUnitPrice(), 'sale_availability' => $item->getSaleAvailability(), 'offer_availability' => $item->getOfferAvailability());
return json_encode($json);
}
}
})->assert('format', 'csv|json')->assert('type', 'sell|buy');
return $controllers;
}
/**
* Get the associated Item object
*
* @param PropelPDO $con Optional Connection object.
* @return Item The associated Item object.
* @throws PropelException
*/
public function getItem(PropelPDO $con = null)
{
if ($this->aItem === null && $this->item_id !== null) {
$this->aItem = ItemQuery::create()->findPk($this->item_id, $con);
/* The following can be used additionally to
guarantee the related object contains a reference
to this object. This level of coupling may, however, be
undesirable since it could result in an only partially populated collection
in the referenced object.
$this->aItem->addOnWatchlists($this);
*/
}
return $this->aItem;
}
/**
* Gets the number of Item objects related by a many-to-many relationship
* to the current object by way of the recipe_ingredient cross-reference table.
*
* @param Criteria $criteria Optional query object to filter the query
* @param boolean $distinct Set to true to force count distinct
* @param PropelPDO $con Optional connection object
*
* @return int the number of related Item objects
*/
public function countItems($criteria = null, $distinct = false, PropelPDO $con = null)
{
if (null === $this->collItems || null !== $criteria) {
if ($this->isNew() && null === $this->collItems) {
return 0;
} else {
$query = ItemQuery::create(null, $criteria);
if ($distinct) {
$query->distinct();
}
return $query->filterByRecipe($this)->count($con);
}
} else {
return count($this->collItems);
}
}
<?php
ini_set('memory_limit', '1G');
use GW2Spidy\DB\ItemQuery;
require dirname(__FILE__) . '/../autoload.php';
$con = Propel::getConnection();
$stmt1 = $con->prepare("REPLACE INTO gw2spidy.sell_listing SELECT * FROM gw2spidy_old.sell_listing WHERE item_id = :item_id");
$stmt2 = $con->prepare("REPLACE INTO gw2spidy.buy_listing SELECT * FROM gw2spidy_old.buy_listing WHERE item_id = :item_id");
$items = ItemQuery::create()->find();
$cnt = count($items);
$i = 0;
foreach ($items as $item) {
echo "[{$item->getDataId()}] [{$i} / {$cnt}] ... \n";
$stmt1->bindValue('item_id', $item->getDataId());
$stmt1->execute();
$stmt2->bindValue('item_id', $item->getDataId());
$stmt2->execute();
$i++;
}
public function connect(Application $app)
{
$controllers = $app['controllers_factory'];
/**
* ----------------------
* route /types
* ----------------------
*/
$controllers->get("/{format}/types", function (Request $request, $format) use($app) {
$results = array();
foreach (ItemTypeQuery::getAllTypes() as $type) {
$result = array('id' => $type->getId(), 'name' => $type->getTitle(), 'subtypes' => array());
foreach ($type->getSubTypes() as $subtype) {
$result['subtypes'][] = array('id' => $subtype->getId(), 'name' => $subtype->getTitle());
}
$results[] = $result;
}
$response = array('results' => $results);
if ($format == 'csv') {
ob_start();
echo "id,name,parent_id\r\n";
foreach ($results as $result) {
echo "{$result['id']},{$result['name']},\r\n";
foreach ($result['subtypes'] as $subresult) {
echo "{$subresult['id']},{$subresult['name']},{$result['id']}\r\n";
}
}
return $app['api-helper']->outputResponse($request, ob_get_clean(), $format, "types", true);
} else {
return $app['api-helper']->outputResponse($request, $response, $format, "types");
}
})->assert('format', 'csv|json|xml');
/**
* ----------------------
* route /disciplines
* ----------------------
*/
$controllers->get("/{format}/disciplines", function (Request $request, $format) use($app) {
$results = array();
foreach (DisciplineQuery::getAllDisciplines() as $disc) {
$results[] = array('id' => $disc->getId(), 'name' => $disc->getName());
}
$response = array('results' => $results);
return $app['api-helper']->outputResponse($request, $response, $format, "disciplines");
})->assert('format', 'csv|json|xml');
/**
* ----------------------
* route /rarities
* ----------------------
*/
$controllers->get("/{format}/rarities", function (Request $request, $format) use($app) {
$results = array(array("id" => 0, "name" => "Junk"), array("id" => 1, "name" => "Common"), array("id" => 2, "name" => "Fine"), array("id" => 3, "name" => "Masterwork"), array("id" => 4, "name" => "Rare"), array("id" => 5, "name" => "Exotic"), array("id" => 6, "name" => "Ascended"), array("id" => 7, "name" => "Legendary"));
$response = array('results' => $results);
return $app['api-helper']->outputResponse($request, $response, $format, "rarities");
})->assert('format', 'csv|json|xml');
/**
* ----------------------
* route /all-items
* ----------------------
*/
$controllers->match("/{format}/all-items/{typeId}", function (Request $request, $format, $typeId) use($app) {
$q = ItemQuery::create()->select(ItemPeer::getFieldNames(\BasePeer::TYPE_PHPNAME));
$q->filterByUnsellableFlag(false);
if (in_array($typeId, array('all', '*all*'))) {
$typeId = null;
}
if (!is_null($typeId)) {
if (!($type = ItemTypeQuery::create()->findPk($typeId))) {
return $app->abort(404, "Invalid type [{$typeId}]");
}
$q->filterByItemType($type);
}
$count = $q->count();
$results = array();
foreach ($q->find() as $item) {
$results[] = $app['api-helper']->buildItemDataArray($item);
}
$response = array('count' => $count, 'results' => $results);
return $app['api-helper']->outputResponse($request, $response, $format, "all-items-{$typeId}");
})->assert('format', 'csv|json|xml')->assert('typeId', '\\d+|\\*?all\\*?');
/**
* ----------------------
* route /items
* ----------------------
*/
$controllers->match("/{format}/items/{typeId}/{page}", function (Request $request, $format, $typeId, $page) use($app) {
$itemsperpage = min($request->get('perpage', 100), 250);
$page = intval($page > 0 ? $page : 1);
$q = ItemQuery::create()->select(ItemPeer::getFieldNames(\BasePeer::TYPE_PHPNAME));
if (in_array($typeId, array('all', '*all*'))) {
$typeId = null;
}
if (!is_null($typeId)) {
if (!($type = ItemTypeQuery::create()->findPk($typeId))) {
return $app->abort(404, "Invalid type [{$typeId}]");
}
$q->filterByItemType($type);
}
if (($sortTrending = $request->get('sort_trending')) && in_array($sortTrending, array('sale', 'offer'))) {
$q->filterBySaleAvailability(200, \Criteria::GREATER_THAN);
$q->filterByOfferAvailability(200, \Criteria::GREATER_THAN);
if ($sortTrending == 'sale') {
$q->orderBySalePriceChangeLastHour(\Criteria::DESC);
} else {
if ($sortTrending == 'offer') {
$q->orderByOfferPriceChangeLastHour(\Criteria::DESC);
}
}
}
if ($rarity = $request->get('rarity')) {
$q->filterByRarity($rarity);
}
if ($filterIds = $request->get('filter_ids')) {
$filterIds = array_unique(array_filter(array_map('intval', explode(",", $filterIds))));
if (count($filterIds) > $itemsperpage) {
return $app->abort(400, "More IDs in filter_ids than allowed.");
}
$q->filterByDataId($filterIds, \Criteria::IN);
}
$total = $q->count();
if ($total > 0) {
$lastpage = ceil($total / $itemsperpage);
} else {
$page = 1;
$lastpage = 1;
}
$q->offset($itemsperpage * ($page - 1))->limit($itemsperpage);
$count = $q->count();
$results = array();
foreach ($q->find() as $item) {
$results[] = $app['api-helper']->buildItemDataArray($item);
}
$response = array('count' => $count, 'page' => $page, 'last_page' => $lastpage, 'total' => $total, 'results' => $results);
return $app['api-helper']->outputResponse($request, $response, $format, "items-{$typeId}-{$page}");
})->assert('format', 'csv|json|xml')->assert('typeId', '\\d+|\\*?all\\*?')->assert('page', '\\d*')->value('page', 1);
/**
* ----------------------
* route /item
* ----------------------
*/
$controllers->get("/{format}/item/{dataId}", function (Request $request, $format, $dataId) use($app) {
$q = ItemQuery::create();
// $q = ItemQuery::create()->select(ItemPeer::getFieldNames(\BasePeer::TYPE_PHPNAME));
$q->filterByPrimaryKey($dataId);
if (!($item = $q->findOne())) {
return $app->abort(404, "Item Not Found [{$dataId}].");
}
$response = array('result' => $app['api-helper']->buildItemDataArray($item));
return $app['api-helper']->outputResponse($request, $response, $format, "item-{$dataId}");
})->assert('format', 'csv|json|xml')->assert('dataId', '\\d+');
/**
* ----------------------
* route /listings
* ----------------------
*/
$controllers->get("/{format}/listings/{dataId}/{type}/{page}", function (Request $request, $format, $dataId, $type, $page) use($app) {
if ($small = $request->get('small')) {
$itemsperpage = 10;
} else {
$itemsperpage = 1000;
}
$page = intval($page > 0 ? $page : 1);
if ($type == 'sell') {
$q = SellListingQuery::create()->select(SellListingPeer::getFieldNames(\BasePeer::TYPE_PHPNAME));
} else {
$q = BuyListingQuery::create()->select(BuyListingPeer::getFieldNames(\BasePeer::TYPE_PHPNAME));
}
$q->filterByItemId($dataId);
$q->orderByListingDatetime(\ModelCriteria::DESC);
$total = $q->count();
if ($total > 0) {
$lastpage = ceil($total / $itemsperpage);
} else {
$page = 1;
$lastpage = 1;
}
$q->offset($itemsperpage * ($page - 1))->limit($itemsperpage);
$count = 0;
$results = array();
foreach ($q->find() as $listing) {
$results[] = $app['api-helper']->buildListingDataArray($listing);
$count++;
}
$response = array('sell-or-buy' => $type, 'count' => $count, 'page' => $page, 'last_page' => $lastpage, 'total' => $total, 'results' => $results);
return $app['api-helper']->outputResponse($request, $response, $format, "item-listings-{$dataId}-{$type}-{$page}");
})->assert('dataId', '\\d+')->assert('format', 'csv|json|xml')->assert('page', '\\d*')->assert('type', 'sell|buy')->value('page', 1);
/**
* ----------------------
* route /item-search
* ----------------------
*/
$controllers->get("/{format}/item-search/{name}/{page}", function (Request $request, $format, $name, $page) use($app) {
$itemsperpage = 50;
$page = intval($page > 0 ? $page : 1);
$q = ItemQuery::create()->select(ItemPeer::getFieldNames(\BasePeer::TYPE_PHPNAME));
$q->filterByName("%{$name}%");
if ($request->get('exclude_unsellable', false)) {
$q->filterByUnsellableFlag(false);
}
if ($q->count() == 0 && $name != trim($name)) {
$name = trim($name);
$q = ItemQuery::create()->select(ItemPeer::getFieldNames(\BasePeer::TYPE_PHPNAME));
$q->filterByName("%{$name}%");
if ($request->get('exclude_unsellable', false)) {
$q->filterByUnsellableFlag(false);
}
}
$total = $q->count();
if ($total > 0) {
$lastpage = ceil($total / $itemsperpage);
} else {
$page = 1;
$lastpage = 1;
}
$q->offset($itemsperpage * ($page - 1))->limit($itemsperpage)->addAscendingOrderByColumn('name');
$count = $q->count();
$results = array();
foreach ($q->find() as $item) {
$results[] = $app['api-helper']->buildItemDataArray($item);
}
$response = array('count' => $count, 'page' => $page, 'last_page' => $lastpage, 'total' => $total, 'results' => $results);
return $app['api-helper']->outputResponse($request, $response, $format, "item-search-{$page}");
})->assert('format', 'csv|json|xml')->assert('page', '\\d*')->value('page', 1);
/**
* ----------------------
* route /recipes
* ----------------------
*/
$controllers->get("/{format}/recipes/{discId}/{page}", function (Request $request, $format, $discId, $page) use($app) {
$itemsperpage = 100;
$page = intval($page > 0 ? $page : 1);
$q = RecipeQuery::create();
if (in_array($discId, array('all', '*all*'))) {
$discId = null;
}
if (!is_null($discId)) {
if (!($disc = DisciplineQuery::create()->findPk($discId))) {
return $app->abort(404, "Invalid discipline [{$discId}]");
}
$q->filterByDiscipline($disc);
}
$total = $q->count();
if ($total > 0) {
$lastpage = ceil($total / $itemsperpage);
} else {
$page = 1;
$lastpage = 1;
}
$q->offset($itemsperpage * ($page - 1))->limit($itemsperpage);
$count = $q->count();
$results = array();
foreach ($q->find() as $recipe) {
$results[] = $app['api-helper']->buildRecipeDataArray($recipe);
}
$response = array('count' => $count, 'page' => $page, 'last_page' => $lastpage, 'total' => $total, 'results' => $results);
return $app['api-helper']->outputResponse($request, $response, $format, "recipes-{$discId}-{$page}");
})->assert('format', 'csv|json|xml')->assert('discId', '\\d+|\\*?all\\*?')->assert('page', '\\d*')->value('page', 1);
/**
* ----------------------
* route /all-recipes
* ----------------------
*/
$controllers->match("/{format}/all-recipes/{discId}", function (Request $request, $format, $discId) use($app) {
$t = microtime(true);
$q = RecipeQuery::create();
if (in_array($discId, array('all', '*all*'))) {
$discId = null;
}
if (!is_null($discId)) {
if (!($disc = DisciplineQuery::create()->findPk($discId))) {
return $app->abort(404, "Invalid discipline [{$discId}]");
}
$q->filterByDiscipline($disc);
}
$count = $q->count();
$results = array();
foreach ($q->find() as $recipe) {
$results[] = $app['api-helper']->buildRecipeDataArray($recipe);
}
$response = array('count' => $count, 'results' => $results);
return $app['api-helper']->outputResponse($request, $response, $format, "all-recipes-{$discId}");
})->assert('format', 'csv|json|xml')->assert('typeId', '\\d+|\\*?all\\*?');
/**
* ----------------------
* route /recipe
* ----------------------
*/
$controllers->get("/{format}/recipe/{dataId}", function (Request $request, $format, $dataId) use($app) {
if (!($recipe = RecipeQuery::create()->findPk($dataId))) {
return $app->abort(404, "Recipe Not Found [{$dataId}].");
}
$response = array('result' => $app['api-helper']->buildRecipeDataArray($recipe));
return $app['api-helper']->outputResponse($request, $response, $format, "recipe-{$dataId}");
})->assert('format', 'csv|json|xml')->assert('dataId', '\\d+');
/**
* ----------------------
* route /gem-price
* ----------------------
*/
$controllers->get("/{format}/gem-price", function (Request $request, $format) use($app) {
$gemtogold = GemToGoldRateQuery::create()->addDescendingOrderByColumn("rate_datetime")->offset(-1)->limit(1)->findOne();
$goldtogem = GoldToGemRateQuery::create()->addDescendingOrderByColumn("rate_datetime")->offset(-1)->limit(1)->findOne();
if (!$gemtogold || !$goldtogem) {
return $app->abort(404, "Gem Data Not Found.");
}
$response = array('result' => array('gem_to_gold' => $gemtogold->getRate(), 'gold_to_gem' => $goldtogem->getRate()));
return $app['api-helper']->outputResponse($request, $response, $format, "gem-price");
})->assert('format', 'csv|json|xml');
/**
* ----------------------
* route /item-tooltip
* ----------------------
*/
$controllers->get("/{format}/item-tooltip/{dataId}", function (Request $request, $format, $dataId) use($app) {
$APIItem = APIItem::getItemById($dataId);
$response = array('result' => array('Tooltip' => $APIItem->getTooltip(), 'Type' => 'api/v0.9/json/item-tooltip', 'Id' => $dataId));
return $app['api-helper']->outputResponse($request, $response, $format, "item-tooltip-{$dataId}");
})->assert('format', 'csv|json|xml')->assert('dataId', '\\d+');
/**
* ----------------------
* route /recipe-tooltip
* ----------------------
*/
$controllers->get("/{format}/recipe-tooltip/{dataId}", function (Request $request, $format, $dataId) use($app) {
$APIRecipe = APIRecipe::getRecipeById($dataId);
$response = array('result' => array('Tooltip' => $APIRecipe->getTooltip(), 'Type' => 'api/v0.9/json/recipe-tooltip', 'Id' => $dataId));
return $app['api-helper']->outputResponse($request, $response, $format, "recipe-tooltip-{$dataId}");
})->assert('format', 'csv|json|xml')->assert('dataId', '\\d+');
return $controllers;
}
/**
* generic function used for /search and /type
*
* @param Application $app
* @param Request $request
* @param ItemQuery $q
* @param int $page
* @param int $itemsperpage
* @param array $tplVars
*/
function item_list(Application $app, Request $request, ItemQuery $q, $page, $itemsperpage, array $tplVars = array())
{
$sortByOptions = array('name', 'rarity', 'restriction_level', 'min_sale_unit_price', 'max_offer_unit_price', 'sale_availability', 'offer_availability', 'margin');
foreach ($sortByOptions as $sortByOption) {
if ($request->get("sort_{$sortByOption}", null)) {
$sortOrder = $request->get("sort_{$sortByOption}", 'asc');
$sortBy = $sortByOption;
}
}
$sortBy = isset($sortBy) && in_array($sortBy, $sortByOptions) ? $sortBy : 'name';
$sortOrder = isset($sortOrder) && in_array($sortOrder, array('asc', 'desc')) ? $sortOrder : 'asc';
if (($rarityFilter = $request->get('rarity_filter', null)) !== null && is_numeric($rarityFilter) && in_array($rarityFilter, array(0, 1, 2, 3, 4, 5, 6, 7))) {
$q->filterByRarity($rarityFilter);
}
if ($minLevelFilter = $request->get('min_level', null)) {
$q->filterByRestrictionLevel($minLevelFilter, \Criteria::GREATER_EQUAL);
}
if ($maxLevelFilter = $request->get('max_level', null)) {
$q->filterByRestrictionLevel($maxLevelFilter, \Criteria::LESS_EQUAL);
}
if (($unsellableFilter = $request->get('unsellable_flag', null)) === null) {
$q->filterByUnsellableFlag(false);
}
$count = $q->count();
if ($count > 0) {
$lastpage = ceil($count / $itemsperpage);
if ($page > $lastpage) {
$page = $lastpage;
}
} else {
$page = 1;
$lastpage = 1;
}
$q->addAsColumn("margin", "min_sale_unit_price * 0.85 - max_offer_unit_price");
$q->addSelectColumn("*");
$q->offset($itemsperpage * ($page - 1))->limit($itemsperpage);
if ($sortOrder == 'asc') {
$q->addAscendingOrderByColumn($sortBy);
} else {
if ($sortOrder == 'desc') {
$q->addDescendingOrderByColumn($sortBy);
}
}
$items = $q->find();
return $app['twig']->render('item_list.html.twig', $tplVars + array('page' => $page, 'lastpage' => $lastpage, 'items' => $items, 'rarity_filter' => $rarityFilter !== null ? $rarityFilter : -1, 'min_level' => $minLevelFilter, 'max_level' => $maxLevelFilter, 'current_sort' => $sortBy, 'current_sort_order' => $sortOrder, 'unsellable_flag' => $unsellableFilter));
}
