from tkinter import*
import sqlite3
import tkinter.ttk as ttk
import tkinter.messagebox as tkMessageBox
root = Tk()
root.geometry("1000x650")
root.configure(bg="#426ff5")
root.resizable(0,0)
root.title("Marcação de Consultas")
def Database():
    global conn, cursor
    conn = sqlite3.connect('marcarconsultas.db')
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS `member` (mem_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                   "nome TEXT, cc TEXT, sns TEXT, datanascimento TEXT, diaconsulta TEXT, mesconsulta TEXT,anoconsulta TEXT,"
                   "horaconsulta TEXT)")

def Criar():
    if  NOME.get() == "" or CC.get() == "" or SNS.get() == "" or DATANASCIMENTO.get() == "" or DIA.get() == ""\
            or MES.get() == "" or ANO.get() == "" or HORA.get() == "":
        txt_result.config(text="Por favor, complete de preencher!", fg="red")
    else:
        Database()
        cursor.execute("INSERT INTO `member` (nome, cc, sns, datanascimento, diaconsulta,"
                       " mesconsulta,anoconsulta,horaconsulta) VALUES(?, ?, ?, ?, ?, ?,?,?)", (str(NOME.get()),
                                                                                               str(CC.get()),
                                                                                               str(SNS.get()),
                                                                                               str(DATANASCIMENTO.get()),
                                                                                               str(DIA.get()),
                                                                                               str(MES.get()),str(ANO.get()),
                                                                                               str(HORA.get())
                                                                                               ))
        tree.delete(*tree.get_children())
        cursor.execute("SELECT * FROM `member` ORDER BY `nome` ASC")
        fetch = cursor.fetchall()
        for data in fetch:
            tree.insert('', 'end', values=(data[0], data[1], data[2], data[3], data[4], data[5], data[6],data[7],data[8]))
        conn.commit()
        NOME.set("")
        CC.set("")
        SNS.set("")
        DATANASCIMENTO.set("")
        DIA.set("")
        MES.set("")
        ANO.set("")
        HORA.set("")
        cursor.close()
        conn.close()
        txt_result.config(text="Criado Dado!", fg="green")

def Ler():
    tree.delete(*tree.get_children())
    Database()
    cursor.execute("SELECT * FROM `member` ORDER BY `nome` ASC")
    fetch = cursor.fetchall()
    for data in fetch:
        tree.insert('', 'end', values=(data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8]))
    cursor.close()
    conn.close()
    txt_result.config(text="Totalidade das Marcações", fg="black")
def Update():
    Database()
    tree.delete(*tree.get_children())
    cursor.execute("UPDATE `member` SET `nome` = ?, `cc` = ?, `sns` =?,  `datanascimento` = ?,  `diaconsulta` = ?, `mesconsulta` = ? , `anoconsulta` = ?, `horaconsulta` = ?WHERE `mem_id` = ?", (str(NOME.get()),str(CC.get()),str(SNS.get()),str(DATANASCIMENTO.get()),str(DIA.get()),str(MES.get()),str(ANO.get()),str(HORA.get()), int(mem_id)))
    conn.commit()
    cursor.execute("SELECT * FROM `member` ORDER BY `nome` ASC")
    fetch = cursor.fetchall()
    for data in fetch:
        tree.insert('', 'end', values=(data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8]))
    cursor.close()
    conn.close()
    NOME.set("")
    CC.set("")
    SNS.set("")
    DATANASCIMENTO.set("")
    DIA.set("")
    MES.set("")
    ANO.set("")
    HORA.set("")
    btn_create.config(state=NORMAL)
    btn_read.config(state=NORMAL)
    btn_update.config(state=DISABLED)
    btn_delete.config(state=NORMAL)
    txt_result.config(text="Alterado com Sucesso!", fg="green")


