Active_prgs/Evidox/EvidoxJSParser.py

"""

EvidoxJSParser

Created by
Emanuel Borges
03.20.2018

A program or just set of methods that will parse and create a new report from the JS.


"""

from win32com.client import Dispatch
import os, datetime
import pickle as pickle

def GatherColumnTableEnd(sheet, rowStart, colStart):
    """Takes a JS sheet and returns the start and end range of what has data in it."""
    right = colStart
    while sheet.Cells(rowStart, right + 1).Value not in [None,""]:
        right = right +1

    colEnd = right

    return colStart, colEnd

def GatherRowTableEnd(sheet, rowStart, col):
    """using only the col field, will test all rows, starting at rowstart and returns the end row"""
    bottom = rowStart
    while sheet.Cells(bottom + 1, col).Value not in [None, '']:
        bottom = bottom +1

    rowEnd = bottom
    return rowEnd

def ProcessSheet(sheetObj, acceptedFieldsList, acceptedHeadderColor, mediaLogHeadderRow, targetxlBook, targetSheetNumb, caseName, rowAboveOveride = False, skipIfEmpty = False):
    """The main processor that takes a sheet and processes it into a new sheet.  Returns 1 if sucesful and None if not."""
    mediaLogJobNumberColumn = None
    mediaLogColSizeMatrix = {}
    
    ##  Unhide all hidden rows.  Set to 10,000 rows for now.
    sheetObj.Rows('1:10000').Hidden = False
    sheetObj.Rows.AutoFit()
    
    ##  Get a list of the colums that you should grab based on the testing
    sheetFieldsToGrab = []
    colStart,colEnd = GatherColumnTableEnd(sheetObj,mediaLogHeadderRow,1)

    ##  Having an issue where the mediaheadderrow is actually one lower.  Trying to fix this by testing to see how many columns were returned and if less than 3, retry 1 row higher.

    totalColTest = colEnd - colStart
    print("colstartend = %s, %s"%(colStart,colEnd))
    print("totalcol is %s"% totalColTest)
    if totalColTest < 4:
        print("Column count was very low, so trying a higher head row number.")
        mediaLogHeadderRow = mediaLogHeadderRow + 1
        colStart,colEnd = GatherColumnTableEnd(sheetObj,mediaLogHeadderRow,1)

    
    for i in range(colEnd):
        i = i + 1
        #print "Testing %s"%i
        #print "color is %d"% sht.Cells(mediaLogHeadderRow,i).Interior.ColorIndex
        ##  First test the color
        if sheetObj.Cells(mediaLogHeadderRow,i).Interior.ColorIndex == acceptedHeadderColor:
            ##  Then test that the test is in the approved list
            cellText = sheetObj.Cells(mediaLogHeadderRow,i).Value
            if cellText in acceptedFieldsList:
                sheetFieldsToGrab.append(i)
                mediaLogColSizeMatrix[i] = sheetObj.Cells(mediaLogHeadderRow,i).ColumnWidth
                ##  Try to find the Job Number field too
                if cellText == acceptedFieldsList[0]:
                    mediaLogJobNumberColumn = i
    print(sheetFieldsToGrab)
    
    ##  Using the jobNumber column, gather the highest row number
    if mediaLogJobNumberColumn:
        rowEnd = GatherRowTableEnd(sheetObj, mediaLogHeadderRow, mediaLogJobNumberColumn)
        print("End row is %d"% rowEnd)
        skipIt = False
        if skipIfEmpty:
            ##  With this flag, if the rows are nothing but a headder row, skip it.  magic number for the rowaboveoveride seems to be 4 when empty
            if rowAboveOveride:
                if rowEnd < 5:
                    ## it's headder row only, so set skipIt to True
                    skipIt = True
        if skipIt:
            print("sheet basically empty.  Skipping it.")
            return None
        else:
            #targetxlBook = xlApp.Workbooks.Add()
            print("sheet numb will be %d"%targetSheetNumb)
            ##  Turns out you have to add sheets over the default 3 you get with a new WB
            if targetSheetNumb > 3:
                targetxlBook.Worksheets.Add(Before=None, After=targetxlBook.Worksheets(targetxlBook.Worksheets.Count))
                
            targetSht = targetxlBook.Worksheets(targetSheetNumb)
            
            ##  First let's set a starting column on the target
            targetColumnStart = 2
            
            if rowAboveOveride:
                mediaLogHeadderRow = mediaLogHeadderRow -1
            for fieldNumber in sheetFieldsToGrab:
                targetMediaLogHeadderRow = mediaLogHeadderRow-mediaLogHeadderRow +3
                targetRowEnd = rowEnd-mediaLogHeadderRow + 1
                sheetObj.Range(sheetObj.Cells(mediaLogHeadderRow,fieldNumber),sheetObj.Cells(rowEnd,fieldNumber)).Copy(targetSht.Range(targetSht.Cells(targetMediaLogHeadderRow,targetColumnStart),targetSht.Cells(targetRowEnd,targetColumnStart)))
                #targetSht.Columns.AutoFit()
                targetSht.Cells(targetMediaLogHeadderRow,targetColumnStart).ColumnWidth = mediaLogColSizeMatrix[fieldNumber]
                
                targetColumnStart = targetColumnStart + 1
                
            ##  Set the pretty case name at the top of the media log
            targetSht.Cells(1,2).Value = "%s %s"% (caseName, sheetObj.Name)
            targetSht.Cells(1,2).Font.Name = "Calibri"
            targetSht.Cells(1,2).Font.Size = 16
            targetSht.Name = sheetObj.Name
            return 1
    else:
        print("ERROR: Not able to locate job number column!!")
        return None

