def __init__(self):
        QtGui.QMainWindow.__init__(self)
        self.ui = Ui_Search_MainWindow()
        self.ui.setupUi(self)

        self.Admin_id = 0
        
        self.conn() #need catch exception
        
        #TODO deal with foreign key
        self.ui.Teacher = QSqlRelationalTableModel(db = self.db)
        self.ui.Teacher.setTable("Teacher")

        
        self.ui.Teacher.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
        self.ui.Teacher.setHeaderData(1, QtCore.Qt.Horizontal, "Name")
        self.ui.Teacher.setHeaderData(7, QtCore.Qt.Horizontal, "Gender")
        self.ui.Teacher.setHeaderData(11, QtCore.Qt.Horizontal, "Date of Birth")
        self.ui.Teacher.setHeaderData(2, QtCore.Qt.Horizontal, "Home Phone")
        self.ui.Teacher.setHeaderData(3, QtCore.Qt.Horizontal, "Cell Phone")
        self.ui.Teacher.setHeaderData(4, QtCore.Qt.Horizontal, "Work Phone")
        self.ui.Teacher.setFilter('')
        self.ui.Teacher.select()
        
        

        #display window
        self.ui.Teacher_view.setModel(self.ui.Teacher)
        self.ui.Teacher_view.hideColumn(5)
        self.ui.Teacher_view.hideColumn(6)
        self.ui.Teacher_view.hideColumn(8)
        self.ui.Teacher_view.hideColumn(9)
        self.ui.Teacher_view.hideColumn(10)
        self.ui.Teacher_view.hideColumn(11)
        self.ui.Teacher_view.setEditTriggers(QAbstractItemView.NoEditTriggers)


        self.ui.Search_btn.clicked.connect(self.search)
        self.ui.Adv_search_btn.clicked.connect(self.advsearch_show)
        self.ui.Reset_search_btn.clicked.connect(self.reset_table)
        self.ui.Detail_btn.clicked.connect(self.detail_show)
        self.ui.Back_btn.clicked.connect(self.close)

        self.ui.Teacher_view.setSelectionMode(QAbstractItemView.SingleSelection)
        self.ui.Teacher_view.setSelectionBehavior(QAbstractItemView.SelectRows)
