def add_moz_zero_charge(apps):
mzn, _ = (apps.get_model('accounting', 'Currency')
if apps else Currency).objects.get_or_create(code='MZN')
sms_gateway_fee_class = apps.get_model(
'smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model(
'smsbillables',
'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('0'),
country_code=None,
prefix='',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('0'),
country_code=None,
prefix='',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Moz gateway default fees.")
def bootstrap_smsgh_gateway(apps=None):
default_currency, _ = (apps.get_model("accounting", "Currency") if apps else Currency).objects.get_or_create(
code=settings.DEFAULT_CURRENCY
)
sms_gateway_fee_class = apps.get_model("smsbillables", "SmsGatewayFee") if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = (
apps.get_model("smsbillables", "SmsGatewayFeeCriteria") if apps else SmsGatewayFeeCriteria
)
SmsGatewayFee.create_new(
SQLSMSGHBackend.get_api_id(),
INCOMING,
Decimal("0.0"),
currency=default_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSMSGHBackend.get_api_id(),
OUTGOING,
Decimal("0.0"),
currency=default_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated SMSGH gateway fees.")
def bootstrap_apposit_gateway(apps=None):
usd_currency, _ = (apps.get_model('accounting', 'Currency')
if apps else Currency).objects.get_or_create(code="USD")
sms_gateway_fee_class = apps.get_model(
'smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model(
'smsbillables',
'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLAppositBackend.get_api_id(),
INCOMING,
Decimal('0.02'),
currency=usd_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLAppositBackend.get_api_id(),
OUTGOING,
Decimal('0.02'),
currency=usd_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Apposit gateway fees.")
def bootstrap_smsgh_gateway(apps=None):
default_currency, _ = (apps.get_model('accounting', 'Currency')
if apps else Currency).objects.get_or_create(
code=settings.DEFAULT_CURRENCY)
sms_gateway_fee_class = apps.get_model(
'smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model(
'smsbillables',
'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLSMSGHBackend.get_api_id(),
INCOMING,
Decimal('0.0'),
currency=default_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSMSGHBackend.get_api_id(),
OUTGOING,
Decimal('0.0'),
currency=default_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated SMSGH gateway fees.")
def bootstrap_grapevine_gateway(apps):
currency_class = apps.get_model('accounting', 'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
relevant_backends = SQLGrapevineBackend.get_global_backends_for_this_class(SQLGrapevineBackend.SMS)
currency = currency_class.objects.get_or_create(code="ZAR")[0]
# any incoming message
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.10'),
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Global Grapevine gateway fees.")
# messages relevant to our Grapevine Backends
for backend in relevant_backends:
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.10'),
currency=currency, backend_instance=backend.couch_id,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), OUTGOING, Decimal('0.22'),
currency=currency, backend_instance=backend.couch_id,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Grapevine fees for backend %s" % backend.name)
def bootstrap_grapevine_gateway(apps):
currency_class = apps.get_model('accounting', 'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
relevant_backends = SQLGrapevineBackend.get_global_backends_for_this_class(SQLGrapevineBackend.SMS)
currency = currency_class.objects.get_or_create(code="ZAR")[0]
# any incoming message
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.10'),
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Global Grapevine gateway fees.")
# messages relevant to our Grapevine Backends
for backend in relevant_backends:
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.10'),
currency=currency, backend_instance=backend.couch_id,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), OUTGOING, Decimal('0.22'),
currency=currency, backend_instance=backend.couch_id,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Grapevine fees for backend %s" % backend.name)
def add_moz_zero_charge(apps):
mzn, _ = (apps.get_model('accounting', 'Currency') if apps else Currency).objects.get_or_create(code='MZN')
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('0'),
country_code=None,
prefix='',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('0'),
country_code=None,
prefix='',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Moz gateway default fees.")
def get_mach_data():
mach_workbook = xlrd.open_workbook(
'corehq/apps/smsbillables/management/'
'commands/pricing_data/Syniverse_coverage_list_DIAMONDplus.xls')
mach_table = mach_workbook.sheet_by_index(0)
mach_data = {}
try:
row = 7
while True:
country_code = int(mach_table.cell_value(row, 0))
iso = mach_table.cell_value(row, 1)
network = mach_table.cell_value(row,
5).lower().replace(' ', '')
subscribers = 0
try:
subscribers = int(
mach_table.cell_value(row, 10).replace('.', ''))
except ValueError:
log_smsbillables_info(
"Incomplete subscriber data for country code %d" %
country_code)
if not (iso in mach_data):
mach_data[iso] = {}
mach_data[iso][network] = (country_code, subscribers)
row += 1
except IndexError:
pass
return mach_data
def bootstrap_yo_gateway(apps):
ugx, _ = (apps.get_model('accounting', 'Currency')
if apps else Currency).objects.get_or_create(code='UGX')
sms_gateway_fee_class = apps.get_model(
'smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model(
'smsbillables',
'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLYoBackend.get_api_id(),
INCOMING,
Decimal('110.0'),
currency=ugx,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLYoBackend.get_api_id(),
OUTGOING,
Decimal('55.0'),
currency=ugx,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Yo gateway fees.")
def bootstrap_grapevine_gateway_update(apps):
currency_class = apps.get_model('accounting', 'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
currency = currency_class.objects.get_or_create(code="ZAR")[0]
# Incoming message to South Africa
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.65'),
country_code='27',
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Outgoing message from South Africa
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), OUTGOING, Decimal('0.22'),
country_code='27',
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Explicitly include Lesotho fees for pricing table UI.
# Incoming message to Lesotho
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.90'),
country_code='266',
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Outgoing message from Lesotho
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), OUTGOING, Decimal('0.90'),
country_code='266',
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Incoming message to arbitrary country
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.90'),
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Outgoing message from arbitrary country
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), OUTGOING, Decimal('0.90'),
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Global Grapevine gateway fees.")
def bootstrap_grapevine_gateway_update(apps):
currency_class = apps.get_model('accounting', 'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
currency = currency_class.objects.get_or_create(code="ZAR")[0]
# Incoming message to South Africa
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.65'),
country_code='27',
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Outgoing message from South Africa
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), OUTGOING, Decimal('0.22'),
country_code='27',
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Explicitly include Lesotho fees for pricing table UI.
# Incoming message to Lesotho
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.90'),
country_code='266',
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Outgoing message from Lesotho
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), OUTGOING, Decimal('0.90'),
country_code='266',
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Incoming message to arbitrary country
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), INCOMING, Decimal('0.90'),
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Outgoing message from arbitrary country
SmsGatewayFee.create_new(
SQLGrapevineBackend.get_api_id(), OUTGOING, Decimal('0.90'),
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Global Grapevine gateway fees.")
def bootstrap_mach_gateway(apps):
currency_class = apps.get_model('accounting', 'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
workbook = xlrd.open_workbook('corehq/apps/smsbillables/management/pricing_data/Syniverse_coverage_list_PREMIUM_Sept2019.xlsx')
table = workbook.sheet_by_index(0)
data = {}
try:
row = 7
while True:
if table.cell_value(row, 6) == 'yes':
country_code = int(table.cell_value(row, 0))
if not(country_code in data):
data[country_code] = []
subscribers = table.cell_value(row, 10).replace('.', '')
try:
data[country_code].append(
(table.cell_value(row, 9), int(subscribers)))
except ValueError:
log_smsbillables_info('Incomplete data for country code %d' % country_code)
row += 1
except IndexError:
pass
for country_code in data:
total_subscribers = 0
weighted_price = 0
for price, subscribers in data[country_code]:
total_subscribers += subscribers
weighted_price += price * subscribers
if total_subscribers == 0:
continue
weighted_price = weighted_price / total_subscribers
SmsGatewayFee.create_new(
SQLMachBackend.get_api_id(),
OUTGOING,
weighted_price,
country_code=country_code,
currency=currency_class.objects.get(code="EUR"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Fee for invalid phonenumber
SmsGatewayFee.create_new(
SQLMachBackend.get_api_id(),
OUTGOING,
0.0225,
country_code=None,
currency=currency_class.objects.get(code="EUR"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated MACH/Syniverse gateway fees.")
def bootstrap_mach_gateway(apps):
currency_class = apps.get_model('accounting', 'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
workbook = xlrd.open_workbook('corehq/apps/smsbillables/management/'
'commands/pricing_data/Syniverse_coverage_list_DIAMONDplus.xls')
table = workbook.sheet_by_index(0)
data = {}
try:
row = 7
while True:
if table.cell_value(row, 6) == 'yes':
country_code = int(table.cell_value(row, 0))
if not(country_code in data):
data[country_code] = []
subscribers = table.cell_value(row, 10).replace('.', '')
try:
data[country_code].append(
(table.cell_value(row, 9), int(subscribers)))
except ValueError:
log_smsbillables_info('Incomplete data for country code %d' % country_code)
row += 1
except IndexError:
pass
for country_code in data:
total_subscribers = 0
weighted_price = 0
for price, subscribers in data[country_code]:
total_subscribers += subscribers
weighted_price += price * subscribers
weighted_price = weighted_price / total_subscribers
SmsGatewayFee.create_new(
SQLMachBackend.get_api_id(),
OUTGOING,
weighted_price,
country_code=country_code,
currency=currency_class.objects.get(code="EUR"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Fee for invalid phonenumber
SmsGatewayFee.create_new(
SQLMachBackend.get_api_id(),
OUTGOING,
0.0225,
country_code=None,
currency=currency_class.objects.get(code="EUR"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated MACH/Syniverse gateway fees.")
def deactivate_grapevine_instance_fee_criteria(apps=None):
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
to_deactivate = sms_gateway_fee_criteria_class.objects.filter(
backend_api_id=SQLGrapevineBackend.get_api_id(),
backend_instance__isnull=False,
is_active=True,
)
assert to_deactivate.count() <= 2
to_deactivate.update(is_active=False)
log_smsbillables_info("Deactivated Grapevine instance fees.")
def deactivate_grapevine_instance_fee_criteria(apps=None):
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
to_deactivate = sms_gateway_fee_criteria_class.objects.filter(
backend_api_id=SQLGrapevineBackend.get_api_id(),
backend_instance__isnull=False,
is_active=True,
)
assert to_deactivate.count() <= 2
to_deactivate.update(is_active=False)
log_smsbillables_info("Deactivated Grapevine instance fees.")
def bootstrap_mach_gateway(apps):
currency_class = apps.get_model('accounting', 'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') \
if apps else SmsGatewayFeeCriteria
filename = 'corehq/apps/smsbillables/management/pricing_data/Syniverse_coverage_list_PREMIUM_Sept2019.xlsx'
workbook = openpyxl.load_workbook(filename, read_only=True, data_only=True)
table = workbook.worksheets[0]
data = {}
for row in islice(table.rows, 7, None):
if row[6].value == 'yes':
country_code = int(row[0].value)
if not(country_code in data):
data[country_code] = []
subscribers = row[10].value.replace('.', '')
try:
data[country_code].append((row[9].value, int(subscribers)))
except ValueError:
log_smsbillables_info('Incomplete data for country code %d' % country_code)
for country_code in data:
total_subscribers = 0
weighted_price = 0
for price, subscribers in data[country_code]:
total_subscribers += subscribers
weighted_price += price * subscribers
if total_subscribers == 0:
continue
weighted_price = weighted_price / total_subscribers
SmsGatewayFee.create_new(
SQLMachBackend.get_api_id(),
OUTGOING,
weighted_price,
country_code=country_code,
currency=currency_class.objects.get(code="EUR"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
# Fee for invalid phonenumber
SmsGatewayFee.create_new(
SQLMachBackend.get_api_id(),
OUTGOING,
0.0225,
country_code=None,
currency=currency_class.objects.get(code="EUR"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated MACH/Syniverse gateway fees.")
def bootstrap_unicel_gateway(apps):
currency = (apps.get_model('accounting.Currency') if apps else Currency).objects.get(code="INR")
sms_gateway_fee_class = apps.get_model('smsbillables.SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables.SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(SQLUnicelBackend.get_api_id(), INCOMING, 0.50,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class)
SmsGatewayFee.create_new(SQLUnicelBackend.get_api_id(), OUTGOING, 0.50,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class)
log_smsbillables_info("Updated Unicel gateway fees.")
def bootstrap_unicel_gateway(apps):
currency = (apps.get_model('accounting.Currency') if apps else Currency).objects.get(code="INR")
sms_gateway_fee_class = apps.get_model('smsbillables.SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables.SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(SQLUnicelBackend.get_api_id(), INCOMING, 0.50,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class)
SmsGatewayFee.create_new(SQLUnicelBackend.get_api_id(), OUTGOING, 0.50,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class)
log_smsbillables_info("Updated Unicel gateway fees.")
def bootstrap_tropo_gateway(apps):
currency = (apps.get_model('accounting', 'Currency')
if apps else Currency).objects.get(code="USD")
sms_gateway_fee_class = apps.get_model(
'smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model(
'smsbillables',
'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLTropoBackend.get_api_id(),
INCOMING,
0.01,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
rates_csv = open(
'corehq/apps/smsbillables/management/'
'pricing_data/tropo_international_rates_2013-12-19.csv',
'r',
encoding='utf-8')
for line in rates_csv.readlines():
data = line.split(',')
if data[1] == 'Fixed Line' and data[4] != '\n':
SmsGatewayFee.create_new(
SQLTropoBackend.get_api_id(),
OUTGOING,
float(data[4].rstrip()),
country_code=int(data[2]),
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
rates_csv.close()
# Fee for invalid phonenumber
SmsGatewayFee.create_new(
SQLTropoBackend.get_api_id(),
OUTGOING,
0.01,
country_code=None,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Tropo gateway fees.")
def bootstrap_twilio_gateway_incoming(apps):
currency_class = apps.get_model('accounting', 'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
# https://www.twilio.com/sms/pricing/us
SmsGatewayFee.create_new(
SQLTwilioBackend.get_api_id(),
INCOMING,
0.0075,
country_code=None,
currency=currency_class.objects.get(code="USD"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated INCOMING Twilio gateway fees.")
def bootstrap_tropo_gateway(apps):
currency = (apps.get_model("accounting", "Currency") if apps else Currency).objects.get(code="USD")
sms_gateway_fee_class = apps.get_model("smsbillables", "SmsGatewayFee") if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = (
apps.get_model("smsbillables", "SmsGatewayFeeCriteria") if apps else SmsGatewayFeeCriteria
)
SmsGatewayFee.create_new(
SQLTropoBackend.get_api_id(),
INCOMING,
0.01,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
rates_csv = open(
"corehq/apps/smsbillables/management/" "pricing_data/tropo_international_rates_2013-12-19.csv", "r"
)
for line in rates_csv.readlines():
data = line.split(",")
if data[1] == "Fixed Line" and data[4] != "\n":
SmsGatewayFee.create_new(
SQLTropoBackend.get_api_id(),
OUTGOING,
float(data[4].rstrip()),
country_code=int(data[2]),
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
rates_csv.close()
# Fee for invalid phonenumber
SmsGatewayFee.create_new(
SQLTropoBackend.get_api_id(),
OUTGOING,
0.01,
country_code=None,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Tropo gateway fees.")
def _bootstrap_gateway(apps, backend):
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee')\
if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria')\
if apps else SmsGatewayFeeCriteria
default_currency, _ = (apps.get_model('accounting', 'Currency') if apps else Currency)\
.objects.get_or_create(code=settings.DEFAULT_CURRENCY)
for direction in [INCOMING, OUTGOING]:
SmsGatewayFee.create_new(
backend.get_api_id(),
direction,
None,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
currency=default_currency,
)
log_smsbillables_info(backend.get_api_id() + " - Updated gateway fees.")
def bootstrap_twilio_gateway_incoming(apps):
currency_class = apps.get_model('accounting',
'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model(
'smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model(
'smsbillables',
'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
# https://www.twilio.com/sms/pricing/us
SmsGatewayFee.create_new(
SQLTwilioBackend.get_api_id(),
INCOMING,
0.0075,
country_code=None,
currency=currency_class.objects.get(code="USD"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated INCOMING Twilio gateway fees.")
def get_twilio_data():
twilio_file = open(twilio_rates_filename)
twilio_csv = csv.reader(twilio_file.read().splitlines())
twilio_data = {}
skip = 0
for row in twilio_csv:
if skip < 4:
skip += 1
continue
else:
try:
iso = row[0].lower()
provider = row[2].split('-')[1].lower().replace(' ', '')
rate = float(row[3])
if not(iso in twilio_data):
twilio_data[iso] = {}
twilio_data[iso][provider] = rate
except IndexError:
log_smsbillables_info("Twilio index error %s:" % row)
twilio_file.close()
return twilio_data
def bootstrap_tropo_gateway(apps):
currency = (apps.get_model('accounting', 'Currency') if apps else Currency).objects.get(code="USD")
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLTropoBackend.get_api_id(),
INCOMING,
0.01,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
rates_csv = open('corehq/apps/smsbillables/management/'
'pricing_data/tropo_international_rates_2013-12-19.csv', 'r', encoding='utf-8')
for line in rates_csv.readlines():
data = line.split(',')
if data[1] == 'Fixed Line' and data[4] != '\n':
SmsGatewayFee.create_new(
SQLTropoBackend.get_api_id(),
OUTGOING,
float(data[4].rstrip()),
country_code=int(data[2]),
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
rates_csv.close()
# Fee for invalid phonenumber
SmsGatewayFee.create_new(
SQLTropoBackend.get_api_id(), OUTGOING, 0.01,
country_code=None,
currency=currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Tropo gateway fees.")
def get_mach_data():
mach_workbook = xlrd.open_workbook('corehq/apps/smsbillables/management/'
'commands/pricing_data/Syniverse_coverage_list_DIAMONDplus.xls')
mach_table = mach_workbook.sheet_by_index(0)
mach_data = {}
try:
row = 7
while True:
country_code = int(mach_table.cell_value(row, 0))
iso = mach_table.cell_value(row, 1)
network = mach_table.cell_value(row, 5).lower().replace(' ', '')
subscribers = 0
try:
subscribers = int(mach_table.cell_value(row, 10).replace('.', ''))
except ValueError:
log_smsbillables_info("Incomplete subscriber data for country code %d" % country_code)
if not(iso in mach_data):
mach_data[iso] = {}
mach_data[iso][network] = (country_code, subscribers)
row += 1
except IndexError:
pass
return mach_data
def bootstrap_apposit_gateway(apps=None):
usd_currency, _ = (apps.get_model('accounting', 'Currency') if apps else Currency).objects.get_or_create(code="USD")
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLAppositBackend.get_api_id(),
INCOMING,
Decimal('0.02'),
currency=usd_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLAppositBackend.get_api_id(),
OUTGOING,
Decimal('0.02'),
currency=usd_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Apposit gateway fees.")
def bootstrap_telerivet_gateway(apps):
default_currency, _ = (apps.get_model('accounting', 'Currency') if apps else Currency).objects.get_or_create(code=settings.DEFAULT_CURRENCY)
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLTelerivetBackend.get_api_id(),
INCOMING,
Decimal('0.0'),
currency=default_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLTelerivetBackend.get_api_id(),
OUTGOING,
Decimal('0.0'),
currency=default_currency,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Telerivet gateway fees.")
def bootstrap_yo_gateway(apps):
ugx, _ = (apps.get_model('accounting', 'Currency') if apps else Currency).objects.get_or_create(code='UGX')
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLYoBackend.get_api_id(),
INCOMING,
Decimal('110.0'),
currency=ugx,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLYoBackend.get_api_id(),
OUTGOING,
Decimal('55.0'),
currency=ugx,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Yo gateway fees.")
def bootstrap_twilio_gateway(apps, twilio_rates_filename):
currency_class = apps.get_model('accounting', 'Currency') if apps else Currency
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
# iso -> provider -> rate
def get_twilio_data():
twilio_file = open(twilio_rates_filename)
twilio_csv = csv.reader(twilio_file.read().splitlines())
twilio_data = {}
skip = 0
for row in twilio_csv:
if skip < 4:
skip += 1
continue
else:
try:
iso = row[0].lower()
provider = row[2].split('-')[1].lower().replace(' ', '')
rate = float(row[3])
if not(iso in twilio_data):
twilio_data[iso] = {}
twilio_data[iso][provider] = rate
except IndexError:
log_smsbillables_info("Twilio index error %s:" % row)
twilio_file.close()
return twilio_data
# iso -> provider -> (country code, number of subscribers)
def get_mach_data():
mach_workbook = xlrd.open_workbook('corehq/apps/smsbillables/management/'
'commands/pricing_data/Syniverse_coverage_list_DIAMONDplus.xls')
mach_table = mach_workbook.sheet_by_index(0)
mach_data = {}
try:
row = 7
while True:
country_code = int(mach_table.cell_value(row, 0))
iso = mach_table.cell_value(row, 1)
network = mach_table.cell_value(row, 5).lower().replace(' ', '')
subscribers = 0
try:
subscribers = int(mach_table.cell_value(row, 10).replace('.', ''))
except ValueError:
log_smsbillables_info("Incomplete subscriber data for country code %d" % country_code)
if not(iso in mach_data):
mach_data[iso] = {}
mach_data[iso][network] = (country_code, subscribers)
row += 1
except IndexError:
pass
return mach_data
twilio_data = get_twilio_data()
mach_data = get_mach_data()
for iso in twilio_data:
if iso in mach_data:
weighted_price = 0
total_subscriptions = 0
country_code = None
calculate_other = False
for twilio_provider in twilio_data[iso]:
if twilio_provider == 'other':
calculate_other = True
else:
for mach_provider in mach_data[iso]:
try:
if twilio_provider in mach_provider:
country_code, subscriptions = mach_data[iso][mach_provider]
weighted_price += twilio_data[iso][twilio_provider] * subscriptions
total_subscriptions += subscriptions
mach_data[iso][mach_provider] = country_code, 0
break
except UnicodeDecodeError:
pass
if calculate_other:
other_rate_twilio = twilio_data[iso]['other']
for _, subscriptions in mach_data[iso].values():
weighted_price += other_rate_twilio * subscriptions
total_subscriptions += subscriptions
if country_code is not None:
weighted_price = weighted_price / total_subscriptions
SmsGatewayFee.create_new(
SQLTwilioBackend.get_api_id(),
OUTGOING,
weighted_price,
country_code=country_code,
currency=currency_class.objects.get(code="USD"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
else:
log_smsbillables_info("%s not in mach_data" % iso)
# https://www.twilio.com/help/faq/sms/will-i-be-charged-if-twilio-encounters-an-error-when-sending-an-sms
SmsGatewayFee.create_new(
SQLTwilioBackend.get_api_id(),
OUTGOING,
0.00,
country_code=None,
currency=currency_class.objects.get(code="USD"),
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Twilio gateway fees.")
def bootstrap_usage_fees(apps):
SmsUsageFee.create_new(INCOMING, 0.01)
SmsUsageFee.create_new(OUTGOING, 0.01)
log_smsbillables_info("Updated usage fees.")
def bootstrap_moz_gateway(apps):
mzn, _ = (apps.get_model('accounting', 'Currency') if apps else Currency).objects.get_or_create(code='MZN')
sms_gateway_fee_class = apps.get_model('smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model('smsbillables', 'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('1.4625'),
country_code='258',
prefix='82',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('1.4625'),
country_code='258',
prefix='83',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('1.4625'),
country_code='258',
prefix='84',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('0.702'),
country_code='258',
prefix='86',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('0.702'),
country_code='258',
prefix='87',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('0.3627'),
country_code='258',
prefix='82',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('0.3627'),
country_code='258',
prefix='83',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('0.3627'),
country_code='258',
prefix='84',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('1.1232'),
country_code='258',
prefix='86',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('1.1232'),
country_code='258',
prefix='87',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Moz gateway fees.")
def bootstrap_usage_fees(apps):
SmsUsageFee.create_new(INCOMING, 0.01)
SmsUsageFee.create_new(OUTGOING, 0.01)
log_smsbillables_info("Updated usage fees.")
def bootstrap_moz_gateway(apps):
mzn, _ = (apps.get_model('accounting', 'Currency')
if apps else Currency).objects.get_or_create(code='MZN')
sms_gateway_fee_class = apps.get_model(
'smsbillables', 'SmsGatewayFee') if apps else SmsGatewayFee
sms_gateway_fee_criteria_class = apps.get_model(
'smsbillables',
'SmsGatewayFeeCriteria') if apps else SmsGatewayFeeCriteria
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('1.4625'),
country_code='258',
prefix='82',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('1.4625'),
country_code='258',
prefix='83',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('1.4625'),
country_code='258',
prefix='84',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('0.702'),
country_code='258',
prefix='86',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
INCOMING,
Decimal('0.702'),
country_code='258',
prefix='87',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('0.3627'),
country_code='258',
prefix='82',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('0.3627'),
country_code='258',
prefix='83',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('0.3627'),
country_code='258',
prefix='84',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('1.1232'),
country_code='258',
prefix='86',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
SmsGatewayFee.create_new(
SQLSislogBackend.get_api_id(),
OUTGOING,
Decimal('1.1232'),
country_code='258',
prefix='87',
currency=mzn,
fee_class=sms_gateway_fee_class,
criteria_class=sms_gateway_fee_criteria_class,
)
log_smsbillables_info("Updated Moz gateway fees.")
