Set dataManagerTables = '','esync_tags','esync_tagshistory','esync_tagpages','esync_stations';
//This block renames script tables from non generated section which conflict with the names of managed tables
For each name in $(dataManagerTables)
Let index = 0;
Let currentName = name;
Let tableNumber = TableNumber(name);
Let matches = 0;
Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
index = index + 1;
currentName = name & '-' & index;
tableNumber = TableNumber(currentName)
matches = Match('$(currentName)', $(dataManagerTables));
Loop
If index > 0 then
Rename Table '$(name)' to '$(currentName)';
EndIf;
Next;
Set dataManagerTables = ;
Unqualify *;
LIB CONNECT TO [MySQL_Enterprise_Edition_10.42.1.241];
[esync_tags]:
LOAD
[Id] AS [Id-TagId],
[StationId] AS [StationId-Id],
[eWONTagId],
[Name] AS [esync_tags.Name],
[Description] AS [esync_tags.Description],
[Val] AS [esync_tags.Val],
[AlarmStatus],
[AlarmType],
[AlarmHint],
[Quality] AS [esync_tags.Quality],
[Page],
[Type],
[ReadOnly];
SELECT Id,
StationId,
eWONTagId,
Name,
Description,
Val,
AlarmStatus,
AlarmType,
AlarmHint,
Quality,
Page,
Type,
ReadOnly
FROM esync.`esync_tags`;
[esync_tagshistory]:
FIRST 100 LOAD
[TagId] AS [Id-TagId],
[_date],
[msec],
[Val] AS [esync_tagshistory.Val],
[Quality] AS [esync_tagshistory.Quality];
SELECT TagId,
`_date`,
msec,
Val,
Quality
FROM esync.`esync_tagshistory`;
[data]:
LOAD
[TagId] AS [Id-TagId],
[_date],
[msec],
[Val] AS [esync_tagshistory.Val],
if([TagId] >= 70 and [TagId] <=85, chr([Val]),[Val]) as [Val]
FROM esync.`esync_tagshistory`;
NoConcatenate
Load [_date],concat([esync_tagshistory.Val]) as [esync_tagshistory.Val]
group by [_date];
Load * Resident [data]
where [Id-TagId] >= 70 and [Id-TagId] <=85
order by [_date],[Id-TagId] asc;
[esync_tagpages]:
LOAD
[StationId] AS [StationId-Id],
[PageId],
[PageName];
SELECT StationId,
PageId,
PageName
FROM esync.`esync_tagpages`;
[esync_stations]:
LOAD
[Id] AS [StationId-Id],
[Name] AS [esync_stations.Name],
[ModBusId],
[Description] AS [esync_stations.Description],
[HTTime],
[HTMsec],
[HTIntrasec],
[HATime],
[CrcConfig],
[EVTime],
[Appointment],
[IP],
[SN],
[Suspend],
[HostName],
[ConnectionType],
[Path],
[Info],
[Password],
[Folder],
[eWonLogin],
[eWonPassword],
[VpnIP],
[LanIP],
[LanSubnet],
[LastSynchro];
SELECT Id,
Name,
ModBusId,
Description,
HTTime,
HTMsec,
HTIntrasec,
HATime,
CrcConfig,
EVTime,
Appointment,
IP,
SN,
Suspend,
HostName,
ConnectionType,
Path,
Info,
Password,
Folder,
eWonLogin,
eWonPassword,
VpnIP,
LanIP,
LanSubnet,
LastSynchro
FROM esync.`esync_stations`;
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [_date] USING [autoCalendar] ;