class Search_teacher_window(QtGui.QMainWindow):
    def __init__(self):
        QtGui.QMainWindow.__init__(self)
        self.ui = Ui_Search_MainWindow()
        self.ui.setupUi(self)

        self.Admin_id = 0
        
        self.conn() #need catch exception
        
        #TODO deal with foreign key
        self.ui.Teacher = QSqlRelationalTableModel(db = self.db)
        self.ui.Teacher.setTable("Teacher")

        
        self.ui.Teacher.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
        self.ui.Teacher.setHeaderData(1, QtCore.Qt.Horizontal, "Name")
        self.ui.Teacher.setHeaderData(7, QtCore.Qt.Horizontal, "Gender")
        self.ui.Teacher.setHeaderData(11, QtCore.Qt.Horizontal, "Date of Birth")
        self.ui.Teacher.setHeaderData(2, QtCore.Qt.Horizontal, "Home Phone")
        self.ui.Teacher.setHeaderData(3, QtCore.Qt.Horizontal, "Cell Phone")
        self.ui.Teacher.setHeaderData(4, QtCore.Qt.Horizontal, "Work Phone")
        self.ui.Teacher.setFilter('')
        self.ui.Teacher.select()
        
        

        #display window
        self.ui.Teacher_view.setModel(self.ui.Teacher)
        self.ui.Teacher_view.hideColumn(5)
        self.ui.Teacher_view.hideColumn(6)
        self.ui.Teacher_view.hideColumn(8)
        self.ui.Teacher_view.hideColumn(9)
        self.ui.Teacher_view.hideColumn(10)
        self.ui.Teacher_view.hideColumn(11)
        self.ui.Teacher_view.setEditTriggers(QAbstractItemView.NoEditTriggers)


        self.ui.Search_btn.clicked.connect(self.search)
        self.ui.Adv_search_btn.clicked.connect(self.advsearch_show)
        self.ui.Reset_search_btn.clicked.connect(self.reset_table)
        self.ui.Detail_btn.clicked.connect(self.detail_show)
        self.ui.Back_btn.clicked.connect(self.close)

        self.ui.Teacher_view.setSelectionMode(QAbstractItemView.SingleSelection)
        self.ui.Teacher_view.setSelectionBehavior(QAbstractItemView.SelectRows)

    def Teacherinfo_update(self):
        #TODO check input validity
        
        self.detail.TeacherID = self.detail.ui.Id_detail_lineEdit.text()
        self.detail.TeacherName = self.detail.ui.Name_detail_lineEdit.text()      
        self.detail.TeacherGender = self.detail.ui.Gender_comboBox.currentText()   
        self.detail.TeacherEmail = self.detail.ui.Email_detail_lineEdit.text()
        self.detail.TeacherEmail.lower()
        self.detail.TeacherBirth = self.detail.ui.Birth_detail_dateEdit.date()
        self.detail.TeacherBirth = self.detail.TeacherBirth.toPyDate()
        self.detail.TeacherHomePhone = self.detail.ui.Homephone_detail_lineEdit.text()
        self.detail.TeacherCellPhone = self.detail.ui.Cellphone_detail_lineEdit.text()  
        self.detail.TeacherWorkPhone = self.detail.ui.Workphone_detail_lineEdit.text()
        self.detail.TeacherSSN = self.detail.ui.SSN_detail_lineEdit.text()
        self.detail.TeacherZipcode = self.detail.ui.Zipcode_detail_lineEdit.text()
        self.detail.TeacherAddress = self.detail.ui.Address_detail_lineEdit.text()
        self.detail.TeacherAddress.upper()
        self.detail.TeacherCity = self.detail.ui.City_detail_lineEdit.text()
        self.detail.TeacherCity.upper()
        self.detail.TeacherState = self.detail.ui.State_detail_ComboBox.currentText()
        self.detail.TeacherMedical = self.detail.ui.Medical_detail_textEdit.toPlainText()


        #check is name empty
        if self.detail.TeacherName == "":
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "student name cannot be empty!" )
            return    
        #check is email empty
        if self.detail.TeacherEmail == "":
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "email cannot be empty!" )
            return
        elif self.detail.TeacherEmail != "" and \
        re.match('^([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})$', self.detail.TeacherEmail) == None:
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "please enter a valid email address!" )
            return
        
        #check if the homw phone is valid
        if self.detail.TeacherHomePhone == "":
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "home phone cannot be empty!" )
            return
        elif self.detail.TeacherHomePhone != "" and re.match('^[2-9]\d{2}-\d{3}-\d{4}$', self.detail.TeacherHomePhone) == None:
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "please enter a valid phone number!")
            return
        
        #check if cell phone is valid
        if self.detail.TeacherCellPhone != "" and re.match('^[2-9]\d{2}-\d{3}-\d{4}$', self.detail.TeacherCellPhone) == None:
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "please enter a valid phone number!")
            return
        
        #check if work phone is valid
        if self.detail.TeacherWorkPhone != "" and re.match('^[2-9]\d{2}-\d{3}-\d{4}$', self.detail.TeacherWorkPhone) == None:
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "please enter a valid phone number!")
            return

        #check if ssn valid
        if self.detail.TeacherSSN == "":
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "SSN cannot be empty!" )
            return
        elif self.detail.TeacherSSN != "" and re.match('^\d{3}-\d{2}-\d{4}$', self.detail.TeacherSSN) == None:
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "please enter a SSN number!")
            return

        #check for valid zipcode
        if self.detail.TeacherZipcode != "" and \
           re.match('^\d{5}(?:[-\s]\d{4})?$', self.detail.TeacherZipcode) == None:
            QtGui.QMessageBox.warning(
                    self.detail, 'Error', "please enter a valid zipcode!")
            return

        update_query = QSqlQuery()

        update_query.exec("Select Admin_id From Account Where Teacher_id = '%d'" % int(self.detail.TeacherID))
        if update_query.next():
            record = update_query.record()
            self.Admin_id = int(record.value(0))
        

        if update_query.exec_("Update Teacher, Address set Teacher.Teacher_name = '%s', Teacher.Teacher_sex = '%s',\
                            Teacher.Teacher_email = '%s', Teacher.Teacher_date_of_birth = '%s', \
                            Teacher.Teacher_home_phone = '%s', Teacher.Teacher_cell_phone = '%s',\
                            Teacher.Teacher_work_phone = '%s', Teacher.Teacher_SSN = '%s',\
                            Address.Zipcode = '%s', Address.Street = '%s', Address.City = '%s', Address.State = '%s', \
                            Teacher.Teacher_medical_information = '%s' where Teacher.Teacher_id = '%d' and Address.Address_id = '%d'"\
                           % (self.detail.TeacherName, self.detail.TeacherGender, \
                           self.detail.TeacherEmail,self.detail.TeacherBirth, self.detail.TeacherHomePhone, self.detail.TeacherCellPhone,\
                           self.detail.TeacherWorkPhone,self.detail.TeacherSSN, self.detail.TeacherZipcode,\
                           self.detail.TeacherAddress, self.detail.TeacherCity, self.detail.TeacherState, self.detail.TeacherMedical,\
                           int(self.detail.TeacherID), int(self.detail.Address_id))):
            if self.Admin_id != 0:
                update_query.exec_("Update Admin, Address set Admin.Admin_name = '%s', Admin.Admin_sex = '%s',\
                            Admin.Admin_email = '%s', Admin.Admin_date_of_birth = '%s', \
                            Admin.Admin_home_phone = '%s', Admin.Admin_cell_phone = '%s',\
                            Admin.Admin_work_phone = '%s', Admin.Admin_SSN = '%s',\
                            Address.Zipcode = '%s', Address.Street = '%s', Address.City = '%s', Address.State = '%s', \
                            Admin.Admin_medical_information = '%s' where Admin.Admin_id = '%d' and Address.Address_id = '%d'"\
                           % (self.detail.TeacherName, self.detail.TeacherGender, \
                           self.detail.TeacherEmail,self.detail.TeacherBirth, self.detail.TeacherHomePhone, self.detail.TeacherCellPhone,\
                           self.detail.TeacherWorkPhone,self.detail.TeacherSSN, self.detail.TeacherZipcode,\
                           self.detail.TeacherAddress, self.detail.TeacherCity, self.detail.TeacherState, self.detail.TeacherMedical,\
                           int(self.Admin_id), int(self.detail.Address_id)))

                
            QtGui.QMessageBox.information(
                self.detail, 'Success', 'Update record successfully')
            self.reset_table()
        else:
            QtGui.QMessageBox.warning(
                self, 'Error', 'Update record unsuccessfully')
       
 
        
    def detail_show(self):
        curIndex = self.ui.Teacher_view.currentIndex().row()
        if curIndex == -1:
            QtGui.QMessageBox.warning(
                self, 'Error', 'Please select a row')
            return curIndex
        
       
        self.detail = Teacher_info_dialog()
        
        self.detail.show()
        
        self.detail.record = self.ui.Teacher.record(curIndex)

        address = QSqlQuery()
        self.detail.Address_id = self.detail.record.field(5).value()
        if not isinstance(self.detail.Address_id, QtCore.QPyNullVariant):
            address.exec_("select * from Address where Address_id = %d" % self.detail.Address_id)
            address.next()
        self.detail.record_A = address.record()
    
  
        
        #check weather the data exists in database

        #TeacherID
        if not isinstance(self.detail.record.field(0).value(), QtCore.QPyNullVariant):
            self.detail.ui.Id_detail_lineEdit.setText(str(self.detail.record.field(0).value()))
        #TeacherName
        if not isinstance(self.detail.record.field(1).value(), QtCore.QPyNullVariant):
            self.detail.ui.Name_detail_lineEdit.setText(self.detail.record.field(1).value())
        #TeacherGender
        if not isinstance(self.detail.record.field(7).value(), QtCore.QPyNullVariant):
            self.detail.ui.Gender_comboBox.addItem("Male")
            self.detail.ui.Gender_comboBox.addItem("Female")
            if self.detail.record.field(7).value() == "Male":
                self.detail.ui.Gender_comboBox.setCurrentIndex(0)
            else:
                self.detail.ui.Gender_comboBox.setCurrentIndex(1)
            
        
        #TeacherEmail
        if not isinstance(self.detail.record.field(6).value(), QtCore.QPyNullVariant):
            self.detail.ui.Email_detail_lineEdit.setText(self.detail.record.field(6).value())
        #need detail
        #TeacherBirth
        if not isinstance(self.detail.record.field(11).value(), QtCore.QPyNullVariant):
            self.detail.ui.Birth_detail_dateEdit.setDate(self.detail.record.field(11).value())
        #TeacherHomePhone
        if not isinstance(self.detail.record.field(2).value(), QtCore.QPyNullVariant):
            self.detail.ui.Homephone_detail_lineEdit.setText(self.detail.record.field(2).value())
        #TeacherCellPhone
        if not isinstance(self.detail.record.field(3).value(), QtCore.QPyNullVariant):
            self.detail.ui.Cellphone_detail_lineEdit.setText(self.detail.record.field(3).value())
        #TeacherWorkPhone
        if not isinstance(self.detail.record.field(4).value(), QtCore.QPyNullVariant):
            self.detail.ui.Workphone_detail_lineEdit.setText(self.detail.record.field(4).value())

        #TeacherSNN  
        if not isinstance(self.detail.record.field(8).value(), QtCore.QPyNullVariant):
            self.detail.ui.SSN_detail_lineEdit.setText(self.detail.record.field(8).value())
        #Teacher address
        if not isinstance(self.detail.record_A.field(4).value(), QtCore.QPyNullVariant):
            self.detail.ui.Zipcode_detail_lineEdit.setText(str(self.detail.record_A.field(4).value()))

        if not isinstance(self.detail.record.field(1).value(), QtCore.QPyNullVariant):
            self.detail.ui.Address_detail_lineEdit.setText(self.detail.record_A.field(1).value())

        if not isinstance(self.detail.record_A.field(2).value(), QtCore.QPyNullVariant):
            self.detail.ui.City_detail_lineEdit.setText(self.detail.record_A.field(2).value())
        if not isinstance(self.detail.record_A.field(3).value(), QtCore.QPyNullVariant):
            index = self.detail.ui.State_detail_ComboBox.findText(self.detail.record_A.field(3).value())
            self.detail.ui.State_detail_ComboBox.setCurrentIndex(index)
        #TeacherMedical
        if not isinstance(self.detail.record.field(12).value(), QtCore.QPyNullVariant):
            self.detail.ui.Medical_detail_textEdit.setText(str(self.detail.record.field(10).value()))
        

        self.detail.ui.Close_detail_btn.clicked.connect(self.detail.close)
        self.detail.ui.Update_detail_btn.clicked.connect(self.Teacherinfo_update)
            
    def advsearch_show(self):
        self.adv = Advsearch_Dialog()
        self.adv.show()
        self.adv.ui.Seacch_adv_btn.clicked.connect(self.advsearch_query)
        self.adv.ui.Cancel_adv_btn.clicked.connect(self.adv.close)
        
    def advsearch_query(self):
        #self.reset_table()
        self.adv.ui.Id_adv_label.hide()
        self.adv.ui.Name_adv_label.hide()
        self.adv.ui.Homephone_adv_label.hide()
        self.adv.ui.Cellphone_adv_label.hide()
        self.adv.ui.Workphone_adv_label.hide()

        
        
        Teacher_ID = self.adv.ui.ID_adv_ledit.text()
        Teacher_name = self.adv.ui.Name_adv_ledit.text()
        Teacher_homephone = self.adv.ui.Homephone_adv_ledit.text()
        Teacher_cellphone = self.adv.ui.Cellphone_adv_ledit.text()
        Teacher_workphone = self.adv.ui.Workphone_adv_ledit.text()
        Teacher_datebirth = self.adv.ui.Start_dateedit.date()
        Teacher_end_datebirth = self.adv.ui.End_dateedit.date()
        
        whereClause = ''
        
        flag = True
        
          
        if self.adv.ui.ID_cbox.isChecked() and Teacher_ID == '':
            self.adv.ui.Id_adv_label.show()
            flag = False 
        elif Teacher_ID != '':
            if Teacher_ID.isdigit():
                whereClause += ("Teacher_id = %s"%Teacher_ID)
            else:
                QtGui.QMessageBox.warning(
                    self.adv, 'Error', "please enter valid id!" )
                return

            
        if self.adv.ui.Name_cobx.isChecked() and Teacher_name == '':
            self.adv.ui.Name_adv_label.show()
            flag = False
        elif Teacher_name != '':
            if whereClause != '':
                whereClause += ' and '
            if self.adv.ui.Name_Exact_cobx.isChecked():
                whereClause += ("Teacher_name = '%s'"%Teacher_name)
            else:
                whereClause += ("Teacher_name like '%%%s%%'"%Teacher_name)
            
        if self.adv.ui.Homephone_cbox.isChecked() and Teacher_homephone == '':
            self.adv.ui.Homephone_adv_label.show()
            flag = False
        elif Teacher_homephone != '':
            if whereClause != '':
                whereClause += ' and '
            if self.adv.ui.Homephone_Exact_cbox.isChecked():
                whereClause += ("Teacher_home_phone = '%s'"%Teacher_homephone)
            else:
                whereClause += ("Teacher_home_phone like '%%%s%%'"%Teacher_homephone)
            
        if self.adv.ui.Cellphone_cbox.isChecked() and Teacher_cellphone == '':
            self.adv.ui.Cellphone_adv_label.show()
            flag = False
        elif Teacher_cellphone != '':
            if whereClause != '':
                whereClause += ' and '
            if self.adv.ui.Cellphone_Exact_cbox.isChecked():
                whereClause += ("Teacher_cell_phone = '%s'" % Teacher_cellphone)
            else:
                whereClause += ("Teacher_cell_phone like '%%%s%%'" % Teacher_cellphone)
                
        if self.adv.ui.Workphone_cbox.isChecked() and Teacher_workphone == '':
            self.adv.ui.Workphone_adv_label.show()
            flag = False
        elif Teacher_workphone != '':
            if whereClause != '':
                whereClause += ' and '
            if self.adv.ui.Workphone_Exact_cbox.isChecked():
                whereClause += ("Teacher_work_phone = '%s'" % Teacher_workphone)
            else:
                whereClause += ("Teacher_work_phone like '%%%s%%'" % Teacher_workphone)


        if self.adv.ui.Birth_cbox.isChecked():
            if whereClause != '':
                whereClause += ' and '
            whereClause += ("Teacher_date_of_birth >= '%s' and Teacher_date_of_birth <= '%s'"% \
                        (Teacher_datebirth.toString("yyyy-MM-dd"), Teacher_end_datebirth.toString("yyyy-MM-dd")))

        self.ui.Teacher.setFilter(whereClause)

        if flag:
            self.adv.close()    
        
        
        
    def reset_table(self):
        self.ui.Teacher.setFilter('')
        
    def search(self):
        if not self.conn():
            QtGui.QMessageBox.warning(
                self, 'Error', 'database contecting error')          
        #get input data from user
        #refreash rows
        self.reset_table()
        input_Teacher_name = self.ui.Search_lineEdit.text()

        
        if input_Teacher_name != '':
            if self.ui.Exact_search_cbox.isChecked():
                self.ui.Teacher.setFilter("Teacher_name = '%s'" % input_Teacher_name)
            else:
                self.ui.Teacher.setFilter("Teacher_name like '%%%s%%'" % input_Teacher_name)
           
            
        if self.ui.Search_lineEdit.text() == '':
            QtGui.QMessageBox.warning(
                self, 'Error', 'Please input keyword you want to search by') 

        
        

    def conn(self):
        self.db = QSqlDatabase.addDatabase("QMYSQL")
        self.db.setHostName("services1.mcs.sdsmt.edu")
        self.db.setDatabaseName("db_dancesoft_f15")
        self.db.setUserName("dancesoft_f15")
        self.db.setPassword("DanceSoft")
        return self.db.open()
