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