Author Topic: Export Orders & Products  (Read 72136 times)

chris.t.uk

  • Contributing Developer
  • Jr. Member
  • *
  • Posts: 98
Export Orders & Products
« on: February 07, 2008, 22:14:23 pm »
VM1.1.X users please visit
http://forum.virtuemart.net/index.php?topic=52215.0 for the latest package


I wasn't so sure that CSVImproved (mainly products) and RokReporter (frontend based) quite filled what I was trying to achieve so I've made a hack that adds the option of exporting order details from within the Virtuemart backed.

It allows for the export of all orders into a tab-delimited text file, compatible with Excel etc. (CSV in as good as sense as other exports) or Microsoft Excel format

You can limit selection with options of:
    1. Between selected dates
    2. After a date
    3. Before a date
or 4. All orders

Selection can be limited to the standard types of orders, i.e. only show completed, or pending.

Order header fields exported are:
Order ID, Customer Name, Invoice Address, Delivery Address, Vendor Name, Sub Total, Shipping, Tax, Total, Coupon Discount, Order Discount, Currency, Status, Date, Customer Note, IP Address

Option to show/hide orderlines in the report.
Orderline details exported are: SKU, Description, Quantity, Price (Ex), Total (Ex), Price (Inc), Total (Inc), Attributes

Is in the VirtueMart backend, under Orders->Export Orders
Also allows for individual orders to be exported Orders->List Orders (0.4+)



Also included is updated Froogle / Google Base Export (1.4.5) with support, and option, for sh404sef/std sef/normal urls.

This is now available in an RSS feed format for Google to index and add to Base. (Only for VM1.0)

See prior versions at:
http://forum.virtuemart.net/index.php?topic=14664.0 and
http://virtuemart.net/index.php?option=com_smf&Itemid=71&topic=26575.0

Download is in zip format

Please follow instructions in the README file, Step b. is crucial for it to work (it is simple to do!)

0.2.c.zip -> Works with Joomla-VM 1.0.14!
export-0.3.uzigo.zip -> is ready to be UnZippedandInstalled and should be ready to use. (No Modules/functions to manually install)
0.3.1 Added Customer First Name to Customer field and new Company Name Field
0.3.3 Show's database id's
0.4  Orders -> List Orders: Shows 'Export' column, allowing export of individual orders, exports all available data for machines to import into other applications
0.4.1 Email disclaimer text removal (Creeped into product export)
0.5  Option to Export in M$ Excel or CSV format, plus product feed for Google (go to VM Administration->Frontend features for more details[VM1.0 only])
NOTE: Two 0.5 versions please download the correct one for your Virtuemart
0.5.1 Patched for VM 1.1.2, Excel export fixed, same notes as for 0.5!
0.5.2 Patched for VM 1.1.2, problem with updated Joomla

Note 1:VM1.1.X users please visit the VirtueMart configuration page after installation so you can register the functions.

Note 2:VM1.1.X users must use the simple mode to show the 'Export Orders' option in the side menu bar or extended if your side menu shows in extended (there is not currently a top menu option)


[attachment cleanup by admin]
Hacks
Export Orders & Batch Update -> http://forum.virtuemart.net/index.php?topic=52215.0
UK Counties -> http://forum.virtuemart.net/index.php?topic=33968.0
Sites running Virtuemart
http://www.ladders247.co.uk http://www.tools247.co.uk http://www.mackay.co.uk
Synchronization
Synchronized with our instore system via PrePrepSQL and missing Image Remote Retrieval system ;-)

crony

  • Beginner
  • *
  • Posts: 46
Re: Export Orders & Products
« Reply #1 on: February 11, 2008, 20:34:48 pm »
Hello,

Just what I needed ! :)
But I have an error under VM 1.0.13a :

Code: [Select]
Erreur: Fonction Non Enregistrée. export_orders_csv n'est pas une fonction VirtueMart valide.
I have the same error using export_froogle...

Do I have to add something in language file ? In a module from VM ?

Thank you for your help !

chris.t.uk

  • Contributing Developer
  • Jr. Member
  • *
  • Posts: 98
Re: Export Orders & Products
« Reply #2 on: February 12, 2008, 11:51:28 am »
Well, isn't that a bugger, I've forgotten to include the readme that mentions registering the 2 functions.

It is on my PC at home so I will repost tonight.
Hacks
Export Orders & Batch Update -> http://forum.virtuemart.net/index.php?topic=52215.0
UK Counties -> http://forum.virtuemart.net/index.php?topic=33968.0
Sites running Virtuemart
http://www.ladders247.co.uk http://www.tools247.co.uk http://www.mackay.co.uk
Synchronization
Synchronized with our instore system via PrePrepSQL and missing Image Remote Retrieval system ;-)