def OnSelected(event):
    global mem_id;
    curItem = tree.focus()
    contents =(tree.item(curItem))
    selecteditem = contents['values']
    mem_id = selecteditem[0]
    NOME.set("")
    CC.set("")
    SNS.set("")
    DATANASCIMENTO.set("")
    DIA.set("")
    MES.set("")
    ANO.set("")
    HORA.set("")
    NOME.set(selecteditem[1])
    CC.set(selecteditem[2])
    SNS.set(selecteditem[3])
    DATANASCIMENTO.set(selecteditem[4])
    DIA.set(selecteditem[5])
    MES.set(selecteditem[6])
    ANO.set(selecteditem[7])
    HORA.set(selecteditem[8])
    btn_create.config(state=DISABLED)
    btn_read.config(state=DISABLED)
    btn_update.config(state=NORMAL)
    btn_delete.config(state=DISABLED)

def Apagar():
    if not tree.selection():
        txt_result.config(text="Por favor,selecione a marcação!", fg="blue")
    else:
        result = tkMessageBox.askquestion('Eliminar Marcação',
                                          'Deseja apagar a marcação?', icon="warning")
        if result == 'yes':
            curItem = tree.focus()
            contents = (tree.item(curItem))
            selecteditem = contents['values']
            tree.delete(curItem)
            Database()
            cursor.execute("DELETE FROM `member` WHERE `mem_id` = %d" % selecteditem[0])
            conn.commit()
            cursor.close()
            conn.close()
            txt_result.config(text="Marcação apagada com sucesso!", fg="red")

def Exit():
    result = tkMessageBox.askquestion('Marcação de Consultas', 'Deseja sair do programa?', icon="warning")
    if result == 'yes':
        root.destroy()
        exit()
Top = Frame(root, width=300, height=50, bd=8, relief="raise",bg="#1510a3")
Top.pack(side=TOP)
Left = Frame(root, width=400, height=500, bd=8, relief="raise",bg="#1510a3")
Left.pack(side=LEFT)
Right = Frame(root, width=400, height=500, bd=8, relief="raise",bg="#429ef5")
Right.pack(side=RIGHT)
Forms = Frame(Left, width=500, height=450,bg="#42f5e6")
Forms.pack(side=TOP)
Buttons = Frame(Left, width=400, height=100, bd=8, relief="raise",bg="#105aa3")
Buttons.pack(side=BOTTOM)
# TEXTO
txt_titulo = Label(Top, width=900, font=('arial', 24), text = "Marcação de Consultas",bg="#42f5e6",fg="#1602b0")
txt_titulo.pack()
txt_nomecompleto = Label(Forms, text="Nome Completo:", font=('arial', 16), bd=15,bg="#42f5e6",fg="#a3990a")
txt_nomecompleto.grid(row=0, sticky="e")
txt_cc = Label(Forms, text="Cartão de Cidadão:", font=('arial', 16), bd=15,bg="#42f5e6",fg="#a3990a")
txt_cc.grid(row=1, sticky="e")
txt_sns = Label(Forms, text="Cartão de Saúde:", font=('arial', 16), bd=15,bg="#42f5e6",fg="#a3990a")
txt_sns.grid(row=2, sticky="e")
txt_datanascimento = Label(Forms, text="Data de Nascimento:", font=('arial', 16), bd=15,bg="#42f5e6",fg="#a3990a")
txt_datanascimento.grid(row=3, sticky="e")
txt_dia_consulta = Label(Forms, text="Dia da Consulta:", font=('arial', 16), bd=15,bg="#42f5e6",fg="#a3990a")
txt_dia_consulta.grid(row=4, sticky="e")
txt_mes_consulta = Label(Forms, text="Mês da Consulta:", font=('arial', 16), bd=15,bg="#42f5e6",fg="#a3990a")
txt_mes_consulta.grid(row=5, sticky="e")
txt_ano_consulta = Label(Forms, text="Ano da Consulta:", font=('arial', 16), bd=15,bg="#42f5e6",fg="#a3990a")
txt_ano_consulta.grid(row=6, sticky="e")
txt_hora_consulta = Label(Forms, text="Hora da Consulta:", font=('arial', 16), bd=15,bg="#42f5e6",fg="#a3990a")
txt_hora_consulta.grid(row=7, sticky="e")
txt_result = Label(Buttons)
txt_result.pack(side=TOP)
NOME = StringVar()
CC = StringVar()
SNS = StringVar()
DIA = StringVar()
MES = StringVar()
ANO = StringVar()
HORA = StringVar()
DATANASCIMENTO  = StringVar()

