cli:franke:shared_data

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
cli:franke:shared_data [2018/08/27 12:23]
f.zoppini [Logical scheme] 		cli:franke:shared_data [2018/08/27 14:01] (current)
f.zoppini [Sample queries]
Line 2: Line 2:
 ==== Database access: technical information ==== ==== Database access: technical information ====
 ^DB Server | 10.100.145.20 (fkt20g.fa.franke.world) | ^DB Server | 10.100.145.20 (fkt20g.fa.franke.world) |
-^SB Server Port |5432|+^DB Server Port |5432|
 ^DBMS | Postgresql| ^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]] | ^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| ^Database name|andon_FWK|
-^Username|(readonly)| +^Database schema|shareddata| 
-^Password||+^Username| michael.lehmann (readonly)| 
 +^Password| Swh2Rj6Q|
  
 ==== E-R diagram ==== ==== E-R diagram ====
Line 34: Line 35:
 | | |oracr|Time of the tracked production unit (HH:MM:SS)|varchar| | | | |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| | | | |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| | | | |descrizio|Downtime description of the monitored resource|varchar| |
 | | |tipofm|Downtime type: F = unplanned downtime, M = microstop, P = planned downtime|varchar| | | | |tipofm|Downtime type: F = unplanned downtime, M = microstop, P = planned downtime|varchar| |
Line 72: Line 73:
 |**regfm**(__codice__, data, ora, tm, tmdiff, tipoevento, codubi, caufm)|- //codubi// foreign key on //prodline.codice// \\ - //caufm// foreign key on //causafm.codice//| |**regfm**(__codice__, data, ora, tm, tmdiff, tipoevento, codubi, caufm)|- //codubi// foreign key on //prodline.codice// \\ - //caufm// foreign key on //causafm.codice//|
  
-==== Quering samples ==== +==== Sample queries ==== 
-Selezione dei pezzi prodotti il 18 maggio 2017 con data, ora, identificativo univoco prodotto, descrizione della linea, descrizione del reparto, descrizione del prodotto e ordine di produzione, in ordine di istante di produzione+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> <code sql>
-select data, ora, codlot, prodline.descrizio as linea, prodline.descrep, unitprod.dart, unitprod.procord from regprod  +SELECT DATA, ora, codlot, prodline.descrizio AS linea, prodline.descrep, unitprod.dart, unitprod.procord FROM shareddata.regprod  
-inner join prodline on prodline.codice=regprod.codubi +INNER JOIN shareddata.prodline ON prodline.codice=shareddata.regprod.codubi 
-inner join unitprod on unitprod.codice=regprod.codlot +INNER JOIN shareddata.unitprod ON unitprod.codice=shareddata.regprod.codlot 
-where data='20170518' +WHERE DATA='20180618' 
-order by tm+ORDER BY tm
 </code> </code>
-Conteggio dei pezzi prodotti dopo il 17 Maggio (compreso) raggruppati per giorno, per prodotto e ordini di produzione, nella linea 04+Count produced items after June 18th 2018 (included) grouped by day, article code and work order on production resource 821102A (DMG LB)
 <code sql> <code sql>
-select data, unitprod.dart, unitprod.procord, sum(qta) as pezzi from regprod +select data, unitprod.dart, unitprod.procord, sum(qta) as pezzi from shareddata.regprod 
-inner join unitprod on unitprod.codice=regprod.codlot +inner join shareddata.unitprod on shareddata.unitprod.codice=shareddata.regprod.codlot 
-where regprod.codubi='04' and regprod.data>='20170517'+where regprod.codubi='821102A' and regprod.data>='20180618'
 group by data, dart, procord group by data, dart, procord
 </code> </code>
-Estrarre la lista delle fermate (inizio, fine) della linea 04 dalle ore 06:00 alle ore 14:00 del giorno 17 Maggio, con data, ora, differenza temporale tra due eventi in minuti, tipo di evento, descrizione della causa di fermo, tipo di fermo, setup (1,0)+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> <code sql>
-select data, ora, tmdiff/60 as dtm, tipoevento, causafm.descrizio as dfermo, causafm.tipofm, causafm.setup from regfm +select data, ora, tmdiff/60 as dtm, tipoevento, qcausafm.descrizio as dfermo, qcausafm.tipofm, qcausafm.setup from shareddata.regfm 
-inner join causafm on causafm.codice=regfm.caufm +inner join shareddata.qcausafm on shareddata.qcausafm.codice=regfm.caufm 
-where codubi='04' and data='20170517' and ora>='06:00' and ora<='14:00'+where codubi='821102A' and data='20180618' and ora>='06:00' and ora<='14:00'
 order by tm order by tm
 </code> </code>
-{{:cli:elica:2017-05-18.gif|}} 
  • cli/franke/shared_data.1535365384.txt.gz
  • Last modified: 2018/08/27 12:23
  • by f.zoppini