From 90c3c60a7ea9485a2460b551c5cf31bf6e5862a5 Mon Sep 17 00:00:00 2001 From: Drew Fisher Date: Wed, 9 Sep 2009 00:52:01 -0500 Subject: [PATCH] Analytics, in the form of a text report. This adds support for basic analytics functionality to expedite figuring out which rooms we still need data for. This should make it much easier to determine which rooms need to be doorknocked to make the directory more complete. --- analytics.py | 127 ++++++++++++++++++++++++++++++++++++++++++++++++ chooseaction.py | 6 +++ mainapp.py | 15 +++++- 3 files changed, 146 insertions(+), 2 deletions(-) create mode 100644 analytics.py diff --git a/analytics.py b/analytics.py new file mode 100644 index 0000000..7bde135 --- /dev/null +++ b/analytics.py @@ -0,0 +1,127 @@ +from PyQt4.QtCore import * +from PyQt4.QtGui import * +from PyQt4.QtSql import * + + +class Analytics(QWidget): + def __init__(self,parent=None, database=None): + QWidget.__init__(self, parent) + self.roomlists = { "Lechner" : [111, 112, 113, 114, 115, 116, 117, 121, 122, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 205, 206, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 221, 222, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 305, 306, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 321, 322, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 338, 339, 340, 405, 406, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 421, 422, 427, 428, 429, 430, 431, 432, 433, 434, 435, 436, 437, 438, 439, 440] , + "McFadden" : [151, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 170, 171, 172, 173, 175, 176, 177, 178, 179, 182, 183, 184, 185, 186, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 270, 271, 272, 274, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 374, 442, 443, 444, 445, 446, 447, 448, 449, 450, 451, 452, 453, 455, 456, 457, 458, 459, 460, 461, 462, 463, 464, 465, 466, 467, 468, 469, 470, 471, 472, 474] , + "Clements" : [101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323, 324, 325, 326, 327, 328, 329, 330, 332, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 432] + } + self.db = database + self.reportbox = QTextEdit() + self.reportbox.setTextInteractionFlags( Qt.TextSelectableByMouse | Qt.TextSelectableByKeyboard ) + self.verbosereportbox = QTextEdit() + self.verbosereportbox.setTextInteractionFlags( Qt.TextSelectableByMouse | Qt.TextSelectableByKeyboard ) + self.goback = QPushButton("Return to Main Menu") + QObject.connect(self.goback, SIGNAL("clicked()"), self.finished) + self.layout = QGridLayout() + self.layout.addWidget(QLabel("

Summary:

"),0,0,1,1) + self.summary = QLabel() + self.layout.addWidget(self.summary,1,0,1,1) + self.layout.addWidget(QLabel("

Details:

"),2,0,1,1) + self.layout.addWidget(self.verbosereportbox,3,0,1,1) + self.layout.addWidget(self.goback,4,0,1,1) + self.setLayout(self.layout) + + def updateDB(self, newdbname): + self.db.close() + self.db.setDatabaseName(newdbname) + self.db.open() + + def vlog(self, line): # append to the verbose log only + self.verbosereport = self.verbosereport + line + "\n" + + def log(self, line): # append to the verbose and report logs + self.verbosereport = self.verbosereport + line + "\n" + self.report = self.report + line + "\n" + + def generateReport(self): + self.report = "" + self.verbosereport = "" + q = QSqlQuery(self.db) + dormsfound = [] + for d in self.roomlists.keys(): + q.prepare("SELECT id FROM people WHERE dorm=:dorm") + q.bindValue(":dorm",QVariant(QString(d))) + q.exec_() + found = False + while q.next(): + found = True + if found: + dormsfound.append(d) + if len(dormsfound) != 0: + self.log("Found data for people from " + ", ".join(dormsfound) + "\n") + else: + self.log("No records found") + for d in dormsfound: # Check each room in roomlists[d] for two residents + self.log(d + ":" ) + q.prepare("SELECT id FROM people WHERE dorm=:dorm") + q.bindValue(":dorm",QVariant(QString(d))) + q.exec_() + pop = 0 + while q.next(): + pop = pop + 1 + maxpop = len(self.roomlists[d]) * 2 + self.log("Registered Population: {0} of {1} ({2}%)".format(pop , maxpop, pop * 100 / maxpop ) ) + self.log("Breakdown by floor:") + # The following code assumes four floors and a numbering scheme where the + # hundreds digit of the room number is the floor. If this changes, this + # code will need to be rewritten. + floors = [ [], [], [], [] ] # Note: sorted from bottom floor to top + for r in self.roomlists[d]: + if r < 200: + floors[0].append(r) + if r >= 200 and r < 300: + floors[1].append(r) + if r >= 300 and r < 400: + floors[2].append(r) + if r >= 400 and r < 500: + floors[3].append(r) + floorpop = [] + for floor in floors: # calculate the registered population + thisfloorpop = 0 + for room in floor: + q.prepare("SELECT id FROM people WHERE dorm=:dorm AND room=:room") + q.bindValue(":dorm",QVariant(QString(d))) + q.bindValue(":room",QVariant(room)) + q.exec_() + while q.next(): + thisfloorpop = thisfloorpop + 1 + floorpop.append(thisfloorpop) + self.log("First floor: {0} of {1} registered ({2}%)".format( floorpop[0], len(floors[0]) * 2, floorpop[0] * 50 / len(floors[0]))) + self.log("Second floor: {0} of {1} registered ({2}%)".format( floorpop[1], len(floors[1]) * 2, floorpop[1] * 50 / len(floors[1]))) + self.log("Third floor: {0} of {1} registered ({2}%)".format( floorpop[2], len(floors[2]) * 2, floorpop[2] * 50 / len(floors[2]))) + self.log("Fourth floor: {0} of {1} registered ({2}%)".format( floorpop[3], len(floors[3]) * 2, floorpop[3] * 50 / len(floors[3]))) + self.vlog("Breakdown by room:") + for room in self.roomlists[d]: + q.prepare("SELECT id, surname, forename FROM people WHERE dorm=:dorm AND room=:room") + q.bindValue(":dorm",QVariant(QString(d))) + q.bindValue(":room",QVariant(room)) + q.exec_() + r = q.record() + col_id = r.indexOf("id") + col_surname = r.indexOf("surname") + col_forename = r.indexOf("forename") + records = [] + while q.next(): + records.append( (q.value(col_id).toString(), q.value(col_forename).toString(), q.value(col_surname).toString() ) ) + if len(records) == 0: + self.vlog("\tRoom " + str(room) + ": Need data for both residents") + if len(records) == 1: + self.vlog("\tRoom " + str(room) + ": Need data for resident NOT named " + str(records[0][1]) + " " + str(records[0][2]) ) + if len(records) == 2: + self.vlog("\tRoom " + str(room) + ": done") + if len(records) > 2: + self.vlog("\tRoom " + str(room) + ": There seem to be more than two people living here!") + self.vlog("\t\t" + ", ".join( [ str(d[1]) + " " + str(d[2]) for d in records] ) ) + + self.log("") # put a newline between each dorm + self.summary.setText(self.report) + self.verbosereportbox.setPlainText(self.verbosereport) + + def finished(self): + self.emit(SIGNAL("done()")) + diff --git a/chooseaction.py b/chooseaction.py index a944fa7..274f465 100644 --- a/chooseaction.py +++ b/chooseaction.py @@ -13,16 +13,19 @@ class ChooseAction (QWidget): self.pb2 = QCommandLinkButton("&Edit a directory record","If you've already added your data, but want to edit it, hit this button.") self.pb3 = QCommandLinkButton("&Import photos","To match picture from a camera to entered data, hit this button.") self.pb4 = QCommandLinkButton("E&xport as document","To export the directory as a document for printing, hit this button.") + self.pb5 = QCommandLinkButton("&Perform Analytics","To analyze the data you have (and are lacking), hit this button.") self.layout = QGridLayout() self.layout.addWidget(self.l1,0,0,1,3) self.layout.addWidget(self.pb1,1,1,1,1) self.layout.addWidget(self.pb2,2,1,1,1) self.layout.addWidget(self.pb3,3,1,1,1) self.layout.addWidget(self.pb4,4,1,1,1) + self.layout.addWidget(self.pb5,5,1,1,1) QtCore.QObject.connect(self.pb1, QtCore.SIGNAL("clicked()"), self.addPerson) QtCore.QObject.connect(self.pb2, QtCore.SIGNAL("clicked()"), self.editPerson) QtCore.QObject.connect(self.pb3, QtCore.SIGNAL("clicked()"), self.importPhotos) QtCore.QObject.connect(self.pb4, QtCore.SIGNAL("clicked()"), self.exportDocument) + QtCore.QObject.connect(self.pb5, QtCore.SIGNAL("clicked()"), self.runAnalytics) self.setLayout(self.layout) def addPerson(self): wiz = NewPersonWizard(self,self.db) @@ -38,3 +41,6 @@ class ChooseAction (QWidget): if not fileName.endsWith(".odt"): # if they leave off the extension, add it fileName = fileName.append(".odt") self.emit(QtCore.SIGNAL("exportDocument(QString)"),fileName) + def runAnalytics(self): + self.emit(QtCore.SIGNAL("runAnalytics()")) + diff --git a/mainapp.py b/mainapp.py index f48f2de..037c082 100644 --- a/mainapp.py +++ b/mainapp.py @@ -6,6 +6,7 @@ from chooseaction import ChooseAction from editperson import EditPerson from mergephotos import MergePhotos from odtwriter import ODTWriter +from analytics import Analytics class MainApp (QMainWindow): schema = "CREATE TABLE people ( id INTEGER PRIMARY KEY AUTOINCREMENT, forename TEXT NOT NULL, surname TEXT NOT NULL, netid TEXT NOT NULL, email TEXT, birthday TEXT, phone TEXT, major TEXT, dorm TEXT, room INTEGER, photo BLOB, createtime TEXT NOT NULL, mtime TEXT NOT NULL);" @@ -23,9 +24,11 @@ class MainApp (QMainWindow): self.chooseaction = ChooseAction(self,self.db) self.editperson = EditPerson(self,self.db) self.mergephotos = MergePhotos(self,self.db) + self.analytics = Analytics(self,self.db) self.center.addWidget(self.chooseaction) self.center.addWidget(self.editperson) self.center.addWidget(self.mergephotos) + self.center.addWidget(self.analytics) self.setCentralWidget(self.center) self.createActions() @@ -46,11 +49,14 @@ class MainApp (QMainWindow): self.fileQuitAction = QAction("&Quit",self) QObject.connect( self.fileQuitAction, SIGNAL("triggered()"), self.quit) QObject.connect( self.chooseaction, SIGNAL("editPerson()"), self.editPersonSlot) - QObject.connect( self.chooseaction, SIGNAL("mergePhotos()"), self.mergePhotos) + QObject.connect( self.chooseaction, SIGNAL("mergePhotos()"), self.mergePhotosSlot) QObject.connect( self.chooseaction, SIGNAL("exportDocument(QString)"), self.exportDocumentSlot) + QObject.connect( self.chooseaction, SIGNAL("runAnalytics()"), self.runAnalyticsSlot) QObject.connect( self.editperson, SIGNAL("done()"), self.returnToMainMenu) QObject.connect( self.mergephotos, SIGNAL("done()"), self.returnToMainMenu) + QObject.connect( self.analytics, SIGNAL("done()"), self.returnToMainMenu) QObject.connect( self, SIGNAL("databaseChanged(QString)"), self.editperson.updateDB) + QObject.connect( self, SIGNAL("databaseChanged(QString)"), self.analytics.updateDB) QObject.connect( self, SIGNAL("mergeDone()"), self.editperson.reselect ) @@ -84,6 +90,7 @@ class MainApp (QMainWindow): self.db.setDatabaseName(fileName) self.db.open() self.statusBar().showMessage("Successfully loaded database " + fileName) + self.returnToMainMenu() self.emit(SIGNAL("databaseChanged(QString)"),self.db.databaseName()) else: self.statusBar().showMessage("Canceled loading database") @@ -150,13 +157,17 @@ class MainApp (QMainWindow): def editPersonSlot(self): self.editperson.model.select() # update the table self.center.setCurrentWidget(self.editperson) - def mergePhotos(self): + def mergePhotosSlot(self): print "beginning photo merge" self.center.setCurrentWidget(self.mergephotos) #self.mergephotos.beginMerge() def exportDocumentSlot(self, filename): writer = ODTWriter(self.db) writer.write(filename) + def runAnalyticsSlot(self): + print "Running analytics" + self.analytics.generateReport() + self.center.setCurrentWidget(self.analytics) if __name__ == "__main__" : -- 2.39.5