Export Orders & Products

[chris.t.uk]

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]

[crony]

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]

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.

[chris.t.uk]

Version 0.1a posted, should have the README file with instructions to avoid the issue Crony came up with. (add a function)

[kessels1234]

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]

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,

[bulesz]

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]

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]

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.

[bulesz]

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]

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

[crony]

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

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]

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]

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]

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 ?