里面用到了Mysql 里专有 DATE_FORMAT 函数来格式化日期，前端用 FusionCharts 来显示数据
def search_keydata(): tab_name = request.args.get('tab_name', '', type=str)#获取选项卡名 units = request.args.get('units', '', type=str)#获取统计单位 data_contrast = request.args.get('data_contrast', '', type=str)#获取数据对比标示符 apply_item = request.args.get('apply_item', '', type=str)#获取项目名称 datetime_start = request.args.get('datetime_start', '', type=str)#获取起始日期 datetime_end = request.args.get('datetime_end', '', type=str)#获取统计终止日期 #Python中还原JavaScript的escape函数编码后字符串 units = "".join([(len(i)>0 and unichr(int(i,16)) or "") for i in units.split('%u')]) if units == u'日': search_date_fromat = '%Y-%m-%d' elif units == u'月': search_date_fromat = '%Y-%m' else: search_date_fromat = '%Y' if datetime_start == '' or '-01': datetime_start = '1000-1-1' if tab_name == 'A': apply_item = "".join([(len(i)>0 and unichr(int(i,16)) or "") for i in apply_item.split('%u')]) if apply_item == '': search_data = db.session.query(func.DATE_FORMAT(ApplyRecord.applystarttime,search_date_fromat),\ func.COUNT(ApplyRecord.purpsoe))\ .filter(ApplyRecord.applystarttime >= datetime_start)\ .filter(ApplyRecord.applystarttime <= datetime_end)\ .group_by(func.DATE_FORMAT(ApplyRecord.applystarttime,search_date_fromat)).all() result_data = [{"label":data[0],"value":data[1]} for data in search_data] else: if data_contrast == 'N': search_data = db.session.query(func.DATE_FORMAT(ApplyRecord.applystarttime,search_date_fromat),\ func.COUNT(ApplyRecord.purpsoe))\ .filter(ApplyRecord.applystarttime >= datetime_start)\ .filter(ApplyRecord.applystarttime <= datetime_end)\ .filter(ApplyRecord.applyperson == apply_item)\ .group_by(func.DATE_FORMAT(ApplyRecord.applystarttime,search_date_fromat)).all() result_data = [{"label":data[0],"value":data[1]} for data in search_data] else: search_data = db.session.query(func.DATE_FORMAT(ApplyRecord.applystarttime,search_date_fromat),\ func.COUNT(ApplyRecord.purpsoe),\ func.SUM(case([(ApplyRecord.applyperson==apply_item,'1')])))\ .filter(ApplyRecord.applystarttime >= datetime_start)\ .filter(ApplyRecord.applystarttime <= datetime_end)\ .group_by(func.DATE_FORMAT(ApplyRecord.applystarttime,search_date_fromat)).all() search_data_arr_T = (array(search_data)).T search_date = search_data_arr_T[0].tolist() data_sum_num = search_data_arr_T[1].tolist() data_single_user_num = search_data_arr_T[2].tolist() data_sum_num = [0 if data == None else data for data in data_sum_num] data_single_user_num = [0 if data == None else data for data in data_single_user_num] category_dict_list = [{"label":data} for data in search_date] data_sum_num_dict_list = [{"value":data} for data in data_sum_num] data_single_user_num_dict_list = [{"value":data} for data in data_single_user_num] result_data = [] result_data.append(category_dict_list) result_data.append(data_sum_num_dict_list) result_data.append(data_single_user_num_dict_list) else: if apply_item == '': saearch_first = db.session.query(func.DATE_FORMAT(ApplyNum.applydate,search_date_fromat).label("d"),\ (func.SUM(ApplyNum.applynum)/func.COUNT(func.SUBSTRING_INDEX(ApplyNum.purpose,'(',1))).label("n"))\ .filter(ApplyNum.applydate >= datetime_start)\ .filter(ApplyNum.applydate <= datetime_end)\ .group_by(func.SUBSTRING_INDEX(ApplyNum.purpose,'(',1)).subquery() search_data = db.session.query(saearch_first.c.d,\ func.SUM(saearch_first.c.n))\ .group_by(saearch_first.c.d).all() else: saearch_first = db.session.query(func.DATE_FORMAT(ApplyNum.applydate,search_date_fromat).label("d"),\ (func.SUM(ApplyNum.applynum)/func.COUNT(func.SUBSTRING_INDEX(ApplyNum.purpose,'(',1))).label("n"))\ .filter(ApplyNum.modeltype == apply_item)\ .filter(ApplyNum.applydate >= datetime_start)\ .filter(ApplyNum.applydate <= datetime_end)\ .group_by(func.SUBSTRING_INDEX(ApplyNum.purpose,'(',1)).subquery() search_data = db.session.query(saearch_first.c.d,\ func.SUM(saearch_first.c.n))\ .group_by(saearch_first.c.d).all() # search_data = db.session.query(func.DATE_FORMAT(ApplyNum.applystarttime,search_date_fromat),\ # func.SUM(ApplyNum.applynum))\ # .func.COUTN(func.DISTINCT(func.SUBSTRING_INDEX(ApplyNum.purpsoe,'(',1)))\ # .filter(ApplyNum.applystarttime >= datetime_start)\ # .filter(ApplyNum.applystarttime <= datetime_end)\ # .group_by(func.DATE_FORMAT(ApplyNum.applystarttime,search_date_fromat)).all() result_data = [{"label":data[0],"value":float(data[1])} for data in search_data] result_data = json.dumps({'search_data':result_data}) return result_data
