import csv
import os
import mysql.connector
import pandas as pd
from datetime import datetime, timedelta
import openpyxl
from reportlab.lib.pagesizes import letter
from reportlab.pdfgen import canvas
import subprocess

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()

