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 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 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