nome = Entry(Forms, textvariable=NOME, width=20)
nome.grid(row=0, column=1)
cc = Entry(Forms, textvariable=CC, width=20)
cc.grid(row=1, column=1)
sns = Entry(Forms, textvariable=SNS, width=20)
sns.grid(row=2, column=1)
datanascimento = Entry(Forms, textvariable=DATANASCIMENTO, width=20)
datanascimento.grid(row=3, column=1)
diaconsulta = Entry(Forms, textvariable=DIA, width=20)
diaconsulta.grid(row=4, column=1)
mesconsulta = Entry(Forms, textvariable= MES, width=20)
mesconsulta.grid(row=5, column=1)
anoconsulta = Entry(Forms, textvariable= ANO, width=20)
anoconsulta.grid(row=6, column=1)
horaconsulta = Entry(Forms, textvariable= HORA, width=20)
horaconsulta.grid(row=7, column=1)
#
btn_create = Button(Buttons, width=10, text="Criar",command=Criar)
btn_create.pack(side=LEFT)

btn_read = Button(Buttons, width=10, text="Ler", command=Ler)
btn_read.pack(side=LEFT)
btn_update = Button(Buttons, width=10, text="Update",command=Update,state=DISABLED)
btn_update.pack(side=LEFT)

btn_delete = Button(Buttons, width=10, text="Eliminar", command=Apagar)
btn_delete.pack(side=LEFT)
btn_exit = Button(Buttons, width=10, text="Sair", command=Exit)
btn_exit.pack(side=LEFT)


# TABELA
scrollbary = Scrollbar(Right, orient=VERTICAL)
scrollbarx = Scrollbar(Right, orient=HORIZONTAL)
tree = ttk.Treeview(Right, columns=("MemberID", "Nome", "Cc", "Sns", "Datanascimento", "Diaconsulta",
                                    "Mesconsulta","Anoconsulta","Horaconsulta"),
                    selectmode="extended", height=500, yscrollcommand=scrollbary.set, xscrollcommand=scrollbarx.set)
scrollbary.config(command=tree.yview)
scrollbary.pack(side=RIGHT, fill=Y)
scrollbarx.config(command=tree.xview)
scrollbarx.pack(side=BOTTOM, fill=X)
tree.heading('MemberID', text="MemberID", anchor=W)
tree.heading('Nome', text="Nome", anchor=W)
tree.heading('Cc', text="Cartão de Cidadão", anchor=W)
tree.heading('Sns', text="Cartão de Saúde", anchor=W)
tree.heading('Datanascimento', text="Data de Nascimento", anchor=W)
tree.heading('Diaconsulta', text="Dia da Consulta", anchor=W)
tree.heading('Mesconsulta', text="Mês da Consulta", anchor=W)
tree.heading('Anoconsulta', text="Ano da Consulta", anchor=W)
tree.heading('Horaconsulta', text="Hora da Consulta", anchor=W)
tree.column('#0', stretch=NO, minwidth=0, width=0)
tree.column('#1', stretch=NO, minwidth=0, width=0)
tree.column('#2', stretch=NO, minwidth=0, width=80)
tree.column('#3', stretch=NO, minwidth=0, width=120)
tree.column('#4', stretch=NO, minwidth=0, width=80)
tree.column('#5', stretch=NO, minwidth=0, width=150)
tree.column('#6', stretch=NO, minwidth=0, width=120)
tree.column('#7', stretch=NO, minwidth=0, width=120)
tree.column('#8', stretch=NO, minwidth=0, width=120)
tree.column('#8', stretch=NO, minwidth=0, width=120)
tree.column('#9', stretch=NO, minwidth=0, width=120)

tree.pack()
tree.bind('<Double-Button-1>', OnSelected)

if __name__ == '__main__':
    root.mainloop()