def olappermission_add():
# 配置权限
result = 'fail'
code = 0
message = '保存失败'
success = False
from myolap.utils.authutils import get_current_username
current_username = get_current_username()
if current_username is None or current_username.strip() != 'admin':
#不是登录用户
return json_result(result=result,
code=0,
message='无操作权限',
success=False)
formdata = json.loads(request.get_data())
olap_id = formdata['olap_id']
username = formdata['username']
from myolap.model.sysdbmodel import OlapDimensionPermission, multi_to_list, multi_to_list_by_columns
try:
u = g.session.query(OlapDimensionPermission).filter(
OlapDimensionPermission.username == username).filter(
OlapDimensionPermission.olap_id == olap_id).first()
if u is None:
# 新增
u = OlapDimensionPermission()
u.olap_id = formdata['olap_id']
g.session.add(u)
#更新字段内容
u.username = formdata['username']
u.col_permission = json.dumps(formdata['col_permission'])
u.row_permission = json.dumps(formdata['row_permission'])
u.create_by = current_username
u.version = formdata['version']
g.session.commit()
result = 'ok'
message = '保存成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def olapconfig_checkfield():
#检查olap多维表的字段配置是否正确
result = 'fail'
code = 0
message = '检查字段失败'
success = False
try:
formdata = json.loads(request.get_data())
from myolap.model.sysdbmodel import OlapModelConfig, OlapModelConfigMetadata
u = g.session.query(OlapModelConfig).filter(
OlapModelConfig.model_name == formdata['model_name']).first()
if u is None:
# 校验失败 模型没找到
message = '模型校验失败，没有找到模型'
else:
#提交请求到对应数据源，查看是否执行成功
sql = 'select {} from ( {} limit 1 ) x '.format(
formdata['sql_express'], u.model_sql)
from myolap.dataquery.dbhelper import get_data_by_dsandsql
r = dbhelper.get_data_by_dsandsql(u.datasource_name, sql)
if r is not None:
code = 200
message = '校验成功'
result = 'ok'
success = True
else:
message = '模型校验失败'
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def modelconfig_list():
#目前不支持分頁
# pageNo = request.args.get("pageNo")
# pageSize = request.args.get("pageSize")
result = 'fail'
code = 0
message = '获取列表失败';
success = False
from myolap.app import sysdb
from myolap.model.sysdbmodel import OlapModelConfig, multi_to_list
try:
modellist = g.session.query(OlapModelConfig).all()
list = multi_to_list(modellist)
result = {
"current": 0,
"pages": 0,
"records": list,
"size": 0,
"total": 0
}
success= True
message = '获取列表成功'
except Exception as e:
message = 'error: %s' %(e)
current_app.logger.error('error: %s' %(e))
return json_result(result=result, code=0, message=message,success=success)
def olapconfig_shortcut_list():
# 检查olap多维表的字段配置是否正确
result = 'fail'
code = 0
message = '获取快捷键信息失败'
success = False
try:
from myolap.utils.authutils import get_current_username
username = get_current_username()
olap_id = request.args.get("olap_id")
from myolap.model.sysdbmodel import OlapDimensionShortcut, multi_to_list, multi_to_list_by_columns
modellist = g.session.query(OlapDimensionShortcut)\
.with_entities(OlapDimensionShortcut.id,OlapDimensionShortcut.olap_id,OlapDimensionShortcut.shortcut_name)\
.filter(OlapDimensionShortcut.create_by == username).filter(OlapDimensionShortcut.olap_id == olap_id).all()
list = multi_to_list_by_columns(modellist,
['id', 'olap_id', 'shortcut_name'])
result = {
"current": 0,
"pages": 0,
"records": list,
"size": 0,
"total": 0
}
message = '查询信息成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def olapconfig_querydimension():
result = 'fail'
code = 0
message = '查询维度信息失败'
success = False
try:
formdata = json.loads(request.get_data())
print(formdata)
result = get_dimension_items_mysql(formdata)
message = '查询维度信息成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def olapconfig_shortcut_delete():
#删除某个快捷键
result = 'fail'
code = 0
message = '删除信息失败'
success = False
try:
shortcut_id = request.args.get("shortcut_id")
from myolap.model.sysdbmodel import OlapDimensionShortcut
g.session.query(OlapDimensionShortcut).filter(
OlapDimensionShortcut.id == shortcut_id).delete()
g.session.commit()
result = 'OK'
message = '删除数据成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def modelconfig_delete():
result = 'fail'
code = 0
message = '删除信息失败';
success = False
id = request.args.get("id")
try:
from myolap.model.sysdbmodel import OlapModelConfig,OlapModelConfigMetadata
g.session.query(OlapModelConfigMetadata).filter(OlapModelConfigMetadata.pid == id).delete()
g.session.query(OlapModelConfig).filter(OlapModelConfig.id==id).delete()
g.session.commit();
result = 'ok'
message='删除数据成功'
success= True
except Exception as e:
message = 'error: %s' %(e)
current_app.logger.error('error: %s' %(e))
return json_result(result=result, code=0, message=message,success=success)
def olapconfig_get():
# 检查olap多维表的字段配置是否正确
result = 'fail'
code = 0
message = '获取多维表信息失败'
success = False
try:
olap_id = request.args.get("olap_id")
per = get_permission(olap_id)
from myolap.model.sysdbmodel import OlapDimension
u = g.session.query(OlapDimension).filter(
OlapDimension.olap_id == olap_id).first()
if u is None:
result = {
"checked_list": [],
"filter_list": {},
"dimension_name": '',
"olap_id": olap_id
}
message = '未查询到相关信息'
success = True
else:
#进行列限制
allow_cols = per['col_permission']
checked_list = json.loads(u.checked_list)
filter_list = json.loads(u.filter_list)
remove_not_allow_field(allow_cols, checked_list, filter_list)
result = {
"checked_list": checked_list,
"dimension_name": u.dimension_name,
"filter_list": filter_list,
"olap_id": u.olap_id
}
message = '查询信息成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def olapconfig_list():
# 检查olap多维表的字段配置是否正确
result = 'fail'
code = 0
message = '获取快捷键信息失败'
success = False
try:
from myolap.utils.authutils import get_current_username
#当前登录用户
username = get_current_username()
from myolap.model.sysdbmodel import OlapDimension, OlapDimensionPermission, multi_to_list, multi_to_list_by_columns2
# 只查询指定的字段
# create_by = request.args.get("create_by")
if username == 'admin':
print('admin........')
modellist = g.session.query(OlapDimension).all()
else:
print('普通用户')
modellist = g.session.query(OlapDimension).filter(
OlapDimension.create_by == username).all()
list = multi_to_list_by_columns2(
modellist, ['olap_id', 'dimension_name', 'create_by'],
'permission')
result = {
"current": 0,
"pages": 0,
"records": list,
"size": 0,
"total": 0
}
message = '查询信息成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def olapconfig_shortcut_save():
#检查olap多维表的字段配置是否正确
result = 'fail'
code = 0
message = '保存快捷键信息失败'
success = False
try:
from myolap.utils.authutils import get_current_username
from myolap.model.sysdbmodel import OlapDimensionShortcut
username = get_current_username()
formdata = json.loads(request.get_data())
u = g.session.query(OlapDimensionShortcut).filter(
OlapDimensionShortcut.id == formdata['shortcut_id']).first()
if u is None:
# 新增
u = OlapDimensionShortcut()
u.id = formdata['shortcut_id']
g.session.add(u)
#更新字段内容
u.shortcut_name = formdata['shortcut_name']
u.olap_id = formdata['olap_id']
u.create_by = username
u.version = formdata['version']
str_checked_list = json.dumps(formdata['checked_list'])
str_filter_list = json.dumps(formdata['filter_list'])
u.checked_list = str_checked_list
u.filter_list = str_filter_list
g.session.commit()
result = 'ok'
message = '保存快捷键信息成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def olapconfig_delete():
# 删除某张多维表
result = 'fail'
code = 0
message = '获取快捷键信息失败'
success = False
try:
olap_id = request.args.get("id")
print('delete................')
print(olap_id)
# from myolap.app import sysdb
from myolap.model.sysdbmodel import OlapDimension
g.session.query(OlapDimension).filter(
OlapDimension.olap_id == olap_id).delete()
g.session.commit()
result = 'OK'
message = '删除成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def olapconfig_shortcut_get():
# 检查olap多维表的字段配置是否正确
result = 'fail'
code = 0
message = '获取快捷键信息失败'
success = False
try:
shortcut_id = request.args.get("shortcut_id")
from myolap.model.sysdbmodel import OlapDimensionShortcut
u = g.session.query(OlapDimensionShortcut).filter(
OlapDimensionShortcut.id == shortcut_id).first()
if u is None:
result = {
"checked_list": [],
"filter_list": {},
"shortcut_name": '',
"olap_id": '',
"shortcut_id": ''
}
message = '未查询到快捷键信息'
success = True
else:
result = {
"checked_list": u.checked_list,
"shortcut_name": u.shortcut_name,
"filter_list": u.filter_list,
"olap_id": u.olap_id,
"shortcut_id": u.id
}
message = '查询信息成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def olapconfig_query():
#目前不支持分頁
result = 'fail'
code = 0
message = '查询数据失败'
success = False
try:
formdata = json.loads(request.get_data())
#根据formdata 拼装sql
pdresult = get_data_mysql(formdata)
result = {
"data":
json.loads(
pdresult.to_json(orient='records',
date_format='iso',
double_precision=2))
}
message = '查询数据成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
def olappermission_delete():
# 删除某张多维表
result = 'fail'
code = 0
message = '保存失败'
success = False
from myolap.utils.authutils import get_current_username
current_username = get_current_username()
if current_username is None or current_username.strip() != 'admin':
# 不是登录用户
return json_result(result=result,
code=0,
message='无操作权限',
success=False)
olap_id = request.args.get("olap_id")
usernames = request.args.get("usernames").split(',')
if olap_id is None or usernames is None:
return json_result(result=result,
code=0,
message='参数无效',
success=False)
from myolap.model.sysdbmodel import OlapDimensionPermission
try:
if len(usernames) > 0:
for username in usernames:
g.session.query(OlapDimensionPermission).filter(
OlapDimensionPermission.olap_id == olap_id).filter(
OlapDimensionPermission.username == username).delete()
g.session.commit()
result = 'OK'
message = '删除成功'
success = True
except Exception as e:
message = 'error: %s' % (e)
current_app.logger.error('error: %s' % (e))
return json_result(result=result, code=0, message=message, success=success)
# @handler.route("/olap/olappermission/getuserlist", methods=['GET'])
# def olappermission_getuserlist():
# #获取某个olap多维表的权限列表
# olap_id = request.args.get("olap_id")
# from myolap.model.sysdbmodel import OlapDimensionPermission,multi_to_list_by_columns
# modellist= g.session.query(OlapDimensionPermission).with_entities(OlapDimensionPermission.username, OlapDimensionPermission.olap_id).filter(OlapDimensionPermission.olap_id==olap_id).all()
# list = multi_to_list_by_columns(modellist, ['olap_id','username'])
#
# message = '查询信息成功'
#
# return json_result(list, code=0, message=message)
# @handler.route("/olap/olappermission/get", methods=['GET'])
# def olappermission_get():
# # 获取某个olap多维表的权限列表
# olap_id = request.args.get("olap_id")
# username = request.args.get("username")
#
# from myolap.model.sysdbmodel import OlapDimensionPermission, multi_to_list_by_columns
# u = g.session.query(OlapDimensionPermission).filter(OlapDimensionPermission.olap_id == olap_id).filter(OlapDimensionPermission.username == username).first()
# if u is None:
# result = {
# "col_permission": [],
# "row_permission": [],
# "username": username,
# "olap_id": olap_id
# }
# else:
# result = {
# "col_permission": u.col_permission,
# "row_permission": u.row_permission,
# "username": username,
# "olap_id": olap_id
# }
# message = '查询信息成功'
#
# return json_result(result, code=0, message=message)
def modelconfig_preview():
"""
参数json字符串格式为：
{
"datasource_name": "adb",
"model_name": "ad_dianji_01",
"model_sql": "",
"page_size": 10,
"sql": "",
"type": 0
}
:return:
返回结果外层和其他统一，内部一个data，表示查询的数据列；另外一个metadata，是各个列的元数据
{
"code": 200,
"message": "查询数据成功",
"result": {
"data": [
{
"adagent": "耀广",
"adclicks": 6,
"adexposurecnt": 149,
"adnetname": "头条天",
"appkey": "1514446452050",
"cost": 0.47,
"devicetype": "2",
"dt": "2019-08-30",
"spreadname": "AND天头条041"
}
],
"metadata": [
{
"columnAlias": "devicetype",
"columnType": 0,
"modelId": 0,
"modelName": "ad_dianji_01",
"sqlField": "devicetype",
"sqlFieldType": "字符串"
},
{
"columnAlias": "cost",
"columnType": 2,
"modelId": 0,
"modelName": "ad_dianji_01",
"sqlField": "cost",
"sqlFieldType": "数值"
}
]
},
"success": true,
"timestamp": 1591262086161
}
"""
#pymysql 返回字段类型编码值
# DECIMAL = 0
# TINY = 1
# SHORT = 2
# LONG = 3
# FLOAT = 4
# DOUBLE = 5
# NULL = 6
# TIMESTAMP = 7
# LONGLONG = 8
# INT24 = 9
# DATE = 10
# TIME = 11
# DATETIME = 12
# YEAR = 13
# NEWDATE = 14
# VARCHAR = 15
# BIT = 16
# JSON = 245
# NEWDECIMAL = 246
# ENUM = 247
# SET = 248
# TINY_BLOB = 249
# MEDIUM_BLOB = 250
# LONG_BLOB = 251
# BLOB = 252
# VAR_STRING = 253
# STRING = 254
# GEOMETRY = 255
result = 'fail'
code = 0
message = '预览数据失败';
success = False
try:
formdata = json.loads(request.get_data())
from myolap.dataquery.dbhelper import get_meta_by_dsandsql
sql = ' {} limit 10 '.format(formdata['model_sql'])
# 修改 通过pymysql cusor 获取元数据
# df = get_data_by_dsandsql(formdata['datasource_name'], sql )
meta, df = get_meta_by_dsandsql(formdata['datasource_name'], sql)
from pymysql.constants import FIELD_TYPE
from myolap.utils.const import _const
metalist = []
for m in meta:
str_field_type= _const.SQL_FIELD_TYPE_STR
if m[1] == FIELD_TYPE.DATE or m[1] ==FIELD_TYPE.DATETIME or m[1] ==FIELD_TYPE.NEWDATE :
str_field_type= _const.SQL_FIELD_TYPE_DATE
elif m[1] <= 9:
str_field_type = _const.SQL_FIELD_TYPE_NUM
else:
pass
metadata = {}
metadata['model_id'] = 0;
metadata['model_name'] = formdata['model_name'];
metadata['sql_field'] = m[0];
metadata['sql_field_type'] =str_field_type;
metalist.append(metadata)
print(metalist)
result = {
"data": json.loads(df.to_json(orient='records', date_format='iso')),
"metadata": metalist
}
message ='预览数据成功'
success = True
except Exception as e:
message = 'error: %s' %(e)
current_app.logger.error('error: %s' %(e))
return json_result(result=result, code=0, message=message,success=success)
def modelconfig_metadata():
"""
参数json字符串格式为：
{
}
:return:
返回结果外层和其他统一，内部一个data，表示查询的数据列；另外一个metadata，是各个列的元数据
{
"code": 200,
"message": "查询数据成功",
"result": [
{
"datasource_name": "datasource_name",
"db_type": "db_type",
"metadata": [
{
"column_alias": "devicetype",
"column_type": 0,
"model_id": 0,
"model_name": "ad_dianji_01",
"sql_field": "devicetype",
"sql_field_type": "字符串"
},
{
"column_alias": "cost",
"column_type": 2,
"model_id": 0,
"model_name": "ad_dianji_01",
"sql_field": "cost",
"sql_field_type": "数值"
}
],
"model_name": "aaa",
"model_sql": "model_sql"
}
],
"success": true,
"timestamp": 1591262086161
}
"""
# from myolap.app import sysdb
from myolap.model.sysdbmodel import OlapModelConfig, OlapModelConfigMetadata, multi_to_list, single_to_dict
resultlist = {}
modellist = g.session.query(OlapModelConfig).all()
for model in modellist:
smodel = single_to_dict(model)
metalist=[]
for meta in model.meta_data:
metadict = single_to_dict(meta)
metadict['model_name'] = model.model_name
metadict['datasource_name'] = model.datasource_name
metadict['db_type'] = model.db_type
# metadict['model_sql'] = model.model_sql
metalist.append(metadict)
smodel["meta_data"] =metalist
resultlist[model.model_name]= metalist
result = resultlist
return json_result(result,code=0,message='查询成功')
def modelconfig_save():
"""
参数json字符串格式为：
{
"datasource_name": "mysqltest",
"db_type": "mysql",
"id": 26,
"metadata": [
{
"column_alias": "gamechannel",
"column_type": 0,
"model_id": 0,
"model_name": "hooh_1",
"sql_field": "gamechannel",
"sql_field_ype": "字符串"
},
{
"column_alias": "serverid",
"column_type": 0,
"model_id": 0,
"model_name": "hooh_1",
"sqlFieldType": "字符串",
"sql_field": "serverid"
},
{
"column_alias": "dcnt",
"column_type": 2,
"model_id": 0,
"model_name": "hooh_1",
"sql_field": "dcnt",
"sql_field_ype": "数值"
}
],
"model_name": "hooh_1",
"model_sql": "SELECT gamechannel,serverid,COUNT(DISTINCT deviceid) AS dcnt FROM h1_first_login WHERE serverid IN ('3990','1301') GROUP BY gamechannel,serverid ",
"type": 0
}
:return:
返回结果
"""
result = 'fail'
code = 0
message = '保存数据失败';
success = False
try:
print(request.get_data())
formdata = json.loads(request.get_data())
from myolap.model.sysdbmodel import OlapModelConfig,OlapModelConfigMetadata
id = formdata['id']
u = g.session.query(OlapModelConfig).filter(OlapModelConfig.id == id).first()
if u is None:
#新增
u = OlapModelConfig()
g.session.add(u)
else:
#删除原来数据
for m in u.meta_data:
g.session.delete(m)
#更新字段内容
u.model_name = formdata['model_name']
u.datasource_name = formdata['datasource_name']
u.db_type = formdata['db_type']
u.model_sql = formdata['model_sql']
g.session.commit()
u.meta_data =[]
for meta in formdata['metadata']:
obj = OlapModelConfigMetadata()
obj.sql_field = meta['sql_field']
obj.sql_field_type = meta['sql_field_type']
obj.pid = u.id
g.session.add(obj)
g.session.commit()
result = 'ok'
message ='保存信息成功'
success=True
except Exception as e:
message = 'error: %s' %(e)
current_app.logger.error('error: %s' %(e))
return json_result(result=result, code=0, message=message,success=success)