def __init__(self):
QtGui.QDialog.__init__(self)
self.admin = Ui_add_admin()
self.admin.setupUi(self)
self.conn()
self.admin.sel_admin = QSqlRelationalTableModel(db = self.db)
self.admin.sel_admin.setTable("Teacher")
if not self.conn():
QtGui.QMessageBox.warning(
self, 'Error', 'database contecting error')
self.admin.selectComboBox.addItem("New Admin")
self.teach_query = QSqlQuery()
self.teach_query.exec_("Select Teacher_name FROM Teacher, Account where \
Account.Teacher_id = Teacher.Teacher_id AND \
Account.Admin_id = 0 ")
while self.teach_query.next():
record = self.teach_query.record()
self.name = str(record.value(0))
self.admin.selectComboBox.addItem(self.name)
self.admin.Select_btn.clicked.connect(self.fill_existing)
self.admin.Submit_btn.clicked.connect(self.insert_admin)
class add_admin(QtGui.QDialog):
def __init__(self):
QtGui.QDialog.__init__(self)
self.admin = Ui_add_admin()
self.admin.setupUi(self)
self.conn()
self.admin.sel_admin = QSqlRelationalTableModel(db = self.db)
self.admin.sel_admin.setTable("Teacher")
if not self.conn():
QtGui.QMessageBox.warning(
self, 'Error', 'database contecting error')
self.admin.selectComboBox.addItem("New Admin")
self.teach_query = QSqlQuery()
self.teach_query.exec_("Select Teacher_name FROM Teacher, Account where \
Account.Teacher_id = Teacher.Teacher_id AND \
Account.Admin_id = 0 ")
while self.teach_query.next():
record = self.teach_query.record()
self.name = str(record.value(0))
self.admin.selectComboBox.addItem(self.name)
self.admin.Select_btn.clicked.connect(self.fill_existing)
self.admin.Submit_btn.clicked.connect(self.insert_admin)
def check_existing_address(self):
temp_id = 0
existing_address_query =QSqlQuery()
existing_address_query.exec_("SELECT * FROM Address WHERE Street = '%s'\
AND City = '%s' AND State = '%s' AND Zipcode = '%s'" % (self.address, self.city, self.state, self.zip))
while existing_address_query.next():
result = existing_address_query.record()
temp_id = int(result.value(0))
return temp_id
def fill_existing(self):
self.admin.sel_teach = QSqlRelationalTableModel(db = self.db)
self.admin.sel_teach.setTable("Teacher")
selected_teacher_name = str(self.admin.selectComboBox.currentText())
query = QSqlQuery()
address_query =QSqlQuery()
query.exec_("SELECT * FROM Teacher WHERE Teacher_name = '%s'" % selected_teacher_name)
address_query.exec_("SELECT Address_id, Street, City, State, Zipcode FROM Address, \
Teacher WHERE Teacher_address = Address_id AND Teacher_name = '%s'" % selected_teacher_name)
while query.next():
record = query.record()
self.Teacher_id = str(record.value(0))
self.admin.nameLineEdit.setText(str(record.value(1)))
self.admin.homePhoneLineEdit.setText(str(record.value(2)))
self.admin.cellPhoneLineEdit.setText(str(record.value(3)))
self.admin.workPhoneLineEdit.setText(str(record.value(4)))
while address_query.next():
address_record =address_query.record()
self.address_id = str(address_record.value(0))
self.admin.addressLineEdit.setText(str(address_record.value(1)))
self.admin.cityLineEdit.setText(str(address_record.value(2)))
find = self.admin.stateComboBox.findText(str(address_record.value(3)),QtCore.Qt.MatchFixedString)
self.admin.zipcodeLineEdit.setText(str(address_record.value(4)))
if find >= 0:
self.admin.stateComboBox.setCurrentIndex(find)
self.admin.emailLineEdit.setText(str(record.value(6)))
find = self.admin.genderComboBox.findText(str(record.value(7)),QtCore.Qt.MatchFixedString)
if find >= 0:
self.admin.genderComboBox.setCurrentIndex(find)
self.admin.SSNLineEdit.setText(str(record.value(8)))
self.admin.payRateDoubleSpinBox.setValue(float(record.value(9)))
self.admin.medicalTextEdit.setText(str(record.value(10)))
self.admin.DOBDateEdit.setDate(record.value(11))
def insert_admin(self):
self.name = self.admin.nameLineEdit.text()
self.home = self.admin.homePhoneLineEdit.text()
self.home = re.sub('[^0-9]+', '', self.home)
self.home = re.sub("(\d)(?=(\d{3})+(?!\d))", r"\1-", "%d" % int(self.home[:-1])) + self.home[-1]
self.cell = self.admin.cellPhoneLineEdit.text()
if self.cell != '':
self.cell = re.sub('[^0-9]+', '', self.cell)
self.cell = re.sub("(\d)(?=(\d{3})+(?!\d))", r"\1-", "%d" % int(self.cell[:-1])) + self.cell[-1]
self.work = self.admin.workPhoneLineEdit.text()
if self.work != '':
self.work = re.sub('[^0-9]+', '', self.work)
self.work = re.sub("(\d)(?=(\d{3})+(?!\d))", r"\1-", "%d" % int(self.work[:-1])) + self.work[-1]
self.address = self.admin.addressLineEdit.text()
self.address.upper()
self.city = self.admin.cityLineEdit.text()
self.city.upper()
self.state = self.admin.stateComboBox.currentText()
self.zip = self.admin.zipcodeLineEdit.text()
self.email = self.admin.emailLineEdit.text()
self.email.lower()
self.gender = self.admin.genderComboBox.currentText()
self.SSN = self.admin.SSNLineEdit.text()
self.pay = self.admin.payRateDoubleSpinBox.value()
self.medical = self.admin.medicalTextEdit.toPlainText()
self.DOB = self.admin.DOBDateEdit.date()
self.DOB = self.DOB.toPyDate()
if self.name == '':
QtGui.QMessageBox.warning(
self, 'Error', "Please fill in required fields: Name, House Phone, Address, City, State, Zipcode, Gender, and Social security number(SSN).\
\nPhone number format: ###-###-#### \nAddress format: #'s Street \nSNN format: ###-##-####" )
elif self.home == '' or len(self.home) < 10 or len(self.home) > 12:
QtGui.QMessageBox.warning(
self, 'Error', "Please enter vaild phone number.\nPhone number format: ###-###-####" )
elif self.cell != '' and len(self.cell) < 10 or len(self.cell) > 12:
QtGui.QMessageBox.warning(
self, 'Error', "Please enter vaild phone number.\nPhone number format: ###-###-####" )
elif self.work != '' and len(self.work) < 10 or len(self.work) > 12:
QtGui.QMessageBox.warning(
self, 'Error', "Please enter vaild phone number.\nPhone number format: ###-###-####" )
elif self.address == '' or len(self.address) < 5:
QtGui.QMessageBox.warning(
self, 'Error', "Please fill in required fields: Name, DOB, House Phone, Address, City, State, Zipcode, Gender, and Social security number(SSN).\
\nPhone number format: ###-###-#### \nAddress format: #'s Street \nSNN format: ###-##-####" )
elif self.city == '':
QtGui.QMessageBox.warning(
self, 'Error', "Please fill in required fields: Name, DOB, House Phone, Address, City, State, Zipcode, Gender, and Social security number(SSN).\
\nPhone number format: ###-###-#### \nAddress format: #'s Street \nSNN format: ###-##-####" )
elif self.zip == '' or re.match('^\d{5}(-\d{4})?$', self.zip) == None:
QtGui.QMessageBox.warning(
self, 'Error', "Please enter a vaild zipcode")
elif self.SSN == '' or re.match('^\d{3}-\d{2}-\d{4}$', self.SSN) == None:
QtGui.QMessageBox.warning(
self, 'Error', "Please enter a vaild SSN. SNN format: ###-##-####")
else:
self.address_exist = self.check_existing_address()
results_msg = "Pending Insert: \n Name:'%s' \n DOB:'%s' \n Home phone:'%s' \n Cell phone:'%s'\
\n Work phone:'%s' \n Address:'%s' \n City:'%s' \n State:'%s' \
\n Zipcode: '%s' \n Email:'%s' \n Gender:'%s' \n SSN:'%s' \n Pay:'%s' \n Medical:'%s'" \
%( self.name, str(self.DOB), self.home, self.cell, self.work,\
self.address, self.city, self.state, self.zip, \
self.email, self.gender, self.SSN, str(self.pay), self.medical)
reply = QtGui.QMessageBox.question(self, 'Message',
results_msg, QtGui.QMessageBox.Yes, QtGui.QMessageBox.No)
if reply == QtGui.QMessageBox.Yes:
if self.address_exist == 0:
self.id_query =QSqlQuery()
self.id_query.exec_("SELECT Address_id FROM Address ORDER BY Address_id DESC LIMIT 1")
while self.id_query.next():
self.record = self.id_query.record()
self.address_id = self.record.value(0)
self.address_id += 1
self.Insert_Address_query = QSqlQuery()
self.Insert_Address_query.exec_("Insert into Address (Address_id, Street,\
City, State, zipcode) values('%u','%s','%s','%s','%s')" \
%( int(self.address_id), self.address, self.city, self.state, self.zip))
else:
self.exist_msg = "Address already exist, setting Admin to existing address."
self.exist_reply = QtGui.QMessageBox.information(self, 'Already Exist',
self.exist_msg, QtGui.QMessageBox.Ok)
self.address_id = self.address_exist
self.id_query =QSqlQuery()
self.id_query.exec_("SELECT Admin_id FROM Admin ORDER BY Admin_id DESC LIMIT 1")
while self.id_query.next():
self.record = self.id_query.record()
self.admin_id = self.record.value(0)
self.admin_id += 1
Insert_Admin_query = QSqlQuery()
Insert_Admin_query.exec_("Insert into Admin (Admin_id,Admin_name,\
Admin_home_phone, Admin_cell_phone, Admin_work_phone,\
Admin_address, Admin_email, Admin_sex, Admin_SSN, \
Admin_pay_rate, Admin_medical_information, Admin_date_of_birth)\
values(%d,'%s', '%s', '%s','%s',%d,'%s','%s', '%s', '%s', '%s','%s')"
%(int(self.admin_id), self.name, self.home, self.cell, self.work,\
int(self.address_id), self.email, self.gender, self.SSN, self.pay,\
self.medical, self.DOB))
self.create_account()
def create_account(self):
if str(self.admin.selectComboBox.currentText()) == "New Admin":
id_query =QSqlQuery()
id_query.exec_("SELECT User_id FROM Account ORDER BY User_id DESC LIMIT 1")
while id_query.next():
result = id_query.record()
user_id = result.value(0)
user_id += 1
username = self.admin.nameLineEdit.text()
pword = "rcdancearts"
access = 1
stu_id = 0
teach_id = 0
new_query = QSqlQuery()
new_query.exec_("Insert into Account (User_id, User_name, User_password,\
Access_level, Student_id, Teacher_id, Admin_id) values(%d,'%s','%s',%d,\
%d,%d,%d)" % (int(user_id), username, pword, access, stu_id, \
teach_id, int(self.admin_id)))
else:
update_query = QSqlQuery()
update_query.exec_("Update Account SET Access_level=1, Admin_id=%d \
WHERE Teacher_id = (SELECT Teacher_id FROM Teacher WHERE Teacher_name ='%s')"\
%(int(self.admin_id), self.name))
print(self.admin_id, self.name)
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()
