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