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.1.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.RunRowNumberIntegrityCheck()
    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()
Revision:	876
Committed:	Thu Jan 9 22:34:47 2025 UTC (14 months, 2 weeks ago) by nino.borges
Content type:	text/x-python
File size:	6849 byte(s)
Log Message:	Adding some notes as to what it does now and some version information because I'll be adding some additional features.
#	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	876	version = '0.1.0'
29
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
44			#nv.RunRowNumberIntegrityCheck()
45			xlApp = Dispatch('Excel.Application')
46			xlBook = xlApp.Workbooks.Open(masterAttorneyListFileName)
47			splitRoleMatrix = {}
48			for excelTab in excelTabParametersList:
49			hcRowNumberList = []
50			sht = xlBook.Worksheets(excelTab['tabName'])
51			print(f"Analyzing sheet {excelTab['tabName']}.")
52			excelFieldPositionMatrix = {}
53			for col in range (excelTab['beginColNumber'], excelTab['endColNumber'] +1):
54			excelFieldPositionMatrix[sht.Cells(1,col).Value] = col
55			for row in range(excelTab['beginRowNumber'], excelTab['endRowNumber'] +1):
56			if excelTab['tabName'] == 'Attorneys':
57			hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Row']).Value
58			## First start by grabbing the hard coded row number into a list that you can test. Also check for rows where this is empty.
59			if hcRowNumber == None:
60			print(f"WARNING: Empty hard coded row number for Excel row {row}.")
61			else:
62			hcRowNumberList.append(int(hcRowNumber))
63			## 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.
64			isAttorneyValue = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value
65			isAttorneyValue = isAttorneyValue.strip()
66			if isAttorneyValue.upper() == "YES":
67			pass
68			elif isAttorneyValue.upper() == "SPLIT ROLE":
69			if hcRowNumber in list(splitRoleMatrix.keys()):
70			print(f"ERROR: duplicate split role number found!")
71			else:
72
73			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()]
74			else:
75			print(f"ERROR: is attorney contains value that shouldnt be here, which is {isAttorneyValue.upper()}!!")
76			elif excelTab['tabName'] == 'Split Role Attorneys':
77			hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Attorney Row']).Value
78			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()]:
79			pass
80			else:
81			print(f"ERROR: {hcRowNumber} is pointing to a different attorney on the attorneys tab!! ")
82			elif excelTab['tabName'] == 'Downgrades':
83			hcRowNumber = sht.Cells(row,excelFieldPositionMatrix['Row']).Value
84			## First start by grabbing the hard coded row number into a list that you can test. Also check for rows where this is empty.
85			if hcRowNumber == None:
86			print(f"WARNING: Empty hard coded row number for Excel row {row}.")
87			else:
88			hcRowNumberList.append(int(hcRowNumber))
89			## Next verify that these all have their is attorney set to NO.
90			isAttorneyValue = sht.Cells(row,excelFieldPositionMatrix['Is Attorney']).Value.strip()
91			if isAttorneyValue.upper() == "NO":
92			pass
93			else:
94			print(f"ERROR: Excel row number {row} 'is attorney' contains value that shouldnt be here!!")
95
96			else:
97			print("ERROR")
98
99			## Now check for gaps and export a list of the missing numbers
100			print("Checking for number gaps in the hard coded row numbers...")
101			if hcRowNumberList:
102			hcRowNumberList.sort()
103			compareSet = set(range(hcRowNumberList[0], hcRowNumberList[-1]))
104			diffList = compareSet - set(hcRowNumberList)
105			if diffList:
106			print(diffList)
107			else:
108			print(f"There are no gaps in the hard coded row numbers in the {excelTab['tabName']} tab.")
109			else:
110			print(f"A hard coded row number analysis was not done for the {excelTab['tabName']} tab.")
111
112
113
114			xlBook.Close()