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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229 | def pipelineProcess(**context):
token = context.get("ti").xcom_pull(key="token")
# Contract Data
Data = getSheetData(token, "63fd6bf19f48080c646e8978")
# "Section and key date data"
Data2 = getSheetData(token, "63fd6c769f48080c646e8d3e")
conn_string = getdrowPSQLConnectionString()
db = create_engine(conn_string)
conn = db.connect()
df = pd.DataFrame()
_df = pd.DataFrame()
with conn as conn:
for x in Data:
df_nested_list = json_normalize(x)
df2 = df_nested_list
df = df.append(df2)
df['starting date']=df['starting date'].apply(pd.to_datetime)
df['ori comp date']=df['ori comp date'].apply(pd.to_datetime)
df.columns = df.columns.str.replace(' ', '_').str.replace('.', '').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent').str.replace('/', '_')
df.to_sql('c7_nec_section_of_work', con=conn, if_exists='replace', index= False)
for x in Data2:
df_nested_list = json_normalize(x)
df2=df_nested_list
_df = _df.append(df2)
_df['Starting Date']=_df['Starting Date'].apply(pd.to_datetime)
_df['Original completion dates']=_df['Original completion dates'].apply(pd.to_datetime)
_df.columns = _df.columns.str.replace(' ', '_').str.replace('.', '').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent').str.replace('/', '_')
_df.to_sql('c7_nec_section_of_work_key_date', con=conn, if_exists='replace', index= False)
#"PWDD and Target Cost with Actual Monthly Total"
Data = getSheetData(token, "63fef0885b52030c99e27906")
df = pd.DataFrame.from_dict(Data)
numerics = df.select_dtypes(include="number").columns
df=df.apply(pd.to_numeric, errors='ignore')
df[numerics]=df[numerics].apply(lambda x: np.round(x, decimals=5))
df['IP No.']=df['IP No.'].astype(str)
df['Month - Year']=df['Month - Year'].apply(pd.to_datetime)
df.columns = df.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent')
db = create_engine(conn_string)
conn = db.connect()
with conn as conn:
df.to_sql('c7_finance_data', con=conn, if_exists='replace')
conn.close()
# "Approved and Forecast Price Bar Chart"
data = getSheetData(token, "63fd6ba2d6779f0c607a7392")
df = pd.DataFrame.from_dict(data)
numerics = df.select_dtypes(include="number").columns
df=df.apply(pd.to_numeric, errors='ignore')
df[numerics]=df[numerics].apply(lambda x: np.round(x, decimals=5))
df['Month - Year']=df['Month - Year'].apply(pd.to_datetime)
df.columns = df.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent')
db = create_engine(conn_string)
conn = db.connect()
with conn as conn:
df.to_sql('c7_finance_status_data', con=conn, if_exists='replace')
conn.close()
# EOT DATA
# data = getSheetData(token, "63fd6c1b86bb350c631873b3")
# df = pd.DataFrame.from_dict(data)
# numerics = df.select_dtypes(include="number").columns
# df=df.apply(pd.to_numeric, errors='ignore')
# df[numerics]=df[numerics].apply(lambda x: np.round(x, decimals=5))
# df['Month - Year']=df['Month - Year'].apply(pd.to_datetime)
# df.columns = df.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent')
# db = create_engine(conn_string)
# conn = db.connect()
# with conn as conn:
# df.to_sql('c7_eot_data', con=conn, if_exists='replace')
# conn.close()
# "Programme Data"
data= getSheetData(token, "63fd6c484fa5210cfa582fe0")
if data:
df = pd.DataFrame.from_dict(data)
df['Submission Date']=df['Submission Date'].apply(pd.to_datetime)
df['Acceptance Date']=df['Acceptance Date'].apply(pd.to_datetime)
df.columns = df.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent')
db = create_engine(conn_string)
conn = db.connect()
with conn as conn:
df.to_sql('c7_programme_data', con=conn, if_exists='replace')
conn.close()
#"key date Planned Completion Date (PCD)"
data = getSheetData(token, "63fd6c9a9f48080c646e8dba")
df = pd.DataFrame.from_dict(data)
df['Planned Completion Date(PCD)']=df['Planned Completion Date(PCD)'].apply(pd.to_datetime)
df.columns = df.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent')
db = create_engine(conn_string)
conn = db.connect()
with conn as conn:
df.to_sql('c7_key_date_data', con=conn, if_exists='replace')
conn.close()
# CAS
_Data = getWorkflowData(token, "637c7d22b38f8ca02f5c49a9")
Mapping= {
"Original Doc No.": "Original_Doc_No",
"NEC Doc Type": "NEC_Doc_Type",
"NEC Event No.": "NEC_Event_No",
"Doc Ver.": "Doc_Ver",
"Doc Date": "Doc_Date",
"Subject": "Subject",
"From": "From",
"To": "To",
"CE Amount": "CE_Amount",
"CE Increase / Decrease": "CE_Increase_Decrease",
"Quotation Status": "Quotation_Status",
"NEC Clause": "NEC_Clause",
}
conn_string = getdrowPSQLConnectionString()
db = create_engine(conn_string)
conn = db.connect()
df = pd.DataFrame()
with conn as conn:
for x in _Data:
try:
# print(x['data'])
if len(x['data'].keys()) == 0:
continue
df_nested_list = json_normalize(x['data'])
df2 = df_nested_list.reindex(columns=Mapping.keys())
df2['NEC Doc Title']=x['data']['NEC Doc Type']+x['data']['NEC Event No.']
df2['Doc Org Ver']= x['data']['Doc Ver.']
# y=0
# if x['data']['Doc Ver.'] == None:
# df2['Doc Ver.'] = y
# elif x['data']['Doc Ver.'].startswith('Rev. '):
# y = x['data']['Doc Ver.'].replace('Rev. ', '')
# y = int(y)
# else:
# y = x['data']['Doc Ver.'].replace('-', '').replace('r','')
# if y!='' and not y.isnumeric():
# y = int(ord(y)) - int(ord('A')) + 1
# elif y =='':
# y = 0
# else :
# y = int(y)
# df2['Doc Ver.'] = y
# if (not df2['NEC Doc Title'].empty and 'NEC Doc Title' in df.columns):
# check_ver_df = df.loc[(df['NEC Doc Title'] == x['data']['NEC Doc Type']+x['data']['NEC Event No.'])]
# if check_ver_df.empty:
# df2['is_latest'] = 'Yes'
# else :
# check_ver_df2 = check_ver_df.loc[(check_ver_df['Doc Ver.'] > y)]
# if not check_ver_df2.empty:
# df2['is_latest'] = "No"
# else:
# df.loc[(df['NEC Doc Title'] == x['data']['NEC Doc Type']+x['data']['NEC Event No.']) & ( df['Doc Ver.']<y), 'is_latest'] = 'No'
# df2['is_latest'] = 'Yes'
# else:
# df2['is_latest'] = 'Yes'
df2['NEC Doc Title With Version']=x['data']['NEC Doc Type']+x['data']['NEC Event No.']
# +'-'+str(y)
if len(x['data']['Change to Time'])>0 and x['data']['NEC Doc Type']!='EW':
df4=pd.DataFrame()
for change_to_time_table in x['data']['Change to Time']:
df3=df2.copy()
if 'Key Date' in change_to_time_table:
df3['Key Date'] = change_to_time_table['Key Date']
if 'Extension in days' in change_to_time_table:
df3['Extension in days'] = change_to_time_table['Extension in days']
if 'Ori Completion Date' in change_to_time_table:
df3['Ori Completion Date'] = change_to_time_table['Ori Completion Date']
if 'Revised Completion Date' in change_to_time_table:
df3['Revised Completion Date'] = change_to_time_table['Revised Completion Date']
df4 = df4.append(df3)
df2 = df2.iloc[0:0]
df2=df2.append(df4)
df = df.append(df2)
except Exception as e:
print(e)
continue
df.rename(columns=Mapping, inplace=True)
df['Doc_Date']=df['Doc_Date'].apply(pd.to_datetime)
df['Doc_Date'] = df['Doc_Date'] - pd.Timedelta(hours=8)
df['Ori Completion Date']=df['Ori Completion Date'].apply(pd.to_datetime)
df['Ori Completion Date'] = df['Ori Completion Date'] - pd.Timedelta(hours=8)
df['Revised Completion Date']=df['Revised Completion Date'].apply(pd.to_datetime)
df['Revised Completion Date'] = df['Revised Completion Date'] - pd.Timedelta(hours=8)
df.columns = df.columns.str.replace(' ', '_').str.replace('.', '').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent').str.replace('/', '_')
df.to_sql('c7_nec_cas', con=conn, if_exists='replace', index= False)
conn.close()
# Risk Registry
resData = getWorkflowData(token, "638b33e5a1faf60c870390dd")
db = create_engine(conn_string)
conn = db.connect()
df = pd.DataFrame()
with conn as conn:
for x in resData:
df_nested_list = json_normalize(x['data'])
df2 = df_nested_list
if x['data']['Date of Early Warning'] == None:
Date_of_Early_Warning = datetime.now(timezone.utc)
else:
Date_of_Early_Warning = datetime.strptime(x['data']['Date of Early Warning'], '%Y-%m-%dT%H:%M:%S.%f%z')
if x['data']['Date of Close of EW'] == None:
Date_of_Close_of_EW = datetime.now(timezone.utc)
else:
Date_of_Close_of_EW = datetime.strptime(x['data']['Date of Close of EW'], '%Y-%m-%dT%H:%M:%S.%f%z')
# print((Date_of_Close_of_EW - Date_of_Early_Warning))
if (Date_of_Close_of_EW - Date_of_Early_Warning) > np.timedelta64(24, 'h'):
df2['Elapsed_Time'] = ((Date_of_Close_of_EW - Date_of_Early_Warning))
else:
df2['Elapsed_Time'] = np.timedelta64(0, 'D')
if (df2['Elapsed_Time'] >= np.timedelta64(365, 'D')).bool():
df2['Elapsed_Time_more_then_1_year'] = True
else:
df2['Elapsed_Time_more_then_1_year'] = False
df2['Elapsed_Time'] = df2['Elapsed_Time'] / 1000 / 1000 / 86400000
df = df.append(df2)
df['Date of Close of EW']=df['Date of Close of EW'].apply(pd.to_datetime)
df['Date of Close of EW'] = df['Date of Close of EW'] - pd.Timedelta(hours=8)
df['Date of Early Warning']=df['Date of Early Warning'].apply(pd.to_datetime)
df['Date of Early Warning'] = df['Date of Early Warning'] - pd.Timedelta(hours=8)
# df['Action Party (CEDD / AECOM / CW-KL JV)']=np.array(df['Action Party (CEDD / AECOM / CRCC-PY JV)'].tolist())
df.columns = df.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('/', '_').str.replace('%', 'percent')
# df['Action_Party___CEDD_/_AECOM_/_DCK_JV']=np.array(df['Action_Party___CEDD_/_AECOM_/_DCK_JV'].tolist())
df.to_sql('c7_nec_risk_register', con=conn, if_exists='replace', index= False)
conn.close()
|