|
|
""" Compute statistics of the mass mailing campaign """
default_vals = {
'received_ratio': 0,
'opened_ratio': 0,
'replied_ratio': 0,
'bounced_ratio': 0
}
if not self.ids:
self.update(default_vals)
return
self.env.cr.execute("""
SELECT
c.id as campaign_id,
COUNT(s.id) AS expected,
COUNT(s.sent_datetime) AS sent,
COUNT(s.trace_status) FILTER (WHERE s.trace_status in ('sent', 'open', 'reply')) AS delivered,
COUNT(s.trace_status) FILTER (WHERE s.trace_status in ('open', 'reply')) AS open,
COUNT(s.trace_status) FILTER (WHERE s.trace_status = 'reply') AS reply,
COUNT(s.trace_status) FILTER (WHERE s.trace_status = 'bounce') AS bounce,
COUNT(s.trace_status) FILTER (WHERE s.trace_status = 'cancel') AS cancel
FROM
mailing_trace s
RIGHT JOIN
utm_campaign c
ON (c.id = s.campaign_id)
WHERE
c.id IN %s
GROUP BY
c.id
""", (tuple(self.ids), ))
all_stats = self.env.cr.dictfetchall()
stats_per_campaign = {
stats['campaign_id']: stats
for stats in all_stats
}
for campaign in self:
stats = stats_per_campaign.get(campaign.id)
if not stats:
vals = default_vals
else:
total = (stats['expected'] - stats['cancel']) or 1
delivered = stats['sent'] - stats['bounce']
vals = {
'received_ratio': float_round(100.0 * delivered / total, precision_digits=2),
'opened_ratio': float_round(100.0 * stats['open'] / total, precision_digits=2),
'replied_ratio': float_round(100.0 * stats['reply'] / total, precision_digits=2),
'bounced_ratio': float_round(100.0 * stats['bounce'] / total, precision_digits=2)
}
campaign.update(vals)
|
|
