189 lines
7.1 KiB
Python
189 lines
7.1 KiB
Python
import streamlit as st
|
|
from streamlit_option_menu import option_menu
|
|
from sqlalchemy import create_engine
|
|
import sites.services_reporting as sr
|
|
import sites.userlist as us
|
|
import sites.server as s
|
|
import sites.tickets as ti
|
|
import sites.lastrun as lr
|
|
from datetime import datetime
|
|
from dateutil.relativedelta import relativedelta
|
|
import mysql.connector
|
|
import pandas as pd
|
|
import os
|
|
|
|
# Page Settings
|
|
st.set_page_config(page_title="Reporting", layout="wide")
|
|
|
|
start_date = datetime.today().replace(day=1) - relativedelta(months=1)
|
|
end_date = datetime.today().replace(day=1) - relativedelta(days=1)
|
|
start_date_lastmonth = datetime.today().replace(day=1) - relativedelta(months=2)
|
|
end_date_lastmonth = datetime.today().replace(day=1) - relativedelta(months=1) - relativedelta(days=1)
|
|
|
|
# Datumsformatierung
|
|
start_date_format = start_date.strftime("%Y-%m-%d")
|
|
end_date_format = end_date.strftime("%Y-%m-%d")
|
|
|
|
# Load custom CSS
|
|
def load_css(file_name):
|
|
with open(file_name) as f:
|
|
st.markdown(f'<style>{f.read()}</style>', unsafe_allow_html=True)
|
|
|
|
def get_customer_used_service(end_date):
|
|
mydb = mysql.connector.connect(
|
|
host=os.getenv("MYSQL_HOST"),
|
|
user=os.getenv("MYSQL_USER"),
|
|
password=os.getenv("MYSQL_PASSWORD"),
|
|
database=os.getenv("MYSQL_DATABASE")
|
|
)
|
|
|
|
mycursor = mydb.cursor()
|
|
|
|
mycursor.execute(f"""SELECT cs.companyname, cs.customer_ID, cs.services_ID,cs.name from Kunden.`daily.customer.services` cs WHERE add_date LIKE '%{end_date}%'""")
|
|
|
|
myresult = mycursor.fetchall()
|
|
mydb.close()
|
|
return myresult
|
|
|
|
def load_server_list(start_date, end_date):
|
|
db_url = (
|
|
f"mysql+mysqlconnector://{os.getenv('MYSQL_USER')}:"
|
|
f"{os.getenv('MYSQL_PASSWORD')}@{os.getenv('MYSQL_HOST')}/"
|
|
f"{os.getenv('MYSQL_DATABASE')}"
|
|
)
|
|
engine = create_engine(db_url)
|
|
query = f"""
|
|
SELECT server, cores, customer_id
|
|
FROM Kunden.`daily.spla.server` sr
|
|
WHERE sr.timestamp BETWEEN '{start_date}' AND '{end_date}'
|
|
"""
|
|
max_server_count = pd.read_sql_query(query, engine)
|
|
return max_server_count
|
|
|
|
|
|
def load_user_service_list(service_id, customer_id, start_date, end_date):
|
|
db_url = (
|
|
f"mysql+mysqlconnector://{os.getenv('MYSQL_USER')}:"
|
|
f"{os.getenv('MYSQL_PASSWORD')}@{os.getenv('MYSQL_HOST')}/"
|
|
f"{os.getenv('MYSQL_DATABASE')}"
|
|
)
|
|
engine = create_engine(db_url)
|
|
|
|
query = f"""
|
|
SELECT COUNT(*) AS max_count
|
|
FROM (
|
|
SELECT username
|
|
FROM Kunden.`daily.user.enabled` sr
|
|
WHERE sr.customer_ID = {customer_id}
|
|
AND sr.services_ID = {service_id}
|
|
AND sr.timestamp BETWEEN '{start_date_format}' AND '{end_date_format}'
|
|
) AS sub;
|
|
"""
|
|
max_user_count = pd.read_sql_query(query, engine)
|
|
return max_user_count.iloc[0]['max_count'] if not max_user_count.empty else 0
|
|
|
|
def load_user_disabled(start_date, end_date,customer_id):
|
|
db_url = (
|
|
f"mysql+mysqlconnector://{os.getenv('MYSQL_USER')}:"
|
|
f"{os.getenv('MYSQL_PASSWORD')}@{os.getenv('MYSQL_HOST')}/"
|
|
f"{os.getenv('MYSQL_DATABASE')}"
|
|
)
|
|
engine = create_engine(db_url)
|
|
|
|
query = f"""
|
|
SELECT disabledate, username, service_name, customer_name, ticketnumber, comment
|
|
FROM Kunden.`daily.user.disabled` sud
|
|
WHERE sud.disabledate BETWEEN '{start_date}' AND '{end_date}' AND customer_id = '{customer_id}' AND services_id IN (100,101,116,120) ORDER BY service_name
|
|
"""
|
|
all = pd.read_sql_query(query, engine)
|
|
return all
|
|
|
|
def home():
|
|
st.title("Dashboard")
|
|
edit_start_date = start_date.strftime("%d.%m.%Y")
|
|
edit_end_date = end_date.strftime("%d.%m.%Y")
|
|
edit_start_date_lastmonth = start_date_lastmonth.strftime("%d.%m.%Y")
|
|
edit_end_date_lastmonth = end_date_lastmonth.strftime("%d.%m.%Y")
|
|
st.subheader(f"Übersicht {edit_start_date} - {edit_end_date}")
|
|
previous_value = None
|
|
columns = None
|
|
|
|
c = 0
|
|
print(end_date)
|
|
for i in get_customer_used_service(end_date.strftime("%Y-%m-%d")):
|
|
print(i)
|
|
if previous_value != i[1]:
|
|
st.subheader(f"{i[0]}")
|
|
columns = st.columns(4)
|
|
df = load_user_disabled(start_date_lastmonth, end_date_lastmonth, i[1])
|
|
|
|
if not df.empty:
|
|
st.text(f"Deaktivierte User {edit_start_date_lastmonth} - {edit_end_date_lastmonth}")
|
|
st.data_editor(df,use_container_width=True)
|
|
c = 0
|
|
|
|
active_users = load_user_service_list(i[2], i[1], start_date, end_date)
|
|
|
|
# Filter nach dem spezifischen Service und zähle die eindeutigen Benutzernamen
|
|
disabled_users_count = 0
|
|
if not df.empty and 'service_name' in df.columns and 'username' in df.columns:
|
|
# Annahme: i[3] enthält den Service-Namen, der mit der 'service_name'-Spalte übereinstimmt
|
|
service_filtered_df = df[df['service_name'] == i[3]]
|
|
disabled_users_count = service_filtered_df['username'].nunique()
|
|
|
|
if not active_users:
|
|
st.info(f"Kunde {i[0]} - Service {i[3]} - Not Data found!")
|
|
else:
|
|
columns[c].metric(
|
|
label=f"Aktive {i[3]} User",
|
|
value=active_users,
|
|
delta=f"-{disabled_users_count}" if disabled_users_count > 0 else None,
|
|
delta_color="inverse"
|
|
)
|
|
c += 1
|
|
previous_value = i[1]
|
|
|
|
col1, = st.columns(1)
|
|
df = load_server_list(start_date,end_date)
|
|
grouped = df.groupby('server')['cores'].count()*8/2
|
|
grouped_series = df.groupby('server')['cores'].max()
|
|
grouped_str = grouped_series.to_string(header=False)
|
|
df['cores'] = pd.to_numeric(df['cores'], errors='coerce')
|
|
server_cores = df.groupby('server')['cores'].max()
|
|
count_cores = server_cores.count()
|
|
with col1:
|
|
st.header("CPU Liste")
|
|
st.text(
|
|
f"Anzahl der Cores:\n{grouped_str}\n "
|
|
)
|
|
st.text(f"Gesamte Anzahl der Cores: {count_cores}")
|
|
st.text(f"Berechung der Core-Pakete = Anzahl der Cores ({count_cores}) * Core-Pakete aus SPLA (8) / 2")
|
|
st.header(f"Gesamt : {str(grouped.sum()).split('.')[0]} Pakete")
|
|
|
|
# Ausgabe der Ergebnisse
|
|
|
|
# Navigation bar using streamlit-option-menu
|
|
with st.sidebar:
|
|
selected_page = option_menu(
|
|
menu_title="Navigation", # required
|
|
options=["Dashboard", "Services Reporting", "User Filter", "Server", "Tickets", "Last-Run"], # required
|
|
icons=["house", "bar-chart", "filter", "server", "ticket", "clock"], # optional
|
|
menu_icon="cast", # optional
|
|
default_index=0, # optional
|
|
orientation="vertikal", # horizontal navigation
|
|
)
|
|
|
|
# Page display logic based on selected option
|
|
if selected_page == "Dashboard":
|
|
home()
|
|
elif selected_page == "Services Reporting":
|
|
sr.services_reporting()
|
|
elif selected_page == "User Filter":
|
|
us.user_filter()
|
|
elif selected_page == "Server":
|
|
s.server_filter()
|
|
elif selected_page == "Tickets":
|
|
ti.ticket_filter()
|
|
elif selected_page == "Last-Run":
|
|
lr.user_filter()
|