Import Database App (Java, SQLite)

My routine of putting everything in large excel files proved risky when I accidentally overwrote that file one time so I felt like moving that to SQL would make my data more secure. In addition there was an audit of the last 5 years worth of import transactions so flexible specific queries and table outputs, not just Excel's everything always at once, became more desirable.

The technology for reading excel files is POI, for the database I chose SQLite because it's easier and I wanted the data strictly local anyway. I had to edit the excel files such that they follow only one strict pattern and add stuff for the audit, that took surprisingly long hours. Finally, the throwaway code for building the database: It has one loop for the POI row iterator and a lower order loop for each row where for each table there is a prepared statement with a long sequence of parameters and each column value (e.g. A of row 2) is assigned to its "?", then execute before moving on to the next row.

To properly represent purchasing, I wanted to have one table that contains information every transaction has only once, such as the order number, order date, order confirmation number, delivery schedule (dispatch and arrival), invoice number, invoice due date, the actual arrival date, customs no, tracking no, the day you paid etc. (headtail) and then there is of course another table (body) that relates transactions in headtail to their multiple products and their respective unit prices, quantities, serial numbers etc. So in chronological order from left to right with "body" in the middle (that part is usually finalized when the invoice is issued), the data looks like the snake that ate an elephant. I added another table called freightpack to represent how freight and packing charges as stored in body will, upon arrival, need to be distributed to other items in body, to keep distribution and conversion automated just like in the Python script.

With Java Swing, setting up an OK interface is not hard and if you right-click and let "create Listener" do its job you will safely place your logic where it belongs. Model-View-Controller is a concept I did not implement well here. If a button is supposed to trigger just the very same query and display over and over again (e.g. select all unpaid invoices and tell me when they are due, show me a table of that), I personally feel fine putting the whole thing right into that listener. They say the abstraction would facilitate porting, but e.g. when moving a java swing button for an SQL query to android, not only the code for the user interface changes, but also the Androidish code for SQLite is so different you will have to rewrite both no matter what. C++ with MFC and SQLite3 vs. JAVA SQLite and Swing is likely a similar story. But then, to at least remove some ugly hairballs, for more clarity I tried factoring some of the SQL and table stuff out to Executer classes. As for the Java tables I would have liked to move that into only one generic class or function but for different purposes you need different subtotals and what not, so that was not doable, that's why some executors do the specific table stuff too, again mixing up model and view.

The bit that distributes freight/packing and does the currency conversion has grown into quite a hairball. But I wanted it to be able to process multiple transactions in whatever order you feed them and just followed through no matter how many layers of loops that may take. I suspect that trying to factor parts of that monolith out I would probably lose track of what's happening, so I don't.

TO DO LINK