addons/membership/membership.py

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