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 cPickle 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 = [u'Media Log',u'Processing',u'Processing-Eclipse',u'Processing-LAW',u'Processing-Legacy',u'Processing-ADD', u'ADD Processing',u'Outgoing Productions',u'Incoming Productions']
    
    acceptedMediaLogFieldsList = [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'Custodian(s)',u'Party', u'Media Received', u'Media Returned']
    acceptedMediaLogHeadderColor = 2
    mediaLogHeadderRow = 15

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

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

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


    for reportCaseItem in 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 u'Media Log' in acceptedSheetMatrix.keys():
                        print "media Log"
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Media Log']), acceptedMediaLogFieldsList, acceptedMediaLogHeadderColor, mediaLogHeadderRow,targetxlBook, targetSheetNumb, caseName)
                        if sheetResultCode == 1:
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True
                            
                    if u'Outgoing Productions' in acceptedSheetMatrix.keys():
                        print "outgoing productions"
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Outgoing Productions']), acceptedOProdFieldsList, acceptedOProdHeadderColor, oProdHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
                        if sheetResultCode == 1 :
                            targetSheetNumb = targetSheetNumb + 1
                            atleastOneSheetCreated = True

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

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

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

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

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

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

                    if u'ADD Processing' in acceptedSheetMatrix.keys():
                        print "ADD Processing"
                        sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'ADD Processing']), 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"L:\__People\Emanuel\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:	666
