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:10]
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 ==== ==== 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 13: Line 14:
  
 ==== Data dictionary ==== ==== Data dictionary ====
-=== Entity ===+=== Entities ===
 ^  Entity  ^^^^^^ ^  Entity  ^^^^^^
 ^  Name  ^  Description  ^  Attributes  ^^^^^ ^  Name  ^  Description  ^  Attributes  ^^^^^
Line 25: Line 26:
 ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
 | **unitprod** |**Unique unit of production, produced items**|__codice__|Produced item unique identifier|varchar|  YES  | | **unitprod** |**Unique unit of production, produced items**|__codice__|Produced item unique identifier|varchar|  YES  |
-| | |codart|Codice del prodotto cui si riferisce l'unità|varchar| | +| | |codart|Product code|varchar| | 
-| | |dart|Descrizione del prodotto cui si riferisce l'unità|varchar| | +| | |dart|Product description|varchar| | 
-| | |procord|Numero dell'ordine di produzione da SAP|varchar| | +| | |procord|Work order (from ERP)|varchar| | 
-| | |qtaprev|Numero di unità di prodotto da produrre per l'ordine di produzione|double| | +| | |qtaprev|Work order total quantity to be produced|double| | 
-| | |unita|Numero di pezzi che compongono l'unità di produzione (in genere 1)|double| | +| | |unita|Produced items for each work cycle (generally 1)|double| | 
-| | |tmcr|Time Epoch di creazione dell'unità (numero di secondi dal 1 Gen 1970)|long| | +| | |tmcr|Time Epoch of the tracked production unit (seconds since 01/01/1970)|long| | 
-| | |datacr|Data di creazione dell'unità nel formato YYYYMMGG|varchar| | +| | |datacr|Date of the tracked production unit (YYYYMMGG)|varchar| | 
-| | |oracr|Ora di creazione dell'unità nel formato HH:MM:SS|varchar| |+| | |oracr|Time of the tracked production unit (HH:MM:SS)|varchar| |
 ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
-| **causanc** |**Lista delle causali di non conformità**|__codice__|Codice univoco della causale|varchar|  SI  | +| **qcausanc** |**Non compliant causes list**|__codice__|Cause unique identifier|varchar|  YES  | 
-| | |descrizio|Descrizione della causale di non conformità|varchar| |+| | |descrizio|Cause description|varchar| |
 ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
-| **causafm** |**Lista delle causali di fermo linea**|__codice__|Codice univoco della causale|varchar|  SI  | +| **qcausafm** |**Downtime causes list**|__codice__|Downtime unique identifier|varchar|  YES  | 
-| | |descrizio|Descrizione della causale di fermo linea|varchar| | +| | |descrizio|Downtime description of the monitored resource|varchar| | 
-| | |tipofm|Tipologia di fermo: F = downtime, M = micro-fermata, P = pausa programmata|varchar| | +| | |tipofm|Downtime type: F = unplanned downtime, M = microstop, P = planned downtime|varchar| | 
-| | |setup|Riferita alle fermate di tipo setup: 1 = fermata di tipo setup, 0 = fermata di altro tipo|integer| |+| | |setup|Downtime for setup reason (flag): 1 = setup kind, 0 = general downtime|integer| |
  
 +=== Relationships ===
 +^  Relationship  ^^^^^^
 +^ Name ^  Description  ^  Components  ^ Attributes  ^^^
 +^ ^ ^ ^ Name  ^  Description  ^  Type ^
 +| **regprod** |**Each row refers to a production event**|  prodline, unitprod  |data|Event date (YYYYMMGG)|varchar|
 +| | | |ora|Event time (HH:MM:SS)|varchar|
 +| | | |tm|Event Time Epoch|long|
 +| | | |qta|Number of produced items|double|
 +^ ^ ^ ^ ^ ^ ^
 +| **regnc** |**Each row refers to a non compliant production event**|  prodline, unitprod, causanc  |data|Event date (YYYYMMGG)|varchar|
 +| | | |ora|Event time (HH:MM:SS)|varchar|
 +| | | |tm|Event Time Epoch|long|
 +| | | |qta|Number of non compliant produced items|double|
 +^ ^ ^ ^ ^ ^ ^
 +| **regfm** |**Each row refers to a begin or an end of a downtime**|  prodline, causafm  |data|Event date (YYYYMMGG)|varchar|
 +| | | |ora|Event time (HH:MM:SS)|varchar|
 +| | | |tm|Event Time Epoch|long|
 +| | | |tmdiff|Time difference from end and begin of the downtime events|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>
  • cli/franke/shared_data.1535364601.txt.gz
  • Last modified: 2018/08/27 12:10
  • by f.zoppini