[FIX] account: sql in constraint

Task 2002068
The constraints were executed before the data was written in the database. Therefore, the data was not correclty constrained.
Also add the invoice date in the constraint on duplicated vendor references as sometimes, your vendor uses a numbering that loops over time (each year it restarts at 0 for example) and in this case you may receive two different bills that legitimely have the same reference.

The reference on a refund is now 'Reversal of {name}: {reason}'. Before
the reason was in the payment reference, and it didn't make much sense.

closes odoo/odoo#35046

Signed-off-by: Quentin De Paoli (qdp) <qdp@openerp.com>

4 files changed, 24 insertions, 18 deletions

diff --git a/addons/account/models/account_move.py b/addons/account/models/account_move.py
index 26805800f3b..996c6cfb06a 100644
--- a/addons/account/models/account_move.py
+++ b/addons/account/models/account_move.py
@@ -8,7 +8,7 @@ from odoo.addons import decimal_precision as dp
 
 from collections import OrderedDict
 from datetime import date
-from itertools import groupby
+from itertools import groupby, chain
 from stdnum.iso7064 import mod_97_10
 from itertools import zip_longest
 
@@ -1219,8 +1219,11 @@ class AccountMove(models.Model):
             return
 
         self._cr.execute('''
-            SELECT move.id
-            FROM account_move move
+            WITH current_moves (id, name, state, company_id, journal_id, type) as ( values {} )
+            SELECT move2.id
+            FROM (    SELECT id, name, state, company_id, journal_id, type from account_move
+            UNION ALL SELECT id, name, state, company_id, journal_id, type from current_moves
+            ) move
             INNER JOIN account_move move2 ON
                 move2.name = move.name
                 AND move2.company_id = move.company_id
@@ -1230,7 +1233,7 @@ class AccountMove(models.Model):
             WHERE move.id IN %s
             AND move.state = 'posted'
             AND move2.state = 'posted'
-        ''', [tuple(self.ids)])
+        '''.format(", ".join(["(%s, %s, %s, %s, %s, %s)"] * len(self))), list(chain(*self.mapped(lambda r: [r.id, r.name, r.state, r.company_id.id, r.journal_id.id, r.type]))) + [tuple(self.ids)])
         res = self._cr.fetchone()
         if res:
             raise ValidationError(_('Posted journal entry must have an unique sequence number per company.'))
@@ -1242,18 +1245,22 @@ class AccountMove(models.Model):
             return
 
         self._cr.execute('''
-            SELECT move.id
-            FROM account_move move
+            WITH current_moves (id,  ref, company_id, commercial_partner_id, type, invoice_date) as ( values {} )
+            SELECT move2.id
+            FROM (    SELECT id, ref, company_id, commercial_partner_id, type, invoice_date from account_move
+            UNION ALL SELECT id, ref, company_id, commercial_partner_id, type, invoice_date from current_moves
+            ) move
             INNER JOIN account_move move2 ON
                 move2.ref = move.ref
                 AND move2.company_id = move.company_id
                 AND move2.commercial_partner_id = move.commercial_partner_id
                 AND move2.type = move.type
+                AND (move2.invoice_date = move.invoice_date OR move.invoice_date is NULL)
                 AND move2.id != move.id
             WHERE move.id IN %s
             AND move.type in ('in_invoice', 'in_refund')
             AND move.ref IS NOT NULL
-        ''', [tuple(self.ids)])
+        '''.format(", ".join(["(%s, %s, %s, %s, %s, CAST(%s as DATE))"] * len(self))), list(chain(*self.mapped(lambda r: [r.id, r.ref, r.company_id.id, r.commercial_partner_id.id, r.type, r.invoice_date or None]))) + [tuple(moves.ids)])
         if self._cr.fetchone():
             raise ValidationError(_('Duplicated vendor reference detected. You probably encoded twice the same vendor bill/credit note.'))
 
@@ -1824,8 +1831,8 @@ class AccountMove(models.Model):
         move_vals_list = []
         for move, default_values in zip(self, default_values_list):
             default_values.update({
-                'type': reverse_type_map[self.type],
-                'reversed_entry_id': self.id,
+                'type': reverse_type_map[move.type],
+                'reversed_entry_id': move.id,
             })
             move_vals_list.append(move._reverse_move_vals(default_values, cancel=cancel))
         reverse_moves = self.env['account.move'].create(move_vals_list)
diff --git a/addons/account/tests/test_account_move_in_invoice.py b/addons/account/tests/test_account_move_in_invoice.py
index b14c1206f4b..79f2353c9e2 100644
--- a/addons/account/tests/test_account_move_in_invoice.py
+++ b/addons/account/tests/test_account_move_in_invoice.py
@@ -11,7 +11,7 @@ class TestAccountMoveInInvoiceOnchanges(InvoiceTestCommon):
     @classmethod
     def setUpClass(cls):
         super(TestAccountMoveInInvoiceOnchanges, cls).setUpClass()
-        
+
         cls.invoice = cls.init_invoice('in_invoice')
 
         cls.product_line_vals_1 = {
@@ -892,13 +892,13 @@ class TestAccountMoveInInvoiceOnchanges(InvoiceTestCommon):
             **self.move_vals,
             'date': move_reversal.date,
             'state': 'draft',
-            'invoice_payment_ref': move_reversal.reason,
+            'ref': 'Reversal of: %s, %s' % (self.invoice.name, move_reversal.reason),
             'invoice_payment_state': 'not_paid',
         })
 
         move_reversal = self.env['account.move.reversal'].with_context(active_ids=self.invoice.ids).create({
             'date': fields.Date.from_string('2019-02-01'),
-            'reason': 'no reason',
+            'reason': 'because',
             'refund_method': 'cancel',
         })
         reversal = move_reversal.reverse_moves()
@@ -934,7 +934,7 @@ class TestAccountMoveInInvoiceOnchanges(InvoiceTestCommon):
             **self.move_vals,
             'date': move_reversal.date,
             'state': 'posted',
-            'invoice_payment_ref': move_reversal.reason,
+            'ref': 'Reversal of: %s, %s' % (self.invoice.name, move_reversal.reason),
             'invoice_payment_state': 'paid',
         })
 
diff --git a/addons/account/tests/test_account_move_out_invoice.py b/addons/account/tests/test_account_move_out_invoice.py
index 50038eb9efc..3c097924b31 100644
--- a/addons/account/tests/test_account_move_out_invoice.py
+++ b/addons/account/tests/test_account_move_out_invoice.py
@@ -13,7 +13,7 @@ class TestAccountMoveOutInvoiceOnchanges(InvoiceTestCommon):
     @classmethod
     def setUpClass(cls):
         super(TestAccountMoveOutInvoiceOnchanges, cls).setUpClass()
-        
+
         cls.invoice = cls.init_invoice('out_invoice')
 
         cls.product_line_vals_1 = {
@@ -878,7 +878,7 @@ class TestAccountMoveOutInvoiceOnchanges(InvoiceTestCommon):
             **self.move_vals,
             'date': move_reversal.date,
             'state': 'draft',
-            'invoice_payment_ref': move_reversal.reason,
+            'ref': 'Reversal of: %s, %s' % (self.invoice.name, move_reversal.reason),
             'invoice_payment_state': 'not_paid',
         })
 
@@ -921,7 +921,7 @@ class TestAccountMoveOutInvoiceOnchanges(InvoiceTestCommon):
             **self.move_vals,
             'date': move_reversal.date,
             'state': 'posted',
-            'invoice_payment_ref': move_reversal.reason,
+            'ref': 'Reversal of: %s, %s' % (self.invoice.name, move_reversal.reason),
             'invoice_payment_state': 'paid',
         })
 
diff --git a/addons/account/wizard/account_move_reversal.py b/addons/account/wizard/account_move_reversal.py
index bcf2acdb62d..4fcacbfa498 100644
--- a/addons/account/wizard/account_move_reversal.py
+++ b/addons/account/wizard/account_move_reversal.py
@@ -51,8 +51,7 @@ class AccountMoveReversal(models.TransientModel):
         default_values_list = []
         for move in moves:
             default_values_list.append({
-                'ref': _('Reversal of: %s') % move.name,
-                'invoice_payment_ref': self.reason,
+                'ref': _('Reversal of: %s, %s') % (move.name, self.reason),
                 'date': self.date or move.date,
                 'invoice_date': move.is_invoice(include_receipts=True) and (self.date or move.date) or False,
                 'journal_id': self.journal_id and self.journal_id.id or move.journal_id.id,