def get_in_interval(first_month, last_month): sql = ("SELECT * FROM assignments " "WHERE first_month>='%s' " "AND last_month<='%s'") % (first_month, last_month) qry = QtSql.QSqlQuery() if not qry.exec_(sql): raise AllocatException(qry.lastError().text()) return Recordset.to_list(qry, Assignment)
def get_all(): sql = ("SELECT asn.*, prj.nickname as project_name, emp.name as employee_name " "FROM assignments asn " "JOIN projects prj on (asn.project_id=prj.id) " "JOIN employees emp on (asn.employee_id=emp.id)") qry = QtSql.QSqlQuery() if not qry.exec_(sql): raise AllocatException(qry.lastError().text()) return Recordset.to_list(qry, Assignment)
def get_projects(self): from models import Project self.set_assignments() if not self.assignments: return None prj_ids = set([asn.project_id for asn in self.assignments]) sql = "SELECT nickname FROM projects WHERE id IN (" +\ ','.join(str(prj_id) for prj_id in prj_ids) + ")" if not self.query.exec_(sql): raise AllocatException(self.query.lastError().text()) return Recordset.to_list(self.query, Project)
def set_assignments(self): if self.assignments: return from models import Assignment sql = ("SELECT asn.*, prj.nickname as project_name, emp.name as employee_name " "FROM assignments asn " "JOIN projects prj on (asn.project_id=prj.id) " "JOIN employees emp on (asn.employee_id=emp.id) " "WHERE asn.employee_id=" + str(self.id)) if not self.query.exec_(sql): raise AllocatException(self.query.lastError().text()) self.assignments = Recordset.to_list(self.query, Assignment)