def start(m): chat_id: int = m.chat.id with sqhelp.Connection() as curs: # curs.execute("select chat_id from \"user\" where chat_id = %d", [chat_id]) try: curs.execute("insert into usr (id) values (%s)", [chat_id]) set_balance(m) except psycopg2.errors.UniqueViolation: menu(m)
def del_category(self, type_, name): """ :param type_: True for income, False for expense :param name: """ with sqhelp.Connection() as curs: curs.execute( "delete from category where name = %s and type = %s and user_id = %s", [name, type_, self.chat_id])
def get_category_list(self, type_): """ :param type_: True for income, False for expense :return: """ with sqhelp.Connection() as curs: curs.execute( 'select name from category where user_id = %s and type = %s', [self.chat_id, type_]) fetch = curs.fetchall() return [i[0] for i in fetch]
def add_category(self, type_, name): """ :param type_: True for income, False for expense :param name: """ with sqhelp.Connection() as curs: query = curs.mogrify( "insert into category (name, type, user_id) values (%s, %s, %s)", [name, type_, self.chat_id]) curs.execute(query)
def __get_operations(self, type_, category=None, how_many=10): with sqhelp.Connection() as curs: if category: query = curs.mogrify( f''' select op.total, to_char(op.date :: date, 'DD.MM'), to_char(op.time :: time, 'HH24:MI' ) from operation op inner join category cat on op.category_id = cat.id where op.type = %(type)s and op.user_id = %(user_id)s and cat.name = %(category)s order by date desc, time desc limit {how_many} ''', { 'type': type_, 'user_id': self.chat_id, 'category': category }) else: query = curs.mogrify( f''' select op.total, to_char(op.date :: date, 'DD.MM'), to_char(op.time :: time, 'HH24:MI'), cat.name from operation op inner join category cat on op.category_id = cat.id where op.type = %(type)s and op.user_id = %(user_id)s order by date desc, time desc limit {how_many} ''', { 'type': type_, 'user_id': self.chat_id }) curs.execute(query) fetch = curs.fetchall() return fetch
def add_operation(self, type_, category, total): """ :param type_: True for income, False for expense :param category: :param total: """ operator = '+' if type_ else '-' with sqhelp.Connection() as curs: curs.execute( 'insert into operation (type, total, user_id, category_id) ' 'values (%(type)s, %(total)s, %(user_id)s, ' '(select id from category ' 'where category.user_id = %(user_id)s ' 'and category.type = %(type)s ' 'and category.name = %(category)s))', { 'type': type_, 'total': total, 'user_id': self.chat_id, 'category': category }) curs.execute( f'update usr set balance = balance {operator} %s where id = %s', [total, self.chat_id])
def set_balance(self, total): with sqhelp.Connection() as curs: curs.execute('update usr set balance = %s where id = %s', [total, self.chat_id])
def get_balance(self): with sqhelp.Connection() as curs: curs.execute('select balance from usr where id = %s', [self.chat_id]) return curs.fetchone()[0]