import csv import os import mysql.connector import pandas as pd from datetime import datetime, timedelta import openpyxl import calendar import http.client, urllib # from reportlab.lib.pagesizes import letter # from reportlab.pdfgen import canvas from openpyxl.styles import Alignment, Font import subprocess date = datetime.now() - timedelta(1) month = datetime.now().strftime("%m") year = datetime.now().strftime("%Y") start_date_00 = date.replace(hour=0, minute=0, second=0, microsecond=0) start_date_23 = start_date_00.replace(hour=23, minute=59, second=59, microsecond=0) start_date_str = start_date_00.strftime("01.%m") last_date_str = start_date_00.strftime(f"{calendar.monthrange(int(year),int(month))[1]}.%m") print(date) main_path = fr"/docker/app_data/data/karstenstoecker/files/Stines GmbH/1. Verwaltung/4. Verträge/2. Zulieferer - Dienstleister/MS - SPLA/Insight/Reports/{year}" mydb = mysql.connector.connect( host="172.17.1.21", port="3306", user="root", password="N53yBCswuawzBzS445VNAhWVMs3N59Gb9szEsrzXRBzarDqpdETpQeyt5v5CGe", database="Kunden", auth_plugin='mysql_native_password', ) mydb.connect() cursor = mydb.cursor() cursor.execute(f"""SELECT ANY_VALUE(c.companyname) AS companyname, ANY_VALUE(c.customer_ID) AS customer_ID, ANY_VALUE(cs.services_ID) FROM Kunden.company c INNER JOIN Kunden.`customers.services` cs ON cs.customer_ID = c.customer_ID WHERE cs.`services_ID` IN (100, 101) GROUP BY c.customer_ID; """) kunden = cursor.fetchall() for i in kunden: kunde = i[0] komplett_pfad = fr"{main_path}/{kunde}/{start_date_str} - {last_date_str}/{kunde}_Komplett.xlsx" if os.path.exists(komplett_pfad): os.remove(komplett_pfad) print(f"Removed file: {komplett_pfad}") else: print(f"File not found: {komplett_pfad}") if os.path.exists(fr"{main_path}/{i}/{start_date_str} - {last_date_str}/RAW"): next else: try: os.mkdir(fr"{main_path}") except: next try: os.mkdir(fr"{main_path}/{kunde}") except: next try: os.mkdir(fr"{main_path}/{kunde}/{start_date_str} - {last_date_str}") except: next try: os.mkdir(fr"{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW") except: next ## AD - User Export START ## with open(fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/Active-Directory-User - {kunde} - {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 DISTINCT u.username, us.status, us.timestamp as Last_Status FROM Kunden.`users.status` us JOIN Kunden.users u ON u.user_ID = us.user_id JOIN Kunden.`users.services` uss ON us.user_id = uss.user_id JOIN Kunden.services s ON uss.service_ID = s.service_ID JOIN Kunden.company c ON c.customer_ID = us.customer_ID WHERE c.customer_ID = {i[1]} """) csv_writer.writerow([i[0] for i in cursor.description]) csv_writer.writerows(cursor.fetchall()) # ## AD - User Export ENDE ## # ## SERVER - Export START ## with open(fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/SERVER - {kunde} - {start_date_00.strftime("%Y-%m-%d")}.csv', 'w', newline='', encoding='utf-8') as csvfile: csv_writer = csv.writer(csvfile,delimiter=";") #print(f"SELECT * FROM Kunden.server s WHERE s.customer_ID = 2 AND `Windows-Key` IS NOT NULL") cursor.execute(f"""SELECT s.hostname,s.privat_ipaddress,s.public_ipaddress, s.macaddress, s.ram ,hc.name as CPUName, hc.typ as Servertyp, hc.ghz as GhZ, hc.core as Kerne, s.os as OS, ss.name as ServiceName, s.licensekey as Lizenzschlüssel, s.status as Serverstatus FROM Kunden.server s JOIN Kunden.`hardware.cpu` hc ON hc.cpu_ID = s.CPU_ID Join Kunden.services ss ON s.service_ID = ss.service_ID WHERE s.customer_ID = {i[1]} and `licensekey` IS NOT NULL""") csv_writer.writerow([i[0] for i in cursor.description]) csv_writer.writerows(cursor.fetchall()) # ## SERVER - Export ENDE ## # ## RDS - User Export START ## with open(fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/RDS-User - {kunde} - {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 username as Username, reportingdate, lastaccess from Kunden.`services.reporting` sr where sr.customer_ID = {i[1]} and sr.service_ID = 101 and sr.reportingdate LIKE '%{date.strftime('%Y-%m-%d')}%';") #cursor.execute(f"select username as Username, reportingdate, lastaccess from Kunden.`services.reporting` sr where sr.customer_ID = {i[1]} and sr.service_ID = 101 and DATE(sr.reportingdate) = CURDATE();") csv_writer.writerow([i[0] for i in cursor.description]) csv_writer.writerows(cursor.fetchall()) ## RDS - User Export ENDE ## ## EX - User Export Start ## if i[2] == 100: with open(fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/EX-User - {kunde} - {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 u.username as Username, u.primarymail as EMailAdresse,sr.reportingdate as ReportDate, sr.lastaccess as LastAccess from Kunden.`services.reporting` sr JOIN Kunden.users u ON u.username = sr.username where sr.customer_ID = {i[1]} and sr.service_ID = 100 and sr.reportingdate LIKE '%{date.strftime('%Y-%m-%d')}%';""") csv_writer.writerow([i[0] for i in cursor.description]) csv_writer.writerows(cursor.fetchall()) else: next ## EX - User Export ENDE ## ## RAW EXPORT ENDE ## ## CREATE Excel Sheet from csv Export ## ## EXPORT to AD- User Excel-Sheet ## csv_datei = fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/RDS-User - {kunde} - {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"{main_path}/{kunde}/{start_date_str} - {last_date_str}/{kunde}_User_Export Stand({last_date_str}).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 ## EXPORT to AD- User Excel-Sheet ## csv_datei = fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/SERVER - {kunde} - {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"{main_path}/{kunde}/{start_date_str} - {last_date_str}/{kunde}_Server_Export Stand({last_date_str}).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 csv_datei = fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/RDS-User - {kunde} - {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"{main_path}/{kunde}/{start_date_str} - {last_date_str}/{kunde}_Komplett.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'RDS - User') workbook = writer.book worksheet = writer.sheets[f'RDS - User'] 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 if i[2] == 100: ## Export Mailbox if Service 100 exist on Customer ## csv_datei = fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/EX-User - {kunde} - {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"{main_path}/{kunde}/{start_date_str} - {last_date_str}/{kunde}_Mailbox_Export Stand({last_date_str}).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:E1" # 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 csv_datei = fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/EX-User - {kunde} - {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"{main_path}/{kunde}/{start_date_str} - {last_date_str}/{kunde}_Komplett.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'Exchange - User') workbook = writer.book worksheet = writer.sheets[f'Exchange - User'] worksheet.auto_filter.ref = "A1:E1" # 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 else: next ## Export Server ## csv_datei = fr'{main_path}/{kunde}/{start_date_str} - {last_date_str}/RAW/SERVER - {kunde} - {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"{main_path}/{kunde}/{start_date_str} - {last_date_str}/{kunde}_Komplett.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'Server') workbook = writer.book worksheet = writer.sheets[f'Server'] 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}/{start_date_str} - {last_date_str}/PDF_Export Stand({last_date_str}).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() os.system(f'chmod 777 -R "/docker/app_data/data/karstenstoecker/files/Stines GmbH/1. Verwaltung/4. Verträge/2. Zulieferer - Dienstleister/MS - SPLA/Insight/Reports/{year}"') os.system(f"/bin/bash -c 'docker exec -u www-data app-server /bin/bash -c 'php occ files:scan --path=karstenstoecker/files/Stines\ GmbH/1.\ Verwaltung/4.\ Verträge/2.\ Zulieferer\ -\ Dienstleister/MS\ -\ SPLA/Insight/Reports/''") # SEND Pushover ## conn = http.client.HTTPSConnection("api.pushover.net:443") conn.request("POST", "/1/messages.json", urllib.parse.urlencode({ "token": "avzcexyjeu7y71pcskwshyx8ytmq8i", "user": "uo2sf2pmrtjvt8auu786fviabimimr", "message": "SPLA Reporting was running!", }), { "Content-type": "application/x-www-form-urlencoded" }) conn.getresponse()