This page describes the integration of the SugarCRM CRM 6.5 system with ]project-open[. The integration mechanism copies customer and contact information from SugarCRM to ]po[. Please see the SugarCRM integration package for the updated source code of the integration scripts.
Written by Bernd Dongus and Frank Bergmann
SugarCRM is a free and open-source CRM Web application, similar in architecture to ]project-open[. SugarCRM uses the free MySQL database as a back-end and Apache Web-server for delivering pages.
SugarCRM contains some project management functionality in their enterprise version including Gantt charts, project templates and a dashboard. However, it does not compare with the depth of project related functionality in ]project-open[. Also, it does not include the specific invocing functionality that allows ]po[ to create invoices based on time and material projects.
As a result, the two systems complement each other very well, with relatively little functional overlap.
The SugarCRM integration code is available as part of the SugarCRM integration package.
Get the code from CVS:
su - projop cd ~/packages/ cvs -d :pserver:anonymous@cvs.project-open.net:/home/cvsroot checkout intranet-sugarcrm
Now go to Admin -> Package Manager -> Install Packages and please select only the package "]project-open[ SugarCRM Integration" for installation.
The package includes a BASH script for integration in ~/packages/intranet-sugarcrm/sql/postgresql/sugarcrm-import.bash that is designed to perform all the integration steps below in a single step. However, we recommend to perform the individual lines of the script one-by-one as explained in the following sections.
For questions and issues please visit the SourceForge discussion thread about the SugarCRM integration .
The integration process consists of the following basic steps:
In the following we will assume that SugarCRM and ]project-open[ are installed on the same server:
Here are the individual steps:
# 1. mysqldump -u root sugarcrm > sugarcrm.mysql.sql # 2. perl mysql2pgsql.perl sugarcrm.mysql.sql sugarcrm.pgsql.raw.sql # 3. echo "create schema sugarcrm;" > /tmp/sugarcrm.sql echo "SET search_path TO sugarcrm;" >> /tmp/sugarcrm.sql cat sugarcrm.pgsql.raw.sql >> /tmp/sugarcrm.sql
The last three lines are used to prepend two lines to the PostgreSQL dump that will cause the dump to be loaded in a new "sugarcrm" schema in the database. Schemas are a kind of name spaces that allow us for example to create two tables with the name "users" in the same database without interference.
Now you can copy the data into your PostgreSQL database:
# 4. su - projop psql -f /tmp/sugarcrm.sql
Please use pgAdminIII or a similar tool in order to check that the schema "sugarcrm" contains a number of tables with data.
This method is suitable for small and medium sized SugarCRM installations. You can extend this mechanism using ETL tools if the process should take too long.
For ease of the following integration, we are going to add some dynamic fields to the ]project-open[ data-model that we will use to store the SugarCRM object IDs of the imported companies and contacts.
These SugarCRM IDs allow us to quickly check if an object has been copied from SugarCRM, or whether it has been created in ]project-open[. They will reduce the complexity of the following integration scripts.
Please execute the following commands using the "psql" tool as user "projop":
-- Companies -- SugarCRM ids are char(36) alter table im_companies add SugarCRM_parent_id char(36); alter table im_companies add SugarCRM_id char(36); -- Offices alter table im_offices add SugarCRM_id char(36); -- Persons alter table persons add SugarCRM_id char(36);
Please check that the tables im_company, im_offices and persons contain the new fields:
projop=# \d persons
Table "public.persons"
Column | Type | Modifiers
--------------------+-------------------------+-----------
person_id | integer | not null
first_names | character varying(100) | not null
last_name | character varying(100) | not null
title | character varying(1000) |
portrait_checkdate | date |
portrait_file | character varying(400) |
demo_password | character varying(50) |
demo_group | character varying(50) |
demo_sort_order | integer |
bio | text |
sugarcrm_id | character(36) |
[...]
SugarCRM uses some types and states that do not yet exist in ]project-open[.
For example, SugarCRM know about a company type "Competitor", amongst "Customer" and others.
This type of types and states are handled using categories in ]project-open[. You will probably have to add some custom categories yourself, as the import scripts will write out error messages that it couldn't import objects because of such missing categories.
Here is the example on how to add a new company type "Competitor":
Go to your ]project-open[ server -> Admin -> Categories -> Intranet Company Type and create a new category named "Competitor".]po[ will propose you a new category_id, which is usually OK. These category_ids are used as constants in ]project-open[.
The following helper functions perform look-up operations needed further below in the actual integration scripts.
You can create these scripts by entering the code below into the "psql" command as user "projop":
-- Lookup a category in ]po[ and return the category_id.
-- ]po[ stores all object states and types in a single table
-- im_categories, instead of storing these states and types
-- in separate tables, as the 2nd database normal form would
-- suggest.
-- The procedure takes a category string (for example "Open",
-- "Closed", "Customer" or "Provider") and a category type
-- (for example "Intranet Project Status" or "Intranet Company
-- Type") and returns an integer.
--
create or replace function import_cat(varchar, varchar)
returns integer as '
DECLARE
p_category alias for $1;
p_category_type alias for $2;
v_category_id integer;
BEGIN
SELECT c.category_id INTO v_category_id FROM im_categories c
WHERE lower(c.category) = lower(p_category) AND
lower(c.category_type) = lower(p_category_type);
IF v_category_id is null AND p_category is not null THEN
RAISE WARNING ''import_cat(%,%): Did not find category'', p_category, p_category_type;
END IF;
RETURN v_category_id;
END;' language 'plpgsql';
-- select import_cat('Open','Intranet Project Status');
-- Should return 76
-- Please perform "select * from im_project_status;" for reference.
-- Find a user using his email or username.
-- The procedure expects a parameter for the user email and a
-- "purpose" string (for debugging only) and returns the user_id
-- of the user.
create or replace function import_user_email (varchar, varchar)
returns integer as '
DECLARE
p_email alias for $1;
p_purpose alias for $2;
v_user_id integer;
BEGIN
IF p_email is null THEN return null; END IF;
IF p_email = '''' THEN return null; END IF;
SELECT p.party_id INTO v_user_id
FROM parties p
WHERE lower(p.email) = lower(p_email);
IF v_user_id is null THEN
SELECT u.user_id INTO v_user_id
FROM users u
WHERE lower(u.username) = lower(p_email);
END IF;
IF v_user_id is null THEN
RAISE WARNING ''import_user_email(%) for %: Did not find user'', p_email, p_purpose;
END IF;
RETURN v_user_id;
END;' language 'plpgsql';
-- select import_user_email('sysadmin@tigerpond.com', 'Test Purposes only');
-- Should return 624
-- Please perform "select * from parties order by party_id;" for reference.
-- Find a user using his first and last name..
-- The procedure expects parameters for the first and last name
-- and a "purpose" string (for debugging only) and returns the
-- user_id of the user.
--
create or replace function import_user_name (varchar, varchar, varchar)
returns integer as '
DECLARE
p_first_names alias for $1;
p_last_name alias for $2;
p_purpose alias for $3;
v_user_id integer;
BEGIN
IF p_first_names is null OR p_last_name is null THEN return null; END IF;
IF p_first_names = '''' OR p_last_name = '''' THEN return null; END IF;
SELECT p.person_id INTO v_user_id
FROM persons p
WHERE lower(p.first_names) = lower(p_first_names) and
lower(p.last_name) = lower(p_last_name);
IF v_user_id is null THEN
RAISE WARNING ''import_user_name(%) for % %: Did not find user'',
p_first_names, p_last_name, p_purpose;
END IF;
RETURN v_user_id;
END;' language 'plpgsql';
-- select import_user_name('System', 'Administrator', 'Test Purposes only');
-- Should return 624
-- Please perform "select * from parties order by party_id;" for reference.
The following PL/SQL procedure loops through the sugarcrm.users table and creates equivalent users of type "employee" in ]po[.
-- Actually create new users by going through the SugarCRM.users table
-- line by line and inserting or updating the user into the ]po[ DB.
--
create or replace function SugarCRM_import_users ()
returns integer as $BODY$
DECLARE
row RECORD;
v_user_id integer;
v_string varchar;
v_exists_p integer;
v_username varchar;
v_country_code varchar;
v_group_id integer;
BEGIN
FOR row IN
select *
from SugarCRM.users
left join SugarCRM.email_addr_bean_rel br
ON (br.bean_module = 'Users' and br.primary_address = 1 and br.bean_id = users.id)
left join SugarCRM.email_addresses e ON (e.id = br.email_address_id)
LOOP
RAISE NOTICE 'Processing User: first=%, last=%, email=%, no=%',
row.first_name, row.last_name, row.email_address, trim(row.id);
IF row.first_name is null OR row.first_name = '' THEN
RAISE NOTICE 'Skipping User (empty first_names): % % (%)',
row.first_name, row.last_name, row.email_address;
continue;
END IF;
IF row.last_name is null OR row.last_name = '' THEN
RAISE NOTICE 'Skipping User (empty last_name): % % (%)',
row.first_name, row.last_name, row.email_address;
continue;
END IF;
IF row.email_address is null OR row.email_address = '' THEN
RAISE NOTICE 'Skipping User (empty email): % % (%)',
row.first_name, row.last_name, row.email_address;
continue;
END IF;
-- Check if the user already exists / has been imported already
-- during the last run of this script. PrimKey is first_names+last_name.
select person_id into v_user_id from persons
where SugarCRM_id = row.id;
IF v_user_id is null THEN
select party_id into v_user_id from parties p
where trim(lower(p.email)) = trim(lower(row.email_address));
END IF;
IF v_user_id is null THEN
select person_id into v_user_id from persons p
where (trim(lower(p.first_names)) = trim(lower(row.first_name)) and
trim(lower(p.last_name)) = trim(lower(row.last_name)));
END IF;
-- Create a new user if the user wasnt there
IF v_user_id is null THEN
RAISE NOTICE 'Insert User: % % (%)', row.first_name, row.last_name, row.email_address;
-- Create a default username
v_username := row.user_name;
v_user_id := acs__add_user(
null, 'user', now(), 0, '0.0.0.0',
null, v_username, row.email_address, null,
row.first_name, row.last_name,
'hashed_password', 'salt',
v_username, 't', 'approved'
);
INSERT INTO users_contact (user_id) VALUES (v_user_id);
INSERT INTO im_employees (employee_id) VALUES (v_user_id);
-- INSERT INTO im_freelancers (user_id) VALUES (v_user_id);
ELSE
SELECT username into v_username from users
where user_id = v_user_id;
END IF;
-----------------------------------------------------------------
-- This is the main part of the import process, this part is
-- executed for every line in the import_users table every time
-- this script is executed.
-- Update the users information, no matter whether its a new or
-- an already existing user.
RAISE NOTICE 'Update User: email=%, id=%', row.email_address, v_user_id;
-- Fix Country Codes
v_country_code := lower(row.address_country);
IF v_country_code = 'schweiz' THEN v_country_code := 'ch'; END IF;
IF v_country_code = 'deutschland' THEN v_country_code := 'de'; END IF;
IF v_country_code = 'liechtenstein' THEN v_country_code := 'li'; END IF;
IF v_country_code = '' THEN v_country_code := NULL; END IF;
-- The user information is spread across multiple tables unfortunately:
-- persons: Physical persons (first_name, last_name)
-- parties: Everybody with an email (email)
-- users: Users who can log-in to ]po[ (username, password, salt)
-- users_contact: Contact information (home_phone, ...)
update users_contact set
home_phone =row.phone_home,
work_phone =row.phone_work,
cell_phone =row.phone_mobile,
pager =row.phone_other,
fax = row.phone_fax,
aim_screen_name = CASE row.messenger_type WHEN 'AOL' THEN row.messenger_id ELSE '' END,
msn_screen_name = CASE row.messenger_type WHEN 'MSN' THEN row.messenger_id ELSE '' END,
-- icq_number character varying(50),
-- m_address character(1),
-- ha_line1 character varying(80),
-- ha_line2 character varying(80),
-- ha_city character varying(80),
-- ha_state character varying(80),
-- ha_postal_code character varying(80),
-- ha_country_code character(2),
wa_line1 = row.address_street,
-- wa_line2 character varying(80),
wa_city = row.address_city,
wa_postal_code = row.address_postalcode,
wa_state = row.address_state,
wa_country_code = v_country_code,
note = row.description
-- current_information character varying(4000),
where user_id = v_user_id;
update users set
username = v_username
where user_id = v_user_id;
update persons set
first_names = row.first_name,
last_name = row.last_name
where person_id = v_user_id;
update parties set
email = row.email_address
-- url = row.web_site
where party_id = v_user_id;
update im_employees set
-- IDEA: convert and import row.reports_to_id into supervisor_id
job_title = row.title
where employee_id = v_user_id;
-- Add the new user to the group Employees
select group_id into v_group_id from groups
where group_name = 'Employees';
-- Check if the user is already a member of that group
select count(*) into v_exists_p
from acs_rels r
where rel_type = 'membership_rel' and
object_id_one = v_group_id and
object_id_two = v_user_id;
-- Add the user to the group if he is not a member already.
IF v_exists_p = 0 THEN
RAISE NOTICE 'Adding User to Group: %', v_group_id;
PERFORM membership_rel__new(
null, -- rel_id, default null
'membership_rel', -- object_type, default membership_rel
v_group_id, -- object_id_one
v_user_id, -- object_id_two
'approved', -- new__member_state, default approved
null, -- creation_user, default null
'0.0.0.0' -- creation_ip, default null
);
END IF;
END LOOP;
RETURN 0;
END;$BODY$ language 'plpgsql';
select SugarCRM_import_users ();
-- Should return something like:
-- NOTICE: Processing User: first=<NULL>, last=Administrator, email=<NULL>, no=1
-- NOTICE: Skipping User (empty first_names): <NULL> Administrator (<NULL>)
-- NOTICE: Processing User: first=Chris, last=Olliver, email=chris@example.com, no=seed_chris_id
-- NOTICE: Insert User: Chris Olliver (chris@example.com)
-- NOTICE: Update User: email=chris@example.com, id=36566
-- NOTICE: Adding User to Group: 461
-- [...]
Now go to your ]project-open[ server -> Users -> Customer and search for "Chris Olliver".
The following procedure loops through the SugarCRM.accounts table and converts entries in ]po[ Companies, together with their [Offices], which are the company's venues:
create or replace function SugarCRM_import_accounts ()
returns integer as $BODY$
DECLARE
row RECORD;
v_company_id integer;
v_office_id integer;
v_exists_p integer;
v_company_name varchar;
v_office_name varchar;
v_office_status_id integer;
v_office_type_id integer;
v_company_status_id integer;
v_company_type_id integer;
v_duplicate_p integer;
v_primary_contact_id integer;
v_note varchar;
v_parent_company_id integer;
v_country_code varchar;
v_counter integer;
BEGIN
v_counter := 0;
FOR row IN
SELECT email_address,
accounts.*
FROM SugarCRM.accounts
LEFT JOIN SugarCRM.email_addr_bean_rel br
ON (br.bean_module = 'Accounts' and br.primary_address = 1 and br.bean_id = accounts.id)
LEFT JOIN SugarCRM.email_addresses e ON (e.id = br.email_address_id)
WHERE account_type = 'Customer'
ORDER BY SugarCRM.accounts.id
LOOP
v_counter := v_counter + 1;
------------------------ Name Fiddling ---------------------------------------------
-- Process the name in order to deal with duplicates.
-- ]po[ does not allow for duplicate company or office names.
-- By default just use the standard name
v_company_name := row.name;
-- The office name is the company name + " Main Office".
v_office_name := v_company_name;
IF row.parent_id IS NULL THEN
v_office_name := v_office_name || ' Main Office';
END IF;
------------------------ Default Values ---------------------------------------------
RAISE NOTICE 'Processing Customer %: %', v_counter, v_company_name;
v_office_status_id := import_cat('Active', 'Intranet Office Status');
v_office_type_id := import_cat('Main Office', 'Intranet Office Type');
v_company_status_id := import_cat('Active', 'Intranet Company Status');
v_company_type_id := import_cat('Customer', 'Intranet Company Type');
------------------------ Translated Values ---------------------------------------------
IF row.account_type <> 'Customer' THEN
CASE row.account_type
WHEN 'Prospect' THEN v_company_type_id := import_cat('Potential', 'Intranet Company Status');
WHEN 'Competitor' THEN v_company_type_id := import_cat('Competitor', 'Intranet Company Type');
WHEN 'Partner' THEN v_company_type_id := import_cat('Provider', 'Intranet Company Type');
WHEN 'Investor' THEN v_company_type_id := import_cat('Other', 'Intranet Company Type');
ELSE v_company_type_id := import_cat('Unknown', 'Intranet Company Type');
END CASE;
END IF;
-- Set customer industry if category exists
IF v_company_type_id = import_cat('Customer', 'Intranet Company Type')
AND import_cat(row.industry, 'Intranet Company Type') IS NOT NULL
THEN
v_company_type_id := import_cat(row.industry, 'Intranet Company Type');
END IF;
------------------------ New Office and Company ------------------------------------
-- Check if the office is already there
select office_id into v_office_id from im_offices o
where o.SugarCRM_id = row.id;
-- Create a new office first, because the im_company.main_office_id is not null
IF v_office_id is null THEN
RAISE NOTICE 'Insert Office: %', v_office_name;
v_office_id := im_office__new (
NULL, 'im_office', now()::date, 0, '0.0.0.0', null,
v_office_name, REPLACE(REGEXP_REPLACE(lower(v_office_name), '[^a-z0-9]', ''), ' ', ''),
v_office_type_id, v_office_status_id, null
);
END IF;
-- Check if the company is already there
select company_id into v_company_id from im_companies c
where c.SugarCRM_id = row.id;
IF v_company_id is null THEN
RAISE NOTICE 'Insert Company: %', v_company_name;
v_company_id := im_company__new (
NULL, 'im_company', now()::date, 0, '0.0.0.0', null,
v_company_name, row.id,
v_office_id, v_company_type_id, v_company_status_id
);
END IF;
------------------------ Update Office and Company----------------------------------
-- Add other fields to the "note" field
v_note := NULL;
IF row.rating is not null AND row.rating <> '' THEN
v_note := v_note || 'Rating=' || row.rating ||', ';
END IF;
IF row.description is not null AND row.description <> '' THEN
v_note := v_note || 'Description=' || row.description ||', ';
END IF;
-- Primary contact
select person_id into v_primary_contact_id from persons
where SugarCRM_id = row.assigned_user_id;
IF v_primary_contact_id is null THEN
IF row.email_address is not null AND row.email_address <> '' THEN
v_primary_contact_id := import_user_email(row.email_address,
'sugarCRM_import_customer.im_company.primary_contact_id');
END IF;
END IF;
-- Parent Company
select company_id into v_parent_company_id from im_companies
where SugarCRM_id = row.parent_id;
-- Determine Country Code based on American country name
select min(iso) into v_country_code from country_codes
where iso = lower(row.billing_address_country)
or lower(country_name) = lower(row.billing_address_country);
-- handle some non English country names.
IF lower(row.billing_address_country) = 'schweiz' THEN v_country_code := 'ch'; END IF;
IF lower(row.billing_address_country) = 'deutschland' THEN v_country_code := 'de'; END IF;
IF lower(row.billing_address_country) = 'liechtenstein' THEN v_country_code := 'li'; END IF;
IF v_country_code = '' THEN v_country_code := NULL; END IF;
RAISE NOTICE 'Update Office: %', v_office_name;
update im_offices set
office_name = v_office_name,
office_status_id = v_office_status_id,
office_type_id = v_office_type_id,
phone = row.phone_office,
fax = row.phone_fax,
address_line1 = row.billing_address_street,
address_line2 = row.shipping_address_street,
address_city = row.billing_address_city,
address_country_code = v_country_code,
address_postal_code = row.billing_address_postalcode,
address_state = row.billing_address_state,
SugarCRM_id = row.id
where office_id = v_office_id;
RAISE NOTICE 'Update Company: %', v_company_name;
update im_companies set
company_name = v_company_name,
main_office_id = v_office_id,
company_status_id = v_company_status_id,
company_type_id = v_company_type_id,
site_concept = row.website,
-- vat_number = row.VAT Registration No_,
-- default_payment_days = v_payment_days,
primary_contact_id = v_primary_contact_id,
accounting_contact_id = v_primary_contact_id,
note = v_note,
SugarCRM_parent_id = v_parent_company_id,
SugarCRM_id = row.id
where company_id = v_company_id;
END LOOP;
RETURN 0;
END;$BODY$ language 'plpgsql';
select SugarCRM_import_accounts ();
Loop through all SugarCRM contacts and insert them into ]po[.
-- Create new users by going through the SugarCRM.contacts table
-- line by line and inserting the user into the DB.
--
-- Create new users by going through the SugarCRM.contacts table
-- line by line and inserting the user into the DB.
--
create or replace function SugarCRM_import_contacts ()
returns integer as $BODY$
DECLARE
row RECORD;
v_user_id integer;
v_string varchar;
v_exists_p integer;
v_username varchar;
v_email_address varchar;
v_first_name varchar;
v_country_code varchar;
v_group_id integer;
v_customer_group_id integer;
v_company_id integer;
BEGIN
FOR row IN
select a.name as account_name, email_address, *
from SugarCRM.contacts
left join SugarCRM.email_addr_bean_rel br
ON (br.bean_module = 'Contacts' and br.primary_address = 1 and br.bean_id = contacts.id)
left join SugarCRM.email_addresses e ON (e.id = br.email_address_id)
left join SugarCRM.accounts_contacts ac
ON (ac.deleted = 0 and ac.contact_id = contacts.id)
left join SugarCRM.accounts a ON (a.id = ac.account_id)
where account_type = 'Customer'
LOOP
v_email_address = row.email_address;
v_first_name = row.first_name;
RAISE NOTICE 'Processing Contact: first=%, last=%, email=%, no=%, company_no=%',
v_first_name, row.last_name, v_email_address, row.id, row.account_name;
IF COALESCE(v_first_name, '') = '' THEN
IF COALESCE(row.salutation, '') ='' THEN
RAISE NOTICE 'Skipping User (empty first_names): % % (%)',
v_first_name, row.last_name, v_email_address;
continue;
END IF;
v_first_name = row.salutation;
END IF;
IF COALESCE(row.last_name, '') = '' THEN
RAISE NOTICE 'Skipping User (empty last_name): % % (%)',
v_first_name, row.last_name, v_email_address;
continue;
END IF;
IF COALESCE(v_email_address, '') = '' THEN
v_email_address = REPLACE(v_first_name, ' ', '_') || '.' ||
REPLACE(row.last_name, ' ', '_') || '@example.com';
RAISE NOTICE 'Generated email address :', v_email_address;
END IF;
v_email_address := trim(lower(v_email_address));
-- Check if the user already exists / has been imported already
-- during the last run of this script. PrimKey is first_names+last_name.
-- Check 1. SugarCRM_id, 2. Email and 3. Name
select person_id into v_user_id from persons
where SugarCRM_id = row.id;
IF v_user_id is null THEN
select min(party_id) into v_user_id from parties p
where trim(lower(p.email)) = v_email_address;
END IF;
IF v_user_id is null THEN
select min(person_id) into v_user_id from persons p
where (trim(lower(p.first_names)) = v_email_address and
trim(lower(p.last_name)) = trim(lower(row.last_name)));
END IF;
-- Create a new user if the user wasnt there
IF v_user_id is null THEN
RAISE NOTICE 'Insert User: % % (%)', v_first_name, row.last_name, v_email_address;
-- Create a default username
v_username := v_email_address;
v_user_id := acs__add_user(
null, 'user', now(), 0, '0.0.0.0',
null, v_username, v_email_address, null,
v_first_name, row.last_name,
'hashed_password', 'salt',
v_username, 't', 'approved'
);
INSERT INTO users_contact (user_id) VALUES (v_user_id);
INSERT INTO im_employees (employee_id) VALUES (v_user_id);
-- INSERT INTO im_freelancers (user_id) VALUES (v_user_id);
ELSE
SELECT username into v_username from users where user_id = v_user_id;
END IF;
-----------------------------------------------------------------
-- This is the main part of the import process, this part is
-- executed for every line in the import_users table every time
-- this script is executed.
-- Update the users information, no matter whether its a new or
-- an already existing user.
RAISE NOTICE 'Update User: email=%, id=%', v_email_address, v_user_id;
-- Determine Country Code based on American country name
select min(iso) into v_country_code from country_codes
where iso = lower(row.billing_address_country)
OR lower(country_name) = lower(row.billing_address_country);
-- handle some non English country names.
IF lower(row.billing_address_country) = 'schweiz' THEN v_country_code := 'ch'; END IF;
IF lower(row.billing_address_country) = 'deutschland' THEN v_country_code := 'de'; END IF;
IF lower(row.billing_address_country) = 'liechtenstein' THEN v_country_code := 'li'; END IF;
IF v_country_code = '' THEN v_country_code := NULL; END IF;
update users_contact set
home_phone =row.phone_home,
work_phone =row.phone_work,
cell_phone =row.phone_mobile,
pager =row.phone_other,
fax = row.phone_fax,
wa_line1 = row.primary_address_street,
wa_line2 = row.alt_address_street,
wa_city = row.primary_address_city,
wa_postal_code = row.primary_address_postalcode,
wa_state = row.primary_address_state,
wa_country_code = v_country_code,
note = row.lead_source,
current_information = row.description || '/nAbteilung: ' || row.department
where user_id = v_user_id;
update users set
username = v_username
where user_id = v_user_id;
update persons set
first_names = v_first_name,
last_name = row.last_name
where person_id = v_user_id;
-- update parties set
-- url = row.home-page
-- where party_id = v_user_id;
update im_employees set
-- IDEA: convert and import row.reports_to_id into supervisor_id
job_title = row.title
where employee_id = v_user_id;
----------------------------------------------------------------
-- Make the new user a member of group Customers or Providers
-- select out the ID of the Customers group
IF row.account_type='Customer' OR row.account_type='Prospect' THEN
RAISE NOTICE 'User is a Customer: %', v_email_address;
select group_id into v_customer_group_id from groups
where group_name = 'Customers';
ELSE
RAISE NOTICE 'User is a Provider: %', v_email_address;
select group_id into v_customer_group_id from groups
where group_name = 'Freelancers';
END IF;
-- Check if the user is already a member of that group
select count(*)
into v_exists_p
from acs_rels r
where rel_type = 'membership_rel' and
object_id_one = v_customer_group_id and
object_id_two = v_user_id;
-- Add the user to the group if he is not a member already.
IF false AND v_exists_p = 0 THEN
RAISE NOTICE 'Adding User to Group: %', v_email_address;
PERFORM membership_rel__new(
null, -- rel_id, default null
'membership_rel', -- object_type, default membership_rel
v_customer_group_id, -- object_id_one
v_user_id, -- object_id_two
'approved', -- new__member_state, default approved
null, -- creation_user, default null
'0.0.0.0' -- creation_ip, default null
);
END IF;
--------------------------------------------------------------------
-- Lookup company
select company_id into v_company_id from im_companies
where SugarCRM_id = row.account_id;
IF v_company_id is null THEN
RAISE WARNING 'Did not find company=%', row.account_id;
continue;
END IF;
--------------------------------------------------------------------
-- Create the relationship between company and contact
RAISE NOTICE 'Making % member of %', im_name_from_user_id(v_user_id), v_company_id;
PERFORM im_biz_object_member__new (
null,
'im_biz_object_member',
v_company_id, -- the business object
v_user_id, -- the person related to biz_object
1300, -- role: Full Member
null, -- percentage
null, -- creation user
'0.0.0.0' -- creation IP
);
END LOOP;
RETURN 0;
END;$BODY$ language 'plpgsql';
select SugarCRM_import_contacts ();
Calle Aprestadora 19, 12o-2a
08902 Hospitalet de Llobregat (Barcelona)
Spain
Tel Europe: +34 609 953 751
Tel US: +1 415 200 2465
Mail: info@project-open.com