Author Topic: Status changed from DB not updating Sales Report  (Read 1318 times)

m.davide82

  • Jr. Member
  • **
  • Posts: 137
Status changed from DB not updating Sales Report
« on: June 15, 2016, 10:31:48 am »
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

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 9223
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 3.6.8
Re: Status changed from DB not updating Sales Report
« Reply #1 on: June 15, 2016, 12:27:15 pm »
#__virtuemart_orders also has a status column -- maybe reports uses that
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

m.davide82

  • Jr. Member
  • **
  • Posts: 137
Re: Status changed from DB not updating Sales Report
« Reply #2 on: June 15, 2016, 12:39:21 pm »
Thanks GJC.

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

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 9223
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 3.6.8
Re: Status changed from DB not updating Sales Report
« Reply #3 on: June 15, 2016, 12:42:24 pm »
#__virtuemart_order_items as well
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

m.davide82

  • Jr. Member
  • **
  • Posts: 137
Re: Status changed from DB not updating Sales Report
« Reply #4 on: June 15, 2016, 12:49:22 pm »
I'm updating it too...

I'm updating these tables:

- virtuemart_order_histories
- virtuemart_order_items
- virtuemart_orders

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3025
  • VirtueMart Version: 3.6.3
Re: Status changed from DB not updating Sales Report
« Reply #5 on: June 15, 2016, 15:44:08 pm »
"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!
regards
A

Joomla 3.9.12
php 7.2

m.davide82

  • Jr. Member
  • **
  • Posts: 137
[SOLVED] Re: Status changed from DB not updating Sales Report
« Reply #6 on: June 15, 2016, 17:10:46 pm »
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

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3025
  • VirtueMart Version: 3.6.3
Re: Status changed from DB not updating Sales Report
« Reply #7 on: June 16, 2016, 09:16:04 am »
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.
regards
A

Joomla 3.9.12
php 7.2