Диссертация (1167217), страница 76
Текст из файла (страница 76)
Dict of lists [money, agreements]self.coursesReport = {}# how many language groupings. Dict of lists [money, agreements]self.langGroups = {}conn = sqlite3.connect('cdo.db')courses = self.getLstSQL(conn.execute("SELECT * FROM course"))for course in courses:if course[10] not in self.langGroups.keys():self.langGroups[course[10]] = [0, 0]self.coursesReport[course[9]] = [0, 0]# how many agreements (active and not active), money volume.quantActive = self.getStrSQL(conn.execute("SELECT COUNT (*) FROMcoursesfortutor WHERE active=1"))summaActive = self.getStrSQL(conn.execute("SELECT SUM (price) FROMcoursesfortutor WHERE active=1"))quantNotActive = self.getStrSQL(conn.execute("SELECT COUNT (*) FROMcoursesfortutor WHERE active=0"))if quantNotActive > 0:summaNotActive = self.getStrSQL(conn.execute("SELECT SUM (price)FROM coursesfortutor WHERE active=0"))else:summaNotActive = 0summaAll = summaActive + summaNotActivequantAll = quantActive + quantNotActive#self.ui.statusbar.showMessage("ok")self.gridReport = QGridLayout(self.ui.tab_6)if not self.isSetText:self.text = QTextEdit()self.text.setText("General Information:\nActive Agreements: %s - %srub.
\nNot Active Agreements: %s - %s rub.\nAll Agreements: %s - %s rub." %501(str(quantActive), str(summaActive), str(quantNotActive), str(summaNotActive),str(quantAll), str(summaAll)))self.text.append("Average Agreement: %.2f rub.\n" %(summaAll/quantAll))rows = conn.execute("SELECT * FROM coursesfortutor")# WHEREactive=1")lst = self.getLstSQL(rows)#combo for the month reportif not self.isSetText:self.comboYearMonth = QComboBox()self.comboYearMonth.addItem("Choose a Month")today = str(date.today())monthsFromNow = []currMonth = today.split('-')[1]currYear = today.split('-')[0]currYearMinusOne = str(int(currYear)-1)monthsFromNow.append(currYearMinusOne + '-' + currMonth)for m in range(0, 12):currMonth = str(int(currMonth)+1)if int(currMonth) == 13:currYearMinusOne = str(int(currYearMinusOne)+1)currMonth = '01'elif int(currMonth) < 10:currMonth = '0' + currMonthmonthsFromNow.append(currYearMinusOne + '-' + currMonth)monthsFromNow.sort(reverse=True)monthsFromNow2 = []for m in range(0, len(monthsFromNow)):monthsFromNow2.append(monthsFromNow[m].split('-')[0] + "-" +self.monthsName[int(monthsFromNow[m].split('-')[1])-1])monthsFromNow = monthsFromNow2.copy()if not self.isSetText:self.comboYearMonth.addItems(monthsFromNow)# define how many years differyears = []for row in lst:if int(row[7].split('-')[0]) not in years:years.append(int(row[7].split('-')[0]))years.sort()self.text.append("Year Report (All Agreements)")#list of dicts for the html fileself.lstDict = []for year in years[::-1]:rows = conn.execute("SELECT SUM (price) FROM coursesfortutorWHERE agreemdate LIKE '%s%%'" % str(year)) #active=1 ANDquant = conn.execute("SELECT COUNT (*) FROM coursesfortutorWHERE agreemdate LIKE '%s%%'" % str(year)) #active=1 ANDself.text.append(str(year) + ' - ' + str(self.getStrSQL(quant))+ ' - ' + str(self.getStrSQL(rows)))rows = conn.execute("SELECT * FROM coursesfortutor WHEREagreemdate LIKE '%s%%'" % str(year)) #active=1 ANDcurrentYearLst = self.getLstSQL(rows)502dict1 = {}for cyl in currentYearLst:iMonth = int(cyl[7].split('-')[1])if iMonth in dict1.keys():dict1[iMonth][0] += cyl[15]dict1[iMonth][1] += 1else:dict1[iMonth] = [cyl[15], 1]#report by languageslang = self.getStrSQL(conn.execute("SELECT courseLang FROMcourse WHERE id=%d" % cyl[2]))self.langGroups[lang][0] += cyl[15]self.langGroups[lang][1] += 1#report by coursescourse = self.getStrSQL(conn.execute("SELECT courseshortFROM course WHERE id=%d" % cyl[2]))self.coursesReport[course][0] += cyl[15]self.coursesReport[course][1] += 1for m in range(0, len(self.monthsName)):if (m+1) in dict1.keys():mResult = self.monthsName[m] + ' : ' +str(dict1[m+1][1]) + ' - ' + str(dict1[m+1][0])else:mResult = self.monthsName[m] + ' : -'self.text.append('\t' + mResult)#get info for the html chartself.lstDict.append(dict1)self.text.append('\nGroupings:')# report by langs output sortedod = collections.OrderedDict(sorted(self.langGroups.items()))for k, v in od.items():self.text.append("\t" + k + ": " + str(v[0]) + " - " +str(v[1]))# report by courses outputself.text.append('\nCourses:')#sort coursesod = collections.OrderedDict(sorted(self.coursesReport.items()))self.coursesReport = {}for k, v in od.items():self.coursesReport[k] = vself.text.append("\t" + k + ": " + str(v[0]) + " - " +str(v[1]))"""for course in self.coursesReport:self.text.append("\t" + course + ": " +str(self.coursesReport[course][1]) + " - " + str(self.coursesReport[course][0]))"""#get info for the html chartself.years = years[::-1]#text.setStyleSheet("color: black;")#text.setEnabled(False)503#self.comboYearMonth.currentIndexChanged.connect(self.getMonthReport)if not self.isSetText:self.gridReport.addWidget(self.comboYearMonth)comboBtn = QPushButton("Generate")comboBtn.clicked.connect(self.getMonthReport)self.gridReport.addWidget(comboBtn)self.gridReport.addWidget(self.text)reportBtn = QPushButton("Printable General Report")reportBtn.clicked.connect(self.printableChartGen)self.gridReport.addWidget(reportBtn)self.comboYearMonth.setCurrentIndex(0)conn.close()self.ui.tabWidget.setCurrentIndex(6)self.genReportText = self.text.toPlainText()def getMonthReport(self):self.isSetText = Trueindx = self.comboYearMonth.currentText()#self.text.clear()self.text.setText(indx)conn = sqlite3.connect('cdo.db')# take all coursesmonthRepDataLst = self.getLstSQL(conn.execute("SELECT * FROMcoursesfortutor"))# WHERE active=1"))# find out how many tutors are active # all courses nowtutorsId = []agreements = [] # alteredfor t in monthRepDataLst:if t[3] not in tutorsId:tutorsId.append(t[3])agreements.append(t[6]) # altered#get agreement numbers""" alteredagreements = []for table in self.newTables:#tableLst = self.getLstSQL(conn.execute("SELECT * FROM %s" %table))agreements.append(table[1:-2] + "/" + table[-2:] + "-ДО")"""# output by tutor namesmonthPayments = []for id in tutorsId:fio = self.getLstSQL(conn.execute("SELECT * FROM tutor WHEREid=%d" % id))fullname = fio[0][1] + " " + fio[0][2] + " " + fio[0][3]#self.text.append(fullname)agreementOfTheCurrentTutor = self.getLstSQL(conn.execute("SELECT* FROM coursesfortutor WHERE tutor=%d" % id))nameAccu = []for agr in agreementOfTheCurrentTutor:if agr[6] in agreements:504coursename = self.getStrSQL(conn.execute("SELECTcoursename FROM course WHERE id=%s" % agr[2]))vcPrice = self.getStrSQL(conn.execute("SELECTcourseVCprice FROM course WHERE id=%s" % agr[2]))wtPrice = self.getStrSQL(conn.execute("SELECTcourseWTprice FROM course WHERE id=%s" % agr[2]))#self.text.append('\t' + agr[6] + ' - ' + coursename)isThereAMonth = self.getStrSQL(conn.execute("SELECTCOUNT (*) FROM %s WHERE month='%s'" % (("t" + agr[6].replace("/", "")[:-3]),indx)))%s"if int(isThereAMonth) > 0:if fullname not in nameAccu:self.text.append(fullname)nameAccu.append(fullname)self.text.append('\t' + agr[6] + ' - ' + coursename)work = self.getLstSQL(conn.execute("SELECT * FROM% ("t" + agr[6].replace("/", "")[:-3])))for my in work:if my[1] == indx:if my[2] == None:my[2] = 0if my[3] == None:my[3] = 0if my[4] == None:my[4] = 0monthPayments.append(my[2]*vcPrice+my[3]*wtPrice)self.text.append('\t\tVideo Conferences - '+ str(my[2]) + ", Open Tasks - " + str(my[3]) + " = %d" %(my[2]*vcPrice+my[3]*wtPrice))else:pass#self.text.append('\t\t- -' )conn.close()self.text.append("For the given month: " +str((sum(monthPayments))))def getMonthReportForYear_payments(self):# months from 09.2016 -- all agreements -m = date.today().monthy = date.today().yeary2 = y-2016m2 = 9res = (y2*12+(m-m2))+1monthsFromThen = []data = []for i in range(0, res):monthsFromThen.append(str(y) + '-' + self.monthsName[int(m)-1])m -= 1if m == 0:m = 12y -= 1conn = sqlite3.connect('cdo.db')505monthRepDataLst = self.getLstSQL(conn.execute("SELECT * FROMcoursesfortutor WHERE active=1 OR active=0"))#get agreement numbersagreements = []for row in monthRepDataLst:agr = row[6].split('-')[0]agr = agr.replace('/', '')agr = 't' + agragreements.append(agr)# iterate through months from the current and to the past# accumulatoraccumulator = 0accuMan = 0for m in monthsFromThen:# iterate through all agreements searching for month's datalstVC = []lstWT = []lstMan = []for agr in agreements:agr2 = agr[1:-2] + "/" + agr[-2:] + "-ДО"courseId = int(self.getStrSQL(conn.execute("SELECT courseFROM coursesfortutor WHERE agreement='%s'" % agr2)))vcPrice = self.getStrSQL(conn.execute("SELECT courseVCpriceFROM course WHERE id=%d" % courseId))wtPrice = self.getStrSQL(conn.execute("SELECT courseWTpriceFROM course WHERE id=%d" % courseId))#managing = self.getStrSQL(conn.execute("SELECTcourseManaging FROM course WHERE id=%d" % courseId))quant = int(self.getStrSQL(conn.execute("SELECT COUNT (*)FROM %s WHERE month='%s'" % (agr, m))))if quant > 0:lst = self.getLstSQL(conn.execute("SELECT vconf,opentask, managing FROM %s WHERE month='%s'" % (agr, m)))if lst[0][0]:lstVC.append(lst[0][0]*vcPrice)else:lstVC.append(0)if lst[0][1]:lstWT.append(lst[0][1]*wtPrice)else:lstWT.append(0)if lst[0][2]:lstMan.append(lst[0][2])else:lstMan.append(0)data.append(sum(lstVC) + sum(lstWT) + sum(lstMan))accumulator += sum(lstVC) + sum(lstWT) + sum(lstMan)accuMan += sum(lstMan)#print(sum(lstVC) + sum(lstWT) + sum(lstMan))conn.close()#print(accuMan*.87)#self.text.append("For this month: " +str((sum(self.monthPayments))))506txt = """<!DOCTYPE HTML><html><head><scriptsrc="Chart.js"></script><style>@media print { footer { page-break-after: always; }} body { background: white; padding: 16px; } canvas { border: 1px dotted navy;}</style></head><body>"""txt += "<h2>Month Payments Report - All Agreements</h2>"txt += "<p>Total: <b>%d</b> rub.</p>" % accumulatortxt += "<p>+30,2%%: <b>%.2f</b> rub.</p>" % (accumulator*1.302)f = open('chartstringOne.txt', 'r', encoding='utf-8')rawStr = f.read()f.close()rawStr = rawStr.replace("$$chartid$$", "'chart_payments'")rawStr = rawStr.replace("$$labels$$", str(monthsFromThen[::-1]))rawStr = rawStr.replace("$$datasetname$$", "'payments'")rawStr = rawStr.replace("$$data$$", str(data[::-1]))txt += rawStr############################################################# active agreements ####################################################################################################m = date.today().monthy = date.today().yeary2 = y-2016m2 = 9res = (y2*12+(m-m2))+1monthsFromThen = []data = []for i in range(0, res):monthsFromThen.append(str(y) + '-' + self.monthsName[int(m)-1])m -= 1if m == 0:m = 12y -= 1conn = sqlite3.connect('cdo.db')monthRepDataLst = self.getLstSQL(conn.execute("SELECT * FROMcoursesfortutor WHERE active=1"))#get agreement numbersagreements = []for row in monthRepDataLst:agr = row[6].split('-')[0]agr = agr.replace('/', '')agr = 't' + agragreements.append(agr)# iterate through months from the current and to the past# accumulatoraccumulator = 0for m in monthsFromThen:# iterate through all agreements searching for month's datalstVC = []lstWT = []lstMan = []for agr in agreements:agr2 = agr[1:-2] + "/" + agr[-2:] + "-ДО"courseId = int(self.getStrSQL(conn.execute("SELECT courseFROM coursesfortutor WHERE agreement='%s'" % agr2)))vcPrice = self.getStrSQL(conn.execute("SELECT courseVCpriceFROM course WHERE id=%d" % courseId))507wtPrice = self.getStrSQL(conn.execute("SELECT courseWTpriceFROM course WHERE id=%d" % courseId))#managing = self.getStrSQL(conn.execute("SELECTcourseManaging FROM course WHERE id=%d" % courseId))quant = int(self.getStrSQL(conn.execute("SELECT COUNT (*)FROM %s WHERE month='%s'" % (agr, m))))if quant > 0:lst = self.getLstSQL(conn.execute("SELECT vconf,opentask, managing FROM %s WHERE month='%s'" % (agr, m)))if lst[0][0]:lstVC.append(lst[0][0]*vcPrice)else:lstVC.append(0)if lst[0][1]:lstWT.append(lst[0][1]*wtPrice)else:lstWT.append(0)if lst[0][2]:lstMan.append(lst[0][2])else:lstMan.append(0)data.append(sum(lstVC) + sum(lstWT) + sum(lstMan))accumulator += sum(lstVC) + sum(lstWT) + sum(lstMan)#print(sum(lstVC) + sum(lstWT) + sum(lstMan))conn.close()txt += "<br/><footer>%s</footer>" % str(datetime.now())txt += "<h2>Month Payments Report - Active Agreements</h2>"txt += "<p>Total: <b>%d</b> rub.</p>" % accumulatortxt += "<p>+30,2%%: <b>%.2f</b> rub.</p>" % (accumulator*1.302)f = open('chartstringOne.txt', 'r', encoding='utf-8')rawStr = f.read()f.close()rawStr = rawStr.replace("$$chartid$$", "'chart_payments2'")rawStr = rawStr.replace("$$labels$$", str(monthsFromThen[::-1]))rawStr = rawStr.replace("$$datasetname$$", "'payments2'")rawStr = rawStr.replace("$$data$$", str(data[::-1]))txt += rawStr############################################################# not active agreements ################################################################################################m = date.today().monthy = date.today().yeary2 = y-2016m2 = 9res = (y2*12+(m-m2))+1monthsFromThen = []data = []for i in range(0, res):monthsFromThen.append(str(y) + '-' + self.monthsName[int(m)-1])m -= 1if m == 0:m = 12y -= 1conn = sqlite3.connect('cdo.db')508monthRepDataLst = self.getLstSQL(conn.execute("SELECT * FROMcoursesfortutor WHERE active=0"))#get agreement numbersagreements = []for row in monthRepDataLst:agr = row[6].split('-')[0]agr = agr.replace('/', '')agr = 't' + agragreements.append(agr)# iterate through months from the current and to the past# accumulatoraccumulator = 0for m in monthsFromThen:# iterate through all agreements searching for month's datalstVC = []lstWT = []lstMan = []for agr in agreements:agr2 = agr[1:-2] + "/" + agr[-2:] + "-ДО"courseId = int(self.getStrSQL(conn.execute("SELECT courseFROM coursesfortutor WHERE agreement='%s'" % agr2)))vcPrice = self.getStrSQL(conn.execute("SELECT courseVCpriceFROM course WHERE id=%d" % courseId))wtPrice = self.getStrSQL(conn.execute("SELECT courseWTpriceFROM course WHERE id=%d" % courseId))#managing = self.getStrSQL(conn.execute("SELECTcourseManaging FROM course WHERE id=%d" % courseId))quant = int(self.getStrSQL(conn.execute("SELECT COUNT (*)FROM %s WHERE month='%s'" % (agr, m))))if quant > 0:lst = self.getLstSQL(conn.execute("SELECT vconf,opentask, managing FROM %s WHERE month='%s'" % (agr, m)))if lst[0][0]:lstVC.append(lst[0][0]*vcPrice)else:lstVC.append(0)if lst[0][1]:lstWT.append(lst[0][1]*wtPrice)else:lstWT.append(0)if lst[0][2]:lstMan.append(lst[0][2])else:lstMan.append(0)data.append(sum(lstVC) + sum(lstWT) + sum(lstMan))accumulator += sum(lstVC) + sum(lstWT) + sum(lstMan)#print(sum(lstVC) + sum(lstWT) + sum(lstMan))conn.close()txt += "<br/><footer>%s</footer>" % str(datetime.now())txt += "<h2>Month Payments Report - Not Active Agreements</h2>"txt += "<p>Total: <b>%d</b> rub.</p>" % accumulatortxt += "<p>+30,2%%: <b>%.2f</b> rub.</p>" % (accumulator*1.302)f = open('chartstringOne.txt', 'r', encoding='utf-8')rawStr = f.read()509f.close()rawStr = rawStr.replace("$$chartid$$", "'chart_payments3'")rawStr = rawStr.replace("$$labels$$", str(monthsFromThen[::-1]))rawStr = rawStr.replace("$$datasetname$$", "'payments3'")rawStr = rawStr.replace("$$data$$", str(data[::-1]))txt += rawStrtxt += "</body></html>"fName = 'month_output.html'f = open(fName, 'w', encoding='utf-8')f.write(txt)f.close()self.ui.statusbar.showMessage("file %s was successfully generated" %fName, 2000)def monthPayment(self):self.getMonthReportForYear_payments()def pureIncome(self):passdef printableChartGen(self):txt = """<!DOCTYPE HTML><html><head><scriptsrc="Chart.js"></script><style>@media print { footer { page-break-after: always; }} body { background: white; padding: 16px; } canvas { border: 1px dotted navy;}</style></head><body>"""txt += "<h2>Центр дистанционного обучения МГЛУ</h2><h2>GeneralReport</h2>"counter = 0for year in self.years:f = open('chartstring.txt', 'r', encoding='utf-8')rawStr = f.read()f.close()rawStr = rawStr.replace("$$chartid$$", "'chart_%s'" % str(year))rawStr = rawStr.replace("$$labels$$", str(self.monthsName))rawStr = rawStr.replace("$$datasetname$$", "'%s_income'" %str(year))rawStr = rawStr.replace("$$datasetname2$$", "'%s_agreements'" %str(year))data = []data2 = []for m in range(len(self.monthsName)):if (m+1) in self.lstDict[counter].keys():data.append(self.lstDict[counter].get(m+1)[0]/1000)data2.append(self.lstDict[counter].get(m+1)[1])else:data.append(0)data2.append(0)rawStr = rawStr.replace("$$data$$", str(data))rawStr = rawStr.replace("$$data2$$", str(data2))txt += rawStr + "<br/>"counter += 1txt += "<footer>%s</footer>" % str(datetime.now())txt += "<h2>By Languages</h2>"f = open('chartstring.txt', 'r', encoding='utf-8')510rawStr = f.read()f.close()rawStr = rawStr.replace("$$chartid$$", "'chart_groups'")lgSorted = list(self.langGroups.keys())lgSorted.sort()rawStr = rawStr.replace("$$labels$$", str(lgSorted))rawStr = rawStr.replace("$$datasetname$$", "'income'")rawStr = rawStr.replace("$$datasetname2$$", "'agreements'")data = []data2 = []for g in lgSorted:data.append(self.langGroups[g][0]/1000)data2.append(self.langGroups[g][1])rawStr = rawStr.replace("$$data$$", str(data))rawStr = rawStr.replace("$$data2$$", str(data2))txt += rawStr + "<br/><footer>%s</footer>" % str(datetime.now())#Doughnut by languagestxt += "<h2>By Languages 2</h2>"f = open('daughnutstringOne.txt', 'r', encoding='utf-8')rawStr = f.read()f.close()rawStr = rawStr.replace("$$chartid2$$", "'chart_groups_dn'")lgSorted = list(self.langGroups.keys())lgSorted.sort()rawStr = rawStr.replace("$$labels2$$", str(lgSorted))rawStr = rawStr.replace("$$datasetname2$$", "'income'")# randomize colorscolors = []for l in range(len(lgSorted)):color = "rgba(%d, %d, %d, 0.4)" % (random.randint(0, 255),random.randint(0, 255), random.randint(0, 255))colors.append(color)rawStr = rawStr.replace("$$colors$$", str(colors))data = []data2 = []summa = 0for g in lgSorted:summa += self.langGroups[g][0]for g in lgSorted:data.append("%.2f" % (self.langGroups[g][0]/summa*100))#data.append(self.langGroups[g][0]/1000)data2.append(self.langGroups[g][1])rawStr = rawStr.replace("$$data$$", str(data))txt += rawStr + "<br/>"txt += "<h2>By Courses</h2>"f = open('chartstring.txt', 'r', encoding='utf-8')rawStr = f.read()f.close()rawStr = rawStr.replace("$$chartid$$", "'chart_courses'")crSorted = list(self.coursesReport.keys())crSorted.sort()511rawStr = rawStr.replace("$$labels$$", str(crSorted))rawStr = rawStr.replace("$$datasetname$$", "'income'")rawStr = rawStr.replace("$$datasetname2$$", "'agreements'")data = []data2 = []for c in crSorted:data.append(self.coursesReport[c][0]/1000)data2.append(self.coursesReport[c][1])rawStr = rawStr.replace("$$data$$", str(data))rawStr = rawStr.replace("$$data2$$", str(data2))txt += rawStr + "<br/><footer>%s</footer>" % str(datetime.now())#Doughnut by coursestxt += "<h2>By Courses 2</h2>"f = open('daughnutstringOne.txt', 'r', encoding='utf-8')rawStr = f.read()f.close()rawStr = rawStr.replace("$$chartid2$$", "'chart_courses_dn'")crSorted = list(self.coursesReport.keys())crSorted.sort()rawStr = rawStr.replace("$$labels2$$", str(crSorted))rawStr = rawStr.replace("$$datasetname2$$", "'income'")# randomize colorscolors = []for l in range(len(crSorted)):color = "rgba(%d, %d, %d, 0.4)" % (random.randint(0, 255),random.randint(0, 255), random.randint(0, 255))colors.append(color)rawStr = rawStr.replace("$$colors$$", str(colors))data = []data2 = []summa = 0for c in crSorted:summa += self.coursesReport[c][0]for c in crSorted:data.append("%.2f" % (self.coursesReport[c][0]/summa*100))data2.append(self.coursesReport[c][1])rawStr = rawStr.replace("$$data$$", str(data))txt += rawStr + "<br/>"txt += "<footer>%s</footer>" % str(datetime.now())self.genReportText = self.genReportText.replace("\n","<br/>").replace("\t", " "*5)txt += "<p>%s</p>" % self.genReportTexttxt += "</body></html>"fName = 'output.html'f = open(fName, 'w', encoding='utf-8')f.write(txt)f.close()self.ui.statusbar.showMessage("file %s was successfully generated" %fName, 2000)def showTableCourses(self):conn = sqlite3.connect('cdo.db')512if int(self.getStrSQL(conn.execute("SELECT COUNT (*) FROM course")))> 0:rows = conn.execute("SELECT * FROM course")tabLst = []for row in rows:self.cells = []for cell in row:self.cells.append(cell) # cellstabLst.append(self.cells) # rows#print(tabLst)self.tableWidget1 = QTableWidget()r = len(tabLst)c = len(self.cells)self.tableWidget1.setRowCount(r)self.tableWidget1.setColumnCount(c)count = 0headerLst = conn.execute("PRAGMA table_info(course)")for header in headerLst:self.tableWidget1.setHorizontalHeaderItem(count,QTableWidgetItem(str(header[1])))count += 1for i in range(0, r):for ii in range(0, c):# not editable cellstwi = QTableWidgetItem(str(tabLst[i][ii]))self.tableWidget1.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)self.ui.gridLayout_2.addWidget(self.tableWidget1)self.tableWidget1.setSortingEnabled(True)self.tableWidget1.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)self.tableWidget1.setSelectionBehavior(QtWidgets.QAbstractItemView.SelectRows)self.tableWidget1.setStyleSheet("QTableWidget {color: black;}")self.tableWidget1.show()self.noCourses = Falseself.ui.comboBox.clear()self.ui.comboBox.addItem('Modify Record ...')rows = conn.execute("SELECT * FROM course")tabLst2 = []for row in rows:self.cells = []for cell in row:self.cells.append(cell) # cellstabLst2.append(self.cells) # rowsfor item in tabLst2:self.ui.comboBox.addItem(str(item[0]) + ' - ' +str(item[9]))self.ui.comboBox.currentIndexChanged.connect(self.changeCourse)conn.close()513def showTableStudents(self):conn = sqlite3.connect('cdo.db')if int(self.getStrSQL(conn.execute("SELECT COUNT (*) FROMstudent"))) > 0:rows = conn.execute("SELECT * FROM student")tabLst = []for row in rows:self.cells = []for cell in row:self.cells.append(cell) # cellstabLst.append(self.cells) # rows#print(tabLst)self.tableWidget2 = QTableWidget()r = len(tabLst)c = len(self.cells)self.tableWidget2.setRowCount(r)self.tableWidget2.setColumnCount(c)count = 0headerLst = conn.execute("PRAGMA table_info(student)")for header in headerLst:self.tableWidget2.setHorizontalHeaderItem(count,QTableWidgetItem(str(header[1])))count += 1for i in range(0, r):for ii in range(0, c):twi = QTableWidgetItem(str(tabLst[i][ii]))self.tableWidget2.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)self.ui.gridLayout_3.addWidget(self.tableWidget2)self.tableWidget2.setSortingEnabled(True)self.tableWidget2.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)self.tableWidget2.setSelectionBehavior(QtWidgets.QAbstractItemView.SelectRows)self.tableWidget2.setStyleSheet("QTableWidget {color: black;}")self.tableWidget2.show()self.noStudents = Falseself.ui.comboBox_2.clear()self.ui.comboBox_2.addItem('Modify Record ...')rows = conn.execute("SELECT * FROM student")tabLst2 = []for row in rows:self.cells = []for cell in row:self.cells.append(cell) # cellstabLst2.append(self.cells) # rowsfor item in tabLst2:self.ui.comboBox_2.addItem(str(item[0]) + ' - ' +str(item[1]) + ' ' + str(item[2] + ' ' + str(item[3])))514self.ui.comboBox_2.currentIndexChanged.connect(self.changeStudent)conn.close()def showTableTutors(self):conn = sqlite3.connect('cdo.db')if int(self.getStrSQL(conn.execute("SELECT COUNT (*) FROM tutor")))> 0:rows = conn.execute("SELECT * FROM tutor")tabLst = []for row in rows:self.cells = []for cell in row:self.cells.append(cell) # cellstabLst.append(self.cells) # rowsself.tableWidget3 = QTableWidget()rows = conn.execute("SELECT * FROM course")allcourses = []for one in rows:allcoursesID = []allcoursesID.append(one[0])allcoursesID.append(one[9])allcourses.append(allcoursesID)r = len(tabLst)c = len(self.cells) + len(allcourses)self.tableWidget3.setRowCount(r)self.tableWidget3.setColumnCount(c)count = 0headerLst = conn.execute("PRAGMA table_info(tutor)")for header in headerLst:self.tableWidget3.setHorizontalHeaderItem(count,QTableWidgetItem(str(header[1])))count += 1for i in range(0, r):for ii in range(0, c-len(allcourses)):twi = QTableWidgetItem(str(tabLst[i][ii]))self.tableWidget3.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)count = 1for onecourse in allcourses:checker = Falserows = conn.execute("SELECT limitdate FROM authorizedWHERE tutor=%d AND course=%d" % (tabLst[i][0], onecourse[0]))for row in rows:checker = Truetwi = QTableWidgetItem(onecourse[1] + " - " +str(self.dateExtr(row[0])))twi.setToolTip(row[0])self.tableWidget3.setItem(i,ii+count, twi)#twi.setFlags(self.flagsEdit)if self.dateExtr(row[0]) < 90:twi.setBackground(QtGui.QColor(230,99,99))if not checker:twi = QTableWidgetItem("-")515self.tableWidget3.setItem(i,ii+count, twi)#twi.setFlags(self.flagsEdit)count += 1self.ui.gridLayout_4.addWidget(self.tableWidget3)self.tableWidget3.setSortingEnabled(True)self.tableWidget3.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)self.tableWidget3.setSelectionBehavior(QtWidgets.QAbstractItemView.SelectRows)self.tableWidget3.setStyleSheet("QTableWidget {color: black;}")self.tableWidget3.show()self.noTutors = Falseself.ui.comboBox_3.clear()self.ui.comboBox_3.addItem('Modify Record ...')for item in tabLst:self.ui.comboBox_3.addItem(str(item[0]) + ' - ' +str(item[1:4]))self.ui.comboBox_3.currentIndexChanged.connect(self.changeTutor)conn.close()def showTableAuthorized(self):conn = sqlite3.connect('cdo.db')if int(self.getStrSQL(conn.execute("SELECT COUNT (*) FROMauthorized"))) > 0:rows = conn.execute("SELECT * FROM authorized")tabLst = []for row in rows:self.cells = []for cell in row:self.cells.append(cell) # cellstabLst.append(self.cells) # rowsself.tableWidget4 = QTableWidget()r = len(tabLst)c = len(self.cells)self.tableWidget4.setRowCount(r)self.tableWidget4.setColumnCount(c)count = 0headerLst = conn.execute("PRAGMA table_info(authorized)")for header in headerLst:self.tableWidget4.setHorizontalHeaderItem(count,QTableWidgetItem(str(header[1])))count += 1for i in range(0, r):for ii in range(0, c):if ii == 2:value = conn.execute("SELECT courseshort FROM courseWHERE id=%d" % int(tabLst[i][ii]))for v in value:valueToInsert = v[0]twi = QTableWidgetItem(valueToInsert)self.tableWidget4.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)elif ii == 1:516value = conn.execute("SELECT famname, name, fathnameFROM tutor WHERE id=%d" % int(tabLst[i][ii]))for v in value:valueToInsert = v[0] + ' ' + v[1] + ' ' + v[2]twi = QTableWidgetItem(valueToInsert)self.tableWidget4.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)elif ii == 3:twi = QTableWidgetItem(str(tabLst[i][ii]))if self.dateExtr(str(tabLst[i][ii])) < 1:twi.setBackground(QtGui.QColor(230,99,99))self.tableWidget4.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)else:twi = QTableWidgetItem(str(tabLst[i][ii]))self.tableWidget4.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)self.ui.gridLayout_12.addWidget(self.tableWidget4)self.tableWidget4.setSortingEnabled(True)self.tableWidget4.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)self.tableWidget4.setSelectionBehavior(QtWidgets.QAbstractItemView.SelectRows)self.tableWidget4.setStyleSheet("QTableWidget {color: black;}")self.tableWidget4.show()self.noAuth = Falseconn.close()def showTableCourseForTutors(self):conn = sqlite3.connect('cdo.db')if int(self.getStrSQL(conn.execute("SELECT COUNT (*) FROMcoursesfortutor"))) > 0:rows = conn.execute("SELECT * FROM coursesfortutor ORDER BYactive DESC")self.tabLst = []for row in rows:self.cells = []for cell in row:self.cells.append(cell) # cellsself.tabLst.append(self.cells) # rowsself.tableWidget = QTableWidget()r = len(self.tabLst)c = len(self.cells)self.tableWidget.setRowCount(r)self.tableWidget.setColumnCount(c)count = 0headerLst = conn.execute("PRAGMA table_info(coursesfortutor)")for header in headerLst:self.tableWidget.setHorizontalHeaderItem(count,QTableWidgetItem(str(header[1])))if str(header[1]) == 'duration':durCell = countif str(header[1]) == 'startdate':517startCell = countcount += 1for i in range(0, r):for ii in range(0, c):if ii == 2:value = conn.execute("SELECT courseshort FROM courseWHERE id=%d" % self.tabLst[i][ii])for v in value:valueToInsert = v[0]twi = QTableWidgetItem(valueToInsert)self.tableWidget.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)elif ii == 1:#chkBoxItem = QTableWidgetItem()#chkBoxItem.setFlags(QtCore.Qt.ItemIsUserCheckable |QtCore.Qt.ItemIsEnabled)#if self.tabLst[i][ii] == 'TRUE':#chkBoxItem.setCheckState(QtCore.Qt.Checked)#else:#chkBoxItem.setCheckState(QtCore.Qt.Unchecked)twi = QTableWidgetItem(str(self.tabLst[i][ii]))self.tableWidget.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)elif ii == 3:value = conn.execute("SELECT famname, name, fathnameFROM tutor WHERE id=%d" % self.tabLst[i][ii])for v in value:valueToInsert = v[0] + ' ' + v[1] + ' ' + v[2]twi = QTableWidgetItem(valueToInsert)self.tableWidget.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)elif ii == 4:value = conn.execute("SELECT famname, name, fathnameFROM student WHERE id=%d" % self.tabLst[i][ii])for v in value:valueToInsert = v[0] + ' ' + v[1] + ' ' + v[2]twi = QTableWidgetItem(valueToInsert)self.tableWidget.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)elif ii == 5:value = conn.execute("SELECT famname, name, fathnameFROM tutor WHERE id=%d" % self.tabLst[i][ii])for v in value:valueToInsert = v[0] + ' ' + v[1] + ' ' + v[2]twi = QTableWidgetItem(valueToInsert)self.tableWidget.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)elif ii == 11:twi = QTableWidgetItem(self.tabLst[i][ii])self.tableWidget.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)if self.tabLst[i][ii-1] and not self.tabLst[i][ii]:twi.setBackground(QtGui.QColor(230,99,99))else:518twi = QTableWidgetItem(str(self.tabLst[i][ii]))self.tableWidget.setItem(i,ii, twi)#twi.setFlags(self.flagsEdit)if ii == durCell:twi.setToolTip(str(self.dateExtr(self.tabLst[i][startCell])*-1))if self.dateExtr(self.tabLst[i][startCell])*-1 >int(twi.text()):twi.setBackground(QtGui.QColor(230,99,99))#twi.setFlags(self.flagsEdit)self.ui.gridLayout_5.addWidget(self.tableWidget)self.tableWidget.setSortingEnabled(True)self.tableWidget.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)self.tableWidget.setSelectionBehavior(QtWidgets.QAbstractItemView.SelectRows)#self.tableWidget.doubleClicked.connect(self.getInformation)self.tableWidget.setStyleSheet("QTableWidget {color: black;}")self.tableWidget.show()self.noCST = Falseself.ui.comboBox_4.clear()self.ui.comboBox_4.addItem('Modify Record ...')for item in sorted(self.tabLst):self.ui.comboBox_4.addItem(str(item[0]) + ' - ' +str(item[6])) # + ' ' + str(item[2]) + ' ' + str(item[3]))self.ui.comboBox_4.currentIndexChanged.connect(self.changeCST)conn.close()def showTableCSTTables(self):if self.activeInCST == False:self.tab.deleteLater()self.ui.tabWidget.setCurrentIndex(5)conn = sqlite3.connect('cdo.db')rows = conn.execute("SELECT * FROM coursesfortutor WHERE active=1")# here is all table coursesfortutor as a listself.lst = self.getLstSQL(rows)quant = conn.execute("SELECT COUNT (*) FROM coursesfortutor WHEREactive=1")self.tab = QTabWidget() # all tabsself.tabItems = [] # tabs one by oneself.gridItems = [] # tables in each tabself.tables = []self.buttonsSave = []# adding tabs with info one by onerangeFor = self.getStrSQL(quant)conn.close()# new tables namesself.newTables = []for i in range(rangeFor):self.tabItems.append(QTabBar())self.tab.addTab(self.tabItems[i], self.lst[i][6])519self.gridItems.append(QtWidgets.QGridLayout(self.tabItems[i]))scroll = QScrollArea(self.tabItems[i])scroll.setHorizontalScrollBarPolicy(QtCore.Qt.ScrollBarAlwaysOn)scroll.setVerticalScrollBarPolicy(QtCore.Qt.ScrollBarAlwaysOn)viewport = QWidget(self.tabItems[i])scroll.setWidget(viewport)scroll.setWidgetResizable(True)l = QGridLayout(viewport)viewport.setLayout(l)# adding info before the table# student nameconn = sqlite3.connect('cdo.db')rows = conn.execute("SELECT * FROM student WHERE id=%d" %self.lst[i][4])student = self.getLstSQL(rows)l.addWidget(QLabel("Student: %s %s %s" % (student[0][1],student[0][2], student[0][3])))# courserows = conn.execute("SELECT * FROM course WHERE id=%d" %self.lst[i][2])course = self.getLstSQL(rows)l.addWidget(QLabel("Course: %s" % (course[0][1])))discount = self.lst[i][14]price = self.lst[i][15]l.addWidget(QLabel("Price: %s (%d %% discount)" % (price,discount)))vconf = course[0][3]vconfPrice = course[0][6]opentask = course[0][4]opentaskPrice = course[0][7]managing = course[0][8]# startdate and durationstartdate = self.lst[i][8]duration = self.lst[i][9]l.addWidget(QLabel("Start: %s" % (startdate)))l.addWidget(QLabel("Duration: %s" % (duration)))# tutorrows = conn.execute("SELECT * FROM tutor WHERE id=%d" %self.lst[i][3])tutor = self.getLstSQL(rows)l.addWidget(QLabel("Tutor: %s %s %s" % (tutor[0][1],tutor[0][2], tutor[0][3])))# examinerrows = conn.execute("SELECT * FROM tutor WHERE id=%d" %self.lst[i][5])examiner = self.getLstSQL(rows)l.addWidget(QLabel("Examiner: %s %s %s" % (examiner[0][1],examiner[0][2], examiner[0][3])))# moneyl.addWidget(QLabel("Video Conferences: %s x %s = %s" %(str(vconf), str(vconfPrice), str(vconf*vconfPrice))))520l.addWidget(QLabel("Open Task Ex.: %s x %s = %s" %(str(opentask), str(opentaskPrice), str(opentask*opentaskPrice))))l.addWidget(QLabel("Managing: %s" % (str(managing))))# additional consultationsaddConsPrice = self.lst[i][16]addConsQuant = self.lst[i][17]l.addWidget(QLabel("Add.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
