Support » Plugin: WP-CRM - Customer Relations Management for WordPress » FULL CRM NON ADMIN GUIDE!

  • gdiangelus

    (@gdiangelus)


    Okay this is quite a long review but for anyone looking to use this as a full-fledged CRM, I have your solution. It requires either buying or “finding” a second plugin, and a little bit off MySQL, but it works. In my case I wanted to use woocomerce, WP-CRM, and wpDataTables to display all of the non-core user info that wp_Crm generates. The standard WordPress user info is stored in the wp_users table, and all custom data is in the wp_usermeta table. _Usermeta is structured as value-key pairs, so it requires a pivot to compile the data properly, but if you use this code :

    
    SELECT u.id           AS name
         , m.*
      FROM wp_XXXXXXXX_users u
      inner
      JOIN (SELECT um.user_id
                 , MAX(CASE WHEN um.meta_key='company' THEN um.meta_value END) AS company
                 , MAX(CASE WHEN um.meta_key='description' THEN um.meta_value END) AS 
                 , MAX(CASE WHEN um.meta_key='city' THEN um.meta_value END) AS city
                 , MAX(CASE WHEN um.meta_key='state' THEN um.meta_value END) AS state
                 , MAX(CASE WHEN um.meta_key='zip' THEN um.meta_value END) AS zip
                 , MAX(CASE WHEN um.meta_key='phone_number' THEN um.meta_value END) AS phone_number
                 , MAX(CASE WHEN um.meta_key='nickname'   THEN um.meta_value END) AS email
                 , MAX(CASE WHEN um.meta_key='purchaser' THEN um.meta_value END) AS purchaser
                 , MAX(CASE WHEN um.meta_key='sales_preference' THEN um.meta_value END) AS sales_preference
                 , MAX(CASE WHEN um.meta_key='delivery_day' THEN um.meta_value END) AS delivery_day
                 , MAX(CASE WHEN um.meta_key='delivery_time' THEN um.meta_value END) AS delivery_time    
                 , MAX(CASE WHEN um.meta_key='assigned_rep' THEN um.meta_value END) AS assigned_rep
                       
             FROM wp_XXXXXX_usermeta um
             GROUP
                BY um.user_id
           ) m
        ON m.user_id = u.id
    LIMIT 100000
    

    Use this code in wpDataTables to create a MySQL table, and make sure to replace any occurrence of XXXXXX with your own database name. All of the lines that read Max(case when……..) Are for my specific meta keys, yours will differ based on what value you select in wp-CRM as custom fields. Once your preview is working in wpDataTables, save the table, and put the short code on a new blank page. I use wp conditional menus to allow only admins and editors to see the link to this page in the navbar, but you can set whatever user role accces you’d like. Enjoy! And let me know if you have questions, as I’m not sure how the sql will display here

Viewing 1 replies (of 1 total)
Viewing 1 replies (of 1 total)
  • The topic ‘FULL CRM NON ADMIN GUIDE!’ is closed to new replies.