Hello,
I have a script from my management software that updates the order status in these tables:
- virtuemart_order_histories
- virtuemart_order_items
- virtuemart_orders
But when I go to Sales Report and I use the filter for each Order Status, the one updated by my software is not visible in the report.
Anyone can help me please?
Thanks a lot in advance!
PS.
If I update the order manually, it works in right mode.
#__virtuemart_orders also has a status column -- maybe reports uses that
Thanks GJC.
I've just controlled and I'm updating this table too... :'(
#__virtuemart_order_items as well
I'm updating it too...
I'm updating these tables:
- virtuemart_order_histories
- virtuemart_order_items
- virtuemart_orders
"If I update the order manually, it works in right mode."
Check in the database to see what the differences are between your script updated value and the system updated value for the "order_status" field.
From my quick test it appears to just check the "order_status" in virtuemart_order_items and ignores the status of the virtuemart_orders
I do something similar and it works perfectly well
Try manually adjusting an order_item entry directly in the dbase and you will see the report change
Just a suggestion - have you considered running your reports as pivot tables within MS excel - using ODBC
This will give you lots of flexibility that is not possible using this one stock report!
Thank you very much AH.
I just put the site in local, I deleted all the orders, I did 2 orders and manually I did this:
First I changed the table:
virtuemart_order_items to update in the right mode the REPORTS
After I updated the table
virtuemart_orders because I need to know in the backend the REAL STATUS of the order.
Third, I updated the table:
virtuemart_orders_histories because I need to know the history of the status changes.
It works.
Now, I have to change the script that changes the status.
Thank you very much for your suggestion AH.
I'm sorry, but I don't know ODBC, I will have to study! :D
I would use sql to write your own reports
At some point you will want to do this if you are serious about your store.
Using a third party plugin will always have its benefits of simplicity, but also restrictions. Plus using your own backup data and doing a bit of sql will really help you better understand your data (not just for reporting)
Create a copy of the dbase on a local machine
Use heidisql to write simple sql queries on the sales database (and whatever other queries you want)
You could even take them into a spreadsheet for further analytics using pivot tables and excels native data from an sql database linkage
You will need to use the relevant odbc driver for mysql on your machine
This is relatively straightforward if you understand some sql and how to get data into excel from an external source use
Here are some links that may help
Instead why not create local copies of the database for reporting and then work on them using heidisql http://www.heidisql.com/
Using "offline" data and direct local connections via either Heidisql or Excel ODBC will save you having to write specific code, reduces live server load and is Extremely flexible!
Updating the local dbase with a live db dump, takes seconds in heidisql.
Using heidisql, in many cases" will save you having to write specific code to get data as you can save the SQL result directly to a CSV file from heidisql
You can also build "snippets" of sql for re-use
you could connect to the database using the mysql odbc connector then create a report using excel and mysql
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html (http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html)
excel data connections
http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx#BMunderstanding_data_connections (http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx#BMunderstanding_data_connections)
and
http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx (http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx)
I hope this helps you and anyone out there that wants to get to the data and really get to grips with their store.
Give it a go.