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

#!/usr/bin/python3

import sqlite3
import json
import sys
import os

import MAPPING

"""
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:
        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.

:param: index 2 for start data, 3 for result data
:returns: Recovered data, in JSON format.
:raises Exception: The data couldn't be red or converted to JSON.
"""
def readData(index):
    try:
        data = json.loads(sys.argv[index])
        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["wizard_info"]["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_start: JSON data of the run start.
:param data_result: JSON data of the run result.
"""
def parseRun(db, data_request, data_response):
    print("Parsing run...")
    cursor = db.cursor()

    # Read basic data
    uid = data_request["wizard_id"]
    dtime = data_response["tvaluelocal"]

    # Check if run has already been inserted.
    cursor.execute("SELECT count(id) AS c FROM run WHERE uid = ? AND dtime = ?;", (uid, dtime))
    if (cursor.fetchone()[0] > 0):
        print("    Run already in database. Stopping....")
        return 409;

    # We are inserting, get aditional info
    area_type = 9 # Tartarus Labyrinth
    area = 9 # Tartarus Labyrinth
    stage = 0 # Battle type
    difficulty = data_request["difficulty"]
    win = data_response["win_lose"]
    helper = 0

    tile_id = data_request["tile_id"]
    # We now have the tile id from the request.
    # Now we must loop all tiles in the response to get the info
    for tile in data_response["guildmaze_tiles"]:
        # TODO: Get correct types
        if tile["tile_id"] == tile_id:
            if tile["battle_type"] == 0: # Tartarus
                stage = 1
            elif tile["battle_type"] == 302: # Kottos (Fire guardian)
                stage = 2
            elif tile["battle_type"] == 0: # Leos (Water guardian)
                stage = 3
            elif tile["battle_type"] == 0: # Guilles (Wind guardian)
                stage = 4
            elif tile["battle_type"] == 0: # Normal stage
                stage = 5
            elif tile["battle_type"] == 0: # Rescue stage
                stage = 6
            elif tile["battle_type"] == 0: # Explode stage
                stage = 7
            elif tile["battle_type"] == 0: # Cooltime stage
                stage = 8
            elif tile["battle_type"] == 201: # Speed limit stage
                stage = 9
            elif tile["battle_type"] == 202: # Time limit stage
                stage = 10
            break
    score = 0 # TODO
    rank = ''
    time = data_request["clear_time"]
    if ("mana" in data_response["reward"]):
        mana = data_response["reward"]["mana"]
    else:
        mana = 0
    if ("energy" in data_response["reward"]):
        energy = data_response["reward"]["energy"]
    else:
        energy = 0
    if ("crystal" in data_response["reward"]):
        crystal = data_response["reward"]["crystal"]
    else:
        crystal = 0
    if ("guild-point" in data_response["reward"]):
        guild_points = data_response["reward"]["guild-point"]
    else:
        guild_points = 0

    # Get the new ID and insert
    cursor.execute("SELECT max(id) + 1 AS id FROM run;")
    id = cursor.fetchone()[0]
    if id == None:
        id = 1

    insert(db, "run", (uid, id, dtime, area_type, area, stage, difficulty, win, time, mana, energy, crystal, guild_points, helper, score, rank))

    # Parse various types of reward
    crate = data_response["reward"]["crate"]

    # Parse rune reward
    if "rune" in crate:
        rune = crate["rune"]
        rune_id = rune["rune_id"]
        rune_type = rune["set_id"]
        slot = rune["slot_no"]
        stars = rune["class"]
        ancient = 0
        quality = rune["rank"]
        value = rune["sell_value"]
        efficiency, max_efficiency = MAPPING.calculate_efficiency(rune)
        main_stat = rune["pri_eff"][0]
        main_stat_value = rune["pri_eff"][1]
        if "prefix_eff" in rune:
            innate_stat = rune["prefix_eff"][0]
            innate_stat_value = rune["prefix_eff"][1]
        else:
            innate_stat = 0
            innate_stat_value = 0
        substat_1 = 0
        substat_1_value = 0
        substat_2 = 0
        substat_2_value = 0
        substat_3 = 0
        substat_3_value = 0
        substat_4 = 0
        substat_4_value = 0
        if "0" in rune["sec_eff"]:
            substat_1 = rune["sec_eff"]["0"]["0"]
            substat_1_value = rune["sec_eff"]["0"]["1"]
        if "1" in rune["sec_eff"]:
            substat_1 = rune["sec_eff"]["1"]["0"]
            substat_1_value = rune["sec_eff"]["1"]["1"]
        if "2" in rune["sec_eff"]:
            substat_1 = rune["sec_eff"]["2"]["0"]
            substat_1_value = rune["sec_eff"]["2"]["1"]
        if "3" in rune["sec_eff"]:
            substat_1 = rune["sec_eff"]["3"]["0"]
            substat_1_value = rune["sec_eff"]["3"]["1"]
        insert(db, "run_drop_rune", (id, rune_id, rune_type, slot, stars, ancient, quality, value, efficiency, max_efficiency, main_stat, main_stat_value, innate_stat, innate_stat_value, substat_1, substat_1_value, substat_2, substat_2_value, substat_3, substat_3_value, substat_4, substat_4_value))


    # TODO: Parse grindstones/gems

    # Parse units
    first = True
    for unit in data_response["unit_list"]:
        unit_id = unit["unit_id"]
        unit_master_id = unit["unit_master_id"]
        if (first):
            leader = 1
            first = False
        else:
            leader = 0
        front = 0
        insert(db, "run_party", (id, unit_id, unit_master_id, leader, front))
    db.commit()


"""
Begin script
"""
data_request = readData(2)
data_response = readData(3)
key = readKey()
db = openDatabase()
if verifyKey(db, data_response, key) == False:
    print("Invalid API KEY...")
    sys.exit(401)
else:
    parseRun(db, data_request, data_response)
sys.exit(201)