-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathget_job_status.py
132 lines (113 loc) · 3.58 KB
/
get_job_status.py
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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
from config import CONN_STRING_TDWH
import sqlalchemy
from sqlalchemy.types import String
import pandas as pd
import pyodbc
import win32com.client
outlook = win32com.client.Dispatch("Outlook.Application")
from absl import app
from absl import flags
FLAGS = flags.FLAGS
flags.DEFINE_string("cust",
None,
"customer name"
)
flags.DEFINE_string("job",
None,
"job name"
)
flags.DEFINE_bool("debug",
False,
"Run in debug mode")
# Required flag
#flags.mark_flag_as_required("cust")
flags.mark_flag_as_required("job")
# Amazon FreshPN command line args: --cust amazon_freshpn --job GECM_RefreshAmazonFreshPN
# Sams pickup command line args: --cust sams_pickup --job GECM_RefreshSamsPickup
# WMT OG command line args: --cust walmart_og --job GECM_RefreshWalmartOG
def main(argv):
del argv
cust = FLAGS.cust
job = FLAGS.job
debug = FLAGS.debug
html = get_job_status_html(job=job, debug=debug)
html_load = get_load_status_html(cust=cust, debug=debug)
if (html_load is not None):
if (html is None):
html = html_load
else:
html += '\n\n' + html_load
#print('html:\n', html)
if (html is None):
html = '<html><body>Nothing to report.</html></body>'
send_email(cust=cust, job=job, msg_body=html, debug=debug)
def get_job_status_html(job, debug=False):
query = """
SELECT
--TOP 1
[JobName]
,[Output]
,[created]
,[createdby]
FROM [TDWH].[dbo].[ManualFileLoad_JobStatus]
WHERE [JobName] = '{job}'
ORDER BY [created] DESC
""".format(job=job)
connection_string = CONN_STRING_TDWH
db_engine = sqlalchemy.create_engine(connection_string)
# if (debug):
# print('sp:\n', sp)
# db_engine.execute(sp)
if (debug):
print('Query:\n', query)
df = pd.read_sql(query, db_engine)
row_count, col_count = df.shape
if (debug):
print('row_count: ', row_count)
if (row_count == 0):
print('\nFollowing query produced no rows:\n', query)
output = None
else:
output = df.Output[0]
if (debug):
print('Output: ', output)
return output
def get_load_status_html(cust, debug=False):
query = """
SELECT
TOP 1
[Cust]
,[Output]
,[created]
,[createdby]
FROM [TDWH].[dbo].[ManualFileLoad_LoadStatus]
WHERE [Cust] = '{cust}'
ORDER BY [created] DESC
""".format(cust=cust)
connection_string = CONN_STRING_TDWH
db_engine = sqlalchemy.create_engine(connection_string)
if (debug):
print('Query:\n', query)
df = pd.read_sql(query, db_engine)
row_count, col_count = df.shape
if (debug):
print('row_count: ', row_count)
if (row_count == 0):
print('\nFollowing query produced no rows:\n', query)
output = None
else:
output = df.Output[0]
if (debug):
print('Output: ', output)
return output
def send_email(cust, job, msg_body, debug=False):
Msg = outlook.CreateItem(0) # Email
Msg.To = "[email protected]" # you can add multiple emails with the ; as delimiter. E.g. [email protected]; [email protected];
#Msg.CC = "[email protected]"
Msg.Subject = "[MAN_FILE_LOAD] Load Status for '" + cust.upper() + "'" # + ' (Job: ' + job + ')'
Msg.BodyFormat = 2
Msg.HTMLBody = msg_body
#Msg.display()
Msg.Send()
if __name__ == "__main__":
app.run(main)