load_data.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on: 16/01/2023
@author: Andoni Aranguren Ubierna
@updates: Sergio Campos 02-03/2023
"""
import os

import sys
import numpy as np
import pandas as pd
import requests
from mysql import connector
from utm.conversion import to_latlon as utm_to_latlon

import sys
print(sys.path)
from src import constants


def to_latlon(df, x_str, y_str):
    x, y = [], []

    for index, row in df.iterrows():
        coords_portal = utm_to_latlon(row[x_str], row[y_str], zone_number=30, zone_letter="T")
        x += [coords_portal[0]]
        y += [coords_portal[1]]
    df["lon"] = np.round(x, 8)
    df["lat"] = np.round(y, 8)

    return df


def generate_actions(cnx):
    cursor = cnx.cursor(buffered=True)
    path = constants.PATH_actionS

    file = path + "/actionS_LugaresTuristicos_Bilbao.csv"
    if os.path.isfile(file):
        df = pd.read_csv(file, encoding="utf-8", sep=";", decimal=".", encoding_errors='replace')
        df = to_latlon(df, "COORDENADA_UTM_X", "COORDENADA_UTM_Y")
        df.drop_duplicates(subset=["lon", "lat", "NOMBRE_LUGAR_CAS"], inplace=True)
        valores_preferencias = set([i for x in df["kpiS"].unique().tolist() for i in x.split(",")])

        df_sql = df[["ID", "lon", "lat", "NOMBRE_LUGAR_CAS"]]
        cursor.executemany(constants.sql_insert_action, df_sql.values.tolist())
        cnx.commit()
        for kpi in valores_preferencias:
            cursor.execute(constants.sql_insert_kpi, [kpi])
        cnx.commit()

        names_dict = {}
        for kpi in valores_preferencias:
            names_dict[kpi] = ""

        cursor.execute(constants.sql_get_kpi_ids.format(names="','".join(names_dict.keys())), )
        cnx.commit()
        ret = cursor.fetchall()

        for row in ret:
            names_dict[row[0]] = row[1]

        for index, row in df.iterrows():
            for kpi in row["kpiS"].split(","):
                cursor.execute(constants.sql_insert_action_kpi.format(kpi=names_dict[kpi]),
                               row[["lon", "lat", "NOMBRE_LUGAR_CAS"]].values.tolist())
        cnx.commit()


def generate_plan_details(cnx):
    cursor = cnx.cursor(buffered=True)
    a, b = 'áéíóúüÁÉÍÓÚÜ', '%%%%%%%%%%%%'
    trans = str.maketrans(a, b)
    path = constants.PATH_plan_detailS
    for filename in os.listdir(path):
        f = os.path.join(path, filename)
        if os.path.isfile(f):
            df = pd.read_csv(f, sep="\t")
            json_plan = None
            for index, row in df.iterrows():
                names = row["action_names"].replace(";", "','")
                dict_id = {}
                for name in row["action_names"].split(";"):
                    cursor.execute(constants.sql_get_chosen_action_ids,[name.translate(trans)])
                    cnx.commit()
                    ret = cursor.fetchall()
                    try:
                        dict_id[ret[0][0]] = str(ret[0][1])
                    except:
                        pass

                action_id = ",".join(list(dict_id.values()))

                url_plan_detail = 'http://localhost:5000/planner/plan_detail?'
                parameters = "&".join(["fromPlace=" + row["from_place"],
                                       "toPlace=" + row["to_place"],
                                       "timeSlot=" + str(int(row["time_slot"])),
                                       "sports=" + str(row["sports"]),
                                       "culture=" + str(row["culture"]),
                                       "sightseeing=" + str(row["sightseeing"]),
                                       "gastronomy=" + str(row["gastronomy"])])
                if action_id != '':
                    parameters += "&intermediateNodesIds=" + action_id

                if json_plan is None:
                    json_plan = requests.get(url_plan_detail+parameters).json()
                else:
                    time_slot = str(int(row["time_slot"]))
                    json_plan["plan_details"][time_slot] = requests.get(url_plan_detail+parameters).json()["plan_details"][time_slot]
            url_save_it = 'http://localhost:5000/database/save/plan'
            requests.post(url_save_it, json=json_plan)


def main(ddbb_config: dict):
    cnx = connector.connect(**ddbb_config)
    generate_actions(cnx)
    cnx.close()


if __name__ == '__main__':
    ddbb_config = constants.DDBB_CONFIG
    main(ddbb_config)