# Database Inladen Python3
from tkinter import Tk
from fillpdf import fillpdfs
from tkinter.filedialog import askdirectory
from tkinter import *
import xlrd
import webbrowser
import tkinter as tk
import glob
from PyPDF2 import PdfFileMerger


def display_input():
        number_as_string1 = str(var1.get())
        var1PDF = number_as_string1.replace(str(1), "Database/InstallQ/1.pdf")
        var1PDF2 = var1PDF.replace(str(0), "")
        
        number_as_string2 = str(var2.get())
        var2PDF = number_as_string2.replace(str(1), "Database/InstallQ/2.pdf")
        var2PDF2 = var2PDF.replace(str(0), "")
        
        number_as_string3 = str(var3.get())
        var3PDF = number_as_string3.replace(str(1), "Database/InstallQ/3.pdf")
        var3PDF2 = var3PDF.replace("0", "")

        allepdf = [var1PDF2,var2PDF2,var3PDF2]

        list_with_empty_strings = list(allepdf)

        new_list = [x for x in list_with_empty_strings if x != '']

        print(new_list)

        # Input pad
        path2 = askdirectory(title='Selecteer de folder met berekeningen') 
        path3 = path2+'/*.xls'        

        # Output Pad
        doellocatie2 = askdirectory(title='Selecteer InstallQ Locatie') 
        output_file = doellocatie2+"/InstallQ.pdf"

        merger = PdfFileMerger()

        for pdf in new_list:
            merger.append(pdf)

        merger.write(output_file)
        merger.close()
    
        files2 = glob.glob(path3)
    
        i = 0
        while i < len(files2):
        
            # Excel File openen
            excel_workbook = xlrd.open_workbook(files2[i])
            excel_worksheet_2021 = excel_workbook.sheet_by_name('CSV Export')


            # Selecteer Data uit XLS
            ID01ex = excel_worksheet_2021.cell_value(1, 1)  # Projectnummer
            ID02ex = excel_worksheet_2021.cell_value(2, 1)  # Kavelnummer
            ID04ex = excel_worksheet_2021.cell_value(4, 1)  # Merk en type Ventilator
            ID05ex = excel_worksheet_2021.cell_value(5, 1)  # Serienr:
            ID06ex = excel_worksheet_2021.cell_value(6, 1)  # Inregeling uitgevoerd door:
            ID07ex = excel_worksheet_2021.cell_value(7, 1)  # Datum
            ID08ex = excel_worksheet_2021.cell_value(8, 1)  # Woonkamer 1 Inblaas
            ID09ex = excel_worksheet_2021.cell_value(9, 1)  # Woonkamer 2 Inblaas
            ID20ex = excel_worksheet_2021.cell_value(10, 1) # TOTAAL INBLAAS

            # Lege Regels aanvullen ter voorkoming van foutmelding
            try:
                ID01 = int(ID01ex) # Projectnummer
            except:
                ID01 = int("")

            try:
                ID02 = str(ID02ex) # Kavelnummer
            except:
                ID02 = str("")
    
            try:
                ID04 = str(ID04ex) # Merk en type Ventilator
            except:
                ID04 = str("")
    
            try:
                ID05 = int(ID05ex) # Serienr:
            except:
                ID05 = int("")
    
            try:
                ID06 = str(ID06ex) # Inregeling uitgevoerd door:
            except:
                ID06 = str("")
    
            try:
                ID07 = str(ID07ex) # Datum
            except:
                ID07 = str("")
    
            try:
                ID08 = int(ID08ex) # Woonkamer 1 Inblaas
            except:
                ID08 = int("")

            try:
                ID09 = int(ID09ex) # Woonkamer 2 Inblaas
            except:
                ID09 = int("")

            try:
                ID20 = int(ID20ex) # TOTAAL INBLAAS
            except:
                ID20 = int("")
  
            # Open PDF Files  
            fillpdfs.get_form_fields(output_file)
            print (output_file)

            # Data Toevoegen aan PDF
            data_dict = {
            '5 Merk en type Ventilator': ID04,
            '6 serienummer Ventilator': ID05,
            '7 Inregeling Uitgevoerd door': ID06,
            '8 Datum inregeling': ID07,
            'Tekstveld198': ID08,
            'Tekstveld199': ID09,
            'totaal 1': ID20,
            }

            # Savenaam aanmaken
            ID01naam = str(ID01) # Projectnummer
            ID02naam = str(ID02) # Kavelnummer
            Savenaam = doellocatie2+"/"+ID01naam+"_bnr"+ID02naam+".pdf"
            Savenaam2 = Savenaam.replace( ".0.pdf" , ".pdf" )
            
            print (Savenaam2)


            # PDF Opslaan
            fillpdfs.write_fillable_pdf(output_file, Savenaam2, data_dict)
        
            i = i + 1


# Lege Radiobuttons
    var1 = IntVar()
    var2 = IntVar()
    var3 = IntVar()
    
    # Checkboxen
    t1 = Checkbutton(toplevel, text="PDF 1", variable=var1, onvalue=1, offvalue=0)
    t1.grid(row=0, column=0, sticky=NW, pady=50, padx=10)
    t2 = Checkbutton(toplevel, text="PDF 2", variable=var2, onvalue=1, offvalue=0)
    t2.grid(row=0, column=0, sticky=NW, pady=80, padx=10)
    t3 = Checkbutton(toplevel, text="PDF 3", variable=var3, onvalue=1, offvalue=0)
    t3.grid(row=0, column=0, sticky=NW, pady=110, padx=10)
    
    t9 = Label(toplevel, text='Mail', cursor="hand2", relief='raised', foreground='Black', image=dwnd2, borderwidth = 0, width=85,
    height=28, bd='0', highlightthickness = 0)
    t9.grid(row=0, column=0, sticky=SW, pady=140, padx=10)
    t9.bind("<Button-1>", lambda e:display_input())