USE [My Movies];

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1 -- Enable
GO
EXEC sp_configure 'xp_cmdshell', '1'  -- Enable
GO
RECONFIGURE
GO

DECLARE @counter int;
DECLARE @Path VARCHAR(2000);
DECLARE @Pfad VARCHAR(2000);
DECLARE @MD VARCHAR(2000);
DECLARE @Datei VARCHAR(2000);
DECLARE @Txt  VARCHAR(2000);
DECLARE @Filme VARCHAR(255);
DECLARE @Serien VARCHAR(255);
SET @Path = 'C:\Users\Public\Videos';
SET @Filme = 'Movies\';
SET @Serien = 'Series\';

DECLARE tabcurs CURSOR FOR 
	SELECT	  REPLACE(REPLACE(title.nvcLocalTitle, ':', ''), '"', '') AS OrigTitle
			, CASE WHEN ISNULL(ptitle.nvcTitle, '' ) = '' THEN title.nvcLocalTitle ELSE ptitle.nvcTitle END AS OwnTitle
			, title.intProductionYear
			, REPLACE(REPLACE(types.nvcName, '-', ''), ' ', '') AS Typ
			, CASE WHEN title.bit3D = 1 THEN '.3D' ELSE '' END AS KZ3D
			, CASE WHEN ptitle.intCollectionNumber = -1 THEN 'unbekannt' ELSE CAST(ptitle.intCollectionNumber AS varchar) END AS CNum
			, ptitle.nvcLocation AS Loc 
			, bitWorksAsTVSeries AS Serie
			, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(title.nvcLocalTitle, '?', ''), '/', '_'), ':', ' -'), '"', ''), '  ', ' ') AS dateiname
	FROM tblTitles AS title
	LEFT JOIN tblTitlePersonal AS ptitle ON ptitle.intTitle = title.intId
	LEFT JOIN tblTypes AS types ON types.intID = title.intType
	LEFT JOIN tblTitleSeries AS tSeries ON tSeries.intTitle = title.intId
	WHERE bitWorksAsTVSeries = 0
	AND ISNULL(tSeries.intTitleSerie, '') = ''
	ORDER BY OrigTitle;

OPEN tabcurs
DECLARE @title NVARCHAR(255);
DECLARE @owntitle NVARCHAR(255);
DECLARE @jahr int;
DECLARE @typ NVARCHAR(255);
DECLARE @3D NVARCHAR(255);
DECLARE @CNum NVARCHAR(255);
DECLARE @Loc NVARCHAR(255);
DECLARE @Serie bit;
DECLARE @dateiname NVARCHAR(255);

FETCH NEXT FROM tabcurs INTO @title, @owntitle, @jahr, @typ, @3D, @CNum, @Loc, @Serie, @dateiname
WHILE @@fetch_status = 0
BEGIN
	SET @Datei = @Filme +  @dateiname + ' (' + CAST(@jahr AS varchar) + ').' + LOWER(@typ) + @3D + '.disc';
    SET @Txt = '<discstub>
  <message>Befindet sich hier: ' + @CNum + '</message>
</discstub>';
	EXECUTE spWriteStringToFile @Txt, @Path, @Datei
	FETCH NEXT FROM tabcurs INTO @title, @owntitle, @jahr, @typ, @3D, @CNum, @Loc, @Serie, @dateiname
END
CLOSE tabcurs;
DEALLOCATE tabcurs;



