SWDB/application/API/v2/bin/update_logbook.py

#!/usr/bin/python3

import sqlite3
import json
import sys
import os


"""
Reads the API KEY, that must be passed as first command line argument.

:returns: Recovered API KEY.
:raises Exception: Th KEY couldn't be red.
"""
def readKey():
    try:
        #print(sys.argv[0])
        key = sys.argv[1]
        return key
    except Exception as e:
        print("Error parsing API KEY: " + str(e))
        raise

"""
Reads the JSON data, that must be passed as second command line argument.

:returns: Recovered data, in JSON format.
:raises Exception: The data couldn't be red or converted to JSON.
"""
def readData():
    try:
        data = json.loads(sys.argv[2])
        return data
    except Exception as e:
        print("Error parsing data: " + str(e))
        raise

"""
Verifies that the API key matches the player data and that it exists in th DB.

:param db: Connection to the database.
:returns: Connection to the database.
:param data: Data in json format.
:param key: API KEY.
:returns: True if key and player match, False otherwise.
:raises IntegrityError: The queryes couldn't bre executed.
"""
def verifyKey(db, data, key):
    print('Verifying KEY...')
    status = False
    try:
        uid = data["lobby_wizard_log"]["wizard_id"]
        cursor = db.cursor()
        cursor.execute('SELECT count(uid) AS c FROM player WHERE uid = ? AND api_key = ?;', (uid, key))
        if cursor.fetchone()[0] == 1:
            status = True
        cursor.close()
    except sqlite3.IntegrityError as e:
        print("Error executing statement: " + str(e))
        raise
    return status

"""
Opens the database file and deletes from the user tables

:param name: The path to the sqlite database.
:returns: Connection to the database.
:raises IntegrityError: The queryes couldn't bre executed.
:raises IOError: The sqlite file couldn't be created.
"""
def openDatabase():
    kdb = os.path.dirname(os.path.realpath(sys.argv[0])) + '/../../../sw.sqlite'
    udb = os.path.dirname(os.path.realpath(sys.argv[0])) + '/../../../../data/sw.sqlite'
    print('Configuring database...')
    try:
        db = sqlite3.connect(kdb)
        cursor = db.cursor()
        cursor.execute('attach "' + udb + '" as data;')
        cursor.close()
        return db
    except sqlite3.IntegrityError as e:
        print("Error executing statement: " + str(e))
        raise
    except IOError as e:
        print("I/O Error creating database " + name + ": " + str(e))
        raise

"""
Inserts a row into the database.

:param db: Connection to the database.
:param table: Name of the table to insert into.
:param values: List of values to insert.
:raises IntegrityError: The insert query was unsuccesfull.
"""
def insert(db, table, values):
    cursor = db.cursor()
    placeholders = ''
    for x in range(0, len(values)):
        placeholders = placeholders + '?, '
    placeholders = placeholders[:len(placeholders) - 2]
    query = 'INSERT INTO ' + table + ' VALUES (' + placeholders + ')'
    try:
        cursor.execute(query, values)
    except sqlite3.IntegrityError as e:
        print('Error Inserting into ' + table + ' with values (' + str(values) + '): ' + str(e))
        raise
    cursor.close;

