def update_report():
id = None
try:
id = request.form.get('id')
except:
pass
print('id:', id)
template = 'null'
data = ''
# 报告id
# 添加数据
if id == None:
logger.debug('id 为空,添加报告数据..')
# 新建需要提供模板和标题
try:
template = request.form.get('template', 'null')
title = request.form.get('title')
recordid = request.form.get('recordid')
except Exception as e:
logger.warning('update_report 更新,缺少参数', exc_info=True)
nowtime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
# 有模板表示要创建模板
if template != 'null':
query = zs.query.filter(zs.recordid == recordid)
data = get_report_template(query, template, recordid)
pass
report = Report(id=id,
data=data,
title=title,
template=template,
time=nowtime,
userid=current_user.get_id())
else:
report = Report.query.filter(
and_(Report.id == id,
Report.userid == current_user.get_id())).first()
if report == None:
return rjson('id %d 不存在', 1)
# 更新有的数据
if 'data' in request.form.keys():
report.data = request.form['data']
if 'title' in request.form.keys():
report.title = request.form['title']
if 'template' in request.form.keys():
report.template = request.form['template']
logger.debug('更新数据...')
report.userid = current_user.id
db.session.add(report)
db.session.commit()
return rjson('更新数据成功', 1)
def loginuser():
logger.debug(request.form.to_dict())
id = request.form['id']
password = request.form['password']
#验证是否登录成功
if id!=password:
return rjson('用户名或密码错误',1)
user = User.query.filter(User.id==id).first()
if user==None:
return rjson('用户不存在(User not exists!)',1)
logger.debug('登录用户:%s', user)
#登录用户，添加用户到session中，需要传入User对象
login_user(user)
logger.debug('登录,user:%s,password:%s',id,password)
# return rjson('登录成功',1)，重定向到报告界面,
url = url_for('report.index')
print(url)
return redirect(url)
def importdata():
zsyear = request.form['zsyear']
recordid = request.form['recordid']
field_columns = {}
for col in needcolumns_fields:
field_columns[col] = request.form[col]
# 将选择的列保存起来，下次可以直接读取，str将所有变成字符串再join
fields = ",".join(map(lambda x: str(x), field_columns.values()))
print("字段和 输入列的关系:", field_columns)
record = Record.query.filter(
and_(Record.id == recordid,
Record.userid == current_user.get_id())).first()
file = os.path.join('upload', record.filename)
# excel转换为字典,只提取fields这些列名的
excel_data = excel2list(file, columns=field_columns.values())
try:
#
print("删除原始数据...")
deleteresult = zs.query.filter(zs.zsyear == zsyear).delete()
print("删除结果:", deleteresult)
size = 0
for row in excel_data:
params = {}
for key in field_columns.keys():
params[key] = row[field_columns[key]]
params['zsyear'] = zsyear
params['recordid'] = record.id
#params['userid']=current_user.get_id()
zsitem = zs(**params)
db.session.add(zsitem)
size += 1
print("开始提交数据...")
# 更新记录数和选择的字段
record.size = size
record.fields = fields
record.status = '已导入数据'
print("更新数据:", record)
db.session.commit()
return rjson("导入数据成功,共导入" + str(size) + "条数据", 0)
except Exception as e:
print("发生异常:回滚数据", e)
db.session.rollback()
return rjson("导入数据失败:" + str(e), 1)
def upload():
pass
print(request.files)
print(request.args)
print(request.form)
# 文件名名
tablefile = request.files['table'].filename
zsyear = request.form['zsyear']
# filename_py=pinyin.get_pinyin( tablefile)
# 文件名暂时用年份来表示
# --------------------------检查年的存不存在
# result = Record.query.filter( and_( Record.id==recordid, Record.userid==current_user.get_id()) ).first()
#
# if result:
# return rjson( zsyear+ "年的数据已存在",1)
filename_py = zsyear + os.path.splitext(tablefile)[1]
# 指定保存的文件名为拼音处理后的
filename = upload_tables.save(request.files['table'], name=filename_py)
print(filename)
ab = os.path.abspath(os.path.join("upload", filename))
print("保存路径:", ab)
try:
# parse_csv(ab,zsyear)
record = Record(id=0,
time=datetime.now(),
zsyear=zsyear,
status="未导入数据",
filename=filename_py,
userid=current_user.get_id())
print(record)
# 尝试解析数据
raw_columns = ','.join(get_columns(getUploadPath(filename)).keys())
record.raw_fields = raw_columns
db.session.add(record)
print("开始提交数据....")
db.session.commit()
except Exception as e:
print("发生异常,rollback回滚数据:", e)
db.session.rollback()
return rjson("上传失败:" + str(e), 1)
pass
return rjson("上传成功.")
def delete_record(id):
try:
item = Record.query.filter(
and_(Record.id == id,
Record.userid == current_user.get_id())).first()
# 先删除招生数据再删除记录
zss = zs.query.filter(zs.zsyear == item.zsyear).delete()
result = db.session.delete(item)
print("删除结果:", zss)
db.session.commit()
except Exception as e:
return rjson("错误:{}".format(str(e)), 1)
return rjson("删除成功", 0)
def options(type):
logger.debug('获取内置options,类型:%s' % type)
# 招生数据
if type == 'zs':
sql = zs.query.filter(zs.recordid == 3)
chart = zschart(sql, 1)
options = chart.options()
# chart1 = drawChart(sql, 'bar', 'group', 'sex_name,departments', 'count_total_score_of_filing', 'null',
# 'null', -1)
# print("返回option",options)
return rjson(options, 0)
def delete_reprots(id):
logger.debug('删除报告:%s', id)
try:
item = Report.query.filter(
and_(Report.id == id,
Report.userid == current_user.get_id())).first_or_404()
logger.debug(item)
item.isdelete = 1
db.session.add(item)
db.session.commit()
except Exception as e:
logger.warning('删除报告异常:' + str(e))
return rjson('删除成功', 1)
def export(id):
if not os.path.exists(export_dir):
logger.debug('建立下载文件夹:%s', export_dir)
os.makedirs(export_dir)
from app.report.models import Report
report = Report.query.with_entities(
Report.id, Report.data, Report.title,
func.unix_timestamp(
Report.title).label('time')).filter(Report.id == id).first()
filename = '%s_%s_%s.docx' % (report.id, report.title, report.time)
path = os.path.join(export_dir, filename)
# 报告的数据
jsondata = report.data
jiexi_result = doc.jiexi(jsondata, report.title, path)
logger.debug('导出结果:%s', jiexi_result)
if os.path.exists(path):
return rjson('导出成功', 0)
else:
return rjson('导出失败', 1)
def get_fields():
# 现在只有一个
print('获取填充字段....请求参数', request.args)
id = None
if 'id' in request.args.keys():
id = request.args['id']
type = request.args['type']
#预览数据
preview_data = []
# 选择的字段
values = []
#没有id代表不加载预览数据
if id != None:
nowrecord = Record.query.filter(
and_(Record.id == id, Record.userid == current_user.id)).first()
filename = os.path.join("upload", nowrecord.filename)
inputcolumns = get_columns(filename, previewsize=10)
for field, values in inputcolumns.items():
preview_data.append({'field': field, 'values': values})
values = nowrecord.fields
# fields是空的话,就进行推断,将需要的字段 和 输入的字段进行匹配
if values == None:
values = caculateFields(need_columns, inputcolumns)
else:
values = values.split(",")
vue_fields = []
if type == 'zs':
for field, name, pattern in zip(needcolumns_fields, needcolumns_name,
needcolumns_pattern):
vue_fields.append({
'field': field,
'name': name,
'desc': name,
'pattern': pattern
})
return rjson(
{
'fields': vue_fields,
'values': values,
'preview_data': preview_data
}, 0)
def data():
# 找出没有被删除的,isdelete>0的
data = Report.query.filter(
Report.userid == current_user.get_id()).with_entities(
Report.id, Report.title,
func.date_format(Report.time,
'%Y-%m-%d %H:%i:%S').label('time')).filter(
or_(Report.isdelete < 1,
Report.isdelete == None))
logger.debug('获取数据,sql' + str(data))
data = data.all()
result = []
for item in data:
result.append({'title': item.title, 'id': item.id, 'time': item.time})
return rjson(result, 0)
def get_records():
datasets = Record.query.filter(
Record.userid == current_user.get_id()).all()
# 查询到的是Record对象，不能序列化
result = []
for data in datasets:
print(dir(data))
result.append({
"id": data.id,
"time": data.time,
"zsyear": data.zsyear,
"status": data.status
})
#
return rjson(result, 0)
def preview(id):
# 返回类型，返回json还是html表格
type = request.args['type']
# item = Record.query.filter( and_( Record.id==id,Record.userid==current_user.get_id()) ).first()
# zsyear = item.zsyear
students = zs.query.filter(zs.recordid == id).order_by(
func.rand()).limit(10).all()
logger.debug('获取预览学生信息,记录id %s,:%s', id, students)
if type == 'json':
return rjson(students, 0)
else:
return render_template('admin/preview.html',
students=students,
need_column=need_columns)
def charts2():
print('表单:', request.form)
print('args:', request.args)
if 'recordid' not in request.form:
return rjson('recordid 没有选择', 1)
# 4个参数
try:
recordid = request.form['recordid']
charttype = request.form['chartType']
# datatype = request.form['dataType']
groupfield = request.form['groupfield']
aggfield = request.form['aggfield']
filter = request.form['filter']
except Exception as e:
return rjson(str(e) + " " + str(e.args), 1)
orderBy = request.form['orderBy']
limit = request.form['limit']
logger.debug(dir(request.form))
logger.debug('charts2 参数:%s' % request.form.to_dict())
sql = zs.query.filter(zs.recordid == recordid)
result = drawChart(sql, charttype, 'group', groupfield, aggfield, filter,
orderBy, limit)
# 如果返回的是元组，则表示返回的是错误信息
if type(result) == tuple:
return rjson(result[0], result[1])
if result == None:
return rjson("返回null", 1)
# 如果是画图
if charttype != 'table':
# option装字典
result_dict = json.loads(result.dump_options())
params = {}
for key in [
'chartType', 'groupfield', 'aggfield', 'orderBy', 'filter',
'limit', 'dataType'
]:
if key in request.form:
params[key] = request.form[key]
# 要返回报告的数据结构
item = ReportItem('chart', 0, 400, 400, 1, **params)
# 设置生成的option
item.option = result_dict
pass
elif charttype == 'table':
# 直接是返回dataset
print('表格类型:')
print(result)
params = {}
for key in [
'chartType', 'groupfield', 'aggfield', 'orderBy', 'filter',
'limit', 'dataType'
]:
if key in request.form:
params[key] = request.form[key]
# 要返回报告的数据结构
item = ReportItem('table', 0, 800, 400, 1, **params)
# 设置生成的option
item.rows = result
pass
# x=[]
# y=[]
# a = All_Picture()
# options = a.getchart( db,zsyear,chartid)
#
# sql = zs.query.with_entities(zs.sex_name,func.count()).group_by(zs.sex_name)
# print(sql)
# dataset = sql.all()
# for item in dataset:
# x.append(item[0])
# y.append(item[1])
#
# result = a.bar_picture(x, y)
# result_dict = json.loads(result)
# test = db.session.execute("select sex_name,count(1) from zs group by sex_name;").fetchall()
#
# print(test)
# print(dir(test))
return rjson(item.to_dict(), 0)
def get_reports(id):
report = Report.query.filter(
and_(Report.id == id,
Report.userid == current_user.get_id())).first_or_404()
return rjson({'id': report.id, 'data': report.data})