Exporting Data

  • Posts: 1
  • Thank you received: 0
13 years 8 months ago #7193

Does Hika Shop Essential or Business have a way to download order data in Excel format with all the order data on one row?

If so, is it customizable so I can choose which fields download (including custom fields)?

Thanks for your help.

Please Log in or Create an account to join the conversation.

  • Posts: 82683
  • Thank you received: 13337
  • MODERATOR
13 years 8 months ago #7194

It's not yet possible in HikaShop. That's something we want to add in the future though.

Please Log in or Create an account to join the conversation.

  • Posts: 10
  • Thank you received: 0
13 years 8 months ago #7482

That's a pity.

I am using the business version for registrations for events/courses/seminars. I need to get an overview of all registrants per course/seminar and their basic details.

Can you advise how I can achieve that?

Thanks in advance.

Please Log in or Create an account to join the conversation.

  • Posts: 82683
  • Thank you received: 13337
  • MODERATOR
13 years 8 months ago #7544

Could you be more precise ?

How do you handle the registrations on your website ? With custom entry fields ?

Please Log in or Create an account to join the conversation.

  • Posts: 10
  • Thank you received: 0
13 years 8 months ago #7549

I have created products (=seminars) that visitors can add to their cart as way of registering (nothing special here I guess). Payment is now done via bank transfer until the Dutch IDEAL plugin is available.

I would like to have a report (excel) with the following info:
ordered product(seminar), product id, first name, last name, address, zip code, city, country, date registered, name on certificate(customfield in address table), name of association(customfield in address table), order status.

The standard reports in the dashboard all don't have the user info (except ID and email) nor custom fields.

Does this make sense? I hope you can help me out here.

Please Log in or Create an account to join the conversation.

  • Posts: 82683
  • Thank you received: 13337
  • MODERATOR
13 years 8 months ago #7550

As I said in my first post on that thread there is no such function at the moment in hikashop. Retrieving all that information for each item purchased and making it a CSV file for excel is not something we can do like this. It will require some work.
We are currently in discussion with another user to do such custom work for a fee but it's not yet confirmed and will take us a few weeks.

Please Log in or Create an account to join the conversation.

  • Posts: 10
  • Thank you received: 0
13 years 8 months ago #7571

Thanks. Yes, I understood from the previous posts that it is not possible at the moment.

I was looking for some suggestions/tips/recommendations as a workaround until the solution is ready.

Do you/anyone have experience with this using Joomla extensions or something like that?

Please Log in or Create an account to join the conversation.

  • Posts: 82683
  • Thank you received: 13337
  • MODERATOR
13 years 8 months ago #7575

I don't know of such extensions capable of that. The only way is to develop an export function.

Please Log in or Create an account to join the conversation.

  • Posts: 23
  • Thank you received: 0
13 years 6 months ago #13526

Hi

I know there are extensions that enable you to get the database values and export as CSV. They are probably not that easy to use, but you can try them. Here is the link to JED.
extensions.joomla.org/extensions/migrati...data-import-a-export

@ctsang007: see my "handtekening", iDEAL plugin is ready.

Regards
David


iDEAL for Hikashop by Dutch iDEAL experts

Please Log in or Create an account to join the conversation.

  • Posts: 82683
  • Thank you received: 13337
  • MODERATOR
13 years 6 months ago #13554

Ah yes, good idea ! Indeed such extensions might be able to do the interim while we develop a great export directly in hikashop.

Please Log in or Create an account to join the conversation.

  • Posts: 73
  • Thank you received: 2
12 years 1 month ago #64902

I use (Joomla 2.5) Ari Data Tables to create table from SQL however, because the way Hika Shop does it db tables, there's not common field that I can make a join report. Right now I can only join orders and order products table (order_id in common) without being able to included the associated buyers name (not the choice I was looking for). This can give category and product filtering only.

www.ari-soft.com/vmchk/Joomla-Components...d-product-flyer.html

hope this helps.

Please Log in or Create an account to join the conversation.

  • Posts: 82683
  • Thank you received: 13337
  • MODERATOR
12 years 1 month ago #65008

Hi,

If you want to load the user address, including its name, you can use the order_billing_address_id to link the order table with the address table

Note that HikaShop already has such reporting tools:
www.hikashop.com/en/support/documentatio...hop-report-form.html
And that it is also possible to feed the ARI Data Tables extension a CSV so you could just export your orders via the corresponding button of the orders listing and use it instead of the tables. The CSV will contain all the information of the orders.

Please Log in or Create an account to join the conversation.

  • Posts: 73
  • Thank you received: 2
12 years 1 month ago #65104