"""
Parses run data (tables run, run_party, run_drop_rune, run_drop_runecraft,
run_drop_item, run_drop_unit, run_drop_sd, run_drop_unit_pieces,
run_drop_shapeshifting).

:param db: Sqlite database connection.
:param data: JSON data.
"""
def parseData(db, data):

    cursor = db.cursor()

    # Page 1: Cairos non-elemental, rift raid, rift dungeon.
    if (data["lobby_wizard_log"]["page_no"] == 1):
        print("Parsing logbook data...")
        
        uid = data["lobby_wizard_log"]["wizard_id"]
        joined = data["lobby_wizard_log"]["account_create_timestamp"]
        top_rank_arena = data["lobby_wizard_log"]["pvp_best_rating_id"]
        top_rank_world_arena = data["lobby_wizard_log"]["rtpvp_rank_best_rating_id"]
        top_rank_special_league = data["lobby_wizard_log"]["rtpvp_contest_best_rating_id"]
        top_rank_gw = data["lobby_wizard_log"]["guildwar_best_rating_id"]
        top_rank_siege = data["lobby_wizard_log"]["guildsiege_best_rating_id"]
        top_rank_wboss = data["lobby_wizard_log"]["world_boss_best_rank_id"]
        top_rank_toan = data["lobby_wizard_log"]["trial_tower_normal_best_floor"]
        top_rank_toah = data["lobby_wizard_log"]["trial_tower_hard_best_floor"]
        cursor.execute("""
            UPDATE player SET 
              joined = ?,
              top_rank_arena = ?,
              top_rank_world_arena = ?,
              top_rank_special_league = ?,
              top_rank_gw = ?,
              top_rank_siege = ?,
              top_rank_wboss = ?,
              top_rank_toan = ?,
              top_rank_toah = ?
            WHERE uid = ?;
            """,
            (
                joined,
                top_rank_arena,
                top_rank_world_arena,
                top_rank_special_league,
                top_rank_gw,
                top_rank_siege,
                top_rank_wboss,
                top_rank_toan,
                top_rank_toah,
                uid,
            )
        )

        # Loop Cairos records
        for record in data["lobby_wizard_log"]["dungeon_best_clear_info_list"]:
            area_type = 2 # Cairos Dungeons
            area = record["dungeon_id"]
            stage = record["stage_id"]
            time = record["clear_time"]
            score = 0 # No scores in Cairos
            rank = None # No rank in Cairos
            cursor.execute("DELETE FROM record_party WHERE uid = ? AND area_type = ? AND area = ?;", (uid, area_type, area))
            cursor.execute("DELETE FROM record WHERE uid = ? AND area_type = ? AND area = ?;", (uid, area_type, area))
            insert(db, "record", (uid, area_type, area, stage, time, score, rank))
            for party in record["my_unit_deck_list"]:
                unit_id = party["unit_id"]
                unit_master_id = party["unit_master_id"]
                leader = party["leader"]
                front = 0 # No frontline in Cairos
                insert(db, "record_party", (uid, area_type, area, unit_id, unit_master_id, leader, front))

        # Rift Raid record
        if data["lobby_wizard_log"]["raid_best_clear_info_list"][0]:
            area_type = 4
            stage = data["lobby_wizard_log"]["raid_best_clear_info_list"][0]["stage_id"]
            area = stage
            time = data["lobby_wizard_log"]["raid_best_clear_info_list"][0]["clear_time"]
            score = 0 # No scores in Rift Raid
            rank = None # No rank in Rift Raid
            cursor.execute("DELETE FROM record_party WHERE uid = ? AND area_type = ? AND area = ?;", (uid, area_type, area))
            cursor.execute("DELETE FROM record WHERE uid = ? AND area_type = ? AND area = ?;", (uid, area_type, area))
            insert(db, "record", (uid, area_type, area, stage, time, score, rank))
            for party in data["lobby_wizard_log"]["raid_best_clear_info_list"][0]["my_unit_deck_list"]:
                unit_id = party["unit_id"]
                unit_master_id = party["unit_master_id"]
                leader = party["leader"]
                if party["slot_index"] <= 4:
                    front = 1
                else:
                    front = 0
                insert(db, "record_party", (uid, area_type, area, unit_id, unit_master_id, leader, front))

        # Loop Rift Dungeon records
        for record in data["lobby_wizard_log"]["rift_dungeon_best_clear_info_list"]:
            area_type = 3 # Rift Elemental Dungeons
            area = record["rift_dungeon_id"]
            stage = 0 # No stage in Rift Dungeons
            time = 0 # No time in Rift Dungeons
            score = record["clear_damage"]
            raw_rank = raw_rank = record["clear_rating"]
            if raw_rank == 2:
                rank = "D"
            elif raw_rank == 3:
                rank = "C"
            elif raw_rank == 4:
                rank = "B-"
            elif raw_rank == 5:
                rank = "B"
            elif raw_rank == 6:
                rank = "B+"
            elif raw_rank == 7:
                rank = "A-"
            elif raw_rank == 8:
                rank = "A"
            elif raw_rank == 9:
                rank = "A+"
            elif raw_rank == 90:
                rank = "S"
            elif raw_rank == 11:
                rank = "SS"
            elif raw_rank == 12:
                rank = "SSS"
            else:
                rank = None
            cursor.execute("DELETE FROM record_party WHERE uid = ? AND area_type = ? AND area = ?;", (uid, area_type, area))
            cursor.execute("DELETE FROM record WHERE uid = ? AND area_type = ? AND area = ?;", (uid, area_type, area))
            insert(db, "record", (uid, area_type, area, stage, time, score, rank))
            for party in record["my_unit_deck_list"]:
                unit_id = party["unit_id"]
                unit_master_id = party["unit_master_id"]
                leader = party["leader"]
                if party["slot_index"] <= 4:
                    front = 1
                else:
                    front = 0
                insert(db, "record_party", (uid, area_type, area, unit_id, unit_master_id, leader, front))
        db.commit()

    # Page 2: Cairos non-elemental, rift raid, rift dungeon.
    elif (data["lobby_wizard_log"]["page_no"] == 2):
        uid = data["lobby_wizard_log"]["wizard_id"]
        # Loop Cairos records
        for record in data["lobby_wizard_log"]["dungeon_best_clear_info_list"]:
            area_type = 2 # Cairos Dungeons
            area = record["dungeon_id"]
            stage = record["stage_id"]
            time = record["clear_time"]
            score = 0 # No scores in Cairos
            rank = None # No rank in Cairos
            cursor.execute("DELETE FROM record_party WHERE uid = ? AND area_type = ? AND area = ?;", (uid, area_type, area))
            cursor.execute("DELETE FROM record WHERE uid = ? AND area_type = ? AND area = ?;", (uid, area_type, area))
            insert(db, "record", (uid, area_type, area, stage, time, score, rank))
            for party in record["my_unit_deck_list"]:
                unit_id = party["unit_id"]
                unit_master_id = party["unit_master_id"]
                leader = party["leader"]
                front = 0 # No frontline in Cairos
                insert(db, "record_party", (uid, area_type, area, unit_id, unit_master_id, leader, front))
        db.commit()

"""
Begin script
"""
data = readData()
key = readKey()
db = openDatabase()
if verifyKey(db, data, key) == False:
    print("Invalid API KEY...")
    sys.exit(-1)
else:
    parseData(db, data)
sys.exit(0)