From e98983b390107ed1982fdd6890fe38267e2a2792 Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic Date: Wed, 17 Mar 2021 17:36:26 +0100 Subject: [PATCH] fix barcodes to 13 digits and update datelastseen --- intranet/cgi-bin/fer/fer_fix_barcode.sql | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) create mode 100644 intranet/cgi-bin/fer/fer_fix_barcode.sql diff --git a/intranet/cgi-bin/fer/fer_fix_barcode.sql b/intranet/cgi-bin/fer/fer_fix_barcode.sql new file mode 100644 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 ; + -- 2.20.1