def update_allowance(self, allowanceid: int, amount: float, user: str):
"""
Description: public function to update the allowance
Parameters:
allowanceid: ID of the allowance to be updated
amount: amount to be updated for the allowance
user: name of the user performing the udpate
Returns:
True if success update, else None
Raises exceptions on errors
"""
try:
if allowanceid <= 0:
raise ETInputValueException("Invalid allowance id: %s" %
allowanceid)
if amount < 200 and amount > 400:
raise ETInputValueException(
"Allowance amount must be between 200 and 400. %s given" %
amount)
return self._sqli.update_allowance(allowanceid, amount, user)
except DatabaseError:
raise
except DataError:
raise
except ETInputValueException:
raise
except ETGeneralException:
raise
except Exception:
raise
# return nothing
return None
def get_specific_month_allowance(self, month: int, year: int):
"""
Description: public method to get specific month's allowance
Parameters:
month: specific month to retrieve the allowance
year: specific year to retrieve the allowance
Returns:
Allowance if found, else None
Raises exceptions on errors
"""
try:
if not month >= 1 and month <= 12:
raise ETInputValueException("Invalid Month provided: %s" %
month)
if len(str(year)) < 4:
raise ETInputValueException("Invalid Year provided: %s" % year)
return self._sqli.get_allowances(specificmonth=month,
specificyear=year)
except DatabaseError:
raise
except DataError:
raise
except ETInputValueException:
raise
except Exception:
raise
# return nothing
return None
def update_allowance(self, allowanceid: int, amount: float, user: str):
""" update the allowance with the given parameters """
try:
if allowanceid <= 0:
raise ETInputValueException("Invalid allowance id: %s" % allowanceid)
if amount < 200 and amount > 400:
raise ETInputValueException("Allowance amount must be between 200 and 400. %s given" % amount)
if not self.get_allowances(findbyid=True, allowanceid=allowanceid):
raise ETGeneralException("Allowance doesn't exist or is archived. ID: %s" % allowanceid)
query = ("UPDATE Allowance SET Allowance_Amount=:a_amt, "
"MODIFIEDBY=:a_user, MODIFIEDON=datetime('now') "
"WHERE Allowance_ID=:a_id;")
queryparams = { "a_amt": amount, "a_user": user, "a_id": allowanceid, }
return self._execute_query(query, queryparams, False)
except sqlite3.DatabaseError:
raise
except sqlite3.DataError:
raise
except ETInputValueException:
raise
except ETGeneralException:
raise
except Exception:
raise
# return nothing
return None
def add_expense_type(self, typename: str, user: str):
"""
Description: insert a new expense type with the given name
Parameters:
- typename: name of expense type
- user: name of user inserting the record
Returns:
True if inserted successfully, else None
Raises exceptions on errors
"""
try:
if not typename:
raise ETInputValueException("Type name cannot be empty")
if self.get_expense_type(specificname=typename, partialname=True):
raise ETGeneralException("Expense Type %s already exists" %
typename)
query = (
"INSERT INTO ExpenseType (ET_Name, CREATEDBY, MODIFIEDBY) "
"VALUES (?, ?, ?)")
queryparams = (typename, user, user)
return self._execute_query(query, queryparams, fetch=False)
except sqlite3.DataError:
raise
except sqlite3.DatabaseError:
raise
except ETInputValueException:
raise
except ETGeneralException:
raise
except Exception:
raise
# return nothing
return None
def get_all_expense_locations(self,
getall=False,
locid=0,
findbyid=False,
searchparams=[]):
"""
Description: get the expense locations
Parameters:
- getall: flag when set will retrieve all locations
- locid: ID of the expense location
- findbyid: flag when set will retrieve the location of the given id
- searchparams: list of search keys.
Example: [{'columnname': 'EL_StoreName', 'operator': '=', 'value': 'REWE' }, ]
Returns:
List of expense locations if found, else None
Raises exceptions on errors
"""
try:
if not getall and not findbyid and not searchparams:
raise ETInputValueException(
"Invalid parameters provided to get the expense locations")
except sqlite3.DataError:
raise
except sqlite3.DatabaseError:
raise
except ETInputValueException:
raise
except ETGeneralException:
raise
except Exception:
raise
# return nothing
return None
def archive_allowance(self, allowanceid: int, user: str):
"""
Description: public function to archive the given allowance
Parameters:
allowanceid: ID of the allowance to be archived
user: name of the user performing the operation
Returns:
True if successfully archived, else None
Raises exceptions on errors
"""
try:
if allowanceid <= 0:
raise ETInputValueException("Invalid allowance id: %s" %
allowanceid)
return self._sqli.archive_allowance(allowanceid, user)
except DatabaseError:
raise
except DataError:
raise
except ETInputValueException:
raise
except Exception:
raise
# return nothing
return None
def add_allowance(self, month: int, year: int, amount: float,
currency: str, user: str):
"""
Description: insert the allowance with the given data
Parameters:
- month: month for which the allowance has to be inserted
- year: year for which the allowance has to be inserted
- amount: allowance amount
- currency: currency of allowance amount
- user: user adding the allowance
Returns:
True if inserted successfully, else None.
Raises exceptions on errors
"""
try:
if not month >= 1 and month <= 12:
raise ETInputValueException("Invalid Month provided: %s" %
month)
if len(str(year)) < 4:
raise ETInputValueException("Invalid Year provided: %s" % year)
if currency != "EUR":
raise ETInputValueException("Currency must be EUR. %s given" %
currency)
if user.lower() not in ["krishna", "indira"]:
raise ETInputValueException("Invalid user: %s" % user)
if amount < 200 and amount > 400:
raise ETInputValueException(
"Allowance amount must be between 200 and 400. %s given" %
amount)
return self._sqli.add_allowance(month=month,
year=year,
amount=amount,
currency=currency,
user=user)
except DatabaseError:
raise
except DataError:
raise
except ETInputValueException:
raise
except Exception:
raise
# return nothing
return None
def add_allowance(self, month: int, year: int, amount: float, currency: str, user: str):
"""
Description: insert the allowance with the given data
Parameters:
- month: month for which the allowance has to be inserted
- year: year for which the allowance has to be inserted
- amount: allowance amount
- currency: currency of allowance amount
- user: user adding the allowance
Returns:
True if inserted successfully, else None.
Raises exceptions on errors
"""
try:
if not month >= 1 and month <= 12:
raise ETInputValueException("Invalid Month provided: %s" % month)
if len(str(year)) < 4:
raise ETInputValueException("Invalid Year provided: %s" % year)
if currency != "EUR":
raise ETInputValueException("Currency must be EUR. %s given" % currency)
if user.lower() not in ["krishna", "indira"]:
raise ETInputValueException("Invalid user: %s" % user)
if amount < 200 and amount > 400:
raise ETInputValueException("Allowance amount must be between 200 and 400. %s given" % amount)
if self.get_allowances(specificmonth=month, specificyear=year):
raise ETGeneralException("Allowance for month %s year %s already exists. Try updating" % (month, year))
query = ("INSERT INTO Allowance (Allowance_Month, Allowance_Year, "
"Allowance_Amount, Allowance_Currency, CREATEDBY, MODIFIEDBY) "
"VALUES(?, ?, ?, ?, ?, ?)")
queryparams = (month, year, amount, currency, user)
return self._execute_query(query, queryparams, fetch=False)
except sqlite3.DatabaseError:
raise
except sqlite3.DataError:
raise
except ETInputValueException:
raise
except ETGeneralException:
raise
except Exception:
raise
# return nothing
return None
def update_expense_type(self, typeid: int, typename: str, user: str):
"""
Description: Update the expense type
Parameters:
- typeid: expense type ID for which the name has to be updated.
- typename: name to be updated with
- user: name of the user updating the record
Returns:
True if update is successful, else None
Raises execptions on errors
"""
try:
if typeid <= 0:
raise ETInputValueException("Invalid type id: %s" % typeid)
if not typename:
raise ETInputValueException("Type name cannot be empty")
if not self.get_expense_type(specificid=typeid, findbyid=True):
raise ETGeneralException(
"Type ID: %s doesn't exist or this record is archived!" %
typeid)
if self.get_expense_type(specificname=typename):
raise ETGeneralException("Type name: %s already exists!" %
typename)
query = ("UPDATE ExpenseType SET ET_Name=:et_name, "
"MODIFIEDBY=:et_user, MODIFIEDON=datetime('now') "
"WHERE ET_ID=:et_id;")
queryparams = {
"et_name": typename,
"et_user": user,
"et_id": typeid,
}
return self._execute_query(query, queryparams, fetch=False)
except sqlite3.DataError:
raise
except sqlite3.DatabaseError:
raise
except ETInputValueException:
raise
except ETGeneralException:
raise
except Exception:
raise
# return nothing
return None
def _execute_query(self, query, queryparams=(), fetch=True):
"""
Description: function to execute the given query with(out) params
Access Permissions:
This is a private/protected function.
Only this class or classes inheriting this class can access this function
Parameters:
query: query to be executed
queryparams: parameters to be supplied to execute the query. default: empty tuple
fetch: flag to indicate whether to execute the query as fetch or commit. default: True
Returns:
fetch=True: List of result if found, else None
fetch=False: True if query success committed, else None
"""
conn = None
try:
if not query:
raise ETInputValueException("Query cannot be empty!")
conn = self._connect_to_sqlite3()
sqlcursor = conn.cursor()
if queryparams == None:
queryparams = ()
# execute the given query with the given params
sqlcursor.execute(query, queryparams)
# fetch the results if the flag is set
if fetch:
results = sqlcursor.fetchall()
if results:
columnnames = map(lambda x: x[0], sqlcursor.description)
resultlist = []
for row in results:
rowdict = dict(zip(columnnames, row))
resultlist.append(rowdict)
return resultlist
else:
# commit the changes and return true
conn.commit()
return True
except sqlite3.DatabaseError:
raise
except sqlite3.DataError:
raise
except ETInputValueException:
raise
except Exception:
raise
finally:
self._disconnect_from_sqlite3(conn)
# return nothing
return None
def get_expense_types(self,
specificid=0,
specificname="",
findbyid=False,
partialname=False):
"""
Description: get the expense types
Parameters:
- specificid: get the expense type for the given id. default: 0
- specificname: get the expense type for the given name. default: empty
- findbyid: flag to search by id. default: False
- partialname: flag to search for partial name. default: False
Returns:
List of expense type(s) if found, else None
Raises exception on error
"""
try:
query = "SELECT * FROM ExpenseType WHERE IS_DELETED='False'"
queryparams = None
if findbyid and specificid <= 0:
raise ETInputValueException("Invalid type id: %s" % specificid)
if findbyid and specificid > 0:
query += " AND ET_ID=:et_id"
queryparams = {
"et_id": specifictype,
}
elif specificname and partialname:
query += " AND ET_Name like :et_name"
queryparams = {
"et_name": "%%%s%%" % specificname,
}
elif specificname:
query += " AND ET_Name=:et_name"
queryparams = {
"et_name": specificname,
}
query += ";"
return self._execute_query(query, queryparams)
except sqlite3.DatabaseError:
raise
except sqlite3.DataError:
raise
except ETInputValueException:
raise
except Exception:
raise
# return nothing
return None
def get_allowances(self, getall= False, specificmonth= 0, specificyear= 0,
allowanceid=0, findbyid=False):
"""
Description: get the allowances
Parameters:
- getall: get all allowances. default: False
- specificmonth: get the allowance for specific month. defailt: 0
- specificyear: get the allowance for specific year. default: 0
Returns:
List of allowance if found, else None.
Raises exceptions on errors
"""
try:
if findbyid and allowanceid <= 0:
raise ETInputValueException("Invalid allowance id: %s" % allowanceid)
query = "SELECT * FROM Allowance WHERE IS_DELETED='False'"
queryparams = None
if not getall:
if findbyid:
query = " AND Allowance_ID=:a_id"
queryparams = { "a_id": allowanceid, }
else:
query += " AND Allowance_Month=:month AND Allowance_Year=:year"
month = datetime.now().month
year = datetime.now().year
# specific month and year
if specificmonth > 0:
month = specificmonth
if specificyear > 0:
year = specificyear
queryparams = { "month": month, "year": year, }
query += ";"
return self._execute_query(query, queryparams)
except sqlite3.DatabaseError:
raise
except sqlite3.DataError:
raise
except ETInputValueException:
raise
except Exception:
raise
# return nothing
return None
def archive_expense_type(self, typeid: int, user: str):
"""
Description: archive the given expense type
Parameters:
- typeid: ID of the expense type to be archived
- user: name of the user archiving the record
Returns:
True if successfully archived, else None
Raises execptions on errors
"""
try:
if typeid <= 0:
raise ETInputValueException("Invalid type id: %s" % typeid)
if not self.get_expense_type(specificid=typeid, findbyid=True):
raise ETGeneralException(
"Type ID: %s doesn't exist or is already archived!" %
typeid)
query = ("UPDATE ExpenseType SET IS_DELETED='True', "
"MODIFIEDBY=:et_user, MODIFIEDON=datetime('now') "
"WHERE ET_ID=:et_id;")
queryparams = {
"et_user": user,
"et_id": typeid,
}
return self._execute_query(query, queryparams, fetch=False)
except sqlite3.DataError:
raise
except sqlite3.DatabaseError:
raise
except ETInputValueException:
raise
except ETGeneralException:
raise
except Exception:
raise
# return nothing
return None
def archive_allowance(self, allowanceid: int, user: str):
"""
Description: archive the given allowance
Parameters:
- allowanceid: ID of the allowance that has to be archived
- user: name of the user archiving the record
Returns:
True if successfully archived, else None
Raises exceptions on errors
"""
try:
if allowanceid <= 0:
raise ETInputValueException("Invalid allowance id: %s" % allowanceid)
if not self.get_allowances(findbyid=True, allowanceid=allowanceid):
raise ETGeneralException("Allowance doesn't exist or is already archived. ID: %s" % allowanceid)
query = ("UPDATE Allowance SET IS_DELETED='True', "
"MODIFIEDON=datetime('now'), MODIFIEDBY=:a_user "
"WHERE Allowance_ID=:a_id;")
queryparams = { "a_user": user, "a_id": allowanceid, }
return self._execute_query(query, queryparams, fetch=False)
except sqlite3.DatabaseError:
raise
except sqlite3.DataError:
raise
except ETInputValueException:
raise
except ETGeneralException:
raise
except Exception:
raise
# return nothing
return None