Committed:	Thu Dec 12 21:18:56 2019 UTC (6 years, 3 months ago) by nino.borges
Content type:	text/x-python
File size:	19857 byte(s)
Log Message:	small changes
#	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			import cPickle 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			print "colstartend = %s, %s"%(colStart,colEnd)
54			print "totalcol is %s"% totalColTest
55			if totalColTest < 4:
56			print "Column count was very low, so trying a higher head row number."
57			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			print sheetFieldsToGrab
77
78			## Using the jobNumber column, gather the highest row number
79			if mediaLogJobNumberColumn:
80			rowEnd = GatherRowTableEnd(sheetObj, mediaLogHeadderRow, mediaLogJobNumberColumn)
81			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			print "sheet basically empty. Skipping it."
91			return None
92			else:
93			#targetxlBook = xlApp.Workbooks.Add()
94			print "sheet numb will be %d"%targetSheetNumb
95			## 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			print "ERROR: Not able to locate job number column!!"
123			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			acceptedSheetNames = [u'Media Log',u'Processing',u'Processing-Eclipse',u'Processing-LAW',u'Processing-Legacy',u'Processing-ADD', u'ADD Processing',u'Outgoing Productions',u'Incoming Productions']
176
177			acceptedMediaLogFieldsList = [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'Custodian(s)',u'Party', u'Media Received', u'Media Returned']
178	nino.borges	647	acceptedMediaLogHeadderColor = 2
179			mediaLogHeadderRow = 15
180
181	nino.borges	666	acceptedOProdFieldsList = [u'Evidox Job Number', u'Source / Criteria', u'Total Docs Requested for Production', u'Total Exclusions', u'Total Docs Produced',u'Total Docs Produced ',u'Total Docs Produced with Images',u'Total Pages Produced with Images',u'Total Docs Produced Natively',u'Bates Range',u'Docs Requiring',u'Total Pages',u'Unsupported Docs with Placeholders',u'Native Docs with Placeholders',u'Docs Requiring',u'Docs Errored',u'Total Docs',u'Total Pages',u'Opposing Production Volume',u'Opposing Native Production Volume',u'Opposing Native Production Volume ',u'Client Volume',u'Date Delivered',u'Notes']
182	nino.borges	647	acceptedOProdHeadderColor = 20
183			oProdHeadderRow = 3
184
185			acceptedIProdFieldsList = [u'Evidox Job Number',u'Evidox Media ID',u'Total Docs Received',u'Total Images / Pages Received',u'Total Text Files Received',u'Total Natives Received',u'Bates / Production Control Number Range(s) ',u'Load Files Received (list extensions)',u'Metadata Received (Y/N)',u'Attachment Info Received (Y/N)',u'Volume',u'Date Delivered',u'Docs Requiring',u'Docs Errored / Excluded',u'Total Docs',u'Total Pages',u'Docs Requiring',u'Docs Errored / Excluded',u'Total Docs',u'Total Pages']
186			acceptedIProdHeadderColor = 40
187			iProdHeadderRow = 3
188
189	nino.borges	666	## all processing tabs will use these came settings.
190			acceptedProcFieldsList = [u'Evidox Job Number',u'Evidox Media ID',u'Precull',u'GB',u'Total Docs Received',u'Duplicates',u'Excluded / Errored',u'Total Docs (Minus Dups and Excluded)',u'Total Unique Search Hits ',u'In As Family',u'Family Excluded (Corrupt)',u'Total Search Hits',u'Total Docs Delivered',u'Total Pages Delivered (Images or Scans)',u'Control Nos. / DocIDs',u'Docs Requiring',u'Docs Errored / Excluded',u'Total Docs',u'Total Pages',u'Docs Requiring',u'Docs Errored / Excluded',u'Total Docs',u'Total Pages',u'Volume',u'Date Delivered',u'Notes',u'Custodian(s)',u'GB Ingested',u'Filtered/\nExcluded',u'Total Pages (Images or Scans)',u'EVDXID Range']
191			acceptedProcHeadderColor = 19
192			procHeadderRow = 3
193	nino.borges	647
194	nino.borges	666
195	nino.borges	633	for reportCaseItem in reportsToCreate.keys():
196			caseName = reportCaseItem
197			sourceSpreadsheetPath,targetSpreadsheet = reportsToCreate[reportCaseItem]
198
199	nino.borges	666	if os.path.exists(sourceSpreadsheetPath):
200	nino.borges	633
201	nino.borges	666	print "\n\nPerforming modDate test on %s..."%caseName
202			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			print "Source has been modified."
209	nino.borges	633
210	nino.borges	666	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			if u'Media Log' in acceptedSheetMatrix.keys():
230			print "media Log"
231			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Media Log']), acceptedMediaLogFieldsList, acceptedMediaLogHeadderColor, mediaLogHeadderRow,targetxlBook, targetSheetNumb, caseName)
232			if sheetResultCode == 1:
233			targetSheetNumb = targetSheetNumb + 1
234			atleastOneSheetCreated = True
235
236			if u'Outgoing Productions' in acceptedSheetMatrix.keys():
237			print "outgoing productions"
238			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Outgoing Productions']), acceptedOProdFieldsList, acceptedOProdHeadderColor, oProdHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
239			if sheetResultCode == 1 :
240			targetSheetNumb = targetSheetNumb + 1
241			atleastOneSheetCreated = True
242	nino.borges	647
243	nino.borges	666	if u'Incoming Productions' in acceptedSheetMatrix.keys():
244			print "Incoming Productions"
245			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Incoming Productions']), acceptedIProdFieldsList, acceptedIProdHeadderColor, iProdHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
246			if sheetResultCode == 1:
247			targetSheetNumb = targetSheetNumb + 1
248			atleastOneSheetCreated = True
249	nino.borges	647
250	nino.borges	666	if u'Processing' in acceptedSheetMatrix.keys():
251			print "Processing"
252			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Processing']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
253			if sheetResultCode == 1:
254			targetSheetNumb = targetSheetNumb + 1
255			atleastOneSheetCreated = True
256	nino.borges	647
257	nino.borges	666	if u'Processing-Eclipse' in acceptedSheetMatrix.keys():
258			print "Processing-Eclipse"
259			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Processing-Eclipse']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
260			if sheetResultCode == 1:
261			targetSheetNumb = targetSheetNumb + 1
262			atleastOneSheetCreated = True
263
264			if u'Processing-LAW' in acceptedSheetMatrix.keys():
265			print "Processing-LAW"
266			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Processing-LAW']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
267			if sheetResultCode == 1:
268			targetSheetNumb = targetSheetNumb + 1
269			atleastOneSheetCreated = True
270
271			if u'Processing-Legacy' in acceptedSheetMatrix.keys():
272			print "Processing-Legacy"
273			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Processing-Legacy']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
274			if sheetResultCode == 1:
275			targetSheetNumb = targetSheetNumb + 1
276			atleastOneSheetCreated = True
277
278			if u'Processing-ADD' in acceptedSheetMatrix.keys():
279			print "Processing-ADD"
280			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'Processing-ADD']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
281			if sheetResultCode == 1:
282			targetSheetNumb = targetSheetNumb + 1
283			atleastOneSheetCreated = True
284
285			if u'ADD Processing' in acceptedSheetMatrix.keys():
286			print "ADD Processing"
287			sheetResultCode = ProcessSheet(xlBook.Worksheets(acceptedSheetMatrix[u'ADD Processing']), acceptedProcFieldsList, acceptedProcHeadderColor, procHeadderRow, targetxlBook, targetSheetNumb, caseName, True, True)
288			if sheetResultCode == 1:
289			targetSheetNumb = targetSheetNumb + 1
290			atleastOneSheetCreated = True
291
292			if atleastOneSheetCreated:
293			print "Trying to save as %s"%targetSpreadsheet
294			targetxlBook.SaveAs(targetSpreadsheet)
295			targetxlBook.Close()
296			else:
297			targetxlBook.Close(SaveChanges=False)
298			print "%s Processed. Closing that spreadsheet."% caseName
299			xlBook.Close(SaveChanges=False)
300			else:
301			print "Source hasnt been modified. Skipping."
302
303
304
305			else:
306			errFile = open(r"L:\__People\Emanuel\MCP3_Temp\errlog.txt",'a')
307			errFile.write("%s :: %s does not exist\n"% (datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d %H:%M'),sourceSpreadsheetPath))
308			errFile.close()
309	nino.borges	647	#sht = xlBook.Worksheets(1)
310			#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']
311			#acceptedHeadderColor = 2
312
313			#mediaLogHeadderRow = 15
314
315	nino.borges	633
316	nino.borges	647
317	nino.borges	633