Samuele Locatelli
81 lines
2.5 KiB
Transact-SQL
81 lines
2.5 KiB
Transact-SQL
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
BEGIN tran
|
|
|
|
-- dichiarazioni variabili x recupero dati
|
|
DECLARE @LastIdx INT = 0
|
|
DECLARE @NewIdx INT = 0
|
|
|
|
-- recupero valore ultima riga stato processata...
|
|
SELECT @LastIdx = ISNULL(LastIdx,0)
|
|
FROM SyncState
|
|
WHERE TableName = 'ToMachineSigLog'
|
|
|
|
-- recupero nuovo MAX idx
|
|
SELECT @NewIdx = ISNULL(MAX(ID),0)
|
|
FROM syn_M2ERP_MACHINE_STATE
|
|
|
|
-- eseguo SOLO SE ho dati aggiuntivi...
|
|
IF(@NewIdx > @LastIdx)
|
|
BEGIN
|
|
/* --------------------------------------------------
|
|
* DECODIFICA STANDARD
|
|
* STATE MACHINE 60
|
|
* bitmap MAPO
|
|
* B0: POWER_ON
|
|
* B1: RUN
|
|
* B2: pzCount
|
|
* B3: allarme
|
|
* B4: manuale
|
|
* B5: slowTC
|
|
* B6: WarmUpCoolDown
|
|
* B7: emergenza
|
|
* -------------------------------------------------- */
|
|
|
|
-- decodifica colonne PAMA:
|
|
-- M_ON --> PowerON (B0)
|
|
-- Auto --> Lavora (B1)
|
|
-- AlarmMode --> Allarme (B3)
|
|
-- ManualMode/Mda/Jog --> Manuale (B4)
|
|
-- Mda/Jog --> Manuale (B4)
|
|
-- ManualMode --> Manuale (B4)
|
|
|
|
-- recupero i nuovi record da processare...
|
|
;WITH cteSigLog AS
|
|
(
|
|
SELECT [DateTime] AS DtEve
|
|
,Targa AS IdxMacchina
|
|
, 128 -- B7: emergenza armata (B7 --> 128)
|
|
+ CASE WHEN M_ON = 1 THEN 1 ELSE 0 END -- B0: spenta
|
|
+ CASE WHEN [Auto] = 1 THEN 2 ELSE 0 END -- B1: lavora
|
|
+ CASE WHEN AlarmMode = 1 THEN 8 ELSE 0 END -- B3: allarme
|
|
+ CASE WHEN (ManualMode = 1 OR Mda = 1 OR Jog = 1) THEN 16 ELSE 0 END -- B4: manuale
|
|
AS ValInt
|
|
FROM syn_M2ERP_MACHINE_STATE
|
|
WHERE ID > @LastIdx AND ID <= @NewIdx
|
|
)
|
|
|
|
--vero insert valori
|
|
INSERT INTO MachineSignalLog(DtEve, IdxMacchina, ValInt, Valore)
|
|
SELECT *
|
|
,CONVERT(VARCHAR(1000),CONVERT(VARBINARY(2), ValInt), 2) AS Valore
|
|
--,CONVERT(VARBINARY(2), ValInt) AS Valore
|
|
FROM cteSigLog
|
|
|
|
-- aggiorno valore indice processato
|
|
MERGE SyncState AS tgt
|
|
USING (SELECT @NewIdx as LastIdx, 'ToMachineSigLog' AS TableName) as src (LastIdx, TableName)
|
|
ON (tgt.TableName = src.TableName)
|
|
WHEN MATCHED THEN
|
|
UPDATE SET LastIdx = src.LastIdx
|
|
,Note = 'UPDATED from ' + CAST(@LastIdx AS NVARCHAR(50))
|
|
,LastUpdate = GETDATE()
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (LastIdx, TableName, Note, LastUpdate)
|
|
VALUES (src.LastIdx, src.TableName, 'CREATED', GETDATE());
|
|
|
|
END
|
|
|
|
COMMIT tran |