#!/usr/bin/env python3 """Normalise the install schedule workbook into a single, reviewable CSV. The workbook is ten regional sheets of historical solar installs, each with a different set of columns, and each row is one installation. This script does the part that is easier in Python than in PHP - reading .xlsx and cleaning 8,000 rows of free text - and writes one tidy CSV: storage/app/import/install-schedule.csv the rows to import storage/app/import/dropped.csv rows left out, and why storage/app/import/report.json what the clean-up decided The CSV is the contract with `php artisan crm:import-install-schedule`, which does every database write. Keeping the two apart means the parsing can be inspected - and corrected - before anything touches the CRM. What it decides, and why: * The first column is always the client's name. On NT and "Cairns (OLD)" the phone and address columns are the other way round, so the swap is detected from the header labels and confirmed against the cell contents. * A client with several installations appears once per installation, and the names are not always spelled the same. Rows are merged into one client when the normalised names match exactly, or when the names are near-identical (>= 0.88) and the row carries the same phone number or the same address. That second rule catches "Eric Beckman"/"Eric Beckmann" and "Phil Wilkerson"/"Philip Wilkerson" without merging the common names of strangers. * A row is only kept when it has a name, a phone number and an address. * State and postcode are pulled out of the address where they are present, and otherwise fall back to the sheet the row came from, because the sheets are regional. * Job status is read from wherever the sheet records the outcome, since the column is called "Instructions", "Complete/Pending" or "Appoval" depending on the region. Usage: python tools/parse_install_schedule.py "C:/Users/techs/Downloads/Install Schedule.xlsx" """ from __future__ import annotations import argparse import collections import csv import difflib import json import pathlib import re import unicodedata import openpyxl # Sheets the workbook owner asked us to leave alone: a project sheet and a form # response export, neither of which is an install. EXCLUDED_SHEETS = {"GRC Project", "Form Responses 2"} # Which state a sheet's rows belong to when the address does not say. SHEET_STATE = { "VIC": "VIC", "TAS": "TAS", "NSW": "NSW", "SEQ": "QLD", "Regional QLD": "QLD", "SA": "SA", "WA": "WA", "ACT": "ACT", "NT": "NT", "Cairns (OLD)": "QLD", } STATES = {"NSW", "VIC", "QLD", "SA", "WA", "TAS", "ACT", "NT"} # Postcode ranges decide the state when the address does not name one, because # the sheets are only roughly regional: the VIC sheet holds Townsville and # Alice Springs installs. Ranges are inclusive, on the four-digit number. POSTCODE_STATE = ( (800, 999, "NT"), (1000, 2599, "NSW"), (2600, 2618, "ACT"), (2619, 2899, "NSW"), (2900, 2920, "ACT"), (2921, 2999, "NSW"), (3000, 3999, "VIC"), (4000, 4999, "QLD"), (5000, 5999, "SA"), (6000, 6999, "WA"), (7000, 7999, "TAS"), (8000, 8999, "VIC"), (9000, 9999, "QLD"), ) def state_for_postcode(postcode: str) -> str: if not postcode.isdigit(): return "" value = int(postcode) for low, high, state in POSTCODE_STATE: if low <= value <= high: return state return "" STATE_NAMES = { "new south wales": "NSW", "victoria": "VIC", "queensland": "QLD", "south australia": "SA", "western australia": "WA", "tasmania": "TAS", "australian capital territory": "ACT", "northern territory": "NT", } STATE_RE = re.compile( r"\b(" + "|".join(sorted(STATES, key=len, reverse=True)) + r")\b", re.IGNORECASE ) # Street types, used to split "6 Kamil St Melton Sth" into a street and a suburb # when there is no comma to go on (about a fifth of the rows). STREET_TYPES = { "st", "street", "rd", "road", "ave", "avenue", "av", "cres", "crescent", "crt", "court", "ct", "dve", "drive", "dr", "cl", "close", "pl", "place", "tce", "terrace", "hwy", "highway", "lane", "ln", "way", "blvd", "boulevard", "cir", "circuit", "pde", "parade", "esp", "esplanade", "gr", "grove", "gdn", "gardens", "rise", "loop", "mews", "quay", "row", "sq", "square", "walk", "cct", "pkwy", "parkway", "hollow", "hts", "heights", "bend", "brae", "crest", "gate", "glen", "manor", "ridge", "run", "slope", "track", "vale", "vista", "waters", "grange", "pocket", "rv", "rtt", "ent", "entrance", "bowl", "chase", "corners", "elbow", "fairway", "fwy", "freeway", "gully", "knoll", "link", "mwy", "motorway", "outlook", "park", "pass", "point", "quays", "reach", "rest", "retreat", "rise", "round", "steps", "strand", "strip", "tarn", "walkway", "bend", "bypass", } # Words that make a client an organisation rather than a household. The CRM # bills organisations against company_name, so getting this wrong leaves the # customer showing as "Customer #123". ORG_WORDS = ( "pty", "ltd", "limited", "inc", "proprietary", "holdings", "group", "trust", "services", "service", "electrical", "electrics", "constructions", "construction", "building", "builders", "developments", "projects", "project", "engineering", "enterprises", "enterprise", "australia", "australian", "trading", "transport", "hardware", "container", "containers", "farm", "farms", "station", "stations", "school", "college", "church", "council", "shires", "hotel", "motel", "resort", "caravan", "park", "childcare", "child care", "medical", "health", "pharmacy", "clinic", "dental", "accountants", "solicitors", "real estate", "agency", "management", "industries", "supplies", "wholesale", "retail", "cafe", "restaurant", "bakery", "warehouse", "logistics", "solutions", "technology", "technologies", "systems", "energy", "solar", "mining", "resources", "aboriginal", "corporation", "cooperative", "association", "club", "sports", "dairy", "winery", "vineyard", "nursery", "plant", "quarries", "haulage", "earthmoving", "investment", "investments", "superannuation", "fund", "funds", "care", "aged care", "disability", "hospital", "university", "tafe", "government", "department", "authority", ) # Outcome columns differ per sheet; whichever exists is read for the job status. STATUS_HEADERS = ( "complete/pending", "instructions", "appoval", "approval", "scheduled", ) # Everything else is matched by keyword, because the ten sheets use ten # different sets of names for the same thing. FIELD_HEADERS = { "system_size": ("sys size", "system size"), "inverter_size": ("inverter size",), "panel_count": ("# panels", "# of panels"), "panel_wattage": ("panel wattage", "250w", "watt", "wattage"), "nmi": ("nmi",), "meter_id": ("meter id",), "retailer": ("elect retailer", "retailer"), "email": ("email",), "installer": ("installer", "installler"), "roof": ("roof",), "storeys": ("single/double",), "componentry": ("componentry",), "battery": ("battery",), "notes": ("notes",), "location": ("location",), "state": ("state",), "region": ("region",), } def text(value) -> str: """Cell to trimmed text, without the ".0" that openpyxl adds to numbers.""" if value is None: return "" if isinstance(value, float) and value == int(value): return str(int(value)) if isinstance(value, str): return re.sub(r"\s+", " ", value).strip() return str(value).strip() # A trailing sequence marker the sheet author added to keep repeat clients # apart: "Emjay Homes #2", "Bob McCallum 3", "Emjay Homes P/L (6)". NAME_MARKER = re.compile(r"\s*(?:[#\-\u2013]\s*\d{1,3}|\(\s*\d{1,3}\s*\))\s*$") def fold(value: str) -> str: """A name reduced to something two spellings of it can be compared through.""" value = unicodedata.normalize("NFKD", value).lower() value = re.sub(r"\b(mr|mrs|ms|miss|dr|sir|prof|the)\b\.?", " ", value) value = value.replace("&", " and ") value = re.sub(r"[^a-z0-9 ]+", " ", value) return re.sub(r"\s+", " ", value).strip() def phone_digits(value: str) -> str: """The last nine digits of a number, so 0419 983 639 matches 0419983639.""" digits = re.sub(r"\D", "", value) return digits[-9:] if len(digits) >= 9 else digits def address_fold(value: str) -> str: return re.sub(r"\s+", " ", re.sub(r"[^a-z0-9 ]+", " ", value.lower())).strip() # A run of digits, with the separators people write inside phone numbers. NUMBER_RUN = re.compile(r"\d[\d\s\-()]{5,}\d") def phone_numbers(value: str) -> list[str]: """Every phone number in a cell, in the order they appear. The column is full of real-world entries: two numbers separated by a slash, a number with the contact's name stuck on the end ("0438750374-ShaunAdams") or a mobile written without its leading zero. Returning nothing is the one case that matters - a cell such as "0", "0NA" or "04732Hyland" is not a phone number, and a row without one is not imported. """ if not value: return [] stripped = value.replace("\u00a0", " ").strip() found = [ match.group(0).strip() for match in NUMBER_RUN.finditer(stripped) if len(re.sub(r"\D", "", match.group(0))) >= 8 ] if found: return found # Nothing the length of a phone number, but the cell may still be digits # with a separator or two - "0419) 380539". digits = re.sub(r"\D", "", stripped) if len(digits) >= 6 and len(digits) / max(len(stripped), 1) >= 0.5: return [digits] return [] def number(value: str) -> float | None: """A measurement like "3.00KW", "1.52Kw", "4.0K" or "3*1.6Kw".""" if not value: return None value = value.replace(",", "") match = re.search(r"(\d+(?:\.\d+)?)", value) if not match: return None try: found = float(match.group(1)) except ValueError: return None return found if 0 < found < 1000 else None def integer(value: str) -> int | None: if not value: return None match = re.search(r"(\d+)", value.replace(",", "")) if not match: return None found = int(match.group(1)) return found if 0 < found < 5000 else None def storeys(value: str) -> int | None: folded = value.strip().lower() if folded.startswith("single"): return 1 if folded.startswith("double") or folded.startswith("2 "): return 2 return None def parse_address(raw: str) -> dict: """Split a free-text address into the columns the sites table wants.""" cleaned = re.sub(r"\s+", " ", raw.replace("\n", " ")).strip(" ,.;") state = "" match = STATE_RE.search(cleaned) if match: state = match.group(1).upper() cleaned = (cleaned[: match.start()] + " " + cleaned[match.end():]).strip(" ,;") else: lowered = cleaned.lower() for name, code in STATE_NAMES.items(): if name in lowered: state = code cleaned = re.sub(re.escape(name), " ", cleaned, flags=re.IGNORECASE).strip(" ,;") break postcode = "" candidates = [m for m in re.finditer(r"(?= 2: line1 = parts[0] suburb = " ".join(parts[1:]) else: words = cleaned.split() cut = None for index, word in enumerate(words): if word.strip(".,").lower() in STREET_TYPES: cut = index if cut is not None and cut + 1 < len(words): line1 = " ".join(words[: cut + 1]) suburb = " ".join(words[cut + 1:]) else: line1 = cleaned suburb = "" line2 = "" line1 = re.sub(r"\s+", " ", line1).strip(" ,;.-") suburb = re.sub(r"\s+", " ", suburb).strip(" ,;.-") # A unit number belongs with the street it is on: "Unit 1/18 Glenmore Cres" # stays whole, because breaking it up moves the street number away from the # street name and sends the installer to the wrong place. if len(line1) > 90: cut = line1.rfind(" ", 0, 90) cut = cut if cut > 40 else 90 line2 = line1[cut:].strip() line1 = line1[:cut].strip(" ,;.-") return { "address_line1": line1, "address_line2": line2, "suburb": suburb, "state": state if state in STATES else "", "postcode": postcode, } # A few rows in the name column are not people or companies at all: they are # the parts list for a job, written out ("Daniel G- 2 HT 440 W panels + 440W # Risen ... & SAJ R5-5K-S2"). Importing one would create a customer named after # a spec sheet, so they are left out and reported. # # The test has to be careful: real clients carry a size in their name # ("Pamela King & Colin Leask 10kw") and the odd asterisk, so a quantity like # "2 x" or two pieces of equipment wording is what gives a parts list away. QUANTITY = re.compile(r"\b\d+\s*[xX]\b") SPEC_WORDS = ( "panel", "batter", "inverter", "isolator", "meter", "rail", "tilt", "feet", "cliplok", "kwh", "kw ", "hybrid", "switchboard", "subboard", "trench", "bracket", "mount", "fuse", "dc ", "ac ", ) def looks_like_a_name(name: str) -> bool: # 250 characters is the database column, and long names are still real # clients: "Hein Anthony Dreyer and Johanna Margretha Dreyer ITOR & ATF # Dreyer Family Trust T/A Dreyer Mechanical and hydraulic services". if len(name) > 250: return False if QUANTITY.search(name): return False lowered = name.lower() return sum(1 for word in SPEC_WORDS if word in lowered) < 2 def looks_like_street(address: str) -> bool: """Whether an address carries anything that identifies a property.""" if re.search(r"\d", address): return True return any(w.strip(".,").lower() in STREET_TYPES for w in address.split()) # Matched in order, most specific first, against the componentry and notes. JOB_TYPES = ( ("service_call", ("service call", "repair", "fault", "warranty call", "health check", "failed")), ("system_upgrade", ("upgrade", "reinstall", "swap", "replace", "remove existing", "expansion")), ("battery_install", ("battery only", "battery install", "batteries only")), ) def job_type(componentry: str, details: str, commercial: bool) -> str: haystack = f"{componentry} {details}".lower() for name, needles in JOB_TYPES: if name == "battery_install" and any( word in haystack for word in ("panel", "panels", "kw system") ): continue for needle in needles: if needle in haystack: return name return "commercial_install" if commercial else "residential_install" def job_status(raw: str) -> str: """Map the sheet's own wording onto the CRM pipeline.""" folded = raw.strip().lower() if "cancel" in folded or "reject" in folded: return "cancelled" if "complete" in folded or folded.startswith("ok") or "approve" in folded or "done" in folded: return "completed" if "pending" in folded: return "install_scheduled" if folded == "": return "installed" return "installed" def title_for(system_size, panel_count, componentry: str) -> str: if system_size: size = f"{system_size:g}kW" elif panel_count: size = f"{panel_count} panel" else: size = "" if size and panel_count and system_size: return f"{size} solar system ({panel_count} panels)" if size: return f"{size} solar system" if componentry: return componentry[:80] return "Solar installation" def pick_display_name(variants: collections.Counter) -> str: """The cleanest spelling of a client's name, preferring one with no suffix.""" def score(item): name, count = item marked = bool(NAME_MARKER.search(name)) # A capitalised spelling beats a lower-case one of the same name, so a # stray "tom Freeman" does not become the customer's name. shouted = not name[:1].isupper() return (marked, shouted, -count, -len(name)) return sorted(variants.items(), key=score)[0][0] class Client: """One customer, with every installation row that belongs to them.""" def __init__(self, key: str): self.key = key self.variants: collections.Counter = collections.Counter() self.rows: list[dict] = [] def to_record(self) -> dict: name = pick_display_name(self.variants) phones = collections.Counter(r["phone"] for r in self.rows if r["phone"]) alt = collections.Counter(r["phone_alt"] for r in self.rows if r["phone_alt"]) emails = collections.Counter(r["email"] for r in self.rows if r["email"]) addresses = collections.Counter( f"{r['address_line1']}|{r['suburb']}|{r['state']}|{r['postcode']}" for r in self.rows ) sheets = sorted({r["sheet"] for r in self.rows}) commercial = is_organisation(name) first, last = ("", "") if commercial else split_person_name(name) home = addresses.most_common(1)[0][0].split("|") if addresses else ["", "", "", ""] return { "client_key": self.key, "name": name, "type": "commercial" if commercial else "residential", "company_name": name if commercial else "", "first_name": first, "last_name": last, "phone": phones.most_common(1)[0][0] if phones else "", "phone_alt": alt.most_common(1)[0][0] if alt else "", "email": emails.most_common(1)[0][0] if emails else "", "sites": len(set(addresses)), "jobs": len(self.rows), "sheets": " ".join(sheets), "billing_address_line1": home[0], "billing_suburb": home[1], "billing_state": home[2], "billing_postcode": home[3], "name_variants": " | ".join(v for v, _ in self.variants.most_common(4)), } def is_organisation(name: str) -> bool: folded = f" {name.lower()} " if any(f" {word} " in folded or f" {word}s " in folded for word in ORG_WORDS): return True # "Maritime Container Services", "Fungs Holding (Sonny)" and friends. return bool(re.search(r"\(.*\)\s*$", name)) and " and " not in folded def split_person_name(name: str) -> tuple[str, str]: parts = name.split() if not parts: return "", "" if len(parts) == 1: return parts[0], "" return parts[0], " ".join(parts[1:]) def read_rows(workbook) -> tuple[list[dict], list[dict]]: """Every usable row in the workbook, plus the rows that were left out.""" rows: list[dict] = [] dropped: list[dict] = [] for sheet in workbook.worksheets: if sheet.title in EXCLUDED_SHEETS: continue grid = [list(values) for values in sheet.iter_rows(values_only=True)] if not grid: continue header = [text(v) for v in grid[0]] # NT and "Cairns (OLD)" put the address before the phone. The label says # so, and the cells agree: a column of digits is the phone column. swapped = ( len(header) > 2 and "phone" in header[2].lower() and "address" in header[1].lower() ) indexes = map_columns(header) sheet_state = SHEET_STATE.get(sheet.title, "") for offset, line in enumerate(grid[1:], start=2): values = [text(v) for v in line] + [""] * max(0, len(header) - len(line)) if not any(values): continue name = values[0].strip() phone = (values[2] if swapped else values[1]).strip() address = (values[1] if swapped else values[2]).strip() numbers = phone_numbers(phone) reasons = [] if not name: reasons.append("no name") elif not looks_like_a_name(name): reasons.append("name is a parts list, not a client") if not numbers: reasons.append("no phone") if not address or len(address) < 4: reasons.append("no address") if reasons: dropped.append( { "sheet": sheet.title, "row": offset, "name": name, "phone": phone, "address": address, "reason": ", ".join(reasons), } ) continue parsed = parse_address(address) state = ( parsed["state"] or state_for_postcode(parsed["postcode"]) or sheet_state ) status_raw = pick(values, indexes.get("status")) componentry = pick(values, indexes.get("componentry")) battery = pick(values, indexes.get("battery")) details = " · ".join(dict.fromkeys(filter(None, (componentry, battery)))) notes = " · ".join(dict.fromkeys(pick_all(values, indexes.get("notes")))) size = number(pick(values, indexes.get("system_size"))) panels = integer(pick(values, indexes.get("panel_count"))) commercial = is_organisation(name) rows.append( { "sheet": sheet.title, "sheet_row": offset, "name": name, "name_fold": fold(name), "phone": numbers[0], "phone_alt": " / ".join(numbers[1:]), "phone_key": phone_digits(numbers[0]), "email": email_of(pick(values, indexes.get("email"))), "address_raw": address, "address_fold": address_fold(address), **parsed, "state": state, "nmi": pick(values, indexes.get("nmi")), "meter_id": pick(values, indexes.get("meter_id")), "retailer": pick(values, indexes.get("retailer")), "roof": pick(values, indexes.get("roof")), "storeys": storeys(pick(values, indexes.get("storeys"))), "system_size_kw": size, "inverter_kw": number(pick(values, indexes.get("inverter_size"))), "panel_count": panels, "panel_wattage": number(pick(values, indexes.get("panel_wattage"))), "componentry": componentry, "details": details[:2000], "notes": notes[:2000], "installer": pick(values, indexes.get("installer")), "status_raw": status_raw, "job_status": job_status(status_raw), "job_type": job_type(componentry, details, commercial), "job_title": title_for(size, panels, componentry), "street_like": looks_like_street(parsed["address_line1"]), } ) return rows, dropped def map_columns(header: list[str]) -> dict[str, object]: """Find each field's column, by keyword, for this sheet's own header row.""" indexes: dict[str, object] = {} lowered = [h.lower().strip() for h in header] # Headers are matched from the left, because the sheets decorate them: # "NMI #", "Email Address", "# of Panels", "Wattage of Panel". for field, candidates in FIELD_HEADERS.items(): for candidate in candidates: for index, label in enumerate(lowered): if label.startswith(candidate): indexes.setdefault(field, []).append(index) # type: ignore[union-attr] break for label in STATUS_HEADERS: for index, value in enumerate(lowered): if value.startswith(label): indexes["status"] = [index] break if "status" in indexes: break return indexes def pick(values: list[str], indexes: object) -> str: """The first non-empty value among a field's columns.""" if not isinstance(indexes, list): return "" for index in indexes: if index < len(values) and values[index]: return values[index] return "" def pick_all(values: list[str], indexes: object) -> list[str]: if not isinstance(indexes, list): return [] return [values[i] for i in indexes if i < len(values) and values[i]] def email_of(value: str) -> str: match = re.search(r"[\w.+-]+@[\w-]+\.[\w.-]+", value) return match.group(0) if match else "" def merge_clients( rows: list[dict], merge_marked_names: bool = False ) -> tuple[dict[str, Client], list[dict]]: """Group the rows into clients, merging the duplicate and variant names. Two things are treated as the same client: names that match once normalised, and names that are near-identical *and* share a phone number or an address. The second rule is what catches "Eric Beckman"/"Eric Beckmann" without merging the many strangers who share a common name. `merge_marked_names` also folds away a trailing "#2"-style marker, which the sheet uses for a client's repeat entries. It merges marks the cleaner name in the group, so only use it when a repeated name should always mean the same client. """ groups: dict[str, list[dict]] = collections.defaultdict(list) for row in rows: key = row["name_fold"] if merge_marked_names: key = fold(NAME_MARKER.sub("", row["name"])) groups[key].append(row) keys = list(groups) parent = {key: key for key in keys} def find(key: str) -> str: while parent[key] != key: parent[key] = parent[parent[key]] key = parent[key] return key def union(a: str, b: str) -> None: root_a, root_b = find(a), find(b) if root_a != root_b: parent[root_b] = root_a by_phone: dict[str, list[str]] = collections.defaultdict(list) by_address: dict[str, list[str]] = collections.defaultdict(list) for key, items in groups.items(): for item in items: if item["phone_key"]: by_phone[item["phone_key"]].append(key) if len(item["address_fold"]) > 8: by_address[item["address_fold"]].append(key) merges: list[dict] = [] def try_merge(bucket: dict[str, list[str]], evidence: str) -> None: for value, candidates in bucket.items(): unique = list(dict.fromkeys(candidates)) for i in range(len(unique)): for j in range(i + 1, len(unique)): a, b = unique[i], unique[j] if find(a) == find(b): continue ratio = difflib.SequenceMatcher(None, a, b).ratio() if ratio < 0.88: continue merges.append( { "kept": a, "merged": b, "similarity": round(ratio, 3), "evidence": evidence, "evidence_value": value if evidence == "phone" else value[:60], } ) union(a, b) try_merge(by_phone, "phone") try_merge(by_address, "address") clients: dict[str, Client] = {} for key, items in groups.items(): root = find(key) client = clients.setdefault(root, Client(root)) for item in items: client.variants[item["name"]] += 1 client.rows.append(item) return clients, merges # Cells holding a placeholder instead of a client's name. The sheets leave # these on repeat entries, under the client the entry belongs to. NAMELESS_CELLS = { "d", "x", "n", "y", "na", "n a", "nil", "none", "tbc", "tba", "dup", "dupe", "duplicate", "same", "as above", "do not use", "unknown", } def is_filler_name(name: str) -> bool: """Whether the name cell holds a placeholder rather than a client's name.""" folded = fold(name) # A single letter is never somebody's whole name, and a cell of punctuation # folds away to nothing at all. return len(folded) <= 1 or folded in NAMELESS_CELLS def resolve_placeholder_names( rows: list[dict], dropped: list[dict] ) -> tuple[list[dict], list[dict]]: """Give the placeholder-name rows the identity of the client they repeat. A row whose name cell is a placeholder still carries real work — the address, the system, the install date — so it is not thrown away when the client can be identified another way: exactly one other row in the workbook shares its phone number. Letters in a name column do not survive, but phone numbers do. When nothing else shares that number the row belongs to nobody, and is dropped like any other row with no name. """ by_phone: dict[str, list[dict]] = collections.defaultdict(list) for row in rows: if row["phone_key"] and not is_filler_name(row["name"]): by_phone[row["phone_key"]].append(row) adopted: list[dict] = [] kept: list[dict] = [] for row in rows: if not is_filler_name(row["name"]): kept.append(row) continue candidates = by_phone.get(row["phone_key"], []) owners = {candidate["name_fold"] for candidate in candidates} if len(owners) != 1: dropped.append( { "sheet": row["sheet"], "row": row["sheet_row"], "name": row["name"], "phone": row["phone"], "address": row["address_raw"], "reason": "name is a placeholder and no other row shares its phone", } ) continue owner = candidates[0] adopted.append( { "sheet": row["sheet"], "row": row["sheet_row"], "placeholder": row["name"], "client": owner["name"], "phone": row["phone"], "address": row["address_raw"], } ) row["name"] = owner["name"] row["name_fold"] = owner["name_fold"] kept.append(row) return adopted, kept CSV_FIELDS = [ "client_key", "name", "type", "company_name", "first_name", "last_name", "phone", "phone_alt", "email", "billing_address_line1", "billing_suburb", "billing_state", "billing_postcode", # How many installations and addresses this client ended up with, which is # the evidence that the merging worked. "jobs", "sites", "sheet", "sheet_row", "address_raw", "address_line1", "address_line2", "suburb", "state", "postcode", "nmi", "meter_id", "retailer", "roof", "storeys", "system_size_kw", "inverter_kw", "panel_count", "componentry", "details", "notes", "installer", "status_raw", "job_status", "job_type", "job_title", "site_key", ] def main() -> int: parser = argparse.ArgumentParser(description=__doc__) parser.add_argument("workbook", type=pathlib.Path) parser.add_argument( "--out", type=pathlib.Path, default=pathlib.Path("storage/app/import"), help="directory for the generated CSV and report", ) parser.add_argument( "--merge-marked-names", action="store_true", help="treat names differing only by a trailing #2-style marker as one client", ) args = parser.parse_args() if not args.workbook.exists(): print(f"Workbook not found: {args.workbook}") return 1 workbook = openpyxl.load_workbook(args.workbook, read_only=True, data_only=True) rows, dropped = read_rows(workbook) adopted, rows = resolve_placeholder_names(rows, dropped) clients, merges = merge_clients(rows, merge_marked_names=args.merge_marked_names) # Each row carries its client's details, so the CSV is readable on its own. records = {key: client.to_record() for key, client in clients.items()} owner_of = { row["name_fold"]: key for key, client in clients.items() for row in client.rows } for row in rows: row["client_key"] = owner_of[row["name_fold"]] row["site_key"] = f"{row['client_key']}|{row['address_fold']}" args.out.mkdir(parents=True, exist_ok=True) csv_path = args.out / "install-schedule.csv" with csv_path.open("w", newline="", encoding="utf-8") as handle: writer = csv.DictWriter(handle, fieldnames=CSV_FIELDS, extrasaction="ignore") writer.writeheader() for row in rows: writer.writerow({**records[row["client_key"]], **row}) dropped_path = args.out / "dropped.csv" with dropped_path.open("w", newline="", encoding="utf-8") as handle: writer = csv.DictWriter( handle, fieldnames=["sheet", "row", "name", "phone", "address", "reason"] ) writer.writeheader() writer.writerows(dropped) statuses = collections.Counter(row["job_status"] for row in rows) types = collections.Counter(row["job_type"] for row in rows) by_sheet = collections.Counter(row["sheet"] for row in rows) no_state = sum(1 for row in rows if not row["state"]) no_suburb = sum(1 for row in rows if not row["suburb"]) no_postcode = sum(1 for row in rows if not row["postcode"]) states_off_sheet = sum(1 for row in rows if row["state"] != SHEET_STATE.get(row["sheet"], "")) states_by_postcode = sum( 1 for row in rows if row["state"] == state_for_postcode(row["postcode"]) and row["state"] != SHEET_STATE.get(row["sheet"], "") ) feeble = [row for row in rows if not row["street_like"]] multi = [record for record in records.values() if record["jobs"] > 1] missing_email = sum(1 for row in rows if not row["email"]) report = { "workbook": str(args.workbook), "rows_kept": len(rows), "rows_dropped": len(dropped), "clients": len(records), "clients_with_multiple_installs": len(multi), "sites": len({row["site_key"] for row in rows}), "jobs_by_status": dict(statuses), "jobs_by_type": dict(types), "rows_by_sheet": dict(by_sheet), "rows_without_email": missing_email, "rows_without_state": no_state, "rows_whose_state_differs_from_their_sheet": states_off_sheet, "rows_whose_state_came_from_the_postcode": states_by_postcode, "rows_without_suburb": no_suburb, "rows_without_postcode": no_postcode, "rows_with_feeble_address": len(feeble), "feeble_address_examples": [ {"sheet": row["sheet"], "row": row["sheet_row"], "name": row["name"], "address": row["address_raw"]} for row in feeble[:25] ], "dropped_examples": dropped[:25], "merged_names": merges, "placeholder_names_resolved": adopted, "largest_clients": sorted( records.values(), key=lambda record: record["jobs"], reverse=True )[:15], } (args.out / "report.json").write_text(json.dumps(report, indent=2), encoding="utf-8") print(f"rows kept: {len(rows)}") print(f"rows dropped: {len(dropped)}") print(f"clients: {len(records)} ({len(multi)} with more than one install)") print(f"sites: {report['sites']}") print(f"name merges: {len(merges)}") print(f"placeholder names matched to a client by phone: {len(adopted)}") print(f"statuses: {dict(statuses)}") print(f"types: {dict(types)}") print(f"no email {missing_email}, no state {no_state}, no suburb {no_suburb}, " f"no postcode {no_postcode}, feeble address {len(feeble)}") print(f"state from postcode rather than the sheet: {states_by_postcode}" f" ({states_off_sheet} rows differ from their sheet)") print(f"wrote {csv_path}, {dropped_path}, {args.out / 'report.json'}") return 0 if __name__ == "__main__": raise SystemExit(main())