First release of Northwind Traders from MS Access

[Drazen D. Babic]

Hi all,

here is the first release of migrated App from Access:

https://northwind.pythonanywhere.com/ 

It can be directly compared  with the Desktop app.

Not finished yet,  and thanks to Danijel who helped.

The Products are showing multiple Suppliers name, 

hence a bit of delay in rendering. It is very fast on my PC.

The Export with the DB is there. The App has no users passwords atm,

since this is how Desktop App is designed.

D.

[Drazen D. Babic]

Some explanations how the Northwind Traders Access App works, and what is still needed:
https://answers.microsoft.com/en-us/msoffice/forum/all/northwind-traders-template-how-is-on-hand/4cd99f2f-c391-493d-99eb-7ce2cebb8c51

https://dba.stackexchange.com/questions/289915/ms-northwind-traders-db-inventory-transactions-entity-relationship-question

https://www.youtube.com/watch?v=ADTaZdJlO8s&t=218s

The Inventory List is the most important, since without it the App is not useful. So I did have a look at Access queries,

and will try to implement. 

 Ultimately, the Inventory List should look exactly like on the Pic. Thanks
 

[Drazen D. Babic]

Here is the complete MS Access SQL query for the Inventory List! Very complex stuff but we will get there!

Inventory query:

SELECT Products.ID AS [Product ID], Products.[Product Name], Products.[Product Code], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz([Quantity Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold], [Qty Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty On Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz([Quantity On Back Order],0) AS [Qty On Back Order], Products.[Reorder Level], Products.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level], IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To Reorder]
FROM ((((Products LEFT JOIN [Inventory Sold] ON Products.ID = [Inventory Sold].[Product ID]) LEFT JOIN [Inventory Purchased] ON Products.ID = [Inventory Purchased].[Product ID]) LEFT JOIN [Inventory On Hold] ON Products.ID = [Inventory On Hold].[Product ID]) LEFT JOIN [Inventory On Order] ON Products.ID = [Inventory On Order].[Product ID]) LEFT JOIN [Products On Back Order] ON Products.ID = [Products On Back Order].[Product ID];

So looks like it needs Inventory Sold query:

SELECT [Inventory Transactions].[Product ID], Sum([Inventory Transactions].Quantity) AS [Quantity Sold]
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].[Transaction Type])=2))
GROUP BY [Inventory Transactions].[Product ID];

and then Inventory Purchased:

SELECT [Inventory Transactions].[Product ID], Sum([Inventory Transactions].Quantity) AS [Quantity Purchased]
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].[Transaction Type])=1))
GROUP BY [Inventory Transactions].[Product ID];

Than INventory on Order:
SELECT [Purchase Order Details].[Product ID] AS [Product ID], Sum([Purchase Order Details].Quantity) AS [Quantity On Order]
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Posted To Inventory])=False))
GROUP BY [Purchase Order Details].[Product ID];

And Inventory on Hold:

SELECT [Inventory Transactions].[Product ID], Sum([Inventory Transactions].Quantity) AS [Quantity On Hold]
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].[Transaction Type])=3))
GROUP BY [Inventory Transactions].[Product ID];

Finally, it need Products on Back order:

SELECT [Order Details].[Product ID] AS [Product ID], Sum([Order Details].Quantity) AS [Quantity On Back Order]
FROM [Order Details]
WHERE ((([Order Details].[Status ID])=4))

GROUP BY [Order Details].[Product ID]; 

[Drazen D. Babic]

Hmm,

not sure how to solve this, the most of the data is in ONE single table, Inventory Transactions, which has 1,2 or 3 as criteria for calculating EACH Quantity:

  

WHERE ((([Inventory Transactions].[Transaction Type])=2))

WHERE ((([Inventory Transactions].[Transaction Type])=1))

WHERE ((([Inventory Transactions].[Transaction Type])=3))

So Quantity for Type=1 is different, that is Purchased Quantity, Type=2 is Sold Quantity, so we need to Extract Sold from Purchased 

That gives us "QTY on Hand", and Type=3 is "QTY on Hold" and Hand Minus Hold = "QTY Available" 
"Quantity On Order" and "Quantity On Back Order" is not affecting QTY Available.

It would be exactly the same for the Report, but this Virtual Table is used to click on "Purchase" button when "QTY Available" is ZERO.

Andrew is the Warehousing master, I hope he will find some time...

REgards

D.

[Danijel Kaurin]

Hi Dražen.

