· 4 years ago · Apr 22, 2021, 09:14 PM
1from sqlalchemy import create_engine, MetaData, Table, select, exc
2import base64
3import pyodbc
4import requests
5import pandas as pd
6from six.moves import urllib
7import datetime
8from datetime import timedelta, datetime
9import numpy as np
10# import pyodbc
11
12
13# Returns acess token
14def get_access_token(tenantCode):
15 # Obtaining the access token from the Token API ##
16 url = "https://portal.neighborlysoftware.com/Token"
17
18 data = {
19 "grant_type": "password",
20 "auth_type": "password",
21 "tenantcode": tenantCode,
22 "machine_code": "7fe4fe85-4c1e-4743-b752-ac0de8f78f5e",
23 "username": "TDR.ERAPData@TetraTech.com",
24 "password": "TDRCategory5$"
25 }
26
27 page = requests.post(url, data=data)
28 token_dict = page.json()
29 new_token = (token_dict["access_token"])
30
31 return new_token
32
33
34def get_start_date():
35 # Getting last record date from SQL Server ##
36 cnxn = pyodbc.connect(r"Driver={ODBC Driver 17 for SQL Server};"
37 r"DSN=ODBC Driver 17 for SQL Server;"
38 r"Server=TDRS706RTSQL2;"
39 r"Database=ERAP;"
40 r"UID=erap_worker;"
41 r"PWD=6k2XgAy6d9kkVV2r;"
42 r"Trusted_Connection=no"
43 )
44
45 cursor = cnxn.cursor()
46 name = list(tenantMap.values())[0][0]
47 test = cursor.execute(f'SELECT MAX([EntryDateTime]) FROM [dbo].[' + name + ']')
48 records = cursor.fetchall()
49 records = records[0]
50
51 gen_startDate = records[0]
52 #gen_startDate = gen_startDate + datetime.timedelta(second = 5)
53 #gen_startDate = '2021-01-01'
54 return gen_startDate
55
56
57# Main program
58def get_sql_engine():
59 sql_server = "TDRS706RTSQL2"
60 sql_db = "ERAP"
61 # meta = MetaData()
62 # table_name_long = Table("GreenvilleSC_AuditLog", meta,
63 # Column("ID", Integer, primary_key=True, autoincrement=True),
64 # Column("CaseID", Integer),
65 # Column("CaseName", String),
66 # Column("EntryType", String),
67 # Column("EntryDateTime", DateTime),
68 # Column("User", String),
69 # Column("Note", String)),
70 # Column("TimeInStatus", BigInteger)),
71 # Column("Note", String))
72 sql_username = "erap_worker"
73 sql_password = "6k2XgAy6d9kkVV2r"
74
75 engine = create_engine("mssql+pyodbc://" + sql_username + ":" +
76 sql_password + "@" + sql_server + "/" + sql_db + "?driver=SQL+Server")
77
78 return engine
79
80
81# 'X-NBLY-Tenant-Code': 'ERAP-GREENVILLECOUNTYSC'
82# 'workflowId': '1437',
83# Takes in tenantCode, workflowID, authorisation token, startDate and endDate.
84# Returns dataframe of tenant's data
85def get_tenant_data(tenantCode, new_token, gen_startDate):
86 # Using global variable tenantMap
87 global tenantMap
88
89 # Pulling the new data from the Audit API ##
90 url_pull = "https://portal.neighborlysoftware.com/api/Reporting/GetAuditLogReport"
91 pull_headers = {
92 'Authorization': "Bearer " + new_token,
93 'X-NBLY-Tenant-Code': tenantCode
94 }
95
96 pull_params = {
97 'workflowId': tenantMap[tenantCode][1],
98 'startDate': gen_startDate,
99 'endDate': datetime.now(),
100 'entryTypes': '0,1,2,3,4,21,22,23,24,25,26,27,28,29,30,55,56,57,59'
101 }
102
103 pull_response = requests.get(
104 url_pull, headers=pull_headers, params=pull_params)
105 pulled_data_list = []
106 record_data = pull_response.json()
107 data = record_data["DataSet"]
108 pulled_data_list.extend(data)
109
110 df = pd.DataFrame(pulled_data_list)
111 df.columns = ["EntryID", "CaseID", "CaseName",
112 "EntryType", "EntryDateTime", "User", "Note", "drop1"]
113 del df['drop1']
114 df['TimeInStatus'] = np.nan
115 df['AssignmentProcessed'] = np.nan
116 df[["CaseID", "CaseName", "EntryID", "EntryType", "Note", "User",
117 "EntryDateTime", 'TimeInStatus', 'AssignmentProcessed']]
118 df = df.iloc[:-1]
119
120 return df
121
122
123# Main program
124# tenantCode -> ["table_name", "workflowID"]
125global tenantMap
126tenantMap = {
127 "ERAP-GREENVILLECOUNTYSC": ["GreenvilleCountySC_AuditLog", "1437"],
128 "ERAP-WinstonSalemNC": ["WinstonSalemNC_AuditLog", "1554"],
129 "ERAP-LEONCOUNTYFL": ["LeonCountyFL_AuditLog", "1422"],
130 "ERAP-MOBILECOUNTYAL": ["MobileCountyAL_AuditLog", "1392"],
131 "ERAP-PINELLASCOUNTYFL": ["PinellasCountyFL_AuditLog", "1552"],
132 "ERAP-RICHLANDCOUNTYSC": ["RichlandCountySC_AuditLog", "1488"],
133 "ERAP-CHARLESTONCOUNTYSC": ["CharlestonCountySC_AuditLog", "1582"]
134}
135
136gen_startDate = get_start_date()
137engine = get_sql_engine()
138connection = engine.connect()
139# Only needed if updating primary key column
140# engine.execute("SET IDENTITY_INSERT GreenvilleSC_AuditLog ON")
141# print(engine.table_names())
142
143for tenantCode in tenantMap.keys():
144 new_token = get_access_token(tenantCode)
145 df = get_tenant_data(tenantCode, new_token, gen_startDate)
146 # table_name = "GreenvilleCountySC_AuditLog"
147 table_name = tenantMap[tenantCode][0]
148 df.to_sql(table_name, con=engine, index=False,
149 chunksize=50, method='multi', if_exists='append')