''''''''''Bat file
CD C:\prog\REP00_AUTORUN\REP016_CH_SBL_TRM01_HIST\
rem ОПРЕДЕЛЯЕМ LAST_CLOSE_DT
copy nul btq\REP016_CH_SBL_TRM01_HIST_LCDT.btq
CScript.exe vbs\REP016_CH_SBL_TRM01_HIST_LCDT.vbs
BTEQ < btq\REP016_CH_SBL_TRM01_HIST_LCDT.btq > log\REP016_CH_SBL_TRM01_HIST_LCDT.log
rem ВЫГРУЗКА ПЕРЕМЕННЫХ ДЛЯ РАСЧЕТА
copy nul btq\REP016_CH_SBL_TRM01_HIST_OPT.btq
copy nul REP016_CH_SBL_TRM01_HIST_OPT.exp
CScript.exe vbs\REP016_CH_SBL_TRM01_HIST_OPT.vbs
BTEQ < btq\REP016_CH_SBL_TRM01_HIST_OPT.btq >> log\REP016_CH_SBL_TRM01_HIST.log
rem ОПРЕДЕЛЯЕМ LAST_DT И ПРОЧИЕ ДАТЫ
copy nul btq\REP016_CH_SBL_TRM01_HIST_LDT.btq
CScript.exe vbs\REP016_CH_SBL_TRM01_HIST_LDT.vbs
BTEQ < btq\REP016_CH_SBL_TRM01_HIST_LDT.btq > log\REP016_CH_SBL_TRM01_HIST_LDT.log
rem ВЫГРУЗКА ПЕРЕМЕННЫХ ДЛЯ РАСЧЕТА
copy nul btq\REP016_CH_SBL_TRM01_HIST_OPT.btq
copy nul REP016_CH_SBL_TRM01_HIST_OPT.exp
CScript.exe vbs\REP016_CH_SBL_TRM01_HIST_OPT.vbs
BTEQ < btq\REP016_CH_SBL_TRM01_HIST_OPT.btq >> log\REP016_CH_SBL_TRM01_HIST.log
rem ВЫГРУЖАЕМ ФАКТ И ЗАВЕРШАЕМ ВЫГРУЗКУ
copy nul btq\REP016_CH_SBL_TRM01_HIST_FACT.btq
CScript.exe vbs\REP016_CH_SBL_TRM01_HIST_FACT.vbs
BTEQ < btq\REP016_CH_SBL_TRM01_HIST_FACT.btq > log\REP016_CH_SBL_TRM01_HIST_FACT.log
rem ОТПРАВКА ПИСЬМА
copy nul MSG_BODY.exp
copy nul btq\REP016_CH_SBL_TRM01_HIST_END.btq
CScript.exe vbs\REP016_CH_SBL_TRM01_HIST_END.vbs
BTEQ < btq\REP016_CH_SBL_TRM01_HIST_END.btq > log\REP016_CH_SBL_TRM01_HIST_END.log
CScript.exe vbs\SENDMAIL.vbs
''''''''''''''REP016_CH_SBL_TRM01_HIST_LCDT.vbs
Dim fso, file, bteq, opt, ts1, ts2, f_sql, s, m_opt
dim i
dim f_block
dim opt_arr (9, 1)
dim rep_nm, rep_step_num, rep_dt, rep_run_flg, step_result, rep_step_desc
dim log_flg
dim l_kuskovnv, l_upravlenka
dim work_dir, main_dir
rep_nm = "REP016_CH_SBL_TRM01_HIST"
sql_nm = "REP016_CH_SBL_TRM01_HIST_LCDT"
rep_run_flg = "1"
step_result = "null"
'Флаг вставки скриптов логирования (не нужны на этапе задания переменных для процедуры)
log_flg = "0"
SET FSO = WScript.CreateObject("Scripting.FileSystemObject")
Const ForReading = 1, ForWriting = 2, TristateUseDefault = -2
'файл общих настроек
main_dir = FSO.GetAbsolutePathName("..")
work_dir = FSO.GetAbsolutePathName(".")
f_m_opt = main_dir & "\OPTIONS.OPT"
SET m_opt = FSO.GetFile(f_m_opt)
'Общие настройки
SET ts2 = m_opt.OpenAsTextStream(ForReading, TristateUseDefault)
DO WHILE NOT ts2.AtEndOfStream
s = ts2.ReadLine
IF mid(s, 1, INSTR(s, "=")) = "KUSKOVNV=" THEN
l_kuskovnv = mid(s, INSTR(s, "=") + 1, LEN(s))
END IF
IF mid(s, 1, INSTR(s, "=")) = "UPRAVLENKA=" THEN
l_upravlenka = mid(s, INSTR(s, "=") + 1, LEN(s))
END IF
IF mid(s, 1, INSTR(s, "=")) = "CURRLOGIN=" THEN
l_curr = mid(s, INSTR(s, "=") + 1, LEN(s))
END IF
LOOP
ts2.CLOSE
'открываем нужный SQL
f_sql = work_dir & "\sql\" & sql_nm & ".sql"
SET file = FSO.GetFile(f_sql)
'итоговый файл
b_sql = work_dir & "\btq\" & sql_nm & ".btq"
SET bteq = FSO.GetFile(b_sql)
'файл переменных
f_opt = work_dir & "\" & rep_nm & "_OPT.EXP"
SET opt = FSO.GetFile(f_opt)
i = -1
SET ts2 = opt.OpenAsTextStream(ForReading, TristateUseDefault)
DO WHILE NOT ts2.AtEndOfStream
s = ts2.ReadLine
'пропускаем 1 строку с заголовком
IF i > -1 THEN
opt_arr(i, 0) = mid(s, 1, INSTR(s, ";")-1)
opt_arr(i, 1) = mid(s, INSTR(s, ";") + 1, len(s))
END IF
i = i + 1
LOOP
ts2.CLOSE
s = ""
SET ts1 = bteq.OpenAsTextStream(ForWriting, -1)
'ts1.WriteLine ".SET SESSION TRANSACTION ANSI;"
IF l_curr = "KUSKOVNV" THEN
ts1.WriteLine l_kuskovnv
ELSEIF l_curr = "UPRAVLENKA" THEN
ts1.WriteLine l_upravlenka
ELSE
ts1.WriteLine l_kuskovnv
END IF
rep_dt = FindDtRep(s, i, opt_arr)
IF log_flg = "1" THEN
'если была ошибка на предыдущем этапе, то выходим
ts1.WriteLine "SELECT * FROM prod_oyo_sbx.Z00_REPORTS_RESULT WHERE REP_NM = '" & rep_nm & "' AND RUN_FLG <> '0'"
ts1.WriteLine ";"
ts1.WriteLine ".IF ACTIVITYCOUNT <> 0 THEN .QUIT 100"
ts1.WriteLine "DELETE FROM prod_oyo_sbx.Z00_REPORTS_LOGS"
ts1.WriteLine "WHERE rep_nm = '" & rep_nm & "' AND rep_dt = '" & rep_dt & "'"
ts1.WriteLine ";"
ts1.WriteLine ".IF ERRORCODE > 0 THEN .GOTO lbl_ERROR"
ts1.WriteLine "--"
ts1.WriteLine "INSERT INTO prod_oyo_sbx.Z00_REPORTS_LOGS VALUES("
ts1.WriteLine "'" & rep_nm & "', '10', '/*------------------------------------------------*/', '" & rep_dt & "', CURRENT_TIMESTAMP(0), CURRENT_TIMESTAMP(0), 0, 'null')"
ts1.WriteLine ";"
ts1.WriteLine ".IF ERRORCODE > 0 THEN .GOTO lbl_ERROR"
ts1.WriteLine "--"
END IF
SET ts2 = File.OpenAsTextStream(ForReading, TristateUseDefault)
DO WHILE NOT ts2.AtEndOfStream
s = ts2.ReadLine
'ищем первый блок
IF mid(s, 1, 3) = "/*#" THEN
f_block = 1
rep_step_num = mid(s, INSTR(s, "#") + 1, INSTR(s, ":") - 4) 'условие по формату - начинается всегда с /*#STEP
rep_step_num = Replace(rep_step_num, "STEP", "")
rep_step_desc = mid(s, INSTR(s, ":") + 1, INSTR(s, "*/") - 1)
rep_dt = FindDtRep(s, i, opt_arr)
'записываем лог
IF log_flg = "1" THEN
ts1.WriteLine "INSERT INTO prod_oyo_sbx.Z00_REPORTS_LOGS VALUES("
ts1.WriteLine "'" & rep_nm & "', '" & rep_step_num & "', '" & rep_step_desc & "', '" & rep_dt & "', CURRENT_TIMESTAMP(0), CURRENT_TIMESTAMP(0), " & rep_run_flg & ", '" & step_result & "')"
ts1.WriteLine ";"
ts1.WriteLine ".IF ERRORCODE > 0 THEN .GOTO lbl_ERROR"
ts1.WriteLine "--"
END IF
END IF
IF (f_block = 1) THEN
s = OptReplace(s, i, opt_arr)
ts1.WriteLine s
END IF
'проверям ";" - в самом конце
IF s = ";" THEN
f_block = 0
'добавляем обработку ошибок
ts1.WriteLine ".IF ERRORCODE > 0 THEN .GOTO lbl_ERROR"
'записываем лог
IF log_flg = "1" THEN
ts1.WriteLine "UPDATE prod_oyo_sbx.Z00_REPORTS_LOGS"
ts1.WriteLine "SET STEP_END_DTTM = CURRENT_TIMESTAMP(0), rep_run_flg = 0"
ts1.WriteLine "WHERE rep_nm = '" & rep_nm & "' AND rep_step_num = '" & rep_step_num & "' AND rep_dt = '" & rep_dt & "'"
ts1.WriteLine ";"
ts1.WriteLine ".IF ERRORCODE > 0 THEN .GOTO lbl_ERROR"
ts1.WriteLine "--"
END IF
END IF
LOOP
ts2.CLOSE
ts1.WriteLine "DELETE FROM prod_oyo_sbx.Z00_REPORTS_RESULT WHERE REP_NM = '" & rep_nm & "'"
ts1.WriteLine ";"
ts1.WriteLine "INSERT INTO prod_oyo_sbx.Z00_REPORTS_RESULT VALUES ("
ts1.WriteLine "'" & rep_nm & "'"
ts1.WriteLine ", '" & sql_nm & "'"
ts1.WriteLine ", '0'"
ts1.WriteLine ", '0'"
ts1.WriteLine ")"
ts1.WriteLine ";"
ts1.WriteLine ".IF ERRORCODE > 0 THEN .QIUT ERRORCODE"
ts1.WriteLine ".LOGOFF ;"
ts1.WriteLine ".QUIT"
ts1.WriteLine ".LABEL lbl_ERROR"
ts1.WriteLine "DELETE FROM prod_oyo_sbx.Z00_REPORTS_RESULT WHERE REP_NM = '" & rep_nm & "'"
ts1.WriteLine ";"
ts1.WriteLine "INSERT INTO prod_oyo_sbx.Z00_REPORTS_RESULT VALUES ("
ts1.WriteLine "'" & rep_nm & "'"
ts1.WriteLine ", '" & sql_nm & "'"
ts1.WriteLine ", '-1'"
ts1.WriteLine ", '-1'"
ts1.WriteLine ")"
ts1.WriteLine ";"
ts1.WriteLine ".IF ERRORCODE > 0 THEN .QIUT ERRORCODE"
ts1.WriteLine ".LOGOFF ;"
ts1.WriteLine ".QUIT"
ts1.CLOSE
'Функция замены переменных на установленные значения
FUNCTION OptReplace(str, i_max, arr)
dim i
dim s
FOR i = 0 TO i_max
str = REPLACE(str, arr(i, 0), arr(i, 1))
NEXT 'i
OptReplace = str
END FUNCTION
'Функция поиска даты отчета
FUNCTION FindDtRep(str, i_max, arr)
dim i
FindDtRep = ""
FOR i = 0 TO i_max
IF arr(i, 0) = "&DT_REP" THEN
FindDtRep = arr(i, 1)
END IF
NEXT 'i
END FUNCTION
'''''''''SQL
/*#STEP01:Z00_REPORTS_OPTIONS---DELETE*/
DELETE FROM prod_oyo_sbx.Z00_REPORTS_OPTIONS WHERE REP_NM = 'REP016_CH_SBL_TRM01_HIST'
;
/*#STEP02:Z00_REPORTS_OPTIONS---INSERT LAST_CLOSE_DT*/
INSERT INTO prod_oyo_sbx.Z00_REPORTS_OPTIONS VALUES (
'REP016_CH_SBL_TRM01_HIST'
, 'LAST_CLOSE_DT'
, ( SELECT CAST(MIN(CAST(PARAM_VALUE AS DATE)) AS DATE FORMAT 'yyyy-mm-dd') AS REPORT_DT
FROM prod_v_cdm.PROMO_MESSAGE_BOX
WHERE 1=1
AND ETL_FLOW_NAME = 'AGG_PLAN_FACT_ISSUE_DAILY_CDM_BUILD'
AND ANALYTIC_DIMENSION LIKE '000%'
AND STATE_FLG = '1'
AND PARAM_NAME = 'REPORT_DT'
)
, 'REPLACE')
;