from django.conf import settings
from django.db import transaction
from django.utils.translation import ugettext as _
from django.utils.timezone import now as timezone_now
from django.core.signing import Signer
import stripe
from zerver.lib.logging_util import log_to_file
from zerver.lib.timestamp import datetime_to_timestamp, timestamp_to_datetime
from zerver.lib.utils import generate_random_token
from zerver.models import Realm, UserProfile, RealmAuditLog
from corporate.models import Customer, CustomerPlan, LicenseLedger, \
get_active_plan
from zproject.settings import get_secret
STRIPE_PUBLISHABLE_KEY = get_secret('stripe_publishable_key')
stripe.api_key = get_secret('stripe_secret_key')
BILLING_LOG_PATH = os.path.join('/var/log/zulip'
if not settings.DEVELOPMENT
else settings.DEVELOPMENT_LOG_DIRECTORY,
'billing.log')
billing_logger = logging.getLogger('corporate.stripe')
log_to_file(billing_logger, BILLING_LOG_PATH)
log_to_file(logging.getLogger('stripe'), BILLING_LOG_PATH)
CallableT = TypeVar('CallableT', bound=Callable[..., Any])
MIN_INVOICED_LICENSES = 30
DEFAULT_INVOICE_DAYS_UNTIL_DUE = 30
from zerver.lib.management import ZulipBaseCommand
from zilencer.models import Plan
from zproject.settings import get_secret
from typing import Any
import stripe
stripe.api_key = get_secret('stripe_secret_key')
class Command(ZulipBaseCommand):
help = """Script to add the appropriate products and plans to Stripe."""
def handle(self, *args: Any, **options: Any) -> None:
Plan.objects.all().delete()
# Zulip Cloud offerings
product = stripe.Product.create(
name="Zulip Cloud Premium",
type='service',
statement_descriptor="Zulip Cloud Premium",
unit_label="user")
plan = stripe.Plan.create(currency='usd',
interval='month',
product=product.id,
amount=800,
billing_scheme='per_unit',
nickname=Plan.CLOUD_MONTHLY,
usage_type='licensed')
Plan.objects.create(nickname=Plan.CLOUD_MONTHLY,
from django.conf import settings
from django.db import transaction
from django.utils.translation import ugettext as _
from django.core.signing import Signer
import stripe
from zerver.lib.exceptions import JsonableError
from zerver.lib.logging_util import log_to_file
from zerver.lib.timestamp import datetime_to_timestamp, timestamp_to_datetime
from zerver.lib.utils import generate_random_token
from zerver.models import Realm, UserProfile, RealmAuditLog
from zilencer.models import Customer, Plan
from zproject.settings import get_secret
STRIPE_PUBLISHABLE_KEY = get_secret('stripe_publishable_key')
stripe.api_key = get_secret('stripe_secret_key')
BILLING_LOG_PATH = os.path.join(
'/var/log/zulip' if not settings.DEVELOPMENT else
settings.DEVELOPMENT_LOG_DIRECTORY, 'billing.log')
billing_logger = logging.getLogger('zilencer.stripe')
log_to_file(billing_logger, BILLING_LOG_PATH)
log_to_file(logging.getLogger('stripe'), BILLING_LOG_PATH)
# To generate the fixture data in stripe_fixtures.json:
# * Set PRINT_STRIPE_FIXTURE_DATA to True
# * ./manage.py setup_stripe
# * Customer.objects.all().delete()
# * Log in as a user, and go to http://localhost:9991/upgrade/
# * Click Add card. Enter the following billing details:
AuthenticationError, APIConnectionError, StripeError
from zerver.decorator import require_post, zulip_login_required
from zerver.lib.exceptions import JsonableError
from zerver.lib.logging_util import log_to_file
from zerver.lib.push_notifications import send_android_push_notification, \
send_apple_push_notification
from zerver.lib.request import REQ, has_request_variables
from zerver.lib.response import json_error, json_success
from zerver.lib.validator import check_int
from zerver.models import UserProfile
from zerver.views.push_notifications import validate_token
from zilencer.models import RemotePushDeviceToken, RemoteZulipServer, Customer
from zproject.settings import get_secret
STRIPE_SECRET_KEY = get_secret('stripe_secret_key')
STRIPE_PUBLISHABLE_KEY = get_secret('stripe_publishable_key')
stripe.api_key = STRIPE_SECRET_KEY
BILLING_LOG_PATH = os.path.join(
'/var/log/zulip' if not settings.DEVELOPMENT else
settings.DEVELOPMENT_LOG_DIRECTORY, 'billing.log')
billing_logger = logging.getLogger('zilencer.stripe')
log_to_file(billing_logger, BILLING_LOG_PATH)
log_to_file(logging.getLogger('stripe'), BILLING_LOG_PATH)
def validate_entity(entity: Union[UserProfile, RemoteZulipServer]) -> None:
if not isinstance(entity, RemoteZulipServer):
raise JsonableError(_("Must validate with valid Zulip server API key"))
from zerver.lib.management import ZulipBaseCommand
from zilencer.models import Plan, Coupon, Customer
from zproject.settings import get_secret
from typing import Any
import stripe
stripe.api_key = get_secret('stripe_secret_key')
class Command(ZulipBaseCommand):
help = """Script to add the appropriate products and plans to Stripe."""
def handle(self, *args: Any, **options: Any) -> None:
Customer.objects.all().delete()
Plan.objects.all().delete()
Coupon.objects.all().delete()
# Zulip Cloud offerings
product = stripe.Product.create(
name="Zulip Cloud Premium",
type='service',
statement_descriptor="Zulip Cloud Premium",
unit_label="user")
plan = stripe.Plan.create(
currency='usd',
interval='month',
product=product.id,
amount=800,
billing_scheme='per_unit',
nickname=Plan.CLOUD_MONTHLY,
def realm_summary_table(realm_minutes: Dict[str, float]) -> str:
now = timezone_now()
query = '''
SELECT
realm.string_id,
realm.date_created,
realm.plan_type,
coalesce(user_counts.dau_count, 0) dau_count,
coalesce(wau_counts.wau_count, 0) wau_count,
(
SELECT
count(*)
FROM zerver_userprofile up
WHERE up.realm_id = realm.id
AND is_active
AND not is_bot
) user_profile_count,
(
SELECT
count(*)
FROM zerver_userprofile up
WHERE up.realm_id = realm.id
AND is_active
AND is_bot
) bot_count
FROM zerver_realm realm
LEFT OUTER JOIN
(
SELECT
up.realm_id realm_id,
count(distinct(ua.user_profile_id)) dau_count
FROM zerver_useractivity ua
JOIN zerver_userprofile up
ON up.id = ua.user_profile_id
WHERE
up.is_active
AND (not up.is_bot)
AND
query in (
'/json/send_message',
'send_message_backend',
'/api/v1/send_message',
'/json/update_pointer',
'/json/users/me/pointer',
'update_pointer_backend'
)
AND
last_visit > now() - interval '1 day'
GROUP BY realm_id
) user_counts
ON user_counts.realm_id = realm.id
LEFT OUTER JOIN
(
SELECT
realm_id,
count(*) wau_count
FROM (
SELECT
realm.id as realm_id,
up.email
FROM zerver_useractivity ua
JOIN zerver_userprofile up
ON up.id = ua.user_profile_id
JOIN zerver_realm realm
ON realm.id = up.realm_id
WHERE up.is_active
AND (not up.is_bot)
AND
ua.query in (
'/json/send_message',
'send_message_backend',
'/api/v1/send_message',
'/json/update_pointer',
'/json/users/me/pointer',
'update_pointer_backend'
)
GROUP by realm.id, up.email
HAVING max(last_visit) > now() - interval '7 day'
) as wau_users
GROUP BY realm_id
) wau_counts
ON wau_counts.realm_id = realm.id
WHERE EXISTS (
SELECT *
FROM zerver_useractivity ua
JOIN zerver_userprofile up
ON up.id = ua.user_profile_id
WHERE
up.realm_id = realm.id
AND up.is_active
AND (not up.is_bot)
AND
query in (
'/json/send_message',
'/api/v1/send_message',
'send_message_backend',
'/json/update_pointer',
'/json/users/me/pointer',
'update_pointer_backend'
)
AND
last_visit > now() - interval '2 week'
)
ORDER BY dau_count DESC, string_id ASC
'''
cursor = connection.cursor()
cursor.execute(query)
rows = dictfetchall(cursor)
cursor.close()
# Fetch all the realm administrator users
realm_admins = defaultdict(list) # type: Dict[str, List[str]]
for up in UserProfile.objects.select_related("realm").filter(
is_realm_admin=True,
is_active=True
):
realm_admins[up.realm.string_id].append(up.email)
for row in rows:
row['date_created_day'] = row['date_created'].strftime('%Y-%m-%d')
row['plan_type_string'] = [
'', 'self hosted', 'limited', 'standard', 'open source'][row['plan_type']]
row['age_days'] = int((now - row['date_created']).total_seconds()
/ 86400)
row['is_new'] = row['age_days'] < 12 * 7
row['realm_admin_email'] = ', '.join(realm_admins[row['string_id']])
# get messages sent per day
counts = get_realm_day_counts()
for row in rows:
try:
row['history'] = counts[row['string_id']]['cnts']
except Exception:
row['history'] = ''
# estimate annual subscription revenue
total_amount = 0
if settings.BILLING_ENABLED:
from corporate.lib.stripe import estimate_customer_arr
from corporate.models import Customer
stripe.api_key = get_secret('stripe_secret_key')
estimated_arr = {}
try:
for stripe_customer in stripe.Customer.list(limit=100):
# TODO: could do a select_related to get the realm.string_id, potentially
customer = Customer.objects.filter(stripe_customer_id=stripe_customer.id).first()
if customer is not None:
estimated_arr[customer.realm.string_id] = estimate_customer_arr(stripe_customer)
except stripe.error.StripeError:
pass
for row in rows:
row['amount'] = estimated_arr.get(row['string_id'], None)
total_amount = sum(estimated_arr.values())
# augment data with realm_minutes
total_hours = 0.0
for row in rows:
string_id = row['string_id']
minutes = realm_minutes.get(string_id, 0.0)
hours = minutes / 60.0
total_hours += hours
row['hours'] = str(int(hours))
try:
row['hours_per_user'] = '******' % (hours / row['dau_count'],)
except Exception:
pass
# formatting
for row in rows:
row['stats_link'] = realm_stats_link(row['string_id'])
row['string_id'] = realm_activity_link(row['string_id'])
# Count active sites
def meets_goal(row: Dict[str, int]) -> bool:
return row['dau_count'] >= 5
num_active_sites = len(list(filter(meets_goal, rows)))
# create totals
total_dau_count = 0
total_user_profile_count = 0
total_bot_count = 0
total_wau_count = 0
for row in rows:
total_dau_count += int(row['dau_count'])
total_user_profile_count += int(row['user_profile_count'])
total_bot_count += int(row['bot_count'])
total_wau_count += int(row['wau_count'])
total_row = dict(
string_id='Total',
plan_type_string="",
amount=total_amount,
stats_link = '',
date_created_day='',
realm_admin_email='',
dau_count=total_dau_count,
user_profile_count=total_user_profile_count,
bot_count=total_bot_count,
hours=int(total_hours),
wau_count=total_wau_count,
)
rows.insert(0, total_row)
content = loader.render_to_string(
'analytics/realm_summary_table.html',
dict(rows=rows, num_active_sites=num_active_sites,
now=now.strftime('%Y-%m-%dT%H:%M:%SZ'))
)
return content
from functools import wraps
import logging
import os
from typing import Any, Callable, TypeVar
from django.conf import settings
from django.utils.translation import ugettext as _
import stripe
from zerver.lib.exceptions import JsonableError
from zerver.lib.logging_util import log_to_file
from zerver.models import Realm, UserProfile
from zilencer.models import Customer
from zproject.settings import get_secret
STRIPE_SECRET_KEY = get_secret('stripe_secret_key')
STRIPE_PUBLISHABLE_KEY = get_secret('stripe_publishable_key')
stripe.api_key = STRIPE_SECRET_KEY
BILLING_LOG_PATH = os.path.join('/var/log/zulip'
if not settings.DEVELOPMENT
else settings.DEVELOPMENT_LOG_DIRECTORY,
'billing.log')
billing_logger = logging.getLogger('zilencer.stripe')
log_to_file(billing_logger, BILLING_LOG_PATH)
log_to_file(logging.getLogger('stripe'), BILLING_LOG_PATH)
CallableT = TypeVar('CallableT', bound=Callable[..., Any])
class StripeError(JsonableError):
pass
def realm_summary_table(realm_minutes: Dict[str, float]) -> str:
now = timezone_now()
query = '''
SELECT
realm.string_id,
realm.date_created,
realm.plan_type,
coalesce(user_counts.dau_count, 0) dau_count,
coalesce(wau_counts.wau_count, 0) wau_count,
(
SELECT
count(*)
FROM zerver_userprofile up
WHERE up.realm_id = realm.id
AND is_active
AND not is_bot
) user_profile_count,
(
SELECT
count(*)
FROM zerver_userprofile up
WHERE up.realm_id = realm.id
AND is_active
AND is_bot
) bot_count
FROM zerver_realm realm
LEFT OUTER JOIN
(
SELECT
up.realm_id realm_id,
count(distinct(ua.user_profile_id)) dau_count
FROM zerver_useractivity ua
JOIN zerver_userprofile up
ON up.id = ua.user_profile_id
WHERE
up.is_active
AND (not up.is_bot)
AND
query in (
'/json/send_message',
'send_message_backend',
'/api/v1/send_message',
'/json/update_pointer',
'/json/users/me/pointer',
'update_pointer_backend'
)
AND
last_visit > now() - interval '1 day'
GROUP BY realm_id
) user_counts
ON user_counts.realm_id = realm.id
LEFT OUTER JOIN
(
SELECT
realm_id,
count(*) wau_count
FROM (
SELECT
realm.id as realm_id,
up.email
FROM zerver_useractivity ua
JOIN zerver_userprofile up
ON up.id = ua.user_profile_id
JOIN zerver_realm realm
ON realm.id = up.realm_id
WHERE up.is_active
AND (not up.is_bot)
AND
ua.query in (
'/json/send_message',
'send_message_backend',
'/api/v1/send_message',
'/json/update_pointer',
'/json/users/me/pointer',
'update_pointer_backend'
)
GROUP by realm.id, up.email
HAVING max(last_visit) > now() - interval '7 day'
) as wau_users
GROUP BY realm_id
) wau_counts
ON wau_counts.realm_id = realm.id
WHERE EXISTS (
SELECT *
FROM zerver_useractivity ua
JOIN zerver_userprofile up
ON up.id = ua.user_profile_id
WHERE
up.realm_id = realm.id
AND up.is_active
AND (not up.is_bot)
AND
query in (
'/json/send_message',
'/api/v1/send_message',
'send_message_backend',
'/json/update_pointer',
'/json/users/me/pointer',
'update_pointer_backend'
)
AND
last_visit > now() - interval '2 week'
)
ORDER BY dau_count DESC, string_id ASC
'''
cursor = connection.cursor()
cursor.execute(query)
rows = dictfetchall(cursor)
cursor.close()
# Fetch all the realm administrator users
realm_admins = defaultdict(list) # type: Dict[str, List[str]]
for up in UserProfile.objects.select_related("realm").filter(
is_realm_admin=True, is_active=True):
realm_admins[up.realm.string_id].append(up.email)
for row in rows:
row['date_created_day'] = row['date_created'].strftime('%Y-%m-%d')
row['plan_type_string'] = [
'', 'self hosted', 'limited', 'standard', 'open source'
][row['plan_type']]
row['age_days'] = int(
(now - row['date_created']).total_seconds() / 86400)
row['is_new'] = row['age_days'] < 12 * 7
row['realm_admin_email'] = ', '.join(realm_admins[row['string_id']])
# get messages sent per day
counts = get_realm_day_counts()
for row in rows:
try:
row['history'] = counts[row['string_id']]['cnts']
except Exception:
row['history'] = ''
# estimate annual subscription revenue
total_amount = 0
if settings.BILLING_ENABLED:
from corporate.lib.stripe import estimate_customer_arr
from corporate.models import Customer
stripe.api_key = get_secret('stripe_secret_key')
estimated_arr = {}
try:
for stripe_customer in stripe.Customer.list(limit=100):
# TODO: could do a select_related to get the realm.string_id, potentially
customer = Customer.objects.filter(
stripe_customer_id=stripe_customer.id).first()
if customer is not None:
estimated_arr[
customer.realm.string_id] = estimate_customer_arr(
stripe_customer)
except stripe.error.StripeError:
pass
for row in rows:
row['amount'] = estimated_arr.get(row['string_id'], None)
total_amount = sum(estimated_arr.values())
# augment data with realm_minutes
total_hours = 0.0
for row in rows:
string_id = row['string_id']
minutes = realm_minutes.get(string_id, 0.0)
hours = minutes / 60.0
total_hours += hours
row['hours'] = str(int(hours))
try:
row['hours_per_user'] = '******' % (hours / row['dau_count'], )
except Exception:
pass
# formatting
for row in rows:
row['stats_link'] = realm_stats_link(row['string_id'])
row['string_id'] = realm_activity_link(row['string_id'])
# Count active sites
def meets_goal(row: Dict[str, int]) -> bool:
return row['dau_count'] >= 5
num_active_sites = len(list(filter(meets_goal, rows)))
# create totals
total_dau_count = 0
total_user_profile_count = 0
total_bot_count = 0
total_wau_count = 0
for row in rows:
total_dau_count += int(row['dau_count'])
total_user_profile_count += int(row['user_profile_count'])
total_bot_count += int(row['bot_count'])
total_wau_count += int(row['wau_count'])
total_row = dict(
string_id='Total',
plan_type_string="",
amount=total_amount,
stats_link='',
date_created_day='',
realm_admin_email='',
dau_count=total_dau_count,
user_profile_count=total_user_profile_count,
bot_count=total_bot_count,
hours=int(total_hours),
wau_count=total_wau_count,
)
rows.insert(0, total_row)
content = loader.render_to_string(
'analytics/realm_summary_table.html',
dict(rows=rows,
num_active_sites=num_active_sites,
now=now.strftime('%Y-%m-%dT%H:%M:%SZ')))
return content
