def get_table_data():
'''
show data from database
'''
table_name = request.form.get('name')
project_number = session.get('project_number') if session.get(
'project_number') else ''
if request.method == 'POST' and table_name:
'''
#分页
page = request.values.get('page') if request.values.get('page') else 1
# show all data
rows = request.values.get('rows') if request.values.get('page') else 100
offset = (int(page) - 1) * int(rows)
'''
#排序
sort_col = request.values.get('sort') if request.values.get(
'sort') else 'sample_name'
order_type = request.values.get('order') if request.values.get(
'order') else 'asc'
data = []
i = 0
db = DBConn()
cmd = "select * from {table} where project_id = '{project_number}' order by {sort_col} {order}".format(
table=table_name,
project_number=project_number,
sort_col=sort_col,
order=order_type)
results = db.execute(cmd)
for result in results:
data.append(dict(result))
i += 1
return jsonify({'total': i, 'rows': data})
return redirect(request.referrer)
def upload_return_table():
if request.method == 'POST':
file = request.files['file']
if file and interface.allow_files(file.filename):
filename = secure_filename(file.filename)
if not os.path.exists(app.config['UPLOAD_FOLDER']):
os.makedirs(app.config['UPLOAD_FOLDER'])
file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
data = interface.transfer_excel_to_json2(file_name=filepath)
if not data:
flash('your xlsx have more sheets!')
return redirect(request.referrer)
table_name = interface.check_table(data[0].keys())
if table_name != 'return_table':
flash('it not {file_name},please try again!'.format(
file_name='return_table'))
return redirect(request.referrer)
db = DBConn()
for each_record in data:
db.insert('return_table', each_record)
return redirect(request.referrer)
elif file == '':
flash('no select file!')
return redirect(request.referrer)
else:
flash('only .xls or .xlsx file required!')
return redirect(request.referrer)
def export_table_info(table_name, project_id, title_list):
book = Workbook()
sheet1 = book.worksheets[0]
# connect database:
db = DBConn()
cmd = "select * from {table_name} where project_id = '{project_id}'".format(table_name=table_name,
project_id=project_id)
results = db.execute(cmd)
all_table_data = []
for result in results:
all_table_data.append(dict(result))
for i, title in enumerate(title_list):
sheet1.cell(row=1, column=i + 1).value = title
for i, data in enumerate(all_table_data):
for j, title in enumerate(title_list):
sheet1.cell(row=i + 2, column=j + 1).value = data.get(header_map[title])
export_path = os.path.join(os.path.abspath(os.path.dirname(__file__)),'static/export/')
file_name = table_name + '_' + project_id + '_' + datetime.datetime.now().strftime("%Y%m%d%H%M%S") + '.xls'
full_path_name = os.path.join(export_path, file_name)
if not os.path.exists(export_path):
os.mkdir(export_path)
open(full_path_name,'w+').close()
book.save(filename=full_path_name)
return file_name
def get_table_data():
if request.method == 'POST':
table_name = request.values.get('name',None)
#分页
page = request.values.get('page') if request.values.get('page') else 1
rows = request.values.get('rows') if request.values.get('page') else 10
offset = (int(page) - 1) * int(rows)
#排序
sort_col = request.values.get('sort') if request.values.get('sort') else 'sample_name'
order_type = request.values.get('order') if request.values.get('order') else 'asc'
if table_name:
data = []
i = 0
db = DBConn()
cmd = 'select * from {table} order by {sort_col} {order} limit {offset},{rows}'.format(
table=table_name,
sort_col=sort_col,
order=order_type,
offset=offset,
rows=rows)
results = db.execute(cmd)
for result in results:
data.append(dict(result))
i += 1
return jsonify({'total':i,'rows':data})
else:
return redirect(url_for('index'))
return redirect(url_for('index'))
def destroy_all_sample():
project_number = session.get('project_number') if session.get(
'project_number') else ''
table_name = request.args.get('table')
db = DBConn()
ans = db.delete(table_name, condition_dict={'project_id': project_number})
if ans:
return jsonify({'success': 'true', 'errMsg': 'error'})
else:
return jsonify({'success': 'false', 'errMsg': 'del fail!'})
def del_select_data():
sample_name = request.args.get('id')
sample_time = request.args.get('time')
table_name = request.args.get('table')
db = DBConn()
ans = db.delete(table_name, condition_dict={'sample_name': sample_name})
if ans:
return jsonify({'success': 'true', 'errMsg': 'error'})
else:
return jsonify({'success': 'false', 'errMsg': 'del fail!'})
def upload_file():
if request.method == 'POST':
file = request.files['file']
if file and allow_files(file.filename):
filename = secure_filename(file.filename)
if not os.path.exists(app.config['UPLOAD_FOLDER']):
os.makedirs(app.config['UPLOAD_FOLDER'])
file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
data = transfer_excel_to_json2(file_name=filepath)
#input to database:
db = DBConn()
for each_record in data:
db.insert('send_sample_table',each_record)
return redirect(url_for('index'))
elif file == '':
flash('no select file!')
return redirect(url_for('index'))
else:
flash('only .xls or .xlsx file required!')
return redirect(url_for('index'))
def save_input_data():
if request.method == 'POST':
action = request.form.get('action',None)
table_name = request.form.get('table_name',None)
#just test two tables
send_sample_table_dict = dict.fromkeys(['project_id','sample_name',
'species','extract_part',
'sample_number','comment','time'])
quality_inspection_table_dict = dict.fromkeys(['project_id','sample_name','sample_id',
'od_260_or_230','od_260_or_280','25S_or_18S',
'rin','volume','concentration','quantum',
'database_type','judgeresult','comment','time'])
up_machine_table_dict = dict.fromkeys(['project_id','sample_name',
'upmachine_type','upmachine_mode','up_quant',
'comment','time'])
down_machine_table_dict = dict.fromkeys(['project_id','sample_name','sample_id','down_quant',
'reads','q30','comment','time'])
return_table_dict = dict.fromkeys(['project_id','sample_name','species','surplus','comment','time'])
all_table_dicts = dict(send_sample_table=send_sample_table_dict,
quality_inspection_table=quality_inspection_table_dict,
up_machine_table=up_machine_table_dict,
down_machine_table=down_machine_table_dict,
return_table=return_table_dict)
send_sample_row = None
for each_table,table_content in all_table_dicts.items():
if table_name == each_table:
send_sample_row = all_table_dicts[each_table]
for key,value in send_sample_row.items():
send_sample_row[key] = request.form.get(key,None)
db = DBConn()
if action == 'insert' and send_sample_row:
db.insert(table_name, send_sample_row)
return jsonify({'success': 'true', 'errMsg': 'error'})
elif action == 'update' and send_sample_row:
try:
db.update(table_name,
condition_dict={'sample_id':send_sample_row['sample_id']},
update_dict=send_sample_row)
except Exception,e:
db.update(table_name,
condition_dict={'sample_name': send_sample_row['sample_name']},
update_dict=send_sample_row)
return jsonify({'success': 'true', 'errMsg': 'error'})
else:
return jsonify({'success': 'false', 'errMsg': 'error'})