/***********************************************
-- Photo extract process for Destiny
-- By: Matt Penner
-- Val Verde Unified School District
-- mpenner@valverde.edu
--
-- Desc: Creates the text necessary to save in a batch file
-- that when ran retrieves all valid photos and generates the
-- folder structure and zip files necessary to update
-- photos in Destiny
--
-- Requres that the free 7-zip utility (www.7-zip.com)
-- is installed on the computer that the batch file is 
-- ran from.
***********************************************/

-- Best to switch to results to Text, remove any ---- lines from the results, save results as a file such as run.bat and execute in a command window

SET NOCOUNT ON;

-- Generate table of existing photos
create table #pics (fullname varchar(128), sc as left(right(fullname, len(fullname) - len('\\YourServerName\')), 3), shortname as substring(fullname, len('\\YourServerName\XXX\') + 1, len(fullname) - len('\\YourServerName\XXX\.jpg')))

-- We store our photos on our Aeries.Net server (used to be our ABI server).  We have a single shared directory per school site, which I believe is standard for Aeries.
-- We use 3 digit school codes in our Aeries.  You will need to add a single line below for each school site you want to pull from. Replace the XXX with your school code
insert into #pics (fullname) exec master..xp_cmdshell 'dir \\YourServerName\XXX\*.jpg /b /s'
delete #pics where fullname = 'File Not Found' or fullname is NULL

-- Batch file command
print 'ECHO OFF'

-- Generate statements to get photos for Destiny -- Could do this all from the script if we executed the commands rather than simply saved them to a batch file.  Not sure which would be faster.
print 'ECHO Selecting photos...'
-- ***The following requires 7-zip, a free fast archiving utility.  If 7-zip is not avaialble use the queries farther below
-- Generate list of files to zip
select 'echo \\YourServerName\' + cast(stu.sc as varchar) + '\' + bm + '.JPG >> c:\pics\' + cast(stu.sc as varchar) + 'PicList.txt'
from stu
join #pics on (stu.sc = #pics.sc and stu.bm = #pics.shortname)
where del = '' and tg = '' and bm <> ''
order by stu.sc, bm


-- Add idlink.txt to each of the lists of files to zip
select distinct 'echo c:\Pics\idlink.txt >> c:\pics\' + cast(stu.sc as varchar) + 'PicList.txt'
from stu
join #pics on (stu.sc = #pics.sc and stu.bm = #pics.shortname)
where del = '' and tg = '' and bm <> ''

-- Generate idlink.txt files
print 'ECHO Generating idlink.txt files...'
select 'echo "' + cast(id as varchar) + '","' + bm + '.jpg" >> c:\pics\' + cast(stu.sc as varchar) + 'idlink.txt' 
from stu 
join #pics on (stu.sc = #pics.sc and stu.bm = #pics.shortname)
where del = '' and tg = '' and bm <> ''
order by stu.sc, bm

-- Use 7-Zip to zip the files in the lists generated above (much faster than zipping individually)
-- Need to use a cursor because:
-- The link file must be named idlink.txt, not 120idlink.txt etc.  therefore each idlink.txt must be saved in a diff folder
-- If we use 7-zip to add 120\idlink.txt to 120\120pics.zip then the zip is given a 120 folder with idlink.txt inside
-- Therefore we have to rename the 120idlink.txt to idlink.txt, add it to the archive and delete it or move it depending on if blue bear needs it

print 'ECHO Adding photos to zip files...'

-- Declare variables and a cursor for the schools used
DECLARE @sch AS char(3)
DECLARE curSchools CURSOR FOR
select distinct stu.sc
from stu 
join #pics on (stu.sc = #pics.sc and stu.bm = #pics.shortname)
where del = '' and tg = '' and bm <> ''

OPEN curSchools

-- Grab the first procedure from the list
FETCH NEXT FROM curSchools INTO @sch
WHILE (@@FETCH_STATUS = 0)
BEGIN
	-- Execute the procedure giving it its ProcID and the JobID
	print 'ECHO Adding school ' + @sch + '...'
	print 'rename c:\pics\' + @sch + 'idlink.txt idlink.txt'
	-- The final switch ">nul 2>&1" means to direct the stdout and errout to nul, or simply don't show any output
	print '"c:\Program Files\7-Zip\7z.exe" a -tzip c:\pics\' + @sch + 'Pics.zip -i@c:\pics\' + @sch + 'PicList.txt >nul 2>&1'

	print 'del c:\pics\idlink.txt'

	-- Grab the next school from the list
	FETCH NEXT FROM curSchools INTO @sch
END

-- Clean up
CLOSE curSchools
DEALLOCATE curSchools

-- Delete temp PicList.txt files
select 'del c:\pics\???PicList.txt'

--Clean up
drop table #pics

print 'ECHO Photo script complete'

-- Return execution to the calling batch file
print 'EXIT /B'