A friend of mine has a small business and I offered her to help. For me it would be a great case study to learn a few things along the way, as I'm a beginner in Python in SQL. At the moment she uses Excel to do her administration, but given she is not very skilled at it and often her accountant reverts back with errors in her sheet. Hence, I want to help her out with creating a simple and straight forward tool in Python (let's call it ERP for future reference), connected to a db. And a manual 3-monthly output (need to give the accountant a call for his preference) which she can email to her accountant. I've broken it down into 4 fases for myself and despite some things I found online, I'm a bit fearfull that I invest a lot of time and get an unstable output.
Fase 1. Hosting a SQLite DB online and make sure the ERP can read, update, and backup the DB. Fase 2. Write the ERP with a simple; CRM and "order" DB (1 "order" would be a customer for 30-60 minutes). Fase 3. timesheet/calender (for making apointments) and an IF, if my friend had a customer, that it autmatically adds an entry "clean room" of 1.5 hours to the day. (timesheet is an IRS thing, she needs to proof how many hours she worked). Fase 4. Live testing.
The ERP would just be a gui connected to the DB. With 4 tabs, "CRM", "Appointment/Orders", "Calender(optional)",
So my questions per fase are;
Fase 1
SQLite is free (vs MSSQL) and normally hosted locally. I want to host it external to avoid losing the DB if I.E. HDD crash / pc-format by someone else (friend needs help from someone who might not be aware of the DB). Dropbox, doesn't work appearantly. Github should be possible. Is Github safe and stable for this? Or would it be better to host it with a website, with logon to reach the DB? Seems complexer, but once that works I could use the website method for more programs/tools. Unfortunately can't find much on it, other than it should be possible.
According to this article; https://www.geeksforgeeks.org/how-to-create-a-backup-of-a-sqlite-database-using-python/ a backup can be easily created. However I would like to do it;
- With an interval I.E. Weekly, that Python checks at startup whether the week number (2022_33) already excists or if the last backup was longer than 7 days ago. Any idea how I can achieve this?
- Automatic, so my friend doesn't have to remember to do it, nor press a button (user-friendly).
Would this be possible? If so, does anyone have additional documentation to achieve this?
Fase 2 Pretty sure I can achieve this by myself / read up where needed . Most common things can be found online / has been asked before or I've done before :).
Fase 3 I have no clue here, all though it is optional (would be great addition when turning it into a mobile app as well), I would love to learn how to do this. I did find how to create a calender (view dates only), but not one where you can "plan" appointments. Similar to I.E. Outlook and schedule a teams meeting idea. I would assume this could be a DB in SQlite? And where I would be totally lost is how to write a function, where it would check IF on a day an appointment is TRUE - Then add "Clean_room" = 1. ELIF "Clean_Room' = 0. Where it would automatically add an entry in the "Timesheet" DB for "Clean_Room" = "90 minutes". Any help on this would be very appriciated, most likely I've used the wrong search terms to find a piece a code / article that covers this "option". As I'm more than sure it has been done before.
Fase 4 As with many things, it feels far, far, away now...
Many thanks in advance for taking the time to read this extended "question".
CodePudding user response：
The SQLite database can be compiled to WebAssembly (WASM) and served using static website hosting. SQLite stores data using files so you can avoid downloading the whole thing by making clever use of HTTP range requests which accept a certain byte range as a 204 partial request. The HTTP 204 No Content success status response code indicates that a request has succeeded, but that the client doesn't need to navigate away from its current page. This might be used, for example, when implementing "save and continue editing" functionality for a wiki site. See Developer Mozilla docs.
Be careful, the SQLite will be usable only for getting data sake, SQLite is just a file on storage unit, such as harddisk or memory.
I don't see why you want to use SQLite exactly, knowing well it is only a local database, but I recommend you to use any other SQL database for your application, the process is quite the same, especially the SQL won't change for almost all requests obviously, see The Most Popular SQL Databases for reference. I most of the time use MySQL or PostgreSQL for SQL databases, check them out.