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