addons/membership/membership.py

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