You can try with custom sql query:

def on_open(item, params):

    connection = item.task.connect()

    cursor = connection.cursor()

    query = 'SELECT * FROM "demo_users"''

    cursor.execute(query)

    rows = cursor.fetchall()

    result = []

    #print(query)

    #print(result)

Regards

[Drazen D. Babic]

Thanks Danijel,

SQLite3 is not using ALIASES, hence no SQL arithmetic in select clause.

Plus, Nz and IIf should be ported to sqlite3.

And then this sql would not work on some other DB's. Hence, not portable :(

However, I think set_where is portable. As seen, it is getting some data, I just don't know how to get all :)

I think it is a child play for the Boss, will see.

[Drazen D. Babic]

All done!

To compare how MS Access Inventory List looks like, here is the Access screenshot which is the same as on 

https://northwind.pythonanywhere.com/

It is not the most elegant or efficient way but it works:) Need to add "Qty Available" and reports.

The Export is there with the DB if someone would like to have a look!

So, to conclude, this App has absolutely no SQL. The only JS code is for some tables, and Python side for Inv list.

The data was directly moved to Jam with our utility. 

After that, just a Lookup List was created and some Primary Keys for below tables:

Inventory_Transaction_Types Order_Details_Status Orders_Status Orders_Tax_Status Purchase_Order_Status 

Did not test much since not finished yet. 

D.

 

[Drazen D. Babic]

Started testing it. A few bugs, some smaller some big :)
The biggest obstacle is splitting the table into two, for example Purchase Orders has Payment Type, which is Cash, Check, etc.

This should go into a Lookup List (or a table), but for that, the field must be INT, and now is TEXT.

If this is changed to a Lookup, that breaks data import (not Export zip!) to MySQL, due to:

.

INFO - copying table purchase_orders records: 29
ERROR - invalid literal for int() with base 10: 'Check'
Traceback (most recent call last):
.

copy_rows
    r[j] = int(r[j])
ValueError: invalid literal for int() with base 10: 'Check'

Because it is still 'Check' in the table. 

One solution is this:

table2.open(open_empty=True)

for t in table1:

    table2.append()

    t.id.value = table2.id.value

    t. PaymentType .value = table2. PaymentType .value

    table2.post()    

table2.apply()

However, this is not the best because it leaves the data in table1, which is redundant.

[Drazen D. Babic]

Hello all,

I implemented Fabio's SQL ( https://groups.google.com/g/jam-py/c/6yx7c724OtM), instead of Python loop

on Server side for virtual table.

The Inventory List is quite fast and the Export is available to see how it's implemented.

Next step would be to finish the logic for buttons (for Purchase, Approve, etc.)

Cheers

D.

[Drazen D. Babic]

All good, I figured what to do!

[Dean D. Babic]

Sorry for spamming :)

Jam.py is now listed on the first google page results when searching for "Moving MS Access to Web"!!!

[Dean D. Babic]

Hi all, 

the new release is up in here:

https://northwind.pythonanywhere.com/

And it is getting very close to the Access desktop App.

Thanks to Danijel Kaurin, who implemented the new "Home Page" and most of the backhand stuff!

The "Inventory to Reorder" is a Virtual Table. The "Active Orders" is a table.

"Quick Links" is a code in Virtual Table "Home Page" in catalogs, and it is shown first.  

The "Send Mail" is used on Employees, Shippers and Customers, and pulling email address from the field. 

That is all for now.

Enjoy

[Dean D. Babic]

Hi all, 

it has bee a while for an update on this app.

Today, I finally tested the application and managed to Pump >5 000 000 records!

Then did the Analytics RFM pandas, numpy and matplotlib test after I updated the Python code.

Here is the result:

sqlite3 northwind.sqlite3

SQLite version 3.43.2 2023-10-10 13:08:14

Enter ".help" for usage hints.

sqlite> SELECT count(*) FROM products p LEFT JOIN order_details o ON p.id=o.product_id WHERE Date_Allocated is not NULL and Date_Allocated > '2008-01-01' and TOTAL_PRICE is not NULL;

4939745

Now the RFM Pandas test which pulls all 5mil rows into the memory to present the result in less than a minute:

127.0.0.1 - - [15/Apr/2024 21:43:19] "POST /api HTTP/1.1" 200 -

RFM Started

Plotted

127.0.0.1 - - [15/Apr/2024 21:44:11] "POST /api HTTP/1.1" 200 -

This is actually phenomenal! 

Cheers