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 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
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 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 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 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)
print sql
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))
print sql
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 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]