chris.t.uk

  • Contributing Developer
  • Jr. Member
  • *
  • Posts: 98
Re: Export Orders & Products
« Reply #3 on: February 12, 2008, 19:36:22 pm »
Version 0.1a posted, should have the README file with instructions to avoid the issue Crony came up with. (add a function)
Hacks
Export Orders & Batch Update -> http://forum.virtuemart.net/index.php?topic=52215.0
UK Counties -> http://forum.virtuemart.net/index.php?topic=33968.0
Sites running Virtuemart
http://www.ladders247.co.uk http://www.tools247.co.uk http://www.mackay.co.uk
Synchronization
Synchronized with our instore system via PrePrepSQL and missing Image Remote Retrieval system ;-)

kessels1234

  • Full Member
  • ***
  • Posts: 208
Re: Export Orders & Products
« Reply #4 on: February 12, 2008, 20:38:16 pm »
HI,

I would love to see this feature in my website. I did everything that was told in the instructions and everything seems to work. Only, the feature i need that is the export orders doesn't work(I don't need the froogle export).It gives me a blank output with only the header information (only the names of the column). The rest is empty. Is it possible that this won't work with Virtuemart 1.0.10 and joomla 1.0.12?

any ideas on this

Thanks in advance
Danny

chris.t.uk

  • Contributing Developer
  • Jr. Member
  • *
  • Posts: 98
Re: Export Orders & Products
« Reply #5 on: February 12, 2008, 21:19:12 pm »
Could you run the following queries against your db for me please? (can you change the #__{vm} to your table leader)

/*Checks customer details*/
SELECT address_type_name FROM #__{vm}_user_info GROUP BY address_type_name;

There should be -default- in here, which I'm relying on as the first customer details per order. (maybe a language thing?)

/*Checking order types*/
Select * from #__{vm}_order_status;

Hopefully this will return:
+-----------------+-------------------+-------------------+------------+-----------+
| order_status_id | order_status_code | order_status_name | list_order | vendor_id |
+-----------------+-------------------+-------------------+------------+-----------+
|               1 | P                 | Pending           |          1 |         1 |
|               2 | C                 | Confirmed         |          2 |         1 |
|               3 | X                 | Cancelled         |          3 |         1 |
|               4 | R                 | Refunded          |          4 |         1 |
|               5 | S                 | Shipped           |          5 |         1 |
+-----------------+-------------------+-------------------+------------+-----------+

If not I will have to hack out selecting the types from this table, at the mo it just uses these hardcoded against the checkboxes.

Chris,
Hacks
Export Orders & Batch Update -> http://forum.virtuemart.net/index.php?topic=52215.0
UK Counties -> http://forum.virtuemart.net/index.php?topic=33968.0
Sites running Virtuemart
http://www.ladders247.co.uk http://www.tools247.co.uk http://www.mackay.co.uk
Synchronization
Synchronized with our instore system via PrePrepSQL and missing Image Remote Retrieval system ;-)

bulesz

  • Jr. Member
  • **
  • Posts: 194
Re: Export Orders & Products
« Reply #6 on: February 12, 2008, 22:13:06 pm »
Thats what I need! :)

I meen, all the x status orders could export to a csv file. (customer details, ordered items with prices)

Does it do the job for me?

crony

  • Beginner
  • *
  • Posts: 46
Re: Export Orders & Products
« Reply #7 on: February 13, 2008, 01:22:32 am »
Thank you ! I'll get it tested soon !

You mean I have to find something into order.orders_export.php, find/change the query ?
In phpmyadmin, getting errors with table names...

chris.t.uk

  • Contributing Developer
  • Jr. Member
  • *
  • Posts: 98
Re: Export Orders & Products
« Reply #8 on: February 13, 2008, 09:49:23 am »
Hi

Bulesz: Hopefully, that is the plan. You will notice that Kessells1234 is getting a blank report at the mo, this may be because of language/order status discrepancies for which I am awaiting feedback. This version has the order status/default customer details hard-coded which may prove the wrong thing to have done (multilingual?).

Crony: Hopefully you will not need to be running the queries. The idea behind this hack is that you don't have to run around in the database pulling out what should be available naturally.  I left the queries for Kessells1234 as I am trying to find out why he has a blank report. REMEMBER: The #__{vm} will not be in your database, (this is an internal thing for Joomla/Vm to deal with). If you have a standard installation try SELECT address_type_name FROM jos_vm_user_info GROUP BY address_type_name;Select * from jos_vm_order_status; this should keep phpMyAdmin happy.

