429064af6b
Add the ability to skip processing of some of the Folders Added logic to save the name of the folder if it contains reserved characters (space, dash, slash and backslash)
153 lines
5.4 KiB
Transact-SQL
153 lines
5.4 KiB
Transact-SQL
/* ========================================================================
|
|
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
|
|
' |