8Ucb1PMW

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