Chris.
Hacks
Export Orders & Batch Update -> http://forum.virtuemart.net/index.php?topic=52215.0
UK Counties -> http://forum.virtuemart.net/index.php?topic=33968.0
Sites running Virtuemart
http://www.ladders247.co.uk http://www.tools247.co.uk http://www.mackay.co.uk
Synchronization
Synchronized with our instore system via PrePrepSQL and missing Image Remote Retrieval system ;-)

bulesz

  • Jr. Member
  • **
  • Posts: 194
Re: Export Orders & Products
« Reply #9 on: February 13, 2008, 09:59:32 am »
Great! We have made a similar "order management system", which listed the orders, and I could export the active statused orders to CSV for the booking program. But it was made for our own developed cart system, not for VM. :(

I mean, I could set the status of the order AND the products too! (for ge, one item couldnt get, the other is ok, so I could send only one product from the order of two)

And this was very important to could make the "shopping list" of our buyer. :) who was in the wholesale store.

So if you nedd our experience of using this kind of system "live" I'll be glad to help you! ;)

Cheers,

B.

please infomr me, when I could start to test it!

chris.t.uk

  • Contributing Developer
  • Jr. Member
  • *
  • Posts: 98
Re: Export Orders & Products
« Reply #10 on: February 13, 2008, 13:54:10 pm »
I've posted version 0.1b only difference is there is now a carriage return in the output file!!!!!

Possibility that Order details were all on one line so if you opened in notepad it appeared no orders are exported.

Please test and post any problems,

Many thanks,


Chris

Hacks
Export Orders & Batch Update -> http://forum.virtuemart.net/index.php?topic=52215.0
UK Counties -> http://forum.virtuemart.net/index.php?topic=33968.0
Sites running Virtuemart
http://www.ladders247.co.uk http://www.tools247.co.uk http://www.mackay.co.uk
Synchronization
Synchronized with our instore system via PrePrepSQL and missing Image Remote Retrieval system ;-)

crony

  • Beginner
  • *
  • Posts: 46
Re: Export Orders & Products
« Reply #11 on: February 13, 2008, 18:51:35 pm »
Ok I've tested !
But I still have a last problem... While try to export, the error message is:

"sorry we could not verify your security token , go back an try again"

In \administrator\components\com_virtuemart\classes\ps_main.php
Line 1144, "that thing is bugging me !" but I know I can't switch this off for obvious security reasons... :D

I've tested with an empty function to replace , the result is the same export page, without admin's buttons, nor skin, just the order.orders_export.php...

I'm testing this localy, but the database is coming from a backup from online site.

Any clue ? ???

PS: tested the 2 queries for info :
1.adress is default
2. I have different status because I'm using different hacks...So your script may have to read the user's personalised values ? (later release maybe ^^)

kessels1234

  • Full Member
  • ***
  • Posts: 208
Re: Export Orders & Products
« Reply #12 on: February 13, 2008, 19:33:56 pm »
Hi Chris,

I want to test this for you (and ofcourse for me) but what shoud I do:
I have PHPMyAdmin
Should I enter :
SELECT address_type_name FROM #__{vm}_user_info GROUP BY address_type_name;
in the textbox and then "start"

Same is for:
Select * from #__{vm}_order_status;

I have never done something like this before. Can this cause any harm to my database?

Please let me know what to do.
The first time I can check this is friday night. After that I will post you the results

Thanks for your help
Danny

crony

  • Beginner
  • *
  • Posts: 46
Re: Export Orders & Products
« Reply #13 on: February 13, 2008, 19:59:25 pm »
Hello Kessels,

If I may help a bit...

First of all : backup your database (in this case, you can't break it, but you MUST backup your database, just in case of a little mistake... ;) )

In phpMyadmin, select your database, click on "sql" at the top, then paste this query :
SELECT address_type_name FROM jos_vm_user_info GROUP BY address_type_name;

You'll get a result, could be "default"

Go back, and try again :
Select * from jos_vm_order_status;

That will show you your status list.

And voilĂ ...

crony

  • Beginner
  • *
  • Posts: 46
Re: Export Orders & Products
« Reply #14 on: February 13, 2008, 20:07:21 pm »
This hack as many may not work on 1.0.14, see this thread...

http://forum.virtuemart.net/index.php?topic=36070.msg111565

EDIT :
It's WORKING ! On vm 1.0.13a , but not on 1.0.14 (see link below)

But I still have a problem...The tax_order field is translated into CSV as dates fileds !!! 01/03/2008 for example...
The values are good into the jos_vm_orders table and into orders on the website... :'(

Can someone explains me how to trace this value between the export.php file and generated.txt file please ?  ;D