def getBusinessEntry(self, businessName):
business = BusinessEntry.selectBy(
businessName=businessName, userId=session.getUserId()).getOne(None)
if (business == None):
business = BusinessEntry(businessName=businessName,
marketingName=businessName,
category="",
userId=session.getUserId())
return business
def markByBusiness(self):
# params = [
# {'name': 'tracker', 'value': [str(self.id)]},
# {'name': 'business', 'value': [str(self.businessId)]}
# ]
#db.runUpdate(self.Q_MARKBANKENTRYBYBUSINESS, params)
#db.runUpdate(self.Q_MARKCREDITENTRYBYBUSINESS, params)
bankEntries = BankEntry.selectBy(business=self.businessId,
userId=session.getUserId())
for e in bankEntries:
e.trackerId = self.id
creditEntries = CreditEntry.selectBy(business=self.businessId,
userId=session.getUserId())
for c in creditEntries:
c.trackerId = self.id
def processSummary(self):
for card in self.cards.getAll():
entry = FileEntry(userId=session.getUserId(),
source=self.type,
reportDate=card.reportDate,
total=card.total,
refId=card.cardNumber,
flagged=card.flagged)
def processSummary(self):
reportDate = self.start + ' to ' + self.end
entry = FileEntry(userId=session.getUserId(),
source=self.type,
reportDate=reportDate,
total=None,
refId=self.account,
flagged=True)
def hideCreditCardReports():
fList = FileEntry.select(
AND(FileEntry.q.userId == session.getUserId(), FileEntry.total
is not None, FileEntry.q.flagged == False))
#Bank entries are flagged on creation so they will not appear here
for f in fList:
if BankEntriesPostProcessor.hideCreditCardReport(
f.reportDate, f.total):
f.flagged = True
def markByAmount(self):
# params = [
# {'name': 'tracker', 'value': [str(self.id)]},
# {'name': 'amount', 'value': [str(self.amount)]}
# ]
#db.runUpdateFromFile(self.F_MARKBYAMOUNT, params)
businesses = BusinessEntry.select(
AND(LIKE(BusinessEntry.q.marketingName, "%check%"),
BusinessEntry.q.userId == session.getUserId()))
businessIds = []
for b in businesses:
businessIds.append(b.id)
bankEntries = BankEntry.select(
AND(BankEntry.q.debit == self.amount,
IN(BankEntry.q.business, businessIds),
BusinessEntry.q.userId == session.getUserId()))
for e in bankEntries:
e.trackerId = self.id
def generateMonthlyReport(month, categories):
params = [{
'name': 'month',
'value': [month]
}, {
'name': 'filter',
'value': categories
}, {
'name': 'userid',
'value': [session.getUserId()]
}]
return db.runQueryFromFile(F_MONTHLY, params)
def generatePieChartData(month, categories):
params = [{
'name': 'month',
'value': [month]
}, {
'name': 'filter',
'value': categories
}, {
'name': 'userid',
'value': [session.getUserId()]
}]
return db.runQueryFromFile(F_MONTHLYBYCATEGORY, params)
def queryByAmount(self):
# params = [
# {'name': 'mincount', 'value': [self.minOccurrences]}
# ]
dataSet = db.runQueryFromFile(self.F_RECURRINGBYAMOUNT,
session.getUserIdParam())
for row in dataSet.values:
# debit, COUNT(*), min(date) as first, max(date) as last
amount = row[0] # CurrencyCol()
count = row[1] # IntCol()
startDate = row[2] # DateCol()
lastDate = row[3] # DateCol()
businessId = 0
name = 'Check'
type = RecurrentExpense.TYPE_FIXED # StringCol()
avgAmount = 0 # CurrencyCol()
maxAmount = 0 # CurrencyCol()
minAmount = 0 # CurrencyCol()
# If recurring expense is already tracked by business, skip it
# This is no longer relevant because we only track checks here
# tracker = self.getExpenseTrackerByBusiness(businessId)
# if (tracker != None):
# print('Recurring expense already tracked by business ' + str(businessId))
# continue
if math.isnan(amount):
continue
tracker = self.getExpenseTrackerByAmount(amount)
if (tracker == None):
tracker = RecurrentExpense(businessId=0,
name=name,
type=type,
amount=amount,
count=count,
startDate=startDate,
lastDate=lastDate,
avgAmount=avgAmount,
minAmount=minAmount,
maxAmount=maxAmount,
userId=session.getUserId())
print('New expense tracked by amount ' + str(amount))
else:
tracker.update(0, name, type, amount, count, startDate,
lastDate, avgAmount, minAmount, maxAmount)
print('Update expense tracked by amount ' + str(amount))
self.trackers.append(tracker)
print(tracker.id)
return
def generateTrackersReport(dateRange):
if dateRange is None:
return None
params = [{
'name': 'start',
'value': [dateRange[0]]
}, {
'name': 'stop',
'value': [dateRange[1]]
}, {
'name': 'userid',
'value': [session.getUserId()]
}]
return db.runQueryFromFile(F_TRACKERS, params)
def hideCreditCardReport(inputDate, inputTotal):
reportDate = datetime.strptime(inputDate, '%Y-%m-%d').date()
start = datetime(reportDate.year, reportDate.month, 1).date()
end = datetime(reportDate.year, reportDate.month, 28).date()
# delta = timedelta(days=3)
# start = reportDate - delta
# end = reportDate + delta
bList = BankEntry.select(
AND(BankEntry.q.userId == session.getUserId(),
BankEntry.q.date >= start, BankEntry.q.date <= end,
BankEntry.q.debit == inputTotal))
found = False
for b in bList:
b.hide = 1
found = True
return found
def addCreditEntry(self, row):
reportDate = self.extractReportDate(row)
purchaseDate = self.extractPurchaseDate(row)
businessName = self.extractBusinessName(row)
amount = self.extractAmount(row)
cardNumber = self.getCardNumber(row)
business = self.getBusinessEntry(businessName)
entry = CreditEntry(reportDate=reportDate,
purchaseDate=purchaseDate,
business=business.id,
cardNumber=cardNumber,
bankId="0",
credit=self.credit(amount),
debit=self.debit(amount),
balance=0,
trackerId=0,
userId=session.getUserId())
return entry
def queryByBusiness(self):
# params = [
# {'name': 'mincount', 'value': [self.minOccurrences]}
# ]
dataSet = db.runQueryFromFile(self.F_RECURRINGBYBUSINESS,
session.getUserIdParam())
for row in dataSet.values:
name = row[2] #StringCol()
businessId = row[0] #IntCol()
amount = 0 #CurrencyCol()
type = RecurrentExpense.TYPE_VARIABLE #StringCol()
count = row[1] #IntCol()
startDate = row[6] #DateCol()
lastDate = row[7] #DateCol()
avgAmount = row[3] #CurrencyCol()
maxAmount = row[5] #CurrencyCol()
minAmount = row[4] #CurrencyCol()
tracker = self.getExpenseTrackerByBusiness(businessId)
if (tracker == None):
tracker = RecurrentExpense(businessId=businessId,
name=name,
type=type,
amount=amount,
count=count,
startDate=startDate,
lastDate=lastDate,
avgAmount=avgAmount,
minAmount=minAmount,
maxAmount=maxAmount,
userId=session.getUserId())
print('New expense tracked by business ' + str(businessId))
else:
tracker.update(businessId, name, type, amount, count,
startDate, lastDate, avgAmount, minAmount,
maxAmount)
print('Update expense tracked by business ' + str(businessId))
self.trackers.append(tracker)
print(tracker.id)
return
def processRow(self, row):
date = self.extractDate(row)
businessName = self.extractBusiness(row)
refId = self.extractRefId(row)
credit = self.extractCredit(row)
debit = self.extractDebit(row)
balance = self.extractBalance(row)
if (myString.isEmpty(date)):
return None
business = self.getBusinessEntry(businessName)
entry = BankEntry(date=date,
business=business.id,
hide=0,
refId=refId,
credit=credit,
debit=debit,
balance=balance,
trackerId=0,
userId=session.getUserId())
entry.toCSV(
) # for debugging, find a better way to log only when in debug mode
return entry
def updateBusinessEntry(businessId, newName, newCategory):
business = BusinessEntry.selectBy(id=businessId,
userId=session.getUserId()).getOne(None)
if (business != None):
business.set(marketingName=newName, category=newCategory)
def updateTrackerEntry(trackerId, newName):
tracker = RecurrentExpense.selectBy(
id=trackerId, userId=session.getUserId()).getOne(None)
if (tracker != None):
tracker.set(name=newName)
def getExpenseTrackerByBusiness(self, businessId):
tracker = RecurrentExpense.selectBy(
businessId=businessId, userId=session.getUserId()).getOne(None)
return tracker
def getExpenseTrackerByAmount(self, amount):
tracker = RecurrentExpense.selectBy(
amount=amount, userId=session.getUserId()).getOne(None)
return tracker