1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
7 # This program is free software: you can redistribute it and/or modify
8 # it under the terms of the GNU Affero General Public License as
9 # published by the Free Software Foundation, either version 3 of the
10 # License, or (at your option) any later version.
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU Affero General Public License for more details.
17 # You should have received a copy of the GNU Affero General Public License
18 # along with this program. If not, see <http://www.gnu.org/licenses/>.
20 ##############################################################################
22 from openerp.osv import fields, osv
24 from openerp import netsvc
25 from openerp import tools
27 class report_transaction_pos(osv.osv):
28 _name = "report.transaction.pos"
29 _description = "transaction for the pos"
32 'date_create': fields.char('Date', size=16, readonly=True),
33 'journal_id': fields.many2one('account.journal', 'Sales Journal', readonly=True),
34 'jl_id': fields.many2one('account.journal', 'Cash Journals', readonly=True),
35 'user_id': fields.many2one('res.users', 'User', readonly=True),
36 'no_trans': fields.float('Number of Transaction', readonly=True),
37 'amount': fields.float('Amount', readonly=True),
38 'invoice_id': fields.float('Nbr Invoice', readonly=True),
39 'invoice_am': fields.float('Invoice Amount', readonly=True),
40 'product_nb': fields.float('Product Nb.', readonly=True),
41 'disc': fields.float('Disc.', readonly=True),
45 tools.drop_view_if_exists(cr, 'report_transaction_pos')
47 create or replace view report_transaction_pos as (
50 count(absl.id) as no_trans,
51 sum(absl.amount) as amount,
52 sum((100.0-line.discount) * line.price_unit * line.qty / 100.0) as disc,
53 to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')::text as date_create,
54 po.user_id as user_id,
55 po.sale_journal as journal_id,
56 abs.journal_id as jl_id,
57 count(po.invoice_id) as invoice_id,
58 count(p.id) as product_nb
60 account_bank_statement_line as absl,
61 account_bank_statement as abs,
63 pos_order_line as line,
66 absl.pos_statement_id = po.id and
67 line.order_id=po.id and
68 line.product_id=p.id and
69 absl.statement_id=abs.id
72 po.user_id,po.sale_journal, abs.journal_id,
73 to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')::text
76 #to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')
77 #to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')::text as date_create,
78 report_transaction_pos()
80 class report_sales_by_user_pos(osv.osv):
81 _name = "report.sales.by.user.pos"
82 _description = "Sales by user"
85 'date_order': fields.date('Order Date',required=True, select=True),
86 'amount': fields.float('Total', readonly=True, select=True),
87 'qty': fields.float('Quantity', readonly=True, select=True),
88 'user_id': fields.many2one('res.users', 'User', readonly=True, select=True),
92 tools.drop_view_if_exists(cr, 'report_sales_by_user_pos')
94 create or replace view report_sales_by_user_pos as (
97 to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::text as date_order,
98 po.user_id as user_id,
100 sum((pol.price_unit * pol.qty * (1 - (pol.discount) / 100.0))) as amount
102 pos_order as po,pos_order_line as pol,product_product as pp,product_template as pt
104 pt.id=pp.product_tmpl_id and pp.id=pol.product_id and po.id = pol.order_id
106 to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::text,
111 report_sales_by_user_pos()
113 class report_sales_by_user_pos_month(osv.osv):
114 _name = "report.sales.by.user.pos.month"
115 _description = "Sales by user monthly"
118 'date_order': fields.date('Order Date',required=True, select=True),
119 'amount': fields.float('Total', readonly=True, select=True),
120 'qty': fields.float('Quantity', readonly=True, select=True),
121 'user_id': fields.many2one('res.users', 'User', readonly=True, select=True),
125 tools.drop_view_if_exists(cr, 'report_sales_by_user_pos_month')
127 create or replace view report_sales_by_user_pos_month as (
130 to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text as date_order,
131 po.user_id as user_id,
133 sum((pol.price_unit * pol.qty * (1 - (pol.discount) / 100.0))) as amount
135 pos_order as po,pos_order_line as pol,product_product as pp,product_template as pt
137 pt.id=pp.product_tmpl_id and pp.id=pol.product_id and po.id = pol.order_id
139 to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text,
144 report_sales_by_user_pos_month()
146 class report_sales_by_margin_pos(osv.osv):
147 _name = "report.sales.by.margin.pos"
148 _description = "Sales by margin"
151 'product_name':fields.char('Product Name', size=64, readonly=True),
152 'date_order': fields.date('Order Date',required=True, select=True),
153 'user_id': fields.many2one('res.users', 'User', readonly=True, select=True),
154 'qty': fields.float('Qty', readonly=True, select=True),
155 'net_margin_per_qty':fields.float('Net margin per Qty', readonly=True, select=True),
156 'total':fields.float('Margin', readonly=True, select=True),
161 tools.drop_view_if_exists(cr, 'report_sales_by_margin_pos')
163 create or replace view report_sales_by_margin_pos as (
166 po.user_id as user_id,
167 pt.name as product_name,
168 to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::text as date_order,
170 pt.list_price-pt.standard_price as net_margin_per_qty,
171 (pt.list_price-pt.standard_price) *sum(pol.qty) as total
173 product_template as pt,
174 product_product as pp,
175 pos_order_line as pol,
178 pol.product_id = pp.product_tmpl_id and
179 pp.product_tmpl_id = pt.id and
187 to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::text
191 report_sales_by_margin_pos()
193 class report_sales_by_margin_pos_month(osv.osv):
194 _name = "report.sales.by.margin.pos.month"
195 _description = "Sales by margin monthly"
198 'product_name':fields.char('Product Name', size=64, readonly=True),
199 'date_order': fields.date('Order Date',required=True, select=True),
200 'user_id': fields.many2one('res.users', 'User', readonly=True, select=True),
201 'qty': fields.float('Qty', readonly=True, select=True),
202 'net_margin_per_qty':fields.float('Net margin per Qty', readonly=True, select=True),
203 'total':fields.float('Margin', readonly=True, select=True),
207 tools.drop_view_if_exists(cr, 'report_sales_by_margin_pos_month')
209 create or replace view report_sales_by_margin_pos_month as (
212 po.user_id as user_id,
213 pt.name as product_name,
214 to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text as date_order,
216 pt.list_price-pt.standard_price as net_margin_per_qty,
217 (pt.list_price-pt.standard_price) *sum(pol.qty) as total
219 product_template as pt,
220 product_product as pp,
221 pos_order_line as pol,
224 pol.product_id = pp.product_tmpl_id and
225 pp.product_tmpl_id = pt.id and
233 to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text
237 report_sales_by_margin_pos_month()
239 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: