-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathimport.bat
108 lines (104 loc) · 4.35 KB
/
import.bat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
@echo off
title Bidtracer Import Script
call "%~dp0../env_vars.bat"
setlocal EnableDelayedExpansion
set "estimateFileRaw=%UserProfile%\Downloads\DCReportData.xlsx"
set "estimateFile=%UserProfile%\Downloads\DCReportData.csv"
set "marginFileRaw=%UserProfile%\Downloads\Margin Report.xlsx"
set "marginFile=%UserProfile%\Downloads\Margin Report.csv"
set "script=%~dp0mail_script.ps1"
set "helper=%~dp0helper.ps1"
(
if exist "%script%" del /F "%script%" >nul
set "err=Error source is unknown."
call :main
if !ErrorLevel! EQU 0 (
echo !date!-!time! - Import succeeded.
if exist "%estimateFileRaw%" del /F "%estimateFileRaw%" >nul 2>nul
if exist "%marginFileRaw%" del /F "%marginFileRaw%" >nul 2>nul
) else (
echo !err!
echo !date!-!time! - Import failed.
call :email
)
if exist "%estimateFile%" del /F "%estimateFile%" >nul 2>nul
if exist "%marginFile%" del /F "%marginFile%" >nul 2>nul
) >> "%~dp0log.txt" 2>&1
exit /b
:main
echo %date%-%time% - Attempting to import data.
if not exist "%estimateFileRaw%" (
set "err=Could not locate raw estimates data file."
exit /b 1
)
if not exist "%marginFileRaw%" (
set "err=Could not locate raw margin data file."
exit /b 1
)
PowerShell -NoLogo -File "%helper%" "%estimateFileRaw%"
if not exist "%estimateFile%" (
set "err=Failed to convert raw estimates data file from XLSX to CSV."
exit /b 1
)
PowerShell -NoLogo -File "%helper%" "%marginFileRaw%"
if not exist "%marginFile%" (
set "err=Failed to convert raw margin data file from XLSX to CSV."
exit /b 1
)
set "PGPASSWORD=!postgresql_pass!"
set suc=1
for /l %%i in (1,1,%attempts%) do (
if !suc! NEQ 0 (
if %%i EQU 1 (
psql -h "!postgresql_url!" -p !postgresql_port! -U "!postgresql_user!" -d "!postgresql_database!" -q -c "\copy bidtracer.alloc (bid_id, bid_title, cost_code, cost_description, hours, amount) from '%estimateFile%' with DELIMITER ',' CSV HEADER"
set "suc=!ErrorLevel!"
) else (
timeout /nobreak /t 300 >nul
psql -h "!postgresql_url!" -p !postgresql_port! -U "!postgresql_user!" -d "!postgresql_database!" -q -c "\copy bidtracer.alloc (bid_id, bid_title, cost_code, cost_description, hours, amount) from '%estimateFile%' with DELIMITER ',' CSV HEADER" >nul 2>&1
set "suc=!ErrorLevel!"
)
)
)
if !suc! NEQ 0 (
set "err=Failed to import estimate data file into database."
exit /b 1
)
set suc=1
for /l %%i in (1,1,%attempts%) do (
if !suc! NEQ 0 (
if %%i EQU 1 (
psql -h "!postgresql_url!" -p !postgresql_port! -U "!postgresql_user!" -d "!postgresql_database!" -q -c "\copy bidtracer.margin (bid_id, margin, contract, cost) from '%marginFile%' with DELIMITER ',' CSV HEADER"
set "suc=!ErrorLevel!"
) else (
timeout /nobreak /t 300 >nul
psql -h "!postgresql_url!" -p !postgresql_port! -U "!postgresql_user!" -d "!postgresql_database!" -q -c "\copy bidtracer.margin (bid_id, margin, contract, cost) from '%marginFile%' with DELIMITER ',' CSV HEADER" >nul 2>&1
set "suc=!ErrorLevel!"
)
)
)
if !suc! NEQ 0 (
set "err=Failed to import margin data file into database."
exit /b 1
)
exit /b 0
:email
(
echo Send-MailMessage -From "!pbi_email!" -To "!error_email!".Split^(";"^) -Subject "Bidtracer Import Failure" -Body "Script triggered by Power Automate failed to update database. %err%" -SmtpServer "smtp-mail.outlook.com" -Port 587 -UseSsl -Credential ^(New-Object PSCredential^("!pbi_email!", ^(ConvertTo-SecureString "!pbi_password!" -AsPlainText -Force^)^)^)
echo if ^( $? ^){ exit 0 }else{ exit 1 }
)>"%script%"
PowerShell -ExecutionPolicy Bypass -NoLogo -NonInteractive -File "%script%"
if %ErrorLevel% NEQ 0 (
echo [!date! - !time!] Failed to send email notification with 2 attempts left.>>"%~dp0log.txt"
timeout /t 5 /nobreak >nul
PowerShell -ExecutionPolicy Bypass -NoLogo -NonInteractive -File "%script%"
if !ErrorLevel! NEQ 0 (
echo [!date! - !time!] Failed to send email notification with 1 attempt left.>>"%~dp0log.txt"
timeout /t 5 /nobreak >nul
PowerShell -ExecutionPolicy Bypass -NoLogo -NonInteractive -File "%script%"
if !ErrorLevel! NEQ 0 (
echo [!date! - !time!] Failed to send email notification with 0 attempts left.>>"%~dp0log.txt"
)
)
)
if exist "%script%" del /F "%script%" >nul
exit /b 0