1 files changed, 33 insertions, 0 deletions
diff --git a/configs/thetvdbview.sql b/configs/thetvdbview.sql
new file mode 100644
index 0000000..d6b06af
--- /dev/null
+++ b/configs/thetvdbview.sql
@@ -0,0 +1,33 @@
+CREATE VIEW thetvdbview as
+select
+ ev.eventid,
+ case when ep.compname = 'TATORT' then concat('Tatort',replace(substring(ep.extracol1,11),' und ',' & ')) else ev.title end title,
+ ev.scrsp,
+ case when ep.compname = 'TATORT' then Null else ep.season end season,
+ case when ep.compname = 'TATORT' then Null else ep.part end part,
+ case when ep.compname = 'TATORT' then Null else ep.number end number,
+ ep.partname shorttext
+from
+ events ev, episodes ep
+where
+ ev.episodecompname = ep.compname and
+ ev.episodecomppartname = ep.comppartname and
+ ev.episodelang = ep.lang and
+ ev.scrsp is null and
+ updflg in('A','T','C','P')
+union
+select
+ eventid,
+ title,
+ scrsp,
+ case when substring(shorttext,1,1)='S' and substring(shorttext,2,1) REGEXP ('[0-9]') then TRIM(LEADING '0' from replace(SUBSTRING_INDEX(shorttext, 'E', 1),'S','')) end  season,
+ case when substring(shorttext,1,1)='S' and substring(shorttext,2,1) REGEXP ('[0-9]') then TRIM(LEADING '0' from SUBSTRING_INDEX(shorttext, 'E', -1)) end  part,
+ null number,
+ case when substring(shorttext,1,1)='S' and substring(shorttext,2,1) REGEXP ('[0-9]') then Null else shorttext end shorttext
+from
+ events
+where
+ episodecompname is Null
+ and scrsp is null
+ and updflg in('A','T','C','P')
+ and category = 'Serie';