Zero Euro Bot: Chat bot for domestic cash flow control

This project builds a chatbot to easily save domestic economy expenses and incomes on Telegram. It can also be used in different contexts;

Domestic Economy chatbot functionalities:

✔️ You can register your expenses (see /expenses command), informing a category and a subcategory (see /category & /subcategory commands);
✔️ You can add new category & subcategory (see /add command);
✔️ You can register your incomes (see /income command);
✔️ You can retrieve a month summary of your expenses (see /summary command);
✔️ You can retrieve a month summary in a graphical way of your expenses (see /plot command);
✔️ You can send sql query by message (see /sql command);
✔️ You can back up your database (see /backup command);
✔️ you can use it on a group chat with people yout share your expenses and incomes 👫;
✔️ you can Have this chatbot deploy in pythonanywherer with a flask webhook;
✔️ you can Have a timeseries graphs of all your expenses and incomes see chatbot-functions;

Few things I'm planning to improve later:

• Refactor using dotenv
• Refactor using python-telegram-bot;
• Refactor Data base management:
  • SQLAlchemy;
  • Alembique - It is already setup but showed some errors. Not using for now;
  • initials data;
• Add test;
• Add Github Actions;
• Improve UX/UI by using buttons menu;
• Implement commando by voice by using Google API

Setting up

Cloning and installing python dependencies using poetry:

gti clone git@github.com:FelipeSBarros/DomesticEconomy.git
cd DomesticEconomy
poetry install

Data base

Set database URL on .env file: dialect+driver://username:password@host:port/database

creating database and adding initial data

poetry run python fixtures.py

Running chatbot:

Provide the chatbot token

poetry run python economybot.py

About files and codes:

• fixture.py: will create the database, tables and insert initial values. :warning: Take a look on it if you plan to adequate both category and subcategories to your reality;

• dbhelper.py: has all the functions related to the database: insert & retrieve data;

• economybot.py: is the bot it self. Mannage the text sent to bot and call the according functions;

• Bot_run.sh: a bash script to keep bot running even after it breaks;

• .env: You must create this file where you should put the BOT_TOKEN and DB_URL;

• BOT_TOKEN: is needed to use the bot; Be careful to keep it save;

• user-email and email password: will be used on /backup function (see /backup) to send the database backup by e-mail;

• DB_URL: Url to data base connection. See SQLAlchemy Engine Configuration;

• This file should be like:

BOT_TOKEN = '12312312ADAsxlclncxca'
DB_URL = 'dialect+driver://username:password@host:port/database'

chatbot Functions

• /start will greeting the user, check if user is already registered in users table. If not, it will be registered in users table;
  

• /category will retrieve all categories already registered in the category table;
  e.g.: /category
  

• /subcategory [category] will show the subcategories related to a especific category;
  e.g.: /subcategory casa
  

• /income [value] will save the value assed as a income in the database.
  ⚠️ Use . as decimal!.
  e.g.: /income 1999.99
  

• /expenses [value] [category] [subcategory] will save the value with category and subcategory assigned in the database;
  ⚠️ Use . as decimal!.
  e.g.: /expenses 19.99 casa luz
  

• /summary [param] [paramII] {month} {year} will return the summary of data saved on database grouping by param. If a second param is passed (paramII) the summary will be for category and subcategory. Also you can optionally specify a month and year. Otherwise, the summary shown will be of the current month and year;
  e.g.: /summary category subcategory 01 2018
  
  
  

• /plot [param] [paramII] {month} {year} will return a graphical (:bar_chart:) summary of data saved on database groupping by param.
  e.g.: /plot category 01 2018
  
  
  
  

• /backup will create and save a database backup in backup folder. Also the backup file will be sent by e-mail (:warning: see API.py).
  e.g.: /backup

• /add [param] [paramII] will create and new category or subcategory. When the command /add is sent with param, only, the function will check if param already exists as category. Otherwise, will be inserted in category table. If /add is sent with both [param] and [paramII], the paramII will be save as subcategory with relation to param category.
  ⚠️ If intending to create a new category and subcategory, first create the category (eg.: /add newCategory) to later save the new subcategory related to the category previusly created (e.g.: /add newCategory newSubCategory).

• /sql send a sql query and the results sent by message.
  ⚠️ commands like ALTER TABLE or DROP TABLE won't be accepted. This can be changed on economybot.py.
  e.g.: /sql select * from view_general limit 3

Useful Links

Some links that was useful to develop this project and study python:

• Python para impacientes
• Python Anywhere
• SQLite3 Documentation
• SQLite3 Tutorials
• Building a telegram bot
• Matplotlib
• cookbook-plotting
• Pandas visualization