Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Generate inventory report from osc2.3


Rachael w.

Recommended Posts

Any chance anyone knows of an add-on to run a full csv report of the stores inventory?

I have more than one image but I could modify it if I had a sort of template to work with. I also have the in/out of stock add-on so it would be nice to pull that in too.  And, to be able to change the category ID in the report so I can pull just by category rather than one huge csv. 

 

Something that had an output with this:

Product ID  |  Product Name | Product UPC | Product Price | Product Description | Quantity | In/out Stock | Item Number | Weight | Category String |  Image 1 | Image 2 | Image 3 |

 

Thank you in advance for your assistance! 

Link to comment
Share on other sites

9 hours ago, Rachael w. said:

change the category ID in the report so I can pull just by category rather than one huge csv. 

The simplest way to do this is to use your db. Simply export the required tables in the format you prefer and then simply use your editing tool to sort the file as required.

If you are happy doing a little coding then you should take a look at this open source PHP report frame work.

https://www.koolreport.com/

Using this you can produce any report you like from your data base. They have example showing how easy it is to use.

https://www.koolreport.com/examples/reports/basic/sales_by_country/

 

Link to comment
Share on other sites

There is an addon that I use called stocktaking cost which is here https://apps.oscommerce.com/50HQp&stocktaking-cost.

It can export to CSV and you can select categories that are not listed, but that has to be done manually. I generate a full report then edit that report for what I want. It does need the initial page layout, and the closing page layout to be changed to match that of the latest pages, but nothing too serious and the changes can be compared to almost any other admin report page.

Its a lot less risky than messing with your database if you are unsure of what you are doing.

REMEMBER BACKUP, BACKUP AND BACKUP

Link to comment
Share on other sites

2 hours ago, 14steve14 said:

Its a lot less risky than messing with your database

Not sure using a 10 year old add-on is less risky than using a proper db tool like phpAdmin? This is the recomended way of exporting CSV or other formats of your db to your pc to work with safely. You can then use simple tools like excel to sort and produce any report you like. All with no risk to your original db or any need to edit code to add an old add-on.

Hey but that's just my opinion.

This is how simple and safe using phpAdmin is.

 

 

Link to comment
Share on other sites

Just remember that not everyone has the same skill levels as you. Most are just store owners with no or only basic coding skills. Most wont even have accessed their database in the past.

Only trying to help, but wont bother next time.

REMEMBER BACKUP, BACKUP AND BACKUP

Link to comment
Share on other sites

14 minutes ago, 14steve14 said:

skill levels as you

Skill level required Zero as shown by the example! Most store owners I interact with are quite smart individuals that are quite capable of doing this sort of thing.

Why you would not bother to help the OP next time is quite a strange but hey that's upto you.

 

Link to comment
Share on other sites

@Whitehat Hennry has build this into my shop on my request. 

I don't know the code, but it looks like it was build into my ïnventory_report.php file.

I could provide the file if it contains no private data. Or just contact Henry.

 

Link to comment
Share on other sites

For those who are able to do some basic script work this is a very good php based report generator. Simply install in subdirectory on your site and your able to generate reports from your database. Just be carefull not to run the tutorials on your storedata base.

http://www.reportico.org/site2/index.php

image.png.8d14454d9fe6689e6a13043b41a808fd.png

image.thumb.png.4c4e96a9715274e0403aefc6b6ec5582.png

 

Link to comment
Share on other sites

If your wondering why would you need to use this, the answer is quite simple. It is totally independent of your cart so no need to make any changes to core

files or even install add-on’s in order to make a new report.

You can make as many new reports as you like and keep and run them when ever you like allowing your store to carry on running independently.

All your ever doing is simply pulling data from your store data base.

installation is very simple just copy the files into a subdirectory under you admin, then simply using your browser go to the start.php file and thats it, it will run

and ask you to enter a admin password that done just follow the instruction.

