def setupQuery(auth, profile, matkulList): # If first time, get mahasiswa profile if profile is None: try: query = """SELECT * FROM user JOIN profil_mahasiswa ON user.id=profil_mahasiswa.user_id_mahasiswa WHERE user.id=%s""" format = (auth.id,) profile = execQuery(query, format)[0] except Exception as e: print(e) # If first time, get mahasiswa matkul list if matkulList is None: try: query = """SELECT * FROM ( mata_kuliah NATURAL JOIN waktu NATURAL JOIN jadwal NATURAL JOIN slot_waktu_jadwal NATURAL JOIN slot_waktu ) WHERE mata_kuliah.kode_jurusan=%s""" format = (profile.kode_jurusan,) matkulList = execQuery(query, format) except Exception as e: print(e) return (profile, matkulList)
def setupQuery(auth, profile, studyList): # If first time, get mahasiswa profile if profile is None: try: query = """SELECT * FROM user JOIN profil_mahasiswa ON user.id=profil_mahasiswa.user_id_mahasiswa WHERE user.id=%s""" format = (auth.id,) profile = execQuery(query, format)[0] except Exception as e: print(e) # If first time, get mahasiswa study list if studyList is None: try: query = """SELECT * FROM ( mata_kuliah_diambil NATURAL JOIN waktu NATURAL JOIN mata_kuliah NATURAL JOIN jadwal NATURAL JOIN slot_waktu_jadwal NATURAL JOIN slot_waktu ) WHERE mata_kuliah_diambil.nim=%s""" format = (profile.nim,) studyList = execQuery(query, format) except Exception as e: print(e) return (profile, studyList)
def setupQuery(auth, profile, teachList): # If first time, get dosen profile if profile is None: try: query = """SELECT * FROM user JOIN profil_dosen ON user.id=profil_dosen.user_id_dosen WHERE user.id=%s""" format = (auth.id, ) profile = execQuery(query, format)[0] except Exception as e: print(e) # If first time, get dosen teach list if teachList is None: try: query = """SELECT * FROM ( mata_kuliah_diampu NATURAL JOIN waktu NATURAL JOIN mata_kuliah NATURAL JOIN jadwal NATURAL JOIN slot_waktu_jadwal NATURAL JOIN slot_waktu ) WHERE mata_kuliah_diampu.nip=%s""" format = (profile.NIP, ) teachList = execQuery(query, format) except Exception as e: print(e) return (profile, teachList)
def sendEmailButtonClicked(content): global _usernameInput_L_3, _emailInput_L_3 message = QMessageBox() message.setIcon(QMessageBox.Information) message.setWindowTitle("Invalid input") # Get text username = _usernameInput_L_3.text() email = _emailInput_L_3.text() # Check username and password if len(username) == 0 or len(email) == 0: message.setText("Username or email cannot be empty") message.exec_() return # Query database query = "SELECT * FROM user WHERE username=%s" format = (username, ) user = None try: user = execQuery(query, format) except Exception as e: print(e) # Cek if user found if user: print(f"""\ Email sent to : {email} Content : username = {user[0].username} password = {user[0].password}\ """) message.setText("Email sent") message.exec_() else: # Not found message.setText("Username not found") message.exec_()
def insertUserToDatabaseAndRenderPage(): global _addUserPopup_A_1, _content_A_1 _userUsername = _addUserPopup_A_1.findChild(QTextEdit, "userUsername") _userPassword = _addUserPopup_A_1.findChild(QTextEdit, "userPassword") _userRole = _addUserPopup_A_1.findChild(QTextEdit, "userRole") _userImage = _addUserPopup_A_1.findChild(QTextEdit, "userImage") userUserName = _userUsername.toPlainText() userPassword = _userPassword.toPlainText() userRole = _userRole.toPlainText() userImage = _userImage.toPlainText() query = 'INSERT INTO user(username,password,role,image) values(%s,%s,%s,%s)' format = (userUserName, userPassword, userRole, userImage) execQuery(query, format=format, queryType="INSERT") setupUserContent(_content_A_1)
def loginButtonClicked(window): global _usernameInput_L_2, _passwordInput_L_2 message = QMessageBox() message.setIcon(QMessageBox.Information) message.setWindowTitle("Invalid login") # Get text username = _usernameInput_L_2.text() password = _passwordInput_L_2.text() # Check username and password if len(username) == 0 or len(password) == 0: message.setText("Username or password cannot be empty") message.exec_() return # Query database query = "SELECT * FROM user WHERE username=%s AND password=%s" format = ( username, password, ) user = None try: user = execQuery(query, format) except Exception as e: print(e) # Cek if user found if user: if (user[0].role == "dosen"): initDosenPage(window, user[0]) elif (user[0].role == "mahasiswa"): initMahasiswaPage(window, user[0]) else: initAdminPage(window, user[0]) else: # Not found message.setText("Invalid username or password") message.exec_()
def insertMatkulToDatabaseAndRenderPage(): global _addMatkulPopup_A_2, _content_A_2 _matkulCode = _addMatkulPopup_A_2.findChild(QTextEdit, "matkulCode") _matkulName = _addMatkulPopup_A_2.findChild(QTextEdit, "matkulName") _matkulDescription = _addMatkulPopup_A_2.findChild(QTextEdit, "matkulDescription") _jurusanCode = _addMatkulPopup_A_2.findChild(QTextEdit, "jurusanCode") matkulCode = _matkulCode.toPlainText() matkulName = _matkulName.toPlainText() matkulDescription = _matkulDescription.toPlainText() jurusanCode = _jurusanCode.toPlainText() query = 'INSERT INTO mata_kuliah(kode_matkul,nama_matkul,deskripsi_matkul,kode_jurusan) values(%s,%s,%s,%s)' format = (matkulCode, matkulName, matkulDescription, jurusanCode) execQuery(query, format=format, queryType="INSERT") setupMatkulContent(_content_A_2)
def setupQuery(auth, profile): # If first time, get mahasiswa profile if profile is None: try: query = """SELECT * FROM user JOIN profil_mahasiswa ON user.id=profil_mahasiswa.user_id_mahasiswa WHERE user.id=%s""" format = (auth.id,) profile = execQuery(query, format)[0] except Exception as e: print(e) return profile
def setupMatkulSection(content, profile, matkulList): global _mainVLayout_M_5 # Add table header # Setup label title = QLabel() header = QLabel() hBox = QHBoxLayout() header.setLayout(hBox) header.setFixedHeight(75) header.setStyleSheet("background-color: black") # Label style styleTitle = "font: 20pt Franklin Gothic Demi Cond" styleHeader = "font: 13pt Franklin Gothic Demi Cond; color: white" # Title Mata Kuliah yang dapat diambil title.setText("\nMata Kuliah yang Dapat Diambil") title.setAlignment(Qt.AlignLeft) title.setStyleSheet(styleTitle) # Header Nama Mata Kuliah namaHeader = QLabel() namaHeader.setText("Nama Mata Kuliah") namaHeader.setAlignment(Qt.AlignCenter) namaHeader.setStyleSheet(styleHeader) # Header Jadwal Mata Kuliah matkulHeader = QLabel() matkulHeader.setText("Jadwal") matkulHeader.setAlignment(Qt.AlignCenter) matkulHeader.setStyleSheet(styleHeader) # Header Daftar Mata Kuliah hadirHeader = QLabel() hadirHeader.setText("") hadirHeader.setAlignment(Qt.AlignCenter) hadirHeader.setStyleSheet(styleHeader) # Set in hBox hBox.addWidget(namaHeader) hBox.addWidget(matkulHeader) hBox.addWidget(hadirHeader) # Set in mainVLayout _mainVLayout_M_5.addWidget(title) _mainVLayout_M_5.addWidget(header) # Add table row # Label style styleRow = "font: 13pt Franklin Gothic Demi Cond; border: 1px solid blue" map = {} matkulDiambilList = execQuery("SELECT * FROM mata_kuliah_diambil") assert matkulDiambilList is not None for i, matkul in enumerate(matkulList): # Setup label row = QLabel() hBox = QHBoxLayout() row.setLayout(hBox) row.setFixedHeight(75) row.setStyleSheet("border: 2px solid black") # Row Nama Mata Kuliah namaRow = QLabel() namaRow.setText(matkul.nama_matkul) namaRow.setAlignment(Qt.AlignCenter) namaRow.setStyleSheet(styleRow) # Row Jadwal Mata Kuliah matkulRow = QLabel() matkulRow.setText(matkul.hari + ", " + str(matkul.waktu_mulai) + " - " + str(matkul.waktu_selesai)) matkulRow.setAlignment(Qt.AlignCenter) matkulRow.setStyleSheet(styleRow) # Row Daftar Mata Kuliah daftarCheckBox = QCheckBox() for matkulDiambil in matkulDiambilList: if matkulDiambil.nim == profile.nim and matkulDiambil.kode_matkul == matkul.kode_matkul: daftarCheckBox.setChecked(True) daftarCheckBox.setEnabled(False) map[i] = (daftarCheckBox, matkul) # Set in hBox hBox.addWidget(namaRow) hBox.addWidget(matkulRow) hBox.addWidget(map[i][0]) # Set in mainVLayout _mainVLayout_M_5.addWidget(row) # Setup connection for key in map.keys(): map[key][0].toggled.connect(lambda _, k=key: daftarCheckboxChanged(map[k][0], profile, map[k][1]))
def getUsers(): query = """SELECT * FROM user""" users = execQuery(query) return users
def removeUser(userId): query = "DELETE FROM user WHERE id = %s" format = (userId, ) execQuery(query, format=format, queryType="DELETE")
def submitButtonClicked(profile): global _nimInput_D_5, _angkatanInput_D_5, _namaInput_D_5, \ _tempatLahirInput_D_5, _tanggalLahirInput_D_5, _alamatRumahInput_D_5, \ _alamatTinggalInput_D_5, _emailInput_D_5, _kodeJurusanInput_D_5, \ _userDefaultInput_D_5, _passDefaultInput_D_5 # Get value nim = _nimInput_D_5.displayText() angkatan = _angkatanInput_D_5.displayText() nama = _namaInput_D_5.displayText() tempatLahir = _tempatLahirInput_D_5.displayText() tanggalLahir = _tanggalLahirInput_D_5.displayText() alamatRumah = _alamatRumahInput_D_5.displayText() alamatTinggal = _alamatTinggalInput_D_5.displayText() email = _emailInput_D_5.displayText() kodeJurusan = _kodeJurusanInput_D_5.displayText() userDefault = _userDefaultInput_D_5.displayText() passDefault = _passDefaultInput_D_5.displayText() message = QMessageBox() message.setIcon(QMessageBox.Information) # Check if username or nim duplicate try: userList = execQuery( """SELECT username FROM user WHERE username=%s""", (userDefault, )) if userList: message.setWindowTitle("Username error") message.setText("Username has already been taken") message.exec_() return mhsList = execQuery( """SELECT nim FROM profil_mahasiswa WHERE nim=%s""", (nim, )) if mhsList: message.setWindowTitle("NIM error") message.setText("NIM has already been taken") message.exec_() return except Exception as e: print(e) message.setWindowTitle("Query error") message.setText("There seems to be an error in the query process") message.exec_() return # Insert query to user table try: query = """INSERT INTO user (username, password, role, image) VALUES (%s, %s, %s, %s)""" format = ( userDefault, passDefault, "mahasiswa", "-", ) db = getDatabase() cursor = db.cursor() cursor.execute(query, format) db.commit() # Get user id query = """SELECT id FROM user WHERE username=%s""" format = (userDefault, ) id = execQuery(query, format)[0].id except Exception as e: print(e) return # Insert query to profil_mahasiswa table try: # (nim, angkatan, nama_mahasiswa, tempat_lahir, tanggal_lahir, # alamat_rumah, alamat_tinggal, email, user_id_mahasiswa, kode_jurusan, # nip_dosen_wali) query = """INSERT INTO profil_mahasiswa VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""" format = (nim, angkatan, nama, tempatLahir, tanggalLahir, alamatRumah, alamatTinggal, email, id, kodeJurusan, profile.NIP) db = getDatabase() cursor = db.cursor() cursor.execute(query, format) db.commit() message.setWindowTitle("Process successful") message.setText("User mahasiswa has been added successfully") message.exec_() except Exception as e: # Rollback user table query = """DELETE FROM user WHERE username=%s""" format = (userDefault, ) db = getDatabase() cursor = db.cursor() cursor.execute(query, format) db.commit() print(e)
def getJadwals(): query = """SELECT kode_matkul, waktu_mulai, waktu_selesai, hari FROM waktu NATURAL JOIN slot_waktu_jadwal NATURAL JOIN slot_waktu""" jadwals = execQuery(query) return jadwals
def getMatkuls(): query = """SELECT * FROM mata_kuliah""" matkuls = execQuery(query) return matkuls
def removeMatkulAndRender(matkulId): global _content_A_2 query = "DELETE FROM mata_kuliah WHERE kode_matkul = %s" format = (matkulId, ) execQuery(query, format=format, queryType="DELETE") setupMatkulContent(_content_A_2)