Active_prgs/Redgrave/NS_MasterAttorneyList.py

"""

NS_MasterAttorneyList

Created by:
Emanuel Borges
11.01.2025

A library for the creation and management of the Norfolk Southern MAL.

"""

import os, uuid, pickle, re
from dataclasses import dataclass, field, fields
from typing import List, Tuple, Optional
from collections import namedtuple
from win32com.client import Dispatch


version = "2.1"

@dataclass
class Person:
    first_name: Optional[str] = None
    last_name: Optional[str] = None
    alt_first_names: Optional[str] = None
    alt_surnames: Optional[str] = None
    ##  TODO: Make these email addresses lists instead of strings
    work_email_addresses: Optional[str] = None
    raw_email_addresses: Optional[str] = None
    _id: uuid.UUID = field(default_factory=uuid.uuid4)
    is_attorney: Optional[str] = None
    split_role_date_range: Optional[str] = None
    middle_initial: Optional[str] = None
    company: Optional[str] = None
    category: Optional[str] = None
    job_title: Optional[str] = None
    full_name_preferred: Optional[str] = None
    vendor_normalized_name: Optional[str] = None
    user_id: Optional[str] = None
    full_name_overide: Optional[str] = None
    ## Only gather unique_attorney_row_number from the attorney and split role attorney tabs.  NEVER from Non-Attorneys.
    unique_attorney_row_number:Optional[str] = None
    ##  Will be saving this as a list of tuple pairs (startdate,enddate). Allowing None for now but may update this to forcing an empty list, to avoid mutable default issues.
    dates_as_counsel:Optional[List[Tuple[str,str]]] = None

    def __post_init__(self):
        """Convert all string fields to uppercase."""
        if self.first_name:
            self.first_name = self.first_name.strip().upper()
        if self.alt_first_names:
            self.alt_first_names = self.alt_first_names.strip().upper()
        if self.alt_surnames:
            self.alt_surnames = self.alt_surnames.strip().upper()
        if self.last_name:
            self.last_name = self.last_name.strip().upper()
        if self.work_email_addresses:
            self.work_email_addresses = self.work_email_addresses.strip().upper()

        if self.raw_email_addresses:
            self.raw_email_addresses = self.raw_email_addresses.strip().upper()
        if self.is_attorney:
            self.is_attorney = self.is_attorney.strip().upper()
        if self.split_role_date_range:
            self.split_role_date_range = self.split_role_date_range.strip().upper()
        if self.middle_initial:
            self.middle_initial = self.middle_initial.strip().upper()
        if self.company:
            self.company = self.company.strip().upper()
        if self.category:
            self.category = self.category.strip().upper()
        if self.job_title:
            self.job_title = self.job_title.strip().upper()
        if self.full_name_preferred:
            self.full_name_preferred = self.full_name_preferred.strip().upper()
        if self.vendor_normalized_name:
            self.vendor_normalized_name = self.vendor_normalized_name.strip().upper()
        if self.user_id:
            self.user_id = self.user_id.strip().upper()


