def getCommentsList(self, project, category, ticketRelativeID): con = getConnection() checkProjectCategoryLogin(con, project, category) lang = projectLang(con, project) comments = loadComments(con, project, category, ticketRelativeID) commentsHTML = self.getCommentsHTML(project, lang, comments) return commentsHTML
def profile(self, project): con = getConnection() checkProjectLogin(con, project) lang = projectLang(con, project) cursor = con.cursor() query = """ SELECT name FROM Project WHERE id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), )) projectName = cursor.fetchone()[0] query=""" SELECT login, level, mail FROM Client WHERE id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_idUser'), )) row = cursor.fetchone() login = row[0] level = row[1] mail = row[2] mytemplate = Template(filename='templates/'+lang+'/profile.html', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') page = mytemplate.render( projectWebName = project, projectName = projectName, userLevel = cherrypy.session.get(str(project)+'_loginLevel', 1), login = login, mail = mail ) return page
def saveTicket(self, project, category, status, severity, name, description, files=[], **kwargs): """Save newly created ticket into the database""" con = getConnection() checkProjectCategoryLogin(con, project, category) cursor = con.cursor() categoryCode = {'bug' : 1, 'feature' : 2} # if ticket is created with status "being taken care of" or "solved", then the user is an admin # he is considered as the one who solved or is taking care of the issue. idMaintainer = 0 if int(status) == 1 or int(status) == 2 : idMaintainer = cherrypy.session.get(str(project)+'_idUser') # saving ticket and getting the id of that ticket query = """ INSERT INTO Ticket(project, category, name, description, status, severity, creator, maintainer) VALUES (%s, %s, %s, %s, %s, %s, %s, %s); SELECT lastval(); """ cursor.execute(query, ( cherrypy.session.get(str(project)+'_projectCode'), categoryCode[category], name, formatText(description), status, severity, cherrypy.session.get(str(project)+'_idUser'), idMaintainer) ) # id of that last ticket created id = int(cursor.fetchone()[0]) # relativeID of that last ticket query = """ SELECT relativeID FROM Ticket WHERE id = %s""" cursor.execute(query, (id, )) relativeID = int(cursor.fetchone()[0]) # attaching files to the ticket query = """ UPDATE File SET ticket = %s WHERE id = %s""" args = [] if type(files) == type('dummy'): args.append((id, files)) elif type(files) == type(['dummy', 'dummy']): for fileID in files : args.append( (id, fileID) ) cursor.executemany(query, args) con.commit() con.close() data = {'error_code': 0, 'relativeID': relativeID}; return demjson.encode(data);
def deleteFile(self, project, id): """Delete specified file""" con = getConnection() checkProjectLogin(con, project) cursor = con.cursor() query=""" SELECT count(*) FROM File WHERE id = %s AND project = %s """ cursor.execute(query, (id, cherrypy.session.get(str(project)+'_projectCode'))) row = cursor.fetchone() nb_files = row[0] if nb_files == 0: con.close() return '1' # code for "no such file available" elif nb_files > 1: con.close() return '2' # this should never ever happen, but just in case: "more than one file was found" else: # delete file from database query = """ DELETE FROM File WHERE id = %s AND project = %s """ cursor.execute(query, (id, cherrypy.session.get(str(project)+'_projectCode'))) con.commit() con.close() # delete file from HDD try : os.remove('files/'+str(id)) except : pass # all went well return '0'
def newProject(self, lang='en'): con = getConnection() con.close() langs = ['en', 'fr'] if lang not in langs: lang = 'en' mytemplate = Template(filename='templates/'+lang+'/new_project.html', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') page = mytemplate.render() return page
def updateEMail(self, project, mail): con = getConnection() checkProjectLogin(con, project) cursor = con.cursor() query = """UPDATE Client SET mail = %s WHERE id = %s""" cursor.execute(query, (mail, cherrypy.session.get(str(project)+'_idUser'))) con.commit() con.close() return 'ok'
def register(self, project): con = getConnection() checkProject(con, project) lang = projectLang(con, project) mytemplate = Template(filename='templates/'+lang+'/register.html', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') page = mytemplate.render( projectWebName = project, ) return page
def getTicketsList(self, project, category, solved, selectedTicketRelativeID=0): """Retrieve tickets and return html code for the tickets""" con = getConnection() checkProjectCategoryLogin(con, project, category) tickets = loadTickets(con, project, category, solved) lang = projectLang(con, project) unread = updatedTickets(con, project, category, selectedTicketRelativeID) con.close() ticketsList = self.getTicketsListHTML(project, category, solved, lang, tickets, selectedTicketRelativeID, unread) return ticketsList
def setSort(self, project, mode): con = getConnection() checkProjectLogin(con, project) con.close() # all possible modes modes = ['lastModDown', 'lastModUp', 'idDown', 'idUp', 'severityDown', 'severityUp', 'statusDown', 'statusUp' ] if mode not in modes : return 'error: non valid mode' # saving value cherrypy.session[str(project)+'_sortMode'] = mode return 'mode set'
def doLostPassword(self, projectWebName, login): con = getConnection() checkProject(con, projectWebName) lang = projectLang(con, projectWebName) cursor = con.cursor() login = login.strip() query = """ SELECT id, mail FROM Client WHERE project = %s AND login = %s AND isDeleted = False""" cursor.execute(query, (cherrypy.session.get(str(projectWebName)+'_projectCode'), login)) rows = cursor.fetchall() n = 0 for row in rows: n = n + 1 userID = int(row[0]) mail = row[1] if n == 0: return 'not found' else: # update the password newPassword = randomPwd() # encrypting the password m = md5.new() m.update(newPassword) m.update(projectWebName) m.update(login) query = """ UPDATE Client SET password = %s WHERE id = %s AND project = %s""" cursor.execute(query, (m.hexdigest(), userID, cherrypy.session.get(str(projectWebName)+'_projectCode')) ) con.commit() # sending email with new password query = """ SELECT name FROM Project WHERE id = %s""" cursor.execute(query, (cherrypy.session.get(str(projectWebName)+'_projectCode'), )) projectName = cursor.fetchone()[0] emailTemplate = Template(filename='templates/'+lang+'/mails/lost_password.mail', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') email = emailTemplate.render( to = mail, userName = login, projectName = projectName, projectWebName = projectWebName, newPwd = newPassword ) sendMail(mail, email) return 'ok'
def updateUserLevel(self, project, userID, level): con = getConnection() checkProjectLoginLevel(con, project) lang = projectLang(con, project) cursor = con.cursor() # check that level is a valid value if int(level) is not 1 and int(level) is not 2: con.close() return 'no' # check that user does exist query = """ SELECT count(*) FROM Client WHERE project = %s AND id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), userID)) n = int(cursor.fetchone()[0]) if n == 1: # update user level query = """ UPDATE Client SET level = %s WHERE project = %s AND id = %s""" cursor.execute(query, (level, cherrypy.session.get(str(project)+'_projectCode'), userID)) con.commit() # send him a mail to let him know his new status query = """ SELECT Client.mail, Client.login, Project.webname, Project.name FROM Client INNER JOIN Project ON Client.project = Project.id WHERE Client.project = %s AND Client.id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), userID)) row = cursor.fetchone() mail = row[0] userName = row[1] projectWebName = row[2] projectName = row[3] emailTemplate = Template(filename='templates/'+lang+'/mails/modification.mail', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') email = emailTemplate.render( to = mail, userName = userName, projectName = projectName, projectWebName = projectWebName, level = level ) sendMail(mail, email) con.close() return 'ok' else: con.close() return 'no'
def users(self, project): con = getConnection() checkProjectLoginLevel(con, project) lang = projectLang(con, project) cursor = con.cursor() query = """ SELECT name FROM Project WHERE id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), )) projectName = cursor.fetchone()[0] # get users query = """ SELECT CLient.id, Client.login, Client.mail, Client.level, Client.allowed, Client.isDeleted FROM Client WHERE project = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), )) rows = cursor.fetchall() users = [] requests = [] deleted = [] for row in rows: isDeleted = row[5] isAllowed = row[4] if isDeleted: deleted.append({'id': row[0], 'login': row[1], 'mail': row[2], 'level': row[3]}) elif isAllowed: users.append({'id': row[0], 'login': row[1], 'mail': row[2], 'level': row[3]}) else: requests.append({'id': row[0], 'login': row[1], 'mail': row[2], 'level': row[3]}) con.close() mytemplate = Template(filename='templates/'+lang+'/users.html', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') page = mytemplate.render( projectWebName = project, projectName = projectName, users = users, requests = requests, deletedUsers = deleted ) return page
def ticketForm(self, project, category, status=0, severity=0, name='', description='', files=[], mode='new'): """Return formular used for creating and editing tickets""" con = getConnection() checkProjectCategoryLogin(con, project, category) lang = projectLang(con, project) con.close() mytemplate = Template(filename='templates/'+lang+'/ticket_form.html', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') form = mytemplate.render( projectWebName = project, category = category, status = status, severity = severity, name = name, description = unFormatText(description), files = files, userLevel = cherrypy.session.get(str(project)+'_loginLevel', 1), mode = mode ) return form
def saveComment(self, project, category, relativeID, content, files=[]): """ """ con = getConnection() checkProjectCategoryLogin(con, project, category) cursor = con.cursor() # get ticket ID query = """ SELECT id FROM Ticket WHERE relativeID = %s AND project = %s AND category = %s""" categoryCode = { 'bug' : 1, 'feature' : 2 } cursor.execute(query, (relativeID, cherrypy.session.get(str(project)+'_projectCode'), categoryCode[category]) ) row = cursor.fetchone() ticketID = row[0] # save comment query = """ INSERT INTO Comment(author, ticket, content) VALUES (%s,%s,%s); SELECT lastval();""" cursor.execute(query, (cherrypy.session.get(str(project)+'_idUser'), ticketID, formatText(content)) ) commentID = int(cursor.fetchone()[0]) # attaching files to the comment. query = """ UPDATE File SET ticket = %s WHERE id = %s""" args = [] if type(files) == type('dummy'): args.append((commentID, files)) elif type(files) == type(['dummy', 'dummy']): for fileID in files : args.append( (commentID, fileID) ) cursor.executemany(query, args) con.commit() con.close() return 'ok'
def sendInvitations(self, project, to, msg) : """ sendInvitations page This is not a real page. It sends all invitations sent using the form found on the manageUsers page but has not a page on its own. The page displayed at the end is the manageUsers one, with a message to confirm that mails were sent. """ con = getConnection() checkProjectLoginLevel(con, project) cursor = con.cursor() lang = projectLang(con, project) # getting author name query = """ SELECT login FROM Client WHERE id = %s AND project = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_idUser'), cherrypy.session.get(str(project)+'_projectCode') ) ) rows = cursor.fetchall() author = 'unknown user' for row in rows : author = row[0] # processing "to" field (ie mail addresses) mails = to.split(';') for mail in mails : mail = mail.strip() emailTemplate = Template(filename='templates/'+lang+'/mails/invitation.mail', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') email = emailTemplate.render( to = mail, msg = msg, author = author, projectName = cherrypy.session.get(str(project)+'_projectName'), projectWebName = project ) sendMail(mail, email) con.close() return 'ok'
def newTicket(self, project, category): """ newTicket page This method only returns the new page """ con = getConnection() checkProjectCategoryLogin(con, project, category) cursor = con.cursor() #unread = updatedTickets(con, project, category) lang = projectLang(con, project) con.close() mytemplate = Template(filename='templates/'+lang+'/create.html', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') page = mytemplate.render( projectWebName = project, projectName = cherrypy.session.get(str(project)+'_projectName'), userLevel = cherrypy.session.get(str(project)+'_loginLevel', 1), category = category, ticketForm = self.ticketForm(project, category), ticketsList = self.getTicketsList(project, category, False) ) return page
def doLogin(self, project, login, password): con = getConnection() checkProject(con, project) cursor = con.cursor() query = """ SELECT Client.id, Client.level FROM Client INNER JOIN Project ON Client.project = Project.id WHERE Client.login = %s AND Client.password = %s AND Project.webname = %s AND Client.allowed = True AND Client.isDeleted = False""" # encoding password m = md5.new() m.update(password) m.update(project) m.update(login) cursor.execute(query, (login, m.hexdigest(), project)) # fetching the results id = None level = None rows = cursor.fetchall() for row in rows : id = row[0] level = row[1] # is the login ok ? if id == None or level == None: con.close() return 'no' else: con.close() # saving to the session cherrypy.session[str(project)+'_idUser'] = id cherrypy.session[str(project)+'_loginLevel'] = level return 'ok'
def editTicketForm(self, project, category, ticketRelativeID): con = getConnection() checkProjectCategoryLogin(con, project, category) query = """ SELECT Ticket.id, Ticket.name, Ticket.status, Ticket.severity, Ticket.description FROM Ticket WHERE project = %s AND category = %s AND relativeID = %s""" cursor = con.cursor() categoryCode = {'bug' : 1, 'feature' : 2} cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), categoryCode[category], ticketRelativeID)) row = cursor.fetchone() ticketID = row[0] name = row[1] status = row[2] severity = row[3] description = row[4] files = [] query = """ SELECT File.id, File.name FROM File WHERE File.type = 'ticket' AND File.ticket = %s""" cursor.execute(query, ( ticketID, )) rows = cursor.fetchall() for row in rows: files.append({'id': row[0], 'name': row[1]}) page = self.ticketForm(project, category, status, severity, name, description, files, 'edit') return page
def addFile(self, project, file_type, file, **kwargs): """Save uploaded file on HDD and in the database""" con = getConnection() checkProjectLogin(con, project) cursor = con.cursor() # insert file into database query = """ INSERT INTO File (project, name, type) VALUES (%s, %s, %s); SELECT lastval(); """ cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), formatFileName(file.filename), file_type)) con.commit() id = int(cursor.fetchone()[0]) con.close() # saving the file on the disk f = open('files/'+str(id), 'w') f.write(file.value) f.close() # we cannot send back a JSON object for ajaxUpload only takes string as input # since data to send back is made only of the id, a simple string should be enough return 'ok:'+str(id)
def files(self, project, id): """ Serves the file defined by the id. """ con = getConnection() checkProjectLogin(con, project) cursor = con.cursor() # selecting file name name = None query = """ SELECT name from File WHERE project = %s AND id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), id)) try : name = cursor.fetchone()[0] except : pass con.close() # if the file was found in the database if(name != None) : # finding mimetype so that file can be served correcly ext = "" i = name.rfind('.') if i != -1: ext = name[i:].lower() mimetypes.init() mimetypes.types_map['.dwg']='image/x-dwg' mimetypes.types_map['.ico']='image/x-icon' content_type = mimetypes.types_map.get(ext, "text/plain") # serving file return serve_file(os.path.join(_curdir, './files/%s' % id), content_type = content_type, disposition="attachment", name=name) # the file was not found else : raise cherrypy.NotFound
def updatePwd(self, project, pwd1, pwd2): con = getConnection() checkProjectLogin(con, project) if pwd1 != pwd2: con.close() return 'no' cursor = con.cursor() query = """ SELECT login FROM Client WHERE id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_idUser'), )) login = cursor.fetchone()[0] m = md5.new() m.update(pwd1) m.update(project) m.update(login) query = """ UPDATE Client SET password = %s WHERE id = %s AND project = %s""" cursor.execute(query, (m.hexdigest(), cherrypy.session.get(str(project)+'_idUser'), cherrypy.session.get(str(project)+'_projectCode'))) con.commit() con.close() return 'ok'
def logMeIn(self, project=None, category='bug', idTicket=0, login='', password='') : """ logMeIn form. """ con = getConnection() checkProject(con, project) lang = projectLang(con, project) query = "SELECT name FROM project WHERE webname=%s" cursor = con.cursor() cursor.execute(query, (project, )) row = cursor.fetchone() projectName = row[0] con.close() mytemplate = Template(filename='templates/'+lang+'/login.html', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') page = mytemplate.render( projectWebName = project, projectName = projectName, login = login, category = category, password = password, idTicket = idTicket, error = '', comingFrom = cherrypy.session.get(str(project)+'_previousPage', url(project)) ) return page
def deleteUser(self, project, userID): con = getConnection() checkProjectLoginLevel(con, project) lang = projectLang(con, project) cursor = con.cursor() # check that user does exist query = """ SELECT count(*) FROM Client WHERE project = %s AND id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), userID)) n = int(cursor.fetchone()[0]) if n == 1: # check that user is not the project's owner query = """ SELECT owner FROM Project WHERE id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), )) row = cursor.fetchone() ownerID = row[0] if int(ownerID) != int(userID) : # send revocation or rejection email query = """ SELECT Client.mail, Client.login, Project.webname, Project.name, Client.allowed FROM Client INNER JOIN Project ON Client.project = Project.id WHERE Client.project = %s AND Client.id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), userID)) row = cursor.fetchone() mail = row[0] userName = row[1] projectWebName = row[2] projectName = row[3] isAllowed = row[4] if isAllowed: mailType = 'revocation' else: mailType = 'rejection' emailTemplate = Template(filename='templates/'+lang+'/mails/'+mailType+'.mail', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') email = emailTemplate.render( to = mail, userName = userName, projectName = projectName, projectWebName = projectWebName, ) sendMail(mail, email) # delete user # if the user was already in the project, we don't really delete it # we just set isDeleted to True so that he can't log in anymore if isAllowed: query = """ UPDATE Client SET isDeleted = True WHERE project = %s AND id = %s""" else: query = """ DELETE FROM Client WHERE project = %s AND id = %s""" cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), userID)) con.commit() con.close() return 'ok' else: con.close() return 'owner' else: con.close() return 'no'
def search(self, project, search=''): con = getConnection() checkProjectLogin(con, project) lang = projectLang(con, project) cursor = con.cursor() query = """ SELECT name FROM Project WHERE webname = %s""" cursor.execute(query, (project, )) projectName = cursor.fetchone()[0] mytemplate = Template(filename='templates/'+lang+'/search.html', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') tickets = [] if search is not '': query = """ SELECT Ticket.id, Ticket.name, Ticket.severity, Ticket.status, Ticket.category, Ticket.relativeID, ts_rank_cd( setweight(to_tsvector(%(lang)s, no_accents(coalesce(Ticket.name, ''))), 'A') || setweight(to_tsvector(%(lang)s, no_accents(coalesce(Ticket.description, ''))), 'C') || setweight(to_tsvector(%(lang)s, no_accents(coalesce(concat_comments(Ticket.id), ''))), 'D'), plainto_tsquery(%(lang)s,no_accents(%(search)s))) AS rank, ts_headline(%(lang)s, no_accents(Ticket.name) || ' ' || no_accents(Ticket.description) || ' ' || no_accents(concat_comments(Ticket.id)), plainto_tsquery(%(lang)s, no_accents(%(search)s))) FROM Ticket WHERE project = %(project)s AND to_tsvector( %(lang)s, no_accents(coalesce(Ticket.name, '')) || no_accents(coalesce(Ticket.description, '')) || no_accents(coalesce(concat_comments(Ticket.id), '')) ) @@ plainto_tsquery(%(lang)s, no_accents(%(search)s)) ORDER BY rank DESC""" # ADD NEW LANGS HERE lang_names = {'en': 'english', 'fr': 'french'} cursor.execute(query, {'project': cherrypy.session.get(str(project)+'_projectCode'), 'search': search, 'lang': lang_names[lang]}) rows = cursor.fetchall() tickets = [] for row in rows: ticket = {'id': int(row[0]), 'name': row[1], 'severity': int(row[2]), 'status': int(row[3]), 'category': int(row[4]), 'relativeID': int(row[5]), 'headline': row[7] } tickets.append(ticket) page = mytemplate.render( projectWebName = project, projectName = projectName, userLevel = cherrypy.session.get(str(project)+'_loginLevel', 1), search = search, tickets = tickets ) return page
def doRegister(self, projectWebName, login, pwd1, pwd2, email): con = getConnection() checkProject(con, projectWebName) lang = projectLang(con, projectWebName) # clean data login = login.strip() pwd1 = pwd1.strip() pwd2 = pwd2.strip() email = email.strip() # check data errors = { 'equalPwd': True, 'emptyPwd': (pwd1 == '' or pwd2 == ''), 'emptyLogin': login=='', 'emptyEMail': email=='', 'loginTaken': False, 'ok': False} # pwd1 and pwd2 are equal if pwd1 != pwd2 : errors['equalPwd'] = False # login is already taken query = """ SELECT count(*) FROM Client WHERE project = %s AND login = %s""" cursor = con.cursor() cursor.execute(query, (cherrypy.session.get(str(projectWebName)+'_projectCode'), login)) if int(cursor.fetchone()[0]) > 0: errors['loginTaken'] = True # return errors if errors['emptyPwd'] or errors['emptyLogin'] or errors['emptyEMail'] or not errors['equalPwd'] or errors['loginTaken']: return demjson.encode(errors) # no error were found, just go on with the normal procedure query = """ INSERT INTO Client(login, password, project, level, allowed, mail) VALUES (%s, %s, %s, 1, False, %s)""" m = md5.new() m.update(pwd1) m.update(projectWebName) m.update(login) cursor.execute(query, (login, m.hexdigest(), cherrypy.session.get(str(projectWebName)+'_projectCode'), email)) con.commit() # send emails # to admins # get projectName query = """ SELECT name FROM Project WHERE Project.id = %s""" cursor.execute(query, (cherrypy.session.get(str(projectWebName)+'_projectCode'), )) projectName = cursor.fetchone()[0] # send mails to all admins query = """ SELECT login, mail FROM Client WHERE project = %s AND level = 2""" cursor.execute(query, (cherrypy.session.get(str(projectWebName)+'_projectCode'), )) rows = cursor.fetchall() for row in rows : emailTemplate = Template(filename='templates/'+lang+'/mails/new_user.mail', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') emailContent = emailTemplate.render( to = row[1], userName = row[0], projectName = projectName, projectWebName = projectWebName) sendMail(row[1], emailContent) # to new user emailTemplate = Template(filename='templates/'+lang+'/mails/registration.mail', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') emailContent = emailTemplate.render( to = email, userName = login, projectName = projectName, projectWebName = projectWebName, password = pwd1) sendMail(email, emailContent) errors['ok'] = True return demjson.encode(errors)
def deleteTicket(self, project, category, ticketID) : """ deleteTicket page Deletes the ticket, all files attached, all comments and all of comments' files. Then redirects to the home of the category. """ con = getConnection() checkProjectCategoryLogin(con, project, category) checkSuperUserOrCreator(con, project, category, ticketID) lang = projectLang(con, project) categoryCode = {'bug' : 1, 'feature' : 2} cursor = con.cursor() # selecting real id query = """ SELECT id FROM Ticket WHERE relativeID = %s AND project = %s AND category = %s""" categoryCode = { 'bug' : 1, 'feature' : 2 } cursor.execute(query, (ticketID, cherrypy.session.get(str(project)+'_projectCode'), categoryCode[category]) ) row = cursor.fetchone() id = row[0] # delete file # on the disk query = """ SELECT id FROM File WHERE ( type = 'ticket' AND ticket = %s ) OR ( type='comment' AND ticket in (SELECT id FROM Comment WHERE ticket = %s) ) """ cursor.execute(query, (id, id ) ) rows = cursor.fetchall() for row in rows : file = 'files/'+str(row[0]) try : os.remove(file) except : print 'file not found or at least can\'t be deleted' # on the db query = """ DELETE FROM File WHERE ( type = 'ticket' AND ticket = %s ) OR ( type='comment' AND ticket in (SELECT id FROM Comment WHERE ticket = %s) ) """ cursor.execute(query, (id, id ) ) # delete comments query = """ DELETE FROM Comment WHERE ticket = %s""" cursor.execute(query, (id, ) ) # delete ticket query = """ DELETE FROM Ticket WHERE relativeID = %s AND project = %s AND category = %s """ cursor.execute(query, (ticketID, cherrypy.session.get(str(project)+'_projectCode'), categoryCode[category])) con.commit() con.close() return 'ok'
def editTicket(self, project, category, relativeID, status, severity, name, description, files=[]): """ editTicket page It works in the same way as newTicket does, but when all arguments are empty, we load values from the DB """ con = getConnection() checkProjectCategoryLogin(con, project, category) checkSuperUserOrCreator(con, project, category, relativeID) categoryCode = {'bug' : 1, 'feature' : 2} cursor = con.cursor() lang = projectLang(con, project) # we now have a well defined session userID = cherrypy.session.get(str(project)+'_id') level = cherrypy.session.get(str(project)+'_loginLevel', 1) idMaintainer = cherrypy.session.get(str(project)+'_idUser', 0) # updating ticket query = """ UPDATE Ticket SET name = %s, description = %s, status = %s, severity = %s, maintainer = %s, lastModification = current_timestamp WHERE relativeID = %s AND project = %s AND category = %s""" description = formatText(description) cursor.execute(query, (name, description, status, severity, idMaintainer, relativeID, cherrypy.session.get(str(project)+'_projectCode'), categoryCode[category] )) # select real id query = """ SELECT id FROM Ticket WHERE project = %s AND relativeID = %s AND category = %s """ cursor.execute(query, (cherrypy.session.get(str(project)+'_projectCode'), relativeID, categoryCode[category] )) ticketID = int(cursor.fetchone()[0]) # attaching files to the ticket query = """ UPDATE File SET ticket = %s WHERE id = %s""" args = [] if type(files) == type('dummy'): args.append((ticketID, files)) elif type(files) == type(['dummy', 'dummy']): for fileID in files : args.append( (ticketID, fileID) ) cursor.executemany(query, args) con.commit() con.close() res = {'relativeID' : relativeID} return demjson.encode(res)
def createProject(self, projectName, projectWebName, login, pwd1, pwd2, email, lang): langs = ['en', 'fr'] if lang not in langs: lang = 'en' # clean data projectName = projectName.strip() projectWebName = projectWebName.strip().lower() login = login.strip() pwd1 = pwd1.strip() pwd2 = pwd2.strip() email = email.strip() # check data errors = { 'validChars': True, 'equalPwd': True, 'validProjectWebName': True, 'emptyPwd': (pwd1 == '' or pwd2 == ''), 'emptyLogin': login=='', 'emptyProjectWebName': projectWebName=='', 'emptyProjectName': projectName == '', 'emptyEMail': email=='', 'ok': False} # the webname does not contains special chars validChars = "0123456789abcdefghijklmnopqrstuvwxyz" for c in projectWebName : if c not in validChars : errors['validChars'] = False # pwd1 and pwd2 are equal if pwd1 != pwd2 : errors['equalPwd'] = False # checking that a project with the same webname does not already exists con = getConnection() cursor = con.cursor() query = """ SELECT count(*) FROM Project WHERE webname = %s""" cursor.execute(query, (projectWebName, )) row = cursor.fetchone() if row[0] >= 1 : errors['validProjectWebName'] = False # if there are errors, just send them back to the page error = errors['emptyPwd'] or errors['emptyLogin'] or errors['emptyProjectWebName'] or errors['emptyProjectName'] or errors['emptyEMail'] error = error or not errors['validChars'] or not errors['equalPwd'] or not errors['validProjectWebName'] if error: return demjson.encode(errors) # no error were found, just go on with the normal procedure # create project query = """ INSERT INTO Project(name, webname, lang) VALUES (%s, %s, %s)""" cursor.execute(query, (projectName, projectWebName, lang)) con.commit() checkProject(con, projectWebName) # add superuser for the project query = """ INSERT INTO Client(login, password, project, level, allowed, mail) VALUES (%s, %s, %s, 2, True, %s); SELECT lastval();""" m = md5.new() m.update(pwd1) m.update(projectWebName) m.update(login) cursor.execute(query, (login, m.hexdigest(), cherrypy.session.get(str(projectWebName)+'_projectCode'), email)) adminID = int(cursor.fetchone()[0]) # updating project owner : query = """ UPDATE Project SET owner = %s WHERE webname = %s""" cursor.execute(query, (adminID, projectWebName)) con.commit() con.close() # send email emailTemplate = Template(filename='templates/'+lang+'/mails/new_project.mail', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') emailContent = emailTemplate.render( to = email, userName = login, projectName = projectName, projectWebName = projectWebName, password = pwd1 ) sendMail(email, emailContent) errors['ok'] = True return demjson.encode(errors)
def projects(self, project, category, ticketID=None): """ Main method of the Class. projects will display the ticket of relative id (ie this is not the real id of the ticket, but the one that will be displayed, in order to get consistency through notations) 'idTicket' """ # creating connection con = getConnection() # getting project lang (used to select templates afterwars) lang = projectLang(con, project) # verifications to ensure that the page can be displayed # this is necessary since values checked here are passed as GET # and thus easily changed by "curious" users checkProjectCategoryLogin(con, project, category) # check that ticketID is a valid ID if not isTicketIDValid(con, project, category, ticketID): # go to "new ticket page" raise cherrypy.HTTPRedirect(url(project, category, 'new')) # loading details about the selected ticket # if the idTicket is not OK, then the selectedTicket hash will be empty selectedTicket = loadSelectedTicket(con, project, category, ticketID) selectedTicketHTML = self.getTicketHTML(project, lang, category, selectedTicket) # loading ticket's comments comments = loadComments(con, project, category, selectedTicket['id']) commentsHTML = self.getCommentsHTML(project, lang, comments) # loading all tickets from the selected project in the selected category # tickets are divided in two lists which are displayed separatly on # the page # note that tickets are already sorted solved = ((selectedTicket['status'] == 1) or (selectedTicket['status'] == 4)) #tickets = loadTickets(con, project, category, solved) #ticketsList = self.getTicketsListHTML(project, category, solved, lang, tickets, selectedTicket['id']) ticketsList = self.getTicketsList(project, category, solved, ticketID) # list of tickets containing modification since last visit #unread = updatedTickets(con, project, category, idTicket) # closing connection con.close() # generating page through template mytemplate = Template(filename='templates/'+lang+'/bugs.html', output_encoding='utf-8', default_filters=['decode.utf8'], input_encoding='utf-8') page = mytemplate.render( projectWebName = project, projectName = cherrypy.session.get(str(project)+'_projectName'), userLevel = cherrypy.session.get(str(project)+'_loginLevel', 1), category = category, ticketsList = ticketsList, selectedTicketRelativeID = selectedTicket['id'], selectedTicketHTML = selectedTicketHTML, comments = commentsHTML, solved = solved ) return page