fix barcodes to 13 digits and update datelastseen

[share-koha-fer] / intranet / cgi-bin / fer / fer_fix_barcode.sql

-- create table fer_i2 as select date_scanned, lpad(barcode + 0,13,'00000000') as barcode, source_id, timestamp from fer_inventura ;

-- select concat(date_scanned,barcode,source_id) as k,count(*),max(timestamp),min(timestamp) from fer_i2 group by k having count(*) > 1 ;



delete from fer_inventura where timestamp in ('2021-02-24 11:31:50','2021-03-02 13:00:56', '2021-03-02 13:14:22','2021-03-03 13:19:43','2021-03-09 09:38:25','2021-03-15 15:13:37');

update fer_inventura set barcode = lpad(barcode + 0,13,'00000000') ;


select i.barcode,i.datelastseen,fi.date_scanned from items i join fer_inventura fi on i.barcode = fi.barcode where i.datelastseen < date_scanned ;

update items i join fer_inventura fi on i.barcode = fi.barcode set datelastseen = date_scanned where i.datelastseen is null ;

update items i join fer_inventura fi on i.barcode = fi.barcode set datelastseen = date_scanned where i.datelastseen < date_scanned ;

-- repeat to update to lastest scan
update items i join fer_inventura fi on i.barcode = fi.barcode set datelastseen = date_scanned where i.datelastseen < date_scanned ;