====== Shared production data ======
==== Database access: technical information ====
^DB Server | 10.100.145.20 (fkt20g.fa.franke.world) |
^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|
^Database schema|shareddata|
^Username| michael.lehmann (readonly)|
^Password| Swh2Rj6Q|

==== E-R diagram ====
{{:cli:elica:airfactory-er.png|}} \\ \\

==== Data dictionary ====
=== Entities ===
^  Entity  ^^^^^^
^  Name  ^  Description  ^  Attributes  ^^^^^
^ ^ ^  Name  ^  Description  ^  Type  ^ Identifier  ^
| **prodline** | **Production resources list**|__codice__|Production resource unique identifier|varchar|  Yes  |
| | |descrizio|Production resource name|varchar| |
| | |codrep|Department unique identifier|varchar| |
| | |descrep|Department description|varchar| |
| | |codstab|Plant identifier|varchar| |
| | |descstab|Plant description|varchar| |
^ ^ ^ ^ ^ ^ ^
| **unitprod** |**Unique unit of production, produced items**|__codice__|Produced item unique identifier|varchar|  YES  |
| | |codart|Product code|varchar| |
| | |dart|Product description|varchar| |
| | |procord|Work order (from ERP)|varchar| |
| | |qtaprev|Work order total quantity to be produced|double| |
| | |unita|Produced items for each work cycle (generally 1)|double| |
| | |tmcr|Time Epoch of the tracked production unit (seconds since 01/01/1970)|long| |
| | |datacr|Date of the tracked production unit (YYYYMMGG)|varchar| |
| | |oracr|Time of the tracked production unit (HH:MM:SS)|varchar| |
^ ^ ^ ^ ^ ^ ^
| **qcausanc** |**Non compliant causes list**|__codice__|Cause unique identifier|varchar|  YES  |
| | |descrizio|Cause description|varchar| |
^ ^ ^ ^ ^ ^ ^
| **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| |
| | |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>