SugarCRM Integration

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

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.

Installation

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 


Step-By-Step Integration

The integration process consists of the following basic steps:

  • Copy the SugarCRM data into the ]po[ database
  • Modify the ]po[ data-model in order to accommodate SugarCRM data
  • Execute PL/SQL scripts that import SugarCRM data into ]project-open[.

In the following we will assume that SugarCRM and ]project-open[ are installed on the same server:

  1. Create a full backup of the SugarCRM MySQL database
  2. Convert the SugarCRM MySQL datatabase dump into PostgreSQL format using the tool mysql2pgsql 
  3. Perform some modifications on the PostgreSQL dump
  4. Import the SugarCRM data into a different "schema" in the PostgreSQL ]project-open[ database


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. 

Modify the ]po[ Data-Model

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)           |
[...]

Adding SugarCRM Specific Categories

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[.


Helper Functions

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.

Import "Users" as ]po[ Employees

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". 

Import Accounts as ]po[ Companies

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 ();

 

Import SugarCRM Contacts as ]po[ Customer Contacts

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 ();

 



  Contact Us
  Project Open Business Solutions S.L.

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