def usrRegDate():
dbConn = ma.connect()
tUsers = ma.Table(dbConn, 'tUsers')
tUsers.selectAll()
regDate = {}
for user in tUsers.cursor.fetchall():
yearMonth = '%d%s' % (user[1].year, str(user[1].month).zfill(2))
if yearMonth not in regDate: regDate[yearMonth] = 0
regDate[yearMonth] += 1
ma.disconnect(dbConn)
keyList = regDate.keys()
keyList.sort()
colorLst = ['y', 'm', 'c', 'r', 'g', 'b']
yearLst = list(set([date[:4] for date in keyList]))
yearLst.sort()
yearCol = [colorLst[yearLst.index(date[:4])] for date in keyList]
axs = plt.subplot()
axs.set_xticks(range(len(regDate)))
axs.set_xticklabels([key[-2:] for key in keyList])
axs.bar(range(len(regDate)), [regDate[key] for key in keyList],
color=yearCol)
plt.show()
return
def usrRegDate():
dbConn = ma.connect()
tUsers = ma.Table(dbConn, 'tUsers')
tUsers.selectAll()
regDate = {}
for user in tUsers.cursor.fetchall():
yearMonth = '%d%s' % (user[1].year, str(user[1].month).zfill(2))
if yearMonth not in regDate: regDate[yearMonth] = 0
regDate[yearMonth] += 1
ma.disconnect(dbConn)
keyList = regDate.keys()
keyList.sort()
colorLst = ['y', 'm', 'c', 'r', 'g', 'b']
yearLst = list(set([date[:4] for date in keyList]))
yearLst.sort()
yearCol = [colorLst[yearLst.index(date[:4])] for date in keyList]
axs = plt.subplot()
axs.set_xticks(range(len(regDate)))
axs.set_xticklabels([key[-2:] for key in keyList])
axs.bar(range(len(regDate)), [regDate[key] for key in keyList], color=yearCol)
plt.show()
return
def smmry(n=2,
v=1e-08,
fromYear=2015,
toDate='31-12-2999',
shw=True,
ret=True,
xLast=20):
with ma.connect() as dbConn:
with ma.Cursor(dbConn) as crsr:
# Att!! I'm currently not taking into account hiden reports neither B.status={1:'public', 0:'flagged', -1:'hidden'} to score
if fromYear == None:
sql = """SELECT A.user_id, A.hide, A.type, A.server_upload_time, A.location_choice, A.current_location_lon, A.current_location_lat, A.selected_location_lon, A.selected_location_lat, B.report_id, B.user_id as expert_id, B.status, B.tiger_certainty_category, B.site_certainty_category FROM tigaserver_app_report AS A RIGHT JOIN tigacrafting_expertreportannotation AS B ON A."version_UUID" = B.report_id ORDER by A.server_upload_time DESC; """
crsr.execute(sql)
else:
sql = """SELECT A.user_id, A.type, A.hide, A.server_upload_time, A.location_choice, A.current_location_lon, A.current_location_lat, A.selected_location_lon, A.selected_location_lat, B.report_id, B.user_id as expert_id, B.status, B.tiger_certainty_category, B.site_certainty_category FROM tigaserver_app_report as A RIGHT JOIN tigacrafting_expertreportannotation AS B ON A."version_UUID" = B.report_id WHERE (extract(year from A.server_upload_time) >= (%s) AND A.server_upload_time <= (%s)) ORDER by A.server_upload_time DESC; """
crsr.execute(sql, (fromYear, toDate))
startTime = datetime.now()
dSmmry = Dsmmry(n=n, v=v, xLast=xLast)
while crsr.rownumber < crsr.rowcount:
iRow = crsr.rowInstance()
# ignore flagged reports (status == 0) and/or mission reports
if iRow['status'] == 0 or iRow['type'] == 'mission':
continue
uuid = iRow['user_id']
rprtId = iRow['report_id']
# check/add user in dSmmry
if uuid not in dSmmry.keys():
dSmmry[uuid] = Usmmry(uuid)
# check/add report in uSmmry
if rprtId not in dSmmry[uuid].rprtDict.keys():
dSmmry[uuid].rprtDict[rprtId] = Report(rprtId, iRow)
# add report label
dSmmry[uuid].rprtDict[rprtId].addLbl(iRow)
if shw:
dSmmry.smmryTime = datetime.now() - startTime
dSmmry.smmry0()
dSmmry.catPrior()
dSmmry.expCond()
dSmmry.rank_get()
if ret: return dSmmry
def smmry(n=2, v=1e-08, fromYear=2015, toDate='31-12-2999', shw=True, ret=True, xLast=20):
with ma.connect() as dbConn:
with ma.Cursor(dbConn) as crsr:
# Att!! I'm currently not taking into account hiden reports neither B.status={1:'public', 0:'flagged', -1:'hidden'} to score
if fromYear == None:
sql = """SELECT A.user_id, A.hide, A.type, A.server_upload_time, A.location_choice, A.current_location_lon, A.current_location_lat, A.selected_location_lon, A.selected_location_lat, B.report_id, B.user_id as expert_id, B.status, B.tiger_certainty_category, B.site_certainty_category FROM tigaserver_app_report AS A RIGHT JOIN tigacrafting_expertreportannotation AS B ON A."version_UUID" = B.report_id ORDER by A.server_upload_time DESC; """
crsr.execute(sql)
else:
sql = """SELECT A.user_id, A.type, A.hide, A.server_upload_time, A.location_choice, A.current_location_lon, A.current_location_lat, A.selected_location_lon, A.selected_location_lat, B.report_id, B.user_id as expert_id, B.status, B.tiger_certainty_category, B.site_certainty_category FROM tigaserver_app_report as A RIGHT JOIN tigacrafting_expertreportannotation AS B ON A."version_UUID" = B.report_id WHERE (extract(year from A.server_upload_time) >= (%s) AND A.server_upload_time <= (%s)) ORDER by A.server_upload_time DESC; """
crsr.execute(sql, (fromYear, toDate))
startTime = datetime.now()
dSmmry = Dsmmry(n=n, v=v, xLast=xLast)
while crsr.rownumber < crsr.rowcount:
iRow = crsr.rowInstance()
# ignore flagged reports (status == 0) and/or mission reports
if iRow['status'] == 0 or iRow['type'] == 'mission':
continue
uuid = iRow['user_id']
rprtId = iRow['report_id']
# check/add user in dSmmry
if uuid not in dSmmry.keys():
dSmmry[uuid] = Usmmry(uuid)
# check/add report in uSmmry
if rprtId not in dSmmry[uuid].rprtDict.keys():
dSmmry[uuid].rprtDict[rprtId] = Report(rprtId, iRow)
# add report label
dSmmry[uuid].rprtDict[rprtId].addLbl(iRow)
if shw:
dSmmry.smmryTime = datetime.now() - startTime
dSmmry.smmry0()
dSmmry.catPrior()
dSmmry.expCond()
dSmmry.rank_get()
if ret: return dSmmry
def detail(self):
with ma.connect() as dbConn:
with ma.Cursor(dbConn) as crsr:
sql = """SELECT A.hide, A.type, A.mission_id, A.location_choice, A.current_location_lon, A.current_location_lat, A.selected_location_lon, A.selected_location_lat, B.report_id, B.user_id as expert_id, B.status, B.tiger_certainty_category, B.site_certainty_category FROM tigaserver_app_report as A RIGHT JOIN tigacrafting_expertreportannotation AS B ON A."version_UUID" = B.report_id WHERE (A.user_id = (%s) AND B.validation_complete = TRUE) ORDER by A.server_upload_time; """
crsr.execute(sql, (self.uuid, ))
crsr.browse()
def image_list():
(conn, cur) = ma.connect()
t = ma.Table('tREPIMG', cur)
t.rows = t.selectAll(cur)
ma.disconnect(conn, cur)
return [row[-1] for row in t.rows]
def image_list():
(conn, cur) = ma.connect()
t = ma.Table('tREPIMG', cur)
t.rows = t.selectAll(cur)
ma.disconnect(conn, cur)
return [row[-1] for row in t.rows]
def detail(self):
with ma.connect() as dbConn:
with ma.Cursor(dbConn) as crsr:
sql = """SELECT A.hide, A.type, A.mission_id, A.location_choice, A.current_location_lon, A.current_location_lat, A.selected_location_lon, A.selected_location_lat, B.report_id, B.user_id as expert_id, B.status, B.tiger_certainty_category, B.site_certainty_category FROM tigaserver_app_report as A RIGHT JOIN tigacrafting_expertreportannotation AS B ON A."version_UUID" = B.report_id WHERE (A.user_id = (%s) AND B.validation_complete = TRUE) ORDER by A.server_upload_time; """
crsr.execute(sql, (self.uuid,))
crsr.browse()
def smmry(xLast=20, n=10, v=0.0002, fromyear=2015, toyear=3000, ret=True):
with ma.connect() as dbConn:
with ma.Cursor(dbConn) as crsr:
# Att!! I'm currently not taking into account hiden reports neither B.status={1:'public', 0:'flagged', -1:'hidden'} to score
if fromyear == None:
sql = """SELECT A.user_id, A.hide, A.type, A.location_choice, A.current_location_lon, A.current_location_lat, A.selected_location_lon, A.selected_location_lat, B.report_id, B.user_id as expert_id, B.status, B.tiger_certainty_category, B.site_certainty_category FROM tigaserver_app_report AS A RIGHT JOIN tigacrafting_expertreportannotation AS B ON A."version_UUID" = B.report_id ORDER by A.server_upload_time; """
crsr.execute(sql)
else:
sql = """SELECT A.user_id, A.type, A.hide, A.location_choice, A.current_location_lon, A.current_location_lat, A.selected_location_lon, A.selected_location_lat, B.report_id, B.user_id as expert_id, B.status, B.tiger_certainty_category, B.site_certainty_category FROM tigaserver_app_report as A RIGHT JOIN tigacrafting_expertreportannotation AS B ON A."version_UUID" = B.report_id WHERE (extract(year from A.server_upload_time) >= (%s) AND extract(year from A.server_upload_time) < (%s)) ORDER by A.server_upload_time; """
crsr.execute(sql, (fromyear, toyear))
startTime = datetime.now()
dSmmry = Dsmmry(xLast=xLast, n=n, v=v)
while crsr.rownumber < crsr.rowcount:
iRow = crsr.rowInstance()
uuid = iRow['user_id']
rprtId = iRow['report_id']
# check/add user in dSmmry
if uuid not in dSmmry.keys():
dSmmry[uuid] = Usmmry(uuid)
# check/add report in uSmmry
if rprtId not in dSmmry[uuid].rprtDict.keys():
dSmmry[uuid].rprtDict[rprtId] = Report(
rprtId, iRow['type'])
# get report label
rprtLbl = dSmmry[uuid].rprtDict[rprtId].getLbl(iRow)
# add report label
dSmmry[uuid].rprtDict[rprtId].addLbl(rprtLbl,
iRow['expert_id'])
# ignore flagged reports (status == 0)
if iRow['status'] == 0:
dSmmry[uuid].flaggedRprts += 1
continue
# update reports counts
if iRow['type'] == 'adult':
dSmmry[uuid].adultRprts[rprtLbl] += 1
dSmmry[uuid].xLastRprts.append(rprtLbl)
elif iRow['type'] == 'site':
dSmmry[uuid].bSiteRprts[rprtLbl] += 1
dSmmry[uuid].xLastRprts.append(rprtLbl + 7)
elif iRow['type'] == 'mission':
dSmmry[uuid].missionRprts += 1
# update user_report_coordinates list
if iRow['type'] != 'mission':
# do not take into account hidden reports here
if not iRow['hide'] and iRow['status'] == 1:
if iRow['location_choice'] == 'current':
dSmmry[uuid].addCoord(
(iRow['current_location_lon'],
iRow['current_location_lat']))
elif iRow['location_choice'] == 'selected':
dSmmry[uuid].addCoord(
(iRow['selected_location_lon'],
iRow['selected_location_lat']))
# check xLastRprts length is not greater than x
if len(dSmmry[uuid].xLastRprts) > dSmmry.xLast:
dSmmry[uuid].xLastRprts.pop()
dSmmry.smmryTime = datetime.now() - startTime
dSmmry.smmry0()
dSmmry.rank_get()
if ret: return dSmmry