def aggregate_monthly_churn(organization,
account,
interval,
from_date=None,
orig='orig',
dest='dest'):
"""
Returns a table of records over a period of 12 months *from_date*.
"""
#pylint: disable=too-many-locals,too-many-arguments
customers = []
receivables = []
new_customers = []
new_receivables = []
churn_customers = []
churn_receivables = []
# We want to be able to compare *last* to *from_date* and not get django
# warnings because timezones are not specified.
dates = month_periods(13, from_date)
trail_period_start = dates[0]
period_start = dates[1]
for period_end in dates[2:]:
if interval == Plan.YEARLY:
prev_period_start = datetime(day=period_start.day,
month=period_start.month,
year=period_start.year - 1,
tzinfo=period_start.tzinfo)
prev_period_end = datetime(day=period_end.day,
month=period_end.month,
year=period_end.year - 1,
tzinfo=period_end.tzinfo)
else:
# default to monthly
prev_period_start = trail_period_start
prev_period_end = period_start
churn_query = RawQuery(
"""SELECT COUNT(DISTINCT(prev.%(dest)s_organization_id)),
SUM(prev.%(dest)s_amount)
FROM saas_transaction prev
LEFT OUTER JOIN (
SELECT distinct(%(dest)s_organization_id)
FROM saas_transaction
WHERE created_at >= '%(period_start)s'
AND created_at < '%(period_end)s'
AND %(orig)s_organization_id = '%(organization_id)s'
AND %(orig)s_account = '%(account)s') curr
ON prev.%(dest)s_organization_id = curr.%(dest)s_organization_id
WHERE prev.created_at >= '%(prev_period_start)s'
AND prev.created_at < '%(prev_period_end)s'
AND prev.%(orig)s_organization_id = '%(organization_id)s'
AND prev.%(orig)s_account = '%(account)s'
AND curr.%(dest)s_organization_id IS NULL""" % {
"orig": orig,
"dest": dest,
"prev_period_start": prev_period_start,
"prev_period_end": prev_period_end,
"period_start": period_start,
"period_end": period_end,
"organization_id": organization.id,
"account": account
}, router.db_for_read(Transaction))
churn_customer, churn_receivable = next(iter(churn_query))
# A bit ugly but it does the job ...
if orig == 'orig':
kwargs = {
'orig_organization': organization,
'orig_account': account
}
else:
kwargs = {
'dest_organization': organization,
'dest_account': account
}
query_result = Transaction.objects.filter(
created_at__gte=period_start, created_at__lt=period_end,
**kwargs).aggregate(Count('%s_organization' % dest, distinct=True),
Sum('%s_amount' % dest))
customer = query_result['%s_organization__count' % dest]
receivable = query_result['%s_amount__sum' % dest]
new_query = RawQuery(
"""SELECT count(distinct(curr.%(dest)s_organization_id)),
SUM(curr.%(dest)s_amount)
FROM saas_transaction curr
LEFT OUTER JOIN (
SELECT distinct(%(dest)s_organization_id)
FROM saas_transaction
WHERE created_at >= '%(prev_period_start)s'
AND created_at < '%(prev_period_end)s'
AND %(orig)s_organization_id = '%(organization_id)s'
AND %(orig)s_account = '%(account)s') prev
ON curr.%(dest)s_organization_id = prev.%(dest)s_organization_id
WHERE curr.created_at >= '%(period_start)s'
AND curr.created_at < '%(period_end)s'
AND curr.%(orig)s_organization_id = '%(organization_id)s'
AND curr.%(orig)s_account = '%(account)s'
AND prev.%(dest)s_organization_id IS NULL""" % {
"orig": orig,
"dest": dest,
"prev_period_start": prev_period_start,
"prev_period_end": prev_period_end,
"period_start": period_start,
"period_end": period_end,
"organization_id": organization.id,
"account": account
}, router.db_for_read(Transaction))
new_customer, new_receivable = next(iter(new_query))
period = period_end
churn_customers += [(period, churn_customer)]
churn_receivables += [(period, int(churn_receivable or 0))]
customers += [(period, customer)]
receivables += [(period, int(receivable or 0))]
new_customers += [(period, new_customer)]
new_receivables += [(period, int(new_receivable or 0))]
trail_period_start = period_start
period_start = period_end
return ((churn_customers, customers, new_customers),
(churn_receivables, receivables, new_receivables))
def _aggregate_transactions_change_by_period(organization,
account,
date_periods,
orig='orig',
dest='dest'):
"""
Returns a table of records over a period of 12 months *from_date*.
"""
#pylint:disable=too-many-locals,too-many-arguments,too-many-statements
#pylint:disable=invalid-name
customers = []
receivables = []
new_customers = []
new_receivables = []
churn_customers = []
churn_receivables = []
unit = None
period_start = date_periods[0]
for period_end in date_periods[1:]:
delta = Plan.get_natural_period(1, organization.natural_interval)
prev_period_end = period_end - delta
prev_period_start = prev_period_end - relativedelta(
period_end, period_start)
LOGGER.debug(
"computes churn between periods ['%s', '%s'] and ['%s', '%s']",
prev_period_start.isoformat(), prev_period_end.isoformat(),
period_start.isoformat(), period_end.isoformat())
try:
churn_query = RawQuery(
"""SELECT COUNT(DISTINCT(prev.%(dest)s_organization_id)),
SUM(prev.%(dest)s_amount),
prev.%(dest)s_unit
FROM saas_transaction prev
LEFT OUTER JOIN (
SELECT distinct(%(dest)s_organization_id), %(orig)s_unit
FROM saas_transaction
WHERE created_at >= '%(period_start)s'
AND created_at < '%(period_end)s'
AND %(orig)s_organization_id = '%(organization_id)s'
AND %(orig)s_account = '%(account)s'
) curr
ON prev.%(dest)s_organization_id = curr.%(dest)s_organization_id
WHERE prev.created_at >= '%(prev_period_start)s'
AND prev.created_at < '%(prev_period_end)s'
AND prev.%(orig)s_organization_id = '%(organization_id)s'
AND prev.%(orig)s_account = '%(account)s'
AND curr.%(dest)s_organization_id IS NULL
GROUP BY prev.%(dest)s_unit
""" % {
"orig": orig,
"dest": dest,
"prev_period_start": prev_period_start,
"prev_period_end": prev_period_end,
"period_start": period_start,
"period_end": period_end,
"organization_id": organization.id,
"account": account
}, router.db_for_read(Transaction))
churn_customer, churn_receivable, churn_receivable_unit = next(
iter(churn_query))
if churn_receivable_unit:
unit = churn_receivable_unit
except StopIteration:
churn_customer, churn_receivable, churn_receivable_unit = 0, 0, None
# A bit ugly but it does the job ...
if orig == 'orig':
kwargs = {
'orig_organization': organization,
'orig_account': account
}
else:
kwargs = {
'dest_organization': organization,
'dest_account': account
}
customer = 0
receivable = 0
receivable_unit = None
query_result = Transaction.objects.filter(
created_at__gte=period_start, created_at__lt=period_end,
**kwargs).values('%s_unit' % dest).annotate(
count=Count('%s_organization' % dest, distinct=True),
sum=Sum('%s_amount' % dest))
if query_result:
customer = query_result[0]['count']
receivable = query_result[0]['sum']
receivable_unit = query_result[0]['%s_unit' % dest]
if receivable_unit:
unit = receivable_unit
try:
new_query = RawQuery(
"""SELECT count(distinct(curr.%(dest)s_organization_id)),
SUM(curr.%(dest)s_amount),
curr.%(dest)s_unit
FROM saas_transaction curr
LEFT OUTER JOIN (
SELECT distinct(%(dest)s_organization_id)
FROM saas_transaction
WHERE created_at >= '%(prev_period_start)s'
AND created_at < '%(prev_period_end)s'
AND %(orig)s_organization_id = '%(organization_id)s'
AND %(orig)s_account = '%(account)s') prev
ON curr.%(dest)s_organization_id = prev.%(dest)s_organization_id
WHERE curr.created_at >= '%(period_start)s'
AND curr.created_at < '%(period_end)s'
AND curr.%(orig)s_organization_id = '%(organization_id)s'
AND curr.%(orig)s_account = '%(account)s'
AND prev.%(dest)s_organization_id IS NULL
GROUP BY curr.%(dest)s_unit""" % {
"orig": orig,
"dest": dest,
"prev_period_start": prev_period_start,
"prev_period_end": prev_period_end,
"period_start": period_start,
"period_end": period_end,
"organization_id": organization.id,
"account": account
}, router.db_for_read(Transaction))
new_customer, new_receivable, new_receivable_unit = next(
iter(new_query))
if new_receivable_unit:
unit = new_receivable_unit
except StopIteration:
new_customer, new_receivable, new_receivable_unit = 0, 0, None
units = get_different_units(churn_receivable_unit, receivable_unit,
new_receivable_unit)
if len(units) > 1:
LOGGER.error("different units: %s", units)
period = period_end
churn_customers += [(period, churn_customer)]
churn_receivables += [(period, int(churn_receivable or 0))]
customers += [(period, customer)]
receivables += [(period, int(receivable or 0))]
new_customers += [(period, new_customer)]
new_receivables += [(period, int(new_receivable or 0))]
period_start = period_end
return ((churn_customers, customers, new_customers),
(churn_receivables, receivables, new_receivables), unit)
def _aggregate_transactions_change_by_period(organization,
account,
date_periods,
orig='orig',
dest='dest'):
"""
Returns a table of records over a period of 12 months *from_date*.
"""
#pylint: disable=too-many-locals,too-many-arguments,invalid-name
customers = []
receivables = []
new_customers = []
new_receivables = []
churn_customers = []
churn_receivables = []
period_start = date_periods[0]
for period_end in date_periods[1:]:
delta = Plan.get_natural_period(1, organization.natural_interval)
prev_period_end = period_end - delta
prev_period_start = prev_period_end - relativedelta(
period_end, period_start)
LOGGER.debug(
"computes churn between periods ['%s', '%s'] and ['%s', '%s']",
prev_period_start.isoformat(), prev_period_end.isoformat(),
period_start.isoformat(), period_end.isoformat())
churn_query = RawQuery(
"""SELECT COUNT(DISTINCT(prev.%(dest)s_organization_id)),
SUM(prev.%(dest)s_amount)
FROM saas_transaction prev
LEFT OUTER JOIN (
SELECT distinct(%(dest)s_organization_id)
FROM saas_transaction
WHERE created_at >= '%(period_start)s'
AND created_at < '%(period_end)s'
AND %(orig)s_organization_id = '%(organization_id)s'
AND %(orig)s_account = '%(account)s') curr
ON prev.%(dest)s_organization_id = curr.%(dest)s_organization_id
WHERE prev.created_at >= '%(prev_period_start)s'
AND prev.created_at < '%(prev_period_end)s'
AND prev.%(orig)s_organization_id = '%(organization_id)s'
AND prev.%(orig)s_account = '%(account)s'
AND curr.%(dest)s_organization_id IS NULL""" % {
"orig": orig,
"dest": dest,
"prev_period_start": prev_period_start,
"prev_period_end": prev_period_end,
"period_start": period_start,
"period_end": period_end,
"organization_id": organization.id,
"account": account
}, router.db_for_read(Transaction))
churn_customer, churn_receivable = next(iter(churn_query))
# A bit ugly but it does the job ...
if orig == 'orig':
kwargs = {
'orig_organization': organization,
'orig_account': account
}
else:
kwargs = {
'dest_organization': organization,
'dest_account': account
}
query_result = Transaction.objects.filter(
created_at__gte=period_start, created_at__lt=period_end,
**kwargs).aggregate(Count('%s_organization' % dest, distinct=True),
Sum('%s_amount' % dest))
customer = query_result['%s_organization__count' % dest]
receivable = query_result['%s_amount__sum' % dest]
new_query = RawQuery(
"""SELECT count(distinct(curr.%(dest)s_organization_id)),
SUM(curr.%(dest)s_amount)
FROM saas_transaction curr
LEFT OUTER JOIN (
SELECT distinct(%(dest)s_organization_id)
FROM saas_transaction
WHERE created_at >= '%(prev_period_start)s'
AND created_at < '%(prev_period_end)s'
AND %(orig)s_organization_id = '%(organization_id)s'
AND %(orig)s_account = '%(account)s') prev
ON curr.%(dest)s_organization_id = prev.%(dest)s_organization_id
WHERE curr.created_at >= '%(period_start)s'
AND curr.created_at < '%(period_end)s'
AND curr.%(orig)s_organization_id = '%(organization_id)s'
AND curr.%(orig)s_account = '%(account)s'
AND prev.%(dest)s_organization_id IS NULL""" % {
"orig": orig,
"dest": dest,
"prev_period_start": prev_period_start,
"prev_period_end": prev_period_end,
"period_start": period_start,
"period_end": period_end,
"organization_id": organization.id,
"account": account
}, router.db_for_read(Transaction))
new_customer, new_receivable = next(iter(new_query))
period = period_end
churn_customers += [(period, churn_customer)]
churn_receivables += [(period, int(churn_receivable or 0))]
customers += [(period, customer)]
receivables += [(period, int(receivable or 0))]
new_customers += [(period, new_customer)]
new_receivables += [(period, int(new_receivable or 0))]
period_start = period_end
return ((churn_customers, customers, new_customers),
(churn_receivables, receivables, new_receivables))