@echo off
setlocal EnableDelayedExpansion

:loop
rem Step 1: Loop through each store code in st.txt
for /f "usebackq delims=" %%i in ("st.txt") do (
    set "storeCode=%%i"

    rem Step 2: Transform store code to pth12345
    set "transformedCode=pth!storeCode!"

    rem Step 3: Check if the pth12345 file exists
    if exist "!transformedCode!" (
        echo Found the file: !transformedCode!
        echo Skipping to next store code.
        goto :next
    )

    rem Step 4: Ping pth12345
    ping -n 1 "!transformedCode!" > nul 2>&1
    if errorlevel 1 (
        echo !transformedCode! is offline. Stopping for this store code.
        goto :next
    )

    rem Fetch yesterday's date using osql
    osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat

    rem Check if the date.dat file was created
    if not exist date.dat (
        echo Failed to retrieve date from the database. Exiting.
        goto :end
    )

    rem Read the date from the file and format it
    set /p date=<date.dat
    set day=%date:~0,10%   rem Get the full date string "YYYY-MM-DD"

    rem Extract components
    set yyyy=%day:~0,4%    rem Year
    set mm=%day:~5,2%      rem Month
    set dd=%day:~8,2%      rem Day

    rem Construct the file names
    set "brandFile=BRAND_!mm!!dd!.!storeCode!"
    set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!"
    set "productFile=PRODUCT_!mm!!dd!.!storeCode!"

    rem Step 5: Run the osql command and check result for the specific product pattern
    set "result="  rem Initialize result variable to empty

    for /f "delims=" %%j in ('osql -UTRANSF -PFSNART -S!transformedCode! -Q "exec xp_cmdshell 'dir d:\FTPRoot\transfIN\PRODUCT_!mm!!dd!.!storeCode!'"') do (
        echo %%j | findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" >nul
        if not errorlevel 1 (
            set "result=PRODUCT_!mm!!dd!.!storeCode!"
        )
    )

    rem Step 6: Check result
    if defined result (
        echo Found result: !result!
        echo Result indicates true for the presence of PRODUCT pattern for store code !storeCode!.
        goto :next
    )

    echo No relevant result found for store code !storeCode!. Continuing to download.

    rem Step 7: FTP download
    (
        echo open ftp.yourserver.com
        echo username
        echo password
        echo get !brandFile! D:\map_drive\wprx\!brandFile!
        echo get !departmentFile! D:\map_drive\wprx\!departmentFile!
        echo get !productFile! D:\map_drive\wprx\!productFile!
        echo bye
    ) > ftp_commands.txt

    ftp -n -s:ftp_commands.txt

    echo Downloaded files to D:\map_drive\wprx\

    rem Step 8: Copy files to the pth12345 folder
    (
        echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761'
        echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y'
        echo exec xp_cmdshell 'net use x: /d /y' 
    ) > transfer2store.sql
    
    osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql
    echo Copied files complete for store code !storeCode!

    :next
)

rem Compare st.txt and st_result.txt
fc /b st.txt st_result.txt > nul 2>&1
if errorlevel 1 (
    echo st.txt and st_result.txt are different. Restarting loop.
    timeout /t 60 /nobreak > nul
    goto loop
) else (
    echo st.txt and st_result.txt are the same. Stopping program.
)

:end
echo Script finished processing all store codes.
pause
endlocal

i got error
) was unexpected at this time.