DECLARE tabcurs CURSOR FOR 
	SELECT	  sls.nvcName AS Serie
			, intSeasonNumber AS Staffel
			, MIN(EpsGlobal.intEpisodeNumber) AS Episode1
			, MAX(EpsGlobal.intEpisodeNumber) AS Episode2
			, REPLACE(REPLACE(title.nvcLocalTitle, ':', ''), '"', '') AS OrigTitle
			, REPLACE(CASE WHEN ISNULL(ptitle.nvcTitle, '' ) = '' THEN title.nvcLocalTitle ELSE ptitle.nvcTitle END, ':', '') AS OwnTitle
			, disc.nvcLabel AS DiskLabel
			, LOWER(REPLACE(REPLACE(types.nvcName, '-', ''), ' ', '')) AS Typ
			, CASE WHEN title.bit3D = 1 THEN '.3D' ELSE '' END AS KZ3D
			, CASE WHEN ptitle.intCollectionNumber = -1 THEN 'unbekannt' ELSE CAST(ptitle.intCollectionNumber AS varchar) END AS CNum
			, ptitle.nvcLocation AS Loc 
			, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ols.nvcName, '?', ''), '/', '_'), ':', ' -'), '"', ''), '  ', ' ') AS dateiname
	FROM tblTitles AS title
	LEFT JOIN tblTitlePersonal AS ptitle ON ptitle.intTitle = title.intId
	LEFT JOIN tblTypes AS types ON types.intID = title.intType
	LEFT JOIN tblDiscs disc ON disc.intTitle = title.intId
	JOIN	  tblDiscTitles DiscTitles ON DiscTitles.intDisc = disc.intId
	JOIN	  tblEpisodeGlobal EpsGlobal ON EpsGlobal.guid = DiscTitles.guidEpisode
	JOIN	  tblEpisodePersonal tep ON tep.guidEpisode = DiscTitles.guidEpisode
	JOIN	  tblSeriesLanguageSpecific sls ON sls.guidSeries = EpsGlobal.guidSerie
	LEFT JOIN tblSeriesLanguageSpecific ols ON ols.guidSeries = EpsGlobal.guidSerie
	LEFT JOIN tblLanguages lng ON lng.guid = sls.guidLanguage
	LEFT JOIN tblLanguages olng ON olng.guid = ols.guidLanguage
	WHERE	bitWorksAsTVSeries = 1
	AND		lng.nvcCode = 'de'
	AND		olng.nvcCode = 'en'
	GROUP BY sls.nvcName, intSeasonNumber, disc.intId, title.nvcLocalTitle, ptitle.nvcTitle, disc.nvcLabel, types.nvcName, title.bit3D, ptitle.intCollectionNumber, ptitle.nvcLocation, ols.nvcName
	ORDER BY sls.nvcName, intSeasonNumber, disc.intId;

OPEN tabcurs
DECLARE @disklabel NVARCHAR(255);
DECLARE @Serienname NVARCHAR(255);
DECLARE @Staffel NVARCHAR(100);
DECLARE @Episode1 int;
DECLARE @Episode2 int;
DECLARE @Episoden NVARCHAR(255);

FETCH NEXT FROM tabcurs INTO @Serienname, @Staffel, @Episode1, @Episode2, @title, @owntitle, @disklabel, @typ, @3D, @CNum, @Loc, @dateiname
WHILE @@fetch_status = 0
BEGIN
	SET @Episoden = '';

	SET @counter = @Episode1;
	WHILE @counter <= @Episode2
	BEGIN
		SET @Episoden = @Episoden + 'E' + RIGHT(REPLICATE('0', 2) + CAST(@counter AS VARCHAR(2)), 2);
		SET @counter = @counter + 1;
	END

	SET @Pfad = @Serien + REPLACE(@dateiname, ' ', '.') + '\Season ' + RIGHT(REPLICATE('0', 2) + CAST(@Staffel AS VARCHAR(2)), 2) + '\';
	SET @MD = 'MD "' + @Path + '\' + @Pfad + '"'
	EXEC master.dbo.xp_cmdshell @MD;
	SET @Datei = @Pfad + REPLACE(@dateiname, ' ', '.') + '_S' + RIGHT(REPLICATE('0', 2) + CAST(@Staffel AS VARCHAR(2)), 2) + @Episoden + '.' + @typ + '.disc';
    SET @Txt = '<discstub>
  <message>Befindet sich hier: ' + @CNum + ', ' + @disklabel + '</message>
</discstub>';
	EXECUTE spWriteStringToFile @Txt, @Path, @Datei
	FETCH NEXT FROM tabcurs INTO @Serienname, @Staffel, @Episode1, @Episode2, @title, @owntitle, @disklabel, @typ, @3D, @CNum, @Loc, @dateiname
END
CLOSE tabcurs;
DEALLOCATE tabcurs;



EXEC sp_configure 'Ole Automation Procedures', 0 -- Disable
GO
EXEC sp_configure 'xp_cmdshell', '0'  -- Disable
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO