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
23 from openerp import tools
24 import openerp.addons.decimal_precision as dp
27 ('none', 'Non Member'),
28 ('canceled', 'Cancelled Member'),
29 ('old', 'Old Member'),
30 ('waiting', 'Waiting Member'),
31 ('invoiced', 'Invoiced Member'),
32 ('free', 'Free Member'),
33 ('paid', 'Paid Member'),
36 class report_membership(osv.osv):
37 '''Membership Analysis'''
39 _name = 'report.membership'
40 _description = __doc__
44 'year': fields.char('Year', size=4, readonly=True, select=1),
45 'month': fields.selection([('01', 'January'), ('02', 'February'), \
46 ('03', 'March'), ('04', 'April'),\
47 ('05', 'May'), ('06', 'June'), \
48 ('07', 'July'), ('08', 'August'),\
49 ('09', 'September'), ('10', 'October'),\
50 ('11', 'November'), ('12', 'December')], 'Month', readonly=True),
51 'date_from': fields.datetime('Start Date', readonly=True, help="Start membership date"),
52 'date_to': fields.datetime('End Date', readonly=True, help="End membership date"),
53 'num_waiting': fields.integer('# Waiting', readonly=True),
54 'num_invoiced': fields.integer('# Invoiced', readonly=True),
55 'num_paid': fields.integer('# Paid', readonly=True),
56 'tot_pending': fields.float('Pending Amount', digits_compute= dp.get_precision('Account'), readonly=True),
57 'tot_earned': fields.float('Earned Amount', digits_compute= dp.get_precision('Account'), readonly=True),
58 'partner_id': fields.many2one('res.partner', 'Member', readonly=True),
59 'associate_member_id': fields.many2one('res.partner', 'Associate Member', readonly=True),
60 'membership_id': fields.many2one('product.product', 'Membership Product', readonly=True),
61 'membership_state': fields.selection(STATE, 'Current Membership State', readonly=True),
62 'user_id': fields.many2one('res.users', 'Salesperson', readonly=True),
63 'company_id': fields.many2one('res.company', 'Company', readonly=True)
68 tools.drop_view_if_exists(cr, 'report_membership')
70 CREATE OR REPLACE VIEW report_membership AS (
82 COUNT(num_waiting) AS num_waiting,
83 COUNT(num_invoiced) AS num_invoiced,
84 COUNT(num_paid) AS num_paid,
85 SUM(tot_pending) AS tot_pending,
86 SUM(tot_earned) AS tot_earned,
94 p.membership_state AS membership_state,
95 p.associate_member AS associate_member_id,
96 p.membership_amount AS membership_amount,
97 TO_CHAR(p.membership_start, 'YYYY-MM-DD') AS date_from,
98 TO_CHAR(p.membership_stop, 'YYYY-MM-DD') AS date_to,
99 TO_CHAR(p.membership_start, 'YYYY') AS year,
100 TO_CHAR(p.membership_start,'MM') AS month,
101 CASE WHEN ml.state = 'waiting' THEN ml.id END AS num_waiting,
102 CASE WHEN ml.state = 'invoiced' THEN ml.id END AS num_invoiced,
103 CASE WHEN ml.state = 'paid' THEN ml.id END AS num_paid,
104 CASE WHEN ml.state IN ('waiting', 'invoiced') THEN SUM(il.price_subtotal) ELSE 0 END AS tot_pending,
105 CASE WHEN ml.state = 'paid' OR p.membership_state = 'old' THEN SUM(il.price_subtotal) ELSE 0 END AS tot_earned,
106 ml.membership_id AS membership_id,
107 p.company_id AS company_id
109 LEFT JOIN membership_membership_line ml ON (ml.partner = p.id)
110 LEFT JOIN account_invoice_line il ON (ml.account_invoice_line = il.id)
111 LEFT JOIN account_invoice ai ON (il.invoice_id = ai.id)
112 WHERE p.membership_state != 'none' and p.active = 'true'
119 TO_CHAR(p.membership_start, 'YYYY-MM-DD'),
120 TO_CHAR(p.membership_stop, 'YYYY-MM-DD'),
121 TO_CHAR(p.membership_start, 'YYYY'),
122 TO_CHAR(p.membership_start,'MM'),
144 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: