def getList(self, userId, dateBegin = None, dateEnd = None):
where = self.__table + ".userId = " + str(userId)
if dateBegin:
where += " AND " + self.__table + ".date >= '" + dateBegin + "'"
if dateEnd:
where += " AND " + self.__table + ".date <= '" + dateEnd + "'"
spendingName = SpendingName()
fieldsToSelect = []
for field in self.__fields:
if field not in ['amountEncrypted']:
if self.__encryptionKey and field == 'amount':
fieldsToSelect.append(str("AES_DECRYPT(" + self.__table + ".amountEncrypted, "+web.db.sqlquote(self.__encryptionKey)+") AS amount "))
else:
fieldsToSelect.append(self.__table + "." + field)
fieldsToSelect.append(spendingName.getTableName() + ".name AS spendingName")
fieldsToSelect = ', '.join(fieldsToSelect)
spendings = db.query(
"SELECT " + fieldsToSelect + "\
FROM " + self.__table + " \
JOIN " + spendingName.getTableName() + " ON " + self.__table + ".spendingNameId = " + spendingName.getTableName() + ".id \
WHERE " + where
)
return spendings
def getData(self, userId, dateBegin = None, dateEnd = None):
spendingName = SpendingName()
spending = Spending()
where = spending.getTableName() + ".userId = " + str(userId)
if dateBegin:
where += " AND " + spending.getTableName() + ".date >= '" + dateBegin + "'"
if dateEnd:
where += " AND " + spending.getTableName() + ".date <= '" + dateEnd + "'"
fieldsToSelect = ['spendingNameId']
if self.__encryptionKey:
fieldsToSelect.append(str("ROUND(SUM(AES_DECRYPT(" + spending.getTableName() + ".amountEncrypted, "+web.db.sqlquote(self.__encryptionKey)+")), 2) AS amount "))
else:
fieldsToSelect.append("ROUND(SUM(" + spending.getTableName() + ".amount), 2) AS amount")
fieldsToSelect.append(spendingName.getTableName() + ".name AS spendingName")
fieldsToSelect = ', '.join(fieldsToSelect)
spendings = db.query(
"SELECT " + fieldsToSelect + "\
FROM " + spending.getTableName() + " \
JOIN " + spendingName.getTableName() + " ON " + spending.getTableName() + ".spendingNameId = " + spendingName.getTableName() + ".id \
WHERE " + where + "\
GROUP BY " + spending.getTableName() + ".spendingNameId"
)
return spendings
def updateEncryptedData(self, userId, oldKey, newKey):
"""
This method must be called when user's password is changing
Or when encryption option for user is changing
if oldKey or newKey is None it means than amount was not encrypted or will not be encrypted
"""
amount = '';
updateExpression = '';
if oldKey == None:
amount = 'amount'
else:
amount = "AES_DECRYPT(amountEncrypted, "+web.db.sqlquote(oldKey)+")"
if newKey != None:
amount = "AES_ENCRYPT("+amount+", "+web.db.sqlquote(newKey)+")"
updateExpression = "SET amountEncrypted = "+amount+", amount = 0"
else:
updateExpression = "SET amount = "+amount+", amountEncrypted = ''"
spendingsNames = db.query(
"UPDATE "+self.__table+" \
"+updateExpression+" \
WHERE userId = '"+str(userId)+"'"
)
def getForAutocomplete(self, name, limit=10):
spendingsNames = db.query(
"SELECT name \
FROM "+self.__table+" \
WHERE \
name LIKE '"+str(name.encode("UTF-8"))+"%' \
ORDER BY id\
LIMIT "+str(limit)
)
result = list()
for spendingsName in spendingsNames:
result.append(spendingsName.name)
return result
def getData(self, userId, period, spendingNameInputParam = None, dateBegin = None, dateEnd = None):
if not period in self.__periods:
raise Exception('Wrong period')
spendingName = SpendingName()
spending = Spending()
where = spending.getTableName() + ".userId = " + str(userId)
if spendingNameInputParam:
where += " AND " + spendingName.getTableName() + ".name = " + web.db.sqlquote(spendingNameInputParam)
if dateBegin:
where += " AND " + spending.getTableName() + ".date >= " + web.db.sqlquote(dateBegin)
if dateEnd:
where += " AND " + spending.getTableName() + ".date <= " + web.db.sqlquote(dateEnd)
fieldsToSelect = ['YEAR(date) AS year']
if self.__encryptionKey:
fieldsToSelect.append(str("ROUND(SUM(AES_DECRYPT(" + spending.getTableName() + ".amountEncrypted, "+web.db.sqlquote(self.__encryptionKey)+")), 2) AS amount "))
else:
fieldsToSelect.append("ROUND(SUM(" + spending.getTableName() + ".amount), 2) AS amount")
fieldsToGroup = ['YEAR(date)']
if period != 'year':
fieldsToSelect.append('MONTH(date) AS month')
fieldsToGroup.append('MONTH(date)')
if period != 'month':
fieldsToSelect.append('WEEKOFYEAR(date) AS week')
fieldsToGroup.append('WEEKOFYEAR(date)')
spendings = db.query(
"SELECT " + (', '.join(fieldsToSelect)) + "\
FROM " + spending.getTableName() + " \
JOIN " + spendingName.getTableName() + " ON (" + spendingName.getTableName() + ".id = " + spending.getTableName() + ".spendingNameId) \
WHERE " + where + "\
GROUP BY " + (', '.join(fieldsToGroup))
)
return spendings
def getTopSpendings(self, userId, limit = 10): spendingName = SpendingName() spendingsNames = db.query( "SELECT "+ spendingName.getTableName()+".name "+ "FROM "+self.__table+" "+ "JOIN " + spendingName.getTableName() + " ON " + self.__table + ".spendingNameId = " + spendingName.getTableName() + ".id "+ "WHERE "+ self.__table+".userId='"+str(userId)+"' "+ "GROUP BY "+self.__table+".spendingNameId \ ORDER BY COUNT(*) DESC\ LIMIT "+str(limit) ) result = list() for spendingsName in spendingsNames: result.append(spendingsName.name) return result
def isExists(self, id, userId):
result = db.query("SELECT COUNT(*) AS foundSpendingsNumber FROM "+self.__table+" WHERE id='"+str(id)+"' AND userId='"+str(userId)+"'")
return result[0].foundSpendingsNumber == 1
