def exp_excel():
vl_id = request.args.get('vl_id')
votetype = request.args.get('votetype')
filename = request.args.get('filename')
if int(votetype) == 1:
# lists = get_graduate_excel(vl_id)
q = db.session.query(Graduateresult.s_id.label('id'),
Graduateresult.g_agreenum.label('毕业同意数'),
Graduateresult.g_disagreenum.label('毕业不同意数'),
Graduateresult.g_abstained.label('毕业弃权数'),
Graduateresult.d_agreenum.label('授予硕士学位同意数'),
Graduateresult.d_disagreenum.label('授予硕士学位不同意数'),
Graduateresult.d_abstained.label('授予硕士学位弃权数'),
Masterstudents.name.label('姓名'),
Masterstudents.account.label('学号'),
Masterstudents.major.label('学科、领域'),
Masterstudents.title.label('论文题目'),
Masterstudents.tutor.label('导师'),
Masterstudents.college.label('所在培养单位'),
Masterstudents.thesisurl.label('链接')).filter(
Graduateresult.s_id == Masterstudents.s_id,
Graduateresult.vl_id == vl_id)
query_sets = q.all()
return excel.make_response_from_query_sets(
query_sets,
column_names=[
'姓名',
# '专业',
'学科、领域',
'所在培养单位',
# '学科、领域',
'毕业同意数',
'毕业不同意数',
'毕业弃权数',
'授予硕士学位同意数',
'授予硕士学位不同意数',
'授予硕士学位弃权数',
],
file_type='xlsx',
file_name=filename if filename != None else '数据' + '.xlsx')
else:
# lists = get_excellent_excel(vl_id)
q = db.session.query(Masterstudents.name.label('姓名'),
Masterstudents.college.label('学科、领域'),
Masterstudents.title.label('论文题目'),
Masterstudents.tutor.label('指导教师'),
Excellentresult.agreenum.label('同意数量'),
Excellentresult.disagreenum.label('不同意数'),
Excellentresult.abstained.label('弃权')).filter(
Excellentresult.vl_id == vl_id,
Masterstudents.s_id == Excellentresult.s_id)
query_sets = q.all()
return excel.make_response_from_query_sets(
query_sets,
column_names=['姓名', '学科、领域', '论文题目', '指导教师', '同意数量', '不同意数', '弃权'],
file_type='xlsx',
file_name=filename if filename != None else '数据' + '.xlsx')
def export_service():
list = Service.query.all()
column_names = ['name', 'description']
return excel.make_response_from_query_sets(list,
column_names,
"xls",
file_name="service_data")
def doexport():
column_names = ['feedback', 'rating', 'user_id', 'commment_date']
company_review = Comment.query.filter(
(Comment.companyName == current_user.name)).all()
return excel.make_response_from_query_sets(company_review, column_names,
"xls")
def export_label():
list = Label.query.all()
column_names = ['id', 'name', 'description']
return excel.make_response_from_query_sets(list,
column_names,
"xls",
file_name="marque_data")
def docustomexport():
query_sets = Category.query.filter_by(id=1).all()
column_names = ["id", "name"]
return excel.make_response_from_query_sets(query_sets,
column_names,
"xls",
dest_sheet_name="custom_sheet")
def station_events_download(station_id):
start = int(request.args.get('start', 0))
search = request.args.get('search[value]', '')
date_start = request.args.get('date_start', None)
date_end = request.args.get('date_end', None)
event_type = request.args.get('event_type', None)
length = int(request.args.get('length', -1))
events, total_count = get_log_events(station_id=station_id,
start=start,
search=search,
date_start=date_start,
date_end=date_end,
event_type=event_type,
length=length)
column_names = ["date", "category", "action", "content"]
excel.init_excel(current_app)
events_list = []
file_name = "log_{}".format(station_id)
for v in [event_type, date_start, date_end]:
if v:
file_name += "_{}".format(v)
file_name += ".xlsx"
return excel.make_response_from_query_sets(events.all(),
column_names,
"xlsx",
file_name=file_name)
def story_to_excel():
# story_list = db.session.query(Story.story_id,Story.story_url).all()
# column_names = ['story_id', 'story_url']
# # story_list = Story.query.filter_by()
# story_updatas = story_list
# flask_excel.make_response_from_query_sets(story_updatas,column_names=['id','url'],file_name='test',file_type='xlsx')
pagination = db.session.query(
StoryChapter.chapter_id.label('章节'), Story.story_id.label('id'),
Story.story_url.label('url')).outerjoin(
Story, Story.story_id == StoryChapter.story_id).filter(
Story.story_id == '39651').paginate(
1,
per_page=current_app.config['FLASKY_POSTS_PER_PAGE'],
error_out=False)
# story_list = db.session.query(Story.story_id.label('id'),Story.story_url.label('url')).all()
column_names = ['章节', 'id', 'url']
story_list = pagination.items
return excel.make_response_from_query_sets(story_list,
column_names,
file_name='test',
file_type='xlsx')
# return excel.make
# return excel.make_response_from_query_sets(story_list, column_names, file_name='test',file_type='xlsx')
return excel.make_response_from_array(story_list, "xlsx", column_names)
def export_category():
list = Category.query.all()
column_names = ['id', 'name', 'description']
return excel.make_response_from_query_sets(list,
column_names,
"csv",
file_name="category")
def export_results():
# Requires initialization of Excel library done in __init__.py to work properly
# Report is a global variable that holds the last run report query from the reports page
if report is None:
t3exceptions.Error(
"Attempted to export CSV before report query was run. Did the button display too early?"
)
else:
for r in report:
if r.department == "1":
r.department = "Information"
elif r.department == "2":
r.department = "Youth"
else:
t3exceptions.Error(
"Unable to parse department, was a new one added?")
if r.referral == 0:
r.referral = "No"
elif r.referral == 1:
r.referral = "Yes"
else:
t3exceptions.Error(
"Unable to convert referrals to strings when exporting CSV"
)
today = datetime.datetime.now().strftime("%Y.%m.%d")
f_name = today + "_Tally3-Report"
output = make_response_from_query_sets(
report,
['date', 'time', 'department', 'q_type', 'referral', 'notes'],
"csv",
file_name=f_name)
return output
def export():
if request.method == 'GET':
user = UserController.find_by_id(request.args.get("user_id"))
query_sets = CompanyController.get_items(user.user_handle_industry)
column_names = CompanyController.get_columns_name()
return excel.make_response_from_query_sets(query_sets, column_names,
"xlsx")
def doexport0():
query_sets0 = HangboardWerk.query.filter_by(user_id=current_user.id).all()
column_names0 = [
'arm_used', 'board', 'hangtime', 'holds_used', 'name', 'reps',
'resttime', 'timestamp', 'user_id', 'weight_kg'
]
return excel.make_response_from_query_sets(query_sets0, column_names0,
"xls")
def doexport4():
query_sets4 = Blocs.query.filter_by(user_id=current_user.id).all()
column_names4 = [
'angle', 'comments', 'grade', 'intensity', 'name', 'style',
'timestamp', 'venue', 'werktime'
]
return excel.make_response_from_query_sets(query_sets4, column_names4,
"xls")
def doexport3():
query_sets3 = CircuitMoves.query.filter_by(user_id=current_user.id).all()
column_names3 = [
'comments', 'grade', 'intensity', 'name', 'numberofmoves', 'timestamp',
'werktime'
]
return excel.make_response_from_query_sets(query_sets3, column_names3,
"xls")
def dl_file(process_id):
query_sets = DHTData.query.filter_by(process_id=process_id).all()
column_names = [
'time_stamp', 'temp', 'temp1', 'temp2', 'temp3', 'hum', 'hum1', 'hum2',
'hum3'
]
return excel.make_response_from_query_sets(
query_sets, column_names, "xls", file_name="{}".format(process_id))
def exp_excel():
drugs = db.session.query(Drug.id.label('药品编码'), Drug.name.label('药品名称'),
Drug.pro.label('药品厂商'), Drug.num.label('药品数量'),
Drug.price.label('药品价格')).order_by(Drug.id).all()
return excel.make_response_from_query_sets(
drugs,
column_names=['药品编码', '药品名称', '药品厂商', '药品数量', '药品价格'],
file_type='xlsx',
file_name='list.xlsx')
def download():
register = session.query(Register).all()
column_names = ['id', 'name', 'surname', 'email', 'branch', 'mobile']
res = fe.make_response_from_query_sets(register,
column_names,
file_type="csv",
status=200,
file_name="student")
return res
def export_equipment():
list = Equipment.query.all()
column_names = [
'category_id', 'label_id', 'service_id', 'model', 'serial', 'name',
'arrived_at', 'description'
]
return excel.make_response_from_query_sets(list,
column_names,
"xls",
file_name="equipment_data")
def export():
users = User.query.filter(User.username != 'admin').all()
column_names = [
'initial_name', 'first_name', 'last_name', 'username', 'email',
'password', 'active'
]
return excel.make_response_from_query_sets(users,
column_names,
"xls",
file_name="users.xls")
def export_out():
list = Trainee.query.all()
column_names = [
'registration_number', 'first_name', 'last_name', 'level_id',
'unit_id', 'school', 'email', 'phone', 'theme'
]
return excel.make_response_from_query_sets(list,
column_names,
"xls",
file_name="trainee_data")
def exp_excel():
excel.init_excel(app)
query_sets = Notes.query.filter(Notes.owner == current_user.username).all()
column_names = ['id', 'title', 'content', 'link', 'owner']
return excel.make_response_from_query_sets(
query_sets,
column_names,
file_type='xlsx',
file_name=current_user.username + '\'s_Notes_' +
time.strftime("%Y%m%d", time.localtime()) + '.xlsx')
def exp_excel():
q = db.session.query(
Activationlist.ip.label('ip'),
Activationlist.Id.label('Id'),
Activationlist.created_time.label('created_time'),
Activationlist.updated_time.label('updated_time')
).order_by(Activationlist.updated_time.asc())
query_sets = q.all()
excel.make_response_from_query_sets(
query_sets,
column_names=[
'ip',
'Id',
'created_time',
'updated_time',
],
file_type='xlsx',
file_name='activationlist',
)
def doexport():
col = [
'resource_type', 'booking_ref', 'description', 'update_on', 'location'
]
qs = Reference.query.join(Resource, Reference.resource_id == Resource.id).add_columns( \
Reference.resource_type, Reference.booking_ref, Resource.description, \
Reference.update_on, Resource.location).filter( \
Reference.resource_id != None)
# qs = Resource.query.filter(Resource.booking_ref != None)
return excel.make_response_from_query_sets(qs, col, "csv")
def get(self):
"""Download asset categories with their assets count"""
asset_categories = AssetCategory._query(request.args).all()
column_names = ['name', 'assets_count']
return excel.make_response_from_query_sets(
asset_categories,
column_names,
'csv',
file_name=f'Asset Categories Export - {date.today()}')
def get(self, public_id):
"""Download attendance session"""
column_names = ['student_id', 'first_name', 'last_name']
response = get_students(public_id)
if response[1] == 201:
return flask_excel.make_response_from_query_sets(
response[0],
column_names,
"xlsx",
file_name="course_attendance.xlsx")
else:
return response
def get(self, group_id, group_name, alias, user_id):
"""Download user attendance"""
column_names = ['registration_id', 'first_name', 'last_name']
response = get_checkedout_users(group_id, group_name, alias, user_id)
if response[1] == 201:
return flask_excel.make_response_from_query_sets(
response[0],
column_names,
"xlsx",
file_name="{}_attendance.xlsx".format(alias))
else:
return response
def export():
query_sets = Issue.query.all()
return excel.make_response_from_query_sets(
query_sets,
column_names=[
'id', 'key', 'status', 'pro_status_id', 'created_time', 'summary',
'creator', 'url', 'ui_schedule', 'back_schedule', 'front_schedule',
'test_schedule', 'ui_staff', 'back_staff', 'front_staff',
'test_staff', 'is_pro', 'resolutiondate', 'updated'
],
file_type='xlsx',
file_name='全部需求.xlsx')
def exp_voterin_excel():
vl_id = request.args.get('vl_id')
filename = request.args.get('filename')
# lists = get_graduate_excel(vl_id)
q = db.session.query(Voter.teacher_account.label('教工号'),
Voter.nickname.label('委员姓名')).filter(
Voterin.vl_id == vl_id,
Voter.id == Voterin.voter_id)
query_sets = q.all()
return excel.make_response_from_query_sets(
query_sets,
column_names=['教工号', '委员姓名'],
file_type='xlsx',
file_name=filename if filename != None else '数据' + '.xlsx')
def export_class_data(class_id):
if current_user.is_authenticated and app.models.is_admin(current_user.username):
# Check if current user is registered as a class manager
if app.classes.models.check_if_turma_id_belongs_to_a_teacher (class_id, current_user.id) is False:
abort (403)
query_sets = db.session.query(User).join(
Enrollment, Enrollment.user_id == User.id).filter(
Enrollment.turma_id == class_id).order_by(User.student_number.asc()).all()
class_object = Turma.query.get(class_id)
filename = class_object.turma_label + ' - ' + class_object.turma_term + ' ' + str(class_object.turma_year)
column_names = ['student_number', 'username', 'email']
return excel.make_response_from_query_sets(query_sets, column_names, "xlsx", file_name = filename)
abort (403)
def CloudReportGeneration():
conn = pypyodbc.connect(connString, autocommit=True)
cursor = conn.cursor()
sqlcmd1 = "SELECT cloudProviderID, SUM(fileSize) FROM VideoData GROUP BY cloudProviderID ORDER BY cloudProviderID"
cursor.execute(sqlcmd1)
records = []
while True:
dbrow = cursor.fetchone()
if not dbrow:
break
print(1)
conn3 = pypyodbc.connect(connString, autocommit=True)
cursor3 = conn3.cursor()
temp = str(dbrow[0])
sqlcmd3 = "SELECT cloudProviderName, storageSpace, usageCostPerGB FROM CloudProviderData WHERE cloudProviderID = '"+temp+"'"
print(sqlcmd3)
cursor3.execute(sqlcmd3)
cloudProviderRow = cursor3.fetchone()
if cloudProviderRow:
print(1)
cloudReportModelObj = CloudReportModel()
cloudReportModelObj.cloudProviderName = cloudProviderRow[0]
cloudReportModelObj.storageSpace = float(cloudProviderRow[1])
cloudReportModelObj.usageCostPerGB = float(cloudProviderRow[2])
totalUsedStorage = dbrow[1]
if totalUsedStorage > 0:
print(1)
totalUsedStorage = totalUsedStorage / (1024 * 1024 * 1024)
print(2)
totalUsageCost = totalUsedStorage * float(cloudProviderRow[2])
print(3)
cloudReportModelObj.totalUsedStorage = totalUsedStorage
print(4)
cloudReportModelObj.totalUsageCost = totalUsageCost
reprow = []
reprow.append(cloudReportModelObj.cloudProviderName)
reprow.append(cloudReportModelObj.storageSpace)
reprow.append(cloudReportModelObj.usageCostPerGB)
reprow.append(cloudReportModelObj.totalUsedStorage)
reprow.append(cloudReportModelObj.totalUsageCost)
records.append(reprow)
print(">>>>>>>qw dsad w>>>>>>>>>>>>asdsdsadas>>", "%.5f" % (cloudReportModelObj.totalUsedStorage) , "%.5f" % cloudReportModelObj.totalUsageCost)
cursor.close()
conn.close()
return excel.make_response_from_query_sets(records, ['Cloud Provider Name', 'Available Space', 'Cost Per GB', 'Used Space', "Usage Cost"], "xls", file_name="export_data")
def export_csv(project_id):
sub = aliased(Label)
q = LabeledClip.query.join(LabeledClip.label).join(
sub, LabeledClip.sub_label,
isouter=True).join(User).join(AudioFile).join(
Equipment, AudioFile.sn ==
Equipment.serial_number).join(MonitoringStation).filter(
MonitoringStation.project_id == project_id).with_entities(
AudioFile.path, LabeledClip.file_name, LabeledClip.offset,
Label.name.label('label'), sub.name.label('sub_label'),
LabeledClip.certain, LabeledClip.notes,
LabeledClip.modified, User.name.label('user')).all()
column_names = [
'path', 'file_name', 'offset', 'label', 'sub_label', 'certain',
'notes', 'modified', 'user'
]
return excel.make_response_from_query_sets(q, column_names, 'csv')
def station_events_download(station_id):
start = int(request.args.get('start', 0))
search = request.args.get('search[value]', '')
date_start = request.args.get('date_start', None)
date_end = request.args.get('date_end', None)
event_type = request.args.get('event_type', None)
length = int(request.args.get('length', -1))
events, total_count = get_log_events(station_id=station_id, start=start, search=search, date_start=date_start,
date_end=date_end, event_type=event_type, length=length)
column_names = ["date", "category", "action", "content"]
excel.init_excel(current_app)
events_list = []
file_name = "log_{}".format(station_id)
for v in [event_type, date_start, date_end]:
if v:
file_name += "_{}".format(v)
file_name += ".xlsx"
return excel.make_response_from_query_sets(events.all(), column_names, "xlsx", file_name=file_name)
def teacher_export():
teachers=Teacher.query.filter_by(shop_id=current_user.shop_id)
column_names = ['firstname', 'lastname','sex','phone','wx','course','fee','resume']
return flask_excel.make_response_from_query_sets(teachers,column_names, "xls")
def student_export():
students=Student.query.filter_by(shop_id=current_user.shop_id)
column_names = ['firstname', 'lastname','course','sex','phone1','school','province','city','area','address','parent','phone2']
return flask_excel.make_response_from_query_sets(students,column_names, "xls")
