Importing Orders

[dmm2020]

I need to import orders from the old database. I already have the products imported and made sure the product ID matches what I am going to import.

How do you suggest I go about importing the orders over since they are multiple table laid out?

What are the minimum fields I need to populate so that the import don't crash the cart?

Essentially I am going to import everything based on username as the link (join).

Do I also need to import the customer data over OR is that going to happen automatically as soon as the member enters our store?

[Greystoke]

Is the old database from osCommerce or from vB Osc Shopping Cart Pro.

[dmm2020]

Neither:

below is the table structure of existing orders table so far:

CREATE TABLE IF NOT EXISTS `orders` (
`primkey` int(11) NOT NULL auto_increment,
`invoice` int(11) NOT NULL default '0',
`product` int(11) NOT NULL default '0',
`quantity` int(11) NOT NULL default '0',
`coupon` varchar(32) NOT NULL default '',
`status` varchar(255) NOT NULL default '',
`user` int(11) NOT NULL default '0',
`serial` varchar(255) NOT NULL default '',
`agareskey` varchar(255) NOT NULL default '',
`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`username` varchar(100) default NULL,
PRIMARY KEY (`primkey`)

I am thinking it's WP-Cart but prefix is elgg_ for most of tables in database I drew the orders SQL from.

[Greystoke]

So when a customer from the old database creates a new account, you want to add their orders from your old shop.
This isn't going to be easy as fields and tables are more than likely going to be totally different as its not oscommerce.

First off their old customer id is not going to be the same, so you will have to make sure that when you are importing their old orders that they get assigned to the new current customer id.

If you send me the database, I will take a look.

[dmm2020]

The database is HUGE so sending the database will not work. I have to export it in bits and pieces. That's why I am linking the username when I do the SQL than the userid.

I am a database programmer and do know how to export via SQL. I just need to know what has to be populated and how each of the customer and order tables are linked so that whole order is displayed online when done. I do know the order history will be non-existent as hold database does not provide for order history.

[Greystoke]

When an order is placed, it is added to the tables:

osc_orders
osc_orders_products
osc_orders_products_attributes (only that product has attributes)
osc_orders_status_history
osc_orders_total


Here is an example of what got inserted into the database when a product was purchased:


insert into osc_orders (orders_id, customers_id, customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, last_modified, date_purchased, orders_status, orders_date_finished, currency, currency_value) values ('2', '1', 'name', '', 'street', 'suburb', 'city', 'postcode', 'state', 'country', 'tele no', 'email', '1', 'name', '', 'street', 'suburb', 'city', 'postcode', 'state', 'country', '1', 'name', '', 'street', 'suburb', 'city', 'postcode', 'state', 'country', '1', 'Cash on Delivery', '', '', '', '', NULL, '2010-06-04 03:35:34', '1', NULL, 'USD', '1.000000');
insert into osc_orders_products (orders_products_id, orders_id, products_id, products_model, products_name, products_price, final_price, products_tax, products_quantity, vendors_id) values ('2', '2', '26', 'MSIMEXP', 'Microsoft IntelliMouse Explorer', '64.9500', '64.9500', '0.0000', '1', '1');
insert into osc_orders_products_attributes (orders_products_attributes_id, orders_id, orders_products_id, products_options, products_options_values, options_values_price, price_prefix) values ('1', '2', '2', 'Model', 'PS/2', '0.0000', '+');
insert into osc_orders_status_history (orders_status_history_id, orders_id, orders_status_id, date_added, customer_notified, comments) values ('2', '2', '1', '2010-06-04 03:35:34', '1', '');
insert into osc_orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('4', '2', 'Sub-Total:', '$64.95', '64.9500', 'ot_subtotal', '1');
insert into osc_orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('5', '2', 'Flat Rate (Best Way):', '$5.00', '5.0000', 'ot_shipping', '2');
insert into osc_orders_total (orders_total_id, orders_id, title, text, value, class, sort_order) values ('6', '2', 'Total:', '<b>$69.95</b>', '69.9500', 'ot_total', '4');

[dmm2020]

I think I can figure that part out...question I have is do I need to populate the customers table or is that done automatically when they log in and then enter the store?

Eventually, I will have the orders table that I exported out of our old table link to the new customer ID and ready to import. Just want to be aware of all the relationships of each of the tables that have to be processed in this import.

[Greystoke]

When the customer is logged in and then enters the store for the first time the Table osc_customers get populated like:


insert into osc_customers (customers_id, customers_username, customers_gender, customers_firstname, customers_lastname, customers_dob, customers_email_address, customers_default_address_id, customers_telephone, customers_fax, customers_password, customers_newsletter) values ('2', 'Greystoke', 'm', 'Greystoke', '', '0000-00-00 00:00:00', 'example@example.com', '0', '', NULL, '', '1');


Their first and last name, and telephone and fax will get updated when they add their name and address within their account on the store.

The Table osc_address_book will need to be updated as well, as this only gets updated when they add their name and address within their account on the store.