待解决：图形中当改变分组时，图例不正确
存储比率计算时，分组合计不正确
wizard：过滤条件向导，用于输入过滤条件
wizard/sale_chart.py
# -*- coding: utf-8 -*-
from openerp.osv import fields,
osv
import datetime
class
dispatch_sale_chart(osv.osv_memory):
_name = "dispatch.sale.chart"
_description =
u"产品报表向导"
def _get_date_from(self,
cr, uid, context=None):
"""Return
default date_from value"""
DATE_FORMAT = "%Y-%m"
return
datetime.datetime.strftime(datetime.date.today(),
DATE_FORMAT)+'-01'
def
_get_date_to(self, cr, uid,
context=None):
"""Return default
date_to value"""
DATE_FORMAT =
"%Y-%m-%d"
return
datetime.datetime.strftime(datetime.date.today(),
DATE_FORMAT)
_columns =
{
'date_from':fields.date(u'日期从',
select=True, required=True),
'date_to':fields.date(u'日期至', select=True,
required=True),
'target_state':
fields.selection([('draft', u'草稿'),('confirmed', u'已确认'),('all',
u'全部')], u'选择状态', required=True), #暂未使用
}
_defaults
= {
'target_state':
'all',
'date_from':
_get_date_from,
'date_to':
_get_date_to,
}
#动态调用 模块 中的 action ,
组合默认过滤条件（domain）和默认分组（context）
def
dispatch_sale_chart_open_window(self, cr, uid, ids,
context=None):
mod_obj =
self.pool.get('ir.model.data')
act_obj =
self.pool.get('ir.actions.act_window')
if context is
None:
context = {}
data =
self.read(cr, uid, ids, [],
context=context)[0]
result = mod_obj.get_object_reference(cr, uid, 'kl_dispatch',
'action_dispatch_sale_profit_tree')
id = result and result[1] or False
result = act_obj.read(cr, uid, [id],
context=context)[0]
if
context.get('emp_profit', False):
#职员利润统计
result['context'] =
str({'group_by_no_leaf':1,'group_by':['department_id', 'employee_id']})
#默认按部门、职员分组，不显示明细
else:
result['context'] = str({'group_by_no_leaf':1,'group_by':['category',
'product']})
#默认按产品类别、产品名称分组，不显示明细
result['domain'] =
str([('date', '>=', data.get('date_from')), ('date', '<=',
data.get('date_to'))])
#日期查询条件
'''
if data.get('target_state') ==
'all':
result['domain'] = str([('date', '>=', data.get('date_from')), ('date',
'<=', data.get('date_to'))])
else:
result['domain'] = str([('date', '>=', data.get('date_from')), ('date',
'<=', data.get('date_to')), ('state', '=',
data.get('target_state'))])
'''
result['name'] = '利润统计('
+ data.get('date_from') + '至' + data.get('date_to') + ')' #显示页面标题
return result
dispatch_sale_chart()
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
wizard/sale_chart_view.xml
<?xml version="1.0"
encoding="utf-8"?>
<openerp>
<data>
<record
id="view_dispatch_sale_chart" model="ir.ui.view">
<field name="name">dispatch_sale_chart_form</field>
<field name="model">dispatch.sale.chart</field>
<field name="arch" type="xml">
<form string="销售报表向导" version="7.0">
<group>
<field name="date_from" class="oe_inline"/> -
<field name="date_to" class="oe_inline"/>
</group>
<footer>
<button string="查询报表" name="dispatch_sale_chart_open_window" type="object"
class="oe_highlight"/>
or
<button string="取消" class="oe_link"
special="cancel"/>
</footer>
</form>
</field>
</record>
<record
id="action_dispatch_sale_emp_profit_chart"
model="ir.actions.act_window">
<field
name="name">职员利润统计</field>
<field
name="res_model">dispatch.sale.chart</field>
<field
name="view_type">form</field>
<field
name="view_mode">tree,form</field>
<field name="view_id"
ref="view_dispatch_sale_chart"/>
<field
name="target">new</field>
<field name="context">{'emp_profit':True}</field>
<!--职员利润-->
</record>
<record
id="action_dispatch_sale_product_profit_chart"
model="ir.actions.act_window">
<field
name="name">产品利润统计</field>
<field
name="res_model">dispatch.sale.chart</field>
<field
name="view_type">form</field>
<field
name="view_mode">tree,form</field>
<field name="view_id"
ref="view_dispatch_sale_chart"/>
<field
name="target">new</field>
<field
name="context">{'product_profit':True}</field>
<!--产品利润-->
</record>
<menuitem name="利润统计"
id="menu_dispatch_sale_profit_chart" parent="menu_dispatch_report_root"
groups="group_dispatch_manager"
sequence="20"/>
<menuitem name="职员利润统计"
id="menu_dispatch_sale_emp_profit_chart"
action="action_dispatch_sale_emp_profit_chart"
parent="menu_dispatch_sale_profit_chart"
sequence="10"/>
<menuitem name="产品利润统计"
id="menu_dispatch_sale_product_profit_chart"
action="action_dispatch_sale_product_profit_chart"
parent="menu_dispatch_sale_profit_chart"
sequence="20"/>
</data>
</openerp>
report：分组报表、图形分析
report/sale_report.py
# -*- coding:utf-8 -*-
import pooler
import logging
import netsvc
import
tools
logger = netsvc.Logger()
import datetime
import time
import
math
from osv import fields,osv
from openerp.tools.translate import
_ #用于翻译代码中的静态字符串
class
dispatch_sale_report(osv.osv):
_name='dispatch.sale.report'
_description=u"销售单视图"
#增加产品类别的关联，用于查询报表
_auto =
False
_rec_name =
'name'
_columns={
'name':fields.char(u'单号', size=64,
readonly=True),
'date':fields.date(u'日期',
readonly=True),
'month':fields.char(_size=10, string = u'月份',
readonly=True),
"customer":fields.many2one("dispatch.customer", u"客户",
readonly=True),
"product":fields.many2one("dispatch.product", u"产品",
readonly=True),
'category':fields.many2one('product.category', u'产品分类',
readonly=True),
'price':fields.float(u'单价', digits=(18, 2),
readonly=True),
'num':fields.integer(u'数量',
readonly=True),
'cost':fields.float(u'成本', digits=(18, 2),
readonly=True),
'sum_sale':fields.float(digits=(18, 2), string = u'销售金额',
readonly=True),
'sum_cost':fields.float(digits=(18, 2), string = u'成本金额',
readonly=True),
'sum_profit':fields.float(digits=(18, 2), string = u'利润金额',
readonly=True),
'employee_id':fields.many2one('hr.employee',u'业务员',
readonly=True),
'department_id':fields.many2one('hr.department',u'部门',
readonly=True),
'klbh':fields.char(u'开龙单号',size=64,
readonly=True),
"work_order":fields.many2one("dispatch.work_order","服务单",
readonly=True),
'note':fields.text(u'备注',
readonly=True),
'state':
fields.selection([('draft', u'草稿'),('confirmed', u'已确认')], u'状态',
readonly=True),
'create_uid':
fields.many2one('res.users', u"创建用户", readonly=True),
}
def init(self,
cr):
tools.drop_view_if_exists(cr,
'dispatch_sale_report')
cr.execute("""
create or replace view dispatch_sale_report as
(
select sale.id, sale.name, sale.date, sale.month, sale.customer, sale.product,
product.category, sale.price,
sale.num,
sale.cost, sale.sum_sale, sale.sum_cost, sale.sum_profit, sale.employee_id,
sale.department_id,
sale.klbh, sale.work_order, sale.note, sale.state,
sale.create_uid
from dispatch_sale as
sale
inner join dispatch_product as product on (product.id =
sale.product)
)
""")
dispatch_sale_report()
report/sale_report_view.xml
<?xml
version="1.0"?>
<openerp>
<data>
<!-- views dispatch_sale_profit_tree
-->
<record model="ir.ui.view"
id="dispatch_sale_profit_tree">
<field
name="name">dispatch.sale.profit.tree</field>
<field
name="model">dispatch.sale.report</field>
<field
name="type">tree</field>
<field
name="priority">1</field>
<field name="arch"
type="xml">
<tree string="销售利润表"create="false" edit="false"
delete="false">
<field name="date"
invisible="1"/>
<field name="month"
invisible="1"/>
<field name="name"
invisible="1"/>
<field name="customer"
invisible="1"/>
<field name="category"
invisible="1"/>
<field name="product"
invisible="1"/>
<field name="num"
sum="合计"/>
<field name="price"
invisible="1"/>
<field name="cost"
invisible="1"/>
<field name="sum_sale"
sum="合计"/>
<field name="sum_cost"
sum="合计"/>
<field name="sum_profit"
sum="合计"/>
<field name="employee_id"
invisible="1"/>
<field name="department_id"
invisible="1"/>
<field name="work_order"
invisible="1"/>
<field name="klbh"
invisible="1"/>
<field name="state"
invisible="1"/>
</tree>
</field>
</record>
<record
id="dispatch_sale_profit_graph"
model="ir.ui.view">
<field
name="name">dispatch.sale.profit.graph</field>
<field
name="model">dispatch.sale.report</field>
<field name="arch"
type="xml">
<graphstring="销售利润表"type="bar">
<field
name="product"/>
<field
name="sum_profit"/>
<field name="category"
group="True"/>
</graph>
</field>
</record>
<!-- views
dispatch_sale_profit_filter -->
<record
model="ir.ui.view"
id="dispatch_sale_profit_filter">
<field
name="name">dispatch.sale.profit.filter</field>
<field
name="model">dispatch.sale.report</field>
<field
name="type">search</field>
<field name="arch"
type="xml">
<search
string="销售单">
<field
name="customer"/>
<field
name="category"/>
<field
name="product"/>
<field
name="department_id"/>
<field
name="employee_id"/>
<field
name="month"/>
<separator/>
<filter icon="terp-check" domain="[('state','=','draft')]" string="草稿"
name="draft"/>
<filter icon="terp-check" domain="[('state','=','confirmed')]" string="已确认"
name="confirm"/>
<filter
icon="terp-check" domain="[('state','in',('draft', 'confirmed'))]" string="全部"
name="all"/>
<group expand="0"
string="分组">
<filter string="产品类别" icon="terp-personal+" domain="[]"
context="{'group_by':'category'}"/>
<filter string="产品" icon="terp-personal+" domain="[]"
context="{'group_by':'product'}"/>
<filter string="客户" icon="terp-personal" domain="[]"
context="{'group_by':'customer'}"/>
<filter string="部门" icon="terp-personal+" domain="[]"
context="{'group_by':'department_id'}"/>
<filter string="员工" icon="terp-personal" domain="[]"
context="{'group_by':'employee_id'}"/>
<filter string="按月" icon="terp-personal" domain="[]"
context="{'group_by':'month'}"/>
<filter string="状态" icon="terp-stock_effects-object-colorize" domain="[]"
context="{'group_by':'state'}"/>
</group>
</search>
</field>
</record>
<!-- action 默认tree,graph视图 -->
<record model="ir.actions.act_window"
id="action_dispatch_sale_profit_tree">
<field
name="name">销售利润统计</field>
<field name="sequence"
eval="10"/>
<field
name="type">ir.actions.act_window</field>
<field
name="res_model">dispatch.sale.report</field>
<field
name="view_type">form</field>
<field
name="view_mode">tree,graph</field>
<field name="view_id"
ref="dispatch_sale_profit_tree"/>
<field name="search_view_id"
ref="dispatch_sale_profit_filter"/>
</record>
<!-- action 添加tree视图，可用于视图排序显示 -->
<record
model="ir.actions.act_window.view"
id="action_dispatch_sale_profit_tree1">
<field
name="sequence"
eval="10"/>
<field
name="view_mode">tree</field>
<field
name="view_id"
ref="dispatch_sale_profit_tree"/>
<field
name="act_window_id"
ref="action_dispatch_sale_profit_tree"/>
</record>
<!-- action 添加graph视图，可用于视图排序显示 -->
<record
model="ir.actions.act_window.view"
id="action_dispatch_sale_profit_graph">
<field
name="sequence"
eval="20"/>
<field
name="view_mode">graph</field>
<field
name="view_id"
ref="dispatch_sale_profit_graph"/>
<field
name="act_window_id"
ref="action_dispatch_sale_profit_tree"/>
</record>
</data>
</openerp>
report：数据库创建视图中计算比率
# -*- coding: utf-8 -*-
from openerp import tools
from openerp.osv
import fields, osv
class dispatch_transfer_order_report(osv.osv):
_name =
"dispatch.transfer_order.report"
_description =
u"转机单统计"
_auto = False
_rec_name =
'storefront'
_order = 'storefront'
_columns = {
'year':
fields.char(u'年', size=4,
readonly=True),
'month': fields.char(u'月',
size=8,readonly=True),
"storefront":fields.many2one("dispatch.storefront",u"店面",
readonly=True),
"sale_num":fields.float(u"销售数量",readonly=True),
"transfer_num":fields.float(u"转机数量",readonly=True
),
"transfer_rate":fields.float(u"转机率",readonly=True),
}
def init(self,
cr):
tools.drop_view_if_exists(cr,
'dispatch_transfer_order_report')
cr.execute("""
create or replace view dispatch_transfer_order_report as
(
select
min(transfer_order.id)
as
id,
storefront.id as
storefront,
to_char(transfer_order.date,
'YYYY') as
year,
transfer_order.month as
month,
sum(transfer_order.transfer_num) as
transfer_num,
sum(transfer_order.sale_num) as
sale_num,
sum(transfer_order.transfer_num)/sum(transfer_order.sale_num)
as
transfer_rate
from dispatch_transfer_order as
transfer_order
inner join dispatch_storefront as storefront on (storefront.id =
transfer_order.storefront)
group by
storefront.id,
to_char(transfer_order.date,
'YYYY'),
transfer_order.month
)
""")
dispatch_transfer_order_report()