def dbtdel(desc):
if request.method == "DELETE":
try:
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
del_data_table_id = request.json.get("dmp_data_table_id")
if del_data_table_id:
del_data_table = DataTable.get(del_data_table_id)
if del_data_table:
is_user = Users.get(
DataTable.get(del_data_table_id).dmp_user_id
).id == current_user_id
is_user_leader = Users.get(
DataTable.get(del_data_table_id).dmp_user_id
).leader_dmp_user_id == current_user_id
is_admin = Users.get(current_user_id).dmp_group_id == 1
if is_user or is_user_leader or is_admin:
del_data_table.delete()
current_app.logger.info("del db table complete!")
return resp_hanlder(result="OK")
else:
return resp_hanlder(code=302)
else:
return resp_hanlder(code=404)
else:
return resp_hanlder(code=101)
except Exception as err:
return resp_hanlder(err=err)
def csu(dmp_username, real_name, passwd, email):
"""创建管理员用户"""
from dmp.models import Users, Groups
from dmp.utils.ep_data import EnvelopedData
if not all([dmp_username, real_name, passwd, email]):
return 'Insufficient parameter, please recreate superuser.'
db_user_count = Users.query.count()
if db_user_count == 0:
user = Users(dmp_username=dmp_username,
real_name=real_name,
password=passwd,
email=email)
rootgroup = Groups.query.filter(Groups.id == 1).first()
user.dmp_group_id = 1
user.leader_dmp_user_id = None
user.confirmed = True
db.session.add(user)
db.session.commit()
res = EnvelopedData.create_root(rootgroup)
if isinstance(res, str):
return {'msg': res}
return {'msg': 'Super administrator has been created successfully.'}
else:
return {
'msg':
'The super administrator already exists in the database, please do not add it again.'
}
def del_queries_by_id(desc, query_id):
"""
删除保存的SQL
---
tags:
- SQL
parameters:
- name: query_id
in:
type: int
required: true
description: 要删除的查询的ID
responses:
o:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if SavedQuery.exist_item_by_id(query_id):
current_queries = SavedQuery.get(query_id)
if current_queries.created_dmp_user_id == current_user_id:
current_queries.delete()
return resp_hanlder(code=0, result="OK")
else:
return resp_hanlder(code=999, msg="您不是该查询的所有者,无权删除改查询")
else:
return resp_hanlder(code=999, msg="该查询不存在或已被删除")
def del_archive_star(desc, archive_id):
"""
文件夹取消置顶
---
tags:
- BI
parameters:
- name: archive_id
type: int
required: true
description: 要取消置顶的文件夹ID
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if DashboardArchive.exist_item_by_id(archive_id):
is_exists = db.session.query(exists().where(
and_(ArchiveStar.dmp_user_id == current_user_id,
ArchiveStar.dmp_archive_id == archive_id))).scalar()
if is_exists:
ArchiveStar.query.filter_by(dmp_user_id=current_user_id,
dmp_archive_id=archive_id).delete()
return resp_hanlder(code=0, msg="OK")
else:
return resp_hanlder(code=999, msg="文件夹未置顶")
else:
return resp_hanlder(code=999, msg="文件夹不存在或已被删除")
def get_queries_by_id(desc, query_id):
"""
根据ID获取保存的SQL
---
tags:
- SQL
parameters:
- name: query_id
in:
type: int
required: ture
description: 查询的ID
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if SavedQuery.exist_item_by_id(query_id):
query_obj = SavedQuery.get(query_id)
if query_obj.created_dmp_user_id == current_user_id:
query_info = query_obj.__json__()
result = {"data": query_info}
return resp_hanlder(code=0, result=result)
else:
return resp_hanlder(code=999, msg="非改查询的所有者，无权查看")
else:
return resp_hanlder(code=999, msg="查询不存在或已被删除")
def set_index(desc, dashboard_id):
"""
设置首页
---
tags:
- BI
parameters:
- name: dashboard_id
type: int
required: true
description: 要设置为首页的看板ID
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if Dashboard.exist_item_by_id(dashboard_id):
UserDashboard.query.filter_by(dmp_user_id=current_user_id).delete()
index_page = UserDashboard(dmp_user_id=current_user_id,
dmp_dashboard_id=dashboard_id)
index_page.save()
return resp_hanlder(code=0, result="OK")
def migration(desc):
if request.method == "POST":
try:
auth_token = request.headers.get('Authorization')
submit_dmp_user_id = Users.decode_auth_token(auth_token)
form_info = request.json
origin_dmp_table_id = form_info.get("origin_dmp_table_id")
rule = form_info.get("rule")
destination_dmp_database_id = form_info.get(
"destination_dmp_database_id")
new_table_name = form_info.get("new_table_name")
method = form_info.get("method")
description = form_info.get("description")
new_form_info = FormMigrate(
origin_dmp_table_id=origin_dmp_table_id,
destination_dmp_database_id=destination_dmp_database_id,
new_table_name=new_table_name,
)
new_form_info.save()
new_form = Forms(submit_dmp_user_id=submit_dmp_user_id,
description=description,
form_info_id=new_form_info.fid,
form_type=3)
new_form.save()
current_app.logger.info(new_form.id)
return resp_hanlder(result="OK")
except Exception as err:
return resp_hanlder(code=999, err=err)
def from_db(desc):
if request.method == "POST":
try:
auth_token = request.headers.get('Authorization')
submit_dmp_user_id = Users.decode_auth_token(auth_token)
form_info = request.json
data_tablename = form_info.get("data_tablename")
db_tablename = form_info.get("db_tablename")
database_id = form_info.get("database_id")
dmp_data_case_id = form_info.get("dmp_data_case_id")
description = form_info.get("description")
new_form_info = FormAddDataTable(
dmp_data_table_name=data_tablename,
db_table_name=db_tablename,
dmp_database_id=database_id,
dmp_case_id=dmp_data_case_id,
)
new_form_info.save()
new_form = Forms(submit_dmp_user_id=submit_dmp_user_id,
description=description,
form_info_id=new_form_info.fid,
form_type=1)
new_form.save()
return resp_hanlder(result="OK")
except Exception as err:
return resp_hanlder(code=999, err=err)
def del_dashboard_star(desc, dashboard_id):
"""
看板取消置顶
---
tags:
- BI
parameters:
- name: dashboard_id
type: int
required: true
description: 要取消置顶的看板ID
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if Dashboard.exist_item_by_id(dashboard_id):
is_exists = db.session.query(exists().where(
and_(DashboardStar.dmp_user_id == current_user_id,
DashboardStar.dmp_dashboard_id == dashboard_id))).scalar()
if is_exists:
DashboardStar.query.filter_by(
dmp_user_id=current_user_id,
dmp_dashboard_id=dashboard_id).delete()
return resp_hanlder(code=0, msg="OK")
else:
return resp_hanlder(code=999, msg="看板已删除置顶")
else:
return resp_hanlder(code=999, msg="看板不存在或已被删除")
def add_queries(desc):
"""
保存SQL
---
tags:
- SQL
parameters:
- name: query_name
in:
type: string
required: true
description: 查询的名称，最长64字符
- name: query_sql
in:
type: string
required: true
description: 查询语句，最长65535字符
- name: description
in:
type: string
required: false
description: 查询的简介，最长512字符
- name: dmp_case_id
in:
type: int
required: true
description: 原数据案例ID
- name: dmp_data_table_id
in:
type: int
required: true
description: 原数据表ID
responses:
0:
description: OK
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
request_json = request.json if request.json else {}
valid = Add_queries_validator(request_json)
if not valid.is_valid():
return resp_hanlder(code=201, msg=valid.str_errors)
new_queries = SavedQuery(
query_name=request_json.get("query_name"),
query_sql=request_json.get("query_sql"),
description=request_json.get("description"),
dmp_data_table_id=request_json.get("dmp_data_table_id"),
dmp_case_id=request_json.get("dmp_case_id"),
created_dmp_user_id=current_user_id,
changed_dmp_user_id=current_user_id,
)
new_queries.save()
return resp_hanlder(code=0, result=new_queries.__json__())
def form_permission(user_id):
user = Users.get(user_id)
usergroup_id = user.groups.id
approve_id = Permissions.query.filter_by(route="/form/approve/").first().id
p_ids = [p.id for p in user.groups.permissions]
if usergroup_id == 1:
return 3
elif approve_id in p_ids:
return 2
elif approve_id not in p_ids:
return 1
def post(desc):
db_info = request.json
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if request.method == "POST":
try:
new_db = Database(
dmp_database_name=db_info.get("dmp_database_name"),
db_type=db_info.get("db_type"),
db_host=db_info.get("db_host"),
db_port=db_info.get("db_port"),
db_username=db_info.get("db_username"),
db_passwd=db_info.get("db_passwd"),
db_name=db_info.get("db_name"),
ispublic=db_info.get("ispublic"),
description=db_info.get("description"),
dmp_user_id=current_user_id,
)
new_db.save()
current_app.logger.info("add new database")
return resp_hanlder(result={"add_database": "complete!"})
except Exception as err:
return resp_hanlder(code=200, err=err)
elif request.method == "PUT":
try:
if db_info.get("dmp_database_id"):
modify_db = Database.get(db_info.get("dmp_database_id"))
if "dmp_database_name" in db_info.keys():
modify_db.dmp_database_name = db_info.get("dmp_database_name")
if "db_type" in db_info.keys():
modify_db.db_type = db_info.get("db_type")
if "db_host" in db_info.keys():
modify_db.db_host = db_info.get("db_host")
if "db_port" in db_info.keys():
modify_db.db_port = db_info.get("db_port")
if "db_username" in db_info.keys():
modify_db.db_username = db_info.get("db_username")
if "db_passwd" in db_info.keys():
modify_db.db_passwd = db_info.get("db_passwd")
if "db_name" in db_info.keys():
modify_db.db_name = db_info.get("db_name")
if "ispublic" in db_info.keys():
modify_db.ispublic = True if db_info.get("ispublic") else False
if "description" in db_info.keys():
modify_db.description = db_info.get("description")
modify_db.put()
current_app.logger.info("database info modify complete!")
return resp_hanlder(result={"modify": "ok!"})
else:
return resp_hanlder(code=101)
except Exception as err:
return resp_hanlder(code=999, err=err)
def info(desc):
if request.method == "GET":
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
try:
database_id = request.json.get("dmp_database_id") if request.json else None
if database_id:
data = Database.query.get(database_id).__json__()
else:
data = []
if Users.get(current_user_id).dmp_group_id == 1:
data = [d.__json__() for d in Database.query.all()]
else:
user_ids = [u.id for u in Users.query.filter_by(leader_dmp_user_id=current_user_id).all()]
user_ids.append(current_user_id)
# current_app.logger.info(user_ids)
data = [d.__json__() for d in
Database.query.filter(Database.dmp_user_id.in_(user_ids) | Database.ispublic == True).all()]
# current_app.logger.info(data)
return resp_hanlder(result=data)
except Exception as err:
return resp_hanlder(code=999, err=err)
def put(desc):
if request.method == "PUT":
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
dbt_info = request.json
dbt_id = dbt_info.get("dmp_data_table_id")
dbt = DataTable.get(dbt_id)
if dbt:
dmp_user_id = dbt.dmp_user_id
if current_user_id == 1 or current_user_id == dmp_user_id or Users.get(
dmp_user_id).leader_dmp_user_id == current_user_id:
if "dmp_data_table_name" in dbt_info.keys():
dbt.dmp_data_table_name = dbt_info.get(
"dmp_data_table_name")
if "description" in dbt_info.keys():
dbt.description = dbt_info.get("description")
dbt.put()
return resp_hanlder(result="OK!")
else:
return resp_hanlder(code=301)
else:
return resp_hanlder(code=404)
def get_index_dashboards(desc):
"""
获取首页看板
---
tags:
- BI
parameters:
- name: 无
type: int
required: false
description: 无需参数
responses:
0:
description: OK!
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
index_dashboards = None
ud = UserDashboard.query.filter_by(dmp_user_id=current_user_id).first()
index_dashboards_id = ud.dmp_dashboard_id if ud else None
if index_dashboards_id:
index_dashboards_obj = db.session.query(
Dashboard.id.label("id"),
literal("dashboard").label("type"),
Dashboard.dmp_dashboard_name.label("name"),
Dashboard.charts_position.label("charts_position"),
Dashboard.description.label("description"),
Dashboard.release.label("release"),
db.session.query(DashboardStar.id).filter(
and_(DashboardStar.dmp_dashboard_id == Dashboard.id,
DashboardStar.dmp_user_id ==
current_user_id)).exists().label("is_star"),
exists().where(
and_(UserDashboard.dmp_dashboard_id == Dashboard.id,
UserDashboard.dmp_user_id == current_user_id)).label(
"is_index"),
Dashboard.upper_dmp_dashboard_archive_id.label(
"upper_dmp_dashboard_archive_id"),
db.session.query(Users.dmp_username).filter(
Users.id == Dashboard.created_dmp_user_id).subquery().c.
dmp_username.label("created_dmp_user_name"),
Dashboard.created_dmp_user_id.label("created_dmp_user_id"),
Dashboard.created_on.label("created_on"),
Dashboard.changed_on.label("changed_on")).filter(
Dashboard.id == index_dashboards_id).first()
index_dashboards = index_dashboards_obj._asdict(
) if index_dashboards_obj else None
return resp_hanlder(code=0, result={"index": index_dashboards})
def register(desc):
'''
说明:用户注册及超级管理员单一添加用户接口
参数:dmp_username,real_name,password,email;说明:客户端请求参数信息,数据类型:JSON
返回值:成功与失败返回对应的状态码及提示信息,数据类型:JSON,数据格式:{'msg':'pass','results':null,'status':xxx}
'''
try:
user_obj = Users.query.filter_by(id=1).first()
# 判断初始状态有没有超级用管理员，没有则不能创建用户，必须要先创建一个超级管理员
ret = UserVerify.judge_superuser(user_obj)
if ret:
return resp_hanlder(code=999, msg=ret)
data = request.json
if data == None:
return resp_hanlder(code=999)
auth_token = data.get('Authorization')
dmp_username = data.get('dmp_username')
real_name = data.get('real_name')
passwd = data.get('password')
email = data.get('email')
user = Users(dmp_username=dmp_username, real_name=real_name, password=passwd,
email=email, leader_dmp_user_id=1)
res_token = PuttingData.get_obj_data(Users, auth_token)
if auth_token != None and isinstance(res_token, dict):
res = PuttingData.root_add_user(
data, res_token, user, dmp_username, real_name)
# 返回字典-管理员单一添加成功
if isinstance(res, dict):
return resp_hanlder(code=0, msg=res)
# 返回元组-管理员/教师单一添加缺少参数
elif isinstance(res, tuple):
return resp_hanlder(code=999, msg=res[1])
# 普通管理员和教师无法添加管理员角色，需要超级管理员添加
elif res == -1:
return resp_hanlder(code=999, msg='无法添加管理员用户组用户,请联系管理员添加.')
elif res == -2:
return resp_hanlder(code=999, msg='无法添加管理员或教师用户组用户,请联系管理员添加.')
# 返回token错误的字符串-注册成功(注册时无token)
db.session.add(user)
db.session.commit()
ValidationEmail().activate_email(user, email)
return resp_hanlder(code=1001, msg=RET.alert_code[1001])
except Exception as err:
db.session.rollback()
return resp_hanlder(code=999, msg=str(err))
def get_dashboard_by_id(desc, dashboard_id):
"""
获取单一看板信息
---
tags:
- BI
parameters:
- name: dashboard_id
type: int
required: true
description: 要获取的看板ID
responses:
0:
description: OK
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if Dashboard.exist_item_by_id(dashboard_id):
dashboards_obj = db.session.query(
Dashboard.id.label("id"),
literal("dashboard").label("type"),
Dashboard.dmp_dashboard_name.label("name"),
Dashboard.description.label("description"),
Dashboard.charts_position.label("charts_position"),
Dashboard.release.label("release"),
db.session.query(DashboardStar.id).filter(
and_(DashboardStar.dmp_dashboard_id == Dashboard.id,
DashboardStar.dmp_user_id ==
current_user_id)).exists().label("is_star"),
exists().where(
and_(UserDashboard.dmp_dashboard_id == Dashboard.id,
UserDashboard.dmp_user_id == current_user_id)).label(
"is_index"),
Dashboard.upper_dmp_dashboard_archive_id.label(
"upper_dmp_dashboard_archive_id"),
db.session.query(Users.dmp_username).filter(
Users.id == Dashboard.created_dmp_user_id).subquery().c.
dmp_username.label("created_dmp_user_name"),
Dashboard.created_dmp_user_id.label("created_dmp_user_id"),
Dashboard.created_on.label("created_on"),
Dashboard.changed_on.label("changed_on")).filter(
Dashboard.id == dashboard_id).first()
dashboards = dashboards_obj._asdict() if dashboards_obj else None
return resp_hanlder(code=0, result={"data": dashboards})
else:
return resp_hanlder(code=999, msg="看板不存在或已被删除")
def changepwd(desc):
'''
说明:用户重设密码接口
参数:Authorization,newpassword,说明:重置密码生成的标识token及新密码,都通过json传入，数据类型:JSON
返回值:成功返回状态码及对应提示信息,数据类型:JSON,数据格式:{'msg':'pass','results':null,'status':xxx}
'''
if request.method == 'PUT':
try:
data = request.json
token = data.get('authorization')
newpassword = data.get('newpassword')
res = Users.reset_password(token, newpassword)
if res == True:
return resp_hanlder(code=1006, msg=RET.alert_code[1006])
else:
return resp_hanlder(code=1007, msg=res)
except Exception as err:
return resp_hanlder(code=999, msg=str(err))
def get_data_service_by_id(id, desc):
"""
获取单一数据服务
---
tags:
- DataService
parameters:
- name: id
in: path
type: int
required: true
description: URL参数要获取的数据服务内容的ID
responses:
500:
description: Error The language is not awesome!
0:
description: 查询单一数据服务信息
schema:
$ref: '#/definitions/DataServiceParameterItems'
"""
if request.method == 'GET':
try:
auth_token = request.headers.get("Authorization")
user_id = Users.decode_auth_token(auth_token)
data_services = DataService.get(id)
if data_services:
create_user_id = data_services.created_dmp_user_id
'''判断数据服务是否属于当前用户'''
if create_user_id != user_id:
return resp_hanlder(code=301)
parameters = [
d.__json__() for d in DataServiceParameter.query.filter_by(
dmp_data_service_id=id)
]
result = data_services.__json__()
result['parameters'] = parameters
return resp_hanlder(result=result)
else:
return resp_hanlder(code=7401, msg="数据服务不存在或在操作期间被删除")
except Exception as err:
return resp_hanlder(code=999, error=err)
def from_file(desc):
if request.method == "POST":
try:
auth_token = request.headers.get('Authorization')
submit_dmp_user_id = Users.decode_auth_token(auth_token)
form_info = request.json
filetype = form_info.get("filetype")
filepath = form_info.get("filepath")
column_line = form_info.get("column_line")
column = form_info.get("column")
json_dimension_reduction = form_info.get(
"json_dimension_reduction")
destination_dmp_database_id = form_info.get(
"destination_dmp_database_id")
destination_db_table_name = form_info.get("tablename")
dmp_data_table_name = form_info.get("dmp_data_table_name")
method = form_info.get("method")
dmp_data_case_id = form_info.get("dmp_data_case_id")
description = form_info.get("description")
new_form_info = FormUpload(
filetype=filetype,
filepath=filepath,
column_line=column_line,
column=column,
destination_dmp_database_id=destination_dmp_database_id,
destination_db_table_name=destination_db_table_name,
dmp_data_table_name=dmp_data_table_name,
method=method,
dmp_case_id=dmp_data_case_id,
)
new_form_info.save()
new_form = Forms(
submit_dmp_user_id=submit_dmp_user_id,
description=description,
form_info_id=new_form_info.fid,
form_type=2,
)
new_form.save()
return resp_hanlder(result="OK")
except Exception as err:
return resp_hanlder(code=999, err=err)
def download(desc):
if request.method == "POST":
try:
auth_token = request.headers.get('Authorization')
submit_dmp_user_id = Users.decode_auth_token(auth_token)
form_info = request.json
dmp_data_table_id = form_info.get("dmp_data_table_id")
rule = form_info.get("rule")
description = form_info.get("description")
new_form_info = FormDownload(dmp_data_table_id=dmp_data_table_id, )
new_form_info.save()
new_form = Forms(submit_dmp_user_id=submit_dmp_user_id,
description=description,
form_info_id=new_form_info.fid,
form_type=4)
new_form.save()
current_app.logger.info(new_form.id)
return resp_hanlder(result="OK")
except Exception as err:
return resp_hanlder(code=999, err=err)
def activate(desc):
'''
说明:用户邮箱激活接口
参数:Authorization,说明:邮件生成的客户端标识,通过json传入,数据类型:JSON
返回值:成功返回状态码及对应提示信息,数据类型:JSON,数据格式:{'msg':'pass','results':null,'status':xxx}
'''
try:
# 激活邮箱--注册时激活、token失效或者忘记导致未激活
token = request.json.get('authorization')
res = PuttingData.get_obj_data(Users, token)
# 校验token的有效期及正确性
if isinstance(res, dict):
if res.get('confirmed') == True:
return resp_hanlder(code=999, msg=RET.alert_code[1014])
# 已激活,confirmed为True
if Users.check_activate_token(res) == True:
return resp_hanlder(code=1009, msg=RET.alert_code[1009])
else:
return resp_hanlder(code=2002, msg=RET.alert_code[2002])
except Exception as err:
return resp_hanlder(code=999, msg=str(err))
def del_index(desc):
"""
取消设置首页
---
tags:
- BI
parameters:
- name: 无
in: <++>
type: string
required: false
description: 不需要参数
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if current_user_id:
UserDashboard.query.filter_by(dmp_user_id=current_user_id).delete()
return resp_hanlder(code=0, result="OK")
def delete_data_service_by_id(id, desc):
"""
删除数据服务
---
tags:
- DataService
parameters:
- name: id
in: path
type: integer
required: true
description: URL参数，要删除的数据服务的ID
responses:
500:
description: Error The language is not awesome!
0:
msg: 'ok'
"""
if request.method == 'DELETE':
try:
if id:
if DataService.exist_item_by_id(id):
item = DataService.get(id)
auth_token = request.headers.get("Authorization")
user_id = Users.decode_auth_token(auth_token)
if user_id == item.created_dmp_user_id:
item.delete()
return resp_hanlder(
result={"delete_data_service": "success"})
else:
return resp_hanlder(code=301)
else:
return resp_hanlder(code=7401, msg="数据服务不存在或在操作期间被删除")
else:
return resp_hanlder(code=101)
except Exception as err:
return resp_hanlder(code=999, error=err)
def approve(desc):
if request.method == "PUT":
try:
# 获取请求参数
approve_form_info = request.json
auth_token = request.headers.get('Authorization')
approve_user_id = Users.decode_auth_token(auth_token)
form_type = approve_form_info.get("dmp_form_type", None)
form_id = approve_form_info.get("dmp_form_id", None)
approve_result = approve_form_info.get("approve_result", None)
answer = approve_form_info.get("answer", None)
# 保存表单审批信息
approve_form = Forms.get(form_id)
approve_form.approve_dmp_user_id = approve_user_id
approve_form.approve_result = approve_result
approve_form.approve_on = datetime.datetime.now()
approve_form.answer = answer
approve_form.put()
# 执行审批动作
if form_type == 1:
# 从数据库添加数据表单
try:
if approve_result == 1:
post(
dmp_data_table_name=approve_form.info_form.
dmp_data_table_name,
db_table_name=approve_form.info_form.db_table_name,
dmp_user_id=approve_form.submit_dmp_user_id,
dmp_database_id=approve_form.info_form.
dmp_database_id,
dmp_case_id=approve_form.info_form.dmp_case_id,
description=approve_form.description)
approve_form.result = "SUCCEED！"
except Exception as err:
approve_form.result = "ASSOCIATION FAILED，ERROR_MSG:%s" % str(
err)
approve_form.finish = True
elif form_type == 2:
# 文件上传添加数据表单
upload_path = current_app.config.get("UPLOADED_PATH")
file_path = os.path.join(upload_path,
approve_form.info_form.filepath)
file_type = approve_form.info_form.filetype
column_line = approve_form.info_form.column_line
column = approve_form.info_form.column.split(",") if type(
approve_form.info_form.column) == str else []
destination_dmp_database_id = approve_form.info_form.destination_dmp_database_id
destination_db_table_name = approve_form.info_form.destination_db_table_name
dmp_data_table_name = approve_form.info_form.dmp_data_table_name
method = approve_form.info_form.method
description = approve_form.description
submit_dmp_user_id = approve_form.submit_dmp_user_id
dmp_case_id = approve_form.info_form.dmp_case_id
destination_database = Database.get(
destination_dmp_database_id)
destination_database_type = destination_database.db_type
destination_db_host = destination_database.db_host
destination_db_port = destination_database.db_port
destination_db_username = destination_database.db_username
destination_db_passwd = destination_database.db_passwd
destination_db_name = destination_database.db_name
try:
reader = []
text_column = []
csv_filepath = os.path.join(
current_app.config.get("UPLOADED_PATH"), file_path)
if file_type == 1 or file_type == 3:
# csv、excel
dt = pd.read_csv(csv_filepath, header=column_line)
csv_column = [
"_".join(lazy_pinyin(d))
if is_contains_chinese(d) else d
for d in list(dt.columns)
]
text_column = column if column and len(column) == len(
csv_column) else csv_column
csv_column_d = [{
"index": i,
"type": "string"
} for i, cc in enumerate(text_column)]
reader = textfile_reader(filepath=csv_filepath,
column=csv_column_d)
elif file_type == 2:
# json
dt = pd.read_csv(csv_filepath, header=0)
csv_column = [
"_".join(lazy_pinyin(d))
if is_contains_chinese(d) else d
for d in list(dt.columns)
]
text_column = column if column and len(column) == len(
csv_column) else csv_column
csv_column_d = [{
"index": i,
"type": "string"
} for i, cc in enumerate(text_column)]
reader = textfile_reader(filepath=csv_filepath,
column=csv_column_d)
pass
writer = []
if destination_database_type == 1:
# hive_writer
hive_columns = [{
"name": col,
"type": "string"
} for col in text_column]
hive_path = "/user/hive/warehouse/%s.db/%s" % (
destination_db_name, destination_db_table_name)
hive_conn = auto_connect(
db_id=destination_dmp_database_id)
create_table_sql = create_table_query_handler(
table_name=destination_db_table_name,
fields=text_column,
uniform_type="string",
id_primary_key=False,
semicolon=False,
fieldDelimiter=",")
print(create_table_sql)
if method == 1:
hive_conn.execsql(create_table_sql)
elif method == 3:
del_table_sql = "drop table {table_name}"
hive_conn.execsql(
del_table_sql.format(
table_name=destination_db_table_name))
hive_conn.execsql(create_table_sql)
else:
pass
writer = hive_writer(
host=destination_db_host,
port=8020,
path=hive_path,
filename=destination_db_table_name,
column=hive_columns,
fieldDelimiter=",",
)
elif destination_database_type == 2:
# mysql_writer
create_table_sql = create_table_query_handler(
table_name=destination_db_table_name,
fields=text_column,
uniform_type="text",
id_primary_key=True,
semicolon=True,
fieldDelimiter=None)
current_app.logger.info(create_table_sql)
mysql_conn = auto_connect(
db_id=destination_dmp_database_id)
del_table_sql = "drop table {table_name};"
if method == 1:
mysql_conn.execsql(sql=create_table_sql)
elif method == 2:
pass
elif method == 3:
mysql_conn.execsql(
del_table_sql.format(
table_name=destination_db_table_name))
mysql_conn.execsql(create_table_sql)
column = text_column
writer = mysql_writer(
model=1,
username=destination_db_username,
password=destination_db_passwd,
column=column,
host=destination_db_host,
port=destination_db_port,
db=destination_db_name,
table=destination_db_table_name,
)
elif destination_database_type == 3:
# mongo_writer
mongo_conn = auto_connect(
db_id=destination_dmp_database_id)
if method == 3:
mongo_conn.del_table(
table_name=destination_db_table_name)
column = [{
"name": col,
"type": "string"
} for col in text_column]
writer = mongodb_writer(
host=destination_db_host,
port=destination_db_port,
username=destination_db_username,
password=destination_db_passwd,
db_name=destination_db_name,
collection_name=destination_db_table_name,
column=column,
)
meta = {
"form_id": approve_form.id,
"csv_file_path": csv_filepath,
"dmp_data_table_name": dmp_data_table_name,
"db_table_name": destination_db_table_name,
"dmp_user_id": submit_dmp_user_id,
"dmp_database_id": destination_dmp_database_id,
"dmp_case_id": dmp_case_id,
"description": description,
}
job_hanlder.delay(reader=reader,
writer=writer,
channel=3,
func=postfunc,
meta=meta)
approve_form.result = "SUCCEED!"
except Exception as err:
approve_form.result = "CREATE UPLOAD JOB FAILED，ERROR MESSAGE：%s" % str(
err)
approve_form.finish = True
elif form_type == 3:
# 数据迁移表单
if approve_result == 1:
origin_data_table = DataTable.get(
approve_form.info_form.origin_dmp_table_id)
origin_database = Database.get(
origin_data_table.dmp_database_id)
origin_database_type = origin_database.db_type
origin_db_host = origin_database.db_host
origin_db_port = origin_database.db_port
origin_db_username = origin_database.db_username
origin_db_passwd = origin_database.db_passwd
origin_db_name = origin_database.db_name
origin_db_table_name = origin_data_table.db_table_name
destination_database = Database.get(
approve_form.info_form.destination_dmp_database_id)
destination_database_type = destination_database.db_type
destination_db_host = destination_database.db_host
destination_db_port = destination_database.db_port
destination_db_username = destination_database.db_username
destination_db_passwd = destination_database.db_passwd
destination_db_name = destination_database.db_name
destination_db_table_name = approve_form.info_form.new_table_name
try:
base_column = auto_connect(
db_id=origin_data_table.dmp_database_id).columns(
origin_data_table.db_table_name)
# current_app.logger.info(base_column)
reader = []
if origin_database_type == 1:
# hive_reader
reader = hive_reader(
host=origin_db_host,
port=8020,
path="/user/hive/warehouse/%s.db/%s" %
(origin_db_name, origin_db_table_name),
fileType="text",
column=["*"],
fieldDelimiter=',',
encoding="utf-8")
elif origin_database_type == 2:
# mysql_reader
column = [
col.get("dmp_data_table_column_name")
for col in base_column
]
reader = mysql_reader(
username=origin_db_username,
password=origin_db_passwd,
column=column,
host=origin_db_host,
port=origin_db_port,
db=origin_db_name,
table=origin_db_table_name,
where=None,
)
elif origin_database_type == 3:
# mongodb
column = [{
"index":
i + 1,
"name":
col.get("dmp_data_table_column_name"),
"type":
col.get("dmp_data_table_column_type")
} for i, col in enumerate(base_column)]
reader = mongodb_reader(
host=origin_db_host,
port=origin_db_port,
db_name=origin_db_name,
collection_name=origin_db_table_name,
column=column,
username=origin_db_username,
password=origin_db_passwd)
pass
writer = []
if destination_database_type == 1:
# hive_writer
hive_col = [
col.get("dmp_data_table_column_name")
for col in base_column
]
hive_columns = [{
"name": col,
"type": "string"
} for col in hive_col]
hive_path = "/user/hive/warehouse/%s.db/%s" % (
destination_db_name, destination_db_table_name)
hive_conn = auto_connect(
db_id=approve_form.info_form.
destination_dmp_database_id)
create_table_sql = create_table_query_handler(
table_name=destination_db_table_name,
fields=hive_col,
uniform_type="string",
id_primary_key=False,
semicolon=False,
fieldDelimiter=",")
hive_conn.execsql(create_table_sql)
writer = hive_writer(
host=destination_db_host,
port=8020,
path=hive_path,
filename=destination_db_table_name,
column=hive_columns,
fieldDelimiter=",",
)
elif destination_database_type == 2:
# mysql_writer
column = [
col.get("dmp_data_table_column_name")
for col in base_column
]
create_table_sql = create_table_query_handler(
table_name=destination_db_table_name,
fields=column,
uniform_type="text",
id_primary_key=True,
semicolon=True,
fieldDelimiter=None)
mysql_conn = auto_connect(
db_id=approve_form.info_form.
destination_dmp_database_id)
mysql_conn.execsql(sql=create_table_sql)
# preSQL = []
writer = mysql_writer(
model=1,
username=destination_db_username,
password=destination_db_passwd,
column=column,
host=destination_db_host,
port=destination_db_port,
db=destination_db_name,
table=destination_db_table_name,
)
elif destination_database_type == 3:
# mongo_writer
column = [{
"name":
col.get("dmp_data_table_column_name"),
"type":
"string"
} for col in base_column]
writer = mongodb_writer(
host=destination_db_host,
port=destination_db_port,
username=destination_db_username
if destination_db_username else None,
password=destination_db_passwd
if destination_db_passwd else None,
db_name=destination_db_name,
collection_name=destination_db_table_name,
column=column,
)
meta = {"form_id": approve_form.id}
job_hanlder.delay(reader=reader,
writer=writer,
channel=3,
func=job_finish,
meta=meta)
except Exception as err:
approve_form.result = "CREATE MIGRATE JOB FAILED，ERROR MESSAGE：%s" % str(
err)
approve_form.finish = True
raise err
elif form_type == 4:
# 数据导出表单
if approve_result == 1:
origin_data_table = DataTable.get(
approve_form.info_form.dmp_data_table_id)
if origin_data_table:
origin_database = Database.get(
origin_data_table.dmp_database_id)
origin_database_type = origin_database.db_type
origin_db_host = origin_database.db_host
origin_db_port = origin_database.db_port
origin_db_username = origin_database.db_username
origin_db_passwd = origin_database.db_passwd
origin_db_name = origin_database.db_name
origin_db_table_name = origin_data_table.db_table_name
try:
base_column = auto_connect(
db_id=origin_data_table.dmp_database_id
).columns(origin_data_table.db_table_name)
reader = []
if origin_database_type == 1:
# hive_reader
reader = hive_reader(
host=origin_db_host,
port=8020,
path="/user/hive/warehouse/%s.db/%s" %
(origin_db_name, origin_db_table_name),
fileType="text",
column=["*"],
fieldDelimiter=',',
encoding="utf-8")
elif origin_database_type == 2:
# mysql_reader
column = [
col.get("dmp_data_table_column_name")
for col in base_column
]
reader = mysql_reader(
username=origin_db_username,
password=origin_db_passwd,
column=column,
host=origin_db_host,
port=origin_db_port,
db=origin_db_name,
table=origin_db_table_name,
where=None,
)
elif origin_database_type == 3:
# mongodb
column = [{
"index":
i + 1,
"name":
col.get("dmp_data_table_column_name"),
"type":
col.get("dmp_data_table_column_type")
} for i, col in enumerate(base_column)]
reader = mongodb_reader(
host=origin_db_host,
port=origin_db_port,
db_name=origin_db_name,
collection_name=origin_db_table_name,
column=column,
username=origin_db_username,
password=origin_db_passwd)
pass
writer = []
download_path = os.path.join(
current_app.config.get("DOWNLOAD_PATH"),
approve_form.submit_dmp_username)
file_name = origin_db_table_name + uuid_str(
) + ".csv"
finally_name = origin_db_table_name + "-" + uuid_str(
) + ".csv"
headers = [
col.get("dmp_data_table_column_name")
for col in base_column
]
writer = textfile_writer(filepath=download_path,
filename=file_name,
header=headers)
ip = socket.gethostbyname(socket.gethostname())
ftp_url = "ftp://%s:21/%s" % (
str(ip),
str(
os.path.join(
approve_form.submit_dmp_username,
finally_name)))
meta = {
"form_id": approve_form.id,
"file_name": file_name,
"finally_name": finally_name,
"download_path": download_path,
"ftp_url": ftp_url,
}
job_hanlder.delay(reader=reader,
writer=writer,
channel=1,
func=dlfunc,
meta=meta)
except Exception as err:
approve_form.result = "CREATE DOWNLOAD JOB FAILED，ERROR MESSAGE：%s" % str(
err)
approve_form.finish = True
else:
approve_form.result = "The original data sheet information is not obtained"
approve_form.finish = True
approve_form.put()
return resp_hanlder(result="OK!")
except Exception as err:
return resp_hanlder(code=999, err=err, msg=str(err))
def info(desc):
if request.method == "GET":
auth_token = request.headers.get('Authorization')
user_id = Users.decode_auth_token(auth_token)
form_status = request.json.get("form_status", 0)
page_limit = request.json.get("page_limit", 10)
page_num = request.json.get("page_num", 1)
try:
committed, pending, complete = {
"form_status_code": 1
}, {
"form_status_code": 2
}, {
"form_status_code": 3
}
if form_permission(user_id) == 1:
if form_status == 1 or form_status == 0:
committed_query = Forms.query.filter_by(
submit_dmp_user_id=user_id, approve_result=0)
committed["form_count"] = committed_query.count()
committed["page_num"] = page_num
committed["forms"] = [
f.__json__() for f in committed_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
if form_status == 3 or form_status == 0:
complete_query = Forms.query.filter(
Forms.submit_dmp_user_id == user_id,
Forms.approve_result != 0)
complete["form_count"] = complete_query.count()
complete["page_num"] = page_num
complete["forms"] = [
f.__json__() for f in complete_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
return resp_hanlder(result={
"committed": committed,
"complete": complete
})
elif form_permission(user_id) == 2:
if form_status == 1 or form_status == 0:
committed_query = Forms.query.filter_by(
submit_dmp_user_id=user_id, approve_result=0)
committed["form_count"] = committed_query.count()
committed["page_num"] = page_num
committed["forms"] = [
f.__json__() for f in committed_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
if form_status == 2 or form_status == 0:
pending_query = Forms.query.filter(
and_(
tuple_(Forms.submit_dmp_user_id, ).in_(
Users.query.with_entities(Users.id).filter_by(
leader_dmp_user_id=user_id).all()),
Forms.approve_result == 0))
pending["form_count"] = pending_query.count()
pending["page_num"] = page_num
pending["forms"] = [
f.__json__() for f in pending_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
if form_status == 3 or form_status == 0:
complete_query = Forms.query.filter(
and_(
or_(Forms.submit_dmp_user_id == user_id,
Forms.approve_dmp_user_id == user_id),
Forms.approve_result != 0))
complete["form_count"] = complete_query.count()
complete["page_num"] = page_num
complete["forms"] = [
f.__json__() for f in complete_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
return resp_hanlder(
result={
"committed": committed,
"pending": pending,
"complete": complete
})
elif form_permission(user_id) == 3:
if form_status == 2 or form_status == 0:
pending_query = Forms.query.filter_by(approve_result=0)
pending["form_count"] = pending_query.count()
pending["page_num"] = page_num
pending["forms"] = [
f.__json__() for f in pending_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
if form_status == 3 or form_status == 0:
complete_query = Forms.query.filter(
Forms.approve_result != 0)
complete["form_count"] = complete_query.count()
complete["page_num"] = page_num
complete["forms"] = [
f.__json__() for f in complete_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
return resp_hanlder(result={
"pending": pending,
"complete": complete
})
except Exception as err:
return resp_hanlder(code=999, err=err)
def submit_dmp_username(self):
from dmp.models import Users
u = Users.get(self.submit_dmp_user_id)
s_u_name = u.dmp_username if u else "-"
return s_u_name
def approve_dmp_username(self):
from dmp.models import Users
u = Users.get(self.approve_dmp_user_id)
a_u_name = u.dmp_username if u else "-"
return a_u_name
def dmp_user_name(self):
from dmp.models import Users
u = Users.get(self.dmp_user_id)
user_name = u.dmp_username if u else "-"
return user_name
def get_dashboards_and_archives(desc):
"""
查询多个文件夹和看板服务
---
tags:
- BI
parameters:
- name: upper_dmp_dashboard_archive_id
type: int
required: false
description: 父文件夹
- name: is_owner
type: bool
required: true
description: 是否是我的
- name: state
type: int
required: false
description: 发布状态
- name: name
type: string
required: true
description: 要检索的看板名或用户名
- name: pagenum
type: int
required: true
description: 页码
- name: pagesize
type: int
required: true
description: 每页内容数量
responses:
0:
description: OK!
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
# current_user_id = 1
request_json = request.json if request.json else {}
valid = Get_dashboards_and_archives_validator(request_json)
if not valid.is_valid():
return resp_hanlder(code=201, msg=valid.str_errors)
upper_dmp_dashboard_archive_id = request_json.get(
"upper_dmp_dashboard_archive_id", None)
is_owner = request_json.get("is_owner", False)
state = request_json.get("state", 3)
name = request_json.get("name", None)
pagenum = request_json.get("pagenum", 1)
pagesize = request_json.get("pagesize", 10)
dashboards_filters = {
Dashboard.upper_dmp_dashboard_archive_id ==
upper_dmp_dashboard_archive_id,
}
archives_filters = {
DashboardArchive.upper_dmp_dashboard_archive_id ==
upper_dmp_dashboard_archive_id,
}
if name != None:
dashboards_filters.clear()
archives_filters.clear()
dashboards_filters.add(
Dashboard.dmp_dashboard_name.like("%" + name + "%"))
archives_filters.add(
DashboardArchive.dashboard_archive_name.like("%" + name + "%"))
if is_owner == True:
dashboards_filters.add(
Dashboard.created_dmp_user_id == current_user_id)
archives_filters.add(
DashboardArchive.created_dmp_user_id == current_user_id)
if state != 3:
dashboards_filters.add(Dashboard.release == state)
name_subquery = db.session.query(
Users.id, Users.dmp_username.label("username")).subquery()
dashboards = db.session.query(
Dashboard.id.label("id"),
literal("dashboard").label("type"),
Dashboard.dmp_dashboard_name.label("name"),
Dashboard.description.label("description"),
Dashboard.release.label("release"),
db.session.query(DashboardStar.id).filter(
and_(DashboardStar.dmp_dashboard_id == Dashboard.id,
DashboardStar.dmp_user_id == current_user_id)).exists().label(
"is_star"),
db.session.query(DashboardStar.id).filter(
and_(UserDashboard.dmp_dashboard_id == Dashboard.id,
UserDashboard.dmp_user_id == current_user_id)).exists().label(
"is_index"),
# exists().where(and_(UserDashboard.dmp_dashboard_id ==Dashboard.id,UserDashboard.dmp_user_id==current_user_id)).label("is_index"),
Dashboard.upper_dmp_dashboard_archive_id.label(
"upper_dmp_dashboard_archive_id"),
Dashboard.created_dmp_user_id.label("created_dmp_user_id"),
name_subquery.c.username.label("created_dmp_user_name"),
# db.session.query(Users.dmp_username).filter(Users.id == Dashboard.created_dmp_user_id).subquery().c.dmp_username.label("created_dmp_user_name"),
Dashboard.changed_dmp_user_id.label("changed_dmp_user_id"),
name_subquery.c.username.label("changed_dmp_user_name"),
# db.session.query(Users.dmp_username.label("changed_dmp_user_name")).join(Dashboard,Users.id == Dashboard.changed_dmp_user_id).subquery(),
Dashboard.created_on.label("created_on"),
Dashboard.changed_on.label("changed_on")).outerjoin(
name_subquery,
Dashboard.changed_dmp_user_id == name_subquery.c.id).filter(
*dashboards_filters)
archives = db.session.query(
DashboardArchive.id.label("id"),
literal("archive").label("type"),
DashboardArchive.dashboard_archive_name.label("name"),
literal("-").label("description"),
literal(0).label("release"),
exists().where(
and_(ArchiveStar.dmp_archive_id == DashboardArchive.id,
ArchiveStar.dmp_user_id == current_user_id)).label("is_star"),
literal(0).label("is_index"),
DashboardArchive.upper_dmp_dashboard_archive_id.label(
"upper_dmp_dashboard_archive_id"),
DashboardArchive.created_dmp_user_id.label("created_dmp_user_id"),
name_subquery.c.username.label("created_dmp_user_name"),
# db.session.query(Users.dmp_username).filter(Users.id == Dashboard.created_dmp_user_id).subquery().c.dmp_username.label("created_dmp_user_name"),
DashboardArchive.changed_dmp_user_id.label("changed_dmp_user_id"),
name_subquery.c.username.label("changed_dmp_user_name"),
# db.session.query(Users.dmp_username.label("changed_dmp_user_name")).join(DashboardArchive,Users.id == DashboardArchive.changed_dmp_user_id).subquery(),
DashboardArchive.created_on.label("created_on"),
DashboardArchive.changed_on.label("changed_on")).outerjoin(
name_subquery,
DashboardArchive.changed_dmp_user_id == name_subquery.c.id).filter(
*archives_filters)
dashboards_and_archives = dashboards.union(archives)
# dashboards_and_archives = union(dashboards.alias("dashboards"), archives.alias("archives"))
# dashboards_and_archives = db.session.query([dashboards, archives]).select_entity_from(union(dashboards.select(), archives.select()))
# current_app.logger.info(dashboards_and_archives)
count = dashboards_and_archives.count()
data = [
d._asdict() for d in dashboards_and_archives.order_by(
desc_("is_index"), desc_("is_star"), desc_("changed_on"),
"type").offset((pagenum - 1) * pagesize).limit(pagesize)
]
res = {
"data_count": count,
"pagenum": pagenum,
"pagesize": pagesize,
"data": data
}
return resp_hanlder(result=res)
