The following scripts and commentary are a general guide that I created while migrating a customer from SugarCRM Community Edition with the Advanced OpenSales plugin to SugarCRM Professional using the built-in Quotes and Products modules. It's not perfect, and your mileage may very, but I hope it assists someone else as a solid starting point for a similar migration from AOS/SuiteCRM back to SugarCRM Pro.
These scripts ignore the PDF Template and Contract functionality from AOS. These were not relevant to my project, so I have no reason to attempt them.
insert into product_templates
(id, deleted, date_entered, date_modified, modified_user_id, created_by, type_id, manufacturer_id, category_id, name, mft_part_num, vendor_part_num, date_cost_price, cost_price, discount_price, list_price, cost_usdollar, discount_usdollar, list_usdollar, currency_id, currency, status, tax_class, date_available, website, weight, qty_in_stock, description, support_name, support_description, support_contact, support_term, pricing_formula, pricing_factor)
select
id, deleted, date_entered, date_modified, modified_user_id, created_by, type, null, category, name, part_number, maincode, null, cost, null, price, cost, null, price, currency_id, null, null, null, null, url, null, null, description, null, null, null, null, null, null
from
aos_products;
insert into product_categories
(id,deleted,date_entered,date_modified,modified_user_id,created_by,
name,list_order,description,parent_id)
select distinct(category),0,now(),now(),1,1,category,
null,null,null from aos_products where aos_products.deleted=0
and aos_products.category is not null
and aos_products.category <> '';
insert into product_types
(id,deleted,date_entered,date_modified,modified_user_id,created_by,name,description,list_order)
select
distinct(type),0,now(),now(),1,1,type,null,null
from aos_products where aos_products.deleted=0;
insert into products_audit
select * from aos_products_audit;
insert into quotes
(id, name, date_entered, date_modified, modified_user_id, created_by, description, deleted, assigned_user_id, team_id, team_set_id, shipper_id, currency_id, taxrate_id, show_line_nums, calc_grand_total, quote_type, date_quote_expected_closed, original_po_date, payment_terms, date_quote_closed, date_order_shipped, order_stage, quote_stage, purchase_order_num, quote_num, subtotal, subtotal_usdollar, shipping, shipping_usdollar, discount, deal_tot, deal_tot_usdollar, new_sub, new_sub_usdollar, tax, tax_usdollar, total, total_usdollar, billing_address_street, billing_address_city, billing_address_state, billing_address_postalcode, billing_address_country, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_postalcode, shipping_address_country, system_id)
select
id, name, date_entered, date_modified, modified_user_id, created_by, description, deleted, assigned_user_id, 1, 1, null, currency_id, null, null, null, null, expiration, null, terms_c, null, null, stage, stage, null, number, subtotal_amount, subtotal_amount, shipping_amount, shipping_amount, discount_amount, total_amt, total_amt, subtotal_tax_amount, subtotal_tax_amount, tax_amount, tax_amount, total_amount, total_amount, billing_address_street, billing_address_city, billing_address_state, billing_address_postalcode, billing_address_country, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_postalcode, shipping_address_country, null
from aos_quotes;
insert into quotes_accounts
(id,quote_id,account_id,account_role,date_modified,deleted)
select
UUID(),id,billing_account_id,'Bill To',now(),0
from aos_quotes where deleted=0;
insert into quotes_contacts
(id,quote_id,contact_id,contact_role,date_modified,deleted)
select
UUID(),id,billing_contact_id,'Bill To',now(),0
from aos_quotes where deleted=0;
insert into quotes_opportunities
(id,opportunity_id,quote_id,date_modified,deleted)
select
UUID(),opportunity_id,id,now(),0
from aos_quotes where deleted=0;
Note that the following fields were left out of our quote record mapping. If this was a deal-breaker, we could either create custom fields and map them in or concat() them all into the description field. I'm opting to leave them be for this system.
- approval_issue
- template_ddown_c
- term
- approval_status
- invoice_status
insert into quotes_audit
select * from aos_quotes_audit;
This one is tricky, because AOS does not have functionality for bundling products. Method is to simply populate the products table from aos_products_quotes, then go through and create the product bundles programmatically.
Note that we're importing the AOS field 'number' here as serial_number. That's wrong, but we need it for the import process. We'll nullify it later.
insert into products (
id, name, date_entered, date_modified, modified_user_id,
created_by, description, deleted, team_id, team_set_id,
product_template_id, account_id, contact_id,
type_id, quote_id, manufacturer_id, category_id, mft_part_num,
vendor_part_num, date_purchased, cost_price,
discount_price, discount_amount, discount_select, deal_calc,
deal_calc_usdollar, discount_amount_usdollar, list_price, cost_usdollar, discount_usdollar,
list_usdollar, currency_id, status, tax_class, website,
weight, quantity, support_name, support_description, support_contact,
support_term, date_support_expires, date_support_starts, pricing_formula, pricing_factor,
serial_number, asset_number, book_value, book_value_date, book_value_usdollar)
select
id, name, date_entered, date_modified, modified_user_id,
created_by, description, deleted, '1', '1',
product_id, null, null,
null, parent_id, null, null, null,
null, null, product_cost_price,
product_list_price, product_discount, if(discount='Percentage',1,0), abs(product_discount_amount),
abs(product_discount_amount), product_discount, product_list_price, product_cost_price, product_list_price,
product_list_price, null, null, null, null,
null, product_qty, null, null, null,
null, null, null, null, null,
number, null, null, null, null
from aos_products_quotes;
Ain't no law says a quote GUID can't be a bundle GUID. Makes the import easier.
insert into product_bundles
(id,team_id,team_set_id,deleted,date_entered,date_modified, modified_user_id,created_by,name,bundle_stage,description,tax,tax_usdollar,total,total_usdollar,subtotal_usdollar,shipping_usdollar,deal_tot,deal_tot_usdollar,new_sub,new_sub_usdollar,subtotal,shipping,currency_id)
select distinct(quotes.id),'1','1',0,date_entered,date_modified,modified_user_id,created_by,'Imported Bundle',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
from quotes;
that's why they share a GUID
insert into product_bundle_quote
(id,date_modified,deleted,bundle_id,quote_id,bundle_index)
select UUID(),date_modified,0,id,id,1 from quotes;
insert into product_bundle_product
(id,date_modified,deleted,bundle_id,product_id,product_index)
select UUID(),date_modified,0,quote_id,id,serial_number from products;
just like I promised.
update products set serial_number = null;
update product_bundles
join (
select
product_bundles.id as product_bundle_id,
sum(discount_price*quantity) as subtotal,
sum(deal_calc*quantity) as deal_tot
from products
join product_bundle_product on products.id = product_bundle_product.product_id
join product_bundles on product_bundles.id = product_bundle_product.bundle_id
group by product_bundles.id
) as temp on product_bundles.id = temp.product_bundle_id
set product_bundles.subtotal = temp.subtotal,
product_bundles.deal_tot = temp.deal_tot;
update product_bundles set new_sub = subtotal-deal_tot;
update product_bundles set total = new_sub;
update product_bundles set
total_usdollar=total,
subtotal_usdollar=subtotal,
deal_tot_usdollar=deal_tot,
new_sub_usdollar=new_sub;
(Always seems to be empty)
insert into products_audit
select * from aos_products_quotes_audit;