@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
REM ----------------------------------------------------------------------------------------------
REM Script: SOM_Dashboard_query.cmd
REM Created: 2011/11/01
REM Author: Brian Fergusson
REM Version: 1
REM Description:
REM This is the main script for extracting data for the Testing Statistics SOM Dashboard.
REM The result of each dashboard query is outputted to a csv file.
REM The queries are run for the previous month of data (Variable, date_code (set below), must be set to 1 for this mode).
REM ----------------------------------------------------------------------------------------------

SET DIR_INPUT=.\queries
SET DIR_OUTPUT=.\output
REM SET DIR_OUTPUT=\\auperanas01\Data\Transit\temp


REM Disable debug mode
SET DEBUG=N
SET SERVER_TD=AUPERAMTD01
SET SERVER_CQ=AUPERASQL05
SET SERVER_JR=AUPERASQL05

REM Versions of each query (for debugging)
SET VER_TD=5
SET VER_JR=3
SET VER_CQ=8

PUSHD .

ECHO.
ECHO Test Director Server: %SERVER_TD%
ECHO Clear Quest Server:   %SERVER_CQ%
ECHO Jira Server:          %SERVER_JR%

REM  get month.  This is for the csv filename
SET month_qry=SQLCMD -S%SERVER_TD% -E -Q "SET NOCOUNT ON; select rtrim(year(DATEADD(mm,-1,CURRENT_TIMESTAMP))) + '_' + right('0' + rtrim(month(DATEADD(mm,-1,CURRENT_TIMESTAMP))),2)" -h-1 -s"," -W
FOR /f "usebackq tokens=1,2 delims=_" %%A IN (`%month_qry%`) DO (
  SET year=%%A
  SET month=%%B
)

ECHO Query Month:          %year%/%month%

ECHO.
ECHO Running SOM Dashboard queries for Month: %year%/%month%
ECHO.

REM  query previous month
REM date_code is accessed in the sql script
SET date_code=1
CALL :query_to_csv %SERVER_TD% "%DIR_INPUT%\SOM_Dashboard_query_TD.sql"    "%DIR_OUTPUT%\SOM_Dashboard_%year%%month%_TD.csv"    "Test Director"    "-E"
CALL :query_to_csv %SERVER_CQ% "%DIR_INPUT%\SOM_Dashboard_query_CQ_JR.sql" "%DIR_OUTPUT%\SOM_Dashboard_%year%%month%_CQ_JR.csv" "Clear Quest/Jira" "-E"

IF %DEBUG% == Y (
  REM  This block of code is used for debugging purposes.
  REM  It is not required in a live system, ie SET DEBUG=N

  REM  Query data since 2009
  SET date_code=2
  CALL :query_to_csv %SERVER_TD% "%DIR_INPUT%\SOM_Dashboard_query_TD.sql"    "%DIR_OUTPUT%\SOM_Dashboard_Test_TD_TEST_v%VER_TD%.csv"    "Test Director"    "-E"
  CALL :query_to_csv %SERVER_CQ% "%DIR_INPUT%\SOM_Dashboard_query_CQ_JR.sql" "%DIR_OUTPUT%\SOM_Dashboard_Test_CQ_DEVI_v%VER_CQ_JR%.csv" "Clear Quest/Jira" "-E"

  REM  generate row data for issue investigation purposes
  REM  query data for a specific debug date range, See Dashboard_query_init.sql
  SET date_code=3
  CALL :query_to_csv  %SERVER_TD% "%DIR_INPUT%\SOM_Dashboard_query_TD_TEST_rows_aug_to_oct.sql" "%DIR_OUTPUT%\SOM_Dashboard_Test_TD_TEST_rows_aug_to_oct_v%VER_TD%.csv" "Test Director" "-E"
  CALL :query_to_csv  %SERVER_CQ% "%DIR_INPUT%\SOM_Dashboard_query_CQ_DEVI_rows_aug_to_oct.sql" "%DIR_OUTPUT%\SOM_Dashboard_Test_CQ_DEVI_rows_aug_to_oct_v%VER_CQ%.csv" "Clear Quest"   "-E"
  CALL :query_to_csv  %SERVER_JR% "%DIR_INPUT%\SOM_Dashboard_query_JR_DEVI_rows_aug_to_oct.sql" "%DIR_OUTPUT%\SOM_Dashboard_Test_JR_DEVI_rows_aug_to_oct_v%VER_JR%.csv" "Jira"          "-E"
)


ECHO.
ECHO Completed SOM Dashboard queries for Month: %year%/%month%
ECHO.
goto end

REM ----------------------------------------------------------------------------------------------
: query_to_csv
REM  Runs SQLCMD to run the specified query and output the result to the specified output file.
REM 
REM 

REM Note: %~1: removes surrounding quotes on parameter %1
SET server=%1
SET inpfile=%~2
SET outfile=%~3
SET datatitle=%~4
SET login=%~5

ECHO   Querying %datatitle% databases...
ECHO   Running %inpfile%
ECHO   Outputting to:%outfile%

REM outsputs to csv using a comma separater (-s",") and includes a header and no trailing spaces (-W).  Note: to remove headers use (-h-1)
REM Run sqlcmd and remove dashed line after the column header

SQLCMD -S%server% %login% -i "%inpfile%" -o "%outfile%" -s"," -W
REM   SQLCMD -S%server% %login% -i "%inpfile%" -s"," -W | find /v "-----" > "%outfile%"

ECHO   Completed querying %datatitle% databases.
ECHO.
GOTO endp
REM ----------------------------------------------------------------------------------------------


:end
POPD
REM pause
:endp