def createEmotionTable(self): qzoneDB = DB(self.db) qzoneDB.createTable('emotion', ( 'id integer primary key autoincrement', 'name varchar(20)', 'weight varchar(20)', 'datetime varchar(20)' )) qzoneDB.close()
def computeDifficulty(self, jsonRomFileName, preset): randomizedRom = os.path.basename(jsonRomFileName.replace( 'json', 'sfc')) presetFileName = "{}/{}.json".format(getPresetDir(preset), preset) (fd, jsonFileName) = tempfile.mkstemp() db = DB() id = db.initSolver() params = [ getPythonExec(), os.path.expanduser("~/RandomMetroidSolver/solver.py"), '-r', str(jsonRomFileName), '--preset', presetFileName, '--difficultyTarget', str(self.session.solver['difficultyTarget']), '--pickupStrategy', self.session.solver['pickupStrategy'], '--type', 'web', '--output', jsonFileName, '--runtime', '10' ] for item in self.session.solver['itemsForbidden']: params += ['--itemsForbidden', item] db.addSolverParams(id, randomizedRom, preset, self.session.solver['difficultyTarget'], self.session.solver['pickupStrategy'], self.session.solver['itemsForbidden']) print("before calling solver: {}".format(params)) start = datetime.now() ret = subprocess.call(params) end = datetime.now() duration = (end - start).total_seconds() print("ret: {}, duration: {}s".format(ret, duration)) if ret == 0: with open(jsonFileName) as jsonFile: result = json.load(jsonFile) else: result = "Solver: something wrong happened while solving the ROM" db.addSolverResult(id, ret, duration, result) db.close() os.close(fd) os.remove(jsonFileName) return (ret == 0, result)
def updateEmotion(self): timestamp, lastTimestamp = self.getUpdateTimestamp() url = 'http://taotao.qq.com/cgi-bin/emotion_cgi_msglist_v6' params = { 'uin': self.user, 'pos': 0, 'num': 20, 'g_tk': Qzone.getCSRFToken(self.session.cookies.get('skey')), 'code_version': 1, 'format': 'json' } total, msgList, content, createdTime = None, None, None, None qzoneDB = DB(self.db) while True: res = self.session.get(url, params=params) data = res.json() total = data['total'] msgList = data['msglist'] for all in msgList: content = all['content']; createdTime = all['created_time'] if int(createdTime) < int(lastTimestamp): return qzoneDB.close() if int(createdTime) < int(timestamp) and int(createdTime) > int(lastTimestamp) \ and re.match(r'^~?\d{2}\.?\d?$', content): # print(content, '#', createdTime) qzoneDB.insert('emotion', { 'name': str(self.user), 'weight': content, 'datetime': createdTime }) # when to break loop if total < params['num'] or total < params['pos'] + params['num']: return qzoneDB.close() params['pos'] = params['pos'] + params['num']
def createQzoneDB(self): qzoneDB = DB(self.db) qzoneDB.close()
class DbSearch(): def __init__(self): print('connect') self.connect = DB().conn() def search_proj_before_bug(self): PROJ_BEFORE_BUG = {} title = '查询项目纳入统计之前的新建bug数' print(title.center(40, '-')) cur = self.connect.cursor() sql = 'select * from wbg_proj_before_bug' cur.execute(sql) result = cur.fetchall() print(result) for i in result: PROJ_BEFORE_BUG[i[0]] = i[1] print("项目纳入统计之前的新建bug数:" ,PROJ_BEFORE_BUG) return PROJ_BEFORE_BUG # 统计当前周数 def week(self): cur = self.connect.cursor() sql = 'select last_week from wbg_week where id = 0' cur.execute(sql) week = cur.fetchall()[0][0] print("当前周数:", week) # print(type(week)) self.connect.close() return week # 查询是否为新增项目(查询wbg_history_data表中的上周数据是否有该proj的值) def search_new_project(self,PROJ_DICT,WEEK,NEW_PROJECT): title = "查询新增项目" print(title.center(40, '-')) cur = self.connect.cursor() for proj in PROJ_DICT: sql = 'select project from wbg_history_data where week_num = %(week_num)s and project = %(project)s' # week_num = str(self.WEEK-1)+'周' value = {'week_num': str(WEEK - 1) + '周', 'project': proj} # print(self.WEEK) # print(type(self.WEEK)) cur.execute(sql, value) projDate = cur.fetchone() if not projDate: NEW_PROJECT.append(proj) print('新增项目查询结束'.center(40, '-')) print("新增项目列表:", NEW_PROJECT) return NEW_PROJECT # 查询并计算当前周bug总数 def search_current_week_totalBugNum(self, proj): title = "查询当前%s项目当前周bug总数" % proj print(title.center(40, '-')) cur = self.connect.cursor() sql = 'select year,month,build_bug_num from wbg_year_sta where project = %(proj)s' value = {'proj': proj} cur.execute(sql, value) result = cur.fetchall() print(result) total_num = 0 for i in result: if i[2]: total_num += i[2] print('-------', total_num) return total_num # 查询wbg_history_data某项目所有历史数据,返回成列表,用来绘制首页图表 def search_allDate_history(self,PROJ_DICT): # 查询某项目某周数据 title = "查询当前所有项目所有历史数据" print(title.center(40, '-')) cur = self.connect.cursor() sql = 'select project,week_num,bug_leave_num,bug_total_num,bug_leave_rate,bug_add_num,bug_solve_num from wbg_history_data where del_flag is NULL ' # value = {'proj': 'BIM'} cur.execute(sql) result = cur.fetchall() # print(result) resultDict = {} for proj in PROJ_DICT: resultDict[proj] = {'wkn': [], 'bln': [], 'btn': [], 'blr': [], 'ban': [], 'bsn': []} for date in result: if date[0] in resultDict: resultDict[date[0]]['wkn'].append(date[1][0:-1]) # 周数列表 resultDict[date[0]]['bln'].append(int(date[2])) # bug遗留数列表 resultDict[date[0]]['btn'].append(int(date[3])) # bug总数列表 resultDict[date[0]]['blr'].append(float(date[4][0:5]) * 100) # bug遗留率 resultDict[date[0]]['ban'].append(int(date[5])) # bug新增数 resultDict[date[0]]['bsn'].append(int(date[6])) # bug解决数 # print(resultDict) return resultDict # 在wbg_history_data中根据周数查询每周历史数据 def search_weekDate_history(self, weekNum, proj=None, ): # 查询某项目某周数据 title = "查询当前%s项目%s周历史数据" % (proj, weekNum) print(title.center(40, '-')) cur = self.connect.cursor() sql = 'select project,week_num,statis_time,bug_leave_num,bug_total_num from wbg_history_data where week_num = %(week_num)s' if proj != None: sql = 'select project,week_num,statis_time,bug_leave_num,bug_total_num from wbg_history_data where (week_num = %(week_num)s and project=%(proj)s)' value = {'week_num': weekNum, 'proj': proj} # print(sql) cur.execute(sql, value) weekData = cur.fetchall() # print(weekData) print(weekData) return weekData
def createDateBase(): db = DB(app.config['DATABASE']) db.close()
def write_Proj_sheet(workbook, PROJ_DICT, PROJ_BEFORE_BUG, LEAVE_BUG): YEAR = time.strftime('%Y', time.localtime(time.time())) # MONTH = time.strftime('%m', time.localtime(time.time())) # WEEK = DbSearch().week() # 当前周 connect = DB().conn() PROJ_DICT_sorted = sorted(PROJ_DICT.items(), key=lambda PROJ_DICT: PROJ_DICT[1]['index']) for proj in PROJ_DICT_sorted: proj = proj[0] title = "开始编写【%s】工作表" % PROJ_DICT[proj]['sheet_name'] print(title.center(40, '=')) worksheet = workbook.add_worksheet(PROJ_DICT[proj]['sheet_name']) worksheet.hide_gridlines(option=2) # 隐藏网格线 style = Styles(workbook).style_of_cell() style_title = Styles(workbook).style_of_cell('14') # style_title2 = style_of_cell('noBold') # style_bold = style_of_cell('bold') sql = "select year from wbg_year_sta where project=%(proj)s" value = {'proj': proj} result = DB().search(sql, value, connect) # print(result) yearList = [] # [2017,2018,2019] for i in result: if int(i[0]) not in yearList: yearList.append(int(i[0])) yearList.sort() print(yearList) worksheet.write('B2', PROJ_DICT[proj]['sheet_name'], style_title) worksheet.write(3, 1, '年份', style) worksheet.write(3, 2, '遗留BUG数', style) worksheet.write(3, 3, '新建BUG总数', style) worksheet.write(3, 4, '遗留率', style) r = len(yearList) for index in range(r): worksheet.write(4 + index, 1, yearList[index], style) sql = "select year,leave_bug_num,build_bug_num from wbg_year_sta where project=%(proj)s and year=%(year)s" value = {'year': yearList[index], 'proj': proj} result = DB().search(sql, value, connect) lbn = 0 nbn = 0 for i in result: lbn += i[1] nbn += i[2] worksheet.write(4 + index, 2, lbn, style) if proj in PROJ_BEFORE_BUG: nbn += PROJ_BEFORE_BUG[proj] worksheet.write(4 + index, 3, nbn, style) if nbn == 0: worksheet.write(4 + index, 4, 0, style) else: worksheet.write(4 + index, 4, format(int(lbn) / int(nbn), '.1%'), style) style_1 = Styles(workbook).style_of_cell(1) # 本年bug状况 worksheet.merge_range(5 + r, 1, 5 + r, 3, '%s年BUG状况' % YEAR, style_1) worksheet.write(6 + r, 1, '月份', style) worksheet.write(6 + r, 2, '遗留BUG数', style) worksheet.write(6 + r, 3, '新建BUG总数', style) worksheet.write(6 + r, 4, '遗留率', style) sql = "select month,leave_bug_num,build_bug_num from wbg_year_sta where project=%(proj)s and year=%(year)s" value = {'proj': proj, 'year': YEAR} result = DB().search(sql, value, connect) # print(result) monthdict = {} for i in result: monthdict[int(i[0])] = (i[1], i[2]) print(monthdict) row = len(monthdict) # 9/3 monthdict = sorted(monthdict.items(), key=lambda x: x[0]) # currentMonth = datetime.datetime.now().month index = 0 for j in monthdict: mlbn = j[1][0] mbbn = j[1][1] month = j[0] index += 1 # # todo # if row == currentMonth: # mlbn = monthdict[str(j + 1)][0] # mbbn = monthdict[str(j + 1)][1] # month = j + 1 # else: # mlbn = monthdict[str(currentMonth - row + 1 + j)][0] # mbbn = monthdict[str(currentMonth - row + 1 + j)][1] # month = currentMonth - row + 1 + j worksheet.write(7 + r + index, 1, month, style) worksheet.write(7 + r + index, 2, mlbn, style) worksheet.write(7 + r + index, 3, mbbn, style) if mbbn == 0: worksheet.write(7 + r + index, 4, '0.0%', style) else: worksheet.write(7 + r + index, 4, format(int(mlbn) / int(mbbn), '.1%'), style) # 遗留率 # bug遗留时效 worksheet.merge_range(row + 8 + r, 1, row + 9 + r, 1, '姓名', style) worksheet.merge_range(row + 8 + r, 2, row + 9 + r, 2, '遗留bug数 ', style) worksheet.merge_range(row + 8 + r, 3, row + 8 + r, 6, 'bug遗留时效 ', style) worksheet.write(row + 9 + r, 3, '一周', style) worksheet.write(row + 9 + r, 4, '一周~二周', style) worksheet.write(row + 9 + r, 5, '二周~一月', style) worksheet.write(row + 9 + r, 6, '一月以上', style) bugData = LEAVE_BUG[proj] print(bugData) del bugData['proj'] del bugData['lbn'] del bugData['bugStatusYear'] r1 = len(bugData) k = 0 total1 = 0 total2 = 0 total3 = 0 total4 = 0 total5 = 0 # for k in range(r): # 列 for name in bugData: # 行 total = bugData[name]['一周'] + bugData[name]['一周~二周'] + bugData[ name]['二周~一月'] + bugData[name]['一月以上'] worksheet.write(row + 10 + r + k, 1, name, style) # 姓名 worksheet.write(row + 10 + r + k, 2, total, style) # 遗留bug数 worksheet.write(row + 10 + r + k, 3, bugData[name]['一周'], style) # 一周 worksheet.write(row + 10 + r + k, 4, bugData[name]['一周~二周'], style) # 一周~二周 worksheet.write(row + 10 + r + k, 5, bugData[name]['二周~一月'], style) # 二周~一月 worksheet.write(row + 10 + r + k, 6, bugData[name]['一月以上'], style) # 一月以上 k += 1 total1 += total total2 += bugData[name]['一周'] total3 += bugData[name]['一周~二周'] total4 += bugData[name]['二周~一月'] total5 += bugData[name]['一月以上'] style_2 = workbook.add_format({ 'bold': True, # 字体加粗 'border': 1, # 单元格边框宽度 'align': 'center', # 水平对齐方式 'valign': 'vcenter', # 垂直对齐方式 # 'fg_color': color, # 单元格背景颜色 'text_wrap': True, # 是否自动换行 'font_size': 11, # 字体 'font_name': u'微软雅黑' }) worksheet.write(row + 10 + r + r1, 1, '汇总', style_2) worksheet.write(row + 10 + r + r1, 2, total1, style_2) worksheet.write(row + 10 + r + r1, 3, total2, style_2) worksheet.write(row + 10 + r + r1, 4, total3, style_2) worksheet.write(row + 10 + r + r1, 5, total4, style_2) worksheet.write(row + 10 + r + r1, 6, total5, style_2) if proj == 'BIM': worksheet.merge_range( row + 11 + r + r1, 1, row + 13 + r + r1, 6, "备注:2018年6月之前采用C++进行编码,此版本已经废弃,但Jira上保留C++编码版本Bug记录,本次统计已排除6月前Bug记录.2018年6月份之前的Bug共计41个", style_1) worksheet.set_column(1, 6, 12) worksheet.set_column(0, 0, 4) # print("新增项目列表:", NEW_PROJECT) print("【%s】工作表编写完毕".center(40, '-') % proj) connect.close()
def webService(self): # web service to compute a new random (returns json string) print("randomizerWebService") # check validity of all parameters switchs = ['suitsRestriction', 'hideItems', 'strictMinors', 'areaRandomization', 'areaLayout', 'lightAreaRandomization', 'doorsColorsRando', 'allowGreyDoors', 'escapeRando', 'removeEscapeEnemies', 'bossRandomization', 'minimizer', 'funCombat', 'funMovement', 'funSuits', 'layoutPatches', 'variaTweaks', 'nerfedCharge', 'itemsounds', 'elevators_speed', 'fast_doors', 'spinjumprestart', 'rando_speed', 'animals', 'No_Music', 'random_music', 'Infinite_Space_Jump', 'refill_before_save', 'hud', "scavRandomized"] quantities = ['missileQty', 'superQty', 'powerBombQty', 'minimizerQty', "scavNumLocs"] multis = ['majorsSplit', 'progressionSpeed', 'progressionDifficulty', 'morphPlacement', 'energyQty', 'startLocation', 'gravityBehaviour'] others = ['complexity', 'paramsFileTarget', 'seed', 'preset', 'maxDifficulty', 'objective', 'tourian'] validateWebServiceParams(self.request, switchs, quantities, multis, others, isJson=True) # randomize db = DB() id = db.initRando() # race mode useRace = False if self.vars.raceMode == 'on': magic = self.getMagic() useRace = True (fd1, presetFileName) = tempfile.mkstemp() presetFileName += '.json' (fd2, jsonFileName) = tempfile.mkstemp() (fd3, jsonRandoPreset) = tempfile.mkstemp() print("randomizerWebService, params validated") for var in self.vars: print("{}: {}".format(var, self.vars[var])) with open(presetFileName, 'w') as presetFile: presetFile.write(self.vars.paramsFileTarget) if self.vars.seed == '0': self.vars.seed = str(random.randrange(sys.maxsize)) preset = self.vars.preset params = [getPythonExec(), os.path.expanduser("~/RandomMetroidSolver/randomizer.py"), '--runtime', '20', '--output', jsonFileName, '--param', presetFileName, '--preset', preset] if useRace == True: params += ['--race', str(magic)] # load content of preset to get controller mapping try: controlMapping = PresetLoader.factory(presetFileName).params['Controller'] except Exception as e: os.close(fd1) os.remove(presetFileName) os.close(fd2) os.remove(jsonFileName) os.close(fd3) os.remove(jsonRandoPreset) return json.dumps({"status": "NOK", "errorMsg": "randomizerWebService: can't load the preset"}) (custom, controlParam) = getCustomMapping(controlMapping) if custom == True: params += ['--controls', controlParam] if "Moonwalk" in controlMapping and controlMapping["Moonwalk"] == True: params.append('--moonwalk') randoPresetDict = {var: self.vars[var] for var in self.vars if var != 'paramsFileTarget'} # set multi select as list as expected in a rando preset for var, value in randoPresetDict.items(): if 'MultiSelect' in var: randoPresetDict[var] = value.split(',') randoPresetDict['objective'] = self.vars.objective.split(',') with open(jsonRandoPreset, 'w') as randoPresetFile: json.dump(randoPresetDict, randoPresetFile) params += ['--randoPreset', jsonRandoPreset] db.addRandoParams(id, self.vars) print("before calling: {}".format(' '.join(params))) start = datetime.now() ret = subprocess.call(params) end = datetime.now() duration = (end - start).total_seconds() print("ret: {}, duration: {}s".format(ret, duration)) if ret == 0: with open(jsonFileName) as jsonFile: locsItems = json.load(jsonFile) # check if an info message has been returned msg = '' if len(locsItems['errorMsg']) > 0: msg = locsItems['errorMsg'] if msg[0] == '\n': msg = msg[1:] locsItems['errorMsg'] = msg.replace('\n', '<br/>') db.addRandoResult(id, ret, duration, msg) if "forcedArgs" in locsItems: db.updateRandoParams(id, locsItems["forcedArgs"]) # store ips in local directory guid = str(uuid.uuid4()) if self.storeLocalIps(guid, locsItems["fileName"], locsItems["ips"]): db.addRandoUploadResult(id, guid, locsItems["fileName"]) locsItems['seedKey'] = guid db.close() os.close(fd1) os.remove(presetFileName) os.close(fd2) os.remove(jsonFileName) os.close(fd3) os.remove(jsonRandoPreset) locsItems["status"] = "OK" return json.dumps(locsItems) else: # extract error from json try: with open(jsonFileName) as jsonFile: msg = json.load(jsonFile)['errorMsg'] if msg[0] == '\n': msg = msg[1:] msg = msg.replace('\n', '<br/>') except: msg = "randomizerWebService: something wrong happened" db.addRandoResult(id, ret, duration, msg) db.close() os.close(fd1) os.remove(presetFileName) os.close(fd2) os.remove(jsonFileName) os.close(fd3) os.remove(jsonRandoPreset) return json.dumps({"status": "NOK", "errorMsg": msg})