
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import QTableWidgetItem
import mysql.connector


class Ui_MainWindow(object):
    def __init__(self):
        self.mydb = mysql.connector.connect(host="localhost",user="root",passwd="root", database="courier"        )
        self.mycursor = self.mydb.cursor()
        #self.mycursor.execute("SHOW DATABASES"
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.tabWidget = QtWidgets.QTabWidget(self.centralwidget)
        self.tabWidget.setGeometry(QtCore.QRect(80, 40, 651, 481))
        sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Maximum, QtWidgets.QSizePolicy.Maximum)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.tabWidget.sizePolicy().hasHeightForWidth())
        self.tabWidget.setSizePolicy(sizePolicy)
        self.tabWidget.setObjectName("tabWidget")
        self.tab = QtWidgets.QWidget()
        self.tab.setEnabled(True)
        self.tab.setObjectName("tab")
        self.label_8 = QtWidgets.QLabel(self.tab)
        self.label_8.setGeometry(QtCore.QRect(340, 60, 57, 16))
        self.label_8.setObjectName("label_8")
        self.label_7 = QtWidgets.QLabel(self.tab)
        self.label_7.setGeometry(QtCore.QRect(340, 40, 57, 16))
        self.label_7.setObjectName("label_7")
        self.label_6 = QtWidgets.QLabel(self.tab)
        self.label_6.setGeometry(QtCore.QRect(340, 100, 71, 20))
        self.label_6.setObjectName("label_6")
        self.label = QtWidgets.QLabel(self.tab)
        self.label.setGeometry(QtCore.QRect(30, 20, 57, 16))
        self.label.setObjectName("label")
        self.add_order_sentfrom = QtWidgets.QTextEdit(self.tab)
        self.add_order_sentfrom.setGeometry(QtCore.QRect(420, 20, 201, 21))
        self.add_order_sentfrom.setObjectName("add_order_sentfrom")
        self.add_order_sentto = QtWidgets.QTextEdit(self.tab)
        self.add_order_sentto.setGeometry(QtCore.QRect(420, 40, 201, 21))
        self.add_order_sentto.setObjectName("add_order_sentto")
        self.label_2 = QtWidgets.QLabel(self.tab)
        self.label_2.setGeometry(QtCore.QRect(30, 40, 57, 16))
        self.label_2.setText("")
        self.label_2.setObjectName("label_2")
        self.add_order_weight = QtWidgets.QTextEdit(self.tab)
        self.add_order_weight.setGeometry(QtCore.QRect(420, 100, 111, 21))
        self.add_order_weight.setObjectName("add_order_weight")
        self.add_order_staff = QtWidgets.QTextEdit(self.tab)
        self.add_order_staff.setGeometry(QtCore.QRect(420, 80, 201, 21))
        self.add_order_staff.setObjectName("add_order_staff")
        self.add_order_pincode = QtWidgets.QTextEdit(self.tab)
        self.add_order_pincode.setGeometry(QtCore.QRect(110, 40, 111, 21))
        self.add_order_pincode.setObjectName("add_order_pincode")
        self.add_order_contact = QtWidgets.QTextEdit(self.tab)
        self.add_order_contact.setGeometry(QtCore.QRect(420, 60, 201, 21))
        self.add_order_contact.setObjectName("add_order_contact")
        self.label_4 = QtWidgets.QLabel(self.tab)
        self.label_4.setGeometry(QtCore.QRect(340, 20, 57, 16))
        self.label_4.setObjectName("label_4")
        self.label_3 = QtWidgets.QLabel(self.tab)
        self.label_3.setGeometry(QtCore.QRect(30, 40, 57, 16))
        self.label_3.setObjectName("label_3")
        self.add_order_uid = QtWidgets.QTextEdit(self.tab)
        self.add_order_uid.setGeometry(QtCore.QRect(110, 60, 111, 21))
        self.add_order_uid.setObjectName("add_order_uid")
        self.label_10 = QtWidgets.QLabel(self.tab)
        self.label_10.setGeometry(QtCore.QRect(340, 80, 57, 16))
        self.label_10.setObjectName("label_10")
        self.label_5 = QtWidgets.QLabel(self.tab)
        self.label_5.setGeometry(QtCore.QRect(30, 60, 57, 16))
        self.label_5.setObjectName("label_5")
        self.add_order_name = QtWidgets.QTextEdit(self.tab)
        self.add_order_name.setGeometry(QtCore.QRect(110, 20, 201, 21))
        self.add_order_name.setObjectName("add_order_name")
        self.add_order_button = QtWidgets.QPushButton(self.tab)
        self.add_order_button.setGeometry(QtCore.QRect(110, 110, 90, 32))
        #self.add_order_button.setAcceptDrops(False)
        self.add_order_button.setAutoDefault(False)
        self.add_order_button.setFlat(False)
        self.add_order_button.setObjectName("add_order_button")
        self.add_order_button.clicked.connect(self.add_order)
        self.tableWidget = QtWidgets.QTableWidget(self.tab)
        self.tableWidget.setGeometry(QtCore.QRect(20, 150, 611, 271))
        self.tableWidget.setObjectName("tableWidget")
        self.tableWidget.setHorizontalHeaderLabels(["uid","name","sentfrom","sentto"])
        self.tableWidget.setColumnCount(4)
        self.tableWidget.setRowCount(8)
        icon = QtGui.QIcon.fromTheme("Dark")
        self.tabWidget.addTab(self.tab, icon, "")
        self.tab_3 = QtWidgets.QWidget()
        self.tab_3.setObjectName("tab_3")
        self.label_9 = QtWidgets.QLabel(self.tab_3)
        self.label_9.setGeometry(QtCore.QRect(40, 60, 57, 16))
        self.label_9.setObjectName("label_9")
        self.label_11 = QtWidgets.QLabel(self.tab_3)
        self.label_11.setGeometry(QtCore.QRect(40, 80, 57, 16))
        self.label_11.setObjectName("label_11")
        self.track_name = QtWidgets.QTextEdit(self.tab_3)
        self.track_name.setGeometry(QtCore.QRect(90, 60, 191, 21))
        self.track_name.setObjectName("track_name")
        self.track_uid = QtWidgets.QTextEdit(self.tab_3)
        self.track_uid.setGeometry(QtCore.QRect(90, 80, 191, 21))
        self.track_uid.setObjectName("track_uid")
        self.track_button = QtWidgets.QPushButton(self.tab_3)
        self.track_button.setGeometry(QtCore.QRect(330, 60, 90, 32))
        self.track_button.setObjectName("track_button")
        self.track_button.clicked.connect(self.track_order)
        self.label_12 = QtWidgets.QLabel(self.tab_3)
        self.label_12.setGeometry(QtCore.QRect(170, 150, 57, 16))
        self.label_12.setObjectName("label_12")
        self.label_13 = QtWidgets.QLabel(self.tab_3)
        self.label_13.setGeometry(QtCore.QRect(170, 170, 91, 16))
        self.label_13.setObjectName("label_13")
        self.label_14 = QtWidgets.QLabel(self.tab_3)
        self.label_14.setGeometry(QtCore.QRect(170, 190, 101, 16))
        self.label_14.setObjectName("label_14")
        self.track_progress = QtWidgets.QProgressBar(self.tab_3)
        self.track_progress.setGeometry(QtCore.QRect(210, 220, 172, 23))
        self.track_progress.setProperty("value", 0)
        self.track_progress.setObjectName("track_progress")
        self.track_status = QtWidgets.QLabel(self.tab_3)
        self.track_status.setGeometry(QtCore.QRect(320, 150, 181, 16))
        self.track_status.setObjectName("track_status")
        self.track_lastloc = QtWidgets.QLabel(self.tab_3)
        self.track_lastloc.setGeometry(QtCore.QRect(320, 170, 171, 16))
        self.track_lastloc.setObjectName("track_lastloc")
        self.track_currentloc = QtWidgets.QLabel(self.tab_3)
        self.track_currentloc.setGeometry(QtCore.QRect(320, 190, 181, 16))
        self.track_currentloc.setObjectName("track_currentloc")
        self.tabWidget.addTab(self.tab_3, "")
        self.tab_4 = QtWidgets.QWidget()
        self.tab_4.setObjectName("tab_4")
        # self.label_15 = QtWidgets.QLabel(self.tab_4)
        # self.label_15.setGeometry(QtCore.QRect(80, 40, 111, 16))
        # self.label_15.setObjectName("label_15")
        # self.textEdit = QtWidgets.QTextEdit(self.tab_4)
        # self.textEdit.setGeometry(QtCore.QRect(180, 40, 151, 21))
        # self.textEdit.setObjectName(s"textEdit")
        # self.pushButton = QtWidgets.QPushButton(self.tab_4)
        # self.pushButton.setGeometry(QtCore.QRect(340, 30, 90, 32))
        # self.pushButton.setObjectName("pushButton")
        # self.tableView = QtWidgets.QTableView(self.tab_4)
        # self.tableView.setGeometry(QtCore.QRect(80, 90, 291, 321))
        # self.tableView.setObjectName("tableView")
        # self.tabWidget.addTab(self.tab_4, "")
        self.label_15 = QtWidgets.QLabel(self.tab_4)
        self.label_15.setGeometry(QtCore.QRect(40, 40, 57, 16))
        self.label_15.setObjectName("label_15")
        self.label_16 = QtWidgets.QLabel(self.tab_4)
        self.label_16.setGeometry(QtCore.QRect(40, 60, 101, 16))
        self.label_16.setObjectName("label_16")
        self.label_17 = QtWidgets.QLabel(self.tab_4)
        self.label_17.setGeometry(QtCore.QRect(40, 80, 57, 16))
        self.label_17.setObjectName("label_17")
        self.label_18 = QtWidgets.QLabel(self.tab_4)
        self.label_18.setGeometry(QtCore.QRect(40, 100, 57, 16))
        self.label_18.setObjectName("label_18")
        self.update_uid = QtWidgets.QTextEdit(self.tab_4)
        self.update_uid.setGeometry(QtCore.QRect(160, 40, 141, 21))
        self.update_uid.setObjectName("update_uid")
        self.update_currentlocation = QtWidgets.QTextEdit(self.tab_4)
        self.update_currentlocation.setGeometry(QtCore.QRect(160, 60, 141, 21))
        self.update_currentlocation.setObjectName("update_currentlocation")
        self.update_progress = QtWidgets.QTextEdit(self.tab_4)
        self.update_progress.setGeometry(QtCore.QRect(160, 80, 141, 21))
        self.update_progress.setObjectName("update_progress")
        self.update_status = QtWidgets.QTextEdit(self.tab_4)
        self.update_status.setGeometry(QtCore.QRect(160, 100, 104, 21))
        self.update_status.setObjectName("update_status")
        self.update_button = QtWidgets.QPushButton(self.tab_4)
        self.update_button.setGeometry(QtCore.QRect(400, 100, 90, 32))
        self.update_button.setObjectName("update_button")
        self.update_button.clicked.connect(self.update_order)
        self.update_table = QtWidgets.QTableWidget(self.tab_4)
        self.update_table.setGeometry(QtCore.QRect(150, 140, 351, 281))
        self.update_table.setObjectName("update_table")
        self.update_table.setColumnCount(0)
        self.update_table.setRowCount(0)
        self.tabWidget.addTab(self.tab_4, "")
        #self.tabWidget.addTab(self.tab_4, "")
        self.tab_2 = QtWidgets.QWidget()
        self.tab_2.setObjectName("tab_2")
        self.label_19 = QtWidgets.QLabel(self.tab_2)
        self.label_19.setGeometry(QtCore.QRect(30, 50, 131, 16))
        self.label_19.setObjectName("label_19")
        self.label_20 = QtWidgets.QLabel(self.tab_2)
        self.label_20.setGeometry(QtCore.QRect(360, 50, 81, 16))
        self.label_20.setText("")
        self.label_20.setObjectName("label_20")
        self.details_customername = QtWidgets.QTextEdit(self.tab_2)
        self.details_customername.setGeometry(QtCore.QRect(140, 50, 104, 21))
        self.details_customername.setObjectName("details_customername")
        self.details_pushbutton = QtWidgets.QPushButton(self.tab_2)
        self.details_pushbutton.setGeometry(QtCore.QRect(280, 40, 90, 32))
        self.details_pushbutton.setObjectName("details_pushbutton")
        self.details_pushbutton.clicked.connect(self.get_details)
        self.tableWidget_2 = QtWidgets.QTableWidget(self.tab_2)
        self.tableWidget_2.setGeometry(QtCore.QRect(30, 90, 581, 341))
        self.tableWidget_2.setObjectName("tableWidget_2")
        self.tableWidget_2.setColumnCount(5)
        self.tableWidget_2.setRowCount(10)
        self.tabWidget.addTab(self.tab_2, "")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 28))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        self.tabWidget.setCurrentIndex(0)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)
        self.updateordertable()

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.label_8.setText(_translate("MainWindow", "Contact "))
        self.label_7.setText(_translate("MainWindow", "Sent to"))
        self.label_6.setText(_translate("MainWindow", "Pkg Weigth"))
        self.label.setText(_translate("MainWindow", "Name"))
        self.label_4.setText(_translate("MainWindow", "Sent from"))
        self.label_3.setText(_translate("MainWindow", "Pincode"))
        self.label_10.setText(_translate("MainWindow", "Staff"))
        self.label_5.setText(_translate("MainWindow", "UID"))
        self.add_order_button.setText(_translate("MainWindow", "Add Order"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab), _translate("MainWindow", "Add Order"))
        self.label_9.setText(_translate("MainWindow", "Name"))
        self.label_11.setText(_translate("MainWindow", "UID"))
        self.track_button.setText(_translate("MainWindow", "Track!!"))
        self.label_12.setText(_translate("MainWindow", "Status"))
        self.label_13.setText(_translate("MainWindow", "Last Location"))
        self.label_14.setText(_translate("MainWindow", "Current Location"))
        self.track_status.setText(_translate("MainWindow", " "))
        self.track_lastloc.setText(_translate("MainWindow", " "))
        self.track_currentloc.setText(_translate("MainWindow", " "))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_3), _translate("MainWindow", "Track Order"))
                #self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_3), _translate("MainWindow", "Page"))
        self.label_15.setText(_translate("MainWindow", "UID"))
        self.label_16.setText(_translate("MainWindow", "Current Location"))
        self.label_17.setText(_translate("MainWindow", "Progress"))
        self.label_18.setText(_translate("MainWindow", "Status"))
        self.update_button.setText(_translate("MainWindow", "Update"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_4), _translate("MainWindow", "Update"))
        #self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_4), _translate("MainWindow", "Page"))
        self.label_19.setText(_translate("MainWindow", "Customer Name"))
        self.details_pushbutton.setText(_translate("MainWindow", "Get Details"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_2), _translate("MainWindow", "Details"))
        #self.label_15.setText(_translate("MainWindow", "Branch Name"))
        #self.pushButton.setText(_translate("MainWindow", "Get Details"))
        #self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_4), _translate("MainWindow", "Branch Details"))
    def add_order(self):
        order_details={}
        order_details["name"]=self.add_order_name.toPlainText().strip()
        self.add_order_name.clear()
        order_details["pincode"]=self.add_order_pincode.toPlainText().strip()
        self.add_order_pincode.clear()
        order_details["uid"]=self.add_order_uid.toPlainText().strip()
        self.add_order_uid.clear()
        order_details["sentfrom"]=self.add_order_sentfrom.toPlainText().strip()
        self.add_order_sentfrom.clear()
        order_details["sentto"]=self.add_order_sentto.toPlainText().strip()
        self.add_order_sentto.clear()
        order_details["contact"]=self.add_order_contact.toPlainText().strip()
        self.add_order_contact.clear()
        order_details["staff"]=self.add_order_staff.toPlainText().strip()
        self.add_order_staff.clear()
        order_details["weight"]=self.add_order_weight.toPlainText().strip()
        self.add_order_weight.clear()
        orderdetails_query = """INSERT INTO order_details 
                                VALUES (%s, %s, %s, %s) """
        customerdetails_query = """INSERT INTO customer_details 
                                VALUES (%s, %s, %s) """
        packagedetails_query = """INSERT INTO package_details 
                                VALUES (%s, %s, %s, %s, %s) """        
        order_tuple=(order_details["uid"],order_details["name"],order_details["sentfrom"],order_details["sentto"])
        customer_tuple=(order_details["name"],order_details["pincode"],order_details["contact"])
        package_tuple=(order_details["uid"],order_details["sentfrom"],order_details["sentfrom"],"Order Placed",0)
        self.mycursor.execute(orderdetails_query,order_tuple)
        self.mycursor.execute(customerdetails_query,customer_tuple)
        self.mycursor.execute(packagedetails_query,package_tuple)
        self.mydb.commit()
        self.updateordertable()
        print(order_details)
    def track_order(self):
        track_name=self.track_name.toPlainText().strip()
        self.track_name.clear()
        track_uid=self.track_uid.toPlainText().strip()
        self.track_uid.clear()
        track_query="""SELECT status,current,last,percentage from package_details where uid in (select uid from order_details where uid=%s and name =%s)"""
        track_tuple=(track_uid,track_name)
        self.mycursor.execute(track_query,track_tuple)
        trackingdeets=self.mycursor.fetchall()
        if len(trackingdeets)==0:
            self.track_status.setText("Not Available")
        else:
            trackingdeets=trackingdeets[0]
        #print(trackingdeets[0])
            self.track_status.setText(trackingdeets[0])
            self.track_currentloc.setText(trackingdeets[1])
            self.track_lastloc.setText(trackingdeets[2])
            self.track_progress.setValue(trackingdeets[3])
        self.mydb.commit()
        #self.updateordertable()
    def updateordertable(self):
        update_query="""SELECT * from order_details order by uid desc"""
        self.mycursor.execute(update_query)
        update_data=self.mycursor.fetchall()
        for i in range(8):
            #print()
            for j in range(4):
                try:
                    self.tableWidget.setItem(i,j,QTableWidgetItem(str(update_data[i][j])))
                    #print(update_data[i][j],end="")
                except IndexError:
                    break
        self.mydb.commit()
    def update_order(self):
        update_details={}
        update_details["uid"]=self.update_uid.toPlainText().strip()
        self.update_uid.clear()
        update_details["current"]=self.update_currentlocation.toPlainText().strip()
        self.update_currentlocation.clear()
        update_details["progress"]=self.update_progress.toPlainText().strip()
        self.update_progress.clear()
        update_details["status"]=self.update_status.toPlainText().strip()
        self.update_status.clear()
        get_last_query="""SELECT last from package_details where uid=%s"""
        self.mycursor.execute(get_last_query,(update_details["uid"],))
        last_loc=self.mycursor.fetchall()[0][0]
        print(last_loc)
        update_query="""UPDATE package_details SET current=%s,last=%s,percentage=%s,status=%s where uid=%s"""
        update_tuple=(update_details["current"],last_loc,update_details["progress"],update_details["status"],update_details["uid"])
        self.mycursor.execute(update_query,update_tuple)
        self.mydb.commit()
    def get_details(self):
        self.tableWidget_2.setRowCount(0)
        self.tableWidget_2.setRowCount(10)
        customer_name=self.details_customername.toPlainText().strip()
        update_query="""select distinct(OD.uid),CD.name,sentfrom,PD.status,PD.current from order_details OD join package_details PD on OD.uid=PD.uid join customer_details CD on CD.name=OD.name order by OD.uid"""
        update_query2="""select distinct(OD.uid),CD.name,sentfrom,PD.status,PD.current from order_details OD join package_details PD on OD.uid=PD.uid join customer_details CD on CD.name=OD.name where CD.name=%s order by OD.uid"""
        if customer_name=="":
            self.mycursor.execute(update_query)
        else:
            self.mycursor.execute(update_query2,(customer_name,))
        deets=self.mycursor.fetchall()
        #print(deets)
        for i in range(10):
            #print()
            for j in range(5):
                try:
                    self.tableWidget_2.setItem(i,j,QTableWidgetItem(str(deets[i][j])))
                    #print(update_data[i][j],end="")
                except IndexError:
                    break
        self.mydb.commit()

        

if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())
