backlog/main/models.py

import datetime
import uuid

from django.contrib.auth.models import User
from django.core import serializers
from django.db import models, connection
from django.db.models.aggregates import Sum
from martor.models import MartorField


class BaseModel(models.Model):
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)
    objects = models.Manager()
    uuid = models.UUIDField(default=uuid.uuid4, editable=False)
    
    class Meta:
        abstract = True

    def model_name(self):
        try:
            return self._meta.verbose_name
        except AttributeError:
            return ""

    def comments(self):
        return Comment.objects.filter(obj_uuid = self.uuid)

    def to_json(self):
        return serializers.serialize('json', [ self, ])

class Project(BaseModel):
    class Meta:
        verbose_name = "projet"
        verbose_name_plural = "projets"
        
    name = models.CharField(max_length=200)
    description = MartorField(blank=True, default="", verbose_name="Description")
    color = models.CharField(max_length=7, default="#f6755e")
    
    def __str__(self):
        return self.name
    
class Epic(BaseModel):
    class Meta:
        verbose_name = "epic"
        verbose_name_plural = "epics"
        ordering = ('-value', )
        
    SIZES = (("XXS","XXS (< 1 sprints)"), 
             ("XS", "XS (1 sprint)"), 
             ("S", "S (1-2 sprints)"), 
             ("M", "M (2-3 sprints)"), 
             ("L", "L (3-4 sprints)"), 
             ("XL", "XL (4-6 sprints)"), 
             ("XXL", "XXL  (> 6 sprints)"))
    
    name = models.CharField(max_length=200, default="", verbose_name="Nom")
    size = models.CharField(max_length=10, default="M", choices=SIZES, verbose_name="Taille")
    value = models.IntegerField(default=0, verbose_name="Valeur")
    description = MartorField(blank=True, default="", verbose_name="Description")
    project = models.ForeignKey(Project, on_delete=models.PROTECT, null=True, verbose_name="Projet")
    closed = models.BooleanField(default=False, verbose_name="Clôturée")
    
    def __str__(self):
        return self.name
    
    def nb_stories(self):
        return len(self.stories.all())
    
    def nb_closed_stories(self):
        return len(self.stories.filter(closed=True))
    
    def nb_active_stories(self):
        res = 0
        for story in self.stories.filter(closed=False):
            if story.running():
                res += 1
        return res
    
    def contributors(self):
        qry = User.objects.raw("""SELECT DISTINCT auth_user.* 
                                    FROM ((auth_user INNER JOIN main_story_assignees ON auth_user.id = main_story_assignees.user_id) 
                                    INNER JOIN main_story ON main_story_assignees.story_id = main_story.id)
                                    WHERE main_story.epic_id = {};
                                    """.format(self.id))
        return ", ".join(u.username for u in qry)
    
class Sprint(BaseModel):
    class Meta:
        verbose_name_plural = "sprint"
        verbose_name_plural = "sprints"
        ordering = ('-date_start', )
    date_start = models.DateField()
    date_end = models.DateField()
    retro = MartorField(blank=True, default="", verbose_name="Bilan / Rétrospective")
        
    def __str__(self):
        return "Sprint #{} ({:%d/%m/%Y} > {:%d/%m/%Y})".format(self.id, self.date_start, self.date_end)
    
    def running(self):
        return self.date_start < datetime.date.today() <= self.date_end
    
    def nb_stories(self):
        return self.stories.count()
    
    def planned_velocity(self):
        total =  self.stories.aggregate(Sum('weight'))['weight__sum']
        return total if total is not None else "NA"
    
    def real_velocity(self):
        if datetime.date.today() < self.date_start:
            return "NA"
        sql = """SELECT SUM(main_story.weight) as vel
                FROM main_story 
                    INNER JOIN (SELECT story_id, max(sprint_id) as sprint_id
                                FROM main_story_sprints 
                                GROUP BY story_id) as last_sprints 
                    ON main_story.id = last_sprints.story_id
                WHERE last_sprints.sprint_id = {} AND closed = True;
            """.format(self.id)
        with connection.cursor() as cursor:
            cursor.execute(sql)
            row = cursor.fetchone()
        return row[0] if row[0] else 0

    @classmethod
    def current(cls):
        today = datetime.datetime.today()
        try:
            return Sprint.objects.filter(date_start__lte = today).filter(date_end__gt=today)[0]
        except IndexError:
            return None

class Story(BaseModel):
    class Meta:
        verbose_name = "story"
        verbose_name_plural = "stories"
        ordering = ('closed', '-updated')
        
    WEIGHTS = ((1, 1),(2, 2),(3, 3),(5, 5),(8, 8),(13, 13),(21, 21))
        
    epic = models.ForeignKey(Epic, 
                             on_delete=models.PROTECT, 
                             null=True, blank=True,
                             related_name="stories", 
                             verbose_name="Epic")
    name = models.CharField(max_length=200, default="", verbose_name="Nom")
    weight = models.IntegerField(blank=True, choices=WEIGHTS, verbose_name="Poids")
    description = MartorField(blank=True, default="", verbose_name="Description")
    closed = models.BooleanField(default=False, verbose_name="Clôturée")
    author = models.ForeignKey(User, 
                               on_delete=models.PROTECT, 
                               null=True, blank=True,
                               related_name="stories",
                               related_query_name="story", 
                               verbose_name="Auteur")
    
    assignees = models.ManyToManyField(User, 
                                       blank=True,
                                       related_name="assigned", 
                                       verbose_name="Assignés")
    
    sprints = models.ManyToManyField(Sprint, 
                                       blank=True,
                                       related_name="stories",
                                       related_query_name="story", 
                                       verbose_name="Sprints")
    
    def __str__(self):
        return self.name
                
    def running(self):
        for sprint in self.sprints.all():
            if sprint.running():
                return True
        return False


class Comment(BaseModel):
    class Meta:
        verbose_name = "commentaire"
        verbose_name_plural = "commentaires"
        ordering = ('-created', )
        
    def __str__(self):
        return "De {}, le {:%Y-%m-%d}: {}".format(self.author.username, self.created, self.content[:30])
                
    obj_uuid = models.UUIDField(default="")
    content = MartorField(blank=False, default="", verbose_name="Commentaire")
    author = models.ForeignKey(User, 
                               on_delete=models.PROTECT, 
                               verbose_name="Auteur")