def getexporttechnicalSupport():
try:
startdate = request.args["startdate"]
enddate = (
datetime.datetime.strptime(request.args["enddate"], "%Y-%m-%d") +
datetime.timedelta(days=1)).strftime("%Y-%m-%d")
print(startdate, type(startdate))
print(enddate, type(enddate))
#startdate = '2020-2-25'
#enddate = '2020-2-26'
day1 = time.strptime(startdate, '%Y-%m-%d')
day2 = time.strptime(enddate, '%Y-%m-%d')
daynum = (int(time.mktime(day2)) - int(time.mktime(day1))) / (24 * 60 *
60)
print(abs(int(daynum)), type(abs(int(daynum))))
if (abs(int(daynum)) > 30):
response = {"success": "false", "msg": "日期间隔请小于30天~"}
else:
db = "jira"
#sql = "select t2.SUMMARY,t2.CREATOR,t2.ASSIGNEE,t4.pname,t3.customvalue,t2.CREATED from (select ISSUE,STRINGVALUE from customfieldvalue where CUSTOMFIELD = 17807 and STRINGVALUE in ('20565','20566','19626','19630','20584','20568','19627','19628','19631','19633','19632','19634','19635','19636','19884')) t1 left join jiraissue t2 on t1.ISSUE=t2.ID left join customfieldoption t3 on t1.STRINGVALUE=t3.ID left join issuestatus t4 on t4.ID=t2.issuestatus where t2.PROJECT =13117 and t2.issuetype =14806 and t2.CREATED between '%s' and '%s' order by t2.CREATED"%(startdate,enddate)
sql = "select tmp1.SUMMARY, case when tuser1.name_cn is null then tmp1.CREATOR else tuser1.name_cn end as CREATOR, case when tuser2.name_cn is null then tmp1.ASSIGNEE else tuser2.name_cn end as ASSIGNEE, tmp1.pname, tmp1.CREATED, tmp1.customvalue from (select t2.SUMMARY, t2.CREATOR, t2.ASSIGNEE, t4.pname, t2.CREATED, t3.customvalue from (select ISSUE, STRINGVALUE from customfieldvalue where CUSTOMFIELD = 17807 and STRINGVALUE in ('20565', '20566', '19626', '19630', '20584', '20568', '19627', '19628', '19631', '19633', '19632', '19634', '19635', '19636', '19884')) t1 left join jiraissue t2 on t1.ISSUE = t2.ID left join customfieldoption t3 on t1.STRINGVALUE = t3.ID left join issuestatus t4 on t2.issuestatus = t4.ID where t2.PROJECT = 13117 and t2.issuetype = 14806 and t2.CREATED between '%s' and '%s' order by t2.CREATED) tmp1 left join t_oa_user tuser1 on tmp1.CREATOR = tuser1.name_en left join t_oa_user tuser2 on tmp1.ASSIGNEE = tuser2.name_en" % (
startdate, enddate)
fromSql = queryFromSql(db, sql)
list = fromSql.sqlToList()
#print(list)
response = {"success": "true", "msg": "OK", "data": list}
except Exception as e:
raise e
response = {"success": "false", "msg": "查询失败~~"}
response = json.dumps(response, ensure_ascii=False)
return response
def oa_member():
'''
建立部门人员名称带不带数字的对应关系 希望一个部门不要有重名的哈哈😀
:return:
'''
sql = "select name_cn from t_oa_user where dept_fir = '系统产品研发BU'"
d = queryFromSql('jira', sql).querySql()
member = {}
for i in d[1:]:
for y in i:
res = y.translate(remove_digits)
member[res] = y
return member
def jajj(data):
page = Page(layout=Page.SimplePageLayout)
map = None
for i in data:
title = i['title']
db = i['db']
sql = i['sql']
type = i['type']
d_soure = queryFromSql(db, sql, 0).toechart()
y_bar_list = []
y_line_list = []
x_list = []
bar_line_list = []
for i in range(len(d_soure)):
try:
if type[i] == 0:
x = d_soure[i][1:]
x_list.append(x)
elif type[i] == 1:
y = {d_soure[i][0]: d_soure[i][1:]}
y_bar_list.append(y)
elif type[i] == 2:
y = {d_soure[i][0]: d_soure[i][1:]}
y_line_list.append(y)
try:
bar = bar_datazoom_slider(x_list, y_bar_list, title)
except Exception as e:
logging.exception(e)
try:
if y_line_list != []:
line = line_markpoint(x_list, y_line_list, title)
except Exception as e:
logging.exception(e)
finally:
if 2 in type:
map = bar.overlap(line)
else:
map = bar
except Exception as e:
logging.exception(e)
page.add(map)
return Markup(page.render_embed())
def getEpicinfo():
try:
infos=[]
db="jira"
#select * from project_key where project_key in ('FIN','MST','MID','TNTMC','TWEEKER')
sql="select distinct b.id,b.reporter,b.issuenum,b.summary,c.pname,d.project_key,count(b.id) as cnt from issuelink a, jiraissue b, issuestatus c,project_key d where linktype=10200 and issuetype=10000 and a.source=b.id and project in (20505) and project=PROJECT_ID and b.issuestatus=c.id group by(b.id) order by b.id desc limit 30"
fromSql=queryFromSql(db,sql)
list=fromSql.sqlToList()
# topiclist = ['系统产品研发-应用']
for i in list:
status=foo(i["pname"] )
# sql1="select sum(a. numbervalue) as sumpoint from customfieldvalue a where CUSTOMFIELD = 10408 and issue in (select id from jiraissue where id in(select destination from issuelink where linktype=10200 and SOURCE =%d))"%(int(i["id"]))
# fromSql = queryFromSql(db, sql1)
# list1 = fromSql.sqlToList()
# for j in list1:
# print("aa========================================")
# point=list1[0]["sumpoint"]
# if point=="":
# sumpoint=0
# else:
# sumpoint=point
# teamtopic = topiclist[0]
# if i["project_key"]== 'FIN':
# teamtopic =topiclist[0]
# elif i["project_key"]== 'MID':
# teamtopic =topiclist[1]
# else :
# teamtopic =topiclist[2]
infos.append({'value': i["id"],'label':i["summary"] ,'status':status,'issuenum':i['issuenum'],'project_key':i["project_key"],'reporter':i["reporter"],'cnt':i["cnt"]})
# print (infos+'\n'+infos1+'\n'+infos2)
response = {"success": "true", "msg": "OK", "data": infos}
print(response)
except Exception as e:
raise e
response = {"success": "false", "msg": "查询失败~~"}
response =jsonify (response)
return response
def getEpicxmind():
epic = request.args['epic']
print(epic)
id= int(request.args['id'] )
print(id)
issuenum= request.args['issuenum']
print(issuenum)
project_key=request.args['project_key']
print(project_key)
infos = []
try:
uni=0
preVer=""
preteamName=""
preId=""
db="jira"
sql="select a.id,b.stringvalue ,c.name, issuenum,project,d.project_key,reporter,issuetype,summary,issuestatus,e.pname from jiraissue a left join customfieldvalue b on a.id =b.issue and b.customfield=10300 left join AO_60DB71_SPRINT c on b.stringvalue=c.id join project_key d on a.project=d.project_id join issuestatus e on issuestatus=e.id where a.id in(select destination from issuelink where linktype=10200 and SOURCE=%d) order by stringvalue desc"%(id)
fromSql=queryFromSql(db,sql)
list=fromSql.sqlToList()
url = "https://jira.tuniu.org/browse/" + project_key + "-" + issuenum
print(url)
infos.append({'id': 'root', 'isroot': 'True', 'topic': epic,'url':url})
for i in list:
url = "https://jira.tuniu.org/browse/" + i["project_key"] + "-" + i["issuenum"]
# print(url)
if i["name"]!="":
name=i["name"].replace("-","_")
name = name.replace(" ", "_")
nameArr=name.split("_",2)
ver=nameArr[0] #版本
teamName=nameArr[1] #团队名称
issuenum=str(i["issuenum"])
verId = i["stringvalue"]
teamId = verId + teamName
if ver!=preVer:
infos.append({'id': verId, 'parentid': 'root', 'topic': ver,'url':""})
infos.append({'id': teamId, 'parentid': verId, 'topic': teamName,'url':""})
infos.append({'id': issuenum, 'parentid': teamId, 'topic': i["pname"]+"-"+i["summary"],'url':url})
else:
if teamName !=preteamName:
infos.append({'id': teamId, 'parentid': preId, 'topic': teamName,'url':""})
infos.append({'id': issuenum, 'parentid': teamId, 'topic': i["pname"]+"-"+i["summary"],'url':url})
else:
infos.append({'id': issuenum, 'parentid': teamId, 'topic': i["pname"]+"-"+i["summary"],'url':url})
preVer=ver
preteamName=teamName
preId=verId
else :
if uni ==0:
infos.append({'id': 'sub1', 'parentid': 'root', 'topic': '未排期','url':"", 'direction': 'right', 'expanded': 'False',
'background-color': '#0000ff'})
infos.append({'id': i["issuenum"], 'parentid': 'sub1', 'topic': i["pname"]+"-"+i["summary"], 'url': url})
uni=1
else:
infos.append({'id': i["issuenum"], 'parentid': 'sub1', 'topic': i["pname"]+"-"+i["summary"],'url':url})
response = {"success": "true", "msg": "OK", "data": infos}
print(response)
except Exception as e:
raise e
response = {"success": "false", "msg": "查询失败~~"}
response =jsonify (response)
return response
data = ''
con1 = ''
for i in args:
data = ''
db = i['db']
total = 0
for x in i['sqlList']:
sql = x['sql']
desc = x['desc']
con1 = ''
if BUILD_URL == '':
title = "{}{}{}".format('**', desc, '**') + '\n'
else:
title = f"##### [{desc}]({BUILD_URL}parameters) \n"
d = queryFromSql(db, sql).querySql()
total += len(d)
'''
#感觉应该先判断行 但是之前没有考虑行 后边可以优化噻 后边考虑如果没有数据 把title也不要了 101行
列名1 列名2
1 2
如果行数>1 列>2 显示从第一行开始
列<=2 显示从第二行开始
行数<=1 即 行数=1 仅有列名行 不显示
'''
# 如果大于2列 显示列名 否则不显示
# if len(d[0]) > 2:
# for k in d:
# # 如果大于1行显示列名 否则不显示
def jira_tuniu_test(start_time, end_time):
#自定义时间：
#start_time = '2019-08-09'
#end_time = '2019-08-15'
#start_time, end_time, start, end, day = timer.time_l()
year = int(end_time.split('-')[0])
month = int(end_time.split('-')[1])
day = int(end_time.split('-')[2])
week = str(datetime.date(year, month, day).isocalendar()[1]) + 'W'
db = 'jira'
excelWriter = pd.ExcelWriter('OnlineBU'+'-'+week+'.xlsx', engine='openpyxl')
# 只是为了部门在第一个sheet
l = pd.DataFrame(['', ''])
l.to_excel(excelWriter, sheet_name='部门人员统计')
# 查询worklog表中数据
sql = f'''
SELECT b.name_cn AS 姓名, b.dept_sec AS 组织架构, STR_TO_DATE(STARTDATE, '%Y-%m-%d') AS 日期
,a.timeworked / 3600 / 8 AS 工时
, CONCAT(d.pkey, '-', c.issuenum) AS 单号
, e.pname AS 类型, c.id, g.LINKNAME
, IF(e.pname IN ('技术需求', '业务需求', '新技术支持'), c.id, f.SOURCE) AS SOURCE
FROM worklog a
LEFT JOIN t_oa_user b ON a.AUTHOR = b.name_en
LEFT JOIN jiraissue c ON c.id = a.issueid
LEFT JOIN project d ON c.PROJECT = d.ID
LEFT JOIN issuetype e ON c.issuetype = e.id
LEFT JOIN issuelink f ON f.DESTINATION = c.id
LEFT JOIN issuelinktype g ON f.LINKTYPE = g.id
WHERE b.dept_fir = '系统产品研发BU'
AND b.dept_sec in ("研发TeamI","研发TeamJ","研发TeamK","研发TeamL","研发TeamM","设计TeamN","产品TeamP","质量TeamS")
AND e.pname IN ('子事务', 'Sub-task')
AND g.id = 10100
-- 取jira为('子事务', 'Sub-task') 链接类型是jira_subtask_link
AND STARTDATE BETWEEN '{start_time}' AND '{end_time}'
UNION ALL
SELECT b.name_cn AS 姓名, b.dept_sec AS 组织架构, STR_TO_DATE(STARTDATE, '%Y-%m-%d') AS 日期
, a.timeworked / 3600 / 8 AS 工时
, CONCAT(d.pkey, '-', c.issuenum) AS 单号
, e.pname AS 类型, c.id, g.LINKNAME
, IF(e.pname IN ('技术需求', '业务需求', '新技术支持'), c.id, f.SOURCE) AS SOURCE
-- 限制父级jira在('技术需求', '业务需求', '新技术支持')
FROM worklog a
LEFT JOIN t_oa_user b ON a.AUTHOR = b.name_en
LEFT JOIN jiraissue c ON c.id = a.issueid
LEFT JOIN project d ON c.PROJECT = d.ID
LEFT JOIN issuetype e ON c.issuetype = e.id
LEFT JOIN issuelink f ON f.DESTINATION = c.id
LEFT JOIN issuelinktype g ON f.LINKTYPE = g.id
WHERE b.dept_fir = '系统产品研发BU'
AND b.dept_sec in ("研发TeamI","研发TeamJ","研发TeamK","研发TeamL","研发TeamM","设计TeamN","产品TeamP","质量TeamS")
AND e.pname NOT IN ('子事务', 'Sub-task')
AND STARTDATE BETWEEN '{start_time}' AND '{end_time}'
-- 取其他jira 此时不限制
'''
data = queryFromSql(db, sql).querySql()
res = pd.DataFrame(data[1:], columns=data[0])
ids = res['SOURCE']
ids = ids.tolist()
l = list(set(ids))
ids = ''
for i in l:
if i != "":
ids += i + ','
ids = ids.strip().strip(',')
# 查询jra父级相关属性或本身 ('业务需求','技术需求','新技术支持')属性
sql_2 = f'''
SELECT distinct c.id AS SOURCE, CONCAT(d.pkey, '-', c.issuenum) AS 父级jira
, e.pname AS 类型
-- , f.SOURCE 父级id
, c.SUMMARY 主题
, c.DESCRIPTION 描述,
(SELECT b.name_cn FROM customfieldvalue af left join t_oa_user b on b.name_en = af.STRINGVALUE WHERE af.ISSUE=c.ID AND CUSTOMFIELD=10023) AS 需求提出人,
(SELECT cfd.customvalue FROM customfieldvalue af LEFT JOIN customfieldoption cfd ON cfd.id=af.STRINGVALUE WHERE af.ISSUE=c.ID AND af.CUSTOMFIELD=17905) AS 需求提出部门,
IFNULL((SELECT cfop.customvalue FROM customfieldvalue cfvp,customfieldoption cfop WHERE c.ID= cfvp.ISSUE AND cfvp.CUSTOMFIELD = 12100 AND cfop.ID = cfvp.STRINGVALUE),'未立项()') AS 立项项目名称,
(SELECT STRINGVALUE FROM customfieldvalue af WHERE af.ISSUE = c.ID AND CUSTOMFIELD = 10304) Epic
FROM jiraissue c
LEFT JOIN worklog a ON c.id = a.issueid
LEFT JOIN t_oa_user b ON a.AUTHOR = b.name_en
LEFT JOIN project d ON c.PROJECT = d.ID
LEFT JOIN issuetype e ON c.issuetype = e.id
LEFT JOIN issuelink f ON f.DESTINATION = c.id
WHERE c.id in ({ids}) AND e.pname in ('业务需求','技术需求','新技术支持')
'''
# 序号 ID LINKNAME INWARD OUTWARD pstyle
# 1 10000 Blocks is blocked by blocks
# 2 10001 Cloners is cloned by clones
# 3 10002 Duplicate is duplicated by duplicates
# 4 10003 Relates relates to relates to
# 5 10100 jira_subtask_link jira_subtask_inward jira_subtask_outward jira_subtask
# 6 10200 Epic-Story Link has Epic is Epic of jira_gh_epic_story
data_2 = queryFromSql(db, sql_2).querySql()
res_2 = pd.DataFrame(data_2[1:], columns=data_2[0])
df = pd.DataFrame.merge(res, res_2, how='left', on='SOURCE')
# 取所有的填写工时的人
members = tuple(set(df['姓名']))
# 工时列 字符串转数字
df[['工时']] = df[['工时']].apply(pd.to_numeric)
df.to_excel(excelWriter, sheet_name='明细', index=None)
excelWriter.save()
#print('df11111====',type(df),df)
# 从excel中取数据
wb2 = openpyxl.load_workbook('OnlineBU'+'-'+week+'.xlsx')
sheet = wb2["明细"]
# excel 行数 列数
max_column = sheet.max_column
max_row = sheet.max_row
# 查询部门当中时间段内未填写工时的人员
sql_mem_0 = f'''
select name_cn as 姓名,dept_sec as 组织架构 from t_oa_user where dept_fir ='系统产品研发BU' AND dept_sec in ("研发TeamI","研发TeamJ","研发TeamK","研发TeamL","研发TeamM","设计TeamN","产品TeamP","质量TeamS") and level_cn !='已离职'
and name_cn not in {members}
order by dept_sec, name_cn
'''
data = queryFromSql(db, sql_mem_0).querySql()
#print('data1111====',type(data),data)
# 未填写工时的人加入excel
for i in data[1:]:
for y in range(0, len(i)):
sheet.cell(max_row + 1, y + 1).value = i[y]
# 默认立项项目名称 未立项()
sheet.cell(max_row + 1, 16).value = '未立项()'
# 默认工时0
sheet.cell(max_row + 1, 4).value = '0'
max_row += 1
wb2.save('OnlineBU'+'-'+week+'.xlsx')
df = pd.read_excel(excelWriter, sheet_name='明细')
df = df.sort_values(by=['姓名', '日期'])
#print('df22222====',type(df),df)
#print("value=2222===,",df.get_values())
df1 = df.groupby(by=['立项项目名称', '姓名'])['工时'].sum()
df1 = df1.groupby(level=0, group_keys=False).nlargest(200)
df1.to_excel(excelWriter, sheet_name='项目预估与投入填写')
#df.to_excel(excelWriter, sheet_name='明细', index=None)
df2 = df.groupby(by=['组织架构', '姓名'])['工时'].sum()
df2 = df2.groupby(level=0, group_keys=False).nlargest(200)
df2.to_excel(excelWriter, sheet_name='部门人员统计')
excelWriter.save()
excelWriter.close()
return df