You are here

SuiteCRM / SugarCRM Cheat Sheet

Contents

  • 1. Find id of record to unlink.
    SQL> SET @uid = 'f232e8d0-9b82-20f7-7c35-5c1a5ce94e0a';
    
  • 2. Find entry in Target List table:
    SQL> SELECT plp.id, plp.related_type, plp.date_modified, plp.deleted
         , pl.name
      FROM prospect_lists_prospects AS plp
      JOIN prospect_lists AS pl ON plp.prospect_list_id = pl.id
     WHERE plp.related_id = @uid
    ;
    
  • 3. Update entry in Target List table:
    SQL> START TRANSACTION;
    SQL> UPDATE prospect_lists_prospects
       SET deleted = 1
     WHERE related_id = @uid
       AND id = '5b3047b2-3b45-1a6e-e6fb-5d3fe1efa93b'
    ;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    SQL> COMMIT;
    

Find Target Lists of Target/Prospect

Result:

+--------------------------------------+--------------+------------+-----------+------------------------+
| id                                   | first_name   | last_name  | type      | target_lists           |
+--------------------------------------+--------------+------------+-----------+------------------------+
| e9678a2a-ee96-dbc6-6fe0-5d0e7be4ac45 | Thomas       | Benderskiy | Prospects | Target EN              |
| e97b4caf-317a-9d97-58b0-5d0e38b81959 | Marko        | Marklund   | Prospects | Target DACH            |
| e98c07e8-64e7-8731-8ad9-5d0e7a6e8844 | Avik         | Beyer      |           |                        |
| e98106c2-f0cf-f453-162b-5d0e80422eb9 | Christian    | Ribas      | Prospects | Target DACH, Target EN |
+--------------------------------------+--------------+------------+-----------+------------------------+

Accounts:

SELECT a.id, a.name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM accounts AS a
  JOIN accounts_cstm AS ac ON a.id = ac.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = a.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE ac.interests_c LIKE '%^brman^%'
   AND a.deleted = 0
 GROUP BY a.id, a.name, plp.related_type
;

Contacts:

SELECT c.id, c.first_name, c.last_name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM contacts AS c
  JOIN contacts_cstm AS cc ON c.id = cc.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = c.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE cc.interests_c LIKE '%^brman^%'
   AND c.deleted = 0
 GROUP BY c.id, c.first_name, c.last_name, plp.related_type
;

Leads:

SELECT l.id, l.first_name, l.last_name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM leads AS l
  JOIN leads_cstm AS lc ON l.id = lc.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = l.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE lc.interests_c LIKE '%^brman^%'
   AND l.deleted = 0
 GROUP BY l.id, l.first_name, l.last_name, plp.related_type
;

Targets / Prospects:

SELECT p.id, p.first_name, p.last_name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM prospects AS p
  JOIN prospects_cstm AS pc ON p.id = pc.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = p.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE p.deleted = 0
 GROUP BY p.id, p.first_name, p.last_name, plp.related_type
;

List all email addresses of all accounts

SELECT ea.email_address, eabr.bean_module
  FROM email_addr_bean_rel AS eabr
  JOIN email_addresses AS ea ON ea.id = eabr.email_address_id
 WHERE eabr.bean_module = 'Accounts'
  AND eabr.deleted = 0
;
+------------------------+-------------+
| email_address          | bean_module |
+------------------------+-------------+
| info@top.org           | Accounts    |
| first.last@company.com | Accounts    |
+------------------------+-------------+

Objects without Target List

Contacts without Target List:

SELECT c.first_name, c.last_name
  FROM contacts AS c
  LEFT JOIN prospect_lists_prospects AS plp ON c.id = plp.related_id AND plp.deleted = 0
 WHERE c.deleted = 0
   AND plp.id IS NULL
;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Hans       | Maier     |
| Fritz      | Müller    |
+------------+-----------+

Add Contacts without Target List to a Target List:

SQL> SELECT id, name FROM prospect_lists WHERE deleted = 0 ORDER BY name;
+--------------------------------------+----------------------------------------+---------+
| id                                   | name                                   | deleted |
+--------------------------------------+----------------------------------------+---------+
| 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | Target DACH                            |       0 |
| 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | Target EN                              |       0 |
+--------------------------------------+----------------------------------------+---------+

SQL> SELECT id INTO @plid FROM prospect_lists WHERE deleted = 0 AND name = 'Target DACH';
SQL> START TRANSACTION;
SQL> INSERT INTO prospect_lists_prospects
(id, prospect_list_id, related_id, related_type, date_modified, deleted)
SELECT uuid(), @plid, c.id, 'Contacts', CURRENT_TIMESTAMP(), 0
  FROM contacts AS c
  LEFT JOIN prospect_lists_prospects AS plp ON c.id = plp.related_id AND plp.deleted = 0
 WHERE c.deleted = 0
   AND plp.id IS NULL
;
SQL> COMMIT;

Leads without Target List:

SELECT l.first_name, l.last_name
  FROM leads AS l
  LEFT JOIN prospect_lists_prospects AS plp ON l.id = plp.related_id AND plp.deleted = 0
 WHERE l.deleted = 0
   AND plp.id IS NULL
;

Add Leads without Target List to a Target List:

SQL> SELECT id, name FROM prospect_lists WHERE deleted = 0 ORDER BY name;
+--------------------------------------+----------------------------------------+---------+
| id                                   | name                                   | deleted |
+--------------------------------------+----------------------------------------+---------+
| 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | Target DACH                            |       0 |
| 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | Target EN                              |       0 |
+--------------------------------------+----------------------------------------+---------+

