def yzl_info():
info = request.form.get('info', '')
user_id = request.form.get('user_id', '')
if not info.startswith("@助手"):
return "回复：不是给我的命令"
info_arr = info.split()
if info_arr[1] == 'list':
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute(
'SELECT id,report_name FROM zhanbao_tbl WHERE worker_no=%s and is_catalog=0 order by xuhao asc',
user_id)
ret = cursor.fetchall()
return '\n'.join([f"{x['id']}_{x['report_name']}" for x in ret])
if is_number(info_arr[1]):
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute(
'SELECT config_txt,report_name FROM zhanbao_tbl WHERE worker_no=%(user_id)s and id=%(id)d and is_catalog=0 order by xuhao asc',
{
"user_id": user_id,
"id": int(info_arr[1])
})
ret = cursor.fetchone()
if ret is None:
return "没有这个ID:" + info_arr[1]
hnclic.tasks.zb_execute.delay(int(info_arr[1]),
json.loads(ret['config_txt']), user_id,
ret['report_name'])
return ret['report_name'] + ",已经开始执行"
return """ @助手 list 可以看到所有自己做的战报ID 和名字
def getMenu(parentId):
if (parentId == '0'):
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute(
'SELECT id,report_name,isnull(pid,0) pid,is_catalog FROM zhanbao_tbl WHERE worker_no=%s order by xuhao asc',
session['userid'])
ret = cursor.fetchall()
ret = [{
'path': f'/zb/zb/{x["id"]}',
'icon': 'el-icon-document',
'component': 'views/zhanbao/zb_index',
'label': x["report_name"],
'id': x["id"],
'pid': x["pid"],
"is_catalog": x['is_catalog'],
'meta': {
'id': x["id"],
'keepAlive': True
}
} for x in ret]
elif parentId == '1':
with glb.db_connect() as conn:
with conn.cursor(
as_dict=True) as cursor: #.encode('latin-1').decode('gbk')
cursor.execute(
"""SELECT m.id, m.name label, m.parent_id pid, m.url res_url, m.icon
FROM [10.20.54.104].localapp.dbo.protal_sys_menu m
LEFT JOIN [10.20.54.104].localapp.dbo.protal_sys_role_menu rm ON (rm.menu_id = m.id)
LEFT JOIN [10.20.54.104].localapp.dbo.protal_sys_user_role ur ON (ur.role_id = rm.role_id)
LEFT JOIN [10.20.54.104].localapp.dbo.protal_sys_user u ON (u.id = ur.user_id)
WHERE u.name = %s""", session['userid'])
ret = cursor.fetchall()
ret = [{
'path': x["res_url"] if x["res_url"] != None else "",
'icon': 'el-icon-document-copy',
'label': x["label"].encode('latin-1').decode('gbk'),
'id': x["id"],
'pid': x["pid"],
'meta': {
'id': x["id"],
'isTab': False
}
} for x in ret]
elif parentId == '2' and session['userid'] == '14100298':
ret = [{
'label': "报表目录",
'icon': 'el-icon-document',
'path': "/rpt-list/index"
}]
else:
ret = []
return jsonify(data=ret)
def test():
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute('SELECT * FROM zhanbao_tbl WHERE id=6817 ')
row = cursor.fetchone()
upload_path=f"{glb.config['UPLOAD_FOLDER']}\\{row['worker_no']}\\{row['id']}"
return load_all_data(json.loads(row['config_txt']),row['id'],upload_path=upload_path,userid=row['worker_no'])
def ReportDefine_list():
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute(
'SELECT id,report_name label FROM zhanbao_tbl WHERE worker_no=%s',
session['userid'])
ret = cursor.fetchall()
return json.dumps(ret, ensure_ascii=False)
def raw_get(id, rawtype, ds_names):
glb.redis.hincrby("zb:tj", 'api')
ds_names = ds_names.split(',')
cur = None
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute('SELECT * FROM zhanbao_tbl WHERE id=%s', id)
config_data = json.loads(cursor.fetchone()['config_txt'])
#for one_data_from in json.loads(cur_row['config_txt'])['data_from'] for one on one_data_from['ds'] is one['name'] in ds_names
user_input_form_data = {**request.args, **request.form}
# ds_dict= ce.load_all_data(config_data,id,args=args,upload_path=glb.user_report_upload_path(id),userid=glb.ini['user_login']['test_user'])
async_result = hnclic.tasks.load_all_data.delay(
config_data,
id,
user_input_form_data=user_input_form_data,
upload_path=glb.user_report_upload_path(id),
userid=glb.ini['user_login']['test_user'])
while not async_result.ready():
asyncio.run(asyncio.sleep(0.1))
if async_result.status == 'FAILURE':
raise RuntimeError(async_result.traceback.replace('\n', '<br>\n'))
df_arr, ds_dict, ret_config_data = async_result.result
if isinstance(df_arr, str):
raise RuntimeError(df_arr)
ret_dict = dict(
{key: value
for key, value in ds_dict.items() if key in ds_names})
rawtype = rawtype.split(":")
ret_str = '{"errcode":0,"message":"success",'
if rawtype[0] == 'json':
'''json的格式如下
split ，样式为 {index -> [index], columns -> [columns], data -> [values]}
records ，样式为[{column -> value}, … , {column -> value}]
index ，样式为 {index -> {column -> value}}
columns ，样式为 {index -> {column -> value}}
values ，数组样式
table ，样式为{‘schema': {schema}, ‘data': {data}}，和records类似
'''
json_type = 'records'
if len(rawtype) == 2:
if rawtype[1] in "split,records,index,columns,values,table":
json_type = rawtype[1]
else:
return '{"errcode":1,"message":"json选择的类型不正确，应该是下面之一： split,records,index,columns,values,table"}'
for key, value in ret_dict.items():
ret = json.loads(value)
ret_str = ret_str + '\n"' + key + '":' + pd.DataFrame(
ret["data"], columns=ret["columns"]).to_json(
orient=json_type, force_ascii=False) + ','
ret_str = ret_str[:-1] + '}'
return ret_str
def ReportDefine_get(id):
my_path = glb.user_report_upload_path(id)
my_file_list = [{
'name': x,
'url': f'/mg/file/download/{id}/{x}'
} for x in (os.listdir(my_path) if os.path.exists(my_path) else [])]
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute('SELECT * FROM zhanbao_tbl WHERE id=%d', id)
ret = cursor.fetchone()
ret['fileList'] = my_file_list
return json.dumps(ret, ensure_ascii=False)
def delete_one():
glb.update_scheduler(request.json['id'], '0', None, None, None, None)
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute(
'delete from zhanbao_tbl where worker_no=%(worker_no)s and id=%(id)d',
{
'worker_no': session['userid'],
'id': request.json['id']
})
conn.commit()
return {'code': 0, 'message': 'success'}
def update_title():
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
params = {**request.json}
params['worker_no'] = session['userid']
cursor.execute(
'update zhanbao_tbl set report_name=%(label)s where id=%(id)s',
{
'label': request.json['label'],
'id': request.json['id']
})
conn.commit()
return {'code': 0, 'message': ""}
def sys_register(action):
if glb.ini['user_login']['test_user'] != session['userid']:
return {'errcode': 1, 'message': '你没有权限修改配置'}
data = json.loads(json.dumps(request.json))
data['worker_no'] = session['userid']
data['json_txt'] = json.dumps(request.json, ensure_ascii=False)
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
if action == "delete":
cursor.execute("delete from sys_register where id=%d",
data['id'])
conn.commit()
glb.redis.hdel("zb:sys_register", data['name'])
del glb.login_getData_template_dict[data['name']]
return jsonify(errcode=0, )
if data.get('id') is None or data['id'] == 0:
cursor.execute(
"""INSERT INTO [dbo].[sys_register]([name],worker_no,[type],json_txt)
VALUES (%(name)s,%(worker_no)s,%(type)s,%(json_txt)s)""", {
'name': data['name'],
'worker_no': data['worker_no'],
'type': data['type'],
'json_txt': data['json_txt']
})
conn.commit()
data['id'] = cursor.lastrowid
glb.redis.hset("zb:sys_register", data['name'],
data['json_txt'])
glb.login_getData_template_dict[data['name']] = json.loads(
data['json_txt'])
return jsonify(errcode=0, id=cursor.lastrowid)
else:
cursor.execute(
""" update [sys_register] set
name=%(name)s,worker_no=%(worker_no)s
,type=%(type)s
,json_txt=%(json_txt)s where id=%(id)d
""", {
'name': data['name'],
'worker_no': data['worker_no'],
'type': data['type'],
'json_txt': data['json_txt'],
'id': data['id']
})
conn.commit()
glb.redis.hset("zb:sys_register", data['name'],
data['json_txt'])
glb.login_getData_template_dict[data['name']] = json.loads(
data['json_txt'])
return jsonify(errcode=0, )
def get(data_from, userid, user_login=None):
#if data_from['type'] in ["html",'json'] and (data_from['ds'] is None or len(data_from['ds'])==0):
# match_arry=[]
# url=data_from['url']
# for k,v in glb.login_getData_template_dict.items():
# if re.search(v['allow_userid'],userid):
# for one_rule in v['patterns']:
# if url.startswith(one_rule):
# match_arry.append((v["name"],v,one_rule))
# break
# if len(match_arry)==0:
# raise RuntimeError(f"你的工号不能查询这个网址！")
# else:
# match_arry=sorted(match_arry,key=lambda x:-len(x[2])) #按匹配最长的URL被选中
# data_from['type']=match_arry[0][0]
if data_from['type'] in ["html", 'json'] and userid[1:3] == "41":
sys_name = "河南通用"
else:
sys_name = data_from['type']
#
login_getData_template = glb.getSysRegister(sys_name)
if login_getData_template is None:
raise RuntimeError(f"没有实现类型<{module_name}>！")
module_name = login_getData_template['type']
module = module_dict.get(module_name)
if module is None:
module = __import__(module_name)
module_dict[module_name] = module
if module is None:
raise RuntimeError(f"没有实现类型<{module_name}>！")
if user_login is None:
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute(
'SELECT * FROM login_tbl WHERE sys_name=%(sys_name)s and worker_no=%(worker_no)s',
{
"sys_name": sys_name,
"worker_no": userid
})
user_login = cursor.fetchone()
#user_login=user_login_dict.get(sys_name,{})
#user_login_decrypt=dict()
#for k,v in user_login.items():
# user_login_decrypt[k]=AES_decrypt(v,login_getData_template['name'])
ret = module.MyDataInterface(data_from, userid, login_getData_template,
user_login if user_login is not None else {})
return ret
def ReportDefine_save(id):
lastrowid = id
cron_str = request.json.get('cron_str', '')
if cron_str is None:
cron_str = ''
cron_start = request.json.get('cron_start', 0)
if cron_start is None:
cron_start = 0
if cron_str.startswith("*") or cron_str.startswith("* *"):
return {'message': '设置的定时会太频繁执行，请修改,前两位不要是: * *！', 'errcode': -1}
data = {
'config_data': json.dumps(request.json['config_data'],
ensure_ascii=False),
'userid': session['userid'],
'id': id,
'report_name': request.json['report_name'],
'cron_str': cron_str,
'cron_start': int(cron_start),
}
with glb.db_connect() as conn:
with conn.cursor() as cursor:
if id == 0:
cursor.execute(
'insert into zhanbao_tbl(config_txt,worker_no,report_name,cron_str,cron_start) '
+
'values(%(config_data)s, %(userid)s,%(report_name)s,%(cron_str)s,%(cron_start)s)',
data)
lastrowid = cursor.lastrowid
else:
cursor.execute(
'update zhanbao_tbl set config_txt=%(config_data)s,report_name=%(report_name)s,'
+ 'cron_str=%(cron_str)s,cron_start=%(cron_start)d' +
'WHERE id=%(id)d and worker_no=%(userid)s', data)
conn.commit()
#id,cron_str,cron_start,config_data,userid
glb.update_scheduler(id, cron_str, data['cron_start'],
request.json['config_data'], session['userid'],
request.json['report_name'])
return {
'errcode': 0,
'message': '更新成功！',
'lastrowid': int(str(lastrowid)),
'report_name': request.json['report_name']
}
def create_one():
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
pid = request.json.get('id', 0)
cursor.execute('insert into zhanbao_tbl(worker_no,report_name,is_catalog,pid,config_txt,cron_start) ' + \
'values(%(worker_no)s,%(label)s,%(is_catalog)d,%(pid)d,\'{"data_from": [],"fileList": [ ],"output": [ ],"form_input": [ ] }\',0)',
{'label':request.json['label'],
'worker_no':session['userid'],
'is_catalog':request.json['type']=="Catalog",
'pid':pid
})
id = cursor.lastrowid
conn.commit()
return {
'path': f'/zb/zb/{id}',
'icon': 'list',
'label': request.json['label'],
'id': id,
'pid': pid,
'is_catalog': request.json['type'] == "Catalog"
}
def login_tbl():
data = request.json
data['userid'] = session['userid']
sys_reg = glb.getSysRegister(data['sys_name'])
di = data_adapter.get({"type": data['sys_name']}, data['userid'], {
"username": data["username"],
"password": data["password"]
})
cookies, login_headers = asyncio.run(di.login_check())
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute(
""" MERGE INTO [login_tbl] t
USING (VALUES (%(sys_name)s,%(username)s,%(password)s,%(userid)s))
AS s(sys_name,username,password,worker_no)
ON (t.[sys_name]=s.[sys_name] and t.worker_no=s.worker_no)
WHEN MATCHED THEN UPDATE SET [username]=%(username)s,[password]=%(password)s
WHEN NOT MATCHED THEN INSERT (sys_name,username,password,worker_no)
VALUES(%(sys_name)s,%(username)s,%(password)s,%(userid)s);""",
data)
conn.commit()
return jsonify(errcode=0, )
def move_one():
#draggingID:draggingNode.data.id,dropID:dropNode.data.id,dropType
params = {**request.json}
params['worker_no'] = session['userid']
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
if request.json["dropType"] == "inner":
cursor.execute(
'update zhanbao_tbl set pid=%(dropID)s where id=%(draggingID)s',
params)
elif request.json["dropType"] == "before":
cursor.execute(
'''
update zhanbao_tbl set pid=(select pid from zhanbao_tbl where id=%(dropID)s) where id=%(draggingID)s
update zhanbao_tbl set xuhao=(select xuhao+1 from zhanbao_tbl where id=%(dropID)s) where id=%(draggingID)s
;with aaa as(
select pid,id,xuhao,row_number() over(partition by pid order by xuhao) rn from zhanbao_tbl
where worker_no=%(worker_no)s
)
update aaa set xuhao=rn
update zhanbao_tbl set xuhao=xuhao-1 where worker_no=%(worker_no)s and xuhao<=(select xuhao from zhanbao_tbl where worker_no=%(worker_no)s and id=%(draggingID)s )
update zhanbao_tbl set xuhao=(select xuhao+1 from zhanbao_tbl where worker_no=%(worker_no)s and id=%(draggingID)s ) where worker_no=%(worker_no)s
and id=%(dropID)s''', params)
elif request.json["dropType"] == "after":
cursor.execute(
'''
update zhanbao_tbl set pid=(select pid from zhanbao_tbl where id=%(dropID)s) where id=%(draggingID)s
update zhanbao_tbl set xuhao=(select xuhao+1 from zhanbao_tbl where id=%(dropID)s) where id=%(draggingID)s
;with aaa as(
select pid,id,xuhao,row_number() over(partition by pid order by xuhao) rn from zhanbao_tbl
where worker_no=%(worker_no)s
)
update aaa set xuhao=rn
update zhanbao_tbl set xuhao=xuhao+1 where worker_no=%(worker_no)s and xuhao>=(select xuhao from zhanbao_tbl where worker_no=%(worker_no)s and id=%(draggingID)s )
update zhanbao_tbl set xuhao=(select xuhao-1 from zhanbao_tbl where worker_no=%(worker_no)s and id=%(draggingID)s ) where worker_no=%(worker_no)s
and id=%(dropID)s''', params)
conn.commit()
return {'code': 0, 'message': ""}
def zb_execute(self,rptid,config_data=None,userid=None,report_name=""):
if config_data is None or userid is None:
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute('SELECT config_txt,worker_no,report_name FROM zhanbao_tbl WHERE id=%(id)d and is_catalog=0 order by xuhao asc',
{"id":rptid}
)
ret=cursor.fetchone()
if ret is None:
return "没有这个ID:"+rptid
config_data=json.loads(ret['config_txt'])
userid=ret['worker_no']
report_name=ret['report_name']
try:
glb.redis.sadd("zb:executing",rptid)
return asyncio.run(ce.files_template_exec(rptid,config_data,userid,glb.config['UPLOAD_FOLDER'],wx_queue=glb.msg_queue))
except Exception as e:
if self.request.retries>=3:
glb.msg_queue.put({'type':'sendMessage',"wxid":'qywx:'+userid,"content":f"{rptid}：{report_name},执行报错。错误信息："+ str(e.args)})
print(f"{rptid}：{report_name},执行报错。错误信息："+ str(e))
raise self.retry(exc=e, countdown=5)
finally:
glb.redis.srem("zb:executing",rptid)
def getLoginGetDataTemplate():
all_sys = None
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
if glb.ini['user_login']['test_user'] == session['userid']:
cursor.execute('SELECT * FROM sys_register')
all_sys = cursor.fetchall()
cursor.execute('SELECT * FROM login_tbl WHERE worker_no=%s',
session['userid'])
login_tbl = cursor.fetchall()
return {
'errcode':
0,
'sys_register':
all_sys if all_sys is not None else [],
'parsers':
list([
x[:-3] for x in os.listdir("./data_adapter/") if x[-3:] == '.py'
and x != '__init__.py' and x != 'DataInterface.py'
]),
'login_tbl':
login_tbl
}
def user_info():
r_json = session['userInfo']
with glb.db_connect() as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute(
'SELECT id,report_name,pid,is_catalog FROM zhanbao_tbl WHERE worker_no=%s order by xuhao asc',
session['userid'])
ret = cursor.fetchall()
r_json['roles'] = ['Admin', 'Vistor']
r_json['menus'] = [{
'path': f'/zb/zb/{x["id"]}',
'icon': 'list',
'title': x["report_name"],
'id': x["id"],
'pid': x["pid"],
"is_catalog": x['is_catalog']
} for x in ret]
r_json['name'] = r_json['username']
r_json['userInfo'] = {
'username': session['userid'],
'name': r_json['username'],
'avatar': 'https://gitee.com/uploads/61/632261_smallweigit.jpg',
}
r_json['permission'] = [
'sys_crud_btn_add',
'sys_crud_btn_export',
'sys_menu_btn_add',
'sys_menu_btn_edit',
'sys_menu_btn_del',
'sys_role_btn1',
'sys_role_btn2',
]
if glb.ini['user_login']['test_user'] == session['userid']:
r_json['permission'].append('sys_register')
r_json['canReadSys'] = glb.getSysByUser(session['userid'])
return json.dumps({"code": 0, "data": r_json})
