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 def _get_invoice_partner(self, cr, uid, ids, context=None):
229 data_inv = self.pool.get('account.invoice').browse(cr, uid, ids, context)
231 for data in data_inv:
232 list_partner.append(data.partner_id.id)
235 def _membership_state(self, cr, uid, ids, name, args, context=None):
239 today = time.strftime('%Y-%m-%d')
241 partner_data = self.browse(cr,uid,id)
242 if partner_data.membership_cancel and today > partner_data.membership_cancel:
245 if partner_data.membership_stop and today > partner_data.membership_stop:
249 if partner_data.member_lines:
250 for mline in partner_data.member_lines:
251 if mline.date_from <= today and mline.date_to >= today:
252 if mline.account_invoice_line and mline.account_invoice_line.invoice_id:
253 mstate = mline.account_invoice_line.invoice_id.state
257 elif mstate == 'open' and s!=0:
259 elif mstate == 'cancel' and s!=0 and s!=1:
261 elif (mstate == 'draft' or mstate == 'proforma') and s!=0 and s!=1:
264 for mline in partner_data.member_lines:
265 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':
281 if partner_data.free_member and s!=0:
283 if partner_data.associate_member:
284 associate_partners_list = []
285 query="SELECT DISTINCT associate_member FROM res_partner"
287 for p in cr.fetchall():
288 if p != partner_data.id:
289 associate_partners_list.append(p)
290 if associate_partners_list != []:
291 self._membership_state(cr, uid, associate_partners_list, name, args, context)
292 res[id] = partner_data.associate_member.membership_state
295 def _membership_start(self, cr, uid, ids, name, args, context=None):
296 '''Return the start date of membership'''
298 member_line_obj = self.pool.get('membership.membership_line')
299 for partner in self.browse(cr, uid, ids):
300 if partner.associate_member:
301 partner_id = partner.associate_member.id
303 partner_id = partner.id
304 line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
305 limit=1, order='date_from')
307 res[partner.id] = member_line_obj.read(cr, uid, line_id[0],
308 ['date_from'])['date_from']
310 res[partner.id] = False
313 def _membership_stop(self, cr, uid, ids, name, args, context=None):
314 '''Return the stop date of membership'''
316 member_line_obj = self.pool.get('membership.membership_line')
317 for partner in self.browse(cr, uid, ids):
318 cr.execute('select membership_state from res_partner where id=%s', (partner.id,))
319 data_state = cr.fetchall()
320 if partner.associate_member:
321 partner_id = partner.associate_member.id
323 partner_id = partner.id
324 line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
325 limit=1, order='date_to desc')
327 res[partner.id] = member_line_obj.read(cr, uid, line_id[0],
328 ['date_to'])['date_to']
330 res[partner.id] = False
333 def _membership_cancel(self, cr, uid, ids, name, args, context=None):
334 '''Return the cancel date of membership'''
336 member_line_obj = self.pool.get('membership.membership_line')
337 for partner_id in ids:
338 line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
339 limit=1, order='date_cancel')
341 res[partner_id] = member_line_obj.read(cr, uid, line_id[0],
342 ['date_cancel'])['date_cancel']
344 res[partner_id] = False
347 def _get_partners(self, cr, uid, ids, context={}):
350 ids2 = self.search(cr, uid, [('associate_member','in',ids2)], context=context)
355 'associate_member': fields.many2one('res.partner', 'Associate member'),
356 'member_lines': fields.one2many('membership.membership_line', 'partner', 'Membership'),
357 'free_member': fields.boolean('Free member'),
358 'membership_amount': fields.float(
359 'Membership amount', digites=(16, 2),
360 help='The price negociated by the partner'),
361 'membership_state': fields.function(
362 _membership_state, method = True,
363 string = 'Current membership state', type = 'selection',
364 selection = STATE ,store = {
365 'account.invoice':(_get_invoice_partner,['state'], 10),
366 'membership.membership_line':(_get_partner_id,['state'], 10),
367 'res.partner':(_get_partners, ['free_member'], 10)
370 'membership_start': fields.function(
371 _membership_start, method=True,
372 string = 'Start membership date', type = 'date',
374 'account.invoice':(_get_invoice_partner,['state'], 10),
375 'membership.membership_line':(_get_partner_id,['state'], 10),
376 'res.partner':(lambda self,cr,uid,ids,c={}:ids, ['free_member'], 10)
379 'membership_stop': fields.function(
380 _membership_stop, method = True,
381 string = 'Stop membership date', type = 'date',
383 'account.invoice':(_get_invoice_partner,['state'], 10),
384 'membership.membership_line':(_get_partner_id,['state'], 10),
385 'res.partner':(lambda self,cr,uid,ids,c={}:ids, ['free_member'], 10)
389 'membership_cancel': fields.function(
390 _membership_cancel, method = True,
391 string = 'Cancel membership date', type='date',
393 'account.invoice':(_get_invoice_partner,['state'], 10),
394 'membership.membership_line':(_get_partner_id,['state'], 10),
395 'res.partner':(lambda self,cr,uid,ids,c={}:ids, ['free_member'], 10)
400 'free_member': lambda *a: False,
401 'membership_cancel' : lambda *d : False,
404 def _check_recursion(self, cr, uid, ids):
407 cr.execute('select distinct associate_member from res_partner where id in ('+','.join(map(str,ids))+')')
408 ids = filter(None, map(lambda x:x[0], cr.fetchall()))
415 (_check_recursion, 'Error ! You can not create recursive associated members.', ['associate_member'])
420 class product_template(osv.osv):
421 _inherit = 'product.template'
423 'member_price':fields.float('Member Price', digits=(16, int(config['price_accuracy']))),
427 class Product(osv.osv):
429 def fields_view_get(self, cr, user, view_id=None, view_type='form', context=None, toolbar=False):
430 if ('product' in context) and (context['product']=='membership_product'):
431 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'])])
432 resource_id_form = self.pool.get('ir.model.data').read(cr,user,model_data_ids_form,fields=['res_id','name'])
434 for i in resource_id_form:
435 dict_model[i['name']]=i['res_id']
436 if view_type=='form':
437 view_id = dict_model['membership_products_form']
439 view_id = dict_model['membership_products_tree']
440 return super(Product,self).fields_view_get(cr, user, view_id, view_type, context, toolbar)
443 _inherit = 'product.product'
444 _description = 'product.product'
447 'membership': fields.boolean('Membership', help='Specify if this product is a membership product'),
448 'membership_date_from': fields.date('Date from'),
449 'membership_date_to': fields.date('Date to'),
450 # 'member_price':fields.float('Member Price'),
454 'membership': lambda *args: False
459 class Invoice(osv.osv):
462 _inherit = 'account.invoice'
464 def create(self, cr, uid, vals, context={}):
465 result = super(Invoice, self).create(cr, uid, vals, context)
466 invoice = self.browse(cr, uid, result)
467 member_line_obj = self.pool.get('membership.membership_line')
468 for line in invoice.invoice_line:
469 if line.product_id and line.product_id.membership:
470 date_from = line.product_id.membership_date_from
471 date_to = line.product_id.membership_date_to
472 if invoice.date_invoice > date_from and invoice.date_invoice < date_to:
473 date_from = invoice.date_invoice
474 line_id = member_line_obj.create(cr, uid, {
475 'partner': invoice.partner_id.id,
476 'date_from': date_from,
478 'account_invoice_line': line.id,
482 def action_cancel(self, cr, uid, ids, context=None):
483 '''Create a 'date_cancel' on the membership_line object'''
486 member_line_obj = self.pool.get('membership.membership_line')
487 today = time.strftime('%Y-%m-%d')
488 for invoice in self.browse(cr, uid, ids):
489 mlines = member_line_obj.search(cr,uid,
490 [('account_invoice_line','in',
491 [ l.id for l in invoice.invoice_line])], context)
492 member_line_obj.write(cr,uid,mlines, {'date_cancel':today}, context)
493 return super(Invoice, self).action_cancel(cr, uid, ids, context)
497 class ReportPartnerMemberYear(osv.osv):
498 '''Membership by Years'''
500 _name = 'report.partner_member.year'
501 _description = __doc__
505 'year': fields.char('Year', size='4', readonly=True, select=1),
506 'canceled_number': fields.integer('Canceled', readonly=True),
507 'waiting_number': fields.integer('Waiting', readonly=True),
508 'invoiced_number': fields.integer('Invoiced', readonly=True),
509 'paid_number': fields.integer('Paid', readonly=True),
510 'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
511 'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
512 'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
513 'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
514 'currency': fields.many2one('res.currency', 'Currency', readonly=True,
519 '''Create the view'''
521 CREATE OR REPLACE VIEW report_partner_member_year AS (
524 COUNT(ncanceled) as canceled_number,
525 COUNT(npaid) as paid_number,
526 COUNT(ninvoiced) as invoiced_number,
527 COUNT(nwaiting) as waiting_number,
528 SUM(acanceled) as canceled_amount,
529 SUM(apaid) as paid_amount,
530 SUM(ainvoiced) as invoiced_amount,
531 SUM(awaiting) as waiting_amount,
535 CASE WHEN ai.state = 'cancel' THEN ml.id END AS ncanceled,
536 CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
537 CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
538 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
539 THEN ml.id END AS nwaiting,
540 CASE WHEN ai.state = 'cancel'
541 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
542 ELSE 0 END AS acanceled,
543 CASE WHEN ai.state = 'paid'
544 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
546 CASE WHEN ai.state = 'open'
547 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
548 ELSE 0 END AS ainvoiced,
549 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
550 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
551 ELSE 0 END AS awaiting,
552 TO_CHAR(ml.date_from, 'YYYY') AS year,
553 ai.currency_id AS currency,
555 FROM membership_membership_line ml
556 JOIN (account_invoice_line ail
557 LEFT JOIN account_invoice ai
558 ON (ail.invoice_id = ai.id))
559 ON (ml.account_invoice_line = ail.id)
561 ON (ml.partner = p.id)
562 GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
563 ai.currency_id, ml.id) AS foo
564 GROUP BY year, currency)
567 ReportPartnerMemberYear()
570 class ReportPartnerMemberYearNew(osv.osv):
571 '''New Membership by Years'''
573 _name = 'report.partner_member.year_new'
574 _description = __doc__
579 'year': fields.char('Year', size='4', readonly=True, select=1),
580 'canceled_number': fields.integer('Canceled', readonly=True),
581 'waiting_number': fields.integer('Waiting', readonly=True),
582 'invoiced_number': fields.integer('Invoiced', readonly=True),
583 'paid_number': fields.integer('Paid', readonly=True),
584 'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
585 'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
586 'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
587 'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
588 'currency': fields.many2one('res.currency', 'Currency', readonly=True,
593 '''Create the view'''
595 CREATE OR REPLACE VIEW report_partner_member_year AS (
598 COUNT(ncanceled) as canceled_number,
599 COUNT(npaid) as paid_number,
600 COUNT(ninvoiced) as invoiced_number,
601 COUNT(nwaiting) as waiting_number,
602 SUM(acanceled) as canceled_amount,
603 SUM(apaid) as paid_amount,
604 SUM(ainvoiced) as invoiced_amount,
605 SUM(awaiting) as waiting_amount,
609 CASE WHEN ai.state = 'cancel' THEN ml.id END AS ncanceled,
610 CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
611 CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
612 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
613 THEN ml.id END AS nwaiting,
614 CASE WHEN ai.state = 'cancel'
615 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
616 ELSE 0 END AS acanceled,
617 CASE WHEN ai.state = 'paid'
618 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
620 CASE WHEN ai.state = 'open'
621 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
622 ELSE 0 END AS ainvoiced,
623 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
624 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
625 ELSE 0 END AS awaiting,
626 TO_CHAR(ml.date_from, 'YYYY') AS year,
627 ai.currency_id AS currency,
629 FROM membership_membership_line ml
630 JOIN (account_invoice_line ail
631 LEFT JOIN account_invoice ai
632 ON (ail.invoice_id = ai.id))
633 ON (ml.account_invoice_line = ail.id)
635 ON (ml.partner = p.id)
636 GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
637 ai.currency_id, ml.id) AS foo
638 GROUP BY year, currency)
641 ReportPartnerMemberYear()
644 class ReportPartnerMemberYearNew(osv.osv):
645 '''New Membership by Years'''
647 _name = 'report.partner_member.year_new'
648 _description = __doc__
652 'year': fields.char('Year', size='4', readonly=True, select=1),
653 'canceled_number': fields.integer('Canceled', readonly=True),
654 'waiting_number': fields.integer('Waiting', readonly=True),
655 'invoiced_number': fields.integer('Invoiced', readonly=True),
656 'paid_number': fields.integer('Paid', readonly=True),
657 'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
658 'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
659 'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
660 'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
661 'currency': fields.many2one('res.currency', 'Currency', readonly=True,
665 def init(self, cursor):
666 '''Create the view'''
668 CREATE OR REPLACE VIEW report_partner_member_year_new AS (
671 COUNT(ncanceled) AS canceled_number,
672 COUNT(npaid) AS paid_number,
673 COUNT(ninvoiced) AS invoiced_number,
674 COUNT(nwaiting) AS waiting_number,
675 SUM(acanceled) AS canceled_amount,
676 SUM(apaid) AS paid_amount,
677 SUM(ainvoiced) AS invoiced_amount,
678 SUM(awaiting) AS waiting_amount,
682 CASE WHEN ai.state = 'cancel' THEN ml2.id END AS ncanceled,
683 CASE WHEN ai.state = 'paid' THEN ml2.id END AS npaid,
684 CASE WHEN ai.state = 'open' THEN ml2.id END AS ninvoiced,
685 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
686 THEN ml2.id END AS nwaiting,
687 CASE WHEN ai.state = 'cancel'
688 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
689 ELSE 0 END AS acanceled,
690 CASE WHEN ai.state = 'paid'
691 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
693 CASE WHEN ai.state = 'open'
694 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
695 ELSE 0 END AS ainvoiced,
696 CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
697 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
698 ELSE 0 END AS awaiting,
699 TO_CHAR(ml2.date_from, 'YYYY') AS year,
700 ai.currency_id AS currency,
704 MIN(date_from) AS date_from
705 FROM membership_membership_line
708 JOIN membership_membership_line ml2
709 JOIN (account_invoice_line ail
710 LEFT JOIN account_invoice ai
711 ON (ail.invoice_id = ai.id))
712 ON (ml2.account_invoice_line = ail.id)
713 ON (ml1.id = ml2.partner AND ml1.date_from = ml2.date_from)
715 ON (ml2.partner = p.id)
716 GROUP BY TO_CHAR(ml2.date_from, 'YYYY'), ai.state,
717 ai.currency_id, ml2.id) AS foo
718 GROUP BY year, currency
722 ReportPartnerMemberYearNew()
723 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: