bottleneckcrm/backend/opportunity/models.py
Ashwin e26ab380fc Add unit tests for ticket board provider, status settable, web forms, CSV import, deal aging badge, and tickets selection
- Implement comprehensive tests for the ticket board provider to validate kanban and pipeline modes, including ticket movement and error handling.
- Create tests for ticket status settable to ensure proper status selection logic.
- Enhance web form model tests to cover ticket-specific fields and submission behavior.
- Introduce CSV import sheet tests to verify file handling and error reporting.
- Add deal aging badge tests to confirm correct display of aging statuses.
- Implement tickets selection tests to ensure UI responsiveness and functionality at various screen sizes.
2026-09-26 16:53:22 +05:30

941 lines
34 KiB
Python

from decimal import Decimal
from django.core.exceptions import ValidationError
from django.db import IntegrityError, models, transaction
from django.db.models import Count, Exists, OuterRef, Q, Sum
from django.db.models.functions import Coalesce
from django.utils import timezone
from django.utils.timesince import timesince
from django.utils.translation import gettext_lazy as _
from accounts.models import Account
from common.base import (
SAMPLE_DATA_HELP_TEXT,
AssignableMixin,
BaseModel,
BaseOrgModel,
)
from common.models import Org, Profile, Tags, Teams
from common.money import deal_currency, org_currency
from common.utils import (
CURRENCY_CODES,
GOAL_TYPES,
OPPORTUNITY_TYPES,
PERIOD_TYPES,
SOURCES,
)
from contacts.models import Contact
from opportunity.workflow import (
CLOSED_KINDS,
DEFAULT_PIPELINE_NAME,
DEFAULT_STAGES,
OPEN,
STAGE_KINDS,
WON,
aging_thresholds,
stage_probability,
)
# Amount source choices for Opportunity
AMOUNT_SOURCE_CHOICES = (
("MANUAL", "Manual"),
("CALCULATED", "Calculated from Products"),
)
# Discount type choices
DISCOUNT_TYPES = (
("PERCENTAGE", "Percentage (%)"),
("FIXED", "Fixed Amount"),
)
class DealPipeline(BaseOrgModel):
"""A named sequence of deal stages. Every org has exactly one default.
The default pipeline is where a deal lands when nothing names a pipeline,
which is how clients that predate pipelines keep working. It is created by
`opportunity/0020` for every org that existed then, and lazily by
`default_for` for any org created since.
"""
name = models.CharField(_("Pipeline Name"), max_length=100)
is_default = models.BooleanField(default=False)
class Meta:
verbose_name = "Deal Pipeline"
verbose_name_plural = "Deal Pipelines"
# Not `deal_pipeline`: that name belongs to an orphan table some
# databases still carry (see opportunity/0015, which leaves it in
# place when it holds rows).
db_table = "opportunity_pipeline"
ordering = ("-is_default", "name")
# Restated: a subclass Meta replaces BaseOrgModel's, index included.
indexes = [models.Index(fields=["org", "-created_at"])]
constraints = [
models.UniqueConstraint(
fields=["org"],
condition=Q(is_default=True),
name="unique_default_deal_pipeline_per_org",
),
models.UniqueConstraint(
fields=["org", "name"], name="unique_deal_pipeline_name_per_org"
),
]
def __str__(self):
return self.name
@classmethod
def default_for(cls, org):
"""The org's default pipeline, created with the seed stages if missing.
Two first requests racing here both try to insert; the partial unique
constraint lets one win and the loser reads the winner's row.
"""
pipeline = cls.objects.filter(org=org, is_default=True).first()
if pipeline is not None:
return pipeline
try:
with transaction.atomic():
pipeline = cls.objects.create(
org=org, name=DEFAULT_PIPELINE_NAME, is_default=True
)
pipeline.seed_stages()
except IntegrityError:
pipeline = cls.objects.get(org=org, is_default=True)
return pipeline
def seed_stages(self):
"""Give a new pipeline the default stages, one of each kind at least."""
DealStage.objects.bulk_create(
DealStage(
org_id=self.org_id,
pipeline=self,
code=code,
label=label,
kind=kind,
order=position,
expected_days=expected_days,
)
for position, (code, label, kind, expected_days) in enumerate(
DEFAULT_STAGES, start=1
)
)
class DealStage(BaseOrgModel):
"""One column of a deal pipeline.
`code` is what `Opportunity.stage` stores. It is fixed at creation, so a
rename changes the label on every deal and board without touching a row.
`kind` is what the stage means: reports, goals and the invoice gate read
it, never the code or the label. `expected_days` / `warning_days` are the
rotting thresholds (see `workflow.aging_thresholds`); empty means deals in
this stage never age.
"""
pipeline = models.ForeignKey(
DealPipeline, on_delete=models.CASCADE, related_name="stages"
)
code = models.CharField(_("Code"), max_length=64)
label = models.CharField(_("Label"), max_length=100)
order = models.PositiveIntegerField(default=0)
kind = models.CharField(max_length=10, choices=STAGE_KINDS, default=OPEN)
expected_days = models.PositiveIntegerField(
_("Expected Days"), null=True, blank=True
)
warning_days = models.PositiveIntegerField(_("Warning Days"), null=True, blank=True)
class Meta:
verbose_name = "Deal Stage"
verbose_name_plural = "Deal Stages"
db_table = "opportunity_pipeline_stage"
ordering = ("order", "created_at")
indexes = [models.Index(fields=["org", "-created_at"])]
constraints = [
models.UniqueConstraint(
fields=["pipeline", "code"], name="unique_deal_stage_code_per_pipeline"
),
]
def __str__(self):
return f"{self.pipeline.name} - {self.label}"
def stage_kind_q(*kinds):
"""A filter matching deals whose current stage is one of `kinds`.
For any Opportunity queryset, including one nested in a subquery:
`.filter(stage_kind_q(WON))`, `.exclude(stage_kind_q(*CLOSED_KINDS))`.
A correlated EXISTS on the (pipeline, code) unique key, so it costs one
index probe per row and no per-request lookup of the org's stages. A deal
whose code its pipeline lacks matches no kind.
"""
return Exists(
DealStage.objects.filter(
pipeline_id=OuterRef("pipeline_id"), code=OuterRef("stage"), kind__in=kinds
)
)
class Opportunity(AssignableMixin, BaseModel):
"""
Opportunity model for CRM - Sales pipeline management
Based on Twenty CRM and Salesforce patterns
"""
# Core Opportunity Information
name = models.CharField(_("Opportunity Name"), max_length=255)
account = models.ForeignKey(
Account,
related_name="opportunities",
on_delete=models.CASCADE,
blank=True,
null=True,
)
# A `DealStage.code` of `pipeline`. Not a FK so a stage keeps one stable
# value on the wire for every client; `save()` keeps it pointing at a real
# stage of the pipeline, and the serializers refuse anything else.
stage = models.CharField(_("Stage"), max_length=64, blank=True)
# RESTRICT, not PROTECT: a pipeline holding deals cannot be deleted on its
# own, but deleting the org takes its deals and pipelines together.
pipeline = models.ForeignKey(
DealPipeline,
on_delete=models.RESTRICT,
related_name="opportunities",
)
opportunity_type = models.CharField(
_("Type"), max_length=64, choices=OPPORTUNITY_TYPES, blank=True, null=True
)
# Financial Information
currency = models.CharField(
_("Currency"), max_length=3, choices=CURRENCY_CODES, blank=True, null=True
)
amount = models.DecimalField(
_("Amount"), decimal_places=2, max_digits=12, blank=True, null=True
)
amount_source = models.CharField(
_("Amount Source"),
max_length=20,
choices=AMOUNT_SOURCE_CHOICES,
default="MANUAL",
)
probability = models.IntegerField(
_("Probability (%)"), default=0, blank=True, null=True
)
closed_on = models.DateField(_("Expected Close Date"), blank=True, null=True)
# Source & Context
lead_source = models.CharField(
_("Lead Source"), max_length=255, choices=SOURCES, blank=True, null=True
)
# Relationships
contacts = models.ManyToManyField(Contact, related_name="opportunity_contacts")
# Assignment
assigned_to = models.ManyToManyField(
Profile, related_name="opportunity_assigned_users"
)
teams = models.ManyToManyField(Teams, related_name="opportunity_teams")
closed_by = models.ForeignKey(
Profile,
on_delete=models.SET_NULL,
null=True,
blank=True,
related_name="opportunity_closed_by",
)
# Tags
tags = models.ManyToManyField(Tags, related_name="opportunity_tags", blank=True)
# Notes
description = models.TextField(_("Notes"), blank=True, null=True)
custom_fields = models.JSONField(
default=dict,
blank=True,
help_text="Per-org schema extension; values are validated against common.CustomFieldDefinition.",
)
# Deal Aging
stage_changed_at = models.DateTimeField(
_("Stage Changed At"), null=True, blank=True
)
# Kanban positioning within a stage column (fractional indexing, same
# convention as Task.kanban_order: large stride between rows lets drag-drop
# insert by averaging neighbors without needing to renumber the column).
kanban_order = models.DecimalField(
_("Kanban Order"),
max_digits=15,
decimal_places=6,
default=0,
help_text="Order within the kanban column for drag-drop positioning",
)
# System Fields
is_active = models.BooleanField(default=True)
is_sample = models.BooleanField(default=False, help_text=SAMPLE_DATA_HELP_TEXT)
org = models.ForeignKey(
Org,
on_delete=models.CASCADE,
related_name="opportunities",
)
class Meta:
verbose_name = "Opportunity"
verbose_name_plural = "Opportunities"
db_table = "opportunity"
ordering = ("-created_at",)
indexes = [
models.Index(fields=["stage"]),
models.Index(fields=["org", "-created_at"]),
models.Index(fields=["stage", "kanban_order"], name="opp_stage_kanban_idx"),
]
constraints = [
# Probability must be 0-100
models.CheckConstraint(
condition=Q(probability__gte=0) & Q(probability__lte=100),
name="opportunity_probability_range",
),
# Amount must be non-negative
models.CheckConstraint(
condition=Q(amount__gte=0) | Q(amount__isnull=True),
name="opportunity_amount_non_negative",
),
]
def __str__(self):
return f"{self.name}"
@property
def created_on_arrow(self):
return timesince(self.created_at) + " ago"
def clean(self):
"""Validate opportunity data against the kind of stage it sits in."""
super().clean()
errors = {}
stage = self.current_stage()
kind = stage.kind if stage else None
if kind in CLOSED_KINDS and not self.closed_on:
errors["closed_on"] = _("Close date is required when a deal is closed")
if kind == WON and not self.amount:
errors["amount"] = _("Amount is required for a won deal")
if errors:
raise ValidationError(errors)
def current_stage(self, stages=None):
"""The `DealStage` this deal sits in, or None if its pipeline lacks the code.
`stages` is an optional `{(pipeline_id, code): DealStage}` map, see
`opportunity.stages.stage_index`; pass it when reading many deals so
each row does not query.
"""
if stages is not None:
return stages.get((self.pipeline_id, self.stage))
# An unsaved deal with no pipeline yet is headed for the default one
# (see `save`), so that is where its code is looked up.
pipeline_id = self.pipeline_id or DealPipeline.default_for(self.org).id
return DealStage.objects.filter(
org_id=self.org_id, pipeline_id=pipeline_id, code=self.stage
).first()
def recalculate_amount(self):
"""
Recalculate opportunity amount from line items.
Only updates if amount_source is CALCULATED or if line items exist.
Returns True if amount was updated, False otherwise.
"""
line_items = self.line_items.all()
if line_items.exists():
total = line_items.aggregate(total=Sum("total"))["total"] or Decimal("0")
self.amount = total
self.amount_source = "CALCULATED"
return True
if self.amount_source == "CALCULATED":
# No line items but was calculated - reset to manual
self.amount_source = "MANUAL"
self.amount = Decimal("0")
return True
return False
@property
def days_in_current_stage(self):
"""Number of days the deal has been in its current stage."""
if not self.stage_changed_at:
return 0
delta = timezone.now() - self.stage_changed_at
return delta.days
def get_aging_status(self, stages=None):
"""Return 'green', 'yellow', or 'red' based on deal aging.
Thresholds come from the deal's `DealStage` (see
`workflow.aging_thresholds`). A closed stage, a stage with no expected
days, and a code the pipeline does not know all read as green.
`stages` is passed through to `current_stage`.
"""
stage = self.current_stage(stages)
if stage is None:
return "green"
thresholds = aging_thresholds(
stage.kind, stage.expected_days, stage.warning_days
)
if thresholds is None:
return "green"
yellow_days, red_days = thresholds
days = self.days_in_current_stage
if days >= red_days:
return "red"
if days >= yellow_days:
return "yellow"
return "green"
@property
def aging_status(self):
"""Return 'green', 'yellow', or 'red' based on deal aging."""
return self.get_aging_status()
def _settle_stage(self):
"""Put the deal on a real stage of its pipeline and return that stage.
A code the pipeline does not have (an internal caller such as lead
conversion naming a stage an admin has since deleted, or a blank one)
falls back to the pipeline's first open stage, so no deal is ever
stranded outside every column. The lookups carry the deal's org, so a
pipeline from another org resolves to nothing and is refused.
"""
stage = self.current_stage()
if stage is None:
stage = (
DealStage.objects.filter(
org_id=self.org_id, pipeline_id=self.pipeline_id, kind=OPEN
)
.order_by("order", "created_at")
.first()
)
if stage is None:
raise ValidationError(
{"pipeline": _("This pipeline has no open stage for the deal.")}
)
self.stage = stage.code
return stage
def save(self, *args, **kwargs):
"""Settle the pipeline and stage, auto-set probability, track stage changes."""
if not self.pipeline_id:
self.pipeline = DealPipeline.default_for(self.org)
extra_fields = set()
if self._state.adding:
moved = True
if not self.stage_changed_at:
self.stage_changed_at = timezone.now()
else:
old = (
Opportunity.objects.filter(pk=self.pk)
.values_list("stage", "pipeline_id")
.first()
)
moved = old is not None and old != (self.stage, self.pipeline_id)
if moved:
self.stage_changed_at = timezone.now()
extra_fields |= {"stage", "stage_changed_at"}
# Auto-set probability from the stage (only if it is default/0).
if moved or not self.probability:
stage = self._settle_stage() if moved else self.current_stage()
if not self.probability and stage is not None:
self.probability = stage_probability(stage.code, stage.kind)
extra_fields.add("probability")
# Keep a narrowed save from dropping what the lines above changed.
if kwargs.get("update_fields") is not None and extra_fields:
kwargs["update_fields"] = list(set(kwargs["update_fields"]) | extra_fields)
super().save(*args, **kwargs)
class OpportunityLineItem(BaseModel):
"""
Line item for an opportunity - represents a product or service being quoted.
Similar to InvoiceLineItem but for the pre-sale stage.
"""
opportunity = models.ForeignKey(
Opportunity,
on_delete=models.CASCADE,
related_name="line_items",
)
product = models.ForeignKey(
"invoices.Product",
on_delete=models.SET_NULL,
null=True,
blank=True,
related_name="opportunity_line_items",
)
# Item details (can override product info or be custom)
name = models.CharField(_("Item Name"), max_length=255, blank=True)
description = models.CharField(_("Description"), max_length=500, blank=True)
# Quantity and pricing
quantity = models.DecimalField(
_("Quantity"), max_digits=10, decimal_places=2, default=1
)
unit_price = models.DecimalField(
_("Unit Price"), max_digits=12, decimal_places=2, default=0
)
# Per-item discount
discount_type = models.CharField(
_("Discount Type"), max_length=20, choices=DISCOUNT_TYPES, blank=True
)
discount_value = models.DecimalField(
_("Discount Value"), max_digits=12, decimal_places=2, default=0
)
discount_amount = models.DecimalField(
_("Discount Amount"), max_digits=12, decimal_places=2, default=0
)
# Computed totals
subtotal = models.DecimalField(
_("Subtotal"), max_digits=12, decimal_places=2, default=0
)
total = models.DecimalField(_("Total"), max_digits=12, decimal_places=2, default=0)
# Display order
order = models.PositiveIntegerField(_("Order"), default=0)
# Organization (for RLS)
org = models.ForeignKey(
Org,
on_delete=models.CASCADE,
related_name="opportunity_line_items",
)
class Meta:
verbose_name = "Opportunity Line Item"
verbose_name_plural = "Opportunity Line Items"
db_table = "opportunity_line_item"
ordering = ["order", "created_at"]
indexes = [
models.Index(fields=["opportunity"]),
models.Index(fields=["org"]),
]
def __str__(self):
display_name = (
self.name if self.name else (self.product.name if self.product else "Item")
)
return f"{display_name} x {self.quantity}"
def save(self, *args, **kwargs):
"""Calculate totals before saving."""
# Get name from product if not set
if not self.name and self.product:
self.name = self.product.name
# Get unit price from product if not set and product exists
if self.unit_price == 0 and self.product:
self.unit_price = self.product.price or Decimal("0")
# Calculate subtotal
self.subtotal = self.quantity * self.unit_price
# Calculate discount
if self.discount_type == "PERCENTAGE":
self.discount_amount = self.subtotal * (
self.discount_value / Decimal("100")
)
else:
self.discount_amount = self.discount_value
# Calculate total (no tax at opportunity stage)
self.total = self.subtotal - self.discount_amount
# Ensure org is set from opportunity if not provided
if not self.org_id and self.opportunity_id:
self.org_id = self.opportunity.org_id
super().save(*args, **kwargs)
# Recalculate opportunity amount after saving line item
if self.opportunity:
self.opportunity.recalculate_amount()
self.opportunity.save(update_fields=["amount", "amount_source"])
def delete(self, *args, **kwargs):
"""Recalculate opportunity amount after deleting line item."""
opportunity = self.opportunity
super().delete(*args, **kwargs)
# Recalculate opportunity amount after deletion
if opportunity:
opportunity.recalculate_amount()
opportunity.save(update_fields=["amount", "amount_source"])
class SalesGoal(BaseModel):
"""Sales goal / quota for tracking revenue or deals closed targets."""
name = models.CharField(_("Goal Name"), max_length=255)
goal_type = models.CharField(_("Goal Type"), max_length=20, choices=GOAL_TYPES)
target_value = models.DecimalField(
_("Target Value"), max_digits=12, decimal_places=2
)
# A target is one number, so a goal is in one currency. There are no
# exchange rates, so a REVENUE goal counts only the won deals in this
# currency. Declared like `Opportunity.currency`; `save()` fills a blank one
# from the org's default, so a stored goal always has one.
currency = models.CharField(
_("Currency"), max_length=3, choices=CURRENCY_CODES, blank=True, null=True
)
period_type = models.CharField(
_("Period Type"), max_length=20, choices=PERIOD_TYPES
)
period_start = models.DateField(_("Period Start"))
period_end = models.DateField(_("Period End"))
assigned_to = models.ForeignKey(
Profile,
on_delete=models.SET_NULL,
null=True,
blank=True,
related_name="sales_goals",
)
team = models.ForeignKey(
Teams,
on_delete=models.SET_NULL,
null=True,
blank=True,
related_name="sales_goals",
)
type_weights = models.JSONField(
_("Deal Type Weights"),
default=dict,
blank=True,
help_text=(
"Optional multiplier per opportunity type, e.g. "
'{"RENEWAL": 0.5, "NEW_BUSINESS": 1.5}. A type left out of the map, '
"and a deal with no type at all, counts at its full value. An empty "
"map is an unweighted goal. Not accepted on an ACTIVITIES goal, "
"which has no deal type to weigh."
),
)
is_active = models.BooleanField(default=True)
milestone_50_notified = models.BooleanField(default=False)
milestone_90_notified = models.BooleanField(default=False)
milestone_100_notified = models.BooleanField(default=False)
org = models.ForeignKey(
Org,
on_delete=models.CASCADE,
related_name="sales_goals",
)
class Meta:
verbose_name = "Sales Goal"
verbose_name_plural = "Sales Goals"
db_table = "sales_goal"
ordering = ["-created_at"]
indexes = [
models.Index(fields=["org", "-created_at"]),
models.Index(fields=["org", "period_start", "period_end"]),
]
def __str__(self):
return f"{self.name} ({self.get_goal_type_display()})"
def save(self, *args, **kwargs):
if not self.currency:
self.currency = org_currency(self.org)
super().save(*args, **kwargs)
def member_profile_ids(self):
"""Profile ids whose work counts toward this goal, or None for the org.
`None` is "everybody in the org", which is what an unassigned goal
means. It is distinct from `[]`, an empty team, which counts nothing.
"""
if self.assigned_to_id:
return [self.assigned_to_id]
if self.team_id:
return list(
Profile.objects.filter(
user_teams=self.team_id, is_active=True
).values_list("id", flat=True)
)
return None
def weight_for(self, deal_type):
"""The multiplier this goal applies to one opportunity type.
A type missing from the map, and a deal saved without a type at all,
weigh 1: an unlisted type is not a type worth nothing, it is a type
nobody chose to re-weigh. `type_weights` is validated on write, so the
values here are already numbers.
"""
if not self.type_weights or not deal_type:
return Decimal("1")
weight = self.type_weights.get(deal_type)
return Decimal("1") if weight is None else Decimal(str(weight))
def tally_to_progress(self, tally):
"""Fold a per-deal-type tally into this goal's single progress number.
`tally` maps an opportunity type (or None) to `(amount, count)`. Both
the single-goal query and the batched `attach_progress` build one of
these, so the weighting arithmetic has exactly one definition rather
than a copy per call site.
"""
unit = 0 if self.goal_type == "REVENUE" else 1
total = Decimal("0")
for deal_type, amounts in tally.items():
total += self.weight_for(deal_type) * amounts[unit]
return total
def _opportunity_queryset(self):
"""Won deals in this goal's period, one row per deal.
`Opportunity.assigned_to` is a ManyToMany, so filtering a team goal with
`assigned_to__in=<members>` joins the through table and returns a deal
once per member who holds it. Summing that double-counted a deal shared
by two teammates and inflated the team's attainment. The membership test
is a subquery on `id` instead, which is set membership and cannot
duplicate a row.
"""
opps = Opportunity.objects.filter(
stage_kind_q(WON),
org=self.org,
closed_on__gte=self.period_start,
closed_on__lte=self.period_end,
)
member_ids = self.member_profile_ids()
if member_ids is None:
return opps
return opps.filter(
id__in=Opportunity.objects.filter(assigned_to__in=member_ids).values("id")
)
def _activity_count(self):
"""Activities logged by this goal's owners inside the period."""
from common.models import Activity
activities = Activity.objects.filter(
org=self.org,
created_at__date__gte=self.period_start,
created_at__date__lte=self.period_end,
)
member_ids = self.member_profile_ids()
if member_ids is not None:
activities = activities.filter(user_id__in=member_ids)
return Decimal(str(activities.count()))
def compute_progress(self):
"""Compute current progress toward this goal.
REVENUE and DEALS_CLOSED read won opportunities in the period;
ACTIVITIES counts logged activity instead, so `type_weights` does not
apply to it and is refused on write. REVENUE sums only the deals in the
goal's currency (a blank deal currency is the org's default, per
`deal_currency`); the two count types count deals in any currency.
Results are cached on the instance to avoid redundant DB queries when
progress_percent and status are accessed in the same request. For a
collection of goals, call `SalesGoal.attach_progress` first: it fills
this same cache for every goal in a fixed number of queries.
"""
if hasattr(self, "_cached_progress"):
return self._cached_progress
if self.goal_type == "ACTIVITIES":
result = self._activity_count()
else:
opps = self._opportunity_queryset()
if self.goal_type == "REVENUE":
opps = opps.annotate(counted_in=deal_currency(self.org)).filter(
counted_in=self.currency
)
# Grouped by type even when unweighted: it is still one query, and
# it keeps both progress paths reading the same tally shape.
tally = {
row["opportunity_type"]: (
row["amount_total"],
Decimal(str(row["deal_count"])),
)
for row in opps.values("opportunity_type").annotate(
amount_total=Coalesce(Sum("amount"), Decimal("0")),
deal_count=Count("id"),
)
}
result = self.tally_to_progress(tally)
self._cached_progress = result
return result
@classmethod
def attach_progress(cls, goals):
"""Populate every goal's progress cache in a fixed number of queries.
Serializing a list called `compute_progress()` once per goal, so a page
of N goals cost N aggregate queries (the web list asks for up to 1000).
This reads the won deals and activities that span the whole collection's
period once, then does the per-goal arithmetic in Python through the
same `tally_to_progress` the single-goal path uses.
Returns the list it was given, so callers can wrap a queryset inline.
"""
goals = list(goals)
pending = [g for g in goals if not hasattr(g, "_cached_progress")]
if not pending:
return goals
org_ids = {g.org_id for g in pending}
period_start = min(g.period_start for g in pending)
period_end = max(g.period_end for g in pending)
# One query for every team referenced, so a page of team goals does not
# re-read the same membership per goal.
team_ids = {g.team_id for g in pending if g.team_id and not g.assigned_to_id}
members_by_team = {team_id: [] for team_id in team_ids}
if team_ids:
for team_id, profile_id in Profile.objects.filter(
user_teams__in=team_ids, is_active=True
).values_list("user_teams__id", "id"):
if team_id in members_by_team:
members_by_team[team_id].append(profile_id)
deal_goals = [g for g in pending if g.goal_type != "ACTIVITIES"]
deals = []
# One query per org, because the currency a blank-currency deal counts
# in is that org's default. Every caller passes one org's goals, so in
# practice this is one org lookup and one deal query.
for org in Org.objects.filter(id__in={g.org_id for g in deal_goals}):
# One row per (deal, assignee); an unassigned deal still arrives
# once, with a null profile, because an org-wide goal counts it.
deals.extend(
Opportunity.objects.filter(
stage_kind_q(WON),
org=org,
closed_on__gte=period_start,
closed_on__lte=period_end,
)
.annotate(counted_in=deal_currency(org))
.values_list(
"id",
"org_id",
"closed_on",
"opportunity_type",
"amount",
"assigned_to__id",
"counted_in",
)
)
activity_goals = [g for g in pending if g.goal_type == "ACTIVITIES"]
activities = []
if activity_goals:
from common.models import Activity
activities = list(
Activity.objects.filter(
org_id__in=org_ids,
created_at__date__gte=period_start,
created_at__date__lte=period_end,
).values_list("org_id", "created_at", "user_id")
)
for goal in pending:
if goal.assigned_to_id:
member_ids = {goal.assigned_to_id}
elif goal.team_id:
member_ids = set(members_by_team.get(goal.team_id, []))
else:
member_ids = None
if goal.goal_type == "ACTIVITIES":
goal._cached_progress = Decimal(
str(
sum(
1
for org_id, created_at, user_id in activities
if org_id == goal.org_id
and goal.period_start
<= timezone.localtime(created_at).date()
<= goal.period_end
and (member_ids is None or user_id in member_ids)
)
)
)
continue
tally = {}
counted = set()
for (
deal_id,
org_id,
closed_on,
deal_type,
amount,
profile_id,
currency,
) in deals:
if org_id != goal.org_id or deal_id in counted:
continue
if goal.goal_type == "REVENUE" and currency != goal.currency:
continue
if not (goal.period_start <= closed_on <= goal.period_end):
continue
if member_ids is not None and profile_id not in member_ids:
continue
# `counted` is what stops a deal shared by two teammates from
# landing in the tally twice, the same duplicate the single-goal
# subquery avoids in SQL.
counted.add(deal_id)
amount_total, deal_count = tally.get(
deal_type, (Decimal("0"), Decimal("0"))
)
tally[deal_type] = (
amount_total + (amount or Decimal("0")),
deal_count + 1,
)
goal._cached_progress = goal.tally_to_progress(tally)
return goals
@property
def progress_percent(self):
"""Progress as an integer percentage, capped at 100."""
if not self.target_value or self.target_value == 0:
return 0
progress = self.compute_progress()
return min(int(progress / self.target_value * 100), 100)
@property
def status(self):
"""Goal status based on progress vs expected pace."""
percent = self.progress_percent
if percent >= 100:
return "completed"
today = timezone.localdate()
if today < self.period_start:
expected_pace = 0
elif today >= self.period_end:
expected_pace = 100
else:
total_days = (self.period_end - self.period_start).days or 1
elapsed_days = (today - self.period_start).days
expected_pace = (elapsed_days / total_days) * 100
if expected_pace == 0:
return "on_track"
if percent >= expected_pace:
return "on_track"
if percent >= expected_pace * 0.8:
return "at_risk"
return "behind"