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 = %d
 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 _membership_state_search_inv(self,cr,uid,ids):
 229         data_inv = self.pool.get('account.invoice').browse(cr,uid,ids)
 230         list_partner = []
 231         for data in data_inv:
 232             list_partner.append(data.partner_id.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
 241         for id in ids:
 242             partner_data = self.browse(cr,uid,id)
 243             if partner_data.membership_cancel and today > partner_data.membership_cancel:
 244                 res[id] = 'canceled'
 245                 continue
 246             if partner_data.membership_stop and today > partner_data.membership_stop:
 247                 res[id] = 'old'
 248                 continue
 249             s = 4
 250             if partner_data.member_lines:
 251                 for mline in partner_data.member_lines:
 252                     if mline.date_from <= today and mline.date_to >= today:
 253                         if mline.account_invoice_line and mline.account_invoice_line.invoice_id:
 254                             mstate = mline.account_invoice_line.invoice_id.state
 255                             if mstate == 'paid':
 256                                 s = 0
 257                                 break
 258                             elif mstate == 'open' and s!=0:
 259                                 s = 1
 260                             elif mstate == 'cancel' and s!=0 and s!=1:
 261                                 s = 2
 262                             elif  (mstate == 'draft' or mstate == 'proforma') and s!=0 and s!=1:
 263                                 s = 3
 264                 if s==4:
 265                     for mline in partner_data.member_lines:
 266                         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':
 267                             s = 5
 268                         else:
 269                             s = 6
 270                 if s==0:
 271                     res[id] = 'paid'
 272                 elif s==1:
 273                     res[id] = 'invoiced'
 274                 elif s==2:
 275                     res[id] = 'canceled'
 276                 elif s==3:
 277                     res[id] = 'waiting'
 278                 elif s==5:
 279                     res[id] = 'old'
 280                 elif s==6:
 281                     res[id] = 'none'
 282             if partner_data.free_member and s!=0:
 283                 res[id] = 'free'
 284             if partner_data.associate_member:
 285                 assciate_partner = self.browse(cr,uid,partner_data.associate_member.id)
 286                 cr.execute('select membership_state from res_partner where id=%d', (partner_data.id,))
 287                 data_partner_state = cr.fetchall()
 288                 for i in assciate_partner.member_lines:
 289                     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':
 290                         res[id] = 'associated'
 291 #
 292 #       '''Compute membership state of partners'''
 293 #       today = time.strftime('%Y-%m-%d')
 294 ##      res = {}
 295 ##      for id in ids:
 296 ##          res[id] = 'none'
 297 #       clause = 'WHERE partner IN (' + ','.join([str(id) for id in ids]) + ')'
 298 #       cr.execute(REQUETE % (today, today, today, today, today, today, clause))
 299 #       fetches = cr.fetchall()
 300 #       for fetch in fetches:
 301 #           res[fetch[0]] = fetch[1]
 302         return res
 303
 304 #no more need becaz of new functionality store attribut on function field
 305 #   def _membership_state_search(self, cr, uid, obj, name, args):
 306 #       '''Search on membership state'''
 307 #
 308 #       today = time.strftime('%Y-%m-%d')
 309 #       clause = 'WHERE '
 310 #       for i in range(len(args)):
 311 #           if i!=0:
 312 #               clause += 'OR '
 313 #           clause += 'state '+args[i][1]+" '"+args[i][2]+"' "
 314 #       cr.execute(REQUETE % (today, today, today, today, today, today, clause))
 315 #       ids=[x[0] for x in cr.fetchall()]
 316 #
 317 #       return [('id', 'in', ids)]
 318
 319     def _membership_start(self, cr, uid, ids, name, args, context=None):
 320         '''Return the start date of membership'''
 321         res = {}
 322         member_line_obj = self.pool.get('membership.membership_line')
 323         for partner in self.browse(cr, uid, ids):
 324             if partner.membership_state == 'associated':
 325                 partner_id = partner.associate_member.id
 326             else:
 327                 partner_id = partner.id
 328             line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
 329                     limit=1, order='date_from')
 330             if line_id:
 331                 res[partner.id] = member_line_obj.read(cr, uid, line_id[0],
 332                         ['date_from'])['date_from']
 333             else:
 334                 res[partner.id] = False
 335         return res
 336
 337     def _membership_start_search(self, cr, uid, obj, name, args):
 338         '''Search on membership start date'''
 339         if not len(args):
 340             return []
 341         where = ' AND '.join(['date_from '+x[1]+' \''+str(x[2])+'\''
 342             for x in args])
 343         cr.execute('SELECT partner, MIN(date_from) \
 344                 FROM ( \
 345                     SELECT partner, MIN(date_from) AS date_from \
 346                     FROM membership_membership_line \
 347                     GROUP BY partner \
 348                 ) AS foo \
 349                 WHERE '+where+' \
 350                 GROUP BY partner')
 351         res = cr.fetchall()
 352         if not res:
 353             return [('id', '=', '0')]
 354         return [('id', 'in', [x[0] for x in res])]
 355
 356     def _membership_stop(self, cr, uid, ids, name, args, context=None):
 357         '''Return the stop date of membership'''
 358         res = {}
 359         member_line_obj = self.pool.get('membership.membership_line')
 360         for partner in self.browse(cr, uid, ids):
 361             cr.execute('select membership_state from res_partner where id=%d', (partner.id,))
 362             data_state = cr.fetchall()
 363             #if partner.membership_state == 'associated':
 364             if data_state[0][0] == 'associated':
 365                 partner_id = partner.associate_member.id
 366             else:
 367                 partner_id = partner.id
 368             line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
 369                     limit=1, order='date_to desc')
 370             if line_id:
 371                 res[partner.id] = member_line_obj.read(cr, uid, line_id[0],
 372                         ['date_to'])['date_to']
 373             else:
 374                 res[partner.id] = False
 375         return res
 376
 377     def _membership_stop_search(self, cr, uid, obj, name, args):
 378         '''Search on membership stop date'''
 379         if not len(args):
 380             return []
 381         where = ' AND '.join(['date_to '+x[1]+' \''+str(x[2])+'\''
 382             for x in args])
 383         cr.execute('SELECT partner, MAX(date_to) \
 384                 FROM ( \
 385                     SELECT partner, MAX(date_to) AS date_to \
 386                     FROM membership_membership_line \
 387                     GROUP BY partner \
 388                 ) AS foo \
 389                 WHERE '+where+' \
 390                 GROUP BY partner')
 391         res = cr.fetchall()
 392         if not res:
 393             return [('id', '=', '0')]
 394         return [('id', 'in', [x[0] for x in res])]
 395
 396     def _membership_cancel(self, cr, uid, ids, name, args, context=None):
 397         '''Return the cancel date of membership'''
 398         res = {}
 399         member_line_obj = self.pool.get('membership.membership_line')
 400         for partner_id in ids:
 401             line_id = member_line_obj.search(cr, uid, [('partner', '=', partner_id)],
 402                     limit=1, order='date_cancel')
 403             if line_id:
 404                 res[partner_id] = member_line_obj.read(cr, uid, line_id[0],
 405                         ['date_cancel'])['date_cancel']
 406             else:
 407                 res[partner_id] = False
 408         return res
 409
 410     def _membership_cancel_search(self, cr, uid, obj, name, args):
 411         '''Search on membership cancel date'''
 412         if not len(args):
 413             return []
 414         where = ' AND '.join(['date_cancel '+x[1]+' \''+str(x[2])+'\''
 415             for x in args])
 416         cr.execute('SELECT partner, MIN(date_cancel) \
 417                 FROM ( \
 418                     SELECT partner, MIN(date_cancel) AS date_cancel \
 419                     FROM membership_membership_line \
 420                     GROUP BY partner \
 421                 ) AS foo \
 422                 WHERE '+where+' \
 423                 GROUP BY partner')
 424         res = cr.fetchall()
 425         if not res:
 426             return [('id', '=', '0')]
 427         return [('id', 'in', [x[0] for x in res])]
 428
 429
 430
 431     _inherit = 'res.partner'
 432     _columns = {
 433         'member_lines': fields.one2many('membership.membership_line', 'partner',
 434             'Membership'),
 435         'membership_amount': fields.float('Membership amount', digites=(16, 2),
 436             help='The price negociated by the partner'),
 437 #       'membership_state': fields.function(_membership_state, method=True, string='Current membership state',
 438 #           type='selection', selection=STATE, fnct_search=_membership_state_search),
 439         'membership_state': fields.function(_membership_state, method=True, string='Current membership state',
 440             type='selection',selection=STATE,store={'account.invoice':(['state'],_membership_state_search_inv)}),
 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_stop': fields.function(_membership_stop, method=True,
 447             string='Stop membership date', type='date',
 448             fnct_search=_membership_stop_search),
 449         'membership_cancel': fields.function(_membership_cancel, method=True,
 450             string='Cancel membership date', type='date',
 451             fnct_search=_membership_cancel_search),
 452     }
 453     _defaults = {
 454         'free_member': lambda *a: False,
 455         'membership_cancel' : lambda *d : False,
 456     }
 457
 458 Partner()
 459
 460 class product_template(osv.osv):
 461     _inherit = 'product.template'
 462     _columns = {
 463             'member_price':fields.float('Member Price', digits=(16, int(config['price_accuracy']))),
 464             }
 465 product_template()
 466
 467 class Product(osv.osv):
 468
 469     def fields_view_get(self, cr, user, view_id=None, view_type='form', context=None, toolbar=False):
 470         if ('product' in context) and (context['product']=='membership_product'):
 471             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'])])
 472             resource_id_form = self.pool.get('ir.model.data').read(cr,user,model_data_ids_form,fields=['res_id','name'])
 473             dict_model={}
 474             for i in resource_id_form:
 475                 dict_model[i['name']]=i['res_id']
 476             if view_type=='form':
 477                 view_id = dict_model['membership_products_form']
 478             else:
 479                 view_id = dict_model['membership_products_tree']
 480         return super(Product,self).fields_view_get(cr, user, view_id, view_type, context, toolbar)
 481
 482     '''Product'''
 483     _inherit = 'product.product'
 484     _description = 'product.product'
 485
 486     _columns = {
 487             'membership': fields.boolean('Membership', help='Specify if this product is a membership product'),
 488             'membership_date_from': fields.date('Date from'),
 489             'membership_date_to': fields.date('Date to'),
 490 #           'member_price':fields.float('Member Price'),
 491             }
 492
 493     _defaults = {
 494             'membership': lambda *args: False
 495             }
 496 Product()
 497
 498
 499 class Invoice(osv.osv):
 500     '''Invoice'''
 501
 502     _inherit = 'account.invoice'
 503
 504
 505     def action_move_create(self, cr, uid, ids, context=None):
 506         '''Create membership.membership_line if the product is for membership'''
 507         if context is None:
 508             context = {}
 509         member_line_obj = self.pool.get('membership.membership_line')
 510         partner_obj = self.pool.get('res.partner')
 511         for invoice in self.browse(cr, uid, ids):
 512
 513             # fetch already existing member lines
 514             former_mlines = member_line_obj.search(cr,uid,
 515                     [('account_invoice_line','in',
 516                         [ l.id for l in invoice.invoice_line])], context)
 517             # empty them :
 518             if former_mlines:
 519                 member_line_obj.write(cr,uid,former_mlines, {'account_invoice_line':False}, context)
 520
 521             for line in invoice.invoice_line:
 522                 if line.product_id and line.product_id.membership:
 523                     date_from = line.product_id.membership_date_from
 524                     date_to  = line.product_id.membership_date_to
 525                     if invoice.date_invoice > date_from and invoice.date_invoice < date_to:
 526                         date_from = invoice.date_invoice
 527                     member_line_obj.create(cr, uid, {
 528                         'partner': invoice.partner_id.id,
 529                         'date_from': date_from,
 530                         'date_to': date_to,
 531                         'account_invoice_line': line.id,
 532                         })
 533         return super(Invoice, self).action_move_create(cr, uid, ids, context)
 534
 535     def action_cancel(self, cr, uid, ids, context=None):
 536         '''Create a 'date_cancel' on the membership_line object'''
 537         if context is None:
 538             context = {}
 539         member_line_obj = self.pool.get('membership.membership_line')
 540         today = time.strftime('%Y-%m-%d')
 541         for invoice in self.browse(cr, uid, ids):
 542             mlines = member_line_obj.search(cr,uid,
 543                     [('account_invoice_line','in',
 544                         [ l.id for l in invoice.invoice_line])], context)
 545             member_line_obj.write(cr,uid,mlines, {'date_cancel':today}, context)
 546         return super(Invoice, self).action_cancel(cr, uid, ids, context)
 547 Invoice()
 548
 549
 550 class ReportPartnerMemberYear(osv.osv):
 551     '''Membership by Years'''
 552
 553     _name = 'report.partner_member.year'
 554     _description = __doc__
 555     _auto = False
 556     _rec_name = 'year'
 557     _columns = {
 558         'year': fields.char('Year', size='4', readonly=True, select=1),
 559         'canceled_number': fields.integer('Canceled', readonly=True),
 560         'waiting_number': fields.integer('Waiting', readonly=True),
 561         'invoiced_number': fields.integer('Invoiced', readonly=True),
 562         'paid_number': fields.integer('Paid', readonly=True),
 563         'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
 564         'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
 565         'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
 566         'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
 567         'currency': fields.many2one('res.currency', 'Currency', readonly=True,
 568             select=2),
 569     }
 570
 571     def init(self, cr):
 572         '''Create the view'''
 573         cr.execute("""
 574     CREATE OR REPLACE VIEW report_partner_member_year AS (
 575         SELECT
 576         MIN(id) AS id,
 577         COUNT(ncanceled) as canceled_number,
 578         COUNT(npaid) as paid_number,
 579         COUNT(ninvoiced) as invoiced_number,
 580         COUNT(nwaiting) as waiting_number,
 581         SUM(acanceled) as canceled_amount,
 582         SUM(apaid) as paid_amount,
 583         SUM(ainvoiced) as invoiced_amount,
 584         SUM(awaiting) as waiting_amount,
 585         year,
 586         currency
 587         FROM (SELECT
 588             CASE WHEN ai.state = 'cancel' THEN ml.id END AS ncanceled,
 589             CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
 590             CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
 591             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 592                 THEN ml.id END AS nwaiting,
 593             CASE WHEN ai.state = 'cancel'
 594                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 595             ELSE 0 END AS acanceled,
 596             CASE WHEN ai.state = 'paid'
 597                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 598             ELSE 0 END AS apaid,
 599             CASE WHEN ai.state = 'open'
 600                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 601             ELSE 0 END AS ainvoiced,
 602             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 603                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 604             ELSE 0 END AS awaiting,
 605             TO_CHAR(ml.date_from, 'YYYY') AS year,
 606             ai.currency_id AS currency,
 607             MIN(ml.id) AS id
 608             FROM membership_membership_line ml
 609             JOIN (account_invoice_line ail
 610                 LEFT JOIN account_invoice ai
 611                 ON (ail.invoice_id = ai.id))
 612             ON (ml.account_invoice_line = ail.id)
 613             JOIN res_partner p
 614             ON (ml.partner = p.id)
 615             GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
 616             ai.currency_id, ml.id) AS foo
 617         GROUP BY year, currency)
 618                 """)
 619
 620 ReportPartnerMemberYear()
 621
 622
 623 class ReportPartnerMemberYearNew(osv.osv):
 624     '''New Membership by Years'''
 625
 626     _name = 'report.partner_member.year_new'
 627     _description = __doc__
 628     _auto = False
 629     _rec_name = 'year'
 630
 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, cr):
 646         '''Create the view'''
 647         cr.execute("""
 648     CREATE OR REPLACE VIEW report_partner_member_year 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 ml.id END AS ncanceled,
 663             CASE WHEN ai.state = 'paid' THEN ml.id END AS npaid,
 664             CASE WHEN ai.state = 'open' THEN ml.id END AS ninvoiced,
 665             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 666                 THEN ml.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(ml.date_from, 'YYYY') AS year,
 680             ai.currency_id AS currency,
 681             MIN(ml.id) AS id
 682             FROM membership_membership_line ml
 683             JOIN (account_invoice_line ail
 684                 LEFT JOIN account_invoice ai
 685                 ON (ail.invoice_id = ai.id))
 686             ON (ml.account_invoice_line = ail.id)
 687             JOIN res_partner p
 688             ON (ml.partner = p.id)
 689             GROUP BY TO_CHAR(ml.date_from, 'YYYY'), ai.state,
 690             ai.currency_id, ml.id) AS foo
 691         GROUP BY year, currency)
 692                 """)
 693
 694 ReportPartnerMemberYear()
 695
 696
 697 class ReportPartnerMemberYearNew(osv.osv):
 698     '''New Membership by Years'''
 699
 700     _name = 'report.partner_member.year_new'
 701     _description = __doc__
 702     _auto = False
 703     _rec_name = 'year'
 704     _columns = {
 705         'year': fields.char('Year', size='4', readonly=True, select=1),
 706         'canceled_number': fields.integer('Canceled', readonly=True),
 707         'waiting_number': fields.integer('Waiting', readonly=True),
 708         'invoiced_number': fields.integer('Invoiced', readonly=True),
 709         'paid_number': fields.integer('Paid', readonly=True),
 710         'canceled_amount': fields.float('Canceled', digits=(16, 2), readonly=True),
 711         'waiting_amount': fields.float('Waiting', digits=(16, 2), readonly=True),
 712         'invoiced_amount': fields.float('Invoiced', digits=(16, 2), readonly=True),
 713         'paid_amount': fields.float('Paid', digits=(16, 2), readonly=True),
 714         'currency': fields.many2one('res.currency', 'Currency', readonly=True,
 715             select=2),
 716     }
 717
 718     def init(self, cursor):
 719         '''Create the view'''
 720         cursor.execute("""
 721         CREATE OR REPLACE VIEW report_partner_member_year_new AS (
 722         SELECT
 723         MIN(id) AS id,
 724         COUNT(ncanceled) AS canceled_number,
 725         COUNT(npaid) AS paid_number,
 726         COUNT(ninvoiced) AS invoiced_number,
 727         COUNT(nwaiting) AS waiting_number,
 728         SUM(acanceled) AS canceled_amount,
 729         SUM(apaid) AS paid_amount,
 730         SUM(ainvoiced) AS invoiced_amount,
 731         SUM(awaiting) AS waiting_amount,
 732         year,
 733         currency
 734         FROM (SELECT
 735             CASE WHEN ai.state = 'cancel' THEN ml2.id END AS ncanceled,
 736             CASE WHEN ai.state = 'paid' THEN ml2.id END AS npaid,
 737             CASE WHEN ai.state = 'open' THEN ml2.id END AS ninvoiced,
 738             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 739                 THEN ml2.id END AS nwaiting,
 740             CASE WHEN ai.state = 'cancel'
 741                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 742             ELSE 0 END AS acanceled,
 743             CASE WHEN ai.state = 'paid'
 744                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 745             ELSE 0 END AS apaid,
 746             CASE WHEN ai.state = 'open'
 747                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 748             ELSE 0 END AS ainvoiced,
 749             CASE WHEN (ai.state = 'draft' OR ai.state = 'proforma')
 750                 THEN SUM(ail.price_unit * ail.quantity * (1 - ail.discount / 100))
 751             ELSE 0 END AS awaiting,
 752             TO_CHAR(ml2.date_from, 'YYYY') AS year,
 753             ai.currency_id AS currency,
 754             MIN(ml2.id) AS id
 755             FROM (SELECT
 756                     partner AS id,
 757                     MIN(date_from) AS date_from
 758                     FROM membership_membership_line
 759                     GROUP BY partner
 760                 ) AS ml1
 761                 JOIN membership_membership_line ml2
 762                 JOIN (account_invoice_line ail
 763                     LEFT JOIN account_invoice ai
 764                     ON (ail.invoice_id = ai.id))
 765                 ON (ml2.account_invoice_line = ail.id)
 766                 ON (ml1.id = ml2.partner AND ml1.date_from = ml2.date_from)
 767             JOIN res_partner p
 768             ON (ml2.partner = p.id)
 769             GROUP BY TO_CHAR(ml2.date_from, 'YYYY'), ai.state,
 770             ai.currency_id, ml2.id) AS foo
 771         GROUP BY year, currency
 772         )
 773     """)
 774
 775 ReportPartnerMemberYearNew()
 776 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 777