ECHO OFF
REM MediaFilesStatus.bat
REM Tom Holden 13 Mar 2011
REM rev 2021-07-04 Media folder parameterized as %2; support double-quotes around calling parameters
REM Lists files under a folder and sub-folders and checks 
REM and flags if they are used by a RM4-7 database; does not support RM8.
REM Command line: MediaFilesStatus RootsMagicFile.rmgc MediaFolder
REM - full paths or relative to the 'Start in' location, double quoted if space character is in path
REM
REM List files under the media path and store to a file.
DIR %2 /B /S /ON > MediaFilesList.txt
REM
REM Erase old sql file and database file
ERASE MediaFilesStatus.sql
ERASE MediaFilesStatus.db3
REM Build a SQL query and save to a file
ECHO CREATE TABLE FILES(file); > MediaFilesStatus.sql
ECHO .import MediaFilesList.txt FILES >> MediaFilesStatus.sql
ECHO ATTACH DATABASE REPLACE('%1', '^"', '') AS RM ; >> MediaFilesStatus.sql 
ECHO .mode tabs >> MediaFilesStatus.sql
ECHO .output MediaFilesStatus.txt >> MediaFilesStatus.sql
ECHO SELECT LIKE(FILE,MediaPath^|^|MediaFile) AS USED, FILE FROM FILES LEFT JOIN MultimediaTable ON (FILE LIKE MediaPath^|^|MediaFile) >> MediaFilesStatus.sql
ECHO  WHERE FILE NOT LIKE '%%.THU' AND FILE LIKE '%%.___%%' ORDER BY FILE; >> MediaFilesStatus.sql
REM SQL file building finished
REM
REM Execute SQL to generate report file
sqlite3.exe MediaFilesStatus.db3 < MediaFilesStatus.sql
REM
REM Open report file with Excel
excel.lnk MediaFilesStatus.txt
REM END