# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Setup

import requests
import pandas as pd
from bs4 import BeautifulSoup
import datetime
from datetime import datetime, timedelta, date

# # Part 1 : Main Data Analysis from GW2 official API for ROI

# #### Récupération du CSV contenant tous les id/name des objets de GW2.

offapi = pd.read_csv('items.csv')
# print(offapi.to_string())
# print(offapi)
offapi = offapi.astype({
        'name': 'str', 
})
offapi.head(5)

# #### Création d'une boucle parcourant tous les id des objets dans l'API officiel de GW2.

# +
df = pd.DataFrame()
cpt = 0
for idx in offapi['id']:
    
    r = requests.get("https://api.guildwars2.com/v2/commerce/prices/" + str(idx))
    j = r.json()
    
    now = datetime.now()

    if "text" in j:
        continue
        
    id = j['id']
    bought_quantity = j['buys']['quantity']
    bought_unit_price = j['buys']['unit_price']
    sold_unit_price = j['sells']['unit_price']
    sold_quantity = j['sells']['quantity']
    
    
    profit = sold_unit_price *0.85 - bought_unit_price
    
#     if bought_unit_price == 0:
#         profit_percentage = 0
#     else:
#         profit_percentage = profit/bought_unit_price *100
    
    
    dict = {
        'id': id,
        'bought_quantity': bought_quantity,
        'bought_unit_price': bought_unit_price,
        'sold_unit_price': sold_unit_price,
        'sold_quantity': sold_quantity,
        'profit' : profit,
#         'profit_percentage' : profit_percentage,
        'date' : now
    }
    df = df.append(dict, ignore_index=True)
    
    df = df.astype({
            'id': 'int64', 
            'bought_quantity': 'int64', 
            'bought_unit_price': 'int64',
            'sold_unit_price': 'int64',
            'sold_quantity': 'int64',
            'date' : 'str',
    })
    cpt += 1
print(cpt)
display(df)
# -

# #### Restructuration du dataframe et innerjoin sur l'id avec le df csv pour récupérer le nom de l'objet.

# +
df = df.merge(offapi[['id','name']], on='id')

df = df.astype({
        'name': 'str',
})

colorder = ['id', 'name', 'bought_quantity', 'bought_unit_price', 'sold_quantity', 'sold_unit_price', 'profit', 'date']
df = df.reindex(columns=colorder)

display(df)
# -

# #### Sauvegarde du dataframe dans un CSV (au cas où)

df.to_csv(r'C:\Users\tadav\OneDrive\Bureau\Data&GO\PFF\items2.csv' ,index=False)

df = pd.read_csv(r'items2.csv')

# #### Push du dataframe sur postgreSQL pour visualisation sur Tableau

from datetime import datetime
from sqlalchemy import Column, Date, Integer, String, TIMESTAMP
from sqlalchemy.orm import sessionmaker
import psycopg2
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

# +
# gw2 ici est = nom de la database, part 1 = nom de la table
engine = sqlalchemy.create_engine('postgresql+psycopg2://postgres:azerty09@localhost:5432/gw2')
Base = declarative_base()
# create a configured "Session" class
Session = sessionmaker(bind=engine)

# create a Session
session = Session()
# -

# Write data into the table in PostgreSQL database
df.to_sql('part1',engine, if_exists='replace')

# # Part 2 : Trend Analysis

# #### Using non offical GW2 API from gw2spidy.com to get prices and quantities history

# +
from time import time, sleep

while True:

    df2 = pd.DataFrame()
    combined_results = {}

    r = requests.get("http://www.gw2spidy.com/api/v0.9/json/listings/19976/sell/")
    j = r.json()

    number_of_pages = j['last_page']
    links_name = ['sell', 'buy']
    for link_name in links_name:
        for page in range (1, number_of_pages + 1):

            r = requests.get("http://www.gw2spidy.com/api/v0.9/json/listings/19976/" + link_name + "/"+ str(page))
            j = r.json()

            sell_or_buy = j['sell-or-buy']

            #j['results'] = results:[{...}, {...}]
            #element == {...}
            for element in j['results']:

                date = element['listing_datetime']
                selling_price = element['unit_price']
                selling_quantity = element['quantity']

                datetime_str = date
                datetime_object = datetime.strptime(datetime_str, '%Y-%m-%d %H:%M:%S %Z')

                dict = {
                'sell-or-buy': sell_or_buy,
                'listing_datetime' : datetime_object,
                'unit_price' : selling_price,
                'quantity' : selling_quantity

                }

                df2 = df2.append(dict, ignore_index=True)


    print(df2)
    
    engine = sqlalchemy.create_engine('postgresql+psycopg2://postgres:azerty09@localhost:5432/gw2')
    Base = declarative_base()
    # create a configured "Session" class
    Session = sessionmaker(bind=engine)

    # create a Session
    session = Session()

    # Write data into the table in PostgreSQL database
    df2.to_sql('part2',engine, if_exists='replace')
    
    sleep(60 - time() % 60)
# -

# # Part 3 : Model Price Prediction

# Importing modules
import numpy as np 
import pandas as pd
import matplotlib.pyplot as plt
import datetime

df2.head(5)

gw2model = df2[df2['listing_datetime'].notnull() & (df2['sell-or-buy'] == "sell") & (df2['unit_price']).notnull()]
print(gw2model)
print(gw2model.columns)

# +
gw2model = gw2model[['listing_datetime', 'unit_price']]

print(gw2model.to_string())
print(gw2model.columns)
# -

# Setting the index 
plot_gw2 = gw2model.set_index('listing_datetime')

plot_gw2['unit_price'].plot(figsize=(16,6))

gw2model = gw2model.groupby(pd.Grouper(key="listing_datetime", freq="D")).mean().reset_index()
print(gw2model.to_string())

new_gw2 = gw2model.loc["2016-06-01":]
print(new_gw2)



StarcoderdataJupyterScriptsDedupFiltered