def insertsocialrelationships(request):
html_response = HttpResponse()
# Rebuild Relationships
SocialRelationship.objects.filter().delete()
sql = """
SELECT clatoolkit_learningrecord.username, clatoolkit_learningrecord.verb, obj, clatoolkit_learningrecord.platform, clatoolkit_learningrecord.datetimestamp, clatoolkit_learningrecord.message, clatoolkit_learningrecord.course_code, clatoolkit_learningrecord.verb, clatoolkit_learningrecord.platformid
FROM clatoolkit_learningrecord, json_array_elements(clatoolkit_learningrecord.xapi->'context'->'contextActivities'->'other') obj
"""
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
#print row
dict = row[2]
tag = dict["definition"]["name"]["en-US"]
if tag.startswith('@'): # hastags are also returned in query and need to be filtered out
socialrelationship = SocialRelationship()
socialrelationship.fromusername = row[0] #get_username_fromsmid(row[0], row[3])
socialrelationship.tousername = get_username_fromsmid(tag[1:], row[3]) #remove @symbol
socialrelationship.message = row[5]
socialrelationship.datetimestamp = row[4]
socialrelationship.platform = row[3]
socialrelationship.verb = "mentioned"
socialrelationship.platformid = row[8]
socialrelationship.course_code = row[6]
socialrelationship.save()
print row[3]
#get all statements with platformparentid
sql = """
SELECT clatoolkit_learningrecord.username, clatoolkit_learningrecord.parentusername, clatoolkit_learningrecord.verb, clatoolkit_learningrecord.platform, clatoolkit_learningrecord.platformid, clatoolkit_learningrecord.message, clatoolkit_learningrecord.datetimestamp, clatoolkit_learningrecord.course_code
FROM clatoolkit_learningrecord
WHERE COALESCE(clatoolkit_learningrecord.platformparentid, '') != ''
"""
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
socialrelationship = SocialRelationship()
socialrelationship.fromusername = row[0] #get_username_fromsmid(row[0], row[3])
socialrelationship.tousername = row[1] #get_username_fromsmid(row[1], row[3])
socialrelationship.message = row[5]
socialrelationship.datetimestamp = row[6]
socialrelationship.platform = row[3]
socialrelationship.verb = row[2]
socialrelationship.platformid = row[4]
socialrelationship.course_code = row[7]
socialrelationship.save()
html_response.write('Social Relationships Updated.')
return html_response
def insertsocialrelationships(request):
html_response = HttpResponse()
# Rebuild Relationships
SocialRelationship.objects.filter().delete()
sql = """
SELECT clatoolkit_learningrecord.username, clatoolkit_learningrecord.verb, obj, clatoolkit_learningrecord.platform, clatoolkit_learningrecord.datetimestamp, clatoolkit_learningrecord.message, clatoolkit_learningrecord.course_code, clatoolkit_learningrecord.verb, clatoolkit_learningrecord.platformid
FROM clatoolkit_learningrecord, json_array_elements(clatoolkit_learningrecord.xapi->'context'->'contextActivities'->'other') obj
"""
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
# print row
dict = row[2]
tag = dict["definition"]["name"]["en-US"]
if tag.startswith("@"): # hastags are also returned in query and need to be filtered out
socialrelationship = SocialRelationship()
socialrelationship.fromusername = row[0] # get_username_fromsmid(row[0], row[3])
socialrelationship.tousername = get_username_fromsmid(tag[1:], row[3]) # remove @symbol
socialrelationship.message = row[5]
socialrelationship.datetimestamp = row[4]
socialrelationship.platform = row[3]
socialrelationship.verb = "mentioned"
socialrelationship.platformid = row[8]
socialrelationship.course_code = row[6]
socialrelationship.save()
print row[3]
# get all statements with platformparentid
sql = """
SELECT clatoolkit_learningrecord.username, clatoolkit_learningrecord.parentusername, clatoolkit_learningrecord.verb, clatoolkit_learningrecord.platform, clatoolkit_learningrecord.platformid, clatoolkit_learningrecord.message, clatoolkit_learningrecord.datetimestamp, clatoolkit_learningrecord.course_code
FROM clatoolkit_learningrecord
WHERE COALESCE(clatoolkit_learningrecord.platformparentid, '') != ''
"""
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
socialrelationship = SocialRelationship()
socialrelationship.fromusername = row[0] # get_username_fromsmid(row[0], row[3])
socialrelationship.tousername = row[1] # get_username_fromsmid(row[1], row[3])
socialrelationship.message = row[5]
socialrelationship.datetimestamp = row[6]
socialrelationship.platform = row[3]
socialrelationship.verb = row[2]
socialrelationship.platformid = row[4]
socialrelationship.course_code = row[7]
socialrelationship.save()
html_response.write("Social Relationships Updated.")
return html_response
