addons/membership/membership.py

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