If you want to run the tutorial then I would recomend making a new database just for those. It comes with a number of very good tutorial.

 

Link to comment
Share on other sites

22 minutes ago, honda4 said:

is the possibility to show which product I sold to which customer on which date

But that is shown in the admin side in orders? You could simply use the code in that to produce a report if required or just add a print function in the orders to print out for a given date range. But yes you are right the reporting in osC is poor.

image.png.3de2f790f08aa0fce9b4849cd9e7ca9a.png

 

Link to comment
Share on other sites

Zahid, Yes I can see the sales per customer, but I have a recall for a product, I do not have the correct tools. It's not often, but well.

If I could I would build a tool in which I could enter the productname (which is a partnumber in my case), and the tool would give me the customername-date ordered and ordernumber.

And maybe total sales - very maybe per period 🙂

Link to comment
Share on other sites

😊 unfortunatly Rene your talking to the wrong person, like you I try and avoid doing db work. I prefer simple click and get options to geting data from the db.

It shoud not be too hard to change the script in that file. All you need to do is add a "Where" to the script something like this.

SELECT DISTINCT op.products_name, op.products_price, o.customers_name, o.customers_email_address, o.date_purchased
FROM orders o
LEFT JOIN orders_products op
ON o.orders_id = op.orders_id
Where products_model = 'Test MSIMEXP'
ORDER BY op.products_name

when you run that in phpAdmin SQL window you will get. It's just a case of looking at the tables you need to join in order to pull the infomation you need, then select the filter you need and then sort the results.

Here we pick the tables orders and orders_products from which we pull

op.products_name, op.products_price, o.customers_name, o.customers_email_address, o.date_purchased

we say only

Where products_model = 'Test MSIMEXP'

and then sort results by

products_name

it's a case of just carfully plan out what you need and then test script.

 

I know there will be SQL gurus that say thats not how to do it you should do this! but at the end of the day I just need to pull the required data and if it works it will do. 😊

image.png.d743ddca858f656f5ce93b835621c8cc.png

 

Link to comment
Share on other sites

16 hours ago, honda4 said:

Zahid, Yes I can see the sales per customer, but I have a recall for a product, I do not have the correct tools. It's not often, but well.

If I could I would build a tool in which I could enter the productname (which is a partnumber in my case), and the tool would give me the customername-date ordered and ordernumber.

And maybe total sales - very maybe per period 🙂

Search the addons area. I had the exact same problem with a recal, and found one that did what I wanted. It did need some modification to for the admin page style as it was an older addon, but I cant give you the name as it will mean me advising people to use an old addon and that is not the done thing apparently. But there are several there already to do what you want.

REMEMBER BACKUP, BACKUP AND BACKUP

Link to comment
Share on other sites

32 minutes ago, 14steve14 said:

not the done thing apparently

Odd if it's a usefull add-on then please post and hopully it can be updated and added to the app's again! Or if you have it already updated even better .

I've linked old add-ons all the time how else will people know what to update?

 

Link to comment
Share on other sites

18 hours ago, honda4 said:

If I could I would build a tool in which I could enter the productname

Try this, it an update of an old report it uses a dropdown menu to select product and you can set time frame also, gives a list of all customers that bought a particular product.

You can develop it further if yo u like to add email and stuff.

It's for the admin site so you will need to edit some code to ad the report to the reports box.

Or just copy all the files over and simply call the file in your browser!           yoursite/admin/stats_customers_per_product.php ?

hope it helps.

image.png.091ed36c9ed2e9b9b8b431000a836ec2.png

Show-customers-per-products-ordered-CE.zip

 

Link to comment
Share on other sites

Sorry, I had surgery right after I posted. 

I can pull it directly from the database, but I maybe just lazy. I have over 300,000 products. I just wondered if there was something out there already that I didn't know about. When I am able to sit and look closer at this thread and my head is less fuzzy I'll see what I can do. 

Thank you all!

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...