if __name__ == '__main__':

    version = 'v1.06'

    jsPickleFile = r"C:\Dev\Site\TRON-Conscripts\ReportsToCreate.pkl"

    xlApp = Dispatch('Excel.Application')
    
    ##  Turn off any messages about saving over existing target files.
    xlApp.DisplayAlerts = False
    

    ## matrix of casename, (sourceJS, targetJS)
##    reportsToCreate = {'Bielins - Bennardo':
##                       (r"C:\Test-PY\liveTest\Bielins-Bennardo-Job Summary.xlsx",r"C:\Test-PY\liveTest\Bielins - BennardoTESTJSLiveReport.xlsx"),
##                       'SharkNinja - Keurig':
##                       (r"C:\Test-PY\liveTest\SharkNinja-Keurig-Job Summary.xlsx",r"C:\Test-PY\liveTest\SharkNinja - KeurigTESTJSLiveReport.xlsx"),
##                       'Dairy Farmers-Litigation':
##                       (r"C:\Test-PY\liveTest\Dairy Farmers-Litigation-Job Summary.xlsx",r"C:\Test-PY\liveTest\Dairy Farmers-LitigationTESTJSLiveReport.xlsx")}

##    reportsToCreate = {'I Grace Productions':
##                       (r"\\iadcifs01\job\Lamb Barnosky\I Grace Productions\I Grace Productions--Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\I Grace Productions-JS-LiveReport.xlsx"),
##                       'McLaughlin Northrup - McDonald Fracassa':
##                       (r"\\iadcifs01\job\Murphy King\McLaughlin Northup-McDonald Fracassa\McLaughlin Northrup-McDonald Fracassa-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\McLaughlin Northrup - McDonald Fracassa-JS-LiveReport.xlsx"),
##                       'Dairy Farmers - Litigation':
##                       (r"\\iadcifs01\job\Nystrom Beckman\Dairy Farmers-Litigation\Dairy Farmers-Litigation-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Dairy Farmers-Litigation-JS-LiveReport.xlsx"),
##                       'Glassman - Metropolitan':
##                       (r"\\iadcifs01\job\PollackSD\Glassman-Metropolitan\Glassman-Metropolitan-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Glassman-Metropolitan-JS-LiveReport.xlsx"),
##                       'Emilfarb - DOJ Subpoena':
##                       (r"\\iadcifs01\job\Libby Hoopes\Emilfarb-DOJ Subpoena\Emilfarb-DOJ Subpoena-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Emilfarb-DOJ Subpoena-JS-LiveReport.xlsx"),
##                       'Bielins - Bennardo':
##                       (r"\\Iadcifs01\job\Lamb Barnosky\Gary Bielins-Bennardo\Bielins-Bennardo-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Bielins-Bennardo-JS-LiveReport.xlsx"),
##                       'Bulger Capital Partners - Jenzabar Subpoena':
##                       (r"\\iadcifs01\job\PollackSD\Bulger Capital Partners-Jenzabar Subpoena\Bulger Capital Partners-Jenzabar Subpoena-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Bulger Capital Partners-Jenzabar Subpoena-JS-LiveReport.xlsx"),
##                       'Schneider Electric - Perkins':
##                       (r"\\iadcifs01\job\Duffy\Schneider Electric-Perkins Will\Schneider Electric-Perkins Will-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Schneider Electric-Perkins Will-JS-LiveReport.xlsx")
##                       
##                       }


