class NoteFilter(HttpRequestResponser, Formatter):
def __init__(self):
self.sql = SQLExecuter()
def getValuesBasedonKey(self, valueset, key):
result = []
for row in valueset:
result.append(row[key])
return result
def getKeywordString(self, data):
result, keywordset, sql = [{}, [], ""]
keywords = string.split(data["keywords"], " ")
# log keywords the user uses and his location
Log_Keywords().logUserKeywords(data, keywords)
# make query string
for word in keywords:
keywordset.append("%" + word + "%")
keywordset.append("%" + word + "%")
sql += "(a.note like %s Or c.tag_name like %s) And "
result["sql"] = "(" + sql[: len(sql) - 5] + ")"
result["keywords"] = keywordset
result["n_keywords"] = len(keywordset)
return result
def getNoteInfoListByKewords(self, data, currenttime):
# data['keywords'] = '%' + data['keywords'] + '%'
keywordset = self.getKeywordString(data)
# strSQL = "Select a.*, b.tagid, c.sys_tagid, c.tag_name, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=0) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid And (a.note like %s Or c.tag_name like %s) Union Select a.*, b.tagid, c.sys_tagid, c.tag_name, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=1) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid And (a.note like %s Or c.tag_name like %s)"
strSQL = (
"Select a.*, b.tagid, c.sys_tagid, c.tag_name, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=0) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid And %s Union Select a.*, b.tagid, c.sys_tagid, c.tag_name, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=1) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid And %s"
% ("%s", keywordset["sql"], "%s", keywordset["sql"])
)
values = keywordset["keywords"] * 2
values.insert(0, currenttime)
values.insert(int(keywordset["n_keywords"]) + 1, currenttime)
# print values
noteslist = self.sql.doRawSQL(strSQL, values)
return noteslist
def getNoteInfoList(self, currenttime):
# retrieve every detail of notes
strSQL = "Select a.*, b.tagid, c.sys_tagid, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=0) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid Union Select a.*, b.tagid, c.sys_tagid, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=1) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid"
noteslist = self.sql.doRawSQL(strSQL, [currenttime, currenttime])
return noteslist
def getUserCategoryTagsList(self, data):
args = {}
args["columns"] = ["b.*, c.sys_tagid"]
args["tables"] = ["state as a", "filter as b", "tag as c"]
args["joins"] = ["a.stateid=b.stateid And a.uid=b.uid And b.tagid=c.tagid And a.is_current=1 And is_checked=1"]
args["conditions"] = [{"criteria": "b.uid=", "logic": "And"}]
args["values"] = [data["uid"]]
uCTags = self.sql.doSelectData(args)
return uCTags
def computeDistance(self, data, n_longitude, n_latitude):
"""
Calculate the great circle distance between two points
on the earth (specified in decimal degrees)
"""
# convert decimal degrees to radians
lon1, lat1, lon2, lat2 = map(
radians, [float(n_longitude), float(n_latitude), float(data["u_longitude"]), float(data["u_latitude"])]
)
# haversine formula
dlon = lon2 - lon1
dlat = lat2 - lat1
a = sin(dlat / 2) ** 2 + cos(lat1) * cos(lat2) * sin(dlon / 2) ** 2
c = 2 * asin(sqrt(a))
dist = (6367 * c) * 1093.61 # km to yard
return dist
def filterByTags(self, uProfile, noteslist):
result = []
passset = self.getValuesBasedonKey(uProfile, "sys_tagid")
for note in noteslist:
if note["sys_tagid"] in passset:
result.append(note)
return result
def filterByTime(self, uProfile, noteslist, currenttime):
result = []
sys_tagset = []
currenttime = datetime.datetime.strptime(currenttime, "%Y-%m-%d %H:%M:%S")
for filter in uProfile:
if filter["f_repeat"]:
current = currenttime.strftime("%H:%M:%S")
start = filter["f_start_time"].strftime("%H:%M:%S")
end = filter["f_stop_time"].strftime("%H:%M:%S")
else:
current = currenttime.strftime("%Y-%m-%d %H:%M:%S")
start = filter["f_start_time"]
end = filter["f_stop_time"]
# print "current %s" % current
# print "start %s" % start
# print "end %s" % end
if current >= start and current <= end:
sys_tagset.append(filter["sys_tagid"])
# print "active sys_tagset"
# print sys_tagset
for note in noteslist:
# print "note " + str(note['noteid']) + " has sys:" + str(note['sys_tagid'])
if note["sys_tagid"] in sys_tagset:
# print "note " + str(note['noteid']) + " passed"
result.append(note)
return result
def filterByVisibility(self, data, uProfile, noteslist):
friendslist = Friend().getFriendsList(data)
# print "friendslist"
# print friendslist
# generalize visibility of user tags based on sys_tags
sys_visset = {}
result = []
# print "current uProfile"
# print uProfile
for ufilter in uProfile:
sys_tag = ufilter["sys_tagid"]
visibility = ufilter["f_visibility"]
if (sys_tag in sys_visset and sys_visset[sys_tag] < visibility) or (sys_tag not in sys_visset):
sys_visset[sys_tag] = visibility
# print "visibility of sys_tag"
# print sys_visset
for note in noteslist:
# print "with sys:" + str(note['sys_tagid']) + ", note " + str(note['noteid']) + " has vis:" + str(note['n_visibility']) + " while user has vis:" + str(sys_visset[note['sys_tagid']])
case_a, case_b, case_c, case_d, case_e, case_f, case_g = [False, False, False, False, False, False, False]
if note["sys_tagid"] in sys_visset:
# print str(note['uid']) in friendslist
# if both of them have visibilities of public
if sys_visset[note["sys_tagid"]] == 0 and note["n_visibility"] == 0:
case_a = True
# print "note " + str(note['noteid']) + " passed because of case_a"
# if sys_visset[note['sys_tagid']] == 0 and note['n_visibility'] == 1 and (str(note['uid']) != str(data['uid']) and note['uid'] in friendslist):
if (
sys_visset[note["sys_tagid"]] == 0
and note["n_visibility"] == 1
and not ((str(note["uid"]) != str(data["uid"]) and note["uid"] not in friendslist))
):
case_b = True
# print "note " + str(note['noteid']) + " passed because of case_b"
if (
sys_visset[note["sys_tagid"]] == 0
and note["n_visibility"] == 2
and str(note["uid"]) == str(data["uid"])
):
case_c = True
# print "note " + str(note['noteid']) + " passed because of case_c"
if (
sys_visset[note["sys_tagid"]] == 1
and note["n_visibility"] == 0
and not (str(note["uid"]) != str(data["uid"]) and note["uid"] not in friendslist)
):
case_d = True
# print "note " + str(note['noteid']) + " passed because of case_d"
if (
sys_visset[note["sys_tagid"]] == 1
and note["n_visibility"] == 1
and not (str(note["uid"]) != str(data["uid"]) and note["uid"] not in friendslist)
):
case_e = True
# print "note " + str(note['noteid']) + " passed because of case_e"
if (
sys_visset[note["sys_tagid"]] == 1
and note["n_visibility"] == 2
and str(note["uid"]) == str(data["uid"])
):
case_f = True
# print "note " + str(note['noteid']) + " passed because of case_f"
# if reader has private and he is poster also
# print data['uid']
# print note['uid']
if sys_visset[note["sys_tagid"]] == 2 and str(note["uid"]) == str(data["uid"]):
case_g = True
# print "note " + str(note['noteid']) + " passed because of case_g"
if case_a or case_b or case_c or case_d or case_e or case_f or case_g:
result.append(note)
"""
if note['sys_tagid'] in sys_visset and sys_visset[note['sys_tagid']] == note['n_visibility']:
if (note['n_visibility'] == 1 and note['uid'] in friendslist) or note['n_visibility'] == 0:
result.append(note)
"""
# print "after visibility"
# print result
return result
def filterByLocation(self, data, noteslist):
result = []
for note in noteslist:
dist = self.computeDistance(data, note["n_longitude"], note["n_latitude"])
if dist <= note["radius"]:
result.append(note)
return result
def filterNotes(self, data, mode="normal"):
localtime = JingoTimezone().getLocalTime()
currenttime = localtime.strftime("%Y-%m-%d %H:%M:%S")
if mode == "normal":
noteslist = self.getNoteInfoList(currenttime)
else:
noteslist = self.getNoteInfoListByKewords(data, currenttime)
uProfile = self.getUserCategoryTagsList(data)
# filter by user's tags
noteslist = self.filterByTags(uProfile, noteslist)
# filter by user's time range
noteslist = self.filterByTime(uProfile, noteslist, currenttime)
# filter by visibility and friendship
noteslist = self.filterByVisibility(data, uProfile, noteslist)
# filter by location
noteslist = self.filterByLocation(data, noteslist)
# print noteslist
return noteslist
def retrieveNotesByKeywords(self, data):
return self.filterNotes(data, "keyword")
class NoteFilter(HttpRequestResponser, Formatter):
def __init__(self):
self.sql = SQLExecuter()
def getValuesBasedonKey(self, valueset, key):
result = []
for row in valueset:
result.append(row[key])
return result
def getKeywordString(self, data):
result, keywordset, sql = [{}, [], '']
keywords = string.split(data['keywords'], ' ')
# log keywords the user uses and his location
Log_Keywords().logUserKeywords(data, keywords)
# make query string
for word in keywords:
keywordset.append('%' + word + '%')
keywordset.append('%' + word + '%')
sql += '(a.note like %s Or c.tag_name like %s) And '
result['sql'] = '(' + sql[:len(sql) - 5] + ')'
result['keywords'] = keywordset
result['n_keywords'] = len(keywordset)
return result
def getNoteInfoListByKewords(self, data, currenttime):
#data['keywords'] = '%' + data['keywords'] + '%'
keywordset = self.getKeywordString(data)
#strSQL = "Select a.*, b.tagid, c.sys_tagid, c.tag_name, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=0) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid And (a.note like %s Or c.tag_name like %s) Union Select a.*, b.tagid, c.sys_tagid, c.tag_name, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=1) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid And (a.note like %s Or c.tag_name like %s)"
strSQL = "Select a.*, b.tagid, c.sys_tagid, c.tag_name, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=0) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid And %s Union Select a.*, b.tagid, c.sys_tagid, c.tag_name, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=1) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid And %s" % (
'%s', keywordset['sql'], '%s', keywordset['sql'])
values = keywordset['keywords'] * 2
values.insert(0, currenttime)
values.insert(int(keywordset['n_keywords']) + 1, currenttime)
#print values
noteslist = self.sql.doRawSQL(strSQL, values)
return noteslist
def getNoteInfoList(self, currenttime):
# retrieve every detail of notes
strSQL = 'Select a.*, b.tagid, c.sys_tagid, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=0) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid Union Select a.*, b.tagid, c.sys_tagid, d.n_start_time, d.n_stop_time, n_repeat From note as a, note_tag as b, tag as c, (Select * From note_time Where %s between n_start_time And n_stop_time And n_repeat=1) as d Where a.noteid=b.noteid And b.tagid=c.tagid And a.noteid=d.noteid'
noteslist = self.sql.doRawSQL(strSQL, [currenttime, currenttime])
return noteslist
def getUserCategoryTagsList(self, data):
args = {}
args['columns'] = ['b.*, c.sys_tagid']
args['tables'] = ['state as a', 'filter as b', 'tag as c']
args['joins'] = [
'a.stateid=b.stateid And a.uid=b.uid And b.tagid=c.tagid And a.is_current=1 And is_checked=1'
]
args['conditions'] = [{'criteria': 'b.uid=', 'logic': 'And'}]
args['values'] = [data['uid']]
uCTags = self.sql.doSelectData(args)
return uCTags
def computeDistance(self, data, n_longitude, n_latitude):
"""
Calculate the great circle distance between two points
on the earth (specified in decimal degrees)
"""
# convert decimal degrees to radians
lon1, lat1, lon2, lat2 = map(radians, [
float(n_longitude),
float(n_latitude),
float(data['u_longitude']),
float(data['u_latitude'])
])
# haversine formula
dlon = lon2 - lon1
dlat = lat2 - lat1
a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
c = 2 * asin(sqrt(a))
dist = (6367 * c) * 1093.61 # km to yard
return dist
def filterByTags(self, uProfile, noteslist):
result = []
passset = self.getValuesBasedonKey(uProfile, 'sys_tagid')
for note in noteslist:
if note['sys_tagid'] in passset:
result.append(note)
return result
def filterByTime(self, uProfile, noteslist, currenttime):
result = []
sys_tagset = []
currenttime = datetime.datetime.strptime(currenttime,
'%Y-%m-%d %H:%M:%S')
for filter in uProfile:
if filter['f_repeat']:
current = currenttime.strftime('%H:%M:%S')
start = filter['f_start_time'].strftime('%H:%M:%S')
end = filter['f_stop_time'].strftime('%H:%M:%S')
else:
current = currenttime.strftime('%Y-%m-%d %H:%M:%S')
start = filter['f_start_time']
end = filter['f_stop_time']
#print "current %s" % current
#print "start %s" % start
#print "end %s" % end
if current >= start and current <= end:
sys_tagset.append(filter['sys_tagid'])
#print "active sys_tagset"
#print sys_tagset
for note in noteslist:
#print "note " + str(note['noteid']) + " has sys:" + str(note['sys_tagid'])
if note['sys_tagid'] in sys_tagset:
#print "note " + str(note['noteid']) + " passed"
result.append(note)
return result
def filterByVisibility(self, data, uProfile, noteslist):
friendslist = Friend().getFriendsList(data)
#print "friendslist"
#print friendslist
# generalize visibility of user tags based on sys_tags
sys_visset = {}
result = []
#print "current uProfile"
#print uProfile
for ufilter in uProfile:
sys_tag = ufilter['sys_tagid']
visibility = ufilter['f_visibility']
if (sys_tag in sys_visset and
sys_visset[sys_tag] < visibility) or (sys_tag
not in sys_visset):
sys_visset[sys_tag] = visibility
#print "visibility of sys_tag"
#print sys_visset
for note in noteslist:
#print "with sys:" + str(note['sys_tagid']) + ", note " + str(note['noteid']) + " has vis:" + str(note['n_visibility']) + " while user has vis:" + str(sys_visset[note['sys_tagid']])
case_a, case_b, case_c, case_d, case_e, case_f, case_g = [
False, False, False, False, False, False, False
]
if note['sys_tagid'] in sys_visset:
#print str(note['uid']) in friendslist
# if both of them have visibilities of public
if sys_visset[
note['sys_tagid']] == 0 and note['n_visibility'] == 0:
case_a = True
#print "note " + str(note['noteid']) + " passed because of case_a"
#if sys_visset[note['sys_tagid']] == 0 and note['n_visibility'] == 1 and (str(note['uid']) != str(data['uid']) and note['uid'] in friendslist):
if sys_visset[note['sys_tagid']] == 0 and note[
'n_visibility'] == 1 and not (
(str(note['uid']) != str(data['uid'])
and note['uid'] not in friendslist)):
case_b = True
#print "note " + str(note['noteid']) + " passed because of case_b"
if sys_visset[note['sys_tagid']] == 0 and note[
'n_visibility'] == 2 and str(note['uid']) == str(
data['uid']):
case_c = True
#print "note " + str(note['noteid']) + " passed because of case_c"
if sys_visset[note['sys_tagid']] == 1 and note[
'n_visibility'] == 0 and not (str(note['uid']) != str(
data['uid']) and note['uid'] not in friendslist):
case_d = True
#print "note " + str(note['noteid']) + " passed because of case_d"
if sys_visset[note['sys_tagid']] == 1 and note[
'n_visibility'] == 1 and not (str(note['uid']) != str(
data['uid']) and note['uid'] not in friendslist):
case_e = True
#print "note " + str(note['noteid']) + " passed because of case_e"
if sys_visset[note['sys_tagid']] == 1 and note[
'n_visibility'] == 2 and str(note['uid']) == str(
data['uid']):
case_f = True
#print "note " + str(note['noteid']) + " passed because of case_f"
# if reader has private and he is poster also
#print data['uid']
#print note['uid']
if sys_visset[note['sys_tagid']] == 2 and str(
note['uid']) == str(data['uid']):
case_g = True
#print "note " + str(note['noteid']) + " passed because of case_g"
if case_a or case_b or case_c or case_d or case_e or case_f or case_g:
result.append(note)
'''
if note['sys_tagid'] in sys_visset and sys_visset[note['sys_tagid']] == note['n_visibility']:
if (note['n_visibility'] == 1 and note['uid'] in friendslist) or note['n_visibility'] == 0:
result.append(note)
'''
#print "after visibility"
#print result
return result
def filterByLocation(self, data, noteslist):
result = []
for note in noteslist:
dist = self.computeDistance(data, note['n_longitude'],
note['n_latitude'])
if dist <= note['radius']:
result.append(note)
return result
def filterNotes(self, data, mode='normal'):
localtime = JingoTimezone().getLocalTime()
currenttime = localtime.strftime('%Y-%m-%d %H:%M:%S')
if mode == 'normal':
noteslist = self.getNoteInfoList(currenttime)
else:
noteslist = self.getNoteInfoListByKewords(data, currenttime)
uProfile = self.getUserCategoryTagsList(data)
# filter by user's tags
noteslist = self.filterByTags(uProfile, noteslist)
# filter by user's time range
noteslist = self.filterByTime(uProfile, noteslist, currenttime)
# filter by visibility and friendship
noteslist = self.filterByVisibility(data, uProfile, noteslist)
# filter by location
noteslist = self.filterByLocation(data, noteslist)
#print noteslist
return noteslist
def retrieveNotesByKeywords(self, data):
return self.filterNotes(data, 'keyword')
