import sys
import pymysql
import pandas as pd
from datetime import datetime
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import *
from userlogin import Ui_UserLogin
from registernav import Ui_RegisterNavigation
from userfunc  import Ui_UserFunctionality
from visitorfunc import Ui_VisitorFunctionality
from staffvisitorfunc import Ui_StaffVisitorFunctionality
from adminvisitorfunc import Ui_AdminVisitorFunctionality
from managervisitorfunc import Ui_ManagerVisitorFunctionality
from stafffunc import Ui_StaffFunctionality
from adminfunc import Ui_AdminFunctionality
from managerfunc import Ui_ManagerFunctionality
from userviewtransithistory import Ui_UserTransitHistory
from usertaketransit import Ui_UserTakeTransit
from admineditsite import Ui_AdminEditSite
from admincreatesite import Ui_AdminCreateSite
from registeruser import Ui_RegisterUser
from registervisitor import Ui_RegisterVisitor
from registeremployee import Ui_RegisterEmployee
from registeremployeevisitor import Ui_RegisterEmployeeVisitor
from adminmanagesite import Ui_AdminManageSite
from adminmanageuser import Ui_AdminManageUser
from adminmanagetransit import Ui_AdminManageTransit
from adminedittransit import Ui_AdminEditTransit
from admincreatetransit import Ui_AdminCreateTransit
from managermanageevent import Ui_ManagerManageEvent
from managermanagestaff import Ui_ManagerManageStaff
from managersitereport import Ui_ManagerSiteReport
from managerdailydetail import Ui_ManagerDailyDetail
from staffmanageprofile import Ui_EmployeeManageProfile
from staffviewschedule import Ui_StaffViewSchedule
from staffeventdetail import Ui_StaffEventDetail
from managercreateevent import Ui_ManagerCreateEvent
from visitorexploreevent import Ui_VisitorExploreEvent
from visitoreventdetail import Ui_VisitorEventDetail
from visitorexploresite import Ui_VisitorExploreSite
from visitortransitdetail import Ui_VisitorTransitDetail
from visitorsitedetail import Ui_VisitorSiteDetail
from visitorvisithistory import Ui_VisitorVisitHistory
from managervieweditevent import Ui_ManagerViewEditEvent

def hashPassword(password):
    import hashlib
    return hashlib.md5(str(password).encode('utf-8')).hexdigest()

class PandasModel(QAbstractTableModel):
    """
    Class to populate a table view with a pandas dataframe
    """
    def __init__(self, data, parent=None):
        QAbstractTableModel.__init__(self, parent)
        self._data = data

    def rowCount(self, parent=None):
        return len(self._data.values)

    def columnCount(self, parent=None):
        return self._data.columns.size

    def data(self, index, role=Qt.DisplayRole):
        if index.isValid():
            if role == Qt.DisplayRole:
                return str(self._data.values[index.row()][index.column()])
        return None

    def headerData(self, col, orientation, role):
        if orientation == Qt.Horizontal and role == Qt.DisplayRole:
            return self._data.columns[col]
        return None

class loginwindow(QMainWindow, Ui_UserLogin):

    def __init__(self, parent= None):
        super(loginwindow, self).__init__(parent)
        self.setupUi(self)
        self.loginbutton.clicked.connect(self.login_button)
        self.registrationbutton.clicked.connect(self.register_button)
        self.lineEdit_2.setEchoMode(QLineEdit.Password)

    def register_button(self):
        self.window = registernavigation()
        self.close()
        self.window.show()

    def login_button(self):
        USERNAME = self.lineEdit.text()
        PASSWORD = self.lineEdit_2.text()

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        get_user = f"SELECT * FROM user WHERE username = '{USERNAME}' and password ='{hashPassword(PASSWORD)}'"

        self.currentuser = USERNAME

        if cursor.execute(get_user):

            type_query = f"SELECT usertype from user where username = '{USERNAME}' and password ='{hashPassword(PASSWORD)}'"
            cursor.execute(type_query)
            usertype = cursor.fetchone()[0]

            if usertype == "User":
                self.window = userfunctionality()
                self.close()
                self.window.show()

            elif usertype == "Visitor":
                self.window = visitorfunctionality()
                self.close()
                self.window.show()

            elif usertype == "Employee":
                employeetypequery = f"SELECT user.usertype, employee.employeetype from employee join user on user.username = employee.username where user.username = '{USERNAME}' and user.password = '{hashPassword(PASSWORD)}'"
                cursor.execute(employeetypequery)
                employee_type = cursor.fetchone()[1]


                if employee_type == "Staff":
                    self.window = stafffunctionality()
                    self.close()
                    self.window.show()

                elif employee_type == "Administrator":
                    self.window = adminfunctionality()
                    self.close()
                    self.window.show()

                elif employee_type == "Manager":
                    self.window = managerfunctionality()
                    self.close()
                    self.window.show()

            elif usertype == "Employee-Visitor":

                employeetypequery = f"SELECT user.usertype, employee.employeetype from employee join user on user.username = employee.username where user.username = '{USERNAME}' and user.password = '{hashPassword(PASSWORD)}'"
                cursor.execute(employeetypequery)
                employee_type = cursor.fetchone()[1]


                if employee_type == "Staff":
                    self.window = staffvisitorfunctionality()
                    self.close()
                    self.window.show()

                elif employee_type == "Administrator":
                    self.window = adminvisitorfunctionality()
                    self.close()
                    self.window.show()

                elif employee_type == "Manager":
                    self.window = managervisitorfunctionality()
                    self.close()
                    self.window.show()


        else:
            print("Incorrect Login Info")