Beispiel #3
0
class Search_teacher_window(QtGui.QMainWindow):
    def __init__(self):
        QtGui.QMainWindow.__init__(self)
        self.ui = Ui_Search_MainWindow()
        self.ui.setupUi(self)

        
        self.conn() #need catch exception
        
       
        #TODO deal with foreign key
        self.ui.Teacher = QSqlRelationalTableModel(db = self.db)
        self.ui.Teacher.setTable("Teacher")

        
        self.ui.Teacher.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
        self.ui.Teacher.setHeaderData(1, QtCore.Qt.Horizontal, "Name")
        self.ui.Teacher.setHeaderData(7, QtCore.Qt.Horizontal, "Gender")
        self.ui.Teacher.setHeaderData(11, QtCore.Qt.Horizontal, "Date of Birth")
        self.ui.Teacher.setHeaderData(2, QtCore.Qt.Horizontal, "Home Phone")
        self.ui.Teacher.setHeaderData(3, QtCore.Qt.Horizontal, "Cell Phone")
        self.ui.Teacher.setHeaderData(4, QtCore.Qt.Horizontal, "Work Phone")
        self.ui.Teacher.setFilter('')
        self.ui.Teacher.select()
        
        

        #display window
        self.ui.Teacher_view.setModel(self.ui.Teacher)
        self.ui.Teacher_view.hideColumn(5)
        self.ui.Teacher_view.hideColumn(6)
        self.ui.Teacher_view.hideColumn(8)
        self.ui.Teacher_view.hideColumn(9)
        self.ui.Teacher_view.hideColumn(10)


        self.ui.Search_btn.clicked.connect(self.search)
        self.ui.Adv_search_btn.clicked.connect(self.advsearch_show)
        self.ui.Reset_search_btn.clicked.connect(self.reset_table)
        self.ui.Detail_btn.clicked.connect(self.detail_show)
        self.ui.Back_btn.clicked.connect(self.close)


    def Teacherinfo_update(self):
        #TODO check input validity
        #TODO Solve foreign key!!!!!!!!!!!!!!!
        
        self.detail.TeacherID = self.detail.ui.Id_detail_lineEdit.text()
        self.detail.TeacherName = self.detail.ui.Name_detail_lineEdit.text()      
        self.detail.TeacherGender = self.detail.ui.Gender_detail_lineEdit.text()   
        self.detail.TeacherEmail = self.detail.ui.Email_detail_lineEdit.text()
        self.detail.TeacherBirth = self.detail.ui.Birth_detail_dateEdit.date()  
        self.detail.TeacherPhone = self.detail.ui.Phone_detail_lineEdit.text()
        self.detail.TeacherPG = self.detail.ui.Pguradian_detail_lineEdit.text()  
        self.detail.TeacherSG = self.detail.ui.Sguardian_detail_lineEdit.text()
        self.detail.TeacherEcon = self.detail.ui.Econtact_detail_lineEdit.text()
        self.detail.TeacherEphone = self.detail.ui.Ephone_detail_lineEdit.text()
        self.detail.TeacherTuition = self.detail.ui.Tuition_detail_lineEdit.text()
        self.detail.TeacherAddress = self.detail.ui.Address_detail_lineEdit.text()
        self.detail.TeacherCity = self.detail.ui.City_detail_lineEdit.text()    
        self.detail.TeacherState = self.detail.ui.State_detail_lineEdit.text()
        self.detail.TeacherMedical = self.detail.ui.Medical_detail_textEdit.toPlainText()


        update_query = QSqlQuery()
        
        if update_query.exec_("Update Teacher, Address, Guardian Set Teacher.Teacher_name = '%s', Teacher.Teacher_sex = '%s', Teacher.Teacher_email = '%s', \
                           Teacher.Teacher_date_of_birth = '%s', Teacher.Teacher_home_phone = '%s', Teacher.Teacher_Emergency_contact = '%s', Teacher.Emergency_contact_phone = '%s', \
                           Teacher.Teacher_medical_information = '%s', Teacher.Tuition = '%s', \
                           Address.Street = '%s', Address.City = '%s', Address.State = '%s', Guardian.Guardian_name = '%s'\
                           Where Teacher.Teacher_id = '%d' and Teacher.Teacher_address = Address.Address_id and Teacher.Guardian_primary = Guardian.Guardian_id"\
                           %(self.detail.TeacherName,  self.detail.TeacherGender, self.detail.TeacherEmail, self.detail.TeacherBirth.toString("yyyy-MM-dd"), self.detail.TeacherPhone,  self.detail.TeacherEcon, self.detail.TeacherEphone,\
                           self.detail.TeacherMedical, self.detail.TeacherTuition, self.detail.TeacherAddress, self.detail.TeacherCity, self.detail.TeacherState, self.detail.TeacherPG, int(self.detail.TeacherID)))\
            and update_query.exec_("Update Teacher, Guardian Set Guardian.Guardian_name = '%s '\
                                    Where Teacher.Teacher_id = '%d' and Teacher.Guardian_secondary = Guardian.Guardian_id" \
                                   %(self.detail.TeacherSG, int(self.detail.TeacherID))):
            QtGui.QMessageBox.information(
                self.detail, 'Success', 'Update record successfully')
            self.reset_table()
        else:
            QtGui.QMessageBox.warning(
                self, 'Error', 'Update record unsuccessfully')
        
    def detail_show(self):
        curIndex = self.ui.Teacher_view.currentIndex().row()
        if curIndex == -1:
            QtGui.QMessageBox.warning(
                self, 'Error', 'Please select a row')
            return curIndex
        
       
        self.detail = Teacher_info_dialog()
        
        self.detail.show()
        
        self.detail.record = self.ui.Teacher.record(curIndex)

        address = QSqlQuery()
        self.detail.Address_id = self.detail.record.field(5).value()
        address.exec_("select * from Address where Address_id = %d" % self.detail.Address_id)
        address.next()
        self.detail.record_A = address.record()
    
  
        
        #check weather the data exists in database

        #TeacherID
        if not isinstance(self.detail.record.field(0).value(), QtCore.QPyNullVariant):
            self.detail.ui.Id_detail_lineEdit.setText(str(self.detail.record.field(0).value()))
        #TeacherName
        if not isinstance(self.detail.record.field(1).value(), QtCore.QPyNullVariant):
            self.detail.ui.Name_detail_lineEdit.setText(self.detail.record.field(1).value())
        #TeacherGender
        if not isinstance(self.detail.record.field(7).value(), QtCore.QPyNullVariant):
            self.detail.ui.Gender_detail_lineEdit.setText(self.detail.record.field(7).value())
        #TeacherEmail
        if not isinstance(self.detail.record.field(6).value(), QtCore.QPyNullVariant):
            self.detail.ui.Email_detail_lineEdit.setText(self.detail.record.field(6).value())
        #need detail
        #TeacherBirth
        if not isinstance(self.detail.record.field(11).value(), QtCore.QPyNullVariant):
            self.detail.ui.Birth_detail_dateEdit.setDate(self.detail.record.field(11).value())
        #TeacherHomePhone
        if not isinstance(self.detail.record.field(2).value(), QtCore.QPyNullVariant):
            self.detail.ui.Homephone_detail_lineEdit.setText(self.detail.record.field(2).value())
        #TeacherCellPhone
        if not isinstance(self.detail.record.field(3).value(), QtCore.QPyNullVariant):
            self.detail.ui.Cellphone_detail_lineEdit.setText(self.detail.record.field(3).value())
        #TeacherWorkPhone
        if not isinstance(self.detail.record.field(4).value(), QtCore.QPyNullVariant):
            self.detail.ui.Workphone_detail_lineEdit.setText(self.detail.record.field(4).value())

        #TeacherSNN  
        if not isinstance(self.detail.record.field(8).value(), QtCore.QPyNullVariant):
            self.detail.ui.SSN_detail_lineEdit.setText(self.detail.record.field(8).value())
        #TeacherPayRate
        if not isinstance(self.detail.record.field(9).value(), QtCore.QPyNullVariant):
            self.detail.ui.Payrate_detail_lineEdit.setText(str(self.detail.record.field(9).value()))
        #Teacher address
        if not isinstance(self.detail.record_A.field(4).value(), QtCore.QPyNullVariant):
            self.detail.ui.Zipcode_detail_lineEdit.setText(str(self.detail.record_A.field(4).value()))

        if not isinstance(self.detail.record.field(1).value(), QtCore.QPyNullVariant):
            self.detail.ui.Address_detail_lineEdit.setText(self.detail.record_A.field(1).value())

        if not isinstance(self.detail.record_A.field(2).value(), QtCore.QPyNullVariant):
            self.detail.ui.City_detail_lineEdit.setText(self.detail.record_A.field(2).value())
        if not isinstance(self.detail.record_A.field(3).value(), QtCore.QPyNullVariant):
            self.detail.ui.State_detail_lineEdit.setText(self.detail.record_A.field(3).value())
        #TeacherMedical
        if not isinstance(self.detail.record.field(12).value(), QtCore.QPyNullVariant):
            self.detail.ui.Medical_detail_textEdit.setText(self.detail.record.field(12).value())
        

        self.detail.ui.Close_detail_btn.clicked.connect(self.detail.close)
        self.detail.ui.Update_detail_btn.clicked.connect(self.Teacherinfo_update)
            
    def advsearch_show(self):
        self.adv = Advsearch_Dialog()
        self.adv.show()
        self.adv.ui.Seacch_adv_btn.clicked.connect(self.advsearch_query)
        self.adv.ui.Cancel_adv_btn.clicked.connect(self.adv.close)
        
    def advsearch_query(self):
        #self.reset_table()
        self.adv.ui.Id_adv_label.hide()
        self.adv.ui.Name_adv_label.hide()
        self.adv.ui.Homephone_adv_label.hide()
        self.adv.ui.Cellphone_adv_label.hide()
        self.adv.ui.Workphone_adv_label.hide()

        
        
        Teacher_ID = self.adv.ui.ID_adv_ledit.text()
        Teacher_name = self.adv.ui.Name_adv_ledit.text()
        Teacher_homephone = self.adv.ui.Homephone_adv_ledit.text()
        Teacher_cellphone = self.adv.ui.Cellphone_adv_ledit.text()
        Teacher_workphone = self.adv.ui.Workphone_adv_ledit.text()
        whereClause = ''
        
        flag = True
        
          
        if self.adv.ui.ID_cbox.isChecked() and Teacher_ID == '':
            self.adv.ui.Id_adv_label.show()
            flag = False 
        elif Teacher_ID != '':
            whereClause += ("Teacher_id = %s"%Teacher_ID)

            
        if self.adv.ui.Name_cobx.isChecked() and Teacher_name == '':
            self.adv.ui.Name_adv_label.show()
            flag = False
        elif Teacher_name != '':
            if whereClause != '':
                whereClause += ' and '
            if self.adv.ui.Name_Exact_cobx.isChecked():
                whereClause += ("Teacher_name = '%s'"%Teacher_name)
            else:
                whereClause += ("Teacher_name like '%%%s%%'"%Teacher_name)
            
        if self.adv.ui.Homephone_cbox.isChecked() and Teacher_homephone == '':
            self.adv.ui.Homephone_adv_label.show()
            flag = False
        elif Teacher_homephone != '':
            if whereClause != '':
                whereClause += ' and '
            if self.adv.ui.Homephone_Exact_cbox.isChecked():
                whereClause += ("Teacher_home_phone = '%s'"%Teacher_homephone)
            else:
                whereClause += ("Teacher_home_phone like '%%%s%%'"%Teacher_homephone)
            
        if self.adv.ui.Cellphone_cbox.isChecked() and Teacher_cellphone == '':
            self.adv.ui.Cellphone_adv_label.show()
            flag = False
        elif Teacher_cellphone != '':
            if whereClause != '':
                whereClause += ' and '
            if self.adv.ui.Cellphone_Exact_cbox.isChecked():
                whereClause += ("Teacher_cell_phone = '%s'" % Teacher_cellphone)
            else:
                whereClause += ("Teacher_cell_phone like '%%%s%%'" % Teacher_cellphone)
                
        if self.adv.ui.Workphone_cbox.isChecked() and Teacher_workphone == '':
            self.adv.ui.Workphone_adv_label.show()
            flag = False
        elif Teacher_workphone != '':
            if whereClause != '':
                whereClause += ' and '
            if self.adv.ui.WorkPhone_Exact_cbox.isChecked():
                whereClause += ("Teacher_work_phone = '%s'" % Teacher_workphone)
            else:
                whereClause += ("Teacher_work_phone like '%%%s%%'" % Teacher_workphone)

        self.ui.Teacher.setFilter(whereClause)

        if flag:
            self.adv.close()    
        
        
        
    def reset_table(self):
        self.ui.Teacher.setFilter('')
        
    def search(self):
        if not self.conn():
            QtGui.QMessageBox.warning(
                self, 'Error', 'database contecting error')          
        #get input data from user
        #refreash rows
        self.reset_table()
        input_Teacher_name = self.ui.Search_lineEdit.text()

        
        if input_Teacher_name != '':
            if self.ui.Exact_search_cbox.isChecked():
                self.ui.Teacher.setFilter("Teacher_name = '%s'" % input_Teacher_name)
            else:
                self.ui.Teacher.setFilter("Teacher_name like '%%%s%%'" % input_Teacher_name)
           
            
        if self.ui.Search_lineEdit.text() == '':
            QtGui.QMessageBox.warning(
                self, 'Error', 'Please input keyword you want to search by') 

        
        

    def conn(self):
        self.db = QSqlDatabase.addDatabase("QMYSQL")
        self.db.setHostName("services1.mcs.sdsmt.edu")
        self.db.setDatabaseName("db_dancesoft_f15")
        self.db.setUserName("dancesoft_f15")
        self.db.setPassword("DanceSoft")
        return self.db.open()