/* ========================================================================
   Copyright 2018 - Volian Enterprises, Inc. All rights reserved.          
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
   ------------------------------------------------------------------------
   This is a SQL script which can be used to create a CMD Batch file to
   perform automated testing on all of the folders within the current
   database and server.
   ------------------------------- NOTE -----------------------------------
   There are no parameters in this script.  The following values are 
   retrieved from the databse which is currently open
   DB_NAME() - Gets the current Database Name
   @@Servername - Gets the current SQL Servername
   These are passed as parameters to PROMS along with some other paramters 
   which specifies how the procedure should be printed.
   You can add REM before any folders that should not print
   ****************************** CAUTION *********************************
   If you are comparing against code created before 8/1/2018 you will need
   to specify /NW to exclude the Word output which had not previously been
   included.
   =======================================================================*/
PRINT 'REM Blank Line Before Batch Commands
@ECHO OFF
ECHO Automated testing assumes the following:
ECHO C:\PDFS will be used to store the results of the Automated Print Testing
ECHO C:\PROMS will be used to store the executable for the Automated Print Testing
ECHO ---
ECHO The following Parameter Needs to be set:
set PROMSEXE=1.5.1805.910.Production
ECHO PROMSEXE [%PROMSEXE%] - Subfolder of the PROMS Executable
ECHO ---
ECHO Automated Testing will:
ECHO   Delete PDFs
ECHO   Update Formats From the folder promsformats under the PROMSEXE folder
set dbName=' + DB_NAME() + '
set /p GroupComment=Specify Output[%PROMSEXE%_%dbName%]: || set GroupComment=%PROMSEXE%_%dbName%
ECHO AAA >%TEMP%\VEPROMS\NewFile.txt
for %%a in (%TEMP%\VEPROMS\NewFile.txt) do Set fdt=%%~ta
for /F "tokens=1,2,3,4,5,6 delims=/: " %%i in (''echo %fdt%'') do call:sc_FixTime %%k %%i %%j %%n %%l %%m
set fdt=%fdt::=%_%GroupComment%
set ServerName=' + @@Servername + '
md %TEMP%\VEPROMS\%fdt%
md C:\PDFs\%fdt%
echo %date% %time% Start ' + @@Servername + ' ' + DB_NAME() + '%GroupComment% >>C:\pdfs\%fdt%\Times.txt
del %TEMP%\VEPROMS\NewFile.txt
Rem Cleanup output folder before processing
del %temp%\VEPROMS\*.txt
del %temp%\VEPROMS\*.pdf
Rem Delete PDFs
sqlcmd -E -S%servername% -d%DBNAME% -b -Q "delete from pdfs"
if ERRORLEVEL 1 goto :oops
Rem Update Formats
call :sc_setformats unnecessary unnecessary ' + DB_NAME() + ' unnecessary %PROMSEXE%
Rem Perform PROMS printing for each Folder ----------------------------------------
GoTo :SkipStart
Rem Skip some DocVersions (Working Drafts)
:SkipStart
'
 
declare @CommandLine nvarchar(255)
declare Commands cursor for
select 'Call :sc_PrintPDF %TEMP%\VEPROMS ' + 
	cast(VersionID as varchar(5))+ ' ' + db_name() + ' ' + 
	Replace(Replace(Replace(Replace(ff.name,'/','_'),'\','_'),' ','_'),'-','_') + ' %PROMSEXE%'
	from docversions dv 
	join folders ff on dv.folderID = ff.FolderID
	order by versionID
OPEN Commands
FETCH NEXT FROM Commands INTO @CommandLine
WHILE @@FETCH_STATUS = 0
BEGIN
  print @CommandLine
	FETCH NEXT FROM Commands INTO @CommandLine
END
CLOSE Commands
DEALLOCATE Commands
PRINT 'GoTo :SkipEnd
Rem Skip Some DocVersions (Working Drafts)
:SkipEnd
Call :sc_MoveErrorLog ' + db_name() +'
Rem Record direcctly to C:\PDFs\%fdt%
echo %date% %time% Done >>C:\PDFS\%fdt%\Times.txt
Move %TEMP%\VEPROMS\%fdt%\*.* C:\PDFs\%fdt%
rd %TEMP%\VEPROMS\%fdt%

set GroupComment=
set fdt=
rem wait 20
goto:eof

:sc_setformats
echo loading formats from  c:\proms\%5\promsformats
"C:\PROMS\%5\VEPROMS.exe" /UF=c:\proms\%5\promsformats /P=  /DB=%ServerName%:%3 
timeout /T 10
goto:eof

:sc_PrintPDF
echo %date% %time% Start %2 (%4) >>C:\PDFS\%fdt%\Times.txt
echo Creating PDFs for DocVersion %2 (%4) in %3 
rem del %userprofile%\Documents\VEPROMS\%3*.txt
"C:\PROMS\%5\VEPROMS.exe" /P=%2 /DB=%ServerName%:%3 /NT /NC
rem -----------------------------------------------
rem Add /NW to exclude Word Output to the Meta File
rem -----------------------------------------------
echo %date% %time% Done %2 (%4) >>C:\PDFS\%fdt%\Times.txt
call :sc_AddText %3 %2 %4 %5
C:
cd %1
if not exist DebugP*.txt goto:sc_PrintPDFNoFiles
set fdt1=%fdt%\%3\%2_%4
md c:\PDFs\%fdt1%
del DOC_*.pdf > nul
move *.pdf c:\PDFs\%fdt1% > nul
move *.txt c:\PDFs\%fdt1% > nul
for /F %%c in (''cd'') do echo Files Moved to %%c\%fdt1%
set fdt1=
goto:eof
:sc_PrintPDFNoFiles
for /F %%d in (''cd'') do echo No Files to Move in %%d
goto:eof

:sc_FixTime
set fdt=%1%2%3_%5%6
if "%5%4"=="12PM" goto:sc_FixTimeDone
if "%5%4"=="12AM" set fdt=%1%2%3_00%6
if "%4"=="AM" goto:sc_FixTimeDone
if "%5"=="01" set fdt=%1%2%3_13%6
if "%5"=="02" set fdt=%1%2%3_14%6
if "%5"=="03" set fdt=%1%2%3_15%6
if "%5"=="04" set fdt=%1%2%3_16%6
if "%5"=="05" set fdt=%1%2%3_17%6
if "%5"=="06" set fdt=%1%2%3_18%6
if "%5"=="07" set fdt=%1%2%3_19%6
if "%5"=="08" set fdt=%1%2%3_20%6
if "%5"=="09" set fdt=%1%2%3_21%6
if "%5"=="10" set fdt=%1%2%3_22%6
if "%5"=="11" set fdt=%1%2%3_23%6
:sc_FixTimeDone
echo %2/%3/%1 %5:%6 %4 %fdt% 
goto:eof

:sc_MoveErrorLog
move %userprofile%\Documents\VEPROMS\%1*.txt C:\PDFS\%fdt%\%1 > nul
goto:eof

:sc_AddText
for %%a in ("%userprofile%\Documents\VEPROMS\%1 *.txt") do Set fil=%%a
echo "=========== %4 Printing Complete %2 %3 ===========" >>"%fil%"
set fil=
goto :eof

oops:
TIMEOUT /T 60 
'