def getFilenamesFromGoogleSheets(pathToDirectoryOfFiles,
googleAccountUsername):
accountLevelObj = myGspreadFunc.getSpreadsheetLevelObj(
True,
pathToThisPythonFile,
googleAccountUsername=googleAccountUsername)
spreadsheetLevelObj = accountLevelObj.open('Vendor Rebates')
sheetLevelObj = spreadsheetLevelObj.worksheet('filenamesToWrite')
filenamesToWriteArray = sheetLevelObj.get_all_values()
for filename in filenamesToWriteArray[1:]:
if filename[2] == '':
oldFilenamePath = Path(pathToDirectoryOfFiles,
' - '.join(filename[0:2]) + '.pdf')
else:
oldFilenamePath = Path(pathToDirectoryOfFiles,
' - '.join(filename[0:3]) + '.pdf')
# if oldFilenamePath.exists():
# p('True')
# else:
# p('False')
newFilenamePath = Path(
pathToDirectoryOfFiles,
filename[0] + ' - ' + filename[1] + ' - ' + filename[3] + '.pdf')
# command = 'mv "' + str(oldFilenamePath) + '" "' + str(newFilenamePath) + '"'
# p(command)
oldFilenamePath.rename(newFilenamePath)
def getFilenamesFromDisk(googleAccountUsername,
pathToDirectoryOfFilesForNameExtraction):
# p(Path(pathToDirectoryOfFilesForNameExtraction).parents[3])
extractedFilenamesToDisplay = [['Check Date', 'Vendor', 'Amount']]
for node in Path(pathToDirectoryOfFilesForNameExtraction).iterdir():
arrayToAppend = node.stem.split(' - ')
arrayToAppend[2] = float(arrayToAppend[2])
extractedFilenamesToDisplay.append(arrayToAppend)
accountLevelObj = myGspreadFunc.getSpreadsheetLevelObj(
True,
pathToThisPythonFile,
googleAccountUsername=googleAccountUsername)
spreadsheetLevelObj = accountLevelObj.open('Vendor Rebates')
sheetLevelObj = spreadsheetLevelObj.worksheet('extractedFilenames')
clearAndResizeParameters = [{
'sheetObj': sheetLevelObj,
'resizeRows': 2,
'startingRowIndexToClear': 0,
'resizeColumns': 1
}]
myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
myGspreadFunc.displayArray(sheetLevelObj, extractedFilenamesToDisplay)
# p(extractedFilenamesToDisplay)
sheetLevelObj.format(
'C:C', {'numberFormat': {
'type': 'NUMBER',
'pattern': '#,###.00'
}})
myGspreadFunc.autoAlignColumnsInSpreadsheet(spreadsheetLevelObj)
def reconcileArrays(oAuthMode, googleAccountUsername, googleSheetTitle):
pathBelowRepos = pathToThisPythonFile
spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(
oAuthMode, pathBelowRepos,
googleAccountUsername=googleAccountUsername).open(googleSheetTitle)
endingExtractedFilenamesSheet = spreadsheetLevelObj.worksheet(
'endingExtractedFilenames')
comparisonSheet = spreadsheetLevelObj.worksheet('Comparison')
extractedFilenames = spreadsheetLevelObj.worksheet(
'extractedFilenames').get_all_values()
gpTransactions = spreadsheetLevelObj.worksheet(
'gpTransactions').get_all_values()
gpTransactionsDebitColumnIndex = 5
gpTransactionsCreditColumnIndex = gpTransactionsDebitColumnIndex + 1
gpTransactionsAmountColumnIndex = 12
gpTransactionsVendorColumnIndex = 9
gpTransactionsDateColumnIndex = 2
spacingColumnIndex = gpTransactionsAmountColumnIndex + 1
extractedFilenamesAmountColumnIndex = 2
extractedFilenamesVendorColumnIndex = 1
extractedFilenamesDateColumnIndex = 0
def mapGPTransactions(currentRowIndex, currentRow):
if currentRowIndex == 0:
currentRow.append('Amount')
else:
currentRow.append(
float(currentRow[gpTransactionsDebitColumnIndex].replace(
',', '')) -
float(currentRow[gpTransactionsCreditColumnIndex].replace(
',', '')))
currentRow[gpTransactionsDateColumnIndex] = myPyFunc.dateStrToStr(
currentRow[gpTransactionsDateColumnIndex])
for column in [
gpTransactionsDebitColumnIndex,
gpTransactionsCreditColumnIndex,
gpTransactionsAmountColumnIndex
]:
if isinstance(currentRow[column], str):
currentRow[column] = float(currentRow[column].replace(
',', ''))
gpTransactions = myPyFunc.mapArray(mapGPTransactions, gpTransactions)
gpTransactionsFirstRow = gpTransactions.pop(0)
def mapExtractedFilenames(currentRowIndex, currentRow):
if currentRowIndex > 0:
currentRow[extractedFilenamesAmountColumnIndex] = -float(
currentRow[extractedFilenamesAmountColumnIndex].replace(
',', ''))
# currentRowDateStr = currentRow[extractedFilenamesDateColumnIndex]
# p(currentRowDateStr[6:8])
# currentRowDateObj = datetime(int(currentRowDate[0:4]), int(currentRowDate[6:8]), int(currentRowDate[4:6]))
# currentRow[extractedFilenamesDateColumnIndex] = f"{currentRowDateObj.day}/{currentRowDateObj.month}/{currentRowDateObj.strftime('%y')}"
extractedFilenames = myPyFunc.mapArray(mapExtractedFilenames,
extractedFilenames)
extractedFilenamesFirstRow = extractedFilenames.pop(0)
comparedTransactions = [
['GP Transactions'] + [''] * (len(gpTransactionsFirstRow)) +
['Extracted Filenames'] + [''] * (len(extractedFilenamesFirstRow) - 1)
]
comparedTransactions.append(gpTransactionsFirstRow + ['Match Status'] +
extractedFilenamesFirstRow)
while gpTransactions:
gpTransactionsCurrentRow = gpTransactions.pop(0)
rowToAppend = gpTransactionsCurrentRow + ['']
extractedFilenamesCurrentRowIndex = 0
while extractedFilenamesCurrentRowIndex in range(
0,
len(extractedFilenames) -
1) and len(rowToAppend) == len(gpTransactionsCurrentRow) + 1:
extractedFilenamesCurrentRow = extractedFilenames[
extractedFilenamesCurrentRowIndex]
if gpTransactionsCurrentRow[
gpTransactionsAmountColumnIndex] == extractedFilenamesCurrentRow[
extractedFilenamesAmountColumnIndex] and gpTransactionsCurrentRow[
gpTransactionsVendorColumnIndex] == extractedFilenamesCurrentRow[
extractedFilenamesVendorColumnIndex] and gpTransactionsCurrentRow[
gpTransactionsDateColumnIndex] == extractedFilenamesCurrentRow[
extractedFilenamesDateColumnIndex]:
extractedFilenamesRowToAppend = extractedFilenames.pop(
extractedFilenamesCurrentRowIndex)
rowToAppend = rowToAppend + extractedFilenamesRowToAppend
rowToAppend[
spacingColumnIndex] = 'Matched on amount, vendor, and date'
extractedFilenamesCurrentRowIndex = extractedFilenamesCurrentRowIndex + 1
comparedTransactions.append(rowToAppend)
for comparedTransactionsCurrentRowIndex, comparedTransactionsCurrentRow in enumerate(
comparedTransactions):
if len(comparedTransactionsCurrentRow
) == len(gpTransactionsFirstRow) + 1:
gpTransactionsRowsThatMatchComparedTransactionsCurrentRow = []
for extractedFilenamesCurrentRowIndex, extractedFilenamesCurrentRow in enumerate(
extractedFilenames):
if comparedTransactionsCurrentRow[
gpTransactionsAmountColumnIndex] == extractedFilenamesCurrentRow[
extractedFilenamesAmountColumnIndex] and comparedTransactionsCurrentRow[
gpTransactionsVendorColumnIndex] == extractedFilenamesCurrentRow[
extractedFilenamesVendorColumnIndex]:
gpTransactionsRowsThatMatchComparedTransactionsCurrentRow.append(
{
'extractedFilenamesRowIndex':
extractedFilenamesCurrentRowIndex,
'extractedFilenamesRow':
extractedFilenamesCurrentRow
})
if len(gpTransactionsRowsThatMatchComparedTransactionsCurrentRow
) == 1:
comparedTransactions[
comparedTransactionsCurrentRowIndex] = comparedTransactions[
comparedTransactionsCurrentRowIndex] + extractedFilenames.pop(
gpTransactionsRowsThatMatchComparedTransactionsCurrentRow[
0]['extractedFilenamesRowIndex'])
comparedTransactions[comparedTransactionsCurrentRowIndex][
spacingColumnIndex] = 'Matched on amount and vendor'
for comparedTransactionsCurrentRowIndex, comparedTransactionsCurrentRow in enumerate(
comparedTransactions):
if len(comparedTransactionsCurrentRow
) == len(gpTransactionsFirstRow) + 1:
gpTransactionsRowsThatMatchComparedTransactionsCurrentRow = []
for extractedFilenamesCurrentRowIndex, extractedFilenamesCurrentRow in enumerate(
extractedFilenames):
if comparedTransactionsCurrentRow[
gpTransactionsAmountColumnIndex] == extractedFilenamesCurrentRow[
extractedFilenamesAmountColumnIndex]:
gpTransactionsRowsThatMatchComparedTransactionsCurrentRow.append(
{
'extractedFilenamesRowIndex':
extractedFilenamesCurrentRowIndex,
'extractedFilenamesRow':
extractedFilenamesCurrentRow
})
if len(gpTransactionsRowsThatMatchComparedTransactionsCurrentRow
) == 1:
comparedTransactions[
comparedTransactionsCurrentRowIndex] = comparedTransactions[
comparedTransactionsCurrentRowIndex] + extractedFilenames.pop(
gpTransactionsRowsThatMatchComparedTransactionsCurrentRow[
0]['extractedFilenamesRowIndex'])
comparedTransactions[comparedTransactionsCurrentRowIndex][
spacingColumnIndex] = 'Matched on amount'
clearAndResizeParameters = [{
'sheetObj': comparisonSheet,
'resizeRows': 3,
'startingRowIndexToClear': 0,
'resizeColumns': 1
}, {
'sheetObj': endingExtractedFilenamesSheet,
'resizeRows': 2,
'startingRowIndexToClear': 0,
'resizeColumns': 1
}]
myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
extractedFilenames.insert(0, extractedFilenamesFirstRow)
myGspreadFunc.displayArray(endingExtractedFilenamesSheet,
extractedFilenames)
myGspreadFunc.displayArray(comparisonSheet, comparedTransactions)
myGspreadFunc.setFiltersOnSpreadsheet(spreadsheetLevelObj,
{'Comparison': 2})
numberFormatObj = {
'numberFormat': {
'type': 'NUMBER',
'pattern': '#,##0.00;(#,##0.00)'
}
}
formatParameters = [{
'sheetName': 'Comparison',
'formatRanges': ['F:F', 'G:G', 'M:M', 'Q:Q'],
'formatObj': numberFormatObj
}, {
'sheetName': 'endingExtractedFilenames',
'formatRanges': ['C:C'],
'formatObj': numberFormatObj
}]
myGspreadFunc.updateFormatting(spreadsheetLevelObj, formatParameters)
myGspreadFunc.autoAlignColumnsInSpreadsheet(spreadsheetLevelObj)
def reconcileArraysBankRec(arrayOfArguments):
# p(arrayOfArguments)
googleAccountUsername = arrayOfArguments[1]
bankAccount = arrayOfArguments[2]
pathStrToAuthorizedUserFile = arrayOfArguments[3]
pathStrToJSONCredentialsFile = arrayOfArguments[4]
# p(pathStrToAuthorizedUserFile)
googleSheetTitle = 'Bank Rec ' + bankAccount
pathBelowRepos = pathToThisPythonFile
spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(
True,
pathStrToAuthorizedUserFile,
googleAccountUsername=googleAccountUsername,
pathStrToJSONCredentialsFile=pathStrToJSONCredentialsFile).open(
googleSheetTitle)
dteStrColNme = 'New Date'
newAmtColName = 'New Amount'
gpAmtColIdx = 5
gpTypColIdx = 11
gpNumColIdx = 12
gpDateStrColIdx = 19
gpNewAmtColIdx = 20
gpNme = 'GP'
bankNme = 'Bank'
dlyDepNme = 'Daily Deposits'
mtchdNme = 'Matched'
notMtchdNme = 'Did Not Match'
def getGPArrayForRecon(spreadsheetLevelObj):
gpArray = spreadsheetLevelObj.worksheet(gpNme).get_all_values()
def filterOnPopulatedDates(currentRow):
gpDteColIdx = 1
if currentRow[gpDteColIdx] != '':
return True
return False
gpArray = list(filter(filterOnPopulatedDates, gpArray))
def mapGPArray(currentRowIndex, currentRow):
gpDteColIdx = 1
gpTypColIdx = 11
gpPdToColIdx = 14
if currentRowIndex:
currentType = currentRow[gpTypColIdx]
currentPaidTo = currentRow[gpPdToColIdx]
currentAmount = myPyFunc.strToFloat(currentRow[gpAmtColIdx])
if currentType in [
'Decrease Adjustment', 'Check', 'Withdrawl'
] or 'Transfer To' in currentPaidTo:
currentAmount = -currentAmount
gpTypObj = {
'Check': 2,
'Decrease Adjustment': 5,
'Deposit': 1,
'Increase Adjustment': 4,
'Withdrawl': 3,
'Interest Income': 8
}
newGPTyp = str(gpTypObj[
currentRow[gpTypColIdx]]) + ' - ' + currentRow[gpTypColIdx]
if currentRow[gpPdToColIdx]:
if currentRow[gpPdToColIdx][:11] == 'Transfer To':
newGPTyp = '6 - Transfer Out'
elif currentRow[gpPdToColIdx][:13] == "Transfer From":
newGPTyp = '7 - Transfer In'
for columnToAppend in [
newGPTyp, currentRow[gpNumColIdx],
myPyFunc.dateStrToStr(currentRow[gpDteColIdx]),
currentAmount
]:
currentRow.append(columnToAppend)
else:
for columnToAppend in [
'New CM Trx Type', 'New CM Trx Number', dteStrColNme,
newAmtColName
]:
currentRow.append(columnToAppend)
return currentRow
return myPyFunc.mapArray(mapGPArray, gpArray)
gpArray = getGPArrayForRecon(spreadsheetLevelObj)
gpArrayFirstRow = gpArray.pop(0)
bankArray = spreadsheetLevelObj.worksheet(bankNme).get_all_values()
def getBankArrayForReconPrimary(bankArray):
bankStatusColIdx = 0
bankDteColIdx = 1
bankDrCrColIdx = 8
def writeNotesToBankArray(bankArray):
bankNotesColIdx = 13
notMtchdArray = spreadsheetLevelObj.worksheet(
notMtchdNme).get_all_values()
for notMtchdArrayRow in notMtchdArray:
for bankArrayRow in bankArray:
if rowsMatch(notMtchdArrayRow, bankArrayRow,
bankNotesColIdx):
bankArrayRow[bankNotesColIdx] = notMtchdArrayRow[
bankNotesColIdx]
return bankArray
# p(arrayOfArguments)
# if arrayOfArguments[3] == 'copyNotes': p(1)
if len(arrayOfArguments) > 3 and arrayOfArguments[3] == 'copyNotes':
bankArray = writeNotesToBankArray(bankArray)
def filterBankArrayPrimary(currentRow):
if currentRow[bankStatusColIdx] not in [
'H', 'B', 'T'
] and currentRow[bankTypColIdx] not in [
'Data', 'Ledger Balance', 'Collected + 1 Day',
'Opening Collected', 'One Day Float', '2 Day Float',
'3 Day + Float', 'MTD Avg Collected',
'MTD Avg Neg Collected', 'Total Credits',
'Number of Credits', 'Total Debits', 'Number of Debits',
'Float Adjustment(s)'
]:
return True
return False
bankArray = list(filter(filterBankArrayPrimary, bankArray))
def mapBankArrayPrimary(currentRowIndex, currentRow):
if currentRowIndex:
currentAmount = myPyFunc.strToFloat(currentRow[bankAmtColIdx])
currentDate = currentRow[bankDteColIdx]
lengthOfDateStr = 8
lengthOfMonthAndDay = 4
if len(currentDate) < lengthOfDateStr:
currentDate = '0' + currentDate
currentDate = currentDate[
lengthOfMonthAndDay:
lengthOfDateStr] + currentDate[:lengthOfMonthAndDay]
if currentRow[bankDrCrColIdx] == 'Debit':
currentAmount = -currentAmount
currentRow[bankThrdDescColIdx] = currentRow[
bankThrdDescColIdx].replace('\n', '')
for columnToAppend in [currentAmount, currentDate]:
currentRow.append(columnToAppend)
else:
for columnToAppend in [newAmtColName, dteStrColNme]:
currentRow.append(columnToAppend)
return myPyFunc.mapArray(mapBankArrayPrimary, bankArray)
def getDailyDepositsArrayForReconPrimary():
dailyDepositsArray = spreadsheetLevelObj.worksheet(
dlyDepNme).get_all_values()
def mapDailyDeposits(currentRowIndex, currentRow):
if currentRowIndex:
currentRow[dlyDepNetAmtColIdx] = myPyFunc.strToFloat(
currentRow[dlyDepNetAmtColIdx])
return currentRow
return myPyFunc.mapArray(mapDailyDeposits, dailyDepositsArray)
def getBankArrayForReconSecondary(bankArray):
bankDteColIdx = 0
bankDrColIdx = 5
bankCrColIdx = 6
def mapBankArraySecondary(currentRowIndex, currentRow):
if currentRowIndex:
currentDebitAmount = currentRow[bankDrColIdx]
currentCreditAmount = currentRow[bankCrColIdx]
currentDate = currentRow[bankDteColIdx]
for columnToAppend in [
myPyFunc.strToFloat(currentCreditAmount) -
myPyFunc.strToFloat(currentDebitAmount),
myPyFunc.dateStrToStr(currentDate)
]:
currentRow.append(columnToAppend)
else:
for columnToAppend in [newAmtColName, dteStrColNme]:
currentRow.append(columnToAppend)
return currentRow
return myPyFunc.mapArray(mapBankArraySecondary, bankArray)
# def mapDailyDepositsArray(currentRowIndex, currentRow):
# if currentRowIndex == 0:
# currentRow.append('BisTrack Amount')
# currentRow.append('Bank Amount')
# else:
# currentType = currentRow[2]
# def getBistrackAmount():
# currentBistrackAmount = currentRow[6]
# return myPyFunc.ifConditionFlipSign(myPyFunc.strToFloat(currentBistrackAmount), currentType, 'Debit')
# def getBankAmount():
# currentBankAmount = currentRow[4]
# return myPyFunc.ifConditionFlipSign(myPyFunc.strToFloat(currentBankAmount), currentType, 'Debit')
# currentDate = currentRow[0]
# dateToAppend = None
# if currentDate != '':
# dateToAppend = currentDate
# currentRow.append(dateToAppend)
# currentRow.append(getBistrackAmount())
# currentRow.append(getBankAmount())
# dailyDepositsArray = spreadsheetLevelObj.worksheet(dailyDepositsSheetName).get_all_values()
# dailyDepositsArray = myPyFunc.mapArray(mapDailyDepositsArray, dailyDepositsArray)
if bankAccount == 'Primary':
bankTypColIdx = 7
bankAmtColIdx = 9
bankThrdDescColIdx = 12
dlyDepNetAmtColIdx = 5
bankArray = getBankArrayForReconPrimary(bankArray)
# p([row for row in bankArray if row[14] == 14771.6 or row[14] == -14771.6])
dailyDepositsArray = getDailyDepositsArrayForReconPrimary()
elif bankAccount == 'Secondary':
bankNewAmtColIdx = 7
bankDteStrColIdx = 8
bankArray = getBankArrayForReconSecondary(bankArray)
bankArrayFirstRow = bankArray.pop(0)
matchedArray = [[gpNme] + [''] * (len(gpArrayFirstRow) - 1) + [''] +
[bankNme] + [''] * (len(bankArrayFirstRow) - 1)]
matchedArray.append(gpArrayFirstRow + [''] + bankArrayFirstRow)
def createFilterOnUnmatchedRows(matchedArrayCurrentRow):
def filterOnUnmatchedRows(arrayToFilterCurrentRow):
if len(arrayToFilterCurrentRow) == len(
gpArrayFirstRow
) and arrayToFilterCurrentRow[
gpNewAmtColIdx] == matchedArrayCurrentRow[gpNewAmtColIdx]:
return True
return False
return filterOnUnmatchedRows
def getMatchedArrayPrimary(matchedArray):
amountComparisonFunction = myPyFunc.getColumnComparisonFunction(
gpNewAmtColIdx, bankNewAmtColIdx)
dateComparisonFunction = myPyFunc.getColumnComparisonFunction(
gpDateStrColIdx, bankDteStrColIdx)
trxNumComparisonFunction = myPyFunc.getColumnComparisonFunction(
gpNumColIdx, bankScndDescColIdx)
lengthOfCheckNumber = 5
def typeComparisonFunction(firstArrayCurrentRow, bankArrayCurrentRow):
if firstArrayCurrentRow[gpTypColIdx] == bankArrayCurrentRow[
bankTypColIdx][:lengthOfCheckNumber]:
return True
return False
def rowForMatchedArrayOnAmountTrxNumType(gpArrayCurrentRow):
rowToReturn = gpArrayCurrentRow
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst([
amountComparisonFunction, trxNumComparisonFunction,
typeComparisonFunction
], gpArrayCurrentRow, bankArray)
if len(rowIndicesThatMatch) == 1:
filterFieldsForMatchStatus = myPyFunc.getFilterByIndexFunction(
[gpNewAmtColIdx, gpDateStrColIdx, gpTypColIdx])
rowToReturn.extend([
myPyFunc.getMatchStatus(
myPyFunc.filterArray(filterFieldsForMatchStatus,
gpArrayFirstRow))
] + bankArray.pop(rowIndicesThatMatch[0]))
elif len(rowIndicesThatMatch) > 1:
p('More than one row matches on the first pass')
return rowToReturn
myPyFunc.transferToArray(gpArray, matchedArray,
rowForMatchedArrayOnAmountTrxNumType)
def checkComparisonFunction(firstArrayCurrentRow, bankArrayCurrentRow):
if firstArrayCurrentRow[gpTypColIdx] != 'Check' or (
firstArrayCurrentRow[gpTypColIdx] == 'Check'
and len(firstArrayCurrentRow[gpNumColIdx]) !=
lengthOfCheckNumber):
return True
return False
def rowForMatchedArrayOnAmountDateNotCheck(matchedArrayCurrentRow):
if len(matchedArrayCurrentRow) == len(gpArrayFirstRow):
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst(
[
amountComparisonFunction, dateComparisonFunction,
checkComparisonFunction
], matchedArrayCurrentRow, bankArray)
filterByCurrentAmount = createFilterOnUnmatchedRows(
matchedArrayCurrentRow)
if len(rowIndicesThatMatch) == 1 or len(
rowIndicesThatMatch) == len(
list(filter(filterByCurrentAmount, matchedArray))):
filterFieldsForMatchStatus = myPyFunc.getFilterByIndexFunction(
[gpNewAmtColIdx, gpDateStrColIdx])
return matchedArrayCurrentRow + [
myPyFunc.getMatchStatus(
myPyFunc.filterArray(filterFieldsForMatchStatus,
gpArrayFirstRow))
] + bankArray.pop(rowIndicesThatMatch[0])
return matchedArrayCurrentRow
matchedArray = list(
map(rowForMatchedArrayOnAmountDateNotCheck, matchedArray))
def rowForMatchedArrayOnAmountNotCheck(matchedArrayCurrentRow):
if len(matchedArrayCurrentRow) == len(gpArrayFirstRow):
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst(
[amountComparisonFunction, checkComparisonFunction],
matchedArrayCurrentRow, bankArray)
filterByCurrentAmount = createFilterOnUnmatchedRows(
matchedArrayCurrentRow)
if len(rowIndicesThatMatch) == 1 or len(
rowIndicesThatMatch) == len(
list(filter(filterByCurrentAmount, matchedArray))):
filterFieldsForMatchStatus = myPyFunc.getFilterByIndexFunction(
[gpNewAmtColIdx])
return matchedArrayCurrentRow + [
myPyFunc.getMatchStatus(
myPyFunc.filterArray(filterFieldsForMatchStatus,
gpArrayFirstRow))
] + bankArray.pop(rowIndicesThatMatch[0])
return matchedArrayCurrentRow
matchedArray = list(
map(rowForMatchedArrayOnAmountNotCheck, matchedArray))
def rowForMatchedArrayOnAmountDate(matchedArrayCurrentRow):
if len(matchedArrayCurrentRow) == len(gpArrayFirstRow):
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst(
[amountComparisonFunction, dateComparisonFunction],
matchedArrayCurrentRow, bankArray)
filterByCurrentAmount = createFilterOnUnmatchedRows(
matchedArrayCurrentRow)
if len(rowIndicesThatMatch) == 1 or len(
rowIndicesThatMatch) == len(
list(filter(filterByCurrentAmount, matchedArray))):
return matchedArrayCurrentRow + [
'Matched On New Amount and Date Str, ignoring transaction type'
] + bankArray.pop(rowIndicesThatMatch[0])
return matchedArrayCurrentRow
matchedArray = list(map(rowForMatchedArrayOnAmountDate, matchedArray))
def rowForMatchedArrayAmount(matchedArrayCurrentRow):
if len(matchedArrayCurrentRow) == len(gpArrayFirstRow):
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst(
[amountComparisonFunction], matchedArrayCurrentRow,
bankArray)
filterByCurrentAmount = createFilterOnUnmatchedRows(
matchedArrayCurrentRow)
if len(rowIndicesThatMatch) == 1 or len(
rowIndicesThatMatch) == len(
list(filter(filterByCurrentAmount, matchedArray))):
return matchedArrayCurrentRow + [
'Matched On New Amount, ignoring transaction type'
] + bankArray.pop(rowIndicesThatMatch[0])
return matchedArrayCurrentRow
matchedArray = list(map(rowForMatchedArrayAmount, matchedArray))
def rowForMatchedArrayOnTrxNumTypeNotAmount(matchedArrayCurrentRow):
if len(matchedArrayCurrentRow) == len(gpArrayFirstRow):
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst(
[trxNumComparisonFunction, typeComparisonFunction],
matchedArrayCurrentRow, bankArray)
filterByCurrentAmount = createFilterOnUnmatchedRows(
matchedArrayCurrentRow)
if len(rowIndicesThatMatch) == 1 or len(
rowIndicesThatMatch) == len(
list(filter(filterByCurrentAmount, matchedArray))):
return matchedArrayCurrentRow + [
'AMOUNT DOESN\'T MATCH! Matched trx number and type, but not amount'
] + bankArray.pop(rowIndicesThatMatch[0])
return matchedArrayCurrentRow
matchedArray = list(
map(rowForMatchedArrayOnTrxNumTypeNotAmount, matchedArray))
def rowForMatchedArrayOnDlyDepBistrackID(matchedArrayCurrentRow):
if len(matchedArrayCurrentRow) == len(gpArrayFirstRow):
def dailyDepBistrackIDComparisonFunction(
matchedArrayCurrentRow, dailyDepositsArrayCurrentRow):
lengthOfBistrackID = 5
lengthOfBistrackPrefix = 2
dlyDepTrxIdColIdx = 7
if matchedArrayCurrentRow[gpNumColIdx][
lengthOfBistrackPrefix:lengthOfBistrackID +
lengthOfBistrackPrefix] == dailyDepositsArrayCurrentRow[
dlyDepTrxIdColIdx]:
return True
return False
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst(
[dailyDepBistrackIDComparisonFunction],
matchedArrayCurrentRow, dailyDepositsArray)
filterByCurrentAmount = createFilterOnUnmatchedRows(
matchedArrayCurrentRow)
if len(rowIndicesThatMatch) == 1 or len(
rowIndicesThatMatch) == len(
list(filter(filterByCurrentAmount, matchedArray))):
return matchedArrayCurrentRow + [
'AMOUNT DOESN\'T MATCH! Matched to Daily Deposits on Bistrack ID'
] + dailyDepositsArray.pop(rowIndicesThatMatch[0])
return matchedArrayCurrentRow
matchedArray = list(
map(rowForMatchedArrayOnDlyDepBistrackID, matchedArray))
return matchedArray
def getMatchedArraySecondary(matchedArray):
amountComparisonFunction = myPyFunc.getColumnComparisonFunction(
gpNewAmtColIdx, bankNewAmtColIdx)
def rowForMatchedArrayOnAmountDate(gpArrayCurrentRow):
rowToReturn = gpArrayCurrentRow
dateComparisonFunction = myPyFunc.getColumnComparisonFunction(
gpDateStrColIdx, bankDteStrColIdx)
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst(
[amountComparisonFunction, dateComparisonFunction],
gpArrayCurrentRow, bankArray)
if len(rowIndicesThatMatch) == 1:
filterFieldsForMatchStatus = myPyFunc.getFilterByIndexFunction(
[gpNewAmtColIdx, gpDateStrColIdx])
rowToReturn.extend([
myPyFunc.getMatchStatus(
myPyFunc.filterArray(filterFieldsForMatchStatus,
gpArrayFirstRow))
] + bankArray.pop(rowIndicesThatMatch[0]))
elif len(rowIndicesThatMatch) > 1:
p('More than one row matches on the first pass')
for rowIndexThatMatches in rowIndicesThatMatch:
p(bankArray[rowIndexThatMatches])
return rowToReturn
myPyFunc.transferToArray(gpArray, matchedArray,
rowForMatchedArrayOnAmountDate)
def rowForMatchedArrayOnAmount(matchedArrayCurrentRow):
if len(matchedArrayCurrentRow) == len(gpArrayFirstRow):
rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst(
[amountComparisonFunction], matchedArrayCurrentRow,
bankArray)
filterByCurrentAmount = createFilterOnUnmatchedRows(
matchedArrayCurrentRow)
if len(rowIndicesThatMatch) == 1 or len(
rowIndicesThatMatch) == len(
list(filter(filterByCurrentAmount, matchedArray))):
filterFieldsForMatchStatus = myPyFunc.getFilterByIndexFunction(
[gpNewAmtColIdx])
return matchedArrayCurrentRow + [
myPyFunc.getMatchStatus(
myPyFunc.filterArray(filterFieldsForMatchStatus,
gpArrayFirstRow))
] + bankArray.pop(rowIndicesThatMatch[0])
return matchedArrayCurrentRow
matchedArray = list(map(rowForMatchedArrayOnAmount, matchedArray))
# # def addMatchesFromDailyDepositsArray(currentRowIndex, currentRow):
# # if len(currentRow) == len(gpArrayFirstRow):
# # rowsThatMatch = getMatchedDailyDepositsRows(dailyDepositsArray, currentRow)
# # if len(rowsThatMatch) == 1:
# # pass
# # matchedArray = myPyFunc.mapArray(addMatchesFromDailyDepositsArray, matchedArray)
return matchedArray
if bankAccount == 'Primary':
bankScndDescColIdx = 11
bankNewAmtColIdx = 14
bankDteStrColIdx = 15
matchedArray = getMatchedArrayPrimary(matchedArray)
elif bankAccount == 'Secondary':
matchedArray = getMatchedArraySecondary(matchedArray)
bankArray.insert(0, bankArrayFirstRow)
clearAndResizeParameters = [{
'sheetObj':
spreadsheetLevelObj.worksheet(mtchdNme),
'resizeRows':
3,
'startingRowIndexToClear':
0,
'resizeColumns':
3
}, {
'sheetObj':
spreadsheetLevelObj.worksheet(notMtchdNme),
'resizeRows':
2,
'startingRowIndexToClear':
0,
'resizeColumns':
3
}]
myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
myGspreadFunc.displayArray(spreadsheetLevelObj.worksheet(mtchdNme),
matchedArray)
myGspreadFunc.displayArray(spreadsheetLevelObj.worksheet(notMtchdNme),
bankArray)
topRowsParameters = {mtchdNme: 2}
myGspreadFunc.setFiltersOnSpreadsheet(spreadsheetLevelObj,
topRowsParameters)
gpFormatParameters = {
gpNme: [{
'range': ['F:F', 'G:G'],
'format': 'currencyWithoutSymbol'
}]
}
myGspreadFunc.setFormattingOnSpreadsheet(spreadsheetLevelObj,
gpFormatParameters)
if bankAccount == 'Primary':
bankFormatParameters = [{
'range': ['J:J'],
'format': 'currencyWithoutSymbol'
}]
allFormatParameters = {
bankNme:
bankFormatParameters,
dlyDepNme: [{
'range': ['F:F'],
'format': 'currencyWithoutSymbol'
}],
mtchdNme:
gpFormatParameters[gpNme] + [{
'range': ['U:U', 'AF:AF', 'AK:AK'],
'format': 'currencyWithoutSymbol'
}],
notMtchdNme:
bankFormatParameters + [{
'range': ['O:O'],
'format': 'currencyWithoutSymbol'
}]
}
myGspreadFunc.setFormattingOnSpreadsheet(spreadsheetLevelObj,
allFormatParameters)
myGspreadFunc.autoAlignColumnsInSpreadsheet(spreadsheetLevelObj)
if bankAccount == 'Primary':
columnWidthParameters = [{
'sheetName':
mtchdNme,
'columnToAdjust':
len(gpArrayFirstRow) + 1 + bankThrdDescColIdx
}, {
'sheetName': notMtchdNme,
'columnToAdjust': bankThrdDescColIdx
}]
columnWidthRequest = {"requests": []}
for sheetInfo in columnWidthParameters:
columnWidthRequest['requests'].append({
"updateDimensionProperties": {
"range": {
"sheetId":
spreadsheetLevelObj.worksheet(
sheetInfo['sheetName'])._properties['sheetId'],
"dimension":
"COLUMNS",
"startIndex":
sheetInfo['columnToAdjust'],
"endIndex":
sheetInfo['columnToAdjust'] + 1
},
"properties": {
"pixelSize": 400
},
"fields": "pixelSize"
}
})
columnWidthRequest['requests'].append({
"repeatCell": {
"range": {
"sheetId":
spreadsheetLevelObj.worksheet(
sheetInfo['sheetName'])._properties['sheetId'],
"startColumnIndex":
sheetInfo['columnToAdjust'],
"endColumnIndex":
sheetInfo['columnToAdjust'] + 1
},
"cell": {
"userEnteredFormat": {
"wrapStrategy": "CLIP",
}
},
"fields": "userEnteredFormat(wrapStrategy)"
}
})
# import json
# p(json.dumps(columnWidthRequest, indent=4, sort_keys=True))
# p(columnWidthRequest)
spreadsheetLevelObj.batch_update(columnWidthRequest)
def reconcileArrays(oAuthMode, googleSheetTitle, googleAccountUsername=None):
pathToRepos = myPyFunc.getPathUpFolderTree(pathToThisPythonFile, 'repos')
pathToThisProjectRoot = pathToThisPythonFile.parents[3]
gspObj = myGspreadFunc.getSpreadsheetLevelObj(
oAuthMode,
pathToThisProjectRoot,
googleAccountUsername=googleAccountUsername)
spacingColumnIndex = 18
gspSpreadsheet = gspObj.open(googleSheetTitle)
gspGL = gspSpreadsheet.worksheet('GL')
gspCheckbook = gspSpreadsheet.worksheet('Checkbook')
gspComparison = gspSpreadsheet.worksheet('Comparison')
gspEnding = gspSpreadsheet.worksheet('Ending')
checkbookTrxDateColumnIndex = 1
checkbookAmountColumnIndex = 5
checkbookTrxTypeColumnIndex = 11
checkbookTrxNumberColumnIndex = 12
checkbookPaidToReceivedFromColumnIndex = 14
checkbookTransferColumnIndex = 17
checkbookArray = gspCheckbook.get_all_values()
checkbookArray = [
currentRow for currentRow in checkbookArray
if currentRow[checkbookTrxDateColumnIndex] not in ['']
]
glArray = gspGL.get_all_values()
# glStatusCol = 0
# glTransactionTypeColumnIndex = 7
# glAmountColumnIndex = 9
# glDescriptionTwoColumnIndex = 11
glTrxDateColumnIndex = 2
glDebitColumnIndex = 5
glCreditColumnIndex = 6
glAmountColumnIndex = len(glArray[0])
def addAmountColumn(currentRow):
if currentRow[glTrxDateColumnIndex] != 'TRX Date':
currentRow[glTrxDateColumnIndex] = str(
datetime.strptime(currentRow[glTrxDateColumnIndex],
'%m/%d/%Y'))
try:
currentRow.append(
float(currentRow[glDebitColumnIndex].replace(',', '')) -
float(currentRow[glCreditColumnIndex].replace(',', '')))
except:
currentRow.append('')
return currentRow
glArray = [addAmountColumn(currentRow) for currentRow in glArray]
glArray[0][len(glArray[0]) - 1] = 'Amount'
for currentRowIndex, currentRow in enumerate(checkbookArray):
if currentRowIndex == 0:
currentRow.append('Transfer')
else:
currentRow[checkbookAmountColumnIndex] = float(
currentRow[checkbookAmountColumnIndex].replace(',', ''))
currentRow[checkbookTrxDateColumnIndex] = str(
datetime.strptime(currentRow[checkbookTrxDateColumnIndex],
'%m/%d/%Y'))
if currentRow[checkbookPaidToReceivedFromColumnIndex]:
if currentRow[checkbookPaidToReceivedFromColumnIndex][
0:11] == 'Transfer To':
currentRow.append('Out')
if currentRow[checkbookPaidToReceivedFromColumnIndex][
0:13] == "Transfer From":
currentRow.append('In')
if len(currentRow) == checkbookTransferColumnIndex:
currentRow.append('')
if currentRow[checkbookTrxTypeColumnIndex]:
if not currentRow[checkbookTransferColumnIndex]:
if currentRow[checkbookTrxTypeColumnIndex] in [
'Increase Adjustment', 'Deposit'
]:
currentRow[checkbookTransferColumnIndex] = "In"
if currentRow[checkbookTrxTypeColumnIndex] in [
'Decrease Adjustment', 'Withdrawl', 'Check'
]:
currentRow[checkbookTransferColumnIndex] = "Out"
if currentRow[checkbookTransferColumnIndex] == 'Out':
currentRow[checkbookAmountColumnIndex] = -currentRow[
checkbookAmountColumnIndex]
checkbookFirstRow = checkbookArray.pop(0)
glFirstRow = glArray.pop(0)
comparisonArray = [['Checkbook'] + [''] * (len(checkbookArray[0])) +
['GL'] + [''] * (len(glArray[0]) - 1)]
comparisonArray.append(checkbookFirstRow + ['Match Status'] + glFirstRow)
while checkbookArray:
checkbookCurrentRow = checkbookArray.pop(0)
rowToAppend = checkbookCurrentRow + ['']
glCurrentRowIndex = 0
while glCurrentRowIndex in range(
0,
len(glArray) -
1) and len(rowToAppend) == len(checkbookCurrentRow) + 1:
glRowsThatMatchComparisonCurrentRow = []
glCurrentRow = glArray[glCurrentRowIndex]
if glCurrentRow[glAmountColumnIndex] == checkbookCurrentRow[
checkbookAmountColumnIndex]:
if glCurrentRow[glTrxDateColumnIndex] == checkbookCurrentRow[
checkbookTrxDateColumnIndex]:
glRowsThatMatchComparisonCurrentRow.append({
'glDataRowIndex':
glCurrentRowIndex,
'glDataRow':
glCurrentRow
})
if glCurrentRow[glAmountColumnIndex] == -2191.7:
p(1)
# glDataRowToAppend = glArray.pop(glCurrentRowIndex)
# rowToAppend = rowToAppend + glDataRowToAppend
# rowToAppend[spacingColumnIndex] = 'Matched on amount and date'
if len(glRowsThatMatchComparisonCurrentRow) == 1:
glDataRowToAppend = glArray.pop(
glRowsThatMatchComparisonCurrentRow[0]['glDataRowIndex'])
rowToAppend = rowToAppend + glDataRowToAppend
rowToAppend[spacingColumnIndex] = 'Matched on amount and date'
glCurrentRowIndex = glCurrentRowIndex + 1
comparisonArray.append(rowToAppend)
for comparisonCurrentRowIndex, comparisonCurrentRow in enumerate(
comparisonArray):
if len(comparisonCurrentRow) == len(checkbookFirstRow) + 1:
glRowsThatMatchComparisonCurrentRow = []
for glCurrentRowIndex, glCurrentRow in enumerate(glArray):
if comparisonCurrentRow[
checkbookAmountColumnIndex] == glCurrentRow[
glAmountColumnIndex]:
glRowsThatMatchComparisonCurrentRow.append({
'glDataRowIndex':
glCurrentRowIndex,
'glDataRow':
glCurrentRow
})
if len(glRowsThatMatchComparisonCurrentRow) == 1:
comparisonArray[comparisonCurrentRowIndex] = comparisonArray[
comparisonCurrentRowIndex] + glArray.pop(
glRowsThatMatchComparisonCurrentRow[0]
['glDataRowIndex'])
comparisonArray[comparisonCurrentRowIndex][
spacingColumnIndex] = 'Matched on amount'
# for comparisonCurrentRowIndex, comparisonCurrentRow in enumerate(comparisonArray):
# if len(comparisonCurrentRow) == len(glDataFirstRow) + 1 and comparisonCurrentRow[glTransactionTypeColumnIndex] != 'Check(s) Paid':
# glRowsThatMatchComparisonCurrentRow = []
# # if comparisonCurrentRow[glAmountColumnIndex] == -1100.48:
# # p(1)
# for checkbookCurrentRowIndex in reversed(range(0, len(checkbookArray))):
# if comparisonCurrentRow[glAmountColumnIndex] == checkbookArray[checkbookCurrentRowIndex][glAmountColumnIndex]:
# if checkbookArray[checkbookCurrentRowIndex][glTrxTypeColumnIndex] != 'Check' or (checkbookArray[checkbookCurrentRowIndex][glTrxTypeColumnIndex] == 'Check' and len(checkbookArray[checkbookCurrentRowIndex][glTrxNumberColumnIndex])!= 5):
# glRowsThatMatchComparisonCurrentRow.append({
# 'glDataRowIndex': checkbookCurrentRowIndex,
# 'glDataRow': checkbookArray[checkbookCurrentRowIndex]})
# if len(glRowsThatMatchComparisonCurrentRow) == 1:
# comparisonArray[comparisonCurrentRowIndex] = comparisonArray[comparisonCurrentRowIndex] + checkbookArray.pop(glRowsThatMatchComparisonCurrentRow[0]['glDataRowIndex'])
# comparisonArray[comparisonCurrentRowIndex][spacingColumnIndex] = 'Matched on amount, 1 gl row with 1 gl row'
# if len(glRowsThatMatchComparisonCurrentRow) > 1:
# comparisonRowsThatMatchComparisonCurrentRow = []
# for comparisonDuplicateRowIndex, comparisonDuplicateRow in enumerate(comparisonArray):
# if comparisonDuplicateRow[glAmountColumnIndex] == comparisonCurrentRow[glAmountColumnIndex] and len(comparisonDuplicateRow) == len(glDataFirstRow) + 1:
# comparisonRowsThatMatchComparisonCurrentRow.insert(0, {
# 'comparisonDuplicateRowIndex': comparisonDuplicateRowIndex,
# 'comparisonDuplicateRow': comparisonDuplicateRow
# })
# glRowsThatMatchLength = len(glRowsThatMatchComparisonCurrentRow)
# if glRowsThatMatchLength == len(comparisonRowsThatMatchComparisonCurrentRow):
# for comparisonDuplicateMatchedRowIndex in range(0, len(comparisonRowsThatMatchComparisonCurrentRow)):
# comparisonArray[comparisonRowsThatMatchComparisonCurrentRow[comparisonDuplicateMatchedRowIndex]['comparisonDuplicateRowIndex']] = comparisonArray[comparisonRowsThatMatchComparisonCurrentRow[comparisonDuplicateMatchedRowIndex]['comparisonDuplicateRowIndex']] + checkbookArray.pop(glRowsThatMatchComparisonCurrentRow[comparisonDuplicateMatchedRowIndex]['glDataRowIndex'])
# comparisonArray[comparisonRowsThatMatchComparisonCurrentRow[comparisonDuplicateMatchedRowIndex]['comparisonDuplicateRowIndex']][spacingColumnIndex] = f'Matched on amount, {glRowsThatMatchLength} gl rows with {glRowsThatMatchLength} gl rows'
# for comparisonCurrentRowIndex, comparisonCurrentRow in enumerate(comparisonArray):
# if len(comparisonCurrentRow) == len(glDataFirstRow) + 1 and comparisonCurrentRow[glTransactionTypeColumnIndex] != 'Check(s) Paid':
# # p(comparisonCurrentRow)
# for dailyDepositsCurrentRow in dailyDepositsArray:
# if comparisonCurrentRow[glAmountColumnIndex] == dailyDepositsCurrentRow[dailyDepositsAmountColumnIndex]:
# glRowsThatMatchComparisonCurrentRow = []
# for checkbookCurrentRowIndex in reversed(range(0, len(checkbookArray))):
# if checkbookArray[checkbookCurrentRowIndex][glTrxNumberColumnIndex][2:7] == dailyDepositsCurrentRow[dailyDepositsTransactionIDColumnIndex]:
# comparisonArray[comparisonCurrentRowIndex] = comparisonArray[comparisonCurrentRowIndex] + checkbookArray[checkbookCurrentRowIndex]
# comparisonArray[comparisonCurrentRowIndex][spacingColumnIndex] = 'Matched from Daily Deposits file'
# for comparisonCurrentRowIndex, comparisonCurrentRow in enumerate(comparisonArray):
# if len(comparisonCurrentRow) == len(glDataFirstRow) + 1 and comparisonCurrentRow[glTransactionTypeColumnIndex] == 'Check(s) Paid':
# glRowsThatMatchComparisonCurrentRow = []
# for checkbookCurrentRowIndex, checkbookCurrentRow in enumerate(checkbookArray):
# if comparisonCurrentRow[glAmountColumnIndex] == checkbookCurrentRow[glAmountColumnIndex] and comparisonCurrentRow[glDateColumnIndex] == checkbookCurrentRow[glTrxDateColumnIndex]:
# glRowsThatMatchComparisonCurrentRow.append({
# 'glDataRowIndex': checkbookCurrentRowIndex,
# 'glDataRow': checkbookCurrentRow})
# if len(glRowsThatMatchComparisonCurrentRow) == 1:
# comparisonArray[comparisonCurrentRowIndex] = comparisonArray[comparisonCurrentRowIndex] + checkbookArray.pop(glRowsThatMatchComparisonCurrentRow[0]['glDataRowIndex'])
# comparisonArray[comparisonCurrentRowIndex][spacingColumnIndex] = 'Matched on amount and date, gl transaction is a check, gl transaction does not have the same check number'
clearAndResizeParameters = [{
'sheetObj': gspComparison,
'resizeRows': 3,
'startingRowIndexToClear': 0,
'resizeColumns': 3
}, {
'sheetObj': gspEnding,
'resizeRows': 2,
'startingRowIndexToClear': 0,
'resizeColumns': 1
}]
gspComparison.clear_basic_filter()
gspEnding.clear_basic_filter()
myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
myGspreadFunc.displayArray(gspComparison, comparisonArray)
glArray.insert(0, glFirstRow)
myGspreadFunc.displayArray(gspEnding, glArray)
gspComparison.set_basic_filter(2, 1, len(comparisonArray),
len(comparisonArray[0]) + 1)
gspEnding.set_basic_filter(1, 1, len(glArray), len(glArray[0]))
myGspreadFunc.autoResizeColumnsOnSheet(gspSpreadsheet, 'Checkbook')
myGspreadFunc.autoResizeColumnsOnSheet(gspSpreadsheet, 'GL')
myGspreadFunc.autoResizeColumnsOnSheet(gspSpreadsheet, 'Comparison')
myGspreadFunc.autoResizeColumnsOnSheet(gspSpreadsheet, 'Ending')