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         ''' % ','.join([str(id) for id in 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 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_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', digits=(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', 'associate_member'], 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 in ('+','.join(map(str,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 Partner()
 417
 418 class product_template(osv.osv):
 419     _inherit = 'product.template'
 420     _columns = {
 421             'member_price':fields.float('Member Price', digits=(16, int(config['price_accuracy']))),
 422             }
 423 product_template()
 424
 425 class Product(osv.osv):
 426
 427     def fields_view_get(self, cr, user, view_id=None, view_type='form', context=None, toolbar=False):
 428         if ('product' in context) and (context['product']=='membership_product'):
 429             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'])])
 430             resource_id_form = self.pool.get('ir.model.data').read(cr,user,model_data_ids_form,fields=['res_id','name'])
 431             dict_model={}
 432             for i in resource_id_form:
 433                 dict_model[i['name']]=i['res_id']
 434             if view_type=='form':
 435                 view_id = dict_model['membership_products_form']
 436             else:
 437                 view_id = dict_model['membership_products_tree']
 438         return super(Product,self).fields_view_get(cr, user, view_id, view_type, context, toolbar)
 439
 440     '''Product'''
 441     _inherit = 'product.product'
 442     _description = 'product.product'
 443
 444     _columns = {
 445             'membership': fields.boolean('Membership', help='Specify if this product is a membership product'),
 446             'membership_date_from': fields.date('Date from'),
 447             'membership_date_to': fields.date('Date to'),
 448 #           'member_price':fields.float('Member Price'),
 449             }
 450
 451     _defaults = {
 452             'membership': lambda *args: False
 453             }
 454 Product()
 455
 456
 457 class Invoice(osv.osv):
 458     '''Invoice'''
 459
 460     _inherit = 'account.invoice'
 461
 462     def action_cancel(self, cr, uid, ids, context=None):
 463         '''Create a 'date_cancel' on the membership_line object'''
 464         if context is None:
 465             context = {}
 466         member_line_obj = self.pool.get('membership.membership_line')
 467         today = time.strftime('%Y-%m-%d')
 468         for invoice in self.browse(cr, uid, ids):
 469             mlines = member_line_obj.search(cr,uid,
 470                     [('account_invoice_line','in',
 471                         [ l.id for l in invoice.invoice_line])], context)
 472             member_line_obj.write(cr,uid,mlines, {'date_cancel':today}, context)
 473         return super(Invoice, self).action_cancel(cr, uid, ids, context)
 474 Invoice()
 475
 476
 477 class ReportPartnerMemberYear(osv.osv):
 478     '''Membership by Years'''
 479
 480     _name = 'report.partner_member.year'
 481     _description = __doc__
 482     _auto = False
 483     _rec_name = 'year'
 484     _columns = {
 485         'year': fields.char('Year', size='4', readonly=True, select=1),
 486         'canceled_number': fields.integer('Canceled', readonly=True),
 487         'waiting_number': fields.integer('Waiting', readonly=True),
 488         'invoiced_number': fields.integer('Invoiced', readonly=True),
 489         'paid_number': fields.integer('Paid', readonly=True),
 490         'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
 491         'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
 492         'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
 493         'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
 494         'currency': fields.many2one('res.currency', 'Currency', readonly=True,
 495             select=2),
 496     }
 497
 498     def init(self, cr):
 499         '''Create the view'''
 500         cr.execute("""
 501     CREATE OR REPLACE VIEW report_partner_member_year AS (
 502         SELECT
 503         MIN(id) AS id,
 504         COUNT(ncanceled) as canceled_number,
 505         COUNT(npaid) as paid_number,
 506         COUNT(ninvoiced) as invoiced_number,
 507         COUNT(nwaiting) as waiting_number,
 508         SUM(acanceled) as canceled_amount,
 509         SUM(apaid) as paid_amount,
 510         SUM(ainvoiced) as invoiced_amount,
 511         SUM(awaiting) as waiting_amount,
 512         year,
 513         currency
 514         FROM (SELECT
 515             CASE WHEN ai.state = 'cancel' THEN ml.id END AS ncanceled,
 516             CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
 517             CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
 518             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 519                 THEN ml.id END AS nwaiting,
 520             CASE WHEN ai.state = 'cancel'
 521                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 522             ELSE 0 END AS acanceled,
 523             CASE WHEN ai.state = 'paid'
 524                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 525             ELSE 0 END AS apaid,
 526             CASE WHEN ai.state = 'open'
 527                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 528             ELSE 0 END AS ainvoiced,
 529             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 530                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 531             ELSE 0 END AS awaiting,
 532             TO_CHAR(ml.date_from, 'YYYY') AS year,
 533             ai.currency_id AS currency,
 534             MIN(ml.id) AS id
 535             FROM membership_membership_line ml
 536             JOIN (account_invoice_line ail
 537                 LEFT JOIN account_invoice ai
 538                 ON (ail.invoice_id = ai.id))
 539             ON (ml.account_invoice_line = ail.id)
 540             JOIN res_partner p
 541             ON (ml.partner = p.id)
 542             GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
 543             ai.currency_id, ml.id) AS foo
 544         GROUP BY year, currency)
 545                 """)
 546
 547 ReportPartnerMemberYear()
 548
 549
 550 class ReportPartnerMemberYearNew(osv.osv):
 551     '''New Membership by Years'''
 552
 553     _name = 'report.partner_member.year_new'
 554     _description = __doc__
 555     _auto = False
 556     _rec_name = 'year'
 557
 558     _columns = {
 559         'year': fields.char('Year', size='4', readonly=True, select=1),
 560         'canceled_number': fields.integer('Canceled', readonly=True),
 561         'waiting_number': fields.integer('Waiting', readonly=True),
 562         'invoiced_number': fields.integer('Invoiced', readonly=True),
 563         'paid_number': fields.integer('Paid', readonly=True),
 564         'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
 565         'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
 566         'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
 567         'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
 568         'currency': fields.many2one('res.currency', 'Currency', readonly=True,
 569             select=2),
 570     }
 571
 572     def init(self, cr):
 573         '''Create the view'''
 574         cr.execute("""
 575     CREATE OR REPLACE VIEW report_partner_member_year AS (
 576         SELECT
 577         MIN(id) AS id,
 578         COUNT(ncanceled) as canceled_number,
 579         COUNT(npaid) as paid_number,
 580         COUNT(ninvoiced) as invoiced_number,
 581         COUNT(nwaiting) as waiting_number,
 582         SUM(acanceled) as canceled_amount,
 583         SUM(apaid) as paid_amount,
 584         SUM(ainvoiced) as invoiced_amount,
 585         SUM(awaiting) as waiting_amount,
 586         year,
 587         currency
 588         FROM (SELECT
 589             CASE WHEN ai.state = 'cancel' THEN ml.id END AS ncanceled,
 590             CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
 591             CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
 592             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 593                 THEN ml.id END AS nwaiting,
 594             CASE WHEN ai.state = 'cancel'
 595                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 596             ELSE 0 END AS acanceled,
 597             CASE WHEN ai.state = 'paid'
 598                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 599             ELSE 0 END AS apaid,
 600             CASE WHEN ai.state = 'open'
 601                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 602             ELSE 0 END AS ainvoiced,
 603             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 604                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 605             ELSE 0 END AS awaiting,
 606             TO_CHAR(ml.date_from, 'YYYY') AS year,
 607             ai.currency_id AS currency,
 608             MIN(ml.id) AS id
 609             FROM membership_membership_line ml
 610             JOIN (account_invoice_line ail
 611                 LEFT JOIN account_invoice ai
 612                 ON (ail.invoice_id = ai.id))
 613             ON (ml.account_invoice_line = ail.id)
 614             JOIN res_partner p
 615             ON (ml.partner = p.id)
 616             GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
 617             ai.currency_id, ml.id) AS foo
 618         GROUP BY year, currency)
 619                 """)
 620
 621 ReportPartnerMemberYear()
 622
 623
 624 class ReportPartnerMemberYearNew(osv.osv):
 625     '''New Membership by Years'''
 626
 627     _name = 'report.partner_member.year_new'
 628     _description = __doc__
 629     _auto = False
 630     _rec_name = 'year'
 631     _columns = {
 632         'year': fields.char('Year', size='4', readonly=True, select=1),
 633         'canceled_number': fields.integer('Canceled', readonly=True),
 634         'waiting_number': fields.integer('Waiting', readonly=True),
 635         'invoiced_number': fields.integer('Invoiced', readonly=True),
 636         'paid_number': fields.integer('Paid', readonly=True),
 637         'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
 638         'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
 639         'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
 640         'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
 641         'currency': fields.many2one('res.currency', 'Currency', readonly=True,
 642             select=2),
 643     }
 644
 645     def init(self, cursor):
 646         '''Create the view'''
 647         cursor.execute("""
 648         CREATE OR REPLACE VIEW report_partner_member_year_new AS (
 649         SELECT
 650         MIN(id) AS id,
 651         COUNT(ncanceled) AS canceled_number,
 652         COUNT(npaid) AS paid_number,
 653         COUNT(ninvoiced) AS invoiced_number,
 654         COUNT(nwaiting) AS waiting_number,
 655         SUM(acanceled) AS canceled_amount,
 656         SUM(apaid) AS paid_amount,
 657         SUM(ainvoiced) AS invoiced_amount,
 658         SUM(awaiting) AS waiting_amount,
 659         year,
 660         currency
 661         FROM (SELECT
 662             CASE WHEN ai.state = 'cancel' THEN ml2.id END AS ncanceled,
 663             CASE WHEN ai.state = 'paid' THEN ml2.id END AS npaid,
 664             CASE WHEN ai.state = 'open' THEN ml2.id END AS ninvoiced,
 665             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 666                 THEN ml2.id END AS nwaiting,
 667             CASE WHEN ai.state = 'cancel'
 668                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 669             ELSE 0 END AS acanceled,
 670             CASE WHEN ai.state = 'paid'
 671                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 672             ELSE 0 END AS apaid,
 673             CASE WHEN ai.state = 'open'
 674                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 675             ELSE 0 END AS ainvoiced,
 676             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 677                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 678             ELSE 0 END AS awaiting,
 679             TO_CHAR(ml2.date_from, 'YYYY') AS year,
 680             ai.currency_id AS currency,
 681             MIN(ml2.id) AS id
 682             FROM (SELECT
 683                     partner AS id,
 684                     MIN(date_from) AS date_from
 685                     FROM membership_membership_line
 686                     GROUP BY partner
 687                 ) AS ml1
 688                 JOIN membership_membership_line ml2
 689                 JOIN (account_invoice_line ail
 690                     LEFT JOIN account_invoice ai
 691                     ON (ail.invoice_id = ai.id))
 692                 ON (ml2.account_invoice_line = ail.id)
 693                 ON (ml1.id = ml2.partner AND ml1.date_from = ml2.date_from)
 694             JOIN res_partner p
 695             ON (ml2.partner = p.id)
 696             GROUP BY TO_CHAR(ml2.date_from, 'YYYY'), ai.state,
 697             ai.currency_id, ml2.id) AS foo
 698         GROUP BY year, currency
 699         )
 700     """)
 701
 702 ReportPartnerMemberYearNew()
 703
 704 class account_invoice_line(osv.osv):
 705     _inherit='account.invoice.line'
 706     def write(self, cr, uid, ids, vals, context=None):
 707         if not context:
 708             context={}
 709         res = super(account_invoice_line, self).write(cr, uid, ids, vals, context=context)
 710         member_line_obj = self.pool.get('membership.membership_line')
 711         for line in self.browse(cr, uid, ids):
 712             ml_ids = member_line_obj.search(cr, uid, [('account_invoice_line','=',line.id)])
 713             if line.product_id and line.product_id.membership and not ml_ids:
 714                 # Product line has changed to a membership product
 715                 date_from = line.product_id.membership_date_from
 716                 date_to = line.product_id.membership_date_to
 717                 if line.invoice_id.date_invoice > date_from and line.invoice_id.date_invoice < date_to:
 718                     date_from = line.invoice_id.date_invoice
 719                 line_id = member_line_obj.create(cr, uid, {
 720                     'partner': line.invoice_id.partner_id.id,
 721                     'date_from': date_from,
 722                     'date_to': date_to,
 723                     'account_invoice_line': line.id,
 724                     })
 725             if line.product_id and not line.product_id.membership and ml_ids:
 726                 # Product line has changed to a non membership product
 727                 member_line_obj.unlink(cr, uid, ml_ids, context=context)
 728         return res
 729
 730     def unlink(self, cr, uid, ids, context=None):
 731         if not context:
 732             context={}
 733         member_line_obj = self.pool.get('membership.membership_line')
 734         for id in ids:
 735             ml_ids = member_line_obj.search(cr, uid, [('account_invoice_line','=',id)])
 736             member_line_obj.unlink(cr, uid, ml_ids, context=context)
 737         return super(account_invoice_line, self).unlink(cr, uid, ids, context=context)
 738
 739     def create(self, cr, uid, vals, context={}):
 740         result = super(account_invoice_line, self).create(cr, uid, vals, context)
 741         line = self.browse(cr, uid, result)
 742         member_line_obj = self.pool.get('membership.membership_line')
 743         ml_ids = member_line_obj.search(cr, uid, [('account_invoice_line','=',line.id)])
 744         if line.product_id and line.product_id.membership and not ml_ids:
 745             # Product line is a membership product
 746             date_from = line.product_id.membership_date_from
 747             date_to = line.product_id.membership_date_to
 748             if line.invoice_id.date_invoice > date_from and line.invoice_id.date_invoice < date_to:
 749                 date_from = line.invoice_id.date_invoice
 750             line_id = member_line_obj.create(cr, uid, {
 751                 'partner': line.invoice_id.partner_id and line.invoice_id.partner_id.id or False,
 752                 'date_from': date_from,
 753                 'date_to': date_to,
 754                 'account_invoice_line': line.id,
 755                 })
 756         return result
 757
 758 account_invoice_line()
 759 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: