BeCloud Internal Wiki

import glob
import math
import os
import pandas as pd
import zipfile
import warnings

folder_path = '.'

# scorro tutti i file nella cartella
for filename in os.listdir(folder_path):
    # prendo solo i file .zip
    if filename.endswith('.zip'):
        zip_path = os.path.join(folder_path, filename)
        extract_path = folder_path 
        # estraggo tutti i .zip nella folder in modo da avere n file .csv
        with zipfile.ZipFile(zip_path, 'r') as zip_ref:
            zip_ref.extractall(extract_path)


# scorro tutti i file nella cartella
for filename in os.listdir(folder_path):
    #prendo solo i file .xlsx e tengo solo i billing
    if filename.endswith('.xlsx') and 'billing' not in filename:
        file_path = os.path.join(folder_path, filename)
        os.remove(file_path)

# ignoro warning di openpyxl
warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl")

df_list = []

# scorro tutti i file .xlsx nella cartella
for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx'):
        file_path = os.path.join(folder_path, filename)
        try:
            df = pd.read_excel(file_path, engine='openpyxl')
            df_list.append(df)
        except Exception as e:
            print(f"Errore nel file {filename}: {e}")

# combino tutti i dataframe in uno solo
df = pd.concat(df_list, ignore_index=True)
df['From'] = df['From'].astype(str)

#creo il dataframe
df = df[['From', 'Purchase Price','Traffic Type']]
df['From'] = df['From'].astype(str)
df['Purchase Price'] = df['Purchase Price'] / 100

#associo ogni apikey al cliente
api_keys = {
    "390287238444": {
        "nome": "Alidays"
    },
    "390287238484": {
        "nome": "Alidays"
    },
    "390287238400": {
        "nome": "Alidays"
    },
    "390521832000": {
        "nome": "Arquati"
    },
    "393346275858": {
        "nome": "Estendo"
    },
    "390620190197": {
        "nome": "Farmacie"
    },
    "393316787339": {
        "nome": "F.IMM"
    },
    "3902300821": {
        "nome": "Grenke"
    }
}

#creo i dataframes per ogni apikey
numbers = df["From"].unique()
for number in numbers:
    df['From'] = df['From'].replace({number: api_keys[number]["nome"]})
dfs = [group for _, group in df.groupby('From')]

#faccio i conti e stampo
for d in dfs:
    print("\033[1m",d["From"].iloc[0],"\033[0m")
    print("\tWA: ", len(d[d["Traffic Type"] == "Service"]))
    print("\tTemplates Utility: ", len(d[d["Traffic Type"] == "Utility"]))
    print("\tTemplates Marketing: ", len(d[d["Traffic Type"] == "Marketing"]))
    print("\tCosto a noi WA: ", round(d[d["Traffic Type"] == "Service"]["Purchase Price"].sum(),2), "€")
    print("\tCosto a noi Utility: ", round(d[d["Traffic Type"] == "Utility"]["Purchase Price"].sum(),2), "€")
    print("\tCosto a noi Marketing: ", round(d[d["Traffic Type"] == "Marketing"]["Purchase Price"].sum(),2), "€")
    print("\tFatturare: ", round(len(d[d["Traffic Type"] == "Service"]) * 0.0619 + len(d[d["Traffic Type"] == "Utility"]) * 0.0647 + len(d[d["Traffic Type"] == "Marketing"]) * 0.0872 ,2), "€")