import csv import os import mysql.connector import pandas as pd from datetime import datetime, timedelta mydb = mysql.connector.connect( host="172.17.1.21", port="3306", user="root", password="N53yBCswuawzBzS445VNAhWVMs3N59Gb9szEsrzXRBzarDqpdETpQeyt5v5CGe", database="Stines-GmbH", auth_plugin='mysql_native_password', ) mydb.connect() cursor = mydb.cursor() for k in range(0,31): heute = datetime.now() start_date = datetime(2023, 8, 1).replace(hour=0, minute=0, second=0, microsecond=0) start_date_00 = start_date + timedelta(days=k) start_date_23 = start_date_00.replace(hour=23, minute=59, second=59, microsecond=0) last_date = datetime(2023, 8, 31) cursor.execute("SELECT Datenbank FROM Kunden GROUP by Datenbank") kunden = cursor.fetchall() for i in kunden: if os.path.exists(fr"{i[0]}\{start_date.strftime("%d.%m")} - {last_date.strftime("%d.%m")}"): next else: try: os.mkdir(i[0]) except: next os.mkdir(fr"{i[0]}\{start_date.strftime("%d.%m")} - {last_date.strftime("%d.%m")}") os.mkdir(fr"{i[0]}\{start_date.strftime("%d.%m")} - {last_date.strftime("%d.%m")}\RAW") # RAW EXPORT with open(fr'{i[0]}\{start_date.strftime("%d.%m")} - {last_date.strftime("%d.%m")}\RAW\Active-Directory-User - {i[0]} - {start_date_00.strftime("%Y-%m-%d")}.csv', 'w', newline='', encoding='utf-8') as csvfile: csv_writer = csv.writer(csvfile,delimiter=";") cursor.execute(f'SELECT * FROM `{i[0]}`.`Active-Directory-User` where importdate BETWEEN {start_date_00.timestamp()} and {start_date_23.timestamp()}') csv_writer.writerow([i[0] for i in cursor.description]) csv_writer.writerows(cursor.fetchall()) with open(fr'{i[0]}\{start_date.strftime("%d.%m")} - {last_date.strftime("%d.%m")}\RAW\RDS-User - {i[0]} - {start_date_00.strftime("%Y-%m-%d")}.csv', 'w', newline='', encoding='utf-8') as csvfile: csv_writer = csv.writer(csvfile,delimiter=";") try: cursor.execute(f"SELECT id FROM `{i[0]}`.`Exchange-User` LIMIT 1") except: next table_exists = cursor.fetchone() is not None if table_exists: cursor.execute(f'SELECT db1.importdate as "time",db1.CreateTimeStamp,db1.SamAccountName,db1.DisplayName, db1.EmailAddress, db1.extensionAttribute1 AS "RDS Deaktviert am", db1.extensionAttribute2 AS "Exchange Deaktviert am", db1.Description,db1.Deleted,db1.LastLogonDate,db1.City FROM `{i[0]}`.`Active-Directory-User` db1 INNER JOIN `{i[0]}`.`Active-Directory-RDS-User` db2 ON db1.SamAccountName = db2.SamAccountName WHERE db1.importdate BETWEEN {start_date_00.timestamp()} and {start_date_23.timestamp()} AND db2.SamAccountName NOT LIKE "%test%" AND db2.SamAccountName NOT LIKE "%admin%"') else: cursor.execute(f'SELECT db1.importdate as "time",db1.CreateTimeStamp,db1.SamAccountName,db1.DisplayName, db1.EmailAddress, db1.extensionAttribute1 AS "RDS Deaktviert am", db1.Description,db1.Deleted,db1.LastLogonDate,db1.City FROM `{i[0]}`.`Active-Directory-User` db1 INNER JOIN `{i[0]}`.`Active-Directory-RDS-User` db2 ON db1.SamAccountName = db2.SamAccountName WHERE db1.importdate BETWEEN {start_date_00.timestamp()} and {start_date_23.timestamp()} AND db2.SamAccountName NOT LIKE "%test%" AND db2.SamAccountName NOT LIKE "%admin%"') csv_writer.writerow([i[0] for i in cursor.description]) csv_writer.writerows(cursor.fetchall()) if i[0] == "tnp-Invest-GmbH": with open(fr'{i[0]}\{start_date.strftime("%d.%m")} - {last_date.strftime("%d.%m")}\RAW\Exchange-User - {i[0]} - {start_date_00.strftime("%Y-%m-%d")}.csv', 'w', newline='', encoding='utf-8') as csvfile: csv_writer = csv.writer(csvfile,delimiter=";") cursor.execute(f'SELECT * FROM `{i[0]}`.`Exchange-User` where importdate BETWEEN {start_date_00.timestamp()} and {start_date_23.timestamp()}') csv_writer.writerow([i[0] for i in cursor.description]) csv_writer.writerows(cursor.fetchall()) # RAW EXPORT ENDE # CREATE Excel Sheet from csv Export # EXPORT to Excel-Sheet csv_datei = fr'{i[0]}\{start_date.strftime("%d.%m")} - {last_date.strftime("%d.%m")}\RAW\RDS-User - {i[0]} - {start_date_00.strftime("%Y-%m-%d")}.csv' df = pd.read_csv(csv_datei, sep=";", encoding="utf-8") # Leere Felder mit "-" füllen df = df.fillna("-") df["time"] = pd.to_datetime(df["time"], unit='s') # Excel-Datei erstellen und Daten schreiben excel_datei = fr"{i[0]}\{start_date.strftime("%d.%m")} - {last_date.strftime("%d.%m")}\Excel_Export Stand({last_date.strftime("%d.%m")}).xlsx" if os.path.exists(excel_datei): mode = 'a' else: mode = 'w' with pd.ExcelWriter(excel_datei, engine='openpyxl', mode=f'{mode}') as writer: df.to_excel(writer, index=False, sheet_name=f'{start_date_00.strftime("%d-%m")}') workbook = writer.book worksheet = writer.sheets[f'{start_date_00.strftime("%d-%m")}'] worksheet.auto_filter.ref = "A1:J1" # Spaltenbreite festlegen for column in worksheet.columns: max_length = 0 column_letter = column[0].column_letter for cell in column: try: if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length + 2) * 1.2 worksheet.column_dimensions[column_letter].width = adjusted_width os.mkdir("") # # print(table_exists) # # c = canvas.Canvas(fr"{i[0]}\{start_date.strftime("%d.%m")} - {last_date.strftime("%d.%m")}\PDF_Export Stand({last_date.strftime("%d.%m")}).pdf", pagesize=letter) # # Hier können Sie IhrenCode zum Hinzufügen von Daten zur PDF einfügen # c.setFont("Helvetica", 10) # c.drawString(50, 750, f"Create Date - {datetime.now().strftime("%d-%m-%Y")}") # c.drawImage("Firmen-Logo.png", x=450, y=730, width=150, height=40) # c.setFont("Helvetica", 12) # c.drawString(100, 680, "REPORT der RDP / Exchange User der Firma bla") # # k = 10 # n = 650 # x = 100 # t = 0 # if n < 50: # if t == 1: # x = 400 # n = 650 # print(x) # print("insede") # else: # x = 250 # n = 650 # t += 1 # c.setFont("Helvetica", 8) # c.drawString(x, n, f"Zeitraum: {i}") # c.drawString(x, n - 10, f"Aktive RDS-User: {i}") # c.drawString(x, n - 20, f"Deaktivierte RDS-User: {i}") # if table_exists: # c.drawString(x, n - 30, f"Aktive EX-User: {i}") # c.drawString(x, n - 40, f"Deaktivierte EX-User: {i}") # n = n - 60 # c.save() # subprocess.Popen(["start", "output.pdf"], shell=True) cursor.close() mydb.close()