Community mailing list archives

community@mail.odoo.com

Check holes in sequence progress

by
Sergio Corato
- 05/07/2016 15:46:40
Hi all,
I played to check holes or prefix error in sequences with only postgresql, I wonder if is it possible (I'm not so good in sql).
My code works good on checking prefix errors, not in sequence hole (temp table vars is anyway linked to account invoice table, so can't show holes), perhaps python is needed (or I missed an already existant module?)
Thanks for any opinion!

WITH vars AS (
SELECT asf.sequence_id AS sequence_id, ir.prefix AS prefix,
generate_series((REGEXP_REPLACE(min(i.internal_number), '^(.*/*/)', '')::integer),(REGEXP_REPLACE(max(i.internal_number), '^(.*/*/)', '')::integer))
AS progress_internal_number from
account_invoice i left join account_period ap on (i.period_id=ap.id)
    left join account_journal j on (i.journal_id=j.id)
    left join account_sequence_fiscalyear asf on (ap.fiscalyear_id=asf.fiscalyear_id and j.sequence_id=asf.sequence_main_id)
    left join ir_sequence ir on (asf.sequence_id=ir.id)
    group by asf.sequence_id, i.internal_number, ir.prefix
    order by asf.sequence_id, ir.prefix)
select
i.id as id, i.registration_date as registration_date, i.date_invoice as date_invoice, i.internal_number as internal_number,
i.period_id as period_id, i.journal_id as journal_id, ir.prefix as prefix,
(REGEXP_REPLACE((i.internal_number), '^(.*/*/)', '')::integer) as inumb,
CASE WHEN
    char_length(REPLACE(i.internal_number, ir.prefix, '')) = char_length(i.internal_number)
    THEN 'Prefix error'
     WHEN
    (REGEXP_REPLACE((i.internal_number), '^(.*/*/)', '')::integer) != vars.progress_internal_number
    THEN 'error1'
     ELSE i.internal_number
     END
from
account_invoice i
left join account_period ap on (i.period_id=ap.id)
left join account_journal j on (i.journal_id=j.id)
left join account_sequence_fiscalyear asf on (ap.fiscalyear_id=asf.fiscalyear_id and j.sequence_id=asf.sequence_main_id)
left join ir_sequence ir on (asf.sequence_id=ir.id)
left join vars vars on (asf.sequence_id=vars.sequence_id and ir.prefix=vars.prefix and (REGEXP_REPLACE((i.internal_number), '^(.*/*/)', '')::integer)=vars.progress_internal_number)
order by ir.prefix asc, i.internal_number asc


Sergio Corato
Product Manager
SimplERP srl
tel. +390498597240
fax +390498596066