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.


"""


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:	878
Committed:	Fri Jan 10 22:04:34 2025 UTC (14 months, 2 weeks ago) by nino.borges
Content type:	text/x-python
File size:	7254 byte(s)
Log Message:	Adds a new integrity check, by calling this from the Names Norm QC library, that will look for email addresses outside of email address fields in the MAL
#	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	870
18	nino.borges	876
19
20
21	nino.borges	870	"""
22
23
24			import os
25			import MyCode.Active_prgs.Redgrave.Amazon_NamesNormQC
26			from win32com.client import Dispatch
27
28	nino.borges	878	version = '0.2.0'
29	nino.borges	876
30	nino.borges	870	if __name__ == '__main__':
31			cleanedDatExportFileName = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Amazon\_PrivLogQCProcess\20241215\PrivLogExports\PrivLogExport_20241211_CAAG_Converted.txt"
32	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"
33	nino.borges	870	fullNameOveridesFileName = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Amazon\_PrivLogQCProcess\Consilio\CAAG-MasterAttorneyList\FullNameOverides.txt"
34
35			## TODO: Soon, change this to pulling it from the nv object.
36	nino.borges	876	excelTabParametersList = [{"tabName":"Attorneys", "beginRowNumber":2, "endRowNumber":10923, "beginColNumber":1, "endColNumber":17},
37			{"tabName":"Downgrades", "beginRowNumber":2, "endRowNumber":726, "beginColNumber":1, "endColNumber":16},
38	nino.borges	870	{"tabName":"Split Role Attorneys", "beginRowNumber":2, "endRowNumber":21, "beginColNumber":1, "endColNumber":10}]
39
40			nv = MyCode.Active_prgs.Redgrave.Amazon_NamesNormQC.NamesVerification(cleanedDatExportFileName, masterAttorneyListFileName, fullNameOveridesFileName)
41
42			nv.RunMalEmailAddressIntegrityCheck()
43	nino.borges	878	nv.RunMalEmailOutsideEmailFieldsIntegrityCheck()
44	nino.borges	870
45			#nv.RunRowNumberIntegrityCheck()
46	nino.borges	878	## print("Performing raw MAL spreadsheet integrity checks...")
47			## xlApp = Dispatch('Excel.Application')
48			## xlBook = xlApp.Workbooks.Open(masterAttorneyListFileName)
49			## splitRoleMatrix = {}
50			## for excelTab in excelTabParametersList:
51			## hcRowNumberList = []
52			## sht = xlBook.Worksheets(excelTab['tabName'])
53			## print(f"Analyzing sheet {excelTab['tabName']}.")
54			## excelFieldPositionMatrix = {}
55			## for col in range (excelTab['beginColNumber'], excelTab['endColNumber'] +1):
56			## excelFieldPositionMatrix[sht.Cells(1,col).Value] = col
57			## for row in range(excelTab['beginRowNumber'], excelTab['endRowNumber'] +1):
58			## if excelTab['tabName'] == 'Attorneys':
59			## hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Row']).Value
60			## ## First start by grabbing the hard coded row number into a list that you can test. Also check for rows where this is empty.
61			## if hcRowNumber == None:
62			## print(f"WARNING: Empty hard coded row number for Excel row {row}.")
63			## else:
64			## hcRowNumberList.append(int(hcRowNumber))
65			## ## 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.
66			## isAttorneyValue = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value
67			## isAttorneyValue = isAttorneyValue.strip()
68			## if isAttorneyValue.upper() == "YES":
69			## pass
70			## elif isAttorneyValue.upper() == "SPLIT ROLE":
71			## if hcRowNumber in list(splitRoleMatrix.keys()):
72			## print(f"ERROR: duplicate split role number found!")
73			## else:
74			##
75			## 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()]
76			## else:
77			## print(f"ERROR: is attorney contains value that shouldnt be here, which is {isAttorneyValue.upper()}!!")
78			## elif excelTab['tabName'] == 'Split Role Attorneys':
79			## hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Attorney Row']).Value
80			## 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()]:
81			## pass
82			## else:
83			## print(f"ERROR: {hcRowNumber} is pointing to a different attorney on the attorneys tab!! ")
84			## elif excelTab['tabName'] == 'Downgrades':
85			## hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Row']).Value
86			## ## First start by grabbing the hard coded row number into a list that you can test. Also check for rows where this is empty.
87			## if hcRowNumber == None:
88			## print(f"WARNING: Empty hard coded row number for Excel row {row}.")
89			## else:
90			## hcRowNumberList.append(int(hcRowNumber))
91			## ## Next verify that these all have their is attorney set to NO.
92			## isAttorneyValue = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value.strip()
93			## if isAttorneyValue.upper() == "NO":
94			## pass
95			## else:
96			## print(f"ERROR: Excel row number {row} 'is attorney' contains value that shouldnt be here!!")
97			##
98			## else:
99			## print("ERROR")
100			##
101			## ## Now check for gaps and export a list of the missing numbers
102			## print("Checking for number gaps in the hard coded row numbers...")
103			## if hcRowNumberList:
104			## hcRowNumberList.sort()
105			## compareSet = set(range(hcRowNumberList[0], hcRowNumberList[-1]))
106			## diffList = compareSet - set(hcRowNumberList)
107			## if diffList:
108			## print(diffList)
109			## else:
110			## print(f"There are no gaps in the hard coded row numbers in the {excelTab['tabName']} tab.")
111			## else:
112			## print(f"A hard coded row number analysis was not done for the {excelTab['tabName']} tab.")
113			##
114			##
115			##
116			## xlBook.Close()
117			##
118			## ## Now run the checks that do not require the spreadsheet
119			## print("\nRaw MAL spreadsheet integrity check complete.")
120	nino.borges	870
121
122	nino.borges	878