##    reportsToCreate = {'Legacy Global - Internal':
##                       (u'\\\\iadcifs01\\job\\PollackSD\\Legacy Global-Internal\\Legacy Global-Internal-Job Summary.xlsx', u'L:\\__People\\Emanuel\\MCP3_Temp\\Legacy Global-Internal-JS-LiveReport.xlsx'),
##                       'Epic Genetics - Rodrigues':
##                       (u'\\\\iadcifs01\\job\\Hirsch Roberts Weinstein\\Epic Genetics-Rodrigues\\Epic Genetics-Rodrigues-JobSummary.xlsx', u'L:\\__People\\Emanuel\\MCP3_Temp\\Epic Genetics-Rodrigues-JobSummary.xlsx')
##                       }
    

    with open(jsPickleFile, 'rb') as input:
        reportsToCreate = pickle.load(input)

    acceptedSheetNames = ['Media Log', 'Media Log-XEDD Processed' , 'XEDD-Processing-Eclipse', 'Processing','Processing-Eclipse','Processing-LAW','Processing-Legacy','Processing-ADD', 'ADD Processing','Outgoing Productions','Incoming Productions']
    
    acceptedMediaLogFieldsList = ['Evidox Job Number', 'Evidox Media ID', 'Media Type', 'Disk Label - Folder / File name', 'Total File Count (# of Files Received)', 'Extracted Filesize (GB)', 'Serial Number', 'Notes', 'Custodian(s)\nREQUIRED','Custodian(s)','Party', 'Media Received', 'Media Returned']
    acceptedMediaLogHeadderColor = 2
    mediaLogHeadderRow = 15

    acceptedOProdFieldsList = ['Evidox Job Number', 'Source / Criteria', 'Total Docs Requested for Production', 'Total Exclusions', 'Total Docs Produced','Total Docs Produced ','Total Docs Produced with Images','Total Pages Produced with Images','Total Docs Produced Natively','Bates Range','Docs Requiring','Total Pages','Unsupported Docs with Placeholders','Native Docs with Placeholders','Docs Requiring','Docs Errored','Total Docs','Total Pages','Opposing Production Volume','Opposing Native Production Volume','Opposing Native Production Volume ','Client Volume','Date Delivered','Notes']
    acceptedOProdHeadderColor = 20
    oProdHeadderRow = 3

    acceptedIProdFieldsList = ['Evidox Job Number','Evidox Media ID','Total Docs Received','Total Images / Pages Received','Total Text Files Received','Total Natives Received','Bates / Production Control Number Range(s) ','Load Files Received (list extensions)','Metadata Received (Y/N)','Attachment Info Received (Y/N)','Volume','Date Delivered','Docs Requiring','Docs Errored / Excluded','Total Docs','Total Pages','Docs Requiring','Docs Errored / Excluded','Total Docs','Total Pages']
    acceptedIProdHeadderColor = 40
    iProdHeadderRow = 3

    ##  all processing tabs will use these came settings.
    acceptedProcFieldsList = ['Evidox Job Number','Evidox Media ID','Precull','GB','Total Docs Received','Duplicates','Excluded / Errored','Total Docs (Minus Dups and Excluded)','Total Unique Search Hits ','In As Family','Family Excluded (Corrupt)','Total Search Hits','Total Docs Delivered','Total Pages Delivered (Images or Scans)','Control Nos. / DocIDs','Docs Requiring','Docs Errored / Excluded','Total Docs','Total Pages','Docs Requiring','Docs Errored / Excluded','Total Docs','Total Pages','Volume','Date Delivered','Notes','Custodian(s)','GB Ingested','Filtered/\nExcluded','Total Pages  (Images or Scans)','EVDXID Range']
    acceptedProcHeadderColor = 19
    procHeadderRow = 3


    for reportCaseItem in list(reportsToCreate.keys()):
        caseName = reportCaseItem
        sourceSpreadsheetPath,targetSpreadsheet = reportsToCreate[reportCaseItem]

        if os.path.exists(sourceSpreadsheetPath):

            print("\n\nPerforming modDate test on %s..."%caseName)
            sourceModifiedDTT = os.stat(sourceSpreadsheetPath)[8]
            if os.path.exists(targetSpreadsheet):
                targetModifiedDTT = os.stat(targetSpreadsheet)[8]
            else:
                targetModifiedDTT = 0
            if sourceModifiedDTT > targetModifiedDTT:
                print("Source has been modified.")

                print("Now processing %s..."% caseName)

                xlBook = xlApp.Workbooks.Open(sourceSpreadsheetPath)
                #xlBook = xlApp.Workbooks.Open(r"\\iadcifs01\job\Lamb Barnosky\I Grace Productions\I Grace Productions--Job Summary.xlsx")
                #targetSpreadsheet = r"C:\Test-PY\liveTest\OutputJSReport.xlsx"

                ##  First lets create a matrix of the sheets to be grabbed from this source
                acceptedSheetMatrix = {}
                for i in range(xlBook.Sheets.Count):
                    if xlBook.Sheets(i+1).Name in acceptedSheetNames:
                        acceptedSheetMatrix[xlBook.Sheets(i+1).Name] = i+1
                if acceptedSheetMatrix:
                    ##  Only save if at least one of the sheets get created.
                    atleastOneSheetCreated = False
                    
                    targetSheetNumb = 1
                    targetxlBook = xlApp.Workbooks.Add()
                    #for sheetName in acceptedSheetMatrix.keys():
                    ## Eventually make this an object but for now you need control over the properties passed to ProcessSheet and cant just loop through
                    if 'Media Log' in list(acceptedSheetMatrix.keys()):
                        print("media Log")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Media Log']), acceptedMediaLogFieldsList, acceptedMediaLogHeadderColor, mediaLogHeadderRow,targetxlBook, targetSheetNumb, caseName)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True
                            
                    if 'Media Log-XEDD Processed' in list(acceptedSheetMatrix.keys()):
                        print("Media Log-XEDD Processed")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Media Log-XEDD Processed']), acceptedMediaLogFieldsList, acceptedMediaLogHeadderColor, mediaLogHeadderRow,targetxlBook, targetSheetNumb, caseName)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True
                            
                    if 'Outgoing Productions' in list(acceptedSheetMatrix.keys()):
                        print("outgoing productions")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Outgoing Productions']), acceptedOProdFieldsList, acceptedOProdHeadderColor, oProdHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1 :
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

                    if 'Incoming Productions' in list(acceptedSheetMatrix.keys()):
                        print("Incoming Productions")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Incoming Productions']), acceptedIProdFieldsList, acceptedIProdHeadderColor, iProdHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

                    if 'Processing' in list(acceptedSheetMatrix.keys()):
                        print("Processing")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

                    if 'Processing-Eclipse' in list(acceptedSheetMatrix.keys()):
                        print("Processing-Eclipse")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing-Eclipse']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

                    if 'Processing-LAW' in list(acceptedSheetMatrix.keys()):
                        print("Processing-LAW")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing-LAW']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

                    if 'Processing-Legacy' in list(acceptedSheetMatrix.keys()):
                        print("Processing-Legacy")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing-Legacy']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

                    if 'Processing-ADD' in list(acceptedSheetMatrix.keys()):
                        print("Processing-ADD")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing-ADD']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

                    if 'ADD Processing' in list(acceptedSheetMatrix.keys()):
                        print("ADD Processing")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['ADD Processing']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

                    if 'XEDD-Processing-Eclipse' in list(acceptedSheetMatrix.keys()):
                        print("XEDD-Processing-Eclipse")
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['XEDD-Processing-Eclipse']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

                    if atleastOneSheetCreated:
                        print("Trying to save as %s"%targetSpreadsheet)
                        targetxlBook.SaveAs(targetSpreadsheet)
                        targetxlBook.Close()
                    else:
                        targetxlBook.Close(SaveChanges=False)
                print("%s Processed.  Closing that spreadsheet."% caseName)
                xlBook.Close(SaveChanges=False)
            else:
                print("Source hasnt been modified. Skipping.")


        else:
            errFile = open(r"\\Xiprofsrvw01\xiprofsrvw01\Admin\MCP\MCP3_Temp\errlog.txt",'a')
            errFile.write("%s :: %s does not exist\n"% (datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d %H:%M'),sourceSpreadsheetPath))
            errFile.close()
        #sht = xlBook.Worksheets(1)
        #acceptedFieldsList = [u'Evidox Job Number', u'Evidox Media ID', u'Media Type', u'Disk Label - Folder / File name', u'Total File Count (# of Files Received)', u'Extracted Filesize (GB)', u'Serial Number', u'Notes', u'Custodian(s)\nREQUIRED', u'Media Received', u'Media Returned']
        #acceptedHeadderColor = 2
        
        #mediaLogHeadderRow = 15
        
            
Revision:	746
Committed:	Thu Apr 15 20:11:16 2021 UTC (4 years, 11 months ago) by nino.borges
Content type:	text/x-python
File size:	21059 byte(s)
Log Message:	Updated to be compatible with python3, which made the bak files.
#	User	Rev	Content
1	nino.borges	633	"""
2
3			EvidoxJSParser
4
5			Created by
6			Emanuel Borges
7			03.20.2018
8
9			A program or just set of methods that will parse and create a new report from the JS.
10
11
12			"""
13
14			from win32com.client import Dispatch
15	nino.borges	666	import os, datetime
16	nino.borges	746	import pickle as pickle
17	nino.borges	633
18			def GatherColumnTableEnd(sheet, rowStart, colStart):
19			"""Takes a JS sheet and returns the start and end range of what has data in it."""
20			right = colStart
21			while sheet.Cells(rowStart, right + 1).Value not in [None,""]:
22			right = right +1
23
24			colEnd = right
25
26			return colStart, colEnd
27
28			def GatherRowTableEnd(sheet, rowStart, col):
29			"""using only the col field, will test all rows, starting at rowstart and returns the end row"""
30			bottom = rowStart
31			while sheet.Cells(bottom + 1, col).Value not in [None, '']:
32			bottom = bottom +1
33
34			rowEnd = bottom
35			return rowEnd
36
37	nino.borges	666	def ProcessSheet(sheetObj, acceptedFieldsList, acceptedHeadderColor, mediaLogHeadderRow, targetxlBook, targetSheetNumb, caseName, rowAboveOveride = False, skipIfEmpty = False):
38	nino.borges	647	"""The main processor that takes a sheet and processes it into a new sheet. Returns 1 if sucesful and None if not."""
39			mediaLogJobNumberColumn = None
40			mediaLogColSizeMatrix = {}
41
42			## Unhide all hidden rows. Set to 10,000 rows for now.
43			sheetObj.Rows('1:10000').Hidden = False
44			sheetObj.Rows.AutoFit()
45
46			## Get a list of the colums that you should grab based on the testing
47			sheetFieldsToGrab = []
48			colStart,colEnd = GatherColumnTableEnd(sheetObj,mediaLogHeadderRow,1)
49	nino.borges	666
50			## Having an issue where the mediaheadderrow is actually one lower. Trying to fix this by testing to see how many columns were returned and if less than 3, retry 1 row higher.
51
52			totalColTest = colEnd - colStart
53	nino.borges	746	print("colstartend = %s, %s"%(colStart,colEnd))
54			print("totalcol is %s"% totalColTest)
55	nino.borges	666	if totalColTest < 4:
56	nino.borges	746	print("Column count was very low, so trying a higher head row number.")
57	nino.borges	666	mediaLogHeadderRow = mediaLogHeadderRow + 1
58			colStart,colEnd = GatherColumnTableEnd(sheetObj,mediaLogHeadderRow,1)
59
60
61
62	nino.borges	647	for i in range(colEnd):
63			i = i + 1
64			#print "Testing %s"%i
65			#print "color is %d"% sht.Cells(mediaLogHeadderRow,i).Interior.ColorIndex
66			## First test the color
67			if sheetObj.Cells(mediaLogHeadderRow,i).Interior.ColorIndex == acceptedHeadderColor:
68			## Then test that the test is in the approved list
69			cellText = sheetObj.Cells(mediaLogHeadderRow,i).Value
70			if cellText in acceptedFieldsList:
71			sheetFieldsToGrab.append(i)
72			mediaLogColSizeMatrix[i] = sheetObj.Cells(mediaLogHeadderRow,i).ColumnWidth
73			## Try to find the Job Number field too
74			if cellText == acceptedFieldsList[0]:
75			mediaLogJobNumberColumn = i
76	nino.borges	746	print(sheetFieldsToGrab)
77	nino.borges	647
78			## Using the jobNumber column, gather the highest row number
79			if mediaLogJobNumberColumn:
80			rowEnd = GatherRowTableEnd(sheetObj, mediaLogHeadderRow, mediaLogJobNumberColumn)
81	nino.borges	746	print("End row is %d"% rowEnd)
82	nino.borges	666	skipIt = False
83			if skipIfEmpty:
84			## With this flag, if the rows are nothing but a headder row, skip it. magic number for the rowaboveoveride seems to be 4 when empty
85			if rowAboveOveride:
86			if rowEnd < 5:
87			## it's headder row only, so set skipIt to True
88			skipIt = True
89			if skipIt:
90	nino.borges	746	print("sheet basically empty. Skipping it.")
91	nino.borges	666	return None
92			else:
93			#targetxlBook = xlApp.Workbooks.Add()
94	nino.borges	746	print("sheet numb will be %d"%targetSheetNumb)
95	nino.borges	666	## Turns out you have to add sheets over the default 3 you get with a new WB
96			if targetSheetNumb > 3:
97			targetxlBook.Worksheets.Add(Before=None, After=targetxlBook.Worksheets(targetxlBook.Worksheets.Count))
98
99			targetSht = targetxlBook.Worksheets(targetSheetNumb)
100	nino.borges	647
101	nino.borges	666	## First let's set a starting column on the target
102			targetColumnStart = 2
103	nino.borges	647
104	nino.borges	666	if rowAboveOveride:
105			mediaLogHeadderRow = mediaLogHeadderRow -1
106			for fieldNumber in sheetFieldsToGrab:
107			targetMediaLogHeadderRow = mediaLogHeadderRow-mediaLogHeadderRow +3
108			targetRowEnd = rowEnd-mediaLogHeadderRow + 1
109			sheetObj.Range(sheetObj.Cells(mediaLogHeadderRow,fieldNumber),sheetObj.Cells(rowEnd,fieldNumber)).Copy(targetSht.Range(targetSht.Cells(targetMediaLogHeadderRow,targetColumnStart),targetSht.Cells(targetRowEnd,targetColumnStart)))
110			#targetSht.Columns.AutoFit()
111			targetSht.Cells(targetMediaLogHeadderRow,targetColumnStart).ColumnWidth = mediaLogColSizeMatrix[fieldNumber]
112
113			targetColumnStart = targetColumnStart + 1
114
115			## Set the pretty case name at the top of the media log
116			targetSht.Cells(1,2).Value = "%s %s"% (caseName, sheetObj.Name)
117			targetSht.Cells(1,2).Font.Name = "Calibri"
118			targetSht.Cells(1,2).Font.Size = 16
119			targetSht.Name = sheetObj.Name
120			return 1
121	nino.borges	647	else:
122	nino.borges	746	print("ERROR: Not able to locate job number column!!")
123	nino.borges	647	return None
124	nino.borges	633
125			if __name__ == '__main__':
126	nino.borges	647
127	nino.borges	666	version = 'v1.06'
128	nino.borges	647
129	nino.borges	666	jsPickleFile = r"C:\Dev\Site\TRON-Conscripts\ReportsToCreate.pkl"
130
131	nino.borges	633	xlApp = Dispatch('Excel.Application')
132	nino.borges	666
133	nino.borges	633	## Turn off any messages about saving over existing target files.
134			xlApp.DisplayAlerts = False
135	nino.borges	666
136	nino.borges	633
137			## matrix of casename, (sourceJS, targetJS)
138	nino.borges	647	## reportsToCreate = {'Bielins - Bennardo':
139			## (r"C:\Test-PY\liveTest\Bielins-Bennardo-Job Summary.xlsx",r"C:\Test-PY\liveTest\Bielins - BennardoTESTJSLiveReport.xlsx"),
140			## 'SharkNinja - Keurig':
141			## (r"C:\Test-PY\liveTest\SharkNinja-Keurig-Job Summary.xlsx",r"C:\Test-PY\liveTest\SharkNinja - KeurigTESTJSLiveReport.xlsx"),
142			## 'Dairy Farmers-Litigation':
143			## (r"C:\Test-PY\liveTest\Dairy Farmers-Litigation-Job Summary.xlsx",r"C:\Test-PY\liveTest\Dairy Farmers-LitigationTESTJSLiveReport.xlsx")}
144	nino.borges	633
145	nino.borges	666	## reportsToCreate = {'I Grace Productions':
146			## (r"\\iadcifs01\job\Lamb Barnosky\I Grace Productions\I Grace Productions--Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\I Grace Productions-JS-LiveReport.xlsx"),
147			## 'McLaughlin Northrup - McDonald Fracassa':
148			## (r"\\iadcifs01\job\Murphy King\McLaughlin Northup-McDonald Fracassa\McLaughlin Northrup-McDonald Fracassa-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\McLaughlin Northrup - McDonald Fracassa-JS-LiveReport.xlsx"),
149			## 'Dairy Farmers - Litigation':
150			## (r"\\iadcifs01\job\Nystrom Beckman\Dairy Farmers-Litigation\Dairy Farmers-Litigation-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Dairy Farmers-Litigation-JS-LiveReport.xlsx"),
151			## 'Glassman - Metropolitan':
152			## (r"\\iadcifs01\job\PollackSD\Glassman-Metropolitan\Glassman-Metropolitan-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Glassman-Metropolitan-JS-LiveReport.xlsx"),
153			## 'Emilfarb - DOJ Subpoena':
154			## (r"\\iadcifs01\job\Libby Hoopes\Emilfarb-DOJ Subpoena\Emilfarb-DOJ Subpoena-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Emilfarb-DOJ Subpoena-JS-LiveReport.xlsx"),
155			## 'Bielins - Bennardo':
156			## (r"\\Iadcifs01\job\Lamb Barnosky\Gary Bielins-Bennardo\Bielins-Bennardo-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Bielins-Bennardo-JS-LiveReport.xlsx"),
157			## 'Bulger Capital Partners - Jenzabar Subpoena':
158			## (r"\\iadcifs01\job\PollackSD\Bulger Capital Partners-Jenzabar Subpoena\Bulger Capital Partners-Jenzabar Subpoena-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Bulger Capital Partners-Jenzabar Subpoena-JS-LiveReport.xlsx"),
159			## 'Schneider Electric - Perkins':
160			## (r"\\iadcifs01\job\Duffy\Schneider Electric-Perkins Will\Schneider Electric-Perkins Will-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\Schneider Electric-Perkins Will-JS-LiveReport.xlsx")
161			##
162			## }
163	nino.borges	647
164	nino.borges	666
165			## reportsToCreate = {'Legacy Global - Internal':
166			## (u'\\\\iadcifs01\\job\\PollackSD\\Legacy Global-Internal\\Legacy Global-Internal-Job Summary.xlsx', u'L:\\__People\\Emanuel\\MCP3_Temp\\Legacy Global-Internal-JS-LiveReport.xlsx'),
167			## 'Epic Genetics - Rodrigues':
168			## (u'\\\\iadcifs01\\job\\Hirsch Roberts Weinstein\\Epic Genetics-Rodrigues\\Epic Genetics-Rodrigues-JobSummary.xlsx', u'L:\\__People\\Emanuel\\MCP3_Temp\\Epic Genetics-Rodrigues-JobSummary.xlsx')
169			## }
170	nino.borges	633
171	nino.borges	666
172			with open(jsPickleFile, 'rb') as input:
173			reportsToCreate = pickle.load(input)
174
175	nino.borges	746	acceptedSheetNames = ['Media Log', 'Media Log-XEDD Processed' , 'XEDD-Processing-Eclipse', 'Processing','Processing-Eclipse','Processing-LAW','Processing-Legacy','Processing-ADD', 'ADD Processing','Outgoing Productions','Incoming Productions']
176	nino.borges	666
177	nino.borges	746	acceptedMediaLogFieldsList = ['Evidox Job Number', 'Evidox Media ID', 'Media Type', 'Disk Label - Folder / File name', 'Total File Count (# of Files Received)', 'Extracted Filesize (GB)', 'Serial Number', 'Notes', 'Custodian(s)\nREQUIRED','Custodian(s)','Party', 'Media Received', 'Media Returned']
178	nino.borges	647	acceptedMediaLogHeadderColor = 2
179			mediaLogHeadderRow = 15
180
181	nino.borges	746	acceptedOProdFieldsList = ['Evidox Job Number', 'Source / Criteria', 'Total Docs Requested for Production', 'Total Exclusions', 'Total Docs Produced','Total Docs Produced ','Total Docs Produced with Images','Total Pages Produced with Images','Total Docs Produced Natively','Bates Range','Docs Requiring','Total Pages','Unsupported Docs with Placeholders','Native Docs with Placeholders','Docs Requiring','Docs Errored','Total Docs','Total Pages','Opposing Production Volume','Opposing Native Production Volume','Opposing Native Production Volume ','Client Volume','Date Delivered','Notes']
182	nino.borges	647	acceptedOProdHeadderColor = 20
183			oProdHeadderRow = 3
184
185	nino.borges	746	acceptedIProdFieldsList = ['Evidox Job Number','Evidox Media ID','Total Docs Received','Total Images / Pages Received','Total Text Files Received','Total Natives Received','Bates / Production Control Number Range(s) ','Load Files Received (list extensions)','Metadata Received (Y/N)','Attachment Info Received (Y/N)','Volume','Date Delivered','Docs Requiring','Docs Errored / Excluded','Total Docs','Total Pages','Docs Requiring','Docs Errored / Excluded','Total Docs','Total Pages']
186	nino.borges	647	acceptedIProdHeadderColor = 40
187			iProdHeadderRow = 3
188
189	nino.borges	666	## all processing tabs will use these came settings.
190	nino.borges	746	acceptedProcFieldsList = ['Evidox Job Number','Evidox Media ID','Precull','GB','Total Docs Received','Duplicates','Excluded / Errored','Total Docs (Minus Dups and Excluded)','Total Unique Search Hits ','In As Family','Family Excluded (Corrupt)','Total Search Hits','Total Docs Delivered','Total Pages Delivered (Images or Scans)','Control Nos. / DocIDs','Docs Requiring','Docs Errored / Excluded','Total Docs','Total Pages','Docs Requiring','Docs Errored / Excluded','Total Docs','Total Pages','Volume','Date Delivered','Notes','Custodian(s)','GB Ingested','Filtered/\nExcluded','Total Pages (Images or Scans)','EVDXID Range']
191	nino.borges	666	acceptedProcHeadderColor = 19
192			procHeadderRow = 3
193	nino.borges	647
194	nino.borges	666
195	nino.borges	746	for reportCaseItem in list(reportsToCreate.keys()):
196	nino.borges	633	caseName = reportCaseItem
197			sourceSpreadsheetPath,targetSpreadsheet = reportsToCreate[reportCaseItem]
198
199	nino.borges	666	if os.path.exists(sourceSpreadsheetPath):
200	nino.borges	633
201	nino.borges	746	print("\n\nPerforming modDate test on %s..."%caseName)
202	nino.borges	666	sourceModifiedDTT = os.stat(sourceSpreadsheetPath)[8]
203			if os.path.exists(targetSpreadsheet):
204			targetModifiedDTT = os.stat(targetSpreadsheet)[8]
205			else:
206			targetModifiedDTT = 0
207			if sourceModifiedDTT > targetModifiedDTT:
208	nino.borges	746	print("Source has been modified.")
209	nino.borges	633
210	nino.borges	746	print("Now processing %s..."% caseName)
211	nino.borges	633
212	nino.borges	666	xlBook = xlApp.Workbooks.Open(sourceSpreadsheetPath)
213			#xlBook = xlApp.Workbooks.Open(r"\\iadcifs01\job\Lamb Barnosky\I Grace Productions\I Grace Productions--Job Summary.xlsx")
214			#targetSpreadsheet = r"C:\Test-PY\liveTest\OutputJSReport.xlsx"
215	nino.borges	633
216	nino.borges	666	## First lets create a matrix of the sheets to be grabbed from this source
217			acceptedSheetMatrix = {}
218			for i in range(xlBook.Sheets.Count):
219			if xlBook.Sheets(i+1).Name in acceptedSheetNames:
220			acceptedSheetMatrix[xlBook.Sheets(i+1).Name] = i+1
221			if acceptedSheetMatrix:
222			## Only save if at least one of the sheets get created.
223			atleastOneSheetCreated = False
224
225			targetSheetNumb = 1
226			targetxlBook = xlApp.Workbooks.Add()
227			#for sheetName in acceptedSheetMatrix.keys():
228			## Eventually make this an object but for now you need control over the properties passed to ProcessSheet and cant just loop through
229	nino.borges	746	if 'Media Log' in list(acceptedSheetMatrix.keys()):
230			print("media Log")
231			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Media Log']), acceptedMediaLogFieldsList, acceptedMediaLogHeadderColor, mediaLogHeadderRow,targetxlBook, targetSheetNumb, caseName)
232	nino.borges	666	if sheetResultCode == 1:
233			targetSheetNumb = targetSheetNumb + 1
234			atleastOneSheetCreated = True
235
236	nino.borges	746	if 'Media Log-XEDD Processed' in list(acceptedSheetMatrix.keys()):
237			print("Media Log-XEDD Processed")
238			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Media Log-XEDD Processed']), acceptedMediaLogFieldsList, acceptedMediaLogHeadderColor, mediaLogHeadderRow,targetxlBook, targetSheetNumb, caseName)
239	nino.borges	698	if sheetResultCode == 1:
240			targetSheetNumb = targetSheetNumb + 1
241			atleastOneSheetCreated = True
242
243	nino.borges	746	if 'Outgoing Productions' in list(acceptedSheetMatrix.keys()):
244			print("outgoing productions")
245			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Outgoing Productions']), acceptedOProdFieldsList, acceptedOProdHeadderColor, oProdHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
246	nino.borges	666	if sheetResultCode == 1 :
247			targetSheetNumb = targetSheetNumb + 1
248			atleastOneSheetCreated = True
249	nino.borges	647
250	nino.borges	746	if 'Incoming Productions' in list(acceptedSheetMatrix.keys()):
251			print("Incoming Productions")
252			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Incoming Productions']), acceptedIProdFieldsList, acceptedIProdHeadderColor, iProdHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
253	nino.borges	666	if sheetResultCode == 1:
254			targetSheetNumb = targetSheetNumb + 1
255			atleastOneSheetCreated = True
256	nino.borges	647
257	nino.borges	746	if 'Processing' in list(acceptedSheetMatrix.keys()):
258			print("Processing")
259			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
260	nino.borges	666	if sheetResultCode == 1:
261			targetSheetNumb = targetSheetNumb + 1
262			atleastOneSheetCreated = True
263	nino.borges	647
264	nino.borges	746	if 'Processing-Eclipse' in list(acceptedSheetMatrix.keys()):
265			print("Processing-Eclipse")
266			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing-Eclipse']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
267	nino.borges	666	if sheetResultCode == 1:
268			targetSheetNumb = targetSheetNumb + 1
269			atleastOneSheetCreated = True
270
271	nino.borges	746	if 'Processing-LAW' in list(acceptedSheetMatrix.keys()):
272			print("Processing-LAW")
273			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing-LAW']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
274	nino.borges	666	if sheetResultCode == 1:
275			targetSheetNumb = targetSheetNumb + 1
276			atleastOneSheetCreated = True
277
278	nino.borges	746	if 'Processing-Legacy' in list(acceptedSheetMatrix.keys()):
279			print("Processing-Legacy")
280			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing-Legacy']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
281	nino.borges	666	if sheetResultCode == 1:
282			targetSheetNumb = targetSheetNumb + 1
283			atleastOneSheetCreated = True
284
285	nino.borges	746	if 'Processing-ADD' in list(acceptedSheetMatrix.keys()):
286			print("Processing-ADD")
287			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['Processing-ADD']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
288	nino.borges	666	if sheetResultCode == 1:
289			targetSheetNumb = targetSheetNumb + 1
290			atleastOneSheetCreated = True
291
292	nino.borges	746	if 'ADD Processing' in list(acceptedSheetMatrix.keys()):
293			print("ADD Processing")
294			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['ADD Processing']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
295	nino.borges	666	if sheetResultCode == 1:
296			targetSheetNumb = targetSheetNumb + 1
297			atleastOneSheetCreated = True
298
299	nino.borges	746	if 'XEDD-Processing-Eclipse' in list(acceptedSheetMatrix.keys()):
300			print("XEDD-Processing-Eclipse")
301			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix['XEDD-Processing-Eclipse']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
302	nino.borges	670	if sheetResultCode == 1:
303			targetSheetNumb = targetSheetNumb + 1
304			atleastOneSheetCreated = True
305
306	nino.borges	666	if atleastOneSheetCreated:
307	nino.borges	746	print("Trying to save as %s"%targetSpreadsheet)
308	nino.borges	666	targetxlBook.SaveAs(targetSpreadsheet)
309			targetxlBook.Close()
310			else:
311			targetxlBook.Close(SaveChanges=False)
312	nino.borges	746	print("%s Processed. Closing that spreadsheet."% caseName)
313	nino.borges	666	xlBook.Close(SaveChanges=False)
314			else:
315	nino.borges	746	print("Source hasnt been modified. Skipping.")
316	nino.borges	666
317
318
319			else:
320	nino.borges	670	errFile = open(r"\\Xiprofsrvw01\xiprofsrvw01\Admin\MCP\MCP3_Temp\errlog.txt",'a')
321	nino.borges	666	errFile.write("%s :: %s does not exist\n"% (datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d %H:%M'),sourceSpreadsheetPath))
322			errFile.close()
323	nino.borges	647	#sht = xlBook.Worksheets(1)
324			#acceptedFieldsList = [u'Evidox Job Number', u'Evidox Media ID', u'Media Type', u'Disk Label - Folder / File name', u'Total File Count (# of Files Received)', u'Extracted Filesize (GB)', u'Serial Number', u'Notes', u'Custodian(s)\nREQUIRED', u'Media Received', u'Media Returned']
325			#acceptedHeadderColor = 2
326
327			#mediaLogHeadderRow = 15
328
329	nino.borges	633
330	nino.borges	647
331	nino.borges	633