[Koha-bugs] [Bug 29689] Update to 21.11 broken auto-generated barcode in <branchcode>0001 option

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29689

--- Comment #13 from Manos PETRIDIS <egpetridis at yahoo.com> ---
In my installation (single active branch/library, not a lending library, less
than 9999 new items per month, autoBarcode set to <branchcode>yymm0001) I've
used the following SQL statement to fill missing barcodes. Should you decide to
use it, please remember to first check it thourougly and change "ABC" with your
own branch code. I know it will leave gaps in the barcode sequence, but I don't
really mind it.

UPDATE items
SET barcode = CONCAT (
                'ABC'
                ,lpad(substring(extract(YEAR FROM datelastseen), 3, 2), 2, 0)
                ,lpad(extract(month FROM datelastseen), 2, 0)
                ,lpad(1 + itemnumber - (
                                SELECT T.INI
                                FROM (
                                        SELECT min(i.itemnumber) AS ini
                                                ,extract(YEAR FROM
i.datelastseen) AS DTE_YY
                                                ,extract(month FROM
i.datelastseen) AS DTE_MM
                                        FROM items i
                                        WHERE i.homebranch = 'ABC'
                                        GROUP BY extract(YEAR FROM
i.datelastseen)
                                                ,extract(month FROM
i.datelastseen)
                                        ) T
                                WHERE T.dte_yy = extract(YEAR FROM
datelastseen)
                                        AND T.DTE_MM = extract(MONTH FROM
datelastseen)
                                ), 4, 0)
                )
WHERE items.homebranch = 'ABC'
        AND barcode IS NULL;

-- 
You are receiving this mail because:
You are watching all bug changes.