Thank you for the information. For my needs I wanted to provide a restricted report view online with only specific fields by pulling via SQL; not allow them access to the backend reporting stats (which are good). Downloading via CSV I still need to format to get rid of those fields I don't want; especially all the empty fields. It's still not a clean easiest process to download for me but for now it will have to do as I've spent the money to buy your Essential level. Maybe I'm still missing something.

Last edit: 12 years 1 month ago by aheng5.

Please Log in or Create an account to join the conversation.

  • Posts: 82683
  • Thank you received: 13337
  • MODERATOR
12 years 1 month ago #65109

There is no interface to select the columns you want for the export for now. It's on the todo list for a future release.

Please Log in or Create an account to join the conversation.

  • Posts: 73
  • Thank you received: 2
12 years 1 month ago #65143

Ok so I'm successful in combining 3 tables to get the results I need (code below); however I'm having problems with the 'order_created' date. I don't know the right SQL code to convert this field to date format mm-dd-yyyy. I get a long digit similar to what I get when I export to cvs from the backend. Any suggestions? Thanks


SELECT DATE_FORMAT(jml_hikashop_order.order_created) as 'Order Date', jml_hikashop_order.order_status as 'Status', jml_hikashop_order.order_number as 'Order #',
jml_hikashop_address.address_firstname as 'First Name', jml_hikashop_address.address_lastname as 'Last Name',

jml_hikashop_address.address_telephone as 'Phone',
jml_hikashop_order.order_payment_method as 'Payment Method', Round(jml_hikashop_order.order_full_price,2) as

'Total', jml_hikashop_order_product.order_product_quantity as 'Qty', jml_hikashop_order_product.order_product_name

as 'Item', jml_hikashop_order_product.order_product_options as 'Options'
FROM jml_hikashop_address
JOIN jml_hikashop_order
ON
(jml_hikashop_address.address_user_id=jml_hikashop_order.order_user_id)
JOIN jml_hikashop_order_product
ON jml_hikashop_order.order_id=jml_hikashop_order_product.order_id

ORDER BY jml_hikashop_address.address_lastname DESC

Please Log in or Create an account to join the conversation.

  • Posts: 82683
  • Thank you received: 13337
  • MODERATOR
12 years 1 month ago #65145

You should do like that: DATE_FORMAT(DATE( FROM_UNIXTIME(jml_hikashop_order.order_created) ), '%m-%d-%Y' ) AS 'Order Date'

Please Log in or Create an account to join the conversation.

  • Posts: 73
  • Thank you received: 2
12 years 1 month ago #65152

THANK YOU :cheer: this completes what I need to do on my web site.

Here's my final sql coding. I just change my filter to the product code I want to report:

SELECT DATE_FORMAT(DATE( FROM_UNIXTIME(jml_hikashop_order.order_created) ), '%m-%d-%Y' ) AS 'Order Date', jml_hikashop_order.order_status as 'Status', jml_hikashop_order.order_number as 'Order #',
jml_hikashop_address.address_firstname as 'First Name', jml_hikashop_address.address_lastname as 'Last Name', jml_hikashop_address.address_telephone as 'Phone',
jml_hikashop_order.order_payment_method as 'Payment Method', Round(jml_hikashop_order_product.order_product_price,2) as 'Item Price', jml_hikashop_order_product.order_product_quantity as 'Qty', jml_hikashop_order_product.order_product_name as 'Item', jml_hikashop_order_product.order_product_options as 'Options'
FROM jml_hikashop_address
JOIN jml_hikashop_order
ON
(jml_hikashop_address.address_user_id=jml_hikashop_order.order_user_id)
JOIN jml_hikashop_order_product
ON jml_hikashop_order.order_id=jml_hikashop_order_product.order_id
WHERE jml_hikashop_order_product.order_product_name LIKE '%Member'
ORDER BY jml_hikashop_address.address_lastname DESC

Please Log in or Create an account to join the conversation.

  • Posts: 73
  • Thank you received: 2
12 years 1 week ago #71635

Hi, by chance what is the syntax after the date to include the time of the order? Thanks Alana

Please Log in or Create an account to join the conversation.

  • Posts: 82683
  • Thank you received: 13337
  • MODERATOR
12 years 1 week ago #71905

It should be:
DATE_FORMAT(DATE( FROM_UNIXTIME(jml_hikashop_order.order_created) ), '%m-%d-%Y %H:%i' ) AS 'Order Date'

dev.mysql.com/doc/refman/5.5/en/date-and...function_date-format

Please Log in or Create an account to join the conversation.

  • Posts: 21
  • Thank you received: 1
10 years 10 months ago #134894

is there any progress in exporting functionality?

Last edit: 10 years 10 months ago by mavievs. Reason: grammar

Please Log in or Create an account to join the conversation.

Time to create page: 0.091 seconds
Powered by Kunena Forum