def mainFunction(arrayOfArguments):
pathBelowRepos = pathToThisPythonFile
spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(
True, pathBelowRepos,
googleAccountUsername=arrayOfArguments[2]).open(arrayOfArguments[1])
with open(arrayOfArguments[3], 'r') as filehandle:
invoicesArray = json.load(filehandle)
clearAndResizeParameters = [
{
'sheetObj': spreadsheetLevelObj.worksheet('Invoices'),
'resizeRows': 2,
'startingRowIndexToClear': 0,
'resizeColumns': 1
},
]
myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
myGspreadFunc.displayArray(spreadsheetLevelObj.worksheet('Invoices'),
invoicesArray)
myGspreadFunc.setFiltersOnSpreadsheet(spreadsheetLevelObj)
myGspreadFunc.autoAlignColumnsInSpreadsheet(spreadsheetLevelObj)
def mainFunction(arrayOfArguments):
def getArrayOfPDFFiles(pathToRoot, foldersToExclude):
# p(foldersToExclude)
def ifPDFFile(fileObj):
# p(fileObj)
if fileObj.is_file() and fileObj.suffix == '.pdf': return fileObj
return False
# folderPathsToExclude = []
# for folderToExclude in foldersToExclude:
# folderPathsToExclude.append(Path(pathToRoot, folderToExclude))
return myPyFunc.getArrayOfFileObjInTreeBreadthFirst(Path(pathToRoot), ifPDFFile, pathsToExclude=foldersToExclude)
googleAccountUsername = arrayOfArguments[1]
googleSpreadsheetTitle = arrayOfArguments[2]
googleSheetTitleToSaveListTo = arrayOfArguments[3]
pathToRootToBeginSearching = arrayOfArguments[4]
arrayOfFoldersToExclude = arrayOfArguments[5:]
arrayOfFoldersToExclude = [e.replace('\\\\', '\\') for e in arrayOfFoldersToExclude]
pathBelowRepos = pathToThisPythonFile
spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(True, pathBelowRepos, googleAccountUsername=googleAccountUsername).open(googleSpreadsheetTitle)
arrayOfPDFFilesFromDisk = getArrayOfPDFFiles(pathToRootToBeginSearching, arrayOfFoldersToExclude)
arrayOfPDFFilesFromDisk = [[str(e)] for e in arrayOfPDFFilesFromDisk]
# arrayUploadToGoogleSheets = []
# for filePath in arrayOfPDFFilesFromDisk:
# arrayUploadToGoogleSheets.append([filePath, ''])
arrayOfPDFFilesFromDisk.insert(0, ['File Path'])
# p(arrayOfPDFFilesFromDisk)
clearAndResizeParameters = [
{
'sheetObj': spreadsheetLevelObj.worksheet(googleSheetTitleToSaveListTo),
'resizeRows': 2,
'startingRowIndexToClear': 0,
'resizeColumns': 1
},
]
myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
myGspreadFunc.displayArray(spreadsheetLevelObj.worksheet(googleSheetTitleToSaveListTo), arrayOfPDFFilesFromDisk)
def mainFunction(arrayOfArguments):
pathBelowRepos = pathToThisPythonFile
spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(True, pathBelowRepos, googleAccountUsername=arrayOfArguments[2]).open(arrayOfArguments[1])
invoicesArray = spreadsheetLevelObj.worksheet('Invoices').get_all_values()
with open(arrayOfArguments[3], 'w') as filehandle:
json.dump(invoicesArray, filehandle)
def mainFunction(arrayOfArguments):
pyautogui.PAUSE = 0.01
sendingKeystrokes = True
# gspObj = gspread.service_account(filename=myPyFunc.addToPath(pathToRepos, arrayOfPartsToAddToPath))
# p(arrayOfArguments)
spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(
True,
arrayOfArguments[3],
googleAccountUsername=arrayOfArguments[1],
pathStrToJSONCredentialsFile=arrayOfArguments[4]).open(
'Transactions To Post')
sheetName = arrayOfArguments[2]
toPostFromArray = spreadsheetLevelObj.worksheet(sheetName).get_all_values()
# charactersNeedingShift = (list(range(123, 127)) + list(range(94, 96)) + list(range(62, 91)) + [60, 58] + list(range(40, 44)) + list(range(33, 39)))
# p(charactersNeedingShift)
columnNameToIndexObj = {}
for columnIndexNumber, columnName in enumerate(toPostFromArray[0]):
columnNameToIndexObj[columnName] = columnIndexNumber
columnNameToNumberOfTabsObj = {}
if sendingKeystrokes:
with pynput.mouse.Listener(
on_click=myPyFunc.functionOnClick) as listenerObj:
print("Click on 'Clear' to begin posting...")
listenerObj.join()
if 'Bank Transactions' in sheetName:
columnNameToNumberOfTabsObj['Checkbook ID'] = 2
columnNameToNumberOfTabsObj['Amount'] = 6
arrayOfKeysResetDropDown = ['down', 'up', 'up', 'up']
# p(columnNameToIndexObj)
for row in toPostFromArray[1:]: #toPostFromArray[1728:1729]: #
optionData = row[columnNameToIndexObj['Option']]
if row[columnNameToIndexObj[
'Status']] == '' and optionData != 'Enter/Edit':
if optionData != 'Enter Transaction' or (
optionData == 'Enter Transaction'
and row[columnNameToIndexObj['Type']]
not in ['Check', 'Decrease Adjustment']):
columnNameToNumberOfTabsObj['Account'] = 2
else:
columnNameToNumberOfTabsObj['Account'] = 1
if myPyFunc.numLockIsOff():
pyautogui.press('numlock')
# p(columnNameToNumberOfTabsObj)
# p(columnNameToIndexObj)
for columnIndexNumber, columnData in enumerate(row):
for columnIndexNumberFromObj, columnNameFromObj in enumerate(
columnNameToIndexObj):
if columnIndexNumberFromObj == columnIndexNumber:
currentColumnName = columnNameFromObj
if currentColumnName not in [
'Status', 'Person', 'Notes1', 'Notes2'
]: #< 9: #
# p(currentColumnName)
if currentColumnName in ['Option', 'Type']:
pyautogui.press(arrayOfKeysResetDropDown)
numberOfDownKeyPresses = 0
if columnData in ['Enter Receipt', 'Cash']:
numberOfDownKeyPresses = 1
if columnData == 'Increase Adjustment':
numberOfDownKeyPresses = 2
if columnData == 'Decrease Adjustment':
numberOfDownKeyPresses = 3
myPyAutoGui.repetitiveKeyPress(
numberOfDownKeyPresses, 'down')
if currentColumnName == 'Transaction Date':
# p(columnData)
dateObj = datetime.datetime.strptime(
columnData, '%m/%d/%Y')
columnData = dateObj.strftime('%m%d%Y')
if currentColumnName == 'Account':
columnData = columnData.replace('-', '')
if currentColumnName in ['Amount', 'Amount2']:
columnData = columnData.lstrip('$').replace(
'.', '').replace(',', '')
if currentColumnName not in ['Option', 'Type']:
myPyAutoGui.typeCharactersOnRemoteDesktop(
columnData)
if currentColumnName in columnNameToNumberOfTabsObj:
myPyAutoGui.repetitiveKeyPress(
columnNameToNumberOfTabsObj[currentColumnName],
'tab')
else:
myPyAutoGui.repetitiveKeyPress(1, 'tab')
if not myPyFunc.numLockIsOff():
pyautogui.press('numlock')
with pynput.mouse.Listener(
on_click=myPyFunc.functionOnClick) as listenerObj:
print(
"Click on 'Post' or 'Clear' to continue with this entry..."
)
listenerObj.join()
elif sheetName == 'Bank Transfers':
# p(columnNameToIndexObj)
columnNameToNumberOfTabsObj[
columnNameToIndexObj['Transfer From Checkbook ID']] = 2
for row in toPostFromArray[1:]:
if row[columnNameToIndexObj['Status']] == '':
if myPyFunc.numLockIsOff():
pyautogui.press('numlock')
myPyAutoGui.repetitiveKeyPress(2, "tab")
rowToEnumerate = row[0:5]
# p(rowToEnumerate)
for columnIndexNumber, columnData in enumerate(rowToEnumerate):
# p(columnData)
if columnIndexNumber == columnNameToIndexObj[
'Transfer Date']:
dateObj = datetime.datetime.strptime(
columnData, '%m/%d/%Y')
columnData = dateObj.strftime('%m%d%Y')
if columnIndexNumber == columnNameToIndexObj['Amount']:
columnData = columnData.lstrip('$').replace(
'.', '').replace(',', '')
myPyAutoGui.typeCharactersOnRemoteDesktop(columnData)
# for characterToType in columnData:
# if ord(characterToType) in charactersNeedingShift:
# priorPyAutoGuiPause = pyautogui.PAUSE
# pyautogui.PAUSE = .000000000001
# pyautogui.keyDown('shift')
# pyautogui.press(characterToType)
# pyautogui.keyUp('shift')
# pyautogui.PAUSE = priorPyAutoGuiPause
# else:
# pyautogui.press(characterToType)
if columnIndexNumber in columnNameToNumberOfTabsObj:
pass
myPyAutoGui.repetitiveKeyPress(
columnNameToNumberOfTabsObj[columnIndexNumber],
'tab')
else:
pass
myPyAutoGui.repetitiveKeyPress(1, 'tab')
if not myPyFunc.numLockIsOff():
pyautogui.press('numlock')
with pynput.mouse.Listener(
on_click=myPyFunc.functionOnClick) as listenerObj:
print(
"Click on 'Post' or 'Clear' to continue with this entry..."
)
listenerObj.join()
def ocrPDFFiles(arrayOfArguments):
pathBelowRepos = pathToThisPythonFile
spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(
True, pathBelowRepos,
googleAccountUsername=arrayOfArguments[1]).open(arrayOfArguments[2])
filesSheetName = arrayOfArguments[3]
googleSheetsFileArray = spreadsheetLevelObj.worksheet(
filesSheetName).get_all_values()
filePathColIdx = 0
completedColIdx = 3
lastRowIndexToOCR = None
for rowIndex, row in enumerate(googleSheetsFileArray):
if row[completedColIdx] == '':
lastRowIndexToOCR = rowIndex
currentGroupCount = 0
currentGroupRowIndices = []
for rowIndex, row in enumerate(googleSheetsFileArray):
if row[completedColIdx] == '':
fileObjPath = Path(row[filePathColIdx])
addFileToAcrobatOCRList(fileObjPath)
currentGroupCount = currentGroupCount + 1
currentGroupRowIndices.append(rowIndex)
if currentGroupCount == int(
arrayOfArguments[5]) or rowIndex == lastRowIndexToOCR:
myPyAutoGui.clickWhenLocalPNGAppears(
'nextButtonBeginOCR', pathToThisPythonFile.parents[0])
myPyAutoGui.clickWhenLocalPNGAppears(
'closeActionCompleted', pathToThisPythonFile.parents[0])
myPyAutoGui.waitUntilLocalPNGDisappears(
'closeActionCompleted', pathToThisPythonFile.parents[0])
for currentGroupRowIndex in currentGroupRowIndices:
googleSheetsFileArray[currentGroupRowIndex][
completedColIdx] = 'Yes - ' + str(
datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))
clearAndResizeParameters = [
{
'sheetObj':
spreadsheetLevelObj.worksheet(arrayOfArguments[4]),
'resizeRows':
2,
'startingRowIndexToClear':
0,
'resizeColumns':
1
},
]
p('Updating Google Sheets...')
myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
myGspreadFunc.displayArray(
spreadsheetLevelObj.worksheet(arrayOfArguments[4]),
googleSheetsFileArray)
# myGspreadFunc.autoAlignColumnsInSpreadsheet(spreadsheetLevelObj)
p('Done updating Google Sheets.')
currentGroupCount = 0
currentGroupRowIndices = []
pyautogui.press(['alt', 'f', 'w', 'down', 'down', 'enter'])
def mainFunction(arrayOfArguments):
pathBelowRepos = pathToThisPythonFile
spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(
True, pathBelowRepos,
googleAccountUsername=arrayOfArguments[1]).open(arrayOfArguments[2])
arrayFilenameColIdx = 0
arrayCompletedStatusColIdx = 1
filesToReviewArray = spreadsheetLevelObj.worksheet(
arrayOfArguments[3]).get_all_values()
filesToReviewArrayFirstRow = filesToReviewArray.pop(0)
filesCompletedArray = spreadsheetLevelObj.worksheet(
arrayOfArguments[4]).get_all_values()
filesCompletedArray = list(
filter(lambda x: x[arrayCompletedStatusColIdx] != '',
filesCompletedArray))
matchedArray = []
matchedArray.append(filesToReviewArrayFirstRow + [''] +
filesCompletedArray[0])
# p(filesCompletedWithCompletedStatusArray)
def filenameComparisonFunction(firstArrayCurrentRow,
filesCompletedArrayCurrentRow):
if firstArrayCurrentRow[
arrayFilenameColIdx] == filesCompletedArrayCurrentRow[
arrayFilenameColIdx]:
return True
return False
def rowForMatchedArray(filesToReviewArrayCurrentRow):
rowToReturn = filesToReviewArrayCurrentRow
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst(
[filenameComparisonFunction], filesToReviewArrayCurrentRow,
filesCompletedArray)
if len(rowIndicesThatMatch) == 1:
rowToReturn.extend([''] +
filesCompletedArray.pop(rowIndicesThatMatch[0]))
elif len(rowIndicesThatMatch) > 1:
p('More than one row matches on the first pass')
return rowToReturn
myPyFunc.transferToArray(filesToReviewArray, matchedArray,
rowForMatchedArray)
clearAndResizeParameters = [{
'sheetObj':
spreadsheetLevelObj.worksheet(arrayOfArguments[5]),
'resizeRows':
2,
'startingRowIndexToClear':
0,
'resizeColumns':
1
}, {
'sheetObj':
spreadsheetLevelObj.worksheet(arrayOfArguments[6]),
'resizeRows':
2,
'startingRowIndexToClear':
0,
'resizeColumns':
1
}]
myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
myGspreadFunc.displayArray(
spreadsheetLevelObj.worksheet(arrayOfArguments[5]), matchedArray)
myGspreadFunc.displayArray(
spreadsheetLevelObj.worksheet(arrayOfArguments[6]),
filesCompletedArray)
myGspreadFunc.autoAlignColumnsInSpreadsheet(spreadsheetLevelObj)
def mainFunction(arrayOfArguments):
# p(arrayOfArguments[3])
pathBelowRepos = pathToThisPythonFile
spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(
True, pathBelowRepos,
googleAccountUsername=arrayOfArguments[1]).open(arrayOfArguments[2])
if arrayOfArguments[4] == 'From Google Sheets':
invoicesArray = spreadsheetLevelObj.worksheet(
'Invoices').get_all_values()
elif arrayOfArguments[4] == 'From Local File':
with open(arrayOfArguments[5], 'r') as filehandle:
invoicesArray = json.load(filehandle)
invoicePulledColIdx = 0
typeColIdx = 1
journalEntryColIdx = 2
dateColIdx = 4
acctNumColIdx = 5
debitColIdx = 7
creditColIdx = 8
nameColIdx = 11
notesColIdx = 18
def paste():
pyautogui.keyDown('ctrl')
pyautogui.press('v')
pyautogui.keyUp('ctrl')
def clearColumnFieldAndPaste(strToPaste):
pyperclip.copy(strToPaste)
pyautogui.press('backspace')
# m.clickWhenLocalPNGAppears('emptyColumnName', parentDir)
paste()
if m.locateOnScreenLocal('searchWindow', parentDir):
m.clickWhenLocalPNGAppears('closeGPWindow', parentDir)
for row in invoicesArray:
if row[invoicePulledColIdx] == '':
m.clickWhenLocalPNGAppears('search', parentDir)
m.clickWhenLocalPNGAppears('searchWindow', parentDir)
if row[typeColIdx]:
clearColumnFieldAndPaste('TRX Date')
pyautogui.press(['tab'] * 3)
datePartsArray = row[dateColIdx].split('/')
dateStr = datePartsArray[0].zfill(2) + datePartsArray[1].zfill(
2) + datePartsArray[2]
pyautogui.write(dateStr)
else:
clearColumnFieldAndPaste('Journal Entry')
pyautogui.press(['tab'] * 2)
pyautogui.write(row[journalEntryColIdx])
pyautogui.press('tab')
pyautogui.press(['tab'] * 2)
# exit()
if row[typeColIdx]:
if m.locateOnScreenLocal('accountNumberColumnName', parentDir):
clearColumnFieldAndPaste('')
pyautogui.press('tab')
else:
clearColumnFieldAndPaste('Account Number')
pyautogui.press(['tab'] * 2)
pyautogui.write(row[acctNumColIdx])
pyautogui.press('tab')
pyautogui.press(['tab'] * 3)
# if row[debitColIdx] != '228.94':
# exit()
pyautogui.write(row[debitColIdx])
pyautogui.press(['tab'] * 5)
pyautogui.write(row[creditColIdx])
pyautogui.press('enter')
m.waitUntilLocalPNGAppears('completedOneTransactionSmallScreen',
parentDir)
pyautogui.press(['tab'] * 15)
while not m.locateOnScreenLocal('accountBlue', parentDir):
p('Looking for accountBlue...')
pydirectinput.press('down')
while not m.locateOnScreenLocal('blue', parentDir):
p('Looking for blue...')
pydirectinput.press('down')
m.doubleClickWhenLocalPNGAppears('blue', parentDir)
m.clickWhenLocalPNGAppears('sourceDocument', parentDir)
m.clickWhenLocalPNGAppears('imageButton', parentDir)
while not m.locateOnScreenLocal('relatedDocumentsIcon', parentDir):
m.clickWhenLocalPNGAppears('bistrackIcon', parentDir)
m.clickWhenLocalPNGAppears('relatedDocumentsIcon', parentDir)
m.clickWhenLocalPNGAppears('relatedDocumentsWindow', parentDir)
while not m.locateOnScreenLocal('openInvoice', parentDir):
pydirectinput.press('down')
m.clickWhenLocalPNGAppears('openInvoice', parentDir)
m.clickWhenLocalPNGAppears('print', parentDir)
m.waitUntilLocalPNGAppears('selectPrinter', parentDir)
pyautogui.press(['c', 'u', 'enter'])
while not m.locateOnScreenLocal('cutePDFSaveAsIcon', parentDir):
p('Looking for cutePDFSaveAs...')
if m.getCoordinatesIfLocalPNGIsShowing(
'gpInvoiceWindowNotHighlighted', parentDir):
m.clickWhenLocalPNGAppears('gpInvoiceWindowNotHighlighted',
parentDir)
# elif m.getCoordinatesIfLocalPNGIsShowing('cutePDFSaveAsIcon', parentDir):
# m.clickWhenLocalPNGAppears('cutePDFSaveAsIcon', parentDir)
while not m.locateOnScreenLocal('cutePDFSaveAs', parentDir):
if m.getCoordinatesIfLocalPNGIsShowing('cutePDFSaveAsIcon',
parentDir):
m.clickWhenLocalPNGAppears('cutePDFSaveAsIcon', parentDir)
m.clickWhenLocalPNGAppears('cutePDFSaveAs', parentDir)
pyautogui.press(['tab'] * 5) #5)
if row[typeColIdx]:
pyperclip.copy(
arrayOfArguments[3] + '\\' +
row[typeColIdx].replace('\\', '').replace('&', ''))
paste()
else:
pyperclip.copy(arrayOfArguments[3] + '\\' +
row[journalEntryColIdx] + ' - ' +
row[acctNumColIdx] + ' - ' +
row[nameColIdx].replace('\\', '').replace(
'&', '').replace('/', '') + ' - ' +
row[notesColIdx])
paste()
pyautogui.press('enter')
m.clickLocalPNGWhenAppearsAndWaitUntilLocaPNGDisappears(
'closeGPInvoice', 'print', parentDir)
while m.locateOnScreenLocal('closeRelatedDocuments', parentDir):
m.clickWhenLocalPNGAppears('closeRelatedDocuments', parentDir)
m.waitUntilLocalPNGDisappears('relatedDocumentsWindow', parentDir)
m.clickWhenLocalPNGAppears('payablesEntry', parentDir)
m.clickLocalPNGWhenAppearsAndWaitUntilLocaPNGDisappears(
'closeGPWindow', 'payablesEntryActive', parentDir)
m.clickLocalPNGWhenAppearsAndWaitUntilLocaPNGDisappears(
'closeGPWindow', 'transactionEntry', parentDir)
row[invoicePulledColIdx] = 'Yes'
if arrayOfArguments[4] == 'From Google Sheets':
clearAndResizeParameters = [
{
'sheetObj': spreadsheetLevelObj.worksheet('Invoices'),
'resizeRows': 2,
'startingRowIndexToClear': 0,
'resizeColumns': 1
},
]
myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
myGspreadFunc.displayArray(
spreadsheetLevelObj.worksheet('Invoices'), invoicesArray)
# myGspreadFunc.setFiltersOnSpreadsheet(spreadsheetLevelObj)
myGspreadFunc.autoAlignColumnsInSpreadsheet(
spreadsheetLevelObj)
elif arrayOfArguments[4] == 'From Local File':
with open(arrayOfArguments[5], 'w') as filehandle:
json.dump(invoicesArray, filehandle)