class registernavigation(QMainWindow, Ui_RegisterNavigation):
    def __init__(self, parent= None):
        super(registernavigation, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.UserButton.clicked.connect(self.user_button)
        self.VisitorButton.clicked.connect(self.visitor_button)
        self.EmployeeButton.clicked.connect(self.employee_button)
        self.EmployeeVisitorButton.clicked.connect(self.employee_visitor_button)

    def back_button(self):
        # self.window = loginwindow()
        self.close()
        # self.window.show()
        loginwindow.show()

    def user_button(self):
        self.window = registeruser()
        self.close()
        self.window.show()
    def visitor_button(self):
        self.window = registervisitor()
        self.close()
        self.window.show()
    def employee_button(self):
        self.window = registeremployee()
        self.close()
        self.window.show()
    def employee_visitor_button(self):
        self.window = registeremployeevisitor()
        self.close()
        self.window.show()

class registeruser(QMainWindow, Ui_RegisterUser):
    def __init__(self, parent= None):
        super(registeruser, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.registerButton.clicked.connect(self.register_button)
        self.addEmailButton.clicked.connect(self.add_button)
        self.removeEmailButton.clicked.connect(self.remove_button)
        self.emailList = []

    def back_button(self):
        self.window = registernavigation()
        self.close()
        self.window.show()

    def add_button(self):
        emailText = self.emailEdit.text()
        self.emailList.append(emailText)

    def remove_button(self):
        emailText = self.emailEdit.text()
        try:
            self.emailList.remove(emailText)
        except:
            print('fail')

    def register_button(self):
        firstname = self.firstNameEdit.text()
        lastname = self.lastNameEdit.text()
        USERNAME = self.usernameEdit.text()
        PASSWORD = self.passEdit.text()
        confirm_password = self.confirmPassEdit.text()
        #email = self.emailEdit.text()

        if PASSWORD == confirm_password:
            connection = pymysql.connect(host="localhost",
                                        user="root",
                                        password="12345678",
                                        db="beltline")
            cursor = connection.cursor()
            insert_user = f"INSERT INTO user (Username,Firstname,Lastname,Status,UserType,Password) VALUES ('{USERNAME}', '{firstname}', '{lastname}', 'Pending', 'User', '{hashPassword(PASSWORD)}')"
            if cursor.execute(insert_user):
                connection.commit()
                for email in self.emailList:
                    insert_email = f"insert into email (Username, Email) values ('{USERNAME}', '{email}')"
                    cursor.execute(insert_email)
                    connection.commit()
                self.close()
                loginwindow.show()

class registervisitor(QMainWindow, Ui_RegisterVisitor):
    def __init__(self, parent= None):
        super(registervisitor, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.addEmailButton.clicked.connect(self.add_button)
        self.removeEmailButton.clicked.connect(self.remove_button)
        self.registerButton.clicked.connect(self.register_button)
        self.emailList = []

    def back_button(self):
        self.window = registernavigation()
        self.close()
        self.window.show()

    def add_button(self):
        emailText = self.emailEdit.text()
        self.emailList.append(emailText)
        print(emailText)

    def remove_button(self):
        emailText = self.emailEdit.text()
        try:
            self.emailList.remove(emailText)
        except:
            print('fail')

    def register_button(self):
        firstname = self.firstNameEdit.text()
        lastname = self.lastNameEdit.text()
        USERNAME = self.usernameEdit.text()
        PASSWORD = self.passEdit.text()
        confirm_password = self.confirmPassEdit.text()
        #email = self.emailEdit.text()

        if PASSWORD == confirm_password:
            connection = pymysql.connect(host="localhost",
                                        user="root",
                                        password="12345678",
                                        db="beltline")
            cursor = connection.cursor()
            insert_user = f"INSERT INTO user (Username,Firstname,Lastname,Status,UserType,Password) VALUES ('{USERNAME}', '{firstname}', '{lastname}', 'Pending', 'Visitor', '{hashPassword(PASSWORD)}')"
            if cursor.execute(insert_user):
                connection.commit()
                for email in self.emailList:
                    insert_email = f"insert into email (Username, Email) values ('{USERNAME}', '{email}')"
                    cursor.execute(insert_email)
                    connection.commit()

                self.close()
                loginwindow.show()

class registeremployee(QMainWindow, Ui_RegisterEmployee):
    def __init__(self, parent= None):
        super(registeremployee, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.registerButton.clicked.connect(self.register_button)
        self.addEmailButton.clicked.connect(self.add_button)
        self.removeEmailButton.clicked.connect(self.remove_button)
        self.emailList = []

        stateList = ['AK', 'AL', 'AR', 'AZ', 'CA', 'CO',
             'CT', 'DC', 'DE', 'FL', 'GA', 'HI',
             'IA', 'ID', 'IL', 'IN', 'KS', 'KY',
             'LA', 'MA', 'MD', 'ME', 'MI', 'MN',
             'MO', 'MS', 'MT', 'NC', 'ND', 'NE',
             'NH', 'NJ', 'NM', 'NV', 'NY', 'OH',
             'OK', 'OR', 'PA', 'RI', 'SC', 'SD',
             'TN', 'TX', 'UT', 'VA', 'VT', 'WA',
             'WI', 'WV', 'WY', 'Other']
        self.stateComboBox.addItems(stateList)

        typeList = ['Manager', 'Staff']
        self.stateComboBox_2.addItems(typeList)

    def back_button(self):
        self.window = registernavigation()
        self.close()
        self.window.show()

    def add_button(self):
        emailText = self.emailEdit.text()
        self.emailList.append(emailText)

    def remove_button(self):
        emailText = self.emailEdit.text()
        try:
            self.emailList.remove(emailText)
        except:
            print('fail')


    def register_button(self):
        firstname = self.firstNameEdit.text()
        lastname = self.lastNameEdit.text()
        USERNAME = self.usernameEdit.text()
        PASSWORD = self.passEdit.text()
        confirm_password = self.confirmPassEdit.text()
        email = self.emailEdit.text()
        address = self.addressEdit.text()
        phone = self.phoneEdit.text()
        zipc = self.zipcodeEdit.text()
        city = self.cityEdit.text()
        state = self.stateComboBox.currentText()
        employeetype = self.stateComboBox_2.currentText()
        print(state)
        print(employeetype)


        if PASSWORD == confirm_password:
            connection = pymysql.connect(host="localhost",
                                        user="root",
                                        password="12345678",
                                        db="beltline")
            cursor = connection.cursor()
            insert_user = f"INSERT INTO user (Username,Firstname,Lastname,Status,UserType,Password) VALUES ('{USERNAME}', '{firstname}', '{lastname}', 'Pending', 'Employee', '{hashPassword(PASSWORD)}')"
            cursor.execute(insert_user)
            connection.commit()

            get_id = f"select employeeID from employee"
            cursor.execute(get_id)
            id_list = cursor.fetchall()
            newID = int(id_list[-1][0]) + 1


            insert_employee = f"INSERT INTO employee (EmployeeID,Username,Phone,Address, City, State, Zipcode, EmployeeType) values ('{newID}','{USERNAME}', '{phone}', '{address}', '{city}', '{state}', '{zipc}', '{employeetype}')"
            cursor.execute(insert_employee)
            connection.commit()


            for email in self.emailList:
                insert_email = f"insert into email (Username, Email) values ('{USERNAME}', '{email}')"
                cursor.execute(insert_email)
                connection.commit()

            self.close()
            loginwindow.show()

class registeremployeevisitor(QMainWindow, Ui_RegisterEmployeeVisitor):
    def __init__(self, parent= None):
        super(registeremployeevisitor, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.registerButton.clicked.connect(self.register_button)
        stateList = ['AK', 'AL', 'AR', 'AZ', 'CA', 'CO',
             'CT', 'DC', 'DE', 'FL', 'GA', 'HI',
             'IA', 'ID', 'IL', 'IN', 'KS', 'KY',
             'LA', 'MA', 'MD', 'ME', 'MI', 'MN',
             'MO', 'MS', 'MT', 'NC', 'ND', 'NE',
             'NH', 'NJ', 'NM', 'NV', 'NY', 'OH',
             'OK', 'OR', 'PA', 'RI', 'SC', 'SD',
             'TN', 'TX', 'UT', 'VA', 'VT', 'WA',
             'WI', 'WV', 'WY', 'Other']
        self.stateComboBox.addItems(stateList)

        typeList = ['Manager', 'Staff']
        self.stateComboBox_2.addItems(typeList)
        self.addEmailButton.clicked.connect(self.add_button)
        self.removeEmailButton.clicked.connect(self.remove_button)
        self.emailList = []


    def back_button(self):
        self.window = registernavigation()
        self.close()
        self.window.show()

    def add_button(self):
        emailText = self.emailEdit.text()
        self.emailList.append(emailText)

    def remove_button(self):
        emailText = self.emailEdit.text()
        try:
            self.emailList.remove(emailText)
        except:
            print('fail')

    def register_button(self):
        firstname = self.firstNameEdit.text()
        lastname = self.lastNameEdit.text()
        USERNAME = self.usernameEdit.text()
        PASSWORD = self.passEdit.text()
        confirm_password = self.confirmPassEdit.text()
        email = self.emailEdit.text()
        address = self.addressEdit.text()
        phone = self.phoneEdit.text()
        zipc = self.zipcodeEdit.text()
        city = self.cityEdit.text()
        state = self.stateComboBox.currentText()
        employeetype = self.stateComboBox_2.currentText()



        if PASSWORD == confirm_password:
            connection = pymysql.connect(host="localhost",
                                        user="root",
                                        password="12345678",
                                        db="beltline")
            cursor = connection.cursor()
            insert_user = f"INSERT INTO user (Username,Firstname,Lastname,Status,UserType,Password) VALUES ('{USERNAME}', '{firstname}', '{lastname}', 'Pending', 'Employee-Visitor', '{hashPassword(PASSWORD)}')"
            cursor.execute(insert_user)
            connection.commit()

            get_id = f"select employeeID from employee"
            cursor.execute(get_id)
            id_list = cursor.fetchall()
            newID = int(id_list[-1][0]) + 1

            insert_employee = f"INSERT INTO employee (EmployeeID,Username,Phone,Address, City, State, Zipcode, EmployeeType) values ({newID},'{USERNAME}', '{phone}', '{address}', '{city}', '{state}', '{zipc}', '{employeetype}')"
            cursor.execute(insert_employee)
            connection.commit()

            for email in self.emailList:
                insert_email = f"insert into email (Username, Email) values ('{USERNAME}', '{email}')"
                cursor.execute(insert_email)
                connection.commit()

            self.close()
            loginwindow.show()

class userfunctionality(QMainWindow, Ui_UserFunctionality):
    def __init__(self, parent= None):
        super(userfunctionality, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.ViewTransitButton.clicked.connect(self.viewtransit)
        self.TakeTransitButton.clicked.connect(self.taketransit)


    def back_button(self):
        # self.window = loginwindow()
        self.close()
        # self.window.show()
        loginwindow.show()

    def taketransit(self):
        self.window =usertaketransit()
        self.close()
        self.window.show()

    def viewtransit(self):
        self.window = userviewtransithistory()
        self.close()
        self.window.show()

class userviewtransithistory(QMainWindow, Ui_UserTransitHistory):
    def __init__(self, parent= None):
        super(userviewtransithistory, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.filterButton.clicked.connect(self.filter_button)
        connection = pymysql.connect(host="localhost",
                                        user="root",
                                        password="12345678",
                                        db="beltline")
        cursor = connection.cursor()
        managequery = f"select take.date, take.route, transit.transittype, transit.price from transit join take on take.route = transit.route where take.username ='{loginwindow.currentuser}' group by transit.route"
        df = pd.read_sql(managequery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)



        self.comboBox.addItems(['MARTA', 'Bus', 'Bike'])

        siteQuery = f"select name from site"
        cursor.execute(siteQuery)
        list1OfSites = cursor.fetchall()
        listOfSites = []
        for a in list1OfSites:
            listOfSites.append(a[0])
        self.comboBox_2.addItems(listOfSites)


    def back_button(self):
        self.window = userfunctionality()
        self.close()
        self.window.show()

    def filter_button(self):
        contain_site = self.comboBox_2.currentText()
        transport_type= self.comboBox.currentText()
        route = self.lineEdit.text()
        start_date = self.lineEdit_2.text()
        end_date = self.lineEdit_3.text()

        connection = pymysql.connect(host="localhost",
                                        user="root",
                                        password="12345678",
                                        db="beltline")
        cursor = connection.cursor()
        managequery = f"select take.date, take.route, transit.transittype, transit.price from transit join take on take.route = transit.route join connect on connect.route where take.username ='{loginwindow.currentuser}' and connect.name = '{contain_site}' and transit.TransitType = '{transport_type}' and take.route ='{route}' and take.date between '{start_date}' and '{end_date}' group by transit.route"

        df = pd.read_sql(managequery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

class usertaketransit(QMainWindow, Ui_UserTakeTransit):
    def __init__(self, parent= None):
        super(usertaketransit, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.filterButton.clicked.connect(self.filter_button)
        self.logTransitButton.clicked.connect(self.logTransit_button)
        self.comboBox_2.addItems(["Bus", "Bike", "MARTA"])
        connection = pymysql.connect(host="localhost",
                                        user="root",
                                        password="12345678",
                                        db="beltline")

        cursor = connection.cursor()
        managequery = f"select transit.route, transit.transittype, transit.price, count(connect.route) as '# Connected Sites' from transit join connect on connect.route = transit.route join take on take.route = transit.route group by transit.route"
        df = pd.read_sql(managequery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

        siteQuery = f"select name from site"
        cursor.execute(siteQuery)
        list1OfSites = cursor.fetchall()
        listOfSites = []
        for a in list1OfSites:
            listOfSites.append(a[0])

        self.comboBox.addItems(listOfSites)


        self.comboBox_2.addItems(['MARTA', 'Bus', 'Bike'])

        self.tableView.setSelectionBehavior(QTableView.SelectRows)
        self.tableView.setSelectionMode(QAbstractItemView.SingleSelection)





    def back_button(self):
        self.window = userfunctionality()
        self.close()
        self.window.show()

    def filter_button(self):
        contain_site = self.comboBox.currentText()
        transport_type= self.comboBox_2.currentText()
        price_low = self.lineEdit.text()
        price_high = self.lineEdit_2.text()
        # transit_date = lineEdit_3.text()

        connection = pymysql.connect(host="localhost",
                                        user="root",

                                        password="12345678",


                                        db="beltline")
        cursor = connection.cursor()
        managequery = f"select transit.route, transit.transittype, transit.price, count(connect.route) as '# Connected Sites' from transit join connect on connect.route = transit.route join take on take.route = transit.route  where connect.name = 'Inman Park' and connect.TransitType = 'Bus' and transit.price between '{price_low}' and '{price_high}' group by transit.route"
        df = pd.read_sql(managequery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
    def logTransit_button(self):
        transport_type= self.comboBox_2.currentText()
        price_low = self.lineEdit.text()
        price_high = self.lineEdit_2.text()


        connection = pymysql.connect(host="localhost",
                                        user="root",
                                        password="12345678",
                                        db="beltline")
        cursor = connection.cursor()
        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()

        model = self.tableView.model()
        print(model.rowCount())
        route = model.index(index, 0).data()
        transitType = model.index(index, 1).data()

        print(route, transitType)

        logTransit = f"insert into take (Username, TransitType, Route, Date) values ('{loginwindow.currentuser}', '{transitType}', '{route}', '{self.lineEdit_3.text()}')"
        cursor.execute(logTransit)
        connection.commit()

class adminfunctionality(QMainWindow, Ui_AdminFunctionality):
    def __init__(self, parent= None):
        super(adminfunctionality, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.ManageSiteButton.clicked.connect(self.managesite_button)
        self.ManageUserButton.clicked.connect(self.manageuser_button)
        self.ManageTransitButton.clicked.connect(self.managetransit_button)
        self.manageProfileButton.clicked.connect(self.manageprofile_button)
        self.ViewTransitButton.clicked.connect(self.viewtransit)
        self.TakeTransitButton.clicked.connect(self.taketransit)

    def back_button(self):
        # self.window = loginwindow()
        self.close()
        # self.window.show()
        loginwindow.show()

    def managesite_button(self):
        self.window = adminmanagesite()
        self.close()
        self.window.show()
    def manageuser_button(self):
        self.window = adminmanageuser()
        self.close()
        self.window.show()
    def managetransit_button(self):
        self.window = adminmanagetransit()
        self.close()
        self.window.show()
    def manageprofile_button(self):
        self.window = staffmanageprofile()
        self.close()
        self.window.show()
    def taketransit(self):
        self.window =usertaketransit()
        self.close()
        self.window.show()

    def viewtransit(self):
        self.window = userviewtransithistory()
        self.close()
        self.window.show()

class adminmanageuser(QMainWindow, Ui_AdminManageUser):
    def __init__(self, parent= None):
        super(adminmanageuser, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.approveButton.clicked.connect(self.approve_button)
        self.declineButton.clicked.connect(self.decline_button)
        self.filterButton.clicked.connect(self.filter_button)

        self.statusComboBox.addItems(["Approved", "Pending", "Declined"])
        self.typeComboBox.addItems(["User", "Visitor", "Employee", "Manager"])


        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()




        managequery = f"select user.username, count(email), user.usertype, user.status from user join email on email.username = user.username group by user.username"
        df = pd.read_sql(managequery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)


    def back_button(self):
        self.window = adminfunctionality()
        self.close()
        self.window.show()
    def approve_button(self, managequery):
        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select user.username, count(email), user.usertype, user.status from user join email on email.username = user.username group by user.username"
        cursor.execute(managequery)

        username = cursor.fetchall()[index][0]

        updateuser = f"update user set status = 'Approved' where Username = '{username}'"
        cursor.execute(updateuser)
        connection.commit()
        self.update()
    def decline_button(self, managequery):
        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select user.username, count(email), user.usertype, user.status from user join email on email.username = user.username group by user.username"
        cursor.execute(managequery)

        fetchedData = cursor.fetchall()
        username = fetchedData[index][0]
        status = fetchedData[index][3]

        # updateuser = f"update user set status = 'Declined' where Username = '{username}'"

        if status != 'Approved':
            updateuser = f"update user set status = 'Declined' where Username = '{username}'"
            cursor.execute(updateuser)
            connection.commit()
        else:
            print("Can't Deny an Approved Account")

    def filter_button(self):

        USERNAME = self.usernameEdit.text()
        Status = self.statusComboBox.currentText()
        userType = self.typeComboBox.currentText()

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        if USERNAME != "":
            filterquery = f"select user.username, count(email), user.usertype, user.status from user join email on email.username = user.username where user.UserType = '{userType}' and Status = '{Status}' group by user.username"
        else:
            filterquery = f"select user.username, count(email), user.usertype, user.status from user join email on email.username = user.username where user.username ='{USERNAME}' and user.UserType = '{userType}' and Status = '{Status}' group by user.username"

        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

class adminmanagesite(QMainWindow, Ui_AdminManageSite):
    def __init__(self, parent= None):
        super(adminmanagesite, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.editButton.clicked.connect(self.edit_button)
        self.deleteButton.clicked.connect(self.delete_button)
        self.createButton.clicked.connect(self.create_button)
        self.filterButton.clicked.connect(self.filter_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select site.name, concat(user.firstname, ' ' , user.lastname), site.openeveryday from site join employee on site.manager = employee.employeeid join user on user.username = employee.username"
        df = pd.read_sql(managequery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
        self.tableView.setSelectionBehavior(QTableView.SelectRows)
        self.tableView.setSelectionMode(QAbstractItemView.SingleSelection)

    def back_button(self):
        self.window = adminfunctionality()
        self.close()
        self.window.show()
    def edit_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        index=0
        for i in userlist:
            index = i.row()
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select site.name, concat(user.firstname, ' ' , user.lastname), site.openeveryday from site join employee on site.manager = employee.employeeid join user on user.username = employee.username"
        cursor.execute(managequery)

        name = cursor.fetchall()[index][0]
        self.window = admineditsite(name)
        self.close()
        self.window.show()
    # def delete_button(self):
    def create_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()
        self.window = admincreatesite()
        self.close()
        self.window.show()

    def delete_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        index=0
        for i in userlist:
            index = i.row()
            print(index)
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select site.name, concat(user.firstname, ' ' , user.lastname), site.openeveryday from site join employee on site.manager = employee.employeeid join user on user.username = employee.username"
        cursor.execute(managequery)

        name = cursor.fetchall()[index][0]
        print(name)
        deletesite = f"delete from site where name = '{name}'"
        cursor.execute(deletesite)
        connection.commit()
        # self.update()
    def filter_button(self):

        # site =
        # manager
        # openeveryday =

        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()

        filterquery = f"select site.name, concat(user.firstname, ' ' , user.lastname), site.openeveryday from site join employee on site.manager = employee.employeeid join user on user.username = employee.username where site.name = 'Inman Park' and concat(user.firstname, ' ' , user.lastname) = 'David Smith' and site.openeveryday = 'YES'"

        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

class admineditsite(QMainWindow, Ui_AdminEditSite):
    def __init__(self, name, parent= None):
        super(admineditsite, self).__init__(parent)
        print(name)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.updateButton.clicked.connect(self.update_button)
        self.name = name

    def update_button(self):
        address = self.addressEdit.text()
        zipc = self.zipcodeEdit.text()
        name = self.nameEdit.text()
        openeveryday = "YES" if self.checkBox.isChecked() else "NO"

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        edit_site = f"update site set Name = '{name}', Address = '{address}', Zipcode = '{zipc}', OpenEveryday = '{openeveryday}' where Name = '{self.name}'"
        cursor.execute(edit_site)
        connection.commit()
        self.window = adminfunctionality()
        self.close()
        self.window.show()


    def back_button(self):
        self.window = adminmanagesite()
        self.close()
        self.window.show()

class admincreatesite(QMainWindow, Ui_AdminCreateSite):
    def __init__(self, parent= None):
        super(admincreatesite, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.updateButton.clicked.connect(self.create_button)


    def back_button(self):
        self.window = adminmanagesite()
        self.close()
        self.window.show()

    def create_button(self):

        sitename = self.nameEdit.text()
        zipc = self.zipcodeEdit.text()
        address = self.addressEdit.text()
        # manager = self.managerComboBox.text()
        openeveryday = "YES" if self.checkBox.isChecked() else "NO"



        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        insert_site = f"INSERT INTO site (Name,Address,Zipcode,OpenEveryday,Manager) VALUES ('{sitename}', '{zipc}', '{address}', '{openeveryday}','000000006')"
        if cursor.execute(insert_site):
            connection.commit()
            self.close()
            self.window = adminmanagesite()
            self.window.show()

class adminmanagetransit(QMainWindow, Ui_AdminManageTransit):
    def __init__(self, parent= None):
        super(adminmanagetransit, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.editButton.clicked.connect(self.edit_button)
        self.deleteButton.clicked.connect(self.delete_button)
        self.createButton.clicked.connect(self.create_button)
        self.filterButton.clicked.connect(self.filter_button)
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        #Need CY to do query
        managetransit = f"select transit.route, transit.transittype, transit.price, count(connect.route) as '# Connected Sites', count(take.route) as '# Transits Logged' from transit join connect on connect.route = transit.route join take on take.route = transit.route group by transit.route"
        df = pd.read_sql(managetransit, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
        self.tableView.setSelectionBehavior(QTableView.SelectRows)
        self.tableView.setSelectionMode(QAbstractItemView.SingleSelection)

    def back_button(self):
        self.window = adminfunctionality()
        self.close()
        self.window.show()
    def edit_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        index=0
        for i in userlist:
            index = i.row()
            print(index)
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select transit.route, transit.transittype, transit.price, count(connect.route) as '# Connected Sites', count(take.route) as '# Transits Logged' from transit join connect on connect.route = transit.route join take on take.route = transit.route group by transit.route"
        cursor.execute(managequery)

        route = cursor.fetchall()[index][0]
        self.window = adminedittransit(route)
        self.close()
        self.window.show()
    # def delete_button(self):
    def create_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()
        self.window = admincreatetransit()
        self.close()
        self.window.show()

    def delete_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        index=0
        for i in userlist:
            index = i.row()
            print(index)
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select transit.route, transit.transittype, transit.price, count(connect.route) as '# Connected Sites', count(take.route) as '# Transits Logged' from transit join connect on connect.route = transit.route join take on take.route = transit.route group by transit.route"
        cursor.execute(managequery)

        route = cursor.fetchall()[index][0]
        print(route)
        deletetransit = f"delete from transit where route = '{route}'"
        cursor.execute(deletetransit)
        connection.commit()

    def filter_button(self):

        route = self.routeEdit.text()
        price_low = self.price1.text()
        price_high = self.price2.text()

        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()
        filterquery = f"select transit.route, transit.transittype, transit.price, count(connect.route) as '# Connected Sites', count(take.route) as '# Transits Logged' from transit join connect on connect.route = transit.route join take on take.route = transit.route where transit.route ='{route}' and transit.price between '{price_low}' and '{price_high}' and transit.transittype = 'BUS' group by transit.route"
        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
        # self.update())

class adminedittransit(QMainWindow, Ui_AdminEditTransit):
    def __init__(self, route, parent= None):
        super(adminedittransit, self).__init__(parent)
        print(route)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        get_transport_type = f"select TransitType from transit where route = '{route}'"
        cursor.execute(get_transport_type)
        transitType = cursor.fetchone()[0]
        print(transitType)

        self.route = route
        self.setupUi(self)
        self.transportTypeLabel.setText(transitType)
        self.BackButton.clicked.connect(self.back_button)
        self.updateButton.clicked.connect(self.update_button)

        get_site = f"select name from site"
        df = pd.read_sql(get_site, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
        self.tableView.setSelectionMode(QAbstractItemView.MultiSelection)

        cursor.execute(get_site)
        full1ListOfNames = cursor.fetchall()
        fullListOfNames = []
        for name in full1ListOfNames:
            fullListOfNames.append(name[0])
        newNameQuery = f"select name from connect where transittype = '{self.transportTypeLabel.text()}' and route = '{self.route}'"
        cursor.execute(newNameQuery)
        selected1Names = cursor.fetchall()
        selectedNames = []
        for name in selected1Names:
            selectedNames.append(name[0])

        for i in range(0, len(fullListOfNames)):
            if fullListOfNames[i] in selectedNames:
                self.tableView.selectRow(i)


    def update_button(self):
        route = self.routeEdit.text()
        price = self.priceEdit.text()

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")

        cursor = connection.cursor()

        userlist = self.tableView.selectionModel().selectedRows()
        listOfIndexes = []
        for i in userlist:
            listOfIndexes.append(i.row())
        print(listOfIndexes)

        nameQuery = f"select name from site"
        cursor.execute(nameQuery)
        fullListOfNames = cursor.fetchall()
        listOfNames = []
        for i in range(0, len(fullListOfNames)):
            if i in listOfIndexes:
                listOfNames.append(fullListOfNames[i][0])

        print(self.transportTypeLabel.text())
        deleteQuery = f"delete from connect where transittype = '{self.transportTypeLabel.text()}' and route = '{self.route}'"
        print(cursor.execute(deleteQuery))
        connection.commit()

        edit_transit = f"update transit set Route = '{route}', Price = '{price}' where Route = '{self.route}'"
        cursor.execute(edit_transit)
        connection.commit()

        for name in listOfNames:
            insertQuery = f"insert into connect (Name, TransitType, Route) values ('{name}', '{self.transportTypeLabel.text()}', '{route}')"
            print(cursor.execute(insertQuery))
            connection.commit()

        self.window = adminmanagetransit()
        self.close()
        self.window.show()

    def back_button(self):
        self.window = adminmanagetransit()
        self.close()
        self.window.show()

class admincreatetransit(QMainWindow, Ui_AdminCreateTransit):
    def __init__(self, parent= None):
        super(admincreatetransit, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.createButton.clicked.connect(self.create_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        get_site = f"select name from site"
        df = pd.read_sql(get_site, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

        transportTypes = ['MARTA', 'Bus', 'Bike']
        self.transportTypeLabel.addItems(transportTypes)




    def back_button(self):
        self.window = adminmanagetransit()
        self.close()
        self.window.show()
    def create_button(self):
        route = self.routeEdit.text()
        price = self.priceEdit.text()

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        create_site = f"insert into transit (Route, TransitType, Price) values ('{route}', '{str(self.transportTypeLabel.currentText())}', '{price}')"
        cursor.execute(create_site)
        connection.commit()

        userlist = self.tableView.selectionModel().selectedRows()
        listOfIndexes = []
        for i in userlist:
            listOfIndexes.append(i.row())

        nameQuery = f"select name from site"
        cursor.execute(nameQuery)
        fullListOfNames = cursor.fetchall()
        listOfNames = []
        for i in range(0, len(fullListOfNames)):
            if i in listOfIndexes:
                listOfNames.append(fullListOfNames[i][0])

        for name in listOfNames:
            insertQuery = f"insert into connect (Name, TransitType, Route) values ('{name}', '{str(self.transportTypeLabel.currentText())}', '{route}')"
            print(cursor.execute(insertQuery))
            connection.commit()




        self.window = adminmanagetransit()
        self.close()
        self.window.show()

#starting from here: create, edit delete, filter buttons need to have correct functionality
class managerfunctionality(QMainWindow, Ui_ManagerFunctionality):
    def __init__(self, parent= None):
        super(managerfunctionality, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.manageeventButton.clicked.connect(self.manage_event_button)
        self.managestaffButton.clicked.connect(self.manage_staff_button)
        self.viewsiteButton.clicked.connect(self.viewsite_button)
        self.manageprofileButton.clicked.connect(self.manageprofile_button)
        self.viewtransithistoryButton.clicked.connect(self.viewtransit)
        self.taketransitButton.clicked.connect(self.taketransit)

    def taketransit(self):
        self.window =usertaketransit()
        self.close()
        self.window.show()

    def viewtransit(self):
        self.window = userviewtransithistory()
        self.close()
        self.window.show()

    def back_button(self):
        # self.window = loginwindow()
        self.close()
        # self.window.show()
        loginwindow.show()
    def manage_event_button(self):
        self.window = managermanageevent()
        self.close()
        self.window.show()
    def manage_staff_button(self):
        self.window = managermanagestaff()
        self.close()
        self.window.show()
    def viewsite_button(self):
        self.window = managersitereport()
        self.close()
        self.window.show()
    def manageprofile_button(self):
        self.window = staffmanageprofile()
        self.close()
        self.window.show()

#create, view/edit / delete needs to be updated queries.
class managermanageevent(QMainWindow, Ui_ManagerManageEvent):
    def __init__(self, parent= None):
        super(managermanageevent, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.viewEditButton.clicked.connect(self.edit_button)
        self.deleteButton.clicked.connect(self.delete_button)
        self.createButton.clicked.connect(self.create_button)
        self.filterButton.clicked.connect(self.filter_button)
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        manageevent = f"select event.name, count(distinct assignto.employeeid, assignto.startdate) as StaffCount, DATEDIFF(event.EndDate, event.StartDate) + 1 as Duration, count(visitevent.username) as TotalVisits, count(visitevent.username)*event.price as TotalRevenue from event join assignto on assignto.name = event.name join visitevent on visitevent.name = event.name group by event.name"
        df = pd.read_sql(manageevent, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

    def back_button(self):
        self.window = managerfunctionality()
        self.close()
        self.window.show()
    def edit_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        index=0
        for i in userlist:
            index = i.row()
            print(index)
        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select event.name, count(distinct assignto.employeeid, assignto.startdate) as StaffCount, DATEDIFF(event.EndDate, event.StartDate) + 1 as Duration from event join assignto on assignto.name = event.name group by event.name"
        cursor.execute(managequery)


        event = cursor.fetchall()[index][0]
        self.window = managereditevent(event)
        self.close()
        self.window.show()
    def create_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()
        self.window = managercreateevent()
        self.close()
        self.window.show()



        event = cursor.fetchall()[index][0]
        self.window = managerviewedit(event)
        self.close()
        self.window.show()
    def create_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()
        self.window = managercreateevent()
        self.close()
        self.window.show()


    def delete_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        index=0
        for i in userlist:
            index = i.row()
            print(index)
        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select transit.route, transit.transittype, transit.price, count(connect.route) as '# Connected Sites', count(take.route) as '# Transits Logged' from transit join connect on connect.route = transit.route join take on take.route = transit.route group by transit.route"
        cursor.execute(managequery)

        route = cursor.fetchall()[index][0]
        print(route)
        deletetransit = f"delete from transit where route = '{route}'"
        cursor.execute(deletetransit)
        connection.commit()
    def filter_button(self):

        name = self.nameEdit.text()
        descriptionKeyword = self.descriptionKeyword.text()
        start_date = self.startDate.text()
        end_date = self.endDate.text()
        duration_low = self.duration1.text()
        duration_high = self.duration2.text()
        totalvisits_low = self.visits1.text()
        totalvisits_high = self.visits2.text()
        revenue_low = self.rev1.text()
        revenue_high = self.rev2.text()



        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()
        filterquery = f"select event.name, count(distinct assignto.employeeid, assignto.startdate) as StaffCount, DATEDIFF(event.EndDate, event.StartDate) + 1 as Duration, count(visitevent.username) as TotalVisits, count(visitevent.username)*event.price as TotalRevenue from event join assignto on assignto.name = event.name join visitevent on visitevent.name = event.name where event.name = '{name}' and DATEDIFF(event.EndDate, event.StartDate) + 1 between '{duration_low}' and '{duration_high}' group by event.name  having count(visitevent.username) between '{totalvisits_low}' and '{totalvisits_high}' and count(visitevent.username)*event.price between '{revenue_low}' and '{revenue_high}'"
        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
        # and contains(Description, '{descriptionKeyword}')

class managereditevent(QMainWindow, Ui_ManagerViewEditEvent):
    def __init__(self, event, parent= None):
        super(managereditevent, self).__init__(parent)
        self.setupUi(self)
        print(event)
        self.event = event
        self.BackButton.clicked.connect(self.back_button)
        self.updateButton.clicked.connect(self.update_button)
        self.filterButton.clicked.connect(self.filter_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        getEvent = f"select * from event where name = '{event}'"
        cursor.execute(getEvent)
        name, startdate, sitename, enddate, price, capacity, description, minStaffReq = cursor.fetchone()
        self.price = price
        self.startdate = startdate
        self.enddate = enddate
        self.nameLabel.setText(name)
        self.priceLabel.setText(str(price))
        startdate = startdate.strftime('%Y-%m-%d')
        enddate = enddate.strftime('%Y-%m-%d')
        self.startLabel.setText(startdate)
        self.endLabel.setText(enddate)
        self.capacityLabel.setText(str(capacity))
        self.minStaffLabel.setText(str(minStaffReq))
        self.textBrowser.setText(description)



        showStaff = f"select concat(user.firstname, ' ', user.lastname) as 'Staff Name' from user join employee on user.username = employee.username join assignto on assignto.employeeid = employee.employeeid where assignto.name = '{event}'"
        df = pd.read_sql(showStaff, connection)
        model = PandasModel(df)
        self.staffTable.setModel(model)

    def back_button(self):
        self.window = managermanageevent()
        self.close()
        self.window.show()


    def update_button(self):
        visitrange1 = self.visitRange1.text()
        visitrange2 = self.visitRange2.text()
        revrange1 = self.revRange1.text()
        revrange2 = self.revRange2.text()

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")

        cursor = connection.cursor()


        userlist = self.tableView.selectionModel().selectedRows()
        listOfIndexes = []
        for i in userlist:
            listOfIndexes.append(i.row())
        print(listOfIndexes)

        self.window = managermanageevent()
        self.close()
        self.window.show()

    def filter_button(self):
        revrange1 = self.revRange1.text()
        revrange2 = self.revRange2.text()
        visitrange1 = self.visitRange1.text()
        visitrange2 = self.visitRange2.text()
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        filterquery = f"select visitevent.date, count(visitevent.username) as 'Daily Visits', count(visitevent.username)*{self.price} as 'Daily Revenue' from visitevent join event where visitevent.date between '{self.startdate}' and '{self.enddate}' group by visitevent.name having count(visitevent.username) between '{visitrange1}' and '{visitrange2}' and count(visitevent.username)*{self.price} between '{revrange1}' and '{revrange2}' "
        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.revenueTable.setModel(model)

class managercreateevent(QMainWindow, Ui_ManagerCreateEvent):
    def __init__(self, parent= None):
        super(managercreateevent, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.createButton.clicked.connect(self.create_button)
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        showStaff = f"select concat(user.firstname, ' ', user.lastname) as 'Staff Name' from user join employee on user.username = employee.username where employee.employeetype = 'Staff'"
        df = pd.read_sql(showStaff, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)


    def back_button(self):
        self.window = managermanageevent()
        self.close()
        self.window.show()

    def create_button(self):
        name = self.nameEdit.text()
        price = self.priceEdit.text()
        capacity = self.capacityEdit.text()
        minStaffRequired = self.minStaffEdit.text()
        startDate = self.startDateEdit.text()
        endDate = self.endDateEdit.text()
        # description = self.textBrowser.text()
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()

        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()


        createQuery = f"select concat(user.firstname, ' ', user.lastname) from employee join user on employee.username = user.username where employee.employeetype = 'Staff'"
        cursor.execute(createQuery)
        # assignStaff = cursor.fetchall()[index][0]

        # print(assignStaff)

        get_site = f"select site.name from site join employee on employee.employeeid = site.manager where employee.username = '{loginwindow.currentuser}'"
        cursor.execute(get_site)
        # siteName = cursor.fetchone()[0]
        # print(siteName)

        insertQuery = f"insert into event (Name, StartDate, SiteName, EndDate, Price, Capacity, Description, minStaffReq) values ('{name}', '{startDate}','Inman Park','{endDate}','{price}','{capacity}', 'Not Applicable', '5')"
        # '{description}','{minStaffRequired}')"
        cursor.execute(insertQuery)
        connection.commit()

        self.window = managermanageevent()
        self.close()
        self.window.show()

class managermanagestaff(QMainWindow, Ui_ManagerManageStaff):
    def __init__(self, parent= None):
        super(managermanagestaff, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.filterButton.clicked.connect(self.filter_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        # manageevent = f"select * from assignto"
        manageevent = f"select concat(user.firstname, ' ', user.lastname) as 'Staff Name', count(assignto.employeeid) as eventShifts from user join employee on employee.username = user.username join assignto on employee.employeeid = assignto.employeeid join site on site.name = assignto.sitename group by employee.employeeid"
        df = pd.read_sql(manageevent, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

    def back_button(self):
        self.window = managerfunctionality()
        self.close()
        self.window.show()
    def filter_button(self):
        lastname = self.nameEdit.text()
        firstname = self.priceEdit.text()
        startdate = self.startDateEdit.text()
        enddate = self.endDateEdit.text()


        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",

                                    db="beltline")
        cursor = connection.cursor()
        filterquery = f"select concat(user.firstname, ' ', user.lastname) as 'Staff Name', count(assignto.employeeid) as eventShifts from user join employee on employee.username = user.username join assignto on employee.employeeid = assignto.employeeid join site on site.name = assignto.sitename where site.name = 'Inman Park' and assignto.startdate between '{startdate}' and '{enddate}' and user.firstname = '{firstname}' and user.lastname ='{lastname}' group by employee.employeeid"
        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
        # and contains(Description, '{descriptionKeyword}')

class managersitereport(QMainWindow, Ui_ManagerSiteReport):
    def __init__(self, parent= None):
        super(managersitereport, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.dailyDetailButton.clicked.connect(self.dailydetail_button)
        self.filterButton.clicked.connect(self.filter_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        manageevent = f"select assignto.startdate, count(distinct assignto.name, assignto.startdate) as EventCount, count(distinct assignto.employeeid, assignto.startdate) as StaffCount, count(visitevent.username) as TotalVisits, count(visitevent.username)*event.price as TotalRevenue from event join assignto on assignto.name = event.name join visitevent on visitevent.name = event.name group by event.name"
        df = pd.read_sql(manageevent, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

    def back_button(self):
        self.window = managerfunctionality()
        self.close()
        self.window.show()

    def dailydetail_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select assignto.startdate, count(distinct assignto.name, assignto.startdate) as EventCount, count(distinct assignto.employeeid, assignto.startdate) as StaffCount, count(visitevent.username) as TotalVisits, count(visitevent.username)*event.price as TotalRevenue from event join assignto on assignto.name = event.name join visitevent on visitevent.name = event.name group by event.name"
        cursor.execute(managequery)
        values = cursor.fetchall()[index]

        self.window = managerdailydetail(values)
        self.close()
        self.window.show()

    def filter_button(self):
        startdate = self.startDateEdit.text()
        enddate = self.endDateEdit.text()


        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",

                                    db="beltline")
        cursor = connection.cursor()
        filterquery = f"select assignto.startdate, count(distinct assignto.name, assignto.startdate) as EventCount, count(distinct assignto.employeeid, assignto.startdate) as StaffCount, count(visitevent.username) as TotalVisits, count(visitevent.username)*event.price as TotalRevenue from event join assignto on assignto.name = event.name join visitevent on visitevent.name = event.name group by event.name"
        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
        # and contains(Description, '{descriptionKeyword}')

#need to update the query
class managerdailydetail(QMainWindow, Ui_ManagerDailyDetail):
    def __init__(self, values, parent= None):
        super(managerdailydetail, self).__init__(parent)
        self.setupUi(self)
        self.values = values
        print(values)
        self.BackButton.clicked.connect(self.back_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        manageevent = f"select * from employee"
        df = pd.read_sql(manageevent, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

    def back_button(self):
        self.window = managerfunctionality()
        self.close()
        self.window.show()

class stafffunctionality(QMainWindow, Ui_StaffFunctionality):
    def __init__(self, parent= None):
        super(stafffunctionality, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.manageProfileButton.clicked.connect(self.manageprofile_button)
        self.viewScheduleButton.clicked.connect(self.viewschedule_button)
        self.viewTransitButton.clicked.connect(self.viewtransit)
        self.takeTransitButton.clicked.connect(self.taketransit)

    def taketransit(self):
        self.window =usertaketransit()
        self.close()
        self.window.show()

    def viewtransit(self):
        self.window = userviewtransithistory()
        self.close()
        self.window.show()
    def back_button(self):
        self.close()
        loginwindow.show()
    def manageprofile_button(self):
        self.window = staffmanageprofile()
        self.close()
        self.window.show()
    def viewschedule_button(self):
        self.window = staffviewschedule()
        self.close()
        self.window.show()

class staffmanageprofile(QMainWindow, Ui_EmployeeManageProfile):
    def __init__(self, parent= None):
        super(staffmanageprofile, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.updateButton.clicked.connect(self.update_button)
        self.BackButton_2.clicked.connect(self.add_button)
        self.BackButton_3.clicked.connect(self.remove_button)

        # loginwindow.currentuser
        # BACK BUTTON 2 IS ADD
        # BACK BUTTON 3 IS REMOVE
        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()

        managerQuery = f"select employeetype from employee where username = '{loginwindow.currentuser}'"
        cursor.execute(managerQuery)
        fetchAll = cursor.fetchall()
        role = fetchAll[0][0]
        query = None
        if role == 'Manager':
            #code
            query = f"select concat(employee.address, ', ', employee.city, ', ', employee.state, ' ', employee.zipcode) as ADDRESS, employee.employeeid, user.username, site.name  from employee join user on employee.username = user.username join site on employee.employeeid = site.manager where user.username = '{loginwindow.currentuser}'"
            cursor.execute(query)
            fetchAll = cursor.fetchall()
            try:
                self.addressLabel.setText(fetchAll[0][0])
                self.idLabel.setText(fetchAll[0][1])
                self.siteNameLabel.setText(fetchAll[0][3])
                self.usernameLabel.setText(fetchAll[0][2])
            except:
                print('fail')
        else:
            #code
            query = f"select concat(employee.address, ', ', employee.city, ', ', employee.state, ' ', employee.zipcode) as ADDRESS, employee.employeeid, user.username from employee join user on employee.username = user.username where user.username = '{loginwindow.currentuser}'"
            cursor.execute(query)
            fetchAll = cursor.fetchall()
            try:
                self.addressLabel.setText(fetchAll[0][0])
                self.idLabel.setText(fetchAll[0][1])
                self.usernameLabel.setText(fetchAll[0][2])
            except:
                print('fail')

    def back_button(self):
        self.close()
        loginwindow.show()
    def update_button(self):
        firstname = self.firstNameEdit.text()
        lastname = self.lastNameEdit.text()
        phone = self.phoneEdit.text()
        email =  self.emailEdit.text()

        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()
        # edit_profile = f"update employee set firstName = '{firstname}, lastName = '{lastname}', phone = '{phone}' where username = '{loginwindow.currentuser}'"
        # cursor.execute(edit_profile)
        # connection.commit()
        update1 = f"update user set firstname = '{firstname}', lastname = '{lastname}' where username = '{loginwindow.currentuser}'"
        update2 = f"update employee set phone = '{phone}' where username = '{loginwindow.currentuser}'"
        cursor.execute(update1)
        connection.commit()
        cursor.execute(update2)
        connection.commit()

        if self.visitorCheckBox.isChecked():
            query = f"update user set usertype = 'Employee-Visitor' where username = '{loginwindow.currentuser}'"
            cursor.execute(query)
            connection.commit()
        else:
            query = f"update user set usertype = 'Employee' where username = '{loginwindow.currentuser}'"
            cursor.execute(query)
            connection.commit()


        self.window = adminfunctionality()
        self.close()
        self.window.show()

    def add_button(self):
        #code
        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()
        try:
            addQ = f"insert into email (Username, Email) values ('{loginwindow.currentuser}', '{self.emailEdit.text()}')"
            cursor.execute(addQ)
            connection.commit()
        except:
            print('add failed')


    def remove_button(self):
        #code
        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()
        try:
            deleteQ = f"delete from email where email = '{self.emailEdit.text()}' and username = '{loginwindow.currentuser}'"
            cursor.execute(deleteQ)
            connection.commit()
        except:
            print('delete failed!')

class staffviewschedule(QMainWindow, Ui_StaffViewSchedule):
    def __init__(self, parent= None):
        super(staffviewschedule, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.viewEventButton.clicked.connect(self.view_button)
        self.filterButton.clicked.connect(self.filter_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()
        manageevent = f"select event.name as 'Event Name', event.sitename as 'Site Name', event.StartDate, event.EndDate, count(distinct assignto.EmployeeID, assignto.Name) as 'Staff Count' from event join assignto on event.sitename = assignto.sitename group by assignto.sitename"
        df = pd.read_sql(manageevent, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
        self.tableView.setSelectionBehavior(QTableView.SelectRows)
        self.tableView.setSelectionMode(QAbstractItemView.SingleSelection)


    def back_button(self):
        self.window = stafffunctionality()
        self.close()
        self.window.show()
    def view_button(self):
        userlist = self.tableView.selectionModel().selectedRows()
        for i in userlist:
            index = i.row()
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        managequery = f"select event.name, event.sitename as 'Site Name', event.StartDate, event.EndDate, count(distinct assignto.EmployeeID, assignto.Name) as 'Staff Count' from event join assignto on event.sitename = assignto.sitename group by assignto.sitename"
        cursor.execute(managequery)
        values = cursor.fetchall()[index]
        self.window = staffeventdetail(values)
        self.close()
        self.window.show()
    def filter_button(self):
        eventname = self.eventNameEdit.text()
        description = self.descriptionEdit.text()
        startdate = self.startDate.text()
        enddate = self.endDate.text()



        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        filterquery = f"select event.name, event.sitename as 'Site Name', event.StartDate, event.EndDate, count(distinct assignto.EmployeeID, assignto.Name) as 'Staff Count' from event join assignto on event.sitename = assignto.sitename where event.name = '{eventname}' and event.startdate >= '{startdate}' and event.enddate <= '{enddate}' and event.description like '%{description}%' group by assignto.sitename"
        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

class staffeventdetail(QMainWindow, Ui_StaffEventDetail):
    def __init__(self, values, parent= None):
        super(staffeventdetail, self).__init__(parent)
        self.setupUi(self)
        self.eventLabel.setText(values[0])
        self.label_5.setText(values[1])
        startdate = values[2].strftime('%Y%m-%d')
        enddate = values[3].strftime('%Y-%m-%d')
        self.startDateLabel.setText(startdate)
        self.endDateLabel.setText(enddate)
        self.durationLabel.setText(str(abs((values[2] - values[3]).days)))
        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        detailquery = f"select price, capacity, description from event where name = '{values[0]}'"
        cursor.execute(detailquery)
        values_2 = cursor.fetchone()
        self.capacityLabel.setText(str(values_2[1]))
        self.priceLabel.setText(str(values_2[0]))
        self.textBrowser.setText(values_2[2])

        staffquery = f"select concat(user.firstname, ' ', user.lastname) as 'Staff Name' from user join employee on user.username = employee.username join assignto on assignto.employeeid = employee.employeeid where assignto.name = '{values[0]}'"
        df = pd.read_sql(staffquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)
        print(values)
        self.backButton.clicked.connect(self.back_button)
        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()

    def back_button(self):
        self.window = staffviewschedule()
        self.close()
        self.window.show()

class visitorfunctionality(QMainWindow, Ui_VisitorFunctionality):
    def __init__(self, parent= None):
        super(visitorfunctionality, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.exploreEventButton.clicked.connect(self.exploreevent_button)
        self.exploreSiteButton.clicked.connect(self.exploresite_button)
        self.viewVisitButton.clicked.connect(self.viewTransit_button)
        self.viewTransitButton.clicked.connect(self.viewtransit)
        self.takeTransitButton.clicked.connect(self.taketransit)


    def taketransit(self):
        self.window =usertaketransit()
        self.close()
        self.window.show()

    def viewtransit(self):
        self.window = userviewtransithistory()
        self.close()
        self.window.show()

    def back_button(self):
        self.close()
        loginwindow.show()
    def exploreevent_button(self):
        self.window = visitorexploreevent()
        self.close()
        self.window.show()
    def exploresite_button(self):
        self.window = visitorexploresite()
        self.close()
        self.window.show()
    def viewTransit_button(self):
        self.window = visitorvisithistory()
        self.close()
        self.window.show()
#update query
class visitorexploreevent(QMainWindow, Ui_VisitorExploreEvent):
    def __init__(self, parent= None):
        super(visitorexploreevent, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.eventDetailButton.clicked.connect(self.dailydetail_button)
        self.filterButton.clicked.connect(self.filter_button)


        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        manageevent = f"select event.name, event.SiteName as 'Site Name', event.price as 'Ticket Price', event.capacity-count(distinct visitevent.name) as 'Tickets Remaining', count(visitevent.username) as TotalVisits, count(distinct visitevent.username, visitevent.sitename) as 'My Visits' from event join visitevent on visitevent.name = event.name group by event.name"
        # select event.name as 'Event Name', event.SiteName as 'Site Name', event.price as 'Ticket Price', event.capacity - count(distinct visitevent.name) as 'Tickets Remaining', count(something) as 'My Visits' from event join visitevent on visitevent.name = event.name group by event.name
        df = pd.read_sql(manageevent, connection)
        model = PandasModel(df)
        self.dataTable.setModel(model)

    def back_button(self):
        self.window = visitorfunctionality()
        self.close()
        self.window.show()

    def dailydetail_button(self):
        self.window = visitoreventdetail()
        self.close()
        self.window.show()

    def filter_button(self):
        eventname = self.nameEdit.text()
        description = self.descriptionEdit.text()
        startdate = self.startDate.text()
        enddate = self.endDate.text()



        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        filterquery = f"select event.name, event.SiteName as 'Site Name', event.price as 'Ticket Price', event.capacity-count(distinct visitevent.name) as 'Tickets Remaining', count(visitevent.username) as TotalVisits, count(distinct visitevent.username, visitevent.sitename) as 'My Visits' from event join visitevent on visitevent.name = event.name where visitevent.username = '{loginwindow.currentuser}' and event.name = '{eventname}' group by event.name event.startdate >= '{startdate}' and event.enddate <= '{enddate}' and event.description like '%{description}%'"
        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

#need to create log visit button
class visitoreventdetail(QMainWindow, Ui_VisitorEventDetail):
    def __init__(self, parent= None):
        super(visitoreventdetail, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        # cursor = connection.cursor()
        # manageevent = f"select * from employee"
        # df = pd.read_sql(manageevent, connection)
        # model = PandasModel(df)
        # self.tableView.setModel(model)

    def back_button(self):
        self.window = visitorexploreevent()
        self.close()
        self.window.show()

class visitorexploresite(QMainWindow, Ui_VisitorExploreSite):
    def __init__(self, parent= None):
        super(visitorexploresite, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.transitDetailButton.clicked.connect(self.transitdetail_button)
        self.siteDetailButton.clicked.connect(self.sitedetail_button)
        self.filterButton.clicked.connect(self.filter_button)


        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        cursor = connection.cursor()
        manageevent = f"select event.name, event.SiteName as 'Site Name', event.price as 'Ticket Price', event.capacity-count(distinct visitevent.name) as 'Tickets Remaining', count(visitevent.username) as TotalVisits, count(distinct visitevent.username, visitevent.sitename) as 'My Visits' from event join visitevent on visitevent.name = event.name group by event.name"
        # select event.name as 'Event Name', event.SiteName as 'Site Name', event.price as 'Ticket Price', event.capacity - count(distinct visitevent.name) as 'Tickets Remaining', count(something) as 'My Visits' from event join visitevent on visitevent.name = event.name group by event.name
        df = pd.read_sql(manageevent, connection)
        model = PandasModel(df)
        self.dataTable.setModel(model)

    def back_button(self):
        self.window = visitorfunctionality()
        self.close()
        self.window.show()

    def transitdetail_button(self):
        self.window = visitortransitdetail()
        self.close()
        self.window.show()

    def sitedetail_button(self):
        self.window = visitorsitedetail()
        self.close()
        self.window.show()

    def filter_button(self):
        eventname = self.nameEdit.text()
        description = self.descriptionEdit.text()
        startdate = self.startDate.text()
        enddate = self.endDate.text()



        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        filterquery = f"select site.name as 'Site Name', event.capacity - count(distinct visitevent.name) as 'Tickets Remaining', count(distinct visitevent.username, visitevent.sitename) as 'My Visits' from event join visitevent on visitevent.name = event.name where visitevent.username = '{loginwindow.currentuser}' and site.name = '{sitename}' group by event.name event.startdate >= '{startdate}' and event.enddate <= '{enddate}' and event.description like '%{description}%'"
        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

class visitorsitedetail(QMainWindow, Ui_VisitorSiteDetail):
    def __init__(self, parent= None):
        super(visitorsitedetail, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",

                                    password="12345678",


                                    db="beltline")
        # cursor = connection.cursor()
        # manageevent = f"select * from employee"
        # df = pd.read_sql(manageevent, connection)
        # model = PandasModel(df)
        # self.tableView.setModel(model)

    def back_button(self):
        self.window = visitorexploresite()
        self.close()
        self.window.show()

class visitortransitdetail(QMainWindow, Ui_VisitorTransitDetail):
    def __init__(self, parent= None):
        super(visitortransitdetail, self).__init__(parent)

        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        manageevent = f"select Take.Route, Take.TransitType as 'Transport Type', Transit.price, count(connect.route) from Take join Transit on Transit.route = Take.Route join connect on connect.route = Take.Route group by tranit.TransitType"
        df = pd.read_sql(manageevent, connection)
        model = PandasModel(df)
        self.tableView.setModel(model)

    def back_button(self):
        self.window = visitorexploresite()
        self.close()
        self.window.show()

class visitorvisithistory(QMainWindow, Ui_VisitorVisitHistory):
    def __init__(self, parent= None):
        super(visitorvisithistory, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.filterButton.clicked.connect(self.filter_button)

        connection = pymysql.connect(host="localhost",
                                    user="root",
                                    password="12345678",
                                    db="beltline")
        cursor = connection.cursor()
        filterquery = f"select visitsite.date, event.name, visitsite.name, event.price from visitsite join event on visitsite.name = event.sitename"

        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.dataTable.setModel(model)


        siteQuery = f"select name from site"
        cursor.execute(siteQuery)
        fetchAll = cursor.fetchall()
        siteList = []
        for site in fetchAll:
            siteList.append(site[0])
        self.sitesComboBox.addItems(siteList)


    def back_button(self):
        self.window = visitorfunctionality()
        self.close()
        self.window.show()

    def filter_button(self):
        sites = self.sitesComboBox.currentText()
        event = self.eventEdit.text()
        # startdate = self.startdate.text()
        # enddate = self.enddate.text()

        connection = pymysql.connect(host="localhost",
                                        user="root",
                                        password="12345678",
                                        db="beltline")
        filterquery = f"select visitsite.date, event.name, visitsite.name, event.price from visitsite join event on visitsite.name = event.sitename where visitsite.username = '{loginwindow.currentuser}' and event.sitename = '{sites}' and event.name = '{event}'"
        df = pd.read_sql(filterquery, connection)
        model = PandasModel(df)
        self.dataTable.setModel(model)

class adminvisitorfunctionality(QMainWindow, Ui_AdminVisitorFunctionality):
    def __init__(self, parent= None):
        super(adminvisitorfunctionality, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.ManageSiteButton.clicked.connect(self.managesite_button)
        self.ManageUserButton.clicked.connect(self.manageuser_button)
        self.ManageTransitButton.clicked.connect(self.managetransit_button)
        self.manageProfileButton.clicked.connect(self.manageprofile_button)
        self.ViewTransitButton.clicked.connect(self.viewtransit)
        self.TakeTransitButton.clicked.connect(self.taketransit)
        self.exploreEventButton.clicked.connect(self.exploreevent_button)
        self.exploreSiteButton.clicked.connect(self.exploresite_button)
        self.viewVisitButton.clicked.connect(self.viewTransit_button)


    def taketransit(self):
        self.window =usertaketransit()
        self.close()
        self.window.show()

    def viewtransit(self):
        self.window = userviewtransithistory()
        self.close()
        self.window.show()

    def back_button(self):
        self.close()
        loginwindow.show()
    def exploreevent_button(self):
        self.window = visitorexploreevent()
        self.close()
        self.window.show()
    def exploresite_button(self):
        self.window = visitorexploresite()
        self.close()
        self.window.show()
    def viewTransit_button(self):
        self.window = visitorvisithistory()
        self.close()
        self.window.show()

    def back_button(self):
        # self.window = loginwindow()
        self.close()
        # self.window.show()
        loginwindow.show()

    def managesite_button(self):
        self.window = adminmanagesite()
        self.close()
        self.window.show()
    def manageuser_button(self):
        self.window = adminmanageuser()
        self.close()
        self.window.show()
    def managetransit_button(self):
        self.window = adminmanagetransit()
        self.close()
        self.window.show()
    def manageprofile_button(self):
        self.window = staffmanageprofile()
        self.close()
        self.window.show()
    def taketransit(self):
        self.window =usertaketransit()
        self.close()
        self.window.show()

    def viewtransit(self):
        self.window = userviewtransithistory()
        self.close()
        self.window.show()

class staffvisitorfunctionality(QMainWindow, Ui_StaffVisitorFunctionality):
    def __init__(self, parent= None):
        super(staffvisitorfunctionality, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.manageProfileButton.clicked.connect(self.manageprofile_button)
        self.viewScheduleButton.clicked.connect(self.viewschedule_button)
        self.exploreEventButton.clicked.connect(self.exploreevent_button)
        self.exploreSiteButton.clicked.connect(self.exploresite_button)
        self.viewVisitButton.clicked.connect(self.viewTransit_button)
        self.viewTransitButton.clicked.connect(self.viewtransit)
        self.takeTransitButton.clicked.connect(self.taketransit)


    def taketransit(self):
        self.window =usertaketransit()
        self.close()
        self.window.show()

    def viewtransit(self):
        self.window = userviewtransithistory()
        self.close()
        self.window.show()

    def back_button(self):
        self.close()
        loginwindow.show()

    def exploreevent_button(self):
        self.window = visitorexploreevent()
        self.close()
        self.window.show()

    def exploresite_button(self):
        self.window = visitorexploresite()
        self.close()
        self.window.show()
    def viewTransit_button(self):
        self.window = visitorvisithistory()
        self.close()
        self.window.show()

    def manageprofile_button(self):
        self.window = staffmanageprofile()
        self.close()
        self.window.show()
    def viewschedule_button(self):
        self.window = staffviewschedule()
        self.close()
        self.window.show()

class managervisitorfunctionality(QMainWindow, Ui_ManagerVisitorFunctionality):
    def __init__(self, parent= None):
        super(managervisitorfunctionality, self).__init__(parent)
        self.setupUi(self)
        self.BackButton.clicked.connect(self.back_button)
        self.exploreEventButton.clicked.connect(self.exploreevent_button)
        self.exploreSiteButton.clicked.connect(self.exploresite_button)
        self.viewVisitButton.clicked.connect(self.viewTransit_button)
        self.viewTransitButton.clicked.connect(self.viewtransit)
        self.takeTransitButton.clicked.connect(self.taketransit)
        self.manageEventButton.clicked.connect(self.manage_event_button)
        self.manageStaffButton.clicked.connect(self.manage_staff_button)
        self.viewSiteButton.clicked.connect(self.viewsite_button)
        self.manageProfileButton.clicked.connect(self.manageprofile_button)


    def taketransit(self):
        self.window =usertaketransit()
        self.close()
        self.window.show()

    def viewtransit(self):
        self.window = userviewtransithistory()
        self.close()
        self.window.show()

    def back_button(self):
        self.close()
        loginwindow.show()
    def exploreevent_button(self):
        self.window = visitorexploreevent()
        self.close()
        self.window.show()
    def exploresite_button(self):
        self.window = visitorexploresite()
        self.close()
        self.window.show()
    def viewTransit_button(self):
        self.window = visitorvisithistory()
        self.close()
        self.window.show()
    def manage_event_button(self):
        self.window = managermanageevent()
        self.close()
        self.window.show()
    def manage_staff_button(self):
        self.window = managermanagestaff()
        self.close()
        self.window.show()
    def viewsite_button(self):
        self.window = managersitereport()
        self.close()
        self.window.show()

    def manageprofile_button(self):
        self.window = staffmanageprofile()
        self.close()
        self.window.show()


if __name__=='__main__':
    app = QApplication(sys.argv)
    loginwindow = loginwindow()
    loginwindow.show()
    app.exec_()