When SugarCRM Case Closed

Using the cases.date_modified is not the same as the date a case is closed, as the case may have been modified after it was closed.
Thus, the query below will return the date the case was transitioned to ‘Closed’ provided the cases.status field is being audited.

select
  CONVERT_TZ(cases_audit.date_created,'+00:00','+10:00') as 'date_closed',
  accounts.name as 'account',
  cases.case_number,
  cases.name as 'case_id'
from
  cases_audit
  join cases on cases.id = cases_audit.parent_id
  join accounts on accounts.id = cases.account_id
where
  cases_audit.field_name = "status"
  and cases_audit.after_value_string = "Closed"
order by
  accounts.name asc,
  cases_audit.date_created desc;

Note, you might get more then one row returned per case if the case was re-opened, also my code above assumes a timezone of UTC+10

I happen to be in the UTC+10 timezone :).

Tracking down long running queries in SQL

I’ve had a client whose Linux Server (CentOS) gradually gets slower and slower, then falls over.

By logging into the MySQL command line, we were able to see that there were some long running queries that never end.

show processlist;

returns


| Id | User | Host | db | Command | Time | State | Info |
+———+———-+——————-+————–+———+——+————–+——————————————————————————————————+
| 2322386 | ssgadmin | 10.21.1.149:58526 | sugarcrm_ssg | Query | 2202 | Sending data | SELECT IFNULL(hr_humanresources.last_name,’’) hr_humanresources_last_name ,l3_cstm.verification_c l3 |

The key part here is that hr_humanresources_last_name has an underscore between the table name when it should be a period i.e. hr_humanresources.last_name. Knowing the table name was enough to tell me it was caused by something to do with SugarCRM that’s running on that server. But we don’t yet know what action caused these queries.

Knowing it was 2202 seconds ago from the time we ran the query we are able to pinpoint a time the action occurred. Looking through the SugarCRM log files did turn up that the error was often caused by a single user, but didn’t show up anything to help us figure out exactly what was the causal issue. Talking with that user, getting them to do what they’d done at that time didn’t turn up anything, the error wasn’t reproducing on demand.

So I turned to looking in the tracker table in the SugarCRM MySQL database for entries around the time of the error. Turns out that there is a Dashlet being loaded, that Dashlet uses a Report.

Each time I load the Dashlet or run the Report I get a corresponding long running query turn up. We’ve found our culprit, and we’re now able to recreate the report.

Location of the SugarCRM log file

By default it’s in the same base folder as the SugarCRM application files. But on occasion we’ll move it. Quickly find it’s location by looking in the config.php file. The two relevant lines are:


‘log_dir’ => ‘/var/log/sugarcrm/’,
‘log_file’ => ‘sugarcrm.log’,

If you’re at the command line, then this makes it easy:


grep 'log_dir' config.php

Allow a Checkbox to be Mass Update-able in SugarCRM

Here’s how to enable a checkbox to be updated enmasse.

First create a new extvardef.php file containing the following:

Then put that file into at the following location

/var/www/html/custom/Extension/modules/Module_Name/Ext/Vardefs/

Of course, you’d want to check any existing files in the folder to make sure none of them already deal with that field.

How to backup SugarCRM from the command line

From the linux command line, these commands let you backup and restore a SugarCRM database.

Update 2016-04-26: Just saw this SugarCRM KB Providing a Backup Without Sensitive Data which is really useful. Thanks Jared.

Firstly, for a proper back up of SugarCRM you’ll need two files, one containing the application files, one containing the SQL database.

First up, the backup…

Backup files:

Change to the folder you want to backup, then…

tar -zcvf CRM-BACKUP-FILES.tar.gz .

Edit: if you get an error ‘Permission Denied’ you may be trying to write to a folder you don’t have permission for, instead try writing to ~/CRM-BACKUP-FILES.tar.gz and it will likely work.
Edit: see also this StackOverflow article.

Backup sql (empty copy of the database):

mysqldump -u USERNAME -p -–no-data DATABASENAME > CRM-BACKUP-SQL.sql

Backup sql (with the data) :

mysqldump -u USERNAME -p DATABASENAME > CRM-BACKUP-SQL.sql

Then you can ‘tar’ the .sql file with

tar -zcvf DATABASENAME-mysql.tar.gz DATABASENAME-mysql.sql

Backup just a single table

mysqldump db_name table_name | gzip > table_name.sql.gz

If you only want the database schema, then in the SugarCRM web application you can do the following:

  • Admin
  • Diagnostic Tool
  • db schema
  • Download that file

Then for the restoration…

Restore files (to current folder):

tar -zxvf CRM-BACKUP-FILES.tar.gz

Restore sql:

mysql -u USERNAME -p DATABASENAME < CRM-BACKUP-SQL.sql

Restore just a single table

gunzip < table_name.sql.gz | mysql -u username -p db_name

These SugarCRM Knowledge Base articles may also be of use:

MySQL – Counting how many rows have a particular value

Working on a SugarCRM dashboard today, and I needed to count how many records had one value and how many records had a second value. I then wanted to know what percentage the first value was of the total. So there’s a bit of MySQL code that helps make this easy to do.


