Differences

This shows you the differences between two versions of the page.

--- cli:franke:competition [2018/06/04 12:48]
g.capuzzi
+++ cli:franke:competition [2018/06/13 18:09] (current)
f.zoppini [Configurazioni aggiuntive database]
@@ Line 30: / Line 30: @@
 {{:cli:franke:tabella1.png?1200|}}
+===== Configurazioni aggiuntive database =====
+<code sql>
+CREATE TABLE public.thresholds (
+    codubi character varying(51),
+    codturnidet character varying(51),
+    thnp1 double precision,
+    thnp2 double precision,
+    ths1 double precision,
+    ths2 double precision
+);
+COMMENT ON COLUMN public.thresholds.codubi IS '1:1 ubicaz.codice';
+COMMENT ON COLUMN public.thresholds.codturnidet IS '1:1 turnidet.codice';
+COMMENT ON COLUMN public.thresholds.thnp1 IS 'soglia pezzi 1';
+COMMENT ON COLUMN public.thresholds.thnp2 IS 'soglia pezzi 2';
+COMMENT ON COLUMN public.thresholds.ths1 IS 'soglia scarti 1';
+COMMENT ON COLUMN public.thresholds.ths2 IS 'soglia scarti 2';
+ALTER TABLE ONLY public.thresholds
+    ADD CONSTRAINT uq_codubi_codturnidet UNIQUE (codubi, codturnidet);
+</code>
+==== Esempio estrazione dati per configurazione soglie ====
+<code sql>
+select ubicaz.codice, ubicaz.descrizio, ubicaz.codubib, u2.descrizio as dubib,
+concat_pipe(ubicaz.codturno||';'||turni.descrizio||';'||turnidet.codice||';'||turnidet.descrizio||';'||turnidet.orainsec||';'||turnidet.durata||';'||thresholds.thnp1||';'||thresholds.ths1) as info
+from ubicaz
+left join ubicaz u2 on ubicaz.codubib = u2.codice
+left join turni on ubicaz.codturno = turni.codice
+join turnidet on turni.codice = turnidet.codturno
+left join thresholds on ubicaz.codice = thresholds.codubi and turnidet.codice = thresholds.codturnidet
+where ubicaz.tiposet = 'P'
+group by ubicaz.codice, ubicaz.descrizio, ubicaz.codubib, u2.descrizio
+order by ubicaz.codice, ubicaz.descrizio
+</code>