Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- # Get the all the items numbers
- $url1 = "http://api.guildwars2.com/v2/commerce/listings";
- $response1 = file_get_contents($url1);
- $data1 = json_decode($response1, true);
- $amount_added_to_db = 0;
- # Process in chunks to avoid out of memory error
- $chunksize = round(sizeof($data1)/10); #round(sizeof($data1)/10); # divided by 10 equals around 2215
- $offset = 9*$chunksize;
- $length = $chunksize;
- $splitsize = 25; # maximum 200
- $smallchunk = array_slice($data1,$offset, $length);
- $splitted_smallchunk = array_chunk($smallchunk, $splitsize);
- #Retrieve item names and link with numbers
- function request_callback($response,$info) {
- global $associative_array;
- if($info['http_code'] === 200){
- $temporary_array = json_decode($response, true);
- foreach ($temporary_array as $value){
- $associative_array[] = array('name' => $value['name'],'id' => $value['id']);
- }
- }
- }
- # Queue up multiple curl request for handling
- require("rollingcurl.php"); // from https://github.com/LionsAd/rolling-curl
- foreach ($splitted_smallchunk as $urlstring){
- $urlstring = implode (',',$urlstring);
- $urls[] = "https://api.guildwars2.com/v2/items?ids={$urlstring}";
- }
- $rc = new RollingCurl("request_callback");
- $rc->window_size = 20;
- foreach ($urls as $url) {
- $request = new RollingCurlRequest ( $url ) ;
- $rc -> add ( $request ) ;
- }
- $rc->execute();
- try {
- # Make a connection to the database
- $db = new PDO('sqlite:/var/www/gw2db/gw2.sqlite');
- $db ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- # Create the database (if already made, IF NOT EXISTS will prevent error)
- $db->exec("CREATE TABLE IF NOT EXISTS alltheitems (Id INTEGER PRIMARY KEY, itemname TEXT, itemnumber INTEGER)");
- # Prepare the query ONCE
- $stmt = $db->prepare('INSERT INTO alltheitems (itemname, itemnumber) VALUES(:name, :number)');
- $stmt->bindParam(':name', $itemname, SQLITE3_TEXT);
- $stmt->bindParam(':number', $itemnumber, SQLITE3_INTEGER);
- # Insert values and execute statements
- for ($x=0;$x<sizeof($associative_array);$x++){
- $itemname = $associative_array[$x]['name'];
- $itemnumber = $associative_array[$x]['id'];
- # Check if the item is already in the database or invalid URL return
- $duplicatecheck = $db->query("SELECT * FROM alltheitems WHERE itemnumber LIKE '$itemnumber'");
- $alreadyinDB=0;
- foreach($duplicatecheck as $row)
- {
- $alreadyinDB=1;
- }
- if ($alreadyinDB==0) {
- $stmt->execute();
- $amount_added_to_db = $amount_added_to_db + 1;
- }
- }
- $fetchdb = $db->query("SELECT itemnumber, itemname FROM alltheitems");
- # Make array from fetchdb object
- $data_array = $fetchdb->fetchAll();
- if ($amount_added_to_db > 0){
- $data_array_latest_added = array_slice($data_array,-$amount_added_to_db);
- function request_callback2($response,$info) {
- global $joint_array;
- $db2 = new PDO('sqlite:/var/www/gw2db/gw2.sqlite');
- $temporary_array = json_decode($response, true);
- if (($info['http_code'] === 200) && (is_array($temporary_array))){
- foreach ($temporary_array as $value){
- $idnumber = $value['id'];
- $searchname = $db2 ->query("SELECT itemname FROM alltheitems WHERE itemnumber LIKE '$idnumber'");
- $searcharray = $searchname->fetchAll();
- $itemname = $searcharray[0]['itemname'];
- $joint_array[]= array('name'=>$itemname, 'id'=>$value['id'],'buys'=>$value['buys'],'sells'=>$value['sells']);
- #echo $itemname; echo " with id# "; echo $idnumber; echo " has been added to joint_array "; echo "\n";
- }
- }
- $db2 = NULL;
- }
- $splitted_dala = array_chunk($data_array_latest_added, $splitsize);
- foreach ($splitted_dala as $urlnumbers){
- $temp = $urlnumbers;
- $ids = array();
- foreach ($temp as $value){
- $ids[] = $value['itemnumber'];
- $idstring = implode (',',$ids);
- }
- $TPurls[]="https://api.guildwars2.com/v2/commerce/listings?ids={$idstring}";
- unset($ids);
- }
- $rc = new RollingCurl("request_callback2");
- $rc->window_size = 20;
- foreach ($TPurls as $url) {
- $request = new RollingCurlRequest ( $url ) ;
- $rc -> add ( $request ) ;
- }
- $rc->execute();
- #echo '<pre>'; print_r($joint_array); echo '</pre>';
- for ($y=0; $y<sizeof($joint_array);$y++) {
- $totalbuys = 0;
- $totalsells = 0;
- $totalbuyprice = 0;
- $totalsellprice = 0;
- for ($x=0;$x<sizeof($joint_array[$y]['buys']);$x++) {
- $totalbuys = $joint_array[$y]['buys'][$x]['listings'] * $joint_array[$y]['buys'][$x]['quantity'] + $totalbuys;
- $totalbuyprice = $joint_array[$y]['buys'][$x]['listings'] * $joint_array[$y]['buys'][$x]['quantity'] * $joint_array[$y]['buys'][$x]['unit_price'];
- $countB = $x;
- if ($totalbuys >= 1000){
- break;
- }
- }
- for ($x=0;$x<sizeof($joint_array[$y]['sells']);$x++) {
- $totalsells = $joint_array[$y]['sells'][$x]['listings'] * $joint_array[$y]['sells'][$x]['quantity'] + $totalsells;
- $totalsellprice = $joint_array[$y]['sells'][$x]['listings'] * $joint_array[$y]['sells'][$x]['quantity'] * $joint_array[$y]['sells'][$x]['unit_price'];
- $countS = $x;
- if ($totalsells >= 1000){
- break;
- }
- }
- if (($totalsells >= 1000) && ($totalbuys >= 1000)){
- $totalbuyprice = $totalbuyprice - ($totalbuys - 1000)* $joint_array[$y]['buys'][$countB]['unit_price'];
- $totalsellprice = $totalsellprice - ($totalsells - 1000)* $joint_array[$y]['sells'][$countS]['unit_price'];
- $ratio = $totalbuyprice / ($totalsellprice + $totalbuyprice);
- $ratio_small = number_format((float)$ratio, 2, '.', '');
- } else {
- $ratio_small = 0;
- }
- $db->exec("CREATE TABLE IF NOT EXISTS rating (Id INTEGER PRIMARY KEY, itemnumber INTEGER, itemname TEXT, itemratio REAL)");
- # Prepare the query ONCE
- $stmt = $db->prepare('INSERT INTO rating (itemnumber, itemname, itemratio) VALUES(:number, :name, :ratio)');
- $stmt->bindParam(':number', $itemnumber, SQLITE3_INTEGER);
- $stmt->bindParam(':name', $itemname, SQLITE3_TEXT);
- $stmt->bindParam(':ratio', $itemratio, SQLITE3_FLOAT);
- # Insert values and execute statements
- $idnumber = $joint_array[$y]['id'];
- #echo $joint_array[$y][id];'<br>';
- $alreadyinDBcheck = $db->query("SELECT * FROM rating WHERE itemnumber LIKE '$idnumber'");
- $found=0;
- foreach($alreadyinDBcheck as $row)
- {
- $found=1;
- }
- if ($found==0) {
- $itemratio = $ratio_small;
- $itemnumber = $joint_array[$y]['id'];
- $itemname = $joint_array[$y]['name'];
- $stmt->execute();
- } else {
- $db->exec("UPDATE rating SET itemratio='$ratio_small' WHERE itemnumber LIKE '$joint_array[$y][id]'");
- #echo ' already in DB <br>';
- }
- }
- }
- # add creation timestamp
- date_default_timezone_set("Europe/Amsterdam");
- $time = "This page has been updated at " . date("d-m-Y h:i:sa"). "<br><br>";
- $db->exec("CREATE TABLE IF NOT EXISTS creationtime (Id INTEGER PRIMARY KEY, timestring TEXT)");
- $stmt = $db->prepare('INSERT INTO creationtime (timestring) VALUES(:time)');
- $stmt->bindParam(':time', $timestring, SQLITE3_TEXT);
- $timestring = $time;
- $stmt->execute();
- # Clear the table
- #$db-> exec ('DELETE FROM rating');
- #$db-> exec ('DELETE FROM alltheitems');
- # close the database connection
- $db = NULL;
- }
- catch(PDOException $e) {
- echo 'ERROR: ' . $e->getMessage();
- }
- ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement