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
