[REF] supplier_invoice_number_unique: Use unique sql constraint instead.

[moylop260]

For a customer instance we have the following profiling results validating a invoice:

ncalls     tottime    tt_percall cumtime    ct_percall file:lineno (method)
2/1        0.000      0.000      21.459     21.459     /home/odoo/addons-vauxoo/account_anglo_saxon_stock_move/model/invoice.py:123 (invoice_validate)
9/8        0.000      0.000      12.770     1.596      /home/odoo/addons-vauxoo/invoice_datetime/invoice.py:119 (write)
1          0.000      0.000      11.045     11.045     /home/odoo/addons-vauxoo/account_tax_importation/model/account_invoice.py:221 (invoice_validate)
1          0.000      0.000      10.231     10.231     /home/odoo/addons-vauxoo/account_invoice_line_asset_category_required/models/ail_asset_category_required.py:65 (invoice_validate)
1          0.000      0.000      10.206     10.206     /home/odoo/addons-vauxoo/supplier_invoice_number_unique/model/account_invoice.py:65 (invoice_validate)
...

The invoice_validate use too much resources to detect a supplier_invoice_number unique.

This PR change that constraint a sql unique constraint faster

NOTE: Before to install this module you need verify that the following sql don't emit results: SELECT supplier_invoice_number,commercial_partner_id,count(*) AS repeated FROM account_invoice WHERE supplier_invoice_number IS NOT NULL AND state NOT IN ('draft', 'cancel') AND type IN ('in_invoice', 'in_refund') GROUP BY commercial_partner_id,supplier_invoice_number, company_id HAVING count(*) >=2

[moylop260]

@josemoralesp What is the status of the revision of this PR?

[josemoralesp]

Hello @moylop260 We can add this change without problems in Apex, but Lodi has more than 69 records with problems. I figured out that all records belong to the years 2014-2015, only two records in this year What do you advise me for this case?

I don't know the impact if we do something like this:

UPDATE 
    account_invoice AS i 
SET 
    supplier_invoice_number = i.id || '-' || i.supplier_invoice_number 
FROM 
    account_invoice AS ii 
WHERE 
    lower(regexp_replace(i.supplier_invoice_number, '\W', '', 'g')) = lower(regexp_replace(ii.supplier_invoice_number, '\W', '', 'g'))
    AND i.commercial_partner_id = ii.commercial_partner_id 
    AND i.company_id = ii.company_id AND i.type IN ('in_invoice', 'in_refund') 
    AND i.state NOT IN ('draft', 'cancel') AND i.id > ii.id;

What do you think?

[moylop260]

Remove special chars in the where using:

- i.supplier_invoice_number = ii.supplier_invoice_number 
+ lower(regexp_replace(i.supplier_invoice_number, '\W', '', 'g')) = lower(regexp_replace(ii.supplier_invoice_number, '\W', '', 'g'))

[moylop260]

@dsabrinarg Could we merge it?

[dsabrinarg]

@moylop260 waiting by the proper PR for lodi (needed code is still inside the DUMMY) in order to merge both PR's, I let you know asap!

[dsabrinarg]

@moylop260 done!