mirror of
https://github.com/Hestia-Homes/survey-extraction.git
synced 2026-06-30 13:10:56 +00:00
236 lines
No EOL
7.2 KiB
Python
236 lines
No EOL
7.2 KiB
Python
# Wave 2's month end automation
|
|
|
|
from tqdm import tqdm
|
|
from monday import MondayClient
|
|
from etl.osmosis_complaince_address_to_files import get_all_items, extract_asset_ids
|
|
from pprint import pprint
|
|
import pandas as pd
|
|
import json
|
|
|
|
monday_key = "eyJhbGciOiJIUzI1NiJ9.eyJ0aWQiOjQ5ODc2ODQxOCwiYWFpIjoxMSwidWlkIjozNjE3ODAzNCwiaWFkIjoiMjAyNS0wNC0xMVQxMToyMzoxNy40NjdaIiwicGVyIjoibWU6d3JpdGUiLCJhY3RpZCI6MTM5OTc4MjMsInJnbiI6InVzZTEifQ.-2Lit4s46ZF6AXuMW9t0TxIaFLkHqD4Yo-PyM9i2XZY"
|
|
monday = MondayClient(monday_key)
|
|
# NCHA SHDF Westville Wave 1 & 2
|
|
board_ids = ["3900434153"]
|
|
|
|
rate_card_data = {
|
|
"job_type": [
|
|
"RA", "ATT", "Coordination Stage 1 v1", "Coordination Stage 1 v2 remodel", "Coordination Stage 1 v3 remodel",
|
|
"Design Archetype", "Design Repetitive", "Coordination Stage 2", "Lodgement phase 1", "Full lodgement phase 2",
|
|
"Post EPR", "Post EPC", "Post ATT", "retrofit evaluation",
|
|
"RA no show", "ATT no show", "post EPC no show", "Design Revision"
|
|
],
|
|
"rate": [
|
|
207.65, 101, 186.4, 98, 98,
|
|
450, 150, 163, 135, 120,
|
|
"60 - Needs to be verified (Post EPR)", 45, 90.5, 40,
|
|
25, 25, 25, "check with Kevin"
|
|
]
|
|
}
|
|
|
|
rate_card_df = pd.DataFrame(rate_card_data)
|
|
|
|
|
|
for board in tqdm(board_ids):
|
|
board_data = monday.boards.fetch_boards_by_id(board)
|
|
columns = board_data["data"]["boards"][0]["columns"]
|
|
col_id_map = {col["title"].lower(): col["id"] for col in columns}
|
|
reversed_col_id_map = {v: k for k, v in col_id_map.items()}
|
|
|
|
|
|
items = get_all_items(board, monday)
|
|
|
|
all_records = []
|
|
for row in tqdm(items):
|
|
data = {}
|
|
data.update({"address": row['name']})
|
|
data.update({"client": row['group']['title']})
|
|
for col in row.get("column_values", []):
|
|
if col.get("id") in reversed_col_id_map:
|
|
if col.get("type") == "file":
|
|
value = col.get("value")
|
|
no_of_files = 0
|
|
|
|
if value:
|
|
value = json.loads(col["value"])
|
|
no_of_files = len(value.get('files', []))
|
|
data.update({reversed_col_id_map[col.get("id")]: no_of_files})
|
|
elif "no show" in reversed_col_id_map[col.get("id")]:
|
|
def extract_number_from_text(text):
|
|
number_str = ''
|
|
|
|
for char in text:
|
|
if char.isnumeric():
|
|
number_str += char
|
|
elif number_str:
|
|
break # stop once a number sequence ends
|
|
|
|
return int(number_str) if number_str else None
|
|
text = col.get("text")
|
|
if text is None:
|
|
data.update({
|
|
reversed_col_id_map[col.get("id")]: col.get("text")
|
|
})
|
|
else:
|
|
data.update({
|
|
reversed_col_id_map[col.get("id")]: extract_number_from_text(text)
|
|
})
|
|
else:
|
|
data.update({
|
|
reversed_col_id_map[col.get("id")]: col.get("text")
|
|
})
|
|
all_records.append(data)
|
|
|
|
# Convert to DataFrame
|
|
df = pd.DataFrame(all_records)
|
|
|
|
filtered_dfs = []
|
|
|
|
# RA
|
|
ra = df[
|
|
df["ra"].str.lower().isin(["completed rdsap 10", "completed rdsap 9.9"])
|
|
].copy()
|
|
ra["job_type"] = "RA"
|
|
filtered_dfs.append(ra)
|
|
|
|
|
|
# ATT
|
|
att = df[
|
|
df["att"].str.lower().isin(["completed"])
|
|
].copy()
|
|
att["job_type"] = "ATT"
|
|
filtered_dfs.append(att)
|
|
|
|
# V1 Coordination
|
|
v1 = df[
|
|
df["v1 coordination status"].str.lower().isin(["rc complete"])
|
|
].copy()
|
|
v1["job_type"] = "Coordination Stage 1 v1"
|
|
filtered_dfs.append(v1)
|
|
|
|
# V2 Coordination
|
|
_ = df[df["v2 invoiced"].fillna('').str.lower().isin(['to be invoiced'])]
|
|
v2 = _[_["v2 dc/ima/pas"] > 0].copy()
|
|
v2["job_type"] = "Coordination Stage 1 v2 remodel"
|
|
filtered_dfs.append(v2)
|
|
|
|
# V3 Coordination
|
|
v3 = df[
|
|
df["v3 invoiced"].str.lower().isin(["to be invoiced"])
|
|
].copy()
|
|
v3["job_type"] = "Coordination Stage 1 v3 remodel"
|
|
filtered_dfs.append(v3)
|
|
|
|
# Coordination stage 2 Please complete
|
|
cors2 = df[
|
|
df["rc stg. 2"].str.lower().isin(["to invoice"])
|
|
].copy()
|
|
cors2["job_type"] = "Coordination Stage 2"
|
|
filtered_dfs.append(cors2)
|
|
|
|
# Design type archietype
|
|
design1 = df[
|
|
(df["design type for invoicing"].str.lower().isin(["archetype"])) & (df["design invoice status"].str.lower().isin(["to invoice"]))
|
|
].copy()
|
|
design1["job_type"] = "Design Archetype"
|
|
filtered_dfs.append(design1)
|
|
|
|
# design type reptitive
|
|
design1 = df[
|
|
(df["design type for invoicing"].str.lower().isin(["repetitive"])) & df["design invoice status"].str.lower().isin(["to invoice"])
|
|
].copy()
|
|
design1["job_type"] = "Design Repetitive"
|
|
filtered_dfs.append(design1)
|
|
|
|
# Design stage revisions
|
|
design2 = df[
|
|
df["design revision invoice status"].str.lower().isin(["to invoice"])
|
|
].copy()
|
|
design2["job_type"] = "Design Revision"
|
|
filtered_dfs.append(design2)
|
|
|
|
# Lodgement Phase 1
|
|
lodg1 = df[
|
|
df["lodg. phase 1 invoice status"].str.lower().isin(["to invoice"])
|
|
].copy()
|
|
lodg1["job_type"] = "Lodgement phase 1"
|
|
filtered_dfs.append(lodg1)
|
|
|
|
# Full Lodgement Phase
|
|
lodg2 = df[
|
|
df["full lodgement invoice status"].str.lower().isin(["to invoice"])
|
|
].copy()
|
|
lodg2["job_type"] = "Full Lodgement phase 2"
|
|
filtered_dfs.append(lodg2)
|
|
|
|
# POST EPC
|
|
post_epc = df[
|
|
df["post-epc status"].str.lower().isin(["epc files uploaded"])
|
|
].copy()
|
|
post_epc["job_type"] = "Post EPC"
|
|
filtered_dfs.append(post_epc)
|
|
|
|
|
|
# POST EPR
|
|
post_epr = df[
|
|
df["post-epc status"].str.lower().isin(["post epr completed"])
|
|
].copy()
|
|
post_epr["job_type"] = "Post EPR"
|
|
filtered_dfs.append(post_epr)
|
|
|
|
|
|
|
|
# Post ATT
|
|
post_att = df[
|
|
df["post-att"].str.lower().isin(["post-att uploaded"])
|
|
].copy()
|
|
post_att["job_type"] = "POST ATT"
|
|
filtered_dfs.append(post_att)
|
|
|
|
|
|
# Retrofit Evaluation
|
|
retro = df[
|
|
df["retrofit evaluation"].str.lower().isin(["complete"])
|
|
].copy()
|
|
retro["job_type"] = "Retrofit Evaluation"
|
|
filtered_dfs.append(retro)
|
|
|
|
# RA NO Show
|
|
ra_ns = df[
|
|
(df["ra no show evidence"].fillna(-9999) != df["ra no show invoice"].fillna(-9999)) &
|
|
(df["ra no show evidence"] != 0)
|
|
].copy()
|
|
ra_ns["job_type"] = "RA NO SHOW"
|
|
filtered_dfs.append(ra_ns)
|
|
|
|
|
|
|
|
# ATT NO Show
|
|
att_ns = df[
|
|
(df["att no show evidence"].fillna(-9999) != df["att no show invoice"].fillna(-9999)) &
|
|
(df["att no show evidence"] != 0)
|
|
].copy()
|
|
att_ns["job_type"] = "ATT NO SHOW"
|
|
filtered_dfs.append(att_ns)
|
|
|
|
|
|
# Post visit no show
|
|
epc_ns = df[
|
|
(df["epc no show evidence"].fillna(-9999) != df["epc no show invoice"].fillna(-9999)) &
|
|
(df["epc no show evidence"] != 0)
|
|
].copy()
|
|
epc_ns["job_type"] = "post EPC NO SHOW"
|
|
filtered_dfs.append(epc_ns)
|
|
|
|
final_df = pd.concat(filtered_dfs).reset_index(drop=True)
|
|
|
|
|
|
final_df["job_type"] = final_df["job_type"].str.lower()
|
|
rate_card_df["job_type"] = rate_card_df["job_type"].str.lower()
|
|
|
|
# Now perform the merge
|
|
combined_with_rates = final_df.merge(rate_card_df, on="job_type", how="left")
|
|
import datetime
|
|
timestamp = datetime.datetime.now().strftime('%Y-%m-%d_%H-%M')
|
|
|
|
attribute = ['address', 'client', 'job_type', 'rate']
|
|
combined_with_rates[attribute].to_excel(f'NCHA SHDF Westville Wave 1 & 2_{timestamp}.xlsx', index=False) |