# By: Riasat Ullah # This file contains billing related queries. from billings.bill import Bill, Charge from utils import constants, errors, helpers, roles, times, var_names from validations import string_validator import configuration import datetime import json import psycopg2 def get_billable_accounts(conn, billing_start, billing_end): ''' Get all the accounts that are due for billing in a given period of time. :param conn: db connection :param billing_start: the date the billing should start on :param billing_end: the date the billing should end on :return: (dict of list of dict) -> {org_id: [{...}, {...}], org_id_2: ...} :errors: AssertionError, DatabaseError ''' assert isinstance(billing_start, datetime.date) assert isinstance(billing_end, datetime.date) # Increasing the billing end by one day so the start date and end date logic can pull the last day's data as well billing_end = billing_end + datetime.timedelta(days=1) query = ''' with org_sub as ( select organization_id, start_timestamp, end_timestamp, case when start_timestamp < %(bill_start)s then %(bill_start)s else start_timestamp end as month_start, case when end_timestamp > %(bill_end)s then %(bill_end)s else end_timestamp end as month_end, subscription_id, is_trial from organization_subscriptions where start_timestamp < %(bill_end)s and end_timestamp >= %(bill_start)s ) , t1 as ( select organization_id, json_agg(json_build_object( 'subscription_id', subscription_id, 'subscription_type', subscription_type, 'description', subscription_description, 'subscription_fee', fee, 'start_period', month_start, 'end_period', month_end, 'is_trial', is_trial, 'count', count )) as sub_plans from ( select org_sub.organization_id, org_sub.subscription_id, sub.subscription_type, sub.subscription_description, sub.fee, org_sub.month_start, org_sub.month_end, org_sub.is_trial, count(distinct(org_mem.member_id)) filter (where not role_id = any(%(stkdr_rol_ids)s)) as count from org_sub join subscriptions as sub on sub.subscription_id = org_sub.subscription_id and sub.start_date <= org_sub.month_start and sub.end_date > org_sub.month_start join organization_members as org_mem on org_mem.organization_id = org_sub.organization_id and org_mem.start_timestamp < org_sub.month_end and org_mem.end_timestamp >= org_sub.month_start where org_sub.start_timestamp < org_sub.month_end and org_sub.end_timestamp >= org_sub.month_start group by org_sub.organization_id, org_sub.subscription_id, sub.subscription_type, sub.subscription_description, sub.fee, org_sub.month_start, org_sub.month_end, org_sub.is_trial order by org_sub.organization_id, org_sub.month_start ) as sub_table group by organization_id ) , t2 as ( select org_mem.organization_id, count( distinct(org_mem.member_id)) filter (where role_id = any(%(stkdr_rol_ids)s) ) as stakeholder_count from org_sub join subscriptions as sub on sub.subscription_id = org_sub.subscription_id and sub.start_date <= org_sub.month_start and sub.end_date > org_sub.month_start join organization_members as org_mem on org_mem.organization_id = org_sub.organization_id and org_mem.start_timestamp < %(bill_end)s and org_mem.end_timestamp >= %(bill_start)s where org_sub.start_timestamp < org_sub.month_end and org_sub.end_timestamp >= org_sub.month_start and not org_sub.is_trial and sub.subscription_type = %(base_sub_type)s group by org_mem.organization_id ), t3 as ( select organization_id, json_agg(json_build_object( 'iso_country_code', phone_iso_code, 'phone_type', phone_number_type, 'count', count )) as lcr_count from ( select organization_id, phone_iso_code, phone_number_type, count(distinct(phone_number)) as count from live_call_routing lcr where start_timestamp < %(bill_end)s and end_timestamp >= %(bill_start)s group by organization_id, phone_iso_code, phone_number_type ) as lcr_sub_table group by organization_id ) select t1.organization_id, t1.sub_plans, t2.stakeholder_count, t3.lcr_count from t1 left join t2 using(organization_id) left join t3 using(organization_id) order by organization_id; ''' query_params = {'bill_start': billing_start, 'bill_end': billing_end, 'stkdr_rol_ids': roles.get_stakeholder_user_role_ids(), 'base_sub_type': constants.base_subscription_type} try: result = conn.fetch(query, query_params) accounts = dict() for org_id, sub_plans, stkdr_count, lcr_count in result: for item in sub_plans: item[var_names.start_period] = times.get_timestamp_from_string(item[var_names.start_period]).date() item[var_names.end_period] = times.get_timestamp_from_string(item[var_names.end_period]).date() accounts[org_id] = { var_names.subscriptions: sub_plans, var_names.stakeholder_count: stkdr_count, var_names.live_call_routing_count: lcr_count } return accounts except psycopg2.DatabaseError: raise def get_billable_cards(conn, billing_start, billing_end, organization_ids): ''' Get all the cards that are billable within a period of time. This will also pull cards for organizations that have closed their account. :param conn: db connection :param billing_start: the date the billing should start on :param billing_end: the date the billing should end on :param organization_ids: (int or list) of organization ids to check for :return: ''' assert isinstance(billing_start, datetime.date) assert isinstance(billing_end, datetime.date) # Increasing the billing end by one day so the start date and end date logic can pull the last day's data as well billing_end = billing_end + datetime.timedelta(days=1) query = ''' with t1 as( select organization_id, max(end_timestamp) as max_tmsp from organization_cards where start_timestamp <= %(bill_end)s and end_timestamp > %(bill_start)s and default_card = true group by organization_id ) select oc.organization_id, card_id from organization_cards as oc join t1 on oc.organization_id = t1.organization_id and oc.end_timestamp = t1.max_tmsp where start_timestamp < %(bill_end)s and end_timestamp >= %(bill_start)s and default_card = true and oc.organization_id = any(%(org_ids)s); ''' query_params = {'bill_start': billing_start, 'bill_end': billing_end, 'org_ids': helpers.get_int_list(organization_ids)} try: result = conn.fetch(query, query_params) data = dict() for org_id, card_id in result: data[org_id] = card_id return data except psycopg2.DatabaseError: raise def get_basic_billing_info(conn, billing_start, billing_end, organization_ids=None): ''' Get the basic billing info [iso code, currency] associated to an organization on the last active day of the organization in a given billing period. :param conn: db connection :param billing_start: the date the billing should start on :param billing_end: the date the billing should end on :param organization_ids: (list) of organization ids to look for :return: (dict of dict) -> { org_id: {iso_country_code: .., billing_currency: ..}, ...} ''' assert isinstance(billing_start, datetime.date) assert isinstance(billing_end, datetime.date) # Increasing the billing end by one day so the start date and end date logic can pull the last day's data as well billing_end = billing_end + datetime.timedelta(days=1) query_params = {'bill_start': billing_start, 'bill_end': billing_end} org_cond = '' if organization_ids is not None: org_cond = ' and organization_id = any(%(org_id)s) ' query_params['org_id'] = helpers.get_int_list(organization_ids) query = ''' with t1 as ( select organization_id, max(start_timestamp) as max_start from organizations where start_timestamp < %(bill_end)s and end_timestamp >= %(bill_start)s {0} group by organization_id ) select org_det.organization_id, iso_country_code, billing_currency from organizations as org_det join t1 on org_det.organization_id = t1.organization_id and org_det.start_timestamp = t1.max_start where org_det.start_timestamp <= %(bill_end)s and org_det.end_timestamp > %(bill_start)s; '''.format(org_cond) try: result = conn.fetch(query, query_params) data = dict() for org_id, iso_code, curr in result: data[org_id] = { var_names.iso_country_code: iso_code, var_names.billing_currency: curr } return data except psycopg2.DatabaseError: raise def get_optional_billing_info(conn, timestamp, organization_id): ''' Get the optional billing info. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization to fetch the details for :return: (dict of dict) -> {vat_id: , additional_emails: , billing_address: {...}} ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' select vat_id, invoice_emails, address, city, state, zip_code, iso_country_code from organization_billing_info where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id} try: result = conn.fetch(query, query_params) data = dict() for vat_id, inv_emails, address, city, state, zip_code, iso_code in result: if address is None: billing_address = None else: billing_address = { var_names.address: address, var_names.city: city, var_names.state: state, var_names.zip_code: zip_code, var_names.country: iso_code } data = { var_names.vat_id: vat_id, var_names.additional_emails: inv_emails, var_names.billing_address: billing_address } return data except psycopg2.DatabaseError: raise def update_optional_billing_info(conn, timestamp, organization_id, vat_id, additional_emails, billing_address): ''' Update the optional billing info. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization to fetch the details for :param vat_id: VAT ID of the organization :param additional_emails: additional email addresses to send invoices to :param billing_address: billing address to show on invoices (if different) :return: (dict of dict) -> {vat_id: , additional_emails: , billing_address: {...}} ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) if additional_emails is not None: for item in additional_emails: assert string_validator.is_email_address(item) if billing_address is None: addr, city, state, zip_code, iso_code = None, None, None, None, None else: addr = billing_address[var_names.address] city = billing_address[var_names.city] state = billing_address[var_names.state] zip_code = billing_address[var_names.zip_code] iso_code = billing_address[var_names.country] query = ''' begin; update organization_billing_info set end_timestamp = %s where start_timestamp <= %s and end_timestamp > %s and organization_id = %s; insert into organization_billing_info values ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); end; ''' query_params = (timestamp, timestamp, timestamp, organization_id, organization_id, timestamp, constants.end_timestamp, vat_id, additional_emails, addr, city, state, zip_code, iso_code, None,) try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def get_billed_organization_ids(conn, billing_year, billing_month): ''' Get the list of organization IDs that have been billed on a given year and month. :param conn: db connection :param billing_year: the billing year :param billing_month: the billing month :return: (list) of organization IDs ''' assert isinstance(billing_year, int) assert isinstance(billing_month, int) and 1 <= billing_month <= 12 query = ''' select billing_organization from billings where billing_year = %s and billing_month = %s and billing_status != %s; ''' query_params = (billing_year, billing_month, constants.cancelled_state,) try: org_ids = [] result = conn.fetch(query, query_params) for item in result: org_ids.append(item[0]) return org_ids except psycopg2.DatabaseError: raise def get_discounts(conn, check_date, org_list=None, discount_type=None, map_on_org=True): ''' Get all the discounts that are applicable to organizations at a point in time. :param conn: db connection :param check_date: date when this request is being made :param org_list: list of organization ids to check with :param discount_type: type of discount :param map_on_org: (boolean) True the data should be returned as a dict keyed on organization ID :return: if map_on_org -> (dict) of dict -> {org_id: { ... }, ...} else -> (list) of dict -> [{ ... }, ...] :errors: AssertionError, DatabaseError ''' assert isinstance(check_date, datetime.date) query_params = {'time_date': check_date} conditions = [] if org_list is not None: conditions.append(' organization_id is not null and organization_id = any(%(org_id)s) ') query_params['org_id'] = helpers.get_int_list(org_list) if discount_type is not None: assert isinstance(discount_type, list) and\ set(discount_type).issubset(set(configuration.allowed_discount_types)) conditions.append(' discount_type = any(%(disc_type)s) ') query_params['disc_type'] = discount_type query = ''' select discount_id, discount_type, reason, organization_id, discount_percent, discount_amount, currency, iso_country_code, details from discounts where start_date <= %(time_date)s and end_date > %(time_date)s {0}; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) list_data = [] dict_data = dict() for disc_id_, disc_typ_, reason_, org_id_, perc_, amt_, curr_, iso_, det_ in result: item = { var_names.discount_id: disc_id_, var_names.discount_type: disc_typ_, var_names.reason: reason_, var_names.organization_id: org_id_, var_names.discount_percent: float(perc_) if perc_ is not None else None, var_names.discount: float(amt_) if amt_ is not None else None, var_names.billing_currency: curr_, var_names.iso_country_code: iso_, var_names.additional_info: det_ } if map_on_org and org_id_ is not None: dict_data[org_id_] = item else: list_data.append(item) if map_on_org: return dict_data else: return list_data except psycopg2.DatabaseError: raise def get_country_code_mappings(conn): ''' Get all the phone country code mappings to their 2 letter iso country code. :param conn: db connection :return: (dict) -> {phone_code: iso_code, ...} ''' query = 'select phone_country_code, iso_country_code from country_codes;' try: result = conn.fetch(query) data = dict() for phone_, iso_ in result: data[phone_] = iso_ return data except psycopg2.DatabaseError: raise def book_bills_only(conn, timestamp, bill_list): ''' Book db entries for a list of bills. :param conn: db connection :param timestamp: timestamp when this request is being made :param bill_list: (list) of bills :errors: AssertionError, RuntimeError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(bill_list, list) entries = [] for bill in bill_list: assert isinstance(bill, Bill) entries.append(bill.db_query_params(timestamp)) if len(entries) == 0: raise RuntimeError(errors.err_internal_billings_no_bills_to_book) else: query = ''' select create_bill( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' try: conn.execute_batch(query, entries) except psycopg2.DatabaseError: raise def get_open_charges(conn, timestamp, organization_ids=None): ''' Get all the charges that are open on a given date. :param conn: db connection :param timestamp: timestamp when the request was being made :param organization_ids: (int or list) of organization ids :return: (list) of Charge objects ''' assert isinstance(timestamp, datetime.datetime) query_params = {'charge_date': timestamp.date(), 'charge_status': constants.open_state} org_str = '' if organization_ids is not None: org_str = " and crd.organization_id = any(%(org_id)s) " query_params['org_id'] = helpers.get_int_list(organization_ids) # we are intentionally doing a left join here so that if the organization has a charge # but somehow no card is on file then we will be able to detect it here query = ''' select charge_id, charge_organization, chr.card_id, crd.handler, crd.handler_card_token, currency, amount, side from charges as chr left join organization_cards as crd using (card_id) where charge_on_date <= %(charge_date)s and charge_status = %(charge_status)s {0}; '''.format(org_str) try: result = conn.fetch(query, query_params) data = [] for charge_id, org_id, card_id, handler, card_token, currency, amount, side in result: data.append(Charge(charge_id, org_id, card_id, handler, card_token, currency, float(amount), side)) return data except psycopg2.DatabaseError: raise def book_charges(conn, timestamp, charges): ''' Book details of charges that have been processed in the database. :param conn: db connection :param timestamp: timestamp when this request was made :param charges: (list) of Charge objects :errors: AssertionError, DatabaseError, RuntimeError ''' assert isinstance(timestamp, datetime.datetime) if len(charges) > 0: entries = [] for item in charges: entries.append(item.db_query_params(timestamp)) query = " select book_payment(%s, %s, %s, %s); " try: conn.execute_batch(query, entries) except psycopg2.DatabaseError: raise else: raise RuntimeError(errors.err_internal_billings_no_charges_to_book) def get_bills_list(conn, organization_id, billing_start=None, billing_end=None): ''' Get the bills of an organization. :param conn: db connection :param organization_id: ID of the organization :param billing_start: (datetime.date) checking period start :param billing_end: (datetime.date) checking period end :return: (list of dict) -> of basic billing details ''' assert isinstance(organization_id, int) query_params = {'org_id': organization_id} conditions = [] if billing_start is not None: assert isinstance(billing_start, datetime.date) conditions.append(' billing_date >= %(bill_start)s ') query_params['bill_start'] = billing_start if billing_end is not None: assert isinstance(billing_end, datetime.date) conditions.append(' billing_date < %(bill_end)s ') query_params['bill_end'] = billing_end query = ''' select billing_id, billing_year, billing_month, billing_date, billings.currency, billing_total, paid, pmt.payment_date, oca.last_four from billings left join payments as pmt using (payment_id) left join charges on charges.charge_id = billings.charge_id and charges.charge_id = pmt.charge_id left join organization_cards as oca using (card_id) where billing_organization = %(org_id)s {0} order by billing_date desc; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) data = [] for bill_id, bill_year, bill_month, bill_date, currency, total, status, payment_date, instrument in result: data.append({ var_names.bill_id: bill_id, var_names.bill_year: bill_year, var_names.bill_month: bill_month, var_names.bill_date: bill_date, var_names.subscription_currency: currency, var_names.bill_total: float(total), var_names.is_paid: status, var_names.payment_date: payment_date, var_names.card_last_four_digits: instrument }) return data except psycopg2.DatabaseError: raise def get_bill_details(conn, organization_id=None, bill_id=None, bill_year=None, bill_month=None): ''' Get the details of a particular bill. :param conn: db connection :param organization_id: (list or int) ID of the organization(s) the bill is for :param bill_id: (list or int)ID of the bill :param bill_year: (int) year the bill is for :param bill_month: (int) month the bill is for :return: (list of dict) -> details of the bill :errors: AssertionError, DatabaseError, LookupError ''' query_params = {'owner_role_id': roles.owner_role_id} conditions = [] if organization_id is not None: organization_id = helpers.get_int_list(organization_id) conditions.append(' billing_organization = any(%(org_id)s) ') query_params['org_id'] = organization_id if bill_id is not None: bill_id = helpers.get_int_list(bill_id) conditions.append(' billing_id = any(%(bill_id)s) ') query_params['bill_id'] = bill_id if bill_year is not None: assert isinstance(bill_year, int) conditions.append(' billing_year = %(bill_year)s ') query_params['bill_year'] = bill_year if bill_month is not None: assert isinstance(bill_month, int) conditions.append(' billing_month = %(bill_month)s ') query_params['bill_month'] = bill_month query = ''' with t1 as ( select billing_organization, billing_id, billing_status, billing_year, billing_month, billing_start, billing_end, billing_date, currency, round(subtotal, 2), round(discount_amount, 2), credits_applied, round(net_subtotal, 2), vat_iso_country_code, vat_percent, round(vat_amount, 2), round(billing_total, 2) from billings {0} ) , t2 as ( select billing_id, json_agg(json_build_object( 'item_description', item_description, 'item_quantity', item_quantity, 'billing_currency', currency, 'subscription_fee', round(unit_price, 2), 'item_total', round(total, 2) )) as bill_items from billing_items where billing_id in (select billing_id from t1) group by billing_id ) , t3 as ( select billing_id, json_agg(json_build_object( 'description', description, 'billing_currency', currency, 'discount', discount_amount )) as discount_items from billing_discounts where billing_id in (select billing_id from t1) group by billing_id ) , t4 as ( select t1.billing_id, organization_id, case when max_end > t1.billing_end + interval '1 day' then billing_end + interval '1 day' else max_end - interval '1 minute' end as max_end from t1 join ( select organization_id, max(end_timestamp) as max_end from organizations where organization_id in (select billing_organization from t1) group by organization_id ) as temp_tbl on t1.billing_organization = temp_tbl.organization_id ) , t5 as ( select t4.billing_id, org.organization_id, account_id, organization_name, case when obi.address is null then org.address else obi.address end as address, case when obi.address is null then org.city else obi.city end as city, case when obi.address is null then org.state else obi.state end as state, case when obi.address is null then org.zip_code else obi.zip_code end as zip_code, case when obi.address is null then org.iso_country_code else obi.iso_country_code end as iso_country_code, obi.vat_id, users.email, obi.invoice_emails from organizations as org join t4 using(organization_id) join users on users.organization_id = org.organization_id join organization_members as om on users.user_id = om.member_id left join organization_billing_info as obi on obi.organization_id = org.organization_id and obi.start_timestamp <= t4.max_end and obi.end_timestamp > t4.max_end where org.start_timestamp <= t4.max_end and org.end_timestamp > t4.max_end and users.start_timestamp < t4.max_end and users.end_timestamp >= t4.max_end and om.start_timestamp < t4.max_end and om.end_timestamp >= t4.max_end and om.role_id = %(owner_role_id)s ) select t1.*, t2.bill_items, t3.discount_items, t5.account_id, t5.organization_name, t5.address, t5.city, t5.state, t5.zip_code, cc.country, t5.vat_id, t5.email, t5.invoice_emails from t1 join t2 using (billing_id) left join t3 using (billing_id) join t5 on t5.organization_id = t1.billing_organization and t5.billing_id = t1.billing_id join country_codes as cc using(iso_country_code) order by billing_organization, billing_year, billing_month; '''.format(' where ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) if len(result) == 0: raise LookupError(errors.err_unknown_resource) data = [] for org_id, b_id, b_status, b_year, b_month, b_start, b_end, b_date, currency, subtotal, disc_amount,\ credit_used, net_subtotal, vat_iso, vat_perc, vat_amount, b_total, b_items, disc_items, acc_id,\ org_name, address, city, state, zip_code, country, vat_id, owner_email, addn_emails in result: data.append({ var_names.bill_id: b_id, var_names.status: b_status, var_names.bill_year: b_year, var_names.bill_month: b_month, var_names.start_period: b_start, var_names.end_period: b_end, var_names.bill_date: b_date, var_names.billing_currency: currency, var_names.sub_total: float(subtotal), var_names.discount: float(disc_amount), var_names.credit_used: float(credit_used) if credit_used is not None else None, var_names.net_sub_total: float(net_subtotal), var_names.vat_iso_country_code: vat_iso, var_names.vat_percent: float(vat_perc), var_names.vat: float(vat_amount), var_names.bill_total: float(b_total), var_names.bill_items: b_items, var_names.discount_items: disc_items, var_names.account_id: acc_id, var_names.organization_name: org_name, var_names.address: address, var_names.city: city, var_names.state: state, var_names.zip_code: zip_code, var_names.country_name: country, var_names.vat_id: vat_id, var_names.email: owner_email, var_names.additional_emails: addn_emails }) return data except psycopg2.DatabaseError: raise def get_exchange_rates(conn, rate_year, rate_month): ''' Get all the exchange rates that are available for a given year and month. :param conn: db connection :param rate_year: year to get the rate for :param rate_month: month to get the rate for :return: (dict) -> { (from curr, to curr): rate, ... } ''' assert isinstance(rate_year, int) assert isinstance(rate_month, int) query = ''' with t1 as( select from_curr, to_curr, rate, (rate_year * 365) + (rate_month * 30) as md from exchange_rates where rate_year <= %s ) select from_curr, to_curr, rate from t1 where md in (select max(md) from t1); ''' query_params = (rate_year,) try: result = conn.fetch(query, query_params) rates = dict() for from_curr, to_curr, ex_rate in result: rates[(from_curr, to_curr)] = float(ex_rate) return rates except psycopg2.DatabaseError: raise def get_vat_rates(conn, timestamp): ''' Get the current VAT rates across countries. :param conn: db connection :param timestamp: timestamp this request is being made on :return: (dict) -> { iso_code: VAT rate, ...} ''' assert isinstance(timestamp, datetime.datetime) query = ''' select iso_country_code, vat_percent from vat_rates where start_date <= %(time_date)s and end_date > %(time_date)s; ''' query_params = {'time_date': timestamp.date()} try: result = conn.fetch(query, query_params) data = dict() for iso_code, vat_percent in result: data[iso_code] = float(vat_percent) return data except psycopg2.DatabaseError: raise def get_credit_types(conn, timestamp, credit_type=None): ''' Get available credit types. :param conn: db connection :param timestamp: timestamp when the request is being made :param credit_type: type of credit to filter by :return: (list of dict) of credit type details :error: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) query_params = {'timestamp': timestamp} conditions = [] if credit_type is not None: conditions.append(" credit_type = %(crd_typ)s ") query_params['credit_type'] = credit_type query = ''' select credit_type, credit_name, credit_currency, credit_amount, valid_days, max_global_redemption, max_org_redemption, coupon_length, host_region, subscription_id, sales_channel_id, associate_sales_channel, validity_after_registration from credit_types where start_date <= %(timestamp)s and end_date > %(timestamp)s; ''' try: result = conn.fetch(query, query_params) data = dict() for crd_type, crd_name, crd_curr, crd_amount, validity, max_global_rdm, max_org_rdm, coup_length, host_reg,\ sub_id, sc_id, assoc_sc, valid_after_reg in result: data[crd_type] = { var_names.credit_name: crd_name, var_names.credit_currency: crd_curr, var_names.credit_amount: crd_amount, var_names.valid_for: validity, var_names.max_global_redemption: max_global_rdm, var_names.max_organization_redemption: max_org_rdm, var_names.coupon_length: coup_length, var_names.host_region: host_reg, var_names.subscription_id: sub_id, var_names.sales_channel_id: sc_id, var_names.association: assoc_sc, var_names.validity_after_registration: valid_after_reg } return data except psycopg2.DatabaseError: raise def add_credit(conn, timestamp, valid_for, organization_id, credit_type, currency, credit_amount, coupon, details): ''' Add credits to an organization. :param conn: db connection :param timestamp: timestamp when the coupon is being added. This will be the valid start. :param valid_for: (int) number of days the coupon will be valid for. This will be used to calculate the valid end. :param organization_id: ID of the organization the coupon is for :param credit_type: the type of credit that is being added :param currency: currency the credit is in :param credit_amount: the amount of credit that will be applied :param coupon: the coupon code for the credit :param details: details of the coupon :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(valid_for, int) assert isinstance(organization_id, int) assert currency in configuration.allowed_credit_currencies assert isinstance(credit_amount, float) assert isinstance(coupon, str) assert isinstance(details, dict) end_time = timestamp + datetime.timedelta(days=valid_for) query = ''' begin; insert into organization_credits (organization_id, valid_start, valid_end, credit_type, credit_currency, credit_amount, credit_used, coupon, details) values ( %s, %s, %s, %s, %s, %s, 0, %s, %s ); end; ''' query_params = (organization_id, timestamp, end_time, credit_type, currency, credit_amount, coupon, json.dumps(details),) try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def get_organization_credits(conn, timestamp, organization_id, active=None, for_display=False, split_by_org=False): ''' Get the all the credits applied to an organization. Active, inactive or all historical credits can be retrieved with this query. The same query can be used for both internal and external usage. :param conn: db connection :param timestamp: timestamp when this request was made :param organization_id: the organization ID :param active: True if only active coupons should be retrieved; False if inactive; None if all :param for_display: True if the coupon data should be prepared for external display :param split_by_org: True if the credits should be split by organization_id (needed for billings) :return: (list of dict) if not map org; else (dict of list of dict) :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) or isinstance(organization_id, list) if isinstance(organization_id, int): organization_id = [organization_id] query_params = {'timestamp': timestamp, 'org_id': organization_id} conditions = [] if active is not None: if active: conditions.append(" valid_end > %(timestamp)s ") else: conditions.append(" valid_end <= %(timestamp)s ") query = ''' select oc.credit_id, organization_id, valid_start, valid_end, oc.credit_type, ct.credit_name, oc.credit_currency, oc.credit_amount, oc.credit_used, oc.details, ct.max_global_redemption, ct.max_org_redemption from organization_credits as oc join credit_types as ct on ct.credit_type = oc.credit_type and ct.start_date <= oc.valid_start and ct.end_date > oc.valid_start where organization_id = any(%(org_id)s) and valid_start <= %(timestamp)s {0} order by credit_id; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) if split_by_org: data = dict() for cr_id_, oid_, start_, end_, type_, name_, curr_, amount_, used_, details_,\ global_rdm_, org_rdm_ in result: if oid_ not in data: data[oid_] = [] data[oid_].append({ var_names.credit_id: cr_id_, var_names.valid_start: start_, var_names.valid_end: end_, var_names.credit_type: type_, var_names.credit_name: name_, var_names.credit_currency: curr_, var_names.credit_amount: float(amount_), var_names.credit_used: float(used_), var_names.data: details_, var_names.max_global_redemption: global_rdm_, var_names.max_organization_redemption: org_rdm_ }) else: data = [] for cr_id_, oid_, start_, end_, type_, name_, curr_, amount_, used_, details_,\ global_rdm_, org_rdm_ in result: if for_display: data.append({ var_names.valid_start: start_.date(), var_names.valid_end: end_.date(), var_names.credit_name: name_, var_names.credit_currency: curr_, var_names.credit_amount: float(amount_), var_names.credit_used: float(used_), }) else: data.append({ var_names.credit_id: cr_id_, var_names.valid_start: start_, var_names.valid_end: end_, var_names.credit_type: type_, var_names.credit_name: name_, var_names.credit_currency: curr_, var_names.credit_amount: float(amount_), var_names.credit_used: float(used_), var_names.data: details_, var_names.max_global_redemption: global_rdm_, var_names.max_organization_redemption: org_rdm_ }) return data except psycopg2.DatabaseError: raise def get_organization_redemption_info(conn, timestamp, org_id): ''' Get the basic information needed to validate an organization's coupon redemption request. :param conn: db connection :param timestamp: timestamp when this request is being made :param org_id: ID of the organization :return: (dict) of info :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) query = ''' with t1 as ( select og.organization_id, account_id, og_sub.subscription_id, users.email from organizations as og join organization_subscriptions as og_sub using (organization_id) join users using (organization_id) where og.organization_id = %(org_id)s and og.start_timestamp <= %(timestamp)s and og.end_timestamp > %(timestamp)s and og_sub.start_timestamp <= %(timestamp)s and og_sub.end_timestamp > %(timestamp)s and users.start_timestamp <= %(timestamp)s and users.end_timestamp > %(timestamp)s and users.user_id in ( select member_id from organization_members where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s and role_id = %(owner_rol)s ) ) , t2 as ( select organization_id, min(start_timestamp) as reg_timestamp from organization_subscriptions where organization_id = %(org_id)s group by organization_id ) select t1.account_id, t1.subscription_id, t1.email, t2.reg_timestamp from t1 join t2 using(organization_id); ''' query_params = {'timestamp': timestamp, 'org_id': org_id, 'owner_rol': roles.owner_role_id} try: result = conn.fetch(query, query_params) if len(result) == 0: raise LookupError(errors.err_unknown_resource) else: data = { var_names.account_id: result[0][0], var_names.subscription_id: result[0][1], var_names.email: result[0][2], var_names.registration_date: result[0][3] } return data except psycopg2.DatabaseError: raise def get_tax_exempted_organizations(conn, timestamp): ''' Get the list of all organizations that are tax exempted. :param conn: db connection :param timestamp: timestamp when this request is being made :return: (list) organization IDs :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) query = ''' select organization_id from tax_exempted_organizations where start_date <= %(dt)s and end_date > %(dt)s; ''' query_params = {'dt': timestamp.date()} try: result = conn.fetch(query, query_params) data = [] for item in result: data.append(item[0]) return data except psycopg2.DatabaseError: raise def get_external_communication_details(conn, billing_start, billing_end, organization_ids=None): ''' Get the number of external communication that has been made by organizations within a billing period. :param conn: db connection :param billing_start: the date the billing should start on :param billing_end: the date the billing should end on :param organization_ids: (list) of organization ids to look for :return: (dict of dict) -> { org_id: {'SEND EXTERNAL EMAIL': ..., 'SEND EXTERNAL SMS': {ISO code 1: , ..} .. } ..} ''' assert isinstance(billing_start, datetime.date) assert isinstance(billing_end, datetime.date) # Increasing the billing end by one day so the start date and end date logic can pull the last day's data as well billing_end = billing_end + datetime.timedelta(days=1) query_params = {'bill_start': billing_start, 'bill_end': billing_end, 'evn_types': [constants.send_external_email_event, constants.send_external_sms_event]} org_cond = '' if organization_ids is not None: org_cond = ' and inst.organization_id = any(%(org_id)s) ' query_params['org_id'] = helpers.get_int_list(organization_ids) query = ''' select inst.organization_id, ie.event_type, ie.event_log from instance_events as ie join task_instances as inst using(instanceid) where ie.event_timestamp <= %(bill_end)s and ie.event_timestamp > %(bill_start)s and ie.event_type = any(%(evn_types)s) {0}; '''.format(org_cond) try: result = conn.fetch(query, query_params) data = dict() for org_id, evn_typ, evn_log in result: if org_id not in data: data[org_id] = { constants.send_external_email_event: 0, constants.send_external_sms_event: dict() } if evn_typ == constants.send_external_email_event: data[org_id][constants.send_external_email_event] += len(evn_log[var_names.email_to]) elif evn_typ == constants.send_external_sms_event: for iso_code in evn_log[var_names.sms_to]: if iso_code not in data[org_id][constants.send_external_sms_event]: data[org_id][constants.send_external_sms_event][iso_code] = 0 data[org_id][constants.send_external_sms_event][iso_code] +=\ len(evn_log[var_names.sms_to][iso_code]) return data except psycopg2.DatabaseError: raise def get_monitor_checks_run(conn, billing_start, billing_end, organization_ids=None): ''' Get the number of monitoring checks that have been run during the billing period. :param conn: db connection :param billing_start: the date the billing should start on :param billing_end: the date the billing should end on :param organization_ids: (list) of organization ids to look for :return: (dict of dict) -> { org_id: count, ...} ''' assert isinstance(billing_start, datetime.date) assert isinstance(billing_end, datetime.date) # Increasing the billing end by one day so the start date and end date logic can pull the last day's data as well billing_end = billing_end + datetime.timedelta(days=1) query_params = {'bill_start': billing_start, 'bill_end': billing_end} org_cond = '' if organization_ids is not None: org_cond = ' and chk.organization_id = any(%(org_id)s) ' query_params['org_id'] = helpers.get_int_list(organization_ids) query = ''' select chk.organization_id, count(mcl.log_id) from monitor_checks as chk join monitor_check_logs as mcl on chk.check_id = mcl.check_id and chk.start_timestamp <= mcl.run_timestamp and chk.end_timestamp > mcl.run_timestamp where mcl.run_timestamp <= %(bill_end)s and mcl.run_timestamp > %(bill_start)s {0} group by chk.organization_id; '''.format(org_cond) try: result = conn.fetch(query, query_params) data = dict() for org_id, evn_count in result: data[org_id] = evn_count return data except psycopg2.DatabaseError: raise