Osscomerce to virtuemart?
ptannjr:
Problem i have is i dont know how to do "basic sql statements"
pictures i can upload if need be but dont want to have to manually put in all the other information again.
xperidev:
Here are the sql statements I've got so far.
Some fields are hardcoded to fit my environment so be carefull with that!!
Make a copy of your database and try it out on a test site first ofcourse.
What's the idea?
Run sql statement on your OSC database, export the output to a csv, then import it to your Joomla/VM database (using phpmyadmin)
It's not complete (orders still missing) but for me it did the job, because now I have all my products available in VM AND in mulitple languages. In the meantime, I also managed to create a hack on the Joomla login plugin, so I can re-use the passwords of my OSC user database.
Here are the sql statements:
Code:
-----sql to create vm_product table ---------------
select products.products_id as product_id,
products.manufacturers_id as vendor_id,
0 as product_parent_id,
products.products_model as product_sku,
products_description.products_name as product_s_desc,
products_description.products_description as product_desc,
concat('resized/',products.products_image) as product_thumb_image,
products.products_image as product_full_image,
replace(products.products_status,'1','Y') as product_publish,
products.products_weight as product_weight,
'kilo' as product_weight_uom,
'0.0000' as product_length,
'0.0000' as product_width,
'0.0000' as product_height,
'cm' as product_lwh_uom,
products_description.products_url as product_url,
products.products_quantity as product_in_stock,
products.products_date_available as product_availability_date,
'' as product_availability,
'N' as product_special,
0 as product_discount_id,
NULL as ship_code_id,
products.products_date_added as cdate,
products.products_last_modified as mdate,
products.products_model as product_name,
0 as product_sales,
'' as attribute,
'' as custom_attribute,
products.products_tax_class_id as product_tax_id,
'stuk' as product_unit,
0 as product_packaging,
'N,N,N,N,N,N,20%,10%,' as child_options,
'none,0,0,1' as quantity_options,
'' as child_option_ids,
products.products_ordered as product_order_levels
from products inner join products_description on products.products_id = products_description.products_id
where language_id = 1
/* products.products_status as product_publish (replace 1 with Y) !!!!
products_price
manufacturers_id
*/
-------------------sql to create vm_category table-----------------
select categories.categories_id as category_id,
1 as vendor_id,
categories_description.categories_name as category_name,
'' as category_description,
categories.categories_image as category_thumb_image,
categories.categories_image as category_full_image,
'Y' as category_publish,
categories.date_added as cdate,
categories.last_modified as mdate,
'managed' as category_browsepage,
1 as products_per_row,
'flypage.tpl' as category_flypage,
categories.sort_order as list_order
from categories inner join categories_description on categories.categories_id=categories_description.categories_id
where categories_description.language_id = 1
----------------sql to create vm_category_xref table-------------------
select parent_id as category_parent_id,
categories_id as category_child_id,
NULL as category_list
from categories
-----------------sql to create vm_product_category_xref----------------
select categories_id as category_id,
products_id as product_id,
1 as product_list
from products_to_categories
------sql to create vm_product_price table-----
select '' as product_price_id,
products_id as product_id,
products_price as product_price,
'EUR' as product_currency,
0 as product_price_vdate,
0 as product_price_edate,
1240843730 as cdate,
1240843730 as mdate,
5 as shopper_group_id,
0 as price_quantity_start,
0 as price_quantity_end
from products
-----sql to create joomfish translation table----
--- run sql, via phpmyadmin export to csv using ^ and ~, save as file, import into new table using same delimiters
select '' as id,
2 as language_id,
products_id as reference_id,
'vm_product' as reference_table,
'product_s_desc' as reference_field,
products_name as `value`,
'' as original_value,
'' as original_text,
'2009-04-29 10:52:42' as modified,
62 as modified_by,
1 as published
from products_description
where language_id = 2
union
select '' as id,
2 as language_id,
products_id as reference_id,
'vm_product' as reference_table,
'product_desc' as reference_field,
products_description as `value`,
'' as original_value,
'' as original_text,
'2009-04-29 10:52:42' as modified,
62 as modified_by,
1 as published
from products_description
where language_id = 2
union
select '' as id,
2 as language_id,
products_id as reference_id,
'vm_product' as reference_table,
'product_name' as reference_field,
products_description as `value`,
'' as original_value,
'' as original_text,
'2009-04-29 10:52:42' as modified,
62 as modified_by,
1 as published
from products_description
where language_id = 2
---- users for vm jos_user ----
select
customers_id as id,
concat(customers_firstname,' ',customers_lastname) as name,
customers_email_address as username,
customers_email_address as email,
customers_password as password,
'Registered' as usertype,
0 as block,
0 as sendEmail,
18 as gid,
customers_dob as registerDate,
'0000-00-00 00:00:00' as lastvisitDate,
'' as activation,
concat(customers_default_address_id,'^',
customers_newsletter,'^',
member_level,'^',
"admin_language=",customers_language_id) as params
from customers
----sql for jos_core_acl_aro
select
customers_id as id,
'users' as section_value,
customers_id as value,
0 as order_value,
concat(customers_firstname,' ',customers_lastname) as name,
0 as hidden
from customers
----sql for jos_core_acl_groups_aro_map
select
18 as group_id,
'' as section_value,
customers_id as aro_id
from customers
----- jos_vm_user_info -----
--- SELECT * FROM `customers` inner join
--- address_book on customers.customers_id = address_book.customers_id
select
MD5(date_format(date_add( sysdate( ) , INTERVAL FLOOR( 1 + ( RAND( ) *998 ) ) MICROSECOND ) , "%Y%m%d%H%i%s%f" ) ) as user_info_id,
`customers`.customers_id as user_id,
'BT' as address_type,
'-default-' as address_type_name,
entry_company as company,
entry_gender as title,
customers_lastname as last_name,
customers_firstname as first_name,
'' as middle_name,
customers_telephone as phone_1,
'' as phone_2,
customers_fax as fax,
concat(entry_street_address,', ',entry_suburb) as address_1,
'' as address_2,
entry_city as city,
entry_state as state,
countries_iso_code_3 as country,
entry_postcode as zip,
customers_email_address as user_email,
NULL as extra_field_1,
NULL as extra_field_2,
NULL as extra_field_3,
NULL as extra_field_4,
NULL as extra_field_5,
customers_dob as cdate,
customers_dob as mdate,
'shopper' as perms,
'' as bank_account_nr,
'' as bank_name,
'' as bank_sort_code,
'' as bank_iban,
'' as bank_account_holder,
'' as bank_account_type
from `customers` inner join address_book on customers.customers_id = address_book.customers_id
INNER JOIN countries ON address_book.entry_country_id = `countries_id`
---------sql to create vm_vendor table------
select customers.customers_id as vendor_id,
entry_company as vendor_name,
entry_lastname as contact_last_name,
entry_firstname as contact_first_name,
'' as contact_middle_name,
entry_gender as contact_title,
customers_telephone as contact_phone_1,
'' as contact_phone_2,
customers_fax as contact_fax,
'' as contact_email,
'' as vendor_phone,
concat(entry_street_address,', ',entry_suburb) as vendor_address_1,
'' as vendor_address_2,
entry_city as vendor_city,
entry_state as vendor_state,
countries_iso_code_3 as vendor_country,
entry_postcode as vendor_zip,
'' as vendor_store_name,
'' as vendor_store_desc,
0 as vendor_category_id,
'' as vendor_thumb_image,
'no_image_manufacturer.gif' as vendor_full_image,
'EUR' as vendor_currency,
customers_dob as cdate,
customers_dob as mdate,
'' as vendor_image_path,
'' as vendor_terms_of_service,
'' as vendor_url,
0 as vendor_min_pov,
0 as vendor_freeshipping,
'1|€|2|,| |2|1' as vendor_currency_display_style,
'EUR' as vendor_accepted_currencies,
'{storename}
{address_1}
{address_2}
{city}, {zip}' as vendor_address_format,
'%A, %d %B %Y %H:%M' as vendor_date_format
from customers inner join address_book on customers.customers_id = address_book.customers_id
INNER JOIN countries ON address_book.entry_country_id = `countries_id`
Maksym:
Quote from: 02bigboss on December 27, 2007, 13:23:06 PM
Hello
Can migrate the database of osscomerce to virtuemart?
I need it :'(
Thanks
Sorry for my bad English ;)
Hi, there!
There is a powerfull shopping cart migartion service that will satisfy your desires - Cart2Cart.
On this page you will find detailed information about migration options to/from Joomla Virtuemart platform.
billy30:
try cart2cart they are quite good at oscommerce to virtuemart data migration
Navigation
[0] Message Index
[*] Previous page