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 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 CheckScryptFromIDAction(self, idAction):
data = DataBaseModule.GetData(
"""
SELECT scrypt FROM botdb.actiontab
WHERE id = '"""+str(idAction)+"';"
)
return data[0]['scrypt']
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 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 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 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 GetIDGroupListFromIDContext(self, idContext):
data = DataBaseModule.GetData(
"""
SELECT idGroup FROM accesstab
WHERE idContext ='"""+str(idContext)+"';"
)
return [rec['idGroup'] for rec in data]
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 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 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 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 getTeacherList(self, idTeacher, numDay):
sql = "SELECT timetable.id as id, timetable.discipline as discipline, " \
"timetable.numDay as numDay, timetable.numLesson as numLesson, " \
"cathGroup.name as nameGroup, audtable.num as numAud " \
"FROM (riidb.timetable INNER JOIN riidb.cathGroup ON " \
"timetable.idCathGroup = cathGroup.id) INNER JOIN riidb.audtable " \
"ON audtable.id = timetable.idAud " \
"WHERE timetable.idTeacher = '%s' AND timetable.numDay = '%s' " \
"ORDER BY timetable.numLesson;" \
% (idTeacher,numDay)
data = DBM.GetData(sql=sql, nameDB='riidb')
return data
def getFromID(self, id):
sql = "SELECT id as idClient, fio, shortfio, idClientGroup,\
idInfo FROM riidb.clients WHERE id='%s';"%str(id)
rec = DataBaseModule.GetData(nameDB='riidb', sql=sql)
if len(rec):
rec = rec[0]
if rec['idClientGroup'] == 3:
sql = "SELECT id as idCathGroup, name as nameCathGroup, cource," \
" idCathedra, idCurator FROM riidb.cathGroup " \
"WHERE id='%s';"%str(rec['idClientGroup'])
else:
sql = "SELECT teacherinfo.dolzhnost as dozhnost, teacherinfo.obrazovanie " \
"as obrazovanie, teacherinfo.stepen as stepen, teacherinfo.zvanie " \
"as zvanie, teacherinfo.kvalifikacia as kvalifikacia, teacherinfo.idCath " \
"as idCath, cathedra.name as nameCath " \
"FROM riidb.teacherinfo INNER JOIN riidb.cathedra " \
"ON teacherinfo.idCath = cathedra.id" \
" WHERE teacherinfo.id='%s';"%rec['idInfo']
rec = {**rec, **DataBaseModule.GetData(nameDB='riidb', sql=sql)[0]}
return rec
def getGroupList(self, idGroup, numDay):
sql = "SELECT timetable.id as id, timetable.discipline as discipline, " \
"timetable.numDay as numDay, timetable.numLesson as numLesson, " \
"clients.shortfio as fioTeacher, audtable.num as numAud " \
"FROM (riidb.timetable INNER JOIN riidb.clients " \
"ON timetable.idTeacher = clients.id " \
"AND timetable.idCathGroup = '%s' " \
"AND timetable.numDay='%s') " \
"INNER JOIN riidb.audtable ON audtable.id = timetable.idAud " \
"ORDER BY timetable.numLesson;" \
% (idGroup,numDay)
data = DBM.GetData(sql=sql, nameDB='riidb')
return data
def getList(self, idCath=0):
if idCath:
sql = "SELECT cathGroup.id as id, cathGroup.name as name, " \
"cathGroup.course as course, clients.fio as fioCur, " \
"cathedra.name as nameCathedra " \
"FROM (riidb.cathGroup INNER JOIN riidb.clients " \
"ON cathGroup.idCurator = clients.id) INNER JOIN " \
"riidb.cathedra ON cathGroup.idCathedra = cathedra.id " \
"AND cathedra.id='%s';" % idCath
else:
sql = "SELECT * FROM cathGroup"
data = DBM.GetData(sql=sql, nameDB='riidb')
return data
def GetIDDictFromLevel(self, level, idGroup):
data = DataBaseModule.GetData(
"""SELECT id, level, idParent FROM contexttab
WHERE level = '""" + str(level) + "';")
retData = [
{
'id': 0,
'level': 0,
'idParent': 0
},
]
for rec in data:
l = self.GetGroupDict(rec['id'])
if {'idGroup': idGroup} in l:
retData.append(rec)
return retData
def getRecordFromIDTelegram(self, idTelegram):
sql = "SELECT id, idRii, idTelegram, idClientGroup " \
"FROM botdb.clientstab " \
"WHERE idTelegram = '%s';" % idTelegram
data = DBM.GetData(sql=sql, nameDB='botdb')
#data = data[0]
if len(data) == 0 or "Error" in data[0].keys():
return 0
else:
data = data[0]
idClient = data['idRii']
idGroup = data['idClientGroup']
data2 = 0
if idGroup == 2:
data2 = Client().getFromID(id=idClient)
elif idGroup == 3:
data2 = CathGroup().getFromID(id=idClient)
if data2:
return {**data, **data2}
else:
return data
def GetChildContextModelFromParentID(self, id):
sql = """SELECT contexttab.id, contexttab.header
FROM botdb.contexttab
WHERE contexttab.idParent='""" + str(id) + "';"
data = DataBaseModule.GetData(sql)
model = QStandardItemModel()
if len(data):
model.setHorizontalHeaderLabels(['id', 'Заголовок контекста'])
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]['header']))
item.setFlags(QtCore.Qt.ItemIsSelectable
| QtCore.Qt.ItemIsEnabled)
model.setItem(i, 1, item)
else:
model.setHorizontalHeaderLabels(['Отсутствуют'])
return model
def getStudentsListFromIDCathGroup(self, idCathGroup):
sql = "SELECT id, fio " \
"FROM riidb.clients " \
"WHERE idInfo='%s';" % idCathGroup
data = DataBaseModule.GetData(sql=sql, nameDB='riidb')
return data
def __Connect(self):
self.__Table = DataBaseModule.GetData('SELECT * FROM questiontab')
def getList(self):
sql = "SELECT * from riidb.projectors"
data = DBM.GetData(sql=sql, nameDB="riidb")
return data
def __RefreshTable(self):
self.__Table = DataBaseModule.GetData('SELECT * FROM answertab')
def GetRecordFromID(self, idContext):
data = DataBaseModule.GetData("""
SELECT id, level, idParent FROM botdb.contexttab
WHERE id = '""" + str(idContext) + "';")
return self.ConvertData(data)
def GetAnswerDictFromContextID(self, idContext):
data = DataBaseModule.GetData("""
SELECT answertab.answer as answer, answertab.idAction as idAction, actiontab.scrypt as executable
FROM botdb.answertab inner join botdb.actiontab on answertab.idAction = actiontab.id
WHERE idContext = '""" + str(idContext) + "';")
return data
def getFromID(self, id):
sql = "SELECT id, name, idCathedra, idCurator, course " \
"FROM riidb.cathGroup " \
"WHERE id ='%s';"%id
record = DBM.GetData(sql=sql, nameDB='riidb')[0]
return record
def __RefreshTable(self):
self.__Table = DataBaseModule.GetData('SELECT * FROM contexttab')
def __refreshTable(self):
self.__Table = DataBaseModule.GetData('SELECT * FROM usergrouptab')