def create_tables():
cur = db_iface.get_cursor()
cur.execute(CREATE_TAB_TAG)
cur.execute(CREATE_TAB_TAG_IN_MOM)
cur.execute(SET_TAB_VERSION, (TAB_VERSION, ))
db_iface.commit()
cur.close()
def __init__(self,
id: int = None,
value: float = 0,
cause: str = "not specified",
year: int = datetime.datetime.today().date().year,
month: int = datetime.datetime.today().date().month,
day: int = datetime.datetime.today().date().day):
self.value = float(value)
self.cause = cause # description of money movement
self.time = datetime.date(int(year), int(month), int(day))
if id == None:
cur = db_iface.get_cursor()
cur.execute(INSERT_MOM, (
self.value,
self.cause,
self.time,
))
cur.execute(GET_LAST_MOM)
self.id = cur.fetchone()[0]
db_iface.commit()
cur.close()
else:
self.id = id
def get_moms(self,
start_date=datetime.datetime.today().date(),
end_date=datetime.datetime.today().date()):
mlist = []
cur = db_iface.get_cursor()
if start_date is None and end_date is None:
cur.execute(GET_MOMS_0, (self.id, ))
elif start_date is None and end_date is not None:
cur.execute(GET_MOMS_1, (self.id, end_date.strftime('%Y-%m-%d')))
elif start_date is not None and end_date is None:
cur.execute(GET_MOMS_2, (self.id, start_date.strftime('%Y-%m-%d')))
elif start_date is not None and end_date is not None:
cur.execute(GET_MOMS_3, (self.id, start_date.strftime('%Y-%m-%d'),
end_date.strftime('%Y-%m-%d')))
for raw in cur.fetchall():
raw_year = raw[3].split('-')[0]
raw_month = raw[3].split('-')[1]
raw_day = raw[3].split('-')[2]
mom = Mom(id=raw[0],
value=raw[1],
cause=raw[2],
year=raw_year,
month=raw_month,
day=raw_day)
mlist.append(mom)
cur.close()
return mlist
def update(self,
new_value=None,
new_cause=None,
new_year=None,
new_month=None,
new_day=None):
cur = db_iface.get_cursor()
if new_value:
cur.execute(UPDATE_MOM_VALUE, (
new_value,
self.id,
))
if new_cause:
cur.execute(UPDATE_MOM_TIME, (
new_cause,
self.id,
))
if new_year and new_month and new_day:
new_time = datetime.date(year=new_year,
month=new_month,
day=new_day)
cur.execute(UPDATE_MOM_TIME, (
new_time,
self.id,
))
db_iface.commit()
cur.close()
def set_visible(self, value):
self.visible = value
cur = db_iface.get_cursor()
if self.visible:
cur.execute(UPDATE_LOM_VISIBLE, (1, self.id))
else:
cur.execute(UPDATE_LOM_VISIBLE, (0, self.id))
db_iface.commit()
def delete(self):
#TODO : before delete the lom delete all the moms linked
cur = db_iface.get_cursor()
cur.execute(DELETE_LOM, (self.id, ))
db_iface.commit()
cur.close()
self.name = None
self.id = None
def create_tables():
cur = db_iface.get_cursor()
cur.execute(CREATE_TABLE_LOM)
cur.execute(CREATE_TABLE_MOM_IN_LOM)
cur.execute(CREATE_DEFAULT_LOMS)
cur.execute(SET_TAB_VERSION, (TAB_VERSION, ))
db_iface.commit()
cur.close()
def delete(self):
cur = db_iface.get_cursor()
cur.execute("delete from tags where id = ?", (self.id, ))
cur.execute("delete from tag_in_mom where tag_id = ?", (self.id, ))
db_iface.commit()
cur.close()
self.name = None
self.id = None
def get_lom(name=None, id=None):
cur = db_iface.get_cursor()
if name:
cur.execute(GET_LOM_BY_NAME, (name, ))
elif id:
cur.execute(GET_LOM_BY_ID, (id, ))
res = cur.fetchone()
cur.close()
return Lom(res[0], res[1])
def get_tags():
tlist = []
cur = db_iface.get_cursor()
cur.execute('select * from tags')
for l in cur.fetchall():
tlist.append(Tag(id=l[0], name=l[1]))
cur.close()
return tlist
def delete(self):
cur = db_iface.get_cursor()
cur.execute(DELETE_MOM, (self.id, ))
cur.execute(DELETE_MOM_LINK, (self.id, ))
db_iface.commit()
cur.close()
self.value = None
self.cause = None
self.time = None
self.id = None
def add(self, mlist):
cur = db_iface.get_cursor()
for m in mlist:
cur.execute(LINK_MOM_TO_LOM, (
m.id,
self.id,
))
db_iface.commit()
cur.close()
def get_loms():
llist = []
cur = db_iface.get_cursor()
cur.execute(GET_ALL_LOMS)
for l in cur.fetchall():
if l[2] == 1:
llist.append(Lom(l[0], l[1], True, l[3]))
else:
llist.append(Lom(l[0], l[1], False, l[3]))
cur.close()
return llist
def get_mom(self, id):
cur = db_iface.get_cursor()
cur.execute(GET_MOM, (id, ))
raw = cur.fetchone()
raw_year = raw[3].split('-')[0]
raw_month = raw[3].split('-')[1]
raw_day = raw[3].split('-')[2]
return Mom(id=raw[0],
value=raw[1],
cause=raw[2],
year=raw_year,
month=raw_month,
day=raw_day)
def get_tag(id=None, name=None):
cur = db_iface.get_cursor()
if id:
cur.execute('select * from tags where id = ?', (id, ))
elif name:
cur.execute('select * from tags where name = ?', (name, ))
l = cur.fetchone()
t = Tag(id=l[0], name=l[1])
cur.close()
return t
def database(self):
# open db connection
if not os.path.exists(self.cfg['private']['data']):
db_iface.open()
cur = db_iface.get_cursor()
cur.execute(SapyValues.get_value('db.create.app_meta'))
cur.execute(SapyValues.get_value('db.populate.app_meta'),
(SapyConstants.APP.VERSION, ))
db_iface.commit()
cur.close()
moms.create_tables()
loms.create_tables()
tags.create_tables()
objs.create_tables()
else:
db_iface.open()
def __init__(
self,
id=None,
name='noname',
):
self.name = name
if id == None:
cur = db_iface.get_cursor()
cur.execute("insert into tags (name) values ( ? )", (name, ))
cur.execute("select id from tags order by id DESC ;")
self.id = cur.fetchone()[0]
db_iface.commit()
cur.close()
else:
self.id = id
def __init__(self,
id=None,
name="list of movements",
visible=False,
color="black"):
self.logger = LoggerFactory.getLogger(str(self.__class__))
self.name = name
self.visible = visible
self.color = color
if id == None:
cur = db_iface.get_cursor()
cur.execute(INSERT_LOM, (name, visible, color))
cur.execute(GET_LAST_LOM)
self.id = cur.fetchone()[0]
db_iface.commit()
cur.close()
else:
self.id = id
def set_name(self, value):
self.name = value
cur = db_iface.get_cursor()
cur.execute(UPDATE_LOM_NAME, (self.name, self.id))
db_iface.commit()
def set_color(self, value):
self.color = value
cur = db_iface.get_cursor()
cur.execute(UPDATE_LOM_COLOR, (self.color, self.id))
db_iface.commit()
