def test0020reduce_ids_one_hole(self):
     '''
     Test reduce_ids continue list with one hole.
     '''
     self.assert_(('(((id >= %s) AND (id <= %s)) OR '
             '((id >= %s) AND (id <= %s)))', [0, 9, 20, 29])
         == reduce_ids('id', range(10) + map(lambda x: x + 20, range(10))))

    def __register__(cls, module_name):
        pool = Pool()
        StatementLine = pool.get('account.statement.line')
        cursor = Transaction().connection.cursor()
        sql_table = cls.__table__()

        super(Move, cls).__register__(module_name)

        # Migration from 3.4:
        # account.statement.line origin changed to account.statement
        statement_line = StatementLine.__table__()
        cursor.execute(*sql_table.join(statement_line,
                condition=(
                    Concat(StatementLine.__name__ + ',', statement_line.id)
                    == sql_table.origin
                    )
                ).select(sql_table.id, statement_line.statement,
                order_by=(sql_table.id, statement_line.statement)))
        for statement_id, values in groupby(cursor.fetchall(), itemgetter(1)):
            ids = [x[0] for x in values]
            for sub_ids in grouped_slice(ids):
                red_sql = reduce_ids(sql_table.id, sub_ids)
                cursor.execute(*sql_table.update(
                        columns=[sql_table.origin],
                        values=['account.statement,%s' % statement_id],
                        where=red_sql))

    def generate(cls, pains):
        pool = Pool()
        Payment = pool.get('condo.payment')
        payment = Payment.__table__()
        cursor = Transaction().connection.cursor()

        for pain in pains:
            pids = [p.id for group in pain.groups for p in group.payments]
            for sub_ids in grouped_slice(pids):
                red_sql = reduce_ids(payment.id, sub_ids)

                cursor.execute(*payment.update(
                        columns=[payment.state],
                        values=['approved'],
                        where=red_sql))
            try:
                tmpl = pain.get_sepa_template()
                message = tmpl.generate(pain=pain,
                    datetime=datetime, normalize=unicodedata.normalize,
                    ).filter(remove_comment).render()
                pain.message = message

                pain.save()
            except:
                Transaction().rollback()
                cls.raise_user_error('generate_error', (pain.reference,pain.company.party.name))
            else:
                Transaction().commit()

 def get_lastmodified(cls, uri, cache=None):
     pool = Pool()
     object_name, object_id = cls._uri2object(uri, cache=cache)
     if object_name == 'ir.attachment':
         Model = pool.get(object_name)
         if object_id:
             if cache is not None:
                 cache.setdefault(Model.__name__, {})
                 ids = cache[Model.__name__].keys()
                 if object_id not in ids:
                     ids.append(object_id)
                 elif 'lastmodified' in cache[Model.__name__][object_id]:
                     return cache[Model.__name__][object_id][
                         'lastmodified']
             else:
                 ids = [object_id]
             res = None
             cursor = Transaction().cursor
             table = Model.__table__()
             for sub_ids in grouped_slice(ids):
                 red_sql = reduce_ids(table.id, sub_ids)
                 cursor.execute(*table.select(table.id,
                         Extract('EPOCH',
                             Coalesce(table.write_date, table.create_date)),
                         where=red_sql))
                 for object_id2, date in cursor.fetchall():
                     if object_id2 == object_id:
                         res = date
                     if cache is not None:
                         cache[Model.__name__].setdefault(object_id2, {})
                         cache[Model.__name__][object_id2][
                             'lastmodified'] = date
             if res is not None:
                 return res
     return time.time()

 def get_creationdate(cls, uri, cache=None):
     pool = Pool()
     object_name, object_id = cls._uri2object(uri, cache=cache)
     if object_name == 'ir.attachment':
         Model = pool.get(object_name)
         if object_id:
             if cache is not None:
                 cache.setdefault(Model.__name__, {})
                 ids = cache[Model.__name__].keys()
                 if object_id not in ids:
                     ids.append(object_id)
                 elif 'creationdate' in cache[Model.__name__][object_id]:
                     return cache[Model.__name__][object_id][
                         'creationdate']
             else:
                 ids = [object_id]
             res = None
             cursor = Transaction().cursor
             for i in range(0, len(ids), cursor.IN_MAX):
                 sub_ids = ids[i:i + cursor.IN_MAX]
                 red_sql, red_ids = reduce_ids('id', sub_ids)
                 cursor.execute('SELECT id, '
                     'EXTRACT(epoch FROM create_date) '
                     'FROM "' + Model._table + '" '
                     'WHERE ' + red_sql, red_ids)
                 for object_id2, date in cursor.fetchall():
                     if object_id2 == object_id:
                         res = date
                     if cache is not None:
                         cache[Model.__name__].setdefault(object_id2, {})
                         cache[Model.__name__][object_id2][
                             'creationdate'] = date
             if res is not None:
                 return res
     return time.time()

    def get_creationdate(cls, uri, cache=None):
        Party = Pool().get('party.party')
        party_id = cls.vcard(uri)

        cursor = Transaction().cursor

        if party_id is None:
            raise DAV_NotFound
        if party_id:
            if cache is not None:
                cache.setdefault('_contact', {})
                ids = cache['_contact'].keys()
                if party_id not in ids:
                    ids.append(party_id)
                elif 'creationdate' in cache['_contact'][party_id]:
                    return cache['_contact'][party_id]['creationdate']
            else:
                ids = [party_id]
            res = None
            for i in range(0, len(ids), cursor.IN_MAX):
                sub_ids = ids[i:i + cursor.IN_MAX]
                red_sql, red_ids = reduce_ids('id', sub_ids)
                cursor.execute('SELECT id, '
                        'EXTRACT(epoch FROM create_date) '
                    'FROM "' + Party._table + '" '
                    'WHERE ' + red_sql, red_ids)
                for party_id2, date in cursor.fetchall():
                    if party_id2 == party_id:
                        res = date
                    if cache is not None:
                        cache['_contact'].setdefault(party_id2, {})
                        cache['_contact'][party_id2]['creationdate'] = date
            if res is not None:
                return res
        return super(Collection, cls).get_creationdate(uri, cache=cache)

 def _get_balance_query(cls, sub_ids):
     pool = Pool()
     Budget = pool.get('account.budget')
     BudgetAccount = pool.get('account.budget-account.account')
     Move = pool.get('account.move')
     MoveLine = pool.get('account.move.line')
     table = cls.__table__()
     table_a = Budget.__table__()
     table_c = Budget.__table__()
     account = BudgetAccount.__table__()
     move = Move.__table__()
     line = MoveLine.__table__()
     balance = Sum(Coalesce(line.credit, 0) - Coalesce(line.debit, 0)) 
     return table.join(table_a,
         condition=(table_a.id == table.budget)
         ).join(table_c,
         condition=(table_c.left >= table_a.left)
         & (table_c.right <= table_a.right)
         ).join(account, condition=account.budget == table_c.id
         ).join(line, condition=line.account == account.account
         ).join(move, condition=((move.id == line.move)
             & (move.period == table.period))).select(
         table.id, balance,
         where=reduce_ids(table.id, sub_ids),
         group_by=table.id)

    def get_pending_amount(cls, agents, name):
        pool = Pool()
        Commission = pool.get('commission')
        commission = Commission.__table__()
        cursor = Transaction().connection.cursor()

        ids = [a.id for a in agents]
        amounts = dict.fromkeys(ids, None)
        for sub_ids in grouped_slice(ids):
            where = reduce_ids(commission.agent, sub_ids)
            where &= commission.invoice_line == Null
            query = commission.select(commission.agent, Sum(commission.amount),
                where=where,
                group_by=commission.agent)
            cursor.execute(*query)
            amounts.update(dict(cursor.fetchall()))
        digits = cls.pending_amount.digits
        exp = Decimal(str(10.0 ** -digits[1]))
        for agent_id, amount in amounts.items():
            if amount:
                # SQLite uses float for SUM
                if not isinstance(amount, Decimal):
                    amount = Decimal(str(amount))
                amounts[agent_id] = amount.quantize(exp)
        return amounts

    def get_creationdate(cls, uri, cache=None):
        Party = Pool().get('party.party')
        party = Party.__table__()
        party_id = cls.vcard(uri)

        cursor = Transaction().cursor

        if party_id is None:
            raise DAV_NotFound
        if party_id:
            if cache is not None:
                cache.setdefault('_contact', {})
                ids = cache['_contact'].keys()
                if party_id not in ids:
                    ids.append(party_id)
                elif 'creationdate' in cache['_contact'][party_id]:
                    return cache['_contact'][party_id]['creationdate']
            else:
                ids = [party_id]
            res = None
            for sub_ids in grouped_slice(ids):
                red_sql = reduce_ids(party.id, sub_ids)
                cursor.execute(*party.select(party.id,
                        Extract('EPOCH', party.create_date),
                        where=red_sql))
                for party_id2, date in cursor.fetchall():
                    if party_id2 == party_id:
                        res = date
                    if cache is not None:
                        cache['_contact'].setdefault(party_id2, {})
                        cache['_contact'][party_id2]['creationdate'] = date
            if res is not None:
                return res
        return super(Collection, cls).get_creationdate(uri, cache=cache)

    def _get_duration_timesheet(works, invoiced):
        pool = Pool()
        TimesheetLine = pool.get('timesheet.line')
        cursor = Transaction().cursor
        line = TimesheetLine.__table__()

        durations = {}
        twork2work = dict((w.work.id, w.id) for w in works if w.work)
        ids = twork2work.keys()
        for sub_ids in grouped_slice(ids):
            red_sql = reduce_ids(line.work, sub_ids)
            if invoiced:
                where = line.invoice_line != Null
            else:
                where = line.invoice_line == Null
            cursor.execute(*line.select(line.work, Sum(line.duration),
                    where=red_sql & where,
                    group_by=line.work))
            for twork_id, duration in cursor.fetchall():
                if duration:
                    # SQLite uses float for SUM
                    if not isinstance(duration, datetime.timedelta):
                        duration = datetime.timedelta(seconds=duration)
                    durations[twork2work[twork_id]] = duration
        return durations

    def validate_active(self):
        #Deactivate mandate as unit mandate on canceled state
        if (self.id > 0) and self.state=='canceled':
            condoparties = Pool().get('condo.party').__table__()
            condopayments = Pool().get('condo.payment').__table__()
            cursor = Transaction().connection.cursor()

            cursor.execute(*condopayments.select(condopayments.id,
                                        where=(condopayments.sepa_mandate == self.id) & (
                                              (condopayments.state == 'draft') | (condopayments.state == 'approved')),
                                        ))
            ids = [ids for (ids,) in cursor.fetchall()]
            if len(ids):
                self.raise_user_error('Can\'t cancel mandate "%s".\nThere are %s payments in draft or approved state with this mandate!',
                                                              (self.identification, len(ids)))

            cursor.execute(*condoparties.select(condoparties.id,
                                        where=(condoparties.sepa_mandate == self.id)))
            ids = [ids for (ids,) in cursor.fetchall()]
            if len(ids):
                self.raise_user_warning('warn_canceled_mandate',
                    'Mandate "%s" will be canceled as mean of payment in %d unit(s)/apartment(s)!', (self.identification, len(ids)))

                for sub_ids in grouped_slice(ids):
                    red_sql = reduce_ids(condoparties.id, sub_ids)
                    # Use SQL to prevent double validate loop
                    cursor.execute(*condoparties.update(
                            columns=[condoparties.sepa_mandate],
                            values=[None],
                            where=red_sql))

    def get_lastmodified(cls, uri, cache=None):
        Todo = Pool().get('calendar.todo')

        cursor = Transaction().cursor

        calendar_id = cls.calendar(uri)
        if calendar_id and (uri[10:].split('/', 1) + [None])[1]:
            todo_id = cls.todo(uri, calendar_id=calendar_id)
            if todo_id:
                if cache is not None:
                    cache.setdefault('_calendar', {})
                    cache['_calendar'].setdefault(Todo.__name__, {})
                    ids = cache['_calendar'][Todo.__name__].keys()
                    if todo_id not in ids:
                        ids.append(todo_id)
                    elif 'lastmodified' in cache['_calendar'][
                            Todo.__name__][todo_id]:
                        return cache['_calendar'][Todo.__name__][
                            todo_id]['lastmodified']
                else:
                    ids = [todo_id]
                res = None
                for i in range(0, len(ids), cursor.IN_MAX / 2):
                    sub_ids = ids[i:i + cursor.IN_MAX / 2]
                    red_id_sql, red_id_ids = reduce_ids('id', sub_ids)
                    red_parent_sql, red_parent_ids = reduce_ids('parent',
                            sub_ids)
                    cursor.execute('SELECT COALESCE(parent, id), '
                            'MAX(EXTRACT(epoch FROM '
                            'COALESCE(write_date, create_date))) '
                        'FROM "' + Todo._table + '" '
                        'WHERE ' + red_id_sql + ' '
                            'OR ' + red_parent_sql + ' '
                        'GROUP BY parent, id', red_id_ids + red_parent_ids)
                    for todo_id2, date in cursor.fetchall():
                        if todo_id2 == todo_id:
                            res = date
                        if cache is not None:
                            cache['_calendar'][Todo.__name__]\
                                .setdefault(todo_id2, {})
                            cache['_calendar'][Todo.__name__][
                                todo_id2]['lastmodified'] = date
                if res is not None:
                    return res

        return super(Collection, cls).get_lastmodified(uri, cache=cache)

 def test_reduce_ids_float(self):
     'Test reduce_ids with integer as float'
     self.assertEqual(reduce_ids(self.table.id,
             [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0,
                 15.0, 18.0, 19.0, 21.0]),
         (((self.table.id >= 1.0) & (self.table.id <= 12.0))
             | (self.table.id.in_([15.0, 18.0, 19.0, 21.0]))))
     self.assertRaises(AssertionError, reduce_ids, self.table.id, [1.1])

 def test0040reduce_ids_complex(self):
     '''
     Test reduce_ids complex list.
     '''
     self.assertEqual(('(((id >= %s) AND (id <= %s)) OR '
             '(id IN (%s,%s,%s,%s,%s)))', [0, 14, 25, 26, 27, 28, 29]),
         reduce_ids('id', range(10) + map(lambda x: x + 25, range(5))
             + map(lambda x: x + 5, range(10))))

 def test0050reduce_ids_complex_small_continue(self):
     '''
     Test reduce_ids complex list with small continue.
     '''
     self.assertEqual(('(((id >= %s) AND (id <= %s)) '
             'OR (id IN (%s,%s,%s,%s)))', [1, 12, 15, 18, 19, 21]),
         reduce_ids('id', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 15, 18,
                 19, 21]))

 def test0055reduce_ids_float(self):
     '''
     Test reduce_ids with integer as float.
     '''
     self.assert_(('(((id >= %s) AND (id <= %s)) OR (id IN (%s,%s,%s,%s)))',
             [1.0, 12.0, 15.0, 18.0, 19.0, 21.0])
         == reduce_ids('id', [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0,
                 10.0, 11.0, 12.0, 15.0, 18.0, 19.0, 21.0]))
     self.assertRaises(AssertionError, reduce_ids, 'id', [1.1])

    def get_receivable_payable(cls, parties, names):
        '''
        Function to compute receivable, payable (today or not) for party ids.
        '''
        result = {}
        pool = Pool()
        MoveLine = pool.get('account.move.line')
        Account = pool.get('account.account')
        User = pool.get('res.user')
        Date = pool.get('ir.date')
        cursor = Transaction().cursor

        line = MoveLine.__table__()
        account = Account.__table__()

        for name in names:
            if name not in ('receivable', 'payable',
                    'receivable_today', 'payable_today'):
                raise Exception('Bad argument')
            result[name] = dict((p.id, Decimal('0.0')) for p in parties)

        user = User(Transaction().user)
        if not user.company:
            return result
        company_id = user.company.id

        line_query, _ = MoveLine.query_get(line)

        amount = Sum(Coalesce(line.debit, 0) - Coalesce(line.credit, 0))
        for name in names:
            code = name
            today_where = Literal(True)
            if name in ('receivable_today', 'payable_today'):
                code = name[:-6]
                today_where = ((line.maturity_date <= Date.today())
                    | (line.maturity_date == Null))
            for sub_parties in grouped_slice(parties):
                sub_ids = [p.id for p in sub_parties]
                party_where = reduce_ids(line.party, sub_ids)
                cursor.execute(*line.join(account,
                        condition=account.id == line.account
                        ).select(line.party, amount,
                        where=(account.active
                            & (account.kind == code)
                            & (line.reconciliation == Null)
                            & (account.company == company_id)
                            & line_query
                            & party_where
                            & today_where
                            & (account.kind == code)),
                        group_by=line.party))
                for party, value in cursor.fetchall():
                    # SQLite uses float for SUM
                    if not isinstance(value, Decimal):
                        value = Decimal(str(value))
                    result[name][party] = value
        return result

    def _purchase_cost(cls, works):
        'Compute direct purchase cost'
        pool = Pool()
        Currency = pool.get('currency.currency')
        PurchaseLine = pool.get('purchase.line')
        InvoiceLine = pool.get('account.invoice.line')
        Invoice = pool.get('account.invoice')
        Company = pool.get('company.company')

        cursor = Transaction().cursor
        table = cls.__table__()
        purchase_line = PurchaseLine.__table__()
        invoice_line = InvoiceLine.__table__()
        invoice = Invoice.__table__()
        company = Company.__table__()

        amounts = defaultdict(Decimal)
        work_ids = [w.id for w in works]
        work2currency = {}
        iline2work = {}
        for sub_ids in grouped_slice(work_ids):
            where = reduce_ids(table.id, sub_ids)
            cursor.execute(*table.join(purchase_line,
                    condition=purchase_line.work == table.id
                    ).join(invoice_line,
                    condition=invoice_line.origin == Concat(
                        'purchase.line,', purchase_line.id)
                    ).join(invoice,
                    condition=invoice_line.invoice == invoice.id
                    ).select(invoice_line.id, table.id,
                    where=where & ~invoice.state.in_(['draft', 'cancel'])))
            iline2work.update(cursor.fetchall())

            cursor.execute(*table.join(company,
                    condition=table.company == company.id
                    ).select(table.id, company.currency,
                    where=where))
            work2currency.update(cursor.fetchall())

        currencies = Currency.browse(set(work2currency.itervalues()))
        id2currency = {c.id: c for c in currencies}

        invoice_lines = InvoiceLine.browse(iline2work.keys())
        for invoice_line in invoice_lines:
            invoice = invoice_line.invoice
            work_id = iline2work[invoice_line.id]
            currency_id = work2currency[work_id]
            currency = id2currency[currency_id]
            if currency != invoice.currency:
                with Transaction().set_context(date=invoice.currency_date):
                    amount = Currency.compute(invoice.currency,
                        invoice_line.amount, currency)
            else:
                amount = invoice_line.amount
            amounts[work_id] += amount
        return amounts

    def get_lastmodified(cls, uri, cache=None):
        Todo = Pool().get('calendar.todo')
        todo = Todo.__table__()

        cursor = Transaction().cursor

        calendar_id = cls.calendar(uri)
        if calendar_id and (uri[10:].split('/', 1) + [None])[1]:
            todo_id = cls.todo(uri, calendar_id=calendar_id)
            if todo_id:
                if cache is not None:
                    cache.setdefault('_calendar', {})
                    cache['_calendar'].setdefault(Todo.__name__, {})
                    ids = cache['_calendar'][Todo.__name__].keys()
                    if todo_id not in ids:
                        ids.append(todo_id)
                    elif 'lastmodified' in cache['_calendar'][
                            Todo.__name__][todo_id]:
                        return cache['_calendar'][Todo.__name__][
                            todo_id]['lastmodified']
                else:
                    ids = [todo_id]
                res = None
                for sub_ids in grouped_slice(ids, cursor.IN_MAX / 2):
                    red_id_sql = reduce_ids(todo.id, sub_ids)
                    red_parent_sql = reduce_ids(todo.parent, sub_ids)
                    cursor.execute(*todo.select(Coalesce(todo.parent, todo.id),
                            Max(Extract('EPOCH', Coalesce(todo.write_date,
                                        todo.create_date))),
                            where=red_id_sql | red_parent_sql,
                            group_by=(todo.parent, todo.id)))
                    for todo_id2, date in cursor.fetchall():
                        if todo_id2 == todo_id:
                            res = date
                        if cache is not None:
                            cache['_calendar'][Todo.__name__]\
                                .setdefault(todo_id2, {})
                            cache['_calendar'][Todo.__name__][
                                todo_id2]['lastmodified'] = date
                if res is not None:
                    return res

        return super(Collection, cls).get_lastmodified(uri, cache=cache)

    def get_quantity_executed(cls, lines, name):
        cursor = Transaction().cursor
        pool = Pool()
        Move = pool.get("stock.move")
        Location = pool.get("stock.location")
        Uom = pool.get("product.uom")
        Forecast = pool.get("stock.forecast")
        LineMove = pool.get("stock.forecast.line-stock.move")

        result = dict((x.id, 0) for x in lines)
        key = lambda line: line.forecast.id
        lines.sort(key=key)
        for forecast_id, lines in itertools.groupby(lines, key):
            forecast = Forecast(forecast_id)
            product2line = dict((line.product.id, line) for line in lines)
            product_ids = product2line.keys()
            for i in range(0, len(product_ids), cursor.IN_MAX):
                sub_ids = product_ids[i : i + cursor.IN_MAX]
                red_sql, red_ids = reduce_ids("product", sub_ids)
                cursor.execute(
                    "SELECT m.product, "
                    "SUM(m.internal_quantity) AS quantity "
                    'FROM "' + Move._table + '" AS m '
                    'JOIN "' + Location._table + '" AS fl '
                    "ON m.from_location = fl.id "
                    'JOIN "' + Location._table + '" AS tl '
                    "ON m.to_location = tl.id "
                    'LEFT JOIN "' + LineMove._table + '" AS lm '
                    "ON m.id = lm.move "
                    "WHERE " + red_sql + " "
                    "AND fl.left >= %s AND fl.right <= %s "
                    "AND tl.left >= %s AND tl.right <= %s "
                    "AND m.state != %s "
                    "AND COALESCE(m.effective_date, m.planned_date) >= %s "
                    "AND COALESCE(m.effective_date, m.planned_date) <= %s "
                    "AND lm.id IS NULL "
                    "GROUP BY m.product",
                    red_ids
                    + [
                        forecast.warehouse.left,
                        forecast.warehouse.right,
                        forecast.destination.left,
                        forecast.destination.right,
                        "cancel",
                        forecast.from_date,
                        forecast.to_date,
                    ],
                )
                for product_id, quantity in cursor.fetchall():
                    line = product2line[product_id]
                    result[line.id] = Uom.compute_qty(line.product.default_uom, quantity, line.uom)
        return result