搜索功能是一个项目个性化需求最强烈的部分,用户想要不同的报表,可以通过搜索不同的字段来实现。
项目描述
实现搜索页面
在试图函数中实现相关搜索功能
views.py
中的内容如下:
@show.route('/01',methods = ['GET','POST']) @login_required def _01(): search_form = SearchForm(prefix='search') ... if current_user.role == 'admin': ... ... if search_form.validate_on_submit(): database = db.session.query(OusiStaff.department,OusiStaff.name.label('staff_name'),OusiStaff.phone,OusiStaff.role,g1.name.label('guest_name'),g1.month,g1.balance,func.nvl(db.session.query(g2.balance).filter( g1.name==g2.name,func.to_date(g2.month,'yyyy-mm')==func.add_months( func.to_date(g1.month,'yyyy-mm'),-1) ),0).label('last_balance') ).filter( OusiStaff.phone == g1.staff_phone,current_user.department==OusiStaff.department,and_(g1.month.between(search_form.start_time.data.strip(),search_form.end_time.data.strip()),OusiStaff.name.like('%{}%'.format(search_form.name.data.strip())),OusiStaff.phone.like('%{}%'.format(search_form.phone.data.strip()))) ).order_by(g1.name).group_by(OusiStaff.department,g1.balance) ... else: ... ... if search_form.validate_on_submit(): database = db.session.query(OusiStaff.department,func.nvl(db.session.query(g2.balance).filter( g1.name == g2.name,'yyyy-mm') == func.add_months( func.to_date(g1.month,current_user.phone == OusiStaff.phone,g1.month.between(search_form.start_time.data.strip(),search_form.end_time.data.strip()) ).order_by(g1.name).group_by(OusiStaff.department,g1.balance) ... ... return render_template('show/01.html',data=data,searchForm=search_form)
这是第一个页面的的搜索功能实现方法,使用了 if current_user.role == 'admin' else ...
来区分权限。
@show.route('/02','POST']) @login_required def _02(): search_form = SearchForm(prefix='search') ... ... if current_user.role == 'admin': ... ... if search_form.validate_on_submit(): database = db.session.query(sbq.c.department,sbq.c.role,sbq.c.staff_name,sbq.c.staff_phone,sbq.c.month,func.count(sbq.c.guest_name).label('members'),func.sum(sbq.c.balance).label('balance'),func.sum(sbq.c.last_balance).label('last_balance')). \ filter(and_(sbq.c.month.between(search_form.start_time.data.strip(),sbq.c.staff_name.like('%{}%'.format(search_form.name.data.strip())),sbq.c.staff_phone.like('%{}%'.format(search_form.phone.data.strip()))) ).group_by(sbq.c.department,sbq.c.month) else: ... ... if search_form.validate_on_submit(): database = db.session.query(sbq.c.department,func.sum(sbq.c.last_balance).label('last_balance')). \ filter(sbq.c.month.between(search_form.start_time.data.strip(),search_form.end_time.data.strip())).group_by(sbq.c.department,sbq.c.month) ... ... return render_template('show/02.html',searchForm=search_form)
结果展示
第一个页面
第二个页面
总结:
本实现方法还是很繁琐的,大家如果对 sql 语句很熟悉的话,在区分权限的时候,应该知道case ... when ... else ... end
这个语句,如何把这样的 sql 语句 sqlalchemy 化,还得精进自己的技能,如果你恰好会这样的技能,请通知我一声。