def __init__(self):
data = execute(query_by_project)
# print(json.dumps(data))
value = {'epic': '无', 'timeworked': 0}
self.df = pd.DataFrame().from_records(data=data).fillna(value=value)
return
def byepic_month():
month = 0
list = []
listdata = {}
data = execute(query_epicby_month)
# data=json.dumps(info)
# print(json.dumps(data))
for item in data:
if item['month'] != month:
listdata = {'month': item['month']}
listdata.update({item['epic']: item['timeworded']})
list.append(listdata)
else:
listdata.update({item['epic']: item['timeworded']})
month = item['month']
return list
def detail_to_excel():
data = execute(query_by_project)
print(data)
col = []
for i in data:
for k, v in i.items():
col.append(k)
print(col)
break
df = pd.DataFrame(data=data, columns=col)
filepath = 'temp.xlsx'
excelWriter = pd.ExcelWriter(filepath, engine='openpyxl')
df.to_excel(excelWriter)
excelWriter.save()
return
def detail():
'''工时明细'''
data = execute(query_by_project)
return data
LEFT JOIN t_oa_user user ON user.name_en = wl.AUTHOR
WHERE p.pkey IN ('AMID')
AND ji.issuetype IN (12100, 25, 10300)
UNION all
SELECT month(wl.created) as month, concat(pepic.pkey,'-',epic.issuenum,' ', cfv.STRINGVALUE) AS epic
, dept_sec, ifnull(wl.timeworked,0)/28800 as timeworked
FROM jiraissue ji
LEFT JOIN issuelink sub ON sub.DESTINATION = ji.id
LEFT JOIN jiraissue jifa ON jifa.id = sub.SOURCE
LEFT JOIN project p ON p.id = jifa.project
LEFT JOIN issuetype it ON it.id = ji.issuetype
LEFT JOIN issuelink epicl ON epicl.DESTINATION = jifa.id
LEFT JOIN jiraissue epic ON epicl.source = epic.id
left join project pepic on pepic.id =epic.PROJECT
LEFT JOIN customfieldvalue cfv
ON cfv.issue = epic.id
AND cfv.CUSTOMFIELD = 10304
LEFT JOIN worklog wl ON ji.id = wl.issueid
LEFT JOIN t_oa_user user ON user.name_en = wl.AUTHOR
WHERE ji.issuetype IN (10302, 10301, 5)
AND p.pkey IN ('AMID') ) a where month is not null group by month,epic order by month
'''
if __name__ == '__main__':
sql = query_by_project
time1 = time.time()
d = execute(sql)
print(json.dumps(d))
time2 = time.time()
print(time2 - time1)