Active_prgs/Redgrave/Amazon_PerformMalIntegrityChecks.py

"""

Amazon_PerformMalIntegrityChecks

Created by:
Emanuel Borges
12.18.2024

This program performs the various integrity checks on the MAL.
Currently looks for:
Rows on any of the 3 tabs where the unique row number is empty.
Rows on any of the 3 tabs where the unique row number skips a number. (gap test) This is skipped for Split Role tab
Verifies that all rows on the Attorneys Tab has the Is Attorney set to either YES or Split Role.
Verifies that all rows on the Downgrades Tab has the Is Attorney set to NO.
Rows on the Attorneys tab where more than one Split Role row references a duplicate value. (two sep rows that point to the same unique row number) this shouldnt be the case unless there are duplicate row numbers
Verifies that the row in the split role tab is referencing the correct attorney row number in the Attorneys tab by comparing the firstname, lastname and work email value.
Looks for any duplicates in the emails, across the tabs and within, both within fields and across the two separate email fields.
Looks for any email addresses that exist outside of the email address fields.

"""


import os
import MyCode.Active_prgs.Redgrave.Amazon_NamesNormQC
from win32com.client import Dispatch

version = '0.2.0'

if __name__ == '__main__':
    cleanedDatExportFileName = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Amazon\_PrivLogQCProcess\20241215\PrivLogExports\PrivLogExport_20241211_CAAG_Converted.txt"
    masterAttorneyListFileName = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Amazon\_PrivLogQCProcess\Amazon_ Master Attorney List  2025.01.07(20250109-0241).xlsx"
    fullNameOveridesFileName = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Amazon\_PrivLogQCProcess\Consilio\CAAG-MasterAttorneyList\FullNameOverides.txt"

    ##  TODO: Soon, change this to pulling it from the nv object.
    excelTabParametersList = [{"tabName":"Attorneys", "beginRowNumber":2, "endRowNumber":10923, "beginColNumber":1, "endColNumber":17},
                              {"tabName":"Downgrades", "beginRowNumber":2, "endRowNumber":726, "beginColNumber":1, "endColNumber":16},                              
                              {"tabName":"Split Role Attorneys", "beginRowNumber":2, "endRowNumber":21, "beginColNumber":1, "endColNumber":10}]

    nv = MyCode.Active_prgs.Redgrave.Amazon_NamesNormQC.NamesVerification(cleanedDatExportFileName, masterAttorneyListFileName, fullNameOveridesFileName)
    
    nv.RunMalEmailAddressIntegrityCheck()
    nv.RunMalEmailOutsideEmailFieldsIntegrityCheck()

    #nv.RunRowNumberIntegrityCheck()
    print("Performing raw MAL spreadsheet integrity checks...")
    xlApp = Dispatch('Excel.Application')
    xlBook = xlApp.Workbooks.Open(masterAttorneyListFileName)
    splitRoleMatrix = {}
    for excelTab in excelTabParametersList:
        hcRowNumberList = []
        sht = xlBook.Worksheets(excelTab['tabName'])
        print(f"Analyzing 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):
            if excelTab['tabName'] == 'Attorneys':
                hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Row']).Value
                ##  First start by grabbing the hard coded row number into a list that you can test.  Also check for rows where this is empty.
                if hcRowNumber == None:
                    print(f"WARNING: Empty hard coded row number for Excel row {row}.")
                else:
                    hcRowNumberList.append(int(hcRowNumber))
                ##  Next verify that these all have their is attorney set to either yes or split role.  for split role, add to the matrix for testing when you get to that tab.
                isAttorneyValue = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value
                isAttorneyValue = isAttorneyValue.strip()
                if isAttorneyValue.upper() == "YES":
                    pass
                elif isAttorneyValue.upper() == "SPLIT ROLE":
                    if hcRowNumber in list(splitRoleMatrix.keys()):
                        print(f"ERROR: duplicate split role number found!")
                    else:
                        
                        splitRoleMatrix[hcRowNumber] = [sht.Cells(row,excelFieldPositionMatrix['Last Name']).Value.strip().upper(), sht.Cells(row,excelFieldPositionMatrix['First Name']).Value.strip().upper(), sht.Cells(row,excelFieldPositionMatrix['Work Email']).Value.strip().upper()]
                else:
                    print(f"ERROR: is attorney contains value that shouldnt be here, which is {isAttorneyValue.upper()}!!")
            elif excelTab['tabName'] == 'Split Role Attorneys':
                hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Attorney Row']).Value
                if splitRoleMatrix[hcRowNumber] == [sht.Cells(row,excelFieldPositionMatrix['Last Name']).Value.strip().upper(), sht.Cells(row,excelFieldPositionMatrix['First Name']).Value.strip().upper(), sht.Cells(row,excelFieldPositionMatrix['Work Email ']).Value.strip().upper()]:
                    pass
                else:
                    print(f"ERROR: {hcRowNumber} is pointing to a different attorney on the attorneys tab!! ")
            elif excelTab['tabName'] == 'Downgrades':
                hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Row']).Value
                ##  First start by grabbing the hard coded row number into a list that you can test.  Also check for rows where this is empty.
                if hcRowNumber == None:
                    print(f"WARNING: Empty hard coded row number for Excel row {row}.")
                else:
                    hcRowNumberList.append(int(hcRowNumber))
                ##  Next verify that these all have their is attorney set to NO.
                isAttorneyValue = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value.strip()
                if isAttorneyValue.upper() == "NO":
                    pass
                else:
                    print(f"ERROR: Excel row number {row} 'is attorney' contains value that shouldnt be here!!")
                
            else:
                print("ERROR")
        
        ##  Now check for gaps and export a list of the missing numbers
        print("Checking for number gaps in the hard coded row numbers...")
        if hcRowNumberList:
            hcRowNumberList.sort()
            compareSet = set(range(hcRowNumberList[0], hcRowNumberList[-1]))
            diffList = compareSet - set(hcRowNumberList)
            if diffList:
                print(diffList)
            else:
                print(f"There are no gaps in the hard coded row numbers in the {excelTab['tabName']} tab.")
        else:
            print(f"A hard coded row number analysis was not done for the {excelTab['tabName']} tab.")


    xlBook.Close()

    ##  Now run the checks that do not require the spreadsheet
    print("\nRaw MAL spreadsheet integrity check complete.")


Revision:	879
Committed:	Fri Jan 10 22:09:56 2025 UTC (14 months, 2 weeks ago) by nino.borges
Content type:	text/x-python
File size:	7309 byte(s)
Log Message:	Finished adding what the program checks for in the notes and uncommented manual section because I forgot to do that when I did my last commit.
#	User	Rev	Content
1	nino.borges	870	"""
2
3			Amazon_PerformMalIntegrityChecks
4
5			Created by:
6			Emanuel Borges
7			12.18.2024
8
9			This program performs the various integrity checks on the MAL.
10	nino.borges	876	Currently looks for:
11			Rows on any of the 3 tabs where the unique row number is empty.
12			Rows on any of the 3 tabs where the unique row number skips a number. (gap test) This is skipped for Split Role tab
13			Verifies that all rows on the Attorneys Tab has the Is Attorney set to either YES or Split Role.
14			Verifies that all rows on the Downgrades Tab has the Is Attorney set to NO.
15			Rows on the Attorneys tab where more than one Split Role row references a duplicate value. (two sep rows that point to the same unique row number) this shouldnt be the case unless there are duplicate row numbers
16			Verifies that the row in the split role tab is referencing the correct attorney row number in the Attorneys tab by comparing the firstname, lastname and work email value.
17	nino.borges	879	Looks for any duplicates in the emails, across the tabs and within, both within fields and across the two separate email fields.
18			Looks for any email addresses that exist outside of the email address fields.
19	nino.borges	870
20			"""
21
22
23			import os
24			import MyCode.Active_prgs.Redgrave.Amazon_NamesNormQC
25			from win32com.client import Dispatch
26
27	nino.borges	878	version = '0.2.0'
28	nino.borges	876
29	nino.borges	870	if __name__ == '__main__':
30			cleanedDatExportFileName = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Amazon\_PrivLogQCProcess\20241215\PrivLogExports\PrivLogExport_20241211_CAAG_Converted.txt"
31	nino.borges	876	masterAttorneyListFileName = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Amazon\_PrivLogQCProcess\Amazon_ Master Attorney List 2025.01.07(20250109-0241).xlsx"
32	nino.borges	870	fullNameOveridesFileName = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Amazon\_PrivLogQCProcess\Consilio\CAAG-MasterAttorneyList\FullNameOverides.txt"
33
34			## TODO: Soon, change this to pulling it from the nv object.
35	nino.borges	876	excelTabParametersList = [{"tabName":"Attorneys", "beginRowNumber":2, "endRowNumber":10923, "beginColNumber":1, "endColNumber":17},
36			{"tabName":"Downgrades", "beginRowNumber":2, "endRowNumber":726, "beginColNumber":1, "endColNumber":16},
37	nino.borges	870	{"tabName":"Split Role Attorneys", "beginRowNumber":2, "endRowNumber":21, "beginColNumber":1, "endColNumber":10}]
38
39			nv = MyCode.Active_prgs.Redgrave.Amazon_NamesNormQC.NamesVerification(cleanedDatExportFileName, masterAttorneyListFileName, fullNameOveridesFileName)
40
41			nv.RunMalEmailAddressIntegrityCheck()
42	nino.borges	878	nv.RunMalEmailOutsideEmailFieldsIntegrityCheck()
43	nino.borges	870
44			#nv.RunRowNumberIntegrityCheck()
45	nino.borges	879	print("Performing raw MAL spreadsheet integrity checks...")
46			xlApp = Dispatch('Excel.Application')
47			xlBook = xlApp.Workbooks.Open(masterAttorneyListFileName)
48			splitRoleMatrix = {}
49			for excelTab in excelTabParametersList:
50			hcRowNumberList = []
51			sht = xlBook.Worksheets(excelTab['tabName'])
52			print(f"Analyzing sheet {excelTab['tabName']}.")
53			excelFieldPositionMatrix = {}
54			for col in range (excelTab['beginColNumber'], excelTab['endColNumber'] +1):
55			excelFieldPositionMatrix[sht.Cells(1,col).Value] = col
56			for row in range(excelTab['beginRowNumber'], excelTab['endRowNumber'] +1):
57			if excelTab['tabName'] == 'Attorneys':
58			hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Row']).Value
59			## First start by grabbing the hard coded row number into a list that you can test. Also check for rows where this is empty.
60			if hcRowNumber == None:
61			print(f"WARNING: Empty hard coded row number for Excel row {row}.")
62			else:
63			hcRowNumberList.append(int(hcRowNumber))
64			## Next verify that these all have their is attorney set to either yes or split role. for split role, add to the matrix for testing when you get to that tab.
65			isAttorneyValue = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value
66			isAttorneyValue = isAttorneyValue.strip()
67			if isAttorneyValue.upper() == "YES":
68			pass
69			elif isAttorneyValue.upper() == "SPLIT ROLE":
70			if hcRowNumber in list(splitRoleMatrix.keys()):
71			print(f"ERROR: duplicate split role number found!")
72			else:
73
74			splitRoleMatrix[hcRowNumber] = [sht.Cells(row,excelFieldPositionMatrix['Last Name']).Value.strip().upper(), sht.Cells(row,excelFieldPositionMatrix['First Name']).Value.strip().upper(), sht.Cells(row,excelFieldPositionMatrix['Work Email']).Value.strip().upper()]
75			else:
76			print(f"ERROR: is attorney contains value that shouldnt be here, which is {isAttorneyValue.upper()}!!")
77			elif excelTab['tabName'] == 'Split Role Attorneys':
78			hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Attorney Row']).Value
79			if splitRoleMatrix[hcRowNumber] == [sht.Cells(row,excelFieldPositionMatrix['Last Name']).Value.strip().upper(), sht.Cells(row,excelFieldPositionMatrix['First Name']).Value.strip().upper(), sht.Cells(row,excelFieldPositionMatrix['Work Email ']).Value.strip().upper()]:
80			pass
81			else:
82			print(f"ERROR: {hcRowNumber} is pointing to a different attorney on the attorneys tab!! ")
83			elif excelTab['tabName'] == 'Downgrades':
84			hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Row']).Value
85			## First start by grabbing the hard coded row number into a list that you can test. Also check for rows where this is empty.
86			if hcRowNumber == None:
87			print(f"WARNING: Empty hard coded row number for Excel row {row}.")
88			else:
89			hcRowNumberList.append(int(hcRowNumber))
90			## Next verify that these all have their is attorney set to NO.
91			isAttorneyValue = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value.strip()
92			if isAttorneyValue.upper() == "NO":
93			pass
94			else:
95			print(f"ERROR: Excel row number {row} 'is attorney' contains value that shouldnt be here!!")
96
97			else:
98			print("ERROR")
99
100			## Now check for gaps and export a list of the missing numbers
101			print("Checking for number gaps in the hard coded row numbers...")
102			if hcRowNumberList:
103			hcRowNumberList.sort()
104			compareSet = set(range(hcRowNumberList[0], hcRowNumberList[-1]))
105			diffList = compareSet - set(hcRowNumberList)
106			if diffList:
107			print(diffList)
108			else:
109			print(f"There are no gaps in the hard coded row numbers in the {excelTab['tabName']} tab.")
110			else:
111			print(f"A hard coded row number analysis was not done for the {excelTab['tabName']} tab.")
112	nino.borges	870
113
114	nino.borges	879
115			xlBook.Close()
116
117			## Now run the checks that do not require the spreadsheet
118			print("\nRaw MAL spreadsheet integrity check complete.")
119
120
121	nino.borges	878