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
#	Content
1	"""
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	import os, datetime
16	import cPickle as pickle
17
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	def ProcessSheet(sheetObj, acceptedFieldsList, acceptedHeadderColor, mediaLogHeadderRow, targetxlBook, targetSheetNumb, caseName, rowAboveOveride = False, skipIfEmpty = False):
38	"""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
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	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	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
101	## First let's set a starting column on the target
102	targetColumnStart = 2
103
104	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	else:
122	print "ERROR: Not able to locate job number column!!"
123	return None
124
125	if __name__ == '__main__':
126
127	version = 'v1.06'
128
129	jsPickleFile = r"C:\Dev\Site\TRON-Conscripts\ReportsToCreate.pkl"
130
131	xlApp = Dispatch('Excel.Application')
132
133	## Turn off any messages about saving over existing target files.
134	xlApp.DisplayAlerts = False
135
136
137	## matrix of casename, (sourceJS, targetJS)
138	## 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
145	## 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
164
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
171
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	acceptedMediaLogHeadderColor = 2
179	mediaLogHeadderRow = 15
180
181	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	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	## 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
194
195	for reportCaseItem in reportsToCreate.keys():
196	caseName = reportCaseItem
197	sourceSpreadsheetPath,targetSpreadsheet = reportsToCreate[reportCaseItem]
198
199	if os.path.exists(sourceSpreadsheetPath):
200
201	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
210	print "Now processing %s..."% caseName
211
212	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
216	## 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
243	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
250	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
257	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	#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
316
317