@dataclass
class PeopleList:
    people: List[Person] = field(default_factory=list)

    def add_person(self, person: Person):
        self.people.append(person)
        #print(f"Added person: {person}")


    def search_by_email(self, emailAddress:str) -> Optional[Person]:
        """Returns the first matching emailAddress value.  Assumes emailAddresses are unique"""
        for person in self.people:
            if person.work_email_addresses:
                #print(person.work_email_addresses)
                personEmailAddresses = person.work_email_addresses.split(";\n")
                personEmailAddresses = [x.strip() for x in personEmailAddresses]
                if emailAddress in personEmailAddresses:
                #if person.work_email_addresses == emailAddress:
                    return person
    ##            elif person.alt_work_email_addresses == emailAddress:
    ##                return person
        return None

    def search_by_login_id(self, loginID:str) -> Optional[Person]:
        """Returns the first matching login user id value.  Assumes login user ids are unique"""
        for person in self.people:
            if person.user_id:
               personLoginIds = person.user_id.split(";\n")
               personLoginIds = [x.strip() for x in personLoginIds]
               if loginID in personLoginIds:
                   return person
        return None

    def search_by_unique_attorney_row_number(self,uniqueAttorneyRowNumber:str) -> Optional[Person]:
        """Returns the first matching uniqueAttorneyRowNumber value.  Assumes uniqueAttorneyRowNumbers are unique"""
        for person in self.people:
            if person.unique_attorney_row_number == uniqueAttorneyRowNumber:
                return person
        return None

    def search_by_id(self, idNumber):
        """Returns the first matching idNumber value.  Must be in format UUID('7414f78c-8289-4c9f-bd49-a5aaac35545f')."""
        for person in self.people:
            if person._id == idNumber:
                return person
        return None

    def return_list_of_matching_values(self,fieldName, value:str):
        """Returns a full list of items where value is found in fieldName"""
        matchingPeopleList = []
        for person in self.people:
            personVals = getattr(person,fieldName)
            if personVals:
                personVals = personVals.split(";\n")
                personVals = [x.strip() for x in personVals]
                if value in personVals:
                    matchingPeopleList.append(person)
        return matchingPeopleList
        
    def list_people(self):
        for person in self.people:
            print(person)

    def update_full_Name_overide(self, emailAddress:str, fullNameOverideValue) -> Optional[Person]:
        valueUpdated = False
        for person in self.people:
            if person.work_email_addresses == emailAddress.upper():
                person.full_name_overide = fullNameOverideValue.upper()
                valueUpdated = True
                ## Give a quik warning as you add the override value into the database if the last name differs.
                if "," in fullNameOverideValue:
                    lastName = fullNameOverideValue.split(",")[0]
                else:
                    lastName = fullNameOverideValue.split(" ")[-1]
                if lastName.upper() == person.last_name:
                    pass
                else:
                    print(f"WARNING: Overide last name value {lastName.upper()} does not match {person.last_name}.")
        if valueUpdated == False:
            print(f"WARNING: No email address match for {emailAddress} found.")

    def return_person_all_name_variations(self, person):
        ## TODO: Fix this to support multiple alt first names and multiple surnames.
        """This will take a matched person and return a large list of all of the possible full name variations"""
        #last = person.last_name.strip() if person.last_name else None
        lasts = [person.last_name.strip() if person.last_name else None]
        if person.alt_surnames:
            lasts += person.alt_surnames.split(";\n")

        firsts = [person.first_name.strip() if person.first_name else None]
        if person.alt_first_names:
            #firsts.append(person.alt_first_names.strip())
            firsts += person.alt_first_names.split(";\n")

        middle = person.middle_initial.replace(".","").strip() if person.middle_initial else None

        combos = set() ## Using a set here to avoid dupes.

        for last in lasts:
            for first in firsts:
                ## Some basic combinations
                combos.add(f"{first} {last}")
                combos.add(f"{last} {first}")
                combos.add(f"{last}, {first}")

                ## Include middle initial variations if it exists
                if middle:
                    combos.add(f"{first} {middle} {last}")
                    combos.add(f"{last} {first} {middle}")
                    combos.add(f"{last}, {first} {middle}")
                    combos.add(f"{first} {middle}. {last}")
                    combos.add(f"{last} {first} {middle}.")
                    combos.add(f"{last}, {first} {middle}.")

        fNamePrefered = person.full_name_preferred
        if fNamePrefered:
            fNamePrefered = fNamePrefered.split(";\n")
            fNamePrefered = [x.strip() for x in fNamePrefered]
            combos.update(fNamePrefered)
        if person.vendor_normalized_name:
            combos.add(person.vendor_normalized_name.strip())
            ##  Want to add the vendor version of the name without the ESQ here.
            combos.add(person.vendor_normalized_name.upper().replace("(ESQ.)","").strip())
        return list(combos)

class NS_MasterAttorneyList(object):
    """A class for building and performing functions against the NS Master Attorney List."""
    version = '0.01.0'


    def __init__(self, masterAttorneyListFileName,fullNameOveridesFileName = False, forceNewPklFile = False, Encoding = 'UTF8'):
        """Assumes the MAL is a spreadsheet (for now).MAL gets saved to a pkl file for performance reasons.  pkl will be used unless forceNewPklFile is set to true"""
        pklFileName = os.path.splitext(masterAttorneyListFileName)[0] + ".pkl"

        print("Initializing data structures...")
        if forceNewPklFile:
            print("Creating MAL structure...")
            self.malPeopleList = PeopleList()
            self.__IngestMALSpreadsheet(masterAttorneyListFileName)
            print("MAL structure created.")
            if fullNameOveridesFileName:
                print("Loading full name overide values...")
                self.__LoadFullNameOverideValues(fullNameOveridesFileName)
                print("Full name overide values loaded.")
            print("Creating pickle backup...")
            self.__SaveMalToPkl(pklFileName)
            print("Pickle backup created.")
        else:
            if os.path.exists(pklFileName):
                print("Loading MAL structure from pickle file...")
                self.malPeopleList = self.__LoadMalFromPkl(pklFileName)
                print("MAL structure loaded.")
            else:
                print("Pickle file doesnt exist.")
                print("Creating MAL structure...")
                self.malPeopleList = PeopleList()
                self.__IngestMALSpreadsheet(masterAttorneyListFileName)
                print("MAL structure created.")
                if fullNameOveridesFileName:
                    print("Loading full name overide values...")
                    self.__LoadFullNameOverideValues(fullNameOveridesFileName)
                    print("Full name overide values loaded.")
                print("Creating pickle backup...")
                self.__SaveMalToPkl(pklFileName)
                print("Pickle backup created.")


    def __IngestMALSpreadsheet(self, masterAttorneyListFileName):
        """Pseudo-private method which will open an Excel spreadsheet and ingest the values into the peoplelist dataclass."""
        ## There doenst seem to be a consistent value in the "row" column in the MAL, so setting these parameters here to avoid gap issues.

        ##  excelTabParametersList should always be an ordered list because now order matters.
        excelTabParametersList = [{"tabName":"Attorneys", "beginRowNumber":2, "endRowNumber":215, "beginColNumber":1, "endColNumber":20},
                                  {"tabName":"Non-Attorneys", "beginRowNumber":2, "endRowNumber":19, "beginColNumber":1, "endColNumber":20},
                                  {"tabName":"Split Role Attorneys", "beginRowNumber":2, "endRowNumber":21, "beginColNumber":1, "endColNumber":10}]


 #       spreadsheetFileMappingMatrix = {"First Name":"first_name", "Last Name":"last_name", "Work Email":"work_email_address", "Alt Work Email":"alt_work_email_address", "Is Attorney": "is_attorney",
 #                                 "Split Role -  Attorney Capacity Date Range":"split_role_date_range", " Validated by OC??":"sidley_validated", "Category": "category", "Organization":"organization", "Job Title":"job_title",
 #                                 "Business Title":"business_title", "Full Name (Preferred)":"full_name_preferred", "Login":"login", "Department (Fine)":"department_fine", "Addressed during CAAG":"addressed_during_caag",
 #                                       "Last Updated":"last_updated"}

        xlApp = Dispatch('Excel.Application')
        xlBook = xlApp.Workbooks.Open(masterAttorneyListFileName)
        
        for excelTab in excelTabParametersList:
            sht = xlBook.Worksheets(excelTab['tabName'])
            print(f"Ingesting sheet {excelTab['tabName']}.")
            excelFieldPositionMatrix = {}
            for col in range (excelTab['beginColNumber'], excelTab['endColNumber'] +1):
                excelFieldPositionMatrix[sht.Cells(1,col).Value] = col
            for row in range(excelTab['beginRowNumber'], excelTab['endRowNumber'] +1):
                #print(row)
                ##  TODO: Refactor the excelTabParametersList later. Didnt realize columns were not consistent.
                if excelTab['tabName'] == 'Attorneys':
                    self.malPeopleList.add_person(Person(is_attorney = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value,
                                                         #split_role_date_range = sht.Cells(row,excelFieldPositionMatrix['Split Role - Dates as Counsel']).Value,
                                                         company = sht.Cells(row,excelFieldPositionMatrix['Company']).Value,
                                                         category = sht.Cells(row,excelFieldPositionMatrix['Category']).Value,
                                                         last_name = sht.Cells(row,excelFieldPositionMatrix['Last Name']).Value,
                                                         first_name = sht.Cells(row,excelFieldPositionMatrix['First Name']).Value,
                                                         alt_first_names = sht.Cells(row,excelFieldPositionMatrix['Alt First Names']).Value,
                                                         alt_surnames = sht.Cells(row,excelFieldPositionMatrix['Alt Surnames']).Value,
                                                         middle_initial = sht.Cells(row,excelFieldPositionMatrix['Middle Initial']).Value,
                                                         work_email_addresses = sht.Cells(row,excelFieldPositionMatrix['Email Addresses']).Value,
                                                         #alt_work_email_address = sht.Cells(row,excelFieldPositionMatrix['Alt Work Email']).Value,
                                                         raw_email_addresses = sht.Cells(row,excelFieldPositionMatrix['EmailAddress RAW']).Value,
                                                         job_title = sht.Cells(row,excelFieldPositionMatrix['Title']).Value,
                                                         full_name_preferred = sht.Cells(row,excelFieldPositionMatrix['Full Name (Preferred)']).Value,
                                                         vendor_normalized_name = sht.Cells(row,excelFieldPositionMatrix['Vendor Normalized Name']).Value,
                                                         user_id = sht.Cells(row,excelFieldPositionMatrix['UserID']).Value,
                                                         unique_attorney_row_number = sht.Cells(row,excelFieldPositionMatrix['MAL ID']).Value))
                
                elif excelTab['tabName'] == 'Non-Attorneys':
                    ## Make sure to NOT grab the unique attorney row number from here
                    self.malPeopleList.add_person(Person(is_attorney = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value,
                                                         #split_role_date_range = sht.Cells(row,excelFieldPositionMatrix['Split Role - Dates as Counsel']).Value,
                                                         company = sht.Cells(row,excelFieldPositionMatrix['Company']).Value,
                                                         category = sht.Cells(row,excelFieldPositionMatrix['Category']).Value,
                                                         last_name = sht.Cells(row,excelFieldPositionMatrix['Last Name']).Value,
                                                         first_name = sht.Cells(row,excelFieldPositionMatrix['First Name']).Value,
                                                         alt_first_names = sht.Cells(row,excelFieldPositionMatrix['Alt First Names']).Value,
                                                         alt_surnames = sht.Cells(row,excelFieldPositionMatrix['Alt Surnames']).Value,
                                                         middle_initial = sht.Cells(row,excelFieldPositionMatrix['Middle Initial']).Value,
                                                         work_email_addresses = sht.Cells(row,excelFieldPositionMatrix['Email Addresses']).Value,
                                                         #alt_work_email_address = sht.Cells(row,excelFieldPositionMatrix['Alt Work Email']).Value,
                                                         raw_email_addresses = sht.Cells(row,excelFieldPositionMatrix['EmailAddress RAW']).Value,
                                                         job_title = sht.Cells(row,excelFieldPositionMatrix['Title']).Value,
                                                         full_name_preferred = sht.Cells(row,excelFieldPositionMatrix['Full Name (Preferred)']).Value,
                                                         vendor_normalized_name = sht.Cells(row,excelFieldPositionMatrix['Vendor Normalized Name']).Value,
                                                         user_id = sht.Cells(row,excelFieldPositionMatrix['UserID']).Value,
                                                         unique_attorney_row_number = sht.Cells(row,excelFieldPositionMatrix['MAL ID']).Value))
                elif excelTab['tabName'] == 'Split Role Attorneys':
                    ##  Skip this tab for now.
                    pass
##                    unique_attorney_row_number = sht.Cells(row,excelFieldPositionMatrix['Attorney Row']).Value
##                    matchedPerson = self.malPeopleList.search_by_unique_attorney_row_number(unique_attorney_row_number)
##                    if matchedPerson:
##                        
##                        ##  dates_as_counsel should always be a two string value tuple (startdate,enddate).
##                        datesAsCounselValue = sht.Cells(row,excelFieldPositionMatrix['Dates as Counsel']).Value
##                        datesAsCounselList = []
##                        ##  First get rid of any extra data that is on a new line.  Note that they shouldnt be seperating the date ranges by newline.
##                        datesAsCounselValue = datesAsCounselValue.split("\n")[0]
##                        ##  Next split the ranges correctly by semicolon
##                        dateRanges = datesAsCounselValue.split(";")
##                        for dateRange in dateRanges:
##                            ##  Split out the start and end, allowing non-date words.  (current, present, etc) however force these to be uppercase.
##                            counselStartDate, counselEndDate = dateRange.split("-")
##                            counselStartDate = counselStartDate.upper().strip()
##                            counselEndDate = counselEndDate.upper().strip()
##                            datesAsCounselList.append((counselStartDate,counselEndDate))
##                        matchedPerson.dates_as_counsel = datesAsCounselList
                    
                else:
                    print(f"ERROR UNKNOWN TAB! {excelTab['tabName']}  HAVE NEEDED TAB NAMES CHANGED?")
        

        xlBook.Close()


    def __SaveMalToPkl(self, pklFileName):
        """Pseudo-private method which will save the current MAL people list object to a pkl file, for performance reasons."""
        outputFile = open(pklFileName,'wb')
        pickle.dump(self.malPeopleList,outputFile)
        outputFile.close()

    def __LoadMalFromPkl(self, pklFileName):
        """Pseudo-private method which will load a MAL people list object from a pkl file, for performance reasons."""
        contents = open(pklFileName, 'rb')
        obj = pickle.load(contents)
        contents.close()
        return obj

    def __LoadFullNameOverideValues(self, fullNameOveridesFileName):
        """Pseudo-private method which will update the MAL people list object with the full name overide values."""
        contents = open(fullNameOveridesFileName).readlines()
        for line in contents:
            line = line.replace("\n","")
            emailAddress,fullNameOverideValue = line.split("|")
            
            self.malPeopleList.update_full_Name_overide(emailAddress, fullNameOverideValue)

    def SmartDedupeSet(self, currentSet):
        """A method that attempts to do some additional deduplication of the values in a set by lowering all values and deduplicating.  Returns a lowered deduplicated set."""
        newSet = set()
        for val in currentSet:
            newSet.add(val.lower())
        return newSet


    def RunMalEmailAddressIntegrityCheck(self):
        """This method performs an integrity check on the MAL by analyzing and looking for duplicate email addresses."""
        emailTestMatrix = {}
        altTestMatrix = {}
        print("Performing MAL email address integrity check...")
        for i in range(0,len(self.malPeopleList.people)):
            #altAddr =  self.malPeopleList.people[i].alt_work_email_address
            altAddr =  None
            ##  Right now workaddrs are stored as a string that you need to parse to use.
            workAddrs = self.malPeopleList.people[i].work_email_addresses
            if altAddr != None:
                altAddr = altAddr.strip()
                if altAddr in list(emailTestMatrix.keys()):
                    print(f"ISSUE:{altAddr} is a dupe of an workAddr.")
                if altAddr in list(altTestMatrix.keys()):
                    print(f"ISSUE:{altAddr} is a dupe!")
                else:
                    altTestMatrix[altAddr] = 1
            if workAddrs != None:
                workAddrs = [w.upper().strip() for w in workAddrs.split(";\n")] 
                for workAddr in workAddrs:
                    workAddr = workAddr.strip()
                    if workAddr in list(altTestMatrix.keys()):
                        print(f"ISSUE:{workAddr} is a dupe of an altAddr.")
                    if workAddr in list(emailTestMatrix.keys()):
                        print(f"ISSUE:{workAddr} is a dupe!")
                    else:
                        emailTestMatrix[workAddr] = 1
        print("\nEmail address integrity check complete.\n\n")


    def RunMalLoginIdIntegrityCheck(self):
        """This method performs an integrity check on the MAL by analyzing and looking for duplicate User Login ID values."""
        loginIdTestMatrix = {}
        print("Performing MAL user login ID integrity check...")
        for i in range(0,len(self.malPeopleList.people)):
            loginIds = self.malPeopleList.people[i].user_id
            if loginIds != None:
                loginIds = [w.upper().strip() for w in loginIds.split(";\n")]
                for loginId in loginIds:
                    loginId = loginId.strip()
                    if loginId in list(loginIdTestMatrix.keys()):
                        print(f"ISSUE:{loginId} is a dupe!")
                    else:
                        loginIdTestMatrix[loginId] = 1
        print("\nUser Login ID integrity check complete.\n\n")


    def RunUniqueAttorneyRowNumberCheck(self):
        """Performs an integrity check on the unique_attorney_row_number calues, both for attorney and non-attorney"""
        attorneyUniqueNumberList = []
        non_attorneyUniqueNumberList = []
        print("Performing MAL Unique Attorney Row Number integrity check...")
        for i in range(0,len(self.malPeopleList.people)):
            unique_id = self.malPeopleList.people[i].unique_attorney_row_number
            if unique_id:
                if self.malPeopleList.people[i].is_attorney == "NO":
                    non_attorneyUniqueNumberList.append(int(unique_id))
                else:
                    attorneyUniqueNumberList.append(int(unique_id))
        non_attorneyUniqueNumberList.sort()
        attorneyUniqueNumberList.sort()
        print(f"{non_attorneyUniqueNumberList[0]}-{non_attorneyUniqueNumberList[-1]}")
        print(f"{attorneyUniqueNumberList[0]}-{attorneyUniqueNumberList[-1]}")
        
        def analyze_number_list(numbers):
            if not numbers:
                return [], []
            sorted_nums = sorted(numbers)
            duplicates = []
            gaps = []
            previous = sorted_nums[0]
            for current in sorted_nums[1:]:
                if current == previous:
                    duplicates.append(current)
                elif current > previous +1:
                    gaps.extend(range(previous +1, current))
                previous = current
            return gaps, duplicates


        ##  Check the attorney values first
        attorney_gaps, attorney_duplicates = analyze_number_list(attorneyUniqueNumberList)
        print("\nAttorney Unique Number Check")
        print ("-" *40)
        if attorney_gaps:
            print(f"Gaps found({len(attorney_gaps)}): {attorney_gaps}")
        else:
            print("No gaps found.")
        if attorney_duplicates:
            print(f"Duplicates Found({len(attorney_duplicates)}): {attorney_duplicates}")
        else:
            print("No duplicates found.")


        ##  Check non-attorney values
        non_attorney_gaps, non_attorney_duplicates = analyze_number_list(non_attorneyUniqueNumberList)
        print("\nNon-Attorney Unique Number Check")
        print ("-" *40)
        if non_attorney_gaps:
            print(f"Gaps found({len(non_attorney_gaps)}): {non_attorney_gaps}")
        else:
            print("No gaps found.")
        if non_attorney_duplicates:
            print(f"Duplicates Found({len(non_attorney_duplicates)}): {non_attorney_duplicates}")
        else:
            print("No duplicates found.")

if __name__ == '__main__':
    masterAttorneyListFileName = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Norfolk Southern\_NS_Current_MAL\RG - NS Cross-Matter Master Attorney List 20260120 (20260120-0136).xlsx"
    nsMal = NS_MasterAttorneyList(masterAttorneyListFileName)
    test = nsMal.malPeopleList.search_by_email('Catharine.Fletcher@nscorp.com'.upper())
    nsMal.malPeopleList.return_person_all_name_variations(test)
    test = nsMal.malPeopleList.search_by_email('mcarr@dmclaw.com'.upper())
    #test = attMal.malPeopleList.search_by_email('LMENRIQUEZ@DIRECTV.COM')
    #test = attMal.malPeopleList.return_list_of_matching_values('work_email_addresses','LMENRIQUEZ@DIRECTV.COM')
    #test = attMal.malPeopleList.search_by_login_id('JS6637')
    print(test)
    print(nsMal.malPeopleList.return_person_all_name_variations(test))
    nsMal.RunMalEmailAddressIntegrityCheck()
    nsMal.RunMalLoginIdIntegrityCheck()
    nsMal.RunUniqueAttorneyRowNumberCheck()
Revision:	982
Committed:	Tue Feb 24 22:43:35 2026 UTC (4 weeks, 3 days ago) by nino.borges
Content type:	text/x-python
File size:	28183 byte(s)
Log Message:	Added support to run a check on the MAL ID numbers. I dont use these but members of the team does, so I added this as a check.