addons/membership/membership.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
   6 #    $Id$
   7 #
   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.
  12 #
  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.
  17 #
  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/>.
  20 #
  21 ##############################################################################
  22
  23 from osv import fields, osv
  24 from tools import config
  25 import time
  26
  27 STATE = [
  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'),
  35 ]
  36
  37 STATE_PRIOR = {
  38         'none' : 0,
  39         'canceled' : 1,
  40         'old' : 2,
  41         'waiting' : 3,
  42         'invoiced' : 4,
  43         'free' : 6,
  44         'paid' : 7
  45         }
  46
  47
  48
  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
  59     #~ AS state FROM (
  60 #~ SELECT partner,
  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
  65 #~ END END END END
  66 #~ AS state
  67 #~ FROM (
  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
  75 #~ AS state
  76 #~ FROM res_partner p
  77 #~ JOIN account_invoice ai ON (
  78     #~ p.id = ai.partner_id
  79 #~ )
  80 #~ JOIN account_invoice_line ail ON (
  81     #~ ail.invoice_id = ai.id
  82 #~ )
  83 #~ JOIN membership_membership_line ml ON (
  84     #~ ml.account_invoice_line  = ail.id
  85 #~ )
  86 #~ WHERE ml.date_from <= '%s'
  87 #~ AND ml.date_to >= '%s'
  88 #~ GROUP BY
  89 #~ p.id,
  90 #~ ai.state
  91     #~ )
  92     #~ AS inv_digit
  93     #~ GROUP by partner
  94 #~ UNION
  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'
 103     #~ )
 104     #~ THEN 5
 105 #~ END END
 106 #~ AS state
 107 #~ FROM res_partner p
 108 #~ WHERE p.free_member
 109 #~ OR p.associate_member > 0
 110 #~ UNION
 111 #~ SELECT p.id as partner,
 112     #~ MAX(CASE WHEN ai.state = 'paid' THEN 2
 113     #~ ELSE 0
 114     #~ END)
 115 #~ AS state
 116 #~ FROM res_partner p
 117 #~ JOIN account_invoice ai ON (
 118     #~ p.id = ai.partner_id
 119 #~ )
 120 #~ JOIN account_invoice_line ail ON (
 121     #~ ail.invoice_id = ai.id
 122 #~ )
 123 #~ JOIN membership_membership_line ml ON (
 124     #~ ml.account_invoice_line  = ail.id
 125 #~ )
 126 #~ WHERE ml.date_from < '%s'
 127 #~ AND ml.date_to < '%s'
 128 #~ AND ml.date_from <= ml.date_to
 129 #~ GROUP BY
 130 #~ p.id
 131 #~ )
 132 #~ AS members
 133 #~ GROUP BY members.partner
 134 #~ )
 135 #~ AS final
 136 #~ %s
 137 #~ '''
 138
 139
 140 class membership_line(osv.osv):
 141     '''Member line'''
 142
 143     def _check_membership_date(self, cr, uid, ids, context=None):
 144         '''Check if membership product is not in the past'''
 145
 146         cr.execute('''
 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
 151             )
 152         JOIN account_invoice ai ON (
 153             ai.id = ail.invoice_id)
 154         WHERE ml.id in (%s)
 155         ''' % ','.join([str(id) for id in ids]))
 156
 157         res = cr.fetchall()
 158         for r in res:
 159             if r[0] and r[0] < 0:
 160                 return False
 161         return True
 162
 163     def _state(self, cr, uid, ids, name, args, context=None):
 164         '''Compute the state lines'''
 165         res = {}
 166         for line in self.browse(cr, uid, ids):
 167             cr.execute('''
 168             SELECT i.state FROM
 169             account_invoice i WHERE
 170             i.id = (
 171                 SELECT l.invoice_id FROM
 172                 account_invoice_line l WHERE
 173                 l.id = (
 174                     SELECT  ml.account_invoice_line FROM
 175                     membership_membership_line ml WHERE
 176                     ml.id = %s
 177                     )
 178                 )
 179             ''', (line.id,))
 180             fetched = cr.fetchone()
 181             if not fetched :
 182                 res[line.id] = 'canceled'
 183                 continue
 184             istate = fetched[0]
 185             state = 'none'
 186             if (istate == 'draft') | (istate == 'proforma'):
 187                 state = 'waiting'
 188             elif istate == 'open':
 189                 state = 'invoiced'
 190             elif istate == 'paid':
 191                 state = 'paid'
 192             elif istate == 'cancel':
 193                 state = 'canceled'
 194             res[line.id] = state
 195         return res
 196
 197
 198     _description = __doc__
 199     _name = 'membership.membership_line'
 200     _columns = {
 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),
 207             }
 208     _rec_name = 'partner'
 209     _order = 'id desc'
 210     _constraints = [
 211             (_check_membership_date, 'Error, this membership product is out of date', [])
 212     ]
 213
 214 membership_line()
 215
 216
 217 class Partner(osv.osv):
 218     '''Partner'''
 219     _inherit = 'res.partner'
 220
 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)
 223         list_partner = []
 224         for data in data_inv:
 225             list_partner.append(data.partner.id)
 226         ids2 = list_partner
 227         while ids2:
 228             ids2 = self.pool.get('res.partner').search(cr, uid, [('associate_member','in',ids2)], context=context)
 229             list_partner += ids2
 230         return list_partner
 231
 232     def _get_invoice_partner(self, cr, uid, ids, context=None):
 233         data_inv = self.pool.get('account.invoice').browse(cr, uid, ids, context)
 234         list_partner = []
 235         for data in data_inv:
 236             list_partner.append(data.partner_id.id)
 237         ids2 = list_partner
 238         while ids2:
 239             ids2 = self.pool.get('res.partner').search(cr, uid, [('associate_member','in',ids2)], context=context)
 240             list_partner += ids2
 241         return list_partner
 242
 243     def _membership_state(self, cr, uid, ids, name, args, context=None):
 244         res = {}
 245         for id in ids:
 246             res[id] = 'none'
 247         today = time.strftime('%Y-%m-%d')
 248         for id in ids:
 249             partner_data = self.browse(cr,uid,id)
 250             if partner_data.membership_cancel and today > partner_data.membership_cancel:
 251                 res[id] = 'canceled'
 252                 continue
 253             if partner_data.membership_stop and today > partner_data.membership_stop:
 254                 res[id] = 'old'
 255                 continue
 256             s = 4
 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
 262                             if mstate == 'paid':
 263                                 s = 0
 264                                 break
 265                             elif mstate == 'open' and s!=0:
 266                                 s = 1
 267                             elif mstate == 'cancel' and s!=0 and s!=1:
 268                                 s = 2
 269                             elif  (mstate == 'draft' or mstate == 'proforma') and s!=0 and s!=1:
 270                                 s = 3
 271                 if s==4:
 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':
 274                             s = 5
 275                         else:
 276                             s = 6
 277                 if s==0:
 278                     res[id] = 'paid'
 279                 elif s==1:
 280                     res[id] = 'invoiced'
 281                 elif s==2:
 282                     res[id] = 'canceled'
 283                 elif s==3:
 284                     res[id] = 'waiting'
 285                 elif s==5:
 286                     res[id] = 'old'
 287                 elif s==6:
 288                     res[id] = 'none'
 289             if partner_data.free_member and s!=0:
 290                 res[id] = 'free'
 291             if partner_data.associate_member:
 292                 res_state = self._membership_state(cr, uid, [partner_data.associate_member.id], name, args, context)
 293                 res[id] = res_state[partner_data.associate_member.id]
 294         return res
 295
 296     def _membership_start(self, cr, uid, ids, name, args, context=None):
 297         '''Return the start date of membership'''
 298         res = {}
 299         member_line_obj = self.pool.get('membership.membership_line')
 300         for partner in self.browse(cr, uid, ids):
 301             if partner.associate_member:
 302                 partner_id = partner.associate_member.id
 303             else:
 304                 partner_id = partner.id
 305             line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
 306                     limit=1, order='date_from')
 307             if line_id:
 308                 res[partner.id] = member_line_obj.read(cr, uid, line_id[0],
 309                         ['date_from'])['date_from']
 310             else:
 311                 res[partner.id] = False
 312         return res
 313
 314     def _membership_stop(self, cr, uid, ids, name, args, context=None):
 315         '''Return the stop date of membership'''
 316         res = {}
 317         member_line_obj = self.pool.get('membership.membership_line')
 318         for partner in self.browse(cr, uid, ids):
 319             cr.execute('select membership_state from res_partner where id=%s', (partner.id,))
 320             data_state = cr.fetchall()
 321             if partner.associate_member:
 322                 partner_id = partner.associate_member.id
 323             else:
 324                 partner_id = partner.id
 325             line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
 326                     limit=1, order='date_to desc')
 327             if line_id:
 328                 res[partner.id] = member_line_obj.read(cr, uid, line_id[0],
 329                         ['date_to'])['date_to']
 330             else:
 331                 res[partner.id] = False
 332         return res
 333
 334     def _membership_cancel(self, cr, uid, ids, name, args, context=None):
 335         '''Return the cancel date of membership'''
 336         res = {}
 337         member_line_obj = self.pool.get('membership.membership_line')
 338         for partner_id in ids:
 339             line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
 340                     limit=1, order='date_cancel')
 341             if line_id:
 342                 res[partner_id] = member_line_obj.read(cr, uid, line_id[0],
 343                         ['date_cancel'])['date_cancel']
 344             else:
 345                 res[partner_id] = False
 346         return res
 347
 348     def _get_partners(self, cr, uid, ids, context={}):
 349         ids2 = ids
 350         while ids2:
 351             ids2 = self.search(cr, uid, [('associate_member','in',ids2)], context=context)
 352             ids+=ids2
 353         return ids
 354
 355     _columns = {
 356         'associate_member': fields.many2one('res.partner', 'Associate member'),
 357         'member_lines': fields.one2many('membership.membership_line', 'partner', 'Membership'),
 358         'free_member': fields.boolean('Free member'),
 359         'membership_amount': fields.float(
 360                     'Membership amount', digites=(16, 2),
 361                     help='The price negociated by the partner'),
 362         'membership_state': fields.function(
 363                     _membership_state, method = True,
 364                     string = 'Current membership state', type = 'selection',
 365                     selection = STATE ,store = {
 366                         'account.invoice':(_get_invoice_partner,['state'], 10),
 367                         'membership.membership_line':(_get_partner_id,['state'], 10),
 368                         'res.partner':(_get_partners, ['free_member', 'membership_state'], 10)
 369                         }
 370                     ),
 371         'membership_start': fields.function(
 372                     _membership_start, method=True,
 373                     string = 'Start membership date', type = 'date',
 374                     store = {
 375                         'account.invoice':(_get_invoice_partner,['state'], 10),
 376                         'membership.membership_line':(_get_partner_id,['state'], 10),
 377                         'res.partner':(lambda self,cr,uid,ids,c={}:ids, ['free_member'], 10)
 378                         }
 379                     ),
 380         'membership_stop': fields.function(
 381                     _membership_stop, method = True,
 382                     string = 'Stop membership date', type = 'date',
 383                     store = {
 384                         'account.invoice':(_get_invoice_partner,['state'], 10),
 385                         'membership.membership_line':(_get_partner_id,['state'], 10),
 386                         'res.partner':(lambda self,cr,uid,ids,c={}:ids, ['free_member'], 10)
 387                         }
 388                     ),
 389
 390         'membership_cancel': fields.function(
 391                     _membership_cancel, method = True,
 392                     string = 'Cancel membership date', type='date',
 393                     store = {
 394                         'account.invoice':(_get_invoice_partner,['state'], 10),
 395                         'membership.membership_line':(_get_partner_id,['state'], 10),
 396                         'res.partner':(lambda self,cr,uid,ids,c={}:ids, ['free_member'], 10)
 397                         }
 398                     ),
 399     }
 400     _defaults = {
 401         'free_member': lambda *a: False,
 402         'membership_cancel' : lambda *d : False,
 403     }
 404
 405     def _check_recursion(self, cr, uid, ids):
 406         level = 100
 407         while len(ids):
 408             cr.execute('select distinct associate_member from res_partner where id in ('+','.join(map(str,ids))+')')
 409             ids = filter(None, map(lambda x:x[0], cr.fetchall()))
 410             if not level:
 411                 return False
 412             level -= 1
 413         return True
 414
 415     _constraints = [
 416         (_check_recursion, 'Error ! You can not create recursive associated members.', ['associate_member'])
 417     ]
 418
 419 Partner()
 420
 421 class product_template(osv.osv):
 422     _inherit = 'product.template'
 423     _columns = {
 424             'member_price':fields.float('Member Price', digits=(16, int(config['price_accuracy']))),
 425             }
 426 product_template()
 427
 428 class Product(osv.osv):
 429
 430     def fields_view_get(self, cr, user, view_id=None, view_type='form', context=None, toolbar=False):
 431         if ('product' in context) and (context['product']=='membership_product'):
 432             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'])])
 433             resource_id_form = self.pool.get('ir.model.data').read(cr,user,model_data_ids_form,fields=['res_id','name'])
 434             dict_model={}
 435             for i in resource_id_form:
 436                 dict_model[i['name']]=i['res_id']
 437             if view_type=='form':
 438                 view_id = dict_model['membership_products_form']
 439             else:
 440                 view_id = dict_model['membership_products_tree']
 441         return super(Product,self).fields_view_get(cr, user, view_id, view_type, context, toolbar)
 442
 443     '''Product'''
 444     _inherit = 'product.product'
 445     _description = 'product.product'
 446
 447     _columns = {
 448             'membership': fields.boolean('Membership', help='Specify if this product is a membership product'),
 449             'membership_date_from': fields.date('Date from'),
 450             'membership_date_to': fields.date('Date to'),
 451 #           'member_price':fields.float('Member Price'),
 452             }
 453
 454     _defaults = {
 455             'membership': lambda *args: False
 456             }
 457 Product()
 458
 459
 460 class Invoice(osv.osv):
 461     '''Invoice'''
 462
 463     _inherit = 'account.invoice'
 464
 465     def action_cancel(self, cr, uid, ids, context=None):
 466         '''Create a 'date_cancel' on the membership_line object'''
 467         if context is None:
 468             context = {}
 469         member_line_obj = self.pool.get('membership.membership_line')
 470         today = time.strftime('%Y-%m-%d')
 471         for invoice in self.browse(cr, uid, ids):
 472             mlines = member_line_obj.search(cr,uid,
 473                     [('account_invoice_line','in',
 474                         [ l.id for l in invoice.invoice_line])], context)
 475             member_line_obj.write(cr,uid,mlines, {'date_cancel':today}, context)
 476         return super(Invoice, self).action_cancel(cr, uid, ids, context)
 477 Invoice()
 478
 479
 480 class ReportPartnerMemberYear(osv.osv):
 481     '''Membership by Years'''
 482
 483     _name = 'report.partner_member.year'
 484     _description = __doc__
 485     _auto = False
 486     _rec_name = 'year'
 487     _columns = {
 488         'year': fields.char('Year', size='4', readonly=True, select=1),
 489         'canceled_number': fields.integer('Canceled', readonly=True),
 490         'waiting_number': fields.integer('Waiting', readonly=True),
 491         'invoiced_number': fields.integer('Invoiced', readonly=True),
 492         'paid_number': fields.integer('Paid', readonly=True),
 493         'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
 494         'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
 495         'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
 496         'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
 497         'currency': fields.many2one('res.currency', 'Currency', readonly=True,
 498             select=2),
 499     }
 500
 501     def init(self, cr):
 502         '''Create the view'''
 503         cr.execute("""
 504     CREATE OR REPLACE VIEW report_partner_member_year AS (
 505         SELECT
 506         MIN(id) AS id,
 507         COUNT(ncanceled) as canceled_number,
 508         COUNT(npaid) as paid_number,
 509         COUNT(ninvoiced) as invoiced_number,
 510         COUNT(nwaiting) as waiting_number,
 511         SUM(acanceled) as canceled_amount,
 512         SUM(apaid) as paid_amount,
 513         SUM(ainvoiced) as invoiced_amount,
 514         SUM(awaiting) as waiting_amount,
 515         year,
 516         currency
 517         FROM (SELECT
 518             CASE WHEN ai.state = 'cancel' THEN ml.id END AS ncanceled,
 519             CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
 520             CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
 521             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 522                 THEN ml.id END AS nwaiting,
 523             CASE WHEN ai.state = 'cancel'
 524                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 525             ELSE 0 END AS acanceled,
 526             CASE WHEN ai.state = 'paid'
 527                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 528             ELSE 0 END AS apaid,
 529             CASE WHEN ai.state = 'open'
 530                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 531             ELSE 0 END AS ainvoiced,
 532             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 533                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 534             ELSE 0 END AS awaiting,
 535             TO_CHAR(ml.date_from, 'YYYY') AS year,
 536             ai.currency_id AS currency,
 537             MIN(ml.id) AS id
 538             FROM membership_membership_line ml
 539             JOIN (account_invoice_line ail
 540                 LEFT JOIN account_invoice ai
 541                 ON (ail.invoice_id = ai.id))
 542             ON (ml.account_invoice_line = ail.id)
 543             JOIN res_partner p
 544             ON (ml.partner = p.id)
 545             GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
 546             ai.currency_id, ml.id) AS foo
 547         GROUP BY year, currency)
 548                 """)
 549
 550 ReportPartnerMemberYear()
 551
 552
 553 class ReportPartnerMemberYearNew(osv.osv):
 554     '''New Membership by Years'''
 555
 556     _name = 'report.partner_member.year_new'
 557     _description = __doc__
 558     _auto = False
 559     _rec_name = 'year'
 560
 561     _columns = {
 562         'year': fields.char('Year', size='4', readonly=True, select=1),
 563         'canceled_number': fields.integer('Canceled', readonly=True),
 564         'waiting_number': fields.integer('Waiting', readonly=True),
 565         'invoiced_number': fields.integer('Invoiced', readonly=True),
 566         'paid_number': fields.integer('Paid', readonly=True),
 567         'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
 568         'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
 569         'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
 570         'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
 571         'currency': fields.many2one('res.currency', 'Currency', readonly=True,
 572             select=2),
 573     }
 574
 575     def init(self, cr):
 576         '''Create the view'''
 577         cr.execute("""
 578     CREATE OR REPLACE VIEW report_partner_member_year AS (
 579         SELECT
 580         MIN(id) AS id,
 581         COUNT(ncanceled) as canceled_number,
 582         COUNT(npaid) as paid_number,
 583         COUNT(ninvoiced) as invoiced_number,
 584         COUNT(nwaiting) as waiting_number,
 585         SUM(acanceled) as canceled_amount,
 586         SUM(apaid) as paid_amount,
 587         SUM(ainvoiced) as invoiced_amount,
 588         SUM(awaiting) as waiting_amount,
 589         year,
 590         currency
 591         FROM (SELECT
 592             CASE WHEN ai.state = 'cancel' THEN ml.id END AS ncanceled,
 593             CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
 594             CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
 595             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 596                 THEN ml.id END AS nwaiting,
 597             CASE WHEN ai.state = 'cancel'
 598                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 599             ELSE 0 END AS acanceled,
 600             CASE WHEN ai.state = 'paid'
 601                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 602             ELSE 0 END AS apaid,
 603             CASE WHEN ai.state = 'open'
 604                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 605             ELSE 0 END AS ainvoiced,
 606             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 607                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 608             ELSE 0 END AS awaiting,
 609             TO_CHAR(ml.date_from, 'YYYY') AS year,
 610             ai.currency_id AS currency,
 611             MIN(ml.id) AS id
 612             FROM membership_membership_line ml
 613             JOIN (account_invoice_line ail
 614                 LEFT JOIN account_invoice ai
 615                 ON (ail.invoice_id = ai.id))
 616             ON (ml.account_invoice_line = ail.id)
 617             JOIN res_partner p
 618             ON (ml.partner = p.id)
 619             GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
 620             ai.currency_id, ml.id) AS foo
 621         GROUP BY year, currency)
 622                 """)
 623
 624 ReportPartnerMemberYear()
 625
 626
 627 class ReportPartnerMemberYearNew(osv.osv):
 628     '''New Membership by Years'''
 629
 630     _name = 'report.partner_member.year_new'
 631     _description = __doc__
 632     _auto = False
 633     _rec_name = 'year'
 634     _columns = {
 635         'year': fields.char('Year', size='4', readonly=True, select=1),
 636         'canceled_number': fields.integer('Canceled', readonly=True),
 637         'waiting_number': fields.integer('Waiting', readonly=True),
 638         'invoiced_number': fields.integer('Invoiced', readonly=True),
 639         'paid_number': fields.integer('Paid', readonly=True),
 640         'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
 641         'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
 642         'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
 643         'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
 644         'currency': fields.many2one('res.currency', 'Currency', readonly=True,
 645             select=2),
 646     }
 647
 648     def init(self, cursor):
 649         '''Create the view'''
 650         cursor.execute("""
 651         CREATE OR REPLACE VIEW report_partner_member_year_new AS (
 652         SELECT
 653         MIN(id) AS id,
 654         COUNT(ncanceled) AS canceled_number,
 655         COUNT(npaid) AS paid_number,
 656         COUNT(ninvoiced) AS invoiced_number,
 657         COUNT(nwaiting) AS waiting_number,
 658         SUM(acanceled) AS canceled_amount,
 659         SUM(apaid) AS paid_amount,
 660         SUM(ainvoiced) AS invoiced_amount,
 661         SUM(awaiting) AS waiting_amount,
 662         year,
 663         currency
 664         FROM (SELECT
 665             CASE WHEN ai.state = 'cancel' THEN ml2.id END AS ncanceled,
 666             CASE WHEN ai.state = 'paid' THEN ml2.id END AS npaid,
 667             CASE WHEN ai.state = 'open' THEN ml2.id END AS ninvoiced,
 668             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 669                 THEN ml2.id END AS nwaiting,
 670             CASE WHEN ai.state = 'cancel'
 671                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 672             ELSE 0 END AS acanceled,
 673             CASE WHEN ai.state = 'paid'
 674                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 675             ELSE 0 END AS apaid,
 676             CASE WHEN ai.state = 'open'
 677                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 678             ELSE 0 END AS ainvoiced,
 679             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 680                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 681             ELSE 0 END AS awaiting,
 682             TO_CHAR(ml2.date_from, 'YYYY') AS year,
 683             ai.currency_id AS currency,
 684             MIN(ml2.id) AS id
 685             FROM (SELECT
 686                     partner AS id,
 687                     MIN(date_from) AS date_from
 688                     FROM membership_membership_line
 689                     GROUP BY partner
 690                 ) AS ml1
 691                 JOIN membership_membership_line ml2
 692                 JOIN (account_invoice_line ail
 693                     LEFT JOIN account_invoice ai
 694                     ON (ail.invoice_id = ai.id))
 695                 ON (ml2.account_invoice_line = ail.id)
 696                 ON (ml1.id = ml2.partner AND ml1.date_from = ml2.date_from)
 697             JOIN res_partner p
 698             ON (ml2.partner = p.id)
 699             GROUP BY TO_CHAR(ml2.date_from, 'YYYY'), ai.state,
 700             ai.currency_id, ml2.id) AS foo
 701         GROUP BY year, currency
 702         )
 703     """)
 704
 705 ReportPartnerMemberYearNew()
 706
 707 class account_invoice_line(osv.osv):
 708     _inherit='account.invoice.line'
 709     def write(self, cr, uid, ids, vals, context=None):
 710         if not context:
 711             context={}
 712         res = super(account_invoice_line, self).write(cr, uid, ids, vals, context=context)
 713         member_line_obj = self.pool.get('membership.membership_line')
 714         for line in self.browse(cr, uid, ids):
 715             ml_ids = member_line_obj.search(cr, uid, [('account_invoice_line','=',line.id)])
 716             if line.product_id and line.product_id.membership and not ml_ids:
 717                 # Product line has changed to a membership product
 718                 date_from = line.product_id.membership_date_from
 719                 date_to = line.product_id.membership_date_to
 720                 if line.invoice_id.date_invoice > date_from and line.invoice_id.date_invoice < date_to:
 721                     date_from = line.invoice_id.date_invoice
 722                 line_id = member_line_obj.create(cr, uid, {
 723                     'partner': line.invoice_id.partner_id.id,
 724                     'date_from': date_from,
 725                     'date_to': date_to,
 726                     'account_invoice_line': line.id,
 727                     })
 728             if line.product_id and not line.product_id.membership and ml_ids:
 729                 # Product line has changed to a non membership product
 730                 member_line_obj.unlink(cr, uid, ml_ids, context=context)
 731         return res
 732
 733     def unlink(self, cr, uid, ids, context=None):
 734         if not context:
 735             context={}
 736         member_line_obj = self.pool.get('membership.membership_line')
 737         for id in ids:
 738             ml_ids = member_line_obj.search(cr, uid, [('account_invoice_line','=',id)])
 739             member_line_obj.unlink(cr, uid, ml_ids, context=context)
 740         return super(account_invoice_line, self).unlink(cr, uid, ids, context=context)
 741
 742     def create(self, cr, uid, vals, context={}):
 743         result = super(account_invoice_line, self).create(cr, uid, vals, context)
 744         line = self.browse(cr, uid, result)
 745         member_line_obj = self.pool.get('membership.membership_line')
 746         ml_ids = member_line_obj.search(cr, uid, [('account_invoice_line','=',line.id)])
 747         if line.product_id and line.product_id.membership and not ml_ids:
 748             # Product line is a membership product
 749             date_from = line.product_id.membership_date_from
 750             date_to = line.product_id.membership_date_to
 751             if line.invoice_id.date_invoice > date_from and line.invoice_id.date_invoice < date_to:
 752                 date_from = line.invoice_id.date_invoice
 753             line_id = member_line_obj.create(cr, uid, {
 754                 'partner': line.invoice_id.partner_id and line.invoice_id.partner_id.id or False,
 755                 'date_from': date_from,
 756                 'date_to': date_to,
 757                 'account_invoice_line': line.id,
 758                 })
 759         return result
 760 account_invoice_line()
 761 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: