Script to generate charts for ChestShop transactions, logged by the ChestShopLogger Bukkit plugin.

shopstats.py

#!/usr/bin/env python3

"""
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.
"""

import mysql.connector as mysql
import matplotlib.pyplot as plt
import os
import time

DB_USER = "python"
DB_PASSWORD = "12345678"
DB_HOST = "localhost"
DB_SCHEMA = "chestshop"

# Minimum percentage for a item to be drawn or printed
MINPERC = 0.02
# Maximum amount of players printed or exported
MAX_PLAYERS = 20
# Time of the last processed transaction in seconds
TIMESPAN = 30 * 24 * 60 * 60 # 30 days

PLOT_PATH = "stats/"
PLOT_FORMAT = ".png"
PLOT_OFFSET = 0.25
PLOT_BAR_WIDTH = 0.5

# Colors from the LibreOffice default palette
PLOT_BAR_COLOR = "#004586"
PLOT_COLOR_PALETTE = [
    "#004586", "#ff420e", "#ffd320", "#579d1c", "#7e0021", "#83caff", 
    "#314004", "#aecf00", "#4b1f6f", "#ff950e", "#c5000b", "#0084d1"]

# List indices
AMOUNT = 0
PRICE = 1
BUY = 0
SELL = 1

def sorttotal(dictionary):
    return list(sorted(dictionary.items(), key=lambda x: x[1], reverse=True))

def sortitems(dictionary):
    return list(sorted(dictionary.items(), key=lambda x: x[1][1], reverse=True))

def plot_bar(values, labels, title):
    bars_pos = list(x + PLOT_OFFSET for x in range(len(values)))
    
    plt.figure()
    plt.bar(bars_pos, values, PLOT_BAR_WIDTH, color=PLOT_BAR_COLOR)
    plt.title(title)
    # Draw the bar labels
    plt.xticks(list(x + PLOT_BAR_WIDTH/2 for x in bars_pos), labels, 
        rotation="vertical")
    plt.tight_layout()
    
    plt.savefig(os.path.join(PLOT_PATH, title + PLOT_FORMAT))
    plt.close()

def plot_pie(perc, labels, title, path):    
    fig = plt.figure()
    # This just works
    subplot = fig.add_subplot(111)
    # Draw the title above the pie
    subplot.text(0.5, 1.09, title, horizontalalignment="center", 
        transform=subplot.transAxes, fontsize=15)
    subplot.pie(perc, labels=labels, colors=PLOT_COLOR_PALETTE, 
        autopct="%1.1f%%", startangle=90)
    # Set aspect ratio to be equal so that pie is drawn as a circle.
    subplot.axis("equal")
    plt.savefig(os.path.join(path, title + PLOT_FORMAT), bbox_inches="tight")
    plt.close()

endtime = int(time.time() * 1000)
starttime = endtime - TIMESPAN * 1000

playernames = {}
# playernames = {uuid: name}
shopitems = {}
# shopitems = {shopid: itemname}
playeritems = [{}, {}]
# playeritems = [{uuid: {item: [amount, price]}, {uuid: {item: [amount, price]}]
playertotal = [{}, {}]
# playertotal = [{uuid: total}, {uuid: total}]
topplayers = [[], []]
# topplayers = [[uuid, uuid, ...], [uuid, uuid, ...]]

conn = mysql.connect(user=DB_USER, password=DB_PASSWORD, 
    host=DB_HOST, database=DB_SCHEMA)
cur = conn.cursor()

cur.execute("SELECT uuid,name FROM chestshop_player")
for uuid, name in cur:
    playernames[uuid] = name

cur.execute("SELECT id,itemname FROM chestshop_shop")
for shopid, itemname in cur:
    shopitems[shopid] = itemname

cur.execute("SELECT id,shopid,clientuuid,type,amount,price,date FROM "
    "chestshop_transaction WHERE date > {} AND date < {}".format(
    starttime, endtime))

# Counts the amount of items bought/sold and their price
for transid, shopid, uuid, action, amount, price, date in cur:
    if action == "sell":
        action = SELL
    else:
        action = BUY
    
    if uuid not in playeritems[action]:
        playeritems[action][uuid] = {}
    if shopid not in playeritems[action][uuid]:
        playeritems[action][uuid][shopid] = [0, 0]

    playeritems[action][uuid][shopid][AMOUNT] += amount
    playeritems[action][uuid][shopid][PRICE] += price

# Calculates the total amount of money spent by a player
for action in (BUY, SELL):
    for uuid in playeritems[action]:
        for item in playeritems[action][uuid].values():
            if uuid not in playertotal[action]:
                playertotal[action][uuid] = 0
            playertotal[action][uuid] += item[PRICE]

os.makedirs(PLOT_PATH, exist_ok=True)

# Draws the charts for 
for action in (BUY, SELL):
    if action == SELL:
        print("Top Sellers:")
        plottitle = "Top Sellers"
    else:
        print("Top Buyers:")
        plottitle = "Top Buyers"

    plotplayers = []
    plotnumbers = []

    for i, (uuid, total) in enumerate(
            sorttotal(playertotal[action])[:MAX_PLAYERS]):
        topplayers[action].append(uuid)
        print("{:>2}. {:<20} - {:>9.2f}$".format(i+1, playernames[uuid], 
            round(total, 2)))
        
        plotplayers.append(playernames[uuid])
        plotnumbers.append(playertotal[action][uuid])

    plot_bar(plotnumbers, plotplayers, plottitle)

    print()

for action in (BUY, SELL):
    if action == SELL:
        print("\n\n")
        print("Most sold items per player:")
        plotpath = PLOT_PATH + "sell/"
    else:
        print("\n\n")
        print("Most bought items per player:")
        plotpath = PLOT_PATH + "buy/"

    os.makedirs(plotpath, exist_ok=True)

    for uuid in topplayers[action]:
        total = playertotal[action][uuid]
        items = playeritems[action][uuid]
        
        plotperc = []
        plotlabels = []
        remaining = 0
        
        print("\n{} - {:.2f}$".format(playernames[uuid], round(total, 2)))
        for shopid, stats in sortitems(items):
            perc = stats[PRICE] / total
            if perc < MINPERC:
                remaining += perc
                continue
            
            print("{:>4.0%} {:<20} - {:>9.2f}$".format(perc, shopitems[shopid],
                stats[PRICE]))
            plotperc.append(perc)
            plotlabels.append(shopitems[shopid])
        
        if round(remaining,3):
            plotperc.append(remaining)
            plotlabels.append("Others")
        
        plot_pie(plotperc, plotlabels, playernames[uuid], plotpath)