用oracle如何生成报表,基于 oracle 的 flask 项目（三）——生成报表

曾经，因为对 sqlalchemy 不熟悉，固执的抛弃之，直接引入原生的 cx_Oracle 来生成报表，但是经过一堆的“上下文等”错误后，我幡然悔悟，还是回到了 sqlalchemy 的怀抱来。

认真学习 sqlalchemy，你会发现意想不到的惊喜。本文主要讲解如何生成不同需求的报表。

项目描述

第一个页面，以 OusiStaff.phone 和 OusiGuest.staff_phone 为关联，查询出本月职工的关联客户及客户的资产余额。

第二个页面，以 OusiStaff.phone 和 OusiGuest.staff_phone 为关联，查询出每个职工所有关联的客户的本月资产、上月资产及本月管户资产增额。

具有管理员权限的员工可以看到本部门下面所有员工的相关信息，非管理员权限的员工只能查看自己的相关信息。

查询功能留给下一章详细讲解。

建立报表模板

不在赘述。详情请下载代码查看。

实现第一个页面

建立第一个页面的试图函数

第一个页面使用sqlalchemy查询比较简单，关键点是：

区分权限。

联表查询。

建立上个月的虚字段。

分页显示。

查询嵌套。

views.py 内容：

@show.route('/01', methods = ['GET', 'POST'])

@login_required

def _01():

g1 = aliased(OusiGuest) # 重复用到该数据库，需要设置别名。

g2 = aliased(OusiGuest) # 重复用到该数据库，需要设置别名。

page = request.args.get('page', 1, type=int) # 分页

if current_user.role == 'admin': # 区分权限

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,

g1.month == date.today().strftime('%Y-%m')

).order_by(g1.name).group_by(OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone,

OusiStaff.role,

g1.name.label('guest_name'), g1.month, g1.balance)

else:

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.phone==g1.staff_phone,

g1.month==date.today().strftime('%Y-%m')

).order_by(g1.name).group_by(OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone,

OusiStaff.role,

g1.name.label('guest_name'), g1.month, g1.balance)

data = database.paginate(page, per_page=current_app.config['OUSI_POSTS_PER_PAGE'], error_out=False)

return render_template('show/01.html', data=data)

其实，以上内容就是利用 sqlalchemy 的联表查询问题，将 OusiStaff 和 OusiGuest 以 phone 值为关联进行查询，然后将结果显示出来。

其中的难点一是利用 func.to_date 和 func.add_months 对上个月这个时间进行关联查询(凡是数据库支持的函数，在 sqlalchemy 中都可以使用 func 加函数名来操作。)，二是利用 func.nvl 函数来对初始化数据进行赋值 0 的操作。 具体的 sqlalchemy 语法，请参见代码。

提示： 注意 sqlalchemy 的语法。 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') ，这语句的主要是为了实现查询上个月的余额，如果查询不到，通过 oracle 的 nvl 函数设置为 0 。

第一个页面显示结果

权限为 admin 的用户显示本部门下所有员工的相关信息。

权限为 general 的用户只显示本人的相关信息。

实现第二个页面

建立第二个页面的试图函数

第二页使用 sqlalchemy 查询稍微复杂点，关键点除了第一个页面的要求外还有：

使用 func.sum 对相关字段进行相加运算。

用到了子查询。

views.py 内容：

@show.route('/02', methods = ['GET', 'POST'])

@login_required

def _02():

page = request.args.get('page', 1, type=int)

g1 = aliased(OusiGuest)

g2 = aliased(OusiGuest)

if current_user.role == 'admin':

sbq = db.session.query(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),

g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance,

func.nvl(db.session.query(g2.balance).filter(

g1.name==g2.name,

func.add_months(func.to_date(g1.month, 'yyyy-mm'), -1) == func.to_date(g2.month,

'yyyy-mm')

), 0).label('last_balance')).filter(

OusiStaff.phone==g1.staff_phone,

OusiStaff.department==current_user.department

).group_by(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),

g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance).subquery()

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(sbq.c.month==date.today().strftime('%Y-%m')).group_by(sbq.c.department, sbq.c.role,

sbq.c.staff_name, sbq.c.staff_phone,

sbq.c.month)

else:

sbq = db.session.query(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),

g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance,

func.nvl(db.session.query(g2.balance).filter(

g1.name == g2.name,

func.add_months(func.to_date(g1.month, 'yyyy-mm'), -1) == func.to_date(g2.month,

'yyyy-mm')

), 0).label('last_balance')).filter(

OusiStaff.phone == g1.staff_phone,

OusiStaff.phone == current_user.phone

).group_by(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),

g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance).subquery()

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(sbq.c.month == date.today().strftime('%Y-%m')).group_by(sbq.c.department, sbq.c.role,

sbq.c.staff_name, sbq.c.staff_phone,

sbq.c.month)

data = database.paginate(page, per_page=current_app.config['OUSI_POSTS_PER_PAGE'], error_out=False)

return render_template('show/02.html', data=data)

注意 query 的字段以及 group_by 里分组查询的字段。

第二个页面显示结果

权限为 admin 的用户显示本部门下所有员工的相关信息。

权限为 general 的用户只显示本人的相关信息。

下节更精彩，将实现搜索查询功能，又是一堆 sqlalchemy 语句。