Exporting campaign account CSVs from VTiger using SQL

Although VTiger works great as CRM, its campaign management feature is missing one of most important features: It doesn’t support exporting

This post aims to provide technically oriented users a guide on how to use SQL queries to perform that task. We assume basic knowledge of SQL syntax, MySQL and PHPMyAdmin.

The goal for our example is to export a table of Account name and E-Mail. For the email, the primary email will be used, unless it’s empty in which case the secondary email is used.

The input for the query is the campaign name (in our example, this will be MyCampaign).

Querying the campaign number

The campaign number is a string, e.g. CAM4 or CAM9 which is stored in vtiger_campaign.

SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign"

 Finding the campaign record ID

In the campaign account relations n-to-n relations table, the campaign number is not used directly – instead, the campaign record ID is used. We need to query that from vtiger_modtracker_detail, which contain metadata key-value-data about the campaigns (among other things). One of the K-V pairs contain the campaign identifier (key campaign_no) whereas another one contains the record ID (key record_id).

SELECT postvalue FROM `vtiger_modtracker_detail` WHERE `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = <<campaign number>> and fieldname = "campaign_no") AND fieldname = "record_id"

Full query:

SELECT postvalue FROM `vtiger_modtracker_detail` WHERE `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no") AND fieldname = "record_id"

The result of this query is the numeric campaign record ID, e.g. 315.

Querying campaign-to-account relations

We can now use the campaign record ID to query the n-to-n table vtiger_campaignaccountrel. There are other, similar tables for leads (vtiger_campaignleadrel) and contacts (vtiger_campaigncontrel).

SELECT accountid FROM vtiger_campaignaccountrel WHERE `campaignid` = <<campaign record ID>>

Full query:

SELECT accountid FROM vtiger_campaignaccountrel WHERE `campaignid` = (SELECT postvalue FROM `vtiger_modtracker_detail` WHERE fieldname = "record_id" AND `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no"))

The result of this query is a list of account IDs related to the campaign. The order is usually the same as displayed in VTiger.

Querying account information

Using the list of account IDs, we can JOIN on vtiger_account to query the account details. Note that you need to use the appropriate table for leads or contacts if you used an alternative relations table in the previous step.

SELECT acc.accountname AS "Name", COALESCE(acc.email1, acc.email2) AS "EMail" FROM vtiger_campaignaccountrel AS car JOIN vtiger_account acc ON (acc.accountid = car.accountid) WHERE `campaignid` =(SELECT postvalue FROM `vtiger_modtracker_detail` WHERE fieldname = "record_id" AND `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no")) AND (email1 <> '' OR email2 <> '')

Note that besides the SELECT column list there are some aspects which are specific to the Name and Email query:

  1. COALESCE(acc.email1, acc.email2) uses email2 if email1 is empty
  2. AND (email1 <> '' OR email2 <> '') ignores accounts that have neither email1 nor email2 set

If you want a custom list of columns, you need to modify that appropriately.

Exporting CSV

When entering the query into a tool like PHPMyAdmin, you can click the Export button in order to export the result to different formats, including CSV and Excel.

While this might not be the most convenient option, it’s a very flexible way of generating and exporting the data. Any more convenient ways are likely to be less flexible.

Fixing PPA Unable to identify ‚package‘: user@mycomputer in launchpad

Problem:

You’ve uploaded a DEB package to a Launchpad PPA (e.g. using dput), but you get an error message similar to this:

Solution:

You need to use a proper email address (which must be registered in Launchpad) in debian/changelog .

In order to do this, set the $DEBEMAIL environment variable before running dch

Example:

export DEBEMAIL=myemail@example.com
dch [...]

If $DEBEMAIL is not set, [username]@[hostname] will be used

Graph layouting via Graphviz

Problem:

You want to display a Graph.

Solution:

Create a simple text file describing your graph and save it with a .dot file extension:

graph {
node1 -- node2;
node2 -- node3;
node3 -- node4;
node4 -- node1;
}

Afterwards you can use a program of the graphviz package (sudo apt-get install graphviz) in order to visualize the graph. This package contains different layouting programs like dot, neato, fdp (all from the GraphViz project) etc. Simply call one of these programs in order to visualize the graph:

neato -Tsvg yourFile.dot -o outputFile.svg

Output from this command:

Find WordPress category ID by category name using SQL

Problem:

You need the wordpress category ID for a given category name (e.g. My category), e.g. for a shortcode filter.

Solution:

You can use a SQL statement to find the correct category for your term (i.e. the category name, My category). Run this statement in your database administration interface, e.g. phpmyadmin.

SELECT termtax.term_taxonomy_id FROM wp_terms AS terms JOIN wp_term_taxonomy AS termtax ON terms.term_id = termtax.term_id WHERE terms.name = "My category"

Replace My category at the end by the category name you’re looking for.

Get the original language post ID for WPML-translated posts

Problem:

You have a post that has been translated into multiple languages using WPML.
You want to find out the ID of the original post, i.e. the equivalent post in the source langugae.

Solution:

As far as I know, there is no WPML API for this. You can, however, use $wpdb in order to query the database directly.

Here’s the SQL statement:

SELECT trans2.element_id
FROM wp_icl_translations AS trans1
INNER JOIN wp_icl_translations AS trans2
ON trans2.trid = trans1.trid
WHERE trans1.element_id = #myid#
AND trans2.source_language_code IS NULLs

where #myid# is the ID of your current (possibly translated) post.

The algorithm works like this:

  1. Find out the  translation ID (column trid) for the current post by selecting the row for the given post ID (column element_id)
  2. Find all rows with the same translation ID
  3. Select only the row where the source language is NULL – this is the original post.

You can use it like this:

global $wpdb;
$orig_lang_id = $wpdb->get_var("SELECT trans2.element_id FROM wp_icl_translations AS trans1 INNER JOIN wp_icl_translations AS trans2 ON trans2.trid = trans1.trid WHERE trans1.element_id = ".get_the_ID()." AND trans2.source_language_code IS NULL");

and then use $orig_lang_id where required. Note that $orig_lang_id is NULL if the query fails. The main reason for the query to fail is if there is no entry for the given post ID in the wp_icl_translations table.

If the current post is the source post, $orig_lang_id is the same as get_the_ID(), i.e. the current ID of the post.

In my test, the code still works (i.e. returns the correct post ID) even when creating brand-new entry without any translations being present.

linkchecker: Ignore WordPress XMLRPC

Problem:

You are using linkchecker to check your WordPress site, but you get the following error multiple times:

$ linkchecker https://techoverflow.net
URL        `https://techoverflow.net/xmlrpc.php'
Parent URL https://techoverflow.net/, line 8, col 1
Real URL   https://techoverflow.net/xmlrpc.php
Check time 1.394 seconds
Result     Error: 405 Method Not Allowed

Solution:

Installing a plugin that disables XMLRPC doesn’t help here –  because the XMLRPC pingback link will still be present on every page.

Instead, just tell linkchecker to ignore the XMLRPC link altogether:

linkchecker https://techoverflow.net --ignore-url=/xmlrpc.php$

 

Solving Docker permission denied while trying to connect to the Docker daemon socket

Problem:

You are trying to run a docker container or do the docker tutorial, but you only get an error message like this:

docker: Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Post http://%2Fvar%2Frun%2Fdocker.sock/v1.26/containers/create: dial unix /var/run/docker.sock: connect: permission denied.
See 'docker run --help'.

 Solution:

The error message tells you that your current user can’t access the docker engine, because you’re lacking permissions to access the unix socket to communicate with the engine.

As a temporary solution, you can use sudo to run the failed command as root.
However it is recommended to fix the issue by adding the current user to the docker group:

Run this command in your favourite shell and then completely log out of your account and log back in (if in doubt, reboot!):

sudo usermod -a -G docker $USER

After doing that, you should be able to run the command without any issues. Run docker run hello-world as a normal user in order to check if it works. Reboot if the issue still persists.

Logging out and logging back in is required because the group change will not have an effect unless your session is closed.

requests: Download file if it doesn’t exist

Problem:

You want to download a URL to a file using the requests python library, but you want to skip the download if it doesn’t exist

Solution:

Use the following functions:

import requests
import os.path

def download_file(filename, url):
    """
    Download an URL to a file
    """
    with open(filename, 'wb') as fout:
        response = requests.get(url, stream=True)
        response.raise_for_status()
        # Write response data to file
        for block in response.iter_content(4096):
            fout.write(block)

def download_if_not_exists(filename, url):
    """
    Download a URL to a file if the file
    does not exist already.

    Returns
    -------
    True if the file was downloaded,
    False if it already existed
    """
    if not os.path.exists(filename):
        download_file(filename, url)
        return True
    return False

 

An introduction to Z-boxes

You most likely found this post for one of two reasons:

  • Either you haven’t heard of Z-Boxes and are interested in if they can somehow help you
  • or you have to learn about Z-Boxes and you have absolutely no idea how to understand the mathematical definitions.

Either way, we’re going to investigate Z-Boxes – not using a box of formulas but using examples and Python code.

Mehr lesen