def __QuestionOrAnswerModel(self, idCon, tabAnswer=False): tabNam = str() tabField = str() header = str() dlgField = str() if tabAnswer: tabNam = 'answertab' tabField = 'answer' header = 'Ответ' dlgField = 'idA' else: tabNam = 'questiontab' tabField = 'question' header = 'Вопрос' dlgField = 'idQ' sql = "SELECT botdb." + tabNam + ".id, " + tabNam + "." + tabField + """ FROM botdb.contexttab INNER JOIN botdb.""" + tabNam + """ ON contexttab.id = """ + tabNam + """.idContext WHERE contexttab.id = '""" + str(idCon) + "';" data = DataBaseModule.GetData(sql) model = QStandardItemModel() if tabAnswer: header = ['id', header, 'Действие'] sql = """SELECT actiontab.action FROM botdb.actiontab INNER JOIN (botdb.contexttab INNER JOIN botdb.answertab ON contexttab.id = answertab.idContext) ON actiontab.id = answertab.idAction WHERE contexttab.id = '""" + str(idCon) + "';" colAction = DataBaseModule.GetData(sql) else: header = ['id', header] model.setHorizontalHeaderLabels(header) model.setVerticalHeaderLabels([' '] * len(data)) for i in range(len(data)): item = QStandardItem(str(data[i]['id'])) item.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled) model.setItem(i, 0, item) item = QStandardItem(str(data[i][tabField])) item.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled) model.setItem(i, 1, item) if tabAnswer: item = QStandardItem(colAction[i]['action']) item.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled) model.setItem(i, 2, item) return model
def CheckScryptFromIDAction(self, idAction): data = DataBaseModule.GetData( """ SELECT scrypt FROM botdb.actiontab WHERE id = '"""+str(idAction)+"';" ) return data[0]['scrypt']
def getTVModelRII(self): data = self.getAllData() for i in range(len(data)): data2 = 0 idGroup = data[i]['idClientGroup'] if idGroup == 2: data2 = Client().getFromID(data[i]['idRii']) data2['nameClientGroup'] = 'Преподаватель' elif idGroup == 3: data2 = CathGroup().getFromID(id=data[i]['idRii']) if data2: data2['shortfio'] = data2['name'] data2['nameClientGroup'] = 'Студент' if data2: if 'id' in data2.keys(): del data2['id'] data[i] = {**data[i], **data2} else: data[i]['shortfio'] = "-" data[i]['nameClientGroup'] = 'Администратор' fieldsTable = ['id', 'idTelegram', 'shortfio', 'nameClientGroup'] fieldsView = [ 'id', 'idTelegram', 'ФИО/группа клиента', 'Группа клиентов' ] model = DBM.CreateTableViewModelFromData(data=data, fieldTab=fieldsTable, fieldsView=fieldsView) return model
def GetTableViewModel(self): """model = QStandardItemModel() model.setHorizontalHeaderLabels(['id','Заголовок контекста', 'id Родителя', 'Группы пользователей']) model.setVerticalHeaderLabels([' ']*len(self.__Table)) for i in range(len(self.__Table)): item = QStandardItem(str(self.__Table[i]['id'])) item.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled) model.setItem(i, 0, item) item = QStandardItem(str(self.__Table[i]['header'])) item.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled) model.setItem(i, 1, item) item = QStandardItem(str(self.__Table[i]['idParent'])) item.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled) model.setItem(i, 2, item) item = QStandardItem(str(self.__Table[i]['idGroupStr'])) item.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled) model.setItem(i, 3, item)""" sql = 'SELECT * FROM contexttab' model = DataBaseModule.CreateTableViewModel( sql, ['id', 'header', 'idParent', 'level'], ['id', 'Заголовок контекста', 'id Родителя', 'Уровень']) return model
def deleteTeacher(self, id): record = self.getFromID(id) self._deleteRecord(record['idClient']) if record['idClientGroup'] == 2: sql = "DELETE FROM riidb.teacherinfo " \ "WHERE id ='%s'"%record['idInfo'] DataBaseModule.ExecuteSQL(sql=sql, nameDB='riidb')
def GetAnswerAndActionFromAnswerID(self, id): data = DataBaseModule.GetData(""" SELECT answertab.answer as 'ans', actiontab.action as 'act', actiontab.id as 'idAction' FROM botdb.answertab INNER JOIN botdb.actiontab ON answertab.idAction = actiontab.id WHERE answertab.id = '""" + str(id) + "';") return (data[0]['ans'], data[0]['act'])
def getTVTeachersModel(self, idCathedra=0): if not idCathedra: sql = "SELECT clients.id as id, clients.fio as fio, " \ "teacherinfo.dolzhnost as dolzhnost, teacherinfo.obrazovanie as obrazovanie, " \ "teacherinfo.stepen as stepen, teacherinfo.zvanie as zvanie, " \ "teacherinfo.kvalifikacia as kvalifikacia, " \ "cathedra.name as cathedra " \ "FROM (riidb.clients INNER JOIN riidb.teacherinfo " \ "ON clients.idInfo = teacherinfo.id and clients.idClientGroup=2) " \ "INNER JOIN riidb.cathedra ON cathedra.id = teacherinfo.idCath;" else: sql = "SELECT clients.id as id, clients.fio as fio, " \ "teacherinfo.dolzhnost as dolzhnost, teacherinfo.obrazovanie as obrazovanie, " \ "teacherinfo.stepen as stepen, teacherinfo.zvanie as zvanie, " \ "teacherinfo.kvalifikacia as kvalifikacia, " \ "cathedra.name as cathedra " \ "FROM (riidb.clients INNER JOIN riidb.teacherinfo " \ "ON clients.idInfo = teacherinfo.id and clients.idClientGroup=2 ) " \ "INNER JOIN riidb.cathedra ON cathedra.id = teacherinfo.idCath " \ "AND teacherinfo.idCath = '%s';" % idCathedra nameList = ['id', 'fio', 'dolzhnost', 'obrazovanie', 'stepen', 'zvanie', 'kvalifikacia', 'cathedra'] asList = ['id', 'ФИО', 'Должность', 'Образование', "Степень", "Звание", "Квалификация", "Кафедра"] model = DataBaseModule.CreateTableViewModel(sql, nameList, asList, nameDB='riidb') return model
def updateDiscipline(self, idTimeTable, idTeacher, idCathGroup, discipline, numDay, numLesson, idAud): sql = "UPDATE riidb.timetable " \ "SET idTeacher='%s', idCathGroup='%s', discipline='%s', numDay='%s', " \ "numLesson='%s', idAud='%s' " \ "WHERE id='%s'" \ % (idTeacher, idCathGroup, discipline, numDay, numLesson, idAud, idTimeTable) DBM.ExecuteSQL(sql=sql, nameDB='botdb')
def insertDiscipline(self, idTeacher, idCathGroup, discipline, numDay, numLesson, idAud): sql = "INSERT INTO riidb.timetable (idTeacher, idCathGroup, discipline, " \ "numDay, numLesson, idAud) " \ "VALUES ('%s', '%s', '%s', '%s', '%s', '%s');" \ % (idTeacher, idCathGroup, discipline, numDay, numLesson, idAud) index = DBM.ExecuteSQL(sql=sql, nameDB='riidb') return index
def getTVModel(self): sql = "SELECT * FROM botdb.clientstab" fieldsTable = ['id', 'idRii', 'idClientGroup', 'idTelegram'] model = DBM.CreateTableViewModel(sql=sql, fieldsView=fieldsTable, fieldTab=fieldsTable, nameDB='botdb') return model
def getTVModel(self): self.indexingList() fieldsList = ['id', 'title', 'url', 'date'] fieldsView = ['id', 'Заголовок', 'Ссылка', 'Дата'] model = DBM.CreateTableViewModelFromData(data=self.webList, fieldTab=fieldsList, fieldsView=fieldsView) return model
def GetIDGroupListFromIDContext(self, idContext): data = DataBaseModule.GetData( """ SELECT idGroup FROM accesstab WHERE idContext ='"""+str(idContext)+"';" ) return [rec['idGroup'] for rec in data]
def __RefreshTable(self): sql = 'SELECT ' for table in self.tableList: for field in self.fieldList: sql += table + '.' + field + ', ' sql = sql[:-2] + ' FROM ' self.__Table = DataBaseModule.GetData('SELECT * FROM actiontab')
def getTeachersListFromIDCath(self, idCath): sql = "SELECT clients.id as id, clients.shortfio as shortfio " \ "FROM riidb.clients INNER JOIN riidb.teacherinfo " \ "ON clients.idInfo = teacherinfo.id " \ "WHERE clients.idClientGroup=2 and " \ "teacherinfo.idCath='%s';" % idCath data = DataBaseModule.GetData(sql=sql, nameDB='riidb') return data
def getStudentsList(self): sql = "SELECT clients.id as id, clients.fio as fio, " \ "clients.idClientGroup, " \ "cathGroup.name as cathGroup, cathGroup.course as course " \ "FROM riidb.clients INNER JOIN riidb.cathGroup " \ "ON clients.idInfo = cathGroup.id and clients.idClientGroup=3;" data= DataBaseModule.GetData(nameDB='riidb', sql=sql)[0] return data
def GetGroupDict(self, idContext): data = DataBaseModule.GetData(""" SELECT usergrouptab.id as 'idGroup' FROM botdb.usergrouptab INNER JOIN (botdb.contexttab INNER JOIN botdb.accesstab ON contexttab.id = accesstab.idContext ) ON usergrouptab.id = accesstab.idGroup WHERE contexttab.id = '""" + str(idContext) + "';") return self.ConvertData(data)
def AddRecord(self, idGroup, idContext): idG = str(idGroup) idCon = str(idContext) id = DataBaseModule.ExecuteSQL(""" INSERT accesstab (idGroup, idContext) VALUES ('""" + idG +"','" + idCon +"');") return id
def GetChildContextIDList(self, idContext, idGroup): groupDict = self.GetGroupDict(idContext) if {'idGroup': idGroup} in groupDict: data = DataBaseModule.GetData(""" SELECT id, level, idParent FROM botdb.contexttab WHERE idParent = '""" + str(idContext) + "';") return self.ConvertData(data)
def getTVModel(self): sql = "SELECT * from riidb.projectors" fieldsTab = ['id', 'name', 'numAud'] fieldsView = ['id', 'Название', "Ауд."] model = DBM.CreateTableViewModel(sql=sql, fieldsView=fieldsView, fieldTab=fieldsTab, nameDB='riidb') return model
def GetGroupsModelFromID(self, id): sql = """SELECT usergrouptab.id as 'id', usergrouptab.nameGroup as 'nameGroup' FROM botdb.usergrouptab INNER JOIN (botdb.accesstab INNER JOIN botdb.contexttab ON accesstab.idContext = contexttab.id) ON usergrouptab.id = accesstab.idGroup WHERE contexttab.id='""" + str(id) + "';" model = DataBaseModule.CreateTableViewModel(sql, ['id', 'nameGroup'], ['id', 'Группа']) return model
def getRecordFromID(self, id): sql = "SELECT id, idRii, idTelegram, idClientGroup " \ "FROM botdb.clientstab " \ "WHERE id = '%s';" % id data = DBM.GetData(sql=sql, nameDB='botdb')[0] if "Error" in data.keys(): return 0 else: data
def getRecordFromIDRII(self, idRii, idUserGroup): sql = "SELECT id, idRii, idTelegram, idClientGroup " \ "FROM botdb.clientstab " \ "WHERE idRii = '%s' AND idClientGroup = '%s';" % (idRii,idUserGroup) data = DBM.GetData(sql=sql, nameDB='botdb') # data = data[0] if len(data) == 0 or "Error" in data[0].keys(): return 0 else: return data[0]
def UpdateRecord(self,id,action, note, scrypt =0): if scrypt: check =1 else: check = 0 DataBaseModule.ExecuteSQL(''' UPDATE actiontab SET action=\'''' + action +"', note='" + note +"', scrypt ='" + str(check) +"' " + "WHERE id='" + str(id) +"';")
def getTVModel(self): sql = "SELECT id, num FROM riidb.audtable " \ "ORDER BY num" fieldsTable = ['id', 'num'] fieldsView = ['id', 'Номер аудитории'] model = DBM.CreateTableViewModel(sql=sql, fieldTab=fieldsTable, fieldsView=fieldsView, nameDB='riidb') return model
def GetQuestionFromID(self, id): """ for record in self.__Table: if record['id']==id: return record['question'] """ data = DataBaseModule.GetData(""" SELECT question FROM botdb.questiontab WHERE id ='""" + str(id) + "';") return data[0]['question']
def updateTeacher(self, idClient, fio, shortfio, dolzhnost, obrazovanie, stepen, zvanie, kvalifikacia, idCath): idInfo = self.getFromID(idClient)['idInfo'] sql = "UPDATE riidb.teacherinfo " \ "SET dolzhnost='%s', obrazovanie='%s', stepen='%s', zvanie='%s', " \ "kvalifikacia='%s', idCath='%s' " \ "WHERE id='%s';"%(dolzhnost,obrazovanie,stepen,zvanie, kvalifikacia,idCath, idInfo) DataBaseModule.ExecuteSQL(sql,'riidb') self._updateRecord(idClient,fio, shortfio)
def _updateRecord(self, id, fio, shortfio, idInfo=0): if idInfo: sql = "UPDATE riidb.clients " \ "SET fio='%s', shortfio='%s', idInfo='%s' " \ "WHERE id='%s';"%(fio,shortfio,idInfo,id) else: sql = "UPDATE riidb.clients " \ "SET fio='%s', shortfio='%s' " \ "WHERE id='%s';" % (fio,shortfio, id) DataBaseModule.ExecuteSQL(sql=sql, nameDB='riidb')
def getAudInfo(self, id, numDay, numLesson): sql = "SELECT clients.shortfio as teacherFio, timetable.discipline " \ "as discipline " \ "FROM riidb.clients INNER JOIN riidb.timetable " \ "ON clients.id = timetable.idTeacher AND " \ "timetable.idAud ='%s' AND timetable.numDay='%s' " \ "AND timetable.numLesson='%s'" \ % (id, numDay, numLesson) data = DBM.GetData(sql=sql, nameDB='riidb') if len(data): return data[0]
def GetQuestionDictFromContextID(self, idContext, idGroup): groupDict = self.GetGroupDict(idContext) if {'idGroup': idGroup} in groupDict: data = DataBaseModule.GetData(""" SELECT questiontab.id as 'idQ', questiontab.question as 'question', contexttab.id as 'idC', contexttab.level as 'level', contexttab.idParent as 'idParent' FROM botdb.questiontab INNER JOIN botdb.contexttab ON questiontab.idContext = contexttab.id WHERE contexttab.id='""" + str(idContext) + "';") return self.ConvertData(data)
def InsertRecord(self, action, note, scrypt = 0): if scrypt: check =1 else: check = 0 currentid = DataBaseModule.ExecuteSQL(''' INSERT INTO actiontab (action,note,scrypt) VALUES (\'''' + action +"','" + note +"','" + str(check) +"');") return currentid