Differences

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

--- cli:franke:shared_data [2018/08/27 12:17]
f.zoppini [Entity]
+++ cli:franke:shared_data [2018/08/27 14:01] (current)
f.zoppini [Sample queries]
@@ Line 2: / Line 2: @@
 ==== Database access: technical information ====
 ^DB Server | 10.100.145.20 (fkt20g.fa.franke.world) |
-^SB Server Port |5432|
+^DB Server Port |5432|
 ^DBMS | Postgresql|
 ^ODBC Driver | [[https://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_09_03_0300-1.zip|32bit]], [[https://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_09_03_0300-x64-1.zip|64bit]] |
 ^Database name|andon_FWK|
-^Username|(readonly)|
+^Database schema|shareddata|
-^Password||
+^Username| michael.lehmann (readonly)|
+^Password| Swh2Rj6Q|
 ==== E-R diagram ====
@@ Line 34: / Line 35: @@
 | | |oracr|Time of the tracked production unit (HH:MM:SS)|varchar| |
 ^ ^ ^ ^ ^ ^ ^
-| **causanc** |**Non compliant causes list**|__codice__|Cause unique identifier|varchar|  YES  |
+| **qcausanc** |**Non compliant causes list**|__codice__|Cause unique identifier|varchar|  YES  |
 | | |descrizio|Cause description|varchar| |
 ^ ^ ^ ^ ^ ^ ^
-| **causafm** |**Downtime causes list**|__codice__|Downtime unique identifier|varchar|  YES  |
+| **qcausafm** |**Downtime causes list**|__codice__|Downtime unique identifier|varchar|  YES  |
 | | |descrizio|Downtime description of the monitored resource|varchar| |
 | | |tipofm|Downtime type: F = unplanned downtime, M = microstop, P = planned downtime|varchar| |
@@ Line 47: / Line 48: @@
 ^ ^ ^ ^ Name  ^  Description  ^  Type ^
 | **regprod** |**Each row refers to a production event**|  prodline, unitprod  |data|Event date (YYYYMMGG)|varchar|
-| | | |ora|Ora dell'evento nel formato HH:MM:SS|varchar|
+| | | |ora|Event time (HH:MM:SS)|varchar|
-| | | |tm|Time Epoch dell'evento (numero di secondi dal 1 Gen 1970)|long|
+| | | |tm|Event Time Epoch|long|
-| | | |qta|Numero di pezzi prodotti riferiti all'unità di produzione|double|
+| | | |qta|Number of produced items|double|
 ^ ^ ^ ^ ^ ^ ^
-| **regnc** |**Ogni riga è un evento di non conformità di prodotto**|  prodline, unitprod, causanc  |data|Data dell'evento nel formato YYYYMMGG|varchar|
+| **regnc** |**Each row refers to a non compliant production event**|  prodline, unitprod, causanc  |data|Event date (YYYYMMGG)|varchar|
-| | | |ora|Ora dell'evento nel formato HH:MM:SS|varchar|
+| | | |ora|Event time (HH:MM:SS)|varchar|
-| | | |tm|Time Epoch dell'evento (numero di secondi dal 1 Gen 1970)|long|
+| | | |tm|Event Time Epoch|long|
-| | | |qta|Numero di pezzi scartati riferiti all'unità di produzione|double|
+| | | |qta|Number of non compliant produced items|double|
 ^ ^ ^ ^ ^ ^ ^
-| **regfm** |**Ogni riga è un evento di inizio o fine fermo linea**|  prodline, causafm  |data|Data di inizio o fine fermo nel formato YYYYMMGG|varchar|
+| **regfm** |**Each row refers to a begin or an end of a downtime**|  prodline, causafm  |data|Event date (YYYYMMGG)|varchar|
-| | | |ora|Ora di inizio o fine fermo nel formato HH:MM:SS|varchar|
+| | | |ora|Event time (HH:MM:SS)|varchar|
-| | | |tm|Time Epoch di inizio o fine fermo (numero di secondi dal 1 Gen 1970)|long|
+| | | |tm|Event Time Epoch|long|
-| | | |tmdiff|Differenza temporale in secondi tra due eventi|long|
+| | | |tmdiff|Time difference from end and begin of the downtime events|long|
-| | | |tipoevento|INFM=inizio del fermo, FIFM=fine del fermo|long|
+| | | |tipoevento|Downtime event type: INFM=downtime start, FIFM=donwtime end|long|
+==== Logical scheme ====
+^Table^Foreign keys^
+|**prodline**(__codice__, descrizio, codrep, descrep, codstab, descstab)| |
+|**unitprod**(__codice__, codart, dart, procord, qtaprev, unita, tmcr, datacr, oracr)| |
+|**causanc**(__codice__, descrizio)| |
+|**causafm**(__codice__, descrizio, tipofm, setup)| |
+|**regprod**(__codice__, data, ora, tm, qta, codlot, codubi)|- //codlot// foreign key on //unitprod.codice// \\ - //codubi// foreign key on //prodline.codice//|
+|**regnc**(__codice__, data, ora, tm, qta, codlot, codubi, caudef)|- //codlot// foreign key on //unitprod.codice// \\ - //codubi// foreign key on //prodline.codice// \\ - //caudef// foreign key on //causanc.codice//|
+|**regfm**(__codice__, data, ora, tm, tmdiff, tipoevento, codubi, caufm)|- //codubi// foreign key on //prodline.codice// \\ - //caufm// foreign key on //causafm.codice//|
+==== Sample queries ====
+Produced items on June 18th 2018 with date, time, unique identifier, production resource description, department description, article code (model) and work order, sorted by event time epoch
+<code sql>
+SELECT DATA, ora, codlot, prodline.descrizio AS linea, prodline.descrep, unitprod.dart, unitprod.procord FROM shareddata.regprod
+INNER JOIN shareddata.prodline ON prodline.codice=shareddata.regprod.codubi
+INNER JOIN shareddata.unitprod ON unitprod.codice=shareddata.regprod.codlot
+WHERE DATA='20180618'
+ORDER BY tm
+</code>
+Count produced items after June 18th 2018 (included) grouped by day, article code and work order on production resource 821102A (DMG LB)
+<code sql>
+select data, unitprod.dart, unitprod.procord, sum(qta) as pezzi from shareddata.regprod
+inner join shareddata.unitprod on shareddata.unitprod.codice=shareddata.regprod.codlot
+where regprod.codubi='821102A' and regprod.data>='20180618'
+group by data, dart, procord
+</code>
+Get the DMG LB (821102A) downtime events list from 06:00 to 14:00 of June 18th 2018 with date, time, duration in minutes, event type, downtime cause description, downtime kind, changeover time flag
+<code sql>
+select data, ora, tmdiff/60 as dtm, tipoevento, qcausafm.descrizio as dfermo, qcausafm.tipofm, qcausafm.setup from shareddata.regfm
+inner join shareddata.qcausafm on shareddata.qcausafm.codice=regfm.caufm
+where codubi='821102A' and data='20180618' and ora>='06:00' and ora<='14:00'
+order by tm
+</code>