fix barcodes to 13 digits and update datelastseen

diff --git a/intranet/cgi-bin/fer/fer_fix_barcode.sql b/intranet/cgi-bin/fer/fer_fix_barcode.sql
new file mode 100644 (file)
index 0000000..79f5456
--- /dev/null
+++ b/intranet/cgi-bin/fer/fer_fix_barcode.sql
@@ -0,0 +1,20 @@
+-- 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 ;
+