Status changed from DB not updating Sales Report

[m.davide82]

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.

[GJC Web Design]

#__virtuemart_orders also has a status column -- maybe reports uses that

[m.davide82]

Thanks GJC.

I've just controlled and I'm updating this table too... 

[GJC Web Design]

#__virtuemart_order_items as well

[m.davide82]

I'm updating it too...

I'm updating these tables:

- virtuemart_order_histories
- virtuemart_order_items
- virtuemart_orders

[AH]

"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!

[m.davide82]

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! 

[AH]

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
excel 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


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.