class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String, unique=True)
password = db.Column(db.String)
country = db.Column(db.String)
country_slug = db.Column(db.String)
group = db.Column(db.String)
group_slug = db.Column(db.String)
is_superuser = db.Column(db.Boolean, default=False)
authenticated = db.Column(db.Boolean, default=False)
@classmethod
def create_superuser(cls, email, password):
user = cls(email=email, password=password, is_superuser=True)
db.session.add(user)
db.session.commit()
def is_active(self):
return True
def get_id(self):
return self.id
def is_authenticated(self):
return self.authenticated
def is_anonymous(self):
return False
def superuser(self):
return self.is_superuser
def login(self, password):
if self.password == password:
self.authenticated = True
db.session.add(self)
db.session.commit()
login_user(self, remember=True)
return True
return False
def create_slug(self):
if self.country:
self.country_slug = self.country.lower().replace(" ", "_")
if self.group:
self.group_slug = self.group.lower().replace(" ", "_")
def logout(self):
self.authenticated = False
db.session.add(self)
db.session.commit()
logout_user()
@classmethod
def update_country_slug(cls):
for obj in cls.query.all():
obj.create_slug()
db.session.add(obj)
db.session.commit()
class Referral(db.Model):
id = db.Column(db.Integer, primary_key=True)
rapidpro_uuid = db.Column(db.String(50))
code = db.Column(db.String(50))
ref_code = db.Column(db.Integer)
created_on = db.Column(db.DateTime(timezone=True),
server_default=db.func.now())
@classmethod
def is_duplicate(cls, rapidpro_uuid, code):
dup = cls.query.filter_by(code=code.upper(),
rapidpro_uuid=rapidpro_uuid).first()
if dup:
return True
return False
@classmethod
def create(cls, rapidpro_uuid, code):
r = cls(rapidpro_uuid=rapidpro_uuid,
code=code,
ref_code=RefCode.get_by_code(code).id)
db.session.add(r)
db.session.commit()
return r
class Voucher(db.Model):
__tablename__ = 'voucher_vouchers'
id = db.Column(db.Integer, primary_key=True)
flow_id = db.Column(db.Integer, nullable=True)
code = db.Column(db.String(20))
redeemed_on = db.Column(db.DateTime(timezone=True), nullable=True)
created_on = db.Column(db.DateTime(timezone=True),
server_default=db.func.now())
modified_on = db.Column(db.DateTime(timezone=True),
server_default=db.func.now(),
server_onupdate=db.func.now())
redeemed_by = db.Column(db.String(13), nullable=True)
def __init__(self, code):
self.code = code
def __repr__(self):
return self.code
@classmethod
def create(cls):
voucher = cls(code=cls.generate_code())
db.session.add(voucher)
db.session.commit()
return voucher
@classmethod
def add_external_codes(cls, codes):
codes = set(codes)
for code in codes:
voucher = cls(code=code)
db.session.add(voucher)
db.session.commit()
@classmethod
def redeem(cls, code, phone, flow):
voucher = cls.query.filter_by(code=str(code)).first()
if voucher is None:
raise VoucherException("Voucher does not exist")
if voucher.redeemed_on is not None:
raise VoucherException(
"Attempting to redeem an already redeemed voucher")
voucher.redeemed_on = datetime.now()
voucher.redeemed_by = phone
voucher.flow_id = flow
db.session.add(voucher)
db.session.commit()
@classmethod
def _random(cls):
_code = random.randint(100, 999)
while cls.query.filter_by(code=str(_code)).first():
_code = random.randint(100, 999)
return _code
@classmethod
def generate_code(cls):
_code = cls._random()
check_digit = verhoeff.calc_check_digit(_code)
return "%s%s" % (str(_code), str(check_digit))
class Flow(db.Model):
__tablename__ = 'fusion_table_flows'
id = db.Column(db.Integer, primary_key=True, unique=True)
flow_id = db.Column(db.Integer)
name = db.Column(db.String)
ft_id = db.Column(db.String)
ft_columns = db.Column(db.String)
email = db.Column(db.String, nullable=True)
created_on = db.Column(db.DateTime(timezone=True), default=datetime.utcnow)
@classmethod
def create_from_run(cls, run, email):
flow_id = run.get('flow')
flow_name = run.get('flow_name')
values = json.loads(run.get('values'))
columns = cls.get_columns_from_values(values)
flow = cls.create(flow_id, flow_name, columns, email)
return flow
@classmethod
def get_by_flow(cls, flow_id):
return cls.query.filter_by(flow_id=int(flow_id)).first()
@classmethod
def create(cls, flow_id, name, columns, email):
flow = cls(flow_id=flow_id, name=name, email=email)
flow.create_ft(columns)
db.session.add(flow)
db.session.commit()
flow.give_rapidpro_permission()
return flow
@classmethod
def get_columns_from_values(cls, values):
columns = [{'name': 'phone', 'type': 'STRING'}]
n = []
for v in values:
if v.get('node') in n:
continue
columns.append({
'name': '%s (value)' % v.get('label'),
'type': 'STRING'
})
columns.append({
'name': '%s (category)' % v.get('label'),
'type': 'STRING'
})
n.append(v.get('node'))
return columns
def get_updated_columns(self, columns, values):
cl = [
x.get('name')
for x in self.__class__.get_columns_from_values(values)
]
if set(cl) == set(columns):
return columns
new_cl = set(cl) - set(columns)
self.update_ft_table(new_cl)
self.ft_columns = str(cl)
db.session.add(self)
db.session.commit()
return cl
def update_ft_table(self, columns):
service = build_service()
columns = [{'name': x, 'type': 'STRING'} for x in columns]
return [
service.column().insert(tableId=self.ft_id, body=c).execute()
for c in columns
]
def create_ft(self, columns):
service = build_service()
table = {
'name': self.name,
'description': "Rapidpro Flow with ID %s" % self.flow_id,
'isExportable': True,
'columns': columns
}
self.ft_columns = str([str(x.get('name')) for x in columns])
table = service.table().insert(body=table).execute()
self.ft_id = table.get('tableId')
return table
def update_fusion_table(self, phone, values, base_language):
service = build_service()
columns = tuple([str(a) for a in eval(self.ft_columns)])
columns = tuple(
[str(a) for a in self.get_updated_columns(columns, values)])
_order = [str(phone)]
nodes = OrderedDict()
for c in columns:
if c == 'phone':
continue
label = c.rstrip("(value)").strip()
for v in values:
n = v.get('node')
if v.get('label') == label:
category = str(v.get('category').get(base_language))
nodes[n] = [str(v.get('value')), category]
for _v in nodes.values():
_order.extend(_v)
sql = 'INSERT INTO %s %s VALUES %s' % (
self.ft_id, str(tuple(OrderedSet(columns))), str(tuple(_order)))
return service.query().sql(sql=sql).execute()
def give_rapidpro_permission(self):
email = self.email or RAPIDPRO_EMAIL
service = build_drive_service()
body = {
'role': 'writer',
'type': 'user',
'emailAddress': email,
'value': email
}
return service.permissions().insert(
fileId=self.ft_id, body=body,
sendNotificationEmails=True).execute()
def update_email(self, email):
if email and self.email != email:
self.email = email
self.give_rapidpro_permission()
db.session.add(self)
db.session.commit()
return self.email
class FT(db.Model):
id = db.Column(db.Integer, primary_key=True)
ft_id = db.Column(db.String(100))
class RefCode(db.Model):
COLUMNS = ({
'name': 'Rapidpro UUID',
'type': 'STRING'
}, {
'name': 'Join Date',
'type': 'STRING'
})
COLUMN_NAMES = ('Rapidpro UUID', 'Join Date')
ATTR = ({
'name': "ID",
"type": "STRING"
}, {
'name': "Name",
"type": "STRING"
}, {
'name': "Phone",
"type": "STRING"
}, {
'name': "Email",
"type": "STRING"
}, {
'name': "Group",
"type": "STRING"
}, {
'name': "Country",
"type": "STRING"
}, {
'name': "Created On",
"type": "STRING"
}, {
'name': "Fusion Table ID",
"type": "STRING"
}, {
'name': "Referrals",
"type": "STRING"
})
ATTR_NAMES = ("ID", "Name", "Phone", "Email", "Group", "Country",
"Created On", "Fusion Table ID", "Referrals")
id = db.Column(db.Integer, primary_key=True)
ft_id = db.Column(db.String(100))
rapidpro_uuid = db.Column(db.String(100))
name = db.Column(db.String(50))
phone = db.Column(db.String(50))
email = db.Column(db.String(50))
group = db.Column(db.String(50))
country = db.Column(db.String(50))
country_slug = db.Column(db.String(50))
created_on = db.Column(db.DateTime(timezone=True),
server_default=db.func.now())
modified_on = db.Column(db.DateTime(timezone=True),
server_default=db.func.now(),
server_onupdate=db.func.now())
last_ft_update = db.Column(db.DateTime(timezone=True))
in_ft = db.Column(db.Boolean, default=False)
ft_row_id = db.Column(db.String(100))
@classmethod
def create_code(cls, rapidpro_uuid, name, phone, email, group, country):
c = cls.get_by_uuid(rapidpro_uuid)
if c:
return c
ref_code = cls(rapidpro_uuid=rapidpro_uuid)
ref_code.name = name
ref_code.phone = phone
ref_code.email = email
ref_code.group = group
ref_code.country = country
ref_code.country_slug = country.lower().replace(" ", "_")
db.session.add(ref_code)
db.session.commit()
return ref_code
@classmethod
def update_country_slug(cls):
for obj in cls.query.all():
obj.country_slug = obj.country.lower().replace(" ", "_")
db.session.add(obj)
db.session.commit()
@classmethod
def get_by_code(cls, code):
_id = code.split('0', 1)
if len(_id) < 2:
return None
return cls.query.filter_by(id=int(_id[1])).first()
@classmethod
def get_by_uuid(cls, uuid):
return cls.query.filter_by(rapidpro_uuid=uuid).first()
@classmethod
def get_with_no_ft_id(cls):
return cls.query.filter_by(ft_id=None)
@classmethod
def get_main_ft_id(cls):
return FT.query.first().ft_id
@classmethod
def create_main_ft(cls):
service = build_service()
table = {
'name': "Ureport Referrals",
'description': "Code and the number of referrals per code",
'isExportable': True,
'columns': cls.ATTR
}
table = service.table().insert(body=table).execute()
service = build_drive_service()
body = {
'role': 'writer',
'type': 'user',
'emailAddress': RAPIDPRO_EMAIL,
'value': RAPIDPRO_EMAIL
}
service.permissions().insert(fileId=table.get('tableId'),
body=body,
sendNotificationEmails=True).execute()
db.session.add(FT(ft_id=table.get('tableId')))
db.session.commit()
return table
@classmethod
def update_main_ft(cls):
service = build_service()
for code in cls.query.all():
if code.in_ft:
sql = "UPDATE %s SET Referrals = %d WHERE ROWID = '%s'" % (
cls.get_main_ft_id(), code.get_referral_count(),
code.ft_row_id)
service.query().sql(sql=sql).execute()
else:
values = (str(code.id), str(code.name).replace("'", "\\'"),
str(code.phone),
str(code.email), str(code.group).replace("'", "\\'"),
str(code.country).replace("'", "\\'"),
str(code.created_on), str(code.ft_id),
str(code.get_referral_count()))
sql = 'INSERT INTO %s %s VALUES %s' % (
cls.get_main_ft_id(), str(cls.ATTR_NAMES), str(values))
response = service.query().sql(sql=sql).execute()
code.in_ft = True
code.ft_row_id = response['rows'][0][0]
db.session.add(code)
db.session.commit()
logging.info(sql)
def get_prefix(self):
return "%s%s0" % (self.country[:2], self.group)
@property
def ref_code(self):
code = "%s%s0%s" % (self.country[:2], self.group, self.id)
return code.upper()
def get_referrals(self, last_update=False):
if last_update:
return Referral.query.filter(Referral.ref_code == self.id).\
filter(Referral.created_on >= self.last_ft_update).order_by(desc(Referral.created_on))
return Referral.query.filter_by(ref_code=self.id).order_by(
desc(Referral.created_on))
def get_referral_count(self):
return self.get_referrals().count()
@property
def ref_count(self):
return self.get_referral_count()
def create_ft(self):
service = build_service()
table = {
'name': self.name,
'description': "Referrals for Code %s" % self.id,
'isExportable': True,
'columns': self.COLUMNS
}
table = service.table().insert(body=table).execute()
self.ft_id = table.get('tableId')
self.give_rapidpro_permission()
self.give_rapidpro_permission(RAPIDPRO_EMAIL)
db.session.add(self)
db.session.commit()
return table
def update_fusion_table(self):
service = build_service()
refs = self.get_referrals(
True) if self.last_ft_update else self.get_referrals()
values = [
str((str(ref.rapidpro_uuid), str(ref.created_on))) for ref in refs
]
v = [(ref.rapidpro_uuid, str(ref.created_on)) for ref in refs]
if values:
self.last_ft_update = v[0][1]
sql = 'INSERT INTO %s %s VALUES %s' % (
self.ft_id, str(self.COLUMN_NAMES), ','.join(values))
logging.info(sql)
update = service.query().sql(sql=sql).execute()
db.session.add(self)
db.session.commit()
return update
def give_rapidpro_permission(self, email=None):
if not email:
email = self.email or RAPIDPRO_EMAIL
service = build_drive_service()
body = {
'role': 'writer',
'type': 'user',
'emailAddress': email,
'value': email
}
return service.permissions().insert(
fileId=self.ft_id, body=body,
sendNotificationEmails=True).execute()