SugarCRM Notes Import (Young)

-- Backstory: customer's team was able to import all of their existing
-- Accounts/Contacts/Leads into SugarCRM all by themselves, but not the notes. So they
-- handed us three Excel documents with the notes exported from their old
-- system. Each held similar (but not quite the same) data/field definitions.
--
-- I cleaned the files up a bit using Excel/Calc and saved these three
-- files as CSVs and put them into a MySQL database for easier manipulation. The tricky
-- part of importing Notes is that you need to related them to something, so I had to
-- export Accounts/Contacts/Leads from SugarCRM to get the SugarCRM GUIDs for these
-- records. I mashed 'em all together into a master 'notes' table and exported to
-- a single CSV file which should, hopefully, import pretty cleanly into SugarCRM.
--
-- Only caveat on the import was to skip importing the ID field. That was just a local
-- ref and not a very quality SugarCRM GUID.
--
-- In total, 1104 notes were succesfully imported with Account/Lead/Contact references, and
-- of those that didn't make it to the system, it is because I couldn't match them with
-- any of the records in SugarCRM. 415 Accounts Notes, 6 Contact Notes and 7 Lead Notes were
-- skipped over.

-- mysql -u root -p young_moving_notes_import --local-infile=1

create table account_notes (
    id int not null auto_increment primary key,
    subject varchar(255),
    description text,
    date datetime,
    account_name varchar(255)
);

create table contact_notes (
    id int not null auto_increment primary key,
    date datetime,
    subject varchar(255),
    description text,
    first_name varchar(255),
    last_name varchar(255),
    account_name varchar(255)
);

create table leads_notes (
    id int not null auto_increment primary key,
    subject varchar(255),
    date datetime,
    description text,
    first_name varchar(255),
    last_name varchar(255)
);

create table sugar_accounts (
    account_name varchar(255),
    id varchar(40)
);

create table sugar_contacts (
    first_name varchar(255),
    last_name varchar(255),
    id varchar(40)
);

create table sugar_leads (
    first_name varchar(255),
    last_name varchar(255),
    id varchar(40)
);

load data local infile 'account_notes.csv' into table account_notes
fields terminated by ',' enclosed by '"' lines terminated by '\n' IGNORE 1 LINES
(subject,description,@var,account_name)
set date = STR_TO_DATE(@var,'%m/%e/%Y');

load data local infile 'contact_notes.csv' into table contact_notes
fields terminated by ',' enclosed by '"' lines terminated by '\n' IGNORE 1 LINES
(@var,subject,first_name,last_name,account_name)
set date = STR_TO_DATE(@var,'%m/%e/%Y');

load data local infile 'lead_notes.csv' into table leads_notes
fields terminated by ',' enclosed by '"' lines terminated by '\n' IGNORE 1 LINES
(subject,@var,description,first_name,last_name)
set date = STR_TO_DATE(@var,'%m/%e/%Y');

load data local infile 'Accounts.csv' into table sugar_accounts
fields terminated by ',' enclosed by '"' lines terminated by '\n' IGNORE 1 LINES
(account_name,id);

load data local infile 'Contacts.csv' into table sugar_contacts
fields terminated by ',' enclosed by '"' lines terminated by '\n' IGNORE 1 LINES
(first_name,last_name,id);

load data local infile 'Leads.csv' into table sugar_leads
fields terminated by ',' enclosed by '"' lines terminated by '\n' IGNORE 1 LINES
(first_name,last_name,id);

alter table account_notes add column account_id varchar(36);
alter table contact_notes add column contact_id varchar(36);
alter table leads_notes add column leads_id varchar(36);

-- Associates 386 of the 801 Total
update account_notes
join sugar_accounts on account_notes.account_name = sugar_accounts.account_name
set account_notes.account_id = sugar_accounts.id;

-- 262/268 matched
update contact_notes
join sugar_contacts on contact_notes.last_name = sugar_contacts.last_name
    and contact_notes.first_name = sugar_contacts.first_name
set contact_notes.contact_id = sugar_contacts.id;

-- 427/434
update leads_notes
join sugar_leads on leads_notes.last_name = sugar_leads.last_name
    and leads_notes.first_name = sugar_leads.first_name
set leads_notes.leads_id = sugar_leads.id;

create table notes (
    id int not null auto_increment primary key,
    date_entered datetime,
    date_modified datetime,
    name varchar(255),
    parent_type varchar(255),
    parent_id varchar(36),
    description text
);

insert into notes (date_entered,date_modified,name,parent_type,parent_id,description)
select date,NOW(),subject,'Accounts',account_id,description from account_notes where account_id is not null;

insert into notes (date_entered,date_modified,name,parent_type,parent_id,description)
select date,NOW(),subject,'Contacts',contact_id,description from contact_notes where contact_id is not null;

insert into notes (date_entered,date_modified,name,parent_type,parent_id,description)
select date,NOW(),subject,'Leads',leads_id,description from leads_notes where leads_id is not null;