SQL> SET @plid = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6';
SQL> START TRANSACTION;
SQL> INSERT INTO prospect_lists_prospects
(id, prospect_list_id, related_id, related_type, date_modified, deleted)
SELECT uuid(), @plid, l.id, 'Leads', CURRENT_TIMESTAMP(), 0
  FROM leads AS l
  LEFT JOIN prospect_lists_prospects AS plp ON l.id = plp.related_id AND plp.deleted = 0
 WHERE l.deleted = 0
   AND plp.id IS NULL
;
SQL> COMMIT;

Prospects without Target List:

SELECT p.first_name, p.last_name, p.primary_address_country
  FROM prospects AS p
  LEFT JOIN prospect_lists_prospects AS plp ON p.id = plp.related_id AND plp.deleted = 0
 WHERE p.deleted = 0
   AND plp.id IS NULL
;

Add Prospects without Target List to a Target List:

SQL> SET @plid = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6';
SQL> START TRANSACTION;
SQL> INSERT INTO prospect_lists_prospects
(id, prospect_list_id, related_id, related_type, date_modified, deleted)
SELECT uuid(), @plid, p.id, 'Prospects', CURRENT_TIMESTAMP(), 0
  FROM prospects AS p
  LEFT JOIN prospect_lists_prospects AS plp ON p.id = plp.related_id AND plp.deleted = 0
 WHERE p.deleted = 0
   AND plp.id IS NULL
;
SQL> COMMIT;

Find PLP without Objects (Contacts/Leads/Prospects)

SELECT plp.related_id, plp.related_type
  FROM prospect_lists_prospects AS plp
  LEFT JOIN prospects AS p on p.id = plp.related_id
 WHERE plp.deleted = 0
   AND plp.related_type = 'Prospects'
   AND p.id IS NULL
;

SELECT plp.related_id, plp.related_type
  FROM prospect_lists_prospects AS plp
  LEFT JOIN leads AS l on l.id = plp.related_id
 WHERE plp.deleted = 0
   AND plp.related_type = 'Leads'
   AND l.id IS NULL
;

SELECT plp.related_id, plp.related_type
  FROM prospect_lists_prospects AS plp
  LEFT JOIN contacts AS c on c.id = plp.related_id
 WHERE plp.deleted = 0
   AND plp.related_type = 'Contacts'
   AND c.id IS NULL
;

Find Prospect List Entries per Prospect Type

SELECT related_type, COUNT(*)
  FROM (
SELECT DISTINCT plp.related_type, plp.related_id
  FROM prospect_lists_prospects AS plp
 WHERE plp.deleted = 0
) AS d GROUP BY related_type
;
+--------------+----------+
| related_type | COUNT(*) |
+--------------+----------+
| Contacts     |     1959 |
| Leads        |     5891 |
| Prospects    |     8970 |
+--------------+----------+

Quick set-up on Ubuntu 18.04

# apt-get update
# apt-get install mariadb-server php apache2 unzip

Check: http://192.168.56.101

# cd /tmp
# wget https://suitecrm.com/files/160/SuiteCRM-7.10/480/SuiteCRM-7.10.22.zip

# grep -r DocumentRoot /etc/apache2/
/etc/apache2/sites-available/000-default.conf:  DocumentRoot /var/www/html

# cd /var/www/html
# unzip /tmp/SuiteCRM-7.10.22.zip
# ln -s SuiteCRM-7.10.22 SuiteCRM
# cd SuiteCRM
# chown -R www-data:www-data .
# chmod -R 755 .
# chmod -R 775 cache custom modules themes data upload
# chmod 775 config_override.php 2>/dev/null

# apt-get install php-mysqli php-xml php-zip php-curl php-imap php-gd

# There is still a bug in SuiteCRM so 6M is not enough!
# cat >/etc/php/7.2/mods-available/SuiteCRM.ini <<_EOF
[PHP]
upload_max_filesize = 7M
_EOF

# phpenmod -v 7.2 -s apache2 SuiteCRM
# systemctl restart apache2

SQL> CREATE DATABASE suitecrm;
SQL> CREATE USER 'suitecrm'@'localhost' IDENTIFIED BY '...';
SQL> GRANT ALL ON *.* TO 'suitecrm'@'localhost';

Installation: http://192.168.56.101/SuiteCRM/install.php

Add targets from old target lists to new target list

1. Find target lists.

SELECT id, name FROM prospect_lists
 WHERE deleted = 0 AND list_type = 'default'
   AND name IN ('Target DACH', 'Target D', 'Target CH', 'Target A')
;
+--------------------------------------+-------------+
| id                                   | name        |
+--------------------------------------+-------------+
| 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | Target DACH |
| 8eb0ec25-6bbb-68de-d44f-5d0e262cd93d | Target D    |
| d23490c8-99eb-f298-6aad-5d0e28e7fd4f | Target A    |
| d81e9aae-ef60-fca2-7d99-5d0e269de1c0 | Target CH   |
+--------------------------------------+-------------+

2. Find id of new target list:

SELECT id INTO @target_list_id FROM prospect_lists
 WHERE name = 'NewTL'
;
SELECT @target_list_id;
+--------------------------------------+
| @target_list_id                      |
+--------------------------------------+
| 627129c3-7432-9fc4-efb4-5e58e8e04e06 |
+--------------------------------------+

3. Insert targets from old target lists into new target list:

INSERT INTO prospect_lists_prospects
SELECT UUID(), @target_list_id, related_id, related_type, CURRENT_TIMESTAMP(), 0
  FROM prospect_lists_prospects
 WHERE deleted = 0
   AND prospect_list_id IN
       (   SELECT id FROM prospect_lists
            WHERE deleted = 0 AND list_type = 'default'
              AND name IN ('Target DACH', 'Target D', 'Target CH', 'Target A')
       )
;