1 # -*- encoding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
8 # This program is free software: you can redistribute it and/or modify
9 # it under the terms of the GNU General Public License as published by
10 # the Free Software Foundation, either version 3 of the License, or
11 # (at your option) any later version.
13 # This program is distributed in the hope that it will be useful,
14 # but WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with this program. If not, see <http://www.gnu.org/licenses/>.
21 ##############################################################################
23 from osv import fields, osv
24 from tools import config
28 ('none', 'Non Member'),
29 ('canceled', 'Canceled Member'),
30 ('old', 'Old Member'),
31 ('waiting', 'Waiting Member'),
32 ('invoiced', 'Invoiced Member'),
33 ('free', 'Free Member'),
34 ('paid', 'Paid Member'),
49 #~ REQUETE = '''SELECT partner, state FROM (
50 #~ SELECT members.partner AS partner,
51 #~ CASE WHEN MAX(members.state) = 0 THEN 'none'
52 #~ ELSE CASE WHEN MAX(members.state) = 1 THEN 'canceled'
53 #~ ELSE CASE WHEN MAX(members.state) = 2 THEN 'old'
54 #~ ELSE CASE WHEN MAX(members.state) = 3 THEN 'waiting'
55 #~ ELSE CASE WHEN MAX(members.state) = 4 THEN 'invoiced'
56 #~ ELSE CASE WHEN MAX(members.state) = 6 THEN 'free'
57 #~ ELSE CASE WHEN MAX(members.state) = 7 THEN 'paid'
58 #~ END END END END END END END END
61 #~ CASE WHEN MAX(inv_digit.state) = 4 THEN 7
62 #~ ELSE CASE WHEN MAX(inv_digit.state) = 3 THEN 4
63 #~ ELSE CASE WHEN MAX(inv_digit.state) = 2 THEN 3
64 #~ ELSE CASE WHEN MAX(inv_digit.state) = 1 THEN 1
68 #~ SELECT p.id as partner,
69 #~ CASE WHEN ai.state = 'paid' THEN 4
70 #~ ELSE CASE WHEN ai.state = 'open' THEN 3
71 #~ ELSE CASE WHEN ai.state = 'proforma' THEN 2
72 #~ ELSE CASE WHEN ai.state = 'draft' THEN 2
73 #~ ELSE CASE WHEN ai.state = 'cancel' THEN 1
74 #~ END END END END END
77 #~ JOIN account_invoice ai ON (
78 #~ p.id = ai.partner_id
80 #~ JOIN account_invoice_line ail ON (
81 #~ ail.invoice_id = ai.id
83 #~ JOIN membership_membership_line ml ON (
84 #~ ml.account_invoice_line = ail.id
86 #~ WHERE ml.date_from <= '%s'
87 #~ AND ml.date_to >= '%s'
95 #~ SELECT p.id AS partner,
96 #~ CASE WHEN p.free_member THEN 6
97 #~ ELSE CASE WHEN p.associate_member IN (
98 #~ SELECT ai.partner_id FROM account_invoice ai JOIN
99 #~ account_invoice_line ail ON (ail.invoice_id = ai.id AND ai.state = 'paid')
100 #~ JOIN membership_membership_line ml ON (ml.account_invoice_line = ail.id)
101 #~ WHERE ml.date_from <= '%s'
102 #~ AND ml.date_to >= '%s'
107 #~ FROM res_partner p
108 #~ WHERE p.free_member
109 #~ OR p.associate_member > 0
111 #~ SELECT p.id as partner,
112 #~ MAX(CASE WHEN ai.state = 'paid' THEN 2
116 #~ FROM res_partner p
117 #~ JOIN account_invoice ai ON (
118 #~ p.id = ai.partner_id
120 #~ JOIN account_invoice_line ail ON (
121 #~ ail.invoice_id = ai.id
123 #~ JOIN membership_membership_line ml ON (
124 #~ ml.account_invoice_line = ail.id
126 #~ WHERE ml.date_from < '%s'
127 #~ AND ml.date_to < '%s'
128 #~ AND ml.date_from <= ml.date_to
133 #~ GROUP BY members.partner
140 class membership_line(osv.osv):
143 def _check_membership_date(self, cr, uid, ids, context=None):
144 '''Check if membership product is not in the past'''
147 SELECT MIN(ml.date_to - ai.date_invoice)
148 FROM membership_membership_line ml
149 JOIN account_invoice_line ail ON (
150 ml.account_invoice_line = ail.id
152 JOIN account_invoice ai ON (
153 ai.id = ail.invoice_id)
155 ''' % ','.join([str(id) for id in ids]))
159 if r[0] and r[0] < 0:
163 def _state(self, cr, uid, ids, name, args, context=None):
164 '''Compute the state lines'''
166 for line in self.browse(cr, uid, ids):
169 account_invoice i WHERE
171 SELECT l.invoice_id FROM
172 account_invoice_line l WHERE
174 SELECT ml.account_invoice_line FROM
175 membership_membership_line ml WHERE
180 fetched = cr.fetchone()
182 res[line.id] = 'canceled'
186 if (istate == 'draft') | (istate == 'proforma'):
188 elif istate == 'open':
190 elif istate == 'paid':
192 elif istate == 'cancel':
198 _description = __doc__
199 _name = 'membership.membership_line'
201 'partner': fields.many2one('res.partner', 'Partner', ondelete='cascade', select=1),
202 'date_from': fields.date('From'),
203 'date_to': fields.date('To'),
204 'date_cancel' : fields.date('Cancel date'),
205 'account_invoice_line': fields.many2one('account.invoice.line', 'Account Invoice line'),
206 'state': fields.function(_state, method=True, string='State', type='selection', selection=STATE),
208 _rec_name = 'partner'
211 (_check_membership_date, 'Error, this membership product is out of date', [])
217 class Partner(osv.osv):
219 _inherit = 'res.partner'
221 def _get_partner_id(self, cr, uid, ids, context=None):
222 data_inv = self.pool.get('membership.membership_line').browse(cr, uid, ids, context)
224 for data in data_inv:
225 list_partner.append(data.partner.id)
228 ids2 = self.pool.get('res.partner').search(cr, uid, [('associate_member','in',ids2)], context=context)
232 def _get_invoice_partner(self, cr, uid, ids, context=None):
233 data_inv = self.pool.get('account.invoice').browse(cr, uid, ids, context)
235 for data in data_inv:
236 list_partner.append(data.partner_id.id)
239 ids2 = self.pool.get('res.partner').search(cr, uid, [('associate_member','in',ids2)], context=context)
243 def _membership_state(self, cr, uid, ids, name, args, context=None):
247 today = time.strftime('%Y-%m-%d')
249 partner_data = self.browse(cr,uid,id)
250 if partner_data.membership_cancel and today > partner_data.membership_cancel:
253 if partner_data.membership_stop and today > partner_data.membership_stop:
257 if partner_data.member_lines:
258 for mline in partner_data.member_lines:
259 if mline.date_from <= today and mline.date_to >= today:
260 if mline.account_invoice_line and mline.account_invoice_line.invoice_id:
261 mstate = mline.account_invoice_line.invoice_id.state
265 elif mstate == 'open' and s!=0:
267 elif mstate == 'cancel' and s!=0 and s!=1:
269 elif (mstate == 'draft' or mstate == 'proforma') and s!=0 and s!=1:
272 for mline in partner_data.member_lines:
273 if mline.date_from < today and mline.date_to < today and mline.date_from<=mline.date_to and mline.account_invoice_line.invoice_id.state == 'paid':
289 if partner_data.free_member and s!=0:
291 if partner_data.associate_member:
292 associate_partners_list = []
293 query="SELECT DISTINCT associate_member FROM res_partner"
295 for p in cr.fetchall():
296 if p != partner_data.id:
297 associate_partners_list.append(p)
298 if associate_partners_list != []:
299 self._membership_state(cr, uid, associate_partners_list, name, args, context)
300 res[id] = partner_data.associate_member.membership_state
303 def _membership_start(self, cr, uid, ids, name, args, context=None):
304 '''Return the start date of membership'''
306 member_line_obj = self.pool.get('membership.membership_line')
307 for partner in self.browse(cr, uid, ids):
308 if partner.associate_member:
309 partner_id = partner.associate_member.id
311 partner_id = partner.id
312 line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
313 limit=1, order='date_from')
315 res[partner.id] = member_line_obj.read(cr, uid, line_id[0],
316 ['date_from'])['date_from']
318 res[partner.id] = False
321 def _membership_stop(self, cr, uid, ids, name, args, context=None):
322 '''Return the stop date of membership'''
324 member_line_obj = self.pool.get('membership.membership_line')
325 for partner in self.browse(cr, uid, ids):
326 cr.execute('select membership_state from res_partner where id=%s', (partner.id,))
327 data_state = cr.fetchall()
328 if partner.associate_member:
329 partner_id = partner.associate_member.id
331 partner_id = partner.id
332 line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
333 limit=1, order='date_to desc')
335 res[partner.id] = member_line_obj.read(cr, uid, line_id[0],
336 ['date_to'])['date_to']
338 res[partner.id] = False
341 def _membership_cancel(self, cr, uid, ids, name, args, context=None):
342 '''Return the cancel date of membership'''
344 member_line_obj = self.pool.get('membership.membership_line')
345 for partner_id in ids:
346 line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
347 limit=1, order='date_cancel')
349 res[partner_id] = member_line_obj.read(cr, uid, line_id[0],
350 ['date_cancel'])['date_cancel']
352 res[partner_id] = False
355 def _get_partners(self, cr, uid, ids, context={}):
358 ids2 = self.search(cr, uid, [('associate_member','in',ids2)], context=context)
363 'associate_member': fields.many2one('res.partner', 'Associate member'),
364 'member_lines': fields.one2many('membership.membership_line', 'partner', 'Membership'),
365 'free_member': fields.boolean('Free member'),
366 'membership_amount': fields.float(
367 'Membership amount', digites=(16, 2),
368 help='The price negociated by the partner'),
369 'membership_state': fields.function(
370 _membership_state, method = True,
371 string = 'Current membership state', type = 'selection',
372 selection = STATE ,store = {
373 'account.invoice':(_get_invoice_partner,['state'], 10),
374 'membership.membership_line':(_get_partner_id,['state'], 10),
375 'res.partner':(_get_partners, ['free_member'], 10)
378 'membership_start': fields.function(
379 _membership_start, method=True,
380 string = 'Start membership date', type = 'date',
382 'account.invoice':(_get_invoice_partner,['state'], 10),
383 'membership.membership_line':(_get_partner_id,['state'], 10),
384 'res.partner':(lambda self,cr,uid,ids,c={}:ids, ['free_member'], 10)
387 'membership_stop': fields.function(
388 _membership_stop, method = True,
389 string = 'Stop membership date', type = 'date',
391 'account.invoice':(_get_invoice_partner,['state'], 10),
392 'membership.membership_line':(_get_partner_id,['state'], 10),
393 'res.partner':(lambda self,cr,uid,ids,c={}:ids, ['free_member'], 10)
397 'membership_cancel': fields.function(
398 _membership_cancel, method = True,
399 string = 'Cancel membership date', type='date',
401 'account.invoice':(_get_invoice_partner,['state'], 10),
402 'membership.membership_line':(_get_partner_id,['state'], 10),
403 'res.partner':(lambda self,cr,uid,ids,c={}:ids, ['free_member'], 10)
408 'free_member': lambda *a: False,
409 'membership_cancel' : lambda *d : False,
412 def _check_recursion(self, cr, uid, ids):
415 cr.execute('select distinct associate_member from res_partner where id in ('+','.join(map(str,ids))+')')
416 ids = filter(None, map(lambda x:x[0], cr.fetchall()))
423 (_check_recursion, 'Error ! You can not create recursive associated members.', ['associate_member'])
428 class product_template(osv.osv):
429 _inherit = 'product.template'
431 'member_price':fields.float('Member Price', digits=(16, int(config['price_accuracy']))),
435 class Product(osv.osv):
437 def fields_view_get(self, cr, user, view_id=None, view_type='form', context=None, toolbar=False):
438 if ('product' in context) and (context['product']=='membership_product'):
439 model_data_ids_form = self.pool.get('ir.model.data').search(cr,user,[('model','=','ir.ui.view'),('name','in',['membership_products_form','membership_products_tree'])])
440 resource_id_form = self.pool.get('ir.model.data').read(cr,user,model_data_ids_form,fields=['res_id','name'])
442 for i in resource_id_form:
443 dict_model[i['name']]=i['res_id']
444 if view_type=='form':
445 view_id = dict_model['membership_products_form']
447 view_id = dict_model['membership_products_tree']
448 return super(Product,self).fields_view_get(cr, user, view_id, view_type, context, toolbar)
451 _inherit = 'product.product'
452 _description = 'product.product'
455 'membership': fields.boolean('Membership', help='Specify if this product is a membership product'),
456 'membership_date_from': fields.date('Date from'),
457 'membership_date_to': fields.date('Date to'),
458 # 'member_price':fields.float('Member Price'),
462 'membership': lambda *args: False
467 class Invoice(osv.osv):
470 _inherit = 'account.invoice'
472 def create(self, cr, uid, vals, context={}):
473 result = super(Invoice, self).create(cr, uid, vals, context)
474 invoice = self.browse(cr, uid, result)
475 member_line_obj = self.pool.get('membership.membership_line')
476 for line in invoice.invoice_line:
477 if line.product_id and line.product_id.membership:
478 date_from = line.product_id.membership_date_from
479 date_to = line.product_id.membership_date_to
480 if invoice.date_invoice > date_from and invoice.date_invoice < date_to:
481 date_from = invoice.date_invoice
482 line_id = member_line_obj.create(cr, uid, {
483 'partner': invoice.partner_id.id,
484 'date_from': date_from,
486 'account_invoice_line': line.id,
490 def action_cancel(self, cr, uid, ids, context=None):
491 '''Create a 'date_cancel' on the membership_line object'''
494 member_line_obj = self.pool.get('membership.membership_line')
495 today = time.strftime('%Y-%m-%d')
496 for invoice in self.browse(cr, uid, ids):
497 mlines = member_line_obj.search(cr,uid,
498 [('account_invoice_line','in',
499 [ l.id for l in invoice.invoice_line])], context)
500 member_line_obj.write(cr,uid,mlines, {'date_cancel':today}, context)
501 return super(Invoice, self).action_cancel(cr, uid, ids, context)
505 class ReportPartnerMemberYear(osv.osv):
506 '''Membership by Years'''
508 _name = 'report.partner_member.year'
509 _description = __doc__
513 'year': fields.char('Year', size='4', readonly=True, select=1),
514 'canceled_number': fields.integer('Canceled', readonly=True),
515 'waiting_number': fields.integer('Waiting', readonly=True),
516 'invoiced_number': fields.integer('Invoiced', readonly=True),
517 'paid_number': fields.integer('Paid', readonly=True),
518 'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
519 'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
520 'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
521 'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
522 'currency': fields.many2one('res.currency', 'Currency', readonly=True,
527 '''Create the view'''
529 CREATE OR REPLACE VIEW report_partner_member_year AS (
532 COUNT(ncanceled) as canceled_number,
533 COUNT(npaid) as paid_number,
534 COUNT(ninvoiced) as invoiced_number,
535 COUNT(nwaiting) as waiting_number,
536 SUM(acanceled) as canceled_amount,
537 SUM(apaid) as paid_amount,
538 SUM(ainvoiced) as invoiced_amount,
539 SUM(awaiting) as waiting_amount,
543 CASE WHEN ai.state = 'cancel' THEN ml.id END AS ncanceled,
544 CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
545 CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
546 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
547 THEN ml.id END AS nwaiting,
548 CASE WHEN ai.state = 'cancel'
549 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
550 ELSE 0 END AS acanceled,
551 CASE WHEN ai.state = 'paid'
552 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
554 CASE WHEN ai.state = 'open'
555 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
556 ELSE 0 END AS ainvoiced,
557 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
558 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
559 ELSE 0 END AS awaiting,
560 TO_CHAR(ml.date_from, 'YYYY') AS year,
561 ai.currency_id AS currency,
563 FROM membership_membership_line ml
564 JOIN (account_invoice_line ail
565 LEFT JOIN account_invoice ai
566 ON (ail.invoice_id = ai.id))
567 ON (ml.account_invoice_line = ail.id)
569 ON (ml.partner = p.id)
570 GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
571 ai.currency_id, ml.id) AS foo
572 GROUP BY year, currency)
575 ReportPartnerMemberYear()
578 class ReportPartnerMemberYearNew(osv.osv):
579 '''New Membership by Years'''
581 _name = 'report.partner_member.year_new'
582 _description = __doc__
587 'year': fields.char('Year', size='4', readonly=True, select=1),
588 'canceled_number': fields.integer('Canceled', readonly=True),
589 'waiting_number': fields.integer('Waiting', readonly=True),
590 'invoiced_number': fields.integer('Invoiced', readonly=True),
591 'paid_number': fields.integer('Paid', readonly=True),
592 'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
593 'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
594 'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
595 'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
596 'currency': fields.many2one('res.currency', 'Currency', readonly=True,
601 '''Create the view'''
603 CREATE OR REPLACE VIEW report_partner_member_year AS (
606 COUNT(ncanceled) as canceled_number,
607 COUNT(npaid) as paid_number,
608 COUNT(ninvoiced) as invoiced_number,
609 COUNT(nwaiting) as waiting_number,
610 SUM(acanceled) as canceled_amount,
611 SUM(apaid) as paid_amount,
612 SUM(ainvoiced) as invoiced_amount,
613 SUM(awaiting) as waiting_amount,
617 CASE WHEN ai.state = 'cancel' THEN ml.id END AS ncanceled,
618 CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
619 CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
620 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
621 THEN ml.id END AS nwaiting,
622 CASE WHEN ai.state = 'cancel'
623 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
624 ELSE 0 END AS acanceled,
625 CASE WHEN ai.state = 'paid'
626 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
628 CASE WHEN ai.state = 'open'
629 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
630 ELSE 0 END AS ainvoiced,
631 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
632 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
633 ELSE 0 END AS awaiting,
634 TO_CHAR(ml.date_from, 'YYYY') AS year,
635 ai.currency_id AS currency,
637 FROM membership_membership_line ml
638 JOIN (account_invoice_line ail
639 LEFT JOIN account_invoice ai
640 ON (ail.invoice_id = ai.id))
641 ON (ml.account_invoice_line = ail.id)
643 ON (ml.partner = p.id)
644 GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
645 ai.currency_id, ml.id) AS foo
646 GROUP BY year, currency)
649 ReportPartnerMemberYear()
652 class ReportPartnerMemberYearNew(osv.osv):
653 '''New Membership by Years'''
655 _name = 'report.partner_member.year_new'
656 _description = __doc__
660 'year': fields.char('Year', size='4', readonly=True, select=1),
661 'canceled_number': fields.integer('Canceled', readonly=True),
662 'waiting_number': fields.integer('Waiting', readonly=True),
663 'invoiced_number': fields.integer('Invoiced', readonly=True),
664 'paid_number': fields.integer('Paid', readonly=True),
665 'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
666 'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
667 'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
668 'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
669 'currency': fields.many2one('res.currency', 'Currency', readonly=True,
673 def init(self, cursor):
674 '''Create the view'''
676 CREATE OR REPLACE VIEW report_partner_member_year_new AS (
679 COUNT(ncanceled) AS canceled_number,
680 COUNT(npaid) AS paid_number,
681 COUNT(ninvoiced) AS invoiced_number,
682 COUNT(nwaiting) AS waiting_number,
683 SUM(acanceled) AS canceled_amount,
684 SUM(apaid) AS paid_amount,
685 SUM(ainvoiced) AS invoiced_amount,
686 SUM(awaiting) AS waiting_amount,
690 CASE WHEN ai.state = 'cancel' THEN ml2.id END AS ncanceled,
691 CASE WHEN ai.state = 'paid' THEN ml2.id END AS npaid,
692 CASE WHEN ai.state = 'open' THEN ml2.id END AS ninvoiced,
693 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
694 THEN ml2.id END AS nwaiting,
695 CASE WHEN ai.state = 'cancel'
696 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
697 ELSE 0 END AS acanceled,
698 CASE WHEN ai.state = 'paid'
699 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
701 CASE WHEN ai.state = 'open'
702 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
703 ELSE 0 END AS ainvoiced,
704 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
705 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
706 ELSE 0 END AS awaiting,
707 TO_CHAR(ml2.date_from, 'YYYY') AS year,
708 ai.currency_id AS currency,
712 MIN(date_from) AS date_from
713 FROM membership_membership_line
716 JOIN membership_membership_line ml2
717 JOIN (account_invoice_line ail
718 LEFT JOIN account_invoice ai
719 ON (ail.invoice_id = ai.id))
720 ON (ml2.account_invoice_line = ail.id)
721 ON (ml1.id = ml2.partner AND ml1.date_from = ml2.date_from)
723 ON (ml2.partner = p.id)
724 GROUP BY TO_CHAR(ml2.date_from, 'YYYY'), ai.state,
725 ai.currency_id, ml2.id) AS foo
726 GROUP BY year, currency
730 ReportPartnerMemberYearNew()
731 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: