===== Shared database =====

==== Database access technical data ====
^Server IP|//regional server IP//|
^Port|5432|
^Database type|PostgreSQL|
^Access mode|ODBC|
^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_//PLANT_CODE//|
^Username|frankerp|
^Password|f17!|

==== E-R database schema ====
{{:cli:franke:er-schema.png|}} \\ \\

==== Data dictionary ====
=== Entities ===
^  Entity  ^^^^^^
^  Name  ^  Description  ^  Attributes  ^^^^^
^ ^ ^  Name  ^  Description  ^  Type ^ Identifier  ^
| **prodline** | **Production lines**|__key__|Line unique key|varchar|  SI  |
| | |descrizio|Line description|varchar| |
| | |codrep|Department code|varchar| |
| | |descrep|Department name|varchar| |
| | |codstab|Plant code|varchar| |
| | |descstab|Plant description|varchar| |
^ ^ ^ ^ ^ ^ ^
| **unitprod** |**Unique production id, produces items**|__codice__|Production unit unique code|varchar|  YES  |
| | |codart|Article code|varchar| |
| | |dart|Article descrption|varchar| |
| | |procord|Production Order (ERP)|varchar| |
| | |qtaprev|Number of items to be produced per production order|double| |
| | |unita|Number of pieces per unit production (usually 1)|double| |
| | |tmcr|Production unit creation Epoch Time (number of seconds from Jan 1, 1970)|long| |
| | |datacr|Creation date in YYYYMMDD format (database date)|varchar| |
| | |oracr|Creatione time formatted as HH:MM:SS (HH=00..24) |varchar| |
^ ^ ^ ^ ^ ^ ^
| **causanc** |**Non-conformity causes list**|__codice__|Cause unique code|varchar|  SI  |
| | |descrizio|Non-conformity cause description|varchar| |
^ ^ ^ ^ ^ ^ ^
| **causafm** |**Line stop causes list**|__codice__|Stop cause unique code|varchar|  SI  |
| | |descrizio|Line stop cause description|varchar| |
| | |tipofm|Type of stop: F = downtime, M = micro-stop, P = programmed pause|varchar| |
| | |setup|Referred to tooling stops: 1 = stop is a tooling, 0 = stop is not a tooling|integer| |

=== Relations ===
^  Relations  ^^^^^^
^ Name ^  Description  ^  Components ^ Attributes  ^^^
^ ^ ^ ^ Name  ^  Description  ^  Type ^
| **regprod** |**Each row represents a production event**|  prodline, unitprod  |data|Event date formatted as YYYYMMDD|varchar|
| | | |ora|Event time formatted as HH:MM:SS|varchar|
| | | |tm|Event Epoch time (number of seconds elapsed from Jan 1, 1970)|long|
| | | |qta|Number of produced pieces in production units|double|
^ ^ ^ ^ ^ ^ ^
| **regnc** |**Each row represents a non-compliant event**|  prodline, unitprod, causanc  |data|Event date formatted as YYYYMMDD|varchar|
| | | |ora|Event type formatted as HH:MM:SS|varchar|
| | | |tm|Event Time Epoch (number of seconds from Jan 1, 1970)|long|
| | | |qta|Number of scrapt items (production units)|double|
^ ^ ^ ^ ^ ^ ^
| **regfm** |**Every row represents a begin or end of a line stop**|  prodline, causafm  |data|Event date formatted as YYYYMMDD|varchar|
| | | |ora|Event type formatted as HH:MM:SS|varchar|
| | | |tm|Event Time Epoch (number of seconds from Jan 1, 1970)|long|
| | | |tmdiff|Time difference between 2 consecutive events, being them start or stop doesn't matter|long|
| | | |tipoevento|INFM=line stop begins, FIFM=line stop ends|long|

==== Logical Schema ====
^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//|
==== Example Queries ====
Produced pieces on Sep 18, 2017 with date, time, unique identifier, line description, department description, article description and production order, ordered by production time
<code sql>
select data, ora, codlot, prodline.descrizio as linea, prodline.descrep, unitprod.dart, unitprod.procord from regprod 
inner join prodline on prodline.codice=regprod.codubi
inner join unitprod on unitprod.codice=regprod.codlot
where data='20170918'
order by tm
</code>

Produced pieces after Sep 17 (included) grouped by day, product and production order on line 151301
<code sql>
select data, unitprod.dart, unitprod.procord, sum(qta) as pezzi from regprod
inner join unitprod on unitprod.codice=regprod.codlot
where regprod.codubi='151301' and regprod.data>='20170917'
group by data, dart, procord
</code>

List of line 151301 stop (begins and ends) from 06:00 to 14:00 of Sep 17, with date, time, run/stop time in minutes, type of stop, stop cause description, stop type, setup (1,0)
<code sql>
select data, ora, tmdiff/60 as dtm, tipoevento, causafm.descrizio as dfermo, causafm.tipofm, causafm.setup from regfm
inner join causafm on causafm.codice=regfm.caufm
where codubi='151301' and data='20170917' and ora>='06:00' and ora<='14:00'
order by tm
</code>