from sqlobject import (SQLObject, UnicodeCol, StringCol, ForeignKey, MultipleJoin, IntCol, DateTimeCol, BoolCol, RelatedJoin, DatabaseIndex) from turbogears.database import PackageHub from datetime import datetime, timedelta # # the TurboGears "package" hub to manage connections / transactions # hub = PackageHub("whatwhat") __connection__ = hub soClasses = ('Person', 'Groups', 'Permission', 'Project', 'Issue', 'Risk', 'Note', 'Question', 'Answer', 'ProjectFile') # # some constants and mappings to descriptive names # CHANCE_LOW = 1 CHANCE_MEDIUM = 2 CHANCE_HIGH = 3 chance_codes = { CHANCE_LOW: 'Low', CHANCE_MEDIUM: 'Medium', CHANCE_HIGH: 'High' } IMPACT_LOW = 1 IMPACT_MEDIUM = 2 IMPACT_HIGH = 3 impact_codes = { IMPACT_LOW: 'Low', IMPACT_MEDIUM: 'Medium', IMPACT_HIGH: 'High' } STATUS_NOT_STARTED = 1 STATUS_ON_SCHEDULE = 2 STATUS_BEHIND_SCHEDULE = 3 STATUS_COMPLETE = 4 status_codes = { STATUS_NOT_STARTED: 'Not Started', STATUS_ON_SCHEDULE: 'On Schedule', STATUS_BEHIND_SCHEDULE: 'Behind Schedule', STATUS_COMPLETE: 'Complete' } # # the actual model objects for WhatWhat Status # class Person(SQLObject): displayName = StringCol(length=255, alternateID=True) userId = StringCol(length=32, alternateID=True, alternateMethodName="by_user_name") password = StringCol() emailAddress = StringCol() groups = RelatedJoin("Groups", intermediateTable="tg_user_group", joinColumn="user_name", otherColumn="group_name") def _get_permissions(self): perms = set() for g in self.groups: perms = perms | set(g.permissions) return perms class Groups(SQLObject): group_name = StringCol(length=16, alternateID=True, alternateMethodName="by_group_name") displayName = StringCol(length=255) created = DateTimeCol(default=datetime.now) # collection of all users belonging to this group users = RelatedJoin("Person", intermediateTable="tg_user_group", joinColumn="group_name", otherColumn="user_name") # collection of all permissions for this group permissions = RelatedJoin("Permission", joinColumn="group_name", intermediateTable="tg_group_permission", otherColumn="permission_name") class Permission(SQLObject): permissionId = StringCol(length=16, alternateID=True) description = StringCol(length=255) groups = RelatedJoin("Groups", intermediateTable="tg_group_permission", joinColumn="permission_name", otherColumn="group_name") class Project(SQLObject): name = UnicodeCol(length=255, alternateID=True) description = UnicodeCol() archived = BoolCol(default=False) status = IntCol(default=STATUS_NOT_STARTED) parent_project = ForeignKey('Project') contact = ForeignKey('Person') issues = MultipleJoin('Issue', orderBy=['-impact', '-creation_date']) risks = MultipleJoin('Risk', orderBy=['-impact', '-creation_date']) questions = MultipleJoin('Question') notes = MultipleJoin('Note', orderBy='-creation_date') sub_projects = MultipleJoin('Project', joinColumn='parent_project_id') files = MultipleJoin('ProjectFile', orderBy='creation_date') idx_project = DatabaseIndex('parent_project') idx_contact = DatabaseIndex('contact') def _get_recent_notes(self): now = datetime.now() delta = timedelta(15) return [note for note in self.notes if (now - note.creation_date <= delta) or (note.last_edit_date is not None and now - note.last_edit_date <= delta)] def _get_open_risks(self): return [risk for risk in self.risks if not risk.closed] def _get_closed_risks(self): return [risk for risk in self.risks if risk.closed] def _get_open_issues(self): return [issue for issue in self.issues if not issue.closed] def _get_closed_issues(self): return [issue for issue in self.issues if issue.closed] def _get_delayed_subprojects(self): return [sub_project for sub_project in self.sub_projects if sub_project.status == STATUS_BEHIND_SCHEDULE] def _total_questions(self): list1 = [question for question in self.questions] list2 = [question for sub_project in self.sub_projects for question in sub_project.questions] return list1 + list2 def _total_answers(self): list1 = [answer for question in self.questions for answer in question.answers] list2 = [answer for sub_project in self.sub_projects for question in sub_project.questions for answer in question.answers] return list1 + list2 def _total_files(self): list1 = [file for file in self.files] list2 = [file for sub_project in self.sub_projects for file in sub_project.files] return list1 + list2 def _total_risks(self): list1 = [risk for risk in self.risks if not risk.closed] list2 = [risk for sub_project in self.sub_projects for risk in sub_project.risks if not risk.closed] return list1 + list2 def _total_issues(self): list1 = [issue for issue in self.issues if not issue.closed] list2 = [issue for sub_project in self.sub_projects for issue in sub_project.issues if not issue.closed] return list1 + list2 def _total_notes(self): list1 = [note for note in self.notes] list2 = [note for sub_project in self.sub_projects for note in sub_project.notes] return list1 + list2 def _new_notes(self, hours=24): now = datetime.now() delta = timedelta(hours=hours) list1 = [note for note in self.notes if (now - note.creation_date <= delta) or (note.last_edit_date is not None and now - note.last_edit_date <= delta)] list2 = [note for sub_project in self.sub_projects for note in sub_project.notes if (now - note.creation_date <= delta) or (note.last_edit_date is not None and now - note.last_edit_date <= delta)] return list1 + list2 def _new_risks(self, hours=24): now = datetime.now() delta = timedelta(hours=hours) list1 = [risk for risk in self.risks if (now - risk.creation_date <= delta and not risk.closed)] list2 = [risk for sub_project in self.sub_projects for risk in sub_project.risks if (now - risk.creation_date <= delta and not risk.closed)] return list1 + list2 def _new_issues(self, hours=24): now = datetime.now() delta = timedelta(hours=hours) list1 = [issue for issue in self.issues if (now - issue.creation_date <= delta and not issue.closed)] list2 = [issue for sub_project in self.sub_projects for issue in sub_project.issues if (now - issue.creation_date <= delta and not issue.closed)] return list1 + list2 def _new_files(self, hours=24): now = datetime.now() delta = timedelta(hours=hours) list1 = [file for file in self.files if (now - file.creation_date <= delta)] list2 = [file for sub_project in self.sub_projects for file in sub_project.files if (now - file.creation_date <= delta)] return list1 + list2 def _new_questions(self, hours=24): now = datetime.now() delta = timedelta(hours=hours) questionList = [] answerList = [] list1 = [] for question in self.questions: if (now - question.creation_date <= delta): questionList.append(question) for answer in question.answers: if (now - answer.creation_date <= delta): answerList.append(answer) if len(answerList) > 0 or len(questionList) > 0: list1.append(question) answerList = [] questionList = [] answerList = [] questionList = [] for sub_project in self.sub_projects: for question in sub_project.questions: if (now - question.creation_date <= delta): questionList.append(question) for answer in question.answers: if (now - answer.creation_date <= delta): answerList.append(answer) if len(answerList) > 0 or len(questionList) > 0: list1.append(question) answerList = [] questionList = [] return list1 class Issue(SQLObject): issue = UnicodeCol() impact = IntCol() closed = BoolCol(default=False) project = ForeignKey('Project') creator = ForeignKey('Person') creation_date = DateTimeCol(default=datetime.now) idx_project = DatabaseIndex('project') idx_creator = DatabaseIndex('creator') class Risk(SQLObject): chance = IntCol() impact = IntCol() description = UnicodeCol() closed = BoolCol(default=False) creation_date = DateTimeCol(default=datetime.now) project = ForeignKey('Project') creator = ForeignKey('Person') idx_project = DatabaseIndex('project') idx_creator = DatabaseIndex('creator') class Note(SQLObject): note = UnicodeCol() creation_date = DateTimeCol(default=datetime.now) last_edit_date = DateTimeCol(default=None) project = ForeignKey('Project') creator = ForeignKey('Person') idx_project = DatabaseIndex('project') idx_creator = DatabaseIndex('creator') class Question(SQLObject): question = UnicodeCol() creation_date = DateTimeCol(default=datetime.now) project = ForeignKey('Project') creator = ForeignKey('Person') answers = MultipleJoin('Answer') idx_project = DatabaseIndex('project') idx_creator = DatabaseIndex('creator') class Answer(SQLObject): answer = UnicodeCol() creation_date = DateTimeCol(default=datetime.now) question = ForeignKey('Question') creator = ForeignKey('Person') idx_question = DatabaseIndex('question') idx_creator = DatabaseIndex('creator') class ProjectFile(SQLObject): filename = UnicodeCol() systemfilename = UnicodeCol() creation_date = DateTimeCol(default=datetime.now) project = ForeignKey('Project') creator = ForeignKey('Person') idx_project = DatabaseIndex('project') idx_creator = DatabaseIndex('creator')