SELECT
  SUM(IF(field_name = 'value_one',1,0)) AS 'Value One',
  SUM(IF(field_name = 'value_two',1,0)) AS 'Value Two'
FROM table_name

What this does is counts up how many times value_one and value_two appear in field_name in the table table_name.

You can then take this a step further, if you wanted to, and I did…

you can use a line like this within your query:

This gives you the percentage that value_one is of (value_one + value_two).

Hat tip to this stackoverflow answer by eisberg.

Edit: 2014-05-26-21h53m put the final code snippet into a ‘Gist’ on GitHub.

Sugar Logic bug with a workaround for date fields

A client is recording the expiry dates of staff blue cards in their SugarCRM database.

So I used the following sugar logic to show if the blue card has expired or not:
ifElse(isBefore(today(),$bluecard_expiry_c),"Current","EXPIRED")

However, the problem here is that when a user of SugarCRM is editing the record, there is a known bug that means it will display “EXPIRED” regardless of if the date is in the past or future.

The workaround for this bug (thanks Anthony) is to wrap the date field in a date(toString($datefield)) bit of code, this transform the original sugar logic into this:

ifElse(isBefore(today(),date(toString($bluecard_expiry_c))),"Current","EXPIRED")

It is essentially force-casting the field to a date field for the javascript-side while passing the formula validator in Studio by first casting the date-type field to a string type.

Which works a treat.

How to convert UTC time to current timezone in MySQL

Using SugarCRM (this client is using Enterprise 6.5.15) and we want the last two hundred items in the tracker table. The problem is that the `tracker`.`date_modified` field is in UTC time, not the local time zone, which is +10:00.

The solution is to use the CONVERT_TZ MySQL command, as shown in the example below. It will convert between timezones for you.


SELECT `users`.`user_name` AS "User",
`tracker`.`module_name` AS "Module",
`tracker`.`item_summary` AS "Summary",
`tracker`.`action` AS "Action",
CONVERT_TZ(`tracker`.`date_modified`,'+00:00','+10:00') AS "Modified"
FROM `users`
INNER JOIN `tracker` ON
`users`.`id` = `tracker`.`user_id`
WHERE `tracker`.`deleted` = 0
ORDER BY `tracker`.`date_modified` DESC
LIMIT 200

Remove thousand separator in Integers in SugarCRM

We want to record a ‘Year’ value on a record in SugarCRM, but we don’t have a day and month to do with it, you can’t use a field that is of a ‘date’ type as that requires a full date (year, month, day).

You can use an ‘Integer’ field type tho. The downside is that it displays a thousands separator. i.e. it’ll show 2,014 instead of 2014.

To fix this, tick the Disable Format check box in Studio. This will remove the thousand separator.

Screenshot 2014-02-05 13.05.04

Sugar logic to calculate days an opportunity has been open

We want to find out how many days an Opportunity in SugarCRM has been open, using a calculated custom integer field in SugarCRM Studio. I’m assuming you’ve already created a new custom integer field, likely called something similar to “Days open”.

The sugar logic to calculate the difference between two dates is this:

abs(subtract(daysUntil($date1),daysUntil($date2)))

and the sugar logic to do an if then else is:

ifElse(test,if_true,if_false)

The first test we’ll do is if the sales stage is “Closed Lost” or “Closed Won”, if it’s true, we’ll calculate the days between the create date and the expected close date. If the sales stage is something other than “Closed Lost” or “Closed Won” then we’ll calculate the days between the create date and today.

This makes our pseudo sugar logic formula look like this:

ifElse(
	or(
		equal($sales_stage,“Closed Lost”),
		equal($sales_stage,”Closed Won”)
	),
	calc_days_create_to_expected_close_date,
	calc_days_create_to_today
)

Which, when we update it with the real sugar logic parts becomes this:

ifElse(
	or(
		equal($sales_stage,"Closed Lost"),
		equal($sales_stage,"Closed Won")
	),
	abs(
		subtract(
			daysUntil($date_entered),
			daysUntil($date_closed)
		)
	),
	abs(
		subtract(
			daysUntil($date_entered),
			daysUntil(
				today()
			)
		)
	)
)

Now, removing all the spaces we get this:

ifElse(or(equal($sales_stage,"Closed Lost"),equal($sales_stage,"Closed Won")),abs(subtract(daysUntil($date_entered),daysUntil($date_closed))),abs(subtract(daysUntil($date_entered),daysUntil(today()))))

This post was written in response to a question on LinkedIn.

MySQL snippet for past and next 6 Months

Spent some time today working with DATEDIFF, DATE_ADD and all kinds of horrid looking code before realising that to do a MySQL query to get records where the date is in the past or the next x months all I need is the following.


WHERE t_position_cstm.review_date_c <= DATE_ADD(DATE(now()), INTERVAL 6 MONTH)

You can change the 6 to whatever appropriate value you have, and you can change MONTH to a variety of other values, for me the two I used today was MONTH and DAY.

I’m using this on a SugarCRM MySQL database with a custom module